Create/Upload Customers in R12 using Concurrent Program "Customer Interface"

To Upload using api : https://mogalafzal.blogspot.com/2022/09/create-customer-as-organization-api-in.html

Creating customers as Person:

1.       Custom table. XXFUJ_CUST_UPLOAD

2.       Upload using plsql tool, so Arabic will be uploaded correctly.

3.       Insert data into interface tables.

4.       Run the concurrent program.’ Customer Interface’

1.

CREATE TABLE APPS.XXFUJ_CUST_UPLOAD

(

  SNO                  NUMBER,

  CUST_NUMBER          VARCHAR2(18 BYTE),

  OU_ID                NUMBER,                 

  OU_NAME              VARCHAR2(200 BYTE), --OPTIONAL

  CUST_NAME_ENG        VARCHAR2(2000 BYTE), --OPTIONAL

  CUST_FIRST_NAME_AR   VARCHAR2(200 BYTE),

  CUST_SECOND_NAME_AR  VARCHAR2(200 BYTE), --OPTIONAL

  CUST_LAST_NAME_AR    VARCHAR2(200 BYTE),

  CUST_ADDR1           VARCHAR2(240 BYTE),

  CUST_ADDR2           VARCHAR2(240 BYTE),

  CUST_CITY            VARCHAR2(240 BYTE),

  CUST_GENDER          VARCHAR2(240 BYTE),

  FUTURE1              VARCHAR2(240 BYTE), --OPTIONAL

  FUTURE2              VARCHAR2(240 BYTE), --OPTIONAL

  FUTURE3              VARCHAR2(240 BYTE), --OPTIONAL

  FUTURE4              VARCHAR2(240 BYTE), --OPTIONAL

  FUTURE5              VARCHAR2(240 BYTE   --OPTIONAL

)

2.

Plsql developer>tools>ODBC Importer

Data from ODBC Tab>

Connection:

Excel files –if you don’t see Excel Files then you need to chk other version of plsql dev.

Apps

Apps

>connect

>select the excel file

>Table/Query : select the sheet

Data to Oracle >Tab

General:

Apps

XXFUJ_CUST_UPLOAD –table

Please be sure that excel column names are similar to the table column names, else you can map here.

Click Import.

Pls chk below for screenshots.

3. Interface tables:

1.  ar.RA_CUSTOMERS_INTERFACE_ALL

2.       ar.ra_customer_profiles_int_all

I am uploading only customer info and address, so using only above 2 interfaces, for other info to upload, chk the other interface tables.

Creating sequences:

1.  CREATE SEQUENCE APPS.FUJ_CUST_S

  START WITH 29595

  MAXVALUE 9999999

  MINVALUE 1

  NOCYCLE

  NOCACHE

  NOORDER;

2.  CREATE SEQUENCE APPS.FUJ_CUST_ADD_S

  START WITH 29595

  MAXVALUE 99999999

  MINVALUE 1

  NOCYCLE

  NOCACHE

  NOORDER;

Insert custom table data into interface tables:

--TRUNCATE TABLE ar.ra_customer_profiles_int_all

--

   -- Insert record into customer interface table

   --

  

   DECLARE

   CURSOR C1 IS

  

   SELECT * FROM XXFUJ_CUST_UPLOAD

   WHERE  SNO BETWEEN 29594 AND 300000;

  

  

   BEGIN

   FOR I IN C1 LOOP

   INSERT into ar.RA_CUSTOMERS_INTERFACE_ALL

   ( ADDRESS1   

   , ADDRESS2                                                        

   , ORIG_SYSTEM_CUSTOMER_REF              

   , ORIG_SYSTEM_ADDRESS_REF               

   , SITE_USE_CODE                          

   , INSERT_UPDATE_FLAG                    

   , CUSTOMER_NAME                         

   , CUSTOMER_NUMBER                       

   , PERSON_FLAG                                                

   , CUSTOMER_STATUS                       

   , PRIMARY_SITE_USE_FLAG                 

   , CITY                                  

  -- , STATE                                                               

  -- , POSTAL_CODE                           

   , COUNTRY                               

   --, COUNTY                 

   --, JGZZ_FISCAL_CODE                                                          

   , ORG_ID                                

   , LAST_UPDATE_DATE      

   , LAST_UPDATED_BY        

   , CREATION_DATE         

   , CREATED_BY        

   , PERSON_FIRST_NAME

   , PERSON_LAST_NAME                     

   )

   VALUES

   ( I.CUST_ADDR1  -- ADDRESS1                                                            

   , I.CUST_ADDR2  -- ADDRESS2

   , 'FUJ_CUST'||'-'||FUJ_CUST_S.NEXTVAL      -- ORIG_SYSTEM_CUSTOMER_REF              

   , 'FUJ_CUST'||'-'||FUJ_CUST_ADD_S.NEXTVAL     -- ORIG_SYSTEM_ADDRESS_REF               

   , 'BILL_TO'          -- SITE_USE_CODE                          

   , 'I'                -- INSERT_UPDATE_FLAG                    

   , I.CUST_FIRST_NAME_AR      -- CUSTOMER_NAME                         

   , I.CUST_NUMBER           -- CUSTOMER_NUMBER                       

   , 'Y'                -- PERSON_FLAG                                   

   , 'A'                -- CUSTOMER_STATUS                       

   , 'Y'                -- PRIMARY_SITE_USE_FLAG                 

   , I.CUST_CITY           -- CITY                                  

  -- , 'MN'               -- STATE                                                  

  -- , 55400              -- POSTAL_CODE                           

   , 'AE'               -- COUNTRY                               

   --, 'SCOTT'            -- COUNTY                  

   --, '21-3456789'       -- JGZZ_FISCAL_CODE                                    

   , 145                  -- ORG_ID                                

   , SYSDATE            -- LAST_UPDATE_DATE      

   , 2605               -- LAST_UPDATED_BY       

   , SYSDATE            -- CREATION_DATE         

   , 2605               -- CREATED_BY

   , I.CUST_FIRST_NAME_AR

   , I.CUST_LAST_NAME_AR                              

   )

;

   --

   -- Insert records inot profile interface table

   --

   INSERT into ar.ra_customer_profiles_int_all

   ( ORIG_SYSTEM_CUSTOMER_REF            

   , INSERT_UPDATE_FLAG            

   , ORG_ID         

   , CREDIT_HOLD                      

   , CUSTOMER_PROFILE_CLASS_NAME

   , LAST_UPDATE_DATE       

   , LAST_UPDATED_BY       

   , CREATION_DATE         

   , CREATED_BY                              

   )

   VALUES

   ( 'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL  -- ORIG_SYSTEM_CUSTOMER_REF            

   , 'I'            -- INSERT_UPDATE_FLAG            

   , 145              -- ORG_ID         

   , 'N'            -- CREDIT_HOLD                      

   , 'DEFAULT'      -- CUSTOMER_PROFILE_CLASS_NAME

   , SYSDATE        -- LAST_UPDATE_DATE              

   , 2605           -- LAST_UPDATED_BY               

   , SYSDATE        -- CREATION_DATE                 

   , 2605           -- CREATED_BY                                                

   )

;

END LOOP;

COMMIT;

END;

/

4.. Running the concurrent program:” Customer Interface”

Errors and solutions:

Check the log file of the Concurrent program for details of errors and update the interface tables accordingly.

Once uploaded data from the interface tables, will be deleted automatically from the interface tables.

So the records in the interface tables are not-uploaded data - data with error for sure.