1 package body GMD_SAMPLING_PLANS_PVT as
2 /* $Header: GMDVSPNB.pls 120.0 2005/05/25 19:56:00 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SAMPLING_PLAN_ID in NUMBER,
6 X_ATTRIBUTE28 in VARCHAR2,
7 X_ATTRIBUTE29 in VARCHAR2,
8 X_ATTRIBUTE30 in VARCHAR2,
9 X_ATTRIBUTE7 in VARCHAR2,
10 X_ATTRIBUTE8 in VARCHAR2,
11 X_ATTRIBUTE9 in VARCHAR2,
12 X_ATTRIBUTE10 in VARCHAR2,
13 X_ATTRIBUTE11 in VARCHAR2,
14 X_ATTRIBUTE12 in VARCHAR2,
15 X_ATTRIBUTE13 in VARCHAR2,
16 X_ATTRIBUTE14 in VARCHAR2,
17 X_ATTRIBUTE15 in VARCHAR2,
18 X_ATTRIBUTE16 in VARCHAR2,
19 X_ATTRIBUTE17 in VARCHAR2,
20 X_ATTRIBUTE18 in VARCHAR2,
21 X_ATTRIBUTE19 in VARCHAR2,
22 X_ATTRIBUTE20 in VARCHAR2,
23 X_ATTRIBUTE21 in VARCHAR2,
24 X_ATTRIBUTE22 in VARCHAR2,
25 X_ATTRIBUTE23 in VARCHAR2,
26 X_ATTRIBUTE24 in VARCHAR2,
27 X_ATTRIBUTE25 in VARCHAR2,
28 X_ATTRIBUTE26 in VARCHAR2,
29 X_ATTRIBUTE27 in VARCHAR2,
30 X_SAMPLING_PLAN_NAME in VARCHAR2,
31 X_SAMPLE_CNT_REQ in NUMBER,
32 X_SAMPLE_QTY in NUMBER,
33 X_SAMPLE_QTY_UOM in VARCHAR2,
34 X_FREQUENCY_TYPE in VARCHAR2,
35 X_FREQUENCY_CNT in NUMBER,
36 X_FREQUENCY_PER in VARCHAR2,
37 X_RESERVE_CNT_REQ in NUMBER,
38 X_RESERVE_QTY in NUMBER,
39 X_ARCHIVE_CNT_REQ in NUMBER,
40 X_ARCHIVE_QTY in NUMBER,
41 X_DELETE_MARK in NUMBER,
42 X_TEXT_CODE in NUMBER,
43 X_ATTRIBUTE_CATEGORY in VARCHAR2,
44 X_ATTRIBUTE1 in VARCHAR2,
45 X_ATTRIBUTE2 in VARCHAR2,
46 X_ATTRIBUTE3 in VARCHAR2,
47 X_ATTRIBUTE4 in VARCHAR2,
48 X_ATTRIBUTE5 in VARCHAR2,
49 X_ATTRIBUTE6 in VARCHAR2,
50 X_SAMPLING_PLAN_DESC in VARCHAR2,
51 X_CREATION_DATE in DATE,
52 X_CREATED_BY in NUMBER,
53 X_LAST_UPDATE_DATE in DATE,
54 X_LAST_UPDATED_BY in NUMBER,
55 X_LAST_UPDATE_LOGIN in NUMBER
56 ) is
57 cursor C is select ROWID from GMD_SAMPLING_PLANS_B
58 where SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID
59 ;
60 begin
61 insert into GMD_SAMPLING_PLANS_B (
62 ATTRIBUTE28,
63 ATTRIBUTE29,
64 ATTRIBUTE30,
65 ATTRIBUTE7,
66 ATTRIBUTE8,
67 ATTRIBUTE9,
68 ATTRIBUTE10,
69 ATTRIBUTE11,
70 ATTRIBUTE12,
71 ATTRIBUTE13,
72 ATTRIBUTE14,
73 ATTRIBUTE15,
74 ATTRIBUTE16,
75 ATTRIBUTE17,
76 ATTRIBUTE18,
77 ATTRIBUTE19,
78 ATTRIBUTE20,
79 ATTRIBUTE21,
80 ATTRIBUTE22,
81 ATTRIBUTE23,
82 ATTRIBUTE24,
83 ATTRIBUTE25,
84 ATTRIBUTE26,
85 ATTRIBUTE27,
86 SAMPLING_PLAN_ID,
87 SAMPLING_PLAN_NAME,
88 SAMPLE_CNT_REQ,
89 SAMPLE_QTY,
90 SAMPLE_QTY_UOM,
91 FREQUENCY_TYPE,
92 FREQUENCY_CNT,
93 FREQUENCY_PER,
94 RESERVE_CNT_REQ,
95 RESERVE_QTY,
96 ARCHIVE_CNT_REQ,
97 ARCHIVE_QTY,
98 DELETE_MARK,
99 TEXT_CODE,
100 ATTRIBUTE_CATEGORY,
101 ATTRIBUTE1,
102 ATTRIBUTE2,
103 ATTRIBUTE3,
104 ATTRIBUTE4,
105 ATTRIBUTE5,
106 ATTRIBUTE6,
107 CREATION_DATE,
108 CREATED_BY,
109 LAST_UPDATE_DATE,
110 LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN
112 ) values (
113 X_ATTRIBUTE28,
114 X_ATTRIBUTE29,
115 X_ATTRIBUTE30,
116 X_ATTRIBUTE7,
117 X_ATTRIBUTE8,
118 X_ATTRIBUTE9,
119 X_ATTRIBUTE10,
120 X_ATTRIBUTE11,
121 X_ATTRIBUTE12,
122 X_ATTRIBUTE13,
123 X_ATTRIBUTE14,
124 X_ATTRIBUTE15,
125 X_ATTRIBUTE16,
126 X_ATTRIBUTE17,
127 X_ATTRIBUTE18,
128 X_ATTRIBUTE19,
129 X_ATTRIBUTE20,
130 X_ATTRIBUTE21,
131 X_ATTRIBUTE22,
132 X_ATTRIBUTE23,
133 X_ATTRIBUTE24,
134 X_ATTRIBUTE25,
135 X_ATTRIBUTE26,
136 X_ATTRIBUTE27,
137 X_SAMPLING_PLAN_ID,
138 X_SAMPLING_PLAN_NAME,
139 X_SAMPLE_CNT_REQ,
140 X_SAMPLE_QTY,
141 X_SAMPLE_QTY_UOM,
142 X_FREQUENCY_TYPE,
143 X_FREQUENCY_CNT,
144 X_FREQUENCY_PER,
145 X_RESERVE_CNT_REQ,
146 X_RESERVE_QTY,
147 X_ARCHIVE_CNT_REQ,
148 X_ARCHIVE_QTY,
149 X_DELETE_MARK,
150 X_TEXT_CODE,
151 X_ATTRIBUTE_CATEGORY,
152 X_ATTRIBUTE1,
153 X_ATTRIBUTE2,
154 X_ATTRIBUTE3,
155 X_ATTRIBUTE4,
156 X_ATTRIBUTE5,
157 X_ATTRIBUTE6,
158 X_CREATION_DATE,
159 X_CREATED_BY,
160 X_LAST_UPDATE_DATE,
161 X_LAST_UPDATED_BY,
162 X_LAST_UPDATE_LOGIN
163 );
164
165 insert into GMD_SAMPLING_PLANS_TL (
166 SAMPLING_PLAN_ID,
167 LAST_UPDATE_DATE,
168 LAST_UPDATE_LOGIN,
169 SAMPLING_PLAN_DESC,
170 CREATION_DATE,
171 CREATED_BY,
172 LAST_UPDATED_BY,
173 LANGUAGE,
174 SOURCE_LANG
175 ) select
176 X_SAMPLING_PLAN_ID,
177 X_LAST_UPDATE_DATE,
178 X_LAST_UPDATE_LOGIN,
179 X_SAMPLING_PLAN_DESC,
180 X_CREATION_DATE,
181 X_CREATED_BY,
182 X_LAST_UPDATED_BY,
183 L.LANGUAGE_CODE,
184 userenv('LANG')
185 from FND_LANGUAGES L
186 where L.INSTALLED_FLAG in ('I', 'B')
187 and not exists
188 (select NULL
189 from GMD_SAMPLING_PLANS_TL T
190 where T.SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID
191 and T.LANGUAGE = L.LANGUAGE_CODE);
192
193 open c;
194 fetch c into X_ROWID;
195 if (c%notfound) then
196 close c;
197 raise no_data_found;
198 end if;
199 close c;
200
201 end INSERT_ROW;
202
203 procedure LOCK_ROW (
204 X_SAMPLING_PLAN_ID in NUMBER,
205 X_ATTRIBUTE28 in VARCHAR2,
206 X_ATTRIBUTE29 in VARCHAR2,
207 X_ATTRIBUTE30 in VARCHAR2,
208 X_ATTRIBUTE7 in VARCHAR2,
209 X_ATTRIBUTE8 in VARCHAR2,
210 X_ATTRIBUTE9 in VARCHAR2,
211 X_ATTRIBUTE10 in VARCHAR2,
212 X_ATTRIBUTE11 in VARCHAR2,
213 X_ATTRIBUTE12 in VARCHAR2,
214 X_ATTRIBUTE13 in VARCHAR2,
215 X_ATTRIBUTE14 in VARCHAR2,
216 X_ATTRIBUTE15 in VARCHAR2,
217 X_ATTRIBUTE16 in VARCHAR2,
218 X_ATTRIBUTE17 in VARCHAR2,
219 X_ATTRIBUTE18 in VARCHAR2,
220 X_ATTRIBUTE19 in VARCHAR2,
221 X_ATTRIBUTE20 in VARCHAR2,
222 X_ATTRIBUTE21 in VARCHAR2,
223 X_ATTRIBUTE22 in VARCHAR2,
224 X_ATTRIBUTE23 in VARCHAR2,
225 X_ATTRIBUTE24 in VARCHAR2,
226 X_ATTRIBUTE25 in VARCHAR2,
227 X_ATTRIBUTE26 in VARCHAR2,
228 X_ATTRIBUTE27 in VARCHAR2,
229 X_SAMPLING_PLAN_NAME in VARCHAR2,
230 X_SAMPLE_CNT_REQ in NUMBER,
231 X_SAMPLE_QTY in NUMBER,
232 X_SAMPLE_QTY_UOM in VARCHAR2,
233 X_FREQUENCY_TYPE in VARCHAR2,
234 X_FREQUENCY_CNT in NUMBER,
235 X_FREQUENCY_PER in VARCHAR2,
236 X_RESERVE_CNT_REQ in NUMBER,
237 X_RESERVE_QTY in NUMBER,
238 X_ARCHIVE_CNT_REQ in NUMBER,
239 X_ARCHIVE_QTY in NUMBER,
240 X_DELETE_MARK in NUMBER,
241 X_TEXT_CODE in NUMBER,
242 X_ATTRIBUTE_CATEGORY in VARCHAR2,
243 X_ATTRIBUTE1 in VARCHAR2,
244 X_ATTRIBUTE2 in VARCHAR2,
245 X_ATTRIBUTE3 in VARCHAR2,
246 X_ATTRIBUTE4 in VARCHAR2,
247 X_ATTRIBUTE5 in VARCHAR2,
248 X_ATTRIBUTE6 in VARCHAR2,
249 X_SAMPLING_PLAN_DESC in VARCHAR2
250 ) is
251 cursor c is select
252 ATTRIBUTE28,
253 ATTRIBUTE29,
254 ATTRIBUTE30,
255 ATTRIBUTE7,
256 ATTRIBUTE8,
257 ATTRIBUTE9,
258 ATTRIBUTE10,
259 ATTRIBUTE11,
260 ATTRIBUTE12,
261 ATTRIBUTE13,
262 ATTRIBUTE14,
263 ATTRIBUTE15,
264 ATTRIBUTE16,
265 ATTRIBUTE17,
266 ATTRIBUTE18,
267 ATTRIBUTE19,
268 ATTRIBUTE20,
269 ATTRIBUTE21,
270 ATTRIBUTE22,
271 ATTRIBUTE23,
272 ATTRIBUTE24,
273 ATTRIBUTE25,
274 ATTRIBUTE26,
275 ATTRIBUTE27,
276 SAMPLING_PLAN_NAME,
277 SAMPLE_CNT_REQ,
278 SAMPLE_QTY,
279 SAMPLE_QTY_UOM,
280 FREQUENCY_TYPE,
281 FREQUENCY_CNT,
282 FREQUENCY_PER,
283 RESERVE_CNT_REQ,
284 RESERVE_QTY,
285 ARCHIVE_CNT_REQ,
286 ARCHIVE_QTY,
287 DELETE_MARK,
288 TEXT_CODE,
289 ATTRIBUTE_CATEGORY,
290 ATTRIBUTE1,
291 ATTRIBUTE2,
292 ATTRIBUTE3,
293 ATTRIBUTE4,
294 ATTRIBUTE5,
295 ATTRIBUTE6
296 from GMD_SAMPLING_PLANS_B
297 where SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID
298 for update of SAMPLING_PLAN_ID nowait;
299 recinfo c%rowtype;
300
301 cursor c1 is select
302 SAMPLING_PLAN_DESC,
303 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
304 from GMD_SAMPLING_PLANS_TL
305 where SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID
306 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
307 for update of SAMPLING_PLAN_ID nowait;
308 begin
309 open c;
310 fetch c into recinfo;
311 if (c%notfound) then
312 close c;
313 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
314 app_exception.raise_exception;
315 end if;
316 close c;
317
318 if ( ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
319 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
320 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
321 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
322 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
323 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
324 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
325 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
326 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
327 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
328 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
329 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
330 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
331 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
332 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
333 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
334 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
335 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
336 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
337 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
338 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
339 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
340 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
341 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
342 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
343 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
344 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
345 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
346 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
347 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
348 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
349 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
350 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
351 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
352 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
353 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
354 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
355 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
356 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
357 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
358 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
359 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
360 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
361 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
362 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
363 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
364 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
365 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
366 AND (recinfo.SAMPLING_PLAN_NAME = X_SAMPLING_PLAN_NAME)
367 AND ((recinfo.SAMPLE_CNT_REQ = X_SAMPLE_CNT_REQ)
368 OR ((recinfo.SAMPLE_CNT_REQ is null) AND (X_SAMPLE_CNT_REQ is null)))
369 AND ((recinfo.SAMPLE_QTY = X_SAMPLE_QTY)
370 OR ((recinfo.SAMPLE_QTY is null) AND (X_SAMPLE_QTY is null)))
371 AND (recinfo.SAMPLE_QTY_UOM = X_SAMPLE_QTY_UOM)
372 AND ((recinfo.FREQUENCY_TYPE = X_FREQUENCY_TYPE)
373 OR ((recinfo.FREQUENCY_TYPE is null) AND (X_FREQUENCY_TYPE is null)))
374 AND ((recinfo.FREQUENCY_CNT = X_FREQUENCY_CNT)
375 OR ((recinfo.FREQUENCY_CNT is null) AND (X_FREQUENCY_CNT is null)))
376 AND ((recinfo.FREQUENCY_PER = X_FREQUENCY_PER)
377 OR ((recinfo.FREQUENCY_PER is null) AND (X_FREQUENCY_PER is null)))
378 AND ((recinfo.RESERVE_CNT_REQ = X_RESERVE_CNT_REQ)
379 OR ((recinfo.RESERVE_CNT_REQ is null) AND (X_RESERVE_CNT_REQ is null)))
380 AND ((recinfo.RESERVE_QTY = X_RESERVE_QTY)
381 OR ((recinfo.RESERVE_QTY is null) AND (X_RESERVE_QTY is null)))
382 AND ((recinfo.ARCHIVE_CNT_REQ = X_ARCHIVE_CNT_REQ)
383 OR ((recinfo.ARCHIVE_CNT_REQ is null) AND (X_ARCHIVE_CNT_REQ is null)))
384 AND ((recinfo.ARCHIVE_QTY = X_ARCHIVE_QTY)
385 OR ((recinfo.ARCHIVE_QTY is null) AND (X_ARCHIVE_QTY is null)))
386 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
387 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
388 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
389 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
390 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
391 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
392 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
393 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
394 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
395 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
396 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
397 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
398 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
399 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
400 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
401 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
402 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
403 ) then
404 null;
405 else
406 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
407 app_exception.raise_exception;
408 end if;
409
410 for tlinfo in c1 loop
411 if (tlinfo.BASELANG = 'Y') then
412 if ( (tlinfo.SAMPLING_PLAN_DESC = X_SAMPLING_PLAN_DESC)
413 ) then
414 null;
415 else
416 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
417 app_exception.raise_exception;
418 end if;
419 end if;
420 end loop;
421 return;
422 end LOCK_ROW;
423
424 procedure UPDATE_ROW (
425 X_SAMPLING_PLAN_ID in NUMBER,
426 X_ATTRIBUTE28 in VARCHAR2,
427 X_ATTRIBUTE29 in VARCHAR2,
428 X_ATTRIBUTE30 in VARCHAR2,
429 X_ATTRIBUTE7 in VARCHAR2,
430 X_ATTRIBUTE8 in VARCHAR2,
431 X_ATTRIBUTE9 in VARCHAR2,
432 X_ATTRIBUTE10 in VARCHAR2,
433 X_ATTRIBUTE11 in VARCHAR2,
434 X_ATTRIBUTE12 in VARCHAR2,
435 X_ATTRIBUTE13 in VARCHAR2,
436 X_ATTRIBUTE14 in VARCHAR2,
437 X_ATTRIBUTE15 in VARCHAR2,
438 X_ATTRIBUTE16 in VARCHAR2,
439 X_ATTRIBUTE17 in VARCHAR2,
440 X_ATTRIBUTE18 in VARCHAR2,
441 X_ATTRIBUTE19 in VARCHAR2,
442 X_ATTRIBUTE20 in VARCHAR2,
443 X_ATTRIBUTE21 in VARCHAR2,
444 X_ATTRIBUTE22 in VARCHAR2,
445 X_ATTRIBUTE23 in VARCHAR2,
446 X_ATTRIBUTE24 in VARCHAR2,
447 X_ATTRIBUTE25 in VARCHAR2,
448 X_ATTRIBUTE26 in VARCHAR2,
449 X_ATTRIBUTE27 in VARCHAR2,
450 X_SAMPLING_PLAN_NAME in VARCHAR2,
451 X_SAMPLE_CNT_REQ in NUMBER,
452 X_SAMPLE_QTY in NUMBER,
453 X_SAMPLE_QTY_UOM in VARCHAR2,
454 X_FREQUENCY_TYPE in VARCHAR2,
455 X_FREQUENCY_CNT in NUMBER,
456 X_FREQUENCY_PER in VARCHAR2,
457 X_RESERVE_CNT_REQ in NUMBER,
458 X_RESERVE_QTY in NUMBER,
459 X_ARCHIVE_CNT_REQ in NUMBER,
460 X_ARCHIVE_QTY in NUMBER,
461 X_DELETE_MARK in NUMBER,
462 X_TEXT_CODE in NUMBER,
463 X_ATTRIBUTE_CATEGORY in VARCHAR2,
464 X_ATTRIBUTE1 in VARCHAR2,
465 X_ATTRIBUTE2 in VARCHAR2,
466 X_ATTRIBUTE3 in VARCHAR2,
467 X_ATTRIBUTE4 in VARCHAR2,
468 X_ATTRIBUTE5 in VARCHAR2,
469 X_ATTRIBUTE6 in VARCHAR2,
470 X_SAMPLING_PLAN_DESC in VARCHAR2,
471 X_LAST_UPDATE_DATE in DATE,
472 X_LAST_UPDATED_BY in NUMBER,
473 X_LAST_UPDATE_LOGIN in NUMBER
474 ) is
475 begin
476 update GMD_SAMPLING_PLANS_B set
477 ATTRIBUTE28 = X_ATTRIBUTE28,
478 ATTRIBUTE29 = X_ATTRIBUTE29,
479 ATTRIBUTE30 = X_ATTRIBUTE30,
480 ATTRIBUTE7 = X_ATTRIBUTE7,
481 ATTRIBUTE8 = X_ATTRIBUTE8,
482 ATTRIBUTE9 = X_ATTRIBUTE9,
483 ATTRIBUTE10 = X_ATTRIBUTE10,
484 ATTRIBUTE11 = X_ATTRIBUTE11,
485 ATTRIBUTE12 = X_ATTRIBUTE12,
486 ATTRIBUTE13 = X_ATTRIBUTE13,
487 ATTRIBUTE14 = X_ATTRIBUTE14,
488 ATTRIBUTE15 = X_ATTRIBUTE15,
489 ATTRIBUTE16 = X_ATTRIBUTE16,
490 ATTRIBUTE17 = X_ATTRIBUTE17,
491 ATTRIBUTE18 = X_ATTRIBUTE18,
492 ATTRIBUTE19 = X_ATTRIBUTE19,
493 ATTRIBUTE20 = X_ATTRIBUTE20,
494 ATTRIBUTE21 = X_ATTRIBUTE21,
495 ATTRIBUTE22 = X_ATTRIBUTE22,
496 ATTRIBUTE23 = X_ATTRIBUTE23,
497 ATTRIBUTE24 = X_ATTRIBUTE24,
498 ATTRIBUTE25 = X_ATTRIBUTE25,
499 ATTRIBUTE26 = X_ATTRIBUTE26,
500 ATTRIBUTE27 = X_ATTRIBUTE27,
501 SAMPLING_PLAN_NAME = X_SAMPLING_PLAN_NAME,
502 SAMPLE_CNT_REQ = X_SAMPLE_CNT_REQ,
503 SAMPLE_QTY = X_SAMPLE_QTY,
504 SAMPLE_QTY_UOM = X_SAMPLE_QTY_UOM,
505 FREQUENCY_TYPE = X_FREQUENCY_TYPE,
506 FREQUENCY_CNT = X_FREQUENCY_CNT,
507 FREQUENCY_PER = X_FREQUENCY_PER,
508 RESERVE_CNT_REQ = X_RESERVE_CNT_REQ,
509 RESERVE_QTY = X_RESERVE_QTY,
510 ARCHIVE_CNT_REQ = X_ARCHIVE_CNT_REQ,
511 ARCHIVE_QTY = X_ARCHIVE_QTY,
512 DELETE_MARK = X_DELETE_MARK,
513 TEXT_CODE = X_TEXT_CODE,
514 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
515 ATTRIBUTE1 = X_ATTRIBUTE1,
516 ATTRIBUTE2 = X_ATTRIBUTE2,
517 ATTRIBUTE3 = X_ATTRIBUTE3,
518 ATTRIBUTE4 = X_ATTRIBUTE4,
519 ATTRIBUTE5 = X_ATTRIBUTE5,
520 ATTRIBUTE6 = X_ATTRIBUTE6,
521 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
522 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
523 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
524 where SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID;
525
526 if (sql%notfound) then
527 raise no_data_found;
528 end if;
529
530 update GMD_SAMPLING_PLANS_TL set
531 SAMPLING_PLAN_DESC = X_SAMPLING_PLAN_DESC,
532 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
533 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
534 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
535 SOURCE_LANG = userenv('LANG')
536 where SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID
537 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
538
539 if (sql%notfound) then
540 raise no_data_found;
541 end if;
542 end UPDATE_ROW;
543
544 procedure DELETE_ROW (
545 X_SAMPLING_PLAN_ID in NUMBER
546 ) is
547 begin
548 delete from GMD_SAMPLING_PLANS_TL
549 where SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID;
550
551 if (sql%notfound) then
552 raise no_data_found;
553 end if;
554
555 delete from GMD_SAMPLING_PLANS_B
556 where SAMPLING_PLAN_ID = X_SAMPLING_PLAN_ID;
557
558 if (sql%notfound) then
559 raise no_data_found;
560 end if;
561 end DELETE_ROW;
562
563 procedure ADD_LANGUAGE
564 is
565 begin
566 delete from GMD_SAMPLING_PLANS_TL T
567 where not exists
568 (select NULL
569 from GMD_SAMPLING_PLANS_B B
570 where B.SAMPLING_PLAN_ID = T.SAMPLING_PLAN_ID
571 );
572
573 update GMD_SAMPLING_PLANS_TL T set (
574 SAMPLING_PLAN_DESC
575 ) = (select
576 B.SAMPLING_PLAN_DESC
577 from GMD_SAMPLING_PLANS_TL B
578 where B.SAMPLING_PLAN_ID = T.SAMPLING_PLAN_ID
579 and B.LANGUAGE = T.SOURCE_LANG)
580 where (
581 T.SAMPLING_PLAN_ID,
582 T.LANGUAGE
583 ) in (select
584 SUBT.SAMPLING_PLAN_ID,
585 SUBT.LANGUAGE
586 from GMD_SAMPLING_PLANS_TL SUBB, GMD_SAMPLING_PLANS_TL SUBT
587 where SUBB.SAMPLING_PLAN_ID = SUBT.SAMPLING_PLAN_ID
588 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
589 and (SUBB.SAMPLING_PLAN_DESC <> SUBT.SAMPLING_PLAN_DESC
590 ));
591
592 insert into GMD_SAMPLING_PLANS_TL (
593 SAMPLING_PLAN_ID,
594 LAST_UPDATE_DATE,
595 LAST_UPDATE_LOGIN,
596 SAMPLING_PLAN_DESC,
597 CREATION_DATE,
598 CREATED_BY,
599 LAST_UPDATED_BY,
600 LANGUAGE,
601 SOURCE_LANG
602 ) select
603 B.SAMPLING_PLAN_ID,
604 B.LAST_UPDATE_DATE,
605 B.LAST_UPDATE_LOGIN,
606 B.SAMPLING_PLAN_DESC,
607 B.CREATION_DATE,
608 B.CREATED_BY,
609 B.LAST_UPDATED_BY,
610 L.LANGUAGE_CODE,
611 B.SOURCE_LANG
612 from GMD_SAMPLING_PLANS_TL B, FND_LANGUAGES L
613 where L.INSTALLED_FLAG in ('I', 'B')
614 and B.LANGUAGE = userenv('LANG')
615 and not exists
616 (select NULL
617 from GMD_SAMPLING_PLANS_TL T
618 where T.SAMPLING_PLAN_ID = B.SAMPLING_PLAN_ID
619 and T.LANGUAGE = L.LANGUAGE_CODE);
620 end ADD_LANGUAGE;
621
622 FUNCTION fetch_row (p_sampling_plan IN gmd_sampling_plans%ROWTYPE,
623 x_sampling_plan OUT NOCOPY gmd_sampling_plans%ROWTYPE )
624 RETURN BOOLEAN
625 IS
626 BEGIN
627 IF (p_sampling_plan.sampling_plan_id IS NOT NULL) THEN
628 SELECT *
629 INTO x_sampling_plan
630 FROM gmd_sampling_plans
631 WHERE sampling_plan_id = p_sampling_plan.sampling_plan_id;
632 ELSIF (p_sampling_plan.sampling_plan_name IS NOT NULL) THEN
633
634 SELECT *
635 INTO x_sampling_plan
636 FROM gmd_sampling_plans
637 WHERE sampling_plan_name = p_sampling_plan.sampling_plan_name;
638 ELSE
639 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_SAMPLING_PLANS');
640 RETURN FALSE;
641 END IF;
642
643 RETURN TRUE;
644
645 EXCEPTION
646 WHEN NO_DATA_FOUND
647 THEN
648 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_SAMPLING_PLANS');
649 RETURN FALSE;
650 WHEN OTHERS
651 THEN
652 fnd_msg_pub.add_exc_msg ('GMD_SAMPLING_PLAN_PVT', 'FETCH_ROW');
653 RETURN FALSE;
654
655 END fetch_row;
656
657 end GMD_SAMPLING_PLANS_PVT;
[Home] [Help]