PROCEDURE otb_setup_mat_sscatg(p_comp_code VARCHAR2,p_main_code varchar2,p_cat_code varchar2) -- u
is
cursor disp_period_cur
is
select substr(period_finyr,1,2) period_ctrlno,substr(period_finyr,3,4) finyr
from (select distinct decode(length(period_ctrlno),1,0||period_ctrlno,period_ctrlno)||finctrl_no period_finyr
from budg_finperiod_hdr
where comp_code=p_comp_code
and main_code=p_main_code
and from_date>add_months(last_day(trunc(sysdate,'MM')-5),-1)
order by substr(decode(length(period_ctrlno),1,0||period_ctrlno,period_ctrlno)||finctrl_no,3,4) ,
substr(decode(length(period_ctrlno),1,0||period_ctrlno,period_ctrlno)||finctrl_no,1,2)asc)
where rownum<=6
and rownum = 1;
p_fn_yr varchar2(5):='X';
v_fnyr_code varchar2(5):='X';
v_pst_dt date;
v_ped_dt date;
v_fnyr_stdt date;
v_cur_period number;
v_ob_stkval1 number;
v_ob_stkval2 number;
v_marg_perc number;
V_TAX_PERC number; -- naren
v_onorder_sp number;
v_val number;
v_close_stkwillbe number;
v_stktur number;
v_close_stkshdbe number;
v_costval number;
v_otbval_sp number;
v_otbval_cp number;
v_salval number;
v_period number;
v_fnyr varchar2(5);
v_clsd_willbe number;
v_val2 number;
v_val3 number;
v_est_sale number;
v_curfinyr varchar2(6);
v_curperiod number;
v_pend_po_cp number;
v_mkt_pending_cp number;
v_onorder_po_sp number;
v_onorder_mkt_sp number;
v_pend_poqty number;
v_mkt_pending_qty number;
v_mat_name varchar2(200) ;
v_prod_status varchar2(100);
v_prod_status_desc varchar2(100);
v_crtd_dt date ;
v_cntnum number;
v_sup_code varchar2(20);
v_sup_name varchar2(100);
v_f_on_b_order number;
v_min_order_qty number;
v_lst1mnth_sale number;
v_lst3mnth_saleval number;
v_lst3mnth_sale number;
v_lst1mnth_saleval number;
v_lst6mnth_sale number;
v_lst6mnth_saleval number;
v_last_recd date;
v_last_rcvd_qty number;
v_first_rcvd_dt date ;
v_first_rcvd_qty number;
v_mnth1 number := 0;
v_mnth2 number := 0;
v_mnth3 number := 0;
v_mnth4 number := 0;
v_mnth5 number := 0;
v_mnth6 number := 0;
v_mnth1_val number := 0;
v_mnth2_val number := 0;
v_mnth3_val number := 0;
v_mnth4_val number := 0;
v_mnth5_val number := 0;
v_mnth6_val number := 0;
v_sgststk_qty number := 0 ;
v_ema_qty number;
v_finper number;
v_ppst_dt date;
v_pped_dt date;
v_prdno number:= 0;
v_new_margin number;
v_rsp number;
begin
delete from merch_otb_mat_sscateg
where cat_code = p_cat_code;
commit;
for i in disp_period_cur
loop
if i.finyr<>p_fn_yr then
---get the fin year start date
BEGIN
select from_date,fn_year into v_fnyr_stdt,v_fnyr_code
from budg_finyr_mst
where trunc(sysdate) between from_date and to_date ;
exception
when no_data_found then
dbms_output.put_line('Fin Year Start Date Is Not Exists');
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating Fin Year Start Date');
when others then
dbms_output.put_line(sqlerrm||3);
end;
end if;
p_fn_yr:=i.finyr;
----get the period start date and end date
begin
select distinct from_date,to_date,finctrl_no||period_ctrlno,periodnum
into v_pst_dt,v_ped_dt,v_finper, v_prdno
from budg_finperiod_hdr_vu
where comp_code=p_comp_code
and main_code=p_main_code
and finctrl_no=p_fn_yr
and period_ctrlno=i.period_ctrlno;
exception
when no_data_found then
null;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating From And To Date');
when others then
dbms_output.put_line(sqlerrm||2);
end;
---ADD NAREN
insert into merch_otb_mat_saleval(comp_code,
main_code ,
mat_type,
matcode,
cat_code,
SGRPCODE,
SUB_SGRPCODE,
finyr_code,
period,
stock ,
salval,
costval,
LASTYR_SP,
stk_qty,
intrnsit_qty,
stk_sp,
intrnsit_sp
)
(select comp_code,main_code,mat_type,matcode,cat_Code,SGRPCODE,SUB_SGRPCODE,p_fn_yr,i.period_ctrlno
, qty,sp,cp,lastyr_sp --,est_sp
,stk_qty,intrnsit_qty,stk_qty_sp,intrnsit_qty_sp
from
(select comp_code,main_code,mat_type,matcode,cat_Code,SGRPCODE,SUB_SGRPCODE,round(sum(qty)) qty
,round(sum(stk_qty)) stk_qty,round(sum(intrnsit_qty)) intrnsit_qty,round(sum(stk_qty_sp)) stk_qty_sp
,round(sum(intrnsit_qty_sp)) intrnsit_qty_sp,round(sum(sp)) sp,round(sum(cp)) cp,round(sum(lastyr_sp)) lastyr_sp --,round(sum(est_sp)) est_sp
from
(select comp_code,main_code,mat_type,matcode,cat_Code,SGRPCODE,SUB_SGRPCODE,sum(qty) qty
,sum(stk_qty) stk_qty,sum(intrnsit_qty) intrnsit_qty,sum(stk_qty*price) stk_qty_sp
,sum(intrnsit_qty*price) intrnsit_qty_sp,
sum(qty*price) sp,sum(qty*cost_price) cp,0 lastyr_sp --,0 est_sp
from
(
select /*+ index (c uk_matcode)*/
a.comp_Code,a.main_code,a.mat_type, a.matcode,a.qty,a.stk_qty,a.intrnsit_qty,b.price,c.cost_price,c.cat_code,c.SGRPCODE,c.SUB_SGRPCODE
from
(select comp_Code,main_code,mat_type, matcode, sum(qty) qty,sum(stk_qty) stk_qty,sum(intrnsit_qty) intrnsit_qty
from (
select comp_Code,main_code,mat_type, matcode, qty qty,qty stk_qty,0 intrnsit_qty
from
(SELECT COMP_CODE,MAIN_CODE, MAT_TYPE,MATCODE,INC_QTY-DEC_QTY QTY
FROM INV_TRN_DAY_SUM_VU
where COMP_CODE =p_comp_code AND
MAIN_CODE = p_main_code AND
TRN_DATE BETWEEN v_fnyr_stdt AND v_pst_dt-1
UNION ALL
select /*+ index (a Matdtl_compmainserial)*/
COMP_CODE,MAIN_CODE, MAT_TYPE,MATCODE,qty qty
from material_detail a
where
COMP_CODE =p_comp_code AND
MAIN_CODE =p_main_code AND
SERIAL= v_fnyr_code
)
union ALL
select comp_Code,main_code,mat_type, matcode, stk_qty qty,0 stk_qty,stk_qty intrnsit_qty
from (
select A.comp_code, A.main_code,A.mat_type,A.matcode,
nvl(issue_qty,0) -nvl(accpt_qty,0) stk_qty
from INV_STKTRFOUT_HDDTL_VU A,
(SELECT comp_code, main_code, unit_code, sloc_code, matcode, mat_type, control_no,
voucher_code,from_unit, from_sloc, stktrf_no, stktrf_date, ref_cntrlno, DELVNOTE_QTY, other_amt,
remarks, ACCPT_QTY, recv_qty, AUTH_STAT
FROM INV_STKTRFIN_HDDTL_VU
WHERE stktrf_date <= v_pst_dt-1) b
where A.comp_code=B.comp_code(+) and
A.main_code=B.main_code(+) and
A.for_unit=B.unit_code(+) and
A.for_sloc=B.sloc_code(+) and
A.matcode=B.matcode(+) and
A.mat_type=B.mat_type(+) and
A.control_no=B.ref_cntrlno(+) and
a.COMP_CODE=p_comp_code AND
a.MAIN_CODE=p_main_code AND
a.issue_date < = v_pst_dt -1 AND
NVL(B.AUTH_STAT,'P')='P'
)
)
GROUP BY COMP_CODE,MAIN_CODE, MAT_TYPE,MATCODE )a,
(select /*+ index (b prod_price_dfn_cmmpv)*/
b.comp_code,b.main_code,b.mat_type,b.matcode,max(b.prod_price) price
from inv_prod_price_dfn b
where b.comp_code = p_comp_code
and b.main_code = p_main_code
and b.mat_type ='R'
and b.price_type = 'CASH'
and b.VLD_FROM_DT = (select /*+ index (a fk_prod_price_dfn_fkidx)*/
trunc(max(a.VLD_FROM_DT))
from inv_unit_prod_price_dtls a
where a.comp_code = b.comp_code
and a.main_code = b.main_code
and a.matcode = b.matcode
and a.mat_type = b.mat_type
and a.price_type = b.price_type
and v_pst_dt-1 between a.VLD_FROM_DT and NVL(a.VLD_to_DT,v_pst_dt-1) )
group by b.comp_code,b.main_code,b.mat_type,b.matcode
)b,material_master c
where a.comp_code=b.comp_code(+)
and a.main_code=b.main_code(+)
and a.MAT_TYPE=b.mat_type(+)
and a.MATCODE=b.matcode(+)
and a.comp_code=c.comp_code
and a.main_code=c.main_code
and a.MAT_TYPE=c.mat_type
and a.MATCODE=c.matcode
and a.comp_code= p_comp_code
and a.main_code= p_main_code
)
where comp_code= p_comp_code
and main_code= p_main_code
group by comp_code,main_code,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE
union all
select comp_code,main_code,mat_type,matcode,cat_code,sgrpcode,sub_sgrpcode,0 qty,0 stk_qty,0 intrnsit_qty,0 stk_qty_sp,0 intrnsit_qty_sp
,0 sp,0 cp, lastyr_sp --,0 est_sp
from
(select comp_code,main_code,mat_type,matcode,cat_code,sgrpcode,sub_sgrpcode,round(sum(sale_value)) lastyr_sp
from sales_trans
where comp_code= p_comp_code
and main_code= p_main_code
and cntrl_no= p_fn_yr - 1
and period_ctrlno= i.period_ctrlno --11
group by comp_code,main_code,mat_type,matcode,cat_Code,sgrpcode,sub_sgrpcode)
)
where cat_code = p_cat_code --'01' -- in ('01') --,'03')
and sgrpcode = '10'
and sub_sgrpcode = '100'
group by comp_code,main_code,mat_type,matcode,cat_Code,sgrpcode,sub_sgrpcode
)
);
insert into merch_otb_mat_ordcpval (comp_code,
main_code,
mat_type,
matcode,
cat_code,
SGRPCODE,
SUB_SGRPCODE,
finyr_code,
period,
stock,
costval,
pend_poqty,
mkt_pending_qty,
pend_po_cp,
mkt_pending_cp
)
(select comp_code,main_code,mat_type,MAT_CODE,cat_code,sgrpcode,sub_sgrpcode --,sup_code ,cur_code,ex_rtae
,p_fn_yr,i.period_ctrlno,sum(qty),sum(cp),sum(pend_poqty),sum(mkt_pending_qty),sum(pend_po_cp),sum(mkt_pending_cp)
from
(select m.comp_code,m.main_code,m.mat_type,m.MAT_CODE,m.cat_code,m.sgrpcode,m.sub_sgrpcode,m.sup_code,g.cur_code,nvl(g.ex_rate,1) ex_rate,p.lc_factor
,round(sum(pend_qty)) qty,round(sum(nvl(pend_qty,0)*(nvl(unit_rate,0)/nvl(g.ex_rate,1)) * nvl(p.lc_factor,1))) cp
,round(sum(pend_poqty)) pend_poqty ,round(sum(mkt_pending_qty)) mkt_pending_qty
--,round(sum(nvl(pend_poqty,0)*nvl(cost_price,0))) pend_po_cp
,round(sum(nvl(pend_poqty,0)*(nvl(unit_rate,0)/nvl(g.ex_rate,1)) * nvl(p.lc_factor,1))) pend_po_cp
,round(sum(nvl(mkt_pending_qty,0)*nvl(cost_price,0))) mkt_pending_cp
from
(
select a.COMP_CODE,a.MAIN_CODE,a.mat_type,a.MAT_CODE,a.pend_poqty pend_qty,a.pend_poqty,0 mkt_pending_qty
,b.cost_price,b.cat_code,b.sgrpcode,b.sub_sgrpcode,unit_rate,b.sup_code
from
(
select COMP_CODE,MAIN_CODE,UNIT_CODE,PO_CONTROL_NO,ind_control_no,mat_type,MAT_CODE,
nvl(sum(PO_QTY),0) po_qty, nvl(sum(can_qty),0) can_qty,nvl(sum(rcd_qty),0) rcd_qty,
nvl(sum(PO_QTY),0)-(nvl(sum(can_qty),0)+nvl(sum(rcd_qty),0)) pend_poqty,sum(unit_rate) unit_rate
from (
select a.COMP_CODE,a.MAIN_CODE,a.UNIT_CODE,a.PO_CONTROL_NO,a.ind_control_no,a.mat_type,a.MAT_CODE,
nvl(a.PO_QTY,0)+nvl(a.EXCESS_GRNQTY,0) po_qty, a.can_qty,a.po_obrcpt_qty rcd_qty,a.unit_rate
from po_detail a, po_header b
where a.comp_code=b.comp_code
and a.main_code=b.main_code
and a.unit_code=b.unit_code
and a.po_control_no=b.po_control_no
and b.po_stat in ('L') --,'C')
AND A.POAMD_NO=B.POAMD_NO
and nvl(force_close,'N')='N'
and b.po_date BETWEEN v_fnyr_stdt AND v_pst_dt-1
and nvl(force_close,'N')='N'
and case when nvl(b.EST_ARRIVAL_DT,trunc(sysdate))<=trunc(sysdate) then
trunc(sysdate)
else
b.EST_ARRIVAL_DT
end
between v_pst_dt and v_ped_dt
union all
SELECT a.COMP_CODE,a.MAIN_CODE,NVL(A.PO_UNIT,a.UNIT_CODE),to_number(a.PO_CONTROL_NO),a.ind_control_no,a.mat_type,a.MATCODE,
0 po_qty, 0 can_qty,SUM(a.ACCPT_QTY) RCD_QTY,0 unit_rate
FROM INV_GRN_DTL_v a
WHERE a.PO_CONTROL_NO<>999999 ---THIS GIVES THE PO,IND,CONTROL_NO WISE QTY OF MAT WHICH ARE NOT CNCELLED I.E APPROVED OR PENDING----
and a.grn_status<>'C'
and nvl(a.auth_status,'P')in ('P','A')
and a.comp_code=p_comp_code
and a.main_code=p_main_code
-- and NVL(A.PO_UNIT,a.UNIT_CODE) not in ('500','503')
and case when nvl(a.grn_date,trunc(sysdate))<=trunc(sysdate) then
trunc(sysdate)
else
a.grn_date
end between v_pst_dt and v_ped_dt
--and a.grn_date between '27-feb-2017' and '31-mar-2017'
GROUP BY a.COMP_CODE,a.MAIN_CODE,a.UNIT_CODE,to_number(a.PO_CONTROL_NO),a.ind_control_no,a.MATCODE,mat_type,PO_UNIT,a.cur_code
union all
SELECT a.COMP_CODE,a.MAIN_CODE,NVL(A.GRN_UNIT,a.UNIT_CODE),to_number(b.PO_CONTROL_NO),b.ind_control_no,a.mat_type,a.item_CODE,
0 po_qty, 0 can_qty,-1*sum(qty) rej_qty,0 unit_rate
FROM gp_hdr_trn_vu a,inv_grn_dtl b
WHERE b.PO_CONTROL_NO<>999999 ---THIS GIVES THE PO,IND,CONTROL_NO WISE QTY OF MAT WHICH ARE NOT CNCELLED I.E APPROVED OR PENDING----
and a.comp_code=b.comp_code
and a.main_code=b.main_code
and a.grn_unit=b.unit_code
and a.grn_control_no=b.control_no
and a.item_code=b.matcode
-- and NVL(a.attrib_code1,0)=NVL(b.attrib_code1,0)
-- and NVL(a.attrib_code2,0)=NVL(b.attrib_code2,0)
and nvl(a.grn_yn,'Y')='Y'
and a.comp_code=p_comp_code
and a.main_code=p_main_code
-- and NVL(A.GRN_UNIT,a.UNIT_CODE) not in ('500','503')
and case when nvl(a.gp_dt,trunc(sysdate))<=trunc(sysdate) then
trunc(sysdate)
else
a.gp_dt
end between v_pst_dt and v_ped_dt
--and a.gp_dt between '27-feb-2017' and '31-mar-2017'
GROUP BY a.COMP_CODE,a.MAIN_CODE,A.GRN_UNIT,a.UNIT_CODE,b.PO_CONTROL_NO,b.ind_control_no,a.mat_type,a.item_CODE
)
GROUP BY COMP_CODE,MAIN_CODE,UNIT_CODE,PO_CONTROL_NO,ind_control_no,MAT_CODE,mat_type
)a,material_master b
where a.comp_code=b.comp_code
and a.main_code=b.main_code
and a.mat_code=b.matcode
and a.mat_type=b.mat_type
and nvl(a.pend_poqty,0)>0
and b.cat_code = p_cat_code -- in ('01') --,'03','75') ---cat_code where cat_code in ('03','01','75')
and sgrpcode = '10'
and sub_sgrpcode = '100'
) m , curr_exrate_latest_grn g , plnd_stkturn_dtl p
where m.comp_code=p_comp_code
and m.main_code=p_main_code
and m.sup_code = g.supcode(+)
and m.cat_code = p.cat_code
and m.sup_code = p.sup_code
group by m.comp_code,m.main_code,m.mat_type,m.MAT_CODE,m.cat_code,m.sgrpcode,m.sub_sgrpcode,m.sup_code,g.cur_code,g.ex_rate,p.lc_factor
)
group by comp_code,main_code,mat_type,MAT_CODE,cat_code,sgrpcode,sub_sgrpcode
);
v_ob_stkval1:=0;
v_ob_stkval2:=0;
for j in
(
select comp_code,main_code ,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period,
sum(stk_qty) stk_qty
from
(select comp_code,main_code ,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period,
stk_qty
--stock ,salval,costval,LASTYR_SP,stk_qty,intrnsit_qty,stk_sp,intrnsit_sp
from merch_otb_mat_saleval
where comp_code=p_comp_code
and main_code=p_main_code
and finyr_code=p_fn_yr
and period=i.period_ctrlno
and cat_code = p_cat_code
--order by cat_code,sgrpcode,sub_sgrpcode,period
union all
select comp_code,main_code,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period,
0 stk_qty
--stock,costval,pend_poqty,mkt_pending_qty,pend_po_cp,mkt_pending_cp
from merch_otb_mat_ordcpval
where comp_code=p_comp_code
and main_code=p_main_code
and finyr_code=p_fn_yr
and period=i.period_ctrlno
and cat_code = p_cat_code
)
group by comp_code,main_code ,mat_type,matcode,cat_code,SGRPCODE,SUB_SGRPCODE,finyr_code,period
)
loop
begin
select plnd_mths_stk into v_stktur
from plnd_stkturn_mst
where comp_code=j.comp_code
and main_code=j.main_code
and cat_code=j.cat_code
and finyr_code<=j.finyr_code
and rownum=1
order by finyr_code desc ;
exception
when no_data_found then
v_stktur:=3;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating Stock Turn Months');
when others then
dbms_output.put_line(sqlerrm||4);
end;
v_close_stkshdbe:=0;--round(nvl(j.est_sp,0)*v_stktur);
if i.period_ctrlno=1 then
v_period:=13;
v_fnyr:=p_fn_yr-1;
else
v_period:=i.period_ctrlno;
v_fnyr:=p_fn_yr; -------------
end if;
--
/*Begin
SELECT TAX_PERC INTO V_TAX_PERC
FROM MAIN_CATEGORY
where COMP_CODE = P_COMP_CODE
AND MAIN_CODE = P_MAIN_CODE
AND CAT_CODE= j.cat_code;
Exception when others then
V_TAX_PERC := 9;
End;
---
if nvl(j.salval,0)=0 then
v_salval:=1;
ELSE
v_salval:=j.salval - ( j.salval * (V_TAX_PERC / (100 + V_TAX_PERC ))) ;
end if;
v_marg_perc:=round(((v_salval-j.costval)/nvl(v_salval,1))*100);*/
begin
select costval,pend_poqty,mkt_pending_qty,pend_po_cp,mkt_pending_cp into v_costval,v_pend_poqty,v_mkt_pending_qty,v_pend_po_cp,v_mkt_pending_cp
-- stock need to add
from merch_otb_mat_ordcpval
where comp_code=j.comp_code
and main_code=j.main_code
and cat_code=j.cat_code
and matcode = j.matcode
and sgrpcode=j.sgrpcode
and sub_sgrpcode=j.sub_sgrpcode
and finyr_code=j.finyr_code
and period=j.period;
exception
when no_data_found then
v_costval:=0;
v_pend_poqty :=0;
v_mkt_pending_qty:=0;
v_pend_po_cp:=0;
v_mkt_pending_cp:=0;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating Stock Turn Months');
when others then
dbms_output.put_line(sqlerrm||4);
end;
----siva 19062016--from
begin
select finctrl_no,period_ctrlno into v_curfinyr,v_curperiod
from budg_finperiod_hdr
where trunc(sysdate) between from_date and to_date;
exception
when no_data_found then
dbms_output.put_line('period not found');
when others then
dbms_output.put_line(sqlerrm||4);
end;
Begin
select margin into v_new_margin
from plnd_stkturn_mst
where comp_code=j.comp_code
and main_code=j.main_code
and cat_code= j.cat_code ; -- 'BC03';
Exception when no_data_found then
v_new_margin := 40 ;
when others then
dbms_output.put_line(sqlerrm||' Error Cat Margin ');
End;
--v_rsp := v_costval/(1-v_new_margin/100) ;
v_rsp := v_pend_po_cp/(1-v_new_margin/100) ;
v_rsp := v_rsp - v_mkt_pending_cp;
--v_onorder_sp := v_rsp;
v_pend_po_cp := v_rsp;
/*--dbms_output.put_line('j.matcode '|| j.matcode||' v_pend_po_cp '||v_pend_po_cp||' marg '||v_marg_perc);
v_costval:=v_costval-v_mkt_pending_cp;
v_onorder_sp:=round(v_costval+(v_costval*(v_marg_perc/100)));
v_onorder_po_sp:= round(v_pend_po_cp+(v_pend_po_cp*(v_marg_perc/100)));
-- dbms_output.put_line('v_onorder_po_sp '||v_onorder_po_sp);
v_onorder_mkt_sp:=round(v_mkt_pending_cp);
v_onorder_sp:=v_onorder_sp+v_onorder_mkt_sp;
v_val:=nvl(v_ob_stkval2,0)+nvl(v_onorder_sp,0);
v_close_stkwillbe:=round(v_val-p_est_markdown-0); --j.est_sp); -- rmeember this 1
v_otbval_sp:=nvl(v_close_stkshdbe,0)-nvl(v_close_stkwillbe,0);
v_otbval_cp:=round((v_otbval_sp*(100-v_marg_perc))/100);*/
Begin
select mat_name,a.STATUS_CODE,prod_status,a.crtd_dt,a.sup_code,b.sup_name,a.f_on_b_order,a.min_order_qty
into v_mat_name,v_prod_status,v_prod_status_desc,v_crtd_dt,v_sup_code,v_sup_name, v_f_on_b_order,v_min_order_qty
from material_master a, sup_mast b
where a.comp_code=b.comp_Code
and a.main_code = b.main_code
and a.sup_code = b.sup_code
and a.comp_code = p_comp_code
and a.main_code = p_main_code
and a.cat_code = j.cat_code
and a.matcode = j.matcode;
Exception when no_data_found then
v_mat_name := 'Mat Name';
v_prod_status := null ;
v_crtd_dt := null ;
when others then
dbms_output.put_line(sqlerrm||' Error While getting material name');
End;
begin
select distinct from_date,to_date into v_ppst_dt,v_pped_dt
from budg_finperiod_hdr
where comp_code=p_comp_code
and main_code=p_main_code
and finctrl_no||period_ctrlno=p_fn_yr||i.period_ctrlno-1 ;
exception
when no_data_found then
null;
when TOO_MANY_ROWS then
dbms_output.put_line('Too Many Rows While Validating From And To Date');
when others then
dbms_output.put_line(sqlerrm||2);
end;
/*-- getting last 1month sale values
Begin
select sum(nvl(sale_qty,0)) , sum(nvl(val,0)) into v_lst1mnth_sale,v_lst1mnth_saleval
from stk_Sales_vu_mv a, budg_finperiod_hdr_vu b
where a.comp_code = p_comp_code
and a.main_code = p_main_code
and cat_code = j.cat_code
and sgrpcode = j.SGRPCODE
and sub_sgrpcode = j.SUB_SGRPCODE
and matCode = j.matcode
and ord_date between b.from_date and b.to_date
and b.periodnum between (v_prdno - 2) and (v_prdno - 1) ;
--and ord_date between add_months(v_pst_dt-1,-1) and v_pst_dt-1;
Exception when others then
v_lst1mnth_sale := 0;
v_lst1mnth_saleval := 0;
dbms_output.put_line(sqlerrm ||' Error at 1 month sale value picking');
End;
-- getting last 3month sale values
Begin
select sum(nvl(sale_qty,0)) , sum(nvl(val,0)) into v_lst3mnth_sale,v_lst3mnth_saleval
from stk_Sales_vu_mv a, budg_finperiod_hdr_vu b
where a.comp_code = p_comp_code
and a.main_code = p_main_code
and cat_code = j.cat_code
and sgrpcode = j.SGRPCODE
and sub_sgrpcode = j.SUB_SGRPCODE
and matCode = j.matcode
and ord_date between b.from_date and b.to_date
and b.periodnum between (v_prdno - 3) and (v_prdno - 1) ;
--and ord_date between add_months(v_pst_dt-1,-3) and v_pst_dt-1;
Exception when others then
v_lst1mnth_sale := 0;
v_lst1mnth_saleval := 0;
dbms_output.put_line(sqlerrm ||' Error at 3 month sale value picking');
End;*/
-- getting last 6month sale values
Begin
select sum(nvl(sale_qty,0)) , sum(nvl(val,0)) ,
sum(case when b.periodnum = (v_prdno - 1) then nvl(sale_qty,0)
end) mnth1,
sum(case when b.periodnum = (v_prdno - 1) then nvl(val,0)
end) mnth1_val,
sum(case when b.periodnum = (v_prdno - 2) then nvl(sale_qty,0)
end) mnth2,
sum(case when b.periodnum = (v_prdno - 2) then nvl(val,0)
end) mnth2_val,
sum(case when b.periodnum = (v_prdno - 3) then nvl(sale_qty,0)
end) mnth3,
sum(case when b.periodnum = (v_prdno - 3) then nvl(val,0)
end) mnth3_val,
sum(case when b.periodnum = (v_prdno - 4) then nvl(sale_qty,0)
end) mnth4,
sum(case when b.periodnum = (v_prdno - 4) then nvl(val,0)
end) mnth4_val,
sum(case when b.periodnum = (v_prdno - 5) then nvl(sale_qty,0)
end) mnth5,
sum(case when b.periodnum = (v_prdno - 5) then nvl(val,0)
end) mnth5_val,
sum(case when b.periodnum = (v_prdno - 6) then nvl(sale_qty,0)
end) mnth6,
sum(case when b.periodnum = (v_prdno - 6) then nvl(val,0)
end) mnth6_val
into v_lst6mnth_sale,v_lst6mnth_saleval ,
v_mnth1,v_mnth1_val, v_mnth2,v_mnth2_val, v_mnth3,v_mnth3_val, v_mnth4,v_mnth4_val, v_mnth5,v_mnth5_val, v_mnth6,v_mnth6_val
from stk_Sales_vu_mv a, budg_finperiod_hdr_vu b
where a.comp_code = p_comp_code
and a.main_code = p_main_code
and cat_code = j.cat_code
and sgrpcode = j.SGRPCODE
and sub_sgrpcode = j.SUB_SGRPCODE
and matCode = j.matcode
and ord_date between b.from_date and b.to_date
and b.periodnum between (v_prdno - 6) and (v_prdno - 1) ;
--and ord_date between add_months(v_pst_dt-1,-6) and v_pst_dt-1;
Exception when others then
v_lst1mnth_sale := 0;
v_lst1mnth_saleval := 0;
dbms_output.put_line(sqlerrm ||' Error at 6 month sale value picking');
End;
v_lst1mnth_sale := nvl(v_mnth1,0) ;
v_lst1mnth_saleval := nvl(v_mnth1_val,0);
v_lst3mnth_sale := nvl(v_mnth1,0) + nvl(v_mnth2,0) + nvl(v_mnth3,0);
v_lst3mnth_saleval := nvl(v_mnth1_val,0) + nvl(v_mnth2_val,0) + nvl(v_mnth3_val,0);
Begin
select max(grn_date) , sum(a.recd_qty) into v_last_recd,v_last_rcvd_qty
from inv_grn_dtl_v a
where comp_code = p_comp_code and
NVL(grn_status,'P') ='A' and
NVL(accpt_qty,0)>0 and
grn_date = (select max(grn_date)
from inv_grn_dtl_v b
where b.matcode = a.matcode and
NVL(grn_status,'P') ='A' and
b.grn_date<= v_pst_dt -1 --'29-jan-2017'
)
and matcode =j.matcode
group by a.comp_code , a.main_code, a.mat_type, a.matcode ;
Exception when others then
null; --dbms_output.put_line(sqlerrm ||' last rcvd date'||j.matcode||' '||v_pst_dt );
End;
Begin
select min(grn_date) , sum(a.recd_qty) into v_first_rcvd_dt,v_first_rcvd_qty
from inv_grn_dtl_v a
where
NVL(grn_status,'P') ='A' and
NVL(accpt_qty,0)>0 and
grn_date = (select min(grn_date)
from inv_grn_dtl_v b
where b.matcode = a.matcode and
NVL(grn_status,'P') ='A' and
b.grn_date<= v_pst_dt -1 --'29-jan-2017'
)
and matcode = j.matcode
group by a.comp_code , a.main_code, a.mat_type, a.matcode ;
Exception when others then
null; --dbms_output.put_line(sqlerrm ||' first rcvd date'||j.matcode||' '||v_pst_dt );
End;
--v_ema_qty := otb_stock_emv(p_comp_code, j.cat_code, v_sup_code, nvl(v_mnth1,0) , nvl(v_mnth2,0) ,nvl(v_mnth3,0) , nvl(v_mnth4,0), nvl(v_mnth5,0) , nvl(v_mnth6,0) );
v_ema_qty := otb_stock_emv(p_comp_code, j.cat_code, v_sup_code, nvl(v_mnth6,0) , nvl(v_mnth5,0) ,
nvl(v_mnth4,0) , nvl(v_mnth3,0), nvl(v_mnth2,0) , nvl(v_mnth1,0) );
v_sgststk_qty := nvl(v_ema_qty,0) - ( j.stk_qty + v_pend_poqty); -- j.stk_qty
if v_prod_status = 'C' then -- continued
if nvl(v_sgststk_qty,0)>0 then -- suggested is more than 0
v_sgststk_qty := v_sgststk_qty;
else
v_sgststk_qty := null;
End if;
else
v_sgststk_qty := null;
End If;
insert into merch_otb_mat_sscateg(comp_code ,
main_code ,
mat_type ,
matcode ,
mat_name ,
prod_status ,
prod_stat_desc ,
sup_code ,
sup_name ,
cat_code ,
sgrpcode ,
sub_sgrpcode ,
crtd_dt ,
last_recd ,
last_rcvd_qty ,
first_rcvd_dt ,
first_rcvd_qty ,
stk_qty ,
on_ord_qty ,
cost_price_grn ,
on_ord_val ,
lst1mnth_sale ,
lst1mnth_saleval ,
lst3mnth_sale ,
lst3mnth_saleval ,
lst6mnth_sale ,
lst6mnth_saleval ,
sgst_stk_qty ,
tobe_ord_qty ,
finyr_code ,
period_ctrlno ,
fob ,
min_order_qty ,
mnth1_qty ,
mnth2_qty ,
mnth3_qty ,
mnth4_qty ,
mnth5_qty ,
mnth6_qty,
ema_qty,
crtd_by,
crtd_date
)
values
(p_comp_code,
p_main_code,
j.mat_type,
j.matcode,
v_mat_name,
v_prod_status,
v_prod_status_desc,
v_sup_code,
v_sup_name,
j.cat_code,
j.SGRPCODE,
j.SUB_SGRPCODE,
v_crtd_dt,
v_last_recd,
v_last_rcvd_qty,
v_first_rcvd_dt,
v_first_rcvd_qty,
j. stk_qty,
v_pend_poqty,
null,
--v_onorder_sp, --naren
v_pend_po_cp, --
v_lst1mnth_sale,
v_lst1mnth_saleval,
v_lst3mnth_sale ,
v_lst3mnth_saleval,
v_lst6mnth_sale ,
v_lst6mnth_saleval,
v_sgststk_qty,
null,
j.finyr_code,
j.period,
v_f_on_b_order,
v_min_order_qty,
v_mnth1,
v_mnth2,
v_mnth3,
v_mnth4,
v_mnth5,
v_mnth6,
v_ema_qty,
'SMCN',
sysdate);
end loop;
commit;
--end if;
end loop;
----------------
Commit;
end;
No comments:
Post a Comment