pears.iqacSelfBillDocumentStart

CREATE PROCEDURE "pears"."iqacSelfBillDocumentStart"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pTheirRef" CHAR(50),IN "pAccountCode" CHAR(12),IN "pTimesheetID" CHAR(20),IN "pDocDate" DATE,OUT "oResult" CHAR(250),OUT "odocid" CHAR(8) ) 
BEGIN
  DECLARE "dbid" CHAR(2);
  DECLARE "docno" INTEGER;
  DECLARE "i" INTEGER;
  DECLARE "s" CHAR(12);
  DECLARE "ant" CHAR(12);
  DECLARE "anp" CHAR(12);
  DECLARE "antemp" CHAR(12);
  DECLARE "sourref" CHAR(12);
  -- lookups
  DECLARE "TemplateID" CHAR(12);
  DECLARE "email" CHAR(100);
  DECLARE "AttnOf" CHAR(250);
  DECLARE "invaddr" long VARCHAR;
  DECLARE "ourrefprefix" CHAR(12);
  DECLARE "AttnOfType" CHAR(1);
  UPDATE "iqacparams" SET "lastdocumentnumber" = "isnull"("lastdocumentnumber",0)+1;
  SELECT "dbid","lastdocumentnumber" INTO "dbid","docno" FROM "iqacparams";
  IF("isnull"("dbid",'') = '') THEN
    SET "oresult" = '99:~Missing dbid';
    RETURN
  END IF;
  --use docno for pk
  SET "odocid" = "dbid" || "base36"("docno",6);
  SET "i" = 0;
  SELECT "formataddress"("name","addr1","addr2","addr3","town","county","country","postcode",0,'B'),"attentionof",
    "isnull"("documenttemplateid",'PurchInvoice'),
    "email"
    INTO "invaddr","AttnOf",
    "TemplateID",
    "email" FROM "iqacaccount" AS "a" KEY JOIN "iqacaccountsettings"
    WHERE "a"."accountcode" = "paccountcode" AND "a"."ledgerid" = 'Purchase';
  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" = 'SaveDoc' THEN
    UPDATE "iqacdocumenttemplate" SET "autonumbernext" = "isnull"("autonumbernext",0)+1 WHERE "documenttemplateid" = "antemp";
    SET "sourref" = "anp" || (SELECT "autonumbernext"-1 FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "antemp")
  END IF;
  IF "ant" = 'PostDoc' THEN
    SET "sourref" = "ourrefprefix"
  END IF;
  SELECT "isnull"("daystopay",0) INTO "i" FROM "iqacdocumenttemplate" WHERE "documenttemplateid" = "TemplateID";
  INSERT INTO "IQacdocument"( "DocumentID","DocumentTemplateID","OurRef","LedgerID","AccountCode",
    "XRefID","XRef","Description","AccountingDate","DocumentDate","DueDate",
    "TheirRef","AttentionOf","DocumentClass","DocumentType","InvoiceEmail","InvoiceAddress","PrintBatch" ) VALUES
    ( "odocid","templateID","sourref",'Purchase',"paccountcode","pTimesheetID",'T','Timesheet',"pDocDate","pDocDate",
    "dateadd"("day","i","pDocDate"),"pTheirRef","AttnOf",'Invoice','Invoice',"email","invaddr",1 ) ;
  -- change DocumentType to Credit Note after know whether total +ve or -ve
  IF @@rowcount = 0 THEN
    SET "oresult" = '1:~Insert failed';
    RETURN
  END IF;
  SET "oresult" = '0:~Success'
exception
  WHEN others THEN
    resignal
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."iqacSelfBillDocumentStart" IS 
{CREATE PROCEDURE iqacSelfBillDocumentStart 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN pTheirRef CHAR(50),IN pAccountCode CHAR(12), IN pTimesheetID CHAR(20),IN pDocDate DATE,OUT oResult CHAR(250),OUT odocid CHAR(8) ) 
BEGIN
  DECLARE dbid CHAR(2);
  DECLARE docno INTEGER;
  DECLARE i INTEGER;
  DECLARE s CHAR(12);
  DECLARE ant CHAR(12);
  DECLARE anp CHAR(12);
  DECLARE antemp CHAR(12);
  DECLARE sourref CHAR(12);
  -- lookups
  DECLARE TemplateID CHAR(12);
  DECLARE email CHAR(100);
  DECLARE AttnOf CHAR(250);
  DECLARE invaddr long VARCHAR;
  DECLARE ourrefprefix CHAR(12);
  DECLARE AttnOfType CHAR(1);
  UPDATE iqacparams SET lastdocumentnumber = isnull(lastdocumentnumber,0)+1;
  SELECT dbid,lastdocumentnumber INTO dbid,docno FROM iqacparams;
  IF(isnull(dbid,'') = '') THEN
    SET oresult = '99:~Missing dbid';
    RETURN
  END IF;
  --use docno for pk
  SET odocid = dbid || base36(docno,6);
  SET i = 0;
  SELECT formataddress(name,addr1,addr2,addr3,town,county,country,postcode,0,'B'),attentionof,
    isnull(documenttemplateid,'PurchInvoice'),
    email
    INTO invaddr,AttnOf,
    TemplateID,
    email FROM iqacaccount AS a KEY JOIN iqacaccountsettings
    WHERE a.accountcode = paccountcode AND a.ledgerid = 'Purchase';
  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 = 'SaveDoc' THEN
    UPDATE iqacdocumenttemplate SET autonumbernext = isnull(autonumbernext,0)+1 WHERE documenttemplateid = antemp;
    SET sourref = anp || (SELECT autonumbernext-1 FROM iqacdocumenttemplate WHERE documenttemplateid = antemp)
  END IF;
  IF ant = 'PostDoc' THEN
    SET sourref = ourrefprefix
  END IF;
  SELECT isnull(daystopay,0) INTO i FROM iqacdocumenttemplate WHERE documenttemplateid = TemplateID;
  INSERT INTO IQacdocument( DocumentID,DocumentTemplateID,OurRef,LedgerID,AccountCode,
    XRefID,XRef,Description,AccountingDate,DocumentDate,DueDate,
    TheirRef,AttentionOf,DocumentClass,DocumentType,InvoiceEmail,InvoiceAddress,PrintBatch ) 
    VALUES( odocid,templateID,sourref,'Purchase',paccountcode,pTimesheetID,'T','Timesheet',pDocDate,pDocDate,
    dateadd(DAY,i,pDocDate),pTheirRef,AttnOf,'Invoice','Invoice',email,invaddr,1 ) ;
  -- change DocumentType to Credit Note after know whether total +ve or -ve
  IF @@rowcount = 0 THEN
    SET oresult = '1:~Insert failed';
    RETURN
  END IF;
  SET oresult = '0:~Success'
exception
  WHEN others THEN
    resignal
END
}