AHCS Journal Entry Line - Rights Logic Report

WITH SECURITY_TBL AS
(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_AHCS_MANAGER_JOB', '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
	
)

SELECT
 GLL.NAME LEDGER_NAME
, XAH.PERIOD_NAME AS ACCOUNTING_PERIOD
, XET.NAME AS EVENT_TYPE
, GJST.USER_JE_SOURCE_NAME AS JOURNAL_SOURCE 
, GJCT.USER_JE_CATEGORY_NAME AS JOURNAL_CATEGORY
,GJB.NAME AS JOURNAL_BATCH_NAME
, XTE.TRANSACTION_NUMBER
, XAL.AE_LINE_NUM LINE_NUMBER
,XAL.CURRENCY_CODE ENTERED_CURRENCY
,NVL(XAL.ENTERED_DR,0) ENTERED_DR
,NVL(XAL.ENTERED_CR,0) ENTERED_CR
,GLL.CURRENCY_CODE LEDGER_CURRENCY
,NVL(XAL.ACCOUNTED_DR,0) ACCOUNTED_DR
,NVL(XAL.ACCOUNTED_CR,0) ACCOUNTED_CR
, CASE WHEN NVL(GCC.SEGMENT4,'-1')!='-1' THEN  GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5
 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9 END AS ACCOUNT_CODE_COMBINATION
 , FLV3.MEANING AS ACCOUNTING_CLASS
,FLV.MEANING ACCOUNTING_ENTRY_STATUS
,NVL(FLV2.DESCRIPTION, 'Unposted') POSTING_STATUS
, TO_CHAR(XAH.CREATION_DATE,'YYYY-MM-DD','nls_date_language=American') JOURNAL_CREATION_DATE
--, XAH.CREATION_DATE JOURNAL_CREATION_DATE
,XAH.DESCRIPTION HEADER_DESCRIPTION
,XAL.DESCRIPTION LINE_DESCRIPTION
,XAL.SR31 Integration_ID
,XAL.SR32 Source_File_Name
,XAL.SR36 Contract_Number
, XAL.SR37 Source_Set_of_Books
,  XAL.SR33 Reference27
, XAL.SR34 Source_Detail_Reference
,XAL.SR35 Batch_Name

FROM
XLA_TRANSACTION_ENTITIES XTE,
XLA_TRANSACTION_HEADERS XTH,
XLA_AE_HEADERS XAH,
GL_LEDGERS GLL,
XLA_SUBLEDGERS XSL,
XLA_AE_LINES XAL,
GL_CODE_COMBINATIONS GCC,
XLA_SUBLEDGERS_TL XST
,XLA_EVENT_TYPES_TL XET
, GL_JE_SOURCES_TL GJST
, GL_JE_CATEGORIES_TL GJCT
,gl_je_batches GJB
, FND_LOOKUP_VALUES_VL FLV2
, FND_LOOKUP_VALUES_VL FLV
,FND_LOOKUP_VALUES_VL FLV3
,GL_IMPORT_REFERENCES GIR
,SECURITY_TBL ST
WHERE 1=1
AND ST.LEDGER_ID=GLL.LEDGER_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID=XTH.APPLICATION_ID(+)
AND XAH.EVENT_ID=XTH.EVENT_ID(+)
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAH.ENTITY_ID = XTE.ENTITY_ID 
AND XAH.APPLICATION_ID = XTE.APPLICATION_ID  
AND XAH.APPLICATION_ID = XSL.APPLICATION_ID 
AND XSL.APPLICATION_ID=XST.APPLICATION_ID
AND xsT.LANGUAGE  = userenv('LANG')
and xah.application_id = xet.application_id
and xah.event_type_code = xet.event_type_code
AND xET.LANGUAGE  = userenv('LANG')
AND XSL.IMPLEMENTATION_MODE_CODE = 'SIMPLIFIED'
AND XAH.LEDGER_ID = GLL.LEDGER_ID
AND XST.APPLICATION_NAME ='RIGHTSLOGIC'
--AND NVL(XAL.SR31,'-1')!='-1'
AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
AND GJST.JE_SOURCE_NAME=XSL.JE_SOURCE_NAME
AND GJCT.JE_CATEGORY_NAME(+)=XAH.JE_CATEGORY_NAME
AND GJCT.LANGUAGE (+) = userenv('LANG')
AND GJST.LANGUAGE  = userenv('LANG')
AND FLV3.LOOKUP_TYPE='XLA_ACCOUNTING_CLASS'
AND FLV3.LOOKUP_CODE=XAL.ACCOUNTING_CLASS_CODE
AND FLV.LOOKUP_TYPE='XLA_ACCOUNTING_ENTRY_STATUS'
AND FLV.LOOKUP_CODE=XAH.ACCOUNTING_ENTRY_STATUS_CODE
AND GJB.GROUP_ID(+)=XAH.GROUP_ID
   AND FLV2.LOOKUP_TYPE(+)='MJE_BATCH_STATUS'
   AND FLV2.LOOKUP_CODE(+)=GJB.STATUS
   AND GIR.GL_SL_LINK_ID(+)=XAL.GL_SL_LINK_ID	
   AND GIR.GL_SL_LINK_TABLE(+)	=XAL.GL_SL_LINK_TABLE	
   AND GIR.JE_BATCH_ID=GJB.JE_BATCH_ID(+)
   AND XAL.ACCOUNTING_CLASS_CODE=(SELECT  XAL1.ACCOUNTING_CLASS_CODE
                                   FROM XLA_AE_LINES XAL1 
								   WHERE XAL1.AE_HEADER_ID=XAL.AE_HEADER_ID
								   AND XAL1.AE_LINE_NUM=XAL.AE_LINE_NUM
								   AND XAL1.APPLICATION_ID=XAL.APPLICATION_ID
								   AND (XAL1.ACCOUNTING_CLASS_CODE!='EXCHANGE_GAIN_LOSS'
								   OR (XAL1.ACCOUNTED_DR!=0 OR XAL1.ACCOUNTED_CR!=0))
								   ) 

--Parameters
AND XAH.ACCOUNTING_DATE BETWEEN :P_FROM_ACCOUNTING_DATE AND :P_TO_ACCOUNTING_DATE
AND (XAH.PERIOD_NAME IN (:P_ACCOUNTING_PERIOD) OR 'All' IN ('All'||:P_ACCOUNTING_PERIOD))
AND (GLL.NAME IN (:P_Ledger) OR 'All' IN ('All'||:P_Ledger))
AND (XTH.REQUEST_ID  IN (:P_REQUEST_ID) OR 'All' IN ('All'||:P_REQUEST_ID))
AND (XET.NAME  IN (:P_EVENT_TYPE) OR 'All' IN ('All'||:P_EVENT_TYPE))

ORDER BY GLL.NAME, XAH.PERIOD_NAME, XTE.TRANSACTION_NUMBER, XAL.AE_LINE_NUM, XET.NAME,  GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5
 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9