VIEW: APPS.PMIFV_BATCH_DETAILS_V

Source

View Text - Preformatted

SELECT BtchHdr.PLANT_CODE, ORGNMST.CO_CODE, BtchHdr.WIP_WHSE_CODE, BtchHdr.BATCH_ID, BtchHdr.BATCH_NO, BtchHdr.FORMULA_ID, FormMst.FORMULA_NO, FormMst.FORMULA_VERS, BtchHdr.ROUTING_ID, RoutHdr.ROUTING_NO, RoutHdr.ROUTING_VERS, BtchHdr.PLAN_START_DATE, BtchHdr.ACTUAL_START_DATE, BtchHdr.PLAN_CMPLT_DATE, BtchHdr.ACTUAL_CMPLT_DATE, BtchHdr.BATCH_STATUS, BtchDtl.ITEM_ID, ItemMst.ITEM_NO, ItemMst.ITEM_DESC1, ItemMst.PLANNING_CLASS, ItemMst.INV_CLASS, ItemMst.SALES_CLASS, ItemMst.ITEM_UM, BtchDtl.ITEM_UM, BtchDtl.LINE_TYPE, BtchDtl.PLAN_QTY, gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM, BtchDtl.PLAN_QTY,ItemMst.ITEM_UM)*pmi_common_pkg.PMICO_GET_COST( BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,BtchHdr.ACTUAL_CMPLT_DATE), BtchDtl.ACTUAL_QTY, gmicuom.i2uom_cv(BtchDtl.ITEM_ID,0,BtchDtl.ITEM_UM, BtchDtl.ACTUAL_QTY,ItemMst.ITEM_UM)*pmi_common_pkg.PMICO_GET_COST( BtchDtl.ITEM_ID,BtchHdr.WIP_WHSE_CODE,NULL,BtchHdr.ACTUAL_CMPLT_DATE), Calendr.PERIOD_YEAR, Calendr.PERIOD_NUM, Calendr.PERIOD_NAME, PlcyMst.BASE_CURRENCY_CODE, PlcyMst.GL_COST_MTHD FROM GME_BATCH_HEADER BtchHdr, GME_MATERIAL_DETAILS BtchDtl, SY_ORGN_MST OrgnMst, IC_ITEM_MST ItemMst, FM_FORM_MST_VL FormMst, FM_ROUT_HDR RoutHdr, PMI_GL_CALENDAR_V Calendr, GL_PLCY_MST PlcyMst WHERE BtchHdr.BATCH_ID = BtchDtl.BATCH_ID AND BtchHdr.PLANT_CODE = OrgnMst.ORGN_CODE AND OrgnMst.co_CODE = PlcyMst.co_code AND BtchDtl.ITEM_ID = ItemMst.ITEM_ID AND BtchHdr.FORMULA_ID = FormMst.FORMULA_ID AND BtchHdr.ROUTING_ID = RoutHdr.ROUTING_ID(+) AND BtchHdr.BATCH_STATUS in (3,4) AND BtchDtl.LINE_TYPE=1 AND OrgnMst.CO_CODE = calendr.CO_CODE AND trunc(BtchHdr.ACTUAL_CMPLT_DATE) between Calendr.start_date and Calendr.end_date AND (BtchHdr.PLANT_CODE IN (SELECT secorg.orgn_code FROM sy_orgn_mst secorg WHERE PMI_SECURITY_PKG.show_record(secorg.orgn_code) = 'TRUE')) with read only

View Text - HTML Formatted

SELECT BTCHHDR.PLANT_CODE
, ORGNMST.CO_CODE
, BTCHHDR.WIP_WHSE_CODE
, BTCHHDR.BATCH_ID
, BTCHHDR.BATCH_NO
, BTCHHDR.FORMULA_ID
, FORMMST.FORMULA_NO
, FORMMST.FORMULA_VERS
, BTCHHDR.ROUTING_ID
, ROUTHDR.ROUTING_NO
, ROUTHDR.ROUTING_VERS
, BTCHHDR.PLAN_START_DATE
, BTCHHDR.ACTUAL_START_DATE
, BTCHHDR.PLAN_CMPLT_DATE
, BTCHHDR.ACTUAL_CMPLT_DATE
, BTCHHDR.BATCH_STATUS
, BTCHDTL.ITEM_ID
, ITEMMST.ITEM_NO
, ITEMMST.ITEM_DESC1
, ITEMMST.PLANNING_CLASS
, ITEMMST.INV_CLASS
, ITEMMST.SALES_CLASS
, ITEMMST.ITEM_UM
, BTCHDTL.ITEM_UM
, BTCHDTL.LINE_TYPE
, BTCHDTL.PLAN_QTY
, GMICUOM.I2UOM_CV(BTCHDTL.ITEM_ID
, 0
, BTCHDTL.ITEM_UM
, BTCHDTL.PLAN_QTY
, ITEMMST.ITEM_UM)*PMI_COMMON_PKG.PMICO_GET_COST( BTCHDTL.ITEM_ID
, BTCHHDR.WIP_WHSE_CODE
, NULL
, BTCHHDR.ACTUAL_CMPLT_DATE)
, BTCHDTL.ACTUAL_QTY
, GMICUOM.I2UOM_CV(BTCHDTL.ITEM_ID
, 0
, BTCHDTL.ITEM_UM
, BTCHDTL.ACTUAL_QTY
, ITEMMST.ITEM_UM)*PMI_COMMON_PKG.PMICO_GET_COST( BTCHDTL.ITEM_ID
, BTCHHDR.WIP_WHSE_CODE
, NULL
, BTCHHDR.ACTUAL_CMPLT_DATE)
, CALENDR.PERIOD_YEAR
, CALENDR.PERIOD_NUM
, CALENDR.PERIOD_NAME
, PLCYMST.BASE_CURRENCY_CODE
, PLCYMST.GL_COST_MTHD
FROM GME_BATCH_HEADER BTCHHDR
, GME_MATERIAL_DETAILS BTCHDTL
, SY_ORGN_MST ORGNMST
, IC_ITEM_MST ITEMMST
, FM_FORM_MST_VL FORMMST
, FM_ROUT_HDR ROUTHDR
, PMI_GL_CALENDAR_V CALENDR
, GL_PLCY_MST PLCYMST
WHERE BTCHHDR.BATCH_ID = BTCHDTL.BATCH_ID
AND BTCHHDR.PLANT_CODE = ORGNMST.ORGN_CODE
AND ORGNMST.CO_CODE = PLCYMST.CO_CODE
AND BTCHDTL.ITEM_ID = ITEMMST.ITEM_ID
AND BTCHHDR.FORMULA_ID = FORMMST.FORMULA_ID
AND BTCHHDR.ROUTING_ID = ROUTHDR.ROUTING_ID(+)
AND BTCHHDR.BATCH_STATUS IN (3
, 4)
AND BTCHDTL.LINE_TYPE=1
AND ORGNMST.CO_CODE = CALENDR.CO_CODE
AND TRUNC(BTCHHDR.ACTUAL_CMPLT_DATE) BETWEEN CALENDR.START_DATE
AND CALENDR.END_DATE
AND (BTCHHDR.PLANT_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE')) WITH READ ONLY