SELECT GMD.ORGANIZATION_ID
,GBH.BATCH_ID
,GBH.BATCH_NO
, mmt.transaction_date
,GBH.PLAN_START_DATE
,GBH.PLAN_CMPLT_DATE
,GBH.DUE_DATE
,GBH.ACTUAL_START_DATE
,GBH.ACTUAL_CMPLT_DATE
,GBH.BATCH_CLOSE_DATE
,GBH.FORMULA_ID
,FFM.FORMULA_NO
,GBH.ROUTING_ID
,GBH.RECIPE_VALIDITY_RULE_ID
,GBH.BATCH_STATUS
,GBH.BATCH_TYPE
,GMD.LINE_NO
,LINE_TYPE
,DECODE(GMD.LINE_TYPE,'1','PRODUCT','2','BY-PRODUCT','-1','INGREDIENT')LINE_TYPE_DESC
,GMD.INVENTORY_ITEM_ID
,MSIB.SEGMENT1 ITEM_CODE
,MSIB.DESCRIPTION
,NVL(GMD.ORIGINAL_QTY,0) ORIGINAL_QTY
,NVL(GMD.PLAN_QTY,0) PLAN_QTY
,NVL(GMD.ACTUAL_QTY,0) ACTUAL_QTY
,NVL(GMD.WIP_PLAN_QTY,0) WIP_PLAN_QTY
,NVL(MMT.TRANSACTION_QUANTITY,0) TRANSACTED_QTY
,NVL(MMT.SECONDARY_TRANSACTION_QUANTITY,0)SECONDARY_QTY
,MMT.SECONDARY_UOM_CODE
,DTL_UM
,RELEASE_TYPE CONSUMPTION_TYPE
,DECODE(RELEASE_TYPE,'0','AUTOMATIC','1','MANUAL','2','INCREMENTAL','3','AUTOMATIC BY STEP') CONSUMPTION_TYPE_DESC
,GMD.SUBINVENTORY
,GMD.LOCATOR_ID
,(CASE WHEN GMD.LOCATOR_ID IS NOT NULL THEN (MIL.SEGMENT1||'.'||MIL.SEGMENT2||'.'||MIL.SEGMENT3||'.'||MIL.SEGMENT4) END) LOCATOR
,GMD.SCALE_TYPE
,DECODE(GMD.SCALE_TYPE,'0','FIXED','1','PROPOTIONAL') SCALE_TYPE_DESC
,COST_ALLOC
,CONTRIBUTE_STEP_QTY_IND
,DECODE(GMD.CONTRIBUTE_STEP_QTY_IND,'Y','YES','N','NO') CONTRIBUTION
,SCALE_MULTIPLE
,SCALE_ROUNDING_VARIANCE
,ROUNDING_DIRECTION
,DECODE(ROUNDING_DIRECTION,'0','EITHER','1','UP','2','DOWN') ROUNDING_DIR
,SCRAP_FACTOR
,MMT.REASON_ID
,(SELECT REASON_NAME FROM MTL_TRANSACTION_REASONS WHERE REASON_ID = MMT.REASON_ID) REASON_NAME
FROM GME_BATCH_HEADER GBH,
GME_MATERIAL_DETAILS GMD,
MTL_SYSTEM_ITEMS_B MSIB,
FM_FORM_MST FFM,
MTL_ITEM_LOCATIONS MIL,
MTL_MATERIAL_TRANSACTIONS MMT
WHERE 1=1
AND GBH.BATCH_ID = GMD.BATCH_ID
AND GBH.ORGANIZATION_ID = GMD.ORGANIZATION_ID
AND GMD.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND GMD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND FFM.FORMULA_ID(+) = GBH.FORMULA_ID
AND FFM.OWNER_ORGANIZATION_ID = GMD.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID (+) = GMD.LOCATOR_ID
AND MIL.ORGANIZATION_ID (+) = GMD.ORGANIZATION_ID
AND MMT.INVENTORY_ITEM_ID (+)= GMD.INVENTORY_ITEM_ID
AND MMT.TRX_SOURCE_LINE_ID (+) = GMD.MATERIAL_DETAIL_ID
----AND MMT.TRANSACTION_SOURCE_ID (+) = GBH.BATCH_ID
--AND BATCH_STATUS NOT IN ('-1','1')
AND GMD.ORGANIZATION_ID=222
and GBH.BATCH_NO='5555'
ORDER BY BATCH_NO,LINE_TYPE DESC
