VIEW: APPS.MSD_SR_ONHAND_INVENTORY_V

Source

View Text - Preformatted

SELECT itm.organization_id, null, itm.inventory_item_id, null, msd_sr_util.get_all_geo_pk, null, msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null, msd_sr_util.get_all_dcs_pk, null, cal.report_date, (msd_sr_util.uom_conv(inv.uom_code, itm.inventory_item_id) * sum(inv.onhand_qty)) quantity, msd_sr_util.get_null_pk, null, msd_sr_util.get_null_pk, null FROM mtl_system_items itm, msd_setup_parameters para2, msd_setup_parameters morg, msd_app_instance_orgs app, opi_inv_val_sum_mv inv, fii_time_rpt_struct_v cal WHERE morg.parameter_name = 'MSD_MASTER_ORG' and para2.parameter_name = 'MSD_TWO_LEVEL_PLANNING' and decode(nvl(itm.ato_forecast_control,3), 3, decode(nvl(para2.parameter_value, '2'), '1', msd_sr_util.is_product_family_forecastable(morg.parameter_value, itm.inventory_item_id, 2), 2), 1) = 1 and substr(inv.item_org_id,1,instr(inv.item_org_id,'-')-1) = itm.inventory_item_id and inv.organization_id = itm.organization_id and inv.aggregation_level_flag = 0 and app.organization_id = inv.organization_id and (itm.mrp_planning_code <> 6 or (itm.mrp_planning_code = 6 and itm.pick_components_flag = 'Y')) and inv.time_id = cal.time_id and bitand(cal.record_type_id, 1143) = cal.record_type_id group by itm.organization_id, itm.inventory_item_id, inv.uom_code, cal.report_date

View Text - HTML Formatted

SELECT ITM.ORGANIZATION_ID
, NULL
, ITM.INVENTORY_ITEM_ID
, NULL
, MSD_SR_UTIL.GET_ALL_GEO_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_ALL_DCS_PK
, NULL
, CAL.REPORT_DATE
, (MSD_SR_UTIL.UOM_CONV(INV.UOM_CODE
, ITM.INVENTORY_ITEM_ID) * SUM(INV.ONHAND_QTY)) QUANTITY
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
FROM MTL_SYSTEM_ITEMS ITM
, MSD_SETUP_PARAMETERS PARA2
, MSD_SETUP_PARAMETERS MORG
, MSD_APP_INSTANCE_ORGS APP
, OPI_INV_VAL_SUM_MV INV
, FII_TIME_RPT_STRUCT_V CAL
WHERE MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND PARA2.PARAMETER_NAME = 'MSD_TWO_LEVEL_PLANNING'
AND DECODE(NVL(ITM.ATO_FORECAST_CONTROL
, 3)
, 3
, DECODE(NVL(PARA2.PARAMETER_VALUE
, '2')
, '1'
, MSD_SR_UTIL.IS_PRODUCT_FAMILY_FORECASTABLE(MORG.PARAMETER_VALUE
, ITM.INVENTORY_ITEM_ID
, 2)
, 2)
, 1) = 1
AND SUBSTR(INV.ITEM_ORG_ID
, 1
, INSTR(INV.ITEM_ORG_ID
, '-')-1) = ITM.INVENTORY_ITEM_ID
AND INV.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND INV.AGGREGATION_LEVEL_FLAG = 0
AND APP.ORGANIZATION_ID = INV.ORGANIZATION_ID
AND (ITM.MRP_PLANNING_CODE <> 6 OR (ITM.MRP_PLANNING_CODE = 6
AND ITM.PICK_COMPONENTS_FLAG = 'Y'))
AND INV.TIME_ID = CAL.TIME_ID
AND BITAND(CAL.RECORD_TYPE_ID
, 1143) = CAL.RECORD_TYPE_ID GROUP BY ITM.ORGANIZATION_ID
, ITM.INVENTORY_ITEM_ID
, INV.UOM_CODE
, CAL.REPORT_DATE