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