Home » Developer & Programmer » Forms » How do I disable an LOV for existing records and enabling only for NEW records that will be created
| How do I disable an LOV for existing records and enabling only for NEW records that will be created [message #115839] |
Thu, 14 April 2005 12:15  |
mlgonsalv
Messages: 35
Registered: March 2005
Location: DC
|
Member |
|
|
Hi All! This issue is regarding granting admin rights on form objects to certain Individuals i.e. supervisors to certain fields in the application. For all users, certain objects will be disabled all the time unless they are given rights based on their username.
In the database, I created a table- form_rights with two fields- username and privilege. The username lists the all the users who have certain privileges to certain objects in the application.
In granting admin rights, I have created a procedure called set_user_fields which enables and disables certain fields/tab pages based on roles. This proc is called at the form level.
Now, my question is, what trigger would I need to enable a field for all users when they are creating a NEW record. For existing records, this LOV should be disabled all the time for all users except certain ones.
Any suggestions would be greatly appreciated.
Thanks
Michael
|
|
|
|
| Re: How do I disable an LOV for existing records and enabling only for NEW records that will be crea [message #115954 is a reply to message #115839] |
Fri, 15 April 2005 08:28  |
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
|
Senior Member |
|
|
Mike -
What version of internet Application Server (iAS) are you running? Also, the version of Forms you will be using to create the interface?
I am on Forms 6i and iAS 11.5.8. What I did to handle privelages within a form based on a user_name or a responsibility was create a view that held all the fields I would need to display in the Form. In the CREATE VIEW syntax (the view holds no data when it is created) I included as part of the WHERE condition the following clause:
AND papf.PERSON_ID IN (SELECT AK.NUMBER_VALUE
FROM AK_WEB_USER_SEC_ATTR_VALUES AK,
FND_USER FND,
PER_ALL_PEOPLE_F PAPF1
WHERE
PAPF1.PERSON_ID = FND.EMPLOYEE_ID
AND PAPF1.EFFECTIVE_END_DATE = TO_DATE('47121231','YYYYMMDD')
AND FND.USER_ID = AK.WEB_USER_ID
AND AK.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'
AND FND.USER_ID = FND_GLOBAL.USER_ID);
papf = HR table Per_All_People_F
The FND_GLOBAL.USER_ID will return the current users ID (whom is logged into the application)
The effective end date is how we track our current employees. If their end date isn't 4712, then we know they are not a current employee.
What this is going to do is restrict the amount of records that the user can query. The view is dynamically populated based on the user who is logged in. The view will only hold records of those employees who are assigned to the current user.
Then on the Forms side, in the PRE_FORM trigger I wrote this code:
DECLARE
l_user_responsibility_id NUMBER;
BEGIN
/* Recreate the view (before launch, holds no data) with Current User ID package call
to populate the view with the appropriate data */
FORMS_DDL('CREATE OR REPLACE FORCE VIEW RIDOT.RIDOT_EMP_LEAVE_BALANCES_V
(SSN, PERSON_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAMES,
ACCT, PAYGRADE, WORKWEEK, ORGANIZATIONS, START_DATE,
EFFECTIVE_END_DATE, UNIONS, LAST_UPDATE_DATE, PAY_PERIOD_DATE, VAC_HOURS,
SIC_HOURS, DEF_HOURS, PERS_HOURS, FAM_HOURS, MIL_HOURS,
SB_HOURS, EXTRA_HOURS, FROZEN_HOURS)
AS
select
papf.national_identifier SSN,
papf.person_id,
papf.last_name,
papf.first_name,
papf.middle_names,
paaf.ass_attribute6 Acct,
paaf.ass_attribute14 PayGrade,
paaf.normal_hours WorkWeek,
hraou.name Organizations,
to_char(paaf.effective_start_date, '|| 'DD-MON-RRRR' || ') START_DATE,
papf.effective_end_date,
CASE WHEN LENGTH(ppg.segment1) < 3 THEN
LPAD(NVL(ppg.segment1, ' || '99'|| '), 3, 0)
ELSE NVL(ppg.segment1, ' || '099'|| ')
END UNIONS,
elb.last_update_date,
elb.pay_period_date,
NVL(elb.vacation_hours, 0) VAC_Hours,
NVL(elb.sick_hours, 0) SIC_Hours,
NVL(elb.deferred_vac_hours, 0) DEF_Hours,
NVL(elb.personal_leave_hours_YTD, 0) PERS_Hours,
NVL(elb.family_sick_hours_YTD, 0) FAM_Hours,
NVL(elb.military_leave_hours_YTD, 0) MIL_Hours,
NVL(elb.sick_bank_hours, 0) SB_Hours,
NVL(elb.extra_time, 0) EXTRA_Hours,
NVL(elb.frozen_vacation, 0) FROZEN_Hours
from
per_all_people_f papf,
per_all_assignments_f paaf,
pay_people_groups ppg,
hr.hr_all_organization_units hraou,
ridot_emp_leave_balances elb
where
papf.person_id = paaf.person_id
AND paaf.organization_id = hraou.organization_id
AND elb.employee_ssn = papf.national_identifier
AND ppg.people_group_id = paaf.people_group_id
AND paaf.ass_attribute14 IS NOT NULL
AND to_char(paaf.effective_end_date, ' || 'RRRR' || ') = ' || '4712' || '
AND to_char(papf.effective_end_date, ' || 'RRRR' || ') = ' || '4712' || '
AND (papf.employee_number IS NOT NULL OR papf.person_type_id = 3)
AND papf.PERSON_ID IN (SELECT AK.NUMBER_VALUE
FROM AK_WEB_USER_SEC_ATTR_VALUES AK,
FND_USER FND,
PER_ALL_PEOPLE_F PAPF1
WHERE
PAPF1.PERSON_ID = FND.EMPLOYEE_ID
AND PAPF1.EFFECTIVE_END_DATE = TO_DATE(' || '47121231' ||',' ||'YYYYMMDD' ||')
AND FND.USER_ID = AK.WEB_USER_ID
AND AK.ATTRIBUTE_CODE = ' || 'ICX_HR_PERSON_ID' || '
AND FND.USER_ID = FND_GLOBAL.USER_ID);');
/* Restrict Form Functionality dependant on the Responsibility */
l_user_responsibility_id := FND_GLOBAL.RESP_ID;
IF l_user_responsibility_id = XXXX /* value removed */ THEN
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.SSN', VISIBLE, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_VACATION', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_SICK', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_DEFERRED', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_PERSONAL', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_FAMILY', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_MILITARY', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_SB', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_FROZEN', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.TMP_EXTRA', ENABLED, PROPERTY_FALSE);
SET_ITEM_PROPERTY('RIDOT_EMP_LEAVE_BALANCES_V.BTN_UPDATE', ENABLED, PROPERTY_FALSE);
END IF;
The view is populated when the form loads in the application with the correct data. The last part of the code is an example of how to restrict certain items in the form based on the responsibility or, if you wanted to, based on the user.
HTH,
Steve
|
|
|
|
Goto Forum:
Current Time: Thu Sep 19 21:38:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
