FA Asset Additions - draft
Description
Categories: BI Publisher
Application: Assets
Source: Asset Additions Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS420_XML
DB package: FA_FAS420_XMLP_PKG
Source: Asset Additions Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS420_XML
DB package: FA_FAS420_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
SELECT &ACCT_FLEX_BAL_SEG COMP_CODE, FALU.MEANING ASSET_TYPE, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) GL_ACCOUNT, &ACCT_FLEX_COST_SEG COST_CENTER, decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT) RES_ACCOUNT, AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE START_DATE, BKS.DEPRN_METHOD_CODE METHOD, BKS.LIFE_IN_MONTHS LIFE, BKS.PRODUCTION_CAPACITY PROD, BKS.ADJUSTED_RATE ADJ_RATE, 0 BONUS_RATE, sum(DECODE(ADJ.SOURCE_TYPE_CODE, 'CIP ADJUSTMENT', decode(th_captest.transaction_type_code,'', 0, decode(adj.debit_credit_flag,'DR',1,-1) * nvl(adj.adjustment_amount,0)), decode(adj.debit_credit_flag,'DR',1,-1) * nvl(adj.adjustment_amount,0) )) COST, SUM(0) YTD_DEPRN, SUM(0) DEPRN_RESERVE, thcipadd.TRANSACTION_HEADER_ID THID, 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_code, &D_GL_ACCOUNT d_gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE FROM FA_ADDITIONS AD, FA_LOOKUPS FALU, FA_CATEGORY_BOOKS CB, GL_CODE_COMBINATIONS DHCC, fa_transaction_headers thcipadd, FA_ASSET_HISTORY AH, &LP_FA_BOOKS BKS, fa_distribution_history dh2, &LP_FA_DEPRN_PERIODS DP, &LP_FA_ADJUSTMENTS ADJ, FA_TRANSACTION_HEADERS TH_CAPTEST WHERE DP.BOOK_TYPE_CODE = :P_BOOK and dp.period_counter between :PERIOD1_PC and :PERIOD2_PC and dp.period_counter = adj.period_counter_created AND BKS.BOOK_TYPE_CODE = dp.book_type_code AND BKS.ASSET_ID = ad.asset_id AND BKS.DATE_INEFFECTIVE IS NULL AND AD.ASSET_TYPE = 'CIP' AND AH.ASSET_ID = ad.ASSET_ID and AH.TRANSACTION_HEADER_ID_IN = (SELECT MAX(TRANSACTION_HEADER_ID_IN) FROM FA_ASSET_HISTORY WHERE ASSET_ID = AD.ASSET_ID AND ASSET_TYPE = 'CIP' AND DATE_EFFECTIVE BETWEEN :PERIOD1_POD AND :PERIOD2_PCD) AND CB.CATEGORY_ID = AH.CATEGORY_ID AND CB.BOOK_TYPE_CODE = bks.book_type_code AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND FALU.LOOKUP_TYPE = 'ASSET TYPE' AND TH_CAPTEST.BOOK_TYPE_CODE(+) = BKS.BOOK_TYPE_CODE AND TH_CAPTEST.ASSET_ID(+) = BKS.ASSET_ID AND TH_CAPTEST.TRANSACTION_TYPE_CODE(+) = 'ADDITION' and th_captest.date_effective(+) between :Period1_POD and :Period2_PCD AND ADJ.BOOK_TYPE_CODE = bks.book_type_code AND ADJ.ASSET_ID = bks.ASSET_ID AND ADJ.ADJUSTMENT_TYPE LIKE '%COST' AND ADJ.PERIOD_COUNTER_CREATED BETWEEN :PERIOD1_PC AND :PERIOD2_PC AND ADJ.SOURCE_TYPE_CODE like 'CIP AD%' AND thcipadd.book_type_code = bks.book_type_code and thcipadd.asset_id = bks.asset_id anD thcipadd.transaction_header_id in ( select t.transaction_header_id from fa_transaction_headers t where t.book_type_code = bks.book_type_code and t.asset_id = bks.asset_id and t.transaction_type_code = 'CIP ADDITION' and t.date_effective between :period1_pod and :period2_pcd) and adj.distribution_id = dh2.distribution_id and dh2.code_combination_id = dhcc.code_combination_id GROUP BY &ACCT_FLEX_BAL_SEG, FALU.MEANING, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT), &ACCT_FLEX_COST_SEG , decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT), AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION, BKS.DATE_PLACED_IN_SERVICE, BKS.DEPRN_METHOD_CODE, BKS.LIFE_IN_MONTHS, BKS.PRODUCTION_CAPACITY, BKS.ADJUSTED_RATE, THcipadd.TRANSACTION_HEADER_ID, --ADDED FOR DT FIXES 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_GL_ACCOUNT , fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') UNION ALL SELECT &ACCT_FLEX_BAL_SEG COMP_CODE, FALU.MEANING ASSET_TYPE, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) GL_ACCOUNT, &ACCT_FLEX_COST_SEG COST_CENTER, decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT) RES_ACCOUNT, AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE START_DATE, BKS.DEPRN_METHOD_CODE METHOD, BKS.LIFE_IN_MONTHS LIFE, BKS.PRODUCTION_CAPACITY PROD, BKS.ADJUSTED_RATE ADJ_RATE, NVL(DS.BONUS_RATE,0) BONUS_RATE, bks.cost COST, SUM(DD.YTD_DEPRN) YTD_DEPRN, SUM(DD.DEPRN_RESERVE) DEPRN_RESERVE, TH.TRANSACTION_HEADER_ID THID, 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_code, &D_GL_ACCOUNT d_gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE FROM &LP_FA_DEPRN_SUMMARY DS, FA_ADDITIONS AD, GL_CODE_COMBINATIONS DHCC, FA_DISTRIBUTION_HISTORY DH, FA_LOOKUPS FALU, FA_CATEGORY_BOOKS CB, FA_TRANSACTION_HEADERS TH, FA_ASSET_HISTORY AH, &LP_FA_BOOKS BKS, &LP_FA_DEPRN_PERIODS DP, &LP_FA_DEPRN_DETAIL DD WHERE DS.BOOK_TYPE_CODE = :P_BOOK AND DS.ASSET_ID = DD.ASSET_ID AND DS.PERIOD_COUNTER = (SELECT nvl(MIN(DS_SUB.PERIOD_COUNTER),dd.period_counter) FROM FA_DEPRN_SUMMARY DS_SUB WHERE DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND DS_SUB.ASSET_ID = DD.ASSET_ID AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN') AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID AND AD.ASSET_ID = DD.ASSET_ID AND DP.BOOK_TYPE_CODE = :P_BOOK AND DP.PERIOD_COUNTER = dd.period_counter + 1 AND DD.BOOK_TYPE_CODE = DS.BOOK_TYPE_CODE AND DD.DEPRN_SOURCE_CODE = 'B' AND DD.PERIOD_COUNTER >= :PERIOD1_PC-1 and dd.period_counter <= :PERIOD2_PC-1 AND BKS.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND BKS.ASSET_ID = DD.ASSET_ID and bks.transaction_header_id_in = th.transaction_header_id AND AH.ASSET_ID = DD.ASSET_ID AND AH.DATE_EFFECTIVE <= NVL(dp.period_close_date, SYSDATE) AND NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > NVL(dp.period_close_date, SYSDATE) AND CB.CATEGORY_ID = AH.CATEGORY_ID AND CB.BOOK_TYPE_CODE = :P_BOOK AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND FALU.LOOKUP_TYPE = 'ASSET TYPE' AND AD.ASSET_ID = TH.ASSET_ID AND TH.BOOK_TYPE_CODE = :P_BOOK AND ((AH.ASSET_TYPE <> 'CIP' AND TH.TRANSACTION_TYPE_CODE = 'ADDITION')) GROUP BY &ACCT_FLEX_BAL_SEG, FALU.MEANING, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT), &ACCT_FLEX_COST_SEG , decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT), AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION, BKS.DATE_PLACED_IN_SERVICE, BKS.DEPRN_METHOD_CODE, BKS.LIFE_IN_MONTHS, BKS.PRODUCTION_CAPACITY, BKS.ADJUSTED_RATE, NVL(DS.BONUS_RATE,0), TH.TRANSACTION_HEADER_ID,bks.cost, --ADDED FOR DT FIXES 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_GL_ACCOUNT , fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') UNION ALL SELECT &ACCT_FLEX_BAL_SEG COMP_CODE, FALU.MEANING ASSET_TYPE, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) GL_ACCOUNT, &ACCT_FLEX_COST_SEG COST_CENTER, decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT) RES_ACCOUNT, AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE START_DATE, BKS.DEPRN_METHOD_CODE METHOD, BKS.LIFE_IN_MONTHS LIFE, BKS.PRODUCTION_CAPACITY PROD, BKS.ADJUSTED_RATE ADJ_RATE, NVL(DS.BONUS_RATE, 0) BONUS_RATE, sum(decode(adjadd.debit_credit_flag,'DR',1,-1) * nvl(adjadd.adjustment_amount,0)) COST, 0 YTD_DEPRN, 0 DEPRN_RESERVE, TH.TRANSACTION_HEADER_ID THID, --ADDED FOR DT FIXES 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_code, &D_GL_ACCOUNT d_gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE FROM FA_DISTRIBUTION_HISTORY DH, GL_CODE_COMBINATIONS DHCC, &LP_FA_DEPRN_SUMMARY DS, FA_TRANSACTION_HEADERS THDIS, &LP_FA_BOOKS BKS, FA_ADDITIONS AD, FA_CATEGORY_BOOKS CB, FA_LOOKUPS FALU, FA_ASSET_HISTORY AH, &LP_FA_DEPRN_PERIODS DP, FA_TRANSACTION_HEADERS TH, &LP_FA_ADJUSTMENTS ADJADD WHERE DS.BOOK_TYPE_CODE = :P_BOOK AND DS.ASSET_ID = adjadd.asset_id and DS.DEPRN_SOURCE_CODE = 'DEPRN' AND DS.PERIOD_COUNTER = ADJADD.PERIOD_COUNTER_CREATED AND DH.DISTRIBUTION_ID = ADJADD.DISTRIBUTION_ID AND DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID AND ADJADD.BOOK_TYPE_CODE = :P_BOOK AND ADJADD.ASSET_ID = TH.ASSET_ID AND DP.PERIOD_COUNTER >= :PERIOD1_PC AND DP.PERIOD_COUNTER <= :PERIOD2_PC AND TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND TH.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) AND TH.BOOK_TYPE_CODE = :P_BOOK AND th.asset_id = ADJADD.ASSET_ID AND TH.TRANSACTION_TYPE_CODE = 'ADDITION' AND THDIS.TRANSACTION_TYPE_CODE = 'CIP ADDITION' AND THDIS.BOOK_TYPE_CODE = :P_BOOK AND THDIS.ASSET_ID = TH.ASSET_ID AND BKS.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND DP.BOOK_TYPE_CODE = ADJADD.BOOK_TYPE_CODE and DP.PERIOD_COUNTER = ADJADD.PERIOD_COUNTER_CREATED AND AH.ASSET_ID = ADJADD.ASSET_ID AND ADJADD.SOURCE_TYPE_CODE = 'ADDITION' AND ADJADD.ADJUSTMENT_TYPE = 'COST' AND AH.DATE_EFFECTIVE <= NVL(DP.PERIOD_CLOSE_DATE, ah.date_effective+1) AND NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > NVL(DP.PERIOD_CLOSE_DATE, SYSDATE) AND AD.ASSET_ID = ah.ASSET_ID AND CB.CATEGORY_ID = AH.CATEGORY_ID AND CB.BOOK_TYPE_CODE = :P_BOOK AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND FALU.LOOKUP_TYPE = 'ASSET TYPE' GROUP BY &ACCT_FLEX_BAL_SEG, FALU.MEANING, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT), &ACCT_FLEX_COST_SEG , decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT), AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION, BKS.DATE_PLACED_IN_SERVICE, BKS.DEPRN_METHOD_CODE, BKS.LIFE_IN_MONTHS, BKS.PRODUCTION_CAPACITY, BKS.ADJUSTED_RATE, NVL(DS.BONUS_RATE, 0), ADJADD.distribution_id, ADJADD.ADJUSTMENT_AMOUNT, TH.TRANSACTION_HEADER_ID, --ADDED FOR DT FIXES 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_GL_ACCOUNT , fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') UNION ALL SELECT &ACCT_FLEX_BAL_SEG COMP_CODE, FALU.MEANING ASSET_TYPE, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) GL_ACCOUNT, &ACCT_FLEX_COST_SEG COST_CENTER, decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT) RES_ACCOUNT, AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE START_DATE, BKS.DEPRN_METHOD_CODE METHOD, BKS.LIFE_IN_MONTHS LIFE, BKS.PRODUCTION_CAPACITY PROD, BKS.ADJUSTED_RATE ADJ_RATE, 0 BONUS_RATE, sum(decode(adj.debit_credit_flag,'CR',1,-1) * nvl(adj.adjustment_amount,0)) COST, 0 YTD_DEPRN, 0 DEPRN_RESERVE, TH.TRANSACTION_HEADER_ID THID, --ADDED FOR DT FIXES 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_code, &D_GL_ACCOUNT d_gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE FROM FA_DISTRIBUTION_HISTORY DH, GL_CODE_COMBINATIONS DHCC, FA_TRANSACTION_HEADERS THDIS, &LP_FA_BOOKS BKS, FA_ADDITIONS AD, FA_CATEGORY_BOOKS CB, FA_LOOKUPS FALU, FA_ASSET_HISTORY AH, &LP_FA_DEPRN_PERIODS DP, FA_TRANSACTION_HEADERS TH, &LP_FA_ADJUSTMENTS ADJ WHERE DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID AND DP.PERIOD_COUNTER >= :PERIOD1_PC AND DP.PERIOD_COUNTER <= :PERIOD2_PC AND DP.PERIOD_CLOSE_DATE IS NULL AND DP.DEPRN_RUN IS NULL AND TH.DATE_EFFECTIVE >= DP.PERIOD_OPEN_DATE AND TH.DATE_EFFECTIVE < nvl(DP.PERIOD_CLOSE_DATE,th.date_effective+1) AND TH.BOOK_TYPE_CODE = :P_BOOK AND TH.ASSET_ID = DH.ASSET_ID AND TH.TRANSACTION_TYPE_CODE = 'ADDITION' AND THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN' AND THDIS.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK AND THDIS.ASSET_ID = TH.ASSET_ID AND THDIS.DATE_EFFECTIVE < DP.PERIOD_OPEN_DATE AND adj.book_type_code = :P_BOOK AND adj.asset_id = dh.asset_id AND adj.source_type_code = 'ADDITION' AND adj.adjustment_type = 'CIP COST' AND adj.period_counter_created = dp.period_counter AND adj.distribution_id = dh.distribution_id AND BKS.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID AND DP.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE AND AH.ASSET_ID = TH.ASSET_ID AND AH.DATE_EFFECTIVE <= NVL(DP.PERIOD_CLOSE_DATE, ah.date_effective+1) AND NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > NVL(DP.PERIOD_CLOSE_DATE, SYSDATE) AND AD.ASSET_ID = ah.ASSET_ID AND CB.CATEGORY_ID = AH.CATEGORY_ID AND CB.BOOK_TYPE_CODE = :P_BOOK AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND FALU.LOOKUP_TYPE = 'ASSET TYPE' GROUP BY &ACCT_FLEX_BAL_SEG, FALU.MEANING, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT), &ACCT_FLEX_COST_SEG, decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT), AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION, BKS.DATE_PLACED_IN_SERVICE, BKS.DEPRN_METHOD_CODE, BKS.LIFE_IN_MONTHS, BKS.PRODUCTION_CAPACITY, BKS.ADJUSTED_RATE, dh.distribution_id, adj.adjustment_amount, TH.TRANSACTION_HEADER_ID, --ADDED FOR DT FIXES 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_GL_ACCOUNT , fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') UNION All SELECT &ACCT_FLEX_BAL_SEG COMP_CODE, FALU.MEANING ASSET_TYPE, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT) GL_ACCOUNT, &ACCT_FLEX_COST_SEG COST_CENTER, decode(AH.ASSET_TYPE, 'CIP', NULL,CB.DEPRN_RESERVE_ACCT) RES_ACCOUNT, AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE START_DATE, BKS.DEPRN_METHOD_CODE METHOD, BKS.LIFE_IN_MONTHS LIFE, BKS.PRODUCTION_CAPACITY PROD, BKS.ADJUSTED_RATE ADJ_RATE, 0 BONUS_RATE, BKS.cost COST, 0 YTD_DEPRN, 0 DEPRN_RESERVE, TH.TRANSACTION_HEADER_ID THID, 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_code, &D_GL_ACCOUNT d_gl_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') d_cost_center, FA_FAS420_XMLP_PKG.d_lifeformula(BKS.LIFE_IN_MONTHS, BKS.ADJUSTED_RATE, 0, BKS.PRODUCTION_CAPACITY) D_LIFE FROM &LP_FA_DEPRN_SUMMARY DS, FA_ADDITIONS AD, GL_CODE_COMBINATIONS DHCC, FA_DISTRIBUTION_HISTORY DH, FA_LOOKUPS FALU, FA_CATEGORY_BOOKS CB, FA_TRANSACTION_HEADERS TH, FA_ASSET_HISTORY AH, &LP_FA_BOOKS BKS, &LP_FA_DEPRN_PERIODS DP, &LP_FA_DEPRN_DETAIL DD WHERE DS.BOOK_TYPE_CODE = :P_BOOK AND DS.ASSET_ID = DD.ASSET_ID AND DS.PERIOD_COUNTER = (SELECT nvl(MIN(DS_SUB.PERIOD_COUNTER),dd.period_counter) FROM FA_DEPRN_SUMMARY DS_SUB WHERE DS_SUB.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND DS_SUB.ASSET_ID = DD.ASSET_ID AND DS_SUB.DEPRN_SOURCE_CODE = 'DEPRN') AND AD.ASSET_ID = DD.ASSET_ID AND DP.BOOK_TYPE_CODE = ds.book_type_code AND DP.PERIOD_COUNTER = dd.period_counter + 1 AND DD.BOOK_TYPE_CODE = ds.book_type_code AND DD.DEPRN_SOURCE_CODE = 'B' AND DD.PERIOD_COUNTER >= :PERIOD1_PC - 1 and dd.period_counter <= :PERIOD2_PC - 1 AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID AND BKS.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND BKS.ASSET_ID = DD.ASSET_ID and bks.transaction_header_id_in = th.transaction_header_id AND AH.ASSET_ID = DD.ASSET_ID AND AH.DATE_EFFECTIVE <= NVL(dp.period_close_date, SYSDATE) AND NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) > NVL(dp.period_close_date, SYSDATE) AND CB.CATEGORY_ID = AH.CATEGORY_ID AND CB.BOOK_TYPE_CODE = ds.book_type_code AND AH.ASSET_TYPE = FALU.LOOKUP_CODE AND FALU.LOOKUP_TYPE = 'ASSET TYPE' AND dd.asset_id not in (SELECT adjsub.asset_id FROM FA_ADJUSTMENTS adjsub WHERE adjsub.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE AND adjsub.ASSET_ID = DD.ASSET_ID) AND AD.ASSET_ID = TH.ASSET_ID AND CB.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE AND (AH.ASSET_TYPE = 'CIP' AND TH.TRANSACTION_TYPE_CODE = 'CIP ADDITION') GROUP BY &ACCT_FLEX_BAL_SEG, FALU.MEANING, decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT), &ACCT_FLEX_COST_SEG , decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT), AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION, BKS.DATE_PLACED_IN_SERVICE, BKS.DEPRN_METHOD_CODE, BKS.LIFE_IN_MONTHS, BKS.PRODUCTION_CAPACITY, BKS.ADJUSTED_RATE, dh.distribution_id, BKS.cost, TH.TRANSACTION_HEADER_ID, 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_GL_ACCOUNT , fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', DHCC.CHART_OF_ACCOUNTS_ID, NULL, DHCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') ORDER BY D_COMP_CODE,2,3,D_COST_CENTER,5,6,15,7,8,9,12,11,10,13,14 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Book |
|
LOV Oracle | |
| Set of Books Currency |
|
LOV Oracle | |
| From Period |
|
LOV Oracle | |
| To Period |
|
LOV Oracle |
