ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US A1B2C3D4E5F6071829A1B2C3D4E5F607Y select null action_, null status_, null message_, null modified_columns_, 'Update' operation, xma.book_type_code book, xma.invoice_number, xma.ap_distribution_line_number distribution_line, xma.invoice_line_number invoice_line, xxen_util.meaning(xma.queue_name,'QUEUE NAME',140) queue, xma.description, (select falu.meaning from fa_lookups falu where falu.lookup_type='ASSET TYPE' and falu.lookup_code=xma.asset_type) asset_type, (select fcbk.concatenated_segments from fa_categories_b_kfv fcbk where fcbk.category_id=xma.asset_category_id) category, xma.asset_number, xma.fixed_assets_units units, xma.fixed_assets_cost cost, xma.date_placed_in_service, (select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id=xma.expense_code_combination_id) expense_account, (select flk.concatenated_segments from fa_locations_kfv flk where flk.location_id=xma.location_id) location, (select papf.employee_number from per_all_people_f papf where papf.person_id=xma.assigned_to and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date) employee_number, xxen_util.meaning(xma.depreciate_flag,'YES_NO',0) depreciate, to_char(null) merge_group, to_char(null) primary_line, to_char(null) sum_units, (select fab.asset_number from fa_additions_b fab where fab.asset_id=xma.add_to_asset_id) add_to_asset_number, xxen_util.meaning(xma.amortize_flag,'YES_NO',0) amortize, xma.mass_addition_id, xma.mass_addition_id upload_row from fa_mass_additions xma where xma.posting_status not in ('POSTED','MERGED','SPLIT') and 1=1 Code-review fixes: targeted-update apply (no longer nulls untouched feeder/audit/deprn columns); order-independent merge Sum Units; create distribution fail-loud; multi-distribution edit guard; create-into-Post required-field validation; mass_addition_id round-trip so created rows show stored values; Asset Type display sourced from fa_lookups. U A xxen_fa_upload.create_update_mass_addition xxen_fa_upload.validate_mass_addition(:$column$.operation,:$column$.book,:$column$.mass_addition_id,:$column$.invoice_number,:$column$.distribution_line,:$column$.invoice_line,:$column$.queue,:$column$.description,:$column$.asset_type,:$column$.category,:$column$.units,:$column$.cost,:$column$.date_placed_in_service,:$column$.expense_account,:$column$.location,:$column$.employee_number,:$column$.depreciate,:$column$.merge_group,:$column$.add_to_asset_number,:$column$.amortize) US FA Mass Additions Upload Maintains Oracle Assets Prepare Mass Additions queue lines. Use the Operation column to choose what each row does: Update (change fields and/or Queue), Create (add a new mass addition line), Merge, Split, Unsplit, Add to Asset or Undo Add to Asset. Queue (Post, On Hold, Delete, On-Review) sets the posting status of the line on an Update. Merge combines the lines that share a Merge Group label into the one row marked Primary Line = Yes; that line is kept as the asset and accumulates the cost of the other lines (and their units when Sum Units = Yes), and is set to On Hold. After uploading, run the standard Post Mass Additions program to create the assets, exactly as today. Existing lines are matched on Book plus Invoice Number, Distribution Line and Invoice Line. Enginatics Upload 1=1 1 10 1=1 :p_upload_mode like '%'||xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 xma.book_type_code=:book LOV custom Y select fbc.book_type_code id, fbc.book_type_code value, fbc.book_type_name description from fa_book_controls fbc where fbc.book_class='CORPORATE' and nvl(fbc.date_ineffective,sysdate+1)>sysdate order by fbc.book_type_code Y US Book 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 OPERATION LOV custom varchar2 Y select 'Update' id, 'Update' value, null description from dual union all select 'Create', 'Create', null from dual union all select 'Merge', 'Merge', null from dual union all select 'Split', 'Split', null from dual union all select 'Unsplit', 'Unsplit', null from dual union all select 'Add to Asset', 'Add to Asset', null from dual union all select 'Undo Add to Asset', 'Undo Add to Asset', null from dual Update What to do with this row: Update (change fields and/or Queue), Create (add a new line), Merge, Split, Unsplit, Add to Asset, or Undo Add to Asset. Y 6 BOOK LOV custom varchar2 15 Y select fbc.book_type_code id, fbc.book_type_code value, fbc.book_type_name description from fa_book_controls fbc where fbc.book_class='CORPORATE' and nvl(fbc.date_ineffective,sysdate+1)>sysdate order by fbc.book_type_code :$flex$.Book The Fixed Assets corporate depreciation book the line belongs to. Y 7 INVOICE_NUMBER Char varchar2 50 Source AP invoice number. With Distribution Line and Invoice Line it identifies the existing queue line to update, merge or split. 8 DISTRIBUTION_LINE Number number AP distribution line number - part of the key that identifies the existing line. 9 INVOICE_LINE Number number AP invoice line number - part of the key that identifies the existing line. 10 QUEUE LOV custom varchar2 Y select falu.lookup_code id, falu.meaning value, null description from fa_lookups falu where falu.lookup_type='QUEUE NAME' and falu.lookup_code in ('POST','ON HOLD','DELETE','ON-REVIEW') and nvl(falu.end_date_active,sysdate)>=sysdate order by falu.meaning Posting status to set on the line: Post (release to Post Mass Additions), On Hold (keep pending review), Delete (discard), On-Review. 11 DESCRIPTION Char varchar2 80 12 ASSET_TYPE LOV custom varchar2 80 Y select falu.lookup_code id, falu.meaning value, null description from fa_lookups falu where falu.lookup_type='ASSET TYPE' and falu.lookup_code in ('CAPITALIZED','CIP','EXPENSED') order by falu.meaning Capitalized, CIP (construction in process) or Expensed. 13 CATEGORY LOV custom varchar2 61 Y select fcbk.concatenated_segments id, fcbk.concatenated_segments value, null description from fa_categories_b_kfv fcbk, fa_category_books fcb where fcbk.category_id=fcb.category_id and fcb.book_type_code=:$column$.book and fcbk.enabled_flag='Y' order by fcbk.concatenated_segments Asset category; determines the default depreciation method, life and accounts. 14 ASSET_NUMBER Char varchar2 15 15 UNITS Number number 16 COST Number number 17 DATE_PLACED_IN_SERVICE Date date 18 EXPENSE_ACCOUNT LOV custom varchar2 311 Y Y select gcck.concatenated_segments id, gcck.concatenated_segments value, null description from gl_code_combinations_kfv gcck where gcck.chart_of_accounts_id=(select gl.chart_of_accounts_id from fa_book_controls fbc, gl_ledgers gl where fbc.book_type_code=:$column$.book and fbc.set_of_books_id=gl.ledger_id) and gcck.enabled_flag='Y' order by gcck.concatenated_segments GL depreciation expense account for the distribution (from this Book chart of accounts). 19 LOCATION LOV custom varchar2 123 Y Y select flk.concatenated_segments id, flk.concatenated_segments value, null description from fa_locations_kfv flk where flk.enabled_flag='Y' order by flk.concatenated_segments 20 EMPLOYEE_NUMBER Char varchar2 30 21 DEPRECIATE LOV Oracle varchar2 Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description 22 MERGE_GROUP Char varchar2 For a Merge, give every line you want combined the same label, and mark exactly one of them Primary Line = Yes. To sum units, put the Primary Line row above the lines merging into it. 23 PRIMARY_LINE LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Mark Yes on the one line in the Merge Group that is kept as the resulting asset; the others fold into it. 24 SUM_UNITS LOV custom varchar2 Y select 'YES' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual union all select 'NO' id, xxen_util.meaning('N','YES_NO',0) value, null description from dual On the Primary Line: Yes adds the merged lines' units to this asset; No (default) keeps this line's units and sums only cost. 25 ADD_TO_ASSET_NUMBER Char varchar2 15 For Operation = Add to Asset: the existing asset this line cost is added to as an adjustment, instead of creating a new asset. 26 AMORTIZE LOV Oracle varchar2 Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description For Add to Asset: Yes amortizes the cost adjustment; No expenses it in the current period. 27 MASS_ADDITION_ID Number number Y 28 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_operation in Char OPERATION U 4 p_book_type_code in Char BOOK U 5 p_mass_addition_id in Number MASS_ADDITION_ID U 6 p_invoice_number in Char INVOICE_NUMBER U 7 p_distribution_line in Number DISTRIBUTION_LINE U 8 p_invoice_line in Number INVOICE_LINE U 9 p_queue in Char QUEUE U 10 p_description in Char DESCRIPTION U 11 p_asset_number in Char ASSET_NUMBER U 12 p_asset_type in Char ASSET_TYPE U 13 p_category in Char CATEGORY U 14 p_units in Number UNITS U 15 p_cost in Number COST U 16 p_date_placed_in_service in Date DATE_PLACED_IN_SERVICE U 17 p_expense_account in Char EXPENSE_ACCOUNT U 18 p_location in Char LOCATION U 19 p_employee_number in Char EMPLOYEE_NUMBER U 20 p_depreciate in Char DEPRECIATE U 21 p_merge_group in Char MERGE_GROUP U 22 p_primary_line in Char PRIMARY_LINE U 23 p_sum_units in Char SUM_UNITS U 24 p_add_to_asset_number in Char ADD_TO_ASSET_NUMBER U 25 p_amortize in Char AMORTIZE U 26 p_mass_addition_id_out out Number MASS_ADDITION_ID Error SQL Order By upload_row Success SQL select xu.action_, xxen_upload.status_meaning(xu.status_code_) status_, xu.message_, xu.modified_columns_, xu.operation, nvl(xma.book_type_code,xu.book) book, nvl(xma.invoice_number,xu.invoice_number) invoice_number, nvl(xma.ap_distribution_line_number,xu.distribution_line) distribution_line, nvl(xma.invoice_line_number,xu.invoice_line) invoice_line, nvl(xxen_util.meaning(xma.queue_name,'QUEUE NAME',140),xu.queue) queue, nvl(xma.description,xu.description) description, nvl((select falu.meaning from fa_lookups falu where falu.lookup_type='ASSET TYPE' and falu.lookup_code=xma.asset_type),xu.asset_type) asset_type, nvl((select fcbk.concatenated_segments from fa_categories_b_kfv fcbk where fcbk.category_id=xma.asset_category_id),xu.category) category, nvl(xma.asset_number,xu.asset_number) asset_number, nvl(xma.fixed_assets_units,xu.units) units, nvl(xma.fixed_assets_cost,xu.cost) cost, nvl(xma.date_placed_in_service,xu.date_placed_in_service) date_placed_in_service, nvl((select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id=xma.expense_code_combination_id),xu.expense_account) expense_account, nvl((select flk.concatenated_segments from fa_locations_kfv flk where flk.location_id=xma.location_id),xu.location) location, nvl((select papf.employee_number from per_all_people_f papf where papf.person_id=xma.assigned_to and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date),xu.employee_number) employee_number, nvl(xxen_util.meaning(xma.depreciate_flag,'YES_NO',0),xu.depreciate) depreciate, xu.merge_group, xu.primary_line, xu.sum_units, nvl((select fab.asset_number from fa_additions_b fab where fab.asset_id=xma.add_to_asset_id),xu.add_to_asset_number) add_to_asset_number, nvl(xxen_util.meaning(xma.amortize_flag,'YES_NO',0),xu.amortize) amortize, nvl(xma.mass_addition_id,xu.mass_addition_id) mass_addition_id, xu.upload_row from xxen_fa_mass_additions_9656_u xu, fa_mass_additions xma where xu.status_code_=xxen_upload.status_success and xu.mass_addition_id=xma.mass_addition_id(+)