FA Budget-to-Actual - draft

Description
Categories: BI Publisher
Application: Assets
Source: Budget-to-Actual Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS955_XML
DB package: FA_FAS955_XMLP_PKG
SELECT
	1						BUDGET,
	SUM(DECODE(FACBT.PERIOD_NUMBER, 
		:PERIOD_NUM, FACBT.PERIOD_COST,0))	PB_COST,
	SUM(DECODE(FACBT.QUARTER_NUMBER,
		:QUARTER_NUM, FACBT.PERIOD_COST,0))	QB_COST,
	SUM(FACBT.PERIOD_COST)				YB_COST,
	0   PA_COST,
	0   QA_COST,
	0   YA_COST,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
	FA_FAS955_XMLP_PKG.bal_pdevformula(:BAL_PB_COST, :BAL_PA_COST, :PRECISION) BAL_PDEV, 
	FA_FAS955_XMLP_PKG.bal_ydevformula(:BAL_YB_COST, :BAL_YA_COST, :PRECISION) BAL_YDEV, 
	FA_FAS955_XMLP_PKG.bal_qdevformula(:BAL_QB_COST, :BAL_QA_COST, :PRECISION) BAL_QDEV, 
	FA_FAS955_XMLP_PKG.bd_pdevformula(:BD_PB_COST, :BD_PA_COST, :PRECISION) BD_PDEV, 
	FA_FAS955_XMLP_PKG.bd_qdevformula(:BD_QB_COST, :BD_QA_COST) BD_QDEV, 
	FA_FAS955_XMLP_PKG.bd_ydevformula(:BD_YB_COST, :BD_YA_COST, :PRECISION) BD_YDEV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1, 
	FA_FAS955_XMLP_PKG.cc_pdevformula(:CC_PB_COST, :CC_PA_COST, :PRECISION) CC_PDEV, 
	FA_FAS955_XMLP_PKG.cc_qdevformula(:CC_QB_COST, :CC_QA_COST, :PRECISION) CC_QDEV, 
	FA_FAS955_XMLP_PKG.cc_ydevformula(:CC_YB_COST, :CC_YA_COST, :PRECISION) CC_YDEV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_major_cat', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE') D_MAJOR_CAT1, 
	FA_FAS955_XMLP_PKG.cat_pdevformula(:CAT_PB_COST, :CAT_PA_COST, :PRECISION) CAT_PDEV, 
	FA_FAS955_XMLP_PKG.cat_qdevformula(:CAT_QB_COST, :CAT_QA_COST, :PRECISION) CAT_QDEV, 
	FA_FAS955_XMLP_PKG.cat_ydevformula(:CAT_YB_COST, :CAT_YA_COST, :PRECISION) CAT_YDEV
FROM
	FA_CAPITAL_BUDGET	FACBT,
	FA_BUDGET_DISTRIBUTION	FABD,
	FA_CATEGORIES		CAT,
	GL_CODE_COMBINATIONS	DHCC
WHERE	FABD.BOOK_TYPE_CODE  = :P_BOOK
AND	FABD.BUDGET_ID	= FACBT.BUDGET_ID
AND	FACBT.PERIOD_NUMBER <= :PERIOD_NUM
AND	FABD.CATEGORY_ID = CAT.CATEGORY_ID
AND 	FABD.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
GROUP BY
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_major_cat', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE')
UNION
SELECT
	1						BUDGET,
	0						PB_COST,
	0						QB_COST,
	0						YB_COST,
	ROUND(SUM(DECODE(FADP.PERIOD_NUM,
		:PERIOD_NUM, (FADH.UNITS_ASSIGNED * 
		FABKS.COST /AH.UNITS), 0)), :PRECISION)	PA_COST,
	ROUND(SUM(DECODE(DECODE(FADP.PERIOD_NUM, 1,1,
		2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,
		10,4,11,4,12,4,0), 
		:QUARTER_NUM, (FADH.UNITS_ASSIGNED * 
		FABKS.COST /AH.UNITS), 0)), :PRECISION)	QA_COST,
	ROUND(SUM(FADH.UNITS_ASSIGNED * 
		FABKS.COST /AH.UNITS), :PRECISION)		YA_COST
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
	FA_FAS955_XMLP_PKG.bal_pdevformula(:BAL_PB_COST, :BAL_PA_COST, :PRECISION) BAL_PDEV, 
	FA_FAS955_XMLP_PKG.bal_ydevformula(:BAL_YB_COST, :BAL_YA_COST, :PRECISION) BAL_YDEV, 
	FA_FAS955_XMLP_PKG.bal_qdevformula(:BAL_QB_COST, :BAL_QA_COST, :PRECISION) BAL_QDEV, 
	FA_FAS955_XMLP_PKG.bd_pdevformula(:BD_PB_COST, :BD_PA_COST, :PRECISION) BD_PDEV, 
	FA_FAS955_XMLP_PKG.bd_qdevformula(:BD_QB_COST, :BD_QA_COST) BD_QDEV, 
	FA_FAS955_XMLP_PKG.bd_ydevformula(:BD_YB_COST, :BD_YA_COST, :PRECISION) BD_YDEV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1, 
	FA_FAS955_XMLP_PKG.cc_pdevformula(:CC_PB_COST, :CC_PA_COST, :PRECISION) CC_PDEV, 
	FA_FAS955_XMLP_PKG.cc_qdevformula(:CC_QB_COST, :CC_QA_COST, :PRECISION) CC_QDEV, 
	FA_FAS955_XMLP_PKG.cc_ydevformula(:CC_YB_COST, :CC_YA_COST, :PRECISION) CC_YDEV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_major_cat', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE') D_MAJOR_CAT1, 
	FA_FAS955_XMLP_PKG.cat_pdevformula(:CAT_PB_COST, :CAT_PA_COST, :PRECISION) CAT_PDEV, 
	FA_FAS955_XMLP_PKG.cat_qdevformula(:CAT_QB_COST, :CAT_QA_COST, :PRECISION) CAT_QDEV, 
	FA_FAS955_XMLP_PKG.cat_ydevformula(:CAT_YB_COST, :CAT_YA_COST, :PRECISION) CAT_YDEV
