API-SUPPLIER CONVERSION CODE

-----    Staging table for SupplierConversion For Loading The Flat File Data

CREATE TABLE XXC_SUPP_STAGE
(
  VENDOR_NUMBER                 VARCHAR2(30),
  VENDOR_NAME                 VARCHAR2(240),
  VENDOR_TYPE                     VARCHAR2(300),
  ORGANIZATION_TYPE             VARCHAR2(300),
  VENDOR_SITE                     VARCHAR2(300),
  VENDOR_PAY_GROUP                 VARCHAR2(300),
  VENDOR_PAYMENT_TERMS         VARCHAR2(300),
  ADDRESS_LINE1                 VARCHAR2(300),
  ADDRESS_LINE2                 VARCHAR2(300),
  ADDRESS_LINE3                 VARCHAR2(300),
  ADDRESS_LINE4                 VARCHAR2(300),
  CITY                         VARCHAR2(25),
  STATE                         VARCHAR2(15),
  COUNTRY                     VARCHAR2(25),
  ZIP_CODE                     VARCHAR2(20),
  VENDOR_SITES_PAY_GROUP         VARCHAR2(300),
  VENDOR_SITES_PAYMENT_TERMS     VARCHAR2(300),
  OPERATING_UNIT_NAME             VARCHAR2(500),
  LIABILITY_ACCOUNT             VARCHAR2(300),
  PREPAY_ACCOUNT                            VARCHAR2(300),
  BANK_NAME                     VARCHAR2(300),
  BANK_BRANCH_NAME             VARCHAR2(300),
  BANK_ACCOUNT_NAME                     VARCHAR2(300),
  BANK_ACCOUNT_NUMBER                 VARCHAR2(300),
  CURRENCY_CODE                             VARCHAR2(50),
  CONTACT_FIRST_NAME                     VARCHAR2(100),
  CONTACT_LAST_NAME                      VARCHAR2(150),
  CONTACT_PHONE                             VARCHAR2(100),
  CONTACT_EMAIL                              VARCHAR2(300),
  CONTACT_FAX                                 VARCHAR2(100),
  SET_OF_BOOKS_ID                          NUMBER,
  V_SUPP_FLAG                                  VARCHAR2(10),
  V_SITE_FLAG                                   VARCHAR2(10),
  V_CONTACT_FLAG                            VARCHAR2(10),
  SUPPLIER_ERROR_MESSAGE              VARCHAR2(2000),
  SITE_ERROR_MESSAGE                     VARCHAR2(2000),
  CONTACT_ERROR_MESSAGE              VARCHAR2(200),
  CREATED_BY                                    NUMBER,
  CREATION_DATE                              DATE,
  LAST_UPDATED_BY                           NUMBER,
  LAST_UPDATE_DATE                         DATE,
  LAST_UPDATE_LOGIN                        NUMBER
)

-----------------End Of Staging Table Creation---------------------------------------



