VIEW: APPS.CSP_SUBINV_USAGE_DETAILS_V

Source

View Text - Preformatted

SELECT nvl(csi.inventory_item_id,cuh.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID, cuh.ORGANIZATION_ID, cuh.SUBINVENTORY_CODE, sysdate - ROUND((TO_NUMBER(sysdate - cuh.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size PERIOD_START_DATE, sum(cuh.QUANTITY) QUANTITY From CSP_PLANNING_PARAMETERS cpp, CSP_USAGE_HISTORIES cuh, CSP_SUPERSEDE_ITEMS csi, CSP_FORECAST_RULES_B cfrb, csp_usg_transaction_types cutt Where cuh.organization_id = cpp.organization_id And cuh.subinventory_code = cpp.secondary_inventory And cpp.node_type = 'SUBINVENTORY' And cuh.HISTORY_DATA_TYPE = 1 And cuh.PERIOD_START_DATE BETWEEN trunc(sysdate) - cfrb.period_size * cfrb.history_periods AND trunc(SYSDATE) And csi.inventory_item_id(+) = cuh.inventory_item_id And csi.organization_id (+) = cuh.organization_id And csi.sub_inventory_code(+) = cuh.subinventory_code AND cfrb.FORECAST_RULE_ID = cpp.FORECAST_RULE_ID and cutt.forecast_rule_id = cfrb.forecast_rule_id and cuh.transaction_type_id = cutt.transaction_type_id Group By nvl(csi.inventory_item_id,cuh.INVENTORY_ITEM_ID) , cuh.ORGANIZATION_ID, cuh.SUBINVENTORY_CODE, sysdate - ROUND((TO_NUMBER(sysdate - cuh.period_start_date) /cfrb.period_size+0.5)) * cfrb.period_size

View Text - HTML Formatted

SELECT NVL(CSI.INVENTORY_ITEM_ID
, CUH.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID
, CUH.ORGANIZATION_ID
, CUH.SUBINVENTORY_CODE
, SYSDATE - ROUND((TO_NUMBER(SYSDATE - CUH.PERIOD_START_DATE)/CFRB.PERIOD_SIZE+0.5)) * CFRB.PERIOD_SIZE PERIOD_START_DATE
, SUM(CUH.QUANTITY) QUANTITY
FROM CSP_PLANNING_PARAMETERS CPP
, CSP_USAGE_HISTORIES CUH
, CSP_SUPERSEDE_ITEMS CSI
, CSP_FORECAST_RULES_B CFRB
, CSP_USG_TRANSACTION_TYPES CUTT
WHERE CUH.ORGANIZATION_ID = CPP.ORGANIZATION_ID
AND CUH.SUBINVENTORY_CODE = CPP.SECONDARY_INVENTORY
AND CPP.NODE_TYPE = 'SUBINVENTORY'
AND CUH.HISTORY_DATA_TYPE = 1
AND CUH.PERIOD_START_DATE BETWEEN TRUNC(SYSDATE) - CFRB.PERIOD_SIZE * CFRB.HISTORY_PERIODS
AND TRUNC(SYSDATE)
AND CSI.INVENTORY_ITEM_ID(+) = CUH.INVENTORY_ITEM_ID
AND CSI.ORGANIZATION_ID (+) = CUH.ORGANIZATION_ID
AND CSI.SUB_INVENTORY_CODE(+) = CUH.SUBINVENTORY_CODE
AND CFRB.FORECAST_RULE_ID = CPP.FORECAST_RULE_ID
AND CUTT.FORECAST_RULE_ID = CFRB.FORECAST_RULE_ID
AND CUH.TRANSACTION_TYPE_ID = CUTT.TRANSACTION_TYPE_ID GROUP BY NVL(CSI.INVENTORY_ITEM_ID
, CUH.INVENTORY_ITEM_ID)
, CUH.ORGANIZATION_ID
, CUH.SUBINVENTORY_CODE
, SYSDATE - ROUND((TO_NUMBER(SYSDATE - CUH.PERIOD_START_DATE) /CFRB.PERIOD_SIZE+0.5)) * CFRB.PERIOD_SIZE