CST_XLA_TRANSACTION_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
TRANSACTION_COST_ID COST_ID SOURCE_TABLE COST_ELEMENT_ID EXPENSE_POOL_ID ABSORPTION_TYPE ADJUSTMENT_TRANSACTION_ID USER_ADJUSTMENT_ID OVERHEAD_ID TRANSACTION_ID COST_ORG_ID COST_BOOK_ID VAL_STRUCTURE_ID STRUCTURE_INSTANCE_NUMBER VAL_UNIT_ID VAL_UNIT_COMBINATION_ID INVENTORY_ITEM_ID INV_ORG_ID ITEM_VALIDATION_ORG_ID LOGICAL_FLAG BASE_TXN_TYPE_ID BASE_TXN_SOURCE_TYPE_ID COST_PROFILE_ID EXPENSE_TRANSACTION_FLAG COST_METHOD_CODE NEGATIVE_QUANTITY_CODE QUANTITY_FLOW_CODE UOM_CODE CST_INV_TRANSACTION_DTL_ID OWNING_COST_ORG_ID TRANSFER_COST_ORG_ID TRANSFER_BOOK_ID TRANSFER_VAL_UNIT_ID PRICING_OPTION FOB_POINT TXN_SOURCE_DOC_TYPE TXN_SOURCE_DOC_NUMBER TXN_SOURCE_REF_DOC_TYPE TXN_SOURCE_REF_DOC_NUMBER BUSINESS_UNIT_ID TRANSACTION_FLOW_TYPE INTRANSIT_FLAG INTERNAL_PROFIT_TRACKING CST_INV_TRANSACTION_ID EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID SUBINVENTORY_CODE LOCATOR_ID REASON_ID TRANSFER_INV_ORG_ID TRANSFER_SUBINVENTORY_CODE TRANSFER_LOCATOR_ID INTERCOMPANY_INVOICING_FLAG RCV_TRANSACTION_ID PO_DISTRIBUTION_ID WSH_DELIVERY_DETAIL_ID DOO_FULLFILL_LINE_ID TRANSACTION_ATTRIBUTE_CATEGORY TRANSACTION_ATTRIBUTE1 TRANSACTION_ATTRIBUTE2 TRANSACTION_ATTRIBUTE3 TRANSACTION_ATTRIBUTE4 TRANSACTION_ATTRIBUTE5 TRANSACTION_ATTRIBUTE6 TRANSACTION_ATTRIBUTE7 TRANSACTION_ATTRIBUTE8 TRANSACTION_ATTRIBUTE9 TRANSACTION_ATTRIBUTE10 TRANSACTION_ATTRIBUTE11 TRANSACTION_ATTRIBUTE12 TRANSACTION_ATTRIBUTE13 TRANSACTION_ATTRIBUTE14 TRANSACTION_ATTRIBUTE15 TRANSACTION_ATTRIBUTE16 TRANSACTION_ATTRIBUTE17 TRANSACTION_ATTRIBUTE18 TRANSACTION_ATTRIBUTE19 TRANSACTION_ATTRIBUTE20 TRANSACTION_ATTRIBUTE21 TRANSACTION_ATTRIBUTE22 TRANSACTION_ATTRIBUTE23 TRANSACTION_ATTRIBUTE24 TRANSACTION_ATTRIBUTE25 TRANSACTION_ATTRIBUTE26 TRANSACTION_ATTRIBUTE27 TRANSACTION_ATTRIBUTE28 TRANSACTION_ATTRIBUTE29 TRANSACTION_ATTRIBUTE30 TRANSACTION_UOM_CODE PRIMARY_UOM_CODE SECONDARY_TRANSACTION_UOM_CODE SOURCE_SALES_ORDER_NUMBER SOURCE_SALES_ORDER_LINE_NUMBER SALES_ORDER_SOURCE_SYSTEM SOURCE_SO_SHIPMENT_NUMBER RECEIPT_ADVICE_NUMBER RECEIPT_ADVICE_LINE_NUMBER CST_WORK_ORDER_ID WORK_ORDER_OPERATION_ID WORK_CENTER_ID PO_HEADER_ID PO_LINE_ID ASSEMBLY_ASSET_ID

Name

TRANSACTION_COST_ID

COST_ID

SOURCE_TABLE

COST_ELEMENT_ID

EXPENSE_POOL_ID

ABSORPTION_TYPE

ADJUSTMENT_TRANSACTION_ID

USER_ADJUSTMENT_ID

OVERHEAD_ID

TRANSACTION_ID

COST_ORG_ID

COST_BOOK_ID

VAL_STRUCTURE_ID

STRUCTURE_INSTANCE_NUMBER

VAL_UNIT_ID

VAL_UNIT_COMBINATION_ID

INVENTORY_ITEM_ID

INV_ORG_ID

ITEM_VALIDATION_ORG_ID

LOGICAL_FLAG

