FA Annual Additions - draft
Description
Categories: BI Publisher
Application: Assets
Source: Annual Additions Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASANADD_XML
DB package: FA_FASANADD_XMLP_PKG
Source: Annual Additions Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASANADD_XML
DB package: FA_FASANADD_XMLP_PKG
Run
FA Annual Additions - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT &ACCT_FLEX_BAL_SEG COMP_CODE, CB.ASSET_COST_ACCT ASSET_ACCOUNT, AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION ASSET_NUMBER, Round(sum(BOOKS.ORIGINAL_COST * nvl(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS), :PRECISION) ORIGINAL_COST, BOOKS.DATE_PLACED_IN_SERVICE IN_SERVICE, fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP FROM FA_CATEGORY_BOOKS CB, FA_ASSET_HISTORY AH, FA_ADDITIONS AD, FA_BOOK_CONTROLS BC, FA_TRANSACTION_HEADERS THADD, FA_TRANSACTION_HEADERS THTRANSIN, FA_BOOKS BOOKS, GL_CODE_COMBINATIONS DHCC, FA_DISTRIBUTION_HISTORY DH WHERE BOOKS.BOOK_TYPE_CODE = :P_BOOK AND BOOKS.DATE_PLACED_IN_SERVICE >= :P_START_DATE AND BOOKS.DATE_PLACED_IN_SERVICE <= :P_END_DATE AND THADD.TRANSACTION_TYPE_CODE = 'ADDITION'AND THADD.BOOK_TYPE_CODE = :P_BOOK AND THADD.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN AND AD.ASSET_ID = BOOKS.ASSET_ID AND AH.ASSET_ID = BOOKS.ASSET_ID AND AH.DATE_EFFECTIVE <= THADD.DATE_EFFECTIVE AND nvl(AH.DATE_INEFFECTIVE,sysdate)> THADD.DATE_EFFECTIVE AND CB.CATEGORY_ID = AH.CATEGORY_ID AND CB.BOOK_TYPE_CODE = :P_BOOK AND DH.BOOK_TYPE_CODE = :Distribution_Source_Book AND DH.ASSET_ID = THADD.ASSET_ID AND THTRANSIN.BOOK_TYPE_CODE = THADD.BOOK_TYPE_CODE AND THTRANSIN.ASSET_ID = THADD.ASSET_ID AND THTRANSIN.TRANSACTION_TYPE_CODE = decode(BC.BOOK_CLASS,'CORPORATE', 'TRANSFER IN','ADDITION') AND THTRANSIN.DATE_EFFECTIVE >= DH.DATE_EFFECTIVE AND THTRANSIN.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE,SYSDATE) AND THTRANSIN.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE AND DH.ASSET_ID NOT IN (Select thx.asset_id From fa_transaction_headers thx, fa_transaction_headers thadd2, fa_asset_history ahx, fa_transaction_headers thtrans Where thtrans.asset_id = ahx.asset_id and thtrans.transaction_type_code in ('TRANSFER IN', 'TRANSFER IN/VOID', 'UNIT ADJUSTMENT', 'TRANSFER') and thtrans.book_type_code = thx.book_type_code and thtrans.date_effective between ahx.date_effective and nvl(ahx.date_ineffective, sysdate) and ahx.asset_id = dh.asset_id and ahx.asset_type = 'CIP' and thx.transaction_header_id < thadd2.transaction_header_id and thadd2.book_type_code = thx.book_type_code and thadd2.asset_id = thx.asset_id and thadd2.transaction_type_code = 'ADDITION' and thx.asset_id = thadd.asset_id and thx.book_type_code = thadd.book_type_code and thx.transaction_type_code in ('UNIT ADJUSTMENT', 'TRANSFER IN', 'TRANSFER IN/VOID', 'TRANSFER') ) AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID GROUP BY &ACCT_FLEX_BAL_SEG,fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), CB.ASSET_COST_ACCT, AD.ASSET_NUMBER, AD.DESCRIPTION, BOOKS.DATE_PLACED_IN_SERVICE UNION Select &ACCT_FLEX_BAL_SEG, CB.ASSET_COST_ACCT, AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION, Round(sum(BOOKS.ORIGINAL_COST * nvl(DH.UNITS_ASSIGNED,AH.UNITS) / AH.UNITS),:PRECISION) , BOOKS.DATE_PLACED_IN_SERVICE, fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP FROM FA_CATEGORY_BOOKS CB, FA_ASSET_HISTORY AH, FA_ADDITIONS AD, FA_TRANSACTION_HEADERS THADD, FA_BOOKS BOOKS, GL_CODE_COMBINATIONS DHCC, FA_DISTRIBUTION_HISTORY DH WHERE BOOKS.BOOK_TYPE_CODE = :P_BOOK AND BOOKS.DATE_PLACED_IN_SERVICE >= :P_START_DATE AND BOOKS.DATE_PLACED_IN_SERVICE <= :P_END_DATE AND THADD.TRANSACTION_TYPE_CODE = 'ADDITION' AND THADD.BOOK_TYPE_CODE = :P_BOOK AND THADD.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN AND AD.ASSET_ID = BOOKS.ASSET_ID AND AH.ASSET_ID = BOOKS.ASSET_ID AND AH.DATE_EFFECTIVE <= THADD.DATE_EFFECTIVE AND nvl(AH.DATE_INEFFECTIVE,sysdate)> THADD.DATE_EFFECTIVE AND CB.CATEGORY_ID = AH.CATEGORY_ID AND CB.BOOK_TYPE_CODE = :P_BOOK AND DH.ASSET_ID = AD.ASSET_ID AND EXISTS ( Select NULL From fa_transaction_headers thx, fa_transaction_headers thadd2, fa_asset_history ahx, fa_transaction_headers thtrans, fa_distribution_history dhx Where dh.transaction_header_id_in = thx.transaction_header_id and thadd2.asset_id = dh.asset_id and ahx.asset_id = thadd2.asset_id and thtrans.asset_id = ahx.asset_id and dhx.asset_id = thadd2.asset_id and thadd2.asset_id = thx.asset_id and thtrans.transaction_type_code in ('TRANSFER IN', 'TRANSFER IN/VOID', 'UNIT ADJUSTMENT', 'TRANSFER','RECLASS') and thtrans.book_type_code = thx.book_type_code and thtrans.date_effective between ahx.date_effective and nvl(ahx.date_ineffective, sysdate) and ahx.asset_type = 'CIP' and thx.transaction_header_id < thadd2.transaction_header_id and thx.date_effective between dhx.date_effective and nvl(dhx.date_ineffective, sysdate) and dhx.asset_id = dh.asset_id and thadd2.date_effective between dhx.date_effective and nvl(dhx.date_ineffective, sysdate) and thadd2.book_type_code = thx.book_type_code and thadd2.transaction_type_code = 'ADDITION' and thx.asset_id = thadd.asset_id and thx.book_type_code = thadd.book_type_code and thx.transaction_type_code in ('UNIT ADJUSTMENT', 'TRANSFER IN', 'TRANSFER IN/VOID', 'TRANSFER','RECLASS') ) and exists (Select 1 From FA_TRANSACTION_HEADERS THDIS, FA_TRANSACTION_HEADERS THDIS2 Where DH.TRANSACTION_HEADER_ID_IN = THDIS.TRANSACTION_HEADER_ID AND ( (DH.TRANSACTION_HEADER_ID_OUT IS NULL AND THDIS2.TRANSACTION_HEADER_ID = DH.TRANSACTION_HEADER_ID_IN) OR ( (DH.TRANSACTION_HEADER_ID_OUT IS NOT NULL AND THDIS.TRANSACTION_TYPE_CODE in ('TRANSFER IN', 'TRANSFER', 'UNIT ADJUSTMENT', 'RECLASS') AND DH.TRANSACTION_HEADER_ID_OUT = THDIS2.TRANSACTION_HEADER_ID AND THDIS2.TRANSACTION_TYPE_CODE not in ('TRANSFER OUT')) AND (DH.TRANSACTION_HEADER_ID_OUT IS NOT NULL and dh.distribution_id in (select dhx.distribution_id from fa_distribution_history dhx, fa_transaction_headers theff where theff.transaction_type_code = 'ADDITION' and theff.book_type_code = :P_BOOK and theff.asset_id = dh.asset_id and theff.date_effective between dhx.date_effective and nvl(dhx.date_ineffective,sysdate) and dhx.book_type_code = dh.book_type_code and dhx.asset_id = dh.asset_id ) AND THDIS.TRANSACTION_TYPE_CODE in ('TRANSFER IN', 'TRANSFER', 'UNIT ADJUSTMENT','RECLASS') AND DH.TRANSACTION_HEADER_ID_OUT = THDIS2.TRANSACTION_HEADER_ID AND THDIS2.TRANSACTION_TYPE_CODE not in ('TRANSFER OUT')) ) OR (DH.TRANSACTION_HEADER_ID_OUT IS NOT NULL AND THDIS.TRANSACTION_TYPE_CODE = 'TRANSFER IN/VOID' AND DH.TRANSACTION_HEADER_ID_OUT = THDIS2.TRANSACTION_HEADER_ID AND THDIS2.TRANSACTION_TYPE_CODE not in ('UNIT ADJUSTMENT', 'TRANSFER', 'TRANSFER OUT', 'TRANSFER IN', 'RECLASS','TRANSFER IN/VOID')) ) ) AND DH.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID GROUP BY &ACCT_FLEX_BAL_SEG,fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), CB.ASSET_COST_ACCT, AD.ASSET_NUMBER, AD.DESCRIPTION, BOOKS.DATE_PLACED_IN_SERVICE ORDER BY 1,2,3 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Book |
|
LOV Oracle | |
From Date |
|
Date | |
To Date |
|
Date |