How : Difference Between Original Cost,Salvage Cost,Net Book Value and Recoverable Cost.

1- The original cost is the amount of money the asset originally costs. This amount may suffer modifications (adjustments) when, for example, you buy an asset for a certain cost but you have to pay other expenses to put it working.

2- The recoverable cost is the portion of the current cost that can be depreciated. It is the current cost less the salvage value less the Investment Tax Credit basis reduction amount. If you specify a depreciation cost ceiling, and if the recoverable cost is greater than that ceiling, Oracle Assets uses the cost ceiling instead.

3- The net book value is defined as: Net Book Value = Current Cost – Total Reserve (Accumulated Depreciation + Bonus Reserve)

4- You can specify a salvage value as a percentage of an asset’s acquisition cost or as an amount. The percentage salvage value will be defaulted from the category default rules if you have default the salvage value percentage at the category level in the Asset Categories window. The salvage value is calculated by multiplying the acquisition cost by the default salvage value percentage. Then you can define a default percentage salvage value at the category level in the Asset Categories window. Oracle Assets calculates the salvage value by multiplying the acquisition cost by the default salvage value percentage for the category, book, and date placed in service. If you specify the salvage value as an amount, you simply enter the amount.

Read More

Query : Get the Serial Number for an Order

SELECT wl.meaning release_status
, wdd.released_status
, wdd.source_line_id
, wdd.source_code
, wser.fm_serial_number
, wser.TO_SERIAL_NUMBER
, wdd.CREATION_DATE
, oelines.header_id
, oeheader.sold_to_org_id
, wdd.shipped_quantity
, wdd.delivery_detail_id
FROM wsh_delivery_details wdd
, apps.wsh_lookups wl
, OE_ORDER_LINES_ALL oelines
, oe_order_headers_all oeheader
, wsh_serial_numbers wser
WHERE 1 = 1
AND wl.lookup_type = 'PICK_STATUS'
AND wl.lookup_code = wdd.released_status
AND wdd.source_code = 'OE'
AND wdd.source_line_id=oelines.line_id
and oelines.header_id = oeheader.header_id
AND WDD.DELIVERY_DETAIL_ID=wser.delivery_detail_id

Read More

Query : Get a listing of asset categories with the accounting and depreciation information

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'

Read More

How : How to enable the DFF for the Lookup?

If you want to enable the DFF and enter some additional entries in Lookups you just need to follow the below steps

STEPS FOR YOU:

Create a DFF

Login to System Administrator
Application -> Flexfield -> Descriptive -> Segments
Search for Application = Application Object Library
Title = Common Lookups

You can see referenced field as "LOOKUP_TYPE"

Now if you want to enable your lookup, you need to uncheck "Freeze Flexfield Definitions" checkbox on the top left the DFF Segments screen.
this enables you to enter new DFF entries.

Now, enter create a line under Context Field Values

Under "Code" enter your lookup ex: XXPHR_DEDUCT_ELEMENTS

Click on Segments

Enter the number, name, prompt, column and value set(if you have one)

save and compile the DFF.

Now, switch the responsibility to Application Developer to see the DFFs enabled in lookup

Open the common lookups screen and query for the lookup name you have given under "Code" of DFF setup.

and thus your DFF is enabled.

Read More

Query: Print Customer Address Details

SELECT DISTINCT hca.account_number customer_number,
hp.party_name customer_name,
hps.party_site_number site_number, hl.address1 address1,
hl.address2 address2, hl.address3 address3,
hl.address4 address4, hl.city city,
hl.postal_code postal_code, hl.state state,
ftt.territory_short_name country,
hcsua1.LOCATION bill_to_location,
hcsua2.LOCATION ship_to_location
FROM hz_parties hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua1,
hz_locations hl,
fnd_territories_tl ftt,
hz_cust_acct_sites_all hcasa2,
hz_cust_site_uses_all hcsua2
WHERE hp.party_id = hps.party_id(+)
AND hp.party_id = hca.party_id(+)
AND hcasa1.party_site_id(+) = hps.party_site_id
AND hcasa2.party_site_id(+) = hps.party_site_id
AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
AND hcsua1.site_use_code(+) = 'bill_to'
AND hcsua2.site_use_code(+) = 'ship_to'
AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
AND hps.location_id = hl.location_id
AND hl.country = ftt.territory_code
AND ftt.LANGUAGE = USERENV ('lang')
ORDER BY customer_number;

Read More

Query for vendors with their bank account details

SQL query to extract all payabales vendors with their bank account details from the Oracle APPS Database

You can try this query:

SELECT DISTINCT abau.bank_account_uses_id bank_account_uses_id,
abau.end_date end_date,
abau.external_bank_account_id external_bank_account_id,
abau.primary_flag primary_flag,
abau.start_date start_date,
abau.vendor_id vendor_id,
abau.vendor_site_id vendor_site_id,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.bank_account_type bank_account_type,
aba.account_type account_type,
aba.currency_code currency_code,
aba.description bank_account_description,
aba.check_digits check_digits,
aba.multi_currency_flag multi_currency_flag,
abb.bank_name bank_name,
abb.bank_name_alt bank_name_alt,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name,
abb.bank_branch_name_alt bank_branch_name_alt,
abb.bank_num bank_num, abb.institution_type institution_type,
abb.bank_branch_type bank_branch_type,
pv.vendor_name vendor_name,
pv.segment1 vendor_number,
pv.vendor_type_lookup_code vendor_type_lookup_code,
pvs.vendor_site_code vendor_site_code,
pvs.address_line1,
pvs.address_line2,
pvs.city,
pvs.zip,
abb.end_date branch_end_date,
aba.inactive_date acct_inactive_date,
aba.org_id
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
po_vendor_sites_all pvs
WHERE abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
AND abau.vendor_site_id = pvs.vendor_site_id(+)

Read More