SELECT gjh.accrual_rev_flag accrual_rev_flag
,gjh.accrual_rev_period_name accrual_rev_period_name
,gjh.creation_date journal_creation_date
,gjh.currency_code currency_code
,gjh.doc_sequence_value doc_sequence_value
,fu.email_address email_address
,gjh.je_category je_category
,gjh.last_update_date last_update_date
,gjh.name name journal_header_name
,gsobv.name ledger_name
,gjh.period_name period_name
,gjh.posted_date posted_date
,gjh.reversed_je_header_id reversed_je_header_id
,gjh.running_total_accounted_dr running_total_accounted_dr
,gjh.status status
,fu.user_name user_name
,gjsv.user_je_source_name user_je_source_name
,fu1.user_name user_name
,gjbv.name journal_batch_name
,gjbv.posted_by_name posted_by_name
,ppx.email_address email_address
,period.period_set
,CASE WHEN ( ( MIN(fad.attached_document_id) ) IS NOT NULL OR ( MIN(fad1.attached_document_id) ) IS NOT NULL )
THEN 'Y'
ELSE 'N'
END document_exists
FROM applsys.fnd_user fu
,gl.gl_je_headers gjh
,apps.gl_sets_of_books_v gsobv
,applsys.fnd_user fu1
,apps.gl_je_sources_v gjsv
,apps.gl_je_batches_v gjbv
,apps.per_people_x ppx
,applsys.fnd_attached_documents fad
,applsys.fnd_attached_documents fad1
,(SELECT gpv.period_name AS period_name
,MAX((gpv.period_set_name||gpv.period_year||LPAD(gpv.period_num,2,0))) AS period_set
FROM apps.gl_periods_v gpv
WHERE (gpv.period_year >= 2024)
GROUP BY gpv.period_name
) period
WHERE ( (gsobv.set_of_books_id = gjh.ledger_id)
AND (fu1.employee_id = ppx.person_id(+))
AND (gjh.last_updated_by = fu.user_id(+))
AND (gjh.created_by = fu1.user_id(+))
AND (gjh.je_batch_id = gjbv.je_batch_id(+))
AND ((TO_CHAR(gjbv.je_batch_id)) = fad.pk1_value(+))
AND ((TO_CHAR(gjh.je_header_id)) = fad1.pk2_value(+))
AND (gjh.je_source = gjsv.je_source_name)
AND (gjh.period_name = period.period_name(+)))
AND (fad.entity_name(+) = 'GL_JE_BATCHES')
AND (fad1.entity_name(+) = 'GL_JE_HEADERS')
AND (gjh.posted_date >= :p_posted_date)
AND (gjsv.user_je_source_name IN (:p_source))
AND (gjh.status IN ('U'))
AND (gjh.period_name = :p_period_name)
GROUP BY period.period_set
,gjh.accrual_rev_flag
,gjh.accrual_rev_period_name
,gjh.creation_date
,gjh.currency_code
,gjh.doc_sequence_value
,fu.email_address,gjh.je_category
,gjh.last_update_date
,gjh.name
,gsobv.name
,gjh.period_name
,gjh.posted_date
,gjh.reversed_je_header_id
,gjh.running_total_accounted_dr
,gjh.running_total_dr
,gjh.status
,fu.user_name
,gjsv.user_je_source_name
,fu1.user_name
,gjbv.name
,gjbv.posted_by_name
,ppx.email_address
ORDER BY gsobv.name ASC
,period.period_set ASC
,gjsv.user_je_source_name ASC
,gjh.doc_sequence_value ASC
