Entries by Oracle ERP Apps Guide

, , , , ,

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 records3. Execute the spooled records in apps4. Commit; select ‘EXEC ‘||’AD_DD.REGISTER_TABLE(”XXCUST”, ”’||TABLE_NAME||”’,”T”,8,10,90);’ from all_tableswhere 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_columnswhere table_name = :TABLE_NAMEORDER BY COLUMN_ID/select ‘EXEC ‘||’AD_DD.REGISTER_PRIMARY_KEY(”XXCUST”,”’||INDEX_NAME||”’,”’||TABLE_NAME||”’,”’||ITYP_NAME||”’,”S”,”Y”,”Y”);’FROM ALL_INDEXESWHERE table_name = :TABLE_NAME and […]

/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) / […]

/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_idFROM     (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                 […]

/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     , statusFROM   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, […]

/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      […]

/by