Libro Partitario Report

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 GL.NAME IN ('PL ITALY EUR GAAP', 'SL ITALY EUR LOCAL')
AND ST.LEDGER_ID=GL.LEDGER_ID
)

SELECT
	AE_LINE_NUM,
	GL_DATE,
	DOC_SEQ_NUM,
	DOC_SEQ_NAME,
	TRANS_DETAIL,
	EVENT_TYPE,
	SEGMENT4,
	SEGMENT4 ACC,
	USER_JE_SOURCE_NAME,
	SUPPLIER_NAME,
	SUPPLIER_NUMBER,
	DR_ACCOUNTED,
	CR_ACCOUNTED,
	TOTAL,
	PERIOD_NAME,
	LEDGER_NAME,
	LEDGER_CURRENCY,
	PERIOD_FROM,
	PERIOD_TO,
	ACCOUNT_FROM,
	ACCOUNT_TO,
	LEGAL_ENTITY_FROM,
	LEGAL_ENTITY_TO,
	CONV_PERIOD_FROM,
	CONV_PERIOD_TO,
	ENTITY_CODE
FROM
(
SELECT
	AE_LINE_NUM,
	to_char(GL_DATE, 'DD-MON-YY','NLS_DATE_LANGUAGE = italian') GL_DATE,
	DOC_SEQ_NUM,
	DOC_SEQ_NAME,
	TRANS_DETAIL,
	EVENT_TYPE,
	SEGMENT4,
	SEGMENT4 ACC,
	USER_JE_SOURCE_NAME,
	SUPPLIER_NAME,
	SUPPLIER_NUMBER,
	NVL(SUM(DR_ACCOUNTED),0) DR_ACCOUNTED,
	NVL(SUM(CR_ACCOUNTED),0) CR_ACCOUNTED,
	NVL(SUM(TOTAL),0) TOTAL,
	PERIOD_NAME,
	LEDGER_NAME,
	LEDGER_CURRENCY,
	:P_PERIOD_FROM PERIOD_FROM,
	:P_PERIOD_TO PERIOD_TO,
	:P_ACCOUNT_FROM ACCOUNT_FROM,
	:P_ACCOUNT_TO ACCOUNT_TO,
	:P_LEGAL_ENTITY_FROM LEGAL_ENTITY_FROM,
	:P_LEGAL_ENTITY_TO LEGAL_ENTITY_TO,
	CONV_PERIOD_FROM,
	CONV_PERIOD_TO,
	ENTITY_CODE
FROM
(
SELECT
	XAL.AE_LINE_NUM,
	GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
	GJH.posting_acct_seq_value DOC_SEQ_NUM,
	XTE.ENTITY_CODE,
	FSV.header_name DOC_SEQ_NAME,
	(
	CASE WHEN GJSV.USER_JE_SOURCE_NAME IN ('Payables','Receivables','Global Intercompany') THEN 
	GJCV.USER_JE_CATEGORY_NAME  
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Assets' then 
	XETV.name
	when GJSV.USER_JE_SOURCE_NAME in ('Manual','Payroll','Cash Management','Revaluation','Allocations','Spreadsheet','RIGHTSLOGIC') then 
	GJH.name
	END
	) TRANS_DETAIL,
	(
	CASE WHEN GJSV.USER_JE_SOURCE_NAME = 'Assets' then 
	XETV.name
	WHEN GJSV.USER_JE_SOURCE_NAME IN ('Payables','Receivables') THEN
	(
	XTE.transaction_number||'-'||to_char(XAL.ACCOUNTING_DATE,'DD-MON-YY','NLS_DATE_LANGUAGE = italian')
	)
	when GJSV.USER_JE_SOURCE_NAME in ('Manual','Payroll','Revaluation','Allocations','Spreadsheet','RIGHTSLOGIC','Global Intercompany') then
	GJL.description
	when GJSV.USER_JE_SOURCE_NAME = 'Cash Management' then
	XAL.description
	end
	) EVENT_TYPE,
	GCC.SEGMENT4,
	GJSV.USER_JE_SOURCE_NAME,
	(
	CASE WHEN XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_INVOICES' THEN
	(SELECT distinct
	Party.PARTY_NAME AS PARTY_PARTY_NAME 
	FROM 
	AP_INVOICES_ALL InvoiceHeader, 
	HZ_PARTIES Party 
	WHERE 
	1=1
	AND InvoiceHeader.PARTY_ID = Party.PARTY_ID(+)
	and InvoiceHeader.INVOICE_ID = XTE.SOURCE_ID_INT_1
	) 
	WHEN XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_PAYMENTS' THEN
	(
	SELECT DISTINCT
	VENDOR_NAME
	FROM
	AP_CHECKS_ALL 
	WHERE
	1=1
	AND CHECK_ID = XTE.SOURCE_ID_INT_1
	)
	WHEN XTE.SOURCE_APPLICATION_ID = 222 
	THEN
	(SELECT distinct
	HP.PARTY_NAME
	FROM 
	ra_customer_trx_all RCTA,
	hz_cust_accounts HCA,
	hz_cust_acct_sites_all HCAS,
	hz_cust_site_uses_all HCSU,
	hz_party_sites HPS,
	hz_parties HP
	WHERE 
	1=1
	AND RCTA.bill_to_customer_id = HCA.cust_account_id
	AND RCTA.bill_to_site_use_id = HCSU.site_use_id
	AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
	AND HCAS.cust_account_id = HCA.cust_account_id
	AND HCA.party_id = HP.party_id
	AND XAL.party_id = HCA.cust_account_id
	)
	end
	) SUPPLIER_NAME,
	(
	CASE WHEN XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_INVOICES' THEN
	(SELECT distinct
	Party.PARTY_NUMBER
	FROM 
	AP_INVOICES_ALL InvoiceHeader, 
	HZ_PARTIES Party 
	WHERE 
	1=1
	AND InvoiceHeader.PARTY_ID = Party.PARTY_ID(+)
	and InvoiceHeader.INVOICE_ID = XTE.SOURCE_ID_INT_1
	) 
	WHEN XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_PAYMENTS' THEN
	(
	SELECT DISTINCT
	POZ.SEGMENT1
	FROM
	AP_CHECKS_ALL ACA,
	POZ_SUPPLIERS POZ
	WHERE
	1=1
	AND ACA.VENDOR_ID = POZ.VENDOR_ID
	AND ACA.CHECK_ID = XTE.SOURCE_ID_INT_1
	)
	WHEN XTE.SOURCE_APPLICATION_ID = 222 
	THEN
	(SELECT distinct
	HP.PARTY_NUMBER
	FROM 
	ra_customer_trx_all RCTA,
	hz_cust_accounts HCA,
	hz_cust_acct_sites_all HCAS,
	hz_cust_site_uses_all HCSU,
	hz_party_sites HPS,
	hz_parties HP
	WHERE 
	1=1
	AND RCTA.bill_to_customer_id = HCA.cust_account_id
	AND RCTA.bill_to_site_use_id = HCSU.site_use_id
	AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
	AND HCAS.cust_account_id = HCA.cust_account_id
	AND HCA.party_id = HP.party_id
	AND XAL.party_id = HCA.cust_account_id
	)
	end
	) SUPPLIER_NUMBER,
	(
	CASE WHEN GJSV.USER_JE_SOURCE_NAME in ('Manual','Payroll','Revaluation','Allocations','Spreadsheet','RIGHTSLOGIC','Global Intercompany') THEN
	GJL.ACCOUNTED_DR
	ELSE
	XAL.ACCOUNTED_DR
	END
	) DR_ACCOUNTED,
	(
	CASE WHEN GJSV.USER_JE_SOURCE_NAME in ('Manual','Payroll','Revaluation','Allocations','Spreadsheet','RIGHTSLOGIC','Global Intercompany') THEN
	GJL.ACCOUNTED_CR
	ELSE
	XAL.ACCOUNTED_CR
	END
	) CR_ACCOUNTED,
	(
	CASE WHEN GJSV.USER_JE_SOURCE_NAME in ('Manual','Payroll','Revaluation','Allocations','Spreadsheet','RIGHTSLOGIC','Global Intercompany') THEN
	(NVL(GJL.ACCOUNTED_DR,0) - NVL(GJL.ACCOUNTED_CR,0))
	ELSE
	(NVL(XAL.ACCOUNTED_DR,0) - NVL(XAL.ACCOUNTED_CR,0))
	END
	) TOTAL,
	GP.PERIOD_NAME,
	GL.name LEDGER_NAME,
	GL.currency_code LEDGER_CURRENCY,
	TO_CHAR((select distinct start_date from gl_periods where period_set_name = GP.period_set_name and period_name = :P_PERIOD_FROM),'MON-YYYY', 'NLS_DATE_LANGUAGE = ITALIAN') CONV_PERIOD_FROM,
	TO_CHAR((select distinct end_date from gl_periods where period_set_name = GP.period_set_name and period_name = :P_PERIOD_TO),'MON-YYYY', 'NLS_DATE_LANGUAGE = ITALIAN') CONV_PERIOD_TO

FROM
	gl_je_headers GJH,
	gl_ledgers GL,
	gl_periods GP, 
	fun_seq_versions FSV,
	gl_je_categories_tl GJCV,
	gl_je_sources_vl GJSV,
	gl_je_lines GJL,
	gl_code_combinations GCC,
	gl_import_references GIR,
	xla_ae_lines XAL,
	xla_ae_headers XAH,
	xla_event_types_tl XETV,
	xla_transaction_entities XTE,
	SECURITY_TBL ST
WHERE
	1=1
	AND ST.ledger_id						= GL.LEDGER_ID
	AND GJH.LEDGER_ID						= GL.LEDGER_ID
	AND GL.NAME								IN ('PL ITALY EUR GAAP', 'SL ITALY EUR LOCAL')
	AND GJH.posting_acct_seq_version_id 	= FSV.seq_version_id(+)
	AND GJH.JE_CATEGORY						= GJCV.JE_CATEGORY_NAME
	AND GJCV.LANGUAGE						= 'I'
	AND GJH.JE_SOURCE 						= GJSV.JE_SOURCE_NAME
	AND GJH.JE_HEADER_ID					= GJL.JE_HEADER_ID
	AND GJL.CODE_COMBINATION_ID				= GCC.CODE_COMBINATION_ID
	AND GJL.je_line_num          			= GIR.je_line_num(+)
	AND GJH.je_header_id 		 			= GIR.je_header_id(+)
	AND GIR.gl_sl_link_id        			= XAL.gl_sl_link_id(+)
	AND GIR.gl_sl_link_table     			= XAL.gl_sl_link_table(+)
	AND XAL.ae_header_id         			= XAH.ae_header_id(+)
	AND XAH.EVENT_TYPE_CODE					= XETV.EVENT_TYPE_CODE(+)
	AND XETV.LANGUAGE(+) 					= 'I'
	and XAH.application_id					= XETV.application_id(+)
	and XAH.entity_id						= XTE.entity_id(+)
	AND XAH.application_id 					= XTE.application_id(+)
	AND GL.period_set_name 					= GP.period_set_name
	AND GJH.period_name						= GP.period_name
	AND GP.START_DATE						>= (select distinct start_date from gl_periods where period_set_name = GP.period_set_name and period_name = :P_PERIOD_FROM)
	AND GP.end_date						    <= (select distinct end_date from gl_periods where period_set_name = GP.period_set_name and period_name = :P_PERIOD_TO)
	AND GL.name 							in (:P_LEDGER)
	AND cast(GCC.segment4 as NUMERIC)		between cast(:P_ACCOUNT_FROM as NUMERIC) and cast(:P_ACCOUNT_TO as NUMERIC) 						
	AND cast(GCC.segment1 as NUMERIC)		between cast(:P_LEGAL_ENTITY_FROM as NUMERIC) and cast(:P_LEGAL_ENTITY_TO as NUMERIC) 

)
group by
	AE_LINE_NUM,
	GL_DATE,
	DOC_SEQ_NUM,
	DOC_SEQ_NAME,
	TRANS_DETAIL,
	EVENT_TYPE,
	SEGMENT4,
	USER_JE_SOURCE_NAME,
	SUPPLIER_NAME,
	SUPPLIER_NUMBER,
	PERIOD_NAME,
	LEDGER_NAME,
	LEDGER_CURRENCY,
	CONV_PERIOD_FROM,
	CONV_PERIOD_TO,
	ENTITY_CODE
	
)

WHERE
1=1
AND (DR_ACCOUNTED+CR_ACCOUNTED) <> 0