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