====== pears.iqacDocumentStart ====== Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace. ===== Original SQL =====

create procedure "pears"."iqacDocumentStart"( 
  /* 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 "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
    return
  end if;
  --use docno for pk
  set "odocid" = "dbid" || "base36"("docno",6);
  set "i" = 0;
  select cast("placementelementvalueV2"("placement"."placementid","temptimesheet"."personid","placement"."vacancyid",'TINVATTNOF') as varchar(250)),
    "isnull"("placement"."documenttemplateid",(select "documenttemplateid" from "vacancy" where "vacancyid" = "placement"."vacancyid"),"invca"."documenttemplateid",'SalesInvoice'),
    "isnull"(cast("placementelementvalueV2"("placement"."placementid","temptimesheet"."personid","placement"."vacancyid",'TINVEMAIL') as varchar(100)),(select "invoiceemail" from "vacancy" where "vacancyid" = "placement"."vacancyid"),"invca"."invoiceemail"),
    "placementelementvalueV2"("placement"."placementid","temptimesheet"."personid","placement"."vacancyid",'TINVOURREFPREFIX') into "AttnOf",
    "TemplateID","email",
    "ourrefprefix" from "temptimesheet" key join "placement" key join "employment" key join "company" join "companyaccount" as "invca" on "invca"."companyid" = "getinvoicecompanyid"("company"."clientcode")
    where "temptimesheetid" = "pTimesheetID";
  if("isnull"("AttnOf",'') = '') or("AttnOf" = 'Accounts Contact') or("AttnOf" = 'Vacancy Contact') then
    select "left"("value",1) into "AttnOfType" from "wpkglobalswitchsetting" where "wpkglobalswitchid" = 'FALLBACKINVCONTACT';
    if "AttnOfType" = 'A' then
      select "person"."name" into "AttnOf" from "temptimesheet" key join "placement" key join "vacancy" key join "employment" key join "company" key join "companyaccount" as "a" join "employment" as "e" on "a"."accountscontact" = "e"."employmentid"
          key join "person" where "temptimesheet"."temptimesheetid" = "pTimesheetID"
    else if "AttnOfType" <> 'A' then
        select "person"."name" into "AttnOf" from "temptimesheet" key join "placement" key join "vacancy" key join "employment" key join "person" where "temptimesheet"."temptimesheetid" = "pTimesheetID"
      end if
    end if end if;
  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" = "ourrefprefix" || "anp" || (select "autonumbernext"-1 from "iqacdocumenttemplate" where "documenttemplateid" = "antemp")
  end if;
  if "ant" = 'PostDoc' then
    set "sourref" = "ourrefprefix"
  end if;
  select "daystopay" 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" ) values
    ( "odocid","templateID","sourref",'Sales',"paccountcode","pTimesheetID",'T','Timesheet',"pDocDate","pDocDate",
    "dateadd"("day","i","pDocDate"),"pTheirRef","AttnOf",'Invoice','Invoice',"email" ) ;
  -- 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"."iqacDocumentStart" IS 
{create procedure iqacDocumentStart 

/* 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 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
    return
  end if;
  --use docno for pk
  set odocid=dbid || base36(docno,6);
  set i=0;
  select cast(placementelementvalueV2(placement.placementid,temptimesheet.personid,placement.vacancyid,'TINVATTNOF') as varchar(250)),
    isnull(placement.documenttemplateid,(select documenttemplateid from vacancy where vacancyid = placement.vacancyid),invca.documenttemplateid,'SalesInvoice'),
    isnull(cast(placementelementvalueV2(placement.placementid,temptimesheet.personid,placement.vacancyid,'TINVEMAIL') as varchar(100)),(select invoiceemail from vacancy where vacancyid = placement.vacancyid),invca.invoiceemail),
    placementelementvalueV2(placement.placementid,temptimesheet.personid,placement.vacancyid,'TINVOURREFPREFIX') into AttnOf,
    TemplateID,email,
    ourrefprefix from temptimesheet key join placement key join employment key join company join companyaccount as invca on invca.companyid = getinvoicecompanyid(company.clientcode) where
    temptimesheetid = pTimesheetID;
  if(isnull(AttnOf,'') = '') or(AttnOf = 'Accounts Contact') or(AttnOf = 'Vacancy Contact') then
    select "left"(value,1) into AttnOfType from wpkglobalswitchsetting where wpkglobalswitchid = 'FALLBACKINVCONTACT';
    if AttnOfType = 'A' then
      select person.name into AttnOf from temptimesheet key join placement key join vacancy key join employment key join company key join companyaccount as a join employment as e on a.accountscontact = e.employmentid 
          key join person where temptimesheet.temptimesheetid = pTimesheetID
    else if AttnOfType <> 'A' then
        select person.name into AttnOf from temptimesheet key join placement key join vacancy key join employment key join person where temptimesheet.temptimesheetid = pTimesheetID
      end if
    end if
  end if;
  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=ourrefprefix || anp || (select autonumbernext-1 from iqacdocumenttemplate where documenttemplateid = antemp)
  end if;
  if ant = 'PostDoc' then
    set sourref=ourrefprefix
  end if;
  select daystopay 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) values( 
    odocid,templateID,sourref,'Sales',paccountcode,pTimesheetID,'T','Timesheet',pDocDate,pDocDate,
    dateadd(day,i,pDocDate),pTheirRef,AttnOf,'Invoice','Invoice',email) ;
  -- 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
}