return to PRS Technologies website
-------------------------------------------------------------------------------
--
-- Program: pr_mattsub.sql
-- Company: PRS Technologies, Inc.
-- Author: Gary Vinson
-- Description: Print a summary of Split Billing
--
-- Change Log
--
-- Date Name Description..............
--
-- 08/27/2001 GDV Created
--
-------------------------------------------------------------------------------
drop procedure pr_mattsub;
create procedure pr_mattsub (xmatter char(15),
xprof_idx integer,
xbill_date date,
xtime_start date,
xtime_end date,
xcost_start date,
xcost_end date,
xbill_templt char(2),
xtime_format char(5),
xcost_format char(5),
xprof_tmplt char(7),
xinv_num char(8),
xcurr char(4)
)
define tbuff char(132);
define x integer;
define y integer;
define p_mssub like mattsub.mssub;
define p_mspercent decimal(7,2);
define p_msamount money;
define p_msdate date;
define totamt money;
define p_sptbdl like sptime.sptbdl;
define p_spcbam like spcost.spcbam;
define p_tindex integer;
define p_cindex integer;
-----------------------------------------------------------------------
---- set debug file to "trace.out";
---- TRACE ON;
-----------------------------------------------------------------------
define p_msmatter like mattsub.msmatter;
define o_msmatter like mattsub.msmatter;
define d_mspercent CHAR(9);
define d_msamount CHAR(30);
define p_tbilldol like timecard.tbilldol;
define p_cbillamt like cost.cbillamt;
let p_mssub = NULL;
let p_mspercent = 0;
let p_msamount = 0;
let p_sptbdl = 0;
let p_spcbam = 0;
let p_tindex = 0;
let p_cindex = 0;
let totamt = 0;
LET o_msmatter = " ";
FOREACH
select distinct mattsub.msmatter,mattsub.mssub
INTO p_msmatter,p_mssub
FROM mattsub
WHERE mattsub.msmatter IN
(SELECT sptmatter FROM temptime,sptimes,sptime
WHERE temptime.tindex = sptimes.sptsindex AND
sptimes.sptindex = sptime.sptindex ) OR
mattsub.msmatter IN
(SELECT spcmatter FROM tempcost,spcosts,spcost
WHERE tempcost.cindex = spcosts.spcsindex AND
spcosts.spcindex = spcost.spcindex)
ORDER BY msmatter
IF o_msmatter <> p_msmatter THEN
IF p_msamount <> 0 THEN
LET tbuff = " ";
insert into tempproc values (0,tbuff);
LET d_msamount = totamt;
WHILE d_msamount [29,29] = " "
let d_msamount[2,30] = d_msamount[1,29];
let d_msamount[1,1] = " ";
END WHILE
LET p_mssub = "TOTAL ";
LET d_mspercent = " ";
let tbuff = " " || p_mssub || " " || d_mspercent || " " || d_msamount;
insert into tempproc values (0,tbuff);
LET o_msmatter = p_msmatter;
END IF
END IF
-------- FIND TIMECARDS AND CALCULATE ORIGINAL TOTAL BILLED ------------
LET p_tbilldol = 0;
LET p_sptbdl = 0;
select SUM(sptbdl)
into p_sptbdl
from sptime
where sptime.sptindex IN
(SELECT sptimes.sptindex
FROM sptimes,temptime
WHERE temptime.tmatter = p_mssub AND
temptime.tindex = sptimes.sptsindex);
if p_sptbdl IS NULL then
let p_sptbdl = 0;
end if
-------- FIND COST RECS AND CALCULATE ORIGINAL TOTAL BILLED ------------
LET p_cbillamt = 0;
select SUM(spcbam)
into p_spcbam
from spcost
where spcost.spcindex IN
(SELECT spcosts.spcindex
FROM spcosts,tempcost
WHERE tempcost.cmatter = p_mssub AND
tempcost.cindex = spcosts.spcsindex);
if p_spcbam IS NULL then
let p_spcbam = 0;
end if
LET p_msdate = NULL;
SELECT MAX(msdate)
INTO p_msdate
FROM mattsub
WHERE mattsub.msmatter = p_msmatter AND
mattsub.mssub = p_mssub;
LET p_mspercent = NULL;
SELECT mspercent
INTO p_mspercent
FROM mattsub
WHERE mattsub.msmatter = p_msmatter AND
mattsub.mssub = p_mssub AND
mattsub.msdate = p_msdate;
IF p_mspercent IS NULL THEN
LET p_mspercent = 100;
END IF
LET d_mspercent = p_mspercent;
LET p_tbilldol = p_sptbdl * (p_mspercent/100);
IF p_tbilldol IS NULL THEN
LET p_tbilldol = 0;
END IF
LET p_cbillamt = p_spcbam * (p_mspercent/100);
IF p_cbillamt IS NULL THEN
LET p_cbillamt = 0;
END IF
let p_msamount = p_tbilldol + p_cbillamt;
LET totamt = p_sptbdl + p_spcbam;
IF p_msamount = 0 THEN
CONTINUE FOREACH;
END IF
LET d_msamount = p_msamount;
WHILE d_mspercent[7,7] = " "
let d_mspercent[2,8] = d_mspercent[1,7];
let d_mspercent[1,1] = " ";
END WHILE
IF d_mspercent[8,8] = " " THEN
LET d_mspercent[8,8] = "%";
ELSE
LET d_mspercent[9,9] = "%";
END IF
WHILE d_msamount [29,29] = " "
let d_msamount[2,30] = d_msamount[1,29];
let d_msamount[1,1] = " ";
END WHILE
let tbuff = " " || p_mssub || " " || d_mspercent || " " || d_msamount;
insert into tempproc values (0,tbuff);
LET o_msmatter = p_msmatter;
END FOREACH
LET tbuff = " ";
insert into tempproc values (0,tbuff);
LET d_msamount = totamt;
WHILE d_msamount [29,29] = " "
let d_msamount[2,30] = d_msamount[1,29];
let d_msamount[1,1] = " ";
END WHILE
LET p_mssub = "TOTAL ";
LET d_mspercent = " ";
let tbuff = " " || p_mssub || " " || d_mspercent || " " || d_msamount;
insert into tempproc values (0,tbuff);
end procedure