VIEW: APPS.MSD_DEM_REQR_SUPPLIER_CAP_V

Source

View Text - Preformatted

SELECT plans.plan_id , plans.compile_designator supply_plan , mtp1.organization_code org_code , msi.item_name item_code , replace(decode (msd_dem_common_utilities.is_use_new_site_format, 0, mtp.partner_name || ':' || mtps.tp_site_code, TO_CHAR(mtps.partner_site_id)),'''','') site_code, sup.consumed_quantity quantity , sup.consumption_date sdate FROM msc_supplier_requirements sup, msc_plans plans , msc_system_items msi , msc_trading_partners mtp , msc_trading_partner_sites mtps , msc_trading_partners mtp1 WHERE sup.plan_id = plans.plan_id AND sup.organization_id = msi.organization_id AND sup.inventory_item_id = msi.inventory_item_id AND plans.plan_id = msi.plan_id AND sup.sr_instance_id = msi.sr_instance_id AND msi.mrp_planning_code <> 6 AND msi.critical_component_flag = 1 AND mtp1.partner_type = 3 AND mtp1.sr_tp_id = msi.organization_id AND mtp1.sr_instance_id = msi.sr_instance_id AND NVL(mtp1.company_id,-1) = -1 AND mtp.partner_id = sup.supplier_id AND mtp.partner_type = 1 AND mtps.partner_site_id = sup.supplier_site_id AND mtps.partner_type = 1 AND plans.plan_id <> -1 AND plans.daily_material_constraints = 1 AND plans.plan_type NOT IN (101, 102, 103, 105) UNION ALL SELECT plans.plan_id , plans.compile_designator supply_plan , mtpl.organization_code org_code , msi.item_name item_code , replace(decode (msd_dem_common_utilities.is_use_new_site_format, 0, mtp.partner_name || ':' || mtps.tp_site_code, TO_CHAR(mtps.partner_site_id)),'''','') site_code, mr.new_order_quantity quantity , mr.new_dock_date sdate FROM msc_plans plans , msc_supplies mr , msc_trading_partner_sites mtps, msc_trading_partners mtp , msc_trading_partners mtpl , msc_system_items msi WHERE plans.plan_id <> -1 AND (plans.daily_material_constraints = 2 OR plans.plan_type IN (101, 102, 103, 105) ) AND mr.plan_id = plans.plan_id AND mr.order_type IN (5, 1, 2, 51) AND mr.disposition_status_type <> 2 AND DECODE(mtps.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) <= TRUNC(plans.curr_cutoff_date) AND DECODE(mtps.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) >= TRUNC(plans.curr_start_date) AND mtps.partner_site_id = DECODE(mr.order_type,5,mr.source_supplier_site_id, 51,mr.source_supplier_site_id, mr.supplier_site_id) AND mr.organization_id = msi.organization_id AND mr.inventory_item_id = msi.inventory_item_id AND plans.plan_id = msi.plan_id AND mr.sr_instance_id = msi.sr_instance_id AND msi.mrp_planning_code <> 6 AND msi.critical_component_flag = 1 AND mtpl.partner_type = 3 AND mtpl.sr_tp_id = msi.organization_id AND mtpl.sr_instance_id = msi.sr_instance_id AND NVL(mtpl.company_id, -1) = -1 AND mtp.partner_id = DECODE(mr.order_type,5,mr.source_supplier_id, 51, mr.source_supplier_id, mr.supplier_id) AND mtp.partner_type = 1

View Text - HTML Formatted

SELECT PLANS.PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, MTP1.ORGANIZATION_CODE ORG_CODE
, MSI.ITEM_NAME ITEM_CODE
, REPLACE(DECODE (MSD_DEM_COMMON_UTILITIES.IS_USE_NEW_SITE_FORMAT
, 0
, MTP.PARTNER_NAME || ':' || MTPS.TP_SITE_CODE
, TO_CHAR(MTPS.PARTNER_SITE_ID))
, ''''
, '') SITE_CODE
, SUP.CONSUMED_QUANTITY QUANTITY
, SUP.CONSUMPTION_DATE SDATE
FROM MSC_SUPPLIER_REQUIREMENTS SUP
, MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_TRADING_PARTNER_SITES MTPS
, MSC_TRADING_PARTNERS MTP1
WHERE SUP.PLAN_ID = PLANS.PLAN_ID
AND SUP.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND SUP.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PLANS.PLAN_ID = MSI.PLAN_ID
AND SUP.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSI.MRP_PLANNING_CODE <> 6
AND MSI.CRITICAL_COMPONENT_FLAG = 1
AND MTP1.PARTNER_TYPE = 3
AND MTP1.SR_TP_ID = MSI.ORGANIZATION_ID
AND MTP1.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND NVL(MTP1.COMPANY_ID
, -1) = -1
AND MTP.PARTNER_ID = SUP.SUPPLIER_ID
AND MTP.PARTNER_TYPE = 1
AND MTPS.PARTNER_SITE_ID = SUP.SUPPLIER_SITE_ID
AND MTPS.PARTNER_TYPE = 1
AND PLANS.PLAN_ID <> -1
AND PLANS.DAILY_MATERIAL_CONSTRAINTS = 1
AND PLANS.PLAN_TYPE NOT IN (101
, 102
, 103
, 105) UNION ALL SELECT PLANS.PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, MTPL.ORGANIZATION_CODE ORG_CODE
, MSI.ITEM_NAME ITEM_CODE
, REPLACE(DECODE (MSD_DEM_COMMON_UTILITIES.IS_USE_NEW_SITE_FORMAT
, 0
, MTP.PARTNER_NAME || ':' || MTPS.TP_SITE_CODE
, TO_CHAR(MTPS.PARTNER_SITE_ID))
, ''''
, '') SITE_CODE
, MR.NEW_ORDER_QUANTITY QUANTITY
, MR.NEW_DOCK_DATE SDATE
FROM MSC_PLANS PLANS
, MSC_SUPPLIES MR
, MSC_TRADING_PARTNER_SITES MTPS
, MSC_TRADING_PARTNERS MTP
, MSC_TRADING_PARTNERS MTPL
, MSC_SYSTEM_ITEMS MSI
WHERE PLANS.PLAN_ID <> -1
AND (PLANS.DAILY_MATERIAL_CONSTRAINTS = 2 OR PLANS.PLAN_TYPE IN (101
, 102
, 103
, 105) )
AND MR.PLAN_ID = PLANS.PLAN_ID
AND MR.ORDER_TYPE IN (5
, 1
, 2
, 51)
AND MR.DISPOSITION_STATUS_TYPE <> 2
AND DECODE(MTPS.SHIPPING_CONTROL
, 'BUYER'
, MR.NEW_SHIP_DATE
, MR.NEW_DOCK_DATE) <= TRUNC(PLANS.CURR_CUTOFF_DATE)
AND DECODE(MTPS.SHIPPING_CONTROL
, 'BUYER'
, MR.NEW_SHIP_DATE
, MR.NEW_DOCK_DATE) >= TRUNC(PLANS.CURR_START_DATE)
AND MTPS.PARTNER_SITE_ID = DECODE(MR.ORDER_TYPE
, 5
, MR.SOURCE_SUPPLIER_SITE_ID
, 51
, MR.SOURCE_SUPPLIER_SITE_ID
, MR.SUPPLIER_SITE_ID)
AND MR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PLANS.PLAN_ID = MSI.PLAN_ID
AND MR.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MSI.MRP_PLANNING_CODE <> 6
AND MSI.CRITICAL_COMPONENT_FLAG = 1
AND MTPL.PARTNER_TYPE = 3
AND MTPL.SR_TP_ID = MSI.ORGANIZATION_ID
AND MTPL.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND NVL(MTPL.COMPANY_ID
, -1) = -1
AND MTP.PARTNER_ID = DECODE(MR.ORDER_TYPE
, 5
, MR.SOURCE_SUPPLIER_ID
, 51
, MR.SOURCE_SUPPLIER_ID
, MR.SUPPLIER_ID)
AND MTP.PARTNER_TYPE = 1