Oracle Apps R12 creating customer as Organization
--TRUNCATE TABLE ar.ra_customer_profiles_int_all
--TRUNCATE TABLE ar.RA_CUSTOMERS_INTERFACE_ALL
--commit
-- Insert record into customer interface table
--
DECLARE
CURSOR C1 IS
SELECT * FROM XXFUJ_CUST_UPLOAD
WHERE CUST_NUMBER = 'XXCUST_ORG1'
;
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
, LOCATION
, 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
, 'FNCE'||'-'||FUJ_CUST_S.NEXTVAL -- ORIG_SYSTEM_CUSTOMER_REF
, 'FNCE'||'-'||FUJ_CUST_ADD_S.NEXTVAL -- ORIG_SYSTEM_ADDRESS_REF
, 'BILL_TO' -- SITE_USE_CODE
,'Fujairah'
, '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
, 1770 -- 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
, LOCATION
, 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
, 'FNCE'||'-'||FUJ_CUST_S.CURRVAL -- ORIG_SYSTEM_CUSTOMER_REF
, 'FNCE'||'-'||FUJ_CUST_ADD_S.CURRVAL -- ORIG_SYSTEM_ADDRESS_REF
, 'SHIP_TO' -- SITE_USE_CODE
,'Fujairah' --Location
, '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
, 1770 -- 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
( 'FNCE'||'-'||FUJ_CUST_S.CURRVAL -- ORIG_SYSTEM_CUSTOMER_REF
, 'I' -- INSERT_UPDATE_FLAG
, 1770 -- 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;
/
2. run the 'Customer Interface' concurrent program from the Receivables Responsibility with default parameter values.
3. Check output of concprogram for the exceptions and other details.
4. once created successfully, the data from the interface tables will be deleted auto.
No comments:
Post a Comment