VIEW: APPS.GME_OPSM_BATCH_EXTRACT_V

Source

View Text - Preformatted

SELECT GBD.ORGANIZATION_ID ,GBD.ORGANIZATION_CODE ,GBD.ORGANIZATION_CODE ,GBD.BATCH_NO ,GBD.BATCH_TYPE ,GBD.BATCH_STATUS ,GBD.ITEM_NUMBER ,GBD.ITEM_DESCRIPTION ,GBD.INVENTORY_ITEM_ID ,GBD.PRIMARY_UOM_CODE ,GBD.WORK_ORDER_QTY ,GBD.ORGANIZATION_CODE ,GPPL.PARENT_LOT_NUMBER ,GPPL.LOT_NUMBER ,GPPL.ORIGINATION_DATE ,GPPL.RETEST_DATE ,GPPL.EXPIRATION_DATE ,GPPL.BEST_BY_DATE ,GBD.ACTUAL_QTY COMPLETED_QTY ,NVL(GPPL.LOT_QTY,0) ,0 LOT_COMPLETED_QTY ,GBD.BATCH_CREATION_DATE ,GBD.PLAN_START_DATE ,GBD.PLAN_CMPLT_DATE ,GBD.ACTUAL_START_DATE ,GBD.ACTUAL_CMPLT_DATE ,GBD.BATCH_CLOSE_DATE ,GBD.FIRMED_IND ,GBD.TERMINATED_IND ,GREATEST(GBD.BATCH_UPDATE_DATE,GBD.MATERIAL_UPDATE_DATE,nvl(GPPL.LAST_UPDATE_DATE,GREATEST(GBD.BATCH_UPDATE_DATE,GBD.MATERIAL_UPDATE_DATE))) FROM GME_OPSM_BATCH_DETAILS_V GBD , GME_OPSM_PENDING_PROD_LOTS_V GPPL WHERE GBD.BATCH_ID = GPPL.BATCH_ID (+) AND GBD.MATERIAL_DETAIL_ID = GPPL.MATERIAL_DETAIL_ID (+) AND DECODE(NVL(GBD.TERMINATED_IND,0), 0, GBD.BATCH_STATUS,'Y') NOT IN ('COMPLETED','CLOSED') AND DECODE(GBD.BATCH_STATUS,'WIP',DECODE(GBD.ACTUAL_QTY,0,1,DECODE(NVL(GPPL.LOT_QTY,0),0,0,1)),1) = 1 AND DECODE(GBD.TERMINATED_IND, 1, DECODE(GBD.ACTUAL_QTY, 0, 1, DECODE(NVL(GPPL.LOT_QTY,0), 0, 0, 1)),1) <> 0 UNION ALL SELECT GBD.ORGANIZATION_ID ,GBD.ORGANIZATION_CODE ,GBD.ORGANIZATION_CODE ,GBD.BATCH_NO ,GBD.BATCH_TYPE ,GBD.BATCH_STATUS ,GBD.ITEM_NUMBER ,GBD.ITEM_DESCRIPTION ,GBD.INVENTORY_ITEM_ID ,GBD.PRIMARY_UOM_CODE ,GBD.WORK_ORDER_QTY ,GBD.ORGANIZATION_CODE ,MLN.PARENT_LOT_NUMBER ,MLN.LOT_NUMBER ,MLN.ORIGINATION_DATE ,MLN.RETEST_DATE ,MLN.EXPIRATION_DATE ,MLN.BEST_BY_DATE ,GBD.ACTUAL_QTY ,SUM(MTLN.PRIMARY_QUANTITY) ,SUM(MTLN.PRIMARY_QUANTITY) ,GBD.BATCH_CREATION_DATE ,GBD.PLAN_START_DATE ,GBD.PLAN_CMPLT_DATE ,GBD.ACTUAL_START_DATE ,GBD.ACTUAL_CMPLT_DATE ,GBD.BATCH_CLOSE_DATE ,GBD.FIRMED_IND ,GBD.TERMINATED_IND ,GREATEST(GBD.BATCH_UPDATE_DATE,GBD.MATERIAL_UPDATE_DATE) FROM GME_OPSM_BATCH_DETAILS_V GBD , MTL_TRANSACTION_LOT_NUMBERS MTLN , MTL_LOT_NUMBERS MLN WHERE MTLN.TRANSACTION_SOURCE_ID = GBD.BATCH_ID AND MTLN.ORGANIZATION_ID = GBD.ORGANIZATION_ID AND MTLN.INVENTORY_ITEM_ID = GBD.INVENTORY_ITEM_ID AND MLN.LOT_NUMBER = MTLN.LOT_NUMBER AND MLN.ORGANIZATION_ID = MTLN.ORGANIZATION_ID AND MLN.INVENTORY_ITEM_ID = MTLN.INVENTORY_ITEM_ID AND 1 = DECODE((SELECT SUM(TRANSACTION_QUANTITY) FROM MTL_TRANSACTION_LOT_NUMBERS WHERE TRANSACTION_SOURCE_ID = GBD.BATCH_ID AND INVENTORY_ITEM_ID = GBD.INVENTORY_ITEM_ID AND ORGANIZATION_ID = GBD.ORGANIZATION_ID AND LOT_NUMBER = MLN.LOT_NUMBER ), 0, 0,1) GROUP BY GBD.ORGANIZATION_ID , GBD.ORGANIZATION_CODE , GBD.BATCH_NO , GBD.BATCH_TYPE , GBD.BATCH_STATUS , GBD.ITEM_NUMBER , GBD.ITEM_DESCRIPTION , GBD.INVENTORY_ITEM_ID , GBD.PRIMARY_UOM_CODE , GBD.WORK_ORDER_QTY , MLN.PARENT_LOT_NUMBER , MLN.LOT_NUMBER , MLN.ORIGINATION_DATE , MLN.RETEST_DATE , MLN.EXPIRATION_DATE , MLN.BEST_BY_DATE , GBD.ACTUAL_QTY , GBD.BATCH_CREATION_DATE , GBD.PLAN_START_DATE , GBD.PLAN_CMPLT_DATE , GBD.ACTUAL_START_DATE , GBD.ACTUAL_CMPLT_DATE , GBD.BATCH_CLOSE_DATE , GBD.FIRMED_IND , GBD.TERMINATED_IND , GBD.BATCH_UPDATE_DATE , GBD.MATERIAL_UPDATE_DATE , GBD.MATERIAL_DETAIL_ID