FROM	
	FA_BUDGET_DISTRIBUTION	FABD,
       	FA_BOOKS 		FABKS,
       	FA_TRANSACTION_HEADERS 	FAHEAD,
       	FA_ADDITIONS 		FAADD,
	FA_DISTRIBUTION_HISTORY FADH,
  	FA_DEPRN_PERIODS	FADP,
	FA_ASSET_HISTORY	AH,
	FA_TRANSACTION_HEADERS	TH_ADD,
	GL_CODE_COMBINATIONS	DHCC,
	FA_CATEGORIES		CAT
WHERE	
	FABD.BOOK_TYPE_CODE = :P_BOOK  AND
	FABD.CATEGORY_ID = CAT.CATEGORY_ID		AND
	FABD.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
AND	FAHEAD.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK
AND FAHEAD.TRANSACTION_TYPE_CODE IN ('TRANSFER', 'TRANSFER IN', 'TRANSFER IN/VOID') AND
	FAHEAD.ASSET_ID	= FABKS.ASSET_ID		AND
	FAHEAD.ASSET_ID	= FAADD.ASSET_ID		AND
	FABKS.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK  
AND	FADH.ASSET_ID = FAHEAD.ASSET_ID		AND
	FADH.BOOK_TYPE_CODE = FAHEAD.BOOK_TYPE_CODE	AND
 	( FADH.TRANSACTION_HEADER_ID_IN = FAHEAD.TRANSACTION_HEADER_ID
                   and  FADH.date_ineffective is null )
AND	FABKS.TRANSACTION_HEADER_ID_IN = TH_ADD.TRANSACTION_HEADER_ID AND
	FABKS.DATE_EFFECTIVE BETWEEN FADP.PERIOD_OPEN_DATE AND 
		NVL(FADP.PERIOD_CLOSE_DATE, FABKS.DATE_EFFECTIVE)	
AND 	FADP.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK
AND	FADP.FISCAL_YEAR = :PERIOD1_FY  AND
	FADP.PERIOD_NUM <= :PERIOD_NUM
AND
        	TH_ADD.ASSET_ID = AH.ASSET_ID                           AND
	TH_ADD.DATE_EFFECTIVE >= AH.DATE_EFFECTIVE		AND
	TH_ADD.DATE_EFFECTIVE < NVL(AH.DATE_INEFFECTIVE,SYSDATE)
AND
	TH_ADD.ASSET_ID = FAADD.ASSET_ID			AND
	TH_ADD.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK
  AND TH_ADD.TRANSACTION_TYPE_CODE = 'ADDITION'
AND
	FADH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
AND
	FAADD.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
