1 PACKAGE body CSD_RECALL_HEADERS_PKG AS
2 /* $Header: csdtrclb.pls 120.1 2010/07/14 12:10:37 subhat noship $ */
3 PROCEDURE INSERT_ROW
4 (
5 X_ROWID IN OUT nocopy VARCHAR2,
6 X_RECALL_ID IN NUMBER,
7 X_RECALL_NUMBER IN VARCHAR2,
8 X_REPORTED_DATE IN DATE,
9 X_INIT_DATE IN DATE,
10 X_COMP_DATE IN DATE,
11 X_MANDAT_COMP_DATE IN DATE,
12 X_ACT_COMP_DATE IN DATE,
13 X_SERVICE_CODES IN VARCHAR2,
14 X_REG_AGENCY_ID IN VARCHAR2,
15 X_REG_AGENCY_REF_NO IN VARCHAR2,
16 X_OBJECT_VERSION_NUMBER IN NUMBER,
17 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
18 X_ATTRIBUTE1 IN VARCHAR2,
19 X_ATTRIBUTE2 IN VARCHAR2,
20 X_ATTRIBUTE3 IN VARCHAR2,
21 X_ATTRIBUTE4 IN VARCHAR2,
22 X_ATTRIBUTE5 IN VARCHAR2,
23 X_ATTRIBUTE6 IN VARCHAR2,
24 X_ATTRIBUTE7 IN VARCHAR2,
25 X_ATTRIBUTE8 IN VARCHAR2,
26 X_ATTRIBUTE9 IN VARCHAR2,
27 X_ATTRIBUTE10 IN VARCHAR2,
28 X_ATTRIBUTE11 IN VARCHAR2,
29 X_ATTRIBUTE12 IN VARCHAR2,
30 X_ATTRIBUTE13 IN VARCHAR2,
31 X_ATTRIBUTE14 IN VARCHAR2,
32 X_ATTRIBUTE15 IN VARCHAR2,
33 X_SEVERITY_ID IN NUMBER,
34 X_STATUS_ID IN NUMBER,
35 X_RECALL_FLOW_STATUS_ID IN NUMBER,
36 X_WIP_ACCOUNTING_CLASS IN VARCHAR2,
37 X_HOTLINE_NUMBER IN VARCHAR2,
38 X_RECALL_WEBSITE IN VARCHAR2,
39 X_RECALL_REVISION IN NUMBER,
40 X_ESTIMATED_NO_OF_UNITS IN NUMBER,
41 X_CONSUMER_CONTACT IN VARCHAR2,
42 X_DISCOVERY_DATE IN DATE,
43 X_DESCRIPTION IN VARCHAR2,
44 X_DISCOVERY_METHOD IN VARCHAR2,
45 X_INCIDENTS_AND_ISSUES IN VARCHAR2,
46 X_CONSEQUENCE IN VARCHAR2,
47 X_STRATEGY_DESCR IN VARCHAR2,
48 X_REMEDY_ACTIONS IN VARCHAR2,
49 X_REG_AGENCY_COMMENTS IN VARCHAR2,
50 X_RECALL_NAME IN VARCHAR2,
51 X_RECALL_REASON IN VARCHAR2,
52 X_RECALL_RISK IN VARCHAR2,
53 X_CREATION_DATE IN DATE,
54 X_CREATED_BY IN NUMBER,
55 X_LAST_UPDATE_DATE IN DATE,
56 X_LAST_UPDATED_BY IN NUMBER,
57 X_LAST_UPDATE_LOGIN IN NUMBER )
58 IS
59 CURSOR C
60 IS
61 SELECT ROWID FROM CSD_RECALL_HEADERS_B WHERE RECALL_ID = X_RECALL_ID ;
62 BEGIN
63 INSERT
64 INTO CSD_RECALL_HEADERS_B
65 (
66 RECALL_ID,
67 RECALL_NUMBER,
68 REPORTED_DATE,
69 INIT_DATE,
70 COMP_DATE,
71 MANDAT_COMP_DATE,
72 ACT_COMP_DATE,
73 SERVICE_CODES,
74 REG_AGENCY_ID,
75 REG_AGENCY_REF_NO,
76 OBJECT_VERSION_NUMBER,
77 ATTRIBUTE_CATEGORY,
78 ATTRIBUTE1,
79 ATTRIBUTE2,
80 ATTRIBUTE3,
81 ATTRIBUTE4,
82 ATTRIBUTE5,
83 ATTRIBUTE6,
84 ATTRIBUTE7,
85 ATTRIBUTE8,
86 ATTRIBUTE9,
87 ATTRIBUTE10,
88 ATTRIBUTE11,
89 ATTRIBUTE12,
90 ATTRIBUTE13,
91 ATTRIBUTE14,
92 ATTRIBUTE15,
93 SEVERITY_ID,
94 STATUS_ID,
95 RECALL_FLOW_STATUS_ID,
96 WIP_ACCOUNTING_CLASS,
97 HOTLINE_NUMBER,
98 RECALL_WEBSITE,
99 RECALL_REVISION,
100 ESTIMATED_NO_OF_UNITS,
101 CONSUMER_CONTACT,
102 DISCOVERY_DATE,
103 CREATION_DATE,
104 CREATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_LOGIN
108 )
109 VALUES
110 (
111 X_RECALL_ID,
112 X_RECALL_NUMBER,
113 X_REPORTED_DATE,
114 X_INIT_DATE,
115 X_COMP_DATE,
116 X_MANDAT_COMP_DATE,
117 X_ACT_COMP_DATE,
118 X_SERVICE_CODES,
119 X_REG_AGENCY_ID,
120 X_REG_AGENCY_REF_NO,
121 X_OBJECT_VERSION_NUMBER,
122 X_ATTRIBUTE_CATEGORY,
123 X_ATTRIBUTE1,
124 X_ATTRIBUTE2,
125 X_ATTRIBUTE3,
126 X_ATTRIBUTE4,
127 X_ATTRIBUTE5,
128 X_ATTRIBUTE6,
129 X_ATTRIBUTE7,
130 X_ATTRIBUTE8,
131 X_ATTRIBUTE9,
132 X_ATTRIBUTE10,
133 X_ATTRIBUTE11,
134 X_ATTRIBUTE12,
135 X_ATTRIBUTE13,
136 X_ATTRIBUTE14,
137 X_ATTRIBUTE15,
138 X_SEVERITY_ID,
139 X_STATUS_ID,
140 X_RECALL_FLOW_STATUS_ID,
141 X_WIP_ACCOUNTING_CLASS,
142 X_HOTLINE_NUMBER,
143 X_RECALL_WEBSITE,
144 X_RECALL_REVISION,
145 X_ESTIMATED_NO_OF_UNITS,
146 X_CONSUMER_CONTACT,
147 X_DISCOVERY_DATE,
148 X_CREATION_DATE,
149 X_CREATED_BY,
150 X_LAST_UPDATE_DATE,
151 X_LAST_UPDATED_BY,
152 X_LAST_UPDATE_LOGIN
153 );
154 INSERT
155 INTO CSD_RECALL_HEADERS_TL
156 (
157 RECALL_ID,
158 DESCRIPTION,
159 DISCOVERY_METHOD,
160 INCIDENTS_AND_ISSUES,
161 CONSEQUENCE,
162 STRATEGY_DESCR,
163 REMEDY_ACTIONS,
164 REG_AGENCY_COMMENTS,
165 RECALL_NAME,
166 RECALL_REASON,
167 RECALL_RISK,
168 OBJECT_VERSION_NUMBER,
169 CREATED_BY,
170 CREATION_DATE,
171 LAST_UPDATED_BY,
172 LAST_UPDATE_DATE,
173 LAST_UPDATE_LOGIN,
174 LANGUAGE,
175 SOURCE_LANG
176 )
177 SELECT X_RECALL_ID,
178 X_DESCRIPTION,
179 X_DISCOVERY_METHOD,
180 X_INCIDENTS_AND_ISSUES,
181 X_CONSEQUENCE,
182 X_STRATEGY_DESCR,
183 X_REMEDY_ACTIONS,
184 X_REG_AGENCY_COMMENTS,
185 X_RECALL_NAME,
186 X_RECALL_REASON,
187 X_RECALL_RISK,
188 X_OBJECT_VERSION_NUMBER,
189 X_CREATED_BY,
190 X_CREATION_DATE,
191 X_LAST_UPDATED_BY,
192 X_LAST_UPDATE_DATE,
193 X_LAST_UPDATE_LOGIN,
194 L.LANGUAGE_CODE,
195 userenv('LANG')
196 FROM FND_LANGUAGES L
197 WHERE L.INSTALLED_FLAG IN ('I', 'B')
198 AND NOT EXISTS
199 (SELECT NULL
200 FROM CSD_RECALL_HEADERS_TL T
201 WHERE T.RECALL_ID = X_RECALL_ID
202 AND T.LANGUAGE = L.LANGUAGE_CODE
203 );
204
205 OPEN c;
206 FETCH c INTO X_ROWID;
207 IF (c%notfound) THEN
208 CLOSE c;
209 raise no_data_found;
210 END IF;
211 CLOSE c;
212 END INSERT_ROW;
213
214 PROCEDURE LOCK_ROW
215 (
216 X_RECALL_ID IN NUMBER,
217 X_RECALL_NUMBER IN VARCHAR2,
218 X_REPORTED_DATE IN DATE,
219 X_INIT_DATE IN DATE,
220 X_COMP_DATE IN DATE,
221 X_MANDAT_COMP_DATE IN DATE,
222 X_ACT_COMP_DATE IN DATE,
223 X_SERVICE_CODES IN VARCHAR2,
224 X_REG_AGENCY_ID IN VARCHAR2,
225 X_REG_AGENCY_REF_NO IN VARCHAR2,
226 X_OBJECT_VERSION_NUMBER IN NUMBER,
227 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
228 X_ATTRIBUTE1 IN VARCHAR2,
229 X_ATTRIBUTE2 IN VARCHAR2,
230 X_ATTRIBUTE3 IN VARCHAR2,
231 X_ATTRIBUTE4 IN VARCHAR2,
232 X_ATTRIBUTE5 IN VARCHAR2,
233 X_ATTRIBUTE6 IN VARCHAR2,
234 X_ATTRIBUTE7 IN VARCHAR2,
235 X_ATTRIBUTE8 IN VARCHAR2,
236 X_ATTRIBUTE9 IN VARCHAR2,
237 X_ATTRIBUTE10 IN VARCHAR2,
238 X_ATTRIBUTE11 IN VARCHAR2,
239 X_ATTRIBUTE12 IN VARCHAR2,
240 X_ATTRIBUTE13 IN VARCHAR2,
241 X_ATTRIBUTE14 IN VARCHAR2,
242 X_ATTRIBUTE15 IN VARCHAR2,
243 X_SEVERITY_ID IN NUMBER,
244 X_STATUS_ID IN NUMBER,
245 X_RECALL_FLOW_STATUS_ID IN NUMBER,
246 X_WIP_ACCOUNTING_CLASS IN VARCHAR2,
247 X_HOTLINE_NUMBER IN VARCHAR2,
248 X_RECALL_WEBSITE IN VARCHAR2,
249 X_RECALL_REVISION IN NUMBER,
250 X_ESTIMATED_NO_OF_UNITS IN NUMBER,
251 X_CONSUMER_CONTACT IN VARCHAR2,
252 X_DISCOVERY_DATE IN DATE,
253 X_DESCRIPTION IN VARCHAR2,
254 X_DISCOVERY_METHOD IN VARCHAR2,
255 X_INCIDENTS_AND_ISSUES IN VARCHAR2,
256 X_CONSEQUENCE IN VARCHAR2,
257 X_STRATEGY_DESCR IN VARCHAR2,
258 X_REMEDY_ACTIONS IN VARCHAR2,
259 X_REG_AGENCY_COMMENTS IN VARCHAR2,
260 X_RECALL_NAME IN VARCHAR2,
261 X_RECALL_REASON IN VARCHAR2,
262 X_RECALL_RISK IN VARCHAR2 )
263 IS
264 CURSOR c
265 IS
266 SELECT RECALL_NUMBER,
267 REPORTED_DATE,
268 INIT_DATE,
269 COMP_DATE,
270 MANDAT_COMP_DATE,
271 ACT_COMP_DATE,
272 SERVICE_CODES,
273 REG_AGENCY_ID,
274 REG_AGENCY_REF_NO,
275 OBJECT_VERSION_NUMBER,
276 ATTRIBUTE_CATEGORY,
277 ATTRIBUTE1,
278 ATTRIBUTE2,
279 ATTRIBUTE3,
280 ATTRIBUTE4,
281 ATTRIBUTE5,
282 ATTRIBUTE6,
283 ATTRIBUTE7,
284 ATTRIBUTE8,
285 ATTRIBUTE9,
286 ATTRIBUTE10,
287 ATTRIBUTE11,
288 ATTRIBUTE12,
289 ATTRIBUTE13,
290 ATTRIBUTE14,
291 ATTRIBUTE15,
292 SEVERITY_ID,
293 STATUS_ID,
294 RECALL_FLOW_STATUS_ID,
295 WIP_ACCOUNTING_CLASS,
296 HOTLINE_NUMBER,
297 RECALL_WEBSITE,
298 RECALL_REVISION,
299 ESTIMATED_NO_OF_UNITS,
300 CONSUMER_CONTACT,
301 DISCOVERY_DATE
302 FROM CSD_RECALL_HEADERS_B
303 WHERE RECALL_ID = X_RECALL_ID FOR UPDATE OF RECALL_ID nowait;
304
305 recinfo c%rowtype;
306 CURSOR c1
307 IS
308 SELECT DESCRIPTION,
309 DISCOVERY_METHOD,
310 INCIDENTS_AND_ISSUES,
311 CONSEQUENCE,
312 STRATEGY_DESCR,
313 REMEDY_ACTIONS,
314 REG_AGENCY_COMMENTS,
315 RECALL_NAME,
316 RECALL_REASON,
317 RECALL_RISK,
318 DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
319 FROM CSD_RECALL_HEADERS_TL
320 WHERE RECALL_ID = X_RECALL_ID
321 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG) FOR UPDATE OF RECALL_ID nowait;
322 BEGIN
323 OPEN c;
324 FETCH c INTO recinfo;
325 IF (c%notfound) THEN
326 CLOSE c;
327 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
328 app_exception.raise_exception;
329 END IF;
330 CLOSE c;
331 IF ( ((recinfo.RECALL_NUMBER = X_RECALL_NUMBER) OR ((recinfo.RECALL_NUMBER IS NULL) AND (X_RECALL_NUMBER IS NULL)))
332 AND ((recinfo.REPORTED_DATE = X_REPORTED_DATE) OR ((recinfo.REPORTED_DATE IS NULL) AND (X_REPORTED_DATE IS NULL))) AND
333 ((recinfo.INIT_DATE = X_INIT_DATE) OR ((recinfo.INIT_DATE IS NULL) AND (X_INIT_DATE IS NULL))) AND ((recinfo.COMP_DATE = X_COMP_DATE)
334 OR ((recinfo.COMP_DATE IS NULL) AND (X_COMP_DATE IS NULL))) AND ((recinfo.MANDAT_COMP_DATE = X_MANDAT_COMP_DATE)
335 OR ((recinfo.MANDAT_COMP_DATE IS NULL) AND (X_MANDAT_COMP_DATE IS NULL))) AND ((recinfo.ACT_COMP_DATE = X_ACT_COMP_DATE)
336 OR ((recinfo.ACT_COMP_DATE IS NULL) AND (X_ACT_COMP_DATE IS NULL))) AND ((recinfo.SERVICE_CODES = X_SERVICE_CODES)
337 OR ((recinfo.SERVICE_CODES IS NULL) AND (X_SERVICE_CODES IS NULL))) AND ((recinfo.REG_AGENCY_ID = X_REG_AGENCY_ID)
338 OR ((recinfo.REG_AGENCY_ID IS NULL) AND (X_REG_AGENCY_ID IS NULL))) AND ((recinfo.REG_AGENCY_REF_NO = X_REG_AGENCY_REF_NO)
339 OR ((recinfo.REG_AGENCY_REF_NO IS NULL)
340 AND (X_REG_AGENCY_REF_NO IS NULL))) AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
341 OR ((recinfo.OBJECT_VERSION_NUMBER IS NULL) AND (X_OBJECT_VERSION_NUMBER IS NULL))) AND
342 ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY) OR ((recinfo.ATTRIBUTE_CATEGORY IS NULL) AND
343 (X_ATTRIBUTE_CATEGORY IS NULL))) AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1) OR ((recinfo.ATTRIBUTE1 IS NULL) AND
344 (X_ATTRIBUTE1 IS NULL))) AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2) OR ((recinfo.ATTRIBUTE2 IS NULL) AND
345 (X_ATTRIBUTE2 IS NULL))) AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3) OR ((recinfo.ATTRIBUTE3 IS NULL) AND
346 (X_ATTRIBUTE3 IS NULL))) AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4) OR ((recinfo.ATTRIBUTE4 IS NULL) AND
347 (X_ATTRIBUTE4 IS NULL))) AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5) OR ((recinfo.ATTRIBUTE5 IS NULL) AND
348 (X_ATTRIBUTE5 IS NULL))) AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6) OR ((recinfo.ATTRIBUTE6 IS NULL) AND
352 OR ((recinfo.ATTRIBUTE9 IS NULL) AND (X_ATTRIBUTE9 IS NULL))) AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
349 (X_ATTRIBUTE6 IS NULL))) AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7) OR ((
350 recinfo.ATTRIBUTE7 IS NULL) AND (X_ATTRIBUTE7 IS NULL))) AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
351 OR ((recinfo.ATTRIBUTE8 IS NULL) AND (X_ATTRIBUTE8 IS NULL))) AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
353 OR ((recinfo.ATTRIBUTE10 IS NULL) AND (X_ATTRIBUTE10 IS NULL))) AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
354 OR ((recinfo.ATTRIBUTE11 IS NULL) AND (X_ATTRIBUTE11 IS NULL))) AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
355 OR ((recinfo.ATTRIBUTE12 IS NULL) AND (X_ATTRIBUTE12 IS NULL))) AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
356 OR ((recinfo.ATTRIBUTE13 IS NULL) AND (X_ATTRIBUTE13 IS NULL))) AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
357 OR ((recinfo.ATTRIBUTE14 IS NULL) AND (X_ATTRIBUTE14 IS NULL))) AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
358 OR ((recinfo.ATTRIBUTE15 IS NULL) AND (X_ATTRIBUTE15 IS NULL))) AND ((recinfo.SEVERITY_ID = X_SEVERITY_ID)
359 OR ((recinfo.SEVERITY_ID IS NULL)
360 AND (X_SEVERITY_ID IS NULL))) AND ((recinfo.STATUS_ID = X_STATUS_ID) OR ((recinfo.STATUS_ID IS NULL)
361 AND (X_STATUS_ID IS NULL))) AND ((recinfo.RECALL_FLOW_STATUS_ID = X_RECALL_FLOW_STATUS_ID) OR ((recinfo.RECALL_FLOW_STATUS_ID IS NULL)
362 AND (X_RECALL_FLOW_STATUS_ID IS NULL))) AND ((recinfo.WIP_ACCOUNTING_CLASS = X_WIP_ACCOUNTING_CLASS)
363 OR ((recinfo.WIP_ACCOUNTING_CLASS IS NULL) AND (X_WIP_ACCOUNTING_CLASS IS NULL))) AND
364 ((recinfo.HOTLINE_NUMBER = X_HOTLINE_NUMBER) OR ((recinfo.HOTLINE_NUMBER IS NULL) AND
365 (X_HOTLINE_NUMBER IS NULL))) AND ((recinfo.RECALL_WEBSITE = X_RECALL_WEBSITE) OR
366 ((recinfo.RECALL_WEBSITE IS NULL) AND (X_RECALL_WEBSITE IS NULL))) AND
367 ((recinfo.RECALL_REVISION = X_RECALL_REVISION) OR ((recinfo.RECALL_REVISION IS NULL) AND
368 (X_RECALL_REVISION IS NULL))) AND ((recinfo.ESTIMATED_NO_OF_UNITS = X_ESTIMATED_NO_OF_UNITS) OR
369 ((recinfo.ESTIMATED_NO_OF_UNITS IS NULL) AND (X_ESTIMATED_NO_OF_UNITS IS NULL))) AND
370 ((recinfo.CONSUMER_CONTACT = X_CONSUMER_CONTACT) OR (
371 (recinfo.CONSUMER_CONTACT IS NULL) AND (X_CONSUMER_CONTACT IS NULL))) AND
372 ((recinfo.DISCOVERY_DATE = X_DISCOVERY_DATE) OR ((recinfo.DISCOVERY_DATE IS NULL) AND (X_DISCOVERY_DATE IS NULL))) ) THEN
373 NULL;
374 ELSE
375 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
376 app_exception.raise_exception;
377 END IF;
378 FOR tlinfo IN c1
379 LOOP
380 IF (tlinfo.BASELANG = 'Y') THEN
381 IF ( ((tlinfo.DESCRIPTION = X_DESCRIPTION) OR ((tlinfo.DESCRIPTION IS NULL) AND (X_DESCRIPTION IS NULL)))
382 AND ((tlinfo.DISCOVERY_METHOD = X_DISCOVERY_METHOD) OR ((tlinfo.DISCOVERY_METHOD IS NULL) AND (X_DISCOVERY_METHOD IS NULL)))
383 AND ((tlinfo.INCIDENTS_AND_ISSUES = X_INCIDENTS_AND_ISSUES) OR ((tlinfo.INCIDENTS_AND_ISSUES IS NULL) AND (X_INCIDENTS_AND_ISSUES IS NULL)))
384 AND ((tlinfo.CONSEQUENCE = X_CONSEQUENCE) OR ((tlinfo.CONSEQUENCE IS NULL) AND (X_CONSEQUENCE IS NULL)))
385 AND ((tlinfo.STRATEGY_DESCR = X_STRATEGY_DESCR) OR ((tlinfo.STRATEGY_DESCR IS NULL) AND (X_STRATEGY_DESCR IS NULL)))
386 AND ((tlinfo.REMEDY_ACTIONS = X_REMEDY_ACTIONS) OR ((tlinfo.REMEDY_ACTIONS IS NULL) AND (X_REMEDY_ACTIONS IS NULL)))
387 AND ((tlinfo.REG_AGENCY_COMMENTS = X_REG_AGENCY_COMMENTS) OR ((tlinfo.REG_AGENCY_COMMENTS IS NULL) AND (X_REG_AGENCY_COMMENTS IS NULL)))
388 AND ((tlinfo.RECALL_NAME = X_RECALL_NAME) OR ((tlinfo.RECALL_NAME IS NULL) AND (X_RECALL_NAME IS NULL))) AND ((tlinfo.RECALL_REASON =
389 X_RECALL_REASON) OR ((tlinfo.RECALL_REASON IS NULL) AND (X_RECALL_REASON IS NULL))) AND ((tlinfo.RECALL_RISK = X_RECALL_RISK)
390 OR ((tlinfo.RECALL_RISK IS NULL) AND (X_RECALL_RISK IS NULL))) ) THEN
391 NULL;
392 ELSE
393 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
394 app_exception.raise_exception;
395 END IF;
396 END IF;
397 END LOOP;
398 RETURN;
399 END LOCK_ROW;
400
401 PROCEDURE UPDATE_ROW
402 (
403 X_RECALL_ID IN NUMBER,
404 X_RECALL_NUMBER IN VARCHAR2,
405 X_REPORTED_DATE IN DATE,
406 X_INIT_DATE IN DATE,
407 X_COMP_DATE IN DATE,
408 X_MANDAT_COMP_DATE IN DATE,
409 X_ACT_COMP_DATE IN DATE,
410 X_SERVICE_CODES IN VARCHAR2,
411 X_REG_AGENCY_ID IN VARCHAR2,
412 X_REG_AGENCY_REF_NO IN VARCHAR2,
413 X_OBJECT_VERSION_NUMBER IN NUMBER,
414 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
415 X_ATTRIBUTE1 IN VARCHAR2,
416 X_ATTRIBUTE2 IN VARCHAR2,
417 X_ATTRIBUTE3 IN VARCHAR2,
418 X_ATTRIBUTE4 IN VARCHAR2,
419 X_ATTRIBUTE5 IN VARCHAR2,
420 X_ATTRIBUTE6 IN VARCHAR2,
421 X_ATTRIBUTE7 IN VARCHAR2,
422 X_ATTRIBUTE8 IN VARCHAR2,
423 X_ATTRIBUTE9 IN VARCHAR2,
424 X_ATTRIBUTE10 IN VARCHAR2,
425 X_ATTRIBUTE11 IN VARCHAR2,
426 X_ATTRIBUTE12 IN VARCHAR2,
427 X_ATTRIBUTE13 IN VARCHAR2,
428 X_ATTRIBUTE14 IN VARCHAR2,
429 X_ATTRIBUTE15 IN VARCHAR2,
430 X_SEVERITY_ID IN NUMBER,
431 X_STATUS_ID IN NUMBER,
432 X_RECALL_FLOW_STATUS_ID IN NUMBER,
433 X_WIP_ACCOUNTING_CLASS IN VARCHAR2,
434 X_HOTLINE_NUMBER IN VARCHAR2,
435 X_RECALL_WEBSITE IN VARCHAR2,
436 X_RECALL_REVISION IN NUMBER,
437 X_ESTIMATED_NO_OF_UNITS IN NUMBER,
438 X_CONSUMER_CONTACT IN VARCHAR2,
439 X_DISCOVERY_DATE IN DATE,
440 X_DESCRIPTION IN VARCHAR2,
441 X_DISCOVERY_METHOD IN VARCHAR2,
442 X_INCIDENTS_AND_ISSUES IN VARCHAR2,
443 X_CONSEQUENCE IN VARCHAR2,
444 X_STRATEGY_DESCR IN VARCHAR2,
448 X_RECALL_REASON IN VARCHAR2,
445 X_REMEDY_ACTIONS IN VARCHAR2,
446 X_REG_AGENCY_COMMENTS IN VARCHAR2,
447 X_RECALL_NAME IN VARCHAR2,
449 X_RECALL_RISK IN VARCHAR2,
450 X_LAST_UPDATE_DATE IN DATE,
451 X_LAST_UPDATED_BY IN NUMBER,
452 X_LAST_UPDATE_LOGIN IN NUMBER )
453 IS
454 BEGIN
455 UPDATE CSD_RECALL_HEADERS_B
456 SET RECALL_NUMBER = X_RECALL_NUMBER,
457 REPORTED_DATE = X_REPORTED_DATE,
458 INIT_DATE = X_INIT_DATE,
459 COMP_DATE = X_COMP_DATE,
460 MANDAT_COMP_DATE = X_MANDAT_COMP_DATE,
461 ACT_COMP_DATE = X_ACT_COMP_DATE,
462 SERVICE_CODES = X_SERVICE_CODES,
463 REG_AGENCY_ID = X_REG_AGENCY_ID,
464 REG_AGENCY_REF_NO = X_REG_AGENCY_REF_NO,
465 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
466 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
467 ATTRIBUTE1 = X_ATTRIBUTE1,
468 ATTRIBUTE2 = X_ATTRIBUTE2,
469 ATTRIBUTE3 = X_ATTRIBUTE3,
470 ATTRIBUTE4 = X_ATTRIBUTE4,
471 ATTRIBUTE5 = X_ATTRIBUTE5,
472 ATTRIBUTE6 = X_ATTRIBUTE6,
473 ATTRIBUTE7 = X_ATTRIBUTE7,
474 ATTRIBUTE8 = X_ATTRIBUTE8,
475 ATTRIBUTE9 = X_ATTRIBUTE9,
476 ATTRIBUTE10 = X_ATTRIBUTE10,
477 ATTRIBUTE11 = X_ATTRIBUTE11,
478 ATTRIBUTE12 = X_ATTRIBUTE12,
479 ATTRIBUTE13 = X_ATTRIBUTE13,
480 ATTRIBUTE14 = X_ATTRIBUTE14,
481 ATTRIBUTE15 = X_ATTRIBUTE15,
482 SEVERITY_ID = X_SEVERITY_ID,
483 STATUS_ID = X_STATUS_ID,
484 RECALL_FLOW_STATUS_ID = X_RECALL_FLOW_STATUS_ID,
485 WIP_ACCOUNTING_CLASS = X_WIP_ACCOUNTING_CLASS,
486 HOTLINE_NUMBER = X_HOTLINE_NUMBER,
487 RECALL_WEBSITE = X_RECALL_WEBSITE,
488 RECALL_REVISION = X_RECALL_REVISION,
489 ESTIMATED_NO_OF_UNITS = X_ESTIMATED_NO_OF_UNITS,
490 CONSUMER_CONTACT = X_CONSUMER_CONTACT,
491 DISCOVERY_DATE = X_DISCOVERY_DATE,
492 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
493 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
494 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
495 WHERE RECALL_ID = X_RECALL_ID;
496 IF (sql%notfound) THEN
497 raise no_data_found;
498 END IF;
499 UPDATE CSD_RECALL_HEADERS_TL
500 SET DESCRIPTION = X_DESCRIPTION,
501 DISCOVERY_METHOD = X_DISCOVERY_METHOD,
502 INCIDENTS_AND_ISSUES = X_INCIDENTS_AND_ISSUES,
503 CONSEQUENCE = X_CONSEQUENCE,
504 STRATEGY_DESCR = X_STRATEGY_DESCR,
505 REMEDY_ACTIONS = X_REMEDY_ACTIONS,
506 REG_AGENCY_COMMENTS = X_REG_AGENCY_COMMENTS,
507 RECALL_NAME = X_RECALL_NAME,
508 RECALL_REASON = X_RECALL_REASON,
509 RECALL_RISK = X_RECALL_RISK,
510 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
511 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
512 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
513 SOURCE_LANG = userenv('LANG')
514 WHERE RECALL_ID = X_RECALL_ID
515 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
516 IF (sql%notfound) THEN
517 raise no_data_found;
518 END IF;
519 END UPDATE_ROW;
520
521 PROCEDURE DELETE_ROW
522 (
523 X_RECALL_ID IN NUMBER )
524 IS
525 BEGIN
526 DELETE FROM CSD_RECALL_HEADERS_TL WHERE RECALL_ID = X_RECALL_ID;
527 IF (sql%notfound) THEN
528 raise no_data_found;
529 END IF;
530 DELETE FROM CSD_RECALL_HEADERS_B WHERE RECALL_ID = X_RECALL_ID;
531 IF (sql%notfound) THEN
532 raise no_data_found;
533 END IF;
534 END DELETE_ROW;
535
536 PROCEDURE ADD_LANGUAGE
537 IS
538 BEGIN
539 DELETE
540 FROM CSD_RECALL_HEADERS_TL T
541 WHERE NOT EXISTS
542 (SELECT NULL FROM CSD_RECALL_HEADERS_B B WHERE B.RECALL_ID = T.RECALL_ID
543 );
544 UPDATE CSD_RECALL_HEADERS_TL T
545 SET
546 (
547 DESCRIPTION,
548 DISCOVERY_METHOD,
549 INCIDENTS_AND_ISSUES,
550 CONSEQUENCE,
551 STRATEGY_DESCR,
552 REMEDY_ACTIONS,
553 REG_AGENCY_COMMENTS,
554 RECALL_NAME,
555 RECALL_REASON,
556 RECALL_RISK
557 )
558 =
559 (SELECT B.DESCRIPTION,
560 B.DISCOVERY_METHOD,
561 B.INCIDENTS_AND_ISSUES,
562 B.CONSEQUENCE,
563 B.STRATEGY_DESCR,
564 B.REMEDY_ACTIONS,
565 B.REG_AGENCY_COMMENTS,
566 B.RECALL_NAME,
567 B.RECALL_REASON,
568 B.RECALL_RISK
569 FROM CSD_RECALL_HEADERS_TL B
570 WHERE B.RECALL_ID = T.RECALL_ID
571 AND B.LANGUAGE = T.SOURCE_LANG
572 )
573 WHERE ( T.RECALL_ID, T.LANGUAGE ) IN
574 (SELECT SUBT.RECALL_ID,
575 SUBT.LANGUAGE
576 FROM CSD_RECALL_HEADERS_TL SUBB,
577 CSD_RECALL_HEADERS_TL SUBT
578 WHERE SUBB.RECALL_ID = SUBT.RECALL_ID
579 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
580 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
581 OR (SUBB.DESCRIPTION IS NULL
582 AND SUBT.DESCRIPTION IS NOT NULL)
583 OR (SUBB.DESCRIPTION IS NOT NULL
584 AND SUBT.DESCRIPTION IS NULL)
585 OR SUBB.DISCOVERY_METHOD <> SUBT.DISCOVERY_METHOD
586 OR (SUBB.DISCOVERY_METHOD IS NULL
587 AND SUBT.DISCOVERY_METHOD IS NOT NULL)
588 OR (SUBB.DISCOVERY_METHOD IS NOT NULL
589 AND SUBT.DISCOVERY_METHOD IS NULL)
590 OR SUBB.INCIDENTS_AND_ISSUES <> SUBT.INCIDENTS_AND_ISSUES
591 OR (SUBB.INCIDENTS_AND_ISSUES IS NULL
595 OR SUBB.CONSEQUENCE <> SUBT.CONSEQUENCE
592 AND SUBT.INCIDENTS_AND_ISSUES IS NOT NULL)
593 OR (SUBB.INCIDENTS_AND_ISSUES IS NOT NULL
594 AND SUBT.INCIDENTS_AND_ISSUES IS NULL)
596 OR (SUBB.CONSEQUENCE IS NULL
597 AND SUBT.CONSEQUENCE IS NOT NULL)
598 OR (SUBB.CONSEQUENCE IS NOT NULL
599 AND SUBT.CONSEQUENCE IS NULL)
600 OR SUBB.STRATEGY_DESCR <> SUBT.STRATEGY_DESCR
601 OR (SUBB.STRATEGY_DESCR IS NULL
602 AND SUBT.STRATEGY_DESCR IS NOT NULL)
603 OR (SUBB.STRATEGY_DESCR IS NOT NULL
604 AND SUBT.STRATEGY_DESCR IS NULL)
605 OR SUBB.REMEDY_ACTIONS <> SUBT.REMEDY_ACTIONS
606 OR (SUBB.REMEDY_ACTIONS IS NULL
607 AND SUBT.REMEDY_ACTIONS IS NOT NULL)
608 OR (SUBB.REMEDY_ACTIONS IS NOT NULL
609 AND SUBT.REMEDY_ACTIONS IS NULL)
610 OR SUBB.REG_AGENCY_COMMENTS <> SUBT.REG_AGENCY_COMMENTS
611 OR (SUBB.REG_AGENCY_COMMENTS IS NULL
612 AND SUBT.REG_AGENCY_COMMENTS IS NOT NULL)
613 OR (SUBB.REG_AGENCY_COMMENTS IS NOT NULL
614 AND SUBT.REG_AGENCY_COMMENTS IS NULL)
615 OR SUBB.RECALL_NAME <> SUBT.RECALL_NAME
616 OR (SUBB.RECALL_NAME IS NULL
617 AND SUBT.RECALL_NAME IS NOT NULL)
618 OR (SUBB.RECALL_NAME IS NOT NULL
619 AND SUBT.RECALL_NAME IS NULL)
620 OR SUBB.RECALL_REASON <> SUBT.RECALL_REASON
621 OR (SUBB.RECALL_REASON IS NULL
622 AND SUBT.RECALL_REASON IS NOT NULL)
623 OR (SUBB.RECALL_REASON IS NOT NULL
624 AND SUBT.RECALL_REASON IS NULL)
625 OR SUBB.RECALL_RISK <> SUBT.RECALL_RISK
626 OR (SUBB.RECALL_RISK IS NULL
627 AND SUBT.RECALL_RISK IS NOT NULL)
628 OR (SUBB.RECALL_RISK IS NOT NULL
629 AND SUBT.RECALL_RISK IS NULL) )
630 );
631
632 INSERT
633 INTO CSD_RECALL_HEADERS_TL
634 (
635 RECALL_ID,
636 DESCRIPTION,
637 DISCOVERY_METHOD,
638 INCIDENTS_AND_ISSUES,
639 CONSEQUENCE,
640 STRATEGY_DESCR,
641 REMEDY_ACTIONS,
642 REG_AGENCY_COMMENTS,
643 RECALL_NAME,
644 RECALL_REASON,
645 RECALL_RISK,
646 OBJECT_VERSION_NUMBER,
647 CREATED_BY,
648 CREATION_DATE,
649 LAST_UPDATED_BY,
650 LAST_UPDATE_DATE,
651 LAST_UPDATE_LOGIN,
652 LANGUAGE,
653 SOURCE_LANG
654 )
655 SELECT
656 /*+ ORDERED */
657 B.RECALL_ID,
658 B.DESCRIPTION,
659 B.DISCOVERY_METHOD,
660 B.INCIDENTS_AND_ISSUES,
661 B.CONSEQUENCE,
662 B.STRATEGY_DESCR,
663 B.REMEDY_ACTIONS,
664 B.REG_AGENCY_COMMENTS,
665 B.RECALL_NAME,
666 B.RECALL_REASON,
667 B.RECALL_RISK,
668 B.OBJECT_VERSION_NUMBER,
669 B.CREATED_BY,
670 B.CREATION_DATE,
671 B.LAST_UPDATED_BY,
672 B.LAST_UPDATE_DATE,
673 B.LAST_UPDATE_LOGIN,
674 L.LANGUAGE_CODE,
675 B.SOURCE_LANG
676 FROM CSD_RECALL_HEADERS_TL B,
677 FND_LANGUAGES L
678 WHERE L.INSTALLED_FLAG IN ('I', 'B')
679 AND B.LANGUAGE = userenv('LANG')
680 AND NOT EXISTS
681 (SELECT NULL
682 FROM CSD_RECALL_HEADERS_TL T
683 WHERE T.RECALL_ID = B.RECALL_ID
684 AND T.LANGUAGE = L.LANGUAGE_CODE
685 );
686
687 END ADD_LANGUAGE;
688
689 END CSD_RECALL_HEADERS_PKG;
[Home] [Help]