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;
/
No comments:
Post a Comment