View: CE_CP_DISC_TRX_V

Product:	CE - Cash Management
Description:	Cash Positioning Discoverer View for All Transaction Sources
Implementation/DBA Data:	APPS.CE_CP_DISC_TRX_V

View Text

SELECT TRX.WORKSHEET_HEADER_ID
, TRX.BANK_ACCOUNT_ID
, TRX.WORKSHEET_LINE_ID
, TRX.DESCRIPTION
, TRX.SOURCE_TYPE
, TRX.DETAIL
, TRX.BALANCE FROM (SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'ARR' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID FROM CE_CP_DISC_ARR_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'APP' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID FROM CE_CP_DISC_APP_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'XTI' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID FROM CE_CP_DISC_XTI_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'XTO' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID FROM CE_CP_DISC_XTO_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'PAY' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID FROM CE_CP_DISC_PAY_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'CEI' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID FROM CE_CP_DISC_CEI_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, 'CEO' SOURCE_TYPE
, DETAIL
, BALANCE
, REFERENCE_ID FROM CE_CP_DISC_CEO_V UNION ALL SELECT WORKSHEET_HEADER_ID
, BANK_ACCOUNT_ID
, WORKSHEET_LINE_ID
, DESCRIPTION
, SOURCE_TYPE
, DESCRIPTION
, DECODE(SOURCE_TYPE
, 'UDO'
, -AMOUNT
, AMOUNT) BALANCE
, WORKSHEET_LINE_ID REFERENCE_ID FROM CE_CP_WORKSHEET_LINES WHERE SOURCE_TYPE IN ('UDI'
, 'UDO')) TRX WHERE NOT EXISTS ( SELECT 1 FROM CE_CP_TRX_EXCLUDED EXC WHERE EXC.WORKSHEET_HEADER_ID = TRX.WORKSHEET_HEADER_ID AND EXC.TRX_TYPE = TRX.SOURCE_TYPE AND EXC.AS_OF_DATE = TO_DATE(SYSDATE
, 'YYYY/MM/DD') AND EXC.REFERENCE_ID = TRX.REFERENCE_ID)

Columns