1 PACKAGE BODY QP_UTIL AS
2 /* $Header: QPXUTILB.pls 120.14.12000000.2 2007/06/11 05:20:23 jisingh 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 fnd_flex_validation_tables.additional_where_clause%type;
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 /* Added for 2492020 */
1574
1575 IF instr(UPPER(p_table_r.where_clause), 'ORDER BY') > 0 THEN
1576 l_order_by := substr(p_table_r.where_clause, instr(UPPER(p_table_r.where_clause), 'ORDER BY'));
1577 v_where_clause := replace(p_table_r.where_clause, l_order_by ,'');
1578 ELSE
1579 v_where_clause := p_table_r.where_clause;
1580 END IF;
1581
1582
1583 -- if instr(upper(p_table_r.where_clause),'WHERE ') > 0 then --Commented out for 2492020
1584 IF instr(upper(v_where_clause),'WHERE') > 0 then --3839853 removed space in 'WHERE '
1585 --to include the id column name in the query
1586
1587 v_where_clause:= rtrim(ltrim(v_where_clause));
1588 l_pos1 := instr(upper(v_where_clause),'WHERE');
1589 l_where_length := LENGTHB('WHERE');
1590 v_where_clause:= substr(v_where_clause,l_pos1+l_where_length);
1591
1592 IF (p_table_r.id_column_name IS NOT NULL) THEN
1593 --included extra quotes for comparing varchar and num values in select
1594 /* Commented out for 2492020
1595 v_where_clause := replace(UPPER(p_table_r.where_clause)
1596 ,'WHERE '
1597 ,'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND ');
1598 */
1599 -- v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND '||v_where_clause; -- 2492020
1600
1601 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val AND '||v_where_clause;--3210264
1602 ELSE
1603 /* Commented out for 2492020
1604 v_where_clause := replace(UPPER(p_table_r.where_clause)
1605 ,'WHERE '
1606 ,'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND ');
1607 */
1608 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND '||v_where_clause;--2492020
1609
1610 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val AND '||v_where_clause;--3210264
1611
1612 END IF;
1613 ELSE
1614
1615
1616
1617 IF v_where_clause IS NOT NULL THEN -- FP 115.88.1159.7
1618 IF (p_table_r.id_column_name IS NOT NULL) THEN
1619 /* Commented out for 2492020
1620 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
1621 */
1622 --Added for 2492020
1623 --v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||v_where_clause;
1624
1625 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val '||v_where_clause;--3210264
1626 ELSE
1627 /* Commented out for 2492020
1628 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
1629 */
1630 --Added for 2492020
1631 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||v_where_clause;
1632
1633 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val '||v_where_clause;--3210264
1634 END IF;
1635 -- begin FP 115.88.1159.7
1636 /* added ELSE block for 3839853 */
1637 ELSE
1638 IF (p_table_r.id_column_name IS NOT NULL) THEN
1639 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val '||v_where_clause;
1640 ELSE
1641 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val '||v_where_clause;
1642 END IF;
1643 -- end FP 115.88.1159.7
1644 END IF;
1645
1646 end if;
1647
1648 IF l_order_by IS NOT NULL THEN
1649 v_where_clause := v_where_clause||' '||l_order_by;
1650 END IF;
1651
1652 /* Commented out for 2621644
1653
1654 ELSE
1655 v_where_clause := p_table_r.where_clause;
1656 END IF;
1657 */
1658 v_cols := p_table_r.value_column_name;
1659 -------------------
1660 --changes made by spgopal for performance problem
1661 --added out parameters to pass back id and value for given valueset id
1662 -------------------
1663
1664 IF (p_table_r.id_column_name IS NOT NULL) THEN
1665
1666 --
1667 -- to_char() conversion function is defined only for
1668 -- DATE and NUMBER datatypes.
1669 --
1670 IF (p_table_r.id_column_type IN ('D', 'N')) THEN
1671 v_cols := v_cols || ' , To_char(' || p_table_r.id_column_name || ')';
1672 ELSE
1673 v_cols := v_cols || ' , ' || p_table_r.id_column_name;
1674 END IF;
1675 ELSE
1676 v_cols := v_cols || ', NULL ';
1677 END IF;
1678
1679
1680
1681 v_selectstmt := 'SELECT '||v_cols||' FROM '||p_table_r.table_name||' '||v_where_clause;
1682
1683 oe_debug_pub.add('select stmt1'||v_selectstmt);
1684 ------------------
1685
1686 /*
1687 IF p_table_r.id_column_name is not null then
1688
1689 v_selectstmt := 'SELECT '||p_table_r.id_column_name||' FROM '||p_table_r.table_name||' '||v_where_clause;
1690
1691 ELSE
1692
1693 v_selectstmt := 'SELECT '||p_table_r.value_column_name||' FROM '||p_table_r.table_name||' '||p_table_r.where_clause;
1694
1695 END IF;
1696
1697
1698 /* Added for 3210264 */
1699
1700 open v_cursor for v_selectstmt using p_value;
1701 fetch v_cursor into valueset_cur;
1702 IF v_Cursor%NOTFOUND THEN
1703 CLOSE v_cursor;
1704 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1705 RETURN FALSE;
1706 END IF;
1707 x_id := valueset_cur.valueset_id;
1708 x_value := valueset_cur.valueset_value;
1709 CLOSE v_cursor;
1710 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1711 RETURN TRUE;
1712
1713
1714 /*
1715 -- parse the query
1716
1717 DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
1718 oe_debug_pub.add('after parse1');
1719 -- Bind the input variables
1720 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,1,v_value,150);
1721 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,2,v_id,150);
1722 v_retval := DBMS_SQL.EXECUTE(v_cursor_id);
1723 LOOP
1724 -- Fetch rows in to buffer and check the exit condition from the loop
1725 IF( DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0) THEN
1726 EXIT;
1727 END IF;
1728 -- Retrieve the rows from buffer into PLSQL variables
1729 DBMS_SQL.COLUMN_VALUE(v_cursor_id,1,v_value);
1730 DBMS_SQL.COLUMN_VALUE(v_cursor_id,2,v_id);
1731
1732 IF v_id IS NULL AND (p_value = v_value) THEN
1733 oe_debug_pub.add('id null, passing value'||p_value||','||v_value);
1734 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1735 x_id := v_id;
1736 x_value := v_value;
1737 RETURN TRUE;
1738 ELSIF (p_value = v_id) THEN
1739 oe_debug_pub.add('id exists, passing id'||p_value||','||v_id);
1740 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1741 x_id := v_id;
1742 x_value := v_value;
1743 RETURN TRUE;
1744 ELSE
1745 Null;
1746 oe_debug_pub.add('value does notmatch, continue search'||p_value||','||v_id);
1747 END IF;
1748 END LOOP;
1749 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1750 RETURN FALSE;
1751 */
1752 EXCEPTION
1753 WHEN OTHERS THEN
1754 oe_debug_pub.add('value_exists_in_table exception');
1755 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
1756 RETURN FALSE;
1757 END value_exists_in_table;
1758
1759 -- =============================================================================
1760 -- PROCEDURE validate_qp_flexfield
1761 -- procedure type PUBLIC
1762 -- out parameters : context_flag,attribute_flag,context_flag,attribute_flag,value_flag,datatype,precedence,error_code
1763 -- Meaning for error codes
1764 -- errorcode = 0 Successful
1765 -- = 1 flexfield_name is not passed.
1766 -- = 2 context value is not passed
1767 -- = 3 attribute value is not passed.
1768 -- = 4 value is not passed
1769 -- = 5 application short name is not passed
1770
1771 -- = 6 Invalid application short name.
1772 -- = 7 Invalid context passed
1773 -- = 8 Invalid segment passed
1774 -- = 9 Value passed is not a valid value of value set or
1775 -- Value set query is wrongly defined..
1776 -- = 10 Flexfield name is invalid.
1777 -- DESCRIPTION
1778 -- Checks the validity of flexfield,context code,segments and values passed to the procedure.
1779 --==============================================================================
1780
1781 PROCEDURE validate_qp_flexfield(flexfield_name IN VARCHAR2,
1782 context IN VARCHAR2,
1783 attribute IN VARCHAR2,
1784 value IN VARCHAR2,
1785 application_short_name IN VARCHAR2,
1786 context_flag OUT NOCOPY VARCHAR2,
1787 attribute_flag OUT NOCOPY VARCHAR2,
1788 value_flag OUT NOCOPY VARCHAR2,
1789 datatype OUT NOCOPY VARCHAR2,
1790 precedence OUT NOCOPY VARCHAR2,
1791 error_code OUT NOCOPY NUMBER ,
1792 check_enabled IN BOOLEAN := TRUE)
1793 IS
1794
1795 CURSOR Cur_get_application_id(app_short_name VARCHAR2)
1796 IS
1797 SELECT application_id
1798 FROM fnd_application
1799 WHERE application_short_name = app_short_name;
1800
1801 v_context_dr fnd_dflex.contexts_dr;
1802 v_dflex_r fnd_dflex.dflex_r;
1803 v_context_r fnd_dflex.context_r;
1804 v_segments_dr fnd_dflex.segments_dr;
1805 v_value_set_id NUMBER;
1806 v_precedence NUMBER;
1807 v_valueset_r fnd_vset.valueset_r;
1808 v_format_dr fnd_vset.valueset_dr;
1809 v_valueset_dr fnd_vset.valueset_dr;
1810 v_dflex_dr fnd_dflex.dflex_dr;
1811 v_flexfield_val_ind NUMBER DEFAULT 0;
1812 l_value VARCHAR2(150);
1813 l_id VARCHAR2(150);
1814
1815 l_attrmgr_installed VARCHAR2(30);
1816 l_context_type VARCHAR2(30);
1817 l_error_code NUMBER;
1818 l_application_id NUMBER;
1819
1820 BEGIN
1821 --Initialize flags and error_code.
1822 context_flag := 'N';
1823 attribute_flag := 'N';
1824 value_flag := 'N';
1825 error_code := 0;
1826
1827 IF (flexfield_name IS NULL) THEN
1828 error_code := 1; -- flexfield_name is not passed.
1829 RETURN;
1830 END IF;
1831
1832 IF (context IS NULL) THEN
1833 error_code := 2;
1834 RETURN; -- context is not passed
1835 END IF;
1836
1837 IF (attribute IS NULL) THEN
1838 error_code := 3;
1839 RETURN; -- attribute is not passed.
1840 END IF;
1841
1842 IF (value IS NULL) THEN
1843 error_code := 4; -- value is not passed
1844 RETURN;
1845 END IF;
1846
1847 IF (application_short_name IS NULL) THEN
1848 error_code := 5; -- application short name is not passed
1849 RETURN;
1850 END IF;
1851
1852 --Get Attrmgr_Installed status
1853 l_attrmgr_installed := Attrmgr_Installed;
1854
1855 -- Get the application_id
1856
1857 OPEN Cur_get_application_id(application_short_name);
1858
1859 IF l_attrmgr_installed = 'Y' THEN
1860 FETCH Cur_get_application_id INTO l_application_id;
1861 ELSE
1862 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
1863 END IF;
1864
1865 IF (Cur_get_application_id%NOTFOUND) THEN
1866 CLOSE Cur_get_application_id;
1867 error_code := 6; -- Invalid application short name.
1868 RETURN;
1869 END IF;
1870
1871 CLOSE Cur_get_application_id;
1872
1873 -- check if flexfield name passed is a valid one or not.
1874
1875 IF l_attrmgr_installed = 'Y' THEN
1876
1877 IF flexfield_name NOT IN ('QP_ATTR_DEFNS_PRICING',
1878 'QP_ATTR_DEFNS_QUALIFIER')
1879 THEN
1880 error_code := 10; --Invalid Flexfield Name
1881 RETURN;
1882 END IF;
1883
1884 Get_Context_Type(flexfield_name, context,
1885 l_context_type, l_error_code);
1886
1887 IF l_error_code = 0 THEN
1888
1889 IF AM_Context_Exists(l_context_type, context)
1890 THEN
1891 context_flag := 'Y';
1892 ELSE
1893 error_code := 7; -- Invalid context passed
1894 RETURN;
1895 END IF;
1896
1897 IF AM_Segment_Exists(l_context_type, context, attribute,
1898 check_enabled, v_value_set_id, v_precedence)
1899 THEN
1900 precedence := v_precedence;
1901 attribute_flag := 'Y';
1902 ELSE
1903 error_code := 8; -- Invalid Attribute passed
1904 RETURN;
1905 END IF;
1906
1907 END IF; --If l_error_code = 0
1908
1909 ELSE
1910
1911 BEGIN
1912 v_flexfield_val_ind:= 1;
1913 fnd_dflex.get_flexfield(application_short_name,flexfield_name,v_dflex_r,v_dflex_dr);
1914
1915 -- Get the context listing for the flexfield
1916 fnd_dflex.get_contexts(v_dflex_r,v_context_dr);
1917
1918 IF (context_exists(context,v_context_dr,v_context_r) = TRUE) THEN
1919 context_flag := 'Y';
1920 ELSE
1921 context_flag := 'N';
1922 error_code := 7; -- Invalid context passed
1923 RETURN;
1924 END IF;
1925
1926 v_context_r.flexfield := v_dflex_r;
1927
1928 -- Get the enabled segments for the context selected.
1929
1930 --fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
1931 fnd_dflex.get_segments(v_context_r,v_segments_dr,FALSE);
1932
1933 IF (segment_exists(attribute,v_segments_dr,check_enabled,v_value_set_id,v_precedence) = TRUE) THEN
1934 IF (v_precedence IS NOT NULL) THEN
1935 precedence := v_precedence;
1936 END IF;
1937 attribute_flag := 'Y';
1938 IF (v_value_set_id IS NULL) THEN
1939 datatype := 'C';
1940 value_flag := 'Y'; -- If there is no valueset attached then just pass the validation.
1941 error_code := 0;
1942 RETURN;
1943 END IF;
1944 ELSE
1945 attribute_flag :='N';
1946 error_code := 8; -- Invalid segment passed
1947 RETURN;
1948 END IF;
1949
1950 EXCEPTION
1951 WHEN NO_DATA_FOUND THEN
1952 IF (v_flexfield_val_ind = 1) THEN
1953 error_code := 10;
1954 RETURN;
1955 END IF;
1956 END;
1957
1958 END IF; --If l_attrmgr_installed = 'Y'
1959
1960 --Validation of the Value against a Value Set is common to both code paths,
1961 --i.e., with or without Attributes Manager Installed.
1962
1963 -- If there is no valueset attached then just pass the validation.
1964 IF (v_value_set_id IS NULL) THEN
1965 datatype := 'C';
1966 value_flag := 'Y';
1967 error_code := 0;
1968 RETURN;
1969 END IF;
1970
1971 -- Get value set information and validate the value passed.
1972 fnd_vset.get_valueset(v_value_set_id,v_valueset_r,v_valueset_dr);
1973
1974 datatype := v_valueset_dr.format_type;
1975
1976 -- check if there is any value set attached to the segment
1977 IF (v_value_set_id IS NULL or not g_validate_flag)
1978 THEN
1979 value_flag := 'Y';
1980 RETURN;
1981 END IF;
1982
1983 IF (v_valueset_r.validation_type = 'I') THEN --Validation type is independent
1984
1985 IF value_exists(v_value_set_id,value) THEN
1986 value_flag := 'Y';
1987 ELSE
1988 error_code := 9; -- Value does not exist.
1989 END IF;
1990
1991 ELSIF (v_valueset_r.validation_type = 'F') THEN --Validation type is table
1992
1993 IF value_exists_in_table(v_valueset_r.table_info,value,l_id,l_value) THEN
1994 value_flag := 'Y';
1995 ELSE
1996 error_code := 9; -- Value does not exist.
1997 END IF;
1998
1999 ELSIF (v_valueset_r.validation_type = 'N') or datatype in( 'N','X','Y') THEN
2000 ---added for proper handling of dates/number in multilingual envs.
2001 error_code := validate_num_date(datatype,value);
2002
2003 IF error_code = 0 then
2004 value_flag := 'Y';
2005 ELSE
2006 value_flag := 'N';
2007 END IF;
2008
2009 END IF;
2010
2011 END validate_qp_flexfield;
2012
2013
2014 -- =============================================================================
2015 -- PROCEDURE validate_context_code
2016 -- procedure type PUBLIC
2017 -- out parameters : p_error_code
2018 -- Meaning for error codes
2019 -- errorcode = 0 Successfull
2020 -- = 1 flexfield_name is not passed.
2021 -- = 2 context name is not passed
2022 -- = 3 application short name value is not passed.
2023 -- = 4 invalid application short name
2024 -- = 5 invalid flexfield name
2025 -- = 6 Invalid context.
2026 -- DESCRIPTION
2027 -- Validates the context passed to the procedure.
2028 --==============================================================================
2029
2030 PROCEDURE validate_context_code(p_flexfield_name IN VARCHAR2,
2031 p_application_short_name IN VARCHAR2,
2032 p_context_name IN VARCHAR2,
2033 p_error_code OUT NOCOPY NUMBER)
2034 IS
2035 CURSOR Cur_get_application_id(app_short_name VARCHAR2)
2036 IS
2037 SELECT application_id
2038 FROM fnd_application
2039 WHERE application_short_name = app_short_name;
2040
2041 v_flexfield_name NUMBER DEFAULT 1;
2042 v_dflex_r fnd_dflex.dflex_r;
2043 v_context_r fnd_dflex.context_r;
2044 v_context_dr fnd_dflex.contexts_dr;
2045 v_dflex_dr fnd_dflex.dflex_dr;
2046 v_flexfield_val_ind NUMBER;
2047
2048 l_attrmgr_installed VARCHAR2(30);
2049 l_application_id NUMBER;
2050 l_context_type VARCHAR2(30);
2051
2052 BEGIN
2053 IF (p_flexfield_name IS NULL) THEN
2054 p_error_code := 1; -- flexfield_name is not passed.
2055 RETURN;
2056 END IF;
2057
2058 IF (p_context_name IS NULL) THEN
2059 p_error_code := 2;
2060 RETURN; -- context value is not passed
2061 END IF;
2062
2063 IF (p_application_short_name IS NULL) THEN
2064 p_error_code := 3; -- application short name is not passed
2065 RETURN;
2066 END IF;
2067
2068 --Get Attrmgr_Installed status
2069 l_attrmgr_installed := Attrmgr_Installed;
2070
2071 -- Fetch application id for application short name passed.
2072 OPEN Cur_get_application_id(p_application_short_name);
2073
2074 IF l_attrmgr_installed = 'Y' THEN
2075 FETCH Cur_get_application_id INTO l_application_id;
2076 ELSE
2077 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
2078 END IF;
2079
2080 IF (Cur_get_application_id%NOTFOUND) THEN
2081 CLOSE Cur_get_application_id;
2082 p_error_code := 4; -- Invalid application short name.
2083 RETURN;
2084 END IF;
2085
2086 CLOSE Cur_get_application_id;
2087
2088 -- check if flexfield name passed is a valid one or not.
2089
2090 IF l_attrmgr_installed = 'Y' THEN
2091
2092 IF p_flexfield_name NOT IN ('QP_ATTR_DEFNS_PRICING',
2093 'QP_ATTR_DEFNS_QUALIFIER')
2094 THEN
2095 p_error_code := 5; --Invalid Flexfield Name
2096 RETURN;
2097 END IF;
2098
2099 Get_Context_Type(p_flexfield_name,p_context_name,
2100 l_context_type, p_error_code);
2101
2102 IF p_error_code = 0 THEN
2103
2104 IF AM_Context_Exists(l_context_type, p_context_name)
2105 THEN
2106 p_error_code := 0; -- valid context name.
2107 ELSE
2108 p_error_code := 6; -- Invalid context passed
2109 RETURN;
2110 END IF;
2111
2112 END IF; --If p_error_code = 0
2113
2114 ELSE
2115
2116 BEGIN
2117 v_flexfield_val_ind:= 1;
2118 fnd_dflex.get_flexfield(p_application_short_name,p_flexfield_name,
2119 v_dflex_r,v_dflex_dr);
2120
2121 -- Get the context listing for the flexfield
2122 fnd_dflex.get_contexts(v_dflex_r,v_context_dr);
2123
2124 IF (context_exists(p_context_name,v_context_dr,v_context_r) = TRUE)
2125 THEN
2126 p_error_code := 0; -- valid context name.
2127 ELSE
2128 p_error_code := 6; -- Invalid context passed
2129 RETURN;
2130 END IF;
2131
2132 EXCEPTION
2133 WHEN NO_DATA_FOUND THEN
2134 IF (v_flexfield_val_ind = 1) THEN
2135 p_error_code := 5;
2136 RETURN;
2137 END IF;
2138 END;
2139
2140 END IF; --If l_attrmgr_installed = 'Y'
2141
2142 END validate_context_code;
2143
2144 -- =============================================================================
2145 -- PROCEDURE validate_attribute_name
2146 -- procedure type PUBLIC
2147 -- out parameters : p_error_code
2148 -- Meaning for error codes
2149 -- errorcode = 0 Successfull
2150 -- = 1 flexfield_name is not passed.
2151 -- = 2 context name is not passed
2152 -- = 3 application short name value is not passed.
2153 -- = 4 invalid application short name
2154 -- = 5 invalid flexfield name
2155 -- = 6 Invalid context.
2156 -- = 7 No Attribute Passes.
2157 -- = 8 Invalid Attribute.
2158 -- DESCRIPTION
2159 -- Validates the attribute passed to the procedure.
2160 --==============================================================================
2161
2162 PROCEDURE validate_attribute_name(p_application_short_name IN VARCHAR2,
2163 p_flexfield_name IN VARCHAR2,
2164 p_context_name IN VARCHAR2,
2165 p_attribute_name IN VARCHAR2,
2166 p_error_code OUT NOCOPY NUMBER)
2167 IS
2168 CURSOR Cur_get_application_id(app_short_name VARCHAR2) IS
2169 SELECT application_id
2170 FROM fnd_application
2171 WHERE application_short_name = app_short_name;
2172
2173 v_error_code NUMBER DEFAULT 0;
2174 v_dflex_r fnd_dflex.dflex_r;
2175 v_segments_dr fnd_dflex.segments_dr;
2176 v_precedence NUMBER;
2177 v_value_set_id NUMBER;
2178 v_context_r fnd_dflex.context_r;
2179
2180 l_application_id NUMBER;
2181 l_context_type VARCHAR2(30);
2182
2183 BEGIN
2184
2185 Validate_Context_Code(p_flexfield_name,p_application_short_name,
2186 p_context_name,v_error_code);
2187
2188 IF (v_error_code = 0) THEN
2189
2190 IF (p_attribute_name IS NULL) THEN
2191 p_error_code := 7; -- No attribute passed
2192 RETURN;
2193 END IF;
2194
2195 IF Attrmgr_Installed = 'Y' THEN
2196
2197 Get_Context_Type(p_flexfield_name, p_context_name,
2198 l_context_type, p_error_code);
2199
2200 IF p_error_code = 0 THEN
2201
2202 IF AM_Segment_Exists(l_context_type, p_context_name, p_attribute_name,
2203 FALSE, v_value_set_id, v_precedence)
2204 THEN
2205 p_error_code := 0; -- Successful
2206 ELSE
2207 p_error_code := 8; -- Invalid Attribute passed
2208 RETURN;
2209 END IF;
2210
2211 END IF; --If p_error_code is 0
2212
2213 ELSE
2214
2215 -- Just get the application id no validation required.
2216
2217 OPEN Cur_get_application_id(p_application_short_name);
2218 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
2219 CLOSE Cur_get_application_id;
2220
2221 v_dflex_r.flexfield_name := p_flexfield_name;
2222 v_context_r.flexfield := v_dflex_R;
2223 v_context_r.context_code := p_context_name;
2224
2225 -- Get the enabled segments for the context selected.
2226
2227 --FND_DFLEX.GET_SEGMENTS(V_CONTEXT_R,V_SEGMENTS_DR,TRUE);
2228 FND_DFLEX.GET_SEGMENTS(V_CONTEXT_R,V_SEGMENTS_DR,FALSE);
2229
2230 IF (segment_exists(p_attribute_name,v_segments_dr,true,v_value_set_id,
2231 v_precedence) = TRUE)
2232 THEN
2233 p_error_code := 0;
2234 RETURN;
2235 ELSE
2236 p_error_code := 8; -- INVALID ATTRIBUTE PASSED
2237 RETURN;
2238 END IF;
2239
2240 END IF; --If Attrmgr_Installed = 'Y'
2241
2242 ELSE
2243
2244 p_error_code := v_error_code;
2245 RETURN;
2246
2247 END IF; --If v_error_code = 0
2248
2249 END Validate_Attribute_Name;
2250
2251
2252
2253 PROCEDURE Get_Valueset_Id(p_flexfield_name IN VARCHAR2,
2254 p_context IN VARCHAR2 ,
2255 p_seg IN VARCHAR2 ,
2256 x_vsid OUT NOCOPY NUMBER,
2257 x_format_type OUT NOCOPY VARCHAR2,
2258 x_validation_type OUT NOCOPY VARCHAR2)
2259 IS
2260
2261 flexfield fnd_dflex.dflex_r;
2262 flexinfo fnd_dflex.dflex_dr;
2263 test_rec fnd_vset.valueset_r;
2264 x_valuesetid NUMBER := null;
2265 test_frec fnd_vset.valueset_dr;
2266 contexts fnd_dflex.contexts_dr;
2267 i BINARY_INTEGER;
2268 j BINARY_INTEGER;
2269 segments fnd_dflex.segments_dr;
2270
2271 l_context_type VARCHAR2(30);
2272 l_error_code NUMBER;
2273
2274 CURSOR valueset_id_cur(a_context_type VARCHAR2, a_context_code VARCHAR2,
2275 a_segment_code VARCHAR2)
2276 IS
2277 SELECT nvl(a.user_valueset_id, a.seeded_valueset_id)
2278 FROM qp_segments_b a, qp_prc_contexts_b b
2279 WHERE a.prc_context_id = b.prc_context_id
2280 AND b.prc_context_type = a_context_type
2281 AND b.prc_context_code = a_context_code
2282 AND a.segment_code = a_segment_code;
2283
2284
2285 BEGIN
2286
2287 IF Attrmgr_Installed = 'Y' THEN
2288
2289 QP_UTIL.Get_Context_Type(p_flexfield_name, p_context,
2290 l_context_type, l_error_code);
2291
2292 IF l_error_code = 0 THEN
2293
2294 OPEN valueset_id_cur(l_context_type, p_context, p_seg);
2295 FETCH valueset_id_cur INTO x_valuesetid;
2296 CLOSE valueset_id_cur;
2297
2298 END IF; --If l_error_code = 0
2299
2300 ELSE
2301
2302 fnd_dflex.get_flexfield('QP',p_flexfield_name,flexfield,flexinfo);
2303 fnd_dflex.get_contexts(flexfield,contexts);
2304 fnd_dflex.get_segments(fnd_dflex.make_context(flexfield,p_context),segments,true);
2305 FOR j IN 1..segments.nsegments LOOP
2306
2307 IF segments.segment_name(j) = p_seg THEN
2308 x_valuesetid := segments.value_set(j);
2309 END IF;
2310
2311 END LOOP;
2312
2313 END IF; --if Attrmgr_Installed = 'Y'
2314
2315 IF x_valuesetid IS NOT NULL THEN
2316 fnd_vset.get_valueset(x_valuesetid,test_rec,test_frec);
2317 x_vsid :=x_valuesetid;
2318 x_format_type :=test_frec.format_type;
2319 x_validation_type :=test_rec.validation_type;
2320 ELSE
2321 x_vsid := NULL;
2322 x_format_type :='C';
2323 x_validation_type :=NULL;
2324
2325 END IF;
2326
2327 END GET_VALUESET_ID;
2328
2329
2330 PROCEDURE GET_PROD_FLEX_PROPERTIES( PRIC_ATTRIBUTE_CONTEXT IN VARCHAR2,
2331 PRIC_ATTRIBUTE IN VARCHAR2,
2332 PRIC_ATTR_VALUE IN VARCHAR2,
2333 X_DATATYPE OUT NOCOPY VARCHAR2,
2334 X_PRECEDENCE OUT NOCOPY NUMBER,
2335 X_ERROR_CODE OUT NOCOPY NUMBER)
2336 IS
2337
2338 L_CONTEXT_FLAG VARCHAR2(1);
2339 L_ATTRIBUTE_FLAG VARCHAR2(1);
2340 L_VALUE_FLAG VARCHAR2(1);
2341 L_DATATYPE VARCHAR2(1);
2342 L_PRECEDENCE NUMBER;
2343 L_ERROR_CODE NUMBER := 0;
2344
2345 BEGIN
2346
2347 QP_UTIL.VALIDATE_QP_FLEXFIELD(FLEXFIELD_NAME =>'QP_ATTR_DEFNS_PRICING'
2348 ,CONTEXT =>PRIC_ATTRIBUTE_CONTEXT
2349 ,ATTRIBUTE =>PRIC_ATTRIBUTE
2350 ,VALUE =>PRIC_ATTR_VALUE
2351 ,APPLICATION_SHORT_NAME => 'QP'
2352 ,CHECK_ENABLED =>FALSE
2353 ,CONTEXT_FLAG =>L_CONTEXT_FLAG
2354 ,ATTRIBUTE_FLAG =>L_ATTRIBUTE_FLAG
2355 ,VALUE_FLAG =>L_VALUE_FLAG
2356 ,DATATYPE =>L_DATATYPE
2357 ,PRECEDENCE =>L_PRECEDENCE
2358 ,ERROR_CODE =>L_ERROR_CODE
2359 );
2360
2361 X_DATATYPE := NVL(L_DATATYPE,'C');
2362 X_PRECEDENCE := NVL(L_PRECEDENCE,5000);
2363
2364 END GET_PROD_FLEX_PROPERTIES;
2365
2366
2367 PROCEDURE GET_QUAL_FLEX_PROPERTIES( QUAL_ATTRIBUTE_CONTEXT IN VARCHAR2,
2368 QUAL_ATTRIBUTE IN VARCHAR2,
2369 QUAL_ATTR_VALUE IN VARCHAR2,
2370 X_DATATYPE OUT NOCOPY VARCHAR2,
2371 X_PRECEDENCE OUT NOCOPY NUMBER,
2372 X_ERROR_CODE OUT NOCOPY NUMBER)
2373 IS
2374
2375 L_CONTEXT_FLAG VARCHAR2(1);
2376 L_ATTRIBUTE_FLAG VARCHAR2(1);
2377 L_VALUE_FLAG VARCHAR2(1);
2378 L_DATATYPE VARCHAR2(1);
2379 L_PRECEDENCE NUMBER;
2380 L_ERROR_CODE NUMBER := 0;
2381
2382 BEGIN
2383
2384 QP_UTIL.VALIDATE_QP_FLEXFIELD(FLEXFIELD_NAME =>'QP_ATTR_DEFNS_QUALIFIER'
2385 ,CONTEXT =>QUAL_ATTRIBUTE_CONTEXT
2386 ,ATTRIBUTE =>QUAL_ATTRIBUTE
2387 ,VALUE =>QUAL_ATTR_VALUE
2388 ,APPLICATION_SHORT_NAME => 'QP'
2389 ,CHECK_ENABLED =>FALSE
2390 ,CONTEXT_FLAG =>L_CONTEXT_FLAG
2391 ,ATTRIBUTE_FLAG =>L_ATTRIBUTE_FLAG
2392 ,VALUE_FLAG =>L_VALUE_FLAG
2393 ,DATATYPE =>L_DATATYPE
2394 ,PRECEDENCE =>L_PRECEDENCE
2395 ,ERROR_CODE =>L_ERROR_CODE
2396 );
2397
2398 X_DATATYPE := NVL(L_DATATYPE,'C');
2399 X_PRECEDENCE := NVL(L_PRECEDENCE,5000);
2400
2401 END GET_QUAL_FLEX_PROPERTIES;
2402
2403
2404 -- =======================================================================
2405 -- FUNCTION Get_Attribute_Name
2406 -- FUNTION TYPE Public
2407 -- RETURNS APPLICATION_COLUMN_NAME
2408 -- DESCRIPTION
2409 -- searches for segment name and returns coressponding application column name.
2410 -- =======================================================================
2411
2412
2413 FUNCTION Get_Attribute_Name(p_application_short_name IN VARCHAR2,
2414 p_flexfield_name IN VARCHAR2,
2415 p_context_name IN VARCHAR2,
2416 p_attribute_name IN VARCHAR2)
2417 RETURN VARCHAR2
2418 IS
2419
2420 CURSOR cur_get_application_id(app_short_name VARCHAR2)
2421 IS
2422 SELECT application_id
2423 FROM fnd_application
2424 WHERE application_short_name = app_short_name;
2425
2426 v_dflex_r fnd_dflex.dflex_r;
2427 v_segments_dr fnd_dflex.segments_dr;
2428 v_context_r fnd_dflex.context_r;
2429
2430 CURSOR pricing_attribute_name_cur(a_context_code VARCHAR2, a_segment_name VARCHAR2)
2431 IS
2432 SELECT a.segment_mapping_column
2433 FROM qp_segments_v a, qp_prc_contexts_b b
2434 WHERE b.prc_context_id = a.prc_context_id
2435 AND b.prc_context_code = a_context_code
2436 AND a.segment_code = a_segment_name
2437 AND a.segment_mapping_column like 'PRICING%';--deliberately matching a_segment_name
2438 --with segment_code to be consistent
2439 --with old logic/naming convention.
2440
2441 CURSOR qual_attribute_name_cur(a_context_code VARCHAR2, a_segment_name VARCHAR2)
2442 IS
2443 SELECT a.segment_mapping_column
2444 FROM qp_segments_v a, qp_prc_contexts_b b
2445 WHERE b.prc_context_id = a.prc_context_id
2446 AND b.prc_context_code = a_context_code
2447 AND a.segment_code = a_segment_name
2448 AND a.segment_mapping_column like 'QUALIFIER%';--deliberately matching a_segment_name
2449 --with segment_code to be consistent
2450 --with old logic/naming convention.
2451
2452 l_attribute_col_name VARCHAR2(30);
2453
2454 BEGIN
2455
2456 IF Attrmgr_Installed = 'Y' THEN
2457
2458 IF p_flexfield_name = 'QP_ATTR_DEFNS_PRICING' THEN
2459
2460 OPEN pricing_attribute_name_cur(p_context_name, p_attribute_name);
2461 FETCH pricing_attribute_name_cur INTO l_attribute_col_name;
2462 IF pricing_attribute_name_cur%FOUND THEN
2463 CLOSE pricing_attribute_name_cur;
2464 RETURN l_attribute_col_name;
2465 END IF;
2466
2467 CLOSE pricing_attribute_name_cur;
2468
2469 ELSE
2470
2471 OPEN qual_attribute_name_cur(p_context_name, p_attribute_name);
2472 FETCH qual_attribute_name_cur INTO l_attribute_col_name;
2473 IF qual_attribute_name_cur%FOUND THEN
2474 CLOSE qual_attribute_name_cur;
2475 RETURN l_attribute_col_name;
2476 END IF;
2477
2478 CLOSE qual_attribute_name_cur;
2479
2480 END IF;
2481
2482 ELSE
2483
2484 OPEN cur_get_application_id(p_application_short_name);
2485 FETCH cur_get_application_id INTO v_dflex_r.application_id;
2486 CLOSE cur_get_application_id;
2487
2488 v_dflex_r.flexfield_name := p_flexfield_name;
2489 v_context_r.flexfield := v_dflex_r;
2490 v_context_r.context_code := p_context_name;
2491
2492 -- get the enabled segments for the context selected.
2493 fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
2494
2495 IF (v_segments_dr.nsegments > 0) THEN
2496
2497 FOR i IN 1..v_segments_dr.nsegments LOOP
2498
2499 IF (v_segments_dr.segment_name(I) = p_attribute_name) THEN
2500
2501 RETURN (v_segments_dr.application_column_name(i));
2502
2503 END IF;
2504
2505 END LOOP;
2506
2507 ELSE
2508
2509 RETURN('0');
2510
2511 END IF;
2512
2513 END IF; --if Attrmgr_Installed = 'Y'
2514
2515 RETURN NULL;
2516
2517 END Get_Attribute_Name;
2518
2519
2520
2521 FUNCTION check_context_existance( p_application_id IN fnd_application.application_id%TYPE,
2522 p_descriptive_flexfield_name IN VARCHAR2,
2523 p_descr_flex_context_code IN VARCHAR2)
2524 RETURN BOOLEAN
2525
2526 IS
2527
2528 dummy NUMBER(1);
2529 x_context_exists BOOLEAN := TRUE;
2530
2531 BEGIN
2532 SELECT NULL INTO dummy
2533 FROM fnd_descr_flex_contexts
2534 WHERE application_id = p_application_id
2535 AND descriptive_flexfield_name = p_descriptive_flexfield_name
2536 AND descriptive_flex_context_code = p_descr_flex_context_code;
2537
2538 --dbms_output.put_line ('Context Check Successful');
2539 return x_context_exists;
2540
2541 EXCEPTION
2542 WHEN no_data_found THEN
2543 x_context_exists := FALSE;
2544 return x_context_exists;
2545 WHEN OTHERS THEN
2546 NULL;
2547 --dbms_output.put_line ('Error in Context Check');
2548 END;
2549
2550 FUNCTION check_segment_existance( p_application_id NUMBER,
2551 p_context_code VARCHAR2,
2552 p_flexfield_name VARCHAR2,
2553 p_application_column_name VARCHAR2)
2554 RETURN BOOLEAN
2555 IS
2556
2557 dummy NUMBER(1);
2558 x_seg_exists BOOLEAN := TRUE;
2559
2560 BEGIN
2561 select NULL INTO dummy
2562 from FND_DESCR_FLEX_COLUMN_USAGES
2563 where APPLICATION_ID = p_application_id
2564 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code
2565 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2566 and APPLICATION_COLUMN_NAME = p_application_column_name;
2567
2568 --dbms_output.put_line ('Segment Check Successful');
2569 return x_seg_exists ;
2570
2571 EXCEPTION
2572 WHEN no_data_found THEN
2573 x_seg_exists := FALSE;
2574 return x_seg_exists;
2575 WHEN OTHERS THEN
2576 NULL;
2577 --dbms_output.put_line ('Error in Segment Check');
2578 END;
2579
2580 FUNCTION check_segment_name_existance( p_application_id NUMBER,
2581 p_context_code VARCHAR2,
2582 p_flexfield_name VARCHAR2,
2583 p_segment_name VARCHAR2)
2584 RETURN BOOLEAN
2585 IS
2586
2587 dummy NUMBER(1);
2588 x_seg_exists BOOLEAN := TRUE;
2589
2590 BEGIN
2591 select NULL INTO dummy
2592 from FND_DESCR_FLEX_COLUMN_USAGES
2593 where APPLICATION_ID = p_application_id
2594 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_context_code
2595 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2596 and END_USER_COLUMN_NAME = p_segment_name;
2597
2598 --dbms_output.put_line ('Segment Name Check Successful');
2599 return x_seg_exists ;
2600
2601 EXCEPTION
2602 WHEN no_data_found THEN
2603 x_seg_exists := FALSE;
2604 return x_seg_exists;
2605 WHEN OTHERS THEN
2606 NULL;
2607 --dbms_output.put_line ('Error in Segment Name Check');
2608 END;
2609
2610 PROCEDURE QP_UPGRADE_CONTEXT( P_PRODUCT IN VARCHAR2
2611 , P_NEW_PRODUCT IN VARCHAR2
2612 , P_FLEXFIELD_NAME IN VARCHAR2
2613 , P_NEW_FLEXFIELD_NAME IN VARCHAR2)
2614 IS
2615 P_FLEXFIELD FND_DFLEX.DFLEX_R;
2616 P_FLEXINFO FND_DFLEX.DFLEX_DR;
2617 L_CONTEXTS FND_DFLEX.CONTEXTS_DR;
2618 GDE_CONTEXTS FND_DFLEX.CONTEXTS_DR;
2619 L_SEGMENTS FND_DFLEX.SEGMENTS_DR;
2620 GDE_SEGMENTS FND_DFLEX.SEGMENTS_DR;
2621 NEW_GDE_SEGMENTS FND_DFLEX.SEGMENTS_DR;
2622 L_REQUIRED VARCHAR2(5);
2623 L_SECURITY_ENABLED VARCHAR2(5);
2624
2625 L_VALUE_SET_ID NUMBER := 0;
2626 L_VALUE_SET VARCHAR2(100) := NULL;
2627 L_SEGMENT_COUNT NUMBER;
2628 p_segment_name VARCHAR2(240);
2629 NEW_GDE_CONTEXT_CODE CONSTANT VARCHAR2(30) := 'Upgrade Context';
2630 OLD_GDE_CONTEXT_CODE CONSTANT VARCHAR2(30) := 'Global Data Elements';
2631 G_QP_ATTR_DEFNS_PRICING CONSTANT VARCHAR2(30) := 'QP_ATTR_DEFNS_PRICING';
2632 QP_APPLICATION_ID CONSTANT fnd_application.application_id%TYPE := 661;
2633 p_context_name VARCHAR2(240);
2634 p_application_column_name VARCHAR2(240);
2635 p_application_id VARCHAR2(30);
2636 BEGIN
2637
2638 FND_FLEX_DSC_API.SET_SESSION_MODE('customer_data');
2639
2640 FND_PROFILE.PUT('RESP_APPL_ID','0');
2641 FND_PROFILE.PUT('RESP_ID','20419');
2642 FND_PROFILE.PUT('USER_ID','1001');
2643
2644 -- Delete all the segments under the New Global Data Elements Context(if any)
2645
2646 --dbms_output.put_line ('Before even starting the process');
2647 IF ( FND_FLEX_DSC_API.FLEXFIELD_EXISTS( P_NEW_PRODUCT,
2648 P_NEW_FLEXFIELD_NAME )) THEN
2649 --dbms_output.put_line ('Entered the Processing');
2650 IF (P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING) THEN
2651 -- Get the New Global Data Elements Context and Its Segments
2652 FND_DFLEX.GET_FLEXFIELD( P_NEW_PRODUCT
2653 , P_NEW_FLEXFIELD_NAME
2654 , P_FLEXFIELD
2655 , P_FLEXINFO );
2656
2657 -- Get all contexts for the flexfield
2658 FND_DFLEX.GET_CONTEXTS( P_FLEXFIELD, L_CONTEXTS );
2659
2660 -- Get the Context Code for New Global Data Elements Context (if any)
2661 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
2662 --dbms_output.put_line ('Found the Old GDE Context');
2663 IF (L_CONTEXTS.CONTEXT_CODE(I) = OLD_GDE_CONTEXT_CODE) THEN
2664 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , OLD_GDE_CONTEXT_CODE)
2665 ,NEW_GDE_SEGMENTS
2666 , FALSE ) ;
2667 END IF;
2668 EXIT;
2669 END LOOP;
2670
2671 IF (NEW_GDE_SEGMENTS.NSEGMENTS > 0) THEN
2672 --dbms_output.put_line('New GDE has segments');
2673 FOR I IN 1..NEW_GDE_SEGMENTS.NSEGMENTS LOOP
2674 --dbms_output.put_line('Trying to delete segments under old context');
2675 FND_FLEX_DSC_API.DELETE_SEGMENT( P_NEW_PRODUCT
2676 ,P_NEW_FLEXFIELD_NAME
2677 ,OLD_GDE_CONTEXT_CODE -- Global Data Elements
2678 ,NEW_GDE_SEGMENTS.SEGMENT_NAME(I));
2679 END LOOP;
2680 ELSE
2681 NULL;
2682 --dbms_output.put_line('New GDE has no segments');
2683 END IF; -- NEW_GDE_SEGMENTS.NSEGMENTS > 0
2684 END IF;
2685 END IF;
2686
2687 --dbms_output.put_line('Starting the actual Migration');
2688 -- Now start the migration of contexts and segments
2689 FND_DFLEX.GET_FLEXFIELD(
2690 P_PRODUCT
2691 , P_FLEXFIELD_NAME
2692 , P_FLEXFIELD
2693 , P_FLEXINFO );
2694
2695 FND_DFLEX.GET_CONTEXTS( P_FLEXFIELD, L_CONTEXTS );
2696
2697 -- Store all the old contexts
2698 GDE_CONTEXTS := L_CONTEXTS;
2699
2700 -- Check To See If New Flex Structure Exists
2701 IF ( FND_FLEX_DSC_API.FLEXFIELD_EXISTS( P_NEW_PRODUCT,
2702 P_NEW_FLEXFIELD_NAME )) THEN
2703 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
2704 --dbms_output.put_line ( ' Global Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
2705 IF (L_CONTEXTS.CONTEXT_CODE(I) = OLD_GDE_CONTEXT_CODE AND P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING) THEN
2706 --dbms_output.put_line('There are contexts for migration');
2707 IF (check_context_existance(QP_APPLICATION_ID,P_NEW_FLEXFIELD_NAME,NEW_GDE_CONTEXT_CODE) = FALSE) THEN
2708 --dbms_output.put_line ('Creating the Upgrade Context');
2709 FND_FLEX_DSC_API.CREATE_CONTEXT ( P_NEW_PRODUCT
2710 , P_NEW_FLEXFIELD_NAME
2711 , NEW_GDE_CONTEXT_CODE
2712 , NEW_GDE_CONTEXT_CODE
2713 , NEW_GDE_CONTEXT_CODE
2714 , 'Y'
2715 , 'N') ;
2716 --dbms_output.put_line ('Created the Upgrade Context');
2717 ELSE
2718 NULL;
2719 --dbms_output.put_line ('Upgrade Context Already Exists');
2720 END IF;
2721
2722 FND_FLEX_DSC_API.ENABLE_CONTEXT (P_NEW_PRODUCT
2723 , P_NEW_FLEXFIELD_NAME
2724 , NEW_GDE_CONTEXT_CODE
2725 , TRUE );
2726
2727 FND_FLEX_DSC_API.ENABLE_COLUMNS( P_NEW_PRODUCT
2728 , P_NEW_FLEXFIELD_NAME
2729 , 'ATTRIBUTE[0-9]+');
2730
2731 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , L_CONTEXTS.CONTEXT_CODE(I))
2732 ,L_SEGMENTS
2733 , FALSE ) ;
2734
2735
2736 -- Store all the old global data elements' segments
2737 GDE_SEGMENTS := L_SEGMENTS;
2738
2739 --dbms_output.put_line ( 'Old GDE Segments Count##: ' || nvl(GDE_SEGMENTS.NSEGMENTS,0));
2740
2741 FOR J IN 1..L_SEGMENTS.NSEGMENTS LOOP
2742 L_VALUE_SET_ID := L_SEGMENTS.VALUE_SET(J);
2743 BEGIN
2744 IF L_VALUE_SET_ID <> 0 THEN
2745 SELECT FLEX_VALUE_SET_NAME INTO
2746 L_VALUE_SET
2747 FROM FND_FLEX_VALUE_SETS
2748 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
2749 ELSE
2750 L_VALUE_SET := NULL;
2751 END IF;
2752 EXCEPTION
2753 WHEN NO_DATA_FOUND THEN
2754 L_VALUE_SET := NULL;
2755 WHEN TOO_MANY_ROWS THEN
2756 NULL;
2757 END;
2758
2759 IF (L_SEGMENTS.IS_REQUIRED(J) ) THEN
2760 L_REQUIRED := 'Y';
2761 ELSE
2762 L_REQUIRED := 'N';
2763 END IF;
2764
2765 IF (L_SEGMENTS.IS_ENABLED(J) ) THEN
2766 L_SECURITY_ENABLED := 'Y';
2767 ELSE
2768 L_SECURITY_ENABLED := 'N';
2769 END IF;
2770
2771
2772 IF (check_segment_existance(QP_APPLICATION_ID,
2773 NEW_GDE_CONTEXT_CODE,
2774 P_NEW_FLEXFIELD_NAME,
2775 L_SEGMENTS.APPLICATION_COLUMN_NAME(J)) = FALSE ) THEN
2776 IF (check_segment_name_existance(QP_APPLICATION_ID,
2777 NEW_GDE_CONTEXT_CODE,
2778 P_NEW_FLEXFIELD_NAME,
2779 L_SEGMENTS.SEGMENT_NAME(J)) = FALSE ) THEN
2780 p_segment_name := L_SEGMENTS.SEGMENT_NAME(J);
2781 ELSE
2782 p_segment_name := 'QP: ' || L_SEGMENTS.SEGMENT_NAME(J); -- Create new name
2783 END IF;
2784
2785 -- Storing the values for error handling
2786 p_context_name := NEW_GDE_CONTEXT_CODE;
2787 p_application_column_name := L_SEGMENTS.APPLICATION_COLUMN_NAME(J);
2788 p_application_id := QP_APPLICATION_ID;
2789
2790 --dbms_output.put_line ('Creating the Upgrade Context Segments');
2791 BEGIN
2792 FND_FLEX_DSC_API.CREATE_SEGMENT (
2793 APPL_SHORT_NAME => P_NEW_PRODUCT
2794 , FLEXFIELD_NAME => P_NEW_FLEXFIELD_NAME
2795 , CONTEXT_NAME => NEW_GDE_CONTEXT_CODE
2796 , NAME => p_segment_name
2797 , COLUMN => L_SEGMENTS.APPLICATION_COLUMN_NAME(J)
2798 , DESCRIPTION => L_SEGMENTS.DESCRIPTION(J)
2799 , SEQUENCE_NUMBER => J
2800 , ENABLED => 'Y'
2801 , DISPLAYED => 'Y'
2802 , VALUE_SET => L_VALUE_SET
2803 , DEFAULT_TYPE => NULL
2804 , DEFAULT_VALUE => NULL
2805 , REQUIRED => 'Y'
2806 , SECURITY_ENABLED => 'N'
2807 , DISPLAY_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2808 , DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2809 , CONCATENATED_DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2810 , LIST_OF_VALUES_PROMPT => L_SEGMENTS.COLUMN_PROMPT(J)
2811 , WINDOW_PROMPT => L_SEGMENTS.ROW_PROMPT(J)
2812 , RANGE => NULL
2813 , SRW_PARAMETER => NULL) ;
2814 EXCEPTION
2815 WHEN NO_DATA_FOUND THEN
2816 rollback;
2817 Log_Error(p_id1 => -9999,
2818 p_error_type => 'ERROR IN CREATING SEGMENT',
2819 p_error_desc => ' Application Id : ' || p_application_id ||
2820 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
2821 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
2822 ' Context Name : ' || p_context_name ||
2823 ' Application Column Name : ' || p_application_column_name ||
2824 ' Application Segment Name : ' || p_segment_name ,
2825 p_error_module => 'QP_Upgrade_Context');
2826 raise;
2827 END;
2828 END IF;
2829 END LOOP; -- L_SEGMENTS
2830 --EXIT;
2831 END IF; -- Global Data Elements
2832 END LOOP; -- L_CONTEXTS
2833
2834 --dbms_output.put_line('Total Context Count: ' || L_CONTEXTS.NCONTEXTS);
2835 -- Process other contexts(other than Global Data Elements)
2836 FOR I IN 1..L_CONTEXTS.NCONTEXTS LOOP
2837 IF ((L_CONTEXTS.CONTEXT_CODE(I) <> OLD_GDE_CONTEXT_CODE AND P_NEW_FLEXFIELD_NAME = G_QP_ATTR_DEFNS_PRICING)
2838 OR (P_NEW_FLEXFIELD_NAME <> G_QP_ATTR_DEFNS_PRICING)) THEN
2839 --dbms_output.put_line ('Before Other Context Existance Check');
2840 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
2841 IF (check_context_existance(QP_APPLICATION_ID,P_NEW_FLEXFIELD_NAME,L_CONTEXTS.CONTEXT_CODE(I)) = FALSE) THEN
2842 --dbms_output.put_line ('Creating Other Contexts');
2843 FND_FLEX_DSC_API.CREATE_CONTEXT ( P_NEW_PRODUCT
2844 , P_NEW_FLEXFIELD_NAME
2845 , L_CONTEXTS.CONTEXT_CODE(I)
2846 , L_CONTEXTS.CONTEXT_NAME(I)
2847 , L_CONTEXTS.CONTEXT_DESCRIPTION(I)
2848 , 'Y'
2849 , 'N') ;
2850
2851 END IF;
2852
2853 FND_FLEX_DSC_API.ENABLE_CONTEXT ( P_NEW_PRODUCT
2854 , P_NEW_FLEXFIELD_NAME
2855 , L_CONTEXTS.CONTEXT_CODE(I) --2847218 changed to CONTEXT_CODE
2856 , TRUE );
2857
2858 FND_FLEX_DSC_API.ENABLE_COLUMNS(P_NEW_PRODUCT
2859 , P_NEW_FLEXFIELD_NAME
2860 , 'ATTRIBUTE[0-9]+');
2861
2862 FND_DFLEX.GET_SEGMENTS ( FND_DFLEX.MAKE_CONTEXT( P_FLEXFIELD , L_CONTEXTS.CONTEXT_CODE(I))
2863 ,L_SEGMENTS
2864 , FALSE ) ;
2865
2866 L_SEGMENT_COUNT := L_SEGMENTS.NSEGMENTS;
2867 --dbms_output.put_line ('Other Context Segment Count : ' || L_SEGMENT_COUNT);
2868
2869 FOR J IN 1..L_SEGMENTS.NSEGMENTS LOOP
2870 L_VALUE_SET_ID := L_SEGMENTS.VALUE_SET(J);
2871 BEGIN
2872 IF L_VALUE_SET_ID <> 0 THEN
2873 SELECT FLEX_VALUE_SET_NAME INTO
2874 L_VALUE_SET
2875 FROM FND_FLEX_VALUE_SETS
2876 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
2877 ELSE
2878 L_VALUE_SET := NULL;
2879 END IF;
2880 EXCEPTION
2881 WHEN NO_DATA_FOUND THEN
2882 L_VALUE_SET := NULL;
2883 WHEN TOO_MANY_ROWS THEN
2884 NULL;
2885 END;
2886
2887 IF (L_SEGMENTS.IS_REQUIRED(J) ) THEN
2888 L_REQUIRED := 'Y';
2889 ELSE
2890 L_REQUIRED := 'N';
2891 END IF;
2892
2893 IF (L_SEGMENTS.IS_ENABLED(J) ) THEN
2894 L_SECURITY_ENABLED := 'Y';
2895 ELSE
2896 L_SECURITY_ENABLED := 'N';
2897 END IF;
2898
2899 --dbms_output.put_line ('Before Other Context Segment Existance Check');
2900 --dbms_output.put_line ('Before Segment Existance Check for Old Gde Segments');
2901 --dbms_output.put_line ('Flexfield Name : ' || P_NEW_FLEXFIELD_NAME);
2902 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
2903 --dbms_output.put_line ('Application Column Name : ' || L_SEGMENTS.APPLICATION_COLUMN_NAME(J));
2904 IF (check_segment_existance(QP_APPLICATION_ID,
2905 L_CONTEXTS.CONTEXT_CODE(I),
2906 P_NEW_FLEXFIELD_NAME,
2907 L_SEGMENTS.APPLICATION_COLUMN_NAME(J)) = FALSE ) THEN
2908 --dbms_output.put_line ('Segment check false');
2909 IF (check_segment_name_existance(QP_APPLICATION_ID,
2910 NEW_GDE_CONTEXT_CODE,
2911 P_NEW_FLEXFIELD_NAME,
2912 L_SEGMENTS.SEGMENT_NAME(J)) = FALSE ) THEN
2913 --dbms_output.put_line ('Segment name check false');
2914 p_segment_name := L_SEGMENTS.SEGMENT_NAME(J);
2915 ELSE
2916 p_segment_name := 'QP: ' || L_SEGMENTS.SEGMENT_NAME(J);
2917 END IF;
2918
2919 -- Storing the values for error handling
2920 p_context_name := L_CONTEXTS.CONTEXT_CODE(I);
2921 p_application_column_name := L_SEGMENTS.APPLICATION_COLUMN_NAME(J);
2922 p_application_id := QP_APPLICATION_ID;
2923
2924 --dbms_output.put_line ('Creating Other Contexts Segments ');
2925 BEGIN
2926 FND_FLEX_DSC_API.CREATE_SEGMENT (
2927 APPL_SHORT_NAME => P_NEW_PRODUCT
2928 , FLEXFIELD_NAME => P_NEW_FLEXFIELD_NAME
2929 , CONTEXT_NAME => L_CONTEXTS.CONTEXT_CODE(I) --2847218 changed to CONTEXT_CODE
2930 , NAME => p_segment_name
2931 , COLUMN => L_SEGMENTS.APPLICATION_COLUMN_NAME(J)
2932 , DESCRIPTION => L_SEGMENTS.DESCRIPTION(J)
2933 , SEQUENCE_NUMBER => J
2934 , ENABLED => 'Y'
2935 , DISPLAYED => 'Y'
2936 , VALUE_SET => L_VALUE_SET
2937 , DEFAULT_TYPE => NULL
2938 , DEFAULT_VALUE => NULL
2939 , REQUIRED => 'Y'
2940 , SECURITY_ENABLED => 'N'
2941 , DISPLAY_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2942 , DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2943 , CONCATENATED_DESCRIPTION_SIZE => L_SEGMENTS.DISPLAY_SIZE(J)
2944 , LIST_OF_VALUES_PROMPT => L_SEGMENTS.COLUMN_PROMPT(J)
2945 , WINDOW_PROMPT => L_SEGMENTS.ROW_PROMPT(J)
2946 , RANGE => NULL
2947 , SRW_PARAMETER => NULL) ;
2948 EXCEPTION
2949 WHEN NO_DATA_FOUND THEN
2950 rollback;
2951 Log_Error(p_id1 => -9999,
2952 p_error_type => 'ERROR IN CREATING SEGMENT',
2953 p_error_desc => ' Application Id : ' || p_application_id ||
2954 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
2955 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
2956 ' Context Name : ' || p_context_name ||
2957 ' Application Column Name : ' || p_application_column_name ||
2958 ' Application Segment Name : ' || p_segment_name ,
2959 p_error_module => 'QP_Upgrade_Context');
2960 raise;
2961 END ;
2962 END IF;
2963 END LOOP; -- L_SEGMENTS
2964
2965 -- Append all the global data segments into other contexts
2966 --dbms_output.put_line ('Old GDE SEGMENTS Count : ' || nvl(GDE_SEGMENTS.NSEGMENTS,0));
2967 IF (nvl(GDE_SEGMENTS.NSEGMENTS,0) > 0) THEN
2968 FOR K IN 1..GDE_SEGMENTS.NSEGMENTS LOOP
2969 L_VALUE_SET_ID := GDE_SEGMENTS.VALUE_SET(K);
2970 BEGIN
2971 IF L_VALUE_SET_ID <> 0 THEN
2972 SELECT FLEX_VALUE_SET_NAME INTO
2973 L_VALUE_SET
2974 FROM FND_FLEX_VALUE_SETS
2975 WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
2976 ELSE
2977 L_VALUE_SET := NULL;
2978 END IF;
2979 EXCEPTION
2980 WHEN NO_DATA_FOUND THEN
2981 L_VALUE_SET := NULL;
2982 WHEN TOO_MANY_ROWS THEN
2983 NULL;
2984 END;
2985
2986 --dbms_output.put_line ('GDE SEGMENTS Loop 1');
2987 IF (GDE_SEGMENTS.IS_REQUIRED(K) ) THEN
2988 L_REQUIRED := 'Y';
2989 ELSE
2990 L_REQUIRED := 'N';
2991 END IF;
2992
2993 IF (GDE_SEGMENTS.IS_ENABLED(K) ) THEN
2994 L_SECURITY_ENABLED := 'Y';
2995 ELSE
2996 L_SECURITY_ENABLED := 'N';
2997 END IF;
2998
2999 --dbms_output.put_line ('Before Segment Existance Check for Old Gde Segments');
3000 --dbms_output.put_line ('Flexfield Name : ' || P_NEW_FLEXFIELD_NAME);
3001 --dbms_output.put_line ('Context Code : ' || L_CONTEXTS.CONTEXT_CODE(I));
3002 --dbms_output.put_line ('Application Column Name : ' || GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K));
3003 IF (check_segment_existance(QP_APPLICATION_ID,
3004 L_CONTEXTS.CONTEXT_CODE(I),
3005 P_NEW_FLEXFIELD_NAME,
3006 GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K)) = FALSE ) THEN
3007 --dbms_output.put_line ('Segment check false');
3008 IF (check_segment_name_existance(QP_APPLICATION_ID,
3009 L_CONTEXTS.CONTEXT_CODE(I),
3010 P_NEW_FLEXFIELD_NAME,
3011 GDE_SEGMENTS.SEGMENT_NAME(K)) = FALSE ) THEN
3012 --dbms_output.put_line ('Segment name check false');
3013 p_segment_name := GDE_SEGMENTS.SEGMENT_NAME(K);
3014 ELSE
3015 p_segment_name := 'QP: ' || GDE_SEGMENTS.SEGMENT_NAME(K);
3016 END IF;
3017
3018 -- Storing the values for error handling
3019 p_context_name := L_CONTEXTS.CONTEXT_CODE(I);
3020 p_application_column_name := GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K);
3021 p_application_id := QP_APPLICATION_ID;
3022
3023 --dbms_output.put_line ('Creating the OLD Gde segments to all contexts');
3024 BEGIN
3025 FND_FLEX_DSC_API.CREATE_SEGMENT (
3026 APPL_SHORT_NAME => P_NEW_PRODUCT
3027 , FLEXFIELD_NAME =>P_NEW_FLEXFIELD_NAME
3028 , CONTEXT_NAME => L_CONTEXTS.CONTEXT_CODE(I) --2847218 changed to CONTEXT_CODE
3029 , NAME => p_segment_name
3030 , COLUMN => GDE_SEGMENTS.APPLICATION_COLUMN_NAME(K)
3031 , DESCRIPTION => GDE_SEGMENTS.DESCRIPTION(K)
3032 , SEQUENCE_NUMBER => L_SEGMENT_COUNT + K
3033 , ENABLED => 'Y'
3034 , DISPLAYED => 'Y'
3035 , VALUE_SET => L_VALUE_SET
3036 , DEFAULT_TYPE => NULL
3037 , DEFAULT_VALUE => NULL
3038 , REQUIRED => 'Y'
3039 , SECURITY_ENABLED => 'N'
3040 , DISPLAY_SIZE => GDE_SEGMENTS.DISPLAY_SIZE(K)
3041 , DESCRIPTION_SIZE => GDE_SEGMENTS.DISPLAY_SIZE(K)
3042 , CONCATENATED_DESCRIPTION_SIZE => GDE_SEGMENTS.DISPLAY_SIZE(K)
3043 , LIST_OF_VALUES_PROMPT => GDE_SEGMENTS.COLUMN_PROMPT(K)
3044 , WINDOW_PROMPT => GDE_SEGMENTS.ROW_PROMPT(K)
3045 , RANGE => NULL
3046 , SRW_PARAMETER => NULL) ;
3047 EXCEPTION
3048 WHEN NO_DATA_FOUND THEN
3049 rollback;
3050 Log_Error(p_id1 => -9999,
3051 p_error_type => 'ERROR IN CREATING SEGMENT',
3052 p_error_desc => ' Application Id : ' || p_application_id ||
3053 ' Old Flexfield Name : ' || P_FLEXFIELD_NAME ||
3054 ' New Flexfield Name : ' || P_NEW_FLEXFIELD_NAME ||
3055 ' Context Name : ' || p_context_name ||
3056 ' Application Column Name : ' || p_application_column_name ||
3057 ' Application Segment Name : ' || p_segment_name ,
3058 p_error_module => 'QP_Upgrade_Context');
3059 raise;
3060 END ;
3061 END IF;
3062 END LOOP; -- GDE_SEGMENTS
3063 END IF; -- GDE_SEGMENTS.NSEGMENTS > 0
3064 END IF; -- Global Data Elements
3065 END LOOP; -- CONTEXTS
3066 END IF; /* CHECK FOR NEW FLEX FIELD STRUCTURE EXISTS */
3067 EXCEPTION
3068 WHEN OTHERS THEN
3069 --dbms_output.put_line(fnd_flex_dsc_api.message);
3070 rollback;
3071 Log_Error(p_id1 => -6501,
3072 p_error_type => 'FLEXFIELD UPGRADE',
3073 p_error_desc => fnd_flex_dsc_api.message,
3074 p_error_module => 'QP_Upgrade_Context');
3075 raise;
3076 END QP_UPGRADE_CONTEXT;
3077
3078 PROCEDURE LOG_ERROR( P_ID1 VARCHAR2,
3079 P_ID2 VARCHAR2 :=NULL,
3080 P_ID3 VARCHAR2 :=NULL,
3081 P_ID4 VARCHAR2 :=NULL,
3082 P_ID5 VARCHAR2 :=NULL,
3083 P_ID6 VARCHAR2 :=NULL,
3084 P_ID7 VARCHAR2 :=NULL,
3085 P_ID8 VARCHAR2 :=NULL,
3086 P_ERROR_TYPE VARCHAR2,
3087 P_ERROR_DESC VARCHAR2,
3088 P_ERROR_MODULE VARCHAR2) AS
3089
3090 PRAGMA AUTONOMOUS_TRANSACTION;
3091
3092 BEGIN
3093
3094 INSERT INTO QP_UPGRADE_ERRORS(ERROR_ID,UPG_SESSION_ID,ID1,ID2,ID3,ID4,ID5,ID6,ID7,ID8,ERROR_TYPE,
3095 ERROR_DESC,ERROR_MODULE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,
3096 LAST_UPDATED_BY,LAST_UPDATE_LOGIN) VALUES
3097 (QP_UPGRADE_ERRORS_S.NEXTVAL,USERENV('SESSIONID'),
3098 P_ID1,P_ID2,P_ID3,P_ID4,P_ID5,P_ID6,P_ID7,P_ID8,
3099 P_ERROR_TYPE, SUBSTR(P_ERROR_DESC,1,240),P_ERROR_MODULE,SYSDATE,
3100 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID);
3101 COMMIT;
3102
3103 END;
3104
3105
3106 PROCEDURE get_segs_for_flex( flexfield_name IN VARCHAR2,
3107 application_short_name IN VARCHAR2,
3108 x_segs_upg_t OUT NOCOPY v_segs_upg_tab,
3109 error_code OUT NOCOPY number) IS
3110
3111 CURSOR Cur_get_application_id(app_short_name VARCHAR2) IS
3112 SELECT application_id
3113 FROM fnd_application
3114 WHERE application_short_name = app_short_name;
3115
3116 v_context_dr fnd_dflex.contexts_dr;
3117 v_dflex_r fnd_dflex.dflex_r;
3118 v_context_r fnd_dflex.context_r;
3119 v_segments_dr fnd_dflex.segments_dr;
3120 v_value_set_id NUMBER;
3121 v_precedence NUMBER;
3122 v_valueset_r fnd_vset.valueset_r;
3123 v_format_dr fnd_vset.valueset_dr;
3124 v_valueset_dr fnd_vset.valueset_dr;
3125 v_dflex_dr fnd_dflex.dflex_dr;
3126 v_flexfield_val_ind NUMBER DEFAULT 0;
3127 J NUMBER := 0;
3128
3129 BEGIN
3130
3131 error_code := 0;
3132
3133 IF (flexfield_name IS NULL) THEN
3134 error_code := 1; -- flexfield_name is not passed.
3135 RETURN;
3136 END IF;
3137
3138 IF (application_short_name IS NULL) THEN
3139 error_code := 5; -- application short name is not passed
3140 RETURN;
3141 END IF;
3142
3143 -- Get the application_id
3144
3145 OPEN Cur_get_application_id(application_short_name);
3146 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
3147 IF (Cur_get_application_id%NOTFOUND) THEN
3148 CLOSE Cur_get_application_id;
3149 error_code := 6; -- Invalid application short name.
3150 RETURN;
3151 END IF;
3152 CLOSE Cur_get_application_id;
3153
3154 -- check if flexfield name passed is a valid one or not.
3155 v_flexfield_val_ind:= 1;
3156 fnd_dflex.get_flexfield(application_short_name,flexfield_name,v_dflex_r,v_dflex_dr);
3157
3158 -- Get the context listing for the flexfield
3159 fnd_dflex.get_contexts(v_dflex_r,v_context_dr);
3160
3161 For i in 1..v_context_dr.ncontexts LOOP
3162
3163 --dbms_output.put_line('context code -1 is : ' || v_context_dr.context_code(i));
3164
3165 v_context_r.context_code := v_context_dr.context_code(i);
3166 v_context_r.flexfield := v_dflex_r;
3167
3168
3169
3170 fnd_dflex.get_segments(v_context_r,v_segments_dr);
3171
3172 FOR K IN 1..v_segments_dr.nsegments LOOP
3173
3174 J := J + 1;
3175
3176 x_segs_upg_t(J).context_code := v_context_dr.context_code(i);
3177 x_segs_upg_t(J).segment_name := v_segments_dr.application_column_name(K);
3178
3179 -- Get value set information and validate the value passed.
3180
3181
3182 IF v_segments_dr.value_set(K) is null then
3183
3184 x_segs_upg_t(J).datatype := 'C';
3185
3186 ELSE
3187
3188 fnd_vset.get_valueset(v_segments_dr.value_set(K),v_valueset_r,v_valueset_dr);
3189 x_segs_upg_t(J).datatype := v_valueset_dr.format_type;
3190
3191 END IF;
3192
3193 x_segs_upg_t(J).sequence := v_segments_dr.sequence(K);
3194
3195
3196 END LOOP;
3197
3198 End Loop;
3199
3200 error_code := 0;
3201
3202 EXCEPTION
3203
3204 WHEN NO_DATA_FOUND THEN
3205 IF (v_flexfield_val_ind = 1) THEN
3206 error_code := 10;
3207 RETURN;
3208 END IF;
3209
3210 END get_segs_for_flex;
3211
3212 PROCEDURE get_segs_flex_precedence(p_segs_upg_t IN v_segs_upg_tab,
3213 p_context IN VARCHAR2,
3214 p_attribute IN VARCHAR2,
3215 x_precedence OUT NOCOPY NUMBER,
3216 x_datatype OUT NOCOPY VARCHAR2)
3217 IS
3218
3219
3220 v_segs_upg_ind number := 0;
3221
3222 BEGIN
3223
3224 x_datatype := 'C';
3225 x_precedence := 5000;
3226 v_segs_upg_ind := p_segs_upg_t.FIRST;
3227
3228 while v_segs_upg_ind is not null loop
3229
3230 IF ( ( p_segs_upg_t(v_segs_upg_ind).context_code = p_context )
3231 AND ( p_segs_upg_t(v_segs_upg_ind).segment_name = p_attribute )
3232 ) THEN
3233
3234 x_precedence := p_segs_upg_t(v_segs_upg_ind).sequence;
3235 x_datatype := p_segs_upg_t(v_segs_upg_ind).datatype;
3236 RETURN;
3237
3238 END IF;
3239
3240 v_segs_upg_ind := p_segs_upg_t.NEXT(v_segs_upg_ind);
3241
3242 end loop;
3243
3244 END get_segs_flex_precedence;
3245
3246 FUNCTION GET_NUM_DATE_FROM_CANONICAL(p_datatype IN VARCHAR2
3247 ,p_value IN VARCHAR2
3248 )RETURN VARCHAR2 IS
3249
3250 l_varchar_out varchar2(2000);
3251 INVALID_DATA_TYPE EXCEPTION;
3252
3253 BEGIN
3254
3255 IF p_datatype = 'N' THEN
3256
3257 l_varchar_out := to_char(qp_number.canonical_to_number(p_value));
3258 Elsif p_datatype = 'X' THEN
3259 l_varchar_out :=
3260 fnd_date.canonical_to_date(p_value);
3261 Elsif p_datatype = 'Y' THEN
3262 l_varchar_out :=
3263 fnd_date.canonical_to_date(p_value);
3264 Elsif p_datatype = 'C' THEN
3265 l_varchar_out := p_value;
3266 ELse
3267 l_varchar_out := p_value;
3268
3269 END IF;
3270
3271 RETURN l_varchar_out;
3272
3273 EXCEPTION
3274 When Others Then
3275 l_varchar_out := p_value;
3276
3277
3278 END GET_NUM_DATE_FROM_CANONICAL;
3279
3280 PROCEDURE GET_VALUESET_ID_R(P_FLEXFIELD_NAME IN VARCHAR2,
3281 P_CONTEXT IN VARCHAR2 ,
3282 P_SEG IN VARCHAR2 ,
3283 X_VSID OUT NOCOPY NUMBER,
3284 X_FORMAT_TYPE OUT NOCOPY VARCHAR2,
3285 X_VALIDATION_TYPE OUT NOCOPY VARCHAR2
3286 )IS
3287
3288 L_Valueset_R FND_VSET.VALUESET_R;
3289 X_VALUESETID NUMBER;
3290 L_valueset_dr FND_VSET.VALUESET_DR;
3291
3292 CURSOR Cur_get_application_id(app_short_name VARCHAR2) IS
3293 SELECT application_id
3294 FROM fnd_application
3295 WHERE application_short_name = app_short_name;
3296
3297 v_dflex_r fnd_dflex.dflex_r;
3298 v_segments_dr fnd_dflex.segments_dr;
3299 v_context_r fnd_dflex.context_r;
3300 BEGIN
3301 OPEN Cur_get_application_id('QP');
3302 FETCH Cur_get_application_id INTO v_dflex_r.application_id;
3303 CLOSE Cur_get_application_id;
3304 v_dflex_r.flexfield_name := p_flexfield_name;
3305 v_context_r.flexfield := v_dflex_r;
3306 v_context_r.context_code := p_context;
3307 -- Get the enabled segments for the context selected.
3308
3309 fnd_dflex.get_segments(v_context_r,v_segments_dr,TRUE);
3310 -- IF (v_segments_dr.nsegments > 0) THEN
3311
3312 FOR i IN 1..v_segments_dr.nsegments LOOP
3313 IF (v_segments_dr.application_column_name(i) = p_seg) THEN
3314 X_VALUESETID := v_SEGMENTS_dr.VALUE_SET(i);
3315 exit;
3316 END IF;
3317 END LOOP;
3318
3319 IF X_VALUESETID IS NOT NULL THEN
3320 FND_VSET.GET_VALUESET(X_VALUESETID,l_valueset_r,l_valueset_dr);
3321 X_VSID :=X_VALUESETID;
3322 X_FORMAT_TYPE :=l_valueset_dr.FORMAT_TYPE;
3323 X_VALIDATION_TYPE :=l_valueset_r.VALIDATION_TYPE;
3324 ELSE
3325 X_VSID :=NULL;
3326 X_FORMAT_TYPE :='C';
3327 X_VALIDATION_TYPE :=NULL;
3328
3329 END IF;
3330 end GET_VALUESET_ID_R;
3331
3332 FUNCTION Get_Attribute_Value_Meaning(p_FlexField_Name IN VARCHAR2
3333 ,p_Context_Name IN VARCHAR2
3334 ,p_segment_name IN VARCHAR2
3335 ,p_attr_value IN VARCHAR2
3336 ,p_comparison_operator_code IN VARCHAR2 := NULL
3337 ) RETURN VARCHAR2 IS
3338
3339 Vset FND_VSET.valueset_r;
3340 Fmt FND_VSET.valueset_dr;
3341
3342 Found BOOLEAN;
3343 Row NUMBER;
3344 Value FND_VSET.value_dr;
3345
3346
3347
3348 x_Format_Type Varchar2(1);
3349 x_Validation_Type Varchar2(1);
3350 x_Vsid NUMBER;
3351
3352
3353 x_attr_value_code VARCHAR2(240);
3354 x_attr_meaning VARCHAR2(1000);
3355 l_attr_value VARCHAR2(2000);
3356
3357
3358 Value_Valid_In_Valueset BOOLEAN := FALSE;
3359
3360 l_id VARCHAR2(240);
3361 l_value VARCHAR2(240);
3362 l_meaning VARCHAR2(1000);
3363
3364 BEGIN
3365
3366
3367 qp_util.get_valueset_id(p_FlexField_Name,p_Context_Name,
3368 p_Segment_Name,x_Vsid,
3369 x_Format_Type, x_Validation_Type);
3370
3371
3372 l_attr_value := get_num_date_from_canonical(x_format_type,p_attr_value);
3373
3374 -- if comparison operator is other than then no need to get the
3375 -- meaning as the value itself will be stored in qualifier_attr_value
3376
3377 --change made by spgopal. added parameter called p_comparison_operator_code
3378 --to generalise the code for all forms and packages
3379
3380 If p_comparison_operator_code <> 'BETWEEN' THEN
3381
3382 IF x_Validation_Type In('F' ,'I') AND x_Vsid IS NOT NULL THEN
3383 --dbms_output.put_line('valueset found');
3384
3385
3386 IF x_Validation_Type = 'I' THEN
3387 --dbms_output.put_line('validation type = I');
3388
3389 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3390 FND_VSET.get_value_init(Vset,TRUE);
3391 FND_VSET.get_value(Vset,Row,Found,Value);
3392
3393
3394 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3395 While(Found) Loop
3396 --dbms_output.put_line('ID is defined');
3397
3398
3399 If l_attr_value = Value.id Then
3400
3401 x_attr_value_code := Value.value;
3402 --dbms_output.put_line('1 x_attr_value_code = ' || x_attr_value_code);
3403 x_attr_meaning := Value.meaning;
3404 --dbms_output.put_line('1 x_attr_meaning = ' || x_attr_meaning);
3405 Value_Valid_In_Valueset := TRUE;
3406 EXIT;
3407 End If;
3408 FND_VSET.get_value(Vset,Row,Found,Value);
3409
3410 End Loop;
3411
3412 Else -- id not defined.Hence compare for value
3413
3414 While(Found) Loop
3415 --dbms_output.put_line('ID is not defined');
3416
3417 If l_attr_value = Value.value Then
3418
3419 x_attr_value_code := l_attr_value;
3420 --dbms_output.put_line('2 x_attr_value_code = ' || x_attr_value_code);
3421 x_attr_meaning := Value.meaning;
3422 --dbms_output.put_line('2 x_attr_meaning = ' || x_attr_meaning);
3423 Value_Valid_In_Valueset := TRUE;
3424 EXIT;
3425 End If;
3426 FND_VSET.get_value(Vset,Row,Found,Value);
3427
3428 End Loop;
3429
3430 End If; ---end of Fmt.Has_Id
3431
3432 FND_VSET.get_value_end(Vset);
3433
3434 ELSIF X_Validation_type = 'F' THEN
3435 --dbms_output.put_line('validation type = F');
3436
3437 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3438
3439 IF (QP_UTIL.value_exists_in_table(Vset.table_info,l_attr_value,l_id,l_value,l_meaning)) THEN
3440
3441
3442 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3443 --dbms_output.put_line('ID is defined');
3444 If l_attr_value = l_id Then
3445
3446 x_attr_value_code := l_value;
3447 --dbms_output.put_line('3 x_attr_value_code = ' || x_attr_value_code);
3448 x_attr_meaning := l_meaning;
3449 --dbms_output.put_line('3 x_attr_meaning = ' || x_attr_meaning);
3450 Value_Valid_In_Valueset := TRUE;
3451 End If;
3452 Else -- id not defined.Hence compare for value
3453 --dbms_output.put_line('ID is not defined');
3454 If l_attr_value = l_value Then
3455
3456 x_attr_value_code := l_attr_value;
3457 --dbms_output.put_line('4 x_attr_value_code = ' || x_attr_value_code);
3458 x_attr_meaning := l_meaning;
3459 --dbms_output.put_line('4 x_attr_meaning = ' || x_attr_meaning);
3460 Value_Valid_In_Valueset := TRUE;
3461 End If;
3462 End if; -- End of Fmt.Has_ID
3463
3464 ELSE
3465 Value_Valid_In_Valueset := FALSE;
3466 END IF;
3467
3468 END IF; --X_Validation_Type
3469
3470
3471 ELSE -- if validation type is not F or I or valueset id is null (not defined)
3472 --dbms_output.put_line('Value set ID is not found');
3473
3474 x_attr_value_code := l_attr_value;
3475 --dbms_output.put_line('5 x_attr_value_code = ' || x_attr_value_code);
3476 x_attr_meaning := l_attr_value;
3477 --dbms_output.put_line('5 x_attr_meaning = ' || x_attr_meaning);
3478
3479 END IF;
3480 ELSE -- if comparison operator is 'between'
3481
3482 x_attr_value_code := l_attr_value;
3483 --dbms_output.put_line('6 x_attr_value_code = ' || x_attr_value_code);
3484 x_attr_meaning := l_attr_value;
3485 --dbms_output.put_line('6 x_attr_meaning = ' || x_attr_meaning);
3486
3487 END IF;
3488
3489
3490 RETURN x_attr_meaning;
3491
3492 END Get_Attribute_Value_Meaning;
3493
3494 FUNCTION Get_Attribute_Value(p_FlexField_Name IN VARCHAR2
3495 ,p_Context_Name IN VARCHAR2
3496 ,p_segment_name IN VARCHAR2
3497 ,p_attr_value IN VARCHAR2
3498 ,p_comparison_operator_code IN VARCHAR2 := NULL
3499 ) RETURN VARCHAR2 IS
3500
3501 Vset FND_VSET.valueset_r;
3502 Fmt FND_VSET.valueset_dr;
3503
3504 Found BOOLEAN;
3505 Row NUMBER;
3506 Value FND_VSET.value_dr;
3507
3508
3509
3510 x_Format_Type Varchar2(1);
3511 x_Validation_Type Varchar2(1);
3512 x_Vsid NUMBER;
3513
3514
3515 x_attr_value_code VARCHAR2(240);
3516 l_attribute_code VARCHAR2(240);
3517 l_segment_name VARCHAR2(240);
3518 l_attr_value VARCHAR2(2000);
3519
3520
3521 Value_Valid_In_Valueset BOOLEAN := FALSE;
3522
3523 l_id VARCHAR2(240);
3524 l_value VARCHAR2(240);
3525
3526 BEGIN
3527 -- bug 3531203 - POST: QUALIFIER DESCRIPTION ON PRICE LIST MAINTENANCE PAGE SHOWS INTERNAL IDS
3528 -- call api qp_util.get_valueset_id as qp_util.get_valueset_id_r does not look into attribute management schema
3529
3530 /*qp_util.get_valueset_id_r(p_FlexField_Name,p_Context_Name,
3531 p_Segment_Name,x_Vsid,
3532 x_Format_Type, x_Validation_Type);*/
3533
3534 Get_Attribute_Code(p_FlexField_Name,p_Context_Name, p_Segment_Name,
3535 l_attribute_code,
3536 l_segment_name);
3537
3538 qp_util.get_valueset_id(p_FlexField_Name,p_Context_Name,
3539 l_Segment_Name,x_Vsid,
3540 x_Format_Type, x_Validation_Type);
3541
3542 l_attr_value := get_num_date_from_canonical(x_format_type,p_attr_value);
3543
3544 -- if comparison operator is other than then no need to get the
3545 -- meaning as the value itself will be stored in qualifier_attr_value
3546
3547 --change made by spgopal. added parameter called p_comparison_operator_code
3548 --to generalise the code for all forms and packages
3549
3550 If p_comparison_operator_code <> 'BETWEEN' THEN
3551
3552 IF x_Validation_Type In('F' ,'I') AND x_Vsid IS NOT NULL THEN
3553
3554
3555 IF x_Validation_Type = 'I' THEN
3556
3557 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3558 FND_VSET.get_value_init(Vset,TRUE);
3559 FND_VSET.get_value(Vset,Row,Found,Value);
3560
3561
3562 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3563 While(Found) Loop
3564
3565
3566 If l_attr_value = Value.id Then
3567
3568 x_attr_value_code := Value.value;
3569 Value_Valid_In_Valueset := TRUE;
3570 EXIT;
3571 End If;
3572 FND_VSET.get_value(Vset,Row,Found,Value);
3573
3574 End Loop;
3575
3576 Else -- id not defined.Hence compare for value
3577
3578 While(Found) Loop
3579
3580 If l_attr_value = Value.value Then
3581
3582 x_attr_value_code := l_attr_value;
3583 Value_Valid_In_Valueset := TRUE;
3584 EXIT;
3585 End If;
3586 FND_VSET.get_value(Vset,Row,Found,Value);
3587
3588 End Loop;
3589
3590 End If; ---end of Fmt.Has_Id
3591
3592 FND_VSET.get_value_end(Vset);
3593
3594 ELSIF X_Validation_type = 'F' THEN
3595
3596 FND_VSET.get_valueset(x_Vsid,Vset,Fmt);
3597
3598 IF (QP_UTIL.value_exists_in_table(Vset.table_info,l_attr_value,l_id,l_value)) THEN
3599
3600
3601 IF Fmt.Has_Id Then --id is defined.Hence compare for id
3602 If l_attr_value = l_id Then
3603
3604 x_attr_value_code := l_value;
3605 Value_Valid_In_Valueset := TRUE;
3606 End If;
3607 Else -- id not defined.Hence compare for value
3608 If l_attr_value = l_value Then
3609
3610 x_attr_value_code := l_attr_value;
3611 Value_Valid_In_Valueset := TRUE;
3612 End If;
3613 End if; -- End of Fmt.Has_ID
3614
3615 ELSE
3616 Value_Valid_In_Valueset := FALSE;
3617 END IF;
3618
3619 END IF; --X_Validation_Type
3620
3621
3622 ELSE -- if validation type is not F or I or valueset id is null (not defined)
3623
3624 x_attr_value_code := l_attr_value;
3625
3626 END IF;
3627 ELSE -- if comparison operator is 'between'
3628
3629 x_attr_value_code := l_attr_value;
3630
3631 END IF;
3632
3633
3634 RETURN x_attr_value_code;
3635
3636 END Get_Attribute_Value;
3637
3638 FUNCTION Get_Salesrep(p_salesrep_id IN NUMBER)
3639 RETURN VARCHAR2
3640 IS
3641
3642 l_name VARCHAR2(240);
3643
3644 CURSOR salesrep_cur(a_salesrep_id NUMBER)
3645 IS
3646 select name
3647 from ra_salesreps r
3648 where r.salesrep_id = a_salesrep_id;
3649
3650 BEGIN
3651 OPEN salesrep_cur(p_salesrep_id);
3652 FETCH salesrep_cur
3653 INTO l_name;
3654 CLOSE salesrep_cur;
3655
3656 RETURN l_name;
3657
3658 EXCEPTION
3659 WHEN OTHERS THEN
3660 CLOSE salesrep_cur;
3661
3662 END Get_Salesrep;
3663
3664 FUNCTION Get_Term(p_term_id IN NUMBER)
3665 RETURN VARCHAR2
3666 IS
3667
3668 l_name VARCHAR2(240);
3669
3670 CURSOR term_cur(a_term_id NUMBER)
3671 IS
3672 select t.name
3673 from ra_terms_b b ,ra_terms_tl t
3674 where b.term_id = a_term_id and
3675 b.term_id = t.term_id and
3676 t.language = userenv('LANG');
3677
3678 BEGIN
3679 OPEN term_cur(p_term_id);
3680 FETCH term_cur
3681 INTO l_name;
3682 CLOSE term_cur;
3683
3684 RETURN l_name;
3685
3686 EXCEPTION
3687 WHEN OTHERS THEN
3688 CLOSE term_cur;
3689
3690 END Get_Term;
3691
3692
3693 /***************************************************************************/
3694
3695
3696 -- ==========================================================================
3697 -- Function value_exists_in_table overloaded
3698 -- funtion type Private
3699 -- Returns BOOLEAN
3700 -- out parameters : None
3701 -- DESCRIPTION
3702 -- Searches for value if it exist by building dynamic query stmt when when valueset validation type is F
3703 -- the list populated by get_valueset call.
3704 -- ===========================================================================
3705
3706
3707 FUNCTION value_exists_in_table(p_table_r fnd_vset.table_r,
3708 p_value VARCHAR2,
3709 x_id OUT NOCOPY VARCHAR2,
3710 x_value OUT NOCOPY VARCHAR2,
3711 x_meaning OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
3712 v_selectstmt VARCHAR2(2000) ; --dhgupta changed length from 500 to 2000 for bug # 1888160
3713 v_cursor_id INTEGER;
3714 v_value VARCHAR2(150);
3715 /* julin (2271729) - increased size for UTF8 */
3716 v_meaning VARCHAR2(1000);
3717 v_id VARCHAR2(150);
3718 v_retval INTEGER;
3719 v_where_clause fnd_flex_validation_tables.additional_where_clause%type;
3720 v_cols VARCHAR2(1000);
3721 l_order_by VARCHAR2(1000);
3722 l_pos1 number;
3723 l_where_length number;
3724
3725 /* Added for 3210264 */
3726 type refcur is ref cursor;
3727 v_cursor refcur;
3728
3729 type valueset_cur_type is RECORD (
3730 valueset_value varchar2(150),
3731 valueset_id varchar2(150),
3732 valueset_meaning varchar2(1000)
3733 );
3734 valueset_cur valueset_cur_type;
3735
3736
3737 BEGIN
3738 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
3739
3740 --Commented out for 2621644
3741 --IF (p_table_r.id_column_name IS NOT NULL) THEN -- Bug 1982009
3742
3743 /* Added for 2492020 */
3744
3745 IF instr(UPPER(p_table_r.where_clause), 'ORDER BY') > 0 THEN
3746 l_order_by := substr(p_table_r.where_clause, instr(UPPER(p_table_r.where_clause), 'ORDER BY'));
3747 v_where_clause := replace(p_table_r.where_clause, l_order_by ,'');
3748 ELSE
3749 v_where_clause := p_table_r.where_clause;
3750 END IF;
3751
3752 -- if instr(upper(p_table_r.where_clause),'WHERE ') > 0 then --Commented out for 2492020
3753 IF instr(upper(v_where_clause),'WHERE') > 0 then --3839853 removed space in 'WHERE '
3754 --to include the id column name in the query
3755
3756 v_where_clause:= rtrim(ltrim(v_where_clause));
3757 l_pos1 := instr(upper(v_where_clause),'WHERE');
3758 l_where_length :=LENGTHB('WHERE');
3759 v_where_clause:= substr(v_where_clause,l_pos1+l_where_length);
3760
3761
3762 IF (p_table_r.id_column_name IS NOT NULL) THEN
3763 --included extra quotes for comparing varchar and num values in select
3764 /* Commented out for 2492020
3765 v_where_clause := replace(UPPER(p_table_r.where_clause)
3766 ,'WHERE '
3767 ,'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND ');
3768 */
3769 --v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' AND '||v_where_clause;--2492020
3770
3771 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val AND '||v_where_clause;--3210264
3772 ELSE
3773 /* Commented out for 2492020
3774 v_where_clause := replace(UPPER(p_table_r.where_clause)
3775 ,'WHERE '
3776 ,'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND ');
3777 */
3778 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' AND '||v_where_clause;--2492020
3779
3780 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val AND '||v_where_clause;--3210264
3781 END IF;
3782
3783 else
3784 IF v_where_clause IS NOT NULL THEN -- FP 115.88.1159.7
3785 IF (p_table_r.id_column_name IS NOT NULL) THEN
3786 /* Commented out for 2492020
3787 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
3788 */
3789 --Added for 2492020
3790 --v_where_clause := 'WHERE '||p_table_r.id_column_name||' = '''||p_value||''' '||v_where_clause;
3791
3792 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val AND'||v_where_clause;--3210264
3793 ELSE
3794 /* Commented out for 2492020
3795 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||UPPER(p_table_r.where_clause);
3796 */
3797 --Added for 2492020
3798 --v_where_clause := 'WHERE '||p_table_r.value_column_name||' = '''||p_value||''' '||v_where_clause;
3799
3800 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val AND'||v_where_clause;--3210264
3801 END IF;
3802
3803 /* added ELSE block for 3839853 */
3804 ELSE
3805
3806 IF (p_table_r.id_column_name IS NOT NULL) THEN
3807 v_where_clause := 'WHERE '||p_table_r.id_column_name||' = :p_val '||v_where_clause;
3808 ELSE
3809 v_where_clause := 'WHERE '||p_table_r.value_column_name||' = :p_val '||v_where_clause;
3810 END IF;
3811
3812 END IF;
3813 end if;
3814 /* Commented out for 2621644
3815 ELSE
3816 v_where_clause := p_table_r.where_clause;
3817 END IF;
3818 */
3819
3820 v_cols :=p_table_r.value_column_name;
3821
3822 -------------------
3823 --changes made by spgopal for performance problem
3824 --added out parameters to pass back id and value for given valueset id
3825 -------------------
3826
3827 IF (p_table_r.id_column_name IS NOT NULL) THEN
3828
3829 --
3830 -- to_char() conversion function is defined only for
3831 -- DATE and NUMBER datatypes.
3832 --
3833 IF (p_table_r.id_column_type IN ('D', 'N')) THEN
3834 v_cols := v_cols || ' , To_char(' || p_table_r.id_column_name || ')';
3835 ELSE
3836 v_cols := v_cols || ' , ' || p_table_r.id_column_name;
3837 END IF;
3838 ELSE
3839 v_cols := v_cols || ', NULL ';
3840 END IF;
3841
3842
3843 if p_table_r.meaning_column_name is not null then
3844 v_cols := v_cols||','||p_table_r.meaning_column_name;
3845 else
3846 null;
3847 v_cols := v_cols || ', NULL '; --Added for 3210264
3848 end if;
3849
3850 v_selectstmt := 'SELECT '||v_cols||' FROM '||p_table_r.table_name||' '||v_where_clause;
3851
3852 oe_debug_pub.add('select stmt2'||v_selectstmt);
3853
3854 ------------------
3855
3856 /*
3857 IF p_table_r.id_column_name is not null then
3858
3859 v_selectstmt := 'SELECT '||p_table_r.id_column_name||' FROM '||p_table_r.table_name||' '||v_where_clause;
3860
3861 ELSE
3862
3863 v_selectstmt := 'SELECT '||p_table_r.value_column_name||' FROM '||p_table_r.table_name||' '||p_table_r.where_clause;
3864
3865 END IF;
3866 */
3867
3868
3869 /* Added for 3210264 */
3870
3871 open v_cursor for v_selectstmt using p_value;
3872 fetch v_cursor into valueset_cur;
3873 IF v_Cursor%NOTFOUND THEN
3874 CLOSE v_cursor;
3875 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3876 RETURN FALSE;
3877 END IF;
3878 x_id := valueset_cur.valueset_id;
3879 x_value := valueset_cur.valueset_value;
3880 If valueset_cur.valueset_meaning is NOT NULL THEN
3881 x_meaning:= valueset_cur.valueset_meaning;
3882 Else
3883 x_meaning:= valueset_cur.valueset_value;
3884 End If;
3885 CLOSE v_cursor;
3886 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3887 RETURN TRUE;
3888
3889 /*
3890 -- parse the query
3891
3892 DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
3893 oe_debug_pub.add('after parse2');
3894 -- Bind the input variables
3895 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,1,v_value,150);
3896 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,2,v_id,150);
3897 if p_table_r.meaning_column_name IS NOT NULL THEN
3898 -- julin (2271729) - increased size for UTF8
3899 DBMS_SQL.DEFINE_COLUMN(v_cursor_id,3,v_meaning,1000);
3900 end if;
3901 v_retval := DBMS_SQL.EXECUTE(v_cursor_id);
3902 LOOP
3903 -- Fetch rows in to buffer and check the exit condition from the loop
3904 IF( DBMS_SQL.FETCH_ROWS(v_cursor_id) = 0) THEN
3905 EXIT;
3906 END IF;
3907 -- Retrieve the rows from buffer into PLSQL variables
3908 DBMS_SQL.COLUMN_VALUE(v_cursor_id,1,v_value);
3909 DBMS_SQL.COLUMN_VALUE(v_cursor_id,2,v_id);
3910 if p_table_r.meaning_column_name IS NOT NULL THEN
3911 DBMS_SQL.COLUMN_VALUE(v_cursor_id,3,v_meaning);
3912 end if;
3913
3914
3915 IF v_id IS NULL AND (p_value = v_value) THEN
3916 oe_debug_pub.add('id null, passing value'||p_value||','||v_value||' '||v_meaning);
3917 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3918 x_id := v_id;
3919 x_value := v_value;
3920 --added this to return meaning
3921 x_meaning := v_meaning;
3922 RETURN TRUE;
3923 ELSIF (p_value = v_id) THEN
3924 oe_debug_pub.add('id exists, passing id'||p_value||','||v_id||' '||v_meaning);
3925 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3926 x_id := v_id;
3927 x_value := v_value;
3928 --added this to return meaning
3929 if v_meaning is not null then
3930 x_meaning := v_meaning;
3931 else --if meaning not defined in vset, return value
3932 x_meaning := v_value;
3933 end if;
3934 RETURN TRUE;
3935 ELSE
3936 Null;
3937 oe_debug_pub.add('value does notmatch, continue search'||p_value||','||v_id);
3938 END IF;
3939 END LOOP;
3940 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3941 RETURN FALSE;
3942 */
3943 EXCEPTION
3944 WHEN OTHERS THEN
3945 oe_debug_pub.add('value_exists_in_table exception');
3946 DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
3947 RETURN FALSE;
3948 END value_exists_in_table;
3949
3950 PROCEDURE CORRECT_ACTIVE_DATES(p_active_date_first_type IN OUT NOCOPY VARCHAR2,
3951 p_start_date_active_first IN OUT NOCOPY DATE,
3952 p_end_date_active_first IN OUT NOCOPY DATE,
3953 p_active_date_second_type IN OUT NOCOPY VARCHAR2,
3954 p_start_date_active_second IN OUT NOCOPY DATE,
3955 p_end_date_active_second IN OUT NOCOPY DATE)
3956 is
3957 l_active_date_type_temp VARCHAR2(30);
3958 l_start_date_active_temp DATE;
3959 l_end_date_active_temp DATE;
3960 BEGIN
3961
3962 IF (p_active_date_first_type = 'ORD'
3963 AND p_active_date_second_type = 'ORD') THEN
3964 -- Make the second value NULL
3965 p_active_date_second_type := NULL;
3966 p_start_date_active_second := NULL;
3967 p_end_date_active_second := NULL;
3968
3969 ELSIF (p_active_date_first_type is NULL
3970 AND p_active_date_second_type = 'ORD') THEN
3971 -- Assign the second value to first and make the second NULL
3972 p_active_date_first_type := p_active_date_second_type;
3973 p_start_date_active_first := p_start_date_active_second;
3974 p_end_date_active_first := p_end_date_active_second;
3975
3976 p_active_date_second_type := NULL;
3977 p_start_date_active_second := NULL;
3978 p_end_date_active_second := NULL;
3979
3980 ELSIF p_active_date_first_type = 'SHIP' THEN
3981 IF p_active_date_second_type = 'ORD' THEN
3982 -- Swap the values of first and second
3983 l_active_date_type_temp := p_active_date_first_type;
3984 l_start_date_active_temp := p_start_date_active_first;
3985 l_end_date_active_temp := p_end_date_active_first;
3986
3987 p_active_date_first_type := p_active_date_second_type;
3988 p_start_date_active_first := p_start_date_active_second;
3989 p_end_date_active_first := p_end_date_active_second;
3990
3991 p_active_date_second_type := l_active_date_type_temp;
3992 p_start_date_active_second := l_start_date_active_temp;
3993 p_end_date_active_second := l_end_date_active_temp;
3994
3995 ELSIF p_active_date_second_type is NULL THEN
3996 -- Assign the first value to second and make the first NULL
3997 p_active_date_second_type := p_active_date_first_type;
3998 p_start_date_active_second := p_start_date_active_first;
3999 p_end_date_active_second := p_end_date_active_first;
4000
4001 p_active_date_first_type := NULL;
4002 p_start_date_active_first := NULL;
4003 p_end_date_active_first := NULL;
4004
4005 ELSIF p_active_date_second_type = 'SHIP' THEN
4006 -- Make the first NULL
4007 p_active_date_first_type := NULL;
4008 p_start_date_active_first := NULL;
4009 p_end_date_active_first := NULL;
4010 END IF;
4011 END IF;
4012
4013 END CORRECT_ACTIVE_DATES;
4014
4015 -- mkarya for bug 1728764, Prevent update of Trade Management Data in QP
4016 -- mkarya for bug 2442212, Prevent update of modifier if PTE does not match with Profile value
4017 -- New procedure created
4018 PROCEDURE Check_Source_System_Code
4019 ( p_list_header_id IN qp_list_headers_b.list_header_id%type
4020 , p_list_line_id IN qp_list_lines.list_line_id%type
4021 , x_return_status OUT NOCOPY VARCHAR2
4022 )
4023 is
4024 l_source_system_code qp_list_headers_b.source_system_code%type;
4025 l_list_type_code qp_list_headers_b.list_type_code%type;
4026 l_profile_source_system_code qp_list_headers_b.source_system_code%type;
4027 l_profile_pte_code qp_list_headers_b.pte_code%type;
4028 l_pte_code qp_list_headers_b.pte_code%type;
4029 BEGIN
4030 x_return_status := FND_API.G_RET_STS_SUCCESS;
4031
4032 if p_list_header_id is NOT NULL then
4033 select source_system_code
4034 , list_type_code
4035 , pte_code
4036 into l_source_system_code
4037 , l_list_type_code
4038 , l_pte_code
4039 from qp_list_headers_b
4040 where list_header_id = p_list_header_id;
4041 else
4042 select lh.source_system_code
4043 , lh.list_type_code
4044 , lh.pte_code
4045 into l_source_system_code
4046 , l_list_type_code
4047 , l_pte_code
4048 from qp_list_headers_b lh,
4049 qp_list_lines ll
4050 where ll.list_line_id = p_list_line_id
4051 and lh.list_header_id = ll.list_header_id;
4052 end if;
4053
4054 fnd_profile.get('QP_SOURCE_SYSTEM_CODE', l_profile_source_system_code);
4055
4056 IF qp_util.attrmgr_installed = 'Y' then
4057 fnd_profile.get('QP_PRICING_TRANSACTION_ENTITY', l_profile_pte_code);
4058 if (((l_pte_code <> l_profile_pte_code) or
4059 (l_source_system_code <> l_profile_source_system_code)
4060 )
4061 AND (l_list_type_code NOT IN ('AGR', 'PML', 'PRL'))
4062 ) then
4063
4064 x_return_status := FND_API.G_RET_STS_ERROR;
4065
4066 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4067 THEN
4068
4069 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_CHANGE_MODIFIER_PTE');
4070 FND_MESSAGE.SET_TOKEN('SOURCE_SYSTEM', l_source_system_code);
4071 FND_MESSAGE.SET_TOKEN('PTE_CODE', l_pte_code);
4072 OE_MSG_PUB.Add;
4073
4074 END IF;
4075 end if;
4076 else -- attribute manager not installed
4077 if ((l_source_system_code <> l_profile_source_system_code)
4078 AND (l_list_type_code NOT IN ('AGR', 'PML', 'PRL'))) then
4079
4080 x_return_status := FND_API.G_RET_STS_ERROR;
4081
4082 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4083 THEN
4084
4085 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_CHANGE_MODIFIER');
4086 FND_MESSAGE.SET_TOKEN('SOURCE_SYSTEM', l_source_system_code);
4087 OE_MSG_PUB.Add;
4088
4089 END IF;
4090 end if;
4091
4092 end if; -- check if attribute manager installed
4093 EXCEPTION
4094 when no_data_found then
4095 -- if the header/line record is yet to be created
4096 NULL;
4097 END Check_Source_System_Code;
4098
4099 --------------------------------------------------------------------------------
4100 -- This procedure is used in the post query to get attribute name for the
4101 -- corresponding pricing/product/qualifier_attribute. qualifier_attribute
4102 -- stores the 'column' corresponding to the flexfield segment.
4103 -- (For e.g the column for the segment 'agreement_name' is
4104 -- 'qualifier_attribute7'. What is shown on the screen is the window prompt
4105 -- for the segment. For e.g The context 'CUSTOMER' has segments like
4106 -- agreement_name,GSA,agreement_type etc. The window prompt for
4107 -- the segment agreement_name is 'Agreement Name'(UI Value) and the database
4108 -- value for this segement(value stored in qualifier_attribute) is the column
4109 -- name, which is QUALIFIER_ATTRIBUTE7 ,in this case.
4110 --------------------------------------------------------------------------------
4111 PROCEDURE Get_Attribute_Code(p_FlexField_Name IN VARCHAR2,
4112 p_Context_Name IN VARCHAR2,
4113 p_attribute IN VARCHAR2,
4114 x_attribute_code OUT NOCOPY VARCHAR2,
4115 x_segment_name OUT NOCOPY VARCHAR2)
4116 IS
4117
4118 Flexfield FND_DFLEX.dflex_r;
4119 Flexinfo FND_DFLEX.dflex_dr;
4120 Contexts FND_DFLEX.contexts_dr;
4121 segments FND_DFLEX.segments_dr;
4122 i BINARY_INTEGER;
4123
4124 VALID_ATTRIBUTE BOOLEAN := FALSE;
4125
4126 l_pte_code VARCHAR2(30);
4127 l_context_type VARCHAR2(30);
4128 l_error_code NUMBER;
4129
4130 CURSOR attribute_cur(a_context_type VARCHAR2, a_context_code VARCHAR2,
4131 a_pte_code VARCHAR2, a_attribute VARCHAR2)
4132 IS
4133 SELECT nvl(a.user_segment_name, a.seeded_segment_name),
4134 b.segment_code
4135 FROM qp_segments_tl a, qp_segments_b b,
4136 qp_prc_contexts_b c, qp_pte_segments d
4137 WHERE c.prc_context_type = a_context_type
4138 AND c.prc_context_code = a_context_code
4139 AND c.prc_context_id = b.prc_context_id
4140 AND b.segment_mapping_column = a_attribute
4141 AND b.segment_id = a.segment_id
4142 AND a.language = userenv('LANG')
4143 AND b.segment_id = d.segment_id
4144 AND d.pte_code = a_pte_code;
4145
4146
4147 BEGIN
4148
4149 IF Attrmgr_Installed = 'Y' THEN
4150
4151 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
4152
4153 IF l_pte_code IS NULL THEN
4154 l_pte_code := 'ORDFUL';
4155 END IF;
4156
4157 QP_UTIL.Get_Context_Type(p_flexfield_name, p_context_name,
4158 l_context_type, l_error_code);
4159 IF l_error_code = 0 THEN
4160
4161 OPEN attribute_cur(l_context_type, p_context_name,
4162 l_pte_code, p_attribute);
4163
4164 --Deliberately interchanged the output parameters to be consistent with
4165 --existing code(Before Attributes Manager)
4166 FETCH attribute_cur INTO x_attribute_code, x_segment_name;
4167
4168 IF attribute_cur%NOTFOUND then
4169 x_segment_name := p_attribute;
4170 x_attribute_code := p_attribute;
4171 END IF;
4172 CLOSE attribute_cur;
4173
4174 END IF; --If l_error_code = 0
4175
4176 ELSE
4177 /* Added for 2332139 */
4178 BEGIN
4179 select form_left_prompt,end_user_column_name
4180 INTO x_attribute_code,x_segment_name
4181 from FND_DESCR_FLEX_COL_USAGE_VL
4182 where APPLICATION_ID = 661 and
4183 DESCRIPTIVE_FLEXFIELD_NAME = p_FlexField_Name and
4184 DESCRIPTIVE_FLEX_CONTEXT_CODE = p_Context_Name and
4185 application_column_name = p_attribute and
4186 enabled_flag='Y';
4187
4188 EXCEPTION
4189 WHEN OTHERS THEN
4190 x_attribute_code := p_attribute;
4191 x_segment_name := NULL;
4192 END;
4193
4194 /* Commented out for 2332139 */
4195
4196 /*
4197
4198 FND_DFLEX.get_flexfield('QP',p_FlexField_Name,Flexfield,Flexinfo);
4199
4200 --removing the check for the enabled segments as well as per the upgrade
4201 --requirement. While upgrading ,there may be some segments which were
4202 --enabled in the past but disabled now. In such cases ,we still need to
4203 --show the data in the post query.
4204
4205 FND_DFLEX.get_segments(FND_DFLEX.make_context(Flexfield,p_Context_Name),
4206 segments,FALSE);
4207
4208 FOR i IN 1..segments.nsegments LOOP
4209
4210 --removing the check for the enabled segments as well as per the upgrade
4211 --requirement. While upgrading ,there may be some segments which were
4212 --enabled in the past but disabled now. In such cases ,we still need to
4213 --show the data in the post query.
4214 IF segments.is_enabled(i) THEN
4215
4216 IF segments.application_column_name(i) = p_attribute THEN
4217 x_attribute_code := segments.row_prompt(i);
4218 x_segment_name := segments.segment_name(i);
4219 EXIT;
4220 END IF;
4221
4222 END IF;
4223
4224 END LOOP;
4225 */
4226 END IF; --Attrmgr_Installed = 'Y'
4227
4228 END Get_Attribute_Code;
4229
4230 FUNCTION Get_Segment_Level(p_list_header_id IN NUMBER
4231 ,p_Context IN VARCHAR2
4232 ,p_attribute IN VARCHAR2
4233 )
4234 RETURN VARCHAR2
4235 is
4236 l_segment_level VARCHAR2(30);
4237
4238 BEGIN
4239
4240 select c.segment_level
4241 into l_segment_level
4242 from qp_prc_contexts_b a,
4243 qp_segments_b b,
4244 qp_pte_segments c,
4245 qp_list_headers_b d
4246 where a.prc_context_id = b.prc_context_id
4247 and b.segment_id = c.segment_id
4248 and c.pte_code = d.pte_code
4249 and d.list_header_id = p_list_header_id
4250 and a.prc_context_code = p_context
4251 and b.SEGMENT_MAPPING_COLUMN = p_attribute;
4252
4253 return(l_segment_level);
4254
4255 EXCEPTION
4256 WHEN OTHERS THEN
4257 return(NULL);
4258
4259 END Get_Segment_Level;
4260 -- ===========================================================================
4261
4262 /****************************************************************************
4263
4264 PROCEDURE Web_Create_Context_Lov
4265 --------- ----------------------
4266 Procedure for non-Forms(html) based UI's. This Procedure is similar to
4267 Create_Context_Lov except that the Contexts(context code and context_name)
4268 to be displayed in an LOV are returned in a pl/sql table of records.
4269
4270 Input Parameters:
4271 -----------------
4272 p_field_context - 'FACTOR', 'PRODUCT', 'GSA', etc. Pass NULL if irrelevant.
4273 p_context_type - Can be 'PRODUCT', 'PRICING_ATTRIBUTE', 'QUALIFIER' or NULL.
4274 If Attributes Manager is installed, NULL will cause contexts
4275 of types 'PRODUCT', 'QUALIFIER' and 'PRICING_ATTRIBUTE' to
4276 be returned. If Attributes Manager is not installed, then
4277 NULL causes contexts of types 'PRODUCT' and
4278 'PRICING_ATTRIBUTE' to be returned.
4279 p_check_enabled - Default is 'Y'. If 'Y', only enabled contexts will be
4280 returned. If 'N', all contexts will be returned.
4281 p_limits - Default is 'N'. If 'Y', limits-enabled contexts will be
4282 returned. If 'N', all contexts will be returned.
4283 p_list_line_type_code - Examples, 'FREIGHT_CHARGE', etc. Although the default
4284 is NULL, the actual/correct value must be passed to get the
4285 correct list of contexts returned.
4286
4287 Output Parameters:
4288 ------------------
4289 x_return_status - Indicates Success, Expected_error or Unexpected Error -
4290 values can be either FND_API.G_RET_STS_SUCCESS or
4291 FND_API.G_RET_STS_ERROR.
4292 x_context_out_tbl - Table of records where each record has 2 columns,
4293 context_code and context_name
4294
4295 ****************************************************************************/
4296
4297 PROCEDURE Web_Create_Context_Lov(
4298 p_field_context IN VARCHAR2,
4299 p_context_type IN VARCHAR2,
4300 p_check_enabled IN VARCHAR2,
4301 p_limits IN VARCHAR2,
4302 p_list_line_type_code IN VARCHAR2,
4303 x_return_status OUT NOCOPY VARCHAR2,
4304 x_context_out_tbl OUT NOCOPY CREATE_CONTEXT_OUT_TBL)
4305
4306 IS
4307
4308 Flexfield FND_DFLEX.dflex_r;
4309 Flexinfo FND_DFLEX.dflex_dr;
4310 Contexts FND_DFLEX.contexts_dr;
4311
4312 J Binary_Integer := 1;
4313
4314 l_pte_code VARCHAR2(30);
4315
4316 CURSOR context_cur(a_pte_code VARCHAR2, a_qp_status VARCHAR2,
4317 a_limits VARCHAR2, a_list_line_type_code VARCHAR2)
4318 IS
4319 SELECT a.prc_context_code,
4320 nvl(b.user_prc_context_name, b.seeded_prc_context_name)
4321 prc_context_name,
4322 a.enabled_flag, a.prc_context_type
4323 FROM qp_prc_contexts_b a, qp_prc_contexts_tl b
4324 WHERE a.prc_context_id = b.prc_context_id
4325 AND b.language = userenv('LANG')
4326 AND EXISTS (SELECT 'x'
4327 FROM qp_segments_b c, qp_pte_segments d
4328 WHERE d.pte_code = a_pte_code
4329 AND c.segment_id = d.segment_id
4330 AND c.prc_context_id = a.prc_context_id
4331 AND d.lov_enabled = 'Y'
4332 AND (a_limits = 'Y' AND d.limits_enabled = 'Y'
4333 OR
4334 a_limits <> 'Y')
4335 AND (a_qp_status = 'S' AND
4336 (c.availability_in_basic = 'Y' OR
4337 c.availability_in_basic = 'F' AND
4338 a_list_line_type_code = 'FREIGHT_CHARGE')
4339 OR
4340 a_qp_status <> 'S')
4341 );
4342
4343 l_flexfield_name VARCHAR2(30);
4344 l_qp_status VARCHAR2(1);
4345 l_add_flag BOOLEAN;
4346
4347 BEGIN
4348
4349 x_return_status := FND_API.G_RET_STS_SUCCESS;
4350
4351 IF Attrmgr_Installed = 'Y' THEN
4352
4353 --Get the PTE code
4354 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
4355
4356 IF l_pte_code IS NULL THEN
4357 l_pte_code := 'ORDFUL';
4358 END IF;
4359
4360 --Get QP Install Status
4361 l_qp_status := QP_UTIL.Get_QP_Status;
4362
4363 FOR l_rec IN context_cur(l_pte_code, l_qp_status, p_limits,
4364 p_list_line_type_code)
4365 LOOP
4366
4367 l_add_flag := FALSE; --initialize for each iteration of loop
4368
4369 IF (p_check_enabled = 'Y' AND
4370 l_rec.enabled_flag = 'Y'
4371 )
4372 OR
4373 p_check_enabled = 'N'
4374 THEN
4375
4376 IF (p_context_type IS NOT NULL AND
4377 l_rec.prc_context_type = p_context_type
4378 )
4379 OR
4380 p_context_type IS NULL
4381 THEN
4382
4383 IF p_field_context = 'PRODUCT' THEN
4384
4385 IF l_rec.prc_context_code = 'ITEM' THEN
4386 l_add_flag := TRUE;
4387 END IF;
4388
4389 ELSIF p_field_context = 'PRICING_ATTR' THEN
4390
4391 IF l_rec.prc_context_code <> 'VOLUME' THEN
4392 l_add_flag := TRUE;
4393 END IF;
4394
4395 ELSIF p_field_context = 'BASIC' THEN
4396
4397 IF l_rec.prc_context_code IN ('MODLIST', 'CUSTOMER') THEN
4398 l_add_flag := TRUE;
4399 END IF;
4400
4401 ELSIF p_field_context = 'LINE_BASIC' THEN
4402
4403 IF l_rec.prc_context_code IN ('ORDER', 'CUSTOMER') THEN
4404 l_add_flag := TRUE;
4405 END IF;
4406
4407 ELSIF p_field_context = 'GSA' THEN
4408
4409 IF l_rec.prc_context_code IN ('MODLIST', 'CUSTOMER') THEN
4410 l_add_flag := TRUE;
4411 END IF;
4412
4413 ELSIF p_field_context = 'FACTOR' THEN
4414
4415 IF l_rec.prc_context_type IN ('PRODUCT', 'PRICING_ATTRIBUTE') THEN
4416 l_add_flag := TRUE;
4417 END IF;
4418
4419 ELSE
4420
4421 l_add_flag := TRUE;
4422
4423 END IF;
4424
4425 END IF;
4426
4427 END IF;
4428
4429 IF l_add_flag THEN
4430 x_context_out_tbl(j).context_code := l_rec.prc_context_code;
4431 x_context_out_tbl(j).context_name := l_rec.prc_context_name;
4432 j:= j+1;
4433 END IF; --l_add_flag = TRUE
4434
4435 END LOOP; --End FOR Loop
4436
4437 ELSE
4438
4439 IF p_context_type IN ('PRODUCT', 'PRICING_ATTRIBUTE') OR
4440 p_context_type IS NULL
4441 THEN
4442 l_flexfield_name := 'QP_ATTR_DEFNS_PRICING';
4443 ELSIF p_context_type = 'QUALIFIER' THEN
4444 l_flexfield_name := 'QP_ATTR_DEFNS_QUALIFIER';
4445 END IF;
4446
4447 -- Call Flexapi to get contexts
4448 FND_DFLEX.get_flexfield('QP',l_flexfield_name,Flexfield,Flexinfo);
4449 FND_DFLEX.get_contexts(Flexfield,Contexts);
4450
4451 FOR i IN 1..Contexts.ncontexts LOOP
4452
4453 -- If (Contexts.is_enabled(i)) Then
4454 IF(Contexts.is_enabled(i) AND (NOT (Contexts.is_global(i)))) THEN
4455
4456 IF (p_field_context = 'PRODUCT' ) THEN
4457
4458 IF (Contexts.context_code(i) = 'ITEM') THEN
4459 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4460 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4461 j:= j+1;
4462 END IF;
4463
4464 ELSIF (p_field_context = 'PRICING_ATTR' ) THEN
4465
4466 IF (Contexts.context_code(i) = 'ITEM') OR
4467 (Contexts.context_code(i) = 'VOLUME') THEN
4468 NULL;
4469 ELSE
4470 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4471 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4472 j:= j+1;
4473 END IF;
4474
4475 ELSIF (p_field_context = 'BASIC' ) THEN
4476
4477 IF (Contexts.context_code(i) IN ('CUSTOMER', 'MODLIST')) THEN
4478 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4479 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4480 j:= j+1;
4481 END IF;
4482
4483 ELSIF (p_field_context = 'LINE_BASIC' ) THEN
4484
4485 IF (Contexts.context_code(i) IN ('CUSTOMER', 'ORDER')) THEN
4486 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4487 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4488 j:= j+1;
4489 END IF;
4490
4491 ELSIF (p_field_context = 'GSA' ) THEN
4492
4493 IF (Contexts.context_code(i) IN ('CUSTOMER', 'MODLIST')) THEN
4494 x_context_out_tbl(j).context_code := Contexts.context_code(i);
4495 x_context_out_tbl(j).context_name := Contexts.context_name(i);
4496 j:= j+1;
4497 END IF;
4498
4499 ELSE
4500
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
4505 END IF;
4506
4507 END IF;
4508
4509 END LOOP;
4510
4511 END IF; --If Attrmgr_Installed = 'Y'
4512
4513 EXCEPTION
4514 WHEN OTHERS THEN
4515 x_return_status := FND_API.G_RET_STS_ERROR;
4516 FND_MESSAGE.SET_NAME('QP','QP_ERR_IN_VALUESET_DEF');
4517 OE_MSG_PUB.Add;
4518
4519 END Web_Create_Context_Lov;
4520
4521
4522 /****************************************************************************
4523 PROCEDURE Web_Create_Attribute_Lov
4524 --------- ------------------------
4525 Procedure for non-Forms(html) based UI's. This Procedure is similar to
4526 Create_Attribute_Lov except that the Attributes to be displayed in an LOV are
4527 returned in a pl/sql table of records.
4528
4529 Input Parameters:
4530 -----------------
4531 p_context_code - The context_code whose Attributes are to be returned. Ex,
4532 p_context_type - Must be one of the following - 'PRODUCT',
4533 'PRICING_ATTRIBUTE' or 'QUALIFIER' and must correspond
4534 correctly to the correct the p_context_code being passed.
4535 p_check_enabled - Default is 'Y'. If 'Y', only enabled contexts will be
4536 returned. If 'N', all contexts will be returned.
4537 p_limits - Default is 'N'. If 'Y', limits-enabled contexts will be
4538 returned. If 'N', all contexts will be returned.
4539 p_list_line_type_code - Examples, 'FREIGHT_CHARGE', etc. Although the default
4540 is NULL, the actual/correct value must be passed to get the
4541 correct list of contexts returned.
4542 p_segment_level - can have the following possible values:
4543
4544 Value - Meaning
4545 ----- -------
4546 1 ORDER (Segments having segment_level equal to this)
4547 2 LINE (Segments having segment_level equal to this)
4548 3 BOTH (Segments having segment_level equal to this)
4549 4 ORDER or BOTH (Segments having segment_level equal to this)
4550 5 LINE or BOTH (Segments having segment_level equal to this)
4551 6 ORDER or LINE or BOTH (Segments having segment_level equal to this)
4552
4553 p_field_context - 'BASIC', 'LINE_BASIC', 'GSA', 'S','I','N' etc. In most cases,
4554 some value must be passed. For example, for PriceLists,
4555 Formulas related forms, pass 'S' if basic pricing , 'I' for
4556 advanced_pricing installed. Pass NULL if irrelevant(rare).
4557
4558 Output Parameters:
4559 ------------------
4560 x_return_status - Indicates Success, Expected_error or Unexpected Error -
4561 values can be either FND_API.G_RET_STS_SUCCESS or
4562 FND_API.G_RET_STS_ERROR.
4563 x_attribute_out_tbl - Table of records where each record has 5 columns -
4564 segment_mapping_column, segment_name,
4565 segment_code, precedence and valueset_id
4566
4567 ****************************************************************************/
4568
4569 PROCEDURE Web_Create_Attribute_Lov(
4570 p_context_code IN VARCHAR2,
4571 p_context_type IN VARCHAR2,
4572 p_check_enabled IN VARCHAR2,
4573 p_limits IN VARCHAR2,
4574 p_list_line_type_code IN VARCHAR2,
4575 p_segment_level IN NUMBER,
4576 p_field_context IN VARCHAR2,
4577 x_return_status OUT NOCOPY VARCHAR2,
4578 x_attribute_out_tbl OUT NOCOPY CREATE_ATTRIBUTE_OUT_TBL)
4579 IS
4580
4581 Flexfield FND_DFLEX.dflex_r;
4582 Flexinfo FND_DFLEX.dflex_dr;
4583 Contexts FND_DFLEX.contexts_dr;
4584 segments FND_DFLEX.segments_dr;
4585 i BINARY_INTEGER;
4586
4587 j Binary_Integer := 1;
4588
4589 QP_NO_SEGMENT EXCEPTION;
4590
4591 l_pte_code VARCHAR2(30);
4592
4593 CURSOR attribute_cur(a_context_type VARCHAR2, a_context_code VARCHAR2,
4594 a_pte_code VARCHAR2)
4595 IS
4596 SELECT a.segment_mapping_column,
4597 nvl(b.user_segment_name, b.seeded_segment_name) segment_name,
4598 a.segment_code, nvl(a.user_precedence, a.seeded_precedence) precedence,
4599 d.prc_context_code, d.prc_context_type, c.lov_enabled,
4600 a.availability_in_basic, c.limits_enabled, c.segment_level,
4601 nvl(a.user_valueset_id, a.seeded_valueset_id) valueset_id
4602 FROM qp_segments_b a, qp_segments_tl b,
4603 qp_pte_segments c, qp_prc_contexts_b d
4604 WHERE d.prc_context_type = a_context_type
4605 AND d.prc_context_code = a_context_code
4606 AND a.prc_context_id = d.prc_context_id
4607 AND a.segment_id = b.segment_id
4608 AND b.language = userenv('LANG')
4609 AND c.pte_code = a_pte_code
4610 AND c.segment_id = b.segment_id;
4611
4612 l_add_flag BOOLEAN;
4613 l_qp_status VARCHAR2(1);
4614 l_flexfield_name VARCHAR2(30);
4615
4616 NO_SEGMENT_FOUND BOOLEAN := TRUE;
4617
4618 BEGIN
4619
4620 x_return_status := FND_API.G_RET_STS_SUCCESS;
4621
4622 IF Attrmgr_Installed = 'Y' THEN
4623
4624 --Get PTE code from profile
4625 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY',l_pte_code);
4626
4627 IF l_pte_code IS NULL THEN
4628 l_pte_code := 'ORDFUL';
4629 END IF;
4630
4631 --Get QP Install Status
4632 l_qp_status := QP_UTIL.Get_QP_Status;
4633
4634 FOR l_rec IN attribute_cur(p_context_type,
4635 p_context_code, l_pte_code)
4636 LOOP
4637
4638 IF attribute_cur%rowcount = 1 THEN
4639 NO_SEGMENT_FOUND := FALSE;
4640 END IF;
4641
4642 l_add_flag := FALSE; --initialize for each iteration of loop
4643
4644 IF (p_check_enabled = 'Y' AND
4645 l_rec.lov_enabled = 'Y'
4646 )
4647 OR
4648 p_check_enabled = 'N'
4649 THEN
4650
4651 IF (l_qp_status = 'S' AND
4652 (l_rec.availability_in_basic = 'Y' OR
4653 l_rec.availability_in_basic = 'F' AND
4654 p_list_line_type_code = 'FREIGHT_CHARGE'
4655 )
4656 )
4657 OR
4658 l_qp_status <> 'S'
4659 THEN
4660
4661 IF (p_limits = 'Y' AND
4662 l_rec.limits_enabled = 'Y'
4663 )
4664 OR
4665 p_limits <> 'Y'
4666 THEN
4667
4668 IF (p_segment_level = 1 AND
4669 l_rec.segment_level = 'ORDER'
4670 )
4671 OR
4672 (p_segment_level = 2 AND
4673 l_rec.segment_level = 'LINE'
4674 )
4675 OR
4676 (p_segment_level = 3 AND
4677 l_rec.segment_level = 'BOTH'
4678 )
4679 OR
4680 (p_segment_level = 4 AND
4681 l_rec.segment_level IN ('ORDER', 'BOTH')
4682 )
4683 OR
4684 (p_segment_level = 5 AND
4685 l_rec.segment_level IN ('LINE', 'BOTH')
4686 )
4687 OR
4688 (p_segment_level = 6 AND
4689 l_rec.segment_level IN ('ORDER', 'LINE', 'BOTH')
4690 )
4691 THEN
4692
4693 IF (p_list_line_type_code = 'OID' AND
4694 p_field_context = 'PRICING_ATTR_GET'
4695 )
4696 OR
4697 p_field_context = 'GSA'
4698 THEN
4699
4700 IF l_rec.prc_context_code = 'ITEM' AND
4701 l_rec.segment_mapping_column = 'PRICING_ATTRIBUTE1'
4702 THEN
4703 l_add_flag := TRUE;
4704 END IF;
4705
4706 ELSIF p_field_context = 'BASIC' THEN
4707
4708 IF (l_rec.prc_context_code = 'CUSTOMER' AND
4709 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE1',
4710 'QUALIFIER_ATTRIBUTE2',
4711 'QUALIFIER_ATTRIBUTE5')
4712 )
4713 OR
4714 (l_rec.prc_context_code = 'MODLIST' AND
4715 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE4'
4716 )
4717 THEN
4718 l_add_flag := TRUE;
4719 END IF;
4720
4721 ELSIF p_field_context = 'GSA_QUALIFIER' THEN
4722
4723 IF (l_rec.prc_context_code = 'CUSTOMER' AND
4724 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE2',
4725 'QUALIFIER_ATTRIBUTE5')
4726 )
4727 OR
4728 (l_rec.prc_context_code = 'MODLIST' AND
4729 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE4'
4730 )
4731 THEN
4732 l_add_flag := TRUE;
4733 END IF;
4734
4735 ELSIF p_field_context = 'LINE_BASIC' THEN
4736
4737 IF (l_rec.prc_context_code = 'CUSTOMER' AND
4738 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE7',
4739 'QUALIFIER_ATTRIBUTE8')
4740 )
4741 OR
4742 (l_rec.prc_context_code = 'ORDER' AND
4743 l_rec.segment_mapping_column IN ('QUALIFIER_ATTRIBUTE9',
4744 'QUALIFIER_ATTRIBUTE12')
4745 )
4746 OR
4747 (l_rec.prc_context_code = 'VOLUME' AND
4748 l_rec.segment_mapping_column IN ('PRICING_ATTRIBUTE10',
4749 'PRICING_ATTRIBUTE12')
4750 )
4751 THEN
4752 l_add_flag := TRUE;
4753 END IF;
4754
4755 ELSIF p_field_context IN ('S','I','N') THEN
4756 -- These are possible values, that the Price Lists
4757 -- module can assign to the field_context parameter.
4758 IF NOT (l_rec.prc_context_code = 'MODLIST' AND
4759 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE4'
4760 )
4761 AND
4762 NOT (l_rec.prc_context_code = 'VOLUME' AND
4763 l_rec.segment_mapping_column = 'PRICING_ATTRIBUTE12'
4764 )
4765 AND
4766 NOT (l_rec.prc_context_code = 'VOLUME' AND
4767 l_rec.segment_mapping_column = 'QUALIFIER_ATTRIBUTE10'
4768 )
4769 THEN
4770 l_add_flag := TRUE;
4771 END IF;
4772
4773 ELSE
4774
4775 l_add_flag := TRUE;
4776
4777 END IF;
4778
4779 END IF; --If p_segment_level...
4780
4781 END IF; --If l_form_function...
4782
4783 END IF; --If l_qp_status...
4784
4785 END IF; --If p_check_enabled...
4786
4787 IF l_add_flag THEN
4788 x_attribute_out_tbl(j).segment_mapping_column :=
4789 l_rec.segment_mapping_column;
4790 x_attribute_out_tbl(j).segment_name := l_rec.segment_name;
4791 x_attribute_out_tbl(j).segment_code := l_rec.segment_code;
4792 x_attribute_out_tbl(j).precedence := l_rec.precedence;
4793 x_attribute_out_tbl(j).valueset_id := l_rec.valueset_id;
4794 j:= j+1;
4795 END IF; --l_add_flag = TRUE
4796
4797 END LOOP; --End FOR Loop
4798
4799 IF NO_SEGMENT_FOUND THEN
4800 RAISE QP_NO_SEGMENT;
4801 END IF;
4802
4803 ELSE
4804
4805 IF p_context_type IN ('PRODUCT', 'PRICING_ATTRIBUTE') THEN
4806 l_flexfield_name := 'QP_ATTR_DEFNS_PRICING';
4807 ELSIF p_context_type = 'QUALIFIER' THEN
4808 l_flexfield_name := 'QP_ATTR_DEFNS_QUALIFIER';
4809 END IF;
4810
4811 FND_DFLEX.get_flexfield('QP',l_flexfield_name,Flexfield,Flexinfo);
4812 FND_DFLEX.get_segments(FND_DFLEX.make_context(Flexfield,p_Context_Code),
4813 segments,TRUE);
4814
4815 IF segments.nsegments <> 0 THEN
4816
4817 FOR i IN 1..segments.nsegments LOOP
4818
4819 IF segments.is_enabled(i) THEN
4820 --fnd_message.debug(p_Context_Name);
4821
4822 NO_SEGMENT_FOUND := FALSE;
4823
4824 IF (p_list_line_type_code = 'OID' and
4825 p_field_context = 'PRICING_ATTR_GET')
4826 THEN
4827
4828 IF p_context_code = 'ITEM' AND /* Item Number */
4829 segments.application_column_name(i) = 'PRICING_ATTRIBUTE1'
4830 THEN
4831 x_attribute_out_tbl(j).segment_mapping_column :=
4832 segments.application_column_name(i);
4833 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4834 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4835 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4836 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4837 j:= j+1;
4838 END IF;
4839
4840 ELSIF p_field_context = 'GSA' THEN
4841
4842 IF p_context_code = 'ITEM' AND
4843 segments.application_column_name(i) = 'PRICING_ATTRIBUTE1'
4844 THEN
4845 x_attribute_out_tbl(j).segment_mapping_column :=
4846 segments.application_column_name(i);
4847 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4848 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4849 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4850 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4851 j:= j+1;
4852 END IF;
4853
4854 ELSIF p_field_context = 'BASIC' THEN
4855
4856 IF (p_context_code = 'CUSTOMER' AND
4857 segments.application_column_name(i) IN ('QUALIFIER_ATTRIBUTE1',
4858 'QUALIFIER_ATTRIBUTE2',
4859 'QUALIFIER_ATTRIBUTE5'))
4860 OR
4861 (p_context_code = 'MODLIST' AND
4862 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE4')
4863 THEN
4864 x_attribute_out_tbl(j).segment_mapping_column :=
4865 segments.application_column_name(i);
4866 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4867 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4868 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4869 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4870 j:= j+1;
4871 END IF;
4872
4873 ELSIF p_field_context = 'GSA_QUALIFIER' THEN
4874
4875 IF (p_context_code = 'CUSTOMER' AND
4876 segments.application_column_name(i) IN ('QUALIFIER_ATTRIBUTE2',
4877 'QUALIFIER_ATTRIBUTE5'))
4878 OR
4879 (p_context_code = 'MODLIST' AND
4880 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE4')
4881 THEN
4882 x_attribute_out_tbl(j).segment_mapping_column :=
4883 segments.application_column_name(i);
4884 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4885 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4886 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4887 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4888 j:= j+1;
4889 END IF;
4890
4891 ELSIF p_field_context = 'LINE_BASIC' THEN
4892
4893 IF (p_context_code = 'CUSTOMER' AND
4894 segments.application_column_name(i) IN
4895 ('QUALIFIER_ATTRIBUTE7', 'QUALIFIER_ATTRIBUTE8'))
4896 OR
4897 (p_context_code = 'ORDER' and
4898 segments.application_column_name(i) IN ('QUALIFIER_ATTRIBUTE9',
4899 'QUALIFIER_ATTRIBUTE12'))
4900 THEN
4901 x_attribute_out_tbl(j).segment_mapping_column :=
4902 segments.application_column_name(i);
4903 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4904 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4905 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4906 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4907 j:= j+1;
4908 ELSIF (p_context_code = 'VOLUME' AND
4909 segments.application_column_name(i) IN
4910 ('PRICING_ATTRIBUTE10', 'PRICING_ATTRIBUTE12')) THEN
4911 x_attribute_out_tbl(j).segment_mapping_column :=
4912 segments.application_column_name(i);
4913 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4914 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4915 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4916 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4917 j:= j+1;
4918 END IF;
4919
4920 ELSIF p_field_context IN ('S', 'I', 'N') THEN
4921 -- These are possible values, that the
4922 -- Price Lists module can assign to the basic_advanced paramter.
4923 IF NOT (p_context_code = 'MODLIST' AND
4924 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE4')
4925 AND NOT (p_context_code = 'VOLUME' AND
4926 segments.application_column_name(i) = 'PRICING_ATTRIBUTE12')
4927 AND NOT (p_context_code = 'VOLUME' AND
4928 segments.application_column_name(i) = 'QUALIFIER_ATTRIBUTE10')
4929 THEN
4930 -- Don't display qual attr 'Price List' in Price Lists form,
4931 -- don't display qual attr 'Order Amount'(also under 'Volume')
4932 -- and don't display Pricing Attr 'Item Amount' under 'Volume'
4933 -- Context(in the Price Breaks block of Price List form).
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 ELSE --Covers all the 'else' conditions for Modifiers module
4944 --fnd_message.debug(segments.row_prompt(i));
4945
4946 x_attribute_out_tbl(j).segment_mapping_column :=
4947 segments.application_column_name(i);
4948 x_attribute_out_tbl(j).segment_name := segments.row_prompt(i);
4949 x_attribute_out_tbl(j).segment_code := segments.segment_name(i);
4950 x_attribute_out_tbl(j).precedence := segments.sequence(i);
4951 x_attribute_out_tbl(j).valueset_id := segments.value_set(i);
4952 j:= j+1;
4953
4954 END IF;
4955
4956 END IF;
4957
4958 END LOOP;
4959
4960 IF NO_SEGMENT_FOUND THEN
4961 RAISE QP_NO_SEGMENT;
4962 END IF;
4963
4964 ELSE --- segments.nsegments = 0 ie no segments defined for the context.
4965
4966 RAISE QP_NO_SEGMENT;
4967
4968 END IF; ---- if segments.nsegments <> 0
4969
4970 END IF; --If Attrmgr_Installed = 'Y'
4971
4972 EXCEPTION
4973 WHEN QP_NO_SEGMENT THEN
4974 x_return_status := FND_API.G_RET_STS_ERROR;
4975 FND_MESSAGE.SET_NAME('QP','QP_NO_SEGMENTS_AVAILABLE');
4976 OE_MSG_PUB.Add;
4977
4978 END Web_Create_Attribute_Lov;
4979
4980
4981 FUNCTION Get_Item_Validation_Org RETURN NUMBER IS
4982 l_application_id NUMBER;
4983 l_inv_org_id NUMBER;
4984 BEGIN
4985 oe_debug_pub.add('Start G_ORGANIZATION_ID = '||G_ORGANIZATION_ID);
4986 /*
4987 commenting this code R12 per ER 4756750 to move the profile-based
4988 approach for all calling app and come up with a pricing parameter
4989 for the item validation Org per the ER
4990 l_application_id := fnd_global.resp_appl_id;
4991
4992 oe_debug_pub.add('l_application_id = '||l_application_id);
4993 if l_application_id in (201, 178) then -- oracle purchasing/iProcurement
4994 SELECT inventory_organization_id
4995 INTO l_inv_org_id
4996 --fix for bug 4776045 for MOAC
4997 --FROM financials_system_parameters;
4998 FROM FINANCIALS_SYSTEM_PARAMS_ALL
4999 where org_id = get_org_id;
5000
5001 oe_debug_pub.add('inv_org_id from financials_system_parameters = '||l_inv_org_id);
5002 G_ORGANIZATION_ID := l_inv_org_id;
5003 end if;--application_id
5004 if G_ORGANIZATION_ID is null then --above query did not return a value
5005 --because MO Default OU was not set or FSP is not set revert to QP profile
5006 */
5007 -- if G_ORGANIZATION_ID is null then
5008 l_inv_org_id := FND_PROFILE.Value('QP_ORGANIZATION_ID');
5009
5010 oe_debug_pub.add('inv_org_id from profile QP_ORGANIZATION_ID = '||l_inv_org_id);
5011 IF G_ORGANIZATION_ID IS NULL THEN
5012 G_ORGANIZATION_ID := l_inv_org_id;
5013 -- end if;
5014 end if; --if G_ORGANIZATION_ID is null
5015
5016 oe_debug_pub.add('End G_ORGANIZATION_ID = '||G_ORGANIZATION_ID);
5017
5018 return l_inv_org_id;
5019 EXCEPTION
5020 when no_data_found then
5021 oe_debug_pub.add('no data found exception in qp_util.Get_Item_Validation_Org');
5022 --fix for bug 4776045
5023 -- G_ORGANIZATION_ID := FND_PROFILE.Value('QP_ORGANIZATION_ID');
5024 -- return G_ORGANIZATION_ID;
5025 return null;
5026
5027 when others then
5028 oe_debug_pub.add('others exception in qp_util.Get_Item_Validation_Org, error is ' || SQLERRM);
5029 --fix for bug 4776045
5030 -- G_ORGANIZATION_ID := FND_PROFILE.Value('QP_ORGANIZATION_ID');
5031 -- return G_ORGANIZATION_ID;
5032 return null;
5033
5034 END Get_Item_Validation_Org;
5035
5036 --[prarasto] added for MOAC. Used by the engine to get the org id.
5037 FUNCTION get_org_id --[prarasto] changed function signature
5038 RETURN NUMBER IS
5039 l_context_org_id NUMBER;
5040 BEGIN
5041 l_context_org_id := MO_GLOBAL.get_current_org_id;
5042 -- 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
5043 IF l_context_org_id IS NOT NULL THEN
5044 --check if org context is set
5045 RETURN l_context_org_id; --MO_GLOBAL.get_current_org_id;
5046 ELSE
5047 RETURN MO_UTILS.get_default_org_id; --[prarasto] modified to get the default org id from MO_UTILS
5048 END IF;
5049 Exception
5050 When OTHERS Then
5051 return null;
5052 END get_org_id;
5053
5054
5055 --[prarasto] added for MOAC. Used by the engine for validating the org id
5056 FUNCTION validate_org_id (p_org_id NUMBER)
5057 RETURN VARCHAR2 IS
5058 l_dummy VARCHAR2(1);
5059 BEGIN
5060 /*
5061 SELECT 'X'
5062 INTO l_dummy
5063 FROM hr_operating_units hr
5064 WHERE hr.organization_id = p_org_id
5065 AND MO_GLOBAL.check_access(hr.organization_id) = 'Y';
5066 */
5067 l_dummy := MO_GLOBAL.check_access(p_org_id);
5068
5069 /*
5070 IF l_dummy = 'X' THEN
5071 RETURN 'Y';
5072 ELSE
5073 RETURN 'N';
5074 END IF;
5075 */
5076 RETURN l_dummy;
5077 EXCEPTION
5078 When OTHERS Then
5079 return 'N';
5080 END validate_org_id;
5081
5082 --added for moac used by PL/ML VOs to query OU
5083 FUNCTION Get_OU_Name(p_org_id IN NUMBER) RETURN VARCHAR2 IS
5084 l_operating_unit VARCHAR2(240);
5085 BEGIN
5086 IF p_org_id IS NOT NULL THEN
5087 select name
5088 into l_operating_unit
5089 from hr_operating_units
5090 where organization_id = p_org_id;
5091 END IF;
5092 return l_operating_unit;
5093 EXCEPTION
5094 When OTHERS Then
5095 return null;
5096 END Get_OU_Name;
5097
5098 --[prarasto] added for MOAC. Used by the engine for validating the org id
5099
5100 PROCEDURE get_pte_and_ss (p_list_header_id IN NUMBER,
5101 x_pte_code OUT NOCOPY VARCHAR2,
5102 x_source_system_code OUT NOCOPY VARCHAR2)
5103 IS
5104 BEGIN
5105 SELECT pte_code, source_system_code
5106 INTO x_pte_code, x_source_system_code
5107 FROM qp_list_headers_b
5108 WHERE list_header_id = p_list_header_id;
5109 EXCEPTION
5110 When OTHERS Then
5111 null;
5112 END get_pte_and_ss;
5113
5114
5115 -- =======================================================================
5116 -- FUNCTION is_seed_user
5117 -- Function type Public
5118 -- DESCRIPTION
5119 -- Returns a boolean corresponding to whether the current user is
5120 -- the DATAMERGE user
5121 -- =======================================================================
5122 FUNCTION is_seed_user RETURN BOOLEAN
5123 IS
5124 l_db_name VARCHAR2(9);
5125 l_dm_user_id NUMBER;
5126 BEGIN
5127 BEGIN
5128 -- Get database name
5129 SELECT name
5130 INTO l_db_name
5131 FROM v$database;
5132
5133 -- Get DATAMERGE User ID
5134 SELECT user_id
5135 INTO l_dm_user_id
5136 FROM fnd_user
5137 WHERE user_name = 'DATAMERGE';
5138
5139 EXCEPTION
5140 WHEN NO_DATA_FOUND THEN
5141 RETURN FALSE;
5142 END;
5143
5144 -- If the database is a seed DB and the user is DATAMERGE, return TRUE
5145 IF l_db_name LIKE 'SEED%' AND l_dm_user_id = fnd_global.user_id THEN
5146 RETURN TRUE;
5147 ELSE
5148 RETURN FALSE;
5149 END IF;
5150
5151 END is_seed_user;
5152
5153
5154 FUNCTION Validate_Item(p_product_context IN VARCHAR2,
5155 p_product_attribute IN VARCHAR2,
5156 p_product_attr_val IN VARCHAR2) RETURN VARCHAR2 IS
5157 l_appl_id NUMBER := FND_GLOBAL.RESP_APPL_ID;
5158 l_dummy VARCHAR2(30);
5159 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5160 BEGIN
5161
5162 if (p_product_context = 'ITEM')
5163 and (p_product_attribute = 'PRICING_ATTRIBUTE1') THEN
5164 begin
5165 SELECT 'VALID' INTO l_dummy
5166 FROM mtl_system_items_b
5167 where inventory_item_id = p_product_attr_val
5168 AND ((l_appl_id not in (178,201) and customer_order_flag = 'Y')
5169 or (l_appl_id in (178, 201) and NVL(PURCHASING_ENABLED_FLAG, 'N') ='Y'))
5170 and organization_id = Get_Item_Validation_Org;
5171 exception
5172 WHEN NO_DATA_FOUND THEN
5173 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR) THEN
5174 FND_MESSAGE.SET_NAME('QP','QP_ITEM_NOT_VALID');
5175 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_product_attr_val);
5176 OE_MSG_PUB.Add;
5177 RETURN FND_API.G_RET_STS_ERROR;
5178 END IF;
5179 RAISE FND_API.G_EXC_ERROR;
5180 WHEN OTHERS THEN
5181 null;
5182 RETURN FND_API.G_RET_STS_ERROR;
5183 end;
5184 end if;--(p_product_context =
5185
5186 RETURN l_return_status;
5187
5188 EXCEPTION
5189 WHEN OTHERS THEN
5190 null;
5191 END Validate_Item;
5192
5193 END QP_UTIL;
[Home] [Help]