Chandra Sekhar Kadali - Oracle Apps Knowledge Sharing: Precision Retail SKU Master Extract - Dumps All onhand quantity details to a File

CREATE OR REPLACE PACKAGE Xxcofi_Pri_Extract_Pkg AUTHID CURRENT_USER
IS
/* ------------------------------------------------------------------------------------ */
/* Program Name : XXCOFI_PRI_EXTRACT_PKG                                               */
/*                                                                                      */
/* TYPE         : PL/SQL Package                                                       */
/*                                                                                      */
/* Input Parms : p_file_name         IN       VARCHAR                                 */
/*                 p_file_directory    IN       VARCHAR2                                */
/* Output Parms :                                                                      */
/*                                                                                      */
/* Table Access : po_headers_all             - Select                                  */
/*                 po_lines_all               - Select                                  */
/*                 po_line_locations_all      - Select                                  */
/*                 po_vendor_sites_all        - Select                                  */
/*                 cst_items_cost             - Select                                  */
/*                 hr_locations_all           - Select                                  */
/*                 mtl_categories_b           - Select                                  */
/*                 mtl_system_items_b         - Select                                  */
/*                 org_acct_periods           - Select                                  */
/*                 po_asl_attributes          - Select                                  */
/*                 po_vendor_sites_all        - Select                                  */
/*                                                                                      */


    g_request_id       NUMBER;
    g_tstmp            VARCHAR2(19);
    g_errcode          NUMBER;
    g_program_name     fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE;

    PROCEDURE MAIN( errbuf                         OUT VARCHAR2
                  , retcode                        OUT NUMBER
                  , p_file_name                    IN VARCHAR2
                  , p_file_directory               IN VARCHAR2);


    PROCEDURE oh_extract(p_file_name               IN VARCHAR2
                       , p_file_directory          IN VARCHAR2) ;


    PROCEDURE generate_output(p_count              IN NUMBER);


    PROCEDURE WRITE(p_type                         IN VARCHAR2
                  , p_message                      IN VARCHAR2);


END Xxcofi_Pri_Extract_Pkg;
/
CREATE OR REPLACE PACKAGE BODY Xxcofi_Pri_Extract_Pkg
AS

PROCEDURE main( errbuf              OUT      VARCHAR2
              , retcode             OUT      NUMBER
              , p_file_name         IN       VARCHAR2
              , p_file_directory    IN       VARCHAR2)
IS
/************************************************************************
Purpose : Main program calls oh_extract procedure which opens a utl
            file and writes data into it.
*************************************************************************/
BEGIN

      /*Get request id for use in other procedures*/
      g_request_id := Fnd_Global.conc_request_id ;

      BEGIN /*Get program name for use in other procedures*/

          SELECT user_concurrent_program_name ,
                 TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24:MI:SS')
          INTO   g_program_name, g_tstmp
          FROM   fnd_concurrent_programs_vl
          WHERE concurrent_program_id = Fnd_Global.conc_program_id;

      EXCEPTION
           WHEN NO_DATA_FOUND
           THEN

               Xxcofi_Pri_Extract_Pkg.WRITE('L','Program Name not found for Program id '
                                                   || Fnd_Global.conc_program_id);
               retcode := 2; --##4
               RETURN;       --##4
           WHEN OTHERS
           THEN

               Xxcofi_Pri_Extract_Pkg.WRITE('L','Error while selecting the program name ');
               Xxcofi_Pri_Extract_Pkg.WRITE('L', SQLCODE || ' : ' ||     SQLERRM);
               retcode := 2; --##4
               RETURN;       --##4
      END;

      oh_extract(p_file_name, p_file_directory);

      IF g_errcode=2
      THEN
         retcode := 2;
         RETURN; --##4
      END IF;

    COMMIT;

EXCEPTION
     WHEN OTHERS
     THEN
         Xxcofi_Pri_Extract_Pkg.WRITE('L','Program terminated due to error '||CHR(10)
                   || SQLCODE|| SQLERRM);
         retcode := 2; --##4
         RETURN; --##4
END main;

/*****************************************************************************/
PROCEDURE oh_extract(p_file_name      IN VARCHAR2
                   , p_file_directory IN VARCHAR2)
