CST_B_COSTED_ATTR_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 CTLQUANTITY VU_QUANTITY INVENTORY_ORG_ID SUBINVENTORY_CODE LOCATOR_ID PROJECT_ID TASK_ID COUNTRY_OF_ORIGIN_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,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, 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, SUM(CTLQUANTITY) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_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) CTLQUANTITY, SUM(QUANTITY) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID ORDER BY SNAPSHOT_DATE) VU_QUANTITY, INVENTORY_ORG_ID, SUBINVENTORY_CODE, LOCATOR_ID, PROJECT_ID, TASK_ID, COUNTRY_OF_ORIGIN_CODE FROM ( SELECT CT.COST_ORG_ID, CT.COST_BOOK_ID, CT.VAL_UNIT_ID, CT.INVENTORY_ITEM_ID, CT.INVENTORY_ORG_ID, CIT.SUBINVENTORY_CODE, CIT.LOCATOR_ID, CIT.PROJECT_ID, CIT.TASK_ID, CIT.COUNTRY_OF_ORIGIN_CODE, DECODE(CT.PERIOD_NAME, NULL, TRUNC(NVL(CTL.COST_DATE, NVL(CT.COST_DATE, CT.TRANSACTION_DATE))), CPS.START_DATE) SNAPSHOT_DATE, CT.UOM_CODE, SUM(DECODE(CCPB.IGNORE_INTRA_VU_XFER_FLAG, 'Y',DECODE(CTL.TRANSACTION_ID,null,0,CTL.QUANTITY),CTL.QUANTITY) ) CTLQUANTITY, SUM(NVL(CTL.QUANTITY, CT.QUANTITY)) QUANTITY FROM CST_TRANSACTION_LAYERS CTL, CST_TRANSACTIONS CT, CST_INV_TRANSACTIONS CIT, CST_COST_PROFILES_B CCPB, CST_PERIOD_STATUSES CPS WHERE NVL(CTL.POSTED_FLAG, 'Y') NOT IN ('N','X','W') AND CCPB.COST_PROFILE_ID = CT.COST_PROFILE_ID AND CTL.TRANSACTION_ID (+) = CT.TRANSACTION_ID AND CT.CST_INV_TRANSACTION_ID = CIT.CST_INV_TRANSACTION_ID AND DECODE(CTL.COST_ORG_ID, NULL, CT.POSTED_FLAG, 'X') = 'X' AND CT.INTRANSIT_FLAG = 'N' AND CT.COST_ORG_ID = CPS.COST_ORG_ID(+) AND CT.COST_BOOK_ID = CPS.COST_BOOK_ID(+) AND CT.PERIOD_NAME = CPS.PERIOD_NAME(+) GROUP BY CT.COST_ORG_ID, CT.COST_BOOK_ID, CT.VAL_UNIT_ID, CT.INVENTORY_ITEM_ID, CT.INVENTORY_ORG_ID, CIT.SUBINVENTORY_CODE, CIT.LOCATOR_ID, CIT.PROJECT_ID, CIT.TASK_ID, CIT.COUNTRY_OF_ORIGIN_CODE, DECODE(CT.PERIOD_NAME, NULL, TRUNC(NVL(CTL.COST_DATE, NVL(CT.COST_DATE, CT.TRANSACTION_DATE))), CPS.START_DATE), CT.UOM_CODE )