[Home] [Help]
PACKAGE BODY: APPS.OE_LINE_REPRICE
Source
1 PACKAGE BODY OE_LINE_REPRICE AS
2 /* $Header: OEXVREPB.pls 120.0.12010000.3 2008/11/24 13:40:32 aambasth ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Line_REPRICE';
5
6
7 PROCEDURE Reprice_Line
8 (
9 p_line_rec IN OE_Order_Pub.Line_Rec_Type
10 , p_Repricing_date IN VARCHAR2
11 , p_Repricing_event IN VARCHAR2
12 , p_Honor_Price_Flag IN VARCHAR2
13 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
14 )
15 IS
16 CURSOR check_event_cur(p_repricing_event IN VARCHAR2) IS
17 SELECT qp.modifier_level_code
18 FROM qp_pricing_phases qp, qp_event_phases qe
19 WHERE qp.pricing_phase_id = qe.pricing_phase_id
20 AND qe.pricing_event_code = p_repricing_event;
21
22 l_control_rec OE_GLOBALS.Control_Rec_Type;
23 l_return_status VARCHAR2(1);
24 l_Price_Control_Rec QP_PREQ_GRP.control_record_type;
25 l_x_line_tbl OE_Order_Pub.Line_Tbl_Type;
26 l_line_rec OE_Order_Pub.Line_Rec_Type := p_line_rec;
27 l_line_tbl OE_Order_Pub.Line_Tbl_Type;
28 l_old_line_tbl OE_Order_Pub.Line_Tbl_Type;
29 l_price_flag VARCHAR2(1);
30
31 --
32 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
33 --
34 BEGIN
35
36 IF l_debug_level > 0 THEN
37 oe_debug_pub.add( 'ENTERING OE_LINE_REPRICE.REPRICE_LINE '|| TO_CHAR ( P_LINE_REC.LINE_ID ) , 1 ) ;
38 END IF;
39
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41 -- check if repricing event is LINE level event
42 FOR l_event_rec IN check_event_cur(p_repricing_event) LOOP
43 IF l_debug_level > 0 THEN
44 oe_debug_pub.add( 'EVENT LEVEL CODE IS: '||L_EVENT_REC.MODIFIER_LEVEL_CODE , 1 ) ;
45 END IF;
46 IF NVL(l_event_rec.modifier_level_code, 'ORDER') <> 'LINE' THEN
47 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
48 FND_MESSAGE.SET_NAME('ONT','ONT_REPRICE_INVALID_EVENT');
49 OE_MSG_PUB.Add;
50 oe_line_reprice.set_reprice_status('REPRICE_INVALID_SETUP', p_line_rec.line_id);
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
52 EXIT;
53 END IF;
54 END LOOP;
55
56 If (p_Repricing_Date = 'ACTUAL_SHIPMENT_DATE') THEN
57 l_line_rec.Pricing_Date := p_line_rec.Actual_Shipment_Date;
58 Elsif (p_Repricing_Date = 'SCHEDULE_SHIP_DATE') THEN
59 l_line_rec.Pricing_Date := p_line_rec.Schedule_Ship_Date;
60 Elsif (p_Repricing_Date = 'FULFILLMENT_DATE') THEN
61 l_line_rec.Pricing_Date := p_line_rec.fulfillment_date;
62 Elsif (p_Repricing_Date = 'PROMISE_DATE') THEN
63 l_line_rec.Pricing_Date := p_line_rec.Promise_Date;
64 Elsif (p_Repricing_Date = 'REQUEST_DATE') THEN
65 l_line_rec.Pricing_Date := p_line_rec.Request_Date;
66 Elsif (p_Repricing_Date = 'SYSDATE') THEN
67 l_line_rec.Pricing_Date := sysdate;
68 ELSE
69 -- No change to Pricing Date
70 NULL;
71 End If;
72
73 IF l_debug_level > 0 THEN
74 oe_debug_pub.add( 'REPRICING DATE IS: '||P_REPRICING_DATE , 1 ) ;
75 END IF;
76
77 IF l_line_rec.Pricing_Date IS NULL THEN
78 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
79 FND_MESSAGE.SET_NAME('ONT','ONT_REPRICE_INVALID_DATE');
80 oe_line_reprice.set_reprice_status('REPRICE_INVALID_SETUP', p_line_rec.line_id);
81 OE_MSG_PUB.Add;
82 return;
83 -- do not raise error, instead, exit out of Reprice activity.
84 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 l_Price_Control_Rec.pricing_event := p_Repricing_Event;
88 l_Price_Control_Rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
89 l_Price_Control_Rec.Simulation_Flag := 'N';
90 l_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
91
92 l_x_line_tbl(1) := l_line_rec;
93
94 IF l_debug_level > 0 THEN
95 oe_debug_pub.add( 'REPRICING EVENT '||P_REPRICING_EVENT , 2 ) ;
96 oe_debug_pub.add( 'REPRICING DATE '||L_X_LINE_TBL ( 1 ) .PRICING_DATE , 2 ) ;
97 END IF;
98
99 oe_order_adj_pvt.Price_line(
100 X_Return_Status => l_Return_Status
101 ,p_Line_id => NULL
102 ,p_Request_Type_code=> 'ONT'
103 ,p_Control_rec => l_Price_Control_Rec
104 ,p_Write_To_Db => TRUE --- ?????????????
105 ,x_Line_Tbl => l_x_Line_Tbl
106 ,p_honor_price_flag => p_honor_price_flag);
107
108
109 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
110 oe_line_reprice.set_reprice_status('REPRICE_PRICING_ERROR', p_line_rec.line_id);
111 END IF;
112
113 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118
119 x_return_status := l_return_status;
120
121 EXCEPTION
122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
124
125 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
126 THEN
127 OE_MSG_PUB.Add_Exc_Msg
128 ( G_PKG_NAME,
129 'Reprice_Line'
130 );
131 END IF;
132
133 WHEN FND_API.G_EXC_ERROR THEN
134 x_return_status := FND_API.G_RET_STS_ERROR;
135 WHEN OTHERS THEN
136 oe_line_reprice.set_reprice_status('REPRICE_UNEXPECTED_ERROR',p_line_rec.line_id);
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
138 OE_MSG_PUB.set_msg_context(
139 p_entity_code => 'LINE'
140 ,p_entity_id => l_line_rec.line_id
141 ,p_header_id => l_line_rec.header_id
142 ,p_line_id => l_line_rec.line_id
143 ,p_order_source_id => l_line_rec.order_source_id
144 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
145 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
146 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
147 ,p_change_sequence => l_line_rec.change_sequence
148 ,p_source_document_type_id => l_line_rec.source_document_type_id
149 ,p_source_document_id => l_line_rec.source_document_id
150 ,p_source_document_line_id => l_line_rec.source_document_line_id );
151
152 IF l_debug_level > 0 THEN
153 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
154 oe_debug_pub.add('In others of Reprice line');
155 END IF;
156
157 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
158 THEN
159 OE_MSG_PUB.Add_Exc_Msg
160 ( G_PKG_NAME,
161 'Reprice_Line'
162 );
163 END IF;
164
165 END Reprice_Line;
166
167 /*
168 This procedure is to get the work flow activity attribute for a given
169 item type, item key, activity id and attribute name using work flow
170 engine API GetActivityAttrText.
171 */
172
173 PROCEDURE Get_Activity_Attribute
174 (
175 p_item_type IN VARCHAR2
176 , p_item_key IN VARCHAR2
177 , p_activity_id IN VARCHAR2
178 , p_Reprice_attr_name IN VARCHAR2
179 , x_attribute_value OUT NOCOPY /* file.sql.39 change */ VARCHAR2
180 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
181 )
182 IS
183 l_errname VARCHAR2(30);
184 l_errmsg VARCHAR2(2000);
185 l_errstack VARCHAR2(2000);
186 --
187 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
188 --
189 l_header_id NUMBER;
190 l_orig_sys_line_ref VARCHAR2(50);
191 l_orig_sys_shipment_ref VARCHAR2(50);
192 l_order_source_id NUMBER;
193 l_orig_sys_document_ref VARCHAR2(50);
194 l_change_sequence VARCHAR2(50);
195 l_source_document_type_id NUMBER;
196 l_source_document_id NUMBER;
197 l_source_document_line_id NUMBER;
198 BEGIN
199 x_attribute_value := wf_engine.GetActivityAttrText(p_item_type,p_item_key,p_activity_id,p_Reprice_attr_name);
200
201 x_return_status := FND_API.G_RET_STS_SUCCESS;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 oe_line_reprice.set_reprice_status('REPRICE_UNEXPECTED_ERROR',to_number(p_item_key));
206 select header_id, order_source_id, orig_sys_document_ref,
207 orig_sys_line_ref, orig_sys_shipment_ref, change_sequence,
208 source_document_type_id,source_document_id,source_document_line_id
209 into l_header_id, l_order_source_id, l_orig_sys_document_ref,
210 l_orig_sys_line_ref, l_orig_sys_shipment_ref, l_change_sequence,
211 l_source_document_type_id, l_source_document_id, l_source_document_line_id
212 from oe_order_lines_all
213 where line_id = to_number(p_item_key);
214 OE_MSG_PUB.set_msg_context(
215 p_entity_code => 'LINE'
216 ,p_entity_id => to_number(p_item_key)
217 ,p_header_id => l_header_id
218 ,p_line_id => to_number(p_item_key)
219 ,p_order_source_id => l_order_source_id
220 ,p_orig_sys_document_ref => l_orig_sys_document_ref
221 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
222 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
223 ,p_change_sequence => l_change_sequence
224 ,p_source_document_type_id => l_source_document_type_id
225 ,p_source_document_id => l_source_document_id
226 ,p_source_document_line_id => l_source_document_line_id );
227
228 IF l_debug_level > 0 THEN
229 oe_debug_pub.add( 'WORK FLOW ERROR HAS OCCURED ' , 1 ) ;
230 END IF;
231 WF_CORE.Get_Error(l_errname, l_errmsg, l_errstack);
232 IF l_errname = 'WFENG_ACTIVITY_ATTR' THEN
233 IF l_debug_level > 0 THEN
234 oe_debug_pub.add( 'ERROR MESSAGE '||L_ERRMSG , 1 ) ;
235 END IF;
236 x_attribute_value := 'NONE';
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238 ELSE
239 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
240 THEN
241 OE_MSG_PUB.Add_Exc_Msg
242 ( G_PKG_NAME
243 , 'Get_Activity_Attribute'
244 );
245 END IF;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
247 END IF;
248
249 END Get_Activity_Attribute;
250
251 /*
252 This procedure is called when a line reaches Reprice_LINE work flow
253 activity. It gets the Repricing date and Repricing Event attribute
254 and calls reprice_line to reprice the line.
255 */
256
257 PROCEDURE Process_Repricing
258 (
259 p_api_version_number IN NUMBER
260 , p_line_id IN NUMBER
261 , p_activity_id IN NUMBER
262 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
263 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
264 , x_msg_count OUT NOCOPY /* file.sql.39 change */ VARCHAR2
265 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
266 )
267 IS
268
269 l_line_rec OE_Order_Pub.Line_Rec_Type;
270 l_return_status VARCHAR2(1);
271 l_item_key VARCHAR2(240);
272 l_Repricing_date_attr VARCHAR2(30):='REPRICE_DATE';
273 l_Repricing_date VARCHAR2(30);
274 l_Repricing_event_attr VARCHAR2(30):='REPRICE_EVENTS';
275 l_Repricing_event VARCHAR2(30);
276 l_Honor_Price_Flag_attr VARCHAR2(30):='HONOR_PRICE_FLAG';
277 l_Honor_Price_Flag VARCHAR2(30);
278 l_item_type VARCHAR2(8) := OE_GLOBALS.G_WFI_LIN;
279 --
280 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
281 --
282 BEGIN
283
284 IF l_debug_level > 0 THEN
285 oe_debug_pub.add( 'ENTERING OE_LINE_REPRICE.PROCESS_REPRICING '|| TO_CHAR ( P_LINE_ID ) , 1 ) ;
286 END IF;
287
288 x_result_out := 'COMPLETE';
289
290 OE_Line_Util.Query_Row(p_line_id => p_line_id,
291 x_line_rec => l_line_rec);
292
293 OE_MSG_PUB.set_msg_context(
294 p_entity_code => 'LINE'
295 ,p_entity_id => l_line_rec.line_id
296 ,p_header_id => l_line_rec.header_id
297 ,p_line_id => l_line_rec.line_id
298 ,p_order_source_id => l_line_rec.order_source_id
299 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
300 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
301 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
302 ,p_change_sequence => l_line_rec.change_sequence
303 ,p_source_document_type_id => l_line_rec.source_document_type_id
304 ,p_source_document_id => l_line_rec.source_document_id
305 ,p_source_document_line_id => l_line_rec.source_document_line_id );
306
307
308 -- It is a non shippable line, complete the Reprice activity
309 -- with a result of complete:not_eligible.Fix for bug 2883913.
310
311 /*
312 Commented for bug 7592279 start
313 Removed the condition below to enable repricing of Non Shippable lines
314
315 IF nvl(l_line_rec.shippable_flag, 'N') <> 'Y' THEN
316 x_result_out := 'COMPLETE:NOT_ELIGIBLE';
317 oe_line_reprice.set_reprice_status('REPRICE_NOT_ELIGIBLE', p_line_id);
318 return;
319 END IF;
320 Commented for bug 7592279 end*/
321
322 IF l_debug_level > 0 THEN
323 oe_debug_pub.add( 'ACTIVTITY ID : '||TO_CHAR ( P_ACTIVITY_ID ) , 3 ) ;
324 END IF;
325 l_item_key := to_char(p_line_id);
326
327 IF l_debug_level > 0 THEN
328 oe_debug_pub.add( 'CALLING GET REPRICING ACTIVITY ' , 3 ) ;
329 END IF;
330
331 Get_Activity_Attribute
332 (
333 p_item_type => l_item_type,
334 p_item_key => l_item_key,
335 p_activity_id => p_activity_id,
336 p_Reprice_attr_name => l_Repricing_date_attr,
337 x_attribute_value => l_Repricing_date,
338 x_return_status => l_return_status
339 );
340
341 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347 IF l_debug_level > 0 THEN
348 oe_debug_pub.add( 'REPRICING DATE : '|| L_REPRICING_DATE , 3 ) ;
349 oe_debug_pub.add( 'CALLING GET ATTRIBUTE - REPRICING EVENT ' , 3 ) ;
350 END IF;
351
352 Get_Activity_Attribute
353 (
354 p_item_type => l_item_type,
355 p_item_key => l_item_key,
356 p_activity_id => p_activity_id,
357 p_Reprice_attr_name => l_Repricing_Event_Attr,
358 x_attribute_value => l_Repricing_Event,
359 x_return_status => l_return_status
360 );
361
362 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
365 RAISE FND_API.G_EXC_ERROR;
366 END IF;
367
368 IF l_Repricing_Event IS NULL THEN
369 l_Repricing_Event := 'REPRICE_LINE';
370 End If;
371
372 IF l_debug_level > 0 THEN
373 oe_debug_pub.add( 'REPRICING EVENT : '|| L_REPRICING_EVENT , 3 ) ;
374 END IF;
375
376 Get_Activity_Attribute
377 (
378 p_item_type => l_item_type,
379 p_item_key => l_item_key,
380 p_activity_id => p_activity_id,
381 p_Reprice_attr_name => l_Honor_Price_Flag_attr,
382 x_attribute_value => l_Honor_Price_Flag,
383 x_return_status => l_return_status
384 );
385
386 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
389 RAISE FND_API.G_EXC_ERROR;
390 END IF;
391
392 IF l_Honor_Price_Flag IS NULL THEN
393 l_Honor_Price_Flag := 'Y';
394 End If;
395
396 IF l_debug_level > 0 THEN
397 oe_debug_pub.add( 'HONOR PRICE FLAG : '|| L_HONOR_PRICE_FLAG , 3 ) ;
398 END IF;
399
400 Reprice_Line
401 (
402 p_line_rec => l_line_rec,
403 p_Repricing_date => l_Repricing_date,
404 p_Repricing_event => l_Repricing_event,
405 p_Honor_Price_Flag => l_Honor_Price_Flag,
406 x_return_status => l_return_status
407 );
408
409 IF l_debug_level > 0 THEN
410 oe_debug_pub.add( 'RETURN STATUS FROM REPRICE LINE : '||L_RETURN_STATUS , 3 ) ;
411 END IF;
412
413 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
416 RAISE FND_API.G_EXC_ERROR;
417 END IF;
418
419
420 IF l_debug_level > 0 THEN
421 oe_debug_pub.add( 'EXITING FROM OE_LINE_REPRICE.PROCESS_REPRICING : '||X_RETURN_STATUS , 1 ) ;
422 END IF;
423
424 EXCEPTION
425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
426 IF l_debug_level > 0 THEN
427 oe_debug_pub.add( 'PROCESS_REPRICING : EXITING WITH UNEXPECTED ERROR'||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
428 END IF;
429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
430 x_result_out := 'INCOMPLETE';
431 /* IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
432 THEN
433 OE_MSG_PUB.Add_Exc_Msg
434 ( G_PKG_NAME,
435 'Process_Repricing'
436 );
437 END IF; */
438
439 WHEN FND_API.G_EXC_ERROR THEN
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 x_result_out := 'INCOMPLETE';
442 WHEN OTHERS THEN
443 IF l_debug_level > 0 THEN
444 oe_debug_pub.add('In others of Process_Reprice ');
445 oe_debug_pub.add( 'PROCESS_REPRICING : EXITING WITH OTHERS ERROR' , 1 ) ;
446 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
447 END IF;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
449 x_result_out := 'INCOMPLETE';
450 oe_line_reprice.set_reprice_status('REPRICE_UNEXPECTED_ERROR', p_line_id);
451 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
452 THEN
453 OE_MSG_PUB.Add_Exc_Msg
454 ( G_PKG_NAME
455 , 'Process_Repricing'
456 );
457 END IF;
458
459 -- Get message count and data
460
461 OE_MSG_PUB.Count_And_Get
462 ( p_count => x_msg_count
463 , p_data => x_msg_data
464 );
465
466 END Process_Repricing;
467
468 Procedure set_reprice_status (p_flow_status IN VARCHAR2,
469 p_line_id IN NUMBER)
470 IS
471
472 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
473
474 BEGIN
475
476 IF l_debug_level > 0 THEN
477 oe_debug_pub.add('Setting Flow Stauts code to '||p_flow_status || 'for line id ' ||p_line_id);
478 END IF;
479
480 IF oe_code_control.code_release_level>='110510' THEN
481 Update oe_order_lines_all
482 Set flow_status_code = p_flow_status
483 Where line_id = p_line_id;
484 End IF;
485
486 END set_reprice_status;
487
488 END OE_LINE_REPRICE;
489