API to update Supplier Site Payment Method Lookup Code in Oracle APPS - iby_disbursement_setup_pub.update_external_payee

Note: 

• Use iby_disbursement_setup_pub.update_external_payee API to update Supplier Site Payment Method. 
• This API will not update ap_supplier_sites_all.payment_method_lookup_code column.
• Supplier Site payment method details are stored in iby_ext_party_pmt_mthds table.
• AP_VENDOR_PUB_PKG.Update_Vendor_Site do not use for Supplier Site Payment Method Update.
• Parameter Exclusive Payment flag(exclusive_pay_flag) is required.

DECLARE

    p_external_payee_tab_type   iby_disbursement_setup_pub.external_payee_tab_type;

    p_ext_payee_id_tab_type     iby_disbursement_setup_pub.ext_payee_id_tab_type;

    p_ext_payee_id_rec          iby_disbursement_setup_pub.Ext_Payee_ID_Rec_Type;

    l_ext_payee_rec             IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type;

x_return_status             VARCHAR2 (200) := NULL;

    x_msg_count                 NUMBER := 0;

    x_msg_data                  VARCHAR2 (200) := NULL;

    l_payee_upd_status          iby_disbursement_setup_pub.ext_payee_update_tab_type;

    

i                           NUMBER := 0;

    l_user_id fnd_user.user_id%TYPE;

l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;

l_resp_appl_id fnd_responsibility_tl.application_id%TYPE;

l_org_id hr_operating_units.organization_id%TYPE;

 BEGIN

BEGIN

SELECT user_id

  INTO l_user_id

  FROM fnd_user

WHERE user_name = 'XX_USER';

EXCEPTION

WHEN OTHERS THEN

l_user_id := NULL;

END;

BEGIN

SELECT responsibility_id, application_id 

  INTO l_resp_id, l_resp_appl_id

  FROM fnd_responsibility_tl

WHERE responsibility_name = 'Payables Manager'

   AND language = USERENV('LANG')

   ;

EXCEPTION

WHEN OTHERS THEN

l_resp_id := NULL;

l_resp_appl_id := NULL;

END;

BEGIN

SELECT organization_id

  INTO l_org_id

  FROM hr_operating_units

WHERE name = 'XX Operating Unit';

EXCEPTION

WHEN OTHERS THEN

l_org_id := NULL;

END;

-- Initialize apps session

fnd_global.apps_initialize(user_id      => l_user_id

                          ,resp_id      => l_resp_id

  ,resp_appl_id => l_resp_appl_id

  );

mo_global.init('SQLAP');

fnd_client_info.set_org_context(l_org_id);

    FOR j IN (SELECT ieppm.payment_method_code

                    ,iepa.payee_party_id

                    ,assa.vendor_site_id

                    ,iepa.ext_payee_id

                    ,assa.org_id

                    ,iepa.supplier_site_id

                    ,assa.party_site_id

               FROM ap_supplier_sites_all assa

                   ,ap_suppliers sup

                   ,iby_external_payees_all iepa

                   ,iby_ext_party_pmt_mthds ieppm

                   ,hr_operating_units ou

              WHERE sup.vendor_id = assa.vendor_id

                AND assa.pay_site_flag = 'Y'

                AND assa.vendor_site_id = iepa.supplier_site_id

                AND iepa.ext_payee_id = ieppm.ext_pmt_party_id(+)

                AND ieppm.payment_method_code IS NULL

                AND assa.org_id = ou.organization_id

                AND assa.vendor_site_id = 1024

        )

    LOOP

    

       p_external_payee_tab_type(i).default_pmt_method := 'EFT';

       p_external_payee_tab_type(i).payment_function := 'PAYABLES_DISB';

       p_external_payee_tab_type(i).exclusive_pay_flag := 'N';

       p_external_payee_tab_type(i).payee_party_id := j.payee_party_id;

       p_external_payee_tab_type(i).payer_org_id := j.org_id;

       p_external_payee_tab_type(i).payer_org_type := 'OPERATING_UNIT';

       p_external_payee_tab_type(i).supplier_site_id := j.supplier_site_id;

       p_external_payee_tab_type(i).Payee_Party_Site_Id := j.party_site_id;

       p_ext_payee_id_tab_type(i).ext_payee_id := j.ext_payee_id;

       

iby_disbursement_setup_pub.update_external_payee(

p_api_version            => 1.0

,p_init_msg_list          => 'T'

,p_ext_payee_tab          => p_external_payee_tab_type

,p_ext_payee_id_tab       => p_ext_payee_id_tab_type

,x_return_status          => x_return_status

,x_msg_count              => x_msg_count

,x_msg_data               => x_msg_data

,x_ext_payee_status_tab   => l_payee_upd_status

);

DBMS_OUTPUT.PUT_LINE ('External Payee Update :' || j.ext_payee_id);

DBMS_OUTPUT.PUT_LINE ('x_return_status: ' || x_return_status);

IF x_return_status = 'E'

THEN

FOR k IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST

LOOP

DBMS_OUTPUT.put_line('Error Message from table type : '

                                  || l_payee_upd_status (k).Payee_update_Msg);

END LOOP;

END IF;

i := 0;

----------

---COMMIT;

----------

    END LOOP;

 EXCEPTION

    WHEN OTHERS

    THEN

       DBMS_OUTPUT.PUT_LINE ('Error ' || SQLERRM);

 END;