Home » SQL & PL/SQL » SQL & PL/SQL » CURSOR taking more time (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit)
CURSOR taking more time [message #661171] |
Fri, 10 March 2017 02:27 |
|
amarjadhav
Messages: 40 Registered: April 2011 Location: bangalore
|
Member |
|
|
Dear Experts,
I have sql file with main cursor and sub-queries. Below is the cursor which i suspect that taking more time. And query reruns around 1500 - 2000 records which currently running more than 2 hours,
Kindly help or any suggestion to reduce the cursor execution time.
Below is the Main Cursor,
SELECT NVL(A.PRDEM,0),'PRDEM',NVL(B.INDEM,0),'INDEM',GAM.FORACID,GAM.ACID,GAM.ACCT_CRNCY_CODE
FROM
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS PRDEM,ACID AS AACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='PRDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) A ,
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS INDEM ,ACID AS BACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='INDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) B,
DBSCUST.C_MPAMT D,TBAADM.LRCT E,TBAADM.view_GAM GAM,TBAADM.LAM LAM
WHERE GAM.FORACID = D.FORACID
AND GAM.SCHM_CODE = E.REF_CODE
AND GAM.ACID = LAM.ACID
AND (LAM.PAYOFF_FLG != 'Y' OR LAM.PAYOFF_FLG IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y' OR LAM.CHRGE_OFF_FLG IS NULL)
AND A.AACID(+) = GAM.ACID
AND B.BACID(+) = GAM.ACID
AND E.REF_REC_TYPE ='CBGSC'
AND E.DEL_FLG='N'
AND E.SCHM_REF_TYPE='CJ'
AND GAM.SOL_ID = '367'
AND GAM.ACCT_CLS_FLG = 'N'
AND GAM.BANK_ID = 'SG'
AND D.MODE_OF_PAYMNT = 'ACT'
AND D.ACCNT_TYPE IS NOT NULL
AND (A.PRDEM >0 OR B.INDEM > 0)
AND ((D.LSA_COMM_DATE IS NULL) OR (D.LSA_COMM_DATE <= sysdate));
and AUTOTRACE for above cursor,
1521 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2592180231
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10802 | 1824K| | 55121 (1)| 00:11:02 |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN OUTER | | 10802 | 1824K| | 55121 (1)| 00:11:02 |
|* 3 | HASH JOIN OUTER | | 10802 | 1434K| | 30374 (1)| 00:06:05 |
| 4 | NESTED LOOPS | | 10802 | 1044K| | 5626 (1)| 00:01:08 |
| 5 | NESTED LOOPS | | 10803 | 1044K| | 5626 (1)| 00:01:08 |
|* 6 | HASH JOIN | | 10803 | 907K| | 2384 (1)| 00:00:29 |
|* 7 | HASH JOIN | | 10803 | 632K| | 1234 (1)| 00:00:15 |
|* 8 | TABLE ACCESS BY INDEX ROWID| LARGE_REFERENCE_CODE_TABLE | 31 | 558 | | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_LARGE_REFERENCE_CODE_TABLE | 63 | | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| GENERAL_ACCT_MAST_TABLE | 31233 | 1281K| | 1230 (1)| 00:00:15 |
|* 11 | INDEX SKIP SCAN | IDX_GAM_ALT1_SHORT_NAME | 62465 | | | 22 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | CUST_MPAMT_TBL | 45886 | 1165K| | 1149 (1)| 00:00:14 |
|* 13 | INDEX RANGE SCAN | IDX_LA_ACCT_MAST_TABLE | 1 | | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | LA_ACCT_MAST_TABLE | 1 | 13 | | 1 (0)| 00:00:01 |
| 15 | VIEW | | 85054 | 3073K| | 24747 (2)| 00:04:57 |
| 16 | HASH GROUP BY | | 85054 | 2491K| 61M| 24747 (2)| 00:04:57 |
|* 17 | TABLE ACCESS FULL | LA_DMD_TABLE | 1345K| 38M| | 20391 (1)| 00:04:05 |
| 18 | VIEW | | 85054 | 3073K| | 24747 (2)| 00:04:57 |
| 19 | HASH GROUP BY | | 85054 | 2491K| 61M| 24747 (2)| 00:04:57 |
|* 20 | TABLE ACCESS FULL | LA_DMD_TABLE | 1345K| 38M| | 20391 (1)| 00:04:05 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."PRDEM">0 OR "B"."INDEM">0)
2 - access("B"."BACID"(+)="GAM"."ACID")
3 - access("A"."AACID"(+)="GAM"."ACID")
6 - access("GAM"."FORACID"="D"."FORACID")
7 - access("GAM"."SCHM_CODE"="E"."REF_CODE")
8 - filter("E"."DEL_FLG"='N')
9 - access("E"."REF_REC_TYPE"='CBGSC' AND "E"."SCHM_REF_TYPE"='CJ')
filter("E"."SCHM_REF_TYPE"='CJ')
10 - filter("GAM"."ACCT_CLS_FLG"='N' AND "GAM"."BANK_ID"='SG')
11 - access("GAM"."SOL_ID"='367')
filter("GAM"."SOL_ID"='367')
12 - filter("D"."ACCNT_TYPE" IS NOT NULL AND "D"."MODE_OF_PAYMNT"='ACT' AND ("D"."LSA_COMM_DATE" IS NULL OR
"D"."LSA_COMM_DATE"<=SYSDATE@!))
13 - access("GAM"."ACID"="LAM"."ACID")
14 - filter(("LAM"."CHRGE_OFF_FLG" IS NULL OR "LAM"."CHRGE_OFF_FLG"<>'Y') AND ("LAM"."PAYOFF_FLG"<>'Y' OR
"LAM"."PAYOFF_FLG" IS NULL))
17 - filter("DMD_FLOW_ID"='PRDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')
20 - filter("DMD_FLOW_ID"='INDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')
Statistics
----------------------------------------------------------
80 recursive calls
0 db block gets
262477 consistent gets
152573 physical reads
0 redo size
72481 bytes sent via SQL*Net to client
1631 bytes received via SQL*Net from client
103 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1521 rows processed
|
|
|
|
Re: CURSOR taking more time [message #661173 is a reply to message #661172] |
Fri, 10 March 2017 02:44 |
|
amarjadhav
Messages: 40 Registered: April 2011 Location: bangalore
|
Member |
|
|
Sorry for the inconvenience,
SELECT NVL(A.PRDEM,0),'PRDEM',NVL(B.INDEM,0),'INDEM',GAM.FORACID,GAM.ACID,GAM.ACCT_CRNCY_CODE
FROM
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS PRDEM,ACID AS AACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='PRDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) A ,
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS INDEM ,ACID AS BACID FROM TBAADM.LDT WHERE DMD_FLOW_ID ='INDEM' AND DEL_FLG != 'Y' AND BANK_ID = 'SG' GROUP BY ACID) B,
DBSCUST.C_MPAMT D,TBAADM.LRCT E,TBAADM.view_GAM GAM,TBAADM.LAM LAM
WHERE GAM.FORACID = D.FORACID
AND GAM.SCHM_CODE = E.REF_CODE
AND GAM.ACID = LAM.ACID
AND (LAM.PAYOFF_FLG != 'Y' OR LAM.PAYOFF_FLG IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y' OR LAM.CHRGE_OFF_FLG IS NULL)
AND A.AACID(+) = GAM.ACID
AND B.BACID(+) = GAM.ACID
AND E.REF_REC_TYPE ='CBGSC'
AND E.DEL_FLG='N'
AND E.SCHM_REF_TYPE='CJ'
AND GAM.SOL_ID = '367'
AND GAM.ACCT_CLS_FLG = 'N'
AND GAM.BANK_ID = 'SG'
AND D.MODE_OF_PAYMNT = 'ACT'
AND D.ACCNT_TYPE IS NOT NULL
AND (A.PRDEM >0 OR B.INDEM > 0)
AND ((D.LSA_COMM_DATE IS NULL) OR (D.LSA_COMM_DATE <= sysdate));
Explain plan for above cursor query,
1521 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2592180231
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10802 | 1824K| | 55121 (1)| 00:11:02 |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN OUTER | | 10802 | 1824K| | 55121 (1)| 00:11:02 |
|* 3 | HASH JOIN OUTER | | 10802 | 1434K| | 30374 (1)| 00:06:05 |
| 4 | NESTED LOOPS | | 10802 | 1044K| | 5626 (1)| 00:01:08 |
| 5 | NESTED LOOPS | | 10803 | 1044K| | 5626 (1)| 00:01:08 |
|* 6 | HASH JOIN | | 10803 | 907K| | 2384 (1)| 00:00:29 |
|* 7 | HASH JOIN | | 10803 | 632K| | 1234 (1)| 00:00:15 |
|* 8 | TABLE ACCESS BY INDEX ROWID| LARGE_REFERENCE_CODE_TABLE | 31 | 558 | | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_LARGE_REFERENCE_CODE_TABLE | 63 | | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| GENERAL_ACCT_MAST_TABLE | 31233 | 1281K| | 1230 (1)| 00:00:15 |
|* 11 | INDEX SKIP SCAN | IDX_GAM_ALT1_SHORT_NAME | 62465 | | | 22 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | CUST_MPAMT_TBL | 45886 | 1165K| | 1149 (1)| 00:00:14 |
|* 13 | INDEX RANGE SCAN | IDX_LA_ACCT_MAST_TABLE | 1 | | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | LA_ACCT_MAST_TABLE | 1 | 13 | | 1 (0)| 00:00:01 |
| 15 | VIEW | | 85054 | 3073K| | 24747 (2)| 00:04:57 |
| 16 | HASH GROUP BY | | 85054 | 2491K| 61M| 24747 (2)| 00:04:57 |
|* 17 | TABLE ACCESS FULL | LA_DMD_TABLE | 1345K| 38M| | 20391 (1)| 00:04:05 |
| 18 | VIEW | | 85054 | 3073K| | 24747 (2)| 00:04:57 |
| 19 | HASH GROUP BY | | 85054 | 2491K| 61M| 24747 (2)| 00:04:57 |
|* 20 | TABLE ACCESS FULL | LA_DMD_TABLE | 1345K| 38M| | 20391 (1)| 00:04:05 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."PRDEM">0 OR "B"."INDEM">0)
2 - access("B"."BACID"(+)="GAM"."ACID")
3 - access("A"."AACID"(+)="GAM"."ACID")
6 - access("GAM"."FORACID"="D"."FORACID")
7 - access("GAM"."SCHM_CODE"="E"."REF_CODE")
8 - filter("E"."DEL_FLG"='N')
9 - access("E"."REF_REC_TYPE"='CBGSC' AND "E"."SCHM_REF_TYPE"='CJ')
filter("E"."SCHM_REF_TYPE"='CJ')
10 - filter("GAM"."ACCT_CLS_FLG"='N' AND "GAM"."BANK_ID"='SG')
11 - access("GAM"."SOL_ID"='367')
filter("GAM"."SOL_ID"='367')
12 - filter("D"."ACCNT_TYPE" IS NOT NULL AND "D"."MODE_OF_PAYMNT"='ACT' AND ("D"."LSA_COMM_DATE" IS NULL OR
"D"."LSA_COMM_DATE"<=SYSDATE@!))
13 - access("GAM"."ACID"="LAM"."ACID")
14 - filter(("LAM"."CHRGE_OFF_FLG" IS NULL OR "LAM"."CHRGE_OFF_FLG"<>'Y') AND ("LAM"."PAYOFF_FLG"<>'Y' OR
"LAM"."PAYOFF_FLG" IS NULL))
17 - filter("DMD_FLOW_ID"='PRDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')
20 - filter("DMD_FLOW_ID"='INDEM' AND "DEL_FLG"<>'Y' AND "BANK_ID"='SG')
Statistics
----------------------------------------------------------
80 recursive calls
0 db block gets
262477 consistent gets
152573 physical reads
0 redo size
72481 bytes sent via SQL*Net to client
1631 bytes received via SQL*Net from client
103 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1521 rows processed
|
|
|
|
Re: CURSOR taking more time [message #661175 is a reply to message #661173] |
Fri, 10 March 2017 02:52 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
What is the code for the TBAADM.LDT view? It isn't merging.
And have you tried indexing LA_DMD_TABLE on DMD_FLOW_ID,BANK_ID,DEL_FLG ?
--update sorry, I misread the plan: it is the two subqueries that don't merge, because they have aggregations. Can you not re-write them into one query, grouping by ACID and DMD_FLOW_ID, so that you need only one pass through the table?
[Updated on: Fri, 10 March 2017 03:22] Report message to a moderator
|
|
|
Re: CURSOR taking more time [message #661176 is a reply to message #661175] |
Fri, 10 March 2017 03:31 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Does this give the same result?
SELECT NVL(SUM(CASE WHEN a.DMD_FLOW_ID = 'PRDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END),0),
'PRDEM',
NVL(SUM(CASE WHEN a.DMD_FLOW_ID = 'INDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END),0),
'INDEM',
GAM.FORACID,
GAM.ACID,
GAM.ACCT_CRNCY_CODE
FROM TBAADM.view_GAM GAM
JOIN DBSCUST.C_MPAMT D ON GAM.FORACID = D.FORACID
JOIN TBAADM.LRCT E ON GAM.SCHM_CODE = E.REF_CODE
JOIN TBAADM.LAM LAM ON GAM.ACID = LAM.ACID
LEFT JOIN TBAADM.LDT A ON A.ACID = GAM.ACID
AND A.BANK_ID = GAM.BANK_ID
AND A.DMD_FLOW_ID IN ('PRDEM', 'INDEM')
AND A.DEL_FLG != 'Y'
WHERE (LAM.PAYOFF_FLG != 'Y' OR LAM.PAYOFF_FLG IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y' OR LAM.CHRGE_OFF_FLG IS NULL)
AND E.REF_REC_TYPE = 'CBGSC'
AND E.DEL_FLG = 'N'
AND E.SCHM_REF_TYPE = 'CJ'
AND GAM.SOL_ID = '367'
AND GAM.ACCT_CLS_FLG = 'N'
AND GAM.BANK_ID = 'SG'
AND D.MODE_OF_PAYMNT = 'ACT'
AND D.ACCNT_TYPE IS NOT NULL
AND (D.LSA_COMM_DATE IS NULL OR D.LSA_COMM_DATE <= sysdate)
GROUP BY GAM.FORACID,
GAM.ACID,
GAM.ACCT_CRNCY_CODE
HAVING SUM(CASE WHEN a.DMD_FLOW_ID = 'PRDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END) > 0
OR SUM(CASE WHEN a.DMD_FLOW_ID = 'INDEM' THEN a.DMD_AMT - a.TOT_ADJ_AMT END) > 0;
[Updated on: Fri, 10 March 2017 03:59] Report message to a moderator
|
|
|
|
|
|
|
Re: CURSOR taking more time [message #661196 is a reply to message #661173] |
Fri, 10 March 2017 06:27 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
[quote title=amarjadhav wrote on Fri, 10 March 2017 02:44]Sorry for the inconvenience,
Well, you used code tags, but that doesn't help the lack of readability due to lack of formatting to begin with. For your own sanity, you should learn to format your code with logical indention.
Like this: (took me longer to write this sentence than it did to reformat your code in SQL Developer!):
SELECT NVL(A.PRDEM,0),
'PRDEM',
NVL(B.INDEM,0),
'INDEM',
GAM.FORACID,
GAM.ACID,
GAM.ACCT_CRNCY_CODE
FROM
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS PRDEM,
ACID AS AACID
FROM TBAADM.LDT
WHERE DMD_FLOW_ID ='PRDEM'
AND DEL_FLG != 'Y'
AND BANK_ID = 'SG'
GROUP BY ACID
) A ,
(SELECT SUM(DMD_AMT-TOT_ADJ_AMT) AS INDEM ,
ACID AS BACID
FROM TBAADM.LDT
WHERE DMD_FLOW_ID ='INDEM'
AND DEL_FLG != 'Y'
AND BANK_ID = 'SG'
GROUP BY ACID
) B,
DBSCUST.C_MPAMT D,
TBAADM.LRCT E,
TBAADM.view_GAM GAM,
TBAADM.LAM LAM
WHERE GAM.FORACID = D.FORACID
AND GAM.SCHM_CODE = E.REF_CODE
AND GAM.ACID = LAM.ACID
AND (LAM.PAYOFF_FLG != 'Y'
OR LAM.PAYOFF_FLG IS NULL)
AND (LAM.CHRGE_OFF_FLG != 'Y'
OR LAM.CHRGE_OFF_FLG IS NULL)
AND A.AACID(+) = GAM.ACID
AND B.BACID(+) = GAM.ACID
AND E.REF_REC_TYPE ='CBGSC'
AND E.DEL_FLG ='N'
AND E.SCHM_REF_TYPE ='CJ'
AND GAM.SOL_ID = '367'
AND GAM.ACCT_CLS_FLG = 'N'
AND GAM.BANK_ID = 'SG'
AND D.MODE_OF_PAYMNT = 'ACT'
AND D.ACCNT_TYPE IS NOT NULL
AND (A.PRDEM >0
OR B.INDEM > 0)
AND ((D.LSA_COMM_DATE IS NULL)
OR (D.LSA_COMM_DATE <= sysdate));
|
|
|
Re: CURSOR taking more time [message #661562 is a reply to message #661171] |
Thu, 23 March 2017 14:30 |
|
mikek
Messages: 29 Registered: January 2017
|
Junior Member |
|
|
It is a very complex SQL Statement so I encourage
further review/testing/modification of the included
suggestions.
Some Analysis Notes:
Just an FYI - not sure, but the "gam" usage is possibly
using a View "tbaadm.view_gam". Explain Plan does not appear
to show any issues.
Uses Synonyms referencing Tables(View?)
Synonym Table Name Script Table Aliases
------- -------------------------- --------------------
ldt LA_DMD_TABLE a and b
c_mpamt CUST_MPAMT_TBL d
lrct LARGE_REFERENCE_CODE_TABLE e
gam GENERAL_ACCT_MAST_TABLE gam
(View? "view_gam")
lam LA_ACCT_MAST_TABLE lam
Indexing Suggestions:
1. CUST_MPAMT_TBL(c_mpamt)
Check Table CUST_MPAMT_TBL for an index including the Column "foracid".
In the Explain Plan the Table is showing "TABLE ACCESS FULL".
Note: Index Name and Tablespace will need modification. If no index
exist I would suggest the following Index. Research Needed.
CREATE INDEX ak_cust_mpamt_tbl
ON cust_mpamt_tbl(foracid)
TABLESPACE ???;
2. LA_DMD_TABLE (ldt)
Check LA_DMD_TABLE for an index including the Column "acid".
In the Explain Plan the Table is showing "TABLE ACCESS FULL" twice
to satisfy a Sum of a specific type.
As suggested this should be looked at for a rewrite.
Note: Index Name and Tablespace will need modification. If no index
exist I would suggest the following Composite Index. Research Needed.
CREATE INDEX ak_la_dmd_table
ON la_dmd_table(acid, dmd_flow_id)
TABLESPACE ???;
Formatted SQL Based on Original Post:
(Some Reformatting was Done, Formatted SQL is Easier to Analyze)
SELECT NVL(a.prdem, 0),
'PRDEM',
NVL(b.indem, 0),
'INDEM',
gam.foracid,
gam.acid,
gam.acct_crncy_code
FROM ( SELECT SUM(dmd_amt - tot_adj_amt) AS PRDEM,
acid AS AACID
FROM tbaadm.ldt
WHERE dmd_flow_id = 'PRDEM'
AND del_flg != 'Y'
AND bank_id = 'SG'
GROUP BY acid
) a,
( SELECT SUM(dmd_amt - tot_adj_amt) AS indem,
acid AS BACID
FROM tbaadm.ldt
WHERE dmd_flow_id = 'INDEM'
AND del_flg != 'Y'
AND bank_id = 'SG'
GROUP BY acid
) b,
dbscust.c_mpamt d,
tbaadm.lrct e,
tbaadm.view_gam gam,
tbaadm.lam lam
WHERE gam.acid = a.aacid(+)
AND gam.acid = b.bacid(+)
AND gam.foracid = d.foracid
AND d.mode_of_paymnt = 'ACT'
AND d.accnt_type IS NOT NULL
AND ( ( d.lsa_comm_date IS NULL )
OR ( d.lsa_comm_date <= SYSDATE )
)
AND gam.schm_code = e.ref_code
AND e.ref_rec_type = 'CBGSC'
AND e.del_flg = 'N'
AND e.schm_ref_type = 'CJ'
AND gam.acid = lam.acid
AND ( lam.payoff_flg != 'Y'
OR lam.payoff_flg IS NULL )
AND ( lam.chrge_off_flg != 'Y'
OR lam.chrge_off_flg IS NULL )
AND gam.sol_id = '367'
AND gam.acct_cls_flg = 'N'
AND gam.bank_id = 'SG'
AND ( a.prdem > 0
OR b.indem > 0
);
Possible Solution:
(Unable to Test.)
SELECT *
FROM (
SELECT ( SELECT NVL(SELECT SUM(dmd_amt - tot_adj_amt), 0)
FROM tbaadm.ldt
WHERE acid = gam.acid
AND dmd_flow_id = 'PRDEM'
AND del_flg != 'Y'
AND bank_id = 'SG'
) prdem
,'PRDEM'
, ( SELECT NVL(SELECT SUM(dmd_amt - tot_adj_amt), 0)
FROM tbaadm.ldt
WHERE acid = gam.acid
AND dmd_flow_id = 'INDEM'
AND del_flg != 'Y'
AND bank_id = 'SG'
) indem
,'INDEM'
,gam.foracid
,gam.acid
,gam.acct_crncy_code
FROM tbaadm.view_gam gam
JOIN dbscust.c_mpamt d
ON gam.foracid = d.foracid
AND d.mode_of_paymnt = 'ACT'
AND d.accnt_type IS NOT NULL
AND ( ( d.lsa_comm_date IS NULL )
OR ( d.lsa_comm_date <= SYSDATE )
)
JOIN tbaadm.lrct e
ON gam.schm_code = e.ref_code
AND e.ref_rec_type = 'CBGSC'
AND e.del_flg = 'N'
AND e.schm_ref_type = 'CJ'
JOIN tbaadm.lam lam
ON gam.acid = lam.acid
AND ( lam.payoff_flg != 'Y'
OR lam.payoff_flg IS NULL )
AND ( lam.chrge_off_flg != 'Y'
OR lam.chrge_off_flg IS NULL )
WHERE gam.sol_id = '367'
AND gam.acct_cls_flg = 'N'
AND gam.bank_id = 'SG'
)
WHERE ( a.prdem > 0
OR b.indem > 0
);
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:43:37 CDT 2024
|