Secondary Suppliers Extract

/* Thanks to Hareesha for providing this Script */
CREATE OR REPLACE PACKAGE APPS.XXCOFI_SEC_SUPP_RETEK_PKG
AS
/* -------------------------------------------------------------------------- */
/*  Program Name : XXCOFI_SEC_SUPP_RETEK_PKG                                  */
/*                                                                            */
/*  TYPE         : PL/SQL Package                                             */
/*                                                                            */
/*  Input Parms  :                                                            */
/*                                                                            */
/*  Output Parms : --     errbuf, retcode                                     */
/*                                                                            */
/*  Table Access : --                                                         */
/*                                                                            */
/*  AUTHOR       : Hareesha Rodda                                             */
/*                                                                            */
/*  DATE         : 06-DEC-2010                                                */
/*                                                                            */
/*  VERSION      : 1.0                                                        */
/*                                                                            */
/*  DESCRIPTION  : This package will be used for creation of outbound file    */
/*                 for secondary suppliers,which will be imported to Retek    */
/*                                                                            */
/*                                                                            */
/*                                                                            */
/*  CHANGE HISTORY                                                            */
/* -------------------------------------------------------------------------- */
/* DATE        AUTHOR                          VERSION  REASON                */
/* -------------------------------------------------------------------------- */
/* 06/12/2010  Hareesha Rodda                  1.0      Initial creation      */
/* -------------------------------------------------------------------------- */

PROCEDURE XXCOFI_SEC_SUPP_EXTRACT(
     ERRBUF OUT VARCHAR2,
     RETCODE OUT VARCHAR2    
     );
END XXCOFI_SEC_SUPP_RETEK_PKG;
/

CREATE OR REPLACE PACKAGE BODY APPS.XXCOFI_SEC_SUPP_RETEK_PKG
IS
/* -------------------------------------------------------------------------- */
/*  Program Name : XXCOFI_SEC_SUPP_RETEK_PKG                                  */
/*                                                                            */
/*  TYPE         : PL/SQL Package                                             */
/*                                                                            */
/*  Input Parms  :                                                            */
/*                                                                            */
/*  Output Parms : --     errbuf, retcode                                     */
/*                                                                            */
/*  Table Access : --                                                         */
/*                                                                            */
/*  AUTHOR       : Hareesha Rodda                                             */
/*                                                                            */
/*  DATE         : 06-DEC-2010                                                */
/*                                                                            */
/*  VERSION      : 1.0                                                        */
/*                                                                            */
/*  DESCRIPTION  : This package will be used for creation of outbound file    */
/*                 for secondary suppliers,which will be imported to Retek    */
/*                                                                            */
/*                                                                            */
/*                                                                            */
/*  CHANGE HISTORY                                                            */
/* -------------------------------------------------------------------------- */
/* DATE        AUTHOR                          VERSION  REASON                */
/* -------------------------------------------------------------------------- */
/* 06/12/2010  Hareesha Rodda                  1.0      Initial creation      */
/* 02-Jun-2011 Yogesh                          1.1       Changed output path */
/* -------------------------------------------------------------------------- */
   PROCEDURE XXCOFI_SEC_SUPP_EXTRACT (
      errbuf                OUT      VARCHAR2
     ,retcode               OUT      VARCHAR2
     )
   IS
      CURSOR out_record_c
      IS
         SELECT DISTINCT
         si.segment1              SKU
        ,vs.vendor_site_code      Sec_supplier
        ,decode(round(si.list_price_per_unit,2)*100,null,'0',round(si.list_price_per_unit,2)*100) Cost_per_unit
        ,mde3.element_value   VPN
        ,mde4.element_value   Supp_Colour
        ,mde5.element_value   Supp_Size
        ,decode(round(si.unit_length,4)*10000,null,'0',round(si.unit_length,4)*10000)      Item_unit_len
        ,decode(round(si.unit_height,4)*10000,null,'0',round(si.unit_height,4)*10000)       Item_unit_ht
        ,decode(round(si.unit_weight,4)*10000,null,'0',round(si.unit_weight,4)*10000)      Item_unit_wt
        ,decode(round(si.unit_width,4)*10000,null,'0',round(si.unit_width,4) *10000)       Item_unit_wid
        ,decode (round(to_number(sl.attribute3),4)*10000,null,'0',round(to_number(sl.attribute3),4)*10000)        ELC_AMT
    FROM mtl_system_items_b           si
        ,inv.mtl_descr_element_values mde3
        ,inv.mtl_descr_element_values mde4
        ,inv.mtl_descr_element_values mde5
        ,po_vendors                   vn
        ,po_vendor_sites_all          vs
        ,po_approved_supplier_list    sl
    WHERE   1=1
    AND     si.organization_id =22
    ---AND     si.segment1 in('03004863')
    AND     si.inventory_item_id = mde3.inventory_item_id
    AND     si.inventory_item_id = mde4.inventory_item_id
    AND     si.inventory_item_id = mde5.inventory_item_id
    AND     mde3.element_name    = 'Model/Style Number'
    AND     mde4.element_name    = 'Colour'
    AND     mde5.element_name    = 'Size'
    AND     sl.attribute5       <> 'Primary'
    AND     sl.item_id           = si.inventory_item_id
    AND     vs.vendor_id         = sl.vendor_id
    AND     vs.vendor_site_id    = sl.vendor_site_id
    AND     vn.vendor_id         = sl.vendor_id
    AND     NVL(sl.disable_flag,'N') = 'N'
    ORDER BY 1,2
       ;

      v_file_dc                     UTL_FILE.file_type;
      buff_size                     NUMBER := 20000;
      l_top                         VARCHAR2(200) := NULL;
       l_top1                         VARCHAR2(200) := NULL;
      l_record_no                   NUMBER := 0;
      v_file_name                   varchar2(50);
      v_file_name_arc               varchar2(50);

   BEGIN
  
      v_file_name  := 'sec_supplier.dat' ;
     
      fnd_profile.get('XXCOFIDATA_OUT', l_top);
      fnd_profile.get('XXHBC_APARCHIVE',l_top1);
     
      l_top:= l_top||'/ap/retek';  -- Yogesh for Retek
     
      fnd_file.put_line(fnd_file.log,'Extract File path is : '||l_top);
      fnd_file.put_line(fnd_file.log,'Extract File name is : '||v_file_name);
     
      -- Yogesh 2-Jun-2011
     
      v_file_dc :=
            UTL_FILE.fopen (l_top, v_file_name,'w',buff_size);

      FOR out_rec IN out_record_c LOOP

           UTL_FILE.put_line (v_file_dc
                                 ,    LPAD (nvl(out_rec.SKU,'0'), 8, '0')
                                   || LPAD (nvl(out_rec.Sec_supplier,'0'), 10, '0')
                                   || LPAD (out_rec.cost_per_unit, 10, '0')
                                   || RPAD (nvl(out_rec.VPN,' '), 30, ' ')
                                   || RPAD (nvl(out_rec.Supp_Colour,' '), 30, ' ')
                                   || RPAD (nvl(out_rec.Supp_Size,' '), 30, ' ')
                                   || LPAD (out_rec.Item_unit_len, 10, '0')
                                   || LPAD (out_rec.Item_unit_ht, 10, '0')
                                   || LPAD (out_rec.Item_unit_wt, 10, '0')
                                   || LPAD (out_rec.Item_unit_wid, 10, '0')
                                   || LPAD (out_rec.ELC_AMT, 7, '0'));


           l_record_no := l_record_no + 1;

      END LOOP;

      fnd_file.put_line(fnd_file.log, 'Number of Records created  to Secondary Vendor Extract File' || ' : ' || l_record_no);
      UTL_FILE.fflush(v_file_dc);
      UTL_FILE.fclose(v_file_dc);
     
      v_file_name_arc := 'sec_supplier' || TO_CHAR (SYSDATE, 'yyyymmddhh24miss') || '.dat';
     
      -- archive the file for future reference in  archive/ap directory
     
      UTL_FILE.fcopy (l_top, v_file_name, 'XXHBC_APARCHIVE', v_file_name_arc);
     
      fnd_file.put_line(fnd_file.log,'Archive File path is : '||'XXHBC_APARCHIVE');
      fnd_file.put_line(fnd_file.log,'Archive File name is : '||v_file_name_arc );
     
     
     
      errbuf := 'SECONDARY SUPPLIERS EXTRACT COMPLETED SUCCESSFULLY';
      retcode := '0';
      fnd_file.put_line (fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');
      fnd_file.CLOSE;   -- (FND_FILE.OUTPUT);
   EXCEPTION
      WHEN UTL_FILE.invalid_filehandle
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID FILE HANDLE';
         retcode                    := '1';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID FILE HANDLE : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.invalid_path
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID PATH';
         retcode                    := '2';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID PATH : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.invalid_mode
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID MODE';
         retcode                    := '3';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID MODE : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.invalid_operation
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID OPERATION';
         retcode                    := '4';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID OPERATION : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.read_error
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'READ ERROR';
         retcode                    := '5';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is READ ERROR : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.write_error
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'WRITE ERROR';
         retcode                    := '6';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is WRITE ERROR : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.internal_error
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INTERNAL ERROR';
         retcode                    := '7';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INTERNAL ERROR : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN NO_DATA_FOUND
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'NO DATA FOUND';
         retcode                    := '8';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' Error is NO DATA FOUND :' || SQLERRM);
         fnd_file.CLOSE;
      WHEN OTHERS
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'OTHERS ';
         retcode                    := '9';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' Error is OTHERS : ' || SQLERRM);
         fnd_file.CLOSE;
   END;
