FA Budget-to-Actual - draft

➜ Back to Library
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
Run FA Budget-to-Actual - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Budget Book 
 
 LOV Oracle
Corporate Period 
 
 LOV Oracle
Download
Related Blitz Reports
GL Account Analysis GL Account Analysis (Distributions) 11g GL Account Analysis (Distributions) GL Account Analysis 11g JE Hungarian Asset Movement Schedule: Gross Changes- Not Supported: Reserved For Future Use GL Journals INV Inventory Item Upload FA Cost Detail Report- Not Supported: Reserved For Future Use FA Cost Summary Report- Not Supported: Reserved For Future Use
Blitz Report™

Blitz Report™ provides multiple benefits: