[Home] [Help]
PACKAGE BODY: APPS.QP_BULK_PREQ_GRP
Source
1 PACKAGE BODY QP_BULK_PREQ_GRP AS
2 /* $Header: QPBGPREB.pls 120.1 2005/08/19 18:12:35 spgopal noship $ */
3
4 --API to indicate calling applications whether pricing
5 --is happening through HVOP or NOT
6 --This check needs to be done in QP_SOURCING_API_PUB
7 --and other custom APIs which may have references to
8 --OE_ORDER_PUB.G_LINE/G_HDR to source any attributes
9 --so that those APIs look at the bulk memory structures
10 --to srouce values appropriately when pricing happens
11 --through HVOP
12
13 Procedure Bulk_insert_lines(p_header_rec IN OE_BULK_ORDER_PVT.HEADER_REC_TYPE,
14 p_line_rec IN OE_WSH_BULK_GRP.Line_Rec_Type,
15 p_org_id IN NUMBER DEFAULT NULL, --added for moac
16 x_return_status OUT NOCOPY VARCHAR2,
17
18 x_return_status_text OUT NOCOPY VARCHAR2) IS
19
20 l_qual_ctxts_result_tbl QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE;
21 l_price_ctxts_result_tbl QP_ATTR_MAPPING_PUB.CONTEXTS_RESULT_TBL_TYPE;
22
23 l_line_line_index QP_PREQ_GRP.number_type;
24 l_hdr_line_index QP_PREQ_GRP.number_type;
25 l_line_count NUMBER;
26 l_preq_count NUMBER;
27 my_usage_type VARCHAR2(30);
28 PRAGMA AUTONOMOUS_TRANSACTION;
29 i pls_integer;
30 type cur_code_type is table of varchar2(30) index by BINARY_INTEGER;
31 type date_type is table of date index by BINARY_INTEGER;
32 l_currency_code_tbl cur_code_type;
33 l_active_date_first_tbl date_type;
34 --added for moac
35 l_org_id NUMBER;
36
37 BEGIN
38
39 --added for moac
40 --because this is a group API we expect calling app to have set the org context
41 --but we need to populate QP_ATTR_MAPPING_PUB.G_ORG_ID for sourcing rules
42 l_org_id := nvl(p_org_id, QP_UTIL.get_org_id);
43 QP_ATTR_MAPPING_PUB.G_ORG_ID := l_org_id;
44
45 l_line_count := p_line_rec.line_id.count;
46 G_header_rec := p_header_rec;
47 G_line_rec := p_line_rec;
48 l_line_count := p_line_rec.line_id.count;
49 QP_PREQ_GRP.Set_QP_Debug;
50 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
51 If l_debug = 'T' Then
52 i := p_line_rec.line_id.first;
53 --changes for bug 3395657
54 --changed for to while loop and to loop based on line_index
55 --instead of line_id as line_id may be null
56 while i is not null
57 loop
58 --for i in 1..p_line_rec.line_id.count loop
59 oe_debug_pub.add ('in bulk_preq, line_index : '|| p_line_rec.line_index(i));
60 i := p_line_rec.line_id.next(i);
61 end loop;
62 End If;--debug
63 G_HVOP_pricing_ON := 'Y';
64
65
66 --OM will give a different structure to populate
67 --the new control rec columns introduced on lines_tmp for java engine
68
69 IF p_header_rec.header_id.COUNT > 0 THEN
70 forall i in p_header_rec.header_id.FIRST..p_header_rec.header_id.LAST
71 insert into qp_int_lines
72 (
73 LINE_INDEX,
74 LINE_TYPE_CODE,
75 PRICING_EFFECTIVE_DATE,
76 START_DATE_ACTIVE_FIRST,
77 ACTIVE_DATE_FIRST_TYPE,
78 START_DATE_ACTIVE_SECOND,
79 ACTIVE_DATE_SECOND_TYPE,
80 LINE_QUANTITY,
81 LINE_UOM_CODE,
82 REQUEST_TYPE_CODE,
83 PRICED_QUANTITY,
84 PRICED_UOM_CODE,
85 CURRENCY_CODE,
86 UNIT_PRICE,
87 ADJUSTED_UNIT_PRICE,
88 PRICE_FLAG,
89 UOM_QUANTITY,
90 LINE_ID,
91 HEADER_ID,
92 ROUNDING_FACTOR,
93 UPDATED_ADJUSTED_UNIT_PRICE,
94 PRICE_LIST_HEADER_ID,
95 PRICE_REQUEST_CODE,
96 USAGE_PRICING_TYPE,
97 LINE_CATEGORY,
98 CONTRACT_START_DATE,
99 CONTRACT_END_DATE,
100 CATCHWEIGHT_QTY,
101 ACTUAL_ORDER_QUANTITY,
102 PRICING_STATUS_CODE,
103 PRICING_EVENT
104
105 )
106 values
107 (
108 p_header_rec.header_index(i)+l_line_count,--p_header_rec.line_index(i),--Will OM populate this as well??
109 'ORDER',
110 p_header_rec.PRICING_DATE(i),
111 p_header_rec.ORDERED_DATE(i),--p_line_rec.ACTIVE_DATE_FIRST(i)),
112 'ORDER',--p_line_rec.ACTIVE_DATE_FIRST_TYPE(i),
113 NULL,--p_line_rec.ACTIVE_DATE_SECOND(i)),
114 NULL,--p_line_rec.ACTIVE_DATE_SECOND_TYPE(i),
115 NULL,
116
117 NULL,
118 'ONT',--request_type_code hard-coded
119 NULL,
120 NULL,
121 p_header_rec.transactional_curr_code(i),
122 NULL,
123 NULL,
124 'Y',--this needs to change after OM adds calculate_price_flag to their rec structure--p_header_rec.CALCULATE_PRICE_FLAG(i),
125 NULL,--service pricing not supported
126 NULL,--line_id is null for summary line
127 p_header_rec.HEADER_ID(i),
128
129 NULL,--rounding_factor is null
130 NULL,--updated_adjusted_unit_price w/b null for OM
131 p_header_rec.PRICE_LIST_ID(i),
132 NULL,--price_request_code not required as limits not supported
133 QP_PREQ_GRP.G_REGULAR_USAGE_TYPE,--usage_pricing_type
134 'ORDER',--line_category
135 NULL,--CONTRACT_START_DATE
136 NULL,--CONTRACT_END_DATE
137 NULL,--CATCHWEIGHT_QTY
138 NULL,--ACTUAL_ORDER_QUANTITY
139 'X',
140 p_header_rec.event_code(i)
141 );
142
143 QP_PREQ_GRP.G_INT_LINES_NO := QP_PREQ_GRP.G_INT_LINES_NO + l_line_count;
144
145 If l_debug = 'T' Then
146 select count(*) into l_preq_count
147 from qp_int_lines ;
148 oe_debug_pub.add('bulk.l_preq_count := ' || l_preq_count);
149 oe_debug_pub.add('QP_PREQ_GRP.G_INT_LINES_NO='||QP_PREQ_GRP.G_INT_LINES_NO);
150 End If;
151
152 G_line_index.delete;
153
154 G_attr_type.delete;
155 G_attr_context.delete;
156 G_attr_attr.delete;
157 G_attr_value.delete;
158 G_validated_flag.delete;
159
160 If l_debug = 'T' Then
161 oe_debug_pub.add('Before QP_BUILD_SOURCING_PVT.get_attribute_values (H)');
162 oe_debug_pub.add ('G_line_index.count := ' || G_line_index.count);
163 oe_debug_pub.add ('G_line_rec.line_id.count := ' || G_line_rec.line_id.count);
164 End If;
165
166
167 Begin
168 QP_BUILD_SOURCING_PVT.get_attribute_values
169 ('ONT', 'H',
170 l_qual_ctxts_result_tbl, l_price_ctxts_result_tbl);
171 Exception
172 when others then
173 Null;
174 If l_debug = 'T' Then
175 oe_debug_pub.add ('Build Sourcing Errored Out with: ' || sqlerrm);
176 End If;
177 End;
178
179
180 END IF;--p_header_rec.line_id.COUNT
181
182 If l_debug = 'T' Then
183 oe_debug_pub.add('GlineCOUNT = '||G_line_index.COUNT);
184
185 if G_line_index.count > 0 then
186 null;
187 oe_debug_pub.add('Glinelast = '||G_line_index.last);
188 end if;
189
190 --need to populate the new columns added to lines_tmp
191 --which are columns on ctrl rec for java engine
192
193 oe_debug_pub.add ('*************Bulk Debug Holes************');
194
195
196 for i in G_line_index.first..G_line_index.last loop
197 oe_debug_pub.add ('i := ' || i);
198 oe_debug_pub.add ('G_line_index(i)+l_line_count '|| (G_line_index(i)+l_line_count));
199 oe_debug_pub.add ('G_attr_type(i) '|| G_attr_type(i));
200 oe_debug_pub.add ('G_attr_context(i) '|| G_attr_context(i));
201 oe_debug_pub.add ('G_attr_attr(i) '|| G_attr_attr(i));
202 oe_debug_pub.add ('G_attr_value(i) '|| G_attr_value(i));
203 oe_debug_pub.add ('G_validated_flag(i) '|| NVL (G_validated_flag(i), 'N'));
204 end loop;
205 oe_debug_pub.add ('*************Bulk Debug Holes************');
206 End If; --debug
207
208
209 BEGIN
210 If G_line_index.count > 0 then
211 forall i in G_line_index.first..G_line_index.last
212 insert into qp_int_line_attrs
213 (line_index,
214 attribute_level,
215 attribute_type,
216 context,
217 attribute,
218 value_from,
219 validated_flag,
220 applied_flag,
221 pricing_status_code,
222 pricing_attr_flag
223 )
224 VALUES (G_line_index(i)+l_line_count,
225 QP_PREQ_PUB.G_LINE_LEVEL,
226 G_attr_type(i),
227 G_attr_context(i),
228 G_attr_attr(i),
229 G_attr_value(i),
230 NVL (G_validated_flag(i), 'N'),
231 QP_PREQ_PUB.G_LIST_NOT_APPLIED,
232 QP_PREQ_PUB.G_STATUS_UNCHANGED,
233 QP_PREQ_PUB.G_YES
234 );
235
236 QP_PREQ_GRP.G_INT_ATTRS_NO := QP_PREQ_GRP.G_INT_ATTRS_NO + G_attr_context.count;
237 IF l_debug = 'T' THEN
238 oe_debug_pub.add ('QP_PREQ_GRP.G_INT_ATTRS_NO='||QP_PREQ_GRP.G_INT_ATTRS_NO);
239 END IF;
240 End If;
241 EXCEPTION
242 WHEN OTHERS THEN
243 If l_debug = 'T' Then
244 oe_debug_pub.add('Error inserting to qp_preq_line_attrs_temp' || sqlerrm);
245 End If;
246 END;
247
248
249
250 IF p_line_rec.line_id.COUNT > 0 THEN
251
252
253 FOR i in p_line_rec.line_id.FIRST..p_line_rec.line_id.LAST
254 LOOP
255 FOR j in p_header_rec.header_id.FIRST..p_header_rec.header_id.LAST
256 LOOP
257 IF p_header_rec.header_id(j)= p_line_rec.header_id(i) THEN
258 l_currency_code_tbl(i):= p_header_rec.transactional_curr_code(j);
259 l_active_date_first_tbl(i):= p_header_rec.ordered_date(j);
260 END IF;
261 END LOOP;
262 END LOOP;
263
264 for i in l_currency_code_tbl.FIRST..l_currency_code_tbl.LAST LOOP
265 qp_preq_grp.engine_debug('UK Code '||l_currency_code_tbl(i));
266 qp_preq_grp.engine_debug('UK Date '||l_active_date_first_tbl(i));
267 end loop;
268
269
270 forall i in p_line_rec.line_id.FIRST..p_line_rec.line_id.LAST
271 insert into qp_int_lines
272 (
273 LINE_INDEX,
274 LINE_TYPE_CODE,
275 PRICING_EFFECTIVE_DATE,
276 START_DATE_ACTIVE_FIRST,
277 ACTIVE_DATE_FIRST_TYPE,
278 START_DATE_ACTIVE_SECOND,
279 ACTIVE_DATE_SECOND_TYPE,
280 LINE_QUANTITY,
281 LINE_UOM_CODE,
282 REQUEST_TYPE_CODE,
283 PRICED_QUANTITY,
284 PRICED_UOM_CODE,
285 CURRENCY_CODE,
286 UNIT_PRICE,
287 ADJUSTED_UNIT_PRICE,
288 PRICE_FLAG,
289 UOM_QUANTITY,
290 LINE_ID,
291 HEADER_ID,
292 ROUNDING_FACTOR,
293 UPDATED_ADJUSTED_UNIT_PRICE,
294 PRICE_LIST_HEADER_ID,
295 PRICE_REQUEST_CODE,
296 USAGE_PRICING_TYPE,
297 LINE_CATEGORY,
298 CONTRACT_START_DATE,
299 CONTRACT_END_DATE,
300 CATCHWEIGHT_QTY,
301 ACTUAL_ORDER_QUANTITY,
302 PRICING_STATUS_CODE
303 )
304 values
305 (
306 --select
307 p_line_rec.LINE_INDEX(i),
308 'LINE', --line_type_code
309 p_line_rec.PRICING_DATE(i), --OM will default the right value
310 --line.START_DATE_ACTIVE_FIRST,
311 l_active_date_first_tbl(i),
312 'ORDER',--p_line_rec.ACTIVE_DATE_FIRST_TYPE(i),
313 NULL,--p_line_rec.ACTIVE_DATE_SECOND(i)),
314 NULL,--p_line_rec.ACTIVE_DATE_SECOND_TYPE(i),
315 p_line_rec.ORDERED_QUANTITY(i),
316 p_line_rec.ORDER_QUANTITY_UOM(i),
317 'ONT',--request_type_code hard-coded
318 p_line_rec.PRICING_QUANTITY(i),
319 p_line_rec.PRICING_QUANTITY_UOM(i),
320 -- line.currency_code,
321 l_CURRENCY_CODE_tbl(i), --this w/b populated by OM
322 p_line_rec.unit_list_price_per_pqty(i),
323 p_line_rec.unit_selling_price_per_pqty(i),
324
325 p_line_rec.CALCULATE_PRICE_FLAG(i),----OM will default the right value
326 NULL,--uom_qty service pricing is not supported for this phase of HVOP
327 p_line_rec.LINE_ID(i),
328 p_line_rec.HEADER_ID(i),
329 NULL,--rounding_factor is null
330 NULL,--UPDATED_ADJUSTED_UNIT_PRICE is null for OM
331 p_line_rec.PRICE_LIST_ID(i),
332 NULL,--price_req_code is null limits not supported for this phase of HVOP
333 QP_PREQ_GRP.G_REGULAR_USAGE_TYPE,--usage_pricing_type
334 p_line_rec.LINE_CATEGORY_CODE(i),
335 NULL,--CONTRACT_START_DATE
336 NULL,--CONTRACT_END_DATE
337 NULL,--CATCHWT_QTY
338
339 NULL,--ACTUAL_ORDER_QTY
340 'X' );
341 /*FROM qp_int_lines line
342 WHERE line.header_id = p_line_rec.HEADER_ID(i)
343 AND ROWNUM = 1; */
344
345 QP_PREQ_GRP.G_INT_LINES_NO := QP_PREQ_GRP.G_INT_LINES_NO + p_line_rec.LINE_ID.count;
346
347 if l_debug = 'T' then
348 oe_debug_pub.add ('QP_PREQ_GRP.G_INT_LINES_NO :=' || QP_PREQ_GRP.G_INT_LINES_NO);
349 end if; --debug
350
351 END IF;--p_line_rec.line_id.COUNT
352
353 G_line_index.delete;
354 G_attr_type.delete;
355 G_attr_context.delete;
356 G_attr_attr.delete;
357 G_attr_value.delete;
358 G_validated_flag.delete;
359
360
361 If l_debug = 'T' Then
362 oe_debug_pub.add ('Checking header_id on lines.....');
363 IF p_line_rec.line_id.count > 0 THEN
364 for i in p_line_rec.line_id.first..p_line_rec.line_id.last
365 loop
366 IF p_line_rec.header_id.exists(i) THEN
367 oe_debug_pub.add ('header_id ' ||i || ': ' || p_line_rec.header_id(i));
368 oe_debug_pub.add ('header_index ' ||i || ': ' || p_line_rec.header_index(i));
369 END IF;
370 end loop;
371 oe_debug_pub.add('Before get_attributes code=L');
372 END IF;
373 End If;
374
375 QP_BUILD_SOURCING_PVT.get_attribute_values
376 ('ONT', 'L',
377 l_qual_ctxts_result_tbl, l_price_ctxts_result_tbl);
378
379 If l_debug = 'T' Then
380 oe_debug_pub.add('After get_attributes code=L');
381 oe_debug_pub.add ('*************Bulk Debug Holes Lines Begin************');
382 for i in G_line_index.first..G_line_index.last loop
383 Null;
384 oe_debug_pub.add ('i := ' || i);
385 oe_debug_pub.add ('G_line_index(i)+l_line_count '|| (G_line_index(i)+l_line_count));
386 oe_debug_pub.add ('G_attr_type(i) '|| G_attr_type(i));
387 oe_debug_pub.add ('G_attr_context(i) '|| G_attr_context(i));
388 oe_debug_pub.add ('G_attr_attr(i) '|| G_attr_attr(i));
389 oe_debug_pub.add ('G_attr_value(i) '|| G_attr_value(i));
390 oe_debug_pub.add ('G_validated_flag(i) '|| NVL (G_validated_flag(i), 'N'));
391 end loop;
392 oe_debug_pub.add ('*************Bulk Debug Holes Lines End************');
393 End If;
394
395 if G_line_index.count > 0 then
396 forall i in G_line_index.first..G_line_index.last
397 insert into qp_int_line_attrs
398 (line_index,
399 attribute_level,
400 attribute_type,
401 context,
402 attribute,
403 value_from,
404 validated_flag,
405 applied_flag,
406 pricing_status_code,
407 pricing_attr_flag
408 )
409 VALUES (G_line_index(i),
410 QP_PREQ_PUB.G_LINE_LEVEL,
411 G_attr_type(i),
412 G_attr_context(i),
413 G_attr_attr(i),
414 G_attr_value(i),
415 G_validated_flag(i),
416 QP_PREQ_PUB.G_LIST_NOT_APPLIED,
417 QP_PREQ_PUB.G_STATUS_UNCHANGED,
418 QP_PREQ_PUB.G_YES
419 );
420 QP_PREQ_GRP.G_INT_ATTRS_NO := QP_PREQ_GRP.G_INT_ATTRS_NO + G_attr_context.count;
421 IF l_debug = FND_API.G_TRUE THEN
422 oe_debug_pub.add ('QP_PREQ_GRP.G_INT_ATTRS_NO='||QP_PREQ_GRP.G_INT_ATTRS_NO);
423 END IF;
424 end if;--G_line_index.count
425
426 If l_debug = 'T' Then
427 oe_debug_pub.add ('Finished QPBGPREB lines: Success');
428 End If;
429 COMMIT;
430
431 EXCEPTION
432 When OTHERS Then
433 x_return_status := FND_API.G_RET_STS_ERROR;
434 x_return_status_text := 'Exception in Bulk_insert_lines '||SQLERRM;
435 If l_debug = 'T' Then
436 oe_debug_pub.add ('Finished QPBGPREB lines:' ||SQLERRM);
437 End If;
438 END Bulk_insert_lines;
439
440 Procedure Bulk_insert_adj(x_return_status OUT NOCOPY VARCHAR2,
441 x_return_status_text OUT NOCOPY VARCHAR2) IS
442 ldet_count number;
443 line_ldet_count number; --added for bug 3406218
444 PRAGMA AUTONOMOUS_TRANSACTION;
445 BEGIN
446
447 If l_debug = 'T' Then
448 oe_debug_pub.add ('Started QPBGPREB Adjs:' ||SQLERRM);
449 End If;
450 insert into qp_int_ldets
451 (LINE_DETAIL_INDEX,
452 LINE_DETAIL_TYPE_CODE,
453 PRICE_BREAK_TYPE_CODE,
454 LINE_INDEX,
455 CREATED_FROM_LIST_HEADER_ID,
456 CREATED_FROM_LIST_LINE_ID,
457 CREATED_FROM_LIST_LINE_TYPE,
458 CREATED_FROM_LIST_TYPE_CODE,
459 PRICING_GROUP_SEQUENCE,
460 PRICING_PHASE_ID,
461 OPERAND_CALCULATION_CODE,
462 OPERAND_VALUE,
463 SUBSTITUTION_TYPE_CODE,
464 SUBSTITUTION_VALUE_FROM,
465 SUBSTITUTION_VALUE_TO,
466 PRICE_FORMULA_ID,
467 PRICING_STATUS_CODE,
468 PRODUCT_PRECEDENCE,
469 INCOMPATABILITY_GRP_CODE,
470 APPLIED_FLAG,
471 AUTOMATIC_FLAG,
472 OVERRIDE_FLAG,
473 MODIFIER_LEVEL_CODE,
474 BENEFIT_QTY,
475 BENEFIT_UOM_CODE,
476 LIST_LINE_NO,
477 ACCRUAL_FLAG,
478 ACCRUAL_CONVERSION_RATE,
479 ESTIM_ACCRUAL_RATE,
480 CHARGE_TYPE_CODE,
481 CHARGE_SUBTYPE_CODE,
482 PROCESS_CODE,
483 LINE_QUANTITY,
484 UPDATED_FLAG, -- begin shu, fix Bug 2599822
485 CALCULATION_CODE,
486 CHANGE_REASON_CODE,
487 CHANGE_REASON_TEXT,
488 ADJUSTMENT_AMOUNT)
489 (select
490 rownum,
491 'NULL',
492 ll.PRICE_BREAK_TYPE_CODE,
493 line.LINE_INDEX,
494 ll.LIST_HEADER_ID,
495 ll.LIST_LINE_ID,
496 ll.LIST_LINE_TYPE_CODE,
497 lhdr.LIST_TYPE_CODE,
498 ll.PRICING_GROUP_SEQUENCE,
499 ll.PRICING_PHASE_ID,
500 ll.ARITHMETIC_OPERATOR,
501 nvl(adj_iface.OPERAND_PER_PQTY,adj_iface.OPERAND),
502 ll.SUBSTITUTION_ATTRIBUTE,
503 ll.SUBSTITUTION_VALUE,
504 NULL,
505 ll.PRICE_BY_FORMULA_ID,
506 QP_PREQ_PUB.G_STATUS_UNCHANGED,
507 ll.PRODUCT_PRECEDENCE,
508 ll.INCOMPATIBILITY_GRP_CODE,
509 adj_iface.APPLIED_FLAG,
510 ll.AUTOMATIC_FLAG,
511 ll.OVERRIDE_FLAG,
512 ll.MODIFIER_LEVEL_CODE,
513 ll.BENEFIT_QTY,
514 ll.BENEFIT_UOM_CODE,
515 ll.LIST_LINE_NO,
516 ll.ACCRUAL_FLAG,
517 ll.ACCRUAL_CONVERSION_RATE,
518 ll.ESTIM_ACCRUAL_RATE,
519 ll.CHARGE_TYPE_CODE,
520 ll.CHARGE_SUBTYPE_CODE,
521 QP_PREQ_PUB.G_STATUS_NEW,
522 line.priced_quantity, --adj_iface.range_break_quantity, --need to find out what engine populates this as
523 adj_iface.UPDATED_FLAG, -- begin shu, fix Bug 2599822
524 NULL,
525 NULL,
526 NULL,
527 adj_iface.ADJUSTED_AMOUNT_PER_PQTY
528 From oe_price_adjs_iface_all adj_iface,
529 oe_lines_iface_all lines_iface,
530 qp_int_lines line,
531 qp_list_lines ll,
532 qp_list_headers_b lhdr
533 Where adj_iface.ORDER_SOURCE_ID = lines_iface.ORDER_SOURCE_ID
534 and adj_iface.ORIG_SYS_DOCUMENT_REF = lines_iface.ORIG_SYS_DOCUMENT_REF
535 and adj_iface.ORIG_SYS_LINE_REF = lines_iface.ORIG_SYS_LINE_REF
536 and line.line_type_code = 'LINE'
537 and lines_iface.line_id = line.line_id
538 --and lines_iface.header_id = line.header_id
539 and ll.list_line_id = adj_iface.list_line_id
540 and lhdr.list_header_id = ll.list_header_id);
541
542 select nvl(max(line_detail_index),0) into line_ldet_count from qp_int_ldets; -- fix for bug 3406218
543
544 If l_debug = 'T' Then
545 oe_debug_pub.add('Number of line level adjustments are '||line_ldet_count);
546 End if;
547
548 --UNION --split the insert statement for bug 3406218
549 insert into qp_int_ldets
550 (LINE_DETAIL_INDEX,
551 LINE_DETAIL_TYPE_CODE,
552 PRICE_BREAK_TYPE_CODE,
553 LINE_INDEX,
554 CREATED_FROM_LIST_HEADER_ID,
555 CREATED_FROM_LIST_LINE_ID,
556 CREATED_FROM_LIST_LINE_TYPE,
557 CREATED_FROM_LIST_TYPE_CODE,
558 PRICING_GROUP_SEQUENCE,
559 PRICING_PHASE_ID,
560 OPERAND_CALCULATION_CODE,
561 OPERAND_VALUE,
562 SUBSTITUTION_TYPE_CODE,
563 SUBSTITUTION_VALUE_FROM,
564 SUBSTITUTION_VALUE_TO,
565 PRICE_FORMULA_ID,
566 PRICING_STATUS_CODE,
567 PRODUCT_PRECEDENCE,
568 INCOMPATABILITY_GRP_CODE,
569 APPLIED_FLAG,
570 AUTOMATIC_FLAG,
571 OVERRIDE_FLAG,
572 MODIFIER_LEVEL_CODE,
573 BENEFIT_QTY,
574 BENEFIT_UOM_CODE,
575 LIST_LINE_NO,
576 ACCRUAL_FLAG,
577 ACCRUAL_CONVERSION_RATE,
578 ESTIM_ACCRUAL_RATE,
579 CHARGE_TYPE_CODE,
580 CHARGE_SUBTYPE_CODE,
581 PROCESS_CODE,
582 LINE_QUANTITY,
583 UPDATED_FLAG, -- begin shu, fix Bug 2599822
584 CALCULATION_CODE,
585 CHANGE_REASON_CODE,
586 CHANGE_REASON_TEXT,
587 ADJUSTMENT_AMOUNT)
588 (select
589 rownum+line_ldet_count,
590 'NULL',
591 ll.PRICE_BREAK_TYPE_CODE,
592 line.LINE_INDEX,
593 ll.LIST_HEADER_ID,
594 ll.LIST_LINE_ID,
595 ll.LIST_LINE_TYPE_CODE,
596 lhdr.LIST_TYPE_CODE,
597 ll.PRICING_GROUP_SEQUENCE,
598 ll.PRICING_PHASE_ID,
599 ll.ARITHMETIC_OPERATOR,
600 nvl(adj_iface.OPERAND_PER_PQTY,adj_iface.OPERAND),
601 ll.SUBSTITUTION_ATTRIBUTE,
602 ll.SUBSTITUTION_VALUE,
603 NULL,
604 ll.PRICE_BY_FORMULA_ID,
605 QP_PREQ_PUB.G_STATUS_UNCHANGED,
606 ll.PRODUCT_PRECEDENCE,
607 ll.INCOMPATIBILITY_GRP_CODE,
608 adj_iface.APPLIED_FLAG,
609 ll.AUTOMATIC_FLAG,
610 ll.OVERRIDE_FLAG,
611 ll.MODIFIER_LEVEL_CODE,
612 ll.BENEFIT_QTY,
613 ll.BENEFIT_UOM_CODE,
614 ll.LIST_LINE_NO,
615 ll.ACCRUAL_FLAG,
616 ll.ACCRUAL_CONVERSION_RATE,
617 ll.ESTIM_ACCRUAL_RATE,
618 ll.CHARGE_TYPE_CODE,
619 ll.CHARGE_SUBTYPE_CODE,
620 QP_PREQ_PUB.G_STATUS_NEW,
621 line.priced_quantity, --adj_iface.range_break_quantity, --need to find out what engine populates this as
622 adj_iface.UPDATED_FLAG, -- begin shu, fix Bug 2599822
623 NULL,
624 NULL,
625 NULL,
626 adj_iface.ADJUSTED_AMOUNT_PER_PQTY
627 From oe_price_adjs_iface_all adj_iface,
628 oe_headers_iface_all hdrs_iface,
629 qp_int_lines line,
630 qp_list_lines ll,
631 qp_list_headers_b lhdr
632 Where adj_iface.ORDER_SOURCE_ID = hdrs_iface.ORDER_SOURCE_ID
633 and adj_iface.ORIG_SYS_DOCUMENT_REF = hdrs_iface.ORIG_SYS_DOCUMENT_REF
634 and line.line_type_code = 'ORDER'
635 and adj_iface.ORIG_SYS_LINE_REF is null
636 and hdrs_iface.header_id = line.header_id -- fix for bug 3406218
637 and line.line_id is null
638 --and lines_iface.header_id = line.header_id
639 and ll.list_line_id = adj_iface.list_line_id
640 and lhdr.list_header_id = ll.list_header_id);
641
642 IF l_debug = FND_API.G_TRUE THEN
643 oe_debug_pub.add ('QP_BULK_PREQ_GRP.bulk_insert_adj inserted adj count='||SQL%ROWCOUNT);
644 END IF;
645
646 --added for java engine stats accumulation on li's request
647 select count(*) into ldet_count
648 From oe_price_adjs_iface_all adj_iface,
649 oe_lines_iface_all lines_iface,
650 qp_int_lines line,
651 qp_list_lines ll,
652 qp_list_headers_b lhdr
653 Where adj_iface.ORIG_SYS_LINE_REF = lines_iface.ORIG_SYS_LINE_REF
654 and lines_iface.line_id = line.line_id
655 --and lines_iface.header_id = line.header_id
656 and ll.list_line_id = adj_iface.list_line_id
657 and lhdr.list_header_id = ll.list_header_id;
658
659 QP_PREQ_GRP.G_INT_LDETS_NO := QP_PREQ_GRP.G_INT_LDETS_NO + ldet_count;
660 IF l_debug = FND_API.G_TRUE THEN
661 oe_debug_pub.add ('QP_PREQ_GRP.G_INT_LDETS_NO='||QP_PREQ_GRP.G_INT_LDETS_NO);
662 END IF;
663
664 IF l_debug = FND_API.G_TRUE THEN
665 FOR cl in (select line_index, line_detail_index, operand_value, operand_calculation_code, updated_flag, applied_flag
666 ,created_from_list_line_id, automatic_flag, override_flag from qp_int_ldets)
667 LOOP
668 oe_debug_pub.add('Inserted ldet details: line_index '||cl.line_index||' line_detail_index '||cl.line_detail_index
669 ||' created_from_list_line_id '||cl.created_from_list_line_id||' operand_value '||cl.operand_value||' arithmetic_operator '
670 ||cl.operand_calculation_code||' updated_flag '||cl.updated_flag||' applied_flag '||cl.applied_flag||' automatic_flag '
671 ||cl.automatic_flag||' override_flag '||cl.override_flag);
672 END LOOP;
673 END IF;
674
675 COMMIT;
676
677 If l_debug = 'T' Then
678 oe_debug_pub.add ('Finished QPBGPREB adjs: Success') ;
679 End If;
680 EXCEPTION
681 When OTHERS Then
682 x_return_status := FND_API.G_RET_STS_ERROR;
683 x_return_status_text := 'Exception in Bulk_insert_adj '||SQLERRM;
684 If l_debug = 'T' Then
685 oe_debug_pub.add ('Finished QPBGPREB adjs:' ||SQLERRM);
686 End If;
687 End Bulk_insert_adj;
688
689 END QP_BULK_PREQ_GRP;
690