1 Reply Latest reply: Nov 1, 2012 6:10 AM by Jim Smith RSS

    PLS-00364: loop index variable 'S1' use is invalid

    967690
      For l in loc Loop

      v_line_count := 0 ;
      v_rec_count := 0 ;

      --Changed sysdate to trunc(sysdate) in WHERE conditions as per <exp2://Ticket/11674787>
      Select count(1)
      Into v_rec_count
      from sales_data s,
      mdp_matrix mdp, -- added by Rana for order approval
      items i,
      t_ep_item tei,
      location l,
      t_ep_site tes,
      t_ep_e1_cust_cat_2 tel,
      t_ep_l_att_7 comp,
      t_ep_l_att_5 pur_grp,
      t_ep_lr4 tl
      where s.sales_date in ( APL_CURRENT_DATE, APL_CURRENT_DATE-1 )
      and mdp.item_id = s.item_id -- Added by Rana
      and mdp.location_id = s.location_id -- Added by Rana
      --and    tel.e1_cust_cat_2            = p_supply_code
      and tes.site = l.site
      -- and tei.item IN ('H1355L/B','MC516LL/A')
      and s.item_id = i.item_id
      and i.t_ep_item_ep_id = tei.t_ep_item_ep_id
      and s.location_id = l.location_id
      AND l.t_ep_site_ep_id = tes.t_ep_site_ep_id
      AND TES.t_ep_e1_cust_cat_2_EP_ID = TEL.t_ep_e1_cust_cat_2_EP_ID
      AND nvl(s.str_repl_status,0)=0
      -- AND (mdp.rfl_order_appr = 1 OR ( mdp.rfl_order_appr = 0 AND s.str_repl_approved = 1)) -- Added by Rana
      and round(decode(s.sales_date, apl_current_date, nvl(s.str_expedite_order,0), ( decode(--mdp.rfl_order_appr --ashish
      1,1,nvl(s.str_repl_r,nvl(s.str_repl,0)),0) +
      decode(--mdp.rfl_order_appr --ashish
      0,0 ,decode(--s.str_repl_approved --ashish
      1,1,nvl(s.str_repl_r,nvl(s.str_repl,0)),0),0))),0) > 0
      -- and (nvl(s.str_expedite_order,0)+ nvl(s.str_repl_r,nvl(s.str_repl,0)))>0
      /* Commented by Rana on 23-12-2010
      and s.str_planned_shpmnt_appr is not null
      and s.str_planned_shpmnt_appr > 0 */
      and l.t_ep_l_att_7_ep_id = comp.t_ep_l_att_7_ep_id
      and l.t_ep_l_att_5_ep_id = pur_grp.t_ep_l_att_5_ep_id
      AND L.T_EP_LR4_EP_ID = TL.T_EP_LR4_EP_ID
      -- AND mdp.t_ep_repl_type_ep_id = p_repl_type_id -- Returns the value of ZD
      /* ( select t_ep_repl_type_ep_id
      from t_ep_repl_type
      where repl_type_code = 'ZD' )-- 81 */
      and nvl(s.str_order_type,0) = 0 -- For regular order only
      and tel.e1_cust_cat_2 = p_hub_code -- Parameter for hub Code
      and mdp.suppl_str = l.suppl_str
      -- and nvl(s.file_status,'N') = 'N'
      -- and nvl(s.bapi_file_status,'N') = 'N'
      And NOT EXISTS ( select 1
      from sales_data sd1
      where sd1.item_id = s.item_id
      and sd1.location_id = s.location_id
      and sd1.rfl_store_close is not null
      and sd1.sales_date = ( nvl((SELECT DELV_DATE FROM BIIO_DEL_TIME_MATRIX
      where ship_date = apl_current_date +1
      AND LEVEL1 = TES.SITE
      ),trunc(sysdate)+mdp.lead_time+1))
      )
      -- group by 'NB', 'P9999', tel.e1_cust_cat_2, mdp.suppl_str, s.repl_approved, '000', Null, Null, Null, tei.item, tes.site, '001', comp.l_att_7, pur_grp.l_att_5, decode(tl.lr4,'US','USD','CAD'), s.item_id, s.location_id

      ;

      If v_rec_count > 0 Then

      For s1 in cur_po(
      l.site,
      l.suppl_str
      ) Loop



      If v_line_count = 0 Then

      select apl_strhdr_seq.nextval into v_strhdr_seq from dual;


      If s1.suppl_str1 = 1 Then -- <***Error***>
      If s1.demanded_plant is not null Then

      Select plant
      Into v_demand_plant
      From BIIO_DEMANDED_PLAN
      where plant_id = s1.demanded_plant
      ;
      ElsIf s1.demanded_plant is null Then
      v_demand_plant := null ;
      End If;
      Elsif s1.suppl_str = 2 Then
      If s1.demanded_plant is not null Then

      Select plant
      Into v_demand_plant
      From BIIO_DEMANDED_PLAN
      where plant_id = s1.demanded_plant
      ;
      ElsIf s1.demanded_plant is null Then
      v_demand_plant := s1.supplying_site ;
      End If;
      End If;
      .
      .
      .


      The cur_po is defined as :

      Cursor cur_po(p_store_code Varchar2,
      p_suppl_str Number ) is
      Select
      'NB' order_type,
      'P9999' vendor,
      tel.e1_cust_cat_2 supplying_site,
      mdp.suppl_str suppl_str1 ,
      --s.repl_approved  demanded_plant,                              
      -- nvl(to_char(repl_approved),tel.e1_cust_cat_2) demanded_plant,
      '000' purch_grp,
      Null stg_loc,
      Null shipping_cond,
      Null demand_carrier,
      tei.item article,
      sum(
      round(Decode(s.sales_date, apl_current_date, nvl(s.str_expedite_order,0), ( decode(--mdp.rfl_order_appr
      1,1,nvl(s.str_repl_r,nvl(s.str_repl,0)),0) +
      decode(--mdp.rfl_order_appr
      0,0 ,decode(--s.str_repl_approved
      1,1,nvl(s.str_repl_r,nvl(s.str_repl,0)),0),0))
      ),0)
      ) po_qty,
      --nvl(s.str_repl_r,s.str_repl) po_qty ,
      --s.str_planned_shpmnt_appr    po_qty,

      MAX(NVL((SELECT DELV_DATE
      FROM BIIO_DEL_TIME_MATRIX
      WHERE SHIP_DATE = APL_CURRENT_DATE +1
      and level1 = tes.site),sysdate+mdp.lead_time+1)
      )
      --s.dlt_delivery_date
      delivery_date,
      tes.site site,
      '001' storage_location,
      comp.l_att_7 comp_code,
      pur_grp.l_att_5 pur_grp_code,
      decode(tl.lr4,'US','USD','CAD') curr,
      tl.lr4 country,
      max(s.sales_date) sales_date ,
      s.item_id,
      s.location_id,
      max(( select route
      from biio_route_carrier
      where carrier_id = s.rfl_car
      )) carrier
      from sales_data s,
      apps.mdp_matrix mdp,
      items i,
      t_ep_item tei,
      location l,
      t_ep_site tes,
      t_ep_e1_cust_cat_2 tel,
      t_ep_l_att_7 comp,
      t_ep_l_att_5 pur_grp,
      t_ep_lr4 tl
      where s.sales_date in ( APL_CURRENT_DATE, APL_CURRENT_DATE-1 )
      and mdp.item_id = s.item_id
      and mdp.location_id = s.location_id
      --and    tel.e1_cust_cat_2            = p_supply_code
      and tes.site = p_store_code
      -- and tei.item IN ('H1355L/B','MC516LL/A')
      and s.item_id = i.item_id
      and i.t_ep_item_ep_id = tei.t_ep_item_ep_id
      and s.location_id = l.location_id
      AND l.t_ep_site_ep_id = tes.t_ep_site_ep_id
      AND TES.t_ep_e1_cust_cat_2_EP_ID = TEL.t_ep_e1_cust_cat_2_EP_ID
      AND nvl(s.str_repl_status,0)=0
      -- AND (mdp.rfl_order_appr = 1 OR ( mdp.rfl_order_appr = 0 AND s.str_repl_approved = 1))
      and round(decode(s.sales_date, apl_current_date, nvl(s.str_expedite_order,0), ( decode(--mdp.rfl_order_appr
      1,1,nvl(s.str_repl_r,nvl(s.str_repl,0)),0) +
      decode(--mdp.rfl_order_appr
      0,0 ,decode(--s.str_repl_approved
      1,1,nvl(s.str_repl_r,nvl(s.str_repl,0)),0),0))),0) > 0
      -- and (nvl(s.str_expedite_order,0)+ nvl(s.str_repl_r,nvl(s.str_repl,0)))>0
      /* Commented by Rana on 23-12-2010
      and s.str_planned_shpmnt_appr is not null
      and s.str_planned_shpmnt_appr > 0 */
      and l.t_ep_l_att_7_ep_id = comp.t_ep_l_att_7_ep_id
      and l.t_ep_l_att_5_ep_id = pur_grp.t_ep_l_att_5_ep_id
      AND L.T_EP_LR4_EP_ID = TL.T_EP_LR4_EP_ID
      -- AND mdp.t_ep_repl_type_ep_id = p_repl_type_id -- Returns the value of ZD
      /* ( select t_ep_repl_type_ep_id
      from t_ep_repl_type
      where repl_type_code = 'ZD' )-- 81 */
      and nvl(s.str_order_type,0) = 0 -- For regular order only
      and tel.e1_cust_cat_2 = p_hub_code -- Parameter for hub Code
      AND MDP.SUPPL_STR = P_SUPPL_STR
      -- AND s.rfl_store_close is null
      -- and nvl(s.file_status,'N') = 'N'
      -- and nvl(s.bapi_file_status,'N') = 'N'
      And NOT EXISTS ( select 1
      from sales_data sd1
      where sd1.item_id = s.item_id
      and sd1.location_id = s.location_id
      and sd1.rfl_store_close is not null
      and sd1.sales_date = ( nvl((SELECT DELV_DATE FROM BIIO_DEL_TIME_MATRIX
      where ship_date = apl_current_date +1
      AND LEVEL1 = TES.SITE
      ),trunc(sysdate)+mdp.lead_time+1))
      )
      group by 'NB', 'P9999', tel.e1_cust_cat_2, mdp.suppl_str, '000', Null, Null, Null, tei.item, tes.site, '001', comp.l_att_7, pur_grp.l_att_5, decode(tl.lr4,'US','USD','CAD'), tl.lr4, s.item_id, s.location_id
      ;


      Line marked as <***Error***> is throwing the below error

      Error(450,14): PLS-00364: loop index variable 'S1' use is invalid

      Can anyone please guide on this ...