WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
KLASH	1701927951	klash	1	06-Jan-15 15:01	11.2.0.4.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
oradb11	Linux x86 64-bit	48	24	2	126.13

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	3204	08-Jan-15 11:00:15	1007	6.5
End Snap:	3205	08-Jan-15 12:00:31	1259	6.4
Elapsed:		60.26 (mins)
DB Time:		317.79 (mins)

Report Summary

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	5.3	0.3	0.00	0.00
DB CPU(s):	4.9	0.3	0.00	0.00
Redo size (bytes):	8,615,639.5	491,932.4
Logical read (blocks):	542,404.1	30,970.0
Block changes:	73,204.7	4,179.8
Physical read (blocks):	931.8	53.2
Physical write (blocks):	742.4	42.4
Read IO requests:	71.5	4.1
Write IO requests:	62.9	3.6
Read IO (MB):	7.3	0.4
Write IO (MB):	5.8	0.3
User calls:	1,511.5	86.3
Parses (SQL):	291.2	16.6
Hard parses (SQL):	12.7	0.7
SQL Work Area (MB):	190.8	10.9
Logons:	0.5	0.0
Executes (SQL):	14,145.9	807.7
Rollbacks:	1.4	0.1
Transactions:	17.5

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.94	In-memory Sort %:	100.00
Library Hit %:	99.74	Soft Parse %:	95.65
Execute to Parse %:	97.94	Latch Hit %:	99.98
Parse CPU to Parse Elapsd %:	93.71	% Non-Parse CPU:	97.90

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
48	24	2	9.20	10.78	10.1	0.5	1.7	89.4

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
10.3	97.4	0.0

IO Profile

	Read+Write Per Second	Read per Second	Write Per Second
Total Requests:	161.7	74.0	87.6
Database Requests:	134.4	71.5	62.9
Optimized Requests:	0.0	0.0	0.0
Redo Requests:	23.6	0.0	23.6
Total (MB):	21.6	7.3	14.3
Database (MB):	13.1	7.3	5.8
Optimized Total (MB):	0.0	0.0	0.0
Redo (MB):	8.5	0.0	8.5
Database (blocks):	1,674.1	931.8	742.4
Via Buffer Cache (blocks):	1,075.2	332.9	742.3
Direct (blocks):	598.9	598.9	0.0

Memory Statistics

	Begin	End
Host Mem (MB):	129,156.5	129,156.5
SGA use (MB):	43,008.0	43,008.0
PGA use (MB):	3,540.5	4,121.5
% Host Mem used for SGA+PGA:	36.04	36.49

Cache Sizes

	Begin	End
Buffer Cache:	25,600M	25,600M	Std Block Size:	8K
Shared Pool Size:	12,361M	12,334M	Log Buffer:	147,576K

Shared Pool Statistics

	Begin	End
Memory Usage %:	74.97	74.80
% SQL with executions>1:	93.18	94.27
% Memory for SQL w/exec>1:	88.94	89.77

Main Report

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

Wait Events Statistics

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

Time Model Statistics

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

Back to Wait Events Statistics
Back to Top

Operating System Statistics

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

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 99.7% of Total DB time 19,067.11 (s)
• Total FG Wait Time: 1,160.79 (s) DB CPU time: 17,840.64 (s)

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

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

Back to Wait Events Statistics
Back to Top

Background Wait Events

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

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

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

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

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

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

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

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 min to 1 hr)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

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

Back to Wait Events Statistics
Back to Top

SQL Statistics

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

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
• %Total - Elapsed Time as a percentage of Total DB time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 76.0% of Total DB Time (s): 19,067
• Captured PL/SQL account for 36.6% of Total DB Time (s): 19,067

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 80.3% of Total CPU Time (s): 17,841
• Captured PL/SQL account for 38.9% of Total CPU Time (s): 17,841

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

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: 1,961,266,316
• Captured SQL account for 68.4% 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: 3,369,251
• Captured SQL account for 42.9% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

• UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
• %Opt - Optimized Reads as percentage of SQL Read Requests
• %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
• Total Physical Read Requests: 258,368
• Captured SQL account for 4.0% of Total
• Total UnOptimized Read Requests: 258,368
• Captured SQL account for 4.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: 51,149,745
• Captured SQL account for 9.5% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 1,052,755
• Captured SQL account for 55.4% 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

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
0307xvv5xhu0f	SELECT ART_DESC FROM MKT_ARTICLE WHERE ART_ID = :b1
0cju83fh33y95	SELECT COUNT(*) FROM (SELECT DISTINCT DATED FROM OPR_WAGES_EMP_MASTER WHERE TO_DATE(DATED, 'DD-MM-RR') BETWEEN :b1 AND :b2 )
0hdxxufz5jb36	INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."YARN_BAG_STK_LOC" select t.LOC_ID, t.BAG_NO, sum(t.BAG_QTY)BAG_QTY from yarn_po_stock_barcode t group by t.LOC_ID, t.BAG_NO having sum(t.BAG_QTY)>0
0k8522rmdzg4k	獥汥捴⁰物癩汥来⌠晲潭⁳祳慵瑨␠睨敲攠⡧牡湴敥⌽㨱爠杲慮瑥攣㴱⤠慮搠灲楶楬敧攣㸰
0pzg71pnur0kp	SELECT NAME FROM UKLICENSOR WHERE LICENSORID = :b1
0qjt2sznvf0r9	DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.MV_SHIPPING'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
14n50dm1hxqad	SELECT DISTINCT C.SR# FROM PRODUCTION.CUT_SUPLY_BUNDL C, CUT_SUPLY_BUNDL_M M WHERE C.DOC_ID=M.DOC_ID AND C.CARD_NO=:B2 AND M.PLANNO=:B1 AND C.STATUS=0 AND C.SR# IN (SELECT NVL(A.SR#, 0) FROM STIT_REC_ISSUE_D A )
17x173b8v53p7	DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.MV_STITCHING'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
18z93abjz28am	SELECT PONO, 'A' O, 'REQUIRED' TTYPE, HIT_TYPE, HIT_NO, DELIVERY_DATE, NULL SHIPMODE, '0' MANUL_INV_NO, NULL ETA_DATE, S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, S13, S14, S15, S16, S17, S18, S19, S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO = : PONO UNION ALL SELECT PONO, 'A' O, 'PREPACK RATIO' TTYPE, HIT_TYPE, 0, NULL, NULL, NULL, NULL, SUM(S1/PACK_QTY) S1, SUM(S2/PACK_QTY) S2, SUM(S3/PACK_QTY) S3, SUM(S4/PACK_QTY) S4, SUM(S5/PACK_QTY) S5, SUM(S6/PACK_QTY) S6, SUM(S7/PACK_QTY) S7, SUM(S8/PACK_QTY) S8, SUM(S9/PACK_QTY) S9, SUM(S10/PACK_QTY) S10, SUM(S11/PACK_QTY) S11, SUM(S12/PACK_QTY) S12, SUM(S13/PACK_QTY) S13, SUM(S14/PACK_QTY) S14, SUM(S15/PACK_QTY) S15, SUM(S16/PACK_QTY) S16, SUM(S17/PACK_QTY) S17, SUM(S18/PACK_QTY) S18, SUM(S19/PACK_QTY) S19, SUM(S20/PACK_QTY) S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO = : PONO AND HIT_TYPE = 'PREPACK' GROUP BY PONO , HIT_TYPE UNION ALL SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SHIP_DATE, SHIPMODE, MANUL_INV_NO, ETA_DATE, SUM(S1) S1 , SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13), SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM( SELECT M.PONO, 'B' O, 'SHIPPED' TTYPE, D.HIT_TYPE, D.HIT_NO, E.INV_DATE SHIP_DATE, E.SHIP_MODE SHIPMODE, E.INV_NO_M MANUL_INV_NO, E.ETA_DATE ETA_DATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FC TN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'B' , 'SHIPPED', D.HIT_TYPE, D.HIT_NO, E.INV_DATE, E.SHIP_MODE, E.INV_NO_M, E.ETA_DATE UNION ALL -----------------SPLIT PACKING LIST SELECT M.PONO, 'B' O, 'SHIPPED' TTYPE, S.HIT_TYPE, S.HIT_NO, E.INV_DATE SHIP_DATE, E.SHIP_MODE SHIPMODE, E.INV_NO_M MANUL_INV_NO, E.ETA_DATE ETA_DATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S 7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'B' , 'SHIPPED', S.HIT_TYPE, S.HIT_NO, E.INV_DATE, E.SHIP_MODE, E.INV_NO_M, E.ETA_DATE ) GROUP BY PONO , O , TTYPE , HIT_TYPE , HIT_NO , SHIP_DATE , SHIPMODE , MANUL_INV_NO , ETA_DATE UNION ALL SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE, NULL, NULL, NULL, SUM(S1) S1, SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6 ) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13) S13, SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM ( SELECT M.PONO, 'BB' O, 'TOTAL' TTYPE, D.HIT_TYPE, D.HIT_NO, SYSDATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D. FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M , EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'BB' , 'TOTAL', D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, 'BB' O, 'TOTAL' TTYPE, S.HIT_TYPE, S.HIT_NO, SYSDATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'BB' , 'TOTAL', S.HIT_TYPE, S.HIT_NO -- END SHIPPED HITWISE TOTAL ------------------------------- ) GROUP BY PONO , O , TTYPE , HIT_TYPE , HIT_NO , SYSDATE union all SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE, NULL, NULL, NULL, SUM(S1) S1, SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13) S13, SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM ( SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, D.HIT_TYPE, D.HIT_NO, SYSDATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S 1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M , EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_N O IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO and d.hit_type='PREPACK' AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, S.HIT_TYPE, S.HIT_NO, SYSDATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK and s.hit_type='PREPACK' AND P.INV_NO= E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', S.HIT_TYPE, S.HIT_NO union all ------------ SELECT PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE, -TRUNC(NVL(SUM(S1)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S1, -TRUNC(NVL(SUM(S2)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S2, -TRUNC(NVL(SUM(S3)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S3, -TRUNC(NVL(SUM(S4)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S4, -TRUNC(NVL(SUM(S5)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S5, -TRUNC(NVL(SUM(S6)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S6, -TRUNC(NVL(SUM(S7)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S7, -TRUNC(NVL(SUM(S8)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S8, -TRUNC(NVL(SUM(S9)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S9, -TRUNC(NVL(SUM(S10)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S10, -TRUNC(NVL(SUM(S11)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S11, -TRUNC(NVL(SUM(S12)/ge t_qty_po(:PONO), 0))*get_qty_po(:PONO) S12, -TRUNC(NVL(SUM(S13)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S13, -TRUNC(NVL(SUM(S14)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S14, -TRUNC(NVL(SUM(S15)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S15, -TRUNC(NVL(SUM(S16)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S16, -TRUNC(NVL(SUM(S17)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S17, -TRUNC(NVL(SUM(S18)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S18, -TRUNC(NVL(SUM(S19)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S19, -TRUNC(NVL(SUM(S20)/get_qty_po(:PONO), 0))*get_qty_po(:PONO) S20 FROM ( SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, D.HIT_TYPE, D.HIT_NO, SYSDATE, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)) , 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCTN -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M , EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO --and d.hit_type='PREPACK' AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, 'BBBBB' O, 'RATIO BALANCE (STOCK)' TTYPE, S.HIT_TYPE, S.HIT_NO, SYSDATE, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO --and d.hit_type='PREPACK' AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, 'BBBBB' , 'RATIO BALANCE (STOCK)', S.HIT_TYPE, S.HIT_NO ) GROUP BY PONO, O, TTYPE, HIT_TYPE, HIT_NO, SYSDATE -------- /* SELECT PONO, 'BBBBB' O, 'RATIO BALANCE' TTYPE, HIT_TYPE, HIT_NO, SYSDATE , -SUM(S1/PACK_QTY)*get_qty_po(:PONO) S1, -SUM(S2/PACK_QTY)*get_qty_po(:PONO) S2, -SUM(S3/PACK_QTY)*get_qty_po(:PONO) S3, -SUM(S4/PACK_QTY)*get_qty_po(:PONO) S4, -SUM(S5/PACK_QTY)*get_qty_po(:PONO) S5, -SUM(S6/PACK_QTY)*get_qty_po(:PONO) S6, -SUM(S7/PACK_QTY)*get_qty_po(:PONO) S7, -SUM(S8/PACK_QTY)*get_qty_po(:PONO) S8, -SUM(S9/PACK_QTY)*get_qty_po(:PONO) S9, -SUM(S10/PACK_QTY)*get_qty_po(:PONO) S10, -SUM(S11/PACK_QTY)*get_qty_po(:PONO) S11, -SUM(S12/PACK_QTY)*get_qty_po(:PONO) S12, -SUM(S13/PACK_QTY)*get_qty_po(:PONO) S13, -SUM(S14/PACK_QTY)*get_qty_po(:PONO) S14, -SUM(S15/PACK_QTY)*get_qty_po(:PONO) S15, -SUM(S16/PACK_QTY)*get_qty_po(:PONO) S16, -SUM(S17/PACK_QTY)*get_qty_po(:PONO) S17, -SUM(S18/PACK_QTY)*get_qty_po(:PONO) S18, -SUM(S19/PACK_QTY)*get_qty_po(:PONO) S19, -SUM(S20/PACK_QTY)*get_qty_po(:PONO) S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO=:PONO AND HIT_TYPE='PREPACK' GROUP BY PONO, HIT_TYPE, hit_no */ ) GROUP BY PONO , O , TT YPE , HIT_TYPE , HIT_NO , SYSDATE union all SELECT PONO, 'BBBB' O, 'PREPACK AS PER TOTAL' TTYPE, HIT_TYPE, HIT_NO, SYSDATE , to_char((NVL(SUM(S1/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S2/PACK_QTY)*get_qty_po(:PONO), 0)+NVL(SUM(S3/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S4/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S5/PACK_QTY)*get_qty_po(:PONO), 0) + NVL(SUM(S6/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S7/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S8/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S9/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S10/PACK_QTY)*get_qty_po(:PONO), 0) + NVL(SUM(S11/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S12/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S13/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S14/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S15/PACK_QTY)*get_qty_po(:PONO), 0) + NVL(SUM(S16/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S17/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S18/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S19/PACK_QTY)*get_qty_po(:PONO), 0) +NVL(SUM(S20/P ACK_QTY)*get_qty_po(:PONO), 0) )/(NVL(SUM(S1/PACK_QTY), 0)+NVL(SUM(S2/PACK_QTY), 0)+NVL(SUM(S3/PACK_QTY), 0) +NVL(SUM(S4/PACK_QTY), 0)+NVL(SUM(S5/PACK_QTY), 0)+ NVL(SUM(S6/PACK_QTY), 0)+NVL(SUM(S7/PACK_QTY), 0)+NVL(SUM(S8/PACK_QTY), 0)+NVL(SUM(S9/PACK_QTY), 0)+NVL(SUM(S10/PACK_QTY), 0)+ NVL(SUM(S11/PACK_QTY), 0)+NVL(SUM(S12/PACK_QTY), 0)+NVL(SUM(S13/PACK_QTY), 0)+NVL(SUM(S14/PACK_QTY), 0)+ NVL(SUM(S15/PACK_QTY), 0) + NVL(SUM(S16/PACK_QTY), 0) +NVL(SUM(S17/PACK_QTY), 0)+NVL(SUM(S18/PACK_QTY), 0)+NVL(SUM(S19/PACK_QTY), 0)+NVL(SUM(S20/PACK_QTY), 0) )) SHIPMODE, 'PACKED QTY' MANUL_INV_NO, NULL ETA_DATE, SUM(S1/PACK_QTY)*get_qty_po(:PONO) S1, SUM(S2/PACK_QTY)*get_qty_po(:PONO) S2, SUM(S3/PACK_QTY)*get_qty_po(:PONO) S3, SUM(S4/PACK_QTY)*get_qty_po(:PONO) S4, SUM(S5/PACK_QTY)*get_qty_po(:PONO) S5, SUM(S6/PACK_QTY)*get_qty_po(:PONO) S6, SUM(S7/PACK_QTY)*get_qty_po(:PONO) S7, SUM(S8/PACK_QTY)*get_qty_po(:PONO) S8, SUM(S9/PACK_QTY)*get_qty_po(:PONO) S9, SUM(S10/PACK_QTY)*get_qty_po(:PONO) S10 , SUM(S11/PACK_QTY)*get_qty_po(:PONO) S11, SUM(S12/PACK_QTY)*get_qty_po(:PONO) S12, SUM(S13/PACK_QTY)*get_qty_po(:PONO) S13, SUM(S14/PACK_QTY)*get_qty_po(:PONO) S14, SUM(S15/PACK_QTY)*get_qty_po(:PONO) S15, SUM(S16/PACK_QTY)*get_qty_po(:PONO) S16, SUM(S17/PACK_QTY)*get_qty_po(:PONO) S17, SUM(S18/PACK_QTY)*get_qty_po(:PONO) S18, SUM(S19/PACK_QTY)*get_qty_po(:PONO) S19, SUM(S20/PACK_QTY)*get_qty_po(:PONO) S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO = : PONO AND HIT_TYPE = 'PREPACK' GROUP BY PONO , HIT_TYPE , hit_no UNION ALL SELECT PONO, 'C' O, 'BALANCE TO SHIP' TTYPE, HIT_TYPE, HIT_NO, SYSDATE , NULL SHIPMODE, NULL MANUL_INV_NO, NULL ETA_DATE, SUM(S1) S1, SUM(S2) S2, SUM(S3) S3, SUM(S4) S4, SUM(S5) S5, SUM(S6) S6, SUM(S7) S7, SUM(S8) S8, SUM(S9) S9, SUM(S10) S10, SUM(S11) S11, SUM(S12) S12, SUM(S13) S13, SUM(S14) S14, SUM(S15) S15, SUM(S16) S16, SUM(S17) S17, SUM(S18) S18, SUM(S19) S19, SUM(S20) S20 FROM ( SELECT PONO, HIT_TYPE, HIT_NO, -S1 S1, -S2 S2, -S3 S3, -S4 S4, -S5 S5, -S 6 S6, -S7 S7, -S8 S8, -S9 S9, -S10 S10, -S11 S11, -S12 S12, -S13 S13, -S14 S14, -S15 S15, -S16 S16, -S17 S17, -S18 S18, -S19 S19, -S20 S20 FROM PRODUCTION.KCL_PO_HITS WHERE PONO=:PONO UNION ALL SELECT M.PONO, D.HIT_TYPE, D.HIT_NO, NVL(SUM(D.S1*(D.TCTN -D.FCTN+1)), 0) S1, NVL(SUM(D.S2*(D.TCTN -D.FCTN+1)), 0) S2, NVL(SUM(D.S3*(D.TCTN -D.FCTN+1)), 0) S3, NVL(SUM(D.S4*(D.TCTN -D.FCTN+1)), 0) S4, NVL(SUM(D.S5*(D.TCTN -D.FCTN+1)), 0) S5, NVL(SUM(D.S6*(D.TCTN -D.FCTN+1)), 0) S6, NVL(SUM(D.S7*(D.TCTN -D.FCTN+1)), 0) S7, NVL(SUM(D.S8*(D.TCTN -D.FCTN+1)), 0) S8, NVL(SUM(D.S9*(D.TCTN -D.FCTN+1)), 0) S9, NVL(SUM(D.S10*(D.TCTN -D.FCTN+1)), 0) S10, NVL(SUM(D.S11*(D.TCTN -D.FCTN+1)), 0) S11, NVL(SUM(D.S12*(D.TCTN -D.FCTN+1)), 0) S12, NVL(SUM(D.S13*(D.TCTN -D.FCTN+1)), 0) S13, NVL(SUM(D.S14*(D.TCTN -D.FCTN+1)), 0) S14, NVL(SUM(D.S15*(D.TCTN -D.FCTN+1)), 0) S15, NVL(SUM(D.S16*(D.TCTN -D.FCTN+1)), 0) S16, NVL(SUM(D.S17*(D.TCTN -D.FCTN+1)), 0) S17, NVL(SUM(D.S18*(D.TCT N -D.FCTN+1)), 0) S18, NVL(SUM(D.S19*(D.TCTN -D.FCTN+1)), 0) S19, NVL(SUM(D.S20*(D.TCTN -D.FCTN+1)), 0) S20 FROM WPL_DTL D, WPL_MST M, EXPINV_MST E, EXPINV_DTL_PL P WHERE M.PL_NO = D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NOT NULL AND D.HIT_NO IS NOT NULL AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' GROUP BY M.PONO, D.HIT_TYPE, D.HIT_NO UNION ALL SELECT M.PONO, S.HIT_TYPE, S.HIT_NO, NVL(SUM(S.S1), 0) S1, NVL(SUM(S.S2), 0) S2, NVL(SUM(S.S3), 0) S3, NVL(SUM(S.S4), 0) S4, NVL(SUM(S.S5), 0) S5, NVL(SUM(S.S6), 0) S6, NVL(SUM(S.S7), 0) S7, NVL(SUM(S.S8), 0) S8, NVL(SUM(S.S9), 0) S9, NVL(SUM(S.S10), 0) S10, NVL(SUM(S.S11), 0) S11, NVL(SUM(S.S12), 0) S12, NVL(SUM(S.S13), 0) S13, NVL(SUM(S.S14), 0) S14, NVL(SUM(S.S15), 0) S15, NVL(SUM(S.S16), 0) S16, NVL(SUM(S.S17), 0) S17, NVL(SUM(S.S18), 0) S18, NVL(SUM(S.S19), 0) S19, NVL(SUM(S.S20), 0) S20 FROM WPL_MST M, WPL_DTL D, WPL_SPLIT S, EXPINV_MST E, EXPINV_DTL_P L P WHERE M.PL_NO=D.PL_NO AND M.PONO=:PONO AND M.PACK_TYPE='WP' AND D.HIT_TYPE IS NULL AND D.HIT_NO IS NULL AND D.PK=S.FK AND P.INV_NO=E.INV_NO AND P.PL_NO=M.PL_NO AND E.APPROVED=1 AND E.CANCEL_STATUS='N' AND S.HIT_TYPE IS NOT NULL AND S.HIT_NO IS NOT NULL GROUP BY M.PONO, S.HIT_TYPE, S.HIT_NO ) HIT_BAL GROUP BY PONO , 'BALANCE TO SHIP' , HIT_TYPE , HIT_NO , SYSDATE , '0' , '0' , '0' ORDER BY 1 ASC, 4 DESC, 5 ASC , HIT_TYPE DESC , HIT_NO , O ASC
1t8bh6fd2yug3	SELECT DISTINCT R.RATE FROM PRODUCTION.RES_WAGES_PLANOPR R WHERE R.PLANNO = :B2 AND R.OPERATION_ID=:B1
1ytt2nppswspc	select /*+ NO_CPU_COSTING */ MM.A, mm.col, mm.ACT_SIZE, mm.REQ_QTY, MM.TO_CUT_PCS TO_CUT_PC, (MM.TO_CUT_PCS-ab.qty1) RJ_QTY, ab.qty1-NVL(UA.MISSED_QTY, 0) QTY1, nvl(S.S_QTY, 0) S_QTY, NVL(UA.MISSED_QTY, 0) MISS_QTY, round(mm.REQ_QTY + (mm.REQ_QTY*max(tp.waste)/100), 0) req_2, Z.REJ_QTY, ST_QTY, s.cut_status, s.ind_status, s.stit_status, s.plan_status, nvl(WR.WHQTY, 0)whqty, nvl(SR.WRQTY, 0)wrqty, (nvl(WR.WHQTY, 0)-nvl(SR.WRQTY, 0))netwhqty from LS_ACT_CUT_PCS_VIEW1 MM, tblplanorder tp, plan_status s, ( select m.planno, d.g_size, m.garment_id , NVL(sum(d.qty), 0) QTY1, D.HITID from stit_rec_issue_m m, stit_rec_issue_d d where d.doc_id=m.doc_id and m.document_type='STR' and m.planno=:plan AND D.HITID=:P_HIT and d.status=0 group by m.planno, d.g_size, m.garment_id, D.HITID ) ab , ( select V.COL, t.plan_no, t.g_size, v.garment_id, count(t.g_size) MISSED_Qty, V.HITID from missed_gmt_id t, LS_act_cut_pcs_view1 v where t.plan_no = :plan AND V.HITID=:P_HIT AND v.PLAN_NO=t.plan_no and V. ACT_SIZE=T.G_SIZE group by t.plan_no, t.g_size, V.COL, v.garment_id, V.HITID ORDER BY V.COL )UA, ( select v.planno, v.g_size, nvl(sum(v.rej_qty), 0) rej_qty from LS_cut_stch_iss_bal_v v where v.planno=:plan AND V.HITID=:P_HIT group by v.planno, v.g_size ) z , ( select --t.ACT_SIZE COL, v.s col, round(sum(v.s1)/ nvl(GET_PLAN_COUNTGMT(v.plan_no), 1), 0) S_QTY, V.HIT_ID from LS_SHIP_SIZE_VIEW v /*, act_cut_pcs_view t*/ where --t.PLAN_NO=v.plan_no -- and v.plan_no= :PLAN AND V.HIT_ID=:P_HIT --and t.COL=v.S group by v.s, v.plan_no, V.HIT_ID--t.ACT_SIZE order by v.S ) S, ( SELECT T.ACT_SIZE COL, T.GARMENT_ID, SUM(V.QTY) WHQTY, T.HITID FROM LS_STITCH_RECIVE_WHAREHOUSE V, LS_ACT_CUT_PCS_VIEW1 T WHERE T.PLAN_NO=V.PLANNO AND T.PLAN_NO= :PLAN AND V.HITID=:P_HIT AND V.HITID=T.HITID AND T.COL=V.COL_SIZE and v.garment_id(+)=t.garment_id GROUP BY T.ACT_SIZE, V.COL_SIZE, T.GARMENT_ID, T.HITID HAVING SUM(V.QTY)>0 ORDER BY V.COL_SIZE ) WR, ( select d.g_size COL, v.garment_id, SUM(d.qty)WRQTY, D. HITID from stit_rec_issue_m m, stit_rec_issue_d d, LS_ACT_CUT_PCS_VIEW1 v where m.document_type='SRW' and m.doc_id=d.doc_id and m.planno=:PLAN and v.PLAN_NO=m.planno and v.COL=d.col_size and m.garment_id=v.garment_id AND D.HITID=:P_HIT AND D.HITID=V.HITID group by d.g_size , v.GARMENT_ID, D.HITID, d.col_size HAVING SUM(d.QTY)>0 ORDER BY d.col_size )SR, ( select d.g_size, m.garment_id, sum(d.stitched_qty) ST_QTY, D.HITID from cut_suply_bundl_m m, cut_suply_bundl d where d.doc_id=m.doc_id and d.ent_type='IITS' and m.planno=:PLAN AND D.HITID=:P_HIT and d.status=0 and d.stitched_qty is not null group by m.planno, d.g_size, m.garment_id, D.HITID, d.col_size order by d.col_size ) h where tp.plan_no =MM.plan_no AND MM.PLAN_NO = :PLAN AND MM.HITID=:P_HIT and ab.g_size(+)=mm.ACT_SIZE AND UA.G_SIZE(+)=mm.ACT_SIZE and z.g_size(+)=mm.ACT_SIZE and S.COL(+)=mM.COL and h.g_size(+)=mM.act_size and wr.col(+)=mM.act_size and sr.col(+)=mM.act_size --AND MM.REQ_QTY>0 and tp.waste is not null and m m.garment_id=ab.garment_id(+) and mm.garment_id=ua.garment_id(+) and mm.garment_id=h.garment_id(+) and mm.garment_id=sr.garment_id(+) and mm.garment_id=wr.garment_id(+) and mm.plan_no=s.plan_no(+) group by mm.col, MM.PLAN_NO, mm.ACT_SIZE, mm.REQ_QTY, ab.qty1, rej_qty, S.S_QTY, MM.TO_CUT_PCS, ST_QTY, MISSED_QTY, MM.A , s.cut_status, s.ind_status, s.stit_status, s.plan_status, WR.WHQTY, SR.WRQTY, MM.HITID Order by 1
228ak5wrhrzt8	SELECT ITEM_DESC, ITEM_CODE, UNIT_CODE, NVL(SUM(DECODE(D.ENTRYTYPE, 'RE', 1, 'IR', 1, 'SR', 1, 'TF', 1, 'RR', -1, 'IS', -1, 'SP', -1, 'TR', -1, 0) * QTY), 0) BQTY FROM ITEM I, STORE_DETAIL D, STORE_MASTER M WHERE M.SRNO=D.SRNO AND I.ITEM_CODE = D.ITEMCODE (+) AND I.SUB_CODE > 2100 AND D.CCODE = :1 AND D.ENTRYTYPE IN ('RE', 'IR', 'SR', 'TF', 'RR', 'IS', 'SP', 'TR') AND M.SUBSTOREID=:2 GROUP BY ITEM_DESC, ITEM_CODE, UNIT_CODE ORDER BY 1
2jzkmzmzxza9p	DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.yarn_bag_stk_loc'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
38vxaybzk778b	DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PRODUCTION.wages_sheet_mv'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
3gzbt6t7c97cu	SELECT PROSDESC, PROSGRP FROM UKPROS WHERE PROSID = :b1
3ss16xn9jc3by	select /*+ NO_CPU_COSTING */ mm.col, mm.ACT_SIZE, mm.REQ_QTY, MM.TO_CUT_PCS TO_CUT_PC , ab.qty1, nvl(S.S_QTY, 0) S_QTY, round(mm.REQ_QTY + (mm.REQ_QTY*tp.waste/100), 0) req_2, Z.REJ_QTY, ST_QTY, s.cut_status, s.ind_status, s.stit_status, s.plan_status, WR.WHQTY, SR.WRQTY from ACT_CUT_PCS_VIEW MM, tblplanorder tp, plan_status s, ( SELECT m.planno, d.g_SIZe, SUM(nvl(D.QTY, 0)) QTY1 FROM CUT_SUPLY_BUNDL D, CUT_SUPLY_BUNDL_M M WHERE (M.ENTRY_TYPE = 'IITS') AND (M.DOC_ID = D.DOC_ID) and m.planno=:plan GROUP BY M.PLANNO, M.ENTRY_TYPE, D.G_SIZE ) ab , ( select v.planno, v.g_size, nvl(sum(v.rej_qty), 0) rej_qty from cut_stch_iss_bal_v v where v.planno=:plan group by v.planno, v.g_size ) z , ( select t.ACT_SIZE COL, v.s1 S_QTY from SHIP_SIZE_VIEW v, act_cut_pcs_view t where t.PLAN_NO=v.plan_no and t.plan_no= :PLAN and t.COL=v.S order by v.S ) S, (SELECT T.ACT_SIZE COL, SUM(V.QTY) WHQTY FROM STITCH_RECIVE_WHAREHOUSE V, ACT_CUT_PCS_VIEW T WHERE T.PLAN_NO=V.PLANNO AND T.PLAN_NO= :PLAN AND T.COL=V.COL_SIZE GROUP BY T.ACT_SIZE, V.COL_SIZE HAVING SUM(V.QTY)>0 ORDER BY V.COL_SIZE) WR, (SELECT T.ACT_SIZE COL, SUM(R.QTY)WRQTY FROM STITCH_RETURN_WHAREHOUSE R, ACT_CUT_PCS_VIEW T WHERE T.PLAN_NO=R.PLANNO AND T.PLAN_NO=:PLAN AND T.COL=R.COL_SIZE GROUP BY T.ACT_SIZE, R.COL_SIZE HAVING SUM(R.QTY)>0 ORDER BY R.COL_SIZE)SR, (select d.g_size, sum(d.stitched_qty) ST_QTY from cut_suply_bundl_m m, cut_suply_bundl d where d.doc_id=m.doc_id and d.ent_type='IITS' and m.planno=:PLAN and d.stitched_qty is not null group by m.planno, d.g_size order by d.col_size ) h where tp.plan_no =MM.plan_no AND MM.PLAN_NO = :PLAN and ab.g_size(+)=mm.ACT_SIZE and z.g_size(+)=mm.ACT_SIZE and S.COL(+)=mM.act_size and h.g_size(+)=mM.act_size and wr.col(+)=mM.act_size and sr.col(+)=mM.act_size AND MM.REQ_QTY>0 and tp.waste is not null and mm.plan_no=s.plan_no(+) group by mm.col, MM.PLAN_NO, mm.ACT_SIZE, mm.REQ_QTY, ab.qty1, rej_qty, tp.waste, S.S_QTY, MM.TO_CUT_PCS, ST_QTY , s.cut_status, s.ind_ status, s.stit_status, s.plan_status, WR.WHQTY, SR.WRQTY Order by 1
3xqr3nrwy1zyy	SELECT WM_CONCAT1(PONO) FROM KCL_PO_MAIN WHERE ORDERID = :b1
42w5jvwyztq0h	SELECT NVL(GET_PLAN_PO_QTY(:B2 , :B1 ), 0) FROM DUAL
46x2m3gxmrbsq	UPDATE processing.lab_mst SET LABID=:1, REC_DATE=:2, TEST_TYPE=:3, LOTNO=:4, PLANNO=:5, ORDERNO=:6, ROLL_NO=:7, REF_CODE=:8, ccode=:9, CDK_ID=:10, ITEM_CODE=:11, FABRIC_CODE=:12, COLORS_ASSESSED=:13, F_GSM=:14, F_WIDTH=:15, COLOR=:16, STYLE=:17, CLAIMED=:18, CUS_ID=:19, SAMPLENO=:20, ENDUSE=:21, BATCHNO=:22, SPECNO=:23, BUYING_DIV=:24, STATUS=:25, COMPLETE_PASS=:26, APP_BY=:27, APPROVE_CODE=:28, APP_DATE=:29, ACTION_TAK=:30, TEST_BY=:31, remarks=:32, SCOLOR=:33, READY_DISP=:34 WHERE ROWID=:35
485jmv7xv085c	SELECT QUALITY_REF, FIBER_COMPOSITION, AW_WT FROM UKFAB WHERE FAB_ID = :b1
4uw118z2skgz1	SELECT WM_CONCAT(PROCESS_NAME) FROM ( SELECT DISTINCT P.PROCESS_NAME, FR.ORD FROM RND_SR_DTL D, PRODUCTION.SD_FINISH_ROUT FR, PRODUCTION.FINISH_ROUT P WHERE D.SUP_REC_NO=:B2 AND D.PK=FR.PK AND D.PK=:B1 AND FR.PROCESS_ID=P.PROCESS_ID ORDER BY ORD )
4xn0tw9j8rcnh	SELECT SUM(SQTY) FROM UKPO P, UKPOHIT H WHERE P.PK = H.PK AND P.ORDERID = :b1 AND H.ENTRYTYPE = 'BOOKING' AND H.STATUS = 1 AND P.STATUS NOT IN ( 'CANCELLED' )
5fg74rb3ru4ac	SELECT PRINT_NAME FROM UKPRINTER WHERE PRINT_ID = :b1
5n4u7kgq4hwgm	INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."MV_SHIPPING" select t.ccode, c.c_name, s.plan_no, 'BY- ' || e.ship_mode ship_mode, e.inv_date ship_date, t.pono, t.pl_no, trunc(t.pl_date) PL_DATE, m.style, nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) CMT_RATE, e.inv_no, e.inv_no_m, GetPL_Qty_PCS(t.pl_no) PCS, GetPL_Qty_PCS(t.pl_no)*nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) AMOUNT, get_cmt_contract_no(t.ccode, s.plan_no, g.garment_id)contract_no , g.garment_id , t.po_close from wpl_mst t, tblplanorder s, kcl_po_main m, expinv_mst e, dye.cdtl c, expinv_dtl_pl ed, tblplanpics g where t.pack_type = 'WP' and s.order_no = t.pono and e.inv_no = ed.inv_no and ed.pl_no = t.pl_no and m.pono = s.order_no AND E.CANCEL_STATUS='N' and e.approved=1 and g.plan_no=s.plan_no and t.ccode=c.c_code --and s.plan_no LIKE NVL(:planno, '%') --and t.pl_no not in (select r.pl_no from expsal_inv r) -- and trunc(e.inv_date) between nvl(:FDATE , to_date(sysdate-10000, 'DD-MM-RRRR')) AND nvl(:TDATE , to_date(sysdate, 'DD-MM-RRRR')) --and t.ccode=decode(:P_cCODE, 4, 15, NULL, T.CCODE, :P_CCODE) union all select t.ccode, c.c_name, s.plan_no, 'BY- ' || e.ship_mode ship_mode, e.inv_date ship_date, t.pono, t.pl_no, trunc(t.pl_date) PL_DATE, m.style, nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) CMT_RATE, e.inv_no, e.inv_no_m, PL.TOTAL_QTY PCS , PL.TOTAL_QTY*nvl(get_cmt_rate(t.ccode, s.plan_no, g.garment_id), 0) AMOUNT, get_cmt_contract_no(t.ccode, s.plan_no, g.garment_id)contract_no , g.garment_id, t.po_close from wpl_mst t, wpl_dtl_aeopo pl, tblplanorder s, kcl_po_main m, expinv_mst e, dye.cdtl c, expinv_dtl_pl ed, tblplanpics g where t.pack_type = 'WP' and s.order_no = pl.pono and e.inv_no = ed.inv_no and ed.pl_no = t.pl_no and m.pono = s.order_no AND E.CANCEL_STATUS='N' -- and t.unit_12 LIKE NVL(:unit_12, '%') --and c.c_code = e.ccode and e.approved=1 and t.ccode=c.c_code --and s.plan_no LIKE NVL( :planno, '%') and pl.pl_no=t.pl_no and g.plan_no=s.plan_no --and t.pl_no not in (select r.pl_no from expsal_inv r) -- and trunc(e.inv_date) between nvl(:FDATE , to_date(sysdate-10000, 'DD-MM-RRRR')) AND nvl(:TDATE , to_date(sysdate, 'DD-MM-RRRR')) --and t.ccode=decode(:P_CCODE, 4, 15, NULL, T.CCODE, :P_CCODE) union all select m.ccode, c.c_name, m.plan_no, 'LOCAL' ship_mode, m.cdate ship_date, ' ' pono, ' ' pl_no, m.cdate PL_DATE, ' ' style, nvl(get_cmt_rate(m.ccode, m.plan_no, m.gmt_id), 0) CMT_RATE, 0 inv_no, ' ' inv_no_m, m.plan_qty PCS , m.plan_qty*nvl(get_cmt_rate(m.ccode, m.plan_no, m.gmt_id), 0) AMOUNT, get_cmt_contract_no(m.ccode, m.plan_no, m.gmt_id)contract_no , m.gmt_id garment_id, ' 'po_close from tblcontractmast m, dye.cdtl c where m.ccode=c.c_code and m.remarks='LOCALCMT' and m.sam_pcs is not null --and t.pl_no not in (select r.pl_no from expsal_inv r) -- and trunc(m.cdate) between nvl(:FDATE , to_date(sysdate-10000, 'DD-MM-RRRR')) AND nvl(:TDATE , to_d ate(sysdate, 'DD-MM-RRRR')) -- and m.ccode=decode(:P_CCODE, 4, 15, NULL, m.CCODE, :P_CCODE)
61jagxsr76cr0	SELECT SMP_DESC FROM MKT_SMP WHERE SMP_ID = :b1
620nmfq8upvsb	INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PROCESSING"."MV_GSTK" SELECT DISTINCT A.PLANNO , ENTRYTYPE, NARRATION, B.COLOR, CORPORATE_SUP_NO , A.REC_DATE FROM ( /* QUERY FOR RND*/ SELECT DISTINCT A.PLAN_NO PLANNO, DECODE(A.ENTRY_TYPE, 'DHR', 'GLOT', 'DIR', 'RLOT', 'DFR', 'FLOT') ENTRYTYPE, E.NARRATION, NULL PLAN_NO, CORPORATE_SUP_NO , A.REC_DATE FROM PROCESSING.DYE_SR_DTL B, PROCESSING.DYE_SR_MST A, ENTRYTYPE E WHERE A.ENTRY_NO = B.ENTRY_NO AND A.ENTRY_TYPE IN ('DHR', 'DIR', 'DFR') AND E.STOCKTYPE='DYE' AND A.DEPT_CODE=3502 AND E.ENTRYTYPE=DECODE(A.ENTRY_TYPE, 'DHR', 'GLOT', 'DIR', 'RLOT', 'DFR', 'FLOT') ) A , ( select distinct sup_rec_no, color from rnd_sr_dtl ) b, ( select plan_no from PROCESSING.GSTK WHERE SUBSTR(PLAN_NO, 1, 1)='S' ) S where A.CORPORATE_SUP_NO = b.sup_rec_no AND S.PLAN_NO=A.PLANNO
671prjcayrc77	SELECT NAME FROM UKAGENT WHERE AGENTID = :b1
6rf1xb3rsb3c9	SELECT CCODE_VALIDATION(:b1) FROM PRODUCTION.TBLPLANSHEET WHERE PLAN_NO = :b2
6xuhp70u9mhz0	SELECT CCODE, DEPT, deptcode, round(SUM(MAKEUP)) MAKEUP FROM (SELECT CCODE, empcode, empname, fname, PRODUCTION.DESIGCODE_VALIDATION(desigcode) DESIGNATION, desigcode, HRM.DEPTCODE_VALIDATION(deptcode) DEPT, deptcode, DAYS WDAYS, EMP_UNIT_SAL, EMP_SAL, EMP_SAL*DAYS EMP_TOTAL, CASE WHEN ROUND((EMP_UNIT_SAL*((EMP_SAL*DAYS)-U_EMPSUM_SAL))/DECODE(U_EMPSUM_SAL, 0, 1, U_EMPSUM_SAL))<0 and EMP_UNIT_SAL>0 and DAYS>0 THEN 0 when ROUND((EMP_UNIT_SAL*((EMP_SAL*DAYS)-U_EMPSUM_SAL))/DECODE(U_EMPSUM_SAL, 0, 1, U_EMPSUM_SAL))>=0 and EMP_UNIT_SAL>0 and DAYS>0 THEN ROUND((EMP_UNIT_SAL*((EMP_SAL*DAYS)-U_EMPSUM_SAL))/DECODE(U_EMPSUM_SAL, 0, 1, U_EMPSUM_SAL)) ELSE (EMP_SAL*DAYS) END MAKEUP FROM ( SELECT DISTINCT w.ccode, w.empcode, w.empname, w.fname, decode(e.desigcode, NULL, w.desigcode, e.desigcode) desigcode, decode(e.deptcode, NULL, w.deptcode, e.deptcode)deptcode, (26-GET_EMP_ABS_datewise(w.empcode, :fdate, :tdate)) days, NVL(SUM (ROUND (amt, 2)) OVER (PARTITION B Y w.ccode, w.empcode, w.empname, w.fname ), 0)U_EMPSUM_sal, NVL(SUM (ROUND (amt, 2)) OVER (PARTITION BY w.ccode, w.empcode, w.empname, w.fname, decode(e.desigcode, NULL, w.desigcode, e.desigcode) , decode(e.deptcode, NULL, w.deptcode, e.deptcode) ), 0)emp_unit_sal, NVL(round(hrm.GET_EMPCODE_SALARY(w.empcode)/26, 0), 0)emp_sal FROM (select distinct t.empcode, e.ccode, e.empname, e.fname, e.desigcode, e.deptcode from daily_attendance t, tblhrmemployee e where t.dated BETWEEN :fdate AND :tdate and t.timein is not null and e.emptype IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND E.DESIGCODE IN (72, 87, 114, 115, 108) and e.empcode=t.empcode and e.deptcode!=1102 and decode(e.ccode, 4, 21, e.ccode)=:cc) w, (SELECT DISTINCT a.ccode, b.emp_code empcode, e.empname, e.fname, e.desigcode, SUM (ROUND ((a.stitched_qty / 12), 2) * get_plane_opration_rate(a.planno, b.opration_id)) amt, DECODE (a.unit_num, 2547, 2514, a.unit_num) deptcode FROM opr_wages_emp_master a, opr_wages_emp_d tl b, hrm.tblhrmemployee e WHERE a.opr_main_id = b.opr_main_id AND b.emp_code IS NOT NULL AND E.DESIGCODE IN (72, 87, 114, 115, 108) AND e.empcode = b.emp_code AND E.EMPTYPE IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND a.dated BETWEEN :fdate AND :tdate AND a.ccode = :cc --AND NVL (a.paid, 'N') = 'N' GROUP BY a.ccode, e.empname, e.fname, b.emp_code, e.desigcode, DECODE(a.unit_num, 2547, 2514, a.unit_num) UNION ALL SELECT DISTINCT a.ccode, md.empcode empcode, e.empname, e.fname, e.desigcode, SUM(ROUND ((a.stitched_qty / 12), 2)* get_plane_opration_rate(a.planno, opration)) amt, DECODE (a.unit_num, 2547, 2514, a.unit_num) deptcode FROM opr_wages_emp_master a, opr_wages_emp_m md, hrm.tblhrmemployee e WHERE a.opr_main_id = md.opr_main_id AND e.empcode = md.empcode AND E.EMPTYPE IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND E.DESIGCODE IN (72, 87, 114, 115, 108) AND ddate BETWEEN :fdate AND :tdate AND a.ccode = :cc -- AND NVL (dpaid, 'N') = 'N' GROUP BY a.ccode, e.empname, e.f name, md.empcode, e.desigcode, DECODE(a.unit_num, 2547, 2514, a.unit_num) UNION ALL SELECT DISTINCT a.ccode, a.empcode empcode, e.empname, e.fname, e.desigcode, a.amt, a.unit_no FROM production.opr_wages_emp_add a, tblhrmemployee e WHERE a.empcode = e.empcode AND E.EMPTYPE IN ('PERMANENT', 'PERMANENTW', 'TEMPORARY') AND E.DESIGCODE IN (72, 87, 114, 115, 108) AND a.dated BETWEEN :fdate AND :tdate AND a.ccode = :cc -- AND NVL (a.paid, 'N') = 'N' ) e where w.empcode=e.empcode(+) )) --WHERE MAKEUP>0 WHERE (:P_UNIT IS NULL OR deptcode=:P_UNIT) group by CCODE, DEPT, deptcode ORDER BY deptcode
73mzjsxgt8kdb	SELECT NVL(SUM(H.QTY), 0) QTY FROM TBLPLANORDER O, KCL_PO_HITS H WHERE O.ORDER_NO=H.PONO AND O.PLAN_NO=:B2 AND H.GARID=:B1
742vw5aqd5947	DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin PRODUCTION.POP_ACH_ALL; end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
7dp2ntks3umau	SELECT D.PLAN_NO, D.MS_PO, D.ITEM_CODE, SUBSTR(PRODUCTION.GET_YARN(D.ITEM_CODE), 1, 100)ITEM_DESC , D.FTHR_CODE, SUBSTR(PRODUCTION.GET_YARN(D.FTHR_CODE), 1, 100)FTHR_DESC , PROCESSING.GET_ST_STK(:P_TO, D.PK, :P_STK)STK FROM PROCESSING.ST_MST M, PROCESSING.ST_DTL D WHERE M.ENTRY_NO = D.ENTRY_NO AND TRUNC ( M.DATED ) <= : P_TO AND TO_NUMBER ( SUBSTR ( D.ENTRY_NO , 4 , 2 ) ) = : P_CCODE AND D.PLAN_NO = NVL ( : P_PLAN , D.PLAN_NO ) AND D.ITEM_CODE = NVL ( : P_ITEM , D.ITEM_CODE ) AND D.FTHR_CODE = NVL ( : P_FTHR , D.FTHR_CODE ) AND PROCESSING.GET_ST_STK ( : P_TO , D.PK , : P_STK ) <> 0 AND PROCESSING.GET_CONT_DATE ( D.MS_PO ) > '28-FEB-2014' ORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC, 6 ASC
7grsf923r1m4g	select distinct Z.ACT_SIZE S, Z.COL SIZ, ORD from planno_size_qty_view Z where plan_no = :1 and z.req_qty>0 ORDER BY ORD
8k4xz9kr1brkt	SELECT NVL(MAX(OPR_DTL_ID), 0) + 1 FROM OPR_WAGES_EMP_DTL
8r6x06b59nuyt	/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."WAGES_SHEET_MV"("PLANNO", "OPRATION_ID", "DATED", "CCODE", "FLOW_ID", "UNIT_NUM", "EMPCODE", "EARN_AMT") SELECT "M"."PLANNO", "D"."OPRATION_ID", TRUNC("M"."DATED"), "M"."CCODE", "SMT"."FLOW_ID", "M"."UNIT_NUM", "D"."EMP_CODE", SUM(ROUND("M"."STITCHED_QTY"/12, 2)) FROM "OPR_WAGES_EMP_MASTER" "M", "OPR_WAGES_EMP_DTL" "D", "STIT_REC_ISSUE_M" "SMT", "STIT_REC_ISSUE_D" "SDT" WHERE "M"."OPR_MAIN_ID"="D"."OPR_MAIN_ID" AND "D"."EMP_CODE" IS NOT NULL AND "SMT"."DOC_ID"="SDT"."DOC_ID" AN D "SMT"."DOCUMENT_TYPE"='STR' AND "SDT"."SR#"="M"."CUT_BUNDLE_ID" AND TRUNC("M"."DATED")>'01-JUL-2013' GROUP BY "D"."EMP_CODE", "M"."UNIT_NUM", "SMT"."FLOW_ID", TRUNC("M"."DATED"), "M"."CCODE", "M"."PLANNO", "D"."OPRATION_ID"
932827s2cyx97	DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin PRODUCTION.ACTIVITY_TABLE_DATA; end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
95hp4vzj95d6b	/* MV_REFRESH (DEL) */ delete from "PRODUCTION"."WAGES_SHEET_MV"
9pc5hdmqdgx9j	UPDATE tblhrmemployee SET EMPCODE=:1, EMPNAME=:2, FNAME=:3, CCODE=:4, NIC=:5, OLDNIC=:6, EXM=:7, LATE_EXM=:8, GM_ATT=:9, DOB=:10, DEPTCODE=:11, DESIGCODE=:12, HOMEADDRESS=:13, CITY=:14, PERMANENTADDRESS=:15, REFRENCE=:16, CELLNO=:17, MOB_CARD_WEFDATE=:18, PERSONALEMAIL=:19, STATUS=:20, EMPTYPE=:21, PHONE=:22, NTNNO=:23, PROBOTIONPERIOD=:24, ENDPRODATE=:25, REASON=:26, LEAVINGDATE=:27, BGROUP=:28, RPLC_CODE=:29, RELIGION=:30, GENDER=:31, SSNO=:32, EOBINO=:33, OTALLOW=:34, RESTDAY=:35, SHIFT=:36, EMPCODE_OLD=:37, TEMPBLOCK=:38, NIGHTSHIFT=:39, HANDITYPE=:40, HANDICAP=:41, IM_BOSS=:42, INDEXDIS=:43, TOTALSALARY=:44, FORNIGHT=:45, SALMODE=:46, BANKID=:47, BRANCHID=:48, ACCOUNTNO=:49, MODIFYDATE=:50, MODIFYBY=:51, TEMPCODE=:52, MOB_CARD=:53, FUEL=:54, GUN_ALLOUNCE=:55, VEHICLE_MAIN=:56, CNIC_EXPIRY_DATE=:57, JOINIGDATE=:58, MOTHER_NAME=:59 WHERE ROWID=:60
a63g1ffphjkdc	SELECT SUM(DECODE(NVL(DYS, 0), 1, 0, .75, .25, .50, .50, .25, .75, 0, 1, 0)) FROM ( SELECT NVL(A.EMPCODE, :B2 ) EMPCODE, P.DATED , NVL(STATUS, NVL( DECODE(RTRIM(TO_CHAR(P.DATED, 'DAY')), RTRIM(HRM.GET_EMP_REST(:B2 )), 'WR', NULL), 'A') ) STATUS, NVL(DYS, 0) DYS FROM PERIOD P, ( SELECT EMPCODE, DATED, STATUS, DYS FROM DAILY_ATTENDANCE WHERE EMPCODE=:B2 AND TO_CHAR(DATED, 'MMRRRR')=:B1 ) A WHERE TO_CHAR(P.DATED, 'mmrrrr')=:B1 AND P.DATED=A.DATED(+) ) WHERE STATUS NOT IN ('WR', 'GH') AND EMPCODE=:B2 AND TO_CHAR(DATED, 'MMRRRR')=:B1
a8bkyjcv4nuvx	SELECT C.CUS_NAME || '-' || A.ACC_DESC , A.S1, A.S2, A.S3, A.S4, A.S5, A.S6, A.S7, A.S8, A.S9, A.S10, A.S11, A.S12, A.S13, A.S14, A.S15, A.S16, A.S17, A.S18, A.S19, A.S20 FROM ACC_SIZE A, CUSTOMER C WHERE A.CUS_ID = C.CUS_ID AND A.ACC_ID = :b1
ab21w6xh2m3r2	DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin DBMS_SNAPSHOT.REFRESH( 'PROCESSING.mv_gstk'); end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
axqbs2kdrnra7	INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "PRODUCTION"."MV_STITCHING" select distinct trunc(t.dated) dated, TO_CHAR(t.DATED, 'MON-RRRR') MTH, TO_CHAR(t.DATED, 'RRRR') YR, to_number(TO_CHAR(t.DATED, 'MM'))mt, C.CUS_NAME, P.PHASE_DESC, V.c_Name, t.planno, dept_code_validation(t.unit_num) unit, g.GARMANT_DESCRIPTION PRODUCT, nvl(GET_SAM(t.planno, t.garment_id), 0) SAM, sum(t.stitched_qty) Sth_qty from opr_wages_emp_master t, tblplanorder o, kcl_po_main m, GARMENT_TYPE g, CUSTOMER C, PHASE P, cdtl v where o.plan_no=t.planno and m.pono=o.order_no and t.garment_id=g.garment_id and c.cus_id=m.cus_id and p.phase_id=m.phase_id and v.c_code=t.ccode group by TO_CHAR(t.DATED, 'MON-RRRR'), TO_CHAR(t.DATED, 'RRRR') , to_number(TO_CHAR(t.DATED, 'MM')), C.CUS_NAME, P.PHASE_DESC, v.c_name, g.GARMANT_DESCRIPTION , t.planno, t.unit_num, t.garment_id, trunc(t.dated)
c6rkc2anypq67	SELECT SUM(NVL(QTY_IN, 0)-NVL(QTY_OUT, 0)) FROM ST_MST A, ST_DTL B WHERE A.ENTRY_NO=B.ENTRY_NO AND TRUNC(A.DATED) BETWEEN '13-DEC-12' AND :B3 AND B.PO_NO=:B2 AND B.ITEM_CODE=:B1 AND A.ENTRY_TYPE IN ('RST', 'IFD')
cdy4c52fdcanv	SELECT DISTINCT D.PO_NO, D.ITEM_CODE, SUBSTR(PRODUCTION.GET_YARN(D.ITEM_CODE), 1, 100)ITEM_DESC , PROCESSING.ST_YARN_STK(:P_TO, D.PO_NO, D.ITEM_CODE, :P_STK)STK FROM PROCESSING.ST_MST M, PROCESSING.ST_DTL D WHERE M.ENTRY_NO=D.ENTRY_NO AND TRUNC(M.DATED)<=:P_TO AND TO_NUMBER(SUBSTR(D.ENTRY_NO, 4, 2))=:P_CCODE AND D.ITEM_CODE=NVL(:P_ITEM, D.ITEM_CODE) AND PROCESSING.ST_YARN_STK(:P_TO, D.PO_NO, D.ITEM_CODE, :P_STK)<>0
ch0thvp9q6y1w	SELECT DISTINCT T.PLAN_NO, T.PLAN_DATE, to_char(WM_CONCAT(DISTINCT ORDER_NO))PAKPO FROM TBLPLANSHEET T, tblplanorder o WHERE T.PLAN_DATE >='01-JAN-2011' and T.PLAN_NO=O.PLAN_NO GROUP BY T.PLAN_NO, T.PLAN_DATE /*AND UNIT=decode(:GLOBAL.COMPANY_CODE, 13, unit, :GLOBAL.COMPANY_CODE)*/ ORDER BY T.PLAN_DATE DESC
cm5vu20fhtnq1	獥汥捴 ⨫⁣潮湥捴形祟晩汴敲楮朠⨯⁰物癩汥来⌬汥癥氠晲潭⁳祳慵瑨␠捯湮散琠批⁧牡湴敥⌽灲楯爠灲楶楬敧攣⁡湤⁰物癩汥来⌾〠獴慲琠睩瑨⁧牡湴敥⌽㨱⁡湤⁰物癩汥来⌾
d8h9553qm1h2j	SELECT R.LOT#, DECODE(NVL(SUM(R.QTY), 0), 0, 1, NVL(SUM(R.QTY), 0)) SIZZ FROM (SELECT DISTINCT M.DOC_DATE, M.LOT#, B.PLAN_NO, B.JOB#, A.SIZ, B.QTY FROM CUT_PLAN_STS_VIEW B, ACC_SIZE_VIEW A, CUT_JBCARD_M M WHERE A.ACC_ID = B.ACC_ID AND B.COL = A.COL AND B.PLAN_NO = :b1 AND A.SIZ IS NOT NULL AND M.LOT# = :b2 AND M.PLAN_NO = B.PLAN_NO AND M.JOB# = B.JOB# UNION ALL SELECT T.DOC_DATE, T.LOT#, T.PLAN_NO, T.JOB#, D.FABRIC_CODE SIZ, NVL(SUM(D.QTY), 0) QTY FROM CUT_JBCARD_M T, CUT_JBCARD_D D WHERE D.DOC_ID = T.DOC_ID AND T.PLAN_NO = :b1 GROUP BY T.JOB#, T.PLAN_NO, D.FABRIC_CODE, D.USED_FAB, T.DOC_DATE, T.LOT# ) R WHERE R.SIZ NOT LIKE ('FAB%') AND R.LOT# = :b2 GROUP BY R.LOT#
f36gkv9w0n0yk	SELECT T.PLAN_NO, PRODUCTION.GET_FABRIC_PERSON(PS.FAB_PER_ID) FABRIC_PERSON, CCODE_VALIDATION(PRODUCTION.GET_PLAN_CCODE(T.PLAN_NO) ) UNIT, CDK_ID, FABRIC, COLOR, MA, DW, T.RACK_NO, PRODUCTION.GET_RACK(T.RACK_NO) RACK, STK FROM ( SELECT PLAN_NO, X.CDK_ID, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'FABRIC') FABRIC, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'FABRIC_CODE') FABRIC_CODE, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'COLOR') COLOR, PRODUCTION.GET_TBL_CUTDYE_KNIT(X.CDK_ID, 'MAIN_BODY') MA, X.RACK_NO, Y.DW, NVL(SUM(REC), 0)-NVL(SUM(ISU), 0) STK FROM ( SELECT PLAN_NO, D.RACK_NO, D.CDK_ID, NVL(SUM(D.REC_QTY), 0) REC, 0 ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DHR') AND M.REC_DATE <= :P_TODATE GROUP BY D.CDK_ID, D.RACK_NO, PLAN_NO UNION ALL SELECT PLAN_NO, D.RACK_NO, D.CDK_ID, 0 REC, NVL(SUM(D.REC_QTY), 0) ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D WHERE M.ENTRY_NO=D. ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DGR') AND M.REC_DATE <= :P_TODATE GROUP BY D.CDK_ID, D.RACK_NO, PLAN_NO UNION ALL SELECT PLANNO, D.RACKID, D.CDK_ID, 0 REC , NVL(SUM(QTY), 0) ISU FROM PROCESSING.LOTMAKING M, PROCESSING.LOTMAKING_DTL D WHERE M.LOTNO=D.LOTNO AND M.LOTDATE <= :P_TODATE AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE='GLOT' GROUP BY D.CDK_ID, D.RACKID, PLANNO ) X, ( SELECT DISTINCT C.CDK_ID, DECODE(F.PROCESS_ID, 7, 'DYE', PROCESS_NAME) DW FROM PRODUCTION.CDK_FINISH_ROUT C, PRODUCTION.Finish_rout F where f.PROCESS_ID=c.PROCESS_ID AND C.ORD=(select min(ord) from production.cdk_finish_rout x where x.cdk_id=C.CDK_ID) and C.PROCESS_ID in(2, 3, 7) ) Y WHERE X.CDK_ID=Y.CDK_ID AND 'KPL'=NVL(:P_KPL, 'KPL') GROUP BY X.CDK_ID, Y.DW, PLAN_NO, X.RACK_NO HAVING NVL(SUM(REC), 0)-NVL(SUM(ISU), 0) >:P_STK ) T, PRODUCTION.TBLPLANSHEET PS WHERE T.PLAN_NO = PS.PLAN_NO AND PS.DH_GS = 'Y' AND PS.FAB_HEAD_ID = NVL ( : P_FAB_ER_ID , PS.FAB_HEAD_ID ) AND PS.FAB_PER_ID = NVL ( : P_FAB_PER , PS.FAB_PER_ID ) AND T.PLAN_NO = NVL ( : P_PAN , T.PLAN_NO ) AND ( : P_COLOR IS NULL OR T.COLOR = : P_COLOR ) and t.plan_no not in ( select t.plan_no from processing.dyestockplan t ) AND FABRIC_CODE = NVL ( : P_FABRIC , FABRIC_CODE ) AND DW = NVL ( : P_DYEWASH , DW ) AND MA = NVL ( : P_MA , MA ) UNION ALL SELECT T.PLAN_NO, '' FABIC_PERSON, '' UNIT, T.CDK_ID, PRODUCTION.GET_FABRIC(T.FABRIC_CODE) FABRIC, T.COLOR, T.MA, PROCESSING.GET_DW(T.CDK_ID, 'C') DW, T.RACK_NO, PRODUCTION.GET_RACK(T.RACK_NO) RACK, NVL(SUM(REC), 0)- NVL(SUM(ISU), 0) STK FROM ( SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.FABRIC_CODE, P.COLOR, DECODE(P.MA, 'M', 'MAIN BODY', 'ACCESSORY') MA , NVL(SUM(D.REC_QTY), 0) REC, 0 ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PRODUCTION.TBLDYECONTRACTDTL P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN('DHR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.CONTRACT_NO AND D.CDK_ID=P.CPK GROUP BY M.PLAN_NO, D.CDK_ID, D.RAC K_NO , P.FABRIC_CODE, P.COLOR, P.MA UNION ALL SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.FABRIC_CODE, P.COLOR, DECODE(P.MA, 'M', 'MAIN BODY', 'ACCESSORY') MA , 0 REC, NVL(SUM(D.REC_QTY), 0) ISU FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PRODUCTION.TBLDYECONTRACTDTL P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN('DGR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.CONTRACT_NO AND D.CDK_ID=P.CPK GROUP BY M.PLAN_NO, D.CDK_ID, D.RACK_NO, P.FABRIC_CODE, P.COLOR, P.MA UNION ALL SELECT M.PLANNO, D.RACKID, D.CDK_ID, P.FABRIC_CODE, P.COLOR, DECODE(P.MA, 'M', 'MAIN BODY', 'ACCESSORY') MA , 0 REC , NVL(SUM(QTY), 0) ISU FROM PROCESSING.LOTMAKING M, PROCESSING.LOTMAKING_DTL D, TBLDYECONTRACTDTL P WHERE M.LOTNO=D.LOTNO AND M.LOTDATE <= :P_TODATE AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE='GLOT' AND M.PLANNO=P.CONTRACT_NO AND D.CDK_ID=P.CPK GROUP BY M.PLANNO, D.CDK_ID, D.RACKID , P.FABRIC_CODE, P.COLOR, P.MA ) T WHERE T.FABRIC_CODE = NVL ( : P_FABRIC , FABRIC_CODE ) AND T.PLAN_NO = NVL ( : P_PAN , T.PLAN_NO ) and t.plan_no not in ( select t.plan_no from processing.dyestockplan t ) AND ( : P_COLOR IS NULL OR T.COLOR = : P_COLOR ) AND MA = NVL ( : P_MA , MA ) AND PROCESSING.GET_DW ( T.CDK_ID , 'C' ) = NVL ( : P_DYEWASH , PROCESSING.GET_DW ( T.CDK_ID , 'C' ) ) AND 'LS' = NVL ( : P_KPL , 'LS' ) GROUP BY T.PLAN_NO , CDK_ID , T.FABRIC_CODE , T.COLOR , T.MA , T.RACK_NO HAVING NVL ( SUM ( REC ) , 0 ) - NVL ( SUM ( ISU ) , 0 ) > : P_STK UNION ALL SELECT T.PLAN_NO, '' FABIC_PERSON, unit_name UNIT, T.CDK_ID, FABRIC, T.COLOR, T.MA, DYE_WASH, T.RACK_NO, PRODUCTION.GET_RACK(T.RACK_NO) RACK, NVL(SUM(REC), 0)- NVL(SUM(ISU), 0) STK FROM ( SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.dye_wash, P.fabric, P.color, P.MA, NVL(SUM(D.REC_QTY), 0) REC, 0 ISU, P.FABRIC_CODE, unit_name FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PROCESSING.RND_DYE P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DHR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.dem_no AND D.CDK_ID=P.sd_id AND CORPORATE_SUP_NO=sup_rec_no GROUP BY M.PLAN_NO, D.CDK_ID, D.RACK_NO , D.CDK_ID, P.fabric, P.color, P.MA, P.dye_wash, P.FABRIC_CODE, unit_name UNION ALL SELECT M.PLAN_NO, D.RACK_NO, D.CDK_ID, P.dye_wash, P.fabric, P.color, P.MA, 0 REC, NVL(SUM(D.REC_QTY), 0) ISU, P.FABRIC_CODE, unit_name FROM PROCESSING.DYE_SR_MST M, PROCESSING.DYE_SR_DTL D, PROCESSING.RND_DYE P WHERE M.ENTRY_NO=D.ENTRY_NO AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE IN ('DGR') AND M.REC_DATE <= :P_TODATE AND M.PLAN_NO=P.dem_no AND CORPORATE_SUP_NO=sup_rec_no AND D.CDK_ID=P.sd_id GROUP BY M.PLAN_NO, D.CDK_ID, D.RACK_NO , D.CDK_ID, P.fabric, P.color, P.MA, P.dye_wash, P.FABRIC_CODE, unit_name UNION ALL SELECT M.PLANNO, D.RACKID, D.CDK_ID, P.dye_wash, P.fabric, P.color, P.MA, 0 REC , NVL(SUM(QTY), 0) ISU , P.FABRIC_CODE, unit_name FROM PROCESSING.LOTMAKING M, PROCESSING.LOTMAKING_DTL D, PROCESSING.RND_DYE P WHERE M.LOTNO=D.LOTNO AND M.LOTDATE <= :P_TODATE AND M.CCODE=:P_CCODE AND M.ENTRY_TYPE='GLOT' AND M.PLANNO=P.dem_no AND M.RND_SUPNO=P.sup_rec_no AND D.CDK_ID=P.sd_id GROUP BY M.PLANNO, D.CDK_ID, D.RACKID, P.fabric, P.color, P.MA, P.dye_wash, P.FABRIC_CODE, unit_name ) T WHERE T.FABRIC_CODE = NVL ( : P_FABRIC , FABRIC_CODE ) AND T.PLAN_NO = NVL ( : P_PAN , T.PLAN_NO ) AND ( : P_COLOR IS NULL OR T.COLOR = : P_COLOR ) and t.plan_no not in ( select t.plan_no from processing.dyestockplan t ) AND MA = NVL ( : P_MA , MA ) AND DYE_WASH = NVL ( : P_DYEWASH , DYE_WASH ) AND 'RND' = NVL ( : P_KPL , 'RND' ) GROUP BY T.PLAN_NO , CDK_ID , T.FABRIC_CODE , T.COLOR , T.MA , FABRIC , DYE_WASH , unit_name , RACK_NO HAVING NVL ( SUM ( REC ) , 0 ) - NVL ( SUM ( ISU ) , 0 ) <> : P_STK ORDER BY 1 ASC, 2 ASC, 3 ASC, 6 ASC, 10 ASC, 9 ASC , 1
f4032m9c04cv1	SELECT DSC FROM UKPRODUCT WHERE PRODID = :b1
f56xhasgt6st2	SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1), 0), NVL(SUM(C2), 0) FROM (SELECT /*+ NO_PARALLEL("MLOG$_PRODUCTION_ACHIEVEME") FULL("MLOG$_PRODUCTION_ACHIEVEME") NO_PARALLEL_INDEX("MLOG$_PRODUCTION_ACHIEVEME") */ 1 AS C1, 1 AS C2 FROM "PRODUCTION"."MLOG$_PRODUCTION_ACHIEVEME" SAMPLE BLOCK (0.007177 , 1) SEED (1) "MLOG$_PRODUCTION_ACHIEVEME") SAMPLESUB
f9utav8z8czs4	SELECT SUM(QTY) FROM (SELECT S1 QTY, 'S1' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S2 QTY, 'S2' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S3 QTY, 'S3' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S4 QTY, 'S4' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S5 QTY, 'S5' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S6 QTY, 'S6' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S7 QTY, 'S7' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S8 QTY, 'S8' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S9 QTY, 'S9' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S10 QTY, 'S10' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S11 QTY, 'S11' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S12 QTY, 'S12' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S13 QTY, 'S13' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S14 QTY, 'S14' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S15 QTY, 'S15' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S16 QTY, 'S16' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S17 QTY, 'S17' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S18 QTY, 'S18' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S19 QTY, 'S19' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL UNION ALL SELECT S20 QTY, 'S20' AS SIZ , PO, HITID, TYPE FROM PRODUCTION_ACHIEVEMENTS_DTL ) M WHERE TYPE=:B2 AND SIZ=:B1 AND EXISTS (SELECT DISTINCT HITID FROM STITCHING_PLAN_MAIN B WHERE PLAN_NO=:B4 AND B.HITID=M.HITID AND GET_HIT_GAR(B.HITID)=:B3 )
fm1jr4dtpjyx2	SELECT SUM(SQTY) FROM UKPO P, UKPOHIT H WHERE P.PK = H.PK AND P.ORDERID = :b1 AND H.ENTRYTYPE = 'BOOKING' AND H.STATUS = 1 AND P.STATUS NOT IN ( 'CANCELLED', 'LIABILITY' )
fys1zsgmvh9ka	SELECT PHASE_DESC FROM PHASE WHERE PHASE_ID = :b1
g55xjd69zmcmm	SELECT ALL E . EMPCODE , D . DATED ATT_DATE , D . TIMEIN , D . TIMEOUT , D . STATUS , D . WHOURS || ':' || D . WMIN TOTALTIME , DECODE ( D . STATUS , 'WR' , 0 , DYS ) DYS , HRM . DEPTCODE_VALIDATION ( E . DEPTCODE ) DEPARTMENT , E . EMPNAME , E . FNAME , TO_NUMBER ( TO_CHAR ( DATED , 'DD' ) ) DAY , HRM . DESIGCODE_VALIDATION ( E . DESIGCODE ) DESIGNATION , HRM . GET_OVERTIME ( E . EMPCODE , D . DATED ) OVERTIME , 26 - hrm . GET_EMP_ABS ( E . EMPCODE , to_char ( : P_FDATE , 'MMRRRR' ) ) TOT_DYS , case when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) < 0 or mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) < 0 ) and d . status not in ( 'WR' , 'GH' ) then '00:00' when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) = 0 AND mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) <= 40 ) and d . status not in ( 'WR' , 'GH' ) then '00:00' when d . status in ( 'WR' , 'GH' ) and nvl ( e . otallow , 0 ) = 1 then WHOURS || ':' || D . WMIN when nvl ( e . otallow , 0 ) = 1 then lpad ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) , 2 , 0 ) || ':' || lpad ( mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) , 2 , 0 ) else '00:00' end dif_time , case when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) < 0 or mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) < 0 ) and d . status not in ( 'WR' , 'GH' ) then 0 when ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) = 0 AND mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) <= 40 ) and d . status not in ( 'WR' , 'GH' ) then 0 when d . status in ( 'WR' , 'GH' ) and nvl ( e . otallow , 0 ) = 1 then WHOURS * 60 + D . WMIN when nvl ( e . otallow , 0 ) = 1 then nvl ( ( trunc ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) / 60 ) * 60 ) + ( mod ( ( ( ( d . whours * 60 + d . wmin ) - ( 9 * 60 ) ) ) , 60 ) ) , 0 ) else 0 end tmint FROM DAILY_ATTENDANCE D , TBLHRMEMPLOYEE E WHERE ( E . EMPCODE = D . EMPCODE ) AND E . EMPCODE = NVL ( : P_EMPCODE , E . EMPCODE ) AND E . DEPTCODE = NVL ( : P_DEPTCODE , E . DEPTCODE ) AND E . SALMODE = NVL ( : P_SALMODE , E . SALMODE ) AND E . EMPTYPE = NVL ( : P_EMPTYPE , E . EMPTYPE ) AND E . STATUS = NVL ( : P_STATUS , E . STATUS ) AND E . CCODE = : P_CCODE AND DATED BETWEEN : P_FDATE AND : P_TDATE AND E.EMPCODE = D.EMPCODE ORDER BY 1 , 2

Back to SQL Statistics
Back to Top

Instance Activity Statistics

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

Key Instance Activity Stats

• Ordered by statistic name

Back to Instance Activity Statistics
Back to Top

Other Instance Activity Stats

• Ordered by statistic name

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

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

Back to Instance Activity Statistics
Back to Top

IO Stats

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

IOStat by Function summary

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

Back to IO Stats
Back to Top

IOStat by Filetype summary

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

Back to IO Stats
Back to Top

IOStat by Function/Filetype summary

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

Back to IO Stats
Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File

Back to IO Stats
Back to Top

Buffer Pool Statistics

• Buffer Pool Statistics
• Checkpoint Activity

Buffer Pool Statistics

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

Back to Buffer Pool Statistics
Back to Top

Checkpoint Activity

• Total Physical Writes: 2,684,236

Back to Buffer Pool Statistics
Back to Top

Advisory Statistics

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

Instance Recovery Stats

• B: Begin Snapshot, E: End Snapshot

Back to Advisory Statistics
Back to Top

MTTR Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

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

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

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

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations