CST_INV_VAL_BY_TYPE

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_ORG_ID INVENTORY_ORG_ID VAL_STRUCTURE_TYPE_CODE CONV_INV_VALUE REPORTING_CURRENCY_CODE INV_VALUE COST_ORG_CURRENCY_CODE

Name

COST_ORG_ID

INVENTORY_ORG_ID

VAL_STRUCTURE_TYPE_CODE

CONV_INV_VALUE

REPORTING_CURRENCY_CODE

INV_VALUE

COST_ORG_CURRENCY_CODE

Query

SQL_Statement
SELECT l.cost_org_id, q.inventory_org_id, vs.val_structure_type_code, sum(decode (l.cost_method_code, 'ACTUAL', l.onhand_value, l.unit_costq.quantity)) conv_inv_value, l.currency_code reporting_currency_code, sum(decode (l.cost_method_code, 'ACTUAL', l.onhand_value, l.unit_costq.quantity)) inv_value, l.currency_code cost_org_currency_code FROM cst_costed_vu_costs_v l, ( select cost_org_id, cost_book_id, inventory_item_id, val_unit_id, max(inventory_org_id) as inventory_org_id, sum(quantity) as quantity from cst_costed_attr_onhand_v where SYSDATE BETWEEN snapshot_date AND (eff_to_date-1) group by cost_org_id, cost_book_id, inventory_item_id, val_unit_id) q, cst_cost_org_books b, cst_val_units_vl v, cst_val_structures_b vs WHERE SYSDATE BETWEEN l.snapshot_date AND (l.eff_to_date -1 ) AND l.cost_org_id = b.cost_org_id AND b.primary_book_flag = 'Y' AND l.cost_book_id = b.cost_book_id AND l.val_unit_id = v.val_unit_id AND v.val_structure_id = vs.val_structure_id AND l.cost_org_id = q.cost_org_id AND l.cost_book_id = q.cost_book_id AND l.inventory_item_id = q.inventory_item_id AND l.val_unit_id = q.val_unit_id GROUP BY l.cost_org_id, q.inventory_org_id, vs.val_structure_type_code, l.currency_code , l.currency_code ORDER BY inv_value DESC NULLS LAST

SQL_Statement

SELECT l.cost_org_id,

q.inventory_org_id,

vs.val_structure_type_code,

sum(decode (l.cost_method_code, 'ACTUAL', l.onhand_value, l.unit_cost*q.quantity)) conv_inv_value,

l.currency_code reporting_currency_code,

sum(decode (l.cost_method_code, 'ACTUAL', l.onhand_value, l.unit_cost*q.quantity)) inv_value,

l.currency_code cost_org_currency_code

FROM cst_costed_vu_costs_v l,

(

select cost_org_id,

cost_book_id,

inventory_item_id,

val_unit_id,

max(inventory_org_id) as inventory_org_id,

sum(quantity) as quantity

from cst_costed_attr_onhand_v

where SYSDATE BETWEEN snapshot_date AND (eff_to_date-1)

group by cost_org_id,

cost_book_id,

inventory_item_id,

val_unit_id) q,

cst_cost_org_books b,

cst_val_units_vl v,

cst_val_structures_b vs

WHERE SYSDATE BETWEEN l.snapshot_date AND (l.eff_to_date -1 )

AND l.cost_org_id = b.cost_org_id

AND b.primary_book_flag = 'Y'

AND l.cost_book_id = b.cost_book_id

AND l.val_unit_id = v.val_unit_id

AND v.val_structure_id = vs.val_structure_id

AND l.cost_org_id = q.cost_org_id

AND l.cost_book_id = q.cost_book_id

AND l.inventory_item_id = q.inventory_item_id

AND l.val_unit_id = q.val_unit_id

GROUP BY l.cost_org_id,

q.inventory_org_id,

vs.val_structure_type_code,

l.currency_code ,

l.currency_code

ORDER BY inv_value DESC NULLS LAST