Fixed Assets Queries

-- ASSET CATEGORIES

SELECT DISTINCT a.segment1 || '.' || a.segment2 CATEGORY,

                a.segment1 major_category,

                a.segment2 minor_category,

                   gl1.segment1

                || '.'

                || gl1.segment2

                || '.'

                || gl1.segment3

                || '.'

                || gl1.segment4

                || '.'

                || gl1.segment5 asset_cost,

                   gl2.segment1

                || '.'

                || gl2.segment2

                || '.'

                || gl2.segment3

                || '.'

                || gl2.segment4

                || '.'

                || gl2.segment5 asset_cost_clearing,

                c.deprn_expense_acct,

                   gl3.segment1

                || '.'

                || gl3.segment2

                || '.'

                || gl3.segment3

                || '.'

                || gl3.segment4

                || '.'

                || gl3.segment5 deprn_reserve_account,

                deprn_method,

                life_in_months,

                (life_in_months / 12) life,

                prorate_convention_code

           FROM fa_categories a,

                fa_category_book_defaults b,

                fa_category_books c,

                gl_code_combinations gl1,

                gl_code_combinations gl2,

                gl_code_combinations gl3

          WHERE a.category_id = b.category_id

            AND c.category_id = b.category_id

            AND c.category_id = a.category_id

            AND a.enabled_flag = 'Y'

            AND c.asset_cost_account_ccid = gl1.code_combination_id

            AND c.asset_clearing_account_ccid = gl2.code_combination_id

            AND c.reserve_account_ccid = gl3.code_combination_id

            --AND c.WIP_COST_ACCOUNT_CCID = gl4.CODE_COMBINATION_ID

            --AND c.WIP_CLEARING_ACCOUNT_CCID = gl5.CODE_COMBINATION_ID

            AND b.book_type_code = c.book_type_code

            AND c.book_type_code = 'CORP BOOK'

           

           

           

           

           

-- FA YTD Depreciation - FA For getting YTD Depreciation..etc...

