Unrealized Foreign Currency Revaluation Report

SELECT /*+parallel(12)*/
			LEDGER_NAME
		,	ACCOUNTING_STRING
		,	ACCOUNT_DECSCRIPTION
		,	FUNCTIONAL_CURRENCY
		,	JOURNAL_CURRENCY
		,	FOREIGN_CURRENCY_AMOUNT
		,	FUNCTIONAL_CURRENCY_AMOUNT 				
		,	ROUND(DECODE(FOREIGN_CURRENCY_AMOUNT,0,0,(FUNCTIONAL_CURRENCY_AMOUNT/FOREIGN_CURRENCY_AMOUNT)),7) AVERAGE_RATE
		,	ROUND(REVALUATION_RATE,30) REVALUATION_RATE									
		,	(FOREIGN_CURRENCY_AMOUNT*REVALUATION_RATE)				 								REVALUATED_BALANCE
		,	DECODE(FUNCTIONAL_CURRENCY_AMOUNT,0,0,(((FOREIGN_CURRENCY_AMOUNT*REVALUATION_RATE)-FUNCTIONAL_CURRENCY_AMOUNT)/FUNCTIONAL_CURRENCY_AMOUNT)*100) VARIANCE_PERCENT
		,	(FUNCTIONAL_CURRENCY_AMOUNT-(FOREIGN_CURRENCY_AMOUNT*REVALUATION_RATE))					UNREALIZED_GAIN_LOSS_AFT_REV
		,	ENTITY
		,	chart_of_accounts_id,code_combination_id
FROM 
	(
		SELECT /*+parallel(12)*/
				GL.name 				LEDGER_NAME
			,	GCC.segment1||'.'||GCC.segment2||'.'||GCC.segment3||'.'||GCC.segment4||'.'||GCC.segment5||'.'||GCC.segment6||'.'||GCC.segment7||'.'||GCC.segment8||'.'||GCC.segment9 	ACCOUNTING_STRING
			,	FFVV.description 		ACCOUNT_DECSCRIPTION
			--,	 GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID,4,GCC.SEGMENT4) 		ACCOUNT_DECSCRIPTION
			,	GL.currency_code 		FUNCTIONAL_CURRENCY
			,	GB.currency_code 		JOURNAL_CURRENCY
			,	(NVL(GB.begin_balance_dr,0))-(NVL(GB.begin_balance_cr,0))+(NVL(GB.period_net_dr,0))-(NVL(GB.period_net_cr,0)) 					FOREIGN_CURRENCY_AMOUNT
			,	(NVL(GB.begin_balance_dr_beq,0))-(NVL(GB.begin_balance_cr_beq,0))+(NVL(GB.period_net_dr_beq,0))-(NVL(GB.period_net_cr_beq,0))  	FUNCTIONAL_CURRENCY_AMOUNT
			,	(SELECT 
						DISTINCT GDR.Conversion_Rate
                 FROM 	
						gl_daily_conversion_types 		GDC
					,	gl_daily_rates 					GDR
				 WHERE 
						1					=1
					AND GDR.conversion_type	=GDC.conversion_type
					AND GDR.from_currency 	=GB.currency_code 
					AND GDR.to_currency 	=GL.currency_code
					AND (GDC.User_Conversion_Type IN (:P_RATE_TYPE) OR LEAST (:P_RATE_TYPE) IS NULL)
					AND GDR.Conversion_Date 	=:P_CONVERSION_DATE
                 --AND Rownum = 1
				 )					 	REVALUATION_RATE
			,	GCC.segment1 			ENTITY
			,	GCC.chart_of_accounts_id,GCC.code_combination_id

		FROM
				gl_balances						GB
			,	gl_code_combinations 			GCC
			,	gl_ledgers 						GL
			,	fnd_flex_values_vl 				FFVV
					
		WHERE
			1=1
			AND GB.code_combination_id			=GCC.code_combination_id
			AND GB.ledger_id					=GL.ledger_id
			AND GCC.chart_of_accounts_id		=GL.chart_of_accounts_id
			AND GB.translated_flag	IS NOT NULL 
			AND FFVV.flex_value					=GCC.segment4
			AND FFVV.flex_value_set_id IN (SELECT FLEX_VALUE_SET_ID 
                                   FROM fnd_id_flex_segments 
                                   WHERE 
										1=1 
								   and GL.chart_of_accounts_id = id_flex_num 
								   and application_id = '101' 
                                   AND id_flex_code = 'GL#' 
                                   AND enabled_flag = 'Y' 
                                   AND application_column_name = 'SEGMENT4')
			AND EXISTS (SELECT  /*+parallel(12)*/
							CODE_COMBINATION_ID 
						FROM 
							gl_code_combinations GCC1,
							gl_revaluations GR,
							gl_reval_account_ranges GRR
						WHERE 
							1 = 1
							AND CAST(GCC1.SEGMENT4 AS NUMERIC) BETWEEN NVL(CAST(GRR.SEGMENT4_LOW AS NUMERIC), CAST(GCC1.SEGMENT4 AS NUMERIC)) AND NVL(CAST(GRR.SEGMENT4_HIGH AS NUMERIC), CAST(GCC1.SEGMENT4 AS NUMERIC))
							AND GRR.REVALUATION_ID = GR.REVALUATION_ID
							AND GCC.CODE_COMBINATION_ID = GCC1.CODE_COMBINATION_ID
							AND GL.CHART_OF_ACCOUNTS_ID=GR.CHART_OF_ACCOUNTS_ID
							AND (GR.NAME IN (:P_REVAL) OR 'All' IN (:P_REVAL||'All'))
							AND (SELECT DISTINCT GDC.User_Conversion_Type
								 FROM 	
									gl_daily_conversion_types 		GDC
								WHERE 
										1					=1
								AND GR.conversion_rate_type	=GDC.conversion_type)=:P_RATE_TYPE
							AND GCC1.account_type IN ('L','A')
						)
			AND (GL.NAME IN (SELECT DISTINCT GL.NAME
					  FROM 
						gl_ledger_set_assignments 	GLSA,
						gl_ledgers 					GLS	
					  WHERE 
							GLSA.ledger_set_id 		=GLS.ledger_id
						AND GL.ledger_id 			=GLSA.ledger_id
						AND UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
						AND GLS.name = :P_LEDGER) OR GL.NAME IN (:P_LEDGER) OR 'All' IN (:P_LEDGER||'All'))
			AND CAST(GCC.segment1 AS NUMERIC) BETWEEN NVL(:P_FROM_ENTITY,CAST(GCC.segment1 AS NUMERIC)) AND NVL(:P_TO_ENTITY,CAST(GCC.segment1 AS NUMERIC))
			AND (GB.period_name IN (:P_PERIOD) OR 'All' IN (:P_PERIOD||'All'))
	)
ORDER BY 
LEDGER_NAME,
JOURNAL_CURRENCY