CREATE OR REPLACE PROCEDURE db_pro_create_er_saleItem_file
IS
   CURSOR c_sales_data
   IS
     select s.name,trunc(pb.billdate) billdate,to_char(pb.billdate,'hh:mi:ss') time,pb.billno,
            i.barcode,pbi.qty,g.lev1grpname division,g.lev2grpname section,g.grpname department,
            pbi.rsp,case 
                      when pbi.qty>0 then pbi.qty
                      else null
                      end sale_qty,
            case 
               when pbi.qty<0 then pbi.qty
               else null
               end return_qty,
            pb.customer customer_name,c.mobile customer_mobile,pbi.netamt net_amount,
            case 
               when pbi.netamt>0 then pbi.netamt
               else null
               end return_amount,
            case
               when pbi.netamt<0 then pbi.netamt
               else null
               end sale_amount, 
            pbi.mrp mrp_value,pbi.promoamt,pbi.lpdiscountamt loyalty_discount_amount,
            pbi.discountamt total_discount_amount,pbi.extaxamt extra_tax_amount,
            pbi.taxdescription  tax_description, pbi.taxpercent tax_rate,pbi.taxableamt taxable_amount,
            pbi.taxamt tax_amount,pbi.promono promo_no,pbi.promoname promo_name,
            pbi.promostartdate promo_start_date,pbi.promoenddate promo_end_date,
            pbi.remarks item_level_remarks,pb.remarks bill_level_remarks,i.cname1 category1,
            i.cname2 category2,i.cname3 category3,i.cname4 category4,i.cname5 category5,
            i.cname6 category6,i.desc1 desc1,i.desc2 desc2,i.desc3 desc3,i.desc4 desc4,
            ps.FNAME||' '||ps.MNAME||' '||ps.LNAME salesperson,stlm.stlmfor last_settlement_date              
      from psite_posbill pb, psite_posbillitem pbi, admsite s,invitem i,invgrp g,psite_customer c,
           psite_salesperson ps,er_lastposbillsyncinfo e,
           (select admsite_code,max(stlmfor) stlmfor from psite_posstlm where status='C' group by admsite_code) stlm
      where pb.code=pbi.psite_posbill_code          
        and pb.admsite_code=s.code
        and pb.admsite_code=c.admsite_code(+)
        and pb.psite_customer_code=c.code(+)
        and pbi.icode=i.icode
        and i.grpcode=g.grpcode
        and pb.admsite_code=ps.admsite_code(+)
        and pbi.psite_salesperson_id=ps.id(+)
        and pb.admsite_code=stlm.admsite_code
        and trunc(pb.billdate)>trunc(stlm.stlmfor) 
        AND pb.admsite_code = e.admsite_code 
        AND (e.last_stlmdate IS NULL OR trunc(pb.billdate) > e.last_stlmdate);
      
 
   v_file    UTL_FILE.file_type;
   v_file1   VARCHAR2 (50);
   v_dir     VARCHAR2(100);
