WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
EBS1225	2783254286	ebs1225	1	11-Jul-17 14:07	11.2.0.4.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
ebs1225	Linux x86 64-bit	16	16	1	117.78

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	615	11-Jul-17 18:00:31	3283	21.5
End Snap:	617	11-Jul-17 20:00:47	1528	12.7
Elapsed:		120.27 (mins)
DB Time:		115.17 (mins)

Report Summary

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	1.0	0.1	0.00	0.00
DB CPU(s):	0.9	0.1	0.00	0.00
Redo size (bytes):	429,079.1	23,263.5
Logical read (blocks):	97,150.7	5,267.3
Block changes:	2,663.6	144.4
Physical read (blocks):	164.4	8.9
Physical write (blocks):	80.4	4.4
Read IO requests:	11.1	0.6
Write IO requests:	31.6	1.7
Read IO (MB):	1.3	0.1
Write IO (MB):	0.6	0.0
User calls:	583.7	31.7
Parses (SQL):	389.6	21.1
Hard parses (SQL):	0.8	0.0
SQL Work Area (MB):	12.4	0.7
Logons:	0.2	0.0
Executes (SQL):	2,366.3	128.3
Rollbacks:	0.8	0.1
Transactions:	18.4

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	100.00	In-memory Sort %:	100.00
Library Hit %:	99.97	Soft Parse %:	99.81
Execute to Parse %:	83.54	Latch Hit %:	99.94
Parse CPU to Parse Elapsd %:	88.97	% Non-Parse CPU:	99.44

Top 10 Foreground Events by Total Wait Time

Wait Classes by Total Wait Time

Host CPU

CPUs	Cores	Sockets	Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
16	16	1	4.62	0.01	5.4	0.5	0.8	94.1

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
6.0	101.4	0.0

IO Profile

	Read+Write Per Second	Read per Second	Write Per Second
Total Requests:	69.2	13.2	56.0
Database Requests:	42.7	11.1	31.6
Optimized Requests:	0.0	0.0	0.0
Redo Requests:	22.9	0.0	22.9
Total (MB):	2.4	1.3	1.1
Database (MB):	1.9	1.3	0.6
Optimized Total (MB):	0.0	0.0	0.0
Redo (MB):	0.4	0.0	0.4
Database (blocks):	244.7	164.4	80.4
Via Buffer Cache (blocks):	80.3	0.2	80.2
Direct (blocks):	164.4	164.2	0.2

Memory Statistics

	Begin	End
Host Mem (MB):	120,604.3	120,604.3
SGA use (MB):	30,720.0	30,720.0
PGA use (MB):	27,666.2	6,216.1
% Host Mem used for SGA+PGA:	48.41	30.63

Cache Sizes

	Begin	End
Buffer Cache:	25,024M	25,024M	Std Block Size:	8K
Shared Pool Size:	4,468M	4,444M	Log Buffer:	53,948K

Shared Pool Statistics

	Begin	End
Memory Usage %:	50.62	37.41
% SQL with executions>1:	90.70	96.97
% Memory for SQL w/exec>1:	84.93	95.85

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
• Memory Statistics
• Streams Statistics
• Resource Limit Statistics
• Shared Server Statistics
• init.ora Parameters

Wait Events Statistics

• Time Model Statistics
• Operating System Statistics
• Operating System Statistics - Detail
• Foreground Wait Class
• Foreground Wait Events
• Background Wait Events
• Wait Event Histogram
• Wait Event Histogram Detail (64 msec to 2 sec)
• Wait Event Histogram Detail (4 sec to 2 min)
• Wait Event Histogram Detail (4 min to 1 hr)
• Service Statistics
• Service Wait Class Stats

Time Model Statistics

• Total time in database user-calls (DB Time): 6910s
• Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
• Ordered by % or DB time desc, Statistic name

Back to Wait Events Statistics
Back to Top

Operating System Statistics

• *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
• ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 98.4% of Total DB time 6,910.01 (s)
• Total FG Wait Time: 230.96 (s) DB CPU time: 6,571.51 (s)

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

• s - second, ms - millisecond - 1000th of a second
• Only events with Total Wait Time (s) >= .001 are shown
• ordered by wait time desc, waits desc (idle events last)
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)
• Only events with Total Wait Time (s) >= .001 are shown
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
• % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
• Ordered by Event (idle events last)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: s is 1024 milliseconds (approximately 1 second) m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 min to 1 hr)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

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 seconds

Back to Wait Events Statistics
Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Physical Reads (UnOptimized)
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

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
• %Total - Elapsed Time as a percentage of Total DB time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 44.8% of Total DB Time (s): 6,910
• Captured PL/SQL account for 4.6% of Total DB Time (s): 6,910

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 - CPU Time as a percentage of Total DB CPU
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 46.5% of Total CPU Time (s): 6,572
• Captured PL/SQL account for 4.6% of Total CPU Time (s): 6,572

Back to SQL Statistics
Back to Top

SQL ordered by User I/O Wait Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - User I/O Time as a percentage of Total User I/O Wait time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 30.9% of Total User I/O Wait Time (s): 24
• Captured PL/SQL account for 29.0% of Total User I/O Wait Time (s): 24

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 as a percentage of Total Buffer Gets
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 701,082,305
• Captured SQL account for 79.9% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• %Total - Physical Reads as a percentage of Total Disk Reads
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Disk Reads: 1,186,159
• Captured SQL account for 49.7% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

• UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
• %Opt - Optimized Reads as percentage of SQL Read Requests
• %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
• Total Physical Read Requests: 80,212
• Captured SQL account for 46.5% of Total
• Total UnOptimized Read Requests: 80,212
• Captured SQL account for 46.5% of Total
• Total Optimized Read Requests: 1
• Captured SQL account for 0.0% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Executions: 17,075,996
• Captured SQL account for 7.5% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 2,811,216
• Captured SQL account for 13.8% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

