[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_ROLE_RESP_PKG
Source
1 package body JTF_UM_ROLE_RESP_PKG as
2 /* $Header: JTFUMRRB.pls 120.3 2005/11/28 08:51:15 vimohan ship $ */
3 procedure INSERT_USERTYPE_ROLE_ROW (
4 x_usertype_id IN NUMBER,
5 x_principal_name IN VARCHAR2,
6 x_effective_start_date IN DATE,
7 x_effective_end_date IN DATE,
8 x_creation_date IN DATE,
9 x_created_by IN NUMBER,
10 x_last_update_date IN DATE,
11 x_last_updated_by IN NUMBER,
12 x_last_update_login IN NUMBER
13 ) is
14 begin
15 insert into jtf_um_usertype_role (
16 usertype_id ,
17 principal_name ,
18 effective_start_date ,
19 effective_end_date ,
20 creation_date ,
21 created_by ,
22 last_update_date ,
23 last_updated_by ,
24 last_update_login
25 ) values (
26 x_usertype_id ,
27 x_principal_name ,
28 x_effective_start_date ,
29 x_effective_end_date ,
30 x_creation_date ,
31 x_created_by ,
32 x_last_update_date ,
33 x_last_updated_by ,
34 x_last_update_login
35 );
36 end INSERT_USERTYPE_ROLE_ROW;
37
38 procedure UPDATE_USERTYPE_ROLE_ROW (
39 x_usertype_id IN NUMBER,
40 x_principal_name IN VARCHAR2,
41 x_effective_end_date IN DATE,
42 x_last_update_date IN DATE,
43 x_last_updated_by IN NUMBER,
44 x_last_update_login IN NUMBER
45 ) is
46 begin
47 update jtf_um_usertype_role set
48 effective_end_date = x_effective_end_date ,
49 last_update_date = x_last_update_date ,
50 last_updated_by = x_last_updated_by ,
51 last_update_login = x_last_update_login
52 where usertype_id = x_usertype_id AND
53 principal_name = x_principal_name;
54
55 if (sql%notfound) then
56 raise no_data_found;
57 end if;
58 end UPDATE_USERTYPE_ROLE_ROW;
59
60 procedure LOAD_usertype_role_ROW (
61 x_usertype_id IN NUMBER,
62 x_principal_name IN VARCHAR2,
63 x_effective_start_date IN DATE,
64 x_effective_end_date IN DATE,
65 x_owner IN VARCHAR2,
66 x_last_update_date in varchar2 default NULL,
67 X_CUSTOM_MODE in varchar2 default NULL
68 ) is
69 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
70 f_luby number; -- entity owner in file
71 f_ludate date; -- entity update date in file
72 db_luby number; -- entity owner in db
73 db_ludate date; -- entity update date in db
74
75 begin
76 -- if (x_owner = 'SEED') then
77 -- l_user_id := 1;
78 -- end if;
79
80
81 -- Translate owner to file_last_updated_by
82 f_luby := fnd_load_util.owner_id(x_owner);
83
84 -- Translate char last_update_date to date
85 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
86
87 -- This select stmnt also checks if
88 -- there is a row for this app_id and this app_short_name
89 -- Exception is thrown otherwise.
90 select LAST_UPDATED_BY, LAST_UPDATE_DATE
91 into db_luby, db_ludate
92 FROM jtf_um_usertype_role
93 where usertype_id = x_usertype_id AND
94 principal_name = x_principal_name;
95
96 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
97 db_ludate, X_CUSTOM_MODE)) then
98
99 -- Update row if present
100 JTF_UM_ROLE_RESP_PKG.UPDATE_USERTYPE_ROLE_ROW (
101 x_usertype_id => x_usertype_id,
102 x_principal_name => x_principal_name,
103 x_effective_end_date => x_effective_end_date,
104 x_last_update_date => f_ludate,
105 x_last_updated_by => f_luby,
106 x_last_update_login => l_user_id
107 );
108
109 end if;
110
111 exception
112 when NO_DATA_FOUND then
113
114 -- Insert a new row
115 JTF_UM_ROLE_RESP_PKG.INSERT_USERTYPE_ROLE_ROW (
116 x_usertype_id => x_usertype_id,
117 x_principal_name => x_principal_name,
118 x_effective_start_date => x_effective_start_date,
119 x_effective_end_date => x_effective_end_date,
120 x_creation_date => f_ludate,
121 x_created_by => f_luby,
122 x_last_update_date => f_ludate,
123 x_last_updated_by => f_luby,
124 x_last_update_login => l_user_id
125 );
126 end LOAD_USERTYPE_ROLE_ROW;
127
128 procedure INSERT_USERTYPE_RESP_ROW (
129 x_usertype_id IN NUMBER,
130 x_responsibility_key IN VARCHAR2,
131 x_is_default_flag IN VARCHAR2,
132 x_effective_start_date IN DATE,
133 x_effective_end_date IN DATE,
134 x_creation_date IN DATE,
135 x_created_by IN NUMBER,
136 x_last_update_date IN DATE,
137 x_last_updated_by IN NUMBER,
138 x_last_update_login IN NUMBER,
139 x_application_id IN NUMBER
140 ) is
141 begin
142 insert into jtf_um_usertype_resp (
143 usertype_id ,
144 responsibility_key ,
145 is_default_flag ,
146 effective_start_date ,
147 effective_end_date ,
148 creation_date ,
149 created_by ,
150 last_update_date ,
151 last_updated_by ,
152 last_update_login ,
153 application_id
154 ) values (
155 x_usertype_id ,
156 x_responsibility_key ,
157 x_is_default_flag ,
158 x_effective_start_date ,
159 x_effective_end_date ,
160 x_creation_date ,
161 x_created_by ,
162 x_last_update_date ,
163 x_last_updated_by ,
164 x_last_update_login ,
165 x_application_id
166 );
167 end INSERT_USERTYPE_RESP_ROW;
168
169 procedure UPDATE_USERTYPE_RESP_ROW (
170 x_usertype_id IN NUMBER,
171 x_responsibility_key IN VARCHAR2,
172 x_is_default_flag IN VARCHAR2,
173 x_effective_end_date IN DATE,
174 x_last_update_date IN DATE,
175 x_last_updated_by IN NUMBER,
176 x_last_update_login IN NUMBER,
177 x_application_id IN NUMBER
178 ) is
179 begin
180 update jtf_um_usertype_resp set
181 is_default_flag = x_is_default_flag ,
182 effective_end_date = x_effective_end_date ,
183 last_update_date = x_last_update_date ,
184 last_updated_by = x_last_updated_by ,
185 last_update_login = x_last_update_login ,
186 application_id = x_application_id
187 where usertype_id = x_usertype_id AND
188 responsibility_key = x_responsibility_key ;
189
190 if (sql%notfound) then
191 raise no_data_found;
192 end if;
193 end UPDATE_usertype_resp_ROW;
194
195 procedure LOAD_USERTYPE_RESP_ROW (
196 x_usertype_id IN NUMBER,
197 x_responsibility_key IN VARCHAR2,
198 x_is_default_flag IN VARCHAR2,
199 x_effective_start_date IN DATE,
200 x_effective_end_date IN DATE,
201 x_owner IN VARCHAR2,
202 x_application_id IN NUMBER,
203 x_last_update_date in varchar2 default NULL,
204 x_custom_mode in varchar2 default NULL
205 ) is
206 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
207 f_luby number; -- entity owner in file
208 f_ludate date; -- entity update date in file
209 db_luby number; -- entity owner in db
210 db_ludate date; -- entity update date in db
211
212 begin
213 -- if (x_owner = 'SEED') then
214 -- l_user_id := 1;
215 -- end if;
216 -- Translate owner to file_last_updated_by
217 f_luby := fnd_load_util.owner_id(x_owner);
218
219 -- Translate char last_update_date to date
220 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
221
222 -- This select stmnt also checks if
223 -- there is a row for this app_id and this app_short_name
224 -- Exception is thrown otherwise.
225 select LAST_UPDATED_BY, LAST_UPDATE_DATE
226 into db_luby, db_ludate
227 FROM jtf_um_usertype_resp
228 where usertype_id = x_usertype_id AND
229 responsibility_key = x_responsibility_key ;
230
231 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
232 db_ludate, X_CUSTOM_MODE)) then
233
234
235 -- Update row if present
236 JTF_UM_ROLE_RESP_PKG.UPDATE_USERTYPE_RESP_ROW (
237 x_usertype_id => x_usertype_id,
238 x_responsibility_key => x_responsibility_key,
239 x_is_default_flag => x_is_default_flag,
240 x_effective_end_date => x_effective_end_date,
241 x_last_update_date => f_ludate,
242 x_last_updated_by => f_luby,
243 x_last_update_login => l_user_id,
244 x_application_id => x_application_id
245 );
246
247 end if;
248 exception
249 when NO_DATA_FOUND then
250
251 -- Insert a new row
252 JTF_UM_ROLE_RESP_PKG.INSERT_USERTYPE_RESP_ROW (
253 x_usertype_id => x_usertype_id,
254 x_responsibility_key => x_responsibility_key,
255 x_is_default_flag => x_is_default_flag,
256 x_effective_start_date => x_effective_start_date,
257 x_effective_end_date => x_effective_end_date,
258 x_creation_date => f_ludate,
259 x_created_by => f_luby,
260 x_last_update_date => f_ludate,
261 x_last_updated_by => f_luby,
262 x_last_update_login => l_user_id,
263 x_application_id => x_application_id
264 );
265 end LOAD_USERTYPE_RESP_ROW;
266
267 procedure INSERT_SUBSCRIPTION_ROLE_ROW (
268 x_subscription_id IN NUMBER,
269 x_principal_name IN VARCHAR2,
270 x_effective_start_date IN DATE,
271 x_effective_end_date IN DATE,
272 x_creation_date IN DATE,
273 x_created_by IN NUMBER,
274 x_last_update_date IN DATE,
275 x_last_updated_by IN NUMBER,
276 x_last_update_login IN NUMBER
277 ) is
278 begin
279 insert into jtf_um_subscription_role (
280 subscription_id ,
281 principal_name ,
282 effective_start_date ,
283 effective_end_date ,
284 creation_date ,
285 created_by ,
286 last_update_date ,
287 last_updated_by ,
288 last_update_login
289 ) values (
290 x_subscription_id ,
291 x_principal_name ,
292 x_effective_start_date ,
293 x_effective_end_date ,
294 x_creation_date ,
295 x_created_by ,
296 x_last_update_date ,
297 x_last_updated_by ,
298 x_last_update_login
299 );
300 end INSERT_SUBSCRIPTION_ROLE_ROW;
301
302 procedure UPDATE_SUBSCRIPTION_ROLE_ROW (
303 x_subscription_id IN NUMBER,
304 x_principal_name IN VARCHAR2,
305 x_effective_end_date IN DATE,
306 x_last_update_date IN DATE,
307 x_last_updated_by IN NUMBER,
308 x_last_update_login IN NUMBER
309 ) is
310 begin
311 update jtf_um_subscription_role set
312 effective_end_date = x_effective_end_date ,
313 last_update_date = x_last_update_date ,
314 last_updated_by = x_last_updated_by ,
315 last_update_login = x_last_update_login
316 where subscription_id = x_subscription_id AND
317 principal_name = x_principal_name ;
318
319 if (sql%notfound) then
320 raise no_data_found;
321 end if;
322 end UPDATE_SUBSCRIPTION_ROLE_ROW;
323
324 procedure LOAD_SUBSCRIPTION_ROLE_ROW (
325 x_subscription_id IN NUMBER,
326 x_principal_name IN VARCHAR2,
327 x_effective_start_date IN DATE,
328 x_effective_end_date IN DATE,
329 x_owner IN VARCHAR2,
330 x_last_update_date in varchar2 default NULL,
331 x_custom_mode in varchar2 default NULL
332 ) is
333 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
334 f_luby number; -- entity owner in file
335 f_ludate date; -- entity update date in file
336 db_luby number; -- entity owner in db
337 db_ludate date; -- entity update date in db
338
339
340 begin
341 -- if (x_owner = 'SEED') then
342 -- l_user_id := 1;
343 --end if;
344
345
346 -- Translate owner to file_last_updated_by
347 f_luby := fnd_load_util.owner_id(x_owner);
348
349 -- Translate char last_update_date to date
350 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
351
352 -- This select stmnt also checks if
353 -- there is a row for this app_id and this app_short_name
354 -- Exception is thrown otherwise.
355 select LAST_UPDATED_BY, LAST_UPDATE_DATE
356 into db_luby, db_ludate
357 FROM jtf_um_subscription_role
358 where subscription_id = x_subscription_id AND
359 principal_name = x_principal_name ;
360
361
362 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
363 db_ludate, X_CUSTOM_MODE)) then
364
365
366 -- Update row if present
367 JTF_UM_ROLE_RESP_PKG.UPDATE_SUBSCRIPTION_ROLE_ROW (
368 x_subscription_id => x_subscription_id,
369 x_principal_name => x_principal_name,
370 x_effective_end_date => x_effective_end_date,
371 x_last_update_date => f_ludate,
372 x_last_updated_by => f_luby,
373 x_last_update_login => l_user_id
374 );
375
376
377 end if;
378 exception
379 when NO_DATA_FOUND then
380
381 -- Insert a new row
382 JTF_UM_ROLE_RESP_PKG.INSERT_SUBSCRIPTION_ROLE_ROW (
383 x_subscription_id => x_subscription_id,
384 x_principal_name => x_principal_name,
385 x_effective_start_date => x_effective_start_date,
386 x_effective_end_date => x_effective_end_date,
387 x_creation_date => f_ludate,
388 x_created_by => f_luby,
389 x_last_update_date => f_ludate,
390 x_last_updated_by => f_luby,
391 x_last_update_login => l_user_id
392 );
393 end LOAD_SUBSCRIPTION_ROLE_ROW;
394
395 procedure INSERT_SUBSCRIPTION_RESP_ROW (
396 x_subscription_id IN NUMBER,
397 x_responsibility_key IN VARCHAR2,
398 x_effective_start_date IN DATE,
399 x_effective_end_date IN DATE,
400 x_creation_date IN DATE,
401 x_created_by IN NUMBER,
402 x_last_update_date IN DATE,
403 x_last_updated_by IN NUMBER,
404 x_last_update_login IN NUMBER,
405 x_application_id IN NUMBER
406 ) is
407 begin
408 insert into jtf_um_subscription_resp (
409 subscription_id ,
410 responsibility_key ,
411 effective_start_date ,
412 effective_end_date ,
413 creation_date ,
414 created_by ,
415 last_update_date ,
416 last_updated_by ,
417 last_update_login ,
418 application_id
419 ) values (
420 x_subscription_id ,
421 x_responsibility_key ,
422 x_effective_start_date ,
423 x_effective_end_date ,
424 x_creation_date ,
425 x_created_by ,
426 x_last_update_date ,
427 x_last_updated_by ,
428 x_last_update_login ,
429 x_application_id
430 );
431 end INSERT_SUBSCRIPTION_RESP_ROW;
432
433 procedure UPDATE_SUBSCRIPTION_RESP_ROW (
434 x_subscription_id IN NUMBER,
435 x_responsibility_key IN VARCHAR2,
436 x_effective_end_date IN DATE,
437 x_last_update_date IN DATE,
438 x_last_updated_by IN NUMBER,
439 x_last_update_login IN NUMBER,
440 x_application_id IN NUMBER
441 ) is
442 begin
443 update jtf_um_subscription_resp set
444 effective_end_date = x_effective_end_date ,
445 last_update_date = x_last_update_date ,
446 last_updated_by = x_last_updated_by ,
447 last_update_login = x_last_update_login ,
448 application_id = x_application_id
449 where subscription_id = x_subscription_id AND
450 responsibility_key = x_responsibility_key ;
451
452 if (sql%notfound) then
453 raise no_data_found;
454 end if;
455 end UPDATE_SUBSCRIPTION_RESP_ROW;
456
457 procedure LOAD_SUBSCRIPTION_RESP_ROW (
458 x_subscription_id IN NUMBER,
459 x_responsibility_key IN VARCHAR2,
460 x_effective_start_date IN DATE,
461 x_effective_end_date IN DATE,
462 x_owner IN VARCHAR2,
463 x_application_id IN NUMBER,
464 x_last_update_date in varchar2 default NULL,
465 X_CUSTOM_MODE in varchar2 default NULL
466 ) is
467 l_user_id NUMBER := fnd_load_util.owner_id(x_owner);
468
472 db_ludate date; -- entity update date in db
469 f_luby number; -- entity owner in file
470 f_ludate date; -- entity update date in file
471 db_luby number; -- entity owner in db
473
474 begin
475 -- if (x_owner = 'SEED') then
476 -- l_user_id := 1;
477 -- end if;
478
479 -- Translate owner to file_last_updated_by
480 f_luby := fnd_load_util.owner_id(x_owner);
481
482 -- Translate char last_update_date to date
483 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
484
485 -- This select stmnt also checks if
486 -- there is a row for this app_id and this app_short_name
487 -- Exception is thrown otherwise.
488 select LAST_UPDATED_BY, LAST_UPDATE_DATE
489 into db_luby, db_ludate
490 FROM jtf_um_subscription_resp
491 where subscription_id = x_subscription_id AND
492 responsibility_key = x_responsibility_key ;
493
494
495 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
496 db_ludate, X_CUSTOM_MODE)) then
497
498
499 -- Update row if present
500 JTF_UM_ROLE_RESP_PKG.UPDATE_SUBSCRIPTION_RESP_ROW (
501 x_subscription_id => x_subscription_id,
502 x_responsibility_key => x_responsibility_key,
503 x_effective_end_date => x_effective_end_date,
504 x_last_update_date => f_ludate,
505 x_last_updated_by => f_luby,
506 x_last_update_login => l_user_id,
507 x_application_id => x_application_id
508 );
509
510 end if;
511
512 exception
513 when NO_DATA_FOUND then
514
515 -- Insert a new row
516 JTF_UM_ROLE_RESP_PKG.INSERT_SUBSCRIPTION_RESP_ROW (
517 x_subscription_id => x_subscription_id,
518 x_responsibility_key => x_responsibility_key,
519 x_effective_start_date => x_effective_start_date,
520 x_effective_end_date => x_effective_end_date,
521 x_creation_date => f_ludate,
522 x_created_by => f_luby,
523 x_last_update_date => f_ludate,
524 x_last_updated_by => f_luby,
525 x_last_update_login => l_user_id,
526 x_application_id => x_application_id
527 );
528 end LOAD_SUBSCRIPTION_RESP_ROW;
529
530 end JTF_UM_ROLE_RESP_PKG;