1 PACKAGE BODY GR_ITEM_PROPERTIES_PUB AS
2 /* $Header: GRPIITPB.pls 120.7 2010/11/19 16:20:44 plowe noship $
3 *****************************************************************
4 * *
5 * Package GR_ITEM_PROPERTIES_PUB *
6 * *
7 * Contents ITEM_PROPERTIES *
8 * *
9 * *
10 * Use This is the public layer for the ITEM_PROPERTIES *
11 * API *
12 * *
13 * History *
14 * Written by P A Lowe OPM Unlimited Dev *
15 * Peter Lowe 07/03/08 *
16 * *
17 * Updated By For *
18 * *
19 * *
20 *****************************************************************
21 */
22
23 -- Global variables
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):='GR_ITEM_PROPERTIES_PUB';
26
27 --Forward declaration.
28 FUNCTION set_debug_flag RETURN VARCHAR2;
29 l_debug VARCHAR2(1) := set_debug_flag;
30
31 FUNCTION set_debug_flag RETURN VARCHAR2 IS
32 l_debug VARCHAR2(1):= 'N';
33 BEGIN
34 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
35 l_debug := 'Y';
36 END IF;
37 l_debug := 'Y';
38 RETURN l_debug;
39 END set_debug_flag;
40 PROCEDURE ITEM_PROPERTIES
41 ( p_api_version IN NUMBER
42 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
43 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
44 , p_item_properties_tab IN GR_ITEM_PROPERTIES_PUB.gr_item_properties_tab_type
45 , x_return_status OUT NOCOPY VARCHAR2
46 , x_msg_count OUT NOCOPY NUMBER
47 , x_msg_data OUT NOCOPY VARCHAR2
48 )
49
50 IS
51 l_api_name CONSTANT VARCHAR2 (30) := 'ITEM_PROPERTIES';
52 l_api_version CONSTANT NUMBER := 1.0;
53 l_msg_count NUMBER :=0;
54 l_debug_flag VARCHAR2(1) := set_debug_flag;
55
56 l_missing_count NUMBER;
57
58 l_action VARCHAR2(1);
59 l_organization VARCHAR2(3);
60 l_organization_id NUMBER;
61 l_item VARCHAR2(40);
62 l_inventory_item_id NUMBER;
63 l_field_name_code VARCHAR2(5);
64 l_property_id varchar2(6);
65 l_numeric_value NUMBER(15,9); -- 8208515 increased decimal precision from 6 to 9.
66 l_alpha_value VARCHAR2(240);
67 l_phrase_code VARCHAR2(15);
68 l_phrase_code2 VARCHAR2(15);
69 l_date_value date;
70 l_language_code VARCHAR2(4);
71 l_sequence_number NUMBER;
72
73 lv_organization VARCHAR2(3);
74 lv_organization_id NUMBER;
75 lv_inventory_item_id NUMBER;
76 lv_date date;
77
78 l_message1 VARCHAR2(240);
79
80 l_LENGTH NUMBER;
81 l_PRECISION NUMBER;
82 l_range_min NUMBER;
83 l_range_max NUMBER;
84
85 l_calc_PRECISION NUMBER;
86 l_last_update_login NUMBER(15,0) := 0;
87 L_KEY_EXISTS VARCHAR2(1);
88 l_property_type_indicator VARCHAR2(1) := NULL;
89 l_form_block VARCHAR2(14);
90
91 dummy NUMBER;
92 i NUMBER;
93 row_id VARCHAR2(18);
94 return_status VARCHAR2(1);
95 oracle_error NUMBER;
96 msg_data VARCHAR2(2000);
97
98 L_ORACLE_ERROR NUMBER;
99 L_CODE_BLOCK VARCHAR2(2000);
100
101
102 loop_exception EXCEPTION;
103
104 -- Cursor Definitions
105 cursor c_get_org_id is
106 SELECT organization_id INTO l_organization_id
107 FROM mtl_organizations
108 WHERE organization_code = l_organization;
109
110 cursor c_get_item_id is
111 SELECT inventory_item_id into l_inventory_item_id
112 FROM mtl_system_items_b_kfv
113 WHERE concatenated_segments = l_item
114 AND organization_id = l_organization_id;
115
116 cursor c_hazardous_material_flag is
117 SELECT 1
118 FROM mtl_system_items_b
119 WHERE inventory_item_id = l_inventory_item_id
120 AND organization_id = l_organization_id
121 AND hazardous_material_flag = 'Y';
122
123 CURSOR c_get_field_name is
124 SELECT 1
125 FROM
126 GR_LABELS_B B
127 where B.LABEL_CODE = l_field_name_code;
128
129 cursor c_val_fname is
130 SELECT lcb.form_block
131 FROM
132 GR_LABELS_B B , gr_label_classes_b lcb
133 where B.LABEL_CODE = l_field_name_code and
134 lcb.label_class_code = b.label_class_code
135 and lcb.form_block in ('SAFETY_PHRASES', 'RISK_PHRASES' );
136
137 CURSOR c_get_safety_phrase_code IS
138 SELECT safety_phrase_code
139 FROM gr_safety_phrases_vl
140 WHERE safety_phrase_code = l_phrase_code;
141
142 CURSOR c_get_risk_phrase_code IS
143 SELECT risk_phrase_code
144 FROM gr_risk_phrases_vl
145 WHERE risk_phrase_code = l_phrase_code;
146 riskcode c_get_risk_phrase_code%ROWTYPE;
147
148 CURSOR c_get_item_safety_phrase IS
149 SELECT safety_phrase_code
150 FROM gr_inv_item_safety_phrases
151 WHERE safety_phrase_code = l_phrase_code
152 and organization_id = l_organization_id
153 and inventory_item_id = l_inventory_item_id;
154 item_safetycode c_get_item_safety_phrase%ROWTYPE;
155
156 CURSOR c_get_item_risk_phrase IS
157 SELECT risk_phrase_code
158 FROM gr_inv_item_risk_phrases
159 WHERE risk_phrase_code = l_phrase_code
160 and organization_id = l_organization_id
161 and inventory_item_id = l_inventory_item_id;
162 item_riskcode c_get_item_risk_phrase%ROWTYPE;
163
164 CURSOR Cur_get_seq_no IS
165 SELECT lp.sequence_number
166 FROM gr_label_properties lp
167 WHERE lp.label_code = l_field_name_code
168 AND lp.property_id = l_property_id;
169
170 CURSOR c_get_property_id is
171 SELECT 1
172 FROM
173 GR_PROPERTIES_B B
174 where B.PROPERTY_ID = l_property_id;
175
176 CURSOR c_get_property_flag is
177 SELECT property_type_indicator,
178 LENGTH,
179 PRECISION,
180 range_min,
181 range_max
182 FROM
183 GR_PROPERTIES_B B
184 where B.PROPERTY_ID = l_property_id;
185
186 cursor c_get_PROPERTY_values is
187 select 1
188 from GR_PROPERTY_values_TL
189 WHERE property_id = l_property_id
190 and language = l_language_code
191 and value = l_alpha_value;
192
193
194 L_MSG_TOKEN VARCHAR2(100);
195
196
197 BEGIN
198
199 /* Standard call to check for call compatibility. */
200
201 IF NOT FND_API.Compatible_API_CALL
202 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
203 THEN
204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
205 END IF;
206
207 /* Initialize message list if p_int_msg_list is set TRUE. */
208 IF FND_API.to_boolean(p_init_msg_list)
209 THEN
210 FND_MSG_PUB.Initialize;
211 END IF;
212
213 -- Initialize API return Parameters
214 gmd_debug.log_initialize('euro trash');
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 -- IF (l_debug = 'Y') THEN
218 -- gmd_debug.log_initialize('GR ITEM_PROPERTIES API');
219 -- END IF;
220
221 FOR i IN 1 .. p_item_properties_tab.count LOOP
222
223
224 BEGIN
225
226 l_action := p_item_properties_tab(i).action;
227 l_organization := p_item_properties_tab(i).organization;
228 l_organization_id := p_item_properties_tab(i).organization_id;
229 l_item := p_item_properties_tab(i).item;
230 l_inventory_item_id := p_item_properties_tab(i).inventory_item_id;
231 l_field_name_code := p_item_properties_tab(i).field_name_code;
232 l_property_id := p_item_properties_tab(i).property_id;
233 l_numeric_value := p_item_properties_tab(i).numeric_value;
234 l_alpha_value := p_item_properties_tab(i).alpha_value;
235 l_phrase_code := p_item_properties_tab(i).phrase_code;
236 l_date_value := p_item_properties_tab(i).date_value;
237 l_language_code := p_item_properties_tab(i).language_code;
238
239
240 IF l_action is NULL or l_action not in ('I','D','U') then
241 FND_MESSAGE.SET_NAME('GR',
242 'GR_INVALID_ACTION');
243 RAISE loop_exception;
244 END IF;
245
246 IF l_organization is NULL and l_organization_id is NULL then
247 l_msg_token := 'organization or organization_id';
248 --GMD_API_PUB.Log_Message('GR_NULL_VALUE');
249 FND_MESSAGE.SET_NAME('GR',
250 'GR_NULL_VALUE');
251 FND_MESSAGE.SET_TOKEN('CODE',
252 l_msg_token,
253 FALSE);
254 RAISE loop_exception;
255 END IF;
256
257 IF l_organization is not NULL then
258
259 OPEN c_get_org_id;
260 FETCH c_get_org_id into lv_organization_id;
261 IF c_get_org_id%NOTFOUND THEN
262 CLOSE c_get_org_id;
263 l_msg_token := l_organization;
264 FND_MESSAGE.SET_NAME('GR',
265 'GR_RECORD_NOT_FOUND');
266 FND_MESSAGE.SET_TOKEN('CODE',
267 l_msg_token,
268 FALSE);
269 RAISE loop_exception;
270 END IF;
271 l_organization_id := lv_organization_id;
272 CLOSE c_get_org_id;
273
274 END IF; -- IF l_organization is not NULL then
275
276 IF l_item is NULL and l_inventory_item_id is NULL then
277 GMD_API_PUB.Log_Message('GR_INVALID_ITEM');
278 l_msg_token := l_item;
279 FND_MESSAGE.SET_NAME('GR',
280 'GR_INVALID_ITEM');
281 FND_MESSAGE.SET_TOKEN('CODE',
282 l_msg_token,FALSE);
283 RAISE loop_exception;
284 END IF; -- IF l_item is NULL and l_inventory_item_id is NULL then
285
286 IF l_item is not NULL then
287
288 OPEN c_get_item_id;
289 FETCH c_get_item_id into l_inventory_item_id;
290 IF c_get_org_id%NOTFOUND THEN
291
292 CLOSE c_get_item_id;
293 l_msg_token := l_item;
294 FND_MESSAGE.SET_NAME('GR',
295 'GR_RECORD_NOT_FOUND');
296 FND_MESSAGE.SET_TOKEN('CODE',
297 l_msg_token,
298 FALSE);
299 RAISE loop_exception;
300 END IF;
301 CLOSE c_get_item_id;
302
303 END IF; -- IF l_item is not NULL then
304
305 IF l_inventory_item_id is not NULL then
306
307 OPEN c_hazardous_material_flag;
308 FETCH c_hazardous_material_flag into dummy;
309 IF c_hazardous_material_flag%NOTFOUND THEN
310
311 CLOSE c_hazardous_material_flag;
312 l_msg_token := l_item;
313 FND_MESSAGE.SET_NAME('GR',
314 'GR_NOT_REG_ITEM');
315 FND_MESSAGE.SET_TOKEN('CODE',
316 l_msg_token,
317 FALSE);
318 RAISE loop_exception;
319 END IF;
320 CLOSE c_hazardous_material_flag;
321
322 END IF; -- IF l_inventory_item_id is not NULL then
323
324
325 IF l_field_name_code is NULL then
326 --GMD_API_PUB.Log_Message('GR_NULL_VALUE');
327 l_msg_token := l_field_name_code;
328 FND_MESSAGE.SET_NAME('GR',
329 'GR_NULL_VALUE');
330 FND_MESSAGE.SET_TOKEN('CODE',
331 l_msg_token,
332 FALSE);
333 RAISE loop_exception;
334 END IF;
335
336
337 -- Validate that the value of Field Name code exists in the table GR_LABELS_B.
338 -- If it does not, write an error to the log file
339
340 OPEN c_get_field_name;
341 FETCH c_get_field_name INTO dummy;
342 IF c_get_field_name%NOTFOUND THEN
343 l_msg_token := l_field_name_code;
344 CLOSE c_get_field_name;
345 FND_MESSAGE.SET_NAME('GR',
346 'GR_RECORD_NOT_FOUND');
347 FND_MESSAGE.SET_TOKEN('CODE',
348 l_msg_token,
349 FALSE);
350 RAISE loop_exception;
351 END IF;
352 CLOSE c_get_field_name;
353
354 -- check if valid property id
355
356
357 IF l_property_id is not null then
358 dummy:= 0;
359 OPEN c_get_property_id;
360 FETCH c_get_property_id INTO dummy;
361 IF c_get_property_id%NOTFOUND THEN
362 l_msg_token := l_property_id;
363 CLOSE c_get_property_id;
364 FND_MESSAGE.SET_NAME('GR',
365 'GR_RECORD_NOT_FOUND');
366 FND_MESSAGE.SET_TOKEN('CODE',
367 l_msg_token,
368 FALSE);
369 RAISE loop_exception;
370 END IF;
371 CLOSE c_get_property_id;
372
373
374 OPEN Cur_get_seq_no;
375 FETCH Cur_get_seq_no INTO l_sequence_number;
376 IF Cur_get_seq_no%NOTFOUND THEN
377 l_msg_token := l_field_name_code || ' ' || l_property_id;
378 CLOSE Cur_get_seq_no;
379 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
380 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
381 RAISE loop_exception;
382
383 END IF;
384 CLOSE Cur_get_seq_no;
385
386 END IF; -- IF l_property_id is not null then
387
388
389 IF l_action <> 'D' then -- validate inputs for I and U
390
391 IF l_property_id is NULL then
392 IF l_action = 'I' then -- not valid for U action
393 OPEN c_val_fname;
394 FETCH c_val_fname INTO l_form_block;
395 IF c_val_fname%NOTFOUND THEN
396 close c_val_fname;
397 FND_MESSAGE.SET_NAME('GR',
398 'GR_FNAME_NOT_ASSOC_PHRASE');
399 RAISE loop_exception;
400 END IF;
401 close c_val_fname;
402 END IF; -- IF l_action = 'I' then -- not valid for U action
403
404
405
406 END IF; -- IF l_property_id is NULL then
407
408
409 IF l_property_id is not NULL then
410
411 OPEN c_get_property_flag;
412 FETCH c_get_property_flag INTO l_property_type_indicator,
413 l_LENGTH,
414 l_PRECISION,
415 l_range_min,
416 l_range_max;
417 IF c_get_property_flag%NOTFOUND THEN
418
419 CLOSE c_get_property_flag;
420 l_msg_token := l_property_id || ' F';
421 RAISE loop_exception;
422 END IF;
423 close c_get_property_flag;
424
425 END IF; -- IF l_property_id is not NULL then
426
427 --Property Type phrase is not used anymore.
428 IF (l_property_type_indicator = 'P') THEN
429 l_alpha_value := NULL;
430 l_msg_token := ' phrase code';
431 FND_MESSAGE.SET_NAME('GR','GR_PROPERTY_IND_INVALID');
432 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
433 RAISE loop_exception;
434 END IF;
435
436 -- If the property is of type Safety Phrase or the field name is
437 -- associated to a field name class using the Safety Phrases form block,
438 IF (l_property_type_indicator = 'S' or l_form_block = 'SAFETY_PHRASES' ) and l_action = 'I' THEN -- not valid for U action
439
440 -- If the value for Phrase Code is null, an error message will be written to the log file.
441 IF l_phrase_code is null then
442 l_msg_token := ' phrase code';
443 FND_MESSAGE.SET_NAME('GR',
444 'GR_NULL_VALUE');
445 FND_MESSAGE.SET_TOKEN('CODE',
446 l_msg_token,
447 FALSE);
448 RAISE loop_exception;
449 END IF; -- IF l_phrase_code is null then
450
451 -- Validate the value for phrase code against the GR_SAFETY_PHRASES_VL.
452 -- An error message will be written to the log file if the value is invalid
453
454 OPEN c_get_safety_phrase_code;
455 FETCH c_get_safety_phrase_code INTO l_phrase_code2;
456 IF c_get_safety_phrase_code%NOTFOUND THEN
457 l_msg_token := l_phrase_code;
458 CLOSE c_get_safety_phrase_code;
459 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
460 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
461 RAISE loop_exception;
462 END IF;
463 CLOSE c_get_safety_phrase_code;
464 END IF; -- IF l_property_type_indicator = 'S' or l_form_block = 'SAFETY_PHRASES' and l_action = 'I' THEN
465
466 -- If the property is of type Risk Phrase
467 -- or the field name is associated to a field name class using the Risk Phrases form block,
468 IF (l_property_type_indicator = 'R' or l_form_block = 'RISK_PHRASES' ) and l_action = 'I' THEN -- not valid for U action
469
470 -- If the value for Phrase Code is null, an error message will be written to the log file.
471 IF l_phrase_code is null then
472 l_msg_token := ' phrase code';
473 FND_MESSAGE.SET_NAME('GR',
474 'GR_NULL_VALUE');
475 FND_MESSAGE.SET_TOKEN('CODE',
476 l_msg_token,
477 FALSE);
478 RAISE loop_exception;
479 END IF; -- IF l_phrase_code is null then
480
481 -- Validate the value for phrase code against the GR_RISK_PHRASES_VL.
482 -- An error message will be written to the log file if the value is invalid
483 OPEN c_get_risk_phrase_code;
484 FETCH c_get_risk_phrase_code INTO riskcode;
485 IF c_get_risk_phrase_code%NOTFOUND THEN
486 l_msg_token := l_phrase_code;
487 CLOSE c_get_risk_phrase_code;
488 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
489 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
490 RAISE loop_exception;
491 END IF;
492 CLOSE c_get_risk_phrase_code;
493
494 END IF; -- IF l_property_type_indicator = 'R' or l_form_block = 'RISK_PHRASES' THEN
495
496 -- If the property is of type Numeric
497 IF l_property_type_indicator = 'N' then
498
499 -- If the value for Numeric Value is null, an error message will be written to the log file.
500 IF l_numeric_value is NULL then
501 l_msg_token := ' numeric value';
502 FND_MESSAGE.SET_NAME('GR',
503 'GR_NULL_VALUE');
504 FND_MESSAGE.SET_TOKEN('TEXT',
505 l_msg_token,
506 FALSE);
507 RAISE loop_exception;
508 END IF; -- IF l_numeric_value is NULL then
509
510 -- Validate the value for Numeric Value against the property definition in the GR_PROPERTIES_B table.
511 -- An error message will be written to the log file if the value is invalid
512 -- Validate the value for Alphanumeric Value against the property definition in the GR_PROPERTIES_B table.
513 -- Just the length An error message will be written to the log file if the value is invalid
514 IF (length(l_numeric_value) > l_length ) then
515 --l_msg_token := l_length;
516 FND_MESSAGE.SET_NAME('GR',
517 'GR_LENGTH_INVALID');
518 FND_MESSAGE.SET_TOKEN('LENGTH', l_length);
519
520 RAISE loop_exception;
521 END IF; -- IF (length(l_numeric_value) > l_length ) then
522
523
524 IF l_numeric_value > l_range_max or l_numeric_value < l_range_min then
525 FND_MESSAGE.SET_NAME('GR',
526 'GR_MIN_MAX_ERROR');
527 RAISE loop_exception;
528 END IF; -- IF l_numeric_value > l_range_max or l_numeric_value < l_range_min then
529
530
531 l_calc_precision := (length(l_numeric_value - trunc(l_numeric_value))) - 1;
532 -- IF l_calc_precision <> l_precision THEN -- 01/15/09 7709185 replace with below
533 IF l_calc_precision > l_precision or l_calc_precision > 6 -- 8208515 table only will store up to 6 dec. places
534 THEN -- 01/15/09 7709185
535 FND_MESSAGE.SET_NAME('GR',
536 'GR_PRECISION_INVALID');
537 RAISE loop_exception;
538 END IF; -- IF l_calc_precision <> l_precision THEN
539
540 ELSIF l_property_type_indicator = 'A' then
541 -- If the value for Alphanumeric Value is null, an error message will be written to the log file
542 IF l_alpha_value is NULL then
543 l_msg_token := ' alphanumeric value';
544 FND_MESSAGE.SET_NAME('GR',
545 'GR_NULL_VALUE');
546 FND_MESSAGE.SET_TOKEN('CODE',
547 l_msg_token,
548 FALSE);
549 RAISE loop_exception;
550 END IF; -- IF l_alpha_value is NULL then
551
552 -- Validate the value for Alphanumeric Value against the property definition in the GR_PROPERTIES_B table.
553 -- Just the length An error message will be written to the log file if the value is invalid
554 IF (length(l_alpha_value) > l_length ) then --
555 l_msg_token := (length(l_alpha_value));
556 FND_MESSAGE.SET_NAME('GR',
557 'GR_LENGTH_INVALID');
558 FND_MESSAGE.SET_TOKEN('LENGTH', l_length);
559
560 RAISE loop_exception;
561 END IF; -- IF (length(l_alpha_value) > l_length ) then
562
563 ELSIF l_property_type_indicator = 'D' then
564
565 -- If the value for date Value is null, an error message will be written to the log file
566
567 IF p_item_properties_tab(i).date_value is NULL then
568 l_msg_token := ' date value';
569 FND_MESSAGE.SET_NAME('GR',
570 'GR_NULL_VALUE');
571 FND_MESSAGE.SET_TOKEN('CODE',
572 l_msg_token,
573 FALSE);
574 RAISE loop_exception;
575 END IF; -- IF l_date_value is NULL then
576
577 -- Validate the format of the value for Date Value, converting to database format if necessary.
578 -- An error message will be written to the log file if the value is invalid
579 begin
580
581 l_date_value := p_item_properties_tab(i).date_value;
582
583 exception
584 when others then
585 FND_MESSAGE.SET_NAME('GMA',
586 'SY_BAD_DATEFORMAT');
587 RAISE loop_exception;
588 end;
589
590 ELSIF l_property_type_indicator = 'F' then
591
592 -- If the value for alphanumeric Value is null, an error message will be written to the log file
593 IF l_alpha_value is NULL then
594 l_msg_token := ' alphanumeric value';
595 FND_MESSAGE.SET_NAME('GR',
596 'GR_NULL_VALUE');
597 FND_MESSAGE.SET_TOKEN('CODE',
598 l_msg_token,
599 FALSE);
600 RAISE loop_exception;
601 END IF; -- IF l_alpha_value is NULL then
602
603 dummy:= 0;
604 OPEN c_get_PROPERTY_values;
605 FETCH c_get_PROPERTY_values INTO dummy;
606 IF c_get_PROPERTY_values%NOTFOUND THEN
607 close c_get_PROPERTY_values;
608 FND_MESSAGE.SET_NAME('GR',
609 'GR_ALPHA_INVALID');
610 FND_MSG_PUB.ADD;
611 RAISE loop_exception;
612 END IF;
613 close c_get_PROPERTY_values;
614
615 END IF; -- IF l_property_type_indicator = 'N' then
616 -- END IF; -- IF l_property_id is not NULL then
617
618 END IF; -- IF l_action <> 'D' then
619
620 -- below is to check if the field name is associated to a field name class using the Safety or Risk Phrases form block
621
622 IF l_action = 'I' then
623 OPEN c_val_fname;
624 FETCH c_val_fname INTO l_form_block;
625 IF c_val_fname%NOTFOUND THEN
626 null;
627 END IF;
628 close c_val_fname;
629
630 -- If the field name is associated to a field name class using the Safety Phrases form block
631 -- validate that the record does not already exist, and insert the record into the GR_INV_ITEM_SAFETY_PHRASES table
632 -- An error message will be written to the log file if the record already exists
633 IF l_form_block = 'SAFETY_PHRASES' THEN
634
635 dummy:= 0;
636 -- Validate the value for phrase code against the GR_SAFETY_PHRASES_VL.
637 -- An error message will be written to the log file if the value is invalid
638
639 OPEN c_get_safety_phrase_code;
640 FETCH c_get_safety_phrase_code INTO l_phrase_code2;
641 IF c_get_safety_phrase_code%NOTFOUND THEN
642 l_msg_token := l_phrase_code;
643 CLOSE c_get_safety_phrase_code;
644 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
645 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
646 RAISE loop_exception;
647 END IF;
648 CLOSE c_get_safety_phrase_code;
649
650 OPEN c_get_item_safety_phrase;
651 FETCH c_get_item_safety_phrase INTO l_phrase_code2;
652 IF c_get_item_safety_phrase%FOUND THEN
653
654 CLOSE c_get_item_safety_phrase;
655 l_msg_token := l_phrase_code;
656 FND_MESSAGE.SET_NAME('GR',
657 'GR_RECORD_EXISTS');
658 FND_MESSAGE.SET_TOKEN('CODE',
659 l_msg_token,
660 FALSE);
661 RAISE loop_exception;
662 END IF;
663 CLOSE c_get_item_safety_phrase;
664
665
666 INSERT INTO GR_INV_ITEM_SAFETY_PHRASES
667 (organization_id,
668 inventory_item_id,
669 safety_phrase_code,
670 created_by,
671 creation_date,
672 last_updated_by,
673 last_update_date,
674 last_update_login)
675 VALUES
676 (l_organization_id,
677 l_inventory_item_id,
678 l_phrase_code,
679 fnd_global.user_id,
680 sysdate,
681 fnd_global.user_id,
682 sysdate,
683 l_last_update_login);
684
685
686 ELSIF l_form_block = 'RISK_PHRASES' THEN
687 -- Validate the value for phrase code against the GR_RISK_PHRASES_VL.
688 -- An error message will be written to the log file if the value is invalid
689 OPEN c_get_risk_phrase_code;
690 FETCH c_get_risk_phrase_code INTO riskcode;
691 IF c_get_risk_phrase_code%NOTFOUND THEN
692 l_msg_token := l_phrase_code;
693 CLOSE c_get_risk_phrase_code;
694 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
695 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
696 RAISE loop_exception;
697 END IF;
698 CLOSE c_get_risk_phrase_code;
699
700 -- If the field name is associated to a field name class using the Risk Phrases form block,
701 -- validate that the record does not already exist, and insert the record into the GR_INV_ITEM_RISK_PHRASES table
702 -- An error message will be written to the log file if the record already exists
703
704 OPEN c_get_item_risk_phrase;
705 FETCH c_get_item_risk_phrase INTO l_phrase_code2;
706 IF c_get_item_risk_phrase%FOUND THEN
707 CLOSE c_get_item_risk_phrase;
708 l_msg_token := l_phrase_code;
709 FND_MESSAGE.SET_NAME('GR',
710 'GR_RECORD_EXISTS');
711 FND_MESSAGE.SET_TOKEN('CODE',
712 l_msg_token,
713 FALSE);
714 RAISE loop_exception;
715 END IF;
716
717 CLOSE c_get_item_risk_phrase;
718
719 INSERT INTO GR_INV_ITEM_RISK_PHRASES
720 (organization_id,
721 inventory_item_id,
722 risk_phrase_code,
723 created_by,
724 creation_date,
725 last_updated_by,
726 last_update_date,
727 last_update_login)
728 VALUES
729 (l_organization_id,
730 l_inventory_item_id,
731 l_phrase_code,
732 fnd_global.user_id,
733 sysdate,
734 fnd_global.user_id,
735 sysdate,
736 l_last_update_login);
737
738 -- With the exception of those values listed above, all other records will be inserted into the GR_INV_ITEM_PROPERTIES table.
739 -- An error message will be written to the log file if the record already exists
740
741 ELSE
742
743 -- get l_sequence_number
744
745 IF l_property_id is not null then
746 OPEN Cur_get_seq_no;
747 FETCH Cur_get_seq_no INTO l_sequence_number;
748 IF Cur_get_seq_no%NOTFOUND THEN
749 l_msg_token := l_field_name_code || ' ' || l_property_id;
750 CLOSE Cur_get_seq_no;
751 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
752 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
753 RAISE loop_exception;
754 END IF;
755 CLOSE Cur_get_seq_no;
756 END IF; -- IF l_property_id is not null then
757
758 GR_INV_ITEM_PROPERTIES_PKG.Insert_Row (
759 p_commit => p_commit,
760 p_called_by_form => 'F',
761 p_organization_id => l_organization_id,
762 p_inventory_item_id => l_inventory_item_id,
763 p_sequence_number => l_sequence_number, -- populated by gr_item_safety.get_properties
764 p_property_id => l_property_id ,
765 p_label_code => l_field_name_code,
766 p_number_value => l_numeric_value,
767 p_alpha_value => l_alpha_value,
768 p_date_value => l_date_value,
769 p_created_by => FND_GLOBAL.USER_ID,
770 p_creation_date => SYSDATE,
771 p_last_updated_by => FND_GLOBAL.USER_ID,
772 p_last_update_date => SYSDATE,
773 p_last_update_login => l_last_update_login,
774 x_rowid => row_id,
775 x_return_status => return_status,
776 x_oracle_error => oracle_error,
777 x_msg_data => msg_data);
778
779
780
781 IF return_status <> 'S' THEN
782
783 l_oracle_error := APP_EXCEPTION.Get_Code;
784 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
785 FND_MESSAGE.SET_NAME('GR',
786 'GR_NO_RECORD_INSERTED');
787 FND_MESSAGE.SET_TOKEN('CODE',
788 l_code_block,
789 FALSE);
790 APP_EXCEPTION.Raise_Exception;
791
792 x_return_status := return_status;
793 RAISE loop_exception;
794 END IF;
795
796 END IF; --IF l_form_block = 'SAFETY_PHRASES' THEN
797
798
799
800
801
802 ELSIF l_action = 'U' then
803
804 IF l_property_type_indicator not in ('S','R','P') then
805
806 update gr_inv_item_properties
807 set number_value = l_numeric_value ,
808 alpha_value = l_alpha_value,
809 date_value = l_date_value,
810 last_updated_by = fnd_global.user_id,
811 last_update_date = sysdate,
812 last_update_login = l_last_update_login
813 WHERE organization_id = l_organization_id and
814 inventory_item_id = l_inventory_item_id
815 and label_code = l_field_name_code
816 and property_id = l_property_id;
817 IF SQL%NOTFOUND THEN
818 FND_MESSAGE.SET_NAME('GR',
819 'GR_RECORD_NOT_FOUND');
820 FND_MESSAGE.SET_TOKEN('CODE',
821 l_msg_token,
822 FALSE);
823 l_msg_token := l_organization_id || ' ' || l_inventory_item_id || ' ' || l_phrase_code;
824 RAISE loop_exception;
825
826 END IF; -- IF SQL%NOTFOUND THEN
827
828
829 END IF; -- IF (l_property_type_indicator not in ('S','R') then
830
831
832 ELSIF l_action = 'D' then
833
834 OPEN c_val_fname;
835 FETCH c_val_fname INTO l_form_block;
836 IF c_val_fname%NOTFOUND THEN
837 null;
838 END IF;
839 close c_val_fname;
840
841
842 -- If the field name is associated to a field name class using the Safety Phrases form block, validate that
843 -- the record exists, and delete the record from the GR_INV_ITEM_SAFETY_PHRASES table
844 -- An error message will be written to the log file if the record does not exist
845
846 IF l_form_block = 'SAFETY_PHRASES' THEN
847
848 dummy:= 0;
849 OPEN c_get_item_safety_phrase;
850 FETCH c_get_item_safety_phrase INTO l_phrase_code2;
851 IF c_get_item_safety_phrase%NOTFOUND THEN
852 CLOSE c_get_item_safety_phrase;
853 l_msg_token := l_phrase_code;
854 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
855 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
856 RAISE loop_exception;
857 END IF;
858 CLOSE c_get_item_safety_phrase;
859
860 DELETE from GR_INV_ITEM_SAFETY_PHRASES
861 WHERE organization_id = l_organization_id and
862 inventory_item_id = l_inventory_item_id
863 and safety_phrase_code = l_phrase_code;
864 IF SQL%NOTFOUND THEN
865
866 FND_MESSAGE.SET_NAME('GR',
867 'GR_RECORD_NOT_FOUND');
868 FND_MESSAGE.SET_TOKEN('CODE',
869 l_msg_token,
870 FALSE);
871 l_msg_token := l_organization_id || ' ' || l_inventory_item_id || ' ' || l_phrase_code;
872
873 RAISE loop_exception;
874 END IF;
875
876 ELSIF l_form_block = 'RISK_PHRASES' THEN
877
878 -- If the field name is associated to a field name class using the Risk Phrases form block,
879 -- validate that the record exists, and delete the record from the GR_INV_ITEM_RISK_PHRASES table
880 -- An error message will be written to the log file if the record does not exist
881
882 OPEN c_get_item_risk_phrase;
883 FETCH c_get_item_risk_phrase INTO l_phrase_code2;
884 IF c_get_item_risk_phrase%NOTFOUND THEN
885 CLOSE c_get_item_risk_phrase;
886 l_msg_token := l_phrase_code;
887 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
888 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
889 RAISE loop_exception;
890 END IF;
891 CLOSE c_get_item_risk_phrase;
892
893 DELETE from GR_INV_ITEM_RISK_PHRASES
894 WHERE organization_id = l_organization_id and
895 inventory_item_id = l_inventory_item_id
896 and risk_phrase_code = l_phrase_code;
897 IF SQL%NOTFOUND THEN
898 FND_MESSAGE.SET_NAME('GR',
899 'GR_RECORD_NOT_FOUND');
900 FND_MESSAGE.SET_TOKEN('CODE',
901 l_msg_token,
902 FALSE);
903 l_msg_token := l_organization_id || ' ' || l_inventory_item_id || ' ' || l_phrase_code;
904 RAISE loop_exception;
905 END IF;
906
907 -- With the exception of those values listed above, all other records will be deleted from the GR_INV_ITEM_PROPERTIES table.
908 -- An error message will be written to the log file if the record does not exist
909
910
911 ELSE
912
913 GR_INV_ITEM_PROPERTIES_PKG.delete_Rows
914 (p_commit => p_commit,
915 p_called_by_form => 'F',
916 p_delete_option => 'B', -- 'B' Delete all rows using the item and label combination.
917 p_organization_id => l_organization_id,
918 p_inventory_item_id => l_inventory_item_id,
919 p_label_code => l_field_name_code,
920 x_return_status => return_status,
921 x_oracle_error => oracle_error,
922 x_msg_data => msg_data);
923
924 IF return_status <> 'S' THEN
925
926 l_oracle_error := APP_EXCEPTION.Get_Code;
927 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
928 FND_MESSAGE.SET_NAME('GR',
929 'GR_NO_RECORD_INSERTED');
930 FND_MESSAGE.SET_TOKEN('CODE',
931 l_code_block,
932 FALSE);
933 APP_EXCEPTION.Raise_Exception;
934
935 x_return_status := return_status;
936 RAISE loop_exception;
937 END IF;
938
939 END IF; -- IF l_form_block = 'SAFETY_PHRASES' THEN
940
941
942
943 END IF; -- IF action = 'I' then
944
945 EXCEPTION
946
947 WHEN loop_exception THEN
948
949 x_return_status := 'E';
950 FND_MSG_PUB.ADD;
951
952 WHEN OTHERS THEN
953
954 gmi_reservation_util.println('- entering when others ');
955 x_return_status := 'U';
956 oracle_error := SQLCODE;
957 x_msg_data := SUBSTR(SQLERRM, 1, 200);
958 FND_MESSAGE.SET_NAME('GR',
959 'GR_UNEXPECTED_ERROR');
960 FND_MESSAGE.SET_TOKEN('TEXT',
961 l_msg_token,
962 FALSE);
963 FND_MSG_PUB.ADD;
964 x_msg_data := FND_MESSAGE.Get;
965
966 END;
967
968 END LOOP; -- FOR i IN 1 .. p_item_properties_tab.count LOOP
969
970
971 IF x_return_status = 'E' or x_return_status = 'U' then
972 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
973 , p_count => x_msg_count
974 , p_data => x_msg_data
975 );
976 END IF;
977
978
979 IF x_return_status IN (FND_API.G_RET_STS_SUCCESS) AND (FND_API.To_Boolean( p_commit ) ) THEN
980 Commit;
981 END IF;
982 END ITEM_PROPERTIES;
983
984 END GR_ITEM_PROPERTIES_PUB;
[Home] [Help]