, , , ,

Calculating Weekdays between two dates

Create this function:

CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
   RETURN NUMBER IS
   totalsundays     NUMBER;
   totalsaturdays   NUMBER;
begin
   totalsundays
        := NEXT_DAY (todate – 7, ‘sunday’)
           – NEXT_DAY (fromdate – 1, ‘sunday’);
   totalsaturdays
      :=   NEXT_DAY (todate – 7, ‘saturday’)
         – NEXT_DAY (fromdate – 1, ‘saturday’);

   RETURN (todate – fromdate – (totalsundays + totalsaturdays) / 7 – 1);
END totworkdays;

Call this function as follows:

declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays (’01-jan-2009′, ’31-jan-2009′);
dbms_output.put_line(‘Total Work Days: ‘||lv_tot_work_days);
end;

/by
, , , , ,

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