CST_XLA_TRANSACTIONS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
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_ID 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 PO_DISTRIBUTION_ID DOO_FULLFILL_LINE_ID CST_WORK_ORDER_ID CST_WORK_ORDER_OPERATION_ID WORK_ORDER_SUPPLY_TYPE WORK_CENTER_ID SCRAP_COST_TYPE PROVISIONAL_COMPLETION_TYPE SUBINVENTORY_CODE LOCATOR_ID REASON_ID SOURCE_SALES_ORDER_NUMBER SOURCE_SALES_ORDER_LINE_NUMBER SALES_ORDER_SOURCE_SYSTEM SOURCE_SO_SHIPMENT_NUMBER RECEIPT_ADVICE_NUMBER RECEIPT_ADVICE_LINE_NUMBER PRIOR_INVENTORY_ORG_ID PRIOR_BUSINESS_UNIT_ID PRIOR_COST_ORG_ID SUCCESSOR_INVENTORY_ORG_ID SUCCESSOR_BUSINESS_UNIT_ID EXTERNAL_SYSTEM_REF_ID SUCCESSOR_COST_ORG_ID REVENUE_ACCOUNT PO_HEADER_ID PO_LINE_ID CUSTOMER_TRX_ID CUSTOMER_TRX_LINE_ID CUST_TRX_LINE_GL_DIST_ID TO_LINE_ID DESCRIPTION_ITEM_FLAG REVENUE_RECOGNITION_SOURCE SUPPLIER_RETURN_HEADER_ID DISPOSITION_ID VRM_SRC_DOCUMENT_ID VRM_SRC_DOCUMENT_LINE_ID ASSEMBLY_ASSET_ID

Name

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_ID

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

PO_DISTRIBUTION_ID

DOO_FULLFILL_LINE_ID

CST_WORK_ORDER_ID

CST_WORK_ORDER_OPERATION_ID

WORK_ORDER_SUPPLY_TYPE

WORK_CENTER_ID

SCRAP_COST_TYPE

PROVISIONAL_COMPLETION_TYPE

SUBINVENTORY_CODE

LOCATOR_ID

REASON_ID

SOURCE_SALES_ORDER_NUMBER

SOURCE_SALES_ORDER_LINE_NUMBER

SALES_ORDER_SOURCE_SYSTEM

SOURCE_SO_SHIPMENT_NUMBER

RECEIPT_ADVICE_NUMBER

RECEIPT_ADVICE_LINE_NUMBER

PRIOR_INVENTORY_ORG_ID

PRIOR_BUSINESS_UNIT_ID

PRIOR_COST_ORG_ID

SUCCESSOR_INVENTORY_ORG_ID

SUCCESSOR_BUSINESS_UNIT_ID

EXTERNAL_SYSTEM_REF_ID

SUCCESSOR_COST_ORG_ID

REVENUE_ACCOUNT

PO_HEADER_ID

PO_LINE_ID

CUSTOMER_TRX_ID

CUSTOMER_TRX_LINE_ID

CUST_TRX_LINE_GL_DIST_ID

TO_LINE_ID

DESCRIPTION_ITEM_FLAG

REVENUE_RECOGNITION_SOURCE

SUPPLIER_RETURN_HEADER_ID

DISPOSITION_ID

VRM_SRC_DOCUMENT_ID

VRM_SRC_DOCUMENT_LINE_ID

ASSEMBLY_ASSET_ID

Query

