CST_STD_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
STD_COST_ID SCENARIO_ID COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID VAL_UNIT_ID EFFECTIVE_START_DATE EFFECTIVE_END_DATE VAL_STRUCTURE_ID COST_PROFILE_ID INVENTORY_ORG_ID ASSIGNMENT_LEVEL STATUS_CODE TOTAL_COST CURRENCY_CODE UOM_CODE PREVIOUS_STD_COST_ID PREVIOUS_EFFECTIVE_START_DATE PREVIOUS_EFFECTIVE_END_DATE PREVIOUS_TOTAL_COST STD_COST_ADJUST_ID STAGED_FOR_STD_COST_ADJ_FLAG EXTERNAL_SYSTEM_REFERENCE EXTERNAL_SYSTEM_REF_ID INV_VAL_USE_FLAG LAST_USED_DATE SCENARIO_EVENT_ID SCENARIO_ROLLUP_HEADER_ID ORGANIZATION_ID VAL_STRUCTURE_TYPE_CODE CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN REQUEST_ID STD_COST_SOURCE

Name

STD_COST_ID

SCENARIO_ID

COST_ORG_ID

COST_BOOK_ID

INVENTORY_ITEM_ID

VAL_UNIT_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

VAL_STRUCTURE_ID

COST_PROFILE_ID

INVENTORY_ORG_ID

ASSIGNMENT_LEVEL

STATUS_CODE

TOTAL_COST

CURRENCY_CODE

UOM_CODE

PREVIOUS_STD_COST_ID

PREVIOUS_EFFECTIVE_START_DATE

PREVIOUS_EFFECTIVE_END_DATE

PREVIOUS_TOTAL_COST

STD_COST_ADJUST_ID

STAGED_FOR_STD_COST_ADJ_FLAG

EXTERNAL_SYSTEM_REFERENCE

EXTERNAL_SYSTEM_REF_ID

INV_VAL_USE_FLAG

LAST_USED_DATE

SCENARIO_EVENT_ID

SCENARIO_ROLLUP_HEADER_ID

ORGANIZATION_ID

VAL_STRUCTURE_TYPE_CODE

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

REQUEST_ID

STD_COST_SOURCE

Query

