SET DEFINE OFF;

CREATE OR REPLACE PACKAGE ARNewCust	
AS
  PROCEDURE NewCust(ERRBUF OUT VARCHAR2,
            		    RETCODE OUT VARCHAR2); 
END ARNewCust;
/
-----------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ARNewCust 
AS
  PROCEDURE NewCust(ERRBUF OUT VARCHAR2,
    		            RETCODE OUT VARCHAR2) 
  IS
CURSOR c_arstaging 
IS
SELECT ROWID,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  from RA_CUSTOMER_INT_TEMP;

I c_arstaging%ROWTYPE;

V_CustName NUMBER;
V_CustCate NUMBER;
V_CustClass NUMBER;
V_CountryName VARCHAR2(20);
Cust_Exist  NUMBER;
Cust_Adres  NUMBER;
v_Adres VARCHAR2(20);
v_Exists VARCHAR2(20);
BEGIN	
  	FOR r_arstaging IN C_arstaging 
    LOOP 
		V_CustName :=0;
		V_CustCate :=0;
		V_CustClass :=0;
		V_CountryName:=NULL;
		Cust_Exist:=0;
		Cust_Adres:=0;
		v_Adres := NULL;
		v_Exists:= NULL;

        /* 			Customer & Address 		*/
     BEGIN	
         SELECT count(1) 
         INTO v_custname 
		     FROM RA_CUSTOMERS RC,RA_ADDRESSES_ALL  RA
  		   WHERE	rc.customer_id = ra.customer_id	
		     AND	RC.ORIG_SYSTEM_REFERENCE = I.ORIG_SYSTEM_CUSTOMER_REF
		     AND	RA.ORIG_SYSTEM_REFERENCE = I.ORIG_SYSTEM_ADDRESS_REF;
      IF v_custname > 0 THEN
          
          /*INSERT INTO  RA_CUSTOMER_INT_TEMP (ROWID,STATUS,ERROR_MSG)
          VALUES(I.ROWID,'FAIL','Combination of Customer & Address already Exists');*/
    		 UPDATE RA_CUSTOMER_INT_TEMP
		     SET status = 'FAIL',
			    error_msg = 'Combination of Customer & Address already Exists'
			   WHERE ROWID = I.rowid;
      END IF;
	  EXCEPTION
        WHEN OTHERS THEN
      		UPDATE RA_CUSTOMER_INT_TEMP
			    SET status = 'FAIL',
			        error_msg = 'Combination of Customer & Address already Exists'
    			WHERE ROWID = I.rowid;
     END;
       /* 			Customer class Category  		*/
     BEGIN
          SELECT COUNT(1) INTO V_CUSTCATE 
      		FROM  AR_LOOKUPS
      		WHERE LOOKUP_TYPE ='CUSTOMER_CATEGORY'
    	  	AND   UPPER(lookup_code) = UPPER(I.customer_category_code);
	    IF v_custcate = 0		THEN 

		      UPDATE  RA_CUSTOMER_INT_TEMP
 			    SET   status = 'FAIL',
			            error_msg = 'Must Exists in Customer class Category in AR_LOOKUPS'
    		  WHERE ROWID = I.rowid;
	    END IF;		
    EXCEPTION 
  		WHEN OTHERS THEN
		      v_custcate := 0;	
		    UPDATE  RA_CUSTOMER_INT_TEMP
 			  SET   status = 'FAIL',
			        error_msg = 'Must Exists in Customer class Category in AR_LOOKUPS'
			  WHERE ROWID = I.rowid;
     END;
         /* 			Customer class code 		*/
     BEGIN
		      SELECT COUNT(1) INTO V_CUSTCLASS 
    		  FROM  AR_LOOKUPS
		      WHERE LOOKUP_TYPE ='CUSTOMER CLASS'
    		  AND   UPPER(lookup_code) = UPPER(I.customer_class_code);
	    IF v_custclass = 0	THEN	 
  		   UPDATE  RA_CUSTOMER_INT_TEMP
 	       SET   status = 'FAIL',
			      error_msg = 'Must Exists Customer class code in AR_LOOKUPS'
    		 WHERE ROWID = I.rowid;
	    END IF;		
    EXCEPTION 
  	  WHEN OTHERS THEN
		      v_custclass := 0;	
		    UPDATE  RA_CUSTOMER_INT_TEMP
 			  SET   status = 'FAIL',
			        error_msg = 'Must Exists  in Customer class code in AR_LOOKUPS'
			  WHERE ROWID = I.rowid;
    END;
    
	/* 			COUNTRY   		*/
    BEGIN 
          SELECT  territory_short_name 
		      INTO  V_COUNTRYNAME
		      FROM  fnd_territories_tl
		      WHERE UPPER(territory_code) = UPPER(I.country);
    EXCEPTION 
		   WHEN NO_DATA_FOUND THEN 
  		  	  v_countryName := NULL;
			    UPDATE  RA_CUSTOMER_INT_TEMP
 			    SET   status = 'FAIL',
			      error_msg = 'Must Exists in FND_TERRITORIES'
			    WHERE ROWID = I.rowid;
		   WHEN OTHERS THEN
			    v_countryName := NULL;
		      UPDATE  RA_CUSTOMER_INT_TEMP
 			    SET   status = 'FAIL',
			      error_msg = 'Invalid Country Name'
			    WHERE ROWID = I.rowid;
    END;
		/* Checking the existing of Cust & Address in  RA_CUSTOMER_PROFILES_INT_ALL */
    BEGIN
         SELECT COUNT(1) INTO v_Exists  
	       FROM   RA_CUSTOMER_PROFILES_INT_ALL
	       WHERE  ORIG_SYSTEM_CUSTOMER_REF = I.orig_system_customer_ref
	        AND    ORIG_SYSTEM_ADDRESS_REF  IS NULL; 
      IF v_Exists IS NOT NULL THEN 
          Cust_Exist := 0;
   	  ELSE
		      Cust_Exist := 1;
 	    END IF;
     EXCEPTION 
  	    WHEN OTHERS THEN
		      Cust_Exist:= 1;
    END;   
    
    BEGIN
         SELECT COUNT(1) INTO v_Adres
	       FROM   RA_CUSTOMER_PROFILES_INT_ALL
	       WHERE  ORIG_SYSTEM_ADDRESS_REF = I.ORIG_SYSTEM_ADDRESS_REF;
	   
      IF v_Adres IS NOT NULL THEN 
            Cust_Adres := 0;
   	  ELSE
		        Cust_Adres := 1;  
	    END IF;
    EXCEPTION 
  	    WHEN OTHERS THEN
		      Cust_Adres := 1;
    END;  
    
  IF V_CUSTNAME = 0  and  V_CUSTCATE > 0 and V_CUSTCLASS  > 0 and V_COUNTRYNAME IS NOT NULL THEN
 		
		INSERT INTO ra_customers_interface
		(
			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
		(
			I.CUSTOMER_NAME , 
			I.orig_system_customer_ref,
			I.customer_status,
			I.SITE_USE_CODE,
			I.ORIG_SYSTEM_ADDRESS_REF  ,
			I.org_id ,
			NVL(I.PRIMARY_SITE_USE_FLAG,'N'),
			I.location ,
			I.ADDRESS1,
			I.ADDRESS2,
			I.ADDRESS3,
			I.CITY , 
			I.STATE,
			I.COUNTRY,
			I.CUSTOMER_CATEGORY_CODE,
			I.CUSTOMER_CLASS_CODE,
			I.BILL_TO_ORIG_ADDRESS_REF ,  
			I.INSERT_UPDATE_FLAG ,  
			123456,
			SYSDATE , 
			123456,
			SYSDATE ,
			I.CUSTOMER_NUMBER 
		);
	    IF   Cust_Exist = 0     and Cust_Adres = 0 THEN  		/* Checking the existing of Cust & Address in  RA_CUSTOMER_PROFILES_INT_ALL */
		     NULL;
	    ELSIF
	    	Cust_Exist = 0     and Cust_Adres = 1 THEN 
                
                 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
		              (
		               	I.ORIG_SYSTEM_CUSTOMER_REF,
									I.ORIG_SYSTEM_ADDRESS_REF,
									'I',
									I.CUSTOMER_PROFILE_CLASS_NAME,
									'N',
									I.ORG_ID,
									1234,
									SYSDATE,
									1234,
									SYSDATE
  	 	 	 	 	 	 	);
	    ELSIF
		      Cust_Exist = 1     and Cust_Adres = 1 THEN 
                
                 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
		  		  		  (
		  		  		   	I.ORIG_SYSTEM_CUSTOMER_REF,
									I.ORIG_SYSTEM_ADDRESS_REF,
									'I',
									I.CUSTOMER_PROFILE_CLASS_NAME,
									'N',
									I.ORG_ID,
									1234,
									SYSDATE,
									1234,
									SYSDATE
  	 	  	 	  );
	    END IF;		
END IF;
END LOOP;	
END;
END ARNewCust;
	
/

show err;