IGI Inflation Accounting : Cost Detail - draft

Description
Categories: BI Publisher
Columns: Comp Code, Account, Cost Ctr, Asset, Asset Desc, Balance Type, Display Order, Beginning, Addition, Adjustment ...
Application: Public Sector Financials International
Source: Inflation Accounting : Cost Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: IGIIACSD_XML
DB package: IGI_IGIIACSD_XMLP_PKG
SELECT BAL.COMPANY	COMP_CODE,	
              BAL.ACCOUNT	ACCOUNT,
              BAL.COST_CENTER	COST_CTR,
              AD.ASSET_NUMBER        ASSET,
              AD.DESCRIPTION             ASSET_DESC,
              DECODE(BAL.DATA_SOURCE, 'FA', 'Historic','IAC')       BALANCE_TYPE,
              BAL.DISPLAY_ORDER,
              NVL(SUM (DECODE (BAL.Source_Type_Code, 
	 	'BEGIN', NVL(BAL.Amount,0), NULL)), 0)                 BEGINNING,
              NVL(SUM(DECODE(BAL.Source_Type_Code,  
                         'ADDITION', NVL(BAL.Amount,0), NULL)), 0)                       ADDITION,
              NVL(SUM(DECODE(BAL.Source_Type_Code, 
                         'ADJUSTMENT',NVL(BAL.Amount,0), NULL)), 0)                 ADJUSTMENT,
              NVL(SUM(DECODE(BAL.Source_Type_Code, 
	           'RECLASS', NVL(BAL.Amount,0), NULL)), 0)                    RECLASS,
             NVL(SUM(DECODE(BAL.Source_Type_Code, 
                         'RETIREMENT',-NVL(BAL.Amount,0), NULL)), 0)                 RETIREMENT,	    
            NVL( SUM(DECODE(BAL.Source_Type_Code, 
	       'REVALUATION',NVL(BAL.Amount,0),NULL)), 0)                 REVALUATION,
             NVL(SUM(DECODE(BAL.Source_Type_Code, 
	           'TRANSFER', NVL(BAL.Amount,0), NULL)), 0)                 TRANSFER,
             NVL(SUM (DECODE (BAL.Source_Type_Code, 
	 	'END', NVL(BAL.Amount,0), NULL)), 0)	           ENDING, 
	--&D_COMP_CODE d_comp_code, 
	--&D_ACCOUNT d_account, 
	--&D_COST_CTR d_cost_ctr, 
	IGI_IGIIACSD_XMLP_PKG.fa_beginningformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'BEGIN' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_BEGINNING, 
	IGI_IGIIACSD_XMLP_PKG.fa_additionformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_ADDITION, 
	IGI_IGIIACSD_XMLP_PKG.fa_adjustmentformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_ADJUSTMENT, 
	IGI_IGIIACSD_XMLP_PKG.fa_reclassformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_RECLASS, 
	IGI_IGIIACSD_XMLP_PKG.fa_retirementformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'RETIREMENT' , - NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_RETIREMENT, 
	IGI_IGIIACSD_XMLP_PKG.fa_revaluationformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_REVALUATION, 
	IGI_IGIIACSD_XMLP_PKG.fa_transferformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_TRANSFER, 
	IGI_IGIIACSD_XMLP_PKG.fa_endingformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'END' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) FA_ENDING, 
	IGI_IGIIACSD_XMLP_PKG.iac_beginningformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'BEGIN' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_BEGINNING, 
	IGI_IGIIACSD_XMLP_PKG.iac_additionformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'ADDITION' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_ADDITION, 
	IGI_IGIIACSD_XMLP_PKG.iac_adjustmentformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'ADJUSTMENT' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_ADJUSTMENT, 
	IGI_IGIIACSD_XMLP_PKG.iac_reclassformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_RECLASS, 
	IGI_IGIIACSD_XMLP_PKG.iac_retirementformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'RETIREMENT' , - NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_RETIREMENT, 
	IGI_IGIIACSD_XMLP_PKG.iac_revaluationformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_REVALUATION, 
	IGI_IGIIACSD_XMLP_PKG.iac_transferformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_TRANSFER, 
	IGI_IGIIACSD_XMLP_PKG.iac_endingformula(DECODE ( BAL.DATA_SOURCE , 'FA' , 'Historic' , 'IAC' ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'END' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) IAC_ENDING
FROM    IGI_IAC_BALANCES_REPORT BAL,
              FA_ADDITIONS AD
WHERE   :DO_INSERT = 1
AND       AD.ASSET_ID = BAL.ASSET_ID
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,
                   AD.ASSET_NUMBER,
                   AD.DESCRIPTION,
	BAL.DATA_SOURCE,
                   BAL.DISPLAY_ORDER
ORDER BY 1, 2, 3, 4, 5, 7