pears.iqacDocumentStart

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
}