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;
|
] 
Blog
Search
Help
Members
Register
Login
Home
