====== pears.iqacStartNewInvoice ======
<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"."iqacStartNewInvoice"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pTimesheetID" char(20),in "pCompanyID" char(20),in "paccountcode" char(12),in "ptheirref" char(50),in "pperiod" integer,in "pnetcharge" double,
  out "oResult" char(250),out "ocompID" char(20),out "oacccode" char(12),out "otheirref" char(50),out "operiod" integer,out "onetcharge" double ) 
begin
  declare "compname" char(60);
  declare "invgroup" integer;
  select "company"."companyid","period",(select "sum"("round"("unitscharged"*"chargerate",2)) from "temptimesheetline"
      where "temptimesheetid" = "temptimesheet"."temptimesheetid"),
    (if "trim"("isnull"("temptimesheet"."theirref",'')) = '' then "placement"."theirref" else "temptimesheet"."theirref" endif),
    "clientcode",(select "groupinvoice" from "companyaccount" where "companyid" = "getinvoicecompanyid"("clientcode")) into "ocompid",
    "operiod","onetcharge","otheirref","oacccode",
    "invgroup" from "temptimesheet" key join "placement" key join "employment" key join "company"
    where "temptimesheetid" = "pTimesheetID";
  -- check if new acc code create  
  if "paccountcode" <> "oacccode" then
    select "name" into "compname" from "company" where "companyid" = "getinvoicecompanyid"("oacccode");
    call "iqacCreateAccount"('Sales',"oacccode","compname")
  end if;
  -- new invoice needed  
  if "invgroup" = 0 then
    set "oresult" = '1:~New Invoice';
    return
  elseif("invgroup" = 1) and("pcompanyid" <> "ocompid") then
    set "oresult" = '1:~New Invoice';
    return
  elseif("invgroup" = 2) and(("pcompanyid" <> "ocompid") or("isnull"("ptheirref",'') <> "isnull"("otheirref",''))) then
    set "oresult" = '1:~New Invoice';
    return
  elseif("invgroup" = 3) and(("pcompanyid" <> "ocompid") or("pperiod" <> "operiod")) then
    set "oresult" = '1:~New Invoice';
    return
  elseif("invgroup" = 4) and(("pcompanyid" <> "ocompid") or("isnull"("ptheirref",'') <> "isnull"("otheirref",'')) or("pperiod" <> "operiod")) then
    set "oresult" = '1:~New Invoice';
    return
  elseif("invgroup" = 15) and("paccountcode" <> "oacccode") then
    set "oresult" = '1:~New Invoice';
    return
  elseif(("invgroup" = 12) or("invgroup" = 16)) and(("paccountcode" <> "oacccode") or("isnull"("ptheirref",'') <> "isnull"("otheirref",''))) then
    set "oresult" = '1:~New Invoice';
    return
  elseif("pnetcharge" > 0.0) and("onetcharge" < 0.0) then
    set "oresult" = '1:~New Invoice';
    return
  end if;
  set "oresult" = '0:~Continue'
exception
  when others then
    resignal
end
go

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

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

(in pTimesheetID char(20),in pCompanyID char(20),in paccountcode char(12),in ptheirref char(50),in pperiod integer,in pnetcharge double, 
      out oResult char(250),out ocompID char(20),out oacccode char(12),out otheirref char(50),out operiod integer,out onetcharge double)
begin
  declare compname char(60);
  declare invgroup integer;
  select company.companyid,period,(select sum(round(unitscharged*chargerate,2)) from temptimesheetline where
      temptimesheetid = temptimesheet.temptimesheetid),
    (if trim(isnull(temptimesheet.theirref,'')) = '' then placement.theirref else temptimesheet.theirref endif),
    clientcode,(select groupinvoice from companyaccount where companyid = getinvoicecompanyid(clientcode)) into ocompid,
    operiod,onetcharge,otheirref,oacccode,
    invgroup from temptimesheet key join placement key join employment key join company where
    temptimesheetid = pTimesheetID;
  -- check if new acc code create  
  if paccountcode <> oacccode then
    select name into compname from company where companyid = getinvoicecompanyid(oacccode);
    call iqacCreateAccount('Sales',oacccode,compname)
  end if;
  -- new invoice needed  
  if invgroup = 0 then
    set oresult='1:~New Invoice';
    return
  elseif(invgroup = 1) and(pcompanyid <> ocompid) then
    set oresult='1:~New Invoice';
    return
  elseif(invgroup = 2) and((pcompanyid <> ocompid) or(isnull(ptheirref,'') <> isnull(otheirref,''))) then
    set oresult='1:~New Invoice';
    return
  elseif(invgroup = 3) and((pcompanyid <> ocompid) or(pperiod <> operiod)) then
    set oresult='1:~New Invoice';
    return
  elseif(invgroup = 4) and((pcompanyid <> ocompid) or(isnull(ptheirref,'') <> isnull(otheirref,'')) or(pperiod <> operiod)) then
    set oresult='1:~New Invoice';
    return
  elseif(invgroup = 15) and(paccountcode <> oacccode) then
    set oresult='1:~New Invoice';
    return
  elseif((invgroup = 12) or(invgroup = 16)) and((paccountcode <> oacccode) or(isnull(ptheirref,'') <> isnull(otheirref,''))) then
    set oresult='1:~New Invoice';
    return
  elseif(pnetcharge > 0.0) and(onetcharge < 0.0) then
    set oresult='1:~New Invoice';
    return
  end if;
  set oresult='0:~Continue'
exception
  when others then
    resignal
end
}
</code>