Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Thursday, August 4, 2011

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';

8 comments:

Krishna Reddy Gali said...

nice query But Copy is not happens

Raju Chinthapatla said...

Send a mail to OracleApps88@Yahoo.com

Anonymous said...

Nice job

Unknown said...

thanks

Resh said...

Will this query work for EBS ver 12.1.3 as well?

Unknown said...

Thanks

Swapnil Dandgvhal said...

can any one help me with join between PO and fixed asset

Swapnil Dandgvhal said...

how to join purchaser order table with fixed asset ?

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect