[Home] [Help]
PACKAGE BODY: APPS.QP_ADD_ITEM_PRCLIST_PVT
Source
1 PACKAGE BODY QP_ADD_ITEM_PRCLIST_PVT AS
2 /* $Header: QPXPRAIB.pls 120.2.12010000.3 2008/08/08 11:22:25 smuhamme ship $*/
3
4 Procedure Get_Conc_Reqvalues
5 (x_conc_request_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
6 x_conc_program_application_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
7 x_conc_program_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
8 x_conc_login_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
9 x_user_id OUT NOCOPY /* file.sql.39 change */ NUMBER )
10 IS
11 BEGIN
12
13 x_conc_request_id := fnd_global.conc_request_id;
14 x_conc_program_id := fnd_global.conc_program_id;
15 x_user_id := fnd_global.user_id;
16 x_conc_login_id := fnd_global.conc_login_id;
17
18 END GET_CONC_REQVALUES;
19
20 PROCEDURE Add_Items_To_Price_List
21 (
22 ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
23 RETCODE OUT NOCOPY /* file.sql.39 change */ NUMBER,
24 p_price_list_id IN NUMBER,
25 p_start_date_active IN DATE,
26 p_end_date_active IN DATE,
27 p_set_price_flag IN VARCHAR2,
28 p_organization_id IN NUMBER,
29 p_seg1 IN VARCHAR2,
30 p_seg2 IN VARCHAR2,
31 p_seg3 IN VARCHAR2,
32 p_seg4 IN VARCHAR2,
33 p_seg5 IN VARCHAR2,
34 p_seg6 IN VARCHAR2,
35 p_seg7 IN VARCHAR2,
36 p_seg8 IN VARCHAR2,
37 p_seg9 IN VARCHAR2,
38 p_seg10 IN VARCHAR2,
39 p_seg11 IN VARCHAR2,
40 p_seg12 IN VARCHAR2,
41 p_seg13 IN VARCHAR2,
42 p_seg14 IN VARCHAR2,
43 p_seg15 IN VARCHAR2,
44 p_seg16 IN VARCHAR2,
45 p_seg17 IN VARCHAR2,
46 p_seg18 IN VARCHAR2,
47 p_seg19 IN VARCHAR2,
48 p_seg20 IN VARCHAR2,
49 p_category_id IN NUMBER,
50 p_status_code IN VARCHAR2,
51 p_category_set_id IN NUMBER,
52 p_costorg_id IN NUMBER)
53 IS
54 l_conc_request_id NUMBER := -1;
55 l_conc_program_application_id NUMBER := -1;
56 l_conc_program_id NUMBER := -1;
57 l_conc_login_id NUMBER := -1;
58 l_user_id NUMBER := -1;
59 l_rounding_factor NUMBER;
60 l_min_acct_unit NUMBER;
61 l_additems_sql VARCHAR2(4000);
62 l_already_exists NUMBER;
63 l_commit_count NUMBER:=0;
64
65 l_item_tbl_type DBMS_SQL.VARCHAR2_TABLE;
66 l_item_tbl l_item_tbl_type%type;
67
68 l_attr_grp_s NUMBER;
69 l_index BINARY_INTEGER;
70
71 l_price NUMBER;
72 l_uom VARCHAR2(3);
73 l_list_line_id NUMBER;
74 l_min_list_line_id NUMBER;
75 l_max_list_line_id NUMBER;
76
77 CURSOR qual_cur
78 IS
79 SELECT qualifier_id
80 FROM qp_qualifiers
81 WHERE list_header_id = p_price_list_id
82 AND NOT (qualifier_context = 'MODLIST' AND
83 qualifier_attribute = 'QUALIFIER_ATTRIBUTE4');
84 --Do not consider those qualifiers which are Primary PLs
85 --that are qualifiers to their secondary PLs.
86
87 CURSOR precedence_cur(a_pte_code VARCHAR2)
88 IS SELECT a.user_precedence
89 FROM qp_segments_v a,
90 qp_prc_contexts_b b,
91 qp_pte_segments c
92 WHERE
93 b.prc_context_type = 'PRODUCT' and
94 b.prc_context_code = 'ITEM' and
95 b.prc_context_id = a.prc_context_id and
96 a.segment_mapping_column = 'PRICING_ATTRIBUTE1' and
97 a.segment_id = c.segment_id and
98 c.pte_code = a_pte_code;
99
100 l_pte_code VARCHAR2(30);
101
102 l_qual_id NUMBER;
103 l_qualification_ind NUMBER;
104
105
106 Flexfield FND_DFLEX.dflex_r;
107 Flexinfo FND_DFLEX.dflex_dr;
108 Contexts FND_DFLEX.contexts_dr;
109 segments FND_DFLEX.segments_dr;
110 l_sequence_num NUMBER;
111 l_price_rounding VARCHAR2(50) :='';
112
113
114 BEGIN
115 Get_Conc_Reqvalues
116 (l_conc_request_id,
117 l_conc_program_application_id,
118 l_conc_program_id,
119 l_conc_login_id,
120 l_user_id);
121
122 /* -----------------------------------------------------------------------+
123 | Retrieve the items within the item range |
124 +----------------------------------------------------------------------- */
125
126 QP_ITEM_RANGE_PVT.ITEMS_IN_RANGE
127 (
128 p_seg1,
129 p_seg2,
130 p_seg3,
131 p_seg4,
132 p_seg5,
133 p_seg6,
134 p_seg7,
135 p_seg8,
136 p_seg9,
137 p_seg10,
138 p_seg11,
139 p_seg12,
140 p_seg13,
141 p_seg14,
142 p_seg15,
143 p_seg16,
144 p_seg17,
145 p_seg18,
146 p_seg19,
147 p_seg20,
148 p_organization_id,
149 p_category_set_id,
150 p_category_id,
151 p_status_code,
152 l_item_tbl);
153
154 IF QP_UTIL.Attrmgr_Installed = 'Y' THEN
155 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
156
157 IF l_pte_code IS NULL THEN
158 l_pte_code := 'ORDFUL';
159 END IF;
160
161 OPEN precedence_cur(l_pte_code);
162 FETCH precedence_cur INTO l_sequence_num;
163 CLOSE precedence_cur;
164
165 ELSE
166 -- Added by dhgupta for bug 2113793
167
168 FND_DFLEX.get_flexfield('QP','QP_ATTR_DEFNS_PRICING',Flexfield,Flexinfo);
169 FND_DFLEX.get_segments(FND_DFLEX.make_context(Flexfield,'ITEM'),
170 segments,TRUE);
171 For i in 1..segments.nsegments LOOP
172 IF segments.application_column_name(i) = 'PRICING_ATTRIBUTE1' THEN
173 l_sequence_num := segments.sequence(i);
174 END IF;
175 END LOOP;
176 END IF;
177
178
179 select qp_pricing_attr_group_no_s.nextval
180 into l_attr_grp_s
181 from dual;
182
183 select (-1*PL.ROUNDING_FACTOR),
184 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT,-1)
185 into l_rounding_factor, l_min_acct_unit
186 from QP_LIST_HEADERS_B PL, FND_CURRENCIES FC
187 where PL.LIST_HEADER_ID = p_price_list_id
188 and PL.CURRENCY_CODE = FC.CURRENCY_CODE;
189
190
191 l_price_rounding := fnd_profile.value('QP_PRICE_ROUNDING'); --Added for Enhancement 1732601
192
193 OPEN qual_cur;
194 FETCH qual_cur INTO l_qual_id;
195
196 IF qual_cur%FOUND THEN -- Qualifiers present in target Price List
197 l_qualification_ind := 6;
198 ELSE -- Qualifiers not present in target Price List
199 l_qualification_ind := 4;
200 END IF;
201
202 CLOSE qual_cur;
203
204 IF ( p_set_price_flag = 'Y' ) THEN
205 l_index := l_item_tbl.FIRST;
206 WHILE l_index <= l_item_tbl.LAST LOOP
207 begin
208 /*
209 select count(*)
210 into l_already_exists
211 from qp_pricing_attributes qppa
212 where qppa.pricing_phase_id = 1
213 and qppa.qualification_ind in (4,6,20,22)
214 and qppa.product_attribute_context = 'ITEM'
215 and qppa.product_attr_value = l_item_tbl(l_index)
216 and qppa.excluder_flag = 'N'
217 and qppa.list_header_id = p_price_list_id;
218 */
219 l_already_exists := 0;
220 Select 1
221 Into l_already_exists
222 From Dual
223 Where Exists
224 (Select Null
225 from qp_pricing_attributes qppa
226 where qppa.list_header_id = p_price_list_id
227 and qppa.pricing_phase_id = 1
228 and qppa.product_attribute_context = 'ITEM'
229 and qppa.product_attribute = 'PRICING_ATTRIBUTE1'
230 and qppa.product_attr_value = l_item_tbl(l_index)
231 );
232 exception
233 WHEN NO_DATA_FOUND THEN
234 l_already_exists := 0;
235 WHEN OTHERS THEN
236 RAISE;
237 end;
238 IF l_already_exists > 0 THEN
239 -- write rec log of values that cannot be added
240 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Number '|| l_item_tbl(l_index) || ' already exists');
241 ELSE
242 /*
243 select decode(l_min_acct_unit,
244 -1,
245 round(nvl(cst.item_cost,0), l_rounding_factor),
246 round(nvl(cst.item_cost,0)/l_min_acct_unit)
247 * l_min_acct_unit)
248 into l_price
249 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
250 where mtl.inventory_item_id = l_item_tbl(l_index)
251 and cst.inventory_item_id (+)=mtl.inventory_item_id
252 and cst.organization_id (+)= nvl(p_costorg_id,mtl.organization_id)
253 and mtl.organization_id = p_organization_id;
254 */
255 Begin
256
257 IF l_price_rounding IS NOT NULL THEN --Added for Enhancement 1732601
258 If (p_costorg_id Is Null) Then
259 select decode(l_min_acct_unit,
260 -1,
261 round(nvl(cst.item_cost,0), l_rounding_factor),
262 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
263 Into l_price
264 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
265 where mtl.inventory_item_id = l_item_tbl(l_index)
266 and cst.inventory_item_id =mtl.inventory_item_id
267 and cst.organization_id = mtl.organization_id
268 and mtl.organization_id = p_organization_id;
269 Else
270 select decode(l_min_acct_unit,
271 -1,
272 round(nvl(cst.item_cost,0), l_rounding_factor),
273 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
274 Into l_price
275 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
276 where mtl.inventory_item_id = l_item_tbl(l_index)
277 and cst.inventory_item_id =mtl.inventory_item_id
278 and cst.organization_id = p_costorg_id
279 and mtl.organization_id = p_organization_id;
280 End If;
281 ELSE
282 /* Added for Enhancement 1732601 */
283
284 If (p_costorg_id Is Null) Then
285
286 select decode(l_min_acct_unit,
287 -1,
288 nvl(cst.item_cost,0),
289 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
290 Into l_price
291 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
292 where mtl.inventory_item_id = l_item_tbl(l_index)
293 and cst.inventory_item_id =mtl.inventory_item_id
294 and cst.organization_id = mtl.organization_id
295 and mtl.organization_id = p_organization_id;
296 Else
297
298 select decode(l_min_acct_unit,
299 -1,
300 nvl(cst.item_cost,0),
301 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
302 Into l_price
303 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
304 where mtl.inventory_item_id = l_item_tbl(l_index)
305 and cst.inventory_item_id =mtl.inventory_item_id
306 and cst.organization_id = p_costorg_id
307 and mtl.organization_id = p_organization_id;
308 End If;
309
310 END IF;
311 Exception
312 When No_Data_Found Then
313 l_price := 0;
314 End;
315
316
317 select mtl.primary_uom_code
318 into l_uom
319 from mtl_system_items mtl
320 where mtl.inventory_item_id = l_item_tbl(l_index)
321 and mtl.organization_id = p_organization_id;
322
323 select qp_list_lines_s.nextval
324 into l_list_line_id
325 from dual;
326
327 insert
328 into qp_list_lines
329 (LIST_LINE_ID,
330 LIST_LINE_NO,
331 LAST_UPDATE_DATE,
332 CREATION_DATE,
333 LIST_PRICE_UOM_CODE,
334 LIST_PRICE,
335 LAST_UPDATED_BY,
336 CREATED_BY,
337 LAST_UPDATE_LOGIN,
338 LIST_HEADER_ID,
339 REQUEST_ID,
340 PROGRAM_APPLICATION_ID,
341 PROGRAM_ID,
342 PROGRAM_UPDATE_DATE,
343 LIST_LINE_TYPE_CODE,
344 START_DATE_ACTIVE,
345 AUTOMATIC_FLAG,
346 PRICING_PHASE_ID,
347 OPERAND,
348 ARITHMETIC_OPERATOR,
349 INCOMPATIBILITY_GRP_CODE,
350 PRODUCT_PRECEDENCE,
351 MODIFIER_LEVEL_CODE,
352 QUALIFICATION_IND
353 -- Bug 5202021 RAVI
354 ,ORIG_SYS_LINE_REF)
355 values (
356 l_list_line_id,
357 l_list_line_id,
358 -- Begin Bug No: 7281484
359 sysdate,
360 sysdate,
361 -- End Bug No: 7281484
362 l_uom,
363 l_price,
364 l_user_id,
365 l_user_id,
366 l_conc_login_id,
367 p_price_list_id,
368 l_conc_request_id,
369 l_conc_program_application_id,
370 l_conc_program_id,
371 sysdate, --Bug No: 7281484
372 'PLL',
373 trunc(sysdate),
374 'Y',
375 1,
376 l_price,
377 'UNIT_PRICE',
378 'EXCL',
379 l_sequence_num, --modified by dhgupta for bug 2113793
380 -- 220,
381 'LINE',
382 l_qualification_ind
383 -- Bug 5202021 RAVI
384 ,l_list_line_id);
385
386 insert
387 into qp_pricing_attributes
388 (pricing_attribute_id,
389 creation_date,
390 created_by,
391 last_update_date,
392 last_updated_by,
393 last_update_login,
394 program_application_id,
395 program_id,
396 program_update_date,
397 request_id,
398 list_line_id,
399 list_header_id,
400 pricing_phase_id,
401 qualification_ind,
402 product_attribute_context,
403 product_attribute,
404 product_attribute_datatype, --3099578
405 product_attr_value,
406 product_uom_code,
407 excluder_flag,
408 accumulate_flag,
409 comparison_operator_code, --2814272
410 attribute_grouping_no
411 -- Bug 5202021 RAVI
412 ,ORIG_SYS_PRICING_ATTR_REF)
413 values (qp_pricing_attributes_s.nextval,
414 sysdate, -- Bug No: 7281484
415 l_user_id,
416 sysdate, -- Bug No: 7281484
417 l_user_id,
418 l_conc_login_id,
419 l_conc_program_application_id,
420 l_conc_program_id,
421 sysdate, -- Bug No: 7281484
422 l_conc_request_id,
423 l_list_line_id,
424 p_price_list_id,
425 1,
426 l_qualification_ind,
427 'ITEM',
428 'PRICING_ATTRIBUTE1',
432 'N',
429 'C', --3099578
430 l_item_tbl(l_index),
431 l_uom,
433 'N',
434 'BETWEEN', --2814272
435 l_attr_grp_s
436 -- Bug 5202021 RAVI
437 ,qp_pricing_attributes_s.currval);
438
439 END IF;
440
441 l_index := l_item_tbl.NEXT(l_index);
442
443 END LOOP;
444
445 /* ---------------------------------------------------------------------------+
446 | Create price list line for inventory items not on price list |
447 | DO NOT Set list_price = cost |
448 + --------------------------------------------------------------------------*/
449 ELSE
450
451 l_index := l_item_tbl.FIRST;
452 WHILE l_index <= l_item_tbl.LAST LOOP
453 begin
454 /*
455 select count(*)
456 into l_already_exists
457 from qp_pricing_attributes qppa
458 where qppa.list_header_id = p_price_list_id
459 and qppa.pricing_phase_id = 1
460 and qppa.product_attribute_context = 'ITEM'
461 and qppa.product_attr_value = l_item_tbl(l_index);
462 */
463 l_already_exists := 0;
464 Select 1
465 Into l_already_exists
466 From Dual
467 Where Exists
468 (Select Null
469 from qp_pricing_attributes qppa
470 where qppa.list_header_id = p_price_list_id
471 and qppa.pricing_phase_id = 1
472 and qppa.product_attribute_context = 'ITEM'
473 and qppa.product_attribute = 'PRICING_ATTRIBUTE1'
474 and qppa.product_attr_value = l_item_tbl(l_index)
475 );
476 exception
477 when no_data_found then
478 l_already_exists := 0;
479 WHEN OTHERS THEN
480 RAISE;
481 end;
482 IF l_already_exists > 0 THEN
483 -- write rec log of values that cannot be added
484 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Number '|| l_item_tbl(l_index) || ' already exists');
485 ELSE
486
487 select mtl.primary_uom_code
488 into l_uom
489 from mtl_system_items mtl
490 where mtl.inventory_item_id = l_item_tbl(l_index)
491 and mtl.organization_id = p_organization_id;
492
493
494 insert
495 into qp_list_lines
496 (LIST_LINE_ID,
497 LIST_LINE_NO,
498 LAST_UPDATE_DATE,
499 CREATION_DATE,
500 LIST_PRICE_UOM_CODE,
501 LIST_PRICE,
502 LAST_UPDATED_BY,
503 CREATED_BY,
504 LAST_UPDATE_LOGIN,
505 LIST_HEADER_ID,
506 REQUEST_ID,
507 PROGRAM_APPLICATION_ID,
508 PROGRAM_ID,
509 PROGRAM_UPDATE_DATE,
510 LIST_LINE_TYPE_CODE,
511 START_DATE_ACTIVE,
512 AUTOMATIC_FLAG,
513 PRICING_PHASE_ID,
514 OPERAND,
515 ARITHMETIC_OPERATOR,
516 INCOMPATIBILITY_GRP_CODE,
517 PRODUCT_PRECEDENCE,
518 MODIFIER_LEVEL_CODE,
519 QUALIFICATION_IND
520 -- Bug 5202021 RAVI
521 ,ORIG_SYS_LINE_REF)
522 values (
523 qp_list_lines_s.nextval,
524 qp_list_lines_s.currval,
525 -- Begin Bug No: 7281484
526 sysdate,
527 sysdate,
528 -- End -- Bug No: 7281484
529 l_uom,
530 0,
531 l_user_id,
532 l_user_id,
533 l_conc_login_id,
534 p_price_list_id,
535 l_conc_request_id,
536 l_conc_program_application_id,
537 l_conc_program_id,
538 sysdate, -- Bug No: 7281484
539 'PLL',
540 trunc(sysdate),
541 'Y',
542 1,
543 0,
544 'UNIT_PRICE',
545 'EXCL',
546 l_sequence_num,
547 --220, --modified by dhgupta for bug 2113793
548 'LINE',
549 l_qualification_ind
550 -- Bug 5202021 RAVI
551 ,qp_list_lines_s.currval);
552
553 /*------------------------------------------------------------------------+
554 | Insert pricing attributes |
555 +-----------------------------------------------------------------------*/
556
557
558 insert
559 into qp_pricing_attributes
560 (PRICING_ATTRIBUTE_ID,
561 CREATION_DATE,
562 CREATED_BY,
563 LAST_UPDATE_DATE,
564 LAST_UPDATED_BY,
565 LAST_UPDATE_LOGIN,
566 PROGRAM_APPLICATION_ID,
567 PROGRAM_ID,
568 PROGRAM_UPDATE_DATE,
569 REQUEST_ID,
570 LIST_LINE_ID,
571 LIST_HEADER_ID,
572 PRICING_PHASE_ID,
573 QUALIFICATION_IND,
574 PRODUCT_ATTRIBUTE_CONTEXT,
575 PRODUCT_ATTRIBUTE,
579 EXCLUDER_FLAG,
576 product_attribute_datatype, --3099578
577 PRODUCT_ATTR_VALUE,
578 PRODUCT_UOM_CODE,
580 ACCUMULATE_FLAG,
581 comparison_operator_code, --2814272
582 ATTRIBUTE_GROUPING_NO
583 -- Bug 5202021 RAVI
584 ,ORIG_SYS_PRICING_ATTR_REF)
585 values (qp_pricing_attributes_s.nextval,
586 sysdate, -- Bug No: 7281484
587 l_user_id,
588 sysdate, -- Bug No: 7281484
589 l_user_id,
590 l_conc_login_id,
591 l_conc_program_application_id,
592 l_conc_program_id,
593 sysdate, -- Bug No: 7281484
594 l_conc_request_id,
595 qp_list_lines_s.currval,
596 p_price_list_id,
597 1,
598 l_qualification_ind,
599 'ITEM',
600 'PRICING_ATTRIBUTE1',
601 'C', --3099578
602 l_item_tbl(l_index),
603 l_uom,
604 'N',
605 'N',
606 'BETWEEN', --2814272
607 l_attr_grp_s
608 -- Bug 5202021 RAVI
609 ,qp_pricing_attributes_s.currval);
610
611 END IF;
612
613 l_index := l_item_tbl.NEXT(l_index);
614 l_commit_count:=l_commit_count+1; --Commiting after every 100 records for bug2363369
615 IF l_commit_count=100 THEN
616 l_commit_count:=0;
617 COMMIT;
618 END IF;
619
620
621 END LOOP;
622
623 END IF;
624 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
628 where list_header_id = p_price_list_id;
625 select min(list_line_id), max(list_line_id)
626 into l_min_list_line_id, l_max_list_line_id
627 from qp_list_lines
629
630 QP_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
631 QP_ATTR_GRP_PVT.update_pp_lines(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
632 END IF;
633
634 COMMIT;
635 retcode:=0;
636 errbuf:='';
637 EXCEPTION
638 WHEN others THEN
639 ROLLBACK;
640 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLCODE||' - '||SQLERRM);
641 retcode:=2;
642 END Add_Items_To_Price_List;
643
644 END QP_ADD_ITEM_PRCLIST_PVT;