pears.iqacInvoiceOneTimesheet

CREATE PROCEDURE "pears"."iqacInvoiceOneTimesheet"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "ptsid" CHAR(20),IN "pDocDate" DATE,IN "pcoid" CHAR(20),IN "pacccode" CHAR(12),IN "ptheirref" CHAR(50),IN "pperiod" INTEGER,
  IN "pnetcharge" DOUBLE,IN "pinvcount" INTEGER,IN "puncommittedcount" INTEGER,IN "pdocid" CHAR(8),OUT "oResult" CHAR(250),OUT "ocoid" CHAR(20),OUT "oacccode" CHAR(12),OUT "otheirref" CHAR(50),
  OUT "operiod" INTEGER,OUT "onetcharge" DOUBLE,OUT "oinvcount" INTEGER,OUT "ouncommittedcount" INTEGER,
  OUT "odocid" CHAR(8) DEFAULT NULL ) 
BEGIN
  DECLARE "res" CHAR(250);
  DECLARE "invaddres" INTEGER;
  -- invoice finish checks
  DECLARE "currentcompanyid" CHAR(20);
  DECLARE "currentaccountcode" CHAR(12);
  DECLARE "currenttheirref" CHAR(50);
  DECLARE "currentperiod" INTEGER;
  DECLARE "netcharge" DOUBLE;
  -- company fields 
  DECLARE "erni" INTEGER;
  DECLARE "vate" INTEGER;
  DECLARE "fni" DOUBLE;
  DECLARE "fwtr" DOUBLE;
  -- ts line items
  DECLARE "vrate" DOUBLE;
  DECLARE "nrechargeni" DOUBLE;
  DECLARE "nfixedni" DOUBLE;
  DECLARE "nfixedwtr" DOUBLE;
  DECLARE "nominal2" CHAR(20);
  DECLARE "vcode2" CHAR(3);
  DECLARE "descrip2" long VARCHAR;
  -- ts items
  DECLARE "taxtype" INTEGER;
  DECLARE "holallow" INTEGER;
  CALL "iqacStartNewInvoice"("ptsid","pcoid","pacccode","ptheirref","pperiod","pnetcharge","res","ocoid","oacccode","otheirref","operiod","onetcharge");
  IF "left"("res",1) = '1' THEN
    -- if new inv :- inc invcount, set uncommitted = 0, start new invoice,
    IF "puncommittedcount" > 0 THEN -- here be bugs
      CALL "iqacDocumentFinish"("pdocid","ptsid","pDocDate","pacccode","invaddres")
    ELSE SET "invaddres" = 0
    END IF;
    SET "ouncommittedcount" = 1;
    SET "oinvcount" = "pinvcount"+"invaddres";
    CALL "iqacDocumentStart"("otheirref","oacccode","ptsID","pDocDate","Res","odocid")
  ELSE
    SET "ouncommittedcount" = "puncommittedcount"+1;
    SET "oinvcount" = "pinvcount";
    SET "odocid" = "pdocid"
  END IF;
  SELECT "isnull"("ernioninvoice",0),"isnull"("vatexempt",0),"isnull"("fixedni",0.0),"isnull"("fixedwtr",0.0) INTO "erni","vate","fni","fwtr" FROM "companyaccount" WHERE "companyid" = "ocoid";
  SELECT "isnull"("temptimesheet"."taxmethod","pay_employee"."taxmethod"),"isnull"(CAST("holidaypaystatus" AS VARCHAR(1)),
    "tempholidaycalc"("temptimesheet"."personid","truncnum"("period"/100,0),"remainder"("period",100),-1)) INTO "taxtype","holallow" FROM "temptimesheet" KEY JOIN "person" KEY JOIN "pay_employee" WHERE "temptimesheetid" = "ptsid";
  -- loop over lines
  FOR "timesheetlines" AS "tslcursor" no scroll cursor FOR
    SELECT "l"."linenumber",
      "l"."unitscharged",
      "l"."chargerate",
      "round"(("l"."unitscharged"*"l"."chargerate"),2) AS "charge",
      ("l"."unitspaid"*"l"."payrate") AS "pay",
      "nominalfortimesheetline"("l"."temptimesheetlineid") AS "nominal",
      "descriptionfortimesheetline"("l"."temptimesheetlineid") AS "descrip",
      "vatcodefortimesheetline"("l"."temptimesheetlineid") AS "vcode",
      "l"."temptimesheetlineid",
      "b"."unit",
      "isnull"("b"."niexempt",0) AS "niexempt",
      "isnull"("b"."IncludeInHolidayPay",0) AS "IncludeInHolidayPay"
      FROM "temptimesheetline" AS "l" KEY JOIN "temppayband" AS "b"
      WHERE "l"."temptimesheetid" = "ptsid"
      AND "charge" <> 0.0
      ORDER BY "l"."linenumber" ASC FOR READ ONLY
  do -- invoice a line (pdocid)
    SET "vcode2" = "vcode";
    IF "isnull"("vcode2",'') = '' THEN
      IF "vate" = 1 THEN
        SET "vcode2" = 'X'
      ELSE
        SET "vcode2" = 'S'
      END IF END IF;
    SELECT "vatrate" INTO "vrate" FROM "iqacvatrate" WHERE "vatcode" = "vcode2";
    CALL "iqacCreateNominal"("nominal",'P','Temp timesheet revenue '+"nominal",'Sales','NoAccount',NULL,NULL,"res");
    CALL "iqacPostJournalLine"("odocid","nominal","descrip","vcode2","temptimesheetlineid",'',"chargerate","vrate","unitscharged","pdocdate","unit",'T','InvoiceGoods');
    -- erni recharge per ts line
    IF("erni" = 1) AND("taxtype" = 1) THEN
      SELECT "round"("accordernitimesheet"("ptsid","temptimesheetlineid"),2) INTO "nrechargeni";
      IF "nrechargeni" <> 0.0 THEN
        SELECT "ValuesForInvoiceNIRechargeLine"("ptsid","temptimesheetlineid",'N') INTO "nominal2";
        SELECT "ValuesForInvoiceNIRechargeLine"("ptsid","temptimesheetlineid",'V') INTO "vcode2";
        SELECT "ValuesForInvoiceNIRechargeLine"("ptsid","temptimesheetlineid",'D') INTO "descrip2";
        IF "isnull"("vcode2",'') = '' THEN
          IF "vate" = 1 THEN
            SET "vcode2" = 'X'
          ELSE
            SET "vcode2" = 'S'
          END IF END IF;
        SELECT "vatrate" INTO "vrate" FROM "iqacvatrate" WHERE "vatcode" = "vcode2";
        CALL "iqacCreateNominal"("nominal2",'P','Temp timesheet revenue '+"nominal2",'Sales','NoAccount',NULL,NULL,"res");
        CALL "iqacpostjournalline"("odocid","nominal2","descrip2","vcode2","temptimesheetlineid",'',"nrechargeni","vrate",1,"pdocdate",'','I','InvoiceGoods')
      END IF END IF;
    -- fixed ni
    IF("erni" <> 1) AND("fni" <> 0.0) AND("niexempt" = 0) AND("pay" <> 0.0) AND("Taxtype" = 1) THEN
      SET "nfixedni" = "round"("pay"*"fni"/100,2);
      SELECT "ValuesForInvoiceFixedNILine"("ptsid","temptimesheetlineid",'N') INTO "nominal2";
      SELECT "ValuesForInvoiceFixedNILine"("ptsid","temptimesheetlineid",'V') INTO "vcode2";
      SELECT "ValuesForInvoiceFixedNILine"("ptsid","temptimesheetlineid",'D') INTO "descrip2";
      IF "isnull"("vcode2",'') = '' THEN
        IF "vate" = 1 THEN
          SET "vcode2" = 'X'
        ELSE
          SET "vcode2" = 'S'
        END IF END IF;
      SELECT "vatrate" INTO "vrate" FROM "iqacvatrate" WHERE "vatcode" = "vcode2";
      CALL "iqacCreateNominal"("nominal2",'P','Temp timesheet revenue '+"nominal2",'Sales','NoAccount',NULL,NULL,"res");
      CALL "iqacpostjournalline"("odocid","nominal2","descrip2","vcode2","temptimesheetlineid",'',"nfixedni","vrate",1,"pdocdate",'','F','InvoiceGoods')
    END IF;
    -- fixed wtr
    IF("fwtr" <> 0.0) AND("IncludeInHolidayPay" = 1) AND("pay" <> 0.0) AND("Taxtype" = 1) AND("holallow" = 1) THEN
      SET "nfixedwtr" = "round"("pay"*"fwtr"/100,2);
      SELECT "ValuesForInvoiceFixedWTRLine"("ptsid","temptimesheetlineid",'N') INTO "nominal2";
      SELECT "ValuesForInvoiceFixedWTRLine"("ptsid","temptimesheetlineid",'V') INTO "vcode2";
      SELECT "ValuesForInvoiceFixedWTRLine"("ptsid","temptimesheetlineid",'D') INTO "descrip2";
      IF "isnull"("vcode2",'') = '' THEN
        IF "vate" = 1 THEN
          SET "vcode2" = 'X'
        ELSE
          SET "vcode2" = 'S'
        END IF END IF;
      SELECT "vatrate" INTO "vrate" FROM "iqacvatrate" WHERE "vatcode" = "vcode2";
      CALL "iqacCreateNominal"("nominal2",'P','Temp timesheet revenue '+"nominal2",'Sales','NoAccount',NULL,NULL,"res");
      CALL "iqacpostjournalline"("odocid","nominal2","descrip2","vcode2","ptsid",'',"nfixedwtr","vrate",1,"pdocdate",'','W','InvoiceGoods')
    END IF END FOR;
  -- ni recharge by timesheet          
  IF("erni" = 1) AND("Taxtype" = 1) THEN
    SELECT "round"("accordernitimesheet"("ptsid",''),2) INTO "nrechargeni";
    IF "nrechargeni" <> 0.0 THEN
      SELECT "ValuesForInvoiceNIRechargeLine"("ptsid",'','N') INTO "nominal2";
      SELECT "ValuesForInvoiceNIRechargeLine"("ptsid",'','V') INTO "vcode2";
      SELECT "ValuesForInvoiceNIRechargeLine"("ptsid",'','D') INTO "descrip2";
      IF "isnull"("vcode2",'') = '' THEN
        IF "vate" = 1 THEN
          SET "vcode2" = 'X'
        ELSE
          SET "vcode2" = 'S'
        END IF END IF;
      SELECT "vatrate" INTO "vrate" FROM "iqacvatrate" WHERE "vatcode" = "vcode2";
      CALL "iqacCreateNominal"("nominal2",'P','Temp timesheet revenue '+"nominal2",'Sales','NoAccount',NULL,NULL,"res");
      CALL "iqacpostjournalline"("odocid","nominal2","descrip2","vcode2","temptimesheetlineid",'',"nrechargeni","vrate",1,"pdocdate",'','N','InvoiceGoods')
    END IF END IF;
  UPDATE "temptimesheet" SET "billed" = 1 WHERE "temptimesheetid" = "ptsid"
