[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_PRICEORDER_PVT
Source
1 PACKAGE BODY OE_BULK_PRICEORDER_PVT AS
2 /* $Header: OEBVOPRB.pls 120.3.12010000.3 2008/11/18 13:14:23 smusanna ship $ */
3
4
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_BULK_PRICEORDER_PVT';
7 G_CHARGES_FOR_INCLUDED_ITEM Varchar2(30)
8 := nvl(fnd_profile.value('ONT_CHARGES_FOR_INCLUDED_ITEM'),'N');
9 G_FUNCTION_CURRENCY VARCHAR2(30) default NULL;
10 G_PRICE_FLAG_TBL_EXTENDED BOOLEAN default null;
11
12
13
14 Type Price_Flag_Type Is Record
15 ( all_lines_y OE_WSH_BULK_GRP.T_V1 := OE_WSH_BULK_GRP.T_V1(),
16 all_lines_n OE_WSH_BULK_GRP.T_V1 := OE_WSH_BULK_GRP.T_V1(),
17 Mixed OE_WSH_BULK_GRP.T_V1 := OE_WSH_BULK_GRP.T_V1()
18 );
19
20 G_PRICE_FLAG Price_Flag_Type;
21
22 Function get_version Return Varchar2 is
23 Begin
24 Return('/* $Header: OEBVOPRB.pls 120.3.12010000.3 2008/11/18 13:14:23 smusanna ship $ */');
25 End;
26
27 Procedure set_price_flag(p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE,
28 p_index Number,
29 p_header_counter Number
30 ) IS
31 l_count number; -- bug 4558093
32 l_hdr_count number := OE_BULK_ORDER_PVT.G_HEADER_REC.HEADER_ID.count;
33 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
34
35 Begin
36 --bug 4558093
37 IF l_hdr_count > G_PRICE_FLAG.ALL_LINES_Y.COUNT THEN
38 l_count := G_PRICE_FLAG.ALL_LINES_Y.COUNT;
39 G_PRICE_FLAG.ALL_LINES_Y.extend(l_hdr_count - l_count);
40 G_PRICE_FLAG.ALL_LINES_N.extend(l_hdr_count - l_count);
41 G_PRICE_FLAG.MIXED.extend(l_hdr_count - l_count);
42 END IF;
43 If l_debug_level > 0 Then
44 oe_debug_pub.add('inside set_price_flag');
45 oe_debug_pub.add('p_header_counter : '||p_header_counter||'hdr count : '||oe_bulk_order_pvt.g_header_rec.header_id.count);
46 oe_debug_pub.add('G_PRICE_FLAG count : '||G_PRICE_FLAG.ALL_LINES_Y.count);
47 end if;
48
49 If G_PRICE_FLAG.ALL_LINES_Y(p_header_counter) is null or G_PRICE_FLAG.ALL_LINES_N(p_header_counter) is null or G_PRICE_FLAG.MIXED(p_header_counter) is null Then
50 If G_PRICE_FLAG.ALL_LINES_Y(p_header_counter) is null and G_PRICE_FLAG.ALL_LINES_N(p_header_counter) is null and G_PRICE_FLAG.MIXED(p_header_counter) is null then
51 IF p_line_rec.calculate_price_flag(p_index) = 'Y' Then
52 G_PRICE_FLAG.ALL_LINES_Y(p_header_counter) := 'Y';
53 G_PRICE_FLAG.ALL_LINES_N(p_header_counter) := 'N';
54 else
55 G_PRICE_FLAG.ALL_LINES_Y(p_header_counter) := 'N';
56 G_PRICE_FLAG.ALL_LINES_N(p_header_counter) := 'Y';
57 end if;
58 else
59 if p_line_rec.calculate_price_flag(p_index) = 'Y' Then
60 if G_PRICE_FLAG.ALL_LINES_Y(p_header_counter) = 'N' then
61 G_PRICE_FLAG.MIXED(p_header_counter) := 'Y';
62 end if;
63 else
64 if G_PRICE_FLAG.ALL_LINES_N(p_header_counter) = 'N' Then
65 G_PRICE_FLAG.MIXED(p_header_counter) := 'Y';
66 end if;
67 end if;
68 end if;
69 end if;
70 end;
71
72
73
74 Procedure set_hdr_price_flag(p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE) IS
75
76 l_hdr_ctr Number := p_header_rec.header_id.count;
77 i Number;
78 l_count number;
79 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
80 Begin
81
82 If l_debug_level > 0 Then
83 oe_debug_pub.add('inside set_hdr_price_flag');
84 oe_debug_pub.add('p_header_rec.header_id.count = '||l_hdr_ctr);
85 oe_debug_pub.add('g_price_flag count all_lines_y= '|| G_PRICE_FLAG.ALL_LINES_Y.count);
86 oe_debug_pub.add('g_price_flag count mixed= '|| G_PRICE_FLAG.MIXED.count);
87 end if;
88
89 p_header_rec.calculate_price_flag.extend(l_hdr_ctr);
90
91 -- HVOPG added start
92 IF l_hdr_ctr > G_PRICE_FLAG.ALL_LINES_Y.COUNT THEN
93 l_count := G_PRICE_FLAG.ALL_LINES_Y.COUNT;
94 G_PRICE_FLAG.ALL_LINES_Y.extend(l_hdr_ctr - l_count);
95 G_PRICE_FLAG.ALL_LINES_N.extend(l_hdr_ctr - l_count);
96 G_PRICE_FLAG.MIXED.extend(l_hdr_ctr - l_count);
97 END IF;
98 -- HVOP added end
99
100 for i in 1..l_hdr_ctr LOOP
101 IF nvl(G_PRICE_FLAG.MIXED(i),'N') = 'Y' Then
102 p_header_rec.calculate_price_flag(i) := 'P';
103 elsif nvl(G_PRICE_FLAG.ALL_LINES_Y(i), 'N') = 'Y' Then
104 p_header_rec.calculate_price_flag(i) := 'Y';
105 else
106 p_header_rec.calculate_price_flag(i) := 'N';
107 end if;
108
109 --Setting the flags back to null
110 G_PRICE_FLAG.ALL_LINES_Y(i) := NULL;
111 G_PRICE_FLAG.ALL_LINES_N(i) := NULL;
112 G_PRICE_FLAG.MIXED(i) := NULL;
113 end loop;
114 end;
115
116
117 /****************************************************************************************************
118 Procedure Unbook_included_item
119
120 *****************************************************************************************************/
121 Procedure Unbook_Included_Item(p_start_index In Number,p_count In Number) As
122 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
123 Begin
124
125 If l_debug_level > 0 Then
126 oe_debug_pub.add('Starting unbook included item lines');
127 oe_debug_pub.add('p_start_index:'||p_start_index);
128 oe_debug_pub.add('p_count:'||p_count);
129 End If;
130
131 For i in p_start_index..p_start_index + p_count - 1 Loop
132 If OE_BULK_ORDER_PVT.G_Line_Rec.Booked_Flag.exists(i) Then
133 OE_BULK_ORDER_PVT.G_Line_Rec.Booked_Flag(i):='N';
134 Else
135 If l_debug_level > 0 Then
136 oe_debug_pub.add('Record index:'||i||' does not exists');
137 End If;
138 End If;
139 End Loop;
140
141 If l_debug_level > 0 Then
142 oe_debug_pub.add('Leaving unbook included item lines');
143 End If;
144 End;
145
146 /*************************************************************************************************
147 Procedure Insert_Adj
148 This procedure transfers valid adjustments from QP temp tables to oe_price_adjustments
149 **************************************************************************************************/
150 --!!!warning, Insert_Adj look oe_order_pub.g_hdr.header_id will need to change for hvop
151 --OE_ADV_PRICE_PVT.Insert_Adj;
152 --The reason not to call adv_price_pvt.insert_adj is because this will introduce dependency on
153 --qp data model. That is header_id will need to be added to qp_preq_lines_tmp. If we reference
154 --qp_preq_lines_tmp.header_id, in Oe_adj_price_pvt.insert_adj then we will need to include odf that
155 --will have the columns. Due to this reason, I have to copy the code over although it is 99% the same
156 --code.
157
158
159 Procedure Insert_Adj(p_hvop_mode In Boolean Default False)
160 IS
161 l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
162 i Number;
163 --
164 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
165 --
166 cursor test is
167 select pricing_status_code,LINE_DETAIL_INDEX,LINE_INDEX from QP_PREQ_LINE_ATTRS_TMP;
168 BEGIN
169 IF l_debug_level > 0 THEN
170 oe_debug_pub.add( 'INSIDE OE_BULK_PRICEORDER_PVT.INSERT_ADJ' ) ;
171 END IF;
172 --bug 3544829
173 -- added the condition for manual adjustments in the where clause
174 INSERT INTO OE_PRICE_ADJUSTMENTS
175 ( PRICE_ADJUSTMENT_ID
176 , CREATION_DATE
177 , CREATED_BY
178 , LAST_UPDATE_DATE
179 , LAST_UPDATED_BY
180 , LAST_UPDATE_LOGIN
181 , PROGRAM_APPLICATION_ID
182 , PROGRAM_ID
183 , PROGRAM_UPDATE_DATE
184 , REQUEST_ID
185 , HEADER_ID
186 , DISCOUNT_ID
187 , DISCOUNT_LINE_ID
188 , AUTOMATIC_FLAG
189 , PERCENT
190 , LINE_ID
191 , CONTEXT
192 , ATTRIBUTE1
193 , ATTRIBUTE2
194 , ATTRIBUTE3
195 , ATTRIBUTE4
196 , ATTRIBUTE5
197 , ATTRIBUTE6
198 , ATTRIBUTE7
199 , ATTRIBUTE8
200 , ATTRIBUTE9
201 , ATTRIBUTE10
202 , ATTRIBUTE11
203 , ATTRIBUTE12
204 , ATTRIBUTE13
205 , ATTRIBUTE14
206 , ATTRIBUTE15
207 , ORIG_SYS_DISCOUNT_REF
208 , LIST_HEADER_ID
209 , LIST_LINE_ID
210 , LIST_LINE_TYPE_CODE
211 , MODIFIER_MECHANISM_TYPE_CODE
212 , MODIFIED_FROM
213 , MODIFIED_TO
214 , UPDATED_FLAG
215 , UPDATE_ALLOWED
216 , APPLIED_FLAG
217 , CHANGE_REASON_CODE
218 , CHANGE_REASON_TEXT
219 , operand
220 , Arithmetic_operator
221 , COST_ID
222 , TAX_CODE
223 , TAX_EXEMPT_FLAG
224 , TAX_EXEMPT_NUMBER
225 , TAX_EXEMPT_REASON_CODE
226 , PARENT_ADJUSTMENT_ID
227 , INVOICED_FLAG
228 , ESTIMATED_FLAG
229 , INC_IN_SALES_PERFORMANCE
230 , SPLIT_ACTION_CODE
231 , ADJUSTED_AMOUNT
232 , PRICING_PHASE_ID
233 , CHARGE_TYPE_CODE
234 , CHARGE_SUBTYPE_CODE
235 , list_line_no
236 , source_system_code
237 , benefit_qty
238 , benefit_uom_code
239 , print_on_invoice_flag
240 , expiration_date
241 , rebate_transaction_type_code
242 , rebate_transaction_reference
243 , rebate_payment_system_code
244 , redeemed_date
245 , redeemed_flag
246 , accrual_flag
247 , range_break_quantity
248 , accrual_conversion_rate
249 , pricing_group_sequence
250 , modifier_level_code
251 , price_break_type_code
252 , substitution_attribute
253 , proration_type_code
254 , CREDIT_OR_CHARGE_FLAG
255 , INCLUDE_ON_RETURNS_FLAG
256 , AC_CONTEXT
257 , AC_ATTRIBUTE1
258 , AC_ATTRIBUTE2
259 , AC_ATTRIBUTE3
260 , AC_ATTRIBUTE4
261 , AC_ATTRIBUTE5
262 , AC_ATTRIBUTE6
263 , AC_ATTRIBUTE7
264 , AC_ATTRIBUTE8
265 , AC_ATTRIBUTE9
266 , AC_ATTRIBUTE10
267 , AC_ATTRIBUTE11
268 , AC_ATTRIBUTE12
269 , AC_ATTRIBUTE13
270 , AC_ATTRIBUTE14
271 , AC_ATTRIBUTE15
272 , OPERAND_PER_PQTY
273 , ADJUSTED_AMOUNT_PER_PQTY
274 , LOCK_CONTROL
275 )
276 ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
277 ldets.price_adjustment_id
278 , sysdate --p_Line_Adj_rec.creation_date
279 , fnd_global.user_id --p_Line_Adj_rec.created_by
280 , sysdate --p_Line_Adj_rec.last_update_date
281 , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
282 , fnd_global.login_id --p_Line_Adj_rec.last_update_login
283 , NULL --p_Line_Adj_rec.program_application_id
284 , NULL --p_Line_Adj_rec.program_id
285 , NULL --p_Line_Adj_rec.program_update_date
286 , NULL --p_Line_Adj_rec.request_id
287 , lines.header_id
288 , NULL --p_Line_Adj_rec.discount_id
289 , NULL --p_Line_Adj_rec.discount_line_id
290 , ldets.automatic_flag
291 , NULL --p_Line_Adj_rec.percent
292 , decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id)
293 , NULL --p_Line_Adj_rec.context
294 , NULL --p_Line_Adj_rec.attribute1
295 , NULL --p_Line_Adj_rec.attribute2
296 , NULL --p_Line_Adj_rec.attribute3
297 , NULL --p_Line_Adj_rec.attribute4
298 , NULL --p_Line_Adj_rec.attribute5
299 , NULL --p_Line_Adj_rec.attribute6
300 , NULL --p_Line_Adj_rec.attribute7
301 , NULL --p_Line_Adj_rec.attribute8
302 , NULL --p_Line_Adj_rec.attribute9
303 , NULL --p_Line_Adj_rec.attribute10
304 , NULL --p_Line_Adj_rec.attribute11
305 , NULL --p_Line_Adj_rec.attribute12
306 , NULL --p_Line_Adj_rec.attribute13
307 , NULL --p_Line_Adj_rec.attribute14
308 , NULL --p_Line_Adj_rec.attribute15
309 , NULL --p_Line_Adj_rec.orig_sys_discount_ref
310 , ldets.LIST_HEADER_ID
311 , ldets.LIST_LINE_ID
312 , ldets.LIST_LINE_TYPE_CODE
313 , NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
314 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
315 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL)
316 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
317 , ldets.override_flag
318 , ldets.APPLIED_FLAG
319 , NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
320 , NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
321 , nvl(ldets.order_qty_operand, decode(ldets.operand_calculation_code,
322 '%', ldets.operand_value,
323 'LUMPSUM', ldets.operand_value,
324 ldets.operand_value*lines.priced_quantity/nvl(lines.line_quantity,1)))
325 , ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
326 , NULl --p_line_Adj_rec.COST_ID
327 , NULL --p_line_Adj_rec.TAX_CODE
328 , NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
329 , NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
330 , NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
331 , NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
332 , NULL --p_line_Adj_rec.INVOICED_FLAG
333 , NULL --p_line_Adj_rec.ESTIMATED_FLAG
334 , NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
335 , NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
336 , nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))
337 , ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
338 , ldets.CHARGE_TYPE_CODE
339 , ldets.CHARGE_SUBTYPE_CODE
340 , ldets.list_line_no
341 , qh.source_system_code
342 , ldets.benefit_qty
343 , ldets.benefit_uom_code
344 , NULL --p_Line_Adj_rec.print_on_invoice_flag
345 , ldets.expiration_date
346 , ldets.rebate_transaction_type_code
347 , NULL --p_Line_Adj_rec.rebate_transaction_reference
348 , NULL --p_Line_Adj_rec.rebate_payment_system_code
349 , NULL --p_Line_Adj_rec.redeemed_date
350 , NULL --p_Line_Adj_rec.redeemed_flag
351 , ldets.accrual_flag
352 , ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
353 , ldets.accrual_conversion_rate
354 , ldets.pricing_group_sequence
355 , ldets.modifier_level_code
356 , ldets.price_break_type_code
357 , ldets.substitution_attribute
358 , ldets.proration_type_code
359 , NULL --p_Line_Adj_rec.credit_or_charge_flag
360 , ldets.include_on_returns_flag
361 , NULL -- p_Line_Adj_rec.ac_context
362 , NULL -- p_Line_Adj_rec.ac_attribute1
363 , NULL -- p_Line_Adj_rec.ac_attribute2
364 , NULL -- p_Line_Adj_rec.ac_attribute3
365 , NULL -- p_Line_Adj_rec.ac_attribute4
366 , NULL -- p_Line_Adj_rec.ac_attribute5
367 , NULL -- p_Line_Adj_rec.ac_attribute6
368 , NULL -- p_Line_Adj_rec.ac_attribute7
369 , NULL -- p_Line_Adj_rec.ac_attribute8
370 , NULL -- p_Line_Adj_rec.ac_attribute9
371 , NULL -- p_Line_Adj_rec.ac_attribute10
372 , NULL -- p_Line_Adj_rec.ac_attribute11
373 , NULL -- p_Line_Adj_rec.ac_attribute12
374 , NULL -- p_Line_Adj_rec.ac_attribute13
375 , NULL -- p_Line_Adj_rec.ac_attribute14
376 , NULL -- p_Line_Adj_rec.ac_attribute15
377 , ldets.OPERAND_value
378 , ldets.adjustment_amount
379 , 1
380 FROM
381 QP_LDETS_v ldets
382 , QP_PREQ_LINES_TMP lines
383 , QP_LIST_HEADERS_B QH
384 WHERE
385 ldets.list_header_id=qh.list_header_id
386 AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
387 AND lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
388 AND lines.process_status <> 'NOT_VALID'
389 AND ldets.line_index=lines.line_index
390 AND ((nvl(ldets.automatic_flag,'N') = 'Y')
391 OR (ldets.automatic_flag = 'N' AND ldets.applied_flag = 'Y' AND ldets.updated_flag = 'Y'))
392 AND ldets.created_from_list_type_code not in ('PRL','AGR')
393 AND ldets.list_line_type_code<>'PLL'
394 AND ldets.list_line_type_code<>'IUE'
395 );
396
397 IF l_debug_level > 0 THEN
398 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
399 END IF;
400
401 --Insert associations
402
403 INSERT INTO OE_PRICE_ADJ_ASSOCS
404 ( PRICE_ADJUSTMENT_ID
405 ,CREATION_DATE
406 ,CREATED_BY
407 ,LAST_UPDATE_DATE
408 ,LAST_UPDATED_BY
409 ,LAST_UPDATE_LOGIN
410 ,PROGRAM_APPLICATION_ID
411 ,PROGRAM_ID
412 ,PROGRAM_UPDATE_DATE
413 ,REQUEST_ID
414 ,PRICE_ADJ_ASSOC_ID
415 ,LINE_ID
416 ,RLTD_PRICE_ADJ_ID
417 ,LOCK_CONTROL
418 )
419 (SELECT /*+ ORDERED USE_NL(QPL ADJ RADJ) */
420 LDET.price_adjustment_id
421 ,sysdate --p_Line_Adj_Assoc_Rec.creation_date
422 ,fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
423 ,sysdate --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
424 ,fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
425 ,fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
426 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
427 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
428 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
429 ,NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
430 ,OE_PRICE_ADJ_ASSOCS_S.nextval
431 ,NULL
432 ,RLDET.PRICE_ADJUSTMENT_ID
433 ,1
434 FROM
435 QP_PREQ_RLTD_LINES_TMP RLTD,
436 QP_PREQ_LDETS_TMP LDET,
437 QP_PREQ_LDETS_TMP RLDET
438 WHERE
439 LDET.LINE_DETAIL_INDEX = RLTD.LINE_DETAIL_INDEX AND
440 RLDET.LINE_DETAIL_INDEX = RLTD.RELATED_LINE_DETAIL_INDEX AND
441 LDET.PRICING_STATUS_CODE = 'N' AND
442 LDET.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) AND
443 nvl(LDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
444 lDET.CREATED_FROM_LIST_TYPE_CODE NOT IN ('PRL','AGR') AND
445 lDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
446 RLDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
447 RLDET.PRICING_STATUS_CODE = 'N' AND
448 RLDET.PROCESS_CODE = 'N' AND
449 nvl(RLDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
450 -- not in might not be needed
451 RLDET.PRICE_ADJUSTMENT_ID
452 NOT IN (SELECT RLTD_PRICE_ADJ_ID
453 FROM OE_PRICE_ADJ_ASSOCS
454 WHERE PRICE_ADJUSTMENT_ID = LDET.PRICE_ADJUSTMENT_ID ) AND
455 RLTD.PRICING_STATUS_CODE = 'N');
456
457 --Insert pricing attributes
458 If l_debug_level > 0 Then
459 oe_debug_pub.add('after inserting assocs');
460 oe_debug_pub.add('INSERTED '||SQL%ROWCOUNT||' ASSOCIATIONS');
461 for i in test loop
462 oe_debug_pub.add('pricing_status_code = '||i.pricing_status_code);
463 oe_debug_pub.add('LINE_DETAIL_INDEX = '||i.LINE_DETAIL_INDEX);
464 oe_debug_pub.add('LINE_INDEX = '||i.LINE_INDEX);
465 end loop;
466 end if;
467 INSERT INTO OE_PRICE_ADJ_ATTRIBS
468 ( PRICE_ADJUSTMENT_ID
469 ,PRICING_CONTEXT
470 ,PRICING_ATTRIBUTE
471 ,CREATION_DATE
472 ,CREATED_BY
473 ,LAST_UPDATE_DATE
474 ,LAST_UPDATED_BY
475 ,LAST_UPDATE_LOGIN
476 ,PROGRAM_APPLICATION_ID
477 ,PROGRAM_ID
478 ,PROGRAM_UPDATE_DATE
479 ,REQUEST_ID
480 ,PRICING_ATTR_VALUE_FROM
481 ,PRICING_ATTR_VALUE_TO
482 ,COMPARISON_OPERATOR
483 ,FLEX_TITLE
484 ,PRICE_ADJ_ATTRIB_ID
485 ,LOCK_CONTROL
486 )
487 (SELECT LDETS.PRICE_ADJUSTMENT_ID
488 ,QPLAT.CONTEXT
489 ,QPLAT.ATTRIBUTE
490 ,sysdate
491 ,fnd_global.user_id
492 ,sysdate
493 ,fnd_global.user_id
494 ,fnd_global.login_id
495 ,NULL
496 ,NULL
497 ,NULL
498 ,NULL
499 ,QPLAT.SETUP_VALUE_FROM --VALUE_FROM
500 ,QPLAT.SETUP_VALUE_TO --VALUE_TO
501 ,QPLAT.COMPARISON_OPERATOR_TYPE_CODE
502 ,decode(QPLAT.ATTRIBUTE_TYPE,
503 'QUALIFIER','QP_ATTR_DEFNS_QUALIFIER',
504 'QP_ATTR_DEFNS_PRICING')
505 ,OE_PRICE_ADJ_ATTRIBS_S.nextval
506 ,1
507 FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
508 , QP_LDETS_v LDETS
509 WHERE QPLAT.pricing_status_code=QP_PREQ_PUB.G_STATUS_NEW
510 AND QPLAT.LINE_DETAIL_INDEX = LDETS.LINE_DETAIL_INDEX
511 AND QPLAT.LINE_INDEX = LDETS.LINE_INDEX
512 AND LDETS.PROCESS_CODE=QP_PREQ_PUB.G_STATUS_NEW
513 AND LDETS.AUTOMATIC_FLAG = 'Y'
514 AND LDETS.CREATED_FROM_LIST_TYPE_CODE NOT IN ('PRL','AGR')
515 );
516
517
518 IF l_debug_level > 0 THEN
519 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' PRICE ADJ ATTRIBS' , 3 ) ;
520 END IF;
521
522 Exception
523 WHEN OTHERS THEN
524 IF l_debug_level > 0 THEN
525 oe_debug_pub.add('ERROR in inserting adjustments and associations'||sqlerrm);
526 END IF;
527 Raise FND_API.G_EXC_ERROR;
528 END Insert_Adj;
529
530
531
532
533
534 /**************************************************************************************************
535 Procedure Update_Global_Line
536 This procedure updates global line table based on lastest price info from qp table
537 **************************************************************************************************/
538
539 Procedure Update_Global_Line As
540 Cursor valid_lines Is
541 select
542 lines.order_uom_selling_price UNIT_SELLING_PRICE
543 , lines.line_unit_price UNIT_LIST_PRICE
544 , lines.ADJUSTED_UNIT_PRICE UNIT_SELLING_PRICE_PER_PQTY
545 , lines.UNIT_PRICE UNIT_LIST_PRICE_PER_PQTY
546 , lines.priced_quantity PRICING_QUANTITY
547 , lines.priced_uom_code PRICING_QUANTITY_UOM
548 , lines.price_list_header_id PRICE_LIST_ID
549 , lines.price_request_code PRICE_REQUEST_CODE
550 , nvl(lines.percent_price, NULL) UNIT_LIST_PERCENT
551 , nvl(lines.parent_price, NULL) UNIT_PERCENT_BASE_PRICE
552 , decode(lines.parent_price, NULL, 0, 0, 0,
553 lines.adjusted_unit_price/lines.parent_price)
554 UNIT_SELLING_PERCENT
555 , lines.line_index line_index
556 from qp_preq_lines_tmp lines
557 where lines.line_type_code='LINE'
558 and lines.pricing_status_code in (QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
559 and lines.process_status <> 'NOT_VALID';
560
561 l_ordered_quantity Number;
562 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
563
564 Begin
565 If l_debug_level > 0 Then
566 oe_debug_pub.add('inside update_global_line');
567 end if;
568 For valid in valid_lines Loop
569
570 l_ordered_quantity:=OE_BULK_ORDER_PVT.G_Line_Rec.Ordered_Quantity(valid.line_index);
571
572 OE_BULK_ORDER_PVT.G_Line_Rec.Unit_Selling_Price(valid.line_index):=
573 nvl(valid.unit_selling_price, valid.unit_selling_price_per_pqty*nvl(valid.pricing_quantity,l_ordered_quantity)/l_ordered_quantity);
574
575 OE_BULK_ORDER_PVT.G_Line_Rec.Unit_List_Price(valid.line_index):=
576 nvl(valid.UNIT_LIST_PRICE, valid.unit_list_price_per_pqty*nvl(valid.pricing_quantity,l_ordered_quantity)/l_ordered_quantity);
577
578 OE_BULK_ORDER_PVT.G_Line_Rec.Unit_Selling_Price_Per_Pqty(valid.line_index):= valid.Unit_Selling_Price_Per_PQTY;
579
580 OE_BULK_ORDER_PVT.G_Line_Rec.Unit_List_Price_Per_Pqty(valid.line_index):=valid.Unit_List_Price_Per_Pqty;
581
582 If valid.pricing_quantity <> -99999 Then
583 OE_BULK_ORDER_PVT.G_Line_Rec.Pricing_Quantity(valid.line_index):=valid.pricing_quantity;
584 OE_BULK_ORDER_PVT.G_Line_Rec.Pricing_QUantity_Uom(Valid.line_index):=valid.pricing_quantity_uom;
585 Else ---99999 no conversion, set pricing and order uom to same
586 OE_BULK_ORDER_PVT.G_Line_Rec.Pricing_Quantity(valid.line_index):=l_ordered_quantity;
587 OE_BULK_ORDER_PVT.G_Line_Rec.Pricing_QUantity_Uom(Valid.line_index):=OE_BULK_ORDER_PVT.G_Line_Rec.Order_Quantity_Uom(Valid.line_index);
588 End If;
589
590 If valid.price_list_id <> -9999 Then
591 OE_BULK_ORDER_PVT.G_Line_Rec.Price_List_Id(Valid.line_index):=Valid.Price_List_Id;
592 Else
593 OE_BULK_ORDER_PVT.G_Line_Rec.Price_List_Id(Valid.line_index):=NULL;
594 End If;
595
596 -- OE_BULK_ORDER_PVT.G_Line_Rec.Price_Request_Code(Valid.line_index):=Valid.Price_Request_Code;
597 OE_BULK_ORDER_PVT.G_Line_Rec.Unit_List_Percent(Valid.line_index):=Valid.Unit_List_Percent;
598 OE_BULK_ORDER_PVT.G_Line_Rec.Unit_Percent_Base_Price(Valid.line_index):=Valid.Unit_Percent_Base_Price;
599 OE_BULK_ORDER_PVT.G_Line_Rec.Unit_Selling_Percent(Valid.line_index):=Valid.Unit_Selling_Percent;
600
601 End Loop;
602 End;
603
604 PROCEDURE Booking_Failed(p_index IN NUMBER,
605 p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE)
606 IS
607 l_start_index BINARY_INTEGER;
608 BEGIN
609 -- Update DB values
610 UPDATE OE_ORDER_LINES
611 SET booked_flag = 'N'
612 ,flow_status_code = 'ENTERED'
613 WHERE header_id = p_header_rec.header_id(p_index);
614
615 UPDATE OE_ORDER_HEADERS
616 SET booked_flag = 'N'
617 ,booked_date = NULL
618 ,flow_status_code = 'ENTERED'
619 WHERE header_id = p_header_rec.header_id(p_index);
620
621 -- Also, delete from DBI tables if booking fails
622 IF OE_BULK_ORDER_PVT.G_DBI_INSTALLED = 'Y' THEN
623 DELETE FROM ONT_DBI_CHANGE_LOG
624 WHERE header_id = p_header_rec.header_id(p_index);
625 END IF;
626
627 -- Un-set booking fields on global records
628 p_header_rec.booked_flag(p_index) := 'N';
629 l_start_index := 1;
630
631 /*FOR l_index IN l_start_index..OE_Bulk_Order_PVT.G_LINE_REC.HEADER_ID.COUNT LOOP
632 IF OE_Bulk_Order_PVT.G_LINE_REC.header_id(l_index) = p_header_rec.header_id(p_index)
633 THEN
634 OE_Bulk_Order_PVT.G_LINE_REC.booked_flag(l_index) := 'N';
635 ELSIF OE_Bulk_Order_PVT.G_LINE_REC.header_id(l_index) >
636 p_header_rec.header_id(p_index)
637 THEN
638 l_start_index := l_index;
639 EXIT;
640 END IF;
641 END LOOP;*/
642
643 END Booking_Failed;
644
645
646 /**************************************************************************************************
647 PROCEDURE Credit_Check
648 1. OE_BULK_HEADER_UTIL.Insert_Headers will always insert booked_flag = 'N' for the header.
649 2. The g_header_rec memory always contains the correct booked_flag.
650 3. Before process acknowledgment, we call credit_check
651 4. Credit_Check will one by one loop through the G_HEADER_REC updates the db header book_flag as 'BOOKED' and then perform the credit check for each order
652 ****************************************************************************************************/
653 PROCEDURE Credit_Check (p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE) As
654 l_msg_count Number;
655 l_msg_data Varchar2(2000);
656 l_return_status Varchar2(30);
657 l_header_id number;
658 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
659
660 Begin
661 l_return_status:= FND_API.G_RET_STS_SUCCESS;
662
663 If l_debug_level > 0 Then
664 oe_debug_pub.add('Entering OE_BULK_PRICEORDER_PVT.credit_check');
665 End If;
666
667 For i IN 1..p_header_rec.header_id.count Loop
668
669 l_header_id := p_header_rec.header_id(i);
670 If p_header_rec.booked_flag(i) = 'Y' Then
671 Begin
672
673 -- Update the booked flag only if real Time CC is required
674 -- else the booked_flag is already set on the record
675 IF OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED = 'Y' THEN
676 update oe_order_headers_all set booked_flag = 'Y'
677 where header_id = p_header_rec.header_id(i);
678 END IF;
679
680 IF OE_BULK_CACHE.IS_CC_REQUIRED(p_header_rec.order_type_id(i))
681 THEN
682 If l_debug_level > 0 Then
683 oe_debug_pub.add(' Calling OE_Verify_Payment_PUB.Verify_Payment');
684 End If;
685
686 OE_Verify_Payment_PUB.Verify_Payment
687 ( p_header_id => l_header_id
688 , p_calling_action => 'UPDATE'
689 , p_delayed_request=> FND_API.G_TRUE
690 , p_msg_count => l_msg_count
691 , p_msg_data => l_msg_data
692 , p_return_status => l_return_status);
693
694 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
695 oe_debug_pub.add('Verify payment returns status errors:'||l_msg_data);
696 l_return_status := FND_API.G_RET_STS_SUCCESS;
697 Booking_Failed(i,p_header_rec);
698 End If;
699 End IF;
700
701 Exception
702 When no_data_found Then
703 oe_debug_pub.add('Header Id:'|| p_header_rec.header_id(i) || 'not exists in DB');
704 When others Then
705 oe_debug_pub.add('Errors occured when restoring the book flag:'||SQLERRM);
706 End;
707
708 End If;
709 End Loop;
710
711 If l_debug_level > 0 Then
712 oe_debug_pub.add('Leaving OE_BULK_PRICEORDER_PVT.credit_checking');
713 End If;
714 End;
715
716
717 Procedure set_calc_flag_incl_item(p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
718 , p_index Number) Is
719
720 l_calculate_flag varchar2(1);
721 Begin
722
723 IF p_line_rec.calculate_price_flag(p_index) in ( 'Y', 'P' )
724 Then
725 If ( G_CHARGES_FOR_INCLUDED_ITEM = 'N')
726 Then
727 l_calculate_flag := 'N';
728 Else
729 l_calculate_flag := 'P';
730 End If;
731 Else
732 l_calculate_flag := 'N';
733 End IF;
734 p_line_rec.calculate_price_flag(p_index) := l_calculate_flag;
735 End;
736
737
738
739 /****************************************************************************************************
740 PROCEDURE Unbook_Order
741 This procedure will mark (in memory) order and all the lines including included items underneath the lines as UNBOOKED. If the order_header is already unbook, it will return immediately. Otherwise
742 It will iterate up and down from the current line_index position and mark the line as unbooked until
743 the header_index changes. While iterating, if it is a 'KIT' line then we will need to mark included item lines as unbook also. To do that, we should start from global_line_rec.ii_start_index and iterate until ii_start_index + ii_count - 1
744 ******************************************************************************************************/
745 Procedure Unbook_Order(p_header_index IN Number,
746 p_line_index IN Number) As
747 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
748 l_index number;
749 Begin
750 If l_debug_level > 0 Then
751 oe_debug_pub.add('inside Unbook_Order');
752 oe_debug_pub.add('p_header_index = '||p_header_index);
753 oe_debug_pub.add('p_line_index = '||p_line_index);
754 End if;
755
756 If p_header_index Is Null Then
757 If l_debug_level > 0 Then
758 oe_debug_pub.add('Header index is null, unable to proceed. Returning');
759 end if;
760 Return;
761 End If;
762
763 If l_debug_level > 0 Then
764 oe_debug_pub.add('before checking Booked_Flag');
765 oe_debug_pub.add('Booked_Flag = '||OE_Bulk_Order_Pvt.G_Header_Rec.Booked_Flag(p_header_index));
766 oe_debug_pub.add('after printing Booked_Flag');
767 end if;
768 If OE_Bulk_Order_Pvt.G_Header_Rec.Booked_Flag(p_header_index) = 'N' Then
769 If l_debug_level > 0 Then
770 Oe_Debug_Pub.add('Order has been unbooked, no further unbook action is needed');
771 End If;
772 RETURN;
773 End If;
774
775 If OE_Bulk_Order_Pvt.G_Header_Rec.Booked_Flag(p_header_index) = 'Y' Then
776 OE_Bulk_Order_Pvt.G_Header_Rec.Booked_Flag(p_header_index):='N';
777 If p_line_index Is Not NULL Then
778 --First unbook the line, then move the pointer up and unbook the line until header_index changes.
779 --Then move the pointer down from the p_line_index and unblook the line until header_index changes
780 OE_BULK_ORDER_PVT.G_Line_Rec.Booked_Flag(p_line_index):='N';
781
782 l_index := p_line_index;
783
784 While l_index > 0 Loop
785
786 If l_debug_level > 0 Then
787 oe_debug_pub.add('in the while loop');
788 end if;
789 OE_BULK_ORDER_PVT.G_Line_Rec.Booked_Flag(l_index) := 'N';
790
791 If OE_BULK_ORDER_PVT.G_Line_Rec.item_type_code(l_index) = 'KIT' Then
792 Unbook_Included_Item(p_start_index=> OE_BULK_ORDER_PVT.G_Line_Rec.ii_start_index(l_index),
793 p_count => OE_BULK_ORDER_PVT.G_Line_Rec.ii_count(l_index));
794 End If;
795 If l_debug_level > 0 Then
796 oe_debug_pub.add('after checking item_type_code');
797 end if;
798 If l_index <> 1 Then
799 If OE_BULK_ORDER_PVT.G_Line_Rec.Header_Index(l_index) <>
800 OE_BULK_ORDER_PVT.G_Line_Rec.Header_Index(l_index-1)
801 Then
802 Exit;
803 End If;
804 End If;
805
806 l_index := l_index - 1;
807 End Loop;
808
809 l_index := p_line_index;
810
811 While l_index <= OE_BULK_ORDER_PVT.G_Line_Rec.Line_Id.Count Loop
812
813 OE_BULK_ORDER_PVT.G_Line_Rec.Booked_Flag(l_index) := 'N';
814
815 If OE_BULK_ORDER_PVT.G_Line_Rec.item_type_code(l_index) = 'KIT' Then
816 Unbook_Included_Item(p_start_index=> OE_BULK_ORDER_PVT.G_Line_Rec.ii_start_index(l_index),
817 p_count => OE_BULK_ORDER_PVT.G_Line_Rec.ii_count(l_index));
818 End If;
819
820
821 IF l_index <> OE_BULK_ORDER_PVT.G_Line_Rec.Line_Id.Count Then
822 If OE_BULK_ORDER_PVT.G_Line_Rec.Header_Index(l_index) <>
823 OE_BULK_ORDER_PVT.G_Line_Rec.Header_Index(l_index+1) Then
824 Exit;
825 End If;
826 End If;
827
828 l_index := l_index + 1;
829 End Loop;
830 Else
831 If l_debug_level > 0 Then
832 oe_debug_pub.add('Line index is null, unable to unbook line');
833 End If;
834 End If;
835
836 End If;
837
838 End;
839
840
841 --This procedure need to insert adj from interface table and put it into
842 --QP_TEMP_TABLE. Now qp is providing an api for that
843 Procedure Insert_Adjs_From_Iface
844 (p_batch_id IN NUMBER,
845 x_return_status OUT NOCOPY VARCHAR2) AS
846 BEGIN
847 null;
848 END;
849
850
851
852 Function Get_List_Lines (p_line_id Number) Return Varchar2 As
853 Cursor list_lines_no is
854 Select c.name,
855 a.list_line_no
856 From qp_preq_ldets_tmp a,
857 qp_preq_lines_tmp b,
858 qp_list_headers_vl c
859 Where b.line_id = p_line_id
860 And b.line_index = a.line_index
861 And a.created_from_list_header_id = c.list_header_id
862 And a.automatic_flag = 'Y'
863 And a.pricing_status_code = 'N'
864 And b.process_status <> 'NOT_VALID'
865 And a.created_from_list_line_type <> 'PLL';
866
867 l_list_line_nos Varchar2(2000):=' ';
868 l_separator Varchar2(1):='';
869 --
870 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
871 --
872 Begin
873 For i in List_Lines_no Loop
874 l_list_line_nos := i.name||':'||i.list_line_no||l_separator||l_list_line_nos;
875 l_separator := ',';
876 End Loop;
877 Return l_list_line_nos;
878 End Get_List_Lines;
879
880
881
882
883 /*********************************************************************
884 Procedure: Check_Errors.
885 Purpose : Check for errors return by pricing. Post it messaging table.
886 Hold the line if it violates GSA rule. Unbooked all the lines, and included items
887 underneath the KIT if errors.
888 OUTPUT:
889 ***********************************************************************/
890
891 Procedure Check_Errors As
892 l_allow_negative_price Varchar2(30) := nvl(fnd_profile.value('ONT_NEGATIVE_PRICING'),'N');
893 l_has_errors Boolean := False;
894 --l_GSA_Enabled_Flag Varchar2(30) := FND_PROFILE.VALUE('QP_VERIFY_GSA');
895 --l_gsa_violation_action Varchar2(30) := nvl(fnd_profile.value('ONT_GSA_VIOLATION_ACTION'), 'WARNING');
896 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
897 l_invalid_line varchar2(1);
898 l_request_id number;
899 l_price_list Varchar2(240);
900 l_msg_text Varchar2(200);
901
902 cursor wrong_lines is
903 select qp.line_id
904 , qp.line_index
905 , qp.line_type_code
906 , qp.processed_code
907 , qp.pricing_status_code
908 , qp.PRICING_STATUS_TEXT STATUS_TEXT
909 , qp.unit_price
910 , qp.adjusted_unit_price
911 , qp.priced_quantity
912 , qp.line_quantity
913 , qp.priced_uom_code
914 from qp_preq_lines_tmp qp
915 where process_status <> 'NOT_VALID' and
916 (pricing_status_code not in
917 (QP_PREQ_GRP.G_STATUS_UNCHANGED,
918 QP_PREQ_GRP.G_STATUS_UPDATED,
919 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION, --uncommented for bug 3716296
920 'NOT_VALID')
921 OR (l_allow_negative_price = 'N' AND (unit_price<0 OR adjusted_unit_price<0)));
922 -- and l.line_id = qp.line_id;
923
924 l_header_id NUMBER;
925 l_order_source_id Number;
926 l_orig_sys_document_ref Varchar2(50);
927 l_orig_sys_line_ref Varchar2(50);
928 l_orig_sys_shipment_ref Varchar2(50);
929 l_change_sequence Varchar2(50);
930 l_source_document_type_id Number;
931 l_source_document_id Number;
932 l_source_document_line_id Number;
933 l_booked_flag Varchar2(1);
934 l_item_type_code Varchar2(30);
935 l_line_category_code Varchar2(30);
936 l_calculate_price_flag Varchar2(1);
937 l_top_model_line_id Number;
938 l_ordered_item Varchar2(2000);
939 l_order_quantity_uom Varchar2(3);
940 l_price_list_id Number;
941 l_inventory_item_id Number;
942 l_line_count Number := OE_BULK_ORDER_PVT.G_LINE_REC.line_id.count;
943
944 Begin
945
946 If l_debug_level > 0 Then
947 oe_debug_pub.add('inside check_errors');
948 end if;
949 OE_BULK_ORDER_PVT.G_Line_Rec.source_document_type_id.extend(l_line_count);
950 OE_BULK_ORDER_PVT.G_Line_Rec.source_document_line_id.extend(l_line_count);
951
952 For wrong_line in wrong_lines loop
953 if l_debug_level > 0 Then
954 oe_debug_pub.add('inside wrong_line loop');
955 oe_debug_pub.add('line_index = '||wrong_line.line_index);
956 end if;
957
958 l_header_id := OE_BULK_ORDER_PVT.G_Line_Rec.header_id(wrong_line.line_index);
959 l_order_source_id := OE_BULK_ORDER_PVT.G_Line_Rec.order_source_id(wrong_line.line_index);
960 l_orig_sys_document_ref := OE_BULK_ORDER_PVT.G_Line_Rec.orig_sys_document_ref(wrong_line.line_index);
961 l_orig_sys_line_ref := OE_BULK_ORDER_PVT.G_Line_Rec.orig_sys_line_ref(wrong_line.line_index);
962 l_orig_sys_shipment_ref := OE_BULK_ORDER_PVT.G_Line_Rec.orig_sys_shipment_ref(wrong_line.line_index);
963 l_change_sequence := OE_BULK_ORDER_PVT.G_Line_Rec.change_sequence(wrong_line.line_index);
964 l_source_document_type_id := OE_BULK_ORDER_PVT.G_Line_Rec.source_document_type_id(wrong_line.line_index);
965 l_source_document_id := OE_BULK_ORDER_PVT.G_Line_Rec.source_document_id(wrong_line.line_index);
966 l_source_document_line_id := OE_BULK_ORDER_PVT.G_Line_Rec.source_document_line_id(wrong_line.line_index);
967 l_booked_flag := OE_BULK_ORDER_PVT.G_Line_Rec.booked_flag(wrong_line.line_index);
968 l_item_type_code := OE_BULK_ORDER_PVT.G_Line_Rec.item_type_code(wrong_line.line_index);
969 l_line_category_code := OE_BULK_ORDER_PVT.G_Line_Rec.line_category_code(wrong_line.line_index);
970 l_calculate_price_flag := OE_BULK_ORDER_PVT.G_Line_Rec.calculate_price_flag(wrong_line.line_index);
971 l_top_model_line_id := OE_BULK_ORDER_PVT.G_Line_Rec.top_model_line_id(wrong_line.line_index);
972 l_order_quantity_uom := OE_BULK_ORDER_PVT.G_Line_Rec.order_quantity_uom(wrong_line.line_index);
973 l_price_list_id := OE_BULK_ORDER_PVT.G_Line_Rec.price_list_id(wrong_line.line_index);
974 l_inventory_item_id := OE_BULK_ORDER_PVT.G_Line_Rec.inventory_item_id(wrong_line.line_index);
975
976
977 If l_debug_level > 0 Then oe_debug_pub.add('before set_msg_context'); end if;
978
979 OE_BULK_MSG_PUB.set_msg_context
980 ( p_entity_code => 'LINE'
981 ,p_entity_id => wrong_line.line_id
982 ,p_header_id => l_header_id
983 ,p_line_id => wrong_line.line_id
984 ,p_order_source_id => l_order_source_id
985 ,p_orig_sys_document_ref => l_orig_sys_document_ref
986 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
987 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
988 ,p_change_sequence => l_change_sequence
989 ,p_source_document_type_id => l_source_document_type_id
990 ,p_source_document_id => l_source_document_id
991 ,p_source_document_line_id => l_source_document_line_id
992 );
993
994 IF l_debug_level > 0 THEN
995 oe_debug_pub.add( 'THE STATUS'||WRONG_LINE.PRICING_STATUS_CODE||':'||WRONG_LINE.PROCESSED_CODE||':'||WRONG_LINE.STATUS_TEXT ) ;
996 END IF;
997
998 l_invalid_line := 'N';
999 -- add message when the price list is found to be inactive
1000 IF wrong_line.line_Type_code ='LINE' and
1001 wrong_line.processed_code = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND
1002 Then
1003
1004 IF l_debug_level > 0 THEN oe_debug_pub.add( 'PRICE LIST NOT FOUND1' ) ; END IF;
1005
1006 FND_MESSAGE.SET_NAME('ONT','ONT_NO_PRICE_LIST_FOUND');
1007 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_Ordered_Item,l_inventory_item_id));
1008 FND_MESSAGE.SET_TOKEN('UOM',l_Order_Quantity_uom);
1009 OE_BULK_MSG_PUB.Add;
1010 l_invalid_line := 'Y';
1011 END IF;
1012
1013 If wrong_line.line_Type_code ='LINE' and
1014 wrong_line.pricing_status_code in ( QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST,
1015 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND,
1016 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR,
1017 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS,
1018 FND_API.G_RET_STS_UNEXP_ERROR,
1019 FND_API.G_RET_STS_ERROR,
1020 QP_PREQ_GRP.G_STATUS_CALC_ERROR,
1021 QP_PREQ_GRP.G_STATUS_UOM_FAILURE,
1022 QP_PREQ_GRP.G_STATUS_INVALID_UOM,
1023 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST,
1024 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV,
1025 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP,
1026 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR)
1027 --bug 3716296QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
1028 Then
1029
1030 l_invalid_line := 'Y';
1031 Begin
1032 Select name into l_price_list
1033 from qp_list_headers_vl where
1034 list_header_id = l_price_list_id;
1035 Exception When No_data_found then
1036 l_price_list := l_price_list_id;
1037 End;
1038
1039 If wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST then
1040 IF l_debug_level > 0 THEN
1041 oe_debug_pub.add( 'INVALID ITEM/PRICE LIST COMBINATION'||l_ORDERED_ITEM||l_ORDER_QUANTITY_UOM||L_PRICE_LIST ) ;
1042 null;
1043 END IF;
1044
1045 FND_MESSAGE.SET_NAME('ONT','OE_PRC_NO_LIST_PRICE');
1046 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_Ordered_Item,l_inventory_item_id));
1047 FND_MESSAGE.SET_TOKEN('UNIT',l_Order_Quantity_uom);
1048 FND_MESSAGE.SET_TOKEN('PRICE_LIST',l_Price_List);
1049
1050 OE_BULK_MSG_PUB.ADD;
1051
1052
1053 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STS_LHS_NOT_FOUND Then
1054 IF l_debug_level > 0 THEN
1055 oe_debug_pub.add( 'PRICE LIST NOT FOUND' ) ;
1056 END IF;
1057 FND_MESSAGE.SET_NAME('ONT','ONT_NO_PRICE_LIST_FOUND');
1058 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_Ordered_Item,l_inventory_item_id));
1059 FND_MESSAGE.SET_TOKEN('UOM',l_Order_Quantity_uom);
1060 OE_BULK_MSG_PUB.Add;
1061
1062 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_FORMULA_ERROR then
1063 IF l_debug_level > 0 THEN
1064 oe_debug_pub.add( 'ERROR IN FORMULA PROCESSING' ) ;
1065 END IF;
1066 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_ERROR_IN_FORMULA');
1067 FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
1068 OE_BULK_MSG_PUB.Add;
1069
1070 Elsif wrong_line.pricing_status_code in
1071 ( QP_PREQ_GRP.G_STATUS_OTHER_ERRORS , FND_API.G_RET_STS_UNEXP_ERROR,
1072 FND_API.G_RET_STS_ERROR)
1073 then
1074 IF l_debug_level > 0 THEN
1075 oe_debug_pub.add( 'OTHER ERRORS PROCESSING' ) ;
1076 END IF;
1077 FND_MESSAGE.SET_NAME('ONT','ONT_PRICING_ERRORS'); --bug#7149497
1078 FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
1079 OE_BULK_MSG_PUB.Add;
1080
1081 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_UOM then
1082 IF l_debug_level > 0 THEN
1083 oe_debug_pub.add( 'INVALID UOM' ) ;
1084 END IF;
1085 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_UOM');
1086 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_Ordered_Item,l_inventory_item_id));
1087 FND_MESSAGE.SET_TOKEN('UOM',l_Order_Quantity_uom);
1088 OE_BULK_MSG_PUB.Add;
1089 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST then
1090 IF l_debug_level > 0 THEN
1091 oe_debug_pub.add( 'DUPLICATE PRICE LIST' ) ;
1092 END IF;
1093 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_DUPLICATE_PRICE_LIST');
1094
1095 Begin
1096 Select name into l_price_list
1097 from qp_list_headers_vl a,qp_list_lines b where
1098 b.list_line_id = to_number(substr(wrong_line.status_text,1,
1099 instr(wrong_line.status_text,',')-1))
1100 and a.list_header_id=b.list_header_id
1101 ;
1102 Exception When No_data_found then
1103 l_price_list := to_number(substr(wrong_line.status_text,1,
1104 instr(wrong_line.status_text,',')-1));
1105 When invalid_number then
1106 l_price_list := substr(wrong_line.status_text,1,
1107 instr(wrong_line.status_text,',')-1);
1108
1109 End;
1110
1111 FND_MESSAGE.SET_TOKEN('PRICE_LIST1',
1112 '( '||l_Ordered_Item||' ) '||l_price_list);
1113
1114
1115
1116
1117 Begin
1118 Select name into l_price_list
1119 from qp_list_headers_vl a,qp_list_lines b where
1120 b.list_line_id =
1121 to_number(substr(wrong_line.status_text,
1122 instr(wrong_line.status_text,',')+1))
1123 and a.list_header_id=b.list_header_id ;
1124 Exception When No_data_found then
1125 l_price_list := to_number(substr(wrong_line.status_text,
1126 instr(wrong_line.status_text,',')+1));
1127 When invalid_number then
1128 l_price_list := substr(wrong_line.status_text,
1129 instr(wrong_line.status_text,',')+1);
1130 End;
1131
1132 FND_MESSAGE.SET_TOKEN('PRICE_LIST2',l_price_list);
1133 OE_BULK_MSG_PUB.Add;
1134
1135 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV then
1136 IF l_debug_level > 0 THEN
1137 oe_debug_pub.add( 'INVALID UOM CONVERSION' ) ;
1138 END IF;
1139 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_UOM_CONVERSION');
1140 FND_MESSAGE.SET_TOKEN('UOM_TEXT','( '||l_Ordered_Item||' ) '||
1141 wrong_line.status_text);
1142 OE_BULK_MSG_PUB.Add;
1143
1144
1145 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_INVALID_INCOMP then
1146 IF l_debug_level > 0 THEN
1147 oe_debug_pub.add( 'UNABLE TO RESOLVE INCOMPATIBILITY' ) ;
1148 END IF;
1149 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_INVALID_INCOMP');
1150 FND_MESSAGE.SET_TOKEN('ERR_TEXT','( '||
1151 l_Ordered_Item||' ) '||wrong_line.status_text);
1152
1153 OE_BULK_MSG_PUB.Add;
1154
1155 Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR then
1156 IF l_debug_level > 0 THEN
1157 oe_debug_pub.add( 'ERROR WHILE EVALUATING THE BEST PRICE' ) ;
1158 END IF;
1159 FND_MESSAGE.SET_NAME('ONT','ONT_PRC_BEST_PRICE_ERROR');
1160 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_Ordered_Item,l_inventory_item_id));
1161 FND_MESSAGE.SET_TOKEN('ERR_TEXT',wrong_line.status_text);
1162
1163 OE_BULK_MSG_PUB.Add;
1164 --bug 3716296
1165 /* Elsif wrong_line.pricing_status_code = QP_PREQ_GRP.G_STATUS_GSA_VIOLATION THEN
1166 IF (l_GSA_Enabled_Flag = 'Y') THEN
1167 IF l_gsa_violation_action = 'WARNING' THEN
1168 FND_MESSAGE.SET_NAME('ONT','OE_GSA_VIOLATION');
1169 l_msg_text := wrong_line.status_text||' ( '||nvl(l_ordered_item,l_inventory_item_id)||' )';
1170 FND_MESSAGE.SET_TOKEN('GSA_PRICE',l_msg_text);
1171 OE_BULK_MSG_PUB.Add;
1172
1173 IF l_debug_level > 0 THEN
1174 oe_debug_pub.add(' GSA warning occured on line_id:'||wrong_line.line_id);
1175 END IF;
1176 ELSE --violation action is error
1177 FND_MESSAGE.SET_NAME('ONT','OE_GSA_HOLD_APPLIED');
1178 OE_BULK_MSG_PUB.Add;
1179 -- Apply GSA Hold
1180 OE_Bulk_Holds_Pvt.Apply_GSA_Hold
1181 (p_header_id => l_header_id,
1182 p_line_id => wrong_line.line_id,
1183 p_line_number => NULL,
1184 p_hold_id => G_SEED_GSA_HOLD_ID,
1185 p_ship_set_name => NULL,
1186 p_arrival_set_name => NULL,
1187 p_activity_name => NULL,
1188 p_attribute => NULL,
1189 p_top_model_line_id => l_top_model_line_id );
1190 IF l_debug_level > 0 THEN
1191 oe_debug_pub.add(' GSA Hold applied on line_id:'||wrong_line.line_id);
1192 END IF;
1193 END IF;
1194 ELSE
1195 IF l_debug_level > 0 THEN oe_debug_pub.add(' GSA check is disabled'); END If;
1196 END IF;
1197 --bug 3716296 */
1198 END IF;
1199 End if;
1200
1201
1202
1203 --Pricing does not return error status but returns negative price.
1204 IF wrong_line.line_type_code='LINE' and
1205 (wrong_line.unit_price <0 or wrong_line.adjusted_unit_price<0)
1206 Then
1207
1208 FND_MESSAGE.SET_NAME('ONT','ONT_NEGATIVE_PRICE');
1209 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_Ordered_Item,l_inventory_item_id));
1210 FND_MESSAGE.SET_TOKEN('LIST_PRICE',wrong_line.unit_price);
1211 FND_MESSAGE.SET_TOKEN('SELLING_PRICE',wrong_line.Adjusted_unit_price);
1212 OE_BULK_MSG_PUB.Add;
1213
1214 FND_MESSAGE.SET_NAME('ONT','ONT_NEGATIVE_MODIFIERS');
1215 FND_MESSAGE.SET_TOKEN('LIST_LINE_NO',get_list_lines(wrong_line.line_id));
1216 OE_BULK_MSG_PUB.Add;
1217
1218 IF l_debug_level > 0 THEN
1219 oe_debug_pub.add( 'NEGATIVE LIST PRICE '||WRONG_LINE.UNIT_PRICE ||'OR SELLING PRICE '||WRONG_LINE.ADJUSTED_UNIT_PRICE ) ;
1220 oe_debug_pub.add( 'MODIFIERS:'||GET_LIST_LINES ( WRONG_LINE.LINE_ID ) ) ;
1221 END IF;
1222
1223 RAISE FND_API.G_EXC_ERROR;
1224 END IF;
1225
1226
1227 IF l_debug_level > 0 THEN oe_debug_pub.add('before checking l_invalid_line'); END IF;
1228
1229 If l_invalid_line = 'Y' Then
1230 --we need to unbook the order and all the lines and included under this order
1231 --first check if the header is already has been unbooked, if yes, we would
1232 --assume all the lines have already been unbooked. If 'NO', unbook the header
1233 --and all the lines including included under this order.
1234 --we can use header_index.
1235 Unbook_Order(OE_BULK_ORDER_PVT.G_Line_Rec.Header_Index(wrong_line.line_index),wrong_line.line_index);
1236
1237 If l_debug_level > 0 Then oe_debug_pub.add('after call to Unbook_Order'); END IF;
1238 Else
1239 l_invalid_line:='N';
1240 End If;
1241
1242 END loop; /* wrong_lines cursor */
1243
1244
1245
1246 End;
1247
1248
1249 --bug 3716296
1250 PROCEDURE Check_Gsa
1251 IS
1252
1253 l_GSA_Enabled_Flag Varchar2(30) := FND_PROFILE.VALUE('QP_VERIFY_GSA');
1254 --l_gsa_violation_action Varchar2(30) := nvl(fnd_profile.value('ONT_GSA_VIOLATION_ACTION'), 'WARNING');
1255 l_gsa_violation_action Varchar2(30) := nvl(oe_sys_parameters.value('ONT_GSA_VIOLATION_ACTION'), 'WARNING'); --moac
1256 l_ordered_item Varchar2(2000);
1257 l_inventory_item_id Number;
1258 l_msg_text Varchar2(200);
1259 l_top_model_line_id Number;
1260 l_header_id Number;
1261 l_order_source_id Number;
1262 l_orig_sys_document_ref Varchar2(50);
1263 l_orig_sys_line_ref Varchar2(50);
1264 l_orig_sys_shipment_ref Varchar2(50);
1265 l_change_sequence Varchar2(50);
1266 l_source_document_type_id Number;
1267 l_source_document_id Number;
1268 l_source_document_line_id Number;
1269 l_return_status Varchar2(1);
1270 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1271
1272 CURSOR gsa_violators IS
1273 SELECT line_id, PRICING_STATUS_TEXT status_text, line_index
1274 FROM QP_PREQ_LINES_TMP
1275 WHERE LINE_TYPE_CODE='LINE'
1276 AND PROCESS_STATUS <> 'NOT_VALID'
1277 AND PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_GSA_VIOLATION;
1278 BEGIN
1279 IF l_debug_level > 0 THEN
1280 oe_debug_pub.add('inside check gsa');
1281 END IF;
1282 for i in gsa_violators loop
1283 l_inventory_item_id := OE_BULK_ORDER_PVT.G_Line_Rec.inventory_item_id(i.line_index);
1284 l_header_id := OE_BULK_ORDER_PVT.G_Line_Rec.header_id(i.line_index);
1285 l_top_model_line_id := OE_BULK_ORDER_PVT.G_Line_Rec.top_model_line_id(i.line_index);
1286 If l_debug_level > 0 Then
1287 oe_debug_pub.add('before set_msg_context');
1288 end if;
1289
1290 l_order_source_id := OE_BULK_ORDER_PVT.G_Line_Rec.order_source_id(i.line_index);
1291 l_orig_sys_document_ref := OE_BULK_ORDER_PVT.G_Line_Rec.orig_sys_document_ref(i.line_index);
1292 l_orig_sys_line_ref := OE_BULK_ORDER_PVT.G_Line_Rec.orig_sys_line_ref(i.line_index);
1293 l_orig_sys_shipment_ref := OE_BULK_ORDER_PVT.G_Line_Rec.orig_sys_shipment_ref(i.line_index);
1294 l_change_sequence := OE_BULK_ORDER_PVT.G_Line_Rec.change_sequence(i.line_index);
1295 l_source_document_type_id := OE_BULK_ORDER_PVT.G_Line_Rec.source_document_type_id(i.line_index);
1296 l_source_document_id := OE_BULK_ORDER_PVT.G_Line_Rec.source_document_id(i.line_index);
1297 l_source_document_line_id := OE_BULK_ORDER_PVT.G_Line_Rec.source_document_line_id(i.line_index);
1298 OE_BULK_MSG_PUB.set_msg_context
1299 ( p_entity_code => 'LINE'
1300 ,p_entity_id => i.line_id
1301 ,p_header_id => l_header_id
1302 ,p_line_id => i.line_id
1303 ,p_order_source_id => l_order_source_id
1304 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1305 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
1306 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
1307 ,p_change_sequence => l_change_sequence
1308 ,p_source_document_type_id => l_source_document_type_id
1309 ,p_source_document_id => l_source_document_id
1310 ,p_source_document_line_id => l_source_document_line_id
1311 );
1312
1313 IF (l_GSA_Enabled_Flag = 'Y') THEN
1314 IF l_gsa_violation_action = 'WARNING' THEN
1315 FND_MESSAGE.SET_NAME('ONT','OE_GSA_VIOLATION');
1316 l_msg_text := i.status_text||' ( '||nvl(l_ordered_item,l_inventory_item_id)||' )';
1317 FND_MESSAGE.SET_TOKEN('GSA_PRICE',l_msg_text);
1318 OE_BULK_MSG_PUB.Add;
1319
1320 IF l_debug_level > 0 THEN
1321 oe_debug_pub.add(' GSA warning occured on line_id:'||i.line_id);
1322 END IF;
1323 ELSE --violation action is error
1324 /* bug 3735141
1325 FND_MESSAGE.SET_NAME('ONT','OE_GSA_HOLD_APPLIED');
1326 OE_BULK_MSG_PUB.Add;
1327 */
1328 -- Apply GSA Hold
1329 OE_Bulk_Holds_Pvt.Apply_GSA_Hold
1330 (p_header_id => l_header_id,
1331 p_line_id => i.line_id,
1332 p_line_number => NULL,
1333 p_hold_id => G_SEED_GSA_HOLD_ID,
1334 p_ship_set_name => NULL,
1335 p_arrival_set_name => NULL,
1336 p_activity_name => NULL,
1337 p_attribute => NULL,
1338 p_top_model_line_id => l_top_model_line_id,
1339 x_return_status => l_return_status);
1340 --bug 3735141
1341 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1342 IF l_debug_level > 0 THEN
1343 oe_debug_pub.add(' GSA Hold applied on line_id:'||i.line_id);
1344 END IF;
1345 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1346 IF l_debug_level > 0 THEN
1347 oe_debug_pub.add('Unexpected error in applying GSA hold');
1348 END IF;
1349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1350 END IF;
1351 --bug 3735141
1352 END IF;
1353 ELSE
1354 IF l_debug_level > 0 THEN
1355 oe_debug_pub.add(' GSA check is disabled');
1356 END If;
1357 END IF;
1358 END LOOP;
1359 END;
1360 --bug 3716296
1361
1362
1363
1364 PROCEDURE Price_Orders
1365 (p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
1366 , p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
1367 , p_adjustments_exist IN VARCHAR2 --pibadj
1368 , x_return_status OUT NOCOPY VARCHAR2
1369 )
1370 IS
1371 l_price_control_rec QP_PREQ_GRP.control_record_type;
1372 l_request_rec oe_order_pub.request_rec_type;
1373 l_line_tbl oe_order_pub.line_tbl_type;
1374 l_multiple_events VARCHAR2(1);
1375 l_book_failed VARCHAR2(1);
1376 l_header_id NUMBER;
1377 l_header_count NUMBER := p_header_rec.HEADER_ID.COUNT;
1378 I NUMBER;
1379 l_ec_installed VARCHAR2(1);
1380 l_index NUMBER;
1381 l_start_index NUMBER := 1;
1382 x_return_status_text VARCHAR2(2000);
1383 l_set_of_books OE_Order_Cache.Set_Of_Books_Rec_Type;
1384 l_line_count NUMBER := p_line_rec.line_id.count;
1385 l_count number;
1386
1387 CURSOR c_price_attributes(l_header_id NUMBER) IS
1388 SELECT line_id
1389 ,price_list_id
1390 ,unit_list_price
1391 ,unit_selling_price
1392 FROM OE_ORDER_LINES l
1393 WHERE l.header_id = l_header_id;
1394
1395 l_start_time NUMBER;
1396 l_end_time NUMBER;
1397
1398
1399 cursor test is
1400 select CURRENCY_CODE,LINE_INDEX,LINE_ID,LINE_TYPE_CODE from qp_preq_lines_tmp;
1401
1402 --
1403 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1404 --
1405 BEGIN
1406
1407 x_return_status := FND_API.G_RET_STS_SUCCESS;
1408 -- invoke QP API to source and directly insert into temp table
1409
1410 qp_price_request_context.set_request_id;
1411
1412 IF l_debug_level > 0 Then
1413 oe_debug_pub.add('Version:'||get_version);
1414 for l_index in 1..l_header_count loop
1415 oe_debug_pub.add('l_index : '||l_index||' header currency code : '||p_header_rec.transactional_curr_code(l_index));
1416 oe_debug_pub.add('price list id : '||p_header_rec.PRICE_LIST_ID(l_index)||' HEADER_ID : '||p_header_rec.HEADER_ID(l_index));
1417 oe_debug_pub.add('ordered date : '||p_header_rec.ORDERED_DATE(l_index)||' PRICING_DATE : '||p_header_rec.PRICING_DATE(l_index)||' header_index : '||p_header_rec.header_index(l_index));
1418 oe_debug_pub.add('orig_sys_document_ref : '||p_header_rec.orig_sys_document_ref(l_index));
1419 end loop;
1420 /*
1421 for l_index in 1..l_line_count loop
1422 oe_debug_pub.add('l_index : '||l_index||' line currency code : '||p_line_rec.currency_code(l_index));
1423 end loop;
1424 */
1425 end if;
1426
1427 If l_debug_level > 0 Then
1428 -- Bug 5640601 =>
1429 -- Selecting hsecs from v$times is changed to execute only when debug
1430 -- is enabled, as hsec is used for logging only when debug is enabled.
1431 SELECT hsecs INTO l_start_time from v$timer;
1432 oe_debug_pub.add('before QP_BULK_PREQ_GRP.Bulk_insert_lines');
1433 end if;
1434 QP_BULK_PREQ_GRP.Bulk_insert_lines(p_header_rec => p_header_rec
1435 , p_line_rec => p_line_rec
1436 , x_return_status => x_return_status
1437 , x_return_status_text => x_return_status_text);
1438 If l_debug_level > 0 Then
1439 oe_debug_pub.add('return status after Bulk_insert_lines : '||x_return_status||' status text : '||x_return_status_text);
1440 End IF;
1441 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1442 OR x_return_status = FND_API.G_RET_STS_ERROR )
1443 THEN
1444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1445 END IF;
1446
1447 If l_debug_level > 0 Then
1448 select count(*) into l_count from qp_preq_lines_tmp;
1449 oe_debug_pub.add('l_count : '||l_count);
1450 for i in test loop
1451 oe_debug_pub.add('curr code : '||i.CURRENCY_CODE||' LINE_INDEX : '||i.LINE_INDEX||' LINE_ID : '||i.LINE_ID||' LINE_TYPE_CODE : '||i.LINE_TYPE_CODE);
1452 end loop;
1453
1454 -- Bug 5640601 =>
1455 -- Selecting hsecs from v$times is changed to execute only when debug
1456 -- is enabled, as hsec is used for logging only when debug is enabled.
1457 SELECT hsecs INTO l_end_time from v$timer;
1458 end if;
1459
1460 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Bulk_insert_lines is (sec) '||((l_end_time-l_start_time)/100));
1461
1462 If l_debug_level > 0 Then
1463 oe_debug_pub.add('before QP_BULK_PREQ_GRP.Bulk_insert_adj');
1464 end if;
1465 IF p_adjustments_exist = 'Y' THEN --pibadj
1466 QP_BULK_PREQ_GRP.Bulk_insert_adj(x_return_status,
1467 x_return_status_text);
1468 If l_debug_level > 0 Then
1469 oe_debug_pub.add('after Bulk_insert_adj return status : '||x_return_status||' status text : '||x_return_status_text);
1470 end if;
1471 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1472 OR x_return_status = FND_API.G_RET_STS_ERROR )
1473 THEN
1474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1475 END IF;
1476 END IF; --pibadj
1477
1478 IF p_header_rec.booked_flag(1) = 'Y' THEN
1479 l_price_control_rec.pricing_event := 'BATCH,BOOK';
1480 ELSE
1481 l_price_control_rec.pricing_event := 'BATCH';
1482 END IF;
1483
1484 --???Control rec set to per call level might need to be changed to per order header level.... already changed for event code
1485 l_Price_Control_Rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
1486 l_Price_Control_Rec.Simulation_Flag := 'N';
1487 l_Price_Control_rec.temp_table_insert_flag := 'N';
1488
1489 --l_Price_Control_rec.check_cust_view_flag := 'N';
1490 --as per spgopal, we shoud set it to 'Y' to get price adjustment id.
1491 l_Price_Control_rec.check_cust_view_flag := 'Y';
1492
1493 l_Price_Control_rec.request_type_code := 'ONT';
1494 l_Price_Control_rec.rounding_flag := 'Q';
1495 l_Price_Control_rec.use_multi_currency:='Y';
1496 l_Price_Control_rec.manual_adjustments_call_flag := 'N';
1497
1498 IF G_FUNCTION_CURRENCY IS NULL Then
1499 l_set_of_books := Oe_Order_Cache.Load_Set_Of_Books;
1500 G_FUNCTION_CURRENCY := l_set_of_books.currency_code;
1501 END IF;
1502 l_Price_Control_rec.FUNCTION_CURRENCY := G_FUNCTION_CURRENCY;
1503
1504 If l_debug_level > 0 Then
1505 -- Bug 5640601 =>
1506 -- Selecting hsecs from v$times is changed to execute only when debug
1507 -- is enabled, as hsec is used for logging only when debug is enabled.
1508 SELECT hsecs INTO l_start_time from v$timer;
1509
1510 oe_debug_pub.add('before QP_PREQ_PUB.PRICE_REQUEST');
1511 end if;
1512 QP_PREQ_PUB.PRICE_REQUEST
1513 (p_control_rec => l_Price_Control_rec
1514 ,x_return_status =>x_return_status
1515 ,x_return_status_Text =>x_return_status_Text
1516 );
1517 If l_debug_level > 0 Then
1518 oe_debug_pub.add('after PRICE_REQUEST return status : '||x_return_status||' status text : '||x_return_status_Text);
1519
1520 -- Bug 5640601 =>
1521 -- Selecting hsecs from v$times is changed to execute only when debug
1522 -- is enabled, as hsec is used for logging only when debug is enabled.
1523 SELECT hsecs INTO l_end_time from v$timer;
1524 end if;
1525
1526 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in PRICE_REQUEST is (sec) '||((l_end_time-l_start_time)/100));
1527 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1528 OR x_return_status = FND_API.G_RET_STS_ERROR )
1529 THEN
1530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1531 END IF;
1532
1533 -- error handling,if an error happens, the whole order in memory should be marked as unbooked
1534 -- check_errors will also post error message in message processing table.
1535 -- check_errors will also handle gsa violation.
1536 IF l_debug_level > 0 Then
1537 oe_debug_pub.add('before check_errors');
1538
1539 -- Bug 5640601 =>
1540 -- Selecting hsecs from v$times is changed to execute only when debug
1541 -- is enabled, as hsec is used for logging only when debug is enabled.
1542 SELECT hsecs INTO l_start_time from v$timer;
1543 end if;
1544
1545 Check_Errors;
1546 IF l_debug_level > 0 Then
1547 -- Bug 5640601 =>
1548 -- Selecting hsecs from v$times is changed to execute only when debug
1549 -- is enabled, as hsec is used for logging only when debug is enabled.
1550 SELECT hsecs INTO l_end_time from v$timer;
1551 end if;
1552 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Check_Errors is (sec) '||((l_end_time-l_start_time)/100));
1553
1554 --Next Upadate memory with the lastest pricing info
1555 IF l_debug_level > 0 Then
1556 -- Bug 5640601 =>
1557 -- Selecting hsecs from v$times is changed to execute only when debug
1558 -- is enabled, as hsec is used for logging only when debug is enabled.
1559 SELECT hsecs INTO l_start_time from v$timer;
1560 end if;
1561 Update_Global_Line;
1562 IF l_debug_level > 0 Then
1563 -- Bug 5640601 =>
1564 -- Selecting hsecs from v$times is changed to execute only when debug
1565 -- is enabled, as hsec is used for logging only when debug is enabled.
1566 SELECT hsecs INTO l_end_time from v$timer;
1567 end if;
1568
1569 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Update_Global_Line is (sec) '||((l_end_time-l_start_time)/100));
1570 check_gsa;
1571
1572
1573 IF l_debug_level > 0 Then
1574 -- Bug 5640601 =>
1575 -- Selecting hsecs from v$times is changed to execute only when debug
1576 -- is enabled, as hsec is used for logging only when debug is enabled.
1577 SELECT hsecs INTO l_start_time from v$timer;
1578 end if;
1579 Insert_Adj;
1580 IF l_debug_level > 0 Then
1581 -- Bug 5640601 =>
1582 -- Selecting hsecs from v$times is changed to execute only when debug
1583 -- is enabled, as hsec is used for logging only when debug is enabled.
1584 SELECT hsecs INTO l_end_time from v$timer;
1585 end if;
1586 FND_FILE.PUT_LINE(FND_FILE.LOG,'Time spent in Insert_Adj is (sec) '||((l_end_time-l_start_time)/100));
1587
1588 --Credit checking (will be called from OEBVORDB.pls)
1589
1590 EXCEPTION
1591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1592 OE_GLOBALS.G_EC_INSTALLED := l_ec_installed;
1593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1594 WHEN OTHERS THEN
1595 OE_GLOBALS.G_EC_INSTALLED := l_ec_installed;
1596 OE_BULK_MSG_PUB.Add_Exc_Msg
1597 (G_PKG_NAME
1598 ,'Price_Orders');
1599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1600 END Price_Orders;
1601
1602 End OE_BULK_PRICEORDER_PVT;