CREATE OR REPLACE PROCEDURE db_pro_create_er_sales_file
IS
   CURSOR c_sales_data
   IS
      SELECT s.psite_initial storecode, c.mobile customermobile,
             RTRIM (LTRIM (   NVL (c.fname, ' ')
                           || CASE
                                 WHEN c.mname IS NOT NULL
                                    THEN ' '
                                 ELSE ''
                              END
                           || NVL (c.mname, ' ')
                           || CASE
                                 WHEN c.mname IS NOT NULL
                                    THEN ' '
                                 ELSE ''
                              END
                           || NVL (c.lname, ' ')
                          )
                   ) customername,
             TRUNC (pb.billdate) billdate, pb.billno, pbi.discountedsale,
             pbi.freshsale, pb.netamt, pbm.cnramt paidbycreditnote, pb.lpdiscountamt loyalty_discount_amount,
             pbm.redemptionamt pointredemptionamt, pco.shortcode couponapplied,
             case when pb.couponoffer_code IS NOT NULL THEN nvl(MDISCOUNTAMT,0) else 0 END couponamt
        FROM psite_posbill pb,
             (SELECT   d.psite_posbill_code,
                       SUM (CASE
                               WHEN (d.idiscountamt + d.mdiscountamt + d.promoamt) <>
                                                                             0
                                  THEN d.netamt
                               ELSE 0
                            END
                           ) discountedsale,
                       SUM (CASE
                               WHEN (d.idiscountamt + d.mdiscountamt + d.promoamt) =
                                                                             0
                                  THEN d.netamt
                               ELSE 0
                            END
                           ) freshsale
                  FROM psite_posbill m, psite_posbillitem d, ER_LASTPOSBILLSYNCINFO e, psite_posstlm stlm
                  WHERE m.code = d.psite_posbill_code
                  AND   m.admsite_code = stlm.admsite_code
                  AND   TRUNC(m.billdate) = stlm.stlmfor
                  AND m.admsite_code = e.admsite_code (+)
                  AND (e.last_stlmdate IS NULL OR trunc(m.billdate) > e.last_stlmdate)
              GROUP BY d.psite_posbill_code) pbi,
             (SELECT   psite_posbill_code,
                       SUM (CASE
                               WHEN moptype = 'CNR'
                                  THEN baseamt
                               ELSE 0
                            END) cnramt,
                       SUM (CASE
                               WHEN moptype = 'RED'
                                  THEN baseamt
                               ELSE 0
                            END
                           ) redemptionamt
                  FROM psite_posbillmop d, ER_LASTPOSBILLSYNCINFO e, psite_posstlm stlm
                 WHERE d.admsite_code = e.admsite_code (+)
                 AND   paymentsource = 'POSBill'
                 AND   d.admsite_code = stlm.admsite_code
                 AND   TRUNC(d.billdate) = stlm.stlmfor
                 AND (e.last_stlmdate IS NULL OR trunc(d.billdate) > e.last_stlmdate)
              GROUP BY psite_posbill_code) pbm,
             psite_customer c,
             admsite s,
             er_lastposbillsyncinfo e,
             psite_posstlm stlm1,
             psite_couponoffer pco
       WHERE pb.code = pbi.psite_posbill_code(+)
         AND pb.code = pbm.psite_posbill_code(+)
         AND pb.psite_customer_code = c.code(+)
         AND pb.admsite_code = s.code
         AND pb.admsite_code = e.admsite_code (+)
         AND (e.last_stlmdate IS NULL OR trunc(pb.billdate) > e.last_stlmdate)
         AND   pb.admsite_code = stlm1.admsite_code
         AND   TRUNC(pb.billdate) = stlm1.stlmfor
         AND  pb.couponoffer_code = pco.code(+);

   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_' || 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,
                         'STORECODE'
                      || '|'
                      || 'CUSTOMERMOBILE'
                      || '|'
                      || 'CUSTOMERNAME'
                      || '|'
                      || 'BILLDATE'
                      || '|'
                      || 'BILLNO'
                      || '|'
                      || 'DISCOUNTEDSALE'
                      || '|'
                      || 'FRESHSALE'
                      || '|'
                      || 'NETAMT'
                      || '|'
                      || 'LOYALTYDISCOUNT'
                      || '|'
                      || 'PAIDBYCREDITNOTE'
                      || '|'
                      || 'POINTREDEMPTIONAMT'
                      || '|'
                      || 'COUPONAPPLIED'
                      || '|'
                      || 'COUPONAMT'
                     );

   FOR c1 IN c_sales_data
   LOOP
      UTL_FILE.put_line (v_file,
                            c1.storecode
                         || '|'
                         || c1.customermobile
                         || '|'
                         || c1.customername
                         || '|'
                         || c1.billdate
                         || '|'
                         || c1.billno
                         || '|'
                         || c1.discountedsale
                         || '|'
                         || c1.freshsale
                         || '|'
                         || c1.netamt
                         || '|'
                         || c1.loyalty_discount_amount
                         || '|'
                         || c1.paidbycreditnote
                         || '|'
                         || c1.pointredemptionamt
                         || '|'
                         || c1.couponapplied
                         || '|'
                         || c1.couponamt
                        );
   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_sales_file;
/