Query to find out online users

The following query is mainly intended to identify online users as batch jobs can be monitored via the Concurrent Manager.


SELECT c.SID, c.serial# ser, c.module, SUBSTR (c.program, 1, 10) program,
       c.last_call_et last_call, c.sql_hash_value, d.spid, c.process
  FROM v$session c, v$process d
 WHERE c.status = 'ACTIVE'           -- the session is running in the database
   AND c.last_call_et > 300           -- has been running for over 300 seconds
   AND c.paddr = d.addr
   AND c.lockwait IS NULL                       -- not waiting on a (row) lock
   AND (c.program LIKE 'f60%' OR c.program LIKE 'sqlplus%')-- only interesting programs

Oracle Apps Supplier contacts Query

SELECT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,

Daily DBA activites in Oracle Applications

Connect to Oracle Application Thru’ Internet Explorer as SYSADMIN Login for System Administrator Responsibility.

Ø  Checking of Administer Concurrent Manager (where Actual = Target should be same).

Ø  Checking of View All Concurrent Requests (to see Report Server is connected)

    Run Active Roles or Active Responsibilities to check reports are working properly.

Ø  Navigation Path: Switch Responsibility to India Local Inventory -> Click Launch Interface Manager -> Sembiam -> Check for Status Active on Cost Manager and Move Transaction.

Shipping carrier details - wsh_carriers_v

   SELECT wc.ROWID, hp.party_id, wc.scac_code, wc.freight_code,
          wc.manifesting_enabled_flag, wc.currency_code, hp.party_name,
          DECODE (hpu.party_id, NULL, 'I', 'A'), hpu.party_usg_assignment_id,
          wc.attribute_category, wc.attribute1, wc.attribute2, wc.attribute3,
          wc.attribute4, wc.attribute5, wc.attribute6, wc.attribute7,

The back order line details - wsh_backorder_lines_v

   SELECT wdd.delivery_detail_id delivery_detail_id,
          wdd.source_header_number order_number,
          wdd.source_line_number line_number, wdd.source_header_id header_id,
          wdd.source_header_type_id order_type_id,
          wdd.source_header_type_name order_type_name,
          wdd.source_line_id line_id, wdd.customer_id customer_id,
          wdd.inventory_item_id inventory_item_id,

The View script of WSH_DSNO_ITEMS_COMM_V

   SELECT wdd.source_code source_code, wnd.delivery_id delivery_id,
          wda.parent_delivery_detail_id container_instance_id,
          wdd.inventory_item_id item_id, msi.description item_description,
          wdd.source_header_id order_header_id,
          wdd.source_line_id order_line_id,

Sample Inventory Reservations import code

PROCEDURE create_reservation ( 
error_buf OUT VARCHAR2, 
ret_code OUT VARCHAR2, 
p_order_number IN NUMBER, 
p_line_id IN NUMBER, 
p_line_number IN NUMBER 
) 
IS 

Open Interface Requisition to Purchase Order

In this article I like to share some code samples to create a purchase requisition related to a blanket, approve it, create a purchase order from it and approve that. Then perform a receipt on the purchase order just created.

Oracle TCA Party Relationships view - HZ_RELATIONSHIPS_V

SELECT rel.ROWID row_id, rel.relationship_id relationship_id,
       rel.subject_id subject_id, rel.subject_type subject_type,
       rel.subject_table_name subject_table_name, rel.object_id object_id,
       rel.object_type object_type, rel.object_table_name object_table_name,
       rel.party_id party_id, lookup.meaning relationship_meaning,

Oracle TCA Party site uses View - HZ_SITE_USES_V

SELECT DISTINCT su.ROWID, su.site_use_id, su.last_update_date,
                su.object_version_number, su.last_updated_by,
                su.creation_date, su.created_by, su.site_use_code,
                su.cust_acct_site_id address_id, su.primary_flag, su.status,
                su.LOCATION, su.last_update_login, su.contact_id,
                DECODE (acct_role.cust_account_role_id,

The Relation between Party Sites, Party site uses and Party Locations

/* Formatted on 2016/06/07 23:27 (Formatter Plus v4.8.8) */
SELECT a.party_id, a.party_site_id, a.location_id, a.last_update_date,
       a.party_site_number, a.last_updated_by, a.creation_date, a.created_by,
       a.last_update_login, a.request_id, a.program_application_id,
       a.program_id, a.program_update_date, a.wh_update_date,
       a.attribute_category, a.attribute1, a.attribute5, a.attribute2,
       a.attribute3, a.attribute4, a.attribute6, a.attribute7, a.attribute8,
       a.attribute9, a.attribute10, a.attribute11, a.attribute12,

TCA Party Credit Profiles View - HZ_CREDIT_PROFILES_V

SELECT 'Operating_Unit' profile_type, cp.credit_profile_id,
       cp.organization_id, TO_NUMBER (NULL) item_category_id,
       cp.creation_date, cp.created_by, cp.last_update_date,
       cp.last_updated_by, cp.last_update_login, cp.program_application_id,
       cp.program_id, cp.program_update_date, cp.request_id, cp.enable_flag,
       cp.effective_date_from, cp.effective_date_to, cp.credit_checking,

The party contact points view - HZ_CONTACT_POINTS_DFV

SELECT ROWID, ATTRIBUTE_CATEGORY,
 NULL
 FROM HZ_CONTACT_POINTS

The Party Class Categories view - HZ_CLASS_CATEGORIES_V

SELECT   lookup.meaning, CLASS.class_category
    FROM hz_class_categories CLASS,
         hz_class_category_uses USE,
         fnd_lookup_types_vl lookup
   WHERE CLASS.class_category = USE.class_category
     AND CLASS.class_category = lookup.lookup_type
     AND USE.owner_table = 'HZ_PARTIES'

The Party Attribute Translation View - HZ_ATTRIBUTE_TRANS_V

SELECT fnd_lookup_values_vl.attribute1, fnd_lookup_values_vl.attribute2,
       fnd_lookup_values_vl.attribute3, fnd_lookup_values_vl.attribute4,
       fnd_lookup_values_vl.lookup_type, fnd_lookup_values_vl.lookup_code,
       fnd_lookup_values_vl.enabled_flag,
       fnd_lookup_values_vl.start_date_active,