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