[Home] [Help]
PACKAGE BODY: APPS.GMD_STABILITY_STUDIES_GRP
Source
1 PACKAGE BODY GMD_STABILITY_STUDIES_GRP AS
2 /* $Header: GMDGSSTB.pls 120.2 2005/09/02 01:55:35 svankada noship $ */
3
4 FUNCTION Stability_Study_Exist(p_stability_study_no IN VARCHAR2 )
5 RETURN BOOLEAN IS
6 CURSOR Cur_get_stability_study IS
7 SELECT '1'
8 FROM gmd_stability_studies_b
9 WHERE ss_no = p_stability_study_no;
10 l_temp VARCHAR2(1);
11 BEGIN
12 IF (p_stability_study_no IS NOT NULL) THEN
13 OPEN Cur_get_stability_study;
14 FETCH Cur_get_stability_study INTO l_temp;
15 IF (Cur_get_stability_study%FOUND) THEN
16 CLOSE Cur_get_stability_study;
17 RETURN TRUE;
18 ELSE
19 CLOSE Cur_get_stability_study;
20 RETURN FALSE;
21 END IF;
22 ELSE
23 RETURN FALSE;
24 END IF;
25 END Stability_Study_Exist;
26
27 FUNCTION calculate_end_date
28 ( p_storage_plan_id IN NUMBER,
29 p_start_date IN DATE )
30 RETURN DATE IS
31
32 CURSOR cur_get_max_period IS
33 SELECT MAX(tipp.simulated_date - tip.simulation_start_date) + p_start_date
34 FROM gmd_storage_plan_details spd,gmd_test_interval_plans_b tip, gmd_test_interval_plan_periods tipp
35 WHERE
36 spd.storage_plan_id = p_storage_plan_id
37 AND spd.test_interval_plan_id = tip.test_interval_plan_id
38 AND tip.test_interval_plan_id = tipp.test_interval_plan_id ;
39
40 l_end_date DATE ;
41
42 BEGIN
43 IF p_storage_plan_id IS NULL OR p_start_date IS NULL THEN
44 RETURN NULL;
45 END IF;
46
47 OPEN cur_get_max_period;
48 FETCH cur_get_max_period INTO l_end_date ;
49 CLOSE cur_get_max_period ;
50
51 RETURN (l_end_date);
52
53 END calculate_end_date ;
54
55
56 PROCEDURE calculate_sample_qty( --p_ss_id IN NUMBER, INVCONV
57 p_source_id IN NUMBER,
58 -- p_item_id IN NUMBER, INVCONV
59 p_sample_qty OUT NOCOPY NUMBER,
60 p_sample_uom OUT NOCOPY VARCHAR2,
61 x_return_status OUT NOCOPY VARCHAR2) IS
62
63 l_progress VARCHAR2(3);
64
65 CURSOR cr_all_variants IS
66 SELECT variant_id , variant_no,retained_samples ,sample_qty , sample_quantity_uom , storage_organization_id -- INVCONV
67 FROM gmd_ss_variants
68 WHERE material_source_id = p_source_id
69 AND delete_mark = 0 ;
70
71 l_variant_count NUMBER(5) := 0 ;
72 l_variant_no GMD_SS_VARIANTS.VARIANT_NO%TYPE;
73 l_tl_samples NUMBER(5) ;
74 l_tl_time_points_with_samples NUMBER(5);
75 l_tl_time_points NUMBER(5) ;
76
77 VARIANTS_MISSING EXCEPTION;
78 MISSING_RETAINED_SAMPLE EXCEPTION;
79 MISSING_SAMPLE_QTY_UOM EXCEPTION;
80 MISSING_TIME_POINTS EXCEPTION;
81 MISSING_SAMPLE_TIME_POINT EXCEPTION;
82 REQ_FIELDS_MISSING EXCEPTION;
83
84 l_material_src_sample_qty_tl NUMBER := 0 ;
85 l_variant_sample_qty_tl NUMBER := 0 ;
86 l_item_uom VARCHAR2(3); -- INVCONV
87 --l_lot_id NUMBER; INVCONV
88 l_lot_number VARCHAR2(80); -- INVCONV
89 l_sample_qty_item_uom NUMBER;
90 l_tl_variant_samples NUMBER(5);
91 l_inventory_item_id NUMBER; -- INVCONV
92 l_organization_id NUMBER; -- INVCONV
93
94 l_source_organization_id NUMBER;
95
96 BEGIN
97
98 l_progress := '010';
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 IF p_source_id IS NULL THEN
102 RAISE REQ_FIELDS_MISSING ;
103 END IF;
104
105 -- Retrieving inventory item id INVCONV
106 SELECT inventory_item_id, organization_id INTO l_inventory_item_id, l_organization_id -- INVCONV
107 FROM gmd_stability_studies_b
108 WHERE ss_id = (SELECT ss_id FROM gmd_ss_material_sources WHERE source_id = p_source_id);
109
110 SELECT source_organization_id INTO l_source_organization_id
111 FROM gmd_ss_material_sources
112 WHERE source_id = p_source_id;
113
114 SELECT primary_uom_code INTO l_item_uom -- INVCONV
115 FROM mtl_system_items_b
116 WHERE inventory_item_id = l_inventory_item_id
117 AND organization_id = l_source_organization_id;
118
119 SELECT NVL(lot_number,0) INTO l_lot_number -- INVCONV
120 FROM gmd_ss_material_sources
121 WHERE source_id = p_source_id ;
122
123 l_progress := '020';
124
125 FOR cr_all_variants_rec IN cr_all_variants
126 LOOP
127 l_variant_count := l_variant_count + 1;
128 l_variant_no := cr_all_variants_rec.variant_no ;
129
130 IF cr_all_variants_rec.retained_samples IS NULL THEN
131 RAISE MISSING_RETAINED_SAMPLE;
132 END IF;
133
134 IF cr_all_variants_rec.sample_qty IS NULL OR cr_all_variants_rec.sample_quantity_uom IS NULL THEN -- INVCONV
135 l_variant_no := cr_all_variants_rec.variant_no ;
136 RAISE MISSING_SAMPLE_QTY_UOM;
137 END IF;
138
139 SELECT SUM(samples_per_time_point) , SUM(DECODE(samples_per_time_point,NULL,0,1)) , SUM(1)
140 INTO l_tl_samples , l_tl_time_points_with_samples,l_tl_time_points
141 FROM gmd_ss_time_points
142 WHERE variant_id = cr_all_variants_rec.variant_id
143 AND delete_mark = 0 ;
144
145 IF l_tl_time_points IS NULL THEN
146 RAISE MISSING_TIME_POINTS;
147 END IF;
148
149 IF NVL(l_tl_time_points_with_samples,-1) <> l_tl_time_points THEN
150 RAISE MISSING_SAMPLE_TIME_POINT;
151 END IF;
152
153 l_tl_variant_samples := l_tl_samples + cr_all_variants_rec.retained_samples ;
154
155 l_progress := '030';
156
157 -- convert the sample qty for the variant to the primary uom of the item.
158
159 BEGIN
160
161
162
163 /*GMICUOM.icuomcv(pitem_id => p_item_id, -- PAL
164 plot_id => l_lot_id,
165 pcur_qty => l_tl_variant_samples * cr_all_variants_rec.sample_qty,
166 pcur_uom => cr_all_variants_rec.sample_qty_uom,
167 pnew_uom => l_item_uom,
168 onew_qty => l_sample_qty_item_uom); */
169
170
171 l_sample_qty_item_uom := INV_CONVERT.INV_UM_CONVERT(l_inventory_item_id -- INVCONV
172 , l_lot_number
173 ,l_source_organization_id
174 ,5 --NULL
175 ,l_tl_variant_samples * cr_all_variants_rec.sample_qty
176 ,cr_all_variants_rec.sample_quantity_uom
177 ,l_item_uom
178 ,NULL -- From uom name
179 ,NULL -- To uom name
180 );
181
182 EXCEPTION WHEN OTHERS
183 THEN
184 FND_MSG_PUB.ADD;
185 RAISE FND_API.G_EXC_ERROR;
186 END ;
187
188 l_material_src_sample_qty_tl := l_material_src_sample_qty_tl + l_sample_qty_item_uom ;
189
190 END LOOP;
191
192 l_progress := '040';
193
194 IF l_variant_count = 0 THEN
195 RAISE VARIANTS_MISSING ;
196 END IF;
197
198 p_sample_qty := l_material_src_sample_qty_tl ;
199 p_sample_uom := l_item_uom ;
200
201
202 EXCEPTION
203 WHEN REQ_FIELDS_MISSING THEN
204 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_STABILITY_STUDIES_GRP.CALCULATE_SAMPLE_QTY');
205 x_return_status := FND_API.G_RET_STS_ERROR ;
206
207 WHEN MISSING_TIME_POINTS THEN
208 IF cr_all_variants%ISOPEN THEN
209 CLOSE cr_all_variants;
210 END IF ;
211 gmd_api_pub.log_message('GMD_SS_TIME_POINTS_NOT_DEF','VARIANT_NO',to_char(l_variant_no));
212 x_return_status := FND_API.G_RET_STS_ERROR ;
213
214 WHEN MISSING_SAMPLE_TIME_POINT THEN
215 IF cr_all_variants%ISOPEN THEN
216 CLOSE cr_all_variants;
217 END IF ;
218 gmd_api_pub.log_message('GMD_SS_TIME_POINT_NO_SMPL','VARIANT_NO',to_char(l_variant_no));
219 x_return_status := FND_API.G_RET_STS_ERROR ;
220
221 WHEN VARIANTS_MISSING THEN
222 IF cr_all_variants%ISOPEN THEN
223 CLOSE cr_all_variants;
224 END IF ;
225 gmd_api_pub.log_message('GMD_SS_NO_RETAIN_SMPL','VARIANT_NO',to_char(l_variant_no));
226 x_return_status := FND_API.G_RET_STS_ERROR ;
227
228 WHEN MISSING_SAMPLE_QTY_UOM THEN
229 IF cr_all_variants%ISOPEN THEN
230 CLOSE cr_all_variants;
231 END IF ;
232 gmd_api_pub.log_message('GMD_SS_NO_SMPL_QTY_UOM','VARIANT_NO',to_char(l_variant_no));
233 x_return_status := FND_API.G_RET_STS_ERROR ;
234
235 WHEN MISSING_RETAINED_SAMPLE THEN
236 IF cr_all_variants%ISOPEN THEN
237 CLOSE cr_all_variants;
238 END IF ;
239 gmd_api_pub.log_message('GMD_SS_NO_VARIANT_MTRL_SRC');
240 x_return_status := FND_API.G_RET_STS_ERROR ;
241
242 WHEN FND_API.G_EXC_ERROR THEN
243 IF cr_all_variants%ISOPEN THEN
244 CLOSE cr_all_variants;
245 END IF ;
246 x_return_status := FND_API.G_RET_STS_ERROR ;
247 WHEN OTHERS THEN
248 IF cr_all_variants%ISOPEN THEN
249 CLOSE cr_all_variants;
250 END IF ;
251 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_STABILITY_STUDIES_GRP.CALCULATE_SAMPLE_QTY','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
253
254 END calculate_sample_qty ;
255
256
257 PROCEDURE sample_qty_available(p_ss_id IN NUMBER,
258 -- p_item_id IN NUMBER, -- INVCONV
259 x_return_status OUT NOCOPY VARCHAR2)
260 IS
261
262 CURSOR cr_material_sources_lot IS
263 SELECT source_id,source_organization_id, lot_number,sample_qty FROM gmd_ss_material_sources -- INVCONV
264 WHERE ss_id = p_ss_id and lot_number IS NOT NULL ;
265
266 cr_material_sources_lot_rec cr_material_sources_lot%ROWTYPE ;
267
268 l_onhand_qty NUMBER ;
269 l_lot_label VARCHAR2(100);
270 l_lot_number VARCHAR2(80); -- INVCONV
271 l_inventory_item_id NUMBER; -- INVCONV
272
273
274
275 BEGIN
276
277 x_return_status := FND_API.G_RET_STS_SUCCESS;
278
279 SELECT inventory_item_id INTO l_inventory_item_id -- INVCONV
280 FROM gmd_stability_studies_b
281 WHERE ss_id = p_ss_id;
282
283 -- check is sufficient inventory is there for the material source containing the lot.
284
285 FOR cr_material_sources_lot_rec in cr_material_sources_lot
286 LOOP
287
288 /*SELECT nvl(sum(loct_onhand),0) INTO l_onhand_qty -- INVCONV
289 FROM ic_loct_inv
290 WHERE item_id = p_item_id
291 AND lot_id = cr_material_sources_lot_rec.lot_id ; */
292
293 SELECT nvl(sum(transaction_quantity),0) INTO l_onhand_qty --INVOCNV
294 FROM mtl_onhand_quantities
295 WHERE inventory_item_id = l_inventory_item_id
296 AND organization_id = cr_material_sources_lot_rec.source_organization_id
297 AND lot_number = cr_material_sources_lot_rec.lot_number;
298
299 IF cr_material_sources_lot_rec.sample_qty > l_onhand_qty THEN
300 l_lot_label := l_lot_number; -- INVCONV
301 gmd_api_pub.log_message('GMD_SS_SMPL_QTY_LESS','LOT',l_lot_label);
302 x_return_status := FND_API.G_RET_STS_ERROR ;
303 RETURN ;
304 END IF;
305 END LOOP;
306
307 END sample_qty_available ;
308
309 PROCEDURE ss_approval_checklist_ok(p_ss_id IN NUMBER ,
310 x_return_status OUT NOCOPY VARCHAR2)
311 IS
312
313 l_progress VARCHAR2(3);
314
315 REQ_FIELDS_MISSING EXCEPTION;
316 l_material_sources_cnt NUMBER(5);
317 l_actual_mtrl_src_cnt NUMBER(5);
318 l_inventory_item_id NUMBER ; -- INVCONV
319 l_organization_id NUMBER; -- INVCONV
320 l_sample_numbering NUMBER(3);
321 l_sample_qty_out NUMBER;
322 l_sample_uom_out GMD_SS_MATERIAL_SOURCES.SAMPLE_QUANTITY_UOM%TYPE ;
323 l_source_label VARCHAR2(200);
324 l_temp NUMBER;
325 l_quality_parameters GMD_QUALITY_CONFIG%ROWTYPE; -- INVCONV
326 l_return_status VARCHAR2(1); -- INVCONV
327 l_orgn_found BOOLEAN; -- INVCONV
328 l_source_organization_code VARCHAR2(3);
329
330 CURSOR cr_material_sources IS
331 SELECT source_id FROM gmd_ss_material_sources
332 WHERE ss_id = p_ss_id ;
333
334 CURSOR cr_material_src_variant IS
335 SELECT source_organization_id,lot_number,recipe_no
336 FROM gmd_ss_material_sources -- INVCONV
337 WHERE ss_id = p_ss_id
338 and not exists
339 ( select 'x' from gmd_ss_variants
340 where material_source_id = source_id ) ;
341
342 CURSOR cr_variants_storage_spec IS
343 SELECT a.storage_spec_id,b.spec_name,b.spec_vers
344 FROM gmd_ss_variants a,gmd_specifications b
345 WHERE a.ss_id = p_ss_id
346 and a.storage_spec_id = b.spec_id
347 and b.spec_status not in (400,700) ;
348
349 --Added for INVCONV
350 CURSOR cr_material_src_organization (p_organization_id NUMBER) IS
351 SELECT organization_code
352 FROM mtl_parameters
353 WHERE organization_id = p_organization_id;
354
355 cr_material_sources_rec cr_material_sources%ROWTYPE ;
356 cr_material_src_variant_rec cr_material_src_variant%ROWTYPE ;
357 cr_variants_storage_spec_rec cr_variants_storage_spec%ROWTYPE ;
358
359 BEGIN
360
361 l_progress := '010';
362 x_return_status := FND_API.G_RET_STS_SUCCESS;
363
364 FND_MSG_PUB.Initialize;
365
366 IF p_ss_id IS NULL THEN
367 RAISE REQ_FIELDS_MISSING ;
368 END IF;
369
370 SELECT organization_id,material_sources_cnt, inventory_item_id
371 INTO l_organization_id ,l_material_sources_cnt , l_inventory_item_id -- INVCONV
372 FROM gmd_stability_studies_b
373 WHERE ss_id = p_ss_id ;
374
375 -- Sample no. generation should be automatic and not manual.
376 l_progress := '020';
377 gmd_quality_parameters_grp.get_quality_parameters(p_organization_id => l_organization_id,
378 x_quality_parameters => l_quality_parameters,
379 x_return_status => l_return_status,
380 x_orgn_found => l_orgn_found);
381
382 l_sample_numbering := l_quality_parameters.sample_assignment_type; --INVCONV
383
384 if l_sample_numbering < 0 then
385 x_return_status := FND_API.G_RET_STS_ERROR ;
386 RETURN ;
387 elsif l_sample_numbering = 1 THEN -- manual numbering
388 gmd_api_pub.log_message('GMD_SS_SMPL_MANUAL_NUM','ORGN',l_organization_id);
389 x_return_status := FND_API.G_RET_STS_ERROR ;
390 RETURN ;
391 end if;
392
393 l_progress := '030';
394 -- The required number of source materials has been specified.
395
396 SELECT count(1) into l_actual_mtrl_src_cnt
397 FROM gmd_ss_material_sources
398 WHERE ss_id = p_ss_id ;
399
400 IF l_actual_mtrl_src_cnt < l_material_sources_cnt THEN
401 gmd_api_pub.log_message('GMD_SS_MTRL_SRC_CNT_LESS','MTRL_CNT',to_char(l_material_sources_cnt));
402 x_return_status := FND_API.G_RET_STS_ERROR ;
403 RETURN;
404 END IF;
405
406 -- recalculate the sample qty before approving so that it is latest.
407 -- must be able to calculate sample qty for each material source
408
409 l_progress := '040';
410
411 FOR cr_material_sources_rec in cr_material_sources
412 LOOP
413 gmd_stability_studies_grp.calculate_sample_qty(
414 p_source_id => cr_material_sources_rec.source_id,
415 p_sample_qty => l_sample_qty_out,
416 p_sample_uom => l_sample_uom_out,
417 x_return_status => l_return_status) ;
418
419 SELECT source_id INTO l_temp
420 FROM gmd_ss_material_sources
421 WHERE source_id = cr_material_sources_rec.source_id
422 FOR UPDATE OF sample_qty NOWAIT ;
423
424 IF l_return_status = 'S' then
425 UPDATE gmd_ss_material_sources
426 SET sample_qty = l_sample_qty_out,
427 sample_quantity_uom = l_sample_uom_out, -- INVCONV
428 last_updated_by = fnd_global.user_id,
429 last_update_date = sysdate,
430 last_update_login = fnd_global.login_id
431 WHERE source_id = cr_material_sources_rec.source_id ;
432 ELSE
433 x_return_status := l_return_status ;
434 RETURN;
435 END IF;
436 END LOOP ;
437
438 l_progress := '050';
439 -- check is sufficient inventory is there for the material source containing the lot.
440
441 sample_qty_available(p_ss_id => p_ss_id,
442 x_return_status => l_return_status ) ;
443
444 IF l_return_status <> 'S' THEN
445 x_return_status := l_return_status ;
446 RETURN;
447 END IF;
448
449 l_progress := '060';
450
451 -- there should be atleast one variant for every material source defined.
452
453 FOR cr_material_src_variant_rec in cr_material_src_variant
454 LOOP
455 OPEN cr_material_src_organization(cr_material_src_variant_rec.source_organization_id);
456 FETCH cr_material_src_organization INTO l_source_organization_code;
457 CLOSE cr_material_src_organization;
458
459 SELECT l_source_organization_code ||
460 decode(l_source_organization_code,NULL,NULL,decode(cr_material_src_variant_rec.lot_number || cr_material_src_variant_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
461 cr_material_src_variant_rec.lot_number || decode(cr_material_src_variant_rec.lot_number,NULL,NULL,decode(cr_material_src_variant_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
462 cr_material_src_variant_rec.recipe_no INTO l_source_label
463 FROM DUAL ;
464
465 gmd_api_pub.log_message('GMD_SS_MTRL_SRC_VAR_MISSING','SOURCE',l_source_label);
466 x_return_status := FND_API.G_RET_STS_ERROR ;
467 RETURN;
468 END LOOP;
469
470 l_progress := '070';
471 -- Monitoring specs used for variants should be approved
472
473 FOR cr_variants_storage_spec_rec in cr_variants_storage_spec
474 LOOP
475 gmd_api_pub.log_message('GMD_SS_VAR_STORAGE_SPEC','SPEC',cr_variants_storage_spec_rec.spec_name || '-' || cr_variants_storage_spec_rec.spec_vers);
476 x_return_status := FND_API.G_RET_STS_ERROR ;
477 RETURN;
478 END LOOP ;
479
480 EXCEPTION
481 WHEN REQ_FIELDS_MISSING THEN
482 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_STABILITY_STUDIES_GRP.SS_APPROVAL_CHECKLIST_OK');
483 x_return_status := FND_API.G_RET_STS_ERROR ;
484
485 WHEN OTHERS THEN
486 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_STABILITY_STUDIES_GRP.SS_APPROVAL_CHECKLIST_OK','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
488
489 END ss_approval_checklist_ok ;
490
491 PROCEDURE ss_launch_checklist_ok(p_ss_id IN NUMBER ,
492 x_return_status OUT NOCOPY VARCHAR2)
493 IS
494
495 l_progress VARCHAR2(3);
496 l_source_label VARCHAR2(200);
497 l_inventory_item_id NUMBER ; -- INVCONV
498 l_return_status VARCHAR2(1);
499 l_source_organization_code VARCHAR2(3); --INVCONV
500
501 REQ_FIELDS_MISSING EXCEPTION;
502
503 CURSOR cr_material_src_smpl_event IS
504 SELECT source_organization_id,lot_number,recipe_no,sampling_event_id
505 FROM gmd_ss_material_sources -- INVCONV
506 WHERE ss_id = p_ss_id
507 AND (sampling_event_id IS NULL OR lot_number IS NULL) ; -- INVCONV
508
509
510 --Bug#3583299. Changed a condition in the 'where' clause from 'a.ss_id = b.ss_id' to 'a.source_id = b.material_source_id'
511 --in 'cr_material_src_yield_date' cursor.
512 CURSOR cr_material_src_yield_date IS
513 SELECT source_organization_id,lot_number,recipe_no,yield_date,variant_no
514 FROM gmd_ss_material_sources a , gmd_ss_variants b -- INVCONV
515 WHERE a.ss_id = p_ss_id
516 AND a.source_id = b.material_source_id
517 AND ((a.yield_date IS NULL) OR (a.yield_date > b.scheduled_start_date)) ;
518
519 CURSOR cr_variant_storage_date IS
520 SELECT source_organization_id,lot_number,recipe_no,variant_no
521 FROM gmd_ss_material_sources a , gmd_ss_variants b -- INVCONV
522 WHERE a.ss_id = p_ss_id
523 AND a.ss_id = b.ss_id
524 AND b.storage_date IS NULL ;
525
526 --Added for INVCONV
527 CURSOR cr_material_src_organization (p_organization_id NUMBER) IS
528 SELECT organization_code
529 FROM mtl_parameters
530 WHERE organization_id = p_organization_id;
531
532 cr_material_src_smpl_event_rec cr_material_src_smpl_event%ROWTYPE ;
533 cr_material_src_yield_date_rec cr_material_src_yield_date%ROWTYPE ;
534 cr_variant_storage_date_rec cr_variant_storage_date%ROWTYPE ;
535
536 BEGIN
537
538 l_progress := '010';
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 FND_MSG_PUB.Initialize;
542
543 IF p_ss_id IS NULL THEN
544 RAISE REQ_FIELDS_MISSING ;
545 END IF;
546
547 l_progress := '020';
548
549 SELECT inventory_item_id INTO l_inventory_item_id -- INVCONV
550 FROM gmd_stability_studies_b
551 WHERE ss_id = p_ss_id ;
552
553 -- each material source must have sampling event and lot associated with it.
554
555 l_progress := '030';
556
557 FOR cr_material_src_smpl_event_rec in cr_material_src_smpl_event
558 LOOP
559 --Added for INCONV
560 OPEN cr_material_src_organization(cr_material_src_smpl_event_rec.source_organization_id);
561 FETCH cr_material_src_organization INTO l_source_organization_code;
562 CLOSE cr_material_src_organization;
563
564 SELECT l_source_organization_code ||
565 decode(l_source_organization_code,NULL,NULL,decode(cr_material_src_smpl_event_rec.lot_number || cr_material_src_smpl_event_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
566 cr_material_src_smpl_event_rec.lot_number || decode(cr_material_src_smpl_event_rec.lot_number,NULL,NULL,decode(cr_material_src_smpl_event_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
567 cr_material_src_smpl_event_rec.recipe_no INTO l_source_label
568 FROM DUAL ;
569
570 IF cr_material_src_smpl_event_rec.lot_number IS NULL THEN
571 gmd_api_pub.log_message('GMD_SS_MTRL_SRC_LOT_MISSING','SOURCE',l_source_label);
572 ELSE
573 gmd_api_pub.log_message('GMD_SS_MTRL_SRC_EVENT_MISSING','SOURCE',l_source_label);
574 END IF;
575 x_return_status := FND_API.G_RET_STS_ERROR ;
576 RETURN;
577 END LOOP;
578
579 l_progress := '040';
580
581 -- yield date is required for each material source and it must be before the variant schedule start date.
582
583 FOR cr_material_src_yield_date_rec in cr_material_src_yield_date
584 LOOP
585 --Added for INCONV
586 OPEN cr_material_src_organization(cr_material_src_yield_date_rec.source_organization_id);
587 FETCH cr_material_src_organization INTO l_source_organization_code;
588 CLOSE cr_material_src_organization;
589
590 SELECT l_source_organization_code ||
591 decode(l_source_organization_code,NULL,NULL,decode(cr_material_src_yield_date_rec.lot_number || cr_material_src_yield_date_rec.recipe_no,NULL,NULL,'-')) || -- INVCONV
592 cr_material_src_yield_date_rec.lot_number || decode(cr_material_src_yield_date_rec.lot_number,NULL,NULL,decode(cr_material_src_yield_date_rec.recipe_no,NULL,NULL,'-')) ||
593 cr_material_src_yield_date_rec.recipe_no INTO l_source_label
594 FROM DUAL ;
595
596 IF cr_material_src_yield_date_rec.yield_date IS NULL THEN
597 gmd_api_pub.log_message('GMD_SS_MTRL_SRC_YIELD_MISSING','SOURCE',l_source_label);
598 ELSE
599 gmd_api_pub.log_message('GMD_SS_INVALID_VAR_SCH_DATE','SOURCE',l_source_label,'VARIANT',cr_material_src_yield_date_rec.variant_no);
600 END IF;
601 x_return_status := FND_API.G_RET_STS_ERROR ;
602 RETURN;
603 END LOOP;
604
605 l_progress := '050';
606
607 -- check is sufficient inventory is there for the material source containing the lot.
608
609 sample_qty_available(p_ss_id => p_ss_id,
610 x_return_status => l_return_status ) ;
611
612 IF l_return_status <> 'S' THEN
613 x_return_status := l_return_status ;
614 RETURN;
615 END IF;
616
617 -- Variants must have the storage date(they must be in the storage before stability study is lauched).
618 l_progress := '060';
619
620 FOR cr_variant_storage_date_rec in cr_variant_storage_date
621 LOOP
622 gmd_api_pub.log_message('GMD_SS_MISS_VAR_STORAGE_DATE','VARIANT',cr_variant_storage_date_rec.variant_no);
623 x_return_status := FND_API.G_RET_STS_ERROR ;
624 RETURN;
625 END LOOP;
626
627
628 EXCEPTION
629 WHEN REQ_FIELDS_MISSING THEN
630 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_STABILITY_STUDIES_GRP.SS_LAUNCH_CHECKLIST_OK');
631 x_return_status := FND_API.G_RET_STS_ERROR ;
632
633 WHEN OTHERS THEN
634 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_STABILITY_STUDIES_GRP.SS_LAUNCH_CHECKLIST_OK','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
636
637 END ss_launch_checklist_ok ;
638
639 PROCEDURE change_ss_status( p_ss_id IN NUMBER,
640 p_start_status IN NUMBER,
641 p_target_status IN NUMBER,
642 x_return_status OUT NOCOPY VARCHAR2,
643 x_message OUT NOCOPY VARCHAR2 ) IS
644
645 applicationId NUMBER :=552;
646 transactionType VARCHAR2(50) := 'GMDQM_STABILITY_CSTS';
647 nextApprover ame_util.approverRecord;
648 l_pending_status NUMBER(5);
649 l_rework_status NUMBER(5);
650 l_event_status VARCHAR2(20);
651 l_temp VARCHAR2(1);
652 l_return_status VARCHAR2(1);
653 l_event_subscription_enabled BOOLEAN := TRUE ;
654 l_progress VARCHAR2(3);
655 l_temp_index NUMBER ;
656
657 CURSOR cr_subscription_enabled IS
658 SELECT 'X' from wf_event_subscriptions
659 WHERE wf_process_name = 'STABILITY_STS_CHANGE'
660 and status = 'ENABLED' ;
661
662
663 BEGIN
664
665 x_return_status := FND_API.G_RET_STS_SUCCESS;
666 FND_MSG_PUB.Initialize;
667
668 l_progress := '010' ;
669
670 SELECT pending_status,rework_status
671 INTO l_pending_status,l_rework_status
672 FROM gmd_qc_status_next
673 WHERE current_status = p_start_status
674 AND target_status = p_target_status
675 AND entity_type = 'STABILITY' ;
676
677 IF l_pending_status IS NULL OR l_rework_status IS NULL THEN
678 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
679 , p_id => p_ss_id
680 , p_source_status => p_start_status
681 , p_target_status => p_target_status
682 , p_mode => 'A'
683 , p_entity_type => 'STABILITY'
684 , x_return_status => x_return_status
685 , x_message => x_message );
686
687 RETURN ;
688 ELSE
689 -- check if workflow event and subscription are ENABLED or NOT ?
690
691 l_progress := '020' ;
692
693 SELECT status into l_event_status from wf_events where name = 'oracle.apps.gmd.qm.ss.csts' ;
694
695 l_progress := '030' ;
696
697 IF l_event_status = 'DISABLED' THEN
698 -- WORKFLOW EVENT IS DISABLED.Status should be the target status which is already taken care by the forms.
699 l_event_subscription_enabled := FALSE ;
700 ELSE
701 OPEN cr_subscription_enabled ;
702 FETCH cr_subscription_enabled INTO l_temp ;
703 IF cr_subscription_enabled%NOTFOUND THEN
704 -- none of the subscriptions are enabled.
705 l_event_subscription_enabled := FALSE ;
706 END IF;
707 CLOSE cr_subscription_enabled ;
708 END IF;
709
710 -- workflow event/subscription is not ACTIVE.Workflow won't get kicked off. We need to create the samples from here.
711
712 l_progress := '040' ;
713
714 IF NOT (l_event_subscription_enabled) THEN
715
716 IF (p_target_status = 400) THEN
717 -- We got approved, so kick off API to create sampling events
718 GMD_SS_WFLOW_GRP.events_for_status_change(p_ss_id,l_return_status) ;
719 IF l_return_status <> 'S' then
720 x_return_status := l_return_status ;
721 x_message := FND_MESSAGE.GET;
722 END IF;
723 ELSIF (p_target_status = 700) THEN
724 -- We need to launch; Enable the Mother workflow for testing
725 GMD_API_PUB.RAISE ('oracle.apps.gmd.qm.ss.test',p_ss_id);
726 END IF;
727
728 RETURN ;
729 END IF;
730
731 l_progress := '050' ;
732
733 -- mchandak. bug#3005685
734 -- as long as the subscription and event is enabled, update the stability status
735 -- to request for approval and kick off the workflow.
736 -- if no approvals are setup , workflow will send notification to the owner of stability study.
737 -- removing the call to AME api's.
738
739 GMD_SPEC_GRP.change_status( p_table_name => 'GMD_STABILITY_STUDIES_B'
740 , p_id => p_ss_id
741 , p_source_status => p_start_status
742 , p_target_status => p_target_status
743 , p_mode => 'P'
744 , p_entity_type => 'STABILITY'
745 , x_return_status => x_return_status
746 , x_message => x_message );
747
748 IF x_return_status <> 'S' THEN
749 RETURN;
750 END IF;
751
752 -- raise the workflow event
753 GMD_SS_APPROVAL_WF_PKG.RAISE_SS_APPR_EVENT(
754 p_ss_id => p_ss_id,
755 p_start_status => p_start_status,
756 p_target_status =>p_target_status);
757
758 END IF ; -- end of IF l_pending_status IS NULL
759
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_STABILITY_STUDIES_GRP.CHANGE_SS_STATUS','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_progress);
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
765 --x_message := FND_MESSAGE.GET;
766 FND_MSG_PUB.GET(p_msg_index => -3,
767 p_data => X_message,
768 p_encoded => 'F',
769 p_msg_index_out => l_temp_index) ;
770
771 END change_ss_status ;
772
773 END GMD_STABILITY_STUDIES_GRP;