FV_TREASURY_EXTRACT_DETAILS

Details

Schema: FUSION
Object owner: FV
Object type: VIEW

Columns

Name
EVENT_ID TREASURY_CONFIRMATION_ID TC_AIL_LINE_NUMBER LINE_NUMBER LINE_TYPE VOID_PAYMENT_FLAG CHECK_NUMBER CHECK_DATE TREASURY_PAY_NUMBER DIST_CODE_COMBINATION_ID ACC_TC_PAYMENT_AMOUNT ENT_TC_PAYMENT_AMOUNT INVOICE_AMOUNT FUND_VALUE TREASURY_ACCOUNT_SYMBOL EXPIRATION_DATE FEDERAL_PRIOR_YEAR_FLAG PREPAYMENT_FLAG TAS_EXPIRED_STATUS_CODE FUNDING_DERIVATION_TYPE_CODE TC_PAY_CURRENCY_CODE FED_LEDGER_ID FV_AP_ASSET_CCID FV_AP_CASH_CLEARING_CCID

Name

EVENT_ID

TREASURY_CONFIRMATION_ID

TC_AIL_LINE_NUMBER

LINE_NUMBER

LINE_TYPE

VOID_PAYMENT_FLAG

CHECK_NUMBER

CHECK_DATE

TREASURY_PAY_NUMBER

DIST_CODE_COMBINATION_ID

ACC_TC_PAYMENT_AMOUNT

ENT_TC_PAYMENT_AMOUNT

INVOICE_AMOUNT

FUND_VALUE

TREASURY_ACCOUNT_SYMBOL

EXPIRATION_DATE

FEDERAL_PRIOR_YEAR_FLAG

PREPAYMENT_FLAG

TAS_EXPIRED_STATUS_CODE

FUNDING_DERIVATION_TYPE_CODE

TC_PAY_CURRENCY_CODE

FED_LEDGER_ID

FV_AP_ASSET_CCID

FV_AP_CASH_CLEARING_CCID

Query