IS
/************************************************************************
Purpose : This procedure extracts PO data from COFI to RLDM.
*************************************************************************/
    -- Cursor pl_info_cur retrives all oh transaction quantities
    CURSOR pl_info_cur
    IS
SELECT ITM.SEGMENT1                 PRI_SKU,
               ITM.ATTRIBUTE11              PRI_A11,
               ITM.ATTRIBUTE12              PRI_A12,
               ITM.ATTRIBUTE14              PRI_A14,
               ITM.ATTRIBUTE15              PRI_A15,
               POS.ATTRIBUTE3               PRI_A03,
               ITM.CREATION_DATE            PRI_CRE,
               POS.DISABLE_FLAG             PRI_DIS
FROM   MTL_SYSTEM_ITEMS_B        ITM,
         PO_APPROVED_SUPPLIER_LIST POS
WHERE 1=1 --ITM.INVENTORY_ASSET_FLAG = 'Y' Commented by Yogesh 23-Jun-11 for getting it on SKU Master file MCF Tax
    AND ITM.ORGANIZATION_ID      = 22
    AND ITM.INVENTORY_ITEM_ID    = POS.ITEM_ID(+)
    AND ITM.ORGANIZATION_ID      = POS.OWNING_ORGANIZATION_ID(+)
    AND POS.ATTRIBUTE5(+)           = 'Primary';
    -- Variables
    v_file_handle             UTL_FILE.FILE_TYPE;
    v_file                    VARCHAR2(50) := p_file_name;
    v_file_directory          VARCHAR2(50) := p_file_directory;
    v_inventory_item_id       NUMBER;
    v_organization_id         NUMBER;
    v_transaction_quantity    NUMBER;
    v_price1                  VARCHAR2(50) := NULL;
    v_price2                  VARCHAR2(50) := NULL;
    v_price3                  VARCHAR2(50) := NULL;
    v_po_status               VARCHAR2(30) := NULL;
    v_indx                    NUMBER       := 0;
    v_master_org_id           NUMBER;
    v_po_error                VARCHAR(1)   := 'S';
    v_pperiod                 NUMBER := NULL;
    v_pyear                   NUMBER := NULL;
    v_processing_lead_time    NUMBER := NULL;

    v_write_flag              BOOLEAN:=TRUE;

    -- Other temporary Variable Initializations
    v_tmp_cnt                 NUMBER := NULL;
    v_err_msg                 VARCHAR2(1000):= NULL;
    v_error_flag              NUMBER := 1;
    v_cancel_dte              DATE   := NULL;
    v_appt_dte                DATE   := NULL;
    v_sys_dte                 DATE   := NULL;
    v_cost_type_id            NUMBER := 0;
    v_poli_retail_pr          NUMBER := NULL;

    -- ##4

    v_rec       VARCHAR2(500);
    v_rec_count NUMBER :=0;

