SELECT BATCH_ID,
BATCH_NUMBER,
BATCH_STATUS,
INVENTORY_ITEM_ID,
INGREDIENTS,
ORGANIZATION_ID,
ORGANIZATION_CODE,
SUBINVENTORY_CODE,
LOT_NUMBER,
COST_CENTER,
CC_CODE,
WORK_CENTER,
WC_CODE,
RECIPE_ID,
RECIPE_NUM,
RECIPE_VERSION,
FORMULA_ID,
PLAN_START_DATE,
PLAN_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
BATCH_CREATION_DATE,
DUE_DATE,
FORMULA_NUM,
FORMULA_VERSION,
ROUTING_ID,
ROUTING_NUM,
ROUTING_VERSION,
PRIMARY_UOM_CODE,
DETAIL_UOM,
CONVERSION_RATE,
ACTUAL_QUANTITY,
MAT_TRAN_ACTUAL_QUANTITY,
PLAN_QUANTITY,
PRIMARY_PLAN_QUANTITY,
PRIMARY_ACTUAL_QUANTITY,
LOT_ACTUAL_QUANTITY,
ONHAND_QUANTITY,
PRIMARY_RESERVATION_QUANTITY,
ONHAND_QUANTITY-PRIMARY_RESERVATION_QUANTITY AVAILABLE_QUANTITY
FROM ( SELECT SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_ID BATCH_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_NUM BATCH_NUMBER,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID ORGANIZATION_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_CODE
ORGANIZATION_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE
SUBINVENTORY_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.COST_CENTER COST_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.CC_CODE CC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_ID FORMULA_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_CREATION_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.DUE_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_NUM FORMULA_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_VERSION FORMULA_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.WORK_CENTER WORK_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.WC_CODE WC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER LOT_NUMBER,
SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.INGREDIENTS INGREDIENTS,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_STATUS BATCH_STATUS,
SQ_GMD_MMT_MTLN_MTR_MOQD.PRIMARY_UOM_CODE PRIMARY_UOM_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.DETAIL_UOM DETAIL_UOM,
SQ_GMD_MMT_MTLN_MTR_MOQD.CONVERSION_RATE CONVERSION_RATE,
MAX (PLAN_QTY) PLAN_QUANTITY,
MAX (PLAN_QTY * CONVERSION_RATE)
PRIMARY_PLAN_QUANTITY,
MAX (GMD_ACTUAL_QTY) ACTUAL_QUANTITY,
MAX (GMD_ACTUAL_QTY * CONVERSION_RATE)
PRIMARY_ACTUAL_QUANTITY,
MAX (MMT_ACTUAL_QTY)
MAT_TRAN_ACTUAL_QUANTITY,
MAX (MTLN_ACTUAL_QTY)
LOT_ACTUAL_QUANTITY,
MAX (PRIMARY_RESERVATION_QUANTITY)
PRIMARY_RESERVATION_QUANTITY,
SUM (NVL (MOQD.PRIMARY_TRANSACTION_QUANTITY, 0))
ONHAND_QUANTITY
FROM ( SELECT BATCH_ID,
BATCH_NUM,
INVENTORY_ITEM_ID,
INGREDIENTS,
CC_CODE,
RECIPE_ID,
RECIPE_NUM,
RECIPE_VERSION,
FORMULA_ID,
PLAN_START_DATE,
PLAN_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
BATCH_CREATION_DATE,
DUE_DATE,
FORMULA_NUM,
FORMULA_VERSION,
BATCH_STATUS,
ORGANIZATION_ID,
ORGANIZATION_CODE,
COST_CENTER,
WORK_CENTER,
WC_CODE,
ROUTING_ID,
ROUTING_NUM,
ROUTING_VERSION,
DETAIL_UOM,
PRIMARY_UOM_CODE,
NVL (CONVERSION_RATE, 1) CONVERSION_RATE,
SUBINVENTORY_CODE,
LOT_NUMBER,
--MATERIAL_DETAIL_ID,
MAX (GMD_ACTUAL_QTY) GMD_ACTUAL_QTY,
MAX (PLAN_QTY) PLAN_QTY,
MAX (MMT_ACTUAL_QTY) MMT_ACTUAL_QTY,
MAX (MTLN_ACTUAL_QTY) MTLN_ACTUAL_QTY,
SUM (PRIMARY_RESERVATION_QUANTITY)
PRIMARY_RESERVATION_QUANTITY
FROM ( SELECT SQ_GMD_MMT.BATCH_ID,
SQ_GMD_MMT.BATCH_NUM,
SQ_GMD_MMT.MATERIAL_DETAIL_ID,
SQ_GMD_MMT.INVENTORY_ITEM_ID,
SQ_GMD_MMT.INGREDIENTS,
SQ_GMD_MMT.CC_CODE,
SQ_GMD_MMT.RECIPE_ID,
SQ_GMD_MMT.RECIPE_NUM,
SQ_GMD_MMT.RECIPE_VERSION,
SQ_GMD_MMT.FORMULA_ID,
SQ_GMD_MMT.PLAN_START_DATE,
SQ_GMD_MMT.PLAN_END_DATE,
SQ_GMD_MMT.ACTUAL_START_DATE,
SQ_GMD_MMT.ACTUAL_END_DATE,
SQ_GMD_MMT.BATCH_CREATION_DATE,
SQ_GMD_MMT.DUE_DATE,
SQ_GMD_MMT.FORMULA_NUM,
SQ_GMD_MMT.FORMULA_VERSION,
SQ_GMD_MMT.BATCH_STATUS,
SQ_GMD_MMT.ORGANIZATION_ID,
SQ_GMD_MMT.ORGANIZATION_CODE,
SQ_GMD_MMT.COST_CENTER,
SQ_GMD_MMT.WORK_CENTER,
SQ_GMD_MMT.WC_CODE,
SQ_GMD_MMT.ROUTING_ID,
SQ_GMD_MMT.ROUTING_NUM,
SQ_GMD_MMT.ROUTING_VERSION,
SQ_GMD_MMT.DETAIL_UOM,
SQ_GMD_MMT.PRIMARY_UOM_CODE,
SQ_GMD_MMT.SUBINVENTORY_CODE,
SQ_GMD_MMT.CONVERSION_RATE,
SQ_GMD_MMT.TRANSACTION_ID,
SQ_MTLN.LOT_NUMBER,
MAX (SQ_GMD_MMT.GMD_ACTUAL_QTY)
GMD_ACTUAL_QTY,
MAX (SQ_GMD_MMT.PLAN_QTY) PLAN_QTY,
MAX (SQ_GMD_MMT.MMT_ACTUAL_QTY)
MMT_ACTUAL_QTY,
SUM (NVL (SQ_MTLN.MTLN_ACTUAL_QTY, 0))
MTLN_ACTUAL_QTY,
SUM (
NVL (
SQ_MTR.PRIMARY_RESERVATION_QUANTITY,
0))
PRIMARY_RESERVATION_QUANTITY
FROM ( SELECT SQ_GMD.BATCH_ID,
SQ_GMD.BATCH_NUM,
SQ_GMD.MATERIAL_DETAIL_ID,
SQ_GMD.INVENTORY_ITEM_ID,
SQ_GMD.INGREDIENTS,
SQ_GMD.CC_CODE,
SQ_GMD.RECIPE_ID,
SQ_GMD.RECIPE_NUM,
SQ_GMD.RECIPE_VERSION,
SQ_GMD.FORMULA_ID,
SQ_GMD.PLAN_START_DATE,
SQ_GMD.PLAN_END_DATE,
SQ_GMD.ACTUAL_START_DATE,
SQ_GMD.ACTUAL_END_DATE,
SQ_GMD.BATCH_CREATION_DATE,
SQ_GMD.DUE_DATE,
SQ_GMD.FORMULA_NUM,
SQ_GMD.FORMULA_VERSION,
SQ_GMD.BATCH_STATUS,
SQ_GMD.ORGANIZATION_ID,
SQ_GMD.ORGANIZATION_CODE,
SQ_GMD.COST_CENTER,
SQ_GMD.DETAIL_UOM,
SQ_GMD.PRIMARY_UOM_CODE,
SQ_GMD.CONVERSION_RATE,
MMT.SUBINVENTORY_CODE,
MMT.TRANSACTION_ID,
MAX (ACTUAL_QTY) GMD_ACTUAL_QTY,
MAX (PLAN_QTY) PLAN_QTY,
SUM (NVL (MMT.PRIMARY_QUANTITY, 0))
MMT_ACTUAL_QTY,
SQ_WORK_CENTER.WORK_CENTER
WORK_CENTER,
SQ_WORK_CENTER.WC_CODE,
SQ_WORK_CENTER.ROUTING_ID,
SQ_WORK_CENTER.ROUTING_NUM,
SQ_WORK_CENTER.ROUTING_VERSION
FROM (SELECT GMD.BATCH_ID BATCH_ID,
GBH.BATCH_NO BATCH_NUM,
GMD.MATERIAL_DETAIL_ID
MATERIAL_DETAIL_ID,
GMD.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
MSIB.SEGMENT1 INGREDIENTS,
MSIB.PRIMARY_UOM_CODE
PRIMARY_UOM_CODE,
GMD.ORGANIZATION_ID
ORGANIZATION_ID,
MP.ORGANIZATION_CODE
ORGANIZATION_CODE,
FVC.DESCRIPTION COST_CENTER,
GRB.RECIPE_ID RECIPE_ID,
GRB.RECIPE_NO RECIPE_NUM,
GRB.RECIPE_VERSION
RECIPE_VERSION,
FFM.FORMULA_ID FORMULA_ID,
FFM.FORMULA_NO FORMULA_NUM,
FFM.FORMULA_VERS
FORMULA_VERSION,
GRB.ATTRIBUTE2 CC_CODE,
GMD.DTL_UM DETAIL_UOM,
CASE
WHEN GBH.BATCH_STATUS =
1
THEN
'Pending'
WHEN GBH.BATCH_STATUS =
2
THEN
'WIP'
END
BATCH_STATUS,
(inv_convert.inv_um_convert(
GMD.INVENTORY_ITEM_ID,
NULL,
GMD.ORGANIZATION_ID,
8,
GMD.PLAN_QTY,
GMD.DTL_UM,
msib.primary_uom_code,
NULL,
NULL)/nvl((case when GMD.PLAN_QTY=0 then 1 else GMD.PLAN_QTY end),1)) CONVERSION_RATE,
NVL (GMD.ACTUAL_QTY, 0)
ACTUAL_QTY,
NVL (GMD.PLAN_QTY, 0)
PLAN_QTY,
GBH.PLAN_START_DATE
PLAN_START_DATE,
GBH.PLAN_CMPLT_DATE
PLAN_END_DATE,
GBH.ACTUAL_START_DATE
ACTUAL_START_DATE,
GBH.ACTUAL_CMPLT_DATE
ACTUAL_END_DATE,
GBH.CREATION_DATE
BATCH_CREATION_DATE,
GBH.DUE_DATE
DUE_DATE
FROM GME.GME_MATERIAL_DETAILS
GMD
INNER JOIN
GME.GME_BATCH_HEADER GBH
ON (GBH.BATCH_ID =
GMD.BATCH_ID)
INNER JOIN
GMD.GMD_RECIPE_VALIDITY_RULES
GRVR
ON (GBH.RECIPE_VALIDITY_RULE_ID =
GRVR.RECIPE_VALIDITY_RULE_ID)
INNER JOIN
GMD.GMD_RECIPES_B GRB
ON ( GRB.ROUTING_ID =
GBH.ROUTING_ID
AND GRB.RECIPE_ID =
GRVR.RECIPE_ID)
INNER JOIN
INV.MTL_SYSTEM_ITEMS_B MSIB
ON ( GMD.INVENTORY_ITEM_ID =
MSIB.INVENTORY_ITEM_ID
AND GMD.ORGANIZATION_ID =
MSIB.ORGANIZATION_ID)
INNER JOIN
MTL_PARAMETERS MP
ON (GMD.ORGANIZATION_ID =
MP.ORGANIZATION_ID)
INNER JOIN
FM_FORM_MST_B FFM
ON (FFM.FORMULA_ID =
GRB.FORMULA_ID)
INNER JOIN
FND_LOOKUP_VALUES FVC
ON ( FVC.LOOKUP_TYPE =
'XXGMD_COST_CENTERS'
AND FVC.LANGUAGE =
'US'
AND FVC.LOOKUP_CODE =
MP.ORGANIZATION_CODE
|| '-'
|| GRB.ATTRIBUTE2)
WHERE 1=1
AND GBH.BATCH_STATUS IN
(1, 2)
) SQ_GMD
INNER JOIN
( SELECT GBH.BATCH_ID,
LISTAGG (
FVW.DESCRIPTION,
',')
WITHIN GROUP (ORDER BY
GBH.BATCH_ID)
WORK_CENTER,
LISTAGG (
GOB.ATTRIBUTE1,
',')
WITHIN GROUP (ORDER BY
GBH.BATCH_ID)
WC_CODE,
FRD.ROUTING_ID,
GRT.ROUTING_NO ROUTING_NUM,
GRT.ROUTING_VERS
ROUTING_VERSION
FROM GMD_OPERATIONS_B GOB,
APPS.FM_ROUT_DTL FRD,
GMD.GMD_RECIPES_B GRB,
GME_BATCH_HEADER GBH,
GMD_RECIPE_VALIDITY_RULES
GRVR,
FND_LOOKUP_VALUES FVW,
MTL_PARAMETERS MP,
GMD_ROUTINGS_B GRT
WHERE GRT.ROUTING_ID =
FRD.ROUTING_ID
AND GBH.ORGANIZATION_ID =
MP.ORGANIZATION_ID
AND GBH.RECIPE_VALIDITY_RULE_ID =
GRVR.RECIPE_VALIDITY_RULE_ID
AND GRVR.RECIPE_ID =
GRB.RECIPE_ID
AND FRD.ROUTING_ID =
GRB.ROUTING_ID
AND GOB.OPRN_ID =
FRD.OPRN_ID
AND BATCH_STATUS IN
(01, 02)
AND FVW.LOOKUP_TYPE =
'XXGMD_WORK_CENTERS'
AND FVW.LANGUAGE = 'US'
AND FVW.LOOKUP_CODE =
ORGANIZATION_CODE
|| '-'
|| GOB.ATTRIBUTE1
GROUP BY GBH.BATCH_ID,
FRD.ROUTING_ID,
GRT.ROUTING_NO,
GRT.ROUTING_VERS)
SQ_WORK_CENTER
ON (SQ_GMD.BATCH_ID =
SQ_WORK_CENTER.BATCH_ID)
LEFT OUTER JOIN
INV.MTL_MATERIAL_TRANSACTIONS MMT
ON ( SQ_GMD.BATCH_ID =
MMT.TRANSACTION_SOURCE_ID
AND SQ_GMD.MATERIAL_DETAIL_ID =
MMT.TRX_SOURCE_LINE_ID
AND SQ_GMD.ORGANIZATION_ID =
MMT.ORGANIZATION_ID
AND SQ_GMD.INVENTORY_ITEM_ID =
MMT.INVENTORY_ITEM_ID)
GROUP BY SQ_GMD.BATCH_ID,
SQ_GMD.BATCH_NUM,
SQ_GMD.MATERIAL_DETAIL_ID,
SQ_GMD.INVENTORY_ITEM_ID,
SQ_GMD.PRIMARY_UOM_CODE,
SQ_GMD.ORGANIZATION_ID,
SQ_GMD.COST_CENTER,
SQ_WORK_CENTER.WORK_CENTER,
SQ_WORK_CENTER.WC_CODE,
SQ_WORK_CENTER.ROUTING_ID,
SQ_WORK_CENTER.ROUTING_NUM,
SQ_WORK_CENTER.ROUTING_VERSION,
SQ_GMD.DETAIL_UOM,
MMT.SUBINVENTORY_CODE,
MMT.TRANSACTION_ID,
SQ_GMD.INGREDIENTS,
SQ_GMD.CC_CODE,
SQ_GMD.RECIPE_ID,
SQ_GMD.RECIPE_NUM,
SQ_GMD.RECIPE_VERSION,
SQ_GMD.FORMULA_ID,
SQ_GMD.PLAN_START_DATE,
SQ_GMD.PLAN_END_DATE,
SQ_GMD.ACTUAL_START_DATE,
SQ_GMD.ACTUAL_END_DATE,
SQ_GMD.BATCH_CREATION_DATE,
SQ_GMD.DUE_DATE,
SQ_GMD.FORMULA_NUM,
SQ_GMD.FORMULA_VERSION,
SQ_GMD.BATCH_STATUS,
SQ_GMD.CONVERSION_RATE,
SQ_GMD.ORGANIZATION_CODE)
SQ_GMD_MMT
LEFT OUTER JOIN
( SELECT TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
LOT_NUMBER,
TRANSACTION_SOURCE_ID,
SUM (NVL (PRIMARY_QUANTITY, 0))
MTLN_ACTUAL_QTY
FROM INV.MTL_TRANSACTION_LOT_NUMBERS
GROUP BY TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
LOT_NUMBER,
TRANSACTION_SOURCE_ID) SQ_MTLN
ON ( SQ_GMD_MMT.TRANSACTION_ID =
SQ_MTLN.TRANSACTION_ID
AND SQ_GMD_MMT.ORGANIZATION_ID =
SQ_MTLN.ORGANIZATION_ID
AND SQ_GMD_MMT.INVENTORY_ITEM_ID =
SQ_MTLN.INVENTORY_ITEM_ID
AND SQ_GMD_MMT.BATCH_ID =
SQ_MTLN.TRANSACTION_SOURCE_ID)
LEFT OUTER JOIN
( SELECT ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE_ID,
SUBINVENTORY_CODE,
LOT_NUMBER,
SUM (PRIMARY_RESERVATION_QUANTITY)
PRIMARY_RESERVATION_QUANTITY
FROM MTL_RESERVATIONS MTR
GROUP BY ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEMAND_SOURCE_LINE_ID,
DEMAND_SOURCE_HEADER_ID,
SUBINVENTORY_CODE,
LOT_NUMBER ) SQ_MTR
ON ( 1=1
AND SQ_MTR.DEMAND_SOURCE_HEADER_ID = SQ_GMD_MMT.BATCH_ID
AND SQ_MTR.DEMAND_SOURCE_LINE_ID = SQ_GMD_MMT.MATERIAL_DETAIL_ID
AND SQ_MTLN.LOT_NUMBER = SQ_MTR.LOT_NUMBER
AND SQ_MTR.ORGANIZATION_ID =SQ_GMD_MMT.ORGANIZATION_ID
AND SQ_MTR.INVENTORY_ITEM_ID =SQ_GMD_MMT.INVENTORY_ITEM_ID
AND SQ_MTR.SUBINVENTORY_CODE=SQ_GMD_MMT.SUBINVENTORY_CODE
)
GROUP BY SQ_GMD_MMT.BATCH_ID,
SQ_GMD_MMT.BATCH_NUM,
SQ_GMD_MMT.MATERIAL_DETAIL_ID,
SQ_GMD_MMT.INVENTORY_ITEM_ID,
SQ_GMD_MMT.PRIMARY_UOM_CODE,
SQ_GMD_MMT.INGREDIENTS,
SQ_GMD_MMT.CC_CODE,
SQ_GMD_MMT.RECIPE_ID,
SQ_GMD_MMT.RECIPE_NUM,
SQ_GMD_MMT.RECIPE_VERSION,
SQ_GMD_MMT.FORMULA_ID,
SQ_GMD_MMT.PLAN_START_DATE,
SQ_GMD_MMT.PLAN_END_DATE,
SQ_GMD_MMT.ACTUAL_START_DATE,
SQ_GMD_MMT.ACTUAL_END_DATE,
SQ_GMD_MMT.BATCH_CREATION_DATE,
SQ_GMD_MMT.DUE_DATE,
SQ_GMD_MMT.FORMULA_NUM,
SQ_GMD_MMT.FORMULA_VERSION,
SQ_GMD_MMT.BATCH_STATUS,
SQ_GMD_MMT.ORGANIZATION_ID,
SQ_GMD_MMT.ORGANIZATION_CODE,
SQ_GMD_MMT.COST_CENTER,
SQ_GMD_MMT.WORK_CENTER,
SQ_GMD_MMT.WC_CODE,
SQ_GMD_MMT.ROUTING_ID,
SQ_GMD_MMT.ROUTING_NUM,
SQ_GMD_MMT.ROUTING_VERSION,
SQ_GMD_MMT.DETAIL_UOM,
SQ_GMD_MMT.SUBINVENTORY_CODE,
SQ_GMD_MMT.TRANSACTION_ID,
SQ_GMD_MMT.CONVERSION_RATE,
SQ_MTLN.LOT_NUMBER) SQ_GMD_MMT_MTLN_MTR
GROUP BY BATCH_ID,
BATCH_NUM,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
COST_CENTER,
WORK_CENTER,
WC_CODE,
ROUTING_ID,
ROUTING_NUM,
ROUTING_VERSION,
DETAIL_UOM,
SUBINVENTORY_CODE,
LOT_NUMBER,
INGREDIENTS,
CC_CODE,
RECIPE_ID,
RECIPE_NUM,
RECIPE_VERSION,
FORMULA_ID,
PLAN_START_DATE,
PLAN_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
BATCH_CREATION_DATE,
DUE_DATE,
FORMULA_NUM,
FORMULA_VERSION,
BATCH_STATUS,
ORGANIZATION_CODE,
CONVERSION_RATE,
PRIMARY_UOM_CODE) SQ_GMD_MMT_MTLN_MTR_MOQD
LEFT OUTER JOIN INV.MTL_ONHAND_QUANTITIES_DETAIL MOQD
ON ( MOQD.INVENTORY_ITEM_ID =
SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID
AND MOQD.ORGANIZATION_ID =
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID
AND CASE WHEN SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE IS NULL THEN '0' ELSE MOQD.SUBINVENTORY_CODE
END = NVL(SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE,'0')
AND CASE WHEN SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER IS NULL THEN '0' ELSE MOQD.LOT_NUMBER
END = NVL(SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER,'0')
)
GROUP BY SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.INGREDIENTS,
SQ_GMD_MMT_MTLN_MTR_MOQD.CC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_CREATION_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.DUE_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.COST_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.DETAIL_UOM,
SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER,
SQ_GMD_MMT_MTLN_MTR_MOQD.PRIMARY_UOM_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.CONVERSION_RATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.WORK_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.WC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_STATUS);
BATCH_NUMBER,
BATCH_STATUS,
INVENTORY_ITEM_ID,
INGREDIENTS,
ORGANIZATION_ID,
ORGANIZATION_CODE,
SUBINVENTORY_CODE,
LOT_NUMBER,
COST_CENTER,
CC_CODE,
WORK_CENTER,
WC_CODE,
RECIPE_ID,
RECIPE_NUM,
RECIPE_VERSION,
FORMULA_ID,
PLAN_START_DATE,
PLAN_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
BATCH_CREATION_DATE,
DUE_DATE,
FORMULA_NUM,
FORMULA_VERSION,
ROUTING_ID,
ROUTING_NUM,
ROUTING_VERSION,
PRIMARY_UOM_CODE,
DETAIL_UOM,
CONVERSION_RATE,
ACTUAL_QUANTITY,
MAT_TRAN_ACTUAL_QUANTITY,
PLAN_QUANTITY,
PRIMARY_PLAN_QUANTITY,
PRIMARY_ACTUAL_QUANTITY,
LOT_ACTUAL_QUANTITY,
ONHAND_QUANTITY,
PRIMARY_RESERVATION_QUANTITY,
ONHAND_QUANTITY-PRIMARY_RESERVATION_QUANTITY AVAILABLE_QUANTITY
FROM ( SELECT SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_ID BATCH_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_NUM BATCH_NUMBER,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID ORGANIZATION_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_CODE
ORGANIZATION_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE
SUBINVENTORY_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.COST_CENTER COST_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.CC_CODE CC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_ID FORMULA_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_CREATION_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.DUE_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_NUM FORMULA_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_VERSION FORMULA_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.WORK_CENTER WORK_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.WC_CODE WC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER LOT_NUMBER,
SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.INGREDIENTS INGREDIENTS,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_STATUS BATCH_STATUS,
SQ_GMD_MMT_MTLN_MTR_MOQD.PRIMARY_UOM_CODE PRIMARY_UOM_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.DETAIL_UOM DETAIL_UOM,
SQ_GMD_MMT_MTLN_MTR_MOQD.CONVERSION_RATE CONVERSION_RATE,
MAX (PLAN_QTY) PLAN_QUANTITY,
MAX (PLAN_QTY * CONVERSION_RATE)
PRIMARY_PLAN_QUANTITY,
MAX (GMD_ACTUAL_QTY) ACTUAL_QUANTITY,
MAX (GMD_ACTUAL_QTY * CONVERSION_RATE)
PRIMARY_ACTUAL_QUANTITY,
MAX (MMT_ACTUAL_QTY)
MAT_TRAN_ACTUAL_QUANTITY,
MAX (MTLN_ACTUAL_QTY)
LOT_ACTUAL_QUANTITY,
MAX (PRIMARY_RESERVATION_QUANTITY)
PRIMARY_RESERVATION_QUANTITY,
SUM (NVL (MOQD.PRIMARY_TRANSACTION_QUANTITY, 0))
ONHAND_QUANTITY
FROM ( SELECT BATCH_ID,
BATCH_NUM,
INVENTORY_ITEM_ID,
INGREDIENTS,
CC_CODE,
RECIPE_ID,
RECIPE_NUM,
RECIPE_VERSION,
FORMULA_ID,
PLAN_START_DATE,
PLAN_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
BATCH_CREATION_DATE,
DUE_DATE,
FORMULA_NUM,
FORMULA_VERSION,
BATCH_STATUS,
ORGANIZATION_ID,
ORGANIZATION_CODE,
COST_CENTER,
WORK_CENTER,
WC_CODE,
ROUTING_ID,
ROUTING_NUM,
ROUTING_VERSION,
DETAIL_UOM,
PRIMARY_UOM_CODE,
NVL (CONVERSION_RATE, 1) CONVERSION_RATE,
SUBINVENTORY_CODE,
LOT_NUMBER,
--MATERIAL_DETAIL_ID,
MAX (GMD_ACTUAL_QTY) GMD_ACTUAL_QTY,
MAX (PLAN_QTY) PLAN_QTY,
MAX (MMT_ACTUAL_QTY) MMT_ACTUAL_QTY,
MAX (MTLN_ACTUAL_QTY) MTLN_ACTUAL_QTY,
SUM (PRIMARY_RESERVATION_QUANTITY)
PRIMARY_RESERVATION_QUANTITY
FROM ( SELECT SQ_GMD_MMT.BATCH_ID,
SQ_GMD_MMT.BATCH_NUM,
SQ_GMD_MMT.MATERIAL_DETAIL_ID,
SQ_GMD_MMT.INVENTORY_ITEM_ID,
SQ_GMD_MMT.INGREDIENTS,
SQ_GMD_MMT.CC_CODE,
SQ_GMD_MMT.RECIPE_ID,
SQ_GMD_MMT.RECIPE_NUM,
SQ_GMD_MMT.RECIPE_VERSION,
SQ_GMD_MMT.FORMULA_ID,
SQ_GMD_MMT.PLAN_START_DATE,
SQ_GMD_MMT.PLAN_END_DATE,
SQ_GMD_MMT.ACTUAL_START_DATE,
SQ_GMD_MMT.ACTUAL_END_DATE,
SQ_GMD_MMT.BATCH_CREATION_DATE,
SQ_GMD_MMT.DUE_DATE,
SQ_GMD_MMT.FORMULA_NUM,
SQ_GMD_MMT.FORMULA_VERSION,
SQ_GMD_MMT.BATCH_STATUS,
SQ_GMD_MMT.ORGANIZATION_ID,
SQ_GMD_MMT.ORGANIZATION_CODE,
SQ_GMD_MMT.COST_CENTER,
SQ_GMD_MMT.WORK_CENTER,
SQ_GMD_MMT.WC_CODE,
SQ_GMD_MMT.ROUTING_ID,
SQ_GMD_MMT.ROUTING_NUM,
SQ_GMD_MMT.ROUTING_VERSION,
SQ_GMD_MMT.DETAIL_UOM,
SQ_GMD_MMT.PRIMARY_UOM_CODE,
SQ_GMD_MMT.SUBINVENTORY_CODE,
SQ_GMD_MMT.CONVERSION_RATE,
SQ_GMD_MMT.TRANSACTION_ID,
SQ_MTLN.LOT_NUMBER,
MAX (SQ_GMD_MMT.GMD_ACTUAL_QTY)
GMD_ACTUAL_QTY,
MAX (SQ_GMD_MMT.PLAN_QTY) PLAN_QTY,
MAX (SQ_GMD_MMT.MMT_ACTUAL_QTY)
MMT_ACTUAL_QTY,
SUM (NVL (SQ_MTLN.MTLN_ACTUAL_QTY, 0))
MTLN_ACTUAL_QTY,
SUM (
NVL (
SQ_MTR.PRIMARY_RESERVATION_QUANTITY,
0))
PRIMARY_RESERVATION_QUANTITY
FROM ( SELECT SQ_GMD.BATCH_ID,
SQ_GMD.BATCH_NUM,
SQ_GMD.MATERIAL_DETAIL_ID,
SQ_GMD.INVENTORY_ITEM_ID,
SQ_GMD.INGREDIENTS,
SQ_GMD.CC_CODE,
SQ_GMD.RECIPE_ID,
SQ_GMD.RECIPE_NUM,
SQ_GMD.RECIPE_VERSION,
SQ_GMD.FORMULA_ID,
SQ_GMD.PLAN_START_DATE,
SQ_GMD.PLAN_END_DATE,
SQ_GMD.ACTUAL_START_DATE,
SQ_GMD.ACTUAL_END_DATE,
SQ_GMD.BATCH_CREATION_DATE,
SQ_GMD.DUE_DATE,
SQ_GMD.FORMULA_NUM,
SQ_GMD.FORMULA_VERSION,
SQ_GMD.BATCH_STATUS,
SQ_GMD.ORGANIZATION_ID,
SQ_GMD.ORGANIZATION_CODE,
SQ_GMD.COST_CENTER,
SQ_GMD.DETAIL_UOM,
SQ_GMD.PRIMARY_UOM_CODE,
SQ_GMD.CONVERSION_RATE,
MMT.SUBINVENTORY_CODE,
MMT.TRANSACTION_ID,
MAX (ACTUAL_QTY) GMD_ACTUAL_QTY,
MAX (PLAN_QTY) PLAN_QTY,
SUM (NVL (MMT.PRIMARY_QUANTITY, 0))
MMT_ACTUAL_QTY,
SQ_WORK_CENTER.WORK_CENTER
WORK_CENTER,
SQ_WORK_CENTER.WC_CODE,
SQ_WORK_CENTER.ROUTING_ID,
SQ_WORK_CENTER.ROUTING_NUM,
SQ_WORK_CENTER.ROUTING_VERSION
FROM (SELECT GMD.BATCH_ID BATCH_ID,
GBH.BATCH_NO BATCH_NUM,
GMD.MATERIAL_DETAIL_ID
MATERIAL_DETAIL_ID,
GMD.INVENTORY_ITEM_ID
INVENTORY_ITEM_ID,
MSIB.SEGMENT1 INGREDIENTS,
MSIB.PRIMARY_UOM_CODE
PRIMARY_UOM_CODE,
GMD.ORGANIZATION_ID
ORGANIZATION_ID,
MP.ORGANIZATION_CODE
ORGANIZATION_CODE,
FVC.DESCRIPTION COST_CENTER,
GRB.RECIPE_ID RECIPE_ID,
GRB.RECIPE_NO RECIPE_NUM,
GRB.RECIPE_VERSION
RECIPE_VERSION,
FFM.FORMULA_ID FORMULA_ID,
FFM.FORMULA_NO FORMULA_NUM,
FFM.FORMULA_VERS
FORMULA_VERSION,
GRB.ATTRIBUTE2 CC_CODE,
GMD.DTL_UM DETAIL_UOM,
CASE
WHEN GBH.BATCH_STATUS =
1
THEN
'Pending'
WHEN GBH.BATCH_STATUS =
2
THEN
'WIP'
END
BATCH_STATUS,
(inv_convert.inv_um_convert(
GMD.INVENTORY_ITEM_ID,
NULL,
GMD.ORGANIZATION_ID,
8,
GMD.PLAN_QTY,
GMD.DTL_UM,
msib.primary_uom_code,
NULL,
NULL)/nvl((case when GMD.PLAN_QTY=0 then 1 else GMD.PLAN_QTY end),1)) CONVERSION_RATE,
NVL (GMD.ACTUAL_QTY, 0)
ACTUAL_QTY,
NVL (GMD.PLAN_QTY, 0)
PLAN_QTY,
GBH.PLAN_START_DATE
PLAN_START_DATE,
GBH.PLAN_CMPLT_DATE
PLAN_END_DATE,
GBH.ACTUAL_START_DATE
ACTUAL_START_DATE,
GBH.ACTUAL_CMPLT_DATE
ACTUAL_END_DATE,
GBH.CREATION_DATE
BATCH_CREATION_DATE,
GBH.DUE_DATE
DUE_DATE
FROM GME.GME_MATERIAL_DETAILS
GMD
INNER JOIN
GME.GME_BATCH_HEADER GBH
ON (GBH.BATCH_ID =
GMD.BATCH_ID)
INNER JOIN
GMD.GMD_RECIPE_VALIDITY_RULES
GRVR
ON (GBH.RECIPE_VALIDITY_RULE_ID =
GRVR.RECIPE_VALIDITY_RULE_ID)
INNER JOIN
GMD.GMD_RECIPES_B GRB
ON ( GRB.ROUTING_ID =
GBH.ROUTING_ID
AND GRB.RECIPE_ID =
GRVR.RECIPE_ID)
INNER JOIN
INV.MTL_SYSTEM_ITEMS_B MSIB
ON ( GMD.INVENTORY_ITEM_ID =
MSIB.INVENTORY_ITEM_ID
AND GMD.ORGANIZATION_ID =
MSIB.ORGANIZATION_ID)
INNER JOIN
MTL_PARAMETERS MP
ON (GMD.ORGANIZATION_ID =
MP.ORGANIZATION_ID)
INNER JOIN
FM_FORM_MST_B FFM
ON (FFM.FORMULA_ID =
GRB.FORMULA_ID)
INNER JOIN
FND_LOOKUP_VALUES FVC
ON ( FVC.LOOKUP_TYPE =
'XXGMD_COST_CENTERS'
AND FVC.LANGUAGE =
'US'
AND FVC.LOOKUP_CODE =
MP.ORGANIZATION_CODE
|| '-'
|| GRB.ATTRIBUTE2)
WHERE 1=1
AND GBH.BATCH_STATUS IN
(1, 2)
) SQ_GMD
INNER JOIN
( SELECT GBH.BATCH_ID,
LISTAGG (
FVW.DESCRIPTION,
',')
WITHIN GROUP (ORDER BY
GBH.BATCH_ID)
WORK_CENTER,
LISTAGG (
GOB.ATTRIBUTE1,
',')
WITHIN GROUP (ORDER BY
GBH.BATCH_ID)
WC_CODE,
FRD.ROUTING_ID,
GRT.ROUTING_NO ROUTING_NUM,
GRT.ROUTING_VERS
ROUTING_VERSION
FROM GMD_OPERATIONS_B GOB,
APPS.FM_ROUT_DTL FRD,
GMD.GMD_RECIPES_B GRB,
GME_BATCH_HEADER GBH,
GMD_RECIPE_VALIDITY_RULES
GRVR,
FND_LOOKUP_VALUES FVW,
MTL_PARAMETERS MP,
GMD_ROUTINGS_B GRT
WHERE GRT.ROUTING_ID =
FRD.ROUTING_ID
AND GBH.ORGANIZATION_ID =
MP.ORGANIZATION_ID
AND GBH.RECIPE_VALIDITY_RULE_ID =
GRVR.RECIPE_VALIDITY_RULE_ID
AND GRVR.RECIPE_ID =
GRB.RECIPE_ID
AND FRD.ROUTING_ID =
GRB.ROUTING_ID
AND GOB.OPRN_ID =
FRD.OPRN_ID
AND BATCH_STATUS IN
(01, 02)
AND FVW.LOOKUP_TYPE =
'XXGMD_WORK_CENTERS'
AND FVW.LANGUAGE = 'US'
AND FVW.LOOKUP_CODE =
ORGANIZATION_CODE
|| '-'
|| GOB.ATTRIBUTE1
GROUP BY GBH.BATCH_ID,
FRD.ROUTING_ID,
GRT.ROUTING_NO,
GRT.ROUTING_VERS)
SQ_WORK_CENTER
ON (SQ_GMD.BATCH_ID =
SQ_WORK_CENTER.BATCH_ID)
LEFT OUTER JOIN
INV.MTL_MATERIAL_TRANSACTIONS MMT
ON ( SQ_GMD.BATCH_ID =
MMT.TRANSACTION_SOURCE_ID
AND SQ_GMD.MATERIAL_DETAIL_ID =
MMT.TRX_SOURCE_LINE_ID
AND SQ_GMD.ORGANIZATION_ID =
MMT.ORGANIZATION_ID
AND SQ_GMD.INVENTORY_ITEM_ID =
MMT.INVENTORY_ITEM_ID)
GROUP BY SQ_GMD.BATCH_ID,
SQ_GMD.BATCH_NUM,
SQ_GMD.MATERIAL_DETAIL_ID,
SQ_GMD.INVENTORY_ITEM_ID,
SQ_GMD.PRIMARY_UOM_CODE,
SQ_GMD.ORGANIZATION_ID,
SQ_GMD.COST_CENTER,
SQ_WORK_CENTER.WORK_CENTER,
SQ_WORK_CENTER.WC_CODE,
SQ_WORK_CENTER.ROUTING_ID,
SQ_WORK_CENTER.ROUTING_NUM,
SQ_WORK_CENTER.ROUTING_VERSION,
SQ_GMD.DETAIL_UOM,
MMT.SUBINVENTORY_CODE,
MMT.TRANSACTION_ID,
SQ_GMD.INGREDIENTS,
SQ_GMD.CC_CODE,
SQ_GMD.RECIPE_ID,
SQ_GMD.RECIPE_NUM,
SQ_GMD.RECIPE_VERSION,
SQ_GMD.FORMULA_ID,
SQ_GMD.PLAN_START_DATE,
SQ_GMD.PLAN_END_DATE,
SQ_GMD.ACTUAL_START_DATE,
SQ_GMD.ACTUAL_END_DATE,
SQ_GMD.BATCH_CREATION_DATE,
SQ_GMD.DUE_DATE,
SQ_GMD.FORMULA_NUM,
SQ_GMD.FORMULA_VERSION,
SQ_GMD.BATCH_STATUS,
SQ_GMD.CONVERSION_RATE,
SQ_GMD.ORGANIZATION_CODE)
SQ_GMD_MMT
LEFT OUTER JOIN
( SELECT TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
LOT_NUMBER,
TRANSACTION_SOURCE_ID,
SUM (NVL (PRIMARY_QUANTITY, 0))
MTLN_ACTUAL_QTY
FROM INV.MTL_TRANSACTION_LOT_NUMBERS
GROUP BY TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
LOT_NUMBER,
TRANSACTION_SOURCE_ID) SQ_MTLN
ON ( SQ_GMD_MMT.TRANSACTION_ID =
SQ_MTLN.TRANSACTION_ID
AND SQ_GMD_MMT.ORGANIZATION_ID =
SQ_MTLN.ORGANIZATION_ID
AND SQ_GMD_MMT.INVENTORY_ITEM_ID =
SQ_MTLN.INVENTORY_ITEM_ID
AND SQ_GMD_MMT.BATCH_ID =
SQ_MTLN.TRANSACTION_SOURCE_ID)
LEFT OUTER JOIN
( SELECT ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEMAND_SOURCE_HEADER_ID,
DEMAND_SOURCE_LINE_ID,
SUBINVENTORY_CODE,
LOT_NUMBER,
SUM (PRIMARY_RESERVATION_QUANTITY)
PRIMARY_RESERVATION_QUANTITY
FROM MTL_RESERVATIONS MTR
GROUP BY ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEMAND_SOURCE_LINE_ID,
DEMAND_SOURCE_HEADER_ID,
SUBINVENTORY_CODE,
LOT_NUMBER ) SQ_MTR
ON ( 1=1
AND SQ_MTR.DEMAND_SOURCE_HEADER_ID = SQ_GMD_MMT.BATCH_ID
AND SQ_MTR.DEMAND_SOURCE_LINE_ID = SQ_GMD_MMT.MATERIAL_DETAIL_ID
AND SQ_MTLN.LOT_NUMBER = SQ_MTR.LOT_NUMBER
AND SQ_MTR.ORGANIZATION_ID =SQ_GMD_MMT.ORGANIZATION_ID
AND SQ_MTR.INVENTORY_ITEM_ID =SQ_GMD_MMT.INVENTORY_ITEM_ID
AND SQ_MTR.SUBINVENTORY_CODE=SQ_GMD_MMT.SUBINVENTORY_CODE
)
GROUP BY SQ_GMD_MMT.BATCH_ID,
SQ_GMD_MMT.BATCH_NUM,
SQ_GMD_MMT.MATERIAL_DETAIL_ID,
SQ_GMD_MMT.INVENTORY_ITEM_ID,
SQ_GMD_MMT.PRIMARY_UOM_CODE,
SQ_GMD_MMT.INGREDIENTS,
SQ_GMD_MMT.CC_CODE,
SQ_GMD_MMT.RECIPE_ID,
SQ_GMD_MMT.RECIPE_NUM,
SQ_GMD_MMT.RECIPE_VERSION,
SQ_GMD_MMT.FORMULA_ID,
SQ_GMD_MMT.PLAN_START_DATE,
SQ_GMD_MMT.PLAN_END_DATE,
SQ_GMD_MMT.ACTUAL_START_DATE,
SQ_GMD_MMT.ACTUAL_END_DATE,
SQ_GMD_MMT.BATCH_CREATION_DATE,
SQ_GMD_MMT.DUE_DATE,
SQ_GMD_MMT.FORMULA_NUM,
SQ_GMD_MMT.FORMULA_VERSION,
SQ_GMD_MMT.BATCH_STATUS,
SQ_GMD_MMT.ORGANIZATION_ID,
SQ_GMD_MMT.ORGANIZATION_CODE,
SQ_GMD_MMT.COST_CENTER,
SQ_GMD_MMT.WORK_CENTER,
SQ_GMD_MMT.WC_CODE,
SQ_GMD_MMT.ROUTING_ID,
SQ_GMD_MMT.ROUTING_NUM,
SQ_GMD_MMT.ROUTING_VERSION,
SQ_GMD_MMT.DETAIL_UOM,
SQ_GMD_MMT.SUBINVENTORY_CODE,
SQ_GMD_MMT.TRANSACTION_ID,
SQ_GMD_MMT.CONVERSION_RATE,
SQ_MTLN.LOT_NUMBER) SQ_GMD_MMT_MTLN_MTR
GROUP BY BATCH_ID,
BATCH_NUM,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
COST_CENTER,
WORK_CENTER,
WC_CODE,
ROUTING_ID,
ROUTING_NUM,
ROUTING_VERSION,
DETAIL_UOM,
SUBINVENTORY_CODE,
LOT_NUMBER,
INGREDIENTS,
CC_CODE,
RECIPE_ID,
RECIPE_NUM,
RECIPE_VERSION,
FORMULA_ID,
PLAN_START_DATE,
PLAN_END_DATE,
ACTUAL_START_DATE,
ACTUAL_END_DATE,
BATCH_CREATION_DATE,
DUE_DATE,
FORMULA_NUM,
FORMULA_VERSION,
BATCH_STATUS,
ORGANIZATION_CODE,
CONVERSION_RATE,
PRIMARY_UOM_CODE) SQ_GMD_MMT_MTLN_MTR_MOQD
LEFT OUTER JOIN INV.MTL_ONHAND_QUANTITIES_DETAIL MOQD
ON ( MOQD.INVENTORY_ITEM_ID =
SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID
AND MOQD.ORGANIZATION_ID =
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID
AND CASE WHEN SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE IS NULL THEN '0' ELSE MOQD.SUBINVENTORY_CODE
END = NVL(SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE,'0')
AND CASE WHEN SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER IS NULL THEN '0' ELSE MOQD.LOT_NUMBER
END = NVL(SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER,'0')
)
GROUP BY SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.INVENTORY_ITEM_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.INGREDIENTS,
SQ_GMD_MMT_MTLN_MTR_MOQD.CC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.RECIPE_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.PLAN_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_START_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ACTUAL_END_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_CREATION_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.DUE_DATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.FORMULA_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ORGANIZATION_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.COST_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.DETAIL_UOM,
SQ_GMD_MMT_MTLN_MTR_MOQD.SUBINVENTORY_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.LOT_NUMBER,
SQ_GMD_MMT_MTLN_MTR_MOQD.PRIMARY_UOM_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.CONVERSION_RATE,
SQ_GMD_MMT_MTLN_MTR_MOQD.WORK_CENTER,
SQ_GMD_MMT_MTLN_MTR_MOQD.WC_CODE,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_ID,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_NUM,
SQ_GMD_MMT_MTLN_MTR_MOQD.ROUTING_VERSION,
SQ_GMD_MMT_MTLN_MTR_MOQD.BATCH_STATUS);
No comments:
Post a Comment