Friday, 7 August 2015

Asset Register Query in oracle apps

/* Formatted on 8/7/2015 12:45:26 PM (QP5 v5.240.12305.39446) */
SELECT    fk.segment1
       || '.'
       || fk.segment2
       || '.'
       || fk.segment3
       || '.'
       || fk.segment4
          asset_key,
       fb.asset_id,
       asset_number,
       fb.book_type_code,
       fab.description,
       fc.segment1 || ',' || fc.segment2 category,
       fb.date_placed_in_service,
       fb.prorate_convention_code,
       fb.prorate_date,
       deprn_method_code,
       (fb.basic_rate * 100) || '%' basic_rate,
       (fb.adjusted_rate * 100) || '%' adjusted_rate,
       TO_CHAR (date_retired, 'MON-YY') period_retired,
       asset_type,
       COST,
       original_cost,
       salvage_value,
       recoverable_cost,
       (SELECT SUM (total_deprn_amount)
          FROM fa_financial_inquiry_deprn_v, gl_periods gp
         WHERE     asset_id = fb.asset_id
               AND gp.end_date <= :p_end_date
               AND period_entered = gp.period_name)
          ytd_depc,
         original_cost
       - ( (SELECT SUM (total_deprn_amount)
              FROM fa_financial_inquiry_deprn_v, gl_periods gp
             WHERE     asset_id = fb.asset_id
                   AND gp.end_date <= :p_end_date
                   AND period_entered = gp.period_name))
          net_book_value,
       fl.segment1 || '.' || fl.segment2 || '.' || fl.segment3 LOCATION,
       gcc.concatenated_segments gl_number,
       fdh.units_assigned units
  FROM fa_additions_v fab,
       fa_books_v fb,
       fa_distribution_history fdh,
       fa_locations fl,
       fa_retirements fr,
       fa_asset_keywords fk,
       gl_code_combinations_kfv gcc,
       fa_categories_b fc
 WHERE     fab.asset_id = fb.asset_id
       AND fb.asset_id = fdh.asset_id
       AND fdh.location_id = fl.location_id
       AND fdh.retirement_id = fr.retirement_id(+)
       AND fk.code_combination_id = fab.asset_key_ccid
       AND gcc.code_combination_id = fdh.code_combination_id
       AND fab.asset_category_id = fc.category_id
       AND fab.asset_number = :p_asset_number
       AND fb.date_placed_in_service < :p_end_date

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete