DECLARE
x_return_status VARCHAR2 (200) := NULL;
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (200) := NULL;
t_output VARCHAR2 (200) := NULL;
t_msg_dummy VARCHAR2 (200) := NULL;
l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type;
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;
i NUMBER := 0;
cursor c1 is
select distinct
e.ext_payee_id
,e.payee_party_id
,e.supplier_site_id
,e.party_site_id payee_party_site_id,
case when e.remit_advice_email is not null then (‘xxx’)
when e.remit_advice_email is null then ‘yyy’ end email_address
from
ap_suppliers a
,ap_supplier_sites_all b
,iby_external_payees_all e
where 1=1
AND a.vendor_id = b.vendor_id
AND b.inactive_date IS NULL
and b.org_id = 382
AND a.end_date_active IS NULL
AND a.party_id = e.payee_party_id
and e.party_site_id is NULL
and e.supplier_site_id is null
and e.org_id is null
and e.org_type is null
and e.remit_advice_delivery_method = ‘EMAIL’;
BEGIN
FOR rec IN c1
LOOP
fnd_global.apps_initialize(
user_id => 43272,
resp_id => 54240,
resp_appl_id => 200
);
MO_GLOBAL.INIT(‘SQLAP’);
mo_global.set_policy_context(‘S’,382);
i := i + 1;
p_external_payee_tab_type (i).Remit_advice_delivery_method := ‘EMAIL’;
p_external_payee_tab_type (i).Remit_advice_email :=rec.email_address;
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).payer_org_type := NULL;
p_external_payee_tab_type (i).payer_org_id := NULL;
p_ext_payee_id_tab_type (i).ext_payee_id := rec.ext_payee_id;
p_external_payee_tab_type (i).payee_party_id := rec.payee_party_id;
p_external_payee_tab_type (i).supplier_site_id := NULL;
p_external_payee_tab_type (i).Payee_Party_Site_Id := NULL;
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);
IF x_return_status <> ‘S’
THEN
IF x_msg_count > 0
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (i,fnd_api.g_false,x_msg_data,t_msg_dummy);
–DBMS_OUTPUT.put_line (‘Error Message : ‘ || x_msg_data);
t_output := (TO_CHAR (i) || ‘: ‘ || x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.put_line ( ‘Error occured while updating the Payment Details ‘ ||rec.supplier_site_id|| t_output);
ELSE
COMMIT;
END IF;
/*FOR j 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 (j).payee_update_msg);
END LOOP;*/
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (‘Error ‘ || SQLERRM);
END;
