CREATE OR REPLACE PACKAGE APPS.CUSTOMER_CONVERSION_PKG AS
PROCEDURE CUSTOMER_CONVERSION_PROC(ERRBUF OUT VARCHAR,RETCODE OUT NUMBER, P_BATCH VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.CUSTOMER_CONVERSION_PKG AS
PROCEDURE CUSTOMER_CONVERSION_PROC(ERRBUF OUT VARCHAR,RETCODE OUT NUMBER, P_BATCH VARCHAR2)
IS
CURSOR location_c1 (P_BATCH VARCHAR2) IS
SELECT UNIQUE LOCATION FROM XX_CUSTOMERS_STAGING_TABLE
WHERE validation_status IS NULL
AND BATCH_NAME = P_BATCH;
CURSOR orig_addr_sys_ref_c1 (p_location VARCHAR2) IS
SELECT ORIG_SYSTEM_REFERENCE1 FROM XX_CUSTOMERS_STAGING_TABLE
WHERE validation_status IS NULL
AND LOCATION = p_location
FOR UPDATE OF ORIG_SYSTEM_REFERENCE1;
CURSOR customer_master_c1 (P_BATCH VARCHAR2) IS
SELECT DISTINCT
BATCH_NAME
,CUSTOMER_NAME
,ORIG_SYSTEM_REFERENCE
,CUSTOMER_TYPE
,CUSTOMER_PROFILE_CLASS
,CUSTOMER_CATEGORY_CODE
,CUSTOMER_CLASS_CODE
,PAYMENT_TERM
--,ORG_ID
,OPERATING_UNIT
,ADDRESS1
,ADDRESS2
,ADDRESS3
,POSTAL_CODE
,CITY
,COUNTRY
,SITE_USE_CODE
,PRIMARY_FLAG
,GL_ID_REC
,GL_ID_REV
,LOCATION
,ORIG_SYSTEM_REFERENCE1
,ATTRIBUTE1
--,TITLE
--,FIRST_NAME
--,LAST_NAME
--,JOB_TITLE
--,EMAIL_ADDRESS
--,AREA_CODE
--,PHONE_NUMBER
FROM
XX_CUSTOMERS_STAGING_TABLE
WHERE validation_status IS NULL
AND BATCH_NAME = P_BATCH
ORDER BY BATCH_NAME, CUSTOMER_NAME, SITE_USE_CODE;
CURSOR customer_contact_c1 (p_orig_sys_cust_ref VARCHAR2, p_orig_sys_addr_ref VARCHAR2) IS
SELECT ORIG_SYSTEM_REFERENCE
,ORIG_SYSTEM_REFERENCE1
,TITLE
,FIRST_NAME
,LAST_NAME
,JOB_TITLE
,EMAIL_ADDRESS
,AREA_CODE
,PHONE_NUMBER
FROM XX_CUSTOMERS_STAGING_TABLE
WHERE validation_status IS NULL
AND ORIG_SYSTEM_REFERENCE = p_orig_sys_cust_ref
AND ORIG_SYSTEM_REFERENCE1 = p_orig_sys_addr_ref;
---WHERE DECODE(LENGTH(validation_status),2,REPLACE( validation_status,CHR(13),''),VALIDATION_STATUS) IN ('U','E');
-- FOR UPDATE OF VALIDATION_STATUS;
x_billship_valcount NUMBER ;
x_billship_valcount1 NUMBER ;
x_profile_count NUMBER ;
x_opcount NUMBER ;
x_mpcount NUMBER ;
x_fxcount NUMBER ;
x_err_msg VARCHAR2(4000);
x_error_flag VARCHAR2(1) :=' ';
X_COUNT_PROFILE_CLASSES NUMBER;
x_country NUMBER;
x_TITLE NUMBER;
x_org_id hr_operating_units.organization_id%TYPE ;
x_PAYMENT_METHOD_NAME NUMBER;
x_category_count NUMBER;
x_class_count NUMBER;
x_type_count NUMBER;
x_debug_point NUMBER;
X_PAY_TERM VARCHAR2(50);
x_GL_ID_REC VARCHAR2(50);
x_GL_ID_REV VARCHAR2(50);
x_INT_DISTCODE VARCHAR2(50);
X_CUSTOMER_TYPE VARCHAR2(20);
CUST_ADDR_ORIG_REF NUMBER;
CUST_CONT_ORIG_REF NUMBER;
CUST_PHONE_ORIG_REF NUMBER;
BEGIN
-- UPDATE XX_CUSTOMERS_STAGING_TABLE SET LAST_NAME = REPLACE(LAST_NAME, CHR(13), '')
-- WHERE validation_status IS NULL;
-- COMMIT;
/************************************************************************************************************
-- Populate Orig Address Ref in Staging table
FOR vc2 IN location_c1 (P_BATCH) LOOP
Select CUST_ADDR_ORIG_REFS_S.NEXTVAL
Into CUST_ADDR_ORIG_REF
From Dual;
FOR vc3 IN orig_addr_sys_ref_c1 (vc2.LOCATION) LOOP
UPDATE ZNAR_CUSTOMERS_STAGING_TABLE
SET ORIG_SYSTEM_REFERENCE1 = TO_CHAR(CUST_ADDR_ORIG_REF)
WHERE CURRENT OF orig_addr_sys_ref_c1;
END LOOP;
END LOOP;
COMMIT;
************************************************************************************************************/
FOR vc1 IN customer_master_c1 (P_BATCH) LOOP
BEGIN
---- x_PAYMENT_METHOD_NAME :=0;
x_billship_valcount :=0;
x_billship_valcount1 :=0;
x_profile_count :=0;
x_opcount :=0 ;
x_mpcount :=0 ;
x_fxcount :=0 ;
x_err_msg :='';
x_error_flag :='N';
X_COUNT_PROFILE_CLASSES := 0;
x_country :=0;
x_TITLE :=0;
x_ORG_ID :=0;
x_category_count :=0;
x_class_count :=0;
x_GL_ID_REC := NULL;
x_GL_ID_REV := NULL;
x_INT_DISTCODE :=0;
x_debug_point:=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before CUSTOMER_NAME validation '|| ' at debug point : ' ||x_debug_point);
-- getting the value for x_org_id
BEGIN
SELECT ORGANIZATION_ID
INTO X_ORG_ID
FROM HR_OPERATING_UNITS
WHERE NAME = VC1.OPERATING_UNIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'ORG_ID does not existing for the operating unit # '||VC1.OPERATING_UNIT||' CUSTOMER_NAME - '||vc1.CUSTOMER_NAME||'-'||SQLCODE||' '||SQLERRM);
END;
-- VALIDATION FOR CUSTOMER_NAME
BEGIN
IF vc1.CUSTOMER_NAME IS NULL THEN
x_err_msg := x_err_msg|| ' - ' || 'CUSTOMER_NAME cannot be NULL for Batch # '||vc1.BATCH_NAME||' CUSTOMER_NAME - '||vc1.CUSTOMER_NAME;
x_error_flag := 'Y';
END IF ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating CUSTOMER_NAME for Batch # '||vc1.BATCH_NAME||' CUSTOMER_NAME - '||vc1.CUSTOMER_NAME||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR CUSTOMER_NAME ENDS--
x_debug_point:=2;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ORIG_SYSTEM_REFERENCE validation '|| ' at debug point : ' ||x_debug_point ||'x_error_flag '||x_error_flag);
-- VALIDATION FOR ORIG_SYSTEM_REFERENCE STARTS
BEGIN
IF vc1.ORIG_SYSTEM_REFERENCE IS NULL THEN
x_err_msg := x_err_msg|| ' - ' || 'ORIG_SYSTEM_REFERENCE cannot be NULL Batch # '||vc1.BATCH_NAME||' ORIGINAL SYSTEM REFERENCE - '||vc1.ORIG_SYSTEM_REFERENCE;
x_error_flag := 'Y';
END IF ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating ORIG_SYSTEM_REFERENCE Batch # '||vc1.BATCH_NAME||' ORIGINAL SYSTEM REFERENCE - '||vc1.ORIG_SYSTEM_REFERENCE||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR ORIG_SYSTEM_REFERENCE OVER--
x_debug_point:=3;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before customer_type validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
-- Validation for Customer Type starts--
BEGIN
/**********************************************************************************************************
IF vc1.customer_type IS NOT NULL THEN
IF UPPER(vc1.customer_type) NOT IN ('INTERNAL','EXTERNAL') THEN
x_err_msg := x_err_msg || ' - '|| 'Customer Type should be INTERNAL(I) or EXTERNAL(R) for Batch # '||vc1.BATCH_NAME||' customer_type - '||vc1.customer_type;
x_error_flag := 'Y';
END IF;
ELSE
x_err_msg := x_err_msg || ' - '|| 'Customer Type cannot be NULL for Batch # '||vc1.BATCH_NAME||' customer_type - '||vc1.customer_type;
x_error_flag := 'Y';
END IF;
**********************************************************************************************************/
IF vc1.CUSTOMER_TYPE IS NULL THEN
x_err_msg := x_err_msg|| ' - ' ||'CUSTOMER_TYPE cannot be NULL for Batch # '||vc1.BATCH_NAME||' CUSTOMER_TYPE - '||vc1.CUSTOMER_TYPE;
x_error_flag := 'Y';
ELSE
SELECT COUNT(1)
INTO x_type_count
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE= 'CUSTOMER_TYPE'
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE,TRUNC(SYSDATE)) AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
AND LOOKUP_CODE = vc1.CUSTOMER_TYPE;
IF x_type_count = 0 THEN
x_err_msg := x_err_msg|| ' - ' ||'CUSTOMER_TYPE is invalid for Batch # '||vc1.BATCH_NAME||' CUSTOMER_TYPE - '||vc1.CUSTOMER_TYPE;
x_error_flag := 'Y';
END IF;
END IF ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in Validating Customer Type for Batch # '||vc1.BATCH_NAME||' customer_type - '||vc1.customer_type||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- Validation for Customer Type over--
-- VALIDATION FOR PROFILE CLASS NAME STARTS --
BEGIN
IF vc1.CUSTOMER_PROFILE_CLASS IS NULL THEN
x_err_msg := x_err_msg|| ' - ' || 'CUSTOMER_PROFILE_CLASS cannot be NULL for Batch # '||vc1.BATCH_NAME||' CUSTOMER_PROFILE_CLASS - '||vc1.CUSTOMER_PROFILE_CLASS;
x_error_flag:='Y';
ELSE
SELECT COUNT(1)
INTO X_COUNT_PROFILE_CLASSES
FROM HZ_CUST_PROFILE_CLASSES
WHERE NAME = vc1.CUSTOMER_PROFILE_CLASS
AND status = 'A';
IF X_COUNT_PROFILE_CLASSES = 0 THEN
x_err_msg := x_err_msg|| ' - ' || 'Customer Profile Class Name is not valid for Batch # '||vc1.BATCH_NAME||' CUSTOMER_PROFILE_CLASS - '||vc1.CUSTOMER_PROFILE_CLASS;
x_error_flag:='Y';
END IF;
END IF ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating Customer Profile Class Name for Batch # '||vc1.BATCH_NAME||' CUSTOMER_PROFILE_CLASS - '||vc1.CUSTOMER_PROFILE_CLASS||'-'||SQLCODE||' '||SQLERRM);
x_error_flag :='Y';
END;
-- VALIDATION FOR PROFILE CLASS NAME OVER --
x_debug_point:=4;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before CUSTOMER_CATEGORY_CODE validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
-- VALIDATION FOR CUSTOMER_CATEGORY STARTS
BEGIN
IF vc1.CUSTOMER_CATEGORY_CODE IS NOT NULL THEN
-- x_err_msg := x_err_msg|| ' - ' ||'CUSTOMER_CATEGORY_CODE cannot be NULL for Batch # '||vc1.BATCH_NAME||' CUSTOMER_CATEGORY_CODE - '||vc1.CUSTOMER_CATEGORY_CODE;
-- x_error_flag := 'Y';
-- ELSE
SELECT COUNT(1)
INTO x_category_count
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE= 'CUSTOMER_CATEGORY'
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE,TRUNC(SYSDATE)) AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
AND LOOKUP_CODE = vc1.CUSTOMER_CATEGORY_CODE;
IF x_category_count = 0 THEN
x_err_msg := x_err_msg|| ' - ' ||'CUSTOMER_CATEGORY_CODE is invalid for Batch # '||vc1.BATCH_NAME||' CUSTOMER_CATEGORY_CODE - '||vc1.CUSTOMER_CATEGORY_CODE;
x_error_flag := 'Y';
END IF;
END IF ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception In validating CUSTOMER_CATEGORY_CDOE for Batch # '||vc1.BATCH_NAME||' CUSTOMER_CATEGORY_CODE - '||vc1.CUSTOMER_CATEGORY_CODE||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR CUSTOMER_CATEGORY ENDS
-- VALIDATION FOR CUSTOMER_CLASS STARTS
BEGIN
IF vc1.CUSTOMER_CLASS_CODE IS NOT NULL THEN
-- x_err_msg := x_err_msg|| ' - ' ||'CUSTOMER_CLASS_CODE cannot be NULL for Batch # '||vc1.BATCH_NAME||' CUSTOMER_CLASS_CODE - '||vc1.CUSTOMER_CLASS_CODE;
-- x_error_flag := 'Y';
-- ELSE
SELECT COUNT(1)
INTO x_category_count
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE= 'CUSTOMER CLASS'
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE,TRUNC(SYSDATE)) AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
AND LOOKUP_CODE = vc1.CUSTOMER_CLASS_CODE;
IF x_category_count = 0 THEN
x_err_msg := x_err_msg|| ' - ' ||'CUSTOMER_CLASS_CODE is invalid for Batch # '||vc1.BATCH_NAME||' CUSTOMER_CLASS_CODE - '||vc1.CUSTOMER_CLASS_CODE;
x_error_flag := 'Y';
END IF;
END IF ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception In validating CUSTOMER_CLASS_CODE for Batch # '||vc1.BATCH_NAME||' CUSTOMER_CLASS_CODE - '||vc1.CUSTOMER_CLASS_CODE||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR CUSTOMER_CLASS ENDS
x_debug_point:=5;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before PAYMENT_TERM validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
-- VALIDATION FOR PAYMENT_TERM STARTS
BEGIN
IF vc1.PAYMENT_TERM IS NOT NULL THEN
-- x_err_msg := x_err_msg|| ' - ' ||'PAYMENT_TERM cannot be NULL for Batch # '||vc1.BATCH_NAME||' PAYMENT_TERM - '||vc1.PAYMENT_TERM;
-- x_error_flag := 'Y';
-- ELSE
SELECT COUNT(1)
INTO x_pay_term
FROM ra_terms
WHERE ---LANGUAGE= 'US' AND
TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE,TRUNC(SYSDATE)) AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
AND NAME = vc1.PAYMENT_TERM;
IF x_pay_term = 0 THEN
x_err_msg := x_err_msg|| ' - ' ||'PAYMENT_TERM is invalid for Batch # '||vc1.BATCH_NAME||' PAYMENT_TERM - '||vc1.PAYMENT_TERM;
x_error_flag := 'Y';
END IF;
END IF ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception In validating PAYMENT_TERM for Batch # '||vc1.BATCH_NAME||' PAYMENT_TERM - '||vc1.PAYMENT_TERM||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR PAYMENT_TERM ENDS
x_debug_point:=6;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ORG_ID validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
/****************************************************************************************************************************************
-- VALIDATION FOR OPERATING UNIT STARTS
BEGIN
IF x_ORG_ID IS NOT NULL THEN
SELECT COUNT(1) INTO x_org_id FROM hr_operating_units WHERE ORGANIZATION_ID = x_org_id ;
IF x_ORG_ID =0 THEN
x_err_msg := x_err_msg|| ' - ' || 'ORG_ID value is invalid for Batch # '||vc1.BATCH_NAME||'ORG_ID - '||x_org_id;
x_error_flag := 'Y';
END IF;
ELSE
x_err_msg := x_err_msg|| ' - ' || 'ORG_ID cannot be null for Batch # '||vc1.BATCH_NAME||'ORG_ID - '||x_org_id;
x_error_flag := 'Y';
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating ORG_ID operating unit for Batch # '||vc1.BATCH_NAME||'ORG_ID - '||x_org_id||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR OPERATING UNIT ENDS --
****************************************************************************************************************************************/
x_debug_point:=7;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before COUNTRY validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
-- VALIDATION FOR COUNTRY STARTS
BEGIN
IF vc1.country IS NOT NULL THEN
SELECT COUNT(1)
INTO x_Country FROM fnd_territories
WHERE territory_code = vc1.country
AND Obsolete_flag = 'N';
IF x_country = 0 THEN
x_err_msg := x_err_msg|| ' - ' ||'Country is not Valid for Batch # '||vc1.BATCH_NAME||' COUNTRY - '||vc1.COUNTRY;
x_error_flag := 'Y';
END IF;
ELSE
x_err_msg := x_err_msg|| ' - ' ||'Country cannot be null for Batch # '||vc1.BATCH_NAME||' COUNTRY - '||vc1.COUNTRY;
x_error_flag := 'Y';
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception In Validating country for Batch # '||vc1.BATCH_NAME||' COUNTRY - '||vc1.COUNTRY||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
--VALIDATION FOR COUNTRY OVER
-- x_debug_point:=8;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'Before TITLE validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
x_debug_point:=9;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before SITE_USE_CODE validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
-- VALIDATION FOR CUSTOMER SITE STARTS
BEGIN
x_billship_valcount :=0;
x_billship_valcount1 :=0;
IF UPPER(vc1.SITE_USE_CODE) = 'SHIP TO' OR UPPER(vc1.SITE_USE_CODE) = 'SHIP_TO' THEN
SELECT COUNT(1) INTO x_billship_valcount
FROM ZNAR_CUSTOMERS_STAGING_TABLE WHERE ORIG_SYSTEM_REFERENCE = vc1.ORIG_SYSTEM_REFERENCE-------vc1.ORIG_SYSTEM_REFERENCE
AND (SITE_USE_CODE = 'BILL TO' OR SITE_USE_CODE = 'BILL_TO') ;
SELECT COUNT(1) INTO x_billship_valcount1
FROM HZ_CUST_ACCOUNTS HCA , HZ_CUST_ACCT_SITES_ALL HCAS ,HZ_CUST_SITE_USES_ALL HCSU
WHERE HCA.ORIG_SYSTEM_REFERENCE = vc1.ORIG_SYSTEM_REFERENCE
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND (HCSU.SITE_USE_CODE = 'BILL TO' OR HCSU.SITE_USE_CODE = 'BILL_TO') ;
IF x_billship_valcount = 0 AND x_billship_valcount1 =0 THEN
x_err_msg := x_err_msg|| ' - ' ||'Cannot load ship to site without bill to site for '||vc1.BATCH_NAME||' SITE_USE_CODE - '||vc1.SITE_USE_CODE;
x_error_flag := 'Y';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception In Site Validation Batch # '||vc1.BATCH_NAME||' SITE_USE_CODE - '||vc1.SITE_USE_CODE||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR CUSTOMER SITE ENDS
x_debug_point:=10;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before GL_ID_REC validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
/***********************************************************************************************************************************************************************
-- VALIDATION FOR GL_ID_REC STARTS
BEGIN
IF vc1.GL_ID_REC IS NOT NULL THEN
SELECT COUNT(1)
INTO x_GL_ID_REC
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = vc1.GL_ID_REC
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND ACCOUNT_TYPE = 'A'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
IF x_GL_ID_REC = 0 THEN
x_err_msg := x_err_msg|| ' - ' || 'GL_ID_REC is invalid for Batch # '||vc1.batch_name||' GL_ID_REC - '||vc1.GL_ID_REC;
x_error_flag := 'Y';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating GL_ID_REC Code Batch # '||vc1.BATCH_NAME||' GL_ID_REC - '||vc1.GL_ID_REC||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR GL_ID_REC ENDS
x_debug_point:=11;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before GL_ID_REV validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
-- VALIDATION FOR GL_ID_REV STARTS
BEGIN
IF vc1.GL_ID_REV IS NOT NULL THEN
SELECT COUNT(1)
INTO x_GL_ID_REV
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = vc1.GL_ID_REV
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND ACCOUNT_TYPE = 'R'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
IF x_GL_ID_REV = 0 THEN
x_err_msg := x_err_msg|| ' - ' || 'GL_ID_REV is invalid for Batch # '||vc1.batch_name||' GL_ID_REV - '||vc1.GL_ID_REV;
x_error_flag := 'Y';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating GL_ID_REV Code Batch # '||vc1.BATCH_NAME||' GL_ID_REV - '||vc1.GL_ID_REV||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
***********************************************************************************************************************************************************************/
-- VALIDATION FOR GL_ID_REV ENDS
x_debug_point:=12;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ATTRIBUTE1 validation '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
/*************************************************************************************************************************************************************
-- VALIDATION FOR ATTRIBUTE1 STARTS
BEGIN
IF vc1.attribute1 IS NOT NULL THEN
SELECT COUNT(1) INTO x_INT_DISTCODE
FROM HZ_CUST_ACCOUNTS
WHERE attribute1 = vc1.attribute1;
IF x_INT_DISTCODE = 0 THEN
x_err_msg := x_err_msg|| ' - ' || 'Internal Distrbuter Code is invalid for Batch # '||vc1.batch_name||' Attribute1 - '||vc1.attribute1;
x_error_flag := 'Y';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating Internal Distrbuter Code Batch # '||vc1.BATCH_NAME||' Attribute1 - '||vc1.attribute1||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR ATTRIBUTE1 ENDS
*************************************************************************************************************************************************************/
----- Fecth the value for GL_ID_REC
IF vc1.GL_ID_REC IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_GL_ID_REC
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = vc1.GL_ID_REC
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND ACCOUNT_TYPE = 'A'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CODE_COMBINATION_ID does not exists for the given GL_ID_REC Code Batch # '||vc1.BATCH_NAME||' GL_ID_REC - '||vc1.GL_ID_REC||'-'||SQLCODE||' '||SQLERRM);
END;
END IF;
--- value for GL_ID_REC fetch end here
----- Fecth the value for GL_ID_REV
IF vc1.GL_ID_REV IS NOT NULL THEN
BEGIN
SELECT CODE_COMBINATION_ID
INTO X_GL_ID_REV
FROM gl_code_combinations
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'|| SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 = vc1.GL_ID_REV
AND enabled_flag = 'Y'
AND summary_flag = 'N'
AND ACCOUNT_TYPE = 'R'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active,SYSDATE));
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CODE_COMBINATION_ID does not exists for the given GL_ID_REV Code Batch # '||vc1.BATCH_NAME||' GL_ID_REV - '||vc1.GL_ID_REV||'-'||SQLCODE||' '||SQLERRM);
END;
END IF;
--- value for GL_ID_REV fetch end here
----- Fecth the value for CUTOMER_TYPE
BEGIN
select LOOKUP_CODE
INTO X_CUSTOMER_TYPE
from ar_lookups
-- where UPPER(MEANING) = UPPER(VC1.CUSTOMER_TYPE)
where LOOKUP_CODE = UPPER(VC1.CUSTOMER_TYPE)
AND LOOKUP_TYPE = 'CUSTOMER_TYPE'
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE,TRUNC(SYSDATE)) AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE));
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'LOOKUP_CODE does not exists for the given CUSTOMER_TYPE Code Batch # '||vc1.BATCH_NAME||'CUSTOMER_TYPE- '||VC1.CUSTOMER_TYPE||'-'||SQLCODE||' '||SQLERRM);
END;
--- value for CUTOMER_TYPE fetch end here
x_debug_point:= 20;
FND_FILE.PUT_LINE(FND_FILE.LOG,'BEFORE checking the x_error_flag condition '|| ' at debug point : ' ||x_debug_point||'x_error_flag '||x_error_flag);
IF NVL(x_error_flag,'N') <>'Y' THEN
x_debug_point:= 21;
FND_FILE.PUT_LINE(FND_FILE.LOG,'After checking the x_error_flag condition '|| ' at debug point : ' ||x_debug_point);
IF vc1.SITE_USE_CODE = UPPER('BILL TO') OR vc1.SITE_USE_CODE = UPPER('BILL_TO') THEN
x_debug_point:=22;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before insert into ra_customers_interface_all '|| ' at debug point : ' ||x_debug_point);
INSERT INTO RA_CUSTOMERS_INTERFACE_ALL
(CUSTOMER_NAME,
ORIG_SYSTEM_CUSTOMER_REF,
CUSTOMER_TYPE,
CUSTOMER_CATEGORY_CODE,
CUSTOMER_CLASS_CODE,
INSERT_UPDATE_FLAG,
ORG_ID,
ADDRESS1,
ADDRESS2,
ADDRESS3,
POSTAL_CODE,
CITY,
COUNTRY,
SITE_USE_CODE,
PRIMARY_SITE_USE_FLAG,
GL_ID_REC,
GL_ID_REV,
LOCATION, --- LOCATION_ID given in the data file
ORIG_SYSTEM_ADDRESS_REF,
--ADDRESS_ATTRIBUTE1,
CUSTOMER_ATTRIBUTE1,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE
)
VALUES
(vc1.CUSTOMER_NAME,
vc1.ORIG_SYSTEM_REFERENCE,
X_CUSTOMER_TYPE,---vc1.CUSTOMER_TYPE,
vc1.CUSTOMER_CATEGORY_CODE,
vc1.CUSTOMER_CLASS_CODE,
'I',
x_org_id,
vc1.ADDRESS1,
vc1.ADDRESS2,
vc1.ADDRESS3,
vc1.POSTAL_CODE,
vc1.CITY,
vc1.COUNTRY,
vc1.SITE_USE_CODE,----DECODE(vc1.SITE_USE_CODE,'BILL TO','BILL_TO',vc1.SITE_USE_CODE),
vc1.PRIMARY_FLAG,
X_GL_ID_REC,
X_GL_ID_REV,
vc1.location,---x_location_name,
vc1.ORIG_SYSTEM_REFERENCE1,
--TO_CHAR(CUST_ADDR_ORIG_REF),
vc1.attribute1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE );
x_debug_point:=23;
FND_FILE.PUT_LINE(FND_FILE.LOG,'BeTWEEN IF condition into ra_customers_interface_all '|| ' at debug point : ' ||x_debug_point);
END IF;
x_debug_point:=24;
FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into ra_contact_phones_int_all(Bill_to) '|| ' at debug point : ' ||x_debug_point);--- added by Madhu For debugging on 16th Sep 2008
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'SITE_USE_CODE VALUE IS :'||vc1.SITE_USE_CODE);
IF vc1.SITE_USE_CODE = UPPER('SHIP TO') OR vc1.SITE_USE_CODE = ('SHIP_TO') THEN
SELECT COUNT(1) INTO x_billship_valcount FROM ZNAR_CUSTOMERS_STAGING_TABLE WHERE ORIG_SYSTEM_REFERENCE = vc1.ORIG_SYSTEM_REFERENCE
AND (SITE_USE_CODE = UPPER('BILL TO') OR SITE_USE_CODE = UPPER('BILL_TO')) ;
SELECT COUNT(1) INTO x_billship_valcount1
FROM HZ_CUST_ACCOUNTS HCA , HZ_CUST_ACCT_SITES_ALL HCAS ,HZ_CUST_SITE_USES_ALL HCSU
WHERE HCA.ORIG_SYSTEM_REFERENCE = vc1.ORIG_SYSTEM_REFERENCE
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND (HCSU.SITE_USE_CODE = UPPER('BILL TO') OR HCSU.SITE_USE_CODE = UPPER('BILL_TO')) ;
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'COUNT :'||x_billship_valcount||' COUNT1 :'||x_billship_valcount1);
IF x_billship_valcount > 0 OR x_billship_valcount1 >0 THEN
-- FND_FILE.PUT_LINE(FND_FILE.LOG, 'before insert');
INSERT INTO RA_CUSTOMERS_INTERFACE_ALL
(CUSTOMER_NAME,
ORIG_SYSTEM_CUSTOMER_REF,
CUSTOMER_TYPE,
CUSTOMER_CATEGORY_CODE,
CUSTOMER_CLASS_CODE,
INSERT_UPDATE_FLAG,
ORG_ID,
ADDRESS1,
ADDRESS2,
ADDRESS3,
POSTAL_CODE,
CITY,
COUNTRY,
SITE_USE_CODE,
PRIMARY_SITE_USE_FLAG,
--GL_ID_REC,
--GL_ID_REV,
LOCATION, --- LOCATION_ID given in the data file
ORIG_SYSTEM_ADDRESS_REF,
BILL_TO_ORIG_ADDRESS_REF,
-- ADDRESS_ATTRIBUTE1,
CUSTOMER_ATTRIBUTE1,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE
)
VALUES
(vc1.CUSTOMER_NAME,
vc1.ORIG_SYSTEM_REFERENCE,
X_CUSTOMER_TYPE,---vc1.CUSTOMER_TYPE,
vc1.CUSTOMER_CATEGORY_CODE,
vc1.CUSTOMER_CLASS_CODE,
'I',
x_org_id,
vc1.ADDRESS1,
vc1.ADDRESS2,
vc1.ADDRESS3,
vc1.POSTAL_CODE,
vc1.CITY,
vc1.COUNTRY,
vc1.SITE_USE_CODE,----DECODE(vc1.SITE_USE_CODE,'BILL TO','BILL_TO',vc1.SITE_USE_CODE),
vc1.PRIMARY_FLAG,
--X_GL_ID_REC,
--X_GL_ID_REV,
vc1.location,---x_location_name,
vc1.ORIG_SYSTEM_REFERENCE1,
vc1.ORIG_SYSTEM_REFERENCE1,
--TO_CHAR(CUST_ADDR_ORIG_REF),
--TO_CHAR(CUST_ADDR_ORIG_REF),
vc1.attribute1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE );
END IF ;
END IF ;
x_debug_point:=25;
FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into ra_contact_phones_int_all(Ship_to) '|| ' at debug point : ' ||x_debug_point);--- added by Madhu For debugging on 16th Sep 2008
IF vc1.CUSTOMER_PROFILE_CLASS IS NOT NULL THEN
SELECT COUNT(1) INTO x_profile_count FROM ra_customer_profiles_int_all
WHERE ORIG_SYSTEM_CUSTOMER_REF = vc1.ORIG_SYSTEM_REFERENCE AND
CUSTOMER_PROFILE_CLASS_NAME = vc1.CUSTOMER_PROFILE_CLASS ;
IF x_profile_count = 0 THEN
x_debug_point:=26;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before insert into RA_CUSTOMER_PROFILES_INT_ALL '|| ' at debug point : ' ||x_debug_point);--- added by Madhu For debugging on 16th Sep 2008
INSERT INTO RA_CUSTOMER_PROFILES_INT_ALL(ORG_ID,
ORIG_SYSTEM_CUSTOMER_REF
, CREDIT_HOLD
, CUSTOMER_PROFILE_CLASS_NAME
,INSERT_UPDATE_FLAG
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, CREATED_BY
, CREATION_DATE)
VALUES(x_org_id,---vc1.org_id,
vc1.ORIG_SYSTEM_REFERENCE
, 'N'
, vc1.CUSTOMER_PROFILE_CLASS
, 'I'
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE);
END IF ;
END IF;
x_debug_point:=27;
FND_FILE.PUT_LINE(FND_FILE.LOG,'After inserting into RA_CUSTOMER_PROFILES_INT_ALL '|| ' at debug point : ' ||x_debug_point);--- added by Madhu For debugging on 16th Sep 2008
-- Contact references not provided for Prod ------------------------------------------------------------------------
/*******************************************************************************************************************
For vc4 IN customer_contact_c1 (vc1.ORIG_SYSTEM_REFERENCE, vc1.ORIG_SYSTEM_REFERENCE1) LOOP
-- Populate Orig System References from Sequences
-- Select CUST_CONT_ORIG_REFS_S.NEXTVAL
-- Into CUST_CONT_ORIG_REF
-- From Dual;
-- Select CUST_CONT_ORIG_REFS_S.NEXTVAL
-- Into CUST_PHONE_ORIG_REF
-- From Dual;
-- VALIDATION FOR CONTACT TITLE STARTS
BEGIN
IF vc4.TITLE IS NOT NULL THEN
SELECT COUNT(1)
INTO x_TITLE
FROM AR_LOOKUPS
WHERE lookup_type = 'CONTACT_TITLE'
AND LOOKUP_CODE = UPPER(vc4.TITLE)
AND ENABLED_FLAG='Y';
IF x_TITLE = 0 THEN
x_err_msg := x_err_msg|| ' - ' || 'TITLE is invalid for Batch # '||vc1.BATCH_NAME||' TITLE - '||vc4.TITLE;
x_error_flag := 'Y';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in validating TITLE Batch # '||vc1.BATCH_NAME||' TITLE - '||vc4.TITLE||'-'||SQLCODE||' '||SQLERRM);
x_error_flag := 'Y';
END;
-- VALIDATION FOR CONTACT TITLE ENDS --
IF --vc4.area_code IS NOT NULL and
vc4.PHONE_NUMBER IS NOT NULL THEN
x_opcount := 0;
select count(1) into x_opcount from hz_contact_points where
ORIG_SYSTEM_REFERENCE = vc4.ORIG_SYSTEM_REFERENCE and
PHONE_NUMBER = vc4.PHONE_NUMBER;
---AND phone_line_type = 'GEN'
--AND PHONE_EXTENSION=vc4.TELEPHONE_EXTENSION;
if x_opcount = 0 then
x_debug_point:=28;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before insert into ra_contact_phones_int_all '|| ' at debug point : ' ||x_debug_point);--- added by Madhu For debugging on 16th Sep 2008
INSERT INTO ra_contact_phones_int_all
(ORG_ID,
ORIG_SYSTEM_CUSTOMER_REF,
INSERT_UPDATE_FLAG,
ORIG_SYSTEM_ADDRESS_REF,
ORIG_SYSTEM_CONTACT_REF,
ORIG_SYSTEM_TELEPHONE_REF,
TELEPHONE,
TELEPHONE_AREA_CODE,
CONTACT_FIRST_NAME,
CONTACT_LAST_NAME,
CONTACT_TITLE,
EMAIL_ADDRESS,
CONTACT_JOB_TITLE,
CONTACT_POINT_TYPE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE
)
VALUES
(x_org_id,
vc4.ORIG_SYSTEM_REFERENCE,
'I',
vc4.ORIG_SYSTEM_REFERENCE1,
--TO_CHAR(CUST_ADDR_ORIG_REF),
--vc4.ORIG_SYSTEM_REFERENCE||'_CONT',
--vc4.ORIG_SYSTEM_REFERENCE||'_TEL',
TO_CHAR(CUST_CONT_ORIG_REF),
TO_CHAR(CUST_PHONE_ORIG_REF),
vc4.PHONE_NUMBER,
vc4.AREA_CODE,
vc4.FIRST_NAME,
vc4.LAST_NAME,
--UPPER(vc4.TITLE),
vc4.TITLE,
vc4.EMAIL_ADDRESS,
vc4.JOB_TITLE,
'PHONE',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE
);
end if ;
ELSE -- vc4.PHONE_NUMBER is NULL
INSERT INTO ra_contact_phones_int_all
(ORG_ID,
ORIG_SYSTEM_CUSTOMER_REF,
INSERT_UPDATE_FLAG,
ORIG_SYSTEM_ADDRESS_REF,
ORIG_SYSTEM_CONTACT_REF,
CONTACT_FIRST_NAME,
CONTACT_LAST_NAME,
CONTACT_TITLE,
EMAIL_ADDRESS,
CONTACT_JOB_TITLE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE
)
VALUES
(x_org_id,
vc4.ORIG_SYSTEM_REFERENCE,
'I',
vc4.ORIG_SYSTEM_REFERENCE1,
--TO_CHAR(CUST_ADDR_ORIG_REF),
--TO_CHAR(CUST_CONT_ORIG_REFS_S.NEXTVAL),
TO_CHAR(CUST_CONT_ORIG_REF),
vc4.FIRST_NAME,
vc4.LAST_NAME,
--UPPER(vc4.TITLE),
vc4.TITLE,
vc4.EMAIL_ADDRESS,
vc4.JOB_TITLE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE
);
END IF;
END LOOP;
x_debug_point:=29;
FND_FILE.PUT_LINE(FND_FILE.LOG,'After inserting into ra_contact_phones_int_all '|| ' at debug point : ' ||x_debug_point);--- added by Madhu For debugging on 16th Sep 2008
*******************************************************************************************************************/
UPDATE XX_CUSTOMERS_STAGING_TABLE
SET VALIDATION_STATUS = 'I' ,
REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
WHERE ORIG_SYSTEM_REFERENCE = vc1.ORIG_SYSTEM_REFERENCE ;
-- WHERE CURRENT OF customer_master_c1;
ELSE
UPDATE XX_CUSTOMERS_STAGING_TABLE
SET ERROR_MSG= x_err_msg,
VALIDATION_STATUS='E' ,
REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
WHERE ORIG_SYSTEM_REFERENCE = vc1.ORIG_SYSTEM_REFERENCE ;
-- WHERE CURRENT OF customer_master_c1;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception for Batch # '||vc1.BATCH_NAME||' ORIGINAL SYSTEM CUSTOMER REFERENCE - '||vc1.ORIG_SYSTEM_REFERENCE||'-'||SQLCODE||' '||SQLERRM);
END;
END LOOP;
--delete from XX_CUSTOMERS_STAGING_TABLE;
COMMIT;
-- errbuf := 'Number of records inserted :'||x_record_count;
-- retcode := 0;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception In Customer interface'||'-'||SQLCODE||' '||SQLERRM);
END CUSTOMER_CONVERSION_PROC;
END CUSTOMER_CONVERSION_PKG;
/
No comments:
Post a Comment