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 11-Mar-06 21:15:31 1543 178.7
End Snap: 836 11-Mar-06 22:15:37 1818 194.4
Elapsed:   60.11 (mins)    
DB Time:   309.57 (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,157,371.85 27,213.71
Logical reads: 100,708.36 1,270.36
Block changes: 13,265.62 167.34
Physical reads: 137.57 1.74
Physical writes: 229.01 2.89
User calls: 3,283.27 41.42
Parses: 739.19 9.32
Hard parses: 1.89 0.02
Sorts: 917.91 11.58
Logons: 0.69 0.01
Executes: 6,673.24 84.18
Transactions: 79.28  

% Blocks changed per Read: 13.17 Recursive Call %: 77.21
Rollback per transaction %: 22.00 Rows per Sort: 12.83

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.74
Execute to Parse %: 88.92 Latch Hit %: 99.63
Parse CPU to Parse Elapsd %: 87.85 % Non-Parse CPU: 96.76

Shared Pool Statistics

  Begin End
Memory Usage %: 49.49 68.05
% SQL with executions>1: 86.15 96.19
% Memory for SQL w/exec>1: 79.75 92.64

Top 5 Timed Events

Event Waits Time(s) Percent Total DB Time Wait Class
CPU time   14,407 77.57  
db file sequential read 425,351 2,282 12.28 User I/O
log file sync 110,822 1,483 7.98 Commit
log file parallel write 428,341 448 2.41 System I/O
SQL*Net message to client 12,358,319 76 .41 Network

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 425,351 0 2,282 5 1.49
log file sync 110,822 1 1,483 13 0.39
log file parallel write 428,341 0 448 1 1.50
SQL*Net message to client 12,358,319 0 76 0 43.22
enq: TX - row lock contention 141 1 49 348 0.00
SQL*Net more data from client 611,460 0 48 0 2.14
latch: library cache 12,909 0 39 3 0.05
latch: cache buffers chains 11,436 10,855 34 3 0.04
enq: TM - contention 12 6 26 2,171 0.00
db file scattered read 7,753 0 22 3 0.03
SQL*Net more data to client 512,404 0 20 0 1.79
read by other session 3,792 0 16 4 0.01
library cache pin 772 0 14 18 0.00
enq: TX - index contention 2,379 0 12 5 0.01
db file parallel write 52,693 0 10 0 0.18
buffer busy waits 34,258 0 8 0 0.12
enq: UL - contention 49 2 8 157 0.00
log buffer space 68 0 7 102 0.00
direct path read temp 1,542 0 6 4 0.01
SQL*Net break/reset to client 19,818 0 5 0 0.07
latch free 774 602 5 6 0.00
latch: library cache pin 1,310 0 4 3 0.00
library cache load lock 102 0 3 30 0.00
local write wait 2,541 0 3 1 0.01
LGWR wait for redo copy 5,312 8 2 0 0.02
PX Deq: Signal ACK 116 35 2 13 0.00
enq: SQ - contention 1,830 0 1 1 0.01
latch: shared pool 1,478 0 1 1 0.01
latch: In memory undo latch 299 0 1 5 0.00
direct path write temp 1,819 0 1 1 0.01
control file sequential read 1,291 0 1 1 0.00
control file parallel write 1,606 0 1 1 0.01
rdbms ipc reply 3,069 0 1 0 0.01
enq: RO - fast object reuse 188 0 1 3 0.00
enq: DV - contention 41 0 1 13 0.00
latch: session allocation 92 0 1 6 0.00
latch: undo global data 114 0 0 4 0.00
latch: redo allocation 91 0 0 4 0.00
process startup 12 0 0 33 0.00
switch logfile command 9 0 0 26 0.00
direct path read 250 0 0 1 0.00
latch: library cache lock 40 0 0 4 0.00
enq: TX - contention 99 0 0 1 0.00
enq: HW - contention 48 0 0 2 0.00
log file switch (checkpoint incomplete) 9 0 0 10 0.00
PX Deq Credit: send blkd 416 0 0 0 0.00
latch: cache buffers lru chain 56 0 0 1 0.00
latch: row cache objects 30 0 0 2 0.00
enq: CU - contention 3 0 0 19 0.00
latch: enqueue hash chains 26 0 0 2 0.00
control file single write 132 0 0 0 0.00
log file sequential read 4 0 0 6 0.00
latch: object queue header operation 18 0 0 1 0.00
wait list latch free 2 0 0 11 0.00
enq: TX - allocate ITL entry 10 0 0 2 0.00
row cache lock 48 0 0 0 0.00
latch: cache buffer handles 6 0 0 2 0.00
PX qref latch 2 1 0 6 0.00
enq: FB - contention 31 0 0 0 0.00
direct path write 1,079 0 0 0 0.00
PX Deq: Table Q qref 10 0 0 0 0.00
undo segment extension 783 766 0 0 0.00
enq: PS - contention 13 0 0 0 0.00
buffer deadlock 271 271 0 0 0.00
latch: messages 1 0 0 1 0.00
log file single write 4 0 0 0 0.00
SQL*Net message from client 12,358,325 0 2,979,927 241 43.22
pipe get 11,751 6,796 17,592 1,497 0.04
queue messages 1,186 983 10,059 8,481 0.00
Queue Monitor Wait 120 120 3,516 29,298 0.00
wakeup time manager 1,141 436 3,222 2,824 0.00
PL/SQL lock timer 325 320 3,125 9,617 0.00
PX Idle Wait 1,330 1,200 2,372 1,783 0.00
jobq slave wait 22 21 63 2,886 0.00
PX Deq: Execution Msg 1,908 0 20 10 0.01
class slave wait 3 3 15 4,883 0.00
PX Deq: Execute Reply 1,221 0 1 1 0.00
PX Deq: Msg Fragment 869 0 0 0 0.00
PX Deq: Parse Reply 85 0 0 1 0.00
PX Deq: Table Q Normal 434 0 0 0 0.00
PX Deq: Table Q Sample 28 0 0 1 0.00
PX Deq Credit: need buffer 198 0 0 0 0.00
PX Deq: Join ACK 49 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 428,353 0 448 1 1.50
db file parallel write 52,693 0 10 0 0.18
LGWR wait for redo copy 5,312 8 2 0 0.02
control file parallel write 1,606 0 1 1 0.01
latch: cache buffers chains 155 121 1 6 0.00
control file sequential read 694 0 1 1 0.00
buffer busy waits 560 0 1 1 0.00
latch free 106 93 1 6 0.00
rdbms ipc reply 2,716 0 1 0 0.01
latch: library cache 180 0 0 2 0.00
latch: redo allocation 63 0 0 3 0.00
direct path read 250 0 0 1 0.00
process startup 4 0 0 34 0.00
latch: library cache pin 15 0 0 4 0.00
latch: shared pool 48 0 0 1 0.00
latch: session allocation 8 0 0 4 0.00
control file single write 132 0 0 0 0.00
log file sequential read 4 0 0 6 0.00
latch: In memory undo latch 5 0 0 4 0.00
latch: cache buffers lru chain 16 0 0 1 0.00
latch: library cache lock 6 0 0 2 0.00
latch: undo global data 9 0 0 1 0.00
latch: enqueue hash chains 3 0 0 1 0.00
direct path write 150 0 0 0 0.00
latch: messages 1 0 0 1 0.00
log file single write 4 0 0 0 0.00
latch: object queue header operation 3 0 0 0 0.00
buffer deadlock 1 1 0 0 0.00
rdbms ipc message 266,313 9,808 27,588 104 0.93
Queue Monitor Wait 120 120 3,516 29,298 0.00
smon timer 8,587 0 3,403 396 0.03
wakeup time manager 1,141 436 3,222 2,824 0.00

Back to Wait Events Statistics
Back to Top

Time Model Statistics

Statistic Name Time (seconds) % Total DB Time
DB time 18,574.12 100.00
sql execute elapsed time 14,435.69 77.72
DB CPU 14,407.43 77.57
inbound PL/SQL rpc elapsed time 3,867.30 20.82
PL/SQL execution elapsed time 3,735.53 20.11
background elapsed time 1,150.07 6.19
background cpu time 607.96 3.27
parse time elapsed 533.94 2.87
Java execution elapsed time 355.69 1.91
hard parse elapsed time 301.66 1.62
connection management call elapsed time 17.85 0.10
sequence load elapsed time 14.71 0.08
PL/SQL compilation elapsed time 8.20 0.04
failed parse elapsed time 2.51 0.01
hard parse (sharing criteria) elapsed time 1.16 0.01
hard parse (bind mismatch) elapsed time 1.07 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 14,147.30 11,484.90 295,563 303,560,446
SYS$USERS 4,429.00 2,920.50 134,429 36,901,403
SYS$BACKGROUND 0.00 0.00 63,999 22,709,919

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 869,809 139,272 57,312 9,838 0 0 8,544,406 11,208
SYS$USERS 134,304 83,920 5,277 1,601 9 23 4,934,505 3,184
SYS$BACKGROUND 74,796 9,733 963 222 0 0 1 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
886 877 2 442.83 4.77 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
734 709 4 183.38 3.95 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
487 449 57 8.54 2.62 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
447 426 4 111.68 2.41 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
395 382 26,047 0.02 2.13 6h2tpcxpymqu7 JDBC Thin Client BEGIN :1 := FND_AOLJ_UTIL.is_V...
388 21 561 0.69 2.09 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
353 309 4 88.28 1.90 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
332 297 336 0.99 1.79 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
284 146 5,000 0.06 1.53 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
283 82 561 0.50 1.52 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
254 239 1,000 0.25 1.37 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
238 225 18,247 0.01 1.28 3prk3vg9u0abb JDBC Thin Client BEGIN FND_GLOBAL.APPS_INITIA...
208 165 5,055 0.04 1.12 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...
190 11 953 0.20 1.02 72cqcsuq5wgh0 asn.dashboard.server.DashboardAM SELECT aslrt.meaning as rank_n...

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
877 886 2 438.51 4.77 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
709 734 4 177.30 3.95 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
449 487 57 7.88 2.62 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
426 447 4 106.48 2.41 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
382 395 26,047 0.01 2.13 6h2tpcxpymqu7 JDBC Thin Client BEGIN :1 := FND_AOLJ_UTIL.is_V...
309 353 4 77.13 1.90 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
297 332 336 0.88 1.79 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
239 254 1,000 0.24 1.37 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
225 238 18,247 0.01 1.28 3prk3vg9u0abb JDBC Thin Client BEGIN FND_GLOBAL.APPS_INITIA...
167 172 11,270 0.01 0.93 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...
165 208 5,055 0.03 1.12 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...
146 284 5,000 0.03 1.53 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
82 283 561 0.15 1.52 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
21 388 561 0.04 2.09 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
11 190 953 0.01 1.02 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
26,053,715 4 6,513,428.75 7.17 709.19 733.51 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
22,190,641 2 11,095,320.50 6.11 877.01 885.66 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
22,186,874 4 5,546,718.50 6.11 425.94 446.72 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
12,615,093 1 12,615,093.00 3.47 59.05 140.60 05s9358mm6vrr   begin dbms_feature_usage_inter...
11,857,093 5,000 2,371.42 3.26 145.69 283.81 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
11,179,037 1 11,179,037.00 3.08 45.68 106.65 2vpmsug2fa9rf MMON_SLAVE select atc + ix, NULL, NULL ...
10,997,123 4 2,749,280.75 3.03 308.50 353.12 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
9,240,539 1,000 9,240.54 2.54 239.37 253.66 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
8,495,317 336 25,283.68 2.34 296.55 332.32 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
7,938,284 57 139,268.14 2.19 448.89 486.77 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
6,352,464 9 705,829.33 1.75 83.91 87.89 1du9v021t8m46 ARGLTP insert into gl_interface(creat...
5,806,311 9,999 580.69 1.60 47.44 51.41 546kskr40arst PYUSLV select /*+ ORDERED USE_NL(AA...
5,406,167 11,270 479.70 1.49 166.87 172.05 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...

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
62,740 561 111.84 12.64 20.96 388.43 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
36,770 1 36,770.00 7.41 59.05 140.60 05s9358mm6vrr   begin dbms_feature_usage_inter...
34,255 561 61.06 6.90 82.24 282.83 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
29,816 953 31.29 6.01 10.62 190.17 72cqcsuq5wgh0 asn.dashboard.server.DashboardAM SELECT aslrt.meaning as rank_n...
27,634 5,000 5.53 5.57 145.69 283.81 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
21,815 1 21,815.00 4.40 45.68 106.65 2vpmsug2fa9rf MMON_SLAVE select atc + ix, NULL, NULL ...
16,278 15,170 1.07 3.28 57.78 127.63 3vy5h9xkfv1s5 OEXOETEL INSERT INTO WF_ITEM_ATTRIBUTE_...
9,521 56 170.02 1.92 15.32 21.48 3c086a4sm741b ARXAGE select decode ( UPPER ( : p_or...
8,440 4 2,110.00 1.70 308.50 353.12 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
5,776 5,000 1.16 1.16 86.03 103.65 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
5,498 5,055 1.09 1.11 165.40 208.06 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...
5,290 4 1,322.50 1.07 709.19 733.51 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
5,211 5,000 1.04 1.05 25.07 35.95 3g160axpg863b PYUSLV SELECT fnd_number.number_to_ca...

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
1,005,614 335,308 0.33 0.00 0.00 c4y7jp0q2awbf   SELECT PROFILE_OPTION_VALUE FR...
957,855 852,321 0.89 0.00 0.00 6cjfxb1c4kx16   SELECT PROFILE_OPTION_ID, APP...
636,004 636,000 1.00 0.00 0.00 9dvfguzd15kpv JDBC Thin Client SELECT VALUE FROM V$NLS_PARAME...
227,209 227,209 1.00 0.00 0.00 cgb6kp2umq52a   select t.schema, t.name, t.f...
227,021 227,021 1.00 0.00 0.00 8bsm049u7thjd   select min(next_date) from "AP...
171,980 171,981 1.00 0.00 0.00 cd9mbmqyf4qhp ap.oie.server.WebExpensesAM UPDATE WF_ITEM_ACTIVITY_STATUS...
161,068 161,067 1.00 0.00 0.00 gnhmcahkmrnzs hxc.selfservice.timecard.server.TimecardAM INSERT INTO WF_ITEM_ACTIVITY_S...
138,909 138,906 1.00 0.00 0.00 avc1jqzz04wpr JDBC Thin Client SELECT 'x' FROM DUAL
94,213 94,212 1.00 0.00 0.00 38dj2kuc5m9q3 CSCCCCRC SELECT TO_CHAR(NEXT_DAY(:B1 , ...
81,415 62,156 0.76 0.00 0.00 fmfdkztk8vx23   SELECT CATEGORY, SEVERITY, F...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
227,209 227,209 8.52 cgb6kp2umq52a   select t.schema, t.name, t.f...
227,021 490 8.52 20rc3cmapyvnz   select q_name, state, delay,...
227,021 227,021 8.52 8bsm049u7thjd   select min(next_date) from "AP...
138,909 138,909 5.21 avc1jqzz04wpr JDBC Thin Client SELECT 'x' FROM DUAL
81,415 81,415 3.05 fmfdkztk8vx23   SELECT CATEGORY, SEVERITY, F...
68,049 68,049 2.55 cwyx16zn1hpfb JDBC Thin Client select parameter, value from ...
35,000 35,000 1.31 97mt0g20bcfc3 PYUSLV begin ...
34,164 34,165 1.28 6gnth2mfbs2nq INVTTMTX declare v_ret number := 0...
28,090 28,089 1.05 162ttnv4ubqun OEXOETEL begin OE_DS_PVT.Check_PO_Appr...
25,995 25,995 0.98 8camsgjsr9zya OEXOETEL begin OE_TRANSACTION_TYPES_PK...

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text
1,666,151 1,680 0.09 8ay7m8qubw98j POXBWVRP select * from PO_TAX_LINES_SUM...
1,477,905 92 0.08 azfadzhfxbxj3 ARXTWMAI SELECT ROW_ID, APBA_INACTIVE_D...
1,444,040 55 0.07 2fr5f5xzmffd8 POXPRPOP SELECT decode ( : P_sortby , ...
1,372,636 1 0.07 9trqpr8qyg7xm POXPRPOP SELECT distinct pol . line_num...
1,219,213 1,682 0.06 bf4kcv5v66czb ibuSRDetails.jsp SELECT 'NOTE', '2', b.creat...
1,209,570 4,877 0.06 8q8zmrwmrccz4 OEXOETEL SELECT /*+ ORDERED USE_NL(adj ...
1,116,337 1,680 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;
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 ) )
3g160axpg863b SELECT fnd_number.number_to_canonical(/*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0) ) FROM pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and nvl(PACT.future_process_mode, 'Y') = 'Y' and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and pay_core_utils.include_action_in_payment(BACT.action_type, BAL_ASSACT.assignment_action_id, ASSACT.assignment_action_i d) = 'Y' and NOT EXISTS (SELECT NULL FROM pay_payroll_actions RPACT , pay_assignment_actions RASSACT , pay_action_interlocks RINTLK where ASSACT.assignment_action_id = RINTLK.locked_action_id and RINTLK.locking_action_id = RASSACT.assignment_action_id and RPACT.payroll_action_id = RASSACT.payroll_action_id and RPACT.action_type = 'V')
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;
546kskr40arst select /*+ ORDERED USE_NL(AA PA RR RRV LIV IV EE ET FC EC EL) INDEX(AA PAY_ASSIGNMENT_ACTIONS_PK, PA PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, EL PAY_ELEMENT_LINKS_F_PK, IV PAY_INPUT_VALUES_F_PK, RRV PAY_RUN_RESULT_VALUES_N50, LIV +*/ EL.element_link_id , EL.costable_type , decode(:b0, 0, EL.cost_allocation_keyflex_id, EL2.cost_allocation_keyflex_id) , decode(:b0, 0, EL.balancing_keyflex_id, EL2.balancing_keyflex_id) , EL.element_set_id , decode(IV.uom, 'M', nvl(FC.precision, 3), 3) , nvl(EC.costing_debit_or_credit, 'D') , to_char(RR.run_result_id) , decode(:b2, 'A', decode(EE.entry_type, 'A', EE.target_entry_id, 'R', EE.target_entry_id, RR.source_id), RR.source_id) , EE.creator_type , EE.cost_allocation_keyflex_id , EE.source_id , EE.source_asg_action_id , EE.source_link_id , RR.element_type_id , to_char(RRV.input_value_id) , RRV.result_value , IV.uom , decode(EL.costable_type, 'D', decode(EE.creator_type, 'E', 2, 'EE', 2, 3), 1) , RR.jurisdi ction_code , 'E' , decode(IV.uom, 'M', decode(IV.name, 'Pay Value', 1, 0), 0) , to_char(decode(:b0, 0, PA.date_earned, nvl(RR.end_date, PA.date_earned)), 'YYYY/MM/DD') from pay_assignment_actions AA , pay_payroll_actions PA , pay_run_results RR , pay_run_result_values RRV , pay_link_input_values_f LIV , pay_input_values_f IV , pay_element_entries_f EE , pay_element_types_f ET , fnd_currencies FC , pay_element_classifications EC , pay_element_links_f EL , pay_element_links_f EL2 where (((((((((( (((((((((((((((((AA.assignment_action_id=:b4 and AA.payroll_action_id=PA.payroll_action_id) and RR.assignment_action_id=:b4) and RR.status like 'P%') and RR.source_type not in ('I', 'V')) and RR.element_type_id=ET.element_type_id) and RRV.run_result_id=RR.run_result_id) and IV.input_value_id=RRV.input_value_id) and PA.date_earned between IV.effective_start_date and IV.effective_end_date) and IV.element_type_id=ET.element_type_id) and RRV.result_value is not null ) and (decode(:b6, 'Y', 1, fnd_ number.canonical_to_number(RRV.result_value))+decode(IV.uom, 'M', 0, 0))<>0) and RRV.input_value_id=LIV.input_value_id) and PA.date_earned between LIV.effective_start_date and LIV.effective_end_date) and LIV.costed_flag='Y') and LIV.element_link_id=EL.element_link_id) and RR.source_id=EE.element_entry_id) and nvl(RR.end_date, PA.date_earned) between EE.effective_start_date and EE.effective_end_date) and ((PA.action_type='B' and nvl(EE.BALANCE_ADJ_COST_FLAG, 'N')='Y') or PA.action_type<& gt;'B')) and EE.element_link_id=EL.element_link_id) and PA.date_earned between EL.effective_start_date and EL.effective_end_date) and EL.costable_type<>'N') and (EL.element_type_id+0)=(ET.element_type_id+0)) and PA.date_earned between ET.effective_start_date and ET.effective_end_date) and ET.classification_id=EC.classification_id) and ET.output_currency_code=FC.currency_code) and EL2.element_link_id=EL.element_link_id) and nvl(decode(EL.costable_type, 'D', PA.date_earned, RR.end_date), PA. date_earned) between EL2.effective_start_date and EL2.effective_end_date) union all select /*+ ORDERED USE_NL(AA PA RR RRV LIV IV PERA ET FC EC EL) INDEX(AA PAY_ASSIGNMENT_ACTIONS_PK, PA PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, EL PAY_ELEMENT_LINKS_F_PK, IV PAY_INPUT_VALUES_F_PK, PERA PER_ASSIGNMENTS_F_PK, RRV PA +*/ EL.element_link_id , EL.costable_type , decode(:b0, 0, EL.cost_allocation_keyflex_id, EL2.cost_allocation_keyflex_id) , decode(:b0, 0, EL. balancing_keyflex_id, EL2.balancing_keyflex_id) , EL.element_set_id , decode(IV.uom, 'M', nvl(FC.precision, 3), 3) , nvl(EC.costing_debit_or_credit, 'D') , to_char(RR.run_result_id) , RR.source_id , 'N' , 0 , 0 , 0 , 0 , RR.element_type_id , to_char(RRV.input_value_id) , RRV.result_value , IV.uom , decode(EL.costable_type, 'D', 3, 1) , RR.jurisdiction_code , decode(RR.source_type, 'I', 'I', 'V', 'V', 'D') , decode(IV.uom, 'M', decode(IV.name, 'Pay Value', 1, 0), 0) , to_char(decode(:b0, 0, PA.da te_earned, nvl(RR.end_date, PA.date_earned)), 'YYYY/MM/DD') from pay_assignment_actions AA , pay_payroll_actions PA , pay_run_results RR , pay_run_result_values RRV , pay_link_input_values_f LIV , pay_input_values_f IV , per_all_assignments_f PERA , pay_element_types_f ET , fnd_currencies FC , pay_element_classifications EC , pay_element_links_f EL , pay_element_links_f EL2 where (((((((((((((((((((((((((((((((((((((AA.assignment_action_id=:b4 and AA.payroll_action_id=PA.payroll_action_id) and ( not exists (select null from pay_element_entries_f EE1 where (RR.source_id=EE1.element_entry_id and nvl(RR.end_date, PA.date_earned) between EE1.effective_start_date and EE1.effective_end_date)) or RR.source_type in ('I', 'V'))) and RR.assignment_action_id=:b4) and RR.status like 'P%') and RR.element_type_id=ET.element_type_id) and RRV.run_result_id=RR.run_result_id) and IV.input_value_id=RRV.input_value_id) and PA.date_earned between IV.effective_start_date and IV.effective_end_date) and IV .element_type_id=ET.element_type_id) and RRV.result_value is not null ) and (decode(:b6, 'Y', 1, fnd_number.canonical_to_number(RRV.result_value))+decode(IV.uom, 'M', 0, 0))<>0) and RRV.input_value_id=LIV.input_value_id) and PA.date_earned between LIV.effective_start_date and LIV.effective_end_date) and LIV.costed_flag='Y') and LIV.element_link_id=EL.element_link_id) and RR.element_type_id=ET.element_type_id) and PA.date_earned between ET.effective_start_date and ET.effective_end_date) an d ET.classification_id=EC.classification_id) and ET.output_currency_code=FC.currency_code) and (ET.element_type_id+0)=(EL.element_type_id+0)) and PA.date_earned between EL.effective_start_date and EL.effective_end_date) and EL.costable_type<>'N') and ((PA.action_type='B' and hr_cost.cost_bal_adj(RR.source_id, PA.date_earned)='Y') or PA.action_type<>'B')) and PERA.assignment_id=:b13) and (PERA.business_group_id+0)=(EL.business_group_id+0)) and PA.date_earned between PERA.effective_sta rt_date and PERA.effective_end_date) and (((EL.payroll_id is not null and EL.payroll_id=PERA.payroll_id) or (EL.link_to_all_payrolls_flag='Y' and PERA.payroll_id is not null )) or EL.payroll_id is null )) and (EL.organization_id=PERA.organization_id or EL.organization_id is null )) and (EL.position_id=PERA.position_id or EL.position_id is null )) and (EL.job_id=PERA.job_id or EL.job_id is null )) and (EL.grade_id=PERA.grade_id or EL.grade_id is null )) and (EL.location_id=PERA.location_id or EL.location_id is null )) and (EL.pay_basis_id=PERA.pay_basis_id or EL.pay_basis_id is null )) and (EL.employment_category=PERA.employment_category or EL.employment_category is null )) and (EL.people_group_id is null or exists (select 1 from pay_assignment_link_usages_f PAL where ((PAL.assignment_id=:b13 and PAL.element_link_id=EL.element_link_id) and PA.date_earned between PAL.effective_start_date and PAL.effective_end_date)))) and EL2.element_link_id=EL.element_link_id) and nvl(decode(EL.cos table_type, 'D', PA.date_earned, RR.end_date), PA.date_earned) between EL2.effective_start_date and EL2.effective_end_date) order by 19, 8, 16
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 )
8camsgjsr9zya begin OE_TRANSACTION_TYPES_PKG.Val_Ord_Change( :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;
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
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;
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;
gnhmcahkmrnzs INSERT INTO WF_ITEM_ACTIVITY_STATUSES ( ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY, ACTIVITY_STATUS, ACTIVITY_RESULT_CODE, ASSIGNED_USER, NOTIFICATION_ID, BEGIN_DATE, END_DATE, DUE_DATE, EXECUTION_TIME, OUTBOUND_QUEUE_ID ) VALUES ( :B13 , :B12 , :B11 , :B6 , :B10 , NULL, NULL, :B9 , :B8 , :B7 , DECODE(:B6 , :B5 , :B2 , :B4 , :B2 , :B3 , :B2 , NULL), :B1 )

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,468,124 407.06 5.13
CPU used when call started 1,468,550 407.17 5.14
CR blocks created 1,996,646 553.60 6.98
Cached Commit SCN referenced 32,500 9.01 0.11
Commit SCN cached 137 0.04 0.00
DB time 5,112,383 1,417.47 17.88
DBWR checkpoint buffers written 215,086 59.64 0.75
DBWR checkpoints 180 0.05 0.00
DBWR object drop buffers written 5,008 1.39 0.02
DBWR parallel query checkpoint buffers written 0 0.00 0.00
DBWR revisited being-written buffer 160 0.04 0.00
DBWR transaction table writes 4,071 1.13 0.01
DBWR undo block writes 453,064 125.62 1.58
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,575,712 991.41 12.51
IMU Flushes 80,473 22.31 0.28
IMU Redo allocation size 390,379,736 108,237.67 1,365.34
IMU bind flushes 460 0.13 0.00
IMU commits 149,930 41.57 0.52
IMU contention 10,242 2.84 0.04
IMU ktichg flush 3,886 1.08 0.01
IMU pool not allocated 8 0.00 0.00
IMU recursive-transaction flush 505 0.14 0.00
IMU undo allocation size 790,941,236 219,298.37 2,766.29
IMU- failed to get a private strand 8 0.00 0.00
PX local messages recv'd 5,391 1.49 0.02
PX local messages sent 5,391 1.49 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 12,352,849 3,424.98 43.20
active txn count during cleanout 8,939,830 2,478.68 31.27
application wait time 8,852 2.45 0.03
background checkpoints completed 2 0.00 0.00
background checkpoints started 2 0.00 0.00
background timeouts 10,301 2.86 0.04
branch node splits 261 0.07 0.00
buffer is not pinned count 165,539,903 45,898.01 578.97
buffer is pinned count 397,681,774 110,262.26 1,390.88
bytes received via SQL*Net from client 2,794,453,143 774,797.15 9,773.51
bytes sent via SQL*Net to client 2,468,676,174 684,471.41 8,634.12
calls to get snapshot scn: kcmgss 26,623,797 7,381.78 93.12
calls to kcmgas 3,104,099 860.65 10.86
calls to kcmgcs 1,849,066 512.68 6.47
change write time 34,446 9.55 0.12
cleanout - number of ktugct calls 3,805,693 1,055.18 13.31
cleanouts and rollbacks - consistent read gets 1,878,791 520.92 6.57
cleanouts only - consistent read gets 41,505 11.51 0.15
cluster key scan block gets 8,173,858 2,266.30 28.59
cluster key scans 1,673,348 463.96 5.85
commit cleanout failures: block lost 61,305 17.00 0.21
commit cleanout failures: buffer being written 10 0.00 0.00
commit cleanout failures: callback failure 2,663 0.74 0.01
commit cleanout failures: cannot pin 3,962 1.10 0.01
commit cleanouts 2,086,078 578.39 7.30
commit cleanouts successfully completed 2,018,138 559.55 7.06
commit txn count during cleanout 248,908 69.01 0.87
concurrency wait time 11,680 3.24 0.04
consistent changes 58,332,584 16,173.44 204.02
consistent gets 303,997,376 84,287.08 1,063.22
consistent gets - examination 172,430,626 47,808.55 603.07
consistent gets direct 56 0.02 0.00
consistent gets from cache 303,865,387 84,250.49 1,062.76
current blocks converted for CR 0 0.00 0.00
cursor authentications 1,155 0.32 0.00
data blocks consistent reads - undo records applied 57,214,420 15,863.41 200.11
db block changes 47,844,991 13,265.62 167.34
db block gets 59,226,552 16,421.30 207.14
db block gets direct 2,866 0.79 0.01
db block gets from cache 59,222,037 16,420.05 207.13
deferred (CURRENT) block cleanout applications 402,033 111.47 1.41
dirty buffers inspected 396,365 109.90 1.39
enqueue conversions 4,417 1.22 0.02
enqueue releases 6,946,011 1,925.87 24.29
enqueue requests 6,949,931 1,926.96 24.31
enqueue timeouts 3,585 0.99 0.01
enqueue waits 4,434 1.23 0.02
exchange deadlocks 271 0.08 0.00
execute count 24,068,300 6,673.24 84.18
free buffer inspected 1,454,320 403.23 5.09
free buffer requested 3,500,849 970.65 12.24
heap block compress 1,619,160 448.93 5.66
hot buffers moved to head of LRU 1,009,053 279.77 3.53
immediate (CR) block cleanout applications 1,920,299 532.43 6.72
immediate (CURRENT) block cleanout applications 353,314 97.96 1.24
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 48,942,135 13,569.82 171.17
index scans kdiixs1 37,419,839 10,375.12 130.87
java call heap collected bytes 6,599,792 1,829.88 23.08
java call heap collected count 143,710 39.85 0.50
java call heap gc count 14 0.00 0.00
java call heap live object count 11,198 3.10 0.04
java call heap live object count max 11,474 3.18 0.04
java call heap live size 3,364,080 932.73 11.77
java call heap live size max 3,392,320 940.56 11.86
java call heap object count 32,108 8.90 0.11
java call heap object count max 32,108 8.90 0.11
java call heap total size 4,718,592 1,308.29 16.50
java call heap total size max 4,718,592 1,308.29 16.50
java call heap used size 4,315,088 1,196.41 15.09
java call heap used size max 4,315,600 1,196.55 15.09
leaf node 90-10 splits 8,524 2.36 0.03
leaf node splits 62,784 17.41 0.22
logons cumulative 2,482 0.69 0.01
messages received 274,129 76.01 0.96
messages sent 274,128 76.01 0.96
no buffer to keep pinned count 114 0.03 0.00
no work - consistent read gets 93,354,778 25,883.78 326.51
opened cursors cumulative 2,057,986 570.60 7.20
parse count (failures) 843 0.23 0.00
parse count (hard) 6,815 1.89 0.02
parse count (total) 2,666,020 739.19 9.32
parse time cpu 46,699 12.95 0.16
parse time elapsed 53,155 14.74 0.19
physical read IO requests 434,073 120.35 1.52
physical reads 496,167 137.57 1.74
physical reads cache 474,601 131.59 1.66
physical reads cache prefetch 41,886 11.61 0.15
physical reads direct 19,185 5.32 0.07
physical reads direct temporary tablespace 18,935 5.25 0.07
physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 416,758 115.55 1.46
physical writes 825,966 229.01 2.89
physical writes direct 21,951 6.09 0.08
physical writes direct (lob) 1,170 0.32 0.00
physical writes direct temporary tablespace 20,587 5.71 0.07
physical writes from cache 802,491 222.50 2.81
physical writes non checkpoint 717,000 198.80 2.51
pinned buffers inspected 1,805 0.50 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 12 0.00 0.00
process last non-idle time 3,606 1.00 0.01
queries parallelized 14 0.00 0.00
recursive calls 40,125,049 11,125.17 140.34
recursive cpu usage 769,840 213.45 2.69
redo blocks written 15,866,926 4,399.30 55.49
redo buffer allocation retries 101 0.03 0.00
redo entries 23,655,632 6,558.82 82.73
redo log space requests 35 0.01 0.00
redo log space wait time 10 0.00 0.00
redo ordering marks 388,753 107.79 1.36
redo size 7,780,971,484 2,157,371.85 27,213.71
redo synch time 151,443 41.99 0.53
redo synch writes 135,019 37.44 0.47
redo wastage 76,267,448 21,146.11 266.74
redo write time 46,238 12.82 0.16
redo writer latching time 165 0.05 0.00
redo writes 214,852 59.57 0.75
rollback changes - undo records applied 90,971 25.22 0.32
rollbacks only - consistent read gets 115,091 31.91 0.40
rows fetched via callback 38,031,787 10,544.79 133.02
session connect time 0 0.00 0.00
session cursor cache hits 1,215,047 336.89 4.25
session logical reads 363,223,837 100,708.36 1,270.36
session pga memory 11,002,113,048 3,050,473.72 38,479.56
session pga memory max 11,072,476,532 3,069,982.87 38,725.65
session uga memory 6,653,539,300 1,844,777.15 23,270.55
session uga memory max 12,044,939,824 3,339,610.51 42,126.81
shared hash latch upgrades - no wait 40,667,929 11,275.69 142.23
shared hash latch upgrades - wait 2,061 0.57 0.01
sorts (disk) 1 0.00 0.00
sorts (memory) 3,310,612 917.91 11.58
sorts (rows) 42,477,433 11,777.40 148.56
summed dirty queue length 785,723 217.85 2.75
switch current to new buffer 348,018 96.49 1.22
table fetch by rowid 241,407,824 66,933.34 844.32
table fetch continued row 2,210,554 612.90 7.73
table scan blocks gotten 14,368,313 3,983.79 50.25
table scan rows gotten 407,653,174 113,026.95 1,425.75
table scans (cache partitions) 104 0.03 0.00
table scans (direct read) 0 0.00 0.00
table scans (long tables) 985 0.27 0.00
table scans (rowid ranges) 1,079 0.30 0.00
table scans (short tables) 999,636 277.16 3.50
transaction rollbacks 3,042 0.84 0.01
transaction tables consistent read rollbacks 4 0.00 0.00
transaction tables consistent reads - undo records applied 181 0.05 0.00
undo change vector size 2,939,907,332 815,126.15 10,282.24
user I/O wait time 232,837 64.56 0.81
user calls 11,841,729 3,283.27 41.42
user commits 223,011 61.83 0.78
user rollbacks 62,910 17.44 0.22
workarea executions - onepass 2 0.00 0.00
workarea executions - optimal 1,777,088 492.72 6.22
write clones created in background 2,528 0.70 0.01
write clones created in foreground 160 0.04 0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

Statistic Begin Value End Value
session cursor cache count 53,677 113,820
opened cursors current 275,695 353,408
workarea memory allocated 96 96
logons current 1,543 1,818

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

Statistic Total per Hour
log switches (derived) 2 2.00

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_TS_TX_DATA 216,766 60 5.57 1.05 42,647 12 4,105 1.65
APPS_UNDOTS1 1,376 0 1.99 1.00 242,786 67 16,981 0.20
APPS_TS_TX_IDX 133,824 37 6.18 1.00 69,750 19 6,086 1.23
SYSTEM 39,981 11 3.02 1.59 2,620 1 494 1.70
APPS_TS_TX_INTERFACE 5,634 2 3.05 1.33 17,326 5 61 0.16
APPS_TS_PAY_IDX 8,173 2 2.44 1.00 11,369 3 6,953 0.54
APPS_TS_PAY_DATA 8,378 2 3.07 1.00 5,798 2 2,934 0.50
TEMP 3,313 1 16.34 6.82 10,820 3 57 3.33
SYSAUX 3,742 1 2.49 1.23 7,679 2 0 0.00
APPS_TS_SEED 7,278 2 6.60 1.31 854 0 258 4.34
APPS_TS_QUEUES 4,681 1 1.97 1.01 3,338 1 17 1.76
CTXSYS 824 0 5.83 1.00 1,238 0 0 0.00
APPS_TS_SUMMARY 415 0 3.28 1.00 1,247 0 102 0.00
APPS_TS_ARCHIVE 266 0 6.24 1.00 438 0 2 0.00
APPS_TS_NOLOGGING 51 0 5.69 1.00 37 0 0 0.00
OLAP 16 0 3.13 1.69 2 0 0 0.00
APPS_TS_MEDIA 9 0 8.89 1.00 6 0 0 0.00
ODM 7 0 1.43 1.00 2 0 0 0.00
OWAPUB 5 0 20.00 1.00 2 0 0 0.00
PORTAL 5 0 10.00 1.00 2 0 0 0.00
SYNCSERVER 5 0 6.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 115 0 5.57 1.00 193 0 2 0.00
APPS_TS_ARCHIVE /raw_links/archive2 151 0 6.75 1.00 245 0 0 0.00
APPS_TS_MEDIA /raw_links/media1 3 0 10.00 1.00 2 0 0 0.00
APPS_TS_MEDIA /raw_links/media2 3 0 16.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 46 0 5.43 1.00 35 0 0 0.00
APPS_TS_NOLOGGING /raw_links/nologging2 5 0 8.00 1.00 2 0 0 0.00
APPS_TS_PAY_DATA /raw_links/francis1 8,378 2 3.07 1.00 5,798 2 2,934 0.50
APPS_TS_PAY_IDX /raw_links/francis2 8,173 2 2.44 1.00 11,369 3 6,953 0.54
APPS_TS_QUEUES /raw_links/queues1 1,524 0 1.65 1.01 807 0 2 0.00
APPS_TS_QUEUES /raw_links/queues2 1,583 0 2.12 1.03 998 0 11 1.82
APPS_TS_QUEUES /raw_links/queues3 1,574 0 2.13 1.01 1,533 0 4 2.50
APPS_TS_SEED /raw_links/apps_ts_seed01 2,295 1 8.31 1.00 379 0 3 0.00
APPS_TS_SEED /raw_links/reference1 2,468 1 5.92 1.43 258 0 154 4.55
APPS_TS_SEED /raw_links/reference2 2,515 1 5.70 1.47 217 0 101 4.16
APPS_TS_SUMMARY /raw_links/summary1 55 0 2.18 1.00 221 0 18 0.00
APPS_TS_SUMMARY /raw_links/summary2 69 0 3.04 1.00 226 0 3 0.00
APPS_TS_SUMMARY /raw_links/summary3 137 0 3.07 1.00 245 0 59 0.00
APPS_TS_SUMMARY /raw_links/summary4 49 0 3.67 1.00 153 0 12 0.00
APPS_TS_SUMMARY /raw_links/summary5 57 0 4.21 1.00 182 0 5 0.00
APPS_TS_SUMMARY /raw_links/summary6 48 0 3.96 1.00 220 0 5 0.00
APPS_TS_TX_DATA /raw_links/tx_data1 10,611 3 5.15 1.09 2,427 1 274 1.68
APPS_TS_TX_DATA /raw_links/tx_data10 10,283 3 5.27 1.10 2,479 1 252 2.54
APPS_TS_TX_DATA /raw_links/tx_data11 11,190 3 5.51 1.09 2,565 1 231 1.43
APPS_TS_TX_DATA /raw_links/tx_data12 24,571 7 6.03 1.00 3,640 1 180 0.44
APPS_TS_TX_DATA /raw_links/tx_data13 25,002 7 6.03 1.00 3,227 1 177 0.51
APPS_TS_TX_DATA /raw_links/tx_data14 24,505 7 5.90 1.00 3,183 1 156 0.19
APPS_TS_TX_DATA /raw_links/tx_data15 24,035 7 5.97 1.00 3,099 1 114 0.44
APPS_TS_TX_DATA /raw_links/tx_data2 10,279 3 4.95 1.11 2,329 1 279 2.29
APPS_TS_TX_DATA /raw_links/tx_data3 10,926 3 5.11 1.09 2,924 1 282 1.35
APPS_TS_TX_DATA /raw_links/tx_data4 10,945 3 5.13 1.10 2,869 1 472 1.84
APPS_TS_TX_DATA /raw_links/tx_data5 8,810 2 5.33 1.13 2,820 1 463 1.47
APPS_TS_TX_DATA /raw_links/tx_data6 10,399 3 5.44 1.11 2,800 1 346 1.97
APPS_TS_TX_DATA /raw_links/tx_data7 13,465 4 5.50 1.08 3,071 1 287 1.88
APPS_TS_TX_DATA /raw_links/tx_data8 10,757 3 5.02 1.10 2,542 1 253 2.53
APPS_TS_TX_DATA /raw_links/tx_data9 10,988 3 5.13 1.10 2,672 1 339 2.01
APPS_TS_TX_IDX /raw_links/tx_idx1 7,666 2 5.80 1.00 4,107 1 392 0.87
APPS_TS_TX_IDX /raw_links/tx_idx10 7,609 2 6.18 1.00 3,314 1 356 1.29
APPS_TS_TX_IDX /raw_links/tx_idx11 7,720 2 6.17 1.00 3,382 1 468 1.09
APPS_TS_TX_IDX /raw_links/tx_idx12 12,146 3 6.40 1.00 6,692 2 491 0.63
APPS_TS_TX_IDX /raw_links/tx_idx13 11,815 3 6.18 1.00 6,140 2 226 0.88
APPS_TS_TX_IDX /raw_links/tx_idx14 11,715 3 6.44 1.00 6,055 2 279 0.68
APPS_TS_TX_IDX /raw_links/tx_idx15 11,545 3 6.63 1.00 6,259 2 338 0.83
APPS_TS_TX_IDX /raw_links/tx_idx2 7,187 2 5.79 1.00 4,302 1 295 1.46
APPS_TS_TX_IDX /raw_links/tx_idx3 7,763 2 6.20 1.00 4,180 1 224 1.25
APPS_TS_TX_IDX /raw_links/tx_idx4 8,579 2 6.26 1.00 4,170 1 484 2.52
APPS_TS_TX_IDX /raw_links/tx_idx5 7,678 2 5.95 1.00 4,480 1 346 1.73
APPS_TS_TX_IDX /raw_links/tx_idx6 8,086 2 6.19 1.00 4,998 1 850 0.93
APPS_TS_TX_IDX /raw_links/tx_idx7 8,742 2 5.96 1.00 4,568 1 553 1.25
APPS_TS_TX_IDX /raw_links/tx_idx8 7,755 2 5.82 1.00 3,633 1 398 0.98
APPS_TS_TX_IDX /raw_links/tx_idx9 7,818 2 6.22 1.00 3,470 1 386 2.02
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface 1,034 0 4.17 2.06 1,302 0 24 0.42
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface2 2,350 1 2.68 1.16 8,173 2 20 0.00
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface3 2,250 1 2.92 1.17 7,851 2 17 0.00
APPS_UNDOTS1 /raw_links/undo01 241 0 1.99 1.00 40,299 11 3,059 0.15
APPS_UNDOTS1 /raw_links/undo02 246 0 1.42 1.00 38,185 11 2,712 0.19
APPS_UNDOTS1 /raw_links/undo03 208 0 2.07 1.00 38,787 11 2,571 0.14
APPS_UNDOTS1 /raw_links/undo04 215 0 2.37 1.00 43,031 12 2,766 0.37
APPS_UNDOTS1 /raw_links/undo05 241 0 1.78 1.00 42,115 12 3,089 0.19
APPS_UNDOTS1 /raw_links/undo06 225 0 2.40 1.00 40,369 11 2,784 0.16
CTXSYS /raw_links/ctx1 563 0 6.20 1.00 829 0 0 0.00
CTXSYS /raw_links/ctx2 261 0 5.02 1.00 409 0 0 0.00
ODM /raw_links/odm 7 0 1.43 1.00 2 0 0 0.00
OLAP /raw_links/olap 16 0 3.13 1.69 2 0 0 0.00
OWAPUB /raw_links/owa1 5 0 20.00 1.00 2 0 0 0.00
PORTAL /raw_links/portal 5 0 10.00 1.00 2 0 0 0.00
SYNCSERVER /raw_links/mobile01 5 0 6.00 1.00 2 0 0 0.00
SYSAUX /raw_links/sysaux01 3,742 1 2.49 1.23 7,679 2 0 0.00
SYSTEM /raw_links/sys1 6,020 2 3.01 1.56 293 0 203 1.08
SYSTEM /raw_links/sys2 5,775 2 2.84 1.58 360 0 30 7.67
SYSTEM /raw_links/sys3 5,681 2 2.90 1.60 392 0 12 6.67
SYSTEM /raw_links/sys4 5,966 2 3.25 1.60 553 0 22 3.64
SYSTEM /raw_links/sys5 6,396 2 3.08 1.60 459 0 44 2.27
SYSTEM /raw_links/sys6 5,720 2 2.87 1.57 350 0 23 2.17
SYSTEM /raw_links/sys7 4,423 1 3.23 1.63 213 0 160 0.50
TEMP /raw_links/tmp1 867 0 10.30 2.38 2,608 1 0  
TEMP /raw_links/tmp2 1,321 0 21.32 8.03 4,354 1 44 2.95
TEMP /raw_links/tmp3 1,125 0 15.14 8.81 3,858 1 13 4.62

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 362,905,717 474,633 802,699 0 0 38,050


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 86 43,839 1,654,325 2,476,444 17,510,400 2,476,444  
E 0 38 14,759 302,356 2,483,789 17,510,400 2,483,789  

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.60 5,032,006
D 256 0.19 32,096 5.03 3,831,171
D 384 0.28 48,144 4.23 3,219,914
D 512 0.37 64,192 3.72 2,837,813
D 640 0.47 80,240 3.19 2,427,776
D 768 0.56 96,288 2.54 1,934,222
D 896 0.65 112,336 1.73 1,316,547
D 1,024 0.74 128,384 1.49 1,136,448
D 1,152 0.84 144,432 1.24 946,339
D 1,280 0.93 160,480 1.10 838,732
D 1,376 1.00 172,516 1.00 762,032
D 1,408 1.02 176,528 0.99 751,952
D 1,536 1.12 192,576 0.95 722,985
D 1,664 1.21 208,624 0.91 693,846
D 1,792 1.30 224,672 0.88 671,833
D 1,920 1.40 240,720 0.87 659,652
D 2,048 1.49 256,768 0.85 645,744
D 2,176 1.58 272,816 0.83 631,451
D 2,304 1.67 288,864 0.82 621,348
D 2,432 1.77 304,912 0.81 614,682
D 2,560 1.86 320,960 0.80 609,248

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
99.60 82,877 333

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 22,473 8,654.99 0.09 0.00 100.00 0.00 102,400
E 30,720 20,438 11,134.25 0.09 0.00 100.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,675,319 1,675,319 0 0
64K 128K 5,357 5,357 0 0
128K 256K 313 313 0 0
256K 512K 300 300 0 0
512K 1024K 95,520 95,520 0 0
1M 2M 82 82 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 111,489.99 2,737.43 98.00 567
7,680 0.25 111,489.99 405.08 100.00 63
15,360 0.50 111,489.99 166.42 100.00 0
23,040 0.75 111,489.99 166.42 100.00 0
30,720 1.00 111,489.99 166.42 100.00 0
36,864 1.20 111,489.99 166.42 100.00 0
43,008 1.40 111,489.99 166.42 100.00 0
49,152 1.60 111,489.99 166.42 100.00 0
55,296 1.80 111,489.99 166.42 100.00 0
61,440 2.00 111,489.99 166.42 100.00 0
92,160 3.00 111,489.99 166.42 100.00 0
122,880 4.00 111,489.99 166.42 100.00 0
184,320 6.00 111,489.99 166.42 100.00 0
245,760 8.00 111,489.99 166.42 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,035 27,140 1.00 764 1.00 39,374,527
1,280 0.69 371 30,861 27,143 1.00 761 1.00 39,375,245
1,472 0.79 419 35,830 27,143 1.00 761 1.00 39,375,245
1,664 0.90 419 35,830 27,143 1.00 761 1.00 39,375,245
1,856 1.00 419 35,830 27,143 1.00 761 1.00 39,375,245
2,048 1.10 419 35,830 27,143 1.00 761 1.00 39,375,245
2,240 1.21 419 35,830 27,143 1.00 761 1.00 39,375,245
2,432 1.31 419 35,830 27,143 1.00 761 1.00 39,375,245
2,624 1.41 419 35,830 27,143 1.00 761 1.00 39,375,245
2,816 1.52 419 35,830 27,143 1.00 761 1.00 39,375,245
3,008 1.62 419 35,830 27,143 1.00 761 1.00 39,375,245
3,200 1.72 419 35,830 27,143 1.00 761 1.00 39,375,245
3,392 1.83 419 35,830 27,143 1.00 761 1.00 39,375,245
3,584 1.93 419 35,830 27,143 1.00 761 1.00 39,375,245
3,776 2.03 419 35,830 27,143 1.00 761 1.00 39,375,245

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 264 319 1.00 350 1.00 5,648
32 1.00 9 264 319 1.00 350 1.00 5,648
48 1.50 9 264 319 1.00 350 1.00 5,648
64 2.00 9 264 319 1.00 350 1.00 5,648

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 20,790 21 1
undo block 14,812 2 0
undo header 2,166 1 1
file header block 54 0 4
segment header 85 0 2
2nd level bmb 21 0 3
1st level bmb 118 0 0
bitmap index block 3 0 0
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,873 111 1,762 111 50 453.60
TM-DML 1,469,520 1,469,510 0 6 27 4,446.67
TX-Transaction (index contention) 2,300 2,300 0 2,222 13 5.68
UL-User-defined 22,606 20,900 1,706 41 8 192.93
SQ-Sequence Cache 12,662 12,662 0 1,691 2 0.94
RO-Multiple Object Reuse (fast object reuse) 1,246 1,246 0 174 1 3.97
DV-Diana Versioning 6,536 6,536 0 38 1 13.68
TX-Transaction 330,868 330,868 0 72 0 2.08
HW-Segment High Water Mark 78,479 78,477 2 41 0 3.17
CU-Cursor 6,353 6,353 0 3 0 16.67
TX-Transaction (allocate ITL entry) 11 11 0 7 0 2.86
FB-Format Block 7,045 7,045 0 20 0 0.00
PS-PX Process Reservation 624 520 104 8 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 486,747 363,669 2,648 118 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
11-Mar 22:23 15,733 31,796 654 111 0 0 0/0/0/0/0/0
11-Mar 22:13 17,691 33,325 447 111 0 0 0/0/0/0/0/0
11-Mar 22:03 58,603 48,570 465 118 0 0 0/0/0/0/0/0
11-Mar 21:53 163,260 59,159 657 117 0 0 0/0/0/0/0/0
11-Mar 21:43 82,878 70,419 2,648 116 0 0 0/0/0/0/0/0
11-Mar 21:33 38,870 51,087 2,045 114 0 0 0/0/0/0/0/0
11-Mar 21:23 109,712 69,313 1,554 116 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 214,938 0.03 0.00 0 0  
FOB s.o list latch 24,333 0.01 0.00 0 0  
In memory undo latch 56,383,048 0.92 0.00 1 748,268 0.00
JOX SGA heap latch 113,495 0.01 0.00 0 0  
JS queue state obj latch 21,630 0.00   0 0  
JS slv state obj latch 3 0.00   0 0  
KTF sga enqueue 0     0 1,214 0.00
KWQMN job cache list latch 465,167 0.04 1.38 2 0  
MQL Tracking Latch 0     0 72 0.00
Memory Management Latch 18,030 0.00   0 1,201 0.00
PL/SQL warning settings 842,118 0.02 0.00 0 0  
SQL memory manager latch 2 0.00   0 1,200 0.00
SQL memory manager workarea list latch 712,430 0.00 0.00 0 0  
SWRF Alerted Metric Element list 112,195 0.00   0 0  
Shared B-Tree 2 0.00   0 0  
active checkpoint queue latch 57,945 0.00 0.00 0 0  
active service list 10,113 0.10 0.00 0 0  
archive control 46 0.00   0 0  
begin backup scn array 332 0.00   0 0  
cache buffer handles 7,576,081 0.16 0.00 0 0  
cache buffers chains 779,149,621 0.33 0.00 52 2,834,989 0.83
cache buffers lru chain 2,624,729 0.27 0.01 0 5,367,911 0.32
channel handle pool latch 97,828 0.01 0.00 0 0  
channel operations parent latch 341,776 0.00 0.13 0 0  
checkpoint queue latch 2,041,110 0.01 0.00 0 756,934 0.01
child cursor hash table 74,390 0.00   0 0  
client/application info 395,259 0.00 0.00 0 0  
commit callback allocation 36,485 0.00   0 0  
compile environment latch 885,382 0.01 0.00 0 0  
cursor bind value capture 1,460 0.07 0.00 0 16,107 0.00
dictionary lookup 369 0.00   0 0  
dml lock allocation 2,934,983 0.10 0.01 0 0  
dummy allocation 4,704 1.02 0.00 0 0  
enqueue hash chains 13,905,697 0.03 0.01 0 3 0.00
enqueues 11,164,070 0.08 0.00 0 0  
error message lists 272 6.99 0.00 0 0  
event group latch 1,236 0.00   0 0  
file cache latch 12,937 0.00   0 0  
global KZLD latch for mem in SGA 2,346 0.00   0 0  
global ctx hash table latch 5,410 0.00   0 0  
hash table column usage latch 1,519 0.00   0 4,396,997 0.01
hash table modification latch 194 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 244 0.00   0 0  
ktm global data 9,095 0.00   0 0  
lgwr LWN SCN 215,051 0.04 0.00 0 0  
library cache 124,879,615 0.91 0.01 39 1,142 5.43
library cache load lock 4,304 0.00   0 1 0.00
library cache lock 15,503,543 0.05 0.01 0 0  
library cache lock allocation 420,556 0.01 0.00 0 0  
library cache pin 108,884,663 0.16 0.01 4 2 0.00
library cache pin allocation 429,077 0.00 0.06 0 0  
list of block allocation 86,087 0.00 0.00 0 0  
loader state object freelist 78,080 0.00 0.00 0 0  
longop free list parent 66 0.00   0 66 0.00
message pool operations parent latch 98,742 0.00 0.00 0 0  
messages 889,688 0.02 0.01 0 0  
mostly latch-free SCN 218,894 1.59 0.01 0 0  
multiblock read objects 18,962 0.06 0.09 0 0  
ncodef allocation latch 61 0.00   0 0  
object queue header heap 132,354 0.00   0 131,955 0.00
object queue header operation 12,174,342 0.01 0.01 0 0  
object stats modification 24,407 0.02 0.00 0 0  
parallel query alloc buffer 2,992 0.70 0.00 0 0  
parallel query stats 176 15.34 0.00 0 0  
parameter list 7,011 0.00   0 0  
parameter table allocation management 4,704 1.38 0.00 0 0  
post/wait queue 166,400 0.03 0.00 0 111,377 0.01
process allocation 2,494 0.04 0.00 0 1,236 0.00
process group creation 2,478 0.00   0 0  
process queue 1,632 0.80 0.00 0 0  
process queue reference 108,032 0.08 0.00 0 6,957 8.93
query server freelists 1,408 3.62 0.00 0 0  
query server process 8 0.00   0 8 0.00
redo allocation 2,229,831 0.21 0.02 0 23,651,697 0.35
redo copy 0     0 23,670,728 0.04
redo writing 709,287 0.01 0.00 0 0  
row cache objects 50,362,490 0.05 0.00 0 0  
rules engine statistics 10,216 0.00   0 0  
sequence cache 4,900,391 0.18 0.00 0 0  
session allocation 4,076,680 0.17 0.01 1 0  
session idle bit 24,567,939 0.03 0.07 2 0  
session switching 3,903 0.00   0 0  
session timer 1,216 0.00   0 0  
shared pool 42,360,696 0.84 0.00 1 0  
simulator hash latch 25,673,313 0.00 0.01 0 0  
simulator lru latch 27,531 0.17 0.02 0 289,433 0.84
slave class 4 0.00   0 0  
slave class create 15 6.67 1.00 0 0  
sort extent pool 310,864 0.04 0.01 0 0  
state object free list 6 0.00   0 0  
statistics aggregation 168 0.00   0 0  
temp lob duration state obj allocation 95 0.00   0 0  
temporary table state object allocation 2,843 0.14 0.00 0 0  
threshold alerts latch 312 0.00   0 0  
trace latch 2 0.00   0 0  
transaction allocation 31,999 0.00   0 0  
transaction branch allocation 61 0.00   0 0  
undo global data 78,036,452 0.28 0.00 0 0  
user lock 8,832 0.01 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 779,149,621 2,546,343 11,436 2,537,479 6,381 2,404 76
library cache 124,879,615 1,133,754 12,906 1,120,955 12,693 105 1
In memory undo latch 56,383,048 520,876 299 520,591 271 14 0
shared pool 42,360,696 354,042 1,478 352,579 1,448 15 0
undo global data 78,036,452 218,659 114 218,546 112 1 0
library cache pin 108,884,663 177,543 1,310 176,241 1,294 8 0
row cache objects 50,362,490 23,149 30 23,119 30 0 0
cache buffer handles 7,576,081 12,095 6 12,089 6 0 0
enqueues 11,164,070 8,999 21 8,979 19 1 0
sequence cache 4,900,391 8,724 15 8,709 15 0 0
cache buffers lru chain 2,624,729 7,167 56 7,113 52 2 0
library cache lock 15,503,543 7,010 40 6,970 40 0 0
session allocation 4,076,680 6,813 92 6,721 92 0 0
session idle bit 24,567,939 6,163 405 5,949 26 185 3
enqueue hash chains 13,905,697 4,740 26 4,715 24 1 0
redo allocation 2,229,831 4,641 91 4,565 61 15 0
mostly latch-free SCN 218,894 3,485 49 3,436 49 0 0
dml lock allocation 2,934,983 2,911 17 2,894 17 0 0
object queue header operation 12,174,342 1,515 18 1,497 18 0 0
simulator hash latch 25,673,313 363 2 361 2 0 0
messages 889,688 190 1 189 1 0 0
KWQMN job cache list latch 465,167 187 258 52 24 104 7
sort extent pool 310,864 132 1 131 1 0 0
simulator lru latch 27,531 48 1 47 1 0 0
library cache pin allocation 429,077 18 1 17 1 0 0
channel operations parent latch 341,776 15 2 14 0 1 0
multiblock read objects 18,962 11 1 10 1 0 0
slave class create 15 1 1 0 1 0 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 260 273
In memory undo latch ktiFlush: child 0 30 18
In memory undo latch kticmt: child 0 5 0
In memory undo latch ktichg: child 0 2 6
In memory undo latch ktiTxnPoolFree 0 1 0
In memory undo latch ktbgfc 0 1 2
KWQMN job cache list latch kwqmnuji: update job item 0 258 170
cache buffer handles kcbzfs 0 3 0
cache buffer handles kcbzgs 0 3 6
cache buffers chains kcbchg: kslbegin: bufs not pinned 0 6,503 2,916
cache buffers chains kcbgtcr: fast path 0 1,817 5,309
cache buffers chains kcbchg: kslbegin: call CR func 0 690 1,335
cache buffers chains kcbgtcr: kslbegin excl 0 689 801
cache buffers chains kcbzgb: scan from tail. nowait 0 442 0
cache buffers chains kcbgcur: kslbegin 0 380 301
cache buffers chains kcbrls: kslbegin 0 281 365
cache buffers chains kcbget: pin buffer 0 226 214
cache buffers chains kcbnew: new latch again 0 134 16
cache buffers chains kcbgtcr: kslbegin shared 0 100 44
cache buffers chains kcbzwb 0 45 4
cache buffers chains kcbnlc 0 25 26
cache buffers chains kcbget: exchange rls 0 22 1
cache buffers chains kcbgtcr 0 19 0
cache buffers chains kcbzib: finish free bufs 0 17 4
cache buffers chains kcbget: exchange 0 15 2
cache buffers chains kcbzib: multi-block read: nowait 0 10 0
cache buffers chains kcbbxsv 0 5 4
cache buffers chains kcbcge 0 4 15
cache buffers chains kcbo_ivd_process 0 4 2
cache buffers chains kcb_post_apply: kcbhq62 0 1 3
cache buffers chains kcbesc: escalate 0 1 1
cache buffers lru chain kcbzgws 0 43 0
cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 4 0
cache buffers lru chain kcbzgb: posted for free bufs 0 4 32
cache buffers lru chain kcbo_link_q 0 2 0
cache buffers lru chain kcbbwlru 0 1 2
cache buffers lru chain kcbbic2 0 1 4
cache buffers lru chain kcbbxsv: move to being written 0 1 10
channel operations parent latch ksrchdelete() 0 2 0
dml lock allocation ktaiam 0 9 13
dml lock allocation ktaidm 0 8 4
enqueue hash chains ksqrcl 0 17 5
enqueue hash chains ksqgtl3 0 9 21
enqueues ksqgel: create enqueue 0 8 3
enqueues ksqies 0 8 18
enqueues ksqdel 0 5 0
library cache kglupc: child 0 2,927 1,841
library cache kglpndl: child: before processing 0 2,520 1,481
library cache kglpnc: child 0 2,194 3,697
library cache kglpnp: child 0 1,965 3,246
library cache kglpndl: child: after processing 0 856 13
library cache kglobpn: child: 0 382 1,093
library cache kgldte: child 0 0 275 434
library cache kglpin: child: heap processing 0 273 331
library cache kglhdiv: child 0 153 0
library cache kglhdgn: child: 0 76 251
library cache kglupd: child 0 54 0
library cache kglpin 0 53 39
library cache kglget: set reserved lock 0 37 100
library cache kglic 0 34 0
library cache kglhdgc: child: 0 33 45
library cache kglini: child 0 11 0
library cache kgldti: 2child 0 9 5
library cache kglidp: child 0 4 0
library cache kglpin: child: KGLMX 0 3 0
library cache kglobld 0 2 2
library cache kglpur: child 0 2 0
library cache lock kgllkdl: child: no lock handle 0 1,043 326
library cache lock kgllkal: child: multiinstance 0 20 29
library cache lock kgllkdl: child: cleanup 0 20 11
library cache pin kglpndl 0 410 165
library cache pin kglupc 0 277 184
library cache pin kglpnc: child 0 275 393
library cache pin kglpnp: child 0 264 351
library cache pin kglpnal: child: alloc space 0 84 217
library cache pin allocation kglpnget 0 1 1
messages ksarcv: after wait 0 1 0
mostly latch-free SCN kcs04 0 48 50
mostly latch-free SCN kcs05 0 1 0
multiblock read objects kcbzib: mbr get 0 1 0
object queue header operation kcbo_ivbo 0 16 1
object queue header operation kcbw_link_q 0 1 1
object queue header operation kcbw_unlink_q 0 1 14
redo allocation kcrfw_redo_gen: redo allocation 1 0 82 0
redo allocation kcrfw_redo_gen: redo allocation 3 0 3 28
redo allocation kcrfw_redo_write: before write 0 3 39
redo allocation kcrfw_redo_write: more space 0 3 41
row cache objects kqreqd: reget 0 26 0
row cache objects kqreqd 0 2 0
row cache objects kqrpdl 0 1 0
row cache objects kqrpre: find obj 0 1 41
sequence cache kdnssd 0 14 1
sequence cache kdnnxt: cached seq 0 1 0
session allocation ksuxds: not user session 0 29 31
session allocation ksuxds: KSUSFCLC not set 0 19 7
session allocation ksucri 0 16 35
session allocation ksudlc 0 12 3
session allocation ksuprc 0 9 11
session allocation ksudlp 0 3 1
session allocation ksursi 0 1 1
session allocation ksusin 0 1 0
session allocation kxfpqidqr 0 1 1
session allocation kxfprdp 0 1 2
session idle bit ksupuc: set busy 0 257 171
session idle bit ksupuc: clear busy 0 116 205
session idle bit ksuxds 0 32 29
shared pool kghupr1 0 1,381 1,415
shared pool kghalo 0 55 43
shared pool kghfre 0 40 18
shared pool kghalp 0 1 2
shared pool kghfen: not perm alloc class 0 1 0
simulator hash latch kcbsacc: lookup dba 0 2 2
simulator lru latch kcbs_simulate: simulate set 0 1 1
slave class create ksvcreate 0 1 0
sort extent pool ktstdt: dealloc sort extent 0 1 0
undo global data kturimugur: child 0 67 28
undo global data ktudba: KSLBEGIN 0 31 78
undo global data ktucof: at start 0 12 0
undo global data ktufrbs: child 0 2 0
undo global data ktusmasp: ktugd_tuux 0 1 2
undo global data ktudnx:child 0 1 3

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,843,376 5.54
GL APPS_TS_TX_IDX GL_CODE_COMBINATIONS_U1   INDEX 9,400,448 4.40
QP APPS_TS_TX_IDX QP_LIST_LINES_N5   INDEX 6,718,816 3.14
SYS SYSTEM COLTYPE$   TABLE 6,215,616 2.91
GL APPS_TS_TX_INTERFACE GL_INTERFACE_N2   INDEX 5,373,824 2.51

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 11,910 8.22
SYS SYSTEM COL$   TABLE 9,986 6.89
APPLSYS APPS_TS_TX_DATA WF_ITEM_ATTRIBUTE_VALUES   TABLE 9,038 6.24
GL APPS_TS_TX_DATA GL_JE_LINES   TABLE 9,005 6.22
HR APPS_TS_TX_IDX PAY_BALANCE_CONTEXT_VALUES_PK   INDEX 8,212 5.67

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,865 23.00
AR APPS_TS_PAY_DATA RA_CUST_TRX_LINE_GL_DIST_ALL   TABLE 2,679 15.95
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 881 5.24
APPLSYS APPS_TS_TX_DATA WF_ITEM_ACTIVITY_STATUSES WF_ITEM49 TABLE PARTITION 627 3.73
APPLSYS APPS_TS_TX_IDX WF_ITEM_ACTIVITY_STATUSES_N1   INDEX 564 3.36

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 3,146 42.43
APPLSYS APPS_TS_TX_IDX WF_ITEM_ACTIVITY_STATUSES_N1   INDEX 1,306 17.61
HR APPS_TS_TX_IDX PAY_COSTS_FK2   INDEX 435 5.87
WSH APPS_TS_TX_DATA WSH_TRIP_STOPS   TABLE 380 5.12
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 290 3.91

Back to Segment Statistics
Back to Top

Segments by ITL Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type ITL Waits %Total
HR APPS_TS_TX_IDX PAY_COSTS_FK2   INDEX 2 28.57
APPLSYS APPS_TS_TX_IDX WF_ITEM_ATTRIBUTE_VALUES_PK   INDEX 1 14.29
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_FK2   INDEX 1 14.29
APPLSYS APPS_TS_TX_IDX WF_ITEM_ACTIVITY_STATUSES_N1   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 113 0.00 0   5 1
dc_constraints 2,751 49.91 0   2,751 961
dc_files 150 50.00 0   0 75
dc_free_extents 15 46.67 5 0.00 15 5
dc_global_oids 1,241,880 0.00 0   0 84
dc_histogram_data 15,430 2.05 0   51 1,537
dc_histogram_defs 178,739 4.08 0   2,571 30,817
dc_object_ids 2,682,530 0.03 0   290 6,645
dc_objects 74,912 2.17 0   2,052 10,314
dc_profiles 2,346 0.00 0   0 1
dc_rollback_segments 9,185,023 0.00 0   3 536
dc_segments 362,811 0.23 0   5,488 8,276
dc_sequences 13,734 0.15 0   13,734 161
dc_table_scns 19,064 0.01 0   0 14
dc_tablespace_quotas 4,997 0.32 0   4,997 50
dc_tablespaces 929,981 0.00 0   0 25
dc_used_extents 5 100.00 0   5 11
dc_usernames 89,910 0.00 0   0 88
dc_users 2,122,772 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 248,222 0.02 9,384,931 0.00 0 0
CLUSTER 285 0.00 348 0.00 0 0
INDEX 4,173 6.33 5,551 7.04 0 0
JAVA DATA 61 0.00 0   0 0
PIPE 16,569 2.00 21,363 1.55 0 0
SQL AREA 993,335 0.46 28,794,017 0.04 1,806 1,116
TABLE/PROCEDURE 154,104 0.52 13,525,643 0.02 73 0
TRIGGER 7,825 0.08 322,896 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 9,580 9,580 0.00
shared KGLS heap 38,698,424 42,484,216 9.78
shared KQR L SO 116,736 137,216 17.54
shared KQR M PO 21,035,592 24,604,512 16.97
shared KQR M SO 541,724 718,404 32.61
shared KQR S PO 2,177,612 2,595,720 19.20
shared KQR S SO 7,700 8,468 9.97
shared KTI-UNDO 22,647,240 22,647,240 0.00
shared PL/SQL DIANA 26,798,372 29,415,488 9.77
shared PL/SQL MPCODE 67,413,936 69,850,412 3.61
shared PLS non-lib hp 10,904 10,904 0.00
shared PX subheap 38,724 38,724 0.00
shared QSMQUTL summar 25,448 14,276 -43.90
shared enqueue 3,244,356 3,244,356 0.00
shared evaluation con 17,408 17,408 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 983,019,324 621,887,420 -36.74
shared joxlod exec hp 613,280 613,280 0.00
shared joxlod pcod hp 171,616 171,616 0.00
shared joxlod rsrc hp 7,216 7,216 0.00
shared joxs heap 4,220 4,220 0.00
shared library cache 258,669,244 356,474,288 37.81
shared miscellaneous 124,914,252 140,856,432 12.76
shared parameters 1,480,660 1,596,548 7.83
shared partitioning d 525,580 569,536 8.36
shared pl/sql source 13,864 13,864 0.00
shared private strands 21,964,800 21,964,800 0.00
shared repository 2,008,644 2,123,924 5.74
shared rule set evalu 69,084 69,084 0.00
shared sessions 6,888,000 6,888,000 0.00
shared sql area 274,065,840 506,765,836 84.91
shared synonym source 28,636 46,596 62.72
shared table definiti 43,936 52,248 18.92
shared temporary tabl 3,233,964 4,434,092 37.11
shared trigger defini 112,796 120,900 7.18
shared trigger inform 1,508 1,508 0.00
shared trigger source 7,440 7,440 0.00
shared type object de 543,664 705,552 29.78
  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