BEGIN

    v_file_handle := UTL_FILE.FOPEN(v_file_directory,v_file, 'w', 32000);
   BEGIN
       BEGIN
         UTL_FILE.PUT_LINE(v_file_handle,'FHEAD'||G_TSTMP);
         FOR pl_info_rec IN pl_info_cur
         LOOP
             v_rec_count := v_rec_count+1;
             UTL_FILE.PUT_LINE(v_file_handle,
               LPAD(pl_info_rec.PRI_SKU,8,'0')
             ||','
             ||RPAD(NVL(pl_info_rec.PRI_A11,'NULL'),4,' ')
             ||','
             ||RPAD(NVL(pl_info_rec.PRI_A12,'NULL'),20,' ')
             ||','
             ||RPAD(NVL(pl_info_rec.PRI_A14,'NULL'),20,' ')
             ||','
             ||RPAD(NVL(pl_info_rec.PRI_A15,'NULL'),20,' ')
             ||','
             ||RPAD(NVL(pl_info_rec.PRI_A03,'NULL'),20,' ')
            ||','
             ||TO_CHAR(pl_info_rec.PRI_CRE,'YYYY-MM-DD')
            ||','
             ||NVL(pl_info_rec.PRI_DIS,'A'));
         END LOOP;
        EXCEPTION
             WHEN OTHERS
             THEN
                  WRITE('L', 'lt_info_rec loop terminated due to exception '||CHR(10)||
                              SQLCODE||' : '|| SQLERRM);
        END;

        UTL_FILE.PUT_LINE(v_file_handle,'FTAIL'||LPAD(NVL(TO_CHAR(v_rec_count),'0'),10,'0'));

        UTL_FILE.FFLUSH(v_file_handle);
        UTL_FILE.FCLOSE(v_file_handle);

        -- Printing Output
        generate_output(v_rec_count);

    EXCEPTION
         WHEN UTL_FILE.INVALID_PATH
         THEN
              Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - invalid path');
              UTL_FILE.FCLOSE(v_file_handle);
              g_errcode:=2;
              RETURN;

         WHEN UTL_FILE.INVALID_MODE
         THEN
             Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - Invalid Mode');
             UTL_FILE.FCLOSE(v_file_handle);
             g_errcode:=2;
             RETURN;

         WHEN UTL_FILE.INVALID_OPERATION
         THEN
             Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - Invalid Operation');
             UTL_FILE.FCLOSE(v_file_handle);
             g_errcode:=2;
             RETURN;

         WHEN UTL_FILE.INVALID_FILEHANDLE
         THEN
              Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - Invalid Filehandle');
              UTL_FILE.FCLOSE(v_file_handle);
              g_errcode:=2;
              RETURN;

         WHEN UTL_FILE.WRITE_ERROR
         THEN
              Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - Write Error');
              UTL_FILE.FCLOSE(v_file_handle);
              g_errcode:=2;
              RETURN;

         WHEN UTL_FILE.READ_ERROR
         THEN
              Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - Read Error');
              UTL_FILE.FCLOSE(v_file_handle);
              g_errcode:=2;
              RETURN;

         WHEN UTL_FILE.INTERNAL_ERROR
         THEN
             Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - Internal Error');
             UTL_FILE.FCLOSE(v_file_handle);
             g_errcode:=2;
             RETURN;

         WHEN OTHERS
         THEN
              Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION RAISED - Other Error'
                                             || SQLCODE || SQLERRM);
              UTL_FILE.FCLOSE(v_file_handle);
             g_errcode:=2;
             RETURN;

    END;

EXCEPTION
     WHEN OTHERS
     THEN
          WRITE('L', 'Program oh_extract terminated due to error '||CHR(10)
                     || SQLCODE|| ' : '|| SQLERRM);
          g_errcode:=2;
          RETURN;
END;

/************************************************************************/
PROCEDURE WRITE(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : This procedure writes to the output file or log file depending
                on the parameter p_type passed.
*************************************************************************/
BEGIN

    IF p_type = 'L'
    THEN
       Fnd_File.put_line (Fnd_File.LOG, p_message);
    ELSIF p_type = 'O'
    THEN
       Fnd_File.put_line (Fnd_File.output, p_message);
    END IF;

END WRITE;

/************************************************************************/
PROCEDURE generate_output (p_count             IN NUMBER)
IS
/************************************************************************
Purpose : This procedure generates the output file which gives the status
          of number of records extracted.
*************************************************************************/
BEGIN

      WRITE('L','');
      WRITE('L', '________________________________________________________________________________');
      WRITE('L','');
      WRITE('L','          Request Name : '||g_program_name);
      WRITE('L','          Request Id   : '||g_request_id);
      WRITE('L','          Date         : '||TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
    --write('L','          From Date    : '||TO_CHAR(p_last_extract_date,'YYYY-MM-DD HH24:MI:SS')); -- ##1
    --write('L','          To   Date    : '||TO_CHAR(p_to_date          ,'YYYY-MM-DD HH24:MI:SS')); -- ##1
      WRITE('L','');
      WRITE('L','');
      WRITE('L','Total Number of records extracted in the output file : '||p_count);
      WRITE('L', '________________________________________________________________________________');

EXCEPTION
     WHEN OTHERS
     THEN
          WRITE('O','Error while writing the log : '||CHR(10)||SQLCODE||SQLERRM);
END generate_output;

END Xxcofi_Pri_Extract_Pkg;
/
EXIT
/

Chandra Sekhar Kadali - Oracle Apps Knowledge Sharing

Visitors

Wednesday, October 10, 2012

Precision Retail SKU Master Extract - Dumps All onhand quantity details to a File

No comments:

Post a Comment

Translate

Viewers

Labels