Query to find Parameters and Value Sets associated with a Concurrent Program

CREATE OR REPLACE PACKAGE XXDEV_FND_CONCPRG_REGISTR

AS

--V_EXECUTABLE VARCHAR2(50):='XXC_ORDER_DETAILS';

--V_APPLICATION VARCHAR2(20):='Order Management';

--V_CPROGRAM VARCHAR2(50):='ORDER_DETAILS_XXC';

V_EXEC NUMBER(2);

V_CP NUMBER(2);

V_REQ_ID NUMBER(10);

PROCEDURE XXC_FND_CONCPRG_EXECUTABLE(V_EXECUTABLE VARCHAR2,V_APPLICATION VARCHAR2);

PROCEDURE XXC_FND_CONCPRG_DEFINE(V_EXECUTABLE VARCHAR2,V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2) ;

PROCEDURE XXC_FND_CONCPRG_PARAMETER(V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2);

PROCEDURE XXC_FND_CONCPRG_ADDTOGROUP(V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2);

PROCEDURE XXC_FND_CONCPRG_SUBMIT(V_CPROGRAM VARCHAR2);

PROCEDURE XXC_FND_CONCPRG_WAIT_REQUEST;

PROCEDURE XXC_FND_CONCPRG_MAIN(V_EXECUTABLE VARCHAR2,V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2);

END;

/

------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY XXDEV_FND_CONCPRG_REGISTR

AS

/*

       Author              :  A. Devendra Naidu

       Created_Date        :  08-AUG-2012

       Last_Update_by      :

       Last_Updated_Date   :

       Purpose             : Used register Concurrent Progaram into apps (by creating

                             executable,define and submit the concurrenr program)

*/

PROCEDURE XXC_FND_CONCPRG_EXECUTABLE(V_EXECUTABLE VARCHAR2,V_APPLICATION VARCHAR2)

IS

BEGIN

   

       SELECT COUNT(*) INTO V_EXEC

       FROM FND_EXECUTABLES

       WHERE EXECUTABLE_NAME=V_EXECUTABLE;

       

       IF V_EXEC=0 THEN

           

           FND_GLOBAL.APPS_INITIALIZE( USER_ID          =>1318,

                                       RESP_ID          =>21623,

                                       RESP_APPL_ID     =>660);            

           FND_PROGRAM.EXECUTABLE (

                               executable          =>V_EXECUTABLE,

                               application         =>V_APPLICATION,

                               short_name          =>V_EXECUTABLE,

                               execution_method    =>'Oracle Reports',

                               execution_file_name =>V_EXECUTABLE

                                  );     

           COMMIT;

           DBMS_OUTPUT.PUT_LINE('SUCCEEFULLY CREATED EXECUTABLE NAME IS '||V_EXECUTABLE) ;

       ELSE

               DBMS_OUTPUT.PUT_LINE('THIS EXECUTABLE '||V_EXECUTABLE||'IS ALREADY CREATED');

       END IF;

       

EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

END XXC_FND_CONCPRG_EXECUTABLE;

  

PROCEDURE XXC_FND_CONCPRG_DEFINE(V_EXECUTABLE VARCHAR2,V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2)

IS

