pears.iqacSelfBillDocumentFinish

CREATE PROCEDURE "pears"."iqacSelfBillDocumentFinish"( 
  /* 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
    SELECT "isnull"("upper"("currency"),'') INTO "scurrency" FROM "iqacaccountsettings" WHERE "accountcode" = "paccountcode" AND "ledgerid" = 'Purchase';
    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','Purchase')
  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','Purchase')
  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 "ourrefupdate" = "ourrefprefix" || "anp" || (SELECT "autonumbernext"-1 FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "antemp")
  END IF;
  UPDATE "iqacdocument" SET "amount" = 0-"conttotal","vatamount" = 0-"vattotal","whenauthorised" = "whencreated","whoauthorised" = "whocreated",
    "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 "SelfBillBatchComplete"("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"."iqacSelfBillDocumentFinish" IS 
{CREATE PROCEDURE iqacSelfBillDocumentFinish 
 
/* 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
    SELECT isnull(UPPER(currency),'') INTO scurrency FROM iqacaccountsettings WHERE accountcode = paccountcode AND ledgerid = 'Purchase';
    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','Purchase')
  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','Purchase')
  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 ourrefupdate = ourrefprefix || anp || (SELECT autonumbernext-1 FROM iqacdocumenttemplate WHERE documenttemplateid = antemp)
  END IF;
  UPDATE iqacdocument SET amount = 0-conttotal,vatamount = 0-vattotal,whenauthorised = whencreated,whoauthorised = whocreated,
    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 SelfBillBatchComplete(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
}