eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.FV_TREASURY_EXTRACT_DETAILS_V

Source

View Text - Preformatted


SELECT FTC.event_id EVENT_ID, APHD.payment_hist_dist_id LINE_NUMBER, APHD.Accounting_Event_id , FTC.treasury_confirmation_id, FTCC.check_id, AI.Invoice_Currency_code , APHD.amount , APHD.payment_hist_dist_id , APHD.payment_history_id , APHD.invoice_distribution_id , APHD.invoice_payment_id , APHD.payment_hist_dist_id , APH.REV_PMT_HIST_ID , CGAC.cash_clearing_ccid CGAC_cash_clearing_ccid , FVOU.dit_confirm_cash_account , AID.dist_code_combination_id , AID.amount INVOICE_DISTRIBUTION_AMOUNT, AID.distribution_line_number , AID.Line_type_lookup_code , AI.invoice_amount , AI.accts_pay_code_combination_id , AID.po_distribution_id , AI.Invoice_type_lookup_code , AI.Invoice_Date , AI.Exchange_Date , AI.Exchange_Rate , AI.Exchange_Rate_Type , APHD.invoice_dist_amount , 200 BUS_FLOW_AP_APP_ID, 8901 BUS_FLOW_FV_APP_ID, 'AP_INV_DIST' BUS_FLOW_INV_DIST_TYPE, 'AP_INVOICES' BUS_FLOW_INV_ENTITY_CODE, AID.invoice_distribution_Id BUS_FLOW_INV_DIST_ID, AID.invoice_id BUS_FLOW_INV_ID, 201 BUS_FLOW_PO_APP_ID , 'PO_DISTRIBUTIONS_ALL' BUS_FLOW_PO_DIST_TYPE, 'PURCHASE ORDER' BUS_FLOW_PO_ENTITY_CODE, 'AP_PMT_DIST' BUS_FLOW_PAYMENT_DIST_TYPE, 'AP_PAYMENTS' BUS_FLOW_PAYMENT_ENTITY_CODE , 'FV_TREASURY_CONFIRMATIONS_ALL' BUS_FLOW_TREASURY_DIST_TYPE, 'TREASURY_CONFIRMATION' BUS_FLOW_TREASURY_ENTITY_CODE , CGAC.ap_asset_ccid, AI.accts_pay_code_combination_id, AC.CHECK_DATE , AC.CHECK_NUMBER, AC.Treasury_pay_number FROM fv_treasury_confirmations_all FTC, fv_treas_confirm_checks_v FTCC, fv_operating_units_all FVOU, ap_payment_history_all APH, ap_payment_hist_dists APHD, AP_INVOICE_DISTRIBUTIONS_ALL AID, XLA_EVENTS_GT XE, AP_INVOICES_ALL AI, CE_GL_ACCOUNTS_CCID CGAC, AP_CHECKS_ALL AC WHERE XE.application_id = 8901 AND XE.entity_code = 'TREASURY_CONFIRMATION' AND XE.event_type_code IN ('TREASURY_CONFIRM','TREASURY_BACKOUT') AND FTC.event_id = XE.event_id AND FTC.org_id = FVOU.org_id AND FTC.org_id = FTCC.org_id AND FTC.payment_instruction_id = FTCC.payment_instruction_id AND FTCC.check_id NOT IN (SELECT check_id FROM fv_voided_checks WHERE org_id = FTCC.org_id ) AND FTCC.check_id = APH.check_id AND FTCC.org_id = APH.org_id AND APH.accounting_event_id = (SELECT MAX(APH1.accounting_event_id ) FROM ap_payment_history_all APH1 WHERE APH1.check_id = APH.check_id AND APH1.org_id = APH.org_id AND APH1.TRANSACTION_TYPE = 'PAYMENT CREATED' GROUP BY APH.check_id ) AND APH.payment_history_id = APHD.payment_history_id AND APHD.invoice_distribution_id = AID.invoice_distribution_id AND APHD.pay_dist_lookup_code = 'CASH' AND AID.invoice_id = AI.invoice_id AND FTCC.check_id = AC.check_id AND AC.ce_bank_acct_use_id = CGAC.bank_acct_use_id UNION SELECT FVC.event_id EVENT_ID, APHD.payment_hist_dist_id LINE_NUMBER, APHD.Accounting_Event_id , FTC.treasury_confirmation_id, FVC.check_id, AI.Invoice_Currency_code , APHD.amount * -1 , APHD.payment_hist_dist_id , APHD.payment_history_id , APHD.invoice_distribution_id , APHD.invoice_payment_id , APHD.reversed_pay_hist_dist_id , APH.REV_PMT_HIST_ID , CGAC.cash_clearing_ccid CGAC_cash_clearing_ccid , FVOU.dit_confirm_cash_account , AID.dist_code_combination_id , AID.amount INVOICE_DISTRIBUTION_AMOUNT, AID.distribution_line_number , AID.Line_type_lookup_code , AI.invoice_amount , AI.accts_pay_code_combination_id , AID.po_distribution_id , AI.Invoice_type_lookup_code , AI.Invoice_Date , AI.Exchange_Date , AI.Exchange_Rate , AI.Exchange_Rate_Type , APHD.invoice_dist_amount , 200 BUS_FLOW_AP_APP_ID, 8901 BUS_FLOW_FV_APP_ID, 'AP_INV_DIST' BUS_FLOW_INV_DIST_TYPE, 'AP_INVOICES' BUS_FLOW_INV_ENTITY_CODE, AID.invoice_distribution_Id BUS_FLOW_INV_DIST_ID, AID.invoice_id BUS_FLOW_INV_ID, 201 BUS_FLOW_PO_APP_ID , 'PO_DISTRIBUTIONS_ALL' BUS_FLOW_PO_DIST_TYPE, 'PURCHASE ORDER' BUS_FLOW_PO_ENTITY_CODE, 'AP_PMT_DIST' BUS_FLOW_PAYMENT_DIST_TYPE, 'AP_PAYMENTS' BUS_FLOW_PAYMENT_ENTITY_CODE , 'FV_TREASURY_CONFIRMATIONS_ALL' BUS_FLOW_TREASURY_DIST_TYPE, 'TREASURY_CONFIRMATION' BUS_FLOW_TREASURY_ENTITY_CODE, CGAC.AP_ASSET_CCID, AI.accts_pay_code_combination_id, AC.CHECK_DATE , AC.CHECK_NUMBER, AC.Treasury_pay_number FROM fv_voided_checks FVC, fv_treasury_confirmations_all FTC, fv_operating_units_all FVOU, ap_payment_history_all APH, ap_payment_hist_dists APHD, AP_INVOICE_DISTRIBUTIONS_ALL AID, XLA_EVENTS_GT XE, AP_INVOICES_ALL AI, CE_GL_ACCOUNTS_CCID CGAC, AP_CHECKS_ALL AC WHERE XE.application_id = 8901 AND XE.entity_code = 'TREASURY_CONFIRMATION' AND XE.event_type_code = 'TREASURY_VOID' AND XE.event_id = FVC.event_id AND FTC.treasury_confirmation_id = (SELECT max(FTC1.treasury_confirmation_id) FROM fv_treasury_confirmations_all FTC1 WHERE FTC1.payment_instruction_id = FVC.payment_instruction_id GROUP BY FTC1.payment_instruction_id ) AND FTC.confirmation_status_flag <> 'B' AND FTC.org_id = FVOU.org_id AND FVC.check_id = APH.check_id AND AC.Void_date is not null AND APH.accounting_event_id = (SELECT MAX(APH1.accounting_event_id ) FROM ap_payment_history_all APH1 WHERE APH1.check_id = APH.check_id AND APH1.TRANSACTION_TYPE = 'PAYMENT CANCELLED' GROUP BY APH.check_id ) AND APH.payment_history_id = APHD.payment_history_id AND APHD.invoice_distribution_id = AID.invoice_distribution_id AND APHD.pay_dist_lookup_code = 'CASH' AND AID.invoice_id = AI.invoice_id AND FVC.check_id = AC.check_id AND AC.ce_bank_acct_use_id = CGAC.bank_acct_use_id

