16-jan

CREATE OR REPLACE PROCEDURE T07_AR_INTERFACE AS CURSOR C1 IS SELECT * FROM T07_stage_ar; CUSTOMER_NO NUMBER(10); CNT1 NUMBER(10); CUST_NO VARCHAR2(240) := NULL; CUSTOMER_TYPE VARCHAR2(25); COUNTRY_NAME VARCHAR2(60); CITY_NAME VARCHAR2(60); STATE_NAME VARCHAR2(60); UNIQUE_CUST VARCHAR2(240); UNIQUE_ADDR VARCHAR2(240); UNIQUE_PHONE VARCHAR2(240); NO1 NUMBER(3); NO2 NUMBER(3); NO3 NUMBER(3); PTR NUMBER(3); SITE_CODE_USE VARCHAR2(50); MY_SITE_CODE VARCHAR2(100); STAGE_REC T07_stage_ar%ROWTYPE; BEGIN Fnd_global.apps_initialize(1318,50559,222); FOR REC1 IN C1 LOOP IF REC1.CUSTOMER_TYPE IS NULL THEN SELECT COUNT_CUST_REF.CURRVAL INTO CUSTOMER_NO FROM DUAL; UNIQUE_CUST := CONCAT('C',CUSTOMER_NO); SELECT COUNT_ADDR_REF.NEXTVAL INTO CUSTOMER_NO FROM DUAL; UNIQUE_ADDR := CONCAT('A',CUSTOMER_NO); ELSE SELECT COUNT_CUST_REF.NEXTVAL INTO CUSTOMER_NO FROM DUAL; UNIQUE_CUST := CONCAT('C',CUSTOMER_NO); IF REC1.ADDRESS IS NULL THEN UNIQUE_ADDR := NULL; ELSE SELECT COUNT_ADDR_REF.NEXTVAL INTO CUSTOMER_NO FROM DUAL; UNIQUE_ADDR := CONCAT('A',CUSTOMER_NO); END IF; END IF; IF REC1.TELEPHONE IS NULL THEN UNIQUE_PHONE := NULL; ELSE SELECT COUNT_PHONE_REF.NEXTVAL INTO CUSTOMER_NO FROM DUAL; UNIQUE_PHONE := CONCAT('T',CUSTOMER_NO); END IF; SELECT COUNT(1) INTO CNT1 FROM FND_TERRITORIES_TL WHERE UPPER(TERRITORY_SHORT_NAME) = UPPER(REC1.COUNTRY); IF CNT1 = 0 THEN DBMS_OUTPUT.PUT_LINE('COUNTRY NOT FOUND'); EXIT; ELSE SELECT COUNT(1) INTO CNT1 FROM AR_LOOKUPS_OLD WHERE AR_LOOKUPS_OLD.LOOKUP_TYPE = 'CUSTOMER_TYPE'; IF CNT1 = 0 THEN DBMS_OUTPUT.PUT_LINE('U R IN CUSTOMER_TYPE'); EXIT; END IF; NO1 := 0; NO2 := 0;

NO3 := 0; PTR := INSTR(REC1.ADDRESS,'('); PTR := PTR + 1; MY_SITE_CODE := SUBSTR(REC1.STATE,PTR,90); NO1 := INSTR(MY_SITE_CODE,'S'); NO2 := INSTR(MY_SITE_CODE,'B'); IF NO1!=0 AND NO2!=0 THEN NO3 := 1; END IF; SITE_CODE_USE := 'Bill To'; IF NO1 != 0 AND NO2=0 THEN SITE_CODE_USE := 'Ship To'; END IF; FOR I IN 1..2 LOOP -- DBMS_OUTPUT.PUT_LINE('START OF FOR :'||I||'---'||SITE_CODE_USE); INSERT INTO T07_RA_INTERFACE ( ORIG_SYSTEM_CUSTOMER_REF, ORIG_SYSTEM_ADDRESS_REF, INSERT_UPDATE_FLAG, PERSON_FLAG, CUSTOMER_NAME, CUSTOMER_STATUS, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, PRIMARY_SITE_USE_FLAG, COUNTRY, CITY, STATE, ADDRESS1, SITE_USE_CODE, BILL_TO_ORIG_ADDRESS_REF ) VALUES ( UNIQUE_CUST, UNIQUE_ADDR, 'I', 'N', NVL(REC1.NAME,STAGE_REC.NAME), 'A', FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, 'Y', NVL(REC1.COUNTRY,STAGE_REC.COUNTRY), NVL(REC1.CITY,STAGE_REC.CITY), NVL(REC1.STATE,STAGE_REC.STATE), NVL(REC1.ADDRESS,STAGE_REC.ADDRESS), SITE_CODE_USE, NVL(REC1.STATE,STAGE_REC.STATE) ); IF NO3 = 1 THEN SITE_CODE_USE := 'Ship To'; IF REC1.CITY LIKE 'Pune%' THEN

