Oracle Apps R12 Creating customers as Person

Creating customers as Person:

1. Custom table.
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.

Chk the screen shots below.

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                               

  )

;

/*if u need ship_to also, then copy the above insert code twice and change the BILL_TO to SHIP_TO  and change the sequence NEXTVAL to CURRVAL for SHIP_TO */

  --

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