CST_COSTED_ATTR_OTBI

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID VAL_UNIT_ID BUSINESS_UNIT_ID INVENTORY_ORG_ID SUBINVENTORY_CODE LOCATOR_ID PROJECT_ID TASK_ID COUNTRY_OF_ORIGIN_CODE VAL_STRUCTURE_TYPE_CODE SNAPSHOT_DATE EFF_TO_DATE QUANTITY AMOUNT UOM_CODE CURRENCY_CODE PK_INDEX

Name

COST_ORG_ID

COST_BOOK_ID

INVENTORY_ITEM_ID

VAL_UNIT_ID

BUSINESS_UNIT_ID

INVENTORY_ORG_ID

SUBINVENTORY_CODE

LOCATOR_ID

PROJECT_ID

TASK_ID

COUNTRY_OF_ORIGIN_CODE

VAL_STRUCTURE_TYPE_CODE

SNAPSHOT_DATE

EFF_TO_DATE

QUANTITY

AMOUNT

UOM_CODE

CURRENCY_CODE

PK_INDEX

Query

SQL_Statement
with ao as ( select ao.cost_org_id, ao.cost_book_id, ao.val_unit_id, ao.inventory_item_id, greatest(ao.snapshot_date,vo.snapshot_date) snapshot_date, least(ao.eff_to_date,vo.eff_to_date) eff_to_date, ao.quantity, vo.quantity vu_quantity, ao.inventory_org_id, ao.subinventory_code, ao.locator_id, ao.project_id, ao.task_id, ao.country_of_origin_code from cst_costed_attr_onhand_v ao, cst_costed_vu_onhand_v vo where ao.cost_org_id = vo.cost_org_id AND ao.cost_book_id = vo.cost_book_id AND ao.inventory_item_id = vo.inventory_item_id AND ao.val_unit_id = vo.val_unit_id AND ((ao.snapshot_date BETWEEN vo.snapshot_date and (vo.eff_to_date -1)) OR (vo.snapshot_date BETWEEN ao.snapshot_date and (ao.eff_to_date -1))) ) SELECT av.cost_org_id, av.cost_book_id, av.inventory_item_id, av.val_unit_id, nvl(iop.profit_center_bu_id,iop.business_unit_id) business_unit_id, ao.inventory_org_id, ao.subinventory_code, ao.locator_id, ao.project_id, ao.task_id, ao.country_of_origin_code, vs.val_structure_type_code, greatest(av.snapshot_date,ao.snapshot_date) snapshot_date, least(av.eff_to_date,ao.eff_to_date) eff_to_date, ao.quantity, sum(decode (av.cost_method_code, 'ACTUAL', (av.onhand_value/decode(ao.vu_quantity, 0, decode(ao.quantity,0,1,ao.quantity), ao.vu_quantity)ao.quantity), av.unit_costao.quantity)) amount, av.uom_code , av.currency_code, (av.cost_org_id\|\|'~'\|\| av.cost_book_id\|\|'~'\|\|av.inventory_item_id\|\|'~'\|\|av.val_unit_id\|\|'~'\|\|to_char (greatest(av.snapshot_date,ao.snapshot_date), 'mmddyyyy')\|\|'~'\|\|ao.inventory_org_id\|\|'~' \|\|ao.subinventory_code\|\|'~'\|\|ao.locator_id\|\|'~'\|\|ao.project_id\|\|'~'\|\|ao.task_id\|\|'~'\|\|ao.country_of_origin_code ) pk_index FROM cst_costed_vu_costs_v av, ao , cst_val_units_b vu, cst_val_structures_b vs, inv_org_parameters iop WHERE av.cost_org_id = ao.cost_org_id AND av.cost_book_id = ao.cost_book_id AND av.inventory_item_id = ao.inventory_item_id AND av.val_unit_id = ao.val_unit_id AND ((av.snapshot_date BETWEEN ao.snapshot_date and (ao.eff_to_date -1)) OR (ao.snapshot_date BETWEEN av.snapshot_date and (av.eff_to_date -1))) AND av.val_unit_id = vu.val_unit_id AND vu.val_structure_id = vs.val_structure_id AND ao.inventory_org_id = iop.organization_id GROUP BY av.cost_org_id, av.cost_book_id, av.inventory_item_id, av.val_unit_id, nvl(iop.profit_center_bu_id,iop.business_unit_id), ao.inventory_org_id, ao.subinventory_code, ao.locator_id, ao.project_id, ao.task_id, ao.country_of_origin_code, vs.val_structure_type_code, greatest(av.snapshot_date,ao.snapshot_date), ao.quantity, least(av.eff_to_date,ao.eff_to_date) , av.uom_code , av.currency_code

