====== pears.InvoiceCancel ======
<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"."InvoiceCancel"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "DocID" char(8),in "Recreate" smallint,in "DocDate" date ) 
begin atomic
  declare "NewDocID" char(8);
  declare "num" integer;
  declare "CanReason" char(20);
  declare "SCanReason" char(1);
  declare "TSID" char(20);
  declare "ContraTSID" char(20);
  declare "NextTSID" char(20);
  declare "invaddr" long varchar;
  declare "invemail" char(250);
  declare "invprefix" char(10);
  update "iqacparams" set "lastdocumentnumber" = "isnull"("lastdocumentnumber",0)+1;
  select "dbid","lastdocumentnumber" into "NewDocID","num" from "iqacparams";
  select top 1 "invoiceaddress","invoiceemail","invoiceprefix" into "invaddr","invemail","invprefix" from "iqacdocument" where "documentid" = "docid";
  set "NewDocID" = "NewDocID"+"base36"("num",6);
  select "timesheetcancelreasonid" into "CanReason" from "timesheetcancelreason" where "name" = (select "Value" from "WPKGlobalSwitchSetting" where "wpkglobalswitchid" = 'INVTSCANCELREASON');
  select "Value" into "SCanReason" from "WPKGlobalSwitchSetting" where "wpkglobalswitchid" = 'TSSHIFTCANCELREASON';
  insert into "iqacDocument"( "DocumentID","AccountCode","AccountingDate","Adhoc","Amount","AttentionOf","CurrencyCode","Description","DocumentClass",
    "DocumentDate","DocumentTemplateID","DocumentType","DueDate","EmailBatch","ExchangeRate","InvoicePrefix","InvoiceAddress","InvoiceEmail","LastLineNumber",
    "LedgerID","OurRef","PaymentMethod","PrintBatch","StoredAsForeignCurrency","TheirRef","TransferBatch","VATAmount",
    "WhenCreated","WhoCreated","XRef","XRef2","XRefID","XRefID2" ) 
    select "NewDocID","AccountCode","DocDate","Adhoc",-"Amount","AttentionOf","CurrencyCode","Description","DocumentClass",
      "DocDate","DocumentTemplateID",'Credit Note',"DateAdd"("day","DateDiff"("day","DocumentDate","DueDate"),"DocDate"),0,"ExchangeRate","InvoicePrefix","InvoiceAddress","InvoiceEmail","LastLineNumber",
      "LedgerID","string"("OurRef",'C'),"PaymentMethod",0,"StoredAsForeignCurrency","TheirRef",null,-"VATAmount",current timestamp,
      "userstaffid","XRef","XRef2","XRefID","XRefID2" from "iqacDocument" where "documentid" = "docid";
  update "iqacDocument" set "invoiceaddress" = "invaddr","invoiceemail" = "invemail","invoiceprefix" = "invprefix" where "documentid" = "newdocid";
  set "TSID" = '1';
  for "timesheetlines" as "tscursor" no scroll cursor for
    select "LineNumber" as "LN","XRefID","xref"
      from "iqacJournal" where "documentid" = "Docid" order by "LN" asc for read only
  do
    if "xref" = 'T' then select "temptimesheetid" into "NextTSID" from "temptimesheetline" where "temptimesheetlineid" = "XrefID"
    end if;
    if "TSID" <> "NextTSID" then
      call "TemptimesheetCancel"("CanReason","NextTSID","SCanReason","Recreate",'INV');
      // get contraid from cancel table
      select "ContraTempTimeSheetID" into "ContraTSID" from "TempTimeSheetCancellation" where "OriginalTempTimesheetID" = "NextTSID";
      // use blank TSID to mean first ts in inv
      if "TSID" is null then
        update "iqacdocument" set "xrefid" = "ContraTSID" where "documentid" = "docid"
      end if;
      set "TSID" = "NextTSID"
    end if;
    insert into "iqacjournal"( "DocumentID","LineNumber","NominalCode","AccountingDate","LedgerID","AccountCode","VATReturn","XRefID","XRef","Description","Amount",
      "JournalClass","GoodsAmount","VATAmount","VATRate","VATCode","PriceEach","Quantity","ProductCode","UnitDescription","XRefDocumentID","XRefLineNumber","CashVATCode","CashVATAmount",
      "CashAmount","CashAccountLine","AutoAllocate","SubAllocation","AllocAmount","AllocVATAmount" ) 
      select "NewDocID","LineNumber","NominalCode",current date,"LedgerID","AccountCode","VATReturn","XRefID","XRef","Description",-"Amount","JournalClass",-"GoodsAmount",
        -"VATAmount","VATRate","VATCode","PriceEach",-"Quantity","ProductCode","UnitDescription","XRefDocumentID","XRefLineNumber","CashVATCode",-"CashVATAmount",-"CashAmount","CashAccountLine",
        "AutoAllocate","SubAllocation",-"AllocAmount",-"AllocVATAmount"
        from "iqacJournal" where "documentid" = "Docid" and "LineNumber" = "LN"
  end for;
  update "iqacjournal"
    set "xrefid"
     = (select "lnew"."temptimesheetlineid" from "temptimesheetline" as "lnew" key join "temptimesheet" as "tnew" join "TempTimeSheetCancellation" as "c" on "tnew"."temptimesheetid" = "c"."ContraTempTimeSheetID"
        join "temptimesheet" as "told" on "told"."temptimesheetid" = "c"."OriginalTempTimesheetID" key join "temptimesheetline" as "lold" where "lold"."linenumber" = "lnew"."linenumber"
      and "lold"."temptimesheetlineid" = "iqacjournal"."xrefid")
    where "xref" in( 'T','F','W','I','M' ) and "documentid" = "NewDocID";
  update "iqacjournal" set "xrefid" = (select "ContraTempTimeSheetID" from "TempTimeSheetCancellation" where "OriginalTempTimesheetID" = "xrefid") where "xref" = 'N' and "documentid" = "NewDocID"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."InvoiceCancel" IS 
{create procedure InvoiceCancel 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

( in DocID char(8),in Recreate smallint, in DocDate date) 
begin atomic
  declare NewDocID char(8);
  declare num integer;
  declare CanReason char(20);
  declare SCanReason char(1);
  declare TSID char(20);
  declare ContraTSID char(20);
  declare NextTSID char(20);
  declare invaddr long varchar;
  declare invemail char(250);
  declare invprefix char(10);
  update iqacparams set lastdocumentnumber = isnull(lastdocumentnumber,0)+1;
  select dbid,lastdocumentnumber into NewDocID,num from iqacparams;
  select top 1 invoiceaddress, invoiceemail, invoiceprefix into invaddr, invemail, invprefix from iqacdocument where documentid = docid;
  set NewDocID = NewDocID+base36(num,6);
  select timesheetcancelreasonid into CanReason from timesheetcancelreason where name = (select Value from WPKGlobalSwitchSetting where wpkglobalswitchid = 'INVTSCANCELREASON');
  select Value into SCanReason from WPKGlobalSwitchSetting where wpkglobalswitchid = 'TSSHIFTCANCELREASON';
  insert into iqacDocument( DocumentID,AccountCode,AccountingDate,Adhoc,Amount,AttentionOf,CurrencyCode,Description,DocumentClass,
    DocumentDate,DocumentTemplateID,DocumentType,DueDate,EmailBatch,ExchangeRate,InvoicePrefix, InvoiceAddress,InvoiceEmail,LastLineNumber,
    LedgerID,OurRef,PaymentMethod,PrintBatch,StoredAsForeignCurrency,TheirRef,TransferBatch,VATAmount,
    WhenCreated,WhoCreated,XRef,XRef2,XRefID,XRefID2 ) 
    select NewDocID,AccountCode,DocDate,Adhoc,-Amount,AttentionOf,CurrencyCode,Description,DocumentClass,
      DocDate,DocumentTemplateID,'Credit Note',DateAdd(day,DateDiff(day,DocumentDate,DueDate),DocDate),0,ExchangeRate,InvoicePrefix,InvoiceAddress,InvoiceEmail,LastLineNumber,
      LedgerID,string(OurRef,'C'),PaymentMethod,0,StoredAsForeignCurrency,TheirRef,null,-VATAmount,current timestamp,
      userstaffid,XRef,XRef2,XRefID,XRefID2 from iqacDocument where documentid = docid;
  update  iqacDocument set invoiceaddress =  invaddr, invoiceemail = invemail, invoiceprefix = invprefix where documentid = newdocid;
  set TSID = '1';
  for timesheetlines as tscursor no scroll cursor for
    select LineNumber as LN,XRefID,xref
      from iqacJournal where documentid = Docid order by LN asc for read only
  do
    if xref = 'T' then select temptimesheetid into NextTSID from temptimesheetline where temptimesheetlineid = XrefID
    end if;
    if TSID <> NextTSID then
            call TemptimesheetCancel(CanReason,NextTSID,SCanReason,Recreate,'INV');
      // get contraid from cancel table
      select ContraTempTimeSheetID into ContraTSID from TempTimeSheetCancellation where OriginalTempTimesheetID = NextTSID;
      // use blank TSID to mean first ts in inv
      if TSID is null then
        update iqacdocument set xrefid = ContraTSID where documentid = docid
      end if;
      set TSID = NextTSID
    end if;
    insert into iqacjournal( DocumentID,LineNumber,NominalCode,AccountingDate,LedgerID,AccountCode,VATReturn,XRefID,XRef,Description,Amount,
      JournalClass,GoodsAmount,VATAmount,VATRate,VATCode,PriceEach,Quantity,ProductCode,UnitDescription,XRefDocumentID,XRefLineNumber,CashVATCode,CashVATAmount,
      CashAmount,CashAccountLine,AutoAllocate,SubAllocation,AllocAmount,AllocVATAmount ) 
      select NewDocID,LineNumber,NominalCode,current date,LedgerID,AccountCode,VATReturn,XRefID,XRef,Description,-Amount,JournalClass,-GoodsAmount,
        -VATAmount,VATRate,VATCode,PriceEach,-Quantity,ProductCode,UnitDescription,XRefDocumentID,XRefLineNumber,CashVATCode,-CashVATAmount,-CashAmount,CashAccountLine,
        AutoAllocate,SubAllocation,-AllocAmount,-AllocVATAmount
        from iqacJournal where documentid = Docid and LineNumber = LN
  end for;
  update iqacjournal
    set xrefid
     = (select lnew.temptimesheetlineid from temptimesheetline as lnew key join temptimesheet as tnew join TempTimeSheetCancellation as c on tnew.temptimesheetid = c.ContraTempTimeSheetID
        join temptimesheet as told on told.temptimesheetid = c.OriginalTempTimesheetID key join temptimesheetline as lold where lold.linenumber = lnew.linenumber
      and lold.temptimesheetlineid = iqacjournal.xrefid)
    where xref in( 'T','F','W','I','M' ) and documentid = NewDocID;
  update iqacjournal set xrefid = (select ContraTempTimeSheetID from TempTimeSheetCancellation where OriginalTempTimesheetID = xrefid) where xref = 'N' and documentid = NewDocID
end
}
</code>