JV_INTERNAL_TRANSFER_LINE_V

Details

• Schema: FUSION

• Object owner: JV

• Object type: VIEW

Columns

Name

LINE_NUMBER DISTRIBUTION_ID DISTRIBUTION_TYPE_CODE DISTRIBUTION_LINE_TYPE_CODE DISTRIBUTED_AMOUNT PERCENTAGE_OF_INTEREST 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 PARTNER_CONTRIBUTION_NUMBER PARTNER_CONTRIBUTION_ACCOUNT

Query

SQL_Statement

SELECT JD.DISTRIBUTION_ID LINE_NUMBER, JD.DISTRIBUTION_ID DISTRIBUTION_ID, JD.DISTRIBUTION_TYPE_CODE DISTRIBUTION_TYPE_CODE, JD.DISTRIBUTION_LINE_TYPE_CODE DISTRIBUTION_LINE_TYPE_CODE, CASE WHEN JD.DISTRIBUTION_TYPE_CODE = 'R' THEN NVL(JD.DISTRIBUTED_CREDIT_AMOUNT, 0) - NVL(JD.DISTRIBUTED_DEBIT_AMOUNT, 0) ELSE NVL(JD.DISTRIBUTED_DEBIT_AMOUNT, 0) - NVL(JD.DISTRIBUTED_CREDIT_AMOUNT, 0) END DISTRIBUTED_AMOUNT, JD.PERCENTAGE_OF_INTEREST PERCENTAGE_OF_INTEREST, JD.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 JD.DISTRIBUTION_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, JD.CONTRIBUTION_ID PARTNER_CONTRIBUTION_NUMBER, JC.PARTNER_ACCOUNT_CCID PARTNER_CONTRIBUTION_ACCOUNT FROM JV_DISTRIBUTIONS JD, JV_TRANSACTIONS JT, JV_ACCOUNTING_HEADERS JA, XLA_AE_HEADERS XH, XLA_AE_LINES XL, GL_JE_CATEGORIES_B GC, JV_CONTRIBUTIONS_B JC WHERE JT.TRANSACTION_ID = JD.TRANSACTION_ID AND JA.ACCOUNTING_HEADER_ID = JD.TARGET_TRANSACTION_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 JT.APPLICATION_ID > 0 AND JD.CONTRIBUTION_ID=JC.CONTRIBUTION_ID(+) UNION ALL SELECT JD.DISTRIBUTION_ID LINE_NUMBER, JD.DISTRIBUTION_ID DISTRIBUTION_ID, JD.DISTRIBUTION_TYPE_CODE DISTRIBUTION_TYPE_CODE, JD.DISTRIBUTION_LINE_TYPE_CODE DISTRIBUTION_LINE_TYPE_CODE, CASE WHEN JD.DISTRIBUTION_TYPE_CODE = 'R' THEN NVL(JD.DISTRIBUTED_CREDIT_AMOUNT, 0) - NVL(JD.DISTRIBUTED_DEBIT_AMOUNT, 0) ELSE NVL(JD.DISTRIBUTED_DEBIT_AMOUNT, 0) - NVL(JD.DISTRIBUTED_CREDIT_AMOUNT, 0) END DISTRIBUTED_AMOUNT, JD.PERCENTAGE_OF_INTEREST PERCENTAGE_OF_INTEREST, JD.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 JD.DISTRIBUTION_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, JD.CONTRIBUTION_ID PARTNER_CONTRIBUTION_NUMBER, JC.PARTNER_ACCOUNT_CCID PARTNER_CONTRIBUTION_ACCOUNT FROM JV_DISTRIBUTIONS JD, JV_TRANSACTIONS JT, JV_ACCOUNTING_HEADERS JA, GL_JE_HEADERS GH, GL_JE_LINES GL, GL_JE_CATEGORIES_B GC, JV_CONTRIBUTIONS_B JC WHERE JT.TRANSACTION_ID = JD.TRANSACTION_ID AND JA.ACCOUNTING_HEADER_ID = JD.TARGET_TRANSACTION_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 JT.APPLICATION_ID = -99 AND JD.CONTRIBUTION_ID=JC.CONTRIBUTION_ID(+) UNION ALL SELECT GT.LINE_NUM LINE_NUMBER, JD.DISTRIBUTION_ID DISTRIBUTION_ID, JD.DISTRIBUTION_TYPE_CODE DISTRIBUTION_TYPE_CODE, JD.DISTRIBUTION_LINE_TYPE_CODE DISTRIBUTION_LINE_TYPE_CODE, CASE WHEN JD.DISTRIBUTION_TYPE_CODE = 'V' THEN NVL(JD.DISTRIBUTED_CREDIT_AMOUNT, 0) - NVL(JD.DISTRIBUTED_DEBIT_AMOUNT, 0) ELSE NVL(JD.DISTRIBUTED_DEBIT_AMOUNT, 0) - NVL(JD.DISTRIBUTED_CREDIT_AMOUNT, 0) END DISTRIBUTED_AMOUNT, JD.PERCENTAGE_OF_INTEREST PERCENTAGE_OF_INTEREST, JD.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 JD.DISTRIBUTION_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, JD.CONTRIBUTION_ID PARTNER_CONTRIBUTION_NUMBER, JC.PARTNER_ACCOUNT_CCID PARTNER_CONTRIBUTION_ACCOUNT FROM JV_DISTRIBUTIONS JD, JV_TRANSACTIONS JT, JV_ACCOUNTING_HEADERS JA, JV_GENERATED_TRANSACTIONS GT, JV_CONTRIBUTIONS_B JC WHERE JT.TRANSACTION_ID = JD.TRANSACTION_ID AND JD.TARGET_TRANSACTION_ID = JA.ACCOUNTING_HEADER_ID AND JT.HEADER_ID = GT.HEADER_ID AND JT.LINE_NUM = GT.LINE_NUM AND JT.APPLICATION_ID = -10568 AND JD.CONTRIBUTION_ID = JC.CONTRIBUTION_ID(+)