DBMS_OUTPUT.PUT_LINE(I||'NO3 :'|| NO3); DBMS_OUTPUT.PUT_LINE(I||'SITE_CODE_USE :'|| SITE_CODE_USE); END IF; -- DBMS_OUTPUT.PUT_LINE('END OF FOR :'||SITE_CODE_USE); ELSE IF REC1.CITY LIKE 'Pune%' THEN DBMS_OUTPUT.PUT_LINE(I||'IN ELSE NO3 :'|| NO3); DBMS_OUTPUT.PUT_LINE(I||'IN ELSE SITE_CODE_USE :'|| SITE_CODE_USE); END IF; EXIT; END IF; END LOOP; INSERT INTO T07_RA_PROFILES_INTERFACE ( INSERT_UPDATE_FLAG, ORIG_SYSTEM_CUSTOMER_REF, ORIG_SYSTEM_ADDRESS_REF, CREDIT_HOLD, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, COLLECTOR_NAME, CUSTOMER_PROFILE_CLASS_NAME, DUNNING_LETTERS, DUNNING_LETTER_SET_NAME, STANDARD_TERM_NAME, STATEMENTS, STATEMENT_CYCLE_NAME ) VALUES ( 'I', UNIQUE_CUST, UNIQUE_ADDR, 'N', FND_GLOBAL.USER_ID, SYSDATE, SYSDATE, FND_GLOBAL.USER_ID, REC1.COLLECTOR, REC1.PROFILE_CLASS, 'Y', REC1.LETTER_SET, REC1.PAYMENT_TERMS, 'Y', REC1.STATEMENT_CYCLE ); --IF REC1.ADDR IS NULL THEN INSERT INTO T07_RA_PHONES_INTERFACE ( ORIG_SYSTEM_CUSTOMER_REF , ORIG_SYSTEM_ADDRESS_REF, ORIG_SYSTEM_TELEPHONE_REF , INSERT_UPDATE_FLAG , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,

CREATED_BY TELEPHONE

,

) VALUES ( UNIQUE_CUST, UNIQUE_ADDR, UNIQUE_PHONE, 'I', SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, REC1.TELEPHONE ); END IF; STAGE_REC := REC1; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(SQLCODE||'-----'||SQLERRM); END T07_AR_INTERFACE; /

DELETE FROM T07_RA_INTERFACE; DELETE FROM T07_RA_PHONES_INTERFACE; DELETE FROM T07_RA_PROFILES_INTERFACE; COMMIT;

DECLARE NO1 number(5) := 0; NO2 number(5) := 0; NO3 number(5) := 0; SITE_CODE_USE VARCHAR2(50); BEGIN NO1 := INSTR('Bill To Ship tO)','S'); NO2 := INSTR('Bill To Ship tO)','B'); IF NO1!=0 AND NO2!=0 THEN NO3 := 1; END IF; SITE_CODE_USE := 'Bill To'; IF NO1 != 0 AND NO2=0 THEN SITE_CODE_USE := 'Ship To'; END IF; DBMS_OUTPUT.PUT_LINE(NO1); DBMS_OUTPUT.PUT_LINE(NO2); DBMS_OUTPUT.PUT_LINE(NO3); DBMS_OUTPUT.PUT_LINE(SITE_CODE_USE); END; /