Untitled

CREATE OR REPLACE PROCEDURE REP_20_Royalty_v2(taskId NUMBER) IS

task1     Obj$Task := Obj$Task(taskId);
task2     Obj$Task := Obj$Task(taskId);
task3     Obj$Task := Obj$Task(taskId);

TYPE step_table IS TABLE OF VARCHAR2 (6) INDEX BY BINARY_INTEGER;
step       step_table;


TYPE rT IS RECORD (name   VARCHAR2(255),
                   num1   NUMBER,
                   num11  NUMBER,
                   val1   NUMBER,
                   num2   NUMBER,
                   num22  NUMBER,
                   val2   NUMBER);
TYPE tT IS TABLE OF rT INDEX BY BINARY_INTEGER;
t tT;

parms     REV_UTIL.stackT;

beginDate DATE;
endDate   DATE;
regArg1   VARCHAR2(255) := 'REP_20.1';
regArg2   VARCHAR2(255) := 'REP_20.2';
catID     NUMBER;
nodeID    NUMBER;
boxMode   VARCHAR2(255);
r         VARCHAR2(32767);
tab       CHAR(1) := CHR(9);
ret       CHAR(1) := CHR(10);

BEGIN
beginDate := TO_DATE(NVL(task1.p('beginDate'),SYSDATE),'DD/MM/YYYY');
endDate   := TO_DATE(NVL(task1.p('endDate'),SYSDATE),'DD/MM/YYYY') + 1 - 1/86400;

IF task1.p('boxThrow') IS NULL THEN
   step (1) := 'RECALC';
   step (2) := 'FULL';
   step (3) := 'OTHER';
ELSE
   step (1) := task1.p('boxThrow');
