Saturday 8 September 2012

Query to find Payroll details posted to GL in R12


Query to find Payroll details posted to GL in R12

select
  PAPF.EMPLOYEE_NUMBER,
  PAPF.FIRST_NAME ||' '||PAPF.LAST_NAME EMPLOYEENAME,
  XAL.DESCRIPTION,
  NVL(NVL(-xal.accounted_cr,xal.accounted_dr),0) amount
FROM xla.xla_ae_headers xah,
  xla.xla_ae_lines xal,
  xla_distribution_links xdl,
  gl_code_combinations gcc,
  xla.xla_transaction_entities xte,
  gl_import_references gir,
  gl_je_headers gjh,
  gl_je_lines gjl,
  pay_assignment_actions paa,
  per_all_assignments_f paaf,
  PER_ALL_PEOPLE_F PAPF
WHERE XAH.AE_HEADER_ID       = XAL.AE_HEADER_ID
AND gcc.code_combination_id  = xal.code_combination_id
AND xdl.ae_header_id         = xah.ae_header_id
AND xdl.ae_line_num          = xal.ae_line_num
AND xte.entity_id            = xah.entity_id
AND xal.gl_sl_link_id        = gir.gl_sl_link_id
AND gir.je_header_id         = gjl.je_header_id
AND gir.je_line_num          = gjl.je_line_num
AND gir.je_header_id         = gjh.je_header_id
AND gjl.je_header_id         = gjh.je_header_id
AND paa.assignment_action_id = xdl.source_distribution_id_num_1
AND paa.assignment_id        = paaf.assignment_id
AND PAAF.PERSON_ID           = PAPF.PERSON_ID
AND TRUNC(sysdate) BETWEEN TRUNC(paaf.EFFECTIVE_START_DATE) AND TRUNC(paaf.EFFECTIVE_END_DATE)
and TRUNC(sysdate) between TRUNC(PAPF.EFFECTIVE_START_DATE) and TRUNC(PAPF.EFFECTIVE_END_DATE)
and  GJH.PERIOD_NAME='DEC-10'
and JE_CATEGORY_NAME='Payroll'

No comments:

Post a Comment