1. Tables and Views for Financials
  2. XLA_XCC_JRNL_EXTRACT_V

XLA_XCC_JRNL_EXTRACT_V

Details

  • Schema: FUSION

  • Object owner: XLA

  • Object type: VIEW

Columns

Name

DATA_SET_ID

SOURCE_HEADER_ID_1

SOURCE_HEADER_ID_2

TRANSACTION_NUMBER

SOURCE_LINE_ID_1

SOURCE_LINE_ID_2

SOURCE_LINE_ID_3

SOURCE_LINE_ID_4

SOURCE_LINE_ID_5

SOURCE_LINE_ID_6

LINE_GROUP_CODE

LINE_NUM

LEDGER_ID

BUSINESS_UNIT_ID

DESTINATION_TYPE_CODE

DOCUMENT_TYPE_CODE

TRANSACTION_SOURCE_CODE

TRANSACTION_SUBTYPE_CODE

JE_SOURCE_CODE

APPLICATION_ID

JE_CATEGORY_CODE

BUDGET_DATE

ACCOUNTING_DATE

ENCUMBRANCE_TYPE_CODE

ENTERED_CURRENCY

UOM_CODE

QUANTITY

PRICE

ENTERED_AMOUNT

CONVERSION_TYPE_CODE

CONVERSION_DATE

LEDGER_AMOUNT

LIQUIDATION_DATE

LIQUIDATION_QUANTITY

LIQUIDATION_AMOUNT

LIQUIDATION_TRANS_TYPE_CODE

LIQUIDATION_LINE_ID_1

LIQUIDATION_LINE_ID_2

LIQUIDATION_LINE_ID_3

LIQUIDATION_LINE_ID_4

LIQUIDATION_LINE_ID_5

LIQUIDATION_LINE_ID_6

STATISTICAL_AMOUNT

BUDGET_CCID

VENDOR_ID

INVENTORY_ITEM_ID

ORDER_TYPE_INFO

CODE_COMBINATION_ID

PJC_PROJECT_ID

PJC_TASK_ID

PJC_RESOURCE_ID

PJC_CONTRACT_ID

PJC_CONTRACT_LINE_ID

PJC_FUNDING_ALLOCATION_ID

PJC_BILLABLE_FLAG

PJC_CAPITALIZABLE_FLAG

PJC_EXPENDITURE_TYPE_ID

PJC_EXPENDITURE_ITEM_DATE

PJC_ORGANIZATION_ID

PJC_WORK_TYPE_ID

CONTROL_BUDGET_ID

Query

SQL_Statement

SELECT

GT.DATA_SET_ID DATA_SET_ID,

TO_CHAR(JournalHeader.AE_HEADER_ID) SOURCE_HEADER_ID_1,

TO_CHAR(JournalHeader.APPLICATION_ID) SOURCE_HEADER_ID_2,

SUBSTR(JournalHeader.DESCRIPTION,1,30) TRANSACTION_NUMBER,

TO_CHAR(JournalLine.AE_HEADER_ID) SOURCE_LINE_ID_1,

TO_CHAR(JournalLine.APPLICATION_ID) SOURCE_LINE_ID_2,

TO_CHAR(JournalLine.AE_LINE_NUM) SOURCE_LINE_ID_3,

' ' SOURCE_LINE_ID_4,

' ' SOURCE_LINE_ID_5,

' ' SOURCE_LINE_ID_6,

' ' LINE_GROUP_CODE,

null LINE_NUM,

JOURNALHEADER.LEDGER_ID LEDGER_ID,

null BUSINESS_UNIT_ID,

' ' DESTINATION_TYPE_CODE,

' ' DOCUMENT_TYPE_CODE,

' ' TRANSACTION_SOURCE_CODE,

' ' TRANSACTION_SUBTYPE_CODE,

SUBLEDGER.JE_SOURCE_NAME JE_SOURCE_CODE,

JOURNALHEADER.APPLICATION_ID APPLICATION_ID,

JOURNALHEADER.JE_CATEGORY_NAME JE_CATEGORY_CODE,

JournalHeader.ACCOUNTING_DATE BUDGET_DATE,

JournalHeader.ACCOUNTING_DATE ACCOUNTING_DATE,

EncTypes.ENCUMBRANCE_TYPE_CODE ENCUMBRANCE_TYPE_CODE,

JournalLine.CURRENCY_CODE ENTERED_CURRENCY,

TO_CHAR(NULL) UOM_CODE,

null QUANTITY,

null PRICE, DECODE(CODECOMBINATION.ACCOUNT_TYPE,'A',NVL(JOURNALLINE.ENTERED_DR,0) - NVL(JOURNALLINE.ENTERED_CR,0),'E',NVL(JOURNALLINE.ENTERED_DR,0) - NVL(JOURNALLINE.ENTERED_CR,0),nvl(JOURNALLINE.ENTERED_CR,0) - nvl(JOURNALLINE.ENTERED_DR,0)) ENTERED_AMOUNT, DECODE(JOURNALLINE.CURRENCY_CONVERSION_TYPE,'User',null,JOURNALLINE.CURRENCY_CONVERSION_TYPE) CONVERSION_TYPE_CODE, JOURNALLINE.CURRENCY_CONVERSION_DATE CONVERSION_DATE, DECODE(CODECOMBINATION.ACCOUNT_TYPE,'A',NVL(JOURNALLINE.ACCOUNTED_DR,0) - NVL(JOURNALLINE.ACCOUNTED_CR,0),'E',NVL(JOURNALLINE.ACCOUNTED_DR,0) - NVL(JOURNALLINE.ACCOUNTED_CR,0),nvl(JOURNALLINE.ACCOUNTED_CR,0) - nvl(JOURNALLINE.ACCOUNTED_DR,0)) LEDGER_AMOUNT,

null LIQUIDATION_DATE,

null LIQUIDATION_QUANTITY,

null LIQUIDATION_AMOUNT,

' ' LIQUIDATION_TRANS_TYPE_CODE,

' ' LIQUIDATION_LINE_ID_1,

' ' LIQUIDATION_LINE_ID_2,

' ' LIQUIDATION_LINE_ID_3,

' ' LIQUIDATION_LINE_ID_4,

' ' LIQUIDATION_LINE_ID_5,

' ' LIQUIDATION_LINE_ID_6,

JournalLine.statistical_amount STATISTICAL_AMOUNT,

null BUDGET_CCID,

null VENDOR_ID,

null INVENTORY_ITEM_ID,

' ' ORDER_TYPE_INFO,

JOURNALLINE.CODE_COMBINATION_ID CODE_COMBINATION_ID,

null PJC_PROJECT_ID,

null PJC_TASK_ID,

null PJC_RESOURCE_ID,

null PJC_CONTRACT_ID,

null PJC_CONTRACT_LINE_ID,

null PJC_FUNDING_ALLOCATION_ID,

' ' PJC_BILLABLE_FLAG,

' ' PJC_CAPITALIZABLE_FLAG,

null PJC_EXPENDITURE_TYPE_ID,

null PJC_EXPENDITURE_ITEM_DATE,

null PJC_ORGANIZATION_ID, null PJC_WORK_TYPE_ID,null CONTROL_BUDGET_ID

FROM XLA_AE_HEADERS JournalHeader , XLA_AE_LINES JournalLine, XLA_SUBLEDGERS_VL Subledger, XLA_XCC_EXTRACT_GT GT, GL_ENCUMBRANCE_TYPES_B ENCTYPES, GL_CODE_COMBINATIONS CODECOMBINATION

WHERE GT.AE_HEADER_ID = JOURNALHEADER.AE_HEADER_ID AND JOURNALHEADER.AE_HEADER_ID = JOURNALLINE.AE_HEADER_ID AND JOURNALHEADER.APPLICATION_ID = SUBLEDGER.APPLICATION_ID AND ENCTYPES.ENCUMBRANCE_TYPE_ID (+) = JOURNALLINE.ENCUMBRANCE_TYPE_ID AND CODECOMBINATION. CODE_COMBINATION_ID = JOURNALLINE.CODE_COMBINATION_ID