BASE_TXN_TYPE_ID

BASE_TXN_SOURCE_TYPE_ID

COST_PROFILE_ID

EXPENSE_TRANSACTION_FLAG

COST_METHOD_CODE

NEGATIVE_QUANTITY_CODE

QUANTITY_FLOW_CODE

UOM_CODE

CST_INV_TRANSACTION_DTL_ID

OWNING_COST_ORG_ID

TRANSFER_COST_ORG_ID

TRANSFER_BOOK_ID

TRANSFER_VAL_UNIT_ID

PRICING_OPTION

FOB_POINT

TXN_SOURCE_DOC_TYPE

TXN_SOURCE_DOC_NUMBER

TXN_SOURCE_REF_DOC_TYPE

TXN_SOURCE_REF_DOC_NUMBER

BUSINESS_UNIT_ID

TRANSACTION_FLOW_TYPE

INTRANSIT_FLAG

INTERNAL_PROFIT_TRACKING

CST_INV_TRANSACTION_ID

EXTERNAL_SYSTEM_REFERENCE

EXTERNAL_SYSTEM_REF_ID

SUBINVENTORY_CODE

LOCATOR_ID

REASON_ID

TRANSFER_INV_ORG_ID

TRANSFER_SUBINVENTORY_CODE

TRANSFER_LOCATOR_ID

INTERCOMPANY_INVOICING_FLAG

RCV_TRANSACTION_ID

PO_DISTRIBUTION_ID

WSH_DELIVERY_DETAIL_ID

DOO_FULLFILL_LINE_ID

TRANSACTION_ATTRIBUTE_CATEGORY

TRANSACTION_ATTRIBUTE1

TRANSACTION_ATTRIBUTE2

TRANSACTION_ATTRIBUTE3

TRANSACTION_ATTRIBUTE4

TRANSACTION_ATTRIBUTE5

TRANSACTION_ATTRIBUTE6

TRANSACTION_ATTRIBUTE7

TRANSACTION_ATTRIBUTE8

TRANSACTION_ATTRIBUTE9

TRANSACTION_ATTRIBUTE10

TRANSACTION_ATTRIBUTE11

TRANSACTION_ATTRIBUTE12

TRANSACTION_ATTRIBUTE13

TRANSACTION_ATTRIBUTE14

TRANSACTION_ATTRIBUTE15

TRANSACTION_ATTRIBUTE16

TRANSACTION_ATTRIBUTE17

TRANSACTION_ATTRIBUTE18

TRANSACTION_ATTRIBUTE19

TRANSACTION_ATTRIBUTE20

TRANSACTION_ATTRIBUTE21

TRANSACTION_ATTRIBUTE22

TRANSACTION_ATTRIBUTE23

TRANSACTION_ATTRIBUTE24

TRANSACTION_ATTRIBUTE25

TRANSACTION_ATTRIBUTE26

TRANSACTION_ATTRIBUTE27

TRANSACTION_ATTRIBUTE28

TRANSACTION_ATTRIBUTE29

TRANSACTION_ATTRIBUTE30

TRANSACTION_UOM_CODE

PRIMARY_UOM_CODE

SECONDARY_TRANSACTION_UOM_CODE

SOURCE_SALES_ORDER_NUMBER

SOURCE_SALES_ORDER_LINE_NUMBER

SALES_ORDER_SOURCE_SYSTEM

SOURCE_SO_SHIPMENT_NUMBER

RECEIPT_ADVICE_NUMBER

RECEIPT_ADVICE_LINE_NUMBER

CST_WORK_ORDER_ID

WORK_ORDER_OPERATION_ID

WORK_CENTER_ID

PO_HEADER_ID

PO_LINE_ID

ASSEMBLY_ASSET_ID

Query

