Please help me figure out why this view is not working???? It is suposed to pull pay and deductions for checks but it is not balancing to pr52 (some right some wrong and can't find what is wrong,,,)


CREATE VIEW dbo.LAW_Pay_Detail
AS
SELECT EMPLOYEE.EMPLOYEE AS EMPLOYEE, EMPLOYEE.FTE_TOTAL AS FTE, EMPLOYEE.LAST_NAME AS LAST_NAME,
EMPLOYEE.EMP_STATUS AS STATUS, EMPLOYEE.DEPARTMENT AS DEPT, EMPLOYEE.DATE_HIRED AS HIRED, EMPLOYEE.PROCESS_LEVEL AS PL,
EMPLOYEE.FIRST_NAME AS FIRST_NAME, PAYMASTR.CHECK_ID AS CHECK_ID, PAYMASTR.CHECK_DATE AS CHECKDATE, '' AS DED_CODE,
0 AS DED_AMOUNT, PRTIME.PAY_SUM_GRP AS PAY_GRP, PRTIME.WAGE_AMOUNT AS WAGE, EMPLOYEE.COMPANY AS COMPANY,
'P' AS TYPE
FROM (prod.dbo.EMPLOYEE EMPLOYEE INNER JOIN
prod.dbo.PAYMASTR PAYMASTR ON (EMPLOYEE.COMPANY = PAYMASTR.COMPANY) AND (EMPLOYEE.EMPLOYEE = PAYMASTR.EMPLOYEE))
INNER JOIN
prod.dbo.PRTIME PRTIME ON ((PAYMASTR.CHECK_ID = PRTIME.CHECK_ID) AND (PAYMASTR.EMPLOYEE = PRTIME.EMPLOYEE)) AND
(PAYMASTR.COMPANY = PRTIME.COMPANY)
WHERE (EMPLOYEE.COMPANY = 1) AND (EMPLOYEE.EMPLOYEE = 10130)
UNION
SELECT EMPLOYEE.EMPLOYEE AS EMPLOYEE, EMPLOYEE.FTE_TOTAL AS FTE, EMPLOYEE.LAST_NAME AS LAST_NAME,
EMPLOYEE.EMP_STATUS AS STATUS, EMPLOYEE.DEPARTMENT AS DEPT, EMPLOYEE.DATE_HIRED AS HIRED, EMPLOYEE.PROCESS_LEVEL AS PL,
EMPLOYEE.FIRST_NAME AS FIRST_NAME, PAYMASTR.CHECK_ID AS CHECK_ID, PAYMASTR.CHECK_DATE AS DED_CHECKDATE,
PAYDEDUCTN.DED_CODE AS DED_CODE, PAYDEDUCTN.DED_AMT DED_AMOUNT, '' AS PAY_GRP, 0 AS WAGE, EMPLOYEE.COMPANY AS COMPANY,
'D' AS TYPE
FROM prod.dbo.EMPLOYEE EMPLOYEE LEFT OUTER JOIN
prod.dbo.PAYMASTR PAYMASTR ON EMPLOYEE.COMPANY = PAYMASTR.COMPANY AND
EMPLOYEE.EMPLOYEE = PAYMASTR.EMPLOYEE LEFT OUTER JOIN
prod.dbo.PAYDEDUCTN PAYDEDUCTN ON PAYMASTR.COMPANY = PAYDEDUCTN.COMPANY AND
PAYMASTR.EMPLOYEE = PAYDEDUCTN.EMPLOYEE AND PAYMASTR.CHECK_ID = PAYDEDUCTN.CHECK_ID
WHERE (EMPLOYEE.COMPANY = 1) AND (EMPLOYEE.EMPLOYEE = 10130)

I dont know the exact problem, but PR52 gets amounts from PREMDEDHST and QUARTWAGE tables if that helps.

Actually that does help... However, what I am trying to do should be simple and it has been simple with every other system I have used.

I just want the total pay and deductions for each check... When I pull the data I get incorrect totals. If I do each selection separately it works but when I run it through a view then it makes mistakes in the totals.

You might be able to use the PRDISTRIB table to make it a little easier. It has both the earnings and deductions.

I don't know if this will help you, but you might want to check the DEDCODE's adjust pay, S=Employee Paid, A=Add to Net or C=Company paid and calculate the ded amount based on the Adjust pay (Subtract, Add or do nothing for "C") PR52 calculates the total deduction based on this flag.

Arvin Ojales

I know you said you wanted the "total" Pays and Deductions per payment, but did you know about PR51 (Pay Stub Inquiry)? It displays itemized Pay and Deductions for each Payment record.

Also, in Lawson-ese, the term "deductions" includes Company-paid and Add-To-Net transactions. hth.