1. Tables and Views for SCM
  2. CST_B_COSTED_DEL_COSTS_V

CST_B_COSTED_DEL_COSTS_V

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,

REC_TRXN_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, REC_TRXN_ID,

COST_ELEMENT_ID ORDER BY SNAPSHOT_DATE), to_date('4712-12-31', 'yyyy-mm-dd')) EFF_TO_DATE,

SUM(ONHAND_VALUE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID, REC_TRXN_ID, COST_ELEMENT_ID

ORDER BY SNAPSHOT_DATE) ONHAND_VALUE,

UNIT_COST,

CURRENCY_CODE,

UOM_CODE,

COST_METHOD_CODE

FROM

( WITH N_LYR AS

(

SELECT NEG_LYR.TRANSACTION_ID NEG_TRANSACTION_ID,

NEG_LYR.REC_TRXN_ID NEG_REC_TRXN_ID,

NEG_LYR.DEP_TRXN_ID NEG_DEP_TRXN_ID,

NEG_LYR.QUANTITY,

DECODE(RES_LYR.REC_TRXN_ID, 0, NEG_LYR.TRANSACTION_ID, RES_LYR.REC_TRXN_ID) RESOLVE_REC_TRXN_ID,

RES_LYR.COST_DATE,

RES_LYR.QUANTITY RESOLVE_QUANTITY,

RES_LYR.QUANTITY / NEG_LYR.QUANTITY PERCENT_OF_NEG_LAYER

FROM CST_TRANSACTION_LAYERS NEG_LYR,

CST_TRANSACTION_LAYERS RES_LYR

WHERE NEG_LYR.TRANSACTION_ID = NEG_LYR.REC_TRXN_ID

AND NEG_LYR.TRANSACTION_ID = NEG_LYR.DEP_TRXN_ID

AND NEG_LYR.REC_TRXN_ID = RES_LYR.TRANSACTION_ID

AND (RES_LYR.COST_DATE > NEG_LYR.COST_DATE

OR RES_LYR.REC_TRXN_ID = 0

OR RES_LYR.REC_TRXN_ID =RES_LYR.DEP_TRXN_ID)

)

SELECT DISTINCT LC.COST_ORG_ID,

LC.COST_BOOK_ID,

LC.INVENTORY_ITEM_ID,

LC.VAL_UNIT_ID,

NVL(N_LYR.RESOLVE_REC_TRXN_ID, LC.REC_TRXN_ID) REC_TRXN_ID,

LC.COST_ELEMENT_ID,

TRUNC(NVL(N_LYR.COST_DATE, LC.COST_DATE)) SNAPSHOT_DATE,

SUM(LC.UNIT_COST * LC.QUANTITY * NVL(PERCENT_OF_NEG_LAYER, 1)) ONHAND_VALUE,

0 UNIT_COST,

LC.CURRENCY_CODE,

LC.UOM_CODE,

CT.COST_METHOD_CODE

FROM CST_LAYER_COSTS LC,

N_LYR,

CST_TRANSACTIONS CT

WHERE NVL(LC.ABSORPTION_TYPE, 1) = 1

AND CT.TRANSACTION_ID = LC.TRANSACTION_ID

AND CT.COST_METHOD_CODE = 'ACTUAL'

AND LC.TRANSACTION_ID = N_LYR.NEG_TRANSACTION_ID (+)

AND LC.REC_TRXN_ID = N_LYR.NEG_REC_TRXN_ID (+)

GROUP BY LC.COST_ORG_ID,

LC.COST_BOOK_ID,

LC.INVENTORY_ITEM_ID,

LC.VAL_UNIT_ID,

NVL(N_LYR.RESOLVE_REC_TRXN_ID, LC.REC_TRXN_ID),

LC.COST_ELEMENT_ID,

TRUNC(NVL(N_LYR.COST_DATE, LC.COST_DATE)),

LC.CURRENCY_CODE,

LC.UOM_CODE,

CT.COST_METHOD_CODE

UNION ALL

SELECT DISTINCT LC.COST_ORG_ID,

LC.COST_BOOK_ID,

LC.INVENTORY_ITEM_ID,

LC.VAL_UNIT_ID,

-1,

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

)

UNION ALL

SELECT SC.COST_ORG_ID,

SC.COST_BOOK_ID,

SC.INVENTORY_ITEM_ID,

SC.VAL_UNIT_ID,

-1,

SCD.COST_ELEMENT_ID,

SC.EFFECTIVE_START_DATE SNAPSHOT_DATE,

DECODE (SC.EFFECTIVE_END_DATE , to_date('4712-12-31', 'yyyy-mm-dd'), SC.EFFECTIVE_END_DATE, SC.EFFECTIVE_END_DATE + interval

'1' DAY) EFF_TO_DATE,

0 ONHAND_VALUE,

SCD.UNIT_COST,

SC.CURRENCY_CODE,

SC.UOM_CODE,

'STANDARD'

FROM FUSION.CST_STD_COSTS SC,

FUSION.CST_STD_COST_DETAILS SCD,

CST_COST_PROFILES_B CP

WHERE SC.STATUS_CODE = 'PUBLISHED'

AND SC.ASSIGNMENT_LEVEL IN ( 'VALUATION_UNIT','INVENTORY_CONTROL_ATTRIBUTES')

AND SC.VAL_UNIT_ID IS NOT NULL

AND SC.COST_PROFILE_ID = CP.COST_PROFILE_ID

AND CP.COST_METHOD_CODE = 'STANDARD'

AND SC.STD_COST_ID = SCD.STD_COST_ID