View Text - HTML Formatted

SELECT FTC.EVENT_ID EVENT_ID
, APHD.PAYMENT_HIST_DIST_ID LINE_NUMBER
, APHD.ACCOUNTING_EVENT_ID
, FTC.TREASURY_CONFIRMATION_ID
, FTCC.CHECK_ID
, AI.INVOICE_CURRENCY_CODE
, APHD.AMOUNT
, APHD.PAYMENT_HIST_DIST_ID
, APHD.PAYMENT_HISTORY_ID
, APHD.INVOICE_DISTRIBUTION_ID
, APHD.INVOICE_PAYMENT_ID
, APHD.PAYMENT_HIST_DIST_ID
, APH.REV_PMT_HIST_ID
, CGAC.CASH_CLEARING_CCID CGAC_CASH_CLEARING_CCID
, FVOU.DIT_CONFIRM_CASH_ACCOUNT
, AID.DIST_CODE_COMBINATION_ID
, AID.AMOUNT INVOICE_DISTRIBUTION_AMOUNT
, AID.DISTRIBUTION_LINE_NUMBER
, AID.LINE_TYPE_LOOKUP_CODE
, AI.INVOICE_AMOUNT
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AID.PO_DISTRIBUTION_ID
, AI.INVOICE_TYPE_LOOKUP_CODE
, AI.INVOICE_DATE
, AI.EXCHANGE_DATE
, AI.EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE
, APHD.INVOICE_DIST_AMOUNT
, 200 BUS_FLOW_AP_APP_ID
, 8901 BUS_FLOW_FV_APP_ID
, 'AP_INV_DIST' BUS_FLOW_INV_DIST_TYPE
, 'AP_INVOICES' BUS_FLOW_INV_ENTITY_CODE
, AID.INVOICE_DISTRIBUTION_ID BUS_FLOW_INV_DIST_ID
, AID.INVOICE_ID BUS_FLOW_INV_ID
, 201 BUS_FLOW_PO_APP_ID
, 'PO_DISTRIBUTIONS_ALL' BUS_FLOW_PO_DIST_TYPE
, 'PURCHASE ORDER' BUS_FLOW_PO_ENTITY_CODE
, 'AP_PMT_DIST' BUS_FLOW_PAYMENT_DIST_TYPE
, 'AP_PAYMENTS' BUS_FLOW_PAYMENT_ENTITY_CODE
, 'FV_TREASURY_CONFIRMATIONS_ALL' BUS_FLOW_TREASURY_DIST_TYPE
, 'TREASURY_CONFIRMATION' BUS_FLOW_TREASURY_ENTITY_CODE
, CGAC.AP_ASSET_CCID
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AC.CHECK_DATE
, AC.CHECK_NUMBER
, AC.TREASURY_PAY_NUMBER
FROM FV_TREASURY_CONFIRMATIONS_ALL FTC
, FV_TREAS_CONFIRM_CHECKS_V FTCC
, FV_OPERATING_UNITS_ALL FVOU
, AP_PAYMENT_HISTORY_ALL APH
, AP_PAYMENT_HIST_DISTS APHD
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, XLA_EVENTS_GT XE
, AP_INVOICES_ALL AI
, CE_GL_ACCOUNTS_CCID CGAC
, AP_CHECKS_ALL AC
WHERE XE.APPLICATION_ID = 8901
AND XE.ENTITY_CODE = 'TREASURY_CONFIRMATION'
AND XE.EVENT_TYPE_CODE IN ('TREASURY_CONFIRM'
, 'TREASURY_BACKOUT')
AND FTC.EVENT_ID = XE.EVENT_ID
AND FTC.ORG_ID = FVOU.ORG_ID
AND FTC.ORG_ID = FTCC.ORG_ID
AND FTC.PAYMENT_INSTRUCTION_ID = FTCC.PAYMENT_INSTRUCTION_ID
AND FTCC.CHECK_ID NOT IN (SELECT CHECK_ID
FROM FV_VOIDED_CHECKS
WHERE ORG_ID = FTCC.ORG_ID )
AND FTCC.CHECK_ID = APH.CHECK_ID
AND FTCC.ORG_ID = APH.ORG_ID
AND APH.ACCOUNTING_EVENT_ID = (SELECT MAX(APH1.ACCOUNTING_EVENT_ID )
FROM AP_PAYMENT_HISTORY_ALL APH1
WHERE APH1.CHECK_ID = APH.CHECK_ID
AND APH1.ORG_ID = APH.ORG_ID
AND APH1.TRANSACTION_TYPE = 'PAYMENT CREATED' GROUP BY APH.CHECK_ID )
AND APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.INVOICE_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND AID.INVOICE_ID = AI.INVOICE_ID
AND FTCC.CHECK_ID = AC.CHECK_ID
AND AC.CE_BANK_ACCT_USE_ID = CGAC.BANK_ACCT_USE_ID UNION SELECT FVC.EVENT_ID EVENT_ID
, APHD.PAYMENT_HIST_DIST_ID LINE_NUMBER
, APHD.ACCOUNTING_EVENT_ID
, FTC.TREASURY_CONFIRMATION_ID
, FVC.CHECK_ID
, AI.INVOICE_CURRENCY_CODE
, APHD.AMOUNT * -1
, APHD.PAYMENT_HIST_DIST_ID
, APHD.PAYMENT_HISTORY_ID
, APHD.INVOICE_DISTRIBUTION_ID
, APHD.INVOICE_PAYMENT_ID
, APHD.REVERSED_PAY_HIST_DIST_ID
, APH.REV_PMT_HIST_ID
, CGAC.CASH_CLEARING_CCID CGAC_CASH_CLEARING_CCID
, FVOU.DIT_CONFIRM_CASH_ACCOUNT
, AID.DIST_CODE_COMBINATION_ID
, AID.AMOUNT INVOICE_DISTRIBUTION_AMOUNT
, AID.DISTRIBUTION_LINE_NUMBER
, AID.LINE_TYPE_LOOKUP_CODE
, AI.INVOICE_AMOUNT
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AID.PO_DISTRIBUTION_ID
, AI.INVOICE_TYPE_LOOKUP_CODE
, AI.INVOICE_DATE
, AI.EXCHANGE_DATE
, AI.EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE
, APHD.INVOICE_DIST_AMOUNT
, 200 BUS_FLOW_AP_APP_ID
, 8901 BUS_FLOW_FV_APP_ID
, 'AP_INV_DIST' BUS_FLOW_INV_DIST_TYPE
, 'AP_INVOICES' BUS_FLOW_INV_ENTITY_CODE
, AID.INVOICE_DISTRIBUTION_ID BUS_FLOW_INV_DIST_ID
, AID.INVOICE_ID BUS_FLOW_INV_ID
, 201 BUS_FLOW_PO_APP_ID
, 'PO_DISTRIBUTIONS_ALL' BUS_FLOW_PO_DIST_TYPE
, 'PURCHASE ORDER' BUS_FLOW_PO_ENTITY_CODE
, 'AP_PMT_DIST' BUS_FLOW_PAYMENT_DIST_TYPE
, 'AP_PAYMENTS' BUS_FLOW_PAYMENT_ENTITY_CODE
, 'FV_TREASURY_CONFIRMATIONS_ALL' BUS_FLOW_TREASURY_DIST_TYPE
, 'TREASURY_CONFIRMATION' BUS_FLOW_TREASURY_ENTITY_CODE
, CGAC.AP_ASSET_CCID
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AC.CHECK_DATE
, AC.CHECK_NUMBER
, AC.TREASURY_PAY_NUMBER
FROM FV_VOIDED_CHECKS FVC
, FV_TREASURY_CONFIRMATIONS_ALL FTC
, FV_OPERATING_UNITS_ALL FVOU
, AP_PAYMENT_HISTORY_ALL APH
, AP_PAYMENT_HIST_DISTS APHD
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, XLA_EVENTS_GT XE
, AP_INVOICES_ALL AI
, CE_GL_ACCOUNTS_CCID CGAC
, AP_CHECKS_ALL AC
WHERE XE.APPLICATION_ID = 8901
AND XE.ENTITY_CODE = 'TREASURY_CONFIRMATION'
AND XE.EVENT_TYPE_CODE = 'TREASURY_VOID'
AND XE.EVENT_ID = FVC.EVENT_ID
AND FTC.TREASURY_CONFIRMATION_ID = (SELECT MAX(FTC1.TREASURY_CONFIRMATION_ID)
FROM FV_TREASURY_CONFIRMATIONS_ALL FTC1
WHERE FTC1.PAYMENT_INSTRUCTION_ID = FVC.PAYMENT_INSTRUCTION_ID GROUP BY FTC1.PAYMENT_INSTRUCTION_ID )
AND FTC.CONFIRMATION_STATUS_FLAG <> 'B'
AND FTC.ORG_ID = FVOU.ORG_ID
AND FVC.CHECK_ID = APH.CHECK_ID
AND AC.VOID_DATE IS NOT NULL
AND APH.ACCOUNTING_EVENT_ID = (SELECT MAX(APH1.ACCOUNTING_EVENT_ID )
FROM AP_PAYMENT_HISTORY_ALL APH1
WHERE APH1.CHECK_ID = APH.CHECK_ID
AND APH1.TRANSACTION_TYPE = 'PAYMENT CANCELLED' GROUP BY APH.CHECK_ID )
AND APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHD.INVOICE_DISTRIBUTION_ID = AID.INVOICE_DISTRIBUTION_ID
AND APHD.PAY_DIST_LOOKUP_CODE = 'CASH'
AND AID.INVOICE_ID = AI.INVOICE_ID
AND FVC.CHECK_ID = AC.CHECK_ID
AND AC.CE_BANK_ACCT_USE_ID = CGAC.BANK_ACCT_USE_ID