APPS.MSC_X_PLANNING SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 22

     SELECT DISTINCT
          mis.plan_id
        , mis.inventory_item_id
        , mis.organization_id
        , mis.sr_instance_id
        , mis.supplier_id
        , mis.supplier_site_id
        , mis.using_organization_id
     FROM msc_item_suppliers mis
      WHERE mis.plan_id = -1
      AND mis.vmi_flag = 1
      ;

Line: 80

  , p_update_flag IN NUMBER DEFAULT 1
  , p_horizon_start_date IN DATE DEFAULT SYSDATE
  , p_average_daily_demand OUT NOCOPY NUMBER
  )
    IS
    l_total_supply_schedule NUMBER;

Line: 108

     SELECT SUM(sd.primary_quantity) total_demand
     FROM msc_sup_dem_entries sd
      WHERE sd.plan_id = p_plan_id
      AND sd.inventory_item_id = p_inventory_item_id
      AND sd.customer_site_id = p_organization_id
      -- AND sd.sr_instance_id = p_sr_instance_id
      AND sd.supplier_id = p_supplier_id
      AND sd.supplier_site_id = p_supplier_site_id
      AND TRUNC(receipt_date) BETWEEN TRUNC(p_horizon_start_date)
        AND TRUNC(p_horizon_end_date + 1)
      AND publisher_order_type = 2 -- order forecast
      ;

Line: 131

      SELECT  mis.forecast_horizon, mvt.average_daily_demand
	       FROM  msc_item_suppliers mis
	       , msc_vmi_temp mvt
	       WHERE mis.inventory_item_id = p_inventory_item_id
	       AND  mis.plan_id = p_plan_id
	       AND  mis.sr_instance_id = p_sr_instance_id
	       AND  mis.organization_id = p_organization_id
           AND mis. supplier_id = p_supplier_id
           AND mis. supplier_site_id = p_supplier_site_id
           AND mis.using_organization_id = p_using_organization_id
	      and mvt.plan_id (+) = mis.plan_id
	      and mvt.inventory_item_id (+) = mis.inventory_item_id
	      and mvt.organization_id (+) = mis.organization_id
	      and mvt.sr_instance_id (+) = mis.sr_instance_id
	      and mvt.supplier_site_id (+) = mis.supplier_site_id
	      and mvt.supplier_id (+) = mis.supplier_id
	      and NVL (mvt.using_organization_id(+), 1) = NVL(mis.using_organization_id, -1)
          and mvt.vmi_type (+) = 1 -- supplier facing vmi
      ;

Line: 251

  IF (p_update_flag = 1) THEN
      IF (p_average_daily_demand <> l_old_average_daily_demand) THEN
        l_vmi_refresh_flag := 1;

Line: 258

      UPDATE msc_item_suppliers
        SET -- average_daily_demand = p_average_daily_demand
          vmi_refresh_flag = NVL(l_vmi_refresh_flag, vmi_refresh_flag)
        WHERE plan_id = p_plan_id
        AND inventory_item_id = p_inventory_item_id
        AND organization_id = p_organization_id
        AND sr_instance_id = p_sr_instance_id
        AND supplier_id = p_supplier_id
        AND supplier_site_id = p_supplier_site_id
        AND using_organization_id = p_using_organization_id
        ;

Line: 269

print_debug_info( '  vmi refresh flag updated, number of rows updated = '
                                 || SQL%ROWCOUNT
                                 );

Line: 273

      UPDATE msc_vmi_temp
        SET average_daily_demand = p_average_daily_demand
        WHERE plan_id = p_plan_id
        AND inventory_item_id = p_inventory_item_id
        AND organization_id = p_organization_id
        AND sr_instance_id = p_sr_instance_id
        AND supplier_id = p_supplier_id
        AND supplier_site_id = p_supplier_site_id
        AND using_organization_id = p_using_organization_id
        AND vmi_type = 1 -- supplier facing vmi
        ;

Line: 285

print_debug_info( '  average daily demand updated, number of rows updated = '
                                 || SQL%ROWCOUNT
                                 );

Line: 290

		INSERT INTO msc_vmi_temp
		  ( PLAN_ID,
			INVENTORY_ITEM_ID,
			ORGANIZATION_ID ,
			SR_INSTANCE_ID ,
			SUPPLIER_ID ,
			SUPPLIER_SITE_ID ,
			USING_ORGANIZATION_ID ,
			VMI_TYPE ,
			AVERAGE_DAILY_DEMAND
		  ) VALUES
		  ( p_PLAN_ID,
			p_INVENTORY_ITEM_ID,
			p_ORGANIZATION_ID ,
			p_SR_INSTANCE_ID ,
			p_SUPPLIER_ID ,
			p_SUPPLIER_SITE_ID ,
			p_USING_ORGANIZATION_ID ,
			1 ,
			p_AVERAGE_DAILY_DEMAND
		  );

Line: 311

print_debug_info( '  average daily demand inserted, number of rows inserted = '
                                 || SQL%ROWCOUNT
                                 );