JV_CI_JOURNAL_LINE

Details

Schema: FUSION
Object owner: JV
Object type: VIEW

Columns

Name
LINE_NUMBER CI_DISTRIBUTION_ID CI_DISTRIBUTION_TYPE_CODE CI_DISTRIBUTION_LINE_TYPE_CODE CARRIED_AMOUNT CONSENTING_STAKEHOLDER_ID CS_STAKEHOLDER_SHORT_NAME CS_PRIMARY_SEGMENT_VALUE DEFAULT_CI_ACCOUNT CONSENTING_PERCENTAGE NON_CONSENTING_STAKEHOLDER_ID NCS_STAKEHOLDER_SHORT_NAME CURRENCY_CODE EVENT_ID TRANSACTION_ID TRANSACTION_DATE APPLICATION_ID SOURCE_NAME CATEGORY_NAME TRANSACTION_DESCRIPTION TRANSACTION_ACCOUNT ORIGINAL_AMOUNT PROJECT_ID TASK_ID EXPENDITURE_TYPE_ID EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ITEM_DATE

Name

LINE_NUMBER

CI_DISTRIBUTION_ID

CI_DISTRIBUTION_TYPE_CODE

CI_DISTRIBUTION_LINE_TYPE_CODE

CARRIED_AMOUNT

CONSENTING_STAKEHOLDER_ID

CS_STAKEHOLDER_SHORT_NAME

CS_PRIMARY_SEGMENT_VALUE

DEFAULT_CI_ACCOUNT

CONSENTING_PERCENTAGE

NON_CONSENTING_STAKEHOLDER_ID

NCS_STAKEHOLDER_SHORT_NAME

CURRENCY_CODE

EVENT_ID

TRANSACTION_ID

TRANSACTION_DATE

APPLICATION_ID

SOURCE_NAME

CATEGORY_NAME

TRANSACTION_DESCRIPTION

TRANSACTION_ACCOUNT

ORIGINAL_AMOUNT

PROJECT_ID

TASK_ID

EXPENDITURE_TYPE_ID

EXPENDITURE_ORGANIZATION_ID

EXPENDITURE_ITEM_DATE

Query