SQL_Statement
SELECT csc.std_cost_id, csc.scenario_id, csc.cost_org_id, csc.cost_book_id, csc.inventory_item_id, csc.val_unit_id, csc.effective_start_date, csc.effective_end_date, csc.val_structure_id, csc.cost_profile_id, csc.inventory_org_id, csc.assignment_level, csc.status_code, csc.total_cost, csc.currency_code, csc.uom_code, csc.previous_std_cost_id, csc.previous_effective_start_date, csc.previous_effective_end_date, csc.previous_total_cost, csc.std_cost_adjust_id, csc.staged_for_std_cost_adj_flag, csc.external_system_reference, csc.external_system_ref_id, csc.inv_val_use_flag, csc.last_used_date, csc.scenario_event_id, csc.scenario_rollup_header_id, csc.organization_id, vs.val_structure_type_code, csc.created_by, csc.creation_date, csc.last_updated_by, csc.last_update_date, csc.last_update_login, csc.request_id, CASE WHEN source_di in ('Y', '"Y"') THEN 'EXCEL' WHEN scenario_rollup_header_id IS NOT NULL THEN 'COST_PLANNING' WHEN external_system_ref_id IS NOT NULL THEN 'INTERFACE' ELSE 'COST_ACCOUNTING' END std_cost_source FROM cst_std_costs csc, cst_val_structures_b vs WHERE vs.val_structure_id = csc.val_structure_id AND vs.val_structure_type_code = 'ASSET' AND csc.cost_type = 'STANDARD' UNION ALL SELECT DISTINCT csc.std_cost_id, csc.scenario_id, csc.cost_org_id, csc.cost_book_id, csc.inventory_item_id, evu.val_unit_id, csc.effective_start_date, csc.effective_end_date, ecp.val_structure_id, ecp.cost_profile_id, csc.inventory_org_id, csc.assignment_level, csc.status_code, csc.total_cost, csc.currency_code, csc.uom_code, csc.previous_std_cost_id, csc.previous_effective_start_date, csc.previous_effective_end_date, csc.previous_total_cost, csc.std_cost_adjust_id, csc.staged_for_std_cost_adj_flag, csc.external_system_reference, csc.external_system_ref_id, csc.inv_val_use_flag, csc.last_used_date, csc.scenario_event_id, csc.scenario_rollup_header_id, csc.organization_id, vs.val_structure_type_code, csc.created_by, csc.creation_date, csc.last_updated_by, csc.last_update_date, csc.last_update_login, csc.request_id, NULL std_cost_source FROM cst_std_costs csc, cst_item_cost_profiles icp, cst_cost_profiles_b acp, cst_cost_profiles_b ecp, cst_val_units_b avu, cst_val_units_b evu, cst_val_unit_details avud, cst_val_unit_details evud, cst_val_unit_combinations avuc, cst_val_unit_combinations evuc, cst_val_structures_b vs WHERE icp.cost_org_id = csc.cost_org_id AND icp.cost_book_id = csc.cost_book_id AND icp.inventory_item_id = csc.inventory_item_id AND icp.asset_cost_profile_id = acp.cost_profile_id AND icp.expense_cost_profile_id = ecp.cost_profile_id AND avu.cost_org_id = icp.cost_org_id AND avu.cost_book_id = icp.cost_book_id AND avu.val_structure_id = acp.val_structure_id AND evu.cost_org_id = icp.cost_org_id AND evu.cost_book_id = icp.cost_book_id AND evu.val_structure_id = ecp.val_structure_id AND avud.val_unit_id = avu.val_unit_id AND evud.val_unit_id = evu.val_unit_id AND avud.val_unit_combination_id = avuc.val_unit_combination_id AND evud.val_unit_combination_id = evuc.val_unit_combination_id AND avu.cost_org_id = csc.cost_org_id AND avu.cost_book_id = csc.cost_book_id AND avuc.cost_org_code = evuc.cost_org_code AND nvl(avuc.inv_org_code, ' ') = nvl(evuc.inv_org_code, ' ') AND nvl(avuc.subinventory_code, ' ') = nvl(evuc.subinventory_code, ' ') AND nvl(avuc.lot_number, ' ') = nvl(evuc.lot_number, ' ') AND nvl(avuc.grade_code, ' ') = nvl(evuc.grade_code, ' ') AND nvl(avuc.serial_number, ' ') = nvl(evuc.serial_number, ' ') AND nvl(avuc.locator_id, 0) = nvl(evuc.locator_id, 0) AND vs.val_structure_id = evu.val_structure_id AND vs.val_structure_type_code = 'EXPENSE' AND avu.val_unit_id = csc.val_unit_id AND csc.cost_type = 'STANDARD'

SQL_Statement

SELECT

csc.std_cost_id,

csc.scenario_id,

csc.cost_org_id,

csc.cost_book_id,

csc.inventory_item_id,

csc.val_unit_id,

csc.effective_start_date,

csc.effective_end_date,

csc.val_structure_id,

csc.cost_profile_id,

csc.inventory_org_id,

csc.assignment_level,

csc.status_code,

csc.total_cost,

csc.currency_code,

csc.uom_code,

csc.previous_std_cost_id,

csc.previous_effective_start_date,

csc.previous_effective_end_date,

csc.previous_total_cost,

csc.std_cost_adjust_id,

csc.staged_for_std_cost_adj_flag,

csc.external_system_reference,

csc.external_system_ref_id,

csc.inv_val_use_flag,

csc.last_used_date,

csc.scenario_event_id,

csc.scenario_rollup_header_id,

csc.organization_id,

vs.val_structure_type_code,

csc.created_by,

csc.creation_date,

csc.last_updated_by,

csc.last_update_date,

csc.last_update_login,

