VIEW: APPS.PMITS_INV_LINE_SUMM_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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