CST_ALL_COST_TRANSACTIONS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

TRANSACTION_ID SOURCE_TABLE COST_ORG_ID COST_BOOK_ID VAL_UNIT_ID INVENTORY_ITEM_ID RESOURCE_ID INVENTORY_ORG_ID ITEM_ORGANIZATION_ID INTRANSIT_FLAG COST_TRANSACTION_TYPE BASE_TXN_TYPE_ID BASE_TXN_SOURCE_TYPE_ID BASE_TXN_ACTION_ID TRANSACTION_DATE COST_DATE COST_METHOD_CODE COGS_PERCENTAGE QUANTITY UOM_CODE CST_INV_TRANSACTION_ID CST_INV_TRANSACTION_DTL_ID TXN_SOURCE_DOC_TYPE TXN_SOURCE_DOC_NUMBER TXN_SOURCE_REF_DOC_TYPE TXN_SOURCE_REF_DOC_NUMBER BUSINESS_UNIT_ID TRANSACTION_FLOW_TYPE COST_STATUS ACCOUNTING_STATUS CONSIGNED_FLAG CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN TRANSFER_COST_ORG_ID TRANSFER_CST_INV_TXN_ID TRANSACTION_QTY TRANSACTION_UOM_CODE PRIMARY_QTY PRIMARY_UOM_CODE SECONDARY_TRANSACTION_QTY SECONDARY_UOM_CODE REVENUE_LINE_ID DELIVERY_ID SHIPMENT_FULLFILL_LINE_ID RMA_TRANSACTION_ID RCV_TRANSACTION_ID PO_DISTRIBUTION_ID WSH_DELIVERY_DETAIL_ID DOO_FULLFILL_LINE_ID CST_WO_RESOURCE_TXN_ID CST_WORK_ORDER_ID CST_WORK_ORDER_OPERATION_ID WORK_CENTER_ID WO_OPERATION_RESOURCE_ID CST_WO_OPERATION_TXN_ID FROM_GRADE_CODE TO_GRADE_CODE SUPPLY_TYPE TRANSACTION_REASON_ID EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID LOT_NUMBER PARENT_LOT_NUMBER VENDOR_LOT_NUMBER SERIAL_NUMBER GRADE_CODE LE_TIMEZONE_CODE FISCAL_DOC_HEADER_ID FISCAL_DOC_LINE_ID FISCAL_DOC_SCHEDULE_ID FISCAL_DOC_ACCESS_KNUM REF_FISCAL_DOC_HEADER_ID REF_FISCAL_DOC_LINE_ID REF_FISCAL_DOC_SCHEDULE_ID REF_FISCAL_DOC_ACCESS_KNUM PO_ITEM_DESCRIPTION PO_ITEM_DESCRIPTION_TYPE TRANSACTION_AMOUNT TRANSACTION_CURRENCY_CODE PERIOD_NAME PJC_CONTRACT_ID PJC_RESERVED_ATTRIBUTE1 COUNTRY_OF_ORIGIN_CODE TASK_ID PROJECT_ID PJC_ORGANIZATION_ID PJC_EXPENDITURE_ITEM_DATE PJC_WORK_TYPE_ID PJC_EXPENDITURE_TYPE_ID PJC_TASK_ID PJC_PROJECT_ID

Query

SQL_Statement

SELECT ct.transaction_id, 'CST_TRANSACTIONS' source_table, ct.cost_org_id, ct.cost_book_id, ct.val_unit_id, ct.inventory_item_id, NULL resource_id, ct.inventory_org_id, decode(ct.intransit_flag, 'Y', op.master_organization_id, ct.inventory_org_id) item_organization_id, ct.intransit_flag, ct.cost_transaction_type, ct.base_txn_type_id, ct.base_txn_source_type_id, ct.base_txn_action_id, ct.transaction_date, ct.cost_date, ct.cost_method_code, NULL cogs_percentage, ct.quantity, ct.uom_code, ct.cst_inv_transaction_id, ct.cst_inv_transaction_dtl_id, ct.txn_source_doc_type, ct.txn_source_doc_number, ct.txn_source_ref_doc_type, ct.txn_source_ref_doc_number, ct.business_unit_id, ct.transaction_flow_type, ct.cost_status, ct.accounting_status, ct.consigned_flag, ct.created_by, ct.creation_date, ct.last_updated_by, ct.last_update_date, ct.last_update_login, ct.transfer_cost_org_id, ct.transfer_cst_inv_txn_id, ct.transaction_qty, ct.transaction_uom_code, cit.primary_qty, cit.primary_uom_code, cit.secondary_transaction_qty, cit.secondary_transaction_uom_code secondary_uom_code, NULL revenue_line_id, NULL delivery_id, NULL shipment_fullfill_line_id, NULL rma_transaction_id, ct.rcv_transaction_id, ct.po_distribution_id, ct.wsh_delivery_detail_id, ct.doo_fullfill_line_id, NULL cst_wo_resource_txn_id, ct.cst_work_order_id, ct.cst_work_order_operation_id, ct.work_center_id, NULL wo_operation_resource_id, NULL cst_wo_operation_txn_id, cit.from_grade_code, cit.to_grade_code, cit.supply_type, cit.transaction_reason_id, cit.external_system_reference, cit.external_system_ref_id, citd.lot_number, citd.parent_lot_number, citd.vendor_lot_number, citd.serial_number, citd.grade_code, ct.le_timezone_code, cit.fiscal_doc_header_id, cit.fiscal_doc_line_id, cit.fiscal_doc_schedule_id, cit.fiscal_doc_access_knum, cit.ref_fiscal_doc_header_id, cit.ref_fiscal_doc_line_id, cit.ref_fiscal_doc_schedule_id, cit.ref_fiscal_doc_access_knum, ct.po_item_description, ct.po_item_description_type, ct.transaction_amount, ct.transaction_currency_code, ct.period_name, cit.pjc_contract_id, cit.pjc_reserved_attribute1, cit.country_of_origin_code, cit.task_id, cit.project_id, cit.pjc_organization_id, cit.pjc_expenditure_item_date, cit.pjc_work_type_id, cit.pjc_expenditure_type_id, cit.pjc_task_id, cit.pjc_project_id FROM cst_transactions ct, cst_inv_transactions cit, cst_inv_transaction_dtls citd, inv_org_parameters op WHERE ct.cst_inv_transaction_id = cit.cst_inv_transaction_id (+) AND ct.cst_inv_transaction_dtl_id = citd.cst_inv_transaction_dtl_id (+) AND ct.inventory_org_id = op.organization_id UNION ALL SELECT cct.transaction_id, 'CST_COGS_TRANSACTIONS' source_table, cct.cost_org_id, cct.cost_book_id, ct.val_unit_id, cct.inventory_item_id, NULL resource_id, cct.inventory_org_id, decode(ct.intransit_flag, 'Y', op.master_organization_id, cct.inventory_org_id) item_organization_id, ct.intransit_flag, ct.cost_transaction_type, cct.transaction_type_id base_txn_type_id, cct.transaction_source_type_id base_txn_source_type_id, cct.transaction_action_id base_txn_action_id, cct.transaction_date, cct.transaction_date cost_date, ct.cost_method_code, cct.cogs_percentage, ct.quantity quantity, ct.uom_code, ct.cst_inv_transaction_id, ct.cst_inv_transaction_dtl_id, ct.txn_source_doc_type, ct.txn_source_doc_number, ct.txn_source_ref_doc_type, ct.txn_source_ref_doc_number, ct.business_unit_id, ct.transaction_flow_type, cct.cost_status, cct.accounting_status, ct.consigned_flag, cct.created_by, cct.creation_date, cct.last_updated_by, cct.last_update_date, cct.last_update_login, ct.transfer_cost_org_id, ct.transfer_cst_inv_txn_id, ct.transaction_qty, ct.transaction_uom_code, cit.primary_qty, cit.primary_uom_code, cit.secondary_transaction_qty, cit.secondary_transaction_uom_code secondary_uom_code, cct.revenue_line_id, cct.delivery_id, cct.shipment_fullfill_line_id, cct.rma_transaction_id, ct.rcv_transaction_id, ct.po_distribution_id, ct.wsh_delivery_detail_id, ct.doo_fullfill_line_id, NULL cst_wo_resource_txn_id, NULL cst_work_order_id, NULL cst_work_order_operation_id, NULL work_center_id, NULL wo_operation_resource_id, NULL cst_wo_operation_txn_id, NULL from_grade_code, NULL to_grade_code, NULL supply_type, NULL transaction_reason_id, NULL external_system_reference, NULL external_system_ref_id, NULL lot_number, NULL parent_lot_number, NULL vendor_lot_number, NULL serial_number, NULL grade_code, ct.le_timezone_code, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, ct.po_item_description, ct.po_item_description_type, ct.transaction_amount, ct.transaction_currency_code, cct.period_name, NULL pjc_contract_id, NULL pjc_reserved_attribute1, NULL country_of_origin_code, NULL task_id, NULL project_id, NULL pjc_organization_id, NULL pjc_expenditure_item_date, NULL pjc_work_type_id, NULL pjc_expenditure_type_id, NULL pjc_task_id, NULL pjc_project_id FROM cst_cogs_transactions cct, cst_transactions ct, inv_org_parameters op, cst_inv_transactions cit WHERE cct.cst_transaction_id = ct.transaction_id AND ct.inventory_org_id = op.organization_id AND cit.cst_inv_transaction_id = ct.cst_inv_transaction_id UNION ALL SELECT t.acctg_only_txns_id transaction_id, 'CST_ACCTG_ONLY_TXNS' source_table, t.cost_org_id, t.cost_book_id, NULL val_unit_id, t.inventory_item_id, NULL resource_id, t.inventory_org_id, t.inventory_org_id item_organization_id, NULL intransit_flag, NULL cost_transaction_type, 10 base_txn_type_id, 0 base_txn_source_type_id, 10 base_txn_action_id, t.txn_account_date transaction_date, t.txn_account_date cost_date, NULL cost_method_code, NULL cogs_percentage, NULL quantity, NULL uom_code, NULL cst_inv_transaction_id, NULL cst_inv_transaction_dtl_id, 'RCV' txn_source_doc_type, r.receipt_number txn_source_doc_number, 'PO' txn_source_ref_doc_type, ( SELECT p.po_number FROM cmr_purchase_order_dtls p WHERE p.cmr_po_distribution_id = i.cmr_po_distribution_id AND ROWNUM = 1 ) txn_source_ref_doc_number, r.bill_to_business_unit_id business_unit_id, t.transaction_flow_type, t.cost_status, t.accounting_status, t.consigned_flag, t.created_by, t.creation_date, t.last_updated_by, t.last_update_date, t.last_update_login, NULL transfer_cost_org_id, NULL transfer_cst_inv_txn_id, NULL transaction_qty, NULL transaction_uom_code, NULL primary_qty, NULL primary_uom_code, NULL secondary_transaction_qty, NULL secondary_uom_code, NULL revenue_line_id, NULL delivery_id, NULL shipment_fullfill_line_id, NULL rma_transaction_id, to_number(r.external_system_ref_id) rcv_transaction_id, r.po_distribution_id, NULL wsh_delivery_detail_id, NULL doo_fullfill_line_id, NULL cst_wo_resource_txn_id, NULL cst_work_order_id, NULL cst_work_order_operation_id, NULL work_center_id, NULL wo_operation_resource_id, NULL cst_wo_operation_txn_id, NULL from_grade_code, NULL to_grade_code, NULL supply_type, NULL transaction_reason_id, NULL external_system_reference, NULL external_system_ref_id, NULL lot_number, NULL parent_lot_number, NULL vendor_lot_number, NULL serial_number, NULL grade_code, t.le_timezone_code, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, NULL po_item_description, NULL po_item_description_type, NULL transaction_amount, NULL transaction_currency_code, t.period_name, r.pjc_contract_id, r.pjc_reserved_attribute1, r.country_of_origin_code, NULL task_id, NULL project_id, r.pjc_organization_id, r.pjc_expenditure_item_date, r.pjc_work_type_id, r.pjc_expenditure_type_id, CAST(r.pjc_task_id AS NUMBER(18)) pjc_task_id, r.pjc_project_id FROM cst_acctg_only_txns t, cst_invoice_to_exp_txns i, cmr_rcv_transactions r WHERE t.invoice_to_exp_txn_id = i.invoice_to_exp_txn_id AND i.cmr_rcv_transaction_id = r.cmr_rcv_transaction_id (+) UNION ALL SELECT resource_transaction_id transaction_id, 'CST_RESOURCE_TRANSACTIONS' source_table, cost_org_id, cost_book_id, NULL val_unit_id, inventory_item_id, resource_id, inventory_organization_id inventory_org_id, inventory_organization_id item_organization_id, NULL intransit_flag, cost_transaction_type, base_txn_type_id, base_txn_source_type_id, base_txn_action_id, transaction_date, cost_date, cost_method_code, NULL cogs_percentage, primary_quantity quantity, primary_uom_code uom_code, NULL cst_inv_transaction_id, NULL cst_inv_transaction_dtl_id, txn_source_doc_type, txn_source_doc_number, txn_source_ref_doc_type, txn_source_ref_doc_number, NULL business_unit_id, NULL transaction_flow_type, costing_status cost_status, accounting_status, NULL consigned_flag, created_by, creation_date, last_updated_by, last_update_date, last_update_login, NULL transfer_cost_org_id, NULL transfer_cst_inv_txn_id, transaction_quantity transaction_qty, transaction_uom_code, primary_quantity primary_qty, primary_uom_code, NULL secondary_transaction_qty, NULL secondary_uom_code, NULL revenue_line_id, NULL delivery_id, NULL shipment_fullfill_line_id, NULL rma_transaction_id, NULL rcv_transaction_id, NULL po_distribution_id, NULL wsh_delivery_detail_id, NULL doo_fullfill_line_id, cst_wo_resource_txn_id, cst_work_order_id, cst_work_order_operation_id, work_center_id, wo_operation_resource_id, NULL cst_wo_operation_txn_id, NULL from_grade_code, NULL to_grade_code, NULL supply_type, NULL transaction_reason_id, NULL external_system_reference, NULL external_system_ref_id, NULL lot_number, NULL parent_lot_number, NULL vendor_lot_number, NULL serial_number, NULL grade_code, le_timezone_code, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, NULL po_item_description, NULL po_item_description_type, NULL transaction_amount, NULL transaction_currency_code, period_name, pjc_contract_id, pjc_reserved_attribute1, NULL country_of_origin_code, NULL task_id, NULL project_id, pjc_organization_id, pjc_expenditure_item_date, pjc_work_type_id, pjc_expenditure_type_id, pjc_task_id, pjc_project_id FROM cst_resource_transactions UNION ALL SELECT operation_transaction_id transaction_id, 'CST_OPERATION_TRANSACTIONS' source_table, cost_org_id, cost_book_id, NULL val_unit_id, inventory_item_id, NULL resource_id, inventory_organization_id inventory_org_id, inventory_organization_id item_organization_id, NULL intransit_flag, cost_transaction_type, base_txn_type_id, base_txn_source_type_id, base_txn_action_id, transaction_date, cost_date, NULL cost_method_code, NULL cogs_percentage, primary_quantity quantity, primary_uom_code uom_code, NULL cst_inv_transaction_id, NULL cst_inv_transaction_dtl_id, txn_source_doc_type, txn_source_doc_number, txn_source_ref_doc_type, txn_source_ref_doc_number, NULL business_unit_id, NULL transaction_flow_type, costing_status cost_status, accounting_status, NULL consigned_flag, created_by, creation_date, last_updated_by, last_update_date, last_update_login, NULL transfer_cost_org_id, NULL transfer_cst_inv_txn_id, transaction_quantity transaction_qty, transaction_uom_code, primary_quantity primary_qty, primary_uom_code, NULL secondary_transaction_qty, NULL secondary_uom_code, NULL revenue_line_id, NULL delivery_id, NULL shipment_fullfill_line_id, NULL rma_transaction_id, NULL rcv_transaction_id, NULL po_distribution_id, NULL wsh_delivery_detail_id, NULL doo_fullfill_line_id, NULL cst_wo_resource_txn_id, cst_work_order_id, cst_work_order_operation_id, work_center_id, NULL wo_operation_resource_id, cst_wo_operation_txn_id, NULL from_grade_code, NULL to_grade_code, NULL supply_type, NULL transaction_reason_id, NULL external_system_reference, NULL external_system_ref_id, NULL lot_number, NULL parent_lot_number, NULL vendor_lot_number, NULL serial_number, NULL grade_code, le_timezone_code, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, NULL po_item_description, NULL po_item_description_type, NULL transaction_amount, NULL transaction_currency_code, period_name, NULL pjc_contract_id, NULL pjc_reserved_attribute1, NULL country_of_origin_code, NULL task_id, NULL project_id, NULL pjc_organization_id, NULL pjc_expenditure_item_date, NULL pjc_work_type_id, NULL pjc_expenditure_type_id, NULL pjc_task_id, NULL pjc_project_id FROM cst_operation_transactions UNION ALL SELECT wo_update_event_txn_id transaction_id, 'CST_WO_UPDATE_EVENT_TXNS' source_table, cost_org_id, cost_book_id, NULL val_unit_id, inventory_item_id, NULL resource_id, inventory_organization_id inventory_org_id, inventory_organization_id item_organization_id, NULL intransit_flag, NULL cost_transaction_type, base_txn_type_id, base_txn_source_type_id, base_txn_action_id, NVL(transaction_date,event_date) transaction_date, cost_date, cost_method_code, NULL cogs_percentage, total_completion_quantity quantity, uom_code uom_code, NULL cst_inv_transaction_id, NULL cst_inv_transaction_dtl_id, txn_source_doc_type, txn_source_doc_number, txn_source_ref_doc_type, txn_source_ref_doc_number, NULL business_unit_id, NULL transaction_flow_type, costing_status cost_status, accounting_status, NULL consigned_flag, created_by, creation_date, last_updated_by, last_update_date, last_update_login, NULL transfer_cost_org_id, NULL transfer_cst_inv_txn_id, total_completion_quantity transaction_qty, uom_code transaction_uom_code, total_completion_quantity primary_qty, uom_code primary_uom_code, NULL secondary_transaction_qty, NULL secondary_uom_code, NULL revenue_line_id, NULL delivery_id, NULL shipment_fullfill_line_id, NULL rma_transaction_id, NULL rcv_transaction_id, NULL po_distribution_id, NULL wsh_delivery_detail_id, NULL doo_fullfill_line_id, NULL cst_wo_resource_txn_id, cst_work_order_id, NULL cst_work_order_operation_id, NULL work_center_id, NULL wo_operation_resource_id, NULL cst_wo_operation_txn_id, NULL from_grade_code, NULL to_grade_code, NULL supply_type, NULL transaction_reason_id, NULL external_system_reference, NULL external_system_ref_id, NULL lot_number, NULL parent_lot_number, NULL vendor_lot_number, NULL serial_number, NULL grade_code, le_timezone_code, NULL fiscal_doc_header_id, NULL fiscal_doc_line_id, NULL fiscal_doc_schedule_id, NULL fiscal_doc_access_knum, NULL ref_fiscal_doc_header_id, NULL ref_fiscal_doc_line_id, NULL ref_fiscal_doc_schedule_id, NULL ref_fiscal_doc_access_knum, NULL po_item_description, NULL po_item_description_type, NULL transaction_amount, NULL transaction_currency_code, period_name, NULL pjc_contract_id, NULL pjc_reserved_attribute1, NULL country_of_origin_code, NULL task_id, NULL project_id, NULL pjc_organization_id, NULL pjc_expenditure_item_date, NULL pjc_work_type_id, NULL pjc_expenditure_type_id, NULL pjc_task_id, NULL pjc_project_id FROM cst_wo_update_event_txns