API-SUPPLIER CONVERSION VALIDATION CODE

CREATE OR REPLACE procedure xxc_suppliers_validate(p_vendor_number VARCHAR2 default null)
is
CURSOR C_STAGE(p_vendor_number varchar2)
IS SELECT * FROM XXC_SUPP_STAGE
   WHERE vendor_number=nvl(p_vendor_number,vendor_number);
-------Local Variables  Declaration
    lv_vendor_num                VARCHAR2(50);
    lv_vendor_name                VARCHAR2(100);
    lv_organization_type            VARCHAR2(200);
    lv_vendor_pay_group         VARCHAR2(200);
    lv_organization_id              VARCHAR2(250);
    lv_liability_account             VARCHAR2(300);
    lv_prepay_account             VARCHAR2(300);
    lv_terms_id                VARCHAR2(100);
    lv_supp_errmsg                VARCHAR2(2000);
    lv_site_errmsg                VARCHAR2(2000);
    lv_contact_errmsg            VARCHAR2(2000);
    lv_vendor_site_code           VARCHAR2(200);
    lv_vendor_sites_pay_group VARCHAR2(300);
    lv_vendor_type                   VARCHAR2(100);
    lv_vendor_number              VARCHAR2(250);
        lv_currency_code                VARCHAR2(50);
    lv_country_code                  VARCHAR2(10);
    lv_sites_terms_id                 VARCHAR2(100);
    lv_supp_flag                        VARCHAR2(10);
    lv_site_flag                          VARCHAR2(10);
    lv_contact_flag                     VARCHAR2(10);
 BEGIN
 DBMS_OUTPUT.PUT_LINE('Start VALIDATE_SUPLIERS ');
