Share
ReportHi, 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
You’re offline. This is a read only version of the page.
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
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
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.
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
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?
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (