[Home] [Help]
PACKAGE BODY: APPS.QASLSP_TABLE_HANDLER_PKG
Source
1 PACKAGE BODY QASLSP_TABLE_HANDLER_PKG as
2 /* $Header: qaslspb.plb 115.2 2002/11/27 19:19:15 jezheng ship $ */
3
4 PROCEDURE insert_sl_sp_rcv_criteria_row(
5 X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Criteria_id IN OUT NOCOPY NUMBER,
7 X_Organization_id NUMBER,
8 X_Vendor_id NUMBER DEFAULT -1,
9 X_Vendor_Site_id NUMBER DEFAULT -1,
10 X_Item_id NUMBER DEFAULT -1,
11 X_Item_Revision VARCHAR2 DEFAULT '-1',
12 X_Category_id NUMBER DEFAULT -1,
13 X_Manufacturer_id NUMBER DEFAULT -1,
14 X_Project_id NUMBER DEFAULT -1,
15 X_Task_id NUMBER DEFAULT -1,
16 X_Last_Update_Date DATE,
17 X_Last_Updated_By NUMBER,
18 X_Creation_Date DATE,
19 X_Created_By NUMBER,
20 X_Last_Update_Login NUMBER
21 ) IS
22
23 CURSOR C1 IS SELECT rowid FROM qa_sl_sp_rcv_criteria
24 WHERE criteria_id = X_Criteria_id;
25
26 CURSOR C2 IS SELECT qa_sl_sp_criteria_s.nextval FROM dual;
27
28 BEGIN
29
30
31 if (X_Criteria_id is NULL) then
32 OPEN C2;
33 FETCH C2 INTO X_Criteria_id;
34 CLOSE C2;
35 end if;
36
37 INSERT INTO QA_SL_SP_RCV_CRITERIA(
38 criteria_id,
39 organization_id,
40 vendor_id,
41 vendor_site_id,
42 item_id,
43 item_Revision,
44 item_category_id,
45 manufacturer_id,
46 project_id,
47 task_id,
48 last_update_date,
49 last_updated_by,
50 creation_date,
51 created_by,
52 last_update_login
53 )
54 values(
55 X_Criteria_id,
56 X_Organization_id,
57 nvl(X_Vendor_id, -1),
58 nvl(X_Vendor_Site_id, -1),
59 nvl(X_Item_id, -1),
60 nvl(X_Item_Revision, '-1'),
61 nvl(X_Category_id, -1),
62 nvl(X_Manufacturer_id, -1),
63 nvl(X_Project_id, -1),
64 nvl(X_Task_id, -1),
65 X_Last_Update_Date,
66 X_Last_Updated_By,
67 X_Creation_Date,
68 X_Created_By,
69 X_Last_Update_Login
70 );
71
72 --commit;
73
74 OPEN C1;
75 FETCH C1 INTO X_Rowid;
76 if (C1%NOTFOUND) then
77 CLOSE C1;
78 Raise NO_DATA_FOUND;
79 end if;
80 CLOSE C1;
81
82 END insert_sl_sp_rcv_criteria_row;
83
84
85
86
87 PROCEDURE insert_sp_association_row(
88 X_Rowid IN OUT NOCOPY VARCHAR2,
89 X_Criteria_id NUMBER,
90 X_Sampling_Plan_id NUMBER,
91 X_Collection_Plan_id NUMBER DEFAULT -1,
92 X_SP_WF_Role_Name VARCHAR2,
93 X_SP_Effective_From DATE,
94 X_SP_Effective_To DATE,
95 X_Last_Update_Date DATE,
96 X_Last_Updated_By NUMBER,
97 X_Creation_Date DATE,
98 X_Created_By NUMBER,
99 X_Last_Update_Login NUMBER
100 ) IS
101
102 CURSOR C1 IS SELECT rowid FROM qa_sampling_association
103 WHERE criteria_id = X_Criteria_id and
104 sampling_plan_id = X_Sampling_Plan_id;
105
106 BEGIN
107
108 INSERT INTO QA_SAMPLING_ASSOCIATION(
109 criteria_id,
110 sampling_plan_id,
111 collection_plan_id,
112 wf_role_name,
113 effective_from,
114 effective_to,
115 last_update_date,
116 last_updated_by,
117 creation_date,
118 created_by,
119 last_update_login
120 )
121 values(
122 X_Criteria_id,
123 X_Sampling_Plan_id,
124 nvl(X_Collection_Plan_id, -1),
125 X_SP_WF_Role_Name,
126 X_SP_Effective_From,
127 X_SP_Effective_To,
128 X_Last_Update_Date,
129 X_Last_Updated_By,
130 X_Creation_Date,
131 X_Created_By,
132 X_Last_Update_Login
133 );
134
135 --commit;
136
137 OPEN C1;
138 FETCH C1 INTO X_Rowid;
139 if (C1%NOTFOUND) then
140 CLOSE C1;
141 Raise NO_DATA_FOUND;
142 end if;
143 CLOSE C1;
144
145 END insert_sp_association_row;
146
147
148
149 PROCEDURE insert_sl_association_row(
150 X_Rowid IN OUT NOCOPY VARCHAR2,
151 X_Criteria_id NUMBER,
152 X_Process_id NUMBER,
153 X_SL_WF_Role_Name VARCHAR2,
154 X_SL_Effective_From DATE,
155 X_SL_Effective_To DATE,
156 X_Lotsize_From NUMBER,
157 X_Lotsize_To NUMBER,
158 X_Insp_Stage VARCHAR2,
159 X_Last_Update_Date DATE,
160 X_Last_Updated_By NUMBER,
161 X_Creation_Date DATE,
162 X_Created_By NUMBER,
163 X_Last_Update_Login NUMBER
164 ) IS
165
166 CURSOR C1 IS SELECT rowid FROM qa_skiplot_association
167 WHERE criteria_id = X_Criteria_id and
168 process_id = X_Process_id;
169
170 BEGIN
171
172 INSERT INTO QA_SKIPLOT_ASSOCIATION(
173 criteria_id,
174 process_id,
175 effective_from,
176 effective_to,
177 lotsize_from,
178 lotsize_to,
179 wf_role_name,
180 insp_stage,
181 last_update_date,
182 last_updated_by,
183 creation_date,
184 created_by,
185 last_update_login
186 )
187 values(
188 X_Criteria_id,
189 X_Process_id,
190 X_SL_Effective_From,
191 X_SL_Effective_To,
192 X_Lotsize_From,
193 X_Lotsize_To,
194 X_SL_WF_Role_Name,
195 X_Insp_Stage,
196 X_Last_Update_Date,
197 X_Last_Updated_By,
198 X_Creation_Date,
199 X_Created_By,
200 X_Last_Update_Login
201 );
202
203 --commit;
204
205 OPEN C1;
206 FETCH C1 INTO X_Rowid;
207 if (C1%NOTFOUND) then
208 CLOSE C1;
209 Raise NO_DATA_FOUND;
210 end if;
211 CLOSE C1;
212
213 END insert_sl_association_row;
214
215
216
217
218 PROCEDURE update_sl_sp_rcv_criteria_row(
219 X_Rowid VARCHAR2,
220 X_Criteria_id NUMBER,
221 X_Organization_id NUMBER,
222 X_Vendor_id NUMBER DEFAULT -1,
223 X_Vendor_Site_id NUMBER DEFAULT -1,
224 X_Item_id NUMBER DEFAULT -1,
225 X_Item_Revision VARCHAR2 DEFAULT '-1',
226 X_Category_id NUMBER DEFAULT -1,
227 X_Manufacturer_id NUMBER DEFAULT -1,
228 X_Project_id NUMBER DEFAULT -1,
229 X_Task_id NUMBER DEFAULT -1,
230 X_Last_Update_Date DATE,
231 X_Last_Updated_By NUMBER,
232 X_Creation_Date DATE,
233 X_Created_By NUMBER,
234 X_Last_Update_Login NUMBER
235 ) IS
236
237 BEGIN
238
239 UPDATE QA_SL_SP_RCV_CRITERIA
240 SET
241 criteria_id = X_Criteria_id,
242 organization_id = X_Organization_id,
243 vendor_id = nvl(X_Vendor_id, -1),
244 vendor_Site_id = nvl(X_Vendor_Site_id, -1),
245 item_id = nvl(X_Item_id, -1),
246 item_Revision = nvl(X_Item_Revision, '-1'),
247 item_category_id = nvl(X_Category_id, -1),
248 manufacturer_id = nvl(X_Manufacturer_id, -1),
249 project_id = nvl(X_Project_id, -1),
250 task_id = nvl(X_Task_id, -1),
251 last_update_date = X_Last_Update_Date,
252 last_updated_by = X_Last_Updated_By,
253 creation_date = X_Creation_Date,
254 created_by = X_Created_By,
255 last_update_login = X_Last_Update_Login
256 WHERE rowid = X_Rowid;
257
258 --commit;
259
260 if (SQL%NOTFOUND) then
261 Raise NO_DATA_FOUND;
262 end if;
263
264 END update_sl_sp_rcv_criteria_row;
265
266
267
268
269 PROCEDURE update_sp_association_row(
270 X_Rowid VARCHAR2,
271 X_Criteria_id NUMBER,
272 X_Sampling_Plan_id NUMBER,
273 X_Collection_Plan_id NUMBER DEFAULT -1,
274 X_SP_WF_Role_Name VARCHAR2,
275 X_SP_Effective_From DATE,
276 X_SP_Effective_To DATE,
277 X_Last_Update_Date DATE,
278 X_Last_Updated_By NUMBER,
279 X_Creation_Date DATE,
280 X_Created_By NUMBER,
281 X_Last_Update_Login NUMBER
282 ) IS
283
284 BEGIN
285
286 UPDATE QA_SAMPLING_ASSOCIATION
287 SET
288 criteria_id = X_Criteria_id,
289 sampling_plan_id = X_Sampling_Plan_id,
290 collection_plan_id = nvl(X_Collection_Plan_id, -1),
291 wf_role_name = X_SP_WF_Role_Name,
292 effective_from = X_SP_Effective_From,
293 effective_to = X_SP_Effective_To,
294 last_update_date = X_Last_Update_Date,
295 last_updated_by = X_Last_Updated_By,
296 creation_date = X_Creation_Date,
297 created_by = X_Created_By,
298 last_update_login = X_Last_Update_Login
299 WHERE rowid = X_Rowid;
300
301 --commit;
302
303 if (SQL%NOTFOUND) then
304 Raise NO_DATA_FOUND;
305 end if;
306
307 END update_sp_association_row;
308
309
310
311 PROCEDURE update_sl_association_row(
312 X_Rowid IN OUT NOCOPY VARCHAR2,
313 X_Criteria_id NUMBER,
314 X_Process_id NUMBER,
315 X_SL_WF_Role_Name VARCHAR2,
316 X_SL_Effective_From DATE,
317 X_SL_Effective_To DATE,
318 X_Lotsize_From NUMBER,
319 X_Lotsize_To NUMBER,
320 X_Insp_Stage VARCHAR2,
321 X_Last_Update_Date DATE,
322 X_Last_Updated_By NUMBER,
323 X_Creation_Date DATE,
324 X_Created_By NUMBER,
325 X_Last_Update_Login NUMBER
326 ) IS
327
328 BEGIN
329
330 UPDATE QA_SKIPLOT_ASSOCIATION
331 SET
332 criteria_id = X_Criteria_id,
333 process_id = X_Process_id,
334 effective_from = X_SL_Effective_From,
335 effective_to = X_SL_Effective_To,
336 lotsize_from = X_Lotsize_From,
337 lotsize_to = X_Lotsize_To,
338 wf_role_name = X_SL_WF_Role_Name,
339 insp_stage = X_Insp_Stage,
340 last_update_date = X_Last_Update_Date,
341 last_updated_by = X_Last_Updated_By,
342 creation_date = X_Creation_Date,
343 created_by = X_Created_By,
344 last_update_login = X_Last_Update_Login
345 WHERE rowid = X_Rowid;
346
347 --commit;
348
349 if (SQL%NOTFOUND) then
350 Raise NO_DATA_FOUND;
351 end if;
352
353 END update_sl_association_row;
354
355
356
357
358 PROCEDURE lock_sl_sp_rcv_criteria_row(
359 X_Rowid VARCHAR2,
360 X_Criteria_id NUMBER,
361 X_Organization_id NUMBER,
362 X_Vendor_id NUMBER DEFAULT -1,
363 X_Vendor_Site_id NUMBER DEFAULT -1,
364 X_Item_id NUMBER DEFAULT -1,
365 X_Item_Revision VARCHAR2 DEFAULT '-1',
366 X_Category_id NUMBER DEFAULT -1,
367 X_Manufacturer_id NUMBER DEFAULT -1,
368 X_Project_id NUMBER DEFAULT -1,
369 X_Task_id NUMBER DEFAULT -1,
370 X_Last_Update_Date DATE,
371 X_Last_Updated_By NUMBER,
372 X_Creation_Date DATE,
373 X_Created_By NUMBER,
374 X_Last_Update_Login NUMBER
375 ) IS
376
377 CURSOR C IS
378 SELECT *
379 FROM QA_SL_SP_RCV_CRITERIA
380 WHERE rowid = X_Rowid
381 FOR UPDATE of criteria_id NOWAIT;
382
383 Recinfo C%ROWTYPE;
384
385
386 BEGIN
387
388 OPEN C;
389 FETCH C INTO Recinfo;
390
391 if (C%NOTFOUND) then
392 CLOSE C;
393 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
394 APP_EXCEPTION.Raise_Exception;
395 end if;
396
397 CLOSE C;
398
399 if (
400 (Recinfo.criteria_id = X_Criteria_id)
401 AND (Recinfo.organization_id = X_Organization_id)
402 AND (Recinfo.vendor_id = nvl(X_Vendor_id, -1))
403 AND (Recinfo.vendor_site_id = nvl(X_Vendor_Site_id, -1))
404 AND (Recinfo.item_id = nvl(X_Item_id, -1))
405 AND (Recinfo.item_revision = nvl(X_Item_Revision, '-1'))
406 AND (Recinfo.item_category_id = nvl(X_Category_id, -1))
407 AND (Recinfo.manufacturer_id = nvl(X_Manufacturer_id, -1))
408 AND (Recinfo.project_id = nvl(X_Project_id, -1))
409 AND (Recinfo.task_id = nvl(X_Task_id, -1))
410 AND (Recinfo.last_update_date = X_Last_Update_Date)
411 AND (Recinfo.last_updated_by = X_Last_Updated_By)
412 AND (Recinfo.creation_date = X_Creation_Date)
413 AND (Recinfo.created_by = X_Created_By)
414 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
415 OR ( (Recinfo.last_update_login IS NULL)
416 AND (X_Last_Update_Login IS NULL)))
417
418 ) then
419
420 return;
421
422 else
423 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
424 APP_EXCEPTION.Raise_Exception;
425
426 end if;
427
428 END lock_sl_sp_rcv_criteria_row;
429
430
431
432
433 PROCEDURE lock_sp_association_row(
434 X_Rowid VARCHAR2,
435 X_Criteria_id NUMBER,
436 X_Sampling_Plan_id NUMBER,
437 X_Collection_Plan_id NUMBER DEFAULT -1,
438 X_SP_WF_Role_Name VARCHAR2,
439 X_SP_Effective_From DATE,
440 X_SP_Effective_To DATE,
441 X_Last_Update_Date DATE,
442 X_Last_Updated_By NUMBER,
443 X_Creation_Date DATE,
444 X_Created_By NUMBER,
445 X_Last_Update_Login NUMBER
446 ) IS
447
448 CURSOR C IS
449 SELECT *
450 FROM QA_SAMPLING_ASSOCIATION
451 WHERE rowid = X_Rowid
452 FOR UPDATE of criteria_id NOWAIT;
453
454 Recinfo C%ROWTYPE;
455
456 BEGIN
457
458 OPEN C;
459 FETCH C INTO Recinfo;
460
461 if (C%NOTFOUND) then
462 CLOSE C;
463 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
464 APP_EXCEPTION.Raise_Exception;
465 end if;
466
467 CLOSE C;
468
469 if (
470 (Recinfo.criteria_id = X_Criteria_id)
471 AND (Recinfo.sampling_plan_id = X_Sampling_Plan_id)
472 AND (Recinfo.collection_plan_id = X_Collection_Plan_id)
473 AND ( (Recinfo.wf_role_name = X_SP_WF_Role_Name)
474 OR ( (Recinfo.wf_role_name IS NULL)
475 AND (X_SP_WF_Role_Name IS NULL)))
476 AND ( (Recinfo.effective_from = X_SP_Effective_From)
477 OR ( (Recinfo.effective_from IS NULL)
478 AND (X_SP_Effective_From IS NULL)))
479 AND ( (Recinfo.effective_to = X_SP_Effective_To)
480 OR ( (Recinfo.effective_to IS NULL)
481 AND (X_SP_Effective_To IS NULL)))
482 AND (Recinfo.last_update_date = X_Last_Update_Date)
483 AND (Recinfo.last_updated_by = X_Last_Updated_By)
484 AND (Recinfo.creation_date = X_Creation_Date)
485 AND (Recinfo.created_by = X_Created_By)
486 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
487 OR ( (Recinfo.last_update_login IS NULL)
488 AND (X_Last_Update_Login IS NULL)))
489
490 ) then
491 null;
492 -- return;
493
494 else
495 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
496 APP_EXCEPTION.Raise_Exception;
497
498 end if;
499
500 END lock_sp_association_row;
501
502
503
504 PROCEDURE lock_sl_association_row(
505 X_Rowid IN OUT NOCOPY VARCHAR2,
506 X_Criteria_id NUMBER,
507 X_Process_id NUMBER,
508 X_SL_WF_Role_Name VARCHAR2,
509 X_SL_Effective_From DATE,
510 X_SL_Effective_To DATE,
511 X_Lotsize_From NUMBER,
512 X_Lotsize_To NUMBER,
513 X_Insp_Stage VARCHAR2,
514 X_Last_Update_Date DATE,
515 X_Last_Updated_By NUMBER,
516 X_Creation_Date DATE,
517 X_Created_By NUMBER,
518 X_Last_Update_Login NUMBER
519 ) IS
520
521 CURSOR C IS
522 SELECT *
523 FROM QA_SKIPLOT_ASSOCIATION
524 WHERE rowid = X_Rowid
525 FOR UPDATE of criteria_id NOWAIT;
526
527 Recinfo C%ROWTYPE;
528
529
530 BEGIN
531
532 OPEN C;
533 FETCH C INTO Recinfo;
534
535 if (C%NOTFOUND) then
536 CLOSE C;
537 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
538 APP_EXCEPTION.Raise_Exception;
539 end if;
540
541 CLOSE C;
542
543 if (
544 (Recinfo.criteria_id = X_Criteria_id)
545 AND (Recinfo.process_id = X_Process_id)
546 AND ( (Recinfo.effective_from = X_SL_Effective_From)
547 OR ( (Recinfo.effective_from IS NULL)
548 AND (X_SL_Effective_From IS NULL)))
549 AND ( (Recinfo.effective_to = X_SL_Effective_To)
550 OR ( (Recinfo.effective_to IS NULL)
551 AND (X_SL_Effective_To IS NULL)))
552 AND ( (Recinfo.lotsize_from = X_Lotsize_From)
553 OR ( (Recinfo.lotsize_from IS NULL)
554 AND (X_Lotsize_From IS NULL)))
555 AND ( (Recinfo.lotsize_to = X_Lotsize_To)
556 OR ( (Recinfo.lotsize_to IS NULL)
557 AND (X_Lotsize_To IS NULL)))
558 AND (Recinfo.insp_stage = X_Insp_Stage)
559 AND (Recinfo.last_update_date = X_Last_Update_Date)
560 AND (Recinfo.last_updated_by = X_Last_Updated_By)
561 AND (Recinfo.creation_date = X_Creation_Date)
562 AND (Recinfo.created_by = X_Created_By)
563 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
564 OR ( (Recinfo.last_update_login IS NULL)
565 AND (X_Last_Update_Login IS NULL)))
566
567 ) then
568
569 return;
570
571 else
572 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
573 APP_EXCEPTION.Raise_Exception;
574
575 end if;
576
577 END lock_sl_association_row;
578
579
580
581
582 PROCEDURE delete_sl_sp_rcv_criteria_row(X_Rowid VARCHAR2) IS
583
584 BEGIN
585
586 delete from QA_SL_SP_RCV_CRITERIA
587 where rowid = X_Rowid;
588
589 if (SQL%NOTFOUND) then
590 Raise NO_DATA_FOUND;
591 end if;
592
593 END delete_sl_sp_rcv_criteria_row;
594
595
596
597
598
599 PROCEDURE delete_sp_association_row(X_Rowid VARCHAR2) IS
600
601 BEGIN
602 delete from QA_SAMPLING_ASSOCIATION
603 where rowid = X_Rowid;
604
605 if (SQL%NOTFOUND) then
606 Raise NO_DATA_FOUND;
607 end if;
608
609 END delete_sp_association_row;
610
611
612
613
614 PROCEDURE delete_sl_association_row(X_Rowid VARCHAR2) IS
615
616 BEGIN
617
618 delete from QA_SKIPLOT_ASSOCIATION
619 where rowid = X_Rowid;
620
621 if (SQL%NOTFOUND) then
622 Raise NO_DATA_FOUND;
623 end if;
624
625 END delete_sl_association_row;
626
627
628
629
630 END QASLSP_TABLE_HANDLER_PKG;