No data exists for this section of the report.

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
0dfwu7658tj9k	BEGIN QP_Attr_Mapping_PUB.G_Temp_Value := QP_SOURCING_API_PUB.Get_Order_Amount(OE_ORDER_PUB.G_HDR.header_id); END;
0hfaqhz3gz1zw	SELECT DISTINCT PROFILE_CLASS_ID FROM HZ_CUSTOMER_PROFILES WHERE CUST_ACCOUNT_ID = :B1
0r1zf55mxaujd	/* OracleOEM */ declare lv_sql_stmt varchar2(10000); finalQry varchar2(2048); fromClause varchar2(512); lc_var1 varchar2(512); lc_var2 varchar2(30); lc_var3 varchar2(1024); lc_var4 number; lc_var5 number; fullQName varchar2(512); avg_msg_age number; qtable varchar2(512); qry1 varchar2(32767); receivedB number := 0; processedB number := 0; receivedP number := 0; processedP number := 0; totalReceived number := 0; totalProcessed number := 0; finalPerc number := 0; pk varchar2(32767); subs_id number; subs_name varchar2(512); subs_address varchar2(1000); TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; test_cursor sys_refcursor; v_error_code number; v_error_message varchar2(255); begin begin begin EXECUTE IMMEDIATE 'SELECT * FROM AQ_MNTR_MSGS_SUBS where rownum <=1'; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); -- If table does not exist before then create it IF v_error_code = -942 THEN -- ORA-00942: table or view does not exist EXECUTE IMMEDIATE 'CREATE TABLE AQ_MNTR_MSGS_SUBS (ID varchar2(2048) NOT NULL PRIMARY KEY, schema varchar2(256), queue_name varchar2(256), subs_name varchar2(256), subs_address varchar2(512), average_msg_age_subs number, totalProcessed number, totalReceived number, proc_perc number)'; END IF; end; --If no exception means table exists already. Clean it up before reusing EXECUTE IMMEDIATE 'DELETE FROM AQ_MNTR_MSGS_SUBS'; exception WHEN OTHERS THEN null; end; commit; -- Review comment => Do not include sys/system/sysman/dbsnmp schema as these might contain system queues for rec in (select owner, name, queue_table from dba_queues where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') ) LOOP dbms_output.put_line('+++++ Begin Processing for: ' ||rec.owner||' , '||rec.name|| ', '||rec.queue_table); begin -- Calculate the average message age per queue and store them in the AQ_MNTR_MSGS_SUBS table begin from Clause := rec.owner||'.AQ$'||rec.queue_table; finalQry := 'select queue, consumer_name, address, avg(sysdate-ENQ_TIME) * 86400 AVERAGE_MSG_AGE from ' || fromClause ||' group by queue, consumer_name, address' ; DBMS_OUTPUT.PUT_LINE(finalQry); begin open test_cursor for finalQry; loop lc_var1 := ''; lc_var2 := ''; lc_var3 := ''; lc_var4 := 0; pk := ''; fetch test_cursor into lc_var1 , lc_var2, lc_var3, lc_var4; pk := rec.owner||':'||lc_var1||':'||lc_var2||':'||lc_var3; DBMS_OUTPUT.PUT_LINE('pk1: '||pk); begin IF lc_var4 != 0 THEN totalProcessed := null; totalReceived := null; finalPerc := null; execute immediate 'insert into AQ_MNTR_MSGS_SUBS values (:1, :2, :3, :4, :5, :6, :7, :8, :9)' using pk, rec.owner, lc_var1, lc_var2, lc_var3, lc_var4, totalProcessed, totalReceived, finalPerc; commit; END IF; exception WHEN OTHERS THEN rollback; DBMS_OUTPUT.PUT_LINE(' ***** Unique constaint error ***** '); end; exit when test_cursor %notfound; dbms_output.put_line('FETCHED D ATA: ' ||lc_var1||' , '||lc_var2|| ', '||lc_var3||', '||lc_var4||', '||pk); end loop; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Could not open cursor ***** '); end; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating average age ***** '); end; -- Average message age per queue end -- Messages processed % per queue per subscriber. Calculate and update the AQ_MNTR_MSGS_SUBS table begin qtable := rec.owner||'.AQ$_'||rec.queue_table||'_S'; qry1 := 'select subscriber_id, name, address from ' || qtable || ' where queue_name='''||rec.name||''''; DBMS_OUTPUT.PUT_LINE(qry1); open test_cursor for qry1; loop subs_id := 0; subs_name := ''; subs_address := ''; fetch test_cursor into subs_id, subs_na me, subs_address; pk := rec.owner||':'||rec.name||':'||subs_name||':'||subs_address; DBMS_OUTPUT.PUT_LINE('pk2: '||pk); begin begin execute immediate 'SELECT ENQUEUED_MSGS, DEQUEUED_MSGS from V$PERSISTENT_SUBSCRIBERS where QUEUE_SCHEMA = :1 and QUEUE_NAME = :2 and SUBSCRIBER_ID = :3 ' into receivedP, processedP using rec.owner, rec.name, subs_id; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** FIRST QUERY RETURNED NO DATA***** '); end; begin execute immediate 'SELECT CNUM_MSGS, (CNUM_MSGS-EXPIRED_MSGS-NUM_MSGS) from V$BUFFERED_SUBSCRIBERS where QUEUE_SCHEMA = :1 and QUEUE_NAME = :2 and SUBSCRIBER_ID = :3 ' into receivedB, processedB using rec.owner, rec.name, subs_id; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** SECOND QUERY RETURNED NO DATA***** '); end; totalProcessed := processedB + processedP; totalReceived := receivedB + receivedP; IF totalReceived != 0 THEN finalPerc := (totalProcessed/totalReceived) * 100; execute immediate 'select count (*) from AQ_MNTR_MSGS_SUBS where schema = :1 and queue_name = :2' into lc_var5 using rec.owner, rec.name; IF lc_var5 = 0 THEN avg_msg_age := null; execute immediate 'insert into AQ_MNTR_MSGS_SUBS values (:1, :2, :3, :4, :5, :6, :7, :8, :9)' using pk, rec.owner, rec.name, subs_name, subs_address, avg_msg_age, totalProcessed, totalReceived, finalPerc; ELSE execute immediate 'update AQ_MNTR_MSGS_SUBS set totalProcessed = :1, totalReceived = :2, proc_perc = :3 where ID = :4 ' using totalProcessed, totalReceived, finalPerc, pk; END IF; commit; END IF; -- Reset after using pk := ''; receivedB := 0; processedB := 0; receivedP := 0; processedP := 0; totalReceived := 0; totalProcessed := 0; finalPerc := 0; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Insert into AQ_MNTR_MSGS_SUBS failed might be due to unique key constraint ***** '); en d; exit when test_cursor %notfound; end loop; close test_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating processed % ***** '); end; -- Messages processed % per queue end exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in main for loop ***** '); end; dbms_output.put_line('+++++ END Processing +++++ '); end loop; lv_sql_stmt:='select schema, queue_name, subs_name, subs_address, average_msg_age_subs, totalProcessed, totalReceived, proc_perc from AQ_MNTR_MSGS_SUBS'; open data_cursor for lv_sql_stmt; :1 := data_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBM S_OUTPUT.PUT_LINE('>>>>>> Exception in main begin block <<<<<<<'); end;
132qdnqs38dx2	SELECT DECODE(:B4 , NULL, PS.ACCTD_AMOUNT_DUE_REMAINING, PS.AMOUNT_DUE_REMAINING), DECODE(:B29 , '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(:B28 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B27 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B0, DECODE(:B26 , '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(:B25 , CEIL(:B8 -PS.DUE_DATE )), LEAST(:B24 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B1, DECODE(:B23 , '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(:B22 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B21 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B2, DECODE(:B20 , '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(:B19 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B18 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B3, DECODE(:B17 , '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(:B16 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B15 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B4, DECODE(:B14 , '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(:B13 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B12 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B5, DECODE(:B11 , '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(:B10 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B9 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B6 FROM RA_CUST_TRX_TYPES, AR_PAYMENT_SCHEDULES PS WHERE PS.CUST_TRX_TYPE_ID = RA_CUST_TRX_TYPES.CUST_TRX_TYPE_ID AND PS.CUSTOMER_ID = :B6 AND RA_CUST_TRX_TYPES.ORG_ID= PS.ORG_ID AND DECODE(:B5 , NULL, PS.CUSTOMER_SITE_USE_ID, :B5 ) = PS.CUSTOMER_SITE_USE_ID AND DECODE(UPPER(:B4 ), NULL, PS.INVOICE_CURRENCY_CODE, UPPER(:B4 )) = PS.INVOICE_CURRENCY_CODE AND DECODE(UPPER(:B3 ), 'AGE', 'dummy', 'CM') <> PS.CLASS AND DECODE(UPPER(:B3 ), 'AGE', 'dummy', 'PMT') <> PS.CLASS AND DECODE(:B2 , NULL, RA_CUST_TRX_TYPES.NAME, :B2 ) <= RA_CUST_TRX_TYPES.NAME AND DECODE(:B1 , NULL, RA_CUST_TRX_TYPES.NAME, :B1 ) >= RA_CUST_TRX_TYPES.NAME AND PS.STATUS = 'OP' UNION ALL SELECT -SUM(DECODE(:B4 , NU LL, APP.ACCTD_AMOUNT_APPLIED_FROM, APP.AMOUNT_APPLIED)), DECODE(:B29 , '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(:B28 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B27 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B0, DECODE(:B26 , '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(:B25 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B24 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)))B1, DECODE(:B23 , '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(:B22 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B21 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B2, DECODE(:B20 , '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(:B19 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B18 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B3, DECODE(:B17 , '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(:B16 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B15 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , ' PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B4, DECODE(:B14 , '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(:B13 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B12 , CEIL(:B8 -PS.DUE_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B5, DECODE(:B11 , '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(:B10 , CEIL(:B8 -PS.DUE_DATE)), LEAST(:B9 , CEIL(:B8 -PS.DU E_DATE)), 1, 0) * DECODE(NVL(PS.AMOUNT_IN_DISPUTE, 0), 0, 1, DECODE(:B7 , 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(PS.AMOUNT_ADJUSTED_PENDING, 0), 0, 1, DECODE(:B7 , 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) B6 FROM AR_PAYMENT_SCHEDULES PS, AR_RECEIVABLE_APPLICATIONS APP WHERE PS.CASH_RECEIPT_ID+0 = APP.CASH_RECEIPT_ID AND APP.STATUS IN ( 'ACC', 'UNAPP', 'UNID', 'OTHER ACC') AND NVL(APP.CONFIRMED_FLAG, 'Y') = 'Y' AND (APP.REVERSAL_GL_DATE > :B8 OR APP.REVERSAL_GL_DATE IS NULL ) AND NVL( PS.RECEIPT_CONFIRMED_FLAG, 'Y' ) = 'Y' AND PS.CUSTOMER_ID = :B6 AND PS.ORG_ID=APP.ORG_ID AND DECODE(:B5 , NULL, NVL(PS.CUSTOMER_SITE_USE_ID, -10), :B5 ) = NVL(PS.CUSTOMER_SITE_USE_ID, -10) AND DECODE(UPPER(:B4 ), NULL, PS.INVOICE_CURRENCY_CODE, UPPER(:B4 )) = PS.INVOICE_CURRENCY_CODE AND DECODE(UPPER(:B3 ), 'AGE', 'dummy', 'CM') <> PS.CLASS AND DECODE(UPPER(:B3 ), 'AGE', 'dummy', 'PMT') <> PS.CLASS AND PS.STATUS = 'OP' GROUP BY 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, DECODE( APP.STATUS, 'UNID', 'UNID', 'UNAPP')
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;
1awncyx9pyhvm	SELECT SP.ORG_ID, SOB.CHART_OF_ACCOUNTS_ID, SOB.SET_OF_BOOKS_ID, SOB.NAME, FC.CURRENCY_CODE, SP.ACCOUNTING_METHOD, SP.ACCRUE_INTEREST, SP.UNEARNED_DISCOUNT, SP.PARTIAL_DISCOUNT_FLAG, SP.PRINT_REMIT_TO, SP.DEFAULT_CB_DUE_DATE, SP.AUTO_SITE_NUMBERING, SP.CASH_BASIS_SET_OF_BOOKS_ID, SP.CODE_COMBINATION_ID_GAIN, SP.AUTOCASH_HIERARCHY_ID, SP.RUN_GL_JOURNAL_IMPORT_FLAG, SP.CER_SPLIT_AMOUNT, SP.CER_DSO_DAYS, SP.POSTING_DAYS_PER_CYCLE, SP.ADDRESS_VALIDATION, SP.CALC_DISCOUNT_ON_LINES_FLAG, SP.CHANGE_PRINTED_INVOICE_FLAG, SP.CODE_COMBINATION_ID_LOSS, SP.CREATE_RECIPROCAL_FLAG, SP.DEFAULT_COUNTRY, SP.DEFAULT_TERRITORY, SP.GENERATE_CUSTOMER_NUMBER, SP.INVOICE_DELETION_FLAG, SP.LOCATION_STRUCTURE_ID, SP.SITE_REQUIRED_FLAG, ZXPO.TAX_ALLOW_COMPOUND_FLAG, SP.TAX_HEADER_LEVEL_FLAG, ZXPO.ALLOW_TAX_ROUNDING_OVRD_FLAG, SP.TAX_INVOICE_PRINT, ZXPO.TAX_METHOD_CODE, ZXPO.TAX_USE_CUSTOMER_EXEMPT_FLAG, SP.TAX_USE_CUST_EXC_RATE_FLAG, ZXPO.TAX_USE_LOC_EXC_RATE_FLAG, ZXPO.TAX _USE_PRODUCT_EXEMPT_FLAG, SP.TAX_USE_PROD_EXC_RATE_FLAG, SP.TAX_USE_SITE_EXC_RATE_FLAG, SP.AI_LOG_FILE_MESSAGE_LEVEL, SP.AI_MAX_MEMORY_IN_BYTES, SP.AI_ACCT_FLEX_KEY_LEFT_PROMPT, SP.AI_MTL_ITEMS_KEY_LEFT_PROMPT, SP.AI_TERRITORY_KEY_LEFT_PROMPT, SP.AI_PURGE_INTERFACE_TABLES_FLAG, SP.AI_ACTIVATE_SQL_TRACE_FLAG, SP.DEFAULT_GROUPING_RULE_ID, SP.SALESREP_REQUIRED_FLAG, SP.AUTO_REC_INVOICES_PER_COMMIT, SP.AUTO_REC_RECEIPTS_PER_COMMIT, SP.PAY_UNRELATED_INVOICES_FLAG, SP.PRINT_HOME_COUNTRY_FLAG, SP.LOCATION_TAX_ACCOUNT, SP.FROM_POSTAL_CODE, SP.TO_POSTAL_CODE, SP.TAX_REGISTRATION_NUMBER, SP.POPULATE_GL_SEGMENTS_FLAG, SP.UNALLOCATED_REVENUE_CCID, SOB.PERIOD_SET_NAME, FC.PRECISION, FC.EXTENDED_PRECISION, FC.MINIMUM_ACCOUNTABLE_UNIT, RS.NAME, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING( 'YES/NO', 'N'), ARPT_SQL_FUNC_UTIL.GET_FNDLOOKUP_MEANING('ZX_EXEMPTION_CONTROL', 'S'), ZXPO.INCLUSIVE_TAX_USED_FLAG, SP.TAX_ENFORCE_ ACCOUNT_FLAG, SP.TA_INSTALLED_FLAG, SP.BILLS_RECEIVABLE_ENABLED_FLAG, SP.ATTRIBUTE_CATEGORY, SP.ATTRIBUTE1, SP.ATTRIBUTE2, SP.ATTRIBUTE3, SP.ATTRIBUTE4, SP.ATTRIBUTE5, SP.ATTRIBUTE6, SP.ATTRIBUTE7, SP.ATTRIBUTE8, SP.ATTRIBUTE9, SP.ATTRIBUTE10, SP.ATTRIBUTE11, SP.ATTRIBUTE12, SP.ATTRIBUTE13, SP.ATTRIBUTE14, SP.ATTRIBUTE15, SP.CREATED_BY, SP.CREATION_DATE, SP.LAST_UPDATED_BY, SP.LAST_UPDATE_DATE, SP.LAST_UPDATE_LOGIN, ZXPO.TAX_CLASSIFICATION_CODE, SP.TAX_CURRENCY_CODE, ZXPO.TAX_MINIMUM_ACCOUNTABLE_UNIT, ZXPO.TAX_PRECISION, ZXPO.TAX_ROUNDING_RULE, SP.TAX_USE_ACCOUNT_EXC_RATE_FLAG, SP.TAX_USE_SYSTEM_EXC_RATE_FLAG, SP.TAX_HIER_SITE_EXC_RATE, SP.TAX_HIER_CUST_EXC_RATE, SP.TAX_HIER_PROD_EXC_RATE, SP.TAX_HIER_ACCOUNT_EXC_RATE, SP.TAX_HIER_SYSTEM_EXC_RATE, SP.TAX_DATABASE_VIEW_SET, SP.GLOBAL_ATTRIBUTE1, SP.GLOBAL_ATTRIBUTE2, SP.GLOBAL_ATTRIBUTE3, SP.GLOBAL_ATTRIBUTE4, SP.GLOBAL_ATTRIBUTE5, SP.GLOBAL_ATTRIBUTE6, SP.GLOBAL_ATTRIBUTE7, SP.GLOBAL_ATTRIBUT E8, SP.GLOBAL_ATTRIBUTE9, SP.GLOBAL_ATTRIBUTE10, SP.GLOBAL_ATTRIBUTE11, SP.GLOBAL_ATTRIBUTE12, SP.GLOBAL_ATTRIBUTE13, SP.GLOBAL_ATTRIBUTE14, SP.GLOBAL_ATTRIBUTE15, SP.GLOBAL_ATTRIBUTE16, SP.GLOBAL_ATTRIBUTE17, SP.GLOBAL_ATTRIBUTE18, SP.GLOBAL_ATTRIBUTE19, SP.GLOBAL_ATTRIBUTE20, SP.GLOBAL_ATTRIBUTE_CATEGORY, SP.RULE_SET_ID, SP.CODE_COMBINATION_ID_ROUND, SP.TRX_HEADER_LEVEL_ROUNDING, SP.TRX_HEADER_ROUND_CCID, SP.FINCHRG_RECEIVABLES_TRX_ID, SP.SALES_TAX_GEOCODE, SP.REV_TRANSFER_CLEAR_CCID, SP.SALES_CREDIT_PCT_LIMIT, SP.MAX_WRTOFF_AMOUNT, SP.IREC_CC_RECEIPT_METHOD_ID, SP.SHOW_BILLING_NUMBER_FLAG, SP.CROSS_CURRENCY_RATE_TYPE, SP.DOCUMENT_SEQ_GEN_LEVEL, 'Y', SP.IREC_BA_RECEIPT_METHOD_ID, AR_MO_CACHE_UTILS.TM_INSTALLED (SP.ORG_ID), AR_MO_CACHE_UTILS.TM_DEFAULT_SETUP (SP.ORG_ID), SP.PAYMENT_THRESHOLD, SP.STANDARD_REFUND, SP.CREDIT_CLASSIFICATION1, SP.CREDIT_CLASSIFICATION2, SP.CREDIT_CLASSIFICATION3, SP.UNMTCH_CLAIM_CREATION_FLAG, SP.MATCHED_CLAIM_CREATION_ FLAG, SP.MATCHED_CLAIM_EXCL_CM_FLAG, SP.MIN_WRTOFF_AMOUNT, SP.MIN_REFUND_AMOUNT, SP.CREATE_DETAILED_DIST_FLAG, SP.DEFAULT_RCT_MTHD_FOR_LC_INV FROM GL_SETS_OF_BOOKS SOB, FND_CURRENCIES FC, JTF_RS_SALESREPS RS, AR_SYSTEM_PARAMETERS SP, ZX_PRODUCT_OPTIONS ZXPO WHERE SP.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE AND NVL(SP.ORG_ID, -99) = NVL(RS.ORG_ID, -99) AND SP.ORG_ID = NVL(:B1 , SP.ORG_ID) AND RS.SALESREP_ID = -3 AND ZXPO.APPLICATION_ID (+) = 222 AND ZXPO.ORG_ID (+) = SP.ORG_ID
1gpdqku7dc104	SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL FROM DUAL
1t4ykj11rtya1	begin FND_MESSAGE.RETRIEVE(:result); end;
279sut03s49jd	SELECT X.ORGANIZATION_ID ORGANIZATION_ID , X.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , X.REVISION REVISION , NULL LOT_NUMBER , NULL LOT_EXPIRATION_DATE , X.SUBINVENTORY_CODE SUBINVENTORY_CODE , SUB.RESERVABLE_TYPE RESERVABLE_TYPE , X.LOCATOR_ID LOCATOR_ID , SUM(X.PRIMARY_QUANTITY) PRIMARY_QUANTITY , SUM(X.SECONDARY_QUANTITY) SECONDARY_QUANTITY , X.QUANTITY_TYPE QUANTITY_TYPE , X.COST_GROUP_ID COST_GROUP_ID , X.LPN_ID LPN_ID , X.TRANSACTION_ACTION_ID TRANSACTION_ACTION_ID , X.TRANSFER_SUBINVENTORY_CODE TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID TRANSFER_LOCATOR_ID , NULL IS_RESERVABLE_LOT FROM ( SELECT X.ORGANIZATION_ID ORGANIZATION_ID , X.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , DECODE(:B10 , 2, NULL, X.REVISION) REVISION , NULL LOT_NUMBER , X.SUBINVENTORY_CODE SUBINVENTORY_CODE , X.LOCATOR_ID LOCATOR_ID , SUM(X.PRIMARY_QUANTITY) PRIMARY_QUANTITY , SUM(X.SECONDARY_QUANTITY) SECONDARY_QUANTITY , X.QUANTITY_TYPE QUANTITY_TYPE , X.COST_GROUP_ID COST_GROUP_ID , X.LPN_ID LPN_ID , X.TRANSACTION_ACTION_ID TRANSACTION_ACTION_ID , X.TRANSFER_SUBINVENTORY_CODE TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID TRANSFER_LOCATOR_ID FROM ( SELECT MR.ORGANIZATION_ID ORGANIZATION_ID , MR.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MR.REVISION REVISION , MR.LOT_NUMBER LOT_NUMBER , MR.SUBINVENTORY_CODE SUBINVENTORY_CODE , MR.LOCATOR_ID LOCATOR_ID , MR.PRIMARY_RESERVATION_QUANTITY - NVL(MR.DETAILED_QUANTITY, 0) PRIMARY_QUANTITY , MR.SECONDARY_RESERVATION_QUANTITY - NVL(MR.SECONDARY_DETAILED_QUANTITY, 0) SECONDARY_QUANTITY , 3 QUANTITY_TYPE , TO_NUMBER(NULL) COST_GROUP_ID , LPN_ID LPN_ID , TO_NUMBER(NULL) TRANSACTION_ACTION_ID , TO_CHAR(NULL) TRANSFER_SUBINVENTORY_CODE , TO_NUMBER(NULL) TRANSFER_LOCATOR_ID FROM MTL_RESERVATIONS MR WHERE NVL(MR.SUPPLY_SOURCE_TYPE_ID, 13) = 13 AND MR.PRIMARY_RESERVATION_QUANTITY > NVL(MR.DETAILED_QUANTITY, 0) AND ((:B7 <>1) OR (:B7 = 1 AND MR.LPN_ID IS NULL)) AND (:B6 <> 3 OR (:B6 = 3 AND NOT ( :B5 = MR.DEMAND_SOURCE_TYPE_ID AND :B4 = MR.DEMAND_SOURCE_HEADER_ID AND NVL(:B3 , -9999) = NVL(MR.DEMAND_SOURCE_LINE_ID, -9999) AND NVL(:B2 , '@@@###@@#') = NVL(MR.DEMAND_SOURCE_NAME, '@@@###@@#') AND NVL(:B1 , -9999) = NVL(MR.DEMAND_SOURCE_DELIVERY, -9999) ) ) ) UNION ALL SELECT MOQ.ORGANIZATION_ID ORGANIZATION_ID , MOQ.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MOQ.REVISION REVISION , MOQ.LOT_NUMBER LOT_NUMBER , MOQ.SUBINVENTORY_CODE SUBINVENTORY_CODE , MOQ.LOCATOR_ID LOCATOR_ID , MOQ.PRIMARY_TRANSACTION_QUANTITY , MOQ.SECONDARY_TRANSACTION_QUANTITY , 1 QUANTITY_TYPE , MOQ.COST_GROUP_ID COST_GROUP_ID , MOQ.LPN_ID LPN_ID , TO_NUMBER(NULL) TRANSACTION_ACTION_ID , TO_CHAR(NULL) TRANSFER_SUBINVENTORY_CODE , TO_NUMBER(NULL) TRANSFER_LOCATOR_ID FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ UNION ALL SELECT MMTT.ORGANIZATION_ID ORGANIZATION_ID , MMTT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MMTT.REVISION REVISION , NULL LOT_NUMBER , MMTT.SUBINVENTORY_CODE SUBINVENTORY_CODE , MMTT.LOCATOR_I D LOCATOR_ID , DECODE(DECODE(MMTT.TRANSACTION_STATUS, 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, -1, 2), MMTT.TRANSACTION_STATUS) , 2, 1, DECODE(MMTT.TRANSACTION_ACTION_ID, 1, -1, 2, -1, 28, -1, 3, -1, SIGN(MMTT.PRIMARY_QUANTITY))) * ROUND(ABS(MMTT.PRIMARY_QUANTITY), 5) , DECODE(DECODE(MMTT.TRANSACTION_STATUS, 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, -1, 2), MMTT.TRANSACTION_STATUS) , 2, 1, DECODE(MMTT.TRANSACTION_ACTION_ID, 1, -1, 2, -1, 28, -1, 3, -1, SIGN(MMTT.SECONDARY_TRANSACTION_QUANTITY))) * ROUND(ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY), 5) , DECODE(MMTT.TRANSACTION_STATUS, 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, 1, 5), 1) QUANTITY_TYPE , MMTT.COST_GROUP_ID COST_GROUP_ID , DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID), NVL(MMTT.ALLOCATED_LPN_ID, NVL(MMTT.CONTENT_LPN_ID, MMTT.LPN_ID))) LPN_ID , DECODE(MMTT.TRANSACTION_STATUS, 2 , DECODE (MP.PROCESS_ENABLED_FLAG, 'Y', DECODE ((SELECT MOVE_ORDER_TYPE FROM MTL_ TXN_REQUEST_HEADERS MTRH WHERE HEADER_ID = MMTT.MOVE_ORDER_HEADER_ID AND ROWNUM = 1), 5, 28, MMTT.TRANSACTION_ACTION_ID), MMTT.TRANSACTION_ACTION_ID), TO_NUMBER(NULL)) TRANSACTION_ACTION_ID , DECODE(MMTT.TRANSACTION_STATUS, 2 , MMTT.TRANSFER_SUBINVENTORY, TO_CHAR(NULL)) TRANSFER_SUBINVENTORY_CODE , DECODE(MMTT.TRANSACTION_STATUS, 2 , MMTT.TRANSFER_TO_LOCATION, TO_NUMBER(NULL)) TRANSFER_LOCATOR_ID FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT, MTL_PARAMETERS MP WHERE MMTT.POSTING_FLAG = 'Y' AND MMTT.SUBINVENTORY_CODE IS NOT NULL AND (NVL(MMTT.TRANSACTION_STATUS, 0) <> 2 OR NVL(MMTT.TRANSACTION_STATUS, 0) = 2 AND MMTT.TRANSACTION_ACTION_ID IN (1, 2, 28, 3, 21, 29, 32, 34)) AND MMTT.TRANSACTION_ACTION_ID NOT IN (5, 6, 24, 30) AND MMTT.ORGANIZATION_ID = MP.ORGANIZATION_ID UNION ALL SELECT DECODE(MMTT.TRANSACTION_ACTION_ID, 3, MMTT.TRANSFER_ORGANIZATION, MMTT.ORGANIZATION_ID) ORGANIZATION_ID , MMTT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MMTT.REVISION REVISION , NULL LOT_NUM BER , MMTT.TRANSFER_SUBINVENTORY SUBINVENTORY_CODE , MMTT.TRANSFER_TO_LOCATION LOCATOR_ID , ROUND(ABS(MMTT.PRIMARY_QUANTITY), 5) , ROUND(ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY), 5) , DECODE(NVL(MMTT.TRANSACTION_STATUS, 0), 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, 6, 1 ), 1) QUANTITY_TYPE , MMTT.TRANSFER_COST_GROUP_ID COST_GROUP_ID , NVL(MMTT.CONTENT_LPN_ID, MMTT.TRANSFER_LPN_ID) LPN_ID , TO_NUMBER(NULL) TRANSACTION_ACTION_ID , TO_CHAR(NULL) TRANSFER_SUBINVENTORY_CODE , TO_NUMBER(NULL) TRANSFER_LOCATOR_ID FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT WHERE MMTT.POSTING_FLAG = 'Y' AND DECODE( NVL(MMTT.TRANSACTION_STATUS, 0), 2, DECODE(NVL(MMTT.WMS_TASK_STATUS, -1), 4, 1, 2), 1 ) <> 2 AND (MMTT.TRANSACTION_ACTION_ID IN (2, 3) OR (MMTT.TRANSACTION_ACTION_ID = 28 AND :B4 = MMTT.TRANSACTION_SOURCE_ID AND NVL(:B3 , -9999) = NVL(MMTT.TRX_SOURCE_LINE_ID, -9999) AND NVL(:B2 , '@@@###@@#') = NVL(MMTT.TRANSACTION_SOURCE_NAME, '@@@###@@#') AND NVL(:B1 , -9999) = NVL(MM TT.TRX_SOURCE_DELIVERY_ID, -9999) ) ) AND MMTT.WIP_SUPPLY_TYPE IS NULL ) X WHERE X.ORGANIZATION_ID = :B9 AND X.INVENTORY_ITEM_ID = :B8 GROUP BY X.ORGANIZATION_ID, X.INVENTORY_ITEM_ID, X.REVISION , X.LOT_NUMBER, X.SUBINVENTORY_CODE, X.LOCATOR_ID , X.QUANTITY_TYPE, X.COST_GROUP_ID, X.LPN_ID , X.TRANSACTION_ACTION_ID, X.TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID ) X , MTL_SECONDARY_INVENTORIES SUB WHERE X.ORGANIZATION_ID = SUB.ORGANIZATION_ID (+) AND X.SUBINVENTORY_CODE = SUB.SECONDARY_INVENTORY_NAME (+) AND (:B12 = 2 OR NVL(SUB.ASSET_INVENTORY, 1) = 1) AND ( (:B11 = 1 AND NVL(SUB.INVENTORY_ATP_CODE, 1) = 1) OR (:B11 = 2 AND NVL(SUB.AVAILABILITY_TYPE, 1) = 1 ) OR :B11 =3 OR (:B11 = 4 AND (NVL(SUB.INVENTORY_ATP_CODE, 1) = 1 AND NVL(SUB.AVAILABILITY_TYPE, 1)=1)) ) GROUP BY X.ORGANIZATION_ID , X.INVENTORY_ITEM_ID , X.REVISION , NULL , NULL , SUB.RESERVABLE_TYPE , X.SUBINVENTORY_CODE , NULL , X.LOCATOR_ID , X.QUANTITY_TYPE , X.COST_GROUP_ID , X.LPN_ID , X.TRA NSACTION_ACTION_ID , X.TRANSFER_SUBINVENTORY_CODE , X.TRANSFER_LOCATOR_ID
29291gnwbbcqs	SELECT CUSTOMER_TRX_ID COL1, PAYMENT_SCHEDULE_ID COL2, DELINQUENCY_ID COL3, TRX_NUMBER COL4, ACCOUNT_NUMBER COL5, AMOUNT_DUE_ORIGINAL COL6, INVOICE_CURRENCY_CODE COL7, AMOUNT_DUE_REMAINING COL8, INVOICE_CURRENCY_CODE COL9, ACCTD_AMOUNT_DUE_REMAINING COL10, INVOICE_CURRENCY_CODE COL11, INVOICE_CURRENCY_CODE COL12, DAYS_PAST_DUE COL13, CUST_TRX_CLASS_NAME COL14, CUST_TRX_CLASS_CODE COL15, DUE_DATE COL16, TERMS_SEQUENCE_NUMBER COL17, TRX_DATE COL18, TRX_CREATION_DATE COL19, PARTY_SITE_NUMBER COL20, BILL_TO_LOCATION COL21, BILL_TO_ADDRESS COL22, CUST_TRX_TYPE_NAME COL23, CUST_TRX_STATUS_NAME COL24, AMOUNT_IN_DISPUTE COL25, INVOICE_CURRENCY_CODE COL26, DISPUTE_DATE COL27, NULL COL28, NULL COL29, STAGED_DUNNING_LEVEL COL30, DUNNING_LEVEL_OVERRIDE_DATE COL31, PURCHASE_ORDER COL32, NULL COL33, CONS_INV_ID COL34, CONS_BILLING_NUMBER COL35, NULL COL36, NULL COL37, GL_DATE COL38, TERM_NAME COL39, COMMENTS COL40, PRIMARY_SALESREP_NAME COL41, decode(DEFAULT_PRIN TING_OPTION, 'NOT', ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NO', 'Y'), '') COL42, ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NO', PRINTING_PENDING) COL43, DELINQUENCY_STATUS COL44, UWQ_STATUS COL45, OPERATING_UNIT COL46, DEL_STATUS_DSP COL47, UNPAID_REASON_MEANING COL48, RECEIVABLES_COMMENTS COL49, RECEIVABLES_INTERNAL_NOTES COL50, RECEIVABLES_SPL_INSTRUCTIONS COL51, ATTRIBUTE_CATEGORY COL52, ATTRIBUTE1 COL53, ATTRIBUTE2 COL54, ATTRIBUTE3 COL55, ATTRIBUTE4 COL56, ATTRIBUTE5 COL57, ATTRIBUTE6 COL58, ATTRIBUTE7 COL59, ATTRIBUTE8 COL60, ATTRIBUTE9 COL61, ATTRIBUTE10 COL62, ATTRIBUTE11 COL63, ATTRIBUTE12 COL64, ATTRIBUTE13 COL65, ATTRIBUTE14 COL66, ATTRIBUTE15 COL67, INTERFACE_HEADER_CONTEXT COL68, INTERFACE_HEADER_ATTRIBUTE1 COL69, INTERFACE_HEADER_ATTRIBUTE2 COL70, INTERFACE_HEADER_ATTRIBUTE3 COL71, INTERFACE_HEADER_ATTRIBUTE4 COL72, INTERFACE_HEADER_ATTRIBUTE5 COL73, INTERFACE_HEADER_ATTRIBUTE6 COL74, INTERFACE_HEADER_ATTRIBUTE7 COL75, INTERFACE _HEADER_ATTRIBUTE8 COL76, INTERFACE_HEADER_ATTRIBUTE9 COL77, INTERFACE_HEADER_ATTRIBUTE10 COL78, INTERFACE_HEADER_ATTRIBUTE11 COL79, INTERFACE_HEADER_ATTRIBUTE12 COL80, INTERFACE_HEADER_ATTRIBUTE13 COL81, INTERFACE_HEADER_ATTRIBUTE14 COL82, INTERFACE_HEADER_ATTRIBUTE15 COL83, GLOBAL_ATTRIBUTE_CATEGORY COL84, GLOBAL_ATTRIBUTE1 COL85, GLOBAL_ATTRIBUTE2 COL86, GLOBAL_ATTRIBUTE3 COL87, GLOBAL_ATTRIBUTE4 COL88, GLOBAL_ATTRIBUTE5 COL89, GLOBAL_ATTRIBUTE6 COL90, GLOBAL_ATTRIBUTE7 COL91, GLOBAL_ATTRIBUTE8 COL92, GLOBAL_ATTRIBUTE9 COL93, GLOBAL_ATTRIBUTE10 COL94, GLOBAL_ATTRIBUTE11 COL95, GLOBAL_ATTRIBUTE12 COL96, GLOBAL_ATTRIBUTE13 COL97, GLOBAL_ATTRIBUTE14 COL98, GLOBAL_ATTRIBUTE15 COL99, ORG_ID COL100, LEGAL_ENTITY_ID COL101, LEGAL_ENTITY_NAME COL102, NULL COL103 FROM IEX_TRANSACTIONS_V WHERE trunc(due_date) >= :B_DUE_DATE_LOW AND trunc(due_date) <= :B_DUE_DATE_HIGH AND customer_id = :B_CUSTOMER_ID AND cust_trx_class_code = :B_TRX_CLASS AND (delinquency_statu s in ('DELINQUENT', 'PREDELINQUENT') OR (delinquency_status IS NULL AND amount_due_remaining <> 0) OR (delinquency_status = 'CURRENT' AND amount_due_remaining <> 0)) AND (cust_trx_status_code = 'OP') ORDER BY COL16 DESC
2p5b3rpqfjdjk	BEGIN QP_Attr_Mapping_PUB.G_Temp_MultiValue := OZF_QP_QUAL_PVT.get_buying_groups(ASO_PRICING_INT.G_HEADER_REC.party_id, OE_ORDER_PUB.G_LINE.SOLD_TO_ORG_ID); END;
2qh8v5safwdg0	begin OE_2PC2_LINE_RETLIN.Is_Valid( :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;
2w1s3wu1zw63a	select queue, consumer_name, address, avg(sysdate-ENQ_TIME) * 86400 AVERAGE_MSG_AGE from APPLSYS.AQ$WF_NOTIFICATION_OUT group by queue, consumer_name, address
39m4sx9k63ba2	select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
3ktacv9r56b51	select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
3n42vrb4rnh47	begin sys.dbms_backup_restore.commitRmanStatusRow( row_id => :row_id, row_stamp => :row_stamp, mbytes => :mb, status => :status, ibytes => :ib, obytes => :ob, odevtype => :odevtype); end;
3z7bqju2c7wpg	SELECT SUM(ACCTD_AMOUNT_DUE_REMAINING) AMOUNT FROM AR_PAYMENT_SCHEDULES PS, IEX_DELINQUENCIES DEL WHERE PS.CUSTOMER_ID = :B1 AND PS.STATUS = 'OP' AND DEL.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND DEL.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
4uk039gb6g280	BEGIN QP_Attr_Mapping_PUB.G_Temp_Value := QP_SOURCING_API_PUB.GET_LINE_WEIGHT_OR_VOLUME('Weight', OE_ORDER_PUB.G_LINE.inventory_item_id, OE_ORDER_PUB.G_LINE.ordered_quantity, OE_ORDER_PUB.G_LINE.order_quantity_uom); END;
4ur521d1gnqjd	BEGIN QP_Attr_Mapping_PUB.G_Temp_Value := OE_ORDER_PUB.G_HDR.order_type_id; END;
4yak7x2zqz0dh	BEGIN inv_transfer_order_pvt.finalize_pick_confirm(:1, :2, :3, :4, :5, :6, :7); END;
521y9hsruj6y2	SELECT I.TAX_CODE FROM ZX_OUTPUT_CLASSIFICATIONS_V L, MTL_SYSTEM_ITEMS I WHERE I.INVENTORY_ITEM_ID = :B4 AND I.ORGANIZATION_ID = :B3 AND L.LOOKUP_CODE = I.TAX_CODE AND L.ORG_ID IN (:B2 , -99) AND L.ENABLED_FLAG = 'Y' AND (L.START_DATE_ACTIVE <= :B1 OR L.START_DATE_ACTIVE IS NULL) AND (L.END_DATE_ACTIVE >= :B1 OR L.END_DATE_ACTIVE IS NULL) AND ROWNUM = 1 ORDER BY L.ORG_ID DESC
53pkuyc2acm21	/* OracleOEM */ SELECT * FROM ( SELECT /*+ ordered */ 1 "OBJECT_TYPE", u.name "USER_NAME", o.name "OBJECT_NAME", ' ' "PARTITION", ' ' "LOB_COLUMN", 1 "SEG_TYPE", tts.name "TABLESPACE", decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) "PROBLEM" FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tab$ t, sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = t.obj# AND t.ts# = tts.ts# AND t.file# = s.file# AND t.block# = s.block# AND t.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) AND decode (bitand (t.property, 1), 1, 1, 0) = 0 AND decode (b itand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND decode (bitand (t.property, 1024), 1024, 1, 0) = 0 UNION ALL SELECT /*+ ordered */ 2, u.name, o.name, ' ', ' ', 2, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.ind$ i, sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = i.obj# AND i.ts# = tts.ts# AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AN D i.type# not in (4, 8) UNION ALL SELECT /*+ ordered */ 3, u.name, o.name, ' ', ' ', 12, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.clu$ c, sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = c.obj# AND c.ts# = tts.ts# AND c.file# = s.file# AND c.block# = s.block# AND c.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', ' ', 1, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.ind$ i, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" tn, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = i.obj# AND i.ts# = tts.ts# AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND i.bo# = tn.obj# AND tn.obj# = t.obj# AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AND i.type# = 4 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, o.name, o.subname, ' ', 3, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND na me != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabpart$ tp, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = tp.obj# AND tp.ts# = tts.ts# AND tp.file# = s.file# AND tp.block# = s.block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 2, u.name, o.name, o.subname, ' ', 4, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indpart$ ip, sys.seg$ s, sys.ind$ i, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = ip.obj# AND ip.ts# = tts.ts# AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# not in (4, 8) AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', ' ', 5, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tab$ t, sys.seg$ s, sys.tab$ iot, sys."_CURRENT_EDITION_OBJ" tn, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# A ND u.type# !=2 AND o.obj# = t.obj# AND t.ts# = tts.ts# AND t.file# = s.file# AND t.block# = s.block# AND t.ts# = s.ts# AND tn.obj# = t.bobj# AND tn.obj# = iot.obj# AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, ' ', decode(bitand(c.property, 1), 1, substr(ac.name, 0, 500), c.name), 8, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.lob$ l, sys.seg$ s, sys.col$ c, sys.attrcol$ ac, sys."_CURRENT_EDITION_OBJ" tn, sys.user$ u WHERE tn.owner# = u.user# AND u.type# !=2 AND tn.obj# = c.obj# AND c.obj# = l.obj# AND c.intcol# = l.intcol# AND l.ts# = tts.ts# AND l.file# = s.file# AND l.block# = s.block# AND l.ts# = s.ts# AND c.obj# = ac.obj#(+) AND c.intcol# = ac.intcol#(+) AND bitand(c.property, 32768) != 32768 AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tn.name, o.subname, ' ', 3, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indpart$ ip, sys.seg$ s, sys.ind$ i, sys."_CURRENT_EDITION_OBJ" tn, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = ip.obj# AND ip.ts# = tts.ts# AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# = 4 AND i.bo# = tn.obj# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tbo.name, o.subname, ' ', 13, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabpart$ tp, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" tbo, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = tp.obj# AND tp.ts# = tts.ts# AND tp.file# = s.file# AND tp.block# = s. block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 32), 32, 1, 0) = 1 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 AND tbo.obj# = t.bobj# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, tbo.name, po.subname, decode(bitand(c.property, 1), 1, substr(a.name, 0, 500), c.name), 8, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.lobfrag$ lf, sys.seg$ s, sys.lob$ l, sys.partobj$ pobj, sys."_CURRENT_EDITION_OBJ" po, sys."_CURRENT_EDITION_OBJ" tbo, sys.attrcol$ a, sys.col$ c, sy s.user$ u WHERE tbo.owner# = u.user# AND u.type# !=2 AND pobj.obj# = tbo.obj# AND lf.ts# = tts.ts# AND lf.file# = s.file# AND lf.block# = s.block# AND lf.ts# = s.ts# AND mod(pobj.spare2, 256) = 0 AND tbo.obj# = c.obj# AND c.obj# = l.obj# AND c.intcol# = l.intcol# AND l.lobj# = lf.parentobj# AND lf.tabfragobj# = po.obj# AND bitand(c.property, 32768) != 32768 AND c.obj# = a.obj#(+) AND c.intcol# = a.intcol#(+) AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 1, u.name, bo.name, o.subname, ' ', 9, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.tabsubpart$ tsp, sys.seg$ s, sys."_CURRENT_EDITION_OBJ" bo, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = tsp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 2 AND tsp.ts# = tts.ts# AND tsp.file# = s.file# AND tsp.block# = s.block# AND tsp.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) UNION ALL SELECT /*+ ordered */ 2, u.name, bo.name, o.subname, ' ', 10, tts.name, decode (s.extpct, 0, 0, 1) + decode(mod(s.extsize, s.iniexts), 0, 0, 2) FROM (SELECT ts#, name FROM sys.ts$ WHERE (bitmapped = 0 OR (bitmapped != 0 AND bitand(flags, 3)=0)) AND bitand(flags, 2048) != 2048 AND online$ != 3 AND name != 'SYSTEM' AND name != 'SYSAUX' AND dflextpct = 0 AND (dflinit = 0 OR MOD(dflincr, dflinit) = 0)) tts, sys.indsubpart$ isp, sys.seg$ s, sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" bo WHERE o.owner# = u.user# AND u.type# !=2 AND o.obj# = isp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 1 AND isp.ts# = tts.ts# AND isp.file# = s.file# AND isp.block# = s.block# AND isp.ts# = s.ts# AND (s.extpct > 0 OR (s.iniexts > 0 AND (mod(s.extsize, s.iniexts) != 0) ) ) ) where rownum < 201 AND LENGTH(lob_column) <= 30
592madzz61k01	WITH a as (SELECT a$.dbid, a$.snap_id, a$.INSTANCE_NUMBER, a$.PLSQL_ENTRY_OBJECT_ID, a$.PLSQL_ENTRY_SUBPROGRAM_ID, a$.TIME_MODEL FROM WRH$_ACTIVE_SESSION_HISTORY a$ WHERE a$.dbid = :dbid AND a$.instance_number = :inst AND a$.snap_id > :bid AND a$.snap_id <= :eid AND a$.session_type = 1 ) , ash as (SELECT cnt , PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID FROM ( SELECT count(*) as cnt , a.PLSQL_ENTRY_OBJECT_ID, a.PLSQL_ENTRY_SUBPROGRAM_ID FROM a WHERE bitand(a.time_model, power(2, 11 )) !=0 AND a.PLSQL_ENTRY_OBJECT_ID > 0 AND a.PLSQL_ENTRY_SUBPROGRAM_ID > 0 GROUP BY PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID HAVING count(*) >= :rowcnt ORDER BY 1 DESC, 2 ASC ) WHERE rownum <= :ret) SELECT ash.plsql_entry_object_id, ash.plsql_entry_subprogram_id, dba.object_name, dba.object_type, dba.owner, dba.procedure_name, to_number(dba.overload), ash.cnt FROM dba_procedures dba, ash WHERE ash.plsql_entry_object_id = dba.object_id(+) AND ash.plsql_entry _subprogram_id = dba.subprogram_id(+) ORDER BY ash.cnt DESC, ash.plsql_entry_object_id, ash.plsql_entry_subprogram_id
5at5sq48ks98y	SELECT SUM(NVL(ACCTD_AMOUNT_DUE_REMAINING, 0)) FROM AR_PAYMENT_SCHEDULES PS, AR_SYSTEM_PARAMETERS PARM WHERE CUSTOMER_ID = :B1 AND PS.ORG_ID = PARM.ORG_ID AND PS.STATUS = 'OP'
5uc22nkkupb6x	SELECT INVENTORY_ITEM_ID , ORGANIZATION_ID , INVOICING_RULE_ID , ACCOUNTING_RULE_ID , DEFAULT_SHIPPING_ORG , SHIP_MODEL_COMPLETE_FLAG , BUILD_IN_WIP_FLAG , BOM_ITEM_TYPE , REPLENISH_TO_ORDER_FLAG , PRIMARY_UOM_CODE , PICK_COMPONENTS_FLAG , SHIPPABLE_ITEM_FLAG , SERVICE_ITEM_FLAG , ORGANIZATION_ID , CUSTOMER_ORDER_ENABLED_FLAG , INTERNAL_ORDER_ENABLED_FLAG , RETURNABLE_FLAG , RESTRICT_SUBINVENTORIES_CODE FROM MTL_SYSTEM_ITEMS WHERE INVENTORY_ITEM_ID = :B2 AND ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID', :B1 )
6a0xty1ja4gtk	SELECT NVL(ORDR_TYPE.CREDIT_CARD_REV_REAUTH_CODE, OE_SYS_PARAMETERS.VALUE('OE_CC_REVERSAL_REAUTHORIZATION')) FROM OE_ORDER_HEADERS_ALL ORDR , OE_TRANSACTION_TYPES_ALL ORDR_TYPE WHERE ORDR.ORDER_TYPE_ID = ORDR_TYPE.TRANSACTION_TYPE_ID AND ORDR.HEADER_ID = :B1
6dhn7dz44apb9	begin OE_SCHEDULE_UTIL.IS_ITEM_SUBSTITUTED( :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;
6dp3t6txwjvam	SELECT SU.TAX_CODE FROM HZ_CUST_SITE_USES_ALL SU, ZX_OUTPUT_CLASSIFICATIONS_V L WHERE SU.SITE_USE_ID = :B3 AND SU.ORG_ID = :B2 AND L.LOOKUP_CODE = SU.TAX_CODE AND L.ORG_ID IN (:B2 , -99) AND L.ENABLED_FLAG = 'Y' AND (L.START_DATE_ACTIVE <= :B1 OR L.START_DATE_ACTIVE IS NULL) AND (L.END_DATE_ACTIVE >= :B1 OR L.END_DATE_ACTIVE IS NULL) AND ROWNUM = 1 ORDER BY L.ORG_ID DESC
6gvch1xu9ca3g	DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
707cda24y439k	SELECT NVL(count(1), 0), NVL(SUM(ACCTD_AMOUNT_DUE_REMAINING), 0) FROM ar_payment_schedules arp WHERE (arp.customer_id = :cust_account_id and arp.status = 'OP') and ( trunc(sysdate)-trunc(arp.due_date) >= :days_start and trunc(sysdate)-trunc(arp.due_date) <= :days_to and arp.class = :class )
7z0tsy9zxk6p0	SELECT count(distinct ocahb.id) FROM ar_payment_schedules arp, ar_aging_bucket_lines aabl, ra_customer_trx rct, okl_cnsld_ar_strms_b ocasb, OKL_CNSLD_AR_LINES_B ocalb, OKL_CNSLD_AR_HDRS_B ocahb WHERE (:l_date - arp.due_date) >= aabl.days_start and (:l_date - arp.due_date) <= aabl.days_to and arp.class = :l_class and rct.customer_trx_id = arp.customer_trx_id and ocasb.receivables_invoice_id = rct.customer_trx_id and ocalb.id = ocasb.lln_id and ocahb.id = ocalb.cnr_id and rct.interface_header_attribute9 <> :l_interface_attr and rct.interface_header_context = :l_interface_context and arp.status = :l_status and aabl.aging_bucket_line_id = :bucket_line_id AND arp.customer_id = :cust_account_id
89dhu8bb05uvn	/* OracleOEM */ declare lv_sql_stmt varchar2(10000); finalQry varchar2(2048); fromClause varchar2(512); lc_var1 varchar2(1000); lc_var2 number; lc_var3 number; fullQName varchar2(512); avg_msg_age number; TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; test_cursor sys_refcursor; v_error_code number; v_error_message varchar2(255); receivedB number := 0; processedB number := 0; receivedP number := 0; processedP number := 0; totalReceived number := 0; totalProcessed number := 0; finalPerc number := 0; begin begin begin EXECUTE IMMEDIATE 'SELECT * FROM AQ_MNTR_MSGS_QUEUE where rownum <=1'; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); -- If table does not exist before then create it IF v_error_code = -942 THEN -- ORA-00942: table or view does not exist EXECUTE IMMEDIATE 'CREATE TABLE AQ_MNTR_MSGS_QUEUE (FULL_QUEUE_NAME varchar2(256) NOT NULL PRIMARY KEY, schem a varchar2(256), queue_name varchar2(256), average_msg_age number, totalProcessed number, totalReceived number, proc_perc number)'; END IF; end; --If no exception means table exists already. Clean it up before reusing EXECUTE IMMEDIATE 'DELETE FROM AQ_MNTR_MSGS_QUEUE'; exception WHEN OTHERS THEN null; end; commit; -- Review comment => Do not include sys/system/sysman/dbsnmp schema as these might contain system queues for rec in (select owner, name, queue_table from dba_queues where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') ) LOOP dbms_output.put_line('+++++ Begin Processing for: ' ||rec.owner||' , '||rec.name|| ', '||rec.queue_table); begin -- Calculate the average message age per queue and store them in the AQ_MNTR_MSGS_QUEUE table begin fromClause := rec.owner||'.AQ$'||rec.queue_table; finalQry := 'select queue, avg(sysdate-ENQ_TIME) * 86400 AVERAGE_MSG_AGE from ' || fromClause ||' group by queue' ; open test_cursor for finalQry; loop lc_var1 := ''; lc_var2 : = 0; fullQName := ''; fetch test_cursor into lc_var1 , lc_var2 ; fullQName := rec.owner||'.'||lc_var1; begin IF lc_var2 != 0 THEN totalProcessed := null; totalReceived := null; finalPerc := null; execute immediate 'insert into AQ_MNTR_MSGS_QUEUE values (:1, :2, :3, :4, :5, :6, :7)' using fullQName, rec.owner, lc_var1, lc_var2, totalProcessed, totalReceived, finalPerc; commit; END IF; exception WHEN OTHERS THEN rollback; DBMS_OUTPUT.PUT_LINE(' ***** Unique constaint error ***** '); end; exit when test_cursor %notfound; dbms_output.put_line('FETCHED DATA: ' ||lc_var1||' , '||lc_var2|| ', '||fullQName); end loop; lc_var1 := ''; lc_var2 := 0; fullQName := ''; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating average age ***** '); end; -- Average message age per queue end -- Messages processed % per queue. Calculate and update the AQ_MNTR_MSGS_QUEUE table begin fullQName := ''; fullQName := rec.owner||'.'||rec.name; begin select B.CNUM_MSGS receivedB, B.CNUM_MSGS-B.EXPIRED_MSGS-B.NUM_MSGS processedB INTO receivedB, processedB from V$BUFFERED_QUEUES B where B.QUEUE_SCHEMA = rec.owner and B.QUEUE_NAME = rec.name; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** FIRST QUERY RETURNED NO DATA***** '); end; begin select P.ENQUEUED_MSGS receivedP , P.DEQUEUED_MSGS processedP INTO receivedP, processedP from V$PERSISTENT_QUEUES P where P.QUEUE_SCHEMA = rec.owner and P.QUEUE_NAME = rec.name; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** SECOND QUERY RETURNED NO DATA***** '); end; totalProcessed := processedB + processedP; totalReceived := receivedB + receivedP; IF totalReceived != 0 THEN finalPerc := (totalProcessed/totalReceived) * 100; execute immediate 'select count(*) from AQ_MNTR_MSGS_QUEUE where schema = :1 and queue_name = :2' into lc_var3 using rec.owner, rec.name; IF lc_var3 = 0 THEN avg_msg_age := null; execute immediate 'insert into AQ_MNTR_MSGS_QUEUE values (:1, :2, :3, :4, :5, :6, :7)' using fullQName, rec.owner, rec.name, avg_msg_age, totalProcessed, totalReceived, finalPerc; ELSE execute immediate 'update AQ_MNTR_MSGS_QUEUE set totalProcessed = :1, totalReceived = :2, proc_perc = :3 where FULL_QUEUE_NAME = :4 ' using totalProcessed, totalReceived, finalPerc, fullQName; END IF; commit; END IF; -- Reset after using receivedB := 0; processedB := 0; receivedP := 0; processedP := 0; totalReceived := 0; totalProcessed := 0; finalPerc := 0; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating processed % ***** '); end; -- Messages processed % per queue end exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUT PUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in main for loop ***** '); end; dbms_output.put_line('+++++ END Processing +++++ '); end loop; lv_sql_stmt:='select schema, queue_name, average_msg_age, totalProcessed, totalReceived, proc_perc from AQ_MNTR_MSGS_QUEUE'; open data_cursor for lv_sql_stmt; :1 := data_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE('>>>>>> Exception in main begin block <<<<<<<'); end;
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;
8zh8nt6uwvdr3	BEGIN QP_Attr_Mapping_PUB.G_Temp_MultiValue := OZF_QP_QUAL_PVT.get_lists(ASO_PRICING_INT.G_HEADER_REC.party_id, OE_ORDER_PUB.G_LINE.SOLD_TO_ORG_ID); END;
a14wcsg9mk8j3	WITH a as (SELECT a$.dbid, a$.snap_id, a$.INSTANCE_NUMBER, a$.PLSQL_ENTRY_OBJECT_ID, a$.PLSQL_ENTRY_SUBPROGRAM_ID, a$.PLSQL_OBJECT_ID, a$.PLSQL_SUBPROGRAM_ID, a$.TIME_MODEL FROM WRH$_ACTIVE_SESSION_HISTORY a$ WHERE a$.dbid = :dbid AND a$.instance_number = :inst AND a$.snap_id > :bid AND a$.snap_id <= :eid AND a$.session_type = 1 ) , ash as (SELECT cnt , PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID FROM ( SELECT count(*) as cnt , a.PLSQL_OBJECT_ID, a.PLSQL_SUBPROGRAM_ID FROM a WHERE bitand(a.time_model, power(2, 11 )) !=0 AND a.PLSQL_ENTRY_OBJECT_ID = :plsqleid AND a.PLSQL_ENTRY_SUBPROGRAM_ID = :plsqlesid GROUP BY PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID HAVING count(*) >= :rowcnt ORDER BY 1 DESC, 2 ASC ) WHERE rownum <= :ret) SELECT ash.plsql_object_id, ash.plsql_subprogram_id, dba.object_name, dba.object_type, dba.owner, dba.procedure_name, to_number(dba.overload), ash.cnt FROM dba_procedures dba, ash WHERE ash.plsql_object_id = dba.object_id(+) AND ash.plsql_su bprogram_id = dba.subprogram_id(+) ORDER BY ash.cnt DESC, ash.plsql_object_id, ash.plsql_subprogram_id
ab7ktcdksu27j	/* OracleOEM */ SELECT /*+ INDEX(ts) */ o.object_type, u.name, o.object_name, o.partition, o.lob_column, o.seg_type, ts.name, 4 FROM ( SELECT 1 "OBJECT_TYPE", o.owner# "USER_NAME#", o.name "OBJECT_NAME", ' ' "PARTITION", ' ' "LOB_COLUMN", 1 "SEG_TYPE", t.ts# "TABLESPACE#" FROM sys."_CURRENT_EDITION_OBJ" o, sys.tab$ t, sys.seg$ s WHERE o.obj# = t.obj# AND t.ts# in (:1, :2) AND t.file# = s.file# AND t.block# = s.block# AND t.ts# = s.ts# AND decode (bitand (t.property, 1), 1, 1, 0) = 0 AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND decode (bitand (t.property, 1024), 1024, 1, 0) = 0 UNION ALL SELECT 2, o.owner#, o.name, ' ', ' ', 2, i.ts# FROM sys."_CURRENT_EDITION_OBJ" o, sys.ind$ i, sys.seg$ s WHERE o.obj# = i.obj# AND i.ts# in (:1, :2) AND i.file # = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AND i.type# not in (4, 8) UNION ALL SELECT 3, o.owner#, o.name, ' ', ' ', 12, c.ts# FROM sys."_CURRENT_EDITION_OBJ" o, sys.clu$ c, sys.seg$ s WHERE o.obj# = c.obj# AND c.ts# IN (:1, :2) AND c.file# = s.file# AND c.block# = s.block# AND c.ts# = s.ts# UNION ALL SELECT 1, o.owner#, tn.name, ' ', ' ', 1, i.ts# FROM sys."_CURRENT_EDITION_OBJ" o, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" tn, sys.ind$ i WHERE o.obj# = i.obj# AND i.ts# IN (:1, :2) AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND i.bo# = tn.obj# AND tn.obj# = t.obj# AND decode (bitand (i.property, 2), 2, 1, 0) = 0 AND i.type# = 4 UNION ALL SELECT 1, o.owner#, o.name, o.subname, ' ', 3, tp.ts# FROM sys."_CURRENT_EDITION_OBJ" o, sys.seg$ s, sys.tab$ t, sys.tabpart$ tp WHERE o.obj# = tp.obj# AND tp.ts# IN (:1, :2) AND tp.file# = s.file# AND tp.block# = s.block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 UNION ALL SELECT 2, o.owner#, o.name, o.subname, ' ', 4, ip.ts# FROM sys."_CURRENT_EDITION_OBJ" o, sys.seg$ s, sys.ind$ i, sys.indpart$ ip WHERE o.obj# = ip.obj# AND ip.ts# IN (:1, :2) AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# not in (4, 8) UNION ALL SELECT 1, o.owner#, tn.name, ' ', ' ', 5, t.ts# FROM sys."_CURRENT_EDITION_OBJ" o, sys.seg$ s, sys.tab$ t, sys."_CURRENT_EDITION_OBJ" tn, sys.tab$ iot WHERE o.obj# = t.obj# AND t.ts# IN (:1, :2) AND t.file# = s.file# AND t.block# = s.block# AND t.ts# = s.ts# AND tn.obj# = t.bobj# AND tn.obj# = iot.obj# AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 UNION ALL SELECT 1, tn.owner#, tn.name, ' ', decode(bitand(c.property, 1), 1, substr(ac.name, 0, 500), c.name), 8, l.ts# FROM sys.seg$ s, sys."_CURRENT_EDITION_OBJ" tn, sys.attrcol$ ac, sys.col$ c, sys.lob$ l WHERE tn.obj# = c.obj# AND c.obj# = l.obj# AND c.intcol# = l.intcol# AND l.ts# IN (:1, :2) AND l.file# = s.file# AND l.block# = s.block# AND l.ts# = s.ts# AND c.obj# = ac.obj#(+) AND c.intcol# = ac.intcol#(+) AND bitand(c.property, 32768) != 32768 UNION ALL SELECT 1, o.owner#, tn.name, o.subname, ' ', 3, ip.ts# FROM sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys.ind$ i, sys."_CURRENT_EDITION_OBJ" tn, sys.indpart$ ip WHERE o.obj# = ip.obj# AND ip.ts# IN (:1, :2) AND ip.file# = s.file# AND ip.block# = s.block# AND ip.ts# = s.ts# AND i.obj# = ip.bo# AND decode (bitand (i.property, 2), 2, 1, 0) = 1 AND i.type# = 4 AND i.bo# = tn.obj# UNION ALL SELECT 1, o.owner #, tbo.name, o.subname, ' ', 13, tp.ts# FROM sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" tbo, sys.tab$ t, sys.tabpart$ tp WHERE o.obj# = tp.obj# AND tp.ts# IN (:1, :2) AND tp.file# = s.file# AND tp.block# = s.block# AND tp.ts# = s.ts# AND t.obj# = tp.bo# AND decode (bitand (t.property, 32), 32, 1, 0) = 1 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 1 AND tbo.obj# = t.bobj# UNION ALL SELECT 1, tbo.owner#, tbo.name, po.subname, decode(bitand(c.property, 1), 1, substr(a.name, 0, 500), c.name), 8, lf.ts# FROM sys.seg$ s, sys."_CURRENT_EDITION_OBJ" tbo, sys.attrcol$ a, sys.col$ c, sys.lob$ l, sys."_CURRENT_EDITION_OBJ" po, sys.partobj$ pobj, sys.lobfrag$ lf WHERE pobj.obj# = tbo.obj# AND lf.ts# IN (:1, :2) AND lf.file# = s.file# AND lf.block# = s.block# AND lf.ts# = s.ts# AND mod(pobj.spare2, 256) = 0 AND tbo.obj# = c.obj# AND c.obj # = l.obj# AND c.intcol# = l.intcol# AND l.lobj# = lf.parentobj# AND lf.tabfragobj# = po.obj# AND bitand(c.property, 32768) != 32768 AND c.obj# = a.obj#(+) AND c.intcol# = a.intcol#(+) UNION ALL SELECT 1, o.owner#, bo.name, o.subname, ' ', 9, tsp.ts# FROM sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" bo, sys.tabsubpart$ tsp WHERE o.obj# = tsp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 2 AND tsp.file# = s.file# AND tsp.block# = s.block# AND tsp.ts# IN (:1, :2) UNION ALL SELECT 2, o.owner#, bo.name, o.subname, ' ', 10, isp.ts# FROM sys.seg$ s, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" bo, sys.indsubpart$ isp WHERE o.obj# = isp.obj# AND bo.owner# = o.owner# AND bo.name = o.name AND bo.type# = 1 AND isp.file# = s.file# AND isp.block# = s.block# AND isp.ts# IN (:1, :2) ) o, sys.ts$ ts, sys.user$ u WHERE o.USER_NAME# = u.user# AND u.name NOT IN ('MGMT_VIEW', 'OUTLN', 'ANON YMOUS', 'DMSYS', 'LBACSYS', 'WKPROXY', 'TRACESVR', 'SCOTT', 'ADAMS', 'BLAKE', 'CLARK', 'JONES', 'HR', 'PM', 'OE', 'SH', 'IX') AND u.name NOT IN (SELECT DISTINCT schema_name FROM V$SYSAUX_OCCUPANTS) AND u.name NOT LIKE 'FLOWS!_%' escape '!' AND o.TABLESPACE# = ts.ts# AND u.type# !=2 AND ts.ts# IN (:1, :2) AND rownum < 201
akjhjsy8w1qz6	SELECT QPSEG.SEGMENT_MAPPING_COLUMN, QPSOUR.USER_SOURCING_TYPE SRC_TYPE, QPSOUR.USER_VALUE_STRING VALUE_STRING, QPCON.PRC_CONTEXT_CODE CONTEXT_CODE, QPCON.PRC_CONTEXT_TYPE CONTEXT_TYPE FROM QP_SEGMENTS_B QPSEG, QP_ATTRIBUTE_SOURCING QPSOUR, QP_PRC_CONTEXTS_B QPCON, QP_PTE_REQUEST_TYPES_B QPREQ, QP_PTE_SEGMENTS QPPSEG WHERE QPSOUR.SEGMENT_ID = QPSEG.SEGMENT_ID AND QPSOUR.ATTRIBUTE_SOURCING_LEVEL = :B2 AND QPSOUR.ENABLED_FLAG = 'Y' AND QPPSEG.SOURCING_STATUS = 'N' AND QPPSEG.USED_IN_SETUP = 'Y' AND QPPSEG.USER_SOURCING_METHOD = 'ATTRIBUTE MAPPING' AND QPSOUR.REQUEST_TYPE_CODE = :B1 AND QPSEG.PRC_CONTEXT_ID = QPCON.PRC_CONTEXT_ID AND QPREQ.REQUEST_TYPE_CODE = QPSOUR.REQUEST_TYPE_CODE AND QPPSEG.PTE_CODE = QPREQ.PTE_CODE AND QPPSEG.SEGMENT_ID = QPSOUR.SEGMENT_ID AND QPPSEG.SOURCING_ENABLED = 'Y' AND QPCON.PRC_CONTEXT_TYPE IN ('PRICING_ATTRIBUTE', 'PRODUCT', 'QUALIFIER')
b8p7spcxbmqda	select queue, avg(sysdate-ENQ_TIME) * 86400 AVERAGE_MSG_AGE from APPLSYS.AQ$WF_NOTIFICATION_OUT group by queue
cf030hrp7d40k	SELECT PARTY_ID FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID = :B1
fbxfzbmhtvfgc	select count(descriptive_flex_context_code) l_context_count from fnd_descr_flex_contexts where application_id = 7003 and descriptive_flexfield_name = :1 and substr(descriptive_flex_context_code, 4, 2) = :2 and substr(descriptive_flex_context_code, 7, 8) = :3 and enabled_flag = 'Y'
fjvwzpxbpch0h	/* OracleOEM */ select capture_name streams_name, 'capture' streams_type , (available_message_create_time- capture_message_create_time)*86400 latency, nvl(total_messages_enqueued, 0) total_messages from gv$streams_capture union all select propagation_name streams_name, 'propagation' streams_type, last_lcr_latency latency , total_msgs total_messages from gv$propagation_sender where propagation_name is not null union all select server_name streams_name, 'apply' streams_type, (send_time-last_sent_message_create_time)*86400 latency, nvl(total_messages_sent, 0) total_messages from gv$xstream_outbound_server where committed_data_only='NO' union all SELECT distinct apc.apply_name as STREAMS_NAME, 'apply' as STREAMS_TYPE, CASE WHEN aps.state != 'IDLE' THEN nvl((aps.apply_time - aps.create_time)*86400, -1) WHEN apc.state != 'IDLE' THEN nvl((apc.apply_time - apc.create_time)*86400, -1) WHEN apr.state != 'IDLE' THEN nvl((apr.apply_time - apr.create_time)*86400, -1) ELSE 0 END as STR EAMS_LATENCY, nvl(aps.TOTAL_MESSAGES_APPLIED, 0) as TOTAL_MESSAGES FROM ( SELECT apply_name, state, apply_time, applied_message_create_time as create_time, total_messages_applied FROM ( SELECT apply_name, state, apply_time, applied_message_create_time, MAX(applied_message_create_time) OVER (PARTITION BY apply_name) as max_create_time, SUM(total_messages_applied) OVER (PARTITION BY apply_name) as total_messages_applied FROM gv$streams_apply_server ) WHERE MAX_CREATE_TIME||'X' = APPLIED_MESSAGE_CREATE_TIME||'X' ) aps, ( SELECT c.apply_name, state, -- This is the XOUT case c.hwm_time as apply_time, hwm_message_create_time as create_time, total_applied FROM gv$streams_apply_coordinator c, dba_apply p WHERE p.apply_name = c.apply_name and p.apply_name in (select server_name from dba_xstream_outbound) union SELECT c.apply_name, state, -- This is non-XOUT case c.lwm_time as apply_time, lwm_message_create_time as create_time, total_applied FROM gv$streams_apply_coordinator c, dba_apply p WHERE p.apply_name = c.apply_name and p.apply_name not in (select server_name from dba_xstream_outbound) ) apc, ( SELECT apply_name, state, dequeue_time as apply_time, dequeued_message_create_time as create_time FROM gv$streams_apply_reader ) apr WHERE apc.apply_name = apr.apply_name AND apr.apply_name = aps.apply_name
gd3aqp7xuk8yd	begin sys.dbms_backup_restore.createRmanStatusRow( level => :level, parent_id => :pid, parent_stamp => :pts, status => :status, command_id => :command_id, operation => :operation, row_id => :row_id, row_stamp => :row_stamp, flags => :flags); end;
gjm43un5cy843	SELECT SUM(USED), SUM(TOTAL) FROM (SELECT /*+ ORDERED */ SUM(D.BYTES)/(1024*1024)-MAX(S.BYTES) USED, SUM(D.BYTES)/(1024*1024) TOTAL FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) BYTES FROM (SELECT /*+ ORDERED USE_NL(obj tab) */ DISTINCT TS.NAME FROM SYS.OBJ$ OBJ, SYS.TAB$ TAB, SYS.TS$ TS WHERE OBJ.OWNER# = USERENV('SCHEMAID') AND OBJ.OBJ# = TAB.OBJ# AND TAB.TS# = TS.TS# AND BITAND(TAB.PROPERTY, 1) = 0 AND BITAND(TAB.PROPERTY, 4194400) = 0) TN, DBA_FREE_SPACE SP WHERE SP.TABLESPACE_NAME = TN.NAME GROUP BY SP.TABLESPACE_NAME) S, DBA_DATA_FILES D WHERE D.TABLESPACE_NAME = S.TABLESPACE_NAME GROUP BY D.TABLESPACE_NAME)
gmmq9hrnkkssh	Select distinct LOOKUP_CODE tax_code, description, tax_type From zx_output_classifications_v Where lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS' and org_id in (:1, -99) and enabled_flag = 'Y' and :2 between nvl(start_date_active, :3) and nvl(end_date_active, :4)

Back to SQL Statistics
Back to Top

Instance Activity Statistics

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

Key Instance Activity Stats

• Ordered by statistic name

Back to Instance Activity Statistics
Back to Top

Other Instance Activity Stats

• Ordered by statistic name

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

• Statistics identified by '(derived)' come from sources other than SYSSTAT

Back to Instance Activity Statistics
Back to Top

IO Stats

• IOStat by Function summary
• IOStat by Filetype summary
• IOStat by Function/Filetype summary
• Tablespace IO Stats
• File IO Stats

IOStat by Function summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• ordered by (Data Read + Write) desc

Back to IO Stats
Back to Top

IOStat by Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Small Read and Large Read are average service times, in milliseconds
• Ordered by (Data Read + Write) desc

Back to IO Stats
Back to Top

IOStat by Function/Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Ordered by (Data Read + Write) desc for each function

Back to IO Stats
Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File