pears.iqacDocumentFinish

CREATE PROCEDURE "pears"."iqacDocumentFinish"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pdocID" CHAR(8),IN "ptsid" CHAR(20),IN "pDocDate" DATE,IN "paccountcode" CHAR(12),OUT "osuccess" INTEGER ) 
BEGIN
  DECLARE "s" CHAR(250);
  DECLARE "multicurr" INTEGER;
  DECLARE "homecurr" INTEGER;
  DECLARE "scurrency" CHAR(3);
  DECLARE "nrate" DOUBLE;
  DECLARE "vattotal" DOUBLE;
  DECLARE "goodstotal" DOUBLE;
  DECLARE "conttotal" DOUBLE;
  DECLARE "vatnom" CHAR(12);
  DECLARE "contnom" CHAR(12);
  DECLARE "ourrefupdate" CHAR(12);
  DECLARE "TemplateID" CHAR(20);
  DECLARE "docno" INTEGER;
  DECLARE "i" INTEGER;
  DECLARE "ant" CHAR(12);
  DECLARE "anp" CHAR(12);
  DECLARE "antemp" CHAR(12);
  DECLARE "sourref" CHAR(12);
  -- multi-currency
  SELECT "isnull"("multicurrency",0),"isnull"("homecurrency",0) INTO "multicurr","homecurr" FROM "params";
  IF "multicurr" <> 0 THEN
    IF "homecurr" <> 0 THEN
      SELECT "isnull"("upper"("company"."currency"),'') INTO "scurrency" FROM "temptimesheet" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "company" WHERE "temptimesheetid" = "ptsid"
    ELSE SELECT "isnull"("upper"("currency"),'') INTO "scurrency" FROM "temptimesheet" WHERE "temptimesheetid" = "ptsid"
    END IF;
    IF("scurrency" <> '') AND("scurrency" <> "homecurrency") THEN
      SELECT "rateperhome" INTO "nrate" FROM "iqaccurrency" WHERE "currencycode" = "scurrency";
      UPDATE "iqacdocument" SET "currencycode" = "scurrency","exchangerate" = "nrate","storedasforeigncurrency" = (IF "homecurr" <> 0 THEN 0 ELSE 1 endif)
        WHERE "documentid" = "pdocid"
    END IF END IF;
  -- VAT and control
  SELECT "sum"("vatamount") INTO "vattotal" FROM "iqacjournal" WHERE "documentid" = "pdocid";
  SELECT "sum"("goodsamount") INTO "goodstotal" FROM "iqacjournal" WHERE "documentid" = "pdocid";
  IF "vattotal" <> 0 THEN
    SELECT "vatnominalcode" INTO "vatnom" FROM "iqacdocumenttemplate" KEY JOIN "iqacdocument" WHERE "documentid" = "pdocid";
    CALL "iqacpostjournalline"("pdocID","vatnom",'','','','',0-"vattotal",0,1,"pDocDate",'','','InvoiceVAT')
  END IF;
  IF "goodstotal" <> 0 THEN
    SELECT FIRST "nominalcode" INTO "contnom" FROM "iqacnominal" WHERE "ledgerrelationship" = 'LedgerControl'
      AND "ledgerid" = (SELECT "controlledgerid" FROM "iqacdocumenttemplate" KEY JOIN "iqacdocument" WHERE "documentid" = "pdocid");
    SET "conttotal" = "round"("goodstotal"+"vattotal",2);
    CALL "iqacpostjournalline"("pdocID","contnom",'','','',"paccountcode","conttotal",0,1,"pDocDate",'','','InvoiceControl')
  END IF;
  SET "ourrefupdate" = '';
  SET "i" = 0;
  SELECT "documenttemplateid" INTO "TemplateID" FROM "iqacdocument" WHERE "documentid" = "pdocid";
  SELECT "autonumbertype","autonumberprefix" INTO "ant","anp" FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "TemplateID";
  SET "s" = "anp";
  SET "antemp" = "TemplateID";
  while("i" <= 10) AND("ant" = 'LinkTemplate') loop
    SET "antemp" = "s";
    SELECT "autonumbertype","autonumberprefix" INTO "ant","anp" FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "s";
    SET "i" = "i"+1;
    SET "s" = "anp"
  END loop;
  IF "ant" = 'PostDoc' THEN
    UPDATE "iqacdocumenttemplate" SET "autonumbernext" = "isnull"("autonumbernext",0)+1 WHERE "documenttemplateid" = "antemp";
    SET "ourrefipdate" = "ourrefprefix" || "anp" || (SELECT "autonumbernext"-1 FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "antemp")
  END IF;
  UPDATE "iqacdocument" SET "amount" = 0-"conttotal","vatamount" = 0-"vattotal","lastlinenumber" = (SELECT "max"("LineNumber") FROM "iqacjournal" WHERE "documentid" = "pdocid"),
    "documenttype" = IF "goodstotal" > 0 THEN 'Invoice' ELSE 'Credit Note' endif,"ourref" = IF "ourrefupdate" = '' THEN "ourref" ELSE "ourref" || "ourrefupdate" endif WHERE "documentid" = "pdocid";
  SELECT "InvBatchComplete"("pdocID") INTO "s";
  IF "s" > '' THEN
    ROLLBACK WORK;
    SET "osuccess" = 0
  ELSE
    SET "osuccess" = 1;
    commit WORK
  END IF
