CST_B_COSTED_ATTR_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Query

SQL_Statement
SELECT cost_org_id, cost_book_id, inventory_item_id, val_unit_id, inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code, cost_element_id, snapshot_date, NVL(lead (snapshot_date, 1) OVER (PARTITION BY cost_org_id, cost_book_id, inventory_item_id, val_unit_id,inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code, cost_element_id ORDER BY snapshot_date), to_date('4712-12-31', 'yyyy-mm-dd')) eff_to_date, SUM(onhand_value) OVER (PARTITION BY cost_org_id, cost_book_id, inventory_item_id, val_unit_id,inventory_org_id, subinventory_code, locator_id, project_id, task_id, country_of_origin_code, cost_element_id ORDER BY snapshot_date) onhand_value, unit_cost, currency_code, uom_code, cost_method_code FROM (SELECT lc.cost_org_id, lc.cost_book_id, lc.inventory_item_id, lc.val_unit_id, ct.inventory_org_id, cit.subinventory_code, cit.locator_id, cit.project_id, cit.task_id, cit.country_of_origin_code, lc.cost_element_id, TRUNC(lc.cost_date) snapshot_date, SUM(lc.unit_cost * lc.quantity) onhand_value, 0 unit_cost, lc.currency_code, lc.uom_code, ct.cost_method_code FROM cst_layer_costs lc, cst_transactions ct, cst_inv_transactions cit WHERE NVL(lc.absorption_type, 1) = 1 AND ct.transaction_id = lc.transaction_id AND ct.cost_method_code = 'ACTUAL' AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id GROUP BY lc.cost_org_id, lc.cost_book_id, lc.inventory_item_id, lc.val_unit_id, ct.inventory_org_id, cit.subinventory_code, cit.locator_id, cit.project_id, cit.task_id, cit.country_of_origin_code, lc.cost_element_id, TRUNC(lc.cost_date), lc.currency_code, lc.uom_code, ct.cost_method_code UNION ALL SELECT DISTINCT lc.cost_org_id, lc.cost_book_id, lc.inventory_item_id, lc.val_unit_id, -1, ' ', -1, -1, -1, ' ', lc.cost_element_id, TRUNC(lc.cost_date) snapshot_date, 0 onhand_value, last_value(lc.unit_cost_average) OVER (PARTITION BY lc.cost_org_id, lc.cost_book_id, lc.inventory_item_id, lc.val_unit_id, lc.cost_element_id, TRUNC(lc.cost_date) ORDER BY lc.cost_date, lc.dep_trxn_id, lc.transaction_id, lc.rec_trxn_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) unit_cost, lc.currency_code, lc.uom_code, 'PERPETUAL_AVERAGE' FROM cst_perpavg_cost lc ) UNION ALL SELECT sc.cost_org_id, sc.cost_book_id, sc.inventory_item_id, sc.val_unit_id, -1, ' ', -1, -1, -1, ' ', scd.cost_element_id, sc.effective_start_date snapshot_date, DECODE (sc.effective_end_date , to_date('4712-12-31', 'yyyy-mm-dd'), sc.effective_end_date, sc.effective_end_date + INTERVAL '1' DAY) eff_to_date, 0 onhand_value, scd.unit_cost, sc.currency_code, sc.uom_code, 'STANDARD' FROM fusion.cst_std_costs sc, fusion.cst_std_cost_details scd, cst_cost_profiles_b cp WHERE sc.status_code = 'PUBLISHED' AND sc.assignment_level = 'VALUATION_UNIT' AND sc.val_unit_id IS NOT NULL AND sc.cost_profile_id = cp.cost_profile_id AND sc.std_cost_id = scd.std_cost_id AND cp.cost_method_code = 'STANDARD'

SQL_Statement

SELECT cost_org_id,

cost_book_id,

inventory_item_id,

val_unit_id,

inventory_org_id,

subinventory_code,

