Chitika Add

Tuesday, August 30

Making field as mandatory using Forms Personalization


Note : this example has been taken from "http://easyoracleapps.blogspot.com/search/label/Forms%20Personalization"

For quite some time I was thinking about publishing an article about forms personalization in Oracle HRMS.
The Metalink note on Forms Personalization is helpful, but what it lacks is a pictorial approach to implementing
Forms Personalizations. I am a visual animal, so I like to explain in that manner too.

My first article in the series of Forms Personalization is in response to a question raised in Oracle Forum under Oracle Human Resources (HRMS ). As per the Oracle forum request, If the Person Type is Employee, their clients wants Person Title field to become Mandatory ( lets assume it is the title field for now).  When the Person Type field changes to a value that is anything but Employee, the person title field should then toggle back to become optional.

Please note that when Person type Employee is selected, value in field
PERSON.D_PTU_USER_PERSON_TYPE  is assigned a value of “Employee”

Now the requirement is that for “Employee” field PERSON.D_TITLE must be made mandatory.

There are two possible ways the Person Type can change.
Either by picking a dropdown list of Action (e.g. Create Employee) or by directly picking up a value from LOV on field “Person Type for Action”. Whenever the person type changes, WHEN-NEW-ITEM-INSTANCE is fired for one for the below fields(depending upon how its changed).  Hence forms personalization must check conditions for below three fields
PERWSHRG.PERSON.PTU_ACTION_TYPE
PERWSHRG.PERSON.D_PTU_USER_PERSON_TYPE
PERWSHRG.PERSON.SHOW_NUMBER

The demo below contains conditional check on “WHEN-NEW-ITEM-INSTANCE” of PERSON.PTU_ACTION_TYPE

When implementing this, you will have to replicate the steps in the demo for WNII on both PERWSHRG.PERSON.D_PTU_USER_PERSON_TYPE & PERWSHRG.PERSON.SHOW_NUMBER

I have tested the steps below myself, and they appear to work.

OK, here we go.....

STEP 1
Create Personalization as below( to make Title field mandatory)
Sequence: 50
Description: Make Person Title Mandatory when Person Type is Employee.
Trigger Event: WHEN-NEW-ITEM-INSTANCE
Trigger Object: PERSON.PTU_ACTION_TYPE
Condition: ${item.person.d_ptu_user_person_type.value} = 'Employee
Check if Person Type is Employee in When New Item Instance
Check if Person Type is Employee in When New Item Instance


Action Sequence: 10
Action Type: Property
Action Object Type: Item
Action Target Object: PERSON.D_TITLE
Action Property Name: REQUIRED
Action Value: TRUE
Make Title mandatory when Person Type is Employee
Make Title mandatory when Person Type is Employee



STEP 2
Create another Personalization as below ( to make Title field Optional)
Sequence: 51
Description: Make Person Title Mandatory when Person Type is Employee.
Trigger Event: WHEN-NEW-ITEM-INSTANCE
Trigger Object: PERSON.PTU_ACTION_TYPE
Condition: NVL(${item.person.d_ptu_user_person_type.value},'xxyyzz') != 'Employee'
Check if Person Type is anything other than Employee in When New Item Instance
Check if Person Type is anything other than Employee in When New Item Instance
 

Action Sequence: 10
Action Type: Property
Action Object Type: Item
Action Target Object: PERSON.D_TITLE
Action Property Name: REQUIRED
Action Value: FALSE
Make Title option when Person Type is not Employee
Make Title option when Person Type is not Employee

Friday, August 26

How to get Login Passwords for application & Data Base instances


Most often, oracle apps developers have to work in different DB instances for development as well as for testing purposes. In such situations we need to get access to different oracle application instances and data base (dev/test/crp etc.,) instances. So we have to request & follow a lengthy approval process to get those login details, some times you may end up in loosing your delivery schedules.


There is a simple way by which you can get the logins/passwords of your DB & Application with out chasing the DBAs.
Oracle follows an encryption algorithm to encrypt user passwords. Most references to the encryption algorithm point to either the PL/SQL package APPS.FND_WEB_SEC or the Java class "oracle.apps.fnd.security.WebSessionManagerProc".

For decryption and encryption, the following calls are made:
APPS.FND_WEB_SEC >
oracle.apps.fnd.security.WebSessionManagerProc>
oracle.apps.fnd.security.AolSecurity>
oracle.apps.fnd.security.AolSecurityPrivate