SQL_Statement
SELECT CI.CI_DISTRIBUTION_ID LINE_NUMBER, CI.CI_DISTRIBUTION_ID CI_DISTRIBUTION_ID, CI.ACCOUNT_TYPE_CODE CI_DISTRIBUTION_TYPE_CODE, CI.CI_DISTRIBUTION_LINE_TYPE_CODE CI_DISTRIBUTION_LINE_TYPE_CODE, CASE WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) ELSE NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) END CARRIED_AMOUNT, CI.CONSENTING_STAKEHOLDER_ID CONSENTING_STAKEHOLDER_ID, CS.STAKEHOLDER_SHORT_NAME CS_STAKEHOLDER_SHORT_NAME, CS.COST_CENTER_SEGMENT_VALUE CS_PRIMARY_SEGMENT_VALUE, CG.DEFAULT_CI_ACCOUNT_CCID DEFAULT_CI_ACCOUNT, CI.CONSENTING_PERCENTAGE CONSENTING_PERCENTAGE, CI.NON_CONSENTING_STAKEHOLDER_ID NON_CONSENTING_STAKEHOLDER_ID, NS.STAKEHOLDER_SHORT_NAME NCS_STAKEHOLDER_SHORT_NAME, CI.ACCOUNTED_CURRENCY_CODE CURRENCY_CODE, JA.EVENT_ID EVENT_ID, JT.TRANSACTION_ID TRANSACTION_ID, JT.TRANSACTION_DATE TRANSACTION_DATE, JT.APPLICATION_ID APPLICATION_ID, CASE WHEN XH.APPLICATION_ID = 140 THEN 'Assets' WHEN XH.APPLICATION_ID = 200 THEN 'Payables' WHEN XH.APPLICATION_ID = 201 THEN 'Purchasing' WHEN XH.APPLICATION_ID = 222 THEN 'Receivables' WHEN XH.APPLICATION_ID = 235 THEN 'Tax' WHEN XH.APPLICATION_ID = 260 THEN 'Cash Management' WHEN XH.APPLICATION_ID = 435 THEN 'Global Intercompany' WHEN XH.APPLICATION_ID = 602 THEN 'Other' WHEN XH.APPLICATION_ID = 707 THEN 'Cost Accounting' WHEN XH.APPLICATION_ID = 801 THEN 'Payroll' WHEN XH.APPLICATION_ID = 8901 THEN 'US Federal' WHEN XH.APPLICATION_ID = 10016 THEN 'Expenses' WHEN XH.APPLICATION_ID = 10036 THEN 'Project Accounting' WHEN XH.APPLICATION_ID = 10052 THEN 'Budgetary Control' WHEN XH.APPLICATION_ID = 10096 THEN 'Receipt Accounting' WHEN XH.APPLICATION_ID = 10455 THEN 'Revenue Management' WHEN XH.APPLICATION_ID = 10566 THEN 'Channel Revenue' WHEN XH.APPLICATION_ID = 10568 THEN 'Joint Ventures' WHEN XH.APPLICATION_ID = 10571 THEN 'PS Compliance' WHEN XH.APPLICATION_ID = 10572 THEN 'Lease Accounting' END SOURCE_NAME, GC.JE_CATEGORY_KEY CATEGORY_NAME, XL.DESCRIPTION TRANSACTION_DESCRIPTION, XL.CODE_COMBINATION_ID TRANSACTION_ACCOUNT, CASE WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN NVL(XL.ACCOUNTED_CR, 0) - NVL(XL.ACCOUNTED_DR, 0) ELSE NVL(XL.ACCOUNTED_DR, 0) - NVL(XL.ACCOUNTED_CR ,0) END ORIGINAL_AMOUNT, JT.PROJECT_ID PROJECT_ID, JT.TASK_ID TASK_ID, JT.EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID, JT.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID, JT.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE FROM JV_CI_DISTRIBUTIONS CI, JV_TRANSACTIONS JT, JV_ACCOUNTING_HEADERS JA, XLA_AE_HEADERS XH, XLA_AE_LINES XL, GL_JE_CATEGORIES_B GC, JV_STAKEHOLDERS_B CS, JV_STAKEHOLDERS_B NS, JV_CI_GROUP_LINES CG, JV_OWNER_PERCENTS_F_B OP WHERE JT.TRANSACTION_ID = CI.TRANSACTION_ID AND JA.ACCOUNTING_HEADER_ID = CI.ACCOUNTING_HEADER_ID AND XH.AE_HEADER_ID = JT.HEADER_ID AND XH.APPLICATION_ID = JT.APPLICATION_ID AND XL.AE_HEADER_ID = JT.HEADER_ID AND XL.APPLICATION_ID = JT.APPLICATION_ID AND XL.AE_LINE_NUM = JT.LINE_NUM AND GC.JE_CATEGORY_NAME = XH.JE_CATEGORY_NAME AND CS.STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND NS.STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND OP.OWNERSHIP_PERCENTAGE_ID = CI.CI_OWNERSHIP_ID AND CG.STAKEHOLDER_GROUP_ID = OP.STAKEHOLDER_GROUP_ID AND CG.NON_CONSENTING_STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND CG.CONSENTING_STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND JT.APPLICATION_ID > 0 UNION ALL SELECT CI.CI_DISTRIBUTION_ID LINE_NUMBER, CI.CI_DISTRIBUTION_ID CI_DISTRIBUTION_ID, CI.ACCOUNT_TYPE_CODE CI_DISTRIBUTION_TYPE_CODE, CI.CI_DISTRIBUTION_LINE_TYPE_CODE CI_DISTRIBUTION_LINE_TYPE_CODE, CASE WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) ELSE NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) END CARRIED_AMOUNT, CI.CONSENTING_STAKEHOLDER_ID CONSENTING_STAKEHOLDER_ID, CS.STAKEHOLDER_SHORT_NAME CS_STAKEHOLDER_SHORT_NAME, CS.COST_CENTER_SEGMENT_VALUE CS_PRIMARY_SEGMENT_VALUE, CG.DEFAULT_CI_ACCOUNT_CCID DEFAULT_CI_ACCOUNT, CI.CONSENTING_PERCENTAGE CONSENTING_PERCENTAGE, CI.NON_CONSENTING_STAKEHOLDER_ID NON_CONSENTING_STAKEHOLDER_ID, NS.STAKEHOLDER_SHORT_NAME NCS_STAKEHOLDER_SHORT_NAME, CI.ACCOUNTED_CURRENCY_CODE CURRENCY_CODE, JA.EVENT_ID EVENT_ID, JT.TRANSACTION_ID TRANSACTION_ID, JT.TRANSACTION_DATE TRANSACTION_DATE, JT.APPLICATION_ID APPLICATION_ID, GH.JE_SOURCE SOURCE_NAME, GC.JE_CATEGORY_KEY CATEGORY_NAME, GL.DESCRIPTION TRANSACTION_DESCRIPTION, GL.CODE_COMBINATION_ID TRANSACTION_ACCOUNT, CASE WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN NVL(GL.ACCOUNTED_CR, 0) - NVL(GL.ACCOUNTED_DR, 0) ELSE NVL(GL.ACCOUNTED_DR, 0) - NVL(GL.ACCOUNTED_CR ,0) END ORIGINAL_AMOUNT, JT.PROJECT_ID PROJECT_ID, JT.TASK_ID TASK_ID, JT.EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID, JT.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID, JT.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE FROM JV_CI_DISTRIBUTIONS CI, JV_TRANSACTIONS JT, JV_ACCOUNTING_HEADERS JA, GL_JE_HEADERS GH, GL_JE_LINES GL, GL_JE_CATEGORIES_B GC, JV_STAKEHOLDERS_B CS, JV_STAKEHOLDERS_B NS, JV_CI_GROUP_LINES CG, JV_OWNER_PERCENTS_F_B OP WHERE JT.TRANSACTION_ID = CI.TRANSACTION_ID AND JA.ACCOUNTING_HEADER_ID = CI.ACCOUNTING_HEADER_ID AND JT.HEADER_ID = GH.JE_HEADER_ID AND JT.HEADER_ID = GL.JE_HEADER_ID AND JT.LINE_NUM = GL.JE_LINE_NUM AND GC.JE_CATEGORY_NAME = GH.JE_CATEGORY AND CS.STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND NS.STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND OP.OWNERSHIP_PERCENTAGE_ID = CI.CI_OWNERSHIP_ID AND CG.STAKEHOLDER_GROUP_ID = OP.STAKEHOLDER_GROUP_ID AND CG.NON_CONSENTING_STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND CG.CONSENTING_STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND JT.APPLICATION_ID = -99 UNION ALL SELECT CI.CI_DISTRIBUTION_ID LINE_NUMBER, CI.CI_DISTRIBUTION_ID CI_DISTRIBUTION_ID, CI.ACCOUNT_TYPE_CODE CI_DISTRIBUTION_TYPE_CODE, CI.CI_DISTRIBUTION_LINE_TYPE_CODE CI_DISTRIBUTION_LINE_TYPE_CODE, CASE WHEN CI.ACCOUNT_TYPE_CODE = 'V' THEN NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) ELSE NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) END CARRIED_AMOUNT, CI.CONSENTING_STAKEHOLDER_ID CONSENTING_STAKEHOLDER_ID, CS.STAKEHOLDER_SHORT_NAME CS_STAKEHOLDER_SHORT_NAME, CS.COST_CENTER_SEGMENT_VALUE CS_PRIMARY_SEGMENT_VALUE, CG.DEFAULT_CI_ACCOUNT_CCID DEFAULT_CI_ACCOUNT, CI.CONSENTING_PERCENTAGE CONSENTING_PERCENTAGE, CI.NON_CONSENTING_STAKEHOLDER_ID NON_CONSENTING_STAKEHOLDER_ID, NS.STAKEHOLDER_SHORT_NAME NCS_STAKEHOLDER_SHORT_NAME, CI.ACCOUNTED_CURRENCY_CODE CURRENCY_CODE, JA.EVENT_ID EVENT_ID, JT.TRANSACTION_ID TRANSACTION_ID, JT.TRANSACTION_DATE TRANSACTION_DATE, 10568 APPLICATION_ID, 'Joint Ventures' SOURCE_NAME, CASE WHEN GT.TRANSACTION_SOURCE_CODE = 'ORA_JV_MANUAL' THEN 'Manual' ELSE 'Other' END CATEGORY_NAME, GT.DESCRIPTION TRANSACTION_DESCRIPTION, GT.CODE_COMBINATION_ID TRANSACTION_ACCOUNT, CASE WHEN CI.ACCOUNT_TYPE_CODE = 'V' THEN NVL(GT.CREDIT_AMOUNT, 0) - NVL(GT.DEBIT_AMOUNT, 0) ELSE NVL(GT.DEBIT_AMOUNT, 0) - NVL(GT.CREDIT_AMOUNT, 0) END ORIGINAL_AMOUNT, JT.PROJECT_ID PROJECT_ID, JT.TASK_ID TASK_ID, JT.EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID, JT.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID, JT.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE FROM JV_CI_DISTRIBUTIONS CI, JV_TRANSACTIONS JT, JV_ACCOUNTING_HEADERS JA, JV_GENERATED_TRANSACTIONS GT, JV_STAKEHOLDERS_B CS, JV_STAKEHOLDERS_B NS, JV_CI_GROUP_LINES CG, JV_OWNER_PERCENTS_F_B OP WHERE JT.TRANSACTION_ID = CI.TRANSACTION_ID AND JA.ACCOUNTING_HEADER_ID = CI.ACCOUNTING_HEADER_ID AND GT.HEADER_ID = JT.HEADER_ID AND CS.STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND NS.STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND OP.OWNERSHIP_PERCENTAGE_ID = CI.CI_OWNERSHIP_ID AND CG.STAKEHOLDER_GROUP_ID = OP.STAKEHOLDER_GROUP_ID AND CG.NON_CONSENTING_STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND CG.CONSENTING_STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND JT.APPLICATION_ID = -10568

