pears.InvoiceCancel

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
}