VIEW: APPS.CST_XLA_WIP_HEADERS_V

Source

View Text - Preformatted

SELECT X.event_id, 'WIP_TRANSACTION_ACCOUNTS' distribution_type, X.currency_code, X.currency_conversion_date, X.CURRENCY_CONVERSION_RATE, X.CURRENCY_CONVERSION_TYPE, X.transaction_id, X.transaction_date, X.wip_transaction_type_id, X.transaction_source_code, X.transaction_source_line_id, X.wip_entity_id, X.organization_id, X.resource_id, X.wip_txn_basis_type, X.employee_id, X.dept_id, X.chg_dept_id, X.line_id, X.operation_seq_num, X.res_seq_num, X.transaction_reason_id, CDO.activity_id, X.basis_resource_id, X.move_transaction_id, X.transaction_quantity, X.transaction_uom, X.primary_quantity, X.primary_uom, X.usage_rate_or_amount, X.standard_rate_flag, X.autocharge_type, X.actual_resource_rate, X.standard_resource_rate, X.cost_update_id, X.instance_id, X.transaction_reference, X.inventory_item_id, X.completion_transaction_id, X.transaction_attribute_category, X.transaction_attribute1, X.transaction_attribute2, X.transaction_attribute3, X.transaction_attribute4, X.transaction_attribute5, X.transaction_attribute6, X.transaction_attribute7, X.transaction_attribute8, X.transaction_attribute9, X.transaction_attribute10, X.transaction_attribute11, X.transaction_attribute12, X.transaction_attribute13, X.transaction_attribute14, X.transaction_attribute15, X.wip_txn_project_id, X.wip_txn_task_id, X.TRANSFER_TO_GL_INDICATOR FROM ( SELECT /*+ cardinality(xeg,1) */ XEG.event_id, NVL(WT.currency_code, GL.currency_code) currency_code, WT.currency_conversion_date, DECODE(WT.CURRENCY_CODE, NULL, to_number(NULL), GL.CURRENCY_CODE, to_number(NULL), WT.CURRENCY_CONVERSION_RATE ) CURRENCY_CONVERSION_RATE, DECODE(WT.CURRENCY_CODE, NULL, NULL, GL.CURRENCY_CODE, NULL, WT.CURRENCY_CONVERSION_TYPE ) CURRENCY_CONVERSION_TYPE, WT.transaction_id, WT.transaction_date, WT.transaction_type wip_transaction_type_id, WT.source_code transaction_source_code, WT.source_line_id transaction_source_line_id, WT.wip_entity_id, WT.organization_id, XEG.source_id_int_2 resource_id, XEG.source_id_int_3 wip_txn_basis_type, WT.employee_id, WT.department_id dept_id, WT.charge_department_id chg_dept_id, WT.line_id, WT.operation_seq_num operation_seq_num, WT.resource_seq_num res_seq_num, WT.reason_id transaction_reason_id, WT.resource_id basis_resource_id, WT.move_transaction_id, WT.transaction_quantity, WT.transaction_uom, WT.primary_quantity, WT.primary_uom, WT.usage_rate_or_amount, NVL(WT.standard_rate_flag, 2) standard_rate_flag, WT.autocharge_type, WT.actual_resource_rate, WT.standard_resource_rate, WT.cost_update_id, WT.instance_id, WT.reference transaction_reference, WT.primary_item_id inventory_item_id, WT.completion_transaction_id, WT.attribute_category transaction_attribute_category, WT.attribute1 transaction_attribute1, WT.attribute2 transaction_attribute2, WT.attribute3 transaction_attribute3, WT.attribute4 transaction_attribute4, WT.attribute5 transaction_attribute5, WT.attribute6 transaction_attribute6, WT.attribute7 transaction_attribute7, WT.attribute8 transaction_attribute8, WT.attribute9 transaction_attribute9, WT.attribute10 transaction_attribute10, WT.attribute11 transaction_attribute11, WT.attribute12 transaction_attribute12, WT.attribute13 transaction_attribute13, WT.attribute14 transaction_attribute14, WT.attribute15 transaction_attribute15, WT.project_id wip_txn_project_id, WT.task_id wip_txn_task_id, DECODE(MP.GENERAL_LEDGER_UPDATE_CODE, 3, 'N', 'Y' ) TRANSFER_TO_GL_INDICATOR, NVL(MP.avg_rates_cost_type_id, MP.primary_cost_method) cost_type_id FROM WIP_TRANSACTIONS WT, XLA_EVENTS_GT XEG, MTL_PARAMETERS MP, GL_LEDGERS GL WHERE XEG.source_id_int_1 = WT.transaction_id AND XEG.entity_code = 'WIP_ACCOUNTING_EVENTS' AND MP.organization_id = WT.organization_id AND XEG.event_type_code = 'OVERHEAD_ABSORPTION' AND GL.ledger_id = XEG.ledger_id ) X, CST_DEPARTMENT_OVERHEADS CDO WHERE CDO.department_id (+) = X.dept_id AND CDO.cost_type_id (+) = X.cost_type_id AND CDO.overhead_id (+)= X.resource_id AND CDO.basis_type (+)= X.wip_txn_basis_type UNION ALL SELECT /*+ cardinality(xeg,1) */ XEG.event_id, 'WIP_TRANSACTION_ACCOUNTS' distribution_type, NVL(WT.currency_code, GL.currency_code) currency_code, WT.currency_conversion_date, DECODE(WT.CURRENCY_CODE, NULL, to_number(NULL), GL.CURRENCY_CODE, to_number(NULL), WT.CURRENCY_CONVERSION_RATE ) CURRENCY_CONVERSION_RATE, DECODE(WT.CURRENCY_CODE, NULL, NULL, GL.CURRENCY_CODE, NULL, WT.CURRENCY_CONVERSION_TYPE ) CURRENCY_CONVERSION_TYPE, WT.transaction_id, WT.transaction_date, WT.transaction_type wip_transaction_type_id, WT.source_code transaction_source_code, WT.source_line_id transaction_source_line_id, WT.wip_entity_id, WT.organization_id, WT.resource_id, WT.basis_type wip_txn_basis_type, WT.employee_id, WT.department_id dept_id, WT.charge_department_id chg_dept_id, WT.line_id, WT.operation_seq_num operation_seq_num, WT.resource_seq_num res_seq_num, WT.reason_id transaction_reason_id, WT.activity_id, NULL basis_resource_id, WT.move_transaction_id, WT.transaction_quantity, WT.transaction_uom, WT.primary_quantity, WT.primary_uom, WT.usage_rate_or_amount, NVL(WT.standard_rate_flag, 2) standard_rate_flag, WT.autocharge_type, WT.actual_resource_rate, WT.standard_resource_rate, WT.cost_update_id, WT.instance_id, WT.reference transaction_reference, WT.primary_item_id inventory_item_id, WT.completion_transaction_id, WT.attribute_category transaction_attribute_category, WT.attribute1 transaction_attribute1, WT.attribute2 transaction_attribute2, WT.attribute3 transaction_attribute3, WT.attribute4 transaction_attribute4, WT.attribute5 transaction_attribute5, WT.attribute6 transaction_attribute6, WT.attribute7 transaction_attribute7, WT.attribute8 transaction_attribute8, WT.attribute9 transaction_attribute9, WT.attribute10 transaction_attribute10, WT.attribute11 transaction_attribute11, WT.attribute12 transaction_attribute12, WT.attribute13 transaction_attribute13, WT.attribute14 transaction_attribute14, WT.attribute15 transaction_attribute15, WT.project_id wip_txn_project_id, WT.task_id wip_txn_task_id, DECODE(MP.GENERAL_LEDGER_UPDATE_CODE, 3, 'N', 'Y' ) TRANSFER_TO_GL_INDICATOR FROM WIP_TRANSACTIONS WT, XLA_EVENTS_GT XEG, MTL_PARAMETERS MP, GL_LEDGERS GL WHERE XEG.source_id_int_1 = WT.transaction_id AND XEG.entity_code = 'WIP_ACCOUNTING_EVENTS' AND XEG.event_type_code != 'OVERHEAD_ABSORPTION' AND MP.ORGANIZATION_ID = WT.ORGANIZATION_ID AND GL.ledger_id = XEG.ledger_id

