MATERIALIZED VIEW: MSC.MSC_ATP_PLAN_SN

Source

SELECT
     plans.plan_id as Plan_id,
     items.sr_inventory_item_id as sr_inventory_item_id,
     items.organization_id as organization_id,
     items.sr_instance_id as sr_instance_id,
     plans.plan_completion_date as completion_date,
     desig.demand_class as demand_class,
     DECODE(plans.plan_type   ----Order is DRP(for 11i10),MRP,MPS,MPP
	                ,1,1
			 	    ,2,2
				    ,3,3
				    ,5,0)
        +
	 DECODE(items.in_source_plan,2,0,10)Rank

      FROM   msc_system_items items,
             msc_trading_partners tp,
             msc_apps_instances ins,
             msc_plans plans,
             msc_designators desig

      WHERE  desig.inventory_atp_flag = 1
      AND    plans.compile_designator = desig.designator
      AND    plans.sr_instance_id = desig.sr_instance_id
      AND plans.organization_id = desig.organization_id
      AND    ins.instance_id = plans.sr_instance_id
      AND    ins.enable_flag = 1
      AND    tp.sr_tp_id = plans.organization_id
      AND    tp.sr_instance_id = plans.sr_instance_id
      AND    tp.partner_type = 3
      AND    plans.plan_completion_date is not null
      AND    plans.data_completion_date is not null
      AND    items.plan_id = plans.plan_id
      AND    plans.plan_type <> 4