Hey all.. This is in continuation to my blog on “Create Payment Method API“.
Let us take a look at how to update payment method using APIs. API => iby_disbursement_setup_pub.update_external_payee
My requirement was to update the payment method for all the supplier sites that has payment method as “EFT” but no Bank Information.
You may download the PDF version. Click on the link update_payment_method.
Tables Affected:
- IBY_EXTERNAL_PAYEES_ALL
- IBY_EXT_PARTY_PMT_MTHDS
I have also noticed that sometimes you get the error message “IBY_EXT_PAYEE_NOT_EXIST“. Please make sure that:
- Payee_party_site_id is passed
- Session is initialized => use fnd_global , mo_global
Hope this helps. Let me know if you have questions.
I have used your code to create payment method at the supplier site level, and I see it in the payment tables, however, it’s not showing on the supplier screen for payment methods (even for site level), and it’s not defaulting when creating an invoice. If I use the screen to set the payment method, it works correctly and defaults for invoice. If I use the api to update the payment method, again I see the table is updated however the screen does not change, and the invoice default does not change. Any suggestions?
Hi,
I hope you have used “IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee” API to create the payment method.
I would check the following:
1. Make sure that the data is populated in both IBY_EXTERNAL_PAYEES_ALL and IBY_EXT_PARTY_PMT_MTHDS tables.
2. Make sure that the org_id is correctly populated for the supplier site in IBY_EXTERNAL_PAYEES_ALL table
3. Make sure that the payee_party_id, supplier_site_id, party_site_id are populated correctly in IBY_EXTERNAL_PAYEES_ALL table.
4. Check the value for “DEFAULT_PAYMENT_METHOD_CODE” for the supplier site in IBY_EXTERNAL_PAYEES_ALL Table
5. Make sure that “INACTIVE_DATE” column is not populated for the supplier site level record in IBY_EXTERNAL_PAYEES_ALL Table
Also, when you check from the Invoice workbench, are you using the correct Operating Unit / Org? Let me know and we can surely work on this.
Thanks,
Raj
Hi. Thank you for the reply.
i did use the create_external_payee API to create originally, but realized I had not changed the org, so a record exist with the incorrect org. Yesterday, I used the screen to save a payment method, I saw the record in the table (this is when I realized original record had incorrect org). So, then I used the update_external_payee API to update the record. I see new records in the table correctly now with correct org and all other correct information, but when I go to the screen, I still see the one I set manually. If I change the payment method using the screen, I see the records updated in the tables. If I use the API again to update, I do not see the change in the form.
I am going to start with a fresh supplier with no records and use the create_external_payee API and make sure all correct variables including org_id before I proceed today and see if I get different results.
Thanks,
V
Hey.. Let me know once you try the API for fresh suppliers.
During update, what happens is the old record gets end dated (INACTIVE_DATE is populated). And a new record is created. That is my undrestanding 🙂
Check if that is the case. Also there will be a record at the supplier level as well. You can find this record where org_id is null and with the same payee_party_id.
So you will have two records. One at the supplier level (org_id is null) and supplier site level (org_id is populated). check the default_payment_method_code for supplier level and supplier site level records.
I am intrigued to know why it is not showing the payment method correctly!!
Thanks
~Raj
I tried again with fresh supplier, and still experiencing the same thing. I have tried by updating the supplier record only, the supplier site record only, both records. Nothing seems to make this value show in the payment details supplier screens. I have also opened an SR with Oracle Support.
Thanks,
V
Ok. Keep me updated as well. Meanwhile I am also gonna try this in my test instance to see how it behaves.
It has worked for me and I really want to know why it is not working in your instance. I will update the comments if I get anything new on this.
Have a good weekend.
Thanks,
Raj
Hello,
I have to change the default payment method for all the suppliers, so i implemented the iby_disbursement_setup_pub.update_external_payee API.
My problem today is that the payment method are not changing for the Site line in the iby_external_payees_all table (it change for the supplier line) payee.
Please take a look at my script below as for an example:
/* Formatted on 02/07/2014 18:06:19 (QP5 v5.163.1008.3004) */
DECLARE
l_user_id NUMBER := 0;
l_RESPONSIBILITY_ID NUMBER := 0;
l_RESPONSIBILITY_APPL_ID NUMBER := 0;
l_organization_id NUMBER := 0;
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;
–IBY_PAYMENT_METHODS_B.payment_method_code%TYPE :=’CHECK’;
p_ext_payee_id_tab_type iby_disbursement_setup_pub.ext_payee_id_tab_type;
–IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE:=12193 ;
i NUMBER := 1;
CURSOR cur_payees
IS
SELECT payee_party_id, ext_payee_id FROM iby_external_payees_all;
BEGIN
— extraction des id reponsabilité AP Responsable Fournisseur
SELECT resp.RESPONSIBILITY_ID, grp.RESPONSIBILITY_APPLICATION_ID
INTO l_RESPONSIBILITY_ID, l_RESPONSIBILITY_APPL_ID
FROM FND_RESPONSIBILITY_TL resp, FND_USER_RESP_GROUPS grp
WHERE resp.RESPONSIBILITY_ID = grp.RESPONSIBILITY_ID
AND RESPONSIBILITY_NAME LIKE ‘AP – Responsable Fournisseurs%’
AND resp.language = ‘F’;
— extraction id utilisateur CORPORATE1
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘CORPORATE1’;
— extraction de l’unité operationnelle
SELECT organization_id
INTO l_organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE name = ‘WS UO’;
fnd_msg_pub.delete_msg (NULL);
fnd_msg_pub.initialize;
apps.fnd_global.apps_initialize (l_user_id,
l_RESPONSIBILITY_ID,
l_RESPONSIBILITY_APPL_ID);
mo_global.set_policy_context (‘M’, l_organization_id);
— FOR r0 IN cur_payees
— LOOP
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_ext_payee_id_tab_type (i).ext_payee_id := 594; –376
p_external_payee_tab_type (i).payee_party_id := 6246;
p_external_payee_tab_type (i).Payer_Org_Id := NULL;
p_external_payee_tab_type (i).Payer_Org_Type := ‘OPERATING_UNIT’;
p_external_payee_tab_type (i).Supplier_Site_Id := NULL;
p_external_payee_tab_type (i).payee_party_site_id := 2204;
iby_disbursement_setup_pub.update_external_payee (
p_api_version => 1.0,
p_init_msg_list => ‘T’,
–fnd_api.g_true,
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 (‘Return Status : ‘ || x_return_status);
DBMS_OUTPUT.put_line (‘Error Message : ‘ || x_msg_data);
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 occurred while updating the Payment Method’ || t_output);
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;
END;
/
Regards,
Tarik F.
This is the error message:
Error occurred while updating the Payment Method1:
Error Message from table type : IBY_EXT_PAYEE_NOT_EXIST (EXT_PAYEE_ID=594)
Regards,
Tarik F.
Hi Tarik,
The error is because there are no records in IBY_EXT_PARTY_PMT_MTHDS table for that ext_payee_id.
Let me know what you get for the below Select statement:
SELECT *
FROM IBY_EXT_PARTY_PMT_MTHDS
WHERE EXT_PMT_PARTY_ID = 594;
Why don’t you try to update a record that exist in both IBY_EXTERNAL_PAYEES_ALL and IBY_EXT_PARTY_PMT_MTHDS.
You can get the records using the below statement:
SELECT * FROM IBY_EXTERNAL_PAYEES_ALL iepa, IBY_EXT_PARTY_PMT_MTHDS ieppm
WHERE ieppm.EXT_PMT_PARTY_ID = iepa.ext_payee_id
AND iepa.org_id =
AND iepa.supplier_site_id IS NOT NULL;
Thanks,
Raj
Hi Raj,
Thanks for uploading this.This is very useful for me.Can u share full package code if u have for this.my mail id is chkiranapps@gmail.com
Thanks for the comments. For me it was just an update script so I do not have a package code for this. However you can use the same code and create a package. Just remember to add FND Output and Log messages if you are planning on creating a concurrent program. Let me know if you need help on that.
im struggling to update the payment method for the records which are supplier_site_id, org_id,party_site_id,org_type as null .any suggessons are more help full..thanks
Hi Chaitanya,
I am assuming you can populate the following values (without passing the org id and site id) for external_payee_tab_type record:
default_pmt_method
payment_function
exclusive_pay_flag
payee_party_id
ext_payee_id
Let me know if that works.
Thanks,
Arun
Hi Arun,
Thanks for the quick reply,
I have tried by not passing the site_id and org_id but the api is not populating any values.
in iby_external_payees_all table i have 2 rows in which one is populated with site_id,org_id…etc other row is with without these
but i can update the payment method is associated with second row ext_payee_id i.e ext_pmt_party_id of iby_ext_party_pmt_mthds .
please check my below api and correct me if im wrong.
DECLARE
l_user_id NUMBER := XXX;
l_RESPONSIBILITY_ID NUMBER := XXX;
l_RESPONSIBILITY_APPL_ID NUMBER := XXX;
l_organization_id NUMBER := XXX;
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;
–IBY_PAYMENT_METHODS_B.payment_method_code%TYPE :=’CHECK’;
p_ext_payee_id_tab_type iby_disbursement_setup_pub.ext_payee_id_tab_type;
–IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE:=XXX;
i NUMBER := 1;
CURSOR cur_payees
IS
select
epa.payee_party_id,
— assa.vendor_site_id,
epa.ext_payee_id
–assa.org_id,
–assa.party_site_id
from
hz_parties hp ,
iby_external_payees_all epa,
iby_ext_party_pmt_mthds pm
where hp.party_id = epa.payee_party_id
and epa.ext_payee_id = pm.ext_pmt_party_id
and pm.primary_flag =’Y’
and pm.payment_flow=’DISBURSEMENTS’
and pm.ext_party_pmt_mthd_id=XXX;
–and hp.party_name =’XXXXXX’;
BEGIN
apps.fnd_global.apps_initialize (l_user_id,
l_RESPONSIBILITY_ID,
l_RESPONSIBILITY_APPL_ID);
mo_global.set_policy_context (‘M’, l_organization_id);
FOR r0 IN cur_payees
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_ext_payee_id_tab_type (i).ext_payee_id := r0.ext_payee_id;
p_external_payee_tab_type (i).payee_party_id := r0.payee_party_id;
p_external_payee_tab_type (i).Payer_Org_Id := l_organization_id;
p_external_payee_tab_type (i).Payer_Org_Type := ‘OPERATING_UNIT’;
p_external_payee_tab_type (i).Supplier_Site_Id := 12345;
p_external_payee_tab_type (i).payee_party_site_id := 1234567;
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 (‘Return Status : ‘ || x_return_status);
DBMS_OUTPUT.put_line (‘Error Message : ‘ || x_msg_data);
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 occurred while updating the Payment Method’ || t_output);
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;
END;
/
Hi Chaitanya,
The code will work for Supplier site level updates. For updating supplier level details, you have to only pass the below details:
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_ext_payee_id_tab_type (i).ext_payee_id := r0.ext_payee_id;
p_external_payee_tab_type (i).payee_party_id := r0.payee_party_id;
Can you also send me the value for “Payer_Org_Type” field at the supplier level record? Are you getting any error messages when you try the API for supplier level update?
Did you try this for one supplier by hard coding the supplier details?
Thanks,
Arun
I updated using the above API, yes i see twoo entries , one for EFT and other for Check in the Payment Methods table . But there is no inactive date for the previous payment option.
Hi Anil,
What was the previous record? And what did you update?
Thanks,
Arun