SQL_Statement
SELECT 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, nvl(ct.inv_txn_source_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, DECODE(ct.uom_code, ' X ', NULL, ct.uom_code) uom_code, ct.cst_inv_transaction_id cst_inv_transaction_id, 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, ct.po_distribution_id po_distribution_id, ct.doo_fullfill_line_id doo_fullfill_line_id, ct.cst_work_order_id cst_work_order_id, ct.cst_work_order_operation_id cst_work_order_operation_id, ct.supply_type work_order_supply_type, ct.work_center_id work_center_id, ct.operation_scrap_cost_type scrap_cost_type, ct.provisional_completion_type provisional_completion_type, cit.subinventory_code subinventory_code, cit.locator_id locator_id, cit.transaction_reason_id reason_id, 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, cit.prior_inventory_org_id prior_inventory_org_id, cit.prior_business_unit_id prior_business_unit_id, ccioa.cost_org_id prior_cost_org_id, cit.successor_inventory_org_id successor_inventory_org_id, cit.successor_business_unit_id successor_business_unit_id, cit.external_system_ref_id external_system_ref_id, cciob.cost_org_id successor_cost_org_id, - 1 revenue_account, (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, (SELECT DISTINCT crl.customer_trx_id FROM cst_revenue_lines crl, cst_cogs_transactions cct, cst_txn_source_actions ctsa WHERE cct.revenue_line_id = crl.revenue_line_id AND cct.cst_transaction_id = ct.transaction_id AND crl.external_system_reference = 'FUSION' AND ctsa.cogs_recognition_flag = 'Y' AND ct.base_txn_action_id = ctsa.base_txn_action_id AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id AND ROWNUM=1) customer_trx_id, (SELECT DISTINCT crl.customer_trx_line_id FROM cst_revenue_lines crl, cst_cogs_transactions cct, cst_txn_source_actions ctsa WHERE cct.revenue_line_id = crl.revenue_line_id AND cct.cst_transaction_id = ct.transaction_id AND crl.external_system_reference = 'FUSION' AND ctsa.cogs_recognition_flag = 'Y' AND ct.base_txn_action_id = ctsa.base_txn_action_id AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id AND ROWNUM=1) customer_trx_line_id, CAST(NULL AS NUMBER(18)) cust_trx_line_gl_dist_id, CAST(NVL(ctol.external_system_ref_id,cit.inv_transfer_order_line_id) AS NUMBER(18)) to_line_id, nvl2(ct.po_item_description, 'Y', 'N') description_item_flag, CAST (NULL AS VARCHAR2(60)) revenue_recognition_source, cit.supplier_return_header_id supplier_return_header_id, CASE WHEN cit.base_txn_source_type_id = 6 and cit.base_txn_action_id in (1, 27) THEN (SELECT igd.disposition_id FROM inv_generic_dispositions igd, inv_material_txns imt WHERE igd.disposition_id = imt.transaction_source_id AND igd.organization_id = imt.organization_id AND imt.transaction_id = cit.external_system_ref_id AND cit.external_system_reference = 'FUSION' AND cit.base_txn_source_type_id = 6 AND cit.base_txn_action_id IN ( 1, 27 ) ) ELSE NULL END disposition_id, (SELECT DISTINCT crl.vrm_src_doc_id FROM cst_revenue_lines crl, cst_cogs_transactions cct, cst_txn_source_actions ctsa WHERE cct.revenue_line_id = crl.revenue_line_id AND crl.external_system_reference = 'VRM' AND cct.cst_transaction_id = ct.transaction_id AND ctsa.cogs_recognition_flag = 'Y' AND ct.base_txn_action_id = ctsa.base_txn_action_id AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id AND ROWNUM=1) vrm_src_document_id, (SELECT DISTINCT crl.vrm_src_doc_line_id FROM cst_revenue_lines crl, cst_cogs_transactions cct, cst_txn_source_actions ctsa WHERE cct.revenue_line_id = crl.revenue_line_id AND crl.external_system_reference = 'VRM' AND cct.cst_transaction_id = ct.transaction_id AND ctsa.cogs_recognition_flag = 'Y' AND ct.base_txn_action_id = ctsa.base_txn_action_id AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id AND ROWNUM=1) vrm_src_document_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 cst_transactions ct, cst_inv_transactions cit, cst_cost_inv_orgs ccioa, cst_cost_inv_orgs cciob, cst_transfer_order_lines ctol WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id (+) AND ccioa.inv_org_id (+) = cit.prior_inventory_org_id AND cciob.inv_org_id (+) = cit.successor_inventory_org_id AND ctol.external_system_reference (+) = 'FUSION' AND ctol.cst_transfer_order_line_id (+) = ct.cst_transfer_order_line_id UNION ALL SELECT cct.transaction_id transaction_id, cct.cost_org_id cost_org_id, cct.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, cct.inventory_item_id inventory_item_id, cct.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, nvl(ct.inv_txn_source_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_id cst_inv_transaction_id, 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, - 1 po_distribution_id, cct.shipment_fullfill_line_id doo_fullfill_line_id, ct.cst_work_order_id cst_work_order_id, ct.cst_work_order_operation_id cst_work_order_operation_id, ct.supply_type work_order_supply_type, ct.work_center_id work_center_id, ct.operation_scrap_cost_type scrap_cost_type, ct.provisional_completion_type provisional_completion_type, cit.subinventory_code subinventory_code, cit.locator_id locator_id, cit.transaction_reason_id reason_id, 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, cit.prior_inventory_org_id prior_inventory_org_id, cit.prior_business_unit_id prior_business_unit_id, ccioa.cost_org_id prior_cost_org_id, cit.successor_inventory_org_id successor_inventory_org_id, cit.successor_business_unit_id successor_business_unit_id, cit.external_system_ref_id external_system_ref_id, cciob.cost_org_id successor_cost_org_id, crl.revenue_account revenue_account, CAST(NULL AS NUMBER(18)) po_header_id, CAST(NULL AS NUMBER(18)) po_line_id, crl.customer_trx_id customer_trx_id, crl.customer_trx_line_id customer_trx_line_id, crl.external_system_ref_id cust_trx_line_gl_dist_id, CAST(NULL AS NUMBER(18)) to_line_id, 'N' description_item_flag, crl.external_system_reference revenue_recognition_source, cit.supplier_return_header_id supplier_return_header_id, CAST(NULL as NUMBER(18)) disposition_id, CASE WHEN crl.external_system_reference = 'VRM' THEN crl.VRM_SRC_DOC_ID ELSE CAST(NULL as NUMBER(18)) END vrm_src_document_id, CASE WHEN crl.external_system_reference = 'VRM' THEN crl.VRM_SRC_DOC_LINE_ID ELSE CAST(NULL as NUMBER(18)) END vrm_src_document_line_id, null assembly_asset_id FROM cst_cogs_transactions cct, cst_transactions ct, cst_inv_transactions cit, cst_cost_inv_orgs ccioa, cst_cost_inv_orgs cciob, cst_revenue_lines crl WHERE cct.cst_transaction_id = ct.transaction_id AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id (+) AND ccioa.inv_org_id (+) = cit.prior_inventory_org_id AND cciob.inv_org_id (+) = cit.successor_inventory_org_id AND crl.revenue_line_id (+) = cct.revenue_line_id

SQL_Statement

SELECT

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,

nvl(ct.inv_txn_source_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,

DECODE(ct.uom_code, ' X ', NULL, ct.uom_code) uom_code,

ct.cst_inv_transaction_id cst_inv_transaction_id,

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,

ct.po_distribution_id po_distribution_id,

ct.doo_fullfill_line_id doo_fullfill_line_id,

ct.cst_work_order_id cst_work_order_id,

ct.cst_work_order_operation_id cst_work_order_operation_id,

ct.supply_type work_order_supply_type,

ct.work_center_id work_center_id,

ct.operation_scrap_cost_type scrap_cost_type,

ct.provisional_completion_type provisional_completion_type,

cit.subinventory_code subinventory_code,

cit.locator_id locator_id,

cit.transaction_reason_id reason_id,

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,

cit.prior_inventory_org_id prior_inventory_org_id,

cit.prior_business_unit_id prior_business_unit_id,

ccioa.cost_org_id prior_cost_org_id,

cit.successor_inventory_org_id successor_inventory_org_id,

cit.successor_business_unit_id successor_business_unit_id,

cit.external_system_ref_id external_system_ref_id,

cciob.cost_org_id successor_cost_org_id,

- 1 revenue_account,

(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,

(SELECT DISTINCT crl.customer_trx_id

FROM cst_revenue_lines crl,

cst_cogs_transactions cct,

cst_txn_source_actions ctsa

WHERE cct.revenue_line_id = crl.revenue_line_id

AND cct.cst_transaction_id = ct.transaction_id

AND crl.external_system_reference = 'FUSION'

AND ctsa.cogs_recognition_flag = 'Y'

AND ct.base_txn_action_id = ctsa.base_txn_action_id

AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id

AND ROWNUM=1) customer_trx_id,

(SELECT DISTINCT crl.customer_trx_line_id

FROM cst_revenue_lines crl,

cst_cogs_transactions cct,

cst_txn_source_actions ctsa

WHERE cct.revenue_line_id = crl.revenue_line_id

AND cct.cst_transaction_id = ct.transaction_id

AND crl.external_system_reference = 'FUSION'

AND ctsa.cogs_recognition_flag = 'Y'

AND ct.base_txn_action_id = ctsa.base_txn_action_id

AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id

AND ROWNUM=1) customer_trx_line_id,

CAST(NULL AS NUMBER(18)) cust_trx_line_gl_dist_id,

CAST(NVL(ctol.external_system_ref_id,cit.inv_transfer_order_line_id) AS NUMBER(18)) to_line_id,

nvl2(ct.po_item_description, 'Y', 'N') description_item_flag,

CAST (NULL AS VARCHAR2(60)) revenue_recognition_source,

cit.supplier_return_header_id supplier_return_header_id,

CASE WHEN cit.base_txn_source_type_id = 6 and cit.base_txn_action_id in (1, 27) THEN

(SELECT igd.disposition_id FROM inv_generic_dispositions igd, inv_material_txns imt

WHERE igd.disposition_id = imt.transaction_source_id

AND igd.organization_id = imt.organization_id

AND imt.transaction_id = cit.external_system_ref_id

AND cit.external_system_reference = 'FUSION'

AND cit.base_txn_source_type_id = 6

AND cit.base_txn_action_id IN ( 1, 27 )

) ELSE NULL END disposition_id,

(SELECT DISTINCT crl.vrm_src_doc_id

FROM cst_revenue_lines crl,

cst_cogs_transactions cct,

cst_txn_source_actions ctsa

WHERE cct.revenue_line_id = crl.revenue_line_id

AND crl.external_system_reference = 'VRM'

AND cct.cst_transaction_id = ct.transaction_id

AND ctsa.cogs_recognition_flag = 'Y'

AND ct.base_txn_action_id = ctsa.base_txn_action_id

AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id

AND ROWNUM=1) vrm_src_document_id,

(SELECT DISTINCT crl.vrm_src_doc_line_id

FROM cst_revenue_lines crl,

cst_cogs_transactions cct,

cst_txn_source_actions ctsa

WHERE cct.revenue_line_id = crl.revenue_line_id

AND crl.external_system_reference = 'VRM'

AND cct.cst_transaction_id = ct.transaction_id

AND ctsa.cogs_recognition_flag = 'Y'

AND ct.base_txn_action_id = ctsa.base_txn_action_id

AND ct.base_txn_source_type_id = ctsa.base_txn_source_type_id

AND ROWNUM=1) vrm_src_document_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 cst_transactions ct,

cst_inv_transactions cit,

cst_cost_inv_orgs ccioa,

cst_cost_inv_orgs cciob,

cst_transfer_order_lines ctol

WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id (+)

AND ccioa.inv_org_id (+) = cit.prior_inventory_org_id

AND cciob.inv_org_id (+) = cit.successor_inventory_org_id

AND ctol.external_system_reference (+) = 'FUSION'

AND ctol.cst_transfer_order_line_id (+) = ct.cst_transfer_order_line_id

UNION ALL

SELECT

cct.transaction_id transaction_id,

cct.cost_org_id cost_org_id,

cct.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,

cct.inventory_item_id inventory_item_id,

cct.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,

nvl(ct.inv_txn_source_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_id cst_inv_transaction_id,

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,

- 1 po_distribution_id,

cct.shipment_fullfill_line_id doo_fullfill_line_id,

ct.cst_work_order_id cst_work_order_id,

ct.cst_work_order_operation_id cst_work_order_operation_id,

ct.supply_type work_order_supply_type,

ct.work_center_id work_center_id,

ct.operation_scrap_cost_type scrap_cost_type,

ct.provisional_completion_type provisional_completion_type,

cit.subinventory_code subinventory_code,

cit.locator_id locator_id,

cit.transaction_reason_id reason_id,

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,

cit.prior_inventory_org_id prior_inventory_org_id,

cit.prior_business_unit_id prior_business_unit_id,

ccioa.cost_org_id prior_cost_org_id,

cit.successor_inventory_org_id successor_inventory_org_id,

cit.successor_business_unit_id successor_business_unit_id,

cit.external_system_ref_id external_system_ref_id,

cciob.cost_org_id successor_cost_org_id,

crl.revenue_account revenue_account,

CAST(NULL AS NUMBER(18)) po_header_id,

CAST(NULL AS NUMBER(18)) po_line_id,

crl.customer_trx_id customer_trx_id,

crl.customer_trx_line_id customer_trx_line_id,

crl.external_system_ref_id cust_trx_line_gl_dist_id,

CAST(NULL AS NUMBER(18)) to_line_id,

'N' description_item_flag,

crl.external_system_reference revenue_recognition_source,

cit.supplier_return_header_id supplier_return_header_id,

CAST(NULL as NUMBER(18)) disposition_id,

CASE

WHEN crl.external_system_reference = 'VRM'

THEN crl.VRM_SRC_DOC_ID

ELSE

CAST(NULL as NUMBER(18))

END vrm_src_document_id,

CASE

WHEN crl.external_system_reference = 'VRM'

THEN crl.VRM_SRC_DOC_LINE_ID

ELSE

CAST(NULL as NUMBER(18))

END vrm_src_document_line_id,

null assembly_asset_id

FROM cst_cogs_transactions cct,

cst_transactions ct,

cst_inv_transactions cit,

cst_cost_inv_orgs ccioa,

cst_cost_inv_orgs cciob,

cst_revenue_lines crl

WHERE cct.cst_transaction_id = ct.transaction_id

AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id (+)

AND ccioa.inv_org_id (+) = cit.prior_inventory_org_id

AND cciob.inv_org_id (+) = cit.successor_inventory_org_id

AND crl.revenue_line_id (+) = cct.revenue_line_id