Query for Customer Receipt Details

SELECT acra.cash_receipt_id,
DECODE (acra.TYPE,
'cash', 'cash receipt receipt',
'misc', 'miscellaneous',
acra.TYPE
) receipt_type,
acra.currency_code, acra.doc_sequence_value receipt_number,
acra.receipt_number reference_number,
TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
acra.misc_payment_source, hca.account_number customer_no,
NVL (acra.amount, 0) entered_amount,
NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
abb.bank_name, abb.bank_branch_name, acra.comments description
FROM ar_cash_receipts_all acra,
ar_receipt_methods arm,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_cust_accounts hca,
hz_parties hp
WHERE acra.pay_from_customer = hca.cust_account_id(+)
AND acra.org_id = abaa.org_id(+)
AND hca.party_id = hp.party_id(+)
AND acra.receipt_method_id = arm.receipt_method_id
AND acra.remittance_bank_account_id = abaa.bank_account_id
AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;

Read More

Query : Supplier Invoice Details

selectap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount Invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name--,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account
from ap_invoices_all ap,
ap_invoice_distributions_all aid,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
ap_terms_tl at,
gl_code_combinations gcc
where alc.lookup_type='INVOICE TYPE'
and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE
and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'
and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE
and alc2.lookup_type='PAYMENT METHOD'
and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE
and ap.invoice_id=aid.invoice_id
and ap.vendor_id = pv.vendor_id
and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=pvs.vendor_id
and ap.vendor_site_id=pvs.vendor_site_id
--and ap.terms_id=at.term_id(+)
and ap.invoice_num=

Read More

Query : To find DataBase Locking

SELECT o.object_name,
l.oracle_username,
l.os_user_name,
s.machine,
s.program,
DECODE(l.locked_mode,0,'None',1,'NULL',2,'Row-S(SS)',3,'Row-X(SX)',
4,'Share',5,'S/Row-X(SSX)',6,'Exclusive(X)') locked_mode,
s.sid,
s.serial#
FROM all_objects o,v$locked_object l,v$session s
WHERE l.session_id = s.sid
AND l.object_id=o.object_id

Read More

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