pears.NetFileList

CREATE PROCEDURE "pears"."NetFileList"( IN "pWebUserID" CHAR(20),IN "pFileType" CHAR(25) ) 
RESULT( "FileType" CHAR(25),"FilePath" CHAR(250),"FileDate" DATE,"FileDescription" CHAR(100) ) 
BEGIN
  // Dummy place holder FUNCTION
  // Live version will USE pWebUserID TO look up payroll company id AND payroll employee code AND THEN CALL Accord.Accordpayrollruns
  DECLARE LOCAL TEMPORARY TABLE "Xs"(
    "tp" CHAR(25) NULL,
    "pth" CHAR(250) NULL,
    "dt" DATE NULL,
    "descrip" CHAR(100) NULL,) NOT transactional;INSERT INTO "Xs"( "tp","pth","dt","descrip" ) VALUES( 'A','c:\\iqx\\pdfs\\payslip\\payslip1.pdf','2012-1-6','Payslip w/e 6/1/2012' ) ;
  INSERT INTO "Xs"( "tp","pth","dt","descrip" ) VALUES( 'A','c:\\iqx\\pdfs\\payslip\\payslip2.pdf','2012-1-13','Payslip w/e 13/1/2012' ) ;
  INSERT INTO "Xs"( "tp","pth","dt","descrip" ) VALUES( 'C','c:\\temp\\selfbill1.pdf','2012-1-6','Invoice w/e 6/1/2012' ) ;
  INSERT INTO "Xs"( "tp","pth","dt","descrip" ) VALUES( 'C','c:\\temp\\selfbill2.pdf','2012-1-13','Invoice w/e 13/1/2012' ) ;
  INSERT INTO "Xs"( "tp","pth","dt","descrip" ) VALUES( 'B','c:\\temp\\Payslip 2011-2012 Wk-09 05.06.11 001ICG Davies Ryland Dean.pdf','2012-1-13','Thing with complex file name' ) ;
  SELECT "tp","pth","dt","descrip" FROM "Xs" WHERE "tp" = "pFileType" ORDER BY "tp" ASC,"dt" DESC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetFileList" IS 
{CREATE PROCEDURE pears."NetFileList"( IN pWebUserID CHAR(20),IN pFileType CHAR(25) )
RESULT (FileType CHAR(25),FilePath CHAR(250),FileDate DATE,FileDescription CHAR(100) )
BEGIN
 // Dummy place holder FUNCTION
 // Live version will USE pWebUserID TO look up payroll company id AND payroll employee code AND THEN CALL Accord.Accordpayrollruns
 DECLARE LOCAL TEMPORARY TABLE Xs(
   tp CHAR(25),pth CHAR(250), dt DATE, descrip CHAR(100)
   ) NOT transactional;
 INSERT INTO Xs (tp,pth,dt,descrip) VALUES('A','c:\\iqx\\pdfs\\payslip\\payslip1.pdf','2012-1-6','Payslip w/e 6/1/2012');
 INSERT INTO Xs (tp,pth,dt,descrip) VALUES('A','c:\\iqx\\pdfs\\payslip\\payslip2.pdf','2012-1-13','Payslip w/e 13/1/2012');
 INSERT INTO Xs (tp,pth,dt,descrip) VALUES('C','c:\\temp\\selfbill1.pdf','2012-1-6','Invoice w/e 6/1/2012');
 INSERT INTO Xs (tp,pth,dt,descrip) VALUES('C','c:\\temp\\selfbill2.pdf','2012-1-13','Invoice w/e 13/1/2012');
 INSERT INTO Xs (tp,pth,dt,descrip) VALUES('B','c:\\temp\\Payslip 2011-2012 Wk-09 05.06.11 001ICG Davies Ryland Dean.pdf','2012-1-13','Thing with complex file name');
 SELECT tp,pth,dt,descrip FROM Xs WHERE tp=pFileType ORDER BY tp ASC, dt DESC;
END
}