return to PRS Technologies website


pr_mattsub.sql
------------------------------------------------------------------------------- -- -- 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