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 |
