JV_TRANSACTION_AMOUNTS

Details

Schema: FUSION
Object owner: JV
Object type: VIEW

Columns

Name
TRANSACTION_SOURCE_CODE TRANSACTION_ID JOINT_VENTURE_ID LEDGER_ID PERIOD_NAME ACCOUNTING_DATE CODE_COMBINATION_ID LINE_DESCRIPTION ACCOUNTED_DEBIT ACCOUNTED_CREDIT ACCOUNTED_AMOUNT ENTERED_DEBIT ENTERED_CREDIT ENTERED_AMOUNT CURRENCY_CODE

Name

TRANSACTION_SOURCE_CODE

TRANSACTION_ID

JOINT_VENTURE_ID

LEDGER_ID

PERIOD_NAME

ACCOUNTING_DATE

CODE_COMBINATION_ID

LINE_DESCRIPTION

ACCOUNTED_DEBIT

ACCOUNTED_CREDIT

ACCOUNTED_AMOUNT

ENTERED_DEBIT

ENTERED_CREDIT

ENTERED_AMOUNT

CURRENCY_CODE

Query

SQL_Statement
SELECT 'ORA_JV_GL' TRANSACTION_SOURCE_CODE, JT.TRANSACTION_ID TRANSACTION_ID, JT.JOINT_VENTURE_ID JOINT_VENTURE_ID, GL.LEDGER_ID LEDGER_ID, GL.PERIOD_NAME PERIOD_NAME, GL.EFFECTIVE_DATE ACCOUNTING_DATE, GL.CODE_COMBINATION_ID CODE_COMBINATION_ID, GL.DESCRIPTION LINE_DESCRIPTION, GL.ACCOUNTED_DR ACCOUNTED_DEBIT, GL.ACCOUNTED_CR ACCOUNTED_CREDIT, CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN NVL(GL.ACCOUNTED_CR, 0) - NVL(GL.ACCOUNTED_DR, 0) ELSE NVL(GL.ACCOUNTED_DR, 0) - NVL(GL.ACCOUNTED_CR,0) END ACCOUNTED_AMOUNT, GL.ENTERED_DR ENTERED_DEBIT, GL.ENTERED_CR ENTERED_CREDIT, CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN NVL(GL.ENTERED_CR, 0) - NVL(GL.ENTERED_DR, 0) ELSE NVL(GL.ENTERED_DR, 0) - NVL(GL.ENTERED_CR,0) END ENTERED_AMOUNT, GL.CURRENCY_CODE CURRENCY_CODE FROM FUSION.JV_TRANSACTIONS JT, FUSION.GL_JE_LINES GL, FUSION.GL_CODE_COMBINATIONS GC WHERE GL.JE_HEADER_ID = JT.HEADER_ID AND GL.JE_LINE_NUM = JT.LINE_NUM AND JT.APPLICATION_ID = -99 AND GC.CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID UNION ALL SELECT 'ORA_JV_SLA' TRANSACTION_SOURCE_CODE, JT.TRANSACTION_ID TRANSACTION_ID, JT.JOINT_VENTURE_ID JOINT_VENTURE_ID, XL.LEDGER_ID LEDGER_ID, XH.PERIOD_NAME PERIOD_NAME, XL.ACCOUNTING_DATE ACCOUNTING_DATE, XL.CODE_COMBINATION_ID CODE_COMBINATION_ID, XL.DESCRIPTION LINE_DESCRIPTION, XL.ACCOUNTED_DR ACCOUNTED_DEBIT, XL.ACCOUNTED_CR ACCOUNTED_CREDIT, CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN NVL(XL.ACCOUNTED_CR, 0) - NVL(XL.ACCOUNTED_DR, 0) ELSE NVL(XL.ACCOUNTED_DR, 0) - NVL(XL.ACCOUNTED_CR,0) END ACCOUNTED_AMOUNT, XL.ENTERED_DR ENTERED_DEBIT, XL.ENTERED_CR ENTERED_CREDIT, CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN NVL(XL.ENTERED_CR, 0) - NVL(XL.ENTERED_DR, 0) ELSE NVL(XL.ENTERED_DR, 0) - NVL(XL.ENTERED_CR,0) END ENTERED_AMOUNT, XL.CURRENCY_CODE CURRENCY_CODE FROM FUSION.JV_TRANSACTIONS JT, FUSION.XLA_AE_LINES XL, FUSION.XLA_AE_HEADERS XH, FUSION.GL_CODE_COMBINATIONS GC WHERE XL.AE_HEADER_ID = JT.HEADER_ID AND XL.AE_LINE_NUM = JT.LINE_NUM AND XL.APPLICATION_ID = JT.APPLICATION_ID AND XH.AE_HEADER_ID = JT.HEADER_ID AND XH.APPLICATION_ID = JT.APPLICATION_ID AND JT.APPLICATION_ID > 0 AND GC.CODE_COMBINATION_ID = XL.CODE_COMBINATION_ID UNION ALL SELECT CASE WHEN JG.TRANSACTION_SOURCE_CODE = 'ORA_JV_MANUAL' THEN 'ORA_JV_MANUAL' ELSE 'ORA_JV_OH' END TRANSACTION_SOURCE_CODE, JT.TRANSACTION_ID TRANSACTION_ID, JT.JOINT_VENTURE_ID JOINT_VENTURE_ID, JG.LEDGER_ID LEDGER_ID, JG.PERIOD_NAME PERIOD_NAME, NULL ACCOUNTING_DATE, JG.CODE_COMBINATION_ID CODE_COMBINATION_ID, JG.DESCRIPTION LINE_DESCRIPTION, JG.DEBIT_AMOUNT ACCOUNTED_DEBIT, JG.CREDIT_AMOUNT ACCOUNTED_CREDIT, CASE WHEN JG.DISTRIBUTION_TYPE_CODE = 'ORA_JV_REVENUE' THEN NVL(JG.CREDIT_AMOUNT, 0) - NVL(JG.DEBIT_AMOUNT, 0) ELSE NVL(JG.DEBIT_AMOUNT, 0) - NVL(JG.CREDIT_AMOUNT, 0) END ACCOUNTED_AMOUNT, NULL ENTERED_DEBIT, NULL ENTERED_CREDIT, NULL ENTERED_AMOUNT, JG.CURRENCY_CODE CURRENCY_CODE FROM FUSION.JV_TRANSACTIONS JT, FUSION.JV_GENERATED_TRANSACTIONS JG WHERE JG.HEADER_ID = JT.HEADER_ID AND JG.LINE_NUM = JT.LINE_NUM AND JT.APPLICATION_ID = -10568

