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: 3353
    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'
      Tags: paydeductn glchartdtl
      Thanks for using the LawsonGuru.com forums!
      John
      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
        Tags: paydeductn glchartdtl
        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
          Tags: paydeductn glchartdtl