pears.iqacDocumentFinish [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.iqacDocumentFinish ======
<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"."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
}
</code>