API to upload supplier remittance email in Oracle Apps
Hi friends, we are going to discuss about the API to upload supplier remittance email in oracle apps. We will share the detail plsql script using oracle apps standard api to update and upload the supplier remittance email details in system. This is the complete script which helps to upload the supplier remittance details in oracle r12. This plsql script really help to mass update and upload the remittance detail for suppliers. This is the tested script and shared by oracle in the metalink to update the details about supplier remittance informations in oracle apps system.
One important thing about this standard API is , if we are only uploading the supplier remittance emails but wants to keep the other informations about the suppliers as it is then we need to pass each and every value to this api as per the value stored for these fields in oracle apps system. Other wise it will be chance that information can be lost. So oracle and we will always recommend to use the api in the same manner what we are sharing and if we need to keep the other field values too even if we don't want to change and update these field values. In this case we will refer the other field value from the database and refer the remittance email separately from other table or enter it manually for single case. Please find below the complete detail about the API to upload supplier remittance email in oracle apps.
 |
| API to upload supplier remittance email in oracle apps |
Important Standard API to upload supplier remittance email in oracle apps
iby_disbursement_setup_pub.Update_External_Payee
PLSQL API to upload supplier remittance email in oracle apps
Here below is the detail plsql script to using the oracle standard api 'iby_disbursement_setup_pub.Update_External_Payee' to update/upload the supplier remittance emails in oracle apps.
API to upload supplier remittance email in oracle apps
DECLARE
l_ext_payee_tab iby_disbursement_setup_pub.External_Payee_Tab_Type;
l_ext_payee_id_tab iby_disbursement_setup_pub.Ext_Payee_ID_Tab_Type;
l_ext_payee_update_tab iby_disbursement_setup_pub.Ext_Payee_Update_Tab_Type;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_count NUMBER;
l_msg_data VARCHAR2(2000);
cursor w is
select a.party_id
, b.party_site_id
, b.org_id
, e.Ext_Payee_Id
, e.exclusive_payment_flag
, a.vendor_id
, b.vendor_site_id
, e.OBJECT_VERSION_NUMBER
, i.payment_method_code DEFAULT_PAYMENT_METHOD_CODE
, e.ECE_TP_LOCATION_CODE
, e.BANK_CHARGE_BEARER
, e.BANK_INSTRUCTION1_CODE
, e.BANK_INSTRUCTION2_CODE
, e.BANK_INSTRUCTION_DETAILS
, e.PAYMENT_REASON_CODE
, e.PAYMENT_REASON_COMMENTS
, e.INACTIVE_DATE
, e.PAYMENT_TEXT_MESSAGE1
, e.PAYMENT_TEXT_MESSAGE2
, e.PAYMENT_TEXT_MESSAGE3
, e.DELIVERY_CHANNEL_CODE
, e.PAYMENT_FORMAT_CODE
, e.SETTLEMENT_PRIORITY
, xxsr.DELIVERY_METHOD -- here we are referencing the email from custom table --
, xxsr.REMIT_EMAIL -- here we are referencing the email from custom table --
, e.REMIT_ADVICE_FAX
, e.SERVICE_LEVEL_CODE
from ap_suppliers a
, ap_supplier_sites_all b
, iby_external_payees_all e
, iby_ext_party_pmt_mthds i
,xx_supplier_remittance_email xxsr
where a.vendor_id = b.vendor_id
and e.ext_payee_id = i.ext_pmt_party_id
and e.exclusive_payment_flag = 'N'
and e.payee_party_id = a.party_id
and b.vendor_site_id = e.supplier_site_id
and b.vendor_id=xxsr.vendor_id;
BEGIN
for x in w loop
DBMS_OUTPUT.PUT_LINE('Vendor ID : ' || x.vendor_id);
DBMS_OUTPUT.PUT_LINE('Site ID : ' || x.vendor_site_id);
l_ext_payee_tab(1).Payee_Party_Id := x.party_id;
l_ext_payee_id_tab(1).Ext_Payee_Id := x.Ext_Payee_Id;
l_ext_payee_tab(1).Payee_Party_Site_Id := x.party_site_id;
l_ext_payee_tab(1).Supplier_Site_Id := x.vendor_site_id;
l_ext_payee_tab(1).Payment_Function := 'PAYABLES_DISB';
l_ext_payee_tab(1).Exclusive_Pay_Flag := 'Y';
l_ext_payee_tab(1).Payer_Org_Type :='OPERATING_UNIT';
l_ext_payee_tab(1).Default_Pmt_method := x.DEFAULT_PAYMENT_METHOD_CODE;
l_ext_payee_tab(1).ECE_TP_Loc_Code := x.ECE_TP_LOCATION_CODE;
l_ext_payee_tab(1).Bank_Charge_Bearer := x.BANK_CHARGE_BEARER;
l_ext_payee_tab(1).Bank_Instr1_Code := x.BANK_INSTRUCTION1_CODE;
l_ext_payee_tab(1).Bank_Instr2_Code := x.BANK_INSTRUCTION2_CODE;
l_ext_payee_tab(1).Bank_Instr_Detail := x.BANK_INSTRUCTION_DETAILS;
l_ext_payee_tab(1).Pay_Reason_Code := x.PAYMENT_REASON_CODE;
l_ext_payee_tab(1).Pay_Reason_Com := x.PAYMENT_REASON_COMMENTS;
l_ext_payee_tab(1).Inactive_Date := x.INACTIVE_DATE;
l_ext_payee_tab(1).Pay_Message1 := x.PAYMENT_TEXT_MESSAGE1;
l_ext_payee_tab(1).Pay_Message2 := x.PAYMENT_TEXT_MESSAGE2;
l_ext_payee_tab(1).Pay_Message3 := x.PAYMENT_TEXT_MESSAGE3;
l_ext_payee_tab(1).Delivery_Channel := x.DELIVERY_CHANNEL_CODE;
l_ext_payee_tab(1).Pmt_Format := x.PAYMENT_FORMAT_CODE;
l_ext_payee_tab(1).Settlement_Priority := x.SETTLEMENT_PRIORITY;
l_ext_payee_tab(1).Remit_advice_delivery_method := x.DELIVERY_METHOD;
l_ext_payee_tab(1).Remit_advice_email := x.REMIT_EMAIL;
l_ext_payee_tab(1).remit_advice_fax := x.REMIT_ADVICE_FAX;
iby_disbursement_setup_pub.Update_External_Payee (p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_ext_payee_tab => l_ext_payee_tab
, p_ext_payee_id_tab => l_ext_payee_id_tab
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_ext_payee_status_tab => l_ext_payee_update_tab);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Return Status : ' || l_return_status);
IF l_return_status = 'S' THEN
DBMS_OUTPUT.PUT_LINE ('Flag updated successfully.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('Error occurred while updating remittance.');
IF l_msg_count = 1 THEN
DBMS_OUTPUT.PUT_LINE ('l_msg_data: ' || l_msg_data);
ELSIF l_msg_count > 1 THEN
LOOP
l_count := l_count + 1;
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE ('Message' || l_count || ' ---' || l_msg_data);
END LOOP;
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
 |
| API to upload supplier remittance email in oracle apps |
