====== pears.iqacSelfBillOneAccount ======
<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"."iqacSelfBillOneAccount"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "paccountcode" char(12),in "psessionid" char(20),in "pdocdate" date,inout "invcount" integer ) 
begin
  declare "grp" integer;
  -- start new inv flags
  declare "PeriodBreak" integer;
  declare "PersonBreak" integer;
  declare "VacBreak" integer;
  declare "CompBreak" integer;
  declare "NewInv" integer;
  -- old new inv values
  declare "OldCharge" double;
  declare "OldPeriod" integer;
  declare "OldPerson" char(20);
  declare "OldVac" char(20);
  declare "OldComp" char(20);
  -- general variables
  declare "CurrDocID" char(8);
  declare "res" integer; -- result of calls
  declare "sres" char(250); -- result of calls
  declare "LastTSID" char(20);
  -- each timesheet values
  declare "tsid" char(20);
  declare "vacid" char(20);
  declare "persid" char(20);
  declare "compid" char(20);
  declare "chge" double;
  declare "per" integer;
  declare "caninv" long varchar;
  -- set up new inv flags
  select "GroupInvoice" into "grp" from "IQacAccountSettings" where "ledgerid" = 'Purchase' and "AccountCode" = "paccountcode";
  select if "grp" in( 1,4,6,8 ) then 1 else 0 endif into "PeriodBreak";
  select if "grp" in( 3,4 ) then 1 else 0 endif into "PersonBreak";
  select if "grp" in( 7,8 ) then 1 else 0 endif into "VacBreak";
  select if "grp" in( 5,6 ) then 1 else 0 endif into "CompBreak";
  -- init 
  set "OldCharge" = 0.0;
  set "OldPeriod" = 0;
  set "OldPerson" = '';
  set "OldVac" = '';
  set "OldComp" = '';
  set "CurrDocID" = '';
  /*
select 'One for each Timesheet' as name,2 as val no order
select 'One for each Temp' as name, 3 order by personid
select 'One for each Temp and Period'  as name,4 order by personid, period
select 'One for each {G_coname}' as name, 5 order by companyid
select 'One for each {G_coname} and Period' as name,6 order by companyid, period
select 'One Bill' as name,0 no order
select 'One Bill for each  Period' as name,1  order by period
select 'One for each {G_VACNAME}' as name,7 order by vacancyid 
select 'One for each {G_VACNAME} and Period' as name,8 order by vacancyid,period
select 'By Timesheet Serial Number' as name,0 as val
select 'By Timesheet Date' as name,1 as val
select 'By {G_CONAME}' as name,2 as val
select 'By Temp Keyname' as name,3 as val
*/
  select first "ID","temptimesheet"."personid","placement"."vacancyid","period","companyid",
    "temptimesheetselfbill"("temptimesheet"."temptimesheetid"),
    (select "sum"("unitspaid"*"payrate") from "temptimesheetline" where "temptimesheetid" = "temptimesheet"."temptimesheetid")
    into "tsid","persid","vacid","per","compid","caninv","chge"
    from "InvoiceValidReportData" join "temptimesheet" on "temptimesheet"."temptimesheetid" = "InvoiceValidReportData"."id"
      key left outer join("placement" key join "employment")
    where "sessionid" = "psessionid" and "processed" = 0 and "accountcode" = "paccountcode"
    order by "sortorder" asc;
  -- cursor over timesheets no need to commit, loop
  while "tsid" is not null loop
    update "InvoiceValidReportData" set "processed" = 1 where "ID" = "tsid" and "sessionid" = "psessionid" and "accountcode" = "paccountcode";
    if "caninv" = '' then
      -- 1 per timesheet
      if "grp" = 2 then set "NewInv" = 1
      else set "NewInv" = 0 end if;
      -- if break start a new inv  charge +ve to -ve  
      if("PeriodBreak" = 1) and("Per" <> "OldPeriod") then set "NewInv" = 1 end if;
      if("PersonBreak" = 1) and("Persid" <> "OldPerson") then set "NewInv" = 1 end if;
      if("VacBreak" = 1) and("Vacid" <> "OldVac") then set "NewInv" = 1 end if;
      if("CompBreak" = 1) and("Compid" <> "OldComp") then set "NewInv" = 1 end if;
      if("OldCharge" = 0.0) or(("OldCharge" > 0.0) and("chge" < 0.0)) then set "NewInv" = 1 end if;
      -- more
      update "temptimesheet" set "suppliercode" = "paccountcode","paid" = 1,"transferbatch" = if "isnull"("transferbatch",0) > 0 then "transferbatch" else-1 endif,"selfbillinvoiceid" = "CurrDocID" where "temptimesheetid" = "tsid";
      if "NewInv" = 1 then
        if "CurrDocID" <> '' then
          call "iqacSelfBillDocumentFinish"("CurrDocID","tsid","pDocDate","paccountcode","res");
          set "invcount" = "invcount"+"res";
          if "res" = 1 then
            insert into "SelfBillInvoicesforPDF"( "documentid","accountcode","sessionid" ) values( "CurrDocID","paccountcode","psessionid" ) 
          end if end if;
        -- start new inv return CurrDocID
        call "iqacSelfBillDocumentStart"('',"paccountcode","tsid","pDocDate","sres","CurrDocID");
        -- this is done again as first will be blank rest wrong
        update "temptimesheet" set "selfbillinvoiceid" = "CurrDocID" where "temptimesheetid" = "tsid"
      end if;
      call "iqacSelfBillOneTimesheet"("tsid","pdocdate","CurrDocID","paccountcode");
      set "OldCharge" = "chge";
      set "OldPeriod" = "per";
      set "OldPerson" = "persid";
      set "OldVac" = "vacid";
      set "OldComp" = "compid";
      set "LastTSID" = "tsid"
    end if;
    set "tsid" = null;
    select first "ID","temptimesheet"."personid","placement"."vacancyid","period","companyid",
      "temptimesheetselfbill"("temptimesheet"."temptimesheetid"),
      (select "sum"("unitspaid"*"payrate") from "temptimesheetline" where "temptimesheetid" = "temptimesheet"."temptimesheetid")
      into "tsid","persid","vacid","per","compid","caninv","chge"
      from "InvoiceValidReportData" join "temptimesheet" on "temptimesheet"."temptimesheetid" = "InvoiceValidReportData"."id"
        key left outer join("placement" key join "employment")
      where "sessionid" = "psessionid" and "processed" = 0 and "accountcode" = "paccountcode"
      order by "sortorder" asc
  end loop;
  -- finish last invoice here set multi curr  populate pdf table
  call "iqacSelfBillDocumentFinish"("CurrDocID","lasttsid","pDocDate","paccountcode","res");
  set "invcount" = "invcount"+"res";
  if("res" = 1) and("CurrDocID" <> '') then
    insert into "SelfBillInvoicesforPDF"( "documentid","accountcode","sessionid" ) values( "CurrDocID","paccountcode","psessionid" ) 
  end if
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."iqacSelfBillOneAccount" IS 
'create procedure iqacSelfBillOneAccount 

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