View Text - HTML Formatted

SELECT GBD.ORGANIZATION_ID
, GBD.ORGANIZATION_CODE
, GBD.ORGANIZATION_CODE
, GBD.BATCH_NO
, GBD.BATCH_TYPE
, GBD.BATCH_STATUS
, GBD.ITEM_NUMBER
, GBD.ITEM_DESCRIPTION
, GBD.INVENTORY_ITEM_ID
, GBD.PRIMARY_UOM_CODE
, GBD.WORK_ORDER_QTY
, GBD.ORGANIZATION_CODE
, GPPL.PARENT_LOT_NUMBER
, GPPL.LOT_NUMBER
, GPPL.ORIGINATION_DATE
, GPPL.RETEST_DATE
, GPPL.EXPIRATION_DATE
, GPPL.BEST_BY_DATE
, GBD.ACTUAL_QTY COMPLETED_QTY
, NVL(GPPL.LOT_QTY
, 0)
, 0 LOT_COMPLETED_QTY
, GBD.BATCH_CREATION_DATE
, GBD.PLAN_START_DATE
, GBD.PLAN_CMPLT_DATE
, GBD.ACTUAL_START_DATE
, GBD.ACTUAL_CMPLT_DATE
, GBD.BATCH_CLOSE_DATE
, GBD.FIRMED_IND
, GBD.TERMINATED_IND
, GREATEST(GBD.BATCH_UPDATE_DATE
, GBD.MATERIAL_UPDATE_DATE
, NVL(GPPL.LAST_UPDATE_DATE
, GREATEST(GBD.BATCH_UPDATE_DATE
, GBD.MATERIAL_UPDATE_DATE)))
FROM GME_OPSM_BATCH_DETAILS_V GBD
, GME_OPSM_PENDING_PROD_LOTS_V GPPL
WHERE GBD.BATCH_ID = GPPL.BATCH_ID (+)
AND GBD.MATERIAL_DETAIL_ID = GPPL.MATERIAL_DETAIL_ID (+)
AND DECODE(NVL(GBD.TERMINATED_IND
, 0)
, 0
, GBD.BATCH_STATUS
, 'Y') NOT IN ('COMPLETED'
, 'CLOSED')
AND DECODE(GBD.BATCH_STATUS
, 'WIP'
, DECODE(GBD.ACTUAL_QTY
, 0
, 1
, DECODE(NVL(GPPL.LOT_QTY
, 0)
, 0
, 0
, 1))
, 1) = 1
AND DECODE(GBD.TERMINATED_IND
, 1
, DECODE(GBD.ACTUAL_QTY
, 0
, 1
, DECODE(NVL(GPPL.LOT_QTY
, 0)
, 0
, 0
, 1))
, 1) <> 0 UNION ALL SELECT GBD.ORGANIZATION_ID
, GBD.ORGANIZATION_CODE
, GBD.ORGANIZATION_CODE
, GBD.BATCH_NO
, GBD.BATCH_TYPE
, GBD.BATCH_STATUS
, GBD.ITEM_NUMBER
, GBD.ITEM_DESCRIPTION
, GBD.INVENTORY_ITEM_ID
, GBD.PRIMARY_UOM_CODE
, GBD.WORK_ORDER_QTY
, GBD.ORGANIZATION_CODE
, MLN.PARENT_LOT_NUMBER
, MLN.LOT_NUMBER
, MLN.ORIGINATION_DATE
, MLN.RETEST_DATE
, MLN.EXPIRATION_DATE
, MLN.BEST_BY_DATE
, GBD.ACTUAL_QTY
, SUM(MTLN.PRIMARY_QUANTITY)
, SUM(MTLN.PRIMARY_QUANTITY)
, GBD.BATCH_CREATION_DATE
, GBD.PLAN_START_DATE
, GBD.PLAN_CMPLT_DATE
, GBD.ACTUAL_START_DATE
, GBD.ACTUAL_CMPLT_DATE
, GBD.BATCH_CLOSE_DATE
, GBD.FIRMED_IND
, GBD.TERMINATED_IND
, GREATEST(GBD.BATCH_UPDATE_DATE
, GBD.MATERIAL_UPDATE_DATE)
FROM GME_OPSM_BATCH_DETAILS_V GBD
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_LOT_NUMBERS MLN
WHERE MTLN.TRANSACTION_SOURCE_ID = GBD.BATCH_ID
AND MTLN.ORGANIZATION_ID = GBD.ORGANIZATION_ID
AND MTLN.INVENTORY_ITEM_ID = GBD.INVENTORY_ITEM_ID
AND MLN.LOT_NUMBER = MTLN.LOT_NUMBER
AND MLN.ORGANIZATION_ID = MTLN.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID = MTLN.INVENTORY_ITEM_ID
AND 1 = DECODE((SELECT SUM(TRANSACTION_QUANTITY)
FROM MTL_TRANSACTION_LOT_NUMBERS
WHERE TRANSACTION_SOURCE_ID = GBD.BATCH_ID
AND INVENTORY_ITEM_ID = GBD.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = GBD.ORGANIZATION_ID
AND LOT_NUMBER = MLN.LOT_NUMBER )
, 0
, 0
, 1) GROUP BY GBD.ORGANIZATION_ID
, GBD.ORGANIZATION_CODE
, GBD.BATCH_NO
, GBD.BATCH_TYPE
, GBD.BATCH_STATUS
, GBD.ITEM_NUMBER
, GBD.ITEM_DESCRIPTION
, GBD.INVENTORY_ITEM_ID
, GBD.PRIMARY_UOM_CODE
, GBD.WORK_ORDER_QTY
, MLN.PARENT_LOT_NUMBER
, MLN.LOT_NUMBER
, MLN.ORIGINATION_DATE
, MLN.RETEST_DATE
, MLN.EXPIRATION_DATE
, MLN.BEST_BY_DATE
, GBD.ACTUAL_QTY
, GBD.BATCH_CREATION_DATE
, GBD.PLAN_START_DATE
, GBD.PLAN_CMPLT_DATE
, GBD.ACTUAL_START_DATE
, GBD.ACTUAL_CMPLT_DATE
, GBD.BATCH_CLOSE_DATE
, GBD.FIRMED_IND
, GBD.TERMINATED_IND
, GBD.BATCH_UPDATE_DATE
, GBD.MATERIAL_UPDATE_DATE
, GBD.MATERIAL_DETAIL_ID