SELECT fbv.book_type_code,

       fab.asset_number,

       fab.asset_id,

       fab.tag_number,

       fbv.date_placed_in_service,

       fab.description,

       fcb.segment1 || '.' || fcb.segment2 asset_category,

       fcb.segment1 || '.' || fcb.segment2 asset_category1,

       fbv.COST asset_cost,

       fbv.original_cost original_asset_cost,

       fbv.life_in_months asset_life,

       mvl_discoverer_fin_support.get_depreciation_dtls (fab.asset_id,

                                                         fbv.book_type_code,

                                                         fdh.distribution_id,

                                                         fdp.period_counter,

                                                         'ACCUM'

                                                        ) accum_depreciation,

       mvl_discoverer_fin_support.get_depreciation_dtls (fab.asset_id,

                                                         fbv.book_type_code,

                                                         fdh.distribution_id,

                                                         fdp.period_counter,

                                                         'YTD'

                                                        ) ytd_depreciation,

       mvl_discoverer_fin_support.get_depreciation_dtls (fab.asset_id,

                                                         fbv.book_type_code,

                                                         fdh.distribution_id,

                                                         fdp.period_counter,

                                                         'RUN'

                                                        ) dep_this_run,

       gcc.segment2 department_no,

       (SELECT ppx.full_name

          FROM per_people_x ppx

         WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian,

       (SELECT ppx.employee_number

          FROM per_people_x ppx

         WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number,

       (SELECT po_number

          FROM fa_invoice_details_v

         WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number,

       (SELECT invoice_number

          FROM fa_invoice_details_v

         WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number,

       (SELECT vendor_number

          FROM fa_invoice_details_v

         WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number,

       (SELECT vendor_name

          FROM fa_invoice_details_v

         WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name,

       fab.serial_number,

       fl.segment3 || '.' || fl.segment4 location_flexfield,

       fab.attribute1 tax_major_category,

       fab.attribute2 tax_minor_category,

       (SELECT retirement_type_code

          FROM fa_retirements

         WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type,

       (SELECT segment1 || '.' || segment2

          FROM fa_asset_keywords

         WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key,

       fdp.period_name,

       fab.attribute3 acquisition_date,

       fab.manufacturer_name,

       fbv.retirement_pending_flag

  FROM apps.fa_additions_v fab,

       apps.fa_books_v fbv,

       apps.fa_categories_b fcb,

       apps.fa_deprn_periods fdp,

       apps.fa_distribution_history fdh,

       apps.gl_code_combinations gcc,

       apps.fa_locations fl

 WHERE 1 = 1

   AND fab.asset_id = fbv.asset_id

   AND fcb.category_id = fab.asset_category_id

   AND fbv.transaction_header_id_out IS NULL

   AND fdp.book_type_code = fbv.book_type_code

   AND fdh.asset_id = fbv.asset_id

   AND fdh.code_combination_id = gcc.code_combination_id

   AND fdh.location_id = fl.location_id

   AND fbv.transaction_header_id_out IS NULL

   AND fdh.transaction_header_id_out IS NULL

/

SELECT fnd_id_flex_segments.application_column_name,

       fnd_id_flex_segments.segment_name,

       fnd_id_flex_segments.segment_num,

       fnd_flex_values.flex_value_set_id,

       fnd_flex_values.flex_value AS SEGMENT,

       fnd_flex_values_tl.description,

       fnd_id_flex_segments.application_id,

       fnd_flex_values.parent_flex_value_low AS PARENT

  FROM applsys.fnd_flex_values fnd_flex_values,

       applsys.fnd_flex_values_tl fnd_flex_values_tl,

       applsys.fnd_id_flex_segments fnd_id_flex_segments

 WHERE fnd_flex_values.flex_value_id = fnd_flex_values_tl.flex_value_id

   AND fnd_flex_values.flex_value_set_id = fnd_id_flex_segments.flex_value_set_id

   AND fnd_id_flex_segments.flex_value_set_id = 1008035

-- AND fnd_flex_values.flex_value <> 'T'

   AND fnd_id_flex_segments.application_id = '140'

   AND fnd_id_flex_segments.segment_num = 3

/

SELECT fa_additions.asset_id,

       disc_gl_set_of_books.set_of_books_id,

       disc_gl_set_of_books.description AS set_of_books,

       disc_gl_set_of_books.set_of_book_currency,

       fa_additions.asset_number,

       fa_additions.tag_number,

       fa_additions.description,

       fa_additions.manufacturer_name,

       fa_additions.serial_number,

       fa_additions.model_number,

       fa_categories_b.segment1,

       fa_categories_b.segment2,

       fa_categories_b.segment3,

       fa_books.book_type_code,

       ROUND (fa_books.COST * fa_distribution_history.units_assigned / fa_additions.current_units,

              0) COST,

       fa_books.date_effective,

       fa_books.date_ineffective,

       fa_books.date_placed_in_service,

       fa_books.deprn_start_date,

       fa_books.original_cost,

       fa_additions.current_units,

       fa_distribution_history.units_assigned,

       fa_books.life_in_months,

       fa_employees.employee_number,

       fa_employees.NAME employee_name,

       fa_locations.segment1 "STATE",

       fa_locations.segment2 "CITY",

       fa_locations.segment3 "SITE",

       fa_locations.segment4 "LOCATOR",

       fa_category_books.asset_cost_acct "ASSET_ACCOUNT_ID",

       segment2.description AS "ASSET_ACCOUNT",

       fa_book_controls.book_class,

       disc_ccid_dsc_mv.account_type,

       disc_ccid_dsc_mv.code_combination_id,

       disc_ccid_dsc_mv.gl_seg1,

       disc_ccid_dsc_mv.gl_seg2,

       disc_ccid_dsc_mv.gl_seg3,

       disc_ccid_dsc_mv.gl_seg4,

       disc_ccid_dsc_mv.gl_seg5,

       disc_ccid_dsc_mv.gl_seg6,

       disc_ccid_dsc_mv.gl_seg7,

       disc_ccid_dsc_mv.gl_seg8,

       disc_ccid_dsc_mv.gl_seg9,

       disc_ccid_dsc_mv.gl_seg10,

       disc_ccid_dsc_mv.gl_seg11,

       disc_ccid_dsc_mv.gl_name_seg1,

       disc_ccid_dsc_mv.gl_name_seg2,

       disc_ccid_dsc_mv.gl_name_seg3,

       disc_ccid_dsc_mv.gl_name_seg4,

       disc_ccid_dsc_mv.gl_name_seg5,

       disc_ccid_dsc_mv.gl_name_seg6,

       disc_ccid_dsc_mv.gl_name_seg7,

       disc_ccid_dsc_mv.gl_name_seg8,

       disc_ccid_dsc_mv.gl_name_seg9,

       disc_ccid_dsc_mv.gl_name_seg10,

       disc_ccid_dsc_mv.gl_name_seg11,

       fa_books.date_placed_in_service "DATE_IN",

--TO_DATE(FA_DISTRIBUTION_HISTORY.DATE_INEFFECTIVE,'DD-MON-RRRR') "DATE_INEFFECTIVE",

--FA_BOOKS.DATE_INEFFECTIVE

       NULL "DATE_IN_MONTH",

       fa_deprn_summary.deprn_amount,

       fa_deprn_summary.ytd_deprn,

       fa_deprn_summary.deprn_reserve,

       fa_deprn_summary.deprn_source_code,

       fa_deprn_summary.addition_cost_to_clear adjusted_cost,

       fa_deprn_periods.period_name

-- disc_fa_invoice_details.vendor_name,

-- disc_fa_invoice_details.invoice_number,

-- disc_fa_invoice_details.invoice_date,

-- disc_fa_invoice_details.fixed_assets_cost AS fa_cost_by_invoice

FROM   apps.fa_additions fa_additions,

       fa.fa_book_controls fa_book_controls,

       fa.fa_books fa_books,

       fa.fa_categories_b fa_categories_b,

       fa.fa_category_books fa_category_books,

       fa.fa_distribution_history fa_distribution_history,

       gl.gl_code_combinations gl_code_combinations,

       apps.fa_employees fa_employees,

       fa.fa_locations fa_locations,

       apps.disc_ccid_dsc_mv disc_ccid_dsc_mv,

       apps.disc_segment2 segment2,

       apps.disc_gl_set_of_books disc_gl_set_of_books,

-- disc_gl_month in_month,

       fa.fa_deprn_detail fa_deprn_summary,

       fa.fa_deprn_periods fa_deprn_periods

-- disc_fa_invoice_details disc_fa_invoice_details

WHERE  fa_books.book_type_code = fa_book_controls.book_type_code

   AND fa_books.asset_id = fa_additions.asset_id

   AND fa_books.date_ineffective IS NULL

   AND fa_distribution_history.book_type_code = fa_books.book_type_code

   AND fa_distribution_history.asset_id = fa_additions.asset_id

   AND fa_distribution_history.date_ineffective IS NULL

   AND fa_categories_b.category_id = fa_additions.asset_category_id

   AND fa_category_books.book_type_code = fa_distribution_history.book_type_code

   AND fa_category_books.category_id = fa_additions.asset_category_id

   AND gl_code_combinations.code_combination_id = fa_distribution_history.code_combination_id

   AND fa_employees.employee_id(+) = fa_distribution_history.assigned_to

   AND fa_locations.location_id = fa_distribution_history.location_id

   AND gl_code_combinations.code_combination_id = disc_ccid_dsc_mv.code_combination_id

   AND segment2.SEGMENT = fa_category_books.asset_cost_acct

   AND disc_gl_set_of_books.set_of_books_id = fa_book_controls.set_of_books_id

-- AND fa_books.date_placed_in_service BETWEEN in_month.start_date AND in_month.end_date

   AND fa_deprn_summary.asset_id = fa_distribution_history.asset_id

   AND (    fa_deprn_periods.period_counter = fa_deprn_summary.period_counter

        AND fa_deprn_periods.book_type_code = fa_deprn_summary.book_type_code

       )

   AND fa_deprn_summary.distribution_id = fa_distribution_history.distribution_id

-- AND fa_books.asset_id = disc_fa_invoice_details.asset_id(+)

-- AND fa_deprn_summary.deprn_source_code = 'D'

-- and fa_additions.asset_id = '10000767';