BEGIN
   V_DIR := 'ER_WORKING_DIR';
   v_file1 :=
           'ER_GINPOS_Sale_Item' || TO_CHAR (SYSDATE, 'DDMMRRRR_HHMISS')
           || '.csv';
   v_file :=
      UTL_FILE.fopen (LOCATION          => V_DIR,
                      filename          => v_file1,
                      open_mode         => 'w',
                      max_linesize      => 32767
                     );
   UTL_FILE.put_line (v_file,
                         'Source Site'
                      || '|'
                      || 'Bill Date'
                      || '|'
                      || 'Bill Time'
                      || '|'
                      || 'Bill No'
                      || '|'
                      || 'Barcode'
                      || '|'
                      || 'Bill Qty'
                      || '|'
                      || 'Division'
                      || '|'
                      || 'Section'
                      || '|'
                      || 'Department'
                      || '|'
                      || 'RSP '
                      || '|'
                      || 'Sale Qty'
                      || '|'
                      || 'Return Qty'
                      || '|'
                      || 'Customer Name'
                      || '|'
                      || 'Customer Mobile'
                      || '|'
                      || 'Net Amount'
                      || '|'
                      || 'Return Amount'
                      || '|'
                      || 'Sale Amount'
                      || '|'
                      || 'MRP Value'
                      || '|'
                      || 'Promo Amount'
                      || '|'
                      || 'Loyalty Discount Amount'
                      || '|'
                      || 'Total Discount Amount'
                      || '|'
                      || 'Extra Tax Amount'
                      || '|'
                      || 'Tax Description'
                      || '|'
                      || 'Tax Rate'
                      || '|'
                      || 'Taxable Amount'
                      || '|'
                      || 'Tax Amount'
                      || '|'
                      || 'Promo No'
                      || '|'
                      || 'Promo Name'
                      || '|'
                      || 'Promo Start Date'
                      || '|'
                      || 'Promo End Date'
                      || '|'
                      || 'Item Level Remarks'
                      || '|'
                      || 'Bill Level Remarks'
                      || '|'
                      || 'Category1'
                      || '|'
                      || 'Category2'
                      || '|'
                      || 'Category3'
                      || '|'
                      || 'Category4'
                      || '|'
                      || 'Category5'
                      || '|'
                      || 'Category6'
                      || '|'
                      || 'Desc1'
                      || '|'
                      || 'Desc2'
                      || '|'
                      || 'Desc3'
                      || '|'
                      || 'Desc4'
                      || '|'
                      || 'Salesperson'
                      || '|'
                      || 'Last Settlement Date'                      
                     );

   FOR c1 IN c_sales_data
   LOOP
      UTL_FILE.put_line (v_file,
                            c1.name
                         || '|'
                         || c1.billdate
                         || '|'
                         || c1.time
                         || '|'
                         || c1.billno
                         || '|'
                         || c1.barcode
                         || '|'
                         || c1.qty
                         || '|'
                         || c1.division
                         || '|'
                         || c1.section
                         || '|'
                         || c1.department
                         || '|'
                         || c1.sale_qty
                         || '|'
                         || c1.return_qty
                         || '|'
                         || c1.customer_name
                         || '|'
                         || c1.customer_mobile
                         || '|'
                         || c1.net_amount
                         || '|'
                         || c1.return_amount
                         || '|'
                         || c1.sale_amount
                         || '|'
                         || c1.mrp_value
                         || '|'
                         || c1.promoamt
                         || '|'
                         || c1.loyalty_discount_amount
                         || '|'
                         || c1.total_discount_amount
                         || '|'
                         || c1.extra_tax_amount
                         || '|'
                         || c1.tax_description
                         || '|'
                         || c1.tax_rate
                         || '|'
                         || c1.taxable_amount
                         || '|'
                         || c1.tax_amount
                         || '|'
                         || c1.promo_no
                         || '|'
                         || c1.promo_name
                         || '|'
                         || c1.promo_start_date
                         || '|'
                         || c1.promo_end_date
                         || '|'
                         || c1.item_level_remarks
                         || '|'
                         || c1.bill_level_remarks
                         || '|'
                         || c1.category1
                         || '|'
                         || c1.category2
                         || '|'
                         || c1.category3
                         || '|'
                         || c1.category4
                         || '|'
                         || c1.category5
                         || '|'
                         || c1.category6
                         || '|'
                         || c1.desc1
                         || '|'
                         || c1.desc2
                         || '|'
                         || c1.desc3
                         || '|'
                         || c1.desc4
                         || '|'
                         || c1.salesperson
                         || '|'
                         || c1.last_settlement_date
                        );
   END LOOP;



   UTL_FILE.fclose (v_file);
   
   MERGE INTO er_lastposbillsyncinfo e
      USING (select admsite_code,max(stlmfor) stlmfor
            from    psite_posstlm
            group by admsite_code) stlm
      ON (e.admsite_code = stlm.admsite_code)
      WHEN MATCHED THEN
         UPDATE
            SET e.LAST_STLMDATE = stlm.stlmfor
      WHEN NOT MATCHED THEN
         INSERT (admsite_code, last_stlmdate)
         VALUES (stlm.admsite_code, stlm.stlmfor);
      commit;   
         
EXCEPTION
   WHEN OTHERS
   THEN
      UTL_FILE.fclose (v_file);
      RAISE;
END db_pro_create_er_saleItem_file;
/