[Home] [Help]
PACKAGE BODY: APPS.PQH_ROLE_INFO_TYPES_PKG
Source
1 PACKAGE BODY PQH_ROLE_INFO_TYPES_PKG as
2 /* $Header: pqhrlsit.pkb 120.3 2005/10/12 20:18:37 srajakum noship $ */
3 --------------------------------------------------------------------------------
4 g_dummy number(1); -- Dummy for cursor returns which are not needed
5 g_business_group_id number(15); -- For validating translation;
6 g_legislation_code varchar2(150); -- For validating translation;
7 --------------------------------------------------------------------------------
8 --
9 PROCEDURE UNIQUENESS_CHECK(P_INFORMATION_TYPE VARCHAR2,
10 P_ACTIVE_INACTIVE_FLAG VARCHAR2,
11 P_LEGISLATION_CODE VARCHAR2,
12 P_ROWID VARCHAR2,
13 P_DESCRIPTION VARCHAR2)
14 IS
15 L_DUMMY1 number;
16 CURSOR C1 IS
17 select 1
18 from PQH_ROLE_INFO_TYPES t
19 where upper(t.description) = upper(P_DESCRIPTION)
20 and nvl(t.legislation_code, nvl(P_LEGISLATION_CODE, 'XXX') )
21 = nvl(P_LEGISLATION_CODE, 'XXX')
22 and (P_ROWID is null
23 or P_ROWID <> t.rowid);
24 BEGIN
25 OPEN C1;
26 FETCH C1 INTO L_DUMMY1;
27 IF C1%NOTFOUND THEN
28 CLOSE C1;
29 ELSE
30 CLOSE C1;
31 hr_utility.set_message('801','HR_7777_DEF_DESCR_EXISTS');
32 hr_utility.raise_error;
33 END IF;
34 end UNIQUENESS_CHECK;
35 --
36 procedure INSERT_ROW (
37 X_ROWID in out nocopy VARCHAR2,
38 X_INFORMATION_TYPE in VARCHAR2,
39 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
40 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
41 X_LEGISLATION_CODE in VARCHAR2,
42 X_REQUEST_ID in NUMBER,
43 X_OBJECT_VERSION_NUMBER in NUMBER,
44 X_DESCRIPTION in VARCHAR2,
45 X_CREATION_DATE in DATE,
46 X_CREATED_BY in NUMBER,
47 X_LAST_UPDATE_DATE in DATE,
48 X_LAST_UPDATED_BY in NUMBER,
49 X_LAST_UPDATE_LOGIN in NUMBER
50 ) is
51 cursor C is select ROWID from PQH_ROLE_INFO_TYPES
52 where INFORMATION_TYPE = X_INFORMATION_TYPE
53 ;
54 begin
55 insert into PQH_ROLE_INFO_TYPES (
56 INFORMATION_TYPE,
57 ACTIVE_INACTIVE_FLAG,
58 MULTIPLE_OCCURENCES_FLAG,
59 LEGISLATION_CODE,
60 REQUEST_ID,
61 OBJECT_VERSION_NUMBER,
62 DESCRIPTION,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN
68 ) values (
69 X_INFORMATION_TYPE,
70 X_ACTIVE_INACTIVE_FLAG,
71 X_MULTIPLE_OCCURENCES_FLAG,
72 X_LEGISLATION_CODE,
73 X_REQUEST_ID,
74 X_OBJECT_VERSION_NUMBER,
75 X_DESCRIPTION,
76 X_CREATION_DATE,
77 X_CREATED_BY,
78 X_LAST_UPDATE_DATE,
79 X_LAST_UPDATED_BY,
80 X_LAST_UPDATE_LOGIN
81 );
82
83 open c;
84 fetch c into X_ROWID;
85 if (c%notfound) then
86 close c;
87 raise no_data_found;
88 end if;
89 close c;
90
91 end INSERT_ROW;
92
93 procedure LOCK_ROW (
94 X_INFORMATION_TYPE in VARCHAR2,
95 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
96 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
97 X_LEGISLATION_CODE in VARCHAR2,
98 X_REQUEST_ID in NUMBER,
99 X_OBJECT_VERSION_NUMBER in NUMBER,
100 X_DESCRIPTION in VARCHAR2
101 ) is
102 cursor c is select
103 ACTIVE_INACTIVE_FLAG,
104 MULTIPLE_OCCURENCES_FLAG,
105 LEGISLATION_CODE,
106 REQUEST_ID,
107 OBJECT_VERSION_NUMBER
108 from PQH_ROLE_INFO_TYPES
109 where INFORMATION_TYPE = X_INFORMATION_TYPE
110 for update of INFORMATION_TYPE nowait;
111 recinfo c%rowtype;
112
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 end if;
121 close c;
122 if ( (recinfo.ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG)
123 AND (recinfo.MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG)
124 AND ((recinfo.LEGISLATION_CODE = X_LEGISLATION_CODE)
125 OR ((recinfo.LEGISLATION_CODE is null) AND (X_LEGISLATION_CODE is null)))
126 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
127 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
128 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
129 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
130 ) then
131 null;
132 else
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136
137 return;
138 end LOCK_ROW;
139
140 procedure UPDATE_ROW (
141 X_INFORMATION_TYPE in VARCHAR2,
142 X_ACTIVE_INACTIVE_FLAG in VARCHAR2,
143 X_MULTIPLE_OCCURENCES_FLAG in VARCHAR2,
144 X_LEGISLATION_CODE in VARCHAR2,
145 X_REQUEST_ID in NUMBER,
146 X_OBJECT_VERSION_NUMBER in NUMBER,
147 X_DESCRIPTION in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153 update PQH_ROLE_INFO_TYPES set
154 ACTIVE_INACTIVE_FLAG = X_ACTIVE_INACTIVE_FLAG,
155 MULTIPLE_OCCURENCES_FLAG = X_MULTIPLE_OCCURENCES_FLAG,
156 LEGISLATION_CODE = X_LEGISLATION_CODE,
157 REQUEST_ID = X_REQUEST_ID,
158 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
159 DESCRIPTION = X_DESCRIPTION,
160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
163 where INFORMATION_TYPE = X_INFORMATION_TYPE
164 and nvl(last_updated_by,-1) in (X_LAST_UPDATED_BY,-1,1);
165
166 if (sql%notfound) then
167 raise no_data_found;
168 end if;
169
170 end UPDATE_ROW;
171
172 procedure DELETE_ROW (
173 X_INFORMATION_TYPE in VARCHAR2
174 ) is
175 begin
176
177 delete from PQH_ROLE_INFO_TYPES
178 where INFORMATION_TYPE = X_INFORMATION_TYPE;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183 end DELETE_ROW;
184
185 procedure LOAD_SEED_ROW
186 (X_UPLOAD_MODE in varchar2
187 ,X_INFORMATION_TYPE in varchar2
188 ,X_ACTIVE_INACTIVE_FLAG in varchar2
189 ,X_MULTIPLE_OCCURENCES_FLAG in varchar2
190 ,X_DESCRIPTION in varchar2
191 ,X_LEGISLATION_CODE in varchar2
192 ,X_OBJECT_VERSION_NUMBER in number
193 ,X_OWNER in varchar2
194 ,X_LAST_UPDATE_DATE in varchar2
195 ) is
196 --
197 l_data_migrator_mode varchar2(10);
198 --
199 Begin
200 l_data_migrator_mode := hr_general.g_data_migrator_mode ;
201 hr_general.g_data_migrator_mode := 'Y';
202 --
203 if (x_upload_mode = 'NLS') then
204 pqh_role_info_types_pkg.translate_row
205 (x_information_type => X_INFORMATION_TYPE,
206 x_description => X_DESCRIPTION ,
207 x_owner => X_OWNER);
208 else
209 pqh_role_info_types_pkg.LOAD_ROW (
210 x_information_type => X_INFORMATION_TYPE,
211 x_active_inactive_flag => X_ACTIVE_INACTIVE_FLAG,
212 x_multiple_occurences_flag => X_MULTIPLE_OCCURENCES_FLAG,
213 x_legislation_code => X_LEGISLATION_CODE,
214 x_object_version_number => X_OBJECT_VERSION_NUMBER,
215 x_owner => X_OWNER,
216 x_description => X_DESCRIPTION,
217 x_last_update_date => X_LAST_UPDATE_DATE );
218 end if;
219 hr_general.g_data_migrator_mode := l_data_migrator_mode;
220 End;
221 --
222 procedure LOAD_ROW
223 (X_INFORMATION_TYPE in varchar2
224 ,X_ACTIVE_INACTIVE_FLAG in varchar2
225 ,X_MULTIPLE_OCCURENCES_FLAG in varchar2
226 ,X_DESCRIPTION in varchar2
227 ,X_LEGISLATION_CODE in varchar2
228 ,X_OBJECT_VERSION_NUMBER in number
229 ,X_OWNER in varchar2
230 ,X_LAST_UPDATE_DATE in varchar2
231 )
232 is
233 l_proc VARCHAR2(61) := 'PQH_ROLE_INFO_TYPES_PKG.LOAD_ROW';
234 l_rowid rowid;
235 l_request_id PQH_ROLE_INFO_TYPES.request_id%TYPE;
236 l_progam_application_id PQH_ROLE_INFO_TYPES.program_application_id%TYPE;
237 l_program_id PQH_ROLE_INFO_TYPES.program_id%TYPE;
238 l_program_update_date PQH_ROLE_INFO_TYPES.program_update_date%TYPE;
239 l_created_by PQH_ROLE_INFO_TYPES.created_by%TYPE := 0;
240 l_creation_date PQH_ROLE_INFO_TYPES.creation_date%TYPE := SYSDATE;
241 l_last_update_date PQH_ROLE_INFO_TYPES.last_update_date%TYPE := SYSDATE;
242 l_last_updated_by PQH_ROLE_INFO_TYPES.last_updated_by%TYPE := 0;
243 l_last_update_login PQH_ROLE_INFO_TYPES.last_update_login%TYPE := 0;
244 l_dummy varchar2(10);
245 --
246 Cursor csr_info_typ_exists is
247 Select 'x' from PQH_ROLE_INFO_TYPES
248 Where INFORMATION_TYPE = X_INFORMATION_TYPE;
249 --
250 begin
251 -- Translate developer keys to internal parameters
252 l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
253 l_created_by := fnd_load_util.owner_id(X_OWNER);
254 l_creation_date := nvl(to_date(x_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
255 l_last_update_date := nvl(to_date(x_last_update_date,'YYYY/MM/DD'),trunc(sysdate));
256
257 Open csr_info_typ_exists;
258 Fetch csr_info_typ_exists into l_dummy;
259 If csr_info_typ_exists%found then
260 UPDATE_ROW
261 (X_INFORMATION_TYPE => X_INFORMATION_TYPE
262 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
263 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
264 ,X_DESCRIPTION => X_DESCRIPTION
265 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
266 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
267 ,X_REQUEST_ID => l_request_id
268 ,X_LAST_UPDATE_DATE => l_last_update_date
269 ,X_LAST_UPDATED_BY => l_last_updated_by
270 ,X_LAST_UPDATE_LOGIN => l_last_update_login
271 );
272 Else
273 INSERT_ROW
274 (X_ROWID => l_rowid
275 ,X_INFORMATION_TYPE => X_INFORMATION_TYPE
276 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
277 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
278 ,X_DESCRIPTION => X_DESCRIPTION
279 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
280 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
281 ,X_REQUEST_ID => l_request_id
282 ,X_CREATED_BY => l_created_by
283 ,X_CREATION_DATE => l_creation_date
284 ,X_LAST_UPDATE_DATE => l_last_update_date
285 ,X_LAST_UPDATED_BY => l_last_updated_by
286 ,X_LAST_UPDATE_LOGIN => l_last_update_login
287 );
288 End if;
289 Close csr_info_typ_exists;
290 --
291 /**
292 if X_OWNER = 'SEED' then
293 l_created_by := 1;
294 l_last_updated_by := -1;
295 else
296 l_created_by := 0;
297 l_last_updated_by := 0;
298 end if;
299 -- Update or insert row as appropriate
300 begin
301
302 UPDATE_ROW
303 (X_INFORMATION_TYPE => X_INFORMATION_TYPE
304 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
305 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
306 ,X_DESCRIPTION => X_DESCRIPTION
307 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
308 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
309 ,X_REQUEST_ID => l_request_id
310 ,X_LAST_UPDATE_DATE => l_last_update_date
311 ,X_LAST_UPDATED_BY => l_last_updated_by
312 ,X_LAST_UPDATE_LOGIN => l_last_update_login
313 );
314 exception
315 when no_data_found then
316 INSERT_ROW
317 (X_ROWID => l_rowid
318 ,X_INFORMATION_TYPE => X_INFORMATION_TYPE
319 ,X_ACTIVE_INACTIVE_FLAG => X_ACTIVE_INACTIVE_FLAG
320 ,X_MULTIPLE_OCCURENCES_FLAG => X_MULTIPLE_OCCURENCES_FLAG
321 ,X_DESCRIPTION => X_DESCRIPTION
322 ,X_LEGISLATION_CODE => X_LEGISLATION_CODE
323 ,X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
324 ,X_REQUEST_ID => l_request_id
325 ,X_CREATED_BY => l_created_by
326 ,X_CREATION_DATE => l_creation_date
327 ,X_LAST_UPDATE_DATE => l_last_update_date
328 ,X_LAST_UPDATED_BY => l_last_updated_by
329 ,X_LAST_UPDATE_LOGIN => l_last_update_login
330 );
331 end;
332 **/
333 --
334 end LOAD_ROW;
335
336 procedure TRANSLATE_ROW
337 (X_INFORMATION_TYPE in varchar2
338 ,X_DESCRIPTION in varchar2
339 ,X_OWNER in varchar2
340 )
341 is
342 l_last_updated_by PQH_ROLE_INFO_TYPES.last_updated_by%TYPE := 0;
343 begin
344 l_last_updated_by := fnd_load_util.owner_id(X_OWNER);
345 --
346 UPDATE PQH_ROLE_INFO_TYPES
347 SET description = X_DESCRIPTION
348 ,last_update_date = SYSDATE
349 ,last_updated_by = l_last_updated_by
350 ,last_update_login = 0
351 WHERE USERENV('LANG') = (select language_code from fnd_languages
352 where installed_flag = 'B')
353 AND information_type = X_INFORMATION_TYPE;
354 end TRANSLATE_ROW;
355
356
357 END PQH_ROLE_INFO_TYPES_PKG;