SQL_Statement

with ao as

(

select ao.cost_org_id,

ao.cost_book_id,

ao.val_unit_id,

ao.inventory_item_id,

greatest(ao.snapshot_date,vo.snapshot_date) snapshot_date,

least(ao.eff_to_date,vo.eff_to_date) eff_to_date,

ao.quantity,

vo.quantity vu_quantity,

ao.inventory_org_id,

ao.subinventory_code,

ao.locator_id,

ao.project_id,

ao.task_id,

ao.country_of_origin_code

from

cst_costed_attr_onhand_v ao,

cst_costed_vu_onhand_v vo

where

ao.cost_org_id = vo.cost_org_id

AND ao.cost_book_id = vo.cost_book_id

AND ao.inventory_item_id = vo.inventory_item_id

AND ao.val_unit_id = vo.val_unit_id

AND ((ao.snapshot_date BETWEEN vo.snapshot_date and (vo.eff_to_date -1)) OR (vo.snapshot_date BETWEEN ao.snapshot_date and (ao.eff_to_date -1)))

)

SELECT av.cost_org_id,

av.cost_book_id,

av.inventory_item_id,

av.val_unit_id,

nvl(iop.profit_center_bu_id,iop.business_unit_id) business_unit_id,

ao.inventory_org_id,

ao.subinventory_code,

ao.locator_id,

ao.project_id,

ao.task_id,

ao.country_of_origin_code,

vs.val_structure_type_code,

greatest(av.snapshot_date,ao.snapshot_date) snapshot_date,

least(av.eff_to_date,ao.eff_to_date) eff_to_date,

ao.quantity,

sum(decode (av.cost_method_code, 'ACTUAL', (av.onhand_value/decode(ao.vu_quantity, 0, decode(ao.quantity,0,1,ao.quantity), ao.vu_quantity)*ao.quantity), av.unit_cost*ao.quantity)) amount,

av.uom_code ,

av.currency_code,

(av.cost_org_id||'~'|| av.cost_book_id||'~'||av.inventory_item_id||'~'||av.val_unit_id||'~'||to_char (greatest(av.snapshot_date,ao.snapshot_date), 'mmddyyyy')||'~'||ao.inventory_org_id||'~' ||ao.subinventory_code||'~'||ao.locator_id||'~'||ao.project_id||'~'||ao.task_id||'~'||ao.country_of_origin_code ) pk_index

FROM cst_costed_vu_costs_v av,

ao ,

cst_val_units_b vu,

cst_val_structures_b vs,

inv_org_parameters iop

WHERE av.cost_org_id = ao.cost_org_id

AND av.cost_book_id = ao.cost_book_id

AND av.inventory_item_id = ao.inventory_item_id

AND av.val_unit_id = ao.val_unit_id

AND ((av.snapshot_date BETWEEN ao.snapshot_date and (ao.eff_to_date -1)) OR (ao.snapshot_date BETWEEN av.snapshot_date and (av.eff_to_date -1)))

AND av.val_unit_id = vu.val_unit_id

AND vu.val_structure_id = vs.val_structure_id

AND ao.inventory_org_id = iop.organization_id

GROUP BY av.cost_org_id,

av.cost_book_id,

av.inventory_item_id,

av.val_unit_id,

nvl(iop.profit_center_bu_id,iop.business_unit_id),

ao.inventory_org_id,

ao.subinventory_code,

ao.locator_id,

ao.project_id,

ao.task_id,

ao.country_of_origin_code,

vs.val_structure_type_code,

greatest(av.snapshot_date,ao.snapshot_date),

ao.quantity,

least(av.eff_to_date,ao.eff_to_date) ,

av.uom_code ,

av.currency_code