csc.request_id,

CASE WHEN source_di in ('Y', '"Y"') THEN 'EXCEL'

WHEN scenario_rollup_header_id IS NOT NULL THEN 'COST_PLANNING'

WHEN external_system_ref_id IS NOT NULL THEN 'INTERFACE'

ELSE 'COST_ACCOUNTING'

END std_cost_source

FROM

cst_std_costs csc,

cst_val_structures_b vs

WHERE

vs.val_structure_id = csc.val_structure_id

AND vs.val_structure_type_code = 'ASSET'

AND csc.cost_type = 'STANDARD'

UNION ALL

SELECT DISTINCT

csc.std_cost_id,

csc.scenario_id,

csc.cost_org_id,

csc.cost_book_id,

csc.inventory_item_id,

evu.val_unit_id,

csc.effective_start_date,

csc.effective_end_date,

ecp.val_structure_id,

ecp.cost_profile_id,

csc.inventory_org_id,

csc.assignment_level,

csc.status_code,

csc.total_cost,

csc.currency_code,

csc.uom_code,

csc.previous_std_cost_id,

csc.previous_effective_start_date,

csc.previous_effective_end_date,

csc.previous_total_cost,

csc.std_cost_adjust_id,

csc.staged_for_std_cost_adj_flag,

csc.external_system_reference,

csc.external_system_ref_id,

csc.inv_val_use_flag,

csc.last_used_date,

csc.scenario_event_id,

csc.scenario_rollup_header_id,

csc.organization_id,

vs.val_structure_type_code,

csc.created_by,

csc.creation_date,

csc.last_updated_by,

csc.last_update_date,

csc.last_update_login,

csc.request_id,

NULL std_cost_source

FROM

cst_std_costs csc,

cst_item_cost_profiles icp,

cst_cost_profiles_b acp,

cst_cost_profiles_b ecp,

cst_val_units_b avu,

cst_val_units_b evu,

cst_val_unit_details avud,

cst_val_unit_details evud,

cst_val_unit_combinations avuc,

cst_val_unit_combinations evuc,

cst_val_structures_b vs

WHERE

icp.cost_org_id = csc.cost_org_id

AND icp.cost_book_id = csc.cost_book_id

AND icp.inventory_item_id = csc.inventory_item_id

AND icp.asset_cost_profile_id = acp.cost_profile_id

AND icp.expense_cost_profile_id = ecp.cost_profile_id

AND avu.cost_org_id = icp.cost_org_id

AND avu.cost_book_id = icp.cost_book_id

AND avu.val_structure_id = acp.val_structure_id

AND evu.cost_org_id = icp.cost_org_id

AND evu.cost_book_id = icp.cost_book_id

AND evu.val_structure_id = ecp.val_structure_id

AND avud.val_unit_id = avu.val_unit_id

AND evud.val_unit_id = evu.val_unit_id

AND avud.val_unit_combination_id = avuc.val_unit_combination_id

AND evud.val_unit_combination_id = evuc.val_unit_combination_id

AND avu.cost_org_id = csc.cost_org_id

AND avu.cost_book_id = csc.cost_book_id

AND avuc.cost_org_code = evuc.cost_org_code

AND nvl(avuc.inv_org_code, ' ') = nvl(evuc.inv_org_code, ' ')

AND nvl(avuc.subinventory_code, ' ') = nvl(evuc.subinventory_code, ' ')

AND nvl(avuc.lot_number, ' ') = nvl(evuc.lot_number, ' ')

AND nvl(avuc.grade_code, ' ') = nvl(evuc.grade_code, ' ')

AND nvl(avuc.serial_number, ' ') = nvl(evuc.serial_number, ' ')

AND nvl(avuc.locator_id, 0) = nvl(evuc.locator_id, 0)

AND vs.val_structure_id = evu.val_structure_id

AND vs.val_structure_type_code = 'EXPENSE'

AND avu.val_unit_id = csc.val_unit_id

AND csc.cost_type = 'STANDARD'