The actual encryption and decryption routines are in the "oracle.apps.fnd.security.AolSecurityPrivate" Java class. This Java class is stored both in the database as a Java Stored Procedure and in the operating system directory $COMMON_TOP/java.



Create a package specification: get_pwd
-- Package Specification
CREATE OR REPLACE PACKAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2;
END get_pwd;


Create the package body: get_pwd

-- Package Body 
CREATE OR REPLACE PACKAGE BODY get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2 AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;


Call the package function as shown below:
/** Run this on toad, Get the DB apps password */
SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))

Call the package function as shown below:
/** Run this on toad, Get the application usernames and passwords */
SELECT usertable.user_name
, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('username') -- Here username is application login such as 'OPERATIONS'

XML Template code and template name


we can get Lob_Code  from this query to get the details of  RTF files.  Query with lob_code in the application in case if we don't know the  template name

select * from XDO_LOBS
where file_name like '%po_terms%'
and XDO_File_type='RTF'
--and trunc(last_update_date)=trunc(sysdate)

Find Vacation rule details of an user


select * from apps.WF_ROUTING_RULES
where role='<USER NAME>'

we can also check vacation rules for other users by
Oracle Administrator > Workflow status monitor > Administration > Vacation Rules

Need not login as the same user to check for vacation rule details.

Query to get Request group of a program

Using this query we can get the list of request groups to which our concurrent program has been assigned.

select * from fnd_request_groups where request_group_id IN
(
                             SELECT request_group_id
                               FROM fnd_request_group_units
                              WHERE request_unit_id =
                                       (SELECT DISTINCT concurrent_program_id
                                                   FROM fnd_concurrent_programs_tl
                                                  WHERE user_concurrent_program_name =
                                                           '<Concurrent Program Name>'))

Script to find the users logged in to application



SELECT ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address) AS email_address,fl.end_time,fl.start_time,(fl.end_time-fl.start_time)
FROM apps.per_people_x ppx, apps.fnd_user fu, apps.fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppx.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST')
AND fu.user_name in (<enter the user names seperated by comma ','>)
AND fl.end_time is not null
GROUP BY ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address)
,fl.end_time
,fl.start_time
ORDER BY 2Bookmark

Query to get the list of responsibility's to which concurrent program has assigned


 SELECT DISTINCT *
           FROM apps.fnd_responsibility_tl
          WHERE responsibility_id IN (
                   SELECT responsibility_id
                     FROM apps.fnd_responsibility_vl
                    WHERE request_group_id IN (
                             SELECT request_group_id
                               FROM apps.fnd_request_group_units
                              WHERE request_unit_id =
                                       (SELECT DISTINCT concurrent_program_id
                                                   FROM Apps.fnd_concurrent_programs_tl
                                                  WHERE user_concurrent_program_name =
                                                           '<Concurrent Program Name>'))
                      AND end_date IS NULL)
 AND "LANGUAGE" LIKE 'US'
       ORDER BY responsibility_name

Query to find the user, responsibility and concurrent program details of submitted requests


Select B.user_concurrent_program_name,C.user_name,D.responsibility_name, A.* 
from apps.fnd_concurrent_requests A, apps.fnd_concurrent_programs_tl B, Apps.fnd_user C, apps.fnd_responsibility_tl D
 where 1=1
and  B.user_concurrent_program_name like 
 '<Enter the concurrent program name>'
 and B.concurrent_program_id=A.concurrent_program_id
 and A.requested_by=C.user_id
 and A.responsibility_id=D.responsibility_id
and b.language=userenv('LANG')
and d.language=userenv('LANG')
 order by request_date desc

---------------------------------------

Including the Out put views 


SELECT E.FILE_NAME,B.USER_CONCURRENT_PROGRAM_NAME,C.USER_NAME,D.RESPONSIBILITY_NAME, A.* 
from apps.fnd_concurrent_requests A, apps.fnd_concurrent_programs_tl B, Apps.fnd_user C, apps.fnd_responsibility_tl D , apps.FND_CONC_REQ_OUTPUTS_V E
 WHERE 1=1
and  B.user_concurrent_program_name like  'Hologic Printed Purchase Order Report'
 and B.concurrent_program_id=A.concurrent_program_id
 AND A.REQUESTED_BY=C.USER_ID
 and c.user_name ='LMEDEIROS'
 AND A.RESPONSIBILITY_ID=D.RESPONSIBILITY_ID
 AND E.REQUEST_ID(+)=A.REQUEST_ID
and b.language=userenv('LANG')
and d.language=userenv('LANG')
 order by request_date ;