Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SQL_ID a8buyc0rb4jkd, child number 0
- -------------------------------------
- SELECT SUM (TOTAL_LABELS) FROM (SELECT A.*, CASE
- WHEN (A.REF_CASE_NBR IS NOT NULL OR A.LOCN_CLASS='C' ) AND
- Mod(TO_BE_PAKD_UNITS,STD_PACK_QTY)=0 THEN
- (TO_BE_PAKD_UNITS/STD_PACK_QTY)*CMPLTS WHEN (A.REF_CASE_NBR
- IS NOT NULL OR A.LOCN_CLASS='C' ) AND
- Mod(TO_BE_PAKD_UNITS,STD_PACK_QTY)!=0 AND (TO_BE_PAKD_UNITS <
- STD_PACK_QTY) THEN TO_BE_PAKD_UNITS * CMPLTS WHEN
- (A.REF_CASE_NBR IS NOT NULL OR A.LOCN_CLASS='C' ) AND
- Mod(TO_BE_PAKD_UNITS,STD_PACK_QTY)!=0 AND TO_BE_PAKD_UNITS >
- STD_PACK_QTY THEN floor(TO_BE_PAKD_UNITS/STD_PACK_QTY)*CMPLTS +
- Mod(TO_BE_PAKD_UNITS,STD_PACK_QTY)*CMPLTS WHEN
- A.LOCN_CLASS='A' THEN 1 ELSE CMPLTS*TO_BE_PAKD_UNITS
- END AS TOTAL_LABELS FROM (SELECT
- CH.CARTON_NBR,CH.REF_CASE_NBR AS REF_CASE_NBR, LH.LOCN_CLASS,
- CD.TO_BE_PAKD_UNITS,IM.STD_PACK_QTY,CASE IM.INCUB_DAYS WHEN 0 THEN 1
- ELSE IM.INCUB_DAYS END AS "CMPLTS" FROM CARTON_HDR CH,
- CARTON_DTL CD, ITEM_MASTER IM , LOCN_HDR LH,
- Plan hash value: 1824149884
- ----------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | | 44698 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 117 | | | |
- |* 2 | HASH JOIN | | 37664 | 4303K| 4232K| 44698 (6)| 00:01:23 |
- |* 3 | HASH JOIN | | 37664 | 3788K| 2696K| 40449 (5)| 00:01:15 |
- |* 4 | TABLE ACCESS FULL | PKT_HDR | 110K| 1401K| | 5428 (3)| 00:00:11 |
- |* 5 | HASH JOIN | | 37664 | 3310K| 3024K| 34692 (6)| 00:01:05 |
- | 6 | INDEX FULL SCAN | LH_WAVE_IND1 | 129K| 1511K| | 190 (3)| 00:00:01 |
- | 7 | MERGE JOIN | | 37664 | 2868K| | 34177 (6)| 00:01:04 |
- |* 8 | TABLE ACCESS BY INDEX ROWID| CARTON_HDR | 15165 | 710K| | 8481 (3)| 00:00:16 |
- |* 9 | INDEX FULL SCAN | PK_CARTON_HDR | 21322 | | | 5356 (4)| 00:00:10 |
- |* 10 | SORT JOIN | | 1412K| 40M| 108M| 25696 (7)| 00:00:48 |
- | 11 | TABLE ACCESS FULL | CARTON_DTL | 1412K| 40M| | 13355 (6)| 00:00:25 |
- | 12 | TABLE ACCESS FULL | ITEM_MASTER | 184K| 2527K| | 3788 (10)| 00:00:07 |
- ----------------------------------------------------------------------------------------------------------
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
- 1 - SEL$5C160134
- 4 - SEL$5C160134 / PH@SEL$3
- 6 - SEL$5C160134 / LH@SEL$3
- 8 - SEL$5C160134 / CH@SEL$3
- 9 - SEL$5C160134 / CH@SEL$3
- 11 - SEL$5C160134 / CD@SEL$3
- 12 - SEL$5C160134 / IM@SEL$3
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("CD"."SKU_ID"="IM"."SKU_ID")
- 3 - access("PH"."PKT_CTRL_NBR"="CH"."PKT_CTRL_NBR")
- 4 - filter("PH"."ORD_TYPE"='V')
- 5 - access("CH"."PICK_LOCN_ID"="LH"."LOCN_ID")
- 8 - filter(("CH"."PICK_LOCN_ID" IS NOT NULL AND "CH"."WHSE"='02'))
- 9 - filter(SYS_OP_C2C("CH"."CARTON_NBR")=:ID0)
- 10 - access("CH"."CARTON_NBR"="CD"."CARTON_NBR")
- filter("CH"."CARTON_NBR"="CD"."CARTON_NBR")
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
- 1 - (#keys=0) SUM(CASE WHEN (("CH"."REF_CASE_NBR" IS NOT NULL OR "LH"."LOCN_CLASS"='C') AND
- MOD("CD"."TO_BE_PAKD_UNITS","IM"."STD_PACK_QTY")=0) THEN
- "CD"."TO_BE_PAKD_UNITS"/"IM"."STD_PACK_QTY"*CASE "IM"."INCUB_DAYS" WHEN 0 THEN 1 ELSE
- "IM"."INCUB_DAYS" END WHEN (("CH"."REF_CASE_NBR" IS NOT NULL OR "LH"."LOCN_CLASS"='C') AND
- MOD("CD"."TO_BE_PAKD_UNITS","IM"."STD_PACK_QTY")<>0 AND
- "CD"."TO_BE_PAKD_UNITS"<"IM"."STD_PACK_QTY") THEN "CD"."TO_BE_PAKD_UNITS"*CASE "IM"."INCUB_DAYS"
- WHEN 0 THEN 1 ELSE "IM"."INCUB_DAYS" END WHEN (("CH"."REF_CASE_NBR" IS NOT NULL OR
- "LH"."LOCN_CLASS"='C') AND MOD("CD"."TO_BE_PAKD_UNITS","IM"."STD_PACK_QTY")<>0 AND
- "CD"."TO_BE_PAKD_UNITS">"IM"."STD_PACK_QTY") THEN
- FLOOR("CD"."TO_BE_PAKD_UNITS"/"IM"."STD_PACK_QTY")*CASE "IM"."INCUB_DAYS" WHEN 0 THEN 1 ELSE
- "IM"."INCUB_DAYS" END +MOD("CD"."TO_BE_PAKD_UNITS","IM"."STD_PACK_QTY")*CASE "IM"."INCUB_DAYS"
- WHEN 0 THEN 1 ELSE "IM"."INCUB_DAYS" END WHEN "LH"."LOCN_CLASS"='A' THEN 1 ELSE CASE
- "IM"."INCUB_DAYS" WHEN 0 THEN 1 ELSE "IM"."INCUB_DAYS" END *"CD"."TO_BE_PAKD_UNITS" END )[22]
- 2 - (#keys=1) "LH"."LOCN_CLASS"[VARCHAR2,4], "CH"."REF_CASE_NBR"[VARCHAR2,80],
- "CD"."TO_BE_PAKD_UNITS"[NUMBER,22], "IM"."INCUB_DAYS"[NUMBER,22], "IM"."STD_PACK_QTY"[NUMBER,22]
- 3 - (#keys=1) "LH"."LOCN_CLASS"[VARCHAR2,4], "CH"."REF_CASE_NBR"[VARCHAR2,80],
- "CD"."SKU_ID"[VARCHAR2,40], "CD"."TO_BE_PAKD_UNITS"[NUMBER,22]
- 4 - "PH"."PKT_CTRL_NBR"[VARCHAR2,40]
- 5 - (#keys=1) "LH"."LOCN_CLASS"[VARCHAR2,4], "CH"."REF_CASE_NBR"[VARCHAR2,80],
- "CH"."PKT_CTRL_NBR"[VARCHAR2,40], "CD"."TO_BE_PAKD_UNITS"[NUMBER,22], "CD"."SKU_ID"[VARCHAR2,40]
- 6 - "LH"."LOCN_ID"[VARCHAR2,40], "LH"."LOCN_CLASS"[VARCHAR2,4]
- 7 - "CH"."REF_CASE_NBR"[VARCHAR2,80], "CH"."PKT_CTRL_NBR"[VARCHAR2,40],
- "CH"."PICK_LOCN_ID"[VARCHAR2,40], "CD"."SKU_ID"[VARCHAR2,40], "CD"."TO_BE_PAKD_UNITS"[NUMBER,22]
- 8 - "CH"."CARTON_NBR"[VARCHAR2,80], "CH"."PKT_CTRL_NBR"[VARCHAR2,40],
- "CH"."PICK_LOCN_ID"[VARCHAR2,40], "CH"."REF_CASE_NBR"[VARCHAR2,80]
- 9 - "CH".ROWID[ROWID,10], "CH"."CARTON_NBR"[VARCHAR2,80]
- 10 - (#keys=1) "CD"."CARTON_NBR"[VARCHAR2,80], "CD"."SKU_ID"[VARCHAR2,40],
- "CD"."TO_BE_PAKD_UNITS"[NUMBER,22]
- 11 - "CD"."CARTON_NBR"[VARCHAR2,80], "CD"."TO_BE_PAKD_UNITS"[NUMBER,22],
- "CD"."SKU_ID"[VARCHAR2,40]
- 12 - "IM"."SKU_ID"[VARCHAR2,40], "IM"."STD_PACK_QTY"[NUMBER,22], "IM"."INCUB_DAYS"[NUMBER,22]
Add Comment
Please, Sign In to add comment
