CST_COSTED_INTR_BIP

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 TRANSFER_INVENTORY_ORG_ID ITEM_ORGANIZATION_ID SNAPSHOT_DATE EFF_TO_DATE ONHAND_QUANTITY ONHAND_VALUE ACCTD_ONHAND_VALUE FUNCTIONAL_CURRENCY UOM_CODE EXPENSE_TRANSACTION_FLAG CONSIGNED_FLAG COST_METHOD_CODE LEDGER_NAME ITEM_NUMBER DESCRIPTION CATEGORY_CODE CATEGORY_NAME CATEGORY_SET_NAME COST_ORG_NAME COST_BOOK_CODE INV_ORG_NAME TRANSFER_ORG_NAME COST_ELEMENT_ID COST_ELEMENT_CODE COST_ELEMENT_TYPE ACCOUNTED_FLAG

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

TRANSFER_INVENTORY_ORG_ID

ITEM_ORGANIZATION_ID

SNAPSHOT_DATE

EFF_TO_DATE

ONHAND_QUANTITY

ONHAND_VALUE

ACCTD_ONHAND_VALUE

FUNCTIONAL_CURRENCY

UOM_CODE

EXPENSE_TRANSACTION_FLAG

CONSIGNED_FLAG

COST_METHOD_CODE

LEDGER_NAME

ITEM_NUMBER

DESCRIPTION

CATEGORY_CODE

CATEGORY_NAME

CATEGORY_SET_NAME

COST_ORG_NAME

COST_BOOK_CODE

INV_ORG_NAME

TRANSFER_ORG_NAME

COST_ELEMENT_ID

COST_ELEMENT_CODE

COST_ELEMENT_TYPE

ACCOUNTED_FLAG

Query

SQL_Statement
WITH iod AS ( select organization_id, organization_code, organization_name from inv_organization_definitions_v ) 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.transfer_inventory_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 AS functional_currency, q.uom_code, q.expense_transaction_flag, q.consigned_flag, c.cost_method_code, gl.name AS ledger_name, esi.item_number, esi.description, ec.category_code, ec.category_name, ec.category_set_name, co.cost_org_name, cb.cost_book_code, iod.organization_name AS inv_org_name, iod1.organization_name AS transfer_org_name, ce.cost_element_id AS cost_element_id, ce.cost_element_code AS cost_element_code, ce.cost_element_type AS cost_element_type, q.accounted_flag FROM fusion.cst_costed_intr_txn_onhand_v q, fusion.cst_costed_intr_txn_costs_v c, cst_inv_transactions t, inv_org_parameters iop, cst_cost_orgs_v co, cst_cost_books_vl cb, gl_ledgers gl, cst_cost_org_books cob, egp_system_items_vl esi, cst_cost_elements_vl ce, iod iod, iod iod1, (SELECT eic.inventory_item_id, eic.category_id, eic.organization_id, ecv.category_name, ecv.category_code, ecs.category_set_name FROM egp_item_categories eic, egp_default_category_sets edc, egp_categories_vl ecv, egp_category_sets_vl ecs WHERE eic.category_set_id = edc.category_set_id AND edc.functional_area_id = 5 AND eic.category_id = ecv.category_id AND edc.category_set_id = ecs.category_set_id ) ec 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.inventory_org_id = iop.organization_id AND cob.ledger_id = gl.ledger_id(+) AND q.cost_org_id = cob.cost_org_id and q.cost_book_id = cob.cost_book_id AND q.inventory_item_id = esi.inventory_item_id AND iop.master_organization_id = esi.organization_id AND q.inventory_item_id = ec.inventory_item_id(+) AND q.inventory_org_id = ec.organization_id(+) AND q.cost_org_id = co.cost_org_id AND q.cost_book_id = cb.cost_book_id AND iod.organization_id = q.inventory_org_id AND iod1.organization_id = t.transfer_inventory_org_id AND c.cost_element_id = ce.cost_element_id(+) AND c.cost_element_id <> 0 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))) GROUP BY q.txn_source_ref_doc_type, q.txn_source_ref_doc_number, q.txn_source_doc_type, t.txn_source_doc_type, q.txn_source_doc_number, t.txn_source_doc_number, q.transaction_flow_type, q.cost_org_id, q.cost_book_id, 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, q.snapshot_date, c.snapshot_date, 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, gl.name, esi.item_number, esi.description, ec.category_code, ec.category_name, ec.category_set_name, co.cost_org_name, cb.cost_book_code, iod.organization_name, iod1.organization_name, ce.cost_element_id, ce.cost_element_code, ce.cost_element_type, q.accounted_flag

SQL_Statement

WITH iod AS ( select organization_id, organization_code, organization_name from inv_organization_definitions_v )

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.transfer_inventory_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 AS functional_currency,

q.uom_code,

q.expense_transaction_flag,

q.consigned_flag,

c.cost_method_code,

gl.name AS ledger_name,

esi.item_number,

esi.description,

ec.category_code,

ec.category_name,

ec.category_set_name,

co.cost_org_name,

cb.cost_book_code,

iod.organization_name AS inv_org_name,

iod1.organization_name AS transfer_org_name,

ce.cost_element_id AS cost_element_id,

ce.cost_element_code AS cost_element_code,

ce.cost_element_type AS cost_element_type,

q.accounted_flag

FROM

fusion.cst_costed_intr_txn_onhand_v q,

fusion.cst_costed_intr_txn_costs_v c,

cst_inv_transactions t,

inv_org_parameters iop,

cst_cost_orgs_v co,

cst_cost_books_vl cb,

gl_ledgers gl,

cst_cost_org_books cob,

egp_system_items_vl esi,

cst_cost_elements_vl ce,

iod iod,

iod iod1,

(SELECT eic.inventory_item_id,

eic.category_id,

eic.organization_id,

ecv.category_name,

ecv.category_code,

ecs.category_set_name

FROM egp_item_categories eic,

egp_default_category_sets edc,

egp_categories_vl ecv,

egp_category_sets_vl ecs

WHERE eic.category_set_id = edc.category_set_id

AND edc.functional_area_id = 5

AND eic.category_id = ecv.category_id

AND edc.category_set_id = ecs.category_set_id

) ec

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.inventory_org_id = iop.organization_id

AND cob.ledger_id = gl.ledger_id(+)

AND q.cost_org_id = cob.cost_org_id

and q.cost_book_id = cob.cost_book_id

AND q.inventory_item_id = esi.inventory_item_id

AND iop.master_organization_id = esi.organization_id

AND q.inventory_item_id = ec.inventory_item_id(+)

AND q.inventory_org_id = ec.organization_id(+)

AND q.cost_org_id = co.cost_org_id

AND q.cost_book_id = cb.cost_book_id

AND iod.organization_id = q.inventory_org_id

AND iod1.organization_id = t.transfer_inventory_org_id

AND c.cost_element_id = ce.cost_element_id(+)

AND c.cost_element_id <> 0

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)))

GROUP BY

q.txn_source_ref_doc_type,

q.txn_source_ref_doc_number,

q.txn_source_doc_type,

t.txn_source_doc_type,

q.txn_source_doc_number,

t.txn_source_doc_number,

q.transaction_flow_type,

q.cost_org_id,

q.cost_book_id,

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,

q.snapshot_date,

c.snapshot_date,

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,

gl.name,

esi.item_number,

esi.description,

ec.category_code,

ec.category_name,

ec.category_set_name,

co.cost_org_name,

cb.cost_book_code,

iod.organization_name,

iod1.organization_name,

ce.cost_element_id,

ce.cost_element_code,

ce.cost_element_type,

q.accounted_flag