Fetch profile options

To find profile options set on different levels you can use the following query. You can filter on level (application, responsibility, user, etc) if necessary.

SELECT AP.Application_Short_Name

,      OP.PROFILE_OPTION_NAME
,      OP_T.USER_PROFILE_OPTION_NAME
,      DECODE (OV.Level_Id,10001,'Site', 10002,'Applicatie',10003, 'Autorisatie', 10004,'Gebruiker') level_Id
,      DECODE (OV.Level_Id,10002,

(SELECT A.Application_Short_Name FROM FND_APPLICATION A WHERE A.Application_Id = OV.Level_Value)
                          ,10003,

(SELECT R.Responsibility_Name FROM FND_RESPONSIBILITY_TL R, FND_RESPONSIBILITY RR WHERE R.Responsibility_Id = OV.Level_Value   AND OV.Level_Value_Application_Id = R.Application_Id AND R.Language = USERENV ('LANG')

AND R.Responsibility_Id = RR.Responsibility_Id AND R.Application_Id = RR.Application_Id  )
                          ,10004,

(SELECT U.User_Name FROM FND_USER U WHERE U.User_Id = OV.Level_Value)
                          , OV.Level_Value) Level_Value
,      OV.Level_Value
,      OV.Profile_Option_Value
FROM FND_PROFILE_OPTION_VALUES    OV
,    FND_PROFILE_OPTIONS          OP
,    FND_PROFILE_OPTIONS_TL       OP_T
,    FND_APPLICATION              AP
WHERE OV.Profile_Option_id          = OP.Profile_Option_id
AND   OP.Profile_Option_Name        = OP_T.PROFILE_OPTION_NAME
AND   OV.Application_Id             = AP.Application_Id
;

Find eBS table based on value in column

Sometimes I know the value that is used somewhere in a screen, but I have no idea what tables are behind it. So I wrote a dynamic sql script to run through a number of tables to find the table and column that holds the value.

Run it using the value you are searching for and a restriction on the tables.
For example

@FINDVALUE 3004 AR

To find all AR tables that have any column with a value 3004. Currently I only look for CHARs and NUMBERs, but of course you can expand if desired.


SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF

DECLARE
  p_table_name   VARCHAR2(240) := '&&2';
  p_value        VARCHAR2(240) := '&&1';
  v_statement    VARCHAR2(8000);
  v_base_statement VARCHAR2(8000);
  v_found        VARCHAR2(240);
  TheResult      NUMBER;
 
  CURSOR C_Tables
  IS
  SELECT Table_Name
  FROM   ALL_TABLES
  WHERE  Table_Name like p_table_name || '%';
 
  CURSOR C_Table_Cols
  (
    cp_table_name VARCHAR2
  )
  IS
  SELECT Column_Name, Table_Name, Data_Type
  FROM   ALL_TAB_COLUMNS
  WHERE Table_Name = cp_table_name
  AND   Data_Type IN ('NUMBER', 'VARCHAR2')
  ;
 
 