END XXCOFI_SEC_SUPP_RETEK_PKG;
/
SHOW ERROR
/
EXIT

Package Execution from Batch Server

 We Need to Pass 3 Parameters to the Script to Execute the Package.
They are
1.Database server name
2.Package name.procedure name
3.procedure parameter delimited by commas, if any

#!/usr/bin/ksh
## ---------------------------------------------------------------------------------------
##   Program Name  xxmcf_runpkg_frm_batchsrv.prog
##   TYPE          shell script
##   Input Params 
##                 1.Database server name
##                 2.Package name.procedure name
##                 3.procedure params delimited by commas, if any
##
##
##   Output Parms  NA
##  
##            
##   AUTHOR        Chandra Sekhar K
##   DATE          29-NOV-2010
##   VERSION       1.0
##   DESCRIPTION   This script will be executed on CCDEV40,
##                 and will run a package existing on CCDEV39
##--------------------------------------------------------------------------------------------


## Assigning parameters

DB_SRV=$1
PROC_NAME=$2
PROC_PARAMS=$3

export ORACLE_BASE=/mcf11gR1/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11gR1
PATH=$PATH:$ORACLE_HOME/bin:.export PATH

echo "-----------PARAMETERS ENTERED-------------"
echo " "
echo "procedure name is " $PROC_NAME
echo "procedure params are "$PROC_PARAMS
echo " "


echo "----------RUNNING PACKAGE STARTS-------------"
echo " "

  conn_id=`ls|grep $1 /MCFFTP/scripts/bin/conn_string.txt | cut -d "=" -f  2-`

if [ "$3" = "" ]
then
   echo "Package params are null."

     sqlplus $conn_id <<-EOF21

   set heading off
   set feedback off
   set verify off
   set serveroutput on

   begin
     $PROC_NAME;
   end;
   /

   COMMIT;

   exit

EOF21

else
  echo "Procedure requires parameters."


     sqlplus $conn_id <<-EOF22
   set heading off
   set feedback off
   set verify off
   set serveroutput on


  begin
    $PROC_NAME($PROC_PARAMS);
  end;
  /

  COMMIT;

  exit

EOF22

fi


exit 1

Related Items Extract

 CREATE OR REPLACE PACKAGE APPS.xxcofi_related_items_pkg
AS
/* -------------------------------------------------------------------------- */
/*  Program Name : xxcofi_related_items_pkg                                   */
/*                                                                            */
/*  TYPE         : PL/SQL Package                                             */
/*                                                                            */
/*  Input Parms  :                                                            */
/*                                                                            */
/*  Output Parms : --                                                         */
/*                                                                            */
/*  Table Access : --                                                         */
/*                                                                            */
/*  AUTHOR       : Chandra Sekhar                                             */
/*                                                                            */
/*  DATE         : 01-Jun-2010                                                */
/*                                                                            */
/*  VERSION      : 1.0                                                        */
/*                                                                            */
/*  DESCRIPTION  : This package will extract related items and generate flatfile*/
/*                                                                            */
/*                                                                            */
/*                                                                            */
/*  CHANGE HISTORY                                                            */
/* -------------------------------------------------------------------------- */
/* DATE        AUTHOR    VERSION  REASON                                      */
/* -------------------------------------------------------------------------- */
/* 01/06/10    Chandra   1.0      Initial creation                            */
/* -------------------------------------------------------------------------- */
   PROCEDURE main(
      errbuf                     OUT      VARCHAR2
    , retcode                    OUT      NUMBER
    , p_number                   IN       NUMBER
   );
END xxcofi_related_items_pkg;
/
/
CREATE OR REPLACE PACKAGE BODY APPS.xxcofi_related_items_pkg
AS
/* -------------------------------------------------------------------------- */
/*  Program Name : xxcofi_related_items_pkg                                   */
/*                                                                            */
/*  TYPE         : PL/SQL Package                                             */
/*                                                                            */
/*  Input Parms  :                                                            */
/*                                                                            */
/*  Output Parms : --                                                         */
/*                                                                            */
/*  Table Access : --                                                         */
/*                                                                            */
/*  AUTHOR       : Chandra Sekhar                                             */
/*                                                                            */
/*  DATE         : 01-Jun-2010                                                */
/*                                                                            */
/*  VERSION      : 1.0                                                        */
/*                                                                            */
/*  DESCRIPTION  : This package will extract related items and generate flatfile*/
/*                                                                            */
/*                                                                            */
/*                                                                            */
/*  CHANGE HISTORY                                                            */
/* -------------------------------------------------------------------------- */
/* DATE        AUTHOR    VERSION  REASON                                      */
/* -------------------------------------------------------------------------- */
/* 01/06/10    Chandra   1.0      Initial creation                            */
/* 04/20/11    Ashwini   1.1      Convert relationship only for converted items */
/* -------------------------------------------------------------------------- */
   PROCEDURE main(
      errbuf                     OUT      VARCHAR2
    , retcode                    OUT      NUMBER
    ,p_number in number
   )
   AS
/*****************************************************************************************/
---    Procedure to create report outbound File
/*****************************************************************************************/
    -----Variables Declaration-----
      report_name                   VARCHAR2(70) := 'XXCOFI_RELATED_ITEMS_EXTRACT';
      col01e_desc                   VARCHAR2(30) := 'SKU_Number';
      col02e_desc                   VARCHAR2(30) := 'Enterprise_Code';
      col03e_desc                   VARCHAR2(30) := 'UOM';
      col04e_desc                   VARCHAR2(30) := 'Action';
      col05e_desc                   VARCHAR2(30) := 'Associated_quantity';
      col06e_desc                   VARCHAR2(30) := 'Relation_Type';
      col07e_desc                   VARCHAR2(30) := 'Effective_From';
      col08e_desc                   VARCHAR2(30) := 'Effective_To';
      col09e_desc                   VARCHAR2(30) := 'Related_SKU';
      col10e_desc                   VARCHAR2(30) := 'Enterprise_Code';
      col11e_desc                   VARCHAR2(30) := 'UOM';
      v_file_dc                     UTL_FILE.file_type;
      buff_size                     NUMBER := 24576;
      l_record_no                   NUMBER := 0;
      l_top                         VARCHAR2(200) := NULL;
      l_date                        VARCHAR2(60) := NULL;
      l_mode                        VARCHAR2(5) := NULL;
      l_item_seq_num                NUMBER;
      l_extract_flag                VARCHAR2(30);
      l_update_count                NUMBER := 0;
      l_total_insert_count          NUMBER := 0;
      l_additional_insert_count     NUMBER := 0;

-- Cursor to Extract the Related Items Information

      CURSOR cur_item_insert
      IS
      SELECT DISTINCT msi.segment1 f01,
                      decode(SUBSTR(TRIM (msi.primary_uom_code), -1, 1),'Y','DAYS',muomt.unit_of_measure_tl) f04, --muomt.unit_of_measure_tl f04,
                      r_msi.segment1 f02,
                      decode(SUBSTR(TRIM (r_msi.primary_uom_code), -1, 1),'Y','DAYS',r_muomt.unit_of_measure_tl) f05, -- r_muomt.unit_of_measure_tl f05,
                      flv.meaning f03,
                      mri.reciprocal_flag f06
           FROM mtl_related_items mri,
                mtl_system_items_b msi,
                mtl_system_items_b r_msi,
                mtl_units_of_measure_tl muomt,
                mtl_units_of_measure_tl r_muomt,
                fnd_lookup_values_vl flv
          WHERE mri.inventory_item_id = msi.inventory_item_id
            AND mri.related_item_id = r_msi.inventory_item_id
            AND msi.organization_id = 22
            AND r_msi.organization_id = 22
            AND msi.inventory_item_status_code <> 'Suppressed'
            AND r_msi.inventory_item_status_code <> 'Suppressed'
            AND msi.primary_unit_of_measure = muomt.unit_of_measure
            AND muomt.LANGUAGE = 'US'
            AND r_msi.primary_unit_of_measure = r_muomt.unit_of_measure
            AND r_muomt.LANGUAGE = 'US'
            AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES'
            AND flv.lookup_code = mri.relationship_type_id
            AND msi.item_type IN
                   ('CON', 'CUS', 'NC', 'SB', 'SPE', 'STD', 'VM', 'STANDARD')
            AND EXISTS (SELECT 1
                          FROM xxcofi_replacement_item_ext xri
                         WHERE file_seq_num = 1 AND xri.sku = msi.segment1)
            AND EXISTS (SELECT 1
                          FROM xxcofi_replacement_item_ext r_xri
                         WHERE file_seq_num = 1 AND r_xri.sku = r_msi.segment1)
            AND ROWNUM <= p_number
            AND NOT EXISTS (
                   SELECT 1
                     FROM xxcofi_related_item_tbl xxitem
                    WHERE xxitem.sku_number = msi.segment1
                      AND xxitem.related_sku = r_msi.segment1
                      AND flv.meaning = xxitem.relation_type)
--************ Added by Yogesh to convert relationshuip for 802 converted items only
           --**** AND EXISTS (SELECT 1
           --****               FROM xxcofi.sku_list_yog sl
           --****              WHERE comments IS NULL AND sl.segment1 = msi.segment1)
       ORDER BY msi.segment1;

