VIEW: APPS.FV_TREASURY_EXTRACT_DETAILS_V

Source

View Text - Preformatted

SELECT FTC.event_id EVENT_ID, APHD.payment_hist_dist_id LINE_NUMBER, exgt.FUND_CATEGORY FEDERAL_FUND_CATEGORY, exgt.FUND_EXPIRED_STATUS FEDERAL_FUND_EXPIRED_STATUS, APHD.Accounting_Event_id FV_PAY_ACCOUNTING_EVENT_ID, DECODE (exgt.FUND_CATEGORY,'A','Direct','B','Direct','C','Direct','R','Reimbursab le','S','Reimbursable','T','Reimbursable') FEDERAL_FUND_CAT_DESC, exgt.ADJUSTMENT_TYPE FEDERAL_ADJUSTMENT_TYPE, FTC.treasury_confirmation_id FV_TREASURY_CONFIRMATION_ID, exgt.PRIOR_YEAR_FLAG FEDERAL_PRIOR_YEAR_FLAG, FTCC.check_id FV_PAY_CHECK_ID, AI.Invoice_Currency_code FV_INV_CURRENCY_CODE, APHD.amount FV_PAY_DIST_AMOUNT, APHD.payment_hist_dist_id FV_PAY_HIST_DIST_ID, APHD.payment_history_id FV_PAYMENT_HISTORY_ID, APHD.invoice_distribution_id FV_INV_DISTRIBUTION_ID, APHD.invoice_payment_id FV_INVOICE_PAYMENT_ID, APHD.payment_hist_dist_id FV_REV_PAY_HIST_DIST_ID, APH.REV_PMT_HIST_ID FV_REV_PMT_HIST_ID, CGAC.cash_clearing_ccid CGAC_cash_clearing_ccid, exgt.ENT_UNPAID_OBL_AMOUNT ENT_UNPAID_OBL_AMOUNT, FVOU.dit_confirm_cash_account DIT_CONFIRM_CASH_ACCOUNT, exgt.ACC_UNPAID_OBL_AMOUNT ACC_UNPAID_OBL_AMOUNT, decode(AI.Invoice_type_lookup_code, 'INTEREST', decode(NVL(apsp.PRORATE_INT_INV_ACROSS_DISTS,'N'), 'N' , AID.accts_pay_code_combination_id, AID.dist_code_combination_id), AID.dist_code_combination_id), AID.amount INVOICE_DISTRIBUTION_AMOUNT, AID.distribution_line_number FV_INV_DIST_LINE_NUMBER , exgt.ENT_ANTICIPATED_BUDGET_AMOUNT ENT_ANTICIPATED_BUDGET_AMOUNT, exgt.ACC_ANTICIPATED_BUDGET_AMOUNT ACC_ANTICIPATED_BUDGET_AMOUNT, AID.Line_type_lookup_code FV_INV_LINE_TYPE_LOOKUP_CODE, exgt.ENT_UNANTICIPATED_BUD_AMOUNT ENT_UNANTICIPATED_BUD_AMOUNT, exgt.ACC_UNANTICIPATED_BUD_AMOUNT ACC_UNANTICIPATED_BUD_AMOUNT, AI.invoice_amount FV_INVOICE_AMOUNT, AI.accts_pay_code_combination_id FV_INV_ACCTS_PAY_CCID, AID.po_distribution_id FV_PO_DISTRIBUTION_ID, exgt.ENT_UNPAID_EXP_AMOUNT ENT_UNPAID_EXP_AMOUNT, exgt.ACC_UNPAID_EXP_AMOUNT ACC_UNPAID_EXP_AMOUNT, AI.Invoice_type_lookup_code FV_INV_TYPE_LOOKUP_CODE, exgt.ENT_PAID_EXP_AMOUNT ENT_PAID_EXP_AMOUNT, exgt.ACC_PAID_EXP_AMOUNT ACC_PAID_EXP_AMOUNT, AI.Invoice_Date FV_INVOICE_DATE, exgt.ENT_PAID_OBL_AMOUNT ENT_PAID_OBL_AMOUNT, AI.Exchange_Date FV_INV_EXCHANGE_DATE, exgt.ACC_PAID_OBL_AMOUNT ACC_PAID_OBL_AMOUNT, AI.Exchange_Rate FV_INV_EXCHANGE_RATE, AI.Exchange_Rate_Type FV_INV_EXCHANGE_RATE_TYPE, exgt.ACC_EXPENDED_APPROP_AMOUNT ACC_EXPENDED_APPROP_AMOUNT, APHD.invoice_dist_amount FV_APHD_INV_DIST_AMOUNT, exgt.ENT_EXPENDED_APPROP_AMOUNT ENT_EXPENDED_APPROP_AMOUNT, exgt.ENT_PAID_EXP_PYA_AMOUNT ENT_PAID_EXP_PYA_AMOUNT, 200 BUS_FLOW_AP_APP_ID, exgt.ACC_PAID_EXP_PYA_AMOUNT ACC_PAID_EXP_PYA_AMOUNT, exgt.ENT_PAID_EXP_PYA_OFF_AMOUNT ENT_PAID_EXP_PYA_OFF_AMOUNT, 8901 BUS_FLOW_FV_APP_ID, exgt.ACC_PAID_EXP_PYA_OFF_AMOUNT ACC_PAID_EXP_PYA_OFF_AMOUNT, '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 FV_AP_CASH_ACCOUNT, AI.accts_pay_code_combination_id FV_AP_LIABILITY_ACCOUNT , AC.CHECK_DATE FV_CHECK_DATE , AC.CHECK_NUMBER FV_CHECK_NUMBER, AC.Treasury_pay_number FV_TREASURY_PAY_NUMBER, exgt.DIRECT_OR_REIMB FEDERAL_DIRECT_OR_REIMB, exgt.fund_type FEDERAL_FUND_TYPE, NVL(APHD.PAID_BASE_AMOUNT, APHD.amount) FV_PAY_ACC_DIST_AMOUNT 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 , ap_system_parameters_all apsp, FV_EXTRACT_DETAIL_GT exgt 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 and apsp.org_id = ai.org_id and exgt.event_id = FTC.event_id and exgt.line_number = APHD.payment_hist_dist_id UNION SELECT FVC.event_id EVENT_ID, APHD.payment_hist_dist_id LINE_NUMBER, exgt.FUND_CATEGORY FEDERAL_FUND_CATEGORY, exgt.FUND_EXPIRED_STATUS FEDERAL_FUND_EXPIRED_STATUS, APHD.Accounting_Event_id , DECODE (exgt.FUND_CATEGORY,'A','Direct','B','Direct','C','Direct','R','Reimbursable','S','Reimbursable','T','Reimbursable') FEDERAL_FUND_CAT_DESC, exgt.ADJUSTMENT_TYPE FEDERAL_ADJUSTMENT_TYPE, FTC.treasury_confirmation_id, exgt.PRIOR_YEAR_FLAG FEDERAL_PRIOR_YEAR_FLAG, 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 , exgt.ENT_UNPAID_OBL_AMOUNT ENT_UNPAID_OBL_AMOUNT, FVOU.dit_confirm_cash_account , exgt.ACC_UNPAID_OBL_AMOUNT ACC_UNPAID_OBL_AMOUNT, AID.dist_code_combination_id , AID.amount INVOICE_DISTRIBUTION_AMOUNT, AID.distribution_line_number , exgt.ENT_ANTICIPATED_BUDGET_AMOUNT ENT_ANTICIPATED_BUDGET_AMOUNT, exgt.ACC_ANTICIPATED_BUDGET_AMOUNT ACC_ANTICIPATED_BUDGET_AMOUNT, AID.Line_type_lookup_code , exgt.ENT_UNANTICIPATED_BUD_AMOUNT ENT_UNANTICIPATED_BUD_AMOUNT, exgt.ACC_UNANTICIPATED_BUD_AMOUNT ACC_UNANTICIPATED_BUD_AMOUNT, AI.invoice_amount , AI.accts_pay_code_combination_id , AID.po_distribution_id , exgt.ENT_UNPAID_EXP_AMOUNT ENT_UNPAID_EXP_AMOUNT, exgt.ACC_UNPAID_EXP_AMOUNT ACC_UNPAID_EXP_AMOUNT, AI.Invoice_type_lookup_code , exgt.ENT_PAID_EXP_AMOUNT ENT_PAID_EXP_AMOUNT, exgt.ACC_PAID_EXP_AMOUNT ACC_PAID_EXP_AMOUNT, AI.Invoice_Date , exgt.ENT_PAID_OBL_AMOUNT ENT_PAID_OBL_AMOUNT, AI.Exchange_Date , exgt.ACC_PAID_OBL_AMOUNT ACC_PAID_OBL_AMOUNT, AI.Exchange_Rate , AI.Exchange_Rate_Type , exgt. ACC_EXPENDED_APPROP_AMOUNT ACC_EXPENDED_APPROP_AMOUNT, APHD.invoice_dist_amount , exgt. ENT_EXPENDED_APPROP_AMOUNT ENT_EXPENDED_APPROP_AMOUNT, exgt.ENT_PAID_EXP_PYA_AMOUNT ENT_PAID_EXP_PYA_AMOUNT, 200 BUS_FLOW_AP_APP_ID, exgt.ACC_PAID_EXP_PYA_AMOUNT ACC_PAID_EXP_PYA_AMOUNT, exgt.ENT_PAID_EXP_PYA_OFF_AMOUNT ENT_PAID_EXP_PYA_OFF_AMOUNT, 8901 BUS_FLOW_FV_APP_ID, exgt.ACC_PAID_EXP_PYA_OFF_AMOUNT ACC_PAID_EXP_PYA_OFF_AMOUNT, '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 FV_AP_CASH_ACCOUNT, AI.accts_pay_code_combination_id FV_AP_LIABILITY_ACCOUNT, AC.CHECK_DATE FV_CHECK_DATE , AC.CHECK_NUMBER FV_CHECK_NUMBER, AC.Treasury_pay_number FV_TREASURY_PAY_NUMBER, exgt.DIRECT_OR_REIMB FEDERAL_DIRECT_OR_REIMB, exgt.FUND_TYPE FEDERAL_FUND_TYPE, NVL(APHD.PAID_BASE_AMOUNT, APHD.amount) * -1 FV_PAY_ACC_DIST_AMOUNT 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 , FV_EXTRACT_DETAIL_GT exgt 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 and exgt.event_id = FVC.event_id and exgt.line_number = APHD.payment_hist_dist_id

View Text - HTML Formatted

SELECT FTC.EVENT_ID EVENT_ID
, APHD.PAYMENT_HIST_DIST_ID LINE_NUMBER
, EXGT.FUND_CATEGORY FEDERAL_FUND_CATEGORY
, EXGT.FUND_EXPIRED_STATUS FEDERAL_FUND_EXPIRED_STATUS
, APHD.ACCOUNTING_EVENT_ID FV_PAY_ACCOUNTING_EVENT_ID
, DECODE (EXGT.FUND_CATEGORY
, 'A'
, 'DIRECT'
, 'B'
, 'DIRECT'
, 'C'
, 'DIRECT'
, 'R'
, 'REIMBURSAB LE'
, 'S'
, 'REIMBURSABLE'
, 'T'
, 'REIMBURSABLE') FEDERAL_FUND_CAT_DESC
, EXGT.ADJUSTMENT_TYPE FEDERAL_ADJUSTMENT_TYPE
, FTC.TREASURY_CONFIRMATION_ID FV_TREASURY_CONFIRMATION_ID
, EXGT.PRIOR_YEAR_FLAG FEDERAL_PRIOR_YEAR_FLAG
, FTCC.CHECK_ID FV_PAY_CHECK_ID
, AI.INVOICE_CURRENCY_CODE FV_INV_CURRENCY_CODE
, APHD.AMOUNT FV_PAY_DIST_AMOUNT
, APHD.PAYMENT_HIST_DIST_ID FV_PAY_HIST_DIST_ID
, APHD.PAYMENT_HISTORY_ID FV_PAYMENT_HISTORY_ID
, APHD.INVOICE_DISTRIBUTION_ID FV_INV_DISTRIBUTION_ID
, APHD.INVOICE_PAYMENT_ID FV_INVOICE_PAYMENT_ID
, APHD.PAYMENT_HIST_DIST_ID FV_REV_PAY_HIST_DIST_ID
, APH.REV_PMT_HIST_ID FV_REV_PMT_HIST_ID
, CGAC.CASH_CLEARING_CCID CGAC_CASH_CLEARING_CCID
, EXGT.ENT_UNPAID_OBL_AMOUNT ENT_UNPAID_OBL_AMOUNT
, FVOU.DIT_CONFIRM_CASH_ACCOUNT DIT_CONFIRM_CASH_ACCOUNT
, EXGT.ACC_UNPAID_OBL_AMOUNT ACC_UNPAID_OBL_AMOUNT
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'INTEREST'
, DECODE(NVL(APSP.PRORATE_INT_INV_ACROSS_DISTS
, 'N')
, 'N'
, AID.ACCTS_PAY_CODE_COMBINATION_ID
, AID.DIST_CODE_COMBINATION_ID)
, AID.DIST_CODE_COMBINATION_ID)
, AID.AMOUNT INVOICE_DISTRIBUTION_AMOUNT
, AID.DISTRIBUTION_LINE_NUMBER FV_INV_DIST_LINE_NUMBER
, EXGT.ENT_ANTICIPATED_BUDGET_AMOUNT ENT_ANTICIPATED_BUDGET_AMOUNT
, EXGT.ACC_ANTICIPATED_BUDGET_AMOUNT ACC_ANTICIPATED_BUDGET_AMOUNT
, AID.LINE_TYPE_LOOKUP_CODE FV_INV_LINE_TYPE_LOOKUP_CODE
, EXGT.ENT_UNANTICIPATED_BUD_AMOUNT ENT_UNANTICIPATED_BUD_AMOUNT
, EXGT.ACC_UNANTICIPATED_BUD_AMOUNT ACC_UNANTICIPATED_BUD_AMOUNT
, AI.INVOICE_AMOUNT FV_INVOICE_AMOUNT
, AI.ACCTS_PAY_CODE_COMBINATION_ID FV_INV_ACCTS_PAY_CCID
, AID.PO_DISTRIBUTION_ID FV_PO_DISTRIBUTION_ID
, EXGT.ENT_UNPAID_EXP_AMOUNT ENT_UNPAID_EXP_AMOUNT
, EXGT.ACC_UNPAID_EXP_AMOUNT ACC_UNPAID_EXP_AMOUNT
, AI.INVOICE_TYPE_LOOKUP_CODE FV_INV_TYPE_LOOKUP_CODE
, EXGT.ENT_PAID_EXP_AMOUNT ENT_PAID_EXP_AMOUNT
, EXGT.ACC_PAID_EXP_AMOUNT ACC_PAID_EXP_AMOUNT
, AI.INVOICE_DATE FV_INVOICE_DATE
, EXGT.ENT_PAID_OBL_AMOUNT ENT_PAID_OBL_AMOUNT
, AI.EXCHANGE_DATE FV_INV_EXCHANGE_DATE
, EXGT.ACC_PAID_OBL_AMOUNT ACC_PAID_OBL_AMOUNT
, AI.EXCHANGE_RATE FV_INV_EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE FV_INV_EXCHANGE_RATE_TYPE
, EXGT.ACC_EXPENDED_APPROP_AMOUNT ACC_EXPENDED_APPROP_AMOUNT
, APHD.INVOICE_DIST_AMOUNT FV_APHD_INV_DIST_AMOUNT
, EXGT.ENT_EXPENDED_APPROP_AMOUNT ENT_EXPENDED_APPROP_AMOUNT
, EXGT.ENT_PAID_EXP_PYA_AMOUNT ENT_PAID_EXP_PYA_AMOUNT
, 200 BUS_FLOW_AP_APP_ID
, EXGT.ACC_PAID_EXP_PYA_AMOUNT ACC_PAID_EXP_PYA_AMOUNT
, EXGT.ENT_PAID_EXP_PYA_OFF_AMOUNT ENT_PAID_EXP_PYA_OFF_AMOUNT
, 8901 BUS_FLOW_FV_APP_ID
, EXGT.ACC_PAID_EXP_PYA_OFF_AMOUNT ACC_PAID_EXP_PYA_OFF_AMOUNT
, '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 FV_AP_CASH_ACCOUNT
, AI.ACCTS_PAY_CODE_COMBINATION_ID FV_AP_LIABILITY_ACCOUNT
, AC.CHECK_DATE FV_CHECK_DATE
, AC.CHECK_NUMBER FV_CHECK_NUMBER
, AC.TREASURY_PAY_NUMBER FV_TREASURY_PAY_NUMBER
, EXGT.DIRECT_OR_REIMB FEDERAL_DIRECT_OR_REIMB
, EXGT.FUND_TYPE FEDERAL_FUND_TYPE
, NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT) FV_PAY_ACC_DIST_AMOUNT
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
, AP_SYSTEM_PARAMETERS_ALL APSP
, FV_EXTRACT_DETAIL_GT EXGT
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
AND APSP.ORG_ID = AI.ORG_ID
AND EXGT.EVENT_ID = FTC.EVENT_ID
AND EXGT.LINE_NUMBER = APHD.PAYMENT_HIST_DIST_ID UNION SELECT FVC.EVENT_ID EVENT_ID
, APHD.PAYMENT_HIST_DIST_ID LINE_NUMBER
, EXGT.FUND_CATEGORY FEDERAL_FUND_CATEGORY
, EXGT.FUND_EXPIRED_STATUS FEDERAL_FUND_EXPIRED_STATUS
, APHD.ACCOUNTING_EVENT_ID
, DECODE (EXGT.FUND_CATEGORY
, 'A'
, 'DIRECT'
, 'B'
, 'DIRECT'
, 'C'
, 'DIRECT'
, 'R'
, 'REIMBURSABLE'
, 'S'
, 'REIMBURSABLE'
, 'T'
, 'REIMBURSABLE') FEDERAL_FUND_CAT_DESC
, EXGT.ADJUSTMENT_TYPE FEDERAL_ADJUSTMENT_TYPE
, FTC.TREASURY_CONFIRMATION_ID
, EXGT.PRIOR_YEAR_FLAG FEDERAL_PRIOR_YEAR_FLAG
, 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
, EXGT.ENT_UNPAID_OBL_AMOUNT ENT_UNPAID_OBL_AMOUNT
, FVOU.DIT_CONFIRM_CASH_ACCOUNT
, EXGT.ACC_UNPAID_OBL_AMOUNT ACC_UNPAID_OBL_AMOUNT
, AID.DIST_CODE_COMBINATION_ID
, AID.AMOUNT INVOICE_DISTRIBUTION_AMOUNT
, AID.DISTRIBUTION_LINE_NUMBER
, EXGT.ENT_ANTICIPATED_BUDGET_AMOUNT ENT_ANTICIPATED_BUDGET_AMOUNT
, EXGT.ACC_ANTICIPATED_BUDGET_AMOUNT ACC_ANTICIPATED_BUDGET_AMOUNT
, AID.LINE_TYPE_LOOKUP_CODE
, EXGT.ENT_UNANTICIPATED_BUD_AMOUNT ENT_UNANTICIPATED_BUD_AMOUNT
, EXGT.ACC_UNANTICIPATED_BUD_AMOUNT ACC_UNANTICIPATED_BUD_AMOUNT
, AI.INVOICE_AMOUNT
, AI.ACCTS_PAY_CODE_COMBINATION_ID
, AID.PO_DISTRIBUTION_ID
, EXGT.ENT_UNPAID_EXP_AMOUNT ENT_UNPAID_EXP_AMOUNT
, EXGT.ACC_UNPAID_EXP_AMOUNT ACC_UNPAID_EXP_AMOUNT
, AI.INVOICE_TYPE_LOOKUP_CODE
, EXGT.ENT_PAID_EXP_AMOUNT ENT_PAID_EXP_AMOUNT
, EXGT.ACC_PAID_EXP_AMOUNT ACC_PAID_EXP_AMOUNT
, AI.INVOICE_DATE
, EXGT.ENT_PAID_OBL_AMOUNT ENT_PAID_OBL_AMOUNT
, AI.EXCHANGE_DATE
, EXGT.ACC_PAID_OBL_AMOUNT ACC_PAID_OBL_AMOUNT
, AI.EXCHANGE_RATE
, AI.EXCHANGE_RATE_TYPE
, EXGT. ACC_EXPENDED_APPROP_AMOUNT ACC_EXPENDED_APPROP_AMOUNT
, APHD.INVOICE_DIST_AMOUNT
, EXGT. ENT_EXPENDED_APPROP_AMOUNT ENT_EXPENDED_APPROP_AMOUNT
, EXGT.ENT_PAID_EXP_PYA_AMOUNT ENT_PAID_EXP_PYA_AMOUNT
, 200 BUS_FLOW_AP_APP_ID
, EXGT.ACC_PAID_EXP_PYA_AMOUNT ACC_PAID_EXP_PYA_AMOUNT
, EXGT.ENT_PAID_EXP_PYA_OFF_AMOUNT ENT_PAID_EXP_PYA_OFF_AMOUNT
, 8901 BUS_FLOW_FV_APP_ID
, EXGT.ACC_PAID_EXP_PYA_OFF_AMOUNT ACC_PAID_EXP_PYA_OFF_AMOUNT
, '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 FV_AP_CASH_ACCOUNT
, AI.ACCTS_PAY_CODE_COMBINATION_ID FV_AP_LIABILITY_ACCOUNT
, AC.CHECK_DATE FV_CHECK_DATE
, AC.CHECK_NUMBER FV_CHECK_NUMBER
, AC.TREASURY_PAY_NUMBER FV_TREASURY_PAY_NUMBER
, EXGT.DIRECT_OR_REIMB FEDERAL_DIRECT_OR_REIMB
, EXGT.FUND_TYPE FEDERAL_FUND_TYPE
, NVL(APHD.PAID_BASE_AMOUNT
, APHD.AMOUNT) * -1 FV_PAY_ACC_DIST_AMOUNT
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
, FV_EXTRACT_DETAIL_GT EXGT
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
AND EXGT.EVENT_ID = FVC.EVENT_ID
AND EXGT.LINE_NUMBER = APHD.PAYMENT_HIST_DIST_ID