Customer Conversion program to Upload step by step using API in Oracle Apps (EBS)
Step 1 Create table xxomera_customer_stg
xxomera_customer_stg Table Script
Create table xxomera_customer_data_populate
xxomera_customer_data_populate Table Script
Step 2 Create .ctl file xxomera_ar_cust_cnv_load.ctl
xxomera_ar_cust_cnv_load.ctl
Step 3 Create the .csv file omera_customers.csv
omera_customers.csv
Step 4 Register the .csv file with .ctl to upload the data in Table "xxomera_customer_stg"
XXOMERA_UPLOAD_CUSTOMER_LDR
XXOMERA_UPLOAD_CUSTOMER_LDR
Receivables
Upload Customer to staging from flat file
SQL*Loader
xxomera_ar_cust_cnv_load
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKuR2LSy_TsMOY8fgG5ZLcw4qq_5hw0_U0fPDyBqIoI4Vz0A-M7kvdMU2F-yHRuADMNTTuqcL83KcEXeqiGa6feSqOlMa_uVgkFj5xPOpXXF2okDWMQBA0mvmGcoEruV2z1-NGosFCrE8/s640/Untitled1.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlv0dfaat1MnZYoStHRgsnYkwyo2Rpn_19dRAI0yAn6ToyrXiqvrb3RysQmkCwnShR7ouLcFHy_s1Z2hQniy-5xDWbofdyJ8tD2k4VRKfpFgxwYeVP2Okp2IKWnDiA5hE8CJoEFak7Sc4/s640/Untitled2.jpg)
/home/PREPROD/APPS/fs1/EBSapps/appl/ar/12.0.0/bin/omera_customers.csv
Put the .csv or .ctl file in the server location in AR/bin folder
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeUTjqRLsY6NeHqcRauruBNo1_fO3y746fkCTYXR6GuE8jNSNX9sySMGFSg5WCmE5WzBpzdguatQtP_8yVKT1SVaNsoPu_CEUpnci9Pa1HWH9o4QHtzAtuwgbqcGeaPNQg31BHfeBq6ac/s640/Untitled4.jpg)
Step 5 Register the 2nd program for XXOMERA_UPLOAD_CUSTOMER to upload the interface data to the interface table
xxomera_cust_conv_pkg.pks
xxomera_cust_conv_pkg.pkb
xxomera_cust_conv_pkg.xxomera_customer_con006_prc
Omera Create Customer Conversion Program
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS7TAQtCIRSS1DXzOU0GNXPL2xR5wQh_PceCbUZj_EFSa1ofyMyamFilb3mabIBBw-hqnr_EfUBxUMO5_y_6-ykjJXl9dOaDWsyl6CJJl602MPZXd-uzbA3OAFc2XRS0vWRXj2Wznmcxc/s640/Untitled6.jpg)
Step 6 Compile the package specification
Step 7 Add to the Request Group
Add the Program Name to the Responsibility of Receivables
1 run this program
Omera Load Customer to Staging -----1 -- Upload Customer to staging from flat file
2 run this program
Omera Create Customer Conversion Program --2 --Conversion for creating customer
3 run this program
Customer interface ----3
4 run this program
Omera Update Customer --4 --Update Payment Term and Price List for Customer --
5 run this Script
Script
Then check the data in the following table
SELECT party.party_name,
party.party_id,
party.PARTY_NUMBER,
arc.ATTRIBUTE10,
party.ADDRESS1,
party.ADDRESS2,
party.ADDRESS3,
party.city,
Decode (arc.PRICE_LIST_ID,'6010','Distributors Price List-OPL') PRICE_LIST,
hcsua.SITE_USE_CODE,
party.person_first_name CONTACT_PERSON_FIRST_NAME,
party.person_last_name CONTACT_PERSON_LAST_NAME,
cont_point.PHONE_NUMBER
FROM hz_contact_points cont_point,
hz_cust_account_roles acct_role,
hz_parties party,
hz_parties rel_party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_cust_accounts role_acct,
AR_CUSTOMERS arc,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcsa
WHERE acct_role.party_id = rel.party_id
AND acct_role.role_type = 'CONTACT'
AND org_cont.party_relationship_id = rel.relationship_id
AND rel.subject_id = party.party_id
AND rel_party.party_id = rel.party_id
AND cont_point.owner_table_id(+) = rel_party.party_id
AND cont_point.primary_flag = 'Y'
AND acct_role.cust_account_id = role_acct.cust_account_id
AND role_acct.party_id = rel.object_id
AND cont_point.owner_table_name(+) = 'HZ_PARTIES'
--and ROLE_ACCT.cust_account_id = &Enter_Account_id
--and rel.object_id = &Enter_Party_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND party.status = 'A'
AND rel_party.status = 'A'
AND rel.status = 'A'
and role_acct.CUST_ACCOUNT_ID =arc.CUSTOMER_ID
AND hcsa.cust_acct_site_id = hcsua.cust_acct_site_id
AND role_acct.CUST_ACCOUNT_ID = hcsa.cust_account_id
and hcsua.SITE_USE_CODE ='BILL_TO'
;
----------------------- End of Customer Conversion --------------------
Step 1 Create table xxomera_customer_stg
xxomera_customer_stg Table Script
Create table xxomera_customer_data_populate
xxomera_customer_data_populate Table Script
Step 2 Create .ctl file xxomera_ar_cust_cnv_load.ctl
xxomera_ar_cust_cnv_load.ctl
Step 3 Create the .csv file omera_customers.csv
omera_customers.csv
Step 4 Register the .csv file with .ctl to upload the data in Table "xxomera_customer_stg"
XXOMERA_UPLOAD_CUSTOMER_LDR
XXOMERA_UPLOAD_CUSTOMER_LDR
Receivables
Upload Customer to staging from flat file
SQL*Loader
xxomera_ar_cust_cnv_load
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKuR2LSy_TsMOY8fgG5ZLcw4qq_5hw0_U0fPDyBqIoI4Vz0A-M7kvdMU2F-yHRuADMNTTuqcL83KcEXeqiGa6feSqOlMa_uVgkFj5xPOpXXF2okDWMQBA0mvmGcoEruV2z1-NGosFCrE8/s640/Untitled1.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlv0dfaat1MnZYoStHRgsnYkwyo2Rpn_19dRAI0yAn6ToyrXiqvrb3RysQmkCwnShR7ouLcFHy_s1Z2hQniy-5xDWbofdyJ8tD2k4VRKfpFgxwYeVP2Okp2IKWnDiA5hE8CJoEFak7Sc4/s640/Untitled2.jpg)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5ywWEaCUryl3NxbusB2z0tzHor47SKudgien6oyDJ7rF5P_93tfY8W7kIcnjh6O_UMIFV9yxSg5LlvwOU8QSWiSxpPXz2k7gabSuLAQVlM5ziKVUdylCiuhIlP9WdgNm6oCz7_KQ9pBc/s640/Untitled3.jpg)
/home/PREPROD/APPS/fs1/EBSapps/appl/ar/12.0.0/bin/omera_customers.csv
Put the .csv or .ctl file in the server location in AR/bin folder
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjeUTjqRLsY6NeHqcRauruBNo1_fO3y746fkCTYXR6GuE8jNSNX9sySMGFSg5WCmE5WzBpzdguatQtP_8yVKT1SVaNsoPu_CEUpnci9Pa1HWH9o4QHtzAtuwgbqcGeaPNQg31BHfeBq6ac/s640/Untitled4.jpg)
Step 5 Register the 2nd program for XXOMERA_UPLOAD_CUSTOMER to upload the interface data to the interface table
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIwMHriTESX7ltMfIjR74KX5GxdzIGMiLD6AGfqaGkAR9B7xPOUd51SNgbbYtZE-iZteuLWyvLi23y7x7GwC1dlYgjMOH3UFJDdAP426iOnoGEb0hyphenhyphenxgkZHAHkzKfzKNwuBusPCGqS1a0/s640/Untitled5.jpg)
xxomera_cust_conv_pkg.pks
xxomera_cust_conv_pkg.pkb
xxomera_cust_conv_pkg.xxomera_customer_con006_prc
Omera Create Customer Conversion Program
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhS7TAQtCIRSS1DXzOU0GNXPL2xR5wQh_PceCbUZj_EFSa1ofyMyamFilb3mabIBBw-hqnr_EfUBxUMO5_y_6-ykjJXl9dOaDWsyl6CJJl602MPZXd-uzbA3OAFc2XRS0vWRXj2Wznmcxc/s640/Untitled6.jpg)
Step 6 Compile the package specification
Step 7 Add to the Request Group
Add the Program Name to the Responsibility of Receivables
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifxumT6vs4DEQa33-C_QHpF6JzwHQG85llKU8xHwbLrrmU_FOfsAyJsITJ4ZSh3YnzC6hxIVjuWkQDw9ukZjhQPunusD7DlRLcDtcSkJMqrKwzWtvCoJEfoNhbMGCzGmcPXIWkPXRAsH8/s640/Untitled7.jpg)
1 run this program
Omera Load Customer to Staging -----1 -- Upload Customer to staging from flat file
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj3cQdD-ZIJfwHn_OpjCFvV0hYXdCgLHUSJGjLlWL09uehVbhCNPkxaoVJDaFKCMegmbRJxLqKjl_uM279JvJxrRXfb09_SiHPKYegH1DXgB6WaO4ZAtX1UHH-y4UjMi9lZ95NSvzkFZDE/s640/Untitled8.jpg)
2 run this program
Omera Create Customer Conversion Program --2 --Conversion for creating customer
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcAje1NVWNeMVP8KJAxCRL30VLADL1vQmXvLcQAsbEIBw_oDwA4KFhEKP85IsUmCJLe77fErjFlhdOge8TSDvijwKx5rM_Wk2KJxrPOyZtVKI-atjOhVhK3MBnUOqWh42qU7albeyKCyc/s640/Untitled9.jpg)
3 run this program
Customer interface ----3
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuou6DFP42G8CNwhURRHMYa5iYgufqOEh_C8Wr5uolhnJ1rZ9BOWzcFJXLh4W-z6c53rafRZp7Gi1yNGQFgEQBE9TuE5Yz4mrk0DHPr25WCn_bEOMII_HvkEQb4yQhcGqMZQEwOTQItuE/s640/Untitled10.jpg)
4 run this program
Omera Update Customer --4 --Update Payment Term and Price List for Customer --
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDKAlToxsGsDs_J3YLXNbjvY3qaA_TRDfTStSBlRgAphQB8zrW88FpMRzqDop0T-NE63ANaKfzO5Tjc4cXElI8TMbadSeQTEmuaNYltMYu21PEnJyoDBprM_pVwu7DVKL4I6d4eT9XpC4/s640/Untitled11.jpg)
5 run this Script
Script
Then check the data in the following table
SELECT party.party_name,
party.party_id,
party.PARTY_NUMBER,
arc.ATTRIBUTE10,
party.ADDRESS1,
party.ADDRESS2,
party.ADDRESS3,
party.city,
Decode (arc.PRICE_LIST_ID,'6010','Distributors Price List-OPL') PRICE_LIST,
hcsua.SITE_USE_CODE,
party.person_first_name CONTACT_PERSON_FIRST_NAME,
party.person_last_name CONTACT_PERSON_LAST_NAME,
cont_point.PHONE_NUMBER
FROM hz_contact_points cont_point,
hz_cust_account_roles acct_role,
hz_parties party,
hz_parties rel_party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_cust_accounts role_acct,
AR_CUSTOMERS arc,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcsa
WHERE acct_role.party_id = rel.party_id
AND acct_role.role_type = 'CONTACT'
AND org_cont.party_relationship_id = rel.relationship_id
AND rel.subject_id = party.party_id
AND rel_party.party_id = rel.party_id
AND cont_point.owner_table_id(+) = rel_party.party_id
AND cont_point.primary_flag = 'Y'
AND acct_role.cust_account_id = role_acct.cust_account_id
AND role_acct.party_id = rel.object_id
AND cont_point.owner_table_name(+) = 'HZ_PARTIES'
--and ROLE_ACCT.cust_account_id = &Enter_Account_id
--and rel.object_id = &Enter_Party_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
AND party.status = 'A'
AND rel_party.status = 'A'
AND rel.status = 'A'
and role_acct.CUST_ACCOUNT_ID =arc.CUSTOMER_ID
AND hcsa.cust_acct_site_id = hcsua.cust_acct_site_id
AND role_acct.CUST_ACCOUNT_ID = hcsa.cust_account_id
and hcsua.SITE_USE_CODE ='BILL_TO'
;
----------------------- End of Customer Conversion --------------------
No comments:
Post a Comment