SQL_Statement

SELECT

CI.CI_DISTRIBUTION_ID LINE_NUMBER,

CI.CI_DISTRIBUTION_ID CI_DISTRIBUTION_ID,

CI.ACCOUNT_TYPE_CODE CI_DISTRIBUTION_TYPE_CODE,

CI.CI_DISTRIBUTION_LINE_TYPE_CODE CI_DISTRIBUTION_LINE_TYPE_CODE,

CASE

WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN

NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0)

ELSE

NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0)

END CARRIED_AMOUNT,

CI.CONSENTING_STAKEHOLDER_ID CONSENTING_STAKEHOLDER_ID,

CS.STAKEHOLDER_SHORT_NAME CS_STAKEHOLDER_SHORT_NAME,

CS.COST_CENTER_SEGMENT_VALUE CS_PRIMARY_SEGMENT_VALUE,

CG.DEFAULT_CI_ACCOUNT_CCID DEFAULT_CI_ACCOUNT,

CI.CONSENTING_PERCENTAGE CONSENTING_PERCENTAGE,

CI.NON_CONSENTING_STAKEHOLDER_ID NON_CONSENTING_STAKEHOLDER_ID,

NS.STAKEHOLDER_SHORT_NAME NCS_STAKEHOLDER_SHORT_NAME,

