I'm trying to use the customer interface tables (RA_CUSTOMERS_INTERFACE_ALL) in Oracle EBS 12.1 to create a new customer with multiple addresses, and I'm not having any luck.
At this point, I can create the customer and only their BILL_TO address. However, when I add another entry to the interface table for their SHIP_TO and DELIVER_TO addresses, the interface program rejects them, with the following errors.
DELIVER_TO: BILL_TO_ORIG_ADDRESS_REF should only be defined for Ship-to Addresses. You can define accounts only for a customer Bill-To site.
SHIP_TO: You can define accounts only for a customer Bill-To site.
And here's a sample of the code I'm using to insert the records into the RA_CUSTOMERS_INTERFACE_ALL and RA_CUSTOMER_PROFILES_INT_ALL tables. In it, I'm just trying to associate the same address with all three address types, but ideally I'd also like to have the option of using different addresses.
-- BILL_TO
INSERT INTO APPS.RA_CUSTOMERS_INTERFACE_ALL (ORIG_SYSTEM_CUSTOMER_REF,
SITE_USE_CODE,
ORIG_SYSTEM_ADDRESS_REF,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
CUSTOMER_STATUS,
PRIMARY_SITE_USE_FLAG,
ORIG_SYSTEM_PARENT_REF,
LOCATION,
ADDRESS1,
CITY,
STATE,
PROVINCE,
COUNTY,
POSTAL_CODE,
COUNTRY,
ADDRESS_ATTRIBUTE1, --Customer Classification Code
ADDRESS_ATTRIBUTE2, --Sales Channel
SITE_USE_ATTRIBUTE_CATEGORY,
SITE_USE_ATTRIBUTE1,
SITE_USE_ATTRIBUTE4,
SITE_USE_ATTRIBUTE5,
CUSTOMER_CATEGORY_CODE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
ORG_ID,
GL_ID_REC,
GL_ID_REV)
VALUES (
'EVAN1', --ORIG_SYSTEM_CUSTOMER_REF
'BILL_TO', --SITE_USE_CODE
'EVAN1', --ORIG_SYSTEM_ADDRESS_REF
'I', --INSERT_UPDATE_FLAG
'EVAN TEST CUST 1', --CUSTOMER_NAME
'A', --CUSTOMER_STATUS
'Y', --PRIMARY_SITE_USE_FLAG
'PARENT1', --ORIG_SYSTEM_PARENT_REF
'EVAN TEST LOCATION', --LOCATION
'123 TEST ST', --ADDRESS1
'BEVERLY HILLS', --CITY
'CA', --STATE
'CA', --PROVINCE
'COUNTY OF LOS ANGELES', --COUNTY
'90210', --POSTAL_CODE
'US', --COUNTRY
'End User Customer', --ADDRESS_ATTRIBUTE1
'DOMESTIC NON-OEM', --ADDRESS_ATTRIBUTE2
'BILL_TO', --SITE_USE_ATTRIBUTE_CATEGORY
'1', --SITE_USE_ATTRIBUTE1
'No', --SITE_USE_ATTRIBUTE4
'No', --SITE_USE_ATTRIBUTE5
'CUSTOMER', --CUSTOMER_CATEGORY_CODE
1111, --LAST_UPDATED_BY
SYSDATE, --LAST_UPDATE_DATE
1111, --CREATED_BY
SYSDATE, --CREATION_DATE
111, --ORG_ID
1111, --GL_ID_REC
1111 --GL_ID_REV
);
-- SHIP_TO
INSERT INTO APPS.RA_CUSTOMERS_INTERFACE_ALL (ORIG_SYSTEM_CUSTOMER_REF,
SITE_USE_CODE,
ORIG_SYSTEM_ADDRESS_REF,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
CUSTOMER_STATUS,
PRIMARY_SITE_USE_FLAG,
ORIG_SYSTEM_PARENT_REF,
LOCATION,
ADDRESS1,
CITY,
STATE,
PROVINCE,
COUNTY,
POSTAL_CODE,
COUNTRY,
ADDRESS_ATTRIBUTE1, --Customer Classification Code
ADDRESS_ATTRIBUTE2, --Sales Channel
SITE_USE_ATTRIBUTE_CATEGORY,
SITE_USE_ATTRIBUTE1,
SITE_USE_ATTRIBUTE4,
SITE_USE_ATTRIBUTE5,
CUSTOMER_CATEGORY_CODE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
ORG_ID,
GL_ID_REC,
GL_ID_REV)
VALUES (
'EVAN1', --ORIG_SYSTEM_CUSTOMER_REF
'SHIP_TO', --SITE_USE_CODE
'EVAN1', --ORIG_SYSTEM_ADDRESS_REF
'I', --INSERT_UPDATE_FLAG
'EVAN TEST CUST 1', --CUSTOMER_NAME
'A', --CUSTOMER_STATUS
'Y', --PRIMARY_SITE_USE_FLAG
'PARENT1', --ORIG_SYSTEM_PARENT_REF
'EVAN TEST LOCATION', --LOCATION
'123 TEST ST', --ADDRESS1
'BEVERLY HILLS', --CITY
'CA', --STATE
'CA', --PROVINCE
'COUNTY OF LOS ANGELES', --COUNTY
'90210', --POSTAL_CODE
'US', --COUNTRY
'End User Customer', --ADDRESS_ATTRIBUTE1
'DOMESTIC NON-OEM', --ADDRESS_ATTRIBUTE2
'SHIP_TO', --SITE_USE_ATTRIBUTE_CATEGORY
'1', --SITE_USE_ATTRIBUTE1
'No', --SITE_USE_ATTRIBUTE4
'No', --SITE_USE_ATTRIBUTE5
'CUSTOMER', --CUSTOMER_CATEGORY_CODE
1111, --LAST_UPDATED_BY
SYSDATE, --LAST_UPDATE_DATE
1111, --CREATED_BY
SYSDATE, --CREATION_DATE
111, --ORG_ID
1111, --GL_ID_REC
1111 --GL_ID_REV
);
--DELIVER_TO
INSERT INTO APPS.RA_CUSTOMERS_INTERFACE_ALL (ORIG_SYSTEM_CUSTOMER_REF,
SITE_USE_CODE,
ORIG_SYSTEM_ADDRESS_REF,
INSERT_UPDATE_FLAG,
CUSTOMER_NAME,
CUSTOMER_STATUS,
PRIMARY_SITE_USE_FLAG,
ORIG_SYSTEM_PARENT_REF,
LOCATION,
ADDRESS1,
CITY,
STATE,
PROVINCE,
COUNTY,
POSTAL_CODE,
COUNTRY,
ADDRESS_ATTRIBUTE1, --Customer Classification Code
ADDRESS_ATTRIBUTE2, --Sales Channel
SITE_USE_ATTRIBUTE_CATEGORY,
SITE_USE_ATTRIBUTE1,
SITE_USE_ATTRIBUTE4,
SITE_USE_ATTRIBUTE5,
CUSTOMER_CATEGORY_CODE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
ORG_ID,
GL_ID_REC,
GL_ID_REV)
VALUES (
'EVAN1', --ORIG_SYSTEM_CUSTOMER_REF
'DELIVER_TO', --SITE_USE_CODE
'EVAN1', --ORGI_SYSTEM_ADDRESS_REF
'I', --INSERT_UPDATE_FLAG
'EVAN TEST CUST 1', --CUSTOMER_NAME
'A', --CUSTOMER_STATUS
'Y', --PRIMARY_SITE_USE_FLAG
'PARENT1', --ORIG_SYSTEM_PARENT_REF
'EVAN TEST LOCATION', --LOCATION
'123 TEST ST', --ADDRESS1
'BEVERLY HILLS', --CITY
'CA', --STATE
'CA', --PROVINCE
'COUNTY OF LOS ANGELES', --COUNTY
'90210', --POSTAL_CODE
'US', --COUNTRY
'End User Customer', --ADDRESS_ATTRIBUTE1
'DOMESTIC NON-OEM', --ADDRESS_ATTRIBUTE2
'DELIVER_TO', --SITE_USE_ATTRIBUTE_CATEGORY
'1', --SITE_USE_ATTRIBUTE1
'No', --SITE_USE_ATTRIBUTE4
'No', --SITE_USE_ATTRIBUTE5
'CUSTOMER', --CUSTOMER_CATEGORY_CODE
1111, --LAST_UPDATED_BY
SYSDATE, --LAST_UPDATE_DATE
1111, --CREATED_BY
SYSDATE, --CREATION_DATE
111, --ORG_ID
1111, --GL_ID_REC
1111 --GL_ID_REV
);
INSERT INTO APPS.RA_CUSTOMER_PROFILES_INT_ALL (
INSERT_UPDATE_FLAG,
ORIG_SYSTEM_CUSTOMER_REF,
CUSTOMER_PROFILE_CLASS_NAME,
CREDIT_HOLD,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
ORG_ID
) VALUES (
'I', --INSERT_UPDATE_FLAG
'EVAN1', --ORGI_SYSTEM_CUSTOMER_REF
'MYPROFILE', --CUSTOMER_PROFILE_CLASS_NAME
'Y', --CREDIT_HOLD
1111, --LAST_UPDATED_BY
SYSDATE, --LAST_UPDATE_DATE
SYSDATE, --CREATION_DATE
1111, --CREATED_BY
111 --ORG_ID
);
So after a lot more trial and error today, I was able to get it to work. For the non-Bill To addresses, I had to remove the following columns from the customer interface inserts:
With those fields set to NULL, the customer interface program was able to successfully attach the additional addresses to my customer account. By modifying the ORIG_SYSTEM_ADDRESS_REF field, I was able to get it to either use the same address/location for all three, or create separate addresses/location for each type as required.