Oracle EBS - R12 - Inventory - Oracle API to Update Lot Origin Date of Item Lot Numbers

 -- R12 - INV - Sample Script to Update Lot Number Lot Origin Date usinginv_lot_api_pub.update_inv_lot

DECLARE

    l_object_id      NUMBER;

    l_return_status  VARCHAR2(1);

    l_msg_count      NUMBER;

    l_msg_data       VARCHAR2(4000);

    l_expire_date    DATE;

    x_lot_rec        mtl_lot_numbers%rowtype;

    l_lot_rec        mtl_lot_numbers%rowtype;

    l_source         NUMBER;

    l_api_version    NUMBER := 1.0;

    l_init_msg_list  VARCHAR2(1000) := fnd_api.g_false;

    l_commit         VARCHAR2(1000) := fnd_api.g_false;

    CURSOR c_item_info IS

    SELECT

        inventory_item_id,

        organization_id,

        lot_number,

        creation_date,

        origination_date,

        c_attribute1

    FROM

        mtl_lot_numbers

    WHERE

            1 = 1

        AND trunc(origination_date) BETWEEN trunc(TO_DATE('03/03/24', 'MM/DD/YY')) AND trunc(TO_DATE('03/05/24', 'MM/DD/YY'))

        AND trunc(creation_date) != trunc(origination_date)

        AND organization_id IN ( 85, 97 )

--AND TRUNC(CREATION_DATE) >= SYSDATE-150

--and C_ATTRIBUTE1='WMOTCWRJAB'

--and c_attribute1 is not null

        AND lot_number = '1710010' --'1542009' --'1423007'

        ;

BEGIN

-- initialization required for R12

    mo_global.set_policy_context('S', 102);

    mo_global.init('INV');

-- Initialization for Organization_id

    inv_globals.set_org_id(102);

-- initialize environment

    fnd_global.apps_initialize(user_id => 11445,-----------------------------------> pass in user_id User Id=11445 ,

                              resp_id => 20634,

                              resp_appl_id => 401);

    

    FOR i IN c_item_info LOOP

    

    l_lot_rec.inventory_item_id:=i.inventory_item_id;

    l_lot_rec.organization_id:=i.organization_id;

    l_lot_rec.lot_number:=i.lot_number;

    l_lot_rec.origination_date:=i.creation_date;

    

        dbms_output.put_line('Calling inv_lot_api_pub.update_inv_lot API to Update Lot Numbers');

        dbms_output.put_line('*********************************************');

        inv_lot_api_pub.update_inv_lot(x_return_status => l_return_status, x_msg_count => l_msg_count,

                                      x_msg_data => l_msg_data,

                                      x_lot_rec => x_lot_rec,

                                      p_lot_rec => l_lot_rec,

                                      p_source => l_source,

                                      p_api_version => 1.0,

                                      p_init_msg_list => l_init_msg_list,

                                      p_commit => l_commit);

        dbms_output.put_line('The Status Returned by the API is => ' || l_return_status);

        IF l_return_status = fnd_api.g_ret_sts_success THEN

            COMMIT;

        ELSE

            ROLLBACK;

        END IF;

        dbms_output.put_line('x_msg_count :' || l_msg_count);

        dbms_output.put_line('x_msg_data :' || l_msg_data);

    END LOOP;

END;

Oracle EBS - SQL Query to get the employee default expense account

 

SELECT

    concatenated_segments

FROM

    apps.gl_code_combinations_kfv

WHERE

    code_combination_id IN (

        SELECT DISTINCT

            default_code_comb_id

        FROM

            apps.per_all_assignments_f

            WHERE  effective_end_date >= sysdate

        AND

            person_id IN (

                SELECT

                    person_id

                FROM

                    apps.per_all_people_f

                WHERE

                    effective_end_date >= sysdate

                    and person_id LIKE '&person_id'

            )

    );

Oracle EBS - Employee - Manager Hierarchy SQL Query

Subject: SQL Query required to get the employee and Manager hierarchy

--SQL Query:--

SELECT

    papf.employee_number,

    papf.full_name,

    papf.person_id,

    ppg.segment1                 officer_flag, --executive flag for officers (Y is officer, N is not)

    nvl(spapf.full_name, 'NONE') supervisor_name,

    spapf.person_id              supv_person_id,

    level,

    pjobs.name                                         "Job"  

FROM

    per_people_x      papf,

    per_people_x      spapf,

    per_assignments_x paaf,

    pay_people_groups ppg,

    per_jobs                  pjobs

WHERE

        papf.person_id = paaf.person_id

    AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

    AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date

    AND sysdate BETWEEN spapf.effective_start_date AND spapf.effective_end_date

--AND_xxx_person_type.is_employee(:p_eff_date, papf.person_id)

    AND paaf.assignment_type IN ( 'E', 'C' )

    AND paaf.supervisor_id = spapf.person_id

    AND paaf.people_group_id = ppg.people_group_id

    AND paaf.primary_flag = 'Y'

    AND pjobs.job_id (+) = paaf.job_id

  --  AND pjobs.name like '%Senior Director%'

START WITH

    papf.employee_number = :p_emp_num --Enter emp num of employee whom Hierachy Need to Find

CONNECT BY

    PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level

ORDER BY

    level DESC