VIEW: APPS.RA_CUSTOMER_TRX_LINES_FRT_V

Source

View Text - Preformatted

SELECT CTL.ROWID , CTL.CUSTOMER_TRX_ID , CTL.CUSTOMER_TRX_LINE_ID , CTL.LINE_TYPE , CTL.LINE_NUMBER , CTL.EXTENDED_AMOUNT , CTL.REVENUE_AMOUNT , LGD.CUST_TRX_LINE_GL_DIST_ID , LGD.CODE_COMBINATION_ID , LGD.CONCATENATED_SEGMENTS , CTL.PREVIOUS_CUSTOMER_TRX_ID , CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID , CTL.LINK_TO_CUST_TRX_LINE_ID , CTL.LAST_UPDATE_DATE , CTL.LAST_UPDATED_BY , CTL.CREATION_DATE , CTL.CREATED_BY , CTL.LAST_UPDATE_LOGIN , CTL.ATTRIBUTE_CATEGORY , CTL.ATTRIBUTE1 , CTL.ATTRIBUTE2 , CTL.ATTRIBUTE3 , CTL.ATTRIBUTE4 , CTL.ATTRIBUTE5 , CTL.ATTRIBUTE6 , CTL.ATTRIBUTE7 , CTL.ATTRIBUTE8 , CTL.ATTRIBUTE9 , CTL.ATTRIBUTE10 , CTL.ATTRIBUTE11 , CTL.ATTRIBUTE12 , CTL.ATTRIBUTE13 , CTL.ATTRIBUTE14 , CTL.ATTRIBUTE15 , CTL.INTERFACE_LINE_CONTEXT , CTL.INTERFACE_LINE_ATTRIBUTE1 , CTL.INTERFACE_LINE_ATTRIBUTE2 , CTL.INTERFACE_LINE_ATTRIBUTE3 , CTL.INTERFACE_LINE_ATTRIBUTE4 , CTL.INTERFACE_LINE_ATTRIBUTE5 , CTL.INTERFACE_LINE_ATTRIBUTE6 , CTL.INTERFACE_LINE_ATTRIBUTE7 , CTL.INTERFACE_LINE_ATTRIBUTE8 , CTL.INTERFACE_LINE_ATTRIBUTE9 , CTL.INTERFACE_LINE_ATTRIBUTE10 , CTL.INTERFACE_LINE_ATTRIBUTE11 , CTL.INTERFACE_LINE_ATTRIBUTE12 , CTL.INTERFACE_LINE_ATTRIBUTE13 , CTL.INTERFACE_LINE_ATTRIBUTE14 , CTL.INTERFACE_LINE_ATTRIBUTE15 , CTL.DEFAULT_USSGL_TRX_CODE_CONTEXT , CTL.DEFAULT_USSGL_TRANSACTION_CODE , CTL_LINE.LINE_NUMBER , CTL_LINE.EXTENDED_AMOUNT , CTL_INV_LINE.LINE_NUMBER , CTL_INV_FRT.EXTENDED_AMOUNT , LGD_INV_FRT.CODE_COMBINATION_ID , ARP_BAL_UTIL.GET_LINE_BALANCE(CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID, CTL_INV_FRT.EXTENDED_AMOUNT) , ARP_BAL_UTIL.GET_LINE_BALANCE(CTL.CUSTOMER_TRX_LINE_ID, CTL.EXTENDED_AMOUNT) FROM RA_CUSTOMER_TRX_LINES CTL, RA_CUSTOMER_TRX_LINES CTL_LINE, RA_CUST_TRX_LINE_GL_DIST LGD, RA_CUST_TRX_LINE_GL_DIST LGD_INV_FRT, RA_CUSTOMER_TRX_LINES CTL_INV_FRT, RA_CUSTOMER_TRX_LINES CTL_INV_LINE WHERE CTL.LINE_TYPE = 'FREIGHT' AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID = CTL_INV_FRT.CUSTOMER_TRX_LINE_ID(+) AND CTL_LINE.CUSTOMER_TRX_LINE_ID(+) = CTL.LINK_TO_CUST_TRX_LINE_ID AND CTL.CUSTOMER_TRX_LINE_ID = LGD.CUSTOMER_TRX_LINE_ID (+) AND NVL(LGD.CUST_TRX_LINE_GL_DIST_ID, -99) = (SELECT NVL(MAX(CUST_TRX_LINE_GL_DIST_ID), -99) FROM RA_CUST_TRX_LINE_GL_DIST DIST2 WHERE DIST2.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID ) AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID = LGD_INV_FRT.CUSTOMER_TRX_LINE_ID (+) AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID = CTL_INV_FRT.CUSTOMER_TRX_LINE_ID (+) AND CTL_INV_FRT.LINK_TO_CUST_TRX_LINE_ID = CTL_INV_LINE.CUSTOMER_TRX_LINE_ID(+) AND NVL(LGD_INV_FRT.CUST_TRX_LINE_GL_DIST_ID, -99) = (SELECT NVL(MAX(CUST_TRX_LINE_GL_DIST_ID), -99) FROM RA_CUST_TRX_LINE_GL_DIST DIST2 WHERE DIST2.CUSTOMER_TRX_LINE_ID = CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID)

