CST_ACCTD_ATTR_OTBI

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Query

SQL_Statement
SELECT av.cost_org_id, av.cost_book_id, av.inventory_item_id, av.val_unit_id, av.business_unit_id, av.inventory_org_id, av.subinventory_code, av.locator_id, av.project_id, av.task_id, av.country_of_origin_code, av.val_structure_type_code, ao.quantity, greatest(av.snapshot_date,ao.snapshot_date) snapshot_date, least(av.eff_to_date,ao.eff_to_date) eff_to_date, av.costed_value, av.accounted_value amount, av.unaccounted_value unaccounted_value, av.excluded_from_acctg_value excluded_from_acctg_value, 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_acctd_attr_values_v av, cst_costed_attr_onhand_v ao WHERE av.cost_org_id = ao.cost_org_id AND av.cost_book_id = ao.cost_book_id AND av.val_unit_id = ao.val_unit_id AND av.inventory_item_id = ao.inventory_item_id AND av.inventory_org_id = ao.inventory_org_id AND av.subinventory_code = ao.subinventory_code AND nvl(av.locator_id, 0) = nvl(ao.locator_id, 0) AND nvl(av.project_id, 0) = nvl(ao.project_id, 0) AND nvl(av.task_id, 0) = nvl(ao.task_id, 0) AND nvl(av.country_of_origin_code, '~') = nvl(ao.country_of_origin_code, '~') 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)))

SQL_Statement

SELECT av.cost_org_id,

av.cost_book_id,

av.inventory_item_id,

av.val_unit_id,

av.business_unit_id,

av.inventory_org_id,

av.subinventory_code,

av.locator_id,

av.project_id,

av.task_id,

av.country_of_origin_code,

av.val_structure_type_code,

ao.quantity,

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

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

av.costed_value,

av.accounted_value amount,

av.unaccounted_value unaccounted_value,

av.excluded_from_acctg_value excluded_from_acctg_value,

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_acctd_attr_values_v av,

cst_costed_attr_onhand_v ao

WHERE

av.cost_org_id = ao.cost_org_id AND

av.cost_book_id = ao.cost_book_id AND

av.val_unit_id = ao.val_unit_id AND

av.inventory_item_id = ao.inventory_item_id AND

av.inventory_org_id = ao.inventory_org_id AND

av.subinventory_code = ao.subinventory_code AND

nvl(av.locator_id, 0) = nvl(ao.locator_id, 0) AND

nvl(av.project_id, 0) = nvl(ao.project_id, 0) AND

nvl(av.task_id, 0) = nvl(ao.task_id, 0) AND

nvl(av.country_of_origin_code, '~') = nvl(ao.country_of_origin_code, '~')

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)))