SQL_Statement
SELECT ctc.transaction_cost_id transaction_cost_id, ctc.cost_id cost_id, ctc.source_table source_table, ctc.cost_element_id cost_element_id, ctc.expense_pool_id expense_pool_id, ctc.absorption_type absorption_type, ctc.adjustment_transaction_id adjustment_transaction_id, ctc.user_adjustment_id user_adjustment_id, ctc.overhead_id overhead_id, ct.transaction_id transaction_id, ct.cost_org_id cost_org_id, ct.cost_book_id cost_book_id, ct.val_structure_id val_structure_id, ct.structure_instance_number structure_instance_number, ct.val_unit_id val_unit_id, ct.val_unit_combination_id val_unit_combination_id, ct.inventory_item_id inventory_item_id, ct.inventory_org_id inv_org_id, CASE WHEN ct.intransit_flag = 'Y' THEN( SELECT iop.master_organization_id FROM inv_org_parameters iop WHERE iop.organization_id = ct.inventory_org_id ) ELSE ct.inventory_org_id END item_validation_org_id, ct.logical_flag logical_flag, ct.base_txn_type_id base_txn_type_id, ct.base_txn_source_type_id base_txn_source_type_id, ct.cost_profile_id cost_profile_id, ct.expense_transaction_flag expense_transaction_flag, ct.cost_method_code cost_method_code, ct.negative_qty_code negative_quantity_code, ct.quantity_flow_code quantity_flow_code, ct.uom_code uom_code, ct.cst_inv_transaction_dtl_id cst_inv_transaction_dtl_id, ct.owning_cost_org_id owning_cost_org_id, ct.transfer_cost_org_id transfer_cost_org_id, ct.transfer_book_id transfer_book_id, ct.transfer_val_unit_id transfer_val_unit_id, ct.pricing_option pricing_option, ct.fob_point fob_point, ct.txn_source_doc_type txn_source_doc_type, ct.txn_source_doc_number txn_source_doc_number, ct.txn_source_ref_doc_type txn_source_ref_doc_type, ct.txn_source_ref_doc_number txn_source_ref_doc_number, ct.business_unit_id business_unit_id, ct.transaction_flow_type transaction_flow_type, ct.intransit_flag intransit_flag, ct.internal_profit_tracking internal_profit_tracking, cit.cst_inv_transaction_id cst_inv_transaction_id, cit.external_system_reference external_system_reference, cit.external_system_ref_id external_system_ref_id, cit.subinventory_code subinventory_code, cit.locator_id locator_id, cit.transaction_reason_id reason_id, cit.transfer_inventory_org_id transfer_inv_org_id, cit.transfer_subinventory_code transfer_subinventory_code, cit.transfer_locator_id transfer_locator_id, cit.intercompany_invoicing_flag intercompany_invoicing_flag, cit.rcv_transaction_id rcv_transaction_id, cit.po_distribution_id po_distribution_id, cit.wsh_delivery_detail_id wsh_delivery_detail_id, cit.doo_fullfill_line_id doo_fullfill_line_id, cit.attribute_category transaction_attribute_category, cit.attribute1 transaction_attribute1, cit.attribute2 transaction_attribute2, cit.attribute3 transaction_attribute3, cit.attribute4 transaction_attribute4, cit.attribute5 transaction_attribute5, cit.attribute6 transaction_attribute6, cit.attribute7 transaction_attribute7, cit.attribute8 transaction_attribute8, cit.attribute9 transaction_attribute9, cit.attribute10 transaction_attribute10, cit.attribute11 transaction_attribute11, cit.attribute12 transaction_attribute12, cit.attribute13 transaction_attribute13, cit.attribute14 transaction_attribute14, cit.attribute15 transaction_attribute15, cit.attribute16 transaction_attribute16, cit.attribute17 transaction_attribute17, cit.attribute18 transaction_attribute18, cit.attribute19 transaction_attribute19, cit.attribute20 transaction_attribute20, cit.attribute21 transaction_attribute21, cit.attribute22 transaction_attribute22, cit.attribute23 transaction_attribute23, cit.attribute24 transaction_attribute24, cit.attribute25 transaction_attribute25, cit.attribute26 transaction_attribute26, cit.attribute27 transaction_attribute27, cit.attribute28 transaction_attribute28, cit.attribute29 transaction_attribute29, cit.attribute30 transaction_attribute30, cit.transaction_uom_code transaction_uom_code, cit.primary_uom_code primary_uom_code, cit.secondary_transaction_uom_code secondary_transaction_uom_code, cit.source_sales_order_number source_sales_order_number, cit.source_sales_order_line_number source_sales_order_line_number, cit.sales_order_source_system sales_order_source_system, cit.source_so_shipment_number source_so_shipment_number, cit.receipt_advice_number receipt_advice_number, cit.receipt_advice_line_number receipt_advice_line_number, ct.cst_work_order_id cst_work_order_id, ct.cst_work_order_operation_id work_order_operation_id, ct.work_center_id work_center_id, ( SELECT pd.po_header_id FROM po_distributions_all pd WHERE pd.po_distribution_id = ct.po_distribution_id ) po_header_id, ( SELECT pd.po_line_id FROM po_distributions_all pd WHERE pd.po_distribution_id = ct.po_distribution_id ) po_line_id, case when ct.assembly_asset_id is not null then ct.assembly_asset_id when ct.cst_work_order_id is not null then (select min(wwa.asset_id) asset_id from wie_wo_assets wwa, cst_work_orders cwo where cwo.cst_work_order_id = ct.cst_work_order_id and cwo.external_system_ref_id = wwa.work_order_id and nvl(wwa.route_asset_flag,'N') = 'N') else null end assembly_asset_id FROM( SELECT 'cst_layer_costs' source_table, clc.layer_cost_id cost_id, clc.transaction_id, clc.transaction_cost_id, clc.cost_element_id, clc.expense_pool_id, clc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_layer_costs clc, cst_transaction_costs ctc WHERE clc.transaction_cost_id = ctc.transaction_cost_id(+) UNION ALL SELECT 'cst_writeoff_costs' source_table, cwc.writeoff_cost_id cost_id, cwc.transaction_id, cwc.transaction_cost_id, cwc.cost_element_id, cwc.expense_pool_id, cwc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_writeoff_costs cwc, cst_transaction_costs ctc WHERE cwc.transaction_cost_id = ctc.transaction_cost_id(+) UNION ALL SELECT 'cst_variance_costs' source_table, cvc.variance_cost_id cost_id, cvc.transaction_id, cvc.transaction_cost_id, cvc.cost_element_id, cvc.expense_pool_id, cvc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_variance_costs cvc, cst_transaction_costs ctc WHERE cvc.transaction_cost_id = ctc.transaction_cost_id(+) UNION ALL SELECT 'cst_transfer_costs' source_table, cxc.transfer_cost_id cost_id, cxc.transaction_id, cxc.transaction_cost_id, cxc.cost_element_id, cxc.expense_pool_id, cxc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_transfer_costs cxc, cst_transaction_costs ctc WHERE cxc.transaction_cost_id = ctc.transaction_cost_id(+) UNION ALL SELECT 'cst_resource_costs' source_table, crc.resource_cost_id cost_id, crc.resource_transaction_id transaction_id, 0 transaction_cost_id, crc.cost_element_id, crc.expense_pool_id, NULL absorption_type, NULL adjustment_transaction_id, NULL user_adjustment_id, NULL overhead_id FROM cst_resource_costs crc UNION ALL SELECT 'cst_operation_txn_costs' source_table, cotc.operation_txn_cost_id cost_id, cotc.operation_transaction_id transaction_id, 0 transaction_cost_id, cotc.cost_element_id, cotc.expense_pool_id, cotc.absorption_type, NULL adjustment_transaction_id, NULL user_adjustment_id, NULL overhead_id FROM cst_operation_txn_costs cotc UNION ALL SELECT 'cst_wo_cost_variances' source_table, cwov.wo_cost_variance_id cost_id, cwov.wo_update_event_txn_id transaction_id, 0 transaction_cost_id, cwov.cost_element_id, NULL expense_pool_id, NULL absorption_type, NULL adjustment_transaction_id, NULL user_adjustment_id, NULL overhead_id FROM cst_wo_cost_variances cwov UNION ALL SELECT 'cst_cogs_transactions' source_table, cclc.cogs_layer_cost_id cost_id, clc.transaction_id, clc.transaction_cost_id, clc.cost_element_id, clc.expense_pool_id, clc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_cogs_layer_costs cclc, cst_layer_costs clc, cst_transaction_costs ctc WHERE cclc.cst_layer_cost_id = clc.layer_cost_id AND cclc.cst_transaction_id = clc.transaction_id AND clc.transaction_cost_id = ctc.transaction_cost_id(+) UNION ALL SELECT 'cst_cogs_transactions' source_table, cclc.cogs_layer_cost_id cost_id, cxc.transaction_id, cxc.transaction_cost_id, cxc.cost_element_id, cxc.expense_pool_id, cxc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_cogs_layer_costs cclc, cst_transfer_costs cxc, cst_transaction_costs ctc WHERE cclc.cst_layer_cost_id = cxc.transfer_cost_id AND cclc.cst_transaction_id = cxc.transaction_id AND cxc.transaction_cost_id = ctc.transaction_cost_id(+) UNION ALL SELECT 'cst_cogs_transactions' source_table, ccvc.cogs_variance_cost_id cost_id, cvc.transaction_id, cvc.transaction_cost_id, cvc.cost_element_id, cvc.expense_pool_id, cvc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_cogs_variance_costs ccvc, cst_variance_costs cvc, cst_transaction_costs ctc WHERE ccvc.cst_variance_cost_id = cvc.variance_cost_id AND ccvc.cst_transaction_id = cvc.transaction_id AND cvc.transaction_cost_id = ctc.transaction_cost_id(+) UNION ALL SELECT 'cst_wo_osp_costs' source_table, cwoc.wo_osp_cost_id cost_id, cwoc.transaction_id, cwoc.transaction_cost_id, cwoc.cost_element_id, cwoc.expense_pool_id, cwoc.absorption_type, ctc.adjustment_transaction_id, ctc.user_adjustment_id, ctc.overhead_id FROM cst_wo_osp_costs cwoc, cst_transaction_costs ctc WHERE cwoc.transaction_cost_id = ctc.transaction_cost_id(+) )ctc, cst_transactions ct, cst_inv_transactions cit WHERE ct.transaction_id(+)= ctc.transaction_id AND cit.cst_inv_transaction_id(+)= ct.cst_inv_transaction_id UNION ALL SELECT cat.acctg_only_txns_id transaction_cost_id, cat.acctg_only_txns_id cost_id, 'cst_acctg_only_txns' source_table, NULL cost_element_id, NULL expense_pool_id, NULL absorption_type, NULL adjustment_transaction_id, NULL user_adjustment_id, NULL overhead_id, cat.acctg_only_txns_id transaction_id, cat.cost_org_id cost_org_id, cat.cost_book_id cost_book_id, NULL val_structure_id, NULL structure_instance_number, NULL val_unit_id, NULL val_unit_combination_id, cat.inventory_item_id inventory_item_id, cat.inventory_org_id inv_org_id, cat.inventory_org_id item_validation_org_id, NULL logical_flag, 10 base_txn_type_id, 0 base_txn_source_type_id, NULL cost_profile_id, NULL expense_transaction_flag, NULL cost_method_code, NULL negative_quantity_code, NULL quantity_flow_code, crt.transaction_uom_code uom_code, NULL cst_inv_transaction_dtl_id, NULL owning_cost_org_id, NULL transfer_cost_org_id, NULL transfer_book_id, NULL transfer_val_unit_id, NULL pricing_option, NULL fob_point, NULL txn_source_doc_type, NULL txn_source_doc_number, NULL txn_source_ref_doc_type, NULL txn_source_ref_doc_number, iop.profit_center_bu_id business_unit_id, NULL transaction_flow_type, NULL intransit_flag, NULL internal_profit_tracking, NULL cst_inv_transaction_id, NULL external_system_reference, NULL external_system_ref_id, NULL subinventory_code, NULL locator_id, NULL reason_id, NULL transfer_inv_org_id, NULL transfer_subinventory_code, NULL transfer_locator_id, NULL intercompany_invoicing_flag, NULL rcv_transaction_id, CAST(cpod.external_system_ref_id AS NUMBER) po_distribution_id, NULL wsh_delivery_detail_id, NULL doo_fullfill_line_id, NULL transaction_attribute_category, NULL transaction_attribute1, NULL transaction_attribute2, NULL transaction_attribute3, NULL transaction_attribute4, NULL transaction_attribute5, NULL transaction_attribute6, NULL transaction_attribute7, NULL transaction_attribute8, NULL transaction_attribute9, NULL transaction_attribute10, NULL transaction_attribute11, NULL transaction_attribute12, NULL transaction_attribute13, NULL transaction_attribute14, NULL transaction_attribute15, NULL transaction_attribute16, NULL transaction_attribute17, NULL transaction_attribute18, NULL transaction_attribute19, NULL transaction_attribute20, NULL transaction_attribute21, NULL transaction_attribute22, NULL transaction_attribute23, NULL transaction_attribute24, NULL transaction_attribute25, NULL transaction_attribute26, NULL transaction_attribute27, NULL transaction_attribute28, NULL transaction_attribute29, NULL transaction_attribute30, crt.transaction_uom_code transaction_uom_code, crt.primary_uom_code primary_uom_code, NULL secondary_transaction_uom_code, NULL source_sales_order_number, NULL source_sales_order_line_number, NULL sales_order_source_system, NULL source_so_shipment_number, NULL receipt_advice_number, NULL receipt_advice_line_number, NULL cst_work_order_id, NULL work_order_operation_id, NULL work_center_id, CAST( cpod.po_header_id AS NUMBER( 18 )) po_header_id, CAST( cpod.po_line_id AS NUMBER( 18 )) po_line_id, null assembly_asset_id FROM cst_acctg_only_txns cat, cst_invoice_to_exp_txns citet, cmr_rcv_transactions crt, inv_org_parameters iop, cmr_purchase_order_dtls cpod WHERE citet.invoice_to_exp_txn_id = cat.invoice_to_exp_txn_id AND citet.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id(+) AND cpod.cmr_po_distribution_id(+) = citet.cmr_po_distribution_id AND cpod.active_flag(+) = 'Y' AND iop.organization_id = cat.inventory_org_id

