CST_B_COSTED_VU_ONHAND_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
COST_ORG_ID COST_BOOK_ID VAL_UNIT_ID INVENTORY_ITEM_ID SNAPSHOT_DATE EFF_TO_DATE QUANTITY UOM_CODE

Query

SQL_Statement

SELECT COST_ORG_ID, COST_BOOK_ID, VAL_UNIT_ID, INVENTORY_ITEM_ID, SNAPSHOT_DATE, NVL(LEAD (SNAPSHOT_DATE, 1) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID ORDER BY SNAPSHOT_DATE), to_date('4712-12-31', 'yyyy-mm-dd'))EFF_TO_DATE, CASE WHEN PERIOD_NAME IS NULL THEN QUANTITY ELSE VU_QUANTITY END QUANTITY, UOM_CODE FROM ( SELECT DISTINCT CTL.COST_ORG_ID, CTL.COST_BOOK_ID, CTL.VAL_UNIT_ID, CTL.INVENTORY_ITEM_ID, DECODE(CTL.PERIOD_NAME, NULL, TRUNC(COST_DATE), CPS.START_DATE) SNAPSHOT_DATE, LAST_VALUE(QUANTITY_ONHAND) OVER (PARTITION BY CTL.COST_ORG_ID, CTL.COST_BOOK_ID, CTL.INVENTORY_ITEM_ID, CTL.VAL_UNIT_ID, TRUNC(COST_DATE) ORDER BY COST_DATE, DEP_TRXN_ID, TRANSACTION_ID, REC_TRXN_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) QUANTITY, CTL.UOM_CODE, SUM(DECODE(CTL.COST_TRANSACTION_TYPE,'ADJUST',0,CTL.QUANTITY)) OVER (PARTITION BY CTL.COST_ORG_ID, CTL.COST_BOOK_ID, CTL.INVENTORY_ITEM_ID, CTL.VAL_UNIT_ID ORDER BY DECODE(CTL.PERIOD_NAME, NULL, TRUNC(COST_DATE), CPS.START_DATE) ) VU_QUANTITY, CTL.PERIOD_NAME FROM CST_TRANSACTION_LAYERS CTL, CST_PERIOD_STATUSES CPS WHERE CTL.POSTED_FLAG NOT IN ('N','X','W') AND CTL.COST_ORG_ID = CPS.COST_ORG_ID(+) AND CTL.COST_BOOK_ID = CPS.COST_BOOK_ID(+) AND CTL.PERIOD_NAME = CPS.PERIOD_NAME(+) )