[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT o.object_id
, o.database_object_name
, o.pk1_column_name
, o.pk1_column_type
, o.pk2_column_name
, o.pk2_column_type
FROM fnd_objects o
WHERE o.obj_name = cp_obj_name
;
SELECT VALUE
FROM hri_cl_per_n_v
WHERE ID = cp_person_id
AND cp_date BETWEEN effective_start_date AND effective_end_date
;
SELECT VALUE
FROM hri_cl_per_n_v
WHERE ID = cp_person_id
AND cp_date BETWEEN effective_start_date AND effective_end_date
;
SELECT m.menu_id
FROM fnd_menus m
WHERE m.menu_name =cp_menu_name
;
SELECT g.grant_guid
FROM fnd_grants g
WHERE g.grantee_type = cp_grantee_type
AND g.grantee_key = cp_grantee_key
AND g.menu_id = cp_menu_id
AND (cp_end_date IS NULL
OR g.start_date <= cp_end_date)
AND (g.end_date IS NULL
OR cp_start_date <= g.end_date)
AND g.object_id = cp_object_id
AND g.instance_pk1_value = cp_instance_pk1_value
AND ( cp_instance_pk2_value IS NULL
OR cp_instance_pk2_value = g.instance_pk2_value)
AND g.parameter1 = cp_parameter1
AND g.program_name = g_c_program_name
;
PROCEDURE update_delegation_grants
( p_delegate_type IN VARCHAR2
,p_grantee_key IN VARCHAR2 DEFAULT NULL
,p_instance_pk1_value IN VARCHAR2
,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
,p_start_date IN DATE DEFAULT SYSDATE
,p_end_date IN DATE DEFAULT SYSDATE
,p_menu_id IN NUMBER DEFAULT NULL
,x_success OUT NOCOPY VARCHAR /* Boolean */
,x_errorcode OUT NOCOPY VARCHAR2
,p_update_mode IN VARCHAR2 DEFAULT 'EXTEND'
)
IS
--
l_result BOOLEAN;
SELECT g.grant_guid grant_guid
, g.START_DATE start_date
, g.end_date end_date
, g.program_name
, g.grantee_type
, g.grantee_key
, g.menu_id
, g.object_id
, g.instance_pk1_value
, g.instance_pk2_value
, g.parameter1
FROM fnd_grants g
WHERE g.grantee_type = cp_grantee_type
AND (cp_grantee_key IS NULL
OR g.grantee_key = cp_grantee_key)
AND g.menu_id = cp_menu_id
AND (cp_end_date IS NULL
OR g.start_date <= cp_end_date)
AND (g.end_date IS NULL
OR cp_start_date <= g.end_date)
AND g.object_id = cp_object_id
AND g.instance_pk1_value = cp_instance_pk1_value
AND ( cp_instance_pk2_value IS NULL
OR cp_instance_pk2_value = g.instance_pk2_value)
AND g.parameter1 = cp_parameter1
AND g.program_name = g_c_program_name
ORDER BY g.parameter1
,g.program_name
,g.menu_id
,g.grantee_key
,g.instance_pk1_value
,g.start_date
;
dbg(' Update_Delegate_Grants------');
dbg(' p_mode_type :'||p_update_mode);
IF (p_update_mode = 'EXTEND') THEN
IF ( --1.
c_rec.program_name = c_rec_prev.program_name
AND c_rec.parameter1 = c_rec_prev.parameter1
AND c_rec.object_id = c_rec_prev.object_id
AND c_rec.grantee_type = c_rec_prev.grantee_type
AND c_rec.grantee_key = c_rec_prev.grantee_key
AND c_rec.menu_id = c_rec_prev.menu_id
AND c_rec.instance_pk1_value = c_rec_prev.instance_pk1_value
AND NVL(c_rec.instance_pk2_value,'X')
= NVL(c_rec_prev.instance_pk2_value,'X')
)
THEN
-- At this point a record has already been extended
-- and this record is duplicating a period of the grant
-- hence need to determine 2 different cases
-- A. current record is entirely overlapped by prev new record
--- in this case need to delete duplicate record
-- B. current record partially overlapped by prev. record
--- in this case need to update the record
--
dbg(' Overlapping delegation grant found');
THEN -- delete
dbg(' deleting completely overlapped grant....');
dbg('Record Deleted');
fnd_grants_pkg.update_grant
(p_api_version => 1.0
,p_grant_guid => c_rec.grant_guid
,p_start_date => l_start_date
,p_end_date => l_end_date
,p_name => NULL
,p_description => 'BIS_DELEGATION API -> EXTEND'
,x_success => l_success);
dbg('Record Updated');
fnd_grants_pkg.update_grant
(p_api_version => 1.0
,p_grant_guid => c_rec.grant_guid
,p_start_date => l_start_date
,p_end_date => l_end_date
,p_name => NULL
,p_description => 'BIS_DELEGATION API -> EXTEND'
,x_success => l_success);
dbg('Record Updated');
ELSIF (p_update_mode = 'REVOKE'
-- 1.
AND( ( c_rec.end_date IS NULL
OR l_cp_end_date < c_rec.end_date
)
OR l_cp_start_date > c_rec.START_DATE
)
)
THEN
-- 2.
IF c_rec.START_DATE <= TRUNC(SYSDATE) THEN
-- If the grant has started then update the end date
-- Note only changing the end date
dbg(' revoking grant....');
fnd_grants_pkg.update_grant
(p_api_version => 1.0
,p_grant_guid => c_rec.grant_guid
,p_start_date => l_start_date
,p_end_date => l_end_date
,p_name => NULL
,p_description => 'BIS_DELEGATION API -> REVOKE'
,x_success => l_success);
dbg('Record Updated');
dbg('Record Deleted');
dbg(' Record found but does not required update or delete');
dbg(' Update success result :'||x_success);
END update_delegation_grants;
update_delegation_grants
( p_delegate_type =>p_delegate_type--IN VARCHAR2
,p_grantee_key =>p_grantee_key--IN VARCHAR2 DEFAULT NULL
,p_instance_pk1_value =>p_instance_pk1_value--IN VARCHAR2
--,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
--,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
--,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
--,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
,p_start_date =>l_start_date--IN DATE DEFAULT SYSDATE
,p_end_date =>l_end_date--IN DATE DEFAULT SYSDATE
,p_menu_id =>l_menu_id--IN NUMBER DEFAULT NULL
,x_success =>x_success--OUT NOCOPY VARCHAR /* Boolean */
,x_errorcode =>x_errorcode--OUT NOCOPY NUMBER
,p_update_mode =>'EXTEND'--IN VARCHAR2 DEFAULT 'EXTEND'
);
update_delegation_grants
( p_delegate_type =>p_delegate_type--IN VARCHAR2
,p_grantee_key =>p_grantee_key--IN VARCHAR2 DEFAULT NULL
,p_instance_pk1_value =>p_instance_pk1_value--IN VARCHAR2
--,p_instance_pk2_value IN VARCHAR2 DEFAULT NULL
--,p_instance_pk3_value IN VARCHAR2 DEFAULT NULL
--,p_instance_pk4_value IN VARCHAR2 DEFAULT NULL
--,p_instance_pk5_value IN VARCHAR2 DEFAULT NULL
,p_start_date =>l_start_date--IN DATE DEFAULT SYSDATE
,p_end_date =>l_end_date--IN DATE DEFAULT SYSDATE
,p_menu_id =>l_menu_id--IN NUMBER DEFAULT NULL
,x_success =>x_success--OUT NOCOPY VARCHAR /* Boolean */
,x_errorcode =>x_errorcode--OUT NOCOPY NUMBER
,p_update_mode =>'REVOKE'--IN VARCHAR2 DEFAULT 'EXTEND'
);