( in paccountcode char(12),in psessionid char(20),in pdocdate date,inout invcount integer ) 
begin
  declare grp integer;
  -- start new inv flags
  declare PeriodBreak integer;
  declare PersonBreak integer;
  declare VacBreak integer;
  declare CompBreak integer;
  declare NewInv integer;
  -- old new inv values
  declare OldCharge double;
  declare OldPeriod integer;
  declare OldPerson char(20);
  declare OldVac char(20);
  declare OldComp char(20);
  -- general variables
  declare CurrDocID char(8);
  declare res integer; -- result of calls
  declare sres char(250); -- result of calls
  declare LastTSID char(20);
  -- each timesheet values
  declare tsid char(20);
  declare vacid char(20);
  declare persid char(20);
  declare compid char(20);
  declare chge double;
  declare per integer;
  declare caninv long varchar;
  -- set up new inv flags
  select GroupInvoice into grp from IQacAccountSettings where ledgerid = ''Purchase'' and AccountCode = paccountcode;
  select if grp in( 1,4,6,8 ) then 1 else 0 endif into PeriodBreak;
  select if grp in( 3,4 ) then 1 else 0 endif into PersonBreak;
  select if grp in( 7,8 ) then 1 else 0 endif into VacBreak;
  select if grp in( 5,6 ) then 1 else 0 endif into CompBreak;
  -- init 
  set OldCharge = 0.0;
  set OldPeriod = 0;
  set OldPerson = '''';
  set OldVac = '''';
  set OldComp = '''';
  set CurrDocID = '''';
  /*
select ''One for each Timesheet'' as name,2 as val no order
select ''One for each Temp'' as name, 3 order by personid
select ''One for each Temp and Period''  as name,4 order by personid, period
select ''One for each {G_coname}'' as name, 5 order by companyid
select ''One for each {G_coname} and Period'' as name,6 order by companyid, period
select ''One Bill'' as name,0 no order
select ''One Bill for each  Period'' as name,1  order by period
select ''One for each {G_VACNAME}'' as name,7 order by vacancyid 
select ''One for each {G_VACNAME} and Period'' as name,8 order by vacancyid,period
select ''By Timesheet Serial Number'' as name,0 as val
select ''By Timesheet Date'' as name,1 as val
select ''By {G_CONAME}'' as name,2 as val
select ''By Temp Keyname'' as name,3 as val
*/
  select first ID,temptimesheet.personid,placement.vacancyid,period,companyid,
    temptimesheetselfbill(temptimesheet.temptimesheetid),
    (select sum(unitspaid*payrate) from temptimesheetline where temptimesheetid = temptimesheet.temptimesheetid)
    into tsid,persid,vacid,per,compid,caninv,chge
    from InvoiceValidReportData join temptimesheet on temptimesheet.temptimesheetid = InvoiceValidReportData.id
      key left outer join(placement key join employment)
    where sessionid = psessionid and processed = 0 and accountcode = paccountcode
    order by sortorder asc;
  -- cursor over timesheets no need to commit, loop
  while tsid is not null loop
    update InvoiceValidReportData set processed = 1 where ID = tsid and sessionid = psessionid and accountcode = paccountcode;
    if caninv = '''' then
      -- 1 per timesheet
      if grp = 2 then set NewInv = 1
      else set NewInv = 0 end if;
      -- if break start a new inv  charge +ve to -ve  
      if(PeriodBreak = 1) and(Per <> OldPeriod) then set NewInv = 1 end if;
      if(PersonBreak = 1) and(Persid <> OldPerson) then set NewInv = 1 end if;
      if(VacBreak = 1) and(Vacid <> OldVac) then set NewInv = 1 end if;
      if(CompBreak = 1) and(Compid <> OldComp) then set NewInv = 1 end if;
      if(OldCharge = 0.0) or((OldCharge > 0.0) and(chge < 0.0)) then set NewInv = 1 end if;
      -- more
      update temptimesheet set suppliercode = paccountcode,paid = 1,transferbatch =  if isnull(transferbatch,0) > 0 then transferbatch else -1 endif,selfbillinvoiceid = CurrDocID where temptimesheetid = tsid;
      if NewInv = 1 then
        if CurrDocID <> '''' then
          call iqacSelfBillDocumentFinish(CurrDocID,tsid,pDocDate,paccountcode,res);
          set invcount = invcount+res;
          if res = 1 then
            insert into SelfBillInvoicesforPDF( documentid,accountcode,sessionid ) values( CurrDocID,paccountcode,psessionid ) 
          end if
        end if;
        -- start new inv return CurrDocID
        call iqacSelfBillDocumentStart('''',paccountcode,tsid,pDocDate,sres,CurrDocID);
        -- this is done again as first will be blank rest wrong
        update temptimesheet set selfbillinvoiceid = CurrDocID where temptimesheetid = tsid
      end if;
      call iqacSelfBillOneTimesheet(tsid,pdocdate,CurrDocID,paccountcode);
      set OldCharge = chge;
      set OldPeriod = per;
      set OldPerson = persid;
      set OldVac = vacid;
      set OldComp = compid;
      set LastTSID = tsid
    end if;
    set tsid = null;
    select first ID,temptimesheet.personid,placement.vacancyid,period,companyid,
      temptimesheetselfbill(temptimesheet.temptimesheetid),
      (select sum(unitspaid*payrate) from temptimesheetline where temptimesheetid = temptimesheet.temptimesheetid)
      into tsid,persid,vacid,per,compid,caninv,chge
      from InvoiceValidReportData join temptimesheet on temptimesheet.temptimesheetid = InvoiceValidReportData.id
        key left outer join(placement key join employment)
      where sessionid = psessionid and processed = 0 and accountcode = paccountcode
      order by sortorder asc
  end loop;
  -- finish last invoice here set multi curr  populate pdf table
  call iqacSelfBillDocumentFinish(CurrDocID,lasttsid,pDocDate,paccountcode,res);
  set invcount = invcount+res;
  if (res = 1) and (CurrDocID <> '''' ) then
    insert into SelfBillInvoicesforPDF( documentid,accountcode,sessionid ) values( CurrDocID,paccountcode,psessionid ) 
  end if
end'
</code>