SQL_Statement
SELECT FTA.CONFIRMED_EVENT_ID "EVENT_ID", FTA.TREASURY_CONFIRMATION_ID, AIDA.INVOICE_LINE_NUMBER "TC_AIL_LINE_NUMBER", AIDA.DISTRIBUTION_LINE_NUMBER "LINE_NUMBER", AIDA.LINE_TYPE_LOOKUP_CODE "LINE_TYPE", CASE WHEN ACA.VOID_DATE IS NULL THEN 'N' WHEN ACA.VOID_DATE IS NOT NULL THEN 'Y' END "VOID_PAYMENT_FLAG", ACA.CHECK_NUMBER, ACA.CHECK_DATE, ACA.TREASURY_PAY_NUMBER, AIDA.DIST_CODE_COMBINATION_ID, APHD.AMOUNT * CASE WHEN XE.EVENT_TYPE_CODE ='TREASURY_CONFIRM' THEN '1' WHEN XE.EVENT_TYPE_CODE='TREASURY_BACKOUT' THEN '-1' END "ACC_TC_PAYMENT_AMOUNT", APHD.AMOUNT * CASE WHEN XE.EVENT_TYPE_CODE ='TREASURY_CONFIRM' THEN '1' WHEN XE.EVENT_TYPE_CODE='TREASURY_BACKOUT' THEN '-1' END "ENT_TC_PAYMENT_AMOUNT", AIDA.AMOUNT "INVOICE_AMOUNT", FTFA.FUND_CODE "FUND_VALUE", FTTAS.TREASURY_ACCOUNT_SYMBOL, FTTAS.EXPIRATION_DATE, DECODE(FTTAS.PRIOR_YEAR_ADJUSTMENT_FLAG,'Y',CASE WHEN GCC.SEGMENT7<EXTRACT(YEAR FROM FTA.PAYMENT_DATE) THEN 'Y' ELSE 'N' END,'N') FEDERAL_PRIOR_YEAR_FLAG, DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT','Y','N') "PREPAYMENT_FLAG", CASE WHEN FTTAS.EXPIRATION_DATE IS NULL THEN 'Unexpired' WHEN FTTAS.EXPIRATION_DATE >= SYSDATE THEN 'Unexpired' WHEN FTTAS.EXPIRATION_DATE < SYSDATE THEN 'Expired' END "TAS_EXPIRED_STATUS_CODE", FTFA.FUNDING_DERIVATION_TYPE_CODE "FUNDING_DERIVATION_TYPE_CODE", AIPA.INVOICE_CURRENCY_CODE "TC_PAY_CURRENCY_CODE", AIPA.SET_OF_BOOKS_ID "FED_LEDGER_ID", CBA.ASSET_CODE_COMBINATION_ID "FV_AP_ASSET_CCID", CBA.CASH_CLEARING_CCID "FV_AP_CASH_CLEARING_CCID" FROM AP_CHECKS_ALL ACA, AP_INVOICE_PAYMENTS_ALL AIPA, AP_INVOICE_DISTRIBUTIONS_ALL AIDA, AP_PAYMENT_HIST_DISTS APHD, GL_CODE_COMBINATIONS GCC, FV_TR_TREAS_ACCOUNT_SYMBOLS FTTAS, FV_TR_FUND_ATTRIBUTES FTFA, AP_INVOICES_ALL AIA, CE_BANK_ACCOUNTS CBA, IBY_PAYMENTS_ALL IPA, FUN_ALL_BUSINESS_UNITS_V FABU, XLA_EVENTS_GT XE INNER JOIN FV_TREASURY_CONFIRM_ALL FTA ON XE.EVENT_ID = FTA.CONFIRMED_EVENT_ID WHERE ACA.CHECK_ID=AIPA.CHECK_ID AND AIPA.INVOICE_ID=AIDA.INVOICE_ID AND FTA.PAYMENT_INSTRUCTION_ID=ACA.PAYMENT_INSTRUCTION_ID AND APHD.INVOICE_DISTRIBUTION_ID=AIDA.INVOICE_DISTRIBUTION_ID AND APHD.INVOICE_PAYMENT_ID=AIPA.INVOICE_PAYMENT_ID AND GCC.CODE_COMBINATION_ID=AIDA.DIST_CODE_COMBINATION_ID AND GCC.SEGMENT1=FTFA.FUND_CODE AND FTFA.TREASURY_ACCOUNT_SYMBOL_ID=FTTAS.TREASURY_ACCOUNT_SYMBOL_ID AND AIA.INVOICE_ID=AIPA.INVOICE_ID AND IPA.PAYMENT_INSTRUCTION_ID=FTA.PAYMENT_INSTRUCTION_ID AND IPA.PAPER_DOCUMENT_NUMBER=ACA.CHECK_NUMBER AND IPA.INTERNAL_BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID AND FABU.BU_ID=FTA.BU_ID AND FABU.PRIMARY_LEDGER_ID=FTFA.LEDGER_ID

SQL_Statement

SELECT FTA.CONFIRMED_EVENT_ID "EVENT_ID",

FTA.TREASURY_CONFIRMATION_ID,

AIDA.INVOICE_LINE_NUMBER "TC_AIL_LINE_NUMBER",

AIDA.DISTRIBUTION_LINE_NUMBER "LINE_NUMBER",

AIDA.LINE_TYPE_LOOKUP_CODE "LINE_TYPE",

CASE WHEN ACA.VOID_DATE IS NULL THEN 'N'

WHEN ACA.VOID_DATE IS NOT NULL THEN 'Y' END "VOID_PAYMENT_FLAG",

ACA.CHECK_NUMBER,

ACA.CHECK_DATE,

ACA.TREASURY_PAY_NUMBER,

AIDA.DIST_CODE_COMBINATION_ID,

APHD.AMOUNT * CASE WHEN XE.EVENT_TYPE_CODE ='TREASURY_CONFIRM' THEN '1'

WHEN XE.EVENT_TYPE_CODE='TREASURY_BACKOUT' THEN '-1' END "ACC_TC_PAYMENT_AMOUNT",

