Hello experts,
I am having a stored procedure that was written by someone. I am not quite positive about the stored procedure because I missed the basic understanding of it.
I didn't get anything from it. Now that I have to work on similar kind of requirement I need to understand the Stored procedure. I would appreciate if anyone is interested in explaining me the SP. I know this is kind of weird but I feel much better if I tell it out rather than rumbling with it.
DELIMITER $$
DROP PROCEDURE IF EXISTS `spm_pushittothemax` $$
CREATE DEFINER=`mysqluser`@`` PROCEDURE `spm_pushittothemax`(
tCust char(7)
)
begin_label: begin
declare lysales, tysales, promosales, myperc decimal(10, 2) default 0;
if year(current_date) <> 2014 then
leave begin_label;
end if;
select
rmname, rmadd1, rmadd2, rmadd3, rmcity, rmstat, rmzip,
(case when wccust is null then 'F' else 'T' end) pittm,
coalesce(sum(lyamount), 0) lyamount,
coalesce(sum(tyamount), 0) tyamount,
coalesce(sum(promosales), 0) promosales,
coalesce(sum(promosales), 0) * 0.5 promo
from TESTDATA.VARCUST
left outer join TESTDATA.VXWWCU on wctype = 'PITTM' and wccust = rmcust
left outer join (select
sum(case when year(oaordt) = 2013 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as lyamount,
sum(case when year(oaordt) = 2014 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as tyamount,
sum(case when year(oaordt) = 2014 and oaordt between 20141101 and 20141231 then (case when oatype = 'O' then obqord else -obqord end) * obpric else 0 end) as promosales
from TESTDATA.VCOHEAD
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obdel = 'A'
# join TESTDATA.VINITEM on icitem = obitem and icdiv = 1
# join TESTDATA.VININDEX on i001trln = ictrln #and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer')
where oacmp = 1
and oadel = 'A'
and oaprnt <> 'A'
and oaordt between 20130101 and 20141231
and oabocd in (0, 50)
and (oacust = tCust or (tCust = '0599298' and oacust = '2001172' ))
UNION ALL
select
sum(case when year(saordt) = 2013 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as lyamount,
sum(case when year(saordt) = 2014 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as tyamount,
sum(case when year(saordt) = 2014 and saordt between 20141101 and 20141231 then (case when satype = 'O' then sboqty else -sboqty end) * sbpric else 0 end) as promosales
from TESTDATA.VSAHEAD
join TESTDATA.VSADETL on sbcmp = 1 and sbord = saord and sbbocd = sabocd and sbindt = saindt
# join TESTDATA.VINITEM on icitem = sbitem and icdiv = 1
# join TESTDATA.VININDEX on i001trln = ictrln and i001002 in ('Auto Tires', 'Commercial Over the Road Truck Tires', 'Farm Tires', 'Goodride Trailer', 'Light Truck / Recreational Vehicle Tires', 'Medium & Heavy Off-The-Road', 'Trailer')
where sacmp = 1
and saordt between 20130101 and 20141231
and sabocd in (0, 50)
and (sacust = tCust or (tCust = '0599298' and sacust = '2001172' ))
) x on 1=1
where rmcmp = 1 and rmcust = tCust
group by rmname, rmadd1;
set lysales = COALESCE((select scdltd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
set tysales = COALESCE((select
sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
from TESTDATA.VCODLYO
join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
where o17cmp = 1 and oacust = tCust), 0)
+ COALESCE((select scdytd from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
set promosales = COALESCE((select
sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
from TESTDATA.VCODLYO
join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
where o17cmp = 1 and oacust = tCust and current_date + 0 between 20141101 and 20141231), 0)
+COALESCE((select scd10 + scd11 + scd12 from TESTDATA.VSAANAL where sccmp = 1 and scrtyp = '02' and sccust = tCust), 0);
set myperc = (case when tysales = 0 then 0 when lysales = 0 then '9999999' else round((tysales + promosales * .5) / lysales * 100, 2) end);
select
myperc, count(*) + 1 rank
from TESTDATA.VSAANAL
where
sccmp = 1
and scrtyp = '02'
and sccust <> tCust
and
(case when scdytd = 0 then 0 when scdltd = 0 then '9999999' else round(
(scdytd + COALESCE((select sum((case oatype when 'O' then obqshp else -obqshp end) * obpric) as dollars
from TESTDATA.VCODLYO
join TESTDATA.VCOHEAD on oacmp = 1 and oaord = o17ord and oabocd = o17bocd
join TESTDATA.VCODETL on obcmp = 1 and obord = oaord and obbocd = oabocd and obpcod <> 'D'
where o17cmp = 1 and oacust = sccust), 0)
) / scdltd * 100, 2) end)
> myperc;
end $$
DELIMITER ;