locator_id,

project_id,

task_id,

country_of_origin_code,

cost_element_id,

snapshot_date,

NVL(lead (snapshot_date, 1) OVER (PARTITION BY cost_org_id, cost_book_id, inventory_item_id, val_unit_id,inventory_org_id,

subinventory_code, locator_id, project_id, task_id, country_of_origin_code, cost_element_id ORDER BY snapshot_date), to_date('4712-12-31', 'yyyy-mm-dd')) eff_to_date,

SUM(onhand_value) OVER (PARTITION BY cost_org_id, cost_book_id, inventory_item_id, val_unit_id,inventory_org_id,

subinventory_code, locator_id, project_id, task_id, country_of_origin_code, cost_element_id ORDER BY snapshot_date) onhand_value,

unit_cost,

currency_code,

uom_code,

cost_method_code

FROM

(SELECT lc.cost_org_id,

lc.cost_book_id,

lc.inventory_item_id,

lc.val_unit_id,

ct.inventory_org_id,

cit.subinventory_code,

cit.locator_id,

cit.project_id,

cit.task_id,

cit.country_of_origin_code,

lc.cost_element_id,

TRUNC(lc.cost_date) snapshot_date,

SUM(lc.unit_cost * lc.quantity) onhand_value,

0 unit_cost,

lc.currency_code,

lc.uom_code,

ct.cost_method_code

FROM cst_layer_costs lc,

cst_transactions ct,

cst_inv_transactions cit

WHERE NVL(lc.absorption_type, 1) = 1

AND ct.transaction_id = lc.transaction_id

AND ct.cost_method_code = 'ACTUAL'

AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id

GROUP BY lc.cost_org_id,

lc.cost_book_id,

lc.inventory_item_id,

lc.val_unit_id,

ct.inventory_org_id,

cit.subinventory_code,

cit.locator_id,

cit.project_id,

cit.task_id,

cit.country_of_origin_code,

lc.cost_element_id,

TRUNC(lc.cost_date),

lc.currency_code,

lc.uom_code,

ct.cost_method_code

UNION ALL

SELECT DISTINCT lc.cost_org_id,

lc.cost_book_id,

lc.inventory_item_id,

lc.val_unit_id,

-1,

' ',

-1,

' ',

lc.cost_element_id,

TRUNC(lc.cost_date) snapshot_date,

0 onhand_value,

last_value(lc.unit_cost_average) OVER (PARTITION BY lc.cost_org_id, lc.cost_book_id, lc.inventory_item_id,

lc.val_unit_id, lc.cost_element_id, TRUNC(lc.cost_date) ORDER BY lc.cost_date, lc.dep_trxn_id, lc.transaction_id,

lc.rec_trxn_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) unit_cost,

lc.currency_code,

lc.uom_code,

'PERPETUAL_AVERAGE'

FROM cst_perpavg_cost lc

)

UNION ALL

SELECT sc.cost_org_id,

sc.cost_book_id,

sc.inventory_item_id,

sc.val_unit_id,

-1,

' ',

-1,

' ',

scd.cost_element_id,

sc.effective_start_date snapshot_date,

DECODE (sc.effective_end_date , to_date('4712-12-31', 'yyyy-mm-dd'), sc.effective_end_date, sc.effective_end_date + INTERVAL '1' DAY) eff_to_date,

0 onhand_value,

scd.unit_cost,

sc.currency_code,

sc.uom_code,

'STANDARD'

FROM fusion.cst_std_costs sc,

fusion.cst_std_cost_details scd,

cst_cost_profiles_b cp

WHERE sc.status_code = 'PUBLISHED'

AND sc.assignment_level = 'VALUATION_UNIT'

AND sc.val_unit_id IS NOT NULL

AND sc.cost_profile_id = cp.cost_profile_id

AND sc.std_cost_id = scd.std_cost_id

AND cp.cost_method_code = 'STANDARD'