IGI UnBalanced Budget History - draft

Description
Categories: BI Publisher, Financials
Application: Public Sector Financials International
Source: Budgeting: UnBalanced Budget History Report (XML) - Not Supported: Reserved For Future Use
Short Name: IGIGBHRU_XML
DB package: IGI_IGIGBHRU_XMLP_PKG
select 	GLBV.BUDGET_NAME "Budget Name",
	null C_FLEXDATA,
	GSOB.NAME C_NAME,
	to_char(BHE.POSTED_DATE,'DD MON YYYY') "Posted Date",
	LU1.MEANING "TRX Type",
	BHE.CURRENT_YEAR_BUDGET "Current Year Budget",
	BHE.PROFILE_CODE "Profile Code",
	BHE.START_PERIOD "Start Period",
	DECODE(BHE.TRANSACTION_TYPE, 'IGIGBUDIN', BHE.REFERENCE1||'%',
                                                        	'IGIBUDNY', LU3.MEANING,
                                                        	NULL) "Additional", 
	LU4.MEANING "Recurring Entry",
	LU2.MEANING "Effect",
	BHE.NEXT_YEAR_BUDGET "Next Year Budget",
	BHE.REASON_CODE "Reason Code",
	BHE.GROUP_ID,
	PERIODS.PERIOD_NAME HEADER_PERIOD_NAME, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_flexfield', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CF_FLEXFIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_desc_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') CF_DESC_ALL
FROM 	igi_bud_history_entries_v BHE,
         	igi_lookups LU1,
	igi_lookups LU2,
	igi_lookups LU3,
        	gl_code_combinations gcc,
         	gl_sets_of_books gsob,
         	gl_budget_versions GLBV,
	fnd_lookups LU4,
	gl_periods PERIODS
where 	BHE.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
and     	BHE.CURRENCY_CODE = :P_Curr_Code
and     	BHE.CODE_COMBINATION_ID = :P_Code_Comb_ID
and 	BHE.FISCAL_YEAR = :P_FISCAL_YR
and     	GLBV.BUDGET_VERSION_ID = BHE.BUDGET_VERSION_Id
and     	GLBV.BUDGET_VERSION_ID = :P_Budget_Version_ID
and     	GCC.CODE_COMBINATION_ID = BHE.CODE_COMBINATION_ID
and     	GCC.CHART_OF_ACCOUNTS_ID = :P_COA_ID  
and     	LU1.LOOKUP_TYPE = 'IGI_BUD_TRANSACTION'
and     	LU1.LOOKUP_CODE =  BHE.TRANSACTION_TYPE
and     	GSOB.SET_OF_BOOKS_ID = :P_SET_OF_BOOKS_ID
and	LU2.LOOKUP_TYPE = 'IGI_BUD_ENTRY_EFFECT'
and	LU2.LOOKUP_CODE = NVL(BHE.EFFECT, 'PYE')
and	LU3.LOOKUP_TYPE(+) = 'IGI_BUD_PROFILE_METHOD'
and	LU3.LOOKUP_CODE(+) = BHE.REFERENCE1  
and 	LU4.LOOKUP_TYPE = 'YES_NO'
and	LU4.LOOKUP_CODE = BHE.RECURRING
and	PERIODS.PERIOD_TYPE = GSOB.ACCOUNTED_PERIOD_TYPE
and	PERIODS.PERIOD_SET_NAME = GSOB.PERIOD_SET_NAME
AND	PERIODS.PERIOD_YEAR = BHE.FISCAL_YEAR
ORDER BY 1 ASC,3 ASC,9 ASC,4 ASC,5 ASC,6 ASC,11 ASC,7 ASC,8 ASC,10 ASC,12 ASC,13 ASC , BHE.POSTED_DATE DESC , PERIODS.PERIOD_NUM
Parameter Name SQL text Validation
Data Access Set ID
 
Number
Chart of Accounts ID
 
LOV Oracle
Code Combination ID
 
Number
Currency Code
 
LOV Oracle
FISCAL YEAR
 
Number
Budget Version ID
 
Number
Ledger ID
 
LOV Oracle