1 PACKAGE BODY OE_MARGIN_PVT AS
2 /* $Header: OEXVMRGB.pls 120.12.12020000.3 2013/02/01 10:05:32 nilegupt ship $ */
3
4 G_CUSTOM_COST VARCHAR2(3);
5 G_SHIP_FROM_ORG_ID NUMBER:=-1;
6 G_PROJECT_ID NUMBER:=-1;
7 G_COST_GROUP_ID NUMBER:=-1; --bug 14282027
8 G_HEADER_ID NUMBER:=-1;
9 G_MIN_MARGIN_PERCENT NUMBER:=-1;
10 G_COMPUTE_METHOD VARCHAR2(5):=NULL;
11 G_SOB_CURRENCY VARCHAR2(15):=NULL;
12 G_DEBUG VARCHAR2(1):=NULL;
13
14 procedure debug
15 (p_text In Varchar2
16 ,p_level In Number Default 5
17 )
18 IS
19
20 --
21 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
22 --
23 Begin
24 IF G_DEBUG IS NULL THEN
25 IF OE_DEBUG_PUB.G_DEBUG = FND_API.G_TRUE THEN
26 G_DEBUG := 'Y';
27 ELSE
28 G_DEBUG := 'N';
29 END IF;
30 END IF;
31
32 IF G_DEBUG = 'Y' THEN
33 IF l_debug_level > 0 THEN
34 oe_debug_pub.add( P_TEXT , NVL ( P_LEVEL , 5 ) ) ;
35 END IF;
36 END IF;
37 End;
38
39 procedure cost_action
40 (
41 p_selected_records Oe_Globals.Selected_Record_Tbl
42 ,P_cost_level varchar2
43 )
44
45 is
46
47 l_request_rec Oe_Order_Pub.Request_Rec_Type DEFAULT Oe_Order_Pub.G_MISS_REQUEST_REC;
48 l_line_id number;
49 l_header_id number;
50 l_unit_cost number;
51 j number;
52 l_Line_Tbl oe_order_pub.line_tbl_type;
53 l_header_flag boolean;
54 i number;
55 l_org_id Number;
56 l_prev_org_id Number;
57 --
58 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
59 --
60 begin
61 debug('Inside oe_margin_pvt_1.cost_action',1);
62 If P_cost_level ='LINE' then
63 debug('Inside cost level- line',1);
64
65 --MOAC PI
66 i := p_selected_records.first;
67 while i is not null loop
68 l_line_id := p_selected_records(i).id1;
69 l_org_id := p_selected_records(i).org_id;
70 If l_prev_org_id is null or l_prev_org_id <> l_org_id Then
71 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_Org_Id);
72 l_prev_org_id := l_org_id;
73 End If;
74 l_request_rec.entity_id := l_line_id ;
75 l_unit_cost := Oe_Margin_Pvt.Get_Cost(p_request_rec => l_request_rec);
76 i := p_selected_records.next(i);
77 End loop;
78 --MOAC PI
79
80 Else
81 debug('cost level header',1);
82 --MOAC PI
83 i := p_selected_records.first;
84 while i is not null loop
85 l_Header_id := p_selected_records(i).id1;
86 l_org_id := p_selected_records(i).org_id;
87 If l_prev_org_id is null or l_prev_org_id <> l_org_id Then
88 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_Org_Id);
89 l_prev_org_id := l_org_id;
90 End If;
91 oe_Line_util.query_rows(p_header_id => l_Header_id, x_line_tbl => l_Line_Tbl);
92 j := l_Line_Tbl.First;
93 While j Is not null loop
94 l_request_rec.entity_id := l_Line_Tbl(j).line_id;
95 debug('l_request_rec.entity_id = '||l_request_rec.entity_id,1);
96 l_header_flag := TRUE;
97 l_unit_cost := Oe_Margin_Pvt.Get_Cost(p_request_rec => l_request_rec,p_line_rec => l_Line_Tbl(j), p_header_flag => l_header_flag);
98 j := l_Line_Tbl.Next(j);
99 End loop;
100 i := p_selected_records.next(i);
101 End Loop;
102 --MOAC PI
103 End if;
104 End;
105
106 ------------------------------------------------------------------
107 --Check_manual_released_holds
108 --This function is to check is a hold was being manually released.
109 ------------------------------------------------------------------
110 Function CHECK_MANUAL_RELEASED_HOLDS (
111 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
112 p_header_id IN NUMBER,
113 p_line_id IN NUMBER DEFAULT NULL
114 )
115 RETURN Varchar2
116 IS
117 l_hold_release_id number;
118 l_dummy VARCHAR2(1);
119 l_manual_hold_exists varchar2(1) := 'N';
120 l_released_rec_exists varchar2(1) := 'Y';
121 --
122 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
123 --
124
125 --added for BUG#9728597 Start
126 cursor released_hold is
127 SELECT ohr.HOLD_RELEASE_ID
128 FROM OE_ORDER_HOLDS h,
129 OE_HOLD_SOURCES s,
130 oe_hold_releases ohr
131 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
132 AND H.HEADER_ID = p_header_id
133 AND H.LINE_ID IS NULL
134 AND H.HOLD_RELEASE_ID IS NOT NULL
135 AND S.HOLD_ID = p_hold_id
136 AND S.HOLD_ENTITY_CODE = 'O'
137 AND S.HOLD_ENTITY_ID = p_header_id
138 AND S.RELEASED_FLAG ='Y'
139 AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
140 ORDER BY ohr.creation_date DESC;
141
142 cursor released_hold_line is
143 SELECT ohr.HOLD_RELEASE_ID
144 FROM OE_ORDER_HOLDS h,
145 OE_HOLD_SOURCES s,
146 oe_hold_releases ohr
147 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
148 AND H.HEADER_ID = p_header_id
149 AND H.LINE_ID = p_line_id
150 AND H.HOLD_RELEASE_ID IS NOT NULL
151 AND S.HOLD_ID = p_hold_id
152 AND S.HOLD_ENTITY_CODE = 'O'
153 AND S.HOLD_ENTITY_ID = p_header_id
154 AND S.RELEASED_FLAG ='Y'
155 AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
156 ORDER BY ohr.creation_date DESC;
157 --added for BUG#9728597 End
158
159 BEGIN
160 debug('Entering OE_MARGIN_PUB.Check_Manual_Released_Holds');
161 Debug(' Checking for Manually Released Holds on header_id'||
162 to_char(p_header_id) );
163
164 IF p_line_id IS NULL THEN
165 BEGIN
166 --commented for BUG#9728597
167 /* SELECT NVL(MAX(H.HOLD_RELEASE_ID),0)
168 INTO l_hold_release_id
169 FROM OE_ORDER_HOLDS h,
170 OE_HOLD_SOURCES s
171 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
172 AND H.HEADER_ID = p_header_id
173 AND H.LINE_ID IS NULL
174 AND H.HOLD_RELEASE_ID IS NOT NULL
175 AND S.HOLD_ID = p_hold_id
176 AND S.HOLD_ENTITY_CODE = 'O'
177 AND S.HOLD_ENTITY_ID = p_header_id
178 AND S.RELEASED_FLAG ='Y';
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 Debug('No Released record for Margin Holds');
182 l_released_rec_exists := 'N';
183 WHEN OTHERS THEN
184 null;*/
185 --commented for BUG#9728597
186
187 --added for BUG#9728597 Start
188 OPEN released_hold;
189 FETCH released_hold INTO l_hold_release_id;
190 IF (released_hold%notfound) THEN
191 Debug('No Released record for Margin Holds');
192 l_released_rec_exists := 'N';
193 END IF;
194 CLOSE released_hold;
195 EXCEPTION
196 WHEN OTHERS THEN
197 oe_debug_pub.add('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
198 l_released_rec_exists := 'N';
199 --added for BUG#9728597 End
200
201 END;
202
203 ELSE
204 BEGIN
205 --commented for BUG#9728597
206 /*SELECT NVL(MAX(H.HOLD_RELEASE_ID),0)
207 INTO l_hold_release_id
208 FROM OE_ORDER_HOLDS h,
209 OE_HOLD_SOURCES s
210 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
211 AND H.HEADER_ID = p_header_id
212 AND H.LINE_ID = p_line_id
213 AND H.HOLD_RELEASE_ID IS NOT NULL
214 AND S.HOLD_ID = p_hold_id
215 AND S.HOLD_ENTITY_CODE = 'O'
216 AND S.HOLD_ENTITY_ID = p_header_id
217 AND S.RELEASED_FLAG ='Y';
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 Debug('No Released record for margin Holds');
221 l_released_rec_exists := 'N';
222 WHEN OTHERS THEN
223 null;*/
224 --commented for BUG#9728597
225
226 --added for BUG#9728597 Start
227 OPEN released_hold_line;
228 FETCH released_hold_line INTO l_hold_release_id;
229 IF (released_hold_line%notfound) THEN
230 Debug('No Released record for Margin Holds');
231 l_released_rec_exists := 'N';
232 END IF;
233 CLOSE released_hold_line;
234 EXCEPTION
235 WHEN OTHERS THEN
236 oe_debug_pub.add('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
237 l_released_rec_exists := 'N';
238 --added for BUG#9728597 End
239
240 END;
241
242 END IF; -- end if p_line_id is null
243
244 IF l_released_rec_exists = 'Y' THEN
245 BEGIN
246 select 'Y'
247 into l_manual_hold_exists
248 FROM OE_HOLD_RELEASES
249 WHERE HOLD_RELEASE_ID = l_hold_release_id
250 AND RELEASE_REASON_CODE <> 'PASS_MIN_MARGIN'
251 AND CREATED_BY <> 1;
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 Debug('No Manually Released Margin Holds');
255 l_manual_hold_exists := 'N';
256 WHEN OTHERS THEN
257 null;
258 END;
259 END IF;
260
261 Debug(' Manual Holds Exists:' || l_manual_hold_exists );
262 debug('Leaving OE_MARGIN_PUB.Check_Manual_Released_Holds');
263 return l_manual_hold_exists;
264
265 End CHECK_MANUAL_RELEASED_HOLDS;
266
267
268 ----------------------------------------------------------------
269 FUNCTION Get_Cost (p_line_rec IN OE_ORDER_PUB.LINE_REC_TYPE DEFAULT OE_Order_Pub.G_MISS_LINE_REC
270 ,p_request_rec IN Oe_Order_Pub.Request_Rec_Type DEFAULT Oe_Order_Pub.G_MISS_REQUEST_REC
271 ,p_order_currency IN VARCHAR2 Default NULL
272 ,p_sob_currency IN VARCHAR2 Default NULL
273 ,p_inventory_item_id IN NUMBER Default NULL
274 ,p_ship_from_org_id IN NUMBER Default NULL
275 ,p_conversion_Type_code IN VARCHAR2 Default NULL
276 ,p_conversion_rate IN NUMBER Default NULL
277 ,p_item_type_code IN VARCHAR2 Default 'STANDARD'
278 ,p_header_flag IN Boolean Default FALSE
279 ,p_ordered_uom IN VARCHAR2 Default NULL --Bug 11880399
280 )
281 ----------------------------------------------------------------
282 RETURN NUMBER IS
283 l_line_rec OE_ORDER_PUB.LINE_REC_TYPE;
284 l_unit_cost NUMBER;
285 l_cost_group_id NUMBER;
286 l_item_rec OE_ORDER_CACHE.item_rec_type;
287 l_set_of_books Oe_Order_Cache.Set_Of_Books_Rec_Type;
288 l_order_currency VARCHAR2(30);
289 l_set_of_books_id VARCHAR2(30);
290 l_sob_currency VARCHAR2(30);
291 l_old_unit_cost NUMBER;
292 l_control_rec OE_GLOBALS.Control_Rec_Type;
293 l_line_tbl Oe_Order_Pub.Line_Tbl_Type;
294 l_old_line_tbl Oe_Order_Pub.Line_Tbl_Type;
295 l_return_status VARCHAR2(30);
296 l_denominator NUMBER;
297 l_numerator NUMBER;
298 l_rate NUMBER;
299 l_conversion_type_code VARCHAR2(30);
300 l_conversion_rate NUMBER;
301 l_PA_CALL Boolean := FALSE;
302 l_result_code VARCHAR2(30);
303 l_no_of_rows NUMBER;
304 l_cost_mthd VARCHAR2(15);
305 l_cmpnTcls NUMBER;
306 l_analysis_code VARCHAR2(15);
307 l_whse_code VARCHAR2(15);
308 l_orgn_code VARCHAR2(15);
309 l_inventory_org_id number;
310 l_uom_rate NUMBER;
311 -- INVCONV
312 l_status VARCHAR2(1);
313 l_msg_count NUMBER;
314 l_msg_data VARCHAR2(2000);
315 l_ind NUMBER;
316 l_result varchar2(30);
317 l_primary_uom_code VARCHAR2(30); --Bug 11880399
318
319 --INVCONV
320 /*Cursor OPM_CODE(p_organization_id Number) Is -- INVCONV
321 SELECT w.whse_code
322 , s.orgn_code
323 FROM mtl_parameters p
324 , ic_whse_mst w
325 , sy_orgn_mst s
326 , gl_plcy_mst plcy
327 WHERE plcy.co_code = s.co_code
328 AND w.mtl_organization_id = p.organization_id
329 AND s.orgn_code = w.orgn_code
330 AND s.orgn_code = p.process_orgn_code
331 AND p.process_enabled_flag ='Y'
332 AND s.delete_mark = 0
333 AND w.delete_mark = 0
334 AND p.ORGANIZATION_ID = p_organization_id
335 AND rownum < 2; */
336
337 --
338 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
339 --
340 CURSOR drop_ship_line_cost IS
341 SELECT POL.UNIT_PRICE
342 FROM PO_LINES_ALL POL,
343 OE_DROP_SHIP_SOURCES OEDSS
344 WHERE OEDSS.LINE_ID = l_line_rec.line_id
345 AND OEDSS.PO_RELEASE_ID IS NULL
346 AND POL.PO_LINE_ID = OEDSS.PO_LINE_ID
347 UNION
348 SELECT PRL.UNIT_PRICE
349 FROM PO_REQUISITION_LINES_ALL PRL,
350 OE_DROP_SHIP_SOURCES OEDSS
351 WHERE OEDSS.LINE_ID = l_line_rec.line_id
352 AND OEDSS.PO_LINE_ID IS NULL
353 AND PRL.REQUISITION_LINE_ID = OEDSS.REQUISITION_LINE_ID
354 UNION
355 SELECT POLL.PRICE_OVERRIDE UNIT_PRICE
356 FROM PO_LINE_LOCATIONS_ALL POLL,
357 OE_DROP_SHIP_SOURCES OEDSS
358 WHERE OEDSS.LINE_ID = l_line_rec.line_id
359 AND OEDSS.PO_LINE_ID IS NOT NULL
360 AND POLL.LINE_LOCATION_ID = OEDSS.LINE_LOCATION_ID
361 AND OEDSS.PO_RELEASE_ID IS NOT NULL;
362
363
364 BEGIN
365 debug('Entering Oe_Margin_Pvt.get_cost');
366
367 --Not yet decided. We might allow user to write their
368 --own api to get a custom code...
369 IF G_CUSTOM_COST IS NOT NULL THEN
370 G_CUSTOM_COST:=Fnd_Profile.value('ONT_GET_CUSTOM_COST');
371 END IF;
372
373 IF G_COMPUTE_METHOD IS NULL THEN
374 G_COMPUTE_METHOD:=Oe_Sys_Parameters.Value('COMPUTE_MARGIN');
375 END IF;
376
377 IF G_COMPUTE_METHOD = 'N' THEN
378 debug(' Not computing cost, compute method is N');
379 RETURN NULL;
380 END IF;
381
382
383 BEGIN
384 IF p_request_rec.entity_id IS NOT NULL OR
385 p_request_rec.entity_id <> FND_API.G_MISS_NUM
386 AND p_header_flag = FALSE
387 THEN
388 debug('query line');
389 -- bug 4642569 begin replace expensive query_row with direct select
390 OE_ORDER_UTIL.Return_Glb_Ent_Index(OE_GLOBALS.G_ENTITY_LINE,
391 p_request_rec.entity_id,
392 l_ind,
393 l_result,
394 l_return_status);
395 IF l_debug_level > 0 THEN
396 oe_debug_pub.add( 'INDEX=' || L_IND , 1 ) ;
397 oe_debug_pub.add( 'L_RETURN_STATUS =' || L_RETURN_STATUS , 1 ) ;
398 oe_debug_pub.add( 'L_RESULT =' || L_RESULT , 1 ) ;
399 END IF;
400
401 IF l_result = FND_API.G_TRUE then
402 l_line_rec := OE_ORDER_UTIL.G_Line_Tbl(l_ind);
403 ELSE
404 l_line_rec := oe_line_util.query_row(p_request_rec.entity_id);
405 END IF;
406 -- bug 4642569 end
407 ELSE
408 debug('dont query');
409 debug('passed in line_id:'||p_line_rec.line_id);
410 l_line_rec := p_line_rec;
411 END IF;
412 EXCEPTION
413 WHEN NO_DATA_FOUND THEN
414 debug('p_request_rec.entity_id =' || p_request_rec.entity_id);
415 l_return_status := FND_API.G_RET_STS_ERROR;
416 END;
417
418 --RT{
419 IF l_line_rec.retrobill_request_id IS NOT NULL
420 and l_line_rec.retrobill_request_id <> FND_API.G_MISS_NUM THEN
421 debug(' Not computing cost, retrobill line');
422 RETURN NULL;
423 END IF;
424 --RT}
425
426 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
427 RETURN NULL;
428 END IF;
429
430 IF l_line_rec.line_id IS NULL
431 AND p_sob_currency IS NOT NULL
432 AND p_inventory_item_id IS NOT NULL
433 AND p_order_currency IS NOT NULL
434 AND p_ship_from_org_id IS NOT NULL
435 THEN
436 --PRICING and AVAILBLITY CALL, they do not have line record
437 --Therefore they do not pass in p_line_record
438 l_PA_CALL := TRUE;
439 G_SHIP_FROM_ORG_ID := p_ship_from_org_id;
440
441 --Bug 7347299 starts
442 --G_SOB_CURRENCY := p_sob_currency;
443 --getting set of book ID for this ship_from_org
444 BEGIN
445 SELECT SET_OF_BOOKS_ID
446 INTO l_set_of_books_id
447 FROM ORG_ORGANIZATION_DEFINITIONS
448 WHERE ORGANIZATION_ID = G_SHIP_FROM_ORG_ID;
449 EXCEPTION
450 WHEN OTHERS THEN
451 debug(' Error:'||SQLERRM);
452 END;
453
454 --getting currency based on the set of book id
455 BEGIN
456 SELECT Currency_Code
457 INTO G_SOB_CURRENCY
458 FROM OE_GL_SETS_OF_BOOKS_V
459 WHERE SET_OF_BOOKS_ID = l_set_of_books_id;
460 EXCEPTION
461 WHEN OTHERS THEN
462 debug(' Error:'||SQLERRM);
463 END;
464 --Bug 7347299 ends
465
466 debug(' PA call set to true');
467 ELSIF l_line_rec.line_id IS NULL THEN
468 debug(' Invalid get_cost call...Returning');
469 RETURN NULL;
470 END IF;
471
472
473 l_old_unit_cost := l_line_rec.unit_cost;
474
475
476 IF nvl(l_line_rec.inventory_item_id,p_inventory_item_id) IS NULL THEN
477 debug(' Return null because inventory_item_id passed in is null');
478 RETURN NULL;
479 END IF;
480
481 IF nvl(l_line_rec.item_type_code,p_item_type_code) IN ('KIT','MODEL','INCLUDED','CLASS','CONFIG','OPTION') THEN
482 debug(' This item type is not supported:'||nvl(l_line_rec.item_type_code,p_item_type_code));
483 RETURN NULL;
484 END IF;
485
486 IF NOT l_PA_CALL THEN
487 IF l_line_rec.open_flag = 'N' OR l_line_rec.shipped_quantity = l_line_rec.ordered_quantity THEN
488 debug(' Line is either closed or shipped, no new cost will be fetched');
489 RETURN l_line_rec.unit_cost;
490 END IF;
491 END IF;
492
493 --A drop shipment line, getting cost from
494 --Try to get cost from drop ship views (PO).
495 --If record no available that means PO has not been created, then we need to get cost from
496 --mtl_system_items_kfv
497 IF l_line_rec.source_type_code = 'EXTERNAL' THEN
498 debug(' This is a drop ship line');
499
500 /* begin bug 3181730: the following SQL consumes over 1MB memory
501 replace with direct table join
502
503 BEGIN
504 SELECT unit_price
505 INTO l_unit_cost
506 FROM oe_drop_ship_links_v
507 WHERE line_id = l_line_rec.line_id;
508
509
510 EXCEPTION
511
512 WHEN NO_DATA_FOUND THEN
513 end comment out for 3181730*/
514
515 l_unit_cost := NULL;
516 OPEN drop_ship_line_cost;
517 FETCH drop_ship_line_cost INTO l_unit_cost;
518 CLOSE drop_ship_line_cost;
519
520 if l_unit_cost IS NULL then
521 --PO has not been created yet. Getting the cost from mtl_systems_item_kfv
522 debug(' PO has not been created yet. Getting the cost from mtl_system_item:item:'||l_line_rec.inventory_item_id||' Ship from org id:'||l_line_rec.ship_from_org_id);
523
524 /* end bug 3181730 */
525 BEGIN
526 select inventory_organization_id into l_inventory_org_id from financials_system_parameters; --bug 2733946
527
528 SELECT list_price_per_unit
529 INTO l_unit_cost
530 FROM mtl_system_items_kfv
531 WHERE inventory_item_id = nvl(l_line_rec.inventory_item_id,p_inventory_item_id)
532 AND organization_id = l_inventory_org_id; --nvl(l_line_rec.ship_from_org_id,p_ship_from_org_id);
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 debug(' Error in retrieving cost for drop ship lines:'||SQLERRM);
537 END;
538 END IF;
539 END IF;
540
541 debug(' Drop ship cost:'|| l_unit_cost);
542 IF l_line_rec.source_type_code = 'INTERNAL'
543 AND NOT l_PA_CALL
544 THEN
545
546 IF nvl(G_SHIP_FROM_ORG_ID,-1) <> nvl(l_line_rec.ship_from_org_id,-1) OR
547 -- IF G_SHIP_FROM_ORG_ID <> l_line_rec.ship_from_org_id OR bug 6709490/6518329
548 NVL(G_PROJECT_ID,-1) <> NVL(l_line_rec.project_id,-1) THEN
549
550 --cache the value, if it is the same we don't want to hit the db again
551 G_SHIP_FROM_ORG_ID := l_line_rec.ship_from_org_id;
552 G_SOB_CURRENCY := NULL; -- bug 6709490/6518329
553 debug(' Line org is different');
554
555 IF l_line_rec.project_id IS NULL THEN
556
557 G_PROJECT_ID := NULL;
558 SELECT NVL(default_cost_group_id,-1)
559 INTO l_cost_group_id
560 FROM mtl_parameters
561 WHERE organization_id = G_SHIP_FROM_ORG_ID;
562 G_COST_GROUP_ID := l_cost_group_id; --bug14282027
563 IF l_debug_level > 0 THEN
564 oe_debug_pub.add( ' COST GROUP ID FOR NONE PROJECT ITEM:'||L_COST_GROUP_ID ) ;
565 END IF;
566
567 ELSE
568
569 G_PROJECT_ID := l_line_rec.project_id;
570 SELECT NVL(costing_group_id,-1)
571 INTO l_cost_group_id
572 FROM pjm_project_parameters ppp
573 WHERE ppp.project_id = l_line_rec.project_id
574 AND ppp.organization_id = G_SHIP_FROM_ORG_ID;
575 G_COST_GROUP_ID := l_cost_group_id; --bug14282027
576
577 END IF;
578
579 END IF;
580
581 -- INVCONV
582 If l_item_rec.primary_uom_code is null or l_item_rec.primary_uom_code = fnd_api.g_miss_char then
583 l_item_rec := OE_Order_Cache.Load_Item(l_line_rec.inventory_item_id,
584 G_SHIP_FROM_ORG_ID);
585 End if;
586
587 -- IF Process org call OPM API to get cost
588 IF l_item_rec.process_warehouse_flag = 'Y' then
589
590 l_result_code:=GMF_CMCOMMON.Get_Process_Item_Cost
591 (p_api_version =>1
592 , p_init_msg_list => FND_API.G_FALSE
593 , x_return_status => l_return_status
594 , x_msg_count => l_msg_count
595 , x_msg_data => l_msg_count
596 , p_inventory_item_id =>l_line_rec.inventory_item_id
597 , p_organization_id =>G_SHIP_FROM_ORG_ID /*Inventory Organization Id */
598 , p_transaction_date =>nvl(l_line_rec.actual_shipment_date,nvl(l_line_rec.fulfillment_date,sysdate)) /* Cost as on date */
599 , p_detail_flag =>1 /* same as retrieve indicator: */
600 /* 1 = total cost, 2 = details; */
601 /* 3 = cost for a specific component
602 class/analysis code, etc. */
603 , p_cost_method =>l_cost_mthd /* OPM Cost Method */
604 , p_cost_component_class_id =>l_cmpntcls
605 , p_cost_analysis_code => l_analysis_code
606 , x_total_cost =>l_unit_cost /* total cost */
607 , x_no_of_rows => l_no_of_rows /* number of detail rows retrieved */
608 );
609
610 -- INVCONV
611
612 debug(' Result code from process get_cost api:'||l_result_code);
613 debug(' Unit cost for Process org item before convert:'||l_unit_cost);
614 debug('primary_uom_code : '||l_item_rec.primary_uom_code);
615 debug('Order_quantity_uom : '||l_Line_rec.Order_quantity_uom);
616 debug('Inventory_item_id : '||l_Line_rec.Inventory_item_id);
617 If l_item_rec.primary_uom_code <> l_Line_rec.Order_quantity_uom
618 and l_unit_cost is not null and l_unit_cost <> fnd_api.g_miss_num Then
619 INV_CONVERT.INV_UM_CONVERSION(From_Unit => l_Line_rec.Order_quantity_uom
620 ,To_Unit => l_item_rec.primary_uom_code
621 ,Item_ID => l_Line_rec.Inventory_item_id
622 ,Uom_Rate => l_Uom_rate);
623 debug('l_Uom_rate : '||l_Uom_rate);
624 l_unit_cost := l_unit_cost * l_Uom_rate;
625 debug(' Unit cost for Process org item after convert:'||l_unit_cost);
626 End If;
627
628 ELSE -- Regular item call costing api to get cost
629 debug('G_COST_GROUP_ID :'|| G_COST_GROUP_ID); --bug14282027
630 l_unit_cost:=cst_cost_api.get_item_cost
631 (p_api_version=>1
632 ,p_inventory_item_id=>l_line_rec.inventory_item_id
633 ,p_organization_id=>G_SHIP_FROM_ORG_ID
634 ,p_cost_group_id=>G_COST_GROUP_ID --l_cost_group_id bug14282027
635 ,p_cost_type_id=>null
636 ,p_precision=>OE_ORDER_UTIL.G_Precision); -- p_precicion added for bug13861667
637
638 debug(' unit cost before convert:'||l_unit_cost);
639 debug('primary_uom_code : '||l_item_rec.primary_uom_code);
640 If l_item_rec.primary_uom_code is null or l_item_rec.primary_uom_code = fnd_api.g_miss_char then
641 l_item_rec := OE_Order_Cache.Load_Item(l_line_rec.inventory_item_id,
642 G_SHIP_FROM_ORG_ID);
643 End If;
644 debug('Order_quantity_uom : '||l_Line_rec.Order_quantity_uom);
645 debug('Inventory_item_id : '||l_Line_rec.Inventory_item_id);
646 If l_item_rec.primary_uom_code <> l_Line_rec.Order_quantity_uom
647 and l_unit_cost is not null and l_unit_cost <> fnd_api.g_miss_num Then
648 INV_CONVERT.INV_UM_CONVERSION(From_Unit => l_Line_rec.Order_quantity_uom
649 ,To_Unit => l_item_rec.primary_uom_code
650 ,Item_ID => l_Line_rec.Inventory_item_id
651 ,Uom_Rate => l_Uom_rate);
652 debug('l_Uom_rate : '||l_Uom_rate);
653 l_unit_cost := l_unit_cost * l_Uom_rate;
654 debug(' Unit cost for OPM item after convert:'||l_unit_cost);
655 End If;
656 END IF; -- IF l_item_rec.process_warehouse_flag = 'Y' then
657
658 END IF;
659
660
661 debug(' Line ship_from_org_id:'||l_line_rec.ship_from_org_id);
662 debug(' Order ship_from_org_id:'||OE_ORDER_CACHE.g_header_rec.ship_from_org_id);
663
664 --Pricing and Availbility call
665 IF l_PA_CALL THEN
666 SELECT NVL(default_cost_group_id,-1)
667 INTO l_cost_group_id
668 FROM mtl_parameters
669 WHERE organization_id = G_SHIP_FROM_ORG_ID;
670
671 debug(' cost group id for none project item:'||l_cost_group_id);
672
673 l_unit_cost:=cst_cost_api.get_item_cost
674 (p_api_version=>1
675 ,p_inventory_item_id=>p_inventory_item_id
676 ,p_organization_id=>G_SHIP_FROM_ORG_ID
677 ,p_cost_group_id=>l_cost_group_id
678 ,p_cost_type_id=>null
679 ,p_precision=>OE_ORDER_UTIL.G_Precision); -- p_precicion added for bug13861667
680
681 --11880399
682 If p_ordered_uom is not null Then
683 BEGIN
684 SELECT primary_uom_code
685 INTO l_primary_uom_code
686 FROM mtl_system_items
687 WHERE inventory_item_id = p_inventory_item_id
688 AND organization_id = G_SHIP_FROM_ORG_ID;
689 EXCEPTION
690 WHEN OTHERS THEN
691 --need to handle... to be added....
692 debug(' Error:'||SQLERRM);
693 END;
694 END If;
695
696 debug('Order_quantity_uom : '|| p_ordered_uom);
697 debug('Inventory_item_id : '|| p_inventory_item_id);
698 If l_primary_uom_code <> p_ordered_uom
699 and l_unit_cost is not null
700 and p_ordered_uom is not null Then
701
702 debug(' Ordered UOM is not the same as Pricing UOM. ');
703 debug(' Calling INV_UM_CONVERSION for PA_CALL ');
704 debug(' unit cost before convert:'||l_unit_cost);
705 debug(' primary_uom_code : '||l_primary_uom_code);
706
707 INV_CONVERT.INV_UM_CONVERSION(From_Unit => p_ordered_uom
708 ,To_Unit => l_primary_uom_code
709 ,Item_ID => p_inventory_item_id
710 ,Uom_Rate => l_Uom_rate);
711 debug('l_Uom_rate : '||l_Uom_rate);
712 l_unit_cost := l_unit_cost * l_Uom_rate;
713 debug(' PA_CALL:Unit cost for item after convert:'||l_unit_cost);
714 End If;
715
716 --11880399
717
718 END IF;
719
720 IF G_SOB_CURRENCY IS NULL THEN
721 --Global sob currency is not set, execute following to set it
722
723 debug(' getting set of book ID for this ship_from_org');
724 --getting set of book ID for this ship_from_org
725 BEGIN
726 SELECT SET_OF_BOOKS_ID
727 INTO l_set_of_books_id
728 FROM ORG_ORGANIZATION_DEFINITIONS
729 -- WHERE ORGANIZATION_ID = nvl(OE_ORDER_CACHE.g_header_rec.ship_from_org_id,G_SHIP_FROM_ORG_ID);
730 -- bug 6518329/6709490
731 WHERE ORGANIZATION_ID = nvl(G_SHIP_FROM_ORG_ID,OE_ORDER_CACHE.g_header_rec.ship_from_org_id);
732
733 EXCEPTION
734 WHEN OTHERS THEN
735 --need to handle... to be added....
736 debug(' Error:'||SQLERRM);
737 END;
738
739 debug(' getting currency based on the set of book id for the line');
740 --getting currency based on the set of book id for the line
741 BEGIN
742 SELECT Currency_Code
743 INTO G_SOB_CURRENCY
744 FROM OE_GL_SETS_OF_BOOKS_V
745 WHERE SET_OF_BOOKS_ID = l_set_of_books_id;
746 EXCEPTION
747 WHEN OTHERS THEN
748 --need to handle... to be added....
749 debug(' Error:'||SQLERRM);
750 END;
751 END IF;
752
753 IF l_PA_CALL THEN
754 l_order_currency := p_order_currency;
755 ELSE
756 IF OE_ORDER_CACHE.g_header_rec.header_id IS NULL OR
757 OE_ORDER_CACHE.g_header_rec.header_id <> l_line_rec.header_id
758 THEN
759 OE_Header_Util.query_row(p_header_id => l_line_rec.header_id
760 , x_header_rec => OE_ORDER_CACHE.g_header_rec);
761 END IF;
762 l_order_currency := OE_ORDER_CACHE.g_header_rec.transactional_curr_code;
763 END IF;
764
765 DEBUG(' Order currency:'||l_order_currency);
766 DEBUG(' Cost''s sob currency:'||g_sob_currency);
767
768 --Currency different, that is cost from costing api is using different currency
769 --than our order currency, cost will need to be converted to order currency
770 IF l_order_currency <> G_SOB_CURRENCY THEN
771 BEGIN
772
773 IF NOT l_PA_CALL THEN
774 IF OE_ORDER_CACHE.g_header_rec.conversion_type_code = 'User'
775 AND OE_ORDER_CACHE.g_header_rec.conversion_rate IS NULL
776 THEN
777 DEBUG(' USER conversion type without rate, unable to perform cost conversion');
778 RETURN NULL;
779 END IF;
780
781 IF OE_ORDER_CACHE.g_header_rec.conversion_type_code IS NULL THEN
782 DEBUG(' Conversion type not entered in sales order header,unable to perfor cost conversion');
783 RETURN NULL;
784 END IF;
785
786 ELSE
787 IF p_conversion_type_code = 'User' AND p_conversion_rate IS NULL THEN
788 DEBUG(' USER conversion type without rate, unable to perform cost conversion');
789 RETURN NULL;
790 END IF;
791
792 IF p_conversion_type_code IS NULL THEN
793 DEBUG(' Conversion type not entered, unable to perform cost conversion');
794 RETURN NULL;
795 END IF;
796
797 END IF;
798
799 IF NOT l_PA_CALL THEN
800 l_conversion_type_code := OE_ORDER_CACHE.g_header_rec.conversion_type_code;
801 l_conversion_rate := OE_ORDER_CACHE.g_header_rec.conversion_rate;
802 ELSE
803 l_conversion_type_code := p_conversion_type_code;
804 l_conversion_rate := p_conversion_rate;
805 END IF;
806
807 --bug 4695325
808 -- the conversion rate would always be stored in the system in Foreign to Base format irrespective of the profile option DISPLAY_INVERSE_RATE
809 IF l_conversion_rate IS NOT NULL THEN
810 l_conversion_rate := 1/l_conversion_rate;
811 END IF;
812
813 gl_currency_api.convert_closest_amount
814 ( x_from_currency => g_sob_currency
815 , x_to_currency => l_order_currency
816 , x_conversion_date => sysdate
817 , x_conversion_type => l_conversion_type_code
818 , x_amount => l_unit_cost
819 , x_user_rate => l_conversion_rate
820 , x_max_roll_days => -1
821 , x_converted_amount => l_unit_cost
822 , x_denominator => l_denominator
823 , x_numerator => l_numerator
824 , x_rate => l_rate
825 );
826
827 DEBUG(' Converted unit cost:'||l_unit_cost||' rate:'||l_rate);
828 EXCEPTION
829 --will need to handle this later...
830 WHEN OTHERS THEN
831 debug('Gl_Currency_Api.Convert_Amount returns errors:'||SQLERRM);
832 RETURN NULL;
833 END;
834 END IF;
835
836
837
838 IF p_request_rec.entity_id IS NOT NULL
839 OR p_request_rec.entity_id <> FND_API.G_MISS_NUM THEN
840 debug('l_old_unit_cost = '||l_old_unit_cost);
841 IF nvl(l_old_unit_cost,-999.3134) <> l_unit_cost THEN
842 l_control_rec.controlled_operation := FALSE;
843 l_control_rec.write_to_db := TRUE;
844 l_control_rec.change_attributes := TRUE;
845 l_control_rec.default_attributes := FALSE;
846 l_control_rec.validate_entity := FALSE;
847 l_control_rec.clear_dependents := FALSE;
848
849 l_old_line_tbl(1) := l_line_rec;
850 l_line_tbl(1) := l_line_rec;
851 l_line_tbl(1).unit_cost := l_unit_cost;
852 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
853
854 debug('before calling OE_ORDER_PVT.Lines jitesh');
855 OE_ORDER_PVT.Lines(p_validation_level => FND_API.G_VALID_LEVEL_NONE,
856 p_control_rec => l_control_rec,
857 p_x_line_tbl => l_line_tbl,
858 p_x_old_line_tbl => l_old_line_tbl,
859 x_return_status => l_return_status);
860 END IF;
861 END IF;
862 debug('after calling OE_ORDER_PVT.Lines jitesh');
863
864 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
865 RAISE FND_API.G_EXC_ERROR;
866 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868 END IF;
869 debug('just before return oe_margin_pvt.get_cost');
870 debug('l_unit_cost ='||l_unit_cost);
871
872 RETURN l_unit_cost;
873
874 debug('Leaving Oe_Margin_Pvt.get_cost');
875
876 EXCEPTION
877 WHEN OTHERS THEN
878 DEBUG(' OE_MARGIN_PVT:Unable to get cost:'||SQLERRM);
879 Return null;
880 END GET_COST;
881
882 --------------------------------------------------
883 Function Min_Margin_Percent
884 --Return Minimum Margin Percent from setup
885 --------------------------------------------------
886 (p_header_id IN NUMBER) RETURN NUMBER IS
887 l_transaction_type_id NUMBER;
888 l_min_margin_percent NUMBER;
889
890 --
891 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
892 --
893 BEGIN
894 debug('Entering function min_margin_percent');
895 debug(' p_header_id:'||p_header_id);
896 debug(' global header_id:'||G_HEADER_ID);
897
898 IF OE_GLOBALS.Equal(p_header_id,G_HEADER_ID) THEN
899 debug('Leaving function min_margin_percent');
900 RETURN G_MIN_MARGIN_PERCENT;
901 ELSE
902 SELECT a.min_margin_percent
903 INTO l_min_margin_percent
904 FROM OE_TRANSACTION_TYPES_ALL a,
905 OE_ORDER_HEADERS_ALL b
906 WHERE a.transaction_type_id = b.order_type_id
907 AND b.header_id = p_header_id;
908 G_HEADER_ID := p_header_id;
909 G_MIN_MARGIN_PERCENT := l_min_margin_percent;
910 END IF;
911
912 debug('Leaving function min_margin_percent');
913 Return l_min_margin_percent;
914
915 EXCEPTION
916 WHEN OTHERS THEN
917 debug('Error in function get_min_margin_percent:'||SQLERRM);
918 Return -1;
919 END;
920
921 --------------------------------------------------
922 PROCEDURE Get_Order_Margin
923 -------------------------------------------------
924 (p_header_id IN NUMBER,
925 p_org_id IN NUMBER default NULL,
926 x_order_margin_percent OUT NOCOPY NUMBER ,
927
928 x_order_margin_amount OUT NOCOPY NUMBER) IS
929
930 l_compute_method VARCHAR2(1);
931 l_margin_ratio NUMBER;
932 l_margin_amount NUMBER;
933
934 -- {bug 5654745
935 l_total_selling_price Number :=0;
936 l_total_cost Number :=0;
937 l_unit_SP Number;
938 l_unit_cost Number;
939 l_ordered_qty Number;
940
941 CURSOR MARGIN is
942 SELECT ordered_quantity, unit_selling_price, unit_cost
943 FROM OE_ORDER_LINES_ALL
944 WHERE header_id = p_header_id
945 AND unit_cost IS NOT NULL
946 AND line_category_code = 'ORDER';
947 --bug 5654745}
948 --
949 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
950 --
951 BEGIN
952 debug('Entering Oe_Margin_Pvt.Get_Order_Margin');
953 --retrive margin calculation method perference
954 l_compute_method:=Oe_Sys_Parameters.Value('COMPUTE_MARGIN', p_org_id);
955
956 debug(' Margin_Compute_Method:'||l_compute_method);
957
958 IF l_compute_method = 'N' THEN
959 x_order_margin_percent := NULL;
960 x_order_margin_amount := NULL;
961 debug(' Margin not computed system parameter says N');
962 RETURN;
963 End IF;
964
965 --check order type minimum_margin is null... pending
966
967 --check if this is a booked order....pending, maybe need to move somewhere
968 -- {bug 5654745
969 open margin;
970 loop
971 fetch margin into l_ordered_qty,l_unit_SP,l_unit_cost;
972 exit when margin%NOTFOUND;
973 l_total_selling_price := l_total_selling_price + (l_ordered_qty * l_unit_SP);
974 l_total_cost := l_total_cost + (l_ordered_qty * l_unit_cost);
975 end loop;
976 close margin;
977 l_margin_amount := l_total_selling_price-l_total_cost;
978 -- bug 5654745}
979
980 IF l_compute_method = 'P' THEN
981 debug(' Margin based on price');
982 --Margin percent based on price
983
984 -- 3756821 commented the usp > 0
985 /* SELECT SUM(ordered_quantity*(unit_selling_price - unit_cost))/sum(ordered_quantity*unit_selling_price),
986 SUM(ordered_quantity*(unit_selling_price - unit_cost))
987 INTO l_margin_ratio,
988 l_margin_amount
989 FROM OE_ORDER_LINES_ALL
990 WHERE header_id = p_header_id
991 AND unit_cost IS NOT NULL
992 -- AND unit_selling_price > 0
993 AND line_category_code = 'ORDER'; */
994 l_margin_ratio := l_margin_amount/l_total_selling_price; --bug 5654745
995
996 x_order_margin_amount :=l_margin_amount;
997 -- 3756821
998 IF l_margin_amount < 0 THEN
999 --order level margin amount less than 0, making a lost, percent should be negative also
1000 x_order_margin_percent := -1 * ABS(l_margin_ratio * 100);
1001 ELSE
1002 x_order_margin_percent := l_margin_ratio * 100;
1003 END IF;
1004 -- 3756821
1005 debug('Leaving Oe_Margin_Pvt.Get_Order_Margin');
1006 RETURN;
1007
1008 END IF;
1009
1010 IF l_compute_method = 'C' THEN
1011 debug(' Margin based on cost');
1012 --Margin percent based on cost
1013
1014 /* SELECT SUM(ordered_quantity*(unit_selling_price - unit_cost))/sum(ordered_quantity*unit_cost),
1015 SUM(ordered_quantity*(unit_selling_price- unit_cost))
1016 INTO l_margin_ratio,
1017 l_margin_amount
1018 FROM OE_ORDER_LINES_ALL
1019 WHERE header_id = p_header_id
1020 AND unit_cost IS NOT NULL
1021 AND line_category_code = 'ORDER'; */
1022 l_margin_ratio := l_margin_amount/l_total_cost; --bug 5654745
1023
1024 x_order_margin_amount := l_margin_amount;
1025 -- 3756821
1026 IF l_margin_amount < 0 THEN
1027 --order level margin amount less than 0, making a lost, percent should be negative also
1028 x_order_margin_percent := -1 * ABS(l_margin_ratio * 100);
1029 ELSE
1030 x_order_margin_percent := l_margin_ratio * 100;
1031 END IF;
1032 -- 3756821
1033 END IF;
1034
1035 debug('Leaving Oe_Margin_Pvt.Get_Order_Margin');
1036
1037 EXCEPTION
1038 WHEN ZERO_DIVIDE THEN
1039 IF l_compute_method = 'P' THEN
1040 debug(' Oe_Margin_Pvt.Get_Order_Margin ZERO price');
1041
1042 --Good problem to have, user has infinite margin, for sure it should
1043 --pass order margin hold check.
1044 --When null, caller will not continue margin hold/check process
1045
1046 x_order_margin_amount := null;
1047 x_order_margin_percent:= null;
1048
1049 ElSIF l_compute_method = 'C' THEN
1050
1051 debug(' Oe_Margin_Pvt.Get_Order_Margin ZERO cost:');
1052 --Good problem to have, user has infinite margin, for sure should
1053 --pass order margin hold check
1054 --When null, caller will not continue margin hold/check process
1055
1056 x_order_margin_amount := null;
1057 x_order_margin_percent:= null;
1058
1059 ELSE
1060
1061 debug(' Oe_Margin_Pvt.Get_Order_Margin:'||SQLERRM);
1062
1063 END IF;
1064
1065 WHEN OTHERS THEN
1066 debug(' Oe_Margin_Pvt.Get_Order_Margin unable get margin:'||SQLERRM);
1067 End;
1068
1069 ----------------------------------------------
1070 PROCEDURE Margin_Hold
1071 --evaluate margin, hold the order if necessary
1072 ----------------------------------------------
1073 (p_header_id IN NUMBER) IS
1074 l_order_margin_percent NUMBER;
1075 l_min_margin_percent NUMBER;
1076 l_hold_source_rec OE_Holds_Pvt.hold_source_rec_type;
1077 l_hold_release_rec OE_Holds_Pvt.Hold_Release_REC_Type;
1078 l_return_status varchar2(30);
1079 l_x_msg_count number;
1080 l_x_msg_data Varchar2(2000);
1081 l_x_result_out Varchar2(30);
1082 l_line_id NUMBER;
1083 l_order_margin_amount number;
1084 l_manual_released Varchar2(1):= 'N';
1085 l_booked_flag Varchar2(1):='N';
1086 --
1087 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1088 --
1089 BEGIN
1090 debug('Entering Oe_Margin_Pvt.Margin_Hold');
1091
1092 Get_Order_Margin(p_header_id=>p_header_id,
1093 x_order_margin_percent=>l_order_margin_percent,
1094 x_order_margin_amount =>l_order_margin_amount);
1095
1096 IF l_order_margin_percent IS NULL THEN
1097 --Margin not computed, user has set 'N' on compute_method or divide by zero margin
1098 debug(' Order margin percent is Null or compute method is N');
1099 RETURN;
1100 END IF;
1101
1102 l_min_margin_percent:=Min_Margin_Percent(p_header_id);
1103
1104 IF l_min_margin_percent IS NULL THEN
1105 --Margin percent is not set or other errors occurs
1106 debug(' Margin percent is not set or other errors occurs');
1107 RETURN;
1108 END IF;
1109
1110 l_hold_source_rec.hold_id := G_SEEDED_MARGIN_HOLD_ID;
1111 l_hold_source_rec.hold_entity_id := p_header_id;
1112 l_hold_source_rec.header_id := p_header_id;
1113 l_hold_source_rec.Hold_Entity_code := 'O';
1114
1115 -- check if order already on margin hold, place hold if not
1116 OE_Holds_Pub.Check_Holds(
1117 p_api_version => 1.0
1118 ,p_header_id => p_header_id
1119 ,p_line_id => null
1120 ,p_hold_id => l_hold_source_rec.Hold_id
1121 ,x_return_status => l_return_status
1122 ,x_msg_count => l_x_msg_count
1123 ,x_msg_data => l_x_msg_data
1124 ,x_result_out => l_x_result_out
1125 );
1126
1127
1128 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
1129 Debug(' OE_HOLD_PUB.Check_Holds returns unexpected error!');
1130 RAISE FND_API.G_EXC_ERROR;
1131 END IF;
1132
1133 debug(' order_margin_%:'||l_order_margin_percent);
1134 debug(' min_margin_% in setup:'||l_min_margin_percent);
1135
1136 IF l_order_margin_percent < l_min_margin_percent THEN
1137 IF l_x_result_out = FND_API.G_FALSE THEN
1138
1139 --check if this hold had been manually released
1140 --if manually release, do not apply hold again
1141 l_manual_released:=CHECK_MANUAL_RELEASED_HOLDS
1142 (p_hold_id=>G_SEEDED_MARGIN_HOLD_ID,
1143 p_header_id=>p_header_id);
1144
1145 IF l_manual_released = 'N' THEN
1146 OE_HOLDS_PUB.Apply_Holds(
1147 p_api_version => 1.0
1148 ,p_hold_source_rec => l_hold_source_rec
1149 ,x_return_status => l_return_status
1150 ,x_msg_count => l_x_msg_count
1151 ,x_msg_data => l_x_msg_data
1152 );
1153
1154 IF l_return_status = FND_API.g_ret_sts_success then
1155 FND_MESSAGE.SET_NAME('ONT', 'ONT_MARGIN_HOLD_APPLIED');
1156 OE_MSG_PUB.Add;
1157 ELSE
1158 debug('error applying hold',3);
1159 RAISE FND_API.G_EXC_ERROR;
1160 END IF;
1161 END IF;
1162
1163 END IF; --Hold applied check
1164 ELSE
1165 --need to release hold if hold applied
1166 IF l_x_result_out = FND_API.G_TRUE THEN
1167 l_hold_release_rec.release_reason_code :='PASS_MIN_MARGIN';
1168 OE_Holds_Pub.Release_Holds(
1169 p_api_version => 1.0
1170 ,p_hold_source_rec => l_hold_source_rec
1171 ,p_hold_release_rec => l_hold_release_rec
1172 ,x_return_status => l_return_status
1173 ,x_msg_count => l_x_msg_count
1174 ,x_msg_data => l_x_msg_data
1175 );
1176
1177 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1178 debug('Unexpected Error while releasing Margin Hold:'||SQLERRM);
1179 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1180 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1181 debug('Error while releasing Margin Hold');
1182 --RAISE FND_API.G_EXC_ERROR;
1183 END IF;
1184
1185 END IF;
1186 END IF;
1187
1188
1189 debug('Leaving Oe_Margin_Pvt.Margin_Hold');
1190 END;
1191
1192 --------------------------------------------------------------------
1193 --Margin should only avail for pack I
1194 --This is wrapper to a call to OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL
1195 --------------------------------------------------------------------
1196 Function Is_Margin_Avail return Boolean Is
1197 l_release_level Varchar2(15);
1198 l_correct_release Boolean;
1199 --
1200 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1201 --
1202 BEGIN
1203 l_release_level:=Oe_Code_Control.Get_Code_Release_Level;
1204
1205 If l_release_level = 110509 THEN
1206 l_correct_release:=True;
1207 ELSE
1208 l_correct_release:=False;
1209 END IF;
1210
1211 --Always return true first for initial testing purpose. Will turn on
1212 --above logic when checking in the code!
1213 Return True;
1214 END;
1215
1216 Procedure Get_Line_Margin(p_line_rec In OE_ORDER_PUB.LINE_REC_TYPE,
1217 x_unit_cost Out NOCOPY Number,
1218 x_unit_margin_amount Out NOCOPY Number,
1219 x_margin_percent Out NOCOPY Number) As
1220 l_cost Number;
1221 l_margin_amt Number;
1222 l_margin_percent Number;
1223 Begin
1224 l_cost:=Get_Cost(p_line_rec=>p_line_rec);
1225 x_unit_cost:=l_cost;
1226 If p_line_rec.unit_selling_price is Null Then
1227 oe_debug_pub.add('Warning:- unit selling price is null,margin not relevant');
1228 oe_debug_pub.add('Exiting oe_margin_pvt.get_line_margin');
1229 Return;
1230 End If;
1231 l_margin_amt := nvl(p_line_rec.unit_selling_price,0) - nvl(l_cost,0); --bug 5155086
1232 x_unit_margin_amount:=l_margin_amt;
1233
1234 IF G_COMPUTE_METHOD = 'P' THEN
1235 If p_line_rec.unit_selling_price = 0 Then
1236 oe_debug_pub.add('Warning: Price based margin calculation is invalid,because 0 selling price, divided by zero error would occur. Returning');
1237 x_margin_percent:=NULL;
1238 Return;
1239 End If;
1240
1241 l_margin_percent := l_margin_amt/p_line_rec.unit_selling_price*100;
1242 x_margin_percent := l_margin_percent;
1243 Elsif G_COMPUTE_METHOD = 'C' THEN
1244 -- If p_line_rec.unit_selling_price = 0 Then bug5939162
1245 If nvl(l_cost,0) = 0 Then
1246 oe_debug_pub.add('Warning: Cost based margin calculation is invalid,because 0 cost, divided by zero error would occur. Returning');
1247 x_margin_percent:=NULL;
1248 Return;
1249 End If;
1250
1251 x_margin_percent := l_margin_amt/l_cost*100; --added * 100 for bug5155086;
1252 End If;
1253 End;
1254
1255 End OE_MARGIN_PVT;
[Home] [Help]