CST_B_COSTED_DEL_ATTR_ONHAND_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Query

SQL_Statement

SELECT COST_ORG_ID, COST_BOOK_ID, VAL_UNIT_ID, INVENTORY_ITEM_ID, REC_TRXN_ID, SNAPSHOT_DATE, NVL(LEAD (SNAPSHOT_DATE, 1) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, REC_TRXN_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID,INVENTORY_ORG_ID, SUBINVENTORY_CODE, LOCATOR_ID, PROJECT_ID, TASK_ID, COUNTRY_OF_ORIGIN_CODE ORDER BY SNAPSHOT_DATE), to_date('4712-12-31', 'yyyy-mm-dd')) EFF_TO_DATE, SUM(QUANTITY) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, REC_TRXN_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID,INVENTORY_ORG_ID, SUBINVENTORY_CODE, LOCATOR_ID, PROJECT_ID, TASK_ID, COUNTRY_OF_ORIGIN_CODE ORDER BY SNAPSHOT_DATE) QUANTITY, INVENTORY_ORG_ID, SUBINVENTORY_CODE, LOCATOR_ID, PROJECT_ID, TASK_ID, COUNTRY_OF_ORIGIN_CODE FROM (SELECT CTL.COST_ORG_ID, CTL.COST_BOOK_ID, CTL.VAL_UNIT_ID, CTL.INVENTORY_ITEM_ID, (CASE WHEN CTL.REC_TRXN_ID=0 THEN CTL.TRANSACTION_ID ELSE CTL.REC_TRXN_ID END) REC_TRXN_ID, CT.INVENTORY_ORG_ID, CIT.SUBINVENTORY_CODE, CIT.LOCATOR_ID, CIT.PROJECT_ID, CIT.TASK_ID, CIT.COUNTRY_OF_ORIGIN_CODE, TRUNC(CTL.COST_DATE) SNAPSHOT_DATE, CTL.UOM_CODE, SUM(CTL.QUANTITY) QUANTITY FROM CST_TRANSACTION_LAYERS CTL, CST_TRANSACTIONS CT, CST_INV_TRANSACTIONS CIT WHERE CTL.POSTED_FLAG NOT IN ('N','W') AND CASE WHEN CTL.REC_TRXN_ID=0 THEN CTL.TRANSACTION_ID ELSE CTL.REC_TRXN_ID END = CT.TRANSACTION_ID AND CT.CST_INV_TRANSACTION_ID = CIT.CST_INV_TRANSACTION_ID GROUP BY CTL.COST_ORG_ID, CTL.COST_BOOK_ID, CTL.VAL_UNIT_ID, CTL.INVENTORY_ITEM_ID, (CASE WHEN CTL.REC_TRXN_ID=0 THEN CTL.TRANSACTION_ID ELSE CTL.REC_TRXN_ID END), CT.INVENTORY_ORG_ID, CIT.SUBINVENTORY_CODE, CIT.LOCATOR_ID, CIT.PROJECT_ID, CIT.TASK_ID, CIT.COUNTRY_OF_ORIGIN_CODE, TRUNC(CTL.COST_DATE), CTL.UOM_CODE )