JE Hungarian Asset Movement Schedule: Gross Changes - draft

Description
Categories: BI Publisher
Application: European Localizations
Source: Hungarian Asset Movement Schedule: Gross Changes (XML) - Not Supported: Reserved For Future Use
Short Name: JEHUFAMS_XML
DB package: JE_JEHUFAMS_XMLP_PKG
SELECT
	&ACCT_FLEX_BAL_SEG					COMP_CODE,
	NVL(&ACCT_FLEX_ACCT_SEG, BAL.Category_Books_Account)	ACCOUNT,
	LO.description						C_PROPERTY_TYPE,
	CA.description						C_CATEGORY_DESC,
	CA.category_id						C_CATEGORY_ID,
	NVL(SUM (DECODE (BAL.Source_Type_Code, 
		'BEGIN', NVL(BAL.Amount,0), NULL)), 0)			BEGIN,
	NVL(SUM (DECODE(BAL.Source_Type_Code,
			DECODE(:P_REPORT_TYPE,
				'COST', 'ADDITION', 
				'CIP ADDITION'), NVL(BAL.Amount,0),
			NULL)
		) ,0)						ADDITION,
	NVL(SUM (DECODE(BAL.Source_Type_Code,
			DECODE(:P_REPORT_TYPE,
				'CIP COST', 'ADDITION'),-NVL(BAL.Amount,0),
			NULL)
		),0) 						CAPITALIZATION,
	NVL(SUM (DECODE (BAL.Source_Type_Code,
			DECODE(:P_REPORT_TYPE,
				'COST', 'ADJUSTMENT',
				'CIP 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,
			DECODE(:P_REPORT_TYPE,
				'COST', 'RETIREMENT',
				'CIP 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)			END, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_d', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_D, 
	JE_JEHUFAMS_XMLP_PKG.bal_out_of_balanceformula(:BAL_BEGIN, :BAL_ADD, :BAL_REVAL, :BAL_RECLASS, :BAL_RETIRE, :BAL_ADJUST, :BAL_TRANS, :BAL_CAPITAL, :BAL_END) BAL_OUT_OF_BALANCE, 
	JE_JEHUFAMS_XMLP_PKG.acct_out_of_balanceformula(:ACCT_BEGIN, :ACCT_ADD, :ACCT_REVAL, :ACCT_RECLASS, :ACCT_RETIRE, :ACCT_ADJUST, :ACCT_TRANS, :ACCT_CAPITAL, :ACCT_END) ACCT_OUT_OF_BALANCE, 
	JE_JEHUFAMS_XMLP_PKG.caprevalformula(NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , DECODE ( :P_REPORT_TYPE , 'CIP COST' , 'ADDITION' ) , - NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) CAPREVAL, 
	JE_JEHUFAMS_XMLP_PKG.out_of_balanceformula(NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'BEGIN' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , DECODE ( :P_REPORT_TYPE , 'COST' , 'ADDITION' , 'CIP ADDITION' ) , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'REVALUATION' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'RECLASS' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , DECODE ( :P_REPORT_TYPE , 'COST' , 'RETIREMENT' , 'CIP RETIREMENT' ) , - NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , DECODE ( :P_REPORT_TYPE , 'COST' , 'ADJUSTMENT' , 'CIP ADJUSTMENT' ) , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'TRANSFER' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , DECODE ( :P_REPORT_TYPE , 'CIP COST' , 'ADDITION' ) , - NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 ), NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'END' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) OOB, 
	NVL(fnd_flex_xml_publisher_apis.process_kff_combination_1('account_d', 'SQLGL', 'GL#', ajcc.CHART_OF_ACCOUNTS_ID, NULL, ajcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),BAL.Category_Books_Account) ACCOUNT_D
FROM
	JE_HU_BALANCES_REPORT	BAL,
	FA_CATEGORIES		CA,
	FA_LOOKUPS		LO,
	GL_CODE_COMBINATIONS	DHCC,
	GL_CODE_COMBINATIONS	AJCC
WHERE	DHCC.Code_Combination_ID = BAL.Distribution_CCID
AND	AJCC.Code_Combination_ID (+) = BAL.Adjustment_CCID
AND	:DO_INSERT = 1
AND	BAL.category_id  =  CA.category_id
AND	LO.lookup_code = CA.property_type_code 
AND	LO.lookup_type  =  'PROPERTY TYPE' 
AND	LO.enabled_flag = 'Y' 
AND	sysdate BETWEEN NVL(LO.start_date_active,sysdate) AND NVL(LO.end_date_active,sysdate)
AND	:P_COMPANY = fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_d', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')  
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	NVL(&ACCT_FLEX_ACCT_SEG, BAL.Category_Books_Account),
	LO.description,
	CA.description,
	CA.category_id,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_d', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
    NVL(fnd_flex_xml_publisher_apis.process_kff_combination_1('account_d', 'SQLGL', 'GL#', ajcc.CHART_OF_ACCOUNTS_ID, NULL, ajcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),BAL.Category_Books_Account)
ORDER BY
	4
Parameter Name SQL text Validation
Account Flex Structure
 
Number
Report Type
 
Company
 
To Period
 
LOV Oracle
From Period
 
LOV Oracle
Book
 
LOV Oracle