CST_XLA_SVC_COST_REC_TXNS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

TRANSACTION_ID COST_ORG_ID COST_BOOK_ID RESOURCE_ID RESOURCE_TRANSACTION_ID WO_OPERATION_RESOURCE_ID RESOURCE_ACTIVITY_CODE CST_WO_RESOURCE_TXN_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 CST_WORK_ORDER_ID CST_WORK_ORDER_OPERATION_ID WORK_ORDER_SUPPLY_TYPE WORK_CENTER_ID SCRAP_COST_TYPE PROVISIONAL_COMPLETION_TYPE 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 SUCCESSOR_INVENTORY_ORG_ID SUCCESSOR_BUSINESS_UNIT_ID EXTERNAL_SYSTEM_REF_ID PO_HEADER_ID PO_LINE_ID TO_LINE_ID DESCRIPTION_ITEM_FLAG EXTERNAL_SYSTEM_REFERENCE REV_REC_TXN_ID DEBRIEF_NUMBER SUBINVENTORY_CODE CHARGE_POSTING_DATE RECOGNITION_PERCENTAGE CUST_ACCOUNT_ID BILL_TO_PARTY_SITE_ID TECHNITIAN_PARTY_ID SUBSCRIPTION_ID SUBSCRIPTION_TYPE SUBSCRIPTION_NUMBER SUPPLIER_RETURN_HEADER_ID DISPOSITION_ID ASSEMBLY_ASSET_ID

Query

SQL_Statement

SELECT crcrt.transaction_id transaction_id, crcrt.cost_org_id cost_org_id, crcrt.cost_book_id cost_book_id, NULL resource_id, NULL resource_transaction_id, NULL wo_operation_resource_id, NULL resource_activity_code, NULL cst_wo_resource_txn_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, crcrt.inventory_item_id inventory_item_id, crcrt.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, crcrt.transaction_type_id base_txn_type_id, crcrt.transaction_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, 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.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, 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, CAST(NULL AS NUMBER(18)) po_header_id, CAST(NULL AS NUMBER(18)) po_line_id, CAST(NULL AS NUMBER(18)) to_line_id, 'N' description_item_flag, crrrt.external_system_reference external_system_reference, crrrt.rev_rec_txn_id rev_rec_txn_id, crrrt.debrief_number debrief_number, crrrt.subinventory_code subinventory_code, crrrt.charge_posting_date charge_posting_date, crrrt.recognition_percentage recognition_percentage, crrrt.cust_account_id cust_account_id, crrrt.bill_to_party_site_id bill_to_party_site_id, crrrt.technician_party_id technitian_party_id, crrrt.subscription_id subscription_id, crrrt.subscription_type subscription_type, crrrt.subscription_number subscription_number, cit.supplier_return_header_id supplier_return_header_id, CAST(NULL AS NUMBER(18)) disposition_id, CASE WHEN ct.assembly_asset_id IS NOT NULL THEN ct.assembly_asset_id ELSE wo.min_asset_id END assembly_asset_id FROM cst_rcl_cost_rec_txns crcrt, cst_transactions ct, cst_inv_transactions cit, cst_cost_inv_orgs ccio, cst_rcl_rev_rec_txns crrrt, ( SELECT cst_work_order_id, MIN(asset_id) min_asset_id FROM cst_work_orders cwo, wie_wo_assets wwa WHERE cwo.external_system_ref_id = wwa.work_order_id GROUP BY cst_work_order_id ) wo WHERE crcrt.cst_transaction_id = ct.transaction_id AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id AND ccio.inv_org_id = cit.inventory_org_id AND crrrt.rev_rec_txn_id = crcrt.rev_rec_txn_id AND crcrt.cst_work_order_id = wo.cst_work_order_id (+) UNION ALL SELECT crcrt.transaction_id transaction_id, crcrt.cost_org_id cost_org_id, crcrt.cost_book_id cost_book_id, crt.resource_id resource_id, crt.resource_transaction_id resource_transaction_id, crt.wo_operation_resource_id wo_operation_resource_id, crt.resource_activity_code resource_activity_code, crt.cst_wo_resource_txn_id cst_wo_resource_txn_id, NULL val_structure_id, NULL structure_instance_number, NULL val_unit_id, NULL val_unit_combination_id, crcrt.inventory_item_id inventory_item_id, crcrt.inventory_org_id inv_org_id, NULL item_validation_org_id, NULL logical_flag, crcrt.transaction_type_id base_txn_type_id, crcrt.transaction_source_type_id base_txn_source_type_id, crt.cost_profile_id cost_profile_id, NULL expense_transaction_flag, crt.cost_method_code cost_method_code, NULL negative_quantity_code, NULL quantity_flow_code, crt.primary_uom_code uom_code, NULL cst_inv_transaction_id, 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, crt.txn_source_doc_type txn_source_doc_type, crt.txn_source_doc_number txn_source_doc_number, crt.txn_source_ref_doc_type txn_source_ref_doc_type, crt.txn_source_ref_doc_number txn_source_ref_doc_number, NULL business_unit_id, NULL transaction_flow_type, NULL intransit_flag, NULL internal_profit_tracking, - 1 po_distribution_id, crt.cst_work_order_id cst_work_order_id, crt.cst_work_order_operation_id cst_work_order_operation_id, NULL work_order_supply_type, crt.work_center_id work_center_id, NULL scrap_cost_type, NULL provisional_completion_type, NULL locator_id, NULL reason_id, 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 prior_inventory_org_id, NULL prior_business_unit_id, NULL successor_inventory_org_id, NULL successor_business_unit_id, NULL external_system_ref_id, CAST(NULL AS NUMBER(18)) po_header_id, CAST(NULL AS NUMBER(18)) po_line_id, CAST(NULL AS NUMBER(18)) to_line_id, 'N' description_item_flag, crrrt.external_system_reference external_system_reference, crrrt.rev_rec_txn_id rev_rec_txn_id, crrrt.debrief_number debrief_number, crrrt.subinventory_code subinventory_code, crrrt.charge_posting_date charge_posting_date, crrrt.recognition_percentage recognition_percentage, crrrt.cust_account_id cust_account_id, crrrt.bill_to_party_site_id bill_to_party_site_id, crrrt.technician_party_id technitian_party_id, crrrt.subscription_id subscription_id, crrrt.subscription_type subscription_type, crrrt.subscription_number subscription_number, NULL supplier_return_header_id, CAST(NULL AS NUMBER(18)) disposition_id, CASE WHEN crt.assembly_asset_id IS NOT NULL THEN crt.assembly_asset_id ELSE wo.min_asset_id END assembly_asset_id FROM cst_rcl_cost_rec_txns crcrt, cst_resource_transactions crt, cst_cost_inv_orgs ccio, cst_rcl_rev_rec_txns crrrt, ( SELECT cst_work_order_id, MIN(asset_id) min_asset_id FROM cst_work_orders cwo, wie_wo_assets wwa WHERE cwo.external_system_ref_id = wwa.work_order_id GROUP BY cst_work_order_id ) wo WHERE crcrt.cst_transaction_id = crt.resource_transaction_id AND crrrt.rev_rec_txn_id = crcrt.rev_rec_txn_id AND ccio.inv_org_id = crcrt.inventory_org_id AND crt.cst_work_order_id = wo.cst_work_order_id (+)