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 01:27:31 1732 177.7
End Snap: 836 11-Mar-06 02:27:36 2038 196.3
Elapsed:   60.08 (mins)    
DB Time:   381.35 (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,389,415.27 27,164.76
Logical reads: 108,397.97 1,232.35
Block changes: 15,015.45 170.71
Physical reads: 158.28 1.80
Physical writes: 247.04 2.81
User calls: 3,714.26 42.23
Parses: 797.76 9.07
Hard parses: 1.97 0.02
Sorts: 1,014.79 11.54
Logons: 0.77 0.01
Executes: 7,300.46 83.00
Transactions: 87.96  

% Blocks changed per Read: 13.85 Recursive Call %: 76.60
Rollback per transaction %: 22.64 Rows per Sort: 12.46

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.86 In-memory Sort %: 100.00
Library Hit %: 99.98 Soft Parse %: 99.75
Execute to Parse %: 89.07 Latch Hit %: 99.57
Parse CPU to Parse Elapsd %: 84.64 % Non-Parse CPU: 96.72

Shared Pool Statistics

  Begin End
Memory Usage %: 51.28 71.99
% SQL with executions>1: 85.40 93.22
% Memory for SQL w/exec>1: 78.16 85.79

Top 5 Timed Events

Event Waits Time(s) Percent Total DB Time Wait Class
CPU time   17,107 74.76  
db file sequential read 498,214 2,773 12.12 User I/O
log file sync 129,031 2,010 8.78 Commit
log file parallel write 445,391 517 2.26 System I/O
latch: library cache 26,609 141 .62 Concurrency

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 498,214 0 2,773 6 1.57
log file sync 129,031 3 2,010 16 0.41
log file parallel write 445,391 0 517 1 1.40
latch: library cache 26,609 0 141 5 0.08
SQL*Net message to client 13,967,955 0 91 0 44.05
latch: cache buffers chains 18,562 17,675 69 4 0.06
enq: TX - row lock contention 211 1 65 309 0.00
SQL*Net more data from client 692,891 0 62 0 2.19
db file scattered read 7,858 0 26 3 0.02
SQL*Net more data to client 553,925 0 24 0 1.75
enq: TM - contention 10 5 21 2,125 0.00
buffer busy waits 36,320 0 17 0 0.11
latch: library cache pin 2,776 0 16 6 0.01
library cache pin 714 0 16 23 0.00
read by other session 3,764 0 16 4 0.01
enq: TX - index contention 2,441 0 14 6 0.01
db file parallel write 49,949 0 13 0 0.16
enq: UL - contention 103 2 9 92 0.00
SQL*Net break/reset to client 22,058 0 9 0 0.07
latch free 1,432 1,133 9 6 0.00
log buffer space 54 0 6 110 0.00
direct path read temp 1,528 0 6 4 0.00
LGWR wait for redo copy 7,908 49 5 1 0.02
latch: shared pool 3,131 0 4 1 0.01
local write wait 2,810 0 4 1 0.01
latch: In memory undo latch 520 0 3 6 0.00
library cache load lock 82 0 3 32 0.00
enq: SQ - contention 1,697 0 2 1 0.01
control file parallel write 1,605 0 2 1 0.01
latch: undo global data 229 0 2 7 0.00
PX Deq: Signal ACK 105 39 2 15 0.00
control file sequential read 1,285 0 2 1 0.00
latch: redo allocation 325 0 1 4 0.00
direct path write temp 1,653 0 1 1 0.01
rdbms ipc reply 1,904 0 1 1 0.01
enq: RO - fast object reuse 211 0 1 4 0.00
process startup 13 0 1 53 0.00
latch: cache buffers lru chain 139 0 1 5 0.00
latch: session allocation 201 0 1 3 0.00
latch: row cache objects 94 0 1 6 0.00
latch: enqueue hash chains 57 0 1 9 0.00
latch: library cache lock 53 0 0 8 0.00
enq: TX - contention 91 0 0 2 0.00
PX qref latch 43 11 0 5 0.00
enq: DV - contention 29 0 0 7 0.00
PX Deq Credit: send blkd 388 0 0 0 0.00
log file switch (checkpoint incomplete) 12 0 0 15 0.00
latch: checkpoint queue latch 6 0 0 26 0.00
latch: object queue header operation 47 0 0 3 0.00
direct path read 250 0 0 1 0.00
enq: HW - contention 40 0 0 3 0.00
switch logfile command 6 0 0 19 0.00
wait list latch free 8 0 0 14 0.00
control file single write 132 0 0 1 0.00
enq: TX - allocate ITL entry 9 0 0 8 0.00
kksfbc child completion 1 1 0 50 0.00
row cache lock 30 0 0 1 0.00
enq: CU - contention 6 0 0 4 0.00
log file sequential read 4 0 0 5 0.00
direct path write 1,193 0 0 0 0.00
latch: cache buffer handles 11 0 0 1 0.00
undo segment extension 2,479 2,464 0 0 0.01
enq: FB - contention 20 0 0 0 0.00
latch: messages 1 0 0 4 0.00
enq: PS - contention 21 0 0 0 0.00
PX Deq: Table Q qref 12 0 0 0 0.00
buffer deadlock 307 305 0 0 0.00
log file single write 4 0 0 0 0.00
latch: redo writing 2 0 0 0 0.00
SQL*Net message from client 13,967,962 0 3,222,737 231 44.05
pipe get 12,317 6,708 17,557 1,425 0.04
queue messages 1,207 970 10,112 8,378 0.00
PL/SQL lock timer 375 371 3,623 9,662 0.00
Queue Monitor Wait 121 121 3,535 29,211 0.00
wakeup time manager 1,140 541 3,170 2,780 0.00
PX Idle Wait 1,327 1,200 2,373 1,788 0.00
jobq slave wait 42 41 122 2,910 0.00
PX Deq: Execution Msg 1,954 0 23 12 0.01
class slave wait 1 1 5 4,884 0.00
PX Deq: Execute Reply 1,184 0 1 1 0.00
PX Deq: Table Q Normal 517 0 0 1 0.00
PX Deq: Msg Fragment 994 0 0 0 0.00
PX Deq: Parse Reply 75 0 0 1 0.00
PX Deq Credit: need buffer 188 0 0 0 0.00
PX Deq: Table Q Sample 28 0 0 1 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 445,389 0 517 1 1.40
db file parallel write 49,949 0 13 0 0.16
db file sequential read 4,951 0 9 2 0.02
LGWR wait for redo copy 7,908 49 5 1 0.02
latch: cache buffers chains 389 308 2 6 0.00
buffer busy waits 820 0 2 3 0.00
control file parallel write 1,605 0 2 1 0.01
control file sequential read 694 0 1 1 0.00
latch free 163 141 1 6 0.00
latch: library cache 336 0 1 3 0.00
rdbms ipc reply 1,614 0 1 0 0.01
latch: redo allocation 218 0 1 3 0.00
process startup 5 0 0 56 0.00
log buffer space 2 0 0 94 0.00
latch: library cache pin 25 0 0 7 0.00
latch: shared pool 95 0 0 2 0.00
direct path read 250 0 0 1 0.00
latch: library cache lock 7 0 0 17 0.00
latch: cache buffers lru chain 28 0 0 4 0.00
control file single write 132 0 0 1 0.00
latch: enqueue hash chains 9 0 0 10 0.00
latch: session allocation 14 0 0 6 0.00
latch: undo global data 16 0 0 4 0.00
latch: In memory undo latch 8 0 0 8 0.00
log file sequential read 4 0 0 5 0.00
latch: checkpoint queue latch 3 0 0 5 0.00
latch: object queue header operation 5 0 0 2 0.00
direct path write 150 0 0 0 0.00
log file single write 4 0 0 0 0.00
latch: redo writing 2 0 0 0 0.00
buffer deadlock 1 1 0 0 0.00
rdbms ipc message 288,001 9,683 26,811 93 0.91
Queue Monitor Wait 121 121 3,535 29,211 0.00
smon timer 9,990 0 3,386 339 0.03
wakeup time manager 1,140 541 3,170 2,780 0.00

Back to Wait Events Statistics
Back to Top

Time Model Statistics

Statistic Name Time (seconds) % Total DB Time
DB time 22,881.01 100.00
sql execute elapsed time 17,459.98 76.31
DB CPU 17,106.71 74.76
inbound PL/SQL rpc elapsed time 4,914.19 21.48
PL/SQL execution elapsed time 4,610.43 20.15
background elapsed time 1,580.03 6.91
background cpu time 745.96 3.26
parse time elapsed 668.45 2.92
Java execution elapsed time 447.34 1.96
hard parse elapsed time 372.30 1.63
connection management call elapsed time 22.39 0.10
sequence load elapsed time 18.64 0.08
PL/SQL compilation elapsed time 8.65 0.04
failed parse elapsed time 3.18 0.01
hard parse (sharing criteria) elapsed time 1.26 0.01
hard parse (bind mismatch) elapsed time 1.15 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 17,457.30 13,624.90 323,659 320,709,711
SYS$USERS 5,424.90 3,480.90 150,875 41,605,840
SYS$BACKGROUND 0.00 0.00 93,875 28,316,454

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 1,946,181 593,934 76,755 22,163 0 0 9,638,720 13,831
SYS$USERS 155,633 96,962 9,602 5,061 6 12 5,572,677 3,933
SYS$BACKGROUND 78,455 21,161 1,747 699 0 0 2 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
1,016 977 2 508.05 4.44 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
802 756 4 200.44 3.50 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
734 565 62 11.84 3.21 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
514 470 4 128.49 2.25 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
482 456 29,851 0.02 2.11 6h2tpcxpymqu7 JDBC Thin Client BEGIN :1 := FND_AOLJ_UTIL.is_V...
448 27 640 0.70 1.96 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
440 358 384 1.15 1.92 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
385 333 4 96.14 1.68 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
341 103 640 0.53 1.49 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
302 271 1,000 0.30 1.32 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
289 268 20,926 0.01 1.26 3prk3vg9u0abb JDBC Thin Client BEGIN FND_GLOBAL.APPS_INITIA...
283 72 1 283.42 1.24 05s9358mm6vrr   begin dbms_feature_usage_inter...
280 141 4,563 0.06 1.22 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
257 204 5,762 0.04 1.12 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...

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
977 1,016 2 488.42 4.44 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
756 802 4 189.07 3.50 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
565 734 62 9.12 3.21 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
470 514 4 117.38 2.25 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
456 482 29,851 0.02 2.11 6h2tpcxpymqu7 JDBC Thin Client BEGIN :1 := FND_AOLJ_UTIL.is_V...
358 440 384 0.93 1.92 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
333 385 4 83.18 1.68 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
271 302 1,000 0.27 1.32 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
268 289 20,926 0.01 1.26 3prk3vg9u0abb JDBC Thin Client BEGIN FND_GLOBAL.APPS_INITIA...
204 257 5,762 0.04 1.12 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...
141 280 4,563 0.03 1.22 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
103 341 640 0.16 1.49 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
72 283 1 71.69 1.24 05s9358mm6vrr   begin dbms_feature_usage_inter...
27 448 640 0.04 1.96 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
25,537,589 4 6,384,397.25 6.54 756.29 801.74 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
23,573,567 2 11,786,783.50 6.03 976.84 1,016.10 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
21,320,014 4 5,330,003.50 5.46 469.53 513.94 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
12,614,653 1 12,614,653.00 3.23 71.69 283.42 05s9358mm6vrr   begin dbms_feature_usage_inter...
11,436,705 4 2,859,176.25 2.93 332.71 384.55 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
10,819,735 4,563 2,371.19 2.77 140.95 279.76 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
9,274,140 1,000 9,274.14 2.37 270.88 302.10 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
8,689,467 62 140,152.69 2.22 565.20 734.01 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
8,395,215 384 21,862.54 2.15 357.92 439.75 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
6,387,377 11,035 578.83 1.63 191.52 203.24 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...
5,806,205 10,000 580.62 1.49 51.96 56.03 546kskr40arst PYUSLV select /*+ ORDERED USE_NL(AA...
5,246,134 1 5,246,134.00 1.34 61.87 103.11 73z18fnnbb7dw   delete from sys.wri$_optstat_h...
4,879,799 8 609,974.88 1.25 78.95 88.22 1du9v021t8m46 ARGLTP insert into gl_interface(creat...
4,563,579 266 17,156.31 1.17 45.95 54.71 c1a0aqnhptp0c GLLEZL update GL_INTERFACE set statu...
4,006,294 29,851 134.21 1.03 455.57 481.79 6h2tpcxpymqu7 JDBC Thin Client BEGIN :1 := FND_AOLJ_UTIL.is_V...

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
71,353 640 111.49 12.51 26.80 448.35 3dssuqfjsynrk asn.lead.server.LeadUwqAM SELECT count(1) FROM (SELECT *...
43,563 1 43,563.00 7.64 71.69 283.42 05s9358mm6vrr   begin dbms_feature_usage_inter...
40,115 640 62.68 7.03 102.73 340.96 8c8cf4vtv4tx2 asn.lead.server.LeadUwqAM SELECT * FROM ( SELECT LeadEO....
32,286 1,089 29.65 5.66 13.18 211.02 72cqcsuq5wgh0 asn.dashboard.server.DashboardAM SELECT aslrt.meaning as rank_n...
25,071 4,563 5.49 4.39 140.95 279.76 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
18,980 1 18,980.00 3.33 45.99 54.00 8n2cx11wsxuqv GLLEZL select int.rowid , decode(i...
18,848 1 18,848.00 3.30 61.87 103.11 73z18fnnbb7dw   delete from sys.wri$_optstat_h...
11,378 13,625 0.84 1.99 47.28 97.75 3vy5h9xkfv1s5 hxc.selfservice.timecard.server.TimecardAM INSERT INTO WF_ITEM_ATTRIBUTE_...
10,005 65 153.92 1.75 18.05 24.36 3c086a4sm741b ARXAGE select decode ( UPPER ( : p_or...
8,974 62 144.74 1.57 565.20 734.01 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
8,328 4 2,082.00 1.46 332.71 384.55 8c30hkqv9n7cr OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
7,381 5,000 1.48 1.29 84.87 99.59 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
6,809 5,000 1.36 1.19 25.07 33.09 3g160axpg863b PYUSLV SELECT fnd_number.number_to_ca...
6,682 9,126 0.73 1.17 27.06 63.19 fw8fr3nddyd26 PYUSLV insert into pay_run_balances(r...
6,223 5,762 1.08 1.09 203.86 257.20 1q78dwnrrkf6c asn.lead.server.LeadUwqAM BEGIN ASN_SALES_PVT.Lead_Proce...
6,095 4 1,523.75 1.07 756.29 801.74 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...

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,154,334 383,795 0.33 0.00 0.00 c4y7jp0q2awbf   SELECT PROFILE_OPTION_VALUE FR...
1,101,520 980,034 0.89 0.00 0.00 6cjfxb1c4kx16   SELECT PROFILE_OPTION_ID, APP...
731,729 731,711 1.00 0.00 0.00 9dvfguzd15kpv JDBC Thin Client SELECT VALUE FROM V$NLS_PARAME...
281,026 145,500 0.52 0.00 0.00 66tmsr3446uqn ap.oie.server.WebExpensesAM SELECT WIAS.ACTIVITY_STATUS, ...
220,074 220,074 1.00 0.00 0.00 cgb6kp2umq52a   select t.schema, t.name, t.f...
219,872 219,872 1.00 0.00 0.00 8bsm049u7thjd   select min(next_date) from "AP...
160,013 160,012 1.00 0.00 0.00 cd9mbmqyf4qhp ap.oie.server.WebExpensesAM UPDATE WF_ITEM_ACTIVITY_STATUS...
158,657 158,655 1.00 0.00 0.00 avc1jqzz04wpr JDBC Thin Client SELECT 'x' FROM DUAL
149,465 149,465 1.00 0.00 0.00 gnhmcahkmrnzs hxc.selfservice.timecard.server.TimecardAM INSERT INTO WF_ITEM_ACTIVITY_S...
107,576 107,575 1.00 0.00 0.00 38dj2kuc5m9q3 CSCCCCRC SELECT TO_CHAR(NEXT_DAY(:B1 , ...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
220,074 220,074 7.65 cgb6kp2umq52a   select t.schema, t.name, t.f...
219,872 560 7.65 20rc3cmapyvnz   select q_name, state, delay,...
219,872 219,872 7.65 8bsm049u7thjd   select min(next_date) from "AP...
158,655 158,657 5.52 avc1jqzz04wpr JDBC Thin Client SELECT 'x' FROM DUAL
90,780 90,780 3.16 fmfdkztk8vx23   SELECT CATEGORY, SEVERITY, F...
77,757 77,758 2.70 cwyx16zn1hpfb JDBC Thin Client select parameter, value from ...
36,402 36,402 1.27 6gnth2mfbs2nq INVTTMTX declare v_ret number := 0...
32,109 32,108 1.12 162ttnv4ubqun OEXOETEL begin OE_DS_PVT.Check_PO_Appr...
31,943 31,943 1.11 97mt0g20bcfc3 PYUSLV begin ...
29,708 29,707 1.03 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,135 1,925 0.09 8ay7m8qubw98j POXBWVRP select * from PO_TAX_LINES_SUM...
1,478,393 107 0.08 azfadzhfxbxj3 ARXTWMAI SELECT ROW_ID, APBA_INACTIVE_D...
1,443,636 63 0.07 2fr5f5xzmffd8 POXPRPOP SELECT decode ( : P_sortby , ...
1,220,517 1,922 0.06 bf4kcv5v66czb ibuSRDetails.jsp SELECT 'NOTE', '2', b.creat...
1,208,994 5,573 0.06 8q8zmrwmrccz4 OEXOETEL SELECT /*+ ORDERED USE_NL(adj ...
1,118,061 1,921 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
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
66tmsr3446uqn SELECT WIAS.ACTIVITY_STATUS, WIAS.ACTIVITY_RESULT_CODE, WIAS.ASSIGNED_USER, WIAS.NOTIFICATION_ID, WIAS.BEGIN_DATE, WIAS.END_DATE, WIAS.DUE_DATE, WIAS.ERROR_NAME, WIAS.ERROR_MESSAGE, WIAS.ERROR_STACK FROM WF_ITEM_ACTIVITY_STATUSES WIAS WHERE WIAS.ITEM_TYPE = :B3 AND WIAS.ITEM_KEY = :B2 AND WIAS.PROCESS_ACTIVITY = :B1
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
73z18fnnbb7dw delete from sys.wri$_optstat_histhead_history where savtime < :1
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;
8n2cx11wsxuqv select int.rowid , decode(int.SEGMENT1|| int.SEGMENT2|| int.SEGMENT3|| int.SEGMENT4|| int.SEGMENT5 , '', replace(ccid_cc.SEGMENT3, '-', ' ') || '-' || replace(ccid_cc.SEGMENT1, '-', ' ') || '-' || replace(ccid_cc.SEGMENT2, '-', ' ') || '-' || replace(ccid_cc.SEGMENT4, '-', ' ') || '-' || replace(ccid_cc.SEGMENT5, '-', ' ') , replace(int.SEGMENT3, '-', ' ') || '-' || replace(int.SEGMENT1, '-', ' ') || '-' || replace(int.SEGMENT2, '-', ' ') || '-' || replace(int.SEGMENT4, '-', ' ') || '-' | | replace(int.SEGMENT5, '-', ' ') ) flexfield , nvl(flex_cc.code_combination_id, nvl(int.code_combination_id, -4)) , decode(int.SEGMENT1|| int.SEGMENT2|| int.SEGMENT3|| int.SEGMENT4|| int.SEGMENT5 , '', decode(ccid_cc.code_combination_id, null, decode(int.code_combination_id, null, -4, -5), decode(sign(nvl(ccid_cc.start_date_active, int.accounting_date-1) - int.accounting_date), 1, -1, decode(sign(nvl(ccid_cc.end_date_active, int.accounting_date +1) - int.accounting_date), -1, -1, 0)) + decode(ccid_cc.enabled_flag, 'N', -10, 0) + decode(ccid_cc.summary_flag, 'Y', -100, decode(int.actual_flag, 'B', decode(ccid_cc.detail_budgeting_allowed_flag, 'N', -100, 0), decode(ccid_cc.detail_posting_allowed_flag, 'N', -100, 0)))), decode(flex_cc.code_combination_id, null, -4, decode(sign(nvl(flex_cc.start_date_active, int.accounting_date-1) - int.accounting_date), 1, -1, decode(sign(nvl(flex_cc.end_date_active, int.accounting_date +1) - int.accountin g_date), -1, -1, 0)) + decode(flex_cc.enabled_flag, 'N', -10, 0) + decode(flex_cc.summary_flag, 'Y', -100, decode(int.actual_flag, 'B', decode(flex_cc.detail_budgeting_allowed_flag, 'N', -100, 0), decode(flex_cc.detail_posting_allowed_flag, 'N', -100, 0))))) , int.user_je_category_name , int.user_je_category_name , 'UNKNOWN' period_name , decode(actual_flag, 'B' , decode(period_name, NULL, '-1' , period_name), nvl(period_name, '0')) period_name2 , currency_c ode , decode(actual_flag , 'A', actual_flag , 'B', decode(budget_version_id , 1002, '2' , 1003, '2' , 1004, '2' , 1005, '2' , 1006, '2' , 1065, '2' , 1066, '2' , 1087, '2' , 1290, actual_flag , 1311, actual_flag , 1351, actual_flag , 1659, actual_flag , 1939, '2' , 1972, actual_flag , NULL, '1', '6') , 'E', decode(encumbrance_type_id , 1000 , actual_flag , 1001, actual_flag , 1022, actual_flag , 1023, actual_flag , 1024, actual_flag , 1048, actual_flag , 1049, actual_flag , 1050, actual_flag , 1025, actual_flag , 999, actual_flag , 1045, actual_flag , 1046, actual_flag , 1047, actual_flag , 1068, actual_flag , 1088, actual_flag , NULL, '3', '4'), '5') actual_flag , '0' exception_rate , decode( currency_code , 'USD', 1 , 'STAT', 1 , decode(actual_flag, 'E', -8, 'B', 1 , decode(user_currency_conversion_type , 'User', decode(currency_conversion_rate, NULL, -1, currency_conversion_rate) , 'Corporate', decode(currency_conversion_date, NULL, -2, -6) , 'Spot', decode(currency_conversion_date, NULL, -2, -6) , 'Reporting', decode(currency_conversion_date, NULL, -2, -6) , 'HRUK', decode(currency_conversion_date, NULL, -2, -6) , 'DALY', decode(currency_conversion_date, NULL, -2, -6) , NULL, decode(currency_conversion_rate, NULL, decode(decode(nvl(to_char(entered_dr), 'X'), 'X', 1, 2), decode(nvl(to_char(accounted_dr), 'X'), 'X', 1, 2), decode(decode(nvl(to_char(entered_cr), 'X'), 'X', 1, 2), decode(nvl(to_char(accounted_cr), 'X'), 'X', 1, 2), -20, -3), -3), -9), -9))) currency_conversion_rate , to_number(to_char(nvl(int.currency_conversion_date, in t.accounting_date), 'J')) , decode(int.actual_flag , 'A', decode(int.currency_code , 'USD', 'User' , 'STAT', 'User' , nvl(int.user_currency_conversion_type, 'User')) , 'B', 'User', 'E', 'User' , nvl(int.user_currency_conversion_type, 'User')) user_currency_conversion_type , ltrim(rtrim(substrb(rtrim(substrb(int.reference1, 1, 50)) || ' ' || int.user_je_source_name || ' 2749104: ' || int.actual_flag || ' ' || int.group_id, 1, 100))) , rtrim(substrb(nvl(rtrim(in t.reference2), 'Journal Import ' || int.user_je_source_name || ' 2749104:'), 1, 240)) , ltrim(rtrim(substrb(rtrim(rtrim(substrb(int.reference4, 1, 25)) || ' ' || int.user_je_category_name || ' ' || int.currency_code || decode(int.actual_flag, 'E', ' ' || int.encumbrance_type_id, 'B', ' ' || int.budget_version_id, '') || ' ' || int.user_currency_conversion_type || ' ' || decode(int.user_currency_conversion_type, NULL, '', 'User', to_char(int.currency_conversion_rate), to_char(in t.currency_conversion_date))) || ' ' || substrb(int.reference8, 1, 15) || int.originating_bal_seg_value, 1, 100))) , rtrim(nvl(rtrim(int.reference5), 'Journal Import 2749104:')) , rtrim(substrb(nvl(rtrim(int.reference6), 'Journal Import Created'), 1, 80)) , rtrim(decode(upper(substrb(nvl(rtrim(int.reference7), 'N'), 1, 1)), 'Y', 'Y', 'N')) , decode(upper(substrb(int.reference7, 1, 1)), 'Y', decode(rtrim(reference8), NULL, '-1', rtrim(substrb(reference8, 1, 15))), NULL) , rtrim(upper(substrb(int.reference9, 1, 1))) , rtrim(nvl(rtrim(int.reference10), nvl(to_char(int.subledger_doc_sequence_value), 'Journal Import Created'))) , int.entered_dr , int.entered_cr , to_number(to_char(int.accounting_date, 'J')) , to_char(int.accounting_date, 'YYYY/MM/DD') , int.user_je_source_name , nvl(int.encumbrance_type_id, -1) , nvl(int.budget_version_id, -1) , NULL , decode(int.stat_amount, '0', NULL, int.stat_amount) , decode(int.actual_flag , 'E', dec ode(int.currency_code, 'STAT', '1', '0'), '0') , decode(int.actual_flag , 'A', decode(int.budget_version_id , NULL, decode(int.encumbrance_type_id, NULL, '0', '1') , decode(int.encumbrance_type_id, NULL, '2', '3')) , 'B', decode(int.encumbrance_type_id , NULL, '0', '4') , 'E', decode(int.budget_version_id , NULL, '0', '5'), '0') , int.accounted_dr , int.accounted_cr , nvl(int.group_id, -1) , nvl(int.average_journal_flag, 'N') , int.or iginating_bal_seg_value from GL_INTERFACE int, gl_code_combinations flex_cc, gl_code_combinations ccid_cc where int.set_of_books_id = 1 and int.status != 'PROCESSED' and accounting_date between to_date('2006/03/11', 'YYYY/MM/DD') and to_date('2007/04/11', 'YYYY/MM/DD') and (int.user_je_source_name, group_id) in (('Receivables', 49823)) and flex_cc.SEGMENT1(+) = int.SEGMENT1 and flex_cc.SEGMENT2(+) = int.SEGMENT2 and flex_cc.SEGMENT3(+) = int.SEGMENT3 and flex_cc.SEGMENT4(+) = int.SEGMENT4 and flex_cc.SEGMENT5(+) = int.SEGMENT5 and flex_cc.chart_of_accounts_id(+) = 101 and flex_cc.template_id(+) is NULL and ccid_cc.code_combination_id(+) = int.code_combination_id and ccid_cc.chart_of_accounts_id(+) = 101 and ccid_cc.template_id(+) is NULL order by decode(int.SEGMENT1|| int.SEGMENT2|| int.SEGMENT3|| int.SEGMENT4|| int.SEGMENT5 , '' , rpad(ccid_cc.SEGMENT3, 30) || '-' || rpad(ccid_cc.SEGMENT1, 30) || '-' || rpad(ccid_cc.SEGMENT2, 30) || '-' || rpad(ccid_cc.SEGMENT4, 30) || '-' || rpad(ccid_cc.SEGMENT5, 30) , rpad(int.SEGMENT3, 30) || '-' || rpad(int.SEGMENT1, 30) || '-' || rpad(int.SEGMENT2, 30) || '-' || rpad(int.SEGMENT4, 30) || '-' || rpad(int.SEGMENT5, 30) ) , int.entered_dr, int.accounted_dr, int.entered_cr, int.accounted_cr, int.accounting_date
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
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;
c1a0aqnhptp0c update GL_INTERFACE set status = :status , status_description = :description , je_batch_id = :batch_id , je_header_id = :header_id , je_line_num = :line_num , code_combination_id = decode(:ccid, '-1', code_combination_id, :ccid) , accounted_dr = :acc_dr , accounted_cr = :acc_cr , descr_flex_error_message = :descr_description , request_id = to_number(:req_id) where rowid = :row_id
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
fw8fr3nddyd26 insert into pay_run_balances(run_balance_id, assignment_action_id, assignment_id, action_sequence, defined_balance_id, balance_value, payroll_action_id, effective_date, tax_unit_id, jurisdiction_code, jurisdiction_comp1, jurisdiction_comp2, jurisdiction_comp3, source_id, source_text, tax_group, source_number, source_text2) values (:b1, :b2:b3, :b4:b5, :b6:b7, :b8, :b9, :b10:b11, to_date(:b12, :b13), :b14:b15, :b16:b17, :b18:b19, :b20:b21, :b22:b23, :b24:b25, :b26:b27, :b28:b29, :b30:b31, :b32:b3 3)
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,731,391 480.31 5.46
CPU used when call started 1,731,850 480.44 5.46
CR blocks created 2,282,958 633.32 7.20
Cached Commit SCN referenced 36,184 10.04 0.11
Commit SCN cached 157 0.04 0.00
DB time 5,639,621 1,564.51 17.79
DBWR checkpoint buffers written 212,202 58.87 0.67
DBWR checkpoints 183 0.05 0.00
DBWR object drop buffers written 5,330 1.48 0.02
DBWR parallel query checkpoint buffers written 0 0.00 0.00
DBWR revisited being-written buffer 100 0.03 0.00
DBWR transaction table writes 3,212 0.89 0.01
DBWR undo block writes 484,818 134.50 1.53
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 4,127,241 1,144.95 13.02
IMU Flushes 87,745 24.34 0.28
IMU Redo allocation size 410,280,192 113,817.33 1,293.97
IMU bind flushes 455 0.13 0.00
IMU commits 165,500 45.91 0.52
IMU contention 12,123 3.36 0.04
IMU ktichg flush 4,409 1.22 0.01
IMU pool not allocated 5 0.00 0.00
IMU recursive-transaction flush 525 0.15 0.00
IMU undo allocation size 854,918,540 237,166.09 2,696.29
IMU- failed to get a private strand 5 0.00 0.00
PX local messages recv'd 5,460 1.51 0.02
PX local messages sent 5,460 1.51 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 13,964,418 3,873.92 44.04
active txn count during cleanout 10,591,865 2,938.33 33.41
application wait time 10,576 2.93 0.03
background checkpoints completed 2 0.00 0.00
background checkpoints started 2 0.00 0.00
background timeouts 10,291 2.85 0.03
branch node splits 261 0.07 0.00
buffer is not pinned count 174,157,385 48,313.64 549.27
buffer is pinned count 414,784,985 115,067.03 1,308.17
bytes received via SQL*Net from client 3,147,513,651 873,163.32 9,926.81
bytes sent via SQL*Net to client 2,734,605,843 758,617.05 8,624.56
calls to get snapshot scn: kcmgss 28,988,439 8,041.79 91.43
calls to kcmgas 3,480,798 965.62 10.98
calls to kcmgcs 1,510,522 419.04 4.76
change write time 42,758 11.86 0.13
cleanout - number of ktugct calls 3,756,600 1,042.13 11.85
cleanouts and rollbacks - consistent read gets 2,167,785 601.37 6.84
cleanouts only - consistent read gets 44,216 12.27 0.14
cluster key scan block gets 8,179,523 2,269.11 25.80
cluster key scans 1,674,541 464.54 5.28
commit cleanout failures: block lost 66,908 18.56 0.21
commit cleanout failures: buffer being written 30 0.01 0.00
commit cleanout failures: callback failure 2,962 0.82 0.01
commit cleanout failures: cannot pin 4,229 1.17 0.01
commit cleanouts 2,264,946 628.33 7.14
commit cleanouts successfully completed 2,190,817 607.76 6.91
commit txn count during cleanout 268,205 74.40 0.85
concurrency wait time 27,940 7.75 0.09
consistent changes 63,148,143 17,518.16 199.16
consistent gets 324,829,836 90,112.24 1,024.47
consistent gets - examination 184,783,251 51,261.40 582.78
consistent gets direct 49 0.01 0.00
consistent gets from cache 324,695,550 90,074.98 1,024.04
current blocks converted for CR 5 0.00 0.00
cursor authentications 1,255 0.35 0.00
data blocks consistent reads - undo records applied 61,878,315 17,165.89 195.16
db block changes 54,126,550 15,015.45 170.71
db block gets 65,915,343 18,285.82 207.89
db block gets direct 3,042 0.84 0.01
db block gets from cache 65,910,653 18,284.52 207.87
deferred (CURRENT) block cleanout applications 439,077 121.81 1.38
dirty buffers inspected 451,582 125.28 1.42
enqueue conversions 4,557 1.26 0.01
enqueue releases 7,236,394 2,007.47 22.82
enqueue requests 7,240,376 2,008.58 22.84
enqueue timeouts 3,597 1.00 0.01
enqueue waits 4,337 1.20 0.01
exchange deadlocks 307 0.09 0.00
execute count 26,316,167 7,300.46 83.00
free buffer inspected 1,593,144 441.96 5.02
free buffer requested 3,956,111 1,097.48 12.48
heap block compress 1,800,202 499.40 5.68
hot buffers moved to head of LRU 1,113,394 308.87 3.51
immediate (CR) block cleanout applications 2,212,001 613.64 6.98
immediate (CURRENT) block cleanout applications 373,663 103.66 1.18
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 51,850,454 14,384.02 163.53
index scans kdiixs1 39,651,788 10,999.95 125.06
java call heap collected bytes 6,599,792 1,830.87 20.81
java call heap collected count 143,710 39.87 0.45
java call heap gc count 14 0.00 0.00
java call heap live object count 11,198 3.11 0.04
java call heap live object count max 11,474 3.18 0.04
java call heap live size 3,364,080 933.24 10.61
java call heap live size max 3,392,320 941.08 10.70
java call heap object count 32,108 8.91 0.10
java call heap object count max 32,108 8.91 0.10
java call heap total size 4,718,592 1,309.00 14.88
java call heap total size max 4,718,592 1,309.00 14.88
java call heap used size 4,315,088 1,197.06 13.61
java call heap used size max 4,315,600 1,197.21 13.61
leaf node 90-10 splits 8,723 2.42 0.03
leaf node splits 64,646 17.93 0.20
logons cumulative 2,760 0.77 0.01
messages received 279,691 77.59 0.88
messages sent 279,690 77.59 0.88
no buffer to keep pinned count 114 0.03 0.00
no work - consistent read gets 99,492,759 27,600.65 313.79
opened cursors cumulative 2,203,453 611.27 6.95
parse count (failures) 961 0.27 0.00
parse count (hard) 7,094 1.97 0.02
parse count (total) 2,875,721 797.76 9.07
parse time cpu 56,089 15.56 0.18
parse time elapsed 66,267 18.38 0.21
physical read IO requests 507,040 140.66 1.60
physical reads 570,539 158.28 1.80
physical reads cache 548,933 152.28 1.73
physical reads cache prefetch 43,253 12.00 0.14
physical reads direct 19,229 5.33 0.06
physical reads direct temporary tablespace 18,979 5.27 0.06
physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 452,868 125.63 1.43
physical writes 890,524 247.04 2.81
physical writes direct 22,171 6.15 0.07
physical writes direct (lob) 1,337 0.37 0.00
physical writes direct temporary tablespace 20,631 5.72 0.07
physical writes from cache 866,829 240.47 2.73
physical writes non checkpoint 777,260 215.62 2.45
pinned buffers inspected 3,338 0.93 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,605 1.00 0.01
queries parallelized 14 0.00 0.00
recursive calls 43,836,666 12,160.89 138.25
recursive cpu usage 902,681 250.42 2.85
redo blocks written 17,560,139 4,871.42 55.38
redo buffer allocation retries 63 0.02 0.00
redo entries 26,813,726 7,438.49 84.57
redo log space requests 12 0.00 0.00
redo log space wait time 18 0.00 0.00
redo ordering marks 424,225 117.69 1.34
redo size 8,613,184,956 2,389,415.27 27,164.76
redo synch time 206,254 57.22 0.65
redo synch writes 152,197 42.22 0.48
redo wastage 82,869,580 22,989.15 261.36
redo write time 53,753 14.91 0.17
redo writer latching time 518 0.14 0.00
redo writes 223,578 62.02 0.71
rollback changes - undo records applied 96,900 26.88 0.31
rollbacks only - consistent read gets 112,938 31.33 0.36
rows fetched via callback 40,091,733 11,121.99 126.44
session connect time 0 0.00 0.00
session cursor cache hits 1,342,513 372.43 4.23
session logical reads 390,744,872 108,397.97 1,232.35
session pga memory 12,580,591,712 3,490,028.15 39,677.40
session pga memory max 12,671,026,240 3,515,115.92 39,962.61
session uga memory 11,424,506,956 3,169,314.43 36,031.27
session uga memory max 13,722,883,536 3,806,915.52 43,280.02
shared hash latch upgrades - no wait 42,890,007 11,898.27 135.27
shared hash latch upgrades - wait 1,982 0.55 0.01
sorts (disk) 1 0.00 0.00
sorts (memory) 3,658,034 1,014.79 11.54
sorts (rows) 45,568,996 12,641.46 143.72
summed dirty queue length 748,410 207.62 2.36
switch current to new buffer 372,059 103.21 1.17
table fetch by rowid 252,515,422 70,051.23 796.40
table fetch continued row 2,281,695 632.97 7.20
table scan blocks gotten 16,622,858 4,611.41 52.43
table scan rows gotten 511,114,149 141,790.05 1,611.98
table scans (cache partitions) 104 0.03 0.00
table scans (direct read) 0 0.00 0.00
table scans (long tables) 1,001 0.28 0.00
table scans (rowid ranges) 1,094 0.30 0.00
table scans (short tables) 1,088,157 301.87 3.43
transaction rollbacks 2,569 0.71 0.01
transaction tables consistent read rollbacks 5 0.00 0.00
transaction tables consistent reads - undo records applied 174 0.05 0.00
undo change vector size 3,320,727,616 921,215.24 10,473.10
user I/O wait time 282,409 78.34 0.89
user calls 13,388,881 3,714.26 42.23
user commits 245,302 68.05 0.77
user rollbacks 71,770 19.91 0.23
workarea executions - onepass 2 0.00 0.00
workarea executions - optimal 1,977,480 548.58 6.24
write clones created in background 2,802 0.78 0.01
write clones created in foreground 270 0.07 0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

Statistic Begin Value End Value
session cursor cache count 59,222 131,912
opened cursors current 307,704 400,115
workarea memory allocated 0 1,161
logons current 1,732 2,038

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 242,377 67 5.81 1.05 45,321 13 4,705 1.73
APPS_UNDOTS1 842 0 2.85 1.00 256,890 71 19,844 0.43
APPS_TS_TX_IDX 147,159 41 6.63 1.00 75,884 21 6,377 1.63
SYSTEM 45,049 12 4.31 1.56 2,640 1 388 1.31
SYSAUX 21,080 6 1.94 1.06 12,004 3 0 0.00
APPS_TS_PAY_IDX 10,771 3 2.61 1.00 14,340 4 7,224 0.62
APPS_TS_TX_INTERFACE 6,016 2 3.63 1.27 17,944 5 55 1.27
APPS_TS_PAY_DATA 10,166 3 2.81 1.00 7,056 2 1,051 1.10
TEMP 3,695 1 16.97 6.23 13,358 4 46 1.74
APPS_TS_QUEUES 10,448 3 1.89 1.01 3,580 1 20 3.00
APPS_TS_SEED 8,352 2 6.88 1.20 939 0 268 4.10
CTXSYS 838 0 6.66 1.00 1,225 0 3 0.00
APPS_TS_SUMMARY 415 0 3.86 1.00 1,420 0 100 0.30
APPS_TS_ARCHIVE 306 0 6.31 1.00 505 0 3 13.33
APPS_TS_NOLOGGING 41 0 7.56 1.00 38 0 0 0.00
OLAP 16 0 3.75 1.69 2 0 0 0.00
APPS_TS_MEDIA 9 0 8.89 1.00 6 0 0 0.00
ODM 7 0 4.29 1.00 2 0 0 0.00
OWAPUB 5 0 12.00 1.00 2 0 0 0.00
PORTAL 5 0 12.00 1.00 2 0 0 0.00
SYNCSERVER 5 0 4.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 139 0 6.47 1.00 215 0 2 5.00
APPS_TS_ARCHIVE /raw_links/archive2 167 0 6.17 1.00 290 0 1 30.00
APPS_TS_MEDIA /raw_links/media1 3 0 16.67 1.00 2 0 0 0.00
APPS_TS_MEDIA /raw_links/media2 3 0 10.00 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 36 0 8.06 1.00 36 0 0 0.00
APPS_TS_NOLOGGING /raw_links/nologging2 5 0 4.00 1.00 2 0 0 0.00
APPS_TS_PAY_DATA /raw_links/francis1 10,166 3 2.81 1.00 7,056 2 1,051 1.10
APPS_TS_PAY_IDX /raw_links/francis2 10,771 3 2.61 1.00 14,340 4 7,224 0.62
APPS_TS_QUEUES /raw_links/queues1 3,455 1 1.49 1.00 799 0 6 6.67
APPS_TS_QUEUES /raw_links/queues2 3,501 1 2.04 1.01 1,098 0 11 1.82
APPS_TS_QUEUES /raw_links/queues3 3,492 1 2.13 1.00 1,683 0 3 0.00
APPS_TS_SEED /raw_links/apps_ts_seed01 2,606 1 8.01 1.00 415 0 1 0.00
APPS_TS_SEED /raw_links/reference1 2,819 1 6.63 1.27 286 0 159 4.09
APPS_TS_SEED /raw_links/reference2 2,927 1 6.11 1.31 238 0 108 4.17
APPS_TS_SUMMARY /raw_links/summary1 67 0 2.24 1.00 273 0 25 0.00
APPS_TS_SUMMARY /raw_links/summary2 46 0 3.48 1.00 211 0 13 0.00
APPS_TS_SUMMARY /raw_links/summary3 106 0 4.15 1.00 268 0 12 0.00
APPS_TS_SUMMARY /raw_links/summary4 68 0 4.71 1.00 221 0 15 1.33
APPS_TS_SUMMARY /raw_links/summary5 41 0 5.61 1.00 171 0 17 0.00
APPS_TS_SUMMARY /raw_links/summary6 87 0 3.45 1.00 276 0 18 0.56
APPS_TS_TX_DATA /raw_links/tx_data1 11,738 3 5.35 1.09 2,478 1 371 1.35
APPS_TS_TX_DATA /raw_links/tx_data10 11,547 3 5.59 1.09 2,748 1 288 2.12
APPS_TS_TX_DATA /raw_links/tx_data11 12,499 3 5.87 1.08 2,957 1 311 1.96
APPS_TS_TX_DATA /raw_links/tx_data12 27,514 8 6.29 1.00 3,691 1 197 0.61
APPS_TS_TX_DATA /raw_links/tx_data13 28,046 8 6.16 1.00 3,494 1 203 1.13
APPS_TS_TX_DATA /raw_links/tx_data14 27,460 8 6.14 1.00 3,307 1 156 0.58
APPS_TS_TX_DATA /raw_links/tx_data15 26,997 7 6.06 1.00 3,275 1 232 0.60
APPS_TS_TX_DATA /raw_links/tx_data2 11,361 3 5.36 1.10 2,443 1 291 1.86
APPS_TS_TX_DATA /raw_links/tx_data3 12,149 3 5.37 1.09 3,134 1 295 2.81
APPS_TS_TX_DATA /raw_links/tx_data4 12,237 3 5.36 1.09 3,144 1 481 2.37
APPS_TS_TX_DATA /raw_links/tx_data5 9,790 3 5.49 1.12 2,962 1 449 2.09
APPS_TS_TX_DATA /raw_links/tx_data6 11,701 3 5.70 1.10 2,753 1 410 1.80
APPS_TS_TX_DATA /raw_links/tx_data7 15,153 4 5.80 1.07 3,435 1 305 1.31
APPS_TS_TX_DATA /raw_links/tx_data8 12,042 3 5.36 1.09 2,772 1 340 1.68
APPS_TS_TX_DATA /raw_links/tx_data9 12,143 3 5.33 1.09 2,728 1 376 1.81
APPS_TS_TX_IDX /raw_links/tx_idx1 8,280 2 6.22 1.00 4,198 1 352 2.07
APPS_TS_TX_IDX /raw_links/tx_idx10 8,338 2 6.89 1.00 3,614 1 386 1.22
APPS_TS_TX_IDX /raw_links/tx_idx11 8,417 2 6.46 1.00 3,582 1 467 1.16
APPS_TS_TX_IDX /raw_links/tx_idx12 13,593 4 6.78 1.00 7,582 2 607 1.42
APPS_TS_TX_IDX /raw_links/tx_idx13 13,080 4 6.75 1.00 6,865 2 332 0.81
APPS_TS_TX_IDX /raw_links/tx_idx14 13,042 4 6.82 1.00 6,718 2 252 1.23
APPS_TS_TX_IDX /raw_links/tx_idx15 13,035 4 7.13 1.00 6,985 2 365 2.03
APPS_TS_TX_IDX /raw_links/tx_idx2 7,669 2 6.28 1.00 4,496 1 302 1.99
APPS_TS_TX_IDX /raw_links/tx_idx3 8,496 2 6.61 1.00 4,419 1 331 1.69
APPS_TS_TX_IDX /raw_links/tx_idx4 9,353 3 6.83 1.00 4,642 1 476 3.13
APPS_TS_TX_IDX /raw_links/tx_idx5 8,202 2 6.33 1.00 4,690 1 418 1.44
APPS_TS_TX_IDX /raw_links/tx_idx6 8,880 2 6.47 1.00 5,464 2 958 1.22
APPS_TS_TX_IDX /raw_links/tx_idx7 9,777 3 6.17 1.00 5,170 1 443 1.40
APPS_TS_TX_IDX /raw_links/tx_idx8 8,633 2 6.44 1.00 3,927 1 384 1.74
APPS_TS_TX_IDX /raw_links/tx_idx9 8,364 2 6.81 1.00 3,532 1 304 2.47
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface 1,029 0 3.98 1.94 1,507 0 24 2.92
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface2 2,585 1 3.61 1.13 8,316 2 20 0.00
APPS_TS_TX_INTERFACE /raw_links/apps_ts_tx_interface3 2,402 1 3.50 1.14 8,121 2 11 0.00
APPS_UNDOTS1 /raw_links/undo01 141 0 3.83 1.00 45,103 13 3,127 0.47
APPS_UNDOTS1 /raw_links/undo02 152 0 2.96 1.00 43,892 12 3,154 0.21
APPS_UNDOTS1 /raw_links/undo03 127 0 3.07 1.00 43,789 12 3,487 0.37
APPS_UNDOTS1 /raw_links/undo04 125 0 2.56 1.00 40,489 11 3,594 0.82
APPS_UNDOTS1 /raw_links/undo05 134 0 1.87 1.00 41,288 11 3,044 0.33
APPS_UNDOTS1 /raw_links/undo06 163 0 2.76 1.00 42,329 12 3,438 0.35
CTXSYS /raw_links/ctx1 560 0 7.05 1.00 814 0 2 0.00
CTXSYS /raw_links/ctx2 278 0 5.86 1.00 411 0 1 0.00
ODM /raw_links/odm 7 0 4.29 1.00 2 0 0 0.00
OLAP /raw_links/olap 16 0 3.75 1.69 2 0 0 0.00
OWAPUB /raw_links/owa1 5 0 12.00 1.00 2 0 0 0.00
PORTAL /raw_links/portal 5 0 12.00 1.00 2 0 0 0.00
SYNCSERVER /raw_links/mobile01 5 0 4.00 1.00 2 0 0 0.00
SYSAUX /raw_links/sysaux01 21,080 6 1.94 1.06 12,004 3 0 0.00
SYSTEM /raw_links/sys1 6,703 2 4.51 1.54 291 0 130 0.62
SYSTEM /raw_links/sys2 6,468 2 4.03 1.56 367 0 23 2.61
SYSTEM /raw_links/sys3 6,404 2 3.84 1.57 419 0 8 3.75
SYSTEM /raw_links/sys4 6,787 2 4.41 1.56 564 0 11 8.18
SYSTEM /raw_links/sys5 7,185 2 4.40 1.57 457 0 34 2.94
SYSTEM /raw_links/sys6 6,514 2 4.30 1.53 331 0 13 4.62
SYSTEM /raw_links/sys7 4,988 1 4.72 1.59 211 0 169 0.53
TEMP /raw_links/tmp1 1,052 0 5.96 1.00 4,059 1 1 10.00
TEMP /raw_links/tmp2 1,750 0 19.90 8.79 5,976 2 26 2.69
TEMP /raw_links/tmp3 893 0 24.19 7.37 3,323 1 19 0.00

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 390,377,370 548,979 866,829 0 0 40,084


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 40 21,412 493,668 1,390,919 17,510,400 1,390,919  
E 0 38 14,787 328,779 5,311,654 17,510,400 5,311,654  

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 7.19 5,922,065
D 256 0.19 32,096 5.27 4,343,919
D 384 0.28 48,144 4.44 3,657,828
D 512 0.37 64,192 3.94 3,245,298
D 640 0.47 80,240 3.32 2,734,863
D 768 0.56 96,288 2.52 2,073,433
D 896 0.65 112,336 1.72 1,415,734
D 1,024 0.74 128,384 1.42 1,172,624
D 1,152 0.84 144,432 1.24 1,020,128
D 1,280 0.93 160,480 1.09 894,272
D 1,376 1.00 172,516 1.00 823,685
D 1,408 1.02 176,528 0.98 807,608
D 1,536 1.12 192,576 0.93 766,858
D 1,664 1.21 208,624 0.90 741,217
D 1,792 1.30 224,672 0.87 716,216
D 1,920 1.40 240,720 0.85 697,967
D 2,048 1.49 256,768 0.83 679,849
D 2,176 1.58 272,816 0.81 667,477
D 2,304 1.67 288,864 0.80 655,539
D 2,432 1.77 304,912 0.78 644,725
D 2,560 1.86 320,960 0.77 636,191

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
99.65 93,920 334

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 21,892 9,648.54 0.00 0.00 0.00 0.00 102,400
E 30,720 19,442 12,727.97 1.12 0.01 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,862,640 1,862,640 0 0
64K 128K 5,390 5,390 0 0
128K 256K 335 335 0 0
256K 512K 329 329 0 0
512K 1024K 108,491 108,491 0 0
1M 2M 90 90 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 125,093.44 2,991.99 98.00 656
7,680 0.25 125,093.44 1,966.02 98.00 364
15,360 0.50 125,093.44 166.81 100.00 0
23,040 0.75 125,093.44 166.81 100.00 0
30,720 1.00 125,093.44 166.81 100.00 0
36,864 1.20 125,093.44 166.81 100.00 0
43,008 1.40 125,093.44 166.81 100.00 0
49,152 1.60 125,093.44 166.81 100.00 0
55,296 1.80 125,093.44 166.81 100.00 0
61,440 2.00 125,093.44 166.81 100.00 0
92,160 3.00 125,093.44 166.81 100.00 0
122,880 4.00 125,093.44 166.81 100.00 0
184,320 6.00 125,093.44 166.81 100.00 0
245,760 8.00 125,093.44 166.81 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 194 16,176 53,913 1.00 890 1.00 43,977,812
1,280 0.69 380 31,676 53,916 1.00 887 1.00 43,978,461
1,472 0.79 425 36,460 53,916 1.00 887 1.00 43,978,488
1,664 0.90 425 36,460 53,916 1.00 887 1.00 43,978,488
1,856 1.00 425 36,460 53,916 1.00 887 1.00 43,978,488
2,048 1.10 425 36,460 53,916 1.00 887 1.00 43,978,488
2,240 1.21 425 36,460 53,916 1.00 887 1.00 43,978,488
2,432 1.31 425 36,460 53,916 1.00 887 1.00 43,978,488
2,624 1.41 425 36,460 53,916 1.00 887 1.00 43,978,488
2,816 1.52 425 36,460 53,916 1.00 887 1.00 43,978,488
3,008 1.62 425 36,460 53,916 1.00 887 1.00 43,978,488
3,200 1.72 425 36,460 53,916 1.00 887 1.00 43,978,488
3,392 1.83 425 36,460 53,916 1.00 887 1.00 43,978,488
3,584 1.93 425 36,460 53,916 1.00 887 1.00 43,978,488
3,776 2.03 425 36,460 53,916 1.00 887 1.00 43,978,488

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 10 262 178 1.00 414 1.00 3,202
32 1.00 10 262 178 1.00 414 1.00 3,202
48 1.50 10 262 178 1.00 414 1.00 3,202
64 2.00 10 262 178 1.00 414 1.00 3,202

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 19,950 26 1
undo block 17,400 5 0
undo header 2,444 4 1
2nd level bmb 28 0 4
segment header 71 0 1
file header block 44 0 2
1st level bmb 146 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,899 174 1,725 174 67 383.62
TM-DML 1,501,374 1,501,356 0 5 22 4,354.00
TX-Transaction (index contention) 2,703 2,703 0 2,204 14 6.45
UL-User-defined 23,869 22,101 1,768 84 10 115.60
SQ-Sequence Cache 13,167 13,167 0 1,531 2 1.55
RO-Multiple Object Reuse (fast object reuse) 1,267 1,267 0 168 1 4.64
TX-Transaction 356,006 356,006 0 65 0 3.85
DV-Diana Versioning 7,466 7,466 0 27 0 7.04
HW-Segment High Water Mark 85,739 85,739 0 39 0 3.59
TX-Transaction (allocate ITL entry) 21 21 0 9 0 7.78
CU-Cursor 6,651 6,651 0 6 0 3.33
FB-Format Block 7,184 7,184 0 14 0 0.00
PS-PX Process Reservation 624 520 104 11 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 482,945 357,152 2,587 134 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 02:26 22,605 40,752 680 128 0 0 0/0/0/0/0/0
11-Mar 02:16 130,145 59,125 527 132 0 0 0/0/0/0/0/0
11-Mar 02:06 122,662 78,108 822 134 0 0 0/0/0/0/0/0
11-Mar 01:56 58,453 51,594 2,587 132 0 0 0/0/0/0/0/0
11-Mar 01:46 38,537 55,195 1,561 130 0 0 0/0/0/0/0/0
11-Mar 01:36 110,543 72,378 1,690 133 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 223,675 0.03 0.03 0 0  
FOB s.o list latch 27,786 0.02 0.00 0 0  
In memory undo latch 62,235,284 0.70 0.00 3 765,264 0.00
JOX SGA heap latch 117,233 0.01 0.00 0 0  
JS mem alloc latch 2 0.00   0 0  
JS queue access latch 2 0.00   0 0  
JS queue state obj latch 21,644 0.00   0 0  
JS slv state obj latch 10 0.00   0 0  
KTF sga enqueue 0     0 1,214 0.00
KWQMN job cache list latch 451,745 0.06 1.42 2 0  
MQL Tracking Latch 0     0 72 0.00
Memory Management Latch 18,000 0.00   0 1,201 0.00
PL/SQL warning settings 938,526 0.02 0.00 0 0  
SQL memory manager latch 2 0.00   0 1,199 0.00
SQL memory manager workarea list latch 795,577 0.00 0.00 0 0  
SWRF Alerted Metric Element list 124,339 0.00   0 0  
Shared B-Tree 2 0.00   0 0  
active checkpoint queue latch 55,770 0.01 0.00 0 0  
active service list 10,643 0.06 0.00 0 0  
archive control 46 0.00   0 0  
begin backup scn array 378 0.00   0 0  
cache buffer handles 7,747,923 0.17 0.00 0 0  
cache buffers chains 846,055,167 0.41 0.01 95 3,231,330 0.96
cache buffers lru chain 2,843,591 0.33 0.01 1 6,063,041 0.42
channel handle pool latch 111,551 0.01 0.00 0 0  
channel operations parent latch 385,590 0.01 0.17 0 0  
checkpoint queue latch 2,104,282 0.01 0.03 0 840,523 0.01
child cursor hash table 76,140 0.01 0.00 0 0  
client/application info 448,347 0.00 0.00 0 0  
commit callback allocation 41,302 0.00   0 0  
compile environment latch 970,204 0.01 0.00 0 0  
cursor bind value capture 1,492 0.07 0.00 0 16,181 0.02
dictionary lookup 371 0.00   0 0  
dml lock allocation 2,998,268 0.12 0.02 0 0  
dummy allocation 5,228 0.77 0.00 0 0  
enqueue hash chains 14,486,509 0.04 0.01 1 7 0.00
enqueues 11,714,879 0.09 0.00 0 0  
error message lists 272 4.04 0.00 0 0  
event group latch 1,380 0.00   0 0  
file cache latch 11,702 0.00   0 0  
global KZLD latch for mem in SGA 2,620 0.00   0 0  
global ctx hash table latch 6,138 0.00   0 0  
hash table column usage latch 1,553 0.06 0.00 0 4,979,839 0.01
hash table modification latch 216 0.00   0 0  
job workq parent latch 0     0 4 0.00
job_queue_processes parameter latch 62 0.00   0 0  
ksuosstats global area 244 0.00   0 0  
ktm global data 10,399 0.00   0 0  
lgwr LWN SCN 223,829 0.05 0.00 0 0  
library cache 137,196,507 1.05 0.02 141 1,131 5.13
library cache load lock 4,294 0.02 0.00 0 0  
library cache lock 17,007,722 0.05 0.01 0 0  
library cache lock allocation 477,675 0.00 0.00 0 0  
library cache pin 119,834,106 0.18 0.01 16 5 0.00
library cache pin allocation 487,793 0.00 0.05 0 0  
list of block allocation 92,794 0.00 0.00 0 0  
loader state object freelist 88,954 0.00 0.00 0 0  
longop free list parent 67 0.00   0 67 0.00
message pool operations parent latch 112,628 0.00 2.00 0 0  
messages 935,637 0.02 0.00 0 0  
mostly latch-free SCN 228,519 1.85 0.01 0 0  
multiblock read objects 19,176 0.04 0.00 0 0  
ncodef allocation latch 62 0.00   0 0  
object queue header heap 151,469 0.00   0 151,926 0.00
object queue header operation 13,672,115 0.01 0.02 0 0  
object stats modification 27,585 0.02 0.20 0 0  
parallel query alloc buffer 2,992 0.70 0.00 0 0  
parallel query stats 176 11.36 0.00 0 0  
parameter list 7,824 0.00   0 0  
parameter table allocation management 5,228 1.01 0.00 0 0  
post/wait queue 191,744 0.02 0.00 0 129,492 0.02
process allocation 2,786 0.00   0 1,380 0.00
process group creation 2,770 0.04 0.00 0 0  
process queue 1,632 0.12 0.00 0 0  
process queue reference 108,677 0.05 0.02 0 6,951 28.54
query server freelists 1,408 2.70 0.00 0 0  
query server process 8 0.00   0 8 0.00
redo allocation 2,307,516 0.27 0.05 1 26,801,563 0.46
redo copy 0     0 26,824,754 0.05
redo writing 755,622 0.00 0.06 0 0  
row cache objects 54,541,800 0.06 0.00 1 0  
rules engine statistics 11,529 0.02 0.00 0 0  
sequence cache 4,992,423 0.21 0.00 0 0  
session allocation 4,317,170 0.17 0.03 1 0  
session idle bit 27,743,933 0.03 0.11 6 0  
session switching 4,367 0.00   0 0  
session timer 1,220 0.00   0 0  
shared pool 45,968,066 0.97 0.01 4 0  
simulator hash latch 27,426,048 0.00 0.02 0 0  
simulator lru latch 30,086 0.20 0.15 0 299,891 1.43
slave class 4 0.00   0 0  
slave class create 15 0.00   0 0  
sort extent pool 354,546 0.03 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 119 0.00   0 0  
temporary table state object allocation 3,218 0.16 0.00 0 0  
threshold alerts latch 312 0.00   0 0  
trace latch 1 0.00   0 0  
transaction allocation 35,939 0.00   0 0  
transaction branch allocation 62 0.00   0 0  
undo global data 86,483,793 0.37 0.00 2 0  
user lock 9,876 0.06 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 846,055,167 3,485,254 18,562 3,471,667 8,934 4,428 223
library cache 137,196,507 1,439,253 26,609 1,412,904 26,090 258 1
shared pool 45,968,066 447,180 3,131 444,074 3,081 25 0
In memory undo latch 62,235,284 436,325 520 435,836 458 31 0
undo global data 86,483,793 322,093 229 321,871 215 7 0
library cache pin 119,834,106 220,152 2,776 217,399 2,730 23 0
row cache objects 54,541,800 31,608 94 31,514 94 0 0
cache buffer handles 7,747,923 13,337 11 13,327 9 1 0
sequence cache 4,992,423 10,579 41 10,540 37 2 0
enqueues 11,714,879 10,074 47 10,027 47 0 0
cache buffers lru chain 2,843,591 9,306 139 9,172 129 5 0
library cache lock 17,007,722 8,703 53 8,650 53 0 0
session idle bit 27,743,933 7,930 838 7,511 34 355 30
session allocation 4,317,170 7,139 201 6,939 199 1 0
redo allocation 2,307,516 6,321 325 6,062 193 66 0
enqueue hash chains 14,486,509 5,414 57 5,358 55 1 0
mostly latch-free SCN 228,519 4,222 61 4,162 59 1 0
dml lock allocation 2,998,268 3,489 57 3,433 55 1 0
object queue header operation 13,672,115 1,930 47 1,883 47 0 0
simulator hash latch 27,426,048 318 6 312 6 0 0
KWQMN job cache list latch 451,745 249 354 69 26 139 15
messages 935,637 223 1 222 1 0 0
checkpoint queue latch 2,104,282 223 6 217 6 0 0
sort extent pool 354,546 117 1 116 1 0 0
Consistent RBA 223,675 66 2 64 2 0 0
simulator lru latch 30,086 59 9 51 7 1 0
channel operations parent latch 385,590 53 9 49 0 3 1
process queue reference 108,677 52 1 51 1 0 0
redo writing 755,622 34 2 32 2 0 0
library cache pin allocation 487,793 20 1 19 1 0 0
object stats modification 27,585 5 1 4 1 0 0
message pool operations parent latch 112,628 2 4 1 0 0 1

Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch Name Where NoWait Misses Sleeps Waiter Sleeps
In memory undo latch kturbk 0 468 481
In memory undo latch ktiFlush: child 0 28 23
In memory undo latch kticmt: child 0 14 1
In memory undo latch ktichg: child 0 7 13
In memory undo latch ktiTxnPoolFree 0 1 0
In memory undo latch ktiBRacquire 0 1 0
In memory undo latch ktbgfc 0 1 2
KWQMN job cache list latch kwqmnuji: update job item 0 353 237
KWQMN job cache list latch kwqmndej: dequeue 0 1 141
cache buffer handles kcbzgs 0 6 7
cache buffer handles kcbzfs 0 5 4
cache buffers chains kcbchg: kslbegin: bufs not pinned 0 9,220 4,434
cache buffers chains kcbgtcr: fast path 0 3,290 8,115
cache buffers chains kcbgtcr: kslbegin excl 0 1,578 1,805
cache buffers chains kcbchg: kslbegin: call CR func 0 1,194 1,955
cache buffers chains kcbzgb: scan from tail. nowait 0 946 0
cache buffers chains kcbgcur: kslbegin 0 695 670
cache buffers chains kcbrls: kslbegin 0 517 672
cache buffers chains kcbget: pin buffer 0 327 499
cache buffers chains kcbnew: new latch again 0 245 18
cache buffers chains kcbgtcr: kslbegin shared 0 140 70
cache buffers chains kcbzwb 0 64 8
cache buffers chains kcbgtcr 0 55 1
cache buffers chains kcbget: exchange rls 0 51 4
cache buffers chains kcbget: exchange 0 46 15
cache buffers chains kcbnlc 0 45 68
cache buffers chains kcbzib: finish free bufs 0 40 11
cache buffers chains kcbbxsv 0 25 42
cache buffers chains kcbcge 0 21 28
cache buffers chains kcbbic2 0 11 7
cache buffers chains kcbo_ivd_process 0 8 8
cache buffers chains kcb_post_apply: kcbhq62 0 8 0
cache buffers chains kcb_is_private 0 7 60
cache buffers chains kcbzib: multi-block read: nowait 0 6 0
cache buffers chains kcb_post_apply: kcbhq63 0 5 0
cache buffers chains kcb_pre_apply: kcbhq61 0 4 27
cache buffers chains kcbnew_1 0 3 1
cache buffers lru chain kcbzgws 0 91 0
cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 15 0
cache buffers lru chain kcbzgb: posted for free bufs 0 14 87
cache buffers lru chain kcbzswcu 0 5 7
cache buffers lru chain kcbo_ivd_proc 0 4 18
cache buffers lru chain kcbo_link_q 0 4 0
cache buffers lru chain kcbbxsv: move to being written 0 3 9
cache buffers lru chain kcbbic2 0 2 17
cache buffers lru chain kcbzar: KSLNBEGIN 0 1 0
channel operations parent latch ksrapublish() 0 5 0
channel operations parent latch ksrchdelete() 0 4 0
checkpoint queue latch kcbklbc: Link buffer into ckpt queue 0 4 0
checkpoint queue latch kcbswcu: Switch buffers 0 2 3
dml lock allocation ktaiam 0 32 35
dml lock allocation ktaidm 0 25 22
enqueue hash chains ksqrcl 0 33 10
enqueue hash chains ksqgtl3 0 24 48
enqueues ksqies 0 18 34
enqueues ksqgel: create enqueue 0 16 9
enqueues ksqdel 0 13 4
library cache kglupc: child 0 6,475 3,588
library cache kglpndl: child: before processing 0 5,501 3,127
library cache kglpnc: child 0 4,772 7,562
library cache kglpnp: child 0 4,254 7,124
library cache kglpndl: child: after processing 0 1,465 10
library cache kglobpn: child: 0 712 2,109
library cache kglpin: child: heap processing 0 634 745
library cache kgldte: child 0 0 625 830
library cache kglhdiv: child 0 203 1
library cache kglhdgn: child: 0 175 537
library cache kglpin 0 137 65
library cache kglget: set reserved lock 0 81 191
library cache kglupd: child 0 56 0
library cache kglhdgc: child: 0 52 71
library cache kglic 0 52 3
library cache kgldti: 2child 0 16 4
library cache kglpur: child 0 13 1
library cache kglini: child 0 12 0
library cache kglidp: child 0 9 0
library cache kglpin: child: KGLMX 0 6 1
library cache kglati 0 2 1
library cache kglget: unpin heap 0 0 1 5
library cache kglobld 0 1 14
library cache lock kgllkdl: child: no lock handle 0 1,354 635
library cache lock kgllkal: child: multiinstance 0 30 37
library cache lock kgllkdl: child: cleanup 0 23 16
library cache pin kglpndl 0 852 356
library cache pin kglupc 0 615 375
library cache pin kglpnc: child 0 560 810
library cache pin kglpnp: child 0 558 836
library cache pin kglpnal: child: alloc space 0 191 399
library cache pin allocation kglpnget 0 1 1
message pool operations parent latch ksrmalloc() 0 4 0
messages ksarcv 0 1 0
mostly latch-free SCN kcs04 0 57 57
mostly latch-free SCN kcs05 0 4 4
mostly latch-free SCN kcsnew_scn_rba 0 1 0
mostly latch-free SCN kcs01 0 1 2
object queue header operation kcbo_ivbo 0 40 8
object queue header operation kcbw_link_q 0 6 0
object queue header operation kcbo_htab_size_ck 0 1 4
object stats modification ksoslinelm 0 1 1
process queue reference kxfpqrsnd 0 1 0
redo allocation kcrfw_redo_gen: redo allocation 1 0 289 0
redo allocation kcrfw_redo_gen: redo allocation 3 0 16 110
redo allocation kcrfw_redo_write: before write 0 12 107
redo allocation kcrfw_redo_write: more space 0 6 117
redo allocation kcrfw_redo_gen: redo allocation 2 0 2 0
redo writing kcrfw_post: rba scn pair 0 2 0
row cache objects kqreqd: reget 0 70 0
row cache objects kqrpre: find obj 0 19 87
row cache objects kqreqd 0 5 6
sequence cache kdnssd 0 29 0
sequence cache kdnnxt: cached seq 0 11 0
sequence cache kdnss 0 1 42
session allocation ksuxds: not user session 0 58 43
session allocation ksucri 0 46 112
session allocation ksuprc 0 29 23
session allocation ksuxds: KSUSFCLC not set 0 28 8
session allocation ksudlc 0 24 9
session allocation ksursi 0 5 4
session allocation ksudlp 0 4 0
session allocation kxfprdp 0 3 1
session allocation kxfpqidqr 0 2 0
session allocation ksucrp 0 1 1
session allocation ksufap: active sessions 0 1 0
session idle bit ksupuc: set busy 0 605 336
session idle bit ksupuc: clear busy 0 199 431
session idle bit ksuxds 0 34 71
shared pool kghupr1 0 2,913 2,999
shared pool kghalo 0 123 94
shared pool kghfre 0 88 34
shared pool kghalp 0 6 4
shared pool kghfen: not perm alloc class 0 1 0
simulator hash latch kcbsacc: lookup dba 0 6 6
simulator lru latch kcbs_simulate: simulate set 0 9 9
sort extent pool ktstsmonmsg 0 1 0
undo global data kturimugur: child 0 148 41
undo global data ktudba: KSLBEGIN 0 63 178
undo global data ktucof: at start 0 9 1
undo global data ktudnx:child 0 6 6
undo global data ktibnd: child 0 3 7

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,909,808 5.15
GL APPS_TS_TX_IDX GL_CODE_COMBINATIONS_U1   INDEX 9,283,568 4.02
QP APPS_TS_TX_IDX QP_LIST_LINES_N5   INDEX 7,676,336 3.32
SYS SYSTEM COLTYPE$   TABLE 6,232,384 2.70
GL APPS_TS_TX_INTERFACE GL_INTERFACE_N2   INDEX 4,934,608 2.13

Back to Segment Statistics
Back to Top

Segments by Physical Reads

Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
OSM APPS_TS_TX_DATA AS_SALES_LEADS   TABLE 19,178 10.12
APPLSYS APPS_TS_TX_IDX WF_ITEM_ATTRIBUTE_VALUES_PK   INDEX 12,811 6.76
SYS SYSTEM COL$   TABLE 11,048 5.83
APPLSYS APPS_TS_TX_DATA WF_ITEM_ATTRIBUTE_VALUES   TABLE 9,570 5.05
GL APPS_TS_TX_DATA GL_JE_LINES   TABLE 9,020 4.76

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 4,021 24.98
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N6   INDEX 855 5.31
APPLSYS APPS_TS_TX_DATA WF_ITEM_ACTIVITY_STATUSES WF_ITEM49 TABLE PARTITION 839 5.21
AR APPS_TS_PAY_DATA RA_CUST_TRX_LINE_GL_DIST_ALL   TABLE 814 5.06
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 805 5.00

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,214 43.27
APPLSYS APPS_TS_TX_IDX WF_ITEM_ACTIVITY_STATUSES_N1   INDEX 922 12.41
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N5   INDEX 466 6.27
HR APPS_TS_PAY_IDX PAY_RUN_RESULT_VALUES_PK   INDEX 262 3.53
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 246 3.31

Back to Segment Statistics
Back to Top

Segments by ITL Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type ITL Waits %Total
APPLSYS APPS_TS_TX_IDX WF_ITEM_ATTRIBUTE_VALUES_PK   INDEX 6 37.50
GL APPS_TS_TX_IDX GL_JE_LINES_N1   INDEX 2 12.50
PO APPS_TS_TX_IDX PO_REQUISITION_LINES_N9   INDEX 2 12.50
APPLSYS APPS_TS_TX_IDX WF_ITEM_ACTIVITY_STATUSES_N4   INDEX 1 6.25
AR APPS_TS_PAY_IDX RA_CUST_TRX_LINE_GL_DIST_N1   INDEX 1 6.25

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,775 49.91 0   2,775 949
dc_files 150 50.00 0   0 75
dc_free_extents 15 46.67 5 0.00 15 5
dc_global_oids 1,416,950 0.00 0   0 84
dc_histogram_data 15,568 2.09 0   51 1,518
dc_histogram_defs 191,541 3.80 0   2,571 30,722
dc_object_ids 3,072,965 0.03 0   290 6,628
dc_objects 81,420 1.98 0   2,079 10,294
dc_profiles 2,624 0.00 0   0 1
dc_rollback_segments 9,604,092 0.00 0   0 482
dc_segments 366,046 0.23 0   5,641 8,219
dc_sequences 14,497 0.14 0   14,497 161
dc_table_scns 21,054 0.00 0   0 14
dc_tablespace_quotas 5,149 0.35 0   5,149 52
dc_tablespaces 1,040,703 0.00 0   0 25
dc_used_extents 5 100.00 0   5 10
dc_usernames 100,010 0.00 0   0 88
dc_users 2,406,475 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 285,096 0.02 10,473,442 0.00 0 0
CLUSTER 285 0.00 348 0.00 0 0
INDEX 4,697 5.66 6,085 6.46 0 0
JAVA DATA 64 0.00 0   0 0
PIPE 17,961 2.04 23,393 1.57 0 0
SQL AREA 1,007,585 0.47 31,428,895 0.04 1,926 1,131
TABLE/PROCEDURE 173,841 0.46 14,949,634 0.01 74 0
TRIGGER 8,755 0.07 340,200 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,718,544 42,496,176 9.76
shared KQR L SO 116,736 137,216 17.54
shared KQR M PO 20,976,872 24,537,056 16.97
shared KQR M SO 527,872 708,632 34.24
shared KQR S PO 2,141,912 2,564,456 19.73
shared KQR S SO 7,680 8,452 10.05
shared KTI-UNDO 22,647,240 22,647,240 0.00
shared PL/SQL DIANA 26,767,912 29,375,004 9.74
shared PL/SQL MPCODE 67,385,276 69,834,388 3.63
shared PLS non-lib hp 10,904 10,904 0.00
shared PX subheap 41,500 41,500 0.00
shared QSMQUTL summar 8,948 10,404 16.27
shared enqueue 3,244,356 3,244,356 0.00
shared evaluation con 17,072 17,072 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 948,178,476 545,085,812 -42.51
shared joxlod exec hp 612,704 612,704 0.00
shared joxlod pcod hp 173,684 173,684 0.00
shared joxlod rsrc hp 7,216 7,216 0.00
shared joxs heap 4,220 4,220 0.00
shared library cache 279,267,040 392,596,612 40.58
shared miscellaneous 132,588,600 150,878,984 13.79
shared parameters 1,468,720 1,584,484 7.88
shared partitioning d 549,380 614,828 11.91
shared pl/sql source 14,080 14,080 0.00
shared private strands 21,964,800 21,964,800 0.00
shared repository 2,004,848 2,121,096 5.80
shared rule set evalu 68,768 68,768 0.00
shared sessions 6,888,000 6,888,000 0.00
shared sql area 280,429,924 536,981,012 91.48
shared synonym source 26,728 42,816 60.19
shared table definiti 45,700 53,460 16.98
shared temporary tabl 3,590,380 5,001,172 39.29
shared trigger defini 114,244 121,952 6.75
shared trigger inform 1,508 1,508 0.00
shared trigger source 7,440 7,440 0.00
shared type object de 542,460 704,240 29.82
  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