WORKLOAD REPOSITORY report for

DB Name	DB Id	Unique Name	Role	Edition	Release	RAC	CDB
ABS1	2726665232	ABS1	PRIMARY	EE	19.29.0.0.0	NO	NO

Instance	Inst Num	Startup Time	User Name	System Data Visible
ABS1	1	06-Mar-26 21:37	SYS	YES

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
CLSABSDB01	Linux x86 64-bit	10	10	10	62.89

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	2724	10-Mar-26 10:00:25	357	4.4
End Snap:	2725	10-Mar-26 11:00:39	343	4.5
Elapsed:		60.24 (mins)
DB Time:		62.38 (mins)

Report Summary

Top ADDM Findings by Average Active Sessions

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	1.0	0.0	0.00	0.00
DB CPU(s):	0.9	0.0	0.00	0.00
Background CPU(s):	0.0	0.0	0.00	0.00
Redo size (bytes):	436,440.9	2,619.4
Logical read (blocks):	65,185.2	391.2
Block changes:	2,202.1	13.2
Physical read (blocks):	477.9	2.9
Physical write (blocks):	198.0	1.2
Read IO requests:	298.4	1.8
Write IO requests:	156.3	0.9
Read IO (MB):	3.7	0.0
Write IO (MB):	1.6	0.0
IM scan rows:	0.0	0.0
Session Logical Read IM:	0.0	0.0
User calls:	5,696.8	34.2
Parses (SQL):	2,404.4	14.4
Hard parses (SQL):	4.3	0.0
SQL Work Area (MB):	47.2	0.3
Logons:	0.1	0.0
User logons:	0.1	0.0
Executes (SQL):	3,345.0	20.1
Rollbacks:	127.1	0.8
Transactions:	166.6

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.28	In-memory Sort %:	100.00
Library Hit %:	99.70	Soft Parse %:	99.82
Execute to Parse %:	28.12	Latch Hit %:	99.99
Parse CPU to Parse Elapsd %:	66.89	% Non-Parse CPU:	97.07
Flash Cache Hit %:	0.00

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
10	10	10	1.74	1.81	9.5	2.3	4.8	86.4

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
8.9	65.3	0.0

IO Profile

	Read+Write Per Second	Read per Second	Write Per Second
Total Requests:	509.2	308.4	200.8
Database Requests:	454.7	298.4	156.3
Optimized Requests:	0.0	0.0	0.0
Redo Requests:	42.6	0.4	42.2
Total (MB):	6.7	4.3	2.4
Database (MB):	5.3	3.7	1.6
Optimized Total (MB):	0.0	0.0	0.0
Redo (MB):	0.8	0.4	0.4
Database (blocks):	676.0	477.9	198.0
Via Buffer Cache (blocks):	669.2	471.2	198.0
Direct (blocks):	6.8	6.7	0.1

Memory Statistics

	Begin	End
Host Mem (MB):	64,399.6	64,399.6
SGA use (MB):	35,072.0	35,072.0
PGA use (MB):	3,400.3	3,390.6
% Host Mem used for SGA+PGA:	59.74	59.72

Database Resource Limits

	Begin	End
CPUs:	10	10
SGA Target:	36,775,657,472	36,775,657,472
PGA Target:	9,791,602,688	9,791,602,688
Memory Target	0	0

Cache Sizes

	Begin	End
Buffer Cache:	27,136M	27,136M	Std Block Size:	8K
Shared Pool Size:	7,296M	7,296M	Log Buffer:	113,088K
In-Memory Area:	0M	0M

Shared Pool Statistics

	Begin	End
Memory Usage %:	80.78	73.14
% SQL with executions>1:	95.88	70.66
% Memory for SQL w/exec>1:	92.98	58.55

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
• Replication Statistics (GoldenGate, XStream)
• Advanced Queuing
• Resource Limit Statistics
• Shared Server Statistics
• Initialization Parameters
• Active Session History (ASH) Report
• ADDM Reports

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 (up to 64 us)
• Wait Event Histogram (up to 32 ms)
• Wait Event Histogram (up to 2 sec)
• Wait Event Histogram (up to 2 min)
• Wait Event Histogram (up to 1 hr)
• Service Statistics
• Service Wait Class Stats
• Top 10 Channel Waits
• Top Process Types by Wait Class
• Top Process Types by CPU Used

Time Model Statistics

• DB Time represents total time in user calls
• DB CPU represents CPU time of foreground processes
• Total CPU Time represents foreground and background processes
• Statistics including the word "background" measure background process time, therefore do not contribute to the DB time statistic
• Ordered by % of DB time in descending order, followed by 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, us - microsecond, ns - nanosecond
• 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 108.3% of Total DB time 3,743.10 (s)
• Total FG Wait Time: 978.93 (s) DB CPU time: 3,073.95 (s)

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

