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
/

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;

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

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

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’;