CST_B_ACCTD_VU_VALUES_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID VAL_UNIT_ID SNAPSHOT_DATE UOM_CODE EFF_TO_DATE COSTED_VALUE ACCOUNTED_VALUE UNACCOUNTED_VALUE EXCLUDED_FROM_ACCTG_VALUE

Query

SQL_Statement

SELECT COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID, SNAPSHOT_DATE, UOM_CODE, 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, SUM(COSTED_VALUE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID ORDER BY SNAPSHOT_DATE) COSTED_VALUE, SUM(ACCOUNTED_VALUE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID ORDER BY SNAPSHOT_DATE) ACCOUNTED_VALUE, SUM(UNACCOUNTED_VALUE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID ORDER BY SNAPSHOT_DATE) UNACCOUNTED_VALUE, SUM(EXCLUDED_FROM_ACCTG_VALUE) OVER (PARTITION BY COST_ORG_ID, COST_BOOK_ID, INVENTORY_ITEM_ID, VAL_UNIT_ID ORDER BY SNAPSHOT_DATE) EXCLUDED_FROM_ACCTG_VALUE FROM (SELECT L1.COST_ORG_ID, L1.COST_BOOK_ID, L1.INVENTORY_ITEM_ID, L1.VAL_UNIT_ID, DECODE(L1.PERIOD_NAME, NULL, TRUNC(L1.COST_DATE), CPS.START_DATE) SNAPSHOT_DATE, SUM(L1.UNIT_COST* L1.QUANTITY) COSTED_VALUE, L1.UOM_CODE, SUM(DECODE(NVL(CCD.ACCOUNTED_FLAG, 'N'), 'F', L1.UNIT_COST* L1.QUANTITY, 0)) ACCOUNTED_VALUE, SUM(DECODE(NVL(CCD.ACCOUNTED_FLAG, 'N'), 'F', 0, 'X', 0, L1.UNIT_COST * L1.QUANTITY)) UNACCOUNTED_VALUE, SUM(DECODE(NVL(CCD.ACCOUNTED_FLAG, 'N'), 'X', L1.UNIT_COST* L1.QUANTITY, 0)) EXCLUDED_FROM_ACCTG_VALUE FROM CST_LAYER_COSTS L1, CST_COST_DISTRIBUTIONS CCD, CST_PERIOD_STATUSES CPS WHERE NVL(L1.ABSORPTION_TYPE, 1) = 1 AND L1.DISTRIBUTION_ID = CCD.DISTRIBUTION_ID (+) AND L1.COST_ORG_ID = CPS.COST_ORG_ID(+) AND L1.COST_BOOK_ID = CPS.COST_BOOK_ID(+) AND L1.PERIOD_NAME = CPS.PERIOD_NAME(+) GROUP BY L1.COST_ORG_ID, L1.COST_BOOK_ID, L1.INVENTORY_ITEM_ID, L1.VAL_UNIT_ID, L1.UOM_CODE, DECODE(L1.PERIOD_NAME, NULL, TRUNC(L1.COST_DATE), CPS.START_DATE) )