pears.iqacCreateInvoices [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.iqacCreateInvoices ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."iqacCreateInvoices"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pSessionID" char(20),in "pDocDate" date ) 
result( "pResult" char(250),"pinvcount" integer ) 
begin
  declare "caninvoice" char(250);
  declare "totcharge" double;
  declare "res" char(250);
  declare "lasttsid" char(20);
  declare "invaddres" integer;
  -- invoice finish checks
  declare "lastcompanyid" char(20);
  declare "lastaccountcode" char(12);
  declare "lasttheirref" char(50);
  declare "lastperiod" integer;
  declare "lastnetcharge" double;
  -- globals
  declare "invcount" integer;
  declare "uncommittedcount" integer;
  declare "docid" char(8);
  declare "tempdocid" char(8);
  set "uncommittedcount" = 1;
  set "invcount" = 0;
  -- init invoice finish checks
  set "lastcompanyid" = '';
  set "lastaccountcode" = '';
  set "lasttheirref" = '';
  set "lastperiod" = 0;
  set "lastnetcharge" = 0.0;
  -- loop over tsids
  for "timesheets" as "tscursor" no scroll cursor for
    select "temptimesheetid" as "tid"
      from "InvoiceValidReportData" as "d" join "temptimesheet" as "t" on "t"."temptimesheetid" = "d"."id" key join "placement" key join "employment"
      where "d"."sessionid" = "psessionid"
      order by "d"."IVRDID" asc for read only
  do -- insert records in correct order by account code and invoice grouping splitting inv and cred + first
    -- ignoring billed 
    -- 0 charge just mark billed 
    select "temptimesheetinvoice"("tid"),(select "sum"("unitscharged"*"chargerate") from "temptimesheetline" where "temptimesheetid" = "tid") into "caninvoice","totcharge";
    if "totcharge" = 0.0 then
      update "temptimesheet" set "billed" = 1 where "temptimesheetid" = "tid"
    else
      if("caninvoice" = '') and not exists(select "temptimesheetid" from "temptimesheet" where "temptimesheetid" = "tid" and "billed" = 1) then
        -- check allowed else do rest
        call "iqacInvoiceOneTimesheet"("tid","pDocDate","lastcompanyid","lastaccountcode","lasttheirref","lastperiod","lastnetcharge","invcount","uncommittedcount","docid","res",
        "lastcompanyid","lastaccountcode","lasttheirref","lastperiod","lastnetcharge","invcount","uncommittedcount","tempdocid");
        if "tempdocid" is not null then set "docid" = "tempdocid"
        end if
      end if end if;
    set "lasttsid" = "tid" end for;
  if "uncommittedcount" > 0 then
    call "iqacDocumentFinish"("docid","lasttsid","pDocDate","lastaccountcode","invaddres")
  end if; --      set invcount = invcount + invaddres; 
  select '0:~Success',"invcount"
exception
  when others then
    resignal;
    select '1:~Failed',"invcount"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."iqacCreateInvoices" IS 
{create procedure iqacCreateInvoices 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

(in pSessionID char(20),in pDocDate date)
result(pResult char(250),pinvcount integer)
begin
  declare caninvoice char(250);
  declare totcharge double;
  declare res char(250);
  declare lasttsid char(20);
  declare invaddres integer;
  -- invoice finish checks
  declare lastcompanyid char(20);
  declare lastaccountcode char(12);
  declare lasttheirref char(50);
  declare lastperiod integer;
  declare lastnetcharge double;
  -- globals
  declare invcount integer;
  declare uncommittedcount integer;
  declare docid char(8);
  declare tempdocid char(8);
  set uncommittedcount=1;
  set invcount=0;
  -- init invoice finish checks
  set lastcompanyid='';
  set lastaccountcode='';
  set lasttheirref='';
  set lastperiod=0;
  set lastnetcharge=0.0;
  -- loop over tsids
  for timesheets as tscursor no scroll cursor for
    select temptimesheetid as tid from
      InvoiceValidReportData as d join temptimesheet as t on t.temptimesheetid = d.id key join placement key join employment where
      d.sessionid = psessionid order by
      d.IVRDID asc for read only do
    -- insert records in correct order by account code and invoice grouping splitting inv and cred + first
    -- ignoring billed 
    -- 0 charge just mark billed 
    select temptimesheetinvoice(tid),(select sum(unitscharged*chargerate) from temptimesheetline where temptimesheetid = tid) into caninvoice,totcharge;
    if totcharge = 0.0 then
      update temptimesheet set billed = 1 where temptimesheetid = tid
    else
      if(caninvoice = '') and not exists(select temptimesheetid from temptimesheet where temptimesheetid = tid and billed = 1) then
        -- check allowed else do rest
        call iqacInvoiceOneTimesheet(tid,pDocDate,lastcompanyid,lastaccountcode,lasttheirref,lastperiod,lastnetcharge,invcount,uncommittedcount,docid,res, 
             lastcompanyid,lastaccountcode,lasttheirref,lastperiod,lastnetcharge,invcount,uncommittedcount,tempdocid);
        if tempdocid is not null then set docid=tempdocid
        end if
      end if
    end if;
    set lasttsid=tid end for;
  if uncommittedcount > 0 then
    call iqacDocumentFinish(docid,lasttsid,pDocDate,lastaccountcode,invaddres)
  end if; --      set invcount = invcount + invaddres; 
  select '0:~Success',invcount
exception
  when others then
    resignal;
    select '1:~Failed',invcount
end
}
</code>