Product: | PMI - Process Manufacturing Intelligence (Obsolete) |
---|---|
Description: | This is a view on top of OPM Item Master table. It contains all information relating to an item, such as lot/sublot control, unit of measure information, and all class and type designations. |
Implementation/DBA Data: | APPS.PMITS_ITEM_MASTER_V |
SELECT ITEM.ITEM_ID
, ITEM.ITEM_NO
, ITEM.ALT_ITEMA
, ITEM.ALT_ITEMB
, ITEM.ITEM_UM
, ITEM.DUALUM_IND
, DUAL_IND_LKUP.MEANING
, ITEM.ITEM_UM2
, ITEM.DEVIATION_LO
, ITEM.DEVIATION_HI
, ITEM.LOT_CTL
, DECODE(ITEM.LOT_CTL
, '0'
, NO_LKUP.MEANING
, '1'
, YES_LKUP.MEANING
, NULL)
, ITEM.LOT_INDIVISIBLE
, DECODE(ITEM.LOT_INDIVISIBLE
, '0'
, NO_LKUP.MEANING
, '1'
, YES_LKUP.MEANING
, NULL)
, ITEM.SUBLOT_CTL
, DECODE(ITEM.SUBLOT_CTL
, '0'
, NO_LKUP.MEANING
, '1'
, YES_LKUP.MEANING
, NULL)
, ITEM.LOCT_CTL
, LOCT_CTL_LKUP.MEANING
, ITEM.NONINV_IND
, DECODE(ITEM.NONINV_IND
, '0'
, NO_LKUP.MEANING
, '1'
, YES_LKUP.MEANING
, NULL)
, ITEM.MATCH_TYPE
, MATCHING_LKUP.MEANING
, ITEM.INACTIVE_IND
, DECODE(ITEM.INACTIVE_IND
, '0'
, NO_LKUP.MEANING
, '1'
, YES_LKUP.MEANING
, NULL)
, ITEM.INV_TYPE
, ITEM.SHELF_LIFE
, ITEM.RETEST_INTERVAL
, ITEM.GL_CLASS
, ITEM.INV_CLASS
, ITEM.SALES_CLASS
, ITEM.SHIP_CLASS
, ITEM.FRT_CLASS
, ITEM.PRICE_CLASS
, ITEM.STORAGE_CLASS
, ITEM.PURCH_CLASS
, ITEM.TAX_CLASS
, ITEM.CUSTOMS_CLASS
, ITEM.ALLOC_CLASS
, ITEM.PLANNING_CLASS
, ITEM.ITEMCOST_CLASS
, ITEM.UPC_CODE
, ITEM.GRADE_CTL
, DECODE(ITEM.GRADE_CTL
, '0'
, NO_LKUP.MEANING
, '1'
, YES_LKUP.MEANING
, NULL)
, ITEM.STATUS_CTL
, STATUS_CTL_LKUP.MEANING
, ITEM.QC_GRADE
, ITEM.LOT_STATUS
, ITEM.QCITEM_ID
, ITEM.QCHOLD_RES_CODE
, ITEM.EXPACTION_CODE
, ITEM.PLANNING_CATEGORY_ID
, ITEM.PRICE_CATEGORY_ID
, ITEM.EXPACTION_INTERVAL
, ITEM.WHSE_ITEM_ID
, ITEM.EXPERIMENTAL_IND
, EXP_IND_LKUP.MEANING
, ITEM.EXPORTED_DATE
, ITEM.DELETE_MARK
, ITEM.TEXT_CODE
, ITEM.SEQ_DPND_CLASS
, ITEM.COMMODITY_CODE
, ITEM.ONT_PRICING_QTY_SOURCE
, ITEM.CREATION_DATE
, ITEM.CREATED_BY
, ITEM.LAST_UPDATE_DATE
, ITEM.LAST_UPDATED_BY
, ITEM.LAST_UPDATE_LOGIN
, ITEM.ITEM_ABCCODE
, ITEM.ALLOC_CATEGORY_ID
, ITEM.CUSTOMS_CATEGORY_ID
, ITEM.FRT_CATEGORY_ID
, ITEM.GL_CATEGORY_ID
, ITEM.INV_CATEGORY_ID
, ITEM.COST_CATEGORY_ID
, ITEM.PURCH_CATEGORY_ID
, ITEM.SALES_CATEGORY_ID
, ITEM.SEQ_CATEGORY_ID
, ITEM.SHIP_CATEGORY_ID
, ITEM.STORAGE_CATEGORY_ID
, ITEM.TAX_CATEGORY_ID
, ITEM.ITEM_DESC1
, ITEM.ITEM_DESC2
FROM IC_ITEM_MST_VL ITEM
, GEM_LOOKUPS DUAL_IND_LKUP
, GEM_LOOKUPS LOCT_CTL_LKUP
, GEM_LOOKUPS STATUS_CTL_LKUP
, GEM_LOOKUPS MATCHING_LKUP
, GEM_LOOKUPS EXP_IND_LKUP
, GEM_LOOKUPS YES_LKUP
, GEM_LOOKUPS NO_LKUP
WHERE DUAL_IND_LKUP.LOOKUP_TYPE = 'DUALUM_IND'
AND DUAL_IND_LKUP.LOOKUP_CODE = ITEM.DUALUM_IND
AND LOCT_CTL_LKUP.LOOKUP_TYPE = 'LOCATION_CONTROL'
AND LOCT_CTL_LKUP.LOOKUP_CODE = ITEM.LOCT_CTL
AND STATUS_CTL_LKUP.LOOKUP_TYPE = 'STATUS_CONTROL'
AND STATUS_CTL_LKUP.LOOKUP_CODE = ITEM.STATUS_CTL
AND MATCHING_LKUP.LOOKUP_TYPE = 'MATCHING'
AND MATCHING_LKUP.LOOKUP_CODE = ITEM.MATCH_TYPE
AND EXP_IND_LKUP.LOOKUP_TYPE = 'EXPERIMENTAL_IND'
AND EXP_IND_LKUP.LOOKUP_CODE = ITEM.EXPERIMENTAL_IND
AND YES_LKUP.LOOKUP_TYPE = 'GME_YES_NO'
AND YES_LKUP.LOOKUP_CODE = '1'
AND NO_LKUP.LOOKUP_TYPE = 'GME_YES_NO'
AND NO_LKUP.LOOKUP_CODE = '0'