Pending Transaction Detail Report

<![CDATA[/*
Report ID   : RPT-R2R-010
Report Name : Pending Transaction Details Report
Description : Provides the ability to show all the unposted journals, but it is used because it displays all the journal line debits/credits to view the account strings
Date                 Version           Developed by           Description
-----------------------------------------------------------------------------------------------
6/15/2023            1.0               Rajkumar              Initial Development
*/
WITH SECURITY_TBL AS
(
SELECT DISTINCT GL.LEDGER_ID
FROM GL_LEDGERS GL
,(
SELECT DISTINCT GL.LEDGER_ID
FROM 
GL_LEDGERS GL,
GL_ACCESS_SETS GAS,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU

WHERE 
UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
AND GAS.ACCESS_SET_ID=GL.IMPLICIT_ACCESS_SET_ID
	AND (GAS.ACCESS_SET_ID =FURDA.ACCESS_SET_ID OR GL.LEDGER_ID=FURDA.LEDGER_ID)
	AND UPPER(FURDA.role_name) in ('XXC_GL_INQUIRY_JOB','XXC_GENERAL_ACCOUNTANT_JOB','XXC_GENERAL_ACCOUNTING_MANAGER_JOB')
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND  UPPER(PU.USERNAME) = UPPER(:xdo_user_name)
	AND PU.USER_GUID = FURDA.USER_GUID

) ST
WHERE 
1=1

AND ST.LEDGER_ID=GL.LEDGER_ID
)
SELECT 
	GL.name LEDGER_NAME,
	GP.period_name PERIOD,
	GJB.creation_date JOURNAL_BATCH_CREATION_DATE,
	GJB.name JOURNAL_BATCH,
	GJH.name JOURNAL_NAME,
	GJH.currency_code JOURNAL_CURRENCY,
	GJCV.user_je_category_name JOURNAL_CATEGORY,
	GJSV.user_je_source_name JOURNAL_SOURCE,
	FLV.description BATCH_STATUS,
	GJL.je_line_num JOURNAL_LINE_NUMBER,
	FND_FLEX_EXT.GET_SEGS('GL','GL#',GCC.chart_of_accounts_id,gcc.code_combination_id) GL_ACCOUNT_COMBINATION,
	NVL(GJL.entered_dr,0) ENTERED_DR,
	NVL(GJL.entered_cr,0) ENTERED_CR,
	NVL(GJL.accounted_dr,0) ACCOUNTED_DR,
	NVL(GJL.accounted_cr,0) ACCOUNTED_CR,
	GJL.description LINE_DESCRIPTION,
	GJL.created_by JOURNAL_LINE_CREATED_BY,
	GJL.creation_date JOURNAL_LINE_CREATION_DATE,
	GJL.last_updated_by JOURNAL_LINE_LAST_UPDATED_BY,
	GJL.last_update_date JOURNAL_LINE_LAST_UPDATE_DATE
FROM 
	gl_je_batches GJB,
	gl_je_headers GJH,
	gl_je_lines GJL,
	gl_ledgers GL,
	gl_periods GP,
	gl_je_categories_vl GJCV,
	gl_je_sources_vl GJSV,
	gl_code_combinations GCC,
	fnd_lookup_values FLV,
	gl_period_sets GPS,
	SECURITY_TBL ST
WHERE
	1=1
	AND GJB.je_batch_id					= GJH.je_batch_id
	AND GJH.je_header_id				= GJL.je_header_id
	AND GJH.ledger_id					= GL.ledger_id
	AND GJH.period_name 				= GP.period_name
	AND GJH.je_category 				= GJCV.je_category_name
	AND GJH.je_source					= GJSV.je_source_name
	AND GJL.code_combination_id			= GCC.code_combination_id
	AND GJB.status 						not in('P')
	AND GP.PERIOD_SET_NAME				= GPS.PERIOD_SET_NAME
	AND GPS.PERIOD_SET_NAME				= GL.PERIOD_SET_NAME
	AND GJB.status 						= FLV.lookup_code
	AND FLV.lookup_type 				= 'MJE_BATCH_STATUS'
	AND FLV.language					= 'US'
	AND GL.LEDGER_ID					= ST.ledger_id
	AND (GL.name                   		IN (:P_LEDGER)
	OR  'All'                      		IN (:P_LEDGER||'All'))
	AND GP.start_date					>= (select distinct min(start_date) from gl_periods where period_name = :P_FROM_PERIOD)
	AND GP.end_date						<= (select distinct max(end_date) from gl_periods where period_name = :P_TO_PERIOD)  	
	AND (GJB.status                		IN (:P_BSTATUS)
	OR  'All'                      		IN (:P_BSTATUS||'All'))
ORDER BY 1,2,3]]>