BEGIN

       SELECT COUNT(*)

       INTO V_EXEC

       FROM FND_EXECUTABLES

       WHERE EXECUTABLE_NAME=V_EXECUTABLE;

       

       IF V_EXEC=1 THEN

       

           --DBMS_OUTPUT.PUT_LINE('SUCCEEFULLY CREATED EXECUTABLE NAME IS '||V_EXECUTABLE) ;

           

           SELECT COUNT(*) INTO V_CP

           FROM FND_CONCURRENT_PROGRAMS

           WHERE CONCURRENT_PROGRAM_NAME =V_CPROGRAM;

           

           IF V_CP=0 THEN

           

               FND_GLOBAL.APPS_INITIALIZE( USER_ID          =>1318,

                                           RESP_ID          =>21623,

                                           RESP_APPL_ID    =>660);            

               FND_PROGRAM.REGISTER    (

                               program                 =>V_CPROGRAM,

                               application             =>V_APPLICATION,

                               enabled                 =>'Y',

                               short_name              =>V_CPROGRAM,

                               executable_short_name   =>V_EXECUTABLE,

                               executable_application  =>V_APPLICATION,

                               style                   =>'A4',

                               output_type             =>'TEXT',

                               use_in_srs                =>    'Y'

                           ) ;

               COMMIT;

               DBMS_OUTPUT.PUT_LINE('SUCCEEFULLY CREATED  CONCURRENT PROGRAM  '||V_CPROGRAM) ;

           ELSE

               DBMS_OUTPUT.PUT_LINE('CONCURRENT PROGRAM IS NOT CREATED'||SQLCODE||SQLERRM);    

           END IF;

       ELSE

           DBMS_OUTPUT.PUT_LINE('THIS EXECUTABLE'||V_EXECUTABLE||' IS NOT CREATED ');

       END IF;

EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

END XXC_FND_CONCPRG_DEFINE;

PROCEDURE XXC_FND_CONCPRG_PARAMETER(V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2)

IS

BEGIN

       SELECT COUNT(*) INTO V_CP

       FROM FND_CONCURRENT_PROGRAMS

       WHERE CONCURRENT_PROGRAM_NAME =V_CPROGRAM;

       

         IF V_CP=1 THEN

               

                   BEGIN  

                       --DBMS_OUTPUT.PUT_LINE('SUCCEEFULLY CREATED  CONCURRENT PROGRAM  '||V_EXECUTABLE) ;

                       

                       FND_GLOBAL.APPS_INITIALIZE( USER_ID          =>1318,

                                                   RESP_ID          =>21623,

                                                   RESP_APPL_ID    =>660);

                                          

                       FND_PROGRAM.PARAMETER(

                               program_short_name            =>V_CPROGRAM,

                               application                   =>V_APPLICATION,

                               sequence                      =>10,

                               parameter                     =>'P1',

                              -- enabled                       => 'Y',

                               value_set                     =>'DEV_P1',

                               display_size                  =>50,

                               description_size              =>20,

                               concatenated_description_size =>20,

                               prompt                        =>'P_ORDER1',

                               token                         =>'P_ORDER1'

                                );

                      COMMIT;

                       DBMS_OUTPUT.PUT_LINE(' SUCCESSFULLY CREATED PARAMETER FOR CONCURRENT PROGRAM  '||V_CPROGRAM);               

                       EXCEPTION WHEN OTHERS THEN

                       DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

                    END;                 

                         --PARAMETERS ARE STORED IN FND_DESCR_FLEX_COL_USAGE_VL                

                   BEGIN                      

                       FND_PROGRAM.PARAMETER(

                               program_short_name            =>V_CPROGRAM,

                               application                   =>V_APPLICATION,

                               sequence                      =>20,

                               parameter                     =>'P2',

                              -- enabled                       => 'Y',

                               value_set                     =>'DEV_P1',

                               display_size                  =>50,

                               description_size              =>20,

                               concatenated_description_size =>20,

                               prompt                        =>'P_ORDER2',

                               token                         =>'P_ORDER2'

                                );

                   COMMIT;

                   DBMS_OUTPUT.PUT_LINE(' SUCCESSFULLY CREATED PARAMETER FOR CONCURRENT PROGRAM  '||V_CPROGRAM)  ;               

                       EXCEPTION WHEN OTHERS THEN

                       DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);                    

                   END;

         ELSE

              DBMS_OUTPUT.PUT_LINE('CONCURRENT PROGRAM  '||V_CPROGRAM||' IS NOT CREATED')  ;                              

         END IF;       

EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

END XXC_FND_CONCPRG_PARAMETER;

PROCEDURE XXC_FND_CONCPRG_ADDTOGROUP(V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2)

IS