SQL_Statement

SELECT ctc.transaction_cost_id transaction_cost_id,

ctc.cost_id cost_id,

ctc.source_table source_table,

ctc.cost_element_id cost_element_id,

ctc.expense_pool_id expense_pool_id,

ctc.absorption_type absorption_type,

ctc.adjustment_transaction_id adjustment_transaction_id,

ctc.user_adjustment_id user_adjustment_id,

ctc.overhead_id overhead_id,

ct.transaction_id transaction_id,

ct.cost_org_id cost_org_id,

ct.cost_book_id cost_book_id,

ct.val_structure_id val_structure_id,

ct.structure_instance_number structure_instance_number,

ct.val_unit_id val_unit_id,

ct.val_unit_combination_id val_unit_combination_id,

ct.inventory_item_id inventory_item_id,

ct.inventory_org_id inv_org_id,

CASE

WHEN ct.intransit_flag = 'Y' THEN(

SELECT iop.master_organization_id

FROM inv_org_parameters iop

WHERE iop.organization_id = ct.inventory_org_id

)

ELSE ct.inventory_org_id

END item_validation_org_id,

ct.logical_flag logical_flag,

ct.base_txn_type_id base_txn_type_id,

ct.base_txn_source_type_id base_txn_source_type_id,

ct.cost_profile_id cost_profile_id,

