﻿<?xml version="1.0"?>
<WageEasyGrid>
  <Primary>1</Primary>
  <Title>Roubler Employee Export</Title>
  <Grid>
    <AutoWidth>0</AutoWidth>
  </Grid>
  <Column FieldName="EmployeeNo">
    <Caption>EmployeeNo</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="LastName">
    <Caption>LastName</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="FirstName">
    <Caption>FirstName</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="SecondName">
    <Caption>SecondName</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="Gender">
    <Caption>Gender</Caption>
    <Width>50</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="Birthday">
    <Caption>Birthday</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="EmploymentCategory">
    <Caption>EmploymentCategory</Caption>
    <Width>50</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="EmploymentDate">
    <Caption>EmploymentDate</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="TerminateDate">
    <Caption>TerminateDate</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="Association">
    <Caption>Association</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="PayRateName">
    <Caption>PayRateName</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="PayRateAmount">
    <Caption>PayRateAmount</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="EmployeeSalaried">
    <Caption>EmployeeSalaried</Caption>
    <Width>50</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="StandardPay">
    <Caption>Standard Pay</Caption>
    <Width>50</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="StandardPayHours">
    <Caption>Standard Pay Hours</Caption>
    <Width>50</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="HomeAddress">
    <Caption>HomeAddress</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="HomeCity">
    <Caption>HomeCity</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="HomeState">
    <Caption>HomeState</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="HomePostcode">
    <Caption>HomePostcode</Caption>
    <Width>50</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="HomeCountry">
    <Caption>HomeCountry</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="TaxFileNo">
    <Caption>TaxFileNo</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="TaxTable">
    <Caption>Tax Table</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="SecondaryTax">
    <Caption>Secondary Tax</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="PayMethod">
    <Caption>PayMethod</Caption>
    <Width>80</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="AccountNo">
    <Caption>AccountNo</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="AccountName">
    <Caption>AccountName</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="BSB">
    <Caption>BSB</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="BankName">
    <Caption>BankName</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="EmployeeEmail">
    <Caption>EmployeeEmail</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="PhoneNumber">
    <Caption>PhoneNumber</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="PhoneType">
    <Caption>PhoneType</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="SuperNumber">
    <Caption>SuperNumber</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="SuperName">
    <Caption>SuperName</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="AnnualLeaveBalance">
    <Caption>AnnualLeaveBalance</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="SickLeaveBalance">
    <Caption>SickLeaveBalance</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="PersonalBalance">
    <Caption>PersonalBalance</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="PersonalLeaveBalance">
    <Caption>PersonalLeaveBalance</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="LSLBalance">
    <Caption>LSLBalance</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="ADOBalance">
    <Caption>ADOBalance</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="OtherLeaveBalance">
    <Caption>OtherLeaveBalance</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="YTDGross">
    <Caption>YTDGross</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="YTDTax">
    <Caption>YTDTax</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="YTDSuper">
    <Caption>YTDSuper</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="YTDAllowances">
    <Caption>YTDAllowances</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="Award">
    <Caption>Award</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Column FieldName="Department">
    <Caption>Department</Caption>
    <Width>100</Width>
    <Visible>1</Visible>
  </Column>
  <Param Name="FinYear">
    <Caption>Financial Year</Caption>
    <Description>Financial Year</Description>
    <Type>INTEGER</Type>
    <Default>2010</Default>
  </Param>
  <SQL Check="3567826753"><![CDATA[

SELECT E.EmployeeNo, E.LastName, E.FirstName, E.SecondName, E.Gender, E.Birthday, 
IIf(E.EmploymentCategory = 2,'Casual', IIf(E.EmploymentCategory = 1, 'Part Time', 'Full Time')) AS EmploymentCategory, 
E.EmploymentDate, E.TerminateDate,
IIf(E.Salaried,'Y','N') AS EmployeeSalaried, 
IIf((E.IsStandardPays = 1), 'Y','N') AS StandardPay, 
  E.StandardPayHours * 24 AS StandardPayHours, 
E.HomeAddress, E.HomeCity, E.HomeState, E.HomePostcode, E.HomeCountry,
E.TaxFileNo, 
(SELECT Description FROM Todays_TaxTables AS TTT WHERE TTT.TaxKey = E.TaxKey)AS TaxTable, 
(SELECT Description FROM Todays_TaxTables AS TTT WHERE TTT.TaxKey = E.SecondaryTaxKey)AS SecondaryTax,
LTRIM(LEFT(E.AwardKey, 6)) AS Association, EB.PayMethod, EB.AccountNo, EB.AccountName, EB.BSB, Bank.Name AS BankName, 
(SELECT EmailAddress FROM EmpEmail AS EE WHERE EE.EmployeeId = E.Id AND EE.Default = TRUE) AS EmployeeEmail, 
(SELECT Phone FROM EmpPhone AS EP1 WHERE EP1.EmployeeId = E.Id AND EP1.Default = TRUE) AS PhoneNumber, 
(SELECT Description FROM EmpPhone AS EP2 WHERE EP2.EmployeeId = E.Id AND EP2.Default = TRUE) AS PhoneType, 
(SELECT PR1.Description
FROM EmpDepart AS ED1 INNER JOIN PPE_Payrates AS PR1 ON ED1.PayRateKey = PR1.PayRateKey
WHERE (((ED1.EmployeeId)=E.Id) AND ((ED1.DefaultDepartment)=True))) AS PayRateName,
(SELECT PR1.Amount
FROM EmpDepart AS ED1 INNER JOIN PPE_Payrates AS PR1 ON ED1.PayRateKey = PR1.PayRateKey
WHERE (((ED1.EmployeeId)=E.Id) AND ((ED1.DefaultDepartment)=True))) AS PayRateAmount,
(SELECT TOP 1 MembershipNo FROM EmpSuper AS ES WHERE ES.EmployeeId = E.Id AND ES.ContributionType = 0) AS SuperNumber,
(SELECT TOP 1 SF.FundName
FROM EmpSuper AS ES2 INNER JOIN SuperFund AS SF ON ES2.SuperFundId = SF.Id
WHERE (((ES2.EmployeeId)=E.Id) AND ((ES2.ContributionType)=0))) AS SuperName,
(SELECT IIf(E.TerminateDate IS NULL, SUM(((EL.Accrued+EL.Adjustment)-EL.Taken) * 24), 0) AS AnnualLeaveBalance
FROM EmpLeave AS EL INNER JOIN LeaveType AS LT ON EL.LeaveTypeId = LT.Id
GROUP BY EL.LeaveTypeId, EL.EmployeeId, LT.Type, LT.Id
HAVING (((LT.Type)='ANNUAL') AND ((EL.EmployeeId)=E.Id))) AS AnnualLeaveBalance,
(SELECT IIf(E.TerminateDate IS NULL, SUM(((EL.Accrued+EL.Adjustment)-EL.Taken) * 24), 0) AS SickLeaveBalance
FROM EmpLeave AS EL INNER JOIN LeaveType AS LT ON EL.LeaveTypeId = LT.Id
GROUP BY EL.LeaveTypeId, EL.EmployeeId, LT.Type, LT.Id
HAVING (((LT.Type)='SICK') AND ((EL.EmployeeId)=E.Id))) AS SickLeaveBalance,
(SELECT IIf(E.TerminateDate IS NULL, SUM(((EL.Accrued+EL.Adjustment)-EL.Taken) * 24), 0) AS LSLBalance
FROM EmpLeave AS EL INNER JOIN LeaveType AS LT ON EL.LeaveTypeId = LT.Id
GROUP BY EL.LeaveTypeId, EL.EmployeeId, LT.Type, LT.Id
HAVING (((LT.Type)='LONG SERVICE') AND ((EL.EmployeeId)=E.Id))) AS LSLBalance,
(SELECT IIf(E.TerminateDate IS NULL, SUM(((EL.Accrued+EL.Adjustment)-EL.Taken) * 24), 0) AS ADOBalance
FROM EmpLeave AS EL INNER JOIN LeaveType AS LT ON EL.LeaveTypeId = LT.Id
GROUP BY EL.LeaveTypeId, EL.EmployeeId, LT.Type, LT.Id
HAVING (((LT.Type)='ADO') AND ((EL.EmployeeId)=E.Id))) AS ADOBalance,
(SELECT IIf(E.TerminateDate IS NULL, SUM(((EL.Accrued+EL.Adjustment)-EL.Taken) * 24), 0) AS OtherLeaveBalance
FROM EmpLeave AS EL INNER JOIN LeaveType AS LT ON EL.LeaveTypeId = LT.Id
GROUP BY EL.LeaveTypeId, EL.EmployeeId, LT.Type, LT.Id
HAVING (((LT.Type)='OTHER') AND ((EL.EmployeeId)=E.Id))) AS OtherLeaveBalance,
(SELECT Gross FROM EmpGpCert AS EGC WHERE EGC.EmployeeId = E.Id AND EGC.FinancialYear = :FinYear) AS YTDGross,
(SELECT Tax FROM EmpGpCert AS EGC WHERE EGC.EmployeeId = E.Id AND EGC.FinancialYear = :FinYear) AS YTDTax,
(SELECT SUM(EGCS.EmployerSuper) AS SumOfEmployerSuper
FROM EmpGpCert AS EGC INNER JOIN EmpGpCertSuper AS EGCS ON EGC.Id = EGCS.EmpGpCertId
GROUP BY EGC.EmployeeId, EGC.FinancialYear
HAVING (((EGC.EmployeeId)=E.Id) AND ((EGC.FinancialYear) = :FinYear))) AS YTDSuper,
(SELECT Sum(EGCA.AccruedAmount) AS SumOfAccruedAmount
FROM EmpGpCert AS EGC INNER JOIN EmpGpCertAllow AS EGCA ON EGC.Id = EGCA.EmpGpCertId
GROUP BY EGC.EmployeeId, EGC.FinancialYear, EGCA.OnGroupCert
HAVING (((EGC.EmployeeId)=E.Id) AND ((EGC.FinancialYear) = :FinYear) AND ((EGCA.OnGroupCert)=True))) AS YTDAllowances,
(SELECT TOP 1 A.Description FROM Todays_Awards AS A WHERE (((A.AwardKey)=E.AwardKey)) ORDER BY A.ID DESC) AS Award,
(SELECT D.Name FROM Department AS D INNER JOIN EmpDepart AS ED ON D.Id = ED.DepartmentId
WHERE (((ED.EmployeeId)=E.Id) AND ((ED.DefaultDepartment)=True))) AS Department
FROM (Bank RIGHT JOIN EmpBank AS EB  ON Bank.Id = EB.BankId) RIGHT JOIN Employee AS E ON EB.EmployeeId = E.Id

WHERE (((EB.Default)=True))

ORDER BY E.LastName, E.FirstName;

]]>
  </SQL>
</WageEasyGrid>