Raghunath Kashyap Oracle Shopping Mall : Oracle Fixed Assest

Showing posts with label Oracle Fixed Assest. Show all posts

Thursday, August 29, 2013

Oracle Fixed Assets Data Extraction Report

SELECT DISTINCT
          fav.description        "Asset Description",
          fac.segment1           "Major Category",
          fac.segment2           "Minor Category",
          fav.CURRENT_UNITS      "Units",
          fab.cost               "Cost",
          fal.SEGMENT3           "City" ,
          gcc.Segment1           "ExpAcnt1-Company",
          gcc.segment2           "ExpAcnt2-Costcentre",
          gcc.segment3           "ExpAcnt3-MainAccount",
          gcc.segment4           "ExpAcnt4-subAccount",
          gcc.segment5           "ExpAcnt5-project",
          gcc.segment6           "ExpAcnt6-region",
          gcc.segment7           "ExpAcnt7-Future1",
          gcc.segment8           "ExpAcnt8-Future2" ,
          gcc2.Segment1          "ClearAcnt1-Company",
          gcc2.segment2          "ClearAcnt2-Costcentre",
          gcc2.segment3          "ClearAcnt3-MainAccount",
          gcc2.segment4          "ClearAcnt4-subAccount",
          gcc2.segment5          "ClearAcnt5-project",
          gcc2.segment6          "ClearAcnt6-region",
          gcc2.segment7          "ClearAcnt7-Future1",
          gcc2.segment8          "ClearAcnt8-Future2"   ,
          fab.DATE_PLACED_IN_SERVICE "Date Placed in Service" ,
          fav.OWNED_LEASED           "Ownership" ,
          fav.NEW_USED               "Bought" ,
          ds.DEPRN_RESERVE           "Depreciation Reserve",
          ds.YTD_DEPRN               "YTD Depreciation",
          fab.deprn_method_code      "Depreciation Method",
          fab.life_in_months         "Life in Months"
     FROM fa_deprn_summary ds,
          fa_additions_v fav,
          fa_deprn_periods dp,
          fa_book_controls fb,
          gl_sets_of_books glsb,
          FA_CATEGORIES     fac,
          fa_books fab,
          fa_distribution_history fdh,
          gl_code_combinations gcc,
          gl_code_combinations gcc2,
          fa_locations fal     ,
          fa_category_books fcb
    WHERE fb.book_type_code = ds.book_type_code
      AND fb.set_of_books_id = glsb.set_of_books_id
      AND ds.asset_id = fav.asset_id
      AND ds.book_type_code = dp.book_type_code
      AND ds.period_counter = dp.period_counter
      AND ds.deprn_source_code <> 'BOOKS'
      and ds.ASSET_ID =:asset_id
      and ds.book_type_code=:BOOK_TYPE_CODE
      and fav.ASSET_CATEGORY_ID =fac.CATEGORY_ID
      and fab.BOOK_TYPE_CODE =fb.BOOK_TYPE_CODE
      AND fab.date_ineffective IS NULL
      and fav.ASSET_ID =fab.ASSET_ID
      and fdh.ASSET_ID=fav.ASSET_ID
      and fal.LOCATION_ID=fdh.LOCATION_ID
      and fdh.DATE_INEFFECTIVE is null
      and gcc.CODE_COMBINATION_ID =fdh.CODE_COMBINATION_ID
      and gcc2.CODE_COMBINATION_ID=fcb.ASSET_CLEARING_ACCOUNT_CCID
      and fcb.CATEGORY_ID=fac.CATEGORY_ID
      and fcb.BOOK_TYPE_CODE =fb.BOOK_TYPE_CODE

Tuesday, July 3, 2012

Oracle FIxed assets Data Extraction Report for conversion

Tuesday, June 26, 2012

Oracle Fixed Assets--> MASS Additions Interface

MASS ADDITIONS INTERFACE:

1. It is a main interface Program for the Fixed Assets.

2. Primarily used for adding Assets to the system

The main use of this Mass Additions is:

1. Creates Assets

2. Cost Adjust Assets.

3. Create CIP (Construction in Process) Assets.

4. Cost adjusts CIP Assets.

5. Use only with corporate book.

The Main Sources which needs this Interface is:

1. Oracle Accounts Payables.

2. Oracle Projects.

3. Oracle ADI (Application Desktop Integrator).

4. Integrated Payables System

5. Legacy Fixed Assets System.

6. Assets Transfers.

PLAN OF ATTACK:

General Information

a. Set-up

b. Mass Additions Components.

•

Discuss legacy asset conversion using mass additions.

• Discuss feed from Accounts Payable using mass additions.

• Discuss missing features

Components

• Mass additions create program (in oracle payables)

• Mass additions create program (in oracle projects)

• Mass additions prepare screen

• Mass additions posting program

• Mass additions delete program

• Mass additions purge program

What makes up an Asset?

Asset number	Description
Category	Asset cost
Payables clearing account	Depreciation Expense Account
Asset key flex field	Location flex field
Date placed in service	Units (quantity)
Book code (CORPORATE)	Feeder System Name
Depreciate Flag	Asset Type
Depreciation Reserve	YTD Depreciation
Tag Number	Serial Number
Descriptive flex fields

Database Components

FA_MASS_ADDITIONS	Main interface table can be used exclusively if all assets have one and only one distribution
FA_MASSADD_DISTRIBUTIONS	Secondary table, only needed if an asset has more than one distribution

Triggers for auto population of FA_MASSADD_DISTRIBUTIONS table

1. fa_mass_additions_insert_dist

2. fa_mass_additions_update_dist

3. fa_mass_additions_delete_dist

4. fa_mass_additions_insert_name (used with distribution sets).

Key fields:

• Represented in FA_MASS_ADDITIONS as numeric id fields, NOT CHARACTER

• Lookup of ID needed for

– Category

– Location

– Asset key

– Payables clearing account

– Depreciation expense account

Catergories:

• There is no character field for category code in mass additions table.

• There is a numeric field, category_id in mass additions table

• Category_id can be looked up from the FA_CATEGORIES table

Locations:

• There is no character field for location in mass additions table.

• There is a numeric field, location_id in mass additions table

• Location_id can be looked up from the FA_LOCATIONS table

Payables Clearing Account

• There is no character field for payables clearing account in mass additions table.

• There is a numeric field, PAYABLES_CODE_COMBINATION_ID in mass additions table

• PAYABLES_CODE_COMBINATION_ID can be looked up from the GL_CODE_COMBINATIONS table

Useful link: http://docs.oracle.com/cd/A60725_05/html/comnls/us/fa/maoi04.htm