VIEW: APPS.MSD_CURR_ONHAND_INVENTORY_V

Source

View Text - Preformatted

SELECT d_itm.instance, d_org.level_id, d_org.sr_level_pk, d_itm.level_id, d_itm.sr_level_pk, 30 geo_level_id, -3 geo_sr_level_pk, 40 dcs_level_id, -6 dcs_sr_level_pk, 9 time_level_id, trunc(sup.new_schedule_date), sup.NEW_ORDER_QUANTITY FROM msc_supplies sup, msc_system_items msi, msd_level_values d_itm, msd_level_values d_org, (select nvl(fnd_profile.value('MSD_TWO_LEVEL_PLANNING'), '2') profile_value from dual) tlp WHERE sup.plan_id = -1 and nvl(sup.new_order_quantity,0) <> 0 and sup.order_type = 18 and sup.organization_id = msi.organization_id and sup.inventory_item_id = msi.inventory_item_id and sup.plan_id = msi.plan_id and sup.sr_instance_id = msi.sr_instance_id and d_itm.sr_level_pk = to_char(msi.sr_inventory_item_id) and d_itm.instance = to_char(msi.sr_instance_id) and d_itm.level_id = 1 and d_org.sr_level_pk = to_char(sup.organization_id) and d_org.instance = to_char(sup.sr_instance_id) and d_org.level_id = 7 and (msi.mrp_planning_code <> 6 or (msi.mrp_planning_code = 6 and msi.pick_components_flag = 'Y')) and decode( nvl(tlp.profile_value, '2'), '1', decode(nvl(msi.ato_forecast_control,3), 3, msd_common_utilities.is_valid_pf_exist(d_itm.instance, d_itm.sr_level_pk), 1), decode( nvl(msi.ato_forecast_control, 3), 3, 2, 1) ) = 1

View Text - HTML Formatted

SELECT D_ITM.INSTANCE
, D_ORG.LEVEL_ID
, D_ORG.SR_LEVEL_PK
, D_ITM.LEVEL_ID
, D_ITM.SR_LEVEL_PK
, 30 GEO_LEVEL_ID
, -3 GEO_SR_LEVEL_PK
, 40 DCS_LEVEL_ID
, -6 DCS_SR_LEVEL_PK
, 9 TIME_LEVEL_ID
, TRUNC(SUP.NEW_SCHEDULE_DATE)
, SUP.NEW_ORDER_QUANTITY
FROM MSC_SUPPLIES SUP
, MSC_SYSTEM_ITEMS MSI
, MSD_LEVEL_VALUES D_ITM
, MSD_LEVEL_VALUES D_ORG
, (SELECT NVL(FND_PROFILE.VALUE('MSD_TWO_LEVEL_PLANNING')
, '2') PROFILE_VALUE
FROM DUAL) TLP
WHERE SUP.PLAN_ID = -1
AND NVL(SUP.NEW_ORDER_QUANTITY
, 0) <> 0
AND SUP.ORDER_TYPE = 18
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND SUP.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND D_ITM.SR_LEVEL_PK = TO_CHAR(MSI.SR_INVENTORY_ITEM_ID)
AND D_ITM.INSTANCE = TO_CHAR(MSI.SR_INSTANCE_ID)
AND D_ITM.LEVEL_ID = 1
AND D_ORG.SR_LEVEL_PK = TO_CHAR(SUP.ORGANIZATION_ID)
AND D_ORG.INSTANCE = TO_CHAR(SUP.SR_INSTANCE_ID)
AND D_ORG.LEVEL_ID = 7
AND (MSI.MRP_PLANNING_CODE <> 6 OR (MSI.MRP_PLANNING_CODE = 6
AND MSI.PICK_COMPONENTS_FLAG = 'Y'))
AND DECODE( NVL(TLP.PROFILE_VALUE
, '2')
, '1'
, DECODE(NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, MSD_COMMON_UTILITIES.IS_VALID_PF_EXIST(D_ITM.INSTANCE
, D_ITM.SR_LEVEL_PK)
, 1)
, DECODE( NVL(MSI.ATO_FORECAST_CONTROL
, 3)
, 3
, 2
, 1) ) = 1