I have SQL which is taking 16 minutes time even though rows returned for that parameters.
here is the Gather Plan.
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
SQL_ID ff6cznjdbsn5s, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */
DISTINCT voh.paymentmethodtype,
hc.account_number company_number,
hp.party_name customer_name,
acra.receipt_number,
acra.receipt_date,
acra.currency_code payment_currency,
acra.amount payment_amount,
acra.creation_date,
fu.user_name created_by_user,
acra.last_update_date,
fu1.user_name updated_by_user,
arm.name payment_type,
ra.customer_trx_id,
ra.trx_number invoice_number,
ra.trx_date invoice_date,
ra.attribute1 PNREF,
ra.invoice_currency_code invoice_currency,
araa.amount_applied amount_applied_to_invoice,
rat.NAME terms,
ra.org_id,
hou.name operating_unit,
CBA.Bank_Account_Num,
BB.Bank_Name,
BB.Bank_Branch_Name,
CC1.Segment1||'.'||CC1.Segment2||'.'||CC1.Segment3||'.'||CC1.Segment4||'.'||CC1.Segment5||'.'||CC1.Segment6||'.'||CC1.Segment7||'.'||CC1.Segment8 Cash_Account,
(SELECT CC.Segment1||'.'||CC.Segment2||'.'||CC.Segment3||'.'||CC.Segment4||'.'||CC.Segment5||'.'||CC.Segment6||'.'||CC.Segment7||'.'||CC.Segment8
FROM GL.GL_CODE_COMBINATIONS CC
WHERE CC.Code_Combination_Id = MA.RECEIPT_CLEARING_CCID) Receipt_Confirmation_Account,
(SELECT CC.Segment1||'.'||CC.Segment2||'.'||CC.Segment3||'.'||CC.Segment4||'.'||CC.Segment5||'.'||CC.Segment6||'.'||CC.Segment7||'.'||CC.Segment8
FROM GL.GL_CODE_COMBINATIONS CC
WHERE CC.Code_Combination_Id = MA.REMITTANCE_CCID) Remittance_Account,
AB.Batch_Number, AB.GL_Date
FROM apps.AR_CASH_RECEIPTS_ALL ACRA,
apps.AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
apps.RA_CUSTOMER_TRX_ALL RA,
apps.hz_cust_site_uses_all hcsua_bill,
apps.hz_cust_acct_sites_all hcasa_bill,
apps.hz_cust_accounts hc,
apps.hz_parties hp,
apps.hz_party_sites hps_bill,
apps.ra_terms_vl rat,
apps.ar_receipt_methods arm,
xxc_order_header voh,
apps.hr_operating_units hou,
apps.fnd_user fu,
apps.fnd_user fu1,
APPS.CE_BANK_ACCT_USES_ALL REMIT_BANK,
APPS.CE_BANK_ACCOUNTS CBA,
APPS.CE_BANK_BRANCHES_V BB
APPS.GL_CODE_COMBINATIONS CC1,
AR.AR_RECEIPT_METHOD_ACCOUNTS_ALL MA,
(SELECT DISTINCT ACRH.Cash_receipt_id, ABA.Name Batch_Number, ABA.GL_Date
FROM APPS.AR_CASH_RECEIPT_HISTORY_ALL ACRH, APPS.AR_BATCHES_ALL ABA
WHERE ABA.Batch_Id = ACRH.Batch_Id) AB
WHERE 1 = 1
AND ACRA.cash_receipt_id = ARAA.cash_receipt_id
AND ARM.receipt_method_id = ACRA.receipt_method_id
AND MA.Receipt_Method_Id = ARM.Receipt_Method_Id
AND CC1.Code_Combination_Id = MA.CASH_CCID
AND ARAA.applied_customer_trx_id = RA.customer_trx_id (+)
AND ACRA.org_id = HOU.organization_id
AND RA.INTERFACE_HEADER_ATTRIBUTE1 = TO_CHAR (VOH.orderid (+))
AND RA.term_id = RAT.term_id (+)
AND ACRA.customer_site_use_id = HCSUA_BILL.site_use_id (+)
AND HCSUA_BILL.cust_acct_site_id = HCASA_BILL.cust_acct_site_id (+)
AND HCASA_BILL.cust_account_id = HC.cust_account_id (+)
AND HCASA_BILL.status (+) = 'A'
AND HC.party_id = HP.party_id (+)
AND HCSUA_BILL.site_use_code (+) = 'BILL_TO'
AND HCSUA_BILL.status (+) = 'A'
AND HCASA_BILL.party_site_id = HPS_BILL.party_site_id (+)
AND ACRA.last_updated_by = FU1.user_id
AND ACRA.created_by = FU.user_id
AND ACRA.REMIT_BANK_ACCT_USE_ID = REMIT_BANK.BANK_ACCT_USE_ID (+)
AND ACRA.ORG_ID = REMIT_BANK.ORG_ID (+)
AND REMIT_BANK.bank_account_id = CBA.bank_account_id (+)
AND CBA.BANK_BRANCH_ID = BB.bank_branch_id (+)
AND ( ARAA.status = 'APP'
OR ( ARAA.status = 'UNID'
AND ACRA.customer_site_use_id IS NULL)
OR ( ARAA.status = 'ACC'
AND ARAA.Display = 'Y')
)
AND ARAA.cash_receipt_id = AB.cash_receipt_id (+)
AND CC1.Segment1||'.'||CC1.Segment2||'.'||CC1.Segment3||'.'||CC1.Segment4||'.'||CC1.Segment5||'.'||CC1.Segment6||'.'||CC1.Segment7||'.'||CC1.Segment8='1111.11111.0000.000.000.00000.0000'
And CBA.Bank_Account_Num='11111'
ORDER BY ra.org_id, acra.receipt_date, acra.receipt_number;
Plan hash value: 2153481179
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:16:37.97 | 22M| 960K| 9825 | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
|* 2 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
|* 4 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 5 | SORT ORDER BY | | 1 | 252 | 0 |00:16:37.97 | 22M| 960K| 9825 | 1024 | 1024 | | |
| 6 | HASH UNIQUE | | 1 | 252 | 0 |00:16:37.97 | 22M| 960K| 9825 | 809K| 809K| | |
|* 7 | HASH JOIN OUTER | | 1 | 252 | 0 |00:16:37.97 | 22M| 960K| 9825 | 693K| 693K| 204K (0)| |
|* 8 | HASH JOIN RIGHT OUTER | | 1 | 137 | 0 |00:16:37.97 | 22M| 960K| 9825 | 1393K| 1393K| 1579K (0)| |
| 9 | VIEW | RA_TERMS_VL | 1 | 220 | 220 |00:00:00.01 | 33 | 12 | 0 | | | | |
| 10 | NESTED LOOPS OUTER | | 1 | 220 | 220 |00:00:00.01 | 33 | 12 | 0 | | | | |
|* 11 | HASH JOIN | | 1 | 220 | 220 |00:00:00.01 | 30 | 12 | 0 | 1969K| 1969K| 1604K (0)| |
|* 12 | TABLE ACCESS FULL | RA_TERMS_B | 1 | 220 | 220 |00:00:00.01 | 15 | 0 | 0 | | | | |
|* 13 | TABLE ACCESS FULL | RA_TERMS_TL | 1 | 220 | 220 |00:00:00.01 | 15 | 12 | 0 | | | | |
|* 14 | INDEX UNIQUE SCAN | AR_CONS_BILL_CYCLES_TL_U1 | 220 | 1 | 101 |00:00:00.01 | 3 | 0 | 0 | | | | |
|* 15 | HASH JOIN OUTER | | 1 | 137 | 0 |00:16:37.97 | 22M| 960K| 9825 | 693K| 693K| 196K (0)| |
| 16 | NESTED LOOPS OUTER | | 1 | 137 | 0 |00:16:37.97 | 22M| 960K| 9825 | | | | |
| 17 | NESTED LOOPS | | 1 | 137 | 0 |00:16:37.97 | 22M| 960K| 9825 | | | | |
| 18 | NESTED LOOPS | | 1 | 108 | 0 |00:16:37.97 | 22M| 960K| 9825 | | | | |
|* 19 | HASH JOIN | | 1 | 108 | 1799K|00:16:51.41 | 21M| 960K| 9825 | 1321K| 1321K| 1584K (0)| |
| 20 | TABLE ACCESS FULL | AR_RECEIPT_METHOD_ACCOUNTS_ALL | 1 | 641 | 677 |00:00:00.02 | 31 | 28 | 0 | | | | |
|* 21 | HASH JOIN | | 1 | 95 | 1799K|00:16:45.92 | 21M| 960K| 9825 | 1185K| 1185K| 1294K (0)| |
|* 22 | TABLE ACCESS FULL | AR_RECEIPT_METHODS | 1 | 582 | 618 |00:00:00.01 | 31 | 17 | 0 | | | | |
| 23 | NESTED LOOPS OUTER | | 1 | 95 | 1799K|00:16:40.15 | 21M| 960K| 9825 | | | | |
|* 24 | HASH JOIN | | 1 | 95 | 1799K|00:11:38.67 | 15M| 717K| 9825 | 410M| 16M| 446M (0)| 83968 |
|* 25 | HASH JOIN | | 1 | 95 | 1799K|00:11:33.51 | 15M| 707K| 0 | 404M| 16M| 440M (0)| |
| 26 | NESTED LOOPS OUTER | | 1 | 95 | 1799K|00:14:25.19 | 15M| 707K| 0 | | | | |
| 27 | NESTED LOOPS OUTER | | 1 | 95 | 1799K|00:11:43.59 | 10M| 580K| 0 | | | | |
| 28 | NESTED LOOPS OUTER | | 1 | 95 | 1799K|00:07:28.58 | 5354K| 366K| 0 | | | | |
|* 29 | HASH JOIN | | 1 | 95 | 1799K|00:02:19.99 | 95986 | 94582 | 0 | 1185K| 1185K| 1266K (0)| |
|* 30 | TABLE ACCESS FULL | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 255 | 255 |00:00:00.01 | 15 | 0 | 0 | | | | |
|* 31 | HASH JOIN RIGHT SEMI | | 1 | 95 | 1799K|00:02:07.93 | 95971 | 94582 | 0 | 2293K| 2293K| 1583K (0)| |
|* 32 | INDEX SKIP SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 80 | 80 |00:00:00.01 | 5 | 0 | 0 | | | | |
|* 33 | HASH JOIN RIGHT SEMI | | 1 | 306 | 1799K|00:02:02.70 | 95966 | 94582 | 0 | 2293K| 2293K| 1322K (0)| |
|* 34 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 3 | 81 |00:00:00.02 | 14 | 3 | 0 | | | | |
|* 35 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_IX1 | 1 | 8 | 83 |00:00:00.02 | 3 | 3 | 0 | | | | |
|* 36 | HASH JOIN | | 1 | 22940 | 1799K|00:01:57.34 | 95952 | 94579 | 0 | 2293K| 2293K| 1589K (0)| |
| 37 | INDEX FULL SCAN | HR_ORGANIZATION_UNITS_PK | 1 | 255 | 255 |00:00:00.01 | 1 | 0 | 0 | | | | |
| 38 | NESTED LOOPS | | 1 | | 1799K|00:01:47.13 | 95951 | 94579 | 0 | | | | |
| 39 | NESTED LOOPS | | 1 | 22940 | 1799K|00:00:11.88 | 8142 | 8128 | 0 | | | | |
| 40 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.22 | 40 | 26 | 0 | | | | |
| 41 | NESTED LOOPS OUTER | | 1 | 1 | 1 |00:00:00.20 | 38 | 24 | 0 | | | | |
| 42 | TABLE ACCESS BY INDEX ROWID BATCHED | CE_BANK_ACCOUNTS | 1 | 1 | 1 |00:00:00.02 | 3 | 3 | 0 | | | | |
|* 43 | INDEX RANGE SCAN | CE_BANK_ACCOUNTS_N3 | 1 | 1 | 1 |00:00:00.02 | 2 | 2 | 0 | | | | |
| 44 | VIEW PUSHED PREDICATE | CE_BANK_BRANCHES_V | 1 | 1 | 1 |00:00:00.18 | 35 | 21 | 0 | | | | |
| 45 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.18 | 35 | 21 | 0 | | | | |
| 46 | NESTED LOOPS SEMI | | 1 | 1 | 1 |00:00:00.15 | 31 | 18 | 0 | | | | |
| 47 | NESTED LOOPS OUTER | | 1 | 1 | 1 |00:00:00.12 | 27 | 15 | 0 | | | | |
| 48 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.12 | 23 | 14 | 0 | | | | |
| 49 | NESTED LOOPS OUTER | | 1 | 1 | 1 |00:00:00.09 | 19 | 11 | 0 | | | | |
| 50 | NESTED LOOPS OUTER | | 1 | 1 | 1 |00:00:00.09 | 16 | 11 | 0 | | | | |
| 51 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.06 | 13 | 8 | 0 | | | | |
| 52 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.03 | 8 | 4 | 0 | | | | |
|* 53 | D TABLE ACCESS BY INDEX ROWI | HZ_PARTIES | 1 | 1 | 1 |00:00:00.01 | 4 | 0 | 0 | | | | |
|* 54 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | 0 | | | | |
|* 55 | D TABLE ACCESS BY INDEX ROWI | HZ_CODE_ASSIGNMENTS | 1 | 1 | 1 |00:00:00.03 | 4 | 4 | 0 | | | | |
|* 56 | INDEX RANGE SCAN | HZ_CODE_ASSIGNMENTS_U2 | 1 | 1 | 1 |00:00:00.02 | 3 | 3 | 0 | | | | |
| 57 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.03 | 5 | 4 | 0 | 2048 | 2048 | 2048 (0)| |
|* 58 | D TABLE ACCESS BY INDEX ROWI | HZ_RELATIONSHIPS | 1 | 1 | 1 |00:00:00.03 | 5 | 4 | 0 | | | | |
|* 59 | INDEX RANGE SCAN | HZ_RELATIONSHIPS_N5 | 1 | 1 | 1 |00:00:00.03 | 4 | 4 | 0 | | | | |
|* 60 | TABLE ACCESS BY INDEX ROWID | HZ_CONTACT_POINTS | 1 | 1 | 0 |00:00:00.03 | 3 | 3 | 0 | | | | |
|* 61 | INDEX RANGE SCAN | HZ_CONTACT_POINTS_N6 | 1 | 1 | 0 |00:00:00.03 | 3 | 3 | 0 | | | | |
|* 62 | TABLE ACCESS BY INDEX ROWID | HZ_CONTACT_POINTS | 1 | 1 | 0 |00:00:00.01 | 3 | 0 | 0 | | | | |
|* 63 | INDEX RANGE SCAN | HZ_CONTACT_POINTS_N6 | 1 | 1 | 0 |00:00:00.01 | 3 | 0 | 0 | | | | |
|* 64 | TABLE ACCESS BY INDEX ROWID | HZ_ORGANIZATION_PROFILES | 1 | 1 | 1 |00:00:00.02 | 4 | 3 | 0 | | | | |
|* 65 | INDEX RANGE SCAN | HZ_ORGANIZATION_PROFILES_N1 | 1 | 14 | 1 |00:00:00.02 | 3 | 2 | 0 | | | | |
|* 66 | TABLE ACCESS BY INDEX ROWID | HZ_CODE_ASSIGNMENTS | 1 | 1 | 1 |00:00:00.01 | 4 | 1 | 0 | | | | |
|* 67 | INDEX RANGE SCAN | HZ_CODE_ASSIGNMENTS_U2 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | 0 | | | | |
|* 68 | TABLE ACCESS BY INDEX ROWID | HZ_CODE_ASSIGNMENTS | 1 | 1 | 1 |00:00:00.03 | 4 | 3 | 0 | | | | |
|* 69 | INDEX RANGE SCAN | HZ_CODE_ASSIGNMENTS_U2 | 1 | 1 | 1 |00:00:00.02 | 3 | 2 | 0 | | | | |
| 70 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.03 | 4 | 3 | 0 | 2048 | 2048 | 2048 (0)| |
|* 71 | HED TABLE ACCESS BY INDEX ROWID BATC | HZ_ORGANIZATION_PROFILES | 1 | 1 | 1 |00:00:00.03 | 4 | 3 | 0 | | | | |
|* 72 | INDEX RANGE SCAN | HZ_ORGANIZATION_PROFILES_N1 | 1 | 14 | 1 |00:00:00.02 | 3 | 2 | 0 | | | | |
| 73 | TABLE ACCESS BY INDEX ROWID BATCHED | CE_BANK_ACCT_USES_ALL | 1 | 1 | 1 |00:00:00.02 | 2 | 2 | 0 | | | | |
|* 74 | INDEX RANGE SCAN | CE_BANK_ACCT_USES_N1 | 1 | 1 | 1 |00:00:00.01 | 1 | 1 | 0 | | | | |
|* 75 | INDEX RANGE SCAN | AR_CASH_RECEIPTS_CE_N1 | 1 | 60956 | 1799K|00:00:09.07 | 8102 | 8102 | 0 | | | | |
| 76 | TABLE ACCESS BY INDEX ROWID | AR_CASH_RECEIPTS_ALL | 1799K| 18935 | 1799K|00:01:26.42 | 87809 | 86451 | 0 | | | | |
|* 77 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 1799K| 1 | 1758K|00:05:15.04 | 5258K| 272K| 0 | | | | |
|* 78 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 1799K| 1 | 1758K|00:01:14.82 | 3500K| 54794 | 0 | | | | |
|* 79 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 1799K| 1 | 1758K|00:04:10.21 | 5257K| 213K| 0 | | | | |
|* 80 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 1799K| 1 | 1758K|00:01:02.77 | 3499K| 44504 | 0 | | | | |
| 81 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1799K| 1 | 1758K|00:02:38.47 | 5259K| 127K| 0 | | | | |
|* 82 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1799K| 1 | 1758K|00:00:16.39 | 3501K| 5945 | 0 | | | | |
| 83 | TABLE ACCESS FULL | FND_USER | 1 | 7076 | 7078 |00:00:00.01 | 344 | 320 | 0 | | | | |
| 84 | TABLE ACCESS FULL | FND_USER | 1 | 7076 | 7078 |00:00:00.01 | 344 | 184 | 0 | | | | |
| 85 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1799K| 1 | 1758K|00:04:33.74 | 5279K| 242K| 0 | | | | |
|* 86 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1799K| 1 | 1758K|00:01:08.17 | 3511K| 54321 | 0 | | | | |
|* 87 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1799K| 1 | 0 |00:00:14.67 | 1799K| 4 | 0 | | | | |
|* 88 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1799K| 1 | 1799K|00:00:04.72 | 14 | 3 | 0 | | | | |
|* 89 | TABLE ACCESS BY INDEX ROWID BATCHED | AR_RECEIVABLE_APPLICATIONS_ALL | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
|* 90 | INDEX RANGE SCAN | AR_RECEIVABLE_APPLICATIONS_N1 | 0 | 7 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 91 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
|* 92 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 93 | TABLE ACCESS FULL | XXC_ORDER_HEADER | 0 | 9272K| 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 94 | VIEW | | 0 | 15M| 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 95 | HASH UNIQUE | | 0 | 15M| 0 |00:00:00.01 | 0 | 0 | 0 | 1192M| 21M| | |
|* 96 | HASH JOIN | | 0 | 15M| 0 |00:00:00.01 | 0 | 0 | 0 | 17M| 3159K| | |
| 97 | TABLE ACCESS FULL | AR_BATCHES_ALL | 0 | 250K| 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 98 | TABLE ACCESS FULL | AR_CASH_RECEIPT_HISTORY_ALL | 0 | 15M| 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CC"."CODE_COMBINATION_ID"=:B1)
4 - access("CC"."CODE_COMBINATION_ID"=:B1)
7 - access("ARAA"."CASH_RECEIPT_ID"="AB"."CASH_RECEIPT_ID")
8 - access("RA"."TERM_ID"="RAT"."TERM_ID")
11 - access("TERM_ID"="TERM_ID")
12 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE')
13 - filter(("LANGUAGE"=USERENV('LANG') AND NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE'))
14 - access("BILLING_CYCLE_ID"="BILLING_CYCLE_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE')
15 - access("RA"."INTERFACE_HEADER_ATTRIBUTE1"=TO_CHAR("VOH"."ORDERID"))
19 - access("MA"."RECEIPT_METHOD_ID"="RECEIPT_METHOD_ID")
21 - access("RECEIPT_METHOD_ID"="ACRA"."RECEIPT_METHOD_ID")
22 - filter(NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE')
24 - access("ACRA"."LAST_UPDATED_BY"="FU1"."USER_ID")
25 - access("ACRA"."CREATED_BY"="FU"."USER_ID")
29 - access("O"."ORGANIZATION_ID"="ORGANIZATION_ID")
30 - filter(("LANGUAGE"=USERENV('LANG') AND NVL("ZD_EDITION_NAME",'ORA$BASE')='ORA$BASE'))
31 - access("O"."ORGANIZATION_ID"="O3"."ORGANIZATION_ID")
32 - access("O3"."ORG_INFORMATION_CONTEXT"='Operating Unit Information')
filter("O3"."ORG_INFORMATION_CONTEXT"='Operating Unit Information')
33 - access("O"."ORGANIZATION_ID"="O2"."ORGANIZATION_ID")
34 - filter(("O2"."ORG_INFORMATION2"='Y' AND "O2"."ORG_INFORMATION_CONTEXT"||''='CLASS'))
35 - access("O2"."ORG_INFORMATION1"='OPERATING_UNIT')
36 - access("ACRA"."ORG_ID"="O"."ORGANIZATION_ID")
43 - access("CBA"."BANK_ACCOUNT_NUM"='153592619065')
53 - filter(("BRANCHPARTY"."PARTY_TYPE"='ORGANIZATION' AND "BRANCHPARTY"."STATUS"='A'))
54 - access("BRANCHPARTY"."PARTY_ID"="CBA"."BANK_BRANCH_ID")
55 - filter(("BRANCHCA"."STATUS" IS NULL OR "BRANCHCA"."STATUS"='A'))
56 - access("BRANCHCA"."OWNER_TABLE_ID"="CBA"."BANK_BRANCH_ID" AND "BRANCHCA"."OWNER_TABLE_NAME"='HZ_PARTIES' AND "BRANCHCA"."CLASS_CATEGORY"='BANK_INSTITUTION_TYPE')
filter(("BRANCHCA"."CLASS_CATEGORY"='BANK_INSTITUTION_TYPE' AND "BRANCHCA"."OWNER_TABLE_NAME"='HZ_PARTIES' AND INTERNAL_FUNCTION("BRANCHCA"."CLASS_CODE")))
58 - filter(("BRREL"."RELATIONSHIP_CODE"='BRANCH_OF' AND "BRREL"."SUBJECT_TYPE"='ORGANIZATION' AND "BRREL"."OBJECT_TYPE"='ORGANIZATION' AND "BRREL"."OBJECT_TABLE_NAME"='HZ_PARTIES'))
59 - access("BRREL"."SUBJECT_ID"="CBA"."BANK_BRANCH_ID" AND "BRREL"."SUBJECT_TABLE_NAME"='HZ_PARTIES' AND "BRREL"."RELATIONSHIP_TYPE"='BANK_AND_BRANCH' AND "BRREL"."STATUS"='A')
60 - filter("EDICP"."STATUS"='A')
61 - access("EDICP"."OWNER_TABLE_ID"="CBA"."BANK_BRANCH_ID" AND "EDICP"."OWNER_TABLE_NAME"='HZ_PARTIES' AND "EDICP"."CONTACT_POINT_TYPE"='EDI')
filter("EDICP"."OWNER_TABLE_ID"="BRANCHPARTY"."PARTY_ID")
62 - filter("BRANCHCP"."STATUS"='A')
63 - access("BRANCHCP"."OWNER_TABLE_ID"="CBA"."BANK_BRANCH_ID" AND "BRANCHCP"."OWNER_TABLE_NAME"='HZ_PARTIES' AND "BRANCHCP"."CONTACT_POINT_TYPE"='EFT')
filter("BRANCHCP"."OWNER_TABLE_ID"="BRANCHPARTY"."PARTY_ID")
64 - filter((TRUNC(INTERNAL_FUNCTION("BANKORGPROFILE"."EFFECTIVE_START_DATE"))<=SYSDATE@! AND NVL(TRUNC(INTERNAL_FUNCTION("BANKORGPROFILE"."EFFECTIVE_END_DATE")),SYSDATE@!+1)>=SYSDATE@!))
65 - access("BANKORGPROFILE"."PARTY_ID"="BRREL"."OBJECT_ID")
66 - filter(("BRANCHTYPECA"."PRIMARY_FLAG"='Y' AND "BRANCHTYPECA"."STATUS"='A'))
67 - access("BRANCHTYPECA"."OWNER_TABLE_ID"="CBA"."BANK_BRANCH_ID" AND "BRANCHTYPECA"."OWNER_TABLE_NAME"='HZ_PARTIES' AND "BRANCHTYPECA"."CLASS_CATEGORY"='BANK_BRANCH_TYPE')
filter(("BRANCHTYPECA"."CLASS_CATEGORY"='BANK_BRANCH_TYPE' AND "BRANCHTYPECA"."OWNER_TABLE_NAME"='HZ_PARTIES' AND "BRANCHTYPECA"."OWNER_TABLE_ID"="BRANCHPARTY"."PARTY_ID"))
68 - filter(("BANKCA"."STATUS" IS NULL OR "BANKCA"."STATUS"='A'))
69 - access("BANKCA"."OWNER_TABLE_ID"="BANKORGPROFILE"."PARTY_ID" AND "BANKCA"."OWNER_TABLE_NAME"='HZ_PARTIES' AND "BANKCA"."CLASS_CATEGORY"='BANK_INSTITUTION_TYPE')
filter(("BANKCA"."CLASS_CATEGORY"='BANK_INSTITUTION_TYPE' AND "BANKCA"."OWNER_TABLE_NAME"='HZ_PARTIES' AND INTERNAL_FUNCTION("BANKCA"."CLASS_CODE")))
71 - filter((TRUNC(INTERNAL_FUNCTION("BRANCHORGPROFILE"."EFFECTIVE_START_DATE"))<=SYSDATE@! AND NVL(TRUNC(INTERNAL_FUNCTION("BRANCHORGPROFILE"."EFFECTIVE_END_DATE")),SYSDATE@!+1)>=SYSDATE@!))
72 - access("BRANCHORGPROFILE"."PARTY_ID"="CBA"."BANK_BRANCH_ID")
74 - access("REMIT_BANK"."BANK_ACCOUNT_ID"="CBA"."BANK_ACCOUNT_ID")
75 - access("ACRA"."REMIT_BANK_ACCT_USE_ID"="REMIT_BANK"."BANK_ACCT_USE_ID" AND "ACRA"."ORG_ID"="REMIT_BANK"."ORG_ID")
77 - filter(("HCSUA_BILL"."SITE_USE_CODE"='BILL_TO' AND "HCSUA_BILL"."STATUS"='A'))
78 - access("ACRA"."CUSTOMER_SITE_USE_ID"="HCSUA_BILL"."SITE_USE_ID")
79 - filter("HCASA_BILL"."STATUS"='A')
80 - access("HCSUA_BILL"."CUST_ACCT_SITE_ID"="HCASA_BILL"."CUST_ACCT_SITE_ID")
82 - access("HCASA_BILL"."CUST_ACCOUNT_ID"="HC"."CUST_ACCOUNT_ID")
86 - access("HC"."PARTY_ID"="HP"."PARTY_ID")
87 - filter("CC1"."SEGMENT1"||'.'||"CC1"."SEGMENT2"||'.'||"CC1"."SEGMENT3"||'.'||"CC1"."SEGMENT4"||'.'||"CC1"."SEGMENT5"||'.'||"CC1"."SEGMENT6"||'.'||"CC1"."SEGMENT7"||'.'||"CC1"."SEGMENT8"='1203.10
1017.0000.000.000.00000.0000')
88 - access("CC1"."CODE_COMBINATION_ID"="MA"."CASH_CCID")
89 - filter(("ARAA"."STATUS"='APP' OR ("ARAA"."STATUS"='UNID' AND "ACRA"."CUSTOMER_SITE_USE_ID" IS NULL) OR ("ARAA"."DISPLAY"='Y' AND "ARAA"."STATUS"='ACC')))
90 - access("ACRA"."CASH_RECEIPT_ID"="ARAA"."CASH_RECEIPT_ID")
92 - access("ARAA"."APPLIED_CUSTOMER_TRX_ID"="RA"."CUSTOMER_TRX_ID")
96 - access("ABA"."BATCH_ID"="ACRH"."BATCH_ID")
Note
-----
- this is an adaptive plan
CE_BANK_BRANCHES_V view is using hz_organization_profiles BANKORGPROFILE table.
Both are date date types.
BANKORGPROFILE.EFFECTIVE_START_DATE
BANKORGPROFILE.EFFECTIVE_END_DATE
Please let me know if need more details.
Thank you in advanced.