View Text - HTML Formatted

SELECT X.EVENT_ID
, 'WIP_TRANSACTION_ACCOUNTS' DISTRIBUTION_TYPE
, X.CURRENCY_CODE
, X.CURRENCY_CONVERSION_DATE
, X.CURRENCY_CONVERSION_RATE
, X.CURRENCY_CONVERSION_TYPE
, X.TRANSACTION_ID
, X.TRANSACTION_DATE
, X.WIP_TRANSACTION_TYPE_ID
, X.TRANSACTION_SOURCE_CODE
, X.TRANSACTION_SOURCE_LINE_ID
, X.WIP_ENTITY_ID
, X.ORGANIZATION_ID
, X.RESOURCE_ID
, X.WIP_TXN_BASIS_TYPE
, X.EMPLOYEE_ID
, X.DEPT_ID
, X.CHG_DEPT_ID
, X.LINE_ID
, X.OPERATION_SEQ_NUM
, X.RES_SEQ_NUM
, X.TRANSACTION_REASON_ID
, CDO.ACTIVITY_ID
, X.BASIS_RESOURCE_ID
, X.MOVE_TRANSACTION_ID
, X.TRANSACTION_QUANTITY
, X.TRANSACTION_UOM
, X.PRIMARY_QUANTITY
, X.PRIMARY_UOM
, X.USAGE_RATE_OR_AMOUNT
, X.STANDARD_RATE_FLAG
, X.AUTOCHARGE_TYPE
, X.ACTUAL_RESOURCE_RATE
, X.STANDARD_RESOURCE_RATE
, X.COST_UPDATE_ID
, X.INSTANCE_ID
, X.TRANSACTION_REFERENCE
, X.INVENTORY_ITEM_ID
, X.COMPLETION_TRANSACTION_ID
, X.TRANSACTION_ATTRIBUTE_CATEGORY
, X.TRANSACTION_ATTRIBUTE1
, X.TRANSACTION_ATTRIBUTE2
, X.TRANSACTION_ATTRIBUTE3
, X.TRANSACTION_ATTRIBUTE4
, X.TRANSACTION_ATTRIBUTE5
, X.TRANSACTION_ATTRIBUTE6
, X.TRANSACTION_ATTRIBUTE7
, X.TRANSACTION_ATTRIBUTE8
, X.TRANSACTION_ATTRIBUTE9
, X.TRANSACTION_ATTRIBUTE10
, X.TRANSACTION_ATTRIBUTE11
, X.TRANSACTION_ATTRIBUTE12
, X.TRANSACTION_ATTRIBUTE13
, X.TRANSACTION_ATTRIBUTE14
, X.TRANSACTION_ATTRIBUTE15
, X.WIP_TXN_PROJECT_ID
, X.WIP_TXN_TASK_ID
, X.TRANSFER_TO_GL_INDICATOR
FROM ( SELECT /*+ CARDINALITY(XEG
, 1) */ XEG.EVENT_ID
, NVL(WT.CURRENCY_CODE
, GL.CURRENCY_CODE) CURRENCY_CODE
, WT.CURRENCY_CONVERSION_DATE
, DECODE(WT.CURRENCY_CODE
, NULL
, TO_NUMBER(NULL)
, GL.CURRENCY_CODE
, TO_NUMBER(NULL)
, WT.CURRENCY_CONVERSION_RATE ) CURRENCY_CONVERSION_RATE
, DECODE(WT.CURRENCY_CODE
, NULL
, NULL
, GL.CURRENCY_CODE
, NULL
, WT.CURRENCY_CONVERSION_TYPE ) CURRENCY_CONVERSION_TYPE
, WT.TRANSACTION_ID
, WT.TRANSACTION_DATE
, WT.TRANSACTION_TYPE WIP_TRANSACTION_TYPE_ID
, WT.SOURCE_CODE TRANSACTION_SOURCE_CODE
, WT.SOURCE_LINE_ID TRANSACTION_SOURCE_LINE_ID
, WT.WIP_ENTITY_ID
, WT.ORGANIZATION_ID
, XEG.SOURCE_ID_INT_2 RESOURCE_ID
, XEG.SOURCE_ID_INT_3 WIP_TXN_BASIS_TYPE
, WT.EMPLOYEE_ID
, WT.DEPARTMENT_ID DEPT_ID
, WT.CHARGE_DEPARTMENT_ID CHG_DEPT_ID
, WT.LINE_ID
, WT.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
, WT.RESOURCE_SEQ_NUM RES_SEQ_NUM
, WT.REASON_ID TRANSACTION_REASON_ID
, WT.RESOURCE_ID BASIS_RESOURCE_ID
, WT.MOVE_TRANSACTION_ID
, WT.TRANSACTION_QUANTITY
, WT.TRANSACTION_UOM
, WT.PRIMARY_QUANTITY
, WT.PRIMARY_UOM
, WT.USAGE_RATE_OR_AMOUNT
, NVL(WT.STANDARD_RATE_FLAG
, 2) STANDARD_RATE_FLAG
, WT.AUTOCHARGE_TYPE
, WT.ACTUAL_RESOURCE_RATE
, WT.STANDARD_RESOURCE_RATE
, WT.COST_UPDATE_ID
, WT.INSTANCE_ID
, WT.REFERENCE TRANSACTION_REFERENCE
, WT.PRIMARY_ITEM_ID INVENTORY_ITEM_ID
, WT.COMPLETION_TRANSACTION_ID
, WT.ATTRIBUTE_CATEGORY TRANSACTION_ATTRIBUTE_CATEGORY
, WT.ATTRIBUTE1 TRANSACTION_ATTRIBUTE1
, WT.ATTRIBUTE2 TRANSACTION_ATTRIBUTE2
, WT.ATTRIBUTE3 TRANSACTION_ATTRIBUTE3
, WT.ATTRIBUTE4 TRANSACTION_ATTRIBUTE4
, WT.ATTRIBUTE5 TRANSACTION_ATTRIBUTE5
, WT.ATTRIBUTE6 TRANSACTION_ATTRIBUTE6
, WT.ATTRIBUTE7 TRANSACTION_ATTRIBUTE7
, WT.ATTRIBUTE8 TRANSACTION_ATTRIBUTE8
, WT.ATTRIBUTE9 TRANSACTION_ATTRIBUTE9
, WT.ATTRIBUTE10 TRANSACTION_ATTRIBUTE10
, WT.ATTRIBUTE11 TRANSACTION_ATTRIBUTE11
, WT.ATTRIBUTE12 TRANSACTION_ATTRIBUTE12
, WT.ATTRIBUTE13 TRANSACTION_ATTRIBUTE13
, WT.ATTRIBUTE14 TRANSACTION_ATTRIBUTE14
, WT.ATTRIBUTE15 TRANSACTION_ATTRIBUTE15
, WT.PROJECT_ID WIP_TXN_PROJECT_ID
, WT.TASK_ID WIP_TXN_TASK_ID
, DECODE(MP.GENERAL_LEDGER_UPDATE_CODE
, 3
, 'N'
, 'Y' ) TRANSFER_TO_GL_INDICATOR
, NVL(MP.AVG_RATES_COST_TYPE_ID
, MP.PRIMARY_COST_METHOD) COST_TYPE_ID
FROM WIP_TRANSACTIONS WT
, XLA_EVENTS_GT XEG
, MTL_PARAMETERS MP
, GL_LEDGERS GL
WHERE XEG.SOURCE_ID_INT_1 = WT.TRANSACTION_ID
AND XEG.ENTITY_CODE = 'WIP_ACCOUNTING_EVENTS'
AND MP.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND XEG.EVENT_TYPE_CODE = 'OVERHEAD_ABSORPTION'
AND GL.LEDGER_ID = XEG.LEDGER_ID ) X
, CST_DEPARTMENT_OVERHEADS CDO
WHERE CDO.DEPARTMENT_ID (+) = X.DEPT_ID
AND CDO.COST_TYPE_ID (+) = X.COST_TYPE_ID
AND CDO.OVERHEAD_ID (+)= X.RESOURCE_ID
AND CDO.BASIS_TYPE (+)= X.WIP_TXN_BASIS_TYPE UNION ALL SELECT /*+ CARDINALITY(XEG
, 1) */ XEG.EVENT_ID
, 'WIP_TRANSACTION_ACCOUNTS' DISTRIBUTION_TYPE
, NVL(WT.CURRENCY_CODE
, GL.CURRENCY_CODE) CURRENCY_CODE
, WT.CURRENCY_CONVERSION_DATE
, DECODE(WT.CURRENCY_CODE
, NULL
, TO_NUMBER(NULL)
, GL.CURRENCY_CODE
, TO_NUMBER(NULL)
, WT.CURRENCY_CONVERSION_RATE ) CURRENCY_CONVERSION_RATE
, DECODE(WT.CURRENCY_CODE
, NULL
, NULL
, GL.CURRENCY_CODE
, NULL
, WT.CURRENCY_CONVERSION_TYPE ) CURRENCY_CONVERSION_TYPE
, WT.TRANSACTION_ID
, WT.TRANSACTION_DATE
, WT.TRANSACTION_TYPE WIP_TRANSACTION_TYPE_ID
, WT.SOURCE_CODE TRANSACTION_SOURCE_CODE
, WT.SOURCE_LINE_ID TRANSACTION_SOURCE_LINE_ID
, WT.WIP_ENTITY_ID
, WT.ORGANIZATION_ID
, WT.RESOURCE_ID
, WT.BASIS_TYPE WIP_TXN_BASIS_TYPE
, WT.EMPLOYEE_ID
, WT.DEPARTMENT_ID DEPT_ID
, WT.CHARGE_DEPARTMENT_ID CHG_DEPT_ID
, WT.LINE_ID
, WT.OPERATION_SEQ_NUM OPERATION_SEQ_NUM
, WT.RESOURCE_SEQ_NUM RES_SEQ_NUM
, WT.REASON_ID TRANSACTION_REASON_ID
, WT.ACTIVITY_ID
, NULL BASIS_RESOURCE_ID
, WT.MOVE_TRANSACTION_ID
, WT.TRANSACTION_QUANTITY
, WT.TRANSACTION_UOM
, WT.PRIMARY_QUANTITY
, WT.PRIMARY_UOM
, WT.USAGE_RATE_OR_AMOUNT
, NVL(WT.STANDARD_RATE_FLAG
, 2) STANDARD_RATE_FLAG
, WT.AUTOCHARGE_TYPE
, WT.ACTUAL_RESOURCE_RATE
, WT.STANDARD_RESOURCE_RATE
, WT.COST_UPDATE_ID
, WT.INSTANCE_ID
, WT.REFERENCE TRANSACTION_REFERENCE
, WT.PRIMARY_ITEM_ID INVENTORY_ITEM_ID
, WT.COMPLETION_TRANSACTION_ID
, WT.ATTRIBUTE_CATEGORY TRANSACTION_ATTRIBUTE_CATEGORY
, WT.ATTRIBUTE1 TRANSACTION_ATTRIBUTE1
, WT.ATTRIBUTE2 TRANSACTION_ATTRIBUTE2
, WT.ATTRIBUTE3 TRANSACTION_ATTRIBUTE3
, WT.ATTRIBUTE4 TRANSACTION_ATTRIBUTE4
, WT.ATTRIBUTE5 TRANSACTION_ATTRIBUTE5
, WT.ATTRIBUTE6 TRANSACTION_ATTRIBUTE6
, WT.ATTRIBUTE7 TRANSACTION_ATTRIBUTE7
, WT.ATTRIBUTE8 TRANSACTION_ATTRIBUTE8
, WT.ATTRIBUTE9 TRANSACTION_ATTRIBUTE9
, WT.ATTRIBUTE10 TRANSACTION_ATTRIBUTE10
, WT.ATTRIBUTE11 TRANSACTION_ATTRIBUTE11
, WT.ATTRIBUTE12 TRANSACTION_ATTRIBUTE12
, WT.ATTRIBUTE13 TRANSACTION_ATTRIBUTE13
, WT.ATTRIBUTE14 TRANSACTION_ATTRIBUTE14
, WT.ATTRIBUTE15 TRANSACTION_ATTRIBUTE15
, WT.PROJECT_ID WIP_TXN_PROJECT_ID
, WT.TASK_ID WIP_TXN_TASK_ID
, DECODE(MP.GENERAL_LEDGER_UPDATE_CODE
, 3
, 'N'
, 'Y' ) TRANSFER_TO_GL_INDICATOR
FROM WIP_TRANSACTIONS WT
, XLA_EVENTS_GT XEG
, MTL_PARAMETERS MP
, GL_LEDGERS GL
WHERE XEG.SOURCE_ID_INT_1 = WT.TRANSACTION_ID
AND XEG.ENTITY_CODE = 'WIP_ACCOUNTING_EVENTS'
AND XEG.EVENT_TYPE_CODE != 'OVERHEAD_ABSORPTION'
AND MP.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND GL.LEDGER_ID = XEG.LEDGER_ID