ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 58DF54D18E514BF7B596C48E0909204AY select x.ledger, x.book, x.currency, x.period, x.asset_number, x.asset_description, x.tag_number, x.serial_number, x.asset_type, x.category, x.transfer_date, x.transaction_number, xxen_util.meaning(x.side,'GLJAHE',101) side, x.units, papf.full_name employee_name, papf.employee_number, gcck.concatenated_segments expense_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg','SQLGL','GL#',gcck.chart_of_accounts_id,null,gcck.code_combination_id,'FA_COST_CTR','Y','VALUE') cost_center, flk.concatenated_segments location, y.cost, y.reserve, y.cost-y.reserve net_book_value, y.depreciation_expense, y.revaluation_reserve, x.comments, xxen_util.user_name(x.last_updated_by) entered_by, x.entered_date from ( select gsob.name ledger, fbc.book_type_code book, gsob.currency_code currency, fdp.period_name period, fa.asset_number, fa.description asset_description, fa.tag_number, fa.serial_number, (select fl.meaning from fa_lookups fl where fl.lookup_type='ASSET TYPE' and fa.asset_type=fl.lookup_code) asset_type, fcbk.concatenated_segments category, fth.transaction_date_entered transfer_date, fth.transaction_header_id transaction_number, fth.transaction_name comments, fth.last_updated_by, fth.date_effective entered_date, fdh.side, fdh.distribution_id, decode(fdh.side,'FROM',-1,1)*fdh.units_assigned units, fdh.assigned_to, fdh.code_combination_id, fdh.location_id from fa_book_controls_sec fbc, gl_sets_of_books gsob, fa_transaction_headers fth, fa_deprn_periods fdp, fa_additions fa, fa_categories_b_kfv fcbk, ( select 'FROM' side, fdh.transaction_header_id_out transaction_header_id, fdh.distribution_id, fdh.units_assigned, fdh.assigned_to, fdh.code_combination_id, fdh.location_id from fa_distribution_history fdh union all select 'TO' side, fdh.transaction_header_id_in transaction_header_id, fdh.distribution_id, fdh.units_assigned, fdh.assigned_to, fdh.code_combination_id, fdh.location_id from fa_distribution_history fdh ) fdh where 1=1 and fbc.set_of_books_id=gsob.set_of_books_id and fbc.distribution_source_book=fth.book_type_code and fth.transaction_type_code='TRANSFER' and fbc.book_type_code=fdp.book_type_code and fth.date_effective>=fdp.period_open_date and fth.date_effective<=nvl(fdp.period_close_date,sysdate) and fth.asset_id=fa.asset_id and fa.asset_category_id=fcbk.category_id and fth.transaction_header_id=fdh.transaction_header_id ) x, ( select fadj.transaction_header_id, fadj.distribution_id, fadj.book_type_code, sum(case when fadj.adjustment_type in ('COST','CIP COST') then decode(fadj.debit_credit_flag,'DR',1,-1)*fadj.adjustment_amount else 0 end) cost, sum(case when fadj.adjustment_type='RESERVE' then decode(fadj.debit_credit_flag,'CR',1,-1)*fadj.adjustment_amount else 0 end) reserve, sum(case when fadj.adjustment_type='EXPENSE' then decode(fadj.debit_credit_flag,'DR',1,-1)*fadj.adjustment_amount else 0 end) depreciation_expense, sum(case when fadj.adjustment_type='REVAL RESERVE' then decode(fadj.debit_credit_flag,'CR',1,-1)*fadj.adjustment_amount else 0 end) revaluation_reserve from fa_adjustments fadj where fadj.source_type_code='TRANSFER' group by fadj.transaction_header_id, fadj.distribution_id, fadj.book_type_code ) y, per_all_people_f papf, gl_code_combinations_kfv gcck, fa_locations_kfv flk where x.transaction_number=y.transaction_header_id(+) and x.distribution_id=y.distribution_id(+) and x.book=y.book_type_code(+) and x.assigned_to=papf.person_id(+) and x.entered_date>=papf.effective_start_date(+) and x.entered_date<papf.effective_end_date(+)+1 and x.code_combination_id=gcck.code_combination_id and x.location_id=flk.location_id order by x.book, x.asset_number, x.transfer_date, x.transaction_number, x.side desc New report. US FA Asset Transfers Asset transfers between distributions (employee, expense account and location), one row per transfer and distribution side. A transfer closes the From distributions and creates the To distributions. Units, cost and reserve are signed, negative on the From side and positive on the To side, so a transfer nets to zero and a sum by cost center, account or location shows what moved in and out. Cost, reserve, depreciation expense and revaluation reserve are the amounts the transfer moved between the distributions in the selected book, taken from its accounting adjustments. A back-dated transfer also moves the depreciation expense of the periods it reaches back to. A partial unit transfer closes the From distribution and recreates it with the remaining units, so the remainder is listed on the To side as well and nets against the closed distribution. Enginatics 1=1 1 10 1=1 gsob.name=:ledger LOV GL Ledger (Asset Book Secured) 05FCF30F9B56017AE0630100007F6C53 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select set_of_books_id from fa_book_controls_sec) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name US Ledger 2 20 1=1 fbc.book_type_code=:book LOV FA Book B12587B17F666485E0530100007FA7F4 Y select x.value, x.description from ( select fbcs.book_type_code value, fbcs.book_type_name||' ('||fbcs.book_class||')' description from ( select fbc.* from fa_book_controls fbc where fbc.book_type_code in (select xroa.code from xxen_report_org_access xroa where xroa.access_type='FA_BOOK') ) fbcs where :$flex$.ledger is null or fbcs.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y') and nvl(:$flex$.mrc_flag,'P') != 'R' union select fbcs.book_type_code value, fbcs.book_type_name||' ('||fbcs.book_class||')' description from ( select fbc.* from fa_book_controls fbc where fbc.book_type_code in (select xroa.code from xxen_report_org_access xroa where xroa.access_type='FA_BOOK') ) fbcs, fa_mc_book_controls fmbc where fbcs.book_type_code = fmbc.book_type_code and fbcs.set_of_books_id = fmbc.primary_set_of_books_id and :$flex$.ledger is null or fmbc.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y') and nvl(:$flex$.mrc_flag,'P') = 'R' ) x order by x.value Y US Book For a tax book, the transfers of its corporate book are listed with the tax book's cost and reserve. 3 30 1=1 fth.date_effective>=(select fdp2.period_open_date from fa_deprn_periods fdp2 where fdp2.book_type_code=fbc.book_type_code and fdp2.period_name=:period_from) LOV FA Period B12835F5EC2F6F85E0530100007F7410 Y select x.value, x.description, x.fiscal_year, x.period_num from ( select fdp.period_name value, fbcs.deprn_calendar || ': ' || fdp.fiscal_year||' - '||fdp.period_num description, fdp.fiscal_year, fdp.period_num, fbcs.deprn_calendar from fa_book_controls_sec fbcs, fa_deprn_periods fdp where fbcs.book_type_code = fdp.book_type_code and (:$flex$.ledger is null or fdp.book_type_code in (select fbcs.book_type_code from gl_ledgers gl, fa_book_controls_sec fbcs where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcs.set_of_books_id)) and (:$flex$.asset_book is null or xxen_util.contains(:$flex$.asset_book,fdp.book_type_code)='Y') and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y') and fdp.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and nvl(:$flex$.mrc_flag,'P') != 'R' union select fdpmv.period_name value, fbcs.deprn_calendar || ': ' || fdpmv.fiscal_year||' - '||fdpmv.period_num description, fdpmv.fiscal_year, fdpmv.period_num, fbcs.deprn_calendar from fa_book_controls_sec fbcs, fa_deprn_periods_mrc_v fdpmv where fbcs.book_type_code = fdpmv.book_type_code and (:$flex$.ledger is null or fdpmv.book_type_code in (select fbcmv.book_type_code from gl_ledgers gl, fa_book_controls_mrc_v fbcmv where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcmv.set_of_books_id)) and (:$flex$.asset_book is null or xxen_util.contains(:$flex$.asset_book,fdpmv.book_type_code)='Y') and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fdpmv.book_type_code)='Y') and fdpmv.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and nvl(:$flex$.mrc_flag,'P') = 'R' ) x order by x.fiscal_year desc, x.period_num desc, x.deprn_calendar US Period From Depreciation period in which the transfer was entered 4 40 1=1 fth.date_effective<=(select nvl(fdp2.period_close_date,sysdate) from fa_deprn_periods fdp2 where fdp2.book_type_code=fbc.book_type_code and fdp2.period_name=:period_to) LOV FA Period B12835F5EC2F6F85E0530100007F7410 Y select x.value, x.description, x.fiscal_year, x.period_num from ( select fdp.period_name value, fbcs.deprn_calendar || ': ' || fdp.fiscal_year||' - '||fdp.period_num description, fdp.fiscal_year, fdp.period_num, fbcs.deprn_calendar from fa_book_controls_sec fbcs, fa_deprn_periods fdp where fbcs.book_type_code = fdp.book_type_code and (:$flex$.ledger is null or fdp.book_type_code in (select fbcs.book_type_code from gl_ledgers gl, fa_book_controls_sec fbcs where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcs.set_of_books_id)) and (:$flex$.asset_book is null or xxen_util.contains(:$flex$.asset_book,fdp.book_type_code)='Y') and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y') and fdp.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and nvl(:$flex$.mrc_flag,'P') != 'R' union select fdpmv.period_name value, fbcs.deprn_calendar || ': ' || fdpmv.fiscal_year||' - '||fdpmv.period_num description, fdpmv.fiscal_year, fdpmv.period_num, fbcs.deprn_calendar from fa_book_controls_sec fbcs, fa_deprn_periods_mrc_v fdpmv where fbcs.book_type_code = fdpmv.book_type_code and (:$flex$.ledger is null or fdpmv.book_type_code in (select fbcmv.book_type_code from gl_ledgers gl, fa_book_controls_mrc_v fbcmv where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcmv.set_of_books_id)) and (:$flex$.asset_book is null or xxen_util.contains(:$flex$.asset_book,fdpmv.book_type_code)='Y') and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fdpmv.book_type_code)='Y') and fdpmv.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and nvl(:$flex$.mrc_flag,'P') = 'R' ) x order by x.fiscal_year desc, x.period_num desc, x.deprn_calendar :$flex$.period_from US Period To 5 50 1=1 fth.transaction_date_entered>=:transfer_date_from Date US Transfer Date From 6 60 1=1 fth.transaction_date_entered<:transfer_date_to+1 Date :$flex$.transfer_date_from US Transfer Date To 7 70 1=1 fa.asset_number=:asset_number LOV FA Asset Number 53CBABC9B7B42FBCE0630100007F5445 Y Y select fa.asset_number value, fa.description description from fa_additions fa, fa_books fb where fa.asset_id=fb.asset_id and fb.date_ineffective is null and fb.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and (:$flex$.ledger is null or fb.book_type_code in (select fbcs.book_type_code from gl_ledgers gl, fa_book_controls_sec fbcs where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcs.set_of_books_id)) and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fb.book_type_code)='Y') order by fa.asset_number US Asset Number 8 80 1=1 fcbk.concatenated_segments=:category LOV FA Category ED83C3588D118586E0530100007FB788 Y select fcbk.concatenated_segments value, null description from fa_categories_b_kfv fcbk where (nvl(:$flex$.book,:$flex$.asset_book) is null and (:$flex$.ledger_id is null or fcbk.category_id in (select fcb.category_id from gl_ledgers gl, fa_book_controls_sec fbcs, fa_category_books fcb where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcs.set_of_books_id and fbcs.book_type_code = fcb.book_type_code) ) ) or fcbk.category_id in (select fcb.category_id from fa_category_books fcb where xxen_util.contains(nvl(:$flex$.book,:$flex$.asset_book),fcb.book_type_code)='Y') order by fcbk.concatenated_segments US Category AEB129666456447BA312037077957E17 Transfers by Cost Center Asset transfers summarized by From and To cost center: units and cost moved between cost centers, with the transfer details by expense account. SYSADMIN 6 ASSET_DESCRIPTION 5 ASSET_NUMBER 2 1 BOOK 12 COST 4 COST_CENTER 10 EMPLOYEE_NAME 3 EXPENSE_ACCOUNT 1 11 LOCATION 14 NET_BOOK_VALUE 2 PERIOD 13 RESERVE 8 SIDE 4 7 TRANSFER_DATE 3 9 UNITS BOOK FILTER 1 COST VALUE 2 SUM COST_CENTER ROW 1 SIDE COLUMN 1 UNITS VALUE 1 SUM S Site