1. Tables and Views for SCM
  2. CST_INVENTORY_VALUES_LW_V

CST_INVENTORY_VALUES_LW_V

Details

  • Schema: FUSION

  • Object owner: CST

  • Object type: VIEW

Columns

Name

COST_ORG_ID

INVENTORY_ORG_ID

VAL_STRUCTURE_TYPE_CODE

INVENTORY_VALUE

CURRENCY_CODE

Query

SQL_Statement

WITH LATEST_PERIOD_DATE as (SELECT /*+ materialize*/inventory_org_id, MAX(period_start_date) max_period_start_date from cst_attr_onhand_summary group by inventory_org_id)

SELECT/*+ leading(fs) */

caos.cost_org_id cost_org_id,

caos.inventory_org_id inventory_org_id,

caos.val_structure_type_code val_structure_type_code,

SUM(caos.inventory_value) inventory_value,

fc.currency_code currency_code

FROM

cst_attr_onhand_summary caos,

fnd_currencies_vl fc,

cst_cost_org_books ccob, LATEST_PERIOD_DATE lpd

WHERE caos.period_start_date = lpd.max_period_start_date

AND caos.inventory_org_id = lpd.inventory_org_id

AND ccob.cost_book_id = caos.cost_book_id

AND ccob.cost_org_id = caos.cost_org_id

AND fc.currency_code = ccob.currency_code

AND caos.val_structure_type_code = 'ASSET'

AND ccob.primary_book_flag = 'Y'

GROUP BY

caos.cost_org_id,

caos.val_structure_type_code,

caos.inventory_org_id,

fc.currency_code