This should work in Oracle Application Release 11.5.9 to Rel 12.
All Records are linked using ORIG_SYSTEM....REF Columns
All Records are linked using ORIG_SYSTEM....REF Columns
-- -- Insert record into customer interface table -- INSERT into ar.RA_CUSTOMERS_INTERFACE_ALL ( ADDRESS1 , 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 ) VALUES ( '3250 Prem Nagar' -- ADDRESS1 , 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' -- ORIG_SYSTEM_ADDRESS_REF , 'BILL_TO' -- SITE_USE_CODE , 'I' -- INSERT_UPDATE_FLAG , 'WINNIE POOH' -- CUSTOMER_NAME , 'SKM-03' -- CUSTOMER_NUMBER , 'N' -- PERSON_FLAG , 'A' -- CUSTOMER_STATUS , 'Y' -- PRIMARY_SITE_USE_FLAG , 'SAVAGE' -- CITY , 'MN' -- STATE , 55400 -- POSTAL_CODE , 'US' -- COUNTRY , 'SCOTT' -- COUNTY , '21-3456789' -- JGZZ_FISCAL_CODE , 1 -- ORG_ID , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY ) -- -- 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 ( 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'I' -- INSERT_UPDATE_FLAG , 1 -- ORG_ID , 'N' -- CREDIT_HOLD , 'DEFAULT' -- CUSTOMER_PROFILE_CLASS_NAME , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY ) -- -- Insert record into telephone interface table -- INSERT INTO ar.ra_contact_phones_int_all ( ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF , ORIG_SYSTEM_CONTACT_REF , ORIG_SYSTEM_TELEPHONE_REF , INSERT_UPDATE_FLAG , CONTACT_FIRST_NAME , CONTACT_LAST_NAME , TELEPHONE , TELEPHONE_AREA_CODE , TELEPHONE_TYPE , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY , ORG_ID , CONTACT_POINT_TYPE ) VALUES ( 'SKM_TEST_03' --ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' --ORIG_SYSTEM_ADDRESS_REF , 'SKM_TEST_03a' --ORIG_SYSTEM_CONTACT_REF , 'SKM_TEST_03a' --ORIG_SYSTEM_TELEPHONE_REF , 'I' --INSERT_UPDATE_FLAG , 'LICKEY' --CONTACT_FIRST_NAME , 'LOOSE' --CONTACT_LAST_NAME , '123-4756' --TELEPHONE , '612' --TELEPHONE_AREA_CODE , 'GEN' --TELEPHONE_TYPE , SYSDATE --LAST_UPDATE_DATE , 4670 --LAST_UPDATED_BY , SYSDATE --CREATION_DATE , 4670 --CREATED_BY , 1 --ORG_ID , 'PHONE' --CONTACT_POINT_TYPE ) -- -- Insert record into telephone interface table. -- This will be second contact for the customer -- INSERT INTO ar.ra_contact_phones_int_all ( ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF , ORIG_SYSTEM_CONTACT_REF , ORIG_SYSTEM_TELEPHONE_REF , INSERT_UPDATE_FLAG , CONTACT_FIRST_NAME , CONTACT_LAST_NAME , TELEPHONE , TELEPHONE_AREA_CODE , TELEPHONE_TYPE , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY , ORG_ID , CONTACT_POINT_TYPE ) VALUES ( 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' -- ORIG_SYSTEM_ADDRESS_REF , 'SKM_TEST_03b' -- ORIG_SYSTEM_CONTACT_REF , 'SKM_TEST_03b' -- ORIG_SYSTEM_TELEPHONE_REF , 'I' -- INSERT_UPDATE_FLAG , 'LICKEY' -- CONTACT_FIRST_NAME , 'LOOSE TWO' -- CONTACT_LAST_NAME , '123-4756' -- TELEPHONE , '512' -- TELEPHONE_AREA_CODE , 'GEN' -- TELEPHONE_TYPE , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY , 1 -- ORG_ID , 'PHONE' -- CONTACT_POINT_TYPE ) -- -- This wil add email for contact LICKEY LOSOE -- INSERT INTO ar.ra_contact_phones_int_all ( ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF , ORIG_SYSTEM_CONTACT_REF , ORIG_SYSTEM_TELEPHONE_REF , INSERT_UPDATE_FLAG , CONTACT_FIRST_NAME , CONTACT_LAST_NAME , EMAIL_ADDRESS , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY , ORG_ID , CONTACT_POINT_TYPE ) VALUES ( 'SKM_TEST_03' -- ORIG_SYSTEM_CUSTOMER_REF , 'SKM_TEST_03' -- ORIG_SYSTEM_ADDRESS_REF , 'SKM_TEST_03b' -- ORIG_SYSTEM_CONTACT_REF , 'SKM_TEST_03be' -- ORIG_SYSTEM_TELEPHONE_REF , 'I' -- INSERT_UPDATE_FLAG , 'LICKEY' -- CONTACT_FIRST_NAME , 'LOOSE TWO' -- CONTACT_LAST_NAME , 'a.b@c.com' -- EMAIL_ADDRESS , SYSDATE -- LAST_UPDATE_DATE , 4670 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 4670 -- CREATED_BY , 1 -- ORG_ID , 'EMAIL' -- CONTACT_POINT_TYPE )
Nice blog
ReplyDelete