[Home] [Help]
PACKAGE BODY: APPS.GMD_QM_CONC_REPLACE_PKG
Source
1 PACKAGE BODY gmd_qm_conc_replace_pkg AS
2 /* $Header: GMDQRPLB.pls 120.3.12010000.1 2008/07/24 09:58:39 appldev ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GMD_QM_CONC_REPLACE_PKG';
5
6 --Forward declaration.
7 FUNCTION set_debug_flag RETURN VARCHAR2;
8
9 l_debug VARCHAR2 (1) := set_debug_flag;
10 FUNCTION set_debug_flag RETURN VARCHAR2 IS
11 l_debug VARCHAR2 (1) := 'N';
12 BEGIN
13 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
14 l_debug := 'Y';
15 END IF;
16
17 RETURN l_debug;
18 END set_debug_flag;
19
20 PROCEDURE default_log (p_log_msg IN VARCHAR2) IS
21 BEGIN
22 fnd_file.new_line (fnd_file.LOG, 1);
23 fnd_file.put (fnd_file.LOG, p_log_msg);
24 fnd_file.new_line (fnd_file.output, 1);
25 fnd_file.put (fnd_file.output, p_log_msg);
26 END default_log;
27
28 PROCEDURE DEBUG (p_log_msg IN VARCHAR2) IS
29 BEGIN
30 IF (l_debug = 'Y') THEN
31 gmd_debug.put_line (' ' || p_log_msg);
32 END IF;
33 END DEBUG;
34
35 PROCEDURE set_test_values (
36 p_gmd_test_rec IN gmd_qc_tests_b%ROWTYPE
37 , poptional_ind IN VARCHAR2 DEFAULT NULL
38 , pprint_spec_ind IN VARCHAR2 DEFAULT NULL
39 , pprint_result_ind IN VARCHAR2 DEFAULT NULL
40 , ptarget_value IN VARCHAR2 DEFAULT NULL
41 , ptarget_min IN VARCHAR2 DEFAULT NULL
42 , ptarget_max IN VARCHAR2 DEFAULT NULL
43 , preport_precision IN VARCHAR2 DEFAULT NULL
44 , pstore_precision IN VARCHAR2 DEFAULT NULL
45 , ptest_priority IN VARCHAR2 DEFAULT NULL
46 , x_return_status OUT NOCOPY VARCHAR2
47 , x_test_values_rec IN OUT NOCOPY gmd_qm_conc_replace_pkg.test_values
48 );
49
50 PROCEDURE default_spectest_from_test (
51 p_spec_id IN NUMBER
52 , p_test_name IN VARCHAR2
53 , poptional_ind IN VARCHAR2 DEFAULT NULL
54 , pprint_spec_ind IN VARCHAR2 DEFAULT NULL
55 , pprint_result_ind IN VARCHAR2 DEFAULT NULL
56 , ptarget_value IN VARCHAR2 DEFAULT NULL
57 , ptarget_min IN VARCHAR2 DEFAULT NULL
58 , ptarget_max IN VARCHAR2 DEFAULT NULL
59 , preport_precision IN VARCHAR2 DEFAULT NULL
60 , pstore_precision IN VARCHAR2 DEFAULT NULL
61 , ptest_priority IN VARCHAR2 DEFAULT NULL
62 , p_spec_test_rec IN OUT NOCOPY gmd_spec_tests_b%ROWTYPE
63 , x_return_status OUT NOCOPY VARCHAR2
64 );
65
66 PROCEDURE default_spectest_from_spectest (
67 p_from_spec_id IN NUMBER
68 , p_from_test_id IN NUMBER
69 , p_to_test_name IN VARCHAR2
70 , poptional_ind IN VARCHAR2 DEFAULT NULL
71 , pprint_spec_ind IN VARCHAR2 DEFAULT NULL
72 , pprint_result_ind IN VARCHAR2 DEFAULT NULL
73 , ptarget_value IN VARCHAR2 DEFAULT NULL
74 , ptarget_min IN VARCHAR2 DEFAULT NULL
75 , ptarget_max IN VARCHAR2 DEFAULT NULL
76 , preport_precision IN VARCHAR2 DEFAULT NULL
77 , pstore_precision IN VARCHAR2 DEFAULT NULL
78 , ptest_priority IN VARCHAR2 DEFAULT NULL
79 , p_spec_test_rec IN OUT NOCOPY gmd_spec_tests_b%ROWTYPE
80 , x_return_status OUT NOCOPY VARCHAR2
81 );
82
83 PROCEDURE add_spec_test_rec (
84 p_spec_id IN NUMBER
85 , p_test_name IN VARCHAR2
86 , p_spec_name IN VARCHAR2
87 , p_spec_test_rec IN OUT NOCOPY gmd_spec_tests_b%ROWTYPE
88 , x_return_status OUT NOCOPY VARCHAR2
89 );
90
91 PROCEDURE del_spec_test_rec (
92 p_spec_id IN NUMBER
93 , p_spec_name IN VARCHAR2
94 , p_test_name IN VARCHAR2
95 , x_return_status OUT NOCOPY VARCHAR2
96 );
97
98 PROCEDURE init_test_values_rec (
99 p_spec_tests_rec IN gmd_spec_tests_b%ROWTYPE
100 , x_test_values_rec IN OUT NOCOPY gmd_qm_conc_replace_pkg.test_values
101 );
102
103 PROCEDURE insert_spec_test_rec (
104 p_spec_test_rec IN gmd_spec_tests_b%ROWTYPE
105 , x_return_status OUT NOCOPY VARCHAR2
106 );
107
108 PROCEDURE insert_new_spec_rec (
109 p_spec_rec IN gmd_specifications%ROWTYPE
110 , x_new_spec_id OUT NOCOPY NUMBER
111 , x_return_status OUT NOCOPY VARCHAR2
112 );
113
114 PROCEDURE create_new_specification (
115 p_old_spec_id IN NUMBER
116 , p_action_code IN VARCHAR2
117 , x_new_spec_id OUT NOCOPY NUMBER
118 , x_new_spec_vers OUT NOCOPY NUMBER
119 , x_return_status OUT NOCOPY VARCHAR2
120 );
121
122 PROCEDURE process_validity_for_spec (
123 p_object_type IN VARCHAR2
124 , p_old_spec_id IN NUMBER DEFAULT NULL
125 , p_new_spec_id IN NUMBER DEFAULT NULL
126 , p_spec_vr_id IN NUMBER DEFAULT NULL
127 , p_end_date IN DATE DEFAULT NULL
128 , p_start_date IN DATE DEFAULT NULL
129 , p_new_status IN NUMBER DEFAULT NULL
130 , x_return_status OUT NOCOPY VARCHAR2
131 );
132
133 PROCEDURE copy_validity_rule (
134 p_from_vr_id IN NUMBER
135 , p_to_spec_id IN NUMBER
136 , p_spec_status IN NUMBER
137 , p_spec_type IN VARCHAR2
138 , p_create_mode IN VARCHAR2
139 , x_return_status OUT NOCOPY VARCHAR2
140 );
141
142 PROCEDURE end_old_validity_rule (
143 p_vr_id IN NUMBER
144 , p_spec_type IN VARCHAR2
145 , x_return_status OUT NOCOPY VARCHAR2
146 );
147
148 PROCEDURE obsolete_old_validity_rule (
149 p_vr_id IN NUMBER
150 , p_spec_type IN VARCHAR2
151 , x_return_status OUT NOCOPY VARCHAR2
152 );
153
154 PROCEDURE update_validity_rule (
155 p_vr_id IN NUMBER
156 , p_spec_type IN VARCHAR2
157 , p_end_date IN DATE
158 , p_start_date IN DATE
159 , p_new_status IN NUMBER
160 , x_return_status OUT NOCOPY VARCHAR2
161 );
162
163 FUNCTION is_test_in_expression (
164 p_expression IN VARCHAR2
165 , p_test_name IN VARCHAR2
166 , x_return_status OUT NOCOPY VARCHAR2
167 ) RETURN BOOLEAN;
168
169 /*###############################################################
170 # NAME
171 # Populate_search_table
172 # SYNOPSIS
173 # Populate_search_table
174 # DESCRIPTION
175 # Performs populates a PL/SQL table with the search query.
176 ###############################################################*/
177 PROCEDURE populate_search_table (x_search_tbl OUT NOCOPY search_result_tbl) IS
178 l_api_name VARCHAR2 (100) := 'POPULATE_SEARCH_TABLE';
179 l_dsql_text VARCHAR2 (2000);
180 l_cursor_id INT;
181 l_num_of_rows NUMBER;
182 l_value NUMBER;
183 l_row_cnt NUMBER := 0;
184 l_error VARCHAR2 (2000);
185 l_object_id NUMBER;
186 l_object_name VARCHAR2 (240);
187 l_object_vers NUMBER;
188 l_object_desc VARCHAR2 (240);
189 l_object_status_desc VARCHAR2 (240);
190 l_object_select_ind NUMBER;
191 l_object_status_code VARCHAR2 (240);
192 l_debug_text VARCHAR2 (2000);
193 BEGIN
194 -- Delete rows from previous searches
195 DELETE FROM gmd_msnr_results
196 WHERE concurrent_id IS NULL;
197
198 l_cursor_id := DBMS_SQL.open_cursor;
199 fnd_dsql.set_cursor (l_cursor_id);
200 l_dsql_text := fnd_dsql.get_text (FALSE);
201 l_debug_text := fnd_dsql.get_text (TRUE);
202 --raghav_debug ('l_dsql_text ' || l_dsql_text);
203 --raghav_debug ('l_debug_text ' || l_debug_text);
204
205 DBMS_SQL.parse (l_cursor_id, l_dsql_text, DBMS_SQL.native);
206 fnd_dsql.do_binds;
207
208 DBMS_SQL.define_column (l_cursor_id, 1, l_object_id);
209 DBMS_SQL.define_column (l_cursor_id, 2, l_object_name, 240);
210 DBMS_SQL.define_column (l_cursor_id, 3, l_object_vers);
211 DBMS_SQL.define_column (l_cursor_id, 4, l_object_desc, 240);
212 DBMS_SQL.define_column (l_cursor_id, 5, l_object_status_desc, 240);
213 DBMS_SQL.define_column (l_cursor_id, 6, l_object_select_ind);
214 DBMS_SQL.define_column (l_cursor_id, 7, l_object_status_code, 240);
215 l_num_of_rows := DBMS_SQL.EXECUTE (l_cursor_id);
216
217 LOOP
218 IF DBMS_SQL.fetch_rows (l_cursor_id) > 0 THEN
219 l_row_cnt := l_row_cnt + 1;
220 DBMS_SQL.column_value (l_cursor_id, 1, l_object_id);
221 DBMS_SQL.column_value (l_cursor_id, 2, l_object_name);
222 DBMS_SQL.column_value (l_cursor_id, 3, l_object_vers);
223 DBMS_SQL.column_value (l_cursor_id, 4, l_object_desc);
224 DBMS_SQL.column_value (l_cursor_id, 5, l_object_status_desc);
225 DBMS_SQL.column_value (l_cursor_id, 6, l_object_select_ind);
226 DBMS_SQL.column_value (l_cursor_id, 7, l_object_status_code);
227
228 IF (l_object_status_code IN ('200', '500', '800', '1000')) THEN
229 l_object_select_ind := 0;
230 END IF;
231
232 -- Populate the pl/sql table
233 -- This should go away soon !!!!!!
234 x_search_tbl (l_row_cnt).object_id := l_object_id;
235 x_search_tbl (l_row_cnt).object_name := l_object_name;
236 x_search_tbl (l_row_cnt).object_vers := l_object_vers;
237 x_search_tbl (l_row_cnt).object_desc := l_object_desc;
238 x_search_tbl (l_row_cnt).object_status_desc := l_object_status_desc;
239 x_search_tbl (l_row_cnt).object_select_ind := l_object_select_ind;
240 x_search_tbl (l_row_cnt).object_status_code := l_object_status_code;
241
242 -- Save the set of details in work table
243 INSERT INTO gmd_msnr_results
244 (concurrent_id
245 , object_id
246 , object_name
247 , object_vers
248 , object_desc
249 , object_status_code
250 , object_status_desc
251 , object_select_ind
252 )
253 VALUES (NULL
254 , l_object_id
255 , l_object_name
256 , l_object_vers
257 , l_object_desc
258 , l_object_status_code
259 , l_object_status_desc
260 , l_object_select_ind
261 );
262 ELSE
263 EXIT;
264 END IF;
265 END LOOP;
266
267 DBMS_SQL.close_cursor (l_cursor_id);
268 -- Commit all data populated
269 --Commit; -- Bug 4444060 Commented the commit
270 EXCEPTION
271 WHEN OTHERS THEN
272 IF (DBMS_SQL.is_open (l_cursor_id)) THEN
273 DBMS_SQL.close_cursor (l_cursor_id);
274 END IF;
275
276 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
277 END populate_search_table;
278
279 /* ************************************************************************ */
280 /* API name : Mass_Replace_Operation */
281 /* Type : Private */
282 /* Function : */
283 /* Pre-reqs : None. */
284 /* Parameters : */
285 /* IN : prequest_id IN NUMBER (Required) */
286 /* Notes : Performs replace of one or more instance of entities like */
287 /* formula, routing, recipe, operation, Validity Rules. */
288 /* HISTORY */
289 /* RLNAGARA 04-May-2007 Bug6017214 Modified the code such that */
290 /* only status change is allowed for Obsoleted/Archived, on Hold or */
291 /* Request for Approval specifications. */
292 /* SMALLURU 29-Nov-2007 Bug5973270 Modified the code to include spts in */
293 /* l_select variable and also modified the IF condition that verifies the */
294 /* range of test values to consider NULL target values. */
295 /* ************************************************************************ */
296 PROCEDURE mass_replace_oper_spec_val (
297 err_buf OUT NOCOPY VARCHAR2
298 , ret_code OUT NOCOPY VARCHAR2
299 , pconcurrent_id IN VARCHAR2 DEFAULT NULL
300 , pobject_type IN VARCHAR2
301 , preplace_type IN VARCHAR2
302 , pold_name IN VARCHAR2
303 , pnew_name IN VARCHAR2
304 , poptional_ind IN VARCHAR2 DEFAULT NULL
305 , pprint_spec_ind IN VARCHAR2 DEFAULT NULL
306 , pprint_result_ind IN VARCHAR2 DEFAULT NULL
307 , ptarget_value IN VARCHAR2 DEFAULT NULL
308 , ptarget_min IN VARCHAR2 DEFAULT NULL
309 , ptarget_max IN VARCHAR2 DEFAULT NULL
310 , preport_precision IN VARCHAR2 DEFAULT NULL
311 , pstore_precision IN VARCHAR2 DEFAULT NULL
312 , ptest_priority IN VARCHAR2 DEFAULT NULL
313 , pcreate_vers IN VARCHAR2 DEFAULT 'N'
314 ) IS
315 l_api_name VARCHAR2 (100) := 'MASS_REPLACE_OPER_SPEC_VAL';
316 l_mesg_count NUMBER;
317 l_row_id VARCHAR2 (200);
318 l_mesg_data VARCHAR2 (2000);
319 l_return_status VARCHAR2 (1);
320 l_action_flag VARCHAR2 (1) := 'U';
321 l_user_id NUMBER := fnd_global.user_id;
322 l_text VARCHAR2 (100);
323 l_object_name_vers VARCHAR2 (200);
324 l_retval BOOLEAN;
325 l_version_enabled VARCHAR2 (1);
326 l_rowcount NUMBER;
327 l_error_text VARCHAR2 (2000);
328 l_dummy_cnt NUMBER;
329 -- Define different table types
330 l_spec_test_rec gmd_spec_tests_b%ROWTYPE;
331 l_gmd_test_rec gmd_qc_tests_b%ROWTYPE;
332 l_gmd_spec_rec gmd_specifications_b%ROWTYPE;
333 l_optional_ind gmd_spec_tests_b.optional_ind%TYPE;
334 l_print_spec_ind gmd_spec_tests_b.print_spec_ind%TYPE;
335 l_print_result_ind gmd_spec_tests_b.print_result_ind%TYPE;
336 l_target_value_num gmd_spec_tests_b.target_value_num%TYPE;
337 l_target_value_char gmd_spec_tests_b.target_value_char%TYPE;
338 l_min_value_num gmd_spec_tests_b.min_value_num%TYPE;
339 l_min_value_char gmd_spec_tests_b.min_value_char%TYPE;
340 l_max_value_num gmd_spec_tests_b.max_value_num%TYPE;
341 l_max_value_char gmd_spec_tests_b.max_value_char%TYPE;
342 l_report_precision gmd_spec_tests_b.report_precision%TYPE;
343 l_store_precision gmd_spec_tests_b.display_precision%TYPE;
344 l_test_priority gmd_spec_tests_b.test_priority%TYPE;
345 l_target_min VARCHAR2 (80);
346 l_target_max VARCHAR2 (80);
347 l_old_status gmd_specifications_b.spec_status%TYPE;
348 l_new_status gmd_specifications_b.spec_status%TYPE;
349 l_old_owner gmd_specifications_b.owner_id%TYPE;
350 l_new_owner gmd_specifications_b.owner_id%TYPE;
351 l_new_owner_id gmd_specifications_b.owner_id%TYPE;
352 l_old_ownerorg gmd_specifications_b.owner_organization_id%TYPE; -- Bug# 5882074
353 l_new_ownerorg gmd_specifications_b.owner_organization_id%TYPE; -- Bug# 5882074
354 l_end_date gmd_all_spec_vrs_vl.end_date%TYPE;
355 l_start_date gmd_all_spec_vrs_vl.start_date%TYPE;
356 l_test_values_rec gmd_qm_conc_replace_pkg.test_values;
357 l_rep_test_values_rec gmd_qm_conc_replace_pkg.test_values;
358 l_obj_id NUMBER;
359 l_exist NUMBER;
360 l_seq NUMBER;
361 l_new_spec_id NUMBER;
362 l_new_test_id NUMBER;
363 l_new_spec_vers NUMBER;
364 l_revision_exists NUMBER; -- Bug# 5882074
365 l_create_vers VARCHAR2 (1);
366 l_spec_vers_ctl VARCHAR2 (10);
367 l_state VARCHAR2 (1); -- Bug# 5882074
368 l_new_spec VARCHAR2 (1);
369 l_new_spec_success VARCHAR2 (1);
370 l_query_test_name VARCHAR2 (400);
371 l_select VARCHAR2 (1000);
372 l_from VARCHAR2 (200);
373 l_where VARCHAR2 (200);
374 l_query VARCHAR2 (2000);
375 l_string VARCHAR2 (2000);
376
377 TYPE rc IS REF CURSOR;
378
379 l_rec_query rc;
380 -- Exception declare
381 NO_UPDATE_EXCEPTION EXCEPTION;
382 NO_REPLACE_EXCEPTION EXCEPTION;
383
384 CURSOR get_object_info IS
385 SELECT UPPER (pobject_type) object_type -- e.g 'SPEC_TEST' etc
386 , UPPER (preplace_type) replace_type -- e.g 'TEST_VALUE'
387 , pold_name old_name -- e.g 'SHY-SPEC-TEST'
388 , pnew_name new_name -- e.g 'TDAN-SPEC-TEST'
389 , preport_precision report_preci -- defaults to null
390 , pstore_precision store_precis -- defaults to null
391 , object_id -- e.g formula_id = 100
392 , object_name -- e.g formula_no = 'SHY-TEST'
393 , object_vers -- e.g formula_vers = 2
394 , object_desc
395 , object_status_code -- e.g formula_status = '100'
396 , concurrent_id
397 FROM gmd_msnr_results
398 WHERE object_select_ind = 1
399 AND concurrent_id = TO_NUMBER (pconcurrent_id);
400
401 CURSOR get_gmd_test1 (p_test_name IN VARCHAR2) IS
402 SELECT *
403 FROM gmd_qc_tests_b
404 WHERE test_code = p_test_name;
405
406 CURSOR get_gmd_test2 (p_test_id IN NUMBER) IS
407 SELECT *
408 FROM gmd_qc_tests_b
409 WHERE test_id = p_test_id;
410
411 CURSOR get_spec_rec (p_spec_id IN NUMBER) IS
412 SELECT *
413 FROM gmd_specifications_b
414 WHERE spec_id = p_spec_id;
415
416 CURSOR check_spec_test (p_spec_id IN NUMBER, p_test_id IN NUMBER) IS
417 SELECT 1
418 FROM gmd_spec_tests_b
419 WHERE spec_id = p_spec_id
420 AND test_id = p_test_id;
421
422 CURSOR check_spec_test2 (p_spec_id IN NUMBER, p_test_name IN VARCHAR2) IS
423 SELECT 1
424 FROM gmd_spec_tests_b sptst
425 , gmd_qc_tests_b tst
426 WHERE sptst.spec_id = p_spec_id
427 AND sptst.test_id = tst.test_id
428 AND tst.test_code = p_test_name;
429
430 CURSOR get_spec_test_by_spec (p_spec_id IN NUMBER) IS
431 SELECT *
432 FROM gmd_spec_tests_b
433 WHERE spec_id = p_spec_id;
434
435 -- Bug# 5882074 cursor definition to check for organization
436 CURSOR cur_check_item_org(p_org_id IN NUMBER, p_spec_id IN NUMBER) IS
437 SELECT msi.organization_id, msi.inventory_item_id, msi.revision_qty_control_code, s.revision
438 FROM mtl_system_items msi, gmd_specifications_b s
439 WHERE msi.organization_id = p_org_id
440 AND msi.inventory_item_id = s.inventory_item_id
441 AND s.spec_id = p_spec_id
442 AND msi.process_quality_enabled_flag = 'Y';
443
444 item_org_rec CUR_CHECK_ITEM_ORG%ROWTYPE;
445
446 -- Bug# 5882074 cursor definition to check for revision
447 CURSOR cur_check_item_rev(p_org_id IN NUMBER, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2) IS
448 SELECT count(*)
449 FROM mtl_item_revisions
450 WHERE organization_id = p_org_id
451 AND inventory_item_id = p_inventory_item_id
452 AND revision = p_revision;
453
454 BEGIN -- begin of mass_replace_operations
455 gmd_debug.log_initialize ('QMMSNR');
456 -- output the log for the input parameters
457 DEBUG (g_pkg_name || '.' || l_api_name || ' : 1st line ');
458 DEBUG ('Input Parameters:');
459 DEBUG (' Concurrent_id: ' || pconcurrent_id);
460 DEBUG (' Object_type: ' || pobject_type);
461 DEBUG (' Replace_type: ' || preplace_type);
462 DEBUG (' Old_name: ' || pold_name);
463 DEBUG (' New_name: ' || pnew_name);
464 DEBUG (' Optional Ind: ' || poptional_ind);
465 DEBUG (' Print Spec Ind ' || pprint_spec_ind);
466 DEBUG (' Print Result Ind: ' || pprint_result_ind);
467 DEBUG (' Target Value : ' || ptarget_value);
468 DEBUG (' Target Min : ' || ptarget_min);
469 DEBUG (' Target Max : ' || ptarget_max);
470 DEBUG (' Report_Precision: ' || preport_precision);
471 DEBUG (' Store_Precision: ' || pstore_precision);
472 DEBUG (' Test_Priority: ' || ptest_priority);
473 DEBUG (' Create_Vers: ' || pcreate_vers);
474
475 l_string := '';
476 SELECT meaning
477 INTO l_string
478 FROM gem_lookups
479 WHERE lookup_type = 'GMD_QM_REPLACE_OPTIONS'
480 AND lookup_code = preplace_type;
481
482 IF pobject_type = 'SPECIFICATION' THEN
483 -- REPLACE TYPE could be on of the following
484 -- REPSTATUS , REPOWNER, REPOWNERORG
485 -- REPTESTVAL, ADDTEST, DELTEST, REPTEST
486 default_log ('Replacement for Specifications');
487 default_log (' Action: ' || l_string);
488
489 IF preplace_type = 'REPSTATUS' THEN
490 default_log (' Old Status: ' || pold_name);
491 default_log (' New Status: ' || pnew_name);
492 ELSIF preplace_type = 'REPOWNER' THEN
493 default_log (' Old Owner : ' || pold_name);
494 default_log (' New Owner : ' || pnew_name);
495 ELSIF preplace_type = 'REPOWNERORG' THEN
496 default_log (' Old Owner Organization Code: ' || pold_name);
497 default_log (' New Owner Organization Code: ' || pnew_name);
498 ELSIF preplace_type = 'ADDTEST' THEN
499 default_log (' Test Name : ' || pnew_name);
500 ELSIF preplace_type = 'REPTEST' THEN
501 default_log (' Old Test Name : ' || pold_name);
502 default_log (' New Test Name : ' || pnew_name);
503 ELSIF preplace_type = 'DELTEST' THEN
504 default_log (' Test Name : ' || pnew_name);
505 ELSIF preplace_type = 'REPTESTVAL' THEN
506 default_log (' Test Name : ' || pnew_name);
507 default_log (' New Optional Ind: ' || poptional_ind);
508 default_log (' New Print Spec Ind ' || pprint_spec_ind);
509 default_log (' New Print Result Ind: ' || pprint_result_ind);
510 default_log (' New Target Value : ' || ptarget_value);
511 default_log (' New Target Min : ' || ptarget_min);
512 default_log (' New Target Max : ' || ptarget_max);
513 default_log (' New Report_Precision: ' || preport_precision);
514 default_log (' New Store_Precision: ' || pstore_precision);
515 default_log (' New Test_Priority: ' || ptest_priority);
516 END IF;
517 ELSIF pobject_type = 'VALIDITY' THEN
518 -- REPSTART, REPEND, REPSTATUS
519 default_log ('Replacement for Validity Rules');
520 default_log (' Action: ' || l_string);
521
522 IF preplace_type = 'REPSTATUS' THEN
523 default_log (' Old Status: ' || pold_name);
524 default_log (' New Status: ' || pnew_name);
525 ELSIF preplace_type = 'REPSTART' THEN
526 default_log (' Old Start Date : ' || fnd_date.canonical_to_date (pold_name));
527 default_log (' New Start Date : ' || fnd_date.canonical_to_date (pnew_name));
528 ELSIF preplace_type = 'REPEND' THEN
529 default_log (' Old End Date : ' || fnd_date.canonical_to_date (pold_name));
530 default_log (' New End Date : ' || fnd_date.canonical_to_date (pnew_name));
531 END IF;
532 END IF;
533
534 -- Using concurrent_id/request_id we get the details on the object and column that
535 -- is being replaced.
536 -- Please Note : Each request id can have multiple replace rows.
537 FOR get_object_rec IN get_object_info LOOP
538 SAVEPOINT mass_replace_for_one;
539 -- Initialize the following variables
540 l_return_status := 'S';
541 l_new_spec_success := 'N';
542 l_new_spec_id := NULL;
543 l_create_vers := pcreate_vers;
544
545 BEGIN
546 l_string := '';
547 DEBUG (g_pkg_name || '.' || l_api_name);
548 l_string := l_string
549 || 'Replacing - object type: '
550 || get_object_rec.object_type
551 || ' Object_name: '
552 || get_object_rec.object_name
553 || ' Object_vers: '
554 || get_object_rec.object_vers
555 || ' Replacement Type:'
556 || get_object_rec.replace_type
557 || ' Object Status:'
558 || get_object_rec.object_status_code;
559 default_log (' ');
560 default_log (' ');
561 default_log (l_string);
562
563 -- Making new line entry and prompting users about MSNR request
564 fnd_file.new_line (fnd_file.LOG, 1);
565 --FND_FILE.NEW_LINE(FND_FILE.OUTPUT,1);
566 fnd_message.set_name ('GMD', 'GMD_SPVL_REPLACE_MESG');
567 fnd_message.set_token ('OBJECT_TYPE', get_object_rec.object_type);
568 fnd_message.set_token ('NAME', get_object_rec.object_name);
569 fnd_message.set_token ('VERSION', get_object_rec.object_vers);
570 fnd_message.set_token ('REPLACE_TYPE', get_object_rec.replace_type);
571 fnd_file.put (fnd_file.LOG, fnd_message.get);
572 fnd_file.new_line (fnd_file.LOG, 1);
573 --FND_FILE.PUT(FND_FILE.OUTPUT,FND_MESSAGE.GET);
574 --FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
575
576 IF pobject_type = 'SPECIFICATION' THEN
577 /* REPLACE TYPE could be on of the following
578 REPSTATUS , REPOWNER, REPOWNERORG
579 REPTESTVAL, ADDTEST, DELTEST, REPTEST
580 */
581
582 -- Bug# 5882074 get the version control indicator from gmd_quality_config
583 -- l_spec_vers_ctl := fnd_profile.value('GMD_SPEC_VERSION_CONTROL');
584 l_query_test_name := pnew_name;
585 l_select := 'select spts.* '; --Bug#5973270. Included spts.
586
587 IF l_query_test_name IS NOT NULL THEN
588 l_from := ' from gmd_spec_tests_b spts ' || ' , gmd_qc_tests qcts ';
589 l_where :=
590 ' Where spts.test_id = qcts.test_id '
591 || ' and spts.spec_id = ' || get_object_rec.object_id
592 || ' and qcts.test_code = ' || '''' || l_query_test_name || '''';
593 ELSE
594 l_from := ' from gmd_spec_tests_b spts ';
595 l_where := ' Where spts.spec_id = ' || get_object_rec.object_id;
596 END IF;
597
598 l_query := l_select || l_from || l_where;
599
600 --IF (l_debug = 'Y') THEN
601 -- gmd_debug.put_line('l_query '||l_query );
602 --End if;
603
604 /* should new spec version be created? */
605 -- Bug# 5882074 get the version control flag from quality config instead of profile.
606 SELECT spec_version_control_ind
607 INTO l_state
608 FROM gmd_quality_config
609 WHERE organization_id = (SELECT owner_organization_id
610 FROM gmd_specifications_b
611 WHERE spec_id = get_object_rec.object_id);
612
613 l_spec_vers_ctl := gmd_spec_grp.version_control_state (
614 p_entity => l_state -- fnd_profile.value('GMD_SPEC_VERSION_CONTROL')
615 ,p_entity_id => get_object_rec.object_id);
616
617 DEBUG (' l_spec_vers_ctl ' || l_spec_vers_ctl);
618 l_new_spec := 'N';
619
620 IF l_spec_vers_ctl = 'Y' THEN
621 l_new_spec := 'Y';
622 ELSIF ( l_spec_vers_ctl = 'O' AND l_create_vers = 'Y') THEN
623 l_new_spec := 'Y';
624 END IF;
625
626 IF get_object_rec.object_status_code IN (100, 1000) THEN -- new or obsolete status, NO new spec
627 l_new_spec := 'N';
628 END IF;
629 default_log (' Version Controlled: ' || l_new_spec);
630
631 IF get_object_rec.replace_type IN ('REPSTATUS', 'REPOWNER', 'REPOWNERORG') THEN
632 -- Do NOT create new version in any cases for these
633 IF get_object_rec.replace_type = 'REPSTATUS' THEN
634 l_old_status := pold_name;
635 l_new_status := pnew_name;
636 if get_object_rec.object_status_code = l_old_status then
637 default_log ( ' Replace Spec Status, Spec Name: '
638 || get_object_rec.object_name
639 || ', Version: '
640 || get_object_rec.object_vers
641 || ' Old Status :'
642 || l_old_status
643 || ' New Status :'
644 || l_new_status);
645
646 UPDATE gmd_specifications_b
647 SET spec_status = l_new_status
648 WHERE spec_id = get_object_rec.object_id;
649
650 default_log (' Update Specification Succesful');
651 else
652 default_log(' Spec status does not match. Old status: ' ||get_object_rec.object_status_code);
653 raise no_update_exception;
654 end if;
655 ELSIF get_object_rec.replace_type = 'REPOWNER' THEN
656 --RLNAGARA Bug#6017214 Added the below IF condition
657 IF get_object_rec.object_status_code IN (200,500,800,1000) THEN
658 default_log('Only the Status column for this Obsoleted/Archieved or On-Hold or Request for Approval entity can be replaced');
659 RAISE NO_UPDATE_EXCEPTION;
660 END IF;
661 l_old_owner := pold_name;
662 l_new_owner := pnew_name;
663 if l_gmd_spec_rec.owner_id = l_old_owner then
664 default_log ( ' Replace Spec Owner, Spec Name: '
665 || get_object_rec.object_name
666 || ', Version: '
667 || get_object_rec.object_vers
668 || ' Old Owner :'
669 || l_old_owner
670 || ' New Owner :'
671 || l_new_owner);
672
673 /*Select user_id
674 Into l_new_owner_id
675 From fnd_user
676 Where user_name = l_new_Owner;
677 */
678 UPDATE gmd_specifications_b
679 SET owner_id = l_new_owner
680 WHERE spec_id = get_object_rec.object_id;
681 default_log (' Update Specification Succesful');
682 else
683 default_log(' Spec Owner does not match. Old Owner: ' ||l_gmd_spec_rec.owner_id);
684 raise no_update_exception;
685 end if;
686 ELSE
687 --RLNAGARA Bug#6017214 Added the below IF condition
688 IF get_object_rec.object_status_code IN (200,500,800,1000) THEN
689 default_log('Only the Status column for this Obsoleted/Archieved or On-Hold or Request for Approval entity can be replaced');
690 RAISE NO_UPDATE_EXCEPTION;
691 END IF;
692
693 l_old_ownerorg := pold_name;
694 l_new_ownerorg := pnew_name;
695 if l_gmd_spec_rec.owner_orgn_code = l_old_ownerorg then
696 --Bug# 5882074 check if item is assigned to the organization.
697 OPEN cur_check_item_org(l_new_ownerorg, get_object_rec.object_id);
698 FETCH cur_check_item_org into item_org_rec;
699 CLOSE cur_check_item_org;
700
701 IF item_org_rec.organization_id IS NULL THEN
702 --FND_MESSAGE.SET_NAME('gmd', 'GMD_ITEM_ORG_NOT_FOUND');
703 --FND_MESSAGE.SET_TOKEN('ITEM',get_item_no(get_object_rec.new_name));
704 --FND_MESSAGE.SET_TOKEN('ORGN',get_orgn_code(l_orgn_id));
705 --FND_MSG_PUB.ADD;
706 default_log ( ' Item is not assigned to the organization ');
707 RAISE NO_UPDATE_EXCEPTION;
708 END IF;
709
710 -- Check if Revision exists for the item.
711 IF item_org_rec.revision_qty_control_code = 2 AND item_org_rec.revision IS NOT NULL THEN
712 OPEN cur_check_item_rev(item_org_rec.organization_id, item_org_rec.inventory_item_id, item_org_rec.revision);
713 FETCH cur_check_item_rev INTO l_revision_exists;
714 CLOSE cur_check_item_rev;
715
716 IF l_revision_exists <> 1 THEN
717 default_log ( ' Revision Does not exists for the Item/organization ');
718 RAISE NO_UPDATE_EXCEPTION;
719 END IF;
720 END IF;
721
722 IF item_org_rec.revision_qty_control_code = 1 AND item_org_rec.revision IS NOT NULL THEN
723 default_log ( ' Item is not Revision controlled');
724 RAISE NO_UPDATE_EXCEPTION;
725 END IF;
726
727 -- Bug# 5882074 Changed code to id
728 UPDATE gmd_specifications_b
729 SET owner_organization_id = l_new_ownerorg
730 WHERE spec_id = get_object_rec.object_id;
731
732 default_log ( ' Replace Spec OwnerOrganization, Spec Name: '
733 || get_object_rec.object_name
734 || ', Version: '
735 || get_object_rec.object_vers
736 || ' Old OwnerOrganization :'
737 || l_old_ownerorg
738 || ' New OwnerOrganization :'
739 || l_new_ownerorg);
740 default_log (' Update Specification Succesful');
741 else
742 default_log(' Spec OwnerOrg does not match. Old OwnerOrg: ' ||l_gmd_spec_rec.owner_orgn_code);
743 raise no_update_exception;
744 end if;
745 END IF;
746 END IF;
747
748 IF get_object_rec.replace_type = 'REPTESTVAL' THEN
749 --RLNAGARA Bug#6017214 Added the below IF condition
750 IF get_object_rec.object_status_code IN (200,500,800,1000) THEN
751 default_log('Only the Status column for this Obsoleted/Archieved or On-Hold or Request for Approval entity can be replaced');
752 RAISE NO_UPDATE_EXCEPTION;
753 END IF;
754
755 l_new_test_id := NULL;
756
757 OPEN l_rec_query FOR l_query;
758 LOOP
759 FETCH l_rec_query INTO l_spec_test_rec;
760 EXIT WHEN l_rec_query%NOTFOUND;
761 l_new_test_id := l_spec_test_rec.test_id;
762
763 OPEN get_gmd_test2 (l_spec_test_rec.test_id);
764 FETCH get_gmd_test2 INTO l_gmd_test_rec;
765 CLOSE get_gmd_test2;
766
767 /* set the initial value as the same as the record */
768 init_test_values_rec (
769 p_spec_tests_rec => l_spec_test_rec
770 , x_test_values_rec => l_test_values_rec);
771
772 DEBUG (' test_rec.test_type ' || l_gmd_test_rec.test_type);
773 DEBUG (' fetch gmd_qc_tests_b, test id ' || l_spec_test_rec.test_id);
774
775 set_test_values (p_gmd_test_rec => l_gmd_test_rec
776 , poptional_ind => poptional_ind
777 , pprint_spec_ind => pprint_spec_ind
778 , pprint_result_ind => pprint_result_ind
779 , ptarget_value => ptarget_value
780 , ptarget_min => ptarget_min
781 , ptarget_max => ptarget_max
782 , preport_precision => preport_precision
783 , pstore_precision => pstore_precision
784 , ptest_priority => ptest_priority
785 , x_return_status => l_return_status
786 , x_test_values_rec => l_test_values_rec
787 );
788
789 IF l_return_status <> fnd_api.g_ret_sts_success THEN
790 RAISE NO_UPDATE_EXCEPTION;
791 END IF;
792
793 /* need to validate the min max for the test, no changes made if exceeds the max*/
794 --Bug#5973270. To handle NULL target test values in the Mass Search and Replace form.
795 IF (NVL (l_test_values_rec.target_value_num, l_test_values_rec.max_value_num) > NVL (l_test_values_rec.max_value_num, 0))
796 OR (NVL (l_test_values_rec.target_value_num, l_test_values_rec.min_value_num) < NVL (l_test_values_rec.min_value_num, 0)) THEN
797 -- raise an error for this record, abort!
798 default_log ( ' Target value is out of range, '
799 || ' Target Value: '
800 || l_test_values_rec.target_value_num
801 || ' Target Max: '
802 || l_test_values_rec.max_value_num
803 || ' Target Min: '
804 || l_test_values_rec.min_value_num);
805 RAISE NO_UPDATE_EXCEPTION;
806 END IF;
807
808 IF NVL (l_test_values_rec.min_value_num, 0) > NVL (l_test_values_rec.max_value_num, 0) THEN
809 -- raise an error for this record, abort!
810 default_log ( ' Target min value is out of range, '
811 || ' Target Value: '
812 || l_test_values_rec.target_value_num
813 || ' Target Max: '
814 || l_test_values_rec.max_value_num
815 || ' Target Min: '
816 || l_test_values_rec.min_value_num);
817 RAISE NO_UPDATE_EXCEPTION;
818 END IF;
819
820 /* if only the precision, print ind change, No new spec */
821 IF ( (ptarget_value IS NULL)
822 AND (ptarget_min IS NULL)
823 AND (ptarget_max IS NULL)
824 AND (poptional_ind IS NULL)
825 AND (pstore_precision IS NULL)
826 ) THEN
827 l_new_spec := 'N';
828 default_log (' No New spec is creted ');
829 END IF;
830
831 IF (l_new_spec = 'N') THEN
832 DEBUG (' update gmd_spec_tests_b, spec_id ' || l_spec_test_rec.spec_id);
833 DEBUG (' update gmd_spec_tests_b, test_id ' || l_spec_test_rec.test_id);
834
835 UPDATE gmd_spec_tests_b
836 SET optional_ind = l_test_values_rec.optional_ind
837 , print_spec_ind = l_test_values_rec.print_spec_ind
838 , print_result_ind = l_test_values_rec.print_result_ind
839 , target_value_num = l_test_values_rec.target_value_num
840 , target_value_char = l_test_values_rec.target_value_char
841 , min_value_char = l_test_values_rec.min_value_char
842 , max_value_char = l_test_values_rec.max_value_char
843 , min_value_num = l_test_values_rec.min_value_num
844 , max_value_num = l_test_values_rec.max_value_num
845 , report_precision = l_test_values_rec.report_precision
846 , display_precision = l_test_values_rec.store_precision
847 , test_priority = l_test_values_rec.test_priority
848 WHERE spec_id = l_spec_test_rec.spec_id
849 AND test_id = l_spec_test_rec.test_id;
850
851 default_log ( ' Succesfully Replaced Record, Spec Name: '
852 || get_object_rec.object_name
853 || ', Version: '
854 || get_object_rec.object_vers
855 || ', Test Name: '
856 || l_gmd_test_rec.test_code);
857
858 l_new_spec_id := l_spec_test_rec.spec_id;
859 ELSE -- Version controled
860 /* create new version of spec */
861 /* spec only needs to be created once */
862 default_log (' Version Controlled. Creating New Version of Spec:'
863 || get_object_rec.object_name);
864
865 create_new_specification (p_old_spec_id => get_object_rec.object_id
866 , p_action_code => 'NEWVERS'
867 , x_new_spec_id => l_new_spec_id
868 , x_new_spec_vers => l_new_spec_vers
869 , x_return_status => l_return_status
870 );
871
872 IF l_return_status <> fnd_api.g_ret_sts_success THEN
873 RAISE NO_UPDATE_EXCEPTION;
874 END IF;
875
876 /* default new spec_rec with the old one*/
877 l_spec_test_rec.spec_id := l_new_spec_id;
878
879 FOR get_spec_test IN get_spec_test_by_spec (get_object_rec.object_id) LOOP
880 OPEN get_gmd_test2 (get_spec_test.test_id);
881 FETCH get_gmd_test2 INTO l_gmd_test_rec;
882 CLOSE get_gmd_test2;
883
884 l_optional_ind := NULL;
885 l_print_spec_ind := NULL;
886 l_print_result_ind := NULL;
887 l_target_value_char := NULL;
888 l_min_value_char := NULL;
889 l_max_value_char := NULL;
890 l_report_precision := NULL;
891 l_store_precision := NULL;
892 l_test_priority := NULL;
893
894 IF l_new_test_id = l_gmd_test_rec.test_id THEN
895 l_optional_ind := poptional_ind;
896 l_print_spec_ind := pprint_spec_ind;
897 l_print_result_ind := pprint_result_ind;
898 l_target_value_char := ptarget_value;
899 l_min_value_char := ptarget_min;
900 l_max_value_char := ptarget_max;
901 l_report_precision := preport_precision;
902 l_store_precision := pstore_precision;
903 l_test_priority := ptest_priority;
904 END IF;
905
906 default_spectest_from_spectest (p_from_spec_id => get_object_rec.object_id
907 , p_from_test_id => l_gmd_test_rec.test_id
908 , p_to_test_name => l_gmd_test_rec.test_code
909 , poptional_ind => l_optional_ind
910 , pprint_spec_ind => l_print_spec_ind
911 , pprint_result_ind => l_print_result_ind
912 , ptarget_value => l_target_value_char
913 , ptarget_min => l_min_value_char
914 , ptarget_max => l_max_value_char
915 , preport_precision => l_report_precision
916 , pstore_precision => l_store_precision
917 , ptest_priority => l_test_priority
918 , p_spec_test_rec => l_spec_test_rec
919 , x_return_status => l_return_status
920 );
921
922 IF l_return_status <> fnd_api.g_ret_sts_success THEN
923 RAISE NO_UPDATE_EXCEPTION;
924 END IF;
925
926 default_log ( ' Copying Test: '
927 || l_gmd_test_rec.test_code
928 || ' to Spec:'
929 || get_object_rec.object_name
930 || ', Version: '
931 || l_new_spec_vers);
932
933 add_spec_test_rec (p_spec_id => l_new_spec_id
934 , p_test_name => l_gmd_test_rec.test_code
935 , p_spec_name => get_object_rec.object_name
936 , p_spec_test_rec => l_spec_test_rec
937 , x_return_status => l_return_status
938 );
939
940 IF l_return_status <> fnd_api.g_ret_sts_success THEN
941 RAISE NO_UPDATE_EXCEPTION;
942 END IF;
943 END LOOP;
944
945 l_new_spec_success := 'Y';
946 END IF;
947 END LOOP; -- spec_tests_rec loop
948
949 CLOSE l_rec_query;
950
951 IF l_new_test_id IS NULL THEN
952 default_log ( ' Spec test record NOT EXIST -- Spec Name: '
953 || get_object_rec.object_name
954 || ', Version: '
955 || get_object_rec.object_vers
956 || ', Test name: '
957 || pnew_name);
958 RAISE NO_UPDATE_EXCEPTION;
959 END IF;
960 END IF; -- REPLTESTVAL
961
962 IF get_object_rec.replace_type = 'ADDTEST' THEN
963 --RLNAGARA Bug#6017214 Added the below IF condition
964 IF get_object_rec.object_status_code IN (200,500,800,1000) THEN
965 default_log('Only the Status column for this Obsoleted/Archieved or On-Hold or Request for Approval entity can be replaced');
966 RAISE NO_UPDATE_EXCEPTION;
967 END IF;
968 /* check to see if the spec_test already exist, if so, abort, no changes */
969 DEBUG ('ADDTEST, new test name: ' || pnew_name);
970 l_exist := 0;
971
972 OPEN check_spec_test2 (get_object_rec.object_id, pnew_name);
973 FETCH check_spec_test2 INTO l_exist;
974 CLOSE check_spec_test2;
975
976 IF l_exist = 1 THEN
977 default_log ( ' Spec test record EXIST -- Spec Name: '
978 || get_object_rec.object_name
979 || ', Version: '
980 || get_object_rec.object_vers
981 || ', Test name: '
982 || pnew_name);
983 RAISE NO_UPDATE_EXCEPTION;
984 END IF;
985
986 IF (l_new_spec = 'N') THEN -- Not version controled
987 l_new_spec_id := get_object_rec.object_id;
988 ELSE -- version controled
989 /* create new version of spec */
990 /* spec only needs to be created once */
991 default_log (' Version Controlled. Creating New Version of Spec: ' || get_object_rec.object_name);
992
993 create_new_specification (p_old_spec_id => get_object_rec.object_id
994 , p_action_code => 'NEWVERS'
995 , x_new_spec_id => l_new_spec_id
996 , x_new_spec_vers => l_new_spec_vers
997 , x_return_status => l_return_status
998 );
999
1000 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1001 RAISE NO_UPDATE_EXCEPTION;
1002 END IF;
1003
1004 /* do the loop for all spec tests for the old spec */
1005 /* default new spec_rec with the old one*/
1006 l_spec_test_rec.spec_id := l_new_spec_id;
1007
1008 FOR get_spec_test IN get_spec_test_by_spec (get_object_rec.object_id) LOOP
1009 OPEN get_gmd_test2 (get_spec_test.test_id);
1010 FETCH get_gmd_test2 INTO l_gmd_test_rec;
1011 CLOSE get_gmd_test2;
1012
1013 default_spectest_from_spectest (p_from_spec_id => get_object_rec.object_id
1014 , p_from_test_id => l_gmd_test_rec.test_id
1015 , p_to_test_name => l_gmd_test_rec.test_code
1016 , poptional_ind => NULL
1017 , pprint_spec_ind => NULL
1018 , pprint_result_ind => NULL
1019 , ptarget_value => NULL
1020 , ptarget_min => NULL
1021 , ptarget_max => NULL
1022 , preport_precision => NULL
1023 , pstore_precision => NULL
1024 , ptest_priority => NULL
1025 , p_spec_test_rec => l_spec_test_rec
1026 , x_return_status => l_return_status
1027 );
1028
1029 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1030 RAISE NO_UPDATE_EXCEPTION;
1031 END IF;
1032
1033 default_log ( ' Copying Test: '
1034 || l_gmd_test_rec.test_code
1035 || ' to Spec: '
1036 || get_object_rec.object_name
1037 || ', Version: '
1038 || l_new_spec_vers);
1039
1040 add_spec_test_rec (p_spec_id => l_new_spec_id
1041 , p_test_name => l_gmd_test_rec.test_code
1042 , p_spec_name => get_object_rec.object_name
1043 , p_spec_test_rec => l_spec_test_rec
1044 , x_return_status => l_return_status
1045 );
1046
1047 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1048 RAISE NO_UPDATE_EXCEPTION;
1049 END IF;
1050 END LOOP;
1051
1052 l_new_spec_success := 'Y';
1053 END IF;
1054
1055 /* add the new test to the spec */
1056 default_spectest_from_test (p_spec_id => l_new_spec_id
1057 , p_test_name => pnew_name
1058 , poptional_ind => poptional_ind
1059 , pprint_spec_ind => pprint_spec_ind
1060 , pprint_result_ind => pprint_result_ind
1061 , ptarget_value => ptarget_value
1062 , ptarget_min => ptarget_min
1063 , ptarget_max => ptarget_max
1064 , preport_precision => preport_precision
1065 , pstore_precision => pstore_precision
1066 , ptest_priority => ptest_priority
1067 , p_spec_test_rec => l_spec_test_rec
1068 , x_return_status => l_return_status
1069 );
1070
1071 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1072 RAISE NO_UPDATE_EXCEPTION;
1073 END IF;
1074
1075 add_spec_test_rec (p_spec_id => l_new_spec_id
1076 , p_test_name => pnew_name
1077 , p_spec_name => get_object_rec.object_name
1078 , p_spec_test_rec => l_spec_test_rec
1079 , x_return_status => l_return_status
1080 );
1081
1082 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1083 RAISE NO_UPDATE_EXCEPTION;
1084 END IF;
1085 END IF; --ADDTEST
1086
1087 IF get_object_rec.replace_type = 'DELTEST' THEN
1088 --RLNAGARA Bug#6017214 Added the below IF condition
1089 IF get_object_rec.object_status_code IN (200,500,800,1000) THEN
1090 default_log('Only the Status column for this Obsoleted/Archieved or On-Hold or Request for Approval entity can be replaced');
1091 RAISE NO_UPDATE_EXCEPTION;
1092 END IF;
1093 -- DELTEST, spec_test exists
1094 -- Go ahead delete it if not version controled
1095 IF (l_new_spec = 'N') THEN -- Not version controled
1096 del_spec_test_rec (p_spec_id => get_object_rec.object_id
1097 , p_spec_name => get_object_rec.object_name
1098 , p_test_name => pnew_name
1099 , x_return_status => l_return_status
1100 );
1101
1102 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1103 RAISE NO_UPDATE_EXCEPTION;
1104 END IF;
1105 ELSE
1106 -- if version controled, add a new spec version and create spec_test records without this test
1107 /* create new version of spec */
1108 /* spec only needs to be created once */
1109 default_log (' Version Controlled. Creating New Version of Spec: ' || get_object_rec.object_name);
1110
1111 create_new_specification (p_old_spec_id => get_object_rec.object_id
1112 , p_action_code => 'NEWVERS'
1113 , x_new_spec_id => l_new_spec_id
1114 , x_new_spec_vers => l_new_spec_vers
1115 , x_return_status => l_return_status
1116 );
1117 /* do the loop for all spec tests for the old spec */
1118 /* default new spec_rec with the old one*/
1119 l_spec_test_rec.spec_id := l_new_spec_id;
1120
1121 FOR get_spec_test IN get_spec_test_by_spec (get_object_rec.object_id) LOOP
1122 OPEN get_gmd_test2 (get_spec_test.test_id);
1123 FETCH get_gmd_test2 INTO l_gmd_test_rec;
1124 CLOSE get_gmd_test2;
1125
1126 IF l_gmd_test_rec.test_id <> TO_NUMBER (pnew_name) THEN /* do not insert the one to be deleted*/
1127 default_spectest_from_spectest (p_from_spec_id => get_object_rec.object_id
1128 , p_from_test_id => l_gmd_test_rec.test_id
1129 , p_to_test_name => l_gmd_test_rec.test_code
1130 , poptional_ind => NULL
1131 , pprint_spec_ind => NULL
1132 , pprint_result_ind => NULL
1133 , ptarget_value => NULL
1134 , ptarget_min => NULL
1135 , ptarget_max => NULL
1136 , preport_precision => NULL
1137 , pstore_precision => NULL
1138 , ptest_priority => NULL
1139 , p_spec_test_rec => l_spec_test_rec
1140 , x_return_status => l_return_status
1141 );
1142
1143 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1144 RAISE NO_UPDATE_EXCEPTION;
1145 END IF;
1146
1147 default_log ( ' Copying Test: '
1148 || l_gmd_test_rec.test_code
1149 || ' to Spec: '
1150 || get_object_rec.object_name
1151 || ', Version: '
1152 || l_new_spec_vers);
1153
1154 add_spec_test_rec (p_spec_id => l_new_spec_id
1155 , p_test_name => l_gmd_test_rec.test_code
1156 , p_spec_name => get_object_rec.object_name
1157 , p_spec_test_rec => l_spec_test_rec
1158 , x_return_status => l_return_status
1159 );
1160
1161 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1162 RAISE NO_UPDATE_EXCEPTION;
1163 END IF;
1164 END IF;
1165 END LOOP;
1166
1167 l_new_spec_success := 'Y';
1168 END IF;
1169 END IF; --DELTEST
1170
1171 IF get_object_rec.replace_type = 'REPTEST' THEN
1172 --RLNAGARA Bug#6017214 Added the below IF condition
1173 IF get_object_rec.object_status_code IN (200,500,800,1000) THEN
1174 default_log('Only the Status column for this Obsoleted/Archieved or On-Hold or Request for Approval entity can be replaced');
1175 RAISE NO_UPDATE_EXCEPTION;
1176 END IF;
1177
1178 /* old test is deleted and new ones are created */
1179 IF l_new_spec = 'N' THEN
1180 l_new_spec_id := get_object_rec.object_id;
1181 ELSE
1182 -- create new spec version
1183 -- create_spec_test for the new version (without the old test, default from the old spectest )
1184 -- add new_spec_test (default from the test)
1185 /* create new version of spec */
1186 /* spec only needs to be created once */
1187 default_log (' Version Controlled. Creating New Version of Spec: ' || get_object_rec.object_name);
1188
1189 create_new_specification (p_old_spec_id => get_object_rec.object_id
1190 , p_action_code => 'NEWVERS'
1191 , x_new_spec_id => l_new_spec_id
1192 , x_new_spec_vers => l_new_spec_vers
1193 , x_return_status => l_return_status
1194 );
1195
1196 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1197 RAISE NO_UPDATE_EXCEPTION;
1198 END IF;
1199
1200 /* do the loop for all spec tests for the old spec */
1201 /* default new spec_rec with the old one*/
1202 l_spec_test_rec.spec_id := l_new_spec_id;
1203
1204 FOR get_spec_test IN get_spec_test_by_spec (get_object_rec.object_id) LOOP
1205 OPEN get_gmd_test2 (get_spec_test.test_id);
1206 FETCH get_gmd_test2 INTO l_gmd_test_rec;
1207 CLOSE get_gmd_test2;
1208
1209 IF l_gmd_test_rec.test_id <> TO_NUMBER (pold_name) THEN
1210 /* do not insert the one to be replaced*/
1211 default_spectest_from_spectest (p_from_spec_id => get_object_rec.object_id
1212 , p_from_test_id => l_gmd_test_rec.test_id
1213 , p_to_test_name => l_gmd_test_rec.test_code
1214 , poptional_ind => NULL
1215 , pprint_spec_ind => NULL
1216 , pprint_result_ind => NULL
1217 , ptarget_value => NULL
1218 , ptarget_min => NULL
1219 , ptarget_max => NULL
1220 , preport_precision => NULL
1221 , pstore_precision => NULL
1222 , ptest_priority => NULL
1223 , p_spec_test_rec => l_spec_test_rec
1224 , x_return_status => l_return_status
1225 );
1226
1227 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1228 RAISE NO_UPDATE_EXCEPTION;
1229 END IF;
1230
1231 default_log ( ' Copying Test: '
1232 || l_gmd_test_rec.test_code
1233 || ' to Spec: '
1234 || get_object_rec.object_name
1235 || ', Version: '
1236 || l_new_spec_vers);
1237
1238 add_spec_test_rec (p_spec_id => l_new_spec_id
1239 , p_test_name => l_gmd_test_rec.test_code
1240 , p_spec_name => get_object_rec.object_name
1241 , p_spec_test_rec => l_spec_test_rec
1242 , x_return_status => l_return_status
1243 );
1244
1245 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1246 RAISE NO_UPDATE_EXCEPTION;
1247 END IF;
1248 END IF;
1249 END LOOP;
1250
1251 l_new_spec_success := 'Y';
1252 END IF;
1253
1254 -- init test_values, for REPTEST, the pNew_name is required, so only one record
1255 /* take the default from the old spec_test */
1256 l_exist := 0;
1257 OPEN check_spec_test (get_object_rec.object_id, TO_NUMBER (pold_name));
1258 FETCH check_spec_test INTO l_exist;
1259 CLOSE check_spec_test;
1260
1261 IF l_exist = 0 THEN
1262 default_log ( ' Spec test record does NOT exist -- Spec Name: '
1263 || get_object_rec.object_name
1264 || ', Version: '
1265 || get_object_rec.object_vers
1266 || ', test_id: '
1267 || TO_NUMBER (pold_name));
1268 RAISE NO_UPDATE_EXCEPTION;
1269 END IF;
1270
1271 -- Bug# 5882074 changed default_spectest_from_spectest to default_spectest_from_test
1272 /*default_spectest_from_spectest (p_from_spec_id => get_object_rec.object_id
1273 , p_from_test_id => TO_NUMBER (pold_name)
1274 , p_to_test_name => pnew_name
1275 , poptional_ind => poptional_ind
1276 , pprint_spec_ind => pprint_spec_ind
1277 , pprint_result_ind => pprint_result_ind
1278 , ptarget_value => ptarget_value
1279 , ptarget_min => ptarget_min
1280 , ptarget_max => ptarget_max
1281 , preport_precision => preport_precision
1282 , pstore_precision => pstore_precision
1283 , ptest_priority => ptest_priority
1284 , p_spec_test_rec => l_spec_test_rec
1285 , x_return_status => l_return_status
1286 );*/
1287
1288 default_spectest_from_test (p_spec_id => l_new_spec_id
1289 , p_test_name => pnew_name
1290 , poptional_ind => poptional_ind
1291 , pprint_spec_ind => pprint_spec_ind
1292 , pprint_result_ind => pprint_result_ind
1293 , ptarget_value => ptarget_value
1294 , ptarget_min => ptarget_min
1295 , ptarget_max => ptarget_max
1296 , preport_precision => preport_precision
1297 , pstore_precision => pstore_precision
1298 , ptest_priority => ptest_priority
1299 , p_spec_test_rec => l_spec_test_rec
1300 , x_return_status => l_return_status
1301 );
1302
1303 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1304 RAISE NO_UPDATE_EXCEPTION;
1305 END IF;
1306
1307 -- Bug# 5882074 assign back the sequence from the old test.
1308 SELECT seq INTO l_seq
1309 FROM gmd_spec_tests_b
1310 WHERE spec_id = get_object_rec.object_id
1311 AND test_id = TO_NUMBER (pold_name);
1312
1313 l_spec_test_rec.seq := l_seq;
1314
1315 -- delete the old spec test before adding one
1316 IF l_new_spec = 'N' THEN
1317 del_spec_test_rec (p_spec_id => l_new_spec_id
1318 , p_spec_name => get_object_rec.object_name
1319 , p_test_name => pold_name
1320 , x_return_status => l_return_status
1321 );
1322
1323 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1324 RAISE NO_UPDATE_EXCEPTION;
1325 END IF;
1326 ELSE
1327 /* do nothing if 'Y', becasue this record does not exist for the new spec */
1328 NULL;
1329 END IF;
1330
1331 -- Create new spec test
1332 add_spec_test_rec (p_spec_id => l_new_spec_id
1333 , p_test_name => pnew_name
1334 , p_spec_name => get_object_rec.object_name
1335 , p_spec_test_rec => l_spec_test_rec
1336 , x_return_status => l_return_status
1337 );
1338
1339 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1340 RAISE NO_UPDATE_EXCEPTION;
1341 END IF;
1342 END IF; --REPTEST
1343
1344 /* process validity rules for spec */
1345 IF ( NVL (l_new_spec_id, 0) <> 0
1346 AND NVL (l_new_spec_id, 0) <> get_object_rec.object_id) THEN
1347 default_log ( ' Processing Validity Rules For Spec Name: '
1348 || get_object_rec.object_name
1349 || ', Version: '
1350 || get_object_rec.object_vers);
1351
1352 process_validity_for_spec (p_object_type => pobject_type
1353 , p_old_spec_id => get_object_rec.object_id
1354 , p_new_spec_id => l_new_spec_id
1355 , x_return_status => l_return_status
1356 );
1357
1358 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1359 RAISE NO_UPDATE_EXCEPTION;
1360 END IF;
1361 END IF;
1362 ELSIF pobject_type = 'VALIDITY' THEN
1363 /* Replace type could be one of the following
1364 * REPSTART, REPEND, REPSTATUS
1365 * Update the validity rule for the spec with the new values
1366 */
1367 l_start_date := NULL;
1368 l_end_date := NULL;
1369 l_new_status := NULL;
1370
1371 IF get_object_rec.replace_type = 'REPSTART' THEN
1372 l_start_date := fnd_date.canonical_to_date (pnew_name);
1373 ELSIF get_object_rec.replace_type = 'REPEND' THEN
1374 l_end_date := fnd_date.canonical_to_date (pnew_name);
1375 ELSIF get_object_rec.replace_type = 'REPSTATUS' THEN
1376 l_new_status := TO_NUMBER (pnew_name);
1377 END IF;
1378
1379 default_log ( ' Processing Validity Rules For Spec Name: '
1380 || get_object_rec.object_name
1381 || ', Version: '
1382 || get_object_rec.object_vers);
1383
1384 process_validity_for_spec (p_object_type => pobject_type
1385 , p_spec_vr_id => get_object_rec.object_id
1386 , p_end_date => l_end_date
1387 , p_start_date => l_start_date
1388 , p_new_status => l_new_status
1389 , x_return_status => l_return_status
1390 );
1391
1392 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1393 RAISE NO_UPDATE_EXCEPTION;
1394 END IF;
1395 END IF; -- If specification or validity
1396 EXCEPTION
1397 WHEN NO_UPDATE_EXCEPTION THEN
1398 IF pobject_type = 'SPECIFICATION' THEN
1399 default_log ( 'No Replacement for Spec Name: '
1400 || get_object_rec.object_name
1401 || ', Version: '
1402 || get_object_rec.object_vers);
1403 ELSIF pobject_type = 'VALIDITY' THEN
1404 default_log ( 'No Replacement for Validity Rules of Spec Name: '
1405 || get_object_rec.object_name
1406 || ', Version: '
1407 || get_object_rec.object_vers);
1408 END IF;
1409
1410 DEBUG ('sqlerror ' || SUBSTRB (SQLERRM, 1, 100));
1411 ROLLBACK TO SAVEPOINT mass_replace_for_one;
1412 DEBUG ('Rollback to the savepoint');
1413 WHEN OTHERS THEN
1414 IF pobject_type = 'SPECIFICATION' THEN
1415 default_log ( 'No Replacement for Spec Name: '
1416 || get_object_rec.object_name
1417 || ', Version: '
1418 || get_object_rec.object_vers);
1419 ELSIF pobject_type = 'VALIDITY' THEN
1420 default_log ( 'No Replacement for Validity Rules of Spec Name: '
1421 || get_object_rec.object_name
1422 || ', Version: '
1423 || get_object_rec.object_vers);
1424 END IF;
1425
1426 DEBUG ('sqlerror ' || SUBSTRB (SQLERRM, 1, 100));
1427 default_log ('sqlerror ' || SUBSTRB (SQLERRM, 1, 100));
1428 ROLLBACK TO SAVEPOINT mass_replace_for_one;
1429 DEBUG ('Rollback to the savepoint');
1430 END; -- End created to handle exception for each record
1431 END LOOP; -- For all rows that needs to be replaced
1432
1433 -- If MSNR was successful until here then
1434 -- Delete rows specific to this concurrent id
1435 IF (pconcurrent_id IS NOT NULL) THEN
1436 DELETE FROM gmd_msnr_results
1437 WHERE concurrent_id = TO_NUMBER (pconcurrent_id);
1438 COMMIT;
1439 END IF;
1440
1441 -- There were no row selected for replace raise an error
1442 IF (l_rowcount = 0) THEN
1443 fnd_message.set_name ('GMD', 'GMD_CONC_NO_ROW_FOUND');
1444 RAISE NO_REPLACE_EXCEPTION;
1445 END IF;
1446
1447 DEBUG ( g_pkg_name
1448 || '.'
1449 || l_api_name
1450 || 'Completed '
1451 || l_api_name
1452 || ' at '
1453 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
1454 EXCEPTION
1455 -- this exception occurs when no rows were selected for update.
1456 WHEN NO_REPLACE_EXCEPTION THEN
1457 fnd_msg_pub.get (p_msg_index => 1
1458 , p_data => l_error_text
1459 , p_encoded => 'F'
1460 , p_msg_index_out => l_dummy_cnt
1461 );
1462 --ret_code := 2;
1463 --err_buf := NULL;
1464 l_retval := fnd_concurrent.set_completion_status ('WARNING', l_error_text);
1465
1466 IF (l_debug = 'Y') THEN
1467 gmd_debug.put_line ( g_pkg_name
1468 || '.'
1469 || l_api_name
1470 || ' : In the No_replace_exception section '
1471 || ' Error text is '
1472 || l_error_text);
1473 END IF;
1474
1475 fnd_file.put (fnd_file.LOG, fnd_message.get);
1476 fnd_file.new_line (fnd_file.LOG, 1);
1477 -- outer excepption handles all error that occur prior to or after
1478 -- Mass updates (or within LOOP above)
1479 WHEN OTHERS THEN
1480 --ret_code := 2;
1481 --err_buf := NULL;
1482 l_retval := fnd_concurrent.set_completion_status ('WARNING', SQLERRM);
1483 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1484 fnd_message.set_name ('GMD', 'GMD_CONC_UPDATE_OBJECT_FAILED');
1485 fnd_message.set_token ('REPLACE_TYPE', preplace_type);
1486
1487 IF (preplace_type = 'INGREDIENT') THEN
1488 fnd_message.set_token ('REPLACE_VALUE', pnew_name);
1489 ELSE
1490 fnd_message.set_token ('REPLACE_VALUE', pnew_name);
1491 END IF;
1492
1493 fnd_message.set_token ('OBJECT_TYPE', pobject_type);
1494 fnd_message.set_token ('ERRMSG', SQLERRM);
1495 fnd_file.put (fnd_file.LOG, fnd_message.get);
1496 fnd_file.new_line (fnd_file.LOG, 1);
1497 END mass_replace_oper_spec_val;
1498
1499 /* PROCEDURE default_spectest_from_test
1500 This procedure default the spec_test record from the test user wants to add
1501 */
1502 PROCEDURE default_spectest_from_test (
1503 p_spec_id IN NUMBER
1504 , p_test_name IN VARCHAR2
1505 , poptional_ind IN VARCHAR2 DEFAULT NULL
1506 , pprint_spec_ind IN VARCHAR2 DEFAULT NULL
1507 , pprint_result_ind IN VARCHAR2 DEFAULT NULL
1508 , ptarget_value IN VARCHAR2 DEFAULT NULL
1509 , ptarget_min IN VARCHAR2 DEFAULT NULL
1510 , ptarget_max IN VARCHAR2 DEFAULT NULL
1511 , preport_precision IN VARCHAR2 DEFAULT NULL
1512 , pstore_precision IN VARCHAR2 DEFAULT NULL
1513 , ptest_priority IN VARCHAR2 DEFAULT NULL
1514 , p_spec_test_rec IN OUT NOCOPY gmd_spec_tests_b%ROWTYPE
1515 , x_return_status OUT NOCOPY VARCHAR2
1516 ) IS
1517 l_gmd_test_rec gmd_qc_tests_b%ROWTYPE;
1518 l_spec_test_rec gmd_spec_tests_b%ROWTYPE;
1519 l_test_values_rec gmd_qm_conc_replace_pkg.test_values;
1520 l_seq NUMBER;
1521
1522 CURSOR get_gmd_test (p_test_name IN VARCHAR2) IS
1523 SELECT *
1524 FROM gmd_qc_tests_b
1525 WHERE test_code = p_test_name;
1526
1527 -- Bug# 5882074 get the char test values
1528 CURSOR get_char_test_values (p_test_id IN VARCHAR2, p_num_value IN NUMBER) IS
1529 SELECT value_char
1530 FROM gmd_qc_test_values
1531 WHERE test_id = p_test_id
1532 AND text_range_seq = p_num_value;
1533
1534
1535 --Begin smalluru Bug#6415285
1536 found Number;
1537 l_value_char VARCHAR2(16);
1538 l_text_range_seq NUMBER;
1539 l_seq_min NUMBER :=0;
1540 l_seq_max NUMBER :=0;
1541 l_seq_target NUMBER :=0;
1542
1543 Cursor List_of_values_tests Is
1544 select VALUE_CHAR from gmd_qc_test_values
1545 where test_id = l_gmd_test_rec.test_id;
1546
1547 Cursor text_range_tests Is
1548 select text_range_seq, VALUE_CHAR from gmd_qc_test_values
1549 where test_id = l_gmd_test_rec.test_id;
1550
1551 --End smalluru Bug#6415285
1552
1553 BEGIN
1554 x_return_status := fnd_api.g_ret_sts_success;
1555
1556 OPEN get_gmd_test (p_test_name);
1557 FETCH get_gmd_test INTO l_gmd_test_rec;
1558 CLOSE get_gmd_test;
1559
1560 /* init the l_test_values_rec */
1561 l_test_values_rec.optional_ind := NULL;
1562 l_test_values_rec.print_spec_ind := NULL;
1563 l_test_values_rec.print_result_ind := NULL;
1564 l_test_values_rec.target_value_num := NULL;
1565 l_test_values_rec.target_value_char := NULL;
1566 l_test_values_rec.max_value_char := NULL;
1567 l_test_values_rec.min_value_char := NULL;
1568
1569 DEBUG ('Default test values from test :' || p_test_name);
1570 l_test_values_rec.max_value_num := l_gmd_test_rec.max_value_num;
1571 l_test_values_rec.min_value_num := l_gmd_test_rec.min_value_num;
1572 l_test_values_rec.report_precision := l_gmd_test_rec.report_precision;
1573 l_test_values_rec.store_precision := l_gmd_test_rec.display_precision;
1574 l_test_values_rec.test_priority := l_gmd_test_rec.priority;
1575
1576 -- Bug# 5882074 get the char values.
1577
1578 IF l_gmd_test_rec.test_type = 'T' THEN
1579 OPEN get_char_test_values(l_gmd_test_rec.test_id, l_gmd_test_rec.max_value_num);
1580 FETCH get_char_test_values INTO l_test_values_rec.max_value_char;
1581 CLOSE get_char_test_values;
1582
1583 OPEN get_char_test_values(l_gmd_test_rec.test_id, l_gmd_test_rec.min_value_num);
1584 FETCH get_char_test_values INTO l_test_values_rec.min_value_char;
1585 CLOSE get_char_test_values;
1586 END IF;
1587
1588 /* replace with the value defined on the screen */
1589 set_test_values (p_gmd_test_rec => l_gmd_test_rec
1590 , poptional_ind => poptional_ind
1591 , pprint_spec_ind => pprint_spec_ind
1592 , pprint_result_ind => pprint_result_ind
1593 , ptarget_value => ptarget_value
1594 , ptarget_min => ptarget_min
1595 , ptarget_max => ptarget_max
1596 , preport_precision => preport_precision
1597 , pstore_precision => pstore_precision
1598 , ptest_priority => ptest_priority
1599 , x_return_status => x_return_status
1600 , x_test_values_rec => l_test_values_rec
1601 );
1602
1603 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1604 RAISE fnd_api.g_exc_error;
1605 END IF;
1606
1607 -- Begin smalluru Bug#6415285.
1608 -- Validating the values entered in target, min and max fields
1609 If l_gmd_test_rec.test_type in ('N','E','L') Then
1610 If (nvl(l_test_values_rec.max_value_num,0) > nvl(l_gmd_test_rec.max_value_num,0) )
1611 OR (nvl(l_test_values_rec.min_value_num,0) < nvl(l_gmd_test_rec.min_value_num,0) )
1612 OR (nvl(l_test_values_rec.min_value_num,0) > nvl(l_gmd_test_rec.max_value_num,0) )
1613 OR (nvl(l_test_values_rec.target_value_num,0) NOT BETWEEN
1614 nvl(l_test_values_rec.min_value_num,0) AND nvl(l_test_values_rec.max_value_num,0)) Then
1615 -- raise an error for this record, abort!
1616 default_log(' Target values are out of range, '
1617 ||' Target Value: '||l_test_values_rec.target_value_num
1618 ||' Target Max: '||l_test_values_rec.max_value_num
1619 ||' Target Min: '||l_test_values_rec.min_value_num);
1620 raise FND_API.G_EXC_ERROR;
1621 End if;
1622 Elsif l_gmd_test_rec.test_type = 'T' then
1623
1624 open text_range_tests;
1625 LOOP
1626 fetch text_range_tests into l_text_range_seq,l_value_char;
1627 Exit when text_range_tests%NOTFOUND;
1628 if l_test_values_rec.min_value_char IS NULL then
1629 l_test_values_rec.min_value_char := l_value_char;
1630 l_seq_min := l_text_range_seq;
1631 end if;
1632 if upper(l_test_values_rec.min_value_char) = upper(l_value_char) then
1633 l_seq_min := l_text_range_seq;
1634 elsif upper(l_test_values_rec.max_value_char) = upper(l_value_char) then
1635 l_seq_max := l_text_range_seq;
1636 elsif upper(l_test_values_rec.target_value_char) = upper(l_value_char) then
1637 l_seq_target := l_text_range_seq;
1638 end if;
1639 END LOOP;
1640
1641 if text_range_tests%IsOpen then
1642 close text_range_tests;
1643 end if;
1644 if l_test_values_rec.max_value_char IS NULL then
1645 l_test_values_rec.max_value_char := l_value_char;
1646 l_seq_max := l_text_range_seq;
1647 end if;
1648
1649 if l_seq_max < l_seq_min OR (l_test_values_rec.target_value_char IS NOT NULL
1650 AND l_seq_target NOT BETWEEN l_seq_min AND l_seq_max) OR (l_seq_max =0 AND
1651 l_test_values_rec.max_value_char IS NOT NULL) OR (l_seq_min =0 AND
1652 l_test_values_rec.min_value_char IS NOT NULL) OR (l_seq_target =0 AND
1653 l_test_values_rec.target_value_char IS NOT NULL) then
1654
1655 default_log(' Target values are out of range, '
1656 ||' Target Value: '||l_test_values_rec.target_value_char
1657 ||' Target Max: '||l_test_values_rec.max_value_char
1658 ||' Target Min: '||l_test_values_rec.min_value_char);
1659 raise FND_API.G_EXC_ERROR;
1660 end if;
1661
1662 Elsif l_gmd_test_rec.test_type = 'V' Then
1663 If l_test_values_rec.target_value_char is null Then
1664 --Target is required for List of Values Tests.
1665 default_log(' Target value should not be NULL, '
1666 ||' Target Value: '||l_test_values_rec.target_value_char);
1667 raise FND_API.G_EXC_ERROR;
1668 else
1669 found:=0;
1670 Open List_of_values_tests;
1671 LOOP
1672 fetch List_of_values_tests into l_value_char;
1673 Exit when List_of_values_tests%NOTFOUND;
1674 If (upper(l_test_values_rec.target_value_char) = upper(l_value_char)) Then
1675 found := 1;
1676 close List_of_values_tests;
1677 EXIT;
1678 End if;
1679 END LOOP;
1680 If (List_of_values_tests%IsOpen ) Then
1681 Close List_of_values_tests;
1682 End If;
1683 If found = 0 Then
1684 --Target value is not there in the List of Values
1685 default_log(' Target value is out of range, '
1686 ||' Target Value: '||l_test_values_rec.target_value_char);
1687 raise FND_API.G_EXC_ERROR;
1688 End If;
1689
1690 End if;
1691 End if;
1692 --End smalluru Bug#6415285.
1693
1694 l_seq := 0;
1695 SELECT MAX (seq) + 10
1696 INTO l_seq
1697 FROM gmd_spec_tests_b
1698 WHERE spec_id = p_spec_id;
1699
1700 l_spec_test_rec.min_value_char := l_test_values_rec.min_value_char;
1701 l_spec_test_rec.max_value_char := l_test_values_rec.max_value_char;
1702 l_spec_test_rec.target_value_char := l_test_values_rec.target_value_char;
1703 l_spec_test_rec.min_value_num := l_test_values_rec.min_value_num;
1704 l_spec_test_rec.max_value_num := l_test_values_rec.max_value_num;
1705 l_spec_test_rec.target_value_num := l_test_values_rec.target_value_num;
1706 l_spec_test_rec.optional_ind := l_test_values_rec.optional_ind;
1707 l_spec_test_rec.test_priority := l_test_values_rec.test_priority; -- Bug# 5882074
1708 l_spec_test_rec.print_spec_ind := l_test_values_rec.print_spec_ind;
1709 l_spec_test_rec.print_result_ind := l_test_values_rec.print_result_ind;
1710 l_spec_test_rec.display_precision := l_test_values_rec.store_precision;
1711 l_spec_test_rec.report_precision := l_test_values_rec.report_precision;
1712 l_spec_test_rec.spec_id := p_spec_id;
1713 l_spec_test_rec.test_id := l_gmd_test_rec.test_id;
1714 l_spec_test_rec.test_method_id := l_gmd_test_rec.test_method_id;
1715 l_spec_test_rec.seq := l_seq;
1716 l_spec_test_rec.attribute_category := l_gmd_test_rec.attribute_category;
1717 l_spec_test_rec.attribute1 := l_gmd_test_rec.attribute1;
1718 l_spec_test_rec.attribute2 := l_gmd_test_rec.attribute2;
1719 l_spec_test_rec.attribute3 := l_gmd_test_rec.attribute3;
1720 l_spec_test_rec.attribute4 := l_gmd_test_rec.attribute4;
1721 l_spec_test_rec.attribute5 := l_gmd_test_rec.attribute5;
1722 l_spec_test_rec.attribute6 := l_gmd_test_rec.attribute6;
1723 l_spec_test_rec.attribute7 := l_gmd_test_rec.attribute7;
1724 l_spec_test_rec.attribute8 := l_gmd_test_rec.attribute8;
1725 l_spec_test_rec.attribute9 := l_gmd_test_rec.attribute9;
1726 l_spec_test_rec.attribute10 := l_gmd_test_rec.attribute10;
1727 l_spec_test_rec.attribute11 := l_gmd_test_rec.attribute11;
1728 l_spec_test_rec.attribute12 := l_gmd_test_rec.attribute12;
1729 l_spec_test_rec.attribute13 := l_gmd_test_rec.attribute13;
1730 l_spec_test_rec.attribute14 := l_gmd_test_rec.attribute14;
1731 l_spec_test_rec.attribute15 := l_gmd_test_rec.attribute15;
1732 l_spec_test_rec.attribute16 := l_gmd_test_rec.attribute16;
1733 l_spec_test_rec.attribute17 := l_gmd_test_rec.attribute17;
1734 l_spec_test_rec.attribute18 := l_gmd_test_rec.attribute18;
1735 l_spec_test_rec.attribute19 := l_gmd_test_rec.attribute19;
1736 l_spec_test_rec.attribute20 := l_gmd_test_rec.attribute20;
1737 l_spec_test_rec.attribute21 := l_gmd_test_rec.attribute21;
1738 l_spec_test_rec.attribute22 := l_gmd_test_rec.attribute22;
1739 l_spec_test_rec.attribute23 := l_gmd_test_rec.attribute23;
1740 l_spec_test_rec.attribute24 := l_gmd_test_rec.attribute24;
1741 l_spec_test_rec.attribute25 := l_gmd_test_rec.attribute25;
1742 l_spec_test_rec.attribute26 := l_gmd_test_rec.attribute26;
1743 l_spec_test_rec.attribute27 := l_gmd_test_rec.attribute27;
1744 l_spec_test_rec.attribute28 := l_gmd_test_rec.attribute28;
1745 l_spec_test_rec.attribute29 := l_gmd_test_rec.attribute29;
1746 l_spec_test_rec.attribute30 := l_gmd_test_rec.attribute30;
1747 l_spec_test_rec.text_code := l_gmd_test_rec.text_code;
1748 l_spec_test_rec.test_replicate := 1;
1749 l_spec_test_rec.exp_error_type := l_gmd_test_rec.exp_error_type;
1750 l_spec_test_rec.below_spec_min := l_gmd_test_rec.below_spec_min;
1751 l_spec_test_rec.above_spec_min := l_gmd_test_rec.above_spec_min;
1752 l_spec_test_rec.below_spec_max := l_gmd_test_rec.below_spec_max;
1753 l_spec_test_rec.above_spec_max := l_gmd_test_rec.above_spec_max;
1754 l_spec_test_rec.below_min_action_code := l_gmd_test_rec.below_min_action_code;
1755 l_spec_test_rec.above_min_action_code := l_gmd_test_rec.above_min_action_code;
1756 l_spec_test_rec.below_max_action_code := l_gmd_test_rec.below_max_action_code;
1757 l_spec_test_rec.above_max_action_code := l_gmd_test_rec.above_max_action_code;
1758 --l_spec_test_rec.test_priority := l_gmd_test_rec.priority; -- Bug# 5882074
1759 l_spec_test_rec.check_result_interval := NULL;
1760 l_spec_test_rec.out_of_spec_action := NULL;
1761 l_spec_test_rec.use_to_control_step := NULL;
1762 l_spec_test_rec.retest_lot_expiry_ind := NULL;
1763 l_spec_test_rec.print_on_coa_ind := NULL;
1764 --l_spec_test_rec.test_display := null;
1765 l_spec_test_rec.from_base_ind := NULL;
1766 l_spec_test_rec.exclude_ind := NULL;
1767 l_spec_test_rec.modified_ind := NULL;
1768 l_spec_test_rec.test_qty := NULL;
1769 l_spec_test_rec.test_qty_uom := NULL; -- Bug# 5882074 Changed test_uom to test_qty_uom
1770 l_spec_test_rec.creation_date := NULL;
1771 l_spec_test_rec.created_by := NULL;
1772 l_spec_test_rec.last_update_date := NULL;
1773 l_spec_test_rec.last_updated_by := NULL;
1774 l_spec_test_rec.last_update_login := NULL;
1775 l_spec_test_rec.viability_duration := NULL;
1776 l_spec_test_rec.days := NULL;
1777 l_spec_test_rec.hours := NULL;
1778 l_spec_test_rec.minutes := NULL;
1779 l_spec_test_rec.seconds := NULL;
1780 l_spec_test_rec.calc_uom_conv_ind := NULL;
1781 l_spec_test_rec.to_qty_uom := NULL; -- Bug# 5882074 changed to_uom to to_qty_uom
1782 p_spec_test_rec := l_spec_test_rec;
1783 EXCEPTION
1784 WHEN fnd_api.g_exc_error THEN
1785 x_return_status := fnd_api.g_ret_sts_unexp_error;
1786 default_log ('Default spectest from test result in Error');
1787 WHEN OTHERS THEN
1788 x_return_status := fnd_api.g_ret_sts_unexp_error;
1789 default_log ('Default spectest from test result in Error');
1790 END default_spectest_from_test;
1791
1792 PROCEDURE default_spectest_from_spectest (
1793 p_from_spec_id IN NUMBER
1794 , p_from_test_id IN NUMBER
1795 , p_to_test_name IN VARCHAR2
1796 , poptional_ind IN VARCHAR2 DEFAULT NULL
1797 , pprint_spec_ind IN VARCHAR2 DEFAULT NULL
1798 , pprint_result_ind IN VARCHAR2 DEFAULT NULL
1799 , ptarget_value IN VARCHAR2 DEFAULT NULL
1800 , ptarget_min IN VARCHAR2 DEFAULT NULL
1801 , ptarget_max IN VARCHAR2 DEFAULT NULL
1802 , preport_precision IN VARCHAR2 DEFAULT NULL
1803 , pstore_precision IN VARCHAR2 DEFAULT NULL
1804 , ptest_priority IN VARCHAR2 DEFAULT NULL
1805 , p_spec_test_rec IN OUT NOCOPY gmd_spec_tests_b%ROWTYPE
1806 , x_return_status OUT NOCOPY VARCHAR2
1807 ) IS
1808 l_from_spec_test_rec gmd_spec_tests_b%ROWTYPE;
1809 l_to_spec_test_rec gmd_spec_tests_b%ROWTYPE;
1810 l_gmd_test_rec gmd_qc_tests_b%ROWTYPE;
1811 l_test_values_rec gmd_qm_conc_replace_pkg.test_values;
1812 l_row_id VARCHAR2 (200);
1813
1814 CURSOR get_spec_test_rec (p_spec_id IN NUMBER, p_test_id IN NUMBER) IS
1815 SELECT *
1816 FROM gmd_spec_tests_b
1817 WHERE spec_id = p_spec_id
1818 AND test_id = p_test_id;
1819
1820 CURSOR get_test_rec (p_test_name IN VARCHAR2) IS
1821 SELECT *
1822 FROM gmd_qc_tests_b
1823 WHERE test_code = p_test_name;
1824 BEGIN
1825 x_return_status := fnd_api.g_ret_sts_success;
1826
1827 OPEN get_spec_test_rec (p_from_spec_id, p_from_test_id);
1828 FETCH get_spec_test_rec INTO l_from_spec_test_rec;
1829 CLOSE get_spec_test_rec;
1830
1831 DEBUG ( 'In default from spectest , load from spec_test_rec, spec_id '
1832 || l_from_spec_test_rec.spec_id
1833 || ' test_id '
1834 || l_from_spec_test_rec.test_id);
1835
1836 OPEN get_test_rec (p_to_test_name);
1837 FETCH get_test_rec INTO l_gmd_test_rec;
1838 CLOSE get_test_rec;
1839
1840 DEBUG ( 'In default from spectest , load test_rec '
1841 || l_gmd_test_rec.test_code
1842 || ' test_id:'
1843 || l_gmd_test_rec.test_id);
1844
1845 init_test_values_rec (
1846 p_spec_tests_rec => l_from_spec_test_rec
1847 , x_test_values_rec => l_test_values_rec);
1848
1849 DEBUG ('default from spectest , after init test values');
1850 /* replace with the value defined on the screen */
1851 set_test_values (p_gmd_test_rec => l_gmd_test_rec
1852 , poptional_ind => poptional_ind
1853 , pprint_spec_ind => pprint_spec_ind
1854 , pprint_result_ind => pprint_result_ind
1855 , ptarget_value => ptarget_value
1856 , ptarget_min => ptarget_min
1857 , ptarget_max => ptarget_max
1858 , preport_precision => preport_precision
1859 , pstore_precision => pstore_precision
1860 , ptest_priority => ptest_priority
1861 , x_return_status => x_return_status
1862 , x_test_values_rec => l_test_values_rec
1863 );
1864
1865 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1866 RAISE fnd_api.g_exc_error;
1867 END IF;
1868
1869 DEBUG ('default from spectest , after set test values');
1870 l_to_spec_test_rec := l_from_spec_test_rec;
1871 l_to_spec_test_rec.min_value_char := l_test_values_rec.min_value_char;
1872 l_to_spec_test_rec.min_value_num := l_test_values_rec.min_value_num;
1873 l_to_spec_test_rec.target_value_num := l_test_values_rec.target_value_num;
1874 l_to_spec_test_rec.max_value_num := l_test_values_rec.max_value_num;
1875 l_to_spec_test_rec.print_spec_ind := l_test_values_rec.print_spec_ind;
1876 l_to_spec_test_rec.print_result_ind := l_test_values_rec.print_result_ind;
1877 l_to_spec_test_rec.max_value_char := l_test_values_rec.max_value_char;
1878 l_to_spec_test_rec.optional_ind := l_test_values_rec.optional_ind;
1879 l_to_spec_test_rec.display_precision := l_test_values_rec.store_precision;
1880 l_to_spec_test_rec.report_precision := l_test_values_rec.report_precision;
1881 l_to_spec_test_rec.target_value_char := l_test_values_rec.target_value_char;
1882 l_to_spec_test_rec.test_id := l_gmd_test_rec.test_id;
1883 p_spec_test_rec := l_to_spec_test_rec;
1884 EXCEPTION
1885 WHEN fnd_api.g_exc_error THEN
1886 x_return_status := fnd_api.g_ret_sts_unexp_error;
1887 default_log ('Default spectest from spectest result in Error');
1888 WHEN OTHERS THEN
1889 x_return_status := fnd_api.g_ret_sts_unexp_error;
1890 default_log ('Default spectest from spectest result in Error');
1891 END default_spectest_from_spectest;
1892
1893 PROCEDURE set_test_values (
1894 p_gmd_test_rec IN gmd_qc_tests_b%ROWTYPE
1895 , poptional_ind IN VARCHAR2 DEFAULT NULL
1896 , pprint_spec_ind IN VARCHAR2 DEFAULT NULL
1897 , pprint_result_ind IN VARCHAR2 DEFAULT NULL
1898 , ptarget_value IN VARCHAR2 DEFAULT NULL
1899 , ptarget_min IN VARCHAR2 DEFAULT NULL
1900 , ptarget_max IN VARCHAR2 DEFAULT NULL
1901 , preport_precision IN VARCHAR2 DEFAULT NULL
1902 , pstore_precision IN VARCHAR2 DEFAULT NULL
1903 , ptest_priority IN VARCHAR2 DEFAULT NULL
1904 , x_return_status OUT NOCOPY VARCHAR2
1905 , x_test_values_rec IN OUT NOCOPY gmd_qm_conc_replace_pkg.test_values
1906 ) IS
1907 l_optional_ind gmd_spec_tests_b.optional_ind%TYPE;
1908 l_print_spec_ind gmd_spec_tests_b.print_spec_ind%TYPE;
1909 l_print_result_ind gmd_spec_tests_b.print_result_ind%TYPE;
1910 l_target_value_num gmd_spec_tests_b.target_value_num%TYPE;
1911 l_target_value_char gmd_spec_tests_b.target_value_char%TYPE;
1912 l_min_value_num gmd_spec_tests_b.min_value_num%TYPE;
1913 l_min_value_char gmd_spec_tests_b.min_value_char%TYPE;
1914 l_max_value_num gmd_spec_tests_b.max_value_num%TYPE;
1915 l_max_value_char gmd_spec_tests_b.max_value_char%TYPE;
1916 l_report_precision gmd_spec_tests_b.report_precision%TYPE;
1917 l_store_precision gmd_spec_tests_b.display_precision%TYPE;
1918 l_test_priority gmd_spec_tests_b.test_priority%TYPE;
1919 l_gmd_test_rec gmd_qc_tests_b%ROWTYPE;
1920 l_test_values_rec gmd_qm_conc_replace_pkg.test_values;
1921
1922 l_precision VARCHAR2(50); --RLNAGARA Bug 6972284
1923 l_min_max_target NUMBER; --RLNAGARA Bug 6972284
1924
1925 --Bug#6415285 commented the below cursor
1926 -- Bug# 5882074 get the char test values
1927 /* CURSOR get_char_test_values (p_test_id IN VARCHAR2, p_num_value IN NUMBER) IS
1928 SELECT value_char
1929 FROM gmd_qc_test_values
1930 WHERE test_id = p_test_id
1931 AND text_range_seq = p_num_value;
1932 */
1933
1934
1935 BEGIN
1936 x_return_status := fnd_api.g_ret_sts_success;
1937 /* lookup code for test type GMD_QC_TEST_DATE_TYPE
1938 * U -- Non-Validated
1939 * N -- Numeric Range
1940 * V -- List of Test Values
1941 * T -- Text Range
1942 * L -- Numeric Range with Display Text
1943 * E -- Expression
1944 */
1945 l_test_values_rec := x_test_values_rec;
1946 l_gmd_test_rec := p_gmd_test_rec;
1947 l_optional_ind := l_test_values_rec.optional_ind;
1948 l_print_spec_ind := l_test_values_rec.print_spec_ind;
1949 l_print_result_ind := l_test_values_rec.print_result_ind;
1950 l_report_precision := l_test_values_rec.report_precision;
1951 l_store_precision := l_test_values_rec.store_precision;
1952 l_target_value_num := l_test_values_rec.target_value_num;
1953 l_max_value_num := l_test_values_rec.max_value_num;
1954 l_min_value_num := l_test_values_rec.min_value_num;
1955 l_target_value_char := l_test_values_rec.target_value_char;
1956 l_max_value_char := l_test_values_rec.max_value_char;
1957 l_min_value_char := l_test_values_rec.min_value_char;
1958 l_test_priority := l_test_values_rec.test_priority;
1959 DEBUG (' Procecure Set_test_values');
1960 DEBUG (' test_rec.test_type ' || l_gmd_test_rec.test_type);
1961
1962 IF l_gmd_test_rec.test_type IN ('N', 'L', 'E') THEN
1963 IF ptarget_value IS NOT NULL THEN
1964 l_target_value_num := TO_NUMBER (ptarget_value);
1965 END IF;
1966
1967 IF ptarget_max IS NOT NULL THEN
1968 l_max_value_num := TO_NUMBER (ptarget_max);
1969 END IF;
1970
1971 IF ptarget_max IS NOT NULL THEN
1972 l_min_value_num := TO_NUMBER (ptarget_min);
1973 END IF;
1974
1975 l_target_value_char := NULL;
1976 l_max_value_char := NULL;
1977 l_min_value_char := NULL;
1978 -- Bug# 5882074 changed the following condition
1979 --ELSIF l_gmd_test_rec.test_type = 'V' THEN
1980 ELSIF l_gmd_test_rec.test_type in ('V', 'U') THEN
1981 l_target_value_num := NULL;
1982 l_max_value_num := NULL;
1983 l_min_value_num := NULL;
1984
1985 IF ptarget_value IS NOT NULL THEN
1986 l_target_value_char := ptarget_value;
1987 END IF;
1988
1989 l_max_value_char := NULL;
1990 l_min_value_char := NULL;
1991
1992 --Bug#6415285 Commented the below code as ptarget_value,ptarget_max and ptarget_min come as character values
1993 -- for Text range (T) type tests and no need to fetch them separately.
1994
1995 -- Bug 5882074 Added the following elseif and commented the else
1996 /* ELSIF l_gmd_test_rec.test_type = 'T' THEN
1997 debug('sup ptarget_value'||ptarget_value);
1998 IF ptarget_value IS NOT NULL THEN
1999
2000 l_target_value_num := TO_NUMBER (ptarget_value);
2001 OPEN get_char_test_values(l_gmd_test_rec.test_id, l_target_value_num);
2002 FETCH get_char_test_values INTO l_target_value_char;
2003 CLOSE get_char_test_values;
2004 END IF;
2005 debug('sup ptarget_max'||ptarget_max);
2006 IF ptarget_max IS NOT NULL THEN
2007 l_max_value_num := TO_NUMBER (ptarget_max);
2008 OPEN get_char_test_values(l_gmd_test_rec.test_id, l_max_value_num);
2009 FETCH get_char_test_values INTO l_max_value_char;
2010 CLOSE get_char_test_values;
2011 END IF;
2012 debug('sup ptarget_min'||ptarget_min);
2013 IF ptarget_min IS NOT NULL THEN --replaced target with min
2014 l_min_value_num := TO_NUMBER (ptarget_min);
2015 OPEN get_char_test_values(l_gmd_test_rec.test_id, l_min_value_num);
2016 FETCH get_char_test_values INTO l_min_value_char;
2017 CLOSE get_char_test_values;
2018 END IF; */
2019 ELSE
2020 IF ptarget_value IS NOT NULL THEN
2021 l_target_value_char := ptarget_value;
2022 END IF;
2023
2024 IF ptarget_max IS NOT NULL THEN
2025 l_max_value_char := ptarget_max;
2026 END IF;
2027
2028 IF ptarget_min IS NOT NULL THEN
2029 l_min_value_char := ptarget_min;
2030 END IF;
2031
2032 l_target_value_num := NULL;
2033 l_max_value_num := NULL;
2034 l_min_value_num := NULL;
2035 END IF;
2036
2037 IF poptional_ind IS NOT NULL THEN
2038 l_optional_ind := poptional_ind;
2039 END IF;
2040
2041 IF ( (poptional_ind IS NOT NULL)
2042 AND poptional_ind = 'N') THEN
2043 l_optional_ind := NULL;
2044 END IF;
2045
2046 IF pprint_spec_ind IS NOT NULL THEN
2047 l_print_spec_ind := pprint_spec_ind;
2048 END IF;
2049
2050 IF pprint_result_ind IS NOT NULL THEN
2051 l_print_result_ind := pprint_result_ind;
2052 END IF;
2053
2054 --RLNAGARA start Bug 6972284 validation of the test precision
2055 IF (preport_precision > pstore_precision) THEN
2056 default_log ( 'The Report Precision cannot be greater then the Display Precision.'
2057 || ' Report Precision: '
2058 || preport_precision
2059 || ' Display Precision: '
2060 || pstore_precision);
2061 RAISE fnd_api.g_exc_error;
2062 ELSIF (pstore_precision > l_store_precision) THEN
2063 default_log ( 'Display precision for Spec test cannot be greater than the display precision specified in the test definition.'
2064 || ' Display Precision in Spec test: '
2065 || pstore_precision
2066 || ' Display Precision in test: '
2067 || l_store_precision);
2068 RAISE fnd_api.g_exc_error;
2069 ELSIF (preport_precision > l_report_precision) THEN
2070 default_log ( 'Report precision for Spec test cannot be greater than the report precision specified in the test definition.'
2071 || ' Report Precision in Spec test: '
2072 || preport_precision
2073 || ' Report Precision in test: '
2074 || l_report_precision);
2075 RAISE fnd_api.g_exc_error;
2076 END IF;
2077
2078 --RLNAGARA end Bug 6972284
2079
2080 IF preport_precision IS NOT NULL THEN
2081 l_report_precision := preport_precision;
2082 END IF;
2083
2084 IF pstore_precision IS NOT NULL THEN
2085 l_store_precision := pstore_precision;
2086 END IF;
2087
2088 --RLNAGARA start Bug 6972284 After Validation set the precision to the min,max and target values.
2089 IF l_gmd_test_rec.test_type IN ('N', 'L', 'E') THEN
2090 IF (l_store_precision IS NOT NULL AND l_store_precision <> 0) THEN
2091 l_precision := '999999999999999D'||to_char(power(10,l_store_precision)-1);
2092 l_min_max_target := l_target_value_num;
2093 l_target_value_num :=ltrim(to_char(to_number(l_min_max_target),l_precision));
2094 l_min_max_target := l_min_value_num;
2095 l_min_value_num :=ltrim(to_char(to_number(l_min_max_target),l_precision));
2096 l_min_max_target := l_max_value_num;
2097 l_max_value_num :=ltrim(to_char(to_number(l_min_max_target),l_precision));
2098 END IF;
2099 END IF;
2100 --RLNAGARA end Bug 6972284
2101
2102 IF ptest_priority IS NOT NULL THEN
2103 l_test_priority := ptest_priority;
2104 END IF;
2105
2106 l_test_values_rec.optional_ind := l_optional_ind;
2107 l_test_values_rec.print_spec_ind := l_print_spec_ind;
2108 l_test_values_rec.print_result_ind := l_print_result_ind;
2109 l_test_values_rec.report_precision := l_report_precision;
2110 l_test_values_rec.store_precision := l_store_precision;
2111 l_test_values_rec.target_value_num := l_target_value_num;
2112 l_test_values_rec.max_value_num := l_max_value_num;
2113 l_test_values_rec.min_value_num := l_min_value_num;
2114 l_test_values_rec.target_value_char := l_target_value_char;
2115 l_test_values_rec.max_value_char := l_max_value_char;
2116 l_test_values_rec.min_value_char := l_min_value_char;
2117 l_test_values_rec.test_priority := l_test_priority;
2118
2119 /* need to validate the min max for the test, no changes made if exceeds the max*/
2120 -- Bug# 5882074 Added additional if condition to check for target_value_num and changed the existing if
2121 IF l_test_values_rec.target_value_num IS NOT NULL THEN
2122 --IF (NVL (l_test_values_rec.target_value_num, 0) > NVL (l_test_values_rec.max_value_num, 0))
2123 -- OR (NVL (l_test_values_rec.target_value_num, 0) < NVL (l_test_values_rec.min_value_num, 0)) THEN
2124 IF l_test_values_rec.target_value_num > l_test_values_rec.max_value_num
2125 OR l_test_values_rec.target_value_num < l_test_values_rec.min_value_num THEN
2126 -- raise an error for this record, abort!
2127 default_log ( ' Target value is out of range '
2128 || ' Target Value: '
2129 || l_test_values_rec.target_value_num
2130 || ' Target Max: '
2131 || l_test_values_rec.max_value_num
2132 || ' Target Min: '
2133 || l_test_values_rec.min_value_num);
2134 RAISE fnd_api.g_exc_error;
2135 END IF;
2136 END IF;
2137
2138 IF NVL (l_test_values_rec.min_value_num, 0) > NVL (l_test_values_rec.max_value_num, 0) THEN
2139 -- raise an error for this record, abort!
2140 default_log ( ' Target min value is out of range '
2141 || ' Target Value: '
2142 || l_test_values_rec.target_value_num
2143 || ' Target Max: '
2144 || l_test_values_rec.max_value_num
2145 || ' Target Min: '
2146 || l_test_values_rec.min_value_num);
2147 RAISE fnd_api.g_exc_error;
2148 END IF;
2149
2150 x_test_values_rec := l_test_values_rec;
2151 EXCEPTION
2152 WHEN fnd_api.g_exc_error THEN
2153 x_return_status := fnd_api.g_ret_sts_unexp_error;
2154 default_log ('Set Test Values result in Error');
2155 WHEN OTHERS THEN
2156 x_return_status := fnd_api.g_ret_sts_unexp_error;
2157 default_log ('Set Test Values result in Error');
2158 END set_test_values;
2159
2160 /*###############################################################
2161 # NAME
2162 # create_new_specification
2163 # SYNOPSIS
2164 # create_new_specification
2165 # DESCRIPTION
2166 # Create a row in gmd_specifications_b with the default (input)
2167 # values
2168 # p_action_code can be
2169 # -- 'NEWVERS', new version
2170 ###############################################################*/
2171 PROCEDURE create_new_specification (
2172 p_old_spec_id IN NUMBER
2173 , p_action_code IN VARCHAR2
2174 , x_new_spec_id OUT NOCOPY NUMBER
2175 , x_new_spec_vers OUT NOCOPY NUMBER
2176 , x_return_status OUT NOCOPY VARCHAR2
2177 ) IS
2178 l_old_spec_rec gmd_specifications%ROWTYPE;
2179 l_new_spec_rec gmd_specifications%ROWTYPE;
2180 l_version gmd_specifications.spec_vers%TYPE;
2181 l_spec_id gmd_specifications.spec_id%TYPE;
2182 l_default_spec_sts gmd_specifications.spec_status%TYPE;
2183 l_manage_vr_ind VARCHAR2 (1);
2184 l_row_id VARCHAR2 (200);
2185
2186 CURSOR get_spec_rec (p_spec_id IN NUMBER) IS
2187 SELECT *
2188 FROM gmd_specifications
2189 WHERE spec_id = p_spec_id;
2190
2191 -- Bug# 5882074 Changed orgn_code to orgn_id
2192 CURSOR get_default_status (p_orgn_id IN NUMBER) IS
2193 SELECT default_specification_status
2194 , manage_validity_rules_ind
2195 FROM gmd_quality_config
2196 WHERE organization_id = p_orgn_id;
2197
2198 CURSOR get_vrs_rec (p_spec_id IN NUMBER) IS
2199 SELECT spec_vr_status
2200 FROM gmd_all_spec_vrs_vl
2201 WHERE spec_id = p_spec_id;
2202 BEGIN
2203 x_return_status := fnd_api.g_ret_sts_success;
2204
2205 DEBUG ('In creat_new_specification, old_spec_id ' || p_old_spec_id);
2206 OPEN get_spec_rec (p_old_spec_id);
2207 FETCH get_spec_rec INTO l_old_spec_rec;
2208 CLOSE get_spec_rec;
2209
2210 DEBUG ('In creat_new_specification, spec_name ' || l_old_spec_rec.spec_name);
2211 l_default_spec_sts := NULL;
2212
2213 /* search for the configuration rules to default the status */
2214 /* lookup code for manage_validity_rules_ind
2215 * lookup type 'GMD_QM_MANAGING_VALIDITY_RULES'
2216 * lookup code 'C' -- Copy
2217 * 'E' -- Copy and Set End Date for Old Rules
2218 * 'O' -- Copy and Set Obsolete Status for Old Rules
2219 * 'Z' -- Do Not Create
2220 */
2221 -- Bug# 5882074 Changed code to id
2222 OPEN get_default_status (l_old_spec_rec.owner_organization_id);
2223 FETCH get_default_status INTO l_default_spec_sts, l_manage_vr_ind;
2224 CLOSE get_default_status;
2225
2226 IF l_default_spec_sts IS NULL THEN
2227 default_log (' Default Spec Status is not set to crete new specification');
2228 RAISE fnd_api.g_exc_error;
2229 END IF;
2230
2231 IF l_manage_vr_ind IN ('E', 'O')
2232 AND l_default_spec_sts <> 100 THEN
2233 FOR validity_rules IN get_vrs_rec (l_old_spec_rec.spec_id) LOOP
2234 IF validity_rules.spec_vr_status IN (200, 500) THEN
2235 default_log ( ' With the value of Manage Validity Rules, the Default Spec Status should NOT be '
2236 || l_default_spec_sts);
2237 RAISE fnd_api.g_exc_error;
2238 END IF;
2239 END LOOP;
2240 END IF;
2241
2242 l_new_spec_rec := l_old_spec_rec;
2243 l_new_spec_rec.spec_status := l_default_spec_sts;
2244 DEBUG ('In creat_new_specification, new_spec_name: ' || l_new_spec_rec.spec_name);
2245
2246 IF (p_action_code = 'NEWVERS') THEN -- create a new version
2247 SELECT MAX (spec_vers) + 1
2248 INTO l_new_spec_rec.spec_vers
2249 FROM gmd_specifications
2250 WHERE spec_name = l_old_spec_rec.spec_name;
2251 ELSE
2252 NULL;
2253 END IF;
2254
2255 insert_new_spec_rec (p_spec_rec => l_new_spec_rec
2256 , x_new_spec_id => x_new_spec_id
2257 , x_return_status => x_return_status
2258 );
2259
2260 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2261 RAISE fnd_api.g_exc_error;
2262 END IF;
2263
2264 x_new_spec_vers := l_new_spec_rec.spec_vers;
2265
2266 default_log ( ' New Specification Version created '
2267 || ' Spec Name: '
2268 || l_new_spec_rec.spec_name
2269 || ' Spec Version: '
2270 || l_new_spec_rec.spec_vers
2271 || ' Spec Id: '
2272 || x_new_spec_id);
2273 EXCEPTION
2274 WHEN fnd_api.g_exc_error THEN
2275 x_return_status := fnd_api.g_ret_sts_unexp_error;
2276 default_log ('Create New Specification result in Error');
2277 WHEN OTHERS THEN
2278 x_return_status := fnd_api.g_ret_sts_unexp_error;
2279 default_log ('Create New Specification result in Error');
2280 END create_new_specification;
2281
2282 PROCEDURE insert_new_spec_rec (
2283 p_spec_rec IN gmd_specifications%ROWTYPE
2284 , x_new_spec_id OUT NOCOPY NUMBER
2285 , x_return_status OUT NOCOPY VARCHAR2
2286 ) IS
2287 l_new_spec_rec gmd_specifications%ROWTYPE;
2288 l_spec_id gmd_specifications.spec_id%TYPE;
2289 l_row_id VARCHAR2 (200);
2290 BEGIN
2291 x_return_status := fnd_api.g_ret_sts_success;
2292 l_new_spec_rec := p_spec_rec;
2293 DEBUG ('Inserting new version of the spec: ' || l_new_spec_rec.spec_name);
2294 DEBUG ('Inserting new version of the spec ver: ' || l_new_spec_rec.spec_vers);
2295
2296 GMD_SPECIFICATIONS_PVT.INSERT_ROW
2297 ( x_rowid => l_row_id
2298 , x_spec_id => l_spec_id
2299 , x_spec_name => l_new_spec_rec.spec_name
2300 , x_spec_vers => l_new_spec_rec.spec_vers
2301 , x_inventory_item_id => l_new_spec_rec.inventory_item_id /* Bug# 5882074 */
2302 , x_revision => l_new_spec_rec.revision /* Bug# 5882074 */
2303 , x_grade_code => l_new_spec_rec.grade_code /* Bug# 5882074 */
2304 , x_spec_status => l_new_spec_rec.spec_status
2305 , x_overlay_ind => l_new_spec_rec.overlay_ind
2306 , x_spec_type => l_new_spec_rec.spec_type
2307 , x_base_spec_id => l_new_spec_rec.base_spec_id
2308 , x_owner_organization_id => l_new_spec_rec.owner_organization_id /* Bug# 5882074 */
2309 , x_owner_id => l_new_spec_rec.owner_id
2310 , x_sample_inv_trans_ind => l_new_spec_rec.sample_inv_trans_ind
2311 , x_delete_mark => l_new_spec_rec.delete_mark
2312 , x_text_code => l_new_spec_rec.text_code
2313 , x_attribute_category => l_new_spec_rec.attribute_category
2314 , x_attribute1 => l_new_spec_rec.attribute1
2315 , x_attribute2 => l_new_spec_rec.attribute2
2316 , x_attribute3 => l_new_spec_rec.attribute3
2317 , x_attribute4 => l_new_spec_rec.attribute4
2318 , x_attribute5 => l_new_spec_rec.attribute5
2319 , x_attribute6 => l_new_spec_rec.attribute6
2320 , x_attribute7 => l_new_spec_rec.attribute7
2321 , x_attribute8 => l_new_spec_rec.attribute8
2322 , x_attribute9 => l_new_spec_rec.attribute9
2323 , x_attribute10 => l_new_spec_rec.attribute10
2324 , x_attribute11 => l_new_spec_rec.attribute11
2325 , x_attribute12 => l_new_spec_rec.attribute12
2326 , x_attribute13 => l_new_spec_rec.attribute13
2327 , x_attribute14 => l_new_spec_rec.attribute14
2328 , x_attribute15 => l_new_spec_rec.attribute15
2329 , x_attribute16 => l_new_spec_rec.attribute16
2330 , x_attribute17 => l_new_spec_rec.attribute17
2331 , x_attribute18 => l_new_spec_rec.attribute18
2332 , x_attribute19 => l_new_spec_rec.attribute19
2333 , x_attribute20 => l_new_spec_rec.attribute20
2334 , x_attribute21 => l_new_spec_rec.attribute21
2335 , x_attribute22 => l_new_spec_rec.attribute22
2336 , x_attribute23 => l_new_spec_rec.attribute23
2337 , x_attribute24 => l_new_spec_rec.attribute24
2338 , x_attribute25 => l_new_spec_rec.attribute25
2339 , x_attribute26 => l_new_spec_rec.attribute26
2340 , x_attribute27 => l_new_spec_rec.attribute27
2341 , x_attribute28 => l_new_spec_rec.attribute28
2342 , x_attribute29 => l_new_spec_rec.attribute29
2343 , x_attribute30 => l_new_spec_rec.attribute30
2344 , x_spec_desc => l_new_spec_rec.spec_desc
2345 , x_creation_date => l_new_spec_rec.creation_date
2346 , x_created_by => l_new_spec_rec.created_by
2347 , x_last_update_date => l_new_spec_rec.last_update_date
2348 , x_last_updated_by => l_new_spec_rec.last_updated_by
2349 , x_last_update_login => l_new_spec_rec.last_update_login
2350 );
2351
2352 x_new_spec_id := l_spec_id;
2353 EXCEPTION
2354 WHEN OTHERS THEN
2355 x_return_status := fnd_api.g_ret_sts_unexp_error;
2356 default_log ('Insert New spec rec result in Error');
2357 END insert_new_spec_rec;
2358
2359 /* add spec_test record for the test with p_test_name to spec_id
2360 pNew_ind default 'N' = New, create spec_test taking default from this test (p_test_name)
2361 'C' = Copy, create spec_test taking default from old spec test
2362 */
2363 PROCEDURE add_spec_test_rec (
2364 p_spec_id IN NUMBER
2365 , p_test_name IN VARCHAR2
2366 , p_spec_name IN VARCHAR2
2367 , p_spec_test_rec IN OUT NOCOPY gmd_spec_tests_b%ROWTYPE
2368 , x_return_status OUT NOCOPY VARCHAR2
2369 ) IS
2370 l_spec_test_rec gmd_spec_tests_b%ROWTYPE;
2371 l_gmd_test_rec gmd_qc_tests_b%ROWTYPE;
2372 l_exist NUMBER;
2373 l_seq NUMBER;
2374 l_return_status VARCHAR2 (1);
2375 l_check BOOLEAN;
2376
2377 CURSOR get_gmd_test (p_test_name IN VARCHAR2) IS
2378 SELECT *
2379 FROM gmd_qc_tests_b
2380 WHERE test_code = p_test_name;
2381
2382 CURSOR check_spec_test (p_spec_id IN NUMBER, p_test_name IN VARCHAR2) IS
2383 SELECT 1
2384 FROM gmd_spec_tests_b sptst
2385 , gmd_qc_tests_b tst
2386 WHERE sptst.spec_id = p_spec_id
2387 AND sptst.test_id = tst.test_id
2388 AND tst.test_code = p_test_name;
2389
2390 CURSOR find_nonexp_spec_test (p_spec_id IN NUMBER) IS
2391 SELECT t.test_code
2392 , t.test_id
2393 , t.expression
2394 FROM gmd_spec_tests_b s
2395 , gmd_qc_tests_b t
2396 WHERE s.spec_id = p_spec_id
2397 AND s.test_id = t.test_id
2398 AND t.test_type <> 'E';
2399 BEGIN
2400 x_return_status := fnd_api.g_ret_sts_success;
2401 l_exist := 0;
2402 l_spec_test_rec := p_spec_test_rec;
2403 l_spec_test_rec.spec_id := p_spec_id;
2404
2405 DEBUG ('In Add_spec_test_rec, spec_id: ' || p_spec_id);
2406 DEBUG ('In Add_spec_test_rec, spec_name: ' || p_spec_name);
2407 DEBUG ('In Add_spec_test_rec, test_name: ' || p_test_name);
2408
2409 /* check to see if this test is an expression and tests with this expression exist in this spec*/
2410 OPEN get_gmd_test (p_test_name);
2411 FETCH get_gmd_test INTO l_gmd_test_rec;
2412 CLOSE get_gmd_test;
2413
2414 IF l_gmd_test_rec.test_type = 'E' THEN -- it is an expression
2415 DEBUG ( 'Test to be added is an expression type, Test Name:'
2416 || p_test_name
2417 || ' Expression:'
2418 || l_gmd_test_rec.expression);
2419
2420 FOR spec_test_rec IN find_nonexp_spec_test (p_spec_id) LOOP
2421 l_check := is_test_in_expression (
2422 p_expression => l_gmd_test_rec.expression
2423 , p_test_name => spec_test_rec.test_code
2424 , x_return_status => x_return_status
2425 );
2426
2427 IF (NOT l_check) THEN
2428 default_log (' Test (' || p_test_name || ') Expression contains test(s) which are not in this spec');
2429 RAISE fnd_api.g_exc_error;
2430 END IF;
2431
2432 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2433 RAISE fnd_api.g_exc_error;
2434 END IF;
2435 END LOOP;
2436 END IF;
2437
2438 OPEN check_spec_test (p_spec_id, p_test_name);
2439 FETCH check_spec_test INTO l_exist;
2440 CLOSE check_spec_test;
2441
2442 DEBUG ('In Add_spec_test_rec, does the new test exist in spec_test? ' || l_exist);
2443
2444 IF l_exist = 0 THEN
2445 DEBUG ('new spec_test.test_id:' || l_spec_test_rec.test_id);
2446 DEBUG ('new spec_test.seq:' || l_spec_test_rec.seq);
2447
2448 IF (l_spec_test_rec.optional_ind = 'N') THEN
2449 l_spec_test_rec.optional_ind := NULL;
2450 END IF;
2451
2452 DEBUG ('Inserting new optional_ind: ' || l_spec_test_rec.optional_ind);
2453 insert_spec_test_rec (p_spec_test_rec => l_spec_test_rec, x_return_status => x_return_status);
2454
2455 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2456 RAISE fnd_api.g_exc_error;
2457 END IF;
2458
2459 default_log ( ' Created spec test record : '
2460 || p_test_name
2461 || ' to Spec: '
2462 || p_spec_name
2463 || ' Spec Id: '
2464 || p_spec_id);
2465 ELSE
2466 -- ADDTEST but spec test exists, throw out an error msg
2467 default_log ( ' Spec Test Already Exits -- Can not add Test, '
2468 || 'Spec Name: '
2469 || p_spec_name
2470 || ' '
2471 || 'Test Name: '
2472 || p_test_name);
2473 DEBUG (' ADDTEST: Spec Test already exists');
2474 END IF; -- exist spec_test
2475 EXCEPTION
2476 WHEN fnd_api.g_exc_error THEN
2477 x_return_status := fnd_api.g_ret_sts_unexp_error;
2478 default_log ('Create New Spectest result in Error');
2479 WHEN OTHERS THEN
2480 x_return_status := fnd_api.g_ret_sts_unexp_error;
2481 default_log ('Create New Spectest result in Error');
2482 END add_spec_test_rec;
2483
2484 /* delete spec_test record for the test with p_test_name to spec_id
2485 */
2486 PROCEDURE del_spec_test_rec (
2487 p_spec_id IN NUMBER
2488 , p_spec_name IN VARCHAR2
2489 , p_test_name IN VARCHAR2
2490 , x_return_status OUT NOCOPY VARCHAR2
2491 ) IS
2492 l_gmd_test_rec gmd_qc_tests_b%ROWTYPE;
2493 l_exist NUMBER;
2494 l_test_values_rec gmd_qm_conc_replace_pkg.test_values;
2495 l_seq NUMBER;
2496 l_return_status VARCHAR2 (1);
2497
2498 CURSOR get_gmd_test (p_test_name IN VARCHAR2) IS
2499 SELECT *
2500 FROM gmd_qc_tests_b
2501 WHERE test_id = TO_NUMBER (p_test_name);
2502
2503 CURSOR check_spec_test (p_spec_id IN NUMBER, p_test_id IN NUMBER) IS
2504 SELECT 1
2505 FROM gmd_spec_tests_b
2506 WHERE spec_id = p_spec_id
2507 AND test_id = p_test_id;
2508
2509 CURSOR find_exp_spec_test (p_spec_id IN NUMBER) IS
2510 SELECT t.test_code
2511 , t.test_id
2512 , t.expression
2513 FROM gmd_spec_tests_b s
2514 , gmd_qc_tests_b t
2515 WHERE s.spec_id = p_spec_id
2516 AND s.test_id = t.test_id
2517 AND t.test_type = 'E';
2518 BEGIN
2519 x_return_status := fnd_api.g_ret_sts_success;
2520
2521 OPEN get_gmd_test (p_test_name);
2522 FETCH get_gmd_test INTO l_gmd_test_rec;
2523 CLOSE get_gmd_test;
2524
2525 DEBUG ('In del_spec_test_rec, test_name:' || p_test_name);
2526
2527 /* check to see if this test is included in an expression for this spec*/
2528 IF l_gmd_test_rec.test_type <> 'E' THEN
2529 FOR spec_test_rec IN find_exp_spec_test (p_spec_id) LOOP
2530 DEBUG ( ' Expression are found within the spec. Test Name:'
2531 || spec_test_rec.test_code
2532 || ' Expression:'
2533 || spec_test_rec.expression);
2534
2535 IF is_test_in_expression (p_expression => spec_test_rec.expression
2536 , p_test_name => p_test_name
2537 , x_return_status => x_return_status
2538 ) THEN
2539 default_log (' Test ' || p_test_name || ' is used in an expression within the spec');
2540 RAISE fnd_api.g_exc_error;
2541 END IF;
2542
2543 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2544 RAISE fnd_api.g_exc_error;
2545 END IF;
2546 END LOOP;
2547 END IF;
2548
2549 /* check to see if spec_test already exists*/
2550 l_exist := 0;
2551 OPEN check_spec_test (p_spec_id, l_gmd_test_rec.test_id);
2552 FETCH check_spec_test INTO l_exist;
2553 CLOSE check_spec_test;
2554
2555 IF l_exist = 1 THEN
2556 DEBUG (' get_gmd_test1 with name, test_id ' || l_gmd_test_rec.test_id);
2557 -- DELTEST, spec_test exists
2558 -- Go ahead delete it if not version controled
2559 default_log ( ' Deleting spec test record '
2560 || 'Spec Name: '
2561 || p_spec_name
2562 || ' '
2563 || 'Test Name: '
2564 || l_gmd_test_rec.test_code);
2565
2566 DELETE gmd_spec_tests_b
2567 WHERE spec_id = p_spec_id
2568 AND test_id = l_gmd_test_rec.test_id;
2569 ELSE
2570 -- DELTEST but spec_test does NOT exist, throw out an error msg
2571 default_log ( ' Spec Test Does Not Exits for Deleting, '
2572 || 'Spec Name: '
2573 || p_spec_name
2574 || ' '
2575 || 'Test Name: '
2576 || l_gmd_test_rec.test_code);
2577 DEBUG (' DELTEST: Spec Test NOT exists');
2578 x_return_status := fnd_api.g_ret_sts_unexp_error;
2579 END IF;
2580 EXCEPTION
2581 WHEN fnd_api.g_exc_error THEN
2582 x_return_status := fnd_api.g_ret_sts_unexp_error;
2583 default_log ('Delete spec test error ' || SQLERRM);
2584 WHEN OTHERS THEN
2585 x_return_status := fnd_api.g_ret_sts_unexp_error;
2586 default_log ('Delete spec test error ' || SQLERRM);
2587 END del_spec_test_rec;
2588
2589 PROCEDURE init_test_values_rec (
2590 p_spec_tests_rec IN gmd_spec_tests_b%ROWTYPE
2591 , x_test_values_rec IN OUT NOCOPY gmd_qm_conc_replace_pkg.test_values
2592 ) IS
2593 BEGIN
2594 x_test_values_rec.optional_ind := p_spec_tests_rec.optional_ind;
2595 x_test_values_rec.print_spec_ind := p_spec_tests_rec.print_spec_ind;
2596 x_test_values_rec.print_result_ind := p_spec_tests_rec.print_result_ind;
2597 x_test_values_rec.report_precision := p_spec_tests_rec.report_precision;
2598 x_test_values_rec.store_precision := p_spec_tests_rec.display_precision;
2599 x_test_values_rec.target_value_num := p_spec_tests_rec.target_value_num;
2600 x_test_values_rec.max_value_num := p_spec_tests_rec.max_value_num;
2601 x_test_values_rec.min_value_num := p_spec_tests_rec.min_value_num;
2602 x_test_values_rec.target_value_char := p_spec_tests_rec.target_value_char;
2603 x_test_values_rec.max_value_char := p_spec_tests_rec.max_value_char;
2604 x_test_values_rec.min_value_char := p_spec_tests_rec.min_value_char;
2605 x_test_values_rec.test_priority := p_spec_tests_rec.test_priority;
2606 END init_test_values_rec;
2607
2608 PROCEDURE insert_spec_test_rec (
2609 p_spec_test_rec IN gmd_spec_tests_b%ROWTYPE
2610 , x_return_status OUT NOCOPY VARCHAR2
2611 ) IS
2612 l_spec_test_rec gmd_spec_tests_b%ROWTYPE;
2613 l_row_id VARCHAR2 (200);
2614 BEGIN
2615 l_spec_test_rec := p_spec_test_rec;
2616 DEBUG ('Inserting spec test rec ');
2617
2618 GMD_SPEC_TESTS_PVT.INSERT_ROW (
2619 x_rowid => l_row_id
2620 , x_spec_id => l_spec_test_rec.spec_id
2621 , x_test_id => l_spec_test_rec.test_id
2622 , x_attribute1 => l_spec_test_rec.attribute1
2623 , x_attribute2 => l_spec_test_rec.attribute2
2624 , x_min_value_char => l_spec_test_rec.min_value_char
2625 , x_test_method_id => l_spec_test_rec.test_method_id
2626 , x_seq => l_spec_test_rec.seq
2627 , x_from_base_ind => l_spec_test_rec.from_base_ind
2628 , x_exclude_ind => l_spec_test_rec.exclude_ind
2629 , x_modified_ind => l_spec_test_rec.modified_ind
2630 , x_test_qty => l_spec_test_rec.test_qty
2631 , x_test_qty_uom => l_spec_test_rec.test_qty_uom /* Bug# 5882074 */
2632 , x_min_value_num => l_spec_test_rec.min_value_num
2633 , x_target_value_num => l_spec_test_rec.target_value_num
2634 , x_max_value_num => l_spec_test_rec.max_value_num
2635 , x_attribute5 => l_spec_test_rec.attribute5
2636 , x_attribute6 => l_spec_test_rec.attribute6
2637 , x_attribute7 => l_spec_test_rec.attribute7
2638 , x_attribute8 => l_spec_test_rec.attribute8
2639 , x_attribute9 => l_spec_test_rec.attribute9
2640 , x_attribute10 => l_spec_test_rec.attribute10
2641 , x_attribute11 => l_spec_test_rec.attribute11
2642 , x_attribute12 => l_spec_test_rec.attribute12
2643 , x_attribute13 => l_spec_test_rec.attribute13
2644 , x_attribute14 => l_spec_test_rec.attribute14
2645 , x_attribute15 => l_spec_test_rec.attribute15
2646 , x_attribute16 => l_spec_test_rec.attribute16
2647 , x_attribute17 => l_spec_test_rec.attribute17
2648 , x_attribute18 => l_spec_test_rec.attribute18
2649 , x_use_to_control_step => l_spec_test_rec.use_to_control_step
2650 , x_print_spec_ind => l_spec_test_rec.print_spec_ind
2651 , x_print_result_ind => l_spec_test_rec.print_result_ind
2652 , x_text_code => l_spec_test_rec.text_code
2653 , x_attribute_category => l_spec_test_rec.attribute_category
2654 , x_attribute3 => l_spec_test_rec.attribute3
2655 , x_retest_lot_expiry_ind => l_spec_test_rec.retest_lot_expiry_ind
2656 , x_attribute19 => l_spec_test_rec.attribute19
2657 , x_attribute20 => l_spec_test_rec.attribute20
2658 , x_max_value_char => l_spec_test_rec.max_value_char
2659 , x_test_replicate => l_spec_test_rec.test_replicate
2660 , x_check_result_interval => l_spec_test_rec.check_result_interval
2661 , x_out_of_spec_action => l_spec_test_rec.out_of_spec_action
2662 , x_exp_error_type => l_spec_test_rec.exp_error_type
2663 , x_below_spec_min => l_spec_test_rec.below_spec_min
2664 , x_above_spec_min => l_spec_test_rec.above_spec_min
2665 , x_below_spec_max => l_spec_test_rec.below_spec_max
2666 , x_above_spec_max => l_spec_test_rec.above_spec_max
2667 , x_below_min_action_code => l_spec_test_rec.below_min_action_code
2668 , x_above_min_action_code => l_spec_test_rec.above_min_action_code
2669 , x_below_max_action_code => l_spec_test_rec.below_max_action_code
2670 , x_above_max_action_code => l_spec_test_rec.above_max_action_code
2671 , x_optional_ind => l_spec_test_rec.optional_ind
2672 , x_display_precision => l_spec_test_rec.display_precision
2673 , x_report_precision => l_spec_test_rec.report_precision
2674 , x_test_priority => l_spec_test_rec.test_priority
2675 , x_print_on_coa_ind => l_spec_test_rec.print_on_coa_ind
2676 , x_target_value_char => l_spec_test_rec.target_value_char
2677 , x_attribute4 => l_spec_test_rec.attribute4
2678 , x_attribute21 => l_spec_test_rec.attribute21
2679 , x_attribute22 => l_spec_test_rec.attribute22
2680 , x_attribute23 => l_spec_test_rec.attribute23
2681 , x_attribute24 => l_spec_test_rec.attribute24
2682 , x_attribute25 => l_spec_test_rec.attribute25
2683 , x_attribute26 => l_spec_test_rec.attribute26
2684 , x_attribute27 => l_spec_test_rec.attribute27
2685 , x_attribute28 => l_spec_test_rec.attribute28
2686 , x_attribute29 => l_spec_test_rec.attribute29
2687 , x_attribute30 => l_spec_test_rec.attribute30
2688 , x_test_display => NULL --l_to_spec_test_rec.test_display
2689 , x_creation_date => NULL
2690 , x_created_by => NULL
2691 , x_last_update_date => NULL
2692 , x_last_updated_by => NULL
2693 , x_last_update_login => NULL
2694 , x_viability_duration => l_spec_test_rec.viability_duration
2695 , x_test_expiration_days => l_spec_test_rec.days
2696 , x_test_expiration_hours => l_spec_test_rec.hours
2697 , x_test_expiration_minutes => l_spec_test_rec.minutes
2698 , x_test_expiration_seconds => l_spec_test_rec.seconds
2699 , x_calc_uom_conv_ind => l_spec_test_rec.calc_uom_conv_ind
2700 , x_to_qty_uom => l_spec_test_rec.to_qty_uom /* Bug# 5882074 */
2701 );
2702 EXCEPTION
2703 WHEN OTHERS THEN
2704 gmd_debug.put_line ('sqlerror ' || SUBSTRB (SQLERRM, 1, 100));
2705 x_return_status := fnd_api.g_ret_sts_unexp_error;
2706 END insert_spec_test_rec;
2707
2708 /* p_object_type, indicates it is spec or validity rules */
2709 PROCEDURE process_validity_for_spec (
2710 p_object_type IN VARCHAR2
2711 , p_old_spec_id IN NUMBER DEFAULT NULL
2712 , p_new_spec_id IN NUMBER DEFAULT NULL
2713 , p_spec_vr_id IN NUMBER DEFAULT NULL
2714 , p_end_date IN DATE DEFAULT NULL
2715 , p_start_date IN DATE DEFAULT NULL
2716 , p_new_status IN NUMBER DEFAULT NULL
2717 , x_return_status OUT NOCOPY VARCHAR2
2718 ) IS
2719 l_old_spec_rec gmd_specifications_b%ROWTYPE;
2720 l_new_spec_rec gmd_specifications_b%ROWTYPE;
2721 l_cust_spec_vrs gmd_customer_spec_vrs%ROWTYPE;
2722 l_inv_spec_vrs gmd_inventory_spec_vrs%ROWTYPE;
2723 l_wip_spec_vrs gmd_wip_spec_vrs%ROWTYPE;
2724 l_supplier_spec_vrs gmd_supplier_spec_vrs%ROWTYPE;
2725 l_row_id VARCHAR2 (200);
2726 l_vr_id NUMBER;
2727 l_manage_vr_ind VARCHAR2 (1);
2728 l_spec_type VARCHAR2 (1);
2729 l_create_mode VARCHAR2 (1);
2730 l_default_status NUMBER;
2731 l_spec_status NUMBER;
2732
2733 -- Bug# 5882074 changed code to id
2734 CURSOR get_manage_validity (p_orgn_id IN NUMBER) IS
2735 SELECT manage_validity_rules_ind
2736 , default_specification_status
2737 FROM gmd_quality_config
2738 WHERE organization_id = p_orgn_id;
2739
2740 CURSOR get_spec_rec (p_spec_id IN NUMBER) IS
2741 SELECT *
2742 FROM gmd_specifications_b
2743 WHERE spec_id = p_spec_id;
2744
2745 -- Bug# 5882074 changed code to id
2746 CURSOR get_vrs_rec (p_spec_id IN NUMBER, p_spec_name IN VARCHAR2, p_orgn_id IN NUMBER) IS
2747 SELECT spec_vr_id
2748 , spec_type
2749 , spec_vr_status
2750 FROM gmd_all_spec_vrs_vl
2751 WHERE spec_id = p_spec_id
2752 AND spec_name = p_spec_name
2753 AND owner_organization_id = p_orgn_id;
2754 BEGIN
2755 x_return_status := fnd_api.g_ret_sts_success;
2756
2757 OPEN get_spec_rec (p_old_spec_id);
2758 FETCH get_spec_rec INTO l_old_spec_rec;
2759 CLOSE get_spec_rec;
2760 DEBUG ('after get spec rec ');
2761
2762 -- Bug# 5882074 changed code to id
2763 OPEN get_manage_validity (l_old_spec_rec.owner_organization_id);
2764 FETCH get_manage_validity INTO l_manage_vr_ind, l_default_status;
2765 CLOSE get_manage_validity;
2766 DEBUG ('after get manage validity ' || l_old_spec_rec.owner_organization_id);
2767
2768 l_vr_id := NULL;
2769 IF p_object_type = 'SPECIFICATION' THEN
2770 -- Bug# 5882074 changed code to id
2771 FOR validity_rule IN get_vrs_rec (l_old_spec_rec.spec_id
2772 , l_old_spec_rec.spec_name
2773 , l_old_spec_rec.owner_organization_id
2774 ) LOOP
2775 l_vr_id := validity_rule.spec_vr_id;
2776 l_spec_type := validity_rule.spec_type;
2777 DEBUG ('get validity rule ' || l_vr_id);
2778 DEBUG ('get validity rule, spec_type ' || l_spec_type);
2779 /* get the vr record for different types */
2780
2781 /* lookup code for manage_validity_rules_ind
2782 * lookup type 'GMD_QM_MANAGING_VALIDITY_RULES'
2783 * lookup code 'C' -- Copy
2784 * 'E' -- Copy and Set End Date for Old Rules
2785 * 'O' -- Copy and Set Obsolete Status for Old Rules
2786 * 'Z' -- Do Not Create
2787 */
2788 DEBUG ('p_new_spec_id ' || p_new_spec_id);
2789 DEBUG ('l_manage_vr_ind ' || l_manage_vr_ind);
2790
2791 OPEN get_spec_rec (p_new_spec_id);
2792 FETCH get_spec_rec INTO l_new_spec_rec;
2793 CLOSE get_spec_rec;
2794
2795 IF l_manage_vr_ind IN ('C', 'E', 'O') THEN
2796 l_create_mode := '';
2797
2798 IF l_manage_vr_ind = 'C'
2799 OR l_default_status = 100 THEN
2800 l_spec_status := 100;
2801 l_create_mode := 'R';
2802 ELSIF ( ( l_manage_vr_ind = 'O'
2803 OR l_manage_vr_ind = 'E')
2804 AND l_new_spec_rec.spec_status >= validity_rule.spec_vr_status
2805 ) THEN
2806 l_create_mode := '';
2807 ELSIF ( ( l_manage_vr_ind = 'O'
2808 OR l_manage_vr_ind = 'E')
2809 AND validity_rule.spec_vr_status > l_default_status
2810 ) THEN
2811 l_create_mode := 'R';
2812 l_spec_status := l_default_status;
2813 END IF;
2814
2815 copy_validity_rule (p_from_vr_id => l_vr_id
2816 , p_to_spec_id => p_new_spec_id
2817 , p_spec_status => l_spec_status
2818 , p_spec_type => l_spec_type
2819 , p_create_mode => l_create_mode
2820 , x_return_status => x_return_status
2821 );
2822
2823 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2824 RAISE fnd_api.g_exc_error;
2825 END IF;
2826
2827 IF l_manage_vr_ind = 'E' THEN
2828 end_old_validity_rule (p_vr_id => l_vr_id
2829 , p_spec_type => l_spec_type
2830 , x_return_status => x_return_status
2831 );
2832
2833 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2834 RAISE fnd_api.g_exc_error;
2835 END IF;
2836 ELSIF l_manage_vr_ind = 'O' THEN
2837 obsolete_old_validity_rule (p_vr_id => l_vr_id
2838 , p_spec_type => l_spec_type
2839 , x_return_status => x_return_status
2840 );
2841
2842 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2843 RAISE fnd_api.g_exc_error;
2844 END IF;
2845 END IF;
2846 ELSIF l_manage_vr_ind = 'Z' THEN
2847 NULL; -- do nothing
2848 ELSE -- not defined in config
2849 default_log ( ' Configuration for Manage Validity rule is not defined for Org: '
2850 || l_old_spec_rec.owner_organization_id);
2851 END IF;
2852 END LOOP;
2853
2854 IF l_vr_id IS NULL THEN
2855 default_log (' No Validity Rules found ');
2856 END IF;
2857 ELSIF p_object_type = 'VALIDITY' THEN
2858 l_vr_id := p_spec_vr_id;
2859
2860 SELECT spec_type
2861 INTO l_spec_type
2862 FROM gmd_all_spec_vrs_vl
2863 WHERE spec_vr_id = l_vr_id;
2864
2865 update_validity_rule (p_vr_id => l_vr_id
2866 , p_spec_type => l_spec_type
2867 , p_end_date => p_end_date
2868 , p_start_date => p_start_date
2869 , p_new_status => p_new_status
2870 , x_return_status => x_return_status
2871 );
2872
2873 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2874 RAISE fnd_api.g_exc_error;
2875 END IF;
2876 END IF;
2877 EXCEPTION
2878 WHEN fnd_api.g_exc_error THEN
2879 x_return_status := fnd_api.g_ret_sts_unexp_error;
2880 default_log ('Process validity for spec results in Error');
2881 WHEN OTHERS THEN
2882 x_return_status := fnd_api.g_ret_sts_unexp_error;
2883 default_log ('Process validity for spec results in Error');
2884 END process_validity_for_spec;
2885
2886 /* p_create_mode is 'R', replace the old spec_status with passed in p_spec_status */
2887 PROCEDURE copy_validity_rule (
2888 p_from_vr_id IN NUMBER
2889 , p_to_spec_id IN NUMBER
2890 , p_spec_status IN NUMBER
2891 , p_spec_type IN VARCHAR2
2892 , p_create_mode IN VARCHAR2
2893 , x_return_status OUT NOCOPY VARCHAR2
2894 ) IS
2895 l_cust_spec_vrs_in gmd_customer_spec_vrs%ROWTYPE;
2896 l_inv_spec_vrs_in gmd_inventory_spec_vrs%ROWTYPE;
2897 l_wip_spec_vrs_in gmd_wip_spec_vrs%ROWTYPE;
2898 l_supplier_spec_vrs_in gmd_supplier_spec_vrs%ROWTYPE;
2899 l_cust_spec_vrs_out gmd_customer_spec_vrs%ROWTYPE;
2900 l_inv_spec_vrs_out gmd_inventory_spec_vrs%ROWTYPE;
2901 l_wip_spec_vrs_out gmd_wip_spec_vrs%ROWTYPE;
2902 l_supplier_spec_vrs_out gmd_supplier_spec_vrs%ROWTYPE;
2903 l_row_id VARCHAR2 (200);
2904 l_spec_vr_id NUMBER;
2905 l_manage_vr_ind VARCHAR2 (1);
2906 l_spec_type VARCHAR2 (1);
2907 l_return BOOLEAN;
2908 BEGIN
2909 x_return_status := fnd_api.g_ret_sts_success;
2910 l_spec_type := p_spec_type;
2911 l_spec_vr_id := p_from_vr_id;
2912 DEBUG ('in copy validity rule, vr id: ' || l_spec_vr_id);
2913 DEBUG ('in copy validity rule, to_spec_id: ' || p_to_spec_id);
2914 DEBUG ('in copy validity rule, p_spec_status:' || p_spec_status);
2915 DEBUG ('in copy validity rule, spec type: ' || l_spec_type);
2916 DEBUG ('in copy validity rule, p_create_mode: ' || p_create_mode);
2917
2918 IF l_spec_type = 'C' THEN
2919 SELECT *
2920 INTO l_cust_spec_vrs_in
2921 FROM gmd_customer_spec_vrs
2922 WHERE spec_vr_id = l_spec_vr_id;
2923
2924 l_cust_spec_vrs_in.spec_id := p_to_spec_id;
2925
2926 IF p_create_mode = 'R' THEN
2927 l_cust_spec_vrs_in.spec_vr_status := p_spec_status;
2928 END IF;
2929
2930 l_cust_spec_vrs_in.spec_vr_id := NULL;
2931 default_log (' Copy From Cusotmer Validity Rule: ' || l_spec_vr_id);
2932 l_return := gmd_customer_spec_vrs_pvt.insert_row
2933 ( p_customer_spec_vrs => l_cust_spec_vrs_in
2934 , x_customer_spec_vrs => l_cust_spec_vrs_out );
2935
2936 default_log (' Created New Cusotmer Validity Rule: ' || l_cust_spec_vrs_out.spec_vr_id);
2937 ELSIF l_spec_type = 'I' THEN
2938 SELECT *
2939 INTO l_inv_spec_vrs_in
2940 FROM gmd_inventory_spec_vrs
2941 WHERE spec_vr_id = l_spec_vr_id;
2942
2943 l_inv_spec_vrs_in.spec_id := p_to_spec_id;
2944 DEBUG ('l_inv_spec_vrs.spec_id: ' || l_inv_spec_vrs_in.spec_id);
2945
2946 IF p_create_mode = 'R' THEN
2947 l_inv_spec_vrs_in.spec_vr_status := p_spec_status;
2948 END IF;
2949
2950 l_inv_spec_vrs_in.spec_vr_id := NULL;
2951 DEBUG ('l_inv_spec_vrs.spec_vr_status: ' || l_inv_spec_vrs_in.spec_vr_status);
2952 default_log (' Copy From Inventory Validity Rule: ' || l_spec_vr_id);
2953 l_return := gmd_inventory_spec_vrs_pvt.insert_row
2954 ( p_inventory_spec_vrs => l_inv_spec_vrs_in
2955 , x_inventory_spec_vrs => l_inv_spec_vrs_out );
2956
2957 --debug('call insert row for inventory validity rule, l_return '|| to_char(l_return));
2958 default_log (' Created New Inventory Validity Rule: ' || l_inv_spec_vrs_out.spec_vr_id);
2959 ELSIF l_spec_type = 'W' THEN
2960 SELECT *
2961 INTO l_wip_spec_vrs_in
2962 FROM gmd_wip_spec_vrs
2963 WHERE spec_vr_id = l_spec_vr_id;
2964
2965 l_wip_spec_vrs_in.spec_id := p_to_spec_id;
2966
2967 IF p_create_mode = 'R' THEN
2968 l_wip_spec_vrs_in.spec_vr_status := p_spec_status;
2969 END IF;
2970
2971 l_wip_spec_vrs_in.spec_vr_id := NULL;
2972 default_log (' Copy From WIP Validity Rule: ' || l_spec_vr_id);
2973 l_return := gmd_wip_spec_vrs_pvt.insert_row
2974 ( p_wip_spec_vrs => l_wip_spec_vrs_in
2975 , x_wip_spec_vrs => l_wip_spec_vrs_out );
2976
2977 default_log (' Created New WIP Validity Rule: ' || l_wip_spec_vrs_out.spec_vr_id);
2978 ELSIF l_spec_type = 'S' THEN
2979 SELECT *
2980 INTO l_supplier_spec_vrs_in
2981 FROM gmd_supplier_spec_vrs
2982 WHERE spec_vr_id = l_spec_vr_id;
2983
2984 l_supplier_spec_vrs_in.spec_id := p_to_spec_id;
2985
2986 IF p_create_mode = 'R' THEN
2987 l_supplier_spec_vrs_in.spec_vr_status := p_spec_status;
2988 END IF;
2989
2990 l_supplier_spec_vrs_in.spec_vr_id := NULL;
2991 default_log (' Copy From Supplier Validity Rule: ' || l_spec_vr_id);
2992 l_return := gmd_supplier_spec_vrs_pvt.insert_row
2993 ( p_supplier_spec_vrs => l_supplier_spec_vrs_in
2994 , x_supplier_spec_vrs => l_supplier_spec_vrs_out);
2995
2996 default_log (' Created New Supplier Validity Rule: ' || l_supplier_spec_vrs_out.spec_vr_id);
2997 END IF;
2998 EXCEPTION
2999 WHEN fnd_api.g_exc_error THEN
3000 x_return_status := fnd_api.g_ret_sts_unexp_error;
3001 default_log ('Process validity for spec results in Error');
3002 WHEN OTHERS THEN
3003 x_return_status := fnd_api.g_ret_sts_unexp_error;
3004 default_log ('Process validity for spec results in Error');
3005 END copy_validity_rule;
3006
3007 PROCEDURE end_old_validity_rule (
3008 p_vr_id IN NUMBER
3009 , p_spec_type IN VARCHAR2
3010 , x_return_status OUT NOCOPY VARCHAR2
3011 ) IS
3012 l_cust_spec_vrs gmd_customer_spec_vrs%ROWTYPE;
3013 l_inv_spec_vrs gmd_inventory_spec_vrs%ROWTYPE;
3014 l_wip_spec_vrs gmd_wip_spec_vrs%ROWTYPE;
3015 l_supplier_spec_vrs gmd_supplier_spec_vrs%ROWTYPE;
3016 l_row_id VARCHAR2 (200);
3017 l_spec_vr_id NUMBER;
3018 l_manage_vr_ind VARCHAR2 (1);
3019 l_spec_type VARCHAR2 (1);
3020 l_return BOOLEAN;
3021 BEGIN
3022 x_return_status := fnd_api.g_ret_sts_success;
3023 l_spec_type := p_spec_type;
3024 l_spec_vr_id := p_vr_id;
3025 DEBUG ('in End_old_validity_rule, spec_Type ' || l_spec_type);
3026
3027 IF l_spec_type = 'C' THEN
3028 UPDATE gmd_customer_spec_vrs
3029 SET end_date = SYSDATE
3030 WHERE spec_vr_id = l_spec_vr_id;
3031
3032 default_log (' End Customer Validity Rule: ' || l_spec_vr_id);
3033 ELSIF l_spec_type = 'I' THEN
3034 UPDATE gmd_inventory_spec_vrs
3035 SET end_date = SYSDATE
3036 WHERE spec_vr_id = l_spec_vr_id;
3037
3038 default_log (' End Inventory Validity Rule: ' || l_spec_vr_id);
3039 ELSIF l_spec_type = 'W' THEN
3040 UPDATE gmd_wip_spec_vrs
3041 SET end_date = SYSDATE
3042 WHERE spec_vr_id = l_spec_vr_id;
3043
3044 default_log (' End WIP Validity Rule: ' || l_spec_vr_id);
3045 ELSIF l_spec_type = 'S' THEN
3046 UPDATE gmd_supplier_spec_vrs
3047 SET end_date = SYSDATE
3048 WHERE spec_vr_id = l_spec_vr_id;
3049
3050 default_log (' End Supplier Validity Rule: ' || l_spec_vr_id);
3051 END IF;
3052 EXCEPTION
3053 WHEN fnd_api.g_exc_error THEN
3054 x_return_status := fnd_api.g_ret_sts_unexp_error;
3055 default_log ('End validity rule results in Error');
3056 WHEN OTHERS THEN
3057 x_return_status := fnd_api.g_ret_sts_unexp_error;
3058 default_log ('End validity rule results in Error');
3059 END end_old_validity_rule;
3060
3061 PROCEDURE obsolete_old_validity_rule (
3062 p_vr_id IN NUMBER
3063 , p_spec_type IN VARCHAR2
3064 , x_return_status OUT NOCOPY VARCHAR2
3065 ) IS
3066 l_cust_spec_vrs gmd_customer_spec_vrs%ROWTYPE;
3067 l_inv_spec_vrs gmd_inventory_spec_vrs%ROWTYPE;
3068 l_wip_spec_vrs gmd_wip_spec_vrs%ROWTYPE;
3069 l_supplier_spec_vrs gmd_supplier_spec_vrs%ROWTYPE;
3070 l_row_id VARCHAR2 (200);
3071 l_spec_vr_id NUMBER;
3072 l_manage_vr_ind VARCHAR2 (1);
3073 l_spec_type VARCHAR2 (1);
3074 l_return BOOLEAN;
3075 BEGIN
3076 x_return_status := fnd_api.g_ret_sts_success;
3077 l_spec_type := p_spec_type;
3078 l_spec_vr_id := p_vr_id;
3079 DEBUG ('in Obsolete_old_validity_rule, spec_Type ' || l_spec_type);
3080
3081 IF l_spec_type = 'C' THEN
3082 UPDATE gmd_customer_spec_vrs
3083 SET spec_vr_status = 1000
3084 WHERE spec_vr_id = l_spec_vr_id;
3085
3086 default_log (' Obsolete Customer Validity Rule: ' || l_spec_vr_id);
3087 ELSIF l_spec_type = 'I' THEN
3088 UPDATE gmd_inventory_spec_vrs
3089 SET spec_vr_status = 1000
3090 WHERE spec_vr_id = l_spec_vr_id;
3091
3092 default_log (' Obsolete Inventory Validity Rule: ' || l_spec_vr_id);
3093 ELSIF l_spec_type = 'W' THEN
3094 UPDATE gmd_wip_spec_vrs
3095 SET spec_vr_status = 1000
3096 WHERE spec_vr_id = l_spec_vr_id;
3097
3098 default_log (' Obsolete WIP Validity Rule: ' || l_spec_vr_id);
3099 ELSIF l_spec_type = 'S' THEN
3100 UPDATE gmd_supplier_spec_vrs
3101 SET spec_vr_status = 1000
3102 WHERE spec_vr_id = l_spec_vr_id;
3103
3104 default_log (' Obsolete Supplier Validity Rule: ' || l_spec_vr_id);
3105 END IF;
3106 EXCEPTION
3107 WHEN fnd_api.g_exc_error THEN
3108 x_return_status := fnd_api.g_ret_sts_unexp_error;
3109 default_log ('Obsolete validity for spec results in Error');
3110 WHEN OTHERS THEN
3111 x_return_status := fnd_api.g_ret_sts_unexp_error;
3112 default_log ('Obsolete validity for spec results in Error');
3113 END obsolete_old_validity_rule;
3114
3115 PROCEDURE update_validity_rule (
3116 p_vr_id IN NUMBER
3117 , p_spec_type IN VARCHAR2
3118 , p_end_date IN DATE
3119 , p_start_date IN DATE
3120 , p_new_status IN NUMBER
3121 , x_return_status OUT NOCOPY VARCHAR2
3122 ) IS
3123 l_cust_spec_vrs gmd_customer_spec_vrs%ROWTYPE;
3124 l_inv_spec_vrs gmd_inventory_spec_vrs%ROWTYPE;
3125 l_wip_spec_vrs gmd_wip_spec_vrs%ROWTYPE;
3126 l_supplier_spec_vrs gmd_supplier_spec_vrs%ROWTYPE;
3127 l_row_id VARCHAR2 (200);
3128 l_spec_vr_id NUMBER;
3129 l_manage_vr_ind VARCHAR2 (1);
3130 l_spec_type VARCHAR2 (1);
3131 l_spec_status NUMBER;
3132 l_return BOOLEAN;
3133
3134 CURSOR get_spec_status (p_vr_id IN NUMBER) IS
3135 SELECT spec_status
3136 FROM gmd_all_spec_vrs_vl
3137 WHERE spec_vr_id = p_vr_id;
3138 BEGIN
3139 x_return_status := fnd_api.g_ret_sts_success;
3140 l_spec_type := p_spec_type;
3141 l_spec_vr_id := p_vr_id;
3142 DEBUG ('In Update_validity_rule, validity_rule_id ' || l_spec_vr_id);
3143 DEBUG ('In Update_validity_rule, spec_type ' || l_spec_type);
3144 DEBUG ('In Update_validity_rule, end_date ' || p_end_date);
3145 DEBUG ('In Update_validity_rule, start_date ' || p_start_date);
3146 DEBUG ('In Update_validity_rule, new_status ' || p_new_status);
3147
3148 /* check the spec status, the new validity rule status can NOT be higher
3149 * than the spec status */
3150 OPEN get_spec_status (l_spec_vr_id);
3151 FETCH get_spec_status INTO l_spec_status;
3152 CLOSE get_spec_status;
3153
3154 IF l_spec_status < p_new_status THEN
3155 default_log ( ' New Status ('
3156 || p_new_status
3157 || ') for Validity Rule '
3158 || 'Can Not Be Higher than the Spec Status ('
3159 || l_spec_status
3160 || ')');
3161 RAISE fnd_api.g_exc_error;
3162 END IF;
3163
3164 IF l_spec_type = 'C' THEN
3165 IF p_new_status IS NOT NULL THEN
3166 UPDATE gmd_customer_spec_vrs
3167 SET spec_vr_status = p_new_status
3168 WHERE spec_vr_id = l_spec_vr_id;
3169 ELSIF p_start_date IS NOT NULL THEN
3170 UPDATE gmd_customer_spec_vrs
3171 SET start_date = p_start_date
3172 WHERE spec_vr_id = l_spec_vr_id;
3173 ELSIF p_end_date IS NOT NULL THEN
3174 UPDATE gmd_customer_spec_vrs
3175 SET end_date = p_end_date
3176 WHERE spec_vr_id = l_spec_vr_id;
3177 END IF;
3178
3179 default_log (' Updated Customer Validity Rule: ' || l_spec_vr_id);
3180 ELSIF l_spec_type = 'I' THEN
3181 IF p_new_status IS NOT NULL THEN
3182 UPDATE gmd_inventory_spec_vrs
3183 SET spec_vr_status = p_new_status
3184 WHERE spec_vr_id = l_spec_vr_id;
3185 ELSIF p_start_date IS NOT NULL THEN
3186 UPDATE gmd_inventory_spec_vrs
3187 SET start_date = p_start_date
3188 WHERE spec_vr_id = l_spec_vr_id;
3189 ELSIF p_end_date IS NOT NULL THEN
3190 UPDATE gmd_inventory_spec_vrs
3191 SET end_date = p_end_date
3192 WHERE spec_vr_id = l_spec_vr_id;
3193 END IF;
3194
3195 default_log (' Updated Inventory Validity Rule: ' || l_spec_vr_id);
3196 ELSIF l_spec_type = 'W' THEN
3197 IF p_new_status IS NOT NULL THEN
3198 UPDATE gmd_wip_spec_vrs
3199 SET spec_vr_status = p_new_status
3200 WHERE spec_vr_id = l_spec_vr_id;
3201 ELSIF p_start_date IS NOT NULL THEN
3202 UPDATE gmd_wip_spec_vrs
3203 SET start_date = p_start_date
3204 WHERE spec_vr_id = l_spec_vr_id;
3205 ELSIF p_end_date IS NOT NULL THEN
3206 UPDATE gmd_wip_spec_vrs
3207 SET end_date = p_end_date
3208 WHERE spec_vr_id = l_spec_vr_id;
3209 END IF;
3210
3211 default_log (' Updated WIP Validity Rule: ' || l_spec_vr_id);
3212 ELSIF l_spec_type = 'S' THEN
3213 IF p_new_status IS NOT NULL THEN
3214 UPDATE gmd_supplier_spec_vrs
3215 SET spec_vr_status = p_new_status
3216 WHERE spec_vr_id = l_spec_vr_id;
3217 ELSIF p_start_date IS NOT NULL THEN
3218 UPDATE gmd_supplier_spec_vrs
3219 SET start_date = p_start_date
3220 WHERE spec_vr_id = l_spec_vr_id;
3221 ELSIF p_end_date IS NOT NULL THEN
3222 UPDATE gmd_supplier_spec_vrs
3223 SET end_date = p_end_date
3224 WHERE spec_vr_id = l_spec_vr_id;
3225 END IF;
3226
3227 default_log (' Updated Supplier Validity Rule: ' || l_spec_vr_id);
3228 END IF;
3229 EXCEPTION
3230 WHEN fnd_api.g_exc_error THEN
3231 x_return_status := fnd_api.g_ret_sts_unexp_error;
3232 default_log ('Update validity for spec results in Error');
3233 DEBUG ('sqlerror ' || SUBSTRB (SQLERRM, 1, 100));
3234 WHEN OTHERS THEN
3235 x_return_status := fnd_api.g_ret_sts_unexp_error;
3236 default_log ('Update validity for spec results in Error');
3237 DEBUG ('sqlerror ' || SUBSTRB (SQLERRM, 1, 100));
3238 END update_validity_rule;
3239
3240 FUNCTION is_test_in_expression (
3241 p_expression IN VARCHAR2
3242 , p_test_name IN VARCHAR2
3243 , x_return_status OUT NOCOPY VARCHAR2
3244 ) RETURN BOOLEAN IS
3245 l_exptab gmd_utility_pkg.exptab;
3246 l_boolean BOOLEAN := FALSE;
3247 i NUMBER;
3248 BEGIN
3249 x_return_status := fnd_api.g_ret_sts_success;
3250 DEBUG (' In is_test_in_expression, expression: ' || p_expression);
3251 DEBUG (' In is_test_in_expression, test_name: ' || p_test_name);
3252 gmd_utility_pkg.parse (x_exp => p_expression
3253 , x_exptab => l_exptab
3254 , x_return_status => x_return_status
3255 );
3256
3257 FOR i IN 1 .. l_exptab.COUNT LOOP
3258 DEBUG (' In is_test_in_expression, exptab: ' || l_exptab (i).poperand || 'value:' || l_exptab (i).pvalue);
3259
3260 IF p_test_name = l_exptab (i).poperand THEN
3261 RETURN TRUE;
3262 END IF;
3263 END LOOP;
3264
3265 RETURN FALSE;
3266 END is_test_in_expression;
3267
3268 END GMD_QM_CONC_REPLACE_PKG;