GROUP BY
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_major_cat', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE')
UNION
SELECT
	0						BUDGET,
	0						PB_COST,
	0						QB_COST,
	0						YB_COST,
	ROUND(SUM(DECODE(FADP.PERIOD_NUM,
		:PERIOD_NUM, (FADH.UNITS_ASSIGNED * 
		FABKS.COST /AH.UNITS), 0)), :PRECISION)	PA_COST,
	ROUND(SUM(DECODE(DECODE(FADP.PERIOD_NUM, 1,1,
		2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,
		10,4,11,4,12,4,0), 
		:QUARTER_NUM, (FADH.UNITS_ASSIGNED * 
		FABKS.COST /AH.UNITS), 0)), :PRECISION)	QA_COST,
	ROUND(SUM(FADH.UNITS_ASSIGNED * 
		FABKS.COST /AH.UNITS), :PRECISION)		YA_COST
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
	FA_FAS955_XMLP_PKG.bal_pdevformula(:BAL_PB_COST, :BAL_PA_COST, :PRECISION) BAL_PDEV, 
	FA_FAS955_XMLP_PKG.bal_ydevformula(:BAL_YB_COST, :BAL_YA_COST, :PRECISION) BAL_YDEV, 
	FA_FAS955_XMLP_PKG.bal_qdevformula(:BAL_QB_COST, :BAL_QA_COST, :PRECISION) BAL_QDEV, 
	FA_FAS955_XMLP_PKG.bd_pdevformula(:BD_PB_COST, :BD_PA_COST, :PRECISION) BD_PDEV, 
	FA_FAS955_XMLP_PKG.bd_qdevformula(:BD_QB_COST, :BD_QA_COST) BD_QDEV, 
	FA_FAS955_XMLP_PKG.bd_ydevformula(:BD_YB_COST, :BD_YA_COST, :PRECISION) BD_YDEV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1, 
	FA_FAS955_XMLP_PKG.cc_pdevformula(:CC_PB_COST, :CC_PA_COST, :PRECISION) CC_PDEV, 
	FA_FAS955_XMLP_PKG.cc_qdevformula(:CC_QB_COST, :CC_QA_COST, :PRECISION) CC_QDEV, 
	FA_FAS955_XMLP_PKG.cc_ydevformula(:CC_YB_COST, :CC_YA_COST, :PRECISION) CC_YDEV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_major_cat', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE') D_MAJOR_CAT1, 
	FA_FAS955_XMLP_PKG.cat_pdevformula(:CAT_PB_COST, :CAT_PA_COST, :PRECISION) CAT_PDEV, 
	FA_FAS955_XMLP_PKG.cat_qdevformula(:CAT_QB_COST, :CAT_QA_COST, :PRECISION) CAT_QDEV, 
	FA_FAS955_XMLP_PKG.cat_ydevformula(:CAT_YB_COST, :CAT_YA_COST, :PRECISION) CAT_YDEV
FROM 
       	FA_BOOKS 		FABKS,
       	FA_TRANSACTION_HEADERS 	FAHEAD,
       	FA_ADDITIONS 		FAADD,
		FA_DISTRIBUTION_HISTORY FADH,
  		FA_DEPRN_PERIODS	FADP,
		FA_ASSET_HISTORY	AH,
		FA_TRANSACTION_HEADERS	TH_ADD,
		GL_CODE_COMBINATIONS	DHCC,
		FA_CATEGORIES		CAT
WHERE	
	(CAT.CATEGORY_ID, DHCC.CODE_COMBINATION_ID) NOT IN
	(SELECT DISTINCT FABD.CATEGORY_ID, FABD.CODE_COMBINATION_ID
	FROM FA_BUDGET_DISTRIBUTION FABD
	WHERE	FABD.BOOK_TYPE_CODE (+) = :P_BOOK)
AND	FAHEAD.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK 
 AND	FAHEAD.TRANSACTION_TYPE_CODE IN ('TRANSFER', 'TRANSFER IN', 'TRANSFER IN/VOID' ) AND
	FAHEAD.ASSET_ID	= FABKS.ASSET_ID		AND
	FAHEAD.ASSET_ID	= FAADD.ASSET_ID		AND
	FABKS.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK
AND	FADH.ASSET_ID = FAHEAD.ASSET_ID		AND
	FADH.BOOK_TYPE_CODE = FAHEAD.BOOK_TYPE_CODE	AND
 	( FADH.TRANSACTION_HEADER_ID_IN = FAHEAD.TRANSACTION_HEADER_ID
                      and FADH.date_ineffective is null )
AND	FABKS.TRANSACTION_HEADER_ID_IN = TH_ADD.TRANSACTION_HEADER_ID AND
	FABKS.DATE_EFFECTIVE BETWEEN FADP.PERIOD_OPEN_DATE AND 
		NVL(FADP.PERIOD_CLOSE_DATE, FABKS.DATE_EFFECTIVE)	
AND 	FADP.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK
  AND	FADP.FISCAL_YEAR = :PERIOD1_FY  AND
	FADP.PERIOD_NUM <= :PERIOD_NUM  
AND
    TH_ADD.ASSET_ID = AH.ASSET_ID                           AND
	TH_ADD.DATE_EFFECTIVE >= AH.DATE_EFFECTIVE		AND
	TH_ADD.DATE_EFFECTIVE < NVL(AH.DATE_INEFFECTIVE,SYSDATE)
AND
	TH_ADD.ASSET_ID = FAADD.ASSET_ID			AND
	TH_ADD.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK
	AND TH_ADD.TRANSACTION_TYPE_CODE = 'ADDITION'
AND
	FADH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
AND
	FAADD.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
GROUP BY
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_major_cat', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE')
order by 8, 1 desc, 15, 19, 2, 3, 4, 5, 6, 7
Parameter Name SQL text Validation
Corporate Period
 
LOV Oracle
Budget Book
 
LOV Oracle