Oracle EBS - SQL Query - Supplier Site associated Bank Details

Subject: Business need to extract the supplier site level associated bank details.

--SQL:

SELECT

    'Suppplier Site'    "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    iban,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv,

    apps.ap_supplier_sites_all   ass

WHERE

        1 = 1

    AND aps.vendor_id = ass.vendor_id

    AND ass.org_id = :p_org_id

    AND iepa.payee_party_id = aps.party_id

    AND iepa.party_site_id IS NOT NULL

    AND iepa.supplier_site_id IS NOT NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num

Oracle EBS - SQL Query - Supplier Header associated Bank Details

Subject: Business required to get the supplier information where Supplier Header associated bank details.

--SQL:

 SELECT

    'Suppplier Header'  "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv

WHERE

        1 = 1

    AND aps.vendor_id IN (

        SELECT

            vendor_id

        FROM

            apps.ap_supplier_sites_all ass

        WHERE

            org_id = nvl(:p_org_id, org_id)

    )

    AND iepa.payee_party_id = aps.party_id

    AND party_site_id IS NULL

    AND supplier_site_id IS NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

    AND ieba.bank_account_num IS NOT NULL

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num

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;