------Opening  The loop  from Spec For Validating Data
 FOR CP_STAGE IN C_STAGE(p_vendor_number) LOOP
 ------Assinging values To lical Varibles to handle Errors     
      lv_supp_flag           :='N';
      lv_site_flag             :='N';
      lv_contact_flag        :='N';
      lv_supp_errmsg       :='';       
      lv_site_errmsg         :='';       
      lv_contact_errmsg    :='';
      lv_vendor_num         :=NULL;
      LV_VENDOR_NAME       :='';       
      lv_vendor_site_code   :='';
      ------------------START OF VENDOR NUMBERAND VENDORNAME VALIDATION -----------------
       IF CP_STAGE.VENDOR_NUMBER IS NOT NULL THEN
      BEGIN
         SELECT SEGMENT1,VENDOR_NAME
         INTO   lv_vendor_num,LV_VENDOR_NAME
         FROM   AP_SUPPLIERS
         WHERE  TRIM(SEGMENT1)=TRIM(CP_STAGE.VENDOR_NUMBER);
    EXCEPTION
         WHEN NO_DATA_FOUND THEN
          NULL;
         WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE(CP_STAGE.VENDOR_NUMBER||' - '||'THERE ARE MORE THAN ONE VENDOR NUMBER ');
     WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
      END;
      IF lv_vendor_num IS NOT NULL AND lv_vendor_name IS NOT NULL THEN
         lv_supp_flag := 'Y';
         lv_supp_errmsg:=CP_STAGE.VENDOR_NUMBER||'   '||CP_STAGE.VENDOR_NAME||' - '||'VENDOR NUMBER OR VENDOR_NAME IS ALREADY EXIST NOT VALID';
         lv_supp_errmsg:=lv_supp_errmsg||' ; ';
        DBMS_OUTPUT.PUT_LINE(CP_STAGE.VENDOR_NUMBER||'  '||CP_STAGE.VENDOR_NAME||' - '||'VENDOR NUMBER OR VENDOR_NAME ALREADY EXIST NOT VALID');
        END IF;
    END IF;
    ------------------------------END OF VENDOR_ NUMBERAND VENDORNAME VALIDATION------------------
    ---------------------------START OF ORGANIZATION_TYPE --------------------
    IF CP_STAGE.ORGANIZATION_TYPE IS NOT NULL THEN
    BEGIN
        SELECT LOOKUP_CODE
        INTO lv_organization_type
        FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE='ORGANIZATION TYPE'
        AND   TRIM(LOOKUP_CODE)=TRIM(CP_STAGE.ORGANIZATION_TYPE);
        EXCEPTION WHEN NO_DATA_FOUND THEN
            lv_supp_flag := 'Y';
        lv_supp_errmsg:= lv_supp_errmsg||CP_STAGE.ORGANIZATION_TYPE||' -  '||'ORGANIZATION_TYPE  INVALID';
        DBMS_OUTPUT.PUT_LINE(lv_supp_errmsg);
               WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
    END;   
    END IF;
    ----------------------------END OF ORGANIZATION_TYPE---------------
    ------START OF VENDOR_TYPE VALIDATION -----------------
    IF CP_STAGE.VENDOR_TYPE IS NOT NULL THEN
          BEGIN
            SELECT LOOKUP_CODE
            INTO   lv_vendor_type
            FROM   PO_LOOKUP_CODES
            WHERE LOOKUP_TYPE='VENDOR TYPE' AND
            TRIM(LOOKUP_CODE)=TRIM(CP_STAGE.VENDOR_TYPE);
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
              lv_supp_flag := 'Y';
          lv_supp_errmsg:=lv_supp_errmsg||CP_STAGE.VENDOR_TYPE||' - '||'VENDOR TYPE IS NOT VALID';
           DBMS_OUTPUT.PUT_LINE(CP_STAGE.VENDOR_TYPE||' - '||'VENDOR TYPE IS NOT VALID');
       WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
 END IF;
    ----------END OF VENDOR_TYPE----------------------
    ----------------START OF VENDOR_TERMS_NAME ------------------
    IF CP_STAGE.VENDOR_PAYMENT_TERMS IS NOT NULL THEN
        BEGIN
        SELECT  TERM_ID
        INTO    lv_terms_id
        FROM    AP_TERMS_TL
        WHERE    UPPER(TRIM(NAME))= UPPER(TRIM(CP_STAGE.VENDOR_PAYMENT_TERMS));
         EXCEPTION
                WHEN NO_DATA_FOUND THEN
               lv_supp_flag := 'Y';
          lv_supp_errmsg:=lv_supp_errmsg||CP_STAGE.VENDOR_PAYMENT_TERMS||' - '||'INVALID PAYMENT TERMS';
          lv_supp_errmsg:=lv_supp_errmsg||' ; ';            
          WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
   END IF;
   ----------------------END OF TERMS_NAME--------------------------------
   ------------START OF VENDOR_PAY_GROUP -------------------
 IF CP_STAGE.VENDOR_PAY_GROUP IS NOT NULL THEN
  BEGIN
    SELECT LOOKUP_CODE INTO lv_vendor_pay_group
    FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE='PAY GROUP'
    AND  LOOKUP_CODE=CP_STAGE.VENDOR_PAY_GROUP;
  EXCEPTION
              WHEN NO_DATA_FOUND THEN
               lv_supp_flag := 'Y';
           lv_supp_errmsg:=lv_supp_errmsg||CP_STAGE.VENDOR_PAY_GROUP||' - '||'INVALID VENDOR PAY GROUP';
           lv_supp_errmsg:=lv_supp_errmsg||' ; ';            
          WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
END IF;
---------------END OF VENDOR_PAY_GROUP--------------------
   -----------------START OF OPERATING UNIT NAME ----------------
IF CP_STAGE.OPERATING_UNIT_NAME IS NOT NULL THEN
    BEGIN
    SELECT ORGANIZATION_ID
        INTO lv_organization_id
        FROM HR_OPERATING_UNITS
        WHERE  UPPER(NAME)=UPPER(CP_STAGE.OPERATING_UNIT_NAME);
     EXCEPTION
                WHEN NO_DATA_FOUND THEN
               lv_site_flag := 'Y';
          lv_site_errmsg:=lv_site_errmsg||CP_STAGE.OPERATING_UNIT_NAME||' - '||'INVALID OPERATING UNIT NAME';
           lv_site_errmsg:=lv_site_errmsg||' ; ';            
          WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
END IF;
   ---------------------END OF OPERATING_UNIT---------------
 --------------------START OF CURRENCY_CODE ----------------
   IF CP_STAGE.CURRENCY_CODE IS NOT NULL THEN
    BEGIN
    SELECT CURRENCY_CODE 
        INTO lv_currency_code 
        FROM  FND_CURRENCIES
        WHERE UPPER(CURRENCY_CODE)=UPPER(CP_STAGE.CURRENCY_CODE);
     EXCEPTION
                WHEN NO_DATA_FOUND THEN
               lv_site_flag := 'Y';
           lv_site_errmsg:=lv_site_errmsg||CP_STAGE.CURRENCY_CODE||' - '||'INVALID CURRENCY';
          lv_site_errmsg:=lv_site_errmsg||' ; ';            
          WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
