[Home] [Help]
PACKAGE BODY: APPS.AMS_EVENT_HEADERS_ALL_PKG
Source
1 package body AMS_EVENT_HEADERS_ALL_PKG as
2 /* $Header: amslevhb.pls 115.4 2002/11/16 00:41:48 dbiswas ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_EVENT_HEADER_ID in NUMBER,
6 X_FUND_AMOUNT_TC in NUMBER,
7 X_FUND_AMOUNT_FC in NUMBER,
8 X_ATTRIBUTE4 in VARCHAR2,
9 X_USER_STATUS_ID in NUMBER,
10 X_ATTRIBUTE5 in VARCHAR2,
11 X_ATTRIBUTE6 in VARCHAR2,
12 X_ORGANIZATION_ID in NUMBER,
13 X_ATTRIBUTE7 in VARCHAR2,
14 X_ATTRIBUTE8 in VARCHAR2,
15 X_ATTRIBUTE9 in VARCHAR2,
16 X_ATTRIBUTE10 in VARCHAR2,
17 X_ATTRIBUTE11 in VARCHAR2,
18 X_ATTRIBUTE12 in VARCHAR2,
19 X_ATTRIBUTE13 in VARCHAR2,
20 X_ATTRIBUTE14 in VARCHAR2,
21 X_CURRENCY_CODE_TC in VARCHAR2,
22 X_CURRENCY_CODE_FC in VARCHAR2,
23 X_ATTRIBUTE15 in VARCHAR2,
24 X_EMAIL in VARCHAR2,
25 X_PHONE in VARCHAR2,
26 X_ACTUAL_COST in NUMBER,
27 X_COORDINATOR_ID in NUMBER,
28 X_FUND_SOURCE_TYPE_CODE in VARCHAR2,
29 X_FUND_SOURCE_ID in NUMBER,
30 X_OWNER_USER_ID in NUMBER,
31 X_URL in VARCHAR2,
32 X_PRIORITY_TYPE_CODE in VARCHAR2,
33 X_CANCELLATION_REASON_CODE in VARCHAR2,
34 X_INBOUND_SCRIPT_NAME in VARCHAR2,
35 X_ATTRIBUTE_CATEGORY in VARCHAR2,
36 X_ATTRIBUTE1 in VARCHAR2,
37 X_ATTRIBUTE2 in VARCHAR2,
38 X_ATTRIBUTE3 in VARCHAR2,
39 X_FORECASTED_COST in NUMBER,
40 X_OBJECT_VERSION_NUMBER in NUMBER,
41 X_EVENT_LEVEL in VARCHAR2,
42 X_APPLICATION_ID in NUMBER,
43 X_EVENT_TYPE_CODE in VARCHAR2,
44 X_ACTIVE_FLAG in VARCHAR2,
45 X_PRIVATE_FLAG in VARCHAR2,
46 X_SYSTEM_STATUS_CODE in VARCHAR2,
47 X_LAST_STATUS_DATE in DATE,
48 X_STREAM_TYPE_CODE in VARCHAR2,
49 X_SOURCE_CODE in VARCHAR2,
50 X_ACTUAL_REVENUE in NUMBER,
51 X_EVENT_STANDALONE_FLAG in VARCHAR2,
52 X_DAY_OF_EVENT in VARCHAR2,
53 X_AGENDA_START_TIME in DATE,
54 X_AGENDA_END_TIME in DATE,
55 X_REG_REQUIRED_FLAG in VARCHAR2,
56 X_REG_CHARGE_FLAG in VARCHAR2,
57 X_REG_INVITED_ONLY_FLAG in VARCHAR2,
58 X_PARTNER_FLAG in VARCHAR2,
59 X_OVERFLOW_FLAG in VARCHAR2,
60 X_PARENT_EVENT_HEADER_ID in NUMBER,
61 X_DURATION in NUMBER,
62 X_DURATION_UOM_CODE in VARCHAR2,
63 X_ACTIVE_FROM_DATE in DATE,
64 X_ACTIVE_TO_DATE in DATE,
65 X_REG_MAXIMUM_CAPACITY in NUMBER,
66 X_REG_MINIMUM_CAPACITY in NUMBER,
67 X_MAIN_LANGUAGE_CODE in VARCHAR2,
68 X_CERT_CREDIT_TYPE_CODE in VARCHAR2,
69 X_CERTIFICATION_CREDITS in NUMBER,
70 X_INVENTORY_ITEM_ID in NUMBER,
71 X_FORECASTED_REVENUE in NUMBER,
72 X_EVENT_HEADER_NAME in VARCHAR2,
73 X_EVENT_MKTG_MESSAGE in VARCHAR2,
74 X_DESCRIPTION in VARCHAR2,
75 X_CREATION_DATE in DATE,
76 X_CREATED_BY in NUMBER,
77 X_LAST_UPDATE_DATE in DATE,
78 X_LAST_UPDATED_BY in NUMBER,
79 X_LAST_UPDATE_LOGIN in NUMBER
80 ) is
81 cursor C is select ROWID from AMS_EVENT_HEADERS_ALL_B
82 where EVENT_HEADER_ID = X_EVENT_HEADER_ID
83 ;
84 begin
85 insert into AMS_EVENT_HEADERS_ALL_B (
86 FUND_AMOUNT_TC,
87 FUND_AMOUNT_FC,
88 ATTRIBUTE4,
89 USER_STATUS_ID,
90 ATTRIBUTE5,
91 ATTRIBUTE6,
92 ORGANIZATION_ID,
93 ATTRIBUTE7,
94 ATTRIBUTE8,
95 ATTRIBUTE9,
96 ATTRIBUTE10,
97 ATTRIBUTE11,
98 ATTRIBUTE12,
99 ATTRIBUTE13,
100 ATTRIBUTE14,
101 CURRENCY_CODE_TC,
102 CURRENCY_CODE_FC,
103 ATTRIBUTE15,
104 EMAIL,
105 PHONE,
106 ACTUAL_COST,
107 COORDINATOR_ID,
108 FUND_SOURCE_TYPE_CODE,
109 FUND_SOURCE_ID,
110 OWNER_USER_ID,
111 URL,
112 PRIORITY_TYPE_CODE,
113 CANCELLATION_REASON_CODE,
114 INBOUND_SCRIPT_NAME,
115 ATTRIBUTE_CATEGORY,
116 ATTRIBUTE1,
117 ATTRIBUTE2,
118 ATTRIBUTE3,
119 FORECASTED_COST,
120 EVENT_HEADER_ID,
121 OBJECT_VERSION_NUMBER,
122 EVENT_LEVEL,
123 APPLICATION_ID,
124 EVENT_TYPE_CODE,
125 ACTIVE_FLAG,
126 PRIVATE_FLAG,
127 SYSTEM_STATUS_CODE,
128 LAST_STATUS_DATE,
129 STREAM_TYPE_CODE,
130 SOURCE_CODE,
131 ACTUAL_REVENUE,
132 EVENT_STANDALONE_FLAG,
133 DAY_OF_EVENT,
134 AGENDA_START_TIME,
135 AGENDA_END_TIME,
136 REG_REQUIRED_FLAG,
137 REG_CHARGE_FLAG,
138 REG_INVITED_ONLY_FLAG,
139 PARTNER_FLAG,
140 OVERFLOW_FLAG,
141 PARENT_EVENT_HEADER_ID,
142 DURATION,
143 DURATION_UOM_CODE,
144 ACTIVE_FROM_DATE,
145 ACTIVE_TO_DATE,
146 REG_MAXIMUM_CAPACITY,
147 REG_MINIMUM_CAPACITY,
148 MAIN_LANGUAGE_CODE,
149 CERT_CREDIT_TYPE_CODE,
150 CERTIFICATION_CREDITS,
151 INVENTORY_ITEM_ID,
152 FORECASTED_REVENUE,
153 CREATION_DATE,
154 CREATED_BY,
155 LAST_UPDATE_DATE,
156 LAST_UPDATED_BY,
157 LAST_UPDATE_LOGIN
158 ) values (
159 X_FUND_AMOUNT_TC,
160 X_FUND_AMOUNT_FC,
161 X_ATTRIBUTE4,
162 X_USER_STATUS_ID,
163 X_ATTRIBUTE5,
164 X_ATTRIBUTE6,
165 X_ORGANIZATION_ID,
166 X_ATTRIBUTE7,
167 X_ATTRIBUTE8,
168 X_ATTRIBUTE9,
169 X_ATTRIBUTE10,
170 X_ATTRIBUTE11,
171 X_ATTRIBUTE12,
172 X_ATTRIBUTE13,
173 X_ATTRIBUTE14,
174 X_CURRENCY_CODE_TC,
175 X_CURRENCY_CODE_FC,
176 X_ATTRIBUTE15,
177 X_EMAIL,
178 X_PHONE,
179 X_ACTUAL_COST,
180 X_COORDINATOR_ID,
181 X_FUND_SOURCE_TYPE_CODE,
182 X_FUND_SOURCE_ID,
183 X_OWNER_USER_ID,
184 X_URL,
185 X_PRIORITY_TYPE_CODE,
186 X_CANCELLATION_REASON_CODE,
187 X_INBOUND_SCRIPT_NAME,
188 X_ATTRIBUTE_CATEGORY,
189 X_ATTRIBUTE1,
190 X_ATTRIBUTE2,
191 X_ATTRIBUTE3,
192 X_FORECASTED_COST,
193 X_EVENT_HEADER_ID,
194 X_OBJECT_VERSION_NUMBER,
195 X_EVENT_LEVEL,
196 X_APPLICATION_ID,
197 X_EVENT_TYPE_CODE,
198 X_ACTIVE_FLAG,
199 X_PRIVATE_FLAG,
200 X_SYSTEM_STATUS_CODE,
201 X_LAST_STATUS_DATE,
202 X_STREAM_TYPE_CODE,
203 X_SOURCE_CODE,
204 X_ACTUAL_REVENUE,
205 X_EVENT_STANDALONE_FLAG,
206 X_DAY_OF_EVENT,
207 X_AGENDA_START_TIME,
208 X_AGENDA_END_TIME,
209 X_REG_REQUIRED_FLAG,
210 X_REG_CHARGE_FLAG,
211 X_REG_INVITED_ONLY_FLAG,
212 X_PARTNER_FLAG,
213 X_OVERFLOW_FLAG,
214 X_PARENT_EVENT_HEADER_ID,
215 X_DURATION,
216 X_DURATION_UOM_CODE,
217 X_ACTIVE_FROM_DATE,
218 X_ACTIVE_TO_DATE,
219 X_REG_MAXIMUM_CAPACITY,
220 X_REG_MINIMUM_CAPACITY,
221 X_MAIN_LANGUAGE_CODE,
222 X_CERT_CREDIT_TYPE_CODE,
223 X_CERTIFICATION_CREDITS,
224 X_INVENTORY_ITEM_ID,
225 X_FORECASTED_REVENUE,
226 X_CREATION_DATE,
227 X_CREATED_BY,
228 X_LAST_UPDATE_DATE,
229 X_LAST_UPDATED_BY,
230 X_LAST_UPDATE_LOGIN
231 );
232
233 insert into AMS_EVENT_HEADERS_ALL_TL (
234 EVENT_HEADER_ID,
235 LAST_UPDATE_DATE,
236 LAST_UPDATED_BY,
237 CREATION_DATE,
238 CREATED_BY,
239 LAST_UPDATE_LOGIN,
240 EVENT_HEADER_NAME,
241 EVENT_MKTG_MESSAGE,
242 DESCRIPTION,
243 LANGUAGE,
244 SOURCE_LANG
245 ) select
246 X_EVENT_HEADER_ID,
247 X_LAST_UPDATE_DATE,
248 X_LAST_UPDATED_BY,
249 X_CREATION_DATE,
250 X_CREATED_BY,
251 X_LAST_UPDATE_LOGIN,
252 X_EVENT_HEADER_NAME,
253 X_EVENT_MKTG_MESSAGE,
254 X_DESCRIPTION,
255 L.LANGUAGE_CODE,
256 userenv('LANG')
257 from FND_LANGUAGES L
258 where L.INSTALLED_FLAG in ('I', 'B')
259 and not exists
260 (select NULL
261 from AMS_EVENT_HEADERS_ALL_TL T
262 where T.EVENT_HEADER_ID = X_EVENT_HEADER_ID
263 and T.LANGUAGE = L.LANGUAGE_CODE);
264
265 open c;
266 fetch c into X_ROWID;
267 if (c%notfound) then
268 close c;
269 raise no_data_found;
270 end if;
271 close c;
272
273 end INSERT_ROW;
274
275 procedure LOCK_ROW (
276 X_EVENT_HEADER_ID in NUMBER,
277 X_FUND_AMOUNT_TC in NUMBER,
278 X_FUND_AMOUNT_FC in NUMBER,
279 X_ATTRIBUTE4 in VARCHAR2,
280 X_USER_STATUS_ID in NUMBER,
281 X_ATTRIBUTE5 in VARCHAR2,
282 X_ATTRIBUTE6 in VARCHAR2,
283 X_ORGANIZATION_ID in NUMBER,
284 X_ATTRIBUTE7 in VARCHAR2,
285 X_ATTRIBUTE8 in VARCHAR2,
286 X_ATTRIBUTE9 in VARCHAR2,
287 X_ATTRIBUTE10 in VARCHAR2,
288 X_ATTRIBUTE11 in VARCHAR2,
289 X_ATTRIBUTE12 in VARCHAR2,
290 X_ATTRIBUTE13 in VARCHAR2,
291 X_ATTRIBUTE14 in VARCHAR2,
292 X_CURRENCY_CODE_TC in VARCHAR2,
293 X_CURRENCY_CODE_FC in VARCHAR2,
294 X_ATTRIBUTE15 in VARCHAR2,
295 X_EMAIL in VARCHAR2,
296 X_PHONE in VARCHAR2,
297 X_ACTUAL_COST in NUMBER,
298 X_COORDINATOR_ID in NUMBER,
299 X_FUND_SOURCE_TYPE_CODE in VARCHAR2,
300 X_FUND_SOURCE_ID in NUMBER,
301 X_OWNER_USER_ID in NUMBER,
302 X_URL in VARCHAR2,
303 X_PRIORITY_TYPE_CODE in VARCHAR2,
304 X_CANCELLATION_REASON_CODE in VARCHAR2,
305 X_INBOUND_SCRIPT_NAME in VARCHAR2,
306 X_ATTRIBUTE_CATEGORY in VARCHAR2,
307 X_ATTRIBUTE1 in VARCHAR2,
308 X_ATTRIBUTE2 in VARCHAR2,
309 X_ATTRIBUTE3 in VARCHAR2,
310 X_FORECASTED_COST in NUMBER,
311 X_OBJECT_VERSION_NUMBER in NUMBER,
312 X_EVENT_LEVEL in VARCHAR2,
313 X_APPLICATION_ID in NUMBER,
314 X_EVENT_TYPE_CODE in VARCHAR2,
315 X_ACTIVE_FLAG in VARCHAR2,
316 X_PRIVATE_FLAG in VARCHAR2,
317 X_SYSTEM_STATUS_CODE in VARCHAR2,
318 X_LAST_STATUS_DATE in DATE,
319 X_STREAM_TYPE_CODE in VARCHAR2,
320 X_SOURCE_CODE in VARCHAR2,
321 X_ACTUAL_REVENUE in NUMBER,
322 X_EVENT_STANDALONE_FLAG in VARCHAR2,
323 X_DAY_OF_EVENT in VARCHAR2,
324 X_AGENDA_START_TIME in DATE,
325 X_AGENDA_END_TIME in DATE,
326 X_REG_REQUIRED_FLAG in VARCHAR2,
327 X_REG_CHARGE_FLAG in VARCHAR2,
328 X_REG_INVITED_ONLY_FLAG in VARCHAR2,
329 X_PARTNER_FLAG in VARCHAR2,
330 X_OVERFLOW_FLAG in VARCHAR2,
331 X_PARENT_EVENT_HEADER_ID in NUMBER,
332 X_DURATION in NUMBER,
333 X_DURATION_UOM_CODE in VARCHAR2,
334 X_ACTIVE_FROM_DATE in DATE,
335 X_ACTIVE_TO_DATE in DATE,
336 X_REG_MAXIMUM_CAPACITY in NUMBER,
337 X_REG_MINIMUM_CAPACITY in NUMBER,
338 X_MAIN_LANGUAGE_CODE in VARCHAR2,
339 X_CERT_CREDIT_TYPE_CODE in VARCHAR2,
340 X_CERTIFICATION_CREDITS in NUMBER,
341 X_INVENTORY_ITEM_ID in NUMBER,
342 X_FORECASTED_REVENUE in NUMBER,
343 X_EVENT_HEADER_NAME in VARCHAR2,
344 X_EVENT_MKTG_MESSAGE in VARCHAR2,
345 X_DESCRIPTION in VARCHAR2
346 ) is
347 cursor c is select
348 FUND_AMOUNT_TC,
349 FUND_AMOUNT_FC,
350 ATTRIBUTE4,
351 USER_STATUS_ID,
352 ATTRIBUTE5,
353 ATTRIBUTE6,
354 ORGANIZATION_ID,
355 ATTRIBUTE7,
356 ATTRIBUTE8,
357 ATTRIBUTE9,
358 ATTRIBUTE10,
359 ATTRIBUTE11,
360 ATTRIBUTE12,
361 ATTRIBUTE13,
362 ATTRIBUTE14,
363 CURRENCY_CODE_TC,
364 CURRENCY_CODE_FC,
365 ATTRIBUTE15,
366 EMAIL,
367 PHONE,
368 ACTUAL_COST,
369 COORDINATOR_ID,
370 FUND_SOURCE_TYPE_CODE,
371 FUND_SOURCE_ID,
372 OWNER_USER_ID,
373 URL,
374 PRIORITY_TYPE_CODE,
375 CANCELLATION_REASON_CODE,
376 INBOUND_SCRIPT_NAME,
377 ATTRIBUTE_CATEGORY,
378 ATTRIBUTE1,
379 ATTRIBUTE2,
380 ATTRIBUTE3,
381 FORECASTED_COST,
382 OBJECT_VERSION_NUMBER,
383 EVENT_LEVEL,
384 APPLICATION_ID,
385 EVENT_TYPE_CODE,
386 ACTIVE_FLAG,
387 PRIVATE_FLAG,
388 SYSTEM_STATUS_CODE,
389 LAST_STATUS_DATE,
390 STREAM_TYPE_CODE,
391 SOURCE_CODE,
392 ACTUAL_REVENUE,
393 EVENT_STANDALONE_FLAG,
394 DAY_OF_EVENT,
395 AGENDA_START_TIME,
396 AGENDA_END_TIME,
397 REG_REQUIRED_FLAG,
398 REG_CHARGE_FLAG,
399 REG_INVITED_ONLY_FLAG,
400 PARTNER_FLAG,
401 OVERFLOW_FLAG,
402 PARENT_EVENT_HEADER_ID,
403 DURATION,
404 DURATION_UOM_CODE,
405 ACTIVE_FROM_DATE,
406 ACTIVE_TO_DATE,
407 REG_MAXIMUM_CAPACITY,
408 REG_MINIMUM_CAPACITY,
409 MAIN_LANGUAGE_CODE,
410 CERT_CREDIT_TYPE_CODE,
411 CERTIFICATION_CREDITS,
412 INVENTORY_ITEM_ID,
413 FORECASTED_REVENUE
414 from AMS_EVENT_HEADERS_ALL_B
415 where EVENT_HEADER_ID = X_EVENT_HEADER_ID
416 for update of EVENT_HEADER_ID nowait;
417 recinfo c%rowtype;
418
419 cursor c1 is select
420 EVENT_HEADER_NAME,
424 from AMS_EVENT_HEADERS_ALL_TL
421 EVENT_MKTG_MESSAGE,
422 DESCRIPTION,
423 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
425 where EVENT_HEADER_ID = X_EVENT_HEADER_ID
426 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
427 for update of EVENT_HEADER_ID nowait;
428 begin
429 open c;
430 fetch c into recinfo;
431 if (c%notfound) then
432 close c;
433 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
434 app_exception.raise_exception;
435 end if;
436 close c;
437 if ( ((recinfo.FUND_AMOUNT_TC = X_FUND_AMOUNT_TC)
438 OR ((recinfo.FUND_AMOUNT_TC is null) AND (X_FUND_AMOUNT_TC is null)))
439 AND ((recinfo.FUND_AMOUNT_FC = X_FUND_AMOUNT_FC)
440 OR ((recinfo.FUND_AMOUNT_FC is null) AND (X_FUND_AMOUNT_FC is null)))
441 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
442 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
443 AND ((recinfo.USER_STATUS_ID = X_USER_STATUS_ID)
444 OR ((recinfo.USER_STATUS_ID is null) AND (X_USER_STATUS_ID is null)))
445 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
446 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
447 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
448 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
449 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
450 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
451 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
452 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
453 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
454 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
455 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
456 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
457 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
458 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
459 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
460 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
461 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
462 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
463 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
464 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
465 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
466 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
467 AND ((recinfo.CURRENCY_CODE_TC = X_CURRENCY_CODE_TC)
468 OR ((recinfo.CURRENCY_CODE_TC is null) AND (X_CURRENCY_CODE_TC is null)))
469 AND ((recinfo.CURRENCY_CODE_FC = X_CURRENCY_CODE_FC)
470 OR ((recinfo.CURRENCY_CODE_FC is null) AND (X_CURRENCY_CODE_FC is null)))
471 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
472 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
473 AND ((recinfo.EMAIL = X_EMAIL)
474 OR ((recinfo.EMAIL is null) AND (X_EMAIL is null)))
475 AND ((recinfo.PHONE = X_PHONE)
476 OR ((recinfo.PHONE is null) AND (X_PHONE is null)))
477 AND ((recinfo.ACTUAL_COST = X_ACTUAL_COST)
478 OR ((recinfo.ACTUAL_COST is null) AND (X_ACTUAL_COST is null)))
479 AND ((recinfo.COORDINATOR_ID = X_COORDINATOR_ID)
480 OR ((recinfo.COORDINATOR_ID is null) AND (X_COORDINATOR_ID is null)))
481 AND ((recinfo.FUND_SOURCE_TYPE_CODE = X_FUND_SOURCE_TYPE_CODE)
482 OR ((recinfo.FUND_SOURCE_TYPE_CODE is null) AND (X_FUND_SOURCE_TYPE_CODE is null)))
483 AND ((recinfo.FUND_SOURCE_ID = X_FUND_SOURCE_ID)
484 OR ((recinfo.FUND_SOURCE_ID is null) AND (X_FUND_SOURCE_ID is null)))
485 AND ((recinfo.OWNER_USER_ID = X_OWNER_USER_ID)
486 OR ((recinfo.OWNER_USER_ID is null) AND (X_OWNER_USER_ID is null)))
487 AND ((recinfo.URL = X_URL)
488 OR ((recinfo.URL is null) AND (X_URL is null)))
489 AND ((recinfo.PRIORITY_TYPE_CODE = X_PRIORITY_TYPE_CODE)
490 OR ((recinfo.PRIORITY_TYPE_CODE is null) AND (X_PRIORITY_TYPE_CODE is null)))
491 AND ((recinfo.CANCELLATION_REASON_CODE = X_CANCELLATION_REASON_CODE)
492 OR ((recinfo.CANCELLATION_REASON_CODE is null) AND (X_CANCELLATION_REASON_CODE is null)))
493 AND ((recinfo.INBOUND_SCRIPT_NAME = X_INBOUND_SCRIPT_NAME)
494 OR ((recinfo.INBOUND_SCRIPT_NAME is null) AND (X_INBOUND_SCRIPT_NAME is null)))
495 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
496 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
497 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
498 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
499 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
500 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
501 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
502 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
503 AND ((recinfo.FORECASTED_COST = X_FORECASTED_COST)
504 OR ((recinfo.FORECASTED_COST is null) AND (X_FORECASTED_COST is null)))
505 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
506 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
507 AND (recinfo.EVENT_LEVEL = X_EVENT_LEVEL)
508 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
509 AND ((recinfo.EVENT_TYPE_CODE = X_EVENT_TYPE_CODE)
510 OR ((recinfo.EVENT_TYPE_CODE is null) AND (X_EVENT_TYPE_CODE is null)))
511 AND ((recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
512 OR ((recinfo.ACTIVE_FLAG is null) AND (X_ACTIVE_FLAG is null)))
513 AND ((recinfo.PRIVATE_FLAG = X_PRIVATE_FLAG)
514 OR ((recinfo.PRIVATE_FLAG is null) AND (X_PRIVATE_FLAG is null)))
515 AND (recinfo.SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE)
516 AND (recinfo.LAST_STATUS_DATE = X_LAST_STATUS_DATE)
517 AND ((recinfo.STREAM_TYPE_CODE = X_STREAM_TYPE_CODE)
521 AND ((recinfo.ACTUAL_REVENUE = X_ACTUAL_REVENUE)
518 OR ((recinfo.STREAM_TYPE_CODE is null) AND (X_STREAM_TYPE_CODE is null)))
519 AND ((recinfo.SOURCE_CODE = X_SOURCE_CODE)
520 OR ((recinfo.SOURCE_CODE is null) AND (X_SOURCE_CODE is null)))
522 OR ((recinfo.ACTUAL_REVENUE is null) AND (X_ACTUAL_REVENUE is null)))
523 AND ((recinfo.EVENT_STANDALONE_FLAG = X_EVENT_STANDALONE_FLAG)
524 OR ((recinfo.EVENT_STANDALONE_FLAG is null) AND (X_EVENT_STANDALONE_FLAG is null)))
525 AND ((recinfo.DAY_OF_EVENT = X_DAY_OF_EVENT)
526 OR ((recinfo.DAY_OF_EVENT is null) AND (X_DAY_OF_EVENT is null)))
527 AND ((recinfo.AGENDA_START_TIME = X_AGENDA_START_TIME)
528 OR ((recinfo.AGENDA_START_TIME is null) AND (X_AGENDA_START_TIME is null)))
529 AND ((recinfo.AGENDA_END_TIME = X_AGENDA_END_TIME)
530 OR ((recinfo.AGENDA_END_TIME is null) AND (X_AGENDA_END_TIME is null)))
531 AND (recinfo.REG_REQUIRED_FLAG = X_REG_REQUIRED_FLAG)
532 AND (recinfo.REG_CHARGE_FLAG = X_REG_CHARGE_FLAG)
533 AND (recinfo.REG_INVITED_ONLY_FLAG = X_REG_INVITED_ONLY_FLAG)
534 AND (recinfo.PARTNER_FLAG = X_PARTNER_FLAG)
535 AND ((recinfo.OVERFLOW_FLAG = X_OVERFLOW_FLAG)
536 OR ((recinfo.OVERFLOW_FLAG is null) AND (X_OVERFLOW_FLAG is null)))
537 AND ((recinfo.PARENT_EVENT_HEADER_ID = X_PARENT_EVENT_HEADER_ID)
538 OR ((recinfo.PARENT_EVENT_HEADER_ID is null) AND (X_PARENT_EVENT_HEADER_ID is null)))
539 AND ((recinfo.DURATION = X_DURATION)
540 OR ((recinfo.DURATION is null) AND (X_DURATION is null)))
541 AND ((recinfo.DURATION_UOM_CODE = X_DURATION_UOM_CODE)
542 OR ((recinfo.DURATION_UOM_CODE is null) AND (X_DURATION_UOM_CODE is null)))
543 AND ((recinfo.ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE)
544 OR ((recinfo.ACTIVE_FROM_DATE is null) AND (X_ACTIVE_FROM_DATE is null)))
545 AND ((recinfo.ACTIVE_TO_DATE = X_ACTIVE_TO_DATE)
546 OR ((recinfo.ACTIVE_TO_DATE is null) AND (X_ACTIVE_TO_DATE is null)))
547 AND ((recinfo.REG_MAXIMUM_CAPACITY = X_REG_MAXIMUM_CAPACITY)
548 OR ((recinfo.REG_MAXIMUM_CAPACITY is null) AND (X_REG_MAXIMUM_CAPACITY is null)))
549 AND ((recinfo.REG_MINIMUM_CAPACITY = X_REG_MINIMUM_CAPACITY)
550 OR ((recinfo.REG_MINIMUM_CAPACITY is null) AND (X_REG_MINIMUM_CAPACITY is null)))
551 AND ((recinfo.MAIN_LANGUAGE_CODE = X_MAIN_LANGUAGE_CODE)
552 OR ((recinfo.MAIN_LANGUAGE_CODE is null) AND (X_MAIN_LANGUAGE_CODE is null)))
553 AND ((recinfo.CERT_CREDIT_TYPE_CODE = X_CERT_CREDIT_TYPE_CODE)
554 OR ((recinfo.CERT_CREDIT_TYPE_CODE is null) AND (X_CERT_CREDIT_TYPE_CODE is null)))
555 AND ((recinfo.CERTIFICATION_CREDITS = X_CERTIFICATION_CREDITS)
556 OR ((recinfo.CERTIFICATION_CREDITS is null) AND (X_CERTIFICATION_CREDITS is null)))
557 AND ((recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
558 OR ((recinfo.INVENTORY_ITEM_ID is null) AND (X_INVENTORY_ITEM_ID is null)))
559 AND ((recinfo.FORECASTED_REVENUE = X_FORECASTED_REVENUE)
560 OR ((recinfo.FORECASTED_REVENUE is null) AND (X_FORECASTED_REVENUE is null)))
561 ) then
562 null;
563 else
564 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
565 app_exception.raise_exception;
566 end if;
567
568 for tlinfo in c1 loop
569 if (tlinfo.BASELANG = 'Y') then
570 if ( (tlinfo.EVENT_HEADER_NAME = X_EVENT_HEADER_NAME)
571 AND ((tlinfo.EVENT_MKTG_MESSAGE = X_EVENT_MKTG_MESSAGE)
572 OR ((tlinfo.EVENT_MKTG_MESSAGE is null) AND (X_EVENT_MKTG_MESSAGE is null)))
573 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
574 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
575 ) then
576 null;
577 else
578 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
579 app_exception.raise_exception;
580 end if;
581 end if;
582 end loop;
583 return;
584 end LOCK_ROW;
585
586 procedure UPDATE_ROW (
587 X_EVENT_HEADER_ID in NUMBER,
588 X_FUND_AMOUNT_TC in NUMBER,
589 X_FUND_AMOUNT_FC in NUMBER,
590 X_ATTRIBUTE4 in VARCHAR2,
591 X_USER_STATUS_ID in NUMBER,
592 X_ATTRIBUTE5 in VARCHAR2,
593 X_ATTRIBUTE6 in VARCHAR2,
594 X_ORGANIZATION_ID in NUMBER,
595 X_ATTRIBUTE7 in VARCHAR2,
596 X_ATTRIBUTE8 in VARCHAR2,
597 X_ATTRIBUTE9 in VARCHAR2,
598 X_ATTRIBUTE10 in VARCHAR2,
599 X_ATTRIBUTE11 in VARCHAR2,
600 X_ATTRIBUTE12 in VARCHAR2,
601 X_ATTRIBUTE13 in VARCHAR2,
602 X_ATTRIBUTE14 in VARCHAR2,
603 X_CURRENCY_CODE_TC in VARCHAR2,
604 X_CURRENCY_CODE_FC in VARCHAR2,
605 X_ATTRIBUTE15 in VARCHAR2,
606 X_EMAIL in VARCHAR2,
607 X_PHONE in VARCHAR2,
608 X_ACTUAL_COST in NUMBER,
609 X_COORDINATOR_ID in NUMBER,
610 X_FUND_SOURCE_TYPE_CODE in VARCHAR2,
611 X_FUND_SOURCE_ID in NUMBER,
612 X_OWNER_USER_ID in NUMBER,
613 X_URL in VARCHAR2,
614 X_PRIORITY_TYPE_CODE in VARCHAR2,
615 X_CANCELLATION_REASON_CODE in VARCHAR2,
616 X_INBOUND_SCRIPT_NAME in VARCHAR2,
617 X_ATTRIBUTE_CATEGORY in VARCHAR2,
618 X_ATTRIBUTE1 in VARCHAR2,
619 X_ATTRIBUTE2 in VARCHAR2,
620 X_ATTRIBUTE3 in VARCHAR2,
621 X_FORECASTED_COST in NUMBER,
622 X_OBJECT_VERSION_NUMBER in NUMBER,
623 X_EVENT_LEVEL in VARCHAR2,
624 X_APPLICATION_ID in NUMBER,
625 X_EVENT_TYPE_CODE in VARCHAR2,
626 X_ACTIVE_FLAG in VARCHAR2,
627 X_PRIVATE_FLAG in VARCHAR2,
628 X_SYSTEM_STATUS_CODE in VARCHAR2,
629 X_LAST_STATUS_DATE in DATE,
630 X_STREAM_TYPE_CODE in VARCHAR2,
631 X_SOURCE_CODE in VARCHAR2,
632 X_ACTUAL_REVENUE in NUMBER,
636 X_AGENDA_END_TIME in DATE,
633 X_EVENT_STANDALONE_FLAG in VARCHAR2,
634 X_DAY_OF_EVENT in VARCHAR2,
635 X_AGENDA_START_TIME in DATE,
637 X_REG_REQUIRED_FLAG in VARCHAR2,
638 X_REG_CHARGE_FLAG in VARCHAR2,
639 X_REG_INVITED_ONLY_FLAG in VARCHAR2,
640 X_PARTNER_FLAG in VARCHAR2,
641 X_OVERFLOW_FLAG in VARCHAR2,
642 X_PARENT_EVENT_HEADER_ID in NUMBER,
643 X_DURATION in NUMBER,
644 X_DURATION_UOM_CODE in VARCHAR2,
645 X_ACTIVE_FROM_DATE in DATE,
646 X_ACTIVE_TO_DATE in DATE,
647 X_REG_MAXIMUM_CAPACITY in NUMBER,
648 X_REG_MINIMUM_CAPACITY in NUMBER,
649 X_MAIN_LANGUAGE_CODE in VARCHAR2,
650 X_CERT_CREDIT_TYPE_CODE in VARCHAR2,
651 X_CERTIFICATION_CREDITS in NUMBER,
652 X_INVENTORY_ITEM_ID in NUMBER,
653 X_FORECASTED_REVENUE in NUMBER,
654 X_EVENT_HEADER_NAME in VARCHAR2,
655 X_EVENT_MKTG_MESSAGE in VARCHAR2,
656 X_DESCRIPTION in VARCHAR2,
657 X_LAST_UPDATE_DATE in DATE,
658 X_LAST_UPDATED_BY in NUMBER,
659 X_LAST_UPDATE_LOGIN in NUMBER
660 ) is
661 begin
662 update AMS_EVENT_HEADERS_ALL_B set
663 FUND_AMOUNT_TC = X_FUND_AMOUNT_TC,
664 FUND_AMOUNT_FC = X_FUND_AMOUNT_FC,
665 ATTRIBUTE4 = X_ATTRIBUTE4,
666 USER_STATUS_ID = X_USER_STATUS_ID,
667 ATTRIBUTE5 = X_ATTRIBUTE5,
668 ATTRIBUTE6 = X_ATTRIBUTE6,
669 ORGANIZATION_ID = X_ORGANIZATION_ID,
670 ATTRIBUTE7 = X_ATTRIBUTE7,
671 ATTRIBUTE8 = X_ATTRIBUTE8,
672 ATTRIBUTE9 = X_ATTRIBUTE9,
673 ATTRIBUTE10 = X_ATTRIBUTE10,
674 ATTRIBUTE11 = X_ATTRIBUTE11,
675 ATTRIBUTE12 = X_ATTRIBUTE12,
676 ATTRIBUTE13 = X_ATTRIBUTE13,
677 ATTRIBUTE14 = X_ATTRIBUTE14,
678 CURRENCY_CODE_TC = X_CURRENCY_CODE_TC,
679 CURRENCY_CODE_FC = X_CURRENCY_CODE_FC,
680 ATTRIBUTE15 = X_ATTRIBUTE15,
681 EMAIL = X_EMAIL,
682 PHONE = X_PHONE,
683 ACTUAL_COST = X_ACTUAL_COST,
684 COORDINATOR_ID = X_COORDINATOR_ID,
685 FUND_SOURCE_TYPE_CODE = X_FUND_SOURCE_TYPE_CODE,
686 FUND_SOURCE_ID = X_FUND_SOURCE_ID,
687 OWNER_USER_ID = X_OWNER_USER_ID,
688 URL = X_URL,
689 PRIORITY_TYPE_CODE = X_PRIORITY_TYPE_CODE,
690 CANCELLATION_REASON_CODE = X_CANCELLATION_REASON_CODE,
691 INBOUND_SCRIPT_NAME = X_INBOUND_SCRIPT_NAME,
692 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
693 ATTRIBUTE1 = X_ATTRIBUTE1,
694 ATTRIBUTE2 = X_ATTRIBUTE2,
695 ATTRIBUTE3 = X_ATTRIBUTE3,
696 FORECASTED_COST = X_FORECASTED_COST,
697 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
698 EVENT_LEVEL = X_EVENT_LEVEL,
699 APPLICATION_ID = X_APPLICATION_ID,
700 EVENT_TYPE_CODE = X_EVENT_TYPE_CODE,
701 ACTIVE_FLAG = X_ACTIVE_FLAG,
702 PRIVATE_FLAG = X_PRIVATE_FLAG,
703 SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE,
704 LAST_STATUS_DATE = X_LAST_STATUS_DATE,
705 STREAM_TYPE_CODE = X_STREAM_TYPE_CODE,
706 SOURCE_CODE = X_SOURCE_CODE,
707 ACTUAL_REVENUE = X_ACTUAL_REVENUE,
708 EVENT_STANDALONE_FLAG = X_EVENT_STANDALONE_FLAG,
709 DAY_OF_EVENT = X_DAY_OF_EVENT,
710 AGENDA_START_TIME = X_AGENDA_START_TIME,
711 AGENDA_END_TIME = X_AGENDA_END_TIME,
712 REG_REQUIRED_FLAG = X_REG_REQUIRED_FLAG,
713 REG_CHARGE_FLAG = X_REG_CHARGE_FLAG,
714 REG_INVITED_ONLY_FLAG = X_REG_INVITED_ONLY_FLAG,
715 PARTNER_FLAG = X_PARTNER_FLAG,
716 OVERFLOW_FLAG = X_OVERFLOW_FLAG,
717 PARENT_EVENT_HEADER_ID = X_PARENT_EVENT_HEADER_ID,
718 DURATION = X_DURATION,
719 DURATION_UOM_CODE = X_DURATION_UOM_CODE,
720 ACTIVE_FROM_DATE = X_ACTIVE_FROM_DATE,
721 ACTIVE_TO_DATE = X_ACTIVE_TO_DATE,
722 REG_MAXIMUM_CAPACITY = X_REG_MAXIMUM_CAPACITY,
723 REG_MINIMUM_CAPACITY = X_REG_MINIMUM_CAPACITY,
724 MAIN_LANGUAGE_CODE = X_MAIN_LANGUAGE_CODE,
725 CERT_CREDIT_TYPE_CODE = X_CERT_CREDIT_TYPE_CODE,
726 CERTIFICATION_CREDITS = X_CERTIFICATION_CREDITS,
727 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
728 FORECASTED_REVENUE = X_FORECASTED_REVENUE,
729 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
730 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
731 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
732 where EVENT_HEADER_ID = X_EVENT_HEADER_ID;
733
734 if (sql%notfound) then
735 raise no_data_found;
736 end if;
737
738 update AMS_EVENT_HEADERS_ALL_TL set
739 EVENT_HEADER_NAME = X_EVENT_HEADER_NAME,
740 EVENT_MKTG_MESSAGE = X_EVENT_MKTG_MESSAGE,
741 DESCRIPTION = X_DESCRIPTION,
742 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
743 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
744 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
745 SOURCE_LANG = userenv('LANG')
746 where EVENT_HEADER_ID = X_EVENT_HEADER_ID
747 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
748
749 if (sql%notfound) then
750 raise no_data_found;
751 end if;
752 end UPDATE_ROW;
753
754 procedure DELETE_ROW (
755 X_EVENT_HEADER_ID in NUMBER
756 ) is
757 begin
758 delete from AMS_EVENT_HEADERS_ALL_TL
759 where EVENT_HEADER_ID = X_EVENT_HEADER_ID;
760
761 if (sql%notfound) then
762 raise no_data_found;
763 end if;
764
765 delete from AMS_EVENT_HEADERS_ALL_B
766 where EVENT_HEADER_ID = X_EVENT_HEADER_ID;
767
768 if (sql%notfound) then
769 raise no_data_found;
770 end if;
771 end DELETE_ROW;
772
773 procedure ADD_LANGUAGE
774 is
775 begin
776 delete from AMS_EVENT_HEADERS_ALL_TL T
777 where not exists
778 (select NULL
779 from AMS_EVENT_HEADERS_ALL_B B
783 update AMS_EVENT_HEADERS_ALL_TL T set (
780 where B.EVENT_HEADER_ID = T.EVENT_HEADER_ID
781 );
782
784 EVENT_HEADER_NAME,
785 EVENT_MKTG_MESSAGE,
786 DESCRIPTION
787 ) = (select
788 B.EVENT_HEADER_NAME,
789 B.EVENT_MKTG_MESSAGE,
790 B.DESCRIPTION
791 from AMS_EVENT_HEADERS_ALL_TL B
792 where B.EVENT_HEADER_ID = T.EVENT_HEADER_ID
793 and B.LANGUAGE = T.SOURCE_LANG)
794 where (
795 T.EVENT_HEADER_ID,
796 T.LANGUAGE
797 ) in (select
798 SUBT.EVENT_HEADER_ID,
799 SUBT.LANGUAGE
800 from AMS_EVENT_HEADERS_ALL_TL SUBB, AMS_EVENT_HEADERS_ALL_TL SUBT
801 where SUBB.EVENT_HEADER_ID = SUBT.EVENT_HEADER_ID
802 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
803 and (SUBB.EVENT_HEADER_NAME <> SUBT.EVENT_HEADER_NAME
804 or SUBB.EVENT_MKTG_MESSAGE <> SUBT.EVENT_MKTG_MESSAGE
805 or (SUBB.EVENT_MKTG_MESSAGE is null and SUBT.EVENT_MKTG_MESSAGE is not null)
806 or (SUBB.EVENT_MKTG_MESSAGE is not null and SUBT.EVENT_MKTG_MESSAGE is null)
807 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
808 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
809 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
810 ));
811
812 insert into AMS_EVENT_HEADERS_ALL_TL (
813 EVENT_HEADER_ID,
814 LAST_UPDATE_DATE,
815 LAST_UPDATED_BY,
816 CREATION_DATE,
817 CREATED_BY,
818 LAST_UPDATE_LOGIN,
819 EVENT_HEADER_NAME,
820 EVENT_MKTG_MESSAGE,
821 DESCRIPTION,
822 LANGUAGE,
823 SOURCE_LANG
824 ) select
825 B.EVENT_HEADER_ID,
826 B.LAST_UPDATE_DATE,
827 B.LAST_UPDATED_BY,
828 B.CREATION_DATE,
829 B.CREATED_BY,
830 B.LAST_UPDATE_LOGIN,
831 B.EVENT_HEADER_NAME,
832 B.EVENT_MKTG_MESSAGE,
833 B.DESCRIPTION,
834 L.LANGUAGE_CODE,
835 B.SOURCE_LANG
836 from AMS_EVENT_HEADERS_ALL_TL B, FND_LANGUAGES L
837 where L.INSTALLED_FLAG in ('I', 'B')
838 and B.LANGUAGE = userenv('LANG')
839 and not exists
840 (select NULL
841 from AMS_EVENT_HEADERS_ALL_TL T
842 where T.EVENT_HEADER_ID = B.EVENT_HEADER_ID
843 and T.LANGUAGE = L.LANGUAGE_CODE);
844 end ADD_LANGUAGE;
845
846
847 procedure TRANSLATE_ROW(
848 X_EVENT_HEADER_ID in NUMBER,
849 X_EVENT_HEADER_NAME in VARCHAR2,
850 X_DESCRIPTION in VARCHAR2,
851 X_EVENT_MKTG_MESSAGE in VARCHAR2,
852 X_OWNER in VARCHAR2
853 )
854 IS
855 BEGIN
856 update AMS_EVENT_HEADERS_ALL_TL set
857 EVENT_HEADER_NAME = nvl(X_EVENT_HEADER_NAME,EVENT_HEADER_NAME),
858 EVENT_MKTG_MESSAGE = nvl(X_EVENT_MKTG_MESSAGE,EVENT_MKTG_MESSAGE),
859 DESCRIPTION = nvl(X_DESCRIPTION,DESCRIPTION),
860 LAST_UPDATE_DATE = SYSDATE,
861 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
862 LAST_UPDATE_LOGIN = 0,
863 SOURCE_LANG = userenv('LANG')
864 where EVENT_HEADER_ID = X_EVENT_HEADER_ID
865 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
866
867 END TRANSLATE_ROW;
868
869 procedure LOAD_ROW (
870 X_EVENT_HEADER_ID in NUMBER,
871 X_FUND_AMOUNT_TC in NUMBER,
872 X_FUND_AMOUNT_FC in NUMBER,
873 X_ATTRIBUTE4 in VARCHAR2,
874 X_USER_STATUS_ID in NUMBER,
875 X_ATTRIBUTE5 in VARCHAR2,
876 X_ATTRIBUTE6 in VARCHAR2,
877 X_ORGANIZATION_ID in NUMBER,
878 X_ATTRIBUTE7 in VARCHAR2,
879 X_ATTRIBUTE8 in VARCHAR2,
880 X_ATTRIBUTE9 in VARCHAR2,
881 X_ATTRIBUTE10 in VARCHAR2,
882 X_ATTRIBUTE11 in VARCHAR2,
883 X_ATTRIBUTE12 in VARCHAR2,
884 X_ATTRIBUTE13 in VARCHAR2,
888 X_ATTRIBUTE15 in VARCHAR2,
885 X_ATTRIBUTE14 in VARCHAR2,
886 X_CURRENCY_CODE_TC in VARCHAR2,
887 X_CURRENCY_CODE_FC in VARCHAR2,
889 X_EMAIL in VARCHAR2,
890 X_PHONE in VARCHAR2,
891 X_ACTUAL_COST in NUMBER,
892 X_COORDINATOR_ID in NUMBER,
893 X_FUND_SOURCE_TYPE_CODE in VARCHAR2,
894 X_FUND_SOURCE_ID in NUMBER,
895 X_OWNER_USER_ID in NUMBER,
896 X_URL in VARCHAR2,
897 X_PRIORITY_TYPE_CODE in VARCHAR2,
898 X_CANCELLATION_REASON_CODE in VARCHAR2,
899 X_INBOUND_SCRIPT_NAME in VARCHAR2,
900 X_ATTRIBUTE_CATEGORY in VARCHAR2,
901 X_ATTRIBUTE1 in VARCHAR2,
902 X_ATTRIBUTE2 in VARCHAR2,
903 X_ATTRIBUTE3 in VARCHAR2,
904 X_FORECASTED_COST in NUMBER,
905 X_OBJECT_VERSION_NUMBER in NUMBER,
906 X_EVENT_LEVEL in VARCHAR2,
907 X_APPLICATION_ID in NUMBER,
908 X_EVENT_TYPE_CODE in VARCHAR2,
909 X_ACTIVE_FLAG in VARCHAR2,
910 X_PRIVATE_FLAG in VARCHAR2,
911 X_SYSTEM_STATUS_CODE in VARCHAR2,
912 X_LAST_STATUS_DATE in DATE,
913 X_STREAM_TYPE_CODE in VARCHAR2,
914 X_SOURCE_CODE in VARCHAR2,
915 X_ACTUAL_REVENUE in NUMBER,
916 X_EVENT_STANDALONE_FLAG in VARCHAR2,
917 X_DAY_OF_EVENT in VARCHAR2,
918 X_AGENDA_START_TIME in DATE,
919 X_AGENDA_END_TIME in DATE,
920 X_REG_REQUIRED_FLAG in VARCHAR2,
921 X_REG_CHARGE_FLAG in VARCHAR2,
922 X_REG_INVITED_ONLY_FLAG in VARCHAR2,
923 X_PARTNER_FLAG in VARCHAR2,
924 X_OVERFLOW_FLAG in VARCHAR2,
925 X_PARENT_EVENT_HEADER_ID in NUMBER,
926 X_DURATION in NUMBER,
927 X_DURATION_UOM_CODE in VARCHAR2,
928 X_ACTIVE_FROM_DATE in DATE,
929 X_ACTIVE_TO_DATE in DATE,
930 X_REG_MAXIMUM_CAPACITY in NUMBER,
931 X_REG_MINIMUM_CAPACITY in NUMBER,
932 X_MAIN_LANGUAGE_CODE in VARCHAR2,
933 X_CERT_CREDIT_TYPE_CODE in VARCHAR2,
934 X_CERTIFICATION_CREDITS in NUMBER,
935 X_INVENTORY_ITEM_ID in NUMBER,
936 X_FORECASTED_REVENUE in NUMBER,
937 X_EVENT_HEADER_NAME in VARCHAR2,
938 X_EVENT_MKTG_MESSAGE in VARCHAR2,
939 X_DESCRIPTION in VARCHAR2,
940 X_OWNER IN VARCHAR2
941 ) is
942 l_user_id number := 0;
943 l_version number;
944 l_evh_id number;
945 l_dummy_char varchar2(1);
946 l_row_id varchar2(100);
947
948 cursor c_version is
949 select object_version_number
950 from ams_event_headers_all_b
951 where event_header_id = X_EVENT_HEADER_ID;
952
953 cursor c_evh_exists is
954 select 'x'
955 from ams_event_headers_all_b
956 where event_header_id = X_EVENT_HEADER_ID;
957
958 cursor c_evh_id is
959 select ams_event_headers_all_b_s.nextval
960 from dual;
961
962 BEGIN
963
964 if X_OWNER = 'SEED' then
965 l_user_id := 1;
966 end if;
967
968 open c_evh_exists;
969 fetch c_evh_exists into l_dummy_char;
970 if c_evh_exists%notfound then
971 close c_evh_exists;
972 if X_EVENT_HEADER_ID is not null then
973 l_evh_id := X_EVENT_HEADER_ID;
974 else
975 open c_evh_id;
976 fetch c_evh_id into l_evh_id;
977 close c_evh_id;
978 end if;
979 l_version := 1;
980
981
982 AMS_EVENT_HEADERS_ALL_PKG.INSERT_ROW(
983 X_ROWID => l_row_id,
984 X_EVENT_HEADER_ID => l_evh_id,
985 X_OBJECT_VERSION_NUMBER => l_version,
986 X_FUND_AMOUNT_TC => X_FUND_AMOUNT_TC,
987 X_FUND_AMOUNT_FC => X_FUND_AMOUNT_FC,
988 X_ATTRIBUTE4 => X_ATTRIBUTE4,
989 X_USER_STATUS_ID => X_USER_STATUS_ID,
990 X_ATTRIBUTE5 => X_ATTRIBUTE5,
991 X_ATTRIBUTE6 => X_ATTRIBUTE6,
992 X_ORGANIZATION_ID => X_ORGANIZATION_ID,
993 X_ATTRIBUTE7 => X_ATTRIBUTE7,
994 X_ATTRIBUTE8 => X_ATTRIBUTE8,
995 X_ATTRIBUTE9 => X_ATTRIBUTE9,
996 X_ATTRIBUTE10 => X_ATTRIBUTE10,
997 X_ATTRIBUTE11 => X_ATTRIBUTE11,
998 X_ATTRIBUTE12 => X_ATTRIBUTE12,
999 X_ATTRIBUTE13 => X_ATTRIBUTE13,
1000 X_ATTRIBUTE14 => X_ATTRIBUTE14,
1001 X_CURRENCY_CODE_TC => X_CURRENCY_CODE_TC,
1002 X_CURRENCY_CODE_FC => X_CURRENCY_CODE_FC,
1003 X_ATTRIBUTE15 => X_ATTRIBUTE15,
1004 X_EMAIL => X_EMAIL,
1005 X_PHONE => X_PHONE,
1006 X_ACTUAL_COST => X_ACTUAL_COST,
1007 X_COORDINATOR_ID => X_COORDINATOR_ID,
1008 X_FUND_SOURCE_TYPE_CODE => X_FUND_SOURCE_TYPE_CODE,
1009 X_FUND_SOURCE_ID => X_FUND_SOURCE_ID,
1010 X_OWNER_USER_ID => X_OWNER_USER_ID,
1011 X_URL => X_URL,
1012 X_PRIORITY_TYPE_CODE => X_PRIORITY_TYPE_CODE,
1013 X_CANCELLATION_REASON_CODE => X_CANCELLATION_REASON_CODE,
1014 X_INBOUND_SCRIPT_NAME => X_INBOUND_SCRIPT_NAME,
1015 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
1016 X_ATTRIBUTE1 => X_ATTRIBUTE1,
1017 X_ATTRIBUTE2 => X_ATTRIBUTE2,
1018 X_ATTRIBUTE3 => X_ATTRIBUTE3,
1019 X_FORECASTED_COST => X_FORECASTED_COST,
1020 X_EVENT_LEVEL => X_EVENT_LEVEL,
1021 X_APPLICATION_ID => X_APPLICATION_ID,
1022 X_EVENT_TYPE_CODE => X_EVENT_TYPE_CODE,
1023 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
1024 X_PRIVATE_FLAG => X_PRIVATE_FLAG,
1025 X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
1026 X_LAST_STATUS_DATE => X_LAST_STATUS_DATE,
1027 X_STREAM_TYPE_CODE => X_STREAM_TYPE_CODE,
1028 X_SOURCE_CODE => X_SOURCE_CODE,
1029 X_ACTUAL_REVENUE => X_ACTUAL_REVENUE,
1030 X_EVENT_STANDALONE_FLAG => X_EVENT_STANDALONE_FLAG,
1031 X_DAY_OF_EVENT => X_DAY_OF_EVENT,
1032 X_AGENDA_START_TIME => X_AGENDA_START_TIME,
1033 X_AGENDA_END_TIME => X_AGENDA_END_TIME,
1034 X_REG_REQUIRED_FLAG => X_REG_REQUIRED_FLAG,
1035 X_REG_CHARGE_FLAG => X_REG_CHARGE_FLAG,
1036 X_REG_INVITED_ONLY_FLAG => X_REG_INVITED_ONLY_FLAG,
1040 X_DURATION => X_DURATION,
1037 X_PARTNER_FLAG => X_PARTNER_FLAG,
1038 X_OVERFLOW_FLAG => X_OVERFLOW_FLAG,
1039 X_PARENT_EVENT_HEADER_ID => X_PARENT_EVENT_HEADER_ID,
1041 X_DURATION_UOM_CODE => X_DURATION_UOM_CODE,
1042 X_ACTIVE_FROM_DATE => X_ACTIVE_FROM_DATE,
1043 X_ACTIVE_TO_DATE => X_ACTIVE_TO_DATE,
1044 X_REG_MAXIMUM_CAPACITY => X_REG_MAXIMUM_CAPACITY,
1045 X_REG_MINIMUM_CAPACITY => X_REG_MINIMUM_CAPACITY,
1046 X_MAIN_LANGUAGE_CODE => X_MAIN_LANGUAGE_CODE,
1047 X_CERT_CREDIT_TYPE_CODE => X_CERT_CREDIT_TYPE_CODE,
1048 X_CERTIFICATION_CREDITS => X_CERTIFICATION_CREDITS,
1049 X_INVENTORY_ITEM_ID => X_INVENTORY_ITEM_ID,
1050 X_FORECASTED_REVENUE => X_FORECASTED_REVENUE,
1051 X_EVENT_HEADER_NAME => X_EVENT_HEADER_NAME,
1052 X_EVENT_MKTG_MESSAGE => X_EVENT_MKTG_MESSAGE,
1053 X_DESCRIPTION => X_DESCRIPTION,
1054 X_CREATION_DATE => SYSDATE,
1055 X_CREATED_BY => l_user_id,
1056 X_LAST_UPDATE_DATE => SYSDATE,
1057 X_LAST_UPDATED_BY => l_user_id,
1058 X_LAST_UPDATE_LOGIN => 0);
1059
1060 else
1061 close c_evh_exists;
1062 open c_version;
1063 fetch c_version into l_version;
1064 close c_version;
1065
1066 AMS_EVENT_HEADERS_ALL_PKG.UPDATE_ROW(
1067 X_EVENT_HEADER_ID => l_evh_id,
1068 X_OBJECT_VERSION_NUMBER => l_version+1,
1069 X_FUND_AMOUNT_TC => X_FUND_AMOUNT_TC,
1070 X_FUND_AMOUNT_FC => X_FUND_AMOUNT_FC,
1071 X_ATTRIBUTE4 => X_ATTRIBUTE4,
1072 X_USER_STATUS_ID => X_USER_STATUS_ID,
1073 X_ATTRIBUTE5 => X_ATTRIBUTE5,
1074 X_ATTRIBUTE6 => X_ATTRIBUTE6,
1075 X_ORGANIZATION_ID => X_ORGANIZATION_ID,
1076 X_ATTRIBUTE7 => X_ATTRIBUTE7,
1077 X_ATTRIBUTE8 => X_ATTRIBUTE8,
1078 X_ATTRIBUTE9 => X_ATTRIBUTE9,
1079 X_ATTRIBUTE10 => X_ATTRIBUTE10,
1080 X_ATTRIBUTE11 => X_ATTRIBUTE11,
1081 X_ATTRIBUTE12 => X_ATTRIBUTE12,
1082 X_ATTRIBUTE13 => X_ATTRIBUTE13,
1083 X_ATTRIBUTE14 => X_ATTRIBUTE14,
1084 X_CURRENCY_CODE_TC => X_CURRENCY_CODE_TC,
1085 X_CURRENCY_CODE_FC => X_CURRENCY_CODE_FC,
1086 X_ATTRIBUTE15 => X_ATTRIBUTE15,
1087 X_EMAIL => X_EMAIL,
1088 X_PHONE => X_PHONE,
1089 X_ACTUAL_COST => X_ACTUAL_COST,
1090 X_COORDINATOR_ID => X_COORDINATOR_ID,
1091 X_FUND_SOURCE_TYPE_CODE => X_FUND_SOURCE_TYPE_CODE,
1092 X_FUND_SOURCE_ID => X_FUND_SOURCE_ID,
1093 X_OWNER_USER_ID => X_OWNER_USER_ID,
1094 X_URL => X_URL,
1095 X_PRIORITY_TYPE_CODE => X_PRIORITY_TYPE_CODE,
1096 X_CANCELLATION_REASON_CODE => X_CANCELLATION_REASON_CODE,
1097 X_INBOUND_SCRIPT_NAME => X_INBOUND_SCRIPT_NAME,
1098 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
1099 X_ATTRIBUTE1 => X_ATTRIBUTE1,
1100 X_ATTRIBUTE2 => X_ATTRIBUTE2,
1101 X_ATTRIBUTE3 => X_ATTRIBUTE3,
1102 X_FORECASTED_COST => X_FORECASTED_COST,
1103 X_EVENT_LEVEL => X_EVENT_LEVEL,
1104 X_APPLICATION_ID => X_APPLICATION_ID,
1105 X_EVENT_TYPE_CODE => X_EVENT_TYPE_CODE,
1106 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
1107 X_PRIVATE_FLAG => X_PRIVATE_FLAG,
1108 X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
1109 X_LAST_STATUS_DATE => X_LAST_STATUS_DATE,
1110 X_STREAM_TYPE_CODE => X_STREAM_TYPE_CODE,
1111 X_SOURCE_CODE => X_SOURCE_CODE,
1112 X_ACTUAL_REVENUE => X_ACTUAL_REVENUE,
1113 X_EVENT_STANDALONE_FLAG => X_EVENT_STANDALONE_FLAG,
1114 X_DAY_OF_EVENT => X_DAY_OF_EVENT,
1115 X_AGENDA_START_TIME => X_AGENDA_START_TIME,
1116 X_AGENDA_END_TIME => X_AGENDA_END_TIME,
1117 X_REG_REQUIRED_FLAG => X_REG_REQUIRED_FLAG,
1118 X_REG_CHARGE_FLAG => X_REG_CHARGE_FLAG,
1119 X_REG_INVITED_ONLY_FLAG => X_REG_INVITED_ONLY_FLAG,
1123 X_DURATION => X_DURATION,
1120 X_PARTNER_FLAG => X_PARTNER_FLAG,
1121 X_OVERFLOW_FLAG => X_OVERFLOW_FLAG,
1122 X_PARENT_EVENT_HEADER_ID => X_PARENT_EVENT_HEADER_ID,
1124 X_DURATION_UOM_CODE => X_DURATION_UOM_CODE,
1125 X_ACTIVE_FROM_DATE => X_ACTIVE_FROM_DATE,
1126 X_ACTIVE_TO_DATE => X_ACTIVE_TO_DATE,
1127 X_REG_MAXIMUM_CAPACITY => X_REG_MAXIMUM_CAPACITY,
1128 X_REG_MINIMUM_CAPACITY => X_REG_MINIMUM_CAPACITY,
1129 X_MAIN_LANGUAGE_CODE => X_MAIN_LANGUAGE_CODE,
1130 X_CERT_CREDIT_TYPE_CODE => X_CERT_CREDIT_TYPE_CODE,
1131 X_CERTIFICATION_CREDITS => X_CERTIFICATION_CREDITS,
1132 X_INVENTORY_ITEM_ID => X_INVENTORY_ITEM_ID,
1133 X_FORECASTED_REVENUE => X_FORECASTED_REVENUE,
1134 X_EVENT_HEADER_NAME => X_EVENT_HEADER_NAME,
1135 X_EVENT_MKTG_MESSAGE => X_EVENT_MKTG_MESSAGE,
1136 X_DESCRIPTION => X_DESCRIPTION,
1137 X_LAST_UPDATE_DATE => SYSDATE,
1138 X_LAST_UPDATED_BY => l_user_id,
1139 X_LAST_UPDATE_LOGIN => 0);
1140
1141 end if;
1142 END LOAD_ROW;
1143
1144 end AMS_EVENT_HEADERS_ALL_PKG;