[Home] [Help]
PACKAGE BODY: APPS.PV_BENFT_USER_ROLE_MAPS_PKG
Source
1 package body PV_BENFT_USER_ROLE_MAPS_PKG as
2 /* $Header: pvxtulmb.pls 120.0 2005/07/11 23:13:14 appldev noship $ */
3 procedure INSERT_ROW (
4 px_user_role_map_id IN OUT NOCOPY NUMBER,
5 px_object_version_number IN OUT NOCOPY NUMBER,
6 p_benefit_type in VARCHAR2,
7 p_user_role_code in VARCHAR2,
8 p_external_flag in VARCHAR2,
9 p_creation_date IN DATE,
10 p_created_by IN NUMBER,
11 p_last_update_date IN DATE,
12 p_last_updated_by IN NUMBER,
13 p_last_update_login IN NUMBER
14 )
15 IS
16 begin
17 INSERT INTO pv_benft_user_role_maps (
18 user_role_map_id,
19 object_version_number,
20 benefit_type,
21 user_role_code,
22 external_flag,
23 creation_date,
24 created_by,
25 last_update_date,
26 last_updated_by,
27 last_update_login
28 ) values (
29 DECODE ( px_user_role_map_id,FND_API.g_miss_num,NULL,px_user_role_map_id),
30 DECODE ( px_object_version_number,FND_API.g_miss_num,NULL,px_object_version_number),
31 DECODE ( p_benefit_type,FND_API.g_miss_char,NULL,p_benefit_type),
32 DECODE ( p_user_role_code,FND_API.g_miss_char,NULL,p_user_role_code),
33 DECODE ( p_external_flag,FND_API.g_miss_char,NULL,p_external_flag),
34 DECODE ( p_creation_date,FND_API.g_miss_date,NULL,p_creation_date),
35 DECODE ( p_created_by,FND_API.g_miss_num,NULL,p_created_by),
36 DECODE ( p_last_update_date,FND_API.g_miss_date,NULL,p_last_update_date),
37 DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by),
38 DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login));
39 end INSERT_ROW;
40
41 procedure LOCK_ROW (
42 p_user_role_map_id IN NUMBER,
43 p_object_version_number IN NUMBER,
44 p_benefit_type in VARCHAR2,
45 p_user_role_code in VARCHAR2,
46 p_external_flag in VARCHAR2
47 ) is
48 CURSOR C IS SELECT
49 object_version_number,
50 benefit_type,
51 user_role_code,
52 external_flag
53 FROM pv_benft_user_role_maps
54 WHERE user_role_map_id = p_user_role_map_id
55 FOR UPDATE OF user_role_map_id nowait;
56 recinfo c%rowtype;
57
58 begin
59 open c;
60 fetch c into recinfo;
61 if (c%notfound) then
62 close c;
63 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
64 app_exception.raise_exception;
65 end if;
66 close c;
67 if ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
68 AND ((recinfo.BENEFIT_TYPE = p_BENEFIT_TYPE)
69 OR ((recinfo.BENEFIT_TYPE is null) AND (p_BENEFIT_TYPE is null)))
70 AND ((recinfo.USER_ROLE_CODE = p_USER_ROLE_CODE)
71 OR ((recinfo.USER_ROLE_CODE is null) AND (p_USER_ROLE_CODE is null)))
72 AND ((recinfo.external_flag = p_external_flag)
73 OR ((recinfo.external_flag is null) AND (p_external_flag is null)))
74 ) then
75 null;
76 else
77 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
78 app_exception.raise_exception;
79 end if;
80
81 return;
82 end LOCK_ROW;
83
84 procedure UPDATE_ROW (
85 p_user_role_map_id IN NUMBER,
86 p_object_version_number IN NUMBER,
87 p_benefit_type IN VARCHAR2,
88 p_user_role_code IN VARCHAR2,
89 p_external_flag IN VARCHAR2,
90 p_last_update_date IN DATE,
91 p_last_updated_by IN NUMBER,
92 p_last_update_login IN NUMBER
93 ) is
94 begin
95 UPDATE pv_benft_user_role_maps SET
96 object_version_number = DECODE ( p_object_version_number,FND_API.g_miss_num,NULL,p_object_version_number+1),
97 benefit_type = DECODE ( p_benefit_type,FND_API.g_miss_char,NULL,p_benefit_type),
98 user_role_code = DECODE ( p_user_role_code,FND_API.g_miss_char,NULL,p_user_role_code),
99 external_flag = DECODE ( p_external_flag,FND_API.g_miss_char,NULL,p_external_flag),
100 last_update_date = DECODE ( p_last_update_date,FND_API.g_miss_date,NULL,p_last_update_date),
101 last_updated_by = DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by),
102 last_update_login = DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login)
103 WHERE user_role_map_id = p_user_role_map_id;
104
105 IF (SQL%NOTFOUND) THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108
109
110 end UPDATE_ROW;
111
112
113 procedure DELETE_ROW (
114 p_user_role_map_id in NUMBER
115 ) is
116 begin
117
118 DELETE FROM pv_benft_user_role_maps
119 WHERE user_role_map_id = p_user_role_map_id;
120
121 IF (SQL%NOTFOUND) THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124 end DELETE_ROW;
125
126 procedure LOAD_ROW (
127 p_upload_mode IN VARCHAR2,
128 p_user_role_map_id IN NUMBER,
129 p_object_version_number IN NUMBER,
130 p_benefit_type IN VARCHAR2,
131 p_user_role_code IN VARCHAR2,
132 p_external_flag IN VARCHAR2,
133 p_owner IN VARCHAR2
134 )
135 IS
136
137 l_user_id number := 0;
138 l_obj_verno number;
139 l_dummy_char varchar2(1);
140 l_row_id varchar2(100);
141 l_user_role_map_id number := p_user_role_map_id;
142
143 cursor c_obj_verno is
144 SELECT object_version_number
145 FROM pv_benft_user_role_maps
146 WHERE user_role_map_id = p_user_role_map_id;
147
148 cursor c_chk_status_exists is
149 SELECT 'x'
150 FROM pv_benft_user_role_maps
151 WHERE user_role_map_id = p_user_role_map_id;
152
153 BEGIN
154
155 IF p_OWNER = 'SEED' then
156 l_user_id := 1;
157 ELSE
158 l_user_id := 0;
159 END IF;
160 IF p_upload_mode = 'NLS' THEN
161 null;
162 ELSE
163 OPEN c_chk_status_exists;
164 FETCH c_chk_status_exists INTO l_dummy_char;
165 IF c_chk_status_exists%NOTFOUND THEN
166 CLOSE c_chk_status_exists;
167 l_obj_verno := 1;
168
169 INSERT_ROW(
170 px_user_role_map_id => l_user_role_map_id,
171 px_object_version_number => l_obj_verno,
172 p_benefit_type => p_benefit_type,
173 p_user_role_code => p_user_role_code,
174 p_external_flag => p_external_flag,
175 p_creation_date => SYSDATE,
176 p_created_by => l_user_id,
177 p_last_update_date => SYSDATE,
178 p_last_updated_by => l_user_id,
179 p_last_update_login => 0);
180
181
182 ELSE
183 close c_chk_status_exists;
184 open c_obj_verno;
185 fetch c_obj_verno into l_obj_verno;
186 close c_obj_verno;
187
188 UPDATE_ROW (
189 p_user_role_map_id => p_user_role_map_id,
190 p_object_version_number => l_obj_verno,
191 p_benefit_type => p_benefit_type,
192 p_user_role_code => p_user_role_code,
193 p_external_flag => p_external_flag,
194 p_last_update_date => SYSDATE,
195 p_last_updated_by => l_user_id,
196 p_last_update_login => 0);
197
198
199 END IF;
200 END IF;
201
202 END LOAD_ROW;
203 end PV_BENFT_USER_ROLE_MAPS_PKG;