[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.7 2010/10/11 11:13:38 jputta 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 l_orig_sys_header_ref NUMBER; --10185331
77
78 CURSOR qual_cur
79 IS
80 SELECT qualifier_id
81 FROM qp_qualifiers
82 WHERE list_header_id = p_price_list_id
83 AND NOT (qualifier_context = 'MODLIST' AND
84 qualifier_attribute = 'QUALIFIER_ATTRIBUTE4');
85 --Do not consider those qualifiers which are Primary PLs
86 --that are qualifiers to their secondary PLs.
90 FROM qp_segments_v a,
87
88 CURSOR precedence_cur(a_pte_code VARCHAR2)
89 IS SELECT a.user_precedence
91 qp_prc_contexts_b b,
92 qp_pte_segments c
93 WHERE
94 b.prc_context_type = 'PRODUCT' and
95 b.prc_context_code = 'ITEM' and
96 b.prc_context_id = a.prc_context_id and
97 a.segment_mapping_column = 'PRICING_ATTRIBUTE1' and
98 a.segment_id = c.segment_id and
99 c.pte_code = a_pte_code;
100
101 l_pte_code VARCHAR2(30);
102
103 l_qual_id NUMBER;
104 l_qualification_ind NUMBER;
105
106
107 Flexfield FND_DFLEX.dflex_r;
108 Flexinfo FND_DFLEX.dflex_dr;
109 Contexts FND_DFLEX.contexts_dr;
110 segments FND_DFLEX.segments_dr;
111 l_sequence_num NUMBER;
112 l_price_rounding VARCHAR2(50) :='';
113 /* 7388596*/
114 v_result_code number;
115 V_cost_mthd VARCHAR2(15) DEFAULT NULL ;
116 V_cmpntcls_id NUMBER DEFAULT NULL;
117 V_analysis_code VARCHAR2(15) DEFAULT NULL;
118 V_acctg_cost NUMBER ;
119 v_return_status VARCHAR2(15);
120 v_msg_count NUMBER;
121 v_msg_data VARCHAR2(2000);
122 v_item_cost NUMBER;
123 v_no_of_rows NUMBER;
124
125 BEGIN
126 Get_Conc_Reqvalues
127 (l_conc_request_id,
128 l_conc_program_application_id,
129 l_conc_program_id,
130 l_conc_login_id,
131 l_user_id);
132
133 /* -----------------------------------------------------------------------+
134 | Retrieve the items within the item range |
135 +----------------------------------------------------------------------- */
136
137 QP_ITEM_RANGE_PVT.ITEMS_IN_RANGE
138 (
139 p_seg1,
140 p_seg2,
141 p_seg3,
142 p_seg4,
143 p_seg5,
144 p_seg6,
145 p_seg7,
146 p_seg8,
147 p_seg9,
148 p_seg10,
149 p_seg11,
150 p_seg12,
151 p_seg13,
152 p_seg14,
153 p_seg15,
154 p_seg16,
155 p_seg17,
156 p_seg18,
157 p_seg19,
158 p_seg20,
159 p_organization_id,
160 p_category_set_id,
161 p_category_id,
162 p_status_code,
163 l_item_tbl);
164
165
166
167 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_price_list_id-'||p_price_list_id);
168 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_set_price_flag-'||p_set_price_flag);
169 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_organization_id-'||p_organization_id);
170 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_category_id-'||p_category_id);
171 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_status_code-'||p_status_code);
172 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_category_set_id-'||p_category_set_id);
173 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_costorg_id-'||p_costorg_id);
174
175 IF QP_UTIL.Attrmgr_Installed = 'Y' THEN
176 FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY', l_pte_code);
177
178 IF l_pte_code IS NULL THEN
179 l_pte_code := 'ORDFUL';
180 END IF;
181 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_pte_code'||l_pte_code);
182 OPEN precedence_cur(l_pte_code);
183 FETCH precedence_cur INTO l_sequence_num;
184 CLOSE precedence_cur;
185
186 ELSE
187 -- Added by dhgupta for bug 2113793
188
189 FND_DFLEX.get_flexfield('QP','QP_ATTR_DEFNS_PRICING',Flexfield,Flexinfo);
193 IF segments.application_column_name(i) = 'PRICING_ATTRIBUTE1' THEN
190 FND_DFLEX.get_segments(FND_DFLEX.make_context(Flexfield,'ITEM'),
191 segments,TRUE);
192 For i in 1..segments.nsegments LOOP
194 l_sequence_num := segments.sequence(i);
195 END IF;
196 END LOOP;
197 END IF;
198
199
200 select qp_pricing_attr_group_no_s.nextval
201 into l_attr_grp_s
202 from dual;
203
204 select (-1*PL.ROUNDING_FACTOR),
205 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT,-1)
206 into l_rounding_factor, l_min_acct_unit
207 from QP_LIST_HEADERS_B PL, FND_CURRENCIES FC
208 where PL.LIST_HEADER_ID = p_price_list_id
209 and PL.CURRENCY_CODE = FC.CURRENCY_CODE;
210
211
215 FETCH qual_cur INTO l_qual_id;
212 l_price_rounding := fnd_profile.value('QP_PRICE_ROUNDING'); --Added for Enhancement 1732601
213 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_price_rounding-'||l_price_rounding);
214 OPEN qual_cur;
216
217 IF qual_cur%FOUND THEN -- Qualifiers present in target Price List
218 l_qualification_ind := 6;
219 ELSE -- Qualifiers not present in target Price List
220 l_qualification_ind := 4;
221 END IF;
222
223 CLOSE qual_cur;
224
225 IF ( p_set_price_flag = 'Y' ) THEN
226 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entered p_set_price_flag');
227 l_index := l_item_tbl.FIRST;
228 WHILE l_index <= l_item_tbl.LAST LOOP
229 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entered p_set_price_flag while');
230 FND_FILE.PUT_LINE(FND_FILE.LOG, 'inventory item id-'||l_index||'-'||l_item_tbl(l_index));
231 begin
232 /*
233 select count(*)
234 into l_already_exists
235 from qp_pricing_attributes qppa
236 where qppa.pricing_phase_id = 1
237 and qppa.qualification_ind in (4,6,20,22)
238 and qppa.product_attribute_context = 'ITEM'
239 and qppa.product_attr_value = l_item_tbl(l_index)
240 and qppa.excluder_flag = 'N'
241 and qppa.list_header_id = p_price_list_id;
242 */
243 l_already_exists := 0;
244 Select 1
245 Into l_already_exists
246 From Dual
247 Where Exists
248 (Select Null
249 from qp_pricing_attributes qppa
250 where qppa.list_header_id = p_price_list_id
251 and qppa.pricing_phase_id = 1
252 and qppa.product_attribute_context = 'ITEM'
253 and qppa.product_attribute = 'PRICING_ATTRIBUTE1'
254 and qppa.product_attr_value = l_item_tbl(l_index)
255 );
256 exception
257 WHEN NO_DATA_FOUND THEN
258 l_already_exists := 0;
259 WHEN OTHERS THEN
260 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR-'||sqlerrm);
261 RAISE;
262 end;
263 IF l_already_exists > 0 THEN
264 -- write rec log of values that cannot be added
265 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Number '|| l_item_tbl(l_index) || ' already exists');
266 ELSE
267 /*
268 select decode(l_min_acct_unit,
269 -1,
270 round(nvl(cst.item_cost,0), l_rounding_factor),
271 round(nvl(cst.item_cost,0)/l_min_acct_unit)
272 * l_min_acct_unit)
273 into l_price
274 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
275 where mtl.inventory_item_id = l_item_tbl(l_index)
276 and cst.inventory_item_id (+)=mtl.inventory_item_id
277 and cst.organization_id (+)= nvl(p_costorg_id,mtl.organization_id)
278 and mtl.organization_id = p_organization_id;
279 */
280 Begin
281
282 IF l_price_rounding IS NOT NULL THEN --Added for Enhancement 1732601
283 If (p_costorg_id Is Null) Then
284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-1-1');
285 select decode(l_min_acct_unit,
286 -1,
287 round(nvl(cst.item_cost,0), l_rounding_factor),
288 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
289 Into l_price
290 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
291 where mtl.inventory_item_id = l_item_tbl(l_index)
292 and cst.inventory_item_id =mtl.inventory_item_id
293 and cst.organization_id = mtl.organization_id
294 and mtl.organization_id = p_organization_id;
295 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-1');
296 ELSE /* 7388596*/
297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-API-1');
298 IF GMF_validations_PVT.Validate_organization_id(p_costorg_id) THEN
299 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inventory Item Id: '|| l_item_tbl(l_index));
300 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_organization_id: '|| p_costorg_id);
301 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transaction_date: '|| sysdate);
302 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cost_method: '|| NVL(v_cost_mthd,'NULL'));
303 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_cmpntcls_id: '|| NVL(v_cmpntcls_id,0));
304 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_analysis_code: '|| NVL(v_analysis_code,'NULL'));
305 v_result_code := GMF_CMCOMMON.Get_Process_Item_Cost
306 ( p_api_version => 1
307 , p_init_msg_list => 'F'
308 , x_return_status => v_return_status
309 , x_msg_count => v_msg_count
310 , x_msg_data => v_msg_data
311 , p_inventory_item_id => l_item_tbl(l_index)
312 , p_organization_id => p_costorg_id
313 , p_transaction_date => sysdate /* Cost as on date */
314 , p_detail_flag => 1 /* 1 = total cost, 2 = details; 3 = cost for a specific component class/analysis code, etc. */
315 , p_cost_method => v_cost_mthd /* OPM Cost Method */
316 , p_cost_component_class_id => v_cmpntcls_id
317 , p_cost_analysis_code => v_analysis_code
318 , x_total_cost => v_item_cost /* total cost */
319 , x_no_of_rows => v_no_of_rows /* number of detail rows retrieved */
320 );
321 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cost_method: '|| NVL(v_cost_mthd,'NULL'));
322 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_cmpntcls_id: '|| NVL(v_cmpntcls_id,0));
326 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_no_of_rows: '|| NVL(v_no_of_rows,0));
323 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_analysis_code: '|| NVL(v_analysis_code,'NULL'));
324 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_result_code: '|| NVL(v_result_code,0));
325 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_total_cost: '|| NVL(v_item_cost,0));
327 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_return_status: '|| NVL(v_return_status,'N'));
328 IF v_result_code = 1 THEN
329 SELECT decode(l_min_acct_unit,-1,round(nvl(v_item_cost,0), l_rounding_factor),
330 round(nvl(v_item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
331 INTO l_price
332 FROM dual;
333 ELSE
334 l_price := 0;
335 END IF;
336 ELSE
337 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-2-1');
338 select decode(l_min_acct_unit,
339 -1,
340 round(nvl(cst.item_cost,0), l_rounding_factor),
341 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
342 Into l_price
343 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
344 where mtl.inventory_item_id = l_item_tbl(l_index)
345 and cst.inventory_item_id =mtl.inventory_item_id
346 and cst.organization_id = p_costorg_id
347 and mtl.organization_id = p_organization_id;
348 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-2');
349 END IF;
350 End If;
351 ELSE
352 /* Added for Enhancement 1732601 */
353
354 If (p_costorg_id Is Null) Then
355
356 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-3-1');
357 select decode(l_min_acct_unit,
358 -1,
359 nvl(cst.item_cost,0),
360 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
361 Into l_price
362 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
363 where mtl.inventory_item_id = l_item_tbl(l_index)
364 and cst.inventory_item_id =mtl.inventory_item_id
365 and cst.organization_id = mtl.organization_id
366 and mtl.organization_id = p_organization_id;
367 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-3');
368 ELSE /* 7388596*/
369 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-API-2');
370 IF GMF_validations_PVT.Validate_organization_id(p_costorg_id) THEN
371 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inventory Item Id: '|| l_item_tbl(l_index));
372 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_organization_id: '|| p_costorg_id);
373 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transaction_date: '|| sysdate);
374 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cost_method: '|| NVL(v_cost_mthd,'NULL'));
375 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_cmpntcls_id: '|| NVL(v_cmpntcls_id,0));
376 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_analysis_code: '|| NVL(v_analysis_code,'NULL'));
377 v_result_code := GMF_CMCOMMON.Get_Process_Item_Cost
378 ( p_api_version => 1
379 , p_init_msg_list => 'F'
380 , x_return_status => v_return_status
381 , x_msg_count => v_msg_count
382 , x_msg_data => v_msg_data
383 , p_inventory_item_id => l_item_tbl(l_index)
384 , p_organization_id => p_costorg_id
385 , p_transaction_date => sysdate /* Cost as on date */
386 , p_detail_flag => 1 /* 1 = total cost, 2 = details; 3 = cost for a specific component class/analysis code, etc. */
387 , p_cost_method => v_cost_mthd /* OPM Cost Method */
388 , p_cost_component_class_id => v_cmpntcls_id
389 , p_cost_analysis_code => v_analysis_code
390 , x_total_cost => v_item_cost /* total cost */
391 , x_no_of_rows => v_no_of_rows /* number of detail rows retrieved */
392 );
393 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_cost_method: '|| NVL(v_cost_mthd,'NULL'));
394 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_cmpntcls_id: '|| NVL(v_cmpntcls_id,0));
395 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_analysis_code: '|| NVL(v_analysis_code,'NULL'));
396 FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_result_code: '|| NVL(v_result_code,0));
397 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_total_cost: '|| NVL(v_item_cost,0));
398 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_no_of_rows: '|| NVL(v_no_of_rows,0));
399 FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_return_status: '|| NVL(v_return_status,'N'));
400 IF v_result_code = 1 THEN
401 SELECT decode(l_min_acct_unit,-1,round(nvl(v_item_cost,0), l_rounding_factor),
402 round(nvl(v_item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
403 INTO l_price
404 FROM dual;
405 ELSE
406 l_price := 0;
407 END IF;
408 ELSE
409
410 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-4-1');
411 select decode(l_min_acct_unit,
412 -1,
413 nvl(cst.item_cost,0),
414 round(nvl(cst.item_cost,0)/l_min_acct_unit)*l_min_acct_unit)
415 Into l_price
416 from mtl_system_items mtl, cst_item_costs_for_gl_view cst
417 where mtl.inventory_item_id = l_item_tbl(l_index)
418 and cst.inventory_item_id =mtl.inventory_item_id
419 and cst.organization_id = p_costorg_id
420 and mtl.organization_id = p_organization_id;
421 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query-4');
422 END IF;
423 End If;
424
425 END IF;
426 Exception
427 When No_Data_Found Then
428 l_price := 0;
429 FND_FILE.PUT_LINE(FND_FILE.LOG, '1ERROR-'||sqlerrm);
430 End;
431
432
433 select mtl.primary_uom_code
434 into l_uom
435 from mtl_system_items mtl
439 select qp_list_lines_s.nextval
436 where mtl.inventory_item_id = l_item_tbl(l_index)
437 and mtl.organization_id = p_organization_id;
438
440 into l_list_line_id
441 from dual;
442 -- 10185331
443 SELECT orig_system_header_ref INTO
444 l_orig_sys_header_ref
445 FROM qp_list_headers_all_b
446 WHERE list_header_id = p_price_list_id;
447 -- 10185331
448 insert
449 into qp_list_lines
450 (LIST_LINE_ID,
451 LIST_LINE_NO,
452 LAST_UPDATE_DATE,
453 CREATION_DATE,
454 LIST_PRICE_UOM_CODE,
455 LIST_PRICE,
456 LAST_UPDATED_BY,
457 CREATED_BY,
458 LAST_UPDATE_LOGIN,
459 LIST_HEADER_ID,
460 REQUEST_ID,
461 PROGRAM_APPLICATION_ID,
462 PROGRAM_ID,
463 PROGRAM_UPDATE_DATE,
464 LIST_LINE_TYPE_CODE,
465 START_DATE_ACTIVE,
466 AUTOMATIC_FLAG,
467 PRICING_PHASE_ID,
468 OPERAND,
469 ARITHMETIC_OPERATOR,
470 INCOMPATIBILITY_GRP_CODE,
471 PRODUCT_PRECEDENCE,
472 MODIFIER_LEVEL_CODE,
473 QUALIFICATION_IND
474 -- Bug 5202021 RAVI
475 ,ORIG_SYS_LINE_REF
476 ,ORIG_SYS_HEADER_REF) -- 10185331
477 values (
478 l_list_line_id,
479 l_list_line_id,
480 -- Begin Bug No: 7281484
481 sysdate,
482 sysdate,
483 -- End Bug No: 7281484
484 l_uom,
485 l_price,
486 l_user_id,
487 l_user_id,
488 l_conc_login_id,
489 p_price_list_id,
490 l_conc_request_id,
491 l_conc_program_application_id,
492 l_conc_program_id,
493 sysdate, --Bug No: 7281484
494 'PLL',
495 trunc(sysdate),
496 'Y',
497 1,
498 l_price,
499 'UNIT_PRICE',
500 'EXCL',
501 l_sequence_num, --modified by dhgupta for bug 2113793
502 -- 220,
503 'LINE',
504 l_qualification_ind
505 -- Bug 5202021 RAVI
506 ,l_list_line_id
507 ,l_orig_sys_header_ref); -- 10185331
508
509 insert
510 into qp_pricing_attributes
511 (pricing_attribute_id,
512 creation_date,
513 created_by,
514 last_update_date,
515 last_updated_by,
516 last_update_login,
517 program_application_id,
518 program_id,
519 program_update_date,
520 request_id,
521 list_line_id,
522 list_header_id,
523 pricing_phase_id,
524 qualification_ind,
525 product_attribute_context,
526 product_attribute,
527 product_attribute_datatype, --3099578
528 product_attr_value,
529 product_uom_code,
530 excluder_flag,
531 accumulate_flag,
532 comparison_operator_code, --2814272
533 attribute_grouping_no
534 -- Bug 5202021 RAVI
535 ,ORIG_SYS_PRICING_ATTR_REF
536 ,ORIG_SYS_HEADER_REF) -- 10185331
537 values (qp_pricing_attributes_s.nextval,
538 sysdate, -- Bug No: 7281484
539 l_user_id,
540 sysdate, -- Bug No: 7281484
541 l_user_id,
542 l_conc_login_id,
543 l_conc_program_application_id,
544 l_conc_program_id,
545 sysdate, -- Bug No: 7281484
546 l_conc_request_id,
547 l_list_line_id,
548 p_price_list_id,
549 1,
550 l_qualification_ind,
551 'ITEM',
552 'PRICING_ATTRIBUTE1',
553 'C', --3099578
554 l_item_tbl(l_index),
555 l_uom,
556 'N',
557 'N',
558 'BETWEEN', --2814272
559 l_attr_grp_s
560 -- Bug 5202021 RAVI
561 ,qp_pricing_attributes_s.CURRVAL
562 ,l_orig_sys_header_ref); -- 10185331
563
564 END IF;
565
566 l_index := l_item_tbl.NEXT(l_index);
567
568 END LOOP;
569
570 /* ---------------------------------------------------------------------------+
571 | Create price list line for inventory items not on price list |
572 | DO NOT Set list_price = cost |
573 + --------------------------------------------------------------------------*/
574 ELSE
575 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DO NOT Set list_price = cost');
576 l_index := l_item_tbl.FIRST;
577 WHILE l_index <= l_item_tbl.LAST LOOP
578 begin
579 /*
580 select count(*)
581 into l_already_exists
582 from qp_pricing_attributes qppa
583 where qppa.list_header_id = p_price_list_id
584 and qppa.pricing_phase_id = 1
585 and qppa.product_attribute_context = 'ITEM'
586 and qppa.product_attr_value = l_item_tbl(l_index);
587 */
588 l_already_exists := 0;
589 Select 1
590 Into l_already_exists
591 From Dual
592 Where Exists
593 (Select Null
594 from qp_pricing_attributes qppa
595 where qppa.list_header_id = p_price_list_id
596 and qppa.pricing_phase_id = 1
597 and qppa.product_attribute_context = 'ITEM'
598 and qppa.product_attribute = 'PRICING_ATTRIBUTE1'
599 and qppa.product_attr_value = l_item_tbl(l_index)
600 );
601 exception
602 when no_data_found then
603 l_already_exists := 0;
604 WHEN OTHERS THEN
605 RAISE;
606 end;
607 IF l_already_exists > 0 THEN
608 -- write rec log of values that cannot be added
609 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Number '|| l_item_tbl(l_index) || ' already exists');
610 ELSE
611
612 select mtl.primary_uom_code
613 into l_uom
614 from mtl_system_items mtl
615 where mtl.inventory_item_id = l_item_tbl(l_index)
616 and mtl.organization_id = p_organization_id;
617 -- 10185331
618 SELECT orig_system_header_ref INTO
619 l_orig_sys_header_ref
620 FROM qp_list_headers_all_b
621 WHERE list_header_id = p_price_list_id;
622 -- 10185331
623
624 insert
625 into qp_list_lines
626 (LIST_LINE_ID,
627 LIST_LINE_NO,
628 LAST_UPDATE_DATE,
629 CREATION_DATE,
630 LIST_PRICE_UOM_CODE,
631 LIST_PRICE,
632 LAST_UPDATED_BY,
633 CREATED_BY,
634 LAST_UPDATE_LOGIN,
635 LIST_HEADER_ID,
636 REQUEST_ID,
637 PROGRAM_APPLICATION_ID,
638 PROGRAM_ID,
639 PROGRAM_UPDATE_DATE,
640 LIST_LINE_TYPE_CODE,
641 START_DATE_ACTIVE,
642 AUTOMATIC_FLAG,
643 PRICING_PHASE_ID,
644 OPERAND,
645 ARITHMETIC_OPERATOR,
646 INCOMPATIBILITY_GRP_CODE,
647 PRODUCT_PRECEDENCE,
648 MODIFIER_LEVEL_CODE,
649 QUALIFICATION_IND
650 -- Bug 5202021 RAVI
651 ,ORIG_SYS_LINE_REF
652 ,ORIG_SYS_HEADER_REF) -- 10185331
653 values (
654 qp_list_lines_s.nextval,
655 qp_list_lines_s.currval,
656 -- Begin Bug No: 7281484
657 sysdate,
658 sysdate,
659 -- End -- Bug No: 7281484
660 l_uom,
661 0,
662 l_user_id,
663 l_user_id,
664 l_conc_login_id,
665 p_price_list_id,
666 l_conc_request_id,
667 l_conc_program_application_id,
668 l_conc_program_id,
669 sysdate, -- Bug No: 7281484
670 'PLL',
671 trunc(sysdate),
672 'Y',
673 1,
674 0,
675 'UNIT_PRICE',
676 'EXCL',
677 l_sequence_num,
678 --220, --modified by dhgupta for bug 2113793
679 'LINE',
680 l_qualification_ind
681 -- Bug 5202021 RAVI
682 ,qp_list_lines_s.CURRVAL
683 ,l_orig_sys_header_ref); -- 10185331
684
685 /*------------------------------------------------------------------------+
686 | Insert pricing attributes |
687 +-----------------------------------------------------------------------*/
688
689
690 insert
691 into qp_pricing_attributes
692 (PRICING_ATTRIBUTE_ID,
693 CREATION_DATE,
694 CREATED_BY,
695 LAST_UPDATE_DATE,
696 LAST_UPDATED_BY,
697 LAST_UPDATE_LOGIN,
698 PROGRAM_APPLICATION_ID,
699 PROGRAM_ID,
700 PROGRAM_UPDATE_DATE,
701 REQUEST_ID,
702 LIST_LINE_ID,
703 LIST_HEADER_ID,
704 PRICING_PHASE_ID,
705 QUALIFICATION_IND,
706 PRODUCT_ATTRIBUTE_CONTEXT,
707 PRODUCT_ATTRIBUTE,
708 product_attribute_datatype, --3099578
709 PRODUCT_ATTR_VALUE,
710 PRODUCT_UOM_CODE,
711 EXCLUDER_FLAG,
712 ACCUMULATE_FLAG,
713 comparison_operator_code, --2814272
714 ATTRIBUTE_GROUPING_NO
715 -- Bug 5202021 RAVI
716 ,ORIG_SYS_PRICING_ATTR_REF
717 ,ORIG_SYS_HEADER_REF) -- 10185331
718 values (qp_pricing_attributes_s.nextval,
719 sysdate, -- Bug No: 7281484
720 l_user_id,
721 sysdate, -- Bug No: 7281484
722 l_user_id,
723 l_conc_login_id,
724 l_conc_program_application_id,
725 l_conc_program_id,
726 sysdate, -- Bug No: 7281484
727 l_conc_request_id,
728 qp_list_lines_s.currval,
729 p_price_list_id,
730 1,
731 l_qualification_ind,
732 'ITEM',
733 'PRICING_ATTRIBUTE1',
734 'C', --3099578
735 l_item_tbl(l_index),
736 l_uom,
737 'N',
738 'N',
739 'BETWEEN', --2814272
740 l_attr_grp_s
741 -- Bug 5202021 RAVI
742 ,qp_pricing_attributes_s.CURRVAL
743 ,l_orig_sys_header_ref); -- 10185331
744
745 END IF;
746
747 l_index := l_item_tbl.NEXT(l_index);
748 l_commit_count:=l_commit_count+1; --Commiting after every 100 records for bug2363369
749 IF l_commit_count=100 THEN
750 l_commit_count:=0;
751 COMMIT;
752 END IF;
753
754
755 END LOOP;
756
757 END IF;
758 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
759 select min(list_line_id), max(list_line_id)
760 into l_min_list_line_id, l_max_list_line_id
761 from qp_list_lines
762 where list_header_id = p_price_list_id;
763
764 QP_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
768 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'N' THEN
765 QP_ATTR_GRP_PVT.update_pp_lines(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
766 END IF;
767 --- jagan PL/SQL pattern engine
769 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' THEN
770 select min(list_line_id), max(list_line_id)
771 into l_min_list_line_id, l_max_list_line_id
772 from qp_list_lines
773 where list_header_id = p_price_list_id;
774
775 QP_PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
776 QP_PS_ATTR_GRP_PVT.update_pp_lines(p_price_list_id, l_min_list_line_id, l_max_list_line_id);
777 END IF;
778 END IF;
779 COMMIT;
780 retcode:=0;
781 errbuf:='';
782 EXCEPTION
783 WHEN others THEN
784 ROLLBACK;
785 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLCODE||' -DK- '||SQLERRM);
786 retcode:=2;
787 END Add_Items_To_Price_List;
788
789 END QP_ADD_ITEM_PRCLIST_PVT;