This discussion is archived
1 Reply Latest reply: Nov 1, 2012 4:10 AM by Jim Smith RSS

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

967690 Newbie
Currently Being Moderated
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 ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points