pears.AccordUserPayrollRuns

CREATE PROCEDURE "pears"."AccordUserPayrollRuns"( 
  IN @EmployeeCodeList CHAR(255),IN @startdate CHAR(10),IN @enddate CHAR(10) ) 
RESULT( "EmployeeCode" CHAR(6),"CompanyCode" CHAR(2),"PaymentDate" CHAR(10),"Period" CHAR(21),"GrossPay" DOUBLE,"Deductions" DOUBLE,
  "NetPay" DOUBLE,"PeriodNumber" CHAR(2),"payslipfullpath" long VARCHAR ) 
BEGIN
  -- user code can replace. this is for shared DB
  SELECT "s"."EmployeeCode" AS "EMPLOYEECODE",
    "s"."CompanyCode" AS "COMPANYCODE","date"("PaymentDate") AS "PAYMENTDATE",
    "string"("dateformat"("PerStartDate",'Dd Mmm yy'),' - ',"dateformat"("PerEndDate",'Dd Mmm yy')) AS "PERIOD",
    "round"("sum"("Amount3"*(IF "Amount3" < 0 THEN 0 ELSE 1 endif)),2) AS "GROSSPAY",
    "round"("GrossPay"-"NetPay",2) AS "DEDUCTIONS","round"("sum"("Amount3"),2) AS "NETPAY",
    "s"."periodnumber" AS "PERIODNUMBER","f"."payslipfullpath"
    FROM "accord"."PAStatistics" AS "s"
      JOIN "accord"."papayslipfiles" AS "f" ON("s"."CompanyCode" = "f"."CompanyCode" AND "s"."EmployeeCode" = "f"."EmployeeCode" AND "s"."PaymentDate" = "f"."taxperiodenddate")
    WHERE "s"."PaymentDate" BETWEEN "isnull"(@startdate,"dateadd"("year",-1,CURRENT DATE)) AND "isnull"(@enddate,CURRENT DATE)
    AND "s"."employeecode" = any(SELECT "row_value" FROM "sa_split_list"(@EmployeeCodeList))
    GROUP BY "EmployeeCode","CompanyCode","PaymentDate","PerStartDate","PerEndDate","PeriodNumber","payslipfullpath"
END