exception
  WHEN others THEN resignal
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."iqacDocumentFinish" IS 
{CREATE PROCEDURE iqacDocumentFinish 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN pdocID CHAR(8),IN ptsid CHAR(20),IN pDocDate DATE,IN paccountcode CHAR(12),OUT osuccess INTEGER)
BEGIN
  DECLARE s CHAR(250);
  DECLARE multicurr INTEGER;
  DECLARE homecurr INTEGER;
  DECLARE scurrency CHAR(3);
  DECLARE nrate DOUBLE;
  DECLARE vattotal DOUBLE;
  DECLARE goodstotal DOUBLE;
  DECLARE conttotal DOUBLE;
  DECLARE vatnom CHAR(12);
  DECLARE contnom CHAR(12);
  DECLARE ourrefupdate CHAR(12);
  DECLARE TemplateID CHAR(20);
  DECLARE docno INTEGER;
  DECLARE i INTEGER;
  DECLARE ant CHAR(12);
  DECLARE anp CHAR(12);
  DECLARE antemp CHAR(12);
  DECLARE sourref CHAR(12);
  -- multi-currency
  SELECT isnull(multicurrency,0),isnull(homecurrency,0) INTO multicurr,homecurr FROM params;
  IF multicurr <> 0 THEN
    IF homecurr <> 0 THEN
      SELECT isnull(UPPER(company.currency),'') INTO scurrency FROM temptimesheet KEY JOIN placement KEY JOIN employment KEY JOIN company WHERE temptimesheetid = ptsid
    ELSE SELECT isnull(UPPER(currency),'') INTO scurrency FROM temptimesheet WHERE temptimesheetid = ptsid
    END IF;
    IF(scurrency <> '') AND(scurrency <> homecurrency) THEN
      SELECT rateperhome INTO nrate FROM iqaccurrency WHERE currencycode = scurrency;
      UPDATE iqacdocument SET currencycode = scurrency,exchangerate = nrate,storedasforeigncurrency = (IF homecurr <> 0 THEN 0 ELSE 1 endif) WHERE
        documentid = pdocid
    END IF
  END IF;
  -- VAT and control
  SELECT SUM(vatamount) INTO vattotal FROM iqacjournal WHERE documentid = pdocid;
  SELECT SUM(goodsamount) INTO goodstotal FROM iqacjournal WHERE documentid = pdocid;
  IF vattotal <> 0 THEN
    SELECT vatnominalcode INTO vatnom FROM iqacdocumenttemplate KEY JOIN iqacdocument WHERE documentid = pdocid;
    CALL iqacpostjournalline(pdocID,vatnom,'','','','',0-vattotal,0,1,pDocDate,'','','InvoiceVAT')
  END IF;
  IF goodstotal <> 0 THEN
    SELECT FIRST nominalcode INTO contnom FROM iqacnominal WHERE ledgerrelationship = 'LedgerControl' AND
      ledgerid = (SELECT controlledgerid FROM iqacdocumenttemplate KEY JOIN iqacdocument WHERE documentid = pdocid);
    SET conttotal=round(goodstotal+vattotal,2);
    CALL iqacpostjournalline(pdocID,contnom,'','','',paccountcode,conttotal,0,1,pDocDate,'','','InvoiceControl')
  END IF;
  SET ourrefupdate='';
  SET i=0;
  SELECT documenttemplateid INTO TemplateID FROM iqacdocument WHERE documentid = pdocid;
  SELECT autonumbertype,autonumberprefix INTO ant,anp FROM iqacdocumenttemplate WHERE documenttemplateid = TemplateID;
  SET s=anp;
  SET antemp=TemplateID;
  while(i <= 10) AND(ant = 'LinkTemplate') loop
    SET antemp=s;
    SELECT autonumbertype,autonumberprefix INTO ant,anp FROM iqacdocumenttemplate WHERE documenttemplateid = s;
    SET i=i+1;
    SET s=anp
  END loop;
  IF ant = 'PostDoc' THEN
    UPDATE iqacdocumenttemplate SET autonumbernext = isnull(autonumbernext,0)+1 WHERE documenttemplateid = antemp;
    SET ourrefipdate=ourrefprefix || anp || (SELECT autonumbernext-1 FROM iqacdocumenttemplate WHERE documenttemplateid = antemp)
  END IF;
  UPDATE iqacdocument SET amount = 0-conttotal,vatamount = 0-vattotal,lastlinenumber = (SELECT MAX(LineNumber) FROM iqacjournal WHERE documentid = pdocid),
    documenttype = IF goodstotal > 0 THEN 'Invoice' ELSE 'Credit Note' endif,ourref = IF ourrefupdate = '' THEN ourref ELSE ourref || ourrefupdate endif WHERE documentid = pdocid;
  SELECT InvBatchComplete(pdocID) INTO s;
  IF s > '' THEN
    ROLLBACK WORK;
    SET osuccess=0
  ELSE
    SET osuccess=1;
    commit WORK
  END IF
exception
  WHEN others THEN
    resignal
END
}