FA Asset Inventory

➜ Back to Library
Description
Categories: Enginatics
Repository: Github Columns: Company Name, Book, Current Period, Company, Department, Owner, Location, Asset - Description, Units, Serial Number ...
Imported Oracle standard asset inventory report
Source: Asset Inventory Report (XML)
Short Name: FAS410_XML
DB package: FA_FAS410_XMLP_PKG
select
 x.Company_Name
,x.book
,x.Cur_Period "Current Period"
,x.d_comp_code1 "&balancing_segment_p"
,x.d_cost_ctr1      "&cost_center_p"
,x.owner
,x.d_location1     "Location"
,x.asset           "Asset - Description"
,x.units
,x.serial          "Serial Number"
,x.tag             "Tag Number"
,FA_FAS410_XMLP_PKG.as_nbvformula(x.COST, x.RESERVE) "Net Book Value"
,x.cost            "Current Cost"
,case when x.new > 0
   then 'New'
   else null
   end             "New"
,x.asset_type
from
(
select
 fsc.company_name,
 fsc.book,
 fsc.Cur_Period,
 EMP.FULL_NAME				OWNER,
 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION		ASSET,
 sum(DH.UNITS_ASSIGNED)				UNITS,
 AD.SERIAL_NUMBER				SERIAL,
 AD.TAG_NUMBER				TAG,
 sum(decode(dd.deprn_source_code,'B', dd.addition_cost_to_clear, dd.cost))		COST,
 sum(dd.deprn_reserve)				RESERVE,
 decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE,NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
       ,least(BOOKS.DATE_PLACED_IN_SERVICE, NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
       ,1,0)                                               NEW,
 decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E','')		ASSET_TYPE,
 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1,
 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1,
 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1
FROM
    (
     SELECT
       Company_Name,
       Category_Flex_Structure,
       Location_Flex_Structure,
       Asset_Key_Flex_Structure,
	     FA_FAS410_XMLP_PKG.bookformula() Book,
	     FA_FAS410_XMLP_PKG.cur_periodformula(FA_FAS410_XMLP_PKG.bookformula()) Cur_Period,
	     FA_FAS410_XMLP_PKG.report_nameformula() Report_Name,
	     FA_FAS410_XMLP_PKG.Accounting_Flex_Structure_p Accounting_Flex_Structure,
	     FA_FAS410_XMLP_PKG.Currency_Code_p Currency_Code,
	     FA_FAS410_XMLP_PKG.Book_Class_p Book_Class,
	     FA_FAS410_XMLP_PKG.Distribution_Source_Book_p Distribution_Source_Book,
	     FA_FAS410_XMLP_PKG.Cur_Period_PC_p Cur_Period_PC
	   FROM FA_SYSTEM_CONTROLS
    ) fsc,
    fa_deprn_detail         dd,
    per_people_f            emp,
    fa_additions            ad,
    fa_locations            loc,
    gl_code_combinations    cc,
    fa_books                books,
    fa_distribution_history dh
WHERE
    dh.book_type_code      = :P_BOOK    AND
    dh.assigned_to         = emp.person_id(+)      AND
    dh.date_effective between nvl(emp.effective_start_date,dh.date_effective)
                                        and nvl(emp.effective_end_date,dh.date_effective) AND
    fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') between
    NVL(:P_START_CC,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'))  AND
    NVL(:P_END_CC ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')) AND
    dh.asset_id            = ad.asset_id            AND
    dh.location_id         = loc.location_id        AND
    dh.code_combination_id = cc.code_combination_id AND
    dh.date_ineffective is null AND
    dd.asset_id = dh.asset_id             AND
    dd.book_type_code = dh.book_type_code      AND
    dd.distribution_id = dh.distribution_id      AND
    dd.period_counter =
     (SELECT    max (dd2.period_counter)
      FROM    fa_deprn_detail dd2
      WHERE	dd2.book_type_code = books.book_type_code and
            dd2.asset_id = books.asset_id and
            dd2.distribution_id = dd.distribution_id
     ) AND
   books.book_type_code  = :P_BOOK        AND
   books.asset_id = dh.asset_id and
   books.date_placed_in_service between nvl(:P_FROM_DATE, books.date_placed_in_service) and nvl(:P_TO_DATE, books.date_placed_in_service) AND
   books.period_counter_fully_retired is null   AND
   books.date_ineffective is null
group by
   fsc.company_name,
   fsc.book,
   fsc.Cur_Period,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
   fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
   emp.full_name,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
   AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
   dh.units_assigned,
   ad.serial_number,
   ad.tag_number,
   decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E',''),
   decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE,NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
         ,least(BOOKS.DATE_PLACED_IN_SERVICE,NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
         ,1, 0),
   decode(dd.deprn_source_code,'B', dd.addition_cost_to_clear, dd.cost),
   dd.deprn_reserve
UNION ALL
select
 fsc.company_name,
 fsc.book,
 fsc.Cur_Period,
 EMP.FULL_NAME				OWNER,
 AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION		ASSET,
 sum(decode(LU.LOOKUP_CODE
           ,'ADDITION COST',decode(adj.source_type_code
                                  ,'TRANSFER',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED
                                  ,'RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED
                                  ,'CIP RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED
                                  ,'RECLASS',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED,0
                                  )
                           ,0
           )
     )					UNITS,
 AD.SERIAL_NUMBER				SERIAL,
 AD.TAG_NUMBER				TAG,
 sum(DECODE(LU.LOOKUP_CODE,
 'ADDITION COST',
 DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) * ADJ.ADJUSTMENT_AMOUNT,0))		COST,
 sum(DECODE(LU.LOOKUP_CODE
           ,'DEPRECIATION RESERVE', DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 1) * ADJ.ADJUSTMENT_AMOUNT
           , 0)
    )	RESERVE,
 decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE,NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
       ,least(BOOKS.DATE_PLACED_IN_SERVICE,NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
       ,1, 0)                NEW,
 decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E','')		ASSET_TYPE,
 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1,
 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1,
 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1
FROM
    (
     SELECT
       Company_Name,
       Category_Flex_Structure,
       Location_Flex_Structure,
       Asset_Key_Flex_Structure,
	     FA_FAS410_XMLP_PKG.bookformula() Book,
	     FA_FAS410_XMLP_PKG.cur_periodformula(FA_FAS410_XMLP_PKG.bookformula()) Cur_Period,
	     FA_FAS410_XMLP_PKG.report_nameformula() Report_Name,
	     FA_FAS410_XMLP_PKG.Accounting_Flex_Structure_p Accounting_Flex_Structure,
	     FA_FAS410_XMLP_PKG.Currency_Code_p Currency_Code,
	     FA_FAS410_XMLP_PKG.Book_Class_p Book_Class,
	     FA_FAS410_XMLP_PKG.Distribution_Source_Book_p Distribution_Source_Book,
	     FA_FAS410_XMLP_PKG.Cur_Period_PC_p Cur_Period_PC
	   FROM FA_SYSTEM_CONTROLS
    ) fsc,
    per_people_f            emp,
    fa_additions            ad,
    fa_locations            loc,
    gl_code_combinations    cc,
    fa_books                books,
    fa_adjustments	adj,
    fa_distribution_history dh,
    fa_lookups 	lu
WHERE
  dh.book_type_code      = :P_BOOK    AND
  dh.assigned_to         = emp.person_id(+)      AND
  dh.date_effective between nvl(emp.effective_start_date,dh.date_effective)
                                       and   nvl(emp.effective_end_date,dh.date_effective)  AND
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') between
	NVL(:P_START_CC,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'))  AND
	NVL(:P_END_CC ,  fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')) AND
  dh.asset_id            = ad.asset_id            AND
  dh.location_id         = loc.location_id        AND
  dh.code_combination_id = cc.code_combination_id AND
  dh.date_ineffective is null AND
	lu.lookup_type = 'JOURNAL ENTRIES' and
	(   (adj.adjustment_type in ('COST','CIP COST') and lu.lookup_code = 'ADDITION COST')
	 or (adj.adjustment_type = 'RESERVE' and lu.lookup_code = 'DEPRECIATION RESERVE')
	) AND
	adj.source_type_code not in ('DEPRECIATION','ADDITION', 'CIP ADDITION') and
 	adj.book_type_code = :P_BOOK and
	adj.asset_id = dh.asset_id and
	adj.distribution_id = dh.distribution_id and
	adj.period_counter_created = fsc.CUR_PERIOD_PC AND
  books.book_type_code  = :P_BOOK        AND
  books.asset_id = dh.asset_id and
  books.date_placed_in_service between
  nvl(:P_FROM_DATE, books.date_placed_in_service) and
  nvl(:P_TO_DATE, books.date_placed_in_service) AND
  books.period_counter_fully_retired is null   AND
  books.date_ineffective is null
	group by
 fsc.company_name,
 fsc.book,
 fsc.Cur_Period,
	 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
	 emp.full_name,
	 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
   AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
	 decode(LU.LOOKUP_CODE,'ADDITION COST',decode(adj.source_type_code
	                                             ,'TRANSFER',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED
	                                             ,'RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED
	                                             ,'CIP RETIREMENT',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED
	                                             ,'RECLASS',decode(adj.debit_credit_flag,'DR',1,-1)*DH.UNITS_ASSIGNED
	                                                       ,0)
	                                      ,0),
	ad.serial_number,
	ad.tag_number,
	decode(AD.ASSET_TYPE,'CIP', 'C', 'EXPENSED','E',''),
	decode(greatest(BOOKS.DATE_PLACED_IN_SERVICE,NVL(:P_FROM_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
	      ,	least(BOOKS.DATE_PLACED_IN_SERVICE,NVL(:P_TO_DATE, BOOKS.DATE_PLACED_IN_SERVICE))
	      ,	1, 0),
	DECODE(LU.LOOKUP_CODE
	      ,'ADDITION COST', DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) * ADJ.ADJUSTMENT_AMOUNT
	                      ,0),
  DECODE(LU.LOOKUP_CODE
        ,'DEPRECIATION RESERVE',DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 1) * ADJ.ADJUSTMENT_AMOUNT
                               , 0)
ORDER BY  13, 14, 4, 15, 5
) x
Parameter NameSQL textValidation
Book
 
LOV Oracle
From Cost Center
 
To Cost Center
 
From Date Placed in Service
 
Date
To Date Placed in Service
 
Date
Book
  select fifsv.form_left_prompt 
  from   fa_book_controls bc
     ,   fnd_segment_attribute_values fsav
     ,   fnd_id_flex_segments_vl      fifsv
  where  bc.book_type_code      = :p_book
  and    fsav.application_id    = 101
  and    fsav.id_flex_code      = 'GL#'
  and    fsav.segment_attribute_type = 'GL_BALANCING' 
  and    fsav.id_flex_num       = bc.accounting_flex_structure
  and    fsav.attribute_value   = 'Y'
  and    fifsv.application_id   = fsav.application_id
  and    fifsv.id_flex_code     = fsav.id_flex_code
  and    fifsv.id_flex_num      = fsav.id_flex_num
  and    fifsv.application_column_name = fsav.application_column_name
Book
  select fifsv.form_left_prompt 
  from   fa_book_controls bc
     ,   fnd_segment_attribute_values fsav
     ,   fnd_id_flex_segments_vl      fifsv
  where  bc.book_type_code      = :p_book
  and    fsav.application_id    = 101
  and    fsav.id_flex_code      = 'GL#'
  and    fsav.segment_attribute_type = 'FA_COST_CTR' 
  and    fsav.id_flex_num       = bc.accounting_flex_structure
  and    fsav.attribute_value   = 'Y'
  and    fifsv.application_id   = fsav.application_id
  and    fifsv.id_flex_code     = fsav.id_flex_code
  and    fifsv.id_flex_num      = fsav.id_flex_num
  and    fifsv.application_column_name = fsav.application_column_name
Acct Flex Structure
 
Number
Download
 
Blitz Report In Action
Blitz Report™