CST_COSTED_INTR_OTBI_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

TXN_SOURCE_REF_DOC_TYPE TXN_SOURCE_REF_DOC_NUMBER TXN_SOURCE_DOC_TYPE TXN_SOURCE_DOC_NUMBER TRANSACTION_FLOW_TYPE COST_ORG_ID COST_BOOK_ID BUSINESS_UNIT_ID INVENTORY_ORG_ID INVENTORY_ITEM_ID SHIP_FROM_ORG_ID SHIP_TO_ORG_ID ITEM_ORGANIZATION_ID SNAPSHOT_DATE EFF_TO_DATE ONHAND_QUANTITY ONHAND_VALUE ACCTD_ONHAND_VALUE CURRENCY_CODE UOM_CODE EXPENSE_TRANSACTION_FLAG CONSIGNED_FLAG COST_METHOD_CODE ACCOUNTED_FLAG

Query

SQL_Statement

SELECT q.txn_source_ref_doc_type, q.txn_source_ref_doc_number, nvl(q.txn_source_doc_type, t.txn_source_doc_type) txn_source_doc_type, nvl(q.txn_source_doc_number, t.txn_source_doc_number) txn_source_doc_number, q.transaction_flow_type, q.cost_org_id, q.cost_book_id, nvl(iop.profit_center_bu_id, iop.business_unit_id) business_unit_id, q.inventory_org_id, q.inventory_item_id, t.inventory_org_id ship_from_org_id, t.transfer_inventory_org_id ship_to_org_id, iop.master_organization_id item_organization_id, greatest(q.snapshot_date, c.snapshot_date) snapshot_date, least(q.eff_to_date, c.eff_to_date) eff_to_date, SUM(q.quantity + nvl(q.cmr_rcv_quantity,0)) onhand_quantity, SUM(DECODE (q.quantity, 0, c.onhand_value, c.onhand_value * (q.quantity + nvl(q.cmr_rcv_quantity,0))/(q.quantity))) onhand_value, SUM(DECODE (q.quantity, 0, c.acctd_onhand_value, c.acctd_onhand_value * (q.quantity + nvl(q.cmr_rcv_quantity,0))/(q.quantity))) acctd_onhand_value, c.currency_code, q.uom_code, q.expense_transaction_flag, q.consigned_flag, c.cost_method_code, q.accounted_flag FROM cst_costed_intr_txn_onhand_v q, cst_costed_intr_txn_costs_v c, cst_inv_transactions t, inv_org_parameters iop WHERE q.txn_source_ref_doc_number = c.txn_source_ref_doc_number AND q.txn_source_ref_doc_type = c.txn_source_ref_doc_type AND nvl(q.txn_source_doc_number, ' ') = nvl(c.txn_source_doc_number, ' ') AND nvl(q.txn_source_doc_type, ' ') = nvl(c.txn_source_doc_type, ' ') AND q.cost_org_id = c.cost_org_id AND q.cost_book_id = c.cost_book_id AND q.inventory_item_id = c.inventory_item_id AND q.inventory_org_id = c.inventory_org_id AND q.expense_transaction_flag = c.expense_transaction_flag AND q.consigned_flag = c.consigned_flag AND nvl(q.transfer_txn_id, - 1) = nvl(c.transfer_txn_id, - 1) AND to_char(q.transfer_txn_id) = t.external_system_ref_id (+) AND 'FUSION' = t.external_system_reference (+) AND ((q.snapshot_date BETWEEN c.snapshot_date AND (c.eff_to_date - 1)) OR (c.snapshot_date BETWEEN q.snapshot_date AND (q.eff_to_date - 1))) AND q.inventory_org_id = iop.organization_id GROUP BY q.txn_source_ref_doc_type, q.txn_source_ref_doc_number, nvl(q.txn_source_doc_type, t.txn_source_doc_type), nvl(q.txn_source_doc_number, t.txn_source_doc_number), q.transaction_flow_type, q.cost_org_id, q.cost_book_id, nvl(iop.profit_center_bu_id, iop.business_unit_id), q.inventory_org_id, q.inventory_item_id, t.inventory_org_id, t.transfer_inventory_org_id, iop.master_organization_id, greatest(q.snapshot_date, c.snapshot_date), least(q.eff_to_date, c.eff_to_date), c.currency_code, q.uom_code, q.expense_transaction_flag, q.consigned_flag, c.cost_method_code, q.accounted_flag