[Home] [Help]
PACKAGE BODY: APPS.GMD_VARIANT_TIME_POINT_GRP
Source
1 PACKAGE BODY GMD_VARIANT_TIME_POINT_GRP AS
2 /* $Header: GMDGSVTB.pls 120.2 2005/09/06 10:31:09 svankada noship $ */
3
4 --
5 -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter package declarations as shown below
14
15 l_variant_no NUMBER:=-1;
16
17 function get_max_variant_no(p_ss_id NUMBER) return NUMBER;
18
19 PROCEDURE create_variants_time_points
20 ( p_stability_study IN NUMBER,
21 p_material_source_id IN NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2) IS
23
24
25 cursor c_stab_study(p_stability_study_id NUMBER) is
26 select packages_cnt,
27 base_spec_id ,
28 storage_plan_id,
29 storage_conditions_cnt ,
30 scheduled_start_date,
31 actual_start_date,
32 created_by
33 from gmd_stability_studies_b
34 where ss_id = p_stability_study_id
35 and delete_mark=0;
36
37
38 -- bug# 2962208.
39 -- For the variants generated by the system, the variant start date should be
40 -- defaulted to the variant material source yield date.If yield date is not present
41 -- take the Stability study schedule start date
42 -- include the yield date in the cursor.
43
44 -- JD Changed lot_no to lot_number and removed lot_id as not used.
45
46 cursor c_mat_source(p_ss_id number,
47 p_material_source_id NUMBER) is
48 select ss_id,
49 source_id,
50 batch_id,
51 recipe_id ,
52 lot_number ,
53 yield_date
54 from gmd_ss_material_sources
55 where delete_mark=0
56 and ss_id = p_ss_id
57 and (p_material_source_id IS NULL
58 OR
59 source_id = p_material_source_id)
60 ;
61
62 l_return_status VARCHAR2(1);
63 l_package_cnt NUMBER;
64
65
66 BEGIN
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 l_variant_no := get_max_variant_no(p_stability_study);
70
71
72
73
74
75 for l_stab_study in c_stab_study(p_stability_study) loop
76
77 --When p_material_source_id is null, only package_cnt has changed.
78 -- if gmd_stability_studies.package_cnt < no. of records in variants
79 -- make the variants records for difference.
80 if (p_material_source_id IS NULL) then
81 select max(cnt)
82 into l_package_cnt
83 from
84 (select count(*) cnt,
85 material_source_id,
86 storage_spec_id
87 from gmd_ss_variants a
88 where a.ss_id = p_stability_study
89 group by material_source_id, storage_spec_id);
90
91 l_package_cnt := l_stab_study.packages_cnt - l_package_cnt;
92
93 --dbms_output.put_line('the value of l_package_cnt is '||l_package_cnt);
94 --dbms_output.put_line('the value of l_stab_study.packages_cnt is '||l_stab_study.packages_cnt);
95 if l_package_cnt <= 0 then
96 --dbms_output.put_line('the No. of variants package cnt is greater then or equal to stability study package cnt');
97 exit;
98 end if;
99 end if;
100
101 if l_package_cnt IS NULL then
102 l_package_cnt := l_stab_study.packages_cnt;
103 end if;
104
105 for l_mat_source in c_mat_source(p_stability_study,
106 p_material_source_id) loop
107
108 -- bug# 2962208.Pass yield date.
109 --call insert to variants table. variant_id
110 create_variants(p_stability_study,
111 l_package_cnt,
112 l_mat_source.source_id,
113 l_stab_study.storage_plan_id,
114 l_stab_study.base_spec_id,
115 NVL(l_mat_source.yield_date,l_stab_study.scheduled_start_date),
116 l_stab_study.actual_start_date,
117 l_stab_study.created_by,
118 l_return_status);
119
120 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
121 RAISE FND_API.G_EXC_ERROR;
122 END IF;
123
124 end loop;
125 end loop;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 --dbms_output.put_line('GMD_VARIANT_TIME_POINT_GRP.CREATE_VARIANTS_TIME_POINTS');
130 --dbms_output.put_line(SUBSTR(SQLERRM,1,100));
131 GMD_API_PUB.Log_Message('GMD_API_ERROR',
132 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.CREATE_VARIANTS_TIME_POINTS',
133 'ERROR', SUBSTR(SQLERRM,1,100));
134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
135
136 END create_variants_time_points;
137
138
139 procedure create_variants(p_ss_id NUMBER,
140 p_no_of_packages NUMBER,
141 p_material_source_id NUMBER,
142 p_storage_plan_id NUMBER,
143 p_base_spec_id NUMBER,
144 p_scheduled_start_date DATE,
145 p_actual_start_date DATE,
146 p_created_by NUMBER,
147 x_return_status OUT NOCOPY VARCHAR2) IS
148 cursor c_storage_plan(p_storage_plan_id number) is
149 select storage_spec_id ,
150 resources ,
151 storage_organization_id,
152 storage_subinventory,
153 storage_locator_id,
154 test_interval_plan_id ,
155 storage_plan_detail_id
156 from gmd_storage_plan_details
157 where storage_plan_id = p_storage_plan_id;
158
159 -- JD changed whse_code to storage_subinventory.
160 -- JD changed location to storage_locator_id.
161
162 l_variant_id NUMBER;
163 l_return_status VARCHAR2(1);
164
165 begin
166 x_return_status := FND_API.G_RET_STS_SUCCESS;
167
168
169 for package_counter in 1..p_no_of_packages loop
170 for l_storage_plan in c_storage_plan(p_storage_plan_id) loop
171
172 select gmd_qc_ss_variant_id_s.nextval
173 into l_variant_id
174 from dual;
175 l_variant_no := l_variant_no +1;
176
177 -- JD changed whse_code to storage_subinventory.
178 -- JD changed location to storage_locator_id.
179
180 insert into gmd_ss_variants
181 (variant_id,
182 variant_no,
183 ss_id,
184 material_source_id,
185 storage_plan_detail_id,
186 default_spec_id,
187 scheduled_start_date,
188 samples_per_time_point,
189 retained_samples,
190 storage_spec_id,
191 resources,
192 storage_organization_id, --INVCONV
193 storage_subinventory, --INVCONV
194 storage_locator_id, --INVCONV
195 storage_date,
196 delete_mark,
197 creation_date,
198 created_by,
199 last_updated_by,
200 last_update_date,
201 last_update_login)
202 values (l_variant_id,
203 l_variant_no,
204 p_ss_id,
205 p_material_source_id,
206 l_storage_plan.storage_plan_detail_id,
207 p_base_spec_id,
208 p_scheduled_start_date,
209 1,
210 0,
211 l_storage_plan.storage_spec_id,
212 l_storage_plan.resources,
213 l_storage_plan.storage_organization_id, --INVCONV
214 l_storage_plan.storage_subinventory, --INVCONV
215 l_storage_plan.storage_locator_id, --INVCONV
216 p_scheduled_start_date,
217 0,
218 sysdate,
219 p_created_by,
220 p_created_by,
221 sysdate,
222 p_created_by);
223
224
225 create_time_points(l_variant_id,
226 p_scheduled_start_date,
227 p_base_spec_id,
228 l_storage_plan.test_interval_plan_id,
229 p_actual_start_date,
230 1,
231 p_created_by,
232 l_return_status);
233
234 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
235 RAISE FND_API.G_EXC_ERROR;
236 END IF;
237
238 end loop;
239 end loop;
240 EXCEPTION
241 WHEN OTHERS THEN
242 --dbms_output.put_line('GMD_VARIANT_TIME_POINT_GRP.CREATE_VARIANTS');
243 --dbms_output.put_line(SUBSTR(SQLERRM,1,100));
244 GMD_API_PUB.Log_Message('GMD_API_ERROR',
245 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.CREATE_VARIANTS',
246 'ERROR', SUBSTR(SQLERRM,1,100));
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
248 end create_variants;
249
250 procedure create_time_points(p_variant_id NUMBER,
251 p_scheduled_start_date DATE,
252 p_base_spec_id NUMBER,
253 p_test_interval_plan_id NUMBER,
254 p_actual_date DATE,
255 p_samples_per_time_point NUMBER,
256 p_created_by NUMBER,
257 x_return_status OUT NOCOPY VARCHAR2) IS
258 cursor c_time_interval_plan(p_test_interval_plan_id number) is
259 select period_id, name ,
260 years_from_start ,
261 months_from_start ,
262 weeks_from_start ,
263 days_from_start ,
264 hours_from_start
265 from gmd_test_interval_plan_periods
266 where test_interval_plan_id = p_test_interval_plan_id
267 and nvl(exclude,'N') = 'N'
268 order by simulated_date;
269
270
271
272 l_test_interval_plan_id NUMBER;
273
274 begin
275 x_return_status := FND_API.G_RET_STS_SUCCESS;
276 if (p_test_interval_plan_id IS NULL) then
277 select a.test_interval_plan_id
278 into l_test_interval_plan_id
279 from gmd_storage_plan_details a,
280 gmd_ss_variants b
281 where a.storage_plan_detail_id = b.storage_plan_detail_id
282 and b.variant_id = p_variant_id
283 ;
284 else
285 l_test_interval_plan_id := p_test_interval_plan_id;
286 end if;
287
288 for l_time_interval_plan in c_time_interval_plan(l_test_interval_plan_id) loop
289 insert into gmd_ss_time_points
290 (
291 time_point_id,
292 name,
293 variant_id,
294 period_id ,
295 years_from_start,
296 months_from_start,
297 weeks_from_start,
298 days_from_start,
299 hours_from_start,
300 additional_ind,
301 spec_id,
302 scheduled_date,
303 --actual_date,
304 samples_per_time_point,
305 delete_mark,
306 creation_date,
307 created_by,
308 last_updated_by,
309 last_update_date,
310 last_update_login)
311 values
312 (gmd_qc_ss_time_point_id_s.nextval,
313 l_time_interval_plan.name,
314 p_variant_id,
315 l_time_interval_plan.period_id,
316 l_time_interval_plan.years_from_start,
317 l_time_interval_plan.months_from_start,
318 l_time_interval_plan.weeks_from_start,
319 l_time_interval_plan.days_from_start ,
320 l_time_interval_plan.hours_from_start,
321 'N',
322 p_base_spec_id,
323 ADD_MONTHS(p_scheduled_start_date,
324 ((NVL(l_time_interval_plan.years_from_start,0) * 12 )
325 + NVL(l_time_interval_plan.months_from_start,0)))
326 +((NVL(l_time_interval_plan.weeks_from_start,0) * 7 )
327 + NVL(l_time_interval_plan.days_from_start,0)
328 + (NVL(l_time_interval_plan.hours_from_start,0)/24)),
329 /* ADD_MONTHS(p_actual_date,
330 ((NVL(l_time_interval_plan.years_from_start,0) * 12 )
331 + NVL(l_time_interval_plan.months_from_start,0)))
332 +((NVL(l_time_interval_plan.weeks_from_start,0) * 7 )
333 + NVL(l_time_interval_plan.days_from_start,0)
334 + (NVL(l_time_interval_plan.hours_from_start,0)/24)),*/
335 p_samples_per_time_point,
336 0,
337 sysdate,
338 p_created_by,
339 p_created_by,
340 sysdate,
341 p_created_by);
342 end loop;
343 EXCEPTION
344 WHEN OTHERS THEN
345 --dbms_output.put_line('GMD_VARIANT_TIME_POINT_GRP.CREATE_TIME_POINTS');
346 --dbms_output.put_line(SUBSTR(SQLERRM,1,100));
347
348 GMD_API_PUB.Log_Message('GMD_API_ERROR',
349 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.CREATE_TIME_POINTS',
350 'ERROR', SUBSTR(SQLERRM,1,100));
351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
352 end create_time_points;
353
354
355 PROCEDURE delete_variants
356 (p_material_source_id IN NUMBER,
357 x_return_status OUT NOCOPY VARCHAR2) IS
358 Cursor c_ss_id is
359 select ss_id
360 from gmd_ss_variants
361 where material_source_id = p_material_source_id;
362 l_return_status VARCHAR2(1);
363 l_ss_id NUMBER;
364
365 -- mahesh.
366 CURSOR cr_variants IS
367 SELECT variant_id from gmd_ss_variants
368 where material_source_id = p_material_source_id
369 for update of variant_id NOWAIT;
370
371 CURSOR cr_time_points IS
372 SELECT time_point_id from gmd_ss_time_points
373 where variant_id in (select variant_id
374 from gmd_ss_variants
375 where material_source_id = p_material_source_id)
376 for update of time_point_id NOWAIT ;
377
378 record_lock EXCEPTION ;
379 pragma exception_init(record_lock,-00054) ;
380
381
382
383 begin
384
385 x_return_status := FND_API.G_RET_STS_SUCCESS;
386
387 OPEN c_ss_id;
388 FETCH c_ss_id into l_ss_id;
389 CLOSE c_ss_id;
390
391 -- lock the variants and time points before deleting.
392 -- mahesh
393 OPEN cr_variants ;
394 CLOSE cr_variants ;
395
396 OPEN cr_time_points ;
397 CLOSE cr_time_points ;
398
399 delete gmd_ss_time_points
400 where variant_id in (select variant_id
401 from gmd_ss_variants
402 where material_source_id = p_material_source_id);
403 delete gmd_ss_variants
404 where material_source_id = p_material_source_id;
405
406 update_variant_seq(l_ss_id, l_return_status);
407
408 EXCEPTION
409 WHEN RECORD_LOCK THEN -- mahesh
410 IF cr_variants%ISOPEN THEN
411 CLOSE cr_variants;
412 END IF;
413 IF cr_time_points%ISOPEN THEN
414 CLOSE cr_time_points;
415 END IF;
416 GMD_API_PUB.Log_Message('GMD_SS_VARIANTS_LOCKED');
417 x_return_status := FND_API.G_RET_STS_ERROR ;
418 WHEN OTHERS THEN
419 GMD_API_PUB.Log_Message('GMD_API_ERROR',
420 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.DELETE_VARIANTS',
421 'ERROR', SUBSTR(SQLERRM,1,100));
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
423 end delete_variants;
424
425
426
427 PROCEDURE delete_time_points
428 (p_variant_id IN NUMBER,
429 x_return_status OUT NOCOPY VARCHAR2) IS
430 Cursor c_ss_id is
431 select ss_id
432 from gmd_ss_material_sources
433 where ss_id = p_variant_id;
434 l_return_status VARCHAR2(1);
435 l_ss_id NUMBER;
436
437 -- mahesh.
438
439
440 CURSOR cr_time_points IS
441 SELECT time_point_id from gmd_ss_time_points
442 where variant_id = p_variant_id
443 for update of time_point_id NOWAIT ;
444
445 record_lock EXCEPTION ;
446 pragma exception_init(record_lock,-00054) ;
447
448
449
450 begin
451
452 x_return_status := FND_API.G_RET_STS_SUCCESS;
453
454 -- lock the variants and time points before deleting.
455 -- mahesh
456
457
458 OPEN cr_time_points ;
459 CLOSE cr_time_points ;
460
461 delete gmd_ss_time_points
462 where variant_id = p_variant_id;
463
464
465
466 EXCEPTION
467 WHEN RECORD_LOCK THEN -- mahesh
468
469 IF cr_time_points%ISOPEN THEN
470 CLOSE cr_time_points;
471 END IF;
472 GMD_API_PUB.Log_Message('GMD_SS_VARIANTS_LOCKED');
473 x_return_status := FND_API.G_RET_STS_ERROR ;
474 WHEN OTHERS THEN
475 GMD_API_PUB.Log_Message('GMD_API_ERROR',
476 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.DELETE_TIME_POINTS',
477 'ERROR', SUBSTR(SQLERRM,1,100));
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
479 end delete_time_points;
480
481
482 function get_max_variant_no(p_ss_id NUMBER) RETURN NUMBER IS
483 l_variant_num NUMBER;
484 begin
485 select nvl(max(a.variant_no),0)
486 into l_variant_num
487 from gmd_ss_variants a
488 where a.ss_id = p_ss_id
489 ;
490
491 return l_variant_num;
492 end get_max_variant_no;
493
494 PROCEDURE update_variant_seq
495 (ss_id IN NUMBER,
496 x_return_status OUT NOCOPY VARCHAR2) is
497 cursor c_variant_no (p_ss_id NUMBER) is
498 select variant_id,variant_no, rownum
499 from gmd_ss_variants
500 where ss_id = p_ss_id
501 order by variant_no;
502 begin
503 for l_variant_no in c_variant_no(ss_id) loop
504 update gmd_ss_variants
505 set variant_no = l_variant_no.rownum
506 where variant_id = l_variant_no.variant_id;
507 end loop;
508 EXCEPTION
509 WHEN OTHERS THEN
510 GMD_API_PUB.Log_Message('GMD_API_ERROR',
511 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_VARIANT_SEQ',
512 'ERROR', SUBSTR(SQLERRM,1,100));
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
514 end update_variant_seq;
515
516
517 PROCEDURE update_base_spec( p_ss_id IN NUMBER,
518 p_base_spec_id IN NUMBER,
519 x_return_status OUT NOCOPY VARCHAR2 ) IS
520 -- mahesh.
521 CURSOR cr_variants IS
522 SELECT default_spec_id from gmd_ss_variants
523 where ss_id = p_ss_id
524 for update of default_spec_id NOWAIT;
525
526 CURSOR cr_time_points IS
527 SELECT spec_id from gmd_ss_time_points
528 where variant_id in (select variant_id
529 from gmd_ss_variants
530 where ss_id = p_ss_id)
531 for update of spec_id NOWAIT ;
532
533 record_lock EXCEPTION ;
534 pragma exception_init(record_lock,-00054) ;
535 REQ_FIELDS_MISSING EXCEPTION;
536
537 BEGIN
538
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 IF p_ss_id IS NULL OR p_base_spec_id IS NULL THEN
542 RAISE REQ_FIELDS_MISSING ;
543 END IF;
544
545 -- lock the variants and time points before updating.
546 -- mahesh
547 OPEN cr_variants ;
548 CLOSE cr_variants ;
549
550 OPEN cr_time_points ;
551 CLOSE cr_time_points ;
552
553 UPDATE gmd_ss_variants
554 SET default_spec_id = p_base_spec_id
555 WHERE ss_id = p_ss_id ;
556
557 UPDATE gmd_ss_time_points
558 SET spec_id = p_base_spec_id
559 WHERE variant_id in (select variant_id from gmd_ss_variants
560 where ss_id = p_ss_id) ;
561
562 EXCEPTION
563 WHEN RECORD_LOCK THEN -- mahesh
564 IF cr_variants%ISOPEN THEN
565 CLOSE cr_variants;
566 END IF;
567 IF cr_time_points%ISOPEN THEN
568 CLOSE cr_time_points;
569 END IF;
570 GMD_API_PUB.Log_Message('GMD_SS_VARIANTS_LOCKED');
571 x_return_status := FND_API.G_RET_STS_ERROR ;
572 WHEN REQ_FIELDS_MISSING THEN
573 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_BASE_SPEC');
574 x_return_status := FND_API.G_RET_STS_ERROR ;
575 WHEN OTHERS THEN
576 GMD_API_PUB.Log_Message('GMD_API_ERROR',
577 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_BASE_SPEC','ERROR', SUBSTR(SQLERRM,1,100));
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
579 END update_base_spec ;
580
581 PROCEDURE update_scheduled_start_date
582 (p_ss_id IN NUMBER,
583 p_scheduled_start_date IN DATE,
584 x_return_status OUT NOCOPY VARCHAR2 ) IS
585 -- mahesh.
586 CURSOR cr_variants IS
587 SELECT scheduled_start_date from gmd_ss_variants
588 where ss_id = p_ss_id
589 for update of scheduled_start_date NOWAIT;
590
591 CURSOR cr_time_points IS
592 SELECT spec_id from gmd_ss_time_points
593 where variant_id in (select variant_id
594 from gmd_ss_variants
595 where ss_id = p_ss_id)
596 for update of spec_id NOWAIT ;
597
598 record_lock EXCEPTION ;
599 pragma exception_init(record_lock,-00054) ;
600
601 REQ_FIELDS_MISSING EXCEPTION;
602
603 BEGIN
604
605 x_return_status := FND_API.G_RET_STS_SUCCESS;
606
607 IF p_ss_id IS NULL OR p_scheduled_start_date IS NULL THEN
608 RAISE REQ_FIELDS_MISSING ;
609 END IF;
610
611 -- lock the variants and time points before deleting.
612 -- mahesh
613 OPEN cr_variants ;
614 CLOSE cr_variants ;
615
616 OPEN cr_time_points ;
617 CLOSE cr_time_points ;
618
619 UPDATE gmd_ss_variants
620 SET scheduled_start_date = p_scheduled_start_date
621 WHERE ss_id = p_ss_id ;
622
623 UPDATE gmd_ss_time_points
624 SET scheduled_date = ADD_MONTHS(p_scheduled_start_date,((nvl(years_from_start,0) * 12 ) + nvl(months_from_start,0))) +
625 ((nvl(weeks_from_start,0) * 7 ) + nvl(days_from_start,0) + (nvl(hours_from_start,0)/24))
626 WHERE variant_id in (select variant_id from gmd_ss_variants
627 where ss_id = p_ss_id) ;
628
629
630
631 EXCEPTION
632 WHEN RECORD_LOCK THEN -- mahesh
633 IF cr_variants%ISOPEN THEN
634 CLOSE cr_variants;
635 END IF;
636 IF cr_time_points%ISOPEN THEN
637 CLOSE cr_time_points;
638 END IF;
639 GMD_API_PUB.Log_Message('GMD_SS_VARIANTS_LOCKED');
640 x_return_status := FND_API.G_RET_STS_ERROR ;
641 WHEN REQ_FIELDS_MISSING THEN
642 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_SCHEDULED_START_DATE');
643 x_return_status := FND_API.G_RET_STS_ERROR ;
644 WHEN OTHERS THEN
645 GMD_API_PUB.Log_Message('GMD_API_ERROR',
646 'PACKAGE','GMD_VARIANT_TIME_POINT_GRP.UPDATE_SCHEDULED_START_DATE','ERROR', SUBSTR(SQLERRM,1,100));
647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
648
649 END update_scheduled_start_date;
650
651
652 PROCEDURE submit_srs_request(p_variant_id IN NUMBER,
653 p_time_point_id IN NUMBER,
654 p_conc_id OUT NOCOPY NUMBER ) is
655 BEGIN
656 p_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMD', 'SAMPLE_LABEL_GEN_STBL', '', '', FALSE,
657 '', '', '',',',p_variant_id, p_time_point_id);
658 END submit_srs_request;
659 END GMD_VARIANT_TIME_POINT_GRP; -- Package body