XTR Settlements - Journals by Date

Select 	a.company_code				Company		,
	a.journal_date                                     		Jnl_Date		,
	a.currency 				Trans_Ccy		,
	gs.currency_code 				Sob_Currency,
	&cp_flexdata				C_FlexData,
	a.deal_number                                      		Deal_No,
	a.transaction_number                          		Transaction_No,
	a.debit_amount				Dr_Amount,
	a.credit_Amount				Cr_AMount,
	a.accounted_cr				Sob_Cr_Amount,
	a.Accounted_Dr				Sob_Dr_Amount,
	a.deal_type                                        		Deal_Type,
                   to_char(a.journal_date,:c_dateformat)                               Jnl_date_Char,
	a.deal_subtype                                     		Deal_SubType,
	a.product_type                                     		Product,
	a.portfolio_code                                   		Portfolio,
	p.short_name                                       		Comp_Name,
	p.home_currency                                    		Home_Ccy,
	decode(a.transfer_to_external_gl,null,'N','Y') 	Gl_Interfaced,
	a.code_combination_id                       		Code_Combination_Id,
	GCC.chart_of_accounts_id	      		Chart_Acc_Id,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_DESC_ALL
From 	XTR_JOURNALS_V 		a,
	XTR_PARTIES_V 		p,
	XTR_GL_REFERENCES_V 	b,
	GL_CODE_COMBINATIONS  	GCC,
	gl_sets_of_books 		gs
where	&WHERE_FLEX
and 	a.company_code 	like 	nvl(upper(:COMPANY_CODE2),'%')
and 	a.company_code 	= 	p.party_code
and 	a.journal_date 	between 	nvl(:JNLS_FROM_DATE2,a.journal_date) and nvl(:JNLS_TO_DATE2,a.journal_date)
and 	a.deal_type 	like 	nvl(upper(:DEAL_TYPE2),DEAL_TYPE)
and 	a.deal_number	like 	nvl(:DEAL_NUMBER2,DEAL_NUMBER)
and 	a.amount_type	like 	nvl(upper(:AMOUNT_TYPE2),AMOUNT_TYPE)
and 	(((a.debit_amount = :AMOUNT2 or a.credit_amount = :AMOUNT2)
and     	(:AMOUNT2 is NOT NULL)) or (:AMOUNT2 is NULL))
and 	a.code_combination_id 	=	GCC.code_combination_id
and 	a.company_code 		= 	b.company_code
and 	a.code_combination_id 	= 	b.code_combination_id
and 	gs.set_of_books_id 		= 	p.set_of_books_id
and 	&GL_WHERE
order by 	1,2,3,5,6,7