How to Customer Conversion program to Upload step by step using API in Oracle Apps (EBS)

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






 




/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           



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       

 




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 --------------------