---------------------Loader File To load Flat File Data Into Staging Table------------------------------
OPTIONS (SKIP=1)
LOAD DATA
INFILE  '/u02/appltest/apps/apps_st/appl/ap/12.0.0/bin/SAMPLEDATA.txt'
INSERT
into table XXC_SUPP_STAGE
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 VENDOR_NAME,
 VENDOR_NUMBER,
  VENDOR_TYPE,
  ORGANIZATION_TYPE,
  VENDOR_SITE,
  VENDOR_PAY_GROUP,
  VENDOR_PAYMENT_TERMS,
  ADDRESS_LINE1,
  ADDRESS_LINE2,
  ADDRESS_LINE3,
  ADDRESS_LINE4,
  CITY,
  STATE,
  COUNTRY,
  ZIP_CODE,
  VENDOR_SITES_PAY_GROUP,
  VENDOR_SITES_PAYMENT_TERMS,
  OPERATING_UNIT_NAME,
  LIABILITY_ACCOUNT,
  PREPAY_ACCOUNT,
  BANK_NAME,
  BANK_BRANCH_NAME,
  BANK_ACCOUNT_NAME,
  BANK_ACCOUNT_NUMBER,
  CURRENCY_CODE,
  CONTACT_FIRST_NAME,
  CONTACT_LAST_NAME,
  CONTACT_PHONE,
  CONTACT_EMAIL,
  CONTACT_FAX,
  SET_OF_BOOKS_ID CONSTANT "1",
  V_SUPP_FLAG CONSTANT "N",
  V_SITE_FLAG CONSTANT "N",
  V_CONTACT_FLAG CONSTANT "N",
  SUPPLIER_ERROR_MESSAGE CONSTANT "",
  SITE_ERROR_MESSAGE     CONSTANT "",
  CONTACT_ERROR_MESSAGE  CONSTANT "",  
  CREATED_BY CONSTANT "-1",
  CREATION_DATE SYSDATE,
  LAST_UPDATED_BY CONSTANT "-1",
  LAST_UPDATE_DATE SYSDATE,
  LAST_UPDATE_LOGIN CONSTANT "-1"
)

----------------------------------End Of Loader File--------------------------------------------

CREATE OR REPLACE PACKAGE XXC_SUP_CREATION_PKG
/*
Author          :  SAI GOWTHAM.v
File Name       :  XXC_SUP_CREATION_PKG.pks
Created Date    :  AUG 2011
Modified By     :  SAI GOWTHAM.V
Modified Date    :  AUG 2011
Package Name    :  XXC_SUP_CREATION_PKG
Description        :  This package is used to create New Supplier, supplier sites and Contact information.
Comments        :
*/
IS
---THIS PROCEDURE IS TO VALIDATE SUPPLERS
PROCEDURE  XXC_SUPPLIERS_VALIDATE(p_vendor_number varchar2 default null);
 --This Procedure is used to Create Vendor.
 PROCEDURE  XXC_CREATE_SUPPLIERS(p_vendor_number varchar2 default null);
 --This Procedure is used to create Vendor sites.
 PROCEDURE  XXC_CREATE_SUPPLIER_SITES(   p_vendor_number    varchar2 default null,
                    p_vendor_site      varchar2 default null);
--This Procedure is used to create vendor contact information.
PROCEDURE XXC_CREATE_SUPPLIERS_CONTACTS( p_vendor_number    varchar2 default null,
                             p_vendor_site       varchar2 default null);
---This PROCEDURE  is used to create supplier information Totally
 PROCEDURE   XXC_MAIN_SUPPLIER(
                             errbuf out NOCOPY varchar2,
                             retcode out NOCOPY varchar2,
                             p_vendor_number varchar2   default null,
                             p_vendor_site varchar2 default null);
---This PROCEDURE  is used for display purpose----------------
Procedure XXC_DISPLAY_MESSAGE(p_mode Varchar2, p_message Varchar2);
-----------CURSORs----------------------------------

----This Cursor is used for validating the stagingTable Data
CURSOR C_STAGE(p_vendor_number varchar2)
IS SELECT * FROM XXC_SUPP_STAGE
   WHERE vendor_number=nvl(p_vendor_number,vendor_number);

---------------This Cursor is used For Inserting Valid Suppliers Data Into BaseTables

CURSOR c_supplier(p_vendor_number varchar2)
 IS SELECT * FROM XXC_SUPP_STAGE WHERE
  v_supp_flag='V' AND
   vendor_number=nvl(p_vendor_number,vendor_number);

-- --------This Cursor Is used to  For Inserting Valid SupplierSites for Suppliers into the Base Tables
CURSOR c_site(p_vendor_number varchar2,p_vendor_site varchar2)
 IS  SELECT * FROM XXC_SUPP_STAGE WHERE  v_supp_flag='V' and v_site_flag='V'
           and vendor_number=nvl(p_vendor_number,vendor_number)
           and vendor_site=nvl(p_vendor_site,vendor_site);

