Oracle Apps KT: May 2017

Thursday, May 25, 2017

Oracle Apps R12 Creating customers as Organization

Check the http://mogalafzal.blogspot.ae/2017/05/oracle-apps-r12-creating-customers-as.html for custom table and conc program and issues related.

For the API use the below script, its different from above link.

DECLARE
CURSOR C1 IS
SELECT * FROM XXFUJ_CUST_UPLOAD
WHERE CUST_NUMBER <> 'CUST1003';
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
    ,LOCATION
   , 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_NAME_ENG      -- CUSTOMER_NAME
   , I.CUST_NUMBER           -- CUSTOMER_NUMBER
   , 'N'                -- 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
   , 'Fujairah'
   , 1730                  -- 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 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
    ,LOCATION
   , 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.CURRVAL      -- ORIG_SYSTEM_CUSTOMER_REF
   , 'FUJ_CUST'||'-'||FUJ_CUST_ADD_S.CURRVAL   -- ORIG_SYSTEM_ADDRESS_REF
   , 'SHIP_TO'          -- SITE_USE_CODE
   , 'I'                -- INSERT_UPDATE_FLAG
   , I.CUST_NAME_ENG      -- CUSTOMER_NAME
   , I.CUST_NUMBER           -- CUSTOMER_NUMBER
   , 'N'                -- PERSON_FLAG
   , 'A'                -- CUSTOMER_STATUS
   , 'N'                -- PRIMARY_SITE_USE_FLAG
-- , I.CUST_CITY           -- CITY
-- , 'MN'               -- STATE
-- , 55400              -- POSTAL_CODE
   , 'AE'               -- COUNTRY
   --, 'SCOTT'            -- COUNTY
   --, '21-3456789'       -- JGZZ_FISCAL_CODE
   , 'Fujairah'
   , 1730                  -- 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
   , 1730              -- ORG_ID
   , 'N'            -- CREDIT_HOLD
   , 'DEFAULT'      -- CUSTOMER_PROFILE_CLASS_NAME
   , SYSDATE        -- LAST_UPDATE_DATE
   , 2605           -- LAST_UPDATED_BY
   , SYSDATE        -- CREATION_DATE
   , 2605           -- CREATED_BY
   )
;
/*
INSERT
INTO
ra_contact_phones_int_all
(
orig_system_contact_ref,
orig_system_telephone_ref,
orig_system_customer_ref,
orig_system_address_ref,
insert_update_flag,
contact_first_name,
contact_last_name,
contact_title,
contact_job_title,
telephone,
--telephone_extension,
telephone_type,
telephone_area_code,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login,
sex_code,
salutation,
email_address,
mail_stop,
contact_key,
org_id
--contact_job_title_code,
,contact_point_type
)
VALUES
(
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_contact_ref,
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_telephone_ref,
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_customer_ref,
'FUJ_CUST'||'-'||FUJ_CUST_S.CURRVAL,--->:orig_system_address_ref,
'I',
'CUSTOMERFirstNAME0', --->:contact_first_name,
'CUSTOMERlastNAME0', --->:contact_last_name,
NULL, --->:contact_title,
NULL, --->:contact_job_title,
40986341, --->:telephone,
--6341, --->:telephone_extension,
'MOBILE', --->:telephone_type,
971, --->:telephone_area_code,
2605,
SYSDATE,
2605,
SYSDATE,
2605,
'Male', --->:sex_code,
NULL, --->:salutation,
'vphilip@fexico.ae',--->:email_address,
NULL, --->:mail_stop,
NULL, --->:contact_key,
1708
--Customer, --->:contact_job_title_code,
,'PHONE' --->:contact_point_type
)

;
*/
--commit;

--->:====================================================================
END LOOP;
COMMIT;
END;
/

Oracle Apps R12 Creating customers as Person

Creating customers as Person:

Custom table.
Upload using plsql tool, so Arabic will be uploaded correctly.
Insert data into interface tables.
Run the concurrent program.’ Customer Interface’

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

)

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

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

ar.RA_CUSTOMERS_INTERFACE_ALL
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:

CREATE SEQUENCE APPS.FUJ_CUST_S

START WITH 29595

MAXVALUE 9999999

MINVALUE 1

NOCYCLE

NOCACHE

NOORDER;

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.

Search This Blog

Thursday, May 25, 2017

Oracle Apps R12 Creating customers as Organization

Oracle Apps R12 Creating customers as Person