VIEW: APPS.PMI_REP_EXP_INVENTORY_V

Source

View Text - Preformatted

SELECT orgmst.co_code Company_ID, orgmst.orgn_code Organization_id, whsmst.whse_code warehouse_id, decode(profile.default_uom,itmmst.item_um,loctinv.LOCT_ONHAND, itmmst.item_um2,loctinv.LOCT_ONHAND2,gmicuom.i2uom_cv(loctinv.ITEM_ID,loctinv.LOT_ID, itmmst.item_um,loctinv.LOCT_ONHAND,profile.default_uom)) conv_onhand, loctinv.LOCT_ONHAND * pmi_common_pkg.PMICO_GET_COST(loctinv.item_id,loctinv.whse_code,null,sysdate) cur_ohd_val, decode(sign(sysdate-lotmst.expire_date),1,(decode(profile.default_uom, itmmst.item_um,loctinv.LOCT_ONHAND, itmmst.item_um2,loctinv.LOCT_ONHAND2, gmicuom.i2uom_cv(loctinv.ITEM_ID,loctinv.LOT_ID,itmmst.item_um, loctinv.LOCT_ONHAND,profile.default_uom))),0) conv_exp_qty, decode(sign(sysdate-lotmst.expire_date),1, (loctinv.LOCT_ONHAND * pmi_common_pkg.PMICO_GET_COST(loctinv.item_id,loctinv.whse_code,null,sysdate)),0) cur_exp_val from ic_loct_inv loctinv, ic_item_mst itmmst, ic_lots_mst lotmst, ic_whse_mst whsmst, sy_orgn_mst orgmst , (select fnd_profile.value('PMI$CONV_UOM') default_uom from dual) profile WHERE orgmst.orgn_code = whsmst.orgn_code and whsmst.whse_code = loctinv.whse_code and itmmst.item_id = loctinv.item_id and loctinv.item_id = lotmst.item_id and loctinv.lot_id = lotmst.lot_id and loctinv.loct_onhand <> 0 AND loctinv.lot_id <> 0 AND 'TRUE' = PMI_SECURITY_PKG.show_record(orgmst.orgn_code)

View Text - HTML Formatted

SELECT ORGMST.CO_CODE COMPANY_ID
, ORGMST.ORGN_CODE ORGANIZATION_ID
, WHSMST.WHSE_CODE WAREHOUSE_ID
, DECODE(PROFILE.DEFAULT_UOM
, ITMMST.ITEM_UM
, LOCTINV.LOCT_ONHAND
, ITMMST.ITEM_UM2
, LOCTINV.LOCT_ONHAND2
, GMICUOM.I2UOM_CV(LOCTINV.ITEM_ID
, LOCTINV.LOT_ID
, ITMMST.ITEM_UM
, LOCTINV.LOCT_ONHAND
, PROFILE.DEFAULT_UOM)) CONV_ONHAND
, LOCTINV.LOCT_ONHAND * PMI_COMMON_PKG.PMICO_GET_COST(LOCTINV.ITEM_ID
, LOCTINV.WHSE_CODE
, NULL
, SYSDATE) CUR_OHD_VAL
, DECODE(SIGN(SYSDATE-LOTMST.EXPIRE_DATE)
, 1
, (DECODE(PROFILE.DEFAULT_UOM
, ITMMST.ITEM_UM
, LOCTINV.LOCT_ONHAND
, ITMMST.ITEM_UM2
, LOCTINV.LOCT_ONHAND2
, GMICUOM.I2UOM_CV(LOCTINV.ITEM_ID
, LOCTINV.LOT_ID
, ITMMST.ITEM_UM
, LOCTINV.LOCT_ONHAND
, PROFILE.DEFAULT_UOM)))
, 0) CONV_EXP_QTY
, DECODE(SIGN(SYSDATE-LOTMST.EXPIRE_DATE)
, 1
, (LOCTINV.LOCT_ONHAND * PMI_COMMON_PKG.PMICO_GET_COST(LOCTINV.ITEM_ID
, LOCTINV.WHSE_CODE
, NULL
, SYSDATE))
, 0) CUR_EXP_VAL
FROM IC_LOCT_INV LOCTINV
, IC_ITEM_MST ITMMST
, IC_LOTS_MST LOTMST
, IC_WHSE_MST WHSMST
, SY_ORGN_MST ORGMST
, (SELECT FND_PROFILE.VALUE('PMI$CONV_UOM') DEFAULT_UOM
FROM DUAL) PROFILE
WHERE ORGMST.ORGN_CODE = WHSMST.ORGN_CODE
AND WHSMST.WHSE_CODE = LOCTINV.WHSE_CODE
AND ITMMST.ITEM_ID = LOCTINV.ITEM_ID
AND LOCTINV.ITEM_ID = LOTMST.ITEM_ID
AND LOCTINV.LOT_ID = LOTMST.LOT_ID
AND LOCTINV.LOCT_ONHAND <> 0
AND LOCTINV.LOT_ID <> 0
AND 'TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(ORGMST.ORGN_CODE)