[Home] [Help]
PACKAGE BODY: APPS.POS_PRODUCT_SERVICE_UTL_PKG
Source
1 PACKAGE BODY pos_product_service_utl_pkg AS
2 /* $Header: POSPSUTB.pls 120.24.12020000.2 2012/07/23 20:36:17 atjen ship $*/
3 --
4 -- type definition
5 TYPE cursor_ref_type IS REF CURSOR;
6
7 TYPE structure_segment_table IS
8 TABLE OF fnd_flex_key_api.segment_type INDEX BY BINARY_INTEGER;
9
10 TYPE varchar10_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
11 TYPE varchar1000_table IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
12 TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
13
14 TYPE product_segment_record IS RECORD
15 (column_name fnd_id_flex_segments_vl.application_column_name%TYPE,
16 value_set_id fnd_id_flex_segments_vl.flex_value_set_id%TYPE,
17 validation_type fnd_flex_value_sets.validation_type%TYPE,
18 table_name fnd_flex_validation_tables.application_table_name%TYPE,
19 meaning_column fnd_flex_validation_tables.meaning_column_name%TYPE,
20 id_column fnd_flex_validation_tables.id_column_name%TYPE,
21 value_column fnd_flex_validation_tables.value_column_name%TYPE,
22 where_clause fnd_flex_validation_tables.meaning_column_name%TYPE,
23 parent_segment_index INTEGER
24 );
25
26 TYPE product_segment_table IS
27 TABLE OF product_segment_record INDEX BY BINARY_INTEGER;
28
29 TYPE category_segment_record IS RECORD (
30 segment1 mtl_categories_b.segment1%TYPE
31 , segment2 mtl_categories_b.segment2%TYPE
32 , segment3 mtl_categories_b.segment3%TYPE
33 , segment4 mtl_categories_b.segment4%TYPE
34 , segment5 mtl_categories_b.segment5%TYPE
35 , segment6 mtl_categories_b.segment6%TYPE
36 , segment7 mtl_categories_b.segment7%TYPE
37 , segment8 mtl_categories_b.segment8%TYPE
38 , segment9 mtl_categories_b.segment9%TYPE
39 , segment10 mtl_categories_b.segment10%TYPE
40 , segment11 mtl_categories_b.segment11%TYPE
41 , segment12 mtl_categories_b.segment12%TYPE
42 , segment13 mtl_categories_b.segment13%TYPE
43 , segment14 mtl_categories_b.segment14%TYPE
44 , segment15 mtl_categories_b.segment15%TYPE
45 , segment16 mtl_categories_b.segment16%TYPE
46 , segment17 mtl_categories_b.segment17%TYPE
47 , segment18 mtl_categories_b.segment18%TYPE
48 , segment19 mtl_categories_b.segment19%TYPE
49 , segment20 mtl_categories_b.segment20%TYPE
50 );
51
52
53 -- private package constants
54 g_product_segment_profile_name CONSTANT VARCHAR2(30) := 'POS_PRODUCT_SERVICE_SEGMENTS';
55 g_max_number_of_segments CONSTANT NUMBER := 20;
56 g_yes CONSTANT VARCHAR2(1) := 'Y';
57 g_no CONSTANT VARCHAR2(1) := 'N';
58
59 -- cached meta data about product service
60 g_initialized BOOLEAN := FALSE;
61 g_default_po_category_set_id NUMBER;
62 g_structure_id NUMBER;
63 g_product_segment_definition fnd_profile_option_values.profile_option_value%TYPE;
64 g_product_segment_count NUMBER := 0;
65 g_product_segments product_segment_table;
66 g_structure_segment_count NUMBER := 0;
67 g_structure_segments structure_segment_table;
68 g_delimiter fnd_id_flex_structures_vl.concatenated_segment_delimiter%TYPE;
69 g_description_queries varchar1000_table;
70
71 PROCEDURE clear_cache IS
72 BEGIN
73 g_default_po_category_set_id := NULL;
74 g_structure_id := NULL;
75 g_product_segment_definition := NULL;
76 g_product_segment_count := 0;
77 g_product_segments.DELETE;
78 g_structure_segments.DELETE;
79 g_structure_segment_count := 0;
80 g_initialized := FALSE;
81 END clear_cache;
82
83 FUNCTION get_parent_value_set_id
84 (p_value_set_id IN NUMBER) RETURN NUMBER IS
85 CURSOR l_cur IS
86 SELECT parent_flex_value_set_id
87 FROM fnd_flex_value_sets
88 WHERE flex_value_set_id = p_value_set_id;
89 l_parent_value_set_id NUMBER;
90 BEGIN
91 OPEN l_cur;
92 FETCH l_cur INTO l_parent_value_set_id;
93 CLOSE l_cur;
94 RETURN l_parent_value_set_id;
95 END get_parent_value_set_id;
96
97 PROCEDURE validate_segment_definition
98 (x_status OUT NOCOPY VARCHAR2,
99 x_error_message OUT NOCOPY VARCHAR2) IS
100 l_temp fnd_profile_option_values.profile_option_value%TYPE;
101 l_char VARCHAR2(10);
102 l_index NUMBER;
103 l_length NUMBER;
104 l_dot_positions number_table;
105 l_number_of_dots NUMBER;
106 l_number_string fnd_profile_option_values.profile_option_value%TYPE;
107 l_start_pos NUMBER;
108 l_end_pos NUMBER;
109 l_number INTEGER;
110 l_segments_in_structure number_table;
111 l_segments_appeared varchar10_table;
112 l_valueset fnd_vset.valueset_r;
113 l_format fnd_vset.valueset_dr;
114 l_product_segment product_segment_record;
115 l_parent_value_set_id NUMBER;
116 BEGIN
117 x_status := g_no;
118
119 FOR l_index IN 1..g_max_number_of_segments LOOP
120 l_segments_in_structure(l_index) := NULL;
121 l_segments_appeared(l_index) := NULL;
122 END LOOP;
123
124 -- flag which segments are in the flexfield structure
125 FOR l_index IN 1..g_structure_segment_count LOOP
126 l_segments_in_structure(To_number(Substr(g_structure_segments(l_index).column_name,
127 Length('SEGMENT') + 1))) := l_index;
128 END LOOP;
129
130 -- product segment definition can not be null
131 IF g_product_segment_definition IS NULL THEN
132 --x_error_message := 'product segment definition is null';
133 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_NO_DEFINE');
134 RETURN;
135 END IF;
136
137 -- product segment definition should be not have any characters other than digits
138 -- and the . delimiter
139 l_length := Length(g_product_segment_definition);
140 l_number_of_dots := 0;
141 FOR l_index IN 1..l_length LOOP
142 l_char := Substr(g_product_segment_definition,l_index,1);
143 IF l_char NOT IN ('1','2','3','4','5','6','7','8','9','0','.') THEN
144 --x_error_message := 'product segment definition has a character that is not digits or the . delimeter';
145 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_ILLEGAL_CHAR');
146 RETURN;
147 END IF;
148 IF l_char = '.' THEN
149 l_number_of_dots := l_number_of_dots + 1;
150 l_dot_positions(l_number_of_dots) := l_index;
151 END IF;
152 END LOOP;
153
154 -- product segment definition should be in the format of [1-20](.[1-20])*
155 -- a number can not appear twice (e.g. 1.1 is wrong)
156 -- and the corresponding segment must be one of the segments in the
157 -- flexfield structure for default purchasing category set
158 g_product_segment_count := 0;
159 FOR l_index IN 1..l_number_of_dots + 1 LOOP
160 IF l_index = 1 THEN
161 l_start_pos := 1;
162 ELSE
163 l_start_pos := l_dot_positions(l_index - 1) + 1;
164 END IF;
165
166 IF l_index <= l_number_of_dots THEN
167 l_end_pos := l_dot_positions(l_index) - 1;
168 ELSE
169 l_end_pos := l_length;
170 END IF;
171
172 l_number_string := Substr(g_product_segment_definition,
173 l_start_pos,
174 l_end_pos - l_start_pos + 1);
175 l_number := To_number(l_number_string);
176
177 IF l_number > g_max_number_of_segments OR l_number < 0 THEN
178 --x_error_message := 'product segment definition has ' || l_number || ', but SEGMENT' || l_number || ' is not used in the flexfield structure for' || 'the default purchasing category set';
179 x_error_message:= fnd_message.get_string('POS', 'POS_PS_SETUP_WRONG_SEG_NUM');
180 RETURN;
181 END IF;
182
183 IF l_segments_in_structure(l_number) IS NULL THEN
184 --x_error_message := 'product segment definition has ' || l_number || ', but SEGMENT' || l_number || ' is not used in the flexfield structure for' || ' the default purchasing category set';
185 x_error_message:= fnd_message.get_string('POS', 'POS_PS_SETUP_WRONG_SEG_NUM');
186 RETURN;
187 END IF;
188
189 IF l_segments_appeared(l_number) IS NULL THEN
190 l_segments_appeared(l_number) := 'Y';
191 ELSE
192 --x_error_message := 'same segment can not be used twice in product segment definition';
193 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_DUP_SEGMENT');
194 RETURN;
195 END IF;
196
197 l_product_segment.column_name :=
198 g_structure_segments(l_segments_in_structure(l_number)).column_name;
199
200 l_product_segment.value_set_id :=
201 g_structure_segments(l_segments_in_structure(l_number)).value_set_id;
202
203 fnd_vset.get_valueset
204 (g_structure_segments(l_segments_in_structure(l_number)).value_set_id,
205 l_valueset, l_format);
206
207
208 IF l_valueset.validation_type NOT IN ('I','D','F') THEN
209 --
210 -- NOTES
211 --
212 -- the following validation types are not supported
213 -- none, pair, special, because there is no way to get translated description for values;
214 -- translatable independent, translatable dependent because item categories key flexfield
215 -- does not support id type value sets (column allow_id_valuesets in table
216 -- fnd_id_flex for id_flex_code MCAT is N).
217 --
218 -- x_error_message := 'product segment definition error: validation type ' || l_valueset.validation_type || ' not supported';
219 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_WRONG_TYPE');
220 RETURN;
221 END IF;
222
223 IF l_valueset.table_info.meaning_column_name LIKE '%:%' THEN
224 --x_error_message := 'product segment definition error: bind variables in meaning columns are not supported';
225 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_BIND_VAR_1');
226 RETURN;
227 END IF;
228
229 IF l_valueset.table_info.where_clause LIKE '%:%' THEN
230 --x_error_message := 'product segment definition error: bind variables in where clause are not supported';
231 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_BIND_VAR_2');
232 RETURN;
233 END IF;
234
235 l_product_segment.validation_type := l_valueset.validation_type;
236 l_product_segment.table_name := l_valueset.table_info.table_name;
237 l_product_segment.id_column := l_valueset.table_info.id_column_name;
238 l_product_segment.value_column := l_valueset.table_info.value_column_name;
239 l_product_segment.meaning_column := l_valueset.table_info.meaning_column_name;
240 l_product_segment.where_clause := l_valueset.table_info.where_clause;
241
242 IF l_valueset.validation_type IN ('D') THEN
243 -- the first segment cannot have a dependent value set
244 IF g_product_segment_count = 0 THEN
245 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_BAD_DEP_VS');
246 RETURN;
247 END IF;
248
249 -- find out the index to the parent segment record
250 l_parent_value_set_id := get_parent_value_set_id(l_product_segment.value_set_id);
251 FOR l_lookup_back IN REVERSE 1..g_product_segment_count LOOP
252 IF g_product_segments(l_lookup_back).value_set_id = l_parent_value_set_id THEN
253 l_product_segment.parent_segment_index := l_lookup_back;
254 EXIT;
255 END IF;
256 END LOOP;
257 ELSE
258 l_product_segment.parent_segment_index := NULL;
259 END IF;
260
261 g_product_segment_count := g_product_segment_count + 1;
262 g_product_segments(g_product_segment_count) := l_product_segment;
263
264 g_description_queries(g_product_segment_count) := NULL;
265 END LOOP;
266
267 -- check segment def against pos_sup_products_services
268 -- to be implemented later
269 --
270
271 -- all validation is done
272 x_status := g_yes;
273 x_error_message := NULL;
274 RETURN;
275 END validate_segment_definition;
276
277 PROCEDURE query_po_category_flexfield IS
278 l_flexfield fnd_flex_key_api.flexfield_type;
279 l_structure fnd_flex_key_api.structure_type;
280 l_segment_list fnd_flex_key_api.segment_list;
281 BEGIN
282 -- this call is a must before calling other procedures in fnd_flex_key_api package
283 fnd_flex_key_api.set_session_mode('seed_data');
284
285 -- find flexfield
286 l_flexfield := fnd_flex_key_api.find_flexfield('INV','MCAT');
287
288 -- find structure
289 l_structure := fnd_flex_key_api.find_structure(l_flexfield, g_structure_id);
290
291 -- store the segment_separator in package variable
292 g_delimiter := l_structure.segment_separator;
293
294 -- get segment count and name list
295 fnd_flex_key_api.get_segments(flexfield => l_flexfield,
296 structure => l_structure,
297 enabled_only => TRUE,
298 nsegments => g_structure_segment_count,
299 segments => l_segment_list);
300
301 -- get each segment info
302 FOR l_index IN 1..g_structure_segment_count LOOP
303 g_structure_segments(l_index) :=
304 fnd_flex_key_api.find_segment(l_flexfield, l_structure, l_segment_list(l_index));
305 END LOOP;
306 END query_po_category_flexfield;
307
308 PROCEDURE query_def_po_category_set_id IS
309 CURSOR l_cur IS
310 SELECT category_set_id
311 FROM mtl_default_category_sets
312 WHERE functional_area_id = 2;
313 BEGIN
314 OPEN l_cur;
315 FETCH l_cur INTO g_default_po_category_set_id;
316 CLOSE l_cur;
317 END query_def_po_category_set_id;
318
319 PROCEDURE query_structure_id IS
320 CURSOR l_cur IS
321 SELECT structure_id
322 FROM mtl_category_sets
323 WHERE category_set_id = g_default_po_category_set_id;
324 BEGIN
325 OPEN l_cur;
326 FETCH l_cur INTO g_structure_id;
327 CLOSE l_cur;
328 END query_structure_id;
329
330 PROCEDURE do_init
331 (x_status OUT NOCOPY VARCHAR2,
332 x_error_message OUT NOCOPY VARCHAR2) IS
333 BEGIN
334 clear_cache;
335
336 query_def_po_category_set_id;
337 query_structure_id;
338 query_po_category_flexfield;
339
340 IF g_default_po_category_set_id IS NULL THEN
341 x_status := g_no;
342 --x_error_message := 'default purchasing category set id is null';
343 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_DEF_PUR_CAT');
344 RETURN;
345 END IF;
346
347 -- get product and service segments definition
348
349 FOR x IN (SELECT profile_option_value
350 FROM fnd_profile_option_values
351 WHERE profile_option_id IN
352 (SELECT profile_option_id
353 FROM fnd_profile_options
354 WHERE profile_option_name = g_product_segment_profile_name
355 AND level_id = 10001
356 AND level_value = 0
357 )
358 ) LOOP
359 g_product_segment_definition := x.profile_option_value;
360 END LOOP;
361
362 validate_segment_definition(x_status, x_error_message);
363 IF x_status = g_no THEN
364 RETURN;
365 END IF;
366
367 g_initialized := TRUE;
368 x_status := g_yes;
369 x_error_message := NULL;
370 END do_init;
371
372 PROCEDURE validate_segment_prof_nocache (
373 p_product_segment_definition IN VARCHAR2
374 , x_status OUT NOCOPY VARCHAR2
375 , x_error_message OUT NOCOPY VARCHAR2
376 ) IS
377 BEGIN
378 clear_cache;
379
380 query_def_po_category_set_id;
381 query_structure_id;
382 query_po_category_flexfield;
383
384 IF g_default_po_category_set_id IS NULL THEN
385 x_status := g_no;
386 --x_error_message := 'default purchasing category set id is null';
387 x_error_message := fnd_message.get_string('POS', 'POS_PS_SETUP_DEF_PUR_CAT');
388 RETURN;
389 END IF;
390
391 g_product_segment_definition := p_product_segment_definition;
392
393 validate_segment_definition(x_status, x_error_message);
394 clear_cache;
395 RETURN;
396 END validate_segment_prof_nocache;
397
398 PROCEDURE assert_init IS
399 l_status VARCHAR2(1);
400 l_error_message VARCHAR2(2000);
401 BEGIN
402 IF g_initialized = FALSE THEN
403 do_init(l_status, l_error_message);
404 IF l_status = g_yes THEN
405 NULL;
406 ELSE
407 raise_application_error(-20000,l_error_message);
408 END IF;
409 END IF;
410 END assert_init;
411
412 PROCEDURE save_segment_profile (
413 p_product_segment_definition IN VARCHAR2
414 , x_status OUT NOCOPY VARCHAR2
415 , x_error_message OUT NOCOPY VARCHAR2
416 ) IS
417 BEGIN
418
419 IF NOT fnd_profile.save(g_product_segment_profile_name, p_product_segment_definition, 'SITE') THEN
420 x_status := g_no;
421 x_error_message := sqlerrm;
422 clear_cache;
423 RETURN;
424 END IF;
425
426 x_status := g_yes;
427 x_error_message := NULL;
428 clear_cache;
429 END save_segment_profile;
430
431 FUNCTION get_segment_value_desc
432 (p_product_segment_index IN NUMBER,
433 p_segment_value IN VARCHAR2,
434 p_parent_segment_value IN VARCHAR2
435 ) RETURN VARCHAR2
436 IS
437 l_id_or_value fnd_flex_validation_tables.id_column_name%TYPE;
438 l_cur cursor_ref_type;
439 l_description fnd_flex_values_vl.description%TYPE;
440 l_description2 fnd_flex_values_vl.description%TYPE;
441 l_index NUMBER;
442 l_string varchar2(1000);
443 BEGIN
444 IF g_product_segments(p_product_segment_index).validation_type = 'F' THEN -- table
445 -- build and cache the query if the query is not yet built
446 IF g_description_queries(p_product_segment_index) IS NULL THEN
447 l_id_or_value := g_product_segments(p_product_segment_index).id_column;
448 IF l_id_or_value IS NULL THEN
449 l_id_or_value := g_product_segments(p_product_segment_index).value_column;
450 END IF;
451 /*Modified as as part of bug 8611906 considering where clause can be null also*/
452 IF (g_product_segments(p_product_segment_index).where_clause IS null) THEN
453 g_description_queries(p_product_segment_index) :=
454 'select ' ||
455 g_product_segments(p_product_segment_index).meaning_column || ' description' ||
456 ' from ' ||
457 g_product_segments(p_product_segment_index).table_name || ' where (' ||
458 l_id_or_value || ' = :1 )';
459
460 ELSE
461 select instr(g_product_segments(p_product_segment_index).where_clause,'ORDER BY') INTO l_index from dual;
462 IF(l_index=0) then
463 select instr(g_product_segments(p_product_segment_index).where_clause,'order by') INTO l_index from dual;
464 END IF;
465 IF (l_index=0) THEN
466 l_string := g_product_segments(p_product_segment_index).where_clause;
467 ELSE
468 SELECT SubStr(g_product_segments(p_product_segment_index).where_clause,1,l_index-1) INTO l_string FROM dual;
469 END IF;
470
471 g_description_queries(p_product_segment_index) :=
472 'select ' ||
473 g_product_segments(p_product_segment_index).meaning_column || ' description' ||
474 ' from ' ||
475 g_product_segments(p_product_segment_index).table_name ||
476 ' ' || l_string || ' and (' ||
477 l_id_or_value || ' = :1 )';
478
479 END IF;
480 /*Modified as as part of bug 8611906 considering where clause can be null also*/
481
482 END IF;
483 OPEN l_cur FOR g_description_queries(p_product_segment_index)
484 using p_segment_value;
485 ELSIF g_product_segments(p_product_segment_index).validation_type IN ('I') THEN
486 -- validation type is independent
487 g_description_queries(p_product_segment_index) :=
488 'select description from fnd_flex_values_vl ' ||
489 'where flex_value_set_id = :1 and flex_value = :2 ';
490 OPEN l_cur FOR g_description_queries(p_product_segment_index)
491 using g_product_segments(p_product_segment_index).value_set_id, p_segment_value;
492 ELSIF g_product_segments(p_product_segment_index).validation_type IN ('D') THEN
493 -- validation type is dependent
494 g_description_queries(p_product_segment_index) :=
495 'select description from fnd_flex_values_vl ' ||
496 ' where flex_value_set_id = :1 and flex_value = :2 and parent_flex_value_low = :3';
497 OPEN l_cur FOR g_description_queries(p_product_segment_index)
498 using g_product_segments(p_product_segment_index).value_set_id,
499 p_segment_value, p_parent_segment_value;
500 ELSE
501 -- should not reach here, as the validation type is checked during initialization
502 raise_application_error
503 (-2000, 'unsupported validation type: ' ||
504 g_product_segments(p_product_segment_index).validation_type
505 );
506 END IF;
507 -- dbms_output.put_line('query ' || g_description_queries(p_product_segment_index));
508 FETCH l_cur INTO l_description;
509 -- the query should return only one row for independent and dependent, but
510 -- it could return multiple for table validation type which means data
511 -- in custom table is not right
512 -- we should ignore it for now
513 FETCH l_cur INTO l_description2;
514 --dbms_output.put_line(l_description2);
515 CLOSE l_cur;
516 --dbms_output.put_line('returning '||l_description);
517 RETURN l_description;
518 END get_segment_value_desc;
519
520 FUNCTION get_product_description
521 (p_product_segment_index IN NUMBER,
522 p_segment_value IN VARCHAR2,
523 p_parent_segment_value IN VARCHAR2
524 ) RETURN VARCHAR2
525 IS
526 BEGIN
527 RETURN get_segment_value_desc( p_product_segment_index, p_segment_value, p_parent_segment_value);
528 END get_product_description;
529
530 --FUNCTION get_segment_value(p_product_segment_index IN NUMBER,
531 -- p_rec IN pos_sup_products_services%ROWTYPE ) RETURN VARCHAR2
532 FUNCTION get_segment_value(p_product_segment_index IN NUMBER,
533 p_rec IN category_segment_record ) RETURN VARCHAR2
534 IS
535 l_index NUMBER;
536 BEGIN
537 l_index := p_product_segment_index;
538 IF g_product_segments(l_index).column_name = 'SEGMENT1' THEN
539 RETURN p_rec.segment1;
540 ELSIF g_product_segments(l_index).column_name = 'SEGMENT2' THEN
541 RETURN p_rec.segment2;
542 ELSIF g_product_segments(l_index).column_name = 'SEGMENT3' THEN
543 RETURN p_rec.segment3;
544 ELSIF g_product_segments(l_index).column_name = 'SEGMENT4' THEN
545 RETURN p_rec.segment4;
546 ELSIF g_product_segments(l_index).column_name = 'SEGMENT5' THEN
547 RETURN p_rec.segment5;
548 ELSIF g_product_segments(l_index).column_name = 'SEGMENT6' THEN
549 RETURN p_rec.segment6;
550 ELSIF g_product_segments(l_index).column_name = 'SEGMENT7' THEN
551 RETURN p_rec.segment7;
552 ELSIF g_product_segments(l_index).column_name = 'SEGMENT8' THEN
553 RETURN p_rec.segment8;
554 ELSIF g_product_segments(l_index).column_name = 'SEGMENT9' THEN
555 RETURN p_rec.segment9;
556 ELSIF g_product_segments(l_index).column_name = 'SEGMENT10' THEN
557 RETURN p_rec.segment10;
558 ELSIF g_product_segments(l_index).column_name = 'SEGMENT11' THEN
559 RETURN p_rec.segment11;
560 ELSIF g_product_segments(l_index).column_name = 'SEGMENT12' THEN
561 RETURN p_rec.segment12;
562 ELSIF g_product_segments(l_index).column_name = 'SEGMENT13' THEN
563 RETURN p_rec.segment13;
564 ELSIF g_product_segments(l_index).column_name = 'SEGMENT14' THEN
565 RETURN p_rec.segment14;
566 ELSIF g_product_segments(l_index).column_name = 'SEGMENT15' THEN
567 RETURN p_rec.segment15;
568 ELSIF g_product_segments(l_index).column_name = 'SEGMENT16' THEN
569 RETURN p_rec.segment16;
570 ELSIF g_product_segments(l_index).column_name = 'SEGMENT17' THEN
571 RETURN p_rec.segment17;
572 ELSIF g_product_segments(l_index).column_name = 'SEGMENT18' THEN
573 RETURN p_rec.segment18;
574 ELSIF g_product_segments(l_index).column_name = 'SEGMENT19' THEN
575 RETURN p_rec.segment19;
576 ELSIF g_product_segments(l_index).column_name = 'SEGMENT20' THEN
577 RETURN p_rec.segment20;
578 END IF;
579 END get_segment_value;
580
581 PROCEDURE set_segment_value(
582 p_segment_index IN NUMBER
583 , p_segment_value IN VARCHAR2
584 , p_rec IN OUT NOCOPY category_segment_record )
585
586 IS
587 BEGIN
588 IF g_product_segments(p_segment_index).column_name = 'SEGMENT1' THEN
589 p_rec.segment1:= p_segment_value;
590 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT2' THEN
591 p_rec.segment2 := p_segment_value;
592 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT3' THEN
593 p_rec.segment3 := p_segment_value;
594 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT4' THEN
595 p_rec.segment4 := p_segment_value;
596 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT5' THEN
597 p_rec.segment5 := p_segment_value;
598 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT6' THEN
599 p_rec.segment6 := p_segment_value;
600 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT7' THEN
601 p_rec.segment7 := p_segment_value;
602 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT8' THEN
603 p_rec.segment8 := p_segment_value;
604 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT9' THEN
605 p_rec.segment9 := p_segment_value;
606 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT10' THEN
607 p_rec.segment10 := p_segment_value;
608 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT11' THEN
609 p_rec.segment11 := p_segment_value;
610 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT12' THEN
611 p_rec.segment12 := p_segment_value;
612 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT13' THEN
613 p_rec.segment13 := p_segment_value;
614 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT14' THEN
615 p_rec.segment14 := p_segment_value;
616 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT15' THEN
617 p_rec.segment15 := p_segment_value;
618 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT16' THEN
619 p_rec.segment16 := p_segment_value;
620 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT17' THEN
621 p_rec.segment17 := p_segment_value;
622 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT18' THEN
623 p_rec.segment18 := p_segment_value;
624 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT19' THEN
625 p_rec.segment19 := p_segment_value;
626 ELSIF g_product_segments(p_segment_index).column_name = 'SEGMENT20' THEN
627 p_rec.segment20 := p_segment_value;
628 END IF;
629 END set_segment_value;
630
631
632 PROCEDURE check_subcategory
633 (p_classification_id IN NUMBER,
634 x_has_subcategory OUT NOCOPY VARCHAR2)
635 IS
636 CURSOR l_cur IS
637 SELECT * FROM pos_sup_products_services
638 WHERE classification_id = p_classification_id;
639 l_rec l_cur%ROWTYPE;
640 l_segment_values category_segment_record;
641 l_query VARCHAR2(3000);
642 l_segment_value pos_sup_products_services.segment1%TYPE;
643 l_last_not_null INTEGER;
644 l_cur2 cursor_ref_type;
645 l_count NUMBER;
646 BEGIN
647 OPEN l_cur;
648 FETCH l_cur INTO l_rec;
649 IF l_cur%notfound THEN
650 CLOSE l_cur;
651 RAISE no_data_found;
652 END IF;
653 CLOSE l_cur;
654
655 l_segment_values.segment1 := l_rec.segment1;
656 l_segment_values.segment2 := l_rec.segment2;
657 l_segment_values.segment3 := l_rec.segment3;
658 l_segment_values.segment4 := l_rec.segment4;
659 l_segment_values.segment5 := l_rec.segment5;
660 l_segment_values.segment6 := l_rec.segment6;
661 l_segment_values.segment7 := l_rec.segment7;
662 l_segment_values.segment8 := l_rec.segment8;
663 l_segment_values.segment9 := l_rec.segment9;
664 l_segment_values.segment10 := l_rec.segment10;
665 l_segment_values.segment11 := l_rec.segment11;
666 l_segment_values.segment12 := l_rec.segment12;
667 l_segment_values.segment13 := l_rec.segment13;
668 l_segment_values.segment14 := l_rec.segment14;
669 l_segment_values.segment15 := l_rec.segment15;
670 l_segment_values.segment16 := l_rec.segment16;
671 l_segment_values.segment17 := l_rec.segment17;
672 l_segment_values.segment18 := l_rec.segment18;
673 l_segment_values.segment19 := l_rec.segment19;
674 l_segment_values.segment20 := l_rec.segment20;
675
676 -- bug 4157752
677 -- l_last_not_null should be initialized
678 l_last_not_null := 0;
679
680 FOR l_index IN REVERSE 1..g_product_segment_count LOOP
681 l_segment_value := get_segment_value(l_index, l_segment_values);
682 IF l_segment_value IS NOT NULL THEN
683 l_last_not_null := l_index;
684 EXIT;
685 END IF;
686 END LOOP;
687
688 IF l_last_not_null = g_product_segment_count THEN
689 -- the classification has values in all product segments
690 -- so it would not have any children sub categories
691 x_has_subcategory := 'N';
692 RETURN;
693 END IF;
694
695 l_query := 'select mcb.category_id from mtl_categories_b mcb, ' ||
696 ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
697 ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
698 ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
699 ' and mcb.category_id = mcsvc.category_id ' ||
700 ' and psps.classification_id = :1 and (';
701 l_count := 0;
702 FOR l_index IN 1..l_last_not_null LOOP
703 IF l_index > 1 THEN
704 l_query := l_query || ' and ';
705 END IF;
706 l_count := l_count + 1;
707 l_query := l_query || ' psps.' || g_product_segments(l_index).column_name ||
708 ' = mcb. ' || g_product_segments(l_index).column_name;
709 END LOOP;
710 /*Added as part of Bug 8611906 the query build was not proper without this*/
711 IF(l_count >= 1) THEN
712 l_query := l_query || ' and psps.' || g_product_segments(l_last_not_null + 1).column_name || ' is null ' ||
713 ' and mcb.' || g_product_segments(l_last_not_null + 1).column_name || ' is not null) and rownum < 2';
714 ELSE
715 l_query := l_query || ' psps.' || g_product_segments(l_last_not_null + 1).column_name || ' is null ' ||
716 ' and mcb.' || g_product_segments(l_last_not_null + 1).column_name || ' is not null) and rownum < 2';
717 END IF;
718 /*Added as part of Bug 8611906 the query build was not proper without this*/
719
720 OPEN l_cur2 FOR l_query using p_classification_id;
721 FETCH l_cur2 INTO l_count;
722 IF l_cur2%notfound THEN
723 x_has_subcategory := 'N';
724 ELSE
725 x_has_subcategory := 'Y';
726 END IF;
727 CLOSE l_cur2;
728 END check_subcategory;
729
730 --
731 -- before calling other procedures in this package,
732 -- call this procedure to initialize
733 --
734 -- x_status: Y or N for success or failure
735 -- x_error_message: an error message if there is an error
736 --
737 PROCEDURE initialize
738 (x_status OUT NOCOPY VARCHAR2,
739 x_error_message OUT NOCOPY VARCHAR2
740 ) IS
741 BEGIN
742 IF g_product_segment_definition IS NULL THEN
743 do_init(x_status, x_error_message);
744 IF x_status = g_no THEN
745 RETURN;
746 END IF;
747 END IF;
748
749 x_status := g_yes;
750 x_error_message := NULL;
751 END initialize;
752
753 PROCEDURE get_product_meta_data
754 (x_product_segment_definition OUT NOCOPY VARCHAR2,
755 x_product_segment_count OUT NOCOPY NUMBER,
756 x_default_po_category_set_id OUT NOCOPY NUMBER,
757 x_delimiter OUT NOCOPY VARCHAR2
758 ) IS
759 BEGIN
760 assert_init();
761 x_product_segment_definition := g_product_segment_definition;
762 x_product_segment_count := g_product_segment_count;
763 x_default_po_category_set_id := g_default_po_category_set_id;
764 x_delimiter := g_delimiter;
765 END get_product_meta_data;
766
767 PROCEDURE get_product_segment_info
768 (p_index IN NUMBER,
769 x_column_name OUT NOCOPY VARCHAR2,
770 x_value_set_id OUT NOCOPY NUMBER,
771 x_validation_type OUT NOCOPY VARCHAR2,
772 x_table_name OUT NOCOPY VARCHAR2,
773 x_meaning_column OUT NOCOPY VARCHAR2,
774 x_id_column OUT NOCOPY VARCHAR2,
775 x_value_column OUT NOCOPY VARCHAR2,
776 x_where_clause OUT NOCOPY VARCHAR2,
777 x_parent_segment_index OUT NOCOPY INTEGER
778 ) IS
779 BEGIN
780 assert_init();
781
782 IF p_index IS NULL OR p_index > g_product_segment_count OR p_index < 0 THEN
783 raise_application_error
784 (-2000, 'invalid parameter value for p_index: ' || p_index);
785 END IF;
786
787 x_column_name := g_product_segments(p_index).column_name;
788 x_value_set_id := g_product_segments(p_index).value_set_id;
789 x_validation_type := g_product_segments(p_index).validation_type;
790 x_table_name := g_product_segments(p_index).table_name;
791 x_meaning_column := g_product_segments(p_index).meaning_column;
792 x_id_column := g_product_segments(p_index).id_column;
793 x_value_column := g_product_segments(p_index).value_column;
794 x_where_clause := g_product_segments(p_index).where_clause;
795 x_parent_segment_index := g_product_segments(p_index).parent_segment_index;
796 END get_product_segment_info;
797
798 -------------------------------------------------------------
799 -- given the values of a category (or selection), generate
800 -- the string 'desc.desc.desc'
801 -------------------------------------------------------------
802 PROCEDURE get_concatenated_description (
803 p_segments IN category_segment_record
804 , x_description OUT NOCOPY VARCHAR2 )
805 IS
806 l_segment_value mtl_categories_b.segment1%TYPE;
807 l_parent_segment_value mtl_categories_b.segment1%TYPE;
808 l_result VARCHAR2(3000);
809 BEGIN
810 assert_init();
811 --dbms_output.put_line('count ' || g_product_segment_count);
812 FOR l_index IN 1..g_product_segment_count LOOP
813 l_segment_value := get_segment_value(l_index, p_segments);
814 --dbms_output.put_line('value is '|| l_segment_value);
815 IF l_segment_value IS NULL THEN
816 EXIT;
817 END IF;
818 IF g_product_segments(l_index).validation_type = 'D' THEN
819 l_parent_segment_value :=
820 get_segment_value(g_product_segments(l_index).parent_segment_index, p_segments);
821 ELSE
822 l_parent_segment_value := NULL;
823 END IF;
824 IF l_index = 1 THEN
825 l_result := get_segment_value_desc(l_index, l_segment_value, l_parent_segment_value);
826 ELSE
827 l_result := l_result || g_delimiter ||
828 get_segment_value_desc(l_index, l_segment_value, l_parent_segment_value);
829 END IF;
830 END LOOP;
831 x_description := l_result;
832 --dbms_output.put_line(x_description);
833 END get_concatenated_description;
834
835 --
836 -- get the description of product and service for a row
837 -- in pos_sup_products_services table.
838 --
839 PROCEDURE get_product_description
840 (p_classification_id IN NUMBER, x_description OUT NOCOPY VARCHAR2 ) IS
841 CURSOR l_cur IS
842 SELECT * FROM pos_sup_products_services
843 WHERE classification_id = p_classification_id;
844 l_rec l_cur%ROWTYPE;
845 l_segment_values category_segment_record;
846 l_segment_value pos_sup_products_services.segment1%TYPE;
847 BEGIN
848 assert_init();
849 OPEN l_cur;
850 FETCH l_cur INTO l_rec;
851 IF l_cur%notfound THEN
852 CLOSE l_cur;
853 RAISE no_data_found;
854 END IF;
855 CLOSE l_cur;
856 l_segment_values.segment1 := l_rec.segment1;
857 l_segment_values.segment2 := l_rec.segment2;
858 l_segment_values.segment3 := l_rec.segment3;
859 l_segment_values.segment4 := l_rec.segment4;
860 l_segment_values.segment5 := l_rec.segment5;
861 l_segment_values.segment6 := l_rec.segment6;
862 l_segment_values.segment7 := l_rec.segment7;
863 l_segment_values.segment8 := l_rec.segment8;
864 l_segment_values.segment9 := l_rec.segment9;
865 l_segment_values.segment10 := l_rec.segment10;
866 l_segment_values.segment11 := l_rec.segment11;
867 l_segment_values.segment12 := l_rec.segment12;
868 l_segment_values.segment13 := l_rec.segment13;
869 l_segment_values.segment14 := l_rec.segment14;
870 l_segment_values.segment15 := l_rec.segment15;
871 l_segment_values.segment16 := l_rec.segment16;
872 l_segment_values.segment17 := l_rec.segment17;
873 l_segment_values.segment18 := l_rec.segment18;
874 l_segment_values.segment19 := l_rec.segment19;
875 l_segment_values.segment20 := l_rec.segment20;
876 get_concatenated_description(l_segment_values, x_description);
877 --dbms_output.put_line(x_description);
878 END get_product_description;
879
880 --
881 -- get the description of product and service represented
882 -- in the "category" format: 'value.value.valule..'
883 --
884 PROCEDURE get_product_description
885 (p_category IN VARCHAR2, x_description OUT NOCOPY VARCHAR2 ) IS
886
887 l_segment_values category_segment_record;
888 l_segment_value pos_sup_products_services.segment1%TYPE;
889
890 l_length NUMBER;
891 l_num_of_delim NUMBER;
892 l_index NUMBER;
893 l_delim_positions number_table;
894 l_start_pos NUMBER;
895 l_end_pos NUMBER;
896
897 BEGIN
898 assert_init();
899
900 l_length := length(p_category);
901 l_num_of_delim := 0;
902
903 -- count the delimiters
904 FOR l_index IN 1..l_length LOOP
905 IF substr(p_category,l_index,1) = g_delimiter THEN
906 l_num_of_delim := l_num_of_delim + 1;
907 l_delim_positions(l_num_of_delim) := l_index;
908 END IF;
909 END LOOP;
910
911 -- extract segment values
912 FOR l_index IN 1..l_num_of_delim + 1 LOOP
913 IF l_index = 1 THEN
914 l_start_pos := 1;
915 ELSE
916 l_start_pos := l_delim_positions(l_index-1) + 1;
917 END IF;
918 IF l_index <= l_num_of_delim THEN
919 l_end_pos := l_delim_positions(l_index) - 1;
920 ELSE
921 l_end_pos := l_length;
922 END IF;
923 l_segment_value := substr(p_category,
924 l_start_pos,
925 l_end_pos - l_start_pos + 1);
926 set_segment_value(l_index, l_segment_value, l_segment_values);
927 END LOOP;
928
929 get_concatenated_description(l_segment_values, x_description);
930 --dbms_output.put_line(x_description);
931 END get_product_description;
932
933 -- get the description of product and service for a row
934 -- in pos_sup_products_services table, and whether there
935 -- is a subcategories for the product and service
936 PROCEDURE get_desc_check_subcategory
937 (p_classification_id IN NUMBER,
938 x_description OUT NOCOPY VARCHAR2,
939 x_has_subcategory OUT NOCOPY VARCHAR2 -- return Y or N
940 ) IS
941 BEGIN
942 get_product_description(p_classification_id, x_description);
943 check_subcategory(p_classification_id,x_has_subcategory);
944 END get_desc_check_subcategory;
945
946 FUNCTION debug_to_string RETURN VARCHAR2
947 IS
948 l_var VARCHAR2(3000);
949 BEGIN
950 assert_init();
951
952 l_var := 'default po category set id = ' || g_default_po_category_set_id ||
953 ', product segment definition = ' || g_product_segment_definition ||
954 ', product segment count = ' || g_product_segment_count;
955
956 FOR l_index IN 1..g_product_segment_count LOOP
957 l_var := l_var || '; product segment ' || l_index ||
958 ' - column name = ' || g_product_segments(l_index).column_name ||
959 ', value set id = ' || g_product_segments(l_index).value_set_id ||
960 ', validation_type = ' || g_product_segments(l_index).validation_type;
961
962 IF g_product_segments(l_index).validation_type = 'F' THEN -- table
963 l_var := l_var || ', table_name = ' || g_product_segments(l_index).table_name ||
964 ', meaning_column = ' || g_product_segments(l_index).meaning_column ||
965 ', id_column = ' || g_product_segments(l_index).id_column ||
966 ', value_column = ' || g_product_segments(l_index).value_column ||
967 ', where_clause = ' || g_product_segments(l_index).where_clause;
968 END IF;
969
970 l_var := l_var || '.';
971 END LOOP;
972 RETURN l_var;
973 END debug_to_string;
974
975 FUNCTION get_vendor_by_category_query RETURN VARCHAR2
976 IS
977 l_query VARCHAR2(4000);
978 BEGIN
979 assert_init();
980 l_query := 'select psps.vendor_id, mcb.category_id from mtl_categories_b mcb, ' ||
981 ' mtl_category_set_valid_cats mcsvc, pos_sup_products_services psps where ' ||
982 ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
983 ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
984 ' and mcb.category_id = mcsvc.category_id and (';
985 FOR l_index IN 1..g_product_segment_count LOOP
986 IF l_index > 1 THEN
987 l_query := l_query || ' or ';
988 END IF;
989
990 FOR l_index2 IN 1..l_index LOOP
991 IF l_index2 > 1 THEN
992 l_query := l_query || ' and ';
993 END IF;
994 l_query := l_query || ' psps.' || g_product_segments(l_index).column_name ||
995 ' = mcb.' || g_product_segments(l_index).column_name;
996 END LOOP;
997
998 FOR l_index2 IN l_index+1..g_product_segment_count LOOP
999 l_query := l_query || ' and psps.' || g_product_segments(l_index).column_name || ' is null';
1000 END LOOP;
1001 END LOOP;
1002 l_query := l_query || ')';
1003 RETURN l_query;
1004 END get_vendor_by_category_query;
1005 /* Added following for P and S ER 7482793 */
1006
1007 -- Insert the validation set values into global temp table for each of the
1008 -- segments enabled for products and services
1009
1010 PROCEDURE insert_into_glb_temp
1011 (
1012 p_validation_type IN VARCHAR2,
1013 p_curr_seg_val_id IN NUMBER,
1014 p_parent_seg_val_id IN NUMBER,
1015 p_table_name IN VARCHAR2,
1016 p_where_clause IN VARCHAR2,
1017 p_meaning IN VARCHAR2,
1018 p_id_column IN VARCHAR2,
1019 p_value_column IN VARCHAR2,
1020 p_column_name IN VARCHAR2,
1021 p_parent_column_name IN VARCHAR2,
1022 x_return_status OUT nocopy VARCHAR2,
1023 x_msg_count OUT nocopy NUMBER,
1024 x_msg_data OUT nocopy VARCHAR2
1025 ) IS
1026 l_sql varchar2(4000) := null;
1027 l_tbl_sql varchar2(4000) := null;
1028 l_valorid_col varchar2(300) := null;
1029 l_tblcur cursor_ref_type;
1030 l_description varchar2(2000);
1031 l_value varchar2(2000);
1032 l_value_id varchar2(2000);
1033 l_parent_value varchar2(2000);
1034 l_parent_value_id varchar2(2000);
1035 l_parent_value2 varchar2(2000);
1036 l_parent_value_id2 varchar2(2000);
1037 l_cur cursor_ref_type;
1038 l_count NUMBER;
1039 l_parent_column_name VARCHAR2(20);
1040
1041
1042 BEGIN
1043
1044 /*Bug 9043064 (FP 9011350) Added the following debug statements.*/
1045
1046 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1047 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_validation_type : '||p_validation_type);
1048 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_curr_seg_val_id : '||p_curr_seg_val_id);
1049 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_seg_val_id : '||p_parent_seg_val_id);
1050 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_table_name : '||p_table_name);
1051 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_where_clause : '||p_where_clause);
1052 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_meaning : '||p_meaning);
1053 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_id_column : '||p_id_column);
1054 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_value_column : '||p_value_column);
1055 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_column_name : '||p_column_name);
1056 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'p_parent_column_name : '||p_parent_column_name);
1057 END IF;
1058
1059 /*Modified as as part of bug 8611906 considering where clause can be null also and syncind data in global temp table with other VO used fro browse specific*/
1060 if (p_validation_type = 'I') then
1061
1062 /*Bug 9043064 (FP 9011350) Added a condition to pick the categories only that are viewable by supplier.*/
1063 l_tbl_sql := 'SELECT ffvl.flex_value,
1064 ffvl.description,
1065 ffvl.flex_value_id
1066 FROM fnd_flex_values_vl ffvl,
1067 mtl_categories_b mcb,
1068 mtl_category_set_valid_cats mcsvc
1069 WHERE ffvl.flex_value_set_id = '|| p_curr_seg_val_id ||
1070 ' AND mcb.category_id = mcsvc.category_id
1071 and (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'')
1072 AND mcsvc.category_set_id =' || g_default_po_category_set_id ||
1073 ' AND ffvl.flex_value = mcb.' || p_column_name || ' and not exists (select SEGMENT_VALUE_ID
1074 from pos_products_services_gt
1075 where SEGMENT_VALUE_ID = ffvl.flex_value_id )';
1076
1077 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (I) : '||l_tbl_sql);
1079 END IF;
1080
1081 OPEN l_tblcur for l_tbl_sql;
1082 loop
1083 FETCH l_tblcur INTO l_value , l_description , l_value_id ;
1084
1085 EXIT WHEN l_tblcur%NOTFOUND;
1086 select Count(*) INTO l_count FROM pos_products_services_gt WHERE SEGMENT_VALUE_ID = l_value_id ;
1087 IF(l_count = 0) THEN
1088
1089 insert into pos_products_services_gt(SEGMENT_VALUE ,
1090 SEGMENT_VALUE_DESCRIPTION ,
1091 SUPPLIER_SELECTION ,
1092 PARENT_SEGMENT_VALUE ,
1093 PARENT_SEGMENT_VALUE_ID ,
1094 HIERARCHY_LEVEL ,
1095 SEGMENT_VALUE_ID ,
1096 LAST_UPDATE_DATE ,
1097 LAST_UPDATED_BY ,
1098 LAST_UPDATE_LOGIN ,
1099 CREATION_DATE ,
1100 CREATED_BY )
1101 values(l_value,
1102 l_description,
1103 null,
1104 null,
1105 null,
1106 null,
1107 l_value_id,
1108 sysdate,
1109 fnd_global.user_id,
1110 fnd_global.login_id,
1111 sysdate,
1112 fnd_global.user_id);
1113 END IF;
1114
1115 end loop;
1116 CLOSE l_tblcur;
1117
1118 elsif (p_validation_type = 'D') then
1119
1120 /*Bug 9043064 (FP 9011350) Added a condition to pick the categories only that are viewable by supplier.*/
1121
1122 l_sql := 'select ffvl.flex_value,
1123 ffvl.flex_value_id
1124 from fnd_flex_values_vl ffvl,
1125 mtl_categories_b mcb,
1126 mtl_category_set_valid_cats mcsvc
1127 where ffvl.flex_value_set_id = ' || p_parent_seg_val_id || ' AND
1128 mcb.category_id = mcsvc.category_id
1129 and (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'')
1130 AND mcsvc.category_set_id = '|| g_default_po_category_set_id ||
1131 ' AND ffvl.flex_value = mcb.' || p_parent_column_name ;
1132
1133 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1134 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_sql : '||l_sql);
1135 END IF;
1136
1137
1138 OPEN l_cur FOR l_sql;
1139 LOOP
1140 FETCH l_cur INTO l_parent_value , l_parent_value_id ;
1141 EXIT WHEN l_cur%NOTFOUND;
1142
1143 /*Bug 9043064 (FP 9011350) Added a condition to pick the categories only that are viewable by supplier.*/
1144 l_tbl_sql := 'SELECT ffvl2.flex_value,
1145 ffvl2.description,
1146 ffvl2.flex_value_id
1147 FROM fnd_flex_values_vl ffvl2,
1148 mtl_categories_b mcb1,
1149 mtl_category_set_valid_cats mcsvc1
1150 WHERE ffvl2.flex_value_set_id = ' || p_curr_seg_val_id || ' and
1151 mcb1.category_id = mcsvc1.category_id AND
1152 (mcb1.supplier_enabled_flag is null or mcb1.supplier_enabled_flag = ''Y'' or mcb1.supplier_enabled_flag = ''y'') and
1153 mcsvc1.category_set_id = '|| g_default_po_category_set_id || ' AND
1154 ffvl2.flex_value = mcb1.'|| p_column_name || ' and
1155 ffvl2.parent_flex_value_low = mcb1.'|| p_parent_column_name || ' and
1156 ffvl2.parent_flex_value_low = '||'''' || l_parent_value || '''' ||' and
1157 not exists (select SEGMENT_VALUE_ID
1158 from pos_products_services_gt
1159 where SEGMENT_VALUE_ID = ffvl2.flex_value_id)' ;
1160
1161 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1162 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1163 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (D) : '||l_tbl_sql);
1164 END IF;
1165
1166 OPEN l_tblcur for l_tbl_sql;
1167 loop
1168 FETCH l_tblcur INTO l_value , l_description , l_value_id ;
1169
1170 EXIT WHEN l_tblcur%NOTFOUND;
1171 select Count(*) INTO l_count FROM pos_products_services_gt WHERE SEGMENT_VALUE_ID = l_value_id ;
1172 IF(l_count = 0) THEN
1173 insert into pos_products_services_gt(SEGMENT_VALUE ,
1174 SEGMENT_VALUE_DESCRIPTION ,
1175 SUPPLIER_SELECTION ,
1176 PARENT_SEGMENT_VALUE ,
1177 PARENT_SEGMENT_VALUE_ID ,
1178 HIERARCHY_LEVEL ,
1179 SEGMENT_VALUE_ID ,
1180 LAST_UPDATE_DATE ,
1181 LAST_UPDATED_BY ,
1182 LAST_UPDATE_LOGIN ,
1183 CREATION_DATE ,
1184 CREATED_BY )
1185 values(l_value,
1186 l_description,
1187 null,
1188 l_parent_value,
1189 l_parent_value_id,
1190 null,
1191 l_value_id,
1192 sysdate,
1193 fnd_global.user_id,
1194 fnd_global.login_id,
1195 sysdate,
1196 fnd_global.user_id);
1197
1198 END IF;
1199 end loop;
1200 CLOSE l_tblcur;
1201
1202 end loop;
1203 CLOSE l_cur;
1204
1205 elsif (p_validation_type = 'F') then
1206 if (p_id_column is not null) then
1207 l_valorid_col := p_id_column;
1208 else
1209 l_valorid_col := p_value_column;
1210 end if;
1211
1212 IF(p_where_clause IS not null ) then
1213 -- Bug 9126584 (FP 9056630). Added spaces to construct the query string properly.
1214 l_tbl_sql := 'select distinct mcb.'||p_column_name || ' value,
1215 x.description description
1216 from mtl_categories_b mcb, mtl_category_set_valid_cats mcsvc,(SELECT '||p_meaning||' description, '||
1217 l_valorid_col||' value '||
1218 ' FROM '||p_table_name||' '||
1219 p_where_clause||
1220 ') x where mcb.' || p_column_name ||' is not null
1221 and (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y''
1222 or mcb.supplier_enabled_flag = ''y'')
1223 and mcb.category_id = mcsvc.category_id and mcsvc.category_set_id ='||g_default_po_category_set_id || ' and
1224 To_Char(x.value) = mcb.'||p_column_name|| ' AND NOT EXISTS (select SEGMENT_VALUE_ID from pos_products_services_gt where SEGMENT_VALUE_ID = x.value ) ';
1225
1226 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1227 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (T1) : '||l_tbl_sql);
1228 END IF;
1229
1230 ELSE
1231 l_tbl_sql := 'select distinct mcb.' ||p_column_name ||' value,
1232 x.description description
1233 from mtl_categories_b mcb, mtl_category_set_valid_cats mcsvc,'||'(SELECT '||p_meaning||' description, '||
1234 l_valorid_col||' value '||
1235 ' FROM '||p_table_name|| ') x where mcb.' || p_column_name ||' is not null
1236 and (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y''
1237 or mcb.supplier_enabled_flag = ''y'')
1238 and mcb.category_id = mcsvc.category_id and mcsvc.category_set_id ='||g_default_po_category_set_id || ' and
1239 To_Char(x.value) = mcb.'||p_column_name||
1240
1241 ' and NOT EXISTS (select SEGMENT_VALUE_ID from pos_products_services_gt where SEGMENT_VALUE_ID = x.value ) ';
1242
1243 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1244 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.pos_product_service_utl_pkg.insert_into_glb_temp.begin', 'l_tbl_sql (T2) : '||l_tbl_sql);
1245 END IF;
1246
1247 END IF;
1248
1249 OPEN l_tblcur for l_tbl_sql;
1250 loop
1251 FETCH l_tblcur INTO l_value , l_description;
1252 EXIT WHEN l_tblcur%NOTFOUND;
1253 insert into pos_products_services_gt(SEGMENT_VALUE ,
1254 SEGMENT_VALUE_DESCRIPTION ,
1255 SUPPLIER_SELECTION ,
1256 PARENT_SEGMENT_VALUE ,
1257 PARENT_SEGMENT_VALUE_ID ,
1258 HIERARCHY_LEVEL ,
1259 SEGMENT_VALUE_ID ,
1260 LAST_UPDATE_DATE ,
1261 LAST_UPDATED_BY ,
1262 LAST_UPDATE_LOGIN ,
1263 CREATION_DATE ,
1264 CREATED_BY )
1265 values(l_value,
1266 l_description,
1267 null,
1268 null,
1269 null,
1270 null,
1271 l_value,
1272 sysdate,
1273 fnd_global.user_id,
1274 fnd_global.login_id,
1275 sysdate,
1276 fnd_global.user_id);
1277
1278 end loop;
1279 CLOSE l_tblcur;
1280 end if;
1281 /*Modified as as part of bug 8611906 considering where clause can be null also and syncind data in global temp table with other VO used fro browse specific*/
1282 commit;
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 x_return_status := FND_API.G_RET_STS_SUCCESS;
1286 x_msg_data := 'Insert into Global temp failed ';
1287 rollback;
1288 raise_application_error(-20013, x_msg_data, true);
1289
1290 END insert_into_glb_temp;
1291
1292 /* Function to get concatenated segment value description */
1293
1294 FUNCTION get_segment_value_description(x_segment_value_id VARCHAR2)
1295 RETURN VARCHAR2
1296 IS
1297 l_contact_description VARCHAR2(4000) := null;
1298 l_segment_value_description VARCHAR2(240) := null;
1299 CURSOR c1 IS
1300 SELECT SEGMENT_VALUE_DESCRIPTION
1301 FROM pos_products_services_gt
1302 START WITH segment_value_id = x_segment_value_id
1303 CONNECT BY PRIOR parent_segment_value_id =segment_value_id
1304 ORDER BY parent_segment_value_id DESC;
1305
1306 BEGIN
1307 OPEN c1;
1308 LOOP
1309 FETCH c1 INTO l_segment_value_description;
1310 EXIT WHEN C1%NOTFOUND;
1311 IF (l_segment_value_description IS NOT NULL AND l_contact_description IS NOT NULL) THEN
1312 l_contact_description := l_contact_description||g_delimiter;
1313 l_contact_description := l_contact_description||l_segment_value_description;
1314 ELSIF (l_segment_value_description IS NOT NULL AND l_contact_description IS NULL) THEN
1315 l_contact_description := l_segment_value_description;
1316 END IF;
1317 END LOOP;
1318 CLOSE c1;
1319
1320 RETURN (l_contact_description);
1321 END get_segment_value_description;
1322
1323 /* Function to get concatenated segment value code */
1324
1325 FUNCTION get_segment_value_code(x_segment_value_id VARCHAR2)
1326 RETURN VARCHAR2
1327 IS
1328 l_contact_code VARCHAR2(4000) := null;
1329 l_segment_value_code VARCHAR2(240) := null;
1330 CURSOR c1 IS
1331 SELECT SEGMENT_VALUE
1332 FROM pos_products_services_gt
1333 START WITH segment_value_id = x_segment_value_id
1334 CONNECT BY PRIOR parent_segment_value_id =segment_value_id
1335 ORDER BY parent_segment_value_id DESC;
1336
1337 BEGIN
1338 OPEN c1;
1339 LOOP
1340 FETCH c1 INTO l_segment_value_code;
1341 EXIT WHEN C1%NOTFOUND;
1342 IF (l_segment_value_code IS NOT NULL AND l_contact_code IS NOT NULL) THEN
1343 l_contact_code := l_contact_code || g_delimiter;
1344 l_contact_code := l_contact_code || l_segment_value_code;
1345 ELSIF (l_segment_value_code IS NOT NULL AND l_contact_code IS NULL) THEN
1346 l_contact_code := l_segment_value_code;
1347 END IF;
1348 END LOOP;
1349 CLOSE c1;
1350
1351 RETURN (l_contact_code);
1352 END get_segment_value_code;
1353
1354 /* Function to get classification id for a segment value code for the current vendor */
1355
1356 FUNCTION get_classid(x_segment_code in VARCHAR2,x_vendor_id in NUMBER)
1357 RETURN NUMBER
1358 IS
1359 TYPE cursor_ref_type IS REF CURSOR;
1360 l_start_pos number := 0;
1361 l_index number := 0;
1362 l_concat_value varchar2(4000) := null;
1363 l_next_seg varchar2(4000) := null;
1364 l_class_id number := 0;
1365 l_segcode_val varchar2(2000) := null;
1366 l_sql varchar2(4000) := null;
1367 l_parent_sql varchar2(4000) := null;
1368 l_pscur cursor_ref_type;
1369 l_seg_def fnd_profile_option_values.profile_option_value%TYPE;
1370 l_product_segment_profile_name CONSTANT VARCHAR2(30) := 'POS_PRODUCT_SERVICE_SEGMENTS';
1371
1372 begin
1373
1374 l_seg_def := fnd_profile.value(l_product_segment_profile_name);
1375 l_segcode_val := x_segment_code;
1376
1377 while (length(l_seg_def)) > l_start_pos
1378 loop
1379 /*
1380 Bug 8358082 : delimiter of segment numbers in the profile is always '.'.
1381 it doesnot depend on the flex delimiter. in the below statement replace
1382 g_delimiter with '.'
1383 */
1384 l_index := instr(l_seg_def,'.',l_start_pos+1);
1385 if (l_index = 0) then
1386 exit;
1387 end if;
1388 l_concat_value := l_concat_value||'segment'||substr(l_seg_def,l_start_pos+1,(l_index-l_start_pos-1))||'||'||''''||g_delimiter||''''||'||';
1389 l_start_pos := l_index;
1390 end loop;
1391 l_concat_value := l_concat_value||'segment'||substr(l_seg_def,l_start_pos+1);
1392
1393
1394
1395 l_sql := 'SELECT classification_id
1396 FROM pos_sup_products_services
1397 WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1398 'AND status <> '||''''||'X'||''''||
1399 'AND vendor_id = '||x_vendor_id||
1400 'union all
1401 select PS_REQUEST_ID classification_id
1402 from POS_PRODUCT_SERVICE_REQUESTS psr , POS_SUPPLIER_MAPPINGS pmapp
1403 WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1404 'and pmapp.mapping_id = psr.MAPPING_ID
1405 AND (psr.REQUEST_STATUS =''PENDING'' or psr.REQUEST_STATUS =''REJECTED'')
1406 AND pmapp.vendor_id = '||x_vendor_id ;
1407
1408
1409 OPEN l_pscur for l_sql;
1410 FETCH l_pscur INTO l_class_id;
1411 CLOSE l_pscur;
1412
1413
1414 /* check if the immediate parent has been already selected. If so return the classification Id of the parent for the children .
1415 This will disable the children on the screen so user cannot select them again
1416 */
1417
1418 if (l_class_id = 0) then
1419 if (instr(l_concat_value,g_delimiter,-1) <> 0 ) then
1420 l_next_seg := substr(l_concat_value,instr(l_concat_value,g_delimiter,-1)+4);
1421 l_concat_value := substr(l_concat_value,1,(instr(l_concat_value,g_delimiter,-1)-4));
1422 end if;
1423 if (instr(l_segcode_val,g_delimiter,-1) <> 0) then
1424 l_segcode_val := substr(l_segcode_val,1,(instr(l_segcode_val,g_delimiter,-1)-1));
1425 end if;
1426
1427
1428 l_parent_sql := 'SELECT classification_id
1429 FROM pos_sup_products_services
1430 WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1431 'AND '||l_next_seg||' is null '||
1432 'AND status <> '||''''||'X'||''''||
1433 'AND vendor_id = '||x_vendor_id||
1434 'union all
1435 select PS_REQUEST_ID classification_id
1436 from POS_PRODUCT_SERVICE_REQUESTS psr , POS_SUPPLIER_MAPPINGS pmapp
1437 WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1438 'AND '||l_next_seg||' is null '||
1439 'and pmapp.mapping_id = psr.MAPPING_ID
1440 AND (psr.REQUEST_STATUS ='||''''||'PENDING'||''''|| 'or psr.REQUEST_STATUS ='||''''|| 'REJECTED'||''''||')
1441 AND pmapp.vendor_id = '||x_vendor_id ;
1442
1443
1444 OPEN l_pscur for l_parent_sql;
1445 FETCH l_pscur INTO l_class_id;
1446 CLOSE l_pscur;
1447
1448 end if;
1449
1450 return l_class_id;
1451
1452 end;
1453 /*Function to get request id for prospective supplier user*/
1454 FUNCTION get_requestid(x_segment_code in VARCHAR2,x_mapp_id IN NUMBER )
1455 RETURN NUMBER
1456 IS
1457 TYPE cursor_ref_type IS REF CURSOR;
1458 l_start_pos number := 0;
1459 l_index number := 0;
1460 l_concat_value varchar2(4000) := null;
1461 l_next_seg varchar2(4000) := null;
1462 l_class_id number := 0;
1463 l_segcode_val varchar2(2000) := null;
1464 l_sql varchar2(4000) := null;
1465 l_parent_sql varchar2(4000) := null;
1466 l_pscur cursor_ref_type;
1467 l_seg_def fnd_profile_option_values.profile_option_value%TYPE;
1468 l_product_segment_profile_name CONSTANT VARCHAR2(30) := 'POS_PRODUCT_SERVICE_SEGMENTS';
1469
1470
1471 begin
1472
1473 l_seg_def := fnd_profile.value(l_product_segment_profile_name);
1474
1475 l_segcode_val := x_segment_code;
1476
1477 while (length(l_seg_def)) > l_start_pos
1478 loop
1479 /*
1480 Bug 8358082 : delimiter of segment numbers in the profile is always '.'.
1481 it doesnot depend on the flex delimiter. in the below statement replace
1482 g_delimiter with '.'
1483 */
1484 l_index := instr(l_seg_def,'.',l_start_pos+1);
1485 if (l_index = 0) then
1486 exit;
1487 end if;
1488 l_concat_value := l_concat_value||'segment'||substr(l_seg_def,l_start_pos+1,(l_index-l_start_pos-1))||'||'||''''||g_delimiter||''''||'||';
1489 l_start_pos := l_index;
1490 end loop;
1491 l_concat_value := l_concat_value||'segment'||substr(l_seg_def,l_start_pos+1);
1492
1493
1494
1495 l_sql := 'select PS_REQUEST_ID
1496 from POS_PRODUCT_SERVICE_REQUESTS
1497 WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1498 'and MAPPING_ID='||x_mapp_id||
1499 'AND REQUEST_STATUS =''PENDING'' ';
1500
1501
1502
1503 OPEN l_pscur for l_sql;
1504 FETCH l_pscur INTO l_class_id;
1505 CLOSE l_pscur;
1506
1507
1508 /* check if the immediate parent has been already selected. If so return the classification Id of the parent for the children .
1509 This will disable the children on the screen so user cannot select them again
1510 */
1511
1512 if (l_class_id = 0) then
1513 if (instr(l_concat_value,g_delimiter,-1) <> 0 ) then
1514 l_next_seg := substr(l_concat_value,instr(l_concat_value,g_delimiter,-1)+4);
1515 l_concat_value := substr(l_concat_value,1,(instr(l_concat_value,g_delimiter,-1)-4));
1516 end if;
1517 if (instr(l_segcode_val,g_delimiter,-1) <> 0) then
1518 l_segcode_val := substr(l_segcode_val,1,(instr(l_segcode_val,g_delimiter,-1)-1));
1519 end if;
1520
1521
1522 l_parent_sql := '
1523 select PS_REQUEST_ID
1524 from POS_PRODUCT_SERVICE_REQUESTS
1525 WHERE '||l_concat_value||'='||''''||l_segcode_val||''''||
1526 'AND '||l_next_seg||' is null '||
1527 'and MAPPING_ID='||x_mapp_id||
1528 'AND REQUEST_STATUS =''PENDING''';
1529
1530
1531
1532
1533 OPEN l_pscur for l_parent_sql;
1534 FETCH l_pscur INTO l_class_id;
1535 CLOSE l_pscur;
1536
1537 end if;
1538
1539 return l_class_id;
1540
1541 end;
1542
1543
1544 /* Function to get Concatenate Code value for a given classification id */
1545
1546 FUNCTION get_concat_code(x_classification_id in varchar2)
1547 RETURN VARCHAR2 IS
1548 l_index number := 0;
1549 l_concat_code varchar2(4000) := null;
1550 l_segcode_val varchar2(2000) := null;
1551
1552 Cursor ps_code_cur is
1553 (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
1554 segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
1555 from pos_sup_products_services
1556 where classification_id = x_classification_id)
1557 UNION ALL
1558 (select segment1 , segment2, segment3 , segment4,segment5 , segment6,segment7 , segment8,segment9 , segment10,
1559 segment11 , segment12,segment13 , segment14,segment15 , segment16,segment17 , segment18,segment19 , segment20
1560 from POS_PRODUCT_SERVICE_REQUESTS
1561 where PS_REQUEST_ID = x_classification_id);
1562
1563 l_rec category_segment_record;
1564
1565 BEGIN
1566
1567 OPEN ps_code_cur;
1568 FETCH ps_code_cur INTO l_rec;
1569 IF ps_code_cur%notfound THEN
1570 CLOSE ps_code_cur;
1571 RAISE no_data_found;
1572 END IF;
1573 CLOSE ps_code_cur;
1574
1575
1576 FOR l_index IN 1..g_product_segment_count LOOP
1577 l_segcode_val := get_segment_value(l_index, l_rec);
1578 if l_segcode_val is null then
1579 EXIT;
1580 else
1581 if l_index = 1 then
1582 l_concat_code := l_segcode_val;
1583 else
1584 l_concat_code := l_concat_code||g_delimiter||l_segcode_val;
1585 end if;
1586 end if;
1587 END LOOP;
1588
1589 return l_concat_code;
1590
1591 end;
1592 /*End of P&S ER*/
1593 PROCEDURE check_req_subcategory
1594 (p_segment_values IN category_segment_record,
1595 p_ps_request_id IN NUMBER,
1596 x_has_subcategory OUT NOCOPY VARCHAR2)
1597 IS
1598 l_query VARCHAR2(3000);
1599 l_segment_value pos_sup_products_services.segment1%TYPE;
1600 l_last_not_null INTEGER;
1601 l_cur2 cursor_ref_type;
1602 l_count NUMBER;
1603 BEGIN
1604 l_last_not_null := 0;
1605
1606 FOR l_index IN REVERSE 1..g_product_segment_count LOOP
1607 l_segment_value := get_segment_value(l_index, p_segment_values);
1608 IF l_segment_value IS NOT NULL THEN
1609 l_last_not_null := l_index;
1610 EXIT;
1611 END IF;
1612 END LOOP;
1613
1614 IF l_last_not_null = g_product_segment_count THEN
1615 -- the classification has values in all product segments
1616 -- so it would not have any children sub categories
1617 x_has_subcategory := 'N';
1618 RETURN;
1619 END IF;
1620
1621 l_query := 'select mcb.category_id from mtl_categories_b mcb, ' ||
1622 ' mtl_category_set_valid_cats mcsvc, pos_product_service_requests ppsr where ' ||
1623 ' (mcb.supplier_enabled_flag is null or mcb.supplier_enabled_flag = ''Y'' or mcb.supplier_enabled_flag = ''y'') ' ||
1624 ' and mcsvc.category_set_id = ' || g_default_po_category_set_id ||
1625 ' and mcb.category_id = mcsvc.category_id ' ||
1626 ' and ppsr.ps_request_id = :1 and (';
1627 l_count := 0;
1628 FOR l_index IN 1..l_last_not_null LOOP
1629 IF l_index > 1 THEN
1630 l_query := l_query || ' and ';
1631 END IF;
1632 l_count := l_count + 1;
1633
1634 l_query := l_query || ' ppsr.' || g_product_segments(l_index).column_name ||
1635 ' = mcb. ' || g_product_segments(l_index).column_name;
1636 END LOOP;
1637 /*Added as part of Bug 8611906 the query build was not proper without this*/
1638 IF (l_count >= 1) THEN
1639
1640 l_query := l_query || ' and ppsr.' || g_product_segments(l_last_not_null + 1).column_name || ' is null ' ||
1641 ' and mcb.' || g_product_segments(l_last_not_null + 1).column_name || ' is not null) and rownum < 2';
1642 ELSE
1643 l_query := l_query || ' ppsr.' || g_product_segments(l_last_not_null + 1).column_name || ' is null ' ||
1644 ' and mcb.' || g_product_segments(l_last_not_null + 1).column_name || ' is not null) and rownum < 2';
1645 END IF;
1646 /*Added as part of Bug 8611906 the query build was not proper without this*/
1647
1648
1649 OPEN l_cur2 FOR l_query using p_ps_request_id;
1650 FETCH l_cur2 INTO l_count;
1651 IF l_cur2%notfound THEN
1652 x_has_subcategory := 'N';
1653 ELSE
1654 x_has_subcategory := 'Y';
1655 END IF;
1656 CLOSE l_cur2;
1657 END check_req_subcategory;
1658
1659 PROCEDURE get_req_desc_has_sub
1660 (p_ps_request_id IN NUMBER,
1661 x_description OUT NOCOPY VARCHAR2,
1662 x_has_subcategory OUT nocopy VARCHAR2
1663 )
1664 IS
1665 CURSOR l_cur IS
1666 SELECT * FROM pos_product_service_requests
1667 WHERE ps_request_id = p_ps_request_id;
1668 l_rec l_cur%ROWTYPE;
1669 l_segment_values category_segment_record;
1670 l_segment_value pos_product_service_requests.segment1%TYPE;
1671 BEGIN
1672 assert_init();
1673 OPEN l_cur;
1674 FETCH l_cur INTO l_rec;
1675 IF l_cur%notfound THEN
1676 CLOSE l_cur;
1677 RAISE no_data_found;
1678 END IF;
1679 CLOSE l_cur;
1680 l_segment_values.segment1 := l_rec.segment1;
1681 l_segment_values.segment2 := l_rec.segment2;
1682 l_segment_values.segment3 := l_rec.segment3;
1683 l_segment_values.segment4 := l_rec.segment4;
1684 l_segment_values.segment5 := l_rec.segment5;
1685 l_segment_values.segment6 := l_rec.segment6;
1686 l_segment_values.segment7 := l_rec.segment7;
1687 l_segment_values.segment8 := l_rec.segment8;
1688 l_segment_values.segment9 := l_rec.segment9;
1689 l_segment_values.segment10 := l_rec.segment10;
1690 l_segment_values.segment11 := l_rec.segment11;
1691 l_segment_values.segment12 := l_rec.segment12;
1692 l_segment_values.segment13 := l_rec.segment13;
1693 l_segment_values.segment14 := l_rec.segment14;
1694 l_segment_values.segment15 := l_rec.segment15;
1695 l_segment_values.segment16 := l_rec.segment16;
1696 l_segment_values.segment17 := l_rec.segment17;
1697 l_segment_values.segment18 := l_rec.segment18;
1698 l_segment_values.segment19 := l_rec.segment19;
1699 l_segment_values.segment20 := l_rec.segment20;
1700
1701 get_concatenated_description(l_segment_values, x_description);
1702
1703 check_req_subcategory(l_segment_values, p_ps_request_id, x_has_subcategory);
1704
1705 END get_req_desc_has_sub;
1706
1707 PROCEDURE add_new_ps_req
1708 (p_vendor_id IN NUMBER,
1709 p_segment1 IN VARCHAR2,
1710 p_segment2 IN VARCHAR2,
1711 p_segment3 IN VARCHAR2,
1712 p_segment4 IN VARCHAR2,
1713 p_segment5 IN VARCHAR2,
1714 p_segment6 IN VARCHAR2,
1715 p_segment7 IN VARCHAR2,
1716 p_segment8 IN VARCHAR2,
1717 p_segment9 IN VARCHAR2,
1718 p_segment10 IN VARCHAR2,
1719 p_segment11 IN VARCHAR2,
1720 p_segment12 IN VARCHAR2,
1721 p_segment13 IN VARCHAR2,
1722 p_segment14 IN VARCHAR2,
1723 p_segment15 IN VARCHAR2,
1724 p_segment16 IN VARCHAR2,
1725 p_segment17 IN VARCHAR2,
1726 p_segment18 IN VARCHAR2,
1727 p_segment19 IN VARCHAR2,
1728 p_segment20 IN VARCHAR2,
1729 p_segment_definition IN VARCHAR2,
1730 x_return_status OUT nocopy VARCHAR2,
1731 x_msg_count OUT nocopy NUMBER,
1732 x_msg_data OUT nocopy VARCHAR2
1733 )
1734 IS
1735 l_mapping_id NUMBER;
1736 l_party_id NUMBER;
1737 l_count NUMBER;
1738
1739 BEGIN
1740
1741 /*
1742 * If the count of pos_suppliers mapping record of the party id is zero, then
1743 * insert a record into the pos_supplier_mappings table for corresponding vendor id and party id
1744 * else, use the existing mapping_id value of the party id to create a Pending Product and
1745 * Service Request.
1746 * Please refer the bug 7374266 for more information.
1747 */
1748
1749 SELECT party_id
1750 INTO l_party_id
1751 FROM AP_SUPPLIERS
1752 WHERE vendor_id = p_vendor_id;
1753
1754 SELECT COUNT(mapping_id)
1755 INTO l_count
1756 FROM pos_supplier_mappings
1757 WHERE party_id = l_party_id;
1758
1759 IF ( l_count = 0 ) then
1760 INSERT INTO pos_supplier_mappings
1761 (
1762 mapping_id, party_id , vendor_id ,
1763 created_by, creation_date,
1764 last_updated_by, last_update_date, last_update_login
1765 )
1766 VALUES
1767 (
1768 pos_supplier_mapping_s.nextval, l_party_id, p_vendor_id,
1769 fnd_global.user_id, sysdate,
1770 fnd_global.user_id, sysdate, fnd_global.login_id
1771 );
1772 END IF;
1773
1774 select mapping_id
1775 into l_mapping_id
1776 from Pos_supplier_mappings
1777 where vendor_id = p_vendor_id ;
1778
1779 INSERT INTO POS_PRODUCT_SERVICE_REQUESTS
1780 (
1781 PS_REQUEST_ID
1782 , MAPPING_ID
1783 , segment1
1784 , segment2
1785 , segment3
1786 , segment4
1787 , segment5
1788 , segment6
1789 , segment7
1790 , segment8
1791 , segment9
1792 , segment10
1793 , segment11
1794 , segment12
1795 , segment13
1796 , segment14
1797 , segment15
1798 , segment16
1799 , segment17
1800 , segment18
1801 , segment19
1802 , segment20
1803 , request_status
1804 , request_type
1805 , segment_definition
1806 , created_by
1807 , creation_date
1808 , last_updated_by
1809 , last_update_date
1810 , last_update_login
1811 )
1812 VALUES
1813 (
1814 POS_PRODUCT_SERVICE_REQ_S.NEXTVAL
1815 , l_mapping_id
1816 , p_segment1
1817 , p_segment2
1818 , p_segment3
1819 , p_segment4
1820 , p_segment5
1821 , p_segment6
1822 , p_segment7
1823 , p_segment8
1824 , p_segment9
1825 , p_segment10
1826 , p_segment11
1827 , p_segment12
1828 , p_segment13
1829 , p_segment14
1830 , p_segment15
1831 , p_segment16
1832 , p_segment17
1833 , p_segment18
1834 , p_segment19
1835 , p_segment20
1836 , 'PENDING'
1837 , 'ADD'
1838 , p_segment_definition
1839 , fnd_global.user_id
1840 , Sysdate
1841 , fnd_global.user_id
1842 , Sysdate
1843 , fnd_global.login_id
1844 );
1845 x_return_status := fnd_api.g_ret_sts_success;
1846
1847 end add_new_ps_req;
1848
1849 PROCEDURE update_main_ps_req
1850 ( p_req_id_tbl IN po_tbl_number,
1851 p_status IN VARCHAR2,
1852 x_return_status OUT nocopy VARCHAR2,
1853 x_msg_count OUT nocopy NUMBER,
1854 x_msg_data OUT nocopy VARCHAR2
1855 )
1856 IS
1857 BEGIN
1858
1859 for i in 1..p_req_id_tbl.COUNT LOOP
1860
1861 UPDATE POS_SUP_PRODUCTS_SERVICES
1862 SET status = p_status,
1863 last_updated_by = fnd_global.user_id,
1864 last_update_date = Sysdate,
1865 last_update_login = fnd_global.login_id
1866 WHERE classification_id = p_req_id_tbl(i);
1867
1868 END LOOP;
1869
1870 x_return_status := FND_API.G_RET_STS_SUCCESS;
1871
1872 END update_main_ps_req;
1873
1874 PROCEDURE remove_mult_ps_reqs
1875 ( p_req_id_tbl IN po_tbl_number,
1876 x_return_status OUT nocopy VARCHAR2,
1877 x_msg_count OUT nocopy NUMBER,
1878 x_msg_data OUT nocopy VARCHAR2
1879 )
1880 IS
1881 BEGIN
1882
1883 for i in 1..p_req_id_tbl.COUNT LOOP
1884 UPDATE POS_PRODUCT_SERVICE_REQUESTS
1885 SET request_status = 'DELETED',
1886 last_updated_by = fnd_global.user_id,
1887 last_update_date = Sysdate,
1888 last_update_login = fnd_global.login_id
1889 WHERE PS_REQUEST_ID = p_req_id_tbl(i);
1890 END LOOP;
1891
1892 x_return_status := FND_API.G_RET_STS_SUCCESS;
1893 END remove_mult_ps_reqs;
1894
1895 PROCEDURE approve_mult_temp_ps_reqs
1896 ( p_req_id_tbl IN po_tbl_number,
1897 x_return_status OUT nocopy VARCHAR2,
1898 x_msg_count OUT nocopy NUMBER,
1899 x_msg_data OUT nocopy VARCHAR2
1900 )
1901 IS
1902 BEGIN
1903
1904 for i in 1..p_req_id_tbl.COUNT LOOP
1905 UPDATE POS_PRODUCT_SERVICE_REQUESTS
1906 SET request_status = 'PENDING',
1907 request_type = 'ADD',
1908 last_updated_by = fnd_global.user_id,
1909 last_update_date = Sysdate,
1910 last_update_login = fnd_global.login_id
1911 WHERE PS_REQUEST_ID = p_req_id_tbl(i);
1912 END LOOP;
1913
1914 pos_profile_change_request_pkg.approve_mult_ps_reqs( p_req_id_tbl, x_return_status, x_msg_count, x_msg_data) ;
1915
1916 x_return_status := FND_API.G_RET_STS_SUCCESS;
1917 END approve_mult_temp_ps_reqs;
1918
1919 /* Begin Supplier Hub: Supplier Profile Workbench */
1920
1921 /*
1922 The table function 'product_service_ocv' is used for querying all possible
1923 Products and Services classifications, to be used in the Supplier Profile UDA
1924 codes.
1925
1926 Example usage:
1927 SELECT code, meaning
1928 FROM table(pos_product_service_utl_pkg.product_service_ocv)
1929 */
1930
1931 PROCEDURE insert_into_ocv_table
1932 (p_ocv_table IN OUT NOCOPY product_service_ocv_table,
1933 p_parent_val IN VARCHAR2,
1934 p_concat_parent_vals IN VARCHAR2,
1935 p_concat_parent_desc IN VARCHAR2,
1936 p_level IN NUMBER
1937 )
1938 IS
1939
1940 l_product_segment product_segment_record;
1941 l_curr_segment_vals product_service_ocv_table := product_service_ocv_table();
1942 l_tbl_sql VARCHAR2(4000);
1943 l_valorid_col VARCHAR2(240);
1944 l_tblcur cursor_ref_type;
1945 l_curr_val product_service_ocv_rec;
1946
1947 CURSOR l_flex_i_cursor(p_value_set_id NUMBER) IS
1948 SELECT flex_value, description
1949 FROM fnd_flex_values_vl
1950 WHERE flex_value_set_id = p_value_set_id;
1951
1952 CURSOR l_flex_d_cursor(p_value_set_id NUMBER, p_parent_val VARCHAR2) IS
1953 SELECT flex_value, description
1954 FROM fnd_flex_values_vl
1955 WHERE flex_value_set_id = p_value_set_id
1956 AND parent_flex_value_low = p_parent_val;
1957
1958 BEGIN
1959
1960 l_product_segment := g_product_segments(p_level);
1961
1962 IF (l_product_segment.validation_type = 'I') THEN
1963
1964 OPEN l_flex_i_cursor(l_product_segment.value_set_id);
1965 FETCH l_flex_i_cursor BULK COLLECT INTO l_curr_segment_vals;
1966 CLOSE l_flex_i_cursor;
1967
1968 ELSIF (l_product_segment.validation_type = 'D') THEN
1969
1970 OPEN l_flex_d_cursor(l_product_segment.value_set_id, p_parent_val);
1971 FETCH l_flex_d_cursor BULK COLLECT INTO l_curr_segment_vals;
1972 CLOSE l_flex_d_cursor;
1973
1974 ELSIF (l_product_segment.validation_type = 'F') THEN
1975
1976 IF (l_product_segment.id_column IS NOT NULL) THEN
1977 l_valorid_col := l_product_segment.id_column;
1978 ELSE
1979 l_valorid_col := l_product_segment.value_column;
1980 END IF;
1981
1982 -- Bug 13709295: Remove the 'WHERE' keyword
1983 l_tbl_sql := 'SELECT '||l_valorid_col||' value, '||
1984 l_product_segment.meaning_column||' description '||
1985 ' FROM ' ||l_product_segment.table_name||
1986 ' ' ||l_product_segment.where_clause;
1987
1988 OPEN l_tblcur FOR l_tbl_sql;
1989 FETCH l_tblcur BULK COLLECT INTO l_curr_segment_vals;
1990 CLOSE l_tblcur;
1991
1992 END IF;
1993
1994 -- Bug 10151022: Add a check on whether the segment contains any value
1995 -- Only proceed if the segment contains any value
1996 IF (l_curr_segment_vals.COUNT > 0) THEN
1997
1998 FOR i IN l_curr_segment_vals.FIRST..l_curr_segment_vals.LAST LOOP
1999 l_curr_val := l_curr_segment_vals(i);
2000
2001 IF (p_level > 1) THEN
2002 l_curr_val.code := p_concat_parent_vals || g_delimiter || l_curr_val.code;
2003 l_curr_val.meaning := p_concat_parent_desc || g_delimiter || l_curr_val.meaning;
2004 END IF;
2005
2006 p_ocv_table.EXTEND;
2007 p_ocv_table(p_ocv_table.COUNT) := l_curr_val;
2008
2009 IF (p_level < g_product_segment_count) THEN
2010 insert_into_ocv_table(p_ocv_table,
2011 l_curr_segment_vals(i).code,
2012 l_curr_val.code,
2013 l_curr_val.meaning,
2014 p_level + 1);
2015 END IF;
2016
2017 END LOOP;
2018
2019 END IF;
2020
2021 END insert_into_ocv_table;
2022
2023 FUNCTION product_service_ocv
2024 RETURN product_service_ocv_table PIPELINED
2025 IS
2026 l_status VARCHAR2(1);
2027 l_error_message VARCHAR2(2000);
2028 l_product_service_ocv product_service_ocv_table := product_service_ocv_table();
2029 l_concat_parent_vals VARCHAR2(1000);
2030 l_concat_parent_desc VARCHAR2(4000);
2031 BEGIN
2032
2033 IF g_initialized = FALSE THEN
2034 do_init(l_status, l_error_message);
2035 IF l_status = g_no THEN
2036 RETURN;
2037 END IF;
2038 END IF;
2039
2040 insert_into_ocv_table(l_product_service_ocv,
2041 null,
2042 l_concat_parent_vals,
2043 l_concat_parent_desc,
2044 1);
2045
2046 FOR i IN l_product_service_ocv.FIRST..l_product_service_ocv.LAST LOOP
2047 PIPE ROW(l_product_service_ocv(i));
2048 END LOOP;
2049
2050 RETURN;
2051
2052 END product_service_ocv;
2053
2054 -- Added for bug 9275861
2055 -- Returns the columns of the flexfield for PS Category in the form of "SEGMENT1.SEGMENT2...SEGMENT<N>".
2056 FUNCTION get_flexfield_columns
2057 RETURN VARCHAR2
2058 IS
2059 l_concat_cols VARCHAR2(4000) := NULL;
2060 BEGIN
2061
2062 assert_init();
2063
2064 FOR i IN 1..g_structure_segment_count LOOP
2065 IF (g_structure_segments(i).displayed_flag = 'Y') THEN
2066 IF (l_concat_cols IS NULL) THEN
2067 l_concat_cols := g_structure_segments(i).column_name;
2068 ELSE
2069 l_concat_cols := l_concat_cols || '.' || g_structure_segments(i).column_name;
2070 END IF;
2071 END IF;
2072 END LOOP;
2073
2074 RETURN l_concat_cols;
2075
2076 END get_flexfield_columns;
2077
2078 /* End Supplier Hub: Supplier Profile Workbench */
2079
2080 END pos_product_service_utl_pkg;