[Home] [Help]
PACKAGE BODY: APPS.QP_ATTR_MAPPING_PUB
Source
1 PACKAGE BODY Qp_Attr_Mapping_Pub AS
2 /* $Header: QPXPSRCB.pls 120.14.12010000.4 2008/10/19 08:16:13 hmohamme ship $ */
3
4 g_schema VARCHAR2(30);
5 g_conc_mode VARCHAR2(1);
6 g_err_mesg VARCHAR2(2000);
7 line_number NUMBER := 0;
8 segment_ctr NUMBER := 0;
9
10 --fix for bug 2491269
11 G_PRICING_EVENT VARCHAR2(30);
12
13
14 l_debug VARCHAR2(3);
15
16
17 /* Private procedure to modify v_attr_scr string by substituting all occurences
18 of:
19 orig_hdr with new_hdr
20 orig_line with new_line
21 new_source is the package where the bulk structure is defined */
22
23 PROCEDURE HVOP_Bulkify_Structures (v_attr_src_string IN OUT NOCOPY VARCHAR2,
24 orig_hdr IN VARCHAR2,
25 new_hdr IN VARCHAR2,
26 orig_line IN VARCHAR2,
27 new_line IN VARCHAR2,
28 new_source IN VARCHAR2
29 )
30 AS
31
32 source_ptr NUMBER;
33 offset NUMBER;
34 nth_replace NUMBER;
35 Invalid_Attribute EXCEPTION;
36 PRAGMA EXCEPTION_INIT (Invalid_Attribute, -6550);
37 BEGIN
38
39 v_attr_src_string := REPLACE (REPLACE (v_attr_src_string, orig_hdr, new_hdr), orig_line, new_line);
40 nth_replace := 1;
41 source_ptr := INSTR (v_attr_src_string, new_source, 1, nth_replace);
42
43 WHILE source_ptr > 0
44 LOOP
45 offset := INSTR (v_attr_src_string, ',', source_ptr , 1);
46 EXIT WHEN offset = 0;
47
48 -- Check if attribute exists in HVOP structure
49 BEGIN
50 EXECUTE IMMEDIATE ('Declare test_attr ' || SUBSTR (v_attr_src_string, source_ptr, offset - source_ptr ) || '%type; Begin Null; End;');
51 EXCEPTION
52 WHEN Invalid_Attribute
53 THEN
54 put_line ('Invalid HVOP Attribute: ' || SUBSTR (v_attr_src_string, source_ptr, offset - source_ptr ));
55 v_attr_src_string := 'NULL';
56 RETURN;
57 WHEN OTHERS
58 THEN
59 NULL;
60 END;
61
62 v_attr_src_string := SUBSTR (v_attr_src_string, 1, offset - 1)
63 || '(i)'
64 || SUBSTR (v_attr_src_string, offset);
65 nth_replace := nth_replace + 1;
66 source_ptr := INSTR (v_attr_src_string, new_source, 1, nth_replace);
67 END LOOP;
68
69 IF source_ptr <> 0 THEN
70 offset := INSTR (v_attr_src_string, ')', source_ptr , 1);
71 IF offset = 0 THEN
72 offset := LENGTH(v_attr_src_string)+1;
73 END IF;
74
75 -- Check if attribute exists in HVOP structure
76 BEGIN
77 EXECUTE IMMEDIATE ('Declare test_attr ' || SUBSTR (v_attr_src_string, source_ptr, offset - source_ptr ) || '%type; Begin Null; End;');
78 EXCEPTION
79 WHEN Invalid_Attribute
80 THEN
81 put_line ('Invalid HVOP Attribute: ' || SUBSTR (v_attr_src_string, source_ptr, offset - source_ptr ));
82 v_attr_src_string := 'NULL';
83 RETURN;
84 WHEN OTHERS
85 THEN
86 NULL;
87 END;
88
89 v_attr_src_string := SUBSTR (v_attr_src_string, 1, offset - 1)
90 || '(i)'
91 || SUBSTR (v_attr_src_string, offset);
92 END IF;
93
94 END HVOP_Bulkify_Structures;
95
96
97 PROCEDURE Put_Line
98 (Text VARCHAR2)
99 IS
100 BEGIN
101
102 IF g_conc_mode IS NULL THEN
103
104 IF NVL(Fnd_Profile.value('CONC_REQUEST_ID'),0) <> 0 THEN
105 g_conc_mode := 'Y';
106 ELSE
107 g_conc_mode := 'N';
108 END IF;
109
110 END IF;
111
112 IF g_conc_mode = 'Y' THEN
113 Fnd_File.PUT_LINE(Fnd_File.LOG, Text);
114 END IF;
115
116 END Put_Line;
117
118 /* Bug#4509601 - Procedure added to print messages in concurrent program
119 output file.
120 */
121 PROCEDURE Print_Line
122 (Text VARCHAR2)
123 IS
124 BEGIN
125 IF g_conc_mode IS NULL THEN
126 IF NVL(Fnd_Profile.value('CONC_REQUEST_ID'),0) <> 0 THEN
127 g_conc_mode := 'Y';
128 ELSE
129 g_conc_mode := 'N';
130 END IF;
131 END IF;
132
133 IF g_conc_mode = 'Y' THEN
134 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, Text);
135 END IF;
136 END Print_Line;
137
138
139 PROCEDURE Init_Applsys_Schema
140 IS
141 l_app_info BOOLEAN;
142 l_status VARCHAR2(30);
143 l_industry VARCHAR2(30);
144 BEGIN
145
146 IF g_schema IS NULL THEN
147
148 l_app_info := Fnd_Installation.GET_APP_INFO
149 ('FND',l_status, l_industry, g_schema);
150
151 END IF;
152
153 END;
154
155 PROCEDURE New_Line
156 IS
157 BEGIN
158
159 line_number := line_number + 1;
160 ad_ddl.build_package(' ',line_number);
161 -- oe_debug_pub.add(' ');
162
163 END New_Line;
164
165 PROCEDURE COMMENT
166 ( p_comment IN VARCHAR2
167 , p_level IN NUMBER DEFAULT 1
168 )
169 IS
170 BEGIN
171
172 Text('-- '||p_comment,p_level);
173
174 END COMMENT;
175
176 PROCEDURE Break_Text
177 ( p_src_type IN VARCHAR2
178 , p_string IN VARCHAR2
179 )
180 IS
181
182 l_value_string VARCHAR2(2000) := p_string;
183 l_temp1 VARCHAR2(2000);
184 l_temp2 VARCHAR2(2000);
185 l_filler VARCHAR2(20) := ' ';
186
187 lp_position NUMBER := 0;
188 rp_position NUMBER := 0;
189 c_position NUMBER := 0;
190 s_position NUMBER := 0;
191 p NUMBER := 0;
192
193 BEGIN
194
195 IF p_src_type = 'API' THEN
196 Text('v_attr_value := ' , 3);
197 ELSIF p_src_type = 'API_MULTIREC' THEN
198 Text('v_attr_mvalue := ' , 3);
199 END IF;
200
201 WHILE LENGTH(l_value_string) > 200 LOOP
202
203 lp_position := 0;
204 rp_position := 0;
205 c_position := 0;
206 s_position := 0;
207 p := 0;
208
209 lp_position := INSTR(l_value_string,'(');
210 rp_position := INSTR(l_value_string,')');
211 c_position := INSTR(l_value_string,',');
212 s_position := INSTR(l_value_string,' ');
213
214 IF (lp_position > 0) AND (p = 0) THEN
215 p := lp_position;
216 ELSIF (c_position > 0) AND (p = 0) THEN
217 p:= c_position;
218 ELSIF (s_position > 0) AND (p = 0) THEN
219 p:= s_position;
220 ELSIF (rp_position > 0) AND (p = 0) THEN
221 p:= rp_position;
222 END IF;
223
224 IF (lp_position > 0) AND (lp_position <= 200) THEN
225 l_temp1 := SUBSTR(l_value_string,1,lp_position);
226 l_temp2 := SUBSTR(l_value_string,lp_position+1);
227 l_value_string := l_temp2;
228 Text(l_filler || l_temp1 , 3);
229 ELSIF (c_position > 0) AND (c_position <= 200) THEN
230 l_temp1 := SUBSTR(l_value_string,1,c_position);
231 l_temp2 := SUBSTR(l_value_string,c_position+1);
232 l_value_string := l_temp2;
233 Text(l_filler || l_temp1 , 3);
234 ELSIF (s_position > 0) AND (s_position <= 200) THEN
235 l_temp1 := SUBSTR(l_value_string,1,s_position);
236 l_temp2 := SUBSTR(l_value_string,s_position+1);
237 l_value_string := l_temp2;
238 Text(l_filler || l_temp1 , 3);
239 ELSIF (rp_position > 0) AND (rp_position <= 200) THEN
240 l_temp1 := SUBSTR(l_value_string,1,rp_position);
241 l_temp2 := SUBSTR(l_value_string,rp_position+1);
242 l_value_string := l_temp2;
243 Text(l_filler || l_temp1 , 3);
244 ELSE
245 l_temp1 := SUBSTR(l_value_string,1,p);
246 l_temp2 := SUBSTR(l_value_string,p+1);
247 l_value_string := l_temp2;
248 Text(l_filler || l_temp1 , 3);
249 END IF;
250 END LOOP;
251
252 IF LENGTH(l_value_string) > 0 THEN
253 Text(l_filler || l_value_string || ';' , 3);
254 END IF;
255 END Break_Text;
256
257
258 PROCEDURE Text
259 ( p_string IN VARCHAR2
260 , p_level IN NUMBER DEFAULT 1
261 )
262 IS
263 BEGIN
264
265 line_number := line_number + 1;
266 --dbms_output.put_line(LPAD(p_string,p_level*2+LENGTH(p_string)));
267 ad_ddl.build_package(LPAD(p_string,p_level*2+LENGTH(p_string)),line_number);
268 -- oe_debug_pub.add(LPAD(p_string,p_level*2+LENGTH(p_string)));
269
270 END text;
271
272 PROCEDURE Pkg_End
273 ( p_pkg_name IN VARCHAR2
274 , p_pkg_type IN VARCHAR2
275 )
276 IS
277
278 l_is_pkg_body VARCHAR2(30);
279 n NUMBER := 0;
280 l_pkg_name VARCHAR2(30);
281 l_new_pkg_name CONSTANT VARCHAR2(30) := 'QP_BUILD_SOURCING_PVT';
282 v_segment_id NUMBER;
283 v_count BINARY_INTEGER := 1;
284 CURSOR errors IS
285 SELECT line, text
286 FROM user_errors
287 WHERE name = UPPER(l_pkg_name)
288 AND TYPE = DECODE(p_pkg_type,'SPEC','PACKAGE',
289 'BODY','PACKAGE BODY');
290 BEGIN
291
292 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
293 -- end statement.
294 Text('END '||p_pkg_name||';',0);
295
296 -- Show errors.
297 IF p_pkg_type = 'BODY' THEN
298 l_is_pkg_body := 'TRUE';
299 ELSE
300 l_is_pkg_body := 'FALSE';
301 END IF;
302
303 PUT_LINE(
304 'Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
305 IF l_debug = Fnd_Api.G_TRUE THEN
306 Oe_Debug_Pub.ADD('Call AD_DDL to create '||p_pkg_type||' of package '||p_pkg_name);
307
308
309 END IF;
310 ad_ddl.create_package(applsys_schema => g_schema
311 ,application_short_name => 'QP'
312 ,package_name => p_pkg_name
313 ,is_package_body => l_is_pkg_body
314 ,lb => 1
315 ,ub => line_number);
316
317 -- if there were any errors when creating this package, print out
318 -- the errors in the log file
319 l_pkg_name := p_pkg_name;
320 FOR error IN errors LOOP
321 IF n= 0 THEN
322 PUT_LINE('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
323 --dbms_output.put_line('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
324 IF l_debug = Fnd_Api.G_TRUE THEN
325 Oe_Debug_Pub.ADD('ERROR in creating PACKAGE '||p_pkg_type||' :'||p_pkg_name);
326 END IF;
327 END IF;
328 PUT_LINE( 'LINE :'||error.line||' '||SUBSTR(error.text,1,200));
329 --dbms_output.put_line( 'LINE :'||error.line||' '||substr(error.text,1,200));
330 IF l_debug = Fnd_Api.G_TRUE THEN
331 Oe_Debug_Pub.ADD('LINE :'||error.line||' '||SUBSTR(error.text,1,200));
332 END IF;
333 n := 1;
334 END LOOP;
335
336 -- if there was an error in compiling the package, raise
337 -- an error
338 IF n > 0 THEN
339 --dbms_output.put_line('Raising Error now.....');
340 RAISE Fnd_Api.G_EXC_ERROR;
341 END IF;
342
343
344 --changes by spgopal 15-JUN-2001 for BUILD_SOURCE_TMP
345 IF n = 0
346 THEN
347 --no errors in the QP_BUILD_SOURCING_PVT_TMP
348 --now go ahead generate the package
349 --as QP_BUILD_SOURCING_PVT
350
351 PUT_LINE('PACKAGE '||p_pkg_type||' Name to :'
352 ||l_pkg_name||' compiled successfully ');
353
354 IF l_debug = Fnd_Api.G_TRUE THEN
355 Oe_Debug_Pub.ADD('PACKAGE '||p_pkg_type||' Name to :'
356 || l_pkg_name||' compiled successfully ');
357
358 END IF;
359 PUT_LINE('Now create PACKAGE '||p_pkg_type||' : '
360 ||l_new_pkg_name);
361
362 IF l_debug = Fnd_Api.G_TRUE THEN
363 Oe_Debug_Pub.ADD('Now create PACKAGE '||p_pkg_type||' : ' ||l_new_pkg_name);
364
365 END IF;
366 IF INSTR(ad_ddl.glprogtext(1),p_pkg_name) > 0
367 THEN
368 ad_ddl.glprogtext(1) :=
369 REPLACE(ad_ddl.glprogtext(1)
370 ,p_pkg_name
371 ,l_new_pkg_name);
372 PUT_LINE('First change : '
373 ||ad_ddl.glprogtext(1));
374
375 IF l_debug = Fnd_Api.G_TRUE THEN
376 Oe_Debug_Pub.ADD('First change : ' ||ad_ddl.glprogtext(1));
377
378 END IF;
379 ad_ddl.glprogtext(line_number) :=
380 REPLACE(ad_ddl.glprogtext(line_number)
381 ,p_pkg_name
382 ,l_new_pkg_name);
383 PUT_LINE('Second change : '
384 ||' '||ad_ddl.glprogtext(line_number));
385
386 IF l_debug = Fnd_Api.G_TRUE THEN
387 Oe_Debug_Pub.ADD('Second change : ' ||' '||ad_ddl.glprogtext(line_number));
388
389 END IF;
390 PUT_LINE('Trying to create PACKAGE '||p_pkg_type
391 ||' :'||l_new_pkg_name);
392
393 IF l_debug = Fnd_Api.G_TRUE THEN
394 Oe_Debug_Pub.ADD('Trying to create PACKAGE '||p_pkg_type
395 ||' :'||l_new_pkg_name);
396
397 END IF;
398 ad_ddl.create_package(applsys_schema => g_schema
399 ,application_short_name => 'QP'
400 ,package_name => l_new_pkg_name
401 ,is_package_body => l_is_pkg_body
402 ,lb => 1
403 ,ub => line_number);
404
405 l_pkg_name := l_new_pkg_name;
406
407 -- if there were any errors
408 -- when creating this package, print out
409 -- the errors in the log file
410 FOR error IN errors LOOP
411 IF n = 0 THEN
412 PUT_LINE('ERROR in creating PACKAGE '
413 ||p_pkg_type||' :'||l_pkg_name);
414
415 IF l_debug = Fnd_Api.G_TRUE THEN
416 Oe_Debug_Pub.ADD('ERROR in creating PACKAGE '
417 ||p_pkg_type||' :'||l_pkg_name);
418
419 END IF;
420 END IF;
421 PUT_LINE('LINE :'||error.line||' '
422 ||SUBSTR(error.text,1,200));
423
424 IF l_debug = Fnd_Api.G_TRUE THEN
425 Oe_Debug_Pub.ADD('LINE :'||error.line||' '
426 ||SUBSTR(error.text,1,200));
427 END IF;
428 n := 1;
429 END LOOP;
430
431 -- if there was an error in compiling the package, raise
432 -- an error
433 IF n > 0 THEN
434 RAISE Fnd_Api.G_EXC_ERROR;
435 END IF;
436 PUT_LINE('Generated PACKAGE '||p_pkg_type
437 ||' :'||l_new_pkg_name
438 ||' Successfully');
439
440 IF l_debug = Fnd_Api.G_TRUE THEN
441 Oe_Debug_Pub.ADD('Generated PACKAGE '||p_pkg_type
442 ||' :'||l_new_pkg_name ||' Successfully');
443 END IF;
444
445 v_count := 1;
446
447 UPDATE qp_pte_segments
448 SET sourcing_status = 'N',
449 used_in_setup = 'N'
450 WHERE sourcing_status = 'Y' OR used_in_setup = 'Y';
451 LOOP
452
453 EXIT WHEN v_count > G_Segment_Ctr.COUNT;
454 v_segment_id := G_Segment_Ctr(v_count);
455 UPDATE qp_pte_segments
456 SET sourcing_status = 'Y',
460 v_count := v_count + 1;
457 used_in_setup = 'Y'
458 WHERE segment_id = v_segment_id;
459
461
462 END LOOP;
463 COMMIT;
464 g_Segment_Ctr.DELETE;
465 Segment_Ctr := 0;
466 ELSE
467 NULL;
468 END IF;--instr
469 END IF;--n=0
470
471 EXCEPTION
472 WHEN Fnd_Api.G_EXC_ERROR THEN
473 RAISE Fnd_Api.G_EXC_ERROR;
474 WHEN OTHERS THEN
475 RAISE_APPLICATION_ERROR(-20000,SQLERRM||' '||ad_ddl.error_buf);
476 -- PUT_LINE('Iam into exception' ||ad_ddl.error_buf);
477 -- RAISE FND_API.G_EXC_ERROR;
478
479 END Pkg_End;
480
481 -- Generates the Package Header for the package SPEC and BODY
482
483 PROCEDURE Pkg_Header
484 ( p_pkg_name IN VARCHAR2
485 , p_pkg_type IN VARCHAR2
486 )
487 IS
488 header_string VARCHAR2(200);
489 BEGIN
490
491 -- Initialize line number
492 line_number := 0;
493
494 -- Define package.
495
496 IF p_pkg_type = 'BODY' THEN
497 Text ('CREATE or REPLACE PACKAGE BODY '||
498 p_pkg_name|| ' AS',0);
499 ELSE
500 Text ('CREATE or REPLACE PACKAGE '||
501 p_pkg_name|| ' AUTHID CURRENT_USER AS',0);
502 END IF;
503
504 -- $Header clause.
505 header_string := 'Header: QPXVBSTB.pls 115.0 '||SYSDATE||' 23:23:31 appldev ship ';
506 Text('/* $'||header_string||'$ */',0);
507 New_Line;
508
509 -- Copyright section.
510
511 COMMENT ( '',0 );
512 COMMENT (
513 'Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA',0);
514 COMMENT ( 'All rights reserved.',0);
515 COMMENT ( '',0);
516 COMMENT ( 'FILENAME',0);
517 COMMENT ( '',0);
518 COMMENT ( ' '||p_pkg_name,0);
519 COMMENT ( '',0);
520 COMMENT ( 'DESCRIPTION',0);
521 COMMENT ( '',0);
522 COMMENT ( ' '||INITCAP(p_pkg_type)||' of package '
523 ||p_pkg_name,0);
524 COMMENT ( '',0);
525 COMMENT ('NOTES',0);
526 COMMENT ( '',0);
527 COMMENT ('HISTORY',0);
528 COMMENT ( '',0);
529 COMMENT ( TO_CHAR(SYSDATE)||' Created',0);
530 COMMENT ( '',0);
531 New_Line;
532
533 -- Global constant holding package name.
534
535 IF p_pkg_type = 'BODY' THEN
536 COMMENT ( 'Global constant holding the package name',0);
537 -- Global constatnt holding the package name will not be added
538 /*
539 Text (RPAD('G_PKG_NAME',30)||'CONSTANT '||
540 'VARCHAR2(30) := '''||p_pkg_name||''';',0);
541 */
542 New_Line;
543 END IF;
544
545 END Pkg_Header;
546
547
548 PROCEDURE Create_Sourcing_Calls
549 ( p_request_type_code IN VARCHAR2
550 , p_pricing_type IN VARCHAR2
551 , p_HVOP_Call IN VARCHAR2
552 )
553
554 IS
555
556 v_db1 VARCHAR2(30);
557 v_db2 VARCHAR2(30);
558 v_is_used VARCHAR2(1);
559 v_condition_id VARCHAR2(30);
560 v_context_name VARCHAR2(240); --4932085, 4960278
561 l_context_name VARCHAR2(240); --4932085, 4960278
562 v_attribute_name VARCHAR2(240);
563 l_attribute_name VARCHAR2(240);
564 v_attribute_value VARCHAR2(240);
565 v_attribute_mvalue Qp_Attr_Mapping_Pub.t_MultiRecord;
566 v_attr_src_string VARCHAR2(2000);
567 v_src_type VARCHAR2(30);
568 l_src_type VARCHAR2(30);
569 v_src_api_pkg VARCHAR2(1000);
570 l_src_api_pkg VARCHAR2(1000);
571 v_src_api_fn VARCHAR2(1000);
572 l_src_api_fn VARCHAR2(1000);
573 v_src_profile_option VARCHAR2(30);
574 l_src_profile_option VARCHAR2(30);
575 v_src_system_variable VARCHAR2(30);
576 l_src_system_variable VARCHAR2(30);
577 v_src_sys_code VARCHAR2(30);
578 v_context_type VARCHAR2(30);
579 l_context_type VARCHAR2(30);
580 v_src_constant_value VARCHAR2(30);
581 l_src_constant_value VARCHAR2(30);
582 l_sourcing_level VARCHAR2(30);
583 l_value_string VARCHAR2(2000);
584 v_value_string VARCHAR2(2000);
585 l_segment_id NUMBER;
586 v_segment_id NUMBER;
587 v_count BINARY_INTEGER := 1;
588 j NUMBER;
589 source_ptr VARCHAR2(2000);
590 offset VARCHAR2(2000);
591
592 --Fix for bug 2491269
593 l_is_product VARCHAR2(1);
594 l_code_release_level CONSTANT VARCHAR2(30) := Qp_Code_Control.Get_Code_Release_Level;
595 l_context_type_processed VARCHAR2(30);
596
597 L_CHECK_ACTIVE_FLAG VARCHAR2(1);
598
599 CURSOR l_ctxts(p_request_type_code VARCHAR2, p_db1 VARCHAR2, p_db2 VARCHAR2) IS
600 SELECT
601 arules.attribute_code,
602 arules.src_type,
603 arules.src_api_pkg,
604 arules.src_api_fn,
605 arules.src_profile_option,
606 arules.src_system_variable_expr,
607 arules.src_constant_value,
608 condelem.value_string,
609 condelem.attribute_code
610 FROM
614 WHERE
611 oe_def_attr_condns aconds,
612 oe_def_condn_elems condelem,
613 oe_def_attr_def_rules arules
615 aconds.database_object_name IN (p_db1, p_db2)
616 AND condelem.condition_id = aconds.condition_id
617 AND condelem.attribute_code IN ('PRICING_CONTEXT', 'QUALIFIER_CONTEXT')
618 AND arules.attr_def_condition_id = aconds.attr_def_condition_id
619 --added this condition to look at enabled_flag to avoid duplicate sourcing due to
620 --OM changes to lct. enabled_flag is a new column introduced--spgopal
621 AND NVL(aconds.enabled_flag, 'Y') = 'Y'
622 AND EXISTS (SELECT 'x' FROM qp_price_req_sources prs,oe_def_condn_elems condelem1
623 WHERE condelem1.attribute_code = 'SRC_SYSTEM_CODE'
624 AND condelem1.value_string = prs.source_system_code
625 AND prs.request_type_code = p_request_type_code
626 AND condelem.condition_id = condelem1.condition_id);
627
628 CURSOR l_ctxts_new(p_request_type_code VARCHAR2, p_sourcing_level VARCHAR2) IS
629 SELECT qpseg.segment_mapping_column attribute_code,
630 qpsour.user_sourcing_type src_type,
631 qpsour.user_value_string value_string,
632 qpsour.segment_id,
633 qpcon.prc_context_code context_code,
634 qpcon.prc_context_type context_type,
635 '2' is_product
636 FROM
637 qp_segments_b qpseg,
638 qp_attribute_sourcing qpsour,
639 qp_prc_contexts_b qpcon,
640 qp_pte_request_types_b qpreq,
641 qp_pte_segments qppseg
642 WHERE
643 qpsour.segment_id = qpseg.segment_id
644 AND qpsour.attribute_sourcing_level = p_sourcing_level
645 AND qpsour.enabled_flag = 'Y'
646 AND qpsour.request_type_code = p_request_type_code
647 AND qpseg.prc_context_id = qpcon.prc_context_id
648 AND qpreq.request_type_code = qpsour.request_type_code
649 AND qppseg.pte_code = qpreq.pte_code
650 AND qppseg.segment_id = qpsour.segment_id
651 AND qppseg.sourcing_enabled = 'Y'
652 AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
653 AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'QUALIFIER')
654 UNION
655 SELECT qpseg.segment_mapping_column attribute_code,
656 qpsour.user_sourcing_type src_type,
657 qpsour.user_value_string value_string,
658 qpsour.segment_id,
659 qpcon.prc_context_code context_code,
660 qpcon.prc_context_type context_type,
661 '1' is_product
662 FROM
663 qp_segments_b qpseg,
664 qp_attribute_sourcing qpsour,
665 qp_prc_contexts_b qpcon,
666 qp_pte_request_types_b qpreq,
667 qp_pte_segments qppseg
668 WHERE
669 qpsour.segment_id = qpseg.segment_id
670 AND qpsour.attribute_sourcing_level = p_sourcing_level
671 AND qpsour.enabled_flag = 'Y'
672 AND qpsour.request_type_code = p_request_type_code
673 AND qpseg.prc_context_id = qpcon.prc_context_id
674 AND qpreq.request_type_code = qpsour.request_type_code
675 AND qppseg.pte_code = qpreq.pte_code
676 AND qppseg.segment_id = qpsour.segment_id
677 AND qppseg.sourcing_enabled = 'Y'
678 AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
679 AND qpcon.prc_context_type = 'PRODUCT'
680 ORDER BY is_product,attribute_code;
681
682 BEGIN
683
684 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
685 L_CHECK_ACTIVE_FLAG := NVL(Fnd_Profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
686 --fix for 2491269
687 Text('QP_ATTR_MAPPING_PUB.G_Product_Attr_Tbl.delete;',1);
688
689 IF NVL(G_ATTRMGR_INSTALLED,'N') = 'N' THEN
690
691 IF p_pricing_type = 'L' THEN
692 v_db1 := 'QP_LINE_PRICING_ATTRIBS_V';
693 v_db2 := 'QP_LINE_QUALIF_ATTRIBS_V';
694 ELSIF p_pricing_type = 'H' THEN
695 v_db1 := 'QP_HDR_PRICING_ATTRIBS_V';
696 v_db2 := 'QP_HDR_QUALIF_ATTRIBS_V';
697 END IF;
698
699 G_Sourced_Contexts_Tbl.DELETE;
700 v_count := 1;
701
702 OPEN l_ctxts(p_request_type_code,v_db1,v_db2);
703
704 LOOP
705
706 FETCH l_ctxts INTO
707 l_attribute_name,
708 l_src_type,
709 l_src_api_pkg,
710 l_src_api_fn,
711 l_src_profile_option,
712 l_src_system_variable,
713 l_src_constant_value ,
714 l_context_name,
715 l_context_type;
716 EXIT WHEN l_ctxts%NOTFOUND;
717
718 --oe_debug_pub.add('Attribute = ' || l_attribute_name);
719
720 --dbms_output.put_line ('#1');
721 v_is_used := 'N';
722
723 IF L_CHECK_ACTIVE_FLAG = 'N' THEN
724 IF (l_context_name = 'MODLIST') AND (l_attribute_name = 'QUALIFIER_ATTRIBUTE4') THEN
725
726 v_is_used := 'Y';
727
728 ELSE
729
730 BEGIN
731
732 SELECT 'Y'
733 INTO v_is_used
734 FROM qp_qualifiers
735 WHERE qualifier_context = l_context_name
736 AND qualifier_attribute = l_attribute_name
740 WHEN NO_DATA_FOUND THEN
737 AND ROWNUM < 2;
738
739 EXCEPTION
741
742 BEGIN
743 --Bug#5007983 START
744 SELECT 'Y'
745 INTO v_is_used
746 FROM QP_PRICE_FORMULA_LINES A
747 WHERE A.PRICING_ATTRIBUTE_CONTEXT = l_context_name
748 AND A.PRICING_ATTRIBUTE = l_attribute_name
749 AND EXISTS
750 (SELECT /*+ no_unest */ 'x'
751 FROM QP_LIST_LINES B
752 WHERE A.PRICE_FORMULA_ID = B.PRICE_BY_FORMULA_ID)
753 AND ROWNUM < 2;
754
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757
758 BEGIN
759 SELECT 'Y'
760 INTO v_is_used
761 FROM qp_price_formula_lines a
762 WHERE a.pricing_attribute_context = l_context_name
763 AND a.pricing_attribute = l_attribute_name
764 AND EXISTS
765 (SELECT /*+ no_unest */ 'x'
766 FROM qp_currency_details b
767 WHERE a.price_formula_id = b.price_formula_id
768 OR a.price_formula_id = b.markup_formula_id
769 )
770 AND ROWNUM < 2;
771 --Bug#5007983 END
772 EXCEPTION
773 WHEN NO_DATA_FOUND THEN
774
775 BEGIN
776
777 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
778 INTO v_is_used
779 FROM qp_pricing_attributes
780 WHERE pricing_attribute_context = l_context_name
781 AND pricing_attribute = l_attribute_name
782 AND ROWNUM < 2;
783
784 EXCEPTION
785 WHEN NO_DATA_FOUND THEN
786
787 BEGIN
788
789 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
790 INTO v_is_used
791 FROM qp_pricing_attributes
792 WHERE product_attribute_context = l_context_name
793 AND product_attribute = l_attribute_name
794 AND ROWNUM < 2;
795
796 EXCEPTION
797 WHEN NO_DATA_FOUND THEN
798 COMMENT('Attribute not used',0);
799 END;
800 END;
801 END;
802 END;
803 END;
804 END IF;
805 ELSIF L_CHECK_ACTIVE_FLAG = 'Y' THEN
806 IF (l_context_name = 'MODLIST') AND (l_attribute_name = 'QUALIFIER_ATTRIBUTE4') THEN
807
808 v_is_used := 'Y';
809
810 ELSE
811
812 BEGIN
813
814 SELECT 'Y'
815 INTO v_is_used
816 FROM qp_qualifiers
817 WHERE qualifier_context = l_context_name
818 AND qualifier_attribute = l_attribute_name
819 AND active_flag = 'Y'
820 AND ROWNUM < 2;
821
822 EXCEPTION
823 WHEN NO_DATA_FOUND THEN
824
825 BEGIN
826 --Changes made by rnayani Bug # 4960639 START
827 /**
828 SELECT 'Y'
829 INTO v_is_used
830 FROM qp_price_formula_lines a, qp_list_lines b, qp_list_headers_b c
831 WHERE a.pricing_attribute_context = l_context_name
832 AND a.pricing_attribute = l_attribute_name
833 AND a.price_formula_id = b.price_by_formula_id
834 AND b.list_header_id = c.list_header_id
835 AND c.active_flag = 'Y'
836 AND rownum < 2;
837 **/
838 SELECT 'Y'
839 INTO v_is_used
840 FROM qp_price_formula_lines a
841 WHERE a.pricing_attribute_context = l_context_name AND
842 a.pricing_attribute = l_attribute_name AND
843 EXISTS (SELECT 'x'
844 FROM qp_list_lines b, qp_list_headers_b c
845 WHERE a.price_formula_id = b.price_by_formula_id AND
846 b.list_header_id = c.list_header_id AND
847 c.active_flag = 'Y')
848 AND ROWNUM < 2;
849 --Changes made by rnayani Bug # 4960639 END
850
851 EXCEPTION
852 WHEN NO_DATA_FOUND THEN
853
854 BEGIN
855
856 --Bug#5007983 START
857 SELECT 'Y'
858 INTO v_is_used
859 FROM qp_price_formula_lines a, qp_currency_details b
860 WHERE a.pricing_attribute_context = l_context_name
861 AND a.pricing_attribute = l_attribute_name
862 AND EXISTS
863 (SELECT /*+ no_unest */ 'x'
867 )
864 FROM qp_currency_details b
865 WHERE a.price_formula_id = b.price_formula_id
866 OR a.price_formula_id = b.markup_formula_id
868 AND ROWNUM < 2;
869 --Bug#5007983 END
870
871 EXCEPTION
872 WHEN NO_DATA_FOUND THEN
873
874 BEGIN
875
876
877 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
878 INTO v_is_used
879 FROM qp_pricing_attributes
880 WHERE pricing_attribute_context = l_context_name
881 AND pricing_attribute = l_attribute_name
882 AND list_header_id IN
883 (SELECT list_header_id FROM qp_list_headers_b
884 WHERE active_flag = 'Y')
885 AND ROWNUM < 2;
886
887 EXCEPTION
888 WHEN NO_DATA_FOUND THEN
889
890 BEGIN
891
892 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
893 INTO v_is_used
894 FROM qp_pricing_attributes
895 WHERE product_attribute_context = l_context_name
896 AND product_attribute = l_attribute_name
897 AND list_header_id IN
898 (SELECT list_header_id FROM qp_list_headers_b
899 WHERE active_flag = 'Y')
900 AND ROWNUM < 2;
901
902 EXCEPTION
903 WHEN NO_DATA_FOUND THEN
904 BEGIN
905
906 SELECT 'Y'
907 INTO v_is_used
908 FROM qp_limits a, qp_list_headers_b b
909 WHERE ((a.multival_attr1_context = l_context_name
910 AND a.multival_attribute1 = l_attribute_name)
911 OR (a.multival_attr2_context = l_context_name
912 AND a.multival_attribute2 = l_attribute_name))
913 AND a.list_header_id = b.list_header_id
914 AND b.active_flag = 'Y'
915 AND ROWNUM < 2;
916
917 EXCEPTION
918 WHEN NO_DATA_FOUND THEN
919 BEGIN
920
921 SELECT 'Y'
922 INTO v_is_used
923 FROM qp_limit_attributes a, qp_limits b, qp_list_headers_b c
924 WHERE a.limit_attribute_context = l_context_name
925 AND a.limit_attribute = l_attribute_name
926 AND a.limit_id = b.limit_id
927 AND b.list_header_id = c.list_header_id
928 AND c.active_flag = 'Y'
929 AND ROWNUM < 2;
930
931 EXCEPTION
932 WHEN NO_DATA_FOUND THEN
933 COMMENT('Attribute not used',0);
934 END;
935 END;
936 END;
937 END;
938 END;
939 END;
940 END;
941 END IF;
942 END IF;
943 --oe_debug_pub.add('Enable Flag = ' || v_is_used);
944
945 --dbms_output.put_line ('#2');
946 IF v_is_used = 'Y' THEN
947
948 G_Sourced_Contexts_Tbl(v_count).attribute_name := l_attribute_name;
949 G_Sourced_Contexts_Tbl(v_count).src_type := l_src_type;
950 G_Sourced_Contexts_Tbl(v_count).src_api_pkg := l_src_api_pkg;
951 G_Sourced_Contexts_Tbl(v_count).src_api_fn := l_src_api_fn;
952 G_Sourced_Contexts_Tbl(v_count).src_profile_option := l_src_profile_option;
953 G_Sourced_Contexts_Tbl(v_count).src_system_variable := l_src_system_variable;
954 G_Sourced_Contexts_Tbl(v_count).src_constant_value := l_src_constant_value;
955 G_Sourced_Contexts_Tbl(v_count).context_name := l_context_name;
956 G_Sourced_Contexts_Tbl(v_count).context_type := l_context_type;
957
958 v_count := v_count + 1;
959
960 END IF;
961
962 END LOOP;
963
964
965 CLOSE l_ctxts;
966
967 --dbms_output.put_line ('#3');
968
969 Text('IF p_req_type_code = ''' || p_request_type_code || ''' THEN', 0);
970 New_Line;
971 Text('IF p_pricing_type_code = ''' || p_pricing_type || ''' THEN',1);
972 New_Line;
973 Text('NULL;',2);
974 New_Line;
975 --HVOP loop through all attributes
976 IF p_HVOP_call = 'Y' THEN
977 Text('--HVOP Call');
978 IF p_pricing_type = 'L' THEN
979 Text('FOR i in QP_BULK_PREQ_GRP.G_LINE_REC.line_id.first..QP_BULK_PREQ_GRP.G_LINE_REC.line_id.last');
980 --dbms_output.put_line('FOR i in QP_BULK_PREQ_GRP.G_LINE_REC.line_id.first..QP_BULK_PREQ_GRP.G_LINE_REC.line_id.last');
981 Text('Loop');
982 Text ('If QP_BULK_PREQ_GRP.G_LINE_REC.header_id(i) IS NOT NULL Then',2);
986 Text ('QP_PREQ_GRP.G_NEW_PRICING_CALL := ''Y'';',4);
983 Text ('If QP_BULK_PREQ_GRP.G_LINE_REC.header_id(i) = prev_header_id Then', 3);
984 Text ('QP_PREQ_GRP.G_NEW_PRICING_CALL := ''N'';', 4);
985 Text ('Else', 3);
987 Text ('prev_header_id := QP_BULK_PREQ_GRP.G_LINE_REC.header_id(i);', 4);
988 Text ('End If;', 3);
989 Text ('Else', 2);
990 Text ('If QP_PREQ_GRP.G_NEW_PRICING_CALL = ''Y'' Then',3);
991 Text ('QP_PREQ_GRP.G_NEW_PRICING_CALL := ''N'';', 4);
992 Text ('End If;', 3);
993 Text ('End If;' , 2);
994 ELSE
995 Text('FOR i in QP_BULK_PREQ_GRP.G_HEADER_REC.header_id.first..QP_BULK_
996 PREQ_GRP.G_HEADER_REC.header_id.last');
997 --dbms_output.put_line('FOR i in QP_BULK_PREQ_GRP.G_HEADER_REC.header_id.first..QP_BULK_PREQ_GRP.G_header_REC.header_id.last');
998 Text('Loop');
999 END IF;
1000 END IF;
1001 --HVOP loop through all attributes
1002 v_count := 0;
1003
1004 LOOP
1005
1006 v_count := v_count + 1;
1007
1008 EXIT WHEN v_count > G_Sourced_Contexts_Tbl.COUNT;
1009
1010 v_attribute_name := G_Sourced_Contexts_Tbl(v_count).attribute_name;
1011 v_src_type := G_Sourced_Contexts_Tbl(v_count).src_type;
1012 v_src_api_pkg := G_Sourced_Contexts_Tbl(v_count).src_api_pkg;
1013 v_src_api_fn := G_Sourced_Contexts_Tbl(v_count).src_api_fn;
1014 v_src_profile_option := G_Sourced_Contexts_Tbl(v_count).src_profile_option;
1015 v_src_system_variable := G_Sourced_Contexts_Tbl(v_count).src_system_variable;
1016 v_src_constant_value := G_Sourced_Contexts_Tbl(v_count).src_constant_value;
1017 v_context_name := G_Sourced_Contexts_Tbl(v_count).context_name;
1018 v_context_type := G_Sourced_Contexts_Tbl(v_count).context_type;
1019
1020 COMMENT('Src_Type: ' || v_src_type,0);
1021
1022 --dbms_output.put_line('v_context_type 1: ' || v_context_type);
1023
1024
1025 IF v_src_type = 'API' THEN
1026
1027 --dbms_output.put_line ('#5');
1028 v_attr_src_string := v_src_api_pkg || '.' || v_src_api_fn;
1029
1030 IF p_HVOP_call = 'Y' AND INSTR(v_attr_src_string, 'OE_ORDER_PUB.'
1031 ) > 0 THEN --HVOP, replace old structure references
1032
1033 HVOP_Bulkify_Structures ( v_attr_src_string,
1034 'OE_ORDER_PUB.G_HDR',
1035 'QP_BULK_PREQ_GRP.G_HEADER_REC',
1036 'OE_ORDER_PUB.G_LINE',
1037 'QP_BULK_PREQ_GRP.G_LINE_REC',
1038 'QP_BULK_PREQ_GRP'
1039 );
1040
1041 END IF;--p_HVOP_call
1042
1043 Text('BEGIN',2);
1044 --Text('v_attr_value := ' || v_attr_src_string || ';', 3);
1045 Break_Text(v_src_type,v_attr_src_string);
1046 Text('EXCEPTION',2);
1047 Text('WHEN OTHERS THEN',3);
1048 Text('v_attr_value := NULL;',4);
1049 Text('END;',2);
1050 New_Line;
1051 Text('BEGIN',2);
1052 Text('IF v_attr_value = FND_API.G_MISS_NUM THEN',2);
1053 Text('v_attr_value := NULL;',3);
1054 Text('END IF;',2);
1055 Text('EXCEPTION',2);
1056 Text('WHEN VALUE_ERROR THEN',3);
1057 Text('IF v_attr_value = FND_API.G_MISS_CHAR THEN',4);
1058 Text('v_attr_value := NULL;',5);
1059 Text('END IF;',4);
1060 Text('WHEN OTHERS THEN',3);
1061 Text('v_attr_value := NULL;',4);
1062 Text('END;',2);
1063 New_Line;
1064 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1065 New_Line;
1066
1067 END IF;
1068
1069 IF v_src_type = 'PROFILE_OPTION' THEN
1070
1071 v_attr_src_string := v_src_profile_option;
1072
1073
1074 Text('BEGIN',2);
1075 Text('v_attr_value := fnd_profile.value( ''' || v_attr_src_string || ''');', 3);
1076 Text('EXCEPTION',2);
1077 Text('WHEN OTHERS THEN',3);
1078 Text('v_attr_value := NULL;',4);
1079 Text('END;',2);
1080 New_Line;
1081 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1082 New_Line;
1083
1084 END IF;
1085
1086 IF v_src_type = 'SYSTEM' THEN
1087
1088 v_attr_src_string := v_src_system_variable;
1089 Text('BEGIN',2);
1090 Text('SELECT ' || v_attr_src_string || ' INTO v_attr_value FROM DUAL;', 3);
1091 Text('EXCEPTION',2);
1092 Text('WHEN OTHERS THEN',3);
1093 Text('v_attr_value := NULL;',4);
1094 Text('END;',2);
1095 New_Line;
1096 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1097 New_Line;
1098
1099 END IF;
1100
1101 IF v_src_type = 'CONSTANT' THEN
1102
1103 v_attr_src_string := v_src_constant_value;
1104 Text('v_attr_value := ''' || v_attr_src_string || ''';', 3);
1105 New_Line;
1106 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1107 New_Line;
1108
1109 END IF;
1110
1111 IF v_src_type <> 'API_MULTIREC' THEN
1112
1113 IF v_context_type = 'QUALIFIER_CONTEXT' THEN
1114
1115
1116 IF p_HVOP_call = 'Y' THEN --hvop
1117
1118 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1119 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) :=QP_BULK_PREQ_GRP.G_line_rec.line_index(i);');
1120 ELSE --header
1124 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) := ''' || v_context_name || ''';',3);
1121 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;');
1122 END IF; --hvop: pricing type decides line index
1123 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || 'QUALIFIER' || ''';', 3);
1125 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',3);
1126 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_value;',3);
1127 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 3);
1128 ------------------------put validated_flag code here-------
1129 IF v_context_name = 'MODLIST'
1130 AND
1131 v_attribute_name = 'QUALIFIER_ATTRIBUTE4'
1132 THEN
1133 Text ('If NVL(QP_BULK_PREQ_GRP.G_line_rec.agreement_id(i), FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM Then ', 3);
1134 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''Y'';', 4);
1135 Text ('End If;', 4);
1136 END IF;
1137
1138
1139
1140 ELSE
1141 Text('x_qual_ctxts_result_tbl(q_count).context_name := ''' || v_context_name || ''';',3);
1142 Text('x_qual_ctxts_result_tbl(q_count).attribute_name := ''' || v_attribute_name || ''';',3);
1143 Text('x_qual_ctxts_result_tbl(q_count).attribute_value := v_attr_value;',3);
1144 END IF; --hvop
1145
1146 Text('q_count := q_count + 1;',3);
1147 New_Line;
1148 Text('END IF;',2);
1149
1150
1151 ELSIF v_context_type = 'PRICING_CONTEXT' THEN
1152
1153 IF p_HVOP_call = 'Y' THEN --hvop
1154
1155 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1156 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := QP_BULK_PREQ_GRP.G_line_rec.line_index(i);',3);
1157 ELSE --header
1158 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;',3);
1159 END IF; --hvop: pricing type decides line index
1160
1161 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || 'PRICING' || ''';', 3);
1162 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) :=''' || v_context_name || ''';',3);
1163 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',3);
1164 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_value;',3);
1165 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 3);
1166 Text('q_count := q_count + 1;',3);
1167 ELSE
1168 Text('x_price_ctxts_result_tbl(p_count).context_name := ''' || v_context_name || ''';',3);
1169 Text('x_price_ctxts_result_tbl(p_count).attribute_name := ''' || v_attribute_name || ''';',3);
1170 Text('x_price_ctxts_result_tbl(p_count).attribute_value := v_attr_value;',3);
1171 Text('p_count := p_count + 1;',3);
1172 END IF;
1173 New_Line;
1174 Text('END IF;',2);
1175
1176 END IF;
1177
1178 END IF;
1179
1180 IF v_src_type = 'API_MULTIREC' THEN
1181
1182 v_attr_src_string := v_src_api_pkg || '.' || v_src_api_fn;
1183
1184 IF p_HVOP_call = 'Y' AND INSTR(v_attr_src_string, 'OE_ORDER_PUB.') > 0 THEN
1185 HVOP_Bulkify_Structures ( v_attr_src_string,
1186 'OE_ORDER_PUB.G_HDR',
1187 'QP_BULK_PREQ_GRP.G_HEADER_REC',
1188 'OE_ORDER_PUB.G_LINE',
1189 'QP_BULK_PREQ_GRP.G_LINE_REC',
1190 'QP_BULK_PREQ_GRP'
1191 );
1192 END IF;--p_HVOP_call
1193
1194 Text('BEGIN',2);
1195 IF v_attr_src_string = 'NULL' THEN
1196 Text('v_attr_mvalue(1) := ' || v_attr_src_string || ';', 3);
1197 ELSE
1198 Break_Text(v_src_type,v_attr_src_string);
1199 END IF;
1200 Text('EXCEPTION',2);
1201 Text('WHEN OTHERS THEN',3);
1202 Text('IF l_debug = FND_API.G_TRUE THEN',4);
1203 Text('oe_debug_pub.add(''Multirec API error'');',5);
1204 Text('END IF;',4);
1205 Text('END;',2);
1206 New_Line;
1207 Text('IF (v_attr_mvalue.count <> 0) AND (v_attr_mvalue(1) IS NOT NULL) THEN',2);
1208 Text('v_index := 1;',3);
1209 Text('LOOP',3);
1210
1211 IF v_context_type = 'QUALIFIER_CONTEXT' THEN
1212
1213 IF p_HVOP_call = 'Y' THEN --hvop
1214
1215 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1216 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := QP_BULK_PREQ_GRP.G_line_rec.line_index(i);',4);
1217 ELSE --header
1218 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;',4);
1219 END IF; --hvop: pricing type decides line index
1220
1221 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || 'QUALIFIER' || ''';', 4);
1222 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) := ''' || v_context_name || ''';',4);
1223 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',4);
1224 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_mvalue(v_index);',4);
1225 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 4);
1226 ------------------------put validated_flag code here-------
1227 IF v_context_name = 'MODLIST'
1228 AND
1229 v_attribute_name = 'QUALIFIER_ATTRIBUTE4'
1230 THEN
1234 END IF;
1231 Text ('If NVL(QP_BULK_PREQ_GRP.G_line_rec.agreement_id(i), FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM Then ', 3);
1232 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''Y'';', 4);
1233 Text ('End If;', 4);
1235
1236 ELSE
1237 Text('x_qual_ctxts_result_tbl(q_count).context_name := ''' || v_context_name || ''';',4);
1238 Text('x_qual_ctxts_result_tbl(q_count).attribute_name := ''' || v_attribute_name || ''';',4);
1239 Text('x_qual_ctxts_result_tbl(q_count).attribute_value := v_attr_mvalue(v_index);',4);
1240 END IF; --hvop
1241
1242 Text('q_count := q_count + 1;',4);
1243 New_Line;
1244
1245 ELSIF v_context_type = 'PRICING_CONTEXT' THEN
1246
1247 IF p_HVOP_call = 'Y' THEN --hvop
1248
1249 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1250 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := QP_BULK_PREQ_GRP.G_line_rec.line_index(i);',3);
1251 ELSE --header
1252 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;',4);
1253 END IF; --hvop: pricing type decides line index
1254
1255 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || 'PRICING' || ''';', 4);
1256 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) :=''' || v_context_name || ''';',4);
1257 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',4);
1258 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_mvalue(v_index);',4);
1259 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 4);
1260 Text('q_count := q_count + 1;',4);
1261 ELSE
1262 Text('x_price_ctxts_result_tbl(p_count).context_name := ''' || v_context_name || ''';',4);
1263 Text('x_price_ctxts_result_tbl(p_count).attribute_name := ''' || v_attribute_name || ''';',4);
1264 Text('x_price_ctxts_result_tbl(p_count).attribute_value := v_attr_mvalue(v_index);',4);
1265 Text('p_count := p_count + 1;',4);
1266 END IF;
1267 New_Line;
1268 END IF;
1269
1270 New_Line;
1271 Text('EXIT WHEN v_index = v_attr_mvalue.LAST;',4);
1272 Text('v_index := v_index + 1;',4);
1273 Text('END LOOP;',3);
1274 Text('END IF;',2);
1275
1276 END IF;
1277
1278 END LOOP;
1279
1280 Text('END IF;',1);
1281 New_Line;
1282 Text('END IF;',0);
1283 ELSIF NVL(G_ATTRMGR_INSTALLED,'N') = 'Y' THEN
1284
1285 IF p_pricing_type = 'L' THEN
1286 l_sourcing_level := 'LINE';
1287 ELSIF p_pricing_type = 'H' THEN
1288 l_sourcing_level := 'ORDER';
1289 END IF;
1290
1291 G_New_Sourced_Contexts_Tbl.DELETE;
1292 v_count := 1;
1293
1294 OPEN l_ctxts_new(p_request_type_code,l_sourcing_level);
1295
1296 LOOP
1297
1298 FETCH l_ctxts_new INTO
1299 l_attribute_name,
1300 l_src_type,
1301 l_value_string,
1302 l_segment_id,
1303 l_context_name,
1304 l_context_type,
1305 l_is_product;
1306 EXIT WHEN l_ctxts_new%NOTFOUND;
1307
1308
1309 v_is_used := 'N';
1310 IF L_CHECK_ACTIVE_FLAG = 'N' THEN
1311 IF (l_context_name = 'MODLIST') AND (l_attribute_name = 'QUALIFIER_ATTRIBUTE4') THEN
1312
1313 v_is_used := 'Y';
1314
1315 ELSE
1316
1317 BEGIN
1318
1319 SELECT 'Y'
1320 INTO v_is_used
1321 FROM qp_qualifiers
1322 WHERE qualifier_context = l_context_name
1323 AND qualifier_attribute = l_attribute_name
1324 AND ROWNUM < 2;
1325
1326 EXCEPTION
1327 WHEN NO_DATA_FOUND THEN
1328
1329 BEGIN
1330
1331 --Bug#5007983 START
1332 SELECT 'Y'
1333 INTO v_is_used
1334 FROM qp_price_formula_lines a
1335 WHERE a.pricing_attribute_context = l_context_name
1336 AND a.pricing_attribute = l_attribute_name
1337 AND EXISTS
1338 (SELECT /*+ no_unest */ 'x'
1339 FROM qp_list_lines b
1340 WHERE a.price_formula_id = b.price_by_formula_id
1341 )
1342 AND ROWNUM < 2;
1343 --Bug#5007983 END
1344
1345 EXCEPTION
1346 WHEN NO_DATA_FOUND THEN
1347
1348 BEGIN
1349
1350 --Bug#5007983 START
1351 SELECT 'Y'
1352 INTO v_is_used
1353 FROM qp_price_formula_lines a
1354 WHERE a.pricing_attribute_context = l_context_name
1355 AND a.pricing_attribute = l_attribute_name
1356 AND EXISTS
1357 (SELECT /*+ no_unest */ 'x'
1358 FROM qp_currency_details b
1359 WHERE a.price_formula_id = b.price_formula_id
1360 OR a.price_formula_id = b.markup_formula_id
1361 )
1362 AND ROWNUM < 2;
1363 --Bug#5007983 END
1364
1368 BEGIN
1365 EXCEPTION
1366 WHEN NO_DATA_FOUND THEN
1367 --bug 7494395
1369 SELECT 'Y'
1370 INTO v_is_used
1371 FROM qp_currency_details
1372 WHERE curr_attribute_type = l_context_type
1373 AND curr_attribute_context = l_context_name
1374 AND curr_attribute = l_attribute_name
1375 AND rownum < 2;
1376
1377 EXCEPTION
1378 WHEN no_data_found then
1379 --bug 7494395
1380 BEGIN
1381
1382 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
1383 INTO v_is_used
1384 FROM qp_pricing_attributes
1385 WHERE pricing_attribute_context = l_context_name
1386 AND pricing_attribute = l_attribute_name
1387 AND ROWNUM < 2;
1388
1389 EXCEPTION
1390 WHEN NO_DATA_FOUND THEN
1391
1392 BEGIN
1393
1394 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
1395 INTO v_is_used
1396 FROM qp_pricing_attributes
1397 WHERE product_attribute_context = l_context_name
1398 AND product_attribute = l_attribute_name
1399 AND ROWNUM < 2;
1400
1401 EXCEPTION
1402 WHEN NO_DATA_FOUND THEN
1403 BEGIN
1404 SELECT 'Y'
1405 INTO v_is_used
1406 FROM qp_list_lines
1407 WHERE ((break_uom_context = l_context_name
1408 AND break_uom_attribute = l_attribute_name)
1409 OR
1410 (accum_context = l_context_name
1411 AND accum_attribute = l_attribute_name)
1412 )
1413 AND ROWNUM < 2;
1414
1415 EXCEPTION
1416 WHEN NO_DATA_FOUND THEN
1417 COMMENT('Attribute not used',0);
1418 END;
1419 END;
1420 END;
1421 END;
1422 END;
1423 END; --bug 7494395
1424 END;
1425 END IF;
1426 --dbms_output.put_line ('L_CHECK_ACTIVE_FLAG : ' || L_CHECK_ACTIVE_FLAG);
1427 ELSIF L_CHECK_ACTIVE_FLAG = 'Y' THEN
1428 --dbms_output.put_line ('L_CHECK_ACTIVE_FLAG : ' || L_CHECK_ACTIVE_FLAG);
1429 IF (l_context_name = 'MODLIST') AND (l_attribute_name = 'QUALIFIER_ATTRIBUTE4') THEN
1430
1431 v_is_used := 'Y';
1432
1433 ELSE
1434
1435 BEGIN
1436
1437 SELECT 'Y'
1438 INTO v_is_used
1439 FROM qp_qualifiers
1440 WHERE qualifier_context = l_context_name
1441 AND qualifier_attribute = l_attribute_name
1442 AND active_flag = 'Y'
1443 AND ROWNUM < 2;
1444
1445 EXCEPTION
1446 WHEN NO_DATA_FOUND THEN
1447
1448 BEGIN
1449
1450 --Changes made by rnayani Bug # 4960639 START
1451 /**
1452 SELECT 'Y'
1453 INTO v_is_used
1454 FROM qp_price_formula_lines a, qp_list_lines b, qp_list_headers_b c
1455 WHERE a.pricing_attribute_context = l_context_name
1456 AND a.pricing_attribute = l_attribute_name
1457 AND a.price_formula_id = b.price_by_formula_id
1458 AND b.list_header_id = c.list_header_id
1459 AND c.active_flag = 'Y'
1460 AND rownum < 2;
1461 **/
1462 SELECT 'Y'
1463 INTO v_is_used
1464 FROM qp_price_formula_lines a
1465 WHERE a.pricing_attribute_context = l_context_name AND
1466 a.pricing_attribute = l_attribute_name AND
1467 EXISTS (SELECT /*+ no_unest */ 'x'
1468 FROM qp_list_lines b, qp_list_headers_b c
1469 WHERE a.price_formula_id = b.price_by_formula_id AND
1470 b.list_header_id = c.list_header_id AND
1471 c.active_flag = 'Y')
1472 AND ROWNUM < 2;
1473 --Changes made by rnayani Bug # 4960639 END
1474
1475 EXCEPTION
1476 WHEN NO_DATA_FOUND THEN
1477
1478 BEGIN
1479
1480 --Bug#5007983 START
1481 SELECT 'Y'
1482 INTO v_is_used
1483 FROM qp_price_formula_lines a
1484 WHERE a.pricing_attribute_context = l_context_name
1485 AND a.pricing_attribute = l_attribute_name
1486 AND EXISTS
1490 OR a.price_formula_id = b.markup_formula_id
1487 (SELECT /*+ no_unest */ 'x'
1488 FROM qp_currency_details b
1489 WHERE a.price_formula_id = b.price_formula_id
1491 )
1492 AND ROWNUM < 2;
1493 --Bug#5007983 START
1494
1495 EXCEPTION
1496 WHEN NO_DATA_FOUND THEN
1497 --bug 7494395
1498 BEGIN
1499 SELECT 'Y'
1500 INTO v_is_used
1501 FROM qp_currency_details
1502 WHERE curr_attribute_type = l_context_type
1503 AND curr_attribute_context = l_context_name
1504 AND curr_attribute = l_attribute_name
1505 AND rownum < 2;
1506
1507 EXCEPTION
1508 WHEN no_data_found then
1509 --bug 7494395
1510 BEGIN
1511
1512 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
1513 INTO v_is_used
1514 FROM qp_pricing_attributes
1515 WHERE pricing_attribute_context = l_context_name
1516 AND pricing_attribute = l_attribute_name
1517 AND list_header_id IN
1518 (SELECT list_header_id FROM qp_list_headers_b
1519 WHERE active_flag = 'Y')
1520 AND ROWNUM < 2;
1521
1522 EXCEPTION
1523 WHEN NO_DATA_FOUND THEN
1524
1525 BEGIN
1526
1527 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n1) */ 'Y'
1528 INTO v_is_used
1529 FROM qp_pricing_attributes
1530 WHERE product_attribute_context = l_context_name
1531 AND product_attribute = l_attribute_name
1532 AND list_header_id IN
1533 (SELECT list_header_id FROM qp_list_headers_b
1534 WHERE active_flag = 'Y')
1535 AND ROWNUM < 2;
1536
1537 EXCEPTION
1538 WHEN NO_DATA_FOUND THEN
1539 BEGIN
1540
1541 SELECT 'Y'
1542 INTO v_is_used
1543 FROM qp_limits a, qp_list_headers_b b
1544 WHERE ((a.multival_attr1_context = l_context_name
1545 AND a.multival_attribute1 = l_attribute_name)
1546 OR (a.multival_attr2_context = l_context_name
1547 AND a.multival_attribute2 = l_attribute_name))
1548 AND a.list_header_id = b.list_header_id
1549 AND b.active_flag = 'Y'
1550 AND ROWNUM < 2;
1551
1552 EXCEPTION
1553 WHEN NO_DATA_FOUND THEN
1554 BEGIN
1555
1556 SELECT 'Y'
1557 INTO v_is_used
1558 FROM qp_limit_attributes a, qp_limits b, qp_list_headers_b c
1559 WHERE a.limit_attribute_context = l_context_name
1560 AND a.limit_attribute = l_attribute_name
1561 AND a.limit_id = b.limit_id
1562 AND b.list_header_id = c.list_header_id
1563 AND c.active_flag = 'Y'
1564 AND ROWNUM < 2;
1565
1566
1567 EXCEPTION
1568 WHEN NO_DATA_FOUND THEN
1569 BEGIN
1570 SELECT 'Y'
1571 INTO v_is_used
1572 FROM qp_list_lines a, qp_list_headers_b b
1573 WHERE ((a.break_uom_context = l_context_name
1574 AND a.break_uom_attribute = l_attribute_name)
1575 OR
1576 (a.accum_context = l_context_name
1577 AND a.accum_attribute = l_attribute_name)
1578 )
1579 AND a.list_header_id = b.list_header_id
1580 AND b.active_flag = 'Y'
1581 AND ROWNUM < 2;
1582
1583 EXCEPTION
1584 WHEN NO_DATA_FOUND THEN
1585 COMMENT('Attribute not used',0);
1586 END;
1587 END;
1588 END;
1589 END;
1590 END;
1591 END;
1592 END;
1593 END;
1594 END; --bug 7494395
1595 END IF;
1596 END IF;
1597 --oe_debug_pub.add('Enable Flag = ' || v_is_used);
1598 --dbms_output.put_line('Enable Flag = ' || v_is_used);
1599 IF v_is_used = 'Y' THEN
1603 G_New_Sourced_Contexts_Tbl(v_count).value_string := l_value_string;
1600 segment_ctr := segment_ctr + 1;
1601 G_New_Sourced_Contexts_Tbl(v_count).attribute_name := l_attribute_name;
1602 G_New_Sourced_Contexts_Tbl(v_count).src_type := l_src_type;
1604 G_New_Sourced_Contexts_Tbl(v_count).context_name := l_context_name;
1605 G_New_Sourced_Contexts_Tbl(v_count).context_type := l_context_type;
1606
1607 G_Segment_Ctr(segment_ctr) := l_segment_id;
1608
1609 v_count := v_count + 1;
1610 END IF;
1611
1612 END LOOP;
1613
1614 --oe_debug_pub.add('Sourced contexts = ' || G_New_Sourced_Contexts_Tbl.COUNT);
1615
1616 CLOSE l_ctxts_new;
1617
1618
1619 Text('IF p_req_type_code = ''' || p_request_type_code || ''' THEN', 0);
1620 New_Line;
1621 Text('IF p_pricing_type_code = ''' || p_pricing_type || ''' THEN',1);
1622 New_Line;
1623 Text('NULL;',2);
1624 New_Line;
1625 --HVOP
1626 IF p_HVOP_call = 'Y' THEN
1627 Text('--HVOP Call');
1628 IF p_pricing_type = 'L' THEN
1629 Text('FOR i in QP_BULK_PREQ_GRP.G_LINE_REC.line_id.first..QP_BULK_PREQ_GRP.G_LINE_REC.line_id.last');
1630 --dbms_output.put_line('FOR i in QP_BULK_PREQ_GRP.G_LINE_REC.line_id.first..QP_BULK_PREQ_GRP.G_LINE_REC.line_id.last');
1631 Text('Loop');
1632 Text ('--oe_debug_pub.add (''prev_header_id: ''|| prev_header_id);');
1633 Text ('--oe_debug_pub.add (''this_header_id: ''|| nvl(QP_BULK_PREQ_GRP.G_LINE_REC.header_id(i),0));');
1634 Text ('If QP_BULK_PREQ_GRP.G_LINE_REC.header_id(i) IS NOT NULL Then',2
1635 );
1636 Text ('If QP_BULK_PREQ_GRP.G_LINE_REC.header_id(i) = prev_header_id Then', 3);
1637 Text ('QP_PREQ_GRP.G_NEW_PRICING_CALL := ''N'';', 4);
1638 Text ('Else', 3);
1639 Text ('QP_PREQ_GRP.G_NEW_PRICING_CALL := ''Y'';',4);
1640 Text ('prev_header_id := QP_BULK_PREQ_GRP.G_LINE_REC.header_id(i);');
1641 Text ('End If;', 3);
1642 Text ('Else', 2);
1643 Text ('If QP_PREQ_GRP.G_NEW_PRICING_CALL = ''Y'' Then',3);
1644 Text ('QP_PREQ_GRP.G_NEW_PRICING_CALL := ''N'';', 4);
1645 Text ('--oe_debug_pub.add (''prev_header_id: ''|| prev_header_id);');
1646 Text ('End If;', 3);
1647 Text ('End If;' , 2);
1648 ELSE
1649 Text('FOR i in QP_BULK_PREQ_GRP.G_HEADER_REC.header_id.first..QP_BULK_PREQ_GRP.G_HEADER_REC.header_id.last');
1650 --dbms_output.put_line('FOR i in QP_BULK_PREQ_GRP.G_HEADER_REC.header_id.first..QP_BULK_PREQ_GRP.G_header_REC.header_id.last');
1651 Text('Loop');
1652 END IF;
1653 END IF;
1654 --HVOP
1655 v_count := 0;
1656
1657 LOOP
1658
1659 v_count := v_count + 1;
1660
1661
1662 EXIT WHEN v_count > G_New_Sourced_Contexts_Tbl.COUNT;
1663
1664 v_attribute_name := G_New_Sourced_Contexts_Tbl(v_count).attribute_name;
1665 v_src_type := G_New_Sourced_Contexts_Tbl(v_count).src_type;
1666 v_value_string := G_New_Sourced_Contexts_Tbl(v_count).value_string;
1667 v_context_name := G_New_Sourced_Contexts_Tbl(v_count).context_name;
1668 v_context_type := G_New_Sourced_Contexts_Tbl(v_count).context_type;
1669
1670 --Fix for 2491269
1671 IF v_context_type <> 'PRODUCT'
1672 AND l_context_type_processed = 'PRODUCT'
1673 AND l_code_release_level > '110508'
1674 THEN
1675 Text('IF l_debug = FND_API.G_TRUE THEN',2);
1676 text('oe_debug_pub.add(''In check to call line_group'');',3);
1677 Text('END IF;',2);
1678 Text('IF QP_ATTR_MAPPING_PUB.G_CHECK_LINE_FLAG = ''Y'' THEN',2);
1679 Text('BEGIN',3);
1680 Text('IF l_debug = FND_API.G_TRUE THEN',3);
1681 text('oe_debug_pub.add(''Before call line_group'');',4);
1682 Text('END IF;',3);
1683 Text('QP_ATTR_MAPPING_PUB.Check_line_group_items(p_pricing_type_code);',3);
1684 Text('IF l_debug = FND_API.G_TRUE THEN',3);
1685 text('oe_debug_pub.add(''After call line_group'');',4);
1686 Text('END IF;',3);
1687 Text('EXCEPTION',3);
1688 Text('WHEN OTHERS THEN',3);
1689 Text('IF l_debug = FND_API.G_TRUE THEN',4);
1690 Text('oe_debug_pub.add(''Error in Check_line_group_items'');',5);
1691 Text('END IF;',4);
1692 Text('END;',3);
1693 Text('ELSE--QP_ATTR_MAPPING_PUB.G_CHECK_LINE_FLAG',2);
1694 Text('QP_ATTR_MAPPING_PUB.G_PASS_THIS_LINE := ''Y'';',3);
1695 Text('END IF;--QP_ATTR_MAPPING_PUB.G_CHECK_LINE_FLAG',2);
1696 Text('IF QP_ATTR_MAPPING_PUB.G_PASS_THIS_LINE = '||'''N'''||' THEN',2);
1697 Text('IF l_debug = FND_API.G_TRUE THEN',3);
1698 Text('oe_debug_pub.add(''Deleting sourced prod attr'');',4);
1699 Text('END IF;',3);
1700 Text('x_price_ctxts_result_tbl.delete;',3);
1701 Text('RETURN;',3);
1702 Text('END IF;--QP_ATTR_MAPPING_PUB.G_PASS_THIS_LINE',2);
1703 END IF;
1704 --End Fix for 2491269
1705
1706
1707 --dbms_output.put_line ('Src_Type: ' || v_src_type);
1708 COMMENT('Src_Type: ' || v_src_type,0);
1709
1710 l_context_type_processed := NULL;
1711
1715 IF v_src_type = 'API' THEN
1712 --dbms_output.put_line('v_context_type 2: ' || v_context_type);
1713
1714
1716
1717 v_attr_src_string := v_value_string;
1718
1719 IF p_HVOP_call = 'Y' AND INSTR(v_attr_src_string, 'OE_ORDER_PUB.') > 0 THEN
1720 HVOP_Bulkify_Structures ( v_attr_src_string,
1721 'OE_ORDER_PUB.G_HDR',
1722 'QP_BULK_PREQ_GRP.G_HEADER_REC',
1723 'OE_ORDER_PUB.G_LINE',
1724 'QP_BULK_PREQ_GRP.G_LINE_REC',
1725 'QP_BULK_PREQ_GRP'
1726 );
1727 --dbms_output.put_line ('v_attr_src_string: ' || v_attr_src_string);
1728 END IF;--p_HVOP_call
1729
1730 Text('BEGIN',2);
1731 --Text('v_attr_value := ' || v_attr_src_string || ';', 3);
1732 Break_Text(v_src_type,v_attr_src_string);
1733 Text('EXCEPTION',2);
1734 Text('WHEN OTHERS THEN',3);
1735 Text('v_attr_value := NULL;',4);
1736 Text('END;',2);
1737 New_Line;
1738 Text('BEGIN',2);
1739 Text('IF v_attr_value = FND_API.G_MISS_NUM THEN',2);
1740 Text('v_attr_value := NULL;',3);
1741 Text('END IF;',2);
1742 Text('EXCEPTION',2);
1743 Text('WHEN VALUE_ERROR THEN',3);
1744 Text('IF v_attr_value = FND_API.G_MISS_CHAR THEN',4);
1745 Text('v_attr_value := NULL;',5);
1746 Text('END IF;',4);
1747 Text('WHEN OTHERS THEN',3);
1748 Text('v_attr_value := NULL;',4);
1749 Text('END;',2);
1750 New_Line;
1751 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1752 New_Line;
1753
1754 END IF;
1755
1756 IF v_src_type = 'PROFILE_OPTION' THEN
1757
1758 v_attr_src_string := v_value_string;
1759 Text('BEGIN',2);
1760 Text('v_attr_value := fnd_profile.value( ''' || v_attr_src_string || ''');', 3);
1761 Text('EXCEPTION',2);
1762 Text('WHEN OTHERS THEN',3);
1763 Text('v_attr_value := NULL;',4);
1764 Text('END;',2);
1765 New_Line;
1766 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1767 New_Line;
1768 END IF;
1769
1770 IF v_src_type = 'SYSTEM' THEN
1771
1772 v_attr_src_string := v_value_string;
1773 Text('BEGIN',2);
1774 Text('SELECT ' || v_attr_src_string || ' INTO v_attr_value FROM DUAL;', 3);
1775 Text('EXCEPTION',2);
1776 Text('WHEN OTHERS THEN',3);
1777 Text('v_attr_value := NULL;',4);
1778 Text('END;',2);
1779 New_Line;
1780 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1781 New_Line;
1782
1783 END IF;
1784
1785 IF v_src_type = 'CONSTANT' THEN
1786
1787 v_attr_src_string := v_value_string;
1788 Text('v_attr_value := ''' || v_attr_src_string || ''';', 3);
1789 New_Line;
1790 Text('IF (v_attr_value IS NOT NULL) THEN',2);
1791 New_Line;
1792
1793 END IF;
1794
1795 IF v_src_type <> 'API_MULTIREC' THEN
1796
1797 IF v_context_type = 'QUALIFIER' THEN
1798
1799 IF p_HVOP_call = 'Y' THEN --hvop
1800
1801 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1802 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := QP_BULK_PREQ_GRP.G_line_rec.line_index(i);',4);
1803 ELSE --header
1804 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;',3);
1805 END IF; --hvop: pricing type decides line index
1806
1807 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || 'QUALIFIER' || ''';', 3);
1808 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) :=''' || v_context_name || ''';',3);
1809 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',3);
1810 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_value;',3);
1811 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 3);
1812 ------------------------put validated_flag code here-------
1813 IF v_context_name = 'MODLIST'
1814 AND
1815 v_attribute_name = 'QUALIFIER_ATTRIBUTE4'
1816 THEN
1817 Text ('If NVL(QP_BULK_PREQ_GRP.G_line_rec.agreement_id(i), FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM Then ', 3);
1818 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''Y'';', 4);
1819 Text ('End If;', 4);
1820 END IF;
1821
1822 ELSE
1823 Text('x_qual_ctxts_result_tbl(q_count).context_name := ''' || v_context_name || ''';',3);
1824 Text('x_qual_ctxts_result_tbl(q_count).attribute_name := ''' || v_attribute_name || ''';',3);
1825 Text('x_qual_ctxts_result_tbl(q_count).attribute_value := v_attr_value;',3);
1826 END IF; --hvop
1827
1828 Text('q_count := q_count + 1;',3);
1829
1830
1831 ELSIF v_context_type IN ('PRICING_ATTRIBUTE','PRODUCT') THEN
1832
1833 IF p_HVOP_call = 'Y' THEN --hvop
1834
1835 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1836 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := QP_BULK_PREQ_GRP.G_line_rec.line_index(i);',3);
1840
1837 ELSE --header
1838 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;',3);
1839 END IF; --hvop: pricing type decides line index
1841 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || SUBSTR(v_context_type, 1, 7) ||''';', 3);
1842 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) :=''' || v_context_name || ''';',3);
1843 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',3);
1844 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_value;',3);
1845 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 3);
1846 Text('q_count := q_count + 1;',3);
1847 ELSE
1848 Text('x_price_ctxts_result_tbl(p_count).context_name := ''' || v_context_name || ''';',3);
1849 Text('x_price_ctxts_result_tbl(p_count).attribute_name := ''' || v_attribute_name || ''';',3);
1850 Text('x_price_ctxts_result_tbl(p_count).attribute_value := v_attr_value;',3);
1851 Text('p_count := p_count + 1;',4);
1852 END IF;
1853 New_Line;
1854 END IF;
1855 --added for changed lines performance improvement bug 2491269
1856 IF v_context_type = 'PRODUCT'
1857 AND l_code_release_level > '110508'
1858 THEN
1859 Text('IF l_debug = FND_API.G_TRUE THEN',4);
1860 text('oe_debug_pub.add(''Gathering product details'');',5);
1861 Text('END IF;',4);
1862 l_context_type_processed := v_context_type;
1863 Text('BEGIN',4);
1864 Text('QP_ATTR_MAPPING_PUB.G_Product_attr_tbl(QP_ATTR_MAPPING_PUB.G_Product_attr_tbl.COUNT+1) := '
1865 ||'x_price_ctxts_result_tbl(p_count-1);',5);
1866 Text('Exception',4);
1867 Text('When Others Then',4);
1868 Text('IF l_debug = FND_API.G_TRUE THEN',5);
1869 Text('oe_debug_pub.add(''No product sourced '');',6);
1870 Text('END IF;',5);
1871 Text('END;',4);
1872 END IF;--v_context_type = 'PRODUCT'
1873 Text('IF l_debug = FND_API.G_TRUE THEN',4);
1874 text('oe_debug_pub.add(''After product assigned'');',5);
1875 Text('END IF;',4);
1876
1877 Text('END IF;--v_attr_(m)value',2);
1878
1879
1880 END IF;--v_src_type
1881
1882 IF v_src_type = 'API_MULTIREC' THEN
1883
1884 v_attr_src_string := v_value_string;
1885
1886 IF p_HVOP_call = 'Y' AND INSTR(v_attr_src_string, 'OE_ORDER_PUB.') > 0 THEN
1887 HVOP_Bulkify_Structures ( v_attr_src_string,
1888 'OE_ORDER_PUB.G_HDR',
1889 'QP_BULK_PREQ_GRP.G_HEADER_REC',
1890 'OE_ORDER_PUB.G_LINE',
1891 'QP_BULK_PREQ_GRP.G_LINE_REC',
1892 'QP_BULK_PREQ_GRP'
1893 );
1894 END IF;--p_HVOP_call
1895
1896 Text('BEGIN',2);
1897 IF v_attr_src_string = 'NULL' THEN
1898 Text('v_attr_mvalue(1) := ' || v_attr_src_string || ';', 3);
1899 ELSE
1900 Break_Text(v_src_type,v_attr_src_string);
1901 END IF;
1902 Text('EXCEPTION',2);
1903 Text('WHEN OTHERS THEN',3);
1904 Text('IF l_debug = FND_API.G_TRUE THEN',4);
1905 Text('oe_debug_pub.add(''Multirec API error'');',5);
1906 Text('END IF;',4);
1907 Text('END;',2);
1908 New_Line;
1909 Text('IF (v_attr_mvalue.count <> 0) AND (v_attr_mvalue(1) IS NOT NULL) THEN',2);
1910 Text('v_index := 1;',3);
1911 Text('LOOP',3);
1912
1913 IF v_context_type = 'QUALIFIER' THEN
1914
1915 IF p_HVOP_call = 'Y' THEN --hvop
1916
1917 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1918 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := QP_BULK_PREQ_GRP.G_line_rec.line_index(i);',4);
1919 ELSE --header
1920 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;',4);
1921 END IF; --hvop: pricing type decides line index
1922
1923 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || 'QUALIFIER' || ''';', 4);
1924 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) :=''' || v_context_name || ''';',4);
1925 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',4);
1926 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_mvalue(v_index);',4);
1927 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 4);
1928 ------------------------put validated_flag code here-------
1929 IF v_context_name = 'MODLIST'
1930 AND
1931 v_attribute_name = 'QUALIFIER_ATTRIBUTE4'
1932 THEN
1933 Text ('If NVL(QP_BULK_PREQ_GRP.G_line_rec.agreement_id(i), FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM Then ', 3);
1934 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''Y'';', 4);
1935 Text ('End If;', 4);
1936 END IF;
1937
1938 ELSE
1942 END IF; --hvop
1939 Text('x_qual_ctxts_result_tbl(q_count).context_name := ''' || v_context_name || ''';',4);
1940 Text('x_qual_ctxts_result_tbl(q_count).attribute_name := ''' || v_attribute_name || ''';',4);
1941 Text('x_qual_ctxts_result_tbl(q_count).attribute_value := v_attr_mvalue(v_index);',4);
1943
1944 Text('q_count := q_count + 1;',4);
1945 New_Line;
1946
1947
1948 ELSIF v_context_type IN ('PRICING_ATTRIBUTE', 'PRODUCT') THEN
1949
1950 IF p_HVOP_call = 'Y' THEN --hvop
1951
1952 IF p_pricing_type = 'L' THEN --hvop: pricing type decides line index
1953 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := QP_BULK_PREQ_GRP.G_line_rec.line_index(i);',3);
1954 ELSE --header
1955 Text ('QP_BULK_PREQ_GRP.G_line_index(q_count) := i;',4);
1956 END IF; --hvop: pricing type decides line index
1957
1958 Text ('QP_BULK_PREQ_GRP.G_attr_type(q_count) := ''' || SUBSTR(v_context_type, 1, 7) || ''';', 4);
1959 Text('QP_BULK_PREQ_GRP.G_attr_context(q_count) :=''' || v_context_name || ''';',4);
1960 Text('QP_BULK_PREQ_GRP.G_attr_attr(q_count) := ''' || v_attribute_name || ''';',4);
1961 Text('QP_BULK_PREQ_GRP.G_attr_value(q_count) := v_attr_mvalue(v_index);',4);
1962 Text ('QP_BULK_PREQ_GRP.G_validated_flag(q_count) := ''N'';', 4);
1963 Text('q_count := q_count + 1;',4);
1964 ELSE
1965 Text('x_price_ctxts_result_tbl(p_count).context_name := ''' || v_context_name || ''';',4);
1966 Text('x_price_ctxts_result_tbl(p_count).attribute_name := ''' || v_attribute_name || ''';',4);
1967 Text('x_price_ctxts_result_tbl(p_count).attribute_value := v_attr_mvalue(v_index);',4);
1968 Text('p_count := p_count + 1;',4);
1969 END IF;
1970 New_Line;
1971 END IF;
1972
1973 --added for changed lines performance improvement bug 2491269
1974 IF v_context_type = 'PRODUCT'
1975 AND l_code_release_level > '110508'
1976 THEN
1977 Text('IF l_debug = FND_API.G_TRUE THEN',4);
1978 text('oe_debug_pub.add(''Gathering product details'');',5);
1979 Text('END IF;',4);
1980 l_context_type_processed := v_context_type;
1981 Text('BEGIN',4);
1982 Text('QP_ATTR_MAPPING_PUB.G_Product_attr_tbl(QP_ATTR_MAPPING_PUB.G_Product_attr_tbl.COUNT+1) := '
1983 ||'x_price_ctxts_result_tbl(p_count-1);',5);
1984 Text('Exception',4);
1985 Text('When Others Then',4);
1986 Text('IF l_debug = FND_API.G_TRUE THEN',5);
1987 Text('oe_debug_pub.add(''No product sourced '');',6);
1988 Text('END IF;',5);
1989 Text('END;',4);
1990 END IF;--v_context_type = 'PRODUCT'
1991 Text('IF l_debug = FND_API.G_TRUE THEN',4);
1992 text('oe_debug_pub.add(''After product assigned'');',5);
1993 Text('END IF;',4);
1994
1995
1996
1997 New_Line;
1998 Text('EXIT WHEN v_index = v_attr_mvalue.LAST;',4);
1999 Text('v_index := v_index + 1;',4);
2000 Text('END LOOP;',3);
2001 Text('END IF;--v_attr_(m)value',2);
2002
2003 END IF;--v_src_type
2004
2005 END LOOP;
2006
2007 IF p_HVOP_Call = 'Y' THEN
2008 Text('END LOOP;--hvop',2);
2009 New_Line;
2010 END IF;
2011 Text('END IF;',1);
2012 New_Line;
2013 Text('END IF;',0);
2014 New_Line;
2015 END IF;
2016 EXCEPTION
2017 WHEN OTHERS THEN
2018 --dbms_output.put_line ('SQLERRM: '||SQLERRM);
2019 Text('SQLERRM: '||SQLERRM);
2020
2021 END Create_Sourcing_Calls;
2022
2023
2024 PROCEDURE Build_Sourcing_Package
2025 (
2026 err_buff OUT NOCOPY VARCHAR2,
2027 retcode OUT NOCOPY NUMBER
2028 )
2029 IS
2030
2031 TYPE t_cursor IS REF CURSOR;
2032 l_request_type_codes t_cursor;
2033 l_request_type_codes_new t_cursor;
2034 l_request_type_code VARCHAR2(30);
2035 l_sql_statement VARCHAR2(120);
2036 v_is_used VARCHAR2(1) :='N';
2037
2038
2039 BEGIN
2040
2041 --dbms_output.put_line('............1............');
2042 --l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2043 IF l_debug = Fnd_Api.G_TRUE THEN
2044 Oe_Debug_Pub.ADD('##### Begin Build Sourcing #####');
2045
2046 END IF;
2047 --FND_PROFILE.GET('QP_ATTRIBUTE_MANAGER_INSTALLED',G_ATTRMGR_INSTALLED);
2048 --dbms_output.put_line('............11............');
2049 G_ATTRMGR_INSTALLED := Qp_Util.Attrmgr_Installed;
2050 --dbms_output.put_line('............111............');
2051 --dbms_output.put_line('Profile value is : ' || G_ATTRMGR_INSTALLED);
2052
2053 Init_Applsys_Schema;
2054
2055 -- Writing out the body
2056
2057 --changes by spgopal 15-JUN-2001 = 'Y' for BUILD_SOURCE_TMP
2058 Pkg_Header('QP_BUILD_SOURCING_PVT_TMP', 'BODY');
2059 New_Line;
2060 Text('PROCEDURE Get_Attribute_Values',0);
2064 Text(', x_price_ctxts_result_tbl OUT NOCOPY QP_Attr_Mapping_PUB.CONTEXTS_RESULT_TBL_TYPE',0);
2061 Text('( p_req_type_code IN VARCHAR2',0);
2062 Text(', p_pricing_type_code IN VARCHAR2',0);
2063 Text(', x_qual_ctxts_result_tbl OUT NOCOPY QP_Attr_Mapping_PUB.CONTEXTS_RESULT_TBL_TYPE',0);
2065 Text(')',0);
2066 Text('IS',0);
2067 New_line;
2068 Text('v_attr_value VARCHAR2(240);',0); --4932085, 4960278
2069 Text('v_attr_mvalue QP_Attr_Mapping_PUB.t_MultiRecord;',0);
2070 Text('q_count NUMBER := 1;',0);
2071 Text('p_count NUMBER := 1;',0);
2072 Text('v_index NUMBER := 1;',0);
2073 Text('prev_header_id NUMBER := FND_API.G_MISS_NUM;',0);
2074 New_Line;
2075 Text('l_debug VARCHAR2(3);',0);
2076 Text('BEGIN',0);
2077 Text('qp_debug_util.tstart(''FETCH_ATTRIBUTES'',''Fetching the Attribute Values'');',0);
2078 New_Line;
2079
2080 Text('l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;',1);
2081
2082 -- added for g_new_pricing_call for caching - hwong
2083 text('if p_pricing_type_code = ''H'' and qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_no then', 0);
2084 text('qp_preq_grp.g_new_pricing_call := qp_preq_grp.g_yes;', 1);
2085 Text('IF l_debug = FND_API.G_TRUE THEN',1);
2086 text('oe_debug_pub.add(''hw/src/H: change to g_yes'');',2);
2087 Text('END IF;',1);
2088 text('end if;', 0);
2089 new_line;
2090
2091 IF NVL(G_ATTRMGR_INSTALLED,'N') = 'N' THEN
2092
2093 OPEN l_request_type_codes FOR
2094 SELECT DISTINCT request_type_code
2095 FROM qp_price_req_sources;
2096
2097 IF Qp_Code_Control.Get_Code_Release_Level > '110509' THEN
2098 Text ('If QP_Util_PUB.HVOP_Pricing_On= ''N'' Then --Follow Non-HVOP Path',1);
2099 END IF;
2100 LOOP
2101 FETCH l_request_type_codes INTO
2102 l_request_type_code;
2103
2104 EXIT WHEN l_request_type_codes%NOTFOUND;
2105
2106 Create_Sourcing_Calls(l_request_type_code,'L');
2107 Create_Sourcing_Calls(l_request_type_code,'H');
2108
2109 END LOOP;
2110
2111 --HVOP
2112 IF Qp_Code_Control.Get_Code_Release_Level > '110509' THEN
2113 New_Line;
2114 Text ('Else --Follow HVOP Path', 1);
2115 IF QP_JAVA_ENGINE_UTIL_PUB.JAVA_ENGINE_INSTALLED = 'Y' THEN --5295113, 5365971
2116 Create_Sourcing_Calls('ONT','L','Y');
2117 Create_Sourcing_Calls('ONT','H','Y');
2118 END IF;
2119 Text ('NULL;'); --5295113, 5365971
2120 New_Line;
2121 Text ('End If; --HVOP Path', 1);
2122 END IF;--QP_CODE_CONTROL.Get_Code_Release_Level
2123 --HVOP
2124
2125 -- added for g_new_pricing_call for caching - hwong
2126 new_line;
2127 Text ('If QP_Util_PUB.HVOP_Pricing_On= ''N'' Then');
2128 text('if p_pricing_type_code = ''L'' and qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then', 1);
2129 text('qp_preq_grp.g_new_pricing_call := qp_preq_grp.g_no;', 2);
2130 Text('IF l_debug = FND_API.G_TRUE THEN',2);
2131 text('oe_debug_pub.add(''hw/src/L: change to g_no'');',3);
2132 Text('END IF;',2);
2133 text('end if;', 1);
2134 Text('End If;');
2135 Text('qp_debug_util.tstop(''FETCH_ATTRIBUTES'');',0);
2136 New_Line;
2137 Text('END Get_Attribute_Values;',0);
2138 New_Line;
2139 ELSIF NVL(G_ATTRMGR_INSTALLED,'N') = 'Y' THEN
2140 --dbms_output.put_line('............2............');
2141 OPEN l_request_type_codes_new FOR
2142 SELECT DISTINCT request_type_code
2143 FROM QP_PTE_REQUEST_TYPES_B
2144 WHERE ENABLED_FLAG = 'Y'; -- 5365644, 5365968
2145
2146 IF Qp_Code_Control.Get_Code_Release_Level > '110509' THEN
2147 Text ('If QP_Util_PUB.HVOP_Pricing_On= ''N'' Then --Follow Non-HVOP Path',1);
2148 END IF;
2149 LOOP
2150
2151 FETCH l_request_type_codes_new INTO
2152 l_request_type_code;
2153
2154 EXIT WHEN l_request_type_codes_new%NOTFOUND;
2155
2156 Create_Sourcing_Calls(l_request_type_code,'L');
2157 Create_Sourcing_Calls(l_request_type_code,'H');
2158
2159 END LOOP;
2160 --dbms_output.put_line('............3............');
2161
2162 --HVOP
2163 IF Qp_Code_Control.Get_Code_Release_Level > '110509' THEN
2164 New_Line;
2165 Text ('Else --Follow HVOP Path', 1);
2166 IF QP_JAVA_ENGINE_UTIL_PUB.JAVA_ENGINE_INSTALLED = 'Y' THEN --5295113, 5365971
2167 Create_Sourcing_Calls('ONT','L','Y');
2168 Create_Sourcing_Calls('ONT','H','Y');
2169 END IF;
2170 Text ('NULL;'); --5295113, 5365971
2171 New_Line;
2172 Text ('End If; --HVOP Path', 1);
2173 END IF;--QP_CODE_CONTROL.Get_Code_Release_Level
2174 --HVOP
2175
2176 -- added for g_new_pricing_call for caching - hwong
2177 new_line;
2178 Text ('If QP_Util_PUB.HVOP_Pricing_On = ''N'' Then',1);
2179 text('if p_pricing_type_code = ''L'' and qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then', 2);
2180 text('qp_preq_grp.g_new_pricing_call := qp_preq_grp.g_no;', 3);
2181 Text('IF l_debug = FND_API.G_TRUE THEN',3);
2182 text('oe_debug_pub.add(''hw/src/L: change to g_no'');',4);
2183 Text('END IF;',3);
2184 text('end if;', 2);
2185 Text ('End If;', 1);
2186
2190 New_Line;
2187 New_Line;
2188 Text('qp_debug_util.tstop(''FETCH_ATTRIBUTES'');',0);
2189 Text('END Get_Attribute_Values;',0);
2191 END IF;
2192
2193 Text('FUNCTION Is_Attribute_Used (p_attribute_context IN VARCHAR2, p_attribute_code IN VARCHAR2)',0);
2194 Text('RETURN VARCHAR2',0);
2195 New_Line;
2196 Text('IS',0);
2197 New_line;
2198 Text('x_out VARCHAR2(1) := ''N'';',0);
2199 Text('BEGIN',0);
2200 New_line;
2201
2202 v_is_used := Is_Attribute_Used('VOLUME','PRICING_ATTRIBUTE10');
2203
2204 Text('IF (p_attribute_context = ''VOLUME'') and (p_attribute_code = ''PRICING_ATTRIBUTE10'')',0);
2205 Text('THEN',0);
2206 Text('x_out := ''' || v_is_used || ''';',0);
2207 Text('END IF;',0);
2208 Text('IF (p_attribute_context = ''VOLUME'') and (p_attribute_code = ''PRICING_ATTRIBUTE12'')',0);
2209
2210 v_is_used := Is_Attribute_Used('VOLUME','PRICING_ATTRIBUTE12');
2211
2212 Text('THEN',0);
2213 Text('x_out := ''' || v_is_used || ''';',0);
2214 Text('END IF;',0);
2215 New_line;
2216 Text('RETURN x_out;',0);
2217 New_line;
2218 Text('END Is_Attribute_Used;',0);
2219 New_line;
2220
2221 -- Text('END QP_BUILD_SOURCING_PVT;',0);
2222 -- Text('/',0);
2223 --changes by spgopal 15-JUN-2001 for BUILD_SOURCE_TMP
2224 --dbms_output.put_line('............4............');
2225 Pkg_End('QP_BUILD_SOURCING_PVT_TMP', 'BODY');
2226
2227 --dbms_output.put_line('............5............');
2228 retcode := 0;
2229 Fnd_Message.SET_NAME('QP','QP_ATTRIBUTE_SOURCING_SUCCESS');
2230 err_buff := Fnd_Message.GET;
2231
2232 IF l_debug = Fnd_Api.G_TRUE THEN
2233 Oe_Debug_Pub.ADD('##### End Build Sourcing #####');
2234
2235 END IF;
2236 EXCEPTION
2237
2238 WHEN Fnd_Api.G_EXC_ERROR THEN
2239 Fnd_Message.SET_NAME('QP','QP_ATTRIBUTE_SOURCING_ERROR');
2240 Fnd_Message.SET_TOKEN('PACKAGE_TYPE','BODY');
2241 Fnd_Message.SET_TOKEN('PACKAGE_NAME','QP_BUILD_SOURCING_PVT');
2242 Fnd_Message.SET_TOKEN('ERRMSG',SUBSTR(SQLERRM,1,150));
2243 err_buff := Fnd_Message.GET;
2244 retcode := 2;
2245
2246 -- err_buff := 'Please check the log file for error messages';
2247
2248 WHEN OTHERS THEN
2249 Fnd_Message.SET_NAME('QP','QP_ATTRIBUTE_SOURCING_ERROR');
2250 Fnd_Message.SET_TOKEN('PACKAGE_TYPE','BODY');
2251 Fnd_Message.SET_TOKEN('PACKAGE_NAME','QP_BUILD_SOURCING_PVT');
2252 Fnd_Message.SET_TOKEN('ERRMSG',SUBSTR(SQLERRM,1,150));
2253 err_buff := Fnd_Message.GET;
2254 PUT_LINE( 'Error in creating QP_BUILD_SOURCING_PVT '||SQLERRM);
2255 retcode := 2;
2256 -- err_buff := sqlerrm;
2257
2258
2259 END Build_Sourcing_Package;
2260
2261
2262 PROCEDURE Build_Contexts
2263 ( p_request_type_code IN VARCHAR2
2264 , p_pricing_type IN VARCHAR2
2265 --added for MOAC
2266 , p_org_id IN NUMBER DEFAULT NULL
2267 , x_price_contexts_result_tbl OUT NOCOPY CONTEXTS_RESULT_TBL_TYPE
2268 , x_qual_contexts_result_tbl OUT NOCOPY CONTEXTS_RESULT_TBL_TYPE
2269 )
2270 IS
2271
2272 v_count NUMBER := 0;
2273 l_count NUMBER := 0;
2274
2275 l_custom_sourced VARCHAR2(1) := Fnd_Profile.VALUE('QP_CUSTOM_SOURCED');
2276
2277 l_req_type_code VARCHAR2(30);
2278 l_pricing_type_code VARCHAR2(1);
2279 l_qual_contexts_result_tbl Qp_Attr_Mapping_Pub.CONTEXTS_RESULT_TBL_TYPE;
2280 l_price_contexts_result_tbl Qp_Attr_Mapping_Pub.CONTEXTS_RESULT_TBL_TYPE;
2281
2282 l_sourcing_start_time NUMBER;
2283 l_sourcing_end_time NUMBER;
2284 l_time_difference NUMBER;
2285
2286 BEGIN
2287
2288 -- Set the global variable G_DEBUG_ENGINE
2289 Qp_Preq_Grp.Set_QP_Debug;
2290
2291 --added for MOAC
2292 --Set the Global variable G_ORG_ID
2293 G_ORG_ID := NVL(p_org_id, Qp_Util.get_org_id);
2294
2295 --added for moac
2296 --Initialize MOAC and set org context to Org passed in nvl(p_control_rec.org_id, mo_default_org_id)
2297
2298 IF Mo_Global.get_access_mode IS NULL THEN
2299 Mo_Global.Init('QP');
2300 IF G_ORG_ID IS NOT NULL THEN
2301 Mo_Global.set_policy_context('S', G_ORG_ID);
2302 END IF;
2303 END IF;--MO_GLOBAL
2304
2305
2306 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
2307
2308 IF l_debug = Fnd_Api.G_TRUE THEN
2309 Oe_Debug_Pub.ADD('##### Begin Build Contexts #####');
2310 END IF;
2311 --setting time
2312 l_sourcing_start_time := dbms_utility.get_time;
2313
2314 Qp_Attr_Mapping_Pub.G_REQ_TYPE_CODE := p_request_type_code; --bug3848849
2315
2316 Qp_Build_Sourcing_Pvt.Get_Attribute_Values(p_req_type_code => p_request_type_code,
2317 p_pricing_type_code => p_pricing_type,
2318 x_qual_ctxts_result_tbl => x_qual_contexts_result_tbl,
2319 x_price_ctxts_result_tbl => x_price_contexts_result_tbl);
2320
2321
2322 /*
2323 If the attribute sourcing method is 'CUSTOM SOURCING' then user provides code to source the attributes.
2327
2324 User code is written in the package procedure QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values and Build_Contexts program calls this procedure to pickup custom sourced attributes if the profile option 'QP_CUSTOM_SOURCED' is set to 'Y' -- GTIPPIRE
2325 */
2326
2328 IF NVL(l_custom_sourced,'N') = 'Y' THEN
2329 Begin
2330 qp_debug_util.tstart('GET_CUSTOM_ATTRIBUTE_VALUES','Calling the QP_CUSTOM_SOURCE package to fetch the cutom attribute values');
2331 Qp_Custom_Source.Get_Custom_Attribute_Values(p_req_type_code => p_request_type_code,
2332 p_pricing_type_code => p_pricing_type,
2333 x_qual_ctxts_result_tbl => l_qual_contexts_result_tbl,
2334 x_price_ctxts_result_tbl => l_price_contexts_result_tbl);
2335 qp_debug_util.tstop('GET_CUSTOM_ATTRIBUTE_VALUES');
2336 exception
2337 when others then
2338 qp_debug_util.tstop('GET_CUSTOM_ATTRIBUTE_VALUES');
2339 end;
2340
2341 l_count := x_qual_contexts_result_tbl.COUNT;
2342 LOOP
2343 l_count := l_count + 1;
2344 v_count := v_count + 1;
2345 EXIT WHEN v_count > l_qual_contexts_result_tbl.COUNT;
2346 x_qual_contexts_result_tbl(l_count).context_name :=
2347 l_qual_contexts_result_tbl(v_count).context_name;
2348 x_qual_contexts_result_tbl(l_count).attribute_name :=
2349 l_qual_contexts_result_tbl(v_count).attribute_name;
2350 x_qual_contexts_result_tbl(l_count).attribute_value :=
2351 l_qual_contexts_result_tbl(v_count).attribute_value;
2352 END LOOP;
2353
2354 l_count := x_price_contexts_result_tbl.COUNT;
2355 v_count := 0;
2356
2357 LOOP
2358 l_count := l_count + 1;
2359 v_count := v_count + 1;
2360 EXIT WHEN v_count > l_price_contexts_result_tbl.COUNT;
2361 x_price_contexts_result_tbl(l_count).context_name :=
2362 l_price_contexts_result_tbl(v_count).context_name;
2363 x_price_contexts_result_tbl(l_count).attribute_name :=
2364 l_price_contexts_result_tbl(v_count).attribute_name;
2365 x_price_contexts_result_tbl(l_count).attribute_value:=
2366 l_price_contexts_result_tbl(v_count).attribute_value;
2367 END LOOP;
2368 END IF;
2369
2370 /*
2371 If the attribute sourcing method is 'ATTRIBUTE MAPPING' then the values for the attributes
2372 which are used but not yet mapped must be determined.
2373 */
2374
2375 IF Qp_Code_Control.CODE_RELEASE_LEVEL > 110508 THEN
2376 l_price_contexts_result_tbl.DELETE;
2377 l_qual_contexts_result_tbl.DELETE;
2378
2379 Map_Used_But_Not_Mapped_Attrs( p_request_type_code
2380 , p_pricing_type
2381 , l_price_contexts_result_tbl
2382 , l_qual_contexts_result_tbl);
2383
2384 l_count := x_qual_contexts_result_tbl.COUNT;
2385 v_count := 0;
2386
2387 LOOP
2388 l_count := l_count + 1;
2389 v_count := v_count + 1;
2390
2391 EXIT WHEN v_count > l_qual_contexts_result_tbl.COUNT;
2392
2393 x_qual_contexts_result_tbl(l_count).context_name :=
2394 l_qual_contexts_result_tbl(v_count).context_name;
2395 x_qual_contexts_result_tbl(l_count).attribute_name :=
2396 l_qual_contexts_result_tbl(v_count).attribute_name;
2397 x_qual_contexts_result_tbl(l_count).attribute_value :=
2398 l_qual_contexts_result_tbl(v_count).attribute_value;
2399 END LOOP;
2400
2401 l_count := x_price_contexts_result_tbl.COUNT;
2402 v_count := 0;
2403
2404 LOOP
2405 l_count := l_count + 1;
2406 v_count := v_count + 1;
2407
2408 EXIT WHEN v_count > l_price_contexts_result_tbl.COUNT;
2409
2410 x_price_contexts_result_tbl(l_count).context_name :=
2411 l_price_contexts_result_tbl(v_count).context_name;
2412 x_price_contexts_result_tbl(l_count).attribute_name :=
2413 l_price_contexts_result_tbl(v_count).attribute_name;
2414 x_price_contexts_result_tbl(l_count).attribute_value:=
2415 l_price_contexts_result_tbl(v_count).attribute_value;
2416 END LOOP;
2417
2418 END IF;
2419
2420 /*
2421 Debug info
2422 */
2423
2424 v_count := 0;
2425
2426 LOOP
2427
2428 v_count := v_count + 1;
2429 EXIT WHEN v_count > x_qual_contexts_result_tbl.COUNT;
2430
2431 IF l_debug = Fnd_Api.G_TRUE THEN
2432 Oe_Debug_Pub.ADD('Context Name: ' || x_qual_contexts_result_tbl(v_count).context_name);
2433 Oe_Debug_Pub.ADD('Attribute Name: ' || x_qual_contexts_result_tbl(v_count).attribute_name);
2434 Oe_Debug_Pub.ADD('Attribute Value: ' || x_qual_contexts_result_tbl(v_count).attribute_value);
2435
2436 END IF;
2437 END LOOP;
2438
2439 v_count := 0;
2440
2441 LOOP
2442
2443 v_count := v_count + 1;
2444 EXIT WHEN v_count > x_price_contexts_result_tbl.COUNT;
2445
2446 IF l_debug = Fnd_Api.G_TRUE THEN
2447 Oe_Debug_Pub.ADD('Context Name: ' || x_price_contexts_result_tbl(v_count).context_name);
2451 END IF;
2448 Oe_Debug_Pub.ADD('Attribute Name: ' || x_price_contexts_result_tbl(v_count).attribute_name);
2449 Oe_Debug_Pub.ADD('Attribute Value: ' || x_price_contexts_result_tbl(v_count).attribute_value);
2450
2452 END LOOP;
2453
2454 IF l_debug = Fnd_Api.G_TRUE THEN
2455 Oe_Debug_Pub.ADD('##### End Build Contexts #####');
2456
2457 END IF;
2458 --setting time
2459 l_sourcing_end_time := dbms_utility.get_time;
2460 l_time_difference := (l_sourcing_end_time - l_sourcing_start_time)/100 ;
2461
2462 IF l_debug = Fnd_Api.G_TRUE THEN
2463 Oe_Debug_Pub.ADD('##### Total Time in Build_Contexts(in sec) : ' || l_time_difference || ' #####');
2464
2465
2466 END IF;
2467 END Build_Contexts;
2468
2469
2470
2471 /*
2472 overloading build_contexts for AG purpose performance fix
2473 to insert into tmp tables directly for OM Integration
2474 changes by spgopal
2475 */
2476
2477 PROCEDURE Build_Contexts
2478 ( p_request_type_code IN VARCHAR2,
2479 p_line_index IN NUMBER,
2480 p_pricing_type_code IN VARCHAR2,
2481 p_price_list_validated_flag IN VARCHAR2,
2482 --added for MOAC
2483 p_org_id IN NUMBER DEFAULT NULL
2484 )
2485 IS
2486
2487 CURSOR l_line_cur IS
2488 SELECT line_index
2489 FROM qp_npreq_lines_tmp
2490 WHERE line_type_code = Qp_Preq_Pub.G_LINE_LEVEL
2491 AND price_flag IN (Qp_Preq_Pub.G_YES, Qp_Preq_Pub.G_PHASE);
2492
2493 CURSOR l_order_cur IS
2494 SELECT line_index
2495 FROM qp_npreq_lines_tmp
2496 WHERE line_type_code = Qp_Preq_Pub.G_ORDER_LEVEL
2497 AND price_flag IN (Qp_Preq_Pub.G_YES, Qp_Preq_Pub.G_PHASE);
2498
2499
2500
2501 l_price_contexts_result_tbl CONTEXTS_RESULT_TBL_TYPE;
2502 l_qual_contexts_result_tbl CONTEXTS_RESULT_TBL_TYPE;
2503
2504 v_price_contexts_result_tbl CONTEXTS_RESULT_TBL_TYPE;
2505 v_qual_contexts_result_tbl CONTEXTS_RESULT_TBL_TYPE;
2506
2507 l_line_index_tbl Qp_Preq_Grp.PLS_INTEGER_TYPE;
2508 l_line_detail_index_tbl Qp_Preq_Grp.PLS_INTEGER_TYPE;
2509 l_attribute_type_tbl Qp_Preq_Grp.VARCHAR_TYPE;
2510 l_context_tbl Qp_Preq_Grp.VARCHAR_TYPE;
2511 l_attribute_tbl Qp_Preq_Grp.VARCHAR_TYPE;
2512 l_value_from_tbl Qp_Preq_Grp.VARCHAR_TYPE;
2513 l_value_to_tbl Qp_Preq_Grp.VARCHAR_TYPE;
2514 l_validated_flag_tbl Qp_Preq_Grp.VARCHAR_TYPE;
2515 l_ATTRIBUTE_LEVEL_tbl Qp_Preq_Grp.varchar_type;
2516 l_LIST_HEADER_ID_tbl Qp_Preq_Grp.number_type;
2517 l_LIST_LINE_ID_tbl Qp_Preq_Grp.number_type;
2518 l_SETUP_VALUE_FROM_tbl Qp_Preq_Grp.varchar_type;
2519 l_SETUP_VALUE_TO_tbl Qp_Preq_Grp.varchar_type;
2520 l_GROUPING_NUMBER_tbl Qp_Preq_Grp.pls_integer_type;
2521 l_NO_QUALIFIERS_IN_GRP_tbl Qp_Preq_Grp.pls_integer_type;
2522 l_COMPARISON_OPERATOR_TYPE_tbl Qp_Preq_Grp.varchar_type;
2523 l_APPLIED_FLAG_tbl Qp_Preq_Grp.varchar_type;
2524 l_PRICING_STATUS_CODE_tbl Qp_Preq_Grp.varchar_type;
2525 l_PRICING_STATUS_TEXT_tbl Qp_Preq_Grp.varchar_type;
2526 l_QUALIFIER_PRECEDENCE_tbl Qp_Preq_Grp.pls_integer_type;
2527 l_DATATYPE_tbl Qp_Preq_Grp.varchar_type;
2528 l_PRICING_ATTR_FLAG_tbl Qp_Preq_Grp.varchar_type;
2529 l_QUALIFIER_type_tbl Qp_Preq_Grp.varchar_type;
2530 l_PRODUCT_UOM_CODE_TBL Qp_Preq_Grp.varchar_type;
2531 l_EXCLUDER_FLAG_TBL Qp_Preq_Grp.varchar_type ;
2532 l_PRICING_PHASE_ID_TBL Qp_Preq_Grp.pls_integer_type ;
2533 l_INCOMPATABILITY_GRP_CODE_TBL Qp_Preq_Grp.varchar_type ;
2534 l_LINE_DETAIL_type_CODE_TBL Qp_Preq_Grp.varchar_type ;
2535 l_MODIFIER_LEVEL_CODE_TBL Qp_Preq_Grp.varchar_type ;
2536 l_PRIMARY_UOM_FLAG_TBL Qp_Preq_Grp.varchar_type ;
2537
2538 l_pricing_type_code VARCHAR2(1) := Fnd_Api.G_MISS_CHAR;
2539 K PLS_INTEGER;
2540
2541 l_custom_sourced VARCHAR2(1) := Fnd_Profile.VALUE('QP_CUSTOM_SOURCED');
2542
2543 l_sourcing_start_time NUMBER;
2544 l_sourcing_end_time NUMBER;
2545 l_time_difference NUMBER;
2546
2547 v_count NUMBER := 0;
2548 l_count NUMBER := 0;
2549 l_status_code VARCHAR2(30);
2550 l_status_text VARCHAR2(240);
2551 E_ROUTINE_ERRORS EXCEPTION;
2552
2553 BEGIN
2554
2555 -- Set the global variable G_DEBUG_ENGINE
2556 Qp_Preq_Grp.Set_QP_Debug;
2557
2558 --added for MOAC
2559 --Set the Global variable G_ORG_ID
2560 G_ORG_ID := NVL(p_org_id, Qp_Util.get_org_id);
2561
2562 --added for moac
2563 --Initialize MOAC and set org context to Org passed in nvl(p_control_rec.org_id, mo_default_org_id)
2564
2565 IF Mo_Global.get_access_mode IS NULL THEN
2566 Mo_Global.Init('QP');
2567 IF G_ORG_ID IS NOT NULL THEN
2568 Mo_Global.set_policy_context('S', G_ORG_ID);
2569 END IF;
2570 END IF;--MO_GLOBAL
2571
2572 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
2573
2574 --setting time
2575 l_sourcing_start_time := dbms_utility.get_time;
2576
2577 l_price_contexts_result_tbl.DELETE;
2578 l_qual_contexts_result_tbl.DELETE;
2579
2580 v_price_contexts_result_tbl.DELETE;
2581 v_qual_contexts_result_tbl.DELETE;
2582
2583 l_line_index_tbl.DELETE;
2584 l_attribute_type_tbl.DELETE;
2585 l_context_tbl.DELETE;
2586 l_attribute_tbl.DELETE;
2587 l_value_from_tbl.DELETE;
2591 l_LIST_LINE_ID_tbl.DELETE;
2588 l_validated_flag_tbl.DELETE;
2589 l_ATTRIBUTE_LEVEL_tbl.DELETE;
2590 l_LIST_HEADER_ID_tbl.DELETE;
2592 l_SETUP_VALUE_FROM_tbl.DELETE;
2593 l_SETUP_VALUE_TO_tbl.DELETE;
2594 l_GROUPING_NUMBER_tbl.DELETE;
2595 l_NO_QUALIFIERS_IN_GRP_tbl.DELETE;
2596 l_COMPARISON_OPERATOR_TYPE_tbl.DELETE;
2597 l_APPLIED_FLAG_tbl.DELETE;
2598 l_PRICING_STATUS_CODE_tbl.DELETE;
2599 l_PRICING_STATUS_TEXT_tbl.DELETE;
2600 l_QUALIFIER_PRECEDENCE_tbl.DELETE;
2601 l_DATATYPE_tbl.DELETE;
2602 l_PRICING_ATTR_FLAG_tbl.DELETE;
2603 l_QUALIFIER_type_tbl.DELETE;
2604 l_PRODUCT_UOM_CODE_tbl.DELETE;
2605 l_EXCLUDER_FLAG_tbl.DELETE;
2606 l_PRICING_PHASE_ID_tbl.DELETE;
2607 l_INCOMPATABILITY_GRP_CODE_tbl.DELETE;
2608 l_LINE_DETAIL_type_CODE_tbl.DELETE;
2609 l_MODIFIER_LEVEL_CODE_tbl.DELETE;
2610 l_PRIMARY_UOM_FLAG_tbl.DELETE;
2611
2612 IF l_debug = Fnd_Api.G_TRUE THEN
2613 Qp_Preq_Grp.ENGINE_DEBUG('Begin Build contexts');
2614
2615 END IF;
2616 K := 0;
2617 Qp_Attr_Mapping_Pub.G_REQ_TYPE_CODE := p_request_type_code; --bug3848849
2618
2619 --Called Build Sourcing at line level
2620 IF l_debug = Fnd_Api.G_TRUE THEN
2621 Qp_Preq_Grp.ENGINE_DEBUG('Called Build Sourcing at line level----------');
2622
2623 END IF;
2624 l_pricing_type_code := 'L';
2625
2626 IF (p_pricing_type_code = 'L') THEN
2627
2628 Qp_Build_Sourcing_Pvt.Get_Attribute_Values(p_req_type_code => p_request_type_code,
2629 p_pricing_type_code => p_pricing_type_code,
2630 x_qual_ctxts_result_tbl => l_qual_contexts_result_tbl,
2631 x_price_ctxts_result_tbl => l_price_contexts_result_tbl);
2632
2633 /*
2634 If the attribute sourcing method is 'CUSTOM SOURCING' then user provides code to source
2635 the attributes.
2636 User code is written in the package procedure QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values and
2637 Build_Contexts program calls this procedure to pickup custom sourced attributes if the
2638 profile option ' QP_CUSTOM_SOURCED' is set to 'Y' -- GTIPPIRE
2639 */
2640
2641 IF l_debug = Fnd_Api.G_TRUE THEN
2642 Qp_Preq_Grp.ENGINE_DEBUG('Number of qual attrs got from Get_Attribute_Values - ' || l_qual_contexts_result_tbl.COUNT );
2643 Qp_Preq_Grp.ENGINE_DEBUG('Number of pricing attrs got from Get_Attribute_Values - ' || l_price_contexts_result_tbl.COUNT );
2644
2645 END IF;
2646 IF NVL(l_custom_sourced,'N') = 'Y' THEN
2647 IF l_debug = Fnd_Api.G_TRUE THEN
2648 Qp_Preq_Grp.ENGINE_DEBUG('Before Calling Custom Sourcing Package ----------');
2649 END IF;
2650 Begin
2651 qp_debug_util.tstart('GET_CUSTOM_ATTRIBUTE_VALUES','Calling the QP_CUSTOM_SOURCE package to fetch the cutom attribute values');
2652 Qp_Custom_Source.Get_Custom_Attribute_Values(p_req_type_code => p_request_type_code,
2653 p_pricing_type_code => p_pricing_type_code,
2654 x_qual_ctxts_result_tbl => v_qual_contexts_result_tbl,
2655 x_price_ctxts_result_tbl => v_price_contexts_result_tbl);
2656 qp_debug_util.tstop('GET_CUSTOM_ATTRIBUTE_VALUES');
2657 exception
2658 when others then
2659 qp_debug_util.tstop('GET_CUSTOM_ATTRIBUTE_VALUES');
2660 end;
2661 IF l_debug = Fnd_Api.G_TRUE THEN
2662 Qp_Preq_Grp.ENGINE_DEBUG('After Calling Custom Sourcing Package ----------');
2663
2664 Qp_Preq_Grp.ENGINE_DEBUG('Number of qual attrs got from Get_Custom_Attribute_Values - ' || v_qual_contexts_result_tbl.COUNT );
2665 Qp_Preq_Grp.ENGINE_DEBUG('Number of pricing attrs got from Get_Custom_Attribute_Values - ' || v_price_contexts_result_tbl.COUNT );
2666
2667 END IF;
2668 l_count := l_qual_contexts_result_tbl.COUNT;
2669
2670 LOOP
2671 l_count := l_count + 1;
2672 v_count := v_count + 1;
2673 EXIT WHEN v_count > v_qual_contexts_result_tbl.COUNT;
2674 l_qual_contexts_result_tbl(l_count).context_name :=
2675 v_qual_contexts_result_tbl(v_count).context_name;
2676 l_qual_contexts_result_tbl(l_count).attribute_name :=
2677 v_qual_contexts_result_tbl(v_count).attribute_name;
2678 l_qual_contexts_result_tbl(l_count).attribute_value :=
2679 v_qual_contexts_result_tbl(v_count).attribute_value;
2680 END LOOP;
2681
2682 l_count := l_price_contexts_result_tbl.COUNT;
2683 v_count := 0;
2684
2685 LOOP
2686 l_count := l_count + 1;
2687 v_count := v_count + 1;
2688 EXIT WHEN v_count > v_price_contexts_result_tbl.COUNT;
2689 l_price_contexts_result_tbl(l_count).context_name :=
2690 v_price_contexts_result_tbl(v_count).context_name;
2691 l_price_contexts_result_tbl(l_count).attribute_name :=
2692 v_price_contexts_result_tbl(v_count).attribute_name;
2693 l_price_contexts_result_tbl(l_count).attribute_value:=
2694 v_price_contexts_result_tbl(v_count).attribute_value;
2695 END LOOP;
2696 END IF;
2697
2698 /*
2699 If the attribute sourcing method is 'ATTRIBUTE MAPPING' then the values for the attributes
2700 which are used but not yet mapped must be determined. --SFIRESTO
2701 */
2702
2703 IF Qp_Code_Control.GET_CODE_RELEASE_LEVEL > 110508 THEN
2704 v_price_contexts_result_tbl.DELETE;
2705 v_qual_contexts_result_tbl.DELETE;
2706
2707 Map_Used_But_Not_Mapped_Attrs( p_request_type_code
2708 , p_pricing_type_code
2712 l_count := l_qual_contexts_result_tbl.COUNT;
2709 , v_price_contexts_result_tbl
2710 , v_qual_contexts_result_tbl);
2711
2713 v_count := 0;
2714
2715 LOOP
2716 l_count := l_count + 1;
2717 v_count := v_count + 1;
2718
2719 EXIT WHEN v_count > v_qual_contexts_result_tbl.COUNT;
2720
2721 l_qual_contexts_result_tbl(l_count).context_name :=
2722 v_qual_contexts_result_tbl(v_count).context_name;
2723 l_qual_contexts_result_tbl(l_count).attribute_name :=
2724 v_qual_contexts_result_tbl(v_count).attribute_name;
2725 l_qual_contexts_result_tbl(l_count).attribute_value :=
2726 v_qual_contexts_result_tbl(v_count).attribute_value;
2727 END LOOP;
2728
2729 l_count := l_price_contexts_result_tbl.COUNT;
2730 v_count := 0;
2731
2732 LOOP
2733 l_count := l_count + 1;
2734 v_count := v_count + 1;
2735
2736 EXIT WHEN v_count > v_price_contexts_result_tbl.COUNT;
2737
2738 l_price_contexts_result_tbl(l_count).context_name :=
2739 v_price_contexts_result_tbl(v_count).context_name;
2740 l_price_contexts_result_tbl(l_count).attribute_name :=
2741 v_price_contexts_result_tbl(v_count).attribute_name;
2742 l_price_contexts_result_tbl(l_count).attribute_value:=
2743 v_price_contexts_result_tbl(v_count).attribute_value;
2744 END LOOP;
2745
2746 END IF;
2747
2748
2749 --FOR j IN l_line_cur
2750 --LOOP
2751 FOR i IN 1..l_price_contexts_result_tbl.COUNT
2752 LOOP
2753 --sourcing product attributes
2754 K := K + 1;
2755
2756 l_line_index_tbl(K) := p_line_index;
2757 IF l_price_contexts_result_tbl(i).context_name =
2758 Qp_Preq_Pub.G_ITEM_CONTEXT
2759 THEN
2760 l_attribute_type_tbl(K) := Qp_Preq_Pub.G_PRODUCT_TYPE;
2761 ELSE
2762 l_attribute_type_tbl(K) := Qp_Preq_Pub.G_PRICING_TYPE;
2763 END IF;
2764 l_context_tbl(K) := l_price_contexts_result_tbl(i).context_name;
2765 l_attribute_tbl(K) := l_price_contexts_result_tbl(i).attribute_name;
2766 l_value_from_tbl(K) := l_price_contexts_result_tbl(i).attribute_value;
2767
2768 l_validated_flag_tbl(K) := 'N';
2769 /************** Defaulting for Java Engine ********************/
2770 l_ATTRIBUTE_LEVEL_tbl(K) := Qp_Preq_Pub.G_LINE_LEVEL;
2771 l_LIST_HEADER_ID_tbl(K) := NULL;
2772 l_LIST_LINE_ID_tbl(K) := NULL;
2773 l_SETUP_VALUE_FROM_tbl(K) := NULL;
2774 l_SETUP_VALUE_TO_tbl(K) := NULL;
2775 l_GROUPING_NUMBER_tbl(K) := NULL;
2776 l_NO_QUALIFIERS_IN_GRP_tbl(K) := NULL;
2777 l_COMPARISON_OPERATOR_TYPE_tbl(K) := NULL;
2778 l_APPLIED_FLAG_tbl(K) := Qp_Preq_Pub.G_LIST_NOT_APPLIED;
2779 l_PRICING_STATUS_CODE_tbl(K) := Qp_Preq_Pub.G_STATUS_UNCHANGED;
2780 l_PRICING_STATUS_TEXT_tbl(K) := NULL;
2781 l_QUALIFIER_PRECEDENCE_tbl(K) := NULL;
2782 l_DATATYPE_tbl(K) := NULL;
2783 l_PRICING_ATTR_FLAG_tbl(K) := Qp_Preq_Pub.G_YES;
2784 l_QUALIFIER_TYPE_tbl(K) := NULL;
2785 l_PRODUCT_UOM_CODE_tbl(K) := NULL;
2786 l_EXCLUDER_FLAG_tbl(K) := NULL;
2787 l_PRICING_PHASE_ID_tbl(K) := NULL;
2788 l_INCOMPATABILITY_GRP_CODE_tbl(K) := NULL;
2789 l_LINE_DETAIL_TYPE_CODE_tbl(K) := NULL;
2790 l_MODIFIER_LEVEL_CODE_tbl(K) := NULL;
2791 l_PRIMARY_UOM_FLAG_tbl(K) := NULL;
2792
2793 END LOOP;
2794
2795 FOR i IN 1..l_qual_contexts_result_tbl.COUNT
2796 LOOP
2797 --sourcing qualifier attributes
2798 K := K + 1;
2799 l_line_index_tbl(K) := p_line_index;
2800 l_attribute_type_tbl(K) := Qp_Preq_Pub.G_QUALIFIER_TYPE;
2801 l_context_tbl(K) := l_qual_contexts_result_tbl(i).context_name;
2802 l_attribute_tbl(K) := l_qual_contexts_result_tbl(i).attribute_name;
2803 l_value_from_tbl(K) := l_qual_contexts_result_tbl(i).attribute_value;
2804
2805 /************** Defaulting for Java Engine ********************/
2806 l_ATTRIBUTE_LEVEL_tbl(K) := Qp_Preq_Pub.G_LINE_LEVEL;
2807 l_LIST_HEADER_ID_tbl(K) := NULL;
2808 l_LIST_LINE_ID_tbl(K) := NULL;
2809 l_SETUP_VALUE_FROM_tbl(K) := NULL;
2810 l_SETUP_VALUE_TO_tbl(K) := NULL;
2811 l_GROUPING_NUMBER_tbl(K) := NULL;
2812 l_NO_QUALIFIERS_IN_GRP_tbl(K) := NULL;
2813 l_COMPARISON_OPERATOR_TYPE_tbl(K) := NULL;
2814 l_APPLIED_FLAG_tbl(K) := Qp_Preq_Pub.G_LIST_NOT_APPLIED;
2815 l_PRICING_STATUS_CODE_tbl(K) := Qp_Preq_Pub.G_STATUS_UNCHANGED;
2816 l_PRICING_STATUS_TEXT_tbl(K) := NULL;
2817 l_QUALIFIER_PRECEDENCE_tbl(K) := NULL;
2818 l_DATATYPE_tbl(K) := NULL;
2819 l_PRICING_ATTR_FLAG_tbl(K) := Qp_Preq_Pub.G_YES;
2820 l_QUALIFIER_TYPE_tbl(K) := NULL;
2821 l_PRODUCT_UOM_CODE_tbl(K) := NULL;
2822 l_EXCLUDER_FLAG_tbl(K) := NULL;
2823 l_PRICING_PHASE_ID_tbl(K) := NULL;
2824 l_INCOMPATABILITY_GRP_CODE_tbl(K) := NULL;
2825 l_LINE_DETAIL_TYPE_CODE_tbl(K) := NULL;
2829 --changes for bug 2049125 Agreement Price lists must have
2826 l_MODIFIER_LEVEL_CODE_tbl(K) := NULL;
2827 l_PRIMARY_UOM_FLAG_tbl(K) := NULL;
2828
2830 --validated flag 'Y'
2831 -- validated flag is populated based on p_price_list_validated_flag input variable
2832 IF l_qual_contexts_result_tbl(i).context_name ='MODLIST'
2833 AND l_qual_contexts_result_tbl(i).Attribute_Name =
2834 'QUALIFIER_ATTRIBUTE4'
2835 THEN
2836
2837 IF Oe_Order_Pub.G_Line.agreement_id IS NOT NULL
2838 AND Oe_Order_Pub.G_Line.agreement_id <> Fnd_Api.g_miss_num
2839 AND NVL(p_price_list_validated_flag,'Y') = 'Y'
2840 THEN
2841 l_validated_flag_tbl(K) := 'Y';
2842 ELSIF NVL(p_price_list_validated_flag,'N') = 'N' THEN
2843 l_validated_flag_tbl(K) := 'N';
2844 ELSIF p_price_list_validated_flag = 'Y' THEN
2845 l_validated_flag_tbl(K) := 'Y';
2846 END IF;
2847 ELSE
2848 l_validated_flag_tbl(K) := 'N';
2849 END IF;
2850
2851
2852 END LOOP;
2853 --END LOOP;
2854
2855 END IF;
2856
2857 --Called Build Sourcing at header level
2858 IF l_debug = Fnd_Api.G_TRUE THEN
2859 Qp_Preq_Grp.ENGINE_DEBUG('Called Build Sourcing at header level----------');
2860
2861 END IF;
2862 v_count := 0;
2863 l_count := 0;
2864
2865 l_price_contexts_result_tbl.DELETE;
2866 l_qual_contexts_result_tbl.DELETE;
2867
2868 v_price_contexts_result_tbl.DELETE;
2869 v_qual_contexts_result_tbl.DELETE;
2870
2871 l_pricing_type_code := 'H';
2872
2873 IF (p_pricing_type_code = 'H') THEN
2874
2875 Qp_Build_Sourcing_Pvt.Get_Attribute_Values(p_req_type_code => p_request_type_code,
2876 p_pricing_type_code => p_pricing_type_code,
2877 x_qual_ctxts_result_tbl => l_qual_contexts_result_tbl,
2878 x_price_ctxts_result_tbl => l_price_contexts_result_tbl);
2879
2880 IF l_debug = Fnd_Api.G_TRUE THEN
2881 Qp_Preq_Grp.ENGINE_DEBUG('Number of qual attrs got from Get_Attribute_Values - ' || l_qual_contexts_result_tbl.COUNT );
2882 Qp_Preq_Grp.ENGINE_DEBUG('Number of pricing attrs got from Get_Attribute_Values - ' || l_price_contexts_result_tbl.COUNT );
2883
2884 END IF;
2885 /*
2886 If the attribute sourcing method is 'CUSTOM SOURCING' then user provides code to source
2887 the attributes.
2888 User code is written in the package procedure QP_CUSTOM_SOURCE.Get_Custom_Attribute_Values and
2889 Build_Contexts program calls this procedure to pickup custom sourced attributes if the
2890 profile option ' QP_CUSTOM_SOURCED' is set to 'Y' -- GTIPPIRE
2891 */
2892
2893 IF NVL(l_custom_sourced,'N') = 'Y' THEN
2894 IF l_debug = Fnd_Api.G_TRUE THEN
2895 Qp_Preq_Grp.ENGINE_DEBUG('Before Calling Custom Sourcing Package ----------');
2896 END IF;
2897 Begin
2898 qp_debug_util.tstart('GET_CUSTOM_ATTRIBUTE_VALUES','Calling the QP_CUSTOM_SOURCE package to fetch the cutom attribute values');
2899 Qp_Custom_Source.Get_Custom_Attribute_Values(p_req_type_code => p_request_type_code,
2900 p_pricing_type_code => p_pricing_type_code,
2901 x_qual_ctxts_result_tbl => v_qual_contexts_result_tbl,
2902 x_price_ctxts_result_tbl => v_price_contexts_result_tbl);
2903 qp_debug_util.tstop('GET_CUSTOM_ATTRIBUTE_VALUES');
2904 exception
2905 when others then
2906 qp_debug_util.tstop('GET_CUSTOM_ATTRIBUTE_VALUES');
2907 end;
2908
2909 IF l_debug = Fnd_Api.G_TRUE THEN
2910 Qp_Preq_Grp.ENGINE_DEBUG('After Calling Custom Sourcing Package ----------');
2911
2912 Qp_Preq_Grp.ENGINE_DEBUG('Number of qual attrs got from Get_Custom_Attribute_Values - ' || v_qual_contexts_result_tbl.COUNT );
2913 Qp_Preq_Grp.ENGINE_DEBUG('Number of pricing attrs got from Get_Custom_Attribute_Values - ' || v_price_contexts_result_tbl.COUNT );
2914
2915 END IF;
2916 l_count := l_qual_contexts_result_tbl.COUNT;
2917
2918 LOOP
2919 l_count := l_count + 1;
2920 v_count := v_count + 1;
2921 EXIT WHEN v_count > v_qual_contexts_result_tbl.COUNT;
2922 l_qual_contexts_result_tbl(l_count).context_name :=
2923 v_qual_contexts_result_tbl(v_count).context_name;
2924 l_qual_contexts_result_tbl(l_count).attribute_name :=
2925 v_qual_contexts_result_tbl(v_count).attribute_name;
2926 l_qual_contexts_result_tbl(l_count).attribute_value :=
2927 v_qual_contexts_result_tbl(v_count).attribute_value;
2928 END LOOP;
2929
2930 l_count := l_price_contexts_result_tbl.COUNT;
2931 v_count := 0;
2932
2933 LOOP
2934 l_count := l_count + 1;
2935 v_count := v_count + 1;
2936 EXIT WHEN v_count > v_price_contexts_result_tbl.COUNT;
2937 l_price_contexts_result_tbl(l_count).context_name :=
2938 v_price_contexts_result_tbl(v_count).context_name;
2939 l_price_contexts_result_tbl(l_count).attribute_name :=
2940 v_price_contexts_result_tbl(v_count).attribute_name;
2941 l_price_contexts_result_tbl(l_count).attribute_value:=
2942 v_price_contexts_result_tbl(v_count).attribute_value;
2946 /*
2943 END LOOP;
2944 END IF;
2945
2947 If the attribute sourcing method is 'ATTRIBUTE MAPPING' then the values for the attributes
2948 which are used but not yet mapped must be determined. --SFIRESTO
2949 */
2950
2951 IF Qp_Code_Control.GET_CODE_RELEASE_LEVEL > 110508 THEN
2952 v_price_contexts_result_tbl.DELETE;
2953 v_qual_contexts_result_tbl.DELETE;
2954
2955 Map_Used_But_Not_Mapped_Attrs( p_request_type_code
2956 , p_pricing_type_code
2957 , v_price_contexts_result_tbl
2958 , v_qual_contexts_result_tbl);
2959
2960 l_count := l_qual_contexts_result_tbl.COUNT;
2961 v_count := 0;
2962
2963 LOOP
2964 l_count := l_count + 1;
2965 v_count := v_count + 1;
2966
2967 EXIT WHEN v_count > v_qual_contexts_result_tbl.COUNT;
2968
2969 l_qual_contexts_result_tbl(l_count).context_name :=
2970 v_qual_contexts_result_tbl(v_count).context_name;
2971 l_qual_contexts_result_tbl(l_count).attribute_name :=
2972 v_qual_contexts_result_tbl(v_count).attribute_name;
2973 l_qual_contexts_result_tbl(l_count).attribute_value :=
2974 v_qual_contexts_result_tbl(v_count).attribute_value;
2975 END LOOP;
2976
2977 l_count := l_price_contexts_result_tbl.COUNT;
2978 v_count := 0;
2979
2980 LOOP
2981 l_count := l_count + 1;
2982 v_count := v_count + 1;
2983
2984 EXIT WHEN v_count > v_price_contexts_result_tbl.COUNT;
2985
2986 l_price_contexts_result_tbl(l_count).context_name :=
2987 v_price_contexts_result_tbl(v_count).context_name;
2988 l_price_contexts_result_tbl(l_count).attribute_name :=
2989 v_price_contexts_result_tbl(v_count).attribute_name;
2990 l_price_contexts_result_tbl(l_count).attribute_value:=
2991 v_price_contexts_result_tbl(v_count).attribute_value;
2992 END LOOP;
2993
2994 END IF;
2995
2996
2997 --FOR j IN l_order_cur
2998 --LOOP
2999 FOR i IN 1..l_price_contexts_result_tbl.COUNT
3000 LOOP
3001 --sourcing product attributes
3002 K := K + 1;
3003
3004 l_line_index_tbl(K) := p_line_index;
3005 IF l_price_contexts_result_tbl(i).context_name =
3006 Qp_Preq_Pub.G_ITEM_CONTEXT
3007 THEN
3008 l_attribute_type_tbl(K) := Qp_Preq_Pub.G_PRODUCT_TYPE;
3009 ELSE
3010 l_attribute_type_tbl(K) := Qp_Preq_Pub.G_PRICING_TYPE;
3011 END IF;
3012 l_context_tbl(K) := l_price_contexts_result_tbl(i).context_name;
3013 l_attribute_tbl(K) := l_price_contexts_result_tbl(i).attribute_name;
3014 l_value_from_tbl(K) := l_price_contexts_result_tbl(i).attribute_value;
3015 l_validated_flag_tbl(K) := 'N';
3016 /************** Defaulting for Java Engine ********************/
3017 l_ATTRIBUTE_LEVEL_tbl(K) := Qp_Preq_Pub.G_LINE_LEVEL;
3018 l_LIST_HEADER_ID_tbl(K) := NULL;
3019 l_LIST_LINE_ID_tbl(K) := NULL;
3020 l_SETUP_VALUE_FROM_tbl(K) := NULL;
3021 l_SETUP_VALUE_TO_tbl(K) := NULL;
3022 l_GROUPING_NUMBER_tbl(K) := NULL;
3023 l_NO_QUALIFIERS_IN_GRP_tbl(K) := NULL;
3024 l_COMPARISON_OPERATOR_TYPE_tbl(K) := NULL;
3025 l_APPLIED_FLAG_tbl(K) := Qp_Preq_Pub.G_LIST_NOT_APPLIED;
3026 l_PRICING_STATUS_CODE_tbl(K) := Qp_Preq_Pub.G_STATUS_UNCHANGED;
3027 l_PRICING_STATUS_TEXT_tbl(K) := NULL;
3028 l_QUALIFIER_PRECEDENCE_tbl(K) := NULL;
3029 l_DATATYPE_tbl(K) := NULL;
3030 l_PRICING_ATTR_FLAG_tbl(K) := Qp_Preq_Pub.G_YES;
3031 l_QUALIFIER_TYPE_tbl(K) := NULL;
3032 l_PRODUCT_UOM_CODE_tbl(K) := NULL;
3033 l_EXCLUDER_FLAG_tbl(K) := NULL;
3034 l_PRICING_PHASE_ID_tbl(K) := NULL;
3035 l_INCOMPATABILITY_GRP_CODE_tbl(K) := NULL;
3036 l_LINE_DETAIL_TYPE_CODE_tbl(K) := NULL;
3037 l_MODIFIER_LEVEL_CODE_tbl(K) := NULL;
3038 l_PRIMARY_UOM_FLAG_tbl(K) := NULL;
3039
3040 END LOOP;
3041
3042 FOR i IN 1..l_qual_contexts_result_tbl.COUNT
3043 LOOP
3044 --sourcing qualifier attributes
3045 K := K + 1;
3046 l_line_index_tbl(K) := p_line_index;
3047 l_attribute_type_tbl(K) := Qp_Preq_Pub.G_QUALIFIER_TYPE;
3048 l_context_tbl(K) := l_qual_contexts_result_tbl(i).context_name;
3049 l_attribute_tbl(K) := l_qual_contexts_result_tbl(i).attribute_name;
3050 l_value_from_tbl(K) := l_qual_contexts_result_tbl(i).attribute_value;
3051
3052 /************** Defaulting for Java Engine ********************/
3053 l_ATTRIBUTE_LEVEL_tbl(K) := Qp_Preq_Pub.G_LINE_LEVEL;
3054 l_LIST_HEADER_ID_tbl(K) := NULL;
3055 l_LIST_LINE_ID_tbl(K) := NULL;
3056 l_SETUP_VALUE_FROM_tbl(K) := NULL;
3057 l_SETUP_VALUE_TO_tbl(K) := NULL;
3058 l_GROUPING_NUMBER_tbl(K) := NULL;
3059 l_NO_QUALIFIERS_IN_GRP_tbl(K) := NULL;
3060 l_COMPARISON_OPERATOR_TYPE_tbl(K) := NULL;
3061 l_APPLIED_FLAG_tbl(K) := Qp_Preq_Pub.G_LIST_NOT_APPLIED;
3062 l_PRICING_STATUS_CODE_tbl(K) := Qp_Preq_Pub.G_STATUS_UNCHANGED;
3063 l_PRICING_STATUS_TEXT_tbl(K) := NULL;
3064 l_QUALIFIER_PRECEDENCE_tbl(K) := NULL;
3068 l_PRODUCT_UOM_CODE_tbl(K) := NULL;
3065 l_DATATYPE_tbl(K) := NULL;
3066 l_PRICING_ATTR_FLAG_tbl(K) := Qp_Preq_Pub.G_YES;
3067 l_QUALIFIER_TYPE_tbl(K) := NULL;
3069 l_EXCLUDER_FLAG_tbl(K) := NULL;
3070 l_PRICING_PHASE_ID_tbl(K) := NULL;
3071 l_INCOMPATABILITY_GRP_CODE_tbl(K) := NULL;
3072 l_LINE_DETAIL_TYPE_CODE_tbl(K) := NULL;
3073 l_MODIFIER_LEVEL_CODE_tbl(K) := NULL;
3074 l_PRIMARY_UOM_FLAG_tbl(K) := NULL;
3075
3076
3077 --changes for bug 2049125 Agreement Price lists must have
3078 --validated flag 'Y'
3079 IF l_qual_contexts_result_tbl(i).context_name ='MODLIST'
3080 AND l_qual_contexts_result_tbl(i).Attribute_Name =
3081 'QUALIFIER_ATTRIBUTE4'
3082 THEN
3083
3084 IF Oe_Order_Pub.G_Line.agreement_id IS NOT NULL
3085 AND Oe_Order_Pub.G_Line.agreement_id <> Fnd_Api.g_miss_num
3086 THEN
3087 l_validated_flag_tbl(K) := 'Y';
3088 ELSE
3089 l_validated_flag_tbl(K) := 'N';
3090 END IF;
3091 ELSE
3092 l_validated_flag_tbl(K) := 'N';
3093 END IF;
3094
3095 END LOOP;
3096 --END LOOP;
3097
3098 END IF;
3099
3100 IF l_debug = Fnd_Api.G_TRUE THEN
3101
3102 Qp_Preq_Grp.ENGINE_DEBUG('Printing line attributes ----------');
3103 FOR i IN 1..l_line_index_tbl.COUNT
3104 LOOP
3105 IF l_debug = Fnd_Api.G_TRUE THEN
3106 Qp_Preq_Grp.ENGINE_DEBUG('line index '||l_line_index_tbl(i));
3107 Qp_Preq_Grp.ENGINE_DEBUG('attribute type '||l_attribute_type_tbl(i));
3108 Qp_Preq_Grp.ENGINE_DEBUG('context '||l_context_tbl(i));
3109 Qp_Preq_Grp.ENGINE_DEBUG('attribute '||l_attribute_tbl(i));
3110 Qp_Preq_Grp.ENGINE_DEBUG('value from '||l_value_from_tbl(i));
3111 Qp_Preq_Grp.ENGINE_DEBUG('validated flag '||l_validated_flag_tbl(i));
3112 Qp_Preq_Grp.ENGINE_DEBUG('-----------------------------------------');
3113 END IF;
3114 END LOOP;
3115 END IF; --debug true
3116
3117
3118 IF l_line_index_tbl.COUNT > 0
3119 THEN
3120 BEGIN
3121 IF l_debug = Fnd_Api.G_TRUE THEN
3122 Qp_Preq_Grp.ENGINE_DEBUG('Tata Inserting line attributes ----------');
3123 END IF;
3124
3125 IF Qp_Java_Engine_Util_Pub.Java_Engine_Running = 'N' THEN
3126 IF l_debug = Fnd_Api.G_TRUE THEN
3127 Qp_Preq_Grp.ENGINE_DEBUG('Java Engine not Installed ----------');
3128 END IF;
3129
3130 FORALL i IN l_line_index_tbl.FIRST..l_line_index_tbl.LAST
3131 INSERT INTO qp_npreq_line_attrs_tmp
3132 (line_index,
3133 attribute_level,
3134 attribute_type,
3135 context,
3136 attribute,
3137 value_from,
3138 validated_flag,
3139 applied_flag,
3140 pricing_status_code,
3141 pricing_attr_flag
3142 )
3143 VALUES (l_line_index_tbl(i),
3144 Qp_Preq_Pub.G_LINE_LEVEL,
3145 l_attribute_type_tbl(i),
3146 l_context_tbl(i),
3147 l_attribute_tbl(i),
3148 l_value_from_tbl(i),
3149 l_validated_flag_tbl(i),
3150 Qp_Preq_Pub.G_LIST_NOT_APPLIED,
3151 Qp_Preq_Pub.G_STATUS_UNCHANGED,
3152 Qp_Preq_Pub.G_YES
3153 );
3154 ELSE -- Java Engine path added by yangli
3155 IF l_debug = Fnd_Api.G_TRUE THEN
3156 Qp_Preq_Grp.ENGINE_DEBUG('Java Engine Installed path----------');
3157 END IF;
3158 /*QP_PREQ_GRP.INSERT_LINE_ATTRS_AT( l_line_index_tbl,
3159 QP_PREQ_PUB.G_LINE_LEVEL,
3160 l_attribute_type_tbl,
3161 l_context_tbl,
3162 l_attribute_tbl,
3163 l_value_from_tbl,
3164 l_validated_flag_tbl,
3165 QP_PREQ_PUB.G_LIST_NOT_APPLIED,
3166 QP_PREQ_PUB.G_STATUS_UNCHANGED,
3167 QP_PREQ_PUB.G_YES,
3168 l_status_code ,
3169 l_status_text );
3170 */
3171
3172 --bug 3113427
3173 FOR i IN l_line_index_tbl.first..l_line_index_tbl.last
3174 LOOP
3175 l_LINE_DETAIL_INDEX_tbl (i) := NULL;
3176 l_value_to_tbl (i) := NULL;
3177 END LOOP;
3178
3179 Qp_Preq_Grp.INSERT_LINE_ATTRS2 ( p_LINE_INDEX_tbl => l_line_index_tbl,
3180 p_LINE_DETAIL_INDEX_tbl => l_line_Detail_index_tbl,
3181 p_ATTRIBUTE_LEVEL_tbl=> l_ATTRIBUTE_LEVEL_tbl,
3182 p_ATTRIBUTE_TYPE_tbl => l_attribute_type_tbl,
3183 p_LIST_HEADER_ID_tbl=> l_LIST_HEADER_ID_tbl,
3184 p_LIST_LINE_ID_tbl=> l_LIST_LINE_ID_tbl,
3185 p_CONTEXT_tbl => l_context_tbl,
3186 p_ATTRIBUTE_tbl => l_attribute_tbl,
3187 p_VALUE_FROM_tbl => l_value_from_tbl,
3188 p_SETUP_VALUE_FROM_tbl=> l_SETUP_VALUE_FROM_tbl,
3189 p_VALUE_TO_tbl => l_value_to_tbl,
3190 p_SETUP_VALUE_TO_tbl=> l_SETUP_VALUE_TO_tbl,
3191 p_GROUPING_NUMBER_tbl=> l_GROUPING_NUMBER_tbl,
3195 p_APPLIED_FLAG_tbl=> l_APPLIED_FLAG_tbl,
3192 p_NO_QUALIFIERS_IN_GRP_tbl=> l_NO_QUALIFIERS_IN_GRP_tbl,
3193 p_COMPARISON_OPERATOR_TYPE_tbl=> l_COMPARISON_OPERATOR_TYPE_tbl,
3194 p_VALIDATED_FLAG_tbl => l_validated_flag_tbl,
3196 p_PRICING_STATUS_CODE_tbl=> l_PRICING_STATUS_CODE_tbl,
3197 p_PRICING_STATUS_TEXT_tbl=> l_PRICING_STATUS_TEXT_tbl,
3198 p_QUALIFIER_PRECEDENCE_tbl=> l_QUALIFIER_PRECEDENCE_tbl,
3199 p_DATATYPE_tbl=> l_DATATYPE_tbl,
3200 p_PRICING_ATTR_FLAG_tbl=> l_PRICING_ATTR_FLAG_tbl,
3201 p_QUALIFIER_TYPE_tbl=> l_QUALIFIER_TYPE_tbl,
3202 p_PRODUCT_UOM_CODE_tbl=> l_PRODUCT_UOM_CODE_tbl,
3203 p_EXCLUDER_FLAG_tbl=> l_EXCLUDER_FLAG_tbl,
3204 p_PRICING_PHASE_ID_tbl=> l_PRICING_PHASE_ID_tbl,
3205 p_INCOMPATABILITY_GRP_CODE_tbl=> l_INCOMPATABILITY_GRP_CODE_tbl,
3206 p_LINE_DETAIL_TYPE_CODE_tbl=> l_LINE_DETAIL_TYPE_CODE_tbl,
3207 p_MODIFIER_LEVEL_CODE_tbl=> l_MODIFIER_LEVEL_CODE_tbl,
3208 p_PRIMARY_UOM_FLAG_tbl=> l_PRIMARY_UOM_FLAG_tbl,
3209 x_status_code => l_status_code,
3210 x_status_text => l_status_text);
3211 IF l_status_code = Fnd_Api.G_RET_STS_ERROR THEN
3212 RAISE E_ROUTINE_ERRORS;
3213 END IF;
3214 END IF;
3215
3216 IF l_debug = Fnd_Api.G_TRUE THEN
3217 Qp_Preq_Grp.ENGINE_DEBUG('End Inserting line attributes ----------');
3218 END IF;
3219 EXCEPTION
3220 WHEN E_ROUTINE_ERRORS THEN
3221 IF l_debug = Fnd_Api.G_TRUE THEN
3222 Qp_Preq_Grp.engine_debug('QP_ATTR_MAPPING_PUB:Bld Contxt Insert LINE_ATTR '||''||l_status_text);
3223 END IF;
3224 WHEN OTHERS THEN
3225 IF l_debug = Fnd_Api.G_TRUE THEN
3226 Qp_Preq_Grp.engine_debug('QP_ATTR_MAPPING_PUB:Bld Contxt Insert LINE_ATTR '||' '||SQLERRM);
3227 END IF;
3228 END;
3229
3230 END IF;
3231
3232 IF l_debug = Fnd_Api.G_TRUE THEN
3233 Qp_Preq_Grp.ENGINE_DEBUG('End Build contexts');
3234
3235 END IF;
3236 --setting time
3237 l_sourcing_end_time := dbms_utility.get_time;
3238 l_time_difference := (l_sourcing_end_time - l_sourcing_start_time)/100 ;
3239
3240 IF l_debug = Fnd_Api.G_TRUE THEN
3241 Oe_Debug_Pub.ADD('##### Total Time in Build_Contexts(in sec) : ' || l_time_difference || ' #####');
3242
3243 END IF;
3244 EXCEPTION
3245 WHEN OTHERS THEN
3246 IF l_debug = Fnd_Api.G_TRUE THEN
3247 Qp_Preq_Grp.ENGINE_DEBUG('Exception in QP_ATTR_MAPPING_PUB.Build_context '||SQLERRM);
3248
3249 END IF;
3250 END Build_Contexts;
3251
3252
3253
3254 PROCEDURE Get_User_Item_Pricing_Attribs
3255 ( p_request_type_code IN VARCHAR2
3256 , p_item_id IN VARCHAR2
3257 , p_user_attribs_tbl OUT NOCOPY USER_ATTRIBUTE_TBL_TYPE
3258 )
3259 IS
3260
3261 v_item_category VARCHAR2(60);
3262 l_found VARCHAR2(1);
3263 v_pricing_attr_ctxt VARCHAR2(240); --4932085, 4960278
3264 v_pricing_attr VARCHAR2(240); --4932085, 4960278
3265 l_condition_id VARCHAR2(60);
3266 l_context_name VARCHAR2(240); --4932085, 4960278
3267 l_attr_def_condition_id VARCHAR2(60);
3268 v_count BINARY_INTEGER := 1;
3269 l_context_code VARCHAR2(30);
3270 l_context_type VARCHAR2(30);
3271 l_segment_code VARCHAR2(30);
3272 l_segment_mapping_column VARCHAR2(30);
3273
3274 CURSOR l_pricing_attribs IS
3275 SELECT DISTINCT pricing_attribute_context, pricing_attribute
3276 FROM qp_pricing_attributes
3277 WHERE product_attribute = 'PRICING_ATTRIBUTE1'
3278 AND product_attribute_context = 'ITEM'
3279 AND product_attr_value = p_item_id
3280 AND pricing_attribute_context IS NOT NULL
3281 AND pricing_attribute IS NOT NULL;
3282
3283 CURSOR l_cond_cursor(p_pricing_attr VARCHAR2) IS
3284 SELECT c.condition_id, d.attr_def_condition_id
3285 FROM oe_def_conditions a, oe_def_condn_elems b,
3286 oe_def_attr_condns c, oe_def_attr_def_rules d, qp_price_req_sources e
3287 WHERE a.database_object_name LIKE 'QP%'
3288 AND a.condition_id = b.condition_id
3289 AND b.attribute_code = 'SRC_SYSTEM_CODE'
3290 AND b.value_string = e.source_system_code
3291 AND e.request_type_code = p_request_type_code
3292 AND b.condition_id = c.condition_id
3293 AND c.attribute_code = p_pricing_attr
3294 AND c.attr_def_condition_id = d.attr_def_condition_id;
3295
3296 CURSOR l_cond_cursor_new(p_pricing_attr VARCHAR2) IS
3297 SELECT DISTINCT qpseg.segment_code, qpseg.segment_mapping_column,
3298 qpcon.prc_context_code, qpcon.prc_context_type
3299 FROM qp_segments_b qpseg, qp_prc_contexts_b qpcon,
3300 qp_pte_segments qppteseg, qp_pte_request_types_b qpptereq
3301 WHERE qpseg.segment_id = qppteseg.segment_id AND
3302 qpseg.segment_mapping_column = p_pricing_attr AND
3303 qpseg.prc_context_id = qpcon.prc_context_id AND
3304 qpcon.enabled_flag = 'Y' AND
3305 qpptereq.request_type_code = p_request_type_code AND
3306 qpptereq.pte_code = qppteseg.pte_code AND
3307 qppteseg.user_sourcing_method = 'USER ENTERED';
3308
3309 BEGIN
3310
3311 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
3312 --FND_PROFILE.GET('QP_ATTRIBUTE_MANAGER_INSTALLED',G_ATTRMGR_INSTALLED);
3313 G_ATTRMGR_INSTALLED := Qp_Util.Attrmgr_Installed;
3314
3318 LOOP
3315 IF NVL(G_ATTRMGR_INSTALLED,'N') = 'N' THEN
3316 OPEN l_pricing_attribs;
3317
3319
3320 FETCH l_pricing_attribs INTO v_pricing_attr_ctxt, v_pricing_attr;
3321 EXIT WHEN l_pricing_attribs%NOTFOUND;
3322
3323 IF l_debug = Fnd_Api.G_TRUE THEN
3324 Oe_Debug_Pub.ADD('Item Context: ' || v_pricing_attr_ctxt);
3325 Oe_Debug_Pub.ADD('Item Attribute: ' || v_pricing_attr);
3326
3327 END IF;
3328 l_found := 0;
3329
3330 OPEN l_cond_cursor(v_pricing_attr);
3331
3332 LOOP
3333
3334 FETCH l_cond_cursor INTO l_condition_id, l_attr_def_condition_id;
3335 EXIT WHEN l_cond_cursor%NOTFOUND;
3336
3337 SELECT value_string
3338 INTO l_context_name
3339 FROM oe_def_condn_elems
3340 WHERE condition_id = l_condition_id
3341 AND attribute_code = 'PRICING_CONTEXT';
3342
3343 IF l_debug = Fnd_Api.G_TRUE THEN
3344 Oe_Debug_Pub.ADD('Sourced Context: ' || l_context_name);
3345 Oe_Debug_Pub.ADD('Compared Context: ' || v_pricing_attr_ctxt);
3346 END IF;
3347
3348 IF l_context_name = v_pricing_attr_ctxt THEN
3349
3350 l_found := 1;
3351 IF l_debug = Fnd_Api.G_TRUE THEN
3352 Oe_Debug_Pub.ADD('Found : ' || l_found);
3353
3354 END IF;
3355 END IF;
3356
3357
3358 END LOOP;
3359 CLOSE l_cond_cursor;
3360
3361 IF l_found = 0 THEN
3362
3363 IF l_debug = Fnd_Api.G_TRUE THEN
3364 Oe_Debug_Pub.ADD('Not Found context: ' || v_pricing_attr_ctxt);
3365 Oe_Debug_Pub.ADD('Not Found attribute: ' || v_pricing_attr);
3366 END IF;
3367 p_user_attribs_tbl(v_count).context_name := v_pricing_attr_ctxt;
3368 p_user_attribs_tbl(v_count).attribute_name := v_pricing_attr;
3369 v_count := v_count + 1;
3370
3371 END IF;
3372
3373 END LOOP;
3374 CLOSE l_pricing_attribs;
3375 ELSIF NVL(G_ATTRMGR_INSTALLED,'N') = 'Y' THEN
3376
3377 OPEN l_pricing_attribs;
3378
3379 LOOP
3380
3381 FETCH l_pricing_attribs INTO v_pricing_attr_ctxt, v_pricing_attr;
3382 EXIT WHEN l_pricing_attribs%NOTFOUND;
3383
3384 IF l_debug = Fnd_Api.G_TRUE THEN
3385 Oe_Debug_Pub.ADD('Item Context: ' || v_pricing_attr_ctxt);
3386 Oe_Debug_Pub.ADD('Item Attribute: ' || v_pricing_attr);
3387
3388 END IF;
3389 l_found := 0;
3390
3391 OPEN l_cond_cursor_new(v_pricing_attr);
3392
3393 LOOP
3394
3395 FETCH l_cond_cursor_new INTO l_segment_code, l_segment_mapping_column,
3396 l_context_code,l_context_type;
3397 EXIT WHEN l_cond_cursor_new%NOTFOUND;
3398
3399 IF l_debug = Fnd_Api.G_TRUE THEN
3400 Oe_Debug_Pub.ADD('Sourced Context: ' || l_context_code);
3401 Oe_Debug_Pub.ADD('Compared Context: ' || v_pricing_attr_ctxt);
3402 END IF;
3403
3404 IF l_context_code = v_pricing_attr_ctxt THEN
3405
3406 l_found := 1;
3407 IF l_debug = Fnd_Api.G_TRUE THEN
3408 Oe_Debug_Pub.ADD('Found : ' || l_found);
3409
3410 END IF;
3411 END IF;
3412
3413 END LOOP;
3414 CLOSE l_cond_cursor_new;
3415
3416 IF l_found = 1 THEN
3417
3418 IF l_debug = Fnd_Api.G_TRUE THEN
3419 Oe_Debug_Pub.ADD('Found User Entered context: ' || v_pricing_attr_ctxt);
3420 Oe_Debug_Pub.ADD('Found User Entered attribute: ' || v_pricing_attr);
3421 END IF;
3422 p_user_attribs_tbl(v_count).context_name := v_pricing_attr_ctxt;
3423 p_user_attribs_tbl(v_count).attribute_name := v_pricing_attr;
3424 v_count := v_count + 1;
3425
3426 END IF;
3427
3428 END LOOP;
3429 CLOSE l_pricing_attribs;
3430 END IF;
3431 END Get_User_Item_Pricing_Attribs;
3432
3433 PROCEDURE Get_User_Item_Pricing_Attribs
3434 ( p_request_type_code IN VARCHAR2
3435 , p_user_attribs_tbl OUT NOCOPY USER_ATTRIBUTE_TBL_TYPE
3436 )
3437 IS
3438
3439 l_found VARCHAR2(1);
3440 v_pricing_attr_ctxt VARCHAR2(240); --4932085, 4960278
3441 v_pricing_attr VARCHAR2(240); --4932085, 4960278
3442 l_condition_id VARCHAR2(60);
3443 l_context_name VARCHAR2(240); --4932085, 4960278
3444 l_attr_def_condition_id VARCHAR2(60);
3445 v_count BINARY_INTEGER := 1;
3446 l_context_code VARCHAR2(30);
3447 l_context_type VARCHAR2(30);
3448 l_segment_code VARCHAR2(30);
3449 l_segment_mapping_column VARCHAR2(30);
3450
3451 CURSOR l_pricing_attribs IS
3452 SELECT DISTINCT pricing_attribute_context, pricing_attribute
3453 FROM qp_pricing_attributes
3454 WHERE pricing_attribute_context IS NOT NULL
3455 AND pricing_attribute_context NOT IN ('VOLUME','ITEM')
3456 AND pricing_attribute_context <> Fnd_Api.G_MISS_CHAR
3457 AND pricing_attribute <> Fnd_Api.G_MISS_CHAR
3458 AND pricing_attribute IS NOT NULL;
3459
3460 CURSOR l_cond_cursor(p_pricing_attr VARCHAR2) IS
3461 SELECT c.condition_id, d.attr_def_condition_id
3465 AND a.condition_id = b.condition_id
3462 FROM oe_def_conditions a, oe_def_condn_elems b,
3463 oe_def_attr_condns c, oe_def_attr_def_rules d, qp_price_req_sources e
3464 WHERE a.database_object_name LIKE 'QP%'
3466 AND b.attribute_code = 'SRC_SYSTEM_CODE'
3467 AND b.value_string = e.source_system_code
3468 AND e.request_type_code = p_request_type_code
3469 AND b.condition_id = c.condition_id
3470 AND c.attribute_code = p_pricing_attr
3471 AND c.attr_def_condition_id = d.attr_def_condition_id;
3472
3473 CURSOR l_cond_cursor_new(p_pricing_attr VARCHAR2) IS
3474 SELECT DISTINCT qpseg.segment_code, qpseg.segment_mapping_column,
3475 qpcon.prc_context_code, qpcon.prc_context_type
3476 FROM qp_segments_b qpseg, qp_prc_contexts_b qpcon,
3477 qp_pte_segments qppteseg, qp_pte_request_types_b qpptereq
3478 WHERE qpseg.segment_id = qppteseg.segment_id AND
3479 qpseg.segment_mapping_column = p_pricing_attr AND
3480 qpseg.prc_context_id = qpcon.prc_context_id AND
3481 qpcon.enabled_flag = 'Y' AND
3482 qpptereq.request_type_code = p_request_type_code AND
3483 qpptereq.pte_code = qppteseg.pte_code AND
3484 qppteseg.user_sourcing_method = 'USER ENTERED';
3485
3486 BEGIN
3487
3488 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
3489 --FND_PROFILE.GET('QP_ATTRIBUTE_MANAGER_INSTALLED',G_ATTRMGR_INSTALLED);
3490 G_ATTRMGR_INSTALLED := Qp_Util.Attrmgr_Installed;
3491
3492 IF NVL(G_ATTRMGR_INSTALLED,'N') = 'N' THEN
3493 OPEN l_pricing_attribs;
3494
3495 LOOP
3496
3497 FETCH l_pricing_attribs INTO v_pricing_attr_ctxt, v_pricing_attr;
3498 EXIT WHEN l_pricing_attribs%NOTFOUND;
3499
3500 IF l_debug = Fnd_Api.G_TRUE THEN
3501 Oe_Debug_Pub.ADD('Pricing Context: ' || NVL(v_pricing_attr_ctxt,'Jeff'));
3502 Oe_Debug_Pub.ADD('Pricing Attribute: ' || v_pricing_attr);
3503
3504 END IF;
3505 l_found := 0;
3506
3507 OPEN l_cond_cursor(v_pricing_attr);
3508
3509 LOOP
3510
3511 FETCH l_cond_cursor INTO l_condition_id, l_attr_def_condition_id;
3512 EXIT WHEN l_cond_cursor%NOTFOUND;
3513
3514 SELECT value_string
3515 INTO l_context_name
3516 FROM oe_def_condn_elems
3517 WHERE condition_id = l_condition_id
3518 AND attribute_code = 'PRICING_CONTEXT';
3519
3520 IF l_debug = Fnd_Api.G_TRUE THEN
3521 Oe_Debug_Pub.ADD('Sourced Context: ' || l_context_name);
3522 Oe_Debug_Pub.ADD('Compared Context: ' || v_pricing_attr_ctxt);
3523 END IF;
3524
3525 IF l_context_name = v_pricing_attr_ctxt THEN
3526
3527 l_found := 1;
3528 IF l_debug = Fnd_Api.G_TRUE THEN
3529 Oe_Debug_Pub.ADD('Found : ' || l_found);
3530
3531 END IF;
3532 END IF;
3533
3534
3535 END LOOP;
3536 CLOSE l_cond_cursor;
3537
3538 IF l_found = 0 THEN
3539
3540 IF l_debug = Fnd_Api.G_TRUE THEN
3541 Oe_Debug_Pub.ADD('Not Found context: ' || v_pricing_attr_ctxt);
3542 Oe_Debug_Pub.ADD('Not Found attribute: ' || v_pricing_attr);
3543 END IF;
3544 p_user_attribs_tbl(v_count).context_name := v_pricing_attr_ctxt;
3545 p_user_attribs_tbl(v_count).attribute_name := v_pricing_attr;
3546 v_count := v_count + 1;
3547
3548 END IF;
3549 END LOOP;
3550 CLOSE l_pricing_attribs;
3551
3552 v_count := v_count - 1;
3553 IF l_debug = Fnd_Api.G_TRUE THEN
3554 Oe_Debug_Pub.ADD('Number of Non-sourced attributes : ' || v_count);
3555 END IF;
3556 ELSIF NVL(G_ATTRMGR_INSTALLED,'N') = 'Y' THEN
3557 OPEN l_pricing_attribs;
3558
3559 LOOP
3560
3561 FETCH l_pricing_attribs INTO v_pricing_attr_ctxt, v_pricing_attr;
3562 EXIT WHEN l_pricing_attribs%NOTFOUND;
3563
3564 IF l_debug = Fnd_Api.G_TRUE THEN
3565 Oe_Debug_Pub.ADD('Pricing Context: ' || NVL(v_pricing_attr_ctxt,'Jeff'));
3566 Oe_Debug_Pub.ADD('Pricing Attribute: ' || v_pricing_attr);
3567
3568 END IF;
3569 l_found := 0;
3570
3571 OPEN l_cond_cursor_new(v_pricing_attr);
3572
3573 LOOP
3574
3575 FETCH l_cond_cursor_new INTO l_segment_code, l_segment_mapping_column,
3576 l_context_code,l_context_type;
3577 EXIT WHEN l_cond_cursor_new%NOTFOUND;
3578
3579 IF l_debug = Fnd_Api.G_TRUE THEN
3580 Oe_Debug_Pub.ADD('Sourced Context: ' || l_context_code);
3581 Oe_Debug_Pub.ADD('Compared Context: ' || v_pricing_attr_ctxt);
3582 END IF;
3583
3584 IF l_context_code = v_pricing_attr_ctxt THEN
3585
3586 l_found := 1;
3587 IF l_debug = Fnd_Api.G_TRUE THEN
3588 Oe_Debug_Pub.ADD('Found : ' || l_found);
3589
3590 END IF;
3591 END IF;
3592
3593 END LOOP;
3594 CLOSE l_cond_cursor_new;
3595
3596 IF l_found = 1 THEN
3597
3598 IF l_debug = Fnd_Api.G_TRUE THEN
3599 Oe_Debug_Pub.ADD('Found user entered context: ' || v_pricing_attr_ctxt);
3600 Oe_Debug_Pub.ADD('Found user entered attribute: ' || v_pricing_attr);
3601 END IF;
3605
3602 p_user_attribs_tbl(v_count).context_name := v_pricing_attr_ctxt;
3603 p_user_attribs_tbl(v_count).attribute_name := v_pricing_attr;
3604 v_count := v_count + 1;
3606 END IF;
3607 END LOOP;
3608 CLOSE l_pricing_attribs;
3609
3610 v_count := v_count - 1;
3611 IF l_debug = Fnd_Api.G_TRUE THEN
3612 Oe_Debug_Pub.ADD('Number of Non-sourced attributes : ' || v_count);
3613 END IF;
3614 END IF;
3615
3616 END Get_User_Item_Pricing_Attribs;
3617
3618
3619 PROCEDURE Get_User_Item_Pricing_Contexts
3620 ( p_request_type_code IN VARCHAR2
3621 , p_user_attribs_tbl OUT NOCOPY USER_ATTRIBUTE_TBL_TYPE
3622 )
3623 IS
3624
3625 l_found VARCHAR2(1);
3626 v_pricing_attr_ctxt VARCHAR2(60);
3627 l_condition_id VARCHAR2(60);
3628 l_context_name VARCHAR2(60);
3629 v_count BINARY_INTEGER := 1;
3630 l_context_code VARCHAR2(30);
3631 l_context_type VARCHAR2(30);
3632
3633 CURSOR l_pricing_contexts IS
3634 SELECT descriptive_flex_context_code
3635 FROM fnd_descr_flex_contexts
3636 WHERE enabled_flag = 'Y'
3637 AND application_id = 661
3638 AND descriptive_flexfield_name = 'QP_ATTR_DEFNS_PRICING';
3639
3640
3641 CURSOR l_sourced_contexts IS
3642 SELECT DISTINCT b.condition_id
3643 FROM oe_def_conditions a, oe_def_condn_elems b, qp_price_req_sources c,
3644 oe_def_attr_condns d
3645 WHERE a.database_object_name LIKE 'QP%'
3646 AND a.condition_id = b.condition_id
3647 AND b.attribute_code = 'SRC_SYSTEM_CODE'
3648 AND b.value_string = c.source_system_code
3649 AND c.request_type_code = p_request_type_code
3650 AND d.condition_id = a.condition_id
3651 AND d.attribute_code LIKE 'PRICING_ATTRIBUTE%';
3652
3653 CURSOR l_non_sourced_contexts IS
3654 SELECT DISTINCT qpcon.prc_context_code, qpcon.prc_context_type
3655 FROM qp_segments_b qpseg, qp_prc_contexts_b qpcon,
3656 qp_pte_segments qppteseg, qp_pte_request_types_b qpptereq
3657 WHERE qpseg.segment_id = qppteseg.segment_id AND
3658 qpseg.prc_context_id = qpcon.prc_context_id AND
3659 qpcon.enabled_flag = 'Y' AND
3660 qpcon.PRC_CONTEXT_TYPE IN ('PRICING_ATTRIBUTE', 'PRODUCT') AND
3661 qpptereq.request_type_code = p_request_type_code AND
3662 qpptereq.pte_code = qppteseg.pte_code AND
3663 qppteseg.user_sourcing_method = 'USER ENTERED';
3664 BEGIN
3665
3666 l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
3667 --FND_PROFILE.GET('QP_ATTRIBUTE_MANAGER_INSTALLED',G_ATTRMGR_INSTALLED);
3668 G_ATTRMGR_INSTALLED := Qp_Util.Attrmgr_Installed;
3669
3670 IF NVL(G_ATTRMGR_INSTALLED,'N') = 'N' THEN
3671 OPEN l_pricing_contexts;
3672
3673 LOOP
3674
3675 FETCH l_pricing_contexts INTO v_pricing_attr_ctxt;
3676 EXIT WHEN l_pricing_contexts%NOTFOUND;
3677
3678 IF l_debug = Fnd_Api.G_TRUE THEN
3679 Oe_Debug_Pub.ADD('Pricing Context: ' || NVL(v_pricing_attr_ctxt,'Jeff'));
3680
3681 END IF;
3682 l_found := 0;
3683
3684 OPEN l_sourced_contexts;
3685
3686 LOOP
3687
3688 FETCH l_sourced_contexts INTO l_condition_id;
3689 EXIT WHEN l_sourced_contexts%NOTFOUND;
3690
3691 SELECT value_string
3692 INTO l_context_name
3693 FROM oe_def_condn_elems
3694 WHERE condition_id = l_condition_id
3695 AND attribute_code = 'PRICING_CONTEXT';
3696
3697 IF l_debug = Fnd_Api.G_TRUE THEN
3698 Oe_Debug_Pub.ADD('Sourced Context: ' || l_context_name);
3699 Oe_Debug_Pub.ADD('Compared Context: ' || v_pricing_attr_ctxt);
3700 END IF;
3701
3702 IF (l_context_name = v_pricing_attr_ctxt) THEN
3703 l_found := 1;
3704 IF l_debug = Fnd_Api.G_TRUE THEN
3705 Oe_Debug_Pub.ADD('Found : ' || l_found);
3706 END IF;
3707 END IF;
3708
3709 END LOOP;
3710 CLOSE l_sourced_contexts;
3711
3712 IF l_found = 0 THEN
3713 IF l_debug = Fnd_Api.G_TRUE THEN
3714 Oe_Debug_Pub.ADD('Not Found context: ' || v_pricing_attr_ctxt);
3715 END IF;
3716 p_user_attribs_tbl(v_count).context_name := v_pricing_attr_ctxt;
3717 v_count := v_count + 1;
3718 END IF;
3719 END LOOP;
3720 CLOSE l_pricing_contexts;
3721
3722 v_count := v_count - 1;
3723 IF l_debug = Fnd_Api.G_TRUE THEN
3724 Oe_Debug_Pub.ADD('Number of Non-sourced contexts : ' || v_count);
3725 END IF;
3726 ELSIF NVL(G_ATTRMGR_INSTALLED,'N') = 'Y' THEN
3727 OPEN l_non_sourced_contexts;
3728
3729 LOOP
3730
3731 FETCH l_non_sourced_contexts INTO l_context_code, l_context_type;
3732 EXIT WHEN l_non_sourced_contexts%NOTFOUND;
3733 IF l_debug = Fnd_Api.G_TRUE THEN
3734 Oe_Debug_Pub.ADD('User Entered Context: ' || l_context_code);
3735 END IF;
3736 p_user_attribs_tbl(v_count).context_name := l_context_code;
3737 v_count := v_count + 1;
3738 END LOOP;
3739 CLOSE l_non_sourced_contexts;
3740
3741 v_count := v_count - 1;
3742 IF l_debug = Fnd_Api.G_TRUE THEN
3743 Oe_Debug_Pub.ADD('Number of Non-sourced contexts : ' || v_count);
3744 END IF;
3748 END Get_User_Item_Pricing_Contexts;
3745 END IF;
3746
3747
3749
3750 FUNCTION Is_Attribute_Used(p_attribute_context IN VARCHAR2, p_attribute_code IN VARCHAR2) RETURN VARCHAR2
3751 IS
3752
3753 x_out VARCHAR2(1) := 'N';
3754
3755 BEGIN
3756
3757 BEGIN
3758
3759 SELECT 'Y'
3760 INTO x_out
3761 FROM qp_price_formula_lines
3762 WHERE pricing_attribute_context = p_attribute_context
3763 AND pricing_attribute = p_attribute_code
3764 AND ROWNUM < 2;
3765
3766 EXCEPTION
3767 WHEN NO_DATA_FOUND THEN
3768 BEGIN
3769 SELECT /*+ index(qp_pricing_attributes qp_pricing_attributes_n4) */ 'Y'
3770 INTO x_out
3771 FROM qp_pricing_attributes
3772 WHERE pricing_attribute_context = p_attribute_context
3773 AND pricing_attribute = p_attribute_code
3774 AND ROWNUM < 2;
3775
3776 EXCEPTION
3777 WHEN NO_DATA_FOUND THEN
3778 x_out := 'N';
3779 END;
3780
3781 END;
3782
3783 RETURN x_out;
3784
3785 END Is_Attribute_Used;
3786
3787 /*************************************************************************
3788 ***********************Fix for bug 2491269********************************
3789 **********Changed lines API pass only dependent lines*********************
3790 *************************************************************************/
3791
3792 PROCEDURE Check_line_group_items(p_pricing_type_code IN VARCHAR2) IS
3793 --l_prod_exists_tbl QP_PREQ_GRP.FLAG_TYPE;
3794 l_prod_exists VARCHAR2(1) := 'N';
3795 l_all_items_exist VARCHAR2(1) := 'N';
3796 BEGIN
3797 BEGIN
3798 SELECT 'Y' INTO l_all_items_exist
3799 FROM qp_event_phases evt, qp_adv_mod_products item
3800 WHERE INSTR(G_PRICING_EVENT, evt.pricing_event_code) > 0
3801 AND item.pricing_phase_id = evt.pricing_phase_id
3802 AND item.product_attribute = 'PRICING_ATTRIBUTE3'
3803 AND item.product_attr_value = 'ALL'
3804 AND ROWNUM = 1;
3805 EXCEPTION
3806 WHEN NO_DATA_FOUND THEN
3807 l_all_items_exist := 'N';
3808 WHEN OTHERS THEN
3809 l_all_items_exist := 'N';
3810 IF l_debug = Fnd_Api.G_TRUE THEN
3811 Oe_Debug_Pub.ADD('In exception l_all_items_exist:'||SQLERRM);
3812 END IF;
3813 END;
3814
3815 IF l_debug = Fnd_Api.G_TRUE THEN
3816 Oe_Debug_Pub.ADD('In check_line_group_items l_all_items_exist '
3817 ||l_all_items_exist);
3818 Oe_Debug_Pub.ADD('In check_line_group_items G_Product_Attr_tbl.COUNT '
3819 ||G_Product_Attr_tbl.COUNT);
3820 Oe_Debug_Pub.ADD('In check_line_group_items p_pricing_type_code '
3821 ||p_pricing_type_code||' G_CHECK_LINE_FLAG '||G_CHECK_LINE_FLAG);
3822 END IF;
3823 IF G_CHECK_LINE_FLAG = 'N'
3824 OR p_pricing_type_code = 'H'
3825 OR l_all_items_exist = 'Y'
3826 THEN
3827 G_PASS_THIS_LINE := 'Y';
3828 ELSIF G_Product_Attr_tbl.COUNT = 0
3829 THEN
3830 G_PASS_THIS_LINE := 'N';
3831 ELSE
3832 FOR i IN G_Product_Attr_tbl.FIRST..G_Product_Attr_tbl.LAST
3833 LOOP
3834 IF l_debug = Fnd_Api.G_TRUE THEN
3835 Oe_Debug_Pub.ADD('Looping thru prod attr '
3836 ||'attribute '||G_Product_Attr_tbl(i).attribute_name
3837 ||' value '||G_Product_Attr_tbl(i).attribute_value);
3838 END IF;
3839 BEGIN
3840 SELECT 'Y' INTO l_prod_exists
3841 FROM qp_adv_mod_products item, qp_event_phases evt
3842 WHERE INSTR(G_PRICING_EVENT, evt.pricing_event_code) > 0
3843 AND item.pricing_phase_id = evt.pricing_phase_id
3844 AND item.product_attribute =
3845 G_Product_Attr_tbl(i).attribute_name
3846 AND item.product_attr_value =
3847 G_Product_Attr_tbl(i).attribute_value
3848 AND ROWNUM = 1;
3849 EXCEPTION
3850 WHEN NO_DATA_FOUND THEN
3851 NULL;
3852 WHEN OTHERS THEN
3853 IF l_debug = Fnd_Api.G_TRUE THEN
3854 Oe_Debug_Pub.ADD('In exception G_PASS_THIS_LINE:'
3855 ||SQLERRM);
3856 END IF;
3857 END;
3858 IF l_debug = Fnd_Api.G_TRUE THEN
3859 Oe_Debug_Pub.ADD('In check_line_group_items loop '
3860 ||l_prod_exists);
3861 END IF;
3862 IF l_prod_exists = 'Y'
3863 THEN
3864 EXIT;
3865 END IF;
3866 END LOOP;
3867 IF l_prod_exists = 'Y'
3868 THEN
3869 G_PASS_THIS_LINE := 'Y';
3870 IF l_debug = Fnd_Api.G_TRUE THEN
3871 Oe_Debug_Pub.ADD('Check_line_group_items '
3875 G_PASS_THIS_LINE := 'N';
3872 ||G_PASS_THIS_LINE);
3873 END IF;
3874 ELSE
3876 IF l_debug = Fnd_Api.G_TRUE THEN
3877 Oe_Debug_Pub.ADD('Check_line_group_items '
3878 ||G_PASS_THIS_LINE);
3879 END IF;
3880 END IF;
3881
3882 END IF;
3883 IF l_debug = Fnd_Api.G_TRUE THEN
3884 Oe_Debug_Pub.ADD('Check_line_group_items '
3885 ||'Pass Line: '||G_PASS_THIS_LINE);
3886 END IF;
3887 EXCEPTION
3888 WHEN OTHERS THEN
3889 IF l_debug = Fnd_Api.G_TRUE THEN
3890 Oe_Debug_Pub.ADD('Error in QP_ATTR_MAPPING_PUB.Check_line_group_items '||SQLERRM);
3891 END IF;
3892 G_PASS_THIS_LINE := 'Y';
3893 END Check_line_group_items;
3894
3895 --Overloaded in the pl/sql path for changed lines linegroup item dependency
3896 PROCEDURE Build_Contexts
3897 ( p_request_type_code IN VARCHAR2
3898 , p_pricing_type IN VARCHAR2
3899 , p_check_line_flag IN VARCHAR2
3900 , p_pricing_event IN VARCHAR2
3901 --added for MOAC
3902 , p_org_id IN NUMBER DEFAULT NULL
3903 , x_price_contexts_result_tbl OUT NOCOPY CONTEXTS_RESULT_TBL_TYPE
3904 , x_qual_contexts_result_tbl OUT NOCOPY CONTEXTS_RESULT_TBL_TYPE
3905 , x_pass_line OUT NOCOPY VARCHAR2
3906 ) IS
3907 BEGIN
3908 --delete all to start with
3909 G_PRODUCT_ATTR_TBL.DELETE;
3910 G_CHECK_LINE_FLAG := p_check_line_flag;
3911 G_PRICING_EVENT := p_pricing_event||',';
3912 IF l_debug = Fnd_Api.G_TRUE THEN
3913 Oe_Debug_Pub.ADD('p_check_line_flag '||p_check_line_flag);
3914 Oe_Debug_Pub.ADD('p_pricing_event '||p_pricing_event);
3915 Oe_Debug_Pub.ADD('p_pricing_type_code '||p_pricing_type);
3916 END IF;
3917 Qp_Attr_Mapping_Pub.G_REQ_TYPE_CODE := p_request_type_code; --bug3848849
3918 Build_Contexts
3919 ( p_request_type_code => p_request_type_code
3920 , p_pricing_type => p_pricing_type
3921 --added for MOAC
3922 , p_org_id => p_org_id
3923 , x_price_contexts_result_tbl => x_price_contexts_result_tbl
3924 , x_qual_contexts_result_tbl => x_qual_contexts_result_tbl
3925 );
3926
3927 IF x_price_contexts_result_tbl.COUNT > 0 THEN
3928 FOR i IN x_price_contexts_result_tbl.FIRST..x_price_contexts_result_tbl.LAST
3929 LOOP
3930 IF l_debug = Fnd_Api.G_TRUE THEN
3931 Oe_Debug_Pub.ADD('After sourcing ret '||x_price_contexts_result_tbl(i).context_name||' '||x_price_contexts_result_tbl(i).attribute_name||' '||x_price_contexts_result_tbl(i).attribute_value);
3932 END IF;
3933 END LOOP;
3934 END IF;
3935
3936
3937 --Indicates to caller that they need to pass this line
3938 x_pass_line := G_PASS_THIS_LINE;
3939 IF l_debug = Fnd_Api.G_TRUE THEN
3940 Oe_Debug_Pub.ADD('Build_contexts pass this line '||x_pass_line);
3941
3942 END IF;
3943 EXCEPTION
3944 WHEN OTHERS THEN
3945 IF l_debug = Fnd_Api.G_TRUE THEN
3946 Oe_Debug_Pub.ADD('Exception in QP_ATTR_MAPPING_PUB.Build_context'||SQLERRM);
3947 END IF;
3948 END Build_Contexts;
3949
3950 /*Fix for bug 2491269
3951 Overloaded for enhancing performance to pass all/changed lines to
3952 the pricing engine to tell caller whether or not to pass given line*/
3953 PROCEDURE Build_Contexts
3954 ( p_request_type_code IN VARCHAR2,
3955 p_line_index IN NUMBER,
3956 p_check_line_flag IN VARCHAR2,
3957 p_pricing_event IN VARCHAR2,
3958 p_pricing_type_code IN VARCHAR2,
3959 p_price_list_validated_flag IN VARCHAR2,
3960 --added for MOAC
3961 p_org_id IN NUMBER DEFAULT NULL,
3962 x_pass_line OUT NOCOPY VARCHAR2
3963 )
3964 IS
3965 BEGIN
3966 G_CHECK_LINE_FLAG := p_check_line_flag;
3967 G_PRICING_EVENT := p_pricing_event||',';
3968 IF l_debug = Fnd_Api.G_TRUE THEN
3969 Oe_Debug_Pub.ADD('p_check_line_flag '||p_check_line_flag);
3970 Oe_Debug_Pub.ADD('p_pricing_event '||p_pricing_event);
3971 Oe_Debug_Pub.ADD('p_pricing_type_code '||p_pricing_type_code);
3972
3973 END IF;
3974 Qp_Attr_Mapping_Pub.G_REQ_TYPE_CODE := p_request_type_code; --bug3848849
3975 Build_Contexts
3976 ( p_request_type_code => p_request_type_code,
3977 p_line_index => p_line_index,
3978 p_pricing_type_code => p_pricing_type_code,
3979 p_price_list_validated_flag => p_price_list_validated_flag,
3980 --added for MOAC
3981 p_org_id => p_org_id
3982 );
3983 --Indicates to caller that they need to pass this line
3984 x_pass_line := G_PASS_THIS_LINE;
3985 IF l_debug = Fnd_Api.G_TRUE THEN
3986 Oe_Debug_Pub.ADD('Build_contexts pass this line '||x_pass_line);
3987
3988 END IF;
3989 EXCEPTION
3990 WHEN OTHERS THEN
3991 IF l_debug = Fnd_Api.G_TRUE THEN
3995
3992 Oe_Debug_Pub.ADD('Exception in QP_ATTR_MAPPING_PUB.Build_context'||SQLERRM);
3993 END IF;
3994 END Build_Contexts;
3996
3997 FUNCTION Get_API_Value
3998 ( p_src_api_call IN VARCHAR2
3999 , p_successful IN OUT NOCOPY BOOLEAN
4000 ) RETURN VARCHAR2
4001 IS
4002
4003 l_sql_stmt VARCHAR2(2060);
4004 x_return VARCHAR2(240);
4005
4006 BEGIN
4007 p_successful := TRUE;
4008 --dbms_output.put_line('*****Going Dynamic for******: ' || p_src_api_call);
4009
4010 l_sql_stmt := 'BEGIN QP_Attr_Mapping_PUB.G_Temp_Value := ' || p_src_api_call || ';' || ' END;';
4011
4012 --dbms_output.put_line(l_sql_stmt);
4013 EXECUTE IMMEDIATE l_sql_stmt ;
4014
4015 x_return := Qp_Attr_Mapping_Pub.G_Temp_Value;
4016
4017 --dbms_output.put_line('Return Value:' || x_return);
4018
4019 IF x_return = Fnd_Api.G_MISS_NUM THEN
4020 RETURN NULL;
4021 ELSE
4022 RETURN x_return;
4023 END IF;
4024
4025 EXCEPTION
4026 WHEN VALUE_ERROR THEN
4027 IF x_return = Fnd_Api.G_MISS_CHAR THEN
4028 RETURN NULL;
4029 ELSE
4030 RETURN x_return;
4031 END IF;
4032 WHEN NO_DATA_FOUND THEN
4033 RETURN NULL;
4034 WHEN OTHERS THEN
4035 g_err_mesg := SQLERRM;
4036 p_successful:= FALSE;
4037 RETURN NULL;
4038
4039 END Get_API_Value;
4040
4041
4042
4043 FUNCTION Get_API_MultiValue
4044 ( p_src_mult_api_call IN VARCHAR2
4045 , p_successful IN OUT NOCOPY BOOLEAN
4046 ) RETURN Qp_Attr_Mapping_Pub.t_MultiRecord
4047 IS
4048
4049 l_sql_stmt VARCHAR2(2060);
4050 x_return Qp_Attr_Mapping_Pub.t_MultiRecord;
4051
4052 BEGIN
4053 p_successful := TRUE;
4054
4055 --dbms_output.put_line('Going Dynamic for: ' || p_src_mult_api_call);
4056
4057 l_sql_stmt := 'BEGIN QP_Attr_Mapping_PUB.G_Temp_MultiValue := '|| p_src_mult_api_call ||'; END;';
4058 --dbms_output.put_line(l_sql_stmt);
4059
4060 EXECUTE IMMEDIATE l_sql_stmt;
4061 --dbms_output.put_line('Count for Multirec: ' || QP_Attr_Mapping_PUB.G_Temp_MultiValue.count);
4062
4063 x_return := Qp_Attr_Mapping_Pub.G_Temp_MultiValue;
4064
4065 RETURN x_return;
4066 EXCEPTION
4067 WHEN NO_DATA_FOUND THEN
4068 Put_Line('No_Data_Found Exception');
4069 x_return(1) := NULL;
4070 RETURN x_return;
4071 WHEN OTHERS THEN
4072 g_err_mesg := SQLERRM;
4073 p_successful := FALSE;
4074 x_return(1) := NULL;
4075 RETURN x_return;
4076
4077
4078 END Get_API_MultiValue;
4079
4080
4081 FUNCTION Get_Profile_Option_Value
4082 ( p_profile_option IN VARCHAR2
4083 , p_successful IN OUT NOCOPY BOOLEAN
4084 ) RETURN VARCHAR2
4085 IS
4086
4087 BEGIN
4088
4089 p_successful := TRUE;
4090 RETURN Fnd_Profile.value(p_profile_option);
4091
4092 EXCEPTION
4093 WHEN NO_DATA_FOUND THEN
4094 RETURN NULL;
4095 WHEN OTHERS THEN
4096 g_err_mesg := SQLERRM;
4097 p_successful := FALSE;
4098 RETURN NULL;
4099
4100 END Get_Profile_Option_Value;
4101
4102
4103 FUNCTION Get_System_Variable
4104 ( p_sys_variable IN VARCHAR2
4105 , p_successful IN OUT NOCOPY BOOLEAN
4106 ) RETURN VARCHAR2
4107 IS
4108
4109 l_sql_stmt VARCHAR2(100);
4110 x_return VARCHAR2(240);
4111
4112 BEGIN
4113
4114 p_successful := TRUE;
4115 l_sql_stmt := 'SELECT ' || p_sys_variable || ' FROM DUAL';
4116
4117 EXECUTE IMMEDIATE l_sql_stmt INTO x_return;
4118
4119 RETURN x_return;
4120
4121 EXCEPTION
4122 WHEN NO_DATA_FOUND THEN
4123 RETURN NULL;
4124 WHEN OTHERS THEN
4125 g_err_mesg := SQLERRM;
4126 p_successful := FALSE;
4127 RETURN NULL;
4128
4129 END Get_System_Variable;
4130
4131 PROCEDURE Add_to_Contexts_Results_Table
4132 ( p_results_tbl IN OUT NOCOPY CONTEXTS_RESULT_TBL_TYPE
4133 , p_index IN NUMBER
4134 , p_context_name IN VARCHAR2
4135 , p_attribute_name IN VARCHAR2
4136 , p_attribute_value IN VARCHAR2
4137 )
4138
4139 IS
4140
4141 BEGIN
4142
4143 p_results_tbl(p_index).context_name := p_context_name;
4144 -- oe_debug_pub.add('Context(' || p_index || '): ' || p_results_tbl(p_index).context_name);
4145
4146 p_results_tbl(p_index).attribute_name := p_attribute_name;
4147 -- oe_debug_pub.add('Attribute Name(' || p_index || '): ' || p_results_tbl(p_index).attribute_name);
4148
4149 p_results_tbl(p_index).attribute_value := p_attribute_value;
4150 -- oe_debug_pub.add('Attribute Value(' || p_index || '): ' || p_results_tbl(p_index).attribute_value);
4151
4152 END Add_to_Contexts_Results_Table;
4153
4154 PROCEDURE Map_Used_But_Not_Mapped_Attrs
4155 ( p_request_type_code IN VARCHAR2
4156 , p_pricing_type IN VARCHAR2
4157 , x_price_contexts_result_tbl OUT NOCOPY CONTEXTS_RESULT_TBL_TYPE
4161 IS
4158 , x_qual_contexts_result_tbl OUT NOCOPY CONTEXTS_RESULT_TBL_TYPE
4159 )
4160
4162
4163 l_pricing_type VARCHAR2(30);
4164 l_attribute_value VARCHAR2(240);
4165 l_attribute_mvalue Qp_Attr_Mapping_Pub.t_MultiRecord;
4166 l_context_name qp_prc_contexts_b.prc_context_code%TYPE;
4167 l_context_type qp_prc_contexts_b.prc_context_type%TYPE;
4168 l_attribute_name qp_segments_b.segment_mapping_column%TYPE;
4169 l_src_type qp_attribute_sourcing.user_sourcing_type%TYPE;
4170 l_value_string qp_attribute_sourcing.user_value_string%TYPE;
4171 l_qual_count BINARY_INTEGER := 0;
4172 l_price_count BINARY_INTEGER := 0;
4173 l_count BINARY_INTEGER := 0;
4174 l_index BINARY_INTEGER := 0;
4175 l_successful BOOLEAN := TRUE;
4176
4177 CURSOR l_ctxts_new(p_request_type_code VARCHAR2, p_sourcing_level VARCHAR2) IS
4178 SELECT qpseg.segment_mapping_column,
4179 qpsour.user_sourcing_type src_type,
4180 qpsour.user_value_string value_string,
4181 qpcon.prc_context_code context_code,
4182 qpcon.prc_context_type context_type
4183 FROM
4184 qp_segments_b qpseg,
4185 qp_attribute_sourcing qpsour,
4186 qp_prc_contexts_b qpcon,
4187 qp_pte_request_types_b qpreq,
4188 qp_pte_segments qppseg
4189 WHERE
4190 qpsour.segment_id = qpseg.segment_id
4191 AND qpsour.attribute_sourcing_level = p_sourcing_level
4192 AND qpsour.enabled_flag = 'Y'
4193 AND qppseg.sourcing_status = 'N'
4194 AND qppseg.used_in_setup = 'Y'
4195 AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
4196 AND qpsour.request_type_code = p_request_type_code
4197 AND qpseg.prc_context_id = qpcon.prc_context_id
4198 AND qpreq.request_type_code = qpsour.request_type_code
4199 AND qppseg.pte_code = qpreq.pte_code
4200 AND qppseg.segment_id = qpsour.segment_id
4201 AND qppseg.sourcing_enabled = 'Y'
4202 AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');
4203
4204 BEGIN
4205
4206 IF g_dynamic_mapping_needed = 'N' THEN
4207 RETURN;
4208 END IF;
4209
4210 IF p_pricing_type = 'L' THEN
4211 l_pricing_type := 'LINE';
4212 ELSIF p_pricing_type = 'H' THEN
4213 l_pricing_type := 'ORDER';
4214 END IF;
4215
4216 OPEN l_ctxts_new(p_request_type_code, l_pricing_type);
4217
4218 LOOP
4219 -- l_count := l_count + 1; --7323926
4220
4221 FETCH l_ctxts_new INTO
4222 l_attribute_name,
4223 l_src_type,
4224 l_value_string,
4225 l_context_name,
4226 l_context_type;
4227 EXIT WHEN l_ctxts_new%NOTFOUND;
4228
4229 g_dynamic_mapping_count := g_dynamic_mapping_count + 1; -- 7323926
4230
4231 IF l_debug = Fnd_Api.G_TRUE THEN
4232 Oe_Debug_Pub.ADD('Context = ' || l_context_name);
4233 Oe_Debug_Pub.ADD('Attribute = ' || l_attribute_name);
4234
4235 END IF;
4236 l_successful := TRUE;
4237
4238 IF l_src_type = 'API' THEN
4239 --dbms_output.put_line('Before Calling Get_API_Value');
4240 l_attribute_value := Get_API_Value(l_value_string, l_successful);
4241 IF l_debug = Fnd_Api.G_TRUE THEN
4242 Oe_Debug_Pub.ADD('Attr Name = '|| l_value_string);
4243 Oe_Debug_Pub.ADD('Attr Value = '|| l_attribute_value);
4244
4245 END IF;
4246 ELSIF l_src_type = 'API_MULTIREC' THEN
4247 --dbms_output.put_line('Before Calling Get_MULTIREC_API_Value');
4248 l_attribute_mvalue := Get_API_MultiValue(l_value_string, l_successful);
4249 IF l_debug = Fnd_Api.G_TRUE THEN
4250 Oe_Debug_Pub.ADD('Multirec API = '|| l_value_string);
4251
4252 END IF;
4253 ELSIF l_src_type = 'PROFILE_OPTION' THEN
4254 --dbms_output.put_line('Before Calling Get_Profile_Option_Value');
4255 l_attribute_value := Get_Profile_Option_Value(l_value_string, l_successful);
4256 IF l_debug = Fnd_Api.G_TRUE THEN
4257 Oe_Debug_Pub.ADD('Profile = '|| l_value_string);
4258 Oe_Debug_Pub.ADD('Profile Value = '|| l_attribute_value);
4259
4260 END IF;
4261 ELSIF l_src_type = 'SYSTEM' THEN
4262 --dbms_output.put_line('Before Calling Get_System_Variable');
4263 l_attribute_value := Get_System_Variable(l_value_string, l_successful);
4264 IF l_debug = Fnd_Api.G_TRUE THEN
4265 Oe_Debug_Pub.ADD('System Variable = ' || l_value_string);
4266 Oe_Debug_Pub.ADD('System Variable Value = ' || l_attribute_value);
4267
4268 END IF;
4269 ELSIF l_src_type = 'CONSTANT' THEN
4270 l_attribute_value := l_value_string;
4271 IF l_debug = Fnd_Api.G_TRUE THEN
4272 Oe_Debug_Pub.ADD('Constant = ' || l_value_string);
4273
4274 END IF;
4275 ELSE
4276 IF l_debug = Fnd_Api.G_TRUE THEN
4277 Oe_Debug_Pub.ADD('Invalid source type');
4278
4279 END IF;
4280 END IF;
4281
4282 IF (l_attribute_value IS NOT NULL) AND (l_src_type <> 'API_MULTIREC') THEN
4283
4284 IF l_context_type = 'QUALIFIER' THEN
4285
4286 l_qual_count := l_qual_count + 1;
4287
4288 Add_to_Contexts_Results_Table(x_qual_contexts_result_tbl, l_qual_count, l_context_name, l_attribute_name, l_attribute_value);
4289
4290 IF l_debug = Fnd_Api.G_TRUE THEN
4294 Oe_Debug_Pub.ADD('Attribute Name(' || l_qual_count || '): ' || x_qual_contexts_result_tbl(l_qual_count).attribute_name);
4291 Oe_Debug_Pub.ADD('Context(' || l_qual_count || '): ' || x_qual_contexts_result_tbl(l_qual_count).context_name);
4292 Oe_Debug_Pub.ADD('SRC Type(' || l_qual_count || '): ' || l_src_type);
4293 Oe_Debug_Pub.ADD('SRC Value(' || l_qual_count || '): ' || l_attribute_value);
4295 Oe_Debug_Pub.ADD('Attribute Value(' || l_qual_count || '): ' || x_qual_contexts_result_tbl(l_qual_count).attribute_value);
4296 Oe_Debug_Pub.ADD('------------------------------');
4297
4298 END IF;
4299 ELSIF l_context_type = 'PRICING_ATTRIBUTE' OR l_context_type = 'PRODUCT' THEN
4300
4301 l_price_count := l_price_count + 1;
4302
4303 Add_to_Contexts_Results_Table(x_price_contexts_result_tbl, l_price_count, l_context_name, l_attribute_name, l_attribute_value);
4304
4305 IF l_debug = Fnd_Api.G_TRUE THEN
4306 Oe_Debug_Pub.ADD('Context(' || l_price_count || '): ' || x_price_contexts_result_tbl(l_price_count).context_name);
4307 Oe_Debug_Pub.ADD('SRC Type(' || l_price_count || '): ' || l_src_type);
4308 Oe_Debug_Pub.ADD('SRC Value(' || l_price_count || '): ' || l_attribute_value);
4309 Oe_Debug_Pub.ADD('Attribute Name(' || l_price_count || '): ' || x_price_contexts_result_tbl(l_price_count).attribute_name);
4310 Oe_Debug_Pub.ADD('Attribute Value(' || l_price_count || '): ' || x_price_contexts_result_tbl(l_price_count).attribute_value);
4311 Oe_Debug_Pub.ADD('------------------------------');
4312
4313 END IF;
4314 ELSE
4315 IF l_debug = Fnd_Api.G_TRUE THEN
4316 Oe_Debug_Pub.ADD('Context type invalid');
4317
4318 END IF;
4319 END IF;
4320
4321 ELSIF (l_attribute_mvalue IS NOT NULL) AND (l_src_type = 'API_MULTIREC') THEN
4322
4323 IF l_debug = Fnd_Api.G_TRUE THEN
4324 Oe_Debug_Pub.ADD('Found a MULTIREC for context type: ' || l_context_type);
4325
4326 END IF;
4327
4328 IF (l_attribute_mvalue.COUNT <> 0) AND (l_attribute_mvalue(1) IS NOT NULL) THEN
4329
4330 l_index := 0;
4331
4332 LOOP
4333 l_index := l_index + 1;
4334
4335 IF l_context_type = 'QUALIFIER' THEN
4336
4337 l_qual_count := l_qual_count + 1;
4338
4339 Add_to_Contexts_Results_Table(x_qual_contexts_result_tbl,l_qual_count,l_context_name,l_attribute_name,l_attribute_mvalue(l_index));
4340
4341 IF l_debug = Fnd_Api.G_TRUE THEN
4342 Oe_Debug_Pub.ADD('Context(' || l_qual_count || '): ' || x_qual_contexts_result_tbl(l_qual_count).context_name);
4343 Oe_Debug_Pub.ADD('SRC Type(' || l_qual_count || '): ' || l_src_type);
4344 Oe_Debug_Pub.ADD('SRC Value(' || l_qual_count || '): ' || l_attribute_value);
4345 Oe_Debug_Pub.ADD('Attribute Name(' || l_qual_count || '): ' || x_qual_contexts_result_tbl(l_qual_count).attribute_name);
4346 Oe_Debug_Pub.ADD('Attribute Value(' || l_qual_count || '): ' || x_qual_contexts_result_tbl(l_qual_count).attribute_value);
4347 Oe_Debug_Pub.ADD('------------------------------');
4348
4349 END IF;
4350 ELSIF l_context_type = 'PRICING_ATTRIBUTE' OR l_context_type = 'PRODUCT' THEN
4351
4352 l_price_count := l_price_count + 1;
4353
4354 Add_to_Contexts_Results_Table(x_price_contexts_result_tbl,l_price_count,l_context_name,l_attribute_name,l_attribute_mvalue(l_index));
4355
4356 IF l_debug = Fnd_Api.G_TRUE THEN
4357 Oe_Debug_Pub.ADD('Context(' || l_price_count || '): ' || x_price_contexts_result_tbl(l_price_count).context_name);
4358 Oe_Debug_Pub.ADD('SRC Type(' || l_price_count || '): ' || l_src_type);
4359 Oe_Debug_Pub.ADD('SRC Value(' || l_price_count || '): ' || l_attribute_value);
4360 Oe_Debug_Pub.ADD('Attribute Name(' || l_price_count || '): ' || x_price_contexts_result_tbl(l_price_count).attribute_name);
4361 Oe_Debug_Pub.ADD('Attribute Value(' || l_price_count || '): ' || x_price_contexts_result_tbl(l_price_count).attribute_value);
4362 Oe_Debug_Pub.ADD('------------------------------');
4363
4364 END IF;
4365 ELSE
4366 IF l_debug = Fnd_Api.G_TRUE THEN
4367 Oe_Debug_Pub.ADD('Context type invalid');
4368
4369 END IF;
4370 END IF;
4371
4372 EXIT WHEN l_index = l_attribute_mvalue.LAST;
4373
4374 END LOOP;
4375
4376 END IF;
4377
4378 ELSE
4382 END IF;
4379 IF l_debug = Fnd_Api.G_TRUE THEN
4380 Oe_Debug_Pub.ADD('No value was obtained in the mapping process of source type ' || l_src_type);
4381
4383 END IF;
4384
4385 END LOOP;
4386
4387 IF g_dynamic_mapping_count = 0 THEN -- 7323926
4388 -- IF l_count = 1 THEN 7323926
4389 g_dynamic_mapping_needed := 'N';
4390 END IF;
4391
4392 CLOSE l_ctxts_new;
4393
4394 RETURN;
4395
4396 END Map_Used_But_Not_Mapped_Attrs;
4397
4398
4399 /* Bug#4509601 - Call to Put_Line is replaced with Print_Line to print
4400 Sourcing Rules Error Messages in Concurrent Program output file.
4401 */
4402 PROCEDURE Check_All_Mapping
4403 ( err_buff OUT NOCOPY VARCHAR2,
4404 retcode OUT NOCOPY NUMBER,
4405 p_request_type_code IN VARCHAR2
4406 )
4407
4408 IS
4409
4410 TYPE l_cursor_type IS REF CURSOR;
4411
4412 l_attribute_value VARCHAR2(240);
4413 l_attribute_mvalue Qp_Attr_Mapping_Pub.t_MultiRecord;
4414 l_context_name qp_prc_contexts_b.prc_context_code%TYPE;
4415 l_attribute_name qp_segments_b.segment_code%TYPE;
4416 l_attribute_map qp_segments_b.segment_mapping_column%TYPE;
4417 l_src_type qp_attribute_sourcing.user_sourcing_type%TYPE;
4418 l_pricing_type qp_attribute_sourcing.attribute_sourcing_level%TYPE;
4419 l_value_string qp_attribute_sourcing.user_value_string%TYPE;
4420 l_request_type qp_attribute_sourcing.request_type_code%TYPE;
4421 l_pte_name qp_pte_request_types_b.pte_code%TYPE;
4422 l_context_type qp_prc_contexts_b.prc_context_type%TYPE;
4423 l_successful BOOLEAN := TRUE;
4424 l_err_count BINARY_INTEGER := 0;
4425
4426 l_cursor l_cursor_type;
4427
4428 BEGIN
4429 IF Qp_Code_Control.CODE_RELEASE_LEVEL <= 110508 THEN
4430 Print_Line('This concurrent program is reserved for future use.');
4431 ELSE
4432 IF p_request_type_code IS NULL THEN
4433 OPEN l_cursor FOR
4434 SELECT qpseg.segment_mapping_column,
4435 qpsour.user_sourcing_type src_type,
4436 qpsour.user_value_string value_string,
4437 qpcon.prc_context_code context_code,
4438 qpsour.attribute_sourcing_level,
4439 qpsour.request_type_code,
4440 qpreq.pte_code,
4441 qpcon.prc_context_type,
4442 qpseg.segment_code
4443 FROM
4444 qp_segments_b qpseg,
4445 qp_attribute_sourcing qpsour,
4446 qp_prc_contexts_b qpcon,
4447 qp_pte_request_types_b qpreq,
4448 qp_pte_segments qppseg
4449 WHERE
4450 qpsour.segment_id = qpseg.segment_id
4451 AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
4452 AND qpseg.prc_context_id = qpcon.prc_context_id
4453 AND qpreq.request_type_code = qpsour.request_type_code
4454 AND qppseg.pte_code = qpreq.pte_code
4455 AND qppseg.segment_id = qpsour.segment_id
4456 AND qppseg.sourcing_enabled = 'Y'
4457 AND qpsour.enabled_flag='Y' -- Modified for Bug no 5559174 by rassharm
4458 AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');
4459 ELSE
4460 OPEN l_cursor FOR
4461 SELECT qpseg.segment_mapping_column,
4462 qpsour.user_sourcing_type src_type,
4463 qpsour.user_value_string value_string,
4464 qpcon.prc_context_code context_code,
4465 qpsour.attribute_sourcing_level,
4466 qpsour.request_type_code,
4467 qpreq.pte_code,
4468 qpcon.prc_context_type,
4469 qpseg.segment_code
4470
4471 FROM
4472 qp_segments_b qpseg,
4473 qp_attribute_sourcing qpsour,
4474 qp_prc_contexts_b qpcon,
4475 qp_pte_request_types_b qpreq,
4476 qp_pte_segments qppseg
4477 WHERE
4478 qpsour.segment_id = qpseg.segment_id
4479 AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
4480 AND qpsour.request_type_code = p_request_type_code
4481 AND qpseg.prc_context_id = qpcon.prc_context_id
4482 AND qpreq.request_type_code = qpsour.request_type_code
4483 AND qppseg.pte_code = qpreq.pte_code
4484 AND qppseg.segment_id = qpsour.segment_id
4485 AND qppseg.sourcing_enabled = 'Y'
4486 AND qpsour.enabled_flag='Y' -- Modified for Bug no 5559174 by rassharm
4487 AND qpcon.prc_context_type IN ('PRICING_ATTRIBUTE', 'PRODUCT','QUALIFIER');
4488 END IF;
4489
4490 LOOP
4491
4492 FETCH l_cursor INTO
4493 l_attribute_map,
4494 l_src_type,
4495 l_value_string,
4496 l_context_name,
4497 l_pricing_type,
4498 l_request_type,
4499 l_pte_name,
4500 l_context_type,
4501 l_attribute_name;
4502
4503 EXIT WHEN l_cursor%NOTFOUND;
4504
4505 l_successful := TRUE;
4506
4507 IF l_src_type = 'API' THEN
4508 l_attribute_value := Get_API_Value(l_value_string, l_successful);
4509
4510 ELSIF l_src_type = 'API_MULTIREC' THEN
4511 BEGIN
4512 l_attribute_mvalue := Get_API_MultiValue(l_value_string, l_successful);
4513 EXCEPTION
4514 WHEN OTHERS THEN
4515 NULL;
4516 END;
4517
4518 ELSIF l_src_type = 'PROFILE_OPTION' THEN
4519 l_attribute_value := Get_Profile_Option_Value(l_value_string, l_successful);
4520
4521 ELSIF l_src_type = 'SYSTEM' THEN
4522 l_attribute_value := Get_System_Variable(l_value_string, l_successful);
4523
4524 ELSIF l_src_type = 'CONSTANT' THEN
4525 l_attribute_value := l_value_string;
4526
4527 ELSE
4528 IF l_debug = Fnd_Api.G_TRUE THEN
4529 Oe_Debug_Pub.ADD('Invalid source type');
4530
4531 END IF;
4532 END IF;
4533
4534 IF l_successful = FALSE THEN
4535
4536 IF l_err_count = 0 THEN
4537 Print_Line('ATTRIBUTE MAPPING RULES ERRORS FOUND');
4538 Print_Line('------------------------------------------------------------');
4539 Print_Line('');
4540 END IF;
4541
4542 Print_Line('PTE: ' || l_pte_name);
4543 Print_Line('Context Type: ' || l_context_type);
4544 Print_Line('Context: ' || l_context_name);
4545 Print_Line('Attribute: ' || l_attribute_name || ' (' || l_attribute_map || ')');
4546 Print_Line('Request Type: ' || l_request_type);
4547 Print_Line('Level: ' || l_pricing_type);
4548 Print_Line('Sourcing Rule: ' || l_value_string);
4549 Print_Line('Error Message: ' || g_err_mesg);
4550 Print_Line('');
4551
4552 l_err_count := l_err_count + 1;
4553 END IF;
4554
4555 END LOOP;
4556 CLOSE l_cursor;
4557
4558 IF l_err_count = 0 THEN
4559 Print_Line('NO ATTRIBUTE MAPPING RULES ERROR(S) FOUND');
4560 retcode := 0;
4561 ELSE
4562 Print_Line('------------------------------------------------------------');
4563 Print_Line(l_err_count || ' ATTRIBUTE MAPPING RULES ERROR(S) FOUND');
4564 retcode := 1;
4565 END IF;
4566
4567 END IF;
4568
4569 RETURN;
4570
4571 EXCEPTION
4572 WHEN OTHERS THEN
4573 Print_Line('*** ERROR IN CHECK MAPPING RULES CONCURRENT PROGRAM ***');
4574 Print_Line(SQLERRM);
4575 Print_Line('*******************************************************');
4576 retcode := 2;
4577 err_buff := SQLERRM;
4578
4579 END Check_All_Mapping;
4580
4581
4582 END Qp_Attr_Mapping_Pub;