IGI Inflation Accounting : Operating Expense Summary Report- Not Supported: Reserved For Future Use

Description

Categories: BI Publisher
Application: Public Sector Financials International
Source: Inflation Accounting : Operating Expense Summary Report (XML) - Not Supported: Reserved For Future Use
Short Name: IGIIAOPE_XML
DB package: IGI_IGIIAOPE_XMLP_PKG

Imported from BI Publisher
Inflation Accounting : Operating Expense Summary Report

SQL 58 lines

SELECT 
	BAL.COMPANY	COMP_CODE,	
	BAL.ACCOUNT	ACCOUNT,
	BAL.COST_CENTER	COST_CTR,
	BAL.DATA_SOURCE       BALANCE_TYPE,
	BAL.DISPLAY_ORDER    DISPLAY_ORDER,
	SUM(DECODE(BAL.Source_Type_Code,  
		'ADDITION', NVL(BAL.Amount,0), NULL)) 		ADDITION,
	SUM(DECODE(BAL.Source_Type_Code,  
		'DEPRECIATION', NVL(BAL.Amount,0), NULL)) 	DEPRECIATION,
	SUM(DECODE(BAL.Source_Type_Code, 
		'ADJUSTMENT',NVL(BAL.Amount,0), NULL)) 	ADJUSTMENT,
	SUM(DECODE(BAL.Source_Type_Code, 
		'RECLASS', NVL(BAL.Amount,0), NULL)) 		RECLASS,
	SUM(DECODE(BAL.Source_Type_Code, 
		'REVALUATION',NVL(BAL.Amount,0),
                                     'DEPRECIATION',NVL(BAL.Amount,0), NULL)) 	REVALUATION,
	SUM(DECODE(BAL.Source_Type_Code, 
		'TRANSFER', NVL(BAL.Amount,0), NULL)) 		TRANSFER, 
	IGI_IGIIAOPE_XMLP_PKG.cost_additionformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_ADDITION, 
	IGI_IGIIAOPE_XMLP_PKG.cost_adjustmentformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_ADJUSTMENT, 
	IGI_IGIIAOPE_XMLP_PKG.cost_revaluationformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_REVALUATION, 
	IGI_IGIIAOPE_XMLP_PKG.cost_reclassformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_RECLASS, 
	IGI_IGIIAOPE_XMLP_PKG.cost_transferformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_TRANSFER, 
	IGI_IGIIAOPE_XMLP_PKG.cost_movementformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) ), :CC_COST_TRANSFER) COST_MOVEMENT, 
	IGI_IGIIAOPE_XMLP_PKG.blog_additionformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_ADDITION, 
	IGI_IGIIAOPE_XMLP_PKG.blog_adjustmentformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_ADJUSTMENT, 
	IGI_IGIIAOPE_XMLP_PKG.blog_revaluationformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_REVALUATION, 
	IGI_IGIIAOPE_XMLP_PKG.blog_reclassformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_RECLASS, 
	IGI_IGIIAOPE_XMLP_PKG.blog_transferformula(BAL.DATA_SOURCE) BLOG_TRANSFER, 
	IGI_IGIIAOPE_XMLP_PKG.blog_movementformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_MOVEMENT, 
	IGI_IGIIAOPE_XMLP_PKG.net_additionformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_ADDITION, 
	IGI_IGIIAOPE_XMLP_PKG.net_adjustmentformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_ADJUSTMENT, 
	IGI_IGIIAOPE_XMLP_PKG.net_revaluationformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_REVALUATION, 
	IGI_IGIIAOPE_XMLP_PKG.net_reclassformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_RECLASS, 
	IGI_IGIIAOPE_XMLP_PKG.net_transferformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_TRANSFER, 
	IGI_IGIIAOPE_XMLP_PKG.net_movementformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_MOVEMENT
FROM   
	IGI_IAC_BALANCES_REPORT BAL
WHERE   
	:DO_INSERT = 1
	AND     	BAL.COMPANY BETWEEN 
			NVL(:P_COMPANY1,  BAL.COMPANY)  AND
			NVL(:P_COMPANY2,  BAL.COMPANY) 
	AND     	BAL.COST_CENTER BETWEEN 
			NVL(:P_COST_CENTER1,  BAL.COST_CENTER)  AND
			NVL(:P_COST_CENTER2,  BAL.COST_CENTER) 
	AND     	BAL.ACCOUNT BETWEEN 
			NVL(:P_ACCOUNT1,  BAL.ACCOUNT)  AND
			NVL(:P_ACCOUNT2,  BAL.ACCOUNT)