-- Commented by Yogesh to modify as above

      /* SELECT   distinct
         xri.sku f01
       , xri.uom f04
       , xri1.uom f05
       , xri1.sku f02
       , flv.meaning f03
       , mri.reciprocal_flag f06
           FROM mtl_related_items mri
       , xxcofi_replacement_item_ext xri
       , xxcofi_replacement_item_ext xri1
       , fnd_lookup_values_vl flv
       WHERE mri.inventory_item_id = xri.inventory_item_id
     AND mri.related_item_id = xri1.inventory_item_id
     AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES'
     AND flv.lookup_code = mri.relationship_type_id
     AND xri.itemtype in('Consignment','Custom','Non-COFI','Sell/Buy','Special','Standard','Vendor Model')
     AND NOT EXISTS(SELECT 'X'
                               FROM xxcofi_related_item_tbl xxitem
                               WHERE xri.sku= xxitem.sku_number
                               AND xri1.sku=xxitem.related_sku
                               AND flv.meaning=xxitem.relation_type)
     and exists ( select * from xxcofi.sku_list_yog sl
              where comments is null
                and sl.segment1=xri.sku )     --************ Added by Yogesh to convert relationshuip for 803 converted items only                               
     AND ROWNUM<=p_number
     AND xri.file_seq_num=1
     AND xri1.file_seq_num=1
     ---and xri1.sku in ('03014642')
     AND xri.item_status<>'Suppressed'
     AND xri1.item_status<>'Suppressed'
     ORDER BY xri.sku;  */

--fnd_file.put_line (fnd_file.LOG,'Out Bound Extract Started');
      CURSOR cur_record_item
      IS
         SELECT DISTINCT sku_number f01
                       , enterprise_code f02
                       , uom f03
                       , action f04
                       , associated_quantity f05
                       , relation_type f06
                       , to_CHAR(effective_from ,'DD/MM/YYYY') f07
                       , to_CHAR(effective_to ,'DD/MM/YYYY') f08
                       , related_sku f09
                       , enterprise_code2 f10
                       , uom2 f11
                    FROM xxcofi_related_item_tbl
                   WHERE extract_flag = 'TBE'
                     AND item_seq_num = 1
                   --  AND ROWNUM <=p_number           --commneted by ashwini
                ORDER BY sku_number;
   BEGIN
      FOR rec_cur_item_insert IN cur_item_insert
      LOOP
       
            l_item_seq_num             := 1;
            l_extract_flag             := 'TBE';
           
