helpful sql scripts

Hello,

SQL scripts are very powerful and may times handy to fix the data problems, know the system settings and for reporting purposes.

In this post I will be providing few SQL scripts that can be of help while working on oracle applications projects.


#1. below is the URL for SQL Script to compile the invalid objects in the instance.

http://docs.google.com/Doc?id=dg4fxj9f_0cv7wh9fv


#2. while working on the iSupport login issue on R12 upgrade project I had to create a SQL to
find the "profile options" for the given value i.e. 'server name'

Likewise, many times it is needed to know what profile is set at what level in the instance.
or
if the value is known or we need to find the profile options for them

below is the SQL query which can be used to know the profile option value at a particular
level

================= SQL =====================

SELECT n.user_profile_option_name NAME ,
DECODE(v.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', 10005, 'Server', 10006, 'Organization', 10007, 'ServResp', 'Undefined') LEVEL_SET, DECODE(TO_CHAR(v.level_id), '10001', '', '10002', app.application_short_name, '10003', rsp.responsibility_key, '10004', usr.user_name, '10005', svr.node_name, '10006', org.name, '10007', (SELECT n.node_name FROM fnd_nodes n WHERE n.node_id=level_value2 ) '/' (DECODE(v.level_value, -1, 'Default', (SELECT responsibility_key FROM fnd_responsibility WHERE responsibility_id=level_value ) ) ), v.level_id) "CONTEXT" , v.profile_option_value VALUE from fnd_profile_options_vl p, fnd_profile_option_values v , fnd_profile_options_tl n , fnd_user usr , fnd_application app , fnd_responsibility rsp , fnd_nodes svr , hr_operating_units org where p.profile_option_id = v.profile_option_id (+)AND p.profile_option_name = n.profile_option_nameAND ((upper(v.profile_option_value) LIKE upper(NVL('&PROFILE_OPTION_VALUE',upper(v.profile_option_value) ) ) )) AND usr.user_id (+) = v.level_valueAND rsp.application_id (+) = v.level_value_application_id AND rsp.responsibility_id (+) = v.level_valueAND app.application_id (+) = v.level_value AND svr.node_id (+) = v.level_valueAND org.organization_id (+) = v.level_value AND upper(p.user_profile_option_name) LIKE NVL(upper('&user_profile_option_name'), upper(p.user_profile_option_name) )/* only for US language */AND n.language='US'
ORDER BY name, v.level_id
==========================================

Formatting is needed. Use any SQL Dev Tool like OSD (Oracle SQL Developer).

Above SQL prompts for the "profile option value" and "profile option name"

these can contain the wildcard '%' to search for matching of values/profile options.

for e.g .
Enter value for profile_option_value: 01-APR%
Enter value for user_profile_option_name:

or
Enter value for profile_option_value:
Enter value for user_profile_option_name: sla%hist%

Also accessible at the following URL: http://docs.google.com/Doc?id=dg4fxj9f_1cx9tc2fj



#3. important SQL for GL to XLA drilldown and to get the receiving transaction details from the GL-XLA-RCV tables.

This query was created by me for a custom solution that we gave to the client for "Audit" purposes to know the breakup of the accrual account balance/transactions.

The reason was R12 AP-PO reconciliation report balance was not matching with the accrual account balance after R12 upgrade.

Since this report is the YTD report which considers the transaction from the 1st day the trsansactions are entered into the system we can not specify the from and to date range in the report.

Interesting fact is that client was using the same report in 11i and it used to work perfectly for them but in R12 it was not matching with the accrual account balance.

When logged an SR with Oracle Support they said that report is supposed to show the AP-PO match trasnactions , Ap-PO no match transactions and AP-no PO type of transactions and it can not be sure that it will match the accrual account balance.

This was not acceptable at our end since ideally report balance should tie the accrual account balance in GL.

That is why we decided to create a custom query/report which would contain the transactions that HIT the accual account.

so, SQL Query having :
Ap-Invoices
Receiving transactions and
GL manually entered transaction
was created and was given to the KPMG auditors for breakup.

below SQL query is "part" of it for getting the receiving accrual account transactions.

========== start SQL =========

SELECT ph.segment1 po_number ,
aps.vendor_name ,
msi.segment1 item_number ,
msi.description item_description,
'Receive' Accrual_Transaction ,
'PO*' SOURCE ,
(SELECT organization_name
FROM apps.org_organization_definitions
WHERE organization_id = rt.organization_id
) ORG ,
rsh.receipt_num DOC_NUMBER ,
rt.transaction_date DOC_DATE ,
pl.line_num LINE ,
rt.unit_of_measure UOM ,
rt.quantity QUANTITY ,
rt.po_unit_price UNIT_PRICE ,
(NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0) ) accounted_amount,
(NVL(xal.entered_cr,0) - NVL(xal.entered_cr,0) ) entered_amount ,
rt.currency_code
FROM apps.gl_je_headers gjh ,
apps.gl_je_lines gjl ,
apps.gl_import_references gir ,
apps.xla_ae_lines xal ,
apps.xla_ae_headers xah ,
apps.xla_events xe ,
apps.xla_event_types_tl xet ,
apps.xla_event_classes_tl xect ,
apps.rcv_transactions rt ,
apps.XLA_DISTRIBUTION_LINKS xdl ,
apps.rcv_receiving_sub_ledger rrsl,
apps.po_headers_all ph ,
apps.po_lines_all pl ,
apps.ap_suppliers aps ,
apps.rcv_shipment_headers rsh ,
apps.mtl_system_items_b msi
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xe.application_id = xet.application_id
AND xet.LANGUAGE = USERENV ('LANG')
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.application_id = xe.application_id
AND xect.LANGUAGE = USERENV ('LANG')
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = rrsl.RCV_SUB_LEDGER_ID
AND rrsl.rcv_transaction_id = rt.transaction_id
AND rt.po_header_id = ph.po_header_id
AND rt.po_line_id = pl.po_line_id
AND ph.org_id = 83
AND gjl.code_combination_id = 1011
AND gjh.period_name IN ('OCT-08','NOV-08','DEC-08')
AND ph.vendor_id = aps.vendor_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND msi.inventory_item_id = pl.item_id
AND msi.organization_id = rt.organization_id

========== end SQL =========



Regards,
- Faiz

get set go ...

Hello,

I have created this blog to share my experience of Oracle E-Business Suite Applications.

I think blogs are very helpful in terms of sharing the information across the globe.
Any comments, suggestions, improvements and Q&A on Oracle Applications are welcome in my blog.

If you have any questions related to OAF, R12, Multi-Org, JTF or JTT framework, just post them to the blog and I will try my best to answer your question asap.

Hope this blog will be of help to everybody some or other time working on Oracle Apps developement, implementation or support projects.

Thanks & Regards,
- Faiz