[Home] [Help]
PACKAGE BODY: APPS.GMD_SS_WFLOW_GRP
Source
1 PACKAGE BODY GMD_SS_WFLOW_GRP AS
2 /* $Header: GMDSWFGB.pls 120.2 2006/04/12 12:17:44 mgrosser noship $ */
3
4 /*
5 -- Start of comments
6 --
7 -- Copyright (c) 2003 Oracle Corporation
8 -- Redwood Shores, CA, USA
9 -- All Rights Reserved
10 --
11 ----------------------------------------------------------
12 --
13 -- File Name: GMDSWFGB.pls
14 -- Package Name: GMD_SS_WFLOW_GRP
15 -- Type: Group
16 -- History :
17 --
18 -- Saikiran Vankadari 19-May-04 Bug# 3583257. In the 'events_for_status_change' procedure,
19 -- sublot_no is assigned to p_sample before inserting the sample.
20 -- Also, Code is changed in such a way that for a particular variant,
21 -- retained samples are inserted after the timepoint samples
22 -- for that particular variant are inserted.
23 -- Saikiran Vankadari 29-Jun-04 Bug# 3729234. Assigning '0RT' to all dispositions instead of '1P' in the
24 -- 'events_for_status_change' procedure
25 -- Saikiran Vankadari 16-Aug-05 Convergence Changes
26 -- M. Grosser 12-Apr-2006 BUG 4695552 - Modified procedures variant_retianed_sample and events_for_status_change
27 -- to pass revision for creation of samples and sampling events
28 -------------------------------------------------------- */
29
30 FUNCTION get_spec_vr_id
31 ( p_spec_id IN number,
32 p_created_by IN number)
33 RETURN number
34 IS
35 x_spec_vr_id number;
36 l_spec_vr_id number;
37
38 CURSOR c_get_spec_vr IS
39 SELECT spec_vr_id
40 FROM GMD_STABILITY_SPEC_VRS
41 WHERE spec_id = p_spec_id;
42 tp_vr c_get_spec_vr%ROWTYPE;
43
44 BEGIN
45 OPEN c_get_spec_vr;
46 FETCH c_get_spec_vr into x_spec_vr_id;
47 IF c_get_spec_vr%NOTFOUND THEN
48 select gmd.gmd_qc_spec_vr_id_s.NEXTVAL
49 into x_spec_vr_id
50 from dual;
51 /* insert into bfs_msg
52 values
53 ( 'NEW: spec_vr_id = ' || x_spec_vr_id );
54 commit; */
55 INSERT INTO GMD_STABILITY_SPEC_VRS
56 (
57 SPEC_VR_ID
58 ,SPEC_ID
59 ,SPEC_VR_STATUS
60 ,START_DATE
61 ,DELETE_MARK
62 ,CREATION_DATE
63 ,CREATED_BY
64 ,LAST_UPDATE_DATE
65 ,LAST_UPDATED_BY )
66 values (
67 x_spec_vr_id
68 ,p_spec_id
69 ,700
70 ,sysdate
71 ,0
72 ,sysdate
73 ,p_created_by
74 ,sysdate
75 ,p_created_by
76 );
77 END IF;
78
79
80 RETURN x_spec_vr_id;
81 END ;
82
83 PROCEDURE variant_retained_sample
84 ( p_variant_id IN number,
85 p_time_point_id IN number,
86 p_spec_id IN number,
87 x_sampling_event_id OUT NOCOPY number,
88 x_return_status OUT NOCOPY varchar2)
89 IS
90 p_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
91 x_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
92 p_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
93 x_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
94
95 CURSOR c_get_variant IS
96 SELECT ss_id,
97 sample_qty,
98 sample_quantity_uom --INVCONV
99 FROM GMD_SS_VARIANTS v
100 WHERE v.variant_id = p_variant_id;
101 var_rec c_get_variant%ROWTYPE;
102
103 -- M. Grosser 12-Apr-2006 BUG 4695552 - Modified procedures variant_retianed_sample and events_for_status_change
104 -- to pass revision for creation of samples and sampling events
105 CURSOR c_get_ss IS
106 SELECT inventory_item_id,
107 revision,
108 organization_id,
109 created_by
110 FROM gmd_stability_studies ss
111 WHERE ss_id = var_rec.ss_id;
112 ss_rec c_get_ss%ROWTYPE;
113
114 BEGIN
115 x_return_status := 'S';
116 OPEN c_get_variant;
117 FETCH c_get_variant into var_rec;
118 IF c_get_variant%FOUND THEN
119 OPEN c_get_ss;
120 FETCH c_get_ss into ss_rec;
121 IF c_get_ss%FOUND THEN
122 p_sampling_event.original_spec_vr_id :=
123 get_spec_vr_id (p_spec_id,
124 ss_rec.created_by);
125 p_sampling_event.disposition := '1P';
126 /* p_sampling_event.event_type_code := ''; */
127 /* p_sampling_event.event_id := ; */
128 p_sampling_event.source := 'T';
129 p_sampling_event.inventory_item_id := ss_rec.inventory_item_id; --INVCONV
130
131 -- M. Grosser 12-Apr-2006 BUG 4695552 - Modified procedures variant_retianed_sample and events_for_status_change
132 -- to pass revision for creation of samples and sampling events
133 --
134 p_sampling_event.revision := ss_rec.revision;
135
136 p_sampling_event.organization_id := ss_rec.organization_id; --INVCONV
137 p_sampling_event.sample_type := 'I';
138 p_sampling_event.variant_id := p_variant_id;
139
140 /* magupta 2949364, time_point_id set to passed time point id */
141 p_sampling_event.time_point_id := p_time_point_id;
142 --p_sampling_event.time_point_id := NULL;
143 /* magupta 2949364, time_point_id set to passed time point id */
144
145 p_sampling_event.creation_date := sysdate;
146 p_sampling_event.created_by := ss_rec.created_by;
147 p_sampling_event.last_updated_by := ss_rec.created_by;
148 p_sampling_event.last_update_date := sysdate;
149 p_sampling_event.sample_taken_cnt := 1;
150 p_sampling_event.sample_req_cnt := 1;
151 --p_sampling_event.sample_active_cnt := 1;
152 p_sampling_event.sample_active_cnt := 0;
153 p_event_spec_disp.disposition := '1P';
154 p_event_spec_disp.spec_used_for_lot_attrib_ind := 'Y';
155 p_event_spec_disp.spec_id := p_spec_id;
156 p_event_spec_disp.spec_vr_id :=
157 p_sampling_event.original_spec_vr_id;
158 p_event_spec_disp.delete_mark := 0;
159 p_event_spec_disp.creation_date := sysdate;
160 p_event_spec_disp.created_by := ss_rec.created_by;
161 p_event_spec_disp.last_update_date := sysdate;
162 p_event_spec_disp.last_updated_by := ss_rec.created_by;
163 IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
164 p_sampling_event,
165 x_sampling_event ) THEN
166 raise fnd_api.g_exc_error;
167 END IF;
168 /* insert into bfs_msg
169 values
170 ( 'sampling_event_id = '||
171 x_sampling_event.sampling_event_id );
172 commit; */
173 p_event_spec_disp.sampling_event_id :=
174 x_sampling_event.sampling_event_id;
175 x_sampling_event_id := x_sampling_event.sampling_event_id;
176 IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
177 p_event_spec_disp,
178 x_event_spec_disp ) THEN
179 raise fnd_api.g_exc_error;
180 END IF;
181 /* insert into bfs_msg
182 values
183 ( 'event_spec_disp_id = '||
184 x_event_spec_disp.event_spec_disp_id );
185 commit; */
186 END IF;
187 CLOSE c_get_ss;
188 END IF;
189 CLOSE c_get_variant;
190 RETURN;
191 EXCEPTION
192 WHEN FND_API.G_EXC_ERROR THEN
193 x_return_status := 'E';
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 x_return_status := 'U';
196 WHEN OTHERS THEN
197 x_return_status := 'E';
198 END variant_retained_sample;
199
200
201
202
203
204
205
206
207 PROCEDURE events_for_status_change
208 ( p_ss_id IN number,
209 x_return_status OUT NOCOPY varchar2)
210 IS
211 l_status number;
212 l_spec_vr_id number;
213 l_msg varchar2(100);
214 p_sample GMD_SAMPLES%ROWTYPE;
215 x_sample GMD_SAMPLES%ROWTYPE;
216 p_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
217 x_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
218 p_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
219 x_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
220 p_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
221 x_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
222
223 -- M. Grosser 12-Apr-2006 BUG 4695552 - Modified procedures variant_retianed_sample and events_for_status_change
224 -- to pass revision for creation of samples and sampling events
225 --
226 --modified for INVCONV
227 CURSOR c_get_ss IS
228 SELECT inventory_item_id,
229 status,
230 organization_id,
231 lab_organization_id,
232 created_by,
233 last_updated_by,
234 revision
235 FROM GMD_STABILITY_STUDIES_B
236 WHERE ss_id = p_ss_id;
237 ss_rec c_get_ss%ROWTYPE;
238
239 --modified for INVCONV
240 CURSOR c_get_ms IS
241 SELECT a.source_id,
242 a.source_organization_id,
243 a.batch_id,
244 a.recipe_id,
245 a.recipe_no,
246 a.lot_number,
247 a.sampling_event_id
248 FROM GMD_SS_MATERIAL_SOURCES a
249 WHERE ss_id = p_ss_id;
250
251
252 --modified for INVCONV
253 mat_rec c_get_ms%ROWTYPE;
254 CURSOR c_get_var IS
255 SELECT variant_id,
256 variant_no,
257 samples_per_time_point,
258 retained_samples,
259 default_spec_id,
260 /* default_spec_vr_id, */
261 sample_qty,
262 sample_quantity_uom
263 FROM GMD_SS_VARIANTS
264 WHERE material_source_id = mat_rec.source_id;
265
266 var_rec c_get_var%ROWTYPE;
267 CURSOR c_get_tp IS
268 SELECT time_point_id,
269 spec_id,
270 scheduled_date,
271 samples_per_time_point
272 FROM GMD_SS_TIME_POINTS tp
273 WHERE tp.variant_id = var_rec.variant_id ;
274 tp_rec c_get_tp%ROWTYPE;
275
276 CURSOR cr_sampling_event_created IS
277 SELECT 'X' FROM gmd_ss_variants gsv
278 where gsv.ss_id = p_ss_id
279 and exists
280 ( select 'x' from gmd_sampling_events gse
281 where gse.variant_id = gsv.variant_id ) ;
282
283 l_temp VARCHAR2(1);
284
285 BEGIN
286 x_return_status := 'S';
287
288 -- Mahesh.
289 -- If samples are created , don't create it again.
290 -- this API is called from lot of places ( workflow , stability study form , eres workflow )
291 -- just making sure in case it is called twice, samples are not created again.
292
293 OPEN cr_sampling_event_created ;
294 FETCH cr_sampling_event_created into l_temp ;
295 IF cr_sampling_event_created%FOUND THEN
296 CLOSE cr_sampling_event_created;
297 RETURN ;
298 END IF;
299 CLOSE cr_sampling_event_created ;
300
301 OPEN c_get_ss ;
302 FETCH c_get_ss into ss_rec;
303 /* insert into bfs_msg
304 values
305 ( 'p_ss_id = ' || p_ss_id); */
306 /* insert into bfs_msg
307 values
308 ( 'ss_rec.item_id = '|| ss_rec.item_id );
309 commit; */
310 /* need to change to status = 400 */
311 IF ss_rec.status > 0
312 THEN
313 OPEN c_get_ms;
314 FETCH c_get_ms into mat_rec;
315 WHILE c_get_ms%FOUND LOOP
316
317 p_sample.lot_number := mat_rec.lot_number; --INVCONV
318
319
320 IF mat_rec.sampling_event_id is null THEN
321 IF mat_rec.lot_number is not null THEN
322 /*
323 -- Material source has a lot number therefore, a
324 -- Create Sample workflow notification is generated */
325 gmd_api_pub.RAISE (
326 'oracle.apps.gmd.qm.ss.lot',
327 to_char(mat_rec.source_id,9999999999 ));
328 /* insert into bfs_msg
329 values
330 ( 'Create Sample workflow notification= ' ||
331 to_char(mat_rec.source_id,9999999999) );
332 commit; */
333 ELSE
334 /*
335 -- Material source needs to be produced, a Create Batch
336 -- workflow notification is generated */
337 gmd_api_pub.RAISE (
338 'oracle.apps.gmd.qm.ss.batch.cr',
339 mat_rec.source_id );
340 /* insert into bfs_msg
341 values
342 ( 'Create Batch workflow notification= '||
343 mat_rec.source_id);
344 commit; */
345 END IF;
346 END IF;
347 /* ELSE this is for status not correct */
348 /*
349 -- The material source has a lot and corresponding
350 -- sampling event. Therefore, the time point samples
351 -- can be created. */
352 /* insert into bfs_msg
353 values
354 ('creating time point samples');
355 commit; */
356 /*
357 -- Retrieve the material source's corresponding variants */
358 OPEN c_get_var;
359 FETCH c_get_var into var_rec;
360 WHILE c_get_var%FOUND LOOP
361 /* insert into bfs_msg
362 values
363 ('Varint_id = '|| var_rec.variant_id);
364 commit; */
365 p_sampling_event.original_spec_vr_id := l_spec_vr_id;
366 --Bug# 3729234. Assigning '0RT' to p_sampling_event.disposition instead of '1P'
367 --p_sampling_event.disposition := '1P';
368 p_sampling_event.disposition := '0RT';
369 /* p_sampling_event.event_type_code := ''; */
370 /* p_sampling_event.event_id := ; */
371 p_sampling_event.source := 'T';
372 p_sampling_event.inventory_item_id := ss_rec.inventory_item_id; --INVCONV
373
374 -- M. Grosser 12-Apr-2006 BUG 4695552 - Modified procedures variant_retianed_sample and events_for_status_change
375 -- to pass revision for creation of samples and sampling events
376 --
377 p_sampling_event.revision := ss_rec.revision;
378
379 p_sampling_event.organization_id := ss_rec.organization_id; --INVCONV
380 p_sampling_event.sample_type := 'I';
381 p_sampling_event.variant_id := var_rec.variant_id;
382 p_sampling_event.time_point_id := NULL;
383 p_sampling_event.creation_date := sysdate;
384 p_sampling_event.created_by := ss_rec.created_by;
385 p_sampling_event.last_updated_by := ss_rec.created_by;
386 p_sampling_event.last_update_date := sysdate;
387 p_sample.sample_type := 'I';
388 p_sample.lab_organization_id := ss_rec.lab_organization_id; --INVCONV
389 p_sample.inventory_item_id := ss_rec.inventory_item_id; --INVCONV
390
391 -- M. Grosser 12-Apr-2006 BUG 4695552 - Modified procedures variant_retianed_sample and events_for_status_change
392 -- to pass revision for creation of samples and sampling events
393 --
394 p_sample.revision := ss_rec.revision;
395
396 p_sample.sample_qty := var_rec.sample_qty;
397 p_sample.sample_qty_uom := var_rec.sample_quantity_uom; --INVCONV
398 p_sample.source := 'T';
399 p_sample.date_drawn := sysdate;
400 p_sample.priority := '5N';
401 p_sample.delete_mark := 0;
402 p_sample.sampler_id := ss_rec.created_by;
403 p_sample.creation_date := sysdate;
404 p_sample.created_by := ss_rec.created_by;
405 p_sample.last_update_date := sysdate;
406 p_sample.last_updated_by := ss_rec.created_by;
407 p_sample.organization_id := ss_rec.organization_id; --INVCONV
408 p_sample.variant_id := var_rec.variant_id;
409 p_sample.time_point_id := NULL;
410 --Bug# 3729234. Assigning '0RT' to p_sample.sample_disposition instead of '1P'
411 --p_sample.sample_disposition := '1P';
412 p_sample.sample_disposition := '0RT';
413 --Bug# 3729234. Assigning '0RT' to p_event_spec_disp.disposition instead of '1P'
414 --p_event_spec_disp.disposition := '1P';
415 p_event_spec_disp.disposition := '0RT';
416 p_event_spec_disp.spec_used_for_lot_attrib_ind := 'Y';
417 p_event_spec_disp.delete_mark := 0;
418 p_event_spec_disp.creation_date := sysdate;
419 p_event_spec_disp.created_by := ss_rec.created_by;
420 p_event_spec_disp.last_update_date := sysdate;
421 p_event_spec_disp.last_updated_by := ss_rec.created_by;
422 p_sample_spec_disp.delete_mark := 0;
423 p_sample_spec_disp.creation_date := sysdate;
424 p_sample_spec_disp.created_by := ss_rec.created_by;
425 p_sample_spec_disp.last_update_date := sysdate;
426 p_sample_spec_disp.last_updated_by := ss_rec.created_by;
427 ------------------------------------------------------------------------------------------------------------------
428 --Bug#3583257. moved the piece of code (to insert retained samples) to a different place
429 /*
430 --create variant Reserved samples
431 IF var_rec.retained_samples > 0 THEN
432 p_sampling_event.sample_taken_cnt :=
433 var_rec.retained_samples;
434 p_sampling_event.sample_req_cnt :=
435 var_rec.retained_samples;
436 --p_sampling_event.sample_active_cnt := var_rec.retained_samples;
437 p_sampling_event.sample_active_cnt := 0;
438 IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
439 p_sampling_event,
440 x_sampling_event ) THEN
441 raise fnd_api.g_exc_error;
442 END IF;
443 -- insert into bfs_msg
444 --values
445 -- ( 'Var: sampling_event_id = '||
446 -- x_sampling_event.sampling_event_id );
447 --commit;
448 -- GMD_SS_VARIANTS is updated with the sampling_event_id for the
449 -- Variant's retained samples. The variant's retained samples are
450 -- associated with the same sampling event.
451 UPDATE gmd_ss_variants
452 set sampling_event_id =
453 x_sampling_event.sampling_event_id
454 WHERE variant_id = var_rec.variant_id;
455 p_event_spec_disp.sampling_event_id :=
456 x_sampling_event.sampling_event_id;
457 p_sample.sampling_event_id :=
458 x_sampling_event.sampling_event_id;
459 IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
460 p_event_spec_disp,
461 x_event_spec_disp ) THEN
462 raise fnd_api.g_exc_error;
463 END IF;
464 -- insert into bfs_msg
465 --values
466 --( 'Var: event_spec_disp_id = '||
467 -- x_event_spec_disp.event_spec_disp_id );
468 --commit;
469 p_sample_spec_disp.event_spec_disp_id :=
470 x_event_spec_disp.event_spec_disp_id;
471 p_sample_spec_disp.disposition := '1P';
472 FOR smp_cnt in 1..var_rec.retained_samples LOOP
473 p_sample.sample_no :=
474 GMA_GLOBAL_GRP.Get_Doc_No('SMPL', ss_rec.orgn_code);
475 -- insert into bfs_msg
476 --values
477 --(' sample_no = ' || p_sample.sample_no );
478 --commit;
479
480 l_msg := NULL;
481 fnd_message.set_name('GMD','GMD_SS_VARIANT_SAMPLE_DESC');
482 fnd_message.set_token('VARIANT', var_rec.variant_no);
483 fnd_message.set_token('INST', smp_cnt);
484 l_msg := fnd_message.get;
485
486 p_sample.sample_desc := l_msg;
487 p_sample.sample_instance := smp_cnt;
488 IF not GMD_SAMPLES_PVT.insert_row (
489 p_sample,
490 x_sample ) THEN
491 raise fnd_api.g_exc_error;
492 END IF;
493 p_sample_spec_disp.sample_id := x_sample.sample_id;
494 -- insert into bfs_msg
495 -- values
496 -- (' sample_id = ' || x_sample.sample_id );
497 --commit;
498 IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
499 p_sample_spec_disp ) THEN
500 raise fnd_api.g_exc_error;
501 END IF;
502 -- insert into bfs_msg
503 --values
504 --(' Sample Spec: sample_id = ' || x_sample.sample_id );
505 --commit;
506 END LOOP; -- Loop to create sample for number of s-per-
507 END IF; -- Loop var_rec.retained_samples > 0
508
509 */
510
511 --------------------------------------------------------------------------------------------------------------------
512 /* Get Time Point data and create a sampling event for each Time Point */
513 OPEN c_get_tp;
514 FETCH c_get_tp into tp_rec;
515 WHILE c_get_tp%FOUND LOOP
516 /* insert into bfs_msg
517 values
518 ( ' time_point_id = ' ||
519 tp_rec.time_point_id );
520 commit; */
521 l_spec_vr_id := get_spec_vr_id (tp_rec.spec_id,
522 ss_rec.created_by);
523 p_sampling_event.sample_taken_cnt :=
524 tp_rec.samples_per_time_point;
525 p_sampling_event.sample_req_cnt :=
526 tp_rec.samples_per_time_point;
527 --p_sampling_event.sample_active_cnt := tp_rec.samples_per_time_point;
528 p_sampling_event.sample_active_cnt := 0;
529 p_sampling_event.time_point_id :=
530 tp_rec.time_point_id;
531 p_sample.time_point_id := tp_rec.time_point_id;
532
533 IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
534 p_sampling_event,
535 x_sampling_event ) THEN
536 raise fnd_api.g_exc_error;
537 END IF;
538 /* insert into bfs_msg
539 values
540 ( 'sampling_event_id = '||
541 x_sampling_event.sampling_event_id );
542 commit; */
543 /* GMD_SS_VARIANTS is updated with the sampling_event_id for the
544 -- Variant's retained samples. The variant's retained samples are
545 -- associated with the same sampling event. */
546 UPDATE gmd_ss_time_points
547 set sampling_event_id =
548 x_sampling_event.sampling_event_id
549 WHERE time_point_id = tp_rec.time_point_id;
550 p_sample.sampling_event_id :=
551 x_sampling_event.sampling_event_id;
552 p_event_spec_disp.sampling_event_id :=
553 x_sampling_event.sampling_event_id;
554 p_event_spec_disp.spec_id := tp_rec.spec_id;
555 p_event_spec_disp.spec_vr_id := l_spec_vr_id;
556
557 IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
558 p_event_spec_disp,
559 x_event_spec_disp ) THEN
560 raise fnd_api.g_exc_error;
561 END IF;
562 /* insert into bfs_msg
563 values
564 ( 'event_spec_disp_id = '||
565 x_event_spec_disp.event_spec_disp_id );
566 commit; */
567 p_sample_spec_disp.event_spec_disp_id :=
568 x_event_spec_disp.event_spec_disp_id;
569 --Bug# 3729234. Assigning '0RT' to p_sample_spec_disp.disposition instead of '1P'
570 --p_sample_spec_disp.disposition := '1P';
571 p_sample_spec_disp.disposition := '0RT';
572 FOR tp_cnt in 1..tp_rec.samples_per_time_point LOOP
573 /* insert into bfs_msg
574 values
575 ('time_point_id = '|| tp_rec.time_point_id ||
576 ' tp_cnt= '||tp_cnt);
577 commit; */
578 /* Check if spec vr exists for TP for ss_id, variant_id, time_point_id,
579 -- if not, create the spec vr */
580 p_sample.sample_no :=
581 GMD_QUALITY_PARAMETERS_GRP.get_next_sample_no(p_organization_id => ss_rec.organization_id); --INVCONV
582 /* insert into bfs_msg
583 values
584 (' sample_no = ' || p_sample.sample_no );
585 commit; */
586 l_msg := NULL;
587 fnd_message.set_name('GMD','GMD_SS_TP_SAMPLE_DESC');
588 fnd_message.set_token('VARIANT', var_rec.variant_no);
589 fnd_message.set_token('TIMEPOINT', tp_cnt);
590 l_msg := fnd_message.get;
591 p_sample.sample_desc := l_msg;
592 p_sample.sample_instance := tp_cnt;
593 IF not GMD_SAMPLES_PVT.insert_row (
594 p_sample,
595 x_sample ) THEN
596 raise fnd_api.g_exc_error;
597 END IF;
598 /* insert into bfs_msg
599 values
600 (' sample_id = ' || x_sample.sample_id );
601 commit; */
602 p_sample_spec_disp.sample_id := x_sample.sample_id;
603 IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
604 p_sample_spec_disp ) THEN
605 raise fnd_api.g_exc_error;
606
607 END IF;
608 /* insert into bfs_msg
609 values
610 (' sample_id = ' || x_sample.sample_id );
611 commit; */
612 END LOOP;
613 FETCH c_get_tp into tp_rec;
614 /* CLOSE c_get_tp_spec_vr; */
615 END LOOP;
616 CLOSE c_get_tp;
617 --------------------------------------------------------------------------------------------------------------------
618 --Bug#3583257. moved the piece of code (to insert retained samples) from a different place to here
619 /* create variant Reserved samples */
620 IF var_rec.retained_samples > 0 THEN
621 p_sampling_event.sample_taken_cnt :=
622 var_rec.retained_samples;
623 p_sampling_event.sample_req_cnt :=
624 var_rec.retained_samples;
625 --p_sampling_event.sample_active_cnt := var_rec.retained_samples;
626 p_sampling_event.sample_active_cnt := 0;
627
628 --Bug#3583257
629 p_sampling_event.time_point_id := NULL;
630
631 IF not GMD_SAMPLING_EVENTS_PVT.insert_row (
632 p_sampling_event,
633 x_sampling_event ) THEN
634 raise fnd_api.g_exc_error;
635 END IF;
636 /* insert into bfs_msg
637 values
638 ( 'Var: sampling_event_id = '||
639 x_sampling_event.sampling_event_id );
640 commit; */
641 /* GMD_SS_VARIANTS is updated with the sampling_event_id for the
642 -- Variant's retained samples. The variant's retained samples are
643 -- associated with the same sampling event. */
644 UPDATE gmd_ss_variants
645 set sampling_event_id =
646 x_sampling_event.sampling_event_id
647 WHERE variant_id = var_rec.variant_id;
648 p_event_spec_disp.sampling_event_id :=
649 x_sampling_event.sampling_event_id;
650 p_sample.sampling_event_id :=
651 x_sampling_event.sampling_event_id;
652 IF not GMD_EVENT_SPEC_DISP_PVT.insert_row (
653 p_event_spec_disp,
654 x_event_spec_disp ) THEN
655 raise fnd_api.g_exc_error;
656 END IF;
657 /* insert into bfs_msg
658 values
659 ( 'Var: event_spec_disp_id = '||
660 x_event_spec_disp.event_spec_disp_id );
661 commit; */
662 p_sample_spec_disp.event_spec_disp_id :=
663 x_event_spec_disp.event_spec_disp_id;
664 --Bug# 3729234. Assigning '0RT' to p_sample_spec_disp.disposition instead of '1P'
665 --p_sample_spec_disp.disposition := '1P';
666 p_sample_spec_disp.disposition := '0RT';
667 FOR smp_cnt in 1..var_rec.retained_samples LOOP
668 p_sample.sample_no :=
669 GMD_QUALITY_PARAMETERS_GRP.get_next_sample_no(p_organization_id => ss_rec.organization_id); --INVCONV
670
671 /* insert into bfs_msg
672 values
673 (' sample_no = ' || p_sample.sample_no );
674 commit; */
675
676 l_msg := NULL;
677 fnd_message.set_name('GMD','GMD_SS_VARIANT_SAMPLE_DESC');
678 fnd_message.set_token('VARIANT', var_rec.variant_no);
679 fnd_message.set_token('INST', smp_cnt);
680 l_msg := fnd_message.get;
681
682 p_sample.sample_desc := l_msg;
683 p_sample.sample_instance := smp_cnt;
684
685 p_sample.time_point_id := NULL; --Bug#3583257
686
687 IF not GMD_SAMPLES_PVT.insert_row (
688 p_sample,
689 x_sample ) THEN
690 raise fnd_api.g_exc_error;
691 END IF;
692 p_sample_spec_disp.sample_id := x_sample.sample_id;
693 /* insert into bfs_msg
694 values
695 (' sample_id = ' || x_sample.sample_id );
696 commit; */
697 IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
698 p_sample_spec_disp ) THEN
699 raise fnd_api.g_exc_error;
700 END IF;
701 /* insert into bfs_msg
702 values
703 (' Sample Spec: sample_id = ' || x_sample.sample_id );
704 commit; */
705 END LOOP; /* Loop to create sample for number of s-per- */
706 END IF; /* Loop var_rec.retained_samples > 0 */
707
708
709 ----------------------------------------------------------------------------------------------------------------------
710
711 FETCH c_get_var into var_rec;
712 END LOOP;
713 CLOSE c_get_var;
714 FETCH c_get_ms into mat_rec;
715 END LOOP;
716 CLOSE c_get_ms;
717 CLOSE c_get_ss;
718 COMMIT;
719 END IF;
720 EXCEPTION
721 WHEN FND_API.G_EXC_ERROR THEN
722 x_return_status := 'E';
723 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE',
724 'GMD_SS_WFLOW_GRP.events_for_status_change',
725 'ERROR', SUBSTR(SQLERRM,1,100),
726 'POSITION',null);
727 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
728 x_return_status := 'U';
729 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE',
730 'GMD_SS_WFLOW_GRP.events_for_status_change',
731 'ERROR',
732 SUBSTR(SQLERRM,1,100),'POSITION',null);
733 WHEN OTHERS THEN
734 x_return_status := 'E';
735 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE',
736 'GMD_SS_WFLOW_GRP.events_for_status_change',
737 'ERROR',
738 SUBSTR(SQLERRM,1,100),'POSITION',null);
739 END events_for_status_change;
740 END GMD_SS_WFLOW_GRP;
741
742