SQL_Statement

SELECT

'ORA_JV_GL' TRANSACTION_SOURCE_CODE,

JT.TRANSACTION_ID TRANSACTION_ID,

JT.JOINT_VENTURE_ID JOINT_VENTURE_ID,

GL.LEDGER_ID LEDGER_ID,

GL.PERIOD_NAME PERIOD_NAME,

GL.EFFECTIVE_DATE ACCOUNTING_DATE,

GL.CODE_COMBINATION_ID CODE_COMBINATION_ID,

GL.DESCRIPTION LINE_DESCRIPTION,

GL.ACCOUNTED_DR ACCOUNTED_DEBIT,

GL.ACCOUNTED_CR ACCOUNTED_CREDIT,

CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN

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

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

END ACCOUNTED_AMOUNT,

GL.ENTERED_DR ENTERED_DEBIT,

GL.ENTERED_CR ENTERED_CREDIT,

CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN

NVL(GL.ENTERED_CR, 0) - NVL(GL.ENTERED_DR, 0)

ELSE NVL(GL.ENTERED_DR, 0) - NVL(GL.ENTERED_CR,0)

END ENTERED_AMOUNT,

GL.CURRENCY_CODE CURRENCY_CODE

FROM

FUSION.JV_TRANSACTIONS JT,

