Fixed Assets few useful Queries in Oracle APPS

 SELECT *
   FROM fa_additions_b
  WHERE asset_number = p_asset_number;

SELECT *
   FROM fa_additions_tl
  WHERE asset_id = p_asset_id
    AND LANGUAGE = USERENV('LANG');

  SELECT *
    FROM fa_transaction_headers
   WHERE asset_id = p_asset_id
     AND book_type_code = p_book_type_code
ORDER BY book_type_code, date_effective;

SELECT *
  FROM fa_asset_history
 WHERE asset_id = p_asset_id;

 SELECT *
   FROM fa_adjustments
  WHERE asset_id = p_asset_id;

  SELECT *
    FROM fa_books
  WHERE asset_id = p_asset_id;

  SELECT *
    FROM fa_deprn_summary
   WHERE asset_id = p_asset_id
     AND book_type_code = p_book_type_code
  ORDER BY deprn_run_date;

SELECT *
    FROM fa_deprn_summary_h
   WHERE asset_id = p_asset_id;

SELECT *
    FROM fa_deprn_detail
   WHERE asset_id = p_asset_id
  ORDER BY book_type_code, deprn_run_date;

SELECT *
    FROM fa_deprn_detail_h
  WHERE asset_id = p_asset_id;


  SELECT *
    FROM fa_deprn_events
   WHERE asset_id = p_asset_id
ORDER BY book_type_code, deprn_run_date;

SELECT *
  FROM fa_asset_invoices
 WHERE asset_id = p_asset_id;

SELECT *
  FROM fa_invoice_transactions
 WHERE invoice_transaction_id IN (SELECT DISTINCT invoice_transaction_id_in
                                    FROM fa_asset_invoices
                                   WHERE asset_id = p_asset_id);

  SELECT *
    FROM fa_books_summary
   WHERE asset_id = p_asset_id
ORDER BY book_type_code, period_counter;

  SELECT *
    FROM fa_deprn_periods
   WHERE book_type_code = (SELECT DISTINCT book_type_code
                             FROM fa_deprn_detail_h
                            WHERE asset_id = p_asset_id)
ORDER BY period_counter;

SELECT *
  FROM fa_mass_additions
 WHERE asset_number = p_asset_number;

SELECT *
  FROM fa_massadd_distributions
 WHERE mass_addition_id IN (SELECT DISTINCT mass_addition_id
                              FROM FA_MASS_ADDITIONS
                             WHERE asset_number = p_asset_number);

SELECT *
  FROM fa_book_controls
 WHERE book_type_code = (SELECT DISTINCT book_type_code
                           FROM fa_deprn_detail_h
                          WHERE asset_id = p_asset_id);

SELECT *
  FROM fa_book_controls_history
 WHERE book_type_code = (SELECT DISTINCT book_type_code
                           FROM fa_deprn_detail_h
                          WHERE asset_id = p_asset_id
                          );

SELECT *

  FROM fa_categories_b

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number);

SELECT *

  FROM fa_categories_tl

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                        );

SELECT *

  FROM fa_category_books

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                            )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                          );

SELECT *

  FROM fa_category_book_defaults

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                      )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                        );

SELECT *

  FROM fa_calendar_periods

 WHERE calendar_type =

          (SELECT deprn_calendar

             FROM fa_book_controls

            WHERE book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                     )

          );

SELECT *

  FROM fa_conventions

 WHERE prorate_convention_code =

          (SELECT DISTINCT prorate_convention_code

             FROM fa_category_books

            WHERE category_id IN (SELECT DISTINCT asset_category_id

                                    FROM fa_additions_b

                                   WHERE asset_number = p_asset_number

                                     )

              AND book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                    )

          );

SELECT *

  FROM fa_methods

 WHERE     method_code =

              (SELECT DISTINCT deprn_method

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =p_asset_number

                                         )

                  AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

              )

       AND life_in_months =

              (SELECT DISTINCT life_in_months

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =

                                                    p_asset_number)

                      AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

            );

SELECT *

    FROM xla_events

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code)

ORDER BY event_date;

SELECT *

    FROM ( (SELECT *

              FROM xla_transaction_entities

             WHERE source_id_int_1 IN (SELECT DISTINCT

                                                  transaction_header_id

                                         FROM fa_transaction_headers

                                        WHERE asset_id = p_asset_id

                                          AND book_type_code = p_book_type_code

                                          AND event_id IS NOT NULL

                                       )

               AND source_id_char_1 = p_book_type_code

             )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE     source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             AND event_id IS NOT NULL

                                             )

                  AND source_id_char_1 = p_book_type_code

                  AND source_id_int_1 = p_asset_id

           )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary_h

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             )

             AND source_id_char_1 = p_book_type_code

             AND source_id_int_1 = p_asset_id

                  )

       )

ORDER BY creation_date;

SELECT *

    FROM xla_ae_headers

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                       )

ORDER BY accounting_date;