VIEW: APPS.MSD_DEM_AVAIL_SUPPLIER_CAP_V

Source

View Text - Preformatted

SELECT total.plan_id , total.supply_plan , MIN(total.org_code) org_code, total.item_code , total.site_code , AVG(total.quantity) quantity, total.sdate FROM ( SELECT plans.plan_id plan_id, plans.compile_designator supply_plan, mis.sr_instance_id instance_id , mtp.organization_code org_code, msi.item_name item_code, mtp_supplier.partner_name || ':' || mtps_site.tp_site_code site_code, sup.capacity quantity, cal.calendar_date sdate FROM msc_supplier_capacities sup, msc_plans plans, msc_system_items msi, msc_calendar_dates cal, msc_trading_partner_sites mtps_site, msc_trading_partners mtp_supplier, msc_trading_partners mtp, msc_item_suppliers mis WHERE sup.plan_id = plans.plan_id AND mis.plan_id = sup.plan_id AND mis.organization_id = sup.organization_id AND mis.sr_instance_id = sup.sr_instance_id AND mis.supplier_id = sup.supplier_id AND mis.supplier_site_id = sup.supplier_site_id AND mis.inventory_item_id = sup.inventory_item_id AND mtp.sr_instance_id = sup.sr_instance_id AND mtp.sr_tp_id = sup.organization_id AND mtp.partner_type = 3 AND sup.sr_instance_id = cal.sr_instance_id AND cal.calendar_code = msd_dem_common_utilities.get_supplier_calendar(sup.plan_id, sup.sr_instance_id, sup.organization_id, sup.inventory_item_id, sup.supplier_id, sup.supplier_site_id, sup.using_organization_id) AND cal.exception_set_id = -1 AND cal.calendar_date BETWEEN sup.from_date AND nvl(sup.to_date, plans.curr_cutoff_date) AND cal.calendar_date > nvl (trunc(mis.supplier_lead_time_date), trunc(plans.curr_start_date)) AND decode(msd_dem_common_utilities.is_supplier_calendar_present ( sup.plan_id, sup.sr_instance_id, sup.organization_id, sup.inventory_item_id, sup.supplier_id, sup.supplier_site_id, sup.using_organization_id), 1, cal.seq_num, nvl(cal.seq_num, -23453)) = nvl(cal.seq_num, -23453) 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 msi.mrp_planning_code <> 6 AND msi.critical_component_flag = 1 AND mtps_site.partner_site_id = sup.supplier_site_id AND mtp_supplier.partner_id = mtps_site.partner_id AND sup.plan_id <> -1 /* Do we need this join condition */ AND EXISTS (SELECT 1 FROM msc_supplies ms WHERE ms.plan_id = sup.plan_id AND ms.order_type = 5 AND ms.organization_id = sup.organization_id AND ms.inventory_item_id = sup.inventory_item_id AND ms.sr_instance_id = sup.sr_instance_id AND ms.supplier_site_id IS NOT NULL AND ms.supplier_id IS NOT NULL) AND EXISTS (SELECT 1 FROM msc_item_sourcing mis WHERE mis.plan_id = sup.plan_id AND mis.organization_id = sup.organization_id AND mis.sr_instance_id = sup.sr_instance_id AND mis.inventory_item_id = sup.inventory_item_id AND mis.supplier_id = sup.supplier_id AND mis.supplier_site_id = sup.supplier_site_id) ) total GROUP BY total.plan_id, total.supply_plan , total.instance_id , total.item_code , total.site_code , total.sdate

View Text - HTML Formatted