View Text - HTML Formatted

SELECT CTL.ROWID
, CTL.CUSTOMER_TRX_ID
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.LINE_TYPE
, CTL.LINE_NUMBER
, CTL.EXTENDED_AMOUNT
, CTL.REVENUE_AMOUNT
, LGD.CUST_TRX_LINE_GL_DIST_ID
, LGD.CODE_COMBINATION_ID
, LGD.CONCATENATED_SEGMENTS
, CTL.PREVIOUS_CUSTOMER_TRX_ID
, CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID
, CTL.LINK_TO_CUST_TRX_LINE_ID
, CTL.LAST_UPDATE_DATE
, CTL.LAST_UPDATED_BY
, CTL.CREATION_DATE
, CTL.CREATED_BY
, CTL.LAST_UPDATE_LOGIN
, CTL.ATTRIBUTE_CATEGORY
, CTL.ATTRIBUTE1
, CTL.ATTRIBUTE2
, CTL.ATTRIBUTE3
, CTL.ATTRIBUTE4
, CTL.ATTRIBUTE5
, CTL.ATTRIBUTE6
, CTL.ATTRIBUTE7
, CTL.ATTRIBUTE8
, CTL.ATTRIBUTE9
, CTL.ATTRIBUTE10
, CTL.ATTRIBUTE11
, CTL.ATTRIBUTE12
, CTL.ATTRIBUTE13
, CTL.ATTRIBUTE14
, CTL.ATTRIBUTE15
, CTL.INTERFACE_LINE_CONTEXT
, CTL.INTERFACE_LINE_ATTRIBUTE1
, CTL.INTERFACE_LINE_ATTRIBUTE2
, CTL.INTERFACE_LINE_ATTRIBUTE3
, CTL.INTERFACE_LINE_ATTRIBUTE4
, CTL.INTERFACE_LINE_ATTRIBUTE5
, CTL.INTERFACE_LINE_ATTRIBUTE6
, CTL.INTERFACE_LINE_ATTRIBUTE7
, CTL.INTERFACE_LINE_ATTRIBUTE8
, CTL.INTERFACE_LINE_ATTRIBUTE9
, CTL.INTERFACE_LINE_ATTRIBUTE10
, CTL.INTERFACE_LINE_ATTRIBUTE11
, CTL.INTERFACE_LINE_ATTRIBUTE12
, CTL.INTERFACE_LINE_ATTRIBUTE13
, CTL.INTERFACE_LINE_ATTRIBUTE14
, CTL.INTERFACE_LINE_ATTRIBUTE15
, CTL.DEFAULT_USSGL_TRX_CODE_CONTEXT
, CTL.DEFAULT_USSGL_TRANSACTION_CODE
, CTL_LINE.LINE_NUMBER
, CTL_LINE.EXTENDED_AMOUNT
, CTL_INV_LINE.LINE_NUMBER
, CTL_INV_FRT.EXTENDED_AMOUNT
, LGD_INV_FRT.CODE_COMBINATION_ID
, ARP_BAL_UTIL.GET_LINE_BALANCE(CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID
, CTL_INV_FRT.EXTENDED_AMOUNT)
, ARP_BAL_UTIL.GET_LINE_BALANCE(CTL.CUSTOMER_TRX_LINE_ID
, CTL.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES CTL
, RA_CUSTOMER_TRX_LINES CTL_LINE
, RA_CUST_TRX_LINE_GL_DIST LGD
, RA_CUST_TRX_LINE_GL_DIST LGD_INV_FRT
, RA_CUSTOMER_TRX_LINES CTL_INV_FRT
, RA_CUSTOMER_TRX_LINES CTL_INV_LINE
WHERE CTL.LINE_TYPE = 'FREIGHT'
AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID = CTL_INV_FRT.CUSTOMER_TRX_LINE_ID(+)
AND CTL_LINE.CUSTOMER_TRX_LINE_ID(+) = CTL.LINK_TO_CUST_TRX_LINE_ID
AND CTL.CUSTOMER_TRX_LINE_ID = LGD.CUSTOMER_TRX_LINE_ID (+)
AND NVL(LGD.CUST_TRX_LINE_GL_DIST_ID
, -99) = (SELECT NVL(MAX(CUST_TRX_LINE_GL_DIST_ID)
, -99)
FROM RA_CUST_TRX_LINE_GL_DIST DIST2
WHERE DIST2.CUSTOMER_TRX_LINE_ID = CTL.CUSTOMER_TRX_LINE_ID )
AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID = LGD_INV_FRT.CUSTOMER_TRX_LINE_ID (+)
AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID = CTL_INV_FRT.CUSTOMER_TRX_LINE_ID (+)
AND CTL_INV_FRT.LINK_TO_CUST_TRX_LINE_ID = CTL_INV_LINE.CUSTOMER_TRX_LINE_ID(+)
AND NVL(LGD_INV_FRT.CUST_TRX_LINE_GL_DIST_ID
, -99) = (SELECT NVL(MAX(CUST_TRX_LINE_GL_DIST_ID)
, -99)
FROM RA_CUST_TRX_LINE_GL_DIST DIST2
WHERE DIST2.CUSTOMER_TRX_LINE_ID = CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID)