GL Batch Approval With Final Approver Report

 GL Batch Approval With Final Approver Report

SELECT gl.ledger_id,

   gl.name ledger_name,

   gjh.period_name,

   glcc.segment1            co,

   gljs.user_je_source_name source,

   gjb.je_batch_id,

   gjb.name          batch_name,

   gjb.creation_date batch_creation_date,

   gjb.posted_date   batch_posted_date,

   fu.USER_ID      batch_user_id,

   ppnf.display_name    batch_user_name,

   Null preparer_user_id,

   Null preparer,

   SUM(gjl.entered_dr)   entered_dr,

   SUM(gjl.accounted_dr) accounted_dr,

   gjh.currency_code     cy_cd,

   (CASE

WHEN(

gljs.user_je_source_name IN(

'Spreadsheet','Manual','AutoCopy','OBR Manual Adjustments','ON Ledger Conversions','Recurring'

)

AND gjb.approval_status_code = 'A'

) THEN upper(fu.USER_ID)

ELSE NULL

END

) approver_user_id,

(select 

   papf.FULL_NAME 

   from 

   GL_je_action_log gjal,

   per_users pu,

   per_person_names_f_v papf

   where 1=1

   and papf.name_type = 'GLOBAL'

   and trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date

   and gjal.user_id=pu.username(+)

   and pu.person_id=papf.person_id(+)

   and gjal.Action_code in('APPROVED')

AND gjal.je_batch_id=gjb.je_batch_id

   and gjal.action_date=(select min(glall.action_date) 

   FROM                

   GL_je_action_log glall

  where glall.je_batch_id=gjb.je_batch_id

  )

   )approver_name,

   (select 

   gjal.ACTION_DATE 

   from 

   GL_je_action_log gjal,

   per_users pu,

   per_person_names_f_v papf

   where 1=1

   and papf.name_type = 'GLOBAL'

   and trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date

   and gjal.user_id=pu.username(+)

   and pu.person_id=papf.person_id(+)

   and gjal.Action_code in('APPROVED')

AND gjal.je_batch_id=gjb.je_batch_id

   and gjal.action_date=(select min(glall.action_date) 

   FROM                

   GL_je_action_log glall

  where glall.je_batch_id=gjb.je_batch_id

  )

   )approval_date,

   /*(select 

   pu.username  

   from 

   GL_je_action_log gjal,

   per_users pu,

   per_person_names_f_v papf

   where 1=1

   and papf.name_type = 'GLOBAL'

   and trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date

   and gjal.user_id=pu.username(+)

   and pu.person_id=papf.person_id(+)

   and gjal.Action_code in('APPROVED')

AND gjal.je_batch_id=gjb.je_batch_id

   and gjal.action_date=(select min(glall.action_date) 

   FROM                

   GL_je_action_log glall

  where glall.je_batch_id=gjb.je_batch_id

  )*/ null guest_apprv_id,

(select 

   pu.username  

   from 

   GL_je_action_log gjal,

   per_users pu,

   per_person_names_f_v papf

   where 1=1

   and papf.name_type = 'GLOBAL'

   and trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date

   and gjal.user_id=pu.username(+)

   and pu.person_id=papf.person_id(+)

   and gjal.Action_code in('APPROVED')

AND gjal.je_batch_id=gjb.je_batch_id

   and gjal.action_date=(select max(glall.action_date) 

   FROM                

   GL_je_action_log glall

  where glall.je_batch_id=gjb.je_batch_id

  )

   ) approver_id_final,

   (select 

   papf.full_name   from 

   GL_je_action_log gjal,

   per_users pu,

   per_person_names_f_v papf

   where 1=1

   and gjal.user_id=pu.username(+)

   and pu.person_id=papf.person_id(+)

   and Action_code in('APPROVED')

   AND gjal.je_batch_id=gjb.je_batch_id

and papf.name_type = 'GLOBAL'

   and trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date

   and gjal.action_date=(select max(glall.action_date) FROM                

GL_je_action_log glall

where glall.je_batch_id=gjb.je_batch_id)

   ) approver_name_final,

   

   null guest_apprv_name,

   sysdate timestamp

   FROM

  gl_je_headers gjh,

  gl_je_lines gjl,

  gl_je_batches gjb,

  per_users fu,

  per_person_names_f       ppnf,

  gl_ledgers gl,

  gl_code_combinations glcc,

  gl_je_sources_tl gljs

   

 

where    gjh.ledger_id = gl.ledger_id

AND      gjh.je_header_id = gjl.je_header_id

AND      gjb.je_batch_id = gjh.je_batch_id

and      gjb.Created_By = to_char(fu.Username)

and       ppnf.person_id(+) = fu.person_id

and       ppnf.name_type     = 'GLOBAL'

AND      gjl.code_combination_id = glcc.code_combination_id

   -- AND      fu.user_id = gjb.created_by

AND      gjh.je_source = gljs.je_source_name

AND      gljs.LANGUAGE = 'US'

--AND trunc(SYSDATE) BETWEEN xev.person_effective_start_date (+) AND xev.person_effective_end_date (+)

--AND gjb.approver_employee_id = xev.person_id (+)

AND      gljs.user_je_source_name IN ('AutoCopy',

  'Manual',

  'OBR Manual Adjustments',

  'ON Ledger Conversions',

  'Recurring',

  'Spreadsheet')

AND ((COALESCE (NULL,:P_ledger_name) IS NULL)  

OR (gl.name IN (:P_ledger_name))   

OR 'ALL' in (:P_ledger_name))   

                                    AND ((COALESCE (NULL,:P_period_name) IS NULL)  

OR (gjh.period_name IN (:P_period_name))   

OR 'ALL' in (:P_period_name))

                                    AND ((COALESCE (NULL,:P_CO) IS NULL)  

OR (glcc.segment1 IN (:P_CO))   

OR 'ALL' in (:P_CO))

                                    

                                 /*   AND ((COALESCE (NULL,:P_cy_cd) IS NULL)  

OR (gjh.currency_code IN (:P_cy_cd))   

OR 'ALL' in (:P_cy_cd))

                                    

AND ((COALESCE (NULL,:P_source) IS NULL)  

OR (gljs.user_je_source_name IN (:P_source))   

OR 'ALL' in (:P_source)) 

*/

GROUP BY gl.ledger_id,

gl.name,

gjh.period_name,

glcc.segment1,

gljs.user_je_source_name,

gjb.je_batch_id,

gjb.name,

gjb.creation_date,

gjb.posted_date,

fu.USER_ID,

ppnf.display_name,

-- prep.preparer_user_id,

-- prep.preparer,

gjh.currency_code,

CASE

WHEN(

gljs.user_je_source_name IN(

'Spreadsheet','Manual','AutoCopy','OBR Manual Adjustments','ON Ledger Conversions','Recurring'

)

AND gjb.approval_status_code = 'A'

) THEN upper(fu.USER_ID)

ELSE NULL

END