END IF;
   --------------------END OF CURRENCY_CODE-------------
------------------------START OF COUNTRY CODE -----------------
IF CP_STAGE.COUNTRY IS NOT NULL THEN
      BEGIN
         SELECT TERRITORY_CODE
         INTO   lv_country_code FROM   FND_TERRITORIES_VL
         WHERE  TRIM(TERRITORY_CODE)=TRIM(CP_STAGE.COUNTRY);        
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
              lv_site_flag :='Y';
             lv_site_errmsg:=lv_site_errmsg||CP_STAGE.COUNTRY||' - '||'COUNTRY IS NOT VALID';
              lv_site_errmsg:=lv_site_errmsg||' ; ';
          DBMS_OUTPUT.PUT_LINE(CP_STAGE.COUNTRY||' - '||'COUNTRY IS NOT VALID');
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
     END;
 END IF;
---------------------END OF COUNTRY CODE-----------------------
----------------START OF VENDOR_SITES_TERMS_NAME ------------------
    IF CP_STAGE.VENDOR_SITES_PAYMENT_TERMS  IS NOT NULL THEN
        BEGIN
        SELECT  TERM_ID
        INTO    lv_sites_terms_id
        FROM    AP_TERMS_TL
        WHERE    UPPER(TRIM(NAME))= UPPER(TRIM(CP_STAGE.VENDOR_SITES_PAYMENT_TERMS ));
         EXCEPTION
                WHEN NO_DATA_FOUND THEN
               lv_site_flag := 'Y';
          lv_site_errmsg:=lv_site_errmsg||CP_STAGE.VENDOR_SITES_PAYMENT_TERMS ||' - '||'INVALID SITES PAYMENT TERMS';
           lv_site_errmsg:=lv_site_errmsg||' ; ';            
          WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
   END IF;
   ----------------------END OF  VENDOR SITES TERMS_NAME--------------------------------
   ------------START OF VENDOR_SITES_PAY_GROUP -------------------
 IF CP_STAGE.VENDOR_SITES_PAY_GROUP IS NOT NULL THEN
  BEGIN
    SELECT LOOKUP_CODE INTO lv_vendor_sites_pay_group
    FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE='PAY GROUP'
    AND  LOOKUP_CODE=CP_STAGE.VENDOR_SITES_PAY_GROUP;
  EXCEPTION
              WHEN NO_DATA_FOUND THEN
               lv_site_flag := 'Y';
           lv_site_errmsg:=lv_site_errmsg||CP_STAGE.VENDOR_SITES_PAY_GROUP||' - '||'INVALID VENDOR SITES PAY GROUP';
           lv_site_errmsg:=lv_site_errmsg||' ; ';            
          WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
END IF;
---------------END OF VENDOR_SITES_PAY_GROUP--------------------
----------------START OF LIABILITY_ACCOUNT ----------------
IF CP_STAGE.LIABILITY_ACCOUNT IS NOT NULL THEN
BEGIN
    SELECT CONCATENATED_SEGMENTS INTO
    lv_liability_account
    FROM GL_CODE_COMBINATIONS_KFV WHERE TRIM(CONCATENATED_SEGMENTS)=TRIM(CP_STAGE.LIABILITY_ACCOUNT);
EXCEPTION
              WHEN NO_DATA_FOUND THEN
               lv_site_flag := 'Y';
           lv_site_errmsg:=lv_site_errmsg||CP_STAGE.LIABILITY_ACCOUNT||' - '||'INVALID LIABILITY ACCOUNT';
          lv_site_errmsg:=lv_site_errmsg||' ; ';            
          WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
 END IF;
---------------END OF LIABILITY_ACCOUNT---------------------------
----------------START OF PREPAY_ACCOUNT ----------------
IF CP_STAGE.PREPAY_ACCOUNT IS NOT NULL THEN
BEGIN
    SELECT CONCATENATED_SEGMENTS INTO
    lv_prepay_account
    FROM GL_CODE_COMBINATIONS_KFV WHERE TRIM(CONCATENATED_SEGMENTS)=TRIM(CP_STAGE.LIABILITY_ACCOUNT);
EXCEPTION
              WHEN NO_DATA_FOUND THEN
               lv_site_flag := 'Y';
           lv_site_errmsg:=lv_site_errmsg||CP_STAGE.PREPAY_ACCOUNT||' - '||'INVALID PREPAY ACCOUNT';
          lv_site_errmsg:=lv_site_errmsg||' ; ';            
          WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
        END;
 END IF;
---------------END OF LIABILITY_ACCOUNT---------------------------
--------------------START OF VENDOR_SITE_CODE  ------------------
IF CP_STAGE.VENDOR_SITE IS NOT NULL THEN
BEGIN
    SELECT  B.VENDOR_SITE_CODE
    INTO   lv_vendor_site_code   
    FROM  AP_SUPPLIERS A,
    AP_SUPPLIER_SITES_ALL B
    WHERE A.VENDOR_ID=B.VENDOR_ID
    AND TRIM(B.VENDOR_SITE_CODE)=TRIM(CP_STAGE.VENDOR_SITE)
    AND A.SEGMENT1=CP_STAGE.VENDOR_NUMBER;
EXCEPTION
         WHEN NO_DATA_FOUND THEN
          NULL;
         WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE(CP_STAGE.VENDOR_SITE||' - '||'THERE ARE MORE THAN ONE VENDOR SITES ');
     WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
 END;
     IF lv_vendor_site_code IS NOT NULL THEN
         lv_site_flag := 'Y';
         lv_contact_flag:='Y';
        lv_site_errmsg:=CP_STAGE.VENDOR_SITE||' - '||' VENDOR_SITE IS ALREADY EXIST NOT VALID';
        lv_contact_errmsg:=CP_STAGE.VENDOR_SITE||' - '||' VENDOR_SITE IS ALREADY EXIST NOT VALID';
         --LV_ERRMSG:=LV_ERRMSG||' ; ';
        DBMS_OUTPUT.PUT_LINE(CP_STAGE.VENDOR_NUMBER||'  '||CP_STAGE.VENDOR_SITE||' - '||' VENDOR_SITE ALREADY EXIST NOT VALID');
          END IF;
    END IF;
--------------------END OF VENDOR_SITE_CODE--------------------------------------
   -- Changing the STAGEing table flag
------------------------------------
--
IF lv_supp_flag ='N'   THEN
 BEGIN
  UPDATE XXC_SUPP_STAGE
  SET    v_supp_flag ='V'   
        ,supplier_error_message =  null
  where  vendor_number =cp_stage.vendor_number;
COMMIT;
  EXCEPTION
        WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Occured During Updation of Valid Records in Update in xxc_supp_staging');
 END;
END IF;
--
IF lv_supp_flag = 'Y'  THEN
 BEGIN
 UPDATE XXC_SUPP_STAGE
 SET    v_supp_flag ='E'    
       ,supplier_error_message =lv_supp_errmsg
 where  vendor_number = cp_stage.vendor_number;
COMMIT;
 EXCEPTION
         WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Occured During Updation of Error Records in xxc_supp_staging');
 END;
END IF;
--
--
IF lv_site_flag = 'N'  THEN
 BEGIN
  UPDATE XXC_SUPP_STAGE
  SET   
         v_site_flag ='V'   
        ,site_error_message = null
  where  vendor_number = cp_stage.vendor_number;
COMMIT;
  EXCEPTION
        WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Occured During Updation of Valid Records in Update in xxc_supp_staging');
 END;
END IF;
--
IF  lv_site_flag='Y'  THEN
 BEGIN
 UPDATE XXC_SUPP_STAGE
 SET   
         v_site_flag ='E'
       ,site_error_message = lv_site_errmsg
 where  vendor_number =cp_stage.vendor_number;
COMMIT;
 EXCEPTION
         WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Occured During Updation of Error Records in xxc_supp_staging');
 END;
END IF;
--
IF lv_contact_flag ='N' THEN
 BEGIN
  UPDATE XXC_SUPP_STAGE
  SET  
     v_contact_flag='V'
        ,contact_error_message = null
  where  vendor_number = cp_stage.vendor_number;
COMMIT;
  EXCEPTION
        WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Occured During Updation of Valid Records in Update in xxc_supp_staging');
 END;
END IF;
--
IF  lv_contact_flag = 'Y' THEN
 BEGIN
 UPDATE XXC_SUPP_STAGE
 SET  
     v_contact_flag='E'
       ,contact_error_message = lv_contact_errmsg
 where  vendor_number =cp_stage.vendor_number;
COMMIT;
 EXCEPTION
         WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Occured During Updation of Error Records in xxc_supp_staging');
 END;
END IF;
DBMS_OUTPUT.PUT_LINE('End the VALIDATE_SUPLIERS ');
      END LOOP;
    END xxc_suppliers_validate;--------end of validate suppliers-----------------------
----------start of xxc_create_suppliers----------