SHARE
TWEET

Untitled

a guest Aug 4th, 2018 96 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE PROCEDURE REP_20_Royalty_v2(taskId NUMBER) IS
  2.  
  3. task1     Obj$Task := Obj$Task(taskId);
  4. task2     Obj$Task := Obj$Task(taskId);
  5. task3     Obj$Task := Obj$Task(taskId);
  6.  
  7. TYPE step_table IS TABLE OF VARCHAR2 (6) INDEX BY BINARY_INTEGER;
  8. step       step_table;
  9.  
  10.  
  11. TYPE rT IS RECORD (name   VARCHAR2(255),
  12.                    num1   NUMBER,
  13.                    num11  NUMBER,
  14.                    val1   NUMBER,
  15.                    num2   NUMBER,
  16.                    num22  NUMBER,
  17.                    val2   NUMBER);
  18. TYPE tT IS TABLE OF rT INDEX BY BINARY_INTEGER;
  19. t tT;
  20.  
  21. parms     REV_UTIL.stackT;
  22.  
  23. beginDate DATE;
  24. endDate   DATE;
  25. regArg1   VARCHAR2(255) := 'REP_20.1';
  26. regArg2   VARCHAR2(255) := 'REP_20.2';
  27. catID     NUMBER;
  28. nodeID    NUMBER;
  29. boxMode   VARCHAR2(255);
  30. r         VARCHAR2(32767);
  31. tab       CHAR(1) := CHR(9);
  32. ret       CHAR(1) := CHR(10);
  33.  
  34. BEGIN
  35. beginDate := TO_DATE(NVL(task1.p('beginDate'),SYSDATE),'DD/MM/YYYY');
  36. endDate   := TO_DATE(NVL(task1.p('endDate'),SYSDATE),'DD/MM/YYYY') + 1 - 1/86400;
  37.  
  38. IF task1.p('boxThrow') IS NULL THEN
  39.    step (1) := 'RECALC';
  40.    step (2) := 'FULL';
  41.    step (3) := 'OTHER';
  42. ELSE
  43.    step (1) := task1.p('boxThrow');
  44. END IF;
  45.  
  46.  FOR j IN 1 .. step.COUNT
  47.  LOOP
  48.      
  49.       boxMode :=
  50.        CASE WHEN step(j) = 'RECALC'
  51.                      THEN ' AND  cn.mode_throw ='||TO_CHAR(REV_CONST.boxModeThrowRecalculation)
  52.             WHEN step(j) = 'FULL'
  53.                      THEN ' AND  cn.mode_throw ='||TO_CHAR(REV_CONST.boxModeThrowComplete)
  54.             ELSE
  55.              ' AND  cn.mode_throw NOT IN ('||TO_CHAR(REV_CONST.boxModeThrowRecalculation)||','||TO_CHAR(REV_CONST.boxModeThrowComplete)||')'
  56.        END;
  57.      
  58.         DBMS_APPLICATION_INFO.set_action('Retrieving data...');
  59.      
  60.         FOR c IN (SELECT *
  61.                   FROM   registry_folder
  62.                   WHERE  folder_code <> regArg2
  63.                   AND    TRIM(folder_value) IS NOT NULL
  64.                   START WITH folder_code = regArg2 CONNECT BY PRIOR folder_id = parent_id
  65.                  )
  66.         LOOP
  67.            parms := REV_UTIL.parseStr(c.folder_value,';');
  68.      
  69.            r := CASE WHEN r IS NOT NULL THEN r||' UNION ' ELSE r END||
  70.      
  71.       ' SELECT '''||c.folder_code||''' AS code, '''||c.folder_name||''' AS sec, node_id, COUNT(*) AS num
  72.        FROM (
  73.      ';
  74.      
  75.            FOR i IN 1..parms.COUNT
  76.            LOOP
  77.              BEGIN
  78.                SELECT category_id INTO catID FROM category WHERE category_code = parms(i);
  79.                r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT pc.*
  80.                                                                      FROM product_category pc, catalogue ct
  81.                                                                     WHERE pc.node_id = ct.node_id
  82.                                                                       AND ct.type_id IN ('||rev_const.goodTypePrimary||', '||rev_const.goodTypeService||')
  83.                                                                       AND pc.category_id = '||catID;
  84.              EXCEPTION WHEN NO_DATA_FOUND THEN
  85.                r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT * FROM product_category WHERE category_id = -1';
  86.              END;
  87.            END LOOP;
  88.      
  89.            r := r||') GROUP BY '''||c.folder_code||''', '''||c.folder_name||''', node_id HAVING COUNT(*)='||parms.COUNT;
  90.         END LOOP;
  91.  
  92. /*      
  93.         FOR c IN (SELECT *
  94.                   FROM   registry_folder
  95.                   WHERE  folder_code <> regArg1
  96.                   AND    TRIM(folder_value) IS NOT NULL
  97.                   START WITH folder_code = regArg1 CONNECT BY PRIOR folder_id = parent_id
  98.                  )
  99.         LOOP
  100.            parms := REV_UTIL.parseStr(c.folder_value,';');
  101.      
  102.            r := CASE WHEN r IS NOT NULL THEN r||' UNION ' ELSE r END||
  103.      
  104.       ' SELECT '''||c.folder_code||''' AS code, '''||c.folder_name||''' AS sec, node_id, COUNT(*) AS num
  105.         FROM (
  106.       ';
  107.      
  108.            FOR i IN 1..parms.Count
  109.            LOOP
  110.              BEGIN
  111.                SELECT node_id INTO nodeID FROM catalogue WHERE node_code = parms(i);
  112.                r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT node_id FROM catalogue WHERE node_id = '||nodeID;
  113.              EXCEPTION WHEN NO_DATA_FOUND THEN
  114.                r := CASE WHEN i > 1 THEN r||' UNION ' ELSE r END||' SELECT node_id FROM catalogue WHERE node_id =  -1';
  115.              END;
  116.            END LOOP;
  117.            r := r||') GROUP BY '''||c.folder_code||''', '''||c.folder_name||''', node_id HAVING COUNT(*)='||parms.Count;
  118.         END LOOP;
  119. */      
  120.         r :=
  121.       'WITH a AS (
  122.          SELECT code, sec, node_id FROM ('||r||')
  123.                  ),
  124.            v AS (
  125.             SELECT a.sec,
  126.                    0                                                         AS  costNum,
  127.                    0                                                         AS  emptycostNum,
  128.                    0                                                         AS  costSum,
  129.                    COUNT(CASE WHEN r.good_id IS NOT NULL AND NVL(r.return_sum,0) > 0 THEN 1 ELSE NULL END) AS retNum,
  130.                    COUNT(CASE WHEN r.good_id IS NOT NULL AND NVL(r.return_sum,0) = 0 THEN 1 ELSE NULL END) AS emptyretNum,                    
  131.                    SUM(NVL(r.return_sum,0))                                  AS retSum
  132.              FROM  return_item r, a, container cn, client_basket cb
  133.              WHERE r.good_id = a.node_id
  134.                AND r.item_id = cb.item_id(+)
  135.                AND cb.container_id = cn.container_id'||
  136.                 boxMode||'
  137.                AND r.approved_date BETWEEN :b1 AND :b2
  138.             GROUP BY a.sec
  139.      
  140.                   UNION
  141.      
  142.             SELECT a.sec,
  143.                    COUNT(CASE WHEN cb.node_id IS NOT NULL AND NVL(cb.item_cost , 0) > 0 THEN 1 ELSE NULL END) AS costNum,
  144.                    COUNT(CASE WHEN cb.node_id IS NOT NULL AND NVL(cb.item_cost , 0) = 0 THEN 1 ELSE NULL END) AS emptycostNum,                    
  145.                    SUM(NVL(cb.item_cost , 0))                                 AS costSum,
  146.                    0                                                          AS retNum,
  147.                    0                                                          AS emptyretNum,
  148.                    0                                                          AS retSum
  149.              FROM  client_basket cb, container cn, a
  150.              WHERE cb.node_id = a.node_id
  151.                AND cn.send_date BETWEEN :b1 AND :b2
  152.                AND cb.container_id = cn.container_id
  153.                AND cb.item_status = '||TO_CHAR(REV_CONST.itemStatusActive)||
  154.                 boxMode||'
  155.                AND cn.status_id   = '||TO_CHAR(REV_CONST.boxStatusSent)||'
  156.              GROUP BY a.sec
  157.                  )
  158.        SELECT v.sec, SUM(v.costNum), SUM(v.emptycostNum), SUM(v.costSum), SUM(v.retNum), SUM(v.emptyretNum), SUM(v.retSum)
  159.         FROM v
  160.        GROUP BY v.sec'  ;
  161.         EXECUTE IMMEDIATE r BULK COLLECT INTO t
  162.         USING beginDate, endDate, beginDate, endDate;
  163.  
  164.        CASE WHEN step(j) = 'RECALC' THEN
  165.                 task1.addToResult('beginDate'||tab||TO_CHAR(beginDate,'DD/MM/YYYY')||ret||
  166.                                  'endDate'  ||tab||TO_CHAR(endDate,'DD/MM/YYYY')||ret);
  167.                 task1.addToResult('Name' || tab || 'CntPaySent' || tab || 'CntFreeSent' || tab || 'SummSent' || tab || 'CntPayRet' || tab || 'CntFreeRet' || tab || 'SummRet' || ret);                
  168.                 FOR i IN 1..t.COUNT
  169.                 LOOP
  170.                   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);
  171.                 END LOOP;
  172.                
  173.             WHEN step(j) = 'FULL' THEN
  174.                 task2.addToResult('beginDate'||tab||TO_CHAR(beginDate,'DD/MM/YYYY')||ret||
  175.                                  'endDate'  ||tab||TO_CHAR(endDate,'DD/MM/YYYY')||ret);
  176.                 task2.addToResult('Name' || tab || 'CntPaySent' || tab || 'CntFreeSent' || tab || 'SummSent' || tab || 'CntPayRet' || tab || 'CntFreeRet' || tab || 'SummRet' || ret);                              
  177.                 FOR i IN 1..t.COUNT
  178.                 LOOP
  179.                   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);
  180.                 END LOOP;
  181.             ELSE
  182.                 task3.addToResult('beginDate'||tab||TO_CHAR(beginDate,'DD/MM/YYYY')||ret||
  183.                                  'endDate'  ||tab||TO_CHAR(endDate,'DD/MM/YYYY')||ret);
  184.                 task3.addToResult('Name' || tab || 'CntPaySent' || tab || 'CntFreeSent' || tab || 'SummSent' || tab || 'CntPayRet' || tab || 'CntFreeRet' || tab || 'SummRet' || ret);                              
  185.                 FOR i IN 1..t.COUNT
  186.                 LOOP
  187.                   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);
  188.                 END LOOP;
  189.           END CASE;
  190.       r := '';    
  191.       boxMode := '';
  192.       t.DELETE;        
  193.   END LOOP;
  194.   task1.save;
  195.   task1.free;
  196.   task2.save;
  197.   task2.free;
  198.   task3.save;
  199.   task3.free;
  200. END REP_20_Royalty_v2;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top