CI.ACCOUNTED_CURRENCY_CODE CURRENCY_CODE,

JA.EVENT_ID EVENT_ID,

JT.TRANSACTION_ID TRANSACTION_ID,

JT.TRANSACTION_DATE TRANSACTION_DATE,

JT.APPLICATION_ID APPLICATION_ID,

CASE

WHEN XH.APPLICATION_ID = 140 THEN 'Assets'

WHEN XH.APPLICATION_ID = 200 THEN 'Payables'

WHEN XH.APPLICATION_ID = 201 THEN 'Purchasing'

WHEN XH.APPLICATION_ID = 222 THEN 'Receivables'

WHEN XH.APPLICATION_ID = 235 THEN 'Tax'

WHEN XH.APPLICATION_ID = 260 THEN 'Cash Management'

WHEN XH.APPLICATION_ID = 435 THEN 'Global Intercompany'

WHEN XH.APPLICATION_ID = 602 THEN 'Other'

WHEN XH.APPLICATION_ID = 707 THEN 'Cost Accounting'

WHEN XH.APPLICATION_ID = 801 THEN 'Payroll'

WHEN XH.APPLICATION_ID = 8901 THEN 'US Federal'

WHEN XH.APPLICATION_ID = 10016 THEN 'Expenses'

WHEN XH.APPLICATION_ID = 10036 THEN 'Project Accounting'