SELECT TOTAL.PLAN_ID
, TOTAL.SUPPLY_PLAN
, MIN(TOTAL.ORG_CODE) ORG_CODE
, TOTAL.ITEM_CODE
, TOTAL.SITE_CODE
, AVG(TOTAL.QUANTITY) QUANTITY
, TOTAL.SDATE
FROM ( SELECT PLANS.PLAN_ID PLAN_ID
, PLANS.COMPILE_DESIGNATOR SUPPLY_PLAN
, MIS.SR_INSTANCE_ID INSTANCE_ID
, MTP.ORGANIZATION_CODE ORG_CODE
, MSI.ITEM_NAME ITEM_CODE
, MTP_SUPPLIER.PARTNER_NAME || ':' || MTPS_SITE.TP_SITE_CODE SITE_CODE
, SUP.CAPACITY QUANTITY
, CAL.CALENDAR_DATE SDATE
FROM MSC_SUPPLIER_CAPACITIES SUP
, MSC_PLANS PLANS
, MSC_SYSTEM_ITEMS MSI
, MSC_CALENDAR_DATES CAL
, MSC_TRADING_PARTNER_SITES MTPS_SITE
, MSC_TRADING_PARTNERS MTP_SUPPLIER
, MSC_TRADING_PARTNERS MTP
, MSC_ITEM_SUPPLIERS MIS
WHERE SUP.PLAN_ID = PLANS.PLAN_ID
AND MIS.PLAN_ID = SUP.PLAN_ID
AND MIS.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND MIS.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MIS.SUPPLIER_ID = SUP.SUPPLIER_ID
AND MIS.SUPPLIER_SITE_ID = SUP.SUPPLIER_SITE_ID
AND MIS.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND MTP.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MTP.SR_TP_ID = SUP.ORGANIZATION_ID
AND MTP.PARTNER_TYPE = 3
AND SUP.SR_INSTANCE_ID = CAL.SR_INSTANCE_ID
AND CAL.CALENDAR_CODE = MSD_DEM_COMMON_UTILITIES.GET_SUPPLIER_CALENDAR(SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.ORGANIZATION_ID
, SUP.INVENTORY_ITEM_ID
, SUP.SUPPLIER_ID
, SUP.SUPPLIER_SITE_ID
, SUP.USING_ORGANIZATION_ID)
AND CAL.EXCEPTION_SET_ID = -1
AND CAL.CALENDAR_DATE BETWEEN SUP.FROM_DATE
AND NVL(SUP.TO_DATE
, PLANS.CURR_CUTOFF_DATE)
AND CAL.CALENDAR_DATE > NVL (TRUNC(MIS.SUPPLIER_LEAD_TIME_DATE)
, TRUNC(PLANS.CURR_START_DATE))
AND DECODE(MSD_DEM_COMMON_UTILITIES.IS_SUPPLIER_CALENDAR_PRESENT ( SUP.PLAN_ID
, SUP.SR_INSTANCE_ID
, SUP.ORGANIZATION_ID
, SUP.INVENTORY_ITEM_ID
, SUP.SUPPLIER_ID
, SUP.SUPPLIER_SITE_ID
, SUP.USING_ORGANIZATION_ID)
, 1
, CAL.SEQ_NUM
, NVL(CAL.SEQ_NUM
, -23453)) = NVL(CAL.SEQ_NUM
, -23453)
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 MSI.MRP_PLANNING_CODE <> 6
AND MSI.CRITICAL_COMPONENT_FLAG = 1
AND MTPS_SITE.PARTNER_SITE_ID = SUP.SUPPLIER_SITE_ID
AND MTP_SUPPLIER.PARTNER_ID = MTPS_SITE.PARTNER_ID
AND SUP.PLAN_ID <> -1 /* DO WE NEED THIS JOIN CONDITION */
AND EXISTS (SELECT 1
FROM MSC_SUPPLIES MS
WHERE MS.PLAN_ID = SUP.PLAN_ID
AND MS.ORDER_TYPE = 5
AND MS.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND MS.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND MS.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MS.SUPPLIER_SITE_ID IS NOT NULL
AND MS.SUPPLIER_ID IS NOT NULL)
AND EXISTS (SELECT 1
FROM MSC_ITEM_SOURCING MIS
WHERE MIS.PLAN_ID = SUP.PLAN_ID
AND MIS.ORGANIZATION_ID = SUP.ORGANIZATION_ID
AND MIS.SR_INSTANCE_ID = SUP.SR_INSTANCE_ID
AND MIS.INVENTORY_ITEM_ID = SUP.INVENTORY_ITEM_ID
AND MIS.SUPPLIER_ID = SUP.SUPPLIER_ID
AND MIS.SUPPLIER_SITE_ID = SUP.SUPPLIER_SITE_ID) ) TOTAL GROUP BY TOTAL.PLAN_ID
, TOTAL.SUPPLY_PLAN
, TOTAL.INSTANCE_ID
, TOTAL.ITEM_CODE
, TOTAL.SITE_CODE
, TOTAL.SDATE