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:
