[Home] [Help]
PACKAGE BODY: APPS.JUMF_LCT_PKG
Source
1 package body jumf_lct_pkg as
2 /* $Header: JTFUMLSB.pls 120.1 2006/01/16 00:54:35 vimohan noship $*/
3 procedure LOAD_SEED_TEMPLATES(
4 x_upload_mode in varchar2,
5 x_template_name in varchar2,
6 x_description in varchar2,
7 x_owner in varchar2,
8 x_template_key in varchar2,
9 x_page_name in varchar2,
10 x_template_handler in varchar2,
11 x_template_type_code in varchar2,
12 x_enabled_flag in varchar2,
13 x_application_id in varchar2,
14 x_effective_start_date in varchar2,
15 x_effective_end_date in varchar2,
16 x_last_update_date in varchar2 ,
17 x_custom_mode in varchar2
18 )
19 is
20
21 v_db_owner_id number;
22
23 begin
24 if ( x_upload_mode = 'NLS' ) then
25 JTF_UM_TEMPLATES_PKG.TRANSLATE_ROW(
26 X_TEMPLATE_ID => JTF_UMUTIL.template_lookup(x_template_key, to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS')),
27 X_TEMPLATE_NAME => x_template_name,
28 X_DESCRIPTION => x_description,
29 X_OWNER => x_owner,
30 X_LAST_UPDATE_DATE => x_last_update_date,
31 X_CUSTOM_MODE => x_custom_mode
32 );
33 else
34 -- select LAST_UPDATED_BY
35 -- into v_db_owner_id
36 -- from JTF_UM_TEMPLATES_B
37 -- where TEMPLATE_ID = JTF_UMUTIL.template_lookup(x_template_key, to_date(x_effective_start_date,'YYYY/MM/DD HH24: MI:SS'));
38
39 -- if (v_db_owner_id = 1) then
40 JTF_UM_TEMPLATES_PKG.LOAD_ROW(
41 X_TEMPLATE_ID => JTF_UMUTIL.template_lookup(x_template_key, to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS')),
42 X_TEMPLATE_KEY => x_template_key,
43 X_PAGE_NAME => x_page_name,
44 X_TEMPLATE_HANDLER => x_template_handler,
45 X_TEMPLATE_TYPE_CODE => x_template_type_code,
46 X_TEMPLATE_NAME => x_template_name,
47 X_ENABLED_FLAG => x_enabled_flag,
48 X_APPLICATION_ID => to_number(x_application_id),
49 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS'),
50 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
51 X_DESCRIPTION => x_description,
52 X_OWNER => x_owner,
53 X_LAST_UPDATE_DATE => x_last_update_date,
54 X_CUSTOM_MODE => x_custom_mode );
55 -- end if;
56 end if;
57
58 exception
59 when no_data_found then
60 JTF_UM_TEMPLATES_PKG.LOAD_ROW(
61 X_TEMPLATE_ID => JTF_UMUTIL.template_lookup(x_template_key, to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS')),
62 X_TEMPLATE_KEY => x_template_key,
63 X_PAGE_NAME => x_page_name,
64 X_TEMPLATE_HANDLER => x_template_handler,
65 X_TEMPLATE_TYPE_CODE => x_template_type_code,
66 X_TEMPLATE_NAME => x_template_name,
67 X_ENABLED_FLAG => x_enabled_flag,
68 X_APPLICATION_ID => to_number(x_application_id),
69 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS'),
70 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
71 X_DESCRIPTION => x_description,
72 X_OWNER => x_owner,
73 X_LAST_UPDATE_DATE => x_last_update_date,
74 X_CUSTOM_MODE => x_custom_mode
75 );
76
77 end LOAD_SEED_TEMPLATES;
78
79
80 procedure LOAD_SEED_APPROVALS(
81 x_upload_mode in varchar2,
82 x_approval_key in varchar2,
83 x_approval_key_start_date in varchar2,
84 x_approval_name in varchar2,
85 x_description in varchar2,
86 x_owner in varchar2,
87 x_enabled_flag in varchar2,
88 x_application_id in varchar2,
89 x_wf_item_type in varchar2,
90 x_use_pending_req_flag in varchar2,
91 x_effective_end_date in varchar2,
92 x_last_update_date in varchar2,
93 x_custom_mode in varchar2 )
94
95 is
96
97 v_db_owner_id number;
98
99 begin
100 if ( x_upload_mode = 'NLS' ) then
101 JTF_UM_APPROVALS_PKG.TRANSLATE_ROW(
102 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
103 X_APPROVAL_NAME => x_approval_name,
104 X_DESCRIPTION => x_description,
105 X_OWNER => x_owner,
106 X_LAST_UPDATE_DATE => x_last_update_date,
107 X_CUSTOM_MODE => x_custom_mode
108 );
109 else
110 -- select LAST_UPDATED_BY
111 -- into v_db_owner_id
112 -- from JTF_UM_APPROVALS_B
113 -- where APPROVAL_ID = JTF_UMUTIL.approval_lookup(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS'));
114
115 -- if (v_db_owner_id = 1) then
116 JTF_UM_APPROVALS_PKG.LOAD_ROW(
117 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
118 X_APPROVAL_KEY => x_approval_key,
119 X_ENABLED_FLAG => x_enabled_flag,
120 X_APPLICATION_ID => to_number(x_application_id),
121 X_WF_ITEM_TYPE => x_wf_item_type,
122 X_USE_PENDING_REQ_FLAG => x_use_pending_req_flag,
123 X_EFFECTIVE_START_DATE => to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS'),
124 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
125 X_APPROVAL_NAME => x_approval_name,
126 X_DESCRIPTION => x_description,
127 X_OWNER => x_owner,
128 X_LAST_UPDATE_DATE => x_last_update_date,
129 X_CUSTOM_MODE => x_custom_mode
130 );
131 -- end if;
132 end if;
133
134 exception
135 when no_data_found then
136 JTF_UM_APPROVALS_PKG.LOAD_ROW(
137 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
138 X_APPROVAL_KEY => x_approval_key,
139 X_ENABLED_FLAG => x_enabled_flag,
140 X_APPLICATION_ID => to_number(x_application_id),
141 X_WF_ITEM_TYPE => x_wf_item_type,
142 X_USE_PENDING_REQ_FLAG => x_use_pending_req_flag,
143 X_EFFECTIVE_START_DATE => to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS'),
144 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
145 X_APPROVAL_NAME => x_approval_name,
146 X_DESCRIPTION => x_description,
147 X_OWNER => x_owner,
148 X_LAST_UPDATE_DATE => x_last_update_date,
149 X_CUSTOM_MODE => x_custom_mode
150 );
151
152 end LOAD_SEED_APPROVALS;
153
154
155
156 procedure LOAD_SEED_APPROVERS(
157 x_upload_mode in varchar2,
158 x_effective_start_date in varchar2,
159 x_approval_key_start_date in varchar2,
160 x_user_name in varchar2,
161 x_approval_key in varchar2,
162 x_approver_seq in varchar2,
163 x_effective_end_date in varchar2,
164 x_owner in varchar2,
165 x_last_update_date in varchar2,
166 x_custom_mode in varchar2 )
167
168 is
169
170 v_db_owner_id number;
171 BEGIN
172 IF (x_upload_mode = 'NLS') THEN
173 NULL;
174 ELSE
175 -- select LAST_UPDATED_BY
176 -- into v_db_owner_id
177 -- from JTF_UM_APPROVERS
178 -- where EFFECTIVE_START_DATE = to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS')
179 -- and APPROVAL_ID = JTF_UMUTIL.approval_lookup(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
180 -- and USER_ID = JTF_UMUTIL.user_lookup(x_user_name);
181
182 -- if (v_db_owner_id = 1) then
183 JTF_UM_APPROVALS_PKG.LOAD_APPROVERS_ROW(
184 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
185 X_APPROVER_SEQ => to_number(x_approver_seq),
186 X_USER_ID => JTF_UMUTIL.user_lookup(x_user_name),
187 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS'),
188 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
189 X_OWNER => x_owner,
190 X_LAST_UPDATE_DATE => x_last_update_date,
191 X_CUSTOM_MODE => x_custom_mode
192 );
193 -- end if;
194 END IF;
195
196 exception
197 when no_data_found then
198 JTF_UM_APPROVALS_PKG.LOAD_APPROVERS_ROW(
199 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
200 X_APPROVER_SEQ => to_number(x_approver_seq),
201 X_USER_ID => JTF_UMUTIL.user_lookup(x_user_name),
202 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date,'YYYY/MM/DD HH24:MI:SS'),
203 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
204 X_OWNER => x_owner,
205 X_LAST_UPDATE_DATE => x_last_update_date,
206 X_CUSTOM_MODE => x_custom_mode
207 );
208
209 END LOAD_SEED_APPROVERS;
210
211
212
213 procedure LOAD_SEED_USERTYPES(
214 x_upload_mode in varchar2,
215 x_usertype_key in varchar2,
216 x_usertype_key_start_date in varchar2,
217 x_usertype_name in varchar2,
218 x_usertype_shortname in varchar2,
219 x_description in varchar2,
220 x_owner in varchar2,
221 x_is_self_service_flag in varchar2,
222 x_email_notification_flag in varchar2,
223 x_enabled_flag in varchar2,
224 x_approval_key in varchar2,
225 x_approval_key_start_date in varchar2,
226 x_application_id in varchar2,
227 x_effective_end_date in varchar2,
228 x_display_order in varchar2,
229 x_last_update_date in varchar2,
230 x_custom_mode in varchar2
231 )
232
233 is
234
235
236 v_db_owner_id number;
237 v_db_display_order number;
238 v_db_usertype_shortname varchar2(230);
239
240 begin
241 if ( x_upload_mode = 'NLS' ) then
242 JTF_UM_USERTYPES_PKG.TRANSLATE_ROW(
243 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
244 X_USERTYPE_NAME => x_usertype_name,
245 X_USERTYPE_SHORTNAME => NVL(x_usertype_shortname, 'CHANGE ME IN THE ADMIN CONSOLE: USERTYPE SETUP SCREEN'),
246 X_DESCRIPTION => x_description,
247 X_OWNER => x_owner,
248 X_LAST_UPDATE_DATE => x_last_update_date,
249 X_CUSTOM_MODE => x_custom_mode
250 );
251 else
252 select LAST_UPDATED_BY, DISPLAY_ORDER, USERTYPE_SHORTNAME
253 into v_db_owner_id, v_db_display_order, v_db_usertype_shortname
254 from JTF_UM_USERTYPES_VL
255 where USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'));
256
257
258
259 JTF_UM_USERTYPES_PKG.LOAD_ROW(
260 X_USERTYPE_KEY => x_usertype_key,
261 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
262 X_IS_SELF_SERVICE_FLAG => x_is_self_service_flag,
263 X_EMAIL_NOTIFICATION_FLAG => x_email_notification_flag,
264 X_ENABLED_FLAG => x_enabled_flag,
265 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup_with_check(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
266 X_APPLICATION_ID => to_number(x_application_id),
267 X_EFFECTIVE_START_DATE => to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'),
268 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
269 X_USERTYPE_NAME => x_usertype_name,
270 X_USERTYPE_SHORTNAME => NVL(x_usertype_shortname, 'CHANGE ME IN THE ADMIN CONSOLE: USERTYPE SETUP SCREEN'),
271 X_DESCRIPTION => x_description,
272 X_DISPLAY_ORDER => x_display_order,
273 X_OWNER => x_owner,
274 X_LAST_UPDATE_DATE => x_last_update_date,
275 X_CUSTOM_MODE => x_custom_mode
276 );
277
278
279
280
281 end if;
282
283 exception
284 when no_data_found then
285 JTF_UM_USERTYPES_PKG.LOAD_ROW(
286 X_USERTYPE_KEY => x_usertype_key,
287 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
288 X_IS_SELF_SERVICE_FLAG => x_is_self_service_flag,
289 X_EMAIL_NOTIFICATION_FLAG => x_email_notification_flag,
290 X_ENABLED_FLAG => x_enabled_flag,
291 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup_with_check(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
292 X_APPLICATION_ID => to_number(x_application_id),
293 X_EFFECTIVE_START_DATE => to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'),
294 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
295 X_USERTYPE_NAME => x_usertype_name,
296 X_USERTYPE_SHORTNAME => NVL(x_usertype_shortname, 'CHANGE ME IN THE ADMIN CONSOLE: USERTYPE SETUP SCREEN'),
297 X_DESCRIPTION => x_description,
298 X_DISPLAY_ORDER => x_display_order,
299 X_OWNER => x_owner,
300 X_LAST_UPDATE_DATE => x_last_update_date,
301 X_CUSTOM_MODE => x_custom_mode
302 );
303
304 end LOAD_SEED_USERTYPES;
305
306
307 procedure LOAD_SEED_USERTYPE_TMPL(
308 x_upload_mode in varchar2,
309 x_usertype_key in varchar2,
310 x_usertype_key_start_date in varchar2,
311 x_template_key in varchar2,
312 x_template_key_start_date in varchar2,
313 x_effective_start_date in varchar2,
314 x_effective_end_date in varchar2,
315 x_owner in varchar2,
316 x_last_update_date in varchar2,
317 x_custom_mode in varchar2
318 )
319 is
320
321 v_db_owner_id number;
322 v_active_record_count number;
323
324 begin
325 if ( x_upload_mode = 'NLS' ) then
326 null;
327 return;
328 else
329 select count(*)
330 into v_active_record_count
331 from JTF_UM_USERTYPE_TMPL
332 where USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
333 and EFFECTIVE_END_DATE is NULL
334 and LAST_UPDATED_BY <> 1;
335
336 if( v_active_record_count > 0 ) then
337 return;
338 end if;
339
340 select LAST_UPDATED_BY
341 into v_db_owner_id
342 from JTF_UM_USERTYPE_TMPL
343 where USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
344 and TEMPLATE_ID = JTF_UMUTIL.template_lookup(x_template_key, to_date(x_template_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
345 and EFFECTIVE_START_DATE = to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS');
346
347 if (v_active_record_count = 0) then
348 JTF_UM_USERTYPES_PKG.LOAD_USERTYPE_TMPL_ROW(
349 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
350 X_TEMPLATE_ID => JTF_UMUTIL.template_lookup(x_template_key, to_date(x_template_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
351 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS'),
352 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS'),
353 X_OWNER => x_owner,
354 X_LAST_UPDATE_DATE => x_last_update_date,
355 X_CUSTOM_MODE => x_custom_mode
356 );
357 end if;
358
359 end if;
360
361 exception
362 when no_data_found then
363
364 update JTF_UM_USERTYPE_TMPL
365 set EFFECTIVE_END_DATE = SYSDATE
366 where EFFECTIVE_END_DATE is NULL
367 and USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'));
368
369 JTF_UM_USERTYPES_PKG.LOAD_USERTYPE_TMPL_ROW(
370 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
371 X_TEMPLATE_ID => JTF_UMUTIL.template_lookup(x_template_key, to_date(x_template_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
372 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS'),
373 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS'),
374 X_OWNER => x_owner,
375 X_LAST_UPDATE_DATE => x_last_update_date,
376 X_CUSTOM_MODE => x_custom_mode
377 );
378
379 end LOAD_SEED_USERTYPE_TMPL;
380
381
382 procedure LOAD_SEED_USERTYPE_ROLE(
383 x_upload_mode in varchar2,
384 x_usertype_key in varchar2,
385 x_usertype_key_start_date in varchar2,
386 x_principal_name in varchar2,
387 x_effective_start_date in varchar2,
388 x_effective_end_date in varchar2,
389 x_owner in varchar2,
390 x_last_update_date in varchar2,
391 x_custom_mode in varchar2
392 )
393 is
394 v_db_owner_id number;
395
396 BEGIN
397 IF (x_upload_mode = 'NLS') THEN
398 NULL;
399 RETURN;
400 ELSE
401 -- select LAST_UPDATED_BY
402 -- into v_db_owner_id
403 -- from JTF_UM_USERTYPE_ROLE
404 -- where USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
405 -- and PRINCIPAL_NAME = x_principal_name
406 -- and EFFECTIVE_START_DATE = TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' );
407
408 -- if (v_db_owner_id = 1) then
409 JTF_UM_ROLE_RESP_PKG.LOAD_usertype_role_ROW(
410 x_usertype_id => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
411 x_principal_name => x_principal_name ,
412 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
413 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
414 x_owner => x_owner ,
415 X_LAST_UPDATE_DATE => x_last_update_date ,
416 X_CUSTOM_MODE => x_custom_mode
417 );
418 -- end if;
419 END IF;
420
421 exception
422 when no_data_found then
423 JTF_UM_ROLE_RESP_PKG.LOAD_usertype_role_ROW(
424 x_usertype_id => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
425 x_principal_name => x_principal_name ,
426 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
427 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
428 x_owner => x_owner,
429 X_LAST_UPDATE_DATE => x_last_update_date ,
430 X_CUSTOM_MODE => x_custom_mode
431 );
432
433 END LOAD_SEED_USERTYPE_ROLE;
434
435
436
437 procedure LOAD_SEED_USERTYPE_RESP(
438 x_upload_mode in varchar2,
439 x_usertype_key in varchar2,
440 x_usertype_key_start_date in varchar2,
441 x_responsibility_key in varchar2,
442 x_effective_start_date in varchar2,
443 x_is_default_flag in varchar2,
444 x_effective_end_date in varchar2,
445 x_owner in varchar2,
446 x_application_id in varchar2 ,
447 x_last_update_date in varchar2,
448 x_custom_mode in varchar2
449 )
450
451 is
452
453 v_db_owner_id number;
454 v_active_record_count number;
455
456 begin
457 if ( x_upload_mode = 'NLS' ) then
458 null;
459 return;
460 else
461 select count(*)
462 into v_active_record_count
463 from JTF_UM_USERTYPE_RESP
464 where USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
465 and EFFECTIVE_END_DATE is NULL
466 and LAST_UPDATED_BY <> 1;
467
468 if( v_active_record_count > 0 ) then
469 return;
470 end if;
471
472 select LAST_UPDATED_BY
473 into v_db_owner_id
474 from JTF_UM_USERTYPE_RESP
475 where USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
476 and RESPONSIBILITY_KEY = x_responsibility_key
477 and EFFECTIVE_START_DATE = to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS');
478
479 if ( v_active_record_count = 0 ) then
480 JTF_UM_ROLE_RESP_PKG.LOAD_usertype_resp_ROW(
481 x_usertype_id => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
482 x_responsibility_key => x_responsibility_key ,
483 x_is_default_flag => x_is_default_flag ,
484 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
485 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
486 x_owner => x_owner ,
487 x_application_id => TO_NUMBER( x_application_id) ,
488 X_LAST_UPDATE_DATE => x_last_update_date ,
489 X_CUSTOM_MODE => x_custom_mode
490
491 );
492 end if;
493 end if;
494
495 exception
496 when no_data_found then
497
498 update JTF_UM_USERTYPE_RESP
499 set EFFECTIVE_END_DATE = SYSDATE
500 where EFFECTIVE_END_DATE is NULL
501 and USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'));
502
503 JTF_UM_ROLE_RESP_PKG.LOAD_usertype_resp_ROW(
504 x_usertype_id => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
505 x_responsibility_key => x_responsibility_key ,
506 x_is_default_flag => x_is_default_flag ,
507 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
508 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
509 x_owner => x_owner ,
510 x_application_id => TO_NUMBER( x_application_id ),
511 X_LAST_UPDATE_DATE => x_last_update_date ,
512 X_CUSTOM_MODE => x_custom_mode
513 );
514
515 end LOAD_SEED_USERTYPE_RESP;
516
517
518 procedure LOAD_SEED_SUBSCRIPTIONS(
519 x_upload_mode in varchar2,
520 x_subscription_key in varchar2,
521 x_subscription_key_start_date in varchar2,
522 x_subscription_name in varchar2,
523 x_description in varchar2,
524 x_owner in varchar2,
525 x_availability_code in varchar2,
526 x_logon_display_frequency in varchar2,
527 x_parent_subscription_key in varchar2,
528 x_parent_key_start_date in varchar2,
529 x_application_id in varchar2,
530 x_enabled_flag in varchar2,
531 x_approval_key in varchar2,
532 x_approval_key_start_date in varchar2,
533 x_auth_delegation_role_id in varchar2,
534 x_effective_end_date in varchar2,
535 x_last_update_date in varchar2,
536 x_custom_mode in varchar2
537
538 )
539
540 is
541
542 v_db_owner_id number;
543
544 BEGIN
545 if ( x_upload_mode = 'NLS' ) then
546 JTF_UM_SUBSCRIPTIONS_PKG.TRANSLATE_ROW(
547 X_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
548 X_SUBSCRIPTION_NAME => x_subscription_name,
549 X_DESCRIPTION => x_description,
550 X_OWNER => x_owner,
551 x_last_update_date => x_last_update_date ,
552 x_custom_mode => x_custom_mode);
553 else
554 -- select LAST_UPDATED_BY
555 -- into v_db_owner_id
556 -- from JTF_UM_SUBSCRIPTIONS_B
557 -- where SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'));
558
559 -- if (v_db_owner_id = 1) then
560 JTF_UM_SUBSCRIPTIONS_PKG.LOAD_ROW(
561 X_SUBSCRIPTION_KEY => x_subscription_key,
562 X_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
563 X_AVAILABILITY_CODE => x_availability_code,
564 X_LOGON_DISPLAY_FREQUENCY => to_number(x_logon_display_frequency),
565 X_PARENT_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup_with_check(x_parent_subscription_key, to_date(x_parent_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
566 X_APPLICATION_ID => to_number(x_application_id),
567 X_ENABLED_FLAG => x_enabled_flag,
568 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup_with_check(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
569 X_AUTH_DELEGATION_ROLE_ID => to_number(x_auth_delegation_role_id),
570 X_EFFECTIVE_START_DATE => to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'),
571 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
572 X_SUBSCRIPTION_NAME => x_subscription_name,
573 X_DESCRIPTION => x_description,
574 X_OWNER => x_owner,
575 X_LAST_UPDATE_DATE => x_last_update_date,
576 X_CUSTOM_MODE => x_custom_mode
577 );
578 -- end if;
579 end if;
580
581 exception
582 when no_data_found then
583 JTF_UM_SUBSCRIPTIONS_PKG.LOAD_ROW(
584 X_SUBSCRIPTION_KEY => x_subscription_key,
585 X_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
586 X_AVAILABILITY_CODE => x_availability_code,
587 X_LOGON_DISPLAY_FREQUENCY => to_number(x_logon_display_frequency),
588 X_PARENT_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup_with_check(x_parent_subscription_key, to_date(x_parent_key_start_date,'YYYY/MM/DD H
589 H24:MI:SS')),
590 X_APPLICATION_ID => to_number(x_application_id),
591 X_ENABLED_FLAG => x_enabled_flag,
592 X_APPROVAL_ID => JTF_UMUTIL.approval_lookup_with_check(x_approval_key, to_date(x_approval_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
593 X_AUTH_DELEGATION_ROLE_ID => to_number(x_auth_delegation_role_id),
594 X_EFFECTIVE_START_DATE => to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'),
595 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date,'YYYY/MM/DD HH24:MI:SS'),
596 X_SUBSCRIPTION_NAME => x_subscription_name,
597 X_DESCRIPTION => x_description,
598 X_OWNER => x_owner,
599 X_LAST_UPDATE_DATE => x_last_update_date ,
600 X_CUSTOM_MODE => x_custom_mode
601 );
602
603 end LOAD_SEED_SUBSCRIPTIONS;
604
605
606
607 procedure LOAD_SEED_SUBSCRIPTION_TMPL(
608 x_upload_mode in varchar2,
609 x_subscription_key in varchar2,
610 x_subscription_key_start_date in varchar2,
611 x_template_key in varchar2,
612 x_template_key_start_date in varchar2,
613 x_effective_start_date in varchar2,
614 x_effective_end_date in varchar2,
615 x_owner in varchar2,
616 x_last_update_date in varchar2,
617 x_custom_mode in varchar2
618 )
619
620 is
621
622 v_db_owner_id number;
623 v_active_record_count number;
624
625 begin
626 if ( x_upload_mode = 'NLS' ) then
627 null;
628 return;
629 else
630 select count(*)
631 into v_active_record_count
632 from JTF_UM_SUBSCRIPTION_TMPL
633 where SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
634 and EFFECTIVE_END_DATE is NULL
635 and LAST_UPDATED_BY <> 1;
636
637 if( v_active_record_count > 0 ) then
638 return;
639 end if;
640
641 select LAST_UPDATED_BY
642 into v_db_owner_id
643 from JTF_UM_SUBSCRIPTION_TMPL
644 where SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
645 and TEMPLATE_ID = JTF_UMUTIL.template_lookup(x_template_key, to_date(x_template_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
646 and EFFECTIVE_START_DATE = to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS');
647
648 if ( v_active_record_count = 0 ) then
649 JTF_UM_SUBSCRIPTIONS_PKG.LOAD_SUBSCRIPTION_TMPL_ROW(
650 X_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
651 X_TEMPLATE_ID => JTF_UMUTIL.template_lookup(x_template_key, to_date(x_template_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
652 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS'),
653 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS'),
654 X_OWNER => x_owner,
655 X_LAST_UPDATE_DATE => x_last_update_date ,
656 X_CUSTOM_MODE => x_custom_mode
657 );
658 end if;
659 end if;
660
661 exception
662 when no_data_found then
663
664 update JTF_UM_SUBSCRIPTION_TMPL
665 set EFFECTIVE_END_DATE = SYSDATE
666 where EFFECTIVE_END_DATE is NULL
667 and SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'));
668
669 JTF_UM_SUBSCRIPTIONS_PKG.LOAD_SUBSCRIPTION_TMPL_ROW(
670 X_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
671 X_TEMPLATE_ID => JTF_UMUTIL.template_lookup(x_template_key, to_date(x_template_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
672 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS'),
673 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS'),
674 X_OWNER => x_owner,
675 X_LAST_UPDATE_DATE => x_last_update_date ,
676 X_CUSTOM_MODE => x_custom_mode
677 );
678
679 end LOAD_SEED_SUBSCRIPTION_TMPL;
680
681
682
683
684 procedure LOAD_SEED_SUBSCR_USERTYPE(
685 x_upload_mode in varchar2,
686 x_subscription_key in varchar2,
687 x_subscription_key_start_date in varchar2,
688 x_usertype_key in varchar2,
689 x_usertype_key_start_date in varchar2,
690 x_effective_start_date in varchar2,
691 x_subscription_flag in varchar2,
692 x_subscription_display_order in varchar2,
693 x_effective_end_date in varchar2,
694 x_owner in varchar2,
695 x_last_update_date in varchar2,
696 x_custom_mode in varchar2
697 )
698
699 is
700
701 v_db_owner_id number;
702
703 BEGIN
704 if ( x_upload_mode = 'NLS' ) then
705 null;
706 ELSE
707 -- select LAST_UPDATED_BY
708 -- into v_db_owner_id
709 -- from JTF_UM_USERTYPE_SUBSCRIP
710 -- where SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
711 -- and USERTYPE_ID = JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
712 -- and EFFECTIVE_START_DATE = to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS');
713
714 -- if (v_db_owner_id = 1) then
715 JTF_UM_USERTYPES_PKG.LOAD_USERTYPES_SUB_ROW(
716 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
717 X_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
718 X_SUBSCRIPTION_FLAG => x_subscription_flag,
719 X_DISPLAY_ORDER => to_number(x_subscription_display_order),
720 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS'),
721 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS'),
722 X_OWNER => x_owner,
723 X_LAST_UPDATE_DATE => x_last_update_date ,
724 X_CUSTOM_MODE => x_custom_mode
725 );
726 -- end if;
727 end if;
728
729 exception
730 when no_data_found then
731 JTF_UM_USERTYPES_PKG.LOAD_USERTYPES_SUB_ROW(
732 X_USERTYPE_ID => JTF_UMUTIL.usertype_lookup(x_usertype_key, to_date(x_usertype_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
733 X_SUBSCRIPTION_ID => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
734 X_SUBSCRIPTION_FLAG => x_subscription_flag,
735 X_DISPLAY_ORDER => to_number(x_subscription_display_order),
736 X_EFFECTIVE_START_DATE => to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS'),
737 X_EFFECTIVE_END_DATE => to_date(x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS'),
738 X_OWNER => x_owner,
739 X_LAST_UPDATE_DATE => x_last_update_date ,
740 X_CUSTOM_MODE => x_custom_mode
741 );
742
743 end LOAD_SEED_SUBSCR_USERTYPE;
744
745
746 procedure LOAD_SEED_SUBSCRIPTION_ROLE(
747 x_upload_mode in varchar2,
748 x_subscription_key in varchar2,
749 x_subscription_key_start_date in varchar2,
750 x_principal_name in varchar2,
751 x_effective_start_date in varchar2,
752 x_effective_end_date in varchar2,
753 x_owner in varchar2,
754 x_last_update_date in varchar2,
755 x_custom_mode in varchar2)
756
757 is
758
759 v_db_owner_id number;
760
761 BEGIN
762 IF (x_upload_mode = 'NLS') THEN
763 NULL;
764 else
765
766 -- select LAST_UPDATED_BY
767 -- into v_db_owner_id
768 -- from JTF_UM_SUBSCRIPTION_ROLE
769 -- where SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
770 -- and PRINCIPAL_NAME = x_principal_name
771 -- and EFFECTIVE_START_DATE = to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS');
772
773 -- if (v_db_owner_id = 1) then
774 JTF_UM_ROLE_RESP_PKG.LOAD_subscription_role_ROW(
775 x_subscription_id => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
776 x_principal_name => x_principal_name ,
777 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
778 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
779 x_owner => x_owner,
780 X_LAST_UPDATE_DATE => x_last_update_date ,
781 X_CUSTOM_MODE => x_custom_mode
782 );
783 -- end if;
784 end if;
785
786 exception
787 when no_data_found then
788 JTF_UM_ROLE_RESP_PKG.LOAD_subscription_role_ROW(
789 x_subscription_id => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
790 x_principal_name => x_principal_name ,
791 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
792 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
793 x_owner => x_owner,
794 X_LAST_UPDATE_DATE => x_last_update_date ,
795 X_CUSTOM_MODE => x_custom_mode
796 );
797 END LOAD_SEED_SUBSCRIPTION_ROLE;
798
799
800
801 procedure LOAD_SEED_SUBSCRIPTION_RESP(
802 x_upload_mode in varchar2,
803 x_subscription_key in varchar2,
804 x_subscription_key_start_date in varchar2,
805 x_responsibility_key in varchar2,
806 x_effective_start_date in varchar2,
807 x_effective_end_date in varchar2,
808 x_owner in varchar2,
809 x_application_id in varchar2,
810 x_last_update_date in varchar2,
811 x_custom_mode in varchar2
812 )
813
814 is
815
816 v_db_owner_id number;
817 v_active_record_count number;
818
819 begin
820 if ( x_upload_mode = 'NLS' ) then
821 null;
822 return;
823 else
824 select count(*)
825 into v_active_record_count
826 from JTF_UM_SUBSCRIPTION_RESP
827 where SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
828 and EFFECTIVE_END_DATE is NULL
829 and LAST_UPDATED_BY <> 1;
830
831 if( v_active_record_count > 0 ) then
832 return;
833 end if;
834
835 -- select LAST_UPDATED_BY
836 -- into v_db_owner_id
837 -- from JTF_UM_SUBSCRIPTION_RESP
838 -- where SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'))
839 -- and RESPONSIBILITY_KEY = x_responsibility_key
840 -- and EFFECTIVE_START_DATE = to_date(x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS');
841
842 if ( v_active_record_count = 0 ) then
843 JTF_UM_ROLE_RESP_PKG.LOAD_subscription_resp_ROW(
844 x_subscription_id => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
845 x_responsibility_key => x_responsibility_key ,
846 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
847 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
848 x_owner => x_owner ,
849 x_application_id => TO_NUMBER( x_application_id ),
850 X_LAST_UPDATE_DATE => x_last_update_date ,
851 X_CUSTOM_MODE => x_custom_mode
852 );
853 end if;
854 end if;
855
856 exception
857 when no_data_found then
858
859 update JTF_UM_SUBSCRIPTION_RESP
860 set EFFECTIVE_END_DATE = SYSDATE
861 where EFFECTIVE_END_DATE is NULL
862 and SUBSCRIPTION_ID = JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS'));
863
864 JTF_UM_ROLE_RESP_PKG.LOAD_subscription_resp_ROW(
865 x_subscription_id => JTF_UMUTIL.subscription_lookup(x_subscription_key, to_date(x_subscription_key_start_date,'YYYY/MM/DD HH24:MI:SS')),
866 x_responsibility_key => x_responsibility_key ,
867 x_effective_start_date => TO_DATE ( x_effective_start_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
868 x_effective_end_date => TO_DATE ( x_effective_end_date, 'YYYY/MM/DD HH24:MI:SS' ) ,
869 x_owner => x_owner ,
870 x_application_id => TO_NUMBER( x_application_id ),
871 X_LAST_UPDATE_DATE => x_last_update_date ,
872 X_CUSTOM_MODE => x_custom_mode
873 );
874
875 end LOAD_SEED_SUBSCRIPTION_RESP;
876
877 end jumf_lct_pkg;