CREATE INDEX TMP.IDX_RED_COAT_2 ON TMP.RED_COAT
(CODE_YEAR, SUBSTR("CODE",1,2), NVL("SDN",'0'))
NOLOGGING
TABLESPACE TPOCLIENT_INDEX_5M_01
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          237M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )	
NOPARALLEL;

CREATE OR REPLACE PROCEDURE TMP.pr_delight
AS
   v_promo       red_coat.code%TYPE;
   v_date         DATE;

   CURSOR c1 (v_date DATE)
   IS
      SELECT DISTINCT ref_v.sdn sdn, 
                      ref_v.recharge_type recharge_type,
                      ref_v.REFERENCE REFERENCE, 
                      icat.langcode lang_code
                 FROM tmp.DEF_view ref_v, 
                      inf_tmp icat
                WHERE ref_v.sdn = icat.cardnum
                  AND ref_v.currency IN (
                               SELECT emp_id_d
                                 FROM tmp.emp
                                WHERE emp_id_h = 'CURRENCY'
                                      AND emp_txt = 'EURO')
                  AND ref_v.recharge_amount >= 100
                  AND ref_v.date_exec > v_date
                  AND ref_v.sdn NOT IN (
                         SELECT gprs_sdn
                           FROM stage.ppa_gprs
                          WHERE ppa_gprs.prom_idct =
                                   (SELECT emp_txt
                                      FROM tmp.emp
                                     WHERE emp_id_h = 'LOT'
                                       AND emp_id= 'LOT_ID'))
                  AND icat.profiled NOT IN ('KADOR', 'DINHG');

          rec   c1%ROWTYPE;
BEGIN
   v_promo := NULL;
   v_date := NULL;

   SELECT TO_DATE (emp_txt, 'DD-MON-YYYY')
     INTO v_date
     FROM tmp.emp
    WHERE emp_id_h = 'LOT' 
     AND emp_id = 'LAST_DATE' 
     AND emp_id_t = 'D';

    OPEN c1 (v_date);
   LOOP
      FETCH c1 INTO rec;
      EXIT WHEN c1%NOTFOUND;
      SELECT code
        INTO v_promo
        FROM tmp.red_coat
       WHERE SUBSTR (code, 1, 2) = TO_CHAR (SYSDATE, 'MM')
         AND code_year = TO_CHAR (SYSDATE, 'YYYY')
         AND nvl(sdn,0) =0
         AND ROWNUM = 1;

      UPDATE red_coat
         SET sdn = SUBSTR (rec.sdn, 3),
             REFERENCE = rec.REFERENCE,
             recharge_type = rec.recharge_type,
             assign_date = TRUNC (SYSDATE),
             lang_code = rec.lang_code
       WHERE code = v_promo;

      COMMIT;
   END LOOP;
   
   UPDATE tmp.emp
      SET emp_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
    WHERE emp_id_h = 'LOT' 
     AND emp_id = 'LAST_DATE' 
     AND emp_id_t = 'D';

   COMMIT;

   CLOSE c1;
EXCEPTION
   WHEN OTHERS
   THEN
      UPDATE tmp.emp
         SET emp_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
       WHERE emp_id_h = 'LOT' 
        AND emp_id = 'LAST_DATE' 
        AND emp_id_t = 'D';
      COMMIT;

      CLOSE c1;
END pr_delight;

 SELECT code
        INTO v_promo
        FROM tmp.red_coat
       WHERE SUBSTR (code, 1, 2) = TO_CHAR (SYSDATE, 'MM')
         AND code_year = TO_CHAR (SYSDATE, 'YYYY')
         AND nvl(sdn,0) =0
         AND ROWNUM = 1

EXCEPTION
   WHEN OTHERS
   THEN
      UPDATE tmp.emp
         SET emp_txt = TO_CHAR (SYSDATE, 'DD-MON-YYYY')
       WHERE emp_id_h = 'LOT' 
        AND emp_id = 'LAST_DATE' 
        AND emp_id_t = 'D';