END IF;

 FOR j IN 1 .. step.COUNT
 LOOP

      boxMode :=
       CASE WHEN step(j) = 'RECALC'
                     THEN ' AND  cn.mode_throw ='||TO_CHAR(REV_CONST.boxModeThrowRecalculation)
            WHEN step(j) = 'FULL'
                     THEN ' AND  cn.mode_throw ='||TO_CHAR(REV_CONST.boxModeThrowComplete)
            ELSE
             ' AND  cn.mode_throw NOT IN ('||TO_CHAR(REV_CONST.boxModeThrowRecalculation)||','||TO_CHAR(REV_CONST.boxModeThrowComplete)||')'
       END;

        DBMS_APPLICATION_INFO.set_action('Retrieving data...');

        FOR c IN (SELECT *
                  FROM   registry_folder
                  WHERE  folder_code <> regArg2
                  AND    TRIM(folder_value) IS NOT NULL
                  START WITH folder_code = regArg2 CONNECT BY PRIOR folder_id = parent_id
                 )
        LOOP
           parms := REV_UTIL.parseStr(c.folder_value,';');

           r := CASE WHEN r IS NOT NULL THEN r||' UNION ' ELSE r END||

      ' SELECT '''||c.folder_code||''' AS code, '''||c.folder_name||''' AS sec, node_id, COUNT(*) AS num
        FROM (
      ';

           FOR i IN 1..parms.COUNT
           LOOP
             BEGIN
               SELECT category_id INTO catID FROM category WHERE category_code = parms(i);
               r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT pc.*
                                                                      FROM product_category pc, catalogue ct
                                                                     WHERE pc.node_id = ct.node_id
                                                                       AND ct.type_id IN ('||rev_const.goodTypePrimary||', '||rev_const.goodTypeService||')
                                                                       AND pc.category_id = '||catID;
             EXCEPTION WHEN NO_DATA_FOUND THEN
               r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT * FROM product_category WHERE category_id = -1';
             END;
           END LOOP;

           r := r||') GROUP BY '''||c.folder_code||''', '''||c.folder_name||''', node_id HAVING COUNT(*)='||parms.COUNT;
        END LOOP;

/*
        FOR c IN (SELECT *
                  FROM   registry_folder
                  WHERE  folder_code <> regArg1
                  AND    TRIM(folder_value) IS NOT NULL
                  START WITH folder_code = regArg1 CONNECT BY PRIOR folder_id = parent_id
                 )
        LOOP
           parms := REV_UTIL.parseStr(c.folder_value,';');

           r := CASE WHEN r IS NOT NULL THEN r||' UNION ' ELSE r END||

      ' SELECT '''||c.folder_code||''' AS code, '''||c.folder_name||''' AS sec, node_id, COUNT(*) AS num
        FROM (
      ';

           FOR i IN 1..parms.Count
           LOOP
             BEGIN
               SELECT node_id INTO nodeID FROM catalogue WHERE node_code = parms(i);
               r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT node_id FROM catalogue WHERE node_id = '||nodeID;
             EXCEPTION WHEN NO_DATA_FOUND THEN
               r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT node_id FROM catalogue WHERE node_id =  -1';
             END;
           END LOOP;
           r := r||') GROUP BY '''||c.folder_code||''', '''||c.folder_name||''', node_id HAVING COUNT(*)='||parms.Count;
        END LOOP;
*/
        r :=
      'WITH a AS (
          SELECT code, sec, node_id FROM ('||r||')
                  ),
            v AS (
             SELECT a.sec,
                    0                                                         AS  costNum,
                    0                                                         AS  emptycostNum,
                    0                                                         AS  costSum,
                    COUNT(CASE WHEN r.good_id IS NOT NULL AND NVL(r.return_sum,0) > 0 THEN 1 ELSE NULL END) AS retNum,
                    COUNT(CASE WHEN r.good_id IS NOT NULL AND NVL(r.return_sum,0) = 0 THEN 1 ELSE NULL END) AS emptyretNum,
                    SUM(NVL(r.return_sum,0))                                  AS retSum
              FROM  return_item r, a, container cn, client_basket cb
              WHERE r.good_id = a.node_id
                AND r.item_id = cb.item_id(+)
                AND cb.container_id = cn.container_id'||
                boxMode||'
                AND r.approved_date BETWEEN :b1 AND :b2
             GROUP BY a.sec

                   UNION

             SELECT a.sec,
                    COUNT(CASE WHEN cb.node_id IS NOT NULL AND NVL(cb.item_cost , 0) > 0 THEN 1 ELSE NULL END) AS costNum,
                    COUNT(CASE WHEN cb.node_id IS NOT NULL AND NVL(cb.item_cost , 0) = 0 THEN 1 ELSE NULL END) AS emptycostNum,
                    SUM(NVL(cb.item_cost , 0))                                 AS costSum,
                    0                                                          AS retNum,
                    0                                                          AS emptyretNum,
                    0                                                          AS retSum
              FROM  client_basket cb, container cn, a
              WHERE cb.node_id = a.node_id
                AND cn.send_date BETWEEN :b1 AND :b2
                AND cb.container_id = cn.container_id
                AND cb.item_status = '||TO_CHAR(REV_CONST.itemStatusActive)||
                boxMode||'
                AND cn.status_id   = '||TO_CHAR(REV_CONST.boxStatusSent)||'
              GROUP BY a.sec
                  )
        SELECT v.sec, SUM(v.costNum), SUM(v.emptycostNum), SUM(v.costSum), SUM(v.retNum), SUM(v.emptyretNum), SUM(v.retSum)
         FROM v
        GROUP BY v.sec'  ;
        EXECUTE IMMEDIATE r BULK COLLECT INTO t
        USING beginDate, endDate, beginDate, endDate;

       CASE WHEN step(j) = 'RECALC' THEN
                task1.addToResult('beginDate'||tab||TO_CHAR(beginDate,'DD/MM/YYYY')||ret||
                                 'endDate'  ||tab||TO_CHAR(endDate,'DD/MM/YYYY')||ret);
                task1.addToResult('Name' || tab || 'CntPaySent' || tab || 'CntFreeSent' || tab || 'SummSent' || tab || 'CntPayRet' || tab || 'CntFreeRet' || tab || 'SummRet' || ret);
                FOR i IN 1..t.COUNT
                LOOP
                  task1.addToResult(t(i).name || tab||t(i).num1 || tab ||t(i).num11 || tab || t(i).val1 || tab || t(i).num2 || tab || t(i).num22 || tab || t(i).val2 ||ret);
                END LOOP;

            WHEN step(j) = 'FULL' THEN
                task2.addToResult('beginDate'||tab||TO_CHAR(beginDate,'DD/MM/YYYY')||ret||
                                 'endDate'  ||tab||TO_CHAR(endDate,'DD/MM/YYYY')||ret);
                task2.addToResult('Name' || tab || 'CntPaySent' || tab || 'CntFreeSent' || tab || 'SummSent' || tab || 'CntPayRet' || tab || 'CntFreeRet' || tab || 'SummRet' || ret);
                FOR i IN 1..t.COUNT
                LOOP
                  task2.addToResult(t(i).name || tab||t(i).num1 || tab ||t(i).num11 || tab || t(i).val1 || tab || t(i).num2 || tab || t(i).num22 || tab || t(i).val2 ||ret);
                END LOOP;
            ELSE
                task3.addToResult('beginDate'||tab||TO_CHAR(beginDate,'DD/MM/YYYY')||ret||
                                 'endDate'  ||tab||TO_CHAR(endDate,'DD/MM/YYYY')||ret);
                task3.addToResult('Name' || tab || 'CntPaySent' || tab || 'CntFreeSent' || tab || 'SummSent' || tab || 'CntPayRet' || tab || 'CntFreeRet' || tab || 'SummRet' || ret);
                FOR i IN 1..t.COUNT
                LOOP
                  task3.addToResult(t(i).name || tab||t(i).num1 || tab ||t(i).num11 || tab || t(i).val1 || tab || t(i).num2 || tab || t(i).num22 || tab || t(i).val2 ||ret);
                END LOOP;
          END CASE;
      r := '';
      boxMode := '';
      t.DELETE;
  END LOOP;
  task1.save;
  task1.free;
  task2.save;
  task2.free;
  task3.save;
  task3.free;
END REP_20_Royalty_v2;