, , , , ,

Registering Custom Table in Apps

To register your custom table under FND.

1. Input is your custom table name. Execute these  4 queries
2. Spool the records
3. Execute the spooled records in apps
4. Commit;

select ‘EXEC ‘||’AD_DD.REGISTER_TABLE(”XXCUST”, ”’||TABLE_NAME||”’,”T”,8,10,90);’ from all_tables
where table_name = :TABLE_NAME
/
select ‘EXEC ‘||’AD_DD.REGISTER_COLUMN(”XXCUST”, ”’||TABLE_NAME||”’,”’||COLUMN_NAME||”’,’||COLUMN_ID||’,”’||DATA_TYPE||”’,’||DATA_LENGTH||’,”’||NULLABLE||”’,”N”);’ from all_tab_columns
where table_name = :TABLE_NAME
ORDER BY COLUMN_ID
/
select ‘EXEC ‘||’AD_DD.REGISTER_PRIMARY_KEY(”XXCUST”,”’||INDEX_NAME||”’,”’||TABLE_NAME||”’,”’||ITYP_NAME||”’,”S”,”Y”,”Y”);’
FROM ALL_INDEXES
WHERE table_name = :TABLE_NAME
and uniqueness = ‘UNIQUE’
/
select ‘EXEC ‘||’AD_DD.REGISTER_PRIMARY_KEY_COLUMN(”XXCUST”,”’||A.INDEX_NAME||”’,”’||A.TABLE_NAME||”’,”’||A.COLUMN_NAME||”’,’||A.COLUMN_POSITION||’);’
FROM ALL_IND_COLUMNS A, ALL_INDEXES B
WHERE A.TABLE_NAME = :TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME
AND B.UNIQUENESS = ‘UNIQUE’
/

To delete the registered Tables, columns
select ‘EXEC ‘||’AD_DD.DELETE_TABLE(”XXCUST”, ”’||TABLE_NAME);’ from all_tables
where table_name = :TABLE_NAME
/
select ‘EXEC ‘||’AD_DD.REGISTER_COLUMN(”XXCUST”, ”’||TABLE_NAME||”’,”’||COLUMN_NAME);’ from all_tab_columns
where table_name = :TABLE_NAME
ORDER BY COLUMN_ID
/

/by
, , , ,

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