----- Inserting Related Items Information into a staging table
          BEGIN
            INSERT INTO xxcofi_related_item_tbl
                        (item_seq_num
                       , sku_number
                       , enterprise_code
                       , uom
                       , action
                       , associated_quantity
                       , relation_type
                       , effective_from
                       , effective_to
                       , related_sku
                       , enterprise_code2
                       , uom2
                       , extract_flag
                        )
                 VALUES (l_item_seq_num
                       , rec_cur_item_insert.f01
                       , 'BAY'
                       ,rec_cur_item_insert.f04
                       , 'Create'
                       , '1'
                       , rec_cur_item_insert.f03
                       , to_DATE('1/1/2010','DD/MM/YYYY')
                       , to_date('12/12/2999','DD/MM/YYYY')
                       , rec_cur_item_insert.f02
                       , 'BAY'
                       , rec_cur_item_insert.f05
                       , l_extract_flag
                        );         

            COMMIT;
            l_total_insert_count := l_total_insert_count + 1;
          EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line(fnd_file.LOG, SQLERRM || 'Error in Inserting the values into staging table for  SKU:' || rec_cur_item_insert.f01);
          END;
          -- Changes by Ashwini to create reciprocal records
          -- Apr 08, 2011 Requestd by James
       if rec_cur_item_insert.f06 = 'Y'
       then
          BEGIN 
           INSERT INTO xxcofi_related_item_tbl
                        (item_seq_num
                       , sku_number
                       , enterprise_code
                       , uom
                       , action
                       , associated_quantity
                       , relation_type
                       , effective_from
                       , effective_to
                       , related_sku
                       , enterprise_code2
                       , uom2
                       , extract_flag
                        )
                 VALUES (l_item_seq_num
                       , rec_cur_item_insert.f02
                       , 'BAY'
                       ,rec_cur_item_insert.f05
                       , 'Create'
                       , '1'
                       , rec_cur_item_insert.f03
                       , to_DATE('1/1/2010','DD/MM/YYYY')
                       , to_date('12/12/2999','DD/MM/YYYY')
                       , rec_cur_item_insert.f01
                       , 'BAY'
                       , rec_cur_item_insert.f04
                       , l_extract_flag
                        );
           COMMIT;
           l_additional_insert_count := l_additional_insert_count + 1;
           l_total_insert_count      := l_total_insert_count +1;
          
          EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line(fnd_file.LOG, SQLERRM || 'Error in Inserting the values into staging table for  SKU:' || rec_cur_item_insert.f02);
          END; 
        end if;                
      END LOOP;

      fnd_profile.get('XXCOFIDATA_OUT', l_top);                                                                                    --outbound file top
      l_date                     := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI');
      fnd_file.put_line(fnd_file.LOG, 'START RUNNING ' || report_name || '_SS' || ' REPORT PROGRAM.');
      fnd_file.put_line(fnd_file.output, RPAD('HUDSON''S BAY COMPANY', 50) || 'DATE:' || SYSDATE);
      fnd_file.put_line(fnd_file.output, RPAD('COFI Related Item Extract Report', 50) || 'TIME:' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
      fnd_file.put_line(fnd_file.LOG, 'Outputing ' || report_name || '_' || l_date || 'TO ' || l_top);
      v_file_dc                  := UTL_FILE.fopen(l_top
                                                 , 'related_items.dat'
                                                 , 'w'
                                                 , buff_size
                                                  );
--      CREATE THE REPORT HEADER RECORD FOR REPORT OUTPUT FILE
                    --      PUT START MESSAGE IN THE LOG FILE
      fnd_file.put_line(fnd_file.output
                      ,    col01e_desc
                        || ','
                        || col02e_desc
                        || ','
                        || col03e_desc
                        || ','
                        || col04e_desc
                        || ','
                        || col05e_desc
                        || ','
                        || col06e_desc
                        || ','
                        || col07e_desc
                        || ','
                        || col08e_desc
                        || ','
                        || col09e_desc
                        || ','
                        || col10e_desc
                        || ','
                        || col11e_desc);

      FOR rec_item IN cur_record_item
      LOOP
         l_record_no                := l_record_no + 1;
         UTL_FILE.put_line(v_file_dc
                         ,    RPAD(rec_item.f01, 8)
                           || RPAD(rec_item.f02, 3)
                           || RPAD(rec_item.f03, 4)
                           || RPAD(rec_item.f04, 6)
                           || RPAD(rec_item.f05, 1)
                           || RPAD(rec_item.f06, 21)
                           || RPAD(rec_item.f07, 10)
                           || RPAD(rec_item.f08, 10)
                           || RPAD(rec_item.f09, 8)
                           || RPAD(rec_item.f10, 3)
                           || RPAD(rec_item.f11, 4));
         fnd_file.put_line(fnd_file.output
                         ,    rec_item.f01
                           || ','
                           || rec_item.f02
                           || ','
                           || rec_item.f03
                           || ','
                           || rec_item.f04
                           || ','
                           || rec_item.f05
                           || ','
                           || rec_item.f06
                           || ','
                           || rec_item.f07
                           || ','
                           || rec_item.f08
                           || ','
                           || rec_item.f09
                           || ','
                           || rec_item.f10
                           || ','
                           || rec_item.f11);

         BEGIN
            l_update_count             := l_update_count + 1;

            UPDATE xxcofi_related_item_tbl
               SET extract_flag = 'E'
             WHERE sku_number = rec_item.f01;
         -- fnd_file.put_line(fnd_file.LOG, 'rowcount :' || sql%rowcount);
         EXCEPTION
            WHEN OTHERS
            THEN
               fnd_file.put_line(fnd_file.LOG, 'update failed  SKU :' || rec_item.f01);
         END;

         COMMIT;
--------------------------------------------------------------------------------
      END LOOP;

      fnd_file.put_line(fnd_file.output, '-----------------------------------------------------------------------');
      ----- print corresponding information in the log file---------
      fnd_file.put_line(fnd_file.LOG, '                                                                    ');
      fnd_file.put_line(fnd_file.LOG, '------------------------------------------------------------------');
      fnd_file.put_line(fnd_file.LOG, 'COFI RELATED ITEM  EXTRACT');
      fnd_file.put_line(fnd_file.LOG, '                                                                    ');
      fnd_file.put_line(fnd_file.LOG, '                        ');
      fnd_file.put_line(fnd_file.LOG, '**************************************************************************');
      fnd_file.put_line(fnd_file.LOG, '       ');
      fnd_file.put_line(fnd_file.LOG, 'TOTAL NUMBER of RECORDS  inserted in custom table        : ' || l_total_insert_count);
      fnd_file.put_line(fnd_file.LOG, 'ADDITONAL NUMBER of RECORDS  inserted in custom table    : ' || l_additional_insert_count);
      fnd_file.put_line(fnd_file.LOG, 'TOTAL NUMBER of RECORDS with flag as "TBE" initially     : ' || l_record_no);
      fnd_file.put_line(fnd_file.LOG, 'NUMBER of RECORDS extracted into OUTBOUND FILE           : ' || l_record_no);
      fnd_file.put_line(fnd_file.LOG, 'NUMBER of RECORDS updated with flag "E" after extracting : ' || l_update_count);
      fnd_file.put_line(fnd_file.LOG, '       ');
      fnd_file.put_line(fnd_file.LOG, '**************************************************************************');
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
      fnd_file.put_line(fnd_file.output, '                                       ');
--------------------------------------------------------------------------------
--      BEGINNING OF ERROR Pricing Extract Report
--------------------------------------------------------------------------------
--      CREATE THE TRAILER RECORD FOR OUTPUT REPORT FILE
      UTL_FILE.fflush(v_file_dc);
      UTL_FILE.fclose(v_file_dc);
--******************************************************************************
      errbuf                     := report_name || ' REPORT FILE JOB COMPLETED SUCCESSFULLY';
      retcode                    := '0';
--              Output Number Of Record Output
      fnd_file.put_line(fnd_file.LOG, '------------------*********************************************----------------+');
      fnd_file.put_line(fnd_file.LOG, 'Related Item  Extract REPORT                       ');
      fnd_file.put_line(fnd_file.LOG, '                        ');
      fnd_file.put_line(fnd_file.LOG, 'JOB COMPLETED SUCCESSFULLY');
   --    FND_FILE.CLOSE;
   EXCEPTION
      WHEN UTL_FILE.invalid_filehandle
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID FILE HANDLE';
         retcode                    := '1';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID FILE HANDLE : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.invalid_path
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID PATH';
         retcode                    := '2';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID PATH : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.invalid_mode
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID MODE';
         retcode                    := '3';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID MODE : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.invalid_operation
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INVALID OPERATION';
         retcode                    := '4';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INVALID OPERATION : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.read_error
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'READ ERROR';
         retcode                    := '5';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is READ ERROR : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.write_error
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'WRITE ERROR';
         retcode                    := '6';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is WRITE ERROR : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN UTL_FILE.internal_error
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'INTERNAL ERROR';
         retcode                    := '7';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' AND ERROR is INTERNAL ERROR : ' || SQLERRM);
         fnd_file.CLOSE;
      WHEN NO_DATA_FOUND
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'NO DATA FOUND';
         retcode                    := '8';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' Error is NO DATA FOUND :' || SQLERRM);
         fnd_file.CLOSE;
      WHEN OTHERS
      THEN
         UTL_FILE.fclose(v_file_dc);
         errbuf                     := 'OTHERS ';
         retcode                    := '9';
         fnd_file.put_line(fnd_file.LOG, SQLCODE || ' Error is OTHERS : ' || SQLERRM);
         fnd_file.CLOSE;
   END main;
END xxcofi_related_items_pkg;
/