FA Asset Summary Report (Germany)

Description
Categories: BI Publisher
Application: Assets
Source: Asset Summary Report (Germany)
Short Name: FASSUMRPT
DB package: FA_ASSET_SUMM_RPT_PKG
SELECT book_name
      ,asset_no
      ,asset_desc
      ,c_cost_account
      ,major_category
      ,minor_category
      ,account
      ,account_desc
      ,cost_center
      ,asset_type
      ,transaction_sub_type
      ,original_cost
	  ,current_cost
      ,(CASE
         WHEN asset_type = 'CIP' THEN current_cost
         ELSE
            CASE
             WHEN changes_of_accounts <= 0 THEN 
               CASE 
                WHEN current_cost >0 THEN current_cost
                ELSE 0
               END
             ELSE 0
            END
        END) current_cost_new
      ,begin_cost
      ,reserve_amount
      ,retirements      
      ,changes_of_accounts
      ,additions
      ,cost_adjustments
      ,appreciation_amount
      ,accum_deprn      
      ,deprn_expenses    
	  ,(CASE 
	      WHEN asset_type = 'CAPITALIZED' THEN accum_deprn
		  ELSE 0
	    END ) accum_deprn_new
	  ,(CASE 
	      WHEN asset_type = 'CAPITALIZED' THEN deprn_expenses
		  ELSE 0
	    END ) deprn_expenses_new
	  ,(current_cost+additions+changes_of_accounts-retirements-accum_deprn)	NBV_Ending_Period
	  ,(current_cost-reserve_amount) NBV_Beginning_Period
 FROM (SELECT book_name
             ,asset_desc
             ,c_cost_account
             ,major_category
             ,minor_category
             ,account
             ,account_desc
             ,cost_center
             ,asset_type
             ,transaction_sub_type
             ,sum(nvl(original_cost,0))original_cost
             ,SUM(nvl(current_cost,0)) current_cost
             ,SUM(nvl(begin_cost,0)) begin_cost
             ,sum(nvl(reserve_amount,0)) reserve_amount
             ,asset_no
             ,SUM(nvl(retirements,0)) retirements      
             ,SUM(nvl(changes_of_accounts,0)) changes_of_accounts
             ,SUM(nvl(additions,0))  additions
             ,SUM(nvl(additions,0)) cost_adjustments
             ,SUM(nvl(appreciation_amount,0)) appreciation_amount
             ,SUM(nvl(accum_deprn,0)) accum_deprn      
             ,SUM(nvl(deprn_expenses,0)) deprn_expenses     
       FROM (SELECT distinct fbc.book_type_code      book_name
               ,fadd.asset_number         asset_no
               ,fadd.description         asset_desc
               ,DECODE(fah.asset_type, 'CIP', NVL(fcb.cip_cost_acct,fcb.asset_cost_acct)
                                            ,fcb.asset_cost_acct)         c_cost_account
               ,fc.segment1            major_category
               ,fc.segment2            minor_category
               ,fnd_flex_xml_publisher_apis.process_kff_combination_1('nat_acct_seg', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')          account
               ,fnd_flex_xml_publisher_apis.process_kff_combination_1('nat_acct_seg_desc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION')     account_desc
               ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_seg', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE')          cost_center
               ,fah.asset_type            asset_type
                   ,null transaction_sub_type
               ,FA_ASSET_SUMM_RPT_PKG.assigned_units(fadd.asset_id
                                                     ,FC.category_id
                                                     ,FAH.asset_type
                                                     ,gcc.code_combination_id) original_cost
               ,FA_ASSET_SUMM_RPT_PKG.CURRENT_AMOUNT(fadd.asset_id
                                                     ,FC.category_id
                                                     ,FAH.asset_type
                                                     ,gcc.code_combination_id) current_cost
               ,FA_ASSET_SUMM_RPT_PKG.BEGIN_COST(fadd.asset_id
                                                     ,FC.category_id
                                                     ,FAH.asset_type
                                                     ,gcc.code_combination_id) begin_cost
                ,FA_ASSET_SUMM_RPT_PKG.RESERVE_AMOUNT(fadd.asset_id
                                                     ,FC.category_id
                                                     ,FAH.asset_type
                                                     ,gcc.code_combination_id) reserve_amount        
               ,FA_ASSET_SUMM_RPT_PKG.RETIREMENT_AMOUNT(FADD.asset_id
                                                        ,FC.category_id
                                                        ,FAH.asset_type
                                                        ,GCC.code_combination_id)retirements        
               ,FA_ASSET_SUMM_RPT_PKG.CHANGES_OF_ACCOUNTS(FADD.asset_id
                                                          ,FC.category_id
                                                          ,FAH.asset_type
                                                          ,GCC.code_combination_id
                                                          ,fah.transaction_header_id_in) changes_of_accounts
               ,FA_ASSET_SUMM_RPT_PKG.ADDITIONS_AMOUNT(FADD.asset_id
                                                      ,FC.category_id
                                                      ,FAH.asset_type
                                                      ,gcc.code_combination_id
                                                      ,fah.transaction_header_id_in)additions               
               ,FA_ASSET_SUMM_RPT_PKG.APPRECIATION_AMOUNT(FADD.asset_id
                                                          ,FC.category_id
                                                          ,FAH.asset_type
                                                          ,GCC.code_combination_id) appreciation_amount
               ,FA_ASSET_SUMM_RPT_PKG.ACCM_DEPRN_AMT(FADD.asset_id
                                                     ,GCC.code_combination_id
                                                     ,FAH.asset_type
                                                     ,FC.category_id)accum_deprn        
               ,FA_ASSET_SUMM_RPT_PKG.DEPRN_EXPENSE(FADD.asset_id
                                                    ,GCC.code_combination_id
                                                    ,FAH.asset_type
                                                    ,FC.category_id)deprn_expenses 
               ,fb.cost       latest_book_cost
               from  fa_asset_history        fah
                     ,gl_code_combinations    gcc       
                     ,fa_categories           fc
                     ,fa_category_books       fcb
                     ,fa_additions            fadd        
                     ,fa_deprn_periods        fdp1
                     ,fa_deprn_periods        fdp2
                    -- ,fa_deprn_periods        fdp
                     ,fa_book_controls        fbc
                     --,fa_transaction_headers fth        
                     ,fa_books fb
		     ,fa_system_controls FSC
                     ,fnd_id_flex_structures fifs
         where fah.asset_id = fadd.asset_id
         and   fbc.book_type_code = :P_BOOK_NAME
         and   fbc.book_type_code = fb.book_type_code
       --  and   fb.date_ineffective is null
         and   fb.asset_id = fadd.asset_id
		 and   fb.transaction_header_id_in = (select max(fb2.transaction_header_id_in)
                                        from fa_books fb2
										where fb2.book_type_code = fb.book_type_code
                                        and   fb2.asset_id  = fb.asset_id
										and   fb2.date_effective <= fdp2.period_close_date)
		 and   nvl(fb.period_counter_fully_retired,fdp1.period_counter + 1) >= fdp1.period_counter
         and   fbc.book_type_code =  fcb.book_type_code
         and   fah.category_id = fcb.category_id
         and   fah.category_id = fc.category_id
         and   gcc.code_combination_id = DECODE(fah.asset_type, 'CIP', NVL(fcb.wip_cost_account_ccid,fcb.asset_cost_account_ccid)
                                            ,fcb.asset_cost_account_ccid)  
         and   fbc.book_type_code =  fdp1.book_type_code
         and   fdp1.book_type_code = fdp2.book_type_code
           -- and   fdp1.book_type_code = fdp.book_type_code
         and   fdp1.period_name = :P_BEGIN_PERIOD
         and   fdp2.period_name = :P_END_PERIOD
        -- and   fdp.period_counter between fdp1.period_counter and fdp2.period_counter
         and    ((fah.date_effective between fdp1.period_open_date and  fdp2.period_close_date) OR
                (fah.date_effective <  fdp1.period_open_date and fah.date_ineffective > fdp2.period_close_date ) OR
                (fah.date_ineffective is null and fah.date_effective <= fdp2.period_close_date) OR
                (fah.date_ineffective between fdp1.period_open_date and fdp2.period_close_date and fah.date_effective < fdp1.period_open_date))  
         and   FSC.category_flex_structure = fifs.id_flex_num
         AND   fifs.id_flex_code = 'CAT#'  
         and   ((fc.segment1||fifs.concatenated_segment_delimiter
                 ||fc.segment2 between :P_FROM_CATEGORY and :P_TO_CATEGORY and :P_FROM_CATEGORY is not null and :P_TO_CATEGORY is not null) or 
               (:P_FROM_CATEGORY is  null and :P_TO_CATEGORY  is  null) OR
	        (fc.segment1 between :P_FROM_CATEGORY and :P_TO_CATEGORY  and :P_FROM_CATEGORY is not null and :P_TO_CATEGORY is not null AND fc.segment2 IS NULL)OR
		(fc.segment2 between :P_FROM_CATEGORY and :P_TO_CATEGORY  and :P_FROM_CATEGORY is not null and :P_TO_CATEGORY is not null AND fc.segment1 IS NULL))
         and   ((:gc_lex_acct_seg_num between :P_FROM_ACCOUNT and :P_TO_ACCOUNT and :P_FROM_ACCOUNT is not null and :P_TO_ACCOUNT is not null) or
               (:P_TO_ACCOUNT is null and :P_FROM_ACCOUNT is null))
        )
GROUP BY book_name
      ,asset_no
      ,asset_desc
      ,c_cost_account
      ,major_category
      ,minor_category
      ,account
      ,account_desc
      ,cost_center
      ,asset_type
     ,transaction_sub_type
      ,original_cost )
ORDER BY account
        ,major_category
        ,minor_category
        ,account_desc
        ,asset_type
        ,cost_center
        ,asset_no
Parameter Name SQL text Validation
Chart of Accounts ID
 
Number
Category Flex Structure
 
Number
Asset Details (Y/N)
 
LOV Oracle
To Account
 
From Account
 
To Category
 
From Category
 
To Period
 
LOV Oracle
From Period
 
LOV Oracle
Book Name
 
LOV Oracle
Ask a question