WORKLOAD REPOSITORY report for

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

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	834	11-Mar-06 21:15:31	1543	178.7
End Snap:	836	11-Mar-06 22:15:37	1818	194.4
Elapsed:		60.11 (mins)
DB Time:		309.57 (mins)

 

Report Summary

Cache Sizes (end)

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

Load Profile

	Per Second	Per Transaction
Redo size:	2,157,371.85	27,213.71
Logical reads:	100,708.36	1,270.36
Block changes:	13,265.62	167.34
Physical reads:	137.57	1.74
Physical writes:	229.01	2.89
User calls:	3,283.27	41.42
Parses:	739.19	9.32
Hard parses:	1.89	0.02
Sorts:	917.91	11.58
Logons:	0.69	0.01
Executes:	6,673.24	84.18
Transactions:	79.28

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

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	99.99	Redo NoWait %:	100.00
Buffer Hit %:	99.87	In-memory Sort %:	100.00
Library Hit %:	99.97	Soft Parse %:	99.74
Execute to Parse %:	88.92	Latch Hit %:	99.63
Parse CPU to Parse Elapsd %:	87.85	% Non-Parse CPU:	96.76

Shared Pool Statistics

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

Top 5 Timed Events

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

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• SGA Statistics
• Resource Limit Statistics
• init.ora Parameters

Back to Top

Wait Events Statistics

• Wait Events
• Background Wait Events
• Time Model Statistics
• Operating System Statistics
• Service Statistics
• Service Wait Class Stats

Back to Top

Wait Events

• s - second
• cs - centisecond - 100th of a second
• ms - millisecond - 1000th of a second
• us - microsecond - 1000000th of a second
• ordered by wait time desc, waits desc (idle events last)

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

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)

Event	Waits	Timeouts	Total Wait Time (s)	Avg wait (ms)	Waits /txn
log file parallel write	428,353	0	448	1	1.50
db file parallel write	52,693	0	10	0	0.18
LGWR wait for redo copy	5,312	8	2	0	0.02
control file parallel write	1,606	0	1	1	0.01
latch: cache buffers chains	155	121	1	6	0.00
control file sequential read	694	0	1	1	0.00
buffer busy waits	560	0	1	1	0.00
latch free	106	93	1	6	0.00
rdbms ipc reply	2,716	0	1	0	0.01
latch: library cache	180	0	0	2	0.00
latch: redo allocation	63	0	0	3	0.00
direct path read	250	0	0	1	0.00
process startup	4	0	0	34	0.00
latch: library cache pin	15	0	0	4	0.00
latch: shared pool	48	0	0	1	0.00
latch: session allocation	8	0	0	4	0.00
control file single write	132	0	0	0	0.00
log file sequential read	4	0	0	6	0.00
latch: In memory undo latch	5	0	0	4	0.00
latch: cache buffers lru chain	16	0	0	1	0.00
latch: library cache lock	6	0	0	2	0.00
latch: undo global data	9	0	0	1	0.00
latch: enqueue hash chains	3	0	0	1	0.00
direct path write	150	0	0	0	0.00
latch: messages	1	0	0	1	0.00
log file single write	4	0	0	0	0.00
latch: object queue header operation	3	0	0	0	0.00
buffer deadlock	1	1	0	0	0.00
rdbms ipc message	266,313	9,808	27,588	104	0.93
Queue Monitor Wait	120	120	3,516	29,298	0.00
smon timer	8,587	0	3,403	396	0.03
wakeup time manager	1,141	436	3,222	2,824	0.00

Back to Wait Events Statistics
Back to Top

Time Model Statistics

• ordered by Time (seconds) desc

Statistic Name	Time (seconds)	% Total DB Time
DB time	18,574.12	100.00
sql execute elapsed time	14,435.69	77.72
DB CPU	14,407.43	77.57
inbound PL/SQL rpc elapsed time	3,867.30	20.82
PL/SQL execution elapsed time	3,735.53	20.11
background elapsed time	1,150.07	6.19
background cpu time	607.96	3.27
parse time elapsed	533.94	2.87
Java execution elapsed time	355.69	1.91
hard parse elapsed time	301.66	1.62
connection management call elapsed time	17.85	0.10
sequence load elapsed time	14.71	0.08
PL/SQL compilation elapsed time	8.20	0.04
failed parse elapsed time	2.51	0.01
hard parse (sharing criteria) elapsed time	1.16	0.01
hard parse (bind mismatch) elapsed time	1.07	0.01
failed parse (out of shared memory) elapsed t	0.00	0.00

Back to Wait Events Statistics
Back to Top

 

Operating System Statistics

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

Service Name	DB Time (s)	DB CPU (s)	Physical Reads	Logical Reads
VIS	14,147.30	11,484.90	295,563	303,560,446
SYS$USERS	4,429.00	2,920.50	134,429	36,901,403
SYS$BACKGROUND	0.00	0.00	63,999	22,709,919

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in centisecond (100th of a second)

Service Name	User I/O Total Wts	User I/O Wt Time	Concurcy Total Wts	Concurcy Wt Time	Admin Total Wts	Admin Wt Time	Network Total Wts	Network Wt Time
VIS	869,809	139,272	57,312	9,838	0	0	8,544,406	11,208
SYS$USERS	134,304	83,920	5,277	1,601	9	23	4,934,505	3,184
SYS$BACKGROUND	74,796	9,733	963	222	0	0	1	0

Back to Wait Events Statistics
Back to Top

 

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

