VIEW: APPS.QC_TRAN_VW2

Source

View Text - Preformatted

SELECT DISTINCT A.ITEM_ID , A.LOT_ID , B.ITEM_ID as target_item_id , B.LOT_ID as target_lot_id, B.COMPLETED_IND, B.CREATION_DATE , B.DOC_ID , D.LINE_NO as doc_line, B.DOC_TYPE , B.LINE_ID , D.LINE_TYPE , B.LOCATION , B.OP_CODE , B.ORGN_CODE , B.REASON_CODE , B.TEXT_CODE, B.TRANS_DATE , B.TRANS_ID , B.TRANS_QTY , B.TRANS_QTY2 , B.TRANS_STAT , B.TRANS_UM , B.TRANS_UM2 , B.WHSE_CODE , E.BATCH_NO as doc_no FROM IC_TRAN_VW1 A, IC_TRAN_PND B, PM_MATL_DTL C, PM_MATL_DTL D, PM_BTCH_HDR E WHERE A.DOC_TYPE = 'PROD' AND A.LINE_ID = C.LINE_ID AND C.LINE_TYPE = -1 AND B.DOC_TYPE = 'PROD' AND B.LINE_ID = D.LINE_ID AND D.LINE_TYPE IN (1, 2) AND A.DOC_ID = B.DOC_ID AND B.DOC_ID = E.BATCH_ID UNION SELECT A.ITEM_ID, A.LOT_ID,A.ITEM_ID, A.LOT_ID, A.COMPLETED_IND, A.CREATION_DATE, A.DOC_ID, B.LINE_NO, A.DOC_TYPE, A.LINE_ID, -1, A.LOCATION, A.OP_CODE, A.ORGN_CODE, A.REASON_CODE, A.TEXT_CODE, A.TRANS_DATE, A.TRANS_ID, A.TRANS_QTY, A.TRANS_QTY2, A.TRANS_STAT, A.TRANS_UM, A.TRANS_UM2, A.WHSE_CODE, C.ORDER_NO FROM IC_TRAN_VW1 A, OP_ORDR_DTL B, OP_ORDR_HDR C WHERE A.DOC_TYPE = 'OPSO' AND A.LINE_ID = B.LINE_ID AND B.ORDER_ID = C.ORDER_ID UNION SELECT ITEM_ID, LOT_ID, ITEM_ID, LOT_ID, COMPLETED_IND, CREATION_DATE, DOC_ID, DOC_LINE, DOC_TYPE, LINE_ID, -1, LOCATION, OP_CODE, ORGN_CODE, REASON_CODE, TEXT_CODE, TRANS_DATE, TRANS_ID, TRANS_QTY, TRANS_QTY2, TRANS_STAT, TRANS_UM, TRANS_UM2, WHSE_CODE, ' ' FROM IC_TRAN_VW1 WHERE DOC_TYPE IN ('ADJI','ADJR') AND TRANS_QTY < 0.0

View Text - HTML Formatted

SELECT DISTINCT A.ITEM_ID
, A.LOT_ID
, B.ITEM_ID AS TARGET_ITEM_ID
, B.LOT_ID AS TARGET_LOT_ID
, B.COMPLETED_IND
, B.CREATION_DATE
, B.DOC_ID
, D.LINE_NO AS DOC_LINE
, B.DOC_TYPE
, B.LINE_ID
, D.LINE_TYPE
, B.LOCATION
, B.OP_CODE
, B.ORGN_CODE
, B.REASON_CODE
, B.TEXT_CODE
, B.TRANS_DATE
, B.TRANS_ID
, B.TRANS_QTY
, B.TRANS_QTY2
, B.TRANS_STAT
, B.TRANS_UM
, B.TRANS_UM2
, B.WHSE_CODE
, E.BATCH_NO AS DOC_NO
FROM IC_TRAN_VW1 A
, IC_TRAN_PND B
, PM_MATL_DTL C
, PM_MATL_DTL D
, PM_BTCH_HDR E
WHERE A.DOC_TYPE = 'PROD'
AND A.LINE_ID = C.LINE_ID
AND C.LINE_TYPE = -1
AND B.DOC_TYPE = 'PROD'
AND B.LINE_ID = D.LINE_ID
AND D.LINE_TYPE IN (1
, 2)
AND A.DOC_ID = B.DOC_ID
AND B.DOC_ID = E.BATCH_ID UNION SELECT A.ITEM_ID
, A.LOT_ID
, A.ITEM_ID
, A.LOT_ID
, A.COMPLETED_IND
, A.CREATION_DATE
, A.DOC_ID
, B.LINE_NO
, A.DOC_TYPE
, A.LINE_ID
, -1
, A.LOCATION
, A.OP_CODE
, A.ORGN_CODE
, A.REASON_CODE
, A.TEXT_CODE
, A.TRANS_DATE
, A.TRANS_ID
, A.TRANS_QTY
, A.TRANS_QTY2
, A.TRANS_STAT
, A.TRANS_UM
, A.TRANS_UM2
, A.WHSE_CODE
, C.ORDER_NO
FROM IC_TRAN_VW1 A
, OP_ORDR_DTL B
, OP_ORDR_HDR C
WHERE A.DOC_TYPE = 'OPSO'
AND A.LINE_ID = B.LINE_ID
AND B.ORDER_ID = C.ORDER_ID UNION SELECT ITEM_ID
, LOT_ID
, ITEM_ID
, LOT_ID
, COMPLETED_IND
, CREATION_DATE
, DOC_ID
, DOC_LINE
, DOC_TYPE
, LINE_ID
, -1
, LOCATION
, OP_CODE
, ORGN_CODE
, REASON_CODE
, TEXT_CODE
, TRANS_DATE
, TRANS_ID
, TRANS_QTY
, TRANS_QTY2
, TRANS_STAT
, TRANS_UM
, TRANS_UM2
, WHSE_CODE
, ' '
FROM IC_TRAN_VW1
WHERE DOC_TYPE IN ('ADJI'
, 'ADJR')
AND TRANS_QTY < 0.0