====== pears.AccordUserPayrollRuns ======
<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"."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
</code>