ct.expense_transaction_flag expense_transaction_flag,

ct.cost_method_code cost_method_code,

ct.negative_qty_code negative_quantity_code,

ct.quantity_flow_code quantity_flow_code,

ct.uom_code uom_code,

ct.cst_inv_transaction_dtl_id cst_inv_transaction_dtl_id,

ct.owning_cost_org_id owning_cost_org_id,

ct.transfer_cost_org_id transfer_cost_org_id,

ct.transfer_book_id transfer_book_id,

ct.transfer_val_unit_id transfer_val_unit_id,

ct.pricing_option pricing_option,

ct.fob_point fob_point,

ct.txn_source_doc_type txn_source_doc_type,

ct.txn_source_doc_number txn_source_doc_number,

ct.txn_source_ref_doc_type txn_source_ref_doc_type,

ct.txn_source_ref_doc_number txn_source_ref_doc_number,

ct.business_unit_id business_unit_id,

ct.transaction_flow_type transaction_flow_type,

ct.intransit_flag intransit_flag,

ct.internal_profit_tracking internal_profit_tracking,

cit.cst_inv_transaction_id cst_inv_transaction_id,

cit.external_system_reference external_system_reference,

cit.external_system_ref_id external_system_ref_id,

cit.subinventory_code subinventory_code,

