[Home] [Help]
PACKAGE BODY: APPS.OE_ADV_PRICE_PVT
Source
1 PACKAGE BODY OE_ADV_PRICE_PVT AS
2 /* $Header: OEXVAPRB.pls 120.16.12020000.9 2013/05/22 05:13:38 nilegupt ship $ */
3
4 -- Global constant holding the package name
5
6 G_PRICE_LINE_ID_TBL OE_ORDER_ADJ_PVT.Index_Tbl_Type;
7 G_DEBUG BOOLEAN;
8 G_REQUEST_ID NUMBER:=NULL;
9 G_BINARY_LIMIT CONSTANT NUMBER := OE_GLOBALS.G_BINARY_LIMIT; -- BUG 8631297
10
11 procedure Adj_Debug (p_text IN VARCHAR2, p_level IN NUMBER:=5) As
12 --
13 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
14 --
15 Begin
16 If G_DEBUG Then
17 IF l_debug_level > 0 THEN
18 oe_debug_pub.add( P_TEXT , P_LEVEL ) ;
19 END IF;
20 End If;
21 End Adj_Debug;
22
23 procedure Sort_Line_Table(
24 px_line_tbl IN Oe_Order_Pub.Line_Tbl_Type,
25 px_price_line_id_tbl IN OUT NOCOPY Oe_Order_Adj_Pvt.Index_Tbl_Type)
26 IS
27 l_line_index Number;
28 --
29 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
30 --
31 Begin
32
33 l_line_index := px_line_tbl.First;
34 While l_line_index Is Not Null Loop
35 px_price_line_id_tbl(MOD(px_line_tbl(l_line_index).line_id,G_BINARY_LIMIT)) := l_line_index; -- Bug 8631297
36 l_line_index := px_line_tbl.Next(l_line_index);
37 End Loop;
38 End;
39
40
41 Procedure Call_Process_Order(
42 p_old_header_rec OE_Order_Pub.Header_Rec_Type,
43 px_header_rec IN OUT NOCOPY OE_Order_Pub.Header_Rec_Type,
44 px_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type,
45 px_old_line_tbl IN OUT NOCOPY OE_Order_Pub.Line_Tbl_Type,
46 p_control_rec IN OUT NOCOPY OE_GLOBALS.Control_Rec_Type,
47 x_return_status OUT NOCOPY VARCHAR2)
48 IS
49 lx_old_header_rec OE_Order_Pub.Header_Rec_Type := p_old_header_rec;
50 l_line_index NUMBER;
51 -- process_order in variables
52
53 I NUMBER;
54 l_msg_count NUMBER;
55 l_msg_data VARCHAR2(2000);
56 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
57 l_index NUMBER:=1;
58 l_new_line_tbl OE_Order_Pub.Line_Tbl_Type;
59 l_old_line_tbl OE_Order_Pub.Line_Tbl_Type;
60 --
61 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
62 --
63 BEGIN
64 IF l_debug_level > 0 THEN
65 oe_debug_pub.add( 'INSIDE CALL PROCESS ORDER' , 1 ) ;
66 END IF;
67 IF px_line_tbl.COUNT > 0 THEN
68 l_line_index := px_line_tbl.first;
69 while l_line_index is not null loop
70 IF nvl(px_line_tbl(l_line_index).operation,OE_GLOBALS.G_OPR_NONE) = OE_GLOBALS.G_OPR_NONE THEN
71 IF l_debug_level > 0 THEN
72 oe_debug_pub.add('Operation none or null:'||px_line_tbl(l_line_index).line_id||' from px_line_tbl' , 1 ) ;
73 END IF;
74 --Do nothing on this line. None operation
75 NULL;
76 ELSE
77 IF l_debug_level > 0 THEN
78 oe_debug_pub.add('Deleting line id:'||px_line_tbl(l_line_index).line_id||' from px_line_tbl' , 1 ) ;
79 END IF;
80 l_new_line_tbl(l_index):=px_line_tbl(l_line_index);
81 oe_debug_pub.add(' l_index:'||l_index);
82 oe_debug_pub.add(' l_line_index:'||l_line_index);
83 oe_debug_pub.add(' new_line_tbl.count:'||px_line_tbl.count);
84 oe_debug_pub.add(' old_line_tbl.count:'||px_old_line_tbl.count);
85
86 IF px_old_line_tbl.exists(l_line_index) THEN
87 l_old_line_tbl(l_index):=px_old_line_tbl(l_line_index);
88 END IF;
89
90 l_index:=l_index + 1;
91
92 END IF;
93 l_line_index := px_line_tbl.next(l_line_index);
94 end loop;
95
96 -- caller set the security and procees flags on ctrl rec.
97
98 p_control_rec.default_attributes := TRUE;
99 p_control_rec.controlled_operation := TRUE;
100 p_control_rec.change_attributes := TRUE;
101 p_control_rec.validate_entity := TRUE;
102 p_control_rec.write_to_DB := TRUE;
103 p_control_rec.process_entity := OE_GLOBALS.G_ENTITY_HEADER;
104 p_control_rec.process := FALSE; --bug 2866986 added to set process flag to false
105
106 OE_GLOBALS.G_PRICING_RECURSION := 'Y';
107
108 IF l_debug_level > 0 THEN
109 oe_debug_pub.add( 'BEFORE CALLING OE_ORDER_PVT.HEADER' , 1 ) ;
110 oe_debug_pub.add( 'LX_OLD_HEADER_REC.HEADER_ID='||LX_OLD_HEADER_REC.HEADER_ID , 1 ) ;
111 oe_debug_pub.add( 'LX_OLD_HEADER_REC.PAYMENT_TERM_ID='||LX_OLD_HEADER_REC.PAYMENT_TERM_ID , 1 ) ;
112 oe_debug_pub.add( 'LX_OLD_HEADER_REC.OPERATION='||LX_OLD_HEADER_REC.OPERATION , 1 ) ;
113 oe_debug_pub.add( 'PX_HEADER_REC.HEADER_ID='||PX_HEADER_REC.HEADER_ID , 1 ) ;
114 oe_debug_pub.add( 'PX_HEADER_REC.PAYMENT_TERM_ID='||PX_HEADER_REC.PAYMENT_TERM_ID , 1 ) ;
115 oe_debug_pub.add( 'PX_HEADER_REC.OPERATION='||PX_HEADER_REC.OPERATION , 1 ) ;
116 END IF;
117
118 IF (px_header_rec.operation = OE_GLOBALS.G_OPR_UPDATE) Then
119 OE_Order_Pvt.Header
120 ( p_validation_level => FND_API.G_VALID_LEVEL_FULL
121 , p_control_rec => p_control_rec
122 , p_x_header_rec => px_header_rec
123 , p_x_old_header_rec => lx_old_header_rec
124 , x_return_status => l_return_status);
125
126 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
129 RAISE FND_API.G_EXC_ERROR;
130 END IF;
131 END IF;
132 p_control_rec.process_entity := OE_GLOBALS.G_ENTITY_LINE;
133 IF (l_new_line_tbl.count > 0) THEN --bug 2855986 change px_line_tbl to l_new_line_tbl.count
134 OE_Order_Pvt.Lines
135 ( p_validation_level => FND_API.G_VALID_LEVEL_FULL
136 , p_control_rec => p_control_rec
137 , p_x_line_tbl => l_new_line_tbl
138 , p_x_old_line_tbl => l_old_line_tbl
139 , x_return_status => l_return_status);
140
141 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146 END IF;
147 OE_GLOBALS.G_PRICING_RECURSION := 'N';
148 END IF; -- count > 0
149 End Call_Process_Order;
150
151 --Bug 13573144
152 procedure set_tsn(
153 px_line_rec in out nocopy OE_Order_PUB.line_rec_type
154 ,p_term varchar2
155 ,p_substitution_attribute varchar2
156 )
157 is
158 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
159 begin
160 IF l_debug_level > 0 THEN
161 oe_debug_pub.add('In side set_tsn');
162 oe_debug_pub.add('p_term : '||p_term);
163 oe_debug_pub.add('p_substitution_attribute : '||p_substitution_attribute);
164 END IF;
165 IF p_substitution_attribute = 'QUALIFIER_ATTRIBUTE1' THEN
166 px_line_rec.payment_term_id := p_term;
167 ELSIF p_substitution_attribute = 'QUALIFIER_ATTRIBUTE11' THEN
168 px_line_rec.shipping_method_code := p_term;
169 ELSIF p_substitution_attribute = 'QUALIFIER_ATTRIBUTE10' THEN
170 px_line_rec.freight_terms_code := p_term;
171 END IF;
172 end set_tsn;
173 --End of Bug 13573144
174 procedure set_item_for_iue(
175 px_line_rec in out nocopy OE_Order_PUB.line_rec_type
176 ,p_related_item_id NUMBER
177 )
178 is
179 l_org_id NUMBER := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
180 l_ordered_item varchar2(300);
181
182 --
183 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
184 --
185 begin
186
187 IF l_debug_level > 0 THEN
188 oe_debug_pub.add( 'ENTERING OE_ORDER_PRICE_PVT.SET_ITEM_FOR_IUE' ) ;
189
190 /*sdatti*/
191 oe_debug_pub.ADD('px_line_rec.original_inventory_item_id:'||px_line_rec.INVENTORY_ITEM_ID,1);
192 oe_debug_pub.ADD('px_line_rec.original_inventory_item_id:'||px_line_rec.original_INVENTORY_ITEM_ID,1);
193 oe_debug_pub.ADD('px_line_rec.original_ordered_item_id:'||px_line_rec.ordered_item_id,1);
194 oe_debug_pub.ADD('px_line_rec.original_item_identifier_type:'||px_line_rec.item_identifier_type,1);
195 oe_debug_pub.ADD('px_line_rec.original_ordered_item:'||px_line_rec.ordered_item,1);
196
197
198 END IF;
199
200 if px_line_rec.original_inventory_item_id is null then
201 px_line_rec.original_inventory_item_id :=px_line_rec.INVENTORY_ITEM_ID;
202 px_line_rec.original_ordered_item_id :=px_line_rec.ORDERED_ITEM_ID;
203 px_line_rec.original_item_identifier_type :=px_line_rec.item_identifier_type;
204 px_line_rec.original_ordered_item :=px_line_rec.ordered_item;
205 px_line_rec.item_relationship_type :=14;
206 end if;
207 /*sdatti*/
208
209
210 -- There is an item upgrade for this line
211 px_line_rec.inventory_item_id := p_related_item_id;
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add( 'PX_LINE_REC.INVENTORY_ITEM_ID ='||PX_LINE_REC.INVENTORY_ITEM_ID ) ;
214 END IF;
215 px_line_rec.item_identifier_type := 'INT'; --bug 2281351
216 If px_line_rec.item_identifier_type ='INT' then
217 px_line_rec.ordered_item_id := p_related_item_id;
218 Begin
219 SELECT concatenated_segments
220 INTO px_line_rec.ordered_item
221 FROM mtl_system_items_kfv
222 WHERE inventory_item_id = px_line_rec.inventory_item_id
223 AND organization_id = l_org_id;
224 Exception when no_data_found then
225 Null;
226 End;
227 End If;
228 IF l_debug_level > 0 THEN
229 oe_debug_pub.add( 'EXITING OE_ORDER_ADJ_PVT.SET_ITEM_FOR_IUE' ) ;
230 END IF;
231 end set_item_for_iue;
232
233
234 --Procedure delete_attribs_for_iue(p_price_adjustment_id in number) --Bug 13573144
235 Procedure delete_attribs_for_iue(
236 p_price_adjustment_id in number
237 ,p_modifier_level_code IN VARCHAR2 DEFAULT 'LINE') --Bug 13573144
238 is
239 l_Line_Adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
240 l_Header_Adj_rec OE_Order_PUB.Header_Adj_Rec_Type; --Bug 13573144
241 l_return_status VARCHAR2(30);
242 l_index NUMBER;
243 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
244
245 begin
246 l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
247 l_Line_Adj_rec.price_adjustment_id := p_price_adjustment_id;
248 --Bug 13573144
249 if p_modifier_level_code <> 'ORDER' THEN
250 IF l_debug_level > 0 THEN
251 oe_debug_pub.add('Line Level Adjustment');
252 END IF;
253 --End of Bug 13573144
254 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
255 p_line_adj_rec =>l_line_adj_rec,
256 p_line_adj_id => l_line_adj_rec.price_adjustment_id,
257 p_old_line_adj_rec =>l_line_adj_rec,
258 x_index => l_index,
259 x_return_status => l_return_status);
260 --Bug 13573144
261 ELSE
262 IF l_debug_level > 0 THEN
263 oe_debug_pub.add('Header Level Adjustment');
264 END IF;
265 l_Header_Adj_rec.operation := OE_GLOBALS.G_OPR_DELETE;
266 l_Header_Adj_rec.price_adjustment_id := p_price_adjustment_id;
267 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
268 p_hdr_adj_rec =>l_Header_Adj_rec,
269 p_hdr_adj_id => l_Header_Adj_rec.price_adjustment_id,
270 p_old_hdr_adj_rec =>l_Header_Adj_rec,
271 x_index => l_index,
272 x_return_status => l_return_status);
273 end if;
274 --End of Bug 13573144
275 IF l_debug_level > 0 THEN
276 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_ADV_PRICE_PVT.DELETE_ATTRIBS_FOR_IUE IS: ' || L_RETURN_STATUS ) ;
277 END IF;
278
279 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
280
281 IF l_debug_level > 0 THEN
282 oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
283 oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.DELETE_ATTRIBS_FOR_IUE' , 1 ) ;
284 END IF;
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
287 IF l_debug_level > 0 THEN
288 oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_ADV_PRICE_PVT.DELETE_ATTRIBS_FOR_IUE' ) ;
289 oe_debug_pub.add( 'EXITING OE_ORDER_PRICE_PVT.DELETE_DEPENDENTS' , 1 ) ;
290 END IF;
291 RAISE FND_API.G_EXC_ERROR;
292 END IF;
293
294 DELETE FROM OE_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = p_price_adjustment_id;
295 End;
296
297 Procedure Item_Upgrade
298 ( px_old_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
299 px_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
300 p_pricing_events IN VARCHAR2
301 )
302 IS
303
304 Cursor upgraded_items IS
305 SELECT ldets.line_detail_index,ldets.Line_index,ldets.related_item_id,lines.line_id
306 FROM QP_LDETS_V ldets,
307 QP_PREQ_LINES_TMP lines
308 WHERE ldets.list_line_Type_code /*created_from_list_line_type list_line_Type_code*/= 'IUE'
309 AND ldets.process_code IN (QP_PREQ_PUB.G_STATUS_NEW,
310 QP_PREQ_PUB.G_STATUS_UPDATED)
311 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)
312 AND lines.process_status <> 'NOT_VALID'
313 AND ldets.line_index=lines.line_index;
314
315
316 --amy: revert to original item if the IUE is deleted
317 Cursor revert_back_items IS
318 SELECT adj.line_id, adj.modified_from,adj.price_adjustment_id
319 ,adj.modifier_level_code --Bug 13573144
320 FROM OE_PRICE_ADJUSTMENTS adj
321 WHERE HEADER_ID = oe_order_cache.g_header_rec.header_id
322 AND list_line_Type_code = 'IUE'
323 AND PRICING_PHASE_ID IN (select pricing_phase_id from qp_event_phases
324 where instr(p_pricing_events||',', pricing_event_code||',') >0)
325 AND LINE_ID IN (select line_id
326 from qp_preq_lines_tmp where
327 line_type_code='LINE'
328 and price_flag IN ('Y','P')
329 and process_status <> 'NOT_VALID'
330 and pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED, QP_PREQ_PUB.G_STATUS_GSA_VIOLATION, QP_PREQ_PUB.G_STATUS_UNCHANGED))
331 /* AND list_line_id NOT IN (SELECT list_line_id
332 from qp_ldets_v where process_code
333 IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) and line_index=adj.line_id+oe_order_cache.g_header_rec.header_id)*/
334 AND list_line_id NOT IN (SELECT list_line_id
335 from qp_ldets_v ld, qp_preq_lines_tmp l
336 where ld.process_code
337 IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) and l.line_index = ld.line_index
338 and l.line_id = adj.line_id);
339
340
341
342 i number;
343 l_header_id number := oe_order_cache.g_header_rec.header_id;
344 l_mod_line_id NUMBER;
345
346 --
347 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
348 --
349 BEGIN
350
351
352 IF oe_order_cache.g_header_rec.booked_flag = 'Y' THEN
353 RETURN;
354 END IF;
355
356 -- AmyIUE: let's revert back the following way
357 FOR i in revert_back_items LOOP
358 IF l_debug_level > 0 THEN
359 oe_debug_pub.add( 'REVERT BACK ITEM'||I.LINE_ID ) ;
360 END IF;
361 -- 8631297
362 l_mod_line_id := MOD(i.line_id,G_BINARY_LIMIT);
363 oe_debug_pub.add( ' mod line id :'|| l_mod_line_id,1);
364 oe_debug_pub.add( 'line id :'|| G_PRICE_LINE_ID_TBL(l_mod_line_id));
365
366 -- 8631297
367 -- Replaced i.line_id with l_mod_line_id
368 --bug 2858712
369 IF (px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).line_set_id IS NULL OR
370 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).line_set_id = FND_API.G_MISS_NUM) THEN
371 set_item_for_iue(px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)),to_number(i.modified_from));
372 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
373 --bug 2795409
374 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_reason := 'SYSTEM';
375 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_comments := 'REPRICING';
376
377 --bug 2858712
378 DELETE FROM OE_PRICE_ADJUSTMENTS
379 WHERE PRICE_ADJUSTMENT_ID = i.price_adjustment_id;
380 --Bug 13573144
381 if i.modifier_level_code = 'ORDER' then
382 delete_attribs_for_iue(i.price_adjustment_id,i.modifier_level_code);
383 else
384 --End of Bug 13573144
385 delete_attribs_for_iue(i.price_adjustment_id);
386 END IF; --Bug 13573144
387 END IF;
388 END LOOP;
389
390 FOR i in upgraded_items LOOP
391
392 -- 8631297
393 l_mod_line_id := MOD(i.line_id,G_BINARY_LIMIT);
394 oe_debug_pub.add( ' mod line id :'|| l_mod_line_id,1);
395
396 /* change i.line_index - l_header_id with i.line_id */
397
398 -- 8631297
399 -- Replaced i.line_id with l_mod_line_id
400 IF (px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).top_model_line_id IS NULL OR
401 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).top_model_line_id = FND_API.G_MISS_NUM) THEN
402 --bug 2858712
403 IF (px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).line_set_id IS NULL OR
404 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).line_set_id = FND_API.G_MISS_NUM) THEN
405
406 set_item_for_iue(px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)),i.Related_Item_ID);
407 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
408 --bug 2795409
409 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_reason := 'SYSTEM';
410 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_comments := 'REPRICING';
411
412 INSERT INTO OE_PRICE_ADJUSTMENTS
413 ( PRICE_ADJUSTMENT_ID
414 , CREATION_DATE
415 , CREATED_BY
416 , LAST_UPDATE_DATE
417 , LAST_UPDATED_BY
418 , LAST_UPDATE_LOGIN
419 , PROGRAM_APPLICATION_ID
420 , PROGRAM_ID
421 , PROGRAM_UPDATE_DATE
422 , REQUEST_ID
423 , HEADER_ID
424 , DISCOUNT_ID
425 , DISCOUNT_LINE_ID
426 , AUTOMATIC_FLAG
427 , PERCENT
428 , LINE_ID
429 , CONTEXT
430 , ATTRIBUTE1
431 , ATTRIBUTE2
432 , ATTRIBUTE3
433 , ATTRIBUTE4
434 , ATTRIBUTE5
435 , ATTRIBUTE6
436 , ATTRIBUTE7
437 , ATTRIBUTE8
438 , ATTRIBUTE9
439 , ATTRIBUTE10
440 , ATTRIBUTE11
441 , ATTRIBUTE12
442 , ATTRIBUTE13
443 , ATTRIBUTE14
444 , ATTRIBUTE15
445 , ORIG_SYS_DISCOUNT_REF
446 , LIST_HEADER_ID
447 , LIST_LINE_ID
448 , LIST_LINE_TYPE_CODE
449 , MODIFIER_MECHANISM_TYPE_CODE
450 , MODIFIED_FROM
451 , MODIFIED_TO
452 , UPDATED_FLAG
453 , UPDATE_ALLOWED
454 , APPLIED_FLAG
455 , CHANGE_REASON_CODE
456 , CHANGE_REASON_TEXT
457 , operand
458 , Arithmetic_operator
459 , COST_ID
460 , TAX_CODE
461 , TAX_EXEMPT_FLAG
462 , TAX_EXEMPT_NUMBER
463 , TAX_EXEMPT_REASON_CODE
464 , PARENT_ADJUSTMENT_ID
465 , INVOICED_FLAG
466 , ESTIMATED_FLAG
467 , INC_IN_SALES_PERFORMANCE
468 , SPLIT_ACTION_CODE
469 , ADJUSTED_AMOUNT
470 , PRICING_PHASE_ID
471 , CHARGE_TYPE_CODE
472 , CHARGE_SUBTYPE_CODE
473 , list_line_no
474 , source_system_code
475 , benefit_qty
476 , benefit_uom_code
477 , print_on_invoice_flag
478 , expiration_date
479 , rebate_transaction_type_code
480 , rebate_transaction_reference
481 , rebate_payment_system_code
482 , redeemed_date
483 , redeemed_flag
484 , accrual_flag
485 , range_break_quantity
486 , accrual_conversion_rate
487 , pricing_group_sequence
488 , modifier_level_code
489 , price_break_type_code
490 , substitution_attribute
491 , proration_type_code
492 , CREDIT_OR_CHARGE_FLAG
493 , INCLUDE_ON_RETURNS_FLAG
494 , AC_CONTEXT
495 , AC_ATTRIBUTE1
496 , AC_ATTRIBUTE2
497 , AC_ATTRIBUTE3
498 , AC_ATTRIBUTE4
499 , AC_ATTRIBUTE5
500 , AC_ATTRIBUTE6
501 , AC_ATTRIBUTE7
502 , AC_ATTRIBUTE8
503 , AC_ATTRIBUTE9
504 , AC_ATTRIBUTE10
505 , AC_ATTRIBUTE11
506 , AC_ATTRIBUTE12
507 , AC_ATTRIBUTE13
508 , AC_ATTRIBUTE14
509 , AC_ATTRIBUTE15
510 , OPERAND_PER_PQTY
511 , ADJUSTED_AMOUNT_PER_PQTY
512 , LOCK_CONTROL
513 )
514 ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
515 -- oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
516 ldets.price_adjustment_id
517 , sysdate --p_Line_Adj_rec.creation_date
518 , fnd_global.user_id --p_Line_Adj_rec.created_by
519 , sysdate --p_Line_Adj_rec.last_update_date
520 , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
521 , fnd_global.login_id --p_Line_Adj_rec.last_update_login
522 , NULL --p_Line_Adj_rec.program_application_id
523 , NULL --p_Line_Adj_rec.program_id
524 , NULL --p_Line_Adj_rec.program_update_date
525 , NULL --p_Line_Adj_rec.request_id
526 , oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
527 , NULL --p_Line_Adj_rec.discount_id
528 , NULL --p_Line_Adj_rec.discount_line_id
529 , ldets.automatic_flag
530 , NULL --p_Line_Adj_rec.percent
531 , decode(ldets.modifier_level_code,'ORDER',NULL,i.line_id)
532 , NULL --p_Line_Adj_rec.context
533 , NULL --p_Line_Adj_rec.attribute1
534 , NULL --p_Line_Adj_rec.attribute2
535 , NULL --p_Line_Adj_rec.attribute3
536 , NULL --p_Line_Adj_rec.attribute4
537 , NULL --p_Line_Adj_rec.attribute5
538 , NULL --p_Line_Adj_rec.attribute6
539 , NULL --p_Line_Adj_rec.attribute7
540 , NULL --p_Line_Adj_rec.attribute8
541 , NULL --p_Line_Adj_rec.attribute9
542 , NULL --p_Line_Adj_rec.attribute10
543 , NULL --p_Line_Adj_rec.attribute11
544 , NULL --p_Line_Adj_rec.attribute12
545 , NULL --p_Line_Adj_rec.attribute13
546 , NULL --p_Line_Adj_rec.attribute14
547 , NULL --p_Line_Adj_rec.attribute15
548 , NULL --p_Line_Adj_rec.orig_sys_discount_ref
549 , ldets.LIST_HEADER_ID
550 , ldets.LIST_LINE_ID
551 , ldets.LIST_LINE_TYPE_CODE
552 , NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
553 , to_char(ldets.inventory_item_id)
554 , to_char(ldets.related_item_id)
555 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
556 , ldets.override_flag
557 , ldets.APPLIED_FLAG
558 , NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
559 , NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
560 , NULL
561 , ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
562 , NULl --p_line_Adj_rec.COST_ID
563 , NULL --p_line_Adj_rec.TAX_CODE
564 , NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
565 , NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
566 , NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
567 , NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
568 , NULL --p_line_Adj_rec.INVOICED_FLAG
569 , NULL --p_line_Adj_rec.ESTIMATED_FLAG
570 , NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
571 , NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
572 , NULL
573 , ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
574 , ldets.CHARGE_TYPE_CODE
575 , ldets.CHARGE_SUBTYPE_CODE
576 , ldets.list_line_no
577 , qh.source_system_code
578 , ldets.benefit_qty
579 , ldets.benefit_uom_code
580 , NULL --p_Line_Adj_rec.print_on_invoice_flag
581 , ldets.expiration_date
582 , ldets.rebate_transaction_type_code
583 , NULL --p_Line_Adj_rec.rebate_transaction_reference
584 , NULL --p_Line_Adj_rec.rebate_payment_system_code
585 , NULL --p_Line_Adj_rec.redeemed_date
586 , NULL --p_Line_Adj_rec.redeemed_flag
587 , ldets.accrual_flag
588 , ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
589 , ldets.accrual_conversion_rate
590 , ldets.pricing_group_sequence
591 , ldets.modifier_level_code
592 , ldets.price_break_type_code
593 , ldets.substitution_attribute
594 , ldets.proration_type_code
595 , NULL --p_Line_Adj_rec.credit_or_charge_flag
596 , ldets.include_on_returns_flag
597 , NULL -- p_Line_Adj_rec.ac_context
598 , NULL -- p_Line_Adj_rec.ac_attribute1
599 , NULL -- p_Line_Adj_rec.ac_attribute2
600 , NULL -- p_Line_Adj_rec.ac_attribute3
601 , NULL -- p_Line_Adj_rec.ac_attribute4
602 , NULL -- p_Line_Adj_rec.ac_attribute5
603 , NULL -- p_Line_Adj_rec.ac_attribute6
604 , NULL -- p_Line_Adj_rec.ac_attribute7
605 , NULL -- p_Line_Adj_rec.ac_attribute8
606 , NULL -- p_Line_Adj_rec.ac_attribute9
607 , NULL -- p_Line_Adj_rec.ac_attribute10
608 , NULL -- p_Line_Adj_rec.ac_attribute11
609 , NULL -- p_Line_Adj_rec.ac_attribute12
610 , NULL -- p_Line_Adj_rec.ac_attribute13
611 , NULL -- p_Line_Adj_rec.ac_attribute14
612 , NULL -- p_Line_Adj_rec.ac_attribute15
613 , NULL
614 , NULL
615 , 1
616 FROM
617 QP_LDETS_v ldets
618 , QP_LIST_HEADERS_B QH
619 WHERE
620 ldets.line_detail_index = i.line_detail_index
621 and ldets.list_header_id=qh.list_header_id
622 AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
623 );
624
625 --AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
626 IF l_debug_level > 0 THEN
627 oe_debug_pub.add( 'in upgraded items cursor');
628 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
629 END IF;
630 END IF;
631 END IF;
632 END LOOP;
633
634 END Item_Upgrade;
635
636
637
638 Procedure Term_Substitution
639 ( p_old_header_rec IN OE_ORDER_PUB.Header_Rec_Type,
640 px_header_rec IN OUT NOCOPY OE_ORDER_PUB.Header_Rec_Type,
641 px_old_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
642 px_line_tbl IN OUT NOCOPY OE_ORDER_PUB.Line_Tbl_Type,
643 p_pricing_events IN VARCHAR2 --Bug 13573144
644 )
645 Is
646 cursor oe_price_adj_cur(p_line_id number) is
647 select price_adjustment_id,LIST_LINE_ID,PRICING_PHASE_ID ,
648 MODIFIER_LEVEL_CODE
649 from oe_price_adjustments where
650 line_id = p_line_id and LIST_LINE_TYPE_CODE = 'TSN';
651
652 Cursor ldets_cur is
653 select line_index,LINE_DETAIL_INDEX,LINE_DETAIL_TYPE_CODE,
654 CREATED_FROM_LIST_LINE_ID,CREATED_FROM_LIST_LINE_TYPE,
655 CREATED_FROM_LIST_TYPE_CODE,SUBSTITUTION_TYPE_CODE,
656 SUBSTITUTION_VALUE_FROM,SUBSTITUTION_VALUE_TO,PROCESSED_FLAG,
657 PRICING_STATUS_CODE,PRICING_PHASE_ID,APPLIED_FLAG,PROCESS_CODE,
658 UPDATED_FLAG
659 from qp_preq_ldets_tmp where
660 CREATED_FROM_LIST_LINE_TYPE = 'TSN';
661 Cursor test is
662 select Line_index,substitution_attribute,substitution_value_to,
663 process_code
664 FROM QP_LDETS_V
665 WHERE list_line_type_code = 'TSN';
666 Cursor new_terms IS
667 SELECT ldets.Line_index, ldets.line_detail_index, lines.line_id --bug 4190357 added line_detail_index
668 , ldets.substitution_attribute, ldets.substitution_value_to
669 ,CHANGE_REASON_CODE,CHANGE_REASON_TEXT
670 , MODIFIER_LEVEL_CODE --Bug 13573144
671 ,lines.line_type_code --bug 4190357
672 FROM QP_LDETS_V ldets, qp_preq_lines_tmp lines
673 WHERE ldets.list_line_type_code = 'TSN'
674 AND ldets.process_code IN (QP_PREQ_PUB.G_STATUS_NEW
675 , QP_PREQ_PUB.G_STATUS_UPDATED
676 )
677 AND ldets.Line_index = lines.Line_index
678 order by ldets.line_index ASC;
679
680 --Bug 13573144
681 Cursor revert_back_tsn_line IS
682 SELECT adj.line_id, adj.modified_from,adj.price_adjustment_id,
683 adj.SUBSTITUTION_ATTRIBUTE, adj.MODIFIER_LEVEL_CODE,
684 adj.header_id
685 FROM OE_PRICE_ADJUSTMENTS adj
686 WHERE adj.HEADER_ID = oe_order_cache.g_header_rec.header_id
687 AND adj.list_line_Type_code = 'TSN'
688 AND adj.PRICING_PHASE_ID IN (select pricing_phase_id from qp_event_phases
689 where instr(p_pricing_events||',', pricing_event_code||',')>0)
690 AND adj.LINE_ID IN (select line_id
691 from qp_preq_lines_tmp where
692 line_type_code='LINE'
693 and price_flag IN ('Y','P')
694 and process_status <> 'NOT_VALID'
695 and pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED, QP_PREQ_PUB.G_STATUS_GSA_VIOLATION, QP_PREQ_PUB.G_STATUS_UNCHANGED))
696 AND adj.price_Adjustment_id NOT IN (SELECT ld.price_Adjustment_id
697 from qp_ldets_v ld, qp_preq_lines_tmp l
698 where ld.process_code
699 IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) and l.line_index = ld.line_index
700 );
701
702 Cursor revert_back_tsn_order IS
703 SELECT adj.line_id, adj.modified_from,adj.price_adjustment_id,
704 adj.SUBSTITUTION_ATTRIBUTE, adj.MODIFIER_LEVEL_CODE,
705 adj.header_id
706 FROM OE_PRICE_ADJUSTMENTS adj
707 WHERE adj.HEADER_ID = oe_order_cache.g_header_rec.header_id
708 AND adj.list_line_Type_code = 'TSN'
709 AND adj.PRICING_PHASE_ID IN (select pricing_phase_id from qp_event_phases
710 where instr(p_pricing_events||',', pricing_event_code||',')>0)
711 AND adj.line_id IS NULL
712 AND adj.header_id IN (select line_id
713 from qp_preq_lines_tmp where
714 line_type_code='ORDER'
715 and price_flag IN ('Y','P')
716 and process_status <> 'NOT_VALID'
717 and pricing_status_code in (QP_PREQ_PUB.G_STATUS_UPDATED, QP_PREQ_PUB.G_STATUS_GSA_VIOLATION, QP_PREQ_PUB.G_STATUS_UNCHANGED))
718 AND adj.price_Adjustment_id NOT IN (SELECT price_Adjustment_id
719 from qp_ldets_v ld, qp_preq_lines_tmp l
720 where ld.process_code
721 IN (QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) and l.line_index = ld.line_index
722 and l.line_type_code='ORDER' and l.line_id = adj.header_id);
723
724 Cursor order_level_Adj(p_header_id in Number) IS
725 select substitution_attribute, modified_to
726 from oe_price_Adjustments
727 where header_id = p_header_id and line_id is null
728 and list_line_type_code = 'TSN';
729 --End of Bug 13573144
730 i number;
731 l_header_id number := oe_order_cache.g_header_rec.header_id;
732 l_mod_line_id NUMBER;
733 j number;
734 k number;
735 l number;
736 --vcount number; --Bug 13573144
737 vcount number := 0; --Bug 13573144
738 m number;
739 -- l_header_rec OE_ORDER_PUB.Header_Rec_Type := oe_order_cache.g_header_rec;
740 --Bug 13573144
741 /*
742 --bug 4190357
743 l_count NUMBER;
744 --bug 4190357
745 l_old_shipping_method_code Varchar2(30); --bug 4190357
746 */
747 --End of Bug 13573144
748 --Bug 13573144
749 l_modified_From varchar2(240);
750 l_mod_to VARCHAR2(240);
751 ord_pmt_term VARCHAR2(240);
752 ord_shp_mtd_cod VARCHAR2(240);
753 ord_Frt_trm_cod VARCHAR2(240);
754
755 l_query_executed VARCHAR2(1) := 'N';
756 --End of Bug 13573144
757 --
758 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
759 --
760 BEGIN
761 IF l_debug_level > 0 THEN
762 oe_debug_pub.add( 'IN TERMS SUBSTITUTION' , 1 ) ;
763 END IF;
764
765 --Bug 13573144
766 IF nvl(QP_UTIL_PUB.Basic_Pricing_Setup, 'N') = 'Y' THEN
767 IF l_debug_level > 0 THEN
768 oe_debug_pub.add('basic pricing setup Y');
769 END IF;
770 return;
771 END IF;
772 --End of Bug 13573144
773
774 If G_DEBUG Then
775 for l in ldets_cur loop
776 oe_debug_pub.add( 'LINE_INDEX='||L.LINE_INDEX , 1 ) ;
777 oe_debug_pub.add( 'LINE_DETAIL_INDEX='||L.LINE_DETAIL_INDEX , 1 ) ;
778 oe_debug_pub.add( 'LINE_DETAIL_TYPE_CODE='||L.LINE_DETAIL_TYPE_CODE , 1 ) ;
779 oe_debug_pub.add( 'CREATED_FROM_LIST_LINE_ID='||L.CREATED_FROM_LIST_LINE_ID , 1 ) ;
780 oe_debug_pub.add( 'CREATED_FROM_LIST_LINE_TYPE='||L.CREATED_FROM_LIST_LINE_TYPE , 1 ) ;
781 oe_debug_pub.add( 'CREATED_FROM_LIST_TYPE_CODE='||L.CREATED_FROM_LIST_TYPE_CODE , 1 ) ;
782 oe_debug_pub.add( 'SUBSTITUTION_TYPE_CODE='||L.SUBSTITUTION_TYPE_CODE , 1 ) ;
783 oe_debug_pub.add( 'SUBSTITUTION_VALUE_FROM='||L.SUBSTITUTION_VALUE_FROM , 1 ) ;
784 oe_debug_pub.add( 'SUBSTITUTION_VALUE_TO='||L.SUBSTITUTION_VALUE_TO , 1 ) ;
785 oe_debug_pub.add( 'PROCESSED_FLAG='||L.PROCESSED_FLAG , 1 ) ;
786 oe_debug_pub.add( 'PRICING_STATUS_CODE='||L.PRICING_STATUS_CODE , 1 ) ;
787 oe_debug_pub.add( 'PRICING_PHASE_ID='||L.PRICING_PHASE_ID , 1 ) ;
788 oe_debug_pub.add( 'APPLIED_FLAG='||L.APPLIED_FLAG , 1 ) ;
789 oe_debug_pub.add( 'PROCESS_CODE='||L.PROCESS_CODE , 1 ) ;
790 oe_debug_pub.add( 'UPDATED_FLAG='||L.UPDATED_FLAG , 1 ) ;
791
792 end loop;
793
794 for k in test loop
795 oe_debug_pub.add( 'LINE_INDEX = '||K.LINE_INDEX , 1 ) ;
796 oe_debug_pub.add( 'SUBSTITUTION_ATTRIBUTE = '||K.SUBSTITUTION_ATTRIBUTE , 1 ) ;
797 oe_debug_pub.add( 'SUBSTITUTION_VALUE_TO = '||K.SUBSTITUTION_VALUE_TO , 1 ) ;
798 oe_debug_pub.add( 'PROCESS_CODE = '||K.PROCESS_CODE , 1 ) ;
799 end loop;
800 End If; --end if for g_debug
801
802 --Bug 13573144
803 FOR i in revert_back_tsn_order LOOP
804 IF l_debug_level > 0 THEN
805 oe_debug_pub.add('i.line_id : '||i.line_id);
806 oe_debug_pub.add('i.SUBSTITUTION_ATTRIBUTE : '||i.SUBSTITUTION_ATTRIBUTE);
807 oe_debug_pub.add('i.MODIFIER_LEVEL_CODE : '||i.MODIFIER_LEVEL_CODE);
808 oe_debug_pub.add('i.header_id : '||i.header_id);
809 oe_debug_pub.add('i.modified_from : '||i.modified_From);
810 oe_debug_pub.add('i.price_adjustment_id : '||i.price_adjustment_id);
811 END IF;
812 if i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
813 px_header_rec.payment_term_id := i.modified_from;
814 elsif i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE11' THEN
815 px_header_rec.shipping_method_code := i.modified_from;
816 elsif i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE10' Then
817 px_header_rec.freight_terms_code := i.modified_from;
818 end if;
819 px_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
820 -- LOOP through all lines to see if terms need to be reverted back for lines
821 j := px_line_tbl.FIRST;
822 WHILE j Is Not Null loop
823 IF l_debug_level > 0 THEN
824 oe_debug_pub.add( 'J = '||J , 1 ) ;
825 oe_debug_pub.add( 'LINE ID = '||PX_LINE_TBL ( J ) .LINE_ID ,1 ) ;
826 END IF;
827 --update line only if open and not cancelled
828 if( nvl(px_line_tbl(j).cancelled_flag,'N') <> 'Y' and nvl(px_line_tbl(j).open_flag,'Y') <> 'N' ) THEN
829 select count(*) into vcount from oe_price_adjustments where
830 line_id = px_line_tbl(j).line_id and
831 LIST_LINE_TYPE_CODE = 'TSN' and MODIFIER_LEVEL_CODE = 'LINE';
832 If vcount = 0 then
833 If i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
834 px_line_tbl(j).payment_term_id := i.modified_from;
835 Elsif i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE11' Then
836 px_line_tbl(j).shipping_method_code := i.modified_from;
837 Elsif i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE10' Then
838 px_line_tbl(j).freight_terms_code := i.modified_from;
839 End If;
840 px_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
841 px_line_tbl(j).change_reason := 'SYSTEM';
842 px_line_tbl(j).change_comments := 'REPRICING';
843 end if;
844 END IF;
845 j := px_line_tbl.Next(j);
846
847 IF l_debug_level > 0 THEN
848 oe_debug_pub.add( 'J = END OF LOOP '||J , 1 ) ;
849 END IF;
850 END LOOP;
851
852 DELETE FROM OE_PRICE_ADJUSTMENTS
853 WHERE PRICE_ADJUSTMENT_ID = i.price_adjustment_id;
854 if i.MODIFIER_LEVEL_CODE = 'ORDER' then
855 delete_attribs_for_iue(i.price_adjustment_id,i.MODIFIER_LEVEL_CODE);
856 else
857 delete_attribs_for_iue(i.price_adjustment_id);
858 end if;
859 END LOOP;
860
861 FOR i in revert_back_tsn_line LOOP
862 l_mod_line_id := MOD(i.line_id,G_BINARY_LIMIT);
863
864 IF l_debug_level > 0 THEN
865 oe_debug_pub.add( ' mod line id :'|| l_mod_line_id,1);
866 oe_debug_pub.add('i.SUBSTITUTION_ATTRIBUTE : '||i.SUBSTITUTION_ATTRIBUTE);
867 oe_debug_pub.add('i.MODIFIER_LEVEL_CODE : '||i.MODIFIER_LEVEL_CODE);
868 oe_debug_pub.add('i.header_id : '||i.header_id);
869 oe_debug_pub.add('i.modified_from : '||i.modified_From);
870 oe_debug_pub.add('i.price_adjustment_id : '||i.price_adjustment_id);
871 END IF;
872 IF l_query_executed = 'N' THEN
873 If l_debug_level > 0 Then
874 oe_debug_pub.add('executing the query to get order tsn');
875 End If;
876 for k in order_level_adj(i.header_id) Loop
877 If k.substitution_attribute = 'QUALIFIER_ATTRIBUTE1' Then
878 ord_pmt_term := k.modified_to;
879 Elsif k.substitution_attribute = 'QUALIFIER_ATTRIBUTE11' Then
880 ord_shp_mtd_cod := k.modified_to;
881 Elsif k.substitution_attribute = 'QUALIFIER_ATTRIBUTE10' Then
882 ord_Frt_trm_cod := k.modified_to;
883 END IF;
884 end loop;
885 l_query_executed := 'Y';
889 END IF;
886 END IF;
887 IF l_debug_level > 0 THEN
888 oe_debug_pub.add( 'REVERT BACK TSN'||I.LINE_ID ) ;
890 IF i.substitution_attribute = 'QUALIFIER_ATTRIBUTE1' THEN
891 IF ord_pmt_term IS NOT NULL THEN
892 l_mod_to := ord_pmt_term;
893 ELSE
894 l_mod_to := i.modified_from;
895 END IF;
896 ELSIF i.substitution_attribute = 'QUALIFIER_ATTRIBUTE11' THEN
897 IF ord_shp_mtd_cod IS NOT NULL THEN
898 l_mod_to := ord_shp_mtd_cod;
899 ELSE
900 l_mod_to := i.modified_from;
901 END IF;
902 ELSIF i.substitution_attribute = 'QUALIFIER_ATTRIBUTE10' THEN
903 IF ord_Frt_trm_cod IS NOT NULL THEN
904 l_mod_to := ord_Frt_trm_cod;
905 ELSE
906 l_mod_to := i.modified_from;
907 END IF;
908 END IF;
909 set_tsn(px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)),l_mod_to, i.substitution_attribute);
910 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
911 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_reason := 'SYSTEM';
912 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_comments := 'REPRICING';
913 DELETE FROM OE_PRICE_ADJUSTMENTS
914 WHERE PRICE_ADJUSTMENT_ID = i.price_adjustment_id;
915 delete_attribs_for_iue(i.price_adjustment_id);
916 END LOOP;
917 --End of Bug 13573144
918
919 --6965002 start
920 px_header_rec.change_reason := 'SYSTEM';
921 px_header_rec.change_comments := 'REPRICING';
922 --6965002 end
923
924 FOR i in new_terms LOOP
925 IF l_debug_level > 0 THEN
926 oe_debug_pub.add( 'L_HEADER_ID = '||L_HEADER_ID , 1 ) ;
927 oe_debug_pub.add( 'LINE INDEX = '||I.LINE_INDEX , 1 ) ;
928 oe_debug_pub.add( 'SUBSTITUTION_ATTRIBUTE = '||I.SUBSTITUTION_ATTRIBUTE , 1 ) ;
929 oe_debug_pub.add( 'SUBSTITUTION_VALUE_TO = '||I.SUBSTITUTION_VALUE_TO , 1 ) ;
930 oe_debug_pub.add('change rason code = '||I.CHANGE_REASON_CODE);
931 oe_debug_pub.add('change reason text = '||I.CHANGE_REASON_TEXT);
932 END IF;
933
934 -- for header level term substitution, needs to update all lines
935 --IF (i.line_id = l_header_id and i.line_type_code = 'ORDER') THEN --Bug 13573144
936 IF i.line_id = l_header_id THEN --Bug 13573144
937 --Bug 13573144
938 /* --bug 4190357
939 l_count := 0;
940 --bug 4190357 */
941 --End of Bug 13573144
942 if i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
943 l_modified_from := px_header_rec.payment_term_id; --Bug 13573144
944 px_header_rec.payment_term_id := i.Substitution_value_to;
945 elsIf i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE11' Then
946 --l_old_shipping_method_code := px_header_rec.shipping_method_code; --Bug 13573144
947 l_modified_from := px_header_rec.shipping_method_code; --Bug 13573144
948 px_header_rec.shipping_method_code := i.Substitution_value_to;
949 --Bug 13573144
950 /*
951 --bug 4190357
952 SELECT count(*)
953 INTO l_count
954 FROM wsh_carrier_services wsh,
955 wsh_org_carrier_services wsh_org
956 WHERE wsh_org.organization_id = px_header_rec.ship_from_org_id
957 AND wsh.carrier_service_id = wsh_org.carrier_service_id
958 AND wsh.ship_method_code = px_header_rec.shipping_method_code
959 AND wsh_org.enabled_flag = 'Y';
960 --bug 4190357
961 */--End of Bug 13573144
962 elsIf i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE10' Then
963 l_modified_from := px_header_rec.freight_terms_code; --Bug 13573144
964 px_header_rec.freight_terms_code := i.Substitution_value_to;
965 End If;
966 px_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
967 -- LOOP through all lines, update terms on the line
968 j := px_line_tbl.FIRST;
969 WHILE j Is Not Null loop
970 IF l_debug_level > 0 THEN
971 oe_debug_pub.add( 'J = '||J , 1 ) ;
972 oe_debug_pub.add( 'LINE ID = '||PX_LINE_TBL ( J ) .LINE_ID , 1 ) ;
973 oe_debug_pub.add( 'INDEX IN SORTED TABLE = '||G_PRICE_LINE_ID_TBL(MOD( PX_LINE_TBL ( J ) .LINE_ID,G_BINARY_LIMIT)),1); -- BUG 8631297
974 END IF;
975
976 If G_DEBUG Then
977 for m in oe_price_adj_cur(px_line_tbl(j).line_id) loop
978 oe_debug_pub.add( 'PRICE_ADJUSTMENT_ID='||M.PRICE_ADJUSTMENT_ID , 1 ) ;
979 oe_debug_pub.add( 'LIST_LINE_ID='||M.LIST_LINE_ID , 1 ) ;
980 oe_debug_pub.add( 'PRICING_PHASE_ID='||M.PRICING_PHASE_ID , 1 ) ;
981 oe_debug_pub.add( 'MODIFIER_LEVEL_CODE='||M.MODIFIER_LEVEL_CODE , 1 ) ;
982 end loop;
983 End If;
984
985 --bug 4271297 update line only if open and not cancelled
986 if( nvl(px_line_tbl(j).cancelled_flag,'N') <> 'Y' and nvl(px_line_tbl(j).open_flag,'Y') <> 'N' ) THEN
987 if (l_debug_level > 0) then
988 oe_debug_pub.add( 'Lalit As the line in not closed/cancelled updating LINE ID = '||PX_LINE_TBL ( J ) .LINE_ID , 1 ) ;
989 end if;
990 select count(*) into vcount from oe_price_adjustments where
991 line_id = px_line_tbl(j).line_id and
992 LIST_LINE_TYPE_CODE = 'TSN' and MODIFIER_LEVEL_CODE = 'LINE';
996 elsIf i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE11' Then
993 If vcount = 0 then
994 If i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
995 px_line_tbl(j).payment_term_id := i.Substitution_value_to;
997 --Bug 13573144
998 /*
999 --bug 4190357 added the if - else
1000 IF l_count <> 0 THEN
1001 px_line_tbl(j).shipping_method_code := i.Substitution_value_to;
1002 ELSE
1003 --px_line_tbl(j).shipping_method_code := NULL;
1004 null;
1005 END IF; */
1006 --End of Bug 13573144
1007 px_line_tbl(j).shipping_method_code := i.Substitution_value_to; --Bug 13573144
1008 elsIf i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE10' Then
1009 px_line_tbl(j).freight_terms_code := i.Substitution_value_to;
1010 End If;
1011 px_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
1012 --bug 2795409
1013 px_line_tbl(j).change_reason := 'SYSTEM';
1014 px_line_tbl(j).change_comments := 'REPRICING';
1015 END IF; -- bug 4271297
1016 end if;
1017 j := px_line_tbl.Next(j);
1018
1019 IF l_debug_level > 0 THEN
1020 oe_debug_pub.add( 'J = END OF LOOP '||J , 1 ) ;
1021 END IF;
1022
1023 END LOOP;
1024
1025 -- END LOOP;
1026 ELSE --for line level term substitution, need to update the line
1027 l_mod_line_id := MOD(i.line_id,G_BINARY_LIMIT); -- Bug 8631297
1028 -- Replaced i.line_id with l_mod_line_id in the following 7 lines.
1029 If i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE1' Then
1030 l_modified_from := px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).payment_term_id; --Bug 13573144
1031 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).payment_term_id := i.Substitution_value_to;
1032 elsIf i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE11' Then
1033 --l_old_shipping_method_code := px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).shipping_method_code; --Bug 13573144
1034 l_modified_from := px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).shipping_method_code; --Bug 13573144
1035 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).shipping_method_code := i.Substitution_value_to;
1036 elsIf i.Substitution_Attribute ='QUALIFIER_ATTRIBUTE10' Then
1037 l_modified_from := px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).freight_terms_code; --Bug 13573144
1038 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).freight_terms_code := i.Substitution_value_to;
1039 End If;
1040 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
1041 --bug 2795409
1042 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_reason := 'SYSTEM';
1043 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).change_comments := 'REPRICING';
1044 End If;
1045 --bug 4190357
1046 INSERT INTO OE_PRICE_ADJUSTMENTS
1047 ( PRICE_ADJUSTMENT_ID
1048 , CREATION_DATE
1049 , CREATED_BY
1050 , LAST_UPDATE_DATE
1051 , LAST_UPDATED_BY
1052 , LAST_UPDATE_LOGIN
1053 , PROGRAM_APPLICATION_ID
1054 , PROGRAM_ID
1055 , PROGRAM_UPDATE_DATE
1056 , REQUEST_ID
1057 , HEADER_ID
1058 , DISCOUNT_ID
1059 , DISCOUNT_LINE_ID
1060 , AUTOMATIC_FLAG
1061 , PERCENT
1062 , LINE_ID
1063 , CONTEXT
1064 , ATTRIBUTE1
1065 , ATTRIBUTE2
1066 , ATTRIBUTE3
1067 , ATTRIBUTE4
1068 , ATTRIBUTE5
1069 , ATTRIBUTE6
1070 , ATTRIBUTE7
1071 , ATTRIBUTE8
1072 , ATTRIBUTE9
1073 , ATTRIBUTE10
1074 , ATTRIBUTE11
1075 , ATTRIBUTE12
1076 , ATTRIBUTE13
1077 , ATTRIBUTE14
1078 , ATTRIBUTE15
1079 , ORIG_SYS_DISCOUNT_REF
1080 , LIST_HEADER_ID
1081 , LIST_LINE_ID
1082 , LIST_LINE_TYPE_CODE
1083 , MODIFIER_MECHANISM_TYPE_CODE
1084 , MODIFIED_FROM
1085 , MODIFIED_TO
1086 , UPDATED_FLAG
1087 , UPDATE_ALLOWED
1088 , APPLIED_FLAG
1089 , CHANGE_REASON_CODE
1090 , CHANGE_REASON_TEXT
1091 , operand
1092 , Arithmetic_operator
1093 , COST_ID
1094 , TAX_CODE
1095 , TAX_EXEMPT_FLAG
1096 , TAX_EXEMPT_NUMBER
1097 , TAX_EXEMPT_REASON_CODE
1098 , PARENT_ADJUSTMENT_ID
1099 , INVOICED_FLAG
1100 , ESTIMATED_FLAG
1101 , INC_IN_SALES_PERFORMANCE
1102 , SPLIT_ACTION_CODE
1103 , ADJUSTED_AMOUNT
1104 , PRICING_PHASE_ID
1105 , CHARGE_TYPE_CODE
1106 , CHARGE_SUBTYPE_CODE
1107 , list_line_no
1108 , source_system_code
1109 , benefit_qty
1110 , benefit_uom_code
1111 , print_on_invoice_flag
1112 , expiration_date
1113 , rebate_transaction_type_code
1114 , rebate_transaction_reference
1115 , rebate_payment_system_code
1116 , redeemed_date
1117 , redeemed_flag
1118 , accrual_flag
1119 , range_break_quantity
1120 , accrual_conversion_rate
1121 , pricing_group_sequence
1122 , modifier_level_code
1123 , price_break_type_code
1124 , substitution_attribute
1125 , proration_type_code
1126 , CREDIT_OR_CHARGE_FLAG
1127 , INCLUDE_ON_RETURNS_FLAG
1128 , AC_CONTEXT
1129 , AC_ATTRIBUTE1
1130 , AC_ATTRIBUTE2
1131 , AC_ATTRIBUTE3
1132 , AC_ATTRIBUTE4
1133 , AC_ATTRIBUTE5
1134 , AC_ATTRIBUTE6
1135 , AC_ATTRIBUTE7
1136 , AC_ATTRIBUTE8
1137 , AC_ATTRIBUTE9
1138 , AC_ATTRIBUTE10
1139 , AC_ATTRIBUTE11
1140 , AC_ATTRIBUTE12
1141 , AC_ATTRIBUTE13
1142 , AC_ATTRIBUTE14
1143 , AC_ATTRIBUTE15
1144 , OPERAND_PER_PQTY
1145 , ADJUSTED_AMOUNT_PER_PQTY
1146 , LOCK_CONTROL
1147 )
1148 ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
1149 -- oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
1150 ldets.price_adjustment_id
1151 , sysdate --p_Line_Adj_rec.creation_date
1152 , fnd_global.user_id --p_Line_Adj_rec.created_by
1153 , sysdate --p_Line_Adj_rec.last_update_date
1154 , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
1155 , fnd_global.login_id --p_Line_Adj_rec.last_update_login
1156 , NULL --p_Line_Adj_rec.program_application_id
1157 , NULL --p_Line_Adj_rec.program_id
1158 , NULL --p_Line_Adj_rec.program_update_date
1159 , NULL --p_Line_Adj_rec.request_id
1160 , oe_order_pub.g_hdr.header_id --p_Line_Adj_rec.header_id
1161 , NULL --p_Line_Adj_rec.discount_id
1162 , NULL --p_Line_Adj_rec.discount_line_id
1163 , ldets.automatic_flag
1164 , NULL --p_Line_Adj_rec.percent
1165 , decode(ldets.modifier_level_code,'ORDER',NULL,i.line_id)
1166 , NULL --p_Line_Adj_rec.context
1167 , NULL --p_Line_Adj_rec.attribute1
1168 , NULL --p_Line_Adj_rec.attribute2
1169 , NULL --p_Line_Adj_rec.attribute3
1170 , NULL --p_Line_Adj_rec.attribute4
1171 , NULL --p_Line_Adj_rec.attribute5
1172 , NULL --p_Line_Adj_rec.attribute6
1176 , NULL --p_Line_Adj_rec.attribute10
1173 , NULL --p_Line_Adj_rec.attribute7
1174 , NULL --p_Line_Adj_rec.attribute8
1175 , NULL --p_Line_Adj_rec.attribute9
1177 , NULL --p_Line_Adj_rec.attribute11
1178 , NULL --p_Line_Adj_rec.attribute12
1179 , NULL --p_Line_Adj_rec.attribute13
1180 , NULL --p_Line_Adj_rec.attribute14
1181 , NULL --p_Line_Adj_rec.attribute15
1182 , NULL --p_Line_Adj_rec.orig_sys_discount_ref
1183 , ldets.LIST_HEADER_ID
1184 , ldets.LIST_LINE_ID
1185 , ldets.LIST_LINE_TYPE_CODE
1186 , NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
1187 --, l_old_shipping_method_code --Bug 13573144
1188 , l_modified_from --Bug 13573144
1189 , i.Substitution_value_to
1190 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
1191 , ldets.override_flag
1192 , ldets.APPLIED_FLAG
1193 , NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
1194 , NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
1195 , NULL
1196 , ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
1197 , NULl --p_line_Adj_rec.COST_ID
1198 , NULL --p_line_Adj_rec.TAX_CODE
1199 , NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
1200 , NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
1201 , NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
1202 , NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
1203 , NULL --p_line_Adj_rec.INVOICED_FLAG
1204 , NULL --p_line_Adj_rec.ESTIMATED_FLAG
1205 , NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
1206 , NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
1207 , NULL
1208 , ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
1209 , ldets.CHARGE_TYPE_CODE
1210 , ldets.CHARGE_SUBTYPE_CODE
1211 , ldets.list_line_no
1212 , qh.source_system_code
1213 , ldets.benefit_qty
1214 , ldets.benefit_uom_code
1215 , NULL --p_Line_Adj_rec.print_on_invoice_flag
1216 , ldets.expiration_date
1217 , ldets.rebate_transaction_type_code
1218 , NULL --p_Line_Adj_rec.rebate_transaction_reference
1219 , NULL --p_Line_Adj_rec.rebate_payment_system_code
1220 , NULL --p_Line_Adj_rec.redeemed_date
1221 , NULL --p_Line_Adj_rec.redeemed_flag
1222 , ldets.accrual_flag
1223 , ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
1224 , ldets.accrual_conversion_rate
1225 , ldets.pricing_group_sequence
1226 , ldets.modifier_level_code
1227 , ldets.price_break_type_code
1228 , ldets.substitution_attribute
1229 , ldets.proration_type_code
1230 , NULL --p_Line_Adj_rec.credit_or_charge_flag
1231 , ldets.include_on_returns_flag
1232 , NULL -- p_Line_Adj_rec.ac_context
1233 , NULL -- p_Line_Adj_rec.ac_attribute1
1234 , NULL -- p_Line_Adj_rec.ac_attribute2
1235 , NULL -- p_Line_Adj_rec.ac_attribute3
1236 , NULL -- p_Line_Adj_rec.ac_attribute4
1237 , NULL -- p_Line_Adj_rec.ac_attribute5
1238 , NULL -- p_Line_Adj_rec.ac_attribute6
1239 , NULL -- p_Line_Adj_rec.ac_attribute7
1240 , NULL -- p_Line_Adj_rec.ac_attribute8
1241 , NULL -- p_Line_Adj_rec.ac_attribute9
1242 , NULL -- p_Line_Adj_rec.ac_attribute10
1243 , NULL -- p_Line_Adj_rec.ac_attribute11
1244 , NULL -- p_Line_Adj_rec.ac_attribute12
1245 , NULL -- p_Line_Adj_rec.ac_attribute13
1246 , NULL -- p_Line_Adj_rec.ac_attribute14
1247 , NULL -- p_Line_Adj_rec.ac_attribute15
1248 , NULL
1249 , NULL
1250 , 1
1251 FROM
1252 QP_LDETS_v ldets
1253 , QP_LIST_HEADERS_B QH
1254 WHERE
1255 ldets.line_detail_index = i.line_detail_index
1256 and ldets.list_header_id=qh.list_header_id
1257 AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
1258 );
1259 IF l_debug_level > 0 THEN
1260 oe_debug_pub.add( 'in new_terms cursor');
1261 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
1262 END IF;
1263 --bug 4190357
1264 End Loop;
1265 END Term_Substitution;
1266
1267 Procedure Set_Prg(
1268 px_line_rec IN OUT NOCOPY OE_Order_Pub.line_rec_type
1269 , p_line_id IN QP_PREQ_LINES_TMP.line_id%TYPE
1270 , p_line_index IN QP_PREQ_LINES_TMP.line_index%TYPE
1271 , p_line_quantity IN QP_PREQ_LINES_TMP.line_quantity%TYPE
1272 , p_service_duration IN QP_PREQ_LINES_TMP.service_duration%TYPE DEFAULT NULL --Service ER 16227925
1273 , p_service_period IN QP_PREQ_LINES_TMP.service_period%TYPE DEFAULT NULL --Service ER 16227925
1274 , p_line_uom_code IN QP_PREQ_LINES_TMP.line_uom_code%TYPE
1275 , p_unit_price IN QP_PREQ_LINES_TMP.unit_price%TYPE
1276 , p_adjusted_unit_price IN QP_PREQ_LINES_TMP.adjusted_unit_price%TYPE
1277 , p_line_unit_price IN QP_PREQ_LINES_TMP.line_unit_price%TYPE
1278 , p_order_uom_selling_price IN QP_PREQ_LINES_TMP.order_uom_selling_price%TYPE
1279 , p_line_category IN QP_PREQ_LINES_TMP.line_category%TYPE
1280 , p_priced_quantity IN QP_PREQ_LINES_TMP.priced_quantity%TYPE
1281 , p_price_list_header_id IN QP_PREQ_LINES_TMP.price_list_header_id%TYPE
1282 , p_percent_price IN QP_PREQ_LINES_TMP.percent_price%TYPE
1283 , p_priced_uom_code IN QP_PREQ_LINES_TMP.priced_uom_code%TYPE
1284 , p_price_request_code IN QP_PREQ_LINES_TMP.price_request_code%TYPE
1285 )
1286 IS
1287 l_line_rec Oe_Order_Pub.line_rec_type := px_line_rec;
1288 l_org_id Number:= OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
1289 l_tot_qty OE_ORDER_LINES_ALL.ordered_quantity%TYPE;
1290 l_tot_price_qty OE_ORDER_LINES_ALL.pricing_quantity%TYPE;
1291 l_pricing_event varchar2(30);
1292 l_return_status varchar2(1);
1293 --
1294 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1295 --
1299 Begin
1296 l_org ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
1297 l_modifier QP_LIST_HEADERS_TL.NAME%TYPE;
1298 l_list_line_no QP_LIST_LINES.LIST_LINE_NO%TYPE;
1300 IF l_debug_level > 0 THEN
1301 oe_debug_pub.add( 'SET PRG'||L_LINE_REC.OPERATION||' qty'||p_line_quantity ) ;
1302 END IF;
1303 if p_line_quantity < 0
1304 and
1305 ( p_line_category is null or
1306 p_line_category = 'ORDER'
1307 ) then
1308 l_line_rec.line_category_code := 'RETURN' ;
1309 l_line_rec.return_reason_code := 'RETURN' ;
1310 elsif p_line_quantity < 0 and p_line_category = 'RETURN' then
1311 l_line_rec.line_category_code := 'ORDER';
1312 elsif p_line_category is not null then
1313 l_line_rec.line_category_code := p_line_category;
1314 else
1315 l_line_rec.line_category_code := 'ORDER';
1316 end if;
1317 --for bug 2412868 end
1318 -- uom begin
1319 l_line_rec.unit_selling_price_per_pqty := p_adjusted_unit_price ;
1320 l_line_rec.unit_list_price_per_pqty := p_unit_price ;
1321 --for bug 2412868 begin
1322 if l_line_rec.unit_selling_price_per_pqty < 0 then
1323 l_line_rec.unit_selling_price_per_pqty :=
1324 abs(p_adjusted_unit_price ) ;
1325 l_line_rec.unit_list_price_per_pqty :=
1326 abs(p_unit_price );
1327 end if;
1328 --for bug 2412868 end
1329 l_line_rec.unit_list_percent := p_percent_price ;
1330 if nvl(p_percent_price,0) <> 0 then
1331 l_line_rec.unit_selling_percent :=
1332 ( l_line_rec.unit_selling_price_per_pqty * l_line_rec.unit_list_percent)/
1333 p_percent_price ;
1334 end if;
1335 /*
1336 IF (l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
1337 begin
1338 select nvl(sum(ordered_quantity),0),nvl(sum(pricing_quantity),0)
1339 into l_tot_qty,l_tot_price_qty
1340 from oe_order_lines
1341 where split_from_line_id = l_line_rec.line_id
1342 and header_id = l_line_rec.header_id;
1343 exception
1344 when others then
1345 null;
1346 end;
1347 l_line_rec.pricing_quantity :=
1348 p_priced_quantity - nvl(l_tot_price_qty,0);
1349 l_line_rec.ordered_quantity :=
1350 p_line_quantity - nvl(l_tot_qty,0);
1351 ELSE
1352 */
1353
1354 l_line_rec.pricing_quantity := p_priced_quantity ;
1355 l_line_rec.Ordered_Quantity := p_line_quantity ;
1356 --END IF;
1357 -- Service ER 16227925
1358 IF p_service_duration IS NOT NULL THEN
1359 l_line_rec.service_duration :=p_service_duration;
1360 l_line_rec.service_end_date := NULL;--bug 16780881
1361 END IF;
1362 -- Service ER 16227925
1363 IF p_service_period IS NOT NULL THEN
1364 l_line_rec.service_period :=p_service_period;
1365 END IF;
1366
1367
1368 --for bug 2412868 begin
1369 if l_line_rec.pricing_quantity < 0 and l_line_rec.Ordered_quantity < 0
1370 then
1371 l_line_rec.pricing_quantity := abs(l_line_rec.pricing_quantity);
1372 l_line_rec.ordered_quantity := abs(l_line_rec.ordered_quantity);
1373 end if;
1374
1375 --for bug 2412868 end
1376 l_line_rec.pricing_quantity_uom := p_priced_uom_code ;
1377 l_line_rec.unit_list_price := nvl(p_line_unit_price,
1378 l_line_rec.unit_list_price_per_pqty
1379 * l_line_rec.pricing_quantity
1380 / nvl(l_line_rec.ordered_quantity, 1));
1381 l_line_rec.unit_selling_price := nvl(p_order_uom_selling_price,
1382 l_line_rec.unit_selling_price_per_pqty
1383 * l_line_rec.pricing_quantity
1384 / nvl(l_line_rec.ordered_quantity,1));
1385 l_line_rec.price_request_code := p_price_request_code;
1386
1387 --Why hardcode to INT ?
1388 l_line_rec.item_identifier_type := 'INT';
1389
1390 IF l_debug_level > 0 THEN
1391 oe_debug_pub.add( 'RLANKA: SETTING ORDERED QUANTITY UOM' ) ;
1392 END IF;
1393 IF l_debug_level > 0 THEN
1394 oe_debug_pub.add( 'UOM = ' || P_LINE_UOM_CODE ) ;
1395 END IF;
1396 l_line_rec.order_quantity_uom := p_line_uom_code;
1397
1398 --end Bug 1805134
1399
1400 l_line_rec.price_list_id := p_price_list_header_id;
1401 IF l_debug_level > 0 THEN
1402 oe_debug_pub.add( 'PRG_LINES'||L_LINE_REC.OPERATION ) ;
1403 END IF;
1404
1405 G_STMT_NO := 'Process_Other_Item_Line#140';
1406 Begin
1407 SELECT attr.value_from
1408 INTO l_line_rec.inventory_item_id
1409 FROM qp_preq_line_attrs_tmp attr
1410 WHERE attr.context = 'ITEM'
1411 AND attr.attribute =
1412 'PRICING_ATTRIBUTE1'
1413 AND attr.line_index = p_line_index;
1414 Exception When no_data_found Then
1415 Null;
1416 End;
1417
1418 Begin
1419
1420 SELECT concatenated_segments
1421 INTO l_line_rec.ordered_item
1422 FROM mtl_system_items_kfv
1426 FND_MESSAGE.SET_NAME('ONT', 'ONT_PRG_INVALID_MASTER_ORG');
1423 WHERE inventory_item_id = l_line_rec.inventory_item_id
1424 AND organization_id = l_org_id;
1425 Exception when no_data_found then
1427 SELECT QH.NAME, LDET.LIST_LINE_NO
1428 INTO l_modifier
1429 , l_list_line_no
1430 FROM QP_PREQ_RLTD_LINES_TMP RLTD
1431 ,QP_LDETS_V LDET
1432 , QP_LIST_HEADERS_TL QH
1433 WHERE RLTD.RELATED_LINE_INDEX = p_line_index
1434 AND RLTD.RELATIONSHIP_TYPE_CODE = 'GENERATED_LINE'
1435 AND RLTD.LINE_DETAIL_INDEX = LDET.LINE_DETAIL_INDEX
1436 AND LDET.LIST_HEADER_ID = QH.LIST_HEADER_ID AND ROWNUM=1;
1437
1438
1439 FND_MESSAGE.SET_TOKEN('MODIFIER', l_modifier);
1440 FND_MESSAGE.SET_TOKEN('LIST_LINE_NO', l_list_line_no);
1441 FND_MESSAGE.SET_TOKEN('ITEM', l_line_rec.inventory_item_id);
1442 BEGIN
1443 SELECT ORGANIZATION_NAME
1444 INTO l_org
1445 FROM ORG_ORGANIZATION_DEFINITIONS
1446 WHERE ORGANIZATION_ID = l_org_id;
1447 EXCEPTION WHEN OTHERS THEN
1448 l_org := l_org_id;
1449 END;
1450 FND_MESSAGE.SET_TOKEN('ORG', l_org);
1451 OE_MSG_PUB.ADD;
1452 l_line_rec.operation := OE_GLOBALS.G_OPR_NONE;
1453 QP_UTIL_PUB.Update_Lines('MAKE_STATUS_INVALID',l_line_rec.line_id,
1454 NULL,NULL);
1455 End;
1456
1457 /*
1458 Fix for Bug 1729372 : Change calculate_price_flag to 'R'
1459 so that charges can be applied to the new line. This will be
1460 handled in OEXULINB.pls
1461 */
1462
1463 IF l_line_rec.operation = OE_GLOBALS.G_OPR_CREATE
1464 THEN
1465 l_line_rec.calculate_price_flag := 'R';
1466 ELSIF (l_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
1467 if nvl(l_line_rec.booked_flag, 'N') = 'Y' then
1468 l_pricing_event := 'BATCH,BOOK';
1469 else
1470 l_pricing_event := 'BATCH';
1471 end if;
1472
1473 l_line_rec.ordered_quantity2 := null; -- 8459311
1474
1475 OE_delayed_requests_Pvt.log_request(
1476 p_entity_code =>OE_GLOBALS.G_ENTITY_ALL,
1477 p_entity_id => l_line_rec.line_Id,
1478 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1479 p_requesting_entity_id => l_line_rec.line_Id,
1480 p_request_unique_key1 => l_pricing_event,
1481 p_param1 => l_line_rec.header_id,
1482 p_param2 => l_pricing_event,
1483 p_request_type => OE_GLOBALS.G_PRICE_LINE,
1484 x_return_status => l_return_status);
1485 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1486 IF l_debug_level > 0 THEN
1487 oe_debug_pub.add( 'unexp error logging batch event for updated free goods line', 3) ;
1488 oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.set_prg', 3);
1489 END IF;
1490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1491 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1492 IF l_debug_level > 0 THEN
1493 oe_debug_pub.add( 'error logging batch event for update free goods line',3);
1494 oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.set_prg' , 1 ) ;
1495 END IF;
1496 RAISE FND_API.G_EXC_ERROR;
1497 END IF;
1498 -- sol_ord_er #16014165
1499 /* The current Line is PRG line, hence Order has to be logged and check for FPOM should not be used */
1500 oe_delayed_requests_pvt.log_request(
1501 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1502 p_entity_id => l_line_rec.line_Id,
1503 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1504 p_requesting_entity_id => l_line_rec.line_Id,
1505 p_request_type => OE_GLOBALS.G_SERVICE_FIRST_PERIOD_BILL,
1506 p_request_unique_key1 => 'ORDER',
1507 p_param1 => l_line_rec.header_id,
1508 x_return_status => l_return_status);
1509 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1510 IF l_debug_level > 0 THEN
1511 oe_debug_pub.add( 'unexp error logging G_SERVICE_FIRST_PERIOD_BILL', 3) ;
1512 oe_debug_pub.add( '(1) EXITING OE_ADV_PRICE_PVT.set_prg', 3);
1513 END IF;
1514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1515 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1516 IF l_debug_level > 0 THEN
1517 oe_debug_pub.add( 'error logging G_SERVICE_FIRST_PERIOD_BILL',3);
1518 oe_debug_pub.add( '(2) EXITING OE_ADV_PRICE_PVT.set_prg' , 1 ) ;
1519 END IF;
1520 RAISE FND_API.G_EXC_ERROR;
1521 END IF;
1522 -- sol_ord_er #16014165 end
1523 END IF;
1527 IF l_debug_level > 0 THEN
1524
1525 px_line_rec := l_line_rec;
1526 exception when others then
1528 oe_debug_pub.add( 'ERROR IN SET_PRG'||SQLERRM ) ;
1529 END IF;
1530 raise fnd_api.g_exc_error;
1531 end Set_Prg;
1532
1533 Procedure Delete_Prg(
1534 px_line_rec IN OUT NOCOPY OE_Order_Pub.line_rec_type
1535 )
1536 IS
1537 l_line_rec OE_Order_Pub.line_rec_type := px_line_rec;
1538 l_pricing_event varchar2(30);
1539 l_return_status varchar2(1);
1540 --
1541 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1542 --
1543 Begin
1544 IF (nvl(l_line_rec.booked_flag, 'N') = 'N') Then
1545 IF l_debug_level > 0 THEN
1546 oe_debug_pub.add( 'DELETE UNBOOKED ORDER' ) ;
1547 END IF;
1548 l_line_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1549 ELSE
1550 l_line_rec.change_reason := 'SYSTEM';
1551 l_line_rec.change_comments := 'REPRICING';
1552 IF (l_line_rec.shipped_quantity IS NULL) THEN
1553 IF l_debug_level > 0 THEN
1554 oe_debug_pub.add( 'CANCEL BOOKED ORDER , LINE NOT SHIPPED' ) ;
1555 END IF;
1556 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1557 BEGIN
1558 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110510' THEN
1559 UPDATE QP_PREQ_LINES_TMP
1560 SET PROCESS_STATUS='NOT_VALID'
1561 WHERE LINE_ID = l_line_rec.line_id;
1562 ELSE
1563 QP_UTIL_PUB.Update_Lines('MAKE_STATUS_INVALID',l_line_rec.line_id,
1564 NULL,NULL);
1565 END IF;
1566 EXCEPTION
1567 WHEN OTHERS THEN
1568 NULL;
1569 END;
1570 l_line_rec.ordered_quantity := 0;
1571 l_line_rec.pricing_quantity := 0;
1572 ELSE
1573 IF l_debug_level > 0 THEN
1574 oe_debug_pub.add( 'REPRICE BOOKED ORDER , SHIPPED LINE' ) ;
1575 END IF;
1576 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1577 l_line_rec.calculate_price_flag := 'Y';
1578 IF l_debug_level > 0 THEN
1579 oe_debug_pub.add( 'LOGGING DELAYED REQUEST TO PRICE LINE' ) ;
1580 END IF;
1581
1582 l_pricing_event := 'BATCH,BOOK';
1583 OE_delayed_requests_Pvt.log_request(
1584 p_entity_code =>OE_GLOBALS.G_ENTITY_ALL,
1585 p_entity_id => l_line_rec.line_Id,
1586 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1587 p_requesting_entity_id => l_line_rec.line_Id,
1588 p_request_unique_key1 => l_pricing_event,
1589 p_param1 => l_line_rec.header_id,
1590 p_param2 => l_pricing_event,
1591 p_request_type => OE_GLOBALS.G_PRICE_LINE,
1592 x_return_status => l_return_status);
1593 -- sol_ord_er #16014165
1594 /* The current Line is PRG line, hence Order has to be logged and check for FPOM should not be used */
1595 oe_delayed_requests_pvt.log_request(
1596 p_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1597 p_entity_id => l_line_rec.line_Id,
1598 p_requesting_entity_code => OE_GLOBALS.G_ENTITY_ALL,
1599 p_requesting_entity_id => l_line_rec.line_Id,
1600 p_request_type => OE_GLOBALS.G_SERVICE_FIRST_PERIOD_BILL,
1601 p_request_unique_key1 => 'ORDER',
1602 p_param1 => l_line_rec.header_id,
1603 x_return_status => l_return_status);
1604 -- sol_ord_er #16014165 end
1605
1606 end if;
1607 end if;
1608 px_line_rec := l_line_rec;
1609 Exception
1610 When Others then
1611 Raise FND_API.G_EXC_ERROR;
1612 End Delete_Prg;
1613
1614 procedure Process_PRG(px_line_Tbl in out nocopy oe_order_pub.line_tbl_type
1615 , px_old_line_tbl in out nocopy oe_order_pub.line_tbl_type
1616 , px_price_line_id_tbl IN OUT NOCOPY Oe_Order_Adj_Pvt.Index_Tbl_Type
1617 )
1618 IS
1619 cursor prg_lines IS
1620 select line_id
1621 , line_index
1622 , line_quantity
1623 , line_uom_code
1624 , unit_price
1625 , adjusted_unit_price
1626 , line_unit_price
1627 , order_uom_selling_price
1628 , process_status
1629 , line_category
1630 , priced_quantity
1631 , price_list_header_id
1632 , percent_price
1633 , priced_uom_code
1634 , price_request_code
1635 , service_duration --Service ER 16227925
1636 , service_period --Service ER 16227925
1637 From qp_preq_lines_tmp
1638 where
1639 --processed_code = 'ENGINE';
1640 process_status IN (QP_PREQ_PUB.G_STATUS_NEW
1641 , QP_PREQ_PUB.G_STATUS_UPDATED
1642 , QP_PREQ_PUB.G_STATUS_DELETED
1643 , 'FREEGOOD'
1644 );
1645 l_line_rec oe_order_pub.line_rec_type;
1646 l_buy_line_rec oe_order_pub.line_rec_type;
1647 l_parent_line_index pls_integer;
1648 l_parent_line_id number ; -- pls_integer; bug 8631297
1649 l_return_status varchar2(1);
1650 E_CLOSED_LINE Exception;
1651 --
1652 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1653 --
1654 l_tot_qty OE_ORDER_LINES_ALL.ordered_quantity%TYPE ;
1655 l_tot_price_qty OE_ORDER_LINES_ALL.pricing_quantity%TYPE ;
1656 l_ordered_quantity OE_ORDER_LINES_ALL.ordered_quantity%TYPE;
1657 l_pricing_quantity OE_ORDER_LINES_ALL.pricing_quantity%TYPE;
1658 l_mod_line_id NUMBER;
1659
1660 --service er variable declarion start 16227925
1661 l_inventory_item_id NUMBER;
1662 l_org_id Number:= OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
1663 l_contract_item_type_code VARCHAR2(100);
1664 l_service_item_flag VARCHAR2(1);
1665 --service er variable declarion end 16227925
1666
1667 Begin
1668 IF l_debug_level > 0 THEN
1672 For i in prg_lines Loop
1669 oe_debug_pub.add( 'ENTERING PRG_LINES' ) ;
1670 END IF;
1671
1673 -- 8631297
1674
1675 IF i.line_id IS NOT NULL THEN
1676 l_mod_line_id := MOD(i.line_id,G_BINARY_LIMIT);
1677 END IF;
1678
1679 IF l_debug_level > 0 THEN
1680 oe_debug_pub.add( ' mod line id :'|| l_mod_line_id,1);
1681 oe_debug_pub.add( 'PRG LINE:'||I.LINE_ID||' PROCESS STATUS: '||I.PROCESS_STATUS ) ;
1682 END IF;
1683
1684 -- 8631297
1685
1686 If (i.Process_status = QP_PREQ_PUB.G_STATUS_DELETED) THEN
1687 l_line_rec := px_line_tbl(px_price_line_id_tbl(l_mod_line_id));
1688 Delete_Prg(l_line_rec);
1689 px_line_tbl(px_price_line_id_tbl(l_mod_line_id)) := l_line_rec;
1690 Elsif (i.Process_Status = 'FREEGOOD'
1691 and nvl(px_line_tbl(px_price_line_id_tbl(l_mod_line_id)).cancelled_quantity, 0) = 0) THEN
1692 Null;
1693 Elsif (i.Process_Status = QP_PREQ_PUB.G_STATUS_UPDATED
1694 OR i.Process_Status = 'FREEGOOD') THEN
1695 l_line_rec := px_line_tbl(px_price_line_id_tbl(l_mod_line_id));
1696 If l_line_rec.open_flag = 'N' Then
1697 Raise E_Closed_Line;
1698 End If;
1699
1700 begin
1701 select nvl(sum(ordered_quantity + nvl(cancelled_quantity,0)),0),nvl(sum(pricing_quantity + nvl(cancelled_quantity,0)),0)
1702 into l_tot_qty,l_tot_price_qty
1703 from oe_order_lines
1704 where split_from_line_id = l_line_rec.line_id
1705 and header_id = l_line_rec.header_id;
1706 exception
1707 when others then
1708 l_tot_qty := 0;
1709 l_tot_price_qty := 0;
1710 null;
1711 end;
1712 l_pricing_quantity :=
1713 i.priced_quantity - nvl(l_tot_price_qty,0);
1714 l_ordered_quantity :=
1715 i.line_quantity - nvl(l_tot_qty,0);
1716
1717
1718 IF l_debug_level > 0 THEN
1719 oe_debug_pub.add('priced quantity:'||l_pricing_quantity);
1720 oe_debug_pub.add( 'ordered_quantity:Cancelled_quantity'||l_ordered_quantity||':'||l_line_rec.cancelled_quantity ) ;
1721 oe_debug_pub.ADD(' l_line_rec.service_reference_type_code :'||l_line_rec.service_reference_type_code,1); --bug 16317034
1722 oe_debug_pub.ADD(' l_line_rec.service_reference_line_id :'||l_line_rec.service_reference_line_id,1); --bug 16317034
1723 END IF;
1724 --added condition to stop deletion of service lines after splitting for bug 16317034
1725 if (l_ordered_quantity <= nvl(l_line_rec.cancelled_quantity, 0)) AND l_line_rec.service_reference_type_code <> 'ORDER'
1726 AND (l_line_rec.service_reference_line_id IS NULL OR l_line_rec.service_reference_line_id = FND_API.G_MISS_NUM) Then
1727 oe_line_util.update_adjustment_flags(l_line_rec, l_line_rec);
1728 Delete_Prg(l_line_rec);
1729 else
1730 l_ordered_quantity := l_ordered_quantity - nvl(l_line_rec.cancelled_quantity, 0);
1731 l_pricing_quantity := l_pricing_quantity - nvl(l_line_rec.cancelled_quantity, 0);
1732 IF nvl(l_line_rec.cancelled_quantity, 0) > 0 THEN
1733 BEGIN
1734 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110510' THEN
1735 UPDATE QP_PREQ_LINES_TMP
1736 SET PRICED_QUANTITY = l_pricing_quantity
1737 where line_id = l_line_rec.line_id;
1738 ELSE
1739 QP_UTIL_PUB.Update_Lines('UPDATE_PRICED_QUANTITY',l_line_rec.line_id,null,l_pricing_quantity);
1740 END IF;
1741 EXCEPTION
1742 WHEN OTHERS THEN
1743 NULL;
1744 END;
1745 END IF;
1746 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1747 l_line_rec.change_reason := 'SYSTEM';
1748 l_line_rec.change_comments := 'REPRICING';
1749
1750 --bug 16317034 start
1751 IF l_line_rec.service_reference_type_code = 'ORDER' AND l_line_rec.service_reference_line_id IS NOT NULL AND l_line_rec.service_reference_line_id <>FND_API.G_MISS_NUM THEN
1752 l_ordered_quantity := i.line_quantity;
1753 l_pricing_quantity := i.priced_quantity;
1754 oe_debug_pub.ADD('l_ordered_quantity :'||l_ordered_quantity,1);
1755 oe_debug_pub.ADD('l_pricing_quantity :'||l_pricing_quantity,1);
1756 END IF;
1757 --bug 16317034 end
1758
1759 Set_PRG(
1760 l_line_rec
1761 , i.line_id
1762 , i.line_index
1763 , l_ordered_quantity
1764 , i.service_duration --Service ER 16227925
1765 , i.service_period --Service ER 16227925
1766 , i.line_uom_code
1767 , i.unit_price
1768 , i.adjusted_unit_price
1769 , i.line_unit_price
1770 , i.order_uom_selling_price
1771 , i.line_category
1772 , l_pricing_quantity
1773 , i.price_list_header_id
1774 , i.percent_price
1775 , i.priced_uom_code
1776 , i.price_request_code
1777 );
1778 End If; -- cancelled_quantity larger than ordered_quantity?
1779 IF (l_line_rec.operation <> OE_GLOBALS.G_OPR_NONE) THEN
1780 px_line_tbl(px_price_line_id_tbl(l_mod_line_id)) := l_line_rec; -- Bug 8631297
1781 END IF;
1782 Elsif (i.Process_Status = QP_PREQ_PUB.G_STATUS_NEW) THEN
1783 l_line_rec:=OE_ORDER_PUB.G_MISS_LINE_REC;
1784 l_line_rec.cancelled_quantity := 0;
1785 l_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
1786 l_line_rec.Header_id := oe_order_cache.g_header_rec.header_id;
1787 l_line_rec.line_id := OE_DEFAULT_LINE.get_line;
1788 --for Bug 3350425. To Prevent Blanket feilds being defaulting from Header.
1789 l_line_rec.blanket_number:=NULL;
1790 l_line_rec.blanket_line_number:=NULL;
1794 --bug 4234500
1791 IF l_debug_level > 0 THEN
1792 oe_debug_pub.add( 'NEW LINE ID'||L_LINE_REC.LINE_ID ) ;
1793 END IF;
1795 l_line_rec.change_reason := 'SYSTEM';
1796 l_line_rec.change_comments := 'REPRICING';
1797 --bug 4234500
1798
1799 BEGIN
1800 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110510' THEN
1801 UPDATE QP_PREQ_LINES_TMP
1802 SET LINE_ID = l_line_rec.line_id
1803 where line_index = i.line_index;
1804 ELSE
1805 QP_UTIL_PUB.Update_Lines('UPDATE_LINE_ID', l_line_rec.line_id,
1806 i.line_index, null);
1807 END IF;
1808 END;
1809
1810 BEGIN
1811 SELECT rltd.line_index
1812 INTO l_parent_line_index
1813 FROM qp_preq_rltd_lines_tmp rltd
1814 WHERE rltd.related_line_index = i.line_index
1815 AND relationship_type_code = 'GENERATED_LINE' and rownum=1;
1816
1817 SELECT line_id
1818 INTO l_parent_line_id
1819 FROM qp_preq_lines_tmp
1820 WHERE line_index = l_parent_line_index;
1821
1822
1823 l_buy_line_rec := px_line_tbl(px_price_line_id_tbl(MOD(l_parent_line_id, G_BINARY_LIMIT))); --BUG 8631297
1824 If l_buy_line_rec.line_category_code = 'RETURN' and l_buy_line_rec.return_reason_code is Not Null Then
1825 l_line_rec.return_reason_code := l_buy_line_rec.return_reason_code;
1826 End If;
1827
1828 -- service ER 16227925 START -- Service Project
1829 oe_debug_pub.add('buy item type= '||l_buy_line_rec.item_type_code,1);
1830
1831 l_line_rec.service_duration := i.service_duration;
1832 l_line_rec.service_period := i.service_period;
1833 oe_debug_pub.add(' service_duration = '||l_line_rec.service_duration,1);
1834 oe_debug_pub.add(' service_period = '||l_line_rec.service_period,1);
1835
1836 -- getting the free good item id
1837
1838 SELECT attr.value_from
1839 INTO l_inventory_item_id
1840 FROM qp_preq_line_attrs_tmp attr
1841 WHERE attr.context = 'ITEM'
1842 AND attr.attribute = 'PRICING_ATTRIBUTE1'
1843 AND attr.line_index = i.line_index;
1844
1845
1846 oe_debug_pub.ADD(' l_line_rec.inventory_item_id:- '||l_inventory_item_id,1);
1847 oe_debug_pub.ADD(' l_line_rec.ship_from_org_id:- '||l_org_id,1);
1848
1849 BEGIN
1850 -- checking if it is service item which needs to be referenced
1851 SELECT SERVICE_ITEM_FLAG,CONTRACT_ITEM_TYPE_CODE
1852 INTO l_service_item_flag,l_contract_item_type_code
1853 FROM MTL_SYSTEM_ITEMS
1854 WHERE INVENTORY_ITEM_ID = l_inventory_item_id
1855 AND ORGANIZATION_ID = l_org_id;
1856 oe_debug_pub.ADD(' l_service_item_flag:- '||l_service_item_flag,1);
1857 oe_debug_pub.ADD(' l_contract_item_type_code:- '||l_contract_item_type_code,1);
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860 oe_debug_pub.ADD('Error getting SERVICE_ITEM_FLAG and CONTRACT_ITEM_TYPE_CODE',1);
1861 l_service_item_flag := 'N';
1862 l_contract_item_type_code := NULL;
1863 END;
1864
1865
1866 IF Nvl(l_service_item_flag,'N') = 'Y' AND l_contract_item_type_code = 'SERVICE' THEN
1867 L_LINE_REC.item_type_code:='SERVICE';
1868 l_line_rec.service_reference_line_id := l_buy_line_rec.line_id;
1869 oe_debug_pub.add(' service_reference_line_id = '||l_buy_line_rec.line_id,1);
1870 IF l_line_rec.service_reference_line_id IS NOT NULL THEN
1871 l_line_rec.service_reference_type_code := 'ORDER';
1872 END IF;
1873 ELSE
1874 l_line_rec.subscription_enable_flag := 'N';--bug 16838767
1875 END IF;
1876
1877 -- Service ER 16227925 END
1878
1879 -- put into the same ship set
1880 IF l_debug_level > 0 THEN
1881 oe_debug_pub.add( 'PUTTING TO SHIP SET'||L_BUY_LINE_REC.SHIP_SET_ID ) ;
1882 END IF;
1883 l_line_rec.ship_set_id := l_buy_line_rec.ship_set_id;
1884 --bug 7000338/7002146
1885 IF (l_buy_line_rec.ship_set_id is NOT NULL AND l_buy_line_rec.ship_set_id <> FND_API.G_MISS_NUM) THEN
1886 select set_name INTO l_line_rec.ship_set from oe_sets where set_id= l_buy_line_rec.ship_set_id ;
1887 oe_debug_pub.add( 'PUTTING TO SHIP SET name'||L_LINE_REC.SHIP_SET ) ;
1888 END IF;
1889
1890 EXCEPTION WHEN NO_DATA_FOUND THEN
1891 NULL;
1892 END;
1893
1894 Set_PRG(
1895 l_line_rec
1896 , i.line_id
1897 , i.line_index
1898 , i.line_quantity
1899 , NULL --Service ER 16227925
1900 , NULL --Service ER 16227925
1901 , i.line_uom_code
1902 , i.unit_price
1903 , i.adjusted_unit_price
1904 , i.line_unit_price
1905 , i.order_uom_selling_price
1906 , i.line_category
1907 , i.priced_quantity
1908 , i.price_list_header_id
1909 , i.percent_price
1910 , i.priced_uom_code
1911 , i.price_request_code
1912 );
1913 IF (l_line_rec.operation <> OE_GLOBALS.G_OPR_NONE) THEN
1914 -- Display the PRG Item
1915 FND_MESSAGE.SET_NAME('ONT','ONT_CREATED_NEW_LINE');
1916 FND_MESSAGE.SET_TOKEN('ITEM',nvl(l_line_rec.ordered_item,l_line_rec.inventory_item_id));
1917 --bug 2412868 begin
1918 if l_line_rec.line_category_code = 'RETURN' then
1919 FND_MESSAGE.SET_TOKEN('QUANTITY',(-1) * l_line_rec.Ordered_quantity);
1920 else
1921 --bug 2412868 end
1922 FND_MESSAGE.SET_TOKEN('QUANTITY',l_line_rec.Ordered_quantity);
1923 end if;
1924 OE_MSG_PUB.Add('N');
1928 End If;
1925 px_line_tbl(px_line_tbl.last+1) := l_line_rec;
1926 px_price_line_id_tbl(MOD(l_line_rec.line_id, G_BINARY_LIMIT)) := px_line_tbl.last; -- BUG 8631297
1927 END IF;
1929 End Loop;
1930
1931 EXCEPTION
1932 WHEN E_CLOSED_LINE THEN
1933 NULL;
1934 WHEN OTHERS THEN
1935 IF l_debug_level > 0 THEN
1936 oe_debug_pub.add( 'ERROR IN PROCESS_PRG'||SQLERRM ) ;
1937 END IF;
1938 RAISE FND_API.G_EXC_ERROR;
1939 End Process_PRG;
1940
1941
1942 Procedure Promotion_Put_Hold(
1943 p_header_id Number
1944 ,p_line_id Number
1945 )
1946 is
1947 l_hold_source_rec OE_Holds_Pvt.hold_source_rec_type;
1948 l_hold_release_rec OE_Holds_Pvt.Hold_Release_REC_Type;
1949 l_return_status varchar2(30);
1950 l_x_msg_count number;
1951 l_x_msg_data Varchar2(2000);
1952 l_x_result_out Varchar2(30);
1953 l_list_name varchar2(240);
1954 l_operand number;
1955 l_msg_text Varchar2(200);
1956
1957 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1958
1959 Begin
1960
1961 IF l_debug_level > 0 THEN
1962 oe_debug_pub.add('PROMOTIONS - start of procedure Promotion_Put_Hold ');
1963 END IF;
1964
1965 -- use the seeded hold_id
1966 IF (p_line_id IS NULL) THEN
1967 IF l_debug_level > 0 THEN
1968 oe_debug_pub.add('putting header '||p_header_id||' on hold',3);
1969 END IF;
1970
1971 l_hold_source_rec.hold_id := G_SEEDED_PROM_ORDER_HOLD_ID;
1972 ELSE
1973 IF l_debug_level > 0 THEN
1974 oe_debug_pub.add('putting line '||p_line_id||' on hold',3);
1975 END IF;
1976 l_hold_source_rec.hold_id := G_SEEDED_PROM_LINE_HOLD_ID;
1977 END IF;
1978
1979 IF l_debug_level > 0 THEN
1980 --oe_debug_pub.add('PAL PROMOTIONS - after select in procedure Promotion_Put_Hold ');
1981 --oe_debug_pub.add('PAL PROMOTIONS - hold id is '|| l_hold_source_rec.hold_id,2);
1982 --oe_debug_pub.add(' PROMOTIONS - header_id is '|| p_header_id,2);
1983 --oe_debug_pub.add('PAL PROMOTIONS - line_id is '|| p_line_id,2);
1984 null;
1985 END IF;
1986
1987 l_hold_source_rec.hold_entity_id := p_header_id;
1988 l_hold_source_rec.header_id := p_header_id;
1989
1990 l_hold_source_rec.line_id := p_line_id;
1991 l_hold_source_rec.Hold_Entity_code := 'O';
1992
1993 -- check if line already on PROMOTION hold, place hold if not
1994
1995 OE_Holds_Pub.Check_Holds(
1996 p_api_version => 1.0
1997 ,p_header_id => p_header_id
1998 ,p_line_id => p_line_id
1999 ,p_hold_id => l_hold_source_rec.Hold_id
2000 ,x_return_status => l_return_status
2001 ,x_msg_count => l_x_msg_count
2002 ,x_msg_data => l_x_msg_data
2003 ,x_result_out => l_x_result_out
2004 );
2005
2006 IF l_debug_level > 0 THEN
2007 --oe_debug_pub.add('PAL PROMOTIONS - hold_entity_code is '|| l_hold_source_rec.Hold_Entity_code||l_x_result_out,2);
2008 null;
2009 end if;
2010
2011 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
2012 RAISE FND_API.G_EXC_ERROR;
2013 END IF;
2014 If l_x_result_out = FND_API.G_FALSE then
2015 IF l_debug_level > 0 THEN
2016 oe_debug_pub.add('PAL PROMOTIONS - apply holds in procedure Promotion_Put_Hold ');
2017 oe_debug_pub.add('hold line with header_id:'||p_header_id||' line_id: '||p_line_id,1);
2018 END IF;
2019 OE_HOLDS_PUB.Apply_Holds(
2020 p_api_version => 1.0
2021 ,p_hold_source_rec => l_hold_source_rec
2022 ,x_return_status => l_return_status
2023 ,x_msg_count => l_x_msg_count
2024 ,x_msg_data => l_x_msg_data
2025
2026 );
2027
2028 If l_return_status = FND_API.g_ret_sts_success then
2029 IF (p_line_id IS NULL) THEN
2030 FND_MESSAGE.SET_NAME('ONT','ONT_PROMO_HOLD_APPLIED');
2031 ELSE
2032 FND_MESSAGE.SET_NAME('ONT', 'ONT_LINE_PROMO_HOLD_APPLIED');
2033 END IF;
2034 OE_MSG_PUB.Add;
2035 Else
2036 IF l_debug_level > 0 THEN
2037 oe_debug_pub.add('error applying hold',3);
2038 END IF;
2039 RAISE FND_API.G_EXC_ERROR;
2040 End If;
2041 End If; /* check hold */
2042
2043 IF l_debug_level > 0 THEN
2044 -- oe_debug_pub.add('PAL PROMOTIONS - end of procedure Promotion_Put_Hold ');
2045 null;
2046 END IF;
2047 end Promotion_Put_Hold;
2048
2049
2050 Procedure Process_Limits IS
2051
2052 Cursor Hold_Lines IS
2053 SELECT line_index, line_id, line_Type_code, hold_code
2054 FROM QP_PREQ_LINES_TMP
2055 WHERE HOLD_CODE IN (QP_PREQ_GRP.G_STATUS_LIMIT_HOLD, QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED);
2056
2057 Cursor limit_violated_details IS
2058 SELECT ldets.line_index, ldets.limit_text, lines.line_id
2059 FROM QP_LDETS_V ldets, qp_preq_lines_tmp lines
2063 l_Header_id number := oe_order_cache.g_header_rec.Header_id;
2060 WHERE ldets.LIMIT_CODE IN (QP_PREQ_GRP.G_STATUS_LIMIT_EXCEEDED, QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED )
2061 AND ldets.line_index = lines.line_index;
2062 l_limit_hold_action varchar2(30):=NVL(fnd_profile.value('ONT_PROMOTION_LIMIT_VIOLATION_ACTION'), 'NO_HOLD');
2064
2065 l_TRANSACTION_PHASE_CODE varchar2(30) := oe_order_cache.g_header_rec.TRANSACTION_PHASE_CODE;
2066
2067 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2068
2069 l_order_source_id NUMBER;
2070 l_orig_sys_document_ref VARCHAR2(50);
2071 l_orig_sys_line_ref VARCHAR2(50);
2072 l_orig_sys_shipment_ref VARCHAR2(50);
2073 l_change_sequence VARCHAR2(50);
2074 l_source_document_type_id NUMBER;
2075 l_source_document_id NUMBER;
2076 l_source_document_line_id NUMBER;
2077 l_line_id NUMBER;
2078 BEGIN
2079 IF l_debug_level > 0 THEN
2080 oe_debug_pub.add('inside procedure process_limits',1);
2081 END IF;
2082 -- First, hold order or line
2083 For I in hold_lines LOOP
2084 IF l_debug_level > 0 THEN
2085 oe_debug_pub.add('line_index = '||I.line_index,1);
2086 oe_debug_pub.add('line_id = '||I.line_id,1);
2087
2088 oe_debug_pub.add('line_Type_code = '||I.line_Type_code,1);
2089 oe_debug_pub.add('line_Type_code = '||I.hold_code,1);
2090 oe_debug_pub.add('TRANSACTION_PHASE_CODE = '||l_TRANSACTION_PHASE_CODE);
2091
2092 END IF;
2093
2094 IF (l_limit_hold_action = 'NO_HOLD' or
2095 i.hold_code = QP_PREQ_GRP.G_STATUS_LIMIT_ADJUSTED or
2096 nvl(l_TRANSACTION_PHASE_CODE, 'F') <> 'F'
2097 ) THEN
2098 FND_MESSAGE.SET_NAME('ONT','ONT_PROMO_LIMIT_EXCEEDED');
2099 OE_MSG_PUB.ADD;
2100 ELSE
2101 IF ( i.line_type_code = 'LINE')
2102 Then
2103 --Promotion_Put_Hold can be copied from OEXVADJB.pls
2104 Promotion_Put_Hold (p_header_id => l_Header_id,
2105
2106 p_line_id => i.line_id);
2107 IF (l_limit_hold_action = 'ORDER_HOLD') THEN
2108 Promotion_Put_Hold(p_header_id => l_header_id,
2109 p_line_id => NULL);
2110 END IF;
2111
2112 ELSIF ( i.line_type_code = 'ORDER' )
2113 Then
2114 Promotion_Put_Hold (p_header_id => l_header_id,
2115
2116
2117 p_line_id => NULL) ;
2118
2119 END IF; -- i.line_type_code = 'LINE' )
2120 END IF; -- limit violation action
2121 END LOOP;
2122 -- Second, put message about adjustments who violated limits
2123 For I in limit_violated_details LOOP
2124 IF l_debug_level > 0 THEN
2125 oe_debug_pub.add('line_index = '||I.line_index,1);
2126 oe_debug_pub.add('limit_text = '||I.limit_text,1);
2127 END IF;
2128 IF (i.line_id = l_header_id) THEN
2129 OE_MSG_PUB.set_msg_context
2130 ( p_entity_code => 'HEADER'
2131 ,p_entity_id => l_header_id
2132 ,p_header_id =>l_header_id
2133 ,p_line_id => NULL
2134 ,p_orig_sys_document_ref => oe_order_cache.g_header_rec.orig_sys_document_ref
2135 ,p_orig_sys_document_line_ref => NULL
2136 ,p_source_document_id => oe_order_cache.g_header_rec.source_document_id
2137 ,p_source_document_line_id => NULL
2138 ,p_change_sequence => oe_order_cache.g_header_rec.change_sequence
2139 ,p_order_source_id => oe_order_cache.g_header_rec.order_source_id
2140 ,p_source_document_type_id => oe_order_cache.g_header_rec.source_document_type_id);
2141 ELSE
2142 l_line_id := i.line_id;
2143 IF l_line_id IS NOT NULL AND l_line_id <> 0 AND
2144 l_line_id <> FND_API.G_MISS_NUM THEN
2145 BEGIN
2146 IF l_debug_level > 0 THEN
2147 oe_debug_pub.add('Getting reference data ');
2148 END IF;
2149
2150 select order_source_id, orig_sys_document_ref,
2151 orig_sys_line_ref, orig_sys_shipment_ref,
2152 change_sequence, source_document_type_id,
2153 source_document_id, source_document_line_id
2154 into l_order_source_id, l_orig_sys_document_ref,
2155 l_orig_sys_line_ref, l_orig_sys_shipment_ref,
2156 l_change_sequence, l_source_document_type_id,
2157 l_source_document_id, l_source_document_line_id
2158 from oe_order_lines_all
2159 where line_id = l_line_id;
2160 EXCEPTION
2161 WHEN NO_DATA_FOUND THEN
2162 IF l_debug_level > 0 THEN
2163 oe_debug_pub.add('no data found while getting reference data ');
2164 END IF;
2165 l_order_source_id := NULL;
2166 l_orig_sys_document_ref := NULL;
2167 l_orig_sys_line_ref := NULL;
2168 l_orig_sys_shipment_ref := NULL;
2172 l_source_document_line_id := NULL;
2169 l_change_sequence := NULL;
2170 l_source_document_type_id := NULL;
2171 l_source_document_id := NULL;
2173 END;
2174 END IF;
2175
2176 OE_MSG_PUB.set_msg_context
2177 ( p_entity_code => 'LINE'
2178 ,p_entity_id => i.line_id
2179 ,p_header_id =>l_header_id
2180 ,p_line_id => i.line_id
2181 ,p_orig_sys_document_ref => nvl(l_orig_sys_document_ref,
2182 oe_order_cache.g_header_rec.orig_sys_document_ref)
2183 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
2184 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
2185 ,p_change_sequence => nvl(l_change_sequence,
2186 oe_order_cache.g_header_rec.change_sequence)
2187 ,p_source_document_id => nvl(l_source_document_id,
2188 oe_order_cache.g_header_rec.source_document_id)
2189 ,p_source_document_line_id => l_source_document_line_id
2190 ,p_order_source_id =>nvl(l_order_source_id,
2191 oe_order_cache.g_header_rec.order_source_id)
2192 ,p_source_document_type_id => nvl(l_source_document_type_id,
2193 oe_order_cache.g_header_rec.source_document_type_id));
2194 END IF;
2195
2196 FND_MESSAGE.SET_NAME('ONT','ONT_PROMO_LIMIT_EXCEEDED');
2197 FND_MESSAGE.SET_TOKEN('ERR_TEXT', i.LIMIT_TEXT);
2198 OE_MSG_PUB.Add;
2199 IF (OE_GLOBALS.G_UI_FLAG ) THEN
2200 IF (G_REQUEST_ID IS NULL) THEN
2201 select oe_msg_request_id_s.nextval into g_request_id from dual;
2202 END IF;
2203 OE_MSG_PUB.INSERT_MESSAGE(OE_MSG_PUB.COUNT_MSG, G_REQUEST_ID,'U');
2204 OE_MSG_PUB.DELETE_MSG(OE_MSG_PUB.COUNT_MSG);
2205 END IF;
2206
2207
2208 end LOOP;
2209 End Process_Limits;
2210
2211
2212 function check_notify_OC
2213 return boolean
2214 is
2215 l_source_document_type_id number := oe_order_cache.g_header_rec.source_document_type_id;
2216 l_header_id number := oe_order_cache.g_header_rec.header_id;
2217 l_source_system_code number := 0;
2218 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2219 begin
2220
2221 IF l_debug_level > 0 THEN
2222 oe_debug_pub.add(' In OE_ADV_PRICE_PVT.CHECK_NOTIFY');
2223 END IF;
2224 begin
2225 select 1 into l_source_system_code from
2226 oe_order_headers_all oh,
2227 qp_list_headers qh,
2228 -- qp_preq_lines_tmp lines,
2229 qp_preq_ldets_tmp ldets
2230 where qh.currency_code = oh.transactional_curr_code
2231 and oh.header_id = l_header_id
2232 -- and lines.line_index = ldets.line_index
2233 and ldets.CREATED_FROM_LIST_HEADER_ID = qh.LIST_HEADER_ID
2234 and qh.source_system_code = 'AMS'
2235 and qh.active_flag = 'Y'
2236 and rownum = 1;
2237 exception
2238 when no_data_found then
2239 null;
2240 end;
2241
2242 IF l_debug_level > 0 THEN
2243 oe_debug_pub.add('l_source_document_type_id = '||l_source_document_type_id);
2244 oe_debug_pub.add('l_source_system_code = '||l_source_system_code);
2245 END IF;
2246
2247
2248 IF nvl(l_source_document_type_id,0) IN (1, 3, 4, 7, 8, 11, 12, 13, 14,15, 16, 17, 18 , 19) OR l_source_system_code = 1
2249 THEN
2250 return true;
2251 else return false;
2252 end if;
2253 end;
2254
2255
2256 procedure new_and_updated_notify is
2257
2258 l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
2259 cursor insert_adj_cur is
2260 select price_adjustment_id, ldets.automatic_flag auto_flag,line_id,
2261 modifier_level_code, ldets.LIST_HEADER_ID list_header_id,
2262 LIST_LINE_ID, LIST_LINE_TYPE_CODE,inventory_item_id,
2263 substitution_value_to, related_item_id,process_code,
2264 APPLIED_FLAG, override_flag,operand_calculation_code, operand_value,
2265 lines.priced_quantity priced_quantity,lines.line_quantity lquantity,
2266 adjustment_amount,pricing_phase_id,updated_flag,
2267 order_qty_operand, order_qty_adj_amt,
2268 CHARGE_TYPE_CODE, CHARGE_SUBTYPE_CODE,list_line_no,
2269 source_system_code,benefit_qty, benefit_uom_code,
2270 expiration_date, rebate_transaction_type_code,
2271
2272 accrual_flag, ldets.line_quantity line_quantity,accrual_conversion_rate,
2273 pricing_group_sequence,print_on_invoice_flag,
2274 price_break_type_code, substitution_attribute,
2275 proration_type_code, include_on_returns_flag,lines.line_index line_index
2276 from
2277 QP_LDETS_v ldets
2278 , QP_PREQ_LINES_TMP lines
2279 , QP_LIST_HEADERS_B QH
2280 where
2281 ldets.LIST_HEADER_ID = qh.list_header_id
2282 AND ldets.process_code IN (QP_PREQ_GRP.G_STATUS_NEW,QP_PREQ_GRP.G_STATUS_UPDATED,QP_PREQ_GRP.G_STATUS_UNCHANGED) --Bug8467307
2283 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)
2284 AND lines.process_status <> 'NOT_VALID'
2285 AND ldets.line_index=lines.line_index
2289 AND ldets.created_from_list_type_code not in ('PRL','AGR')
2286 AND (nvl(ldets.automatic_flag,'N') = 'Y')
2287 -- or
2288 -- (ldets.list_line_type_code = 'FREIGHT_CHARGE')
2290 AND ldets.list_line_type_code<>'PLL'
2291
2292 AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE');
2293
2294 l_Line_Adj_rec OE_Order_PUB.Line_Adj_Rec_Type;
2295 l_Header_Adj_rec OE_Order_PUB.Header_Adj_Rec_Type;
2296 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2297 l_index NUMBER;
2298 l_return_status VARCHAR2(1);
2299 l_qty NUMBER; --Bug8467307
2300 l_ind PLS_INTEGER; --Bug8467307
2301 G_MAX_REQUESTS NUMBER := 10000; --Bug8467307
2302
2303 begin
2304 IF l_debug_level > 0 THEN
2305 oe_debug_pub.add('in new_and_updated_notify',1);
2306 END IF;
2307 FOR l_insert IN insert_adj_cur LOOP
2308 IF l_insert.modifier_level_code <> 'ORDER' THEN
2309 l_Line_Adj_rec.line_id := l_insert.line_id;
2310 l_Line_Adj_rec.price_adjustment_id := l_insert.price_adjustment_id;
2311 l_Line_Adj_rec.creation_date := sysdate;
2312 l_Line_Adj_rec.created_by := fnd_global.user_id;
2313 l_Line_Adj_rec.last_update_date := sysdate;
2314 l_Line_Adj_rec.last_updated_by := fnd_global.user_id;
2315 l_Line_Adj_rec.last_update_login := fnd_global.user_id;
2316 l_Line_Adj_rec.header_id := oe_order_pub.g_hdr.header_id;
2317 l_Line_Adj_rec.automatic_flag := l_insert.auto_flag;
2318 l_Line_Adj_rec.LIST_HEADER_ID := l_insert.LIST_HEADER_ID;
2319 l_Line_Adj_rec.LIST_LINE_ID := l_insert.LIST_LINE_ID;
2320 l_Line_Adj_rec.LIST_LINE_TYPE_CODE := l_insert.LIST_LINE_TYPE_CODE;
2321 If l_insert.LIST_LINE_TYPE_CODE = 'TSN' Then
2322 l_Line_Adj_rec.modified_from := l_insert.substitution_attribute;
2323 l_Line_Adj_rec.modified_to := l_insert.substitution_value_to;
2324 Elsif l_insert.LIST_LINE_TYPE_CODE = 'IUE' Then
2325 l_Line_Adj_rec.modified_from := to_char(l_insert.inventory_item_id);
2326 l_Line_Adj_rec.modified_to := to_char(l_insert.related_item_id);
2327 End If;
2328 If l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
2329 l_Line_Adj_rec.UPDATED_FLAG := 'N';
2330 Elsif l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
2331 l_Line_Adj_rec.UPDATED_FLAG := l_insert.updated_flag;
2332 l_Line_Adj_rec.print_on_invoice_flag := l_insert.print_on_invoice_flag;
2333 End If;
2334 l_Line_Adj_rec.UPDATE_ALLOWED := l_insert.override_flag;
2335 l_Line_Adj_rec.APPLIED_FLAG := l_insert.APPLIED_FLAG;
2336 IF l_insert.operand_calculation_code = '%' or
2337 l_insert.operand_calculation_code = 'LUMPSUM' then
2338 l_Line_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value);
2339 ELSE
2340 IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
2341 l_Line_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*l_insert.priced_quantity/nvl(l_insert.lquantity,1));
2342 ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
2343 l_Line_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*nvl(l_insert.priced_quantity,l_insert.lquantity)/l_insert.lquantity);
2344 END IF;
2345 END IF;
2346 l_Line_Adj_rec.arithmetic_operator := l_insert.operand_calculation_code;
2347 l_Line_Adj_rec.ADJUSTED_AMOUNT := nvl(l_insert.order_qty_adj_amt, l_insert.adjustment_amount*nvl(l_insert.priced_quantity,1)/nvl(l_insert.lquantity,1));
2348 l_Line_Adj_rec.pricing_phase_id := l_insert.pricing_phase_id;
2349 l_Line_Adj_rec.CHARGE_TYPE_CODE := l_insert.CHARGE_TYPE_CODE;
2350 l_Line_Adj_rec.CHARGE_SUBTYPE_CODE := l_insert.CHARGE_SUBTYPE_CODE;
2351 l_Line_Adj_rec.list_line_no := l_insert.list_line_no;
2352 l_Line_Adj_rec.source_system_code := l_insert.source_system_code;
2353 l_Line_Adj_rec.benefit_qty := l_insert.benefit_qty;
2354 l_Line_Adj_rec.benefit_uom_code := l_insert.benefit_uom_code;
2355 l_Line_Adj_rec.expiration_date := l_insert.expiration_date;
2356 l_Line_Adj_rec.rebate_transaction_type_code := l_insert.rebate_transaction_type_code;
2357 l_Line_Adj_rec.accrual_flag := l_insert.accrual_flag;
2358 l_Line_Adj_rec.range_break_quantity := l_insert.line_quantity;
2359 l_Line_Adj_rec.accrual_conversion_rate := l_insert.accrual_conversion_rate;
2360 l_Line_Adj_rec.pricing_group_sequence := l_insert.pricing_group_sequence;
2361 l_Line_Adj_rec.modifier_level_code := l_insert.modifier_level_code;
2362 l_Line_Adj_rec.price_break_type_code := l_insert.price_break_type_code;
2363 l_Line_Adj_rec.substitution_attribute := l_insert.substitution_attribute;
2364 l_Line_Adj_rec.proration_type_code := l_insert.proration_type_code;
2365 l_Line_Adj_rec.include_on_returns_flag := l_insert.include_on_returns_flag;
2366 l_Line_Adj_rec.OPERAND_PER_PQTY := l_insert.OPERAND_value;
2367
2368 l_Line_Adj_rec.ADJUSTED_AMOUNT_PER_PQTY := l_insert.adjustment_amount;
2369
2370 l_Line_Adj_rec.line_index := l_insert.line_index;
2371 l_Line_Adj_rec.return_status := FND_API.G_RET_STS_SUCCESS;
2372 IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
2373 l_Line_Adj_rec.db_flag := FND_API.G_FALSE;
2374 l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_CREATE;
2375 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
2376 p_old_line_adj_rec => NULL,
2377 p_line_adj_rec =>l_line_adj_rec,
2378 p_line_adj_id => l_line_adj_rec.price_adjustment_id,
2379 x_index => l_index,
2380 x_return_status => l_return_status);
2381 ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
2382 l_Line_Adj_rec.db_flag := FND_API.G_TRUE;
2386 p_line_adj_rec =>l_line_adj_rec,
2383 l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2384
2385 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
2387 p_line_adj_id => l_line_adj_rec.price_adjustment_id,
2388 x_index => l_index,
2389 x_return_status => l_return_status);
2390 --Start of bug#8467307
2391 ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UNCHANGED Then
2392 IF l_debug_level > 0 THEN
2393 oe_debug_pub.add ('Adj ID=' ||l_insert.price_adjustment_id);
2394 END IF;
2395 -- To find the old line quantity
2396 l_qty := l_insert.lquantity;
2397 l_ind := (mod(l_Line_Adj_rec.line_id,100000) * G_MAX_REQUESTS)+1;
2398 IF l_debug_level > 0 THEN
2399 oe_debug_pub.add('Index- :'||l_ind);
2400 END IF;
2401 IF OE_ORDER_UTIL.g_old_line_tbl.exists(l_ind) THEN
2402 l_qty := OE_ORDER_UTIL.g_old_line_tbl(l_ind).ordered_quantity;
2403 IF l_debug_level > 0 THEN
2404 oe_debug_pub.add('FOUND Old Line Quantity :'||l_qty);
2405 END IF;
2406 ELSE
2407 IF l_debug_level > 0 THEN
2408 oe_debug_pub.add('NOT FOUND ');
2409 END IF;
2410 END IF;
2411 IF l_debug_level > 0 THEN
2412 oe_debug_pub.add('Old Line Quantity :'||l_qty);
2413 oe_debug_pub.add('l_insert.lquantity :'||l_insert.lquantity);
2414 END IF;
2415 IF l_qty <> l_insert.lquantity THEN -- IF the Quantity has changed on the Line THEN
2416 l_Line_Adj_rec.UPDATED_FLAG := 'Y';
2417 l_Line_Adj_rec.db_flag := FND_API.G_TRUE;
2418 l_Line_Adj_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2419 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
2420 p_line_adj_rec =>l_line_adj_rec,
2421 p_line_adj_id => l_line_adj_rec.price_adjustment_id,
2422 x_index => l_index,
2423 x_return_status => l_return_status);
2424 END IF; ---End of Bug#8467307
2425 END IF;
2426 ELSE -- modifier level code 'ORDER'
2427 --l_Header_Adj_rec.line_id := l_insert.line_id;
2428 l_Header_Adj_rec.price_adjustment_id := l_insert.price_adjustment_id;
2429 l_Header_Adj_rec.creation_date := sysdate;
2430 l_Header_Adj_rec.created_by := fnd_global.user_id;
2431 l_Header_Adj_rec.last_update_date := sysdate;
2432 l_Header_Adj_rec.last_updated_by := fnd_global.user_id;
2433 l_Header_Adj_rec.last_update_login := fnd_global.user_id;
2434 l_Header_Adj_rec.header_id := oe_order_pub.g_hdr.header_id;
2435 l_Header_Adj_rec.automatic_flag := l_insert.auto_flag;
2436 l_Header_Adj_rec.LIST_HEADER_ID := l_insert.LIST_HEADER_ID;
2437 l_Header_Adj_rec.LIST_LINE_ID := l_insert.LIST_LINE_ID;
2438 l_Header_Adj_rec.LIST_LINE_TYPE_CODE := l_insert.LIST_LINE_TYPE_CODE;
2439 If l_insert.LIST_LINE_TYPE_CODE = 'TSN' Then
2440 l_Header_Adj_rec.modified_from := l_insert.substitution_attribute;
2441 l_Header_Adj_rec.modified_to := l_insert.substitution_value_to;
2442 Elsif l_insert.LIST_LINE_TYPE_CODE = 'IUE' Then
2443 l_Header_Adj_rec.modified_from := to_char(l_insert.inventory_item_id);
2444 l_Header_Adj_rec.modified_to := to_char(l_insert.related_item_id);
2445 End If;
2446 If l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
2447 l_Header_Adj_rec.UPDATED_FLAG := 'N';
2448 Elsif l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
2449 l_Header_Adj_rec.UPDATED_FLAG := l_insert.updated_flag;
2450 l_Header_Adj_rec.print_on_invoice_flag := l_insert.print_on_invoice_flag;
2451 End If;
2452 l_Header_Adj_rec.UPDATE_ALLOWED := l_insert.override_flag;
2453 l_Header_Adj_rec.APPLIED_FLAG := l_insert.APPLIED_FLAG;
2454 IF l_insert.operand_calculation_code = '%' or
2455 l_insert.operand_calculation_code = 'LUMPSUM' then
2456 l_Header_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value);
2457 ELSE
2458 IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
2459 l_Header_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*l_insert.priced_quantity/nvl(l_insert.lquantity,1));
2460 ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
2461 l_Header_Adj_rec.operand := nvl(l_insert.order_qty_operand, l_insert.operand_value*nvl(l_insert.priced_quantity,l_insert.lquantity)/l_insert.lquantity);
2462 END IF;
2463 END IF;
2464 l_Header_Adj_rec.arithmetic_operator := l_insert.operand_calculation_code;
2465 l_Header_Adj_rec.ADJUSTED_AMOUNT := nvl(l_insert.order_qty_adj_amt, l_insert.adjustment_amount*nvl(l_insert.priced_quantity,1)/nvl(l_insert.lquantity,1));
2466 l_Header_Adj_rec.pricing_phase_id := l_insert.pricing_phase_id;
2467 l_Header_Adj_rec.CHARGE_TYPE_CODE := l_insert.CHARGE_TYPE_CODE;
2468 l_Header_Adj_rec.CHARGE_SUBTYPE_CODE := l_insert.CHARGE_SUBTYPE_CODE;
2469 l_Header_Adj_rec.list_line_no := l_insert.list_line_no;
2470 l_Header_Adj_rec.source_system_code := l_insert.source_system_code;
2471 l_Header_Adj_rec.benefit_qty := l_insert.benefit_qty;
2472 l_Header_Adj_rec.benefit_uom_code := l_insert.benefit_uom_code;
2473 l_Header_Adj_rec.expiration_date := l_insert.expiration_date;
2474 l_Header_Adj_rec.rebate_transaction_type_code := l_insert.rebate_transaction_type_code;
2475 l_Header_Adj_rec.accrual_flag := l_insert.accrual_flag;
2476 l_Header_Adj_rec.range_break_quantity := l_insert.line_quantity;
2477 l_Header_Adj_rec.accrual_conversion_rate := l_insert.accrual_conversion_rate;
2481 l_Header_Adj_rec.substitution_attribute := l_insert.substitution_attribute;
2478 l_Header_Adj_rec.pricing_group_sequence := l_insert.pricing_group_sequence;
2479 l_Header_Adj_rec.modifier_level_code := l_insert.modifier_level_code;
2480 l_Header_Adj_rec.price_break_type_code := l_insert.price_break_type_code;
2482 l_Header_Adj_rec.proration_type_code := l_insert.proration_type_code;
2483 l_Header_Adj_rec.include_on_returns_flag := l_insert.include_on_returns_flag;
2484 l_Header_Adj_rec.OPERAND_PER_PQTY := l_insert.OPERAND_value;
2485
2486 l_Header_Adj_rec.ADJUSTED_AMOUNT_PER_PQTY := l_insert.adjustment_amount;
2487
2488 --l_Header_Adj_rec.line_index := l_insert.line_index;
2489 l_Header_Adj_rec.return_status := FND_API.G_RET_STS_SUCCESS;
2490 IF l_insert.process_code = QP_PREQ_GRP.G_STATUS_NEW Then
2491 l_Header_Adj_rec.db_flag := FND_API.G_FALSE;
2492 l_Header_Adj_rec.operation := OE_GLOBALS.G_OPR_CREATE;
2493 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
2494 p_old_hdr_adj_rec => NULL,
2495 p_hdr_adj_rec =>l_header_adj_rec,
2496 p_hdr_adj_id => l_header_adj_rec.price_adjustment_id,
2497 x_index => l_index,
2498 x_return_status => l_return_status);
2499 ELSIF l_insert.process_code = QP_PREQ_GRP.G_STATUS_UPDATED Then
2500 l_Header_Adj_rec.db_flag := FND_API.G_TRUE;
2501 l_Header_Adj_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2502
2503 OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
2504 p_hdr_adj_rec =>l_header_adj_rec,
2505 p_hdr_adj_id => l_header_adj_rec.price_adjustment_id,
2506 x_index => l_index,
2507 x_return_status => l_return_status);
2508 END IF;
2509 END IF;--modifier_leveL_code
2510 /*OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
2511 p_line_adj_rec =>l_line_adj_rec,
2512 p_line_adj_id => l_line_adj_rec.price_adjustment_id,
2513 x_index => l_index,
2514 x_return_status => l_return_status);*/
2515
2516 IF l_debug_level > 0 THEN
2517 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY IS: ' || L_RETURN_STATUS ) ;
2518 END IF;
2519 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2520 IF l_debug_level > 0 THEN
2521 oe_debug_pub.add( 'EVENT NOTIFY - UNEXPECTED ERROR' ) ;
2522 oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY');
2523 END IF;
2524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2525 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2526 IF l_debug_level > 0 THEN
2527 oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY' ) ;
2528 oe_debug_pub.add( 'EXITING OE_ADV_PRICE_PVT.NEW_AND_UPDATE_NOTIFY' , 1 ) ;
2529 END IF;
2530 RAISE FND_API.G_EXC_ERROR;
2531 END IF;
2532
2533 end loop;
2534
2535 end;
2536
2537
2538
2539 --bug 3702538
2540 procedure Register_price_list(
2541 px_line_Tbl IN OUT NOCOPY oe_Order_Pub.Line_Tbl_Type
2542 )
2543 IS
2544
2545 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2546 l_mod_line_id NUMBER;
2547
2548 Cursor updated_lines_prl IS
2549 SELECT l.LINE_ID,
2550 lines.PRICE_LIST_HEADER_ID,
2551 l.price_list_id
2552 FROM
2553 QP_PREQ_LINES_TMP lines
2554 ,OE_ORDER_LINES l
2555 WHERE lines.pricing_status_code IN
2556 ( QP_PREQ_GRP.G_STATUS_UPDATED
2557 ,QP_PREQ_GRP.G_STATUS_GSA_VIOLATION)
2558 AND lines.process_status <> 'NOT_VALID'
2559 AND lines.line_type_code='LINE'
2560 AND nvl(decode(lines.price_list_header_id,-9999,NULL,lines.price_list_header_id),0) <> nvl(l.price_list_id,0)
2561 AND l.line_id = lines.line_id
2562 AND l.ordered_quantity <> 0;
2563
2564 BEGIN
2565
2566 IF l_debug_level > 0 THEN
2567 oe_debug_pub.add('In Register_price_list');
2568 END IF;
2569 for i in updated_lines_prl loop
2570 l_mod_line_id := MOD(i.line_id,G_BINARY_LIMIT); -- Bug 8631297
2571
2572 IF l_debug_level > 0 THEN
2573 oe_debug_pub.add('Old Price List id : '||i.price_list_id);
2574 oe_debug_pub.add('New Price List id : '||i.price_list_header_id);
2575 END IF;
2576 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).price_list_id := i.price_list_header_id;
2577 px_line_tbl(G_PRICE_LINE_ID_TBL(l_mod_line_id)).operation := OE_GLOBALS.G_OPR_UPDATE;
2578 end loop;
2579 END Register_price_list;--bug 3702538
2580
2581
2582 procedure process_adv_modifiers
2583 (
2584 x_return_status OUT NOCOPY Varchar2,
2585 p_Control_Rec IN OE_ORDER_PRICE_PVT.Control_rec_type,
2586 p_any_frozen_line IN Boolean,
2587 px_line_Tbl IN OUT NOCOPY oe_Order_Pub.Line_Tbl_Type,
2588 px_old_line_Tbl IN OUT NOCOPY oe_order_pub.line_tbl_type,
2589 p_header_id IN number,
2590 p_line_id IN number,
2591 p_header_rec IN oe_Order_Pub.header_rec_type,
2592 p_pricing_events IN varchar2
2593 ) IS
2594
2595 l_control_rec OE_GLOBALS.Control_Rec_Type;
2596 lx_new_header_rec oe_Order_Pub.header_rec_type := p_header_rec;
2597 l_notify_flag BOOLEAN;
2598 l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
2599
2600
2601 --
2602 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2603 --
2604 BEGIN
2605 NULL;
2609 IF l_debug_level > 0 THEN
2606 x_return_status := FND_API.G_RET_STS_SUCCESS;
2607 If OE_DEBUG_PUB.G_DEBUG = FND_API.G_TRUE Then
2608 G_DEBUG := TRUE;
2610 oe_debug_pub.add( 'BCT G_DEBUG IS:'||OE_DEBUG_PUB.G_DEBUG ) ;
2611 END IF;
2612 Else
2613 G_DEBUG := FALSE;
2614 End If;
2615
2616 -- not need to process prg, iue, tsn, limits for calculate only call
2617 IF p_control_rec.p_calculate_flag <> QP_PREQ_GRP.G_CALCULATE_ONLY
2618 THEN
2619
2620 IF l_debug_level > 0 THEN
2621 oe_debug_pub.add( 'INSIDE OE_ORDER_ADV_PVT.PROCESS_ADV_MODIFIERS' , 1 ) ;
2622 END IF;
2623
2624 Sort_Line_Table(px_line_Tbl,G_PRICE_LINE_ID_TBL);
2625 Item_Upgrade(px_old_line_tbl,px_line_Tbl,p_pricing_events);
2626 --Term_Substitution(p_header_rec,lx_new_header_rec,px_old_line_tbl,px_line_Tbl); --Bug 13573144
2627 Term_Substitution(p_header_rec,lx_new_header_rec,px_old_line_tbl,px_line_Tbl,p_pricing_events); --Bug 13573144
2628
2629 IF l_debug_level > 0 THEN
2630 oe_debug_pub.add( 'P_PRICING_EVENTS = '||P_PRICING_EVENTS , 1 ) ;
2631 oe_debug_pub.add( 'BEFORE CALL TO CALL PROCESS ORDER' , 1 ) ;
2632 oe_debug_pub.add( 'P_HEADER_REC.HEADER_ID='||P_HEADER_REC.HEADER_ID , 1 ) ;
2633 oe_debug_pub.add( 'P_HEADER_REC.PAYMENT_TERM_ID='||P_HEADER_REC.PAYMENT_TERM_ID , 1 ) ;
2634 oe_debug_pub.add( 'P_HEADER_REC.OPERATION='||P_HEADER_REC.OPERATION , 1 ) ;
2635 oe_debug_pub.add( 'LX_NEW_HEADER_REC.HEADER_ID='||LX_NEW_HEADER_REC.HEADER_ID , 1 ) ;
2636 oe_debug_pub.add( 'LX_NEW_HEADER_REC.PAYMENT_TERM_ID='||LX_NEW_HEADER_REC.PAYMENT_TERM_ID , 1 ) ;
2637 oe_debug_pub.add( 'LX_NEW_HEADER_REC.OPERATION='||LX_NEW_HEADER_REC.OPERATION , 1 ) ;
2638 END IF;
2639
2640 Process_Prg(px_line_Tbl
2641 , px_old_line_tbl
2642 ,G_PRICE_LINE_ID_TBL);
2643
2644 Process_Limits;
2645
2646 --bug 3702538
2647 Register_price_list(px_line_Tbl);
2648 --bug 3702538
2649
2650 Call_Process_Order(p_header_rec,
2651 lx_new_header_rec,
2652 px_line_Tbl,
2653 px_old_line_tbl,
2654 l_control_rec,
2655 x_return_status);
2656
2657 IF l_debug_level > 0 THEN
2658 oe_debug_pub.add('l_booked_flag = '||l_booked_flag);
2659 oe_debug_pub.add('oe_order_cache.g_header_rec.booked_flag = '||oe_order_cache.g_header_rec.booked_flag);
2660 END IF;
2661
2662 END IF; -- <> Calculate_Only
2663
2664 new_and_updated_notify;
2665
2666 END process_adv_modifiers;
2667
2668 Procedure Insert_Adj(p_header_id in Number default null)
2669 IS
2670 l_booked_flag varchar2(1) := oe_order_cache.g_header_rec.booked_flag;
2671 --
2672 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2673 --
2674 BEGIN
2675 IF l_debug_level > 0 THEN
2676 oe_debug_pub.add( 'INSIDE OE_ADV_PRICE_PVT.INSERT_ADJ' ) ;
2677 END IF;
2678 INSERT INTO OE_PRICE_ADJUSTMENTS
2679 ( PRICE_ADJUSTMENT_ID
2680 , CREATION_DATE
2681 , CREATED_BY
2682 , LAST_UPDATE_DATE
2683 , LAST_UPDATED_BY
2684 , LAST_UPDATE_LOGIN
2685 , PROGRAM_APPLICATION_ID
2686 , PROGRAM_ID
2687 , PROGRAM_UPDATE_DATE
2688 , REQUEST_ID
2689 , HEADER_ID
2690 , DISCOUNT_ID
2691 , DISCOUNT_LINE_ID
2692 , AUTOMATIC_FLAG
2693 , PERCENT
2694 , LINE_ID
2695 , CONTEXT
2696 , ATTRIBUTE1
2697 , ATTRIBUTE2
2698 , ATTRIBUTE3
2699 , ATTRIBUTE4
2700 , ATTRIBUTE5
2701 , ATTRIBUTE6
2702 , ATTRIBUTE7
2703 , ATTRIBUTE8
2704 , ATTRIBUTE9
2705 , ATTRIBUTE10
2706 , ATTRIBUTE11
2707 , ATTRIBUTE12
2708 , ATTRIBUTE13
2709 , ATTRIBUTE14
2710 , ATTRIBUTE15
2711 , ORIG_SYS_DISCOUNT_REF
2712 , LIST_HEADER_ID
2713 , LIST_LINE_ID
2714 , LIST_LINE_TYPE_CODE
2715 , MODIFIER_MECHANISM_TYPE_CODE
2716 , MODIFIED_FROM
2717 , MODIFIED_TO
2718 , UPDATED_FLAG
2719 , UPDATE_ALLOWED
2720 , APPLIED_FLAG
2721 , CHANGE_REASON_CODE
2722 , CHANGE_REASON_TEXT
2723 , operand
2724 , Arithmetic_operator
2725 , COST_ID
2726 , TAX_CODE
2727 , TAX_EXEMPT_FLAG
2728 , TAX_EXEMPT_NUMBER
2729 , TAX_EXEMPT_REASON_CODE
2730 , PARENT_ADJUSTMENT_ID
2731 , INVOICED_FLAG
2732 , ESTIMATED_FLAG
2733 , INC_IN_SALES_PERFORMANCE
2734 , SPLIT_ACTION_CODE
2735 , ADJUSTED_AMOUNT
2736 , PRICING_PHASE_ID
2737 , CHARGE_TYPE_CODE
2738 , CHARGE_SUBTYPE_CODE
2739 , list_line_no
2740 , source_system_code
2741 , benefit_qty
2742 , benefit_uom_code
2743 , print_on_invoice_flag
2744 , expiration_date
2745 , rebate_transaction_type_code
2746 , rebate_transaction_reference
2747 , rebate_payment_system_code
2748 , redeemed_date
2749 , redeemed_flag
2750 , accrual_flag
2751 , range_break_quantity
2752 , accrual_conversion_rate
2753 , pricing_group_sequence
2754 , modifier_level_code
2755 , price_break_type_code
2756 , substitution_attribute
2757 , proration_type_code
2758 , CREDIT_OR_CHARGE_FLAG
2759 , INCLUDE_ON_RETURNS_FLAG
2760 , AC_CONTEXT
2761 , AC_ATTRIBUTE1
2762 , AC_ATTRIBUTE2
2766 , AC_ATTRIBUTE6
2763 , AC_ATTRIBUTE3
2764 , AC_ATTRIBUTE4
2765 , AC_ATTRIBUTE5
2767 , AC_ATTRIBUTE7
2768 , AC_ATTRIBUTE8
2769 , AC_ATTRIBUTE9
2770 , AC_ATTRIBUTE10
2771 , AC_ATTRIBUTE11
2772 , AC_ATTRIBUTE12
2773 , AC_ATTRIBUTE13
2774 , AC_ATTRIBUTE14
2775 , AC_ATTRIBUTE15
2776 , OPERAND_PER_PQTY
2777 , ADJUSTED_AMOUNT_PER_PQTY
2778 , LOCK_CONTROL
2779 )
2780 ( SELECT /*+ ORDERED USE_NL(ldets lines qh) */
2781 -- oe_price_adjustments_s.nextval -- p_Line_Adj_rec.price_adjustment_id
2782 ldets.price_adjustment_id
2783 , sysdate --p_Line_Adj_rec.creation_date
2784 , fnd_global.user_id --p_Line_Adj_rec.created_by
2785 , sysdate --p_Line_Adj_rec.last_update_date
2786 , fnd_global.user_id --p_Line_Adj_rec.last_updated_by
2787 , fnd_global.login_id --p_Line_Adj_rec.last_update_login
2788 , NULL --p_Line_Adj_rec.program_application_id
2789 , NULL --p_Line_Adj_rec.program_id
2790 , NULL --p_Line_Adj_rec.program_update_date
2791 , NULL --p_Line_Adj_rec.request_id
2792 , decode(p_header_id, NULL, oe_order_pub.g_hdr.header_id, p_header_id) --p_Line_Adj_rec.header_id
2793 , NULL --p_Line_Adj_rec.discount_id
2794 , NULL --p_Line_Adj_rec.discount_line_id
2795 , ldets.automatic_flag
2796 , NULL --p_Line_Adj_rec.percent
2797 , decode(ldets.modifier_level_code,'ORDER',NULL,lines.line_id)
2798 , NULL --p_Line_Adj_rec.context
2799 , NULL --p_Line_Adj_rec.attribute1
2800 , NULL --p_Line_Adj_rec.attribute2
2801 , NULL --p_Line_Adj_rec.attribute3
2802 , NULL --p_Line_Adj_rec.attribute4
2803 , NULL --p_Line_Adj_rec.attribute5
2804 , NULL --p_Line_Adj_rec.attribute6
2805 , NULL --p_Line_Adj_rec.attribute7
2806 , NULL --p_Line_Adj_rec.attribute8
2807 , NULL --p_Line_Adj_rec.attribute9
2808 , NULL --p_Line_Adj_rec.attribute10
2809 , NULL --p_Line_Adj_rec.attribute11
2810 , NULL --p_Line_Adj_rec.attribute12
2811 , NULL --p_Line_Adj_rec.attribute13
2812 , NULL --p_Line_Adj_rec.attribute14
2813 , NULL --p_Line_Adj_rec.attribute15
2814 -- Bug 7523118
2815 , 'OE_PRICE_ADJUSTMENTS'||ldets.price_adjustment_id --p_Line_Adj_rec.orig_sys_discount_ref
2816 , ldets.LIST_HEADER_ID
2817 , ldets.LIST_LINE_ID
2818 , ldets.LIST_LINE_TYPE_CODE
2819 , NULL --p_Line_Adj_rec.MODIFIER_MECHANISM_TYPE_CODE
2820 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_attribute, 'IUE', to_char(ldets.inventory_item_id), NULL)
2821 , decode(ldets.list_line_type_code, 'TSN', ldets.substitution_value_to, 'IUE', to_char(ldets.related_item_id), NULL)
2822 , 'N' --p_Line_Adj_rec.UPDATED_FLAG
2823 , ldets.override_flag
2824 , ldets.APPLIED_FLAG
2825 , NULL --p_Line_Adj_rec.CHANGE_REASON_CODE
2826 , NULL --p_Line_Adj_rec.CHANGE_REASON_TEXT
2827 , nvl(ldets.order_qty_operand, decode(ldets.operand_calculation_code,
2828 '%', ldets.operand_value,
2829 'LUMPSUM', ldets.operand_value,
2830 ldets.operand_value*lines.priced_quantity/nvl(lines.line_quantity,1)))
2831 , ldets.operand_calculation_code --p_Line_Adj_rec.arithmetic_operator
2832 , NULl --p_line_Adj_rec.COST_ID
2833 , NULL --p_line_Adj_rec.TAX_CODE
2834 , NULL --p_line_Adj_rec.TAX_EXEMPT_FLAG
2835 , NULL --p_line_Adj_rec.TAX_EXEMPT_NUMBER
2836 , NULL --p_line_Adj_rec.TAX_EXEMPT_REASON_CODE
2837 , NULL --p_line_Adj_rec.PARENT_ADJUSTMENT_ID
2838 , NULL --p_line_Adj_rec.INVOICED_FLAG
2839 , NULL --p_line_Adj_rec.ESTIMATED_FLAG
2840 , NULL --p_line_Adj_rec.INC_IN_SALES_PERFORMANCE
2841 , NULL --p_line_Adj_rec.SPLIT_ACTION_CODE
2842 , nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount*nvl(lines.priced_quantity,1)/nvl(lines.line_quantity,1))
2843 , ldets.pricing_phase_id --p_line_Adj_rec.PRICING_PHASE_ID
2844 , ldets.CHARGE_TYPE_CODE
2845 , ldets.CHARGE_SUBTYPE_CODE
2846 , ldets.list_line_no
2847 , qh.source_system_code
2848 , ldets.benefit_qty
2849 , ldets.benefit_uom_code
2850 , NULL --p_Line_Adj_rec.print_on_invoice_flag
2851 , ldets.expiration_date
2852 , ldets.rebate_transaction_type_code
2853 , NULL --p_Line_Adj_rec.rebate_transaction_reference
2854 , NULL --p_Line_Adj_rec.rebate_payment_system_code
2855 , NULL --p_Line_Adj_rec.redeemed_date
2856 , NULL --p_Line_Adj_rec.redeemed_flag
2857 , ldets.accrual_flag
2858 , ldets.line_quantity --p_Line_Adj_rec.range_break_quantity
2859 , ldets.accrual_conversion_rate
2860 , ldets.pricing_group_sequence
2861 , ldets.modifier_level_code
2862 , ldets.price_break_type_code
2863 , ldets.substitution_attribute
2864 , ldets.proration_type_code
2865 , NULL --p_Line_Adj_rec.credit_or_charge_flag
2866 , ldets.include_on_returns_flag
2867 , NULL -- p_Line_Adj_rec.ac_context
2868 , NULL -- p_Line_Adj_rec.ac_attribute1
2869 , NULL -- p_Line_Adj_rec.ac_attribute2
2870 , NULL -- p_Line_Adj_rec.ac_attribute3
2871 , NULL -- p_Line_Adj_rec.ac_attribute4
2872 , NULL -- p_Line_Adj_rec.ac_attribute5
2873 , NULL -- p_Line_Adj_rec.ac_attribute6
2874 , NULL -- p_Line_Adj_rec.ac_attribute7
2875 , NULL -- p_Line_Adj_rec.ac_attribute8
2876 , NULL -- p_Line_Adj_rec.ac_attribute9
2877 , NULL -- p_Line_Adj_rec.ac_attribute10
2878 , NULL -- p_Line_Adj_rec.ac_attribute11
2879 , NULL -- p_Line_Adj_rec.ac_attribute12
2880 , NULL -- p_Line_Adj_rec.ac_attribute13
2881 , NULL -- p_Line_Adj_rec.ac_attribute14
2882 , NULL -- p_Line_Adj_rec.ac_attribute15
2883 , ldets.OPERAND_value
2884 , ldets.adjustment_amount
2885 , 1
2886 FROM
2887 QP_LDETS_v ldets
2888 , QP_PREQ_LINES_TMP lines
2889 , QP_LIST_HEADERS_B QH
2890 WHERE
2891 ldets.list_header_id=qh.list_header_id
2892 AND ldets.process_code=QP_PREQ_GRP.G_STATUS_NEW
2893 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)
2894 AND lines.process_status <> 'NOT_VALID'
2895 AND ldets.line_index=lines.line_index
2896 --AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2897 AND (nvl(ldets.automatic_flag,'N') = 'Y')
2898 -- or
2899 -- (ldets.list_line_type_code = 'FREIGHT_CHARGE'))
2900 AND ldets.created_from_list_type_code not in ('PRL','AGR')
2901 AND ldets.list_line_type_code<>'PLL'
2902 AND ldets.list_line_type_code NOT IN ('IUE', 'TSN') --bug 4190357 excluded TSN
2903 -- AND (p_line_id is null or (p_line_id is not null and lines.line_id = p_line_id and lines.line_type_code = 'LINE'))
2904 -- AND (l_booked_flag = 'N' or ldets.list_line_type_code<>'IUE')
2905 AND LDETS.PRICE_ADJUSTMENT_ID NOT IN (
2906 SELECT PRICE_ADJUSTMENT_ID
2907 FROM OE_PRICE_ADJUSTMENTS
2908 WHERE PRICE_ADJUSTMENT_ID = LDETS.PRICE_ADJUSTMENT_ID
2909 ) --Bug#10193597
2910 );
2911
2912 IF l_debug_level > 0 THEN
2913 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' ADJUSTMENTS' ) ;
2914 END IF;
2915
2916
2917 INSERT INTO OE_PRICE_ADJ_ASSOCS
2918 ( PRICE_ADJUSTMENT_ID
2919 ,CREATION_DATE
2920 ,CREATED_BY
2921 ,LAST_UPDATE_DATE
2922 ,LAST_UPDATED_BY
2923 ,LAST_UPDATE_LOGIN
2924 ,PROGRAM_APPLICATION_ID
2925 ,PROGRAM_ID
2926 ,PROGRAM_UPDATE_DATE
2927 ,REQUEST_ID
2928 ,PRICE_ADJ_ASSOC_ID
2929 ,LINE_ID
2930 ,RLTD_PRICE_ADJ_ID
2931 ,LOCK_CONTROL
2932 )
2933 (SELECT /*+ ORDERED USE_NL(QPL ADJ RADJ) */
2934 LDET.price_adjustment_id
2935 ,sysdate --p_Line_Adj_Assoc_Rec.creation_date
2936 ,fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
2937 ,sysdate --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
2938 ,fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
2939 ,fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
2940 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
2941 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
2942 ,NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
2943 ,NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
2944 ,OE_PRICE_ADJ_ASSOCS_S.nextval
2945 ,NULL
2946 ,RLDET.PRICE_ADJUSTMENT_ID
2947 ,1
2948 FROM
2949 QP_PREQ_RLTD_LINES_TMP RLTD,
2950 QP_PREQ_LDETS_TMP LDET,
2951 QP_PREQ_LDETS_TMP RLDET,
2952 QP_PREQ_LINES_TMP RLINE
2953 WHERE
2954 LDET.LINE_DETAIL_INDEX = RLTD.LINE_DETAIL_INDEX AND
2955 RLDET.LINE_DETAIL_INDEX = RLTD.RELATED_LINE_DETAIL_INDEX AND
2956 LDET.PRICING_STATUS_CODE = 'N' AND
2957 LDET.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) AND
2958 nvl(LDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
2959 lDET.CREATED_FROM_LIST_TYPE_CODE NOT IN ('PRL','AGR') AND
2960 lDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
2961 RLDET.PRICE_ADJUSTMENT_ID IS NOT NULL AND
2962 RLDET.PRICING_STATUS_CODE = 'N' AND
2963 --Start Bug 16467223, commenting below line and added the next line for checking in different pricess_code
2964 --RLDET.PROCESS_CODE = 'N' AND
2965 RLDET.PROCESS_CODE IN (QP_PREQ_PUB.G_STATUS_NEW,QP_PREQ_PUB.G_STATUS_UNCHANGED,QP_PREQ_PUB.G_STATUS_UPDATED) AND
2966 --End Bug 16467223
2967 nvl(RLDET.AUTOMATIC_FLAG, 'N') = 'Y' AND
2968 -- not in might not be needed
2969 RLDET.PRICE_ADJUSTMENT_ID
2970 NOT IN (SELECT RLTD_PRICE_ADJ_ID
2971 FROM OE_PRICE_ADJ_ASSOCS
2972 WHERE PRICE_ADJUSTMENT_ID = LDET.PRICE_ADJUSTMENT_ID ) AND
2973 RLTD.PRICING_STATUS_CODE = 'N'
2974 AND RLINE.LINE_INDEX = RLDET.LINE_INDEX
2975 AND RLINE.PROCESS_STATUS <> 'NOT_VALID' );
2976
2977
2978
2979 IF l_debug_level > 0 THEN
2980 oe_debug_pub.add( 'INSERTED '||SQL%ROWCOUNT||' PRICE ADJ ASSOCS' , 3 ) ;
2981 END IF;
2982 Exception
2983 WHEN OTHERS THEN
2984 IF l_debug_level > 0 THEN
2985 oe_debug_pub.add('ERROR in inserting adjustments and associations'||sqlerrm);
2986 END IF;
2987 Raise FND_API.G_EXC_ERROR;
2988 END Insert_Adj;
2989
2990 end OE_ADV_PRICE_PVT;