1 PACKAGE BODY QP_UTIL AS
2 /* $Header: QPXUTILB.pls 120.19 2012/03/19 09:06:56 jputta ship $ */
3 --===========================================================================
4 -- This script defines a procedure 'GEt_Context_Attribute.
5 -- Input to this procedure is attribute code as defined in
6 -- harcode list file and output will be context and attribute name.
7 -- In case of qualifiers , context will go in qualifier_context ,
8 -- attribute name will go in
9 -- qualifier attribute name .
10 -- In case of pricing attributes , context will go in pricing_context ,
11 -- attribute name will go in
12 -- pricing attribute name .
13 -- All attribute code except method_type_code ,should be included in quotes.
14 -- Method_type_code should not be included in quotes , so that whatever
15 -- value store in
16 -- it ('AMNT', 'PERC') will go in as parameter, and depending on this value,
17 -- get_context_attribute
18 -- will return context as VOLUME or LINEAMT and corresponding attribute name
19 --===========================================================================
20
21 -- =======================================================================
22 -- Procedure get_item_cat_info
23 -- procedure type Public
24 -- DESCRIPTION
25 -- Returns the name and description for an item category ID, using the
26 -- QP_ITEM_CATEGORIES valueset and any PTE/Source System Code
27 -- restrictions.
28 -- =======================================================================
29
30 PROCEDURE get_item_cat_info(p_item_id IN NUMBER,
31 p_item_pte IN VARCHAR2 DEFAULT NULL,
32 p_item_ss IN VARCHAR2 DEFAULT NULL,
33 x_item_name OUT NOCOPY VARCHAR2,
34 x_item_desc OUT NOCOPY VARCHAR2,
35 x_is_valid OUT NOCOPY BOOLEAN)
36 IS
37 l_vset_id NUMBER;
38 l_select_stmt VARCHAR2(4000);
39 l_order_by_idx NUMBER;
40 l_attribute_id NUMBER;
41 BEGIN
42
43 -- Get select statment for ITEM_CATEGORY valueset
44 QP_MASS_MAINTAIN_UTIL.get_valueset_select('ITEM',
45 'ITEM_CATEGORY',
46 l_select_stmt,
47 'PRICING_ATTRIBUTE2',
48 p_item_pte,
49 p_item_ss);
50
51 -- Add attribute ID where clause
52 l_select_stmt := l_select_stmt || ' WHERE attribute_id = ' || p_item_id;
53
54 -- Execute statement
55 EXECUTE IMMEDIATE l_select_stmt INTO l_attribute_id, x_item_name, x_item_desc;
56 x_is_valid := TRUE;
57
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 x_is_valid := FALSE;
61 END get_item_cat_info;
62
63 --Bug# 5523416 RAVI START
64 --==============================================================================
65 --FUNCTION - Is_Valid_Category
66 --FUNC TYPE - Public
67 --DESCRIPTION - Funtion returns true if the category is present in the Functional area
68 -- for the source system and pte in the profile options.
69 --==============================================================================
70 FUNCTION Is_Valid_Category(p_item_id IN NUMBER) RETURN VARCHAR2 IS
71 l_item_name VARCHAR2(1000);
72 l_item_desc VARCHAR2(5000);
73 l_is_valid BOOLEAN;
74 BEGIN
75 QP_UTIL.get_item_cat_info(
76 p_item_id,
77 fnd_profile.value('QP_PRICING_TRANSACTION_ENTITY'),
78 fnd_profile.value('QP_SOURCE_SYSTEM_CODE'),
79 l_item_name,
80 l_item_desc,
81 l_is_valid
82 );
83
84 IF l_is_valid=TRUE THEN
85 RETURN 'TRUE';
86 ELSE
87 RETURN 'FALSE';
88 END IF;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 RETURN 'FALSE';
93 END Is_Valid_Category;
94
95 --Bug# 5523416 RAVI END
96
97 -- =======================================================================
98 -- Function get_fnarea_where_clause
99 -- function type Private
100 -- Returns VARCHAR2
101 -- DESCRIPTION
102 -- Returns the additional where clause for functional areas based on
103 -- PTE and Source System Codes.
104 -- =======================================================================
105
106
107 FUNCTION get_fnarea_where_clause(p_pte_code IN VARCHAR2,
108 p_ss_code IN VARCHAR2,
109 p_table_alias IN VARCHAR2) RETURN VARCHAR2 IS
110 l_table_alias VARCHAR2(50) := p_table_alias;
111 l_where_clause VARCHAR2(240);
112 BEGIN
113 -- Set table Alias
114 IF l_table_alias IS NOT NULL THEN
115 l_table_alias := l_table_alias || '.';
116 END IF;
117
118 IF p_pte_code IS NULL THEN -- No PTE/SS: No restriction
119
120 l_where_clause := NULL;
121
122 ELSE -- Restriction based on PTE
123 l_where_clause := ' ' || l_table_alias || 'functional_area_id IN (';
124 l_where_clause := l_where_clause || 'select distinct utilmap.functional_area_id ';
125 l_where_clause := l_where_clause || 'from qp_fass_v utilmap ';
126 l_where_clause := l_where_clause || 'where utilmap.pte_code = ''' || p_pte_code || ''' ';
127
128 IF p_ss_code IS NOT NULL THEN -- and SS
129
130 l_where_clause := l_where_clause || 'and utilmap.application_short_name = ''' || p_ss_code || ''' ';
131
132 END IF;
133
134 l_where_clause := l_where_clause || ' and utilmap.enabled_flag = ''Y'' ';
135
136 l_where_clause := l_where_clause || ') ';
137
138 END IF;
139
140 RETURN l_where_clause;
141
142 END get_fnarea_where_clause;
143
144
145 -- =======================================================================
146 -- Function merge_fnarea_where_clause
147 -- funtion type Public
148 -- Returns VARCHAR2
149 -- DESCRIPTION
150 -- Returns the merged where clause for functional areas based on
151 -- PTE and Source System Codes merged with a where clause string param.
152 -- =======================================================================
153
154
155 FUNCTION merge_fnarea_where_clause(p_where_clause IN VARCHAR2,
156 p_pte_code IN VARCHAR2,
157 p_ss_code IN VARCHAR2,
158 p_table_alias IN VARCHAR2) RETURN VARCHAR2 IS
159 l_where_clause VARCHAR2(2000);
160 l_fnarea_where_clause VARCHAR2(240);
161 l_order_idx NUMBER;
162 BEGIN
163 -- Get fnarea where clause
164 l_fnarea_where_clause := get_fnarea_where_clause(p_pte_code, p_ss_code, p_table_alias);
165
166 -- If fnarea where clause is null, return param where clause
167 IF l_fnarea_where_clause IS NULL THEN
168 l_where_clause := p_where_clause;
169 ELSE
170 -- Otherwise, we add the fnarea clause before the ORDER BY clause
171 l_order_idx := instr(p_where_clause, 'ORDER BY');
172 l_where_clause := substr(p_where_clause, 1, l_order_idx - 1);
173 l_where_clause := l_where_clause || 'AND ' || l_fnarea_where_clause || ' ';
174 l_where_clause := l_where_clause || substr(p_where_clause, l_order_idx);
175 END IF;
176
177 RETURN l_where_clause;
178
179 END merge_fnarea_where_clause;
180
181 --==============================================================================
182 --FUNCTION - Is_Used
183 --FUNC TYPE - Public
184 --DESCRIPTION - Is Used function returns 'Y' if the corresponding context-Attribute
185 -- pair is used by any pricing setup(pricelist,modifiers..etc),'N' otherwise.
186 --==============================================================================
187 FUNCTION Is_Used (p_context_type IN VARCHAR2,
188 p_context_code IN VARCHAR2,
189 p_attribute_code IN VARCHAR2)
190 RETURN VARCHAR2
191 IS
192
193 x_is_used varchar2(1) := 'N';
194 l_check_active_flag VARCHAR2(1);
195
196 BEGIN
197
198 l_check_active_flag := nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
199
200 IF l_check_active_flag = 'N' THEN
201 IF p_context_type='QUALIFIER' THEN
202 BEGIN
203 SELECT 'Y' INTO x_is_used FROM qp_qualifiers
204 WHERE qualifier_context = p_context_code
205 AND qualifier_attribute = p_attribute_code AND rownum < 2;
206 EXCEPTION
207 WHEN no_data_found THEN
208 BEGIN
209 SELECT 'Y' INTO x_is_used FROM qp_limits
210 WHERE ((multival_attr1_context = p_context_code
211 AND multival_attribute1 = p_attribute_code)
212 OR (multival_attr2_context = p_context_code
213 AND multival_attribute2 = p_attribute_code)) AND rownum < 2;
214 EXCEPTION
215 WHEN no_data_found THEN
216 BEGIN
217 SELECT 'Y' INTO x_is_used FROM qp_limit_attributes
218 WHERE limit_attribute_context = p_context_code
219 AND limit_attribute = p_attribute_code AND rownum < 2;
220 EXCEPTiON
221 WHEN no_data_found THEN
222 x_is_used := 'N';
223 END;
224 END;
225 END;
226 ELSIF p_context_type='PRICING_ATTRIBUTE' THEN
227 BEGIN
228 SELECT 'Y'
229 INTO x_is_used FROM qp_pricing_attributes
230 WHERE pricing_attribute_context = p_context_code
231 AND pricing_attribute = p_attribute_code AND rownum < 2;
232 EXCEPTION
233 WHEN no_data_found THEN
234 BEGIN
235 SELECT 'Y'
236 INTO x_is_used
237 FROM qp_price_formula_lines a, qp_list_lines b
238 WHERE a.pricing_attribute_context = p_context_code
239 AND a.pricing_attribute = p_attribute_code
240 AND a.price_formula_id = b.price_by_formula_id
241 AND rownum < 2;
242 EXCEPTION
243 WHEN no_data_found THEN
244 BEGIN
245 SELECT 'Y'
246 INTO x_is_used
247 FROM qp_price_formula_lines a, qp_currency_details b
248 WHERE a.pricing_attribute_context = p_context_code
249 AND a.pricing_attribute = p_attribute_code
250 AND (a.price_formula_id = b.price_formula_id
251 OR a.price_formula_id = b.markup_formula_id)
252 AND rownum < 2;
253 EXCEPTION
254 WHEN no_data_found THEN
255 x_is_used:= 'N';
256 END;
257 END;
258 END;
259 ELSIF p_context_type='PRODUCT' THEN
260 BEGIN
261 SELECT 'Y' INTO x_is_used FROM dual
262 where exists(SELECT 'Y'
263 FROM qp_pricing_attributes
264 WHERE product_attribute_context = p_context_code
265 AND product_attribute = p_attribute_code);
266 EXCEPTION
267 WHEN no_data_found THEN
268 BEGIN
269 SELECT 'Y' INTO x_is_used FROM qp_limits
270 WHERE ((multival_attr1_context = p_context_code
271 AND multival_attribute1 = p_attribute_code)
272 OR (multival_attr2_context = p_context_code
273 AND multival_attribute2 = p_attribute_code)) AND rownum < 2;
274 EXCEPTION
275 WHEN no_data_found THEN
276 BEGIN
277 SELECT 'Y' INTO x_is_used FROM qp_limit_attributes
278 WHERE limit_attribute_context = p_context_code
279 AND limit_attribute = p_attribute_code AND rownum < 2;
280 EXCEPTiON
281 WHEN no_data_found THEN
282 x_is_used := 'N';
283 END;
284 END;
285 END;
286 END IF;
287 ELSIF l_check_active_flag = 'Y' THEN
288 IF p_context_type='QUALIFIER' THEN
289 BEGIN
290 SELECT 'Y' INTO x_is_used FROM qp_qualifiers
291 WHERE qualifier_context = p_context_code
292 AND qualifier_attribute = p_attribute_code
293 AND active_flag = 'Y' AND rownum < 2;
294 EXCEPTION
295 WHEN no_data_found THEN
296 BEGIN
297 SELECT 'Y' INTO x_is_used FROM qp_limits a, qp_list_headers_b b
298 WHERE ((a.multival_attr1_context = p_context_code
299 AND a.multival_attribute1 = p_attribute_code)
300 OR (a.multival_attr2_context = p_context_code
301 AND a.multival_attribute2 = p_attribute_code))
302 AND a.list_header_id = b.list_header_id AND b.active_flag = 'Y'
303 AND rownum < 2;
304 EXCEPTION
305 WHEN no_data_found THEN
306 BEGIN
307 SELECT 'Y' INTO x_is_used
308 FROM qp_limit_attributes a, qp_limits b, qp_list_headers_b c
309 WHERE a.limit_attribute_context = p_context_code
310 AND a.limit_attribute = p_attribute_code AND a.limit_id = b.limit_id
311 AND b.list_header_id = c.list_header_id AND c.active_flag = 'Y'
312 AND rownum < 2;
313 EXCEPTION
314 WHEN no_data_found THEN
315 x_is_used := 'N';
316 END;
317 END;
318 END;
319 ELSIF p_context_type='PRICING_ATTRIBUTE' THEN
320 BEGIN
321 --modified query to improve performance.
322 SELECT 'Y' INTO x_is_used FROM dual
323 where exists(SELECT 'Y' FROM qp_pricing_attributes qpa, qp_list_headers_b qph
324 WHERE qpa.pricing_attribute_context = p_context_code
325 AND qpa.pricing_attribute = p_attribute_code
326 AND qpa.list_header_id = qph.list_header_id
327 AND qph.active_flag = 'Y'
328 );
329 EXCEPTION
330 WHEN no_data_found THEN
331 BEGIN
332 SELECT 'Y'
333 INTO x_is_used
334 FROM qp_price_formula_lines a, qp_list_lines b, qp_list_headers_b c
335 WHERE a.pricing_attribute_context = p_context_code
336 AND a.pricing_attribute = p_attribute_code
337 AND a.price_formula_id = b.price_by_formula_id
338 AND b.list_header_id = c.list_header_id
339 AND c.active_flag = 'Y'
340 AND rownum < 2;
341 EXCEPTION
342 WHEN no_data_found THEN
343 BEGIN
344 SELECT 'Y'
345 INTO x_is_used
346 FROM qp_price_formula_lines a, qp_currency_details b
347 WHERE a.pricing_attribute_context = p_context_code
348 AND a.pricing_attribute = p_attribute_code
349 AND (a.price_formula_id = b.price_formula_id
350 OR a.price_formula_id = b.markup_formula_id)
351 AND rownum < 2;
352 EXCEPTION
353 WHEN no_data_found THEN
354 x_is_used := 'N';
355 END;
356 END;
357 END;
358 ELSIF p_context_type='PRODUCT' THEN
359 BEGIN
360 SELECT 'Y'
361 INTO x_is_used FROM dual
362 WHERE exists (SELECT 'Y'
363 FROM qp_pricing_attributes a
364 WHERE product_attribute_context = p_context_code
365 AND product_attribute = p_attribute_code
366 AND exists (select 'x' from qp_list_headers_b b
367 where active_flag = 'Y' and a.list_header_id = b.list_header_id));
368 EXCEPTION
369 WHEN no_data_found THEN
370 BEGIN
371 SELECT 'Y' INTO x_is_used FROM qp_limits a, qp_list_headers_b b
372 WHERE ((a.multival_attr1_context = p_context_code
373 AND a.multival_attribute1 = p_attribute_code)
374 OR (a.multival_attr2_context = p_context_code
375 AND a.multival_attribute2 = p_attribute_code))
376 AND a.list_header_id = b.list_header_id AND b.active_flag = 'Y'
377 AND rownum < 2;
378 EXCEPTION
379 WHEN no_data_found THEN
380 BEGIN
381 SELECT 'Y' INTO x_is_used
382 FROM qp_limit_attributes a, qp_limits b, qp_list_headers_b c
383 WHERE a.limit_attribute_context = p_context_code
384 AND a.limit_attribute = p_attribute_code AND a.limit_id = b.limit_id
385 AND b.list_header_id = c.list_header_id AND c.active_flag = 'Y'
386 AND rownum < 2;
387 EXCEPTION
388 WHEN no_data_found THEN
389 x_is_used := 'N';
390 END;
391 END;
392 END;
393 END IF;
394 END IF;
395
396 RETURN x_is_used;
397 END Is_Used;
398
399
400 --==============================================================================
401 --FUNCTION - Get_Schema
402 --FUNC TYPE - Public
403 --DESCRIPTION - Utility Function that returns the schema.
404 --=============================================================================
405 FUNCTION Get_Schema
406 RETURN VARCHAR2
407 IS
408 l_qp_schema VARCHAR2(30);
409 l_status VARCHAR2(30);
410 l_industry VARCHAR2(30);
411
412 BEGIN
413
414 IF (FND_INSTALLATION.GET_APP_INFO('QP', l_status, l_industry, l_qp_schema)) THEN
415 NULL;
416 END IF;
417
418 RETURN l_qp_schema;
419
420 END Get_Schema;
421
422
423
424 --==============================================================================
425 --FUNCTION - Attrmgr_Installed
426 --FUNC TYPE - Public
427 --DESCRIPTION - Utility Function that returns TRUE if Attribute Manager
428 -- is installed. (Will be allways true from Patchset H onwards).
429 --=============================================================================
430 FUNCTION Attrmgr_Installed
431 RETURN VARCHAR2
432 IS
433 l_attrmgr_installed VARCHAR2(30);
434
435 BEGIN
436
437 l_attrmgr_installed := QP_AM_UTIL.Attrmgr_Installed;
438
439 RETURN l_attrmgr_installed;
440
441 END Attrmgr_Installed;
442
443
444 --==============================================================================
445 --PROCEDURE - Get_Sourcing_Info
446 --FUNC TYPE - Public
447 --DESCRIPTION - Utility Procedure that returns the Sourcing_Enabled,
448 -- Sourcing_Status and Sourcing_Method for an attribute
449 --OUT PARAMETER - x_sourcing_enabled can be 'Y' or 'N'
450 -- x_sourcing_status can be 'Y' or 'N'
451 -- x_sourcing_method can be 'ATTRIBUTE MAPPING',
452 -- 'CUSTOM SOURCED' or
453 -- 'USER ENTERED'
454 --=============================================================================
455 PROCEDURE Get_Sourcing_Info(p_context_type IN VARCHAR2,
456 p_context IN VARCHAR2,
457 p_attribute IN VARCHAR2,
458 x_sourcing_enabled OUT NOCOPY VARCHAR2,
459 x_sourcing_status OUT NOCOPY VARCHAR2,
460 x_sourcing_method OUT NOCOPY VARCHAR2)
461 IS
462
463 l_pte_code VARCHAR2(30);
464
465 CURSOR sourcing_enabled_cur(a_context_type VARCHAR2, a_context VARCHAR2,
466 a_attribute VARCHAR2, a_pte_code VARCHAR2)
467 IS
468 SELECT a.sourcing_enabled, a.sourcing_status,
469 nvl(a.user_sourcing_method, a.seeded_sourcing_method)
470 FROM qp_pte_segments a, qp_prc_contexts_b b,
471 qp_segments_b c
472 WHERE b.prc_context_code = a_context
473 AND b.prc_context_type = a_context_type
474 AND c.prc_context_id = b.prc_context_id
475 AND c.segment_mapping_column = a_attribute
476 AND a.segment_id = c.segment_id
477 AND a.pte_code = a_pte_code;
478
479 BEGIN
480
481 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
482
483 IF l_pte_code IS NULL THEN
484 l_pte_code := 'ORDFUL';
485 END IF;
486
487 OPEN sourcing_enabled_cur(p_context_type,p_context,p_attribute,l_pte_code);
488
489 FETCH sourcing_enabled_cur
490 INTO x_sourcing_enabled, x_sourcing_status, x_sourcing_method;
491
492 CLOSE sourcing_enabled_cur;
493
494 END Get_Sourcing_Info;
495
496
497 --==============================================================================
498 --FUNCTION get_context
499 --PROC TYPE Public
500 --DESCRIPTION - Utility Function for Reports. Has the same function as
501 -- the Get_Context function in QPXQPPLQ.pld
502 --=============================================================================
503 FUNCTION Get_Context(p_flexfield_name IN VARCHAR2,
504 p_context IN VARCHAR2)
505 RETURN VARCHAR2
506 IS
507
508 Flexfield FND_DFLEX.dflex_r;
509 Flexinfo FND_DFLEX.dflex_dr;
510 Contexts FND_DFLEX.contexts_dr;
511
512 x_context_name VARCHAR2(240);
513
514 l_context_type VARCHAR2(30);
515 l_error_code NUMBER;
516 l_enabled_flag VARCHAR2(1);
517
518 CURSOR contexts_cur(a_context_code VARCHAR2, a_context_type VARCHAR2)
519 IS
520 SELECT nvl(t.user_prc_context_name, t.seeded_prc_context_name), b.enabled_flag
521 FROM qp_prc_contexts_b b, qp_prc_contexts_tl t
522 WHERE b.prc_context_id = t.prc_context_id
523 AND t.language = userenv('LANG')
524 AND b.prc_context_code = a_context_code
525 AND b.prc_context_type = a_context_type;
526
527 BEGIN
528
529 IF Attrmgr_Installed = 'Y' THEN
530
531 QP_UTIL.Get_Context_Type(p_flexfield_name, p_context,
532 l_context_type, l_error_code);
533
534 IF l_error_code = 0 THEN --success
535
536 OPEN contexts_cur(p_context, l_context_type);
537 FETCH contexts_cur INTO x_context_name, l_enabled_flag;
538 CLOSE contexts_cur;
539
540 END IF; --If l_error_code = 0
541
542 ELSE
543
544 -- Call Flexapi to get contexts
545
546 FND_DFLEX.get_flexfield('QP',p_FlexField_Name,Flexfield,Flexinfo);
547 FND_DFLEX.get_contexts(Flexfield,Contexts);
548
549
550 FOR i IN 1..Contexts.ncontexts LOOP
551
552 -- If(Contexts.is_enabled(i) AND (NOT (Contexts.is_global(i)))) Then
553 IF (Contexts.is_enabled(i)) THEN
554
555 IF p_context = Contexts.context_code(i) THEN
556 x_context_name :=Contexts.context_name(i);
557 EXIT;
558 END IF;
559
560 END IF;
561
562 END LOOP;
563
564 END IF;--If Attrmgr_Installed = 'Y'
565
566 RETURN x_context_name;
567
568 END Get_Context;
569
570
571
572
573 --==============================================================================
574 --PROCEDURE get_context_type
575 --PROC TYPE Public
576 --DESCRIPTION - Utility Function to determine context_type for a context_code
577 --OUT PARAMETER - x_error_code can have one of the following values
578 -- 0 - Success
579 -- 1 - Failure
580 --=============================================================================
581 PROCEDURE Get_Context_Type(p_flexfield_name IN VARCHAR2,
582 p_context_name IN VARCHAR2,
583 x_context_type OUT NOCOPY VARCHAR2,
584 x_error_code OUT NOCOPY VARCHAR2)
585 IS
586 BEGIN
587
588 x_error_code := 0; --Initialize x_error_code to success
589
590 IF p_flexfield_name = 'QP_ATTR_DEFNS_QUALIFIER' THEN
591
592 x_context_type := 'QUALIFIER';
593
594 ELSIF p_flexfield_name = 'QP_ATTR_DEFNS_PRICING' THEN
595
596 IF p_context_name IN ('ITEM', 'PRODUCT') THEN
597 x_context_type := 'PRODUCT';
598 ELSE
599 x_context_type := 'PRICING_ATTRIBUTE';
600 END IF;
601
602 ELSE
603 x_error_code := 1; --Failure
604 END IF;
605
606 END Get_Context_Type;
607
608
609
610 -- =======================================================================
611 -- Function validate_num_date
612 -- funtion type public
613 -- Returns number
614 -- out parameters :
615 -- DESCRIPTION
616 --
617 --
618 -- =======================================================================
619
620 function validate_num_date(p_datatype in varchar2
621 ,p_value in varchar2
622 )return number IS
623
624 x_error_code NUMBER:= 0;
625
626 l_date DATE;
627 l_number NUMBER;
628
629
630
631 BEGIN
632 IF p_datatype = 'N' THEN
633
634 l_number := qp_number.canonical_to_number(p_value);
635
636 ELSIF p_datatype IN ('X', 'Y') THEN
637
638 --l_date := fnd_date.canonical_to_date(p_value);
639 l_date := to_date(p_value,'FXYYYY/MM/DD HH24:MI:SS');
640 END IF;
641
642
643 RETURN x_error_code;
644
645 EXCEPTION
646
647 WHEN OTHERS THEN
648
649 x_error_code := 9;
650
651 RETURN x_error_code;
652
653 END validate_num_date;
654
655
656 -- =======================================================================
657
658
659 PROCEDURE Get_Context_Attribute( p_attribute_code IN VARCHAR2,
660 x_context OUT NOCOPY VARCHAR2,
661 x_attribute_name OUT NOCOPY VARCHAR2
662 ) IS
663 CURSOR context_attr_cur(a_attribute_code VARCHAR2)
664 IS
665 SELECT a.prc_context_code, b.segment_mapping_column
666 FROM qp_prc_contexts_b a, qp_segments_b b
667 WHERE a.prc_context_id = b.prc_context_id
668 AND b.segment_code = UPPER(a_attribute_code);
669
670 BEGIN
671
672 IF UPPER(p_attribute_code) = 'CUSTOMER_CLASS_CODE' THEN
673
674 -------Qualifiers
675
676 x_context := 'CUSTOMER' ;
677 x_attribute_name := 'QUALIFIER_ATTRIBUTE1' ;
678
679 ELSIF UPPER(p_attribute_code) = 'SOLD_TO_ORG_ID' THEN
680
681 x_context := 'CUSTOMER';
682 x_attribute_name := 'QUALIFIER_ATTRIBUTE2' ;
683
684 ELSIF UPPER(p_attribute_code) = 'CUSTOMER_ID' THEN
685
686 x_context := 'CUSTOMER' ;
687 --changed for so to qp upgrade
688 --x_attribute_name := 'QUALIFIER_ATTRIBUTE3' ;
689 x_attribute_name := 'QUALIFIER_ATTRIBUTE2' ;
690
691 ELSIF UPPER(p_attribute_code) = 'SITE_USE_ID' THEN
692
693 x_context := 'CUSTOMER';
694 --changed for so to qp upgrade
695 --x_attribute_name := 'QUALIFIER_ATTRIBUTE4';
696 x_attribute_name := 'QUALIFIER_ATTRIBUTE5';
697
698 ELSIF UPPER(p_attribute_code) = 'SITE_ORG_ID' THEN
699
700 x_context := 'CUSTOMER';
701 x_attribute_name := 'QUALIFIER_ATTRIBUTE5';
702
703 -- Discount
704 ELSIF UPPER(p_attribute_code) = 'DISCOUNT_ID' THEN
705 x_context := 'MODLIST';
706 x_attribute_name := 'QUALIFIER_ATTRIBUTE6';
707
708
709 --Agreement Name 1006
710 ELSIF UPPER(p_attribute_code) IN ('1006','1467') THEN
711 x_context := 'CUSTOMER';
712 x_attribute_name := 'QUALIFIER_ATTRIBUTE7';
713
714 --Agreement Type
715 ELSIF UPPER(p_attribute_code) IN('1005','1468') THEN
716 x_context := 'CUSTOMER';
717 x_attribute_name := 'QUALIFIER_ATTRIBUTE8';
718
719 -- Order Type
720 ELSIF UPPER(p_attribute_code) IN ('1007','1325') THEN
721 x_context := 'ORDER';
722 x_attribute_name := 'QUALIFIER_ATTRIBUTE9';
723
724
725
726 --Price List
727 ELSIF UPPER(p_attribute_code) = 'PRICE_LIST_ID' THEN
728 --changed from order to modlist for so to qp upgrade
729 x_context := 'MODLIST';
730 x_attribute_name := 'QUALIFIER_ATTRIBUTE4';
731
732 --GSA Qualifier
733 ELSIF UPPER(p_attribute_code) = 'GSA_CUSTOMER' THEN
734 --changed from order to modlist for so to qp upgrade
735 x_context := 'CUSTOMER';
736 x_attribute_name := 'QUALIFIER_ATTRIBUTE15';
737
738 --Customer PO
739 ELSIF UPPER(p_attribute_code) IN ( '1004','1053') THEN
740 x_context := 'ORDER';
741 x_attribute_name := 'QUALIFIER_ATTRIBUTE12';
742
743
744 -------Pricing Attributes
745 -- Item Number
746 ELSIF UPPER(p_attribute_code) IN ('1001','1208') THEN
747
748 x_context := 'ITEM' ;
749 x_attribute_name := 'PRICING_ATTRIBUTE1';
750
751 -- Item Category
752 ELSIF UPPER(p_attribute_code) = '1045' THEN
753
754 x_context := 'ITEM';
755 x_attribute_name := 'PRICING_ATTRIBUTE2';
756
757 -- ALL
758 ELSIF UPPER(p_attribute_code) = 'ALL' THEN
759
760 x_context := 'ITEM';
761 x_attribute_name := 'PRICING_ATTRIBUTE3';
762
763 -- Segment_1
764 ELSIF UPPER(p_attribute_code) IN ('SEGMENT1','1020') THEN
765
766 x_context := 'ITEM';
767 x_attribute_name := 'PRICING_ATTRIBUTE4';
768
769 -- Segment_2
770 ELSIF UPPER(p_attribute_code) IN ('SEGMENT2','1021') THEN
771
772
773 x_context := 'ITEM';
774 x_attribute_name := 'PRICING_ATTRIBUTE5';
775
776 -- Segment_3
777 ELSIF UPPER(p_attribute_code) IN ('SEGMENT3','1022') THEN
778
779 x_context := 'ITEM';
780 x_attribute_name := 'PRICING_ATTRIBUTE6';
781
782 -- Segment_4
783 ELSIF UPPER(p_attribute_code) IN ('SEGMENT4', '1023') THEN
784
785 x_context := 'ITEM';
786 x_attribute_name := 'PRICING_ATTRIBUTE7';
787
788 -- Segment_5
789 ELSIF UPPER(p_attribute_code) IN ('SEGMENT5','1024') THEN
790
791 x_context := 'ITEM';
792 x_attribute_name := 'PRICING_ATTRIBUTE8';
793
794 -- Segment_6
795 ELSIF UPPER(p_attribute_code) IN ('SEGMENT6' ,'1025') THEN
796
797 x_context := 'ITEM';
798 x_attribute_name := 'PRICING_ATTRIBUTE9';
799
800 -- Segment_7
801 ELSIF UPPER(p_attribute_code) IN ('SEGMENT7', '1026') THEN
802
803 x_context := 'ITEM';
804 x_attribute_name := 'PRICING_ATTRIBUTE10';
805
806 -- Segment_8
807 ELSIF UPPER(p_attribute_code) IN ('SEGMENT8','1027') THEN
808
809 x_context := 'ITEM';
810 x_attribute_name := 'PRICING_ATTRIBUTE11';
811
812 -- Segment_9
813 ELSIF UPPER(p_attribute_code) IN ('SEGMENT9','1028') THEN
814
815 x_context := 'ITEM';
816 x_attribute_name := 'PRICING_ATTRIBUTE12';
817
818 -- Segment_10
819 ELSIF UPPER(p_attribute_code) IN ('SEGMENT10', '1029') THEN
820
821 x_context := 'ITEM';
822 x_attribute_name := 'PRICING_ATTRIBUTE13';
823
824 -- Segment_11
825 ELSIF UPPER(p_attribute_code) IN ('SEGMENT11', '1030') THEN
826
827 x_context := 'ITEM';
828 x_attribute_name := 'PRICING_ATTRIBUTE14';
829
830 -- Segment_12
831 ELSIF UPPER(p_attribute_code) IN ('SEGMENT12','1031') THEN
832
833 x_context := 'ITEM';
834 x_attribute_name := 'PRICING_ATTRIBUTE15';
835
836 -- Segment_13
837 ELSIF UPPER(p_attribute_code) IN ('SEGMENT13', '1032') THEN
838
839 x_context := 'ITEM';
840 x_attribute_name := 'PRICING_ATTRIBUTE16';
841
842 -- Segment_14
843 ELSIF UPPER(p_attribute_code) IN ('SEGMENT14','1033') THEN
844
845 x_context := 'ITEM';
846 x_attribute_name := 'PRICING_ATTRIBUTE17';
847
848 -- Segment_15
849 ELSIF UPPER(p_attribute_code) IN ('SEGMENT15','1034') THEN
850
851 x_context := 'ITEM';
852 x_attribute_name := 'PRICING_ATTRIBUTE18';
853
854 -- Segment_16
855 ELSIF UPPER(p_attribute_code) IN ('SEGMENT16','1035') THEN
856
857 x_context := 'ITEM';
858 x_attribute_name := 'PRICING_ATTRIBUTE19';
859
860 -- Segment_17
861 ELSIF UPPER(p_attribute_code) IN ('SEGMENT17', '1036') THEN
862
863 x_context := 'ITEM';
864 x_attribute_name := 'PRICING_ATTRIBUTE20';
865
866 -- Segment_18
867 ELSIF UPPER(p_attribute_code) IN ('SEGMENT18' ,'1037') THEN
868
869 x_context := 'ITEM';
870 x_attribute_name := 'PRICING_ATTRIBUTE21';
871
872 -- Segment_19
873 ELSIF UPPER(p_attribute_code) IN ('SEGMENT19' ,'1038') THEN
874
875 x_context := 'ITEM';
876 x_attribute_name := 'PRICING_ATTRIBUTE22';
877
878 -- Segment_20
879 ELSIF UPPER(p_attribute_code) IN ('SEGMENT20', '1039') THEN
880
881 x_context := 'ITEM';
882 x_attribute_name := 'PRICING_ATTRIBUTE23';
883
884
885 ----commenting out pricing attribute 1 to 15 as they are no more required for the upgrade.
886 ---on 30 th march as per rtata.
887 /*
888 -- Pricing_attribute1
889 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE1' ,'1010') THEN
890
891 x_context := 'PRICING ATTRIBUTE';
892 x_attribute_name := 'PRICING_ATTRIBUTE18';
893
894 -- Pricing_attribute2
895 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE2','1011') THEN
896
897 x_context := 'PRICING ATTRIBUTE';
898 x_attribute_name := 'PRICING_ATTRIBUTE19';
899
900 -- Pricing_attribute3
901 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE3' ,'1012') THEN
902
903 x_context := 'PRICING ATTRIBUTE';
904 x_attribute_name := 'PRICING_ATTRIBUTE20';
905
906 -- Pricing_attribute4
907 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE4' ,'1013') THEN
908
909 x_context := 'PRICING ATTRIBUTE';
910 x_attribute_name := 'PRICING_ATTRIBUTE21';
911
912 -- Pricing_attribute5
913 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE5','1014') THEN
914
915 x_context := 'PRICING ATTRIBUTE';
916 x_attribute_name := 'PRICING_ATTRIBUTE22';
917
918 -- Pricing_attribute6
919 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE6' ,'1015') THEN
920
921 x_context := 'PRICING ATTRIBUTE';
922 x_attribute_name := 'PRICING_ATTRIBUTE23';
923
924 -- Pricing_attribute7
925 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE7' ,'1016') THEN
926
927 x_context := 'PRICING ATTRIBUTE';
928 x_attribute_name := 'PRICING_ATTRIBUTE24';
929
930 -- Pricing_attribute8
931 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE8' ,'1017') THEN
932
933 x_context := 'PRICING ATTRIBUTE';
934 x_attribute_name := 'PRICING_ATTRIBUTE25';
935
936 -- Pricing_attribute9
937 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE9','1018') THEN
938
939 x_context := 'PRICING ATTRIBUTE';
940 x_attribute_name := 'PRICING_ATTRIBUTE26';
941
942 -- Pricing_attribute10
943 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE10' ,'1019') THEN
944
945 x_context := 'PRICING ATTRIBUTE';
946 x_attribute_name := 'PRICING_ATTRIBUTE27';
947
948 -- Pricing_attribute11
949 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE11' ,'1040') THEN
950
951 x_context := 'PRICING ATTRIBUTE';
952 x_attribute_name := 'PRICING_ATTRIBUTE28';
953
954 -- Pricing_attribute12
955 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE12' ,'1041') THEN
956
957 x_context := 'PRICING ATTRIBUTE';
958 x_attribute_name := 'PRICING_ATTRIBUTE29';
959
960 -- Pricing_attribute13
961 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE13','1042') THEN
962
963 x_context := 'PRICING ATTRIBUTE';
964 x_attribute_name := 'PRICING_ATTRIBUTE30';
965
966 -- Pricing_attribute14
967 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE14','1043') THEN
968
969 x_context := 'PRICING ATTRIBUTE';
970 x_attribute_name := 'PRICING_ATTRIBUTE31';
971
972 -- Pricing_attribute15
973 ELSIF UPPER(p_attribute_code) IN ('PRICING_ATTRIBUTE15' ,'1044') THEN
974
975 x_context := 'PRICING ATTRIBUTE';
976 x_attribute_name := 'PRICING_ATTRIBUTE32';
977 */
978
979 --Customer Item
980 ELSIF UPPER(p_attribute_code) = 'CUSTOMER_ITEM_ID' THEN
981 --changed for SO to QP upgrade
982 --x_context := 'ITEM';
983 x_context := 'CUSTOMER_ITEM';
984 x_attribute_name := 'PRICING_ATTRIBUTE3';
985
986 -- Units
987 ELSIF UPPER(p_attribute_code) = 'UNITS' THEN
988
989 x_context := 'VOLUME';
990 --changed for so to qp upgrade
991 --x_attribute_name := 'PRICING_ATTRIBUTE3';
992 x_attribute_name := 'PRICING_ATTRIBUTE10';
993
994 -- Amount
995 ELSIF UPPER(p_attribute_code) = 'DOLLARS' THEN
996 --changed for so to qp upgrade
997 -- x_context := 'LINEAMT';
998 x_context := 'VOLUME';
999 x_attribute_name := 'PRICING_ATTRIBUTE12';
1000
1001 -- invalid code passed no context and attribute available
1002 ELSE
1003
1004 IF Attrmgr_Installed = 'Y' THEN
1005
1006 OPEN context_attr_cur(p_attribute_code);
1007
1008 FETCH context_attr_cur INTO x_context, x_attribute_name;
1009
1010 IF context_attr_cur%NOTFOUND THEN
1011 x_context := 'INVALID';
1012 x_attribute_name := 'INVALID';
1013 END IF;
1014
1015 CLOSE context_attr_cur;
1016
1017 ELSE
1018 x_context := 'INVALID';
1019 x_attribute_name := 'INVALID';
1020
1021 END IF; --If Attrmgr_Installed = 'Y'
1022
1023 END IF;
1024
1025 END Get_Context_Attribute;
1026
1027
1028 --===========================================================================
1029 -- Function 'IS_Qualifier' returns 'T' if passed parameter is qualifier
1030 -- otheriwise returns 'F'.
1031 -- Parameter to this procedure is attribute-code from the harcode list.
1032 --===========================================================================
1033
1034 FUNCTION Is_qualifier( p_attribute_code IN VARCHAR2)
1035 RETURN VARCHAR2
1036 IS
1037 CURSOR context_cur(a_attribute_code VARCHAR2)
1038 IS
1039 SELECT a.prc_context_id
1040 FROM qp_prc_contexts_b a, qp_segments_b b
1041 WHERE a.prc_context_id = b.prc_context_id
1042 AND b.segment_code = a_attribute_code
1043 AND a.prc_context_type = 'QUALIFIER';
1044
1045 x_return VARCHAR2(1) := FND_API.G_FALSE;
1046 l_context_id NUMBER;
1047
1048 BEGIN
1049
1050 IF Attrmgr_Installed = 'Y' THEN
1051
1052 OPEN context_cur(p_attribute_code);
1053
1054 FETCH context_cur INTO l_context_id;
1055
1056 IF context_cur%FOUND THEN
1057 x_return := FND_API.G_TRUE;
1058 END IF;
1059
1060 CLOSE context_cur;
1061
1062 ELSE
1063
1064 IF UPPER(p_attribute_code) IN
1065
1066 ('CUSTOMER_CLASS_CODE',
1067 'SOLD_TO_ORG_ID',
1068 'CUSTOMER_ID',
1069 'SITE_USE_ID',
1070 'SITE_ORG_ID',
1071 'PRICE_LIST_ID',
1072 '1006', --Agreement name
1073 '1467', -- new Agreement name
1074 '1005',-- agreement Type
1075 '1468',-- agreement Type
1076 '1007',--Order Type
1077 '1325',-- new Order Type
1078 '1004', --customer PO
1079 '1053', -- new customer PO
1080 'DISCOUNT_ID') THEN
1081
1082 x_return := FND_API.G_TRUE;
1083
1084 END IF;
1085
1086 END IF; --If Attrmgr_Installed = 'Y'
1087
1088 RETURN x_return;
1089
1090 END Is_Qualifier;
1091
1092 --===========================================================================
1093 -- Function 'IS_PricingAttr' returns 'T' if passed parameter is Pricing
1094 -- attribute otheriwise returns 'F'.
1095 -- Parameter to this procedure is attribute-code from the harcode list.
1096 --===========================================================================
1097
1098 FUNCTION Is_PricingAttr( p_attribute_code IN VARCHAR2) RETURN VARCHAR2
1099 IS
1100 CURSOR context_cur(a_attribute_code VARCHAR2)
1101 IS
1102 SELECT a.prc_context_id
1103 FROM qp_prc_contexts_b a, qp_segments_b b
1104 WHERE a.prc_context_id = b.prc_context_id
1105 AND b.segment_code = a_attribute_code
1106 AND a.prc_context_type = 'PRICING';
1107
1108 x_return VARCHAR2(1) := FND_API.G_FALSE;
1109 l_context_id NUMBER;
1110
1111 BEGIN
1112
1113 IF Attrmgr_Installed = 'Y' THEN
1114
1115 OPEN context_cur(p_attribute_code);
1116
1117 FETCH context_cur INTO l_context_id;
1118
1119 IF context_cur%FOUND THEN
1120 x_return := FND_API.G_TRUE;
1121 END IF;
1122
1123 CLOSE context_cur;
1124
1125 ELSE
1126
1127 IF UPPER(p_attribute_code) IN
1128 ( '1001',--item
1129 '1208',-- new item
1130 '1045', --item category
1131 'CUSTOMER_ITEM_ID',
1132 'UNITS',
1133 'DOLLARS'
1134 ) THEN
1135
1136 x_return := FND_API.G_TRUE;
1137
1138 END IF;
1139
1140 END IF; --Attrmgr_Installed = 'Y'
1141
1142 RETURN x_return;
1143
1144 END Is_PricingAttr;
1145
1146
1147 FUNCTION Get_cust_context RETURN VARCHAR2 IS
1148 x_return VARCHAR2(30);
1149
1150 BEGIN
1151
1152 x_return := 'CUSTOMER';
1153
1154 return x_return;
1155
1156 END Get_cust_context;
1157
1158
1159 FUNCTION Get_sold_to_attrib RETURN VARCHAR2 IS
1160 x_return VARCHAR2(30);
1161
1162 BEGIN
1163
1164 x_return := 'QUALIFIER_ATTRIBUTE2';
1165
1166 return x_return;
1167
1168 END Get_sold_to_attrib;
1169
1170 FUNCTION Get_cust_class_attrib RETURN VARCHAR2 IS
1171
1172 x_return VARCHAR2(30);
1173
1174 BEGIN
1175
1176 x_return := 'QUALIFIER_ATTRIBUTE1';
1177
1178 return x_return;
1179
1180 END Get_cust_class_attrib;
1181
1182 FUNCTION Get_site_use_attrib RETURN VARCHAR2 IS
1183
1184 x_return VARCHAR2(30);
1185
1186 BEGIN
1187
1188 x_return := 'QUALIFIER_ATTRIBUTE5';
1189
1190 return x_return;
1191
1192 END Get_site_use_attrib;
1193
1194
1195 -- =======================================================================
1196 -- Function Get_Entity_Value
1197 -- =======================================================================
1198 FUNCTION Get_EntityValue(p_attribute_code IN VARCHAR2)
1199 RETURN NUMBER IS
1200 l_attribute_code VARCHAR2(30);
1201 BEGIN
1202 l_attribute_code := UPPER(p_attribute_code);
1203 IF l_attribute_code = 'PRICING_ATTRIBUTE1' THEN
1204 RETURN 1001;
1205 ELSIF l_attribute_code = 'PRICING_ATTRIBUTE2' THEN
1206 RETURN 1045;
1207 ELSIF l_attribute_code = 'QUALIFIER_ATTRIBUTE7' THEN
1208 RETURN 1006;
1209 ELSIF l_attribute_code = 'QUALIFIER_ATTRIBUTE8' THEN
1210 RETURN 1005;
1211 ELSIF l_attribute_code = 'QUALIFIER_ATTRIBUTE9' THEN
1212 RETURN 1007;
1213 ELSIF l_attribute_code = 'QUALIFIER_ATTRIBUTE12' THEN
1214 RETURN 1004;
1215 END IF;
1216 END;
1217
1218 FUNCTION Get_entityname(p_entity_id IN NUMBER)
1219 RETURN VARCHAR2 IS
1220
1221 l_entity_code VARCHAR2(50);
1222
1223 BEGIN
1224 IF p_entity_id IS NOT NULL THEN
1225 select a1.attribute_label_long into l_entity_code
1226 from ak_object_attributes_tl a1,
1227 oe_ak_obj_attr_ext a2
1228 where a2.attribute_id = p_entity_id
1229 and a1.language= userenv('lang')
1230 and a2.pricing_rule_enabled_flag= 'Y'
1231 and a2.attribute_code=a1.attribute_code
1232 and a2.database_object_name= a1.database_object_name
1233 and a2.attribute_application_id=a1.attribute_application_id;
1234 end if;
1235 return l_entity_code;
1236 END get_entityname;
1237
1238 FUNCTION Get_QP_Status RETURN VARCHAR2 IS
1239
1240 l_status VARCHAR2(1);
1241 l_industry VARCHAR2(1);
1242 l_application_id NUMBER := 661;
1243 l_retval BOOLEAN;
1244 BEGIN
1245
1246
1247 IF G_PRODUCT_STATUS = FND_API.G_MISS_CHAR THEN
1248
1249 l_retval := fnd_installation.get(l_application_id,l_application_id,
1250 l_status,l_industry);
1251
1252 -- if l_status = 'I', QP is fully installed. Advanced pricing functionalities
1253 -- should be available.
1254 --if l_status = 'S', QP is shared ie Basic QP is Installed.Only basic
1255 --pricing functionality should be available.
1256 --if l_status = 'N', -- QP not installled
1257
1258 G_PRODUCT_STATUS := l_status;
1259
1260 END IF;
1261
1262 return G_PRODUCT_STATUS;
1263
1264 END Get_QP_Status;
1265
1266 -- =======================================================================
1267 -- Function context_exists
1268 -- funtion type Private
1269 -- Returns BOOLEAN
1270 -- out parameters : p_context_r
1271 -- DESCRIPTION
1272 -- Searches for context code if it exists in the context list populated by
1273 -- get_contexts call.
1274 -- =======================================================================
1275
1276
1277 FUNCTION context_exists(p_context VARCHAR2,
1278 p_context_dr fnd_dflex.contexts_dr,
1279 p_context_r OUT NOCOPY fnd_dflex.context_r ) RETURN BOOLEAN IS
1280 BEGIN
1281 IF (p_context_dr.ncontexts > 0) THEN
1282 FOR i IN 1..p_context_dr.ncontexts LOOP
1283 IF (p_context = p_context_dr.context_code(i)
1284 AND p_context_dr.is_enabled(i) = TRUE) THEN
1285 p_context_r.context_code := p_context_dr.context_code(i);
1286 RETURN TRUE;
1287 END IF;
1288 END LOOP;
1289 RETURN FALSE;
1290 ELSE
1291 RETURN FALSE;
1292 END IF;
1293 END Context_exists;
1294
1295
1296 --*****************************************************************************
1297 -- Function AM_Context_Exists
1298 -- Function Type Private
1299 -- Returns BOOLEAN
1300 --
1301 -- DESCRIPTION
1302 -- New function introduced in Attributes Manager that is equivalent to
1303 -- existing Context_Exists function but instead of checking for the existence-- of the context in flexfield tables, checks in the QP_PRC_CONTEXTS_B table.
1304 --
1305 --*****************************************************************************
1306
1307 FUNCTION AM_Context_Exists(p_context_type IN VARCHAR2,
1308 p_context_code IN VARCHAR2)
1309 RETURN BOOLEAN
1310 IS
1311
1312 CURSOR context_code_cur(a_context_type VARCHAR2, a_context_code VARCHAR2)
1313 IS
1314 SELECT prc_context_code
1315 FROM qp_prc_contexts_b
1316 WHERE prc_context_type = a_context_type
1317 AND prc_context_code = a_context_code
1318 AND enabled_flag = 'Y';
1319
1320 l_context_code VARCHAR2(30);
1321
1322 BEGIN
1323
1324 OPEN context_code_cur(p_context_type, p_context_code);
1325
1326 FETCH context_code_cur
1327 INTO l_context_code;
1328
1329 IF context_code_cur%NOTFOUND THEN
1330 CLOSE context_code_cur;
1331 RETURN FALSE;
1332 END IF;
1333
1334 CLOSE context_code_cur;
1335 RETURN TRUE;
1336
1337 END AM_Context_Exists;
1338
1339
1340 -- =======================================================================
1341 -- Function segment_exists
1342 -- funtion type Private
1343 -- Returns BOOLEAN
1344 -- out parameters : p_value_set_id,p_precedence
1345 -- DESCRIPTION
1346 -- Searches for segment name if it exists in the segment list populated by
1347 -- get_segments call.
1348 -- =======================================================================
1349
1350
1351 FUNCTION segment_exists(p_segment_name IN VARCHAR2,
1352 p_segments_dr IN fnd_dflex.segments_dr,
1353 p_check_enabled IN BOOLEAN := TRUE,
1354 p_value_set_id OUT NOCOPY NUMBER,
1355 p_precedence OUT NOCOPY NUMBER) RETURN BOOLEAN IS
1356 BEGIN
1357 IF (p_segments_dr.nsegments > 0) THEN
1358 FOR i IN 1..p_segments_dr.nsegments LOOP
1359 IF p_check_enabled then
1360 IF (p_segments_dr.application_column_name(i) = p_segment_name) and
1361 p_segments_dr.is_enabled(i) THEN ---added bu svdeshmu as per renga/jay's request.
1362 p_value_set_id := p_segments_dr.value_set(i);
1363 p_precedence := p_segments_dr.sequence(i);
1364 RETURN TRUE;
1365 END IF;
1366 ELSE
1367 IF p_segments_dr.application_column_name(i) = p_segment_name THEN
1368 p_value_set_id := p_segments_dr.value_set(i);
1369 p_precedence := p_segments_dr.sequence(i);
1370 RETURN TRUE;
1371 END IF;
1372 END IF;
1373
1374 END LOOP;
1375 RETURN FALSE;
1376 ELSE
1377 RETURN FALSE;
1378 END IF;
1379 END segment_exists;
1380
1381
1382 --*****************************************************************************
1383 -- Function AM_Segment_Exists
1384 -- Function Type Private
1385 -- Returns BOOLEAN
1386 --
1387 -- DESCRIPTION
1388 -- New function introduced in Attributes Manager that is equivalent to
1389 -- existing Segment_Exists function but instead of checking for the
1390 -- existence of the segment in flexfield tables, checks in the QP_SEGMENTS_B
1391 -- table.
1392 --
1393 --*****************************************************************************
1394
1395 FUNCTION AM_Segment_Exists(p_context_type IN VARCHAR2,
1396 p_context_code IN VARCHAR2,
1397 p_segment_mapping_column IN VARCHAR2,
1398 p_check_enabled IN BOOLEAN := TRUE,
1399 x_valueset_id OUT NOCOPY NUMBER,
1400 x_precedence OUT NOCOPY NUMBER)
1401 RETURN BOOLEAN
1402 IS
1403
1404 CURSOR context_id_cur(a_context_type VARCHAR2, a_context_code VARCHAR2)
1405 IS
1406 SELECT prc_context_id
1407 FROM qp_prc_contexts_b
1408 WHERE prc_context_type = a_context_type
1409 AND prc_context_code = a_context_code;
1410
1411 CURSOR enabled_segments_cur(a_context_id NUMBER,
1412 a_segment_mapping_column VARCHAR2,
1413 a_pte_code VARCHAR2)
1414 IS
1415 SELECT nvl(user_valueset_id, seeded_valueset_id),
1416 nvl(user_precedence, seeded_precedence)
1417 FROM qp_segments_b a, qp_pte_segments b
1418 WHERE a.prc_context_id = a_context_id
1419 AND a.segment_mapping_column = a_segment_mapping_column
1420 AND b.pte_code = a_pte_code
1421 AND a.segment_id = b.segment_id
1422 AND b.lov_enabled = 'Y';
1423
1424 CURSOR all_segments_cur(a_context_id NUMBER,
1425 a_segment_mapping_column VARCHAR2,
1426 a_pte_code VARCHAR2)
1427 IS
1428 SELECT nvl(user_valueset_id, seeded_valueset_id),
1429 nvl(user_precedence, seeded_precedence)
1430 FROM qp_segments_b a, qp_pte_segments b
1431 WHERE a.prc_context_id = a_context_id
1432 AND a.segment_mapping_column = a_segment_mapping_column
1433 AND b.pte_code = a_pte_code
1434 AND a.segment_id = b.segment_id;
1435
1436 l_context_id NUMBER;
1437 l_pte_code VARCHAR2(30);
1438
1439 BEGIN
1440
1441 --Get the PTE code for the instance.
1442 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
1443
1444 IF l_pte_code IS NULL THEN
1445 l_pte_code := 'ORDFUL';
1446 END IF;
1447
1448 --Get the context_id
1449 OPEN context_id_cur(p_context_type, p_context_code);
1450
1451 FETCH context_id_cur
1452 INTO l_context_id;
1453
1454 IF context_id_cur%NOTFOUND THEN
1455 CLOSE context_id_cur;
1456 -- RAISE Context Not Found Exception;
1457 END IF;
1458
1459 CLOSE context_id_cur;
1460
1461 --Depending on the value passed in parameter p_check_enabled,
1462 --check the existence of the segment.
1463 IF p_check_enabled THEN --Check against only enabled segments
1464 OPEN enabled_segments_cur(l_context_id, p_segment_mapping_column,
1465 l_pte_code);
1466 FETCH enabled_segments_cur
1467 INTO x_valueset_id, x_precedence;
1468
1469 IF enabled_segments_cur%NOTFOUND THEN
1470 CLOSE enabled_segments_cur;
1471 RETURN FALSE;
1472 END IF;
1473
1474 CLOSE enabled_segments_cur;
1475
1476 ELSE --check against all segments if p_check_enabled is FALSE.
1477 OPEN all_segments_cur(l_context_id, p_segment_mapping_column,
1478 l_pte_code);
1479 FETCH all_segments_cur
1480 INTO x_valueset_id, x_precedence;
1481
1482 IF all_segments_cur%NOTFOUND THEN
1483 CLOSE all_segments_cur;
1484 RETURN FALSE;
1485 END IF;
1486
1487 CLOSE all_segments_cur;
1488
1489 END IF; --p_check_enabled
1490
1491 RETURN TRUE;
1492
1493 END AM_Segment_Exists;
1494
1495
1496
1497 -- =======================================================================
1498 -- Function value_exists
1499 -- funtion type Private
1500 -- Returns BOOLEAN
1501 -- out parameters : None
1502 -- DESCRIPTION
1503 -- Searches for value if it exists in the value set list populated by
1504 -- get_valueset call.
1505 -- =======================================================================
1506
1507
1508 FUNCTION value_exists(p_vsid IN NUMBER,p_value IN VARCHAR2) RETURN BOOLEAN IS
1509 v_vset fnd_vset.valueset_r;
1510 v_fmt fnd_vset.valueset_dr;
1511 v_found BOOLEAN;
1512 v_row NUMBER;
1513 v_value fnd_vset.value_dr;
1514 BEGIN
1515 fnd_vset.get_valueset(p_vsid, v_vset, v_fmt);
1516 fnd_vset.get_value_init(v_vset, TRUE);
1517 fnd_vset.get_value(v_vset, v_row, v_found, v_value);
1518
1519 WHILE(v_found) LOOP
1520 IF (v_value.value = p_value) THEN
1521 fnd_vset.get_value_end(v_vset);
1522 RETURN TRUE;
1523 END IF;
1524 fnd_vset.get_value(v_vset, v_row, v_found, v_value);
1525 END LOOP;
1526 fnd_vset.get_value_end(v_vset);
1527 RETURN FALSE;
1528 END value_exists;
1529
1530 -- ==========================================================================
1531 -- Function value_exists_in_table
1532 -- funtion type Private
1533 -- Returns BOOLEAN
1534 -- out parameters : None
1535 -- DESCRIPTION
1536 -- Searches for value if it exist by building dynamic query stmt when when valueset validation type is F
1537 -- the list populated by get_valueset call.
1538 -- ===========================================================================
1539
1540
1541 FUNCTION value_exists_in_table(p_table_r fnd_vset.table_r,
1542 p_value VARCHAR2,
1543 x_id OUT NOCOPY VARCHAR2,
1544 x_value OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
1545 v_selectstmt VARCHAR2(2000) ; --dhgupta changed length from 500 to 2000 for bug # 1888160
1546 v_cursor_id INTEGER;
1547 v_value VARCHAR2(150);
1548 /* julin - unused variable */
1549 -- v_meaning VARCHAR2(240);
1550 v_id VARCHAR2(150);
1551 v_retval INTEGER;
1552 v_where_clause VARCHAR2(2000); -- bug#13844692
1553 v_cols VARCHAR2(1000);
1554 l_order_by VARCHAR2(1000);
1555 l_pos1 number;
1556 l_where_length number;
1557
1558 /* Added for 3210264 */
1559 type refcur is ref cursor;
1560 v_cursor refcur;
1561
1562 type valueset_cur_type is RECORD (
1563 valueset_value varchar2(150),
1564 valueset_id varchar2(150)
1565 );
1566 valueset_cur valueset_cur_type;
1567
1568 BEGIN
1569 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
1570 --Commented out for 2621644
1571 --IF (p_table_r.id_column_name IS NOT NULL) THEN -- Bug 1982009
1572
1573 --8923075 No validation can be done if there is a bind variable
1574 --attached in the value set where clause.
1575 --1. When using forms, validation is done at client side only.
1576 --2. If there is a API call, validation has to be done before calling
1577 -- this function
1578 IF(p_table_r.where_clause IS NOT NULL
1579 AND INSTR(p_table_r.where_clause,':')>0) THEN
1580 oe_debug_pub.add('Found bind variable in where clause');
1581 oe_debug_pub.add('where clause:' || p_table_r.where_clause);
1582 RETURN true;
1583 END if;
1584 --end 8923075
1585
1586 /* Added for 2492020 */
1587
1588 IF instr(UPPER(p_table_r.where_clause), 'ORDER BY') > 0 THEN
1589 l_order_by := substr(p_table_r.where_clause, instr(UPPER(p_table_r.where_clause), 'ORDER BY'));
1590 v_where_clause := replace(p_table_r.where_clause, l_order_by ,'');
1591 ELSE
1592 v_where_clause := p_table_r.where_clause;
1593 END IF;
1594
1595
1596 -- if instr(upper(p_table_r.where_clause),'WHERE ') > 0 then --Commented out for 2492020
1597 IF instr(upper(v_where_clause),'WHERE') > 0 then --3839853 removed space in 'WHERE '
1598 --to include the id column name in the query
1599
1600 v_where_clause:= rtrim(ltrim(v_where_clause));
1601 l_pos1 := instr(upper(v_where_clause),'WHERE');
1602 l_where_length := LENGTHB('WHERE');
1603 v_where_clause:= substr(v_where_clause,l_pos1+l_where_length);
1604
1605 IF (p_table_r.id_column_name IS NOT NULL) THEN
1606 --included extra quotes for comparing varchar and num values in select
1607 /* Commented out for 2492020
1608 v_where_clause := replace(UPPER(p_table_r.where_clause)
1609 ,'WHERE '
1610 ,'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND ');
1611 */
1612 -- v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND '||v_where_clause; -- 2492020
1613
1614 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val AND '||v_where_clause;--3210264
1615 ELSE
1616 /* Commented out for 2492020
1617 v_where_clause := replace(UPPER(p_table_r.where_clause)
1618 ,'WHERE '
1619 ,'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND ');
1620 */
1621 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND '||v_where_clause;--2492020
1622
1623 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val AND '||v_where_clause;--3210264
1624
1625 END IF;
1626 ELSE
1627
1628
1629
1630 IF v_where_clause IS NOT NULL THEN -- FP 115.88.1159.7
1631 IF (p_table_r.id_column_name IS NOT NULL) THEN
1632 /* Commented out for 2492020
1633 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
1634 */
1635 --Added for 2492020
1636 --v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||v_where_clause;
1637
1638 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val AND '||v_where_clause;--3210264 bug#13585536
1639 ELSE
1640 /* Commented out for 2492020
1641 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
1642 */
1643 --Added for 2492020
1644 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||v_where_clause;
1645
1646 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val AND '||v_where_clause;--3210264 bug#13585536
1647 END IF;
1648 -- begin FP 115.88.1159.7
1649 /* added ELSE block for 3839853 */
1650 ELSE
1651 IF (p_table_r.id_column_name IS NOT NULL) THEN
1652 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val '||v_where_clause;
1653 ELSE
1654 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val '||v_where_clause;
1655 END IF;
1656 -- end FP 115.88.1159.7
1657 END IF;
1658
1659 end if;
1660
1661 IF l_order_by IS NOT NULL THEN
1662 v_where_clause := v_where_clause||' '||l_order_by;
1663 END IF;
1664
1665 /* Commented out for 2621644
1666
1667 ELSE
1668 v_where_clause := p_table_r.where_clause;
1669 END IF;
1670 */
1671 v_cols := p_table_r.value_column_name;
1672 -------------------
1673 --changes made by spgopal for performance problem
1674 --added out parameters to pass back id and value for given valueset id
1675 -------------------
1676
1677 IF (p_table_r.id_column_name IS NOT NULL) THEN
1678
1679 --
1680 -- to_char() conversion function is defined only for
1681 -- DATE and NUMBER datatypes.
1682 --
1683 IF (p_table_r.id_column_type IN ('D', 'N')) THEN
1684 v_cols := v_cols || ' , To_char(' || p_table_r.id_column_name || ')';
1685 ELSE
1686 v_cols := v_cols || ' , ' || p_table_r.id_column_name;
1687 END IF;
1688 ELSE
1689 v_cols := v_cols || ', NULL ';
1690 END IF;
1691
1692
1693
1694 v_selectstmt := 'SELECT '||v_cols||' FROM '||p_table_r.table_name||' '||v_where_clause;
1695
1696 oe_debug_pub.add('select stmt1'||v_selectstmt);
1697 ------------------
1698
1699 /*
1700 IF p_table_r.id_column_name is not null then
1701
1702 v_selectstmt := 'SELECT '||p_table_r.id_column_name||' FROM '||p_table_r.table_name||' '||v_where_clause;
1703
1704 ELSE
1705
1706 v_selectstmt := 'SELECT '||p_table_r.value_column_name||' FROM '||p_table_r.table_name||' '||p_table_r.where_clause;
1707
1708 END IF;
1709
1710
1711 /* Added for 3210264 */
1712
1713 open v_cursor for v_selectstmt using p_value;
1714 fetch v_cursor into valueset_cur;
1715 IF v_Cursor%NOTFOUND THEN
1716 CLOSE v_cursor;
1717 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1718 RETURN FALSE;
1719 END IF;
1720 x_id := valueset_cur.valueset_id;
1721 x_value := valueset_cur.valueset_value;
1722 CLOSE v_cursor;
1723 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1724 RETURN TRUE;
1725
1726
1727 /*
1728 -- parse the query
1729
1730 DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
1731 oe_debug_pub.add('after parse1');
1732 -- Bind the input variables
1733 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,1,v_value,150);
1734 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,2,v_id,150);
1735 v_retval := DBMS_SQL.EXECUTE(v_cursor_id);
1736 LOOP
1737 -- Fetch rows in to buffer and check the exit condition from the loop
1738 IF( DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0) THEN
1739 EXIT;
1740 END IF;
1741 -- Retrieve the rows from buffer into PLSQL variables
1742 DBMS_SQL.COLUMN_VALUE(v_cursor_id,1,v_value);
1743 DBMS_SQL.COLUMN_VALUE(v_cursor_id,2,v_id);
1744
1745 IF v_id IS NULL AND (p_value = v_value) THEN
1746 oe_debug_pub.add('id null, passing value'||p_value||','||v_value);
1747 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1748 x_id := v_id;
1749 x_value := v_value;
1750 RETURN TRUE;
1751 ELSIF (p_value = v_id) THEN
1752 oe_debug_pub.add('id exists, passing id'||p_value||','||v_id);
1753 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1754 x_id := v_id;
1755 x_value := v_value;
1756 RETURN TRUE;
1757 ELSE
1758 Null;
1759 oe_debug_pub.add('value does notmatch, continue search'||p_value||','||v_id);
1760 END IF;
1761 END LOOP;
1762 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1763 RETURN FALSE;
1764 */
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 oe_debug_pub.add('value_exists_in_table exception');
1768 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1769 RETURN FALSE;
1770 END value_exists_in_table;
1771
1772 -- =============================================================================
1773 -- PROCEDURE validate_qp_flexfield
1774 -- procedure type PUBLIC
1775 -- out parameters : context_flag,attribute_flag,context_flag,attribute_flag,value_flag,datatype,precedence,error_code
1776 -- Meaning for error codes
1777 -- errorcode = 0 Successful
1778 -- = 1 flexfield_name is not passed.
1779 -- = 2 context value is not passed
1780 -- = 3 attribute value is not passed.
1781 -- = 4 value is not passed
1782 -- = 5 application short name is not passed
1783
1784 -- = 6 Invalid application short name.
1785 -- = 7 Invalid context passed
1786 -- = 8 Invalid segment passed
1787 -- = 9 Value passed is not a valid value of value set or
1788 -- Value set query is wrongly defined..
1789 -- = 10 Flexfield name is invalid.
1790 -- DESCRIPTION
1791 -- Checks the validity of flexfield,context code,segments and values passed to the procedure.
1792 --==============================================================================
1793
1794 PROCEDURE validate_qp_flexfield(flexfield_name IN VARCHAR2,
1795 context IN VARCHAR2,
1796 attribute IN VARCHAR2,
1797 value IN VARCHAR2,
1798 application_short_name IN VARCHAR2,
1799 context_flag OUT NOCOPY VARCHAR2,
1800 attribute_flag OUT NOCOPY VARCHAR2,
1801 value_flag OUT NOCOPY VARCHAR2,
1802 datatype OUT NOCOPY VARCHAR2,
1803 precedence OUT NOCOPY VARCHAR2,
1804 error_code OUT NOCOPY NUMBER ,
1805 check_enabled IN BOOLEAN := TRUE)
1806 IS
1807
1808 CURSOR Cur_get_application_id(app_short_name VARCHAR2)
1809 IS
1810 SELECT application_id
1811 FROM fnd_application
1812 WHERE application_short_name = app_short_name;
1813
1814 v_context_dr fnd_dflex.contexts_dr;
1815 v_dflex_r fnd_dflex.dflex_r;
1816 v_context_r fnd_dflex.context_r;
1817 v_segments_dr fnd_dflex.segments_dr;
1818 v_value_set_id NUMBER;
1819 v_precedence NUMBER;
1820 v_valueset_r fnd_vset.valueset_r;
1821 v_format_dr fnd_vset.valueset_dr;
1822 v_valueset_dr fnd_vset.valueset_dr;
1823 v_dflex_dr fnd_dflex.dflex_dr;
1824 v_flexfield_val_ind NUMBER DEFAULT 0;
1825 l_value VARCHAR2(150);
1826 l_id VARCHAR2(150);
1827
1828 l_attrmgr_installed VARCHAR2(30);
1829 l_context_type VARCHAR2(30);
1830 l_error_code NUMBER;
1831 l_application_id NUMBER;
1832
1833 BEGIN
1834 --Initialize flags and error_code.
1835 context_flag := 'N';
1836 attribute_flag := 'N';
1837 value_flag := 'N';
1838 error_code := 0;
1839
1840 IF (flexfield_name IS NULL) THEN
1841 error_code := 1; -- flexfield_name is not passed.
1842 RETURN;
1843 END IF;
1844
1845 IF (context IS NULL) THEN
1846 error_code := 2;
1847 RETURN; -- context is not passed
1848 END IF;
1849
1850 IF (attribute IS NULL) THEN
1851 error_code := 3;
1852 RETURN; -- attribute is not passed.
1853 END IF;
1854
1855 IF (value IS NULL) THEN
1856 error_code := 4; -- value is not passed
1857 RETURN;
1858 END IF;
1859
1860 IF (application_short_name IS NULL) THEN
1861 error_code := 5; -- application short name is not passed
1862 RETURN;
1863 END IF;
1864
1865 --Get Attrmgr_Installed status
1866 l_attrmgr_installed := Attrmgr_Installed;
1867
1868 -- Get the application_id
1869
1870 OPEN Cur_get_application_id(application_short_name);
1871
1872 IF l_attrmgr_installed = 'Y' THEN
1873 FETCH Cur_get_application_id INTO l_application_id;
1874 ELSE
1875 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
1876 END IF;
1877
1878 IF (Cur_get_application_id%NOTFOUND) THEN
1879 CLOSE Cur_get_application_id;
1880 error_code := 6; -- Invalid application short name.
1881 RETURN;
1882 END IF;
1883
1884 CLOSE Cur_get_application_id;
1885
1886 -- check if flexfield name passed is a valid one or not.
1887
1888 IF l_attrmgr_installed = 'Y' THEN
1889
1890 IF flexfield_name NOT IN ('QP_ATTR_DEFNS_PRICING',
1891 'QP_ATTR_DEFNS_QUALIFIER')
1892 THEN
1893 error_code := 10; --Invalid Flexfield Name
1894 RETURN;
1895 END IF;
1896
1897 Get_Context_Type(flexfield_name, context,
1898 l_context_type, l_error_code);
1899
1900 IF l_error_code = 0 THEN
1901
1902 IF AM_Context_Exists(l_context_type, context)
1903 THEN
1904 context_flag := 'Y';
1905 ELSE
1906 error_code := 7; -- Invalid context passed
1907 RETURN;
1908 END IF;
1909
1910 IF AM_Segment_Exists(l_context_type, context, attribute,
1911 check_enabled, v_value_set_id, v_precedence)
1912 THEN
1913 precedence := v_precedence;
1914 attribute_flag := 'Y';
1915 ELSE
1916 error_code := 8; -- Invalid Attribute passed
1917 RETURN;
1918 END IF;
1919
1920 END IF; --If l_error_code = 0
1921
1922 ELSE
1923
1924 BEGIN
1925 v_flexfield_val_ind:= 1;
1926 fnd_dflex.get_flexfield(application_short_name,flexfield_name,v_dflex_r,v_dflex_dr);
1927
1928 -- Get the context listing for the flexfield
1929 fnd_dflex.get_contexts(v_dflex_r,v_context_dr);
1930
1931 IF (context_exists(context,v_context_dr,v_context_r) = TRUE) THEN
1932 context_flag := 'Y';
1933 ELSE
1934 context_flag := 'N';
1935 error_code := 7; -- Invalid context passed
1936 RETURN;
1937 END IF;
1938
1939 v_context_r.flexfield := v_dflex_r;
1940
1941 -- Get the enabled segments for the context selected.
1942
1943 --fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
1944 fnd_dflex.get_segments(v_context_r,v_segments_dr,FALSE);
1945
1946 IF (segment_exists(attribute,v_segments_dr,check_enabled,v_value_set_id,v_precedence) = TRUE) THEN
1947 IF (v_precedence IS NOT NULL) THEN
1948 precedence := v_precedence;
1949 END IF;
1950 attribute_flag := 'Y';
1951 IF (v_value_set_id IS NULL) THEN
1952 datatype := 'C';
1953 value_flag := 'Y'; -- If there is no valueset attached then just pass the validation.
1954 error_code := 0;
1955 RETURN;
1956 END IF;
1957 ELSE
1958 attribute_flag :='N';
1959 error_code := 8; -- Invalid segment passed
1960 RETURN;
1961 END IF;
1962
1963 EXCEPTION
1964 WHEN NO_DATA_FOUND THEN
1965 IF (v_flexfield_val_ind = 1) THEN
1966 error_code := 10;
1967 RETURN;
1968 END IF;
1969 END;
1970
1971 END IF; --If l_attrmgr_installed = 'Y'
1972
1973 --Validation of the Value against a Value Set is common to both code paths,
1974 --i.e., with or without Attributes Manager Installed.
1975
1976 -- If there is no valueset attached then just pass the validation.
1977 IF (v_value_set_id IS NULL) THEN
1978 datatype := 'C';
1979 value_flag := 'Y';
1980 error_code := 0;
1981 RETURN;
1982 END IF;
1983
1984 -- Get value set information and validate the value passed.
1985 fnd_vset.get_valueset(v_value_set_id,v_valueset_r,v_valueset_dr);
1986
1987 datatype := v_valueset_dr.format_type;
1988
1989 -- check if there is any value set attached to the segment
1990 IF (v_value_set_id IS NULL or not g_validate_flag)
1991 THEN
1992 value_flag := 'Y';
1993 RETURN;
1994 END IF;
1995
1996 IF (v_valueset_r.validation_type = 'I') THEN --Validation type is independent
1997
1998 IF value_exists(v_value_set_id,value) THEN
1999 value_flag := 'Y';
2000 ELSE
2001 error_code := 9; -- Value does not exist.
2002 END IF;
2003
2004 ELSIF (v_valueset_r.validation_type = 'F') THEN --Validation type is table
2005
2006 IF value_exists_in_table(v_valueset_r.table_info,value,l_id,l_value) THEN
2007 value_flag := 'Y';
2008 ELSE
2009 error_code := 9; -- Value does not exist.
2010 END IF;
2011
2012 ELSIF (v_valueset_r.validation_type = 'N') or datatype in( 'N','X','Y') THEN
2013 ---added for proper handling of dates/number in multilingual envs.
2014 error_code := validate_num_date(datatype,value);
2015
2016 IF error_code = 0 then
2017 value_flag := 'Y';
2018 ELSE
2019 value_flag := 'N';
2020 END IF;
2021
2022 END IF;
2023
2024 END validate_qp_flexfield;
2025
2026
2027 -- =============================================================================
2028 -- PROCEDURE validate_context_code
2029 -- procedure type PUBLIC
2030 -- out parameters : p_error_code
2031 -- Meaning for error codes
2032 -- errorcode = 0 Successfull
2033 -- = 1 flexfield_name is not passed.
2034 -- = 2 context name is not passed
2035 -- = 3 application short name value is not passed.
2036 -- = 4 invalid application short name
2037 -- = 5 invalid flexfield name
2038 -- = 6 Invalid context.
2039 -- DESCRIPTION
2040 -- Validates the context passed to the procedure.
2041 --==============================================================================
2042
2043 PROCEDURE validate_context_code(p_flexfield_name IN VARCHAR2,
2044 p_application_short_name IN VARCHAR2,
2045 p_context_name IN VARCHAR2,
2046 p_error_code OUT NOCOPY NUMBER)
2047 IS
2048 CURSOR Cur_get_application_id(app_short_name VARCHAR2)
2049 IS
2050 SELECT application_id
2051 FROM fnd_application
2052 WHERE application_short_name = app_short_name;
2053
2054 v_flexfield_name NUMBER DEFAULT 1;
2055 v_dflex_r fnd_dflex.dflex_r;
2056 v_context_r fnd_dflex.context_r;
2057 v_context_dr fnd_dflex.contexts_dr;
2058 v_dflex_dr fnd_dflex.dflex_dr;
2059 v_flexfield_val_ind NUMBER;
2060
2061 l_attrmgr_installed VARCHAR2(30);
2062 l_application_id NUMBER;
2063 l_context_type VARCHAR2(30);
2064
2065 BEGIN
2066 IF (p_flexfield_name IS NULL) THEN
2067 p_error_code := 1; -- flexfield_name is not passed.
2068 RETURN;
2069 END IF;
2070
2071 IF (p_context_name IS NULL) THEN
2072 p_error_code := 2;
2073 RETURN; -- context value is not passed
2074 END IF;
2075
2076 IF (p_application_short_name IS NULL) THEN
2077 p_error_code := 3; -- application short name is not passed
2078 RETURN;
2079 END IF;
2080
2081 --Get Attrmgr_Installed status
2082 l_attrmgr_installed := Attrmgr_Installed;
2083
2084 -- Fetch application id for application short name passed.
2085 OPEN Cur_get_application_id(p_application_short_name);
2086
2087 IF l_attrmgr_installed = 'Y' THEN
2088 FETCH Cur_get_application_id INTO l_application_id;
2089 ELSE
2090 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
2091 END IF;
2092
2093 IF (Cur_get_application_id%NOTFOUND) THEN
2094 CLOSE Cur_get_application_id;
2095 p_error_code := 4; -- Invalid application short name.
2096 RETURN;
2097 END IF;
2098
2099 CLOSE Cur_get_application_id;
2100
2101 -- check if flexfield name passed is a valid one or not.
2102
2103 IF l_attrmgr_installed = 'Y' THEN
2104
2105 IF p_flexfield_name NOT IN ('QP_ATTR_DEFNS_PRICING',
2106 'QP_ATTR_DEFNS_QUALIFIER')
2107 THEN
2108 p_error_code := 5; --Invalid Flexfield Name
2109 RETURN;
2110 END IF;
2111
2112 Get_Context_Type(p_flexfield_name,p_context_name,
2113 l_context_type, p_error_code);
2114
2115 IF p_error_code = 0 THEN
2116
2117 IF AM_Context_Exists(l_context_type, p_context_name)
2118 THEN
2119 p_error_code := 0; -- valid context name.
2120 ELSE
2121 p_error_code := 6; -- Invalid context passed
2122 RETURN;
2123 END IF;
2124
2125 END IF; --If p_error_code = 0
2126
2127 ELSE
2128
2129 BEGIN
2130 v_flexfield_val_ind:= 1;
2131 fnd_dflex.get_flexfield(p_application_short_name,p_flexfield_name,
2132 v_dflex_r,v_dflex_dr);
2133
2134 -- Get the context listing for the flexfield
2135 fnd_dflex.get_contexts(v_dflex_r,v_context_dr);
2136
2137 IF (context_exists(p_context_name,v_context_dr,v_context_r) = TRUE)
2138 THEN
2139 p_error_code := 0; -- valid context name.
2140 ELSE
2141 p_error_code := 6; -- Invalid context passed
2142 RETURN;
2143 END IF;
2144
2145 EXCEPTION
2146 WHEN NO_DATA_FOUND THEN
2147 IF (v_flexfield_val_ind = 1) THEN
2148 p_error_code := 5;
2149 RETURN;
2150 END IF;
2151 END;
2152
2153 END IF; --If l_attrmgr_installed = 'Y'
2154
2155 END validate_context_code;
2156
2157 -- =============================================================================
2158 -- PROCEDURE validate_attribute_name
2159 -- procedure type PUBLIC
2160 -- out parameters : p_error_code
2161 -- Meaning for error codes
2162 -- errorcode = 0 Successfull
2163 -- = 1 flexfield_name is not passed.
2164 -- = 2 context name is not passed
2165 -- = 3 application short name value is not passed.
2166 -- = 4 invalid application short name
2167 -- = 5 invalid flexfield name
2168 -- = 6 Invalid context.
2169 -- = 7 No Attribute Passes.
2170 -- = 8 Invalid Attribute.
2171 -- DESCRIPTION
2172 -- Validates the attribute passed to the procedure.
2173 --==============================================================================
2174
2175 PROCEDURE validate_attribute_name(p_application_short_name IN VARCHAR2,
2176 p_flexfield_name IN VARCHAR2,
2177 p_context_name IN VARCHAR2,
2178 p_attribute_name IN VARCHAR2,
2179 p_error_code OUT NOCOPY NUMBER)
2180 IS
2181 CURSOR Cur_get_application_id(app_short_name VARCHAR2) IS
2182 SELECT application_id
2183 FROM fnd_application
2184 WHERE application_short_name = app_short_name;
2185
2186 v_error_code NUMBER DEFAULT 0;
2187 v_dflex_r fnd_dflex.dflex_r;
2188 v_segments_dr fnd_dflex.segments_dr;
2189 v_precedence NUMBER;
2190 v_value_set_id NUMBER;
2191 v_context_r fnd_dflex.context_r;
2192
2193 l_application_id NUMBER;
2194 l_context_type VARCHAR2(30);
2195
2196 BEGIN
2197
2198 Validate_Context_Code(p_flexfield_name,p_application_short_name,
2199 p_context_name,v_error_code);
2200
2201 IF (v_error_code = 0) THEN
2202
2203 IF (p_attribute_name IS NULL) THEN
2204 p_error_code := 7; -- No attribute passed
2205 RETURN;
2206 END IF;
2207
2208 IF Attrmgr_Installed = 'Y' THEN
2209
2210 Get_Context_Type(p_flexfield_name, p_context_name,
2211 l_context_type, p_error_code);
2212
2213 IF p_error_code = 0 THEN
2214
2215 IF AM_Segment_Exists(l_context_type, p_context_name, p_attribute_name,
2216 FALSE, v_value_set_id, v_precedence)
2217 THEN
2218 p_error_code := 0; -- Successful
2219 ELSE
2220 p_error_code := 8; -- Invalid Attribute passed
2221 RETURN;
2222 END IF;
2223
2224 END IF; --If p_error_code is 0
2225
2226 ELSE
2227
2228 -- Just get the application id no validation required.
2229
2230 OPEN Cur_get_application_id(p_application_short_name);
2231 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
2232 CLOSE Cur_get_application_id;
2233
2234 v_dflex_r.flexfield_name := p_flexfield_name;
2235 v_context_r.flexfield := v_dflex_R;
2236 v_context_r.context_code := p_context_name;
2237
2238 -- Get the enabled segments for the context selected.
2239
2240 --FND_DFLEX.GET_SEGMENTS(V_CONTEXT_R,V_SEGMENTS_DR,TRUE);
2241 FND_DFLEX.GET_SEGMENTS(V_CONTEXT_R,V_SEGMENTS_DR,FALSE);
2242
2243 IF (segment_exists(p_attribute_name,v_segments_dr,true,v_value_set_id,
2244 v_precedence) = TRUE)
2245 THEN
2246 p_error_code := 0;
2247 RETURN;
2248 ELSE
2249 p_error_code := 8; -- INVALID ATTRIBUTE PASSED
2250 RETURN;
2251 END IF;
2252
2253 END IF; --If Attrmgr_Installed = 'Y'
2254
2255 ELSE
2256
2257 p_error_code := v_error_code;
2258 RETURN;
2259
2260 END IF; --If v_error_code = 0
2261
2262 END Validate_Attribute_Name;
2263
2264
2265
2266 PROCEDURE Get_Valueset_Id(p_flexfield_name IN VARCHAR2,
2267 p_context IN VARCHAR2 ,
2268 p_seg IN VARCHAR2 ,
2269 x_vsid OUT NOCOPY NUMBER,
2270 x_format_type OUT NOCOPY VARCHAR2,
2271 x_validation_type OUT NOCOPY VARCHAR2)
2272 IS
2273
2274 flexfield fnd_dflex.dflex_r;
2275 flexinfo fnd_dflex.dflex_dr;
2276 test_rec fnd_vset.valueset_r;
2277 x_valuesetid NUMBER := null;
2278 test_frec fnd_vset.valueset_dr;
2279 contexts fnd_dflex.contexts_dr;
2280 i BINARY_INTEGER;
2281 j BINARY_INTEGER;
2282 segments fnd_dflex.segments_dr;
2283
2284 l_context_type VARCHAR2(30);
2285 l_error_code NUMBER;
2286
2287 CURSOR valueset_id_cur(a_context_type VARCHAR2, a_context_code VARCHAR2,
2288 a_segment_code VARCHAR2)
2289 IS
2290 SELECT nvl(a.user_valueset_id, a.seeded_valueset_id)
2291 FROM qp_segments_b a, qp_prc_contexts_b b
2292 WHERE a.prc_context_id = b.prc_context_id
2293 AND b.prc_context_type = a_context_type
2294 AND b.prc_context_code = a_context_code
2295 AND a.segment_code = a_segment_code;
2296
2297
2298 BEGIN
2299
2300 IF Attrmgr_Installed = 'Y' THEN
2301
2302 QP_UTIL.Get_Context_Type(p_flexfield_name, p_context,
2303 l_context_type, l_error_code);
2304
2305 IF l_error_code = 0 THEN
2306
2307 OPEN valueset_id_cur(l_context_type, p_context, p_seg);
2308 FETCH valueset_id_cur INTO x_valuesetid;
2309 CLOSE valueset_id_cur;
2310
2311 END IF; --If l_error_code = 0
2312
2313 ELSE
2314
2315 fnd_dflex.get_flexfield('QP',p_flexfield_name,flexfield,flexinfo);
2316 fnd_dflex.get_contexts(flexfield,contexts);
2317 fnd_dflex.get_segments(fnd_dflex.make_context(flexfield,p_context),segments,true);
2318 FOR j IN 1..segments.nsegments LOOP
2319
2320 IF segments.segment_name(j) = p_seg THEN
2321 x_valuesetid := segments.value_set(j);
2322 END IF;
2323
2324 END LOOP;
2325
2326 END IF; --if Attrmgr_Installed = 'Y'
2327
2328 IF x_valuesetid IS NOT NULL THEN
2329 fnd_vset.get_valueset(x_valuesetid,test_rec,test_frec);
2330 x_vsid :=x_valuesetid;
2331 x_format_type :=test_frec.format_type;
2332 x_validation_type :=test_rec.validation_type;
2333 ELSE
2334 x_vsid := NULL;
2335 x_format_type :='C';
2336 x_validation_type :=NULL;
2337
2338 END IF;
2339
2340 END GET_VALUESET_ID;
2341
2342
2343 PROCEDURE GET_PROD_FLEX_PROPERTIES( PRIC_ATTRIBUTE_CONTEXT IN VARCHAR2,
2344 PRIC_ATTRIBUTE IN VARCHAR2,
2345 PRIC_ATTR_VALUE IN VARCHAR2,
2346 X_DATATYPE OUT NOCOPY VARCHAR2,
2347 X_PRECEDENCE OUT NOCOPY NUMBER,
2348 X_ERROR_CODE OUT NOCOPY NUMBER)
2349 IS
2350
2351 L_CONTEXT_FLAG VARCHAR2(1);
2352 L_ATTRIBUTE_FLAG VARCHAR2(1);
2353 L_VALUE_FLAG VARCHAR2(1);
2354 L_DATATYPE VARCHAR2(1);
2355 L_PRECEDENCE NUMBER;
2356 L_ERROR_CODE NUMBER := 0;
2357
2358 BEGIN
2359
2360 QP_UTIL.VALIDATE_QP_FLEXFIELD(FLEXFIELD_NAME =>'QP_ATTR_DEFNS_PRICING'
2361 ,CONTEXT =>PRIC_ATTRIBUTE_CONTEXT
2362 ,ATTRIBUTE =>PRIC_ATTRIBUTE
2363 ,VALUE =>PRIC_ATTR_VALUE
2364 ,APPLICATION_SHORT_NAME => 'QP'
2365 ,CHECK_ENABLED =>FALSE
2366 ,CONTEXT_FLAG =>L_CONTEXT_FLAG
2367 ,ATTRIBUTE_FLAG =>L_ATTRIBUTE_FLAG
2368 ,VALUE_FLAG =>L_VALUE_FLAG
2369 ,DATATYPE =>L_DATATYPE
2370 ,PRECEDENCE =>L_PRECEDENCE
2371 ,ERROR_CODE =>L_ERROR_CODE
2372 );
2373
2374 X_DATATYPE := NVL(L_DATATYPE,'C');
2375 X_PRECEDENCE := NVL(L_PRECEDENCE,5000);
2376
2377 END GET_PROD_FLEX_PROPERTIES;
2378
2379
2380 PROCEDURE GET_QUAL_FLEX_PROPERTIES( QUAL_ATTRIBUTE_CONTEXT IN VARCHAR2,
2381 QUAL_ATTRIBUTE IN VARCHAR2,
2382 QUAL_ATTR_VALUE IN VARCHAR2,
2383 X_DATATYPE OUT NOCOPY VARCHAR2,
2384 X_PRECEDENCE OUT NOCOPY NUMBER,
2385 X_ERROR_CODE OUT NOCOPY NUMBER)
2386 IS
2387
2388 L_CONTEXT_FLAG VARCHAR2(1);
2389 L_ATTRIBUTE_FLAG VARCHAR2(1);
2390 L_VALUE_FLAG VARCHAR2(1);
2391 L_DATATYPE VARCHAR2(1);
2392 L_PRECEDENCE NUMBER;
2393 L_ERROR_CODE NUMBER := 0;
2394
2395 BEGIN
2396
2397 QP_UTIL.VALIDATE_QP_FLEXFIELD(FLEXFIELD_NAME =>'QP_ATTR_DEFNS_QUALIFIER'
2398 ,CONTEXT =>QUAL_ATTRIBUTE_CONTEXT
2399 ,ATTRIBUTE =>QUAL_ATTRIBUTE
2400 ,VALUE =>QUAL_ATTR_VALUE
2401 ,APPLICATION_SHORT_NAME => 'QP'
2402 ,CHECK_ENABLED =>FALSE
2403 ,CONTEXT_FLAG =>L_CONTEXT_FLAG
2404 ,ATTRIBUTE_FLAG =>L_ATTRIBUTE_FLAG
2405 ,VALUE_FLAG =>L_VALUE_FLAG
2406 ,DATATYPE =>L_DATATYPE
2407 ,PRECEDENCE =>L_PRECEDENCE
2408 ,ERROR_CODE =>L_ERROR_CODE
2409 );
2410
2411 X_DATATYPE := NVL(L_DATATYPE,'C');
2412 X_PRECEDENCE := NVL(L_PRECEDENCE,5000);
2413
2414 END GET_QUAL_FLEX_PROPERTIES;
2415
2416
2417 -- =======================================================================
2418 -- FUNCTION Get_Attribute_Name
2419 -- FUNTION TYPE Public
2420 -- RETURNS APPLICATION_COLUMN_NAME
2421 -- DESCRIPTION
2422 -- searches for segment name and returns coressponding application column name.
2423 -- =======================================================================
2424
2425
2426 FUNCTION Get_Attribute_Name(p_application_short_name IN VARCHAR2,
2427 p_flexfield_name IN VARCHAR2,
2428 p_context_name IN VARCHAR2,
2429 p_attribute_name IN VARCHAR2)
2430 RETURN VARCHAR2
2431 IS
2432
2433 CURSOR cur_get_application_id(app_short_name VARCHAR2)
2434 IS
2435 SELECT application_id
2436 FROM fnd_application
2437 WHERE application_short_name = app_short_name;
2438
2439 v_dflex_r fnd_dflex.dflex_r;
2440 v_segments_dr fnd_dflex.segments_dr;
2441 v_context_r fnd_dflex.context_r;
2442
2443 CURSOR pricing_attribute_name_cur(a_context_code VARCHAR2, a_segment_name VARCHAR2)
2444 IS
2445 SELECT a.segment_mapping_column
2446 FROM qp_segments_v a, qp_prc_contexts_b b
2447 WHERE b.prc_context_id = a.prc_context_id
2448 AND b.prc_context_code = a_context_code
2449 AND a.segment_code = a_segment_name
2450 AND a.segment_mapping_column like 'PRICING%';--deliberately matching a_segment_name
2451 --with segment_code to be consistent
2452 --with old logic/naming convention.
2453
2454 CURSOR qual_attribute_name_cur(a_context_code VARCHAR2, a_segment_name VARCHAR2)
2455 IS
2456 SELECT a.segment_mapping_column
2457 FROM qp_segments_v a, qp_prc_contexts_b b
2458 WHERE b.prc_context_id = a.prc_context_id
2459 AND b.prc_context_code = a_context_code
2460 AND a.segment_code = a_segment_name
2461 AND a.segment_mapping_column like 'QUALIFIER%';--deliberately matching a_segment_name
2462 --with segment_code to be consistent
2463 --with old logic/naming convention.
2464
2465 l_attribute_col_name VARCHAR2(30);
2466
2467 BEGIN
2468
2469 IF Attrmgr_Installed = 'Y' THEN
2470
2471 IF p_flexfield_name = 'QP_ATTR_DEFNS_PRICING' THEN
2472
2473 OPEN pricing_attribute_name_cur(p_context_name, p_attribute_name);
2474 FETCH pricing_attribute_name_cur INTO l_attribute_col_name;
2475 IF pricing_attribute_name_cur%FOUND THEN
2476 CLOSE pricing_attribute_name_cur;
2477 RETURN l_attribute_col_name;
2478 END IF;
2479
2480 CLOSE pricing_attribute_name_cur;
2481
2482 ELSE
2483
2484 OPEN qual_attribute_name_cur(p_context_name, p_attribute_name);
2485 FETCH qual_attribute_name_cur INTO l_attribute_col_name;
2486 IF qual_attribute_name_cur%FOUND THEN
2487 CLOSE qual_attribute_name_cur;
2488 RETURN l_attribute_col_name;
2489 END IF;
2490
2491 CLOSE qual_attribute_name_cur;
2492
2493 END IF;
2494
2495 ELSE
2496
2497 OPEN cur_get_application_id(p_application_short_name);
2498 FETCH cur_get_application_id INTO v_dflex_r.application_id;
2499 CLOSE cur_get_application_id;
2500
2501 v_dflex_r.flexfield_name := p_flexfield_name;
2502 v_context_r.flexfield := v_dflex_r;
2503 v_context_r.context_code := p_context_name;
2504
2505 -- get the enabled segments for the context selected.
2506 fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
2507
2508 IF (v_segments_dr.nsegments > 0) THEN
2509
2510 FOR i IN 1..v_segments_dr.nsegments LOOP
2511
2512 IF (v_segments_dr.segment_name(I) = p_attribute_name) THEN
2513
2514 RETURN (v_segments_dr.application_column_name(i));
2515
2516 END IF;
2517
2518 END LOOP;
2519
2520 ELSE
2521
2522 RETURN('0');
2523
2524 END IF;
2525
2526 END IF; --if Attrmgr_Installed = 'Y'
2527
2528 RETURN NULL;
2529
2530 END Get_Attribute_Name;
2531
2532
2533
2534 FUNCTION check_context_existance( p_application_id IN fnd_application.application_id%TYPE,
2535 p_descriptive_flexfield_name IN VARCHAR2,
2536 p_descr_flex_context_code IN VARCHAR2)
2537 RETURN BOOLEAN
2538
2539 IS
2540
2541 dummy NUMBER(1);
2542 x_context_exists BOOLEAN := TRUE;
2543
2544 BEGIN
2545 SELECT NULL INTO dummy
2546 FROM fnd_descr_flex_contexts
2547 WHERE application_id = p_application_id
2548 AND descriptive_flexfield_name = p_descriptive_flexfield_name
2549 AND descriptive_flex_context_code = p_descr_flex_context_code;
2550
2551 --dbms_output.put_line ('Context Check Successful');
2552 return x_context_exists;
2553
2554 EXCEPTION
2555 WHEN no_data_found THEN
2556 x_context_exists := FALSE;
2557 return x_context_exists;
2558 WHEN OTHERS THEN
2559 NULL;
2560 --dbms_output.put_line ('Error in Context Check');
2561 END;
2562
2563 FUNCTION check_segment_existance( p_application_id NUMBER,
2564 p_context_code VARCHAR2,
2565 p_flexfield_name VARCHAR2,
2566 p_application_column_name VARCHAR2)
2567 RETURN BOOLEAN
2568 IS
2569
2570 dummy NUMBER(1);
2571 x_seg_exists BOOLEAN := TRUE;
2572
2573 BEGIN
2574 select NULL INTO dummy
2575 from FND_DESCR_FLEX_COLUMN_USAGES
2576 where APPLICATION_ID = p_application_id
2577 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code
2578 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2579 and APPLICATION_COLUMN_NAME = p_application_column_name;
2580
2581 --dbms_output.put_line ('Segment Check Successful');
2582 return x_seg_exists ;
2583
2584 EXCEPTION
2585 WHEN no_data_found THEN
2586 x_seg_exists := FALSE;
2587 return x_seg_exists;
2588 WHEN OTHERS THEN
2589 NULL;
2590 --dbms_output.put_line ('Error in Segment Check');
2591 END;
2592
2593 FUNCTION check_segment_name_existance( p_application_id NUMBER,
2594 p_context_code VARCHAR2,
2595 p_flexfield_name VARCHAR2,
2596 p_segment_name VARCHAR2)
2597 RETURN BOOLEAN
2598 IS
2599
2600 dummy NUMBER(1);
2601 x_seg_exists BOOLEAN := TRUE;
2602
2603 BEGIN
2604 select NULL INTO dummy
2605 from FND_DESCR_FLEX_COLUMN_USAGES
2606 where APPLICATION_ID = p_application_id
2607 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code
2608 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2609 and END_USER_COLUMN_NAME = p_segment_name;
2610
2611 --dbms_output.put_line ('Segment Name Check Successful');
2612 return x_seg_exists ;
2613
2614 EXCEPTION
2615 WHEN no_data_found THEN
2616 x_seg_exists := FALSE;
2617 return x_seg_exists;
2618 WHEN OTHERS THEN
2619 NULL;
2620 --dbms_output.put_line ('Error in Segment Name Check');
2621 END;
2622
2623 PROCEDURE QP_UPGRADE_CONTEXT( P_PRODUCT IN VARCHAR2
2624 , P_NEW_PRODUCT IN VARCHAR2
2625 , P_FLEXFIELD_NAME IN VARCHAR2
2626 , P_NEW_FLEXFIELD_NAME IN VARCHAR2)
2627 IS
2628 P_FLEXFIELD FND_DFLEX.DFLEX_R;
2629 P_FLEXINFO FND_DFLEX.DFLEX_DR;
2630 L_CONTEXTS FND_DFLEX.CONTEXTS_DR;
2631 GDE_CONTEXTS FND_DFLEX.CONTEXTS_DR;
2632 L_SEGMENTS FND_DFLEX.SEGMENTS_DR;
2633 GDE_SEGMENTS FND_DFLEX.SEGMENTS_DR;
2634 NEW_GDE_SEGMENTS FND_DFLEX.SEGMENTS_DR;
2635 L_REQUIRED VARCHAR2(5);
2636 L_SECURITY_ENABLED VARCHAR2(5);
2637
2638 L_VALUE_SET_ID NUMBER := 0;
2639 L_VALUE_SET VARCHAR2(100) := NULL;
2640 L_SEGMENT_COUNT NUMBER;
2641 p_segment_name VARCHAR2(240);
2642 NEW_GDE_CONTEXT_CODE CONSTANT VARCHAR2(30) := 'Upgrade Context';
2643 OLD_GDE_CONTEXT_CODE CONSTANT VARCHAR2(30) := 'Global Data Elements';
2644 G_QP_ATTR_DEFNS_PRICING CONSTANT VARCHAR2(30) := 'QP_ATTR_DEFNS_PRICING';
2645 QP_APPLICATION_ID CONSTANT fnd_application.application_id%TYPE := 661;
2646 p_context_name VARCHAR2(240);
2647 p_application_column_name VARCHAR2(240);
2648 p_application_id VARCHAR2(30);
2649 BEGIN
2650
2651 FND_FLEX_DSC_API.SET_SESSION_MODE('customer_data');
2652
2653 FND_PROFILE.PUT('RESP_APPL_ID','0');
2654 FND_PROFILE.PUT('RESP_ID','20419');
2655 FND_PROFILE.PUT('USER_ID','1001');
2656
2657 -- Delete all the segments under the New Global Data Elements Context(if any)
2658
2659 --dbms_output.put_line ('Before even starting the process');
2660 IF ( FND_FLEX_DSC_API.FLEXFIELD_EXISTS( P_NEW_PRODUCT,
2661 P_NEW_FLEXFIELD_NAME )) THEN
2662 --dbms_output.put_line ('Entered the Processing');
2663 IF (P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING) THEN
2664 -- Get the New Global Data Elements Context and Its Segments
2665 FND_DFLEX.GET_FLEXFIELD( P_NEW_PRODUCT
2666 , P_NEW_FLEXFIELD_NAME
2667 , P_FLEXFIELD
2668 , P_FLEXINFO );
2669
2670 -- Get all contexts for the flexfield
2671 FND_DFLEX.GET_CONTEXTS( P_FLEXFIELD, L_CONTEXTS );
2672
2673 -- Get the Context Code for New Global Data Elements Context (if any)
2674 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
2675 --dbms_output.put_line ('Found the Old GDE Context');
2676 IF (L_CONTEXTS.CONTEXT_CODE(I) = OLD_GDE_CONTEXT_CODE) THEN
2677 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , OLD_GDE_CONTEXT_CODE)
2678 ,NEW_GDE_SEGMENTS
2679 , FALSE ) ;
2680 END IF;
2681 EXIT;
2682 END LOOP;
2683
2684 IF (NEW_GDE_SEGMENTS.NSEGMENTS > 0) THEN
2685 --dbms_output.put_line('New GDE has segments');
2686 FOR I IN 1..NEW_GDE_SEGMENTS.NSEGMENTS LOOP
2687 --dbms_output.put_line('Trying to delete segments under old context');
2688 FND_FLEX_DSC_API.DELETE_SEGMENT( P_NEW_PRODUCT
2689 ,P_NEW_FLEXFIELD_NAME
2690 ,OLD_GDE_CONTEXT_CODE -- Global Data Elements
2691 ,NEW_GDE_SEGMENTS.SEGMENT_NAME(I));
2692 END LOOP;
2693 ELSE
2694 NULL;
2695 --dbms_output.put_line('New GDE has no segments');
2696 END IF; -- NEW_GDE_SEGMENTS.NSEGMENTS > 0
2697 END IF;
2698 END IF;
2699
2700 --dbms_output.put_line('Starting the actual Migration');
2701 -- Now start the migration of contexts and segments
2702 FND_DFLEX.GET_FLEXFIELD(
2703 P_PRODUCT
2704 , P_FLEXFIELD_NAME
2705 , P_FLEXFIELD
2706 , P_FLEXINFO );
2707
2708 FND_DFLEX.GET_CONTEXTS( P_FLEXFIELD, L_CONTEXTS );
2709
2710 -- Store all the old contexts
2711 GDE_CONTEXTS := L_CONTEXTS;
2712
2713 -- Check To See If New Flex Structure Exists
2714 IF ( FND_FLEX_DSC_API.FLEXFIELD_EXISTS( P_NEW_PRODUCT,
2715 P_NEW_FLEXFIELD_NAME )) THEN
2716 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
2717 --dbms_output.put_line ( ' Global Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
2718 IF (L_CONTEXTS.CONTEXT_CODE(I) = OLD_GDE_CONTEXT_CODE AND P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING) THEN
2719 --dbms_output.put_line('There are contexts for migration');
2720 IF (check_context_existance(QP_APPLICATION_ID,P_NEW_FLEXFIELD_NAME,NEW_GDE_CONTEXT_CODE) = FALSE) THEN
2721 --dbms_output.put_line ('Creating the Upgrade Context');
2722 FND_FLEX_DSC_API.CREATE_CONTEXT ( P_NEW_PRODUCT
2723 , P_NEW_FLEXFIELD_NAME
2724 , NEW_GDE_CONTEXT_CODE
2725 , NEW_GDE_CONTEXT_CODE
2726 , NEW_GDE_CONTEXT_CODE
2727 , 'Y'
2728 , 'N') ;
2729 --dbms_output.put_line ('Created the Upgrade Context');
2730 ELSE
2731 NULL;
2732 --dbms_output.put_line ('Upgrade Context Already Exists');
2733 END IF;
2734
2735 FND_FLEX_DSC_API.ENABLE_CONTEXT (P_NEW_PRODUCT
2736 , P_NEW_FLEXFIELD_NAME
2737 , NEW_GDE_CONTEXT_CODE
2738 , TRUE );
2739
2740 FND_FLEX_DSC_API.ENABLE_COLUMNS( P_NEW_PRODUCT
2741 , P_NEW_FLEXFIELD_NAME
2742 , 'ATTRIBUTE[0-9]+');
2743
2744 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , L_CONTEXTS.CONTEXT_CODE(I))
2745 ,L_SEGMENTS
2746 , FALSE ) ;
2747
2748
2749 -- Store all the old global data elements' segments
2750 GDE_SEGMENTS := L_SEGMENTS;
2751
2752 --dbms_output.put_line ( 'Old GDE Segments Count##: ' || nvl(GDE_SEGMENTS.NSEGMENTS,0));
2753
2754 FOR J IN 1..L_SEGMENTS.NSEGMENTS LOOP
2755 L_VALUE_SET_ID := L_SEGMENTS.VALUE_SET(J);
2756 BEGIN
2757 IF L_VALUE_SET_ID <> 0 THEN
2758 SELECT FLEX_VALUE_SET_NAME INTO
2759 L_VALUE_SET
2760 FROM FND_FLEX_VALUE_SETS
2761 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
2762 ELSE
2763 L_VALUE_SET := NULL;
2764 END IF;
2765 EXCEPTION
2766 WHEN NO_DATA_FOUND THEN
2767 L_VALUE_SET := NULL;
2768 WHEN TOO_MANY_ROWS THEN
2769 NULL;
2770 END;
2771
2772 IF (L_SEGMENTS.IS_REQUIRED(J) ) THEN
2773 L_REQUIRED := 'Y';
2774 ELSE
2775 L_REQUIRED := 'N';
2776 END IF;
2777
2778 IF (L_SEGMENTS.IS_ENABLED(J) ) THEN
2779 L_SECURITY_ENABLED := 'Y';
2780 ELSE
2781 L_SECURITY_ENABLED := 'N';
2782 END IF;
2783
2784
2785 IF (check_segment_existance(QP_APPLICATION_ID,
2786 NEW_GDE_CONTEXT_CODE,
2787 P_NEW_FLEXFIELD_NAME,
2788 L_SEGMENTS.APPLICATION_COLUMN_NAME(J)) = FALSE ) THEN
2789 IF (check_segment_name_existance(QP_APPLICATION_ID,
2790 NEW_GDE_CONTEXT_CODE,
2791 P_NEW_FLEXFIELD_NAME,
2792 L_SEGMENTS.SEGMENT_NAME(J)) = FALSE ) THEN
2793 p_segment_name := L_SEGMENTS.SEGMENT_NAME(J);
2794 ELSE
2795 p_segment_name := 'QP: ' || L_SEGMENTS.SEGMENT_NAME(J); -- Create new name
2796 END IF;
2797
2798 -- Storing the values for error handling
2799 p_context_name := NEW_GDE_CONTEXT_CODE;
2800 p_application_column_name := L_SEGMENTS.APPLICATION_COLUMN_NAME(J);
2801 p_application_id := QP_APPLICATION_ID;
2802
2803 --dbms_output.put_line ('Creating the Upgrade Context Segments');
2804 BEGIN
2805 FND_FLEX_DSC_API.CREATE_SEGMENT (
2806 APPL_SHORT_NAME => P_NEW_PRODUCT
2807 , FLEXFIELD_NAME => P_NEW_FLEXFIELD_NAME
2808 , CONTEXT_NAME => NEW_GDE_CONTEXT_CODE
2809 , NAME => p_segment_name
2810 , COLUMN => L_SEGMENTS.APPLICATION_COLUMN_NAME(J)
2811 , DESCRIPTION => L_SEGMENTS.DESCRIPTION(J)
2812 , SEQUENCE_NUMBER => J
2813 , ENABLED => 'Y'
2814 , DISPLAYED => 'Y'
2815 , VALUE_SET => L_VALUE_SET
2816 , DEFAULT_TYPE => NULL
2817 , DEFAULT_VALUE => NULL
2818 , REQUIRED => 'Y'
2819 , SECURITY_ENABLED => 'N'
2820 , DISPLAY_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2821 , DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2822 , CONCATENATED_DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2823 , LIST_OF_VALUES_PROMPT => L_SEGMENTS.COLUMN_PROMPT(J)
2824 , WINDOW_PROMPT => L_SEGMENTS.ROW_PROMPT(J)
2825 , RANGE => NULL
2826 , SRW_PARAMETER => NULL) ;
2827 EXCEPTION
2828 WHEN NO_DATA_FOUND THEN
2829 rollback;
2830 Log_Error(p_id1 => -9999,
2831 p_error_type => 'ERROR IN CREATING SEGMENT',
2832 p_error_desc => ' Application Id : ' || p_application_id ||
2833 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
2834 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
2835 ' Context Name : ' || p_context_name ||
2836 ' Application Column Name : ' || p_application_column_name ||
2837 ' Application Segment Name : ' || p_segment_name ,
2838 p_error_module => 'QP_Upgrade_Context');
2839 raise;
2840 END;
2841 END IF;
2842 END LOOP; -- L_SEGMENTS
2843 --EXIT;
2844 END IF; -- Global Data Elements
2845 END LOOP; -- L_CONTEXTS
2846
2847 --dbms_output.put_line('Total Context Count: ' || L_CONTEXTS.NCONTEXTS);
2848 -- Process other contexts(other than Global Data Elements)
2849 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
2850 IF ((L_CONTEXTS.CONTEXT_CODE(I) <> OLD_GDE_CONTEXT_CODE AND P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING)
2851 OR (P_NEW_FLEXFIELD_NAME <> G_QP_ATTR_DEFNS_PRICING)) THEN
2852 --dbms_output.put_line ('Before Other Context Existance Check');
2853 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
2854 IF (check_context_existance(QP_APPLICATION_ID,P_NEW_FLEXFIELD_NAME,L_CONTEXTS.CONTEXT_CODE(I)) = FALSE) THEN
2855 --dbms_output.put_line ('Creating Other Contexts');
2856 FND_FLEX_DSC_API.CREATE_CONTEXT ( P_NEW_PRODUCT
2857 , P_NEW_FLEXFIELD_NAME
2858 , L_CONTEXTS.CONTEXT_CODE(I)
2859 , L_CONTEXTS.CONTEXT_NAME(I)
2860 , L_CONTEXTS.CONTEXT_DESCRIPTION(I)
2861 , 'Y'
2862 , 'N') ;
2863
2864 END IF;
2865
2866 FND_FLEX_DSC_API.ENABLE_CONTEXT ( P_NEW_PRODUCT
2867 , P_NEW_FLEXFIELD_NAME
2868 , L_CONTEXTS.CONTEXT_CODE(I) --2847218 changed to CONTEXT_CODE
2869 , TRUE );
2870
2871 FND_FLEX_DSC_API.ENABLE_COLUMNS(P_NEW_PRODUCT
2872 , P_NEW_FLEXFIELD_NAME
2873 , 'ATTRIBUTE[0-9]+');
2874
2875 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , L_CONTEXTS.CONTEXT_CODE(I))
2876 ,L_SEGMENTS
2877 , FALSE ) ;
2878
2879 L_SEGMENT_COUNT := L_SEGMENTS.NSEGMENTS;
2880 --dbms_output.put_line ('Other Context Segment Count : ' || L_SEGMENT_COUNT);
2881
2882 FOR J IN 1..L_SEGMENTS.NSEGMENTS LOOP
2883 L_VALUE_SET_ID := L_SEGMENTS.VALUE_SET(J);
2884 BEGIN
2885 IF L_VALUE_SET_ID <> 0 THEN
2886 SELECT FLEX_VALUE_SET_NAME INTO
2887 L_VALUE_SET
2888 FROM FND_FLEX_VALUE_SETS
2889 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
2890 ELSE
2891 L_VALUE_SET := NULL;
2892 END IF;
2893 EXCEPTION
2894 WHEN NO_DATA_FOUND THEN
2895 L_VALUE_SET := NULL;
2896 WHEN TOO_MANY_ROWS THEN
2897 NULL;
2898 END;
2899
2900 IF (L_SEGMENTS.IS_REQUIRED(J) ) THEN
2901 L_REQUIRED := 'Y';
2902 ELSE
2903 L_REQUIRED := 'N';
2904 END IF;
2905
2906 IF (L_SEGMENTS.IS_ENABLED(J) ) THEN
2907 L_SECURITY_ENABLED := 'Y';
2908 ELSE
2909 L_SECURITY_ENABLED := 'N';
2910 END IF;
2911
2912 --dbms_output.put_line ('Before Other Context Segment Existance Check');
2913 --dbms_output.put_line ('Before Segment Existance Check for Old Gde Segments');
2914 --dbms_output.put_line ('Flexfield Name : ' || P_NEW_FLEXFIELD_NAME);
2915 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
2916 --dbms_output.put_line ('Application Column Name : ' || L_SEGMENTS.APPLICATION_COLUMN_NAME(J));
2917 IF (check_segment_existance(QP_APPLICATION_ID,
2918 L_CONTEXTS.CONTEXT_CODE(I),
2919 P_NEW_FLEXFIELD_NAME,
2920 L_SEGMENTS.APPLICATION_COLUMN_NAME(J)) = FALSE ) THEN
2921 --dbms_output.put_line ('Segment check false');
2922 IF (check_segment_name_existance(QP_APPLICATION_ID,
2923 NEW_GDE_CONTEXT_CODE,
2924 P_NEW_FLEXFIELD_NAME,
2925 L_SEGMENTS.SEGMENT_NAME(J)) = FALSE ) THEN
2926 --dbms_output.put_line ('Segment name check false');
2927 p_segment_name := L_SEGMENTS.SEGMENT_NAME(J);
2928 ELSE
2929 p_segment_name := 'QP: ' || L_SEGMENTS.SEGMENT_NAME(J);
2930 END IF;
2931
2932 -- Storing the values for error handling
2933 p_context_name := L_CONTEXTS.CONTEXT_CODE(I);
2934 p_application_column_name := L_SEGMENTS.APPLICATION_COLUMN_NAME(J);
2935 p_application_id := QP_APPLICATION_ID;
2936
2937 --dbms_output.put_line ('Creating Other Contexts Segments ');
2938 BEGIN
2939 FND_FLEX_DSC_API.CREATE_SEGMENT (
2940 APPL_SHORT_NAME => P_NEW_PRODUCT
2941 , FLEXFIELD_NAME => P_NEW_FLEXFIELD_NAME
2942 , CONTEXT_NAME => L_CONTEXTS.CONTEXT_CODE(I) --2847218 changed to CONTEXT_CODE
2943 , NAME => p_segment_name
2944 , COLUMN => L_SEGMENTS.APPLICATION_COLUMN_NAME(J)
2945 , DESCRIPTION => L_SEGMENTS.DESCRIPTION(J)
2946 , SEQUENCE_NUMBER => J
2947 , ENABLED => 'Y'
2948 , DISPLAYED => 'Y'
2949 , VALUE_SET => L_VALUE_SET
2950 , DEFAULT_TYPE => NULL
2951 , DEFAULT_VALUE => NULL
2952 , REQUIRED => 'Y'
2953 , SECURITY_ENABLED => 'N'
2954 , DISPLAY_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2955 , DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2956 , CONCATENATED_DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2957 , LIST_OF_VALUES_PROMPT => L_SEGMENTS.COLUMN_PROMPT(J)
2958 , WINDOW_PROMPT => L_SEGMENTS.ROW_PROMPT(J)
2959 , RANGE => NULL
2960 , SRW_PARAMETER => NULL) ;
2961 EXCEPTION
2962 WHEN NO_DATA_FOUND THEN
2963 rollback;
2964 Log_Error(p_id1 => -9999,
2965 p_error_type => 'ERROR IN CREATING SEGMENT',
2966 p_error_desc => ' Application Id : ' || p_application_id ||
2967 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
2968 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
2969 ' Context Name : ' || p_context_name ||
2970 ' Application Column Name : ' || p_application_column_name ||
2971 ' Application Segment Name : ' || p_segment_name ,
2972 p_error_module => 'QP_Upgrade_Context');
2973 raise;
2974 END ;
2975 END IF;
2976 END LOOP; -- L_SEGMENTS
2977
2978 -- Append all the global data segments into other contexts
2979 --dbms_output.put_line ('Old GDE SEGMENTS Count : ' || nvl(GDE_SEGMENTS.NSEGMENTS,0));
2980 IF (nvl(GDE_SEGMENTS.NSEGMENTS,0) > 0) THEN
2981 FOR K IN 1..GDE_SEGMENTS.NSEGMENTS LOOP
2982 L_VALUE_SET_ID := GDE_SEGMENTS.VALUE_SET(K);
2983 BEGIN
2984 IF L_VALUE_SET_ID <> 0 THEN
2985 SELECT FLEX_VALUE_SET_NAME INTO
2986 L_VALUE_SET
2987 FROM FND_FLEX_VALUE_SETS
2988 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
2989 ELSE
2990 L_VALUE_SET := NULL;
2991 END IF;
2992 EXCEPTION
2993 WHEN NO_DATA_FOUND THEN
2994 L_VALUE_SET := NULL;
2995 WHEN TOO_MANY_ROWS THEN
2996 NULL;
2997 END;
2998
2999 --dbms_output.put_line ('GDE SEGMENTS Loop 1');
3000 IF (GDE_SEGMENTS.IS_REQUIRED(K) ) THEN
3001 L_REQUIRED := 'Y';
3002 ELSE
3003 L_REQUIRED := 'N';
3004 END IF;
3005
3006 IF (GDE_SEGMENTS.IS_ENABLED(K) ) THEN
3007 L_SECURITY_ENABLED := 'Y';
3008 ELSE
3009 L_SECURITY_ENABLED := 'N';
3010 END IF;
3011
3012 --dbms_output.put_line ('Before Segment Existance Check for Old Gde Segments');
3013 --dbms_output.put_line ('Flexfield Name : ' || P_NEW_FLEXFIELD_NAME);
3014 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
3015 --dbms_output.put_line ('Application Column Name : ' || GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K));
3016 IF (check_segment_existance(QP_APPLICATION_ID,
3017 L_CONTEXTS.CONTEXT_CODE(I),
3018 P_NEW_FLEXFIELD_NAME,
3019 GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K)) = FALSE ) THEN
3020 --dbms_output.put_line ('Segment check false');
3021 IF (check_segment_name_existance(QP_APPLICATION_ID,
3022 L_CONTEXTS.CONTEXT_CODE(I),
3023 P_NEW_FLEXFIELD_NAME,
3024 GDE_SEGMENTS.SEGMENT_NAME(K)) = FALSE ) THEN
3025 --dbms_output.put_line ('Segment name check false');
3026 p_segment_name := GDE_SEGMENTS.SEGMENT_NAME(K);
3027 ELSE
3028 p_segment_name := 'QP: ' || GDE_SEGMENTS.SEGMENT_NAME(K);
3029 END IF;
3030
3031 -- Storing the values for error handling
3032 p_context_name := L_CONTEXTS.CONTEXT_CODE(I);
3033 p_application_column_name := GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K);
3034 p_application_id := QP_APPLICATION_ID;
3035
3036 --dbms_output.put_line ('Creating the OLD Gde segments to all contexts');
3037 BEGIN
3038 FND_FLEX_DSC_API.CREATE_SEGMENT (
3039 APPL_SHORT_NAME => P_NEW_PRODUCT
3040 , FLEXFIELD_NAME =>P_NEW_FLEXFIELD_NAME
3041 , CONTEXT_NAME => L_CONTEXTS.CONTEXT_CODE(I) --2847218 changed to CONTEXT_CODE
3042 , NAME => p_segment_name
3043 , COLUMN => GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K)
3044 , DESCRIPTION => GDE_SEGMENTS.DESCRIPTION(K)
3045 , SEQUENCE_NUMBER => L_SEGMENT_COUNT + K
3046 , ENABLED => 'Y'
3047 , DISPLAYED => 'Y'
3048 , VALUE_SET => L_VALUE_SET
3049 , DEFAULT_TYPE => NULL
3050 , DEFAULT_VALUE => NULL
3051 , REQUIRED => 'Y'
3052 , SECURITY_ENABLED => 'N'
3053 , DISPLAY_SIZE => GDE_SEGMENTS.DISPLAY_SIZE(K)
3054 , DESCRIPTION_SIZE => GDE_SEGMENTS.DISPLAY_SIZE(K)
3055 , CONCATENATED_DESCRIPTION_SIZE => GDE_SEGMENTS.DISPLAY_SIZE(K)
3056 , LIST_OF_VALUES_PROMPT => GDE_SEGMENTS.COLUMN_PROMPT(K)
3057 , WINDOW_PROMPT => GDE_SEGMENTS.ROW_PROMPT(K)
3058 , RANGE => NULL
3059 , SRW_PARAMETER => NULL) ;
3060 EXCEPTION
3061 WHEN NO_DATA_FOUND THEN
3062 rollback;
3063 Log_Error(p_id1 => -9999,
3064 p_error_type => 'ERROR IN CREATING SEGMENT',
3065 p_error_desc => ' Application Id : ' || p_application_id ||
3066 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
3067 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
3068 ' Context Name : ' || p_context_name ||
3069 ' Application Column Name : ' || p_application_column_name ||
3070 ' Application Segment Name : ' || p_segment_name ,
3071 p_error_module => 'QP_Upgrade_Context');
3072 raise;
3073 END ;
3074 END IF;
3075 END LOOP; -- GDE_SEGMENTS
3076 END IF; -- GDE_SEGMENTS.NSEGMENTS > 0
3077 END IF; -- Global Data Elements
3078 END LOOP; -- CONTEXTS
3079 END IF; /* CHECK FOR NEW FLEX FIELD STRUCTURE EXISTS */
3080 EXCEPTION
3081 WHEN OTHERS THEN
3082 --dbms_output.put_line(fnd_flex_dsc_api.message);
3083 rollback;
3084 Log_Error(p_id1 => -6501,
3085 p_error_type => 'FLEXFIELD UPGRADE',
3086 p_error_desc => fnd_flex_dsc_api.message,
3087 p_error_module => 'QP_Upgrade_Context');
3088 raise;
3089 END QP_UPGRADE_CONTEXT;
3090
3091 PROCEDURE LOG_ERROR( P_ID1 VARCHAR2,
3092 P_ID2 VARCHAR2 :=NULL,
3093 P_ID3 VARCHAR2 :=NULL,
3094 P_ID4 VARCHAR2 :=NULL,
3095 P_ID5 VARCHAR2 :=NULL,
3096 P_ID6 VARCHAR2 :=NULL,
3097 P_ID7 VARCHAR2 :=NULL,
3098 P_ID8 VARCHAR2 :=NULL,
3099 P_ERROR_TYPE VARCHAR2,
3100 P_ERROR_DESC VARCHAR2,
3101 P_ERROR_MODULE VARCHAR2) AS
3102
3103 PRAGMA AUTONOMOUS_TRANSACTION;
3104
3105 BEGIN
3106
3107 INSERT INTO QP_UPGRADE_ERRORS(ERROR_ID,UPG_SESSION_ID,ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ERROR_TYPE,
3108 ERROR_DESC,ERROR_MODULE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,
3109 LAST_UPDATED_BY,LAST_UPDATE_LOGIN) VALUES
3110 (QP_UPGRADE_ERRORS_S.NEXTVAL,USERENV('SESSIONID'),
3111 P_ID1,P_ID2,P_ID3,P_ID4,P_ID5,P_ID6,P_ID7,P_ID8,
3112 P_ERROR_TYPE, SUBSTR(P_ERROR_DESC,1,240),P_ERROR_MODULE,SYSDATE,
3113 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID);
3114 COMMIT;
3115
3116 END;
3117
3118
3119 PROCEDURE get_segs_for_flex( flexfield_name IN VARCHAR2,
3120 application_short_name IN VARCHAR2,
3121 x_segs_upg_t OUT NOCOPY v_segs_upg_tab,
3122 error_code OUT NOCOPY number) IS
3123
3124 CURSOR Cur_get_application_id(app_short_name VARCHAR2) IS
3125 SELECT application_id
3126 FROM fnd_application
3127 WHERE application_short_name = app_short_name;
3128
3129 v_context_dr fnd_dflex.contexts_dr;
3130 v_dflex_r fnd_dflex.dflex_r;
3131 v_context_r fnd_dflex.context_r;
3132 v_segments_dr fnd_dflex.segments_dr;
3133 v_value_set_id NUMBER;
3134 v_precedence NUMBER;
3135 v_valueset_r fnd_vset.valueset_r;
3136 v_format_dr fnd_vset.valueset_dr;
3137 v_valueset_dr fnd_vset.valueset_dr;
3138 v_dflex_dr fnd_dflex.dflex_dr;
3139 v_flexfield_val_ind NUMBER DEFAULT 0;
3140 J NUMBER := 0;
3141
3142 BEGIN
3143
3144 error_code := 0;
3145
3146 IF (flexfield_name IS NULL) THEN
3147 error_code := 1; -- flexfield_name is not passed.
3148 RETURN;
3149 END IF;
3150
3151 IF (application_short_name IS NULL) THEN
3152 error_code := 5; -- application short name is not passed
3153 RETURN;
3154 END IF;
3155
3156 -- Get the application_id
3157
3158 OPEN Cur_get_application_id(application_short_name);
3159 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
3160 IF (Cur_get_application_id%NOTFOUND) THEN
3161 CLOSE Cur_get_application_id;
3162 error_code := 6; -- Invalid application short name.
3163 RETURN;
3164 END IF;
3165 CLOSE Cur_get_application_id;
3166
3167 -- check if flexfield name passed is a valid one or not.
3168 v_flexfield_val_ind:= 1;
3169 fnd_dflex.get_flexfield(application_short_name,flexfield_name,v_dflex_r,v_dflex_dr);
3170
3171 -- Get the context listing for the flexfield
3172 fnd_dflex.get_contexts(v_dflex_r,v_context_dr);
3173
3174 For i in 1..v_context_dr.ncontexts LOOP
3175
3176 --dbms_output.put_line('context code -1 is : ' || v_context_dr.context_code(i));
3177
3178 v_context_r.context_code := v_context_dr.context_code(i);
3179 v_context_r.flexfield := v_dflex_r;
3180
3181
3182
3183 fnd_dflex.get_segments(v_context_r,v_segments_dr);
3184
3185 FOR K IN 1..v_segments_dr.nsegments LOOP
3186
3187 J := J + 1;
3188
3189 x_segs_upg_t(J).context_code := v_context_dr.context_code(i);
3190 x_segs_upg_t(J).segment_name := v_segments_dr.application_column_name(K);
3191
3192 -- Get value set information and validate the value passed.
3193
3194
3195 IF v_segments_dr.value_set(K) is null then
3196
3197 x_segs_upg_t(J).datatype := 'C';
3198
3199 ELSE
3200
3201 fnd_vset.get_valueset(v_segments_dr.value_set(K),v_valueset_r,v_valueset_dr);
3202 x_segs_upg_t(J).datatype := v_valueset_dr.format_type;
3203
3204 END IF;
3205
3206 x_segs_upg_t(J).sequence := v_segments_dr.sequence(K);
3207
3208
3209 END LOOP;
3210
3211 End Loop;
3212
3213 error_code := 0;
3214
3215 EXCEPTION
3216
3217 WHEN NO_DATA_FOUND THEN
3218 IF (v_flexfield_val_ind = 1) THEN
3219 error_code := 10;
3220 RETURN;
3221 END IF;
3222
3223 END get_segs_for_flex;
3224
3225 PROCEDURE get_segs_flex_precedence(p_segs_upg_t IN v_segs_upg_tab,
3226 p_context IN VARCHAR2,
3227 p_attribute IN VARCHAR2,
3228 x_precedence OUT NOCOPY NUMBER,
3229 x_datatype OUT NOCOPY VARCHAR2)
3230 IS
3231
3232
3233 v_segs_upg_ind number := 0;
3234
3235 BEGIN
3236
3237 x_datatype := 'C';
3238 x_precedence := 5000;
3239 v_segs_upg_ind := p_segs_upg_t.FIRST;
3240
3241 while v_segs_upg_ind is not null loop
3242
3243 IF ( ( p_segs_upg_t(v_segs_upg_ind).context_code = p_context )
3244 AND ( p_segs_upg_t(v_segs_upg_ind).segment_name = p_attribute )
3245 ) THEN
3246
3247 x_precedence := p_segs_upg_t(v_segs_upg_ind).sequence;
3248 x_datatype := p_segs_upg_t(v_segs_upg_ind).datatype;
3249 RETURN;
3250
3251 END IF;
3252
3253 v_segs_upg_ind := p_segs_upg_t.NEXT(v_segs_upg_ind);
3254
3255 end loop;
3256
3257 END get_segs_flex_precedence;
3258
3259 FUNCTION GET_NUM_DATE_FROM_CANONICAL(p_datatype IN VARCHAR2
3260 ,p_value IN VARCHAR2
3261 )RETURN VARCHAR2 IS
3262
3263 l_varchar_out varchar2(2000);
3264 INVALID_DATA_TYPE EXCEPTION;
3265
3266 BEGIN
3267
3268 IF p_datatype = 'N' THEN
3269
3270 l_varchar_out := to_char(qp_number.canonical_to_number(p_value));
3271 Elsif p_datatype = 'X' THEN
3272 l_varchar_out :=
3273 fnd_date.canonical_to_date(p_value);
3274 Elsif p_datatype = 'Y' THEN
3275 l_varchar_out :=
3276 fnd_date.canonical_to_date(p_value);
3277 Elsif p_datatype = 'C' THEN
3278 l_varchar_out := p_value;
3279 ELse
3280 l_varchar_out := p_value;
3281
3282 END IF;
3283
3284 RETURN l_varchar_out;
3285
3286 EXCEPTION
3287 When Others Then
3288 l_varchar_out := p_value;
3289
3290
3291 END GET_NUM_DATE_FROM_CANONICAL;
3292
3293 PROCEDURE GET_VALUESET_ID_R(P_FLEXFIELD_NAME IN VARCHAR2,
3294 P_CONTEXT IN VARCHAR2 ,
3295 P_SEG IN VARCHAR2 ,
3296 X_VSID OUT NOCOPY NUMBER,
3297 X_FORMAT_TYPE OUT NOCOPY VARCHAR2,
3298 X_VALIDATION_TYPE OUT NOCOPY VARCHAR2
3299 )IS
3300
3301 L_Valueset_R FND_VSET.VALUESET_R;
3302 X_VALUESETID NUMBER;
3303 L_valueset_dr FND_VSET.VALUESET_DR;
3304
3305 CURSOR Cur_get_application_id(app_short_name VARCHAR2) IS
3306 SELECT application_id
3307 FROM fnd_application
3308 WHERE application_short_name = app_short_name;
3309
3310 v_dflex_r fnd_dflex.dflex_r;
3311 v_segments_dr fnd_dflex.segments_dr;
3312 v_context_r fnd_dflex.context_r;
3313 BEGIN
3314 OPEN Cur_get_application_id('QP');
3315 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
3316 CLOSE Cur_get_application_id;
3317 v_dflex_r.flexfield_name := p_flexfield_name;
3318 v_context_r.flexfield := v_dflex_r;
3319 v_context_r.context_code := p_context;
3320 -- Get the enabled segments for the context selected.
3321
3322 fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
3323 -- IF (v_segments_dr.nsegments > 0) THEN
3324
3325 FOR i IN 1..v_segments_dr.nsegments LOOP
3326 IF (v_segments_dr.application_column_name(i) = p_seg) THEN
3327 X_VALUESETID := v_SEGMENTS_dr.VALUE_SET(i);
3328 exit;
3329 END IF;
3330 END LOOP;
3331
3332 IF X_VALUESETID IS NOT NULL THEN
3333 FND_VSET.GET_VALUESET(X_VALUESETID,l_valueset_r,l_valueset_dr);
3334 X_VSID :=X_VALUESETID;
3335 X_FORMAT_TYPE :=l_valueset_dr.FORMAT_TYPE;
3336 X_VALIDATION_TYPE :=l_valueset_r.VALIDATION_TYPE;
3337 ELSE
3338 X_VSID :=NULL;
3339 X_FORMAT_TYPE :='C';
3340 X_VALIDATION_TYPE :=NULL;
3341
3342 END IF;
3343 end GET_VALUESET_ID_R;
3344
3345 FUNCTION Get_Attribute_Value_Meaning(p_FlexField_Name IN VARCHAR2
3346 ,p_Context_Name IN VARCHAR2
3347 ,p_segment_name IN VARCHAR2
3348 ,p_attr_value IN VARCHAR2
3349 ,p_comparison_operator_code IN VARCHAR2 := NULL
3350 ) RETURN VARCHAR2 IS
3351
3352 Vset FND_VSET.valueset_r;
3353 Fmt FND_VSET.valueset_dr;
3354
3355 Found BOOLEAN;
3356 Row NUMBER;
3357 Value FND_VSET.value_dr;
3358
3359
3360
3361 x_Format_Type Varchar2(1);
3362 x_Validation_Type Varchar2(1);
3363 x_Vsid NUMBER;
3364
3365
3366 x_attr_value_code VARCHAR2(240);
3367 x_attr_meaning VARCHAR2(1000);
3368 l_attr_value VARCHAR2(2000);
3369
3370
3371 Value_Valid_In_Valueset BOOLEAN := FALSE;
3372
3373 l_id VARCHAR2(240);
3374 l_value VARCHAR2(240);
3375 l_meaning VARCHAR2(1000);
3376
3377 BEGIN
3378
3379
3380 qp_util.get_valueset_id(p_FlexField_Name,p_Context_Name,
3381 p_Segment_Name,x_Vsid,
3382 x_Format_Type, x_Validation_Type);
3383
3384
3385 l_attr_value := get_num_date_from_canonical(x_format_type,p_attr_value);
3386
3387 -- if comparison operator is other than then no need to get the
3388 -- meaning as the value itself will be stored in qualifier_attr_value
3389
3390 --change made by spgopal. added parameter called p_comparison_operator_code
3391 --to generalise the code for all forms and packages
3392
3393 If p_comparison_operator_code <> 'BETWEEN' THEN
3394
3395 IF x_Validation_Type In('F' ,'I') AND x_Vsid IS NOT NULL THEN
3396 --dbms_output.put_line('valueset found');
3397
3398
3399 IF x_Validation_Type = 'I' THEN
3400 --dbms_output.put_line('validation type = I');
3401
3402 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3403 FND_VSET.get_value_init(Vset,TRUE);
3404 FND_VSET.get_value(Vset,Row,Found,Value);
3405
3406
3407 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3408 While(Found) Loop
3409 --dbms_output.put_line('ID is defined');
3410
3411
3412 If l_attr_value = Value.id Then
3413
3414 x_attr_value_code := Value.value;
3415 --dbms_output.put_line('1 x_attr_value_code = ' || x_attr_value_code);
3416 x_attr_meaning := Value.meaning;
3417 --dbms_output.put_line('1 x_attr_meaning = ' || x_attr_meaning);
3418 Value_Valid_In_Valueset := TRUE;
3419 EXIT;
3420 End If;
3421 FND_VSET.get_value(Vset,Row,Found,Value);
3422
3423 End Loop;
3424
3425 Else -- id not defined.Hence compare for value
3426
3427 While(Found) Loop
3428 --dbms_output.put_line('ID is not defined');
3429
3430 If l_attr_value = Value.value Then
3431
3432 x_attr_value_code := l_attr_value;
3433 --dbms_output.put_line('2 x_attr_value_code = ' || x_attr_value_code);
3434 x_attr_meaning := Value.meaning;
3435 --dbms_output.put_line('2 x_attr_meaning = ' || x_attr_meaning);
3436 Value_Valid_In_Valueset := TRUE;
3437 EXIT;
3438 End If;
3439 FND_VSET.get_value(Vset,Row,Found,Value);
3440
3441 End Loop;
3442
3443 End If; ---end of Fmt.Has_Id
3444
3445 FND_VSET.get_value_end(Vset);
3446
3447 ELSIF X_Validation_type = 'F' THEN
3448 --dbms_output.put_line('validation type = F');
3449
3450 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3451
3452 IF (QP_UTIL.value_exists_in_table(Vset.table_info,l_attr_value,l_id,l_value,l_meaning)) THEN
3453
3454
3455 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3456 --dbms_output.put_line('ID is defined');
3457 If l_attr_value = l_id Then
3458
3459 x_attr_value_code := l_value;
3460 --dbms_output.put_line('3 x_attr_value_code = ' || x_attr_value_code);
3461 x_attr_meaning := l_meaning;
3462 --dbms_output.put_line('3 x_attr_meaning = ' || x_attr_meaning);
3463 Value_Valid_In_Valueset := TRUE;
3464 End If;
3465 Else -- id not defined.Hence compare for value
3466 --dbms_output.put_line('ID is not defined');
3467 If l_attr_value = l_value Then
3468
3469 x_attr_value_code := l_attr_value;
3470 --dbms_output.put_line('4 x_attr_value_code = ' || x_attr_value_code);
3471 x_attr_meaning := l_meaning;
3472 --dbms_output.put_line('4 x_attr_meaning = ' || x_attr_meaning);
3473 Value_Valid_In_Valueset := TRUE;
3474 End If;
3475 End if; -- End of Fmt.Has_ID
3476
3477 ELSE
3478 Value_Valid_In_Valueset := FALSE;
3479 END IF;
3480
3481 END IF; --X_Validation_Type
3482
3483
3484 ELSE -- if validation type is not F or I or valueset id is null (not defined)
3485 --dbms_output.put_line('Value set ID is not found');
3486
3487 x_attr_value_code := l_attr_value;
3488 --dbms_output.put_line('5 x_attr_value_code = ' || x_attr_value_code);
3489 x_attr_meaning := l_attr_value;
3490 --dbms_output.put_line('5 x_attr_meaning = ' || x_attr_meaning);
3491
3492 END IF;
3493 ELSE -- if comparison operator is 'between'
3494
3495 x_attr_value_code := l_attr_value;
3496 --dbms_output.put_line('6 x_attr_value_code = ' || x_attr_value_code);
3497 x_attr_meaning := l_attr_value;
3498 --dbms_output.put_line('6 x_attr_meaning = ' || x_attr_meaning);
3499
3500 END IF;
3501
3502
3503 RETURN x_attr_meaning;
3504
3505 END Get_Attribute_Value_Meaning;
3506
3507 FUNCTION Get_Attribute_Value(p_FlexField_Name IN VARCHAR2
3508 ,p_Context_Name IN VARCHAR2
3509 ,p_segment_name IN VARCHAR2
3510 ,p_attr_value IN VARCHAR2
3511 ,p_comparison_operator_code IN VARCHAR2 := NULL
3512 ) RETURN VARCHAR2 IS
3513
3514 Vset FND_VSET.valueset_r;
3515 Fmt FND_VSET.valueset_dr;
3516
3517 Found BOOLEAN;
3518 Row NUMBER;
3519 Value FND_VSET.value_dr;
3520
3521
3522
3523 x_Format_Type Varchar2(1);
3524 x_Validation_Type Varchar2(1);
3525 x_Vsid NUMBER;
3526
3527
3528 x_attr_value_code VARCHAR2(240);
3529 l_attribute_code VARCHAR2(240);
3530 l_segment_name VARCHAR2(240);
3531 l_attr_value VARCHAR2(2000);
3532
3533
3534 Value_Valid_In_Valueset BOOLEAN := FALSE;
3535
3536 l_id VARCHAR2(240);
3537 l_value VARCHAR2(240);
3538
3539 BEGIN
3540 -- bug 3531203 - POST: QUALIFIER DESCRIPTION ON PRICE LIST MAINTENANCE PAGE SHOWS INTERNAL IDS
3541 -- call api qp_util.get_valueset_id as qp_util.get_valueset_id_r does not look into attribute management schema
3542
3543 /*qp_util.get_valueset_id_r(p_FlexField_Name,p_Context_Name,
3544 p_Segment_Name,x_Vsid,
3545 x_Format_Type, x_Validation_Type);*/
3546
3547 Get_Attribute_Code(p_FlexField_Name,p_Context_Name, p_Segment_Name,
3548 l_attribute_code,
3549 l_segment_name);
3550
3551 qp_util.get_valueset_id(p_FlexField_Name,p_Context_Name,
3552 l_Segment_Name,x_Vsid,
3553 x_Format_Type, x_Validation_Type);
3554
3555 l_attr_value := get_num_date_from_canonical(x_format_type,p_attr_value);
3556
3557 -- if comparison operator is other than then no need to get the
3558 -- meaning as the value itself will be stored in qualifier_attr_value
3559
3560 --change made by spgopal. added parameter called p_comparison_operator_code
3561 --to generalise the code for all forms and packages
3562
3563 If p_comparison_operator_code <> 'BETWEEN' THEN
3564
3565 IF x_Validation_Type In('F' ,'I') AND x_Vsid IS NOT NULL THEN
3566
3567
3568 IF x_Validation_Type = 'I' THEN
3569
3570 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3571 FND_VSET.get_value_init(Vset,TRUE);
3572 FND_VSET.get_value(Vset,Row,Found,Value);
3573
3574
3575 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3576 While(Found) Loop
3577
3578
3579 If l_attr_value = Value.id Then
3580
3581 x_attr_value_code := Value.value;
3582 Value_Valid_In_Valueset := TRUE;
3583 EXIT;
3584 End If;
3585 FND_VSET.get_value(Vset,Row,Found,Value);
3586
3587 End Loop;
3588
3589 Else -- id not defined.Hence compare for value
3590
3591 While(Found) Loop
3592
3593 If l_attr_value = Value.value Then
3594
3595 x_attr_value_code := l_attr_value;
3596 Value_Valid_In_Valueset := TRUE;
3597 EXIT;
3598 End If;
3599 FND_VSET.get_value(Vset,Row,Found,Value);
3600
3601 End Loop;
3602
3603 End If; ---end of Fmt.Has_Id
3604
3605 FND_VSET.get_value_end(Vset);
3606
3607 ELSIF X_Validation_type = 'F' THEN
3608
3609 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3610
3611 IF (QP_UTIL.value_exists_in_table(Vset.table_info,l_attr_value,l_id,l_value)) THEN
3612
3613
3614 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3615 If l_attr_value = l_id Then
3616
3617 x_attr_value_code := l_value;
3618 Value_Valid_In_Valueset := TRUE;
3619 End If;
3620 Else -- id not defined.Hence compare for value
3621 If l_attr_value = l_value Then
3622
3623 x_attr_value_code := l_attr_value;
3624 Value_Valid_In_Valueset := TRUE;
3625 End If;
3626 End if; -- End of Fmt.Has_ID
3627
3628 ELSE
3629 Value_Valid_In_Valueset := FALSE;
3630 END IF;
3631
3632 END IF; --X_Validation_Type
3633
3634
3635 ELSE -- if validation type is not F or I or valueset id is null (not defined)
3636
3637 x_attr_value_code := l_attr_value;
3638
3639 END IF;
3640 ELSE -- if comparison operator is 'between'
3641
3642 x_attr_value_code := l_attr_value;
3643
3644 END IF;
3645
3646
3647 RETURN x_attr_value_code;
3648
3649 END Get_Attribute_Value;
3650
3651 FUNCTION Get_Salesrep(p_salesrep_id IN NUMBER)
3652 RETURN VARCHAR2
3653 IS
3654
3655 l_name VARCHAR2(240);
3656
3657 CURSOR salesrep_cur(a_salesrep_id NUMBER)
3658 IS
3659 select name
3660 from ra_salesreps r
3661 where r.salesrep_id = a_salesrep_id;
3662
3663 BEGIN
3664 OPEN salesrep_cur(p_salesrep_id);
3665 FETCH salesrep_cur
3666 INTO l_name;
3667 CLOSE salesrep_cur;
3668
3669 RETURN l_name;
3670
3671 EXCEPTION
3672 WHEN OTHERS THEN
3673 CLOSE salesrep_cur;
3674
3675 END Get_Salesrep;
3676
3677 FUNCTION Get_Term(p_term_id IN NUMBER)
3678 RETURN VARCHAR2
3679 IS
3680
3681 l_name VARCHAR2(240);
3682
3683 CURSOR term_cur(a_term_id NUMBER)
3684 IS
3685 select t.name
3686 from ra_terms_b b ,ra_terms_tl t
3687 where b.term_id = a_term_id and
3688 b.term_id = t.term_id and
3689 t.language = userenv('LANG');
3690
3691 BEGIN
3692 OPEN term_cur(p_term_id);
3693 FETCH term_cur
3694 INTO l_name;
3695 CLOSE term_cur;
3696
3697 RETURN l_name;
3698
3699 EXCEPTION
3700 WHEN OTHERS THEN
3701 CLOSE term_cur;
3702
3703 END Get_Term;
3704
3705
3706 /***************************************************************************/
3707
3708
3709 -- ==========================================================================
3710 -- Function value_exists_in_table overloaded
3711 -- funtion type Private
3712 -- Returns BOOLEAN
3713 -- out parameters : None
3714 -- DESCRIPTION
3715 -- Searches for value if it exist by building dynamic query stmt when when valueset validation type is F
3716 -- the list populated by get_valueset call.
3717 -- ===========================================================================
3718
3719
3720 FUNCTION value_exists_in_table(p_table_r fnd_vset.table_r,
3721 p_value VARCHAR2,
3722 x_id OUT NOCOPY VARCHAR2,
3723 x_value OUT NOCOPY VARCHAR2,
3724 x_meaning OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
3725 v_selectstmt VARCHAR2(2000) ; --dhgupta changed length from 500 to 2000 for bug # 1888160
3726 v_cursor_id INTEGER;
3727 v_value VARCHAR2(150);
3728 /* julin (2271729) - increased size for UTF8 */
3729 v_meaning VARCHAR2(1000);
3730 v_id VARCHAR2(150);
3731 v_retval INTEGER;
3732 v_where_clause VARCHAR2(2000); -- bug#13844692
3733 v_cols VARCHAR2(1000);
3734 l_order_by VARCHAR2(1000);
3735 l_pos1 number;
3736 l_where_length number;
3737
3738 /* Added for 3210264 */
3739 type refcur is ref cursor;
3740 v_cursor refcur;
3741
3742 type valueset_cur_type is RECORD (
3743 valueset_value varchar2(150),
3744 valueset_id varchar2(150),
3745 valueset_meaning varchar2(1000)
3746 );
3747 valueset_cur valueset_cur_type;
3748
3749
3750 BEGIN
3751 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
3752
3753 --Commented out for 2621644
3754 --IF (p_table_r.id_column_name IS NOT NULL) THEN -- Bug 1982009
3755
3756 /* Added for 2492020 */
3757
3758 IF instr(UPPER(p_table_r.where_clause), 'ORDER BY') > 0 THEN
3759 l_order_by := substr(p_table_r.where_clause, instr(UPPER(p_table_r.where_clause), 'ORDER BY'));
3760 v_where_clause := replace(p_table_r.where_clause, l_order_by ,'');
3761 ELSE
3762 v_where_clause := p_table_r.where_clause;
3763 END IF;
3764
3765 --8923075 removing the where clause if there is a bind variable
3766 --attached in where clause as this will be added in sql statement.
3767 --Further this will be exectued to get value_meaning
3768 IF(p_table_r.where_clause IS NOT NULL
3769 AND INSTR(p_table_r.where_clause,':')>0) THEN
3770 oe_debug_pub.add('Found bind variable in where clause,so truncating the where clause');
3771 oe_debug_pub.add('where clause:' || p_table_r.where_clause);
3772 v_where_clause := '';
3773 END if;
3774
3775 -- if instr(upper(p_table_r.where_clause),'WHERE ') > 0 then --Commented out for 2492020
3776 IF instr(upper(v_where_clause),'WHERE') > 0 then --3839853 removed space in 'WHERE '
3777 --to include the id column name in the query
3778
3779 v_where_clause:= rtrim(ltrim(v_where_clause));
3780 l_pos1 := instr(upper(v_where_clause),'WHERE');
3781 l_where_length :=LENGTHB('WHERE');
3782 v_where_clause:= substr(v_where_clause,l_pos1+l_where_length);
3783
3784
3785 IF (p_table_r.id_column_name IS NOT NULL) THEN
3786 --included extra quotes for comparing varchar and num values in select
3787 /* Commented out for 2492020
3788 v_where_clause := replace(UPPER(p_table_r.where_clause)
3789 ,'WHERE '
3790 ,'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND ');
3791 */
3792 --v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND '||v_where_clause;--2492020
3793
3794 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val AND '||v_where_clause;--3210264
3795 ELSE
3796 /* Commented out for 2492020
3797 v_where_clause := replace(UPPER(p_table_r.where_clause)
3798 ,'WHERE '
3799 ,'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND ');
3800 */
3801 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND '||v_where_clause;--2492020
3802
3803 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val AND '||v_where_clause;--3210264
3804 END IF;
3805
3806 else
3807 IF v_where_clause IS NOT NULL THEN -- FP 115.88.1159.7
3808 IF (p_table_r.id_column_name IS NOT NULL) THEN
3809 /* Commented out for 2492020
3810 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
3811 */
3812 --Added for 2492020
3813 --v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||v_where_clause;
3814
3815 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val AND '||v_where_clause;--3210264 --13703332
3816 ELSE
3817 /* Commented out for 2492020
3818 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
3819 */
3820 --Added for 2492020
3821 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||v_where_clause;
3822
3823 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val AND '||v_where_clause;--3210264 --13703332
3824 END IF;
3825
3826 /* added ELSE block for 3839853 */
3827 ELSE
3828
3829 IF (p_table_r.id_column_name IS NOT NULL) THEN
3830 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val '||v_where_clause;
3831 ELSE
3832 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val '||v_where_clause;
3833 END IF;
3834
3835 END IF;
3836 end if;
3837 /* Commented out for 2621644
3838 ELSE
3839 v_where_clause := p_table_r.where_clause;
3840 END IF;
3841 */
3842
3843 v_cols :=p_table_r.value_column_name;
3844
3845 -------------------
3846 --changes made by spgopal for performance problem
3847 --added out parameters to pass back id and value for given valueset id
3848 -------------------
3849
3850 IF (p_table_r.id_column_name IS NOT NULL) THEN
3851
3852 --
3853 -- to_char() conversion function is defined only for
3854 -- DATE and NUMBER datatypes.
3855 --
3856 IF (p_table_r.id_column_type IN ('D', 'N')) THEN
3857 v_cols := v_cols || ' , To_char(' || p_table_r.id_column_name || ')';
3858 ELSE
3859 v_cols := v_cols || ' , ' || p_table_r.id_column_name;
3860 END IF;
3861 ELSE
3862 v_cols := v_cols || ', NULL ';
3863 END IF;
3864
3865
3866 if p_table_r.meaning_column_name is not null then
3867 v_cols := v_cols||','||p_table_r.meaning_column_name;
3868 else
3869 null;
3870 v_cols := v_cols || ', NULL '; --Added for 3210264
3871 end if;
3872
3873 v_selectstmt := 'SELECT '||v_cols||' FROM '||p_table_r.table_name||' '||v_where_clause;
3874
3875 oe_debug_pub.add('select stmt2'||v_selectstmt);
3876
3877 ------------------
3878
3879 /*
3880 IF p_table_r.id_column_name is not null then
3881
3882 v_selectstmt := 'SELECT '||p_table_r.id_column_name||' FROM '||p_table_r.table_name||' '||v_where_clause;
3883
3884 ELSE
3885
3886 v_selectstmt := 'SELECT '||p_table_r.value_column_name||' FROM '||p_table_r.table_name||' '||p_table_r.where_clause;
3887
3888 END IF;
3889 */
3890
3891
3892 /* Added for 3210264 */
3893
3894 open v_cursor for v_selectstmt using p_value;
3895 fetch v_cursor into valueset_cur;
3896 IF v_Cursor%NOTFOUND THEN
3897 CLOSE v_cursor;
3898 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3899 RETURN FALSE;
3900 END IF;
3901 x_id := valueset_cur.valueset_id;
3902 x_value := valueset_cur.valueset_value;
3903 If valueset_cur.valueset_meaning is NOT NULL THEN
3904 x_meaning:= valueset_cur.valueset_meaning;
3905 Else
3906 x_meaning:= valueset_cur.valueset_value;
3907 End If;
3908 CLOSE v_cursor;
3909 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3910 RETURN TRUE;
3911
3912 /*
3913 -- parse the query
3914
3915 DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
3916 oe_debug_pub.add('after parse2');
3917 -- Bind the input variables
3918 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,1,v_value,150);
3919 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,2,v_id,150);
3920 if p_table_r.meaning_column_name IS NOT NULL THEN
3921 -- julin (2271729) - increased size for UTF8
3922 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,3,v_meaning,1000);
3923 end if;
3924 v_retval := DBMS_SQL.EXECUTE(v_cursor_id);
3925 LOOP
3926 -- Fetch rows in to buffer and check the exit condition from the loop
3927 IF( DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0) THEN
3928 EXIT;
3929 END IF;
3930 -- Retrieve the rows from buffer into PLSQL variables
3931 DBMS_SQL.COLUMN_VALUE(v_cursor_id,1,v_value);
3932 DBMS_SQL.COLUMN_VALUE(v_cursor_id,2,v_id);
3933 if p_table_r.meaning_column_name IS NOT NULL THEN
3934 DBMS_SQL.COLUMN_VALUE(v_cursor_id,3,v_meaning);
3935 end if;
3936
3937
3938 IF v_id IS NULL AND (p_value = v_value) THEN
3939 oe_debug_pub.add('id null, passing value'||p_value||','||v_value||' '||v_meaning);
3940 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3941 x_id := v_id;
3942 x_value := v_value;
3943 --added this to return meaning
3944 x_meaning := v_meaning;
3945 RETURN TRUE;
3946 ELSIF (p_value = v_id) THEN
3947 oe_debug_pub.add('id exists, passing id'||p_value||','||v_id||' '||v_meaning);
3948 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3949 x_id := v_id;
3950 x_value := v_value;
3951 --added this to return meaning
3952 if v_meaning is not null then
3953 x_meaning := v_meaning;
3954 else --if meaning not defined in vset, return value
3955 x_meaning := v_value;
3956 end if;
3957 RETURN TRUE;
3958 ELSE
3959 Null;
3960 oe_debug_pub.add('value does notmatch, continue search'||p_value||','||v_id);
3961 END IF;
3962 END LOOP;
3963 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3964 RETURN FALSE;
3965 */
3966 EXCEPTION
3967 WHEN OTHERS THEN
3968 oe_debug_pub.add('value_exists_in_table exception');
3969 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3970 RETURN FALSE;
3971 END value_exists_in_table;
3972
3973 PROCEDURE CORRECT_ACTIVE_DATES(p_active_date_first_type IN OUT NOCOPY VARCHAR2,
3974 p_start_date_active_first IN OUT NOCOPY DATE,
3975 p_end_date_active_first IN OUT NOCOPY DATE,
3976 p_active_date_second_type IN OUT NOCOPY VARCHAR2,
3977 p_start_date_active_second IN OUT NOCOPY DATE,
3978 p_end_date_active_second IN OUT NOCOPY DATE)
3979 is
3980 l_active_date_type_temp VARCHAR2(30);
3981 l_start_date_active_temp DATE;
3982 l_end_date_active_temp DATE;
3983 BEGIN
3984
3985 IF (p_active_date_first_type = 'ORD'
3986 AND p_active_date_second_type = 'ORD') THEN
3987 -- Make the second value NULL
3988 p_active_date_second_type := NULL;
3989 p_start_date_active_second := NULL;
3990 p_end_date_active_second := NULL;
3991
3992 ELSIF (p_active_date_first_type is NULL
3993 AND p_active_date_second_type = 'ORD') THEN
3994 -- Assign the second value to first and make the second NULL
3995 p_active_date_first_type := p_active_date_second_type;
3996 p_start_date_active_first := p_start_date_active_second;
3997 p_end_date_active_first := p_end_date_active_second;
3998
3999 p_active_date_second_type := NULL;
4000 p_start_date_active_second := NULL;
4001 p_end_date_active_second := NULL;
4002
4003 ELSIF p_active_date_first_type = 'SHIP' THEN
4004 IF p_active_date_second_type = 'ORD' THEN
4005 -- Swap the values of first and second
4006 l_active_date_type_temp := p_active_date_first_type;
4007 l_start_date_active_temp := p_start_date_active_first;
4008 l_end_date_active_temp := p_end_date_active_first;
4009
4010 p_active_date_first_type := p_active_date_second_type;
4011 p_start_date_active_first := p_start_date_active_second;
4012 p_end_date_active_first := p_end_date_active_second;
4013
4014 p_active_date_second_type := l_active_date_type_temp;
4015 p_start_date_active_second := l_start_date_active_temp;
4016 p_end_date_active_second := l_end_date_active_temp;
4017
4018 ELSIF p_active_date_second_type is NULL THEN
4019 -- Assign the first value to second and make the first NULL
4020 p_active_date_second_type := p_active_date_first_type;
4021 p_start_date_active_second := p_start_date_active_first;
4022 p_end_date_active_second := p_end_date_active_first;
4023
4024 p_active_date_first_type := NULL;
4025 p_start_date_active_first := NULL;
4026 p_end_date_active_first := NULL;
4027
4028 ELSIF p_active_date_second_type = 'SHIP' THEN
4029 -- Make the first NULL
4030 p_active_date_first_type := NULL;
4031 p_start_date_active_first := NULL;
4032 p_end_date_active_first := NULL;
4033 END IF;
4034 END IF;
4035
4036 END CORRECT_ACTIVE_DATES;
4037
4038 -- mkarya for bug 1728764, Prevent update of Trade Management Data in QP
4039 -- mkarya for bug 2442212, Prevent update of modifier if PTE does not match with Profile value
4040 -- New procedure created
4041 PROCEDURE Check_Source_System_Code
4042 ( p_list_header_id IN qp_list_headers_b.list_header_id%type
4043 , p_list_line_id IN qp_list_lines.list_line_id%type
4044 , x_return_status OUT NOCOPY VARCHAR2
4045 )
4046 is
4047 l_source_system_code qp_list_headers_b.source_system_code%type;
4048 l_list_type_code qp_list_headers_b.list_type_code%type;
4049 l_profile_source_system_code qp_list_headers_b.source_system_code%type;
4050 l_profile_pte_code qp_list_headers_b.pte_code%type;
4051 l_pte_code qp_list_headers_b.pte_code%type;
4052 BEGIN
4053 x_return_status := FND_API.G_RET_STS_SUCCESS;
4054
4055 if p_list_header_id is NOT NULL then
4056 select source_system_code
4057 , list_type_code
4058 , pte_code
4059 into l_source_system_code
4060 , l_list_type_code
4061 , l_pte_code
4062 from qp_list_headers_b
4063 where list_header_id = p_list_header_id;
4064 else
4065 select lh.source_system_code
4066 , lh.list_type_code
4067 , lh.pte_code
4068 into l_source_system_code
4069 , l_list_type_code
4070 , l_pte_code
4071 from qp_list_headers_b lh,
4072 qp_list_lines ll
4073 where ll.list_line_id = p_list_line_id
4074 and lh.list_header_id = ll.list_header_id;
4075 end if;
4076
4077 fnd_profile.get('QP_SOURCE_SYSTEM_CODE', l_profile_source_system_code);
4078
4079 IF qp_util.attrmgr_installed = 'Y' then
4080 fnd_profile.get('QP_PRICING_TRANSACTION_ENTITY', l_profile_pte_code);
4081 if (((l_pte_code <> l_profile_pte_code) or
4082 (l_source_system_code <> l_profile_source_system_code)
4083 )
4084 AND (l_list_type_code NOT IN ('AGR', 'PML', 'PRL'))
4085 ) then
4086
4087 x_return_status := FND_API.G_RET_STS_ERROR;
4088
4089 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4090 THEN
4091
4092 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_CHANGE_MODIFIER_PTE');
4093 FND_MESSAGE.SET_TOKEN('SOURCE_SYSTEM', l_source_system_code);
4094 FND_MESSAGE.SET_TOKEN('PTE_CODE', l_pte_code);
4095 OE_MSG_PUB.Add;
4096
4097 END IF;
4098 end if;
4099 else -- attribute manager not installed
4100 if ((l_source_system_code <> l_profile_source_system_code)
4101 AND (l_list_type_code NOT IN ('AGR', 'PML', 'PRL'))) then
4102
4103 x_return_status := FND_API.G_RET_STS_ERROR;
4104
4105 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4106 THEN
4107
4108 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_CHANGE_MODIFIER');
4109 FND_MESSAGE.SET_TOKEN('SOURCE_SYSTEM', l_source_system_code);
4110 OE_MSG_PUB.Add;
4111
4112 END IF;
4113 end if;
4114
4115 end if; -- check if attribute manager installed
4116 EXCEPTION
4117 when no_data_found then
4118 -- if the header/line record is yet to be created
4119 NULL;
4120 END Check_Source_System_Code;
4121
4122 --------------------------------------------------------------------------------
4123 -- This procedure is used in the post query to get attribute name for the
4124 -- corresponding pricing/product/qualifier_attribute. qualifier_attribute
4125 -- stores the 'column' corresponding to the flexfield segment.
4126 -- (For e.g the column for the segment 'agreement_name' is
4127 -- 'qualifier_attribute7'. What is shown on the screen is the window prompt
4128 -- for the segment. For e.g The context 'CUSTOMER' has segments like
4129 -- agreement_name,GSA,agreement_type etc. The window prompt for
4130 -- the segment agreement_name is 'Agreement Name'(UI Value) and the database
4131 -- value for this segement(value stored in qualifier_attribute) is the column
4132 -- name, which is QUALIFIER_ATTRIBUTE7 ,in this case.
4133 --------------------------------------------------------------------------------
4134 PROCEDURE Get_Attribute_Code(p_FlexField_Name IN VARCHAR2,
4135 p_Context_Name IN VARCHAR2,
4136 p_attribute IN VARCHAR2,
4137 x_attribute_code OUT NOCOPY VARCHAR2,
4138 x_segment_name OUT NOCOPY VARCHAR2)
4139 IS
4140
4141 Flexfield FND_DFLEX.dflex_r;
4142 Flexinfo FND_DFLEX.dflex_dr;
4143 Contexts FND_DFLEX.contexts_dr;
4144 segments FND_DFLEX.segments_dr;
4145 i BINARY_INTEGER;
4146
4147 VALID_ATTRIBUTE BOOLEAN := FALSE;
4148
4149 l_pte_code VARCHAR2(30);
4150 l_context_type VARCHAR2(30);
4151 l_error_code NUMBER;
4152
4153 CURSOR attribute_cur(a_context_type VARCHAR2, a_context_code VARCHAR2,
4154 a_pte_code VARCHAR2, a_attribute VARCHAR2)
4155 IS
4156 SELECT nvl(a.user_segment_name, a.seeded_segment_name),
4157 b.segment_code
4158 FROM qp_segments_tl a, qp_segments_b b,
4159 qp_prc_contexts_b c, qp_pte_segments d
4160 WHERE c.prc_context_type = a_context_type
4161 AND c.prc_context_code = a_context_code
4162 AND c.prc_context_id = b.prc_context_id
4163 AND b.segment_mapping_column = a_attribute
4164 AND b.segment_id = a.segment_id
4165 AND a.language = userenv('LANG')
4166 AND b.segment_id = d.segment_id
4167 AND d.pte_code = a_pte_code;
4168
4169
4170 BEGIN
4171
4172 IF Attrmgr_Installed = 'Y' THEN
4173
4174 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
4175
4176 IF l_pte_code IS NULL THEN
4177 l_pte_code := 'ORDFUL';
4178 END IF;
4179
4180 QP_UTIL.Get_Context_Type(p_flexfield_name, p_context_name,
4181 l_context_type, l_error_code);
4182 IF l_error_code = 0 THEN
4183
4184 OPEN attribute_cur(l_context_type, p_context_name,
4185 l_pte_code, p_attribute);
4186
4187 --Deliberately interchanged the output parameters to be consistent with
4188 --existing code(Before Attributes Manager)
4189 FETCH attribute_cur INTO x_attribute_code, x_segment_name;
4190
4191 IF attribute_cur%NOTFOUND then
4192 x_segment_name := p_attribute;
4193 x_attribute_code := p_attribute;
4194 END IF;
4195 CLOSE attribute_cur;
4196
4197 END IF; --If l_error_code = 0
4198
4199 ELSE
4200 /* Added for 2332139 */
4201 BEGIN
4202 select form_left_prompt,end_user_column_name
4203 INTO x_attribute_code,x_segment_name
4204 from FND_DESCR_FLEX_COL_USAGE_VL
4205 where APPLICATION_ID = 661 and
4206 DESCRIPTIVE_FLEXFIELD_NAME = p_FlexField_Name and
4207 DESCRIPTIVE_FLEX_CONTEXT_CODE = p_Context_Name and
4208 application_column_name = p_attribute and
4209 enabled_flag='Y';
4210
4211 EXCEPTION
4212 WHEN OTHERS THEN
4213 x_attribute_code := p_attribute;
4214 x_segment_name := NULL;
4215 END;
4216
4217 /* Commented out for 2332139 */
4218
4219 /*
4220
4221 FND_DFLEX.get_flexfield('QP',p_FlexField_Name,Flexfield,Flexinfo);
4222
4223 --removing the check for the enabled segments as well as per the upgrade
4224 --requirement. While upgrading ,there may be some segments which were
4225 --enabled in the past but disabled now. In such cases ,we still need to
4226 --show the data in the post query.
4227
4228 FND_DFLEX.get_segments(FND_DFLEX.make_context(Flexfield,p_Context_Name),
4229 segments,FALSE);
4230
4231 FOR i IN 1..segments.nsegments LOOP
4232
4233 --removing the check for the enabled segments as well as per the upgrade
4234 --requirement. While upgrading ,there may be some segments which were
4235 --enabled in the past but disabled now. In such cases ,we still need to
4236 --show the data in the post query.
4237 IF segments.is_enabled(i) THEN
4238
4239 IF segments.application_column_name(i) = p_attribute THEN
4240 x_attribute_code := segments.row_prompt(i);
4241 x_segment_name := segments.segment_name(i);
4242 EXIT;
4243 END IF;
4244
4245 END IF;
4246
4247 END LOOP;
4248 */
4249 END IF; --Attrmgr_Installed = 'Y'
4250
4251 END Get_Attribute_Code;
4252
4253 FUNCTION Get_Segment_Level(p_list_header_id IN NUMBER
4254 ,p_Context IN VARCHAR2
4255 ,p_attribute IN VARCHAR2
4256 )
4257 RETURN VARCHAR2
4258 is
4259 l_segment_level VARCHAR2(30);
4260
4261 BEGIN
4262
4263 select c.segment_level
4264 into l_segment_level
4265 from qp_prc_contexts_b a,
4266 qp_segments_b b,
4267 qp_pte_segments c,
4268 qp_list_headers_b d
4269 where a.prc_context_id = b.prc_context_id
4270 and b.segment_id = c.segment_id
4271 and c.pte_code = d.pte_code
4272 and d.list_header_id = p_list_header_id
4273 and a.prc_context_code = p_context
4274 and b.SEGMENT_MAPPING_COLUMN = p_attribute;
4275
4276 return(l_segment_level);
4277
4278 EXCEPTION
4279 WHEN OTHERS THEN
4280 return(NULL);
4281
4282 END Get_Segment_Level;
4283 -- ===========================================================================
4284
4285 /****************************************************************************
4286
4287 PROCEDURE Web_Create_Context_Lov
4288 --------- ----------------------
4289 Procedure for non-Forms(html) based UI's. This Procedure is similar to
4290 Create_Context_Lov except that the Contexts(context code and context_name)
4291 to be displayed in an LOV are returned in a pl/sql table of records.
4292
4293 Input Parameters:
4294 -----------------
4295 p_field_context - 'FACTOR', 'PRODUCT', 'GSA', etc. Pass NULL if irrelevant.
4296 p_context_type - Can be 'PRODUCT', 'PRICING_ATTRIBUTE', 'QUALIFIER' or NULL.
4297 If Attributes Manager is installed, NULL will cause contexts
4298 of types 'PRODUCT', 'QUALIFIER' and 'PRICING_ATTRIBUTE' to
4299 be returned. If Attributes Manager is not installed, then
4300 NULL causes contexts of types 'PRODUCT' and
4301 'PRICING_ATTRIBUTE' to be returned.
4302 p_check_enabled - Default is 'Y'. If 'Y', only enabled contexts will be
4303 returned. If 'N', all contexts will be returned.
4304 p_limits - Default is 'N'. If 'Y', limits-enabled contexts will be
4305 returned. If 'N', all contexts will be returned.
4306 p_list_line_type_code - Examples, 'FREIGHT_CHARGE', etc. Although the default
4307 is NULL, the actual/correct value must be passed to get the
4308 correct list of contexts returned.
4309
4310 Output Parameters:
4311 ------------------
4312 x_return_status - Indicates Success, Expected_error or Unexpected Error -
4313 values can be either FND_API.G_RET_STS_SUCCESS or
4314 FND_API.G_RET_STS_ERROR.
4315 x_context_out_tbl - Table of records where each record has 2 columns,
4316 context_code and context_name
4317
4318 ****************************************************************************/
4319
4320 PROCEDURE Web_Create_Context_Lov(
4321 p_field_context IN VARCHAR2,
4322 p_context_type IN VARCHAR2,
4323 p_check_enabled IN VARCHAR2,
4324 p_limits IN VARCHAR2,
4325 p_list_line_type_code IN VARCHAR2,
4326 x_return_status OUT NOCOPY VARCHAR2,
4327 x_context_out_tbl OUT NOCOPY CREATE_CONTEXT_OUT_TBL)
4328
4329 IS
4330
4331 Flexfield FND_DFLEX.dflex_r;
4332 Flexinfo FND_DFLEX.dflex_dr;
4333 Contexts FND_DFLEX.contexts_dr;
4334
4335 J Binary_Integer := 1;
4336
4337 l_pte_code VARCHAR2(30);
4338
4339 CURSOR context_cur(a_pte_code VARCHAR2, a_qp_status VARCHAR2,
4340 a_limits VARCHAR2, a_list_line_type_code VARCHAR2)
4341 IS
4342 SELECT a.prc_context_code,
4343 nvl(b.user_prc_context_name, b.seeded_prc_context_name)
4344 prc_context_name,
4345 a.enabled_flag, a.prc_context_type
4346 FROM qp_prc_contexts_b a, qp_prc_contexts_tl b
4347 WHERE a.prc_context_id = b.prc_context_id
4348 AND b.language = userenv('LANG')
4349 AND EXISTS (SELECT 'x'
4350 FROM qp_segments_b c, qp_pte_segments d
4351 WHERE d.pte_code = a_pte_code
4352 AND c.segment_id = d.segment_id
4353 AND c.prc_context_id = a.prc_context_id
4354 AND d.lov_enabled = 'Y'
4355 AND (a_limits = 'Y' AND d.limits_enabled = 'Y'
4356 OR
4357 a_limits <> 'Y')
4358 AND (a_qp_status = 'S' AND
4359 (c.availability_in_basic = 'Y' OR
4360 c.availability_in_basic = 'F' AND
4361 a_list_line_type_code = 'FREIGHT_CHARGE')
4362 OR
4363 a_qp_status <> 'S')
4364 );
4365
4366 l_flexfield_name VARCHAR2(30);
4367 l_qp_status VARCHAR2(1);
4368 l_add_flag BOOLEAN;
4369
4370 BEGIN
4371
4372 x_return_status := FND_API.G_RET_STS_SUCCESS;
4373
4374 IF Attrmgr_Installed = 'Y' THEN
4375
4376 --Get the PTE code
4377 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
4378
4379 IF l_pte_code IS NULL THEN
4380 l_pte_code := 'ORDFUL';
4381 END IF;
4382
4383 --Get QP Install Status
4384 l_qp_status := QP_UTIL.Get_QP_Status;
4385
4386 FOR l_rec IN context_cur(l_pte_code, l_qp_status, p_limits,
4387 p_list_line_type_code)
4388 LOOP
4389
4390 l_add_flag := FALSE; --initialize for each iteration of loop
4391
4392 IF (p_check_enabled = 'Y' AND
4393 l_rec.enabled_flag = 'Y'
4394 )
4395 OR
4396 p_check_enabled = 'N'
4397 THEN
4398
4399 IF (p_context_type IS NOT NULL AND
4400 l_rec.prc_context_type = p_context_type
4401 )
4402 OR
4403 p_context_type IS NULL
4404 THEN
4405
4406 IF p_field_context = 'PRODUCT' THEN
4407
4408 IF l_rec.prc_context_code = 'ITEM' THEN
4409 l_add_flag := TRUE;
4410 END IF;
4411
4412 ELSIF p_field_context = 'PRICING_ATTR' THEN
4413
4414 IF l_rec.prc_context_code <> 'VOLUME' THEN
4415 l_add_flag := TRUE;
4416 END IF;
4417
4418 ELSIF p_field_context = 'BASIC' THEN
4419
4420 IF l_rec.prc_context_code IN ('MODLIST', 'CUSTOMER') THEN
4421 l_add_flag := TRUE;
4422 END IF;
4423
4424 ELSIF p_field_context = 'LINE_BASIC' THEN
4425
4426 IF l_rec.prc_context_code IN ('ORDER', 'CUSTOMER') THEN
4427 l_add_flag := TRUE;
4428 END IF;
4429
4430 ELSIF p_field_context = 'GSA' THEN
4431
4432 IF l_rec.prc_context_code IN ('MODLIST', 'CUSTOMER') THEN
4433 l_add_flag := TRUE;
4434 END IF;
4435
4436 ELSIF p_field_context = 'FACTOR' THEN
4437
4438 IF l_rec.prc_context_type IN ('PRODUCT', 'PRICING_ATTRIBUTE') THEN
4439 l_add_flag := TRUE;
4440 END IF;
4441
4442 ELSE
4443
4444 l_add_flag := TRUE;
4445
4446 END IF;
4447
4448 END IF;
4449
4450 END IF;
4451
4452 IF l_add_flag THEN
4453 x_context_out_tbl(j).context_code := l_rec.prc_context_code;
4454 x_context_out_tbl(j).context_name := l_rec.prc_context_name;
4455 j:= j+1;
4456 END IF; --l_add_flag = TRUE
4457
4458 END LOOP; --End FOR Loop
4459
4460 ELSE
4461
4462 IF p_context_type IN ('PRODUCT', 'PRICING_ATTRIBUTE') OR
4463 p_context_type IS NULL
4464 THEN
4465 l_flexfield_name := 'QP_ATTR_DEFNS_PRICING';
4466 ELSIF p_context_type = 'QUALIFIER' THEN
4467 l_flexfield_name := 'QP_ATTR_DEFNS_QUALIFIER';
4468 END IF;
4469
4470 -- Call Flexapi to get contexts
4471 FND_DFLEX.get_flexfield('QP',l_flexfield_name,Flexfield,Flexinfo);
4472 FND_DFLEX.get_contexts(Flexfield,Contexts);
4473
4474 FOR i IN 1..Contexts.ncontexts LOOP
4475
4476 -- If (Contexts.is_enabled(i)) Then
4477 IF(Contexts.is_enabled(i) AND (NOT (Contexts.is_global(i)))) THEN
4478
4479 IF (p_field_context = 'PRODUCT' ) THEN
4480
4481 IF (Contexts.context_code(i) = 'ITEM') THEN
4482 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4483 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4484 j:= j+1;
4485 END IF;
4486
4487 ELSIF (p_field_context = 'PRICING_ATTR' ) THEN
4488
4489 IF (Contexts.context_code(i) = 'ITEM') OR
4490 (Contexts.context_code(i) = 'VOLUME') THEN
4491 NULL;
4492 ELSE
4493 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4494 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4495 j:= j+1;
4496 END IF;
4497
4498 ELSIF (p_field_context = 'BASIC' ) THEN
4499
4500 IF (Contexts.context_code(i) IN ('CUSTOMER', 'MODLIST')) THEN
4501 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4502 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4503 j:= j+1;
4504 END IF;
4505
4506 ELSIF (p_field_context = 'LINE_BASIC' ) THEN
4507
4508 IF (Contexts.context_code(i) IN ('CUSTOMER', 'ORDER')) THEN
4509 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4510 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4511 j:= j+1;
4512 END IF;
4513
4514 ELSIF (p_field_context = 'GSA' ) THEN
4515
4516 IF (Contexts.context_code(i) IN ('CUSTOMER', 'MODLIST')) THEN
4517 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4518 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4519 j:= j+1;
4520 END IF;
4521
4522 ELSE
4523
4524 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4525 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4526 j:= j+1;
4527
4528 END IF;
4529
4530 END IF;
4531
4532 END LOOP;
4533
4534 END IF; --If Attrmgr_Installed = 'Y'
4535
4536 EXCEPTION
4537 WHEN OTHERS THEN
4538 x_return_status := FND_API.G_RET_STS_ERROR;
4539 FND_MESSAGE.SET_NAME('QP','QP_ERR_IN_VALUESET_DEF');
4540 OE_MSG_PUB.Add;
4541
4542 END Web_Create_Context_Lov;
4543
4544
4545 /****************************************************************************
4546 PROCEDURE Web_Create_Attribute_Lov
4547 --------- ------------------------
4548 Procedure for non-Forms(html) based UI's. This Procedure is similar to
4549 Create_Attribute_Lov except that the Attributes to be displayed in an LOV are
4550 returned in a pl/sql table of records.
4551
4552 Input Parameters:
4553 -----------------
4554 p_context_code - The context_code whose Attributes are to be returned. Ex,
4555 p_context_type - Must be one of the following - 'PRODUCT',
4556 'PRICING_ATTRIBUTE' or 'QUALIFIER' and must correspond
4557 correctly to the correct the p_context_code being passed.
4558 p_check_enabled - Default is 'Y'. If 'Y', only enabled contexts will be
4559 returned. If 'N', all contexts will be returned.
4560 p_limits - Default is 'N'. If 'Y', limits-enabled contexts will be
4561 returned. If 'N', all contexts will be returned.
4562 p_list_line_type_code - Examples, 'FREIGHT_CHARGE', etc. Although the default
4563 is NULL, the actual/correct value must be passed to get the
4564 correct list of contexts returned.
4565 p_segment_level - can have the following possible values:
4566
4567 Value - Meaning
4568 ----- -------
4569 1 ORDER (Segments having segment_level equal to this)
4570 2 LINE (Segments having segment_level equal to this)
4571 3 BOTH (Segments having segment_level equal to this)
4572 4 ORDER or BOTH (Segments having segment_level equal to this)
4573 5 LINE or BOTH (Segments having segment_level equal to this)
4574 6 ORDER or LINE or BOTH (Segments having segment_level equal to this)
4575
4576 p_field_context - 'BASIC', 'LINE_BASIC', 'GSA', 'S','I','N' etc. In most cases,
4577 some value must be passed. For example, for PriceLists,
4578 Formulas related forms, pass 'S' if basic pricing , 'I' for
4579 advanced_pricing installed. Pass NULL if irrelevant(rare).
4580
4581 Output Parameters:
4582 ------------------
4583 x_return_status - Indicates Success, Expected_error or Unexpected Error -
4584 values can be either FND_API.G_RET_STS_SUCCESS or
4585 FND_API.G_RET_STS_ERROR.
4586 x_attribute_out_tbl - Table of records where each record has 5 columns -
4587 segment_mapping_column, segment_name,
4588 segment_code, precedence and valueset_id
4589
4590 ****************************************************************************/
4591
4592 PROCEDURE Web_Create_Attribute_Lov(
4593 p_context_code IN VARCHAR2,
4594 p_context_type IN VARCHAR2,
4595 p_check_enabled IN VARCHAR2,
4596 p_limits IN VARCHAR2,
4597 p_list_line_type_code IN VARCHAR2,
4598 p_segment_level IN NUMBER,
4599 p_field_context IN VARCHAR2,
4600 x_return_status OUT NOCOPY VARCHAR2,
4601 x_attribute_out_tbl OUT NOCOPY CREATE_ATTRIBUTE_OUT_TBL)
4602 IS
4603
4604 Flexfield FND_DFLEX.dflex_r;
4605 Flexinfo FND_DFLEX.dflex_dr;
4606 Contexts FND_DFLEX.contexts_dr;
4607 segments FND_DFLEX.segments_dr;
4608 i BINARY_INTEGER;
4609
4610 j Binary_Integer := 1;
4611
4612 QP_NO_SEGMENT EXCEPTION;
4613
4614 l_pte_code VARCHAR2(30);
4615
4616 CURSOR attribute_cur(a_context_type VARCHAR2, a_context_code VARCHAR2,
4617 a_pte_code VARCHAR2)
4618 IS
4619 SELECT a.segment_mapping_column,
4620 nvl(b.user_segment_name, b.seeded_segment_name) segment_name,
4621 a.segment_code, nvl(a.user_precedence, a.seeded_precedence) precedence,
4622 d.prc_context_code, d.prc_context_type, c.lov_enabled,
4623 a.availability_in_basic, c.limits_enabled, c.segment_level,
4624 nvl(a.user_valueset_id, a.seeded_valueset_id) valueset_id
4625 FROM qp_segments_b a, qp_segments_tl b,
4626 qp_pte_segments c, qp_prc_contexts_b d
4627 WHERE d.prc_context_type = a_context_type
4628 AND d.prc_context_code = a_context_code
4629 AND a.prc_context_id = d.prc_context_id
4630 AND a.segment_id = b.segment_id
4631 AND b.language = userenv('LANG')
4632 AND c.pte_code = a_pte_code
4633 AND c.segment_id = b.segment_id;
4634
4635 l_add_flag BOOLEAN;
4636 l_qp_status VARCHAR2(1);
4637 l_flexfield_name VARCHAR2(30);
4638
4639 NO_SEGMENT_FOUND BOOLEAN := TRUE;
4640
4641 BEGIN
4642
4643 x_return_status := FND_API.G_RET_STS_SUCCESS;
4644
4645 IF Attrmgr_Installed = 'Y' THEN
4646
4647 --Get PTE code from profile
4648 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY',l_pte_code);
4649
4650 IF l_pte_code IS NULL THEN
4651 l_pte_code := 'ORDFUL';
4652 END IF;
4653
4654 --Get QP Install Status
4655 l_qp_status := QP_UTIL.Get_QP_Status;
4656
4657 FOR l_rec IN attribute_cur(p_context_type,
4658 p_context_code, l_pte_code)
4659 LOOP
4660
4661 IF attribute_cur%rowcount = 1 THEN
4662 NO_SEGMENT_FOUND := FALSE;
4663 END IF;
4664
4665 l_add_flag := FALSE; --initialize for each iteration of loop
4666
4667 IF (p_check_enabled = 'Y' AND
4668 l_rec.lov_enabled = 'Y'
4669 )
4670 OR
4671 p_check_enabled = 'N'
4672 THEN
4673
4674 IF (l_qp_status = 'S' AND
4675 (l_rec.availability_in_basic = 'Y' OR
4676 l_rec.availability_in_basic = 'F' AND
4677 p_list_line_type_code = 'FREIGHT_CHARGE'
4678 )
4679 )
4680 OR
4681 l_qp_status <> 'S'
4682 THEN
4683
4684 IF (p_limits = 'Y' AND
4685 l_rec.limits_enabled = 'Y'
4686 )
4687 OR
4688 p_limits <> 'Y'
4689 THEN
4690
4691 IF (p_segment_level = 1 AND
4692 l_rec.segment_level = 'ORDER'
4693 )
4694 OR
4695 (p_segment_level = 2 AND
4696 l_rec.segment_level = 'LINE'
4697 )
4698 OR
4699 (p_segment_level = 3 AND
4700 l_rec.segment_level = 'BOTH'
4701 )
4702 OR
4703 (p_segment_level = 4 AND
4704 l_rec.segment_level IN ('ORDER', 'BOTH')
4705 )
4706 OR
4707 (p_segment_level = 5 AND
4708 l_rec.segment_level IN ('LINE', 'BOTH')
4709 )
4710 OR
4711 (p_segment_level = 6 AND
4712 l_rec.segment_level IN ('ORDER', 'LINE', 'BOTH')
4713 )
4714 THEN
4715
4716 IF (p_list_line_type_code = 'OID' AND
4717 p_field_context = 'PRICING_ATTR_GET'
4718 )
4719 OR
4720 p_field_context = 'GSA'
4721 THEN
4722
4723 IF l_rec.prc_context_code = 'ITEM' AND
4724 l_rec.segment_mapping_column = 'PRICING_ATTRIBUTE1'
4725 THEN
4726 l_add_flag := TRUE;
4727 END IF;
4728
4729 ELSIF p_field_context = 'BASIC' THEN
4730
4731 IF (l_rec.prc_context_code = 'CUSTOMER' AND
4732 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE1',
4733 'QUALIFIER_ATTRIBUTE2',
4734 'QUALIFIER_ATTRIBUTE5')
4735 )
4736 OR
4737 (l_rec.prc_context_code = 'MODLIST' AND
4738 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE4'
4739 )
4740 THEN
4741 l_add_flag := TRUE;
4742 END IF;
4743
4744 ELSIF p_field_context = 'GSA_QUALIFIER' THEN
4745
4746 IF (l_rec.prc_context_code = 'CUSTOMER' AND
4747 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE2',
4748 'QUALIFIER_ATTRIBUTE5')
4749 )
4750 OR
4751 (l_rec.prc_context_code = 'MODLIST' AND
4752 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE4'
4753 )
4754 THEN
4755 l_add_flag := TRUE;
4756 END IF;
4757
4758 ELSIF p_field_context = 'LINE_BASIC' THEN
4759
4760 IF (l_rec.prc_context_code = 'CUSTOMER' AND
4761 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE7',
4762 'QUALIFIER_ATTRIBUTE8')
4763 )
4764 OR
4765 (l_rec.prc_context_code = 'ORDER' AND
4766 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE9',
4767 'QUALIFIER_ATTRIBUTE12')
4768 )
4769 OR
4770 (l_rec.prc_context_code = 'VOLUME' AND
4771 l_rec.segment_mapping_column IN ('PRICING_ATTRIBUTE10',
4772 'PRICING_ATTRIBUTE12')
4773 )
4774 THEN
4775 l_add_flag := TRUE;
4776 END IF;
4777
4778 ELSIF p_field_context IN ('S','I','N') THEN
4779 -- These are possible values, that the Price Lists
4780 -- module can assign to the field_context parameter.
4781 IF NOT (l_rec.prc_context_code = 'MODLIST' AND
4782 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE4'
4783 )
4784 AND
4785 NOT (l_rec.prc_context_code = 'VOLUME' AND
4786 l_rec.segment_mapping_column = 'PRICING_ATTRIBUTE12'
4787 )
4788 AND
4789 NOT (l_rec.prc_context_code = 'VOLUME' AND
4790 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE10'
4791 )
4792 THEN
4793 l_add_flag := TRUE;
4794 END IF;
4795
4796 ELSE
4797
4798 l_add_flag := TRUE;
4799
4800 END IF;
4801
4802 END IF; --If p_segment_level...
4803
4804 END IF; --If l_form_function...
4805
4806 END IF; --If l_qp_status...
4807
4808 END IF; --If p_check_enabled...
4809
4810 IF l_add_flag THEN
4811 x_attribute_out_tbl(j).segment_mapping_column :=
4812 l_rec.segment_mapping_column;
4813 x_attribute_out_tbl(j).segment_name := l_rec.segment_name;
4814 x_attribute_out_tbl(j).segment_code := l_rec.segment_code;
4815 x_attribute_out_tbl(j).precedence := l_rec.precedence;
4816 x_attribute_out_tbl(j).valueset_id := l_rec.valueset_id;
4817 j:= j+1;
4818 END IF; --l_add_flag = TRUE
4819
4820 END LOOP; --End FOR Loop
4821
4822 IF NO_SEGMENT_FOUND THEN
4823 RAISE QP_NO_SEGMENT;
4824 END IF;
4825
4826 ELSE
4827
4828 IF p_context_type IN ('PRODUCT', 'PRICING_ATTRIBUTE') THEN
4829 l_flexfield_name := 'QP_ATTR_DEFNS_PRICING';
4830 ELSIF p_context_type = 'QUALIFIER' THEN
4831 l_flexfield_name := 'QP_ATTR_DEFNS_QUALIFIER';
4832 END IF;
4833
4834 FND_DFLEX.get_flexfield('QP',l_flexfield_name,Flexfield,Flexinfo);
4835 FND_DFLEX.get_segments(FND_DFLEX.make_context(Flexfield,p_Context_Code),
4836 segments,TRUE);
4837
4838 IF segments.nsegments <> 0 THEN
4839
4840 FOR i IN 1..segments.nsegments LOOP
4841
4842 IF segments.is_enabled(i) THEN
4843 --fnd_message.debug(p_Context_Name);
4844
4845 NO_SEGMENT_FOUND := FALSE;
4846
4847 IF (p_list_line_type_code = 'OID' and
4848 p_field_context = 'PRICING_ATTR_GET')
4849 THEN
4850
4851 IF p_context_code = 'ITEM' AND /* Item Number */
4852 segments.application_column_name(i) = 'PRICING_ATTRIBUTE1'
4853 THEN
4854 x_attribute_out_tbl(j).segment_mapping_column :=
4855 segments.application_column_name(i);
4856 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4857 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4858 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4859 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4860 j:= j+1;
4861 END IF;
4862
4863 ELSIF p_field_context = 'GSA' THEN
4864
4865 IF p_context_code = 'ITEM' AND
4866 segments.application_column_name(i) = 'PRICING_ATTRIBUTE1'
4867 THEN
4868 x_attribute_out_tbl(j).segment_mapping_column :=
4869 segments.application_column_name(i);
4870 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4871 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4872 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4873 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4874 j:= j+1;
4875 END IF;
4876
4877 ELSIF p_field_context = 'BASIC' THEN
4878
4879 IF (p_context_code = 'CUSTOMER' AND
4880 segments.application_column_name(i) IN ('QUALIFIER_ATTRIBUTE1',
4881 'QUALIFIER_ATTRIBUTE2',
4882 'QUALIFIER_ATTRIBUTE5'))
4883 OR
4884 (p_context_code = 'MODLIST' AND
4885 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE4')
4886 THEN
4887 x_attribute_out_tbl(j).segment_mapping_column :=
4888 segments.application_column_name(i);
4889 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4890 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4891 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4892 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4893 j:= j+1;
4894 END IF;
4895
4896 ELSIF p_field_context = 'GSA_QUALIFIER' THEN
4897
4898 IF (p_context_code = 'CUSTOMER' AND
4899 segments.application_column_name(i) IN ('QUALIFIER_ATTRIBUTE2',
4900 'QUALIFIER_ATTRIBUTE5'))
4901 OR
4902 (p_context_code = 'MODLIST' AND
4903 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE4')
4904 THEN
4905 x_attribute_out_tbl(j).segment_mapping_column :=
4906 segments.application_column_name(i);
4907 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4908 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4909 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4910 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4911 j:= j+1;
4912 END IF;
4913
4914 ELSIF p_field_context = 'LINE_BASIC' THEN
4915
4916 IF (p_context_code = 'CUSTOMER' AND
4917 segments.application_column_name(i) IN
4918 ('QUALIFIER_ATTRIBUTE7', 'QUALIFIER_ATTRIBUTE8'))
4919 OR
4920 (p_context_code = 'ORDER' and
4921 segments.application_column_name(i) IN ('QUALIFIER_ATTRIBUTE9',
4922 'QUALIFIER_ATTRIBUTE12'))
4923 THEN
4924 x_attribute_out_tbl(j).segment_mapping_column :=
4925 segments.application_column_name(i);
4926 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4927 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4928 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4929 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4930 j:= j+1;
4931 ELSIF (p_context_code = 'VOLUME' AND
4932 segments.application_column_name(i) IN
4933 ('PRICING_ATTRIBUTE10', 'PRICING_ATTRIBUTE12')) THEN
4934 x_attribute_out_tbl(j).segment_mapping_column :=
4935 segments.application_column_name(i);
4936 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4937 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4938 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4939 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4940 j:= j+1;
4941 END IF;
4942
4943 ELSIF p_field_context IN ('S', 'I', 'N') THEN
4944 -- These are possible values, that the
4945 -- Price Lists module can assign to the basic_advanced paramter.
4946 IF NOT (p_context_code = 'MODLIST' AND
4947 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE4')
4948 AND NOT (p_context_code = 'VOLUME' AND
4949 segments.application_column_name(i) = 'PRICING_ATTRIBUTE12')
4950 AND NOT (p_context_code = 'VOLUME' AND
4951 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE10')
4952 THEN
4953 -- Don't display qual attr 'Price List' in Price Lists form,
4954 -- don't display qual attr 'Order Amount'(also under 'Volume')
4955 -- and don't display Pricing Attr 'Item Amount' under 'Volume'
4956 -- Context(in the Price Breaks block of Price List form).
4957 x_attribute_out_tbl(j).segment_mapping_column :=
4958 segments.application_column_name(i);
4959 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4960 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4961 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4962 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4963 j:= j+1;
4964 END IF;
4965
4966 ELSE --Covers all the 'else' conditions for Modifiers module
4967 --fnd_message.debug(segments.row_prompt(i));
4968
4969 x_attribute_out_tbl(j).segment_mapping_column :=
4970 segments.application_column_name(i);
4971 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4972 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4973 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4974 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4975 j:= j+1;
4976
4977 END IF;
4978
4979 END IF;
4980
4981 END LOOP;
4982
4983 IF NO_SEGMENT_FOUND THEN
4984 RAISE QP_NO_SEGMENT;
4985 END IF;
4986
4987 ELSE --- segments.nsegments = 0 ie no segments defined for the context.
4988
4989 RAISE QP_NO_SEGMENT;
4990
4991 END IF; ---- if segments.nsegments <> 0
4992
4993 END IF; --If Attrmgr_Installed = 'Y'
4994
4995 EXCEPTION
4996 WHEN QP_NO_SEGMENT THEN
4997 x_return_status := FND_API.G_RET_STS_ERROR;
4998 FND_MESSAGE.SET_NAME('QP','QP_NO_SEGMENTS_AVAILABLE');
4999 OE_MSG_PUB.Add;
5000
5001 END Web_Create_Attribute_Lov;
5002
5003
5004 FUNCTION Get_Item_Validation_Org RETURN NUMBER IS
5005 l_application_id NUMBER;
5006 l_inv_org_id NUMBER;
5007 BEGIN
5008 oe_debug_pub.add('Start G_ORGANIZATION_ID = '||G_ORGANIZATION_ID);
5009 /*
5010 commenting this code R12 per ER 4756750 to move the profile-based
5011 approach for all calling app and come up with a pricing parameter
5012 for the item validation Org per the ER
5013 l_application_id := fnd_global.resp_appl_id;
5014
5015 oe_debug_pub.add('l_application_id = '||l_application_id);
5016 if l_application_id in (201, 178) then -- oracle purchasing/iProcurement
5017 SELECT inventory_organization_id
5018 INTO l_inv_org_id
5019 --fix for bug 4776045 for MOAC
5020 --FROM financials_system_parameters;
5021 FROM FINANCIALS_SYSTEM_PARAMS_ALL
5022 where org_id = get_org_id;
5023
5024 oe_debug_pub.add('inv_org_id from financials_system_parameters = '||l_inv_org_id);
5025 G_ORGANIZATION_ID := l_inv_org_id;
5026 end if;--application_id
5027 if G_ORGANIZATION_ID is null then --above query did not return a value
5028 --because MO Default OU was not set or FSP is not set revert to QP profile
5029 */
5030 -- if G_ORGANIZATION_ID is null then
5031 l_inv_org_id := FND_PROFILE.Value('QP_ORGANIZATION_ID');
5032
5033 oe_debug_pub.add('inv_org_id from profile QP_ORGANIZATION_ID = '||l_inv_org_id);
5034 IF G_ORGANIZATION_ID IS NULL THEN
5035 G_ORGANIZATION_ID := l_inv_org_id;
5036 -- end if;
5037 end if; --if G_ORGANIZATION_ID is null
5038
5039 oe_debug_pub.add('End G_ORGANIZATION_ID = '||G_ORGANIZATION_ID);
5040
5041 return l_inv_org_id;
5042 EXCEPTION
5043 when no_data_found then
5044 oe_debug_pub.add('no data found exception in qp_util.Get_Item_Validation_Org');
5045 --fix for bug 4776045
5046 -- G_ORGANIZATION_ID := FND_PROFILE.Value('QP_ORGANIZATION_ID');
5047 -- return G_ORGANIZATION_ID;
5048 return null;
5049
5050 when others then
5051 oe_debug_pub.add('others exception in qp_util.Get_Item_Validation_Org, error is ' || SQLERRM);
5052 --fix for bug 4776045
5053 -- G_ORGANIZATION_ID := FND_PROFILE.Value('QP_ORGANIZATION_ID');
5054 -- return G_ORGANIZATION_ID;
5055 return null;
5056
5057 END Get_Item_Validation_Org;
5058
5059 --[prarasto] added for MOAC. Used by the engine to get the org id.
5060 FUNCTION get_org_id --[prarasto] changed function signature
5061 RETURN NUMBER IS
5062 l_context_org_id NUMBER;
5063 BEGIN
5064 l_context_org_id := MO_GLOBAL.get_current_org_id;
5065 -- IF (FND_GLOBAL.USER_ID IS NOT NULL) AND (FND_GLOBAL.RESP_ID IS NOT NULL) AND (FND_GLOBAL.RESP_APPL_ID IS NOT NULL) THEN
5066 IF l_context_org_id IS NOT NULL THEN
5067 --check if org context is set
5068 RETURN l_context_org_id; --MO_GLOBAL.get_current_org_id;
5069 ELSE
5070 RETURN MO_UTILS.get_default_org_id; --[prarasto] modified to get the default org id from MO_UTILS
5071 END IF;
5072 Exception
5073 When OTHERS Then
5074 return null;
5075 END get_org_id;
5076
5077
5078 --[prarasto] added for MOAC. Used by the engine for validating the org id
5079 FUNCTION validate_org_id (p_org_id NUMBER)
5080 RETURN VARCHAR2 IS
5081 l_dummy VARCHAR2(1);
5082 BEGIN
5083 /*
5084 SELECT 'X'
5085 INTO l_dummy
5086 FROM hr_operating_units hr
5087 WHERE hr.organization_id = p_org_id
5088 AND MO_GLOBAL.check_access(hr.organization_id) = 'Y';
5089 */
5090 l_dummy := MO_GLOBAL.check_access(p_org_id);
5091
5092 /*
5093 IF l_dummy = 'X' THEN
5094 RETURN 'Y';
5095 ELSE
5096 RETURN 'N';
5097 END IF;
5098 */
5099 RETURN l_dummy;
5100 EXCEPTION
5101 When OTHERS Then
5102 return 'N';
5103 END validate_org_id;
5104
5105 --added for moac used by PL/ML VOs to query OU
5106 FUNCTION Get_OU_Name(p_org_id IN NUMBER) RETURN VARCHAR2 IS
5107 l_operating_unit VARCHAR2(240);
5108 BEGIN
5109 IF p_org_id IS NOT NULL THEN
5110 select name
5111 into l_operating_unit
5112 from hr_operating_units
5113 where organization_id = p_org_id;
5114 END IF;
5115 return l_operating_unit;
5116 EXCEPTION
5117 When OTHERS Then
5118 return null;
5119 END Get_OU_Name;
5120
5121 --[prarasto] added for MOAC. Used by the engine for validating the org id
5122
5123 PROCEDURE get_pte_and_ss (p_list_header_id IN NUMBER,
5124 x_pte_code OUT NOCOPY VARCHAR2,
5125 x_source_system_code OUT NOCOPY VARCHAR2)
5126 IS
5127 BEGIN
5128 SELECT pte_code, source_system_code
5129 INTO x_pte_code, x_source_system_code
5130 FROM qp_list_headers_b
5131 WHERE list_header_id = p_list_header_id;
5132 EXCEPTION
5133 When OTHERS Then
5134 null;
5135 END get_pte_and_ss;
5136
5137
5138 -- =======================================================================
5139 -- FUNCTION is_seed_user
5140 -- Function type Public
5141 -- DESCRIPTION
5142 -- Returns a boolean corresponding to whether the current user is
5143 -- the DATAMERGE user
5144 -- =======================================================================
5145 FUNCTION is_seed_user RETURN BOOLEAN
5146 IS
5147 l_db_name VARCHAR2(9);
5148 l_dm_user_id NUMBER;
5149 BEGIN
5150 BEGIN
5151 -- Get database name
5152 SELECT name
5153 INTO l_db_name
5154 FROM v$database;
5155
5156 -- Get DATAMERGE User ID
5157 SELECT user_id
5158 INTO l_dm_user_id
5159 FROM fnd_user
5160 WHERE user_name = 'DATAMERGE';
5161
5162 EXCEPTION
5163 WHEN NO_DATA_FOUND THEN
5164 RETURN FALSE;
5165 END;
5166
5167 -- If the database is a seed DB and the user is DATAMERGE, return TRUE
5168 IF l_db_name LIKE 'SEED%' AND l_dm_user_id = fnd_global.user_id THEN
5169 RETURN TRUE;
5170 ELSE
5171 RETURN FALSE;
5172 END IF;
5173
5174 END is_seed_user;
5175
5176
5177 FUNCTION Validate_Item(p_product_context IN VARCHAR2,
5178 p_product_attribute IN VARCHAR2,
5179 p_product_attr_val IN VARCHAR2) RETURN VARCHAR2 IS
5180 l_appl_id NUMBER := FND_GLOBAL.RESP_APPL_ID;
5181 l_dummy VARCHAR2(30);
5182 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5183 BEGIN
5184
5185 if (p_product_context = 'ITEM')
5186 and (p_product_attribute = 'PRICING_ATTRIBUTE1') THEN
5187 begin
5188 SELECT 'VALID' INTO l_dummy
5189 FROM mtl_system_items_b
5190 where inventory_item_id = p_product_attr_val
5191 AND ((l_appl_id not in (178,201) and customer_order_flag = 'Y')
5192 or (l_appl_id in (178, 201) and NVL(PURCHASING_ENABLED_FLAG, 'N') ='Y'))
5193 and organization_id = Get_Item_Validation_Org;
5194 exception
5195 WHEN NO_DATA_FOUND THEN
5196 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
5197 FND_MESSAGE.SET_NAME('QP','QP_ITEM_NOT_VALID');
5198 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_product_attr_val);
5199 OE_MSG_PUB.Add;
5200 RETURN FND_API.G_RET_STS_ERROR;
5201 END IF;
5202 RAISE FND_API.G_EXC_ERROR;
5203 WHEN OTHERS THEN
5204 null;
5205 RETURN FND_API.G_RET_STS_ERROR;
5206 end;
5207 end if;--(p_product_context =
5208
5209 RETURN l_return_status;
5210
5211 EXCEPTION
5212 WHEN OTHERS THEN
5213 null;
5214 END Validate_Item;
5215
5216 END QP_UTIL;
[Home] [Help]