View: PMIFV_PRODUCTFORMULA_INV_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	Formula detail with Onhanad inventory of the Ingredients
Implementation/DBA Data:	APPS.PMIFV_PRODUCTFORMULA_INV_V

View Text

SELECT FMASTER.FORMULA_NO
, /*REGULAR ATTRIBUTES*/ FMASTER.FORMULA_VERS
, FMASTER.FORMULA_TYPE
, SUBSTR(PMI_COST_ANALZ_PACK.GMCA_GET_MEANING( 'GMD_FORMULA_USE'
, FMASTER.FORMULA_TYPE)
, 1
, 80)
, FMASTER.SCALE_TYPE
, FMASTER.FORMULA_DESC1
, FMASTER.FORMULA_DESC2
, FMASTER.FORMULA_CLASS
, FMASTER.FMCONTROL_CLASS
, FMASTER.FORMULA_ID
, /* ID */ PRODUCTDETAIL.ITEM_ID
, FDETAIL.ITEM_ID
, /* RESOLVED FOREIGN KEYS */ FDETAIL.FORMULALINE_ID
, FDETAIL.LINE_NO
, FDETAIL.QTY
, FDETAIL.ITEM_UM
, FDETAIL.RELEASE_TYPE
, FDETAIL.SCRAP_FACTOR
, FDETAIL.SCALE_TYPE
, FDETAIL.COST_ALLOC
, FDETAIL.PHANTOM_TYPE
, FDETAIL.REWORK_TYPE
, PRODUCTDETAIL.QTY
, PRODUCTDETAIL.ITEM_UM
, INGREDIENT.ITEM_NO
, INGREDIENT.ITEM_DESC1
, INGREDIENT.ITEM_DESC2
, PRODUCT.ITEM_NO
, PRODUCT.ITEM_DESC1
, PRODUCT.ITEM_DESC2
, INVSUM.WHSE_CODE
, DECODE(PRODUCT.ITEM_UM
, FDETAIL.ITEM_UM
, INVSUM.ONHAND_PROD_QTY
, GMICUOM.I2UOM_CV( FDETAIL.ITEM_ID
, 0
, PRODUCT.ITEM_UM
, INVSUM.ONHAND_PROD_QTY
, FDETAIL.ITEM_UM))
, PRODUCT.ITEM_UM
, INGREDIENT.ITEM_UM
, FMASTER.CREATED_BY
, FMASTER.CREATION_DATE
, FMASTER.LAST_UPDATE_DATE
, FMASTER.LAST_UPDATED_BY
FROM FM_FORM_MST FMASTER
, FM_MATL_DTL FDETAIL
, ( SELECT FORMULA_ID
, ITEM_ID
, ITEM_UM
, SUM(QTY) QTY
FROM FM_MATL_DTL
WHERE LINE_TYPE=1 GROUP BY FORMULA_ID
, ITEM_ID
, ITEM_UM ) PRODUCTDETAIL
, IC_ITEM_MST PRODUCT
, IC_ITEM_MST INGREDIENT
, IC_SUMM_INV INVSUM
WHERE FMASTER.FORMULA_ID = FDETAIL.FORMULA_ID
AND FDETAIL.ITEM_ID = INGREDIENT.ITEM_ID
AND PRODUCTDETAIL.FORMULA_ID = FMASTER.FORMULA_ID
AND PRODUCT.ITEM_ID = PRODUCTDETAIL.ITEM_ID
AND FDETAIL.ITEM_ID = INVSUM.ITEM_ID(+)
AND FDETAIL.LINE_TYPE = -1 /* INGREDIENT LINES ONLY */
AND FMASTER.INACTIVE_IND = 0
AND FMASTER.DELETE_MARK = 0
AND 'TRUE' IN (SELECT PMI_SECURITY_PKG.SHOW_RECORD(ORGN_CODE)
FROM FM_FORM_EFF
WHERE FM_FORM_EFF.FORMULA_ID = FMASTER.FORMULA_ID
AND FM_FORM_EFF.ORGN_CODE IS NOT NULL UNION SELECT 'TRUE'FROM FM_FORM_EFF
WHERE FMASTER.FORMULA_ID = FM_FORM_EFF.FORMULA_ID(+)
AND FM_FORM_EFF.ORGN_CODE IS NULL) WITH READ ONLY

Columns