View: PMITS_INV_LINE_SUMM_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	This View Summarizes Transactions at Line Level
Implementation/DBA Data:	APPS.PMITS_INV_LINE_SUMM_V

View Text

SELECT TRAN.DOC_ID
, DECODE(TRAN.DOC_TYPE
, 'XFER'
, TRAN.DOC_ID
, 'TRNI'
, TRAN.DOC_ID
, 'MTRI'
, TRAN.DOC_ID
, 'TRNR'
, TRAN.DOC_ID
, 1) JOIN_DOC_ID
, TRAN.DOC_TYPE
, TRAN.LINE_ID
, TRAN.ITEM_ID
, DECODE(COUNT(DISTINCT(TRAN.CO_CODE))
, 1
, MIN(TRAN.CO_CODE)
, '*') COMPANY_CODE
, DECODE(COUNT(DISTINCT(TRAN.ORGN_CODE))
, 1
, MIN(TRAN.ORGN_CODE)
, '*') ORGANIZATION_CODE
, DECODE(COUNT(DISTINCT(TRAN.WHSE_CODE))
, 1
, MIN(TRAN.WHSE_CODE)
, '*') WAREHOUSE_CODE
, DECODE(COUNT(DISTINCT(TRAN.LOCATION))
, 1
, MIN(TRAN.LOCATION)
, '*') LOCATION
, DECODE(COUNT(DISTINCT(TRAN.TRANS_DATE))
, 1
, FND_DATE.DATE_TO_DISPLAYDT(MIN(TRAN.TRANS_DATE))
, '*') TRANSACTION_DATE
, DECODE(COUNT(DISTINCT(TRAN.LOT_ID))
, 1
, TO_CHAR(MIN(TRAN.LOT_ID))
, '*') LOT_ID
, DECODE(COUNT(DISTINCT(LOT.LOT_NO))
, 1
, MIN(LOT.LOT_NO)
, '*') LOT_NUMBER
, DECODE(COUNT(DISTINCT(NVL(LOT.SUBLOT_NO
, '~')))
, 1
, MIN(LOT.SUBLOT_NO)
, '*') SUBLOT_NUMBER
, DECODE(COUNT(DISTINCT(NVL(TRAN.LOT_STATUS
, '~')))
, 1
, MIN(TRAN.LOT_STATUS)
, '*') LOT_STATUS
, DECODE(COUNT(DISTINCT(NVL(TRAN.QC_GRADE
, '~')))
, 1
, MIN(TRAN.QC_GRADE)
, '*') QUALITY_CONTROL_GRADE
, DECODE(COUNT(DISTINCT(NVL(TRAN.REASON_CODE
, '~')))
, 1
, MIN(TRAN.REASON_CODE)
, '*') REASON_CODE
, DECODE(COUNT(DISTINCT(TRAN.TRANS_UM))
, 1
, MIN(TRAN.TRANS_UM)
, '*') TRANSACTION_UOM
, DECODE(COUNT(DISTINCT(TRAN.TRANS_UM2))
, 1
, MIN(TRAN.TRANS_UM2)
, '*') TRANSACTION_SECONDARY_UOM
, SUM(TRAN.TRANS_QTY) TRANSACTION_QTY
, SUM(TRAN.TRANS_QTY2) TRANSACTION_QTY2
, 'MATERIAL'
FROM IC_TRAN_VW1 TRAN
, IC_LOTS_MST LOT
, IC_ITEM_MST ITM
WHERE TRAN.ITEM_ID = ITM.ITEM_ID
AND LOT.LOT_ID = TRAN.LOT_ID
AND LOT.ITEM_ID = TRAN.ITEM_ID
AND 'TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(TRAN.ORGN_CODE) GROUP BY TRAN.DOC_ID
, DECODE(TRAN.DOC_TYPE
, 'XFER'
, TRAN.DOC_ID
, 'TRNI'
, TRAN.DOC_ID
, 'MTRI'
, TRAN.DOC_ID
, 'TRNR'
, TRAN.DOC_ID
, 1)
, TRAN.DOC_TYPE
, TRAN.LINE_ID
, TRAN.ITEM_ID

Columns