|
Сравнение планов
авторSQL> delete from plan_table;
0 rows deleted.
SQL> explain plan for
2 SELECT d.doc_id, d.doc_number, d.doc_date, d.class, d.category,
3 d.customer_id, d.customer_site_use_id, ec.customer_number, ec.customer_alt_name,
4 DECODE(nvl('1', '0'), '0', null,
5 '1', ec.name_pred,
6 '2', to_char(d.period_beg, 'yyyymmdd'),
7 '3', d.category,
8 '4', /*aa.attribute2||' '||*/ec.customer_alt_name,
9 '5', ec.customer_number||' '||ec.customer_alt_name) group1,
10 DECODE(nvl('2', '0'), '0', null,
11 '1', ec.name_pred,
12 '2', to_char(d.period_beg, 'yyyymmdd'),
13 '3', d.category,
14 '4', /*aa.attribute2||' '||*/ec.customer_alt_name,
15 '5', ec.customer_number||' '||ec.customer_alt_name) group2,
16 DECODE(nvl('3', '0'), '0', null,
17 '1', ec.name_pred,
18 '2', to_char(d.period_beg, 'yyyymmdd'),
19 '3', d.category,
20 '4', /*aa.attribute2||' '||*/ec.customer_alt_name,
21 '5', ec.customer_number||' '||ec.customer_alt_name) group3,
22 d.period_beg, d.period_end,
23 d.due_doc, d.in_dolg, d.amount_due, d.amount_opl, d.out_dolg
24 FROM esb_ar_debt2_v d,
25 ra_esb_customers ec/*,
26 ra_addresses_all aa*/
27 WHERE d.customer_id = ec.customer_id
28 -- AND ec.customer_id = aa.customer_id
29 /*AND d.status_trx = 'OP'*/
30 AND ec.vid_en = '1'
31 AND ec.kod_pred = DECODE(5, -1, ec.kod_pred, 5)
32 AND ec.otd = NVL(7, ec.otd)
33 AND ec.y4 = NVL(75, ec.y4)
34 AND ec.dog = NVL(null, ec.dog)
35 -- AND aa.attribute2 = NVL(to_char(P_KOD_PLAT), aa.attribute2)
36 -- AND ((P_GROUP1<>'4' AND P_GROUP2<>'4' AND P_GROUP3<>'4') OR aa.attribute3 = '1');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 440 | 675 |
|* 1 | FILTER | | | | |
| 2 | SORT GROUP BY | | 1 | 440 | 675 |
|* 3 | HASH JOIN OUTER | | 1 | 440 | 650 |
| 4 | NESTED LOOPS OUTER | | 1 | 431 | 645 |
| 5 | NESTED LOOPS | | 1 | 334 | 644 |
| 6 | NESTED LOOPS OUTER | | 1 | 305 | 643 |
| 7 | NESTED LOOPS | | 1 | 293 | 643 |
| 8 | NESTED LOOPS | | 1 | 273 | 642 |
| 9 | NESTED LOOPS | | 1 | 254 | 640 |
|* 10 | TABLE ACCESS FULL | HZ_CUST_ACCOUNTS | 1 | 64 | 624 |
|* 11 | VIEW | ESB_AR_SALDO6 | 1 | 190 | 640 |
| 12 | UNION-ALL PARTITION | | | | |
|* 13 | FILTER | | | | |
| 14 | NESTED LOOPS | | 1 | 108 | 9 |
| 15 | NESTED LOOPS OUTER | | 1 | 91 | 8 |
| 16 | NESTED LOOPS | | 1 | 86 | 8 |
|* 17 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 18 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 19 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 28 | 2 |
|* 20 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 |
|* 21 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 22 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 23 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
| 24 | NESTED LOOPS OUTER | | 1 | 147 | 23 |
| 25 | NESTED LOOPS | | 1 | 141 | 22 |
| 26 | NESTED LOOPS OUTER | | 1 | 108 | 8 |
|* 27 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 28 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 29 | VIEW PUSHED PREDICATE | ESB_RA_CUSTOMER_TRX_ALL | 1 | 50 | 2 |
| 30 | NESTED LOOPS | | 1 | 50 | 4 |
| 31 | NESTED LOOPS OUTER | | 1 | 33 | 3 |
| 32 | TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_ALL | 1 | 28 | 3 |
|* 33 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 2 |
|* 34 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 35 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 36 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | AR_RECEIVABLE_APPLICATIONS_ALL | 1 | 33 | 14 |
|* 38 | INDEX RANGE SCAN | AR_RECEIVABLE_APPLICATIONS_N8 | 29 | | 3 |
| 39 | VIEW PUSHED PREDICATE | ESB_RA_CUSTOMER_TRX_ALL | 1 | 6 | 1 |
| 40 | NESTED LOOPS | | 1 | 36 | 3 |
| 41 | NESTED LOOPS OUTER | | 1 | 29 | 3 |
| 42 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 24 | 3 |
|* 43 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 2 |
|* 44 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
|* 45 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | 7 | |
|* 46 | TABLE ACCESS BY INDEX ROWID | AR_ADJUSTMENTS_ALL | 1 | 21 | 2 |
| 47 | NESTED LOOPS | | 1 | 129 | 11 |
| 48 | NESTED LOOPS | | 1 | 108 | 9 |
| 49 | NESTED LOOPS OUTER | | 1 | 91 | 8 |
| 50 | NESTED LOOPS | | 1 | 86 | 8 |
|* 51 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 52 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 53 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 28 | 2 |
|* 54 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 |
|* 55 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 56 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 57 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
|* 58 | INDEX RANGE SCAN | AR_ADJUSTMENTS_N3 | 1 | | 1 |
|* 59 | TABLE ACCESS BY INDEX ROWID | AR_RECEIVABLE_APPLICATIONS_ALL | 1 | 28 | 8 |
| 60 | NESTED LOOPS | | 1 | 136 | 17 |
| 61 | NESTED LOOPS | | 1 | 108 | 9 |
| 62 | NESTED LOOPS OUTER | | 1 | 91 | 8 |
| 63 | NESTED LOOPS | | 1 | 86 | 8 |
|* 64 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 65 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 66 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 28 | 2 |
|* 67 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 |
|* 68 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 69 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 70 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
|* 71 | INDEX RANGE SCAN | AR_RECEIVABLE_APPLICATIONS_N3 | 14 | | 3 |
|* 72 | FILTER | | | | |
| 73 | NESTED LOOPS | | 1 | 92 | 9 |
| 74 | NESTED LOOPS | | 1 | 88 | 9 |
| 75 | NESTED LOOPS | | 1 | 79 | 8 |
|* 76 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 61 | 6 |
|* 77 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 78 | TABLE ACCESS BY INDEX ROWID | AR_CASH_RECEIPTS_ALL | 1 | 18 | 2 |
|* 79 | INDEX UNIQUE SCAN | AR_CASH_RECEIPTS_U1 | 1 | | 1 |
| 80 | TABLE ACCESS BY INDEX ROWID | AR_RECEIPT_METHODS | 1 | 9 | 1 |
|* 81 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | | |
|* 82 | INDEX UNIQUE SCAN | AR_RECEIPT_CLASSES_U1 | 1 | 4 | |
|* 83 | TABLE ACCESS BY INDEX ROWID | AR_RECEIVABLE_APPLICATIONS_ALL | 1 | 31 | 7 |
| 84 | NESTED LOOPS | | 1 | 113 | 16 |
| 85 | NESTED LOOPS | | 1 | 82 | 9 |
| 86 | NESTED LOOPS | | 1 | 78 | 9 |
| 87 | NESTED LOOPS | | 1 | 69 | 8 |
|* 88 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 51 | 6 |
|* 89 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 90 | TABLE ACCESS BY INDEX ROWID | AR_CASH_RECEIPTS_ALL | 1 | 18 | 2 |
|* 91 | INDEX UNIQUE SCAN | AR_CASH_RECEIPTS_U1 | 1 | | 1 |
| 92 | TABLE ACCESS BY INDEX ROWID | AR_RECEIPT_METHODS | 1 | 9 | 1 |
|* 93 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | | |
|* 94 | INDEX UNIQUE SCAN | AR_RECEIPT_CLASSES_U1 | 1 | 4 | |
| 95 | INLIST ITERATOR | | | | |
|* 96 | INDEX RANGE SCAN | AR_RECEIVABLE_APPLICATIONS_N1 | 6 | | 3 |
| 97 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 2 | 38 | 2 |
|* 98 | INDEX RANGE SCAN | HZ_CUST_ACCT_SITES_N3 | 2 | | 1 |
|* 99 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 1 | 20 | 1 |
|*100 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 1 | | |
|*101 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 1 | 12 | |
| 102 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 29 | 1 |
|*103 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | |
| 104 | TABLE ACCESS BY INDEX ROWID | YY_TS_OTDCONS | 107 | 10379 | 1 |
|*105 | INDEX UNIQUE SCAN | YY_IU_OTDCONS | 1 | | |
| 106 | TABLE ACCESS FULL | YY_TS_BUDGETS | 1062 | 9558 | 4 |
----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((SUM(CASE WHEN ("S"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND
"S"."TRX_DATE"<"APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND ("S"."APPLY_DATE" IS NULL OR
"S"."APPLY_DATE"<"APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"()) AND NVL("S"."CUSTOMER_TRX_ID",0)<>(-1)) THEN
"S"."AMOUNT" ELSE 0 END )<>0 OR SUM(CASE WHEN ("S"."TRX_DATE">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND
"S"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"() AND "S"."CLASS"='INV' AND "S"."APPLY_DATE" IS NULL)
THEN "S"."AMOUNT" ELSE 0 END )<>0 OR SUM(CASE WHEN ((("S"."TRX_DATE">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"()
AND "S"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"() AND "S"."CLASS"='PMT' AND "S"."APPLY_DATE" IS
NULL) OR ("S"."APPLY_DATE" IS NOT NULL AND "S"."APPLY_DATE">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND
"S"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())) AND NVL("S"."CUSTOMER_TRX_ID",0)<>(-1)) THEN
"S"."AMOUNT" ELSE 0 END )<>0))
3 - access("CUST_ACCT"."ATTRIBUTE5"="BUD"."C_BUDGET"(+))
10 - filter(DECODE("CUST_ACCT"."ATTRIBUTE_CATEGORY",'▌ыхъЄЁю¤эхЁуш ',1,'╥хяыю',2,NULL)=1 AND
("CUST_ACCT"."ATTRIBUTE_CATEGORY"='╥хяыю' OR "CUST_ACCT"."ATTRIBUTE_CATEGORY"='▌ыхъЄЁю¤эхЁуш ') AND
"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("CUST_ACCT"."ATTRIBUTE3")=5 AND
"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGMENT"("CUST_ACCT"."ATTRIBUTE1",1,'
.'))=NVL(7,"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGMENT"("CUST_ACCT"."ATTR
IBUTE1",1,'.'))) AND "APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGMENT"("CUST_A
CCT"."ATTRIBUTE1",2,'.'))=NVL(75,"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGM
ENT"("CUST_ACCT"."ATTRIBUTE1",2,'.'))) AND "APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GET
PARTOFSEGMENT"("CUST_ACCT"."ATTRIBUTE1",3,'.'))=NVL(NULL,"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_R
EP_UTILS"."GETPARTOFSEGMENT"("CUST_ACCT"."ATTRIBUTE1",3,'.'))))
11 - filter("S"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")
13 - filter(TO_DATE(NULL) IS NULL OR "APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()>=TO_DATE(NULL))
17 - filter("PS"."CUSTOMER_TRX_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
18 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
20 - access("PS"."CUSTOMER_TRX_ID"="CT"."CUSTOMER_TRX_ID")
21 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("CT"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"CT"."ATTRIBUTE6",NULL)))
23 - access("CT"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
27 - filter("PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
28 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
33 - access("PS"."CUSTOMER_TRX_ID"="CT"."CUSTOMER_TRX_ID")
34 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("CT"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"CT"."ATTRIBUTE6",NULL)))
36 - access("CT"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
37 - filter(("RA"."STATUS"||''='APP' OR "RA"."STATUS"||''='ACC') AND NVL("RA"."CONFIRMED_FLAG",'Y')='Y' AND
("RA"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()))
38 - access("PS"."PAYMENT_SCHEDULE_ID"="RA"."APPLIED_PAYMENT_SCHEDULE_ID")
filter("RA"."APPLIED_PAYMENT_SCHEDULE_ID" IS NOT NULL)
43 - access("RA"."CUSTOMER_TRX_ID"="CT"."CUSTOMER_TRX_ID")
44 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("CT"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"CT"."ATTRIBUTE6",NULL)))
45 - access("CT"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
46 - filter("ADJ"."STATUS"||''='A' AND ("ADJ"."APPLY_DATE" IS NULL OR
"ADJ"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()))
51 - filter("PS"."CUSTOMER_TRX_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
52 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
54 - access("PS"."CUSTOMER_TRX_ID"="CT"."CUSTOMER_TRX_ID")
55 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("CT"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"CT"."ATTRIBUTE6",NULL)))
57 - access("CT"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
58 - access("PS"."PAYMENT_SCHEDULE_ID"="ADJ"."PAYMENT_SCHEDULE_ID")
59 - filter("RA"."STATUS"||''='APP' AND NVL("RA"."CONFIRMED_FLAG",'Y')='Y' AND
("RA"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()))
64 - filter("PS"."CLASS"||''='CM' AND "PS"."CUSTOMER_TRX_ID" IS NOT NULL AND
"PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
65 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
67 - access("PS"."CUSTOMER_TRX_ID"="CT"."CUSTOMER_TRX_ID")
68 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("CT"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"CT"."ATTRIBUTE6",NULL)))
70 - access("CT"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
71 - access("PS"."PAYMENT_SCHEDULE_ID"="RA"."PAYMENT_SCHEDULE_ID")
72 - filter(TO_DATE(NULL) IS NULL OR "APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()>=TO_DATE(NULL))
76 - filter("PS"."CASH_RECEIPT_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
77 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
79 - access("PS"."CASH_RECEIPT_ID"="CR"."CASH_RECEIPT_ID")
81 - access("CR"."RECEIPT_METHOD_ID"="RM"."RECEIPT_METHOD_ID")
82 - access("RM"."RECEIPT_CLASS_ID"="RC"."RECEIPT_CLASS_ID")
83 - filter(NVL("RA"."CONFIRMED_FLAG",'Y')='Y' AND ("RA"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"
()))
88 - filter("PS"."CASH_RECEIPT_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
89 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
91 - access("CR"."CASH_RECEIPT_ID"="PS"."CASH_RECEIPT_ID")
93 - access("CR"."RECEIPT_METHOD_ID"="RM"."RECEIPT_METHOD_ID")
94 - access("RM"."RECEIPT_CLASS_ID"="RC"."RECEIPT_CLASS_ID")
96 - access("RA"."CASH_RECEIPT_ID"="PS"."CASH_RECEIPT_ID" AND ("RA"."STATUS"='ACC' OR "RA"."STATUS"='APP'))
98 - access("ACCT_SITE"."CUST_ACCOUNT_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")
99 - filter("PARTY_SITE"."IDENTIFYING_ADDRESS_FLAG"='Y')
100 - access("PARTY_SITE"."PARTY_SITE_ID"="ACCT_SITE"."PARTY_SITE_ID")
101 - access("PARTY_SITE"."LOCATION_ID"="LOC"."LOCATION_ID"(+))
103 - access("PARTY"."PARTY_ID"="CUST_ACCT"."PARTY_ID")
105 - access("OTD"."NOTD_ID"(+)=TO_NUMBER("CUST_ACCT"."ATTRIBUTE3"))
Note: cpu costing is off
212 rows selected.
SQL> delete from plan_table;
107 rows deleted.
SQL> explain plan for
2 insert into esb_ardynnop_table
3 SELECT d.doc_id, d.doc_number, d.doc_date, d.class,
4 d.customer_id, d.customer_site_use_id, ec.customer_number, ec.customer_alt_name,
5 DECODE(nvl('1', '0'), '0', null,
6 '1', ec.name_pred,
7 '2', to_char(d.period_beg, 'yyyymmdd'),
8 '3', d.category,
9 '4', /*aa.attribute2||' '||*/ec.customer_alt_name,
10 '5', ec.customer_number||' '||ec.customer_alt_name) group1,
11 DECODE(nvl('2', '0'), '0', null,
12 '1', ec.name_pred,
13 '2', to_char(d.period_beg, 'yyyymmdd'),
14 '3', d.category,
15 '4', /*aa.attribute2||' '||*/ec.customer_alt_name,
16 '5', ec.customer_number||' '||ec.customer_alt_name) group2,
17 DECODE(nvl('3', '0'), '0', null,
18 '1', ec.name_pred,
19 '2', to_char(d.period_beg, 'yyyymmdd'),
20 '3', d.category,
21 '4', /*aa.attribute2||' '||*/ec.customer_alt_name,
22 '5', ec.customer_number||' '||ec.customer_alt_name) group3,
23 d.period_beg, d.period_end,
24 d.due_doc, d.in_dolg, d.amount_due, d.amount_opl, d.out_dolg
25 FROM esb_ar_debt2_v d,
26 ra_esb_customers ec/*,
27 ra_addresses_all aa*/
28 WHERE d.customer_id = ec.customer_id
29 -- AND ec.customer_id = aa.customer_id
30 /*AND d.status_trx = 'OP'*/
31 AND ec.vid_en = '1'
32 AND ec.kod_pred = DECODE(5, -1, ec.kod_pred, 5)
33 AND ec.otd = NVL(7, ec.otd)
34 AND ec.y4 = NVL(75, ec.y4)
35 AND ec.dog = NVL(null, ec.dog)
36 -- AND aa.attribute2 = NVL(to_char(P_KOD_PLAT), aa.attribute2)
37 -- AND ((P_GROUP1<>'4' AND P_GROUP2<>'4' AND P_GROUP3<>'4') OR aa.attribute3 = '1');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 440 | 675 |
|* 1 | FILTER | | | | |
| 2 | SORT GROUP BY | | 1 | 440 | 675 |
|* 3 | HASH JOIN OUTER | | 1 | 440 | 650 |
| 4 | NESTED LOOPS OUTER | | 1 | 431 | 645 |
| 5 | NESTED LOOPS | | 1 | 334 | 644 |
| 6 | NESTED LOOPS OUTER | | 1 | 305 | 643 |
| 7 | NESTED LOOPS | | 1 | 293 | 643 |
| 8 | NESTED LOOPS | | 1 | 273 | 642 |
| 9 | NESTED LOOPS | | 1 | 254 | 640 |
|* 10 | TABLE ACCESS FULL | HZ_CUST_ACCOUNTS | 1 | 64 | 624 |
|* 11 | VIEW | ESB_AR_SALDO6 | 1 | 190 | 640 |
| 12 | UNION-ALL PARTITION | | | | |
|* 13 | FILTER | | | | |
| 14 | NESTED LOOPS | | 1 | 108 | 9 |
| 15 | NESTED LOOPS OUTER | | 1 | 91 | 8 |
| 16 | NESTED LOOPS | | 1 | 86 | 8 |
|* 17 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 18 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 19 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 28 | 2 |
|* 20 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 |
|* 21 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 22 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 23 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
| 24 | NESTED LOOPS OUTER | | 1 | 147 | 23 |
| 25 | NESTED LOOPS | | 1 | 141 | 22 |
| 26 | NESTED LOOPS OUTER | | 1 | 108 | 8 |
|* 27 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 28 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 29 | VIEW PUSHED PREDICATE | ESB_RA_CUSTOMER_TRX_ALL | 1 | 50 | 2 |
| 30 | NESTED LOOPS | | 1 | 50 | 4 |
| 31 | NESTED LOOPS OUTER | | 1 | 33 | 3 |
| 32 | TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_ALL | 1 | 28 | 3 |
|* 33 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 2 |
|* 34 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 35 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 36 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | AR_RECEIVABLE_APPLICATIONS_ALL | 1 | 33 | 14 |
|* 38 | INDEX RANGE SCAN | AR_RECEIVABLE_APPLICATIONS_N8 | 29 | | 3 |
| 39 | VIEW PUSHED PREDICATE | ESB_RA_CUSTOMER_TRX_ALL | 1 | 6 | 1 |
| 40 | NESTED LOOPS | | 1 | 36 | 3 |
| 41 | NESTED LOOPS OUTER | | 1 | 29 | 3 |
| 42 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 24 | 3 |
|* 43 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 2 |
|* 44 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
|* 45 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | 7 | |
|* 46 | TABLE ACCESS BY INDEX ROWID | AR_ADJUSTMENTS_ALL | 1 | 21 | 2 |
| 47 | NESTED LOOPS | | 1 | 129 | 11 |
| 48 | NESTED LOOPS | | 1 | 108 | 9 |
| 49 | NESTED LOOPS OUTER | | 1 | 91 | 8 |
| 50 | NESTED LOOPS | | 1 | 86 | 8 |
|* 51 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 52 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 53 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 28 | 2 |
|* 54 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 |
|* 55 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 56 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 57 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
|* 58 | INDEX RANGE SCAN | AR_ADJUSTMENTS_N3 | 1 | | 1 |
|* 59 | TABLE ACCESS BY INDEX ROWID | AR_RECEIVABLE_APPLICATIONS_ALL | 1 | 28 | 8 |
| 60 | NESTED LOOPS | | 1 | 136 | 17 |
| 61 | NESTED LOOPS | | 1 | 108 | 9 |
| 62 | NESTED LOOPS OUTER | | 1 | 91 | 8 |
| 63 | NESTED LOOPS | | 1 | 86 | 8 |
|* 64 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 58 | 6 |
|* 65 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 66 | TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL | 1 | 28 | 2 |
|* 67 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_U1 | 1 | | 1 |
|* 68 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | 5 | |
| 69 | TABLE ACCESS BY INDEX ROWID | RA_CUST_TRX_TYPES_ALL | 1 | 17 | 1 |
|* 70 | INDEX UNIQUE SCAN | RA_CUST_TRX_TYPES_U1 | 1 | | |
|* 71 | INDEX RANGE SCAN | AR_RECEIVABLE_APPLICATIONS_N3 | 14 | | 3 |
|* 72 | FILTER | | | | |
| 73 | NESTED LOOPS | | 1 | 98 | 9 |
| 74 | NESTED LOOPS | | 1 | 94 | 9 |
| 75 | NESTED LOOPS | | 1 | 79 | 8 |
|* 76 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 61 | 6 |
|* 77 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 78 | TABLE ACCESS BY INDEX ROWID | AR_CASH_RECEIPTS_ALL | 1 | 18 | 2 |
|* 79 | INDEX UNIQUE SCAN | AR_CASH_RECEIPTS_U1 | 1 | | 1 |
| 80 | TABLE ACCESS BY INDEX ROWID | AR_RECEIPT_METHODS | 1 | 15 | 1 |
|* 81 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | | |
|* 82 | INDEX UNIQUE SCAN | AR_RECEIPT_CLASSES_U1 | 1 | 4 | |
|* 83 | TABLE ACCESS BY INDEX ROWID | AR_RECEIVABLE_APPLICATIONS_ALL | 1 | 31 | 7 |
| 84 | NESTED LOOPS | | 1 | 119 | 16 |
| 85 | NESTED LOOPS | | 1 | 88 | 9 |
| 86 | NESTED LOOPS | | 1 | 84 | 9 |
| 87 | NESTED LOOPS | | 1 | 69 | 8 |
|* 88 | TABLE ACCESS BY INDEX ROWID | AR_PAYMENT_SCHEDULES_ALL | 1 | 51 | 6 |
|* 89 | INDEX RANGE SCAN | AR_PAYMENT_SCHEDULES_ESB_1 | 3 | | 3 |
| 90 | TABLE ACCESS BY INDEX ROWID | AR_CASH_RECEIPTS_ALL | 1 | 18 | 2 |
|* 91 | INDEX UNIQUE SCAN | AR_CASH_RECEIPTS_U1 | 1 | | 1 |
| 92 | TABLE ACCESS BY INDEX ROWID | AR_RECEIPT_METHODS | 1 | 15 | 1 |
|* 93 | INDEX UNIQUE SCAN | AR_RECEIPT_METHODS_U1 | 1 | | |
|* 94 | INDEX UNIQUE SCAN | AR_RECEIPT_CLASSES_U1 | 1 | 4 | |
| 95 | INLIST ITERATOR | | | | |
|* 96 | INDEX RANGE SCAN | AR_RECEIVABLE_APPLICATIONS_N1 | 6 | | 3 |
| 97 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 2 | 38 | 2 |
|* 98 | INDEX RANGE SCAN | HZ_CUST_ACCT_SITES_N3 | 2 | | 1 |
|* 99 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 1 | 20 | 1 |
|*100 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 1 | | |
|*101 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 1 | 12 | |
| 102 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 29 | 1 |
|*103 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | |
| 104 | TABLE ACCESS BY INDEX ROWID | YY_TS_OTDCONS | 107 | 10379 | 1 |
|*105 | INDEX UNIQUE SCAN | YY_IU_OTDCONS | 1 | | |
| 106 | TABLE ACCESS FULL | YY_TS_BUDGETS | 1062 | 9558 | 4 |
----------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((SUM(CASE WHEN ("S"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND
"S"."TRX_DATE"<"APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND ("S"."APPLY_DATE" IS NULL OR
"S"."APPLY_DATE"<"APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"()) AND NVL("S"."CUSTOMER_TRX_ID",0)<>(-1)) THEN
"S"."AMOUNT" ELSE 0 END )<>0 OR SUM(CASE WHEN ("S"."TRX_DATE">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND
"S"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"() AND "S"."CLASS"='INV' AND "S"."APPLY_DATE" IS NULL)
THEN "S"."AMOUNT" ELSE 0 END )<>0 OR SUM(CASE WHEN ((("S"."TRX_DATE">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"()
AND "S"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"() AND "S"."CLASS"='PMT' AND "S"."APPLY_DATE" IS
NULL) OR ("S"."APPLY_DATE" IS NOT NULL AND "S"."APPLY_DATE">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"() AND
"S"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())) AND NVL("S"."CUSTOMER_TRX_ID",0)<>(-1)) THEN
"S"."AMOUNT" ELSE 0 END )<>0))
3 - access("CUST_ACCT"."ATTRIBUTE5"="BUD"."C_BUDGET"(+))
10 - filter(DECODE("CUST_ACCT"."ATTRIBUTE_CATEGORY",'▌ыхъЄЁю¤эхЁуш ',1,'╥хяыю',2,NULL)=1 AND
("CUST_ACCT"."ATTRIBUTE_CATEGORY"='╥хяыю' OR "CUST_ACCT"."ATTRIBUTE_CATEGORY"='▌ыхъЄЁю¤эхЁуш ') AND
"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("CUST_ACCT"."ATTRIBUTE3")=5 AND
"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGMENT"("CUST_ACCT"."ATTRIBUTE1",1,'
.'))=NVL(7,"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGMENT"("CUST_ACCT"."ATTR
IBUTE1",1,'.'))) AND "APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGMENT"("CUST_A
CCT"."ATTRIBUTE1",2,'.'))=NVL(75,"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GETPARTOFSEGM
ENT"("CUST_ACCT"."ATTRIBUTE1",2,'.'))) AND "APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_REP_UTILS"."GET
PARTOFSEGMENT"("CUST_ACCT"."ATTRIBUTE1",3,'.'))=NVL(NULL,"APPS"."FND_NUMBER"."CANONICAL_TO_NUMBER"("APPS"."ESB_R
EP_UTILS"."GETPARTOFSEGMENT"("CUST_ACCT"."ATTRIBUTE1",3,'.'))))
11 - filter("S"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")
13 - filter(TO_DATE(NULL) IS NULL OR "APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()>=TO_DATE(NULL))
17 - filter("PS"."CUSTOMER_TRX_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
18 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
20 - access("PS"."CUSTOMER_TRX_ID"="SYS_ALIAS_7"."CUSTOMER_TRX_ID")
21 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("SYS_ALIAS_7"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"SYS_ALIAS_7"."ATTRIBUTE6",NULL)))
23 - access("SYS_ALIAS_7"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
27 - filter("PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
28 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
33 - access("PS"."CUSTOMER_TRX_ID"="SYS_ALIAS_7"."CUSTOMER_TRX_ID")
34 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("SYS_ALIAS_7"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"SYS_ALIAS_7"."ATTRIBUTE6",NULL)))
36 - access("SYS_ALIAS_7"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
37 - filter(("RA"."STATUS"||''='APP' OR "RA"."STATUS"||''='ACC') AND NVL("RA"."CONFIRMED_FLAG",'Y')='Y' AND
("RA"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()))
38 - access("PS"."PAYMENT_SCHEDULE_ID"="RA"."APPLIED_PAYMENT_SCHEDULE_ID")
filter("RA"."APPLIED_PAYMENT_SCHEDULE_ID" IS NOT NULL)
43 - access("RA"."CUSTOMER_TRX_ID"="SYS_ALIAS_7"."CUSTOMER_TRX_ID")
44 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("SYS_ALIAS_7"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"SYS_ALIAS_7"."ATTRIBUTE6",NULL)))
45 - access("SYS_ALIAS_7"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
46 - filter("ADJ"."STATUS"||''='A' AND ("ADJ"."APPLY_DATE" IS NULL OR
"ADJ"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()))
51 - filter("PS"."CUSTOMER_TRX_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
52 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
54 - access("PS"."CUSTOMER_TRX_ID"="SYS_ALIAS_7"."CUSTOMER_TRX_ID")
55 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("SYS_ALIAS_7"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"SYS_ALIAS_7"."ATTRIBUTE6",NULL)))
57 - access("SYS_ALIAS_7"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
58 - access("PS"."PAYMENT_SCHEDULE_ID"="ADJ"."PAYMENT_SCHEDULE_ID")
59 - filter("RA"."STATUS"||''='APP' AND NVL("RA"."CONFIRMED_FLAG",'Y')='Y' AND
("RA"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()))
64 - filter("PS"."CLASS"||''='CM' AND "PS"."CUSTOMER_TRX_ID" IS NOT NULL AND
"PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
65 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
67 - access("PS"."CUSTOMER_TRX_ID"="SYS_ALIAS_7"."CUSTOMER_TRX_ID")
68 - access("RM"."RECEIPT_METHOD_ID"(+)=TO_NUMBER(DECODE("SYS_ALIAS_7"."ATTRIBUTE_CATEGORY",'╬ЄЁшЎ. юяырЄр
ёры№фю',"SYS_ALIAS_7"."ATTRIBUTE6",NULL)))
70 - access("SYS_ALIAS_7"."CUST_TRX_TYPE_ID"="CTT"."CUST_TRX_TYPE_ID" AND "CTT"."ORG_ID"=0)
71 - access("PS"."PAYMENT_SCHEDULE_ID"="RA"."PAYMENT_SCHEDULE_ID")
72 - filter(TO_DATE(NULL) IS NULL OR "APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"()>=TO_DATE(NULL))
76 - filter("PS"."CASH_RECEIPT_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
77 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
79 - access("PS"."CASH_RECEIPT_ID"="SYS_ALIAS_17"."CASH_RECEIPT_ID")
81 - access("SYS_ALIAS_17"."RECEIPT_METHOD_ID"="SYS_ALIAS_18"."RECEIPT_METHOD_ID")
82 - access("SYS_ALIAS_18"."RECEIPT_CLASS_ID"="RC"."RECEIPT_CLASS_ID")
83 - filter(NVL("RA"."CONFIRMED_FLAG",'Y')='Y' AND ("RA"."APPLY_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"
()))
88 - filter("PS"."CASH_RECEIPT_ID" IS NOT NULL AND "PS"."TRX_DATE"<="APPS"."ESB_REP_DEBT2"."GET_PERIOD_END"())
89 - access("PS"."CUSTOMER_ID"="CUST_ACCT"."CUST_ACCOUNT_ID" AND
"PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
filter("PS"."ACTUAL_DATE_CLOSED">="APPS"."ESB_REP_DEBT2"."GET_PERIOD_BEG"())
91 - access("SYS_ALIAS_17"."CASH_RECEIPT_ID"="PS"."CASH_RECEIPT_ID")
93 - access("SYS_ALIAS_17"."RECEIPT_METHOD_ID"="SYS_ALIAS_18"."RECEIPT_METHOD_ID")
94 - access("SYS_ALIAS_18"."RECEIPT_CLASS_ID"="RC"."RECEIPT_CLASS_ID")
96 - access("RA"."CASH_RECEIPT_ID"="PS"."CASH_RECEIPT_ID" AND ("RA"."STATUS"='ACC' OR "RA"."STATUS"='APP'))
98 - access("ACCT_SITE"."CUST_ACCOUNT_ID"="CUST_ACCT"."CUST_ACCOUNT_ID")
99 - filter("PARTY_SITE"."IDENTIFYING_ADDRESS_FLAG"='Y')
100 - access("PARTY_SITE"."PARTY_SITE_ID"="ACCT_SITE"."PARTY_SITE_ID")
101 - access("PARTY_SITE"."LOCATION_ID"="LOC"."LOCATION_ID"(+))
103 - access("PARTY"."PARTY_ID"="CUST_ACCT"."PARTY_ID")
105 - access("OTD"."NOTD_ID"(+)=TO_NUMBER("CUST_ACCT"."ATTRIBUTE3"))
Note: cpu costing is off
212 rows selected.
SQL>
|