--------------This  Cursor is Used For Inserting Valid Suppliers Contacts Data Into the Base tables
CURSOR c_contact(p_vendor_number varchar2,p_vendor_site varchar2)
 IS  SELECT  * FROM XXC_SUPP_STAGE
  WHERE  v_supp_flag='V' and v_contact_flag='V' and v_site_flag='V'
   and vendor_number=nvl(p_vendor_number,vendor_number)
   and vendor_site=nvl(p_vendor_site,vendor_site);
-----------END OF Cursors------------------------------------
END XXC_SUP_CREATION_PKG;
/



CREATE OR REPLACE PACKAGE BODY XXC_SUP_CREATION_PKG
/*
Author          : SAI GOWTHAM .V
File Name       :  XXC_SUPP_CREATION_PKG.pkb
Created Date    :  AUG 2011
Modified By     :  SAI GOWTHAM.V
Modified Date    :  AUG 2011
Package Name    :  XXC_SUP_CREATION_PKG
Description    :  This package is used to create New Supplier, supplier sites and Contact information.
Comments        :
*/
IS
-----------PROCEDURE   for display message--------------
PROCEDURE   XXC_DISPLAY_MESSAGE(p_mode VARCHAR2, p_message VARCHAR2) is
BEGIN
    IF UPPER(p_mode) = 'output' THEN
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
    ELSIF UPPER(p_mode) = 'log' THEN
    FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
    dbms_output.put_line(p_message);
    END IF;
    dbms_output.put_line(substr(p_message,1,80));   
END XXC_DISPLAY_MESSAGE;
-----------END of display message--------------------
--------start of validate suppliers--------------
procedure xxc_suppliers_validate(p_vendor_number VARCHAR2 default null)
is