BEGIN

       SELECT COUNT(*) INTO V_CP

       FROM FND_CONCURRENT_PROGRAMS

       WHERE CONCURRENT_PROGRAM_NAME =V_CPROGRAM;

       

       IF V_CP=1 THEN

       

           BEGIN        

               FND_GLOBAL.APPS_INITIALIZE( USER_ID          =>1318,

                               RESP_ID          =>21623,

                               RESP_APPL_ID    =>660);            

               FND_PROGRAM.ADD_TO_GROUP(

                               program_short_name            =>V_CPROGRAM,

                               program_application           =>V_APPLICATION,

                               request_group                 =>'OM Concurrent Programs',

                               group_application             =>'Order Management'  

                           );

               COMMIT;

               DBMS_OUTPUT.PUT_LINE('SUCCEEFULLY ATTACHED CONCURRENT PROGRAM  TO REQUEST GROUP') ;

           EXCEPTION WHEN OTHERS THEN

               DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

           END;                  

       ELSE

           DBMS_OUTPUT.PUT_LINE('CONCURRENT PROGRAM  '||V_CPROGRAM||' IS NOT CREATED')  ;

       END IF;

       

EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

END XXC_FND_CONCPRG_ADDTOGROUP;

PROCEDURE XXC_FND_CONCPRG_SUBMIT(V_CPROGRAM VARCHAR2)

IS

BEGIN

       

       

               BEGIN

               

                   FND_GLOBAL.APPS_INITIALIZE( USER_ID          =>1318,

                                               RESP_ID          =>21623,

                                               RESP_APPL_ID    =>660);            

                   V_REQ_ID:=FND_REQUEST.SUBMIT_REQUEST(

                                               application => 'ont',

                                               program    =>V_CPROGRAM,

                                               ARGUMENT1 =>'10',

                                               ARGUMENT2 =>'1000'

                                                     );

               COMMIT;

               DBMS_OUTPUT.PUT_LINE('SUCCESSFULLY SUBMITTED. REQUEST ID IS'||V_REQ_ID);

               EXCEPTION WHEN OTHERS THEN

                   DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

               END;

                       

EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

END XXC_FND_CONCPRG_SUBMIT;

PROCEDURE XXC_FND_CONCPRG_WAIT_REQUEST

IS

V_REQ_ID2 NUMBER(2);

V_Phase Varchar2(10);

V_Status Varchar2(10);

V_Phase1 Varchar2(10);

V_Status1 Varchar2(10);

V_Phase2 Varchar2(10);

V_Status2 Varchar2(10);

V_b BOOLEAN;

BEGIN              

               SELECT COUNT(*) INTO V_REQ_ID2

               FROM FND_CONCURRENT_REQUESTS

               WHERE REQUEST_ID=V_REQ_ID;

               

               IF V_REQ_ID2=1 THEN

               

                   --DBMS_OUTPUT.PUT_LINE('SUCCEEFULLY SUBMITTED REQUEST ID IS '||V_REQ_ID) ;  

                   

                   BEGIN                                        

                   

                       FND_GLOBAL.APPS_INITIALIZE(user_id =>1318,resp_id=>21623,resp_appl_id=>660);

                   

                       V_B:=FND_CONCURRENT.wait_for_request(

                                                           request_id =>V_Req_Id,

                                                           interval   => 60,

                                                           max_wait   => 0,

                                                           phase      => v_phase,

                                                           status     => v_status,

                                                           dev_phase  => v_phase1,

                                                           dev_status => v_status1,

                                                           message    => v_Phase2

                                                       );

                   COMMIT;

                   

                   EXCEPTION WHEN OTHERS THEN

                       DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

                   END;                                   

         

                   if v_phase='Completed'and v_status ='Normal' Then

                       DBMS_OUTPUT.PUT_LINE ( V_req_id ||' Sucessfully Completed With Status Normal');

                   ELSIF v_phase='Completed'and v_status ='Error' Then

                       DBMS_OUTPUT.PUT_LINE ( V_req_id ||' Sucessfully Completed With Status Error');

                   ELSIF v_phase='Completed'and v_status ='Warning' Then

                       DBMS_OUTPUT.PUT_LINE (V_req_id ||' Sucessfully Completed With Status Warning');

                   ELSE

                       DBMS_OUTPUT.PUT_LINE (V_req_id || V_Phase||' '||V_Status);

                   END IF;

                   

                   

                                   

               ELSE

                   DBMS_OUTPUT.PUT_LINE('ERROR FOR SUBMITION THE REQUEST. REQUEST ID IS '||V_REQ_ID) ;    

                   

               END IF;

               

