This should work in Oracle Application Release 11.5.9 to Rel 12.
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
)
Posts
No comments:
Post a Comment