Search This Blog

Thursday, May 25, 2017

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;

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

C:\Users\egov\Desktop\customer7.png

C:\Users\egov\Desktop\customer8.png

C:\Users\egov\Desktop\customer1.png

C:\Users\egov\Desktop\customer2.png

C:\Users\egov\Desktop\customer3.png

C:\Users\egov\Desktop\customer4.png

C:\Users\egov\Desktop\customer5.png

C:\Users\egov\Desktop\customer6.png



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.


No comments:

Post a Comment