CST_B_COSTED_VU_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Query

SQL_Statement
SELECT COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID, EXPENSE_POOL_ID, COST_ELEMENT_ID, SNAPSHOT_DATE, NVL(LEAD (SNAPSHOT_DATE, 1) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID, COST_ELEMENT_ID ORDER BY SNAPSHOT_DATE), to_date('4712-12-31', 'yyyy-mm-dd')) EFF_TO_DATE, ONHAND_VALUE, UNIT_COST, CURRENCY_CODE, UOM_CODE, COST_METHOD_CODE, CAST (NULL AS NUMBER(18,0)) SCENARIO_ID, CAST (NULL AS VARCHAR2(30)) STATUS_CODE FROM ( SELECT /+ LEADING(CT LCWC VO) USE_HASH(LCWC)/ DISTINCT LCWC.COST_ORG_ID, LCWC.COST_BOOK_ID, LCWC.INVENTORY_ITEM_ID, LCWC.VAL_UNIT_ID, 0 EXPENSE_POOL_ID, LCWC.COST_ELEMENT_ID, TRUNC(LCWC.COST_DATE) SNAPSHOT_DATE, SUM(LCWC.UNIT_COST * LCWC.QUANTITY) OVER (PARTITION BY LCWC.COST_ORG_ID, LCWC.COST_BOOK_ID, LCWC.INVENTORY_ITEM_ID, LCWC.VAL_UNIT_ID, LCWC.COST_ELEMENT_ID ORDER BY TRUNC(LCWC.COST_DATE)) ONHAND_VALUE, (SUM(LCWC.UNIT_COST * LCWC.QUANTITY) OVER (PARTITION BY LCWC.COST_ORG_ID, LCWC.COST_BOOK_ID, LCWC.INVENTORY_ITEM_ID, LCWC.VAL_UNIT_ID, LCWC.COST_ELEMENT_ID ORDER BY TRUNC(LCWC.COST_DATE))) / DECODE(VO.QUANTITY, 0, 1, VO.QUANTITY) UNIT_COST, LCWC.CURRENCY_CODE, LCWC.UOM_CODE, CT.COST_METHOD_CODE FROM ( SELECT LC.LAYER_COST_ID, LC.TRANSACTION_ID, LC.COST_ORG_ID, LC.COST_BOOK_ID, LC.INVENTORY_ITEM_ID, LC.VAL_UNIT_ID, LC.COST_ELEMENT_ID, LC.ABSORPTION_TYPE, LC.COST_DATE, LC.CURRENCY_CODE, LC.UOM_CODE, LC.UNIT_COST, LC.QUANTITY FROM CST_LAYER_COSTS LC WHERE NVL(LC.ABSORPTION_TYPE, 1) = 1 UNION ALL SELECT WC.WRITEOFF_COST_ID, WC.TRANSACTION_ID, WC.COST_ORG_ID, WC.COST_BOOK_ID, WC.INVENTORY_ITEM_ID, WC.VAL_UNIT_ID, WC.COST_ELEMENT_ID, WC.ABSORPTION_TYPE, WC.COST_DATE, WC.CURRENCY_CODE, WC.UOM_CODE, WC.UNIT_COST, WC.QUANTITY FROM CST_WRITEOFF_COSTS WC WHERE WC.ABSORPTION_TYPE = 1 ) LCWC, CST_TRANSACTIONS CT, CST_B_COSTED_VU_ONHAND_V VO WHERE CT.TRANSACTION_ID = LCWC.TRANSACTION_ID AND CT.COST_METHOD_CODE = 'ACTUAL' AND LCWC.COST_ORG_ID = VO.COST_ORG_ID AND LCWC.COST_BOOK_ID = VO.COST_BOOK_ID AND LCWC.INVENTORY_ITEM_ID = VO.INVENTORY_ITEM_ID AND LCWC.VAL_UNIT_ID = VO.VAL_UNIT_ID AND TRUNC(LCWC.COST_DATE) BETWEEN VO.SNAPSHOT_DATE AND (VO.EFF_TO_DATE -1) UNION ALL SELECT DISTINCT LC.COST_ORG_ID, LC.COST_BOOK_ID, LC.INVENTORY_ITEM_ID, LC.VAL_UNIT_ID, 0 EXPENSE_POOL_ID, LC.COST_ELEMENT_ID, TRUNC(LC.COST_DATE) SNAPSHOT_DATE, 0 ONHAND_VALUE, LAST_VALUE(LC.UNIT_COST_AVERAGE) OVER (PARTITION BY LC.COST_ORG_ID, LC.COST_BOOK_ID, LC.INVENTORY_ITEM_ID, LC.VAL_UNIT_ID, LC.COST_ELEMENT_ID, TRUNC(LC.COST_DATE) ORDER BY LC.COST_DATE, LC.DEP_TRXN_ID, LC.TRANSACTION_ID, LC.REC_TRXN_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) UNIT_COST, LC.CURRENCY_CODE, LC.UOM_CODE, 'PERPETUAL_AVERAGE' FROM CST_PERPAVG_COST LC )

SQL_Statement

SELECT COST_ORG_ID,

COST_BOOK_ID,