• s - second, ms - millisecond, us - microsecond, ns - nanosecond
• 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
• Units for % of Total Waits: us is microseconds
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (idle events last)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 64 us)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: us is microseconds
• % 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 (up to 32 ms)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: us is microseconds, ms is 1024 microseconds (approximately 1 millisecond)
• % 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 (up 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 (up 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.12 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 (up 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

Top 10 Channel Waits

• Top 10 Channel Waits by wait time

Back to Wait Events Statistics
Back to Top

Top Process Types by Wait Class

• This table displays top process types ordered by wait class

Back to Wait Events Statistics
Back to Top

Top Process Types by CPU Used

• This table displays top process types ordered by CPU time

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 28.5% of Total DB Time (s): 3,743
• Captured PL/SQL account for 16.9% of Total DB Time (s): 3,743

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 23.4% of Total CPU Time (s): 3,074
• Captured PL/SQL account for 11.3% of Total CPU Time (s): 3,074

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 38.9% of Total User I/O Wait Time (s): 571
• Captured PL/SQL account for 27.6% of Total User I/O Wait Time (s): 571

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: 235,597,020
• Captured SQL account for 52.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,727,378
• Captured SQL account for 36.9% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

• UnOptimized Read Reqs = Physical Read Reqs - (Optimized Read Reqs - Cell Flash Cache Read Hits for Controlfile)
• %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: 1,078,446
• Captured SQL account for 15.0% of Total
• Total UnOptimized Read Requests: 1,078,446
• Captured SQL account for 15.0% 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: 12,089,613
• Captured SQL account for 19.1% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 8,690,307
• Captured SQL account for 25.9% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

• Only Statements with Sharable Memory greater than 1048576 are displayed

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

• Only Statements with Version Count greater than 20 are displayed

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
01ajbnqh38y8r	select /*+ index(uri XIE1UNIQUERESIDENTIALITEM) */ uri.uniqueresidentialitem_id, uri.primaryid, c.name, dep.description, dr.roomnumber, cl.name, cl.gender, (select pd.description from product_desc pd where pd.product_id = uip.product_id and pd.language_id = wu.language_id) from UniqueResidentialItem uri, Client cl, ClientResident cr, DepartmentRoom dr, Department dep, Customer c, UniqueResidentialItemProduct uip, Product p, WebUser wu, WebUserCustomer wc where substr(uri.primaryid, -:1 ) = :2 and cl.client_id = uri.client_id and cr.client _id = uri.client_id and dr.departmentroom_id = cr.departmentroom_id and dep.department_id = dr.department_id and c.customer_id = dep.customer_id and uip.uniqueresidentialitem_id = uri.uniqueresidentialitem_id and p.product_id = uip.product_id and wu.webuser_id = :3 and wc.webuser_id = wu.webuser_id and wc.customer_id = dep.customer_id
0cpnp62fzhgub	SELECT "CUSTOMER_LINK_ID", "DAY_ID", "INSCAN", "LABELS", "INSCANPOOL", "BU", "BUDESC" FROM "ABSSOLUTE"."V_DWH_RTCV_08" "V"
0za9fv0j1vgkk	WITH MONITOR_DATA AS (SELECT * FROM TABLE(GV$(CURSOR( SELECT USERENV('instance') AS INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, DBOP_NAME, DBOP_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_FULL_PLAN_HASH_VALUE, SESSION_SERIAL#, SQL_TEXT, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET, PX_QCINST_ID, PX_QCSID, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) THEN (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) ELSE ELAPSED_TIME END ELAPSED_TIME, QUEUING_TIME, CPU_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, CASE WHEN ELAPSED_TIME < ( CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) THEN 0 ELSE ELAPSED_TIME - (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) END OTHER_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, FETCHES, BUFFER_GETS, IO_INTERCONNECT_BYTES IO_INTER_BYTES, PHYSICAL_READ_REQUESTS READ_REQS, PHYSICAL_READ_BYTES READ_BYTES, PHYSICAL_WRITE_REQUESTS WRITE_REQS, PHYSICAL_WRITE_BYTES WRITE_BYTES, NVL(PHYSICAL_READ_BYTES, 0) + NVL(PHYSICAL_WRITE_BYTES, 0) IO_BYTES, NVL(PHYSICAL_READ_REQUESTS, 0) + NVL(PHYSICAL_WRITE_REQUESTS, 0) IO_REQS, IO_CELL_UNCOMPRESSED_BYTES IO_UNC_BYTES, IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_ELIG_BYTES, IO_CELL_OFFLOAD_RETURNED_BYTES IO_RET_BYTES, USER# USERID, USERNAME, MODULE, ACTION, SERVICE_NAME, CLIENT_IDENTIFIER, CLIENT_INFO, PROGRAM, PLSQL_OBJECT_ID PL_OID, PLSQL_SUBPROGRAM_ID PL_PROGID, PLSQL_ENTRY_OBJECT_ID PL_ENTRY_OID, PLSQL_ENTRY_SUB PROGRAM_ID PL_ENTRY_PROGID, PX_MAXDOP MAX_DOP, PX_IS_CROSS_INSTANCE, PX_MAXDOP_INSTANCES MAX_DOP_INSTANCES, PX_SERVERS_REQUESTED SERVERS_REQUESTED, PX_SERVERS_ALLOCATED SERVERS_ALLOCATED, ERROR_NUMBER, ERROR_FACILITY, ERROR_MESSAGE, NVL2(OTHER_XML, 'Y', NULL) HAS_OTHER_XML, NVL2(BINDS_XML, 'Y', NULL) HAS_BINDS_XML, NVL2(RM_CONSUMER_GROUP, NVL2(RM_LAST_ACTION_TIME, TO_CHAR(RM_LAST_ACTION_TIME, :B12 ), '00/00/0000 00:00:01') || XMLELEMENT( "rminfo", XMLATTRIBUTES( RM_LAST_ACTION AS "rmlastact", RM_LAST_ACTION_REASON AS "rmlastreason", TO_CHAR(RM_LAST_ACTION_TIME, :B12 ) AS "rmlasttime", RM_CONSUMER_GROUP AS "rmcg")).GETSTRINGVAL(), '00/00/0000 00:00:00') RM_INFO, CON_NAME, CON_ID FROM V$ALL_SQL_MONITOR MO1 WHERE USERENV('INSTANCE') BETWEEN :B11 AND :B10 AND MO1.SQL_ID = NVL(:B9 , MO1.SQL_ID) AND MO1.SQL_EXEC_START = NVL(:B8 , MO1.SQL_EXEC_START) AND MO1.SQL_EXEC_ID = NVL(:B7 , MO1.SQL_EXEC_ID) OR (MO1.DBOP_NA ME = NVL(:B6 , MO1.DBOP_NAME) AND MO1.DBOP_EXEC_ID = NVL(:B5 , MO1.DBOP_EXEC_ID)) AND ((:B4 = 1 AND MO1.PX_QCSID IS NULL) OR (MO1.PX_SERVER_GROUP = NVL(:B3 , MO1.PX_SERVER_GROUP) AND MO1.PX_SERVER_SET = NVL(:B2 , MO1.PX_SERVER_SET) AND MO1.PX_SERVER# = NVL(:B1 , MO1.PX_SERVER#))) ))) ), MONITOR_AGG AS (SELECT MAX_PX_QCSID, MAX_KEY, MAX_INST_ID, MAX_SESSION_ID, MAX_SESSION_SERIAL, MAX_PX_DOP, MAX_PX_DOP_INSTANCES, MAX_PX_IS_CROSS_INSTANCE, SUM_SERVERS_REQUESTED, SUM_SERVERS_ALLOCATED, DIST_INST_COUNT, DIST_PX_GROUP_COUNT, DIST_PX_SET_COUNT, MAX_PLAN_HASH_VALUE, MAX_FULL_PLAN_HASH_VALUE, MAX_USERID, MAX_PROGRAM, MAX_USERNAME, MAX_MODULE, MAX_ACTION, MAX_SERVICE_NAME, MAX_CLIENT_ID, MAX_CLIENT_INFO, MAX_ERROR_NUMBER, MAX_ERROR_FACILITY, MAX_ERROR_MESSAGE, QC_HAS_OTHER_XML, QC_HAS_BINDS_XML, MAX_PL_OID, MAX_PL_PROGID, MAX_PL_ENTRY_OID, MAX_PL_ENTRY_PROGID, MAX_SQL_ID, MAX_SQL_EXEC_START, MAX_SQL_EXEC_ID, MAX_LAST_REFRESH_TIME, MAX_DBOP_NAME, MAX_D BOP_EXEC_ID, CASE WHEN MAX_PL_OID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM ALL_PROCEDURES P WHERE P.OBJECT_ID = MAX_PL_OID AND P.SUBPROGRAM_ID = MAX_PL_PROGID AND ROWNUM = 1), 'Unavailable') END MAX_PL_NAME, CASE WHEN MAX_PL_ENTRY_OID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM ALL_PROCEDURES P WHERE P.OBJECT_ID = MAX_PL_ENTRY_OID AND P.SUBPROGRAM_ID = MAX_PL_ENTRY_PROGID AND ROWNUM = 1), 'Unavailable') END MAX_PL_ENTRY_NAME, MAX_STATUS, SUM_REFRESH_COUNT, MIN_FIRST_REFRESH_TIME, SQLMON_TEXT, SUM_ELAPSED_TIME, MAX_ELAPSED_TIME, MAX_QUEUING_TIME, SUM_CPU_TIME, SUM_USER_IO_WAIT_TIME, SUM_APPLICATION_WAIT_TIME, SUM_CONCURRENCY_WAIT_TIME, SUM_CLUSTER_WAIT_TIME, SUM_PLSQL_EXEC_TIME, SUM_JAVA_EXEC_TIME, SUM_OTHER_WAIT_TIME, SUM_FETCHES, SUM_BUFFER_GETS, SUM_READ_REQS, SUM_READ_BYTES, SUM_WRIT E_REQS, SUM_WRITE_BYTES, SUM_IO_BYTES, SUM_IO_INTER_BYTES, SUM_IO_UNC_BYTES, SUM_IO_ELIG_BYTES, SUM_IO_RET_BYTES, DECODE(:B14 , 1, 'db_name', SYS_CONTEXT('userenv', 'db_unique_name')) DB_UNIQUE_NAME, DECODE(:B14 , 1, 'platform_name', SYS.DBMS_UTILITY.PORT_STRING) PLATFORM_NAME, DECODE(:B14 , 1, 'host_name', SYS_CONTEXT('userenv', 'server_host')) HOST_NAME, AGG_RM_INFO MAX_RM_INFO, MAX_CON_NAME, DECODE(MAX_CON_ID, 0, NULL, MAX_CON_ID) MAX_CON_ID FROM (SELECT MAX(PX_QCSID) MAX_PX_QCSID, MAX(CASE WHEN PX_QCSID IS NULL THEN KEY ELSE NULL END) MAX_KEY, MAX(CASE WHEN PX_QCSID IS NULL THEN INST_ID ELSE NULL END) MAX_INST_ID, MAX(CASE WHEN PX_QCSID IS NULL THEN SID ELSE NULL END) MAX_SESSION_ID, MAX(CASE WHEN PX_QCSID IS NULL THEN SESSION_SERIAL# ELSE NULL END) MAX_SESSION_SERIAL, MAX(MAX_DOP) MAX_PX_DOP, MAX(MAX_DOP_INSTANCES) MAX_PX_DOP_INSTANCES, MAX(PX_IS_CROSS_INSTANCE) MAX_PX_IS_CROSS_INSTANCE, SUM(SERVERS_REQUESTED) SUM_SERVERS_REQUESTED, SUM(SERVERS_A LLOCATED) SUM_SERVERS_ALLOCATED, COUNT(DISTINCT INST_ID) DIST_INST_COUNT, COUNT(DISTINCT PX_SERVER_GROUP) DIST_PX_GROUP_COUNT, COUNT(DISTINCT PX_SERVER_SET) DIST_PX_SET_COUNT, MAX(SQL_PLAN_HASH_VALUE) MAX_PLAN_HASH_VALUE, MAX(SQL_FULL_PLAN_HASH_VALUE) MAX_FULL_PLAN_HASH_VALUE, MAX(USERID) MAX_USERID, MAX(PROGRAM) MAX_PROGRAM, MAX(USERNAME) MAX_USERNAME, MAX(MODULE) MAX_MODULE, MAX(ACTION) MAX_ACTION, MAX(SERVICE_NAME) MAX_SERVICE_NAME, MAX(CLIENT_IDENTIFIER) MAX_CLIENT_ID, MAX(CLIENT_INFO) MAX_CLIENT_INFO, MAX(ERROR_NUMBER) MAX_ERROR_NUMBER, MAX(ERROR_FACILITY) MAX_ERROR_FACILITY, MAX(ERROR_MESSAGE) MAX_ERROR_MESSAGE, MAX(NVL2(PX_QCSID, HAS_OTHER_XML, NULL)) QC_HAS_OTHER_XML, MAX(HAS_BINDS_XML) QC_HAS_BINDS_XML, MAX(PL_OID) MAX_PL_OID, MAX(PL_PROGID) MAX_PL_PROGID, MAX(PL_ENTRY_OID) MAX_PL_ENTRY_OID, MAX(PL_ENTRY_PROGID) MAX_PL_ENTRY_PROGID, MAX(SQL_ID) MAX_SQL_ID, MAX(SQL_EXEC_START) MAX_SQL_EXEC_START, MAX(SQL_EXEC_ID) MAX_SQL_EXEC_ID, MAX(LAST_REFRESH_TIM E) MAX_LAST_REFRESH_TIME, MAX(STATUS) MAX_STATUS, SUM(REFRESH_COUNT) SUM_REFRESH_COUNT, MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME, MAX(DBOP_NAME) MAX_DBOP_NAME, MAX(DBOP_EXEC_ID) MAX_DBOP_EXEC_ID, CASE WHEN :B13 = 0 THEN NULL ELSE MAX(SQL_TEXT) END SQLMON_TEXT, SUM(ELAPSED_TIME) SUM_ELAPSED_TIME, MAX(ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(QUEUING_TIME) MAX_QUEUING_TIME, SUM(CPU_TIME) SUM_CPU_TIME, SUM(USER_IO_WAIT_TIME) SUM_USER_IO_WAIT_TIME, SUM(APPLICATION_WAIT_TIME) SUM_APPLICATION_WAIT_TIME, SUM(CONCURRENCY_WAIT_TIME) SUM_CONCURRENCY_WAIT_TIME, SUM(CLUSTER_WAIT_TIME) SUM_CLUSTER_WAIT_TIME, SUM(PLSQL_EXEC_TIME) SUM_PLSQL_EXEC_TIME, SUM(JAVA_EXEC_TIME) SUM_JAVA_EXEC_TIME, SUM(OTHER_WAIT_TIME) SUM_OTHER_WAIT_TIME, SUM(FETCHES) SUM_FETCHES, SUM(BUFFER_GETS) SUM_BUFFER_GETS, SUM(READ_REQS) SUM_READ_REQS, SUM(READ_BYTES) SUM_READ_BYTES, SUM(WRITE_REQS) SUM_WRITE_REQS, SUM(WRITE_BYTES) SUM_WRITE_BYTES, NVL(SUM(READ_BYTES), 0) + NVL(SUM(WRITE_BYTES), 0) SUM_IO_B YTES, SUM(IO_INTER_BYTES) SUM_IO_INTER_BYTES, SUM(IO_UNC_BYTES) SUM_IO_UNC_BYTES, SUM(IO_ELIG_BYTES) SUM_IO_ELIG_BYTES, SUM(IO_RET_BYTES) SUM_IO_RET_BYTES, MAX(RM_INFO) AGG_RM_INFO, MAX(CON_NAME) MAX_CON_NAME, MAX(CON_ID) MAX_CON_ID FROM MONITOR_DATA) MD), ASH_DATA AS (SELECT AD0.INST_ID, AD0.SESSION_ID, AD0.PLAN_LINE_ID, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, AD0.ACTIVITY_BUCKET_NUM, AD0.PLAN_ACTIVITY_BUCKET_NUM, AD0.SQL, AD0.TOP_LEVEL_SQL_ID, AD0.DBOP_NAME, AD0.IS_MONITORED_SQL, AD0.IS_PX_SLAVE, AD0.BUCKET_ACTIVITY_START, AD0.ACTIVITY_START, AD0.BUCKET_ACTIVITY_END, AD0.ACTIVITY_END, AD0.ACTIVITY_COUNT, AD0.ACTIVITY_TYPE, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.IMQ_COUNT, AD0.WAIT_COUNT, AD0.CPU_COUNT, AD0.OTHER_SQL_COUNT, AD0.PX_SERVER_SET, AD0.PX_DFO_ACTIVITY_COUNT, AD0.DFO_MOST_ACTIVE_IID, AD0.DFO_MOST_ACTIVE_SID, (CASE WHEN AD0.DFO_MOST_ACTIVE_IID = AD0.INST_ID AND AD0.DFO_MOST_ACTIVE_SID = AD0.SESSION_ID AND (((AD0.PX_DFO_ACTIV ITY_COUNT / AD0.DFO_MOST_ACTIVE_COUNT) >= AD0.PX_DOP * 1.05) OR ((AD0.PX_DFO_ACTIVITY_COUNT / AD0.DFO_MOST_ACTIVE_COUNT) <= AD0.PX_DOP *0.95)) AND (AD0.DFO_DURATION * 100) > :B32 THEN AD0.ACTIVITY_COUNT ELSE NULL END) DFO_MOST_ACTIVE_COUNT, AD0.BUCKET_DISTINCT_SAMPLES, AD0.SQL_BUCKET_DISTINCT_SAMPLES, CASE WHEN AD0.PX_SERVER_GROUP IS NULL AND AD0.IS_PX_SLAVE = 1 THEN 1 ELSE AD0.PX_SERVER_GROUP END PX_SERVER_GROUP, AD0.PX_STEP_ID, AD0.PX_DFO_PAIR_TYPE, AD0.PX_DFO_PAIR_ID, AD0.PX_STEP_ARG, AD0.PX_DOP, CASE WHEN AD0.PX_DOP IS NOT NULL AND AD0.PX_DOP <> AD0.PX_MIN_DOP AND AD0.PX_MIN_DOP != 0 THEN PX_MIN_DOP ELSE NULL END PX_MIN_DOP FROM (SELECT /*+ use_hash(ash) leading(mo) */ ASH.INST_ID, ASH.IS_PX_SLAVE, ASH.SESSION_ID, ASH.PLAN_LINE_ID, ASH.PLSQL_OBJECT_ID, ASH.PLSQL_SUBPROGRAM_ID, ASH.ACTIVITY_BUCKET_NUM, ASH.PLAN_ACTIVITY_BUCKET_NUM, ASH.SQL, ASH.TOP_LEVEL_SQL_ID, ASH.DBOP_NAME, ASH.IS_MONITORED_SQL, ASH.BUCKET_ACTIVITY_START, ASH.ACTIVITY_START, ASH.BUCKET_ACTIVITY_END, ASH.ACTIVITY_END, ASH.ACTIVITY_COUNT, ASH.ACTIVITY_TYPE, ASH.OTHER_SQL_ACTIVITY_TYPE, ASH.EVENT_NAME, ASH.IMQ_COUNT, ASH.WAIT_COUNT, ASH.CPU_COUNT, ASH.OTHER_SQL_COUNT, MO.PX_SERVER_SET, ASH.PX_DFO_ACTIVITY_COUNT, TRUNC(ASH.MOST_ACTIVE_IN_DFO / 10000000000) DFO_MOST_ACTIVE_COUNT, MOD(TRUNC(ASH.MOST_ACTIVE_IN_DFO / 1000000), 10000) DFO_MOST_ACTIVE_IID, MOD(ASH.MOST_ACTIVE_IN_DFO, 1000000) DFO_MOST_ACTIVE_SID, ASH.DFO_DURATION, ASH.BUCKET_DISTINCT_SAMPLES, ASH.SQL_BUCKET_DISTINCT_SAMPLES, MO.PX_SERVER_GROUP, ASH.PX_STEP_ID, ASH.PX_DFO_PAIR_TYPE, ASH.PX_DFO_PAIR_ID, ASH.PX_STEP_ARG, ASH.PX_DOP, ASH.PX_MIN_DOP FROM (SELECT /*+ no_merge */ MD.INST_ID, MD.SID, MD.SESSION_SERIAL#, MD.PX_SERVER_SET, MD.PX_SERVER_GROUP FROM MONITOR_DATA MD WHERE MD.SID IS NOT NULL OR :B31 = 1) MO, (SELECT /*+ no_merge */ GVTF.INST_ID, GVTF.GLOBAL_SAMPLE_ID, GVTF.IS_PX_SLAVE, GVTF.SESSION_ID, GVTF.PLAN_LINE_ID, GVTF.PLSQL_OBJECT_ID, GVTF.PLSQL_SUBPROG RAM_ID, GVTF.ACTIVITY_BUCKET_NUM, GVTF.PLAN_ACTIVITY_BUCKET_NUM, GVTF.SQL, GVTF.TOP_LEVEL_SQL_ID, GVTF.DBOP_NAME, GVTF.IS_MONITORED_SQL, GVTF.BUCKET_ACTIVITY_START, GVTF.ACTIVITY_START, GVTF.BUCKET_ACTIVITY_END, GVTF.ACTIVITY_END, GVTF.ACTIVITY_COUNT, GVTF.ACTIVITY_TYPE, GVTF.OTHER_SQL_ACTIVITY_TYPE, GVTF.EVENT_NAME, GVTF.IMQ_COUNT, GVTF.WAIT_COUNT, GVTF.CPU_COUNT, GVTF.OTHER_SQL_COUNT, MAX(GVTF.PER_SERVER_DFO_COUNT * 10000000000 + GVTF.INST_ID * 1000000 + GVTF.SESSION_ID) OVER(PARTITION BY GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID) MOST_ACTIVE_IN_DFO, SUM(GVTF.ACTIVITY_COUNT) OVER(PARTITION BY GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID) PX_DFO_ACTIVITY_COUNT, GVTF.DFO_DURATION, GVTF.PX_STEP_ID, GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID, GVTF.PX_STEP_ARG, GVTF.PX_DOP, GVTF.PX_MIN_DOP, COUNT(DISTINCT GVTF.GLOBAL_SAMPLE_ID) OVER(PARTITION BY GVTF.ACTIVITY_BUCKET_NUM) BUCKET_DISTINCT_SAMPLES, COUNT(DISTINCT GVTF.GLOBAL_SAMPLE_ID) OVER(PARTITION BY GVTF.SQL , GVTF.ACTIVITY_BUCKET_NUM) SQL_BUCKET_DISTINCT_SAMPLES FROM TABLE(SYS.GV$(CURSOR( (SELECT USERENV('INSTANCE') INST_ID, ASH2.GLOBAL_SAMPLE_ID, CASE WHEN :B30 = 1 AND :B27 > 1 THEN BUCKET_NUM ELSE NULL END ACTIVITY_BUCKET_NUM, CASE WHEN :B29 = 1 AND :B27 > 1 THEN BUCKET_NUM ELSE NULL END PLAN_ACTIVITY_BUCKET_NUM, ASH2.SQL, ASH2.TOP_LEVEL_SQL_ID, ASH2.DBOP_NAME, ASH2.IS_MONITORED_SQL, ASH2.PLAN_LINE_ID, ASH2.PLSQL_OBJECT_ID, ASH2.PLSQL_SUBPROGRAM_ID, ASH2.ACTIVITY_TYPE, ASH2.OTHER_SQL_ACTIVITY_TYPE, ASH2.EVENT_NAME, ASH2.IS_PX_SLAVE, ASH2.SESSION_ID, ASH2.PX_STEP_ID, ASH2.PX_DFO_PAIR_TYPE, ASH2.PX_DFO_PAIR_ID, ASH2.PX_STEP_ARG, CASE WHEN ASH2.PX_DFO_PAIR_ID IS NOT NULL THEN DECODE(ASH2.PX_DOP, 0, :B28 , ASH2.PX_DOP) ELSE NULL END PX_DOP, ASH2.PX_MIN_DOP, :B20 + NUMTODSINTERVAL(:B26 * (ASH2.BUCKET_NUM-1), 'SECOND') BUCKET_ACTIVITY_START, :B20 + NUMTODSINTERVAL( :B26 * ASH2.BUCKET_NUM - 1, 'SECOND') BUCKET_ACTIVITY_END, ASH2.ACTIVITY_START, ASH2.ACTIVIT Y_END, ASH2.ACTIVITY_COUNT, ASH2.IMQ_COUNT, ASH2.WAIT_COUNT, ASH2.CPU_COUNT, ASH2.OTHER_SQL_COUNT, SUM(ASH2.ACTIVITY_COUNT) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE, DECODE(ASH2.PX_DFO_PAIR_ID, NULL, NULL, ASH2.SESSION_ID)) PER_SERVER_DFO_COUNT, CEIL((MAX(ASH2.MAX_SAMPLE_DATE) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE) - MIN(ASH2.MIN_SAMPLE_DATE) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE)) * 3600 * 24) DFO_DURATION FROM (SELECT ASH1.BUCKET_NUM, ASH1.GLOBAL_SAMPLE_ID, ASH1.PLAN_LINE_ID, ASH1.PLSQL_OBJECT_ID, ASH1.PLSQL_SUBPROGRAM_ID, ASH1.ACTIVITY_TYPE, ASH1.OTHER_SQL_ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.SESSION_ID, ASH1.PX_STEP_ID, ASH1.PX_STEP_ARG, MAX(ASH1.SQL) SQL, MAX(ASH1.IS_MONITORED_SQL) IS_MONITORED_SQL, MAX(ASH1.PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, MAX(ASH1.PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, MIN(SAMPLE_DATE) MIN_SAMPLE_DATE, MAX(SAMPLE_DATE) MAX_SAMPLE_DATE, MAX(ASH1.IS_PX_SLAVE) IS_PX_SLAVE, MAX(ASH1.PX_DOP) PX_DOP, MIN(ASH1.PX_DOP) PX_MIN_DOP, MIN(ASH1.SAMPLE_DATE) ACTIVITY_START, MAX(ASH1.SAMPLE_DATE) ACTIVITY_END, COUNT(ASH1.SQL) ACTIVITY_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Cpu' AND ASH1.EVENT_NAME = 'in memory' THEN 1 ELSE NULL END) IMQ_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE != 'Other SQL Execution' AND ASH1.ACTIVITY_TYPE != 'Non SQL' AND ASH1.ACTIVITY_TYPE != 'Cpu' THEN 1 ELSE NULL END) WAIT_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Cpu' AND ASH1.EVENT_NAME IS NULL THEN 1 ELSE NULL END) CPU_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Other SQL Execution' AND ASH1.ACTIVITY_TYPE != 'Non SQL' THEN 1 ELSE NULL END) OTHER_SQL_COUNT, MAX(ASH1.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, MAX(ASH1.DBOP_NAME) DBOP_NAME FROM ( SELECT (CASE WHEN :B27 > 1 THEN (TRUNC(DELTA_TIME_SECONDS/ :B26 ) + 1) ELSE 1 END) BUCKET_NUM, ASH00.SQL, ASH00.SAMPLE_DATE, NVL2(DUP.C2, TRUNC(DELTA_TIME_SECONDS/ (:B25 )) + 1, NULL) GLOBAL_SAMPLE_ID, NVL2(DUP.C2, NULL, ASH00 .IS_MONITORED_SQL) IS_MONITORED_SQL, NVL2(DUP.C2, NULL, ASH00.IN_INMEMORY_QUERY) IN_INMEMORY_QUERY, NVL2(DUP.C2, NULL, ASH00.WAIT_CLASS) WAIT_CLASS, NVL2(DUP.C2, NULL, ASH00.ACTIVITY_TYPE) ACTIVITY_TYPE, NVL2(DUP.C2, NULL, ASH00.OTHER_SQL_ACTIVITY_TYPE) OTHER_SQL_ACTIVITY_TYPE, NVL2(DUP.C2, NULL, ASH00.EVENT_NAME) EVENT_NAME, NVL2(DUP.C2, NULL, ASH00.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, NVL2(DUP.C2, NULL, ASH00.DBOP_NAME) DBOP_NAME, NVL2(DUP.C2, NULL, ASH00.IS_PX_SLAVE) IS_PX_SLAVE, NVL2(DUP.C2, NULL, ASH00.SESSION_ID) SESSION_ID, NVL2(DUP.C2, NULL, ASH00.PLSQL_OBJECT_ID) PLSQL_OBJECT_ID, NVL2(DUP.C2, NULL, ASH00.PLSQL_SUBPROGRAM_ID) PLSQL_SUBPROGRAM_ID, NVL2(DUP.C2, NULL, ASH00.PLAN_LINE_ID) PLAN_LINE_ID, NVL2(DUP.C2, NULL, ASH00.PX_STEP_ID) PX_STEP_ID, NVL2(DUP.C2, NULL, ASH00.PX_STEP_ARG) PX_STEP_ARG, NVL2(DUP.C2, NULL, ASH00.PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, NVL2(DUP.C2, NULL, ASH00.PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, NVL2(DUP.C2, NULL , ASH00.PX_DOP) PX_DOP FROM (SELECT 1 C1, NULL C2 FROM V$DUAL UNION ALL SELECT 1 C1, 1 C2 FROM V$DUAL) DUP, (SELECT /*+ no_merge */ 1 C1, ASH000.SAMPLE_DATE, ASH000.IS_MONITORED_SQL, ((EXTRACT(SECOND FROM(DELTA_TIME)) + EXTRACT(MINUTE FROM(DELTA_TIME)) * 60 + EXTRACT(HOUR FROM(DELTA_TIME)) * 3600 + EXTRACT(DAY FROM(DELTA_TIME)) * 86400)) DELTA_TIME_SECONDS, ASH000.IN_INMEMORY_QUERY, ASH000.WAIT_CLASS, DECODE(ASH000.IS_MONITORED_SQL, 1, NVL(ASH000.WAIT_CLASS, 'Cpu'), DECODE(SQL_ID, NULL, 'Non SQL', 'Other SQL Execution')) ACTIVITY_TYPE, NVL(ASH000.WAIT_CLASS, 'Cpu') OTHER_SQL_ACTIVITY_TYPE, DECODE(:B24 , 1, CASE WHEN ASH000.IS_MONITORED_SQL = 1 THEN NVL(ASH000.EVENT, DECODE(ASH000.IN_INMEMORY_QUERY, 'Y', 'in memory', ASH000.EVENT)) WHEN ASH000.SQL_ID IS NOT NULL THEN 'sql_id: ' || ASH000.SQL_ID WHEN ASH000.CALL_NAME IS NOT NULL THEN 'call: ' || ASH000.CALL_NAME ELSE 'anonymous: '|| ASH000.EVENT END, NULL) EVENT_NAME, CASE WHEN ASH000.IS_MONITORED_SQL = 1 AND (NVL(ASH000.SQL_ID, :B9 ) = :B9 OR NVL(ASH000.DBOP_NAME, :B6 ) = :B6 ) THEN 'this' WHEN ASH000.IS_PX_SLAVE = 1 AND ASH000.SQL_ID IS NOT NULL AND ASH000.TOP_LEVEL_SQL_ID != ASH000.SQL_ID THEN ASH000.TOP_LEVEL_SQL_ID WHEN ASH000.SQL_ID IS NOT NULL THEN ASH000.SQL_ID ELSE NVL(CALL_NAME, 'anonymous') END SQL, CASE WHEN ASH000.IS_PX_SLAVE = 0 AND (ASH000.SQL_ID IS NULL OR ASH000.TOP_LEVEL_SQL_ID != ASH000.SQL_ID) THEN ASH000.TOP_LEVEL_SQL_ID END TOP_LEVEL_SQL_ID, ASH000.DBOP_NAME, ASH000.IS_PX_SLAVE, CASE WHEN ASH000.IS_PX_SLAVE = 1 AND ASH000.IS_MONITORED_SQL != 1 THEN 65536 ELSE ASH000.SESSION_ID END SESSION_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PLSQL_OBJECT_ID, NULL) PLSQL_OBJECT_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PLSQL_SUBPROGRAM_ID, NULL) PLSQL_SUBPROGRAM_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.SQL_PLAN_LINE_ID, NULL) PLAN_LINE_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_STEP_ID, NULL) PX_STEP_ID, CASE WHEN ASH000.IS_PX_SLAVE = 1 AN D ASH000.IS_MONITORED_SQL = 1 AND ASH000.PX_STEP_ID IN (1, 2, 3) THEN ASH000.PX_STEP_ARG ELSE NULL END PX_DFO_PAIR_ID, CASE WHEN ASH000.IS_PX_SLAVE = 0 OR ASH000.IS_MONITORED_SQL != 1 THEN NULL WHEN ASH000.PX_STEP_ID = 1 THEN 1 WHEN ASH000.PX_STEP_ID IN (2, 3) THEN 0 ELSE NULL END PX_DFO_PAIR_TYPE, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_STEP_ARG, NULL) PX_STEP_ARG, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_DOP, NULL) PX_DOP FROM (SELECT ASH0.*, CASE WHEN ASH0.IS_TARGET_SQL = 1 OR (ASH0.IS_PX_SLAVE = 1 AND ((NVL(ASH0.TOP_LEVEL_SQL_ID, ASH0.SQL_ID) IS NOT NULL AND NVL(ASH0.TOP_LEVEL_SQL_ID, ASH0.SQL_ID) = :B9 ) OR (SQL_ID IS NULL AND :B23 = 'Y'))) THEN 1 ELSE 0 END IS_MONITORED_SQL FROM (SELECT (CASE WHEN (ASH.SQL_ID = :B9 AND ASH.SQL_EXEC_ID = :B7 AND ASH.SQL_EXEC_START = :B8 ) THEN 1 ELSE 0 END) IS_TARGET_SQL, ASH.SQL_ID, ASH.SQL_PLAN_LINE_ID, ASH.PLSQL_OBJECT_ID, ASH.PLSQL_SUBPROGRAM_ID, ASH.TOP_LEVEL_SQL_ID, DECODE(ASH.SQL_ID, NULL, ASH.TOP_LEVEL_CALL_ NAME, NULL) CALL_NAME, ASH.EVENT, ASH.IN_INMEMORY_QUERY, ASH.WAIT_CLASS, ASH.SQL_EXEC_ID, ASH.SQL_EXEC_START, ASH.DBOP_NAME, ASH.DBOP_EXEC_ID, ASH.SESSION_ID, ASH.SESSION_SERIAL#, CASE WHEN QC_INSTANCE_ID IS NOT NULL AND (ASH.SESSION_ID != ASH.QC_SESSION_ID OR ASH.SESSION_SERIAL# != ASH.QC_SESSION_SERIAL# OR USERENV('instance') != ASH.QC_INSTANCE_ID) THEN 1 ELSE 0 END IS_PX_SLAVE, SAMPLE_TIME - CAST(:B20 AS TIMESTAMP) DELTA_TIME, CAST(FROM_TZ(ASH.SAMPLE_TIME, DBTIMEZONE) AS DATE) SAMPLE_DATE, TRUNC(MOD(PX_FLAGS/65536, 32)) PX_STEP_ID, MOD(PX_FLAGS, 65536) PX_STEP_ARG, TRUNC(PX_FLAGS/2097152) PX_DOP FROM V$ALL_ACTIVE_SESSION_HISTORY ASH WHERE ((ASH.SESSION_ID = :B19 AND ASH.SESSION_SERIAL# = :B18 AND USERENV('INSTANCE') = :B17 ) OR (ASH.QC_SESSION_ID IS NOT NULL AND ASH.QC_SESSION_ID = :B19 AND ASH.QC_SESSION_SERIAL# = :B18 AND ASH.QC_INSTANCE_ID = :B17 )) AND SAMPLE_TIME BETWEEN :B16 AND :B15 ) ASH0 WHERE (ASH0.SAMPLE_DATE BETWEEN :B20 + 1/24/3600 AND :B22 - 1/24/360 0 OR (ASH0.SQL_ID = :B9 AND ASH0.SQL_EXEC_START = :B8 AND ASH0.SQL_EXEC_ID = :B7 ) OR (ASH0.DBOP_NAME = :B6 AND ASH0.DBOP_EXEC_ID = :B5 )) AND (:B21 IS NULL OR ASH0.SQL_PLAN_LINE_ID = :B21 ) AND (ASH0.IS_PX_SLAVE = 0 OR ASH0.SQL_ID IS NOT NULL)) ASH000 ) ASH00 WHERE ASH00.C1 = DUP.C1) ASH1 WHERE ASH1.BUCKET_NUM > 0 AND ASH1.BUCKET_NUM <= :B27 GROUP BY USERENV('INSTANCE'), ASH1.GLOBAL_SAMPLE_ID, ASH1.BUCKET_NUM, ASH1.SESSION_ID, ASH1.PLAN_LINE_ID, ASH1.PLSQL_OBJECT_ID, ASH1.PLSQL_SUBPROGRAM_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.OTHER_SQL_ACTIVITY_TYPE, ASH1.PX_STEP_ID, ASH1.PX_STEP_ARG) ASH2)))) GVTF WHERE GVTF.INST_ID BETWEEN :B11 AND :B10 ) ASH WHERE ASH.GLOBAL_SAMPLE_ID IS NULL AND ASH.SESSION_ID = MO.SID(+) AND ASH.INST_ID = MO.INST_ID(+)) AD0), RESPONSE_TIME_DATA AS (SELECT ADH.BUCKET_NUM, ADH.SQL_ROWNUM, ADH.SQL, ADH.TOP_LEVEL_SQL_ID, ADH.DBOP_NAME, ADH.PX_ID, DECODE( ADH.PX_STEP_ID, NULL, NULL, 0, NULL, 1, 'PX Server(s) - Executing Parent DFO ', 2, 'PX Server(s) - Executing Child DFO', 3, 'PX Server(s) - Sampling Child DFO', 4, 'PX Server(s) - Joining Group', 5, 'QC - Scheduling Child DFO', 6, 'QC - Scheduling Parent DFO', 7, 'QC - Initializing Objects', 8, 'QC - Flushing Objects', 9, 'QC - Allocating Slaves', 10, 'QC - Initializing Granules', 11, 'PX Server(s) - Parsing Cursor', 12, 'PX Server(s) - Executing Cursor', 13, 'PX Server(s) - Preparing Transaction', 14, 'PX Server(s) - Joining Transaction', 15, 'PX Server(s) - Load Commit', 16, 'PX Server(s) - Aborting Transaction', 17, 'QC - Executing Child DFO', 18, 'QC - Executing Parent DFO', 'PX Step - ' || PX_STEP_ID) PX_STEP_ID, ADH.PX_STEP_ARG, ADH.PX_DFO_PAIR_ID, ADH.PX_DOP, ADH.PX_MIN_DOP, ADH.DFO_MOST_ACTIVE_IID, ADH.DFO_MOST_ACTIVE_SID, ADH.DFO_MOST_ACTIVE_COUNT, ADH.ACTIVITY_START, ADH.ACTIVITY_END, ADH.ACTIVITY_TYPE, ADH.OTHER_SQL_ACTIVITY_TYPE, ADH.EVENT_NAME, ADH.PLAN_LINE_ID, ADH.PLSQL_OBJECT_ID, ADH.PLSQL_SUBPRO GRAM_ID, CASE WHEN PLSQL_ROWNUM = 1 AND ADH.PLSQL_OBJECT_ID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM ALL_PROCEDURES P WHERE P.OBJECT_ID = ADH.PLSQL_OBJECT_ID AND P.SUBPROGRAM_ID = ADH.PLSQL_SUBPROGRAM_ID), 'Unavailable') ELSE NULL END PLSQL_NAME, ADH.ACTIVITY_COUNT, ADH.BUCKET_ACTIVE_SECONDS, ADH.BUCKET_IDLE_SECONDS, (CASE WHEN ADH.IS_MONITORED_SQL = 0 THEN ADH.ACTIVE_SECONDS WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_CPU_HEIGHT >= :B34 THEN DECODE(ADH.ACTIVITY_TYPE, 'Cpu', (ADH.DFO_PAIR_ACTIVITY_HEIGHT / ADH.DFO_PAIR_CPU_HEIGHT) * ADH.DFO_PAIR_TOTAL_SECONDS, 0) WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.PX_DOP > :B34 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * DECODE(ADH.ACTIVITY_TYPE, 'Cpu', ADH.DFO_PAIR_ACTIVITY_HEIGHT, (ADH.DFO_PAIR_ACTIVITY_HEIGHT * (:B34 - ADH.DFO_PAIR_CPU_HEIGHT)) / (ADH.DFO_PAIR_TOTAL_HEIGHT_ADJ - ADH.DFO_PAIR_CPU_HEIGHT))) / :B34 WHEN ADH.PX_DFO_PAIR_ ID IS NOT NULL THEN (ADH.DFO_PAIR_TOTAL_SECONDS * ADH.DFO_PAIR_ACTIVITY_HEIGHT) / ADH.PX_DOP ELSE ADH.ACTIVE_SECONDS END) RESP_TIME, (CASE WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_IDLE_HEIGHT > 0 AND ADH.PX_DOP > :B34 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * (ADH.DFO_PAIR_IDLE_HEIGHT * (:B34 - ADH.DFO_PAIR_CPU_HEIGHT)) / (ADH.DFO_PAIR_TOTAL_HEIGHT_ADJ - ADH.DFO_PAIR_CPU_HEIGHT)) / :B34 WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_IDLE_HEIGHT > 0 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * ADH.DFO_PAIR_IDLE_HEIGHT) / ADH.PX_DOP ELSE 0 END) DFO_PAIR_IDLE_RESP_TIME, ADH.DFO_PAIR_ACTIVITY_HEIGHT, ADH.DFO_PAIR_CPU_HEIGHT, ADH.DFO_PAIR_IDLE_HEIGHT, ADH.DFO_PAIR_TOTAL_HEIGHT, ADH.DFO_PAIR_CPU_ACTIVITY, ADH.DFO_PAIR_TOTAL_SECONDS FROM (SELECT ADH_1.*, (ROW_NUMBER() OVER(PARTITION BY ADH_1.PLSQL_OBJECT_ID, ADH_1.PLSQL_SUBPROGRAM_ID ORDER BY ADH_1.BUCKET_NUM, ADH_1.ACTIVITY_TYPE, ADH_1.EVENT_NAME)) PLSQL_ROWNUM, (ROW_NUMBER() OVER(PARTITION BY ADH_1.SQL ORDER BY ADH_1.B UCKET_NUM, ADH_1.ACTIVITY_TYPE, ADH_1.EVENT_NAME)) SQL_ROWNUM, (CASE WHEN ADH_1.PX_DFO_PAIR_ID IS NOT NULL AND (ADH_1.DFO_PAIR_TOTAL_HEIGHT < ADH_1.PX_DOP) AND (ADH_1.DFO_PAIR_CPU_HEIGHT < :B34 ) THEN ADH_1.PX_DOP - ADH_1.DFO_PAIR_TOTAL_HEIGHT ELSE 0 END) DFO_PAIR_IDLE_HEIGHT, (CASE WHEN ADH_1.PX_DFO_PAIR_ID IS NOT NULL AND (ADH_1.DFO_PAIR_TOTAL_HEIGHT < ADH_1.PX_DOP) AND (ADH_1.DFO_PAIR_CPU_HEIGHT < :B34 ) THEN ADH_1.PX_DOP ELSE ADH_1.DFO_PAIR_TOTAL_HEIGHT END) DFO_PAIR_TOTAL_HEIGHT_ADJ FROM (SELECT ADH_0.*, (CASE WHEN ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.DFO_PAIR_TOTAL_ACTIVITY * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELSE 0 END) DFO_PAIR_TOTAL_HEIGHT, (CASE WHEN ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.DFO_PAIR_CPU_ACTIVITY * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELSE 0 END) DFO_PAIR_CPU_HEIGHT, (CASE WHEN ADH_0.PX_DFO_PAIR_ID IS NOT NULL AND ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.ACTIVITY_COUNT * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELS E 0 END) DFO_PAIR_ACTIVITY_HEIGHT FROM (SELECT AD3.*, (SUM(DECODE(AD3.PX_DFO_PAIR_ID, NULL, 0, AD3.ACTIVE_SECONDS)) OVER(PARTITION BY DECODE(AD3.PX_DFO_PAIR_ID, NULL, NULL, AD3.BUCKET_NUM), DECODE(AD3.PX_DFO_PAIR_ID, NULL, NULL, AD3.PX_STEP_ARG))) DFO_PAIR_TOTAL_SECONDS FROM (SELECT AD2.*, CASE WHEN AD2.IS_MONITORED_SQL = 0 THEN SQL_BUCKET_DISTINCT_SAMPLES * :B25 WHEN AD2.PX_ID IS NULL THEN AD2.ACTIVITY_COUNT * :B25 WHEN AD2.BUCKET_PARALLEL_MON_ACTIVITY > 0 THEN (AD2.ACTIVITY_COUNT * AD2.BUCKET_PARALLEL_MON_SECONDS) / AD2.BUCKET_PARALLEL_MON_ACTIVITY ELSE 0 END ACTIVE_SECONDS, CASE WHEN AD2.BUCKET_INTERVAL > BUCKET_ACTIVE_SECONDS THEN AD2.BUCKET_INTERVAL - BUCKET_ACTIVE_SECONDS ELSE 0 END BUCKET_IDLE_SECONDS FROM (SELECT AD1.*, (AD1.BUCKET_SERIAL_MON_ACTIVITY * :B25 ) BUCKET_SERIAL_MON_SECONDS, (AD1.BUCKET_TOTAL_MON_ACTIVITY - AD1.BUCKET_SERIAL_MON_ACTIVITY) BUCKET_PARALLEL_MON_ACTIVITY, (AD1.BUCKET_ACTIVE_SECONDS - (AD1.BUCKET_OTHER_ACTIVITY + AD1.BUCKET_SERIAL_M ON_ACTIVITY) * :B25 ) BUCKET_PARALLEL_MON_SECONDS, (AD1.BUCKET_OTHER_ACTIVITY * :B25 ) BUCKET_OTHER_SECONDS, DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, SUM(AD1.ACTIVITY_COUNT) OVER(PARTITION BY DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, AD1.BUCKET_NUM), AD1.PX_DFO_PAIR_ID)) DFO_PAIR_TOTAL_ACTIVITY, DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, SUM(DECODE(AD1.ACTIVITY_TYPE, 'Cpu', AD1.ACTIVITY_COUNT, 0)) OVER(PARTITION BY DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, AD1.BUCKET_NUM), AD1.PX_DFO_PAIR_ID)) DFO_PAIR_CPU_ACTIVITY FROM (SELECT AD01.*, NVL((SUM(DECODE(AD01.IS_MONITORED_SQL, 1, AD01.ACTIVITY_COUNT, NULL)) OVER(PARTITION BY AD01.BUCKET_NUM)), 0) BUCKET_TOTAL_MON_ACTIVITY, (NVL(SUM(CASE WHEN AD01.IS_MONITORED_SQL = 1 AND AD01.PX_ID IS NULL THEN AD01.ACTIVITY_COUNT ELSE NULL END) OVER(PARTITION BY AD01.BUCKET_NUM), 0)) BUCKET_SERIAL_MON_ACTIVITY, (NVL((SUM(DECODE(AD01.IS_MONITORED_SQL, 0, AD01.SQL_BUCKET_DISTINCT_SAMPLES, NULL)) OVER(PARTITION BY AD01.BUCKET_NUM)), 0)) BUCKET_OTHER_ACTIVITY, (NVL(AD01.BUCKET_DISTINCT_SAMPLES, 0) * :B25 ) BUCKET_ACTIVE_SECONDS, DECODE(AD01.BUCKET_NUM, :B27 , MOD(:B32 , :B26 ), :B26 ) BUCKET_INTERVAL FROM (SELECT AD0.ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.PX_ID, AD0.ACTIVITY_TYPE, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.PLAN_LINE_ID, AD0.PX_STEP_ID, AD0.PX_STEP_ARG, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, MAX(AD0.IS_MONITORED_SQL) IS_MONITORED_SQL, MAX(AD0.SQL) SQL, MAX(AD0.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, MAX(AD0.DBOP_NAME) DBOP_NAME, MAX(DECODE(AD0.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD0.DFO_MOST_ACTIVE_IID)) DFO_MOST_ACTIVE_IID, MAX(DECODE(AD0.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD0.DFO_MOST_ACTIVE_SID)) DFO_MOST_ACTIVE_SID, SUM(AD0.DFO_MOST_ACTIVE_COUNT) DFO_MOST_ACTIVE_COUNT, MAX(PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, MAX(PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, MA X(AD0.PX_DOP) PX_DOP, MIN(AD0.PX_MIN_DOP) PX_MIN_DOP, MAX(AD0.BUCKET_DISTINCT_SAMPLES) BUCKET_DISTINCT_SAMPLES, MAX(AD0.SQL_BUCKET_DISTINCT_SAMPLES) SQL_BUCKET_DISTINCT_SAMPLES FROM (SELECT AD00.*, (CASE WHEN AD00.IS_MONITORED_SQL = 1 AND (AD00.SESSION_ID != :B19 OR AD00.INST_ID != :B17 ) THEN AD00.PX_SERVER_GROUP END) PX_ID FROM ASH_DATA AD00 WHERE :B33 = 1 ) AD0 GROUP BY AD0.ACTIVITY_BUCKET_NUM, AD0.PX_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.PLAN_LINE_ID, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, AD0.PX_STEP_ID, AD0.PX_STEP_ARG ) AD01) AD1) AD2) AD3) ADH_0) ADH_1) ADH) SELECT /*+ no_monitor no_xml_query_rewrite opt_param('_gby_hash_aggregation_enabled', 'false') */ XMLELEMENT( "sql_monitor_report", XMLATTRIBUTES(:B59 AS "version", TO_CHAR(:B58 , :B12 ) AS "sysdate"), XMLELEMENT( "report_parameters", NULL, CASE WHEN :B57 IS NOT NULL THEN XMLFOREST( :B57 AS "dbop_name ", :B56 AS "dbop_exec_id") ELSE XMLFOREST( :B55 AS "sql_id", :B54 AS "sql_exec_id") END, XMLFOREST( :B53 AS "session_id", :B52 AS "session_serial", TO_CHAR(:B51 , :B12 ) AS "sql_exec_start", :B27 AS "bucket_count", TO_CHAR(:B50 , :B12 ) AS "interval_start", TO_CHAR(:B22 , :B12 ) AS "interval_end", DECODE(:B49 , 'Y', :B48 , NULL) AS "auto_refresh", :B47 AS "base_path"), CASE WHEN :B23 = 'Y' AND :B46 IS NOT NULL AND NOT (:B4 = 1 AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL) THEN XMLELEMENT( "parallel_filter", NULL, XMLFOREST( DECODE(:B4 , 1, 'yes', 'no') AS "qc", :B3 AS "server_group", :B2 AS "server_set", :B1 AS "server_number")) ELSE NULL END), CASE WHEN :B14 = 1 THEN XMLELEMENT( "derived_parameters_testing", NULL, XMLFOREST(:B11 AS "instance_id_low", :B10 AS "instanc e_id_high", :B26 AS "bucket_interval_sec", :B32 AS "interval_second", :B27 AS "bucket_calc_count", :B45 AS "bucket_calc_max_count", :B13 AS "sel_sqltext")) ELSE NULL END, (SELECT XMLCONCAT( CASE WHEN :B46 IS NULL AND :B67 IS NULL THEN XMLELEMENT( "target", XMLATTRIBUTES(:B17 AS "instance_id", :B19 AS "session_id", :B18 AS "session_serial", NVL2(:B6 , NULL, :B9 ) AS "sql_id", NVL2(:B6 , NULL, TO_CHAR(:B8 , :B12 )) AS "sql_exec_start", NVL2(:B6 , NULL, :B7 ) AS "sql_exec_id", NVL2(:B6 , :B6 , NULL) AS "dbop_name", NVL2(:B6 , :B5 , NULL) AS "dbop_exec_id", NVL2(:B6 , TO_CHAR(:B8 , :B12 ), NULL) AS "dbop_exec_start", NVL2(:B6 , NULL, MAX_PLAN_HASH_VALUE) AS "sql_plan_hash", NVL2(:B6 , NULL, MAX_FULL_PLAN_HASH_VALUE) AS "sql_full_plan_hash", MAGG.DB_UNIQUE_NAME AS "db_unique_nam e", MAGG.PLATFORM_NAME AS "db_platform_name", MAGG.HOST_NAME AS "report_host_name"), NVL2(SUM_SERVERS_REQUESTED, XMLELEMENT( "servers_requested", NULL, SUM_SERVERS_REQUESTED), NULL), NVL2(SUM_SERVERS_ALLOCATED, XMLELEMENT( "servers_allocated", NULL, SUM_SERVERS_ALLOCATED), NULL), NVL2(MAX_USERID, XMLELEMENT( "user_id", NULL, MAX_USERID), NULL), NVL2(MAX_USERNAME, XMLELEMENT( "user", NULL, MAX_USERNAME), NULL), NVL2(MAX_CON_ID, XMLELEMENT( "con_id", NULL, MAX_CON_ID), NULL), NVL2(MAX_CON_NAME, XMLELEMENT( "con_name", NULL, MAX_CON_NAME), NULL), NVL2(MAX_PROGRAM, XMLELEMENT( "program", NULL, MAX_PROGRAM), NULL), NVL2(MAX_MODULE, XMLELEMENT( "module", NULL, MAX_MODULE), NULL), NVL2(MAX_ACTION, XMLELEMENT( "action", NULL, MAX_ACTION), NULL), NVL2(MAX_SERVICE_NAME, XMLELEMENT( "service", NULL, MAX_SERVICE_NAME), NULL ), NVL2(MAX_CLIENT_ID, XMLELEMENT( "client_id", NULL, MAX_CLIENT_ID), NULL), NVL2(MAX_CLIENT_INFO, XMLELEMENT( "client_info", NULL, MAX_CLIENT_INFO), NULL), NVL2(MAX_PL_ENTRY_OID, XMLELEMENT( "plsql_entry_object_id", NULL, MAX_PL_ENTRY_OID), NULL), NVL2(MAX_PL_ENTRY_PROGID, XMLELEMENT( "plsql_entry_subprogram_id", NULL, MAX_PL_ENTRY_PROGID), NULL), NVL2(MAX_PL_ENTRY_NAME, XMLELEMENT( "plsql_entry_name", NULL, MAX_PL_ENTRY_NAME), NULL), NVL2(MAX_PL_OID, XMLELEMENT( "plsql_object_id", NULL, MAX_PL_OID), NULL), NVL2(MAX_PL_PROGID, XMLELEMENT( "plsql_subprogram_id", NULL, MAX_PL_PROGID), NULL), NVL2(MAX_PL_NAME, XMLELEMENT( "plsql_name", NULL, MAX_PL_NAME), NULL), CASE WHEN (:B13 = 0 OR :B6 IS NOT NULL) THEN NULL ELSE XMLELEMENT( "sql_fulltext", XMLATTRIBUTES( NVL2(:B65 , 'Y', :B66 ) AS "is_full"), NVL2(:B65 , :B65 , SQLMON_TEXT)) END, XMLELEME NT( "status", NULL, MAX_STATUS), XMLELEMENT( "refresh_count", NULL, SUM_REFRESH_COUNT), XMLELEMENT( "first_refresh_time", NULL, TO_CHAR(MIN_FIRST_REFRESH_TIME, :B12 )), XMLELEMENT( "last_refresh_time", NULL, TO_CHAR(:B58 , :B12 )), XMLELEMENT( "duration", NULL, GREATEST(:B64 , LEAST(MAX_ELAPSED_TIME/1000000, 1), CEIL(MAX_QUEUING_TIME/1000000))), DECODE(MAX_RM_INFO, '00/00/0000 00:00:00', NULL, XMLTYPE(SUBSTR(MAX_RM_INFO, 20))), CASE WHEN (:B62 = 'Y') THEN XMLELEMENT( "adaptive_plan", XMLATTRIBUTES(:B63 AS "is_final"), :B62 ) ELSE NULL END ) END, XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), DECODE(NVL(SUM_ELAPSED_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), SUM_ELAPSED_TIME)), DECODE(NVL(MAX_QUEUING_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name "), MAX_QUEUING_TIME)), DECODE(NVL(SUM_CPU_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), SUM_CPU_TIME)), DECODE(NVL(SUM_USER_IO_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), SUM_USER_IO_WAIT_TIME)), DECODE(NVL(SUM_APPLICATION_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), SUM_APPLICATION_WAIT_TIME)), DECODE(NVL(SUM_CONCURRENCY_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), SUM_CONCURRENCY_WAIT_TIME)), DECODE(NVL(SUM_CLUSTER_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), SUM_CLUSTER_WAIT_TIME)), DECODE(NVL(SUM_PLSQL_EXEC_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), SUM_PLSQL_EX EC_TIME)), DECODE(NVL(SUM_JAVA_EXEC_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), SUM_JAVA_EXEC_TIME)), DECODE(NVL(SUM_OTHER_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), SUM_OTHER_WAIT_TIME)), DECODE(NVL(SUM_FETCHES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), SUM_FETCHES)), DECODE(NVL(SUM_BUFFER_GETS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), SUM_BUFFER_GETS)), DECODE(NVL(SUM_READ_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), SUM_READ_REQS)), DECODE(NVL(SUM_WRITE_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), SUM_WRITE_REQS)), DECODE(NVL(SUM_READ_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read _reqs' AS "name"), SUM_READ_REQS)), DECODE(NVL(SUM_READ_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), SUM_READ_BYTES)), DECODE(NVL(SUM_WRITE_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), SUM_WRITE_REQS)), DECODE(NVL(SUM_WRITE_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), SUM_WRITE_BYTES)), DECODE(NVL(SUM_IO_UNC_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), SUM_IO_UNC_BYTES)), DECODE(NVL(SUM_IO_ELIG_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), SUM_IO_ELIG_BYTES)), DECODE(NVL(SUM_IO_RET_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), SUM_IO_RET_BYTES)), CASE WHEN SUM_IO_INTER_BYTES IS NULL OR SUM_IO_BYTES = 0 OR SUM_IO_INTER_ BYTES = SUM_IO_BYTES OR NVL(SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(SUM_IO_BYTES / DECODE(SUM_IO_INTER_BYTES, 0, 1, SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(SUM_IO_ELIG_BYTES, 0) = 0 OR SUM_IO_BYTES = 0 OR SUM_IO_UNC_BYTES = SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (GREATEST(SUM_IO_UNC_BYTES, SUM_IO_ELIG_BYTES) - SUM_IO_RET_BYTES) / SUM_IO_ELIG_BYTES, 2))) END), CASE WHEN :B33 = 1 THEN (SELECT CASE WHEN SUM(ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_sampled", XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME)) ELSE NULL END FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, SUM(AD0.ACTIVITY_COUNT) AC TIVITY_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) ELSE NULL END, NVL2(MAX_ERROR_NUMBER, XMLELEMENT( "error", XMLATTRIBUTES(MAX_ERROR_NUMBER AS "number", MAX_ERROR_FACILITY AS "facility"), MAX_ERROR_MESSAGE), NULL), CASE WHEN :B61 = 1 AND MAGG.QC_HAS_BINDS_XML = 'Y' THEN (SELECT XMLTYPE(BINDS_XML) FROM GV$ALL_SQL_MONITOR MON WHERE MON.INST_ID = :B17 AND MON.KEY = MAGG.MAX_KEY AND MON.SID = MAGG.MAX_SESSION_ID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END, CASE WHEN :B60 = 1 AND MAGG.QC_HAS_OTHER_XML = 'Y' THEN (SELECT XMLTYPE(OTHER_XML) FROM GV$ALL_SQL_MONITOR MON WHERE MON.INST_ID = MAGG.MAX_INST_ID AND MON.KEY = MAGG.MAX_KEY AND MON.SID = MAGG.MAX_SESSION_ID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END) FROM MONITOR_AGG MAGG), CASE WHEN :B44 = 1 THEN (SELECT CASE WHEN AT.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( :B41 AS "ash_missing_seconds", TO_CHAR(AT.ACTIVITY_START, :B12 ) AS "first_sample_time", TO_CHAR(AT.ACTIVITY_END, :B12 ) AS "last_sample_time", ROUND((AT.ACTIVITY_END - AT.ACTIVITY_START) * 3600 * 24) + 1 AS "duration", AT.ACTIVITY_COUNT AS "count", AT.IMQ_COUNT AS "imq_count", AT.WAIT_COUNT AS "wait_count", AT.CPU_COUNT AS "cpu_count", DECODE(AT.OTHER_SQL_COUNT, 0, NULL, AT.OTHER_SQL_COUNT) AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), AT.ACTIVITY_TOTAL, AH.GLOB_ACTIVITY_HISTO) WHEN :B41 IS NOT NULL THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( DECODE( :B41 , -1, 'all', TO_CHAR( :B41 )) AS "ash_missing_seconds")) ELSE NULL END FROM (SELECT MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COU NT, SUM(AD1.IMQ_COUNT) IMQ_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUM(AD1.CPU_COUNT) CPU_COUNT, SUM(AD1.OTHER_SQL_COUNT) OTHER_SQL_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) AT, (SELECT CASE WHEN :B30 = 1 AND :B27 > 1 THEN XMLELEMENT( "activity_histogram", XMLATTRIBUTES( :B26 AS "bucket_interval", :B27 AS "bucket_count", TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR( :B22 , :B12 ) AS "end_time", ROUND(( :B22 - :B20 ) *3600*24) + 1 AS "duration"), XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ELSE NULL END GLOB_ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(IMQ_COUNT) IMQ_COUNT, SUM(WAIT_COUNT) WAIT_COUNT, SUM(CPU_COUNT) CPU_COUNT, SUM(OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTI VITY_BUCKET FROM (SELECT AD0.ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH) ELSE NULL END, CASE WHEN :B33 = 1 THEN (SELECT CASE WHEN AH.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_detail", XMLATTRIBUTES( TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR( :B22 , :B12 ) AS "end_time", :B41 AS "ash_missing_seconds", TO_CHAR(AH.ACTIVITY_START, :B12 ) AS "first_sample_time", TO_CHAR(AH.ACTIVITY_END, :B12 ) AS "last_sample_ time", ROUND((AH.ACTIVITY_END - AH.ACTIVITY_START) * 3600 * 24) + 1 AS "duration", :B25 AS "sample_interval", :B26 AS "bucket_interval", :B27 AS "bucket_count", ROUND((:B22 - :B20 ) *3600*24) + 1 AS "bucket_duration", :B40 AS "cpu_cores", :B34 AS "total_cpu_cores", :B39 AS "hyperthread"), AH.GLOB_ACTIVITY_HISTO) WHEN :B41 IS NOT NULL THEN XMLELEMENT( "activity_detail", XMLATTRIBUTES( DECODE( :B41 , -1, 'all', TO_CHAR( :B41 )) AS "ash_missing_seconds")) ELSE NULL END FROM (SELECT MIN(AD2.ACTIVITY_START) ACTIVITY_START, MAX(AD2.ACTIVITY_END) ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), ACTIVITY_BUCKET_XML) ORDER BY AD2.BUCKET_NUM) GLOB_ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUM(AD1.A CTIVITY_COUNT) ACTIVITY_COUNT, MAX(AD1.BUCKET_IDLE_SECONDS) BUCKET_IDLE_SECONDS, XMLAGG( XMLCONCAT( CASE WHEN AD1.DFO_PAIR_IDLE_RESP_TIME != 0 AND DFO_PAIR_ROWNUM = 1 THEN XMLELEMENT( "activity", XMLATTRIBUTES( 'Parallel Skew' AS "class", AD1.PX_STEP_ARG AS "line", AD1.PX_ID AS "px", ROUND(AD1.DFO_PAIR_IDLE_RESP_TIME, 2) AS "rt"), 0) ELSE NULL END, XMLELEMENT( "activity", XMLATTRIBUTES( NVL(AD1.OTHER_SQL, AD1.RPI) AS "sql", AD1.NON_SQL AS "non_sql", AD1.CLASS AS "class", AD1.OTHER_SQL_CLASS AS "other_sql_class", AD1.EVENT AS "event", AD1.PLAN_LINE_ID AS "line", NVL2(AD1.PLSQL_OBJECT_ID, AD1.PLSQL_OBJECT_ID||'.'|| AD1.PLSQL_SUBPROGRAM_ID, NULL) AS "plsql_id", AD1.PLSQL_NAME AS "plsql_name", CASE WHEN AD1.SQL_ROWNUM = 1 THEN AD1.TOP_LEVEL_SQL_ID END AS "top_sql_id", CASE WHEN AD1.DBOP_NAME IS NOT NULL THEN AD1.DBOP_NAM E END AS "dbop_name", CASE WHEN AD1.DFO_MOST_ACTIVE_IID IS NOT NULL AND :B68 = 'Y' THEN AD1.DFO_MOST_ACTIVE_IID END AS "skew_iid", DECODE(AD1.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD1.DFO_MOST_ACTIVE_SID) AS "skew_sid", AD1.DFO_MOST_ACTIVE_COUNT AS "skew_count", DECODE(AD1.PX_DOP, :B28 , NULL, AD1.PX_DOP) AS "dop", DECODE(AD1.PX_DOP, AD1.PX_MIN_DOP, NULL, AD1.PX_MIN_DOP) AS "min_dop", AD1.PX_ID AS "px", AD1.PX_STEP_ID AS "step", AD1.PX_STEP_ARG AS "arg", DECODE(AD1.ACTIVITY_COUNT, AD1.RESP_TIME, NULL, ROUND(AD1.RESP_TIME, 2)) AS "rt"), AD1.ACTIVITY_COUNT)) ORDER BY AD1.PX_STEP_ID, AD1.PX_STEP_ARG, AD1.DFO_PAIR_ROWNUM) ACTIVITY_BUCKET_XML FROM (SELECT AD01.*, CASE WHEN AD01.ACTIVITY_TYPE != 'Other SQL Execution' AND AD01.ACTIVITY_TYPE != 'Non SQL' THEN AD01.ACTIVITY_TYPE END CLASS, CASE WHEN (AD01.ACTIVITY_TYPE = 'Other SQL Execution' OR AD01.ACTIVITY_TYPE = 'Non SQ L') THEN AD01.OTHER_SQL_ACTIVITY_TYPE END OTHER_SQL_CLASS, CASE WHEN AD01.ACTIVITY_TYPE != 'Other SQL Execution' AND AD01.ACTIVITY_TYPE != 'Non SQL' THEN AD01.EVENT_NAME END EVENT, CASE WHEN AD01.SQL IN ('this', 'anonymous') THEN NULL ELSE AD01.SQL END RPI, DECODE(AD01.ACTIVITY_TYPE, 'Other SQL Execution', SUBSTR(AD01.EVENT_NAME, 9), NULL) OTHER_SQL, DECODE(AD01.ACTIVITY_TYPE, 'Non SQL', AD01.EVENT_NAME, NULL) NON_SQL, ROW_NUMBER() OVER(PARTITION BY AD01.BUCKET_NUM, AD01.PX_DFO_PAIR_ID ORDER BY AD01.ACTIVITY_TYPE, AD01.EVENT_NAME, AD01.PLAN_LINE_ID) DFO_PAIR_ROWNUM FROM RESPONSE_TIME_DATA AD01) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH) ELSE NULL END, CASE WHEN :B23 = 'Y' THEN (SELECT XMLELEMENT( "parallel_info", XMLATTRIBUTES( :B17 AS "qc_instance_id", MAX_PX_QCSID AS "qc_session_id", MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MAX_PX_DOP AS "dop", MAX_PX_DOP_INSTANCES AS "max_dop_instances", DIST_INST_ COUNT AS "inst_count", DIST_PX_GROUP_COUNT AS "server_group_count", DIST_PX_SET_COUNT AS "server_set_count"), CASE WHEN :B70 = 1 THEN PX_SESSIONS ELSE NULL END, CASE WHEN :B68 = 'Y' THEN DECODE(:B69 , 1, PX_INSTANCES, NULL) ELSE NULL END) FROM (SELECT MAX_PX_QCSID, MAX_PX_DOP, MAX_PX_DOP_INSTANCES, MAX_PX_IS_CROSS_INSTANCE, SUM_SERVERS_REQUESTED, SUM_SERVERS_ALLOCATED, DIST_INST_COUNT, DIST_PX_GROUP_COUNT, DIST_PX_SET_COUNT, (SELECT XMLELEMENT( "sessions", XMLATTRIBUTES(MAX(PX_SESSION.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_SESSION.IMQ_COUNT) AS "max_imq_count", MAX(PX_SESSION.CPU_COUNT) AS "max_cpu_count", MAX(PX_SESSION.WAIT_COUNT) AS "max_wait_count", MAX(PX_SESSION.OTHER_SQL_COUNT) AS "max_other_sql_count", MAX(PX_SESSION.MAX_IO_REQS) AS "max_io_reqs", MAX(PX_SESSION.MAX_IO_BYTES) AS "max_io_bytes", MAX(PX_SESSION.MAX_BUFFER_GETS) AS "ma x_buffer_gets", MAX(PX_SESSION.MAX_ELAPSED_TIME) AS "max_elapsed_time"), XMLAGG(PX_SESSION.PX_SESSION_XML ORDER BY PX_SERVER_GROUP NULLS FIRST, PX_SERVER_SET, PX_SERVER#)) FROM (SELECT PX_SERVER_GROUP, PX_SERVER_SET, PX_SERVER#, MAX(PI.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(PI.MAX_IO_REQS) MAX_IO_REQS, MAX(PI.MAX_IO_BYTES) MAX_IO_BYTES, MAX(PI.MAX_BUFFER_GETS) MAX_BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.IMQ_COUNT) IMQ_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, SUM(PI.CPU_COUNT) CPU_COUNT, SUM(PI.OTHER_SQL_COUNT) OTHER_SQL_COUNT, XMLELEMENT( "session", XMLATTRIBUTES( INST_ID AS "inst_id", PROCESS_NAME AS "process_name", SID AS "session_id", SESSION_SERIAL# AS "session_serial", PX_SERVER_GROUP AS "server_group", PX_SERVER_SET AS "server_set", PX_SERVER# AS "server_num"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(M AX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(QUEUING_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MAX(QUEUING_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MAX(CLUST ER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX(BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(RE AD_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(READ_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MAX(READ_BYTES)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(WRITE_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MAX(WRITE_BYTES)), NULL), NVL2(MAX(IO_UNC_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MAX(IO_UNC_BYTES)), NULL), NVL2(MAX(IO_ELIG_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MAX(IO_ELIG_BYTES)), NULL), NVL2(MAX(IO_RET_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MAX(IO_RET_BYTES)), NULL), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_INTE R_BYTES) = MAX(IO_BYTES) OR NVL(MAX(IO_ELIG_BYTES), 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MAX(IO_BYTES) / DECODE(MAX(IO_INTER_BYTES), 0, 1, MAX(IO_INTER_BYTES)), 2)) END, CASE WHEN NVL(MAX(IO_ELIG_BYTES), 0) = 0 OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_UNC_BYTES) = MAX(IO_RET_BYTES) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (GREATEST( NVL(MAX(IO_UNC_BYTES), 0), NVL(MAX(IO_ELIG_BYTES), 0)) - MAX(IO_RET_BYTES)) / MAX(IO_ELIG_BYTES), 2))) END), CASE WHEN SUM(PI.ACTIVITY_COUNT) > 0 AND :B33 = 1 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B12 ) AS "first_sample_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B12 ) AS "last_sample_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 * 24) + 1 AS "duration", SUM(PI.AC TIVITY_COUNT) AS "count", SUM(PI.IMQ_COUNT) AS "imq_count", SUM(PI.CPU_COUNT) AS "cpu_count", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.OTHER_SQL_COUNT)AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME)) ELSE NULL END, CASE WHEN :B60 = 1 AND PI.HAS_OTHER_XML = 'Y' THEN (SELECT XMLTYPE(OTHER_XML) FROM GV$ALL_SQL_MONITOR MON WHERE MON.INST_ID = PI.INST_ID AND MON.KEY = PI.KEY AND MON.SID = PI.SID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END) PX_SESSION_XML FROM (SELECT MO.HAS_OTHER_XML, MO.KEY, MO.INST_ID, DECODE(MO.PROCESS_NAME, 'ora', 'PX Coordinator', MO.PROCESS_NAME) PROCESS_NAME, MO.SID, MO.SESSION_SER IAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, MAX(MO.IO_REQS) MAX_IO_REQS, MAX(MO.IO_BYTES) MAX_IO_BYTES, MAX(MO.BUFFER_GETS) MAX_BUFFER_GETS, MAX(MO.ELAPSED_TIME) MAX_ELAPSED_TIME, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.ACTIVITY_COUNT)) ACTIVITY_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.IMQ_COUNT)) IMQ_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.WAIT_COUNT)) WAIT_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.CPU_COUNT)) CPU_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.OTHER_SQL_COUNT)) OTHER_SQL_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END, MAX(DECODE(MO.ELAPSED_TIME, 0, NULL, MO.ELAPSED_TIME)) ELAPSED_TIME, MAX(DECODE(MO.QUEUING_TIME, 0, NULL, MO.QUEUING_TIME)) QUEUING_TIME, MAX(DECODE(MO.CPU_TIME, 0, NULL, CPU_TIME)) CPU_TIME, MAX(DECODE(MO.FETCHES, 0, NULL, FETCHES)) FETCHES, MAX(DECODE(MO.BUFFER _GETS, 0, NULL, MO.BUFFER_GETS)) BUFFER_GETS, MAX(DECODE(MO.IO_INTER_BYTES, 0, NULL, MO.IO_INTER_BYTES)) IO_INTER_BYTES, MAX(DECODE(MO.READ_REQS, 0, NULL, MO.READ_REQS)) READ_REQS, MAX(DECODE(MO.READ_BYTES, 0, NULL, MO.READ_BYTES)) READ_BYTES, MAX(DECODE(MO.WRITE_REQS, 0, NULL, MO.WRITE_REQS)) WRITE_REQS, MAX(DECODE(MO.WRITE_BYTES, 0, NULL, MO.WRITE_BYTES)) WRITE_BYTES, MAX(DECODE(MO.IO_BYTES, 0, NULL, MO.IO_BYTES)) IO_BYTES, MAX(DECODE(MO.IO_UNC_BYTES, 0, NULL, MO.IO_UNC_BYTES)) IO_UNC_BYTES, MAX(DECODE(MO.IO_ELIG_BYTES, 0, NULL, MO.IO_ELIG_BYTES)) IO_ELIG_BYTES, MAX(DECODE(MO.IO_RET_BYTES, 0, NULL, MO.IO_RET_BYTES)) IO_RET_BYTES, MAX(DECODE(MO.APPLICATION_WAIT_TIME, 0, NULL, MO.APPLICATION_WAIT_TIME)) APPLICATION_WAIT_TIME, MAX(DECODE(MO.CONCURRENCY_WAIT_TIME, 0, NULL, MO.CONCURRENCY_WAIT_TIME)) CONCURRENCY_WAIT_TIME, MAX(DECODE(MO.CLUSTER_WAIT_TIME, 0, NULL, MO.CLUSTER_WAIT_TIME)) CLUSTER_WAIT_TIME, MAX(DECODE(MO.USER_IO_WAIT_TIME, 0, NULL, MO.USER_IO_WAIT_TIME)) USER_IO_WAIT_TIME, MAX(DECODE(PLSQL_EXEC_TIME, 0, NULL, PLSQL_EXEC_TIME)) PLSQL_EXEC_TIME, MAX(DECODE(MO.JAVA_EXEC_TIME, 0, NULL, MO.JAVA_EXEC_TIME)) JAVA_EXEC_TIME, MAX(DECODE(MO.OTHER_WAIT_TIME, 0, NULL, MO.OTHER_WAIT_TIME)) OTHER_WAIT_TIME FROM MONITOR_DATA MO, (SELECT ASH1.INST_ID, ASH1.SESSION_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, SUM(ASH1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH1.IMQ_COUNT) IMQ_COUNT, SUM(ASH1.WAIT_COUNT) WAIT_COUNT, SUM(ASH1.CPU_COUNT) CPU_COUNT, SUM(ASH1.OTHER_SQL_COUNT)OTHER_SQL_COUNT, MIN(ASH1.ACTIVITY_START) ACTIVITY_START, MAX(ASH1.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH1 GROUP BY ASH1.INST_ID, ASH1.SESSION_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME) ASH0 WHERE MO.INST_ID = ASH0.INST_ID(+) AND MO.SID = ASH0.SESSION_ID(+) AND (:B70 = 1 OR :B69 = 1) GROUP BY MO.INST_ID, MO.KEY, MO.HAS_OTHER_XML, MO.PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SER VER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) PI WHERE (:B70 = 1) GROUP BY PI.INST_ID, PI.KEY, PI.HAS_OTHER_XML, PI.SID, PI.PROCESS_NAME, PI.SESSION_SERIAL#, PI.PX_SERVER_GROUP, PI.PX_SERVER_SET, PI.PX_SERVER#) PX_SESSION) PX_SESSIONS, (SELECT XMLELEMENT( "instances", XMLATTRIBUTES( MAX(PX_INSTANCE.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_INSTANCE.IMQ_COUNT) AS "max_imq_count", MAX(PX_INSTANCE.CPU_COUNT) AS "max_cpu_count", MAX(PX_INSTANCE.WAIT_COUNT) AS "max_wait_count", MAX(PX_INSTANCE.OTHER_SQL_COUNT) AS "max_other_sql_count", MAX(PX_INSTANCE.ELAPSED_TIME) AS "max_elapsed_time", MAX(PX_INSTANCE.BUFFER_GETS) AS "max_buffer_gets", MAX(PX_INSTANCE.IO_REQS) AS "max_io_reqs", MAX(PX_INSTANCE.IO_BYTES) AS "max_io_bytes"), XMLAGG(PX_INSTANCE.PX_INSTANCES_XML ORDER BY INST_ID)) FROM (SELECT PI.INST_ID, MAX(PI.ELAPSED_TIME) ELAPSED_TIME, MAX(PI.IO_REQS) IO_REQS, MAX (PI.IO_BYTES) IO_BYTES, MAX(PI.BUFFER_GETS) BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.IMQ_COUNT) IMQ_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, SUM(PI.CPU_COUNT) CPU_COUNT, SUM(PI.OTHER_SQL_COUNT) OTHER_SQL_COUNT, XMLELEMENT( "instance", XMLATTRIBUTES( INST_ID AS "inst_id"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(QUEUING_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MAX(QUEUING_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( " stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2( MAX(BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(READ_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MAX(READ_BYTES)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(WRITE_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MAX(WRITE_BYTES)), NULL), NVL2(MAX(IO_UNC_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MAX(IO_UNC_BYTES)), NULL), NVL2(MAX(IO_ELIG_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MAX(IO_ELIG_BYTES)), NULL), NVL2(MAX(IO_RET_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MAX(IO_RET_BYTES)), NULL), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_INTER_BYTES) = MAX(IO_BYTES) OR NVL(MAX(IO_ELIG_BYTES), 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MAX(IO_BYTES)/ DECODE(MAX(IO_INTER_BYTES), 0, 1, MAX(IO_INTER_BYTES)), 2)) END, CASE WHEN NVL(MAX(IO_ELIG_BYTES), 0) = 0 OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_UNC_BYTES) = MAX(IO_RET_BYTES) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (GREATEST( NVL(MAX(IO_UNC_BYTES), 0), NVL(MAX(IO_ELIG_BYTES), 0)) - MAX(IO_RET_BYTES)) / MAX(IO_ELIG_BYTES), 2))) END), CASE WHEN :B33 = 1 AND SUM(PI.ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B12 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B12 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 * 24) + 1 AS "duration", SUM(PI.ACTIVITY_COUNT) AS "count", SUM(PI.IMQ_COUNT) AS "imq_count", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.CPU_COUNT) AS "cpu_count", SUM(PI.OTHER_SQL_COUNT) AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME)) ELSE NULL END) PX_INSTANCES_XML FROM (SELECT MO.INST_ID, ASH.ACTIVITY_TYPE, ASH.EVENT_ NAME, ASH.ACTIVITY_COUNT, ASH.IMQ_COUNT, ASH.WAIT_COUNT, ASH.CPU_COUNT, ASH.OTHER_SQL_COUNT, ASH.ACTIVITY_START, ASH.ACTIVITY_END, MO.ELAPSED_TIME, MO.QUEUING_TIME, MO.CPU_TIME, MO.APPLICATION_WAIT_TIME, MO.CONCURRENCY_WAIT_TIME, MO.CLUSTER_WAIT_TIME, MO.USER_IO_WAIT_TIME, MO.PLSQL_EXEC_TIME, MO.JAVA_EXEC_TIME, MO.OTHER_WAIT_TIME, MO.FETCHES, MO.BUFFER_GETS, MO.IO_INTER_BYTES, MO.IO_BYTES, MO.READ_REQS, MO.READ_BYTES, MO.WRITE_REQS, MO.WRITE_BYTES, MO.IO_REQS, MO.IO_UNC_BYTES, MO.IO_ELIG_BYTES, MO.IO_RET_BYTES FROM (SELECT MO0.INST_ID, SUM(MO0.ELAPSED_TIME) ELAPSED_TIME, SUM(MO0.QUEUING_TIME) QUEUING_TIME, SUM(MO0.CPU_TIME) CPU_TIME, SUM(MO0.FETCHES) FETCHES, SUM(MO0.BUFFER_GETS) BUFFER_GETS, SUM(MO0.IO_INTER_BYTES) IO_INTER_BYTES, SUM(MO0.IO_BYTES) IO_BYTES, SUM(MO0.READ_REQS) READ_REQS, SUM(MO0.READ_BYTES) READ_BYTES, SUM(MO0.WRITE_REQS) WRITE_REQS, SUM(MO0.WRITE_BYTES) WRITE_BYTES, SUM(MO0.IO_REQS) IO_REQS, SUM(MO0.IO_UNC_BYTES) IO_UNC_BYTES , SUM(MO0.IO_ELIG_BYTES) IO_ELIG_BYTES, SUM(MO0.IO_RET_BYTES) IO_RET_BYTES, SUM(MO0.APPLICATION_WAIT_TIME) APPLICATION_WAIT_TIME, SUM(MO0.CONCURRENCY_WAIT_TIME) CONCURRENCY_WAIT_TIME, SUM(MO0.CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME, SUM(MO0.USER_IO_WAIT_TIME) USER_IO_WAIT_TIME, SUM(MO0.PLSQL_EXEC_TIME) PLSQL_EXEC_TIME, SUM(MO0.JAVA_EXEC_TIME) JAVA_EXEC_TIME, SUM(MO0.OTHER_WAIT_TIME) OTHER_WAIT_TIME FROM MONITOR_DATA MO0 GROUP BY MO0.INST_ID) MO, (SELECT ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, SUM(ASH0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH0.IMQ_COUNT) IMQ_COUNT, SUM(ASH0.WAIT_COUNT) WAIT_COUNT, SUM(ASH0.CPU_COUNT) CPU_COUNT, SUM(ASH0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH0 GROUP BY ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) ASH, MONITOR_AGG WHERE MO.INST_ID = ASH.INST_ID(+) AND MONITOR_AGG.DIST_INST_COUNT > 0 ) PI GROUP BY PI.INST_ID)PX_INSTANCE) PX_INSTAN CES FROM MONITOR_AGG)) ELSE NULL END, XPLAN_XML, CASE WHEN :B43 = 1 THEN (SELECT XMLELEMENT( "plan_monitor", XMLATTRIBUTES(MAX(PLI.MAX_LINE_ACTIVITY_COUNT) AS "max_activity_count", MAX(PLI.OVERALL_MAX_IO_REQS) AS "max_io_reqs", MAX(PLI.OVERALL_MAX_IO_BYTES) AS "max_io_bytes", MAX(PLI.MAX_LINE_IMQ_COUNT) AS "max_imq_count", MAX(PLI.MAX_LINE_CPU_COUNT) AS "max_cpu_count", MAX(PLI.MAX_LINE_WAIT_COUNT) AS "max_wait_count", MAX(PLI.MAX_LINE_OTHER_SQL_COUNT) AS "max_other_sql_count"), XMLAGG( XMLELEMENT( "operation", XMLATTRIBUTES( PLI.PLAN_LINE_ID AS "id", PLI.PARENT_ID AS "parent_id", PLI.OPERATION AS "name", PLI.OPTIONS AS "options", PLI.DEPTH AS "depth", PLI.POSITION AS "position", PLI.INACTIVE AS "skp", PLI.PX_TYPE AS "px_type"), NVL2(PLI.OBJECT_NAME, XMLELEMENT( "object", XMLATTRIBUTES(PL I.OBJECT_TYPE AS "type"), XMLFOREST(PLI.OBJECT_OWNER AS "owner"), XMLFOREST(PLI.OBJECT_NAME AS "name")), NULL), XMLFOREST(PLI.PARTITION_START AS "partition_start", PLI.PARTITION_STOP AS "partition_stop"), CASE WHEN PLI.CARDINALITY IS NULL AND PLI.BYTES IS NULL AND PLI.COST IS NULL AND PLI.TEMP_SPACE IS NULL AND PLI.TIME IS NULL THEN NULL ELSE XMLELEMENT( "optimizer", NULL, NVL2(PLI.CARDINALITY, XMLFOREST(PLI.CARDINALITY AS "cardinality"), NULL), NVL2(PLI.BYTES, XMLFOREST(PLI.BYTES AS "bytes"), NULL), NVL2(PLI.COST, XMLFOREST(PLI.COST AS "cost"), NULL), NVL2(PLI.CPU_COST, XMLFOREST(PLI.CPU_COST AS "cpu_cost"), NULL), NVL2(PLI.IO_COST, XMLFOREST(PLI.IO_COST AS "io_cost"), NULL), NVL2(PLI.TEMP_SPACE, XMLFOREST(PLI.TEMP_SPACE AS "temp"), NULL), NVL2(PLI.TIME, XMLFOREST(PLI.TIME AS "time"), NULL)) END, XMLELEMENT( "stats", XMLATTRIBUTES('plan_monitor' AS "type"), NVL2(PLI.FIRST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES('first_active' AS "name"), TO_CHAR(FIRST_MOVE_TIME, :B12 )), NULL), CASE WHEN PLI.FIRST_MOVE_TIME != PLI.FIRST_CHANGE_TIME THEN XMLELEMENT( "stat", XMLATTRIBUTES('first_row' AS "name"), TO_CHAR(FIRST_CHANGE_TIME, :B12 )) ELSE NULL END, NVL2(PLI.LAST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES('last_active' AS "name"), TO_CHAR(LAST_MOVE_TIME, :B12 )), NULL), CASE WHEN (PLI.FIRST_MOVE_TIME IS NULL OR PLI.LAST_MOVE_TIME IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), ROUND((LAST_MOVE_TIME - FIRST_MOVE_TIME) * 3600 * 24)+1) END, CASE WHEN (PLI.OVERALL_LAST_MOVE_TIME IS NULL OR PLI.LAST_MOVE_TIME IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('from_most_recent' AS "name"), ROUND((PLI.OVERALL_LAST_MOVE_TIME - PLI.LAST_ MOVE_TIME) * 3600 * 24)) END, NVL2(PLI.LAST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES( 'from_sql_exec_start' AS "name"), ROUND( (FIRST_MOVE_TIME - :B8 ) * 3600*24)), NULL), NVL2(PLI.PERCENT_COMPLETE, XMLELEMENT( "stat", XMLATTRIBUTES('percent_complete' AS "name"), PLI.PERCENT_COMPLETE), NULL), NVL2(PLI.TIME_REMAINING, XMLELEMENT( "stat", XMLATTRIBUTES('time_left' AS "name"), PLI.TIME_REMAINING), NULL), CASE WHEN PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES('starts' AS "name"), PLI.STARTS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_starts' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_STARTS/1000000), 10000), NULL) AS "iid", MOD(MAX_STARTS, 1000000) AS "sid"), TRUNC(PLI.MAX_STARTS/10000000000)) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES('dop' AS "name"), PLI.DOP) ELSE NULL END, CASE WHEN NEED_ROWS IS NOT NULL AND PLI.FIRST_MOVE_TIME IS NOT NULL THEN XMLELEMENT( "stat", XMLATTRIBUTES('cardinality' AS "name"), PLI.OUTPUT_ROWS) ELSE NULL END, CASE WHEN PLI.NEED_ROWS IS NOT NULL AND PLI.DOP > 0 AND PLI.MAX_OUTPUT_ROWS IS NOT NULL AND (PLI.FIRST_MOVE_TIME IS NOT NULL) THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_card' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_OUTPUT_ROWS/1000000), 10000), NULL) AS "iid", MOD(MAX_OUTPUT_ROWS, 1000000) AS "sid"), TRUNC(PLI.MAX_OUTPUT_ROWS/10000000000)) ELSE NULL END, CASE WHEN PLI.MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('memory' AS "name"), PLI.MEM) ELSE NULL END, CASE WHEN PLI.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_memory' AS "name"), PLI.MAX_MEM) ELSE NULL END, CASE WHEN PLI.DO P > 0 AND PLI.MIN_MAX_MEM IS NOT NULL AND PLI.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'min_max_mem' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MIN_MAX_MEM/1000000), 10000), NULL) AS "iid", MOD(MIN_MAX_MEM, 1000000) AS "sid"), TRUNC(PLI.MIN_MAX_MEM/10000000000)) ELSE NULL END, CASE WHEN PLI.TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('temp' AS "name"), PLI.TEMP) ELSE NULL END, CASE WHEN PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_temp' AS "name"), PLI.MAX_TEMP) ELSE NULL END, CASE WHEN PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('spill_count' AS "name"), PLI.SPILL_COUNT) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_MAX_TEMP IS NOT NULL AND PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_max_temp' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_MAX_TEMP/1000000), 10000), NULL) AS "iid", MOD(MAX_MAX_TEMP, 1000000) AS "sid"), TRUNC(PLI.MAX_MAX_TEMP/10000000000)) ELSE NULL END, CASE WHEN PLI.READ_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), PLI.READ_REQS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_READ_REQS IS NOT NULL AND PLI.READ_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_read_reqs' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(PLI.MAX_READ_REQS/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_READ_REQS, 1000000) AS "sid"), TRUNC(PLI.MAX_READ_REQS/10000000000)) ELSE NULL END, CASE WHEN PLI.READ_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), PLI.READ_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_READ_BYTES IS NOT NULL AND PLI.READ_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_read_bytes' AS "name", DECODE(:B68 , 'Y ', MOD(TRUNC(PLI.MAX_READ_BYTES/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_READ_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_READ_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.WRITE_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), PLI.WRITE_REQS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_WRITE_REQS IS NOT NULL AND PLI.WRITE_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_write_reqs' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(PLI.MAX_WRITE_REQS/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_WRITE_REQS, 1000000) AS "sid"), TRUNC(PLI.MAX_WRITE_REQS/10000000000)) ELSE NULL END, CASE WHEN PLI.WRITE_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), PLI.WRITE_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_WRITE_BYTES IS NOT NULL AND PLI.WRITE_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_write_bytes' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(PLI.MAX_WRITE_BYTES/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_WRITE_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_WRITE_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES('io_inter_bytes' AS "name"), PLI.IO_INTER_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_io_inter_bytes' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_IO_INTER_BYTES/1000000), 10000), NULL) AS "iid", MOD(MAX_IO_INTER_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_IO_INTER_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND P LI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(PLI.IO_BYTES / DECODE(PLI.IO_INTER_BYTES, 0, 1, PLI.IO_INTER_BYTES), 2)) ELSE NULL END), NVL2(STAT_GID, XMLELEMENT( "rwsstats", XMLATTRIBUTES( PLI.STAT_GID AS "group_id"), DECODE(GID_ROWNUM, 1, (SELECT XMLELEMENT( "metadata", NULL, XMLAGG( XMLELEMENT( "stat", XMLATTRIBUTES( ROWNUM AS "id", NAME AS "name", DESCRIPTION AS "desc", TYPE AS "type", DECODE(FLAGS, 0, NULL, FLAGS) AS "flags"), NULL) ORDER BY ID)) FROM V$SQL_MONITOR_STATNAME WHERE GROUP_ID = PLI.STAT_GID), NULL), NVL2(STAT1_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(1 AS "id"), STAT1_VALUE), NULL), NVL2(STAT2_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(2 AS "id"), STAT2_VALUE), NULL), NVL2(STAT3_VALUE, XMLELEMENT( "stat", XM LATTRIBUTES(3 AS "id"), STAT3_VALUE), NULL), NVL2(STAT4_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(4 AS "id"), STAT4_VALUE), NULL), NVL2(STAT5_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(5 AS "id"), STAT5_VALUE), NULL), NVL2(STAT6_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(6 AS "id"), STAT6_VALUE), NULL), NVL2(STAT7_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(7 AS "id"), STAT7_VALUE), NULL), NVL2(STAT8_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(8 AS "id"), STAT8_VALUE), NULL), NVL2(STAT9_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(9 AS "id"), STAT9_VALUE), NULL), NVL2(STAT10_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(10 AS "id"), STAT10_VALUE), NULL)), NULL), CASE WHEN PLI.LINE_ACTIVITY_COUNT > 0 AND :B33 = 1 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(PLI.LINE_ACTIVITY_START, :B12 ) AS "start_time", TO_CHAR(PLI.LINE_ACTIVITY_END, :B12 ) AS "end_time", ROUND((PLI.LINE_ACTIVITY_END - PLI.LINE_ACTIVITY_START) * 3600*24) + 1 AS "duration", PLI.LINE_ACTIVITY_COUNT AS "count", PLI.LINE_IMQ_COUNT AS "imq_count", PLI.LINE_WAIT_COUNT AS "wait_count", PLI.LINE_CPU_COUNT AS "cpu_count", PLI.LINE_OTHER_SQL_COUNT AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), PLI.ACTIVITY_TOTAL, PLI.PLAN_ACTIVITY_HISTO) ELSE NULL END) ORDER BY PLI.PLAN_LINE_ID) ) FROM (SELECT AT.MAX_LINE_ACTIVITY_COUNT, PM.OVERALL_MAX_IO_REQS, PM.OVERALL_MAX_IO_BYTES, AT.MAX_LINE_IMQ_COUNT, AT.MAX_LINE_CPU_COUNT, AT.MAX_LINE_WAIT_COUNT, AT.LINE_OTHER_SQL_COUNT, AT.MAX_LINE_OTHER_SQL_COUNT, PM.PLAN_LINE_ID, PM.PARENT_ID, PM.OPERATION, PM.OPTIONS, PM.DEPTH, PM.POSITION, PM.INACTIVE, CASE WHEN PM.OPERATION = 'PX COORDINATOR' AND :B23 = 'Y' AND (PM.PX_SERVER_SET IS NOT NU LL OR AH.PX_SERVER_SET IS NOT NULL) THEN 'QC' WHEN PM.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(PM.PX_SERVER_SET) WHEN AH.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(AH.PX_SERVER_SET) WHEN (:B23 = 'N' OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL)) THEN NULL ELSE 'QC' END PX_TYPE, PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START, PM.LAST_CHANGE_TIME, PM.OVERALL_LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END, AT.OVERALL_LINE_ACTIVITY_END, LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)) FIRST_MOVE_TIME, GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)) LAST_MOVE_TIME, GREATEST(NVL(AT.OVERALL_LINE_ACTIVITY_END, PM.OVERALL_LAST_CHANGE_TIME), NVL(PM.OVERALL_LAST_CHANGE_TIME, AT.OVERALL_LINE_ACTIVITY_END)) OVERALL_LAST_MOVE_TIME, CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 THEN 1 ELSE 0 END HAS_EXECUTED, PM.OBJECT_NAME, PM.OBJECT_TYPE, PM.OBJECT_OWN ER, PM.PARTITION_START, PM.PARTITION_STOP, PM.CARDINALITY, PM.BYTES, PM.COST, PM.TEMP_SPACE, PM.TIME, PM.CPU_COST, PM.IO_COST, LO.PERCENT_COMPLETE, LO.TIME_REMAINING, PM.STARTS, PM.DOP, PM.MAX_STARTS, PM.OUTPUT_ROWS, PM.NEED_ROWS, PM.MAX_OUTPUT_ROWS, PM.MEM, PM.MAX_MEM, PM.MIN_MAX_MEM, PM.TEMP, PM.MAX_TEMP, PM.SPILL_COUNT, PM.MAX_MAX_TEMP, PM.READ_REQS, PM.MAX_READ_REQS, PM.READ_BYTES, PM.MAX_READ_BYTES, PM.WRITE_REQS, PM.MAX_WRITE_REQS, PM.WRITE_BYTES, PM.MAX_WRITE_BYTES, PM.IO_INTER_BYTES, PM.IO_BYTES, PM.MAX_IO_INTER_BYTES, AT.LINE_ACTIVITY_COUNT, AT.LINE_IMQ_COUNT, AT.LINE_WAIT_COUNT, AT.LINE_CPU_COUNT, AT.ACTIVITY_TOTAL, AH.PLAN_ACTIVITY_HISTO, PM.STAT_GID, PM.GID_ROWNUM, PM.STAT1_VALUE, PM.STAT2_VALUE, PM.STAT3_VALUE, PM.STAT4_VALUE, PM.STAT5_VALUE, PM.STAT6_VALUE, PM.STAT7_VALUE, PM.STAT8_VALUE, PM.STAT9_VALUE, PM.STAT10_VALUE FROM (SELECT AT0.*, MAX(LINE_ACTIVITY_END) OVER() OVERALL_LINE_ACTIVITY_END, MAX(LINE_ACTIVITY_COUN T) OVER() MAX_LINE_ACTIVITY_COUNT, MAX(LINE_IMQ_COUNT) OVER() MAX_LINE_IMQ_COUNT, MAX(LINE_CPU_COUNT) OVER() MAX_LINE_CPU_COUNT, MAX(LINE_WAIT_COUNT) OVER() MAX_LINE_WAIT_COUNT, MAX(LINE_OTHER_SQL_COUNT) OVER() MAX_LINE_OTHER_SQL_COUNT FROM (SELECT AD1.PLAN_LINE_ID, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) LINE_ACTIVITY_COUNT, SUM(AD1.IMQ_COUNT) LINE_IMQ_COUNT, SUM(AD1.WAIT_COUNT) LINE_WAIT_COUNT, SUM(AD1.OTHER_SQL_COUNT) LINE_OTHER_SQL_COUNT, SUM(AD1.CPU_COUNT) LINE_CPU_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.PLAN_LINE_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD0.ACTIV ITY_END) LINE_ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MAX(AD0.DFO_MOST_ACTIVE_IID) DFO_MOST_ACTIVE_IID, MAX(AD0.DFO_MOST_ACTIVE_SID) DFO_MOST_ACTIVE_SID, SUM(AD0.DFO_MOST_ACTIVE_COUNT) DFO_MOST_ACTIVE_COUNT FROM ASH_DATA AD0 WHERE AD0.PLAN_LINE_ID IS NOT NULL GROUP BY AD0.PLAN_LINE_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID) AT0) AT, (SELECT AD2.PLAN_LINE_ID, MIN(AD2.PX_SERVER_SET) PX_SERVER_SET, MIN(AD2.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD2.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD2.IMQ_COUNT) IMQ_COUNT, SUM(AD2.WAIT_COUNT) WAIT_COUNT, SUM(AD2.CPU_COUNT) CPU_COUNT, SUM(AD2.OTHER_SQL_COUNT) OTHER_SQL_COUNT, CASE WHEN :B29 = 1 AND SUM(AD2.ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_histogram", XMLATTRIBUTES( :B26 AS "buc ket_interval", :B27 AS "bucket_count", TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR(:B22 , :B12 ) AS "end_time", ROUND((:B22 -:B20 ) *3600*24) + 1 AS "duration"), XMLAGG(XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), AD2.ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ELSE NULL END PLAN_ACTIVITY_HISTO FROM (SELECT AD1.PLAN_LINE_ID, AD1.BUCKET_NUM, MIN(AD1.PX_SERVER_SET) PX_SERVER_SET, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.IMQ_COUNT) IMQ_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUM(AD1.CPU_COUNT) CPU_COUNT, SUM(AD1.OTHER_SQL_COUNT) OTHER_SQL_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(NVL2(AD1.ACTIVITY_TYPE, XMLELEMENT( "acti vity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT), NULL) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.PLAN_LINE_ID, AD0.PLAN_ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(NVL2(AD0.ACTIVITY_START, AD0.PX_SERVER_SET, NULL)) PX_SERVER_SET, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD0.ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 GROUP BY AD0.PLAN_LINE_ID, AD0.PLAN_ACTIVITY_BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID, AD1.BUCKET_NUM) AD2 GROUP BY AD2.PLAN_LINE_ID) AH, (SELECT LO2.SQL_PLAN_LINE_ID PLAN_LINE_ID, DECODE( SUM(LO2.TOTALWORK), 0, NULL, ROUND(SUM(LO2.SOFAR)*100/SUM(LO2.TOTALWORK))) PERCENT_COMPLETE, MAX(LO2.TIME_REMAINING) TIME_REMAINING FROM MONITOR_DATA MO, (SELECT * FROM TABLE( GV$(CURSOR( SELECT USERENV('instance') INST_ID, SQL_PLAN_LINE_ID, TOTALWORK, SOFAR, TIME_REMAINING, SID FROM V$SESSION_LONGOPS L WHERE (:B49 = 'Y' OR :B71 = 'DONE (ERROR)') AND L.SQL_ID = :B9 AND L.SQL_EXEC_START = :B8 AND L.SQL_EXEC_ID = :B7 )))) LO2 WHERE LO2.INST_ID = MO.INST_ID AND LO2.SID = MO.SID GROUP BY LO2.SQL_PLAN_LINE_ID ) LO, (SELECT PM0.*, CASE WHEN PM0.STARTS IS NULL OR PM0.STARTS = 0 OR PM0.OUTPUT_ROWS IS NULL THEN NULL ELSE 1 END NEED_ROWS, ROW_NUMBER() OVER(PARTITION BY PM0.STAT_GID ORDER BY PM0.PLAN_LINE_ID ) GID_ROWNUM, MAX(LAST_CHANGE_TIME) OVER() OVERALL_LAST_CHANGE_TIME, MAX(MAX_IO_REQS) OVER() OVERALL_MAX_IO_REQS, MAX(MAX_IO_BYTES) OVER() OVERALL_MAX_IO_BYTES FROM (SELECT /*+ leading(mo) use_hash(plm) */ PLM.PLAN_LINE_ID PLAN_LINE_ID, PLM.PLAN_OPERATION OPERATION, PLM.PLAN _OPTIONS OPTIONS, MAX(PLM.PLAN_PARENT_ID) PARENT_ID, MAX(PLM.PLAN_DEPTH) DEPTH, MAX(PLM.PLAN_POSITION) POSITION, MAX(PLM.PLAN_OPERATION_INACTIVE) INACTIVE, MAX(PLM.PLAN_OBJECT_OWNER) OBJECT_OWNER, MAX(PLM.PLAN_OBJECT_NAME) OBJECT_NAME, MAX(PLM.PLAN_OBJECT_TYPE) OBJECT_TYPE, MAX(PLM.PLAN_COST) COST, MAX(PLM.PLAN_CARDINALITY) CARDINALITY, MAX(PLM.PLAN_BYTES) BYTES, MAX(PLM.PLAN_CPU_COST) CPU_COST, MAX(PLM.PLAN_IO_COST) IO_COST, MAX(PLM.PLAN_TEMP_SPACE) TEMP_SPACE, MAX(PLM.PLAN_TIME) TIME, MAX(PLM.PLAN_PARTITION_START) PARTITION_START, MAX(PLM.PLAN_PARTITION_STOP) PARTITION_STOP, MIN(PLM.FIRST_CHANGE_TIME) FIRST_CHANGE_TIME, MAX(PLM.LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(PLM.LAST_CHANGE_TIME) MIN_LAST_CHANGE_TIME, MIN(NVL2(PLM.FIRST_CHANGE_TIME, MO.PX_SERVER_SET, NULL)) PX_SERVER_SET, COUNT(CASE WHEN PLM.PAR IS NOT NULL AND PLM.STARTS IS NOT NULL AND PLM.STARTS > 0 AND PLM.PLAN_LINE_ID != 0 AND PLM.PLAN_OPERATION != 'PX COORDINATOR' THEN 1 ELSE NULL END) DOP, S UM(PLM.STARTS) STARTS, MAX(NVL2(PAR, PLM.STARTS * 10000000000 + PLM.ES, NULL)) MAX_STARTS, SUM(PLM.OUTPUT_ROWS) OUTPUT_ROWS, MAX(NVL2(PAR, PLM.OUTPUT_ROWS * 10000000000 +PLM.ES, NULL)) MAX_OUTPUT_ROWS, SUM(PLM.WORKAREA_MEM) MEM, SUM(PLM.WORKAREA_MAX_MEM) MAX_MEM, MIN(NVL2(PAR, PLM.WORKAREA_MAX_MEM * 10000000000 + PLM.ES, NULL)) MIN_MAX_MEM, SUM(PLM.WORKAREA_TEMPSEG) TEMP, SUM(PLM.WORKAREA_MAX_TEMPSEG) MAX_TEMP, MAX(NVL2(PAR, PLM.WORKAREA_MAX_TEMPSEG * 10000000000 + PLM.ES, NULL)) MAX_MAX_TEMP, COUNT(PLM.WORKAREA_MAX_TEMPSEG) SPILL_COUNT, SUM(PLM.PHYSICAL_READ_REQUESTS) READ_REQS, MAX(NVL2(PAR, PLM.PHYSICAL_READ_REQUESTS * 10000000000 + PLM.ES, NULL)) MAX_READ_REQS, SUM(PLM.PHYSICAL_READ_BYTES) READ_BYTES, MAX(NVL2(PAR, PLM.PHYSICAL_READ_BYTES * 10000000000 + PLM.ES, NULL)) MAX_READ_BYTES, SUM(PLM.PHYSICAL_WRITE_REQUESTS) WRITE_REQS, MAX(NVL2(PAR, PLM.PHYSICAL_WRITE_REQUESTS * 10000000000 + PLM.ES, NULL)) MAX_WRITE_REQS, SUM(PLM.PHYSICAL_WRITE_BYTES) WRITE _BYTES, MAX(NVL2(PAR, PLM.PHYSICAL_WRITE_BYTES * 10000000000 + PLM.ES, NULL)) MAX_WRITE_BYTES, NVL(SUM(PLM.PHYSICAL_READ_BYTES), 0) + NVL(SUM(PLM.PHYSICAL_WRITE_BYTES), 0) IO_BYTES, SUM(NVL(PLM.PHYSICAL_READ_REQUESTS, 0) + NVL(PLM.PHYSICAL_WRITE_REQUESTS, 0)) MAX_IO_REQS, SUM(NVL(PLM.PHYSICAL_READ_BYTES, 0) + NVL(PLM.PHYSICAL_WRITE_BYTES, 0)) MAX_IO_BYTES, SUM(PLM.IO_INTERCONNECT_BYTES) IO_INTER_BYTES, MAX(NVL2(PAR, PLM.IO_INTERCONNECT_BYTES * 10000000000 + PLM.ES, NULL)) MAX_IO_INTER_BYTES, MAX(OTHERSTAT_GROUP_ID) STAT_GID, NVL(DECODE(MAX(OTHERSTAT_1_TYPE), 3, MAX(OTHERSTAT_1_VALUE), 4, MAX(OTHERSTAT_1_VALUE), 6, MIN(OTHERSTAT_1_VALUE), 7, MIN(OTHERSTAT_1_VALUE), NULL), SUM(DECODE(OTHERSTAT_1_TYPE, 1, OTHERSTAT_1_VALUE, 2, OTHERSTAT_1_VALUE, NULL))) STAT1_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_2_TYPE), 3, MAX(OTHERSTAT_2_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_2_VALUE), 7, MIN(RMAX1 + OTHERSTAT_2_VALUE), NULL), 6, MI N(OTHERSTAT_2_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_2_TYPE, 1, OTHERSTAT_2_VALUE, 2, OTHERSTAT_2_VALUE, NULL))) STAT2_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_3_TYPE), 3, MAX(OTHERSTAT_3_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_3_VALUE), 7, MIN(RMAX1 + OTHERSTAT_3_VALUE), NULL), 6, MIN(OTHERSTAT_3_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_3_TYPE, 1, OTHERSTAT_3_VALUE, 2, OTHERSTAT_3_VALUE, NULL))) STAT3_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_4_TYPE), 3, MAX(OTHERSTAT_4_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_4_VALUE), 7, MIN(RMAX1 + OTHERSTAT_4_VALUE), NULL), 6, MIN(OTHERSTAT_4_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_4_TYPE, 1, OTHERSTAT_4_VALUE, 2, OTHERSTAT_4_VALUE, NULL))) STAT4_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_5_TYPE), 3, MAX(OTHERSTAT_5_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_5_VALUE), 7, MIN(RMAX1 + OTHERSTAT_5_VALUE), NULL), 6, MIN(OTHERSTAT_5_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_5_TYPE, 1, OTHERSTAT_5_VALUE, 2, OTHERSTAT_5_VALUE, NULL))) STAT5_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_6_TYPE), 3, MAX(OTHERSTAT_6_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_6_VALUE), 7, MIN(RMAX1 + OTHERSTAT_6_VALUE), NULL), 6, MIN(OTHERSTAT_6_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_6_TYPE, 1, OTHERSTAT_6_VALUE, 2, OTHERSTAT_6_VALUE, NULL))) STAT6_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_7_TYPE), 3, MAX(OTHERSTAT_7_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_7_VALUE), 7, MIN(RMAX1 + OTHERSTAT_7_VALUE), NULL), 6, MIN(OTHERSTAT_7_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_7_TYPE, 1, OTHERSTAT_7_VALUE, 2, OTHERSTAT_7_VALUE, NULL))) STAT7_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_8_TYPE), 3, MAX(OTHERSTAT_8_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OT HERSTAT_8_VALUE), 7, MIN(RMAX1 + OTHERSTAT_8_VALUE), NULL), 6, MIN(OTHERSTAT_8_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_8_TYPE, 1, OTHERSTAT_8_VALUE, 2, OTHERSTAT_8_VALUE, NULL))) STAT8_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_9_TYPE), 3, MAX(OTHERSTAT_9_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_9_VALUE), 7, MIN(RMAX1 + OTHERSTAT_9_VALUE), NULL), 6, MIN(OTHERSTAT_9_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_9_TYPE, 1, OTHERSTAT_9_VALUE, 2, OTHERSTAT_9_VALUE, NULL))) STAT9_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_10_TYPE), 3, MAX(OTHERSTAT_10_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_10_VALUE), 7, MIN(RMAX1 + OTHERSTAT_10_VALUE), NULL), 6, MIN(OTHERSTAT_10_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_10_TYPE, 1, OTHERSTAT_10_VALUE, 2, OTHERSTAT_10_VALUE, NULL))) STAT10_VALUE FROM (SELECT A.*, OTHERSTAT_1_VALUE * 10000000000000000 RMAX1, A.INST_ID * 100 0000 + A.SID ES, DECODE(A.PROCESS_NAME, 'ora', NULL, 1) PAR FROM (SELECT * FROM TABLE(GV$(CURSOR( SELECT USERENV('instance') INST_ID, A0.* FROM V$ALL_SQL_PLAN_MONITOR A0 WHERE A0.SQL_ID = :B9 AND A0.SQL_EXEC_START = :B8 AND A0.SQL_EXEC_ID = :B7 AND USERENV('instance') BETWEEN :B11 AND :B10 ))) ) A ) PLM, MONITOR_DATA MO WHERE PLM.INST_ID = MO.INST_ID AND PLM.INST_ID BETWEEN :B11 AND :B10 AND PLM.KEY = MO.KEY GROUP BY PLM.PLAN_LINE_ID, PLM.PLAN_OPERATION, PLM.PLAN_OPTIONS) PM0) PM WHERE AH.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND AT.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND LO.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND (:B21 IS NULL OR PM.PLAN_LINE_ID = :B21 )) PLI) ELSE NULL END, CASE WHEN :B42 = 1 THEN DBMS_SQLTUNE.BUILD_STASH_XML( SESSION_ID=>:B19 , SESSION_SERIAL=>:B18 , SESSION_INST_ID=>:B17 , PX_MODE=>'yes', START_TIME=>:B20 , END_TIME=>:B22 , MISSING_SECONDS=> :B41 , INSTANCE_LOW_FILTER=>:B11 , INSTANCE_HIGH_FILTER=>:B10 , BUCKET_MAX_COUNT=>NUL L, BUCKET_INTERVAL=>:B26 , REPORT_LEVEL=>'typical', CPU_CORES=>:B40 , IS_HYPER=>:B39 ) ELSE NULL END), (CASE WHEN :B38 = 1 THEN (SELECT XMLELEMENT( "skewed_sessions", XMLATTRIBUTES( DECODE(MIN(INST_ID), NULL, :B17 , MIN(INST_ID)) AS "min_iid", DECODE(MAX(INST_ID), NULL, :B17 , MAX(INST_ID)) AS "max_iid"), XMLAGG( XMLELEMENT( "s", XMLATTRIBUTES( INST_ID AS "i"), SESSION_ID) ORDER BY INST_ID, SESSION_ID)) FROM (SELECT SESS.* FROM (SELECT DECODE(AD.DFO_MOST_ACTIVE_IID, :B17 , NULL, AD.DFO_MOST_ACTIVE_IID) INST_ID, AD.DFO_MOST_ACTIVE_SID SESSION_ID FROM ASH_DATA AD WHERE AD.DFO_MOST_ACTIVE_COUNT IS NOT NULL GROUP BY AD.DFO_MOST_ACTIVE_IID, AD.DFO_MOST_ACTIVE_SID ORDER BY MAX(AD.DFO_MOST_ACTIVE_COUNT) DESC) SESS WHERE ROWNUM <= 100)) ELSE NULL END), (CASE WHEN :B37 = 1 THEN (SELECT XMLELEMENT( "report_repository_summary", CASE WHEN MA.MAX_DBOP_NAME IS NOT NULL THEN XMLELEMENT( "dbop" , XMLATTRIBUTES('F' AS "detail", MA.MAX_DBOP_NAME AS "dbop_name", TO_CHAR(MA.MIN_FIRST_REFRESH_TIME, :B12 ) AS "dbop_exec_start", MA.MAX_DBOP_EXEC_ID AS "dbop_exec_id"), XMLFOREST( MA.MAX_STATUS AS "status", TO_CHAR(NVL(MA.MAX_SQL_EXEC_START, MA.MIN_FIRST_REFRESH_TIME), :B12 )AS "first_refresh_time", TO_CHAR(MA.MAX_LAST_REFRESH_TIME, :B12 )AS "last_refresh_time", MA.SUM_REFRESH_COUNT AS "refresh_count", MA.MAX_INST_ID AS "inst_id", MA.MAX_SESSION_ID AS "session_id", MA.MAX_SESSION_SERIAL AS "session_serial", MA.MAX_USERID AS "user_id", MA.MAX_USERNAME AS "user", MA.MAX_CON_ID AS "con_id", MA.MAX_CON_NAME AS "con_name", MA.MAX_MODULE AS "module", MA.MAX_ACTION AS "action", MA.MAX_SERVICE_NAME AS "service", MA.MAX_CLIENT_ID AS "client_id", MA.MAX_CLIENT_INFO AS "client_inf o", MA.MAX_PROGRAM AS "program", MA.MAX_PL_ENTRY_OID AS "plsql_entry_object_id", MA.MAX_PL_ENTRY_PROGID AS "plsql_entry_subprogram_id", MA.MAX_PL_OID AS "plsql_object_id", MA.MAX_PL_PROGID AS "plsql_subprogram_id", MA.MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MA.MAX_PX_DOP AS "dop", MA.MAX_PX_DOP_INSTANCES AS "instances"), CASE WHEN MA.MAX_ERROR_NUMBER IS NULL THEN NULL ELSE XMLELEMENT( "error", XMLATTRIBUTES(MA.MAX_ERROR_NUMBER AS "number", MA.MAX_ERROR_FACILITY AS "facility"), MA.MAX_ERROR_MESSAGE) END, XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), GREATEST(ROUND((MA.MAX_LAST_REFRESH_TIME- NVL(MA.MAX_SQL_EXEC_START, MA.MIN_FIRST_REFRESH_TIME))*86400), LEAST(MA.SUM_ELAPSED_TIME/1000000, 1), CEIL(MA.MAX_QUEUING_TIME/1000000))), DECODE(MA. SUM_ELAPSED_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MA.SUM_ELAPSED_TIME)), DECODE(MA.MAX_QUEUING_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MA.MAX_QUEUING_TIME)), DECODE(MA.SUM_CPU_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MA.SUM_CPU_TIME)), DECODE(MA.SUM_USER_IO_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MA.SUM_USER_IO_WAIT_TIME)), DECODE(MA.SUM_APPLICATION_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MA.SUM_APPLICATION_WAIT_TIME)), DECODE(MA.SUM_CONCURRENCY_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MA.SUM_CONCURRENCY_WAIT_TIME)), DECODE(MA.SUM_CLUSTER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat ", XMLATTRIBUTES('cluster_wait_time' AS "name"), MA.SUM_CLUSTER_WAIT_TIME)), DECODE(MA.SUM_PLSQL_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MA.SUM_PLSQL_EXEC_TIME)), DECODE(MA.SUM_JAVA_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MA.SUM_JAVA_EXEC_TIME)), DECODE(MA.SUM_OTHER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), MA.SUM_OTHER_WAIT_TIME)), DECODE(MA.SUM_FETCHES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MA.SUM_FETCHES)), DECODE(MA.SUM_BUFFER_GETS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MA.SUM_BUFFER_GETS)), DECODE(MA.SUM_READ_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MA.SUM_READ_REQS)), DECODE(MA.SUM_REA D_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MA.SUM_READ_BYTES)), DECODE(MA.SUM_WRITE_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MA.SUM_WRITE_REQS)), DECODE(MA.SUM_WRITE_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MA.SUM_WRITE_BYTES)), CASE WHEN MA.SUM_IO_INTER_BYTES IS NULL OR MA.SUM_IO_INTER_BYTES = MA.SUM_IO_BYTES OR MA.SUM_IO_BYTES = 0 OR NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MA.SUM_IO_BYTES / DECODE(MA.SUM_IO_INTER_BYTES, 0, 1, MA.SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 OR MA.SUM_IO_BYTES = 0 OR MA.SUM_IO_UNC_BYTES = MA.SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND( 100 * (GREATEST(MA.SUM_IO_UNC_BYTES, MA.SUM_IO_ELIG_BYTES) - MA.SUM_IO_RET_BYTES) / MA.SUM_IO_ELIG_BYTES, 2))) END) ) ELSE XMLELEMENT( "sql", XMLATTRIBUTES( MA.MAX_SQL_ID AS "sql_id", TO_CHAR(MA.MAX_SQL_EXEC_START, :B12 ) AS "sql_exec_start", MA.MAX_SQL_EXEC_ID AS "sql_exec_id"), XMLFOREST(MA.MAX_STATUS AS "status", SUBSTR(MA.SQLMON_TEXT, 1, 100) AS "sql_text", TO_CHAR(MA.MIN_FIRST_REFRESH_TIME, :B12 ) AS "first_refresh_time", TO_CHAR(MA.MAX_LAST_REFRESH_TIME, :B12 ) AS "last_refresh_time", MA.SUM_REFRESH_COUNT AS "refresh_count", MA.MAX_INST_ID AS "inst_id", MA.MAX_SESSION_ID AS "session_id", MA.MAX_SESSION_SERIAL AS "session_serial", MA.MAX_USERID AS "user_id", MA.MAX_USERNAME AS "user", MA.MAX_CON_ID AS "con_id", MA.MAX_CON_NAME AS "con_name", MA.MAX_MODULE AS "module", MA.MAX_ACTION AS "action", MA.MAX_SERVICE_NAME AS "service", MA.MAX_CLIENT_ID AS "client_id", MA.MAX_CLIENT_INFO AS "client_info", MA.MAX_PROGRAM AS "program", MA.MAX_PLAN_HASH_VALUE AS "plan_hash", MA.MAX_PL_ENTRY_OID AS "plsql_entry_object_id", MA.MAX_PL_ENTRY_PROGID AS "plsql_entry_subprogram_id", MA.MAX_PL_OID AS "plsql_object_id", MA.MAX_PL_PROGID AS "plsql_subprogram_id", MA.MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MA.MAX_PX_DOP AS "dop", MA.MAX_PX_DOP_INSTANCES AS "instances", MA.SUM_SERVERS_REQUESTED AS "px_servers_requested", MA.SUM_SERVERS_ALLOCATED AS "px_servers_allocated"), XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), GREATEST(ROUND((MA.MAX_LAST_REFRESH_TIME- MA.MAX_SQL_EXEC_START) * 86400), LEAST(MA.SUM_ELA PSED_TIME/1000000, 1), CEIL(MA.MAX_QUEUING_TIME/1000000))), DECODE(MA.SUM_ELAPSED_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MA.SUM_ELAPSED_TIME)), DECODE(MA.MAX_QUEUING_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MA.MAX_QUEUING_TIME)), DECODE(MA.SUM_CPU_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MA.SUM_CPU_TIME)), DECODE(MA.SUM_USER_IO_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MA.SUM_USER_IO_WAIT_TIME)), DECODE(MA.SUM_APPLICATION_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MA.SUM_APPLICATION_WAIT_TIME)), DECODE(MA.SUM_CONCURRENCY_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MA.SUM_CONCURRENCY_WAIT_TIME)), DECODE(MA.SUM_CLUSTER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MA.SUM_CLUSTER_WAIT_TIME)), DECODE(MA.SUM_PLSQL_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MA.SUM_PLSQL_EXEC_TIME)), DECODE(MA.SUM_JAVA_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MA.SUM_JAVA_EXEC_TIME)), DECODE(MA.SUM_OTHER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), MA.SUM_OTHER_WAIT_TIME)), DECODE(MA.SUM_FETCHES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MA.SUM_FETCHES)), DECODE(MA.SUM_BUFFER_GETS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MA.SUM_BUFFER_GETS)), DECODE(MA.SUM_READ_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('rea d_reqs' AS "name"), MA.SUM_READ_REQS)), DECODE(MA.SUM_READ_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MA.SUM_READ_BYTES)), DECODE(MA.SUM_WRITE_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MA.SUM_WRITE_REQS)), DECODE(MA.SUM_WRITE_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MA.SUM_WRITE_BYTES)), DECODE(MA.SUM_IO_UNC_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MA.SUM_IO_UNC_BYTES)), DECODE(MA.SUM_IO_ELIG_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MA.SUM_IO_ELIG_BYTES)), DECODE(MA.SUM_IO_RET_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MA.SUM_IO_RET_BYTES)), CASE WHEN MA.SUM_IO_INTER_BYTES IS NULL OR MA.SUM_IO_INTER_BYTES = MA.SUM_IO_BYTES OR M A.SUM_IO_BYTES = 0 OR NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MA.SUM_IO_BYTES / DECODE(MA.SUM_IO_INTER_BYTES, 0, 1, MA.SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 OR MA.SUM_IO_BYTES = 0 OR MA.SUM_IO_UNC_BYTES = MA.SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100 * (GREATEST(MA.SUM_IO_UNC_BYTES, MA.SUM_IO_ELIG_BYTES) - MA.SUM_IO_RET_BYTES) / MA.SUM_IO_ELIG_BYTES, 2))) END) ) END) FROM MONITOR_AGG MA) ELSE NULL END) FROM (SELECT CASE WHEN V1.XPLAN_XML IS NULL OR V1.XPLAN_XML.EXISTSNODE('/error') > 0 THEN NULL ELSE V1.XPLAN_XML END XPLAN_XML FROM (SELECT CASE WHEN :B36 = 1 THEN DBMS_XPLAN.BUILD_PLAN_XML( TABLE_NAME=>'gv$all_sql_plan', PLAN_TAG=>'plan', FILTER_PREDS=>:B35 , FORMAT=>'-PROJECTION +ALIAS +ADAPTIVE') ELSE NULL END XP LAN_XML FROM DUAL) V1) CONST_VIEW
10b80qrp01xs6	select c.customer_id, c.customernumber, c.name, dep.department_id, dep.departmentnumber, dep.description, dp.distributionpoint_id, dp.distributionpointnumber, dp.remarkondeliverynote, dp.remarkonpackingnote, delp.deliverypoint_id, delp.code, delp.description, ds.planneddeliverydate, p.product_id, p.code, (select DESCRIPTION from product_DESC where product_DESC.product_ID=p.product_id and LANGUAGE_ID = :1 ) proddesc, sd.sizedefinition_id, sd.code, fm.finishingmethod_id, fm.code, case when NVL(UI.UNIQUEITEM_ID, 0) = 0 then RI.RAGGEDITEM_ID else UI.UNIQUEITEM_ID end, case when UI.PRIMARYID is null then RI.PRIMARYID else UI.PRIMARYID end, p.packingsequence, case when UI.CUSTOMEROWNED is null then RI.CUSTOMEROWNED else UI.CUSTOMEROWNED end, ds.outscannodelivery, ds.chargewashing, ds.fromstock, ds.scantimestamp, ds.businessunit_id, ds.packingnote_id from (select d.distributionpoint_link_id, d.customer_link_id, d.deliverypoint_link_id, d.packingnote_id, d.product_link_id, d.uniqueitem_link_id, d.sizedefinition_link_id, d.finishingmethod_id, d.outscannodelivery, d.chargewashing, d.fromstock, d.scantimestamp, d.businessunit_id, nvl(rc2.drivingdate, d.planneddeliverydate) planneddeliverydate from deliveryscan d left outer join routecontrol rc2 on rc2.routecontrol_id = d.routecontrol_id where nvl(d.packingnote_id, 0) = 0 and d.businessunit_id = :2 and (:3 = (select PD.PLANT_ID from PLANTDEPARTMENT PD where PD.PLANTDEPARTMENT_ID = d.plantdepartment_id))) ds join distributionpoint dp on dp.distributionpoint_id = ds.distributionpoint_link_id join department dep on dep.department_id = dp.department_id join deliverypoint delp on delp.deliverypoint_id = ds.deliverypoint_link_id join customer c on c.customer_id = ds.customer_link_id join product p on p.product_id = ds.product_link_id left outer join UNIQUEITEM UI on UI.UNIQUEITEM_ID = DS.UNIQUEITEM_LINK_ID left outer join RAGGEDITEM RI on RI.RAGGEDITEM_ID = DS.UNIQUEITEM_LINK_ID left outer join secondaryuniqueitem sui on sui.uniqueitem_id = ds.uniqueitem_link_id join sizedefinition sd on sd.sizedefinition_id = ds.sizedefinition_link_id join finishingmethod fm on fm.finishingmethod_id = ds.finishingmethod_id where c.customer_id = :4 and ds.distributionpoint_link_id = :5 and dep.department_id = :6 and ds.deliverypoint_link_id in (:7 , :8 ) order by c.customer_id, dep.department_id, dp.distributionpoint_id, delp.deliverypoint_id for update of ds.BUSINESSUNIT_ID, ds.UNIQUEITEM_LINK_ID
121ffmrc95v7g	select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), nvl(i.evaledition#, 1), nvl(i.unusablebefore#, 0), nvl(i.unusablebeginning#, 0), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(intcols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.e nabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
1406mcj0yw9fp	select WEMP.WEAREREMPLOYMENT_ID, WEMP.SEQUENCENUMBER, W.WEARER_ID, W.WEARERNUMBER, W.FULLNAME, W.SEARCHNAME, W.FIRSTNAME, W.EMBLEMNAME, W.CUSTOMEREMPLOYEENUMBER, W.SEX, W.MYGENDER, W.DUMMYFORPOOL, W.DATEACTIVE, W.DATEINACTIVE, W.garmentsbudgetchangedate, w.customer_id, WEMP.DISTRIBUTIONPOINT_ID, W.FLAG_ID, W.FLAGSTARTDATE, DIP.DISTRIBUTIONPOINTNUMBER, DIP.DESCRIPTION, DIP.DEPARTMENT_ID, DEP.DEPARTMENTNUMBER, DEP.DESCRIPTION, WEMP.WEARERFUNCTION_ID, WF.CODE, WF.DESCRIPTION, WEMP.LOCKER_ID, L.BANK, L.LOCKER, WEMP.EXTERNALLOCKER_ID, (select ll.bank from locker ll where ll.locker_id = WEMP.EXTERNALLOCKER_ID), (select ll.locker from locker ll where ll.locker_id = WEMP.EXTERNALLOCKER_ID), WEMP.DELIVERYPOINT_ID, L.DELIVERYPOINT_ID, GD.DELIVERYPOINT_ID, DIP.COSTCENTER_ID , CC.CODE, CC.DESCRIPTION , GD.GARMENTDISPENSER_ID, GD.CODE, GD.DESCRIPTION, F.CODE, COALESCE((select DESCRIPTION from FLAG_DESC where FLAG_DESC.FLAG_ID=WEMP.FLAG_ID and LANGUAGE_ID = :1 ), (select DESCRIPTION from FLAG_DESC where FLAG_DESC.FLAG_ID=WEMP.FLAG_ID and LANGUAGE_ID = :2 )), WEMP.dispensecredit, WEMP.cardnumber, w.remark , (SELECT count(1) FROM uniqueitemnonpool uin WHERE uin.weareremployment_id = WEMP.Weareremployment_Id) , (SELECT count(1) FROM nonuniqueitem nui, wearerinventory wi WHERE nui.wearerinventory_id = wi.wearerinventory_id AND wi.weareremployment_id = WEMP.Weareremployment_Id) from WEAREREMPLOYMENT WEMP LEFT OUTER JOIN WEARERFUNCTION WF ON WEMP.WEARERFUNCTION_ID = WF.WEARERFUNCTION_ID LEFT OUTER JOIN LOCKER L ON WEMP.LOCKER_ID = L.LOCKER_ID LEFT OUTER JOIN GARMENTDISPENSER GD ON WEMP.GARMENTDISPENSER_ID = GD.GARMENTDISPENSER_ID LEFT OUTER JOIN FLAG F ON F.FLAG_ID = WEMP.FLAG_ID, WEARER W, DISTRIBUTIONPOINT DIP, DEPARTMENT DEP, DELIVERYPOINT DP, COSTCENTER CC where WEMP.WEARER_ID = W.WEARER_ID and WEMP.DISTRIBUTIONPOINT_ID = DIP.DISTRIBUTIONPOINT_ID and DIP.DEPARTMENT_ID = DEP.DEPARTMENT_ID AND DP.DELIVERYPOINT_ID = DIP.DELIVERYPOINT_ID AND CC.COSTCENTER_ID = DIP.COSTCENTER_ID AND ( NOT EXISTS (SELECT 1 FROM WEBUSERDEPARTMENT WDEP, WEBUSERCUSTOMER WCUST WHERE WCUST.WEBUSER_ID = :3 AND WDEP.WEBUSERCUSTOMER_ID = WCUST.WEBUSERCUSTOMER_ID AND WCUST.CUSTOMER_ID = W.CUSTOMER_ID) OR EXISTS (SELECT 1 FROM WEBUSERDEPARTMENT WDEP, WEBUSERCUSTOMER WCUST WHERE WDEP.DEPARTMENT_ID = DEP.DEPARTMENT_ID AND WCUST.WEBUSER_ID = :4 AND WCUST.CUSTOMER_ID = DEP.CUSTOMER_ID AND WDEP.WEBUSERCUSTOMER_ID = WCUST.WEBUSERCUSTOMER_ID) ) AND ( W.DATEACTIVE <= :5 AND :6 < W.DATEINACTIVE) AND W.CUSTOMER_ID = :7
19k26a29jcn8s	select STOP.STOP_ID, STOP.STOPNUMBER, STOP.SERVICES, STOP.STATUS, STOP.ROUTESTOP_ID, ROUTESTOP.SOILTICKETS, STOP.ROUTECONTROL_ID, ROUTECONTROL.POCKETPCSTATUS, ROUTECONTROL.INVOICESGENERATED, (select RI.TRUCKLOADSTATUS from ROUTECONTROLEXTRAINFO RI where RI.ROUTECONTROL_ID = ROUTECONTROL.ROUTECONTROL_ID), ROUTE.ROUTE_ID, ROUTE.ROUTENUMBER, ROUTE.DESCRIPTION, ROUTE.PACKINGDEADLINE from STOP left outer join ROUTESTOP on ROUTESTOP.ROUTESTOP_ID = STOP.ROUTESTOP_ID, ROUTECONTROL, ROUTE where STOP.ROUTECONTROL_ID = ROUTECONTROL.ROUTECONTROL_ID and ROUTECONTROL.ROUTE_ID = ROUTE.ROUTE_ID and ROUTECONTROL.DRIVINGDATE = :1 and STOP.CUSTOMER_LINK_ID = :2 and nvl(STOP.ROUTESTOP_ID, 0) = 0 and (STOP.STATUS = 0 or STOP.STATUS = 3)
1ku0h1q91uq6y	SELECT "PLANT_ID", "SCANTYPE", "WSCODE", "WSDESC", "LASTIN", "QTYHOUR", "QTYDAY" FROM "ABSSOLUTE"."V_DWH_RTV_8" "RTV"
1s16tnxs998vt	select PRODUCTLISTPERCUSTOMER_ID, CUSTOMCODE, CUSTOMDESCRIPTION, IMAGECARE, SPECIFICPUTUPTYPE, MINQTYTODELIVER, MAXQTYTODELIVER, TREATMENTCHECKSCHEME_ID, FINISHINGMETHOD_ID, RAGGEDITEMACTION, CHARGEEXCESS, BM4ALLOWSALES, NOTCHARGEOUTSTANDINGLOANS, STRAIGHTEVENEXCHANGE, MAXWASHES, DELIVERYLABELLAYOUT_ID, DELAYEDEVENEXCHANGE, WASHINGPROCESS_ID, DEEDELIVERYSCHEME_ID, RENTBUSINESSUNIT_ID, CODEUNIQUELY, EMBLEMTEMPLATE_ID, SRCMETHOD, SRCPERCENTAGEBILLEDQTY, SRCPERCENTAGESOILCOUNT, SRCPERCENTAGEINVENTORY, BILLINGMETHOD , SOILCOUNTMETHOD, DATEACTIVE, DATEINACTIVE, RESIDUALVALUESCHEME_ID, ALLOWPREWASH, MINRESIDUALVALUEAMOUNT, CUSTOMER_ID, SIZEDEFINITION_ID, CHARGEWASHESONLYFORCOG, SOILWEIGHT, MAXIMUMNUMBEROFREPAIRS, PRICEUSAGE, WEARERGARMENTSBUDGETVALUE, STERILISATIONREQUIRED, TREATMENTSPERSUBCONTRACTOR_ID, ALLOWTOREP LACE, ALLOWRETURNTOSTOCK, CHANGESPERWEEK, BM1RENTCPWMAX, DEFAULTRETURNGRADE_ID from PRODUCTLISTPERCUSTOMER where CUSTOMER_ID = :1 and PRODUCT_ID = :2 and SIZEDEFINITION_ID = :3 and FINISHINGMETHOD_ID = :4 ORDER BY DATEACTIVE desc
1xf46m3chyan9	select * from (select dynamicsql.*, rownum rnum from ( SELECT WEARER.WEARER_ID, WEARER.WEARERNUMBER, WEARER.SEARCHNAME WEARERSEARCHNAME, WEARER.FULLNAME, WEARER.EMBLEMNAME, WEARER.CUSTOMEREMPLOYEENUMBER, WEARER.SEX, WEARER.DUMMYFORPOOL, WEARER.DATEACTIVE, WEARER.DATEINACTIVE, WEARER.REMARK, WEARER.FIRSTNAME, WEARER.MYGENDER, WEARER.SYSTEMUSER_ID WEARERSYSTEMUSERID, WEARER.TIMESTAMP WEARERTIMESTAMP, WEARER.FLAG_ID, WEARERFLAG.CODE WEARERFLAGCODE, (select FLAG_DESC.DESCRIPTION from FLAG_DESC where FLAG_DESC.FLAG_ID = WEARER.FLAG_ID and FLAG_DESC.LANGUAGE_ID = :1 ) FLAGDESCRIPTION, WEAREREMPLOYMENT.WEAREREMPLOYMENT_ID, WEAREREMPLOYMENT.SEQUENCENUMBER, DEPARTMENT.DEPARTMENT_ID, DEPARTMENT.DEPARTMENTNUMBER, DEPARTMENT.DESCRIPTION DEPARTMENTDESCRIPTION, LOCKER.LOCKER_ID, LOCKER.BANK, LOCKER.LOCKER, DP2.DELIVERYPOINT_ID DP2DELIVERYPOINT_ID, DP2.CODE DP2CODE, DP2.DESCRIPTION DP2DESCRIPTION, GARMENTDISPENSER.GARMENTDISPENSER_ID, GARMENTDISPENSER.CODE GARMENTDISPENSERCODE, GARMENTDISPENSER.DESCRIPTION GARMENTDISPENSERDESCRIPTION, DP3.DELIVERYPOINT_ID DP3DELIVERYPOINT_ID, DP3.CODE DP3CODE, DP3.DESCRIPTION DP3DESCRIPTION, WEARERFUNCTION.WEARERFUNCTION_ID, WEARERFUNCTION.CODE WEARERFUNCTIONCODE, WEARERFUNCTION.DESCRIPTION WEARERFUNCTIONDESCRIPTION, WEAREREMPLOYMENT.DISTRIBUTIONPOINT_ID, DISTRIBUTIONPOINT.DISTRIBUTIONPOINTNUMBER, DISTRIBUTIONPOINT.DESCRIPTION DISTRIBUTIONPOINTDESCRIPTION, WEAREREMPLOYMENT.SYSTEMUSER_ID, WEAREREMPLOYMENT.TIMESTAMP, WEAREREMPLOYMENT.DELIVERYPOINT_ID, DELIVERYPOINT.CODE DELIVERYPOINTCODE, DELIVERYPOINT.DESCRIPTION DELIVERYPOINTDESCRIPTION, WEAREREMPLOYMENT.DISPENSECREDIT, WEAREREMPLOYMENT.CARDNUMBER, CC.COSTCENTER_ID CCCOSTCENTER_ID, CC.CODE CCCODE, CC.DESCRIPTION CCDESCRIPTION, WEFL.FLAG_ID WEFLFLAG_ID, WEFL.CODE WEFLCODE, WEFL.TRANSFER WEFLTRANSFER, AUTOFL.AUTOMATICFLAG_ID, AUTOFL.TRANSFERABLE, WEAREREMPLOYMENT.EXTERNALLOCKER_ID, EXTERNALLOCKER.BANK EXTERNALLOCKERBANK, EXTERNALLOCKER.LOCKER EXTERNALLOCKERLOCKER, WEARERFUNCTION.CODELABELLAYOUT_ID, WEARERFUNCTION.NAMELABELLAYOUT_ID, WEAREREMPLOYMENT.WEARERFUNCTIONSUBTYPE_ID from WEAREREMPLOYMENT left outer join (LOCKER join DELIVERYPOINT DP2 on LOCKER.DELIVERYPOINT_ID = DP2.DELIVERYPOINT_ID) on LOCKER.LOCKER_ID = WEAREREMPLOYMENT.LOCKER_ID left outer join LOCKER EXTERNALLOCKER on EXTERNALLOCKER.LOCKER_ID = WEAREREMPLOYMENT.EXTERNALLOCKER_ID left outer join (GARMENTDISPENSER left outer join DELIVERYPOINT DP3 on GARMENTDISPENSER.DELIVERYPOINT_ID = DP3.DELIVERYPOINT_ID) on GARMENTDISPENSER.GARMENTDISPENSER_ID = WEAREREMPLOYMENT.GARMENTDISPENSER_ID left outer join WEARERFUNCTION on WEARERFUNCTION.WEARERFUNCTION_ID = WEAREREMPLOYMENT.WEARERFUNCTION_ID left outer join DELIVERYPOINT on DELIVERYPOINT.DELIVERYPOINT_ID = WEAREREMPLOYMENT.DELIVERYPOINT_ID left outer join (FLAG WEFL left outer join AUTOMATICFLAG AUTOFL on WEFL.FLAG_ID = AUTOFL.FLAG_ID) on WEFL.FLAG_ID = WEAREREMPLOYMENT.FLAG_ID , WEARER left outer join FLAG WEARERFLAG on WEARERFLAG.FLAG_ID = WEARER.FLAG_ID , DEPARTMENT, DISTRIBUTIONPOINT, CUSTOMER, COSTCENTER CC where WEAREREMPLOYMENT.WEARER_ID = WEARER.WEARER_ID and WEAREREMPLOYMENT.DISTRIBUTIONPOINT_ID = DISTRIBUTIONPOINT.DISTRIBUTIONPOINT_ID and DISTRIBUTIONPOINT.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID and DEPARTMENT.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID and CC.COSTCENTER_ID = DISTRIBUTIONPOINT.COSTCENTER_ID and WEARER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID and CC.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID and :2 between WEARER.DATEACTIVE and WEARER.DATEINACTIVE and WEARER.CUSTOMER_ID = :3 order by WEARER.WEARERNUMBER ) dynamicsql where rownum <= :4 ) where rnum >= :5
28fx359c3sw6m	select count(1) from SCAN S where S.BUSINESSUNIT_ID = :1 and S.SCANTIMESTAMP >= :2 and S.SCANTIMESTAMP < :3 and S.SCANSTATION = :4 and S.STAY_ID = :5 and S.STATUS_ID <> :6
2bpva66y95xkv	select NAME, VALUE, TYPEOFVALUE, USEDASDEFAULT, GROUPNAME from SYSTEMSETTING SS where SS.PLANT_ID = :1
2n91shk1kxasz	insert into SCAN (scan_id, businessunit_id, scantimestamp, primaryid, uniqueitem_link_id, customer_link_id , weareremployment_link_id, distributionpoint_link_id, product_link_id, sizedefinition_link_id, stay_id, status_id , scanstation, issuedate, route_id, reasoncode_id, customerowned, year , week, dayno, qualitygrade_id, prevstay_id, transactiontype_id, secondaryid , dayslate, systemuser_id, timestamp, batchnumber, scandateoffset, qualitytesttype_id, washingprocess_id, treatmenttype_id, deliverynotenumber) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 )
2tgcfby35j5mh	BEGIN :1 := pack_multiread.f_domultireadoutscanpoolbatch(:2 , :3 , :4 , :5 , :6 ); END;
348vn78sggs27	CALL ABSSOLUTE.SENDTRACKINFO(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19)
39bftv6ycng1t	select primaryid, customernumber, customername, clientnumber, clientname, lastoutnote, prod_desc, id_qty from v_cdn_stackcheck where primaryid = :id
3gkxyuz608qwk	SELECT A.INVOICEHEADER_ID, A.INVOICEDATE, A.INVOICENUMBER, A.DOCUMENTTYPE, A.INVOICEBUSINESSUNIT_ID, A.BILLTOCUSTOMER_LINK_ID, A.DELTOCUSTOMER_LINK_ID, A.ENDWEEK, A.STARTWEEK, A.ENDMONTH, A.STARTMONTH, A.COUNTY, A.STATE, A.CITY, A.ZIPCODE, A.STREET2, A.STREET, A.DELTOCUSTOMERNAME, A.DELTOCUSTOMERNUMBER, A.BILLTOCUSTOMERNAME, A.BILLTOCUSTOMERNUMBER, A.ROUTECONTROL_ID, A.ROUTE_ID, A.CONTACTPERSONPHONE, A.EMAILSTATUS, A.EMAILADDRESS, A.CONTACTPERSONNAME, A.STOPNUMBER, A.STATUS_ID, A.REALCOLLECTIONTYPE, A.ORIGINALCOLLECTIONTYPE, A.INVOICETYPE, A.ADJUSTMENTREASON_ID, A.EXPORTED, A.PURCHASEORDER, A.DELIVERINSTRUCTIONS, A.INVOICECOMMENTS, A.TOTALMINUSADJUSTMENT, A.TOTALPLUSADJUSTMENT, A.CASHCOLLECTED, A.PAYTHISAMOUNT, A.PREBILLAMOUNT, A.INVOICEFREQUENCY_ID, A.SYSTEMUSER_ID, A.TIMESTAMP, A.CURRENCY_ID, 1 , CURRENCY.INVOIC EAMOUNTDECIMALS, A.STARTYEAR, A.ENDYEAR, A.CONTRACTCODE, A.DEPOTCODE, 'Y', CURRENCY.CODE, BUSINESSUNIT.CODE, BUSINESSUNIT.DESCRIPTION, INVOICEFREQUENCYNUMBER, (select DESCRIPTION from INVOICEFREQUENCY_DESC where INVOICEFREQUENCY_DESC.INVOICEFREQUENCY_ID=A.INVOICEFREQUENCY_ID and LANGUAGE_ID = 1), A.TOTALAMOUNTEXCLVAT, A.CREDITSQUEEZEAMOUNT, A.CREDITSQUEEZEPERC, A.CONVERTEDFROMLEGACY, A.STOP_ID, A.SPLITUPCODE, A.SPLITUPDESCRIPTION, A.STARTDATE, A.ENDDATE, A.PARENTINVOICEHEADER_ID, A.CONTRACTSTARTDATE, A.CONTRACTENDDATE, A.CREDITED, A.NEXTPERIODSTARTDATE, A.NEXTPERIODENDDATE, A.NEXTPERIODNOWEEKS, A.STARTDATE, A.ENDDATE, VDELTOCUST.ACTIVE, A.CREDITEDINVOICENO, A.NEXTPERIODSTARTWEEK, A.NEXTPERIODENDWEEK, VDELTOCUST.SERVICETEAM_ID, VDELTOCUST.STCODE, VDELTOCUST.STDESCRIPTION, VDELTOCUST.DEFAULTBUSINESSUNIT_ID, VDELTOCUST.DEFBUCODE, VDELTOCUST.DEFBUDESCRIPTION, A.containresidentialservice, A.HOUSENUMBER, A.HOUSENUMBEREXT, (case when A.CLUS TER_ID is null then (select CPC.CLUSTER_ID from CUSTOMERSPERCLUSTER CPC, CUSTOMERCLUSTER C where C.INVOICEFREQUENCY_ID = A.INVOICEFREQUENCY_ID and CPC.CLUSTER_ID = C.CLUSTER_ID and CPC.CUSTOMER_ID = C1.CUSTOMER_ID and rownum <= 1) else A.CLUSTER_ID end), (case when A.CLUSTER_ID is null then (select C.CODE from CUSTOMERSPERCLUSTER CPC, CUSTOMERCLUSTER C where C.INVOICEFREQUENCY_ID = A.INVOICEFREQUENCY_ID and CPC.CLUSTER_ID = C.CLUSTER_ID and CPC.CUSTOMER_ID = C1.CUSTOMER_ID and rownum <= 1) else CC.CODE end), VDELTOCUST.COUNTRY_ID, A.ADDTOCONSOLIDATEDINV , (SELECT DECODE ( (SELECT COUNT(*) FROM INVOICELINE IL WHERE IL.INVOICEHEADER_ID = IH.INVOICEHEADER_ID AND IL.PRICE IS NULL), 0, 'N', 'Y') FROM INVOICEHEADER IH WHERE IH.INVOICEHEADER_ID = A.INVOICEHEADER_ID) MISSINGPRICES , CR.CLIENT_ID, INVOICEHEADERCLIENT.CLIENT_LINK_ID, INVOICEHEADERCLIENT.CLIENTNUMBER, INVOICEHEADERCLIENT.NAME, INVOICEHEADERCLIENT.ROOMNUMBER, INVOICEHEADERCLIENT.SENDINVOICETO, INVOICEHEADERCLIENT.CLIENTTYPE, INVOICEHEADERCLIENT.PAIDDATE, INVOICEHEADERCLIENT.CLIENTINVOICETYPE, CLIENT.DATEINACTIVE, CLIENT.FLAG_ID, CLIENT.REMARK, CR.DECEASEDDATE , A.PAIDAMOUNT, A.CREDITEDINVOICEHEADER_ID, cbi.consolidatedinvoice, A.UNDERUSEAMOUNT , A.INVOICEJOBSCHEDULE_ID, A.PRINTDATE , IHE.PAYMENTTERMSDUEDATE, IHE.PAYMENTTERMSDESCRIPTION, RC.CODE, (select DESCRIPTION from REASONCODE_DESC where REASONCODE_DESC.REASONCODE_ID=A.ADJUSTMENTREASON_ID and LANGUAGE_ID = 1) , CBI.PRINTINVOICE, FL.CODE, IHE.Formlayoutcode, (select PIHE.Formlayoutcode from invoiceheaderextrainfo PIHE where PIHE.INVOICEHEADER_ID = A.PARENTINVOICEHEA DER_ID), VDELTOCUST.PLANTCODE, VDELTOCUST.PLANTNAME , A.creationtimestamp, A.creationsystemuser_id, A.finalizationtimestamp, A.finalizationsystemuser_id, A.internalremark, A.showonweb, A.showonwebsystemuser_id, A.showonwebtimestamp, VDELTOCUST.MACCUSTNR, VDELTOCUST.MACSEARCHNAME, VDELTOCUST.CHCODE, VDELTOCUST.SEARCHNAME, C1.SEARCHNAME, IHEDI.EDICONTACTPERSON_ID, A.ORIGINALINVOICEDATE, VDELTOCUST.POREQUIRED, A.SETTLEMENTREJECTREASON, A.SPLITLEVEL, A.STREET3, A.DELTOSTREET3, (case when A.TOTALPLUSADJUSTMENT - A.TOTALMINUSADJUSTMENT <> 0 then (select sum(IL.ADJUSTEDAMOUNT) from INVOICELINE IL where IL.INVOICEHEADER_ID = A.INVOICEHEADER_ID and IL.INVOICELINETYPE_ID = 18 group by IL.INVOICEHEADER_ID, IL.INVOICELINETYPE_ID) else (select sum(IL.AMOUNT) from INVOICELINE IL where IL.INVOICEHEADER_ID = A.INVOICEHEADER_ID and IL.INVOICELINETYPE_ID = 18 group by IL.INVOICEHEADER_ID, IL.INVOICELINETYPE_ID) end), A.PURCHASEORDER FROM INVOICEHEADER A left outer join ROUTE on A.ROUTE_ID = ROUTE.ROUTE_ID left outer join CUSTOMERCLUSTER CC on A.CLUSTER_ID = CC.CLUSTER_ID join CUSTOMER C1 on A.BILLTOCUSTOMER_LINK_ID = C1.CUSTOMER_ID left outer join CUSTOMERBILLINGINFO cbi on C1.CUSTOMER_ID = cbi.CUSTOMER_ID left outer join FORMLAYOUT FL on FL.FORMLAYOUT_ID = cbi.CONSOLIDATEDINVOICELAYOUT_ID left outer join INVOICEHEADEREXTRAINFO IHE on IHE.INVOICEHEADER_ID = A.INVOICEHEADER_ID left outer join INVOICEHEADEREDIINFO IHEDI on IHEDI.INVOICEHEADER_ID = A.INVOICEHEADER_ID left outer join REASONCODE RC on RC.REASONCODE_ID = A.ADJUSTMENTREASON_ID LEFT OUTER JOIN (SELECT PB.CUSTOMER_ID, PB.CUSTOMERNUMBER, PB.NAME, PB.SEARCHNAME FROM CUSTOMER PB) VPAYBY ON IHE.PAYBYCUSTOMER_LINK_ID = VP AYBY.CUSTOMER_ID LEFT OUTER JOIN (SELECT DELTO.CUSTOMER_ID CUST_ID, DELTO.CUSTOMERNUMBER, DELTO.SEARCHNAME, DELTO.CHAIN_ID, DELTO.POREQUIRED, DELTO.COUNTRY_ID, DELTO.ACTIVE, DELTO.DEFAULTBUSINESSUNIT_ID, DELTO.SERVICETEAM_ID, CH.CODE CHCODE, CH.NAME CHNAME, MAC.CUSTOMER_ID MACCUSTID, MAC.CUSTOMERNUMBER MACCUSTNR, MAC.NAME MACCUSTNAME, MAC.SEARCHNAME MACSEARCHNAME, COMMONCUST.CUSTOMER_ID COMMONCUSTID, COMMONCUST.CUSTOMERNUMBER COMMONCUSTNR, COMMONCUST.NAME COMMONCUSTNAME, COMMONCUST.SEARCHNAME COMMONSEARCHNAME, P.CODE PLANTCODE, P.NAME PLANTNAME, DEFBU.CODE DEFBUCODE, DEFBU.DESCRIPTION DEFBUDESCRIPTION, SERVICETEAM.CODE STCODE, SERVICETEAM.DESCRIPTION STDESCRIPTION FROM CUSTOMER DELTO LEFT OUTER JOIN CUSTOMeR MAC ON MAC.CUSTOMER_ID = DELTO.MASTERACCOUNT_ID LEFT OUTER JOIN CHAIN CH ON CH.CHAIN_ID = DELTO.CHAIN_ID LEFT OUTER JOIN CUSTOMER COMMONCUST ON COMMONCUST.CUSTOMER_ID = DELTO.COMMONCUSTOMER_ID LE FT OUTER JOIN SERVICETEAM ON SERVICETEAM.SERVICETEAM_ID = DELTO.SERVICETEAM_ID LEFT OUTER JOIN(BUSINESSUNIT DEFBU JOIN PLANT P ON P.PLANT_ID = DEFBU.PLANT_ID) ON DEFBU.BUSINESSUNIT_ID = DELTO.DEFAULTBUSINESSUNIT_ID) VDELTOCUST ON A.DELTOCUSTOMER_LINK_ID = VDELTOCUST.CUST_ID , STATUS, CURRENCY, BUSINESSUNIT, INVOICEFREQUENCY , INVOICEHEADERCLIENT left outer join (CLIENT left outer join CLIENTRESIDENT CR ON CR.CLIENT_ID = CLIENT.CLIENT_ID) on INVOICEHEADERCLIENT.CLIENT_LINK_ID = CLIENT.CLIENT_ID WHERE A.STATUS_ID = STATUS.STATUS_ID AND A.CURRENCY_ID = CURRENCY.CURRENCY_ID AND BUSINESSUNIT.BUSINESSUNIT_ID = A.INVOICEBUSINESSUNIT_ID AND INVOICEFREQUENCY.INVOICEFREQUENCY_ID = A.INVOICEFREQUENCY_ID AND INVOICEHEADERCLIENT.INVOICEHEADER_ID = A.INVOICEHEADER_ID AND TRUNC(A.INVOICEDATE) >= :1 AND STATUS.CODE = :2 AND STATUS.STATUSTYPE = :3 AND CLIENT.CLIENT_ID = :4 AND A.DELTOCU STOMER_LINK_ID = :5 AND A.DOCUMENTTYPE = 3 ORDER BY TRUNC(A.INVOICEDATE) ASC
3hd6n9rvd25ga	select CHARACTERISTIC_ID, language_id, description, systemuser_id, timestamp from CHARACTERISTIC_DESC where CHARACTERISTIC_ID = :1
3vf3ptyxfjh4j	INSERT INTO SCAN (SCAN_ID, BUSINESSUNIT_ID, SCANTIMESTAMP, PRIMARYID, CUSTOMER_LINK_ID, WEAREREMPLOYMENT_LINK_ID, DISTRIBUTIONPOINT_LINK_ID, PRODUCT_LINK_ID, SIZEDEFINITION_LINK_ID, STAY_ID, STATUS_ID, SCANSTATION, ISSUEDATE, ROUTE_ID, REASONCODE_ID, CUSTOMEROWNED, YEAR, WEEK, DAYNO, QUALITYGRADE_ID, SYSTEMUSER_ID, TIMESTAMP, PREVSTAY_ID, TRANSACTIONTYPE_ID, DAYSLATE, UNIQUEITEM_LINK_ID, SECONDARYID, SCANDATEOFFSET, DAYSSINCELASTINSCAN, DAYSSINCELASTOUTSCAN, DAYSSINCELASTSCAN, BATCHNUMBER ) SELECT MBC.SCAN_ID, MB.BUSINESSUNIT_ID, MBC.SCANTIMESTAMP, MBC.IDCODE, MBC.CUSTOMER_ID, CASE WHEN MBC.POOLITEMYN = 'N' THEN MBC.WEAREREMPLOYMENT_ID ELSE NULL END, NVL(MBC.DISTRIBUTIONPOINT_ID, MBC.PREV_DISTRIBUTIONPOINT_ID) DISTRIBUTIONPOINT_ID, MBC.PRODUCT_ID, MBC.SIZEDEFINITION_ID, CASE WHEN :B4 = 55 THEN :B7 ELSE :B6 END, :B5 , W.CODE, MBC.LASTISSUEDATE, MBC.LASTINROUTE_ID, NULL, MBC.CUSTOMEROWNED, GETYEAROFWEEK(TRUNC(MBC.SCANTIMESTAMP)), GETISOWEEK(TRUNC(MBC.SCANTIMESTAMP)), GETABSDAYNUMBER(TRUNC(MBC.SCANTIMESTAMP)), MBC.QUALITYGRADE_ID, MB.SYSTEMUSER_ID, SYSDATE, MBC.STAY_ID, :B4 , 0, MBC.UNIQUEITEM_ID, MBC.SECONDARYID, :B3 - TRUNC(MBC.SCANTIMESTAMP), TRUNC(MBC.SCANTIMESTAMP) - TRUNC(MBC.LASTINSCANDATE), TRUNC(MBC.SCANTIMESTAMP) - TRUNC(MBC.LASTOUTSCANDATE), TRUNC(MBC.SCANTIMESTAMP) - TRUNC(MBC.LASTSCANDATE), MBC.UNIQUEITEMBATCHNUMBER FROM MULTIREADSCANBATCH MB, MULTIREADSCANBATCHCONTENTS MBC, WORKSTATION W WHERE MB.MULTIREADSCANBATCH_ID = :B2 AND MBC.MULTIREADSCANBATCH_ID = MB.MULTIREADSCANBATCH_ID AND (MBC.SUBBATCHNUMBER = :B1 OR :B1 = 0) AND MBC.SCAN_ID IS NOT NULL AND MBC.MULTIREADEXCEPTION_ID IS NULL AND W.WORKSTATION_ID = MB.WORKSTATION_ID
4481rfxq7puw8	select C.CUSTOMER_ID, C.CUSTOMERNUMBER, C.NAME, DEP.DEPARTMENT_ID, DEP.DEPARTMENTNUMBER, DEP.DESCRIPTION, DP.DISTRIBUTIONPOINT_ID, DP.DISTRIBUTIONPOINTNUMBER, DP.DESCRIPTION, DELP.DELIVERYPOINT_ID, DELP.CODE, DELP.DESCRIPTION, ADR.ADDRESS_ID, ADR.STREET, ADR.ZIPCODE, ADR.CITY, TA.TRANSPORTARTICLE_ID, TA.CODE, (select DESCRIPTION from TRANSPORTARTICLE_DESC where TRANSPORTARTICLE_DESC.TRANSPORTARTICLE_ID=TA.TRANSPORTARTICLE_ID and LANGUAGE_ID = :1 ) TADESC, DS.PLANNEDDELIVERYDATE, DS.TOTALNUMBEROFPIECES, DS.TOTALNOTDELIVERED, DS.TOTALNOTDELIVEREDCOG from (select D.DISTRIBUTIONPOINT_LINK_ID, D.CUSTOMER_LINK_ID, D.DELIVERYPOINT_LINK_ ID, nvl(RC2.DRIVINGDATE, D.PLANNEDDELIVERYDATE) PLANNEDDELIVERYDATE , count(case when D.OUTSCANNODELIVERY in (0, 2) then 1 end) TOTALNUMBEROFPIECES , 0 TOTALNOTDELIVERED , 0 TOTALNOTDELIVEREDCOG from DELIVERYSCAN D left outer join ROUTECONTROL RC2 on RC2.ROUTECONTROL_ID = D.ROUTECONTROL_ID where nvl(D.PACKINGNOTE_ID, 0) = 0 and D.BUSINESSUNIT_ID = :2 and (:3 = (select PD.PLANT_ID from PLANTDEPARTMENT PD where PD.PLANTDEPARTMENT_ID = D.PLANTDEPARTMENT_ID)) group by D.DISTRIBUTIONPOINT_LINK_ID, D.CUSTOMER_LINK_ID, D.DELIVERYPOINT_LINK_ID, NVL(RC2.DRIVINGDATE, D.PLANNEDDELIVERYDATE)) DS join DISTRIBUTIONPOINT DP left outer join STANDARDDELIVERY STD join TRANSPORTARTICLE TA on STD.TRANSPORTARTICLE_ID = TA.TRANSPORTARTICLE_ID and STD.SEQUENCENUMBER = (select min(STD1.SEQUENCENUMBER) from STANDARDDELIVERY STD1 where STD1.DISTRIBUTIONPOINT_ID = STD.DISTRIBUTIONPOINT_ID) on DP.DISTRIBUTIONPOINT_ID = STD.DISTRIBUTIONPOINT_ID on DP.DISTRIBUTIONPOINT_ID = DS.DISTRIBUTIONPOINT_LINK_ID join DEPARTMENT DEP on DEP.DEPARTMENT_ID = DP.DEPARTMENT_ID join DELIVERYPOINT DELP join ADDRESS ADR on ADR.ADDRESS_ID = DELP.DELIVERYADDRESS_ID on DELP.DELIVERYPOINT_ID = DS.DELIVERYPOINT_LINK_ID join CUSTOMER C on C.CUSTOMER_ID = DS.CUSTOMER_LINK_ID where DS.CUSTOMER_LINK_ID = :4 order by C.CUSTOMER_ID, DEP.DEPARTMENT_ID, DP.DISTRIBUTIONPOINT_ID, DELP.DELIVERYPOINT_ID
4cbm1n74tbvrg	Begin ABSSOLUTE.SENDTRACKINFO(:v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10, :v11, :v12, :v13, :v14, :v15, :v16, :v17); End;
4tsnx2bu16u5p	select * from (select dynamicsql.*, rownum rnum from ( SELECT /*+index(address XPKADDRESS)*/ DISTINCT CUSTOMER.CUSTOMER_ID , CUSTOMER.CUSTOMERNUMBER custNumber, CUSTOMER.ACTIVE custActive, CUSTOMER.DATEINACTIVE custDateInactive, CUSTOMER.NAME custName, CUSTOMER.SEARCHNAME searchName, CUSTOMER.VISITADDRESS_ID , ADDRESS.STREET a_street , ADDRESS.ZIPCODE a_zipcode , ADDRESS.CITY a_city, CUSTOMER.MASTERACCOUNT_ID, B.CUSTOMERNUMBER masteraccountnumber, CUSTOMER.DEFAULTBUSINESSUNIT_ID, BU.CODE buCode, BU.DESCRIPTION buDesc from CUSTOMER , ADDRESS , CUSTOMER A, CUSTOMER B, BUSINESSUNIT BU where ADDRESS.ADDRESS_ID = CUSTOMER.VISITADDRESS_ID AND CUSTOMER.DEBTOR_ID = A.CUSTOMER_ID AND CUSTOMER.MASTERACCOUNT_ID = B.CUSTOMER_ID AND BU.BUSINESSUNIT_ID = CUSTOMER.DEFAULTBUSINESSUNIT_ID and UPPER(CUSTOMER.NAME) LIKE :1 AND (CUSTOMER.DE FAULTBUSINESSUNIT_ID = :2 or CUSTOMER.DEFAULTINVOICEBUSINESSUNIT_ID = :3 or CUSTOMER.CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMERACTIVITIES CA WHERE CA.INVOICEBUSINESSUNIT_ID = :4 OR CA.PROCESSBUSINESSUNIT_ID = :5 ) ) ORDER BY custNumber ) dynamicsql where rownum <= :6 ) where rnum >= :7
54auqz5nfryzf	insert into MULTIREADSCANBATCHCONTENTS (MULTIREADSCANBATCH_ID, SUBBATCHNUMBER, IDCODE, UNIQUEITEM_ID, MULTIREADEXCEPTION_ID, CUSTOMER_ID, PRODUCT_ID, SIZEDEFINITION_ID, SCANTIMESTAMP, POOLITEMYN, ORPACKITEMYN, STAY_ID, STATUS_ID, LASTSCANTIMESTAMP, FINISHINGMETHOD_ID, PLPC_ID, CUSTOMEROWNED, QUALITYGRADE_ID, LASTINROUTE_ID, UNRELATEDLOAN_ID, TYPEOFMERCHANDIZE, SECONDARYID, LASTISSUEDATE, SCAN_ID, WEAREREMPLOYMENT_ID, DISTRIBUTIONPOINT_ID, SYSTEMUSER_ID, UNIQUEITEMBATCHNUMBER, TIMEFRAMEINITIAL, TIMEFRAMEFINAL, IDCODEREAD) values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 )
59q74g98wjmxw	update CLIENTDELIVERYNOTE set PROCESSBUSINESSUNIT_ID = :1 , INVOICEBUSINESSUNIT_ID = :2 , CLIENTDELIVERYNOTENUMBER = :3 , BATCH_ID = :4 , SEQUENCENUMBER = :5 , CUSTOMER_LINK_ID = :6 , CLIENT_LINK_ID = :7 , NAME = :8 , STREET = :9 , ZIPCODE = :10 , CITY = :11 , STATE = :12 , ROOMNUMBER = :13 , ROUTE_ID = :14 , STOPNUMBER = :15 , ORIGIN = :16 , WEIGHINGCATEGORY_LINK_ID = :17 , TAREWEIGHT = :18 , GROSSWEIGHT = :19 , WEIGHTDECIMALS = :20 , WEIGHINGSEQUENCENUMBER = :21 , WEIGHINGTIMESTAMP = :22 , STARTINSCAN = :23 , STARTOUTSCAN = :24 , DELIVERYDATE = :25 , CREATIONDATE = :26 , CREATIONUSER_ID = :27 , CLOSINGDATE = :28 , CLOSINGUSER_ID = :29 , BOXNUMBER = :30 , SYSTEMUSER_ID = :31 , TIMESTAMP = :32 , REMARK = :33 , INVOICELINE_ID = :34 , INVOICEWEIGHT = :35 , WEIGHTPRICE = :36 , ADDITIONALTONOTE_ID = :37 , CORRECTIONCLI ENTDELNOTE_ID = :38 , STATUS = :39 , EMPTIED = :40 , PRESORTNUMBER = :41 , INTERNALPROCESSINGBU_ID = :42 , PACKINGNOTE_ID = :43 , MOVEDFROMCLIENT_LINK_ID = :44 , MOVEDDATE = :45 , STOP_ID = :46 , ROUTECONTROL_ID = :47 , ORIGINALDELIVERYDATE = :48 where CLIENTDELIVERYNOTE_ID = :49
5kdfrn9w88pms	select d.countdate, (select dd.description from day_desc dd where dd.day_id = scsd.day_id and dd.language_id = :1 ), d.countdate + scsd.deliveryday_id - scsd.day_id, (select dd.description from day_desc dd where dd.day_id = scsd.deliveryday_id and dd.language_id = :2 ), scs.code, scs.description, scs.stockcountschedule_id, scsd.day_id, scsd.compulsoryorderday, (select count(1) from stockcountscheduledist scsdpt, distributionpoint dip, department dpt where dip.distributionpoint_id = scsdpt.distributionpoint_id and dpt.department_id = dip.department_id and dpt.customer_id = scs.customer_id and scsdpt.stockcountschedule_id = scs.stockcountschedule_id and dpt.active = 'Y' and dip.active = 'Y' and exists (select 1 from productlistperd istribpoint plpd where plpd.distributionpoint_id = dip.distributionpoint_id)) diptstobecounted, (select count(1) from internetorder io, stockcountscheduledist scsdip where scsdip.distributionpoint_id = io.distributionpoint_link_id and io.status = 1 and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) diptsnotcounted, (select count(1) from internetorder io, stockcountscheduledist scsdip where scsdip.distributionpoint_id = io.distributionpoint_link_id and io.status = 2 and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) diptscounted, (select c ount(1) from internetorder io, stockcountscheduledist scsdip where scsdip.distributionpoint_id = io.distributionpoint_link_id and io.status = 3 and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) diptsconfirmed, scsd.orderingdeadline, (select max(io.extendedorderdeadline) from internetorder io, stockcountscheduledist scsdip where scsdip.distributionpoint_id = io.distributionpoint_link_id and io.status in (1, 2) and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) extendeddead line from (select trunc(:3 ) + day_id - 1 countdate from day order by day_id) d, stockcountschedule scs, stockcountscheduleday scsd where scsd.stockcountschedule_id = scs.stockcountschedule_id and scs.customer_id = :4 and scs.replenishmenttype = 0 and getabsdaynumber(countdate) = scsd.day_id AND EXISTS (SELECT 1 FROM standarddeliveryline sdl, productlistperdistribpoint plpd, stockcountscheduledist scsdist WHERE sdl.productlistperdistribpoint_id = plpd.productlistperdistribpoint_id AND scsdist.distributionpoint_id = plpd.distributionpoint_id AND scsdist.stockcountschedule_id = scs.stockcountschedule_id AND (sdl.dateinactive > :5 AND sdl.dateactive <= :6 )) order by d.countdate, scs.code
5r9sqv8utt2h4	CALL WGID_GETGARMENTDATARS(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45)
5sgh3uuu2kxdt	SELECT A.ARTRANSACTION_ID, A.BUSINESSUNIT_ID, A.BILLTOCUSTOMER_LINK_ID, A.DELTOCUSTOMER_LINK_ID, A.ARTRANSACTIONDATE, A.DEBITCREDIT, A.ARTRANSACTIONTYPE_ID, A.CURRENCY_ID, A.AMOUNT, A.ROUTE_ID, A.PAYMENTTYPE, A.REMITIDENTIFICATION, A.INVOICENUMBER, A.REMARK, A.REMITBATCH_ID, A.INVOICEHEADER_ID, A.SYSTEMUSER_ID, A.TIMESTAMP, A.PAYMENTREFERENCE, (select DESCRIPTION from ARTRANSACTIONTYPE_DESC where ARTRANSACTIONTYPE_DESC.ARTRANSACTIONTYPE_ID=A.ARTRANSACTIONTYPE_ID and LANGUAGE_ID = :1 ) DESCR, CL2.CUSTOMERNUMBER, CL2.NAME, CURRENCY.CODE, CURRENCY.INVOICEAMOUNTDECIMALS, ROUTE.ROUTENUMBER, CL1.CUSTOMERNUMBER, CL1.NAME, A.EXPORTSTATUS, A.ARBALANCE_ID, A.PARENTARTRANSACTION_ID , A.COMMISSIONCALCULATED, A.INVOICEHEADER_ID, (case when CC.CLUSTER_ID is null then (select CUSTOMERCLUSTER.CLUSTER_ID from CUSTOMERSPERCLUSTER, CUSTOMERCLUSTER where CUSTOMERSPERCLUSTER.CLUSTER_ID =CUSTOMERCLUSTER.CLUSTER_ID and CUSTOMERCLUSTER.INVOICEFREQUENCY_ID = H.INVOICEFREQUENCY_ID and CUSTOMERSPERCLUSTER.CUSTOMER_ID = C1.CUSTOMER_ID) else CC.CLUSTER_ID end), (case when CC.CLUSTER_ID is null then (select CUSTOMERCLUSTER.CODE from CUSTOMERSPERCLUSTER, CUSTOMERCLUSTER where CUSTOMERSPERCLUSTER.CLUSTER_ID =CUSTOMERCLUSTER.CLUSTER_ID and CUSTOMERCLUSTER.INVOICEFREQUENCY_ID = H.INVOICEFREQUENCY_ID and CUSTOMERSPERCLUSTER.CUSTOMER_ID = C1.CUSTOMER_ID) else CC.CODE end), A.PROCESSBUSINESSUNIT_ID, A.CLIENT_LINK_ID , IHC.CLIENT_LINK_ID, IHC.CLIENTNUMBER, IHC.NAME, IHC.ROOMNUMBER, IHC.SENDINVOICETO, IHC.CLIENTTYPE, cbi.debtornumber , H.TOTALAMOUNTEXCLVAT, H.PREBILLAMOUNT , VDELTOCUST.MACCUSTID, VDELTOCUST.MACCUSTNR, VDELTOCUST.MACCUSTNAME , INVBU.CODE, INVBU.DESCRIPTION , A.PROCESSBUSINESSUNIT_ID, PROCBU.CODE, PROCBU.DESCRIPTION , VDELTOCUST.CHAIN_ID, VDELTOCUST.CHCODE, VDELTOCUST.CHNAME , VDELTOCUST.COMMONCUSTID, VDELTOCUST.COMMONCUSTNR, VDELTOCUST.COMMONCUSTNAME , VPAYBY.CUSTOMER_ID, VPAYBY.CUSTOMERNUMBER, VPAYBY.NAME , IHEXTR.PAYMENTDISCOUNT, IHEXTR.DISCOUNTDAYS, A.GLEXPORT_ID , H.PAYTHISAMOUNT, H.INVOICEDATE, H.NETPAYMENTDAYS , IHEXTR.PAYMENTTERMSDUEDATE, IHEXTR.PAYMENTTYPE , PLANT.Code, PLANT.Name , C.TYPENUMBER , A.originaltransaction_id , (select max(ca.applydate) from cashapplication ca where ca.creditartransaction_id=a.artransaction_id and ca.unapplied_yn='N') lastApplyDate , A.ISOPEN_YN, A.exchangeRateDate, A.exchan geRate , A.TOTALAPPLIEDAMOUNT , CASE WHEN (SELECT COUNT(CCT.CREDITCARDTRANSACTION_ID) FROM CREDITCARDTRANSACTION CCT WHERE CCT.ARTRANSACTION_ID = A.ARTRANSACTION_ID AND CCT.STATUS IN (1, 3)) > 0 THEN 'Y' ELSE 'N' END existcreditcardtransaction, (select max(CCT.STATUS) from CREDITCARDTRANSACTION CCT where CCT.ARTRANSACTION_ID = A.ARTRANSACTION_ID) creditCardTransactionStatus, VDELTOCUST.legacycustomernumber deltoLegacy, C1.legacycustomernumber billtoLegacy FROM ARTRANSACTION A left outer join CUSTOMER_LINK CL2 on A.DELTOCUSTOMER_LINK_ID = CL2.CUSTOMER_LINK_ID left outer join ROUTE on A.ROUTE_ID = ROUTE.ROUTE_ID LEFT OUTER JOIN (SELECT DELTO.CUSTOMER_ID CUST_ID, DELTO.CUSTOMERNUMBER, DELTO.SEARCHNAME, DELTO.CHAIN_ID, CH.CODE CHCODE, CH.NAME CHNAME, MAC.CU STOMER_ID MACCUSTID, MAC.CUSTOMERNUMBER MACCUSTNR, MAC.NAME MACCUSTNAME, MAC.SEARCHNAME MACSEARCHNAME, COMMONCUST.CUSTOMER_ID COMMONCUSTID, COMMONCUST.CUSTOMERNUMBER COMMONCUSTNR, COMMONCUST.NAME COMMONCUSTNAME, COMMONCUST.SEARCHNAME COMMONSEARCHNAME, DELTO.legacycustomernumber FROM CUSTOMER DELTO LEFT OUTER JOIN CUSTOMeR MAC ON DELTO.MASTERACCOUNT_ID = MAC.CUSTOMER_ID LEFT OUTER JOIN CHAIN CH ON DELTO.CHAIN_ID = CH.CHAIN_ID LEFT OUTER JOIN CUSTOMER COMMONCUST ON COMMONCUST.CUSTOMER_ID = DELTO.COMMONCUSTOMER_ID ) VDELTOCUST ON A.DELTOCUSTOMER_LINK_ID = VDELTOCUST.CUST_ID left outer join ((INVOICEHEADER H left outer join CUSTOMERCLUSTER CC on H.CLUSTER_ID = CC.CLUSTER_ID left outer join (INVOICEHEADERCLIENT ihc left outer join CLIENTBILLINGINFO cbi on ihc.client_link_id = cbi.clien t_id) on H.INVOICEHEADER_ID = IHC.INVOICEHEADER_ID)) on A.INVOICEHEADER_ID = H.INVOICEHEADER_ID left outer join CUSTOMER C1 on A.BILLTOCUSTOMER_LINK_ID = C1.CUSTOMER_ID LEFT OUTER JOIN INVOICEHEADEREXTRAINFO IHEXTR ON IHEXTR.INVOICEHEADER_ID = A.INVOICEHEADER_ID LEFT OUTER JOIN (SELECT PB.CUSTOMER_ID, PB.CUSTOMERNUMBER, PB.NAME, PB.SEARCHNAME FROM CUSTOMER PB) VPAYBY ON IHEXTR.PAYBYCUSTOMER_LINK_ID = VPAYBY.CUSTOMER_ID , ARTRANSACTIONTYPE C, CUSTOMER_LINK CL1, CURRENCY, BUSINESSUNIT PROCBU, BUSINESSUNIT INVBU, PLANT WHERE A.ARTRANSACTIONTYPE_ID = C.ARTRANSACTIONTYPE_ID AND A.BILLTOCUSTOMER_LINK_ID = CL1.CUSTOMER_LINK_ID AND A.CURRENCY_ID = CURRENCY.CURRENCY_ID AND A.PROCESSBUSINESSUNIT_ID = PROCBU.BUSINESSUNIT_ID AND PROCBU.Plant_Id = PLANT.Plant_Id AND A.BUSINESSUNIT_ID = INVBU.BUSINESSUNIT _ID AND A.EXPORTSTATUS = :2 AND TRUNC(A.ARTRANSACTIONDATE) BETWEEN TRUNC(:3 ) AND TRUNC(:4 ) AND A.DEBITCREDIT = :5 AND A.BUSINESSUNIT_ID = :6 AND C.TYPENUMBER = :7 order by A.ARTRANSACTIONDATE
663uh5t3kv6xa	SELECT "RELATEDCUSTOMER_ID", "DAY_ID", "STOCKNAME", "CUSTOMEROWNED", "PRODCODE", "SIZESEQ", "SIZECODE", "PRODDESC", "PGCODE", "PGDDESC", "ACODE", "ADESC", "PRIMARYID", "WASHESTOTAL", "WEEKSUSED", "WEEKSSTOCK", "PODATE", "LASTSCANDATE", "QUALITYGRADECODE", "RVSCODE", "RVSCALCWEEKS", "RVSMAXWASHES", "REPLACEPRICE", "CURVALUE", "WEEKSINUSE", "CALCBASIS", "MASTER_CUSTOMER" FROM "ABSSOLUTE"."V_DWH_RTCV_06" "Y"
6dnqd6tkujxf0	insert into PACKINGNOTE (packingnote_id, employee_id, notenumber, productiondate, deliverydate, status_id , remark, distributionpoint_id, deliverypoint_id, plantdepartment_id, transportarticle_id, systemuser_id , processbusinessunit_id, route_id, timestamp, cleanreturn, sequencenumber, deadline , occupancy, invoicebusinessunit_id, consignmentnote_id, origin, routecontrol_id, originaldeliverydate , invoicestatus, enddateforuse, stopnumber, daynumber, correctednotenumber, creationtimestamp , referencenumber, returndateloan, stop_id, straightevenreturn, numberofgarmentsinlocker, numberoflockers , bulkpackingnote_id, freeofchargereason_id, year, week, weighingticket_id, totalamount, totalamountexcludingvat, pricescalculated, SHOWONINVOICEAPPENDIX , deliveryRemark, exported, finishedPacking, pickListPrinted , numberofcolli, emailstatus, emailmaybesent, signed, internalprocessingbu_id, creationsystemuser_id , salesnote_id, parceltracecode, calculatetransportcosts, correctionfornote_id, correctconsignmentnote, pnremark, loadsequencenr , correctionreasoncode_id, combinedtransportationnr, reducefactor, deliverynoteremark_id, cleanroombatch, availableForOutscan, parkedbyemployee_id, standardDeliveryIncluded, truckloadtimestamp, truckloadedby, externalpackingstatus) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 )
6hr1av43m8wu5	select DFF.DIALOG_ID, DFF.DIALOGFLEXFIELD_ID, DFF.CHARACTERISTIC_ID, DFF.NUMBERVALUE, DFF.STRINGVALUE, DFF.DATEVALUE, DFF.CHARACTERISTICPREDEFVALUE_ID, DFF.SYSTEMUSER_ID, DFF.TIMESTAMP from DIALOGFLEXFIELDS DFF where DFF.ENTITY_ID = :1 and DFF.DIALOG_ID = (select DIALOG_ID from DIALOGS where CODE = :2 )
6k9fbbg721a4b	SELECT io.internetorder_id, cust.customernumber, cust.name, dept.departmentnumber, dept.description, dip.distributionpoint_id, dip.distributionpointnumber, dip.description, io.timestamp, scs.packoncountday, 2, dept.customer_id, ( select count(1) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.code) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.description) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevi ce_id = scd.mobiledevice_id), scday.orderingdeadline , io.pdaexportstatus , scs.deliverydaydetermination, scday.DELIVERYDAY_ID, (SELECT DD.DESCRIPTION FROM DAY_DESC DD WHERE DD.DAY_ID = scday.DELIVERYDAY_ID AND DD.LANGUAGE_ID = :1 ) , scs.code, scs.description, io.extendedorderdeadline, io.skippedorder, bu.code, bu.description FROM INTERNETORDER IO, DISTRIBUTIONPOINT DIP, DEPARTMENT DEPT, CUSTOMER CUST, STOCKCOUNTSCHEDULE SCS, STOCKCOUNTSCHEDULEDIST SCDIST, STOCKCOUNTSCHEDULEDAY SCDAY, BUSINESSUNIT BU WHERE io.orderdate = :2 and io.status = 3 AND io.distributionpoint_link_id = dip.distributionpoint_id AND dip.department_id = dept.department_id AND dept.customer_id = cust.customer_id AND cust.defaultbusinessunit_id = bu.businessunit_id AND (exists (select 1 from CUSTOMERACTIVITIES CA where CA.PROCESSBUSINESSUNIT_ID = :3 and CA.CUSTOMER_ID = CUST.CUSTOMER_ID)) and dip.distributionpoint_id = scdist.distributionpoint_id AND scdist.stockcountschedu le_id = scday.stockcountschedule_id AND scday.day_id = :4 AND scdist.stockcountschedule_id = scs.stockcountschedule_id AND SCS.REPLENISHMENTTYPE = 0 and (io.confirmationtimestamp is null OR scday.orderingdeadline is null OR (TRUNC(io.confirmationtimestamp) < TRUNC(io.orderdate) OR (TRUNC(io.confirmationtimestamp) = TRUNC(io.orderdate) AND nvl(scday.orderingdeadline, io.confirmationtimestamp) is not null AND TO_CHAR(io.confirmationtimestamp, 'HH24:MI:SS') <= TO_CHAR(scday.orderingdeadline, 'HH24:MI:SS')))) AND not exists (SELECT 1 FROM internetorderline iol, deliveryvariations dv WHERE (dv.deliveryvariations_id = iol.deliveryvariations_id AND nvl(dv.packingnote_id, 0) > 0 AND iol.internetorder_id = io.internetorder_id )) AND not exists (select 1 from packingnote pn where (dip.distributionpoint_id = pn.distributionpoint_id AND pn.productiondate >= :5 AND pn.origin = 0 AND pn.status_id <> 8 and pn.creationtimestamp < io.timestamp AND pn.creationtime stamp IS NOT NULL)) and BU.CODE = :6 UNION ALL SELECT io.internetorder_id, cust.customernumber, cust.name, dept.departmentnumber, dept.description, dip.distributionpoint_id, dip.distributionpointnumber, dip.description, io.timestamp, scs.packoncountday, 5, dept.customer_id, ( select count(1) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.code) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.description) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), scday.orderingdeadline , io.pdaexportstatus , scs.deliverydaydetermination, scday.DELIVERYDAY_ID, (SELECT DD.DESCRIPTION FROM DAY_DESC DD WHERE DD.DAY_ID = scday.DELIVERYDAY_ID AND DD.LANGUAGE_ID = :7 ) , scs.code, scs.description, io.extendedorderdeadline, io.skippedorder, bu.code, bu.description FROM INTERNETORDER IO, DISTRIBUTIONPOINT DIP, DEPARTMENT DEPT, CUSTOMER CUST, STOCKCOUNTSCHEDULE SCS, STOCKCOUNTSCHEDULEDIST SCDIST, STOCKCOUNTSCHEDULEDAY SCDAY, BUSINESSUNIT BU WHERE io.orderdate = :8 and io.status = 3 AND io.distributionpoint_link_id = dip.distributionpoint_id AND dip.department_id = dept.department_id AND dept.customer_id = cust.customer_id AND cust.defaultbusinessunit_id = bu.businessunit_id AND (exists (select 1 from CUSTOMERACTIVITIES CA where CA.PROCESSBUSINESSUNIT_ID = :9 and CA.CUSTOMER_ID = CUST.CUSTOMER_ID)) and dip.distributionpoint_id = scdist.distributionpoint_id AND scdist.stockcountschedule_id = scday.stockcountschedule_id AND scday.day_id = :10 AND scdist.stockcountschedu le_id = scs.stockcountschedule_id AND SCS.REPLENISHMENTTYPE = 0 and (io.confirmationtimestamp is not null AND (TRUNC(io.confirmationtimestamp) > TRUNC(io.orderdate) OR (TRUNC(io.confirmationtimestamp) = TRUNC(io.orderdate) AND nvl(scday.orderingdeadline, io.confirmationtimestamp) is not null AND TO_CHAR(io.confirmationtimestamp, 'HH24:MI:SS') > TO_CHAR(scday.orderingdeadline, 'HH24:MI:SS')))) AND not exists (SELECT 1 FROM internetorderline iol, deliveryvariations dv WHERE (dv.deliveryvariations_id = iol.deliveryvariations_id AND nvl(dv.packingnote_id, 0) > 0 AND iol.internetorder_id = io.internetorder_id )) AND not exists (select 1 from packingnote pn where (dip.distributionpoint_id = pn.distributionpoint_id AND pn.productiondate >= :11 AND pn.origin = 0 AND pn.status_id <> 8 and pn.creationtimestamp < io.timestamp AND pn.creationtimestamp IS NOT NULL)) and BU.CODE = :12 UNION ALL SELECT io.internetorder_id, cust.customernumber, cust.name, dept.departmentnumber, dept.description, dip.distributionpoint_id, dip.distributionpointnumber, dip.description, io.timestamp, scs.packoncountday, 4, dept.customer_id, ( select count(1) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.code) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.description) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), scday.orderingdeadline , io.pdaexportstatus , scs.deliverydaydetermination, scday.DELIVERYDAY_ID, (SELECT DD.DESCRIPTION FROM DAY_DESC DD WHERE DD.DAY _ID = scday.DELIVERYDAY_ID AND DD.LANGUAGE_ID = :13 ) , scs.code, scs.description, io.extendedorderdeadline, io.skippedorder, bu.code, bu.description FROM INTERNETORDER IO, DISTRIBUTIONPOINT DIP, DEPARTMENT DEPT, CUSTOMER CUST, STOCKCOUNTSCHEDULE SCS, STOCKCOUNTSCHEDULEDIST SCDIST, STOCKCOUNTSCHEDULEDAY SCDAY, BUSINESSUNIT BU WHERE io.orderdate = :14 AND io.status = 3 AND io.distributionpoint_link_id = dip.distributionpoint_id AND dip.department_id = dept.department_id AND dept.customer_id = cust.customer_id AND cust.defaultbusinessunit_id = bu.businessunit_id AND (exists (select 1 from CUSTOMERACTIVITIES CA where CA.PROCESSBUSINESSUNIT_ID = :15 and CA.CUSTOMER_ID = CUST.CUSTOMER_ID)) and dip.distributionpoint_id = scdist.distributionpoint_id AND scdist.stockcountschedule_id = scday.stockcountschedule_id AND scday.day_id = :16 AND scdist.stockcountschedule_id = scs.stockcountschedule_id AND SCS.REPLENISHMENTTYPE = 0 and not exists (SELECT 1 FROM internetord erline iol, deliveryvariations dv WHERE (dv.deliveryvariations_id = iol.deliveryvariations_id AND nvl(dv.packingnote_id, 0) > 0 AND iol.internetorder_id = io.internetorder_id )) AND exists (select 1 from packingnote pn where (dip.distributionpoint_id = pn.distributionpoint_id AND pn.productiondate >= :17 AND pn.origin = 0 AND pn.status_id <> 8 and pn.creationtimestamp < io.timestamp AND pn.creationtimestamp IS NOT NULL)) and BU.CODE = :18
6qz2qnfm542gq	insert into INVOICEHEADER (invoiceheader_id, invoicedate, invoicenumber, documenttype, invoicebusinessunit_id, billtocustomer_link_id , deltocustomer_link_id, endweek, startweek, endmonth, startmonth, county , state, city, zipcode, street2, street, deltocustomername , deltocustomernumber, billtocustomername, billtocustomernumber, routecontrol_id, route_id, routenumber, depotcode , emailaddress, contactpersonphone, contactpersonname, emailstatus, status_id, realcollectiontype , originalcollectiontype, invoicetype, adjustmentreason_id, exported, purchaseorder, deliverinstructions , invoicecomments, totalminusadjustment, totalplusadjustment, cashcollected, paythisamount, prebillamount , invoicefrequency_id, systemuser_id, timestamp, currency_id, startyear , endyear, contractcode, startdate, enddate , creditsqueezeperc , convertedfromlegacy, totalamountexclvat, cluster_id, creditsqueezeamount, splitupcode, splitupdescription, stopnumber , stop_id, invoiceyear, invoiceweek, invoicedaynumber, extraname1, extraname2 , parentinvoiceheader_id, contractstartdate, contractenddate, paymentdiscountdays, netpaymentdays, deltostreet , deltostreet2, deltocounty, deltostate, deltocity, deltozipcode, roundingamount , nextPeriodStartDate, nextPeriodEndDate, nextperiodnoweeks, credited, creditedinvoiceno , NEXTPERIODSTARTWEEK, NEXTPERIODENDWEEK, containresidentialservice, housenumber, housenumberext , externalprintdate, exportdocuware, addtoconsolidatedinv, paidamount, CREDITEDINVOICEHEADER_ID, UNDERUSEAMOUNT , INVOICEJOBSCHEDULE_ID, PRINTDATE, REFERENCE, exportItella, ACTIVITYREFERENCE, exportclieop03, COSTCENTERHISTINVDATASTATUS, EXTERNALPRINTRUN, creationTimestamp, creationSystemUser_ID , rentStopStartDate, rentStopEndDate, internalr emark, showonweb, showonwebsystemuser_id, showonwebtimestamp, ORIGINALINVOICEDATE, SETTLEMENTREJECTREASON, SPLITLEVEL , STREET3, DELTOSTREET3) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84 , :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 , :97 , :98 , :99 , :100 , :101 , :102 , :103 , :104 , :105 , :106 , :107 , :108 , :109 , :110 , :111 , :112 , :113 , :114 , :115 )
76czqn6ytt87c	select annotation_name, annotation_value, annotation_type, intcol_id from sys.annotations_usage$ where obj#=:1 order by intcol_id, annotation_name
8frumpf2xmnzb	INSERT INTO DELIVERYSCAN (BUSINESSUNIT_ID, UNIQUEITEM_LINK_ID, SCANTIMESTAMP, CUSTOMER_LINK_ID, WEAREREMPLOYMENT_LINK_ID, DISTRIBUTIONPOINT_LINK_ID, DELIVERYPOINT_LINK_ID, PRODUCT_LINK_ID, SIZEDEFINITION_LINK_ID, FINISHINGMETHOD_ID, ROUTECONTROL_ID, PACKINGNOTE_ID, FROMSTOCK, PREVIOUSSTAY_ID, SYSTEMUSER_ID, TIMESTAMP, REASONCODE_ID, PLANTDEPARTMENT_ID, CHARGEWASHING, LOCKER_ID, INTERNALPROCESSINGBU_ID, SCANSTATION, PLANNEDDELIVERYDATE, POOL_PACKINGNOTE_ID) VALUES (:B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , CASE WHEN (:B13 IN ('30', '40')) THEN NULL ELSE :B12 END, :B11 , :B10 , :B9 , SYSDATE, :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , COALESCE(:B2 , :B1 ), NULL)
8j8qgk5249rk2	select --+ ordered index(a XIF3CLIENTDELNOTELINESCAN) index(b XPKCLIENTDELIVERYNOTELINE) index(c XIF3CLIENTDELIVERYNOTE) index(d XPKBATCH) c.batch_id from clientdelnotelinescan a , clientdeliverynoteline b, clientdeliverynote c, batch d where a.uniqueresidentialitem_link_id = :1 and a.inscanclientdelnoteline_id = b.clientdeliverynoteline_id and b.clientdeliverinote_id = c.clientdeliverynote_id and c.batch_id = d.batch_id order by a.clientdelnotelinescan_id desc
8r3cfxv85yqrt	select PLPC.PRODUCTLISTPERCUSTOMER_ID, PLPC.DATEACTIVE, PLPC.DATEINACTIVE, PLPC.CODEUNIQUELY, PLPC.SOILWEIGHT, PLPC.ONSITEINVENTORYCOUNT, PLPC.RENTPRICEPERFREQUENCY, PLPC.CHANGESPERWEEK, PLPC.PERCENTAGEAFTERDEPRECIATION, PLPC.BILLINGMETHOD, PLPC.SOILCOUNTMETHOD, PLPC.SRCMETHOD, PLPC.CLEANCOUNT, PLPC.SRCPERCENTAGESOILCOUNT, PLPC.SRCPERCENTAGEINVENTORY, PLPC.SRCQUANTITY, PLPC.SRCFREQUENCY, PLPC.CHARGEOVERUSE, PLPC.CHARGESTOCKROOMINVENTORY, PLPC.MINIMUMTURNAROUND, PLPC.MINIMUMQUANTITY, PLPC.QTYFREEOFCHARGE, PLPC.CUSTOMCODE, PLPC.CUSTOMDESCRIPTION, PLPC.CUSTOMBUNDLESIZE, PLPC.DELAYEDEVENEXCHANGE, 0, PLPC.INVENTORYBILLINGTYPE, PLPC.MINIMUMFREQUENCY, PLPC.DELIVERREPLACEMENTQTY, PLPC.RESIDUALVALUESCHEME_ID, PLPC.SUPPRESSONPACKINGREPORTS, 0, PLPC.PRODUCT_ID, PRODUCT.CODE, (select DESCRIPTION from PRODUCT_DESC where PRODUCT_DES C.PRODUCT_ID=PLPC.PRODUCT_ID and LANGUAGE_ID = :1 ), PRODUCT.TYPEOFMERCHANDISE, PRODUCT.RENTTURNOVERGROUP_ID, PRODUCT.WASHTURNOVERGROUP_ID, PRODUCT.SALESTURNOVERGROUP_ID, PLPC.SIZEDEFINITION_ID, SD.CODE, (select DESCRIPTION from SIZEDEFINITION_DESC where SIZEDEFINITION_DESC.SIZEDEFINITION_ID=PLPC.SIZEDEFINITION_ID and LANGUAGE_ID = :2 ), PLPC.FINISHINGMETHOD_ID, FM.CODE, (select DESCRIPTION from FINISHINGMETHOD_DESC where FINISHINGMETHOD_DESC.FINISHINGMETHOD_ID=PLPC.FINISHINGMETHOD_ID and LANGUAGE_ID = :3 ), PRODUCT.PRODUCTGROUP_ID, PGPC.CUSTOMCODE, PG.CODE, PGPC.CUSTOMDESCRITPION, (select DESCRIPTION from PRODUCTGROUP_DESC where PRODUCTGROUP_DESC.PRODUCTGROUP_ID=PG.PRODUCTGROUP_ID and LANGUAGE_ID = :4 ), PRODUCT.INVOICECOLUMN, PRODUCT.SHORTDESCRIPTION, PLPC.SEASONTYPE_ID, PLPC.CHARGERENTINACTIVESEASON, PG.ACTIVITY_ID, PLPC.CHARGEWASHESPACK, PLPC.QUANTITYRETURNED, PLPC.FREEQTYFREQUENCY, plpc.DEPOSITSTOCKCHARGEPERCENTAGE, PLPC.CHARGEWASHESONLYFORCOG, PRODUCT.MOD EL, PRODUCT.PARENTPRODUCT_ID, PLPC.DEEDELIVERYSCHEME_ID, DEE.CODE, DEE.FREQUENCY, DEE.OFFSETWEEK, FM.PRICEFACTOR, PLPC.AUTOREPLACEMENTDIFFERENCE, PLPC.PRICEUSAGE, PLPC.INVOICEFREQRELATEDRENTPRICE, PLPC.INVWASHBASEDONSOIL, coalesce((select PLPC2.DATEACTIVE from PRODUCTLISTPERCUSTOMER PLPC2 where PLPC2.PRODUCT_ID = PLPC.PRODUCT_ID and PLPC2.SIZEDEFINITION_ID = PLPC.SIZEDEFINITION_ID and PLPC2.FINISHINGMETHOD_ID = PLPC.FINISHINGMETHOD_ID and PLPC2.DATEACTIVE >= PLPC.DATEINACTIVE and PLPC2.CUSTOMER_ID = PLPC.CUSTOMER_ID and PLPC2.DATEINACTIVE <= (select min(PLPC3.DATEINACTIVE) from PRODUCTLISTPERCUSTOMER PLPC3 where PLPC3.PRODUCT_ID = PLPC.PRODUCT_ID and PLPC3.SIZEDEFINITION_ID = PLPC.SIZEDEFINITION_ID and PLPC3.FINISHINGMETHOD_ID = PLPC.FINISHINGMETHOD_ID and PLPC3.CUSTOMER_ID = PLPC.CUSTOMER_ID and PLPC3.DATEACTIVE >= PLPC.DATEINACTIVE)), (select PLPC2.DATEACTIVE from PRODUCTLISTPERCUSTOMER PLPC2, SIZEDEFINITION sz2 where PLPC2.PRODUCT_ID = PLPC.PRODUCT_ID and PLPC2.SIZEDEFINITION_ID = SZ2.SIZEDEFINITION_ID and SZ2.CODE = '*' and PLPC2.FINISHINGMETHOD_ID = PLPC.FINISHINGMETHOD_ID and PLPC2.DATEACTIVE >= PLPC.DATEINACTIVE and PLPC2.CUSTOMER_ID = PLPC.CUSTOMER_ID and PLPC2.DATEINACTIVE <= (select min(PLPC3.DATEINACTIVE) from PRODUCTLISTPERCUSTOMER PLPC3 where PLPC3.PRODUCT_ID = PLPC.PRODUCT_ID and PLPC3.SIZEDEFINITION_ID = PLPC.SIZEDEFINITION_ID and PLPC3.FINISHINGMETHOD_ID = PLPC.FINISHINGMETHOD_ID and PLPC3.CUSTOMER_ID = PLPC.CUSTOMER_ID and PLPC3.DATEACTIVE >= PLPC.DATEINACTIVE))) , PLPC.SRCCHARGERENT, PLPC.IMAGECARE , FM.WASHTYPE , PLPC.CHARGESERVICECOSTS, PLPC.CHARGESERVICECOSTSFOR , PLPC.STRAIGHTEVENEXCHANGE , (select min(CUSTOMERACTIVITIES.processbusinessunit_id) from CUSTOMERACTIVITIES where CUSTOMERACTIVITIES.CUSTOMER_ID = PLPC.CUSTOMER_ID and CUSTOMERACTIVITIES.ACTIVITY_ID = PG.ACTIVITY_ID), PLPC.SRCPERCENTAGEBILLEDQTY, PLPC.CONTRACTPURCHASEORDER_ID , PLPC.CHARGEEXCESS, PLPC.CHARGERENTNORETURNPOOLAFTERWKS, PRODUCT.UNIQUEPOOLITEM, PLPC.BUDGETUSAGE, PLPC.CUSTOMERBUDGETDEFINITION_ID, RENTCUSTBUDGETDEFINITION_ID, SALESCUSTBUDGETDEFINITION_ID, PLPC.BM4ALLOWSALES, PLPC.NOTCHARGEOUTSTANDINGLOANS, PLPC.RENTBUSINESSUNIT_ID , PLPC.DEPRECIATIONLOSSSCHEME_ID, PLPC.SHOWFOCRENTONINVOICE, PLPC.BM1FIXEDWEARERS , PLPC.REMARK, PLPC.PRINTREMARKONINVOIC E, PRODUCT.CUSTOMGARMENTCHARGE, PLPC.ALLOWRETURNTOSTOCK , PLPC.SRCPERCENTAGEDELIVEREDQTY, PLPC.BM1RENTCPWMAX from PRODUCTLISTPERCUSTOMER PLPC left outer join DELIVERYSCHEME DEE on (plpc.DEEDELIVERYSCHEME_ID = DEE.DELIVERYSCHEME_ID), PRODUCT , SIZEDEFINITION SD, FINISHINGMETHOD FM, PRODUCTGROUPPERCUSTOMER PGPC, PRODUCTGROUP PG where PLPC.CUSTOMER_ID = :5 and ((PLPC.DATEACTIVE <= :6 and PLPC.DATEINACTIVE >= :7 ) or (PLPC.DATEACTIVE >= :8 and PLPC.DATEACTIVE <= :9 )) and PLPC.PRODUCT_ID = PRODUCT.PRODUCT_ID and PLPC.SIZEDEFINITION_ID = SD.SIZEDEFINITION_ID and PLPC.FINISHINGMETHOD_ID = FM.FINISHINGMETHOD_ID and PLPC.CUSTOMER_ID = PGPC.CUSTOMER_ID and PRODUCT.PRODUCTGROUP_ID = PGPC.PRODUCTGROUP_ID and PRODUCT.PRODUCTGROUP_ID = PG.PRODUCTGROUP_ID and exists (select 1 from CUSTOMERACTIVITIES where CUSTOMERACTIVITIES.CUSTOMER_ID = PLPC.CUSTOMER_ID and CUSTOMERACTIVITIES.ACTIVITY_ID = PG.ACTIVITY_ID ) order by PRODUCT.code, PLPC.DATEINACTIVE
91pvbx7jrzphx	BEGIN DBMS_SESSION.RESET_PACKAGE; END;
93yyqxuszzj9b	select v.uniqueitem_id, v.primaryid, p.code, (select pd.description from product_desc pd where pd.product_id = v.product_id and pd.language_id = :1 ), (select std.description from status_desc std where std.status_id = v.status_id and std.language_id = :2 ), (select ttd.description from treatmenttype_desc ttd where ttd.treatmenttype_id = v.treatmenttype_id and ttd.language_id = :3 ), v.chargenumber, v.expirationdate, bu.code, bu.description, sd.code, (select sdd.description from sizedefinition_desc sdd where sdd.sizedefinition_id = v.sizedefinition_id and sdd.language_id = :4 ), s.code, st.staynr, (select stayd.description from stay_desc stayd where stayd.stay_id = v.stay_id and stayd.language_id = :5 ), tt.code, v.stockexpirationdate, v.creationtimestamp, creationemp.employeenumber, creationemp.name, ll.code, ll.description, v.assembletimestamp, assembleemp.employeenumber, assembleemp.name, v.loadtimestamp, loademp.employeenumber, loademp.name, c.cartnumber, (select ctt.code from carttype ctt where ctt.carttype_id = c.carttype_id), (select ctd.description from carttype_desc ctd where ctd.carttype_id = c.carttype_id and ctd.language_id = :6 ), cuid.primaryid, ster.code, ster.description, v.sterilizationtimestamp, steremp.employeenumber, steremp.name, rc.code, (select rcd.description from reasoncode_desc rcd where rcd.reasoncode_id = v.reject reasoncode_id and rcd.language_id = :7 ), v.deliverytimestamp, delivemp.employeenumber, delivemp.name, pn.notenumber, pn.deliverydate, delivbu.code, delivbu.description, dp.distributionpointnumber, dp.description, d.departmentnumber, d.description, cust.customernumber, cust.name, v.canceltimestamp, cancelemp.employeenumber, cancelemp.name, cancelrc.code, (select rcd.description from reasoncode_desc rcd where rcd.reasoncode_id = v.cancelreasoncode_id and rcd.language_id = :8 ), v.recalltimestamp, recallemp.employeenumber, recallemp.name, recallrc.code, (select rcdd.description from reasoncode_desc rcdd where rcdd.reasoncode_id = v.recallreasoncode_id and rcdd.language_id = :9 ), porp.sterile, pn.packingnote_id, v .product_id, v.customer_id, v.distributionpoint_id, v.sizedefinition_id, v.recallreasoncode_id, v.finishingmethod_id, delp.code, delp.description, fm.code, fm.color, (select fmd.description from finishingmethod_desc fmd where fmd.language_id = :10 and fmd.finishingmethod_id = fm.finishingmethod_id), v.plantdepartment_id from product p, productorpack porp, status s, businessunit bu, sizedefinition sd, stay st, employee creationemp, labellayout ll, v_uniqueorpack v left outer join employee assembleemp on v.assembleemployee_id = assembleemp.employee_id left outer join employee loademp on v.loademployee_id = loademp.employee_id left outer join employee delivemp on v.deliveryemploye e_id = delivemp.employee_id left outer join employee cancelemp on v.cancelemployee_id = cancelemp.employee_id left outer join employee recallemp on v.recallemployee_id = recallemp.employee_id left outer join employee steremp on v.sterilizationemployee_id = steremp.employee_id left outer join sterilizer ster on v.sterilizer_id = ster.sterilizer_id left outer join packingnote pn on pn.packingnote_id = v.packingnote_id left outer join businessunit delivbu on v.deliverybusinessunit_id = delivbu.businessunit_id left outer join treatmenttype tt on v.treatmenttype_id = tt.treatmenttype_id left outer join reasoncode rc on v.rejectreasoncode_id = rc.reasoncode_id left outer join reasoncode cancelrc on v.cancelreasoncode_id = cancelrc.reasoncode_id left outer join reasoncode recallrc on v.recallreasoncode_id = recallrc.reasoncode_id left outer join((distributionpoint dp inner join deliverypoint delp on dp.deliverypoint_id = delp.deliverypoint_id) inner join(department d inner join customer cust on d.customer_id = cust.customer_id) on dp.department_id = d.department_id) on (nvl(v.distributionpoint_id, v.lastdistributionpoint_id) = dp.distributionpoint_id) left outer join(cart c left outer join carttype ct on ct.carttype_id = c.cart_id left outer join cartuniqueid cuid on cuid.cart_id = c.cart_id) on v.cart_id = c.cart_id left outer join finishingmethod fm on v.finishingmethod_id = fm.finishingmethod_id where v.product_id = p.product_id and v.product_id = porp.product_id and v.status_id = s.status_id and v.businessunit_id = bu.businessunit_id and v.sizedefinition_id = sd.sizedefinition_id and v.stay_id = st.stay_id and v.creationemployee_id = creationemp.employee_id and v.labellayout_id = ll.labellayout_id and v.businessunit_id in (select ordbu.delivertobusinessunit_id from ordeliverbusinessunit ordbu where ordbu.orbusinessunit_id = :11 UNION select :12 from dual) and (pn.productiondate between :13 AND :14 or pn.productiondate is null) and (pn.PLANTDEPARTMENT_ID is null or (exists (select 1 from ordeliverbusinessunit ordbu where pn.processbusinessunit_id=ordbu.delivertobusinessunit_id and nvl(ordbu.orplantdepartment_id, pn.plantdepartment_id)=pn.plantdepartment_id )) or pn.plantdepartment_id in ( select pda.plantdepartment_id from plantdepartment pda, businessunit orbus where pda.plant_id = orbus.plant_id and orbus.businessunit_id = :15 ) ) and p.product_id = :16
9f7sb16cv3dyb	select PG.CODE, (select DESCRIPTION from PRODUCTGROUP_DESC where PRODUCTGROUP_DESC.PRODUCTGROUP_ID=PG.PRODUCTGROUP_ID and LANGUAGE_ID = 1), A.ACTIVITY_ID, A.CODE, (select DESCRIPTION from ACTIVITY_DESC where ACTIVITY_DESC.ACTIVITY_ID=A.ACTIVITY_ID and LANGUAGE_ID = 1) from PRODUCTGROUP PG join ACTIVITY A on PG.ACTIVITY_ID = A.ACTIVITY_ID where PG.PRODUCTGROUP_ID = :1
9fy3rjntc83xp	select product_id, code, packingsequence, packingamount, active, garmentset , pack, model, occupancyrelated, specialsize, volumeunits, theoreticalweight , legacyproductcode, fabric_id, color_id, supplier_id, sizetype_id, washingprocess_id , productgroup_id, weighingcategory_id, parentproduct_id, registrationunit_id, soilcountmethod, recordpassivestock , typeofmerchandise, minimumturnaround, autoreplacement, shortdescription, systemuser_id , timestamp, invoicecolumn, rentturnovergroup_id, washturnovergroup_id, replacementturnovergroup_id, salesturnovergroup_id , image_id, maxwashes, uniquepoolitem, putuptype, exchangemethod, eannumber , finishingcode_id, fulfillmentgroup, storeactivestocklocation, misproduct_id, INTRASTAT_ID, benchmarkrelevant, LOSSTURNOVERGROUP_ID, MaxCartQty, ADVANCETURNOVERGROUP _ID, CORRECTIONSTURNOVERGROUP_ID, OPTIMAFACTOR, defaultResidualValueScheme_ID, abccode, defaultprovisiongroup_id, exportdate, squaremeters, washcogturnovergroup_id, mountingproduct, showonmountingorder, showondismountingorder, showonpicklistrsc, usegradecategorydefect, defaultreturngrade_id , percentagefirstdelivery, defaultsalesgrade_id, defaultmountinggrade_id, deliverfromonpicklistrsc, alternativesizereservation , deliverfromonpicklistrsc_mo, deliverfromonpicklistrsc_sn , lotnumberrequired, orpackcontentsallowed, packtype, treatmentcheckscheme_id, remark, remarkforweb , productranking_id, defaultstockroom_id, fixedstockroom , minqtytodeliver, maxqtytodeliver, mrpcode_id, productsortgroup_id, packinggroup, packingproducttype, LOANTURNOVERGROUP_ID, EXTRABAG, MINAPPROVALQTY, SIZEBREAKDOWN , NOOFWASHESNOCHECKPOINT, CHARGELOSSFORPOOL, confirmproductonpda, totalpackweight, packvolumeunits, allowprewash, rentnotretturnovergroup_id, maximum numberofrepairs, materialmaintenanceprotection, customgarmentcharge, cleanRoomBatchPairScan, DELIVERYTOBILLRELATIONSHIP, FABRIC2_ID, FABRIC3_ID , UHFTAGGED, SALESONLY, BULKPACKINGACTIVESTOCKTYPE , MMPDEFAULTPERCENTAGE, AUTOSOIL, MATERIALLOSSPROTECTION, ALLOWONEOFFORDERINGONRA, CODETAPEPLACEMENT_ID, SUPPRESSONPACKINGREPORTS from PRODUCT where product_id = :1
9gr1dyydrzj3j	BEGIN DBMS_SESSION.SET_IDENTIFIER(:1 ); DBMS_APPLICATION_INFO.SET_MODULE(:2 , :3 ); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:4 ); END;
9hcjch2rsgksv	BEGIN "ABSSOLUTE"."VL_SENDTRACK"(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17); END;
9qc20t5v2wjx2	SELECT CHARACTERISTIC_ID, CODE, ACTIVE, DATATYPE, LENGTH, DECIMALS, MANDATORY, KEEPVALUE, MINVALUE, MAXVALUE, DEFNUMBERVALUE, DEFSTRINGVALUE, DEFDATEVALUE, SYSTEMUSER_ID, TIMESTAMP, DIALOG_ID, TOOLTIP FROM CHARACTERISTIC where CHARACTERISTIC_ID = :1
9rfqhpzr4kkr0	update CUSTOMER set CUSTOMERNUMBER = :1 , NAME = :2 , PHONENO = :3 , LEGALNAME = :4 , FAXNO = :5 , EMAIL = :6 , DATEACTIVE = :7 , DATEINACTIVE = :8 , ACTIVE = :9 , REMARK = :10 , SICCODE = :11 , SEARCHNAME = :12 , LEGACYCUSTOMERNUMBER = :13 , OCCUPANCYFROMSTOCKCOUNT = :14 , DEBTOR_ID = :15 , DEFAULTBUSINESSUNIT_ID = :16 , MASTERACCOUNT_ID = :17 , MASTERACCOUNT=:18 , MARKETSEGMENT_ID=:19 , SALESAREA_ID = :20 , CURRENCY_ID = :21 , COUNTRY_ID = :22 , LANGUAGE_ID = :23 , VISITADDRESS_ID = :24 , MAILADDRESS_ID = :25 , COMMUNICATIONMETHOD_ID = :26 , SOILCOUNT=:27 , DELIVERYNOTELAYOUT_ID=:28 , CONSIGNMENTNOTELAYOUT_ID=:29 , consignmentnotelevel=:30 , INTERNETORDERING=:31 , IMPORTOCCUPANCYHHT=:32 , RETURNLINEN=:33 , RETURNGARMENTS=:34 , CODEGARMENTSUNIQUELY=:35 , DAYSNOPREPCHARGE=:36 , CHARGEREPAIRS=:37 , GARMENTRENTSPECIFICATION=:38 , SWINGSUITDELIVERY=:39 , USESGARMENTDISPENSER=:40 , LOCKERMANAGEMENT=:41 , FIRSTDAYSGRADE_ID=:42 , DAYSSPECIALGRADE=:43 , FIRSTISSUEGRADE_ID=:44 , QUALITYGRADE_ID=:45 , SERVICETEAM_ID=:46 , STANDARDQTYDEFINITION=:47 , CORRECTSHORTAGES=:48 , CORRECTOVERDELIVERIES=:49 , INVOICELAYOUT_ID=:50 , INVOICECOPIES=:51 , MINIMUMINVOICEAMOUNT=:52 , TAXABLE=:53 , PAYMENTMETHOD=:54 , PAYMENTTYPE=:55 , STATES_ID=:56 , COUNTYTAXAUTHORITY_ID=:57 , CITYTAXAUTHORITY_ID=:58 , CUSTOMERMARKINGGARMENT=:59 , INVOICEFREQUENCY_ID=:60 , CODELABELLAYOUT_ID=:61 , NAMELABELLAYOUT_ID=:62 , ALLOWPREPCHARGEPERTEMPLATE=:63 , EMBLEMSPECIFICATION=:64 , ALLOWNEGATIVESTOCKCOUNT=:65 , OUTSCANSORT=:66 , CHARGEWASHFIRSTISSUE=:67 , ALLOWAUTOMATICSALES=:68 , CUSTOMERRELATED=:69 , CHARGERESIDUALVALUE=:70 , RENTPERIOD=:71 , SPLITINVOICES=:72 , VATNUMBER=:73 , CHAIN_ID=:74 , SYSTEMUSER_ID=:75 , TIMESTAMP=:76 , GARMENTINSURANCE=:77 , INVOICEADDRESS_ID=:78 , ILNNUMBER=:79 , USESBUNDLES=:80 , EXTRANAME1=:81 , EXTRANAME2=:82 , SCANSONDELIVERYNOTE=:83 , ASSOCI ATIONTYPE=:84 , DIRECTINVDELIVERIES=:85 , DIRECTINVCLEANWEIGHTS=:86 , DIRECTINVSOILWEIGHTS=:87 , DIRECTINVMINIMUMAMOUNT=:88 , USECOVERPAGE=:89 , BULKPACKING=:90 , OWNEDCONTAINER=:91 , EDIMETHOD=:92 , delnoteforbulkputupitems=:93 , insurancepricetype = :94 , passerbycustomer = :95 , misturnoversegment_id = :96 , depositstockallowed = :97 , seasonautoflagallowed = :98 , signaturerequired = :99 , weeklyMinInvAmount = :100 , outscanconfirmwearerchng = :101 , flatratedtypecharge = :102 , inscanbasedforecast = :103 , loadingprogram = :104 , directinvsalesnotes = :105 , directinvsalesnotestype = :106 , deliverynotewithprices = :107 , autoloadpacking = :108 , chargeprecharges = :109 , chargerestockingfee = :110 , chargestockcharge = :111 , plantdepartment_id = :112 , chargelosscharge = :113 , nationalaccount_id = :114 , alternatesorting = :115 , inheritmapricelist = :116 , restrictonmaproducts = :117 , LOSTDATE = :118 , CONTRACTWEEKSLEFT = :119 , LOSTREVENUEAMOUNT = :120 , chargerepaircog = :121 , completnesscheckcontainer = :122 , allocationmethod = :123 , cogallowedonlyplpc = :124 , standardsbasedon = :125 , COCNUMBER = :126 , PNNUMBER = :127 , SERVICEEMPLOYEE_ID = :128 , COMMONCUSTOMER_ID = :129 , BONUSAGREEMENT = :130 , ACQUISIONCODE_ID = :131 , CUSTOMERRANKING_ID = :132 , TDCCode = :133 , SENDINVOICETO = :134 , USEWEARERGARMENTSBUDGET = :135 , exportDateFin = :136 , allowothercontracts = :137 , allowotherpricelists = :138 , masteraccountrelation = :139 , priceconcerncode = :140 , invoicecouplingcode = :141 , SUPPRESSPRINTDELNOTE = :142 , SUPPRESSPRINTDELNOTEBATCH = :143 , customertype = :144 , orderingstockroom_id = :145 , SOLDTOCUSTOMER = :146 , BILLTOCUSTOMER = :147 , mininvamounttobereached = :148 , splitinvoicesperactivity = :149 , contractperiod = :150 , prolongationperiod = :151 , canceladvanceperiod = :152 , TAXEXEMPTCODE=:153 , contractusage = :154 , salesemployee1_id = :155 , salesemployee2_id = :156 , variableservices = :157 , exportdelnoteinfo = :158 , csvFileSeparator = :159 , chargeservicecosts = :160 , labelcolor = :161 , crmcustomernumber = :162 , printtaxonappendix = :163 , mininvoicepersplitgroup = :164 , internalbusinessunit_id = :165 , phaseddeliverywo = :166 , phaseddeliverysalesnote = :167 , DEFAULTINVOICEBUSINESSUNIT_ID = :168 , consignmentcontactperson_id = :169 , consignmentnotetoemail = :170 , linenOrderingMethod = :171 , CHARGELOSSFORPOOLITEMS = :172 , inoutscanconfirmdeptchange = :173 , OUTSCANCONFIRMDEPTCPCHANGE = :174 , directinvallservices = :175 , internalcalcpercentagelog = :176 , internalcalcpercentagecog =:177 , printloadoptimizerdocument = :178 , deliveryterm_id = :179 , informpriceupdate = :180 , allowpartialinvoicing = :181 , cleanReturnFreeOfCharge = :182 , siccode_id = :183 , mainordercutoffdate = :184 , mainorderexpecteddeliverydate = :185 , routeassistantonsitesoilscan = :186 , targetaccount = :187 , maincustomertype = :188 , sortcustomer_id = :189 , scrutinyexcludecheckbags = :190 , scrutinyexcludecheckdv = :191 , taxexemptcertificatedocument = :192 , taxexemptcertificatereceived = :193 , encryptedId = :194 , NOTCHARGEOUTSTANDINGLOANS = :195 , garmentbatchsorting = :196 , boxdeliverydocument = :197 , allowchargereturnrent = :198 , returnrentweeks = :199 , allowchargeminrent = :200 , minrentweeks = :201 , chargelossuniquepooldays = :202 , lossgracescheme_id = :203 , CUSTOMER.LOSSCHARGETRIGGER = :204 , preWashForGarments = :205 , bonuspaidto = :206 , bonuscustomer_id = :207 , bonuspayable = :208 , bonus = :209 , bonusinterval = :210 , bonusremark = :211 , othercustomer_id = :212 , secondlanguage_id = :213 , insurancestartdate = :214 , invwearerbreakdown = :215 , ediinfo_id = :216 , edivalue1 = :217 , edivalue2 = :218 , edivalue3 = :219 , edivalue4 = :220 , depreciationlosscharge = :221 , receivefromlaundryscan = :222 , sendtolaundryscan = :223 , repairspecificati ononinvoice = :224 , porequired = :225 , showreasoncodeoutscan = :226 , suppresscoldelnotelinen = :227 , allowrecalcplanneddata = :228 , salesorderdiscount = :229 , chargeamortizeduniqueitems = :230 , subcontractor = :231 , competitor_id = :232 , CNTUsageReportToEmail = :233 , CNTUsageReportContactPerson_ID = :234 , linenReduceFactor = :235 , contractcoefficient = :236 , usecontractcoefficient = :237 , returnsamecontainer = :238 , ownbatch = :239 , usespecificinvoicecurrency = :240 , invoicecurrency_id = :241 , customgarmentcharge = :242 , useprodsizesurchargepricing = :243 , LOSSCHARGESTARTDATE = :244 , STACKCHECKMIXEDPRODUCTS = :245 , autosoilpercentage = :246 , galaxieincinlosspoolws = :247 , CASHDELAYCHARGE = :248 , OWNBATCHLEVEL = :249 where CUSTOMER_ID = :250
9t6krz95fnhqt	select PRODUCT_ID, LANGUAGE_ID, DESCRIPTION, LONGDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP from PRODUCT_DESC where PRODUCT_ID = :1
a4sntxmta78y7	INSERT INTO SCAN (SCAN_ID, BUSINESSUNIT_ID, SCANTIMESTAMP, PRIMARYID, CUSTOMER_LINK_ID, WEAREREMPLOYMENT_LINK_ID, DISTRIBUTIONPOINT_LINK_ID, PRODUCT_LINK_ID, SIZEDEFINITION_LINK_ID, STAY_ID, STATUS_ID, SCANSTATION, ISSUEDATE, ROUTE_ID, REASONCODE_ID, CUSTOMEROWNED, YEAR, WEEK, DAYNO, QUALITYGRADE_ID, SYSTEMUSER_ID, TIMESTAMP, PREVSTAY_ID, TRANSACTIONTYPE_ID, DAYSLATE, UNIQUEITEM_LINK_ID, SECONDARYID, BATCHNUMBER, DELIVERYNOTENUMBER) VALUES (SEQ_SCAN.NEXTVAL, :B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , SYSDATE, :B8 , CASE WHEN :B7 IS NULL THEN :B6 ELSE 56 END, :B5 , :B4 , :B3 , :B2 , :B1 ) RETURNING SCAN_ID INTO :O0
a5ry5bk6jdap9	Begin lts_warning_reminder('noreply@cleanlease.com', 101, 'EDIFoutmeldingenXPI@cleanlease.com'); end;
acmvv4fhdc9zh	select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9, nvl(dflcollid, 16382), creappid, creverid, modappid, modverid, crepatchid, modpatchid from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
ad1n26shaunty	BEGIN DBMS_SESSION.SET_IDENTIFIER(null); DBMS_APPLICATION_INFO.SET_ACTION(null); DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null); END;
at9t6mv8721rw	BEGIN :1 := pack_multiread.f_domultireadunattended(:2 , :3 ); END;
b2stf1a9d2au6	SELECT io.internetorder_id, cust.customernumber, cust.name, dept.departmentnumber, dept.description, dip.distributionpoint_id, dip.distributionpointnumber, dip.description, io.timestamp, scs.packoncountday, 2, dept.customer_id, ( select count(1) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.code) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.description) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevi ce_id = scd.mobiledevice_id), scday.orderingdeadline , io.pdaexportstatus , scs.deliverydaydetermination, scday.DELIVERYDAY_ID, (SELECT DD.DESCRIPTION FROM DAY_DESC DD WHERE DD.DAY_ID = scday.DELIVERYDAY_ID AND DD.LANGUAGE_ID = :1 ) , scs.code, scs.description, io.extendedorderdeadline, io.skippedorder, bu.code, bu.description FROM INTERNETORDER IO, DISTRIBUTIONPOINT DIP, DEPARTMENT DEPT, CUSTOMER CUST, STOCKCOUNTSCHEDULE SCS, STOCKCOUNTSCHEDULEDIST SCDIST, STOCKCOUNTSCHEDULEDAY SCDAY, BUSINESSUNIT BU WHERE io.orderdate = :2 and io.status = 3 AND io.distributionpoint_link_id = dip.distributionpoint_id AND dip.department_id = dept.department_id AND dept.customer_id = cust.customer_id AND cust.defaultbusinessunit_id = bu.businessunit_id AND (exists (select 1 from CUSTOMERACTIVITIES CA where CA.PROCESSBUSINESSUNIT_ID = :3 and CA.CUSTOMER_ID = CUST.CUSTOMER_ID)) and dip.distributionpoint_id = scdist.distributionpoint_id AND scdist.stockcountschedu le_id = scday.stockcountschedule_id AND scday.day_id = :4 AND scdist.stockcountschedule_id = scs.stockcountschedule_id AND SCS.REPLENISHMENTTYPE = 0 and (io.confirmationtimestamp is null OR scday.orderingdeadline is null OR (TRUNC(io.confirmationtimestamp) < TRUNC(io.orderdate) OR (TRUNC(io.confirmationtimestamp) = TRUNC(io.orderdate) AND nvl(scday.orderingdeadline, io.confirmationtimestamp) is not null AND TO_CHAR(io.confirmationtimestamp, 'HH24:MI:SS') <= TO_CHAR(scday.orderingdeadline, 'HH24:MI:SS')))) AND not exists (SELECT 1 FROM internetorderline iol, deliveryvariations dv WHERE (dv.deliveryvariations_id = iol.deliveryvariations_id AND nvl(dv.packingnote_id, 0) > 0 AND iol.internetorder_id = io.internetorder_id )) AND not exists (select 1 from packingnote pn where (dip.distributionpoint_id = pn.distributionpoint_id AND pn.productiondate >= :5 AND pn.origin = 0 AND pn.status_id <> 8 and pn.creationtimestamp < io.timestamp AND pn.creationtime stamp IS NOT NULL)) UNION ALL SELECT io.internetorder_id, cust.customernumber, cust.name, dept.departmentnumber, dept.description, dip.distributionpoint_id, dip.distributionpointnumber, dip.description, io.timestamp, scs.packoncountday, 5, dept.customer_id, ( select count(1) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.code) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.description) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), scday.orderingdeadline , io.pdaexportstatus , scs.deliveryd aydetermination, scday.DELIVERYDAY_ID, (SELECT DD.DESCRIPTION FROM DAY_DESC DD WHERE DD.DAY_ID = scday.DELIVERYDAY_ID AND DD.LANGUAGE_ID = :6 ) , scs.code, scs.description, io.extendedorderdeadline, io.skippedorder, bu.code, bu.description FROM INTERNETORDER IO, DISTRIBUTIONPOINT DIP, DEPARTMENT DEPT, CUSTOMER CUST, STOCKCOUNTSCHEDULE SCS, STOCKCOUNTSCHEDULEDIST SCDIST, STOCKCOUNTSCHEDULEDAY SCDAY, BUSINESSUNIT BU WHERE io.orderdate = :7 and io.status = 3 AND io.distributionpoint_link_id = dip.distributionpoint_id AND dip.department_id = dept.department_id AND dept.customer_id = cust.customer_id AND cust.defaultbusinessunit_id = bu.businessunit_id AND (exists (select 1 from CUSTOMERACTIVITIES CA where CA.PROCESSBUSINESSUNIT_ID = :8 and CA.CUSTOMER_ID = CUST.CUSTOMER_ID)) and dip.distributionpoint_id = scdist.distributionpoint_id AND scdist.stockcountschedule_id = scday.stockcountschedule_id AND scday.day_id = :9 AND scdist.stockcountschedule_id = scs.stockcoun tschedule_id AND SCS.REPLENISHMENTTYPE = 0 and (io.confirmationtimestamp is not null AND (TRUNC(io.confirmationtimestamp) > TRUNC(io.orderdate) OR (TRUNC(io.confirmationtimestamp) = TRUNC(io.orderdate) AND nvl(scday.orderingdeadline, io.confirmationtimestamp) is not null AND TO_CHAR(io.confirmationtimestamp, 'HH24:MI:SS') > TO_CHAR(scday.orderingdeadline, 'HH24:MI:SS')))) AND not exists (SELECT 1 FROM internetorderline iol, deliveryvariations dv WHERE (dv.deliveryvariations_id = iol.deliveryvariations_id AND nvl(dv.packingnote_id, 0) > 0 AND iol.internetorder_id = io.internetorder_id )) AND not exists (select 1 from packingnote pn where (dip.distributionpoint_id = pn.distributionpoint_id AND pn.productiondate >= :10 AND pn.origin = 0 AND pn.status_id <> 8 and pn.creationtimestamp < io.timestamp AND pn.creationtimestamp IS NOT NULL)) UNION ALL SELECT io.internetorder_id, cust.customernumber, cust.name, dept.departmentnumber, dept.description, dip.distributionpoint_id, dip.distributionpointnumber, dip.description, io.timestamp, scs.packoncountday, 4, dept.customer_id, ( select count(1) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.code) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), ( select min(md.description) from mobiledevice md, stockcountdevice scd where scd.stockcountschedule_id = scs.stockcountschedule_id and md.mobiledevice_id = scd.mobiledevice_id), scday.orderingdeadline , io.pdaexportstatus , scs.deliverydaydetermination, scday.DELIVERYDAY_ID, (SELECT DD.DESCRIPTION FROM DAY_DESC DD WHERE DD.DAY_ID = scday.DELIVERYDAY_ID AND DD.LANGUAGE _ID = :11 ) , scs.code, scs.description, io.extendedorderdeadline, io.skippedorder, bu.code, bu.description FROM INTERNETORDER IO, DISTRIBUTIONPOINT DIP, DEPARTMENT DEPT, CUSTOMER CUST, STOCKCOUNTSCHEDULE SCS, STOCKCOUNTSCHEDULEDIST SCDIST, STOCKCOUNTSCHEDULEDAY SCDAY, BUSINESSUNIT BU WHERE io.orderdate = :12 AND io.status = 3 AND io.distributionpoint_link_id = dip.distributionpoint_id AND dip.department_id = dept.department_id AND dept.customer_id = cust.customer_id AND cust.defaultbusinessunit_id = bu.businessunit_id AND (exists (select 1 from CUSTOMERACTIVITIES CA where CA.PROCESSBUSINESSUNIT_ID = :13 and CA.CUSTOMER_ID = CUST.CUSTOMER_ID)) and dip.distributionpoint_id = scdist.distributionpoint_id AND scdist.stockcountschedule_id = scday.stockcountschedule_id AND scday.day_id = :14 AND scdist.stockcountschedule_id = scs.stockcountschedule_id AND SCS.REPLENISHMENTTYPE = 0 and not exists (SELECT 1 FROM internetorderline iol, deliveryvariations dv WHERE (dv.deliveryvariations_id = iol.deliveryvariations_id AND nvl(dv.packingnote_id, 0) > 0 AND iol.internetorder_id = io.internetorder_id )) AND exists (select 1 from packingnote pn where (dip.distributionpoint_id = pn.distributionpoint_id AND pn.productiondate >= :15 AND pn.origin = 0 AND pn.status_id <> 8 and pn.creationtimestamp < io.timestamp AND pn.creationtimestamp IS NOT NULL))
b572tsbk7bvs9	Begin ABSSOLUTE.WSP_SENDTRACK(:v0, :v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10, :v11, :v12, :v13, :v14, :v15, :v16, :v17); End;
b7ury3v7q4uqw	CALL ABSSOLUTE.GETGARMENTDATA(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44)
bcqnszh0aq01v	select DISTINCT C.CUSTOMERNUMBER from INTERNETORDER IO, DISTRIBUTIONPOINT DIP, DEPARTMENT DEP, CUSTOMER C, CUSTOMERINTERNETOPTIONS CIO where IO.DISTRIBUTIONPOINT_LINK_ID = DIP.DISTRIBUTIONPOINT_ID and DIP.DEPARTMENT_ID = DEP.DEPARTMENT_ID and DEP.CUSTOMER_ID = C.CUSTOMER_ID and C.CUSTOMER_ID = CIO.CUSTOMER_ID and CIO.STOCKCOUNTWEBPORTAL = 'Y' and IO.STATUS = 2 and IO.ORDERDATE > :1 and exists (SELECT 1 FROM WEBUSERCUSTOMER WUC WHERE WUC.WEBUSER_ID = :2 AND WUC.CUSTOMER_ID = C.CUSTOMER_ID) and (exists (select 1 from WEBUSERDEPARTMENT WDEP, WEBUSERCUSTOMER WCUST where WDEP.DEPARTMENT_ID = DEP.DEPARTMENT_ID and WCUST.WEBUSER_ID = :3 and WCUST.CUSTOMER_ID = DEP.CUSTOMER_ID and WDEP.WEBUSERCUSTOMER_ID = WCUST.WEBUSERCUSTOMER_ID) or not exists (select 1 from WEBUSERDEPARTMENT WDEP, WEBUSERCUSTOMER WCUST where WCUST.WEBUSER_ID = :4 and WCUST.CUS TOMER_ID = DEP.CUSTOMER_ID and WDEP.WEBUSERCUSTOMER_ID = WCUST.WEBUSERCUSTOMER_ID))
bmyy69rz6j0bp	select d.countdate, (select dd.description from day_desc dd where dd.day_id = scsd.day_id and dd.language_id = :1 ), d.countdate + scsd.deliveryday_id - scsd.day_id, (select dd.description from day_desc dd where dd.day_id = scsd.deliveryday_id and dd.language_id = :2 ), scs.code, scs.description, scs.stockcountschedule_id, scsd.day_id, scsd.compulsoryorderday, (select count(1) from stockcountscheduledist scsdpt, distributionpoint dip, department dpt, webuserdepartment wdep, webusercustomer wcust where dip.distributionpoint_id = scsdpt.distributionpoint_id and dpt.department_id = dip.department_id and dpt.customer_id = scs.customer_id and scsdpt.stockcountschedule_id = scs.stockcountschedule_id and wdep.department_id = d pt.department_id and wcust.webusercustomer_id = wdep.webusercustomer_id and wcust.webuser_id = :3 and dpt.active = 'Y' and dip.active = 'Y' and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = dip.distributionpoint_id)) diptstobecounted, (select count(1) from internetorder io, stockcountscheduledist scsdip , DISTRIBUTIONPOINT DIP, DEPARTMENT DPT, WEBUSERDEPARTMENT WDEP, WEBUSERCUSTOMER WCUST where scsdip.distributionpoint_id = io.distributionpoint_link_id and io.status = 1 and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and DIP.DISTRIBUTIONPOINT_ID = SCSDIP.DISTRIBUTIONPOINT_ID and DPT.DEPARTMENT_ID = DIP.DEPARTMENT_ID and DPT.CUSTOMER_ID = SCS.CUSTOMER_ID and WDEP.DEPARTMENT_ID = DPT.DEPARTMENT_ID and WCUST.WEBUSERCUSTOMER_ID = WDEP.WEBUSERCUSTOMER_ID and WCUST.WEBUSER _ID = :4 and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) diptsnotcounted, (select count(1) from internetorder io, stockcountscheduledist scsdip , DISTRIBUTIONPOINT DIP, DEPARTMENT DPT, WEBUSERDEPARTMENT WDEP, WEBUSERCUSTOMER WCUST where scsdip.distributionpoint_id = io.distributionpoint_link_id and DIP.DISTRIBUTIONPOINT_ID = SCSDIP.DISTRIBUTIONPOINT_ID and DPT.DEPARTMENT_ID = DIP.DEPARTMENT_ID and DPT.CUSTOMER_ID = SCS.CUSTOMER_ID and WDEP.DEPARTMENT_ID = DPT.DEPARTMENT_ID and WCUST.WEBUSERCUSTOMER_ID = WDEP.WEBUSERCUSTOMER_ID and WCUST.WEBUSER_ID = :5 and io.status = 2 and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) diptscounted, (select count(1) from internetorder io, stockcountscheduledist scsdip , DISTRIBUTIONPOINT DIP, DEPARTMENT DPT, WEBUSERDEPARTMENT WDEP, WEBUSERCUSTOMER WCUST where scsdip.distributionpoint_id = io.distributionpoint_link_id and io.status = 3 and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and DIP.DISTRIBUTIONPOINT_ID = SCSDIP.DISTRIBUTIONPOINT_ID and DPT.DEPARTMENT_ID = DIP.DEPARTMENT_ID and DPT.CUSTOMER_ID = SCS.CUSTOMER_ID and WDEP.DEPARTMENT_ID = DPT.DEPARTMENT_ID and WCUST.WEBUSERCUSTOMER_ID = WDEP.WEBUSERCUSTOMER_ID and WCUST.WEBUSER_ID = :6 and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) diptsconfirmed, scsd.orderingdeadline, (select max(io.extendedorderdeadline) from internetorder io, stockcountscheduledist scsdip where scsdip.distributionpoint_id = io.distributionpoint_link_id and io.status in (1, 2) and io.orderdate = d.countdate and scsdip.stockcountschedule_id = scs.stockcountschedule_id and exists (select 1 from productlistperdistribpoint plpd where plpd.distributionpoint_id = scsdip.distributionpoint_id)) extendeddeadline from (select trunc(:7 ) + day_id - 1 countdate from day order by day_id) d, stockcountschedule scs, stockcountscheduleday scsd where scsd.stockcountschedule_id = scs.stockcountschedule_id and scs.customer_id = :8 and scs.replenishmenttype = 0 and getabsdaynumber(countdate) = scsd.day_id AND EXISTS (SELECT 1 FROM standarddeliveryline sdl, productlistperdistribpoint plpd, stockcountscheduledist scsdist WHERE sdl.product listperdistribpoint_id = plpd.productlistperdistribpoint_id AND scsdist.distributionpoint_id = plpd.distributionpoint_id AND scsdist.stockcountschedule_id = scs.stockcountschedule_id AND (sdl.dateinactive > :9 AND sdl.dateactive <= :10 )) AND EXISTS (SELECT 1 FROM stockcountscheduledist d, distributionpoint dip WHERE d.stockcountschedule_id = scs.stockcountschedule_id AND d.distributionpoint_id = dip.distributionpoint_id AND dip.department_id IN (SELECT wd.department_id FROM webuserdepartment wd, webusercustomer wc WHERE wd.webusercustomer_id = wc.webusercustomer_id AND wc.webuser_id = :11 AND wc.customer_id = :12 ) ) order by d.countdate, scs.code
brq67u1z5x08q	select UNIQUERESIDENTIALITEMPRODUCT.UNIQUERESIDENTIALITEM_ID, UNIQUERESIDENTIALITEMPRODUCT.PRODUCT_ID, UNIQUERESIDENTIALITEMPRODUCT.SIZEDEFINITION_ID, UNIQUERESIDENTIALITEMPRODUCT.FINISHINGMETHOD_ID, SPECIFICCOLOR_ID, SPECIFICWASHINGPROCESS_ID, ASSIGNDATE, UNIQUERESIDENTIALITEM.SYSTEMUSER_ID, UNIQUERESIDENTIALITEM.TIMESTAMP, UNIQUERESIDENTIALITEM.BUSINESSUNIT_ID, UNIQUERESIDENTIALITEM.PRIMARYID, UNIQUERESIDENTIALITEM.SEQUENCENUMBER, UNIQUERESIDENTIALITEM.CLIENT_ID, UNIQUERESIDENTIALITEM.PRINTDATE, UNIQUERESIDENTIALITEM.STATUS_ID, UNIQUERESIDENTIALITEM.STATUSCHANGEDATE, UNIQUERESIDENTIALITEM.LASTSTATUS_ID, UNIQUERESIDENTIALITEM.STAY_ID, UNIQUERESIDENTIALITEM.LASTSTAYDATE, UNIQUERESIDENTIALITEM.LASTSTAY_ID, UNIQUERESIDENTIALITEM.LASTINSCANDATE, UNIQUERESIDENTIALITEM.LASTOUTSCANDATE, UNIQUERESIDENTIALITEM.LASTREWASHDATE, UNIQU ERESIDENTIALITEM.LASTREPAIRDATE, UNIQUERESIDENTIALITEM.NUMBEROFWASHES, UNIQUERESIDENTIALITEM.NUMBEROFREPAIRS, UNIQUERESIDENTIALITEM.NUMBEROFREWASHES, UNIQUERESIDENTIALITEM.LASTCHANGEDATE, UNIQUERESIDENTIALITEM.REMARK, UNIQUERESIDENTIALITEM.PRICE, S.CODE, (select DESCRIPTION from SIZEDEFINITION_DESC where SIZEDEFINITION_DESC.SIZEDEFINITION_ID=S.SIZEDEFINITION_ID and LANGUAGE_ID = 1), F.CODE, (select DESCRIPTION from FINISHINGMETHOD_DESC where FINISHINGMETHOD_DESC.FINISHINGMETHOD_ID=F.FINISHINGMETHOD_ID and LANGUAGE_ID = 1), P.CODE, (select DESCRIPTION from PRODUCT_DESC where PRODUCT_DESC.PRODUCT_ID=P.PRODUCT_ID and LANGUAGE_ID = 1), P.TYPEOFMERCHANDISE, (SELECT WP.CODE FROM WASHINGPROCESS WP WHERE WP.WASHINGPROCESS_ID = UNIQUERESIDENTIALITEMPRODUCT.SPECIFICWASHINGPROCESS_ID), (SELECT WPD.DESCRIPTION FROM WASHINGPROCESS_DESC WPD WHERE WPD.WASHINGPROCESS_ID = UNIQUERESIDENTIALITEMPRODUCT.SPECIFICWASHINGPROCESS_ID AND LANGUAGE_ID = :1 ), (SELECT COL.CODE FROM COLOR COL WHERE COL.COLOR_ID = UNIQUERESIDENTIALITEMPRODUCT.SPECIFICCOLOR_ID), (SELECT COLD.DESCRIPTION FROM COLOR_DESC COLD WHERE COLD.COLOR_ID =UNIQUERESIDENTIALITEMPRODUCT.SPECIFICCOLOR_ID AND COLD.LANGUAGE_ID = :2 ) from UNIQUERESIDENTIALITEMPRODUCT, UNIQUERESIDENTIALITEM, PRODUCT P, SIZEDEFINITION S, FINISHINGMETHOD F where UNIQUERESIDENTIALITEMPRODUCT.SIZEDEFINITION_ID = S.SIZEDEFINITION_ID AND UNIQUERESIDENTIALITEMPRODUCT.FINISHINGMETHOD_ID = F.FINISHINGMETHOD_ID AND UNIQUERESIDENTIALITEMPRODUCT.PRODUCT_ID = P.PRODUCT_ID AND UNIQUERESIDENTIALITEMPRODUCT.PRODUCT_ID = :3 AND UNIQUERESIDENTIALITEMPRODUCT.UNIQUERESIDENTIALITEM_ID IN (SELECT uniqueresidentialitem.uniqueresidentialitem_id FROM uniqueresidentialitem WHERE uniqueresidentialitem.client_id = :4 ) AND UNIQUERESIDENTIALITEM.UNIQUERESIDENTIALITEM_ID=UNIQUERESIDENTIALITEMPRODUCT.UNIQUERESIDENTIALITEM_ID
bt52xfvg688py	update SYSTEMUSER set usergroup_id = :1 , language_id = :2 , username = :3 , password = :4 , fullname = :5 , active = :6 , sysadmin = :7 , remark = :8 , contactmgrfullrights = :9 , creditcardauthorised = :10 , showcontactlogin = :11 , LASTLOGINTIMESTAMP = :12 , passwordchangetimestamp = :13 , lockedout = :14 , lastloginatworkstation = :15 , lastloginatappserver = :16 , lastincorrectlogintimestamp = :17 , incorrectloginssincepwchange = :18 , loggedin = :19 , passwordexpires = :20 , changepasswordnexlogin = :21 , employee_id = :22 , maychangedeliverywo = :23 , maychangedeliverysn = :24 , maychangestockroomforwo = :25 , maychangestockroomformo = :26 , maychangepriorityforwo = :27 , weightoverride = :28 , mayundostockreception = :29 , debugonserver = :30 , mayrundbscripts = :31 , oosallowed = :32 , ratificationauthorizati on = :33 , domainUser = :34 , overridepreqotherusers = :35 , updatedbysystemuser_id = :36 , timestamp = :37 , MANAGELICENSES = :38 , ACHAUTHORISED = :39 where systemuser_id = :40
cm4btc59pa27y	select A.BATCH_ID, A.BUSINESSUNIT_ID, A.YEAR, A.WEEK, A.BATCHNUMBER, A.BATCHCODE, A.DAY_ID, A.BATCHDATE, A.ORIGIN, A.STATUS, A.EXTRAHANDLING, A.BULKMASTERBATCH_ID, A.CLOSINGDATE, A.CLOSINGUSER_ID, A.CUSTOMER_LINK_ID, A.BATCHCOLOR_ID, A.SYSTEMUSER_ID, A.TIMESTAMP, A.EMPLOYEE_ID, A.ROUTE_ID, A.CREATIONTIMESTAMP, C.NAME, C.CUSTOMERNUMBER, B.BATCHCOLORNUMBER, B.DESCRIPTION, D.DAYNUMBER, (select DESCRIPTION from DAY_DESC where DAY_DESC.DAY_ID=A.DAY_ID and LANGUAGE_ID = 1), (select code from day_desc where day_desc.day_id = a.day_id and day_desc.language_id = 1), S.USERNAME, E.NAME, R.DESCRIPTION, A2.BATCHNUMBER, A.DIRECTINVOICETIMESTAMP, A.deliverydate, A.sequencenumber, A.remark, A.infection, (select count(1) from CLIENTDELIVERYNOTE CDN where CDN.BATCH_ID = A.BATCH_ID), (SELECT COUNT(DISTINCT cdn.deliverydate) FROM cl ientdeliverynote cdn WHERE cdn.batch_id = A.BATCH_ID) ddates, (SELECT MIN(cdn.deliverydate) FROM clientdeliverynote cdn WHERE cdn.batch_id = A.BATCH_ID) minDelDate, (select count(*) from RESIDENTIALITEMSCAN RIS where RIS.BATCH_ID = A.BATCH_ID and RIS.TRANSACTIONTYPE_ID = 1) INSCANS, (select count(*) from RESIDENTIALITEMSCAN RIS where RIS.BATCH_ID = A.BATCH_ID and RIS.TRANSACTIONTYPE_ID = 6) SORTSCANS, (select count(*) from RESIDENTIALITEMSCAN RIS where RIS.BATCH_ID = A.BATCH_ID and RIS.TRANSACTIONTYPE_ID = 2) OUTSCANS from BATCH A left outer join CUSTOMER C on A.CUSTOMER_LINK_ID = C.CUSTOMER_ID left outer join SYSTEMUSER S on A.CLOSINGUSER_ID = S.SYSTEMUSER_ID left outer join EMPLOYEE E on A.EMPLOYEE_ID = E.EMPLOYEE_ID left outer join ROUTE R on A.ROUTE_ID = R.ROUTE_ID left outer join BATCH A2 on A.BULKMASTERBATCH_ID = A2.BATCH_ID join BATCHCOLOR B on A.BATCHCOLOR_ID = B.BATCHCOLOR_ID join DAY D on A.DAY_ID = D.DAY_ID where 0 = 0 AND A.YEAR = :1 AND A.WEEK = :2 AND A. BUSINESSUNIT_ID = :3 AND A.STATUS IN (:4 , :5 )
d1y55nnyc86fa	SELECT "CUSTOMER_ID", "DAY_ID", "DEPNR", "DEPDESC", "DACTIVE", "DPNR", "DPDESC", "DPACTIVE", "ACTIVITYCODE", "ACTIVITY", "PRODCODE", "PRODDESC", "SIZECODE", "SIZE_SEQ", "ORDERMETHOD", "MA", "DI", "WO", "DO", "VR", "ZA", "ZO" FROM "ABSSOLUTE"."V_DWH_RTCV_05" "Y" WHERE "DACTIVE"='Y' AND "DPACTIVE"='Y'
dgzttrtzn02nt	update UNIQUEITEM set primaryid = :1 , idcodesequencenumber = :2 , statuschangedate = :3 , staychangedate = :4 , lastinscandate = :5 , lastoutscandate = :6 , lastscandate = :7 , iteminfoupdatedate = :8 , poolitem = :9 , washestotal = :10 , repairstotal = :11 , rewashtotal = :12 , customerowned = :13 , businessunit_id = :14 , status_id = :15 , prevstatus_id = :16 , prevstay_id = :17 , stay_id = :18 , product_id = :19 , sizedefinition_id = :20 , finishingmethod_id = :21 , relatedcustomer_id = :22 , distributionpoint_id = :23 , lastdistributionpoint_link_id = :24 , planneddeliverydate = :25 , deliveryfromstock = :26 , startrentfrom = :27 , unrelatedloan_id = :28 , systemuser_id = :29 , timestamp = :30 , residualvalue = :31 , replacementprice = :32 , residualvalueamount = :33 , activestocklocation_id = :34 , lea seweeks = :35 , lastweareremployment_link_id = :36 , currentuniquebundle_id = :37 , SUBSTITUTEFORPRODUCT_ID = :38 , SUBSTITUTEFORSIZEDEFINITION_ID = :39 , SUBSTITUTEWORKORDERLINE_ID = :40 , batchnumber = :41 , EXPIRATIONDATE = :42 , LASTDELIVERYNOTENUMBER = :43 , REPLACEMENTWORKORDER_ID = :44 where uniqueitem_id = :45
f2uctzdw3zj9s	SELECT REPORTPARAM.COPY_NUMBER, REPORTPARAM.NUMBER_VALUE1, REPORTPARAM.STRING_VALUE1, V_DELIVERYNOTE_LTS.NOTENUMBER, V_DELIVERYNOTE_LTS.CUSTOMERNUMBER, V_DELIVERYNOTE_LTS.CUSTOMERNAME, V_DELIVERYNOTE_LTS.CUSTOMERSTREET, V_DELIVERYNOTE_LTS.CUSTOMERZIP, V_DELIVERYNOTE_LTS.CUSTOMERCITY, V_DELIVERYNOTE_LTS.DEPARTMENTNUMBER, V_DELIVERYNOTE_LTS.DEPARTMENTDESCRIPTION, V_DELIVERYNOTE_LTS.DISTRIBUTIONPOINTNUMBER, V_DELIVERYNOTE_LTS.DISTRIBUTIONPOINTDESCRIPTION, V_DELIVERYNOTE_LTS.DELIVERYPOINTCODE, V_DELIVERYNOTE_LTS.DELIVERYPOINTDESCRIPTION, V_DELIVERYNOTE_LTS.TRANSPORTARTICLEDESCRIPTION, V_DELIVERYNOTE_LTS.OCCUPANCY, V_DELIVERYNOTE_LTS.DELIVERYDATE, V_DELIVERYNOTE_LTS.REMARK, V_DELIVERYNOTE_LTS.PROCESSBUDESCRIPTION, V_DELIVERYNOTE_LTS.WEIGHTUNIT, V_DELIVERYNOTE_LTS.PRINTDELNOTEPERCONTAINER, V_DELIVERYNOTE_LTS.CONTAINERRECORDINGACTIVE, V_DELIVERYNOTE_LTS.PRINTDEL NOTEZEROLINES, V_DELIVERYNOTE_LTS.ROUTENUMBER, V_DELIVERYNOTE_LTS.STOPNUMBER, V_DELIVERYNOTE_LTS.PACKINGNOTE_ID, V_DELIVERYNOTE_LTS.ORIGIN, V_DELIVERYNOTE_LTS.DAYDESCRIPTION, V_DELIVERYNOTE_LTS.WEEK, V_DELIVERYNOTE_LTS.LANGUAGECODE, V_DELIVERYNOTE_LTS.CUSTOMERLANGUAGECODE, V_DELIVERYNOTE_LTS.PRODUCTIONDATE, V_DELIVERYNOTE_LTS.SCANSONDELIVERYNOTE, V_DELIVERYNOTE_LTS.NUMBEROFGARMENTSINLOCKER, V_DELIVERYNOTE_LTS.NUMBEROFLOCKERS, V_DELIVERYNOTE_LTS.NOOFCONTAINERS, V_DELIVERYNOTE_LTS.PLANT_DEP_CODE, V_DELIVERYNOTE_LTS.FREEOFCHARGEREASON_ID, V_DELIVERYNOTE_LTS.NUMBEROFPOSSIBLELINESWITHSCANS, V_DELIVERYNOTE_LTS.PRICESCALCULATED, V_DELIVERYNOTE_LTS.TOTALAMOUNT, V_DELIVERYNOTE_LTS.TOTALAMOUNTEXCLUDINGVAT, V_DELIVERYNOTE_LTS.DECIMALS, V_DELIVERYNOTE_LTS.INVOICEAMOUNTDECIMALS, V_DELIVERYNOTE_LTS.CURRENCYCODE, V_DELIVERYNOTE_LTS.PUTUPREPORTPERCUSTOMER, V_DELIVERYNOTE_LTS.USAGEDAYDESC, V_DELIVERYNOTE_LTS.PURCHASEORDERNO, V_DELIVERYNOTE_LTS.PRD500AMNT, V_DELIVERYNOTE_LTS.DRIVINGDAY_DESC, V_DELIVERYN OTE_LTS.DN_LAYOUTCODE, V_DELIVERYNOTE_LTS.LABELCOLOR_DISTR, V_DELIVERYNOTE_LTS.LABELCOLOR_CUST FROM REPORTPARAM REPORTPARAM INNER JOIN V_DELIVERYNOTE_LTS V_DELIVERYNOTE_LTS ON REPORTPARAM.NUMBER_VALUE=V_DELIVERYNOTE_LTS.NOTENUMBER WHERE (((V_DELIVERYNOTE_LTS.CUSTOMERLANGUAGECODE = REPORTPARAM.STRING_VALUE))) AND ((REPORTPARAM.REPORTPARAM_ID = :"SYS_B_0")) AND ((V_DELIVERYNOTE_LTS.CUSTOMERLANGUAGECODE = V_DELIVERYNOTE_LTS.LANGUAGECODE))
f66v6w7fgd2xs	select A.BATCH_ID, A.BUSINESSUNIT_ID, A.YEAR, A.WEEK, A.BATCHNUMBER, A.BATCHCODE, A.DAY_ID, A.BATCHDATE, A.ORIGIN, A.STATUS, A.EXTRAHANDLING, A.BULKMASTERBATCH_ID, A.CLOSINGDATE, A.CLOSINGUSER_ID, A.CUSTOMER_LINK_ID, A.BATCHCOLOR_ID, A.SYSTEMUSER_ID, A.TIMESTAMP, A.EMPLOYEE_ID, A.ROUTE_ID, A.CREATIONTIMESTAMP, C.NAME, C.CUSTOMERNUMBER, B.BATCHCOLORNUMBER, B.DESCRIPTION, D.DAYNUMBER, (select DESCRIPTION from DAY_DESC where DAY_DESC.DAY_ID=A.DAY_ID and LANGUAGE_ID = 1), (select code from day_desc where day_desc.day_id = a.day_id and day_desc.language_id = 1), S.USERNAME, E.NAME, R.DESCRIPTION, A2.BATCHNUMBER, A.DIRECTINVOICETIMESTAMP, A.deliverydate, A.sequencenumber, A.remark, A.infection, (select count(1) from CLIENTDELIVERYNOTE CDN where CDN.BATCH_ID = A.BATCH_ID), (SELECT COUNT(DISTINCT cdn.deliverydate) FROM cl ientdeliverynote cdn WHERE cdn.batch_id = A.BATCH_ID) ddates, (SELECT MIN(cdn.deliverydate) FROM clientdeliverynote cdn WHERE cdn.batch_id = A.BATCH_ID) minDelDate, (select count(*) from RESIDENTIALITEMSCAN RIS where RIS.BATCH_ID = A.BATCH_ID and RIS.TRANSACTIONTYPE_ID = 1) INSCANS, (select count(*) from RESIDENTIALITEMSCAN RIS where RIS.BATCH_ID = A.BATCH_ID and RIS.TRANSACTIONTYPE_ID = 6) SORTSCANS, (select count(*) from RESIDENTIALITEMSCAN RIS where RIS.BATCH_ID = A.BATCH_ID and RIS.TRANSACTIONTYPE_ID = 2) OUTSCANS from BATCH A left outer join CUSTOMER C on A.CUSTOMER_LINK_ID = C.CUSTOMER_ID left outer join SYSTEMUSER S on A.CLOSINGUSER_ID = S.SYSTEMUSER_ID left outer join EMPLOYEE E on A.EMPLOYEE_ID = E.EMPLOYEE_ID left outer join ROUTE R on A.ROUTE_ID = R.ROUTE_ID left outer join BATCH A2 on A.BULKMASTERBATCH_ID = A2.BATCH_ID join BATCHCOLOR B on A.BATCHCOLOR_ID = B.BATCHCOLOR_ID join DAY D on A.DAY_ID = D.DAY_ID where 0 = 0 AND A.YEAR = :1 AND A.WEEK = :2 AND A. BUSINESSUNIT_ID = :3 AND A.STATUS <> :4
f89svdq5a08cg	SELECT * FROM ABSSOLUTE.V_WSP_CLIENTPRODUCTS WHERE TIMESTAMP > TO_DATE(:"SYS_B_0", :"SYS_B_1") ORDER BY TIMESTAMP
g0rd3cx3f794t	SELECT V_CLIENTDELIVERYNOTE_LTS.PRINTPRICEONDELNOTE, V_CLIENTDELIVERYNOTE_LTS.PRICEDECIMALS, V_CLIENTDELIVERYNOTE_LTS.AMOUNTDECIMALS, V_CLIENTDELIVERYNOTE_LTS.PRICE, V_CLIENTDELIVERYNOTE_LTS.PRICEONDELNOTE, V_CLIENTDELIVERYNOTE_LTS.WEIGHTPRICE, V_CLIENTDELIVERYNOTE_LTS.SPECIALCHARGEINVAMOUNT, V_CLIENTDELIVERYNOTE_LTS.SPECIALCHARGEAMOUNT, V_CLIENTDELIVERYNOTE_LTS.LABELSAMOUNT, V_CLIENTDELIVERYNOTE_LTS.TAXAMOUNT, V_CLIENTDELIVERYNOTE_LTS.NOOFSPECCHARGE, V_CLIENTDELIVERYNOTE_LTS.NOOFTAXES, V_CLIENTDELIVERYNOTE_LTS.CLIENTDELIVERYNOTE_ID, V_CLIENTDELIVERYNOTE_LTS.CUSTOMERNUMBER, V_CLIENTDELIVERYNOTE_LTS.CUSTOMERNAME, V_CLIENTDELIVERYNOTE_LTS.DEPARTMENTNUMBER, V_CLIENTDELIVERYNOTE_LTS.DEPARTMENTDESC, V_CLIENTDELIVERYNOTE_LTS.CLIENTNUMBER, V_CLIENTDELIVERYNOTE_LTS.CLIENTNAME, V_CLIENTDELIVERYNOTE_LTS.CLIENTSEARCHNAME, V_CLIENTDELIVERYNOTE_LTS.ROOM, V_CLIENTDEL IVERYNOTE_LTS.STREET, V_CLIENTDELIVERYNOTE_LTS.ZIPCODE_CITY, V_CLIENTDELIVERYNOTE_LTS.CLIENTDELIVERYNOTENUMBER, V_CLIENTDELIVERYNOTE_LTS.DELIVERYDATE, V_CLIENTDELIVERYNOTE_LTS.BOXNUMBER, V_CLIENTDELIVERYNOTE_LTS.GROSSWEIGHT, V_CLIENTDELIVERYNOTE_LTS.WEIGHTDECIMALS, V_CLIENTDELIVERYNOTE_LTS.PRODUCTCODE, V_CLIENTDELIVERYNOTE_LTS.SIZECODE, V_CLIENTDELIVERYNOTE_LTS.FINISHINGMETHODCODE, V_CLIENTDELIVERYNOTE_LTS.FINISHINGMETHODDESC, V_CLIENTDELIVERYNOTE_LTS.PRODUCTDESC, V_CLIENTDELIVERYNOTE_LTS.QTYDELIVERED, V_CLIENTDELIVERYNOTE_LTS.PRINT_V_CDN_OUTSCANNEDID, V_CLIENTDELIVERYNOTE_LTS.PRINTIDCODE, V_CLIENTDELIVERYNOTE_LTS.LANGUAGECODE, V_CLIENTDELIVERYNOTE_LTS.PRODCUSTOM_CODE, V_CLIENTDELIVERYNOTE_LTS.PRODCUSTOM_DESC, V_CLIENTDELIVERYNOTE_LTS.SEQUENCENUMBER, V_CLIENTDELIVERYNOTE_LTS.BU_DESC, V_CLIENTDELIVERYNOTE_LTS.PLANT_PHONE, V_CLIENTDELIVERYNOTE_LTS.PLANT_EMAIL, V_CLIENTDELIVERYNOTE_LTS.ACTIVITY_CODE_PRODUCT, V_CLIENTDELIVERYNOTE_LTS.ACTIVITY_DESC_PRODUCT, V_CLIENTDELIVERYNOTE_LTS.REGUNIT, V_CLIENTDELIVERYNOTE_LTS.CLIENTREMARK, V_CLIENTDELIVERYNOTE_LTS.LABELLAYOUTCODE, V_CLIENTDELIVERYNOTE_LTS.CUSTOMER_FLEXFIELD, V_CLIENTDELIVERYNOTE_LTS.CLIENT_FLEXFIELD, V_CLIENTDELIVERYNOTE_LTS.XBOX, REPORTPARAM.NUMBER_VALUE1 FROM V_CLIENTDELIVERYNOTE_LTS V_CLIENTDELIVERYNOTE_LTS INNER JOIN REPORTPARAM REPORTPARAM ON V_CLIENTDELIVERYNOTE_LTS.CLIENTDELIVERYNOTE_ID=REPORTPARAM.NUMBER_VALUE WHERE ((REPORTPARAM.REPORTPARAM_ID = :"SYS_B_0")) AND ((V_CLIENTDELIVERYNOTE_LTS.LANGUAGECODE = :"SYS_B_1"))
g0t052az3rx44	select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/*MAXSB1MINAL*/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0), nvl(evaledition#, 1), nvl(unusablebefore#, 0), nvl(unusablebeginning#, 0), case when (type# in (1, 8, 9, 96, 112)) then nvl(collid, 16382) else 0 end case, nvl(collintcol#, 0), nvl(acdrrescol#, 0), nvl(spare7, 0), nvl(spare9, 0), nvl(spare10, 0) from col$ where obj#=:1 order by intcol#
g70rt7a38vddu	select A.NONBUSINESSDAY_ID, A.NONBUSINESSDAY, A.CUSTOMER_ID, A.DEPARTMENT_ID, A.PLANT_ID, A.STOCKROOM_ID, A.CLOSESTATUS, A.MOVEDAYS, A.SYSTEMUSER_ID, A.TIMESTAMP from NONBUSINESSDAY A WHERE A.PLANT_ID = :1 AND A.CUSTOMER_ID is null and A.DEPARTMENT_ID is null UNION select A.NONBUSINESSDAY_ID, A.NONBUSINESSDAY, A.CUSTOMER_ID, A.DEPARTMENT_ID, A.PLANT_ID, A.STOCKROOM_ID, A.CLOSESTATUS, A.MOVEDAYS, A.SYSTEMUSER_ID, A.TIMESTAMP from NONBUSINESSDAY A WHERE A.CUSTOMER_ID = :2 AND A.DEPARTMENT_ID is null UNION select A.NONBUSINESSDAY_ID, A.NONBUSINESSDAY, A.CUSTOMER_ID, A.DEPARTMENT_ID, A.PLANT_ID, A.STOCKROOM_ID, A.CLOSESTATUS, A.MOVEDAYS, A.SYSTEMUSER_ID, A.TIMESTAMP from NONBUSINESSDAY A WHERE A.DEPARTMENT_ID = :3 order by 2 ASC
g9c1x1mv0ku13	select PRODUCT_ID, DOCUMENTTYPE, FILENAME, SYSTEMUSER_ID, TIMESTAMP from PRODUCTINFO where PRODUCT_ID = :1
gcmy14qw4a6cw	select product_id, dateactive, dateinactive, finishingmethod_id, codeuniquely , soilweight, customer_id, onsiteinventorycount, sizedefinition_id, systemuser_id, rentpriceperfrequency , changesperweek, percentageafterdepreciation, timestamp, billingmethod, soilcountmethod, srcmethod , cleancount, srcpercentagesoilcount, srcpercentageinventory, srcpercentagebilledqty, CONTRACTPURCHASEORDER_ID, CONTRACTPURCHASEORDERLINE_ID, srcquantity, srcfrequency, chargeoveruse , chargestockroominventory, minimumturnaround, minimumquantity, qtyfreeofcharge, customcode, customdescription , custombundlesize, gaincompetitor_id, gainreason_id, lostreason_id, lostcompetitor_id, residualvaluescheme_id , delayedevenexchange, inventorybillingtype, minimumfrequency, deliverreplacementqty, emblemtemplate_id, washingprocess_id , automaticsales, customerrelated, autoreplafterlifetime, chargerentinactiveseason, seasontype_id, chargewashespack , raggeditemaction, rentbusinessunit_id, confirmproductonpda, commissionpayable, commissionenddate, autosoil, autosoilpercentage, chargelossforpool, defaultsupplier_id , DEFAULTRETURNGRADE_ID, CODELABELLAYOUT_ID, NAMELABELLAYOUT_ID, freeCleanChange, RESETRVUPONREISSUE, ENDRENTDATEMETHOD, SEASONALRENT, SHOWFOCRENTONINVOICE, ALLOWPREWASH , GDIMPORTRELEVANT, TOTALINCIRCULATION, TOTALINCIRCULATIONCHARGED , CLIENTFILTERSTANDARD, SRCPERCENTAGEDELIVEREDQTY, BM1RENTCPWMAX, SUPPRESSONPACKINGREPORTS, ( select /*+ ordered use_nl (distributionpoint, productlistperdistribpoint, rentalqtyadjustment) index (department XIF51DEPARTMENT) index (distributionpoint XAK2DISTRIBUTIONPOINT) index (productlistperdistribpoint XAK2PRODUCTLISTPERDISTRIBPOINT) index (rentalqtyadjustment XAK1RENTALQTYADJUSTMENT) */ coalesce(sum(RENTALQTYADJUSTMENT.QUANTITY), 0) from department, distributionpoint, productlistperdistribpoint, rentalqtyadjustment where RENTALQTYADJUSTMENT.PRODUCTLISTPERDISTRIBPOINT_ID = PRODUCTLISTPERDISTRIBPOINT.PRODUCTLISTPERDISTRIBPOINT_ID and PRODUCTLISTPERDISTRIBPOINT.DISTRIBUTIONPOINT_ID = DISTRIBUTIONPOINT.DISTRIBUTIONPOINT_ID and DISTRIBUTIONPOINT.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID and DEPARTMENT.CUSTOMER_ID = PRODUCTLISTPERCUSTOMER.CUSTOMER_ID and PRODUCTLISTPERDISTRIBPOINT.PRODUCT_ID = PRODUCTLISTPERCUSTOMER.PRODUCT_ID and PRODUCTLISTPERDISTRIBPOINT.SIZEDEFINITION_ID = PRODUCTLISTPERCUSTOMER.SIZEDEFINITION_ID and PRODUCTLISTPERDISTRIBPOINT.FINISHINGMETHOD_ID = PRODUCTLISTPERCUSTOMER.FINISHINGMETHOD_ID and ADJUSTMENTDATE <= :1 ) , specificputuptype, quantityreturned, straightevenexchange, eannumber, finishingcode_id , freeqtyfrequency, SALESEMPLOYEE_ID, CHARGEWASHESONLYFORCOG, RETURNSTOCKALLOWED, maxwashes, NUMBERWASHESSPECIALTREATMENT , DEPOSITSTOCKALLOWED, DEPOSITSTOCKLOCATION, DEPOSITSTOCKCHARGEPERCENTAGE, BUDGETRELEVANT, deedeliveryscheme_id, ALLOWTOREPLACE, REMARK , WEARERGARMENTSBUDGETVALUE, LOCATION, CHARGERESVALUENEWONLY, UNIQUELOCATION, AUTOREPLACEMENTDIFFERENCE, PRICEUSAGE, INVOICEFREQRELATEDRENTPRICE , INVWASHBASEDONSOIL, SRCCHARGERENT, SPECIFICFILLPERCENTAGE, IMAGECARE, TREATMENTCHECKSCHEME_ID, DELIVERYLABELLAYOUT_ID, SALESEMPLOYEE2_ID , ALLOWRETURNTOSTOCK, CHARGEEXCESS, NOTCHARGEOUTSTANDINGLOANS, MINRESIDUALVALUEAMOUNT, CHARGERENTNOTRETURNED, BM1FIXEDWEARERS , STERILISATIONREQUIRED, TREATMENTSPERSUBCONTRACTOR_ID, MAXIMUMNUMBEROFREPAIRS, PRINTREMARKONINVOICE, USECHIPPINGRATEDEE , USECHIPPINGRATEEXCHCART, CHARGERENTONSTOCK, RENTONSTOCKSTOCKCHARGEPERCENTAGE , MINQTYTODELIVER, MAXQTYTODELIVER , ORDERVIAWEB, DEFAULTSTOCKROOM_ID, FIXEDSTOCKROOM, CUSTOMERCOSTSPECIFICATION_ID, CHARGESERVICECOSTS, CHARGESERVICECOSTSFOR, SHORTCOUNTCODE, NANNUMBER , SPECIFICWEIGHIN GCATEGORY_ID, COSTPRICEINCLWASHSALESPRICE, COSTPRICEINCLRENTPRICE , COSTPRICEINCLWEIGHTPRICE, COSTPRICEINCLCOSTREPORTPRICE, INTERNALCALCPERCENTAGELOG, INTERNALCALCPERCENTAGECOG, DEEDELIVERYFREQUENCY, CHARGERENTNORETURNPOOLAFTERWKS , ALLOWMODIFICATIONSFORSALES, ALLOWMODIFICATIONSFORRENT, BUDGETUSAGE, CUSTOMERBUDGETDEFINITION_ID, RENTCUSTBUDGETDEFINITION_ID, SALESCUSTBUDGETDEFINITION_ID , BM4ALLOWSALES, NOOFWASHESNOCHECKPOINT, CHARGELOSSUNIQUEPOOLDAYS, SELFPAYMENT, DEPRECIATIONLOSSSCHEME_ID from PRODUCTLISTPERCUSTOMER where productlistpercustomer_id = :2
gczqpps1bt3k2	SELECT MIN(PLPC.PRODUCTLISTPERCUSTOMER_ID) FROM PRODUCTLISTPERCUSTOMER PLPC WHERE PLPC.CUSTOMER_ID = :B3 AND PLPC.PRODUCT_ID = :B2 AND PLPC.SIZEDEFINITION_ID = :B1 AND PLPC.DATEACTIVE < SYSDATE AND PLPC.DATEINACTIVE > SYSDATE
gyvm9dpm28p60	select PS.SIZEDEFINITION_ID, PS.SEQUENCENUMBER, PS.SPECIALSIZE, PS.PACKINGAMOUNT, PS.THEORETICALWEIGHT, PS.PROVISIONGROUP_ID, PG.CODE, PS.LegacyCode, PS.SQUAREMETERS, PS.MAXCARTQTY, PS.EXECUTEDBY, PS.ACTIVE, PS.SIZEBILLINGGROUP_ID, PS.SYSTEMUSER_ID, PS.TIMESTAMP, SDEF.CODE, (select DESCRIPTION from SIZEDEFINITION_DESC where SIZEDEFINITION_DESC.SIZEDEFINITION_ID=PS.SIZEDEFINITION_ID and LANGUAGE_ID = :1 ), SDEF.UNFINISHEDLENGTH, SDEF.LEGACYSIZECODE, SDEF.WIDTH, SDEF.LENGTH, SDEF.USEDFOREMBLEM , SAPS.SORTADDRESS , PS.FIXEDSTOCKROOM, PS.DEFAULTSTOCKROOM_ID, (SELECT S.CODE || '<*>' || S.NAME FROM STOCKROOM S WHERE S.STOCKROOM_ID = PS.DEFAULTSTOCKROOM_ID) , PS.STANDARDLENGTH from PRODUCTSIZE PS left outer join SORTADDRESSPERPRODUCTSIZE SAPS on PS.PRODUCT_ID = SAPS.PRODUCT_ID AND PS.SIZEDEFINITION_ID = SAPS.SIZEDEFINITION_ID and SAPS.PLANT_ID = :2 left outer join PROVISIONGROUP PG on PS.PROVISIONGROUP_ID = PG.PROVISIONGROUP_ID, SIZEDEFINITION SDEF where PS.PRODUCT_ID = :3 and PS.SIZEDEFINITION_ID = SDEF.SIZEDEFINITION_ID order by SEQUENCENUMBER

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Key Instance Activity Stats
• 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

Instance Activity Stats

• Ordered by statistic name