• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Human Capital Management Lawson S3 HR/Payroll/Benefits

Traverse Data from paydeductn to glchartdtl

 3 Replies
 0 Subscribed to this topic
 68 Subscribed to this forum
Sort:
Author
Messages
Jeff
Advanced Member
Posts: 30
Advanced Member
3/29/2013 1:43 PM
Via SQL I need to traverse payroll deduction data in paydeductn to glchartdtl.  Has anyone done this?  This my latest attempt to join this data with no results, where per_end_date is a parameter into the query.

       AND employee.employee = paydeductn.employee
       AND paydeductn.per_end_date = :end_date
       AND emdedmastr.company = paydeductn.company
       AND emdedmastr.employee = paydeductn.employee
       AND emdedmastr.ded_code = paydeductn.ded_code
       AND emdedmastr.seq_nbr = paydeductn.edm_seq_nbr
       AND emdedmastr.company = glmaster.company
       AND emdedmastr.acr_acct_unit = glmaster.acct_unit
       AND emdedmastr.acr_account = glmaster.account
       AND emdedmastr.acr_sub_acct = glmaster.sub_account
       AND employee.hm_acct_unit = glnames.acct_unit
       AND glmaster.chart_name = glchartdtl.chart_name
       AND glmaster.account = glchartdtl.account
       AND glmaster.sub_account = glchartdtl.sub_account
Tags: paydeductn glchartdtl
John Henley
Posts: 3362
3/29/2013 3:10 PM
See if this gets you where you want to go...although perhaps you might want to use PRDISTRIB rather than PAYDEDUCTN/EMDEDMASTR


SELECT
PYD.COMPANY,
PYD.EMPLOYEE,
PYD.CHECK_DATE,
PYD.PER_END_DATE,
PYD.DED_CODE,
PYD.DED_AMT,
PYD.EDM_SEQ_NBR,
EDM.ACR_DIST_CO,
EDM.ACR_ACCT_UNIT,
EDM.ACR_ACCOUNT,
EDM.ACR_SUB_ACCT,

GLM.COMPANY, GLS.NAME AS COMPANY_NAME, GLM.VAR_LEVELS, GLM.ACCT_UNIT,
GLM.ACCOUNT, GLM.SUB_ACCOUNT, GLM.POSTING_LEVEL AS AU_POSTING_LEVEL,
GLN.POSTING_FLAG, GLN.DESCRIPTION AS AU_DESCRIPTION, GLN.VAR_LEVEL_DISP,
GLN.LEVEL_DEPTH, GLN.LEVEL_DETAIL_01, GLN.LEVEL_DETAIL_02, GLN.LEVEL_DETAIL_03,
GLN.LEVEL_DETAIL_04, GLN.LEVEL_DETAIL_05, GLN.OBJ_ID, GLN.PARENT_OBJ_ID,
GDT.SUMRY_ACCT_ID, GDT.CHART_SECTION, GDT.ACCOUNT_DESC AS ACCT_DESC,
GDT.POSTING_LEVEL AS ACCT_POSTING_LEVEL, GLS.CURRENCY_CODE, GLS.CURR_CODE_1,
GLS.CURR_CODE_2, GLS.LEVEL_DESC_01, GLS.LEVEL_DESC_02, GLS.LEVEL_DESC_03,
GLS.LEVEL_DESC_04, GLS.LEVEL_DESC_05, GLS.NBR_DIGITS_01, GLS.NBR_DIGITS_02,
GLS.NBR_DIGITS_03, GLS.NBR_DIGITS_04, GLS.NBR_DIGITS_05, GLS.COMPANY_ND,
GLS.CURR_NAME_1, GLS.CURR_CODE_ND_1, GLS.CURR_NAME_2,
GLS.CURR_CODE_ND_2, GDT.CHART_NAME
FROM PAYDEDUCTN PYD
INNER JOIN EMDEDMASTR EDM
ON EDM.COMPANY = PYD.COMPANY
AND EDM.EMPLOYEE = PYD.EMPLOYEE
AND EDM.DED_CODE = PYD.DED_CODE
AND EDM.SEQ_NBR = PYD.EDM_SEQ_NBR
INNER JOIN GLMASTER GLM
ON GLM.COMPANY = EDM.ACR_DIST_CO
AND GLM.ACCT_UNIT = EDM.ACR_ACCT_UNIT
AND GLM.ACCOUNT = EDM.ACR_ACCOUNT
AND GLM.SUB_ACCOUNT = EDM.ACR_SUB_ACCT
INNER JOIN GLNAMES GLN
ON GLN.COMPANY = GLM.COMPANY
AND GLN.ACCT_UNIT = GLM.ACCT_UNIT
INNER JOIN GLCHARTDTL GDT
ON GDT.CHART_NAME = GLM.CHART_NAME
AND GDT.ACCOUNT = GLM.ACCOUNT
AND GDT.SUB_ACCOUNT = GLM.SUB_ACCOUNT
INNER JOIN GLSYSTEM GLS
ON GLS.COMPANY = GLM.COMPANY
WHERE PYD.COMPANY = 4321 AND PYD.PER_END_DATE = '2012-12-31'
Thanks for using the LawsonGuru.com forums!
John
0
liked by Members
Jeff
Advanced Member
Posts: 30
Advanced Member
3/29/2013 3:26 PM
Thanks for the reply John, I had also tried the route via PRDISTRIB but with no results.  I will give your code a shot and will reply back with the results.

Jeff
Jeff
Advanced Member
Posts: 30
Advanced Member
3/29/2013 4:27 PM
Going at the data via PRDISTRIB was definitely the route to take. Thanks once again John.

Jeff