AR To GL Query

SELECT gjl.reference_4 AR_Transaction,

       gjb.je_batch_id,

       gjb.name batch_name,

       gjh.je_header_id,

       gjh.name Journal_Header_Name,

       DECODE (gjh.ACTUAL_FLAG,

               'A', 'Actual',

               'B', 'Budget',

               'E', 'Encumbrance')

          Balance_Type,

       gjl.subledger_doc_sequence_value Doc_Seq_Value,

       gjl.effective_date GL_Date,

       gjl.code_combination_id,

       gjl.accounted_dr,

       gjl.accounted_cr,

       gjl.description Description,

       DECODE (gjl.status,  'P', 'Posted',  'U', 'Unposted',  gjl.status)

          Journal_Line_Status

  FROM gl_je_headers gjh, gl_je_lines gjl, gl_je_batches gjb

 WHERE     gjh.je_source = 'Receivables'

       AND gjh.je_header_id = gjl.je_header_id

       AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID

       AND gjh.JE_HEADER_ID = gjl.JE_HEADER_ID

       AND gjh.period_name = gjb.default_period_name

       AND gjh.set_of_books_id = &sob_id

       AND gjh.period_name = '&Period_Name'

       AND gjl.reference_4 = '&AR_trx_number';

Postion Hierarchy Details with Superior in Oracle HRMS

SELECT peha.position_structure_id,

  peha.EMPLOYEE_ID,

  fndu.user_id,

  ppos.POSITION_ID,

  pps.name Hierarchy,

  fndu.USER_NAME UserName,

  papf.FULL_NAME Person,

  ppos.NAME Position,

  peha.SUPERIOR_LEVEL SuperiorPositionLevel,

  ppos2.NAME SuperiorPosition,

  papf2.FULL_NAME SuperiorPerson,

  fndu2.USER_NAME SuperiorUsername,

  peha.SUPERIOR_ID SuperiorPersonId,

  fndu2.user_id SuperiorUserId,

  ppos2.POSITION_ID SuperiorPosId

FROM PO_EMPLOYEE_HIERARCHIES_ALL peha,

  PER_POSITIONS ppos,

  PER_POSITIONS ppos2,

  per_all_people_f papf,

  per_all_people_f papf2,

  fnd_user fndu,

  fnd_user fndu2,

  per_position_structures pps

WHERE pps.business_group_id   = peha.business_group_id

   AND pps.position_structure_id = peha.position_structure_id

   AND fndu2.EMPLOYEE_ID         = papf2.PERSON_ID

   AND papf2.PERSON_ID           = peha.SUPERIOR_ID

   AND papf2.EFFECTIVE_END_DATE  > sysdate

   AND papf.PERSON_ID            = peha.employee_id

   AND papf.EFFECTIVE_END_DATE   > sysdate

   AND ppos2.POSITION_ID         = peha.SUPERIOR_POSITION_ID

   AND ppos.position_id          = peha.EMPLOYEE_POSITION_ID

   AND peha.superior_level       > 0

   AND peha.employee_id          = fndu.EMPLOYEE_ID

  --and fndu.USER_NAME = upper('&StartingUsername')

ORDER BY peha.position_structure_id,

  peha.superior_level,

  papf2.full_name