WHEN XH.APPLICATION_ID = 10052 THEN 'Budgetary Control'

WHEN XH.APPLICATION_ID = 10096 THEN 'Receipt Accounting'

WHEN XH.APPLICATION_ID = 10455 THEN 'Revenue Management'

WHEN XH.APPLICATION_ID = 10566 THEN 'Channel Revenue'

WHEN XH.APPLICATION_ID = 10568 THEN 'Joint Ventures'

WHEN XH.APPLICATION_ID = 10571 THEN 'PS Compliance'

WHEN XH.APPLICATION_ID = 10572 THEN 'Lease Accounting'

END SOURCE_NAME,

GC.JE_CATEGORY_KEY CATEGORY_NAME,

XL.DESCRIPTION TRANSACTION_DESCRIPTION,

XL.CODE_COMBINATION_ID TRANSACTION_ACCOUNT,

CASE

WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN

NVL(XL.ACCOUNTED_CR, 0) - NVL(XL.ACCOUNTED_DR, 0)

ELSE

NVL(XL.ACCOUNTED_DR, 0) - NVL(XL.ACCOUNTED_CR ,0)

END ORIGINAL_AMOUNT,

JT.PROJECT_ID PROJECT_ID,

JT.TASK_ID TASK_ID,

JT.EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID,

JT.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID,

JT.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE

FROM

JV_CI_DISTRIBUTIONS CI,

JV_TRANSACTIONS JT,

JV_ACCOUNTING_HEADERS JA,

XLA_AE_HEADERS XH,

XLA_AE_LINES XL,

GL_JE_CATEGORIES_B GC,

JV_STAKEHOLDERS_B CS,

JV_STAKEHOLDERS_B NS,

JV_CI_GROUP_LINES CG,

JV_OWNER_PERCENTS_F_B OP

WHERE

JT.TRANSACTION_ID = CI.TRANSACTION_ID AND

JA.ACCOUNTING_HEADER_ID = CI.ACCOUNTING_HEADER_ID AND

XH.AE_HEADER_ID = JT.HEADER_ID AND

XH.APPLICATION_ID = JT.APPLICATION_ID AND

XL.AE_HEADER_ID = JT.HEADER_ID AND

XL.APPLICATION_ID = JT.APPLICATION_ID AND

XL.AE_LINE_NUM = JT.LINE_NUM AND

GC.JE_CATEGORY_NAME = XH.JE_CATEGORY_NAME AND

CS.STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND

NS.STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND

OP.OWNERSHIP_PERCENTAGE_ID = CI.CI_OWNERSHIP_ID AND

CG.STAKEHOLDER_GROUP_ID = OP.STAKEHOLDER_GROUP_ID AND

CG.NON_CONSENTING_STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND

CG.CONSENTING_STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND

JT.APPLICATION_ID > 0

UNION ALL

SELECT

CI.CI_DISTRIBUTION_ID LINE_NUMBER,

CI.CI_DISTRIBUTION_ID CI_DISTRIBUTION_ID,

CI.ACCOUNT_TYPE_CODE CI_DISTRIBUTION_TYPE_CODE,

CI.CI_DISTRIBUTION_LINE_TYPE_CODE CI_DISTRIBUTION_LINE_TYPE_CODE,

CASE

WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN

NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0)

ELSE

NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0)

END CARRIED_AMOUNT,

CI.CONSENTING_STAKEHOLDER_ID CONSENTING_STAKEHOLDER_ID,

CS.STAKEHOLDER_SHORT_NAME CS_STAKEHOLDER_SHORT_NAME,

CS.COST_CENTER_SEGMENT_VALUE CS_PRIMARY_SEGMENT_VALUE,

CG.DEFAULT_CI_ACCOUNT_CCID DEFAULT_CI_ACCOUNT,

CI.CONSENTING_PERCENTAGE CONSENTING_PERCENTAGE,

CI.NON_CONSENTING_STAKEHOLDER_ID NON_CONSENTING_STAKEHOLDER_ID,

NS.STAKEHOLDER_SHORT_NAME NCS_STAKEHOLDER_SHORT_NAME,

CI.ACCOUNTED_CURRENCY_CODE CURRENCY_CODE,

JA.EVENT_ID EVENT_ID,

JT.TRANSACTION_ID TRANSACTION_ID,

JT.TRANSACTION_DATE TRANSACTION_DATE,

JT.APPLICATION_ID APPLICATION_ID,

GH.JE_SOURCE SOURCE_NAME,

GC.JE_CATEGORY_KEY CATEGORY_NAME,

GL.DESCRIPTION TRANSACTION_DESCRIPTION,

GL.CODE_COMBINATION_ID TRANSACTION_ACCOUNT,

CASE

WHEN CI.ACCOUNT_TYPE_CODE = 'R' THEN

NVL(GL.ACCOUNTED_CR, 0) - NVL(GL.ACCOUNTED_DR, 0)

ELSE

NVL(GL.ACCOUNTED_DR, 0) - NVL(GL.ACCOUNTED_CR ,0)

END ORIGINAL_AMOUNT,

JT.PROJECT_ID PROJECT_ID,

JT.TASK_ID TASK_ID,

JT.EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID,

JT.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID,

JT.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE

FROM

JV_CI_DISTRIBUTIONS CI,

JV_TRANSACTIONS JT,

JV_ACCOUNTING_HEADERS JA,

GL_JE_HEADERS GH,

GL_JE_LINES GL,

GL_JE_CATEGORIES_B GC,

JV_STAKEHOLDERS_B CS,

JV_STAKEHOLDERS_B NS,

JV_CI_GROUP_LINES CG,

JV_OWNER_PERCENTS_F_B OP

WHERE

JT.TRANSACTION_ID = CI.TRANSACTION_ID AND

JA.ACCOUNTING_HEADER_ID = CI.ACCOUNTING_HEADER_ID AND

JT.HEADER_ID = GH.JE_HEADER_ID AND

JT.HEADER_ID = GL.JE_HEADER_ID AND

JT.LINE_NUM = GL.JE_LINE_NUM AND

GC.JE_CATEGORY_NAME = GH.JE_CATEGORY AND

CS.STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND

NS.STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND

OP.OWNERSHIP_PERCENTAGE_ID = CI.CI_OWNERSHIP_ID AND

CG.STAKEHOLDER_GROUP_ID = OP.STAKEHOLDER_GROUP_ID AND

CG.NON_CONSENTING_STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND

CG.CONSENTING_STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND

JT.APPLICATION_ID = -99

UNION ALL

SELECT

CI.CI_DISTRIBUTION_ID LINE_NUMBER,

CI.CI_DISTRIBUTION_ID CI_DISTRIBUTION_ID,

CI.ACCOUNT_TYPE_CODE CI_DISTRIBUTION_TYPE_CODE,

CI.CI_DISTRIBUTION_LINE_TYPE_CODE CI_DISTRIBUTION_LINE_TYPE_CODE,

CASE

WHEN CI.ACCOUNT_TYPE_CODE = 'V' THEN

NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0)

ELSE

NVL(CI.DISTRIBUTED_ACCOUNTED_DEBIT, 0) - NVL(CI.DISTRIBUTED_ACCOUNTED_CREDIT, 0)

END CARRIED_AMOUNT,

CI.CONSENTING_STAKEHOLDER_ID CONSENTING_STAKEHOLDER_ID,

CS.STAKEHOLDER_SHORT_NAME CS_STAKEHOLDER_SHORT_NAME,

CS.COST_CENTER_SEGMENT_VALUE CS_PRIMARY_SEGMENT_VALUE,

CG.DEFAULT_CI_ACCOUNT_CCID DEFAULT_CI_ACCOUNT,

CI.CONSENTING_PERCENTAGE CONSENTING_PERCENTAGE,

CI.NON_CONSENTING_STAKEHOLDER_ID NON_CONSENTING_STAKEHOLDER_ID,

NS.STAKEHOLDER_SHORT_NAME NCS_STAKEHOLDER_SHORT_NAME,

CI.ACCOUNTED_CURRENCY_CODE CURRENCY_CODE,

JA.EVENT_ID EVENT_ID,

JT.TRANSACTION_ID TRANSACTION_ID,

JT.TRANSACTION_DATE TRANSACTION_DATE,

10568 APPLICATION_ID,

'Joint Ventures' SOURCE_NAME,

CASE

WHEN GT.TRANSACTION_SOURCE_CODE = 'ORA_JV_MANUAL' THEN

'Manual'

ELSE

'Other'

END CATEGORY_NAME,

GT.DESCRIPTION TRANSACTION_DESCRIPTION,

GT.CODE_COMBINATION_ID TRANSACTION_ACCOUNT,

CASE

WHEN CI.ACCOUNT_TYPE_CODE = 'V' THEN

NVL(GT.CREDIT_AMOUNT, 0) - NVL(GT.DEBIT_AMOUNT, 0)

ELSE

NVL(GT.DEBIT_AMOUNT, 0) - NVL(GT.CREDIT_AMOUNT, 0)

END ORIGINAL_AMOUNT,

JT.PROJECT_ID PROJECT_ID,

JT.TASK_ID TASK_ID,

JT.EXPENDITURE_TYPE_ID EXPENDITURE_TYPE_ID,

JT.EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID,

JT.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE

FROM

JV_CI_DISTRIBUTIONS CI,

JV_TRANSACTIONS JT,

JV_ACCOUNTING_HEADERS JA,

JV_GENERATED_TRANSACTIONS GT,

JV_STAKEHOLDERS_B CS,

JV_STAKEHOLDERS_B NS,

JV_CI_GROUP_LINES CG,

JV_OWNER_PERCENTS_F_B OP

WHERE

JT.TRANSACTION_ID = CI.TRANSACTION_ID AND

JA.ACCOUNTING_HEADER_ID = CI.ACCOUNTING_HEADER_ID AND

GT.HEADER_ID = JT.HEADER_ID AND

CS.STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND

NS.STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND

OP.OWNERSHIP_PERCENTAGE_ID = CI.CI_OWNERSHIP_ID AND

CG.STAKEHOLDER_GROUP_ID = OP.STAKEHOLDER_GROUP_ID AND

CG.NON_CONSENTING_STAKEHOLDER_ID = CI.NON_CONSENTING_STAKEHOLDER_ID AND

CG.CONSENTING_STAKEHOLDER_ID = CI.CONSENTING_STAKEHOLDER_ID AND

JT.APPLICATION_ID = -10568