VIEW: APPS.MSC_ATP_PLAN_SN#

Source

View Text - Preformatted

SELECT /*AUTOREFRESH*/
     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,111
			 	    ,2,112
				    ,3,113
				    ,5,110,plans.plan_type) -- RP-GOP Integration
        +
	 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    items.new_plan_id is null
      AND    items.simulation_set_id is null
      AND    plans.plan_type NOT IN (4,104)

View Text - HTML Formatted

SELECT /*AUTOREFRESH*/ 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
, 111
, 2
, 112
, 3
, 113
, 5
, 110
, PLANS.PLAN_TYPE) -- RP-GOP INTEGRATION + 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 ITEMS.NEW_PLAN_ID IS NULL
AND ITEMS.SIMULATION_SET_ID IS NULL
AND PLANS.PLAN_TYPE NOT IN (4
, 104)