[Home] [Help]
PACKAGE BODY: APPS.UMX_REG_SERVICES_PKG
Source
1 package body UMX_REG_SERVICES_PKG as
2 /* $Header: UMXRGSVB.pls 120.3.12000000.2 2007/04/10 04:56:23 vimohan ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_REG_SERVICE_CODE in VARCHAR2,
6 X_REG_SERVICE_TYPE in VARCHAR2,
7 X_WF_NOTIFICATION_EVENT_GUID in RAW,
8 X_EMAIL_VERIFICATION_FLAG in VARCHAR2,
9 X_APPLICATION_ID in NUMBER,
10 X_START_DATE in DATE,
11 X_SECURITY_GROUP_ID in NUMBER,
12 X_END_DATE in DATE,
13 X_WF_ROLE_NAME in VARCHAR2,
14 X_REG_FUNCTION_ID in NUMBER,
15 X_AME_APPLICATION_ID in NUMBER,
16 X_AME_TRANSACTION_TYPE_ID in VARCHAR2,
17 X_DISPLAY_NAME in VARCHAR2,
18 X_DESCRIPTION in VARCHAR2,
19 X_USAGE in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER,
25 X_WF_BUS_LOGIC_EVENT_GUID in RAW
26 ) is
27 cursor C is select ROWID from UMX_REG_SERVICES_B
28 where REG_SERVICE_CODE = X_REG_SERVICE_CODE
29 ;
30 begin
31
32
33 insert into UMX_REG_SERVICES_B (
34 REG_SERVICE_CODE,
35 REG_SERVICE_TYPE,
36 WF_NOTIFICATION_EVENT_GUID,
37 EMAIL_VERIFICATION_FLAG,
38 APPLICATION_ID,
39 START_DATE,
40 SECURITY_GROUP_ID,
41 END_DATE,
42 WF_ROLE_NAME,
43 REG_FUNCTION_ID,
44 AME_APPLICATION_ID,
45 AME_TRANSACTION_TYPE_ID,
46 WF_BUS_LOGIC_EVENT_GUID,
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_LOGIN
52 ) values (
53 X_REG_SERVICE_CODE,
54 X_REG_SERVICE_TYPE,
55 X_WF_NOTIFICATION_EVENT_GUID,
56 nvl(X_EMAIL_VERIFICATION_FLAG,'N'),
57 X_APPLICATION_ID,
58 X_START_DATE,
59 X_SECURITY_GROUP_ID,
60 X_END_DATE,
61 X_WF_ROLE_NAME,
62 X_REG_FUNCTION_ID,
63 X_AME_APPLICATION_ID,
64 X_AME_TRANSACTION_TYPE_ID,
65 X_WF_BUS_LOGIC_EVENT_GUID,
66 X_CREATION_DATE,
67 X_CREATED_BY,
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_LOGIN
71 );
72
73 insert into UMX_REG_SERVICES_TL (
74 REG_SERVICE_CODE,
75 DISPLAY_NAME,
76 DESCRIPTION,
77 USAGE,
78 CREATION_DATE,
79 CREATED_BY,
80 LAST_UPDATE_DATE,
81 LAST_UPDATED_BY,
82 LAST_UPDATE_LOGIN,
83 SECURITY_GROUP_ID,
84 LANGUAGE,
85 SOURCE_LANG
86 ) select
87 X_REG_SERVICE_CODE,
88 X_DISPLAY_NAME,
89 X_DESCRIPTION,
90 X_USAGE,
91 X_CREATION_DATE,
92 X_CREATED_BY,
93 X_LAST_UPDATE_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_LOGIN,
96 X_SECURITY_GROUP_ID,
97 L.LANGUAGE_CODE,
98 userenv('LANG')
99 from FND_LANGUAGES L
100 where L.INSTALLED_FLAG in ('I', 'B')
101 and not exists
102 (select NULL
103 from UMX_REG_SERVICES_TL T
104 where T.REG_SERVICE_CODE = X_REG_SERVICE_CODE
105 and T.LANGUAGE = L.LANGUAGE_CODE);
106
107 open c;
108 fetch c into X_ROWID;
109 if (c%notfound) then
110 close c;
111 raise no_data_found;
112 end if;
113 close c;
114
115 end INSERT_ROW;
116
117 procedure LOCK_ROW (
118 X_REG_SERVICE_CODE in VARCHAR2,
119 X_REG_SERVICE_TYPE in VARCHAR2,
120 X_WF_NOTIFICATION_EVENT_GUID in RAW,
121 X_EMAIL_VERIFICATION_FLAG in VARCHAR2,
122 X_APPLICATION_ID in NUMBER,
123 X_START_DATE in DATE,
124 X_SECURITY_GROUP_ID in NUMBER,
125 X_END_DATE in DATE,
126 X_WF_ROLE_NAME in VARCHAR2,
127 X_REG_FUNCTION_ID in NUMBER,
128 X_AME_APPLICATION_ID in NUMBER,
129 X_AME_TRANSACTION_TYPE_ID in VARCHAR2,
130 X_DISPLAY_NAME in VARCHAR2,
131 X_DESCRIPTION in VARCHAR2,
132 X_USAGE in VARCHAR2,
133 X_WF_BUS_LOGIC_EVENT_GUID in RAW
134 ) is
135 cursor c is select
136 REG_SERVICE_TYPE,
137 WF_NOTIFICATION_EVENT_GUID,
138 EMAIL_VERIFICATION_FLAG,
139 APPLICATION_ID,
140 START_DATE,
141 SECURITY_GROUP_ID,
142 END_DATE,
143 WF_ROLE_NAME,
144 REG_FUNCTION_ID,
145 AME_APPLICATION_ID,
146 AME_TRANSACTION_TYPE_ID,
147 WF_BUS_LOGIC_EVENT_GUID
148 from UMX_REG_SERVICES_B
149 where REG_SERVICE_CODE = X_REG_SERVICE_CODE
150 for update of REG_SERVICE_CODE nowait;
151 recinfo c%rowtype;
152
153 cursor c1 is select
154 DISPLAY_NAME,
155 DESCRIPTION,
156 USAGE,
157 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
158 from UMX_REG_SERVICES_TL
159 where REG_SERVICE_CODE = X_REG_SERVICE_CODE
160 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
161 for update of REG_SERVICE_CODE nowait;
162 begin
163 open c;
164 fetch c into recinfo;
165 if (c%notfound) then
166 close c;
167 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
168 app_exception.raise_exception;
169 end if;
170 close c;
171 if ( (recinfo.REG_SERVICE_TYPE = X_REG_SERVICE_TYPE)
172 AND (recinfo.WF_NOTIFICATION_EVENT_GUID = X_WF_NOTIFICATION_EVENT_GUID)
173 AND (recinfo.EMAIL_VERIFICATION_FLAG = X_EMAIL_VERIFICATION_FLAG)
174 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
175 AND (recinfo.START_DATE = X_START_DATE)
176 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
177 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
178 AND ((recinfo.END_DATE = X_END_DATE)
179 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
180 AND ((recinfo.WF_ROLE_NAME = X_WF_ROLE_NAME)
181 OR ((recinfo.WF_ROLE_NAME is null) AND (X_WF_ROLE_NAME is null)))
182 AND ((recinfo.REG_FUNCTION_ID = X_REG_FUNCTION_ID)
183 OR ((recinfo.REG_FUNCTION_ID is null) AND (X_REG_FUNCTION_ID is null)))
184 AND ((recinfo.AME_APPLICATION_ID = X_AME_APPLICATION_ID)
185 OR ((recinfo.AME_APPLICATION_ID is null) AND (X_AME_APPLICATION_ID is null)))
186 AND ((recinfo.AME_TRANSACTION_TYPE_ID = X_AME_TRANSACTION_TYPE_ID)
187 OR ((recinfo.AME_TRANSACTION_TYPE_ID is null) AND (X_AME_TRANSACTION_TYPE_ID is null)))
188 AND ((recinfo.WF_BUS_LOGIC_EVENT_GUID = X_WF_BUS_LOGIC_EVENT_GUID)
189 OR ((recinfo.WF_BUS_LOGIC_EVENT_GUID is null) AND (X_WF_BUS_LOGIC_EVENT_GUID is null)))
190 ) then
191 null;
192 else
193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
194 app_exception.raise_exception;
195 end if;
196
197 for tlinfo in c1 loop
198 if (tlinfo.BASELANG = 'Y') then
199 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
200 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
201 AND ((tlinfo.USAGE = X_USAGE)
202 OR ((tlinfo.USAGE is null) AND (X_USAGE is null)))
203 ) then
204 null;
205 else
206 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
207 app_exception.raise_exception;
208 end if;
209 end if;
210 end loop;
211 return;
212 end LOCK_ROW;
213
214 procedure UPDATE_ROW (
215 X_REG_SERVICE_CODE in VARCHAR2,
216 X_REG_SERVICE_TYPE in VARCHAR2,
217 X_WF_NOTIFICATION_EVENT_GUID in RAW,
218 X_EMAIL_VERIFICATION_FLAG in VARCHAR2,
219 X_APPLICATION_ID in NUMBER,
220 X_START_DATE in DATE,
221 X_SECURITY_GROUP_ID in NUMBER,
222 X_END_DATE in DATE,
223 X_WF_ROLE_NAME in VARCHAR2,
224 X_REG_FUNCTION_ID in NUMBER,
225 X_AME_APPLICATION_ID in NUMBER,
226 X_AME_TRANSACTION_TYPE_ID in VARCHAR2,
227 X_DISPLAY_NAME in VARCHAR2,
228 X_DESCRIPTION in VARCHAR2,
229 X_USAGE in VARCHAR2,
230 X_LAST_UPDATE_DATE in DATE,
231 X_LAST_UPDATED_BY in NUMBER,
232 X_LAST_UPDATE_LOGIN in NUMBER,
233 X_WF_BUS_LOGIC_EVENT_GUID in RAW
234 ) is
235 begin
236 update UMX_REG_SERVICES_B set
237 REG_SERVICE_TYPE = X_REG_SERVICE_TYPE,
238 WF_NOTIFICATION_EVENT_GUID = X_WF_NOTIFICATION_EVENT_GUID,
239 EMAIL_VERIFICATION_FLAG = X_EMAIL_VERIFICATION_FLAG,
240 APPLICATION_ID = X_APPLICATION_ID,
241 START_DATE = X_START_DATE,
242 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
243 END_DATE = X_END_DATE,
244 WF_ROLE_NAME = X_WF_ROLE_NAME,
245 REG_FUNCTION_ID = X_REG_FUNCTION_ID,
246 AME_APPLICATION_ID = X_AME_APPLICATION_ID,
247 AME_TRANSACTION_TYPE_ID = X_AME_TRANSACTION_TYPE_ID,
248 WF_BUS_LOGIC_EVENT_GUID = X_WF_BUS_LOGIC_EVENT_GUID ,
249 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
250 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
251 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
252 where REG_SERVICE_CODE = X_REG_SERVICE_CODE;
253
254 if (sql%notfound) then
255 raise no_data_found;
256 end if;
257
258 update UMX_REG_SERVICES_TL set
259 DISPLAY_NAME = X_DISPLAY_NAME,
260 DESCRIPTION = X_DESCRIPTION,
261 USAGE = X_USAGE,
262 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
263 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
264 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
265 SOURCE_LANG = userenv('LANG')
266 where REG_SERVICE_CODE = X_REG_SERVICE_CODE
267 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
268
269 if (sql%notfound) then
270 raise no_data_found;
271 end if;
272 end UPDATE_ROW;
273
274 procedure DELETE_ROW (
275 X_REG_SERVICE_CODE in VARCHAR2
276 ) is
277 begin
278 delete from UMX_REG_SERVICES_TL
279 where REG_SERVICE_CODE = X_REG_SERVICE_CODE;
280
281 if (sql%notfound) then
282 raise no_data_found;
283 end if;
284
285 delete from UMX_REG_SERVICES_B
286 where REG_SERVICE_CODE = X_REG_SERVICE_CODE;
287
288 if (sql%notfound) then
289 raise no_data_found;
290 end if;
291 end DELETE_ROW;
292
293 procedure ADD_LANGUAGE
294 is
295 begin
296 delete from UMX_REG_SERVICES_TL T
297 where not exists
298 (select NULL
299 from UMX_REG_SERVICES_B B
300 where B.REG_SERVICE_CODE = T.REG_SERVICE_CODE
301 );
302
303 update UMX_REG_SERVICES_TL T set (
304 DISPLAY_NAME,
305 DESCRIPTION,
306 USAGE
307 ) = (select
308 B.DISPLAY_NAME,
309 B.DESCRIPTION,
310 B.USAGE
311 from UMX_REG_SERVICES_TL B
312 where B.REG_SERVICE_CODE = T.REG_SERVICE_CODE
313 and B.LANGUAGE = T.SOURCE_LANG)
314 where (
315 T.REG_SERVICE_CODE,
316 T.LANGUAGE
317 ) in (select
318 SUBT.REG_SERVICE_CODE,
319 SUBT.LANGUAGE
320 from UMX_REG_SERVICES_TL SUBB, UMX_REG_SERVICES_TL SUBT
321 where SUBB.REG_SERVICE_CODE = SUBT.REG_SERVICE_CODE
322 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
323 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
324 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
325 or SUBB.USAGE <> SUBT.USAGE
326 ));
327
328 insert into UMX_REG_SERVICES_TL (
329 REG_SERVICE_CODE,
330 DISPLAY_NAME,
331 DESCRIPTION,
332 USAGE,
333 CREATION_DATE,
334 CREATED_BY,
335 LAST_UPDATE_DATE,
336 LAST_UPDATED_BY,
337 LAST_UPDATE_LOGIN,
338 SECURITY_GROUP_ID,
339 LANGUAGE,
340 SOURCE_LANG
341 ) select
342 B.REG_SERVICE_CODE,
343 B.DISPLAY_NAME,
344 B.DESCRIPTION,
345 B.USAGE,
346 B.CREATION_DATE,
347 B.CREATED_BY,
348 B.LAST_UPDATE_DATE,
349 B.LAST_UPDATED_BY,
350 B.LAST_UPDATE_LOGIN,
351 B.SECURITY_GROUP_ID,
352 L.LANGUAGE_CODE,
353 B.SOURCE_LANG
354 from UMX_REG_SERVICES_TL B, FND_LANGUAGES L
355 where L.INSTALLED_FLAG in ('I', 'B')
356 and B.LANGUAGE = userenv('LANG')
357 and not exists
358 (select NULL
359 from UMX_REG_SERVICES_TL T
360 where T.REG_SERVICE_CODE = B.REG_SERVICE_CODE
361 and T.LANGUAGE = L.LANGUAGE_CODE);
362 end ADD_LANGUAGE;
363
364
365 Procedure LOAD_ROW(
366 X_REG_SERVICE_CODE in VARCHAR2,
367 X_REG_SERVICE_TYPE in VARCHAR2,
368 X_WF_NOTIFICATION_EVENT_GUID in VARCHAR2,
369 X_EMAIL_VERIFICATION_FLAG in VARCHAR2,
370 X_APP_SHORT_NAME in VARCHAR2,
371 X_START_DATE in VARCHAR2,
372 X_END_DATE in VARCHAR2,
373 X_WF_ROLE_NAME in VARCHAR2,
374 X_REG_FUNCTION_NAME in VARCHAR2,
375 X_AME_APP_SHORT_NAME in VARCHAR2,
376 X_AME_TRANSACTION_TYPE_ID in VARCHAR2,
377 X_LAST_UPDATE_DATE in VARCHAR2,
378 X_DISPLAY_NAME in VARCHAR2,
379 X_DESCRIPTION in VARCHAR2,
380 X_USAGE in VARCHAR2,
381 X_OWNER in VARCHAR2,
382 X_CUSTOM_MODE in VARCHAR2,
383 X_WF_BUS_LOGIC_EVENT_GUID in VARCHAR2
384
385 ) IS
386 app_id number;
387 ame_app_id number;
388 row_id varchar2(64);
389 f_luby NUMBER;
390 f_ludate date; -- entity update date in file
391 db_luby number; -- entity owner in db
392 db_ludate date; -- entity update date in db
393
394 l_ntf_guid_raw WF_EVENTS.guid%type;
395 l_BUS_LOGIC_guid_raw WF_EVENTS.guid%type;
396 l_event_name WF_EVENTS.name%type;
397 l_wf_role_name wf_local_roles.name%type;
398 l_reg_function_id fnd_form_functions.function_id%type;
399 l_transaction_type_id AME_TRANSACTION_TYPES_V.transaction_type_id%type;
400
401 l_start_date date;
402 l_end_date date;
403
404 CURSOR regfunction is
405 select function_id
406 from fnd_form_functions
407 where function_name = X_REG_FUNCTION_NAME;
408
409 CURSOR roleName is
410 select name from WF_LOCAL_ROLES
411 where name = X_WF_ROLE_NAME;
412
413 CURSOR eventName(x_guid_raw in RAW) is
414 select name
418 CURSOR ame is
415 from wf_events
416 where guid = HEXTORAW(x_guid_raw);
417
419 select ame.TRANSACTION_TYPE_ID, fa.APPLICATION_ID
420 from AME_TRANSACTION_TYPES_V ame, fnd_application fa
421 where nvl(END_DATE,SYSDATE+1) > SYSDATE
422 and fa.application_short_name = X_AME_APP_SHORT_NAME
423 and ame.TRANSACTION_TYPE_ID = X_AME_TRANSACTION_TYPE_ID;
424
425 CURSOR application is
426 select application_id
427 from fnd_application
428 where application_short_name = X_APP_SHORT_NAME;
429
430 BEGIN
431
432 -- Translate owner to file_last_updated_by
433 f_luby := fnd_load_util.owner_id(x_owner);
434
435 -- Translate char last_update_date to date
436 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
437
438
439
440 --validate ame,event_guid,reg_function_name, role_name
441 --convert date or default to sysdate
442
443 --application id
444 if(X_APP_SHORT_NAME is not null) then
445 open application;
446 fetch application into app_id;
447
448 if(application%notfound) then
449 close application;
450 raise_application_error(-20001,'Upload failed, illegal appname for:'||X_REG_SERVICE_CODE);
451 else
452 close application;
453 end if;
454 end if;
455
456 -- notification event guid validation
457 if (X_WF_NOTIFICATION_EVENT_GUID is not null) then
458 l_ntf_guid_raw := hextoraw(X_WF_NOTIFICATION_EVENT_GUID);
459
460 open eventName(l_ntf_guid_raw);
461 fetch eventName into l_event_name;
462 if (eventName%notfound) then
463 close eventName;
464 raise_application_error(-20001,'Upload failed, illegal notficationguid for:'||X_REG_SERVICE_CODE|| '. Make sure that the event exists in the target schema. If error persists download the ldt file using the latest lct file');
465 else
466 close eventName;
467 end if;
468
469 end if;
470 -- BUS_LOGIC event guid validation
471 if (X_WF_BUS_LOGIC_EVENT_GUID is not null) then
472 l_BUS_LOGIC_guid_raw := hextoraw(X_WF_BUS_LOGIC_EVENT_GUID);
473
474 open eventName(l_BUS_LOGIC_guid_raw);
475 fetch eventName into l_event_name;
476 if (eventName%notfound) then
477 close eventName;
478 raise_application_error(-20001,'Upload failed, illegal BUS_LOGICguid for:'||X_REG_SERVICE_CODE || '. Make sure that the event exists in the target schema. If error persists download the ldt file using the latest lct file');
479 else
480 close eventName;
481 end if;
482
483 end if;
484
485
486 -- role name validation
487 if(X_WF_ROLE_NAME is NOT NULL) then
488 open roleName;
489 fetch roleName into L_WF_ROLE_NAME;
490
491 if (roleName%notfound) then
492 close roleName;
493 raise_application_error(-20001,'Upload failed,illegal rolename for:'||X_REG_SERVICE_CODE);
494 else
495 close roleName;
496 end if;
497
498 end if;
499
500 -- reg function validation
501 if(X_REG_FUNCTION_NAME IS NOT NULL) then
502 open regFunction;
503 fetch regFunction into l_reg_function_id;
504
505 if(regFunction%notfound) then
506 close regFunction;
507 raise_application_error(-20001,'Upload failed,illegal formfunction for:'||X_REG_SERVICE_CODE ||'. Make sure that the function exists in the target schema');
508 else
509 close regFunction;
510 end if;
511
512 end if;
513
514 -- ame validation
515
516 if(X_AME_TRANSACTION_TYPE_ID IS NOT NULL and
517 X_AME_APP_SHORT_NAME IS NOT NULL) then
518
519 open ame;
520 fetch ame into l_transaction_type_id, ame_app_id;
521 if (ame%notfound) then
522 close ame;
523 raise_application_error(-20001,'Upload failed,illegal ame for:'||X_REG_SERVICE_CODE ||'. Make sure that the AME transaction type exists in the target schema');
524 else
525 close ame;
526 end if;
527
528 end if;
529
530 --start date and end_date conversion
531 if(X_START_DATE is not null) then
532 l_start_date := to_date(X_START_DATE, 'YYYY/MM/DD');
533 end if;
534
535 if(X_END_DATE is not null) then
536 l_end_date := to_date(X_END_DATE, 'YYYY/MM/DD');
537 end if;
538
539 --db last update date and updated by for this regsvc code
540 select last_updated_by, last_update_date
541 into db_luby, db_ludate
542 from umx_reg_services_b
543 where reg_service_code = X_REG_SERVICE_CODE;
544 -- test if this is a update and if it fails then create a new entry
545 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
546 db_ludate, X_CUSTOM_MODE)) then
547
548 UMX_REG_SERVICES_PKG.UPDATE_ROW(
549 X_REG_SERVICE_CODE => X_REG_SERVICE_CODE,
550 X_REG_SERVICE_TYPE => X_REG_SERVICE_TYPE,
551 X_WF_NOTIFICATION_EVENT_GUID => l_ntf_guid_raw,
552 X_EMAIL_VERIFICATION_FLAG => X_EMAIL_VERIFICATION_FLAG,
553 X_APPLICATION_ID => app_id,
554 X_START_DATE => l_start_date,
555 X_END_DATE => l_end_date,
556 X_WF_ROLE_NAME => X_WF_ROLE_NAME,
557 X_REG_FUNCTION_ID => l_reg_function_id,
558 X_AME_APPLICATION_ID => ame_app_id,
562 X_DESCRIPTION => X_DESCRIPTION,
559 X_AME_TRANSACTION_TYPE_ID => X_AME_TRANSACTION_TYPE_ID,
560 X_WF_BUS_LOGIC_EVENT_GUID => l_BUS_LOGIC_guid_raw,
561 X_DISPLAY_NAME => X_DISPLAY_NAME,
563 X_USAGE => X_USAGE,
564 X_LAST_UPDATE_DATE => f_ludate,
565 X_LAST_UPDATED_BY => f_luby,
566 X_LAST_UPDATE_LOGIN => 0
567 );
568 end if;
569
570 exception
571 when NO_DATA_FOUND then
572 UMX_REG_SERVICES_PKG.INSERT_ROW(
573 X_ROWID => row_id,
574 X_REG_SERVICE_CODE => X_REG_SERVICE_CODE,
575 X_REG_SERVICE_TYPE => X_REG_SERVICE_TYPE,
576 X_WF_NOTIFICATION_EVENT_GUID => l_ntf_guid_raw,
577 X_EMAIL_VERIFICATION_FLAG => X_EMAIL_VERIFICATION_FLAG,
578 X_APPLICATION_ID => app_id,
579 X_START_DATE => l_start_date,
580 X_END_DATE => l_end_date,
581 X_WF_ROLE_NAME => X_WF_ROLE_NAME,
582 X_REG_FUNCTION_ID => l_reg_function_id,
583 X_AME_APPLICATION_ID => ame_app_id,
584 X_AME_TRANSACTION_TYPE_ID => X_AME_TRANSACTION_TYPE_ID,
585 X_WF_BUS_LOGIC_EVENT_GUID => l_BUS_LOGIC_guid_raw,
586 X_DISPLAY_NAME =>X_DISPLAY_NAME,
587 X_DESCRIPTION => X_DESCRIPTION,
588 X_USAGE => X_USAGE,
589 X_CREATION_DATE => f_ludate,
590 X_CREATED_BY => f_luby,
591 X_LAST_UPDATE_DATE => f_ludate,
592 X_LAST_UPDATED_BY => f_luby,
593 X_LAST_UPDATE_LOGIN => 0
594 );
595
596 END LOAD_ROW;
597
598
599 Procedure TRANSLATE_ROW(
600 X_REG_SERVICE_CODE in VARCHAR2,
601 X_LAST_UPDATE_DATE in VARCHAR2,
602 X_DISPLAY_NAME in VARCHAR2,
603 X_DESCRIPTION in VARCHAR2,
604 X_USAGE in VARCHAR2,
605 X_OWNER in VARCHAR2,
606 X_CUSTOM_MODE in VARCHAR2
607 )IS
608 f_luby number;
609 f_ludate date; -- entity update date in file
610 db_luby number; -- entity owner in db
611 db_ludate date; -- entity update date in db
612 BEGIN
613
614 -- Translate owner to file_last_updated_by
615 f_luby := fnd_load_util.owner_id(x_owner);
616
617 -- Translate char last_update_date to date
618 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
619
620 select LAST_UPDATED_BY, LAST_UPDATE_DATE
621 into db_luby, db_ludate
622 from umx_reg_services_tl
623 where reg_service_code = X_REG_SERVICE_CODE
624 and userenv('LANG') = LANGUAGE;
625
626 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
627 db_ludate, X_CUSTOM_MODE)) then
628
629 update umx_reg_services_tl
630 set
631 display_name = nvl(X_DISPLAY_NAME, display_name),
632 description = nvl(X_DESCRIPTION, description),
633 usage = nvl(X_USAGE,usage),
634 source_lang = userenv('LANG'),
635 last_update_date = f_ludate,
636 last_updated_by = f_luby,
637 last_update_login = 0
638 where reg_service_code = X_REG_SERVICE_CODE
639 and userenv('LANG') in (language, source_lang);
640
641 end if;
642
643
644 END TRANSLATE_ROW;
645
646 Procedure LOAD_ROW(
647 X_REG_SERVICE_CODE in VARCHAR2,
648 X_REG_SERVICE_TYPE in VARCHAR2,
649 X_WF_NOTIFICATION_EVENT_GUID in VARCHAR2,
650 X_EMAIL_VERIFICATION_FLAG in VARCHAR2,
651 X_APP_SHORT_NAME in VARCHAR2,
652 X_START_DATE in VARCHAR2,
653 X_END_DATE in VARCHAR2,
654 X_WF_ROLE_NAME in VARCHAR2,
655 X_REG_FUNCTION_NAME in VARCHAR2,
656 X_AME_APP_SHORT_NAME in VARCHAR2,
657 X_AME_TRANSACTION_TYPE_ID in VARCHAR2,
658 X_LAST_UPDATE_DATE in VARCHAR2,
659 X_DISPLAY_NAME in VARCHAR2,
660 X_DESCRIPTION in VARCHAR2,
661 X_USAGE in VARCHAR2,
662 X_OWNER in VARCHAR2,
663 X_CUSTOM_MODE in VARCHAR2,
664 X_WF_BUS_LOGIC_EVENT_GUID in VARCHAR2,
665 X_WF_NOTIFICATION_EVENT_NAME in VARCHAR2,
666 X_WF_BUS_LOGIC_EVENT_NAME in VARCHAR2
667
668 ) IS
669
670 cursor get_notification_guid is
671 select guid from wf_events
672 where name = X_WF_NOTIFICATION_EVENT_NAME;
673
674 cursor get_bus_guid is
675 select guid from wf_events
676 where name = X_WF_BUS_LOGIC_EVENT_NAME;
677
678 l_wf_notification_guid wf_events.guid%type;
679 l_wf_bus_logic_event_guid wf_events.guid%type;
680
681
682 begin
683
684 if X_WF_NOTIFICATION_EVENT_NAME is not null then
685 open get_notification_guid;
686 fetch get_notification_guid into l_wf_notification_guid;
687 close get_notification_guid;
688 else
689 l_wf_notification_guid := X_WF_NOTIFICATION_EVENT_GUID;
690 end if;
691
692 if X_WF_BUS_LOGIC_EVENT_NAME is not null then
693 open get_bus_guid;
694 fetch get_bus_guid into l_wf_bus_logic_event_guid;
695 close get_bus_guid;
696 else
697 l_wf_bus_logic_event_guid := X_WF_BUS_LOGIC_EVENT_GUID;
698 end if;
699
700
701 LOAD_ROW(
702 X_REG_SERVICE_CODE => X_REG_SERVICE_CODE,
703 X_REG_SERVICE_TYPE => X_REG_SERVICE_TYPE,
704 X_WF_NOTIFICATION_EVENT_GUID => l_wf_notification_guid,
705 X_EMAIL_VERIFICATION_FLAG => X_EMAIL_VERIFICATION_FLAG,
706 X_APP_SHORT_NAME => X_APP_SHORT_NAME,
707 X_START_DATE => X_START_DATE,
708 X_END_DATE => X_END_DATE,
709 X_WF_ROLE_NAME => X_WF_ROLE_NAME,
710 X_REG_FUNCTION_NAME => X_REG_FUNCTION_NAME,
711 X_AME_APP_SHORT_NAME => X_AME_APP_SHORT_NAME,
712 X_AME_TRANSACTION_TYPE_ID => X_AME_TRANSACTION_TYPE_ID,
713 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
714 X_DISPLAY_NAME => X_DISPLAY_NAME,
715 X_DESCRIPTION => X_DESCRIPTION,
716 X_USAGE => X_USAGE,
717 X_OWNER => X_OWNER,
718 X_CUSTOM_MODE => X_CUSTOM_MODE,
719 X_WF_BUS_LOGIC_EVENT_GUID => l_wf_bus_logic_event_guid
720 );
721
722 end load_row;
723
724 end UMX_REG_SERVICES_PKG;