exception
  WHEN others THEN
    ROLLBACK WORK
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."iqacInvoiceOneTimesheet" IS 
{CREATE PROCEDURE iqacInvoiceOneTimesheet 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN ptsid CHAR(20),IN pDocDate DATE,IN pcoid CHAR(20),IN pacccode CHAR(12),IN ptheirref CHAR(50),IN pperiod INTEGER, 
   IN pnetcharge DOUBLE,IN pinvcount INTEGER,IN puncommittedcount INTEGER,IN pdocid CHAR(8),OUT oResult CHAR(250),OUT ocoid CHAR(20),OUT oacccode CHAR(12),OUT otheirref CHAR(50), 
   OUT operiod INTEGER,OUT onetcharge DOUBLE,OUT oinvcount INTEGER,OUT ouncommittedcount INTEGER,OUT odocid CHAR(8) DEFAULT
NULL)
BEGIN
  DECLARE res CHAR(250);
  DECLARE invaddres INTEGER;
  -- invoice finish checks
  DECLARE currentcompanyid CHAR(20);
  DECLARE currentaccountcode CHAR(12);
  DECLARE currenttheirref CHAR(50);
  DECLARE currentperiod INTEGER;
  DECLARE netcharge DOUBLE;
  -- company fields 
  DECLARE erni INTEGER;
  DECLARE vate INTEGER;
  DECLARE fni DOUBLE;
  DECLARE fwtr DOUBLE;
  -- ts line items
  DECLARE vrate DOUBLE;
  DECLARE nrechargeni DOUBLE;
  DECLARE nfixedni DOUBLE;
  DECLARE nfixedwtr DOUBLE;
  DECLARE nominal2 CHAR(20);
  DECLARE vcode2 CHAR(3);
  DECLARE descrip2 long VARCHAR;
  -- ts items
  DECLARE taxtype INTEGER;
  DECLARE holallow INTEGER;
  CALL iqacStartNewInvoice(ptsid,pcoid,pacccode,ptheirref,pperiod,pnetcharge,res,ocoid,oacccode,otheirref,operiod,onetcharge);
  IF "left"(res,1) = '1' THEN
    -- if new inv :- inc invcount, set uncommitted = 0, start new invoice,
    IF puncommittedcount > 0 THEN -- here be bugs
      CALL iqacDocumentFinish(pdocid,ptsid,pDocDate,pacccode,invaddres)
    ELSE SET invaddres=0
    END IF;
    SET ouncommittedcount=1;
    SET oinvcount=pinvcount+invaddres;
    CALL iqacDocumentStart(otheirref,oacccode,ptsID,pDocDate,Res,odocid)
  ELSE
    SET ouncommittedcount=puncommittedcount+1;
    SET oinvcount=pinvcount;
    SET odocid=pdocid
  END IF;
  SELECT isnull(ernioninvoice,0),isnull(vatexempt,0),isnull(fixedni,0.0),isnull(fixedwtr,0.0) INTO erni,vate,fni,fwtr FROM companyaccount WHERE companyid = ocoid;
  SELECT isnull(temptimesheet.taxmethod,pay_employee.taxmethod),isnull(CAST(holidaypaystatus AS VARCHAR(1)), 
      tempholidaycalc(temptimesheet.personid,truncnum(period/100,0),remainder(period,100),-1)) INTO taxtype,holallow FROM temptimesheet KEY JOIN person KEY JOIN pay_employee WHERE temptimesheetid = ptsid;
  -- loop over lines
  FOR timesheetlines AS tslcursor no scroll cursor FOR
    SELECT l.linenumber,
      l.unitscharged,
      l.chargerate,
      round((l.unitscharged*l.chargerate),2) AS charge,
      (l.unitspaid*l.payrate) AS pay,
      nominalfortimesheetline(l.temptimesheetlineid) AS nominal,
      descriptionfortimesheetline(l.temptimesheetlineid) AS descrip,
      vatcodefortimesheetline(l.temptimesheetlineid) AS vcode,
      l.temptimesheetlineid,
      b.unit,
      isnull(b.niexempt,0) AS niexempt,
      isnull(b.IncludeInHolidayPay,0) AS IncludeInHolidayPay FROM
      temptimesheetline AS l KEY JOIN temppayband AS b WHERE
      l.temptimesheetid = ptsid AND
      charge <> 0.0 ORDER BY
      l.linenumber ASC FOR READ ONLY do
    -- invoice a line (pdocid)
    SET vcode2=vcode;
    IF isnull(vcode2,'') = '' THEN
      IF vate = 1 THEN
        SET vcode2='X'
      ELSE
        SET vcode2='S'
      END IF
    END IF;
    SELECT vatrate INTO vrate FROM iqacvatrate WHERE vatcode = vcode2;
    CALL iqacCreateNominal(nominal,'P','Temp timesheet revenue '+nominal,'Sales','NoAccount',NULL,NULL,res);
    CALL iqacPostJournalLine(odocid,nominal,descrip,vcode2,temptimesheetlineid,'',chargerate,vrate,unitscharged,pdocdate,unit,'T','InvoiceGoods');
    -- erni recharge per ts line
    IF(erni = 1) AND(taxtype = 1) THEN
      SELECT round(accordernitimesheet(ptsid,temptimesheetlineid),2) INTO nrechargeni;
      IF nrechargeni <> 0.0 THEN
        SELECT ValuesForInvoiceNIRechargeLine(ptsid,temptimesheetlineid,'N') INTO nominal2;
        SELECT ValuesForInvoiceNIRechargeLine(ptsid,temptimesheetlineid,'V') INTO vcode2;
        SELECT ValuesForInvoiceNIRechargeLine(ptsid,temptimesheetlineid,'D') INTO descrip2;
        IF isnull(vcode2,'') = '' THEN
          IF vate = 1 THEN
            SET vcode2='X'
          ELSE
            SET vcode2='S'
          END IF
        END IF;
        SELECT vatrate INTO vrate FROM iqacvatrate WHERE vatcode = vcode2;
        CALL iqacCreateNominal(nominal2,'P','Temp timesheet revenue '+nominal2,'Sales','NoAccount',NULL,NULL,res);
        CALL iqacpostjournalline(odocid,nominal2,descrip2,vcode2,temptimesheetlineid,'',nrechargeni,vrate,1,pdocdate,'','I','InvoiceGoods')
      END IF
    END IF;
    -- fixed ni
    IF(erni <> 1) AND(fni <> 0.0) AND(niexempt = 0) AND(pay <> 0.0) AND(Taxtype = 1) THEN
      SET nfixedni=round(pay*fni/100,2);
      SELECT ValuesForInvoiceFixedNILine(ptsid,temptimesheetlineid,'N') INTO nominal2;
      SELECT ValuesForInvoiceFixedNILine(ptsid,temptimesheetlineid,'V') INTO vcode2;
      SELECT ValuesForInvoiceFixedNILine(ptsid,temptimesheetlineid,'D') INTO descrip2;
      IF isnull(vcode2,'') = '' THEN
        IF vate = 1 THEN
          SET vcode2='X'
        ELSE
          SET vcode2='S'
        END IF
      END IF;
      SELECT vatrate INTO vrate FROM iqacvatrate WHERE vatcode = vcode2;
      CALL iqacCreateNominal(nominal2,'P','Temp timesheet revenue '+nominal2,'Sales','NoAccount',NULL,NULL,res);
      CALL iqacpostjournalline(odocid,nominal2,descrip2,vcode2,temptimesheetlineid,'',nfixedni,vrate,1,pdocdate,'','F','InvoiceGoods')
    END IF;
    -- fixed wtr
    IF(fwtr <> 0.0) AND(IncludeInHolidayPay = 1) AND(pay <> 0.0) AND(Taxtype = 1) AND(holallow = 1) THEN
      SET nfixedwtr=round(pay*fwtr/100,2);
      SELECT ValuesForInvoiceFixedWTRLine(ptsid,temptimesheetlineid,'N') INTO nominal2;
      SELECT ValuesForInvoiceFixedWTRLine(ptsid,temptimesheetlineid,'V') INTO vcode2;
      SELECT ValuesForInvoiceFixedWTRLine(ptsid,temptimesheetlineid,'D') INTO descrip2;
      IF isnull(vcode2,'') = '' THEN
        IF vate = 1 THEN
          SET vcode2='X'
        ELSE
          SET vcode2='S'
        END IF
      END IF;
      SELECT vatrate INTO vrate FROM iqacvatrate WHERE vatcode = vcode2;
      CALL iqacCreateNominal(nominal2,'P','Temp timesheet revenue '+nominal2,'Sales','NoAccount',NULL,NULL,res);
      CALL iqacpostjournalline(odocid,nominal2,descrip2,vcode2,ptsid,'',nfixedwtr,vrate,1,pdocdate,'','W','InvoiceGoods')
    END IF END FOR;
  -- ni recharge by timesheet          
  IF(erni = 1) AND(Taxtype = 1) THEN
    SELECT round(accordernitimesheet(ptsid,''),2) INTO nrechargeni;
    IF nrechargeni <> 0.0 THEN
      SELECT ValuesForInvoiceNIRechargeLine(ptsid,'','N') INTO nominal2;
      SELECT ValuesForInvoiceNIRechargeLine(ptsid,'','V') INTO vcode2;
      SELECT ValuesForInvoiceNIRechargeLine(ptsid,'','D') INTO descrip2;
      IF isnull(vcode2,'') = '' THEN
        IF vate = 1 THEN
          SET vcode2='X'
        ELSE
          SET vcode2='S'
        END IF
      END IF;
      SELECT vatrate INTO vrate FROM iqacvatrate WHERE vatcode = vcode2;
      CALL iqacCreateNominal(nominal2,'P','Temp timesheet revenue '+nominal2,'Sales','NoAccount',NULL,NULL,res);
      CALL iqacpostjournalline(odocid,nominal2,descrip2,vcode2,temptimesheetlineid,'',nrechargeni,vrate,1,pdocdate,'','N','InvoiceGoods')
    END IF
  END IF;
  UPDATE temptimesheet SET billed = 1 WHERE temptimesheetid = ptsid
exception
  WHEN others THEN
    ROLLBACK WORK
END
}