, , , , ,

Onhand Quantity at given date

Below is the query that can help in getting onhand quantity at given date. The query inputs the Item ID, organization ID and date.

SELECT   SUM (target_qty)
       , item_id
FROM     (SELECT   moqv.subinventory_code subinv
                 , moqv.inventory_item_id item_id
                 , SUM (transaction_quantity) target_qty
          FROM     mtl_onhand_qty_cost_v moqv
          WHERE    moqv.organization_id = :org_id
          AND      moqv.inventory_item_id = :item_id
          GROUP BY moqv.subinventory_code
                 , moqv.inventory_item_id
                 , moqv.item_cost
          UNION
          SELECT   mmt.subinventory_code subinv
                 , mmt.inventory_item_id item_id
                 , -SUM (primary_quantity) target_qty
          FROM     mtl_material_transactions mmt
                 , mtl_txn_source_types mtst
          WHERE    mmt.organization_id = :org_id
          AND      transaction_date >= TO_DATE (:hist_date) + 1
          AND      mmt.transaction_source_type_id =
                                               mtst.transaction_source_type_id
          AND      mmt.inventory_item_id = :item_id
          GROUP BY mmt.subinventory_code
                 , mmt.inventory_item_id) oq
GROUP BY oq.item_id

/by
, , , , ,

Database and Application Information

Below are some of the queries that can be used to get the database and Application information.

1) Get Product Version

SELECT product
     , VERSION
     , status
FROM   product_component_version

The other way to get this information is by using following query

select * from v$version;

2) Get Applications Version and Patch Information

SELECT   SUBSTR (a.application_name, 1, 60) Application_Name
       , SUBSTR (i.product_version, 1, 4) Version
       , i.patch_level
       , i.application_id
       , i.last_update_date
FROM     apps.fnd_product_installations i
       , apps.fnd_application_all_view a
WHERE    i.application_id = a.application_id
ORDER BY a.application_name

3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.

SELECT applied_patch_id
     , patch_name
     , patch_type
     , source_code
     , creation_date
     , last_update_date
FROM   ad_applied_patches

4) Check if the application is setup for Multi-Org

SELECT multi_org_flag
FROM   fnd_product_groups

/by
, , , , ,

Which User is Locking the table

Here is another query that can sometime be very useful. This will list the name of user that is locking a table. The object name is taken as an input parameter.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins      fl
,fnd_user        fu
,v$locked_object vlocked
,v$process       vp
,v$session       vs
,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE ‘%’ || upper(‘&tabname_blank4all’) || ‘%’
AND nvl(vs.status,’XX’) != ‘KILLED’;

/by
, , , , ,

Query to find who and when update an Oracle Application user’s profile

SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date “Change Date”,
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) “Created By”,
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) “Last Update By”
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
/by
, , , , ,

Query for Sales Order Details

SELECT   ooha.header_id order_header_id, ottt.NAME order_type_name,
         ooha.order_number, ooha.ordered_date,
         ooha.transactional_curr_code order_currency, hp.party_id,
         hp.party_number, hp.party_name customer_name,
         hca.cust_account_id customer_id, hca.account_number customer_number,
         oola.line_id order_line_id, oola.line_number, oola.inventory_item_id,
         msib.segment1 item_number, msib.description item_desc,
         oola.attribute15 superseded_item, oola.order_quantity_uom,
         oola.ordered_quantity, oola.unit_selling_price
    FROM oe_order_headers_all ooha,
         oe_order_lines_all oola,
         oe_transaction_types_tl ottt,
         mtl_system_items_b msib,
         mtl_parameters mp,
         org_organization_definitions ood,
         hz_parties hp,
         hz_cust_accounts hca
   WHERE ooha.header_id = oola.header_id
     AND ottt.transaction_type_id(+) = ooha.order_type_id
     AND ottt.LANGUAGE = USERENV (‘LANG’)
     AND hca.cust_account_id(+) = ooha.sold_to_org_id
     AND hp.party_id = hca.party_id
     AND ooha.org_id = oola.org_id(+)
     AND msib.inventory_item_id = oola.inventory_item_id
     AND msib.organization_id = mp.master_organization_id
     AND mp.organization_id = ood.organization_id
     AND mp.master_organization_id = mp.organization_id
     AND ood.operating_unit = fnd_profile.VALUE (‘ORG_ID’)
     AND ooha.order_number = :sales_order_number
ORDER BY ottt.NAME, ooha.order_number, oola.line_number;
/by