FUSION.GL_JE_LINES GL,

FUSION.GL_CODE_COMBINATIONS GC

WHERE

GL.JE_HEADER_ID = JT.HEADER_ID AND

GL.JE_LINE_NUM = JT.LINE_NUM AND

JT.APPLICATION_ID = -99 AND

GC.CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID

UNION ALL

SELECT

'ORA_JV_SLA' TRANSACTION_SOURCE_CODE,

JT.TRANSACTION_ID TRANSACTION_ID,

JT.JOINT_VENTURE_ID JOINT_VENTURE_ID,

XL.LEDGER_ID LEDGER_ID,

XH.PERIOD_NAME PERIOD_NAME,

XL.ACCOUNTING_DATE ACCOUNTING_DATE,

XL.CODE_COMBINATION_ID CODE_COMBINATION_ID,

XL.DESCRIPTION LINE_DESCRIPTION,

XL.ACCOUNTED_DR ACCOUNTED_DEBIT,

XL.ACCOUNTED_CR ACCOUNTED_CREDIT,

CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN

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

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

END ACCOUNTED_AMOUNT,

XL.ENTERED_DR ENTERED_DEBIT,

XL.ENTERED_CR ENTERED_CREDIT,

CASE WHEN GC.ACCOUNT_TYPE = 'R' THEN

NVL(XL.ENTERED_CR, 0) - NVL(XL.ENTERED_DR, 0)

ELSE NVL(XL.ENTERED_DR, 0) - NVL(XL.ENTERED_CR,0)

END ENTERED_AMOUNT,

XL.CURRENCY_CODE CURRENCY_CODE

FROM

FUSION.JV_TRANSACTIONS JT,

FUSION.XLA_AE_LINES XL,

FUSION.XLA_AE_HEADERS XH,

FUSION.GL_CODE_COMBINATIONS GC

WHERE

XL.AE_HEADER_ID = JT.HEADER_ID AND

XL.AE_LINE_NUM = JT.LINE_NUM AND

XL.APPLICATION_ID = JT.APPLICATION_ID AND

XH.AE_HEADER_ID = JT.HEADER_ID AND

XH.APPLICATION_ID = JT.APPLICATION_ID AND

JT.APPLICATION_ID > 0 AND

GC.CODE_COMBINATION_ID = XL.CODE_COMBINATION_ID

UNION ALL

SELECT

CASE WHEN JG.TRANSACTION_SOURCE_CODE = 'ORA_JV_MANUAL' THEN

'ORA_JV_MANUAL'

ELSE

'ORA_JV_OH'

END TRANSACTION_SOURCE_CODE,

JT.TRANSACTION_ID TRANSACTION_ID,

JT.JOINT_VENTURE_ID JOINT_VENTURE_ID,

JG.LEDGER_ID LEDGER_ID,

JG.PERIOD_NAME PERIOD_NAME,

NULL ACCOUNTING_DATE,

JG.CODE_COMBINATION_ID CODE_COMBINATION_ID,

JG.DESCRIPTION LINE_DESCRIPTION,

JG.DEBIT_AMOUNT ACCOUNTED_DEBIT,

JG.CREDIT_AMOUNT ACCOUNTED_CREDIT,

CASE

WHEN JG.DISTRIBUTION_TYPE_CODE = 'ORA_JV_REVENUE' THEN

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

ELSE

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

END ACCOUNTED_AMOUNT,

NULL ENTERED_DEBIT,

NULL ENTERED_CREDIT,

NULL ENTERED_AMOUNT,

JG.CURRENCY_CODE CURRENCY_CODE

FROM

FUSION.JV_TRANSACTIONS JT,

FUSION.JV_GENERATED_TRANSACTIONS JG

WHERE

JG.HEADER_ID = JT.HEADER_ID AND

JG.LINE_NUM = JT.LINE_NUM AND

JT.APPLICATION_ID = -10568