[Home] [Help]
PACKAGE BODY: APPS.IEU_SEED_DATA_LOADER_PKG
Source
1 PACKAGE BODY IEU_SEED_DATA_LOADER_PKG AS
2 /* $Header: IEUCMSDLDB.pls 120.0 2005/07/27 13:56:59 appldev noship $ */
3
4 PROCEDURE Load_IEU_UWQ_SVR_MPS_MMAPS (
5 P_UPLOAD_MODE IN VARCHAR2,
6 P_SVR_MPS_MMAP_ID IN VARCHAR2,
7 P_MEDIA_TYPE_ID IN VARCHAR2,
8 P_SVR_TYPE_ID IN VARCHAR2,
9 P_MEDIA_TYPE_MAP IN VARCHAR2,
10 P_OWNER IN VARCHAR2
11 )is
12
13 user_id number := 0;
14
15 begin
16 if (P_UPLOAD_MODE = 'NLS') then
17 null;
18 else
19 begin
20 --if (P_OWNER = 'SEED') then
21 -- user_id := 1;
22 --end if;
23
24 user_id := fnd_load_util.owner_id(P_OWNER);
25
26 update IEU_UWQ_SVR_MPS_MMAPS b
27 set b.LAST_UPDATED_BY = user_id,
28 b.LAST_UPDATE_DATE = SYSDATE,
29 b.LAST_UPDATE_LOGIN = 0,
30 b.MEDIA_TYPE_ID = P_MEDIA_TYPE_ID,
31 b.SVR_TYPE_ID = P_SVR_TYPE_ID,
32 b.MEDIA_TYPE_MAP = P_MEDIA_TYPE_MAP
33 where b.SVR_MPS_MMAP_ID = P_SVR_MPS_MMAP_ID;
34 if sql%notfound then
35 raise NO_DATA_FOUND;
36 end if;
37
38 exception
39 when NO_DATA_FOUND then
40 insert into IEU_UWQ_SVR_MPS_MMAPS (
41 SVR_MPS_MMAP_ID,
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATE_LOGIN,
47 MEDIA_TYPE_ID,
48 SVR_TYPE_ID,
49 MEDIA_TYPE_MAP )
50 values( to_number(P_SVR_MPS_MMAP_ID),
51 user_id,
52 SYSDATE,
53 user_id,
54 SYSDATE,
55 0,
56 to_number(P_MEDIA_TYPE_ID),
57 to_number(P_SVR_TYPE_ID),
58 P_MEDIA_TYPE_MAP);
59 end;
60
61 end if;
62
63 end Load_IEU_UWQ_SVR_MPS_MMAPS;
64
65 PROCEDURE Load_IEU_UWQ_CLI_MED_PLUGINS (
66 P_UPLOAD_MODE IN VARCHAR2,
67 P_CLI_PLUGIN_ID IN VARCHAR2,
68 P_MEDIA_TYPE_ID IN VARCHAR2,
69 P_CLI_PLUGIN_CLASS IN VARCHAR2,
70 P_APPLICATION_ID IN NUMBER,
71 P_OWNER IN VARCHAR2
72 )is
73 user_id number := 0;
74 begin
75 if (P_UPLOAD_MODE = 'NLS') then
76 null;
77 else
78 begin
79 --if (P_OWNER = 'SEED') then
80 -- user_id := 1;
81 --end if;
82
83 user_id := fnd_load_util.owner_id(P_OWNER);
84
85 update IEU_UWQ_CLI_MED_PLUGINS b
86 set b.LAST_UPDATED_BY = user_id,
87 b.LAST_UPDATE_DATE = SYSDATE,
88 b.LAST_UPDATE_LOGIN = 0,
89 b.MEDIA_TYPE_ID = P_MEDIA_TYPE_ID,
90 b.CLI_PLUGIN_CLASS = P_CLI_PLUGIN_CLASS,
91 b.APPLICATION_ID = P_APPLICATION_ID
92 where b.CLI_PLUGIN_ID = P_CLI_PLUGIN_ID;
93
94 if sql%notfound then
95 raise NO_DATA_FOUND;
96 end if;
97
98 exception
99 when NO_DATA_FOUND then
100 insert into IEU_UWQ_CLI_MED_PLUGINS (
101 CLI_PLUGIN_ID,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATE_LOGIN,
107 MEDIA_TYPE_ID,
108 CLI_PLUGIN_CLASS,
109 APPLICATION_ID )
110 values( to_number(P_CLI_PLUGIN_ID),
111 user_id,
112 SYSDATE,
113 user_id,
114 SYSDATE,
115 0,
116 to_number(P_MEDIA_TYPE_ID),
117 P_CLI_PLUGIN_CLASS,
118 P_APPLICATION_ID );
119 end;
120 end if;
121 end Load_IEU_UWQ_CLI_MED_PLUGINS;
122
123 PROCEDURE Load_IEU_UWQ_SVR_MPS_PLUGINS (
124 P_UPLOAD_MODE IN VARCHAR2,
125 P_SVR_MPS_PLUGIN_ID IN VARCHAR2,
126 P_SVR_TYPE_ID IN VARCHAR2,
127 P_SVR_PLUGIN_CLASS IN VARCHAR2,
128 P_APPLICATION_ID IN NUMBER,
129 P_OWNER IN VARCHAR2
130 )is
131 user_id number := 0;
132 begin
133 if (P_UPLOAD_MODE = 'NLS') then
134 null;
135 else
136 begin
137 --if (P_OWNER = 'SEED') then
138 -- user_id := 1;
139 --end if;
140
141 user_id := fnd_load_util.owner_id(P_OWNER);
142
143 update IEU_UWQ_SVR_MPS_PLUGINS b
144 set b.LAST_UPDATED_BY = user_id,
145 b.LAST_UPDATE_DATE = SYSDATE,
146 b.LAST_UPDATE_LOGIN = 0,
147 b.SVR_TYPE_ID = P_SVR_TYPE_ID,
148 b.SVR_PLUGIN_CLASS = P_SVR_PLUGIN_CLASS,
149 b.APPLICATION_ID = P_APPLICATION_ID
150 where b.SVR_MPS_PLUGIN_ID = P_SVR_MPS_PLUGIN_ID;
151 if sql%notfound then
152 raise NO_DATA_FOUND;
153 end if;
154
155 exception
156 when NO_DATA_FOUND then
157 insert into IEU_UWQ_SVR_MPS_PLUGINS (
158 SVR_MPS_PLUGIN_ID,
159 CREATED_BY,
160 CREATION_DATE,
161 LAST_UPDATED_BY,
162 LAST_UPDATE_DATE,
163 LAST_UPDATE_LOGIN,
164 SVR_TYPE_ID,
165 SVR_PLUGIN_CLASS,
166 APPLICATION_ID )
167 values( to_number(P_SVR_MPS_PLUGIN_ID),
168 user_id,
169 SYSDATE,
170 user_id,
171 SYSDATE,
172 0,
173 to_number(P_SVR_TYPE_ID),
174 P_SVR_PLUGIN_CLASS,
175 P_APPLICATION_ID );
176
177 end;
178 end if;
179
180 end Load_IEU_UWQ_SVR_MPS_PLUGINS;
181
182 PROCEDURE Load_IEU_UWQ_SEL_ENUMERATORS (
183 P_UPLOAD_MODE IN VARCHAR2,
184 P_SEL_ENUM_ID IN VARCHAR2,
185 P_ENUM_PROC IN VARCHAR2,
186 P_REFRESH_PROC IN VARCHAR2,
187 P_ENUM_TYPE_UUID IN VARCHAR2,
188 P_OWNER IN VARCHAR2
189 )is
190 user_id number := 0;
191 begin
192 if (P_UPLOAD_MODE = 'NLS') then
193 null;
194 else
195 begin
196 --if (P_OWNER = 'SEED') then
197 -- user_id := 1;
198 -- end if;
199
200 user_id := fnd_load_util.owner_id(P_OWNER);
201
202 update IEU_UWQ_SEL_ENUMERATORS b
203 set b.LAST_UPDATED_BY = user_id,
204 b.LAST_UPDATE_DATE = SYSDATE,
205 b.LAST_UPDATE_LOGIN = 0,
206 b.ENUM_PROC = P_ENUM_PROC,
207 b.REFRESH_PROC = P_REFRESH_PROC,
208 b.ENUM_TYPE_UUID = P_ENUM_TYPE_UUID
209 where b.SEL_ENUM_ID = P_SEL_ENUM_ID;
210
211 if sql%notfound then
212 raise NO_DATA_FOUND;
213 end if;
214
215 exception
216 when NO_DATA_FOUND then
217 insert into IEU_UWQ_SEL_ENUMERATORS (
218 SEL_ENUM_ID,
219 CREATED_BY,
220 CREATION_DATE,
221 LAST_UPDATED_BY,
222 LAST_UPDATE_DATE,
223 LAST_UPDATE_LOGIN,
224 ENUM_PROC,
225 REFRESH_PROC,
226 ENUM_TYPE_UUID )
227 values( to_number(P_SEL_ENUM_ID),
228 user_id,
229 SYSDATE,
230 user_id,
231 SYSDATE,
232 0,
233 P_ENUM_PROC,
234 P_REFRESH_PROC,
235 P_ENUM_TYPE_UUID);
236 end;
237 end if;
238
239 end Load_IEU_UWQ_SEL_ENUMERATORS;
240
241 --ieuenum.lct
242
243 PROCEDURE Load_IEU_UWQ_SEL_ENUMERATORS (
244 P_UPLOAD_MODE IN VARCHAR2,
245 P_SEL_ENUM_ID IN VARCHAR2,
246 P_APPLICATION_SHORT_NAME IN VARCHAR2,
247 P_ENUM_TYPE_UUID IN VARCHAR2,
248 P_OWNER IN VARCHAR2,
249 P_ENUM_PROC IN VARCHAR2,
250 P_REFRESH_PROC IN VARCHAR2,
251 P_WORK_Q_LABEL_LU_TYPE IN VARCHAR2,
252 P_WORK_Q_LABEL_LU_CODE IN VARCHAR2,
253 P_WORK_Q_ENABLE_PROFILE_OPTION IN VARCHAR2,
254 P_WORK_Q_ORDER_PROFILE_OPTION IN VARCHAR2,
255 P_WORK_Q_ORDER_SYSTEM_DEFAULT IN VARCHAR2,
256 P_WORK_Q_VIEW_FOR_PRIMARY_NODE IN VARCHAR2,
257 P_WORK_Q_VIEW_EXTRA_WHERE IN VARCHAR2,
258 P_MEDIA_TYPE_ID IN VARCHAR2,
259 P_DEFAULT_RES_CAT_ID IN VARCHAR2,
260 P_RES_CAT_PROFILE_OPT IN VARCHAR2,
261 P_NOT_FOR_MYWORK_FLAG IN VARCHAR2,
262 P_NOT_VALID_FLAG IN VARCHAR2,
263 P_ACTION_PROC IN VARCHAR2,
264 P_ACTION_PROC_TYPE_CODE IN VARCHAR2,
265 P_ACTION_OBJECT_CODE IN VARCHAR2,
266 P_WORK_Q_REGISTER_TYPE IN VARCHAR2,
267 P_WORK_PANEL_REGISTERED_FLAG IN VARCHAR2
268 )is
269 l_user_id number := 0;
270 l_app_id number;
271 l_cur_app_id number;
272 begin
273 if (P_UPLOAD_MODE = 'NLS') then
274 return;
275 end if;
276
277 select
278 application_id
279 into
280 l_app_id
281 from
282 fnd_application
283 where
284 application_short_name = P_APPLICATION_SHORT_NAME;
285
286 --if (P_OWNER = 'SEED') then
287 -- l_user_id := 1;
288 --end if;
289
290 l_user_id := fnd_load_util.owner_id(P_OWNER);
291
292 begin
293
294 -- if we're uploading IEU data, then we don't want to over-write
295 -- new provider data if if already exists... only if it's still
296 -- owned by IEU.
297
298 if (l_app_id = 696) then
299 l_cur_app_id := 0;
300 select
301 application_id
302 into
303 l_cur_app_id
304 from
305 IEU_UWQ_SEL_ENUMERATORS e
306 where
307 e.SEL_ENUM_ID = P_SEL_ENUM_ID;
308
309 if (sql%notfound) then
310 raise NO_DATA_FOUND;
311 end if;
312
313 if ((l_cur_app_id = 696) or (l_cur_app_id = -1)) then
314
315 update IEU_UWQ_SEL_ENUMERATORS e
316 set
317 e.LAST_UPDATED_BY = l_user_id,
318 e.LAST_UPDATE_DATE = SYSDATE,
319 e.LAST_UPDATE_LOGIN = 0,
320 e.enum_proc = P_ENUM_PROC,
321 e.enum_type_uuid = P_ENUM_TYPE_UUID,
322 e.refresh_proc = P_REFRESH_PROC,
323 e.application_id = l_app_id,
324 e.work_q_label_lu_type = P_WORK_Q_LABEL_LU_TYPE,
325 e.work_q_label_lu_code = P_WORK_Q_LABEL_LU_CODE,
326 e.work_q_enable_profile_option = P_WORK_Q_ENABLE_PROFILE_OPTION,
327 e.work_q_view_for_primary_node = P_WORK_Q_VIEW_FOR_PRIMARY_NODE,
328 e.work_q_view_extra_where = P_WORK_Q_VIEW_EXTRA_WHERE,
329 e.work_q_order_profile_option = P_WORK_Q_ORDER_PROFILE_OPTION,
330 e.work_q_order_system_default = P_WORK_Q_ORDER_SYSTEM_DEFAULT,
331 e.media_type_id = P_MEDIA_TYPE_ID,
332 e.default_res_cat_id = P_DEFAULT_RES_CAT_ID,
333 e.res_cat_profile_opt = P_RES_CAT_PROFILE_OPT,
334 e.not_for_mywork_flag = P_NOT_FOR_MYWORK_FLAG,
335 e.not_valid_flag = P_NOT_VALID_FLAG,
336 e.action_proc = P_ACTION_PROC,
337 e.action_proc_type_code = P_ACTION_PROC_TYPE_CODE,
338 e.action_object_code = P_ACTION_OBJECT_CODE,
339 e.work_q_register_type = P_WORK_Q_REGISTER_TYPE,
340 e.work_panel_registered_flag = P_WORK_PANEL_REGISTERED_FLAG
341 where
342 e.SEL_ENUM_ID = P_SEL_ENUM_ID;
343
344 end if;
345 else
346
347 update IEU_UWQ_SEL_ENUMERATORS e
348 set
349 e.LAST_UPDATED_BY = l_user_id,
350 e.LAST_UPDATE_DATE = SYSDATE,
351 e.LAST_UPDATE_LOGIN = 0,
352 e.enum_proc = P_ENUM_PROC,
353 e.enum_type_uuid = P_ENUM_TYPE_UUID,
354 e.refresh_proc = P_REFRESH_PROC,
355 e.application_id = l_app_id,
356 e.work_q_label_lu_type = P_WORK_Q_LABEL_LU_TYPE,
357 e.work_q_label_lu_code = P_WORK_Q_LABEL_LU_CODE,
358 e.work_q_enable_profile_option = P_WORK_Q_ENABLE_PROFILE_OPTION,
359 e.work_q_view_for_primary_node = P_WORK_Q_VIEW_FOR_PRIMARY_NODE,
360 e.work_q_view_extra_where = P_WORK_Q_VIEW_EXTRA_WHERE,
361 e.work_q_order_profile_option = P_WORK_Q_ORDER_PROFILE_OPTION,
362 e.work_q_order_system_default = P_WORK_Q_ORDER_SYSTEM_DEFAULT,
363 e.media_type_id = P_MEDIA_TYPE_ID,
364 e.default_res_cat_id = P_DEFAULT_RES_CAT_ID,
365 e.res_cat_profile_opt = P_RES_CAT_PROFILE_OPT,
366 e.not_for_mywork_flag = P_NOT_FOR_MYWORK_FLAG,
367 e.not_valid_flag = P_NOT_VALID_FLAG,
368 e.action_proc = P_ACTION_PROC,
369 e.action_proc_type_code = P_ACTION_PROC_TYPE_CODE,
370 e.action_object_code = P_ACTION_OBJECT_CODE,
371 e.work_q_register_type = P_WORK_Q_REGISTER_TYPE,
372 e.work_panel_registered_flag = P_WORK_PANEL_REGISTERED_FLAG
373 where
374 e.SEL_ENUM_ID = P_SEL_ENUM_ID;
375
376 if (sql%notfound) then
377 raise NO_DATA_FOUND;
378 end if;
379
380 end if;
381
382 exception
383 when NO_DATA_FOUND then
384
385 insert into IEU_UWQ_SEL_ENUMERATORS (
386 SEL_ENUM_ID,
387 CREATED_BY,
388 CREATION_DATE,
389 LAST_UPDATED_BY,
390 LAST_UPDATE_DATE,
391 LAST_UPDATE_LOGIN,
392 ENUM_TYPE_UUID,
393 ENUM_PROC,
394 REFRESH_PROC,
395 OBJECT_VERSION_NUMBER,
396 APPLICATION_ID,
397 WORK_Q_LABEL_LU_TYPE,
398 WORK_Q_LABEL_LU_CODE,
399 WORK_Q_ENABLE_PROFILE_OPTION,
400 WORK_Q_ORDER_PROFILE_OPTION,
401 WORK_Q_ORDER_SYSTEM_DEFAULT,
402 WORK_Q_VIEW_FOR_PRIMARY_NODE,
403 WORK_Q_VIEW_EXTRA_WHERE,
404 MEDIA_TYPE_ID,
405 DEFAULT_RES_CAT_ID,
406 RES_CAT_PROFILE_OPT,
407 NOT_FOR_MYWORK_FLAG,
408 NOT_VALID_FLAG,
409 ACTION_PROC,
410 ACTION_PROC_TYPE_CODE,
411 ACTION_OBJECT_CODE,
412 WORK_Q_REGISTER_TYPE,
413 WORK_PANEL_REGISTERED_FLAG
414 )
415 values (
416 to_number(P_SEL_ENUM_ID),
417 l_user_id,
418 SYSDATE,
419 l_user_id,
420 SYSDATE,
421 0,
422 P_ENUM_TYPE_UUID,
423 P_ENUM_PROC,
424 P_REFRESH_PROC,
425 0,
426 l_app_id,
427 P_WORK_Q_LABEL_LU_TYPE,
428 P_WORK_Q_LABEL_LU_CODE,
429 P_WORK_Q_ENABLE_PROFILE_OPTION,
430 P_WORK_Q_ORDER_PROFILE_OPTION,
431 to_number(P_WORK_Q_ORDER_SYSTEM_DEFAULT),
432 P_WORK_Q_VIEW_FOR_PRIMARY_NODE,
433 P_WORK_Q_VIEW_EXTRA_WHERE,
434 to_number(P_MEDIA_TYPE_ID),
435 to_number(P_DEFAULT_RES_CAT_ID),
436 P_RES_CAT_PROFILE_OPT,
437 P_NOT_FOR_MYWORK_FLAG,
438 P_NOT_VALID_FLAG,
439 P_ACTION_PROC,
440 P_ACTION_PROC_TYPE_CODE,
441 P_ACTION_OBJECT_CODE,
442 P_WORK_Q_REGISTER_TYPE,
443 P_WORK_PANEL_REGISTERED_FLAG );
444 end;
445
446 end Load_IEU_UWQ_SEL_ENUMERATORS;
447
448
449
450 PROCEDURE Load_IEU_UWQ_NODE_DS (
451 P_UPLOAD_MODE IN VARCHAR2,
452 P_SEL_ENUM_ID IN VARCHAR2,
453 P_APPLICATION_SHORT_NAME IN VARCHAR2,
454 P_ENUM_TYPE_UUID IN VARCHAR2,
455 P_NODE_DS_ID IN VARCHAR2,
456 P_DATASOURCE_NAME IN VARCHAR2,
457 P_OWNER IN VARCHAR2
458 )is
459 l_user_id number := 0;
460 begin
461
462 if (P_UPLOAD_MODE = 'NLS') then
463 return;
464 end if;
465
466 begin
467
468 --if (:OWNER = 'SEED') then
469 -- l_user_id := 1;
470 --end if;
471
472 l_user_id := fnd_load_util.owner_id(P_OWNER);
473
474 update IEU_UWQ_NODE_DS m
475 set
476 m.LAST_UPDATED_BY = l_user_id,
477 m.LAST_UPDATE_DATE = SYSDATE,
478 m.LAST_UPDATE_LOGIN = 0,
479 m.DATASOURCE_NAME = P_DATASOURCE_NAME
480 where
481 (m.enum_type_uuid = P_ENUM_TYPE_UUID) and
482 (m.node_ds_id = P_NODE_DS_ID);
483
484 if (sql%notfound) then
485 raise NO_DATA_FOUND;
486 end if;
487
488 exception
489 when NO_DATA_FOUND then
490
491 insert into IEU_UWQ_NODE_DS (
492 OBJECT_VERSION_NUMBER,
493 CREATED_BY,
494 CREATION_DATE,
495 LAST_UPDATED_BY,
496 LAST_UPDATE_DATE,
497 LAST_UPDATE_LOGIN,
498 ENUM_TYPE_UUID,
499 NODE_DS_ID,
500 DATASOURCE_NAME
501 )
502 values (
503 0,
504 l_user_id,
505 SYSDATE,
506 l_user_id,
507 SYSDATE,
508 0,
509 P_ENUM_TYPE_UUID,
510 to_number(P_NODE_DS_ID),
511 P_DATASOURCE_NAME
512 );
513 end;
514 end Load_IEU_UWQ_NODE_DS;
515
516 PROCEDURE Load_IEU_UWQ_RES_CAT_ENM_MAPS (
517 P_UPLOAD_MODE IN VARCHAR2,
518 P_SEL_ENUM_ID IN VARCHAR2,
519 P_APPLICATION_SHORT_NAME IN VARCHAR2,
520 P_ENUM_TYPE_UUID IN VARCHAR2,
521 P_OWNER IN VARCHAR2,
522 P_RES_CAT_ID IN VARCHAR2
523 )is
524 l_user_id number := 0;
525 begin
526
527 if (P_UPLOAD_MODE = 'NLS') then
528 return;
529 end if;
530
531 --if (:OWNER = 'SEED') then
532 -- l_user_id := 1;
533 --end if;
534
535 l_user_id := fnd_load_util.owner_id(P_OWNER);
536
537 begin
538 update IEU_UWQ_RES_CAT_ENM_MAPS m
539 set
540 m.LAST_UPDATED_BY = l_user_id,
541 m.LAST_UPDATE_DATE = SYSDATE,
542 m.LAST_UPDATE_LOGIN = 0
543 where
544 (m.enum_type_uuid = P_ENUM_TYPE_UUID) and
545 (m.res_cat_id = P_RES_CAT_ID);
546
547 if (sql%notfound) then
548 raise NO_DATA_FOUND;
549 end if;
550
551 exception
552 when NO_DATA_FOUND then
553
554 insert into IEU_UWQ_RES_CAT_ENM_MAPS (
555 OBJECT_VERSION_NUMBER,
556 CREATED_BY,
557 CREATION_DATE,
558 LAST_UPDATED_BY,
559 LAST_UPDATE_DATE,
560 LAST_UPDATE_LOGIN,
561 ENUM_TYPE_UUID,
562 RES_CAT_ID
563 )
564 values (
565 0,
566 l_user_id,
567 SYSDATE,
568 l_user_id,
569 SYSDATE,
570 0,
571 P_ENUM_TYPE_UUID,
572 to_number(P_RES_CAT_ID)
573 );
574 end;
575
576 end Load_IEU_UWQ_RES_CAT_ENM_MAPS;
577
578 -- ieuctlpg.lct
579
580 PROCEDURE Load_IEU_CLI_PROV_PLUGINS (
581 P_UPLOAD_MODE IN VARCHAR2,
582 P_PLUGIN_ID IN NUMBER,
583 P_PLUGIN_CLASS_NAME IN VARCHAR2,
584 P_IS_ACTIVE_FLAG IN VARCHAR2,
585 P_APPLICATION_ID IN NUMBER,
586 P_OWNER IN VARCHAR2
587 )is
588 user_id NUMBER := 0;
589 begin
590
591 IF (P_UPLOAD_MODE = 'NLS') THEN
592 NULL;
593 ELSE
594 BEGIN
595 --IF (P_OWNER = 'SEED') then
596 --user_id := 1;
597 --END IF;
598
599 user_id := fnd_load_util.owner_id(P_OWNER);
600
601 UPDATE IEU_CLI_PROV_PLUGINS cliprov
602 SET
603 cliprov.LAST_UPDATED_BY = user_id,
604 cliprov.LAST_UPDATE_DATE = SYSDATE,
605 cliprov.LAST_UPDATE_LOGIN = 0,
606 cliprov.PLUGIN_CLASS_NAME = P_PLUGIN_CLASS_NAME,
607 cliprov.IS_ACTIVE_FLAG = P_IS_ACTIVE_FLAG,
608 cliprov.APPLICATION_ID = P_APPLICATION_ID
609 WHERE cliprov.PLUGIN_ID = P_PLUGIN_ID;
610
611 IF sql%notfound then
612 raise NO_DATA_FOUND;
613 END IF;
614
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617
618 INSERT INTO IEU_CLI_PROV_PLUGINS (
619 PLUGIN_ID,
620 CREATED_BY,
621 CREATION_DATE,
622 LAST_UPDATED_BY,
623 LAST_UPDATE_DATE,
624 LAST_UPDATE_LOGIN,
625 PLUGIN_CLASS_NAME,
626 IS_ACTIVE_FLAG,
627 APPLICATION_ID
628 )
629 VALUES (
630 to_number(P_PLUGIN_ID),
631 user_id,
632 SYSDATE,
633 user_id,
634 SYSDATE,
635 0,
636 P_PLUGIN_CLASS_NAME,
637 P_IS_ACTIVE_FLAG,
638 to_number(P_APPLICATION_ID)
639 );
640 END;
641 END IF;
642
643 end Load_IEU_CLI_PROV_PLUGINS;
644
645 PROCEDURE Load_IEU_CLI_PROV_MED_MAPS (
646 P_UPLOAD_MODE IN VARCHAR2,
647 P_PLUGIN_MED_MAP_ID IN NUMBER,
648 P_PLUGIN_ID IN NUMBER,
649 P_MEDIA_TYPE_ID IN NUMBER,
650 P_CONDITIONAL_FUNC IN VARCHAR2,
651 P_OWNER IN VARCHAR2
652 )is
653 user_id NUMBER := 0;
654 begin
655
656 IF (P_UPLOAD_MODE = 'NLS') THEN
657 NULL;
658 ELSE
659 BEGIN
660 --IF (P_OWNER = 'SEED') then
661 -- user_id := 1;
662 -- END IF;
663
664 user_id := fnd_load_util.owner_id(P_OWNER);
665
666 UPDATE IEU_CLI_PROV_PLUGIN_MED_MAPS clipmmap
667 SET
668 clipmmap.LAST_UPDATED_BY = user_id,
669 clipmmap.LAST_UPDATE_DATE = SYSDATE,
670 clipmmap.LAST_UPDATE_LOGIN = 0,
671 clipmmap.PLUGIN_ID = P_PLUGIN_ID,
672 clipmmap.MEDIA_TYPE_ID = P_MEDIA_TYPE_ID,
673 clipmmap.CONDITIONAL_FUNC = P_CONDITIONAL_FUNC
674 WHERE clipmmap.PLUGIN_MED_MAP_ID = P_PLUGIN_MED_MAP_ID;
675
676 IF sql%notfound then
677 raise NO_DATA_FOUND;
678 END IF;
679
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682
683 INSERT INTO IEU_CLI_PROV_PLUGIN_MED_MAPS (
684 PLUGIN_MED_MAP_ID,
685 CREATED_BY,
686 CREATION_DATE,
687 LAST_UPDATED_BY,
688 LAST_UPDATE_DATE,
689 LAST_UPDATE_LOGIN,
690 PLUGIN_ID,
691 MEDIA_TYPE_ID,
692 CONDITIONAL_FUNC )
693 VALUES (
694 to_number(P_PLUGIN_MED_MAP_ID),
695 user_id,
696 SYSDATE,
697 user_id,
698 SYSDATE,
699 0,
700 to_number(P_PLUGIN_ID),
701 to_number(P_MEDIA_TYPE_ID),
702 P_CONDITIONAL_FUNC
703 );
704 END;
705 END IF;
706 end Load_IEU_CLI_PROV_MED_MAPS;
707
708 --ieuwpsec.lct
709
710 PROCEDURE Load_IEU_WP_SECTIONS_B (
711 P_UPLOAD_MODE IN VARCHAR2,
712 P_SECTION_ID IN VARCHAR2,
713 P_SECTION_CODE IN VARCHAR2,
714 P_SECTION_LABEL IN VARCHAR2,
715 P_SECTION_DESCRIPTION IN VARCHAR2,
716 P_OWNER IN VARCHAR2
717 )is
718 user_id number := 0;
719 begin
720
721 --if (p_owner = 'SEED') then
722 -- user_id := 1;
723 --end if;
724
725 user_id := fnd_load_util.owner_id(P_OWNER);
726
727 if (p_upload_mode = 'NLS') then
728 update IEU_WP_SECTIONS_TL
729 set source_lang = userenv('LANG'),
730 object_version_number = object_version_number + 1,
731 section_label = p_section_label,
732 section_description = p_section_description,
733 last_update_date = sysdate,
734 last_updated_by = user_id,
735 last_update_login = 0
736 where (section_id = to_number(p_section_id))
737 and (userenv('LANG') IN (LANGUAGE, SOURCE_LANG));
738
739 else
740
741 begin
742
743 update IEU_WP_SECTIONS_B
744 set object_version_number = object_version_number + 1,
745 last_updated_by = user_id,
746 last_update_date = sysdate,
747 last_update_login = 0,
748 section_code = p_section_code
749 where section_id = to_number(p_section_id);
750
751 if (sql%notfound) then
752 raise no_data_found;
753 end if;
754
755 update IEU_WP_SECTIONS_TL
756 set object_version_number = object_version_number + 1,
757 last_updated_by = user_id,
758 last_update_date = sysdate,
759 last_update_login = 0,
760 section_label = p_section_label,
761 section_description = p_section_description,
762 source_lang = userenv('LANG')
763 where section_id = to_number(p_section_id)
764 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
765
766 if (sql%notfound) then
767 raise no_data_found;
768 end if;
769
770 exception when no_data_found then
771
772 insert into ieu_wp_sections_b
773 (section_id,
774 object_version_number,
775 created_by,
776 creation_date,
777 last_updated_by,
778 last_update_date,
779 last_update_login,
780 section_code)
781 values ( to_number(p_section_id),
782 1,
783 user_id,
784 sysdate,
785 user_id,
786 sysdate,
787 0,
788 p_section_code);
789
790 insert into ieu_wp_sections_tl
791 (section_id,
792 object_version_number,
793 created_by,
794 creation_date,
795 last_updated_by,
796 last_update_date,
797 last_update_login,
798 section_label,
799 section_description,
800 language,
801 source_lang)
802 select to_number(p_section_id),
803 1,
804 user_id,
805 sysdate,
806 user_id,
807 sysdate,
808 0,
809 p_section_label,
810 p_section_description,
811 l.language_code,
812 userenv('LANG')
813 from fnd_languages l
814 where l.installed_flag in ('I', 'B')
815 and not exists
816 (select null from ieu_wp_sections_tl t
817 where t.section_id = to_number(p_section_id)
818 and t.language = l.language_code);
819 end;
820 end if;
821
822 end Load_IEU_WP_SECTIONS_B;
823
824 --ieumact.lct
825
826 PROCEDURE Load_WORK_PANEL_ACTION_PARAMS (
827 P_UPLOAD_MODE IN VARCHAR2,
828 P_MACTION_DEF_ID IN VARCHAR2,
829 P_ACTION_PARAM_MAP_ID IN NUMBER,
830 P_ACTION_PARAM_ID IN NUMBER,
831 P_NOT_VALID_FLAG IN VARCHAR2,
832 P_LAST_UPDATE_DATE IN VARCHAR2,
833 P_OWNER IN VARCHAR2
834 )is
835 user_id number := FND_GLOBAL.USER_ID;
836 created_id number := FND_GLOBAL.USER_ID;
837 begin
838
839 if (p_upload_mode = 'NLS') then
840 null;
841 else
842 begin
843
844 --if (P_OWNER = 'ORACLE' or P_OWNER = 'SEED') then
845 -- user_id := 1;
846 -- created_id := -1;
847 --end if;
848
849 user_id := fnd_load_util.owner_id(P_OWNER);
850
851 update ieu_wp_action_params
852 set LAST_UPDATED_BY = user_id,
853 LAST_UPDATE_DATE = decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
854 LAST_UPDATE_LOGIN = 0,
855 WP_ACTION_DEF_ID = P_MACTION_DEF_ID,
856 PARAM_ID = P_ACTION_PARAM_ID,
857 NOT_VALID_FLAG = P_NOT_VALID_FLAG,
858 OBJECT_VERSION_NUMBER = nvl(object_version_number,0) +1
859 where action_param_map_id = P_ACTION_PARAM_MAP_ID;
860
861 if sql%notfound then
862 raise NO_DATA_FOUND;
863 end if;
864
865 exception
866
867 when NO_DATA_FOUND then
868
869 insert into ieu_wp_action_params
870 ( action_param_map_id ,
871 CREATED_BY,
872 CREATION_DATE,
873 LAST_UPDATED_BY,
874 LAST_UPDATE_DATE,
875 LAST_UPDATE_LOGIN,
876 PARAM_ID,
877 WP_ACTION_DEF_ID,
878 NOT_VALID_FLAG,
879 OBJECT_VERSION_NUMBER)
880 values( P_ACTION_PARAM_MAP_ID,
881 --created_id,
882 user_id,
883 SYSDATE,
884 user_id,
885 decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
886 FND_GLOBAL.LOGIN_ID,
887 P_ACTION_PARAM_ID,
888 P_MACTION_DEF_ID,
889 P_NOT_VALID_FLAG,
890 1);
891 end;
892 end if;
893 end Load_WORK_PANEL_ACTION_PARAMS;
894
895 PROCEDURE Load_NON_MEDIA_ACTIONS (
896 P_UPLOAD_MODE IN VARCHAR2,
897 P_NONMEDIA_ACTION_ID IN NUMBER,
898 P_ACTION_OBJECT_CODE IN VARCHAR2,
899 P_MEDIA_MACTION_DEF_ID IN VARCHAR2,
900 P_APPLICATION_ID IN NUMBER,
901 P_SOURCE_FOR_TASK_FLAG IN varchar2,
902 P_RESPONSIBILITY_ID IN NUMBER,
903 P_LAST_UPDATE_DATE IN VARCHAR2,
904 P_OWNER IN VARCHAR2
905 )is
906 user_id number := FND_GLOBAL.USER_ID;
907 created_id number := FND_GLOBAL.USER_ID;
908 begin
909
910 if (p_upload_mode = 'NLS') then
911 null;
912 else
913 begin
914 --if (P_OWNER = 'ORACLE' or P_OWNER = 'SEED') then
915 -- user_id := 1;
916 -- created_id := -1;
917 --end if;
918
919 user_id := fnd_load_util.owner_id(P_OWNER);
920
921 update IEU_UWQ_NONMEDIA_ACTIONS
922 set LAST_UPDATED_BY = user_id,
923 LAST_UPDATE_DATE = decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
924 LAST_UPDATE_LOGIN = 0,
925 ACTION_OBJECT_CODE = P_ACTION_OBJECT_CODE,
926 MACTION_DEF_ID = P_MEDIA_MACTION_DEF_ID,
927 APPLICATION_ID = P_APPLICATION_ID,
928 SOURCE_FOR_TASK_FLAG = P_SOURCE_FOR_TASK_FLAG,
929 RESPONSIBILITY_ID = P_RESPONSIBILITY_ID,
930 OBJECT_VERSION_NUMBER = nvl(object_version_number,0) +1
931 where nonmedia_action_id = P_NONMEDIA_ACTION_ID;
932
933 if sql%notfound then
934 raise NO_DATA_FOUND;
935 end if;
936
937 exception
938 when NO_DATA_FOUND then
939
940 insert into IEU_UWQ_NONMEDIA_ACTIONS (
941 NONMEDIA_ACTION_ID,
942 CREATED_BY,
943 CREATION_DATE,
944 LAST_UPDATED_BY,
945 LAST_UPDATE_DATE,
946 LAST_UPDATE_LOGIN,
947 ACTION_OBJECT_CODE,
948 MACTION_DEF_ID,
949 APPLICATION_ID,
950 SOURCE_FOR_TASK_FLAG,
951 RESPONSIBILITY_ID,
952 OBJECT_VERSION_NUMBER)
953 values( P_NONMEDIA_ACTION_ID,
954 --created_id,
955 user_id,
956 SYSDATE,
957 user_id,
958 decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
959 FND_GLOBAL.LOGIN_ID,
960 P_ACTION_OBJECT_CODE,
961 P_MEDIA_MACTION_DEF_ID,
962 P_APPLICATION_ID,
963 P_SOURCE_FOR_TASK_FLAG,
964 P_RESPONSIBILITY_ID,
965 1);
966 end;
967 end if;
968 end Load_NON_MEDIA_ACTIONS;
969
970 PROCEDURE Load_WP_ACTION_MAPS (
971 P_UPLOAD_MODE IN VARCHAR2,
972 P_ACTION_PARAM_SET_ID IN NUMBER,
973 P_WP_ACTION_MAP_ID IN NUMBER,
974 P_RESPONSIBILITY_ID IN NUMBER,
975 P_ACTION_MAP_TYPE_CODE IN VARCHAR2,
976 P_ACTION_MAP_CODE IN VARCHAR2,
977 P_ACTION_MAP_SEQUENCE IN NUMBER,
978 P_PANEL_SEC_CAT_CODE IN VARCHAR2,
979 P_APPLICATION_ID IN NUMBER,
980 P_NOT_VALID_FLAG IN VARCHAR2,
981 P_DEV_DATA_FLAG IN VARCHAR2,
982 P_LAST_UPDATE_DATE IN VARCHAR2,
983 P_OWNER IN VARCHAR2
984 )is
985 x_return_status VARCHAR2(2000);
986 x_msg_count NUMBER:=0;
987 x_msg_data VARCHAR2(2000);
988 user_id number := FND_GLOBAL.USER_ID;
989 created_id number := FND_GLOBAL.USER_ID;
990 l_temp_map_sequence ieu_wp_action_maps.action_map_sequence%type;
991 l_section_id ieu_wp_sections_b.section_id%type;
992 l_wp_node_section_map_id ieu_wp_node_section_maps.wp_node_section_map_id%type;
993 l_sequence number:=0;
994 l_def_flag ieu_uwq_maction_defs_b.maction_def_type_flag%type;
995 l_last_updated_by ieu_wp_action_maps.last_updated_by%type;
996 begin
997 --call UpdateParamProps to make sure all actions have all properties which they should have
998 IEU_WP_ACTION_PVT.UpdateParamProps(x_return_status ,x_msg_count ,x_msg_data, P_APPLICATION_ID );
999
1000 if (p_upload_mode = 'NLS') then
1001 null;
1002 else
1003 begin
1004 --if (P_OWNER = 'ORACLE' or P_OWNER = 'SEED') then
1005 -- user_id := 1;
1006 -- created_id := -1;
1007 --end if;
1008
1009 user_id := fnd_load_util.owner_id(P_OWNER);
1010
1011 select last_updated_by into l_last_updated_by
1012 from ieu_wp_action_maps
1013 where wp_action_map_id = P_WP_ACTION_MAP_ID;
1014
1015 -- dolee added on 2-03-03
1016 -- a. when upload seed data, don't change customerized sequence number
1017 -- b. if there are new seed data, give max+1 sequence number.
1018
1019 --a.
1020 if (l_last_updated_by = user_id) then
1021 update ieu_wp_action_maps
1022 set
1023 LAST_UPDATED_BY = user_id,
1024 LAST_UPDATE_DATE = decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
1025 LAST_UPDATE_LOGIN = 0,
1026 APPLICATION_ID = P_APPLICATION_ID,
1027 OBJECT_VERSION_NUMBER = nvl(object_version_number,0) +1,
1028 action_param_set_id = P_ACTION_PARAM_SET_ID,
1029 RESPONSIBILITY_ID = P_RESPONSIBILITY_ID,
1030 action_map_type_code = P_ACTION_MAP_TYPE_CODE,
1031 action_map_code = P_ACTION_MAP_CODE,
1032 action_map_sequence = P_ACTION_MAP_SEQUENCE,
1033 PANEL_SEC_CAT_CODE = P_PANEL_SEC_CAT_CODE,
1034 NOT_VALID_FLAG = P_NOT_VALID_FLAG,
1035 DEV_DATA_FLAG = P_DEV_DATA_FLAG
1036 where wp_action_map_id = P_WP_ACTION_MAP_ID;
1037 else
1038 update ieu_wp_action_maps
1039 set
1040 LAST_UPDATED_BY = user_id,
1041 LAST_UPDATE_DATE = decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
1042 LAST_UPDATE_LOGIN = 0,
1043 APPLICATION_ID = P_APPLICATION_ID,
1044 OBJECT_VERSION_NUMBER = nvl(object_version_number,0) +1,
1045 action_param_set_id = P_ACTION_PARAM_SET_ID,
1046 RESPONSIBILITY_ID = P_RESPONSIBILITY_ID,
1047 action_map_type_code = P_ACTION_MAP_TYPE_CODE,
1048 action_map_code = P_ACTION_MAP_CODE,
1049 --action_map_sequence = P_ACTION_MAP_SEQUENCE,
1050 PANEL_SEC_CAT_CODE = P_PANEL_SEC_CAT_CODE,
1051 --NOT_VALID_FLAG = P_NOT_VALID_FLAG,
1052 DEV_DATA_FLAG = P_DEV_DATA_FLAG
1053 where wp_action_map_id = P_WP_ACTION_MAP_ID;
1054 end if;
1055
1056 if sql%notfound then
1057 raise NO_DATA_FOUND;
1058 end if;
1059
1060 exception
1061 when NO_DATA_FOUND then
1062 begin
1063
1064 -- b.
1065 select max(action_map_sequence) into l_temp_map_sequence
1066 from ieu_wp_action_maps
1067 where application_id = P_APPLICATION_ID
1068 and action_map_type_code = P_ACTION_MAP_TYPE_CODE
1069 and action_map_code = P_ACTION_MAP_CODE;
1070
1071 if l_temp_map_sequence is null then
1072 l_temp_map_sequence :=1;
1073 else
1074 l_temp_map_sequence := l_temp_map_sequence + 1;
1075 end if;
1076
1077 insert into ieu_wp_action_maps
1078 ( wp_action_map_id ,
1079 CREATED_BY,
1080 CREATION_DATE,
1081 LAST_UPDATED_BY,
1082 LAST_UPDATE_DATE,
1083 LAST_UPDATE_LOGIN,
1084 action_param_set_id,
1085 RESPONSIBILITY_ID,
1086 APPLICATION_ID,
1087 action_map_type_code,
1088 action_map_code,
1089 action_map_sequence,
1090 PANEL_SEC_CAT_CODE,
1091 NOT_VALID_FLAG,
1092 DEV_DATA_FLAG,
1093 OBJECT_VERSION_NUMBER)
1094 values
1095 ( P_WP_ACTION_MAP_ID,
1096 --created_id,
1097 user_id,
1098 SYSDATE,
1099 user_id,
1100 decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
1101 FND_GLOBAL.LOGIN_ID,
1102 P_ACTION_PARAM_SET_ID,
1103 P_RESPONSIBILITY_ID,
1104 P_APPLICATION_ID,
1105 P_ACTION_MAP_TYPE_CODE,
1106 P_ACTION_MAP_CODE,
1107 l_temp_map_sequence,
1108 P_PANEL_SEC_CAT_CODE,
1109 P_NOT_VALID_FLAG,
1110 P_DEV_DATA_FLAG,
1111 1
1112 );
1113 end ;
1114 --for outer exception
1115
1116 select MACTION_DEF_TYPE_FLAG into l_def_flag
1117 from ieu_uwq_maction_defs_b
1118 where
1119 maction_def_id in
1120 (select wp_action_def_id
1121 from ieu_wp_act_param_sets_b
1122 where action_param_set_id = P_ACTION_PARAM_SET_ID);
1123
1124 if l_def_flag = 'W' then
1125 l_section_id := 10002;
1126 l_sequence := 2;
1127 elsif l_def_flag = 'I' then
1128 l_section_id := 10001;
1129 l_sequence :=1;
1130 end if;
1131
1132 if l_section_id is not null then
1133 begin
1134 update ieu_wp_node_section_maps
1135 set LAST_UPDATED_BY = user_id,
1136 LAST_UPDATE_DATE = decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
1137 LAST_UPDATE_LOGIN = 0,
1138 OBJECT_VERSION_NUMBER = nvl(object_version_number,0) +1
1139 where
1140 enum_type_uuid = P_ACTION_MAP_CODE
1141 and section_id = l_section_id;
1142
1143
1144 if sql%notfound then
1145 raise NO_DATA_FOUND;
1146 end if;
1147
1148 exception
1149 when NO_DATA_FOUND then
1150
1151 select IEU_WP_NODE_SECTION_MAPS_S1.NEXTVAL into l_wp_node_section_map_id from sys.dual;
1152 insert into ieu_wp_node_section_maps
1153 ( wp_node_section_map_id ,
1154 CREATED_BY,
1155 CREATION_DATE,
1156 LAST_UPDATED_BY,
1157 LAST_UPDATE_DATE,
1158 LAST_UPDATE_LOGIN,
1159 APPLICATION_ID,
1160 enum_type_uuid,
1161 section_id,
1162 section_map_sequence,
1163 OBJECT_VERSION_NUMBER)
1164 values
1165 ( l_wp_node_section_map_id,
1166 --created_id,
1167 user_id,
1168 sysdate,
1169 user_id,
1170 decode(P_LAST_UPDATE_DATE, null,SYSDATE,to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD')),
1171 FND_GLOBAL.LOGIN_ID,
1172 P_APPLICATION_ID,
1173 P_ACTION_MAP_CODE,
1174 l_section_id,
1175 l_sequence,
1176 1);
1177 end;
1178 end if;
1179 end;
1180 end if;
1181 end Load_WP_ACTION_MAPS;
1182
1183
1184 END IEU_SEED_DATA_LOADER_PKG;