APPS.MSC_X_CVMI_PLANNING SQL Statements

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

Line: 29

      SELECT
          msi.plan_id
        , msi.inventory_item_id
        , msi.organization_id
        , msi.sr_instance_id
        , mtp.modeled_customer_id
        , mtp.modeled_customer_site_id
        , NVL(msi.forecast_horizon, 0) forecast_horizon
        , msi.vmi_forecast_type
	, msi.uom_code
        , mvt.average_daily_demand
      FROM msc_system_items msi
      , msc_trading_partners mtp
      , msc_vmi_temp mvt
      WHERE msi.inventory_planning_code = 7
      AND msi.organization_id = mtp.sr_tp_id
      AND msi.sr_instance_id = mtp.sr_instance_id
      AND mtp.partner_type = 3 -- org
      AND mtp.modeled_customer_id IS NOT NULL
      AND mtp.modeled_customer_site_id IS NOT NULL
      AND msi.plan_id = -1
	      and mvt.plan_id (+) = msi.plan_id
	      and mvt.inventory_item_id (+) = msi.inventory_item_id
	      and mvt.organization_id (+) = msi.organization_id
	      and mvt.sr_instance_id (+) = msi.sr_instance_id
          and mvt.vmi_type (+) = 2 -- customer facing vmi
      ;

Line: 141

     SELECT distinct SUM(sd.primary_quantity) total_demand,
			 sd.primary_uom
     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_id = p_customer_id
      AND sd.customer_site_id = p_customer_site_id
      AND TRUNC(key_date) BETWEEN TRUNC(p_horizon_start_date)
				AND TRUNC(p_horizon_end_date)
      AND publisher_order_type = l_forecast_type
      AND sd.supplier_id = 1
      AND p_forecast_horizon > 0
      GROUP BY
	   sd.primary_uom
      ;

Line: 276

      UPDATE msc_system_items
        SET -- average_daily_demand = l_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
        ;

Line: 284

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

Line: 288

      UPDATE msc_vmi_temp
        SET average_daily_demand = l_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 vmi_type = 2 -- customer facing vmi
        ;

Line: 296

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

Line: 301

		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 ,
			NULL, -- p_SUPPLIER_ID ,
			NULL, -- p_SUPPLIER_SITE_ID ,
			NULL, -- p_USING_ORGANIZATION_ID ,
			2 ,
			l_AVERAGE_DAILY_DEMAND
		  );

Line: 323

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

Line: 347

      SELECT cr.object_id
      FROM msc_trading_partner_maps map
      , msc_company_relationships cr
      WHERE map.map_type = p_map_type
      AND map.tp_key = p_tp_key
      AND map.company_key = cr.relationship_id
      AND cr.relationship_type = 1 -- customer of, 2 -- supplier of
      ;

Line: 357

      SELECT map.company_key
      FROM msc_trading_partner_maps map
      , msc_trading_partners tp
      WHERE map.map_type = p_map_type
      AND tp.partner_id = map.tp_key
      AND tp.sr_tp_id = p_tp_key
      AND tp.sr_instance_id = p_sr_instance_id
      ;

Line: 367

      SELECT  map.company_key
      FROM msc_trading_partner_maps map
      WHERE map.map_type = p_map_type
      AND map.tp_key = p_tp_key
      ;

Line: 410

      SELECT map.tp_key
      FROM msc_trading_partner_maps map
      , msc_company_relationships cr
      WHERE map.map_type = p_map_type
      AND cr.object_id = p_company_key
      AND map.company_key = cr.relationship_id
      AND cr.relationship_type = 1 -- customer of, 2 -- supplier of
      AND cr.subject_id = OEM_COMPANY_ID
      ;

Line: 421

      SELECT tp.sr_tp_id
      FROM msc_trading_partner_maps map
      , msc_trading_partners tp
      WHERE map.map_type = p_map_type
      AND tp.partner_id = map.tp_key
      AND map.company_key= p_company_key
      ;

Line: 431

      SELECT  map.tp_key
      FROM msc_trading_partner_maps map
      WHERE map.map_type = p_map_type
      AND  map.company_key = p_company_key

      ;