GROUP BY
	BAL.COMPANY,
	BAL.ACCOUNT,
	BAL.COST_CENTER,
	BAL.DATA_SOURCE,
	BAL.DISPLAY_ORDER
ORDER BY 1,2,3,5

SELECT BAL.COMPANY COMP_CODE, BAL.ACCOUNT ACCOUNT, BAL.COST_CENTER COST_CTR, BAL.DATA_SOURCE BALANCE_TYPE, BAL.DISPLAY_ORDER DISPLAY_ORDER, SUM(DECODE(BAL.Source_Type_Code, 'ADDITION', NVL(BAL.Amount,0), NULL)) ADDITION, SUM(DECODE(BAL.Source_Type_Code, 'DEPRECIATION', NVL(BAL.Amount,0), NULL)) DEPRECIATION, SUM(DECODE(BAL.Source_Type_Code, 'ADJUSTMENT',NVL(BAL.Amount,0), NULL)) ADJUSTMENT, SUM(DECODE(BAL.Source_Type_Code, 'RECLASS', NVL(BAL.Amount,0), NULL)) RECLASS, SUM(DECODE(BAL.Source_Type_Code, 'REVALUATION',NVL(BAL.Amount,0), 'DEPRECIATION',NVL(BAL.Amount,0), NULL)) REVALUATION, SUM(DECODE(BAL.Source_Type_Code, 'TRANSFER', NVL(BAL.Amount,0), NULL)) TRANSFER, IGI_IGIIAOPE_XMLP_PKG.cost_additionformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_ADDITION, IGI_IGIIAOPE_XMLP_PKG.cost_adjustmentformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_ADJUSTMENT, IGI_IGIIAOPE_XMLP_PKG.cost_revaluationformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_REVALUATION, IGI_IGIIAOPE_XMLP_PKG.cost_reclassformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_RECLASS, IGI_IGIIAOPE_XMLP_PKG.cost_transferformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) )) COST_TRANSFER, IGI_IGIIAOPE_XMLP_PKG.cost_movementformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) ), :CC_COST_TRANSFER) COST_MOVEMENT, IGI_IGIIAOPE_XMLP_PKG.blog_additionformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_ADDITION, IGI_IGIIAOPE_XMLP_PKG.blog_adjustmentformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_ADJUSTMENT, IGI_IGIIAOPE_XMLP_PKG.blog_revaluationformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_REVALUATION, IGI_IGIIAOPE_XMLP_PKG.blog_reclassformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_RECLASS, IGI_IGIIAOPE_XMLP_PKG.blog_transferformula(BAL.DATA_SOURCE) BLOG_TRANSFER, IGI_IGIIAOPE_XMLP_PKG.blog_movementformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) BLOG_MOVEMENT, IGI_IGIIAOPE_XMLP_PKG.net_additionformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_ADDITION, IGI_IGIIAOPE_XMLP_PKG.net_adjustmentformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_ADJUSTMENT, IGI_IGIIAOPE_XMLP_PKG.net_revaluationformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_REVALUATION, IGI_IGIIAOPE_XMLP_PKG.net_reclassformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_RECLASS, IGI_IGIIAOPE_XMLP_PKG.net_transferformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_TRANSFER, IGI_IGIIAOPE_XMLP_PKG.net_movementformula(BAL.DATA_SOURCE, SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , 'DEPRECIATION' , NVL ( BAL.Amount , 0 ) , NULL ) ), SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) )) NET_MOVEMENT FROM IGI_IAC_BALANCES_REPORT BAL WHERE :DO_INSERT = 1 AND BAL.COMPANY BETWEEN NVL(:P_COMPANY1, BAL.COMPANY) AND NVL(:P_COMPANY2, BAL.COMPANY) AND BAL.COST_CENTER BETWEEN NVL(:P_COST_CENTER1, BAL.COST_CENTER) AND NVL(:P_COST_CENTER2, BAL.COST_CENTER) AND BAL.ACCOUNT BETWEEN NVL(:P_ACCOUNT1, BAL.ACCOUNT) AND NVL(:P_ACCOUNT2, BAL.ACCOUNT) GROUP BY BAL.COMPANY, BAL.ACCOUNT, BAL.COST_CENTER, BAL.DATA_SOURCE, BAL.DISPLAY_ORDER ORDER BY 1,2,3,5

Parameters 9

Parameter	Type	List of values SQL
Book	LOV Oracle
Period From	LOV Oracle
Period To	LOV Oracle
Company From	Char
Company To	Char
Cost Center From	Char
Cost Center To	Char
Account From	Char
Account To	Char

Description

SQL 58 lines

Parameters 9

Find out more

Features

Download

FAQ