LOV Personalization in Oracle EBS.

Open Form on which you want to personalize or customized the value set.

Through examine see the field name on which value set was assigned.

Open form in Oracle Form Builder and go to on your target field ( On which  you want to change the LOV query).

Find the Record Group name and open it.

Query of Record Group is shown below:

SELECT   p.full_name,

         nvl(p.employee_number,p.npw_number) employee_num,

         p.person_id employee_id

FROM     per_all_people_f p,

         per_all_assignments_f a

WHERE    (Nvl(:poxdoapp_globals.can_preparer_approve_flag,

              'N') = 'Y'

           OR (Nvl(:poxdoapp_globals.can_preparer_approve_flag,

                   'N') = 'N'

               AND p.person_id != :poxdoapp_globals.doc_owner_id))

         AND  p.business_group_id in (select fsp.business_group_id

                                 from financials_system_parameters fsp)

         AND  EXISTS (SELECT peh.superior_id

                      FROM   po_employee_hierarchies peh

                      WHERE  peh.position_structure_id = :po_approve.approval_path_id

                             AND peh.superior_id = p.person_id)

         AND a.person_id = p.person_id

         AND a.primary_flag = 'Y'

         AND Trunc(SYSDATE) BETWEEN p.effective_start_date

                                    AND p.effective_end_date

         AND Trunc(SYSDATE) BETWEEN a.effective_start_date

                                    AND a.effective_end_date

         AND (NVL(CURRENT_EMPLOYEE_FLAG,'N') = 'Y'

             OR NVL(CURRENT_NPW_FLAG,'N') = 'Y')

         AND a.assignment_type in ('E',decode(

             nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N'),'Y','C','E'))

         AND 'TRUE' = Decode(hr_security.view_all,

                             'Y',

                             'TRUE',

                             hr_security.Show_person(p.person_id,

                                                     p.current_applicant_flag,

                                                     p.current_employee_flag,

                                                     p.current_npw_flag,

                                                     p.employee_number,

                                                     p.applicant_number,

                                                     p.npw_number))

         AND 'TRUE' = Decode(hr_security.view_all,

                             'Y',

                             'TRUE',

                             hr_security.Show_record('PER_ALL_ASSIGNMENTS_F',

                                                     a.assignment_id,

                                                     a.person_id,

                                                     a.assignment_type))

ORDER BY p.full_name

Now Open Form Navigate to Personalization Window. Select Event WHEN NEW ITEM INSTANCE as shown below:

 

On Actions Tab add a bulletin and select Create Record Group Query:

Note: You should not change the column names and number of columns.

You can only change the query (add new tables or change whole query or change where clause or add or change order by clause) and you can also perform concatenation with the column that is already exists.

I have concatenated the name Position with Employee name:

SELECT   p.full_name || ' (' || SUBSTR(PFT.NAME,0,INSTR(PFT.NAME,'.')-1) || ')'  full_name,

         nvl(p.employee_number,p.npw_number) employee_num,

         p.person_id employee_id

FROM     per_all_people_f p,

         per_all_assignments_f a,

         hr_all_positions_f_tl pft

WHERE   

(Nvl(:poxdoapp_globals.can_preparer_approve_flag,

              'N') = 'Y'

           OR (Nvl(:poxdoapp_globals.can_preparer_approve_flag,

                   'N') = 'N'

               AND p.person_id != :poxdoapp_globals.doc_owner_id))

         AND 

         p.business_group_id in (select fsp.business_group_id

                                 from financials_system_parameters fsp)

         AND  EXISTS (SELECT peh.superior_id

                      FROM   po_employee_hierarchies peh

                      WHERE  peh.position_structure_id = :po_approve.approval_path_id

                             AND peh.superior_id = p.person_id)

         AND a.person_id = p.person_id

         and a.position_id = pft.position_id

         AND a.primary_flag = 'Y'

         AND Trunc(SYSDATE) BETWEEN p.effective_start_date

                                    AND p.effective_end_date

         AND Trunc(SYSDATE) BETWEEN a.effective_start_date

                                    AND a.effective_end_date

         AND (NVL(CURRENT_EMPLOYEE_FLAG,'N') = 'Y'

             OR NVL(CURRENT_NPW_FLAG,'N') = 'Y')

         AND a.assignment_type in ('E',decode(

             nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N'),'Y','C','E'))

         AND 'TRUE' = Decode(hr_security.view_all,

                             'Y',

                             'TRUE',

                             hr_security.Show_person(p.person_id,

                                                     p.current_applicant_flag,

                                                     p.current_employee_flag,

                                                     p.current_npw_flag,

                                                     p.employee_number,

                                                     p.applicant_number,

                                                     p.npw_number))

         AND 'TRUE' = Decode(hr_security.view_all,

                             'Y',

                             'TRUE',

                             hr_security.Show_record('PER_ALL_ASSIGNMENTS_F',

                                                     a.assignment_id,

                                                     a.person_id,

                                                     a.assignment_type))

ORDER BY p.full_name

Now Finally add Property and Select Object Type LOV and assign value to it.

Finally the resultant LOV is shown below: