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 |
1 comments:
declare
v_status varchar2(1);
v_error_message varchar2(2000);
v_vendor_id number;
v_email_address varchar2(30);
v_employee_id number;
v_vendor_site_id number;
v_party_id number;
v_name varchar2(100);
p_country_code VARCHAR2 (200):='US';
-- p_bank_name VARCHAR2 (200);
p_bank_number VARCHAR2 (200);
p_branch_number VARCHAR2 (200);
v_bank_id NUMBER;
v_branch_id NUMBER;
x_end_date DATE;
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
lv_return_status varchar2(1);
lv_msg_count number;
lv_msg_data varchar2(2000);
lv_vendor_site_id ap_supplier_sites.vendor_site_id%type;
lv_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
lv_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
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;
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;
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;
p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
p_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
p_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
p_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
p_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
p_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
x_contact_point_id NUMBER;
i NUMBER := 0;
v_email varchar2(10):='EMAIL';
begin
FOR j
IN (SELECT ieppm.payment_method_code,
iepa.payee_party_id,
iepa.ext_payee_id,
iepa.remit_advice_email,
iepa.supplier_site_id
FROM ap_suppliers sup,
iby_external_payees_all iepa,
iby_ext_party_pmt_mthds ieppm
WHERE iepa.payee_party_id = sup.party_id
and iepa.ext_payee_id = ieppm.ext_pmt_party_id(+)
AND sup.segment1='392905'
and supplier_site_id is null)
LOOP
fnd_global.apps_initialize (fnd_global.user_id,fnd_global.RESP_ID,fnd_global.RESP_APPL_ID);
MO_GLOBAL.INIT('SQLAP');
dbms_output.put_line('Step1');
p_external_payee_tab_type (i).default_pmt_method := 'WIRE';
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 := null;
p_external_payee_tab_type (i).payer_org_type := 'OPERATING_UNIT';
p_ext_payee_id_tab_type (i).ext_payee_id := j.ext_payee_id;
p_external_payee_tab_type (i).remit_advice_email :='Supplier_Test351@gmail.com';
dbms_output.put_line('Step2');
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('Step3');
COMMIT;
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;
end;
/
Post a Comment