Salam Alaikum,
After struggling with the task of uploading for a long time, i share with you guys a complete API that can be used for uploading customers.
The following API can be used to create the following:
The Code i am sharing is merging all the api together in one block, but i will share the API's separated once i have them.
Hope you found it useful guys, feel free to leave a comment if have any note on the subject.
After struggling with the task of uploading for a long time, i share with you guys a complete API that can be used for uploading customers.
The following API can be used to create the following:
- Create Customer (using: HZ_PARTY_V2PUB.create_organization).
- Create Customer account (using: HZ_CUST_ACCOUNT_V2PUB.create_cust_account).
- Create location (using: HZ_LOCATION_V2PUB.create_location).
- Create Customer Site (using: HZ_PARTY_SITE_V2PUB.create_party_site).
- Create Customer Account site (using: Z_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site).
- Create Customer Account site use (Ship to & Bill to - using: HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use) .
- Create Contact Person (using: HZ_PARTY_V2PUB.create_person & HZ_PARTY_CONTACT_V2PUB.create_org_contact).
- Add phone number to contact (using: HZ_CONTACT_POINT_V2PUB.create_contact_point).
- Create Customer Account Role (using: HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role).
- Create External Bank (using: IBY_EXT_BANKACCT_PUB.create_ext_bank).
- Create External Branch (using: IBY_EXT_BANKACCT_PUB.create_ext_bank_branch).
- Create External Bank Account (using: IBY_EXT_BANKACCT_PUB.create_ext_bank_acct).
- Create Bank Account assignment to the customer (using: IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment).
The Code i am sharing is merging all the api together in one block, but i will share the API's separated once i have them.
DECLARE
v_return_status VARCHAR2 (5000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (5000);
-- misc variables
v_msg_dummy VARCHAR2 (5000);
t_output VARCHAR2 (5000);
v_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
v_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
v_cust_account_id NUMBER;
v_account_number VARCHAR2 (5000);
v_party_id NUMBER;
v_party_number VARCHAR2 (5000);
v_profile_id NUMBER;
v_cust_party_id NUMBER;
v_cust_party_number VARCHAR2 (5000);
v_cust_profile_id NUMBER;
v_application_id NUMBER;
v_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
v_location_id NUMBER;
v_location_rec HZ_LOCATION_V2PUB.location_rec_type;
v_currency_code VARCHAR2 (5000);
v_country_code VARCHAR2 (5000);
v_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
v_party_site_id NUMBER;
v_party_site_number VARCHAR2 (5000);
v_cust_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
v_cust_acct_site_id NUMBER;
v_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
v_bill_site_use_id NUMBER;
v_ship_site_use_id NUMBER;
v_org_id NUMBER;
v_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
v_person_profile_id NUMBER;
v_person_party_id NUMBER;
v_person_party_number VARCHAR2 (5000);
v_contact_party_id NUMBER;
v_contact_party_number VARCHAR2 (5000);
v_org_contact_id NUMBER;
v_party_rel_id NUMBER;
v_contact_point_id NUMBER;
v_org_contact_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
v_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
v_edi_rec HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
v_email_rec HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
v_phone_rec HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
v_telex_rec HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
v_web_rec HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
v_cust_account_role_id NUMBER;
v_cust_account_role_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE;
v_ext_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
v_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
v_bank_id NUMBER;
v_bank_exists NUMBER := 0;
v_ext_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
v_branch_id NUMBER;
v_start_date DATE;
v_end_date DATE;
v_acct_id NUMBER;
v_ext_banK_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
v_payer_context_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
v_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
v_assign_id NUMBER;
BEGIN
-- get application id
SELECT application_id
INTO v_application_id
FROM fnd_application_vl
WHERE application_name = 'Order Management';
-- get org details
SELECT hou.organization_id
INTO v_org_id
FROM hr_operating_units hou
-- hou.name will come from staging table or excel sheet
WHERE hou.name = 'Sahara Bulk Storage Facilities Limited';
-- set operating unit
mo_global.init ('AR'); --fnd_global.apps_initialize(1523, 50758, 222);
mo_global.set_policy_context ('S', v_org_id); -- get currency, territory details
SELECT territory_code, currency_code
INTO v_country_code, v_currency_code
FROM fnd_territories_tl ftt, fnd_currencies fc
WHERE fc.issuing_territory_code = ftt.territory_code -- nigeria comes from excel file
AND UPPER (ftt.territory_short_name) = UPPER ('Nigeria');
-- set organization recordv_organization_rec.created_by_module := 'HZ_CPUI';
v_organization_rec.organization_name := 'Igwe Super Customer';
v_organization_rec.application_id := v_application_id;
-- create organization
HZ_PARTY_V2PUB.create_organization (p_init_msg_list => FND_API.G_TRUE,
p_organization_rec => v_organization_rec,
x_party_id => v_party_id,
x_party_number => v_party_number,
x_profile_id => v_profile_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- set customer record type
v_cust_account_rec.status := 'A';
-- lookup is CUSTOMER_TYPE
v_cust_account_rec.customer_type := 'R'; -- lookup is CUSTOMER CLASS
v_cust_account_rec.customer_class_code := 'PUBLIC SECTOR COMPANIES';
v_cust_account_rec.application_id := v_application_id;
v_cust_account_rec.created_by_module := 'HZ_CPUI';
v_cust_account_rec.account_name := v_organization_rec.organization_name;
-- party id
v_organization_rec.party_rec.party_id := v_party_id;
v_organization_rec.gsa_indicator_flag := 'N';
-- create customer
HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
p_init_msg_list => FND_API.G_FALSE,
p_cust_account_rec => v_cust_account_rec,
p_organization_rec => v_organization_rec,
p_customer_profile_rec => v_customer_profile_rec,
p_create_profile_amt => FND_API.G_FALSE,
x_cust_account_id => v_cust_account_id,
x_account_number => v_account_number,
x_party_id => v_cust_party_id,
x_party_number => v_cust_party_number,
x_profile_id => v_cust_profile_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- create location record
v_location_rec.address1 := 'Block A, Plot 21 Olamijuyi Avenue';
v_location_rec.address2 := 'Parkview Estate. Ikoyi';
v_location_rec.city := 'Lagos';
v_location_rec.state := 'Lagos';
v_location_rec.country := v_country_code;
v_location_rec.created_by_module := 'HZ_CPUI';
v_location_rec.application_id := v_application_id;
v_location_rec.address_style := 'Nigeria Address Format';
-- now create location
HZ_LOCATION_V2PUB.create_location (p_location_rec => v_location_rec,
x_location_id => v_location_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- populate party site record type
v_party_site_rec.party_id := v_party_id;
v_party_site_rec.location_id := v_location_id;
v_party_site_rec.status := 'A';
v_party_site_rec.identifying_address_flag := 'Y';
v_party_site_rec.created_by_module := 'HZ_CPUI';
v_party_site_rec.application_id := v_application_id;
-- create party site
HZ_PARTY_SITE_V2PUB.create_party_site (
p_party_site_rec => v_party_site_rec,
x_party_site_id => v_party_site_id,
x_party_site_number => v_party_site_number,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- create account site record type
v_cust_acct_site_rec.cust_account_id := v_cust_account_id;
v_cust_acct_site_rec.party_site_id := v_party_site_id;
v_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
v_cust_acct_site_rec.application_id := v_application_id;
v_cust_acct_site_rec.status := 'A';
v_cust_acct_site_rec.org_id := v_org_id;
-- create customer account site
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (
p_init_msg_list => FND_API.G_TRUE,
p_cust_acct_site_rec => v_cust_acct_site_rec,
x_cust_acct_site_id => v_cust_acct_site_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ' : ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- create account site use record for SHIP_TO
v_cust_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;
v_cust_site_use_rec.site_use_code := 'SHIP_TO';
v_cust_site_use_rec.primary_flag := 'Y';
v_cust_site_use_rec.status := 'A';
-- location id
v_cust_site_use_rec.LOCATION := v_location_id;
v_cust_site_use_rec.created_by_module := 'HZ_CPUI';
v_cust_site_use_rec.application_id := v_application_id;
v_cust_site_use_rec.org_id := v_org_id;
-- create customer account site use
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
p_init_msg_list => FND_API.G_TRUE,
p_cust_site_use_rec => v_cust_site_use_rec,
p_customer_profile_rec => v_customer_profile_rec,
p_create_profile => FND_API.G_TRUE,
p_create_profile_amt => FND_API.G_FALSE,
x_site_use_id => v_ship_site_use_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- create cust site use for bill to
v_cust_site_use_rec.site_use_code := 'BILL_TO';
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
p_init_msg_list => FND_API.G_TRUE,
p_cust_site_use_rec => v_cust_site_use_rec,
p_customer_profile_rec => v_customer_profile_rec,
p_create_profile => FND_API.G_TRUE,
p_create_profile_amt => FND_API.G_FALSE,
x_site_use_id => v_bill_site_use_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- create contact person rec
v_person_rec.person_first_name := 'Chike';
v_person_rec.person_last_name := 'Iheomamere';
v_person_rec.person_title := 'MR.';
v_person_rec.party_rec.status := 'A';
v_person_rec.head_of_household_flag := 'N';
v_person_rec.application_id := v_application_id;
v_person_rec.created_by_module := 'HZ_CPUI';
-- create person
HZ_PARTY_V2PUB.create_person (p_init_msg_list => fnd_api.g_false,
p_person_rec => v_person_rec,
x_party_id => v_person_party_id,
x_party_number => v_person_party_number,
x_profile_id => v_person_profile_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- create org contact record type
v_org_contact_rec.job_title := 'Manager';
v_org_contact_rec.decision_maker_flag := 'N';
v_org_contact_rec.created_by_module := 'HZ_CPUI';
-- party_id of created contact
v_org_contact_rec.party_rel_rec.subject_id := v_person_party_id;
v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
-- party_id of the organization
v_org_contact_rec.party_rel_rec.object_id := v_party_id;
v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
v_org_contact_rec.party_rel_rec.status := 'A';
-- create contact
HZ_PARTY_CONTACT_V2PUB.create_org_contact (
p_init_msg_list => fnd_api.g_false,
p_org_contact_rec => v_org_contact_rec,
x_org_contact_id => v_org_contact_id,
x_party_rel_id => v_party_rel_id,
x_party_id => v_contact_party_id,
x_party_number => v_contact_party_number,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- create phone record type for contact
v_contact_point_rec.contact_point_type := 'PHONE';
v_contact_point_rec.owner_table_name := 'HZ_PARTIES';
v_contact_point_rec.owner_table_id := v_person_party_id;
v_contact_point_rec.primary_flag := 'Y';
v_contact_point_rec.contact_point_purpose := 'BUSINESS';
v_phone_rec.phone_country_code := '234';
v_phone_rec.phone_area_code := '01';
v_phone_rec.phone_number := '08023165365';
v_contact_point_rec.status := 'A';
v_phone_rec.phone_line_type := 'GEN';
v_contact_point_rec.created_by_module := 'HZ_CPUI';
-- add fax if there is one available
v_telex_rec.telex_number := '04-46879879';
-- emailv_email_rec.email_address := 'info@acornplc.com';
-- webv_web_rec.url := 'www.acornplc.com';
-- add phone number to contact
HZ_CONTACT_POINT_V2PUB.create_contact_point (
p_init_msg_list => fnd_api.g_false,
p_contact_point_rec => v_contact_point_rec,
p_edi_rec => v_edi_rec,
p_email_rec => v_email_rec,
p_phone_rec => v_phone_rec,
p_telex_rec => v_telex_rec,
p_web_rec => v_web_rec,
x_contact_point_id => v_contact_point_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- populate party site record type
v_cust_account_role_rec.party_id := v_contact_party_id;
v_cust_account_role_rec.role_type := 'CONTACT';
v_cust_account_role_rec.cust_account_id := v_cust_account_id;
v_cust_account_role_rec.cust_acct_site_id := v_cust_acct_site_id;
v_cust_account_role_rec.primary_flag := 'Y';
v_cust_account_role_rec.application_id := v_application_id;
v_cust_account_role_rec.created_by_module := 'HZ_CPUI';
v_cust_account_role_rec.status := 'A';
-- create party site
HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
p_init_msg_list => FND_API.G_TRUE,
p_cust_account_role_rec => v_cust_account_role_rec,
x_cust_account_role_id => v_cust_account_role_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- customer banks
-- create ext bank record type
v_ext_bank_rec.bank_name := 'Zenith Bank Nigeria Plc';
v_ext_bank_rec.country_code := v_country_code;
-- check if bank exists
IBY_EXT_BANKACCT_PUB.check_bank_exist (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_country_code => v_ext_bank_rec.country_code,
p_bank_name => v_ext_bank_rec.bank_name,
p_bank_number => NULL,
x_response => v_response,
x_bank_id => v_bank_id,
x_end_date => v_end_date,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
-- if bank does not exist then create it
IF v_bank_id IS NULL THEN
-- create supplier bank
IBY_EXT_BANKACCT_PUB.create_ext_bank (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_rec => v_ext_bank_rec,
x_response => v_response,
x_bank_id => v_bank_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
--debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
-- raise exception here
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
-- end create bank
END IF;
-- set bank branch record
v_ext_bank_branch_rec.bank_party_id := v_bank_id;
-- no branch name given in excel sheet
v_ext_bank_branch_rec.branch_name := 'Default Branch';
v_ext_bank_branch_rec.branch_type := 'OTHER';
-- check if branch exists
IBY_EXT_BANKACCT_PUB.check_ext_bank_branch_exist (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_bank_id => v_ext_bank_branch_rec.bank_party_id,
p_branch_name => v_ext_bank_branch_rec.branch_name,
p_branch_number => NULL,
x_response => v_response,
x_branch_id => v_branch_id,
x_end_date => v_end_date,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
-- if branch does not exist then create it
IF v_branch_id IS NULL THEN
-- create bank branch
IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_branch_rec => v_ext_bank_branch_rec,
x_response => v_response,
x_branch_id => v_branch_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
-- debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
-- raise exception here
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
END IF;
-- set bank account record
v_ext_bank_acct_rec.country_code := v_country_code;
v_ext_bank_acct_rec.branch_id := v_branch_id;
v_ext_bank_acct_rec.bank_id := v_bank_id;
-- party id of the customer
v_ext_bank_acct_rec.acct_owner_party_id := v_party_id;
-- account name is given in excel sheet
v_ext_bank_acct_rec.bank_account_name := 'Igwe Super Customer Account';
v_ext_bank_acct_rec.bank_account_num := '0580034253214';
v_ext_bank_acct_rec.currency := v_currency_code;
-- check if branch exists
IBY_EXT_BANKACCT_PUB.check_ext_acct_exist (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_acct_rec => v_ext_bank_acct_rec,
x_acct_id => v_acct_id,
x_start_date => v_start_date,
x_end_date => v_end_date,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => v_response);
-- if bank account does not exist then create it
IF v_acct_id IS NULL THEN -- create bank branch
IBY_EXT_BANKACCT_PUB.create_ext_bank_acct (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_acct_rec => v_ext_bank_acct_rec,
x_acct_id => v_acct_id,
x_response => v_response,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
-- debug section
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
-- raise exception here
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
-- end debug
END IF;
-- payer record type
/* options for payment function are
AR_CUSTOMER_REFUNDS - Receivables Customer Refunds
CUSTOMER_PAYMENT - To collect payment from a customer
*/
v_payer_context_rec.payment_function := 'CUSTOMER_PAYMENT';
v_payer_context_rec.party_id := v_party_id;
v_payer_context_rec.org_type := 'OPERATING_UNIT';
v_payer_context_rec.org_id := v_org_id;
v_payer_context_rec.cust_account_id := v_cust_account_id;
-- this is not actually the account site id
-- but the account site use id
v_payer_context_rec.account_site_id := v_bill_site_use_id;
-- assignment attributes
v_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
-- the external bank account id
v_assignment_attribs.instrument.instrument_id := v_acct_id;
--dbms_output.put_line(v_acct_id);
v_assignment_attribs.priority := 1;
--v_assignment_attribs.start_date := sysdate;
-- map account to customer
IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_payer => v_payer_context_rec,
p_assignment_attribs => v_assignment_attribs,
x_assign_id => v_assign_id,
x_response => v_response);
-- debug section
IF v_return_status != 'S' THEN
IF v_msg_count > 0 THEN
FOR j IN 1 .. v_msg_count LOOP
fnd_msg_pub.get (j,
FND_API.G_FALSE,
v_msg_data,
v_msg_dummy);
t_output := ('Msg' || TO_CHAR (j) || ': ' || v_msg_data);
-- raise exception here
DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
END LOOP;
END IF;
END IF; -- end debug
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
Hope you found it useful guys, feel free to leave a comment if have any note on the subject.
