pears.iqacStartNewInvoice

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
}