VIEW: APPS.GME_OPSM_BATCH_DETAILS_V

Source

View Text - Preformatted

SELECT MP.ORGANIZATION_ID ,MP.ORGANIZATION_CODE ,GBH.BATCH_ID ,GMD.MATERIAL_DETAIL_ID ,GBH.BATCH_NO ,'PROCESS' ,DECODE(GBH.BATCH_STATUS,1,'PENDING',2,'WIP',3,'COMPLETED',4,'CLOSED',-1,'CANCELLED') ,MSIK.CONCATENATED_SEGMENTS ,MSIK.DESCRIPTION ,MSIK.INVENTORY_ITEM_ID ,MSIK.PRIMARY_UOM_CODE ,GMD.ORIGINAL_PRIMARY_QTY ,GMD.PLAN_QTY ,GMD.WIP_PLAN_QTY ,DECODE(GMD.DTL_UM, MSIK.PRIMARY_UOM_CODE, GMD.ACTUAL_QTY, INV_CONVERT.INV_UM_CONVERT(MSIK.INVENTORY_ITEM_ID, 5, GMD.ACTUAL_QTY, GMD.DTL_UM, MSIK.PRIMARY_UOM_CODE, NULL, NULL)) ,DECODE(TRUNC((GBH.BATCH_STATUS+1)/3), 0,DECODE(GMD.DTL_UM, MSIK.PRIMARY_UOM_CODE, GMD.PLAN_QTY, INV_CONVERT.INV_UM_CONVERT(MSIK.INVENTORY_ITEM_ID, 5, GMD.PLAN_QTY, GMD.DTL_UM, MSIK.PRIMARY_UOM_CODE, NULL, NULL)), DECODE(GMD.DTL_UM, MSIK.PRIMARY_UOM_CODE, GMD.WIP_PLAN_QTY, INV_CONVERT.INV_UM_CONVERT(MSIK.INVENTORY_ITEM_ID, 5, GMD.WIP_PLAN_QTY, GMD.DTL_UM, MSIK.PRIMARY_UOM_CODE, NULL, NULL))) ,GBH.CREATION_DATE ,GBH.PLAN_START_DATE ,GBH.PLAN_CMPLT_DATE ,GBH.ACTUAL_START_DATE ,GBH.ACTUAL_CMPLT_DATE ,GBH.BATCH_CLOSE_DATE ,GBH.FIRMED_IND ,GBH.TERMINATED_IND ,GBH.LAST_UPDATE_DATE ,GMD.LAST_UPDATE_DATE FROM GME_BATCH_HEADER GBH , GME_MATERIAL_DETAILS GMD , MTL_SYSTEM_ITEMS_B_KFV MSIK, MTL_PARAMETERS MP , MTL_CROSS_REFERENCES_VL MCR WHERE GBH.BATCH_TYPE = 0 AND GBH.BATCH_ID = GMD.BATCH_ID AND GBH.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND GMD.LINE_TYPE = 1 AND MSIK.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND MSIK.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID AND MSIK.LOT_CONTROL_CODE = 2 AND MP.ORGANIZATION_ID = GMD.ORGANIZATION_ID AND MP.OPSM_ENABLED_FLAG = 'Y' AND MP.MASTER_ORGANIZATION_ID = MCR.ORGANIZATION_ID AND MCR.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID AND MCR.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED' AND CROSS_REFERENCE = 'YES' AND NOT EXISTS (SELECT 'Y' FROM MTL_CROSS_REFERENCES_VL WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED' AND ORGANIZATION_ID = GMD.ORGANIZATION_ID AND INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID AND UPPER(CROSS_REFERENCE) ='NO' )

View Text - HTML Formatted

SELECT MP.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, GBH.BATCH_ID
, GMD.MATERIAL_DETAIL_ID
, GBH.BATCH_NO
, 'PROCESS'
, DECODE(GBH.BATCH_STATUS
, 1
, 'PENDING'
, 2
, 'WIP'
, 3
, 'COMPLETED'
, 4
, 'CLOSED'
, -1
, 'CANCELLED')
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, MSIK.INVENTORY_ITEM_ID
, MSIK.PRIMARY_UOM_CODE
, GMD.ORIGINAL_PRIMARY_QTY
, GMD.PLAN_QTY
, GMD.WIP_PLAN_QTY
, DECODE(GMD.DTL_UM
, MSIK.PRIMARY_UOM_CODE
, GMD.ACTUAL_QTY
, INV_CONVERT.INV_UM_CONVERT(MSIK.INVENTORY_ITEM_ID
, 5
, GMD.ACTUAL_QTY
, GMD.DTL_UM
, MSIK.PRIMARY_UOM_CODE
, NULL
, NULL))
, DECODE(TRUNC((GBH.BATCH_STATUS+1)/3)
, 0
, DECODE(GMD.DTL_UM
, MSIK.PRIMARY_UOM_CODE
, GMD.PLAN_QTY
, INV_CONVERT.INV_UM_CONVERT(MSIK.INVENTORY_ITEM_ID
, 5
, GMD.PLAN_QTY
, GMD.DTL_UM
, MSIK.PRIMARY_UOM_CODE
, NULL
, NULL))
, DECODE(GMD.DTL_UM
, MSIK.PRIMARY_UOM_CODE
, GMD.WIP_PLAN_QTY
, INV_CONVERT.INV_UM_CONVERT(MSIK.INVENTORY_ITEM_ID
, 5
, GMD.WIP_PLAN_QTY
, GMD.DTL_UM
, MSIK.PRIMARY_UOM_CODE
, NULL
, NULL)))
, GBH.CREATION_DATE
, GBH.PLAN_START_DATE
, GBH.PLAN_CMPLT_DATE
, GBH.ACTUAL_START_DATE
, GBH.ACTUAL_CMPLT_DATE
, GBH.BATCH_CLOSE_DATE
, GBH.FIRMED_IND
, GBH.TERMINATED_IND
, GBH.LAST_UPDATE_DATE
, GMD.LAST_UPDATE_DATE
FROM GME_BATCH_HEADER GBH
, GME_MATERIAL_DETAILS GMD
, MTL_SYSTEM_ITEMS_B_KFV MSIK
, MTL_PARAMETERS MP
, MTL_CROSS_REFERENCES_VL MCR
WHERE GBH.BATCH_TYPE = 0
AND GBH.BATCH_ID = GMD.BATCH_ID
AND GBH.ORGANIZATION_ID = GMD.ORGANIZATION_ID
AND GMD.LINE_TYPE = 1
AND MSIK.ORGANIZATION_ID = GMD.ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID
AND MSIK.LOT_CONTROL_CODE = 2
AND MP.ORGANIZATION_ID = GMD.ORGANIZATION_ID
AND MP.OPSM_ENABLED_FLAG = 'Y'
AND MP.MASTER_ORGANIZATION_ID = MCR.ORGANIZATION_ID
AND MCR.INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID
AND MCR.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND CROSS_REFERENCE = 'YES'
AND NOT EXISTS (SELECT 'Y'
FROM MTL_CROSS_REFERENCES_VL
WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND ORGANIZATION_ID = GMD.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = GMD.INVENTORY_ITEM_ID
AND UPPER(CROSS_REFERENCE) ='NO' )