Showing posts with label Sql Queries. Show all posts
Showing posts with label Sql Queries. Show all posts
Query to get the Approved Supplier Information for the Item
Group Team
This query can be used to get the Approved Supplier List information for the items.

Query is tested in R12 instance.

SELECT ood.organization_code "ORGANIZATION CODE",
              msib.segment1 "ITEM_NUMBER",
              pv.vendor_name "SUPPLIER NAME",
              pv.segment1 "VENDOR NUMBER",
              pvsa.vendor_site_code "SUPPLIER SITE CODE",
              pas.status "SOURCE_ASL_STATUS",
              paa.consigned_from_supplier_flag "CONSIGNED FROM SUPPLIER?"
  FROM APPS.po_approved_supplier_list asl,
              APPS.po_vendors pv,
              APPS.po_vendor_sites_all pvsa,
              APPS.org_organization_definitions ood,
              APPS.mtl_system_items_b msib,
              APPS.po_asl_attributes paa,
             APPS.po_asl_statuses pas
WHERE pv.vendor_id = asl.vendor_id
   AND pvsa.vendor_site_id = asl.vendor_site_id
   AND ood.organization_id = asl.using_organization_id
   AND ood.operating_unit = pvsa.org_id
   AND asl.item_id = msib.inventory_item_id
   AND asl.using_organization_id = msib.organization_id
   AND ood.organization_id = msib.organization_id
   AND asl.asl_id = paa.asl_id
   AND asl.using_organization_id = paa.using_organization_id
   AND msib.organization_id = paa.using_organization_id
   AND ood.organization_id = paa.using_organization_id
   AND asl.asl_status_id = pas.status_id
   AND msib.segment1 = :ITEM NUMBER
Labels: 105 comments | edit post
How to kill a session based on the object name in oracle apps?
Group Team

This article is used to explain how to kill any session based on the object name.

a. Getting the session id for the Object name

Object type for the object name to be killed can be any one of the following
1.       TYPE
2.       CURSOR
3.       FUNCTION
4.       INDEX
5.       JAVA CLASS
6.       LIBRARY
7.       MULTI-VERSIONED OBJECT
8.       NON-EXISTENT
9.       NONE
10.    PACKAGE
11.    PACKAGE BODY
12.    PROCEDURE
13.    RULE EVALUATION CONTEXT
14.    SEQUENCE
15.    SYNONYM
16.    TABLE
17.    TRIGGER
18.    VIEW

Get the session id based on the object name based on below query

SELECT SID,
              owner,
                              OBJECT,
                              TYPE
  FROM v$access
 WHERE OBJECT = ‘Object Name’

b. Get the serial number for the session based on the session id got from above qyery.

SELECT SID,
             serial#,
                             ownerid,
                             status,
                             server,
                             username,
                             osuser,
                             process,
                             machine
  FROM v$session
 WHERE SID = ‘Session id from above query’

c. Command to kill the session

   ALTER SYSTEM KILL SESSION 'Sid from query2, Serial# from Query 2'

Labels: 2 comments | edit post
How to know the sql query that is being executed for the long running concurrent program in Oracle apps?
Group Team

This is the mostly faced issue while running Plsql based concurrent program.
We may not be sure which is the query being executed.
We can take a TKPROF and check the performance of the query.
But to get the TKPROF the program must be completed first.

We can use the below query to get the sql query that is being executed for the currently running Concurrent program.
Request id is the input for this query.


SELECT  fcr.request_id,
              fcp.user_concurrent_program_name,
              vsq.sql_text
  FROM  fnd_concurrent_requests fcr,
               v$process vp,
               v$session vs,
               v$sql vsq,
               fnd_concurrent_programs_vl fcp
 WHERE fcr.request_id = ‘Request Id’
   AND    fcr.oracle_process_id = vp.spid
   AND    vs.sql_address = vsq.address
   AND    vs.paddr = vp.addr
   AND    fcr.concurrent_program_id = fcp.concurrent_program_id;
Labels: 0 comments | edit post
How to kill a session which is locked in Oracle apps
Group Team


This Article is used to explain how to kill a session which is locked

  1. Check if the Package or table are locked using the below query

SELECT b.object_name,
              a.session_id,
                              a.oracle_username,
                              a.os_user_name,
                              a.process,
                              a.locked_mode
  FROM v$locked_object a,
              all_objects b
 WHERE a.object_id = b.object_id

If the Package or table are available in the above query then we need to kill the session

       b.    Get the serial number for the session based on the session id got from above qyery.


SELECT SID,
              serial#,
                             ownerid,
                             status,
                             server,
                             username,
                             osuser,
                             process,
                            machine
FROM v$session
              WHERE SID = ‘Session id from above query’

          c.    Command to kill the session

ALTER SYSTEM KILL SESSION 'Sid from query, Serial# from Query 2'
Labels: 4 comments | edit post
SQL Queries
Group Team
Below are the frequently used Queries

List of Program and Request set Attached to a Responsibility
Labels: 0 comments | edit post