[Home] [Help]
PACKAGE BODY: APPS.QASLSET_TABLE_HANDLER_PKG
Source
1 PACKAGE BODY QASLSET_TABLE_HANDLER_PKG as
2 /* $Header: qaslsetb.plb 115.4 2002/11/27 19:18:54 jezheng ship $ */
3
4 PROCEDURE insert_process_row(
5 X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Process_id IN OUT NOCOPY NUMBER,
7 X_Process_Code VARCHAR2,
8 X_Description VARCHAR2,
9 X_Disqualification_Lots NUMBER default 1,
10 X_Disqualification_Days NUMBER,
11 X_Org_id NUMBER,
12 X_Last_Update_Date DATE,
13 X_Last_Updated_By NUMBER,
14 X_Creation_Date DATE,
15 X_Created_By NUMBER,
16 X_Last_Update_Login NUMBER
17 ) IS
18
19 CURSOR C1 IS SELECT rowid FROM qa_skiplot_processes
20 WHERE process_id = X_Process_id;
21
22 CURSOR C2 IS SELECT qa_skiplot_processes_s.nextval FROM dual;
23
24 BEGIN
25
26 if (X_Process_id is NULL) then
27 OPEN C2;
28 FETCH C2 INTO X_Process_id;
29 CLOSE C2;
30 end if;
31
32 INSERT INTO QA_SKIPLOT_PROCESSES(
33 process_id,
34 process_code,
35 description,
36 disqualification_lots,
37 disqualification_days,
38 organization_id,
39 last_update_date,
40 last_updated_by,
41 creation_date,
42 created_by,
43 last_update_login
44 )
45 values(
46 X_Process_id,
47 X_Process_Code,
48 X_Description,
49 nvl(X_Disqualification_Lots, 1),
50 X_Disqualification_Days,
51 X_Org_id,
52 X_Last_Update_Date,
53 X_Last_Updated_By,
54 X_Creation_Date,
55 X_Created_By,
56 X_Last_Update_Login
57 );
58
59 --commit;
60
61 OPEN C1;
62 FETCH C1 INTO X_Rowid;
63 if (C1%NOTFOUND) then
64 CLOSE C1;
65 Raise NO_DATA_FOUND;
66 end if;
67 CLOSE C1;
68
69 END insert_process_row;
70
71
72
73 PROCEDURE insert_process_plans_row(
74 X_Rowid IN OUT NOCOPY VARCHAR2,
75 X_Process_Plan_id IN OUT NOCOPY NUMBER,
76 X_Process_id NUMBER,
77 X_Plan_id NUMBER,
78 X_Alternate_Plan_id NUMBER,
79 X_Last_Update_Date DATE,
80 X_Last_Updated_By NUMBER,
81 X_Creation_Date DATE,
82 X_Created_By NUMBER,
83 X_Last_Update_Login NUMBER
84 ) IS
85
86 CURSOR C1 IS SELECT rowid FROM qa_skiplot_process_plans
87 WHERE process_plan_id = X_Process_Plan_id;
88
89 CURSOR C2 IS SELECT qa_skiplot_process_plans_s.nextval FROM dual;
90
91 BEGIN
92
93 if (X_Process_Plan_id is NULL) then
94 OPEN C2;
95 FETCH C2 INTO X_Process_Plan_id;
96 CLOSE C2;
97 end if;
98
99 INSERT INTO QA_SKIPLOT_PROCESS_PLANS(
100 process_plan_id,
101 process_id,
102 plan_id,
103 alternate_plan_id,
104 last_update_date,
105 last_updated_by,
106 creation_date,
107 created_by,
108 last_update_login
109 )
110 values(
111 X_Process_Plan_id,
112 X_Process_id,
113 X_Plan_id,
114 X_Alternate_Plan_id,
115 X_Last_Update_Date,
116 X_Last_Updated_By,
117 X_Creation_Date,
118 X_Created_By,
119 X_Last_Update_Login
120 );
121
122 --commit;
123
124 OPEN C1;
125 FETCH C1 INTO X_Rowid;
126 if (C1%NOTFOUND) then
127 CLOSE C1;
128 Raise NO_DATA_FOUND;
129 end if;
130 CLOSE C1;
131
132 END insert_process_plans_row;
133
134
135
136
137 PROCEDURE insert_process_plan_rules_row(
138 X_Rowid IN OUT NOCOPY VARCHAR2,
139 X_Process_Plan_Rule_id IN OUT NOCOPY NUMBER,
140 X_Process_Plan_id NUMBER,
141 X_Rule_Seq NUMBER,
142 X_Frequency_Num NUMBER,
143 X_Frequency_Denom NUMBER,
144 X_Rounds NUMBER,
145 X_Days_Span NUMBER,
146 X_Last_Update_Date DATE,
147 X_Last_Updated_By NUMBER,
148 X_Creation_Date DATE,
149 X_Created_By NUMBER,
150 X_Last_Update_Login NUMBER
151 ) IS
152
153
154 CURSOR C1 IS SELECT rowid FROM qa_skiplot_process_plan_rules
155 WHERE process_plan_id = X_Process_Plan_id and
156 rule_seq = X_Rule_Seq;
157
158 CURSOR C2 IS SELECT qa_skiplot_pp_rules_s.nextval FROM dual;
159
160
161 BEGIN
162
163 if (X_Process_Plan_Rule_id is NULL) then
164 OPEN C2;
165 FETCH C2 INTO X_Process_Plan_Rule_id;
166 CLOSE C2;
167 end if;
168
169
170 INSERT INTO QA_SKIPLOT_PROCESS_PLAN_RULES(
171 process_plan_rule_id,
172 process_plan_id,
173 rule_seq,
174 frequency_num,
175 frequency_denom,
176 rounds,
177 days_span,
178 last_update_date,
179 last_updated_by,
180 creation_date,
181 created_by,
182 last_update_login
183 )
184 values(
185 X_Process_Plan_Rule_id,
186 X_Process_Plan_id,
187 X_Rule_Seq,
188 X_Frequency_Num,
189 X_Frequency_Denom,
190 X_Rounds,
191 X_Days_Span,
192 X_Last_Update_Date,
193 X_Last_Updated_By,
194 X_Creation_Date,
195 X_Created_By,
196 X_Last_Update_Login
197 );
198
199 --commit;
200
201
202 OPEN C1;
203 FETCH C1 INTO X_Rowid;
204 if (C1%NOTFOUND) then
205 CLOSE C1;
206 Raise NO_DATA_FOUND;
207 end if;
208 CLOSE C1;
209
210
211 END insert_process_plan_rules_row;
212
213
214
215
216
217 PROCEDURE update_process_row(
218 X_Rowid VARCHAR2,
219 X_Process_id NUMBER,
220 X_Process_Code VARCHAR2,
221 X_Description VARCHAR2,
222 X_Disqualification_Lots NUMBER default 1,
223 X_Disqualification_Days NUMBER,
224 X_Qualification_Lots NUMBER,
225 X_Qualification_Days NUMBER,
226 X_Org_id NUMBER,
227 X_Last_Update_Date DATE,
228 X_Last_Updated_By NUMBER,
229 X_Creation_Date DATE,
230 X_Created_By NUMBER,
231 X_Last_Update_Login NUMBER
232 ) IS
233
234 CURSOR C IS
235 SELECT process_plan_rule_id
236 FROM QA_SKIPLOT_PROCESSES p, QA_SKIPLOT_PROCESS_PLANS pp, QA_SKIPLOT_PROCESS_PLAN_RULES ppr
237 WHERE p.process_id = X_Process_id
238 and p.process_id = pp.process_id
239 and pp.process_plan_id = ppr.process_plan_id
240 and ppr.rule_seq = 0;
241
242 ppr_id NUMBER;
243
244 BEGIN
245
246 UPDATE QA_SKIPLOT_PROCESSES
247 SET
248 process_id = X_Process_id,
249 process_code = X_Process_Code,
250 description = X_Description,
251 disqualification_lots = nvl(X_Disqualification_Lots, 1),
252 disqualification_days = X_Disqualification_Days,
253 organization_id = X_Org_id,
254 last_update_date = X_Last_Update_Date,
255 last_updated_by = X_Last_Updated_By,
256 creation_date = X_Creation_Date,
257 created_by = X_Created_By,
258 last_update_login = X_Last_Update_Login
259 WHERE rowid = X_Rowid;
260
261 --commit;
262
263 if (SQL%NOTFOUND) then
264 Raise NO_DATA_FOUND;
265 end if;
266
267 OPEN C;
268
269 LOOP
270 FETCH C into ppr_id;
271 EXIT WHEN C%NOTFOUND;
272 UPDATE QA_SKIPLOT_PROCESS_PLAN_RULES
273 SET
274 rounds = X_Qualification_Lots,
275 days_span = X_Qualification_Days,
276 last_update_date = X_Last_Update_Date,
277 last_updated_by = X_Last_Updated_By,
278 creation_date = X_Creation_Date,
279 created_by = X_Created_By,
280 last_update_login = X_Last_Update_Login
281 WHERE process_plan_rule_id = ppr_id;
282 END LOOP;
283
284 END update_process_row;
285
286
287
288 PROCEDURE update_process_plans_row(
289 X_Rowid VARCHAR2,
290 X_Process_Plan_id NUMBER,
291 X_Process_id NUMBER,
292 X_Plan_id NUMBER,
293 X_Alternate_Plan_id NUMBER,
294 X_Last_Update_Date DATE,
295 X_Last_Updated_By NUMBER,
296 X_Creation_Date DATE,
297 X_Created_By NUMBER,
298 X_Last_Update_Login NUMBER
299 ) IS
300
301 BEGIN
302
303 UPDATE QA_SKIPLOT_PROCESS_PLANS
304 SET
305 process_plan_id = X_Process_Plan_id,
306 process_id = X_Process_id,
307 plan_id = X_Plan_id,
308 alternate_plan_id = X_Alternate_Plan_id,
309 last_update_date = X_Last_Update_Date,
310 last_updated_by = X_Last_Updated_By,
311 creation_date = X_Creation_Date,
312 created_by = X_Created_By,
313 last_update_login = X_Last_Update_Login
314 WHERE rowid = X_Rowid;
315
316 --commit;
317
318 if (SQL%NOTFOUND) then
319 Raise NO_DATA_FOUND;
320 end if;
321
322 END update_process_plans_row;
323
324
325
326
327 PROCEDURE update_process_plan_rules_row(
328 X_Rowid VARCHAR2,
329 X_Process_Plan_Rule_id NUMBER,
330 X_Process_Plan_id NUMBER,
331 X_Rule_Seq NUMBER,
332 X_Frequency_Num NUMBER,
333 X_Frequency_Denom NUMBER,
334 X_Rounds NUMBER,
335 X_Days_Span NUMBER,
336 X_Last_Update_Date DATE,
337 X_Last_Updated_By NUMBER,
338 X_Creation_Date DATE,
339 X_Created_By NUMBER,
340 X_Last_Update_Login NUMBER
341 ) IS
342
343 BEGIN
344
345 UPDATE QA_SKIPLOT_PROCESS_PLAN_RULES
346 SET
347 process_plan_rule_id = X_Process_Plan_Rule_id,
348 process_plan_id = X_Process_Plan_id,
349 rule_seq = X_Rule_Seq,
350 frequency_num = X_Frequency_Num,
351 frequency_denom = X_Frequency_Denom,
352 rounds = X_Rounds,
353 days_span = X_Days_Span,
354 last_update_date = X_Last_Update_Date,
355 last_updated_by = X_Last_Updated_By,
356 creation_date = X_Creation_Date,
357 created_by = X_Created_By,
358 last_update_login = X_Last_Update_Login
359 WHERE rowid = X_Rowid;
360
361 --commit;
362
363 if (SQL%NOTFOUND) then
364 Raise NO_DATA_FOUND;
365 end if;
366
367 END update_process_plan_rules_row;
368
369
370
371
372
373 PROCEDURE lock_process_row(
374 X_Rowid VARCHAR2,
375 X_Process_id NUMBER,
376 X_Process_Code VARCHAR2,
377 X_Description VARCHAR2,
378 X_Disqualification_Lots NUMBER default 1,
379 X_Disqualification_Days NUMBER,
380 X_Qualification_Lots NUMBER,
381 X_Qualification_Days NUMBER,
382 X_Org_id NUMBER,
383 X_Last_Update_Date DATE,
384 X_Last_Updated_By NUMBER,
385 X_Creation_Date DATE,
386 X_Created_By NUMBER,
387 X_Last_Update_Login NUMBER
388 ) IS
389
390 CURSOR C IS
391 SELECT *
392 FROM QA_SKIPLOT_PROCESSES
393 WHERE rowid = X_Rowid
394 FOR UPDATE of process_id NOWAIT;
395
396 Recinfo C%ROWTYPE;
397
398
399 CURSOR C1 IS
400 SELECT ppr.rounds qualification_lots, ppr.days_span qualification_days
401 FROM QA_SKIPLOT_PROCESSES p, QA_SKIPLOT_PROCESS_PLANS pp, QA_SKIPLOT_PROCESS_PLAN_RULES ppr
402 WHERE p.process_id = X_Process_id and p.process_id = pp.process_id and pp.process_plan_id = ppr.process_plan_id and ppr.rule_seq = 0
403 FOR UPDATE of ppr.process_plan_id, ppr.process_plan_rule_id NOWAIT;
404
405 Recinfo1 C1%ROWTYPE;
406
407
408 BEGIN
409
410 OPEN C;
411 FETCH C INTO Recinfo;
412
413 if (C%NOTFOUND) then
414 CLOSE C;
415 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
416 APP_EXCEPTION.Raise_Exception;
417 end if;
418
419 CLOSE C;
420
421 if (
422 (Recinfo.process_id = X_Process_id)
423 AND (Recinfo.process_code = X_Process_Code)
424 AND ( (Recinfo.description = X_Description)
425 OR ( (Recinfo.description IS NULL)
426 AND (X_Description IS NULL)))
427 AND (Recinfo.disqualification_lots = X_Disqualification_Lots)
428 AND ( (Recinfo.disqualification_days = X_Disqualification_Days)
429 OR ( (Recinfo.disqualification_days IS NULL)
430 AND (X_Disqualification_Days IS NULL)))
431 AND (Recinfo.organization_id = X_Org_id)
432 AND (Recinfo.last_update_date = X_Last_Update_Date)
433 AND (Recinfo.last_updated_by = X_Last_Updated_By)
434 AND (Recinfo.creation_date = X_Creation_Date)
435 AND (Recinfo.created_by = X_Created_By)
436 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
437 OR ( (Recinfo.last_update_login IS NULL)
438 AND (X_Last_Update_Login IS NULL)))
439
440 ) then
441 null;
442 -- return;
443
444 else
445 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
446 APP_EXCEPTION.Raise_Exception;
447
448 end if;
449
450 -- locking default rule rows with rule_seq =0;
451
452 OPEN C1;
453 /* notice that checking only one row because the default rows all contain the same values for qualification lots and qualification days. and the other columns are unaccessible to the user and hence not checked.*/
454
455 FETCH C1 INTO Recinfo1;
456
457 if (C1%NOTFOUND) then
458 CLOSE C1;
459 else
460
461 if (
462 ( (Recinfo1.qualification_lots = X_Qualification_Lots)
463 OR ( (Recinfo1.qualification_lots IS NULL)
464 AND (X_Qualification_lots IS NULL)))
465 AND ( (Recinfo1.qualification_days = X_Qualification_Days)
466 OR ( (Recinfo1.qualification_days IS NULL)
467 AND (X_Qualification_Days IS NULL)))
468 ) then
469
470 return;
471
472 else
473 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
474 APP_EXCEPTION.Raise_Exception;
475
476 end if;
477
478 end if;
479 CLOSE C1;
480
481 END lock_process_row;
482
483
484
485 PROCEDURE lock_process_plans_row(
486 X_Rowid VARCHAR2,
487 X_Process_Plan_id NUMBER,
488 X_Process_id NUMBER,
489 X_Plan_id NUMBER,
490 X_Alternate_Plan_id NUMBER,
491 X_Last_Update_Date DATE,
492 X_Last_Updated_By NUMBER,
493 X_Creation_Date DATE,
494 X_Created_By NUMBER,
495 X_Last_Update_Login NUMBER
496 ) IS
497
498 CURSOR C IS
499 SELECT *
500 FROM QA_SKIPLOT_PROCESS_PLANS
501 WHERE rowid = X_Rowid
502 FOR UPDATE of process_plan_id NOWAIT;
503
504 Recinfo C%ROWTYPE;
505
506
507 BEGIN
508
509 OPEN C;
510 FETCH C INTO Recinfo;
511
512 if (C%NOTFOUND) then
513 CLOSE C;
514 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
515 APP_EXCEPTION.Raise_Exception;
516 end if;
517
518 CLOSE C;
519
520 if (
521 (Recinfo.process_plan_id = X_Process_Plan_id)
522 AND (Recinfo.process_id = X_Process_id)
523 AND (Recinfo.plan_id = X_Plan_id)
524 AND ( (Recinfo.alternate_plan_id = X_Alternate_Plan_id)
525 OR ( (Recinfo.alternate_plan_id IS NULL)
526 AND (X_Alternate_Plan_id IS NULL)))
527 AND (Recinfo.last_update_date = X_Last_Update_Date)
528 AND (Recinfo.last_updated_by = X_Last_Updated_By)
529 AND (Recinfo.creation_date = X_Creation_Date)
530 AND (Recinfo.created_by = X_Created_By)
531 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
532 OR ( (Recinfo.last_update_login IS NULL)
533 AND (X_Last_Update_Login IS NULL)))
534
535 ) then
536
537 return;
538
539 else
540 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
541 APP_EXCEPTION.Raise_Exception;
542
543 end if;
544
545 END lock_process_plans_row;
546
547
548
549
550 PROCEDURE lock_process_plan_rules_row(
551 X_Rowid VARCHAR2,
552 X_Process_Plan_Rule_id NUMBER,
553 X_Process_Plan_id NUMBER,
554 X_Rule_Seq NUMBER,
555 X_Frequency_Num NUMBER,
556 X_Frequency_Denom NUMBER,
557 X_Rounds NUMBER,
558 X_Days_Span NUMBER,
559 X_Last_Update_Date DATE,
560 X_Last_Updated_By NUMBER,
561 X_Creation_Date DATE,
562 X_Created_By NUMBER,
563 X_Last_Update_Login NUMBER
564 ) IS
565
566 CURSOR C IS
567 SELECT *
568 FROM QA_SKIPLOT_PROCESS_PLAN_RULES
569 WHERE rowid = X_Rowid
570 FOR UPDATE of process_plan_rule_id NOWAIT;
571
572 Recinfo C%ROWTYPE;
573
574 BEGIN
575
576 OPEN C;
577 FETCH C INTO Recinfo;
578
579 if (C%NOTFOUND) then
580 CLOSE C;
581 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
582 APP_EXCEPTION.Raise_Exception;
583 end if;
584
585 CLOSE C;
586
587 if (
588 (Recinfo.process_plan_rule_id = X_Process_Plan_Rule_id)
589 AND (Recinfo.process_plan_id = X_Process_Plan_id)
590 AND (Recinfo.rule_seq = X_Rule_Seq)
591 AND (Recinfo.frequency_num = X_Frequency_Num)
592 AND (Recinfo.frequency_denom = X_Frequency_Denom)
593 AND ( (Recinfo.rounds = X_Rounds)
594 OR ( (Recinfo.rounds IS NULL)
595 AND (X_Rounds IS NULL)))
596 AND ( (Recinfo.days_span = X_Days_Span)
597 OR ( (Recinfo.days_span IS NULL)
598 AND (X_Days_Span IS NULL)))
599 AND (Recinfo.last_update_date = X_Last_Update_Date)
600 AND (Recinfo.last_updated_by = X_Last_Updated_By)
601 AND (Recinfo.creation_date = X_Creation_Date)
602 AND (Recinfo.created_by = X_Created_By)
603 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
604 OR ( (Recinfo.last_update_login IS NULL)
605 AND (X_Last_Update_Login IS NULL)))
606
607 ) then
608
609 return;
610
611 else
612 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
613 APP_EXCEPTION.Raise_Exception;
614
615 end if;
616
617 END lock_process_plan_rules_row;
618
619
620
621
622
623 PROCEDURE delete_process_row(X_Rowid VARCHAR2) IS
624
625 BEGIN
626
627 delete from QA_SKIPLOT_PROCESSES
628 where rowid = X_Rowid;
629
630 if (SQL%NOTFOUND) then
631 Raise NO_DATA_FOUND;
632 end if;
633
634 END delete_process_row;
635
636
637
638
639 PROCEDURE delete_process_plans_row(X_Rowid VARCHAR2) IS
640
641 BEGIN
642
643 delete from QA_SKIPLOT_PROCESS_PLANS
644 where rowid = X_Rowid;
645
646 if (SQL%NOTFOUND) then
647 Raise NO_DATA_FOUND;
648 end if;
649
650 END delete_process_plans_row;
651
652
653
654
655
656 PROCEDURE delete_process_plan_rules_row(X_Rowid VARCHAR2) IS
657
658 BEGIN
659 delete from QA_SKIPLOT_PROCESS_PLAN_RULES
660 where rowid = X_Rowid;
661
662 if (SQL%NOTFOUND) then
663 Raise NO_DATA_FOUND;
664 end if;
665
666 END delete_process_plan_rules_row;
667
668
669
670
671
672
673 END QASLSET_TABLE_HANDLER_PKG;