INVENTORY_ITEM_ID,

VAL_UNIT_ID,

EXPENSE_POOL_ID,

COST_ELEMENT_ID,

SNAPSHOT_DATE,

NVL(LEAD (SNAPSHOT_DATE, 1) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID, COST_ELEMENT_ID ORDER BY SNAPSHOT_DATE), to_date('4712-12-31', 'yyyy-mm-dd')) EFF_TO_DATE,

ONHAND_VALUE,

UNIT_COST,

CURRENCY_CODE,

UOM_CODE,

COST_METHOD_CODE,

CAST (NULL AS NUMBER(18,0)) SCENARIO_ID,

CAST (NULL AS VARCHAR2(30)) STATUS_CODE

FROM

( SELECT /*+ LEADING(CT LCWC VO) USE_HASH(LCWC)*/ DISTINCT LCWC.COST_ORG_ID,

LCWC.COST_BOOK_ID,

LCWC.INVENTORY_ITEM_ID,

LCWC.VAL_UNIT_ID,

0 EXPENSE_POOL_ID,

LCWC.COST_ELEMENT_ID,

TRUNC(LCWC.COST_DATE) SNAPSHOT_DATE,

SUM(LCWC.UNIT_COST * LCWC.QUANTITY) OVER (PARTITION BY LCWC.COST_ORG_ID, LCWC.COST_BOOK_ID, LCWC.INVENTORY_ITEM_ID, LCWC.VAL_UNIT_ID, LCWC.COST_ELEMENT_ID ORDER BY TRUNC(LCWC.COST_DATE)) ONHAND_VALUE,

(SUM(LCWC.UNIT_COST * LCWC.QUANTITY) OVER (PARTITION BY LCWC.COST_ORG_ID, LCWC.COST_BOOK_ID, LCWC.INVENTORY_ITEM_ID, LCWC.VAL_UNIT_ID, LCWC.COST_ELEMENT_ID ORDER BY TRUNC(LCWC.COST_DATE))) / DECODE(VO.QUANTITY, 0, 1, VO.QUANTITY) UNIT_COST,

LCWC.CURRENCY_CODE,

LCWC.UOM_CODE,

CT.COST_METHOD_CODE

FROM

(

SELECT LC.LAYER_COST_ID, LC.TRANSACTION_ID, LC.COST_ORG_ID, LC.COST_BOOK_ID, LC.INVENTORY_ITEM_ID, LC.VAL_UNIT_ID, LC.COST_ELEMENT_ID, LC.ABSORPTION_TYPE,

LC.COST_DATE, LC.CURRENCY_CODE, LC.UOM_CODE, LC.UNIT_COST, LC.QUANTITY FROM CST_LAYER_COSTS LC WHERE NVL(LC.ABSORPTION_TYPE, 1) = 1

UNION ALL

SELECT WC.WRITEOFF_COST_ID, WC.TRANSACTION_ID, WC.COST_ORG_ID, WC.COST_BOOK_ID, WC.INVENTORY_ITEM_ID, WC.VAL_UNIT_ID, WC.COST_ELEMENT_ID, WC.ABSORPTION_TYPE,

WC.COST_DATE, WC.CURRENCY_CODE, WC.UOM_CODE, WC.UNIT_COST, WC.QUANTITY FROM CST_WRITEOFF_COSTS WC WHERE WC.ABSORPTION_TYPE = 1

) LCWC,

CST_TRANSACTIONS CT,

CST_B_COSTED_VU_ONHAND_V VO

WHERE CT.TRANSACTION_ID = LCWC.TRANSACTION_ID

AND CT.COST_METHOD_CODE = 'ACTUAL'

AND LCWC.COST_ORG_ID = VO.COST_ORG_ID

AND LCWC.COST_BOOK_ID = VO.COST_BOOK_ID

AND LCWC.INVENTORY_ITEM_ID = VO.INVENTORY_ITEM_ID

AND LCWC.VAL_UNIT_ID = VO.VAL_UNIT_ID

AND TRUNC(LCWC.COST_DATE) BETWEEN VO.SNAPSHOT_DATE AND (VO.EFF_TO_DATE -1)

UNION ALL

SELECT DISTINCT LC.COST_ORG_ID,

LC.COST_BOOK_ID,

LC.INVENTORY_ITEM_ID,

LC.VAL_UNIT_ID,

0 EXPENSE_POOL_ID,

LC.COST_ELEMENT_ID,

TRUNC(LC.COST_DATE) SNAPSHOT_DATE,

0 ONHAND_VALUE,

LAST_VALUE(LC.UNIT_COST_AVERAGE) OVER (PARTITION BY LC.COST_ORG_ID, LC.COST_BOOK_ID, LC.INVENTORY_ITEM_ID, LC.VAL_UNIT_ID, LC.COST_ELEMENT_ID, TRUNC(LC.COST_DATE) ORDER BY LC.COST_DATE, LC.DEP_TRXN_ID,

LC.TRANSACTION_ID, LC.REC_TRXN_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) UNIT_COST,

LC.CURRENCY_CODE,

LC.UOM_CODE,

'PERPETUAL_AVERAGE'

FROM CST_PERPAVG_COST LC

)