END XXC_FND_CONCPRG_WAIT_REQUEST;

PROCEDURE XXC_FND_CONCPRG_MAIN(V_EXECUTABLE VARCHAR2,V_APPLICATION VARCHAR2,V_CPROGRAM VARCHAR2)

IS

BEGIN

       XXDEV_FND_CONCPRG_REGISTRATION.XXC_FND_CONCPRG_EXECUTABLE;

       XXDEV_FND_CONCPRG_REGISTRATION.XXC_FND_CONCPRG_DEFINE;

       XXDEV_FND_CONCPRG_REGISTRATION.XXC_FND_CONCPRG_PARAMETER;

       XXDEV_FND_CONCPRG_REGISTRATION.XXC_FND_CONCPRG_ADDTOGROUP;

       XXDEV_FND_CONCPRG_REGISTRATION.XXC_FND_CONCPRG_SUBMIT;

       XXDEV_FND_CONCPRG_REGISTRATION.XXC_FND_CONCPRG_WAIT_REQUEST;

       

EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE('ERROR OUT HERE'||SQLCODE||SQLERRM);

END XXC_FND_CONCPRG_MAIN;

END XXDEV_FND_CONCPRG_REGISTR;

/

EXEC XXDEV_FND_CONCPRG_REGISTR.XXC_FND_CONCPRG_MAIN('XXC_ORDER_DETAILS','Order Management','ORDER_DETAILS_XXC')

Supplier Interface

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----------

APPROVED PO REQUISITION REPORT QUERY

 
   APPROVED PO REQUISITION REPORT(PO)
  -----------------------------------

 THIS REPORT SHOWS THE INFORMATION ABOUT APPROVED PURCHASE REQUISITION.THIS
 REPRT CONTAIN BOTHE INTERNAL & PURCHASE REQUISITIONS WHICH ARE APPROVED BY
 THE AUTHORITIES

 SELECT PRHA.SEGMENT1 POREQNO,
        PRHA.TYPE_LOOKUP_CODE POREQTYPE,
        PRHA.LAST_UPDATE_DATE POREQDATE,
        PRHA.PREPARER_ID,
        MSIB.SEGMENT1 ITEM,
        MSIB.DESCRIPTION,
        PRLA.QUANTITY,
        PRLA.RATE PRICE,
        PRLA.QUANTITY*PRLA.UNIT_PRICE AMOUNT,
        GCC.CHART_OF_ACCOUNTS_ID,
        PP.PERSON_ID REQUESTOR,
        HL.ADDRESS_LINE_1 LOCNAME,
        OOD.ORGANIZATION_NAME
 FROM PO_REQUISITION_HEADERS_ALL PRHA,
   PO_REQUISITION_LINES_ALL PRLA,
   PO_REQ_DISTRIBUTIONS_ALL PRDA,
   PER_ALL_PEOPLE_F PP,
   MTL_SYSTEM_ITEMS_B MSIB,
   GL_CODE_COMBINATIONS GCC,
   HR_LOCATIONS HL,
   ORG_ORGANIZATION_DEFINITIONS OOD
 WHERE PRHA.PREPARER_ID=PP.PERSON_ID
   AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
   AND PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
   AND PRDA.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
   AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
   AND PRLA.DELIVER_TO_LOCATION_ID=HL.LOCATION_ID
   AND MSIB.ORGANIZATION_ID=OOD.ORGANIZATION_ID
   AND PRHA.AUTHORIZATION_STATUS='APPROVER'