pears.iqacCreateInvoices

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
}