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