Oracle DB 12.1.0.2
Solaris 14
Hello Team,
I have a procedure which is running for more than 17 hours. It usually takes 2-3 hours to complete.
Explain plan is shown below. As per result below, the tables seem to be indexed.
SQL_ID a7uqx5ak9jtca, child number 0
-------------------------------------
SELECT /*+ PARALLEL(2) */ ACC.SUBSCRIBER_CODE_N, ACC.ACCOUNT_CODE_N,
ACC.ACCOUNT_LINK_CODE_N , ACC.STATUS_CODE_V, ACC.ACTIVATION_DATE_D,
ACC.ERASED_DATE_D, ACC.CURRENCY_CODE_V , ACC.BILL_CYCL_CODE_N,
ACC.SUBSCRIBER_CATEGORY_V, ACC.SUBSCRIBER_SUB_CATEGORY_V ,
ACC.ACCOUNT_NAME_V, ACC.ACCOUNT_TYPE_V,SUB.PROFILE_TYPE_V,SUB.EXT_SUBSCR
IBER_CODE_V FROM CB_ACCOUNT_MASTER ACC, CB_SUBSCRIBER_MASTER SUB WHERE
SUB.SUBSCRIBER_CODE_N = ACC.SUBSCRIBER_CODE_N AND (ACC.ACCOUNT_TYPE_V =
'FXL' OR EXISTS( SELECT 1 FROM CB_ACCOUNT_SERVICE_LIST ASL WHERE
ASL.ACCOUNT_CODE_N = ACC.ACCOUNT_CODE_N AND ASL.STATUS_CODE_V IN('AC',
'SP', 'PR', 'ER') AND ASL.CONTRACT_TYPE_V IN('N', 'H') ) ) ORDER BY
ACC.ACCOUNT_CODE_N
Plan hash value: 4209013835
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 22817 (100)| | | | | | | |
| 1 | SORT ORDER BY | | 593K| 57M| 176M| 22817 (1)| 00:00:02 | | | | 93M| 3042K| 82M (0)|
|* 2 | FILTER | | | | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ20001 | 1386K| 133M| | 11696 (2)| 00:00:01 | Q2,01 | P->S | QC (RAND) | | | |
|* 5 | HASH JOIN | | 1386K| 133M| | 11696 (2)| 00:00:01 | Q2,01 | PCWP | | 131M| 12M| 67M (0)|
| 6 | PX RECEIVE | | 1161K| 18M| | 6252 (2)| 00:00:01 | Q2,01 | PCWP | | | | |
| 7 | PX SEND BROADCAST | :TQ20000 | 1161K| 18M| | 6252 (2)| 00:00:01 | Q2,00 | P->P | BROADCAST | | | |
| 8 | PX BLOCK ITERATOR | | 1161K| 18M| | 6252 (2)| 00:00:01 | Q2,00 | PCWC | | | | |
|* 9 | TABLE ACCESS FULL | CB_SUBSCRIBER_MASTER | 1161K| 18M| | 6252 (2)| 00:00:01 | Q2,00 | PCWP | | | | |
| 10 | PX BLOCK ITERATOR | | 1391K| 111M| | 5437 (2)| 00:00:01 | Q2,01 | PCWC | | | | |
|* 11 | TABLE ACCESS FULL | CB_ACCOUNT_MASTER | 1391K| 111M| | 5437 (2)| 00:00:01 | Q2,01 | PCWP | | | | |
| 12 | PX COORDINATOR | | | | | | | | | | | | |
| 13 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 10 | | 4 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID BATCHED| CB_ACCOUNT_SERVICE_LIST | 1 | 10 | | 4 (0)| 00:00:01 | Q1,01 | PCWP | | | | |
| 15 | BUFFER SORT | | | | | | | Q1,01 | PCWC | | 49M| 2263K| 43M (0)|
| 16 | PX RECEIVE | | 2 | | | 3 (0)| 00:00:01 | Q1,01 | PCWP | | | | |
| 17 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 2 | | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK| | | |
| 18 | PX SELECTOR | | | | | | | Q1,00 | SCWC | | | | |
|* 19 | INDEX RANGE SCAN | CB_ACC_SERVICE_LIST#ACC_CODE | 2 | | | 3 (0)| 00:00:01 | Q1,00 | SCWP | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("ACC"."ACCOUNT_TYPE_V"='FXL' OR IS NOT NULL))
5 - access("SUB"."SUBSCRIBER_CODE_N"="ACC"."SUBSCRIBER_CODE_N")
9 - access(:Z>=:Z AND :Z<=:Z)
11 - access(:Z>=:Z AND :Z<=:Z)
14 - filter((INTERNAL_FUNCTION("ASL"."CONTRACT_TYPE_V") AND INTERNAL_FUNCTION("ASL"."STATUS_CODE_V")))
19 - access("ASL"."ACCOUNT_CODE_N"=:B1)
Note
-----
- Degree of Parallelism is 2 because of hint
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID a7uqx5ak9jtca, child number 1
-------------------------------------
SELECT /*+ PARALLEL(2) */ ACC.SUBSCRIBER_CODE_N, ACC.ACCOUNT_CODE_N,
ACC.ACCOUNT_LINK_CODE_N , ACC.STATUS_CODE_V, ACC.ACTIVATION_DATE_D,
ACC.ERASED_DATE_D, ACC.CURRENCY_CODE_V , ACC.BILL_CYCL_CODE_N,
ACC.SUBSCRIBER_CATEGORY_V, ACC.SUBSCRIBER_SUB_CATEGORY_V ,
ACC.ACCOUNT_NAME_V, ACC.ACCOUNT_TYPE_V,SUB.PROFILE_TYPE_V,SUB.EXT_SUBSCR
IBER_CODE_V FROM CB_ACCOUNT_MASTER ACC, CB_SUBSCRIBER_MASTER SUB WHERE
SUB.SUBSCRIBER_CODE_N = ACC.SUBSCRIBER_CODE_N AND (ACC.ACCOUNT_TYPE_V =
'FXL' OR EXISTS( SELECT 1 FROM CB_ACCOUNT_SERVICE_LIST ASL WHERE
ASL.ACCOUNT_CODE_N = ACC.ACCOUNT_CODE_N AND ASL.STATUS_CODE_V IN('AC',
'SP', 'PR', 'ER') AND ASL.CONTRACT_TYPE_V IN('N', 'H') ) ) ORDER BY
ACC.ACCOUNT_CODE_N
Plan hash value: 3190056323
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 22834 (100)| | | | | | | |
| 1 | SORT ORDER BY | | 594K| 57M| 176M| 22834 (1)| 00:00:02 | | | | 64M| 2565K| |
|* 2 | FILTER | | | | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ20000 | 1388K| 133M| | 11696 (2)| 00:00:01 | Q2,00 | P->S | QC (RAND) | | | |
|* 5 | HASH JOIN | | 1388K| 133M| | 11696 (2)| 00:00:01 | Q2,00 | PCWP | | 131M| 12M| 67M (0)|
| 6 | TABLE ACCESS FULL | CB_SUBSCRIBER_MASTER | 1163K| 18M| | 6252 (2)| 00:00:01 | Q2,00 | PCWP | | | | |
| 7 | PX BLOCK ITERATOR | | 1391K| 111M| | 5437 (2)| 00:00:01 | Q2,00 | PCWC | | | | |
|* 8 | TABLE ACCESS FULL | CB_ACCOUNT_MASTER | 1391K| 111M| | 5437 (2)| 00:00:01 | Q2,00 | PCWP | | | | |
| 9 | PX COORDINATOR | | | | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 10 | | 4 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID BATCHED| CB_ACCOUNT_SERVICE_LIST | 1 | 10 | | 4 (0)| 00:00:01 | Q1,01 | PCWP | | | | |
| 12 | BUFFER SORT | | | | | | | Q1,01 | PCWC | | 73728 | 73728 | |
| 13 | PX RECEIVE | | 2 | | | 3 (0)| 00:00:01 | Q1,01 | PCWP | | | | |
| 14 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 2 | | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK| | | |
| 15 | PX SELECTOR | | | | | | | Q1,00 | SCWC | | | | |
|* 16 | INDEX RANGE SCAN | CB_ACC_SERVICE_LIST#ACC_CODE | 2 | | | 3 (0)| 00:00:01 | Q1,00 | SCWP | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("ACC"."ACCOUNT_TYPE_V"='FXL' OR IS NOT NULL))
5 - access("SUB"."SUBSCRIBER_CODE_N"="ACC"."SUBSCRIBER_CODE_N")
8 - access(:Z>=:Z AND :Z<=:Z)
11 - filter((INTERNAL_FUNCTION("ASL"."CONTRACT_TYPE_V") AND INTERNAL_FUNCTION("ASL"."STATUS_CODE_V")))
16 - access("ASL"."ACCOUNT_CODE_N"=:B1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
PROCEDURE IS SHOWN BELOW:
create or replace PROCEDURE GEN_SUBS_SUMMARY_DEB_REP (
ip_to_date_d IN DATE
, ip_from_date_d IN DATE DEFAULT To_Date('01-JAN-1900', 'DD-MON-YYYY')
, ip_commit_size_n IN PLS_INTEGER DEFAULT 250
) AS
l_from_date_t TIMESTAMP(6) WITH TIME ZONE := Trunc(ip_from_date_d);
l_to_date_t TIMESTAMP(6) WITH TIME ZONE := Trunc(ip_to_date_d) + 1;
CURSOR acc_cur IS
SELECT /*+ PARALLEL(2) */ acc.subscriber_code_n, acc.account_code_n, acc.account_link_code_n
, acc.status_code_v, acc.activation_date_d, acc.erased_date_d, acc.currency_code_v
, acc.bill_cycl_code_n, acc.subscriber_category_v, acc.subscriber_sub_category_v
, acc.account_name_v, acc.account_type_v,sub.profile_type_v,sub.ext_subscriber_code_v
FROM cb_account_master acc, cb_subscriber_master sub
WHERE sub.subscriber_code_n = acc.subscriber_code_n
-- AND ACCOUNT_link_CODE_N=23615141
AND (acc.account_type_v = 'FXL' --All FXL accounts
OR EXISTS( --Other postpaid/hybrid accounts
SELECT 1 FROM cb_account_service_list asl
WHERE asl.account_code_n = acc.account_code_n
AND asl.status_code_v IN('AC', 'SP', 'PR', 'ER')
AND asl.contract_type_v IN('N', 'H')
)
)
--AND acc.activation_date_d >= l_from_date_t
--AND acc.activation_date_d < l_to_date_t
ORDER BY acc.account_code_n;
TYPE acc_rec IS RECORD(
subscriber_code_n SUBSCRIBER_SUMMARY_DEB_DTLS.subscriber_code_n%TYPE
, account_code_n SUBSCRIBER_SUMMARY_DEB_DTLS.account_code_n%TYPE
, main_acc_link_code_n SUBSCRIBER_SUMMARY_DEB_DTLS.main_acc_link_code_n%TYPE
, status_code_v SUBSCRIBER_SUMMARY_DEB_DTLS.status_code_v%TYPE
, activation_date_d SUBSCRIBER_SUMMARY_DEB_DTLS.activation_date_d%TYPE
, erased_date_d SUBSCRIBER_SUMMARY_DEB_DTLS.erased_date_d%TYPE
, currency_code_v SUBSCRIBER_SUMMARY_DEB_DTLS.currency_code_v%TYPE
, bill_cycl_code_n SUBSCRIBER_SUMMARY_DEB_DTLS.bill_cycl_code_n%TYPE
, category_code_v SUBSCRIBER_SUMMARY_DEB_DTLS.category_code_v%TYPE
, sub_category_code_v SUBSCRIBER_SUMMARY_DEB_DTLS.sub_category_code_v%TYPE
, account_name_v SUBSCRIBER_SUMMARY_DEB_DTLS.account_name_v%TYPE
, account_type_v SUBSCRIBER_SUMMARY_DEB_DTLS.account_type_v%TYPE
, profile_type_v SUBSCRIBER_SUMMARY_DEB_DTLS.profile_type_v%TYPE
, ext_subscriber_code_v SUBSCRIBER_SUMMARY_DEB_DTLS.ext_subscriber_code_v%TYPE);
TYPE acc_rec_type IS TABLE OF acc_rec
INDEX BY BINARY_INTEGER;
l_acc_rec_a acc_rec_type;
l_stop_flag_v VARCHAR2(1);
PROCEDURE dsql(
i_sql_v VARCHAR2
) AS
BEGIN
EXECUTE IMMEDIATE i_sql_v;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END dsql;
BEGIN
----drop index, delete old records if exists
dsql('DROP INDEX sfs#bc_act_acc_malc');
dsql('DROP INDEX sfs#malc');
dsql('DROP INDEX sfs#acc');
dsql('DROP INDEX bill_cycle_desc#sfsd');
--
--
-- DELETE SUBSCRIBER_SUMMARY_DEB_DTLS
-- WHERE cutoff_date_d = l_to_date_t - INTERVAL '0.000001' SECOND;
-- COMMIT;
dsql('DROP INDEX sfs#cod');
----start process, open cursor
OPEN acc_cur;
LOOP
SELECT stop_flag_V
INTO l_stop_flag_v
FROM cb_stop_start_flag;
EXIT WHEN l_stop_flag_v ='Y';
BEGIN
FETCH acc_cur BULK COLLECT
INTO l_acc_rec_a
LIMIT ip_commit_size_n;
EXIT WHEN l_acc_rec_a.COUNT = 0;
FOR idx IN 1 .. l_acc_rec_a.COUNT
LOOP
DECLARE
l_sfs_r SUBSCRIBER_SUMMARY_DEB_DTLS%ROWTYPE;
l_total_receipt_n SUBSCRIBER_SUMMARY_DEB_DTLS.total_payment_n%TYPE;
BEGIN
----invoice
SELECT Nvl(Sum(invoice_amt_n)/100, 0)
, Nvl(Sum(invoice_tax_n)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n <= 30 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 31 AND 60 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 61 AND 90 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 91 AND 120 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 121 AND 150 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 151 AND 180 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 181 AND 210 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 211 AND 240 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 241 AND 270 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 271 AND 300 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 301 AND 330 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 331 AND 360 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 361 AND 390 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 391 AND 420 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 421 AND 450 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 451 AND 480 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 481 AND 510 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 511 AND 540 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 541 AND 570 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 571 AND 600 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 601 AND 630 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 631 AND 660 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 661 AND 690 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 691 AND 720 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 721 AND 750 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n BETWEEN 751 AND 780 THEN inv_amt_n END)/100, 0)
, Nvl(Sum(CASE WHEN invoice_days_n >= 781 THEN inv_amt_n END)/100, 0)
INTO l_sfs_r.total_invoice_n
, l_sfs_r.total_tax_n
, l_sfs_r.inv_slab_1_30_n
, l_sfs_r.inv_slab_31_60_n
, l_sfs_r.inv_slab_61_90_n
, l_sfs_r.inv_slab_91_120_n
, l_sfs_r.inv_slab_121_150_n
, l_sfs_r.inv_slab_151_180_n
, l_sfs_r.inv_slab_181_210_n
, l_sfs_r.inv_slab_211_240_n
, l_sfs_r.inv_slab_241_270_n
, l_sfs_r.inv_slab_271_300_n
, l_sfs_r.inv_slab_301_330_n
, l_sfs_r.inv_slab_331_360_n
, l_sfs_r.inv_slab_361_390_n
, l_sfs_r.inv_slab_391_420_n
, l_sfs_r.inv_slab_421_450_n
, l_sfs_r.inv_slab_451_480_n
, l_sfs_r.inv_slab_481_510_n
, l_sfs_r.inv_slab_511_540_n
, l_sfs_r.inv_slab_541_570_n
, l_sfs_r.inv_slab_571_600_n
, l_sfs_r.inv_slab_601_630_n
, l_sfs_r.inv_slab_631_660_n
, l_sfs_r.inv_slab_661_690_n
, l_sfs_r.inv_slab_691_720_n
, l_sfs_r.inv_slab_721_750_n
, l_sfs_r.inv_slab_751_780_n
, l_sfs_r.inv_slab_aft_780_n
FROM ( SELECT invoice_amt_n
, invoice_tax_n
,(invoice_amt_n + invoice_tax_n) inv_amt_n
, (Trunc(l_to_date_t) - Trunc(trans_date_d + 1)) invoice_days_n
FROM cb_invoice inv
WHERE account_link_code_n = l_acc_rec_a(idx).main_acc_link_code_n
AND invoice_generated_on_d >= l_from_date_t
AND invoice_generated_on_d < l_to_date_t
);
----receipts (total receipts, migrated receipts)
select Nvl(-Sum(trans_amt_n)/100,0)
, Nvl(-Sum(Decode(user_code_n, 2, trans_amt, 0))/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n <= 30 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 31 AND 60 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 61 AND 90 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 91 AND 120 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 121 AND 150 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 151 AND 180 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 181 AND 210 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 211 AND 240 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 241 AND 270 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 271 AND 300 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 301 AND 330 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 331 AND 360 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 361 AND 390 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 391 AND 420 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 421 AND 450 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 451 AND 480 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 481 AND 510 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 511 AND 540 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 541 AND 570 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 571 AND 600 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 601 AND 630 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 631 AND 660 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 661 AND 690 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 691 AND 720 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 721 AND 750 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n BETWEEN 751 AND 780 THEN trans_amt_n END)/100, 0)
, Nvl(-Sum(CASE WHEN trans_days_n >= 781 THEN trans_amt_n END)/100, 0)
INTO l_sfs_r.total_receipt_n
, l_sfs_r.total_mig_receipt_n
, l_sfs_r.receipt_slab_1_30_n
, l_sfs_r.receipt_slab_31_60_n
, l_sfs_r.receipt_slab_61_90_n
, l_sfs_r.receipt_slab_91_120_n
, l_sfs_r.receipt_slab_121_150_n
, l_sfs_r.receipt_slab_151_180_n
, l_sfs_r.receipt_slab_181_210_n
, l_sfs_r.receipt_slab_211_240_n
, l_sfs_r.receipt_slab_241_270_n
, l_sfs_r.receipt_slab_271_300_n
, l_sfs_r.receipt_slab_301_330_n
, l_sfs_r.receipt_slab_331_360_n
, l_sfs_r.receipt_slab_361_390_n
, l_sfs_r.receipt_slab_391_420_n
, l_sfs_r.receipt_slab_421_450_n
, l_sfs_r.receipt_slab_451_480_n
, l_sfs_r.receipt_slab_481_510_n
, l_sfs_r.receipt_slab_511_540_n
, l_sfs_r.receipt_slab_541_570_n
, l_sfs_r.receipt_slab_571_600_n
, l_sfs_r.receipt_slab_601_630_n
, l_sfs_r.receipt_slab_631_660_n
, l_sfs_r.receipt_slab_661_690_n
, l_sfs_r.receipt_slab_691_720_n
, l_sfs_r.receipt_slab_721_750_n
, l_sfs_r.receipt_slab_751_780_n
, l_sfs_r.receipt_slab_aft_780_n
FROM ( SELECT trans_amt_n
, trans_amt_n trans_amt --total receipts
,user_code_n
, Trunc(l_to_date_t) - Trunc(trans_date_d + 1) trans_days_n
FROM cb_receipts
WHERE account_link_code_n = l_acc_rec_a(idx).main_acc_link_code_n
AND trans_optn_v = 'R'
AND cash_box_coll_type_v = 'SRCT'
AND Decode(user_code_n, 2, trans_date_d, last_modified_date_d) >= l_from_date_t
AND Decode(user_code_n, 2, trans_date_d, last_modified_date_d) < l_to_date_t);
----receipts (non-migrated receipts + adjusted against any debit)
SELECT l_sfs_r.total_payment_n + Nvl(-Sum(a.paid_amount_n)/100, 0)
INTO l_sfs_r.total_payment_n
FROM cb_receipts R, cb_receipts_adjust A
WHERE r.account_link_code_n = l_acc_rec_a(idx).main_acc_link_code_n
AND r.trans_optn_v = 'R'
AND r.cash_box_coll_type_v = 'SRCT'
AND r.user_code_n <> 2 --skip migrated receipts
AND r.trans_num_v = a.rcpt_trans_num_v
AND Decode(r.user_code_n, 2, r.trans_date_d, r.last_modified_date_d) >= l_from_date_t
AND Decode(r.user_code_n, 2, r.trans_date_d, r.last_modified_date_d) < l_to_date_t
AND a.inv_trans_date_d < l_to_date_t; --transactions got adjusted only till the to_date of report
----receipts (unadjusted/advance = total receipts - (migrated+adjusted) receipts)
l_sfs_r.total_advance_n := l_total_receipt_n - l_sfs_r.total_payment_n;
IF l_acc_rec_a(idx).account_type_v = 'FXL' THEN
SELECT l_sfs_r.total_advance_n+Nvl(-Sum(trans_amt_n)/100, 0),l_sfs_r.total_payment_n+Nvl(Sum(trans_amt_n)/100, 0)
INTO l_sfs_r.total_advance_n,l_sfs_r.total_payment_n
FROM CB_RECEIPTS R
WHERE r.account_link_code_n = l_acc_rec_a(idx).main_acc_link_code_n
AND r.trans_optn_v = 'R'
AND r.cash_box_coll_type_v = 'SRCT'
AND NOT EXISTS(SELECT 1 FROM CB_SUBS_PAID_ADV_DTLS A WHERE A.MAIN_ACCOUNT_LINK_CODE_N=R.ACCOUNT_LINK_CODE_N
AND A.RECEIPT_NO_V=R.TRANS_NUM_V)
AND r.user_code_n = 2
AND r.description_v = 'MIGRATED_ADVANCE';
END IF;
----cheque bounce, surcharge waiveoff, credit note, transfer, refund, sales, sales return, surcharge, debit note