cit.locator_id locator_id,

cit.transaction_reason_id reason_id,

cit.transfer_inventory_org_id transfer_inv_org_id,

cit.transfer_subinventory_code transfer_subinventory_code,

cit.transfer_locator_id transfer_locator_id,

cit.intercompany_invoicing_flag intercompany_invoicing_flag,

cit.rcv_transaction_id rcv_transaction_id,

cit.po_distribution_id po_distribution_id,

cit.wsh_delivery_detail_id wsh_delivery_detail_id,

cit.doo_fullfill_line_id doo_fullfill_line_id,

cit.attribute_category transaction_attribute_category,

cit.attribute1 transaction_attribute1,

cit.attribute2 transaction_attribute2,

cit.attribute3 transaction_attribute3,

cit.attribute4 transaction_attribute4,

cit.attribute5 transaction_attribute5,

cit.attribute6 transaction_attribute6,

cit.attribute7 transaction_attribute7,

cit.attribute8 transaction_attribute8,

cit.attribute9 transaction_attribute9,

cit.attribute10 transaction_attribute10,

cit.attribute11 transaction_attribute11,

cit.attribute12 transaction_attribute12,

cit.attribute13 transaction_attribute13,

cit.attribute14 transaction_attribute14,

cit.attribute15 transaction_attribute15,

cit.attribute16 transaction_attribute16,

cit.attribute17 transaction_attribute17,

cit.attribute18 transaction_attribute18,

cit.attribute19 transaction_attribute19,

cit.attribute20 transaction_attribute20,

cit.attribute21 transaction_attribute21,

cit.attribute22 transaction_attribute22,

cit.attribute23 transaction_attribute23,

cit.attribute24 transaction_attribute24,

cit.attribute25 transaction_attribute25,

cit.attribute26 transaction_attribute26,

cit.attribute27 transaction_attribute27,

cit.attribute28 transaction_attribute28,

cit.attribute29 transaction_attribute29,

cit.attribute30 transaction_attribute30,

cit.transaction_uom_code transaction_uom_code,

cit.primary_uom_code primary_uom_code,

cit.secondary_transaction_uom_code secondary_transaction_uom_code,

cit.source_sales_order_number source_sales_order_number,

cit.source_sales_order_line_number source_sales_order_line_number,

cit.sales_order_source_system sales_order_source_system,

cit.source_so_shipment_number source_so_shipment_number,

cit.receipt_advice_number receipt_advice_number,

cit.receipt_advice_line_number receipt_advice_line_number,

ct.cst_work_order_id cst_work_order_id,

ct.cst_work_order_operation_id work_order_operation_id,

ct.work_center_id work_center_id,

(

SELECT pd.po_header_id

FROM po_distributions_all pd

WHERE pd.po_distribution_id = ct.po_distribution_id

) po_header_id,

(

SELECT pd.po_line_id

FROM po_distributions_all pd

WHERE pd.po_distribution_id = ct.po_distribution_id

) po_line_id,

case when ct.assembly_asset_id is not null then ct.assembly_asset_id

when ct.cst_work_order_id is not null then

(select min(wwa.asset_id) asset_id

from wie_wo_assets wwa, cst_work_orders cwo

where cwo.cst_work_order_id = ct.cst_work_order_id

and cwo.external_system_ref_id = wwa.work_order_id

and nvl(wwa.route_asset_flag,'N') = 'N')

else null end assembly_asset_id

FROM(

SELECT 'cst_layer_costs' source_table,

clc.layer_cost_id cost_id,

clc.transaction_id,

clc.transaction_cost_id,

clc.cost_element_id,

clc.expense_pool_id,

clc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_layer_costs clc,

cst_transaction_costs ctc

WHERE clc.transaction_cost_id = ctc.transaction_cost_id(+)

UNION ALL

SELECT 'cst_writeoff_costs' source_table,

cwc.writeoff_cost_id cost_id,

cwc.transaction_id,

cwc.transaction_cost_id,

cwc.cost_element_id,

cwc.expense_pool_id,

cwc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_writeoff_costs cwc,

cst_transaction_costs ctc

WHERE cwc.transaction_cost_id = ctc.transaction_cost_id(+)

UNION ALL

SELECT 'cst_variance_costs' source_table,

cvc.variance_cost_id cost_id,

cvc.transaction_id,

cvc.transaction_cost_id,

cvc.cost_element_id,

cvc.expense_pool_id,

cvc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_variance_costs cvc,

cst_transaction_costs ctc

WHERE cvc.transaction_cost_id = ctc.transaction_cost_id(+)

UNION ALL

SELECT 'cst_transfer_costs' source_table,

cxc.transfer_cost_id cost_id,

cxc.transaction_id,

cxc.transaction_cost_id,

cxc.cost_element_id,

cxc.expense_pool_id,

cxc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_transfer_costs cxc,

cst_transaction_costs ctc

WHERE cxc.transaction_cost_id = ctc.transaction_cost_id(+)

UNION ALL

SELECT 'cst_resource_costs' source_table,

crc.resource_cost_id cost_id,

crc.resource_transaction_id transaction_id,

0 transaction_cost_id,

crc.cost_element_id,

crc.expense_pool_id,

NULL absorption_type,

NULL adjustment_transaction_id,

NULL user_adjustment_id,

NULL overhead_id

FROM cst_resource_costs crc

UNION ALL

SELECT 'cst_operation_txn_costs' source_table,

cotc.operation_txn_cost_id cost_id,

cotc.operation_transaction_id transaction_id,

0 transaction_cost_id,

cotc.cost_element_id,

cotc.expense_pool_id,

cotc.absorption_type,

NULL adjustment_transaction_id,

NULL user_adjustment_id,

NULL overhead_id

FROM cst_operation_txn_costs cotc

UNION ALL

SELECT 'cst_wo_cost_variances' source_table,

cwov.wo_cost_variance_id cost_id,

cwov.wo_update_event_txn_id transaction_id,

0 transaction_cost_id,

cwov.cost_element_id,

NULL expense_pool_id,

NULL absorption_type,

NULL adjustment_transaction_id,

NULL user_adjustment_id,

NULL overhead_id

FROM cst_wo_cost_variances cwov

UNION ALL

SELECT 'cst_cogs_transactions' source_table,

cclc.cogs_layer_cost_id cost_id,

clc.transaction_id,

clc.transaction_cost_id,

clc.cost_element_id,

clc.expense_pool_id,

clc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_cogs_layer_costs cclc,

cst_layer_costs clc,

cst_transaction_costs ctc

WHERE cclc.cst_layer_cost_id = clc.layer_cost_id

AND cclc.cst_transaction_id = clc.transaction_id

AND clc.transaction_cost_id = ctc.transaction_cost_id(+)

UNION ALL

SELECT 'cst_cogs_transactions' source_table,

cclc.cogs_layer_cost_id cost_id,

cxc.transaction_id,

cxc.transaction_cost_id,

cxc.cost_element_id,

cxc.expense_pool_id,

cxc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_cogs_layer_costs cclc,

cst_transfer_costs cxc,

cst_transaction_costs ctc

WHERE cclc.cst_layer_cost_id = cxc.transfer_cost_id

AND cclc.cst_transaction_id = cxc.transaction_id

AND cxc.transaction_cost_id = ctc.transaction_cost_id(+)

UNION ALL

SELECT 'cst_cogs_transactions' source_table,

ccvc.cogs_variance_cost_id cost_id,

cvc.transaction_id,

cvc.transaction_cost_id,

cvc.cost_element_id,

cvc.expense_pool_id,

cvc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_cogs_variance_costs ccvc,

cst_variance_costs cvc,

cst_transaction_costs ctc

WHERE ccvc.cst_variance_cost_id = cvc.variance_cost_id

AND ccvc.cst_transaction_id = cvc.transaction_id

AND cvc.transaction_cost_id = ctc.transaction_cost_id(+)

UNION ALL

SELECT 'cst_wo_osp_costs' source_table,

cwoc.wo_osp_cost_id cost_id,

cwoc.transaction_id,

cwoc.transaction_cost_id,

cwoc.cost_element_id,

cwoc.expense_pool_id,

cwoc.absorption_type,

ctc.adjustment_transaction_id,

ctc.user_adjustment_id,

ctc.overhead_id

FROM cst_wo_osp_costs cwoc,

cst_transaction_costs ctc

WHERE cwoc.transaction_cost_id = ctc.transaction_cost_id(+)

)ctc,