APHD.AMOUNT * CASE WHEN XE.EVENT_TYPE_CODE ='TREASURY_CONFIRM' THEN '1'

WHEN XE.EVENT_TYPE_CODE='TREASURY_BACKOUT' THEN '-1' END "ENT_TC_PAYMENT_AMOUNT",

AIDA.AMOUNT "INVOICE_AMOUNT",

FTFA.FUND_CODE "FUND_VALUE",

FTTAS.TREASURY_ACCOUNT_SYMBOL,

FTTAS.EXPIRATION_DATE,

DECODE(FTTAS.PRIOR_YEAR_ADJUSTMENT_FLAG,'Y',CASE WHEN GCC.SEGMENT7<EXTRACT(YEAR FROM FTA.PAYMENT_DATE) THEN 'Y' ELSE 'N' END,'N') FEDERAL_PRIOR_YEAR_FLAG,

DECODE(AIA.INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT','Y','N') "PREPAYMENT_FLAG",

CASE WHEN FTTAS.EXPIRATION_DATE IS NULL THEN 'Unexpired'

WHEN FTTAS.EXPIRATION_DATE >= SYSDATE THEN 'Unexpired'

WHEN FTTAS.EXPIRATION_DATE < SYSDATE THEN 'Expired' END "TAS_EXPIRED_STATUS_CODE",

FTFA.FUNDING_DERIVATION_TYPE_CODE "FUNDING_DERIVATION_TYPE_CODE",

AIPA.INVOICE_CURRENCY_CODE "TC_PAY_CURRENCY_CODE",

AIPA.SET_OF_BOOKS_ID "FED_LEDGER_ID",

CBA.ASSET_CODE_COMBINATION_ID "FV_AP_ASSET_CCID",

CBA.CASH_CLEARING_CCID "FV_AP_CASH_CLEARING_CCID"

FROM AP_CHECKS_ALL ACA,

AP_INVOICE_PAYMENTS_ALL AIPA,

AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

AP_PAYMENT_HIST_DISTS APHD,

GL_CODE_COMBINATIONS GCC,

FV_TR_TREAS_ACCOUNT_SYMBOLS FTTAS,

FV_TR_FUND_ATTRIBUTES FTFA,

AP_INVOICES_ALL AIA,

CE_BANK_ACCOUNTS CBA,

IBY_PAYMENTS_ALL IPA,

FUN_ALL_BUSINESS_UNITS_V FABU,

XLA_EVENTS_GT XE INNER JOIN

FV_TREASURY_CONFIRM_ALL FTA

ON XE.EVENT_ID = FTA.CONFIRMED_EVENT_ID

WHERE ACA.CHECK_ID=AIPA.CHECK_ID

AND AIPA.INVOICE_ID=AIDA.INVOICE_ID

AND FTA.PAYMENT_INSTRUCTION_ID=ACA.PAYMENT_INSTRUCTION_ID

AND APHD.INVOICE_DISTRIBUTION_ID=AIDA.INVOICE_DISTRIBUTION_ID

AND APHD.INVOICE_PAYMENT_ID=AIPA.INVOICE_PAYMENT_ID

AND GCC.CODE_COMBINATION_ID=AIDA.DIST_CODE_COMBINATION_ID

AND GCC.SEGMENT1=FTFA.FUND_CODE

AND FTFA.TREASURY_ACCOUNT_SYMBOL_ID=FTTAS.TREASURY_ACCOUNT_SYMBOL_ID

AND AIA.INVOICE_ID=AIPA.INVOICE_ID

AND IPA.PAYMENT_INSTRUCTION_ID=FTA.PAYMENT_INSTRUCTION_ID

AND IPA.PAPER_DOCUMENT_NUMBER=ACA.CHECK_NUMBER

AND IPA.INTERNAL_BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID

AND FABU.BU_ID=FTA.BU_ID

AND FABU.PRIMARY_LEDGER_ID=FTFA.LEDGER_ID