Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Add Comment
Please, Sign In to add comment