pears.iqacSelfBillOneAccount

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'