Home » Applications » Oracle Fusion Apps & E-Business Suite » Problem with AR in NewCustomer Interface Program
Problem with AR in NewCustomer Interface Program [message #223296] Thu, 08 March 2007 06:25 Go to next message
seenujanu
Messages: 53
Registered: August 2006
Location: chennai
Member

I had a problem with importing data into Main Interface tables

i have created a Staging table
This r the columns i have selected and inserted data into this Manulley AR_ATPL_SAMPLE_STG(Staging table) from this i have done validations to Import data into Interface tables
The problem is the data is inserting into RA_CUSTOMERS_INTERFACE_ALL but the columns which belongs to this (CUSTOMER_PROFILE_CLASS_NAME,CREDIT_HOLD) RA_CUSTOMER_PROFILES_INT_ALL(table) is not (data) inserting into this.
I have done the validations for this tables RA_CUSTOMERS_INTERFACE_ALL,RA_CUSTOMER_PROFILES_INT_ALL
the data is not inserting and i have deleted the validations which belongs to the RA_CUSTOMER_PROFILES_INT_ALL ,still i am not inserting the data into profiles interface.
would u plz tell wat the reason is that.
any columns i have to include.

RA_CUSTOMERS_INTERFACE_ALL,RA_CUSTOMER_PROFILES_INT_ALL

CUSTOMER_NAME
,orig_system_customer_ref
,CUSTOMER_STATUS
,SITE_USE_CODE
,ORIG_SYSTEM_ADDRESS_REF
,ORG_ID
,PRIMARY_SITE_USE_FLAG
,LOCATION
,ADDRESS1
,ADDRESS2
,ADDRESS3
,CITY
,STATE
,COUNTRY
,CUSTOMER_CATEGORY_CODE
,CUSTOMER_CLASS_CODE
,BILL_TO_ORIG_ADDRESS_REF
,INSERT_UPDATE_FLAG
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CREATED_BY
,CREATION_DATE
,CUSTOMER_NUMBER
,CUSTOMER_PROFILE_CLASS_NAME
,CREDIT_HOLD
,STATUS
,ERROR_MSG

BEGIN
BEGIN
SELECT COUNT(LOOKUP_CODE)
INTO v_custcate
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE ='CUSTOMER_CATEGORY'
AND UPPER(lookup_code) = UPPER(r_arstg.customer_category_code);
IF v_custcate = 0 THEN
UPDATE ar_atpl_sample_stg
SET status = 'FAIL',
error_msg = 'Must Exist in Customer class Category in AR_LOOKUPS'
WHERE ROWID = r_arstg.rowid;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
v_custcate := 0;
UPDATE ar_atpl_sample_stg
SET status = 'FAIL',
error_msg = 'Must Exist in Customer class Category in AR_LOOKUPS'
WHERE ROWID = r_arstg.rowid;
COMMIT;
END;
BEGIN
INSERT INTO RA_CUSTOMERS_INTERFACE_ALL
( CUSTOMER_NAME
,ORIG_SYSTEM_CUSTOMER_REF
,CUSTOMER_STATUS
,SITE_USE_CODE
,ORIG_SYSTEM_ADDRESS_REF
,ORG_ID
,PRIMARY_SITE_USE_FLAG
,LOCATION
,ADDRESS1
,ADDRESS2
,ADDRESS3
,CITY
,STATE
,COUNTRY
,CUSTOMER_CATEGORY_CODE
,CUSTOMER_CLASS_CODE
,BILL_TO_ORIG_ADDRESS_REF
,INSERT_UPDATE_FLAG
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CREATED_BY
,CREATION_DATE
,CUSTOMER_NUMBER
)
VALUES
( r_arstg.CUSTOMER_NAME
,r_arstg.ORIG_SYSTEM_CUSTOMER_REF
,r_arstg.customer_status
,r_arstg.SITE_USE_CODE
,r_arstg.ORIG_SYSTEM_ADDRESS_REF
,r_arstg.org_id
,NVL(r_arstg.PRIMARY_SITE_USE_FLAG,'N')
,r_arstg.location
,r_arstg.ADDRESS1
,r_arstg.ADDRESS2
,r_arstg.ADDRESS3
,r_arstg.CITY
,r_arstg.STATE
,r_arstg.COUNTRY
,r_arstg.CUSTOMER_CATEGORY_CODE
,r_arstg.CUSTOMER_CLASS_CODE
,r_arstg.BILL_TO_ORIG_ADDRESS_REF
,r_arstg.INSERT_UPDATE_FLAG
,-1
,SYSDATE
,-1
,SYSDATE
,r_arstg.CUSTOMER_NUMBER
);
INSERT INTO RA_CUSTOMER_PROFILES_INT_ALL
( ORIG_SYSTEM_CUSTOMER_REF
,ORIG_SYSTEM_ADDRESS_REF
,INSERT_UPDATE_FLAG
,CUSTOMER_PROFILE_CLASS_NAME
,CREDIT_HOLD
,ORG_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CREATED_BY
,CREATION_DATE
)
VALUES
( r_arstg.ORIG_SYSTEM_CUSTOMER_REF
,r_arstg.ORIG_SYSTEM_ADDRESS_REF
,'I'
,r_arstg.CUSTOMER_PROFILE_CLASS_NAME
,'N'
,r_arstg.ORG_ID
,-1
,SYSDATE
,-1
,SYSDATE
);
COMMIT;
END;
END;
Re: Problem with AR in NewCustomer Interface Program [message #223302 is a reply to message #223296] Thu, 08 March 2007 07:15 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
is the hold check to be applied at customer level or customer site level?
Previous Topic: ORA-00001 Unique Constraint
Next Topic: Impact of AR Foreign Currency Transactions in Reporting SOB
Goto Forum:
  


Current Time: Sat Dec 03 10:16:38 CST 2016

Total time taken to generate the page: 0.07551 seconds