View: CST_XLA_INV_LINES_V

Product:	BOM - Bills of Material
Description:
Implementation/DBA Data:	APPS.CST_XLA_INV_LINES_V

View Text

SELECT CXIHV.EVENT_ID
, /* EVENT IDENTIFIER */ ROW_NUMBER() OVER(PARTITION BY CXIHV.EVENT_ID ORDER BY CXIHV.EVENT_ID) AS LINE_NUMBER
, /* LINE NUMBER */ MTA.REFERENCE_ACCOUNT CODE_COMBINATION_ID
, /* REFERENCE_ACCOUNT */ NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) ENTERED_AMOUNT
, /* ENTERED_AMOUNT */ MTA.ACCOUNTING_LINE_TYPE ACCOUNTING_LINE_TYPE_CODE
, /* ACCOUNTING_LINE_TYPE_CODE */ MTA.BASE_TRANSACTION_VALUE ACCOUNTED_AMOUNT
, /* ACCOUNTED_AMOUNT */ MTA.RATE_OR_AMOUNT
, /* RATE_OR_AMOUNT */ MTA.BASIS_TYPE INV_TXN_BASIS_TYPE
, /* BASIS_TYPE */ NVL(MTA.CURRENCY_CODE
, GL.CURRENCY_CODE) CURRENCY_CODE
, DECODE(MTA.CURRENCY_CODE
, NULL
, MTA.CURRENCY_CONVERSION_DATE
, GL.CURRENCY_CODE
, MTA.CURRENCY_CONVERSION_DATE
, NVL(MTA.CURRENCY_CONVERSION_DATE
, CXIHV.TRANSACTION_DATE) ) CURRENCY_CONVERSION_DATE
, DECODE(MTA.CURRENCY_CODE
, NULL
, TO_NUMBER(NULL)
, GL.CURRENCY_CODE
, TO_NUMBER(NULL)
, MTA.CURRENCY_CONVERSION_RATE ) CURRENCY_CONVERSION_RATE
, DECODE(MTA.CURRENCY_CODE
, NULL
, NULL
, GL.CURRENCY_CODE
, NULL
, MTA.CURRENCY_CONVERSION_TYPE ) CURRENCY_CONVERSION_TYPE
, MTA.RESOURCE_ID MATERIAL_OVERHEAD_ID
, /* MATERIAL_OVERHEAD_ID */ MTA.COST_ELEMENT_ID
, /* COST_ELEMENT_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_SUBINVENTORY_CODE
, CXIHV.H_TRANSFER_SUBINVENTORY )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_SUBINVENTORY_CODE
, CXIHV.H_TRANSFER_SUBINVENTORY )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_SUBINVENTORY_CODE
, CXIHV.H_TRANSFER_SUBINVENTORY )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_SUBINVENTORY_CODE
, CXIHV.H_TRANSFER_SUBINVENTORY )
, H_SUBINVENTORY_CODE ) SUBINVENTORY_CODE
, /* SUBINVENTORY_CODE */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_SUBINVENTORY
, CXIHV.H_SUBINVENTORY_CODE )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_SUBINVENTORY
, CXIHV.H_SUBINVENTORY_CODE )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_SUBINVENTORY
, CXIHV.H_SUBINVENTORY_CODE )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_SUBINVENTORY
, CXIHV.H_SUBINVENTORY_CODE )
, H_TRANSFER_SUBINVENTORY ) TRANSFER_SUBINVENTORY
, /* TRANSFER_SUBINVENTORY */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_LOCATOR_ID
, CXIHV.H_TRANSFER_LOCATOR_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_LOCATOR_ID
, CXIHV.H_TRANSFER_LOCATOR_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_LOCATOR_ID
, CXIHV.H_TRANSFER_LOCATOR_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_LOCATOR_ID
, CXIHV.H_TRANSFER_LOCATOR_ID )
, H_LOCATOR_ID ) LOCATOR_ID
, /* LOCATOR_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_LOCATOR_ID
, CXIHV.H_LOCATOR_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_LOCATOR_ID
, CXIHV.H_LOCATOR_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_LOCATOR_ID
, CXIHV.H_LOCATOR_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_LOCATOR_ID
, CXIHV.H_LOCATOR_ID )
, H_TRANSFER_LOCATOR_ID ) TRANSFER_LOCATOR_ID
, /* TRANSFER_LOCATOR_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_COST_GROUP_ID
, CXIHV.H_TRANSFER_COST_GROUP_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_COST_GROUP_ID
, CXIHV.H_TRANSFER_COST_GROUP_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_COST_GROUP_ID
, CXIHV.H_TRANSFER_COST_GROUP_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_COST_GROUP_ID
, CXIHV.H_TRANSFER_COST_GROUP_ID )
, H_COST_GROUP_ID ) COST_GROUP_ID
, /*COST_GROUP_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_COST_GROUP_ID
, CXIHV.H_COST_GROUP_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_COST_GROUP_ID
, CXIHV.H_COST_GROUP_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_COST_GROUP_ID
, CXIHV.H_COST_GROUP_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TRANSFER_COST_GROUP_ID
, CXIHV.H_COST_GROUP_ID )
, H_TRANSFER_COST_GROUP_ID ) TRANSFER_COST_GROUP_ID
, /*TRANSFER_COST_GROUP_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_PROJECT_ID
, CXIHV.H_PROJECT_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_PROJECT_ID
, CXIHV.H_PROJECT_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_PROJECT_ID
, CXIHV.H_PROJECT_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_PROJECT_ID
, CXIHV.H_PROJECT_ID )
, H_TO_PROJECT_ID ) TO_PROJECT_ID
, /* TO_PROJECT_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_PROJECT_ID
, CXIHV.H_TO_PROJECT_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_PROJECT_ID
, CXIHV.H_TO_PROJECT_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_PROJECT_ID
, CXIHV.H_TO_PROJECT_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_PROJECT_ID
, CXIHV.H_TO_PROJECT_ID )
, H_PROJECT_ID ) PROJECT_ID
, /* PROJECT_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_TASK_ID
, CXIHV.H_TASK_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_TASK_ID
, CXIHV.H_TASK_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_TASK_ID
, CXIHV.H_TASK_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TO_TASK_ID
, CXIHV.H_TASK_ID )
, H_TO_TASK_ID ) TO_TASK_ID
, /* TO_TASK_ID */ DECODE(CXIHV.TRANSACTION_ACTION_ID
, 2
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TASK_ID
, CXIHV.H_TO_TASK_ID )
, 5
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TASK_ID
, CXIHV.H_TO_TASK_ID )
, 28
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TASK_ID
, CXIHV.H_TO_TASK_ID )
, 55
, DECODE(SIGN(MTA.PRIMARY_QUANTITY)
, SIGN(CXIHV.PRIMARY_QUANTITY)
, CXIHV.H_TASK_ID
, CXIHV.H_TO_TASK_ID )
, H_TASK_ID ) TASK_ID
, /* TASK_ID */ CXIHV.H_LEDGER_ID LEDGER_ID
, CXIHV.ORGANIZATION_ID L_ORGANIZATION_ID
, MTA.INV_SUB_LEDGER_ID DISTRIBUTION_IDENTIFIER
, MTA.TRANSACTION_ID
, MP.ORGANIZATION_CODE L_ORGANIZATION_CODE
FROM MTL_TRANSACTION_ACCOUNTS MTA
, CST_XLA_INV_HEADERS_V CXIHV
, GL_LEDGERS GL
, MTL_MATERIAL_TRANSACTIONS MMT
, XLA_EVENTS_GT XGT
, MTL_PARAMETERS MP
WHERE MTA.TRANSACTION_ID = CXIHV.TRANSACTION_ID
AND MTA.TRANSACTION_SOURCE_TYPE_ID = CXIHV.TRANSACTION_SOURCE_TYPE_ID
AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
AND CXIHV.EVENT_ID = XGT.EVENT_ID
AND DECODE(XGT.BUDGETARY_CONTROL_FLAG
, 'Y'
, DECODE(MMT.TRANSACTION_ACTION_ID
, 21
, DECODE(MMT.FOB_POINT
, 1
, CXIHV.TRANSFER_ORGANIZATION_ID
, CXIHV.ORGANIZATION_ID)
, CXIHV.ORGANIZATION_ID)
, CXIHV.ORGANIZATION_ID) = MTA.ORGANIZATION_ID
AND GL.LEDGER_ID = CXIHV.H_LEDGER_ID
AND MTA.ORGANIZATION_ID = MP.ORGANIZATION_ID

Columns