WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst num Release Cluster Host
VIS 118468335 VIS 1 10.1.0.4.0 NO oasb-db.oasb.hp

  Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 834 12-Mar-06 14:56:18 1353 174.3
End Snap: 835 12-Mar-06 15:56:57 1585 196.0
Elapsed:   60.66 (mins)    
DB Time:   262.11 (mins)    

 

Report Summary

Cache Sizes (end)

Buffer Cache: 1,376M Std Block Size: 8K
Shared Pool Size: 1,856M Log Buffer: 10,240K

Load Profile

  Per Second Per Transaction
Redo size: 2,156,789.07 30,472.07
Logical reads: 93,418.21 1,319.85
Block changes: 13,354.01 188.67
Physical reads: 124.66 1.76
Physical writes: 223.06 3.15
User calls: 2,863.40 40.46
Parses: 683.73 9.66
Hard parses: 1.81 0.03
Sorts: 822.18 11.62
Logons: 0.61 0.01
Executes: 6,048.07 85.45
Transactions: 70.78  

% Blocks changed per Read: 14.29 Recursive Call %: 77.92
Rollback per transaction %: 21.28 Rows per Sort: 13.11

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.87 In-memory Sort %: 100.00
Library Hit %: 99.97 Soft Parse %: 99.73
Execute to Parse %: 88.70 Latch Hit %: 99.77
Parse CPU to Parse Elapsd %: 90.02 % Non-Parse CPU: 96.81

Shared Pool Statistics

  Begin End
Memory Usage %: 46.48 63.49
% SQL with executions>1: 86.57 84.63
% Memory for SQL w/exec>1: 83.43 69.03

Top 5 Timed Events

Event Waits Time(s) Percent Total DB Time Wait Class
CPU time   12,162 77.33  
db file sequential read 387,345 1,951 12.41 User I/O
log file sync 96,519 1,458 9.27 Commit
log file parallel write 397,365 469 2.99 System I/O
enq: TX - row lock contention 102 74 .47 Application

Main Report


Back to Top

Wait Events Statistics

Back to Top

Wait Events

Event Waits Timeouts Total Wait Time (s) Avg wait (ms) Waits /txn
db file sequential read 387,345 0 1,951 5 1.50
log file sync 96,519 0 1,458 15 0.37
log file parallel write 397,365 0 469 1 1.54
enq: TX - row lock contention 102 11 74 726 0.00
SQL*Net message to client 10,875,056 0 64 0 42.22
SQL*Net more data from client 535,210 0 36 0 2.08
enq: TM - contention 11 5 21 1,866 0.00
db file scattered read 6,885 0 19 3 0.03
SQL*Net more data to client 472,897 0 18 0 1.84
latch: cache buffers chains 7,376 6,940 14 2 0.03
read by other session 3,652 0 14 4 0.01
library cache pin 769 0 12 16 0.00
enq: TX - index contention 2,499 0 12 5 0.01
latch: library cache 6,088 0 10 2 0.02
enq: UL - contention 87 2 9 107 0.00
log buffer space 62 0 8 135 0.00
db file parallel write 55,241 0 8 0 0.21
buffer busy waits 42,285 1 7 0 0.16
direct path read temp 1,682 0 6 4 0.01
SQL*Net break/reset to client 17,348 0 4 0 0.07
library cache load lock 105 0 3 24 0.00
latch free 369 297 2 5 0.00
direct path write temp 1,520 0 2 1 0.01
PX Deq: Signal ACK 98 40 2 16 0.00
local write wait 2,100 0 1 1 0.01
control file sequential read 1,220 0 1 1 0.00
enq: SQ - contention 1,906 0 1 1 0.01
control file parallel write 1,624 0 1 0 0.01
LGWR wait for redo copy 4,499 2 1 0 0.02
enq: DV - contention 35 0 1 17 0.00
latch: library cache pin 481 0 1 1 0.00
enq: RO - fast object reuse 195 0 1 3 0.00
rdbms ipc reply 2,425 0 0 0 0.01
latch: shared pool 584 0 0 1 0.00
process startup 10 0 0 38 0.00
PX Deq Credit: send blkd 456 0 0 1 0.00
log file switch (checkpoint incomplete) 15 0 0 18 0.00
enq: TX - contention 153 0 0 2 0.00
latch: In memory undo latch 61 0 0 4 0.00
latch: redo allocation 56 0 0 3 0.00
direct path read 250 0 0 1 0.00
latch: session allocation 44 0 0 2 0.00
switch logfile command 7 0 0 14 0.00
enq: CU - contention 8 0 0 12 0.00
latch: undo global data 29 0 0 2 0.00
latch: row cache objects 10 0 0 6 0.00
PX qref latch 5 4 0 11 0.00
kksfbc child completion 1 1 0 50 0.00
latch: cache buffers lru chain 26 0 0 2 0.00
latch: object queue header operation 19 0 0 2 0.00
enq: HW - contention 74 0 0 1 0.00
log file sequential read 4 0 0 10 0.00
control file single write 134 0 0 0 0.00
row cache lock 47 0 0 0 0.00
enq: TX - allocate ITL entry 7 0 0 2 0.00
latch: library cache lock 8 0 0 1 0.00
latch: cache buffer handles 6 0 0 2 0.00
enq: FB - contention 41 0 0 0 0.00
direct path write 1,062 0 0 0 0.00
undo segment extension 971 951 0 0 0.00
latch: enqueue hash chains 4 0 0 1 0.00
PX Deq: Table Q qref 16 0 0 0 0.00
latch: redo writing 1 0 0 4 0.00
buffer deadlock 289 288 0 0 0.00
enq: PS - contention 9 0 0 0 0.00
log file single write 4 0 0 0 0.00
SQL*Net message from client 10,875,069 0 2,655,462 244 42.22
pipe get 10,540 6,930 17,745 1,684 0.04
queue messages 1,181 989 10,091 8,545 0.00
Queue Monitor Wait 121 121 3,522 29,107 0.00
wakeup time manager 894 433 3,306 3,698 0.00
PL/SQL lock timer 303 301 2,932 9,676 0.00
PX Idle Wait 1,329 1,200 2,368 1,782 0.01
jobq slave wait 22 21 63 2,886 0.00
PX Deq: Execution Msg 1,925 0 21 11 0.01
class slave wait 1 1 5 4,884 0.00
PX Deq: Execute Reply 1,096 0 1 1 0.00
PX Deq: Msg Fragment 968 0 0 0 0.00
PX Deq: Table Q Normal 481 0 0 1 0.00
PX Deq: Parse Reply 69 0 0 1 0.00
PX Deq Credit: need buffer 201 0 0 0 0.00
PX Deq: Table Q Sample 28 0 0 1 0.00
PX Deq: Join ACK 62 0 0 0 0.00

Back to Wait Events Statistics
Back to Top

Background Wait Events

Event Waits Timeouts Total Wait Time (s) Avg wait (ms) Waits /txn
log file parallel write 397,369 0 469 1 1.54
db file parallel write 55,269 0 8 0 0.21
control file parallel write 1,624 0 1 0 0.01
control file sequential read 704 0 1 1 0.00
LGWR wait for redo copy 4,499 2 1 0 0.02
latch free 75 72 0 5 0.00
latch: cache buffers chains 89 68 0 4 0.00
rdbms ipc reply 2,079 0 0 0 0.01
log buffer space 5 0 0 48 0.00
buffer busy waits 441 0 0 0 0.00
latch: library cache 150 0 0 1 0.00
direct path read 250 0 0 1 0.00
latch: redo allocation 44 0 0 2 0.00
process startup 2 0 0 39 0.00
log file sequential read 4 0 0 10 0.00
control file single write 134 0 0 0 0.00
latch: shared pool 24 0 0 1 0.00
latch: cache buffers lru chain 7 0 0 2 0.00
latch: library cache pin 5 0 0 1 0.00
latch: redo writing 1 0 0 4 0.00
latch: In memory undo latch 3 0 0 1 0.00
latch: session allocation 6 0 0 1 0.00
latch: object queue header operation 4 0 0 1 0.00
latch: enqueue hash chains 1 0 0 2 0.00
direct path write 150 0 0 0 0.00
latch: library cache lock 1 0 0 1 0.00
log file single write 4 0 0 0 0.00
log file sync 1 0 0 0 0.00
latch: undo global data 1 0 0 0 0.00
rdbms ipc message 237,886 9,942 27,501 116 0.92
Queue Monitor Wait 121 121 3,522 29,107 0.00
smon timer 7,335 0 3,458 471 0.03
wakeup time manager 894 433 3,306 3,698 0.00

Back to Wait Events Statistics
Back to Top

Time Model Statistics

Statistic Name Time (seconds) % Total DB Time
DB time 15,726.62 100.00
sql execute elapsed time 12,181.27 77.46
DB CPU 12,161.58 77.33
PL/SQL execution elapsed time 3,063.46 19.48
inbound PL/SQL rpc elapsed time 3,040.87 19.34
background elapsed time 1,101.64 7.00
background cpu time 551.09 3.50
parse time elapsed 435.70 2.77
Java execution elapsed time 312.85 1.99
hard parse elapsed time 247.74 1.58
connection management call elapsed time 15.35 0.10
sequence load elapsed time 12.30 0.08
PL/SQL compilation elapsed time 6.96 0.04
failed parse elapsed time 1.97 0.01
hard parse (sharing criteria) elapsed time 1.22 0.01
hard parse (bind mismatch) elapsed time 1.13 0.01
failed parse (out of shared memory) elapsed t 0.00 0.00

Back to Wait Events Statistics
Back to Top

 

Operating System Statistics

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

Service Name DB Time (s) DB CPU (s) Physical Reads Logical Reads
VIS 11,985.80 9,825.20 251,660 283,992,782
SYS$USERS 3,745.50 2,337.70 131,106 31,427,748
SYS$BACKGROUND 0.00 0.00 68,698 24,511,604

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Service Name User I/O Total Wts User I/O Wt Time Concurcy Total Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total Wts Network Wt Time
VIS 412,387 112,163 56,277 5,388 0 0 7,520,130 9,109
SYS$USERS 131,625 77,438 2,722 448 7 10 4,359,560 2,642
SYS$BACKGROUND 44,889 9,820 717 84 0 0 0 0

Back to Wait Events Statistics
Back to Top

 

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
864 864 2 431.95 5.49 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
689 671 4 172.34 4.38 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
429 412 59 7.27 2.73 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
393 388 4 98.35 2.50 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
335 16 481 0.70 2.13 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
317 310 22,244 0.01 2.01 6h2tpcxpymqu7 JDBC Thin Client BEGIN :1 := FND_AOLJ_UTIL.is_V...
291 259 4 72.78 1.85 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
273 141 5,000 0.05 1.74 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
238 67 481 0.49 1.51 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
224 214 288 0.78 1.42 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
221 214 1,000 0.22 1.41 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
189 182 15,606 0.01 1.20 3prk3vg9u0abb JDBC Thin Client BEGIN FND_GLOBAL.APPS_INITIA...
160 128 4,338 0.04 1.02 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...
158 8 816 0.19 1.01 72cqcsuq5wgh0 asn.dashboard.server.DashboardAM SELECT aslrt.meaning as rank_n...
158 155 11,435 0.01 1.00 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
864 864 2 431.95 5.49 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
671 689 4 167.71 4.38 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
412 429 59 6.98 2.73 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
388 393 4 96.91 2.50 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
310 317 22,244 0.01 2.01 6h2tpcxpymqu7 JDBC Thin Client BEGIN :1 := FND_AOLJ_UTIL.is_V...
259 291 4 64.64 1.85 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
214 224 288 0.74 1.42 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
214 221 1,000 0.21 1.41 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
182 189 15,606 0.01 1.20 3prk3vg9u0abb JDBC Thin Client BEGIN FND_GLOBAL.APPS_INITIA...
155 158 11,435 0.01 1.00 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...
141 273 5,000 0.03 1.74 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
128 160 4,338 0.03 1.02 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...
67 238 481 0.14 1.51 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
16 335 481 0.03 2.13 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
8 158 816 0.01 1.01 72cqcsuq5wgh0 asn.dashboard.server.DashboardAM SELECT aslrt.meaning as rank_n...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
25,706,897 2 12,853,448.50 7.56 863.90 863.90 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
25,609,328 4 6,402,332.00 7.53 670.85 689.36 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
22,523,279 4 5,630,819.75 6.62 387.66 393.41 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
12,614,062 1 12,614,062.00 3.71 56.91 134.07 05s9358mm6vrr   begin dbms_feature_usage_inter...
11,857,280 5,000 2,371.46 3.49 141.36 273.21 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
11,343,243 4 2,835,810.75 3.34 258.58 291.11 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
11,178,420 1 11,178,420.00 3.29 45.16 106.16 2vpmsug2fa9rf MMON_SLAVE select atc + ix, NULL, NULL ...
9,245,260 1,000 9,245.26 2.72 214.09 221.12 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
8,079,668 59 136,943.53 2.38 411.81 429.10 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
6,336,054 9 704,006.00 1.86 77.22 82.05 1du9v021t8m46 ARGLTP insert into gl_interface(creat...
4,730,681 288 16,425.98 1.39 214.47 223.64 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
4,670,118 11,435 408.41 1.37 155.50 157.81 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...
3,533,861 9 392,651.22 1.04 75.77 76.30 bp8crdjba8zgt ARGLTP update ra_cust_trx_line_gl_dis...
3,464,266 5,000 692.85 1.02 83.62 95.28 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....

Back to SQL Statistics
Back to Top

SQL ordered by Reads

Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
56,174 481 116.79 12.38 16.21 334.72 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
37,688 1 37,688.00 8.31 56.91 134.07 05s9358mm6vrr   begin dbms_feature_usage_inter...
30,443 481 63.29 6.71 66.69 237.97 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
27,623 5,000 5.52 6.09 141.36 273.21 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
26,042 816 31.91 5.74 8.34 158.10 72cqcsuq5wgh0 asn.dashboard.server.DashboardAM SELECT aslrt.meaning as rank_n...
22,894 1 22,894.00 5.05 45.16 106.16 2vpmsug2fa9rf MMON_SLAVE select atc + ix, NULL, NULL ...
14,256 14,578 0.98 3.14 48.65 103.79 3vy5h9xkfv1s5 OEXOETEL INSERT INTO WF_ITEM_ATTRIBUTE_...
8,050 4 2,012.50 1.77 258.58 291.11 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
5,850 49 119.39 1.29 11.86 14.71 3c086a4sm741b ARXAGE select decode ( UPPER ( : p_or...
5,358 4 1,339.50 1.18 670.85 689.36 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
4,688 4,338 1.08 1.03 128.32 160.30 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
868,468 289,863 0.33 0.00 0.00 c4y7jp0q2awbf   SELECT PROFILE_OPTION_VALUE FR...
825,542 730,972 0.89 0.00 0.00 6cjfxb1c4kx16   SELECT PROFILE_OPTION_ID, APP...
543,794 543,792 1.00 0.00 0.00 9dvfguzd15kpv JDBC Thin Client SELECT VALUE FROM V$NLS_PARAME...
230,020 230,020 1.00 0.00 0.00 cgb6kp2umq52a   select t.schema, t.name, t.f...
229,831 229,831 1.00 0.00 0.00 8bsm049u7thjd   select min(next_date) from "AP...
180,162 180,161 1.00 0.00 0.00 cd9mbmqyf4qhp ap.oie.server.WebExpensesAM UPDATE WF_ITEM_ACTIVITY_STATUS...
119,208 119,208 1.00 0.00 0.00 avc1jqzz04wpr JDBC Thin Client SELECT 'x' FROM DUAL
80,696 80,696 1.00 0.00 0.00 38dj2kuc5m9q3 CSCCCCRC SELECT TO_CHAR(NEXT_DAY(:B1 , ...
71,850 55,349 0.77 0.00 0.00 fmfdkztk8vx23   SELECT CATEGORY, SEVERITY, F...
64,636 63,916 0.99 0.00 0.00 91hakq9n36htp ap.oie.server.WebExpensesAM SELECT TEXT_VALUE FROM WF_ITEM...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
230,020 230,020 9.24 cgb6kp2umq52a   select t.schema, t.name, t.f...
229,831 420 9.24 20rc3cmapyvnz   select q_name, state, delay,...
229,831 229,831 9.24 8bsm049u7thjd   select min(next_date) from "AP...
119,208 119,208 4.79 avc1jqzz04wpr JDBC Thin Client SELECT 'x' FROM DUAL
71,850 71,850 2.89 fmfdkztk8vx23   SELECT CATEGORY, SEVERITY, F...
58,379 58,379 2.35 cwyx16zn1hpfb JDBC Thin Client select parameter, value from ...
35,000 35,000 1.41 97mt0g20bcfc3 PYUSLV begin ...
34,790 34,790 1.40 6gnth2mfbs2nq INVTTMTX declare v_ret number := 0...
24,064 24,064 0.97 162ttnv4ubqun OEXOETEL begin OE_DS_PVT.Check_PO_Appr...
23,355 23,355 0.94 1c0b7sas42w2z INVTTMTX SELECT NVL(ITEM_COST, 0), ...

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text
1,665,091 1,440 0.09 8ay7m8qubw98j POXBWVRP select * from PO_TAX_LINES_SUM...
1,477,849 92 0.08 azfadzhfxbxj3 ARXTWMAI SELECT ROW_ID, APBA_INACTIVE_D...
1,443,780 49 0.07 2fr5f5xzmffd8 POXPRPOP SELECT decode ( : P_sortby , ...
1,372,988 1 0.07 9trqpr8qyg7xm POXPRPOP SELECT distinct pol . line_num...
1,220,157 1,441 0.06 bf4kcv5v66czb ibuSRDetails.jsp SELECT 'NOTE', '2', b.creat...
1,209,774 4,178 0.06 8q8zmrwmrccz4 OEXOETEL SELECT /*+ ORDERED USE_NL(adj ...
1,119,101 1,440 0.06 bk9zgysk994av OEXOETEL SELECT /*+ ORDERED USE_NL(qpq ...

Back to SQL Statistics
Back to Top

 

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

SQL Id SQL Text
05s9358mm6vrr begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
0z318y6g3uagc declare l_retstat varchar2 ( 3 ) ; l_msg_data varchar2 ( 244 ) ; l_msg_cnt number ; l_trx_cnt number ; BEGIN :retval := INV_TXN_MANAGER_PUB . process_Transactions ( p_api_version => 1.0 , p_commit => fnd_api . g_true , x_return_status => l_retstat , x_msg_count => l_msg_cnt , x_msg_data => l_msg_data , x_trans_count => l_trx_cnt , p_table => 1 , p_header_id => :header_id ) ; END ;
162ttnv4ubqun begin OE_DS_PVT.Check_PO_Approved( :t_application_id, :t_entity_short_name, :t_validation_entity_short_name, :t_validation_tmplt_short_name, :t_record_set_short_name, :t_scope, :t_result ); end;
1c0b7sas42w2z SELECT NVL(ITEM_COST, 0), COST_UPDATE_ID FROM CST_ITEM_COSTS WHERE INVENTORY_ITEM_ID = :1 AND ORGANIZATION_ID = :2 AND COST_TYPE_ID = :3
1du9v021t8m46 insert into gl_interface(created_by, date_created, status, actual_flag, group_id, set_of_books_id, user_je_source_name, user_je_category_name, accounting_date, subledger_doc_sequence_id, subledger_doc_sequence_value, ussgl_transaction_code, currency_code, code_combination_id, entered_dr, entered_cr, accounted_dr, accounted_cr, reference1, reference10, reference21, reference22, reference23, reference24, reference25, reference26, reference27, reference28, reference29, reference30, request_id)selec t /*+ ORDERED USE_NL (l ctlgd ct ctt cust) INDEX (ctlgd, RA_CUST_TRX_LINE_GL_DIST_N5) +*/ :b0 created_by , trunc(sysdate ) date_created , :b1 status , :b2 actual_flag , :b3 group_id , :b4 sob_id , :b5 source_name , decode(ctt.type, 'CM', :b6, 'DM', :b7, 'CB', :b8, :b9) category , ctlgd.gl_date gl_date , ct.doc_sequence_id doc_seqid , ct.doc_sequence_value doc_num , ctlgd.ussgl_transaction_code ussgl , ct.invoice_currency_code currency , ctlgd.code_combination_id ccid , decod e(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), null , nvl(ctlgd.amount, 0)), 'ROUND', decode(sign(nvl(ctlgd.acctd_amount, 0)), (-1), (-nvl(ctlgd.amount, 0)), null ), decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.amount, 0)), null )) entered_dr , decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.amount, 0)), null ), 'ROUND', decode(sign(nvl(ctlgd.acctd_amount, 0)), (-1), null , nvl(ctlgd.amount, 0)), decode(sign(nvl(ctlgd.amount , 0)), (-1), null , nvl(ctlgd.amount, 0))) entered_cr , decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), null , nvl(ctlgd.acctd_amount, 0)), 'ROUND', decode(sign(nvl(ctlgd.acctd_amount, 0)), (-1), (-nvl(ctlgd.acctd_amount, 0)), null ), decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.acctd_amount, 0)), null )) acctd_dr , decode(ctlgd.account_class, 'REC', decode(sign(nvl(ctlgd.amount, 0)), (-1), (-nvl(ctlgd.acctd_amount, 0)), null ), 'ROUND', decode(sign(nvl(ctlgd. acctd_amount, 0)), (-1), null , nvl(ctlgd.acctd_amount, 0)), decode(sign(nvl(ctlgd.amount, 0)), (-1), null , nvl(ctlgd.acctd_amount, 0))) acctd_cr , (:b10||to_char(:b3)) ref1 , decode(:b12, 'Y', null , ((((((l.meaning||' ')||:b13)||decode(ctt.type, 'CB', :b14, 'CM', :b15, 'DEP', :b16, 'DM', :b17, 'GUAR', :b18, 'INV', :b19, null ))||' ')||ct.trx_number)||:b20)) ref10 , to_char(:b3) ref21 , to_char(ct.customer_trx_id) ref22 , to_char(ctlgd.cust_trx_line_gl_dist_id) ref23 , ct.trx_number ref24 , cu st.account_number ref25 , 'CUSTOMER' ref26 , to_char(ct.bill_to_customer_id) ref27 , decode(ctt.type, 'CM', 'CM', 'DM', 'DM', 'CB', 'CB', 'INV') ref28 , (decode(ctt.type, 'CM', 'CM_', 'DM', 'DM_', 'CB', 'CB_', 'INV_')||ctlgd.account_class) ref29 , 'RA_CUST_TRX_LINE_GL_DIST' ref30 , :b22 from ra_cust_trx_line_gl_dist ctlgd , ra_cust_trx_line_gl_dist rec , ar_lookups l , ra_customer_trx ct , ra_cust_trx_types ctt , hz_cust_accounts cust where ((((((((((((((((ctlgd.cust_trx_line_gl_dist_id+0)<: b23 and ctlgd.gl_date between to_date((:b24||' 00:00:00'), 'DD-MON-RR HH24:MI:SS') and to_date((:b25||' 23:59:59'), 'DD-MON-RR HH24:MI:SS')) and ctlgd.posting_control_id=(-3)) and ctlgd.set_of_books_id=:b4) and ctlgd.account_set_flag='N') and ctlgd.customer_trx_id=ct.customer_trx_id) and l.lookup_type='AUTOGL_TYPE') and l.lookup_code=nvl(ctlgd.account_class, 'REV')) and ct.complete_flag='Y') and ct.bill_to_customer_id=cust.cust_account_id) and ct.cust_trx_type_id=ctt.cust_trx_type_id) and ctlgd. customer_trx_id=rec.customer_trx_id) and rec.account_class='REC') and rec.latest_rec_flag='Y') and rec.account_set_flag='N') and 'NOT_MFAR_TYPE'=AR_MFAR_PKG.ar_mfar_trx_post(ctlgd.customer_trx_id, :b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b12, :b13, :b20, :b14, :b15, :b17, :b16, :b18, :b19, :b24, :b25, :b23))
1q78dwnrrkf6c BEGIN ASN_SALES_PVT.Lead_Process_After_Update( P_Api_Version_Number => 2.0, P_Init_Msg_List => FND_API.G_TRUE, p_Commit => FND_API.G_FALSE, p_Validation_Level => FND_API.G_VALID_LEVEL_NONE, P_Identity_Salesforce_Id => :1, P_Salesgroup_id => :2, P_Sales_Lead_Id => :3, X_Return_Status => :4, X_Msg_Count => :5, X_Msg_Data => :6); END;
20rc3cmapyvnz select q_name, state, delay, expiration, rowid, msgid, dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, priority, exception_qschema, exception_queue, retry_count, corrid, time_manager_info, sender_name, sender_address, sender_protocol from "APPLSYS"."WF_DEFERRED_TABLE_M" where msgid = :1
2fjd6cndjby9p INSERT INTO RA_CUST_TRX_LINE_GL_DIST ( CUSTOMER_TRX_LINE_ID, CUSTOMER_TRX_ID, CODE_COMBINATION_ID, SET_OF_BOOKS_ID, ACCOUNT_CLASS, ACCOUNT_SET_FLAG, PERCENT, AMOUNT, ACCTD_AMOUNT, GL_DATE, CUST_TRX_LINE_SALESREP_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, POSTING_CONTROL_ID, ORIGINAL_GL_DATE, CUST_TRX_LINE_GL_DIST_ID ) SELECT /*+ ORDERED */ ASS.CUSTOMER_TRX_LINE_ID, LINES.CUSTOMER_TRX _ID, DIST.CODE_COMBINATION_ID, :B12 , ASS.ACCOUNT_CLASS, 'N', ROUND( (DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * DECODE(ASS.AMOUNT, 0, DECODE(ASS.ACCOUNT_CLASS, 'REV', DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, 1, -1 ), DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, -1, 1 ) ), ASS.AMOUNT ), FC.PRECISION), ROUND( ((DIST.PERCENT/100) * DECODE(ASS.AMOUNT, 0, DECODE(ASS.ACCOUNT_CLASS, 'REV', DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, 1, -1) , DECODE( LINES.PREVIOUS_CUSTOMER_TRX_ID, NULL, -1, 1) ), ASS.AMOUNT) ) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT) / DECODE(LINES.EXTENDED_AMOUNT, 0, 1, LINES.EXTENDED_AMOUNT)) * DECODE(ASS.AMOUNT, 0, 0, 100), 4), DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * ASS.AMOUNT, FC.PRECISION), ROUND( ((DIST.PERCENT/100) * ASS.AMOUNT) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT), DECODE(:B10 , NULL, ROUND( NVL(HEADER.EXCHANGE_ RATE, 1) * DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * ASS.AMOUNT, FC.PRECISION), ROUND( ((DIST.PERCENT/100) * ASS.AMOUNT) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT ), :B11 ), ROUND( ( NVL(HEADER.EXCHANGE_RATE, 1) * DECODE(FC.MINIMUM_ACCOUNTABLE_UNIT, NULL, ROUND( (DIST.PERCENT/100) * ASS.AMOUNT, FC.PRECISION), ROUND( ( (DIST.PERCENT/100) * ASS.AMOUNT) / FC.MINIMUM_ACCOUNTABLE_UNIT) * FC.MINIMUM_ACCOUNTABLE_UNIT ) ) / :B10 ) * :B10 ), DEC ODE(REC.GL_DATE, NULL, NULL, ARP_AUTO_RULE.ASSIGN_GL_DATE(ASS.GL_DATE) ), DECODE(DIST.CUSTOMER_TRX_ID, HEADER.CUSTOMER_TRX_ID, DIST.CUST_TRX_LINE_SALESREP_ID, CMSREP.CUST_TRX_LINE_SALESREP_ID), :B9 , :B8 , :B7 , SYSDATE, SYSDATE, :B6 , SYSDATE, :B6 , -3, ASS.GL_DATE, RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL FROM RA_CUSTOMER_TRX HEADER, FND_CURRENCIES FC, RA_CUSTOMER_TRX_LINES LINES, RA_RULES ACC_RULE, RA_CUST_TRX_LINE_GL_DIST REC, RA_CUST_TRX_LINE_GL_DIST DIST, RA_CUST_TRX_LINE _SALESREPS CMSREP, AR_REVENUE_ASSIGNMENTS ASS WHERE HEADER.CUSTOMER_TRX_ID = :B1 AND HEADER.COMPLETE_FLAG = 'Y' AND FC.CURRENCY_CODE = HEADER.INVOICE_CURRENCY_CODE AND LINES.CUSTOMER_TRX_ID = HEADER.CUSTOMER_TRX_ID AND LINES.AUTORULE_COMPLETE_FLAG||'' = 'N' AND ASS.CUSTOMER_TRX_LINE_ID = LINES.CUSTOMER_TRX_LINE_ID AND ASS.PERIOD_SET_NAME = :B5 AND ACC_RULE.RULE_ID = LINES.ACCOUNTING_RULE_ID AND DECODE(HEADER.INVOICING_RULE_ID, -3, :B3 , DECODE(NVL(ACC_RULE.DEFERRED_REVENUE_FLAG, 'N'), 'Y', :B2 , DECODE(:B4 , 'N', :B3 , DECODE(HEADER.PREVIOUS_CUSTOMER_TRX_ID, NULL, AR_REVENUE_MANAGEMENT_PVT.LINE_COLLECTIBILITY(:B1 , LINES.CUSTOMER_TRX_LINE_ID), AR_REVENUE_MANAGEMENT_PVT.CASH_BASED(HEADER.PREVIOUS_CUSTOMER_TRX_ID))))) <> :B2 AND REC.CUSTOMER_TRX_ID = HEADER.CUSTOMER_TRX_ID AND REC.ACCOUNT_CLASS = 'REC' AND REC.LATEST_REC_FLAG = 'Y' AND DIST.CUSTOMER_TRX_LINE_ID = (SELECT DECODE(COUNT(CUST_TRX_LINE_GL_DIST_ID), 0, NVL(LINES.PREVIOUS_CUSTOMER_TRX_LINE_ID, LINES.CUST OMER_TRX_LINE_ID), LINES.CUSTOMER_TRX_LINE_ID) FROM RA_CUST_TRX_LINE_GL_DIST SUBDIST2 WHERE SUBDIST2.CUSTOMER_TRX_LINE_ID = LINES.CUSTOMER_TRX_LINE_ID AND SUBDIST2.ACCOUNT_SET_FLAG = 'Y' AND SUBDIST2.GL_DATE IS NULL AND ROWNUM < 2 ) AND DIST.ACCOUNT_CLASS = ASS.ACCOUNT_CLASS AND DIST.ACCOUNT_SET_FLAG = 'Y' AND DIST.CUST_TRX_LINE_SALESREP_ID = CMSREP.PREV_CUST_TRX_LINE_SALESREP_ID (+) AND :B1 = CMSREP.CUSTOMER_TRX_ID (+) AND NOT EXISTS ( SELECT 'distribution exists' FROM RA_CUST_TRX_LINE_GL_D IST SUBDIST WHERE SUBDIST.CUSTOMER_TRX_LINE_ID = ASS.CUSTOMER_TRX_LINE_ID AND SUBDIST.CUSTOMER_TRX_ID + 0 = LINES.CUSTOMER_TRX_ID AND SUBDIST.ACCOUNT_SET_FLAG = 'N' AND SUBDIST.ACCOUNT_CLASS = ASS.ACCOUNT_CLASS AND SUBDIST.ORIGINAL_GL_DATE = ASS.GL_DATE )
2fr5f5xzmffd8 SELECT decode ( : P_sortby , 'PO NUMBER' , null , poh . document_buyer_last_name ) , decode ( : P_sortby , 'PO NUMBER' , null , poh . document_buyer_first_name ) , decode ( : po_num_type , 'NUMERIC' , null , poh . po_num ) , decode ( : po_num_type , 'NUMERIC' , decode ( rtrim ( poh . po_num , '0123456789' ) , NULL , to_number ( poh . po_num ) , - 1 ) , null ) , poh . po_type poh_po_type , por . release_type poh_release_type , poh . po_num || decode ( poh . po_type , 'RELE ASE' , '-' || por . release_num , null ) poh_po_num , poh . po_num security_poh_po_num , por . release_num security_por_po_num , poh . revision_num poh_revision_num , poh . vendor_name poh_vendor_name , poh . vendor_address_line1 poh_vendor_address_line1 , poh . vendor_address_line2 poh_vendor_address_line2 , poh . vendor_address_line3 poh_vendor_address_line3 , decode ( poh . vendor_city , null , poh . vendor_state || ' ' || poh . vendor_postal_code , poh . vendor_city || ', ' || poh . vendor_state || ' ' || poh . vendor_postal_code ) poh_vendor_adr_info , poh . vendor_country poh_vendor_country , poh . customer_num poh_customer , poh . vendor_num poh_vendor_num , poh . creation_date poh_creation_date , poh . revised_date poh_revised_date , substr ( substr ( poh . document_buyer_first_name , 1 , 1 ) || ' ' || poh . document_buyer_last_name , 1 , 12 ) poh_buyer , substr ( trim ( substr ( poh . archive_buyer_first_name , 1 , 1 ) || ' ' || poh . archive_buyer_l ast_name ) , 1 , 12 ) poh_archive_buyer , poh . document_buyer_agent_id poh_agent_id , poh . payment_terms poh_payment_terms , poh . ship_via poh_ship_via , poh . fob poh_fob , poh . freight_terms poh_freight_terms , substr ( poh . vendor_contact_first_name , 1 , 1 ) || ' ' || substr ( poh . vendor_contact_last_name , 1 , 10 ) poh_vendor_contact_name , poh . vendor_phone poh_vendor_phone , poh . vendor_contact_phone poh_vendor_contact_phone , poh . note_to_vendor poh_note_to_vendo r , poh . printed_date poh_printed_date , poh . amount_agreed poh_amount_agreed , poh . cancel_flag poh_cancel_flag , poh . confirming_order_flag poh_confirming_order_flag , poh . acceptance_required_flag poh_acceptance_req_flag , poh . acceptance_due_date poh_acceptance_due_date , poh . currency_code poh_currency_code , poh . currency_code C_CURRENCY , poh . currency_name poh_currency_name , poh . currency_conversion_rate poh_currency_conversion_rate , poh . bill_to_location_id poh_b ill_to_location , poh . ship_to_location_id poh_ship_to_location , poh . po_header_id poh_po_header_id , poh . po_release_id poh_po_release_id , poh . po_type poh_po_type , poh . approved_flag poh_approved_flag , poh . print_count poh_print_count , poh . effective_date poh_effective_date , poh . expiration_date poh_expiration_date , nvl ( poh . po_release_id , - 1 ) poh_join_release_id , poh . vendor_site_id poh_vendor_site_id , poh . vendor_id poh_vendor_id FROM po_headers_print poh , po_releases por WHERE poh . po_release_id = por . po_release_id (+) AND ( nvl ( por . release_num , - 1 ) BETWEEN nvl ( : P_release_num_from , nvl ( por . release_num , - 1 ) ) AND nvl ( : P_release_num_to , nvl ( por . release_num , - 1 ) ) OR poh . po_type != 'RELEASE' ) AND poh . document_buyer_agent_id = nvl ( : P_agent_id , poh . document_buyer_agent_id ) AND ( poh . release_date is NULL OR trunc ( poh . release_date ) BETWEEN nvl ( : P_date_from , trunc ( poh . release_date ) ) AND nvl ( : P_date_to , trunc ( poh . release_date ) + 1 ) ) AND nvl ( poh . approved_flag , 'N' ) = nvl ( : P_approved_flag , nvl ( poh . approved_flag , 'N' ) ) AND ( ( nvl ( : P_print_releases , 'Y' ) = 'Y' ) or ( nvl ( : P_print_releases , 'Y' ) = 'N' and poh . po_type != 'RELEASE' ) ) AND nvl ( poh . consigned_consumption_flag , 'N' ) <> 'Y' AND nvl ( por . consigned_consumption_flag , 'N' ) <> 'Y' AND :P_SINGLE_PO_PRINT != 1 AND :P_PO_ NUM_TYPE = 'NUMERIC' AND decode(rtrim(poh.po_num, '0123456789'), NULL, to_number(poh.po_num), -1) BETWEEN decode(rtrim(nvl(:P_po_num_from, poh.po_num), '0123456789'), NULL, to_number(nvl(:P_po_num_from, poh.po_num)), -1) AND decode(rtrim(nvl(:P_po_num_to, poh.po_num), '0123456789'), NULL, to_number(nvl(:P_po_num_to, poh.po_num)), -1) AND nvl(poh.print_count, 0) = 0 AND NOT EXISTS (SELECT 'X' from ECE_TP_DETAILS ETD, ECE_TP_HEADERS ETH, PO_VENDOR_SITES E_PVS WHERE E_PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND ETH.TP_HEADER_ID = E_PVS.TP_HEADER_ID AND ETD.TP_HEADER_ID = ETH.TP_HEADER_ID AND ETD.EDI_FLAG = 'Y' AND ETD.DOCUMENT_ID = 'POO' AND ETD.DOCUMENT_TYPE = POH.PO_TYPE ) and 1=1 ORDER BY 1 , 2 , 3 , 4
2vpmsug2fa9rf select atc + ix, NULL, NULL from (select count(*) atc from all_tab_columns where data_type_owner = 'MDSYS' and data_type = 'SDO_GEOMETRY' and owner <> 'MDSYS' and not (owner = 'OE' and table_name = 'WAREHOUSES' and column_name = 'WH_GEO_LOCATION') and not (owner = 'OE' and table_name = 'CUSTOMERS' and column_name = 'CUST_GEO_LOCATION')), (select count(*) ix from all_sdo_index_metadata)
38dj2kuc5m9q3 SELECT TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'DY')REACT_DAY_CHAR , TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D') REACT_DAY_NUM , DECODE(SIGN((TO_NUMBER(TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D')) - TO_NUMBER(TO_CHAR(:B1 , 'D')))+1), 1 , TO_NUMBER(TO_CHAR(NEXT_DAY(:B1 , OKS_ENTITLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D')) - TO_NUMBER(TO_CHAR(:B1 , 'D')) , TO_NUMBER(TO_CHAR(NEXT_DAY(:B1 , OKS_ENTI TLEMENTS_PVT.GET_NLS_DAY_OF_WEEK(:B2 )), 'D')) - TO_NUMBER(TO_CHAR(:B1 , 'D')) + 7) REQDAY_RELATIVE FROM DUAL
3c086a4sm741b select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . cust_trx_type_id ) dummy_id , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . name ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , cust_acct . cust_account_id customer_id , cust_acct . account_number customer_number , types . name type , ps . payment_schedule_id payment_sched_id , ps . class class , ps . due_date due_date , decode ( : rp_convert_flag , 'Y' , ps . acctd_amount_due_remaining , ps . amount_due_remaining ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_DATE - ps . due_date ) days_past_due , ps . amount_adjusted amount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , ps . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . excha nge_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp _bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bu cket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b1 , decode ( : rp_bucket_line_type_2 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp _bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_3 , ceil ( : P _AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( par ty . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from ra_cust_trx_types_all types , hz_cust_accounts cust_acct , hz_parties party , AR_PAYMENT_SCHEDULES_ALL ps , RA_CUST_TRX_LINE_GL_DIST_ALL gld , gl_code_combinations cc where TRUNC ( ps.gl_date ) <= : p_as_of_date and ps.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and cust_acct.account_number >= : p_customer_number_low and cust_acct.account_number <= : p_custome r_number_high and ps.cust_trx_type_id = types.cust_trx_type_id and nvl ( ps.org_id , - 99 ) = nvl ( types.org_id , - 99 ) and ps.gl_date_closed > : P_AS_OF_DATE and ps.customer_trx_id + 0 = gld.customer_trx_id and gld.account_class = 'REC' and gld.latest_rec_flag = 'Y' and gld.code_combination_id = cc.code_combination_id AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( TYPES.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( GLD.ORG_I D , : p_reporting_entity_id ) = : p_reporting_entity_id UNION ALL select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , - 999 ) dummy_id , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , decode ( app . applied_payment_schedule_id , - 4 , : c_claim_meaning , : c_payment_meaning ) ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , nvl ( cust_acct . cust_account_id , - 999 ) customer_id , cust_acct . account_number customer_ number , decode ( : p_order_by , 'XXX' , NULL , decode ( app . applied_payment_schedule_id , - 4 , : c_claim_meaning , : c_payment_meaning ) ) type , ps . payment_schedule_id payment_sched_id , ps . class class , ps . due_date due_date , - sum ( decode ( : rp_convert_flag , 'Y' , app . acctd_amount_applied_from , app . amount_applied ) ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_DATE - ps . due_date ) days_past_due , ps . amount_adjusted am ount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , ps . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . exchange_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b1 , decode ( : rp_bucket_line_type_2 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , c eil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISP UTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( party . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from AR_PAYMENT_SCHEDULES_ALL ps , AR_RECEIVABLE_APPLICATIONS_ALL app , gl_code_combinations cc , hz_cust_accounts cust_acct , hz_parti es party where app.gl_date + 0 <= : P_AS_OF_DATE and ps.customer_id = cust_acct.cust_account_id (+) and cust_acct.party_id = party.party_id (+) and nvl ( cust_acct.account_number , : pd_customer_number_low ) >= : p_customer_number_low and nvl ( cust_acct.account_number , : pd_customer_number_low ) <= : p_customer_number_high and ps.cash_receipt_id + 0 = app.cash_receipt_id and app.code_combination_id = cc.code_combination_id and app.status in ( 'ACC' , 'UNAPP' , 'UNID' , 'OTHER A CC' ) and nvl ( app.confirmed_flag , 'Y' ) = 'Y' and ps.gl_date_closed > : P_AS_OF_DATE and ( ( app.reversal_gl_date is not null AND ps.gl_date <= : P_AS_OF_DATE ) OR app.reversal_gl_date is null ) and nvl ( ps.receipt_confirmed_flag , 'Y' ) = 'Y' AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( APP.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id group by party.party_name , cust_acct.account_number , cust_acct.cust_account_id , ps.paym ent_schedule_id , app.applied_payment_schedule_id , ps.due_date , ps.amount_due_original , ps.amount_adjusted , ps.amount_applied , ps.amount_credited , ps.gl_date , ps.amount_in_dispute , ps.amount_adjusted_pending , ps.invoice_currency_code , ps.exchange_rate , ps.class , cc.SEGMENT1 , decode ( app.status , 'UNID' , 'UNID' , 'UNAPP' ) UNION ALL select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , - 999 ) , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , : c_risk_meaning ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , nvl ( cust_acct . cust_account_id , - 999 ) customer_id , cust_acct . account_number customer_number , : c_risk_meaning type , ps . payment_schedule_id payment_sched_id , : c_risk_meaning class , ps . due_date due_date , decode ( : rp_convert_flag , 'Y' , crh . acctd_amount , crh . amount ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_ DATE - ps . due_date ) days_past_due , ps . amount_adjusted amount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , crh . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . exchange_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( gre atest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b1 , decode ( : rp_bucket_line_type _2 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , 0 , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , decode ( greatest ( : rp_bucket_days_from_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ce il ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( party . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from hz_cust_accounts cust_acct , hz_parties party , AR_PAYMENT_SCHEDULES_ALL ps , AR_CASH_RECEIPTS_ALL cr , AR_CASH_RECEIPT_HISTORY_ALL crh , gl_code_combinations cc where crh.gl_date + 0 <= : P_AS_OF_DATE and upper ( : p_risk_option ) != 'NONE' and ps.customer_id = cust_acct.cu st_account_id (+) and cust_acct.party_id = party.party_id (+) and cust_acct.account_number >= : p_customer_number_low and cust_acct.account_number <= : p_customer_number_high and ps.cash_receipt_id = cr.cash_receipt_id and cr.cash_receipt_id = crh.cash_receipt_id and crh.account_code_combination_id = cc.code_combination_id and ( crh.current_record_flag = 'Y' or crh.reversal_gl_date > : p_as_of_date ) and crh.status not in ( decode ( crh.factor_flag , 'Y' , 'RISK_ELIMINATED' , 'N' , 'CLEARED' ) , 'REVERSED' ) and 0 = ( select nvl ( sum ( ra.amount_applied ) , 0 ) from AR_RECEIVABLE_APPLICATIONS_ALL ra where ra.cash_receipt_id = cr.cash_receipt_id and ra.status = 'ACTIVITY' and applied_payment_schedule_id = - 2 and ra.gl_date <= to_date ( : p_as_of_date ) ) AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( CR.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( CRH.ORG_ID , : p_reporting_entity_id ) = : p_reporting_ entity_id UNION ALL select decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . cust_trx_type_id ) dummy_id , decode ( UPPER ( : p_order_by ) , 'CUSTOMER' , NULL , types . name ) dummy_type , rtrim ( rpad ( substrb ( party . party_name , 1 , 50 ) , 36 ) ) customer_name , cust_acct . cust_account_id customer_id , cust_acct . account_number customer_number , types . name type , ps . payment_schedule_id payment_sched_id , ps . class class , ps . due_date due_date , decode ( : rp_convert_flag , 'Y' , ps . acctd_amount_due_remaining , ps . amount_due_remaining ) amt_due_remaining , ps . amount_due_original amt_due_original , ceil ( : P_AS_OF_DATE - ps . due_date ) days_past_due , ps . amount_adjusted amount_adjusted , ps . amount_applied amount_applied , ps . amount_credited amount_credited , ps . gl_date gl_date , decode ( ps . invoice_currency_code , : Functional_Currency , NULL , decode ( ps . exchange_rate , NULL , '*' , NULL ) ) data_converted , nvl ( ps . exchange_rate , 1 ) ps_exchange_rate , decode ( : rp_bucket_line_type_0 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_0 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b0 , decode ( : rp_bucket_line_type_1 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_1 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b1 , decode ( : rp_bucket_line_type_2 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_from_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_2 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b2 , decode ( : rp_bucket_line_type_3 , 'DISPUTE_ONLY' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 0 , 1 ) , 'PENDADJ_ONLY' , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 'DISPUTE_PENDADJ' , decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 0 , 1 ) , 1 ) , decode ( greatest ( : rp_bucket_days_ from_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , least ( : rp_bucket_days_to_3 , ceil ( : P_AS_OF_DATE - ps . due_date ) ) , 1 , 0 ) * decode ( nvl ( ps . amount_in_dispute , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'DISPUTE_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) * decode ( nvl ( ps . amount_adjusted_pending , 0 ) , 0 , 1 , decode ( : rp_bucket_category , 'PENDADJ_ONLY' , 0 , 'DISPUTE_PENDADJ' , 0 , 1 ) ) ) b3 , cc.SEGMENT1 bal_segment_value , rtrim ( rpad ( nvl ( substrb ( party . party_name , 1 , 50 ) , : rp_unid_phrase ) , 36 ) ) customer_name_dsp from ra_cust_trx_types_all types , hz_cust_accounts cust_acct , hz_parties party , AR_PAYMENT_SCHEDULES_ALL ps , ar_transaction_history_all th , AR_DISTRIBUTIONS_ALL dist , gl_code_combinations cc where TRUNC ( ps.gl_date ) <= : p_as_of_date and ps.class = 'BR' and ps.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and cust_acct.account_number >= : p_ customer_number_low and cust_acct.account_number <= : p_customer_number_high and ps.cust_trx_type_id = types.cust_trx_type_id and nvl ( ps.org_id , - 99 ) = nvl ( types.org_id , - 99 ) and ps.gl_date_closed > : P_AS_OF_DATE and ps.customer_trx_id + 0 = th.customer_trx_id and th.transaction_history_id = dist.source_id and dist.source_table = 'TH' and th.transaction_history_id = ( select max ( transaction_history_id ) from ar_transaction_history_all th2 , AR_DISTRIBUTIONS_ALL dist2 where th2.transaction_history_id = dist2.source_id and dist2.source_table = 'TH' and th2.gl_date <= to_date ( : p_as_of_date ) and dist2.amount_dr is not null and th2.customer_trx_id = ps.customer_trx_id ) and dist.amount_dr is not null and dist.source_table_secondary is NULL and dist.code_combination_id = cc.code_combination_id AND NVL ( PS.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id AND NVL ( TYPES.ORG_ID , : p_reporting_entity_id ) = : p_reporting_entity_id ORDER BY 23 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC , 23 , 3 , 5
3dssuqfjsynrk SELECT count(1) FROM (SELECT * FROM ( SELECT LeadEO.sales_lead_id as SalesLeadId , to_char(hz_timezone_pub.convert_datetime(:1, hcp.timezone_id, SYSDATE), 'HH:MI AM ') ||' '||ht.standard_time_short_code as PrimaryContactLocalTime , SUBSTRB(LeadEO.description, 1, 240) as Description , hp.party_name as ContactName , custParty.party_name as CustomerName , aslt.meaning as RankName , ast.meaning as LeadStatus , ROUND(SYSDATE - LeadEO.creation_date) as Age , DECODE(LeadEO.status_code, ' CONVERTED_TO_OPPORTUNITY', 'ASNCnvToOpptyDsbld', 'ASNCnvToOpptyEnbld') as ASNLeadLstCnvToOppty , LeadEO.customer_id , hp.party_id , LeadEO.primary_contact_party_id , hr.relationship_id FROM as_sales_leads LeadEO , ( SELECT /*+ no_merge */ distinct aaa.sales_lead_id, aaa.customer_id FROM as_accesses_all aaa WHERE aaa.sales_lead_id IS NOT NULL AND aaa.lead_id IS NULL AND aaa.salesforce_id = :2) secu , as_statuses_tl ast , as_sales_lead_ranks_tl aslt , hz_parties CustPa rty , hz_parties hp, hz_relationships hr , hz_contact_points hcp , hz_timezones ht , ams_source_codes amsc WHERE LeadEO.sales_lead_id = secu.sales_lead_id AND LeadEO.customer_id = secu.customer_id AND LeadEO.status_code = ast.status_code AND ast.language = USERENV('LANG') AND LeadEO.lead_rank_id = aslt.rank_id (+) AND aslt.language (+) = USERENV('LANG') AND LeadEO.customer_id = CustParty.party_id AND LeadEO.primary_contact_party_id = hr.party_id(+) AND hr.subject_ id = hp.party_id (+) AND hr.subject_table_name (+) = 'HZ_PARTIES' AND hr.object_id(+) = LeadEO.customer_id AND hr.object_table_name (+) = 'HZ_PARTIES' AND LeadEO.primary_contact_party_id = hcp.owner_table_id(+) AND hcp.owner_table_name (+) = 'HZ_PARTIES' AND hcp.primary_flag (+) = 'Y' AND hcp.contact_point_type (+) = 'PHONE' AND hcp.timezone_id = ht.timezone_id (+) AND LeadEO.source_promotion_id = amsc.source_code_id AND amsc.source_code_for_id = :3 AND LeadEO.sta tus_code = :4 ORDER BY age ) )
3prk3vg9u0abb BEGIN FND_GLOBAL.APPS_INITIALIZE( user_id=>:1 , resp_id=>:2 , resp_appl_id=>:3 , security_group_id=>:4 , server_id=>:5);END;
3vy5h9xkfv1s5 INSERT INTO WF_ITEM_ATTRIBUTE_VALUES ( ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE, NUMBER_VALUE, DATE_VALUE ) SELECT :B1 , :B2 , WIA.NAME, WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT FROM WF_ITEM_ATTRIBUTES WIA WHERE WIA.ITEM_TYPE = :B1
4ds53shhfcynn declare begin hr_pre_pay.do_prepayment(:p1, :p2, :p3, :p4, :5, :p6:p6ind, :p7); end;
4yak7x2zqz0dh BEGIN inv_transfer_order_pvt.finalize_pick_confirm(:1, :2, :3, :4, :5, :6, :7); END;
64qc329wgjv39 select /*+ ORDERED INDEX(ASSBAL PAY_ASSIGNMENT_LATEST_BALA_N1) INDEX(DEFBAL PAY_DEFINED_BALANCES_PK) INDEX(ACT PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PACT PAY_PAYROLL_ACTIONS_PK) INDEX(VALUE PAY_BALANCE_CONTEXT_VALUES_PK) USE_NL(ASSBAL DEFBAL ACT PACT VALUE) +*/ ASSBAL.defined_balance_id , DEFBAL.balance_dimension_id , to_char(ASSBAL.value) , ASSBAL.latest_balance_id , ASSBAL.rowid , to_char(ASSBAL.assignment_action_id) , ACT.payroll_action_id , to_char(PACT.effective_date, :b0) , to_number(to_cha r(PACT.effective_date, 'J')) , nvl(VALUE.context_id, 0) , nvl(VALUE.value, 'NULL') , to_char(ASSBAL.expired_assignment_action_id) , to_char(ASSBAL.expired_value) , to_char(ASSBAL.prev_assignment_action_id) , to_char(ASSBAL.prev_balance_value) from pay_assignment_latest_balances ASSBAL , pay_defined_balances DEFBAL , pay_assignment_actions ACT , pay_payroll_actions PACT , pay_balance_context_values VALUE where ((((ASSBAL.assignment_id=:b1 and ACT.assignment_action_id=ASSBAL.assignment_action_id) and PACT.payroll_action_id=ACT.payroll_action_id) and DEFBAL.defined_balance_id=ASSBAL.defined_balance_id) and VALUE.latest_balance_id(+)=ASSBAL.latest_balance_id) union all select /*+ ORDERED INDEX(PERBAL PAY_PERSON_LATEST_BALANCES_N2) INDEX(DEFBAL PAY_DEFINED_BALANCES_PK) INDEX(ACT PAY_ASSIGNMENT_ACTIONS_PK) INDEX(VALUE PAY_BALANCE_CONTEXT_VALUES_PK) INDEX(PACT PAY_PAYROLL_ACTIONS_PK) USE_NL(PERBAL DEFBAL ACT PACT VALUE) +*/ PERBAL.defined_balance_id , DEFBAL.balance_dimension_id , to_char (PERBAL.value) , PERBAL.latest_balance_id , PERBAL.rowid , to_char(PERBAL.assignment_action_id) , ACT.payroll_action_id , to_char(PACT.effective_date, :b0) , to_number(to_char(PACT.effective_date, 'J')) , nvl(VALUE.context_id, 0) , nvl(VALUE.value, 'NULL') , to_char(PERBAL.expired_assignment_action_id) , to_char(PERBAL.expired_value) , to_char(PERBAL.prev_assignment_action_id) , to_char(PERBAL.prev_balance_value) from pay_person_latest_balances PERBAL , pay_defined_balances DEFBAL , pay_assign ment_actions ACT , pay_payroll_actions PACT , pay_balance_context_values VALUE where ((((PERBAL.person_id=:b3 and ACT.assignment_action_id=PERBAL.assignment_action_id) and PACT.payroll_action_id=ACT.payroll_action_id) and DEFBAL.defined_balance_id=PERBAL.defined_balance_id) and VALUE.latest_balance_id(+)=PERBAL.latest_balance_id) order by 6, 2, 1, 4, 10 desc
6cjfxb1c4kx16 SELECT PROFILE_OPTION_ID, APPLICATION_ID, SITE_ENABLED_FLAG , APP_ENABLED_FLAG , RESP_ENABLED_FLAG , USER_ENABLED_FLAG, ORG_ENABLED_FLAG , SERVER_ENABLED_FLAG, HIERARCHY_TYPE FROM FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME = UPPER(:B1 ) AND START_DATE_ACTIVE <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
6gnth2mfbs2nq declare v_ret number := 0; begin if (INV_TABLE_LOCK_PVT.lock_onhand_records(:1, :2, :3, :4, :5, :6, :7) ) then v_ret := 1; end if; :8 := v_ret; end;
6h2tpcxpymqu7 BEGIN :1 := FND_AOLJ_UTIL.is_Valid_ICX(:2, NULL, 'Y', TRUE, TRUE, NULL, NULL, NULL, NULL, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, TRUE);:19 := fnd_session_management.g_enc_key;:20 := fnd_session_management.g_mac_key;END;
72cqcsuq5wgh0 SELECT aslrt.meaning as rank_name , aslrt.rank_id, sum(case when asl.status_open_flag = 'Y' AND round(sysdate-asl.creation_date)<= 2 THEN 1 else 0 END) as day0_2, sum(case when asl.status_open_flag = 'Y' AND round((sysdate - asl.creation_date)) between 3 and 7 THEN 1 else 0 END) as day3_7, sum(case when asl.status_open_flag = 'Y' AND round((sysdate - asl.creation_date)) between 8 and 30 THEN 1 else 0 END) as day8_30, sum(case when asl.status_open_fla g = 'Y' AND round((sysdate - asl.creation_date)) >= 31 THEN 1 else 0 END) as day_31, sum(case when nvl(asl.status_open_flag, 'N') = 'N' AND asl.status_code <> 'CONVERTED_TO_OPPORTUNITY' THEN 1 ELSE 0 END) as closed, sum(case when asl.status_code = 'CONVERTED_TO_OPPORTUNITY' THEN 1 ELSE 0 END) as converted, round((sum(case when asl.status_code = 'CONVERTED_TO_OPPORTUNITY' THEN 1 ELSE 0 END)/count(asl.sales_lead_id)) * 100, 2) as conversion, count(aslrt.ra nk_id) total_leads FROM as_sales_leads asl, as_sales_lead_ranks_tl aslrt, as_sales_lead_ranks_b aslrb, ( SELECT /*+ no_merge */ distinct aaa.sales_lead_id , aaa.customer_id FROM as_accesses_all aaa WHERE aaa.lead_id is null AND aaa.sales_lead_id IS NOT NULL AND aaa.salesforce_id = :1)secu WHERE asl.lead_rank_id = aslrt.rank_id AND aslrb.rank_id = aslrt.rank_id AND aslrt.language = USERENV('LANG') AND secu.sa les_lead_id = asl.sales_lead_id AND secu.customer_id = asl.customer_id GROUP BY aslrt.rank_id, aslrt.meaning, aslrb.min_score ORDER BY aslrb.min_score DESC
7g05xfvc5vp74 BEGIN arp_run.revenue_recognition( :errbuf, :rc, :A0, :A1, :A2); END;
8ay7m8qubw98j select * from PO_TAX_LINES_SUMMARY_V where trx_header_id = :b1 ORDER BY trx_header_id, trx_line_id, trx_shipment_id , trx_distribution_id
8bsm049u7thjd select min(next_date) from "APPLSYS"."AQ$_WF_DEFERRED_TABLE_M_T"
8c30hkqv9n7cr BEGIN OE_BULK_ORDER_IMPORT_PVT.ORDER_IMPORT_CONC_PGM( :errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8); END;
8c8cf4vtv4tx2 SELECT * FROM ( SELECT LeadEO.sales_lead_id as SalesLeadId , to_char(hz_timezone_pub.convert_datetime(:1, hcp.timezone_id, SYSDATE), 'HH:MI AM ') ||' '||ht.standard_time_short_code as PrimaryContactLocalTime , SUBSTRB(LeadEO.description, 1, 240) as Description , hp.party_name as ContactName , custParty.party_name as CustomerName , aslt.meaning as RankName , ast.meaning as LeadStatus , ROUND(SYSDATE - LeadEO.creation_date) as Age , DECODE(LeadEO.status_code, 'CONVERTED_TO_OPPORTUNI TY', 'ASNCnvToOpptyDsbld', 'ASNCnvToOpptyEnbld') as ASNLeadLstCnvToOppty , LeadEO.customer_id , hp.party_id , LeadEO.primary_contact_party_id , hr.relationship_id FROM as_sales_leads LeadEO , ( SELECT /*+ no_merge */ distinct aaa.sales_lead_id, aaa.customer_id FROM as_accesses_all aaa WHERE aaa.sales_lead_id IS NOT NULL AND aaa.lead_id IS NULL AND aaa.salesforce_id = :2) secu , as_statuses_tl ast , as_sales_lead_ranks_tl aslt , hz_parties CustParty , hz_parties hp, hz_relationships hr , hz_contact_points hcp , hz_timezones ht , ams_source_codes amsc WHERE LeadEO.sales_lead_id = secu.sales_lead_id AND LeadEO.customer_id = secu.customer_id AND LeadEO.status_code = ast.status_code AND ast.language = USERENV('LANG') AND LeadEO.lead_rank_id = aslt.rank_id (+) AND aslt.language (+) = USERENV('LANG') AND LeadEO.customer_id = CustParty.party_id AND LeadEO.primary_contact_party_id = hr.party_id(+) AND hr.subject_id = hp.party_id (+) AND hr.subject_table_name (+) = 'HZ_PARTIES' AND hr.object_id(+) = LeadEO.customer_id AND hr.object_table_name (+) = 'HZ_PARTIES' AND LeadEO.primary_contact_party_id = hcp.owner_table_id(+) AND hcp.owner_table_name (+) = 'HZ_PARTIES' AND hcp.primary_flag (+) = 'Y' AND hcp.contact_point_type (+) = 'PHONE' AND hcp.timezone_id = ht.timezone_id (+) AND LeadEO.source_promotion_id = amsc.source_code_id AND amsc.source_code_for_id = :3 AND LeadEO.status_code = :4 ORDER BY age )
8q8zmrwmrccz4 SELECT /*+ ORDERED USE_NL(adj qplh) */ ADJ.LIST_LINE_ID CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , ADJ.PRICE_ADJUSTMENT_ID LINE_DETAIL_INDEX , ADJ.LIST_LINE_TYPE_CODE CREATED_FROM_LIST_LINE_TYPE , ADJ.LIST_HEADER_ID CREATED_FROM_LIST_HEADER_ID , ADJ.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , ADJ.RANGE_BREAK_QUANTITY PRICED_QUANTITY , LINE.P RICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , ADJ.AUTOMATIC_FLAG , ADJ.UPDATE_ALLOWED OVERRIDE_FLAG , ADJ.PRICING_GROUP_SEQUENCE , ADJ.ARITHMETIC_OPERATOR OPERAND_CALCULATION_CODE , NVL(ADJ.OPERAND_PER_PQTY, ADJ.OPERAND) OPERAND_VALUE , ADJ.ADJUSTED_AMOUNT_PER_PQTY ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , ADJ.ACCRUAL_FLAG , NVL(ADJ.UPDATED_FLAG, :B16 ) , 'N' PROCESS_CODE , 'N' PRICING_STATUS_CODE , ' ' PRICING_STATUS_TEXT , ADJ.PRICE_BREAK_TYPE_CODE , ADJ. CHARGE_TYPE_CODE , ADJ.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , ADJ.PRICING_PHASE_ID , '' CREATED_FROM_LIST_TYPE_CODE , '' LIMIT_CODE , '' LIMIT_TEXT , ADJ.LIST_LINE_NO , ADJ.MODIFIER_LEVEL_CODE , ADJ.RANGE_BREAK_QUANTITY GROUP_QUANTITY , ADJ.RANGE_BREAK_QUANTITY GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B15 IS_LDET_REC , LINE.LINE_TYPE_CODE , NULL NET_AMOUNT_FLAG , NULL CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LI NE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , NULL LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE, OE_PRICE_ADJUSTMENTS ADJ, QP_LIST_HEADERS_B QPLH WHERE :B14 = :B7 AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND ADJ.LINE_ID = LINE.LINE_ID AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND LINE.LINE_TYPE_CODE = :B4 AN D LINE.UNIT_PRICE IS NOT NULL AND (ADJ.UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR (:B5 IS NULL AND ADJ.UPDATED_FLAG IS NULL) OR ADJ.PRICING_PHASE_ID NOT IN (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT_EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING _EVENT_CODE||', ') > 0 AND (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B7 OR (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND ADJ.MODIFIER_LEVEL_CODE IN (:B4 , :B3 ) AND QPLH.LIST_HEADER_ID = ADJ.LIST_HEADER_ID AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) AND NOT EXISTS (SELECT 'x' FROM OE_PRICE_ADJ_ASSOCS A, O E_PRICE_ADJUSTMENTS B WHERE A.RLTD_PRICE_ADJ_ID = ADJ.PRICE_ADJUSTMENT_ID AND B.PRICE_ADJUSTMENT_ID = A.PRICE_ADJUSTMENT_ID AND B.LIST_LINE_TYPE_CODE = :B18 ) UNION SELECT /*+ ORDERED USE_NL(adj line qplh) */ ADJ.LIST_LINE_ID CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , ADJ.PRICE_ADJUSTMENT_ID LINE_DETAIL_INDEX , ADJ.LIST_LINE_TYPE_CODE CREATED_FROM_LIST_LINE_TYPE , ADJ.LIST_HEADER_ID CREATED_FROM_LIST_HEADER_ID , ADJ.APPL IED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , ADJ.RANGE_BREAK_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , ADJ.AUTOMATIC_FLAG , ADJ.UPDATE_ALLOWED OVERRIDE_FLAG , ADJ.PRICING_GROUP_SEQUENCE , ADJ.ARITHMETIC_OPERATOR OPERAND_CALCULATION_CODE , NVL(ADJ.OPERAND_PER_PQTY, ADJ.OPERAND) OPERAND_VALUE , ADJ.ADJUSTED_AMOUNT_PER_PQTY ADJUSTMENT_AMOUNT , LINE.UNI T_PRICE , ADJ.ACCRUAL_FLAG , NVL(ADJ.UPDATED_FLAG, :B16 ) , 'N' PROCESS_CODE , 'N' PRICING_STATUS_CODE , ' ' PRICING_STATUS_TEXT , ADJ.PRICE_BREAK_TYPE_CODE , ADJ.CHARGE_TYPE_CODE , ADJ.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , ADJ.PRICING_PHASE_ID , '' CREATED_FROM_LIST_TYPE_CODE , '' LIMIT_CODE , '' LIMIT_TEXT , ADJ.LIST_LINE_NO , ADJ.MODIFIER_LEVEL_CODE , ADJ.RANGE_BREAK_QUANTITY GROUP_QUANTITY , ADJ.RANGE_BREAK_QUANTITY GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICI NG_STATUS_CODE , :B20 IS_LDET_REC , LINE.LINE_TYPE_CODE , NULL NET_AMOUNT_FLAG , NULL CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , NULL LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE1, OE_PRICE_ADJUSTMENTS ADJ , QP_NPREQ_LINES_TMP LINE, QP_LIST_HEADERS_B QPLH WHERE :B14 = :B7 AND LINE1.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE1.LINE_ID = ADJ.HEADER_ID AND LINE1.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.LINE_TYPE_CODE = :B19 AND (LINE1.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE1.PROCESSED_CODE, '0') = :B8 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND (LINE.UNIT_PRICE IS NOT NULL OR LINE.LINE_TYPE_CODE = :B19 ) AND (ADJ .UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR (:B5 IS NULL AND ADJ.UPDATED_FLAG IS NULL) OR ADJ.PRICING_PHASE_ID NOT IN (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT_EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING_EVENT_CODE||', ') > 0 AND (NVL(QP_ PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B7 OR (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(ADJ.LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND ADJ.MODIFIER_LEVEL_CODE = :B19 AND ADJ.LINE_ID IS NULL AND QPLH.LIST_HEADER_ID = ADJ.LIST_HEADER_ID AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) UNION SELECT /*+ ORDERED USE_NL(ldet) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQ UENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LE VEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B23 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE, QP_NP REQ_LDETS_TMP LDET WHERE LINE.LINE_INDEX = LDET.LINE_INDEX AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND LINE.LINE_TYPE_CODE = :B4 AND LINE.UNIT_PRICE IS NOT NULL AND LDET.PRICING_STATUS_CODE IN (:B13 , :B12 ) AND (LDET.APPLIED_FLAG = :B7 OR (LDET.APPLIED_FLAG = :B16 AND LDET.CREATED_FROM_LIST_LINE_TYPE = :B22 AND LDET.AUTOMAT IC_FLAG = :B16 )) AND LDET.CREATED_FROM_LIST_TYPE_CODE NOT IN (:B2 , :B1 ) AND LDET.MODIFIER_LEVEL_CODE IN (:B4 , :B3 ) AND NVL(LDET.LINE_DETAIL_TYPE_CODE, 'NULL') <> :B21 UNION SELECT /*+ ORDERED USE_NL(ldet line) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQUENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CHARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LEVEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B24 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE1, QP_NPREQ_LDETS_TMP LDET , QP_NPREQ_LINES_TMP LINE WHERE LDET.LINE_INDEX = LINE1.LINE_INDEX AND LINE1.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD '||:B10 ) AND LINE1.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.PRICE_FLAG IN (:B7 , :B9 ) AND LINE1.LINE_TYPE_CODE = :B19 AND (LINE.UNIT_PRICE IS NOT NULL OR LINE.LINE_TYPE_CODE = :B19 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LDET.PRICING_STATUS_CODE IN (:B13 , :B12 ) AND (LDET.APPLIED_FLAG = :B7 OR (LDET.APPLIED_FLAG = :B16 AND LDET.CREATED_FROM_LIST_LINE_TYPE = :B22 AND LDET.AUTOMATIC_FLAG = :B16 )) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCES SED_CODE, '0') = :B8 ) AND LDET.MODIFIER_LEVEL_CODE = :B19 AND LDET.CREATED_FROM_LIST_TYPE_CODE NOT IN (:B2 , :B1 ) UNION SELECT /*+ ORDERED USE_NL(ldet qplh) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNI T_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQUENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CH ARGE_SUBTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LEVEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B25 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER _QUANTITY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE, QP_NPREQ_LDETS_TMP LDET, QP_LIST_HEADERS_B QPLH WHERE NVL(:B14 , :B16 ) <> :B7 AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) A ND LINE.LINE_TYPE_CODE = :B4 AND LINE.UNIT_PRICE IS NOT NULL AND LDET.LINE_INDEX = LINE.LINE_INDEX AND LDET.PRICING_STATUS_CODE = :B10 AND (LDET.UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR LDET.PRICING_PHASE_ID NOT IN (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT _EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING_EVENT_CODE||', ') > 0 AND (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B7 OR (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE.LINE_INDEX), LINE.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND LDET.MODIFIER_LEVEL_CODE IN (:B4 , :B3 ) AND QPLH.LIST_HEADER_ID = LDET.CREATED_FROM_LIST_HEADER_I D AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) AND NVL(LDET.LINE_DETAIL_TYPE_CODE, 'NULL') <> :B21 UNION SELECT /*+ ORDERED USE_NL(ldet line qplh) */ LDET.CREATED_FROM_LIST_LINE_ID , LINE.LINE_INDEX LINE_IND , LINE.LINE_INDEX CURR_LINE_INDEX , LINE.LINE_ID LINE_ID , LDET.LINE_DETAIL_INDEX , LDET.CREATED_FROM_LIST_LINE_TYPE , LDET.CREATED_FROM_LIST_HEADER_ID , LDET.APPLIED_FLAG , (LINE.UPDATED_ADJUSTED_UNIT_PRICE - LINE.ADJUSTED_UNIT_PRICE) AMOUNT_CHANGED , LINE.ADJUSTED_UNIT_PRICE , LDET.LINE_QUANTITY PRICED_QUANTITY , LINE.PRICED_QUANTITY LINE_PRICED_QUANTITY , LINE.UPDATED_ADJUSTED_UNIT_PRICE , LDET.AUTOMATIC_FLAG , LDET.OVERRIDE_FLAG , LDET.PRICING_GROUP_SEQUENCE , LDET.OPERAND_CALCULATION_CODE , LDET.OPERAND_VALUE , LDET.ADJUSTMENT_AMOUNT , LINE.UNIT_PRICE , LDET.ACCRUAL_FLAG , NVL(LDET.UPDATED_FLAG, :B16 ) , LDET.PROCESS_CODE , LDET.PRICING_STATUS_CODE , LDET.PRICING_STATUS_TEXT , LDET.PRICE_BREAK_TYPE_CODE , LDET.CHARGE_TYPE_CODE , LDET.CHARGE_SU BTYPE_CODE , LINE.ROUNDING_FACTOR , LDET.PRICING_PHASE_ID , LDET.CREATED_FROM_LIST_TYPE_CODE , LDET.LIMIT_CODE , SUBSTR(LDET.LIMIT_TEXT, 1, 240) , LDET.LIST_LINE_NO , LDET.MODIFIER_LEVEL_CODE , LDET.GROUP_QUANTITY GROUP_QUANTITY , LDET.GROUP_AMOUNT GROUP_AMOUNT , LINE.PRICING_STATUS_CODE LINE_PRICING_STATUS_CODE , :B26 IS_LDET_REC , LINE.LINE_TYPE_CODE , LDET.NET_AMOUNT_FLAG NET_AMOUNT_FLAG , LDET.CALCULATION_CODE CALCULATION_CODE , LINE.CATCHWEIGHT_QTY , LINE.ACTUAL_ORDER_QUANTI TY , LINE.LINE_UNIT_PRICE , LINE.LINE_QUANTITY ORDERED_QTY , LDET.LINE_DETAIL_TYPE_CODE LINE_DETAIL_TYPE_CODE , LINE.LINE_CATEGORY , LINE.PRICE_FLAG FROM QP_NPREQ_LINES_TMP LINE1, QP_NPREQ_LDETS_TMP LDET , QP_NPREQ_LINES_TMP LINE, QP_LIST_HEADERS_B QPLH WHERE NVL(:B14 , :B16 ) <> :B7 AND LINE1.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE.PROCESS_STATUS IN (:B12 , :B13 , :B10 , 'NEW'||:B10 , 'OLD'||:B10 ) AND LINE1.LINE_TYPE_CODE = :B19 AND (LINE1.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE1.PROCESSED_CODE, '0') = :B8 ) AND (LINE.PRICE_FLAG IN (:B7 , :B9 , :B6 ) OR NVL(LINE.PROCESSED_CODE, '0') = :B8 ) AND (LINE.UNIT_PRICE IS NOT NULL OR LINE.LINE_TYPE_CODE = :B19 ) AND LINE.PRICING_STATUS_CODE IN (:B12 , :B11 , :B10 ) AND LINE1.LINE_INDEX = LDET.LINE_INDEX AND LDET.PRICING_STATUS_CODE = :B10 AND (LDET.UPDATED_FLAG = :B7 OR LINE.PRICE_FLAG = :B6 OR LDET.PRICING_PHASE_ID NOT I N (SELECT PH.PRICING_PHASE_ID FROM QP_EVENT_PHASES EV, QP_PRICING_PHASES PH WHERE PH.PRICING_PHASE_ID = EV.PRICING_PHASE_ID AND (EV.END_DATE_ACTIVE IS NULL OR (EV.END_DATE_ACTIVE IS NOT NULL AND EV.END_DATE_ACTIVE > LINE.PRICING_EFFECTIVE_DATE)) AND ((:B17 = :B7 AND PH.FREIGHT_EXISTS = :B7 ) OR (:B17 = :B16 )) AND INSTR(:B5 , EV.PRICING_EVENT_CODE||', ') > 0 AND (NVL(QP_PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B7 OR (NVL(QP_ PREQ_PUB.GET_BUY_LINE_PRICE_FLAG(LDET.CREATED_FROM_LIST_LINE_ID, LINE1.LINE_INDEX), LINE1.PRICE_FLAG) = :B9 AND NVL(PH.USER_FREEZE_OVERRIDE_FLAG, PH.FREEZE_OVERRIDE_FLAG) = :B7 )))) AND LDET.MODIFIER_LEVEL_CODE = :B19 AND QPLH.LIST_HEADER_ID = LDET.CREATED_FROM_LIST_HEADER_ID AND QPLH.LIST_TYPE_CODE NOT IN (:B2 , :B1 ) ORDER BY PRICING_GROUP_SEQUENCE, LINE_IND, IS_LDET_REC
91hakq9n36htp SELECT TEXT_VALUE FROM WF_ITEM_ATTRIBUTE_VALUES WHERE ITEM_TYPE = :B3 AND ITEM_KEY = :B2 AND NAME = :B1
97mt0g20bcfc3 begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end;
9dvfguzd15kpv SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER = :B1
9trqpr8qyg7xm SELECT distinct pol . line_num pol_line_num , pol . po_item_id pol_po_item_id , pol . revision_num pol_item_revision , pol . vendor_product_num pol_vendor_product_num , pol . item_description pol_item_description , round ( pol . quantity_to_print , : P_QTY_PRECISION ) pol_quantity_to_print , nvl ( pol.unit_of_measure , pol . unit_of_measure ) pol_unit_of_measure , pol . price_to_print pol_price_to_print , pol . amount_to_print C_AMOUNT_POL , round ( pol . quantity_comitted , : P_QTY _PRECISION ) pol_quantity_comitted , pol . un_number_and_desc pol_un_number_and_desc , pol . hazard_class pol_hazard_class , pol . cancel_flag pol_cancel_flag , pol . cancel_date pol_cancel_date , pol . note_to_vendor pol_note_to_vendor , pol . contract_num pol_contract_num , pol . po_quote_num pol_po_quote_num , pol . vendor_quote_num pol_vendor_quote_num , pol . quotation_line pol_quotation_line , pol . po_header_id pol_po_header_id , pol . po_line_id pol_po_line_id , rownum pol_ro w_num , pol . line_type pol_line_type , nvl ( pol . po_release_id , - 1 ) pol_join_release_id , nvl ( pol . src_ga_flag , 'N' ) pol_src_ga_flag , pol . from_header_id pol_from_header_id , pol . from_line_id pol_from_line_id FROM po_lines_print pol WHERE ( 1 = 1 AND decode ( : poh_po_type , 'BLANKET' , decode ( : P_BLANKET_LINES , 'Y' , - 1 , - 2 ) , 'PLANNED' , decode ( : P_BLANKET_LINES , 'Y' , - 1 , - 2 ) , 'CONTRACT' , decode ( : P_BLANKET_LINES , 'Y' , - 1 , - 2 ) , - 1 ) = - 1 ) AND ( :poh_po_header_id = pol.po_header_id) AND ( :poh_join_release_id = nvl ( pol.po_release_id , - 1 )) order by pol.line_num
avc1jqzz04wpr SELECT 'x' FROM DUAL
azfadzhfxbxj3 SELECT ROW_ID, APBA_INACTIVE_DATE, DEFAULT_TAX_EXEMPT_FLAG, BS_BATCH_SOURCE_NAME, TRX_NUMBER, RA_BILLING_NUMBER, SOA_AGREEMENT_NAME, RAB_BATCH_NAME, RAC_BILL_TO_CUSTOMER_NAME, RAC_BILL_TO_CUSTOMER_NUM, RAA_BILL_TO_CONCAT_ADDRESS, RAA_BILL_TO_ADDRESS1, RAA_BILL_TO_ADDRESS2, RAA_BILL_TO_ADDRESS3, RAA_BILL_TO_ADDRESS3_DB, RAA_BILL_TO_CITY, FT_BILL_TO_COUNTRY, RAA_BILL_TO_COUNTY, RAA_BILL_TO_POSTAL_CODE, RAA_BILL_TO_PROVINCE, RAA_BILL_TO_STATE, RACO_BILL_TO_CONTACT_NAME, BILL_TO_TAXPAYER_ID, SU_BILL _TO_LOCATION, OF_SHIP_VIA_NAME, CTT_CLASS, COMMENTS, END_DATE_COMMITMENT, START_DATE_COMMITMENT, COMPLETE_FLAG, CT_RELATED_TRX_NUMBER, APBA_BANK_ACCOUNT_NAME, APBA_BANK_ACCOUNT_NUM, APB_CUSTOMER_BANK_BRANCH_NAME, APB_CUSTOMER_BANK_NAME, CUSTOMER_REFERENCE, CUSTOMER_REFERENCE_DATE, INVOICE_CURRENCY_CODE, AL_DEFAULT_TAX_EXEMPT_FLAG, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, PS_DISPUTE_AMOUNT, PS_DISPUTE_DATE, DOC_SEQUENCE_VALUE, TERM_DUE_DATE, EXCHANGE_DATE, EXCHANGE_RATE, GDCT_USER_EXCHANGE_RATE_TYPE, AL_FOB_MEANING, FINANCE_CHARGES, GD_GL_DATE, INVOICING_RULE_ID, CT_MODEL_TRX_NUMBER, PRINTING_ORIGINAL_DATE, CT_INVOICE_FOR_CB, PURCHASE_ORDER_DATE, PURCHASE_ORDER_REVISION, SU_PAYING_CUSTOMER_LOCATION, RAC_PAYING_CUSTOMER_NAME, RAC_PAYING_CUSTOMER_NUM, ARM_RECEIPT_METHOD_NAME, PRINTING_COUNT, PRINTING_LAST_PRINTED, PRINTING_OPT ION, PURCHASE_ORDER, CT_REFERENCE, RAA_CONCAT_REMIT_TO_ADDRESS, RAA_REMIT_TO_ADDRESS1, RAA_REMIT_TO_ADDRESS2, RAA_REMIT_TO_ADDRESS3, RAA_REMIT_TO_ADDRESS3_DB, RAA_REMIT_TO_CITY, RAA_REMIT_TO_COUNTY, RAA_REMIT_TO_STATE, RAA_REMIT_TO_PROVINCE, RAA_REMIT_TO_POSTAL_CODE, FT_REMIT_TO_COUNTRY, CREDIT_METHOD_FOR_RULES, RAS_PRIMARY_SALESREP_NAME, RAS_PRIMARY_SALESREP_NUM, SHIP_DATE_ACTUAL, RAA_SHIP_TO_CONCAT_ADDRESS, RAA_SHIP_TO_ADDRESS1, RAA_SHIP_TO_ADDRESS2, RAA_SHIP_TO_ADDRESS3, RAA_SHIP_TO_ADDRESS3_ DB, RAA_SHIP_TO_CITY, RAA_SHIP_TO_COUNTY, RAA_SHIP_TO_STATE, RAA_SHIP_TO_PROVINCE, RAA_SHIP_TO_POSTAL_CODE, FT_SHIP_TO_COUNTRY, RAC_SHIP_TO_CUSTOMER_NAME, RACO_SHIP_TO_CONTACT_NAME, RAC_SHIP_TO_CUSTOMER_NUM, SU_SHIP_TO_LOCATION, SHIP_TO_TAXPAYER_ID, RAC_SOLD_TO_CUSTOMER_NAME, RAC_SOLD_TO_CUSTOMER_NUM, INTERNAL_NOTES, CREDIT_METHOD_FOR_INSTALLMENTS, STATUS_TRX, RAT_TERM_NAME, DEFAULT_USSGL_TRANSACTION_CODE, TRX_DATE, INTERFACE_HEADER_CONTEXT, INTERFACE_HEADER_ATTRIBUTE1, INTERFACE_HEADER_ATTRIBUT E2, INTERFACE_HEADER_ATTRIBUTE3, INTERFACE_HEADER_ATTRIBUTE4, INTERFACE_HEADER_ATTRIBUTE5, INTERFACE_HEADER_ATTRIBUTE6, INTERFACE_HEADER_ATTRIBUTE7, INTERFACE_HEADER_ATTRIBUTE8, INTERFACE_HEADER_ATTRIBUTE9, INTERFACE_HEADER_ATTRIBUTE10, INTERFACE_HEADER_ATTRIBUTE11, INTERFACE_HEADER_ATTRIBUTE12, INTERFACE_HEADER_ATTRIBUTE13, INTERFACE_HEADER_ATTRIBUTE14, INTERFACE_HEADER_ATTRIBUTE15, CTT_TYPE_NAME, WAYBILL_NUMBER, CUSTOMER_TRX_ID, PREVIOUS_CUSTOMER_TRX_ID, INITIAL_CUSTOMER_TRX_ID, RELATED_BATCH_ SOURCE_ID, RELATED_CUSTOMER_TRX_ID, CUST_TRX_TYPE_ID, BATCH_ID, BATCH_SOURCE_ID, REASON_CODE, TERM_ID, PRIMARY_SALESREP_ID, AGREEMENT_ID, RECEIPT_METHOD_ID, SHIP_VIA, FOB_POINT, CUSTOMER_BANK_ACCOUNT_ID, RECURRED_FROM_TRX_NUMBER, SOLD_TO_CUSTOMER_ID, SOLD_TO_SITE_USE_ID, SOLD_TO_CONTACT_ID, BILL_TO_CUSTOMER_ID, BILL_TO_SITE_USE_ID, BILL_TO_CONTACT_ID, SHIP_TO_CUSTOMER_ID, SHIP_TO_SITE_USE_ID, SHIP_TO_CONTACT_ID, REMIT_TO_ADDRESS_ID, CREATED_FROM, PRINTING_PENDING, LAST_PRINTED_SEQUENCE_NUM, EXCH ANGE_RATE_TYPE, TERRITORY_ID, DOC_SEQUENCE_ID, PAYING_CUSTOMER_ID, PAYING_SITE_USE_ID, DH_MAX_DISPUTE_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, BS_AUTO_TRX_NUMBERING_FLAG, BS_BATCH_SOURCE_TYPE, REV_RECOG_RUN_FLAG, CTT_OPEN_RECEIVABLES_FLAG, CTT_ALLOW_FREIGHT_FLAG, CTT_POST_TO_GL_FLAG, CTT_CREATION_SIGN, CTT_ALLOW_OVERAPPLICATION_FLAG, CTT_NATURAL_APP_ONLY_FLAG, CTT_TAX_CALCULATION_FLAG, CTT_DEFAULT_STATUS, CTT_DEFAULT_TERM, CTT_DEFAULT_PRINTING_OPTION , RAA_BILL_TO_ADDRESS_ID, RAA_SHIP_TO_ADDRESS_ID, ARC_CREATION_METHOD_CODE, ARM_PAYMENT_TYPE_CODE, RAT_TERM_IN_USE_FLAG, RULES_FLAG, ACTIVITY_FLAG, PRINTED_FLAG, POSTED_FLAG, CM_AGAINST_TRX_FLAG, SELECTED_FOR_PAYMENT_FLAG, OVERRIDE_TERMS, COMMITMENTS_EXIST_FLAG, AGREEMENTS_EXIST_FLAG, ATCHMT_FLAG, GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6, GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE10, GLOBAL_ATT RIBUTE11, GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18, GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY, ORIG_SYSTEM_BATCH_NAME, OLD_TRX_NUMBER, BS_COPY_DOC_NUMBER_FLAG FROM RA_CUSTOMER_TRX_PARTIAL_V WHERE NVL(:1 , 0) = NVL(of_organization_id, NVL(:2, 0)) and (CUSTOMER_TRX_ID=:3) order by CUSTOMER_TRX_ID
bf4kcv5v66czb SELECT 'NOTE', '2', b.creation_date, b.entered_by, b.jtf_note_id, t.notes, b.note_status, lkcmttype.meaning, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, sysdate, sysdate, r.resource_id FROM fnd_lookup_values_vl lkcmttype, jtf_notes_tl t, jtf_notes_b b, fnd_user u, per_people_f e, hz_parties p, jtf_rs_r esource_extns r WHERE b.jtf_note_id = t.jtf_note_id AND t.language = userenv('LANG') AND b.source_object_code = 'SR' AND b.note_status = 'E' AND b.source_object_id = :1 AND lkcmttype.lookup_type = 'JTF_NOTE_TYPE' AND b.note_type = lkcmttype.lookup_code AND b.created_by = u.user_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(e.effective_start_date, SYSDATE)) AND TRUNC(NVL(e.effective_end_date, SYSDATE)) UNION ALL SELECT 'ATTACHMENT', '3', lobs.upload_date, atth.created_by, lobs.file_id, null, null, lobs.file_name, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, SYSDATE, SYSDATE, r.resource_id FROM fnd_lobs lobs, fnd_attached_docs_form_vl atth, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extn s r WHERE atth.entity_name = 'CS_INCIDENTS' AND atth.pk1_value = :2 AND atth.created_by = u.user_id AND atth.function_name = :3 AND atth.media_id = lobs.file_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(e.effective_start_date, SYSDATE)) AND TRUNC(NVL(e.effective_end_date, SYSDATE)) UNION ALL SELECT 'TASK', '5', task.creation_date, task.created_by, task.task_id, tl.task_name, tl.description, s tl.name, tktype.name, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, task.planned_start_date, task.planned_end_date, r.resource_id FROM jtf_tasks_b task, jtf_tasks_tl tl, jtf_task_statuses_tl stl, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extns r, jtf_task_types_tl tktype WHERE task.source_object_id = :4 AND task.source_object_type_code = 'SR' AND tl.language = userenv ('LANG') AND stl.language = userenv('LANG') AND tktype.task_type_id = task.task_type_id AND tktype.language = userenv('LANG') AND task.task_status_id = stl.task_status_id AND task.created_by = u.user_id AND task.task_id = tl.task_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND trunc(sysdate) between trunc(nvl(e.effective_start_date, sysdate)) AND trunc(nvl(e.effecti ve_end_date, sysdate)) and task.publish_flag = 'Y' UNION ALL SELECT 'AUDIT', '4', audi.creation_date, audi.created_by, audi.incident_audit_id, null, null, null, AUDI.CHANGE_INCIDENT_SEVERITY_FLAG, sevt_old.name, sevt.name, AUDI.CHANGE_INCIDENT_TYPE_FLAG, typet_old.name, typet.name, AUDI.CHANGE_INCIDENT_STATUS_FLAG, stat_old.name, stat.name, AUDI.CHANGE_INCIDENT_URGENCY_FLAG, urgt_old.name, urgt.name, DECODE(AUDI.CHANGE_CUSTOMER_PRODUCT_FLAG, 'Y', AUDI.CHANGE_CUSTOMER_PRO DUCT_FLAG, AUDI.CHANGE_INVENTORY_ITEM_FLAG), DECODE(AUDI.CHANGE_CUSTOMER_PRODUCT_FLAG, 'Y', DECODE(AUDI.CHANGE_INVENTORY_ITEM_FLAG, 'Y', NVL(to_char(audi.old_customer_product_id), 'INV'||audi.old_inventory_item_id), audi.old_customer_product_id), 'INV'||audi.old_inventory_item_id), DECODE(AUDI.CHANGE_CUSTOMER_PRODUCT_FLAG, 'Y', DECODE(AUDI.CHANGE_INVENTORY_ITEM_FLA G, 'Y', NVL(to_char(audi.customer_product_id), 'INV'||audi.inventory_item_id), audi.customer_product_id), 'INV'||audi.inventory_item_id), u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, sysdate, sysdate, r.resource_id FROM cs_incidents_audit_b audi, CS_INCIDENT_SEVERITIES_VL sevt_old, CS_INCIDENT_SEVERITIES_VL sevt, cs_incident_types_VL typet_old, c s_incident_types_VL typet, cs_incident_statuses_VL stat_old, cs_incident_statuses_VL stat, cs_incident_urgencies_VL urgt_old, cs_incident_urgencies_VL urgt, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extns r WHERE audi.incident_id = :5 AND sevt_old.incident_severity_id = AUDI.old_incident_severity_id AND sevt.incident_severity_id = AUDI.incident_severity_id AND typet_old.incident_type_id = AUDI.old_incident_type_id AND typet.incident_type_id = AUDI.incident_type_id AND sta t_old.incident_status_id = AUDI.old_incident_status_id AND stat.incident_status_id = AUDI.incident_status_id AND urgt_old.incident_urgency_id(+) = AUDI.old_incident_urgency_id AND urgt.incident_urgency_id(+) = AUDI.incident_urgency_id AND audi.created_by = u.user_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND trunc(sysdate) between trunc(nvl(e.effective_start_date, sysdate)) AND trunc(nvl(e.effective_end_date, sysdate)) UNION ALL SELE CT 'SOLUTION', '6', link.creation_date, link.created_by, solu.set_id, solu.set_name, solu.set_number, solu.set_link_type_meaning, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, u.employee_id, u.customer_id, e.first_name, e.last_name, p.party_type, p.person_first_name, p.person_last_name, SYSDATE, SYSDATE, r.resource_id FROM cs_kb_linked_solutions_view solu, cs_kb_set_links link, fnd_user u, per_people_f e, hz_parties p, jtf_rs_resource_extns r WHERE solu.other_id = :6 AND solu.set_id = link.set_id AND solu.object_code = 'SR' AND link.created_by = u.user_id AND link.other_id = solu.other_id AND u.employee_id = e.person_id (+) AND u.customer_id = p.party_id (+) AND u.user_id = r.user_id(+) AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(e.effective_start_date, SYSDATE)) AND TRUNC(NVL(e.effective_end_date, SYSDATE)) ORDER BY 3 desc
bk9zgysk994av SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENC E , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ .LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFIER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.QUALIFIER_ATTR_VALUE = QPLATQ.VALUE_FROM AND QPQ.COMPARISON_OPERATOR_CODE = '=' AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIV E , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE U NION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_o uter_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER _PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.QUALIFIER_ATTR_VALUE = QPLATQ.VALUE_FROM AND QPQ.COMPARISON_OPERATOR_CODE = '=' AND QPQ.SEARCH_IND = 2 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_ CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DA TE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_m ini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_AC TIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFIER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = :B14 AND QPQ.QUALIFIER_DATATYPE = :B13 AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(QPQ.QUALIFIER_DATATYPE, :B13 , QPLATQ.VALUE_FRO M, NULL)) BETWEEN QPQ.QUAL_ATTR_VALUE_FROM_NUMBER AND QPQ.QUAL_ATTR_VALUE_TO_NUMBER AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIV E, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 A ND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_ou ter_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIE R_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_ CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = : B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = :B14 AND QPQ.QUALIFIER_DATATYPE = :B13 AND QPQ.SEARCH_IND = 2 AND FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(QPQ .QUALIFIER_DATATYPE, :B13 , QPLATQ.VALUE_FROM, NULL)) BETWEEN QPQ.QUAL_ATTR_VALUE_FROM_NUMBER AND QPQ.QUAL_ATTR_VALUE_TO_NUMBER AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DAT E_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_T YPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplatq qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATT RIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FI RST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFI ER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.CO MPARISON_OPERATOR_CODE = :B14 AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND QPQ.QUALIFIER_DATATYPE IN (:B17 , :B16 , :B15 ) AND QPLATQ.VALUE_FROM BETWEEN QPQ.QUALIFIER_ATTR_VALUE AND QPQ.QUALIFIER_ATTR_VALUE_TO AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND QPH.ACTIVE_FLAG = :B5 AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPQ.ACTIVE_FLAG = :B5 AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_out er_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_ FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PR ICING_EFFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_D ATE_SECOND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = :B14 AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_DATATYPE IN (:B17 , :B16 , :B15 ) AND QPLATQ.VALUE_FROM BETWEEN QPQ.QUALIFIER_ATTR_VALUE AND QPQ.QUALIFIER_ATTR_VALUE_TO AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND QPH.ACTIVE_FLAG = :B5 AND ((:B7 BETWEEN NV L(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPQ.ACTIVE_FLAG = :B5 AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH. LIST_HEADER_ID AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_outer_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_EFFECTIVE_DATE, QPH .START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SECOND_TYPE, QPH.LIMI T_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID, QUALIFIER_GROUPING_NO FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = QPTQ.QUALIFIER_GROUPING_NO AND QPQ.QUALIFIER_GROUPING_NO <> -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ.COMPARISON_OPERATOR_CODE = 'NOT =' AND QPQ.QUALIFIER_ATTR_VALUE <> QPLATQ.VALUE_FROM AND QPQ.SEARCH_IND = 2 AND QPQ.QUALIFIER_GROUP_CNT > 1 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.ST ART_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLA TQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE UNION ALL SELECT /*+ ORDERED USE_NL(qpq qplh qph) index(qpq qp_qualifiers_n1) index(qph qp_list_headers_b_n7) l_mini_out er_qual_cur_mod */ QPQ.LIST_HEADER_ID, QPQ.LIST_LINE_ID LIST_LINE_ID, QPQ.QUALIFIER_GROUP_CNT, QPQ.OTHERS_GROUP_CNT, QPQ.HEADER_QUALS_EXIST_FLAG, QPQ.SEARCH_IND, :B11 VALIDATED_FLAG, :B11 APPLIED_FLAG, :B12 ATTRIBUTE_LEVEL, :B4 ATTRIBUTE_TYPE, QPLATQ.CONTEXT , QPLATQ.ATTRIBUTE , QPLATQ.VALUE_FROM , QPQ.QUALIFIER_GROUPING_NO QUALIFIER_GROUPING_NO , QPQ.QUALIFIER_PRECEDENCE , QPQ.QUALIFIER_DATATYPE, QPQ.QUALIFIER_ATTR_VALUE , QPQ.QUALIFIER_ATTR_VALUE_TO , QPQ.START_DATE_ACTIVE, QPQ.END_DATE_ACTIVE, :B11 PRICING_ATTR_FLAG, QPQ.COMPARISON_OPERATOR_CODE , QPH.LIST_TYPE_CODE, QPH.ASK_FOR_FLAG, QPLATQ.PRICING_STATUS_CODE, 'OHQ1' , NULL LINE_INDEX, NULL PRICING_E FFECTIVE_DATE, QPH.START_DATE_ACTIVE_FIRST START_DATE_ACTIVE_FIRST, QPH.END_DATE_ACTIVE_FIRST END_DATE_ACTIVE_FIRST, NULL LINE_TYPE_CODE, :B1 PRICING_PHASE_ID, QPQ.LIST_HEADER_ID || '-' || QPQ.LIST_LINE_ID || '-' || QPQ.QUALIFIER_GROUPING_NO UNIQUE_KEY, QPH.START_DATE_ACTIVE START_DATE_ACTIVE_H, QPH.END_DATE_ACTIVE END_DATE_ACTIVE_H, QPH.START_DATE_ACTIVE_SECOND START_DATE_ACTIVE_SECOND, QPH.END_DATE_ACTIVE_SECOND END_DATE_ACTIVE_SECOND, QPH.ACTIVE_DATE_FIRST_TYPE, QPH.ACTIVE_DATE_SEC OND_TYPE, QPH.LIMIT_EXISTS_FLAG FROM (SELECT DISTINCT LIST_HEADER_ID, LIST_LINE_ID FROM QP_PREQ_QUAL_TMP WHERE PRICING_STATUS_CODE = :B2 AND PRICING_PHASE_ID = :B1 ) QPTQ, QP_QUALIFIERS QPQ , QP_NPREQ_LINE_ATTRS_TMP QPLATQ, QP_LIST_HEADERS_B QPH, QP_PRICE_REQ_SOURCES_V QPRS WHERE QPQ.LIST_HEADER_ID = QPTQ.LIST_HEADER_ID AND QPQ.LIST_LINE_ID = QPTQ.LIST_LINE_ID AND QPQ.QUALIFIER_GROUPING_NO = -1 AND QPQ.QUALIFIER_CONTEXT = QPLATQ.CONTEXT AND QPQ.QUALIFIER_ATTRIBUTE = QPLATQ.ATTRIBUTE AND QPQ .COMPARISON_OPERATOR_CODE = 'NOT =' AND QPQ.QUALIFIER_ATTR_VALUE <> QPLATQ.VALUE_FROM AND QPQ.SEARCH_IND = 2 AND ((QPH.CURRENCY_CODE IS NOT NULL AND QPH.CURRENCY_CODE = :B10 ) OR QPH.CURRENCY_CODE IS NULL) AND ((NVL(:B8 , 'OFF') = 'ON' AND :B9 = QPH.ORIG_ORG_ID) OR (NVL(:B8 , 'OFF') = 'ON' AND NVL(QPH.GLOBAL_FLAG, 'Y') = 'Y') OR NVL(:B8 , 'OFF') = 'OFF' ) AND ((:B7 BETWEEN NVL(QPH.START_DATE_ACTIVE , :B7 ) AND NVL(QPH.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPH.START_DATE_ACTIVE , : B6 ) AND NVL(QPH.END_DATE_ACTIVE, :B6 ))) AND ((:B7 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B7 ) AND NVL(QPQ.END_DATE_ACTIVE, :B7 ) OR :B6 BETWEEN NVL(QPQ.START_DATE_ACTIVE , :B6 ) AND NVL(QPQ.END_DATE_ACTIVE, :B6 ))) AND QPH.LIST_TYPE_CODE NOT IN ('PRL', 'AGR') AND QPH.ACTIVE_FLAG = :B5 AND QPQ.ACTIVE_FLAG = :B5 AND QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID AND QPLATQ.LINE_INDEX > 0 AND QPLATQ.DISTINCT_QUALIFIER_FLAG = :B5 AND QPLATQ.PRICING_STATUS_CODE = :B2 AND QPLATQ.ATTRIBUTE_TYPE = :B4 AND QPRS.REQUEST_TYPE_CODE = :B3 AND QPH.SOURCE_SYSTEM_CODE = QPRS.SOURCE_SYSTEM_CODE
bkxrrs52m7d8p BEGIN WSH_SHIP_CONFIRM_ACTIONS.interface_ALL_wrp( :errbuf, :rc, :A0, :A1, :A2, :A3, :A4, :A5, :A6, :A7, :A8); END;
bp8crdjba8zgt update ra_cust_trx_line_gl_dist set gl_posted_date=:b0, posting_control_id=:b1, last_update_date=sysdate , last_updated_by=:b2 where (cust_trx_line_gl_dist_id in (select to_number(reference23) from gl_interface where ((((user_je_source_name=:b3 and set_of_books_id=:b4) and group_id=:b1) and (reference30||'')='RA_CUST_TRX_LINE_GL_DIST') and accounting_date between to_date((:b6||' 00:00:00'), 'DD-MON-RR HH24:MI:SS') and to_date((:b7||' 23:59:59'), 'DD-MON-RR HH24:MI:SS'))) and set_of_books_id=:b 4)
bt0w082dfrm8v BEGIN WSH_SHIP_CONFIRM_ACTIONS.interface_ALL_wrp( :errbuf, :rc, :A0, :A1, :A2, :A3); END;
c4y7jp0q2awbf SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID = :B4 AND APPLICATION_ID = :B3 AND LEVEL_ID = :B2 AND LEVEL_VALUE = :B1
cd9mbmqyf4qhp UPDATE WF_ITEM_ACTIVITY_STATUSES SET ACTIVITY_STATUS = :B8 , ACTIVITY_RESULT_CODE = NVL(:B13 , ACTIVITY_RESULT_CODE), BEGIN_DATE = NVL(:B11 , BEGIN_DATE), END_DATE = NVL(:B12 , END_DATE), DUE_DATE = DECODE(:B11 , TO_DATE(NULL), DUE_DATE, :B10 ), OUTBOUND_QUEUE_ID = :B9 , EXECUTION_TIME = DECODE(:B8 , :B7 , :B4 , :B6 , NVL(EXECUTION_TIME, :B4 ), :B5 , NVL(EXECUTION_TIME, :B4 ), EXECUTION_TIME) WHERE ITEM_TYPE = :B3 AND ITEM_KEY = :B2 AND PROCESS_ACTIVITY = :B1
cgb6kp2umq52a select t.schema, t.name, t.flags, q.name, t.timezone from system.aq$_queue_tables t, system.aq$_queues q where t.objno = :1 and q.table_objno = t.objno and q.usage = 0 and NOT ( t.name in ('DEF$_AQCALL' , 'DEF$_AQERROR') and t.schema = 'SYSTEM')
cwyx16zn1hpfb select parameter, value from nls_session_parameters
fmfdkztk8vx23 SELECT CATEGORY, SEVERITY, FND_LOG_SEVERITY FROM FND_NEW_MESSAGES WHERE MESSAGE_NAME = :B1 AND APPLICATION_ID = :B2 AND LANGUAGE_CODE = :B3 AND ROWNUM = 1
ghw9svn8x4vzu BEGIN WSH_PICK_LIST.RELEASE_BATCH_SRS( :errbuf, :rc, :A0, :A1, :A2); END;

Back to SQL Statistics
Back to Top

 

Instance Activity Statistics

Back to Top

Instance Activity Stats

Statistic Total per Second per Trans
CPU used by this session 1,248,201 342.96 4.85
CPU used when call started 1,248,647 343.08 4.85
CR blocks created 1,993,439 547.72 7.74
Cached Commit SCN referenced 28,345 7.79 0.11
Commit SCN cached 140 0.04 0.00
DB time 4,807,124 1,320.80 18.66
DBWR checkpoint buffers written 224,159 61.59 0.87
DBWR checkpoints 183 0.05 0.00
DBWR object drop buffers written 4,777 1.31 0.02
DBWR parallel query checkpoint buffers written 0 0.00 0.00
DBWR revisited being-written buffer 135 0.04 0.00
DBWR transaction table writes 4,134 1.14 0.02
DBWR undo block writes 456,690 125.48 1.77
DDL statements parallelized 1 0.00 0.00
DFO trees parallelized 16 0.00 0.00
DML statements parallelized 1 0.00 0.00
IMU CR rollbacks 3,146,670 864.58 12.22
IMU Flushes 74,031 20.34 0.29
IMU Redo allocation size 360,161,992 98,958.03 1,398.12
IMU bind flushes 494 0.14 0.00
IMU commits 135,551 37.24 0.53
IMU contention 9,194 2.53 0.04
IMU ktichg flush 3,465 0.95 0.01
IMU pool not allocated 1 0.00 0.00
IMU recursive-transaction flush 445 0.12 0.00
IMU undo allocation size 721,049,104 198,115.29 2,799.06
IMU- failed to get a private strand 1 0.00 0.00
PX local messages recv'd 5,382 1.48 0.02
PX local messages sent 5,382 1.48 0.02
Parallel operations downgraded 25 to 50 pct 13 0.00 0.00
Parallel operations not downgraded 3 0.00 0.00
SQL*Net roundtrips to/from client 10,871,768 2,987.12 42.20
active txn count during cleanout 8,099,545 2,225.43 31.44
application wait time 10,873 2.99 0.04
background checkpoints completed 2 0.00 0.00
background checkpoints started 2 0.00 0.00
background timeouts 10,404 2.86 0.04
branch node splits 249 0.07 0.00
buffer is not pinned count 157,521,444 43,280.56 611.49
buffer is pinned count 382,854,903 105,193.13 1,486.21
bytes received via SQL*Net from client 2,467,550,355 677,983.57 9,578.85
bytes sent via SQL*Net to client 2,217,450,487 609,266.19 8,607.98
calls to get snapshot scn: kcmgss 24,475,407 6,724.86 95.01
calls to kcmgas 3,074,535 844.76 11.94
calls to kcmgcs 1,725,475 474.09 6.70
change write time 31,786 8.73 0.12
cleanout - number of ktugct calls 3,687,279 1,013.12 14.31
cleanouts and rollbacks - consistent read gets 1,876,138 515.49 7.28
cleanouts only - consistent read gets 39,403 10.83 0.15
cluster key scan block gets 8,166,455 2,243.81 31.70
cluster key scans 1,671,976 459.39 6.49
commit cleanout failures: block lost 55,460 15.24 0.22
commit cleanout failures: buffer being written 14 0.00 0.00
commit cleanout failures: callback failure 2,816 0.77 0.01
commit cleanout failures: cannot pin 3,525 0.97 0.01
commit cleanouts 1,940,165 533.08 7.53
commit cleanouts successfully completed 1,878,350 516.10 7.29
commit txn count during cleanout 234,971 64.56 0.91
concurrency wait time 5,865 1.61 0.02
consistent changes 48,855,360 13,423.49 189.65
consistent gets 280,637,402 77,107.87 1,089.41
consistent gets - examination 156,486,130 42,996.09 607.47
consistent gets direct 101 0.03 0.00
consistent gets from cache 280,508,313 77,072.40 1,088.91
current blocks converted for CR 5 0.00 0.00
cursor authentications 1,093 0.30 0.00
data blocks consistent reads - undo records applied 47,866,263 13,151.72 185.81
db block changes 48,602,507 13,354.01 188.67
db block gets 59,361,865 16,310.25 230.44
db block gets direct 2,790 0.77 0.01
db block gets from cache 59,357,422 16,309.03 230.42
deferred (CURRENT) block cleanout applications 375,204 103.09 1.46
dirty buffers inspected 378,445 103.98 1.47
enqueue conversions 4,104 1.13 0.02
enqueue releases 6,144,103 1,688.15 23.85
enqueue requests 6,147,513 1,689.09 23.86
enqueue timeouts 3,229 0.89 0.01
enqueue waits 4,707 1.29 0.02
exchange deadlocks 289 0.08 0.00
execute count 22,012,220 6,048.07 85.45
free buffer inspected 1,365,519 375.19 5.30
free buffer requested 3,431,726 942.90 13.32
heap block compress 1,644,925 451.96 6.39
hot buffers moved to head of LRU 914,804 251.35 3.55
immediate (CR) block cleanout applications 1,915,541 526.31 7.44
immediate (CURRENT) block cleanout applications 342,393 94.08 1.33
index fast full scans (direct read) 0 0.00 0.00
index fast full scans (full) 0 0.00 0.00
index fast full scans (rowid ranges) 0 0.00 0.00
index fetch by key 46,283,386 12,716.81 179.67
index scans kdiixs1 35,244,666 9,683.82 136.82
java call heap collected bytes 6,599,792 1,813.36 25.62
java call heap collected count 143,710 39.49 0.56
java call heap gc count 14 0.00 0.00
java call heap live object count 11,198 3.08 0.04
java call heap live object count max 11,474 3.15 0.04
java call heap live size 3,364,080 924.31 13.06
java call heap live size max 3,392,320 932.07 13.17
java call heap object count 32,108 8.82 0.12
java call heap object count max 32,108 8.82 0.12
java call heap total size 4,718,592 1,296.48 18.32
java call heap total size max 4,718,592 1,296.48 18.32
java call heap used size 4,315,088 1,185.61 16.75
java call heap used size max 4,315,600 1,185.75 16.75
leaf node 90-10 splits 8,195 2.25 0.03
leaf node splits 61,099 16.79 0.24
logons cumulative 2,210 0.61 0.01
messages received 259,826 71.39 1.01
messages sent 259,826 71.39 1.01
no buffer to keep pinned count 116 0.03 0.00
no work - consistent read gets 87,910,282 24,154.21 341.26
opened cursors cumulative 1,940,107 533.06 7.53
parse count (failures) 731 0.20 0.00
parse count (hard) 6,599 1.81 0.03
parse count (total) 2,488,465 683.73 9.66
parse time cpu 38,831 10.67 0.15
parse time elapsed 43,134 11.85 0.17
physical read IO requests 395,357 108.63 1.53
physical reads 453,708 124.66 1.76
physical reads cache 432,206 118.75 1.68
physical reads cache prefetch 38,257 10.51 0.15
physical reads direct 19,133 5.26 0.07
physical reads direct temporary tablespace 18,883 5.19 0.07
physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 398,891 109.60 1.55
physical writes 811,848 223.06 3.15
physical writes direct 21,823 6.00 0.08
physical writes direct (lob) 1,104 0.30 0.00
physical writes direct temporary tablespace 20,535 5.64 0.08
physical writes from cache 788,501 216.65 3.06
physical writes non checkpoint 712,320 195.72 2.77
pinned buffers inspected 1,846 0.51 0.01
prefetch warmup blocks aged out before use 0 0.00 0.00
prefetch warmup blocks flushed out before use 0 0.00 0.00
prefetched blocks aged out before use 10 0.00 0.00
process last non-idle time 3,637 1.00 0.01
queries parallelized 14 0.00 0.00
recursive calls 36,783,510 10,106.63 142.79
recursive cpu usage 656,105 180.27 2.55
redo blocks written 15,989,205 4,393.19 62.07
redo buffer allocation retries 71 0.02 0.00
redo entries 24,153,581 6,636.43 93.76
redo log space requests 15 0.00 0.00
redo log space wait time 30 0.01 0.00
redo ordering marks 401,454 110.30 1.56
redo size 7,849,726,560 2,156,789.07 30,472.07
redo synch time 149,393 41.05 0.58
redo synch writes 119,141 32.74 0.46
redo wastage 69,078,012 18,979.86 268.16
redo write time 48,512 13.33 0.19
redo writer latching time 71 0.02 0.00
redo writes 199,174 54.73 0.77
rollback changes - undo records applied 92,318 25.37 0.36
rollbacks only - consistent read gets 114,813 31.55 0.45
rows fetched via callback 36,102,077 9,919.40 140.15
session connect time 0 0.00 0.00
session cursor cache hits 1,094,672 300.77 4.25
session logical reads 339,999,590 93,418.21 1,319.85
session pga memory 9,095,314,376 2,499,026.49 35,307.35
session pga memory max 9,196,579,372 2,526,850.04 35,700.45
session uga memory 6,484,625,108 1,781,714.11 25,172.84
session uga memory max 10,783,963,524 2,962,999.34 41,862.56
shared hash latch upgrades - no wait 38,354,306 10,538.22 148.89
shared hash latch upgrades - wait 1,536 0.42 0.01
sorts (disk) 1 0.00 0.00
sorts (memory) 2,992,355 822.18 11.62
sorts (rows) 39,234,487 10,780.06 152.31
summed dirty queue length 797,387 219.09 3.10
switch current to new buffer 337,296 92.68 1.31
table fetch by rowid 231,616,683 63,638.95 899.12
table fetch continued row 2,193,239 602.61 8.51
table scan blocks gotten 12,636,895 3,472.11 49.06
table scan rows gotten 345,574,794 94,950.05 1,341.50
table scans (cache partitions) 129 0.04 0.00
table scans (direct read) 0 0.00 0.00
table scans (long tables) 957 0.26 0.00
table scans (rowid ranges) 1,076 0.30 0.00
table scans (short tables) 919,485 252.64 3.57
transaction rollbacks 3,893 1.07 0.02
transaction tables consistent read rollbacks 1 0.00 0.00
transaction tables consistent reads - undo records applied 98 0.03 0.00
undo change vector size 2,996,791,368 823,397.71 11,633.33
user I/O wait time 199,625 54.85 0.77
user calls 10,421,478 2,863.40 40.46
user commits 202,797 55.72 0.79
user rollbacks 54,807 15.06 0.21
workarea executions - onepass 2 0.00 0.00
workarea executions - optimal 1,594,070 437.99 6.19
write clones created in background 2,093 0.58 0.01
write clones created in foreground 86 0.02 0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

Statistic Begin Value End Value
session cursor cache count 42,696 100,404
opened cursors current 235,780 310,637
logons current 1,353 1,585

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

Statistic Total per Hour
log switches (derived) 2 1.98

Back to Instance Activity Statistics
Back to Top

 

IO Stats

Back to Top

Tablespace IO Stats

Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
APPS_UNDOTS1 1,166 0 2.42 1.00 239,542 66 22,777 0.07
APPS_TS_TX_DATA 192,992 53 5.31 1.05 39,000 11 5,019 1.21
APPS_TS_TX_IDX 121,038 33 5.88 1.00 62,356 17 7,007 1.22
SYSTEM 39,383 11 2.85 1.60 2,389 1 658 1.16
APPS_TS_TX_INTERFACE 5,494 2 3.03 1.42 15,382 4 52 1.15
SYSAUX 9,719 3 1.46 1.06 10,594 3 3 0.00
APPS_TS_PAY_IDX 5,749 2 2.40 1.00 9,118 3 6,371 0.44
TEMP 3,079 1 7.44 7.21 9,169 3 3 3.33
APPS_TS_PAY_DATA 6,397 2 3.20 1.00 5,116 1 3,563 0.34
APPS_TS_SEED 6,155 2 5.89 1.25 757 0 258 3.99
APPS_TS_QUEUES 3,036 1 2.22 1.02 2,905 1 17 1.76
CTXSYS 711 0 5.40 1.00 1,100 0 3 0.00
APPS_TS_SUMMARY 358 0 3.77 1.00 1,022 0 201 0.00
APPS_TS_ARCHIVE 218 0 6.24 1.00 431 0 4 0.00
APPS_TS_NOLOGGING 36 0 6.94 1.00 34 0 0 0.00
OLAP 16 0 4.38 1.69 2 0 0 0.00
APPS_TS_MEDIA 9 0 7.78 1.00 6 0 0 0.00
ODM 7 0 5.71 1.00 2 0 0 0.00
OWAPUB 5 0 10.00 1.00 2 0 0 0.00
PORTAL 5 0 12.00 1.00 2 0 0 0.00
SYNCSERVER 5 0 8.00 1.00 2 0 0 0.00

Back to IO Stats
Back to Top

File IO Stats

Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
APPS_TS_ARCHIVE /raw_links/archive1 98 0 5.71 1.00 207 0 1 0.00
APPS_TS_ARCHIVE /raw_links/archive2 120 0 6.67 1.00 224 0 3 0.00
APPS_TS_MEDIA /raw_links/media1 3 0 16.67 1.00 2 0 0 0.00
APPS_TS_MEDIA /raw_links/media2 3 0 6.67 1.00 2 0 0 0.00
APPS_TS_MEDIA /raw_links/media3 3 0 0.00 1.00 2 0 0 0.00
APPS_TS_NOLOGGING /raw_links/nologging1 31 0 7.10 1.00 32 0 0 0.00
APPS_TS_NOLOGGING /raw_links/nologging2 5 0 6.00 1.00 2 0 0 0.00
APPS_TS_PAY_DATA /raw_links/francis1 6,397 2 3.20 1.00 5,116 1 3,563 0.34
APPS_TS_PAY_IDX /raw_links/francis2 5,749 2 2.40 1.00 9,118 3 6,371 0.44
APPS_TS_QUEUES /raw_links/queues1 1,034 0 1.77 1.01 709 0 2 0.00
APPS_TS_QUEUES /raw_links/queues2 1,016 0 2.56 1.03 889 0 8 3.75
APPS_TS_QUEUES /raw_links/queues3 986 0 2.35 1.01 1,307 0 7 0.00
APPS_TS_SEED /raw_links/apps_ts_seed01 1,984 1 7.00 1.00 340 0 0 0.00
APPS_TS_SEED /raw_links/reference1 2,100 1 5.49 1.34 246 0 158 4.56
APPS_TS_SEED /raw_links/reference2 2,071 1 5.25 1.40 171 0 100 3.10
APPS_TS_SUMMARY /raw_links/summary1 59 0 4.41 1.00 185 0 67 0.00
APPS_TS_SUMMARY /raw_links/summary2 48 0 4.17 1.00 159 0 19 0.00
APPS_TS_SUMMARY /raw_links/summary3 81 0 2.84 1.00 194 0 18 0.00
APPS_TS_SUMMARY /raw_links/summary4 42 0 5.48 1.00 118 0 38 0.00
APPS_TS_SUMMARY /raw_links/summary5 63 0 3.65 1.00 185 0 21 0.00
APPS_TS_SUMMARY /raw_links/summary6 65 0 3.08 1.00 181 0 38 0.00
APPS_TS_TX_DATA /raw_links/tx_data1 9,382 3 4.99 1.07 2,319 1 320 1.53
APPS_TS_TX_DATA /raw_links/tx_data10 9,215 3 5.09 1.08 2,209 1 354 1.55
APPS_TS_TX_DATA /raw_links/tx_data11 9,962 3 5.40 1.07 2,378 1 275 1.24
APPS_TS_TX_DATA /raw_links/tx_data12 21,655 6 5.81 1.00 3,201 1 233 0.30
APPS_TS_TX_DATA /raw_links/tx_data13 22,125 6 5.75 1.00 2,727 1 152 0.20
APPS_TS_TX_DATA /raw_links/tx_data14 21,854 6 5.55 1.00 2,763 1 174 2.59
APPS_TS_TX_DATA /raw_links/tx_data15 21,471 6 5.53 1.00 2,832 1 158 0.38
APPS_TS_TX_DATA /raw_links/tx_data2 9,239 3 4.74 1.09 2,169 1 258 1.71
APPS_TS_TX_DATA /raw_links/tx_data3 9,618 3 4.94 1.08 2,654 1 321 1.18
APPS_TS_TX_DATA /raw_links/tx_data4 9,738 3 4.90 1.08 2,645 1 552 1.21
APPS_TS_TX_DATA /raw_links/tx_data5 7,907 2 5.03 1.11 2,706 1 633 0.87
APPS_TS_TX_DATA /raw_links/tx_data6 9,299 3 5.17 1.09 2,589 1 578 0.95
APPS_TS_TX_DATA /raw_links/tx_data7 12,092 3 5.36 1.06 2,831 1 351 1.03
APPS_TS_TX_DATA /raw_links/tx_data8 9,562 3 4.83 1.08 2,413 1 224 2.14
APPS_TS_TX_DATA /raw_links/tx_data9 9,873 3 4.72 1.08 2,564 1 436 1.44
APPS_TS_TX_IDX /raw_links/tx_idx1 7,048 2 5.70 1.00 3,736 1 482 0.98
APPS_TS_TX_IDX /raw_links/tx_idx10 6,844 2 5.91 1.00 3,103 1 444 0.72
APPS_TS_TX_IDX /raw_links/tx_idx11 7,035 2 5.82 1.00 3,043 1 585 3.42
APPS_TS_TX_IDX /raw_links/tx_idx12 10,794 3 5.96 1.00 5,859 2 492 2.26
APPS_TS_TX_IDX /raw_links/tx_idx13 10,456 3 5.81 1.00 5,281 1 417 0.41
APPS_TS_TX_IDX /raw_links/tx_idx14 10,486 3 6.16 1.00 5,279 1 278 0.68
APPS_TS_TX_IDX /raw_links/tx_idx15 10,389 3 6.41 1.00 5,473 2 270 1.26
APPS_TS_TX_IDX /raw_links/tx_idx2 6,574 2 5.43 1.00 3,899 1 369 0.81
APPS_TS_TX_IDX /raw_links/tx_idx3 7,099 2 5.84 1.00 3,904 1 248 1.37
APPS_TS_TX_IDX /raw_links/tx_idx4 7,903 2 6.12 1.00 3,904 1 479 2.19
APPS_TS_TX_IDX /raw_links/tx_idx5 7,081 2 5.54 1.00 4,173 1 456 1.05
APPS_TS_TX_IDX /raw_links/tx_idx6 7,403 2 5.78 1.00 4,412 1 1,143 0.46
APPS_TS_TX_IDX /raw_links/tx_idx7 7,854 2 5.66 1.00 4,049 1 525 0.67
APPS_TS_TX_IDX /raw_links/tx_idx8 7,019 2 5.72 1.00 3,195 1 445 1.01
APPS_TS_TX_IDX /raw_links/tx_idx9 7,053 2 5.97 1.00 3,046 1 374 1.18
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface 1,082 0 3.20 2.55 1,379 0 32 1.56
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface2 2,298 1 2.85 1.14 7,037 2 12 0.83
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface3 2,114 1 3.14 1.15 6,966 2 8 0.00
APPS_UNDOTS1 /raw_links/undo01 175 0 2.57 1.00 38,598 11 3,591 0.09
APPS_UNDOTS1 /raw_links/undo02 165 0 2.18 1.00 40,487 11 3,910 0.06
APPS_UNDOTS1 /raw_links/undo03 172 0 3.08 1.00 39,792 11 4,634 0.07
APPS_UNDOTS1 /raw_links/undo04 175 0 2.80 1.00 39,809 11 3,310 0.09
APPS_UNDOTS1 /raw_links/undo05 251 0 1.20 1.00 39,739 11 3,532 0.05
APPS_UNDOTS1 /raw_links/undo06 228 0 3.03 1.00 41,117 11 3,800 0.09
CTXSYS /raw_links/ctx1 463 0 5.85 1.00 699 0 1 0.00
CTXSYS /raw_links/ctx2 248 0 4.56 1.00 401 0 2 0.00
ODM /raw_links/odm 7 0 5.71 1.00 2 0 0 0.00
OLAP /raw_links/olap 16 0 4.38 1.69 2 0 0 0.00
OWAPUB /raw_links/owa1 5 0 10.00 1.00 2 0 0 0.00
PORTAL /raw_links/portal 5 0 12.00 1.00 2 0 0 0.00
SYNCSERVER /raw_links/mobile01 5 0 8.00 1.00 2 0 0 0.00
SYSAUX /raw_links/sysaux01 9,719 3 1.46 1.06 10,594 3 3 0.00
SYSTEM /raw_links/sys1 5,844 2 2.92 1.58 259 0 334 0.48
SYSTEM /raw_links/sys2 5,636 2 2.67 1.60 344 0 34 3.82
SYSTEM /raw_links/sys3 5,577 2 2.79 1.61 360 0 13 4.62
SYSTEM /raw_links/sys4 5,901 2 2.91 1.61 502 0 17 4.12
SYSTEM /raw_links/sys5 6,330 2 2.91 1.61 400 0 36 3.89
SYSTEM /raw_links/sys6 5,681 2 2.73 1.57 322 0 21 3.33
SYSTEM /raw_links/sys7 4,414 1 3.08 1.64 202 0 203 0.64
TEMP /raw_links/tmp1 1,719 0 6.94 7.98 4,589 1 0  
TEMP /raw_links/tmp2 585 0 3.81 5.17 2,009 1 0  
TEMP /raw_links/tmp3 775 0 11.30 7.05 2,571 1 3 3.33

Back to IO Stats
Back to Top

 

Buffer Pool Statistics

P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits
D 172,516 100 339,712,928 432,231 788,501 0 0 45,937


Back to Top

 

Advisory Statistics

Back to Top

Instance Recovery Stats

  Targt MTTR (s) Estd MTTR (s) Recovery Estd IOs Actual Redo Blks Target Redo Blks Log File Size Redo Blks Log Ckpt Timeout Redo Blks Log Ckpt Interval Redo Blks
B 0 63 39,819 1,065,661 1,619,805 17,510,400 1,619,805  
E 0 37 13,609 279,355 2,045,629 17,510,400 2,045,629  

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

P Size for Estimate (M) Size Factr Buffers for Estimate Est Physical Read Factor Estimated Physical Reads
D 128 0.09 16,048 6.47 4,481,195
D 256 0.19 32,096 4.97 3,443,468
D 384 0.28 48,144 4.20 2,913,027
D 512 0.37 64,192 3.71 2,573,468
D 640 0.47 80,240 3.22 2,230,232
D 768 0.56 96,288 2.86 1,978,764
D 896 0.65 112,336 2.04 1,410,396
D 1,024 0.74 128,384 1.70 1,177,344
D 1,152 0.84 144,432 1.38 954,365
D 1,280 0.93 160,480 1.11 766,591
D 1,376 1.00 172,516 1.00 692,852
D 1,408 1.02 176,528 0.97 671,865
D 1,536 1.12 192,576 0.90 622,954
D 1,664 1.21 208,624 0.86 598,712
D 1,792 1.30 224,672 0.84 580,628
D 1,920 1.40 240,720 0.81 564,572
D 2,048 1.49 256,768 0.80 551,689
D 2,176 1.58 272,816 0.78 542,858
D 2,304 1.67 288,864 0.77 535,790
D 2,432 1.77 304,912 0.77 530,311
D 2,560 1.86 320,960 0.75 517,770

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
99.54 72,264 332

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

  PGA Aggr Target(M) Auto PGA Target(M) PGA Mem Alloc(M) W/A PGA Used(M) %PGA W/A Mem %Auto W/A Mem %Man W/A Mem Global Mem Bound(K)
B 30,720 23,245 7,402.25 0.00 0.00 0.00 0.00 102,400
E 30,720 21,358 9,828.11 0.00 0.00 0.00 0.00 102,400

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Low Optimal High Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
2K 4K 1,504,990 1,504,990 0 0
64K 128K 5,313 5,313 0 0
128K 256K 314 314 0 0
256K 512K 269 269 0 0
512K 1024K 83,045 83,045 0 0
1M 2M 74 74 0 0
2M 4M 10 10 0 0
8M 16M 2 2 0 0
128M 256M 2 0 2 0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

PGA Target Est (MB) Size Factr W/A MB Processed Estd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit % Estd PGA Overalloc Count
3,840 0.13 92,812.49 2,369.89 98.00 483
7,680 0.25 92,812.49 165.96 100.00 0
15,360 0.50 92,812.49 165.96 100.00 0
23,040 0.75 92,812.49 165.96 100.00 0
30,720 1.00 92,812.49 165.96 100.00 0
36,864 1.20 92,812.49 165.96 100.00 0
43,008 1.40 92,812.49 165.96 100.00 0
49,152 1.60 92,812.49 165.96 100.00 0
55,296 1.80 92,812.49 165.96 100.00 0
61,440 2.00 92,812.49 165.96 100.00 0
92,160 3.00 92,812.49 165.96 100.00 0
122,880 4.00 92,812.49 165.96 100.00 0
184,320 6.00 92,812.49 165.96 100.00 0
245,760 8.00 92,812.49 165.96 100.00 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

Shared Pool Size(M) SP Size Factr Est LC Size (M) Est LC Mem Obj Est LC Time Saved (s) Est LC Time Saved Factr Est LC Load Time (s) Est LC Load Time Factr Est LC Mem Obj Hits
1,088 0.59 193 16,286 36,662 1.00 594 1.00 35,307,195
1,280 0.69 360 30,898 36,663 1.00 593 1.00 35,307,689
1,472 0.79 387 34,275 36,663 1.00 593 1.00 35,307,698
1,664 0.90 387 34,275 36,663 1.00 593 1.00 35,307,698
1,856 1.00 387 34,275 36,663 1.00 593 1.00 35,307,698
2,048 1.10 387 34,275 36,663 1.00 593 1.00 35,307,698
2,240 1.21 387 34,275 36,663 1.00 593 1.00 35,307,698
2,432 1.31 387 34,275 36,663 1.00 593 1.00 35,307,698
2,624 1.41 387 34,275 36,663 1.00 593 1.00 35,307,698
2,816 1.52 387 34,275 36,663 1.00 593 1.00 35,307,698
3,008 1.62 387 34,275 36,663 1.00 593 1.00 35,307,698
3,200 1.72 387 34,275 36,663 1.00 593 1.00 35,307,698
3,392 1.83 387 34,275 36,663 1.00 593 1.00 35,307,698
3,584 1.93 387 34,275 36,663 1.00 593 1.00 35,307,698
3,776 2.03 387 34,275 36,663 1.00 593 1.00 35,307,698

Back to Advisory Statistics
Back to Top

Java Pool Advisory

Java Pool Size(M) JP Size Factr Est LC Size (M) Est LC Mem Obj Est LC Time Saved (s) Est LC Time Saved Factr Est LC Load Time (s) Est LC Load Time Factr Est LC Mem Obj Hits
16 0.50 9 253 113 1.00 273 1.00 2,632
32 1.00 9 253 113 1.00 273 1.00 2,632
48 1.50 9 253 113 1.00 273 1.00 2,632
64 2.00 9 253 113 1.00 273 1.00 2,632

Back to Advisory Statistics
Back to Top

 

Wait Statistics

Back to Top

Buffer Wait Statistics

Class Waits Total Wait Time (s) Avg Time (ms)
data block 22,842 20 1
undo block 20,606 1 0
undo header 2,171 0 0
segment header 120 0 1
2nd level bmb 37 0 2
1st level bmb 157 0 0
file header block 3 0 3
extent map 1 0 0

Back to Wait Statistics
Back to Top

Enqueue Activity

Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
TX-Transaction (row lock contention) 1,485 67 1,418 67 76 1,131.94
TM-DML 1,440,150 1,440,142 0 6 21 3,503.33
TX-Transaction (index contention) 2,439 2,439 0 2,344 12 5.27
UL-User-defined 22,022 20,315 1,707 77 10 124.55
SQ-Sequence Cache 12,125 12,125 0 1,779 1 0.67
DV-Diana Versioning 5,771 5,771 0 34 1 19.12
RO-Multiple Object Reuse (fast object reuse) 1,267 1,267 0 175 1 3.26
TX-Transaction 308,566 308,565 0 108 0 2.69
CU-Cursor 6,062 6,062 0 8 0 11.25
HW-Segment High Water Mark 71,312 71,312 0 68 0 0.44
FB-Format Block 6,952 6,952 0 27 0 0.00
PS-PX Process Reservation 624 520 104 7 0 0.00
TX-Transaction (allocate ITL entry) 7 7 0 7 0 0.00

Back to Wait Statistics
Back to Top

 

Undo Statistics

Back to Top

Undo Segment Summary

Undo TS# Undo Blocks Num Trans Max Qry Len (s) Max Tx Concurcy Snap TooOld OutOf Space uS/uR/uU/ eS/eR/eU
1 459,924 307,655 1,923 104 0 0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

End Time Undo Blocks Num Trans Max Qry Len (s) Max Tx Concy Snap TooOld OutOf Space uS/uR/uU/ eS/eR/eU
12-Mar 15:54 13,628 28,008 699 98 0 0 0/0/0/0/0/0
12-Mar 15:44 56,769 45,652 546 100 0 0 0/0/0/0/0/0
12-Mar 15:34 145,093 52,481 509 104 0 0 0/0/0/0/0/0
12-Mar 15:24 97,511 67,322 1,923 101 0 0 0/0/0/0/0/0
12-Mar 15:14 38,125 47,494 1,849 101 0 0 0/0/0/0/0/0
12-Mar 15:04 108,798 66,698 1,001 103 0 0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

 

Latch Statistics

Back to Top

Latch Activity

Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss
Consistent RBA 199,269 0.03 0.00 0 0  
FOB s.o list latch 22,419 0.05 0.00 0 0  
In memory undo latch 46,686,234 0.18 0.00 0 733,393 0.00
JOX SGA heap latch 111,803 0.01 0.00 0 0  
JS queue state obj latch 21,810 0.00   0 0  
JS slv state obj latch 3 0.00   0 0  
KTF sga enqueue 0     0 1,226 0.00
KWQMN job cache list latch 477,648 0.03 1.21 1 0  
MQL Tracking Latch 0     0 72 0.00
Memory Management Latch 18,195 0.00   0 1,213 0.00
PL/SQL warning settings 751,561 0.01 0.00 0 0  
SQL memory manager latch 1 0.00   0 1,212 0.00
SQL memory manager workarea list latch 636,121 0.00 0.00 0 0  
SWRF Alerted Metric Element list 99,673 0.00   0 0  
Shared B-Tree 2 0.00   0 0  
active checkpoint queue latch 60,191 0.00 0.00 0 0  
active service list 9,568 0.07 0.00 0 0  
archive control 46 0.00   0 0  
begin backup scn array 340 0.00   0 0  
cache buffer handles 7,403,353 0.17 0.00 0 0  
cache buffers chains 731,503,543 0.18 0.01 21 2,736,965 0.72
cache buffers lru chain 2,545,966 0.24 0.00 0 5,287,499 0.28
channel handle pool latch 84,845 0.02 0.00 0 0  
channel operations parent latch 296,884 0.01 0.10 0 0  
checkpoint queue latch 2,042,890 0.01 0.00 0 747,654 0.01
child cursor hash table 72,925 0.00 0.00 0 0  
client/application info 346,211 0.00 0.00 0 0  
commit callback allocation 32,048 0.00   0 0  
compile environment latch 806,892 0.01 0.00 0 0  
cursor bind value capture 1,419 0.00   0 15,979 0.00
dictionary lookup 375 0.00   0 0  
dml lock allocation 2,876,836 0.08 0.00 0 0  
dummy allocation 4,204 0.90 0.00 0 0  
enqueue hash chains 12,303,393 0.03 0.00 0 2 0.00
enqueues 9,586,739 0.07 0.00 0 0  
error message lists 272 3.31 0.00 0 0  
event group latch 1,126 0.00   0 0  
file cache latch 12,884 0.00   0 0  
global KZLD latch for mem in SGA 2,076 0.00   0 0  
global ctx hash table latch 4,598 0.00   0 0  
hash table column usage latch 1,770 0.00   0 3,853,161 0.01
hash table modification latch 163 0.00   0 0  
job workq parent latch 0     0 2 0.00
job_queue_processes parameter latch 61 0.00   0 0  
ksuosstats global area 245 0.00   0 0  
ktm global data 7,772 0.00   0 0  
lgwr LWN SCN 199,366 0.05 0.00 0 0  
library cache 113,588,422 0.76 0.01 10 1,157 16.34
library cache load lock 4,225 0.02 0.00 0 0  
library cache lock 14,172,376 0.04 0.00 0 0  
library cache lock allocation 374,396 0.00 0.00 0 0  
library cache pin 98,860,922 0.13 0.00 1 0  
library cache pin allocation 383,600 0.00 0.00 0 0  
list of block allocation 81,847 0.00 0.00 0 0  
loader state object freelist 67,524 0.00   0 0  
longop free list parent 67 0.00   0 67 0.00
message pool operations parent latch 85,500 0.00   0 0  
messages 816,795 0.02 0.00 0 0  
mostly latch-free SCN 202,554 1.45 0.01 0 0  
multiblock read objects 16,918 0.04 0.00 0 0  
ncodef allocation latch 59 0.00   0 0  
object queue header heap 123,032 0.00   0 123,476 0.00
object queue header operation 11,578,683 0.01 0.02 0 0  
object stats modification 22,112 0.02 0.00 0 0  
parallel query alloc buffer 2,996 0.93 0.00 0 0  
parallel query stats 176 14.77 0.00 0 0  
parameter list 6,204 0.00   0 0  
parameter table allocation management 4,204 1.45 0.00 0 0  
post/wait queue 145,163 0.04 0.00 0 97,062 0.02
process allocation 2,274 0.09 0.00 0 1,125 0.09
process group creation 2,258 0.04 0.00 0 0  
process queue 1,632 0.37 0.00 0 0  
process queue reference 106,091 0.05 0.00 0 6,954 19.44
query server freelists 1,408 2.77 0.00 0 0  
query server process 8 0.00   0 8 0.00
redo allocation 2,142,058 0.18 0.01 0 24,149,539 0.28
redo copy 0     0 24,166,396 0.03
redo writing 655,763 0.00 0.04 0 0  
row cache objects 43,339,860 0.04 0.00 0 0  
rules engine statistics 9,037 0.02 0.00 0 0  
sequence cache 4,818,335 0.18 0.00 0 0  
session allocation 3,870,127 0.16 0.01 0 0  
session idle bit 21,647,590 0.02 0.04 1 0  
session switching 3,378 0.00   0 0  
session timer 1,226 0.00   0 0  
shared pool 39,002,287 0.70 0.00 0 0  
simulator hash latch 23,669,686 0.00 0.00 0 0  
simulator lru latch 25,586 0.17 0.02 0 267,433 0.65
slave class 2 0.00   0 0  
slave class create 7 0.00   0 0  
sort extent pool 268,424 0.05 0.00 0 0  
state object free list 4 0.00   0 0  
statistics aggregation 84 0.00   0 0  
temp lob duration state obj allocation 109 0.00   0 0  
temporary table state object allocation 2,522 0.04 0.00 0 0  
threshold alerts latch 312 0.00   0 0  
trace latch 1 0.00   0 0  
transaction allocation 28,451 0.00 0.00 0 0  
transaction branch allocation 59 0.00   0 0  
undo global data 66,225,210 0.16 0.00 0 0  
user lock 7,840 0.05 0.00 0 0  

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
cache buffers chains 731,503,543 1,340,414 7,376 1,334,268 4,921 1,217 7
library cache 113,588,422 865,910 6,088 859,874 5,984 52 0
shared pool 39,002,287 273,708 584 273,127 578 3 0
library cache pin 98,860,922 131,878 481 131,398 479 1 0
undo global data 66,225,210 104,287 29 104,258 29 0 0
In memory undo latch 46,686,234 84,209 61 84,151 55 3 0
row cache objects 43,339,860 17,721 10 17,711 10 0 0
cache buffer handles 7,403,353 12,703 6 12,698 4 1 0
sequence cache 4,818,335 8,569 3 8,566 3 0 0
enqueues 9,586,739 6,263 5 6,258 5 0 0
session allocation 3,870,127 6,191 44 6,147 44 0 0
cache buffers lru chain 2,545,966 6,033 26 6,007 26 0 0
library cache lock 14,172,376 5,303 8 5,295 8 0 0
session idle bit 21,647,590 4,714 169 4,624 11 79 0
redo allocation 2,142,058 3,887 56 3,843 32 12 0
enqueue hash chains 12,303,393 3,582 4 3,578 4 0 0
mostly latch-free SCN 202,554 2,932 35 2,897 35 0 0
dml lock allocation 2,876,836 2,384 2 2,382 2 0 0
object queue header operation 11,578,683 1,258 19 1,239 19 0 0
simulator hash latch 23,669,686 245 1 244 1 0 0
KWQMN job cache list latch 477,648 125 151 46 16 55 8
simulator lru latch 25,586 43 1 42 1 0 0
redo writing 655,763 25 1 24 1 0 0
channel operations parent latch 296,884 20 2 19 0 1 0

Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch Name Where NoWait Misses Sleeps Waiter Sleeps
In memory undo latch kturbk 0 34 53
In memory undo latch ktiFlush: child 0 13 7
In memory undo latch kticmt: child 0 7 0
In memory undo latch ktiTxnPoolFree 0 5 0
In memory undo latch ktichg: child 0 2 1
KWQMN job cache list latch kwqmnuji: update job item 0 151 93
cache buffer handles kcbzfs 0 4 1
cache buffer handles kcbzgs 0 2 5
cache buffers chains kcbchg: kslbegin: bufs not pinned 0 4,720 1,320
cache buffers chains kcbgtcr: fast path 0 979 4,468
cache buffers chains kcbgtcr: kslbegin excl 0 397 346
cache buffers chains kcbzgb: scan from tail. nowait 0 312 0
cache buffers chains kcbgcur: kslbegin 0 237 122
cache buffers chains kcbrls: kslbegin 0 168 243
cache buffers chains kcbget: pin buffer 0 130 99
cache buffers chains kcbnew: new latch again 0 125 10
cache buffers chains kcbgtcr: kslbegin shared 0 108 72
cache buffers chains kcbchg: kslbegin: call CR func 0 104 602
cache buffers chains kcbzwb 0 33 8
cache buffers chains kcbgtcr 0 18 0
cache buffers chains kcbnlc 0 10 10
cache buffers chains kcbget: exchange rls 0 9 2
cache buffers chains kcbo_ivd_process 0 6 0
cache buffers chains kcbget: exchange 0 6 4
cache buffers chains kcbcge 0 4 16
cache buffers chains kcbso1: set no access 0 2 2
cache buffers chains kcb_pre_apply: kcbhq61 0 2 10
cache buffers chains kcb_is_private 0 1 21
cache buffers chains kcbzib: finish free bufs 0 1 3
cache buffers chains kcbesc: escalate 0 1 0
cache buffers chains kcbnew_1 0 1 0
cache buffers lru chain kcbzgws 0 20 0
cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 2 0
cache buffers lru chain kcbbxsv: move to being written 0 2 4
cache buffers lru chain kcbzswcu 0 1 0
cache buffers lru chain kcbzgb: posted for free bufs 0 1 14
channel operations parent latch ksrapublish() 0 2 0
dml lock allocation ktaidm 0 2 0
enqueue hash chains ksqgtl3 0 2 4
enqueue hash chains ksqrcl 0 2 0
enqueues ksqgel: create enqueue 0 3 1
enqueues ksqdel 0 2 0
library cache kglpndl: child: before processing 0 1,154 751
library cache kglupc: child 0 1,118 927
library cache kglpnc: child 0 959 1,790
library cache kglpnp: child 0 751 1,385
library cache kglpndl: child: after processing 0 478 2
library cache kglobpn: child: 0 159 476
library cache kglhdiv: child 0 155 0
library cache kgldte: child 0 0 127 205
library cache kglpin: child: heap processing 0 121 157
library cache kglupd: child 0 52 0
library cache kglhdgn: child: 0 40 153
library cache kglpin 0 31 21
library cache kglget: set reserved lock 0 16 38
library cache kglhdgc: child: 0 9 26
library cache kglini: child 0 9 0
library cache kglic 0 6 0
library cache kglidp: child 0 5 0
library cache kglobld 0 3 0
library cache kglpur: child 0 2 0
library cache kglrtl 0 2 4
library cache kgldti: 2child 0 1 0
library cache kglpin: child: KGLMX 0 1 0
library cache kglati 0 1 0
library cache lock kgllkdl: child: no lock handle 0 888 150
library cache lock kgllkdl: child: cleanup 0 5 2
library cache lock kgllkal: child: multiinstance 0 3 5
library cache pin kglpndl 0 149 67
library cache pin kglupc 0 105 62
library cache pin kglpnc: child 0 92 122
library cache pin kglpnp: child 0 87 139
library cache pin kglpnal: child: alloc space 0 48 91
mostly latch-free SCN kcs04 0 32 34
mostly latch-free SCN kcs05 0 3 1
object queue header operation kcbo_ivbo 0 16 1
object queue header operation kcbw_unlink_q 0 1 17
object queue header operation kcbo_htab_size_ck 0 1 3
object queue header operation kcbw_link_q 0 1 1
redo allocation kcrfw_redo_gen: redo allocation 1 0 52 0
redo allocation kcrfw_redo_gen: redo allocation 3 0 4 13
redo writing kcrfw_post: after write 0 1 0
row cache objects kqreqd: reget 0 9 0
row cache objects kqreqd 0 1 0
sequence cache kdnssd 0 3 0
session allocation ksuxds: KSUSFCLC not set 0 12 13
session allocation ksuxds: not user session 0 11 9
session allocation ksucri 0 5 18
session allocation ksuprc 0 4 1
session allocation ksursi 0 3 2
session allocation ksudlp 0 3 0
session allocation ksudlc 0 3 3
session allocation kxfpqidqr 0 3 0
session idle bit ksupuc: set busy 0 99 87
session idle bit ksupuc: clear busy 0 53 69
session idle bit ksuxds 0 17 13
shared pool kghupr1 0 539 561
shared pool kghfre 0 26 3
shared pool kghalo 0 19 19
simulator hash latch kcbsacc: lookup dba 0 1 1
simulator lru latch kcbs_simulate: simulate set 0 1 1
undo global data kturimugur: child 0 12 5
undo global data ktudba: KSLBEGIN 0 11 22
undo global data ktucof: at start 0 5 0
undo global data ktudnx:child 0 1 1

Back to Latch Statistics
Back to Top

 

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Segment Statistics

Back to Top

Segments by Logical Reads

Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
GL APPS_TS_TX_DATA GL_CODE_COMBINATIONS   TABLE 11,740,192 5.82
GL APPS_TS_TX_IDX GL_CODE_COMBINATIONS_U1   INDEX 9,391,152 4.66
SYS SYSTEM COLTYPE$   TABLE 6,232,288 3.09
QP APPS_TS_TX_IDX QP_LIST_LINES_N5   INDEX 5,824,384 2.89
GL APPS_TS_TX_INTERFACE GL_INTERFACE_N2   INDEX 5,202,448 2.58

Back to Segment Statistics
Back to Top

Segments by Physical Reads

Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
APPLSYS APPS_TS_TX_IDX WF_ITEM_ATTRIBUTE_VALUES_PK   INDEX 10,525 7.92
SYS SYSTEM COL$   TABLE 9,941 7.48
GL APPS_TS_TX_DATA GL_JE_LINES   TABLE 8,973 6.75
APPLSYS APPS_TS_TX_DATA WF_ITEM_ATTRIBUTE_VALUES   TABLE 8,439 6.35
HR APPS_TS_TX_IDX PAY_BALANCE_CONTEXT_VALUES_PK   INDEX 8,212 6.18

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits %Total
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_U1   INDEX 3,435 18.17
AR APPS_TS_PAY_DATA RA_CUST_TRX_LINE_GL_DIST_ALL   TABLE 3,213 16.99
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 776 4.10
APPLSYS APPS_TS_TX_DATA WF_ITEM_ACTIVITY_STATUSES WF_ITEM49 TABLE PARTITION 573 3.03
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_FK1   INDEX 572 3.03

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type Row Lock Waits %Total
APPLSYS APPS_TS_TX_DATA FND_CONCURRENT_REQUESTS   TABLE 2,838 38.24
APPLSYS APPS_TS_TX_IDX WF_ITEM_ACTIVITY_STATUSES_N1   INDEX 1,713 23.08
HR APPS_TS_TX_IDX PAY_COSTS_FK2   INDEX 618 8.33
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 281 3.79
HR APPS_TS_PAY_IDX PAY_RUN_RESULT_VALUES_PK   INDEX 250 3.37

Back to Segment Statistics
Back to Top

Segments by ITL Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type ITL Waits %Total
APPLSYS APPS_TS_TX_IDX WF_ITEM_ATTRIBUTE_VALUES_PK   INDEX 3 42.86
HR APPS_TS_TX_IDX PAY_COSTS_FK2   INDEX 1 14.29
MRP APPS_TS_TX_IDX MRP_ATP_SCHEDULE_TEMP_N1   INDEX 1 14.29
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N6   INDEX 1 14.29
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 1 14.29

Back to Segment Statistics
Back to Top

 

Dictionary Cache Stats

Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage
dc_awr_control 110 0.00 0   3 1
dc_constraints 2,799 49.91 0   2,799 913
dc_files 150 50.00 0   0 75
dc_free_extents 15 46.67 5 0.00 15 5
dc_global_oids 1,071,376 0.00 0   0 84
dc_histogram_data 15,256 1.93 0   51 1,507
dc_histogram_defs 167,855 4.31 0   2,602 30,568
dc_object_ids 2,347,772 0.04 0   294 6,681
dc_objects 68,880 2.33 0   2,086 10,185
dc_profiles 2,077 0.00 0   0 1
dc_rollback_segments 7,727,795 0.00 0   0 528
dc_segments 360,705 0.24 0   5,359 8,256
dc_sequences 13,002 0.15 0   13,002 160
dc_table_scns 17,352 0.01 0   0 14
dc_tablespace_quotas 4,871 0.33 0   4,871 50
dc_tablespaces 832,955 0.00 0   0 25
dc_used_extents 5 100.00 0   5 10
dc_usernames 80,906 0.00 0   0 88
dc_users 1,849,283 0.00 0   0 121
outstanding_alerts 126 0.00 0   0 20


Back to Top

 

Library Cache Activity

Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations
BODY 220,346 0.02 8,378,579 0.00 0 0
CLUSTER 288 0.00 351 0.00 0 0
INDEX 3,671 7.06 5,057 7.67 0 0
JAVA DATA 61 0.00 0   0 0
PIPE 15,015 1.86 18,467 1.51 0 0
SQL AREA 987,339 0.46 26,405,988 0.04 1,676 1,134
TABLE/PROCEDURE 136,858 0.58 12,214,627 0.02 74 0
TRIGGER 7,067 0.08 307,956 0.00 0 0


Back to Top

 

SGA Statistics

Back to Top

SGA Memory Summary

SGA regions Size in Bytes
Database Buffers 1,442,840,576
Fixed Size 781,604
Redo Buffers 10,485,760
Variable Size 1,985,221,340

Back to SGA Statistics
Back to Top

SGA breakdown difference

Pool Name Begin value End value % Diff
java free memory 21,099,584 21,099,584 0.00
java joxlod exec hp 11,882,304 11,882,304 0.00
java joxs heap 572,544 572,544 0.00
shared ASH buffers 16,777,216 16,777,216 0.00
shared KGLF Frame Hea 8,312 8,312 0.00
shared KGLS heap 38,662,564 42,398,196 9.66
shared KQR L SO 116,736 137,216 17.54
shared KQR M PO 20,907,344 24,432,256 16.86
shared KQR M SO 537,600 705,024 31.14
shared KQR S PO 2,153,108 2,575,736 19.63
shared KQR S SO 7,680 8,448 10.00
shared KTI-UNDO 22,647,240 22,647,240 0.00
shared PL/SQL DIANA 26,754,912 29,370,044 9.77
shared PL/SQL MPCODE 67,390,480 69,781,588 3.55
shared PLS non-lib hp 10,904 10,904 0.00
shared PX subheap 27,728 27,728 0.00
shared QSMQUTL summar 17,948 4,648 -74.10
shared enqueue 3,244,356 3,244,356 0.00
shared evaluation con 17,292 17,292 0.00
shared event statistics per sess 68,208,000 68,208,000 0.00
shared fixed allocation callback 516 516 0.00
shared free memory 1,041,562,184 710,482,748 -31.79
shared joxlod exec hp 614,828 614,828 0.00
shared joxlod pcod hp 178,036 178,036 0.00
shared joxlod rsrc hp 7,016 7,016 0.00
shared joxs heap 4,220 4,220 0.00
shared library cache 230,106,660 323,433,892 40.56
shared miscellaneous 116,934,060 130,870,216 11.92
shared parameters 1,471,992 1,586,016 7.75
shared partitioning d 510,612 538,428 5.45
shared pl/sql source 13,780 13,780 0.00
shared private strands 21,964,800 21,964,800 0.00
shared repository 1,999,224 2,115,480 5.82
shared rule set evalu 68,960 68,960 0.00
shared sessions 6,888,000 6,888,000 0.00
shared sql area 252,856,812 462,256,580 82.81
shared synonym source 26,224 42,888 63.54
shared table definiti 42,908 51,824 20.78
shared temporary tabl 2,746,952 3,843,744 39.93
shared trigger defini 115,788 124,780 7.77
shared trigger inform 1,508 1,508 0.00
shared trigger source 7,492 7,492 0.00
shared type object de 545,064 707,100 29.73
  buffer_cache 1,442,840,576 1,442,840,576 0.00
  fixed_sga 781,604 781,604 0.00
  log_buffer 10,485,760 10,485,760 0.00

Back to SGA Statistics
Back to Top

 

 

Resource Limit Stats

No data exists for this section of the report.


Back to Top

 

init.ora Parameters

Parameter Name Begin value End value (if different)
O7_DICTIONARY_ACCESSIBILITY FALSE  
_b_tree_bitmap_plans FALSE  
_fast_full_scan_enabled FALSE  
_like_with_bind_as_equality TRUE  
_shared_pool_reserved_min_alloc 4100  
_sort_elimination_cost_ratio 5  
_sqlexec_progression_cost 2147483647  
_system_trig_enabled TRUE  
_trace_files_public TRUE  
aq_tm_processes 1  
background_dump_dest /oracle/dump/bdump  
compatible 10.1.0  
control_files /raw_links/cntrl01, /raw_links/cn  
core_dump_dest /oracle/dump/cdump  
cursor_sharing EXACT  
db_block_checking FALSE  
db_block_checksum TRUE  
db_block_size 8192  
db_cache_size 1442840576  
db_file_multiblock_read_count 8  
db_files 512  
db_name VIS  
disk_asynch_io TRUE  
dml_locks 10000  
enqueue_resources 32000  
ifile /oracle/OraHome_10g/dbs/VIS_oasb-  
java_pool_size 33554432  
job_queue_processes 10  
log_buffer 10485760  
log_checkpoint_timeout 1200  
log_checkpoints_to_alert TRUE  
max_dump_file_size unlimited  
nls_comp binary  
nls_date_format DD-MON-RR  
nls_language american  
nls_length_semantics BYTE  
nls_numeric_characters .,  
nls_sort binary  
nls_territory america  
olap_page_pool_size 4194304  
open_cursors 600  
optimizer_dynamic_sampling 0  
parallel_max_servers 8  
parallel_min_servers 0  
pga_aggregate_target 32212254720  
plsql_code_type native  
plsql_native_library_dir /oracle/OraHome_10g/plsql_nativel  
plsql_optimize_level 2  
processes 1500  
session_cached_cursors 500  
sessions 3000  
shared_pool_reserved_size 193273528  
shared_pool_size 1946157056  
sql_trace FALSE  
timed_statistics TRUE  
undo_management AUTO  
undo_tablespace APPS_UNDOTS1  
user_dump_dest /oracle/dump/udump  
utl_file_dir /usr/tmp, /oracle/OraHome_10g/app  
workarea_size_policy AUTO  


Back to Top

End of Report