BEGIN

  DBMS_OUTPUT.Put_Line ('List of tables with value '|| p_value);
  DBMS_OUTPUT.Put_Line ('---------------------------------------------------------------------');

  <<Table_Def>>
  FOR T IN C_Tables
  LOOP

      v_base_statement := 'SELECT 1 FROM ' || T.Table_Name || ' WHERE ';
     
      <<Table_Cols>>
      FOR C IN C_Table_Cols (cp_table_name => T.Table_Name)
      LOOP
        IF C.Data_Type = 'NUMBER'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ' || p_value;
        ELSIF C.Data_Type = 'VARCHAR2'
        THEN
          v_statement := v_base_statement || C.Column_Name || ' = ''' || p_value || '''';
        END IF;
        TheResult := 0;
        BEGIN
          --dbms_output.put_line ('Testing ... ' || v_statement);
          execute immediate (v_statement) INTO TheResult;
          --dbms_output.put_line ('... '||  v_statement || ' returns TRUE');
          --dbms_output.put_line ('Result: ' || TheResult);
          EXCEPTION
            WHEN Others THEN
              --dbms_output.put_line ('Error: ' || SQLERRM);
              TheResult := 0;
        END;
       
        IF TheResult = 1
        THEN
          v_found := T.Table_Name || '.' || C.Column_Name || ' (' || C.Data_Type || ')';
          DBMS_OUTPUT.Put_Line (v_found);
        END IF;
      END LOOP Table_Cols;
     
  END LOOP Table_Def;
 
END;
/

Another sample for the same issue is as follows from Saubhik

SELECT   DISTINCT
                 SUBSTR(:val, 1, 11) "Searchword",
                  SUBSTR(table_name, 1, 14) "Table",
                 SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
      FROM     cols,
                   table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype(   'select '
                                                           || column_name
                                                           || ' from '
                                                           || table_name
                                                        || ' where (UPPER('''
                                                         || :val
                                                         || ''')=UPPER('
                                                        || '))').EXTRACT('ROWSET/ROW/*'))) t
    WHERE    table_name IN ('EMP', 'DEPT','EMPLOYEES') --limiting the table names, you can omit this.
     ORDER BY "Table"

  /

Query to find last login date for a user/responsibility

The following query may help to find users that haven't logged in for a long time or which responsibilities are actually used in the system.  You may filter as well on the count or date to print only users who haven't logged in for a year or not more than 10 times, etc.
The first query shows you per user when he logged in last, how long ago that was, how many times he logged in and whether he's an employee who's not terminated yet ..

I use two views. The second also fetches the last responsibility used. Note that per login you can have multiple records in fnd_login_responsibilities, but you can also have none ..

create or replace view xxx_user_counts_v
as
SELECT      U.User_Id
, U.User_Name
,       PX.Full_Name
,       (SELECT Count (D.Responsibility_Id)
         FROM   FND_USER_RESP_GROUPS_DIRECT D
         WHERE  D.User_Id = U.User_id) Nr_of_Responsibilities
,       (SELECT   PS.Actual_Termination_Date
         FROM   PER_PERIODS_OF_SERVICE PS
         WHERE  PS.Person_Id = U.Employee_Id
         AND      PS.Period_Of_Service_Id =
          (
            SELECT Max (PS2.Period_Of_Service_Id)
            FROM PER_PERIODS_OF_SERVICE PS2
            WHERE PS2.Person_Id = U.Employee_Id
          )
        ) Actual_Termination_Date
,           To_Char (Max (L.Start_Time),'DD-MM-YYYY HH24:MI:SS') Last_Login_Date
,           To_Char (Max (LR.Start_Time),'DD-MM-YYYY HH24:MI:SS') Last_Login_Date_Resp
,           Round ((Sysdate-Max (L.Start_Time))) Days_Since_Login
,           Count (L.Login_Id) Nr_Of_Logins
,           Max (L.Login_Id) Last_Login_Id
FROM      FND_USER          U
,           FND_LOGINS                  L
,           FND_LOGIN_RESPONSIBILITIES  LR
--,           FND_RESPONSIBILITY_VL       R
,       PER_PEOPLE_X        PX
WHERE       U.User_Id                          = L.User_Id (+)
AND         L.Login_Id                   = LR.Login_Id  (+)
--AND         LR.Responsibility_Id       = R.Responsibility_Id (+)
AND         (U.End_Date IS NULL OR U.End_Date > Sysdate)
AND     U.EMPLOYEE_ID         = PX.Person_Id (+)
GROUP BY  U.User_Name
,       U.User_Id
,     PX.Full_Name
,       U.Employee_Id
;



create or replace view xxx_user_counts_v2
as
select c.*
, (select rl.responsibility_name
   from FND_RESPONSIBILITY_VL rl
   , fnd_login_responsibilities r
   where r.login_id = c.last_login_id
   and r.responsibility_id = rl.responsibility_id
   and r.login_resp_id =
   (
     select max (r2.login_resp_id)
     from fnd_login_responsibilities r2
     where r2.login_id = r.login_id
   )
   ) last_resp_used
from xxx_user_counts_v c
;

Now you can do queries like 

Number of users who never have logged in, but do have an authorization

select count(*) from xxx_user_counts_v2 where nr_of_logins = 0 and nr_of_responsibilities > 0;

Number of users who haven't logged in for half a year and who have authorizations

select count(*) from xxx_user_counts_v2 where Days_Since_Login > 180 and nr_of_responsibilities > 0; 

Users who's employee record have been terminated, but still have access

select count(*) from xxx_user_counts_v2 where actual_termination_date < sydate;

Fetch GL Period based on date

To fetch the period name based on a specific date you can use a query like below. In The period statuses table you'll find a record for each application that is using it, so make sure you add this to your where clause (could be AR, SQLAP, etc).

SELECT  L.Ledger_Id
,       L.Period_Set_Name
,       P.Show_Status
,       P.Start_Date
,       P.End_Date
,       P.Period_Name
,       P.Adjustment_Period_Flag
,       A.Application_Short_Name
FROM    GL_LEDGERS L
,       GL_PERIOD_STATUSES_V P
,       FND_APPLICATION A
WHERE   L.Name            = mo_utils.Get_Ledger_Name(fnd_profile.value ('ORG_ID') )
AND     L.Ledger_Id       = P.Ledger_Id
AND     P.Application_Id  = A.Application_Id
AND     P.Period_Type     = L.Accounted_Period_Type
AND     Sysdate BETWEEN P.Start_Date AND P.End_Date
AND     A.Application_Short_Name = 'SQLGL'
AND     P.Adjustment_Period_Flag = 'N'
;

Supplier contacts

Suppliers and their contacts are also created in HZ_PARTIES. The supplier is an organization, where the contact is a person. They get a relationship in HZ_RELATIONSHIPS (usually type CONTACT_OF).
You can query the suppliers in hz_parties with their relationships as follows. They can be related to site or to top level.
Note that HZ_RELATIONSHIPS usually contains two records. One for the PERSON-ORGANIZATION relationship (type CONTACT_OF) and one for the ORGANIZATION-PERSON relationship (CONTACT usually). You can check on subject_type and object_type to make sure you fetch the right relationship.

select asup.vendor_name
      ,asup.segment1 vendor_number
      ,org_party.party_name  organization
      ,cont_party.party_name   contact_person
      ,cont_party.party_number contact_person_number
      , hr.status relation_status
      , org_party.status org_status
      , cont_party.status contact_status
      , hoc.status contact_status
from   hz_org_contacts hoc
      ,hz_relationships hr
      ,hz_parties org_party
      ,hz_parties cont_party
     ,ap_suppliers asup
where hr.relationship_id    = hoc.party_relationship_id
and   hr.subject_id         = cont_party.party_id
and   hr.subject_table_name = 'HZ_PARTIES'
and hr.subject_type = 'PERSON'
and hr.object_type = 'ORGANIZATION'
and   hr.relationship_code  = 'CONTACT_OF'
and   hr.object_id          = org_party.party_id
and   hr.object_table_name  = 'HZ_PARTIES'
and   asup.party_id         = org_party.party_id
and   hoc.party_site_id     IS NULL
and   org_party.status      = 'A'
and    cont_party.status    = 'A'
and   hoc.status            = 'A'
;

Fetch last active contract for a given item and customer account

Fetch last active contract for a given item and customer account

This query retrieves the contract related to a specific item for an account.

     SELECT  H.Id
       ,       H.Contract_Number
       ,       H.Contract_Number_Modifier
       ,       L.Start_date -- H.Start_Date
       ,       L.End_Date -- H.End_Date
       ,       DECODE (H.Sts_Code,'ACTIVE',L.Sts_Code,H.Sts_Code) Sts_Code
       ,       HT.Short_Description
       FROM    OKC_K_LINES_B L
       ,       OKC_K_ITEMS   I
       ,       OKC_K_HEADERS_B H
       ,       OKC_K_HEADERS_TL HT
       ,       HZ_CUST_SITE_USES_ALL BAUI
       ,       HZ_CUST_SITE_USES_ALL SAUI
       ,       hz_cust_acct_sites_all bsi
       ,       hz_cust_acct_sites_all ssi
       WHERE   1=1
       -- Site use of the contract line relate to cust account
       AND     L.Bill_To_Site_Use_id = BAUI.Site_Use_Id
       AND     L.Ship_To_Site_Use_Id = SAUI.Site_Use_Id
       AND     BAUI.Cust_Acct_SIte_Id = bsi.cust_acct_site_id
       AND     SAUI.Cust_Acct_Site_Id = ssi.cust_acct_site_id
       AND     bsi.cust_account_id = cp_bill_account_id
       AND     ssi.cust_account_id = cp_ship_account_id
       --
       AND     L.ID = I.Cle_Id
       AND     H.Id = HT.Id
       AND     HT.Language = USERENV ('LANG')
       AND     I.Object1_Id1 IN cp_inventory_item_id
       AND     I.Object1_Id2 = cp_organization_Id
       AND     L.Dnz_Chr_Id = h.id
       -- Fetch active contracts first, then the one with the latest end date/start date.
       -- If you find multiple lines with the same end date, take the lowest
       -- start date.
       ORDER BY DECODE (h.sts_code,'ACTIVE',0,1), NVL (L.End_Date,L.Start_Date) DESC, L.Start_Date,H.Contract_Number desc
       ;

Hierarchical query on employees and their supervisor in Oracle HR

Hierarchical query on employees and their supervisor in Oracle HR

We all know the famous emp/dept hierarchical queries to show employees belonging to a department and employees with their manager. I recently came across a question on how to query all employees and their managers (called supervisors in HR) in a hierarchical manor so I created this query as combination of what I've read in https://technology.amis.nl/2005/08/16/hierarchical-query-with-nodes-from-different-tables-dept-and-emp-nodes-in-one-tree/) and Oracle database schema.

Note that the employee - supervisor link can be circular. In that case you will not find those employees in the tree, since there is no top node where the manager (supervisor_id) is empty.

with emps as
(
-- The managers
select s.person_id empno
,      s.first_name || ' ' || s.last_name ename
,      sa.supervisor_id mgr
from   per_people_x s
, per_assignments_x sa
where    s.person_id = sa.person_id
and exists (select 1 from per_assignments_x a where a.supervisor_id = s.person_id)
union
-- The employees
select p.person_id empno -- all EMP-nodes
,      p.first_name || ' ' || p.last_name ename
,      a.supervisor_id mgr
from   per_people_x p, per_assignments_x a
where   p.person_id = a.person_id
)
select lpad(' ', level*3)||ename ename
from   emps
connect
by     prior empno = mgr
start
with  
nvl (mgr,-1) = -1
/