cst_transactions ct,

cst_inv_transactions cit

WHERE ct.transaction_id(+)= ctc.transaction_id

AND cit.cst_inv_transaction_id(+)= ct.cst_inv_transaction_id

UNION ALL

SELECT cat.acctg_only_txns_id transaction_cost_id,

cat.acctg_only_txns_id cost_id,

'cst_acctg_only_txns' source_table,

NULL cost_element_id,

NULL expense_pool_id,

NULL absorption_type,

NULL adjustment_transaction_id,

NULL user_adjustment_id,

NULL overhead_id,

cat.acctg_only_txns_id transaction_id,

cat.cost_org_id cost_org_id,

cat.cost_book_id cost_book_id,

NULL val_structure_id,

NULL structure_instance_number,

NULL val_unit_id,

NULL val_unit_combination_id,

cat.inventory_item_id inventory_item_id,

cat.inventory_org_id inv_org_id,

cat.inventory_org_id item_validation_org_id,

NULL logical_flag,

10 base_txn_type_id,

0 base_txn_source_type_id,

NULL cost_profile_id,

NULL expense_transaction_flag,

NULL cost_method_code,

NULL negative_quantity_code,

NULL quantity_flow_code,

crt.transaction_uom_code uom_code,

NULL cst_inv_transaction_dtl_id,

NULL owning_cost_org_id,

NULL transfer_cost_org_id,

NULL transfer_book_id,

NULL transfer_val_unit_id,

NULL pricing_option,

NULL fob_point,

NULL txn_source_doc_type,

NULL txn_source_doc_number,

NULL txn_source_ref_doc_type,

NULL txn_source_ref_doc_number,

iop.profit_center_bu_id business_unit_id,

NULL transaction_flow_type,

NULL intransit_flag,

NULL internal_profit_tracking,

NULL cst_inv_transaction_id,

NULL external_system_reference,

NULL external_system_ref_id,

NULL subinventory_code,

NULL locator_id,

NULL reason_id,

NULL transfer_inv_org_id,

NULL transfer_subinventory_code,

NULL transfer_locator_id,

NULL intercompany_invoicing_flag,

NULL rcv_transaction_id,

CAST(cpod.external_system_ref_id AS NUMBER) po_distribution_id,

NULL wsh_delivery_detail_id,

NULL doo_fullfill_line_id,

NULL transaction_attribute_category,

NULL transaction_attribute1,

NULL transaction_attribute2,

NULL transaction_attribute3,

NULL transaction_attribute4,

NULL transaction_attribute5,

NULL transaction_attribute6,

NULL transaction_attribute7,

NULL transaction_attribute8,

NULL transaction_attribute9,

NULL transaction_attribute10,

NULL transaction_attribute11,

NULL transaction_attribute12,

NULL transaction_attribute13,

NULL transaction_attribute14,

NULL transaction_attribute15,

NULL transaction_attribute16,

NULL transaction_attribute17,

NULL transaction_attribute18,

NULL transaction_attribute19,

NULL transaction_attribute20,

NULL transaction_attribute21,

NULL transaction_attribute22,

NULL transaction_attribute23,

NULL transaction_attribute24,

NULL transaction_attribute25,

NULL transaction_attribute26,

NULL transaction_attribute27,

NULL transaction_attribute28,

NULL transaction_attribute29,

NULL transaction_attribute30,

crt.transaction_uom_code transaction_uom_code,

crt.primary_uom_code primary_uom_code,

NULL secondary_transaction_uom_code,

NULL source_sales_order_number,

NULL source_sales_order_line_number,

NULL sales_order_source_system,

NULL source_so_shipment_number,

NULL receipt_advice_number,

NULL receipt_advice_line_number,

NULL cst_work_order_id,

NULL work_order_operation_id,

NULL work_center_id,

CAST( cpod.po_header_id AS NUMBER( 18 )) po_header_id,

CAST( cpod.po_line_id AS NUMBER( 18 )) po_line_id,

null assembly_asset_id

FROM cst_acctg_only_txns cat,

cst_invoice_to_exp_txns citet,

cmr_rcv_transactions crt,

inv_org_parameters iop,

cmr_purchase_order_dtls cpod

WHERE citet.invoice_to_exp_txn_id = cat.invoice_to_exp_txn_id

AND citet.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id(+)

AND cpod.cmr_po_distribution_id(+) = citet.cmr_po_distribution_id

AND cpod.active_flag(+) = 'Y'

AND iop.organization_id = cat.inventory_org_id