-------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
 XXC_DISPLAY_MESSAGE('log','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    :='';
     
      ------------------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
         XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_NUMBER||' - '||'THERE ARE MORE THAN ONE VENDOR NUMBER ');
     WHEN OTHERS THEN
     XXC_DISPLAY_MESSAGE('log',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||' ; ';
        XXC_DISPLAY_MESSAGE('log',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';
        XXC_DISPLAY_MESSAGE('log',lv_supp_errmsg);
               WHEN OTHERS THEN
            XXC_DISPLAY_MESSAGE('log',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';
           XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_TYPE||' - '||'VENDOR TYPE IS NOT VALID');
       WHEN OTHERS THEN
         XXC_DISPLAY_MESSAGE('log',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
          XXC_DISPLAY_MESSAGE('log',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
       XXC_DISPLAY_MESSAGE('log',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
          XXC_DISPLAY_MESSAGE('log',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
       XXC_DISPLAY_MESSAGE('log',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||' ; ';
          XXC_DISPLAY_MESSAGE('log',CP_STAGE.COUNTRY||' - '||'COUNTRY IS NOT VALID');
      WHEN OTHERS THEN
      XXC_DISPLAY_MESSAGE('log',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
          XXC_DISPLAY_MESSAGE('log',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
       XXC_DISPLAY_MESSAGE('log',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
       XXC_DISPLAY_MESSAGE('log',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
       XXC_DISPLAY_MESSAGE('log',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 A.SEGMENT1,
    B.VENDOR_SITE_CODE
    INTO LV_VENDOR_NUMBER,
    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
         XXC_DISPLAY_MESSAGE('log',CP_STAGE.VENDOR_SITE||' - '||'THERE ARE MORE THAN ONE VENDOR SITES ');
     WHEN OTHERS THEN
     XXC_DISPLAY_MESSAGE('log',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||' ; ';
        XXC_DISPLAY_MESSAGE('log',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
     XXC_DISPLAY_MESSAGE('log','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
      XXC_DISPLAY_MESSAGE('log','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
     XXC_DISPLAY_MESSAGE('log','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
      XXC_DISPLAY_MESSAGE('log','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
     XXC_DISPLAY_MESSAGE('log','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
      XXC_DISPLAY_MESSAGE('log','Error Occured During Updation of Error Records in xxc_supp_staging');
 END;
END IF;
XXC_DISPLAY_MESSAGE('log','End the VALIDATE_SUPLIERS ');
      END LOOP;
    END xxc_suppliers_validate;--------end of validate suppliers-----------------------
----------start of xxc_create_suppliers----------
PROCEDURE   XXC_CREATE_SUPPLIERS(p_vendor_number VARCHAR2 default null)
is
        lv_vendor_rec      AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE;
        lv_return_status   VARCHAR2(10);
        lv_msg_count       NUMBER;
        lv_msg_data        VARCHAR2(1000);
        lv_vendor_id        NUMBER;
        lv_party_id           NUMBER;
BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPl_ID);
--fnd_global.apps_initialize(0,20707,201);
FOR cp_supplier in c_supplier(p_vendor_number) LOOP

--Assingning Staging Table Data To Record Type

        lv_vendor_rec.vendor_name                          := cp_supplier.vendor_name;        --Supplier Name
        lv_vendor_rec.segment1                                := cp_supplier.vendor_number;
        lv_vendor_rec.organization_type_lookup_code := cp_supplier.organization_type;
        lv_vendor_rec.vendor_type_lookup_code         := cp_supplier.vendor_type;
        lv_vendor_rec.pay_group_lookup_code            :=cp_supplier.vendor_pay_group;
        lv_vendor_rec.terms_name                             :=cp_supplier.vendor_payment_terms;
        lv_vendor_rec.set_of_books_id                        :=cp_supplier.set_of_books_id;
        lv_vendor_rec.summary_flag := 'N'; 
        lv_vendor_rec.enabled_flag := 'Y';
          --lv_vendor_rec.match_option:='P' ;                     --Match Option

----Api  To Create Vendor

AP_VENDOR_PUB_PKG.CREATE_VENDOR
                    ( p_api_version     => 1,
              p_vendor_rec     => lv_vendor_rec,
                      x_return_status  => lv_return_status,
                      x_msg_count      => lv_msg_count,
                      x_msg_data       => lv_msg_data,
                      x_vendor_id       => lv_vendor_id,
                      x_party_id          => lv_party_id
                     );
             IF (lv_return_status <> 'S') THEN
                   XXC_DISPLAY_MESSAGE('log','Encountered ERROR in supplier creation!!!');
                   XXC_DISPLAY_MESSAGE('log','--------------------------------------');
                   XXC_DISPLAY_MESSAGE('log',lv_msg_data);
                       IF lv_msg_count > 1 THEN
                  FOR i IN 1..lv_msg_count LOOP
                    XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                    XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                  END LOOP;
                              END IF;
              END IF;
COMMIT;
XXC_DISPLAY_MESSAGE('log','return_status: '||lv_return_status);
XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_data);
XXC_DISPLAY_MESSAGE('log','vendor_id: '||lv_vendor_id);
XXC_DISPLAY_MESSAGE('log','party_id: '||lv_party_id);
end LOOP;         --------End Of Supppliers Loop
END XXC_CREATE_SUPPLIERS;
---------end of create suppliers------------------------
-------------------start of create supplier site------------------
PROCEDURE   XXC_CREATE_SUPPLIER_SITES(   p_vendor_number    VARCHAR2 default null,
                                                   p_vendor_site      VARCHAR2 default null)
is
    lv_vendor_site_rec         AP_VENDOR_PUB_PKG.R_VENDOR_SITE_REC_TYPE;
    lv_return_status             VARCHAR2(10);
    lv_msg_count                 NUMBER;
    lv_msg_data                  VARCHAR2(1000);
    lv_vendor_site_id           NUMBER;
    lv_party_site_id              NUMBER;
    lv_location_id                 NUMBER;
BEGIN
    fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
    --fnd_global.apps_initialize(0,20707,201);

---opening loop valid site data

FOR cp_site in c_site(p_vendor_number,p_vendor_site) LOOP
---QUERY TO GET VENDOR_ID
BEGIN
    SELECT vendor_id
    INTO lv_vendor_site_rec.vendor_id
    FROM ap_suppliers
    WHERE segment1 = cp_site.vendor_number ;  --Vendor Number
EXCEPTION 
     WHEN OTHERS THEN
    XXC_DISPLAY_MESSAGE('log','no supplier created'||sqlcode||'  -  '||sqlerrm);
END;
--QUERY TO GET ACCT_PAY_CODE_COMBINATION_ID
BEGIN
SELECT code_combination_id INTO
     lv_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID
 FROM GL_CODE_COMBINATIONS_KFV
 WHERE concatenated_segments=cp_site.liability_account;
 EXCEPTION WHEN OTHERS
  THEN
   XXC_DISPLAY_MESSAGE('log','accts'||SQLCODE||SQLERRM);
END;
----
--QUERY TO GET ACCT_PAY_CODE_COMBINATION_ID
BEGIN
SELECT code_combination_id INTO
     lv_vendor_site_rec.prepay_code_combination_id
 FROM GL_CODE_COMBINATIONS_KFV
 WHERE concatenated_segments=cp_site.prepay_account;
 EXCEPTION WHEN OTHERS
  THEN
   XXC_DISPLAY_MESSAGE('log','accts1'||SQLCODE||SQLERRM);
END;
----
---QUERY TO GET ORG_ID
BEGIN
SELECT  
ORGANIZATION_ID INTO 
lv_vendor_site_rec.org_id FROM
HR_ALL_ORGANIZATION_UNITS  where name =cp_site.operating_unit_name;
EXCEPTION WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log','org_id'||SQLCODE||SQLERRM);
END;
-----------------
----------Assigning Staging Table Data To  Reocrd Type :R_VENDOR_SITE_REC_TYPE

XXC_DISPLAY_MESSAGE('log','Vendor id is :'||lv_vendor_site_rec.vendor_id);
    lv_vendor_site_rec.vendor_site_code             :=cp_site.vendor_site; --Vendor Site Code
    lv_vendor_site_rec.address_line1                   := cp_site.address_line1;       --Address line1
    lv_vendor_site_rec.address_line2                   :=cp_site.address_line2;       --Address line2
        lv_vendor_site_rec.address_line3                   := cp_site.address_line3;       --Address line3
    lv_vendor_site_rec.address_line4                   := cp_site.address_line4;       --Address line4
    lv_vendor_site_rec.city                                  := cp_site.city;               -----city
        lv_vendor_site_rec.state                                := cp_site.state;               ----state
    lv_vendor_site_rec.zip                                   := cp_site.zip_code;              ------zip_code
    lv_vendor_site_rec.country_of_origin_code     := cp_site.country;           --Country
    lv_vendor_site_rec.country                            :=cp_site.country;
    lv_vendor_site_rec.pay_group_lookup_code    := cp_site.vendor_sites_pay_group;--pay group
    lv_vendor_site_rec.terms_name                     := cp_site.vendor_sites_payment_terms;---terms  
        lv_vendor_site_rec.org_name                         := cp_site.operating_unit_name; -- operating unit name
    lv_vendor_site_rec.payment_currency_code    := cp_site.CURRENCY_CODE;       ---.payment_currency_code
    lv_vendor_site_rec.invoice_currency_code       := cp_site.CURRENCY_CODE;      ---invoice currency code
    lv_vendor_site_rec.purchasing_site_flag          :='Y';
    lv_vendor_site_rec.pay_site_flag                     :='Y';
      --Optional
    --lv_vendor_site_rec.rfq_only_site_flag  :='N';

  ------Api to create Venodr sites

AP_VENDOR_PUB_PKG.CREATE_VENDOR_SITE  ( p_api_version             => 1,
                                    p_vendor_site_rec       => lv_vendor_site_rec,
                                    x_return_status           => lv_return_status,
                                    x_msg_count               => lv_msg_count,
                                    x_msg_data                => lv_msg_data,
                                    x_vendor_site_id         => lv_vendor_site_id,
                                    x_party_site_id            => lv_party_site_id,
                                    x_location_id               => lv_location_id
                                    );
             IF (lv_return_status <> 'S') THEN
             XXC_DISPLAY_MESSAGE('log','Encountered ERROR in supplier site creation!!!');
             XXC_DISPLAY_MESSAGE('log','--------------------------------------');
             XXC_DISPLAY_MESSAGE('log',lv_msg_data);
             IF lv_msg_count > 1 THEN
                        FOR i IN 1..lv_msg_count LOOP
            XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                         XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                        END LOOP;
                      END IF;
              END IF;
COMMIT;
XXC_DISPLAY_MESSAGE('log','return_status: '||lv_return_status);
XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_data);
XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_count);
XXC_DISPLAY_MESSAGE('log','vendor_site_id: '||lv_vendor_site_id);
XXC_DISPLAY_MESSAGE('log','party_site_id: '||lv_party_site_id);
XXC_DISPLAY_MESSAGE('log','location_id: '||lv_location_id);
END LOOP;                                       ------------------End of  sites loop
END XXC_CREATE_SUPPLIER_SITES ;
-------------END of create supplier site--------------
----------------start of supplier contact--------------------
PROCEDURE   XXC_CREATE_SUPPLIERS_CONTACTS(   p_vendor_number    VARCHAR2 default null,
                                                     p_vendor_site      VARCHAR2  default null )
IS
lv_vendor_contact_rec      AP_VENDOR_PUB_PKG.R_VENDOR_CONTACT_REC_TYPE;
lv_return_status               VARCHAR2(10);
lv_msg_count                   NUMBER;
lv_msg_data                    VARCHAR2(1000);
lv_vendor_id                    NUMBER;
lv_party_id                       NUMBER;
lv_vendor_contact_id        NUMBER;
lv_per_party_id                NUMBER;
lv_rel_party_id                 NUMBER;
lv_rel_id                          NUMBER;
lv_org_contact_id             NUMBER;
lv_party_site_id                NUMBER;

BEGIN

fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
    --fnd_global.apps_initialize(0,20707,200);
--- opening loop for valid contacts
FOR cp_contact in c_contact(p_vendor_number,p_vendor_site) LOOP

   
--Query to get Vendor Id
   BEGIN
        SELECT vendor_id
        INTO lv_vendor_contact_rec.vendor_id
        FROM ap_suppliers
        WHERE segment1 =cp_contact.vendor_number ;  --Vendor Number
    EXCEPTION WHEN OTHERS THEN
            XXC_DISPLAY_MESSAGE('log','ERROR OCCURED DURING FETCHING VENDOR_ID'||'    '||SQLCODE||' -  '||SQLERRM);
     END;
    --XXC_DISPLAY_MESSAGE('log','Vendor id is :'||lv_vendor_contact_rec.vendor_id);
--Query to get Party Site Id
  BEGIN
        SELECT party_site_id
        INTO   lv_vendor_contact_rec.org_party_site_id
        FROM ap_supplier_sites_all
        WHERE vendor_site_code =cp_contact.vendor_site
        AND vendor_id = lv_vendor_contact_rec.vendor_id ;
   EXCEPTION WHEN OTHERS THEN
    XXC_DISPLAY_MESSAGE('log','ERROR OCCURED DURING FETCHING OF VENDOR_SITE_CODE'||'    '||SQLCODE||'  -  '||SQLERRM);
   END;
---------QUERY TO GET ORG_ID
BEGIN
SELECT  
ORGANIZATION_ID INTO 
lv_vendor_contact_rec.org_id FROM
HR_ALL_ORGANIZATION_UNITS  where name =cp_contact.operating_unit_name;
EXCEPTION WHEN OTHERS THEN
XXC_DISPLAY_MESSAGE('log',SQLCODE||SQLERRM);
END;
-----
    XXC_DISPLAY_MESSAGE('log','Party site id is :'||lv_vendor_contact_rec.org_party_site_id);
  
----Assigning Values to Contacts record type from staging Table

lv_vendor_contact_rec.PERSON_FIRST_NAME := cp_contact.contact_first_name;  --First Name
lv_vendor_contact_rec.PERSON_LAST_NAME  := cp_contact.contact_last_name;   --Last Name
lv_vendor_contact_rec.phone                        := cp_contact.contact_phone;                 --Phone Number
lv_vendor_contact_rec.email_address            := cp_contact.contact_email ;         --Email Address
lv_vendor_contact_rec.fax_phone                  := cp_contact.contact_fax;           -----fax number

-------Api To Create Vendor Contacts

AP_VENDOR_PUB_PKG.CREATE_VENDOR_CONTACT
                            ( p_api_version                => 1,
                              p_vendor_contact_rec     => lv_vendor_contact_rec,
                              x_return_status              => lv_return_status,
                              x_msg_count                  => lv_msg_count,
                              x_msg_data                   => lv_msg_data,
                              x_vendor_contact_id       => lv_vendor_contact_id,
                              x_per_party_id               => lv_per_party_id,
                              x_rel_party_id                => lv_rel_party_id,
                              x_rel_id                         => lv_rel_id,
                              x_org_contact_id            => lv_org_contact_id,
                              x_party_site_id              => lv_party_site_id
                             );
 IF (lv_return_status <> 'S') THEN
             XXC_DISPLAY_MESSAGE('log','Encountered ERROR in supplier contact creation!!!');
             XXC_DISPLAY_MESSAGE('log','--------------------------------------');
             XXC_DISPLAY_MESSAGE('log',lv_msg_data);
             IF lv_msg_count > 1 THEN
                        FOR i IN 1..lv_msg_count LOOP
            XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                         XXC_DISPLAY_MESSAGE('log',substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                        END LOOP;
                      END IF;
              END IF;
 COMMIT;
  XXC_DISPLAY_MESSAGE('log','return_status: '||lv_return_status);
  XXC_DISPLAY_MESSAGE('log','msg_data: '||lv_msg_data);
  XXC_DISPLAY_MESSAGE('log','msg_data_count: '||lv_msg_count);
  XXC_DISPLAY_MESSAGE('log','vendor Contact Id: '||lv_vendor_contact_id);
  XXC_DISPLAY_MESSAGE('log','per_party_id: '||lv_per_party_id);
  XXC_DISPLAY_MESSAGE('log','rel_party_id: '||lv_rel_party_id);
  XXC_DISPLAY_MESSAGE('log','rel_id: '||lv_rel_id);
  XXC_DISPLAY_MESSAGE('log','org_contact_id: '||lv_org_contact_id);
  XXC_DISPLAY_MESSAGE('log','party_site_id: '||lv_party_site_id);
  END LOOP;------------End of Contacts Loop
End XXC_CREATE_SUPPLIERS_CONTACTS;
------------------end of supplier contact-------------------------

----This is The main Procedure it will  Create Records for All valid  Suppliers,Sites and Contacts Information


-----start of main PROCEDURE  -------------------------
 PROCEDURE   XXC_MAIN_SUPPLIER(
                                   ERRBUF OUT NOCOPY VARCHAR2,
                                   RETCODE OUT NOCOPY VARCHAR2,
                                   p_vendor_number VARCHAR2   DEFAULT NULL,
                                   p_vendor_site VARCHAR2 DEFAULT NULL)
is
BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
      XXC_SUPPLIERS_VALIDATE(p_vendor_number);
      XXC_CREATE_SUPPLIERS(p_vendor_number);
      XXC_CREATE_SUPPLIER_SITES(p_vendor_number,p_vendor_site);
      XXC_CREATE_SUPPLIERS_CONTACTS(p_vendor_number,p_vendor_site);               
END xxc_main_supplier;
------------END of main supplier-------------
END XXC_SUP_CREATION_PKG;
/