Cacher is the code snippet organizer for pro developers

Payroll Inquiry Custom Formulas [ New ]
Options

‎06-05-2015 01:57 PM

Custom Formulas

 

    Payroll Check Summary Inquiry, Add State SUI tax amount on it

SELECT

[Employer Amount] =  SUM(pctAmount)

FROM AxPayCheckTax

   LEFT JOIN AxTax         ON pctTax      = taxKey

   LEFT JOIN AxPayCheck    ON pctPayCheck = pcKey

   LEFT JOIN AxPayroll     ON pcPayroll   = prKey

   LEFT JOIN AxGLAccount   ON glaKey      = taxLiabilityAccount

   LEFT JOIN AxVEC         ON vecKey      = pcEmployee

   LEFT JOIN AxEntity      ON eKey        = vecEntity

WHERE

                taxIsEmployerTax = 1

AND

                taxType = 7

AND

                prPayDate BETWEEN '01/01/1900' AND '12/31/2078'

AND

        pcKey = [Pay Check Key]

GROUP BY pctPayCheck

 

    Payroll Check Summary Inquiry, Add State SUI taxable amount

SELECT

      Taxable = ISNULL(SUM(pctTaxable     ),0)

FROM

   AxPayCheck

         LEFT JOIN AxPayroll              ON pcPayroll      = prKey     

         LEFT JOIN AxPayCheckTax          ON pcKey             = pctPayCheck

         LEFT JOIN AxTax                        ON pctTax      = taxKey   

         LEFT JOIN AxVEC      E                 ON pcEmployee  = E.vecKey

      LEFT JOIN AxVEC   S        ON S.vecKey    = E.vecSupervisor

      LEFT JOIN AxEntity         ON E.vecEntity = eKey

WHERE

   taxKey IS NOT NULL

AND

   taxType = 7

AND

   pcStatus = 3

AND

   prPayDate BETWEEN '01/01/2009' AND '12/30/2009'

AND

   pcKey = [Pay Check Key]

    Total Annual Salary for a company (for hourly and salary employees) for the year on a semi monthly payroll for usage in an Employee Inquiry.   Note: it would be slightly different if they had different pay periods.

SELECT

   SalaryAmount = ISNULL(CAST((CASE WHEN empIsHourly = 1

                       THEN empRegularPayRate * ((SELECT sHoursInSemiMonthlyPayPeriod FROM AxSettings)* 24 /* 24 = Periods per year*/)

                            ELSE empSalary * 24 /* 24 = Periods per year*/

                        END)

                         as decimal(19,2)),0)

FROM Employee

   WHERE empKey = [Employee Key]

 

    Add Hourly/Salary to Paycheck Summary Inquiry

Case WHEN (SELECT empIsHourly FROM Employee WHERE empKey = [Employee Key]) = 1

THEN 'hourly'

ELSE 'salary'

END

 

    Add Employee Type to State Quarterly Inquiry

SELECT etDescription from employee join EmployeeType on empEmployeeType = etKey WHERE empkey = [Employee Key]

 

    Payroll Check Summary Inquiry, Add State Withholding tax amount on it

This will work to bring in all state withholdings (in case there are multiple) on the paycheck:

 

SELECT ISNULL(SUM(pctAmount), 0.0)

FROM AxPayCheckTax

JOIN AxTax ON pctTax = taxKey

WHERE pctPayCheck = [Pay Check Key]

AND taxType = 5

 

Or, this will filter it by state if you need a breakout:

 

SELECT ISNULL(pctAmount, 0.0)

FROM AxPayCheckTax

JOIN AxTax ON pctTax = taxKey

WHERE pctPayCheck = [Pay Check Key]

AND taxType = 5

AND taxState = 37

 

Tax State:

0 = na; 1 = AL; 2 = AK; 3 = AZ; 4 = AR; 5 = CA; 6 = CO; 7 = CT; 8 = DE; 9 = FL; 10 = GA; 11 = HI; 12 = ID; 13 = IL; 14 = IN; 15 = IA; 16 = KS; 17 = KY; 18 = LA; 19 = ME; 20 = MD; 21 = MA; 22 = MI; 23 = MN; 24 = MS; 25 = MO; 26 = MT; 27 = NE; 28 = NV; 29 = NH; 30 = NJ; 31 = NM; 32 = NY; 33 = NC; 34 = ND; 35 = OH; 36 = OK; 37 = OR; 38 = PA; 39 = RI; 40 = SC; 41 = SD; 42 = TN; 43 = TX; 44 = UT; 45 = VT; 46 = VA; 47 = WA; 48 = WV; 49 = WI; 50 = WY; 51 = DC; 52 = PR

 

    Payroll Check Summary Inquiry, Add individual Deduction amount on it 

SELECT ISNULL(pcdAmount, 0.0)

FROM AxPayCheckDeduction

JOIN AxDeduction ON pcdDeduction = dedKey

WHERE pcdPayCheck = [Pay Check Key]

AND dedDescription = 'Section 125'

 

Specify the deduction by substituting the name of the deduction where it says Section 125. The single quotes are needed around the deduction description.  

To Use these Custom Formulas

Copy the desired Formula into the definition of your Inquiry column.