CST_COSTED_VU_COSTS

詳細

スキーマ: FUSION
オブジェクト所有者: CST
オブジェクト・タイプ: VIEW

列

名前
COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID VAL_UNIT_ID EXPENSE_POOL_ID COST_ELEMENT_ID SNAPSHOT_DATE EFF_TO_DATE ONHAND_VALUE UNIT_COST CURRENCY_CODE UOM_CODE COST_METHOD_CODE SCENARIO_ID PERIOD_NAME

名前

COST_ORG_ID

COST_BOOK_ID

INVENTORY_ITEM_ID

VAL_UNIT_ID

EXPENSE_POOL_ID

COST_ELEMENT_ID

SNAPSHOT_DATE

EFF_TO_DATE

ONHAND_VALUE

UNIT_COST

CURRENCY_CODE

UOM_CODE

COST_METHOD_CODE

SCENARIO_ID

PERIOD_NAME

問合せ

SQL_Statement
SELECT COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID, EXPENSE_POOL_ID, COST_ELEMENT_ID, SNAPSHOT_DATE, EFF_TO_DATE, ONHAND_VALUE, UNIT_COST, CURRENCY_CODE, UOM_CODE, COST_METHOD_CODE, SCENARIO_ID, CAST(NULL AS VARCHAR2(15)) PERIOD_NAME FROM CST_COSTED_VU_COSTS_MV UNION ALL SELECT SC.COST_ORG_ID, SC.COST_BOOK_ID, SC.INVENTORY_ITEM_ID, SC.VAL_UNIT_ID, SCD.EXPENSE_POOL_ID, 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, SUM(SCD.UNIT_COST) AS UNIT_COST, SC.CURRENCY_CODE, SC.UOM_CODE, 'STANDARD', SC.SCENARIO_ID, CAST(NULL AS VARCHAR2(15)) PERIOD_NAME FROM CST_STD_COSTS_V SC, CST_STD_COST_DETAILS SCD, CST_COST_PROFILES_B CP WHERE 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' AND SC.STATUS_CODE = 'PUBLISHED' GROUP BY SC.COST_ORG_ID, SC.COST_BOOK_ID, SC.INVENTORY_ITEM_ID, SC.VAL_UNIT_ID, SCD.EXPENSE_POOL_ID, SCD.COST_ELEMENT_ID, SC.EFFECTIVE_START_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), SC.CURRENCY_CODE, SC.UOM_CODE, SC.SCENARIO_ID, SC.STATUS_CODE UNION ALL SELECT cpac.cost_org_id, cpac.cost_book_id, cpac.inventory_item_id, cpac.val_unit_id, cast(0 as number) expense_pool_id, cpac.cost_element_id, cps.start_date snapshot_date, decode(cps.status_code,'O',to_date('4712-12-31', 'yyyy-mm-dd') ,cps.end_date + INTERVAL '1' DAY) eff_to_date, cast(0 as number) onhand_value, cpac.periodic_avg_cost unit_cost, cpac.currency_code, cpac.uom_code, cast('PERIODIC_AVERAGE' AS varchar2(30)) cost_method_code, cast(null as number(18,0)) scenario_id, cpac.period_name from cst_periodic_avg_costs cpac, cst_period_statuses cps where cps.cost_org_id = cpac.cost_org_id and cps.cost_book_id = cpac.cost_book_id and cps.period_name = cpac.period_name

SQL_Statement

SELECT

COST_ORG_ID,

COST_BOOK_ID,

INVENTORY_ITEM_ID,

VAL_UNIT_ID,

EXPENSE_POOL_ID,

COST_ELEMENT_ID,

SNAPSHOT_DATE,

EFF_TO_DATE,

ONHAND_VALUE,

UNIT_COST,

CURRENCY_CODE,

UOM_CODE,

COST_METHOD_CODE,

SCENARIO_ID,

CAST(NULL AS VARCHAR2(15)) PERIOD_NAME

FROM

CST_COSTED_VU_COSTS_MV

UNION ALL

SELECT

SC.COST_ORG_ID,

SC.COST_BOOK_ID,

SC.INVENTORY_ITEM_ID,

SC.VAL_UNIT_ID,

SCD.EXPENSE_POOL_ID,

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,

SUM(SCD.UNIT_COST) AS UNIT_COST,

SC.CURRENCY_CODE,

SC.UOM_CODE,

'STANDARD',

SC.SCENARIO_ID,

CAST(NULL AS VARCHAR2(15)) PERIOD_NAME

FROM CST_STD_COSTS_V SC,

CST_STD_COST_DETAILS SCD,

CST_COST_PROFILES_B CP

WHERE 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'

AND SC.STATUS_CODE = 'PUBLISHED'

GROUP BY SC.COST_ORG_ID,

SC.COST_BOOK_ID,

SC.INVENTORY_ITEM_ID,

SC.VAL_UNIT_ID,

SCD.EXPENSE_POOL_ID,

SCD.COST_ELEMENT_ID,

SC.EFFECTIVE_START_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),

SC.CURRENCY_CODE,

SC.UOM_CODE,

SC.SCENARIO_ID,

SC.STATUS_CODE

UNION ALL

SELECT

cpac.cost_org_id,

cpac.cost_book_id,

cpac.inventory_item_id,

cpac.val_unit_id,

cast(0 as number) expense_pool_id,

cpac.cost_element_id,

cps.start_date snapshot_date,

decode(cps.status_code,'O',to_date('4712-12-31', 'yyyy-mm-dd') ,cps.end_date + INTERVAL '1' DAY) eff_to_date,

cast(0 as number) onhand_value,

cpac.periodic_avg_cost unit_cost,

cpac.currency_code,

cpac.uom_code,

cast('PERIODIC_AVERAGE' AS varchar2(30)) cost_method_code,

cast(null as number(18,0)) scenario_id,

cpac.period_name

from

cst_periodic_avg_costs cpac,

cst_period_statuses cps

where

cps.cost_org_id = cpac.cost_org_id

and cps.cost_book_id = cpac.cost_book_id

and cps.period_name = cpac.period_name