pears.iqacSelfBillDocumentStart [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.iqacSelfBillDocumentStart ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
}
</code>