Payroll SQL View

(0) Share

Report

Posted on by Community Member

Hi, Do you have any Payroll SQL Views that will display the Employee details, salary, deductions, benefits, posting date with GL distributions?

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

NelsonC 282 on at

Like (0)

Report

Emerald,

did you ever find a SQL View for this. I am also looking for this.

Was this reply helpful? Yes No
Pete72 540 on at

Like (0)

Report

Me too if anyone has come up with something. Thanks

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi,

What exactly are you looking for? I mean, what do you want the report to look like? If you could take one employees paycheck and show the information you wanted, that would be helpful. I've got a view that provides the paycodes, benefits and deductions, but it doesn't have anything related to distribution accounts. Do you want all of the tax and FICA deductions too? A pro-forma would help a lot. Chances are, you only want one side of the distributions, what does that look like?

Leslie

Was this reply helpful? Yes No
Pete72 540 on at

Like (0)

Report

Thanks for your reply Leslie.

I'm really looking at the best way to reconcile payroll detail with the G/L as opposed to a form of report. The G/L shows the employee name and amount but I'm trying to tie this to their respective checks which includes paycode information. I don't see a transaction table that stores both the paycode and the G/L account so the closest thing I can see is the Payroll posting accounts but if these are modified then my historical data wouldn't be accurate.

What is the best way of reconciling payroll transactions to the General Ledger?

Was this reply helpful? Yes No
Suggested answer

Kumar_Sat 1,282 on at

Like (0)

Report

SELECT comp.INTERID
,a.PEmployeeID
,a.PCutoffDate
,a.PEIDeducted18
,a.PCPPDeducted16
,a.PEmploymentIncome14
,a.PStartDate
,c.PPaychqNum
,c.PChequeAmount
,a.PIncomeTaxDeducted22
,d.PDescription
,d.PIncomeCode
,d.PRate
,d.PPayrollCodeType
,d.PLineTotal
,d.PYTDAmount
,d.PMatchingOvertimePayCode
,d.PUnits
,b.PFirstName
,b.PLastName
,b.PAddress1
,b.PAddress2
,b.PCity
,b.PProvince
,b.PPostalCode
,c.PChequeDate
FROM DYNAMICS..SY01500 comp
INNER JOIN CPY30150 a
ON comp.INTERID = DB_NAME()
INNER JOIN CPY10100 b
ON a.PEmployeeID = b.PEmployeeID
INNER JOIN CPY30110 c
ON a.PEmployeeID = c.PEmployeeID
AND a.BACHNUMB = c.BACHNUMB
LEFT OUTER JOIN CPY30161 d
ON a.BACHNUMB = d.BACHNUMB
AND a.PEmployeeID=d.PEmployeeID
LEFT OUTER JOIN CPY10101 e
ON a.PEmployeeID = e.PEmployeeID
WHERE d.PDescription NOT LIKE '%offset%'
--AND a.PEmployeeID = @EmployeeNumber
--AND c.PPaychqNum = @PaymentNumber
ORDER BY d.PEmployeeID

You have to add the GL distribution as a left outer join and then you should be fine

Was this reply helpful? Yes No
Kumar_Sat 1,282 on at

Like (0)

Report

Also, this is only for the work tables, you might have to find the historical ones by yourself or you may union them if you want both. But I wonder, why someone would need distribution for a payroll transaction. I mean one payroll transaction could have 10 distribution lines, so what kind of purpose it would be solving if you see 10 lines per employee in any report, will not it look weird.

Was this reply helpful? Yes No
Kumar_Sat 1,282 on at

Like (0)

Report

Distribution once posted are stored in CPY30231 which you can filter by batch id, employee ID and posting date.

Was this reply helpful? Yes No
Pete72 540 on at

Like (0)

Report

Hi Leslie,

Would it be possible to send me the sql statement you mention that has the pay, benefits and deductions. I might be able to leverage off that to determine the distribution accounts.

Thanks

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi Pete,

I will be happy to sent the statement. Send your email address to me at leslievail@earthlink.net

Again, the query I created doesn't look at transactions, it's purely looking at setups. What it might help you see is how to do a union among tables that have very different layouts.

Kind regards,

Leslie

Was this reply helpful? Yes No
Deb@gpsupport 112 on at

Like (0)

Report

I don't need one with GL distributions, but I do need one that includes FED/FICA/MED withholdings for an a file to send to a payroll service for a GP company moving off GP Payroll. I still cannot understand why this view doesn't already exist in GP.

Was this reply helpful? Yes No