Back to Top

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
886	877	2	442.83	4.77	ghw9svn8x4vzu	WSHPSRS	BEGIN WSH_PICK_LIST.RELEASE_BA...
734	709	4	183.38	3.95	bkxrrs52m7d8p	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
487	449	57	8.54	2.62	0z318y6g3uagc	INCTCW	declare l_retstat varchar2 ( 3...
447	426	4	111.68	2.41	7g05xfvc5vp74	ARTERRPW	BEGIN arp_run.revenue_recognit...
395	382	26,047	0.02	2.13	6h2tpcxpymqu7	JDBC Thin Client	BEGIN :1 := FND_AOLJ_UTIL.is_V...
388	21	561	0.69	2.09	3dssuqfjsynrk	asn.lead.server.LeadUwqAM	SELECT count(1) FROM (SELECT *...
353	309	4	88.28	1.90	8c30hkqv9n7cr	OEHVIMP	BEGIN OE_BULK_ORDER_IMPORT_PVT...
332	297	336	0.99	1.79	bt0w082dfrm8v	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
284	146	5,000	0.06	1.53	64qc329wgjv39	PYUSLV	select /*+ ORDERED INDEX(ASS...
283	82	561	0.50	1.52	8c8cf4vtv4tx2	asn.lead.server.LeadUwqAM	SELECT * FROM ( SELECT LeadEO....
254	239	1,000	0.25	1.37	2fjd6cndjby9p	ARTERRPW	INSERT INTO RA_CUST_TRX_LINE_G...
238	225	18,247	0.01	1.28	3prk3vg9u0abb	JDBC Thin Client	BEGIN FND_GLOBAL.APPS_INITIA...
208	165	5,055	0.04	1.12	1q78dwnrrkf6c	asn.lead.server.LeadUwqAM	BEGIN ASN_SALES_PVT.Lead_Proce...
190	11	953	0.20	1.02	72cqcsuq5wgh0	asn.dashboard.server.DashboardAM	SELECT aslrt.meaning as rank_n...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

CPU Time (s)	Elapsed Time (s)	Executions	CPU per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
877	886	2	438.51	4.77	ghw9svn8x4vzu	WSHPSRS	BEGIN WSH_PICK_LIST.RELEASE_BA...
709	734	4	177.30	3.95	bkxrrs52m7d8p	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
449	487	57	7.88	2.62	0z318y6g3uagc	INCTCW	declare l_retstat varchar2 ( 3...
426	447	4	106.48	2.41	7g05xfvc5vp74	ARTERRPW	BEGIN arp_run.revenue_recognit...
382	395	26,047	0.01	2.13	6h2tpcxpymqu7	JDBC Thin Client	BEGIN :1 := FND_AOLJ_UTIL.is_V...
309	353	4	77.13	1.90	8c30hkqv9n7cr	OEHVIMP	BEGIN OE_BULK_ORDER_IMPORT_PVT...
297	332	336	0.88	1.79	bt0w082dfrm8v	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
239	254	1,000	0.24	1.37	2fjd6cndjby9p	ARTERRPW	INSERT INTO RA_CUST_TRX_LINE_G...
225	238	18,247	0.01	1.28	3prk3vg9u0abb	JDBC Thin Client	BEGIN FND_GLOBAL.APPS_INITIA...
167	172	11,270	0.01	0.93	4yak7x2zqz0dh	WSHFRREL	BEGIN inv_transfer_order_pvt.f...
165	208	5,055	0.03	1.12	1q78dwnrrkf6c	asn.lead.server.LeadUwqAM	BEGIN ASN_SALES_PVT.Lead_Proce...
146	284	5,000	0.03	1.53	64qc329wgjv39	PYUSLV	select /*+ ORDERED INDEX(ASS...
82	283	561	0.15	1.52	8c8cf4vtv4tx2	asn.lead.server.LeadUwqAM	SELECT * FROM ( SELECT LeadEO....
21	388	561	0.04	2.09	3dssuqfjsynrk	asn.lead.server.LeadUwqAM	SELECT count(1) FROM (SELECT *...
11	190	953	0.01	1.02	72cqcsuq5wgh0	asn.dashboard.server.DashboardAM	SELECT aslrt.meaning as rank_n...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 363,223,837
• Captured SQL accounts for 22.9% of Total Buffer Gets

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
26,053,715	4	6,513,428.75	7.17	709.19	733.51	bkxrrs52m7d8p	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
22,190,641	2	11,095,320.50	6.11	877.01	885.66	ghw9svn8x4vzu	WSHPSRS	BEGIN WSH_PICK_LIST.RELEASE_BA...
22,186,874	4	5,546,718.50	6.11	425.94	446.72	7g05xfvc5vp74	ARTERRPW	BEGIN arp_run.revenue_recognit...
12,615,093	1	12,615,093.00	3.47	59.05	140.60	05s9358mm6vrr		begin dbms_feature_usage_inter...
11,857,093	5,000	2,371.42	3.26	145.69	283.81	64qc329wgjv39	PYUSLV	select /*+ ORDERED INDEX(ASS...
11,179,037	1	11,179,037.00	3.08	45.68	106.65	2vpmsug2fa9rf	MMON_SLAVE	select atc + ix, NULL, NULL ...
10,997,123	4	2,749,280.75	3.03	308.50	353.12	8c30hkqv9n7cr	OEHVIMP	BEGIN OE_BULK_ORDER_IMPORT_PVT...
9,240,539	1,000	9,240.54	2.54	239.37	253.66	2fjd6cndjby9p	ARTERRPW	INSERT INTO RA_CUST_TRX_LINE_G...
8,495,317	336	25,283.68	2.34	296.55	332.32	bt0w082dfrm8v	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
7,938,284	57	139,268.14	2.19	448.89	486.77	0z318y6g3uagc	INCTCW	declare l_retstat varchar2 ( 3...
6,352,464	9	705,829.33	1.75	83.91	87.89	1du9v021t8m46	ARGLTP	insert into gl_interface(creat...
5,806,311	9,999	580.69	1.60	47.44	51.41	546kskr40arst	PYUSLV	select /*+ ORDERED USE_NL(AA...
5,406,167	11,270	479.70	1.49	166.87	172.05	4yak7x2zqz0dh	WSHFRREL	BEGIN inv_transfer_order_pvt.f...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• Total Disk Reads: 496,167
• Captured SQL accounts for 44.7% of Total Disk Reads

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

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• Total Executions: 24,068,300
• Captured SQL accounts for 19.9% of Total Executions

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

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 2,666,020
• Captured SQL accounts for 53.8% of Total Parse Calls

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

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

• Only Statements with Sharable Memory greater than 1048576 are displayed

Sharable Mem (b)	Executions	% Total	SQL Id	SQL Module	SQL Text
1,666,151	1,680	0.09	8ay7m8qubw98j	POXBWVRP	select * from PO_TAX_LINES_SUM...
1,477,905	92	0.08	azfadzhfxbxj3	ARXTWMAI	SELECT ROW_ID, APBA_INACTIVE_D...
1,444,040	55	0.07	2fr5f5xzmffd8	POXPRPOP	SELECT decode ( : P_sortby , ...
1,372,636	1	0.07	9trqpr8qyg7xm	POXPRPOP	SELECT distinct pol . line_num...
1,219,213	1,682	0.06	bf4kcv5v66czb	ibuSRDetails.jsp	SELECT 'NOTE', '2', b.creat...
1,209,570	4,877	0.06	8q8zmrwmrccz4	OEXOETEL	SELECT /*+ ORDERED USE_NL(adj ...
1,116,337	1,680	0.06	bk9zgysk994av	OEXOETEL	SELECT /*+ ORDERED USE_NL(qpq ...

Back to SQL Statistics
Back to Top

 

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

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

Back to SQL Statistics
Back to Top

 

Instance Activity Statistics

• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Back to Top

Instance Activity Stats

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

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)
• Statistics identified by '(derived)' come from sources other than SYSSTAT

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

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

Statistic	Total	per Hour
log switches (derived)	2	2.00

Back to Instance Activity Statistics
Back to Top

 

IO Stats

• Tablespace IO Stats
• File IO Stats

Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Tablespace	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
APPS_TS_TX_DATA	216,766	60	5.57	1.05	42,647	12	4,105	1.65
APPS_UNDOTS1	1,376	0	1.99	1.00	242,786	67	16,981	0.20
APPS_TS_TX_IDX	133,824	37	6.18	1.00	69,750	19	6,086	1.23
SYSTEM	39,981	11	3.02	1.59	2,620	1	494	1.70
APPS_TS_TX_INTERFACE	5,634	2	3.05	1.33	17,326	5	61	0.16
APPS_TS_PAY_IDX	8,173	2	2.44	1.00	11,369	3	6,953	0.54
APPS_TS_PAY_DATA	8,378	2	3.07	1.00	5,798	2	2,934	0.50
TEMP	3,313	1	16.34	6.82	10,820	3	57	3.33
SYSAUX	3,742	1	2.49	1.23	7,679	2	0	0.00
APPS_TS_SEED	7,278	2	6.60	1.31	854	0	258	4.34
APPS_TS_QUEUES	4,681	1	1.97	1.01	3,338	1	17	1.76
CTXSYS	824	0	5.83	1.00	1,238	0	0	0.00
APPS_TS_SUMMARY	415	0	3.28	1.00	1,247	0	102	0.00
APPS_TS_ARCHIVE	266	0	6.24	1.00	438	0	2	0.00
APPS_TS_NOLOGGING	51	0	5.69	1.00	37	0	0	0.00
OLAP	16	0	3.13	1.69	2	0	0	0.00
APPS_TS_MEDIA	9	0	8.89	1.00	6	0	0	0.00
ODM	7	0	1.43	1.00	2	0	0	0.00
OWAPUB	5	0	20.00	1.00	2	0	0	0.00
PORTAL	5	0	10.00	1.00	2	0	0	0.00
SYNCSERVER	5	0	6.00	1.00	2	0	0	0.00

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File

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

Back to IO Stats
Back to Top

 

Buffer Pool Statistics

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

P	Number of Buffers	Pool Hit%	Buffer Gets	Physical Reads	Physical Writes	Free Buff Wait	Writ Comp Wait	Buffer Busy Waits
D	172,516	100	362,905,717	474,633	802,699	0	0	38,050

Back to Top

 

Advisory Statistics

• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• Java Pool Advisory

Back to Top

Instance Recovery Stats

• B: Begin snapshot, E: End snapshot

	Targt MTTR (s)	Estd MTTR (s)	Recovery Estd IOs	Actual Redo Blks	Target Redo Blks	Log File Size Redo Blks	Log Ckpt Timeout Redo Blks	Log Ckpt Interval Redo Blks
B	0	86	43,839	1,654,325	2,476,444	17,510,400	2,476,444
E	0	38	14,759	302,356	2,483,789	17,510,400	2,483,789

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

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

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

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

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

	PGA Aggr Target(M)	Auto PGA Target(M)	PGA Mem Alloc(M)	W/A PGA Used(M)	%PGA W/A Mem	%Auto W/A Mem	%Man W/A Mem	Global Mem Bound(K)
B	30,720	22,473	8,654.99	0.09	0.00	100.00	0.00	102,400
E	30,720	20,438	11,134.25	0.09	0.00	100.00	0.00	102,400

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations

Low Optimal	High Optimal	Total Execs	Optimal Execs	1-Pass Execs	M-Pass Execs
2K	4K	1,675,319	1,675,319	0	0
64K	128K	5,357	5,357	0	0
128K	256K	313	313	0	0
256K	512K	300	300	0	0
512K	1024K	95,520	95,520	0	0
1M	2M	82	82	0	0
2M	4M	10	10	0	0
8M	16M	2	2	0	0
128M	256M	2	0	2	0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

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

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Shared Pool Size(M)	SP Size Factr	Est LC Size (M)	Est LC Mem Obj	Est LC Time Saved (s)	Est LC Time Saved Factr	Est LC Load Time (s)	Est LC Load Time Factr	Est LC Mem Obj Hits
1,088	0.59	193	16,035	27,140	1.00	764	1.00	39,374,527
1,280	0.69	371	30,861	27,143	1.00	761	1.00	39,375,245
1,472	0.79	419	35,830	27,143	1.00	761	1.00	39,375,245
1,664	0.90	419	35,830	27,143	1.00	761	1.00	39,375,245
1,856	1.00	419	35,830	27,143	1.00	761	1.00	39,375,245
2,048	1.10	419	35,830	27,143	1.00	761	1.00	39,375,245
2,240	1.21	419	35,830	27,143	1.00	761	1.00	39,375,245
2,432	1.31	419	35,830	27,143	1.00	761	1.00	39,375,245
2,624	1.41	419	35,830	27,143	1.00	761	1.00	39,375,245
2,816	1.52	419	35,830	27,143	1.00	761	1.00	39,375,245
3,008	1.62	419	35,830	27,143	1.00	761	1.00	39,375,245
3,200	1.72	419	35,830	27,143	1.00	761	1.00	39,375,245
3,392	1.83	419	35,830	27,143	1.00	761	1.00	39,375,245
3,584	1.93	419	35,830	27,143	1.00	761	1.00	39,375,245
3,776	2.03	419	35,830	27,143	1.00	761	1.00	39,375,245

Back to Advisory Statistics
Back to Top

Java Pool Advisory

Java Pool Size(M)	JP Size Factr	Est LC Size (M)	Est LC Mem Obj	Est LC Time Saved (s)	Est LC Time Saved Factr	Est LC Load Time (s)	Est LC Load Time Factr	Est LC Mem Obj Hits
16	0.50	9	264	319	1.00	350	1.00	5,648
32	1.00	9	264	319	1.00	350	1.00	5,648
48	1.50	9	264	319	1.00	350	1.00	5,648
64	2.00	9	264	319	1.00	350	1.00	5,648

Back to Advisory Statistics
Back to Top

 

Wait Statistics

• Buffer Wait Statistics
• Enqueue Activity

Back to Top

Buffer Wait Statistics

• ordered by wait time desc, waits desc

Class	Waits	Total Wait Time (s)	Avg Time (ms)
data block	20,790	21	1
undo block	14,812	2	0
undo header	2,166	1	1
file header block	54	0	4
segment header	85	0	2
2nd level bmb	21	0	3
1st level bmb	118	0	0
bitmap index block	3	0	0
extent map	1	0	0

Back to Wait Statistics
Back to Top

Enqueue Activity

• Enqueue stats gathered prior to 10i should not be compared with 10i data
• ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)	Requests	Succ Gets	Failed Gets	Waits	Wt Time (s)	Av Wt Time(ms)
TX-Transaction (row lock contention)	1,873	111	1,762	111	50	453.60
TM-DML	1,469,520	1,469,510	0	6	27	4,446.67
TX-Transaction (index contention)	2,300	2,300	0	2,222	13	5.68
UL-User-defined	22,606	20,900	1,706	41	8	192.93
SQ-Sequence Cache	12,662	12,662	0	1,691	2	0.94
RO-Multiple Object Reuse (fast object reuse)	1,246	1,246	0	174	1	3.97
DV-Diana Versioning	6,536	6,536	0	38	1	13.68
TX-Transaction	330,868	330,868	0	72	0	2.08
HW-Segment High Water Mark	78,479	78,477	2	41	0	3.17
CU-Cursor	6,353	6,353	0	3	0	16.67
TX-Transaction (allocate ITL entry)	11	11	0	7	0	2.86
FB-Format Block	7,045	7,045	0	20	0	0.00
PS-PX Process Reservation	624	520	104	8	0	0.00

Back to Wait Statistics
Back to Top

 

Undo Statistics

• Undo Segment Summary
• Undo Segment Stats

Back to Top

Undo Segment Summary

• Undo segment block stats:
• uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
• eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo TS#	Undo Blocks	Num Trans	Max Qry Len (s)	Max Tx Concurcy	Snap TooOld	OutOf Space	uS/uR/uU/ eS/eR/eU
1	486,747	363,669	2,648	118	0	0	0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

• ordered by Time desc

End Time	Undo Blocks	Num Trans	Max Qry Len (s)	Max Tx Concy	Snap TooOld	OutOf Space	uS/uR/uU/ eS/eR/eU
11-Mar 22:23	15,733	31,796	654	111	0	0	0/0/0/0/0/0
11-Mar 22:13	17,691	33,325	447	111	0	0	0/0/0/0/0/0
11-Mar 22:03	58,603	48,570	465	118	0	0	0/0/0/0/0/0
11-Mar 21:53	163,260	59,159	657	117	0	0	0/0/0/0/0/0
11-Mar 21:43	82,878	70,419	2,648	116	0	0	0/0/0/0/0/0
11-Mar 21:33	38,870	51,087	2,045	114	0	0	0/0/0/0/0/0
11-Mar 21:23	109,712	69,313	1,554	116	0	0	0/0/0/0/0/0

Back to Undo Statistics
Back to Top

 

Latch Statistics

• Latch Activity
• Latch Sleep Breakdown
• Latch Miss Sources
• Parent Latch Statistics
• Child Latch Statistics

Back to Top

Latch Activity

• "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
• "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
• "Pct Misses" for both should be very close to 0.0

Latch Name	Get Requests	Pct Get Miss	Avg Slps /Miss	Wait Time (s)	NoWait Requests	Pct NoWait Miss
Consistent RBA	214,938	0.03	0.00	0	0
FOB s.o list latch	24,333	0.01	0.00	0	0
In memory undo latch	56,383,048	0.92	0.00	1	748,268	0.00
JOX SGA heap latch	113,495	0.01	0.00	0	0
JS queue state obj latch	21,630	0.00		0	0
JS slv state obj latch	3	0.00		0	0
KTF sga enqueue	0			0	1,214	0.00
KWQMN job cache list latch	465,167	0.04	1.38	2	0
MQL Tracking Latch	0			0	72	0.00
Memory Management Latch	18,030	0.00		0	1,201	0.00
PL/SQL warning settings	842,118	0.02	0.00	0	0
SQL memory manager latch	2	0.00		0	1,200	0.00
SQL memory manager workarea list latch	712,430	0.00	0.00	0	0
SWRF Alerted Metric Element list	112,195	0.00		0	0
Shared B-Tree	2	0.00		0	0
active checkpoint queue latch	57,945	0.00	0.00	0	0
active service list	10,113	0.10	0.00	0	0
archive control	46	0.00		0	0
begin backup scn array	332	0.00		0	0
cache buffer handles	7,576,081	0.16	0.00	0	0
cache buffers chains	779,149,621	0.33	0.00	52	2,834,989	0.83
cache buffers lru chain	2,624,729	0.27	0.01	0	5,367,911	0.32
channel handle pool latch	97,828	0.01	0.00	0	0
channel operations parent latch	341,776	0.00	0.13	0	0
checkpoint queue latch	2,041,110	0.01	0.00	0	756,934	0.01
child cursor hash table	74,390	0.00		0	0
client/application info	395,259	0.00	0.00	0	0
commit callback allocation	36,485	0.00		0	0
compile environment latch	885,382	0.01	0.00	0	0
cursor bind value capture	1,460	0.07	0.00	0	16,107	0.00
dictionary lookup	369	0.00		0	0
dml lock allocation	2,934,983	0.10	0.01	0	0
dummy allocation	4,704	1.02	0.00	0	0
enqueue hash chains	13,905,697	0.03	0.01	0	3	0.00
enqueues	11,164,070	0.08	0.00	0	0
error message lists	272	6.99	0.00	0	0
event group latch	1,236	0.00		0	0
file cache latch	12,937	0.00		0	0
global KZLD latch for mem in SGA	2,346	0.00		0	0
global ctx hash table latch	5,410	0.00		0	0
hash table column usage latch	1,519	0.00		0	4,396,997	0.01
hash table modification latch	194	0.00		0	0
job workq parent latch	0			0	2	0.00
job_queue_processes parameter latch	61	0.00		0	0
ksuosstats global area	244	0.00		0	0
ktm global data	9,095	0.00		0	0
lgwr LWN SCN	215,051	0.04	0.00	0	0
library cache	124,879,615	0.91	0.01	39	1,142	5.43
library cache load lock	4,304	0.00		0	1	0.00
library cache lock	15,503,543	0.05	0.01	0	0
library cache lock allocation	420,556	0.01	0.00	0	0
library cache pin	108,884,663	0.16	0.01	4	2	0.00
library cache pin allocation	429,077	0.00	0.06	0	0
list of block allocation	86,087	0.00	0.00	0	0
loader state object freelist	78,080	0.00	0.00	0	0
longop free list parent	66	0.00		0	66	0.00
message pool operations parent latch	98,742	0.00	0.00	0	0
messages	889,688	0.02	0.01	0	0
mostly latch-free SCN	218,894	1.59	0.01	0	0
multiblock read objects	18,962	0.06	0.09	0	0
ncodef allocation latch	61	0.00		0	0
object queue header heap	132,354	0.00		0	131,955	0.00
object queue header operation	12,174,342	0.01	0.01	0	0
object stats modification	24,407	0.02	0.00	0	0
parallel query alloc buffer	2,992	0.70	0.00	0	0
parallel query stats	176	15.34	0.00	0	0
parameter list	7,011	0.00		0	0
parameter table allocation management	4,704	1.38	0.00	0	0
post/wait queue	166,400	0.03	0.00	0	111,377	0.01
process allocation	2,494	0.04	0.00	0	1,236	0.00
process group creation	2,478	0.00		0	0
process queue	1,632	0.80	0.00	0	0
process queue reference	108,032	0.08	0.00	0	6,957	8.93
query server freelists	1,408	3.62	0.00	0	0
query server process	8	0.00		0	8	0.00
redo allocation	2,229,831	0.21	0.02	0	23,651,697	0.35
redo copy	0			0	23,670,728	0.04
redo writing	709,287	0.01	0.00	0	0
row cache objects	50,362,490	0.05	0.00	0	0
rules engine statistics	10,216	0.00		0	0
sequence cache	4,900,391	0.18	0.00	0	0
session allocation	4,076,680	0.17	0.01	1	0
session idle bit	24,567,939	0.03	0.07	2	0
session switching	3,903	0.00		0	0
session timer	1,216	0.00		0	0
shared pool	42,360,696	0.84	0.00	1	0
simulator hash latch	25,673,313	0.00	0.01	0	0
simulator lru latch	27,531	0.17	0.02	0	289,433	0.84
slave class	4	0.00		0	0
slave class create	15	6.67	1.00	0	0
sort extent pool	310,864	0.04	0.01	0	0
state object free list	6	0.00		0	0
statistics aggregation	168	0.00		0	0
temp lob duration state obj allocation	95	0.00		0	0
temporary table state object allocation	2,843	0.14	0.00	0	0
threshold alerts latch	312	0.00		0	0
trace latch	2	0.00		0	0
transaction allocation	31,999	0.00		0	0
transaction branch allocation	61	0.00		0	0
undo global data	78,036,452	0.28	0.00	0	0
user lock	8,832	0.01	0.00	0	0

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

• ordered by misses desc

Latch Name	Get Requests	Misses	Sleeps	Spin Gets	Sleep1	Sleep2	Sleep3
cache buffers chains	779,149,621	2,546,343	11,436	2,537,479	6,381	2,404	76
library cache	124,879,615	1,133,754	12,906	1,120,955	12,693	105	1
In memory undo latch	56,383,048	520,876	299	520,591	271	14	0
shared pool	42,360,696	354,042	1,478	352,579	1,448	15	0
undo global data	78,036,452	218,659	114	218,546	112	1	0
library cache pin	108,884,663	177,543	1,310	176,241	1,294	8	0
row cache objects	50,362,490	23,149	30	23,119	30	0	0
cache buffer handles	7,576,081	12,095	6	12,089	6	0	0
enqueues	11,164,070	8,999	21	8,979	19	1	0
sequence cache	4,900,391	8,724	15	8,709	15	0	0
cache buffers lru chain	2,624,729	7,167	56	7,113	52	2	0
library cache lock	15,503,543	7,010	40	6,970	40	0	0
session allocation	4,076,680	6,813	92	6,721	92	0	0
session idle bit	24,567,939	6,163	405	5,949	26	185	3
enqueue hash chains	13,905,697	4,740	26	4,715	24	1	0
redo allocation	2,229,831	4,641	91	4,565	61	15	0
mostly latch-free SCN	218,894	3,485	49	3,436	49	0	0
dml lock allocation	2,934,983	2,911	17	2,894	17	0	0
object queue header operation	12,174,342	1,515	18	1,497	18	0	0
simulator hash latch	25,673,313	363	2	361	2	0	0
messages	889,688	190	1	189	1	0	0
KWQMN job cache list latch	465,167	187	258	52	24	104	7
sort extent pool	310,864	132	1	131	1	0	0
simulator lru latch	27,531	48	1	47	1	0	0
library cache pin allocation	429,077	18	1	17	1	0	0
channel operations parent latch	341,776	15	2	14	0	1	0
multiblock read objects	18,962	11	1	10	1	0	0
slave class create	15	1	1	0	1	0	0

Back to Latch Statistics
Back to Top

Latch Miss Sources

• only latches with sleeps are shown
• ordered by name, sleeps desc

Latch Name	Where	NoWait Misses	Sleeps	Waiter Sleeps
In memory undo latch	kturbk	0	260	273
In memory undo latch	ktiFlush: child	0	30	18
In memory undo latch	kticmt: child	0	5	0
In memory undo latch	ktichg: child	0	2	6
In memory undo latch	ktiTxnPoolFree	0	1	0
In memory undo latch	ktbgfc	0	1	2
KWQMN job cache list latch	kwqmnuji: update job item	0	258	170
cache buffer handles	kcbzfs	0	3	0
cache buffer handles	kcbzgs	0	3	6
cache buffers chains	kcbchg: kslbegin: bufs not pinned	0	6,503	2,916
cache buffers chains	kcbgtcr: fast path	0	1,817	5,309
cache buffers chains	kcbchg: kslbegin: call CR func	0	690	1,335
cache buffers chains	kcbgtcr: kslbegin excl	0	689	801
cache buffers chains	kcbzgb: scan from tail. nowait	0	442	0
cache buffers chains	kcbgcur: kslbegin	0	380	301
cache buffers chains	kcbrls: kslbegin	0	281	365
cache buffers chains	kcbget: pin buffer	0	226	214
cache buffers chains	kcbnew: new latch again	0	134	16
cache buffers chains	kcbgtcr: kslbegin shared	0	100	44
cache buffers chains	kcbzwb	0	45	4
cache buffers chains	kcbnlc	0	25	26
cache buffers chains	kcbget: exchange rls	0	22	1
cache buffers chains	kcbgtcr	0	19	0
cache buffers chains	kcbzib: finish free bufs	0	17	4
cache buffers chains	kcbget: exchange	0	15	2
cache buffers chains	kcbzib: multi-block read: nowait	0	10	0
cache buffers chains	kcbbxsv	0	5	4
cache buffers chains	kcbcge	0	4	15
cache buffers chains	kcbo_ivd_process	0	4	2
cache buffers chains	kcb_post_apply: kcbhq62	0	1	3
cache buffers chains	kcbesc: escalate	0	1	1
cache buffers lru chain	kcbzgws	0	43	0
cache buffers lru chain	kcbgtcr:CR Scan:KCBRSKIP	0	4	0
cache buffers lru chain	kcbzgb: posted for free bufs	0	4	32
cache buffers lru chain	kcbo_link_q	0	2	0
cache buffers lru chain	kcbbwlru	0	1	2
cache buffers lru chain	kcbbic2	0	1	4
cache buffers lru chain	kcbbxsv: move to being written	0	1	10
channel operations parent latch	ksrchdelete()	0	2	0
dml lock allocation	ktaiam	0	9	13
dml lock allocation	ktaidm	0	8	4
enqueue hash chains	ksqrcl	0	17	5
enqueue hash chains	ksqgtl3	0	9	21
enqueues	ksqgel: create enqueue	0	8	3
enqueues	ksqies	0	8	18
enqueues	ksqdel	0	5	0
library cache	kglupc: child	0	2,927	1,841
library cache	kglpndl: child: before processing	0	2,520	1,481
library cache	kglpnc: child	0	2,194	3,697
library cache	kglpnp: child	0	1,965	3,246
library cache	kglpndl: child: after processing	0	856	13
library cache	kglobpn: child:	0	382	1,093
library cache	kgldte: child 0	0	275	434
library cache	kglpin: child: heap processing	0	273	331
library cache	kglhdiv: child	0	153	0
library cache	kglhdgn: child:	0	76	251
library cache	kglupd: child	0	54	0
library cache	kglpin	0	53	39
library cache	kglget: set reserved lock	0	37	100
library cache	kglic	0	34	0
library cache	kglhdgc: child:	0	33	45
library cache	kglini: child	0	11	0
library cache	kgldti: 2child	0	9	5
library cache	kglidp: child	0	4	0
library cache	kglpin: child: KGLMX	0	3	0
library cache	kglobld	0	2	2
library cache	kglpur: child	0	2	0
library cache lock	kgllkdl: child: no lock handle	0	1,043	326
library cache lock	kgllkal: child: multiinstance	0	20	29
library cache lock	kgllkdl: child: cleanup	0	20	11
library cache pin	kglpndl	0	410	165
library cache pin	kglupc	0	277	184
library cache pin	kglpnc: child	0	275	393
library cache pin	kglpnp: child	0	264	351
library cache pin	kglpnal: child: alloc space	0	84	217
library cache pin allocation	kglpnget	0	1	1
messages	ksarcv: after wait	0	1	0
mostly latch-free SCN	kcs04	0	48	50
mostly latch-free SCN	kcs05	0	1	0
multiblock read objects	kcbzib: mbr get	0	1	0
object queue header operation	kcbo_ivbo	0	16	1
object queue header operation	kcbw_link_q	0	1	1
object queue header operation	kcbw_unlink_q	0	1	14
redo allocation	kcrfw_redo_gen: redo allocation 1	0	82	0
redo allocation	kcrfw_redo_gen: redo allocation 3	0	3	28
redo allocation	kcrfw_redo_write: before write	0	3	39
redo allocation	kcrfw_redo_write: more space	0	3	41
row cache objects	kqreqd: reget	0	26	0
row cache objects	kqreqd	0	2	0
row cache objects	kqrpdl	0	1	0
row cache objects	kqrpre: find obj	0	1	41
sequence cache	kdnssd	0	14	1
sequence cache	kdnnxt: cached seq	0	1	0
session allocation	ksuxds: not user session	0	29	31
session allocation	ksuxds: KSUSFCLC not set	0	19	7
session allocation	ksucri	0	16	35
session allocation	ksudlc	0	12	3
session allocation	ksuprc	0	9	11
session allocation	ksudlp	0	3	1
session allocation	ksursi	0	1	1
session allocation	ksusin	0	1	0
session allocation	kxfpqidqr	0	1	1
session allocation	kxfprdp	0	1	2
session idle bit	ksupuc: set busy	0	257	171
session idle bit	ksupuc: clear busy	0	116	205
session idle bit	ksuxds	0	32	29
shared pool	kghupr1	0	1,381	1,415
shared pool	kghalo	0	55	43
shared pool	kghfre	0	40	18
shared pool	kghalp	0	1	2
shared pool	kghfen: not perm alloc class	0	1	0
simulator hash latch	kcbsacc: lookup dba	0	2	2
simulator lru latch	kcbs_simulate: simulate set	0	1	1
slave class create	ksvcreate	0	1	0
sort extent pool	ktstdt: dealloc sort extent	0	1	0
undo global data	kturimugur: child	0	67	28
undo global data	ktudba: KSLBEGIN	0	31	78
undo global data	ktucof: at start	0	12	0
undo global data	ktufrbs: child	0	2	0
undo global data	ktusmasp: ktugd_tuux	0	1	2
undo global data	ktudnx:child	0	1	3

Back to Latch Statistics
Back to Top

 

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Segment Statistics

• Segments by Logical Reads
• Segments by Physical Reads
• Segments by Buffer Busy Waits
• Segments by Row Lock Waits
• Segments by ITL Waits

Back to Top

Segments by Logical Reads

• % Total shows % of logical reads for each top segment compared with total logical reads for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
GL	APPS_TS_TX_DATA	GL_CODE_COMBINATIONS		TABLE	11,843,376	5.54
GL	APPS_TS_TX_IDX	GL_CODE_COMBINATIONS_U1		INDEX	9,400,448	4.40
QP	APPS_TS_TX_IDX	QP_LIST_LINES_N5		INDEX	6,718,816	3.14
SYS	SYSTEM	COLTYPE$		TABLE	6,215,616	2.91
GL	APPS_TS_TX_INTERFACE	GL_INTERFACE_N2		INDEX	5,373,824	2.51

Back to Segment Statistics
Back to Top

Segments by Physical Reads

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Reads	%Total
APPLSYS	APPS_TS_TX_IDX	WF_ITEM_ATTRIBUTE_VALUES_PK		INDEX	11,910	8.22
SYS	SYSTEM	COL$		TABLE	9,986	6.89
APPLSYS	APPS_TS_TX_DATA	WF_ITEM_ATTRIBUTE_VALUES		TABLE	9,038	6.24
GL	APPS_TS_TX_DATA	GL_JE_LINES		TABLE	9,005	6.22
HR	APPS_TS_TX_IDX	PAY_BALANCE_CONTEXT_VALUES_PK		INDEX	8,212	5.67

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Buffer Busy Waits	%Total
AR	APPS_TS_PAY_IDX	RA_CUST_TRX_LINE_GL_DIST_U1		INDEX	3,865	23.00
AR	APPS_TS_PAY_DATA	RA_CUST_TRX_LINE_GL_DIST_ALL		TABLE	2,679	15.95
AR	APPS_TS_PAY_IDX	RA_CUST_TRX_LINE_GL_DIST_N1		INDEX	881	5.24
APPLSYS	APPS_TS_TX_DATA	WF_ITEM_ACTIVITY_STATUSES	WF_ITEM49	TABLE PARTITION	627	3.73
APPLSYS	APPS_TS_TX_IDX	WF_ITEM_ACTIVITY_STATUSES_N1		INDEX	564	3.36

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Row Lock Waits	%Total
APPLSYS	APPS_TS_TX_DATA	FND_CONCURRENT_REQUESTS		TABLE	3,146	42.43
APPLSYS	APPS_TS_TX_IDX	WF_ITEM_ACTIVITY_STATUSES_N1		INDEX	1,306	17.61
HR	APPS_TS_TX_IDX	PAY_COSTS_FK2		INDEX	435	5.87
WSH	APPS_TS_TX_DATA	WSH_TRIP_STOPS		TABLE	380	5.12
AR	APPS_TS_PAY_IDX	RA_CUST_TRX_LINE_GL_DIST_N1		INDEX	290	3.91

Back to Segment Statistics
Back to Top

Segments by ITL Waits

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	ITL Waits	%Total
HR	APPS_TS_TX_IDX	PAY_COSTS_FK2		INDEX	2	28.57
APPLSYS	APPS_TS_TX_IDX	WF_ITEM_ATTRIBUTE_VALUES_PK		INDEX	1	14.29
HR	APPS_TS_TX_IDX	PAY_ACTION_INTERLOCKS_FK2		INDEX	1	14.29
APPLSYS	APPS_TS_TX_IDX	WF_ITEM_ACTIVITY_STATUSES_N1		INDEX	1	14.29
AR	APPS_TS_PAY_IDX	RA_CUST_TRX_LINE_GL_DIST_N1		INDEX	1	14.29

Back to Segment Statistics
Back to Top

 

Dictionary Cache Stats

• "Pct Misses" should be very low (< 2% in most cases)
• "Final Usage" is the number of cache entries being used

Cache	Get Requests	Pct Miss	Scan Reqs	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	113	0.00	0		5	1
dc_constraints	2,751	49.91	0		2,751	961
dc_files	150	50.00	0		0	75
dc_free_extents	15	46.67	5	0.00	15	5
dc_global_oids	1,241,880	0.00	0		0	84
dc_histogram_data	15,430	2.05	0		51	1,537
dc_histogram_defs	178,739	4.08	0		2,571	30,817
dc_object_ids	2,682,530	0.03	0		290	6,645
dc_objects	74,912	2.17	0		2,052	10,314
dc_profiles	2,346	0.00	0		0	1
dc_rollback_segments	9,185,023	0.00	0		3	536
dc_segments	362,811	0.23	0		5,488	8,276
dc_sequences	13,734	0.15	0		13,734	161
dc_table_scns	19,064	0.01	0		0	14
dc_tablespace_quotas	4,997	0.32	0		4,997	50
dc_tablespaces	929,981	0.00	0		0	25
dc_used_extents	5	100.00	0		5	11
dc_usernames	89,910	0.00	0		0	88
dc_users	2,122,772	0.00	0		0	121
outstanding_alerts	126	0.00	0		0	20

Back to Top

 

Library Cache Activity

• "Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
BODY	248,222	0.02	9,384,931	0.00	0	0
CLUSTER	285	0.00	348	0.00	0	0
INDEX	4,173	6.33	5,551	7.04	0	0
JAVA DATA	61	0.00	0		0	0
PIPE	16,569	2.00	21,363	1.55	0	0
SQL AREA	993,335	0.46	28,794,017	0.04	1,806	1,116
TABLE/PROCEDURE	154,104	0.52	13,525,643	0.02	73	0
TRIGGER	7,825	0.08	322,896	0.00	0	0

Back to Top

 

SGA Statistics

• SGA Memory Summary
• SGA breakdown difference

Back to Top

SGA Memory Summary

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

Back to SGA Statistics
Back to Top

SGA breakdown difference

Pool	Name	Begin value	End value	% Diff
java	free memory	21,099,584	21,099,584	0.00
java	joxlod exec hp	11,882,304	11,882,304	0.00
java	joxs heap	572,544	572,544	0.00
shared	ASH buffers	16,777,216	16,777,216	0.00
shared	KGLF Frame Hea	9,580	9,580	0.00
shared	KGLS heap	38,698,424	42,484,216	9.78
shared	KQR L SO	116,736	137,216	17.54
shared	KQR M PO	21,035,592	24,604,512	16.97
shared	KQR M SO	541,724	718,404	32.61
shared	KQR S PO	2,177,612	2,595,720	19.20
shared	KQR S SO	7,700	8,468	9.97
shared	KTI-UNDO	22,647,240	22,647,240	0.00
shared	PL/SQL DIANA	26,798,372	29,415,488	9.77
shared	PL/SQL MPCODE	67,413,936	69,850,412	3.61
shared	PLS non-lib hp	10,904	10,904	0.00
shared	PX subheap	38,724	38,724	0.00
shared	QSMQUTL summar	25,448	14,276	-43.90
shared	enqueue	3,244,356	3,244,356	0.00
shared	evaluation con	17,408	17,408	0.00
shared	event statistics per sess	68,208,000	68,208,000	0.00
shared	fixed allocation callback	516	516	0.00
shared	free memory	983,019,324	621,887,420	-36.74
shared	joxlod exec hp	613,280	613,280	0.00
shared	joxlod pcod hp	171,616	171,616	0.00
shared	joxlod rsrc hp	7,216	7,216	0.00
shared	joxs heap	4,220	4,220	0.00
shared	library cache	258,669,244	356,474,288	37.81
shared	miscellaneous	124,914,252	140,856,432	12.76
shared	parameters	1,480,660	1,596,548	7.83
shared	partitioning d	525,580	569,536	8.36
shared	pl/sql source	13,864	13,864	0.00
shared	private strands	21,964,800	21,964,800	0.00
shared	repository	2,008,644	2,123,924	5.74
shared	rule set evalu	69,084	69,084	0.00
shared	sessions	6,888,000	6,888,000	0.00
shared	sql area	274,065,840	506,765,836	84.91
shared	synonym source	28,636	46,596	62.72
shared	table definiti	43,936	52,248	18.92
shared	temporary tabl	3,233,964	4,434,092	37.11
shared	trigger defini	112,796	120,900	7.18
shared	trigger inform	1,508	1,508	0.00
shared	trigger source	7,440	7,440	0.00
shared	type object de	543,664	705,552	29.78
	buffer_cache	1,442,840,576	1,442,840,576	0.00
	fixed_sga	781,604	781,604	0.00
	log_buffer	10,485,760	10,485,760	0.00

Back to SGA Statistics
Back to Top

 

 

Resource Limit Stats

No data exists for this section of the report.

Back to Top

 

init.ora Parameters

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

Back to Top

End of Report