donderdag 25 februari 2016

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;

dinsdag 23 februari 2016

Installation custom application in eBS 12.2.*

Reference: Creating a Custom Application in Oracle E-Business Suite Release 12.2 (Doc ID 1577707.1)

In R12.2 registering a custom application works different than for older releases. In our example we will register a new application called XXX.
In R12.2.x Oracle has introduced online patching, which means there are two file editions, called RUN and PATCH.
We no longer register applications using the Applications form, but will be using adsplice instead.

Step 1: Create the new database schema

CREATE USER XXX
IDENTIFIED BY XXX
DEFAULT TABLESPACE apps_ts_tx_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON apps_ts_tx_data
QUOTA UNLIMITED ON apps_ts_tx_idx;

GRANT CONNECT, RESOURCE, QUERY REWRITE, CREATE SESSION TO XXX;


Step 2: Download the patch 3636980 (Support Diagnostics (IZU) patch for AD Splice). This patch is generic for R12.

Step 3: Unzip the files in the patch and copy the following three files from p3636980_R12_GENERIC\3636980\izu\admin to your desktop/ current working folder.

izuprod.txt
izuterr.txt
newprods.txt

Step 4:Rename the file izuprod.txt to xxxprod.txt and izuterr.txt to xxxterr.txt So newprods.txt remains intact.

Step 5: Open newprods.txt and replace all references of izu to xxx (or your custom module) and all references of IZU to XXX. Make sure you use correct case.

Step 6: Open xxxprod.txt and do the same by replacing izu by xxx (and IZU by XXX).
Now change the application/product id from 278 to your own unique number (3 occurrences). You can select the highest number used so far using

select max (application_id) from fnd_application;
select max (oracle_id) from fnd_oracle_userid;

and then increase by at least 1, but Oracle recommends to use an id above 50000 .

Make sure you change it also at

"
# install oracle id, default ORACLE username, default ORACLE password
50001 XXX XXX

# application id, abbreviation, shortname, prefix
50001 xxx XXX APP
"

Step 7: Open xxxterr.txt. Change all references of izu to xxx and IZU to XXX.
Change 'Oracle_Support_Diagnostic_Tools' to 'xxx_custom_application".

Step 8: Copy the files to $APPL_TOP/admin directory.

Step 9: Navigate to $APPL_TOP/admin and run adsplice.

Is this the correct APPL_TOP: <enter>
Filename (adsplice.log): adsplice_xxx.log
Is this the correct database: <enter>
Enter the password for your 'SYSTEM' ORACLE schema: <your password>
Enter the ORACLE password of Application Object Library [APPS]: <enter or your password for APPS>
...
Please enter the directory where your AD Splicer control file is located.
The default directory is [.../admin]: <enter>
Please enter the name of your AD Splicer control file [newsprods.txt]: <enter>
...
Do you wish to regenerate your environment file [Yes]: <enter>
..
Check the logfile adsplice_xxx.log for errors.

Step 10: Check the setup

select * from fnd_application where application_short_name = 'XXX';
select * from fnd_product_installations where APPLICATION_ID = 50001;
select * from dba_users where username = 'XXX';

Re-login to Application server and check for environment file
$ env |grep XXX
XXX_TOP=/u01/app/oracle/fs1/EBSapps/appl/xxx/12.0.0
$
$ ls $XXX_TOP
admin  log  mesg  out  sql

You now create the other additional directories if needed like bin, /forms/US, include, lib, mds, reports/US, workflow as described in section 4 (create custom objects).

#!/bin/ksh
echo 'Script: $Id: XXX_CUSTOM_DIRECTORY.sh 894 2009-12-04 12:09:55Z apps12 $'
# Create custom application directory structure.
if [ $# -ne 1 ]; then
  echo "Please supply a single custom application short name !"
  exit 1
fi
echo "Creating custom application structure for custom application: $1"
if [ -z $APPL_TOP/12.0.0/ ]; then
  echo "APPL_TOP/12.0.0/ environment variable has not been set !"
  exit 1
fi
if [ -z $COMMON_TOP/12.0.0/ ]; then
  echo "COMMON_TOP/12.0.0/ environment variable has not been set !"
  exit 1
fi
cd $APPL_TOP/${1}_TOP/12.0.0
mkdir -p CEMLIs
mkdir -p bin
mkdir -p data
mkdir -p data/backup
mkdir -p data/in
mkdir -p data/out
mkdir -p forms
mkdir -p forms/US
mkdir -p forms/NL
mkdir -p help
mkdir -p help/US
mkdir -p help/NL
mkdir -p html
mkdir -p install
mkdir -p install/driver
mkdir -p install/import
mkdir -p install/rtf
mkdir -p install/sql
mkdir -p install/workflow
mkdir -p java
mkdir -p log
mkdir -p mds
mkdir -p media
mkdir -p mesg
mkdir -p out
mkdir -p reports
mkdir -p reports/US
mkdir -p reports/NL
mkdir -p resource
mkdir -p sql
mkdir -p xml
mkdir -p files
mkdir -p files/loaded



If you are using a shared APPL_TOP, you run autoconfig on the other nodes and do not rerun adsplice. If it's not shared, you have to repeat the steps for each node.

When you start the next online patching cycle, the prepare phase will run adsplice sync-up actions to synchronize the two file systems.
Check for a known bug 18815526:R12.AD.C in case adsplice sync-up fails when prepare phase is run.