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