1 PACKAGE BODY OE_Default_Line AS
2 /* $Header: OEXDLINB.pls 120.45.12020000.11 2013/03/28 13:22:50 kadiraju ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Default_Line';
7
8
9 g_line_rec OE_Order_PUB.Line_Rec_Type;
10
11 --9040537 /*8319535 start
12 FUNCTION Get_Def_Invoice_Line_Int
13 (p_return_context IN VARCHAR2,
14 p_return_attribute1 IN VARCHAR2,
15 p_return_attribute2 IN VARCHAR2,
16 p_sold_to_org_id IN NUMBER,
17 p_curr_code IN VARCHAR2,
18 p_ref_line_id OUT NOCOPY NUMBER
19
20 ) RETURN NUMBER;
21 --9040537 8319535 end*/
22
23 /* Added procedure default_active_agr_revision for Bug 2154960 */
24
25 procedure Default_Active_Agr_Revision
26 ( p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type,
27 p_old_line_rec IN OE_Order_PUB.Line_Rec_Type :=
28 OE_Order_PUB.G_MISS_LINE_REC
29 );
30
31 /* Added blanket values defaulting
32 --Made this procedure public
33 --By Srini
34
35
36 PROCEDURE Default_Blanket_Values
37 ( p_blanket_number IN NUMBER,
38 p_cust_po_number IN VARCHAR2,
39 p_ordered_item IN VARCHAR2,
40 p_inventory_item_id IN NUMBER,
41 p_item_identifier_type IN VARCHAR2,
42 p_request_date IN DATE,
43 p_sold_to_org_id IN NUMBER,
44 x_blanket_number OUT NOCOPY NUMBER,
45 x_blanket_line_number OUT NOCOPY NUMBER,
46 x_blanket_version_number OUT NOCOPY NUMBER,
47 x_blanket_request_date OUT NOCOPY DATE
48 );
49 */
50
51 -- bug 4668200
52 PROCEDURE Set_Header_Def_Hdlr_Rec (p_header_id IN NUMBER) ;
53
54 FUNCTION Get_Sold_To
55 RETURN NUMBER
56 IS
57 --
58 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
59 --
60 BEGIN
61
62 OE_ORDER_CACHE.Load_Order_Header(g_line_rec.header_id);
63 RETURN (OE_ORDER_CACHE.g_header_rec.SOLD_TO_ORG_ID);
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
68 THEN
69 OE_MSG_PUB.Add_Exc_Msg
70 ( G_PKG_NAME,
71 'Get_Sold_To'
72 );
73 END IF;
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 END Get_Sold_To;
76
77 FUNCTION Get_Order_Source_Id
78 RETURN NUMBER
79 IS
80 --
81 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
82 --
83 BEGIN
84 --For Bug#7592137
85 IF OE_GLOBALS.G_UI_FLAG then
86 RETURN 0;
87 ELSE
88 OE_ORDER_CACHE.Load_Order_Header(g_line_rec.header_id);
89 IF l_debug_level > 0 THEN
90 oe_debug_pub.add( 'SOURCE ID ='||OE_ORDER_CACHE.G_HEADER_REC.ORDER_SOURCE_ID ) ;
91 END IF;
92 RETURN (OE_ORDER_CACHE.g_header_rec.order_source_id);
93 END IF; --End of Bug#7592137
94
95 EXCEPTION
96 WHEN OTHERS THEN
97 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
98 THEN
99 OE_MSG_PUB.Add_Exc_Msg
100 ( G_PKG_NAME,
101 'Get_Order_Source_Id'
102 );
103 END IF;
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105
106 END Get_Order_Source_Id;
107
108 FUNCTION GET_FREIGHT_CARRIER(p_line_rec OE_ORDER_PUB.Line_Rec_Type,
109 p_old_line_rec OE_ORDER_PUB.Line_Rec_Type)
110 RETURN VARCHAR2
111 IS
112 l_freight_code VARCHAR2(80);
113 --
114 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
115 --
116 begin
117 IF l_debug_level > 0 THEN
118 oe_debug_pub.add( 'ENTER GET FREIGHT CARRIER' ) ;
119 END IF;
120
121
122 IF (p_line_rec.shipping_method_code IS NOT NULL AND
123 p_line_rec.shipping_method_code <> FND_API.G_MISS_CHAR) AND
124 (p_line_rec.ship_from_org_id IS NOT NULL AND
125 p_line_rec.ship_from_org_id<> FND_API.G_MISS_NUM) THEN
126
127 -- 3610480 : Validate freight_carrier_code if shipping_method_code or ship_from_org_id is not null
128 IF (NOT OE_GLOBALS.EQUAL(p_line_rec.shipping_method_code
129 ,p_old_line_rec.shipping_method_code) OR
130 NOT OE_GLOBALS.EQUAL(p_line_rec.ship_from_org_id
131 ,p_old_line_rec.ship_from_org_id) OR
132 NOT OE_GLOBALS.EQUAL(p_line_rec.freight_carrier_code
133 ,p_old_line_rec.freight_carrier_code)) THEN
134
135 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110509' THEN
136
137 SELECT freight_code
138 INTO l_freight_code
139 FROM wsh_carriers wsh_ca,wsh_carrier_services wsh,
140 wsh_org_carrier_services wsh_org
141 WHERE wsh_org.organization_id = p_line_rec.ship_from_org_id
142 AND wsh.carrier_service_id = wsh_org.carrier_service_id
143 AND wsh_ca.carrier_id = wsh.carrier_id
144 AND wsh.ship_method_code = p_line_rec.shipping_method_code
145 AND wsh_org.enabled_flag = 'Y';
146 ELSE
147 Select freight_code
148 into l_freight_code
149 from wsh_carrier_ship_methods
150 where ship_method_code = p_line_rec.shipping_method_code
151 and ORGANIZATION_ID = p_line_rec.ship_from_org_id;
152 END IF;
153
154 IF l_debug_level > 0 THEN
155 oe_debug_pub.add( 'EXIT GET FREIGHT CARRIER' || L_FREIGHT_CODE ) ;
156 END IF;
157 RETURN l_freight_code;
158
159 ELSE
160 IF l_debug_level > 0 THEN
161 oe_debug_pub.add( 'INTO NULL CONDITION' || P_LINE_REC.SHIP_FROM_ORG_ID ) ;
162 END IF;
163 RETURN p_line_rec.freight_carrier_code;
164
165 END IF;
166 ELSE
167 IF l_debug_level > 0 THEN
168 oe_debug_pub.add( 'SHIP FROM OR SHIP METHOD IS NULL/MISSING',1 ) ;
169 END IF;
170 RETURN NULL;
171 END IF;
172
173 IF (p_line_rec.shipping_method_code IS NULL OR
174 p_line_rec.shipping_method_code = FND_API.G_MISS_CHAR) THEN
175 RETURN NULL;
176 END IF;
177
178
179 RETURN p_line_rec.freight_carrier_code;
180
181 EXCEPTION
182
183 WHEN NO_DATA_FOUND THEN
184
185 IF l_debug_level > 0 THEN
186 oe_debug_pub.add( 'NO DATA FOUND GET FREIGHT CARRIER' ) ;
187 END IF;
188 RETURN NULL;
189
190 WHEN OTHERS THEN
191 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
192 THEN
193 OE_MSG_PUB.Add_Exc_Msg
194 ( G_PKG_NAME ,
195 'Get_freight_carrier'
196 );
197 END IF;
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add( 'OTHERS IN GET_FREIGHT_CARRIER' , 1 ) ;
200 END IF;
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202
203
204 END GET_FREIGHT_CARRIER;
205
206 FUNCTION Get_Booked
207 RETURN VARCHAR2
208 IS
209 l_booked_flag VARCHAR2(1);
210 --
211 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
212 --
213 BEGIN
214
215 IF NOT oe_globals.G_HTML_FLAG THEN
216
217 -- use order_header cache instead of sql : bug 4200055
218 if ( OE_Order_Cache.g_header_rec.header_id <> FND_API.G_MISS_NUM
219 and OE_Order_Cache.g_header_rec.header_id IS NOT NULL
220 and OE_Order_Cache.g_header_rec.header_id = g_line_rec.header_id ) then
221 l_booked_flag := OE_Order_Cache.g_header_rec.booked_flag ;
222 else
223 OE_ORDER_CACHE.Load_Order_Header(g_line_rec.header_id);
224 l_booked_flag := OE_Order_Cache.g_header_rec.booked_flag ;
225 end if ;
226
227 /*SELECT booked_flag
228 INTO l_booked_flag
229 FROM oe_order_headers_all
230 WHERE header_id = g_line_rec.header_id; */
231 ELSE
232 l_booked_flag := 'N';
233 END IF;
234
235 RETURN l_booked_flag;
236
237 END Get_Booked;
238
239 FUNCTION Get_Cancelled
240 RETURN VARCHAR2
241 IS
242 --
243 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
244 --
245 BEGIN
246
247 RETURN 'N';
248
249 END Get_Cancelled;
250
251 FUNCTION Get_Open
252 RETURN VARCHAR2
253 IS
254 --
255 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
256 --
257 BEGIN
258
259 RETURN 'Y';
260
261 END Get_Open;
262
263 FUNCTION Get_Cancelled_Quantity
264 RETURN NUMBER
265 IS
266 --
267 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
268 --
269 BEGIN
270
271 RETURN 0;
272
273 END Get_Cancelled_Quantity;
274
275
276 /*---------------------------------------------------------
277 Following procedures are mainly related to lines which are
278 model/class/option/config/ato_item/kit/included/
279
280 1) get_component
281 2) get_top_model_line
282 3) model_option_defaulting
283 4) get_ato_line
284 ----------------------------------------------------------*/
285 FUNCTION Get_Component
286 RETURN VARCHAR2
287 IS
288 --
289 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
290 --
291 BEGIN
292
293 IF l_debug_level > 0 THEN
294 oe_debug_pub.add( ' COMP_CODE , ITEM_TYPE_CODE ' || G_LINE_REC.ITEM_TYPE_CODE ) ;
295 END IF;
296 IF (g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL) OR
297 (g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT AND
298 g_line_rec.line_id = g_line_rec.top_model_line_id)
299 THEN
300 g_line_rec.component_code := to_char(g_line_rec.inventory_item_id);
301 RETURN g_line_rec.component_code;
302 END IF;
303
304 RETURN NULL;
305
306 END Get_Component;
307
308
309 /*----------------------------------------------------------------
310 FUNCTION Get_Top_Model_Line
311 -----------------------------------------------------------------*/
312
313 FUNCTION Get_Top_Model_Line
314 RETURN NUMBER
315 IS
316 l_top_model_line_id NUMBER;
317 l_pick_components_flag VARCHAR2(1);
318 l_item_type NUMBER;
319 --
320 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
321 --
322 BEGIN
323
324 -- If top_model_line_id is not null, you do not want to clear it
325 IF l_debug_level > 0 THEN
326 oe_debug_pub.add( 'IN PKG OE_DEFAULT_LINE : PROCEDURE GET_TOP_MODEL_LINE' ) ;
327 END IF;
328
329 IF ( g_line_rec.inventory_item_id is NULL OR
330 g_line_rec.inventory_item_id = FND_API.G_MISS_NUM)
331 THEN
332 RETURN NULL;
333 END IF;
334
335 IF g_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
336 RETURN NULL;
337 ELSE
338 -- class/option, avoid setting value to null by the last return.
339 IF g_line_rec.top_model_line_id <> FND_API.G_MISS_NUM THEN
340 RETURN g_line_rec.top_model_line_id;
341 END IF;
342
343 END IF;
344
345 OE_ORDER_CACHE.Load_Item
346 (p_key1 => g_line_rec.inventory_item_id
347 ,p_key2 => g_line_rec.ship_from_org_id);
348 l_item_type := OE_ORDER_CACHE.g_item_rec.bom_item_type;
349 l_pick_components_flag := OE_ORDER_CACHE.g_item_rec.pick_components_flag;
350
351 IF (l_item_type = 4 AND
352 l_pick_components_flag = 'Y') OR -- KIT
353 l_item_type = 1 -- MODEL
354 THEN
355 IF (g_line_rec.line_id is NOT NULL AND
356 g_line_rec.line_id <> FND_API.G_MISS_NUM)
357 THEN
358 l_top_model_line_id := g_line_rec.line_id;
359 RETURN (l_top_model_line_id);
360 ELSE
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 END IF;
363 ELSE
364 RETURN NULL;
365 END IF;
366
367 EXCEPTION
368 WHEN NO_DATA_FOUND THEN
369 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_ITEM_NOT_FOUND');
370 IF ( g_line_rec.ordered_item is NULL OR -- This IF added for 1722670
371 g_line_rec.ordered_item = FND_API.G_MISS_CHAR) THEN
372 FND_MESSAGE.Set_TOKEN('ITEM',
373 'Item with inventory_item_id='||to_char(g_line_rec.inventory_item_id));
374 ELSE
375 FND_MESSAGE.Set_TOKEN('ITEM', nvl(g_line_rec.ordered_item,g_line_rec.inventory_item_id));
376 END IF;
377 -- FND_MESSAGE.Set_TOKEN('ITEM', g_line_rec.ordered_item); Replaced with the above IF for 1722670
378 FND_MESSAGE.Set_TOKEN
379 ('ORG',OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'));
380 -- oe_organization_id is drpped,
381 -- hence need to call OE_SYS_PARAMETERS.Value('MASTER_ORGANIZATION_ID');
382
383 OE_Msg_Pub.Add;
384
385 IF l_debug_level > 0 THEN
386 oe_debug_pub.add( 'NO DATA FOUND IN GET_TOP_MODEL IN DEFAULTING' , 1 ) ;
387 END IF;
388
389 RAISE FND_API.G_EXC_ERROR;
390
391 WHEN OTHERS THEN
392
393 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
394 THEN
395 OE_MSG_PUB.Add_Exc_Msg
396 ( G_PKG_NAME ,
397 'Get_Top_Model_Line'
398 );
399 END IF;
400 IF l_debug_level > 0 THEN
401 oe_debug_pub.add( 'OTHERS IN GET_TOP_MODEL IN DEFAULTING' , 1 ) ;
402 END IF;
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404
405 END Get_Top_Model_Line;
406
407 -- forward declaration
408 FUNCTION GET_ATO_LINE
409 RETURN NUMBER;
410
411 /* This procedure defaults Active Agreement Revision and
412 calls process order again to default Dependent Attributes based
413 on new Agreement_Id - Bug 2154960 */
414
415 procedure Default_Active_Agr_Revision
416 ( p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type,
417 p_old_line_rec IN OE_Order_PUB.Line_Rec_Type :=
418 OE_Order_PUB.G_MISS_LINE_REC
419 )
420 IS
421 L_RETURN_STATUS VARCHAR2(1);
422
423 l_x_line_Tbl OE_Order_PUB.Line_Tbl_Type;
424
425 l_old_line_tbl OE_Order_PUB.Line_Tbl_Type;
426 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
427 l_control_rec OE_GLOBALS.Control_Rec_Type;
428 l_agreement_name varchar2(240);
429 l_agreement_id number;
430
431 --
432 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
433 --
434 Begin
435
436 IF l_debug_level > 0 THEN
437 oe_debug_pub.add( 'INSIDE DEFAULT_ACTIVE_AGR_REVISION' , 3 ) ;
438 oe_debug_pub.add( 'AGREEMENT_ID '||P_X_LINE_REC.AGREEMENT_ID , 3 ) ;
439 END IF;
440
441 SELECT agreement_id
442 INTO l_agreement_id
443 FROM oe_agreements_vl
444 WHERE name = (select name from oe_agreements_vl
445 where
446 agreement_id = p_x_line_rec.agreement_id)
447 AND trunc(nvl(p_x_line_rec.pricing_date,sysdate)) BETWEEN
448 trunc(nvl(START_DATE_ACTIVE,add_months(sysdate,-10000)))
449 AND trunc(nvl(END_DATE_ACTIVE,add_months(sysdate,+10000)));
450
451 IF l_debug_level > 0 THEN
452 oe_debug_pub.add( 'ACTIVE AGREEMENT REVISION :'||L_AGREEMENT_ID , 3 ) ;
453 END IF;
454
455 If p_x_line_rec.agreement_id <> l_agreement_id Then
456 IF l_debug_level > 0 THEN
457 oe_debug_pub.add( 'ACTIVE AGREEMENT REVISION IS FOUND - CALLING PROCESS ORDER TO DEFAULT DEPENDENT ATTRIBUTES' , 3 ) ;
458 END IF;
459
460 l_control_rec.controlled_operation := TRUE;
461 l_control_rec.check_security := TRUE;
462 l_control_rec.clear_dependents := TRUE;
463 l_control_rec.default_attributes := TRUE;
464 l_control_rec.change_attributes := FALSE;
465 l_control_rec.validate_entity := FALSE;
466 l_control_rec.write_to_DB := FALSE;
467 l_control_rec.process := FALSE;
468
469
470 l_old_line_tbl(1) := p_x_line_rec;
471 p_x_line_rec.agreement_id := l_agreement_id;
472 l_line_tbl(1) := p_x_line_rec;
473
474 Oe_Order_Pvt.Lines
475 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
476 , p_control_rec => l_control_rec
477 , p_x_line_tbl => l_line_tbl
478 , p_x_old_line_tbl => l_old_line_tbl
479 , x_return_status => l_return_status
480 );
481
482 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
483 RAISE FND_API.G_EXC_ERROR;
484 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
486 END IF;
487
488 p_x_line_rec := l_line_tbl(1);
489
490 End If;
491
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 IF l_debug_level > 0 THEN
495 oe_debug_pub.add( 'NO ACTIVE REVISION EXISTS FOR THE AGREEMENT ID :'||P_X_LINE_REC.AGREEMENT_ID , 2 ) ;
496 oe_debug_pub.add( 'ERROR WILL BE RAISED IN ENTITY LEVEL VALIDATION' , 3 ) ;
497 END IF;
498
499 WHEN FND_API.G_EXC_ERROR THEN
500 RAISE FND_API.G_EXC_ERROR;
501
502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504
505 WHEN OTHERS THEN
506 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
507 THEN
508 OE_MSG_PUB.Add_Exc_Msg
509 ( G_PKG_NAME ,
510 'Default_Active_Agr_Revision'
511 );
512 END IF;
513
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515
516 End Default_Active_Agr_Revision;
517 /* End of Bug-2154960 */
518
519 -- BEGIN: Blankets Code Merge
520
521 PROCEDURE Clear_And_Re_Default
522 (p_blanket_number IN NUMBER
523 ,p_blanket_line_number IN NUMBER
524 ,p_blanket_version_number IN NUMBER
525 ,p_x_line_rec IN OUT NOCOPY OE_AK_ORDER_LINES_V%ROWTYPE
526 ,p_old_line_rec IN OE_AK_ORDER_LINES_V%ROWTYPE
527 ,p_default_record IN VARCHAR2
528 )
529 IS
530 l_line_rec OE_AK_ORDER_LINES_V%ROWTYPE;
531 --
532 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
533 --
534 BEGIN
535
536 IF l_debug_level > 0 THEN
537 oe_debug_pub.add('ENTER Clear_And_Re_Default') ;
538 oe_debug_pub.add('Old blanket num :'
539 ||p_x_line_rec.blanket_number) ;
540 oe_debug_pub.add('Old blanket line num :'
541 ||p_x_line_rec.blanket_line_number) ;
542 oe_debug_pub.add('Old blanket version num :'
543 ||p_x_line_rec.blanket_version_number) ;
544 oe_debug_pub.add('New blanket num : '||p_blanket_number) ;
545 oe_debug_pub.add('New blanket line num : '||p_blanket_line_number);
546 oe_debug_pub.add('New blanket version num : '||p_blanket_version_number);
547 END IF;
548
549 -- Copy source attribute values from IN parameters
550 -- to the new record
551 IF p_blanket_number IS NOT NULL
552 THEN
553 IF NOT OE_GLOBALS.EQUAL(p_x_line_rec.blanket_number
554 ,p_blanket_number)
555 THEN
556 p_x_line_rec.blanket_number := p_blanket_number;
557 l_line_rec := p_x_line_rec;
558 -- Clear dependents based on blanket number
559 OE_Line_Util_Ext.Clear_Dependent_Attr
560 (p_attr_id => OE_LINE_UTIL.G_BLANKET_NUMBER
561 ,p_x_line_rec => p_x_line_rec
562 ,p_initial_line_rec => l_line_rec
563 ,p_old_line_rec => p_old_line_rec
564 );
565 END IF;
566 END IF;
567
568 IF p_blanket_line_number IS NOT NULL
569 THEN
570 IF NOT OE_GLOBALS.EQUAL(p_x_line_rec.blanket_line_number
571 ,p_blanket_line_number)
572 THEN
573 p_x_line_rec.blanket_line_number := p_blanket_line_number;
574 l_line_rec := p_x_line_rec;
575 -- Clear dependents based on blanket number
576 OE_Line_Util_Ext.Clear_Dependent_Attr
577 (p_attr_id => OE_LINE_UTIL.G_BLANKET_LINE_NUMBER
578 ,p_x_line_rec => p_x_line_rec
579 ,p_initial_line_rec => l_line_rec
580 ,p_old_line_rec => p_old_line_rec
581 );
582 END IF;
583 END IF;
584
585 IF p_blanket_version_number IS NOT NULL
586 THEN
587 IF NOT OE_GLOBALS.EQUAL(p_x_line_rec.blanket_version_number
588 ,p_blanket_version_number)
589 THEN
590 p_x_line_rec.blanket_version_number := p_blanket_version_number;
591 -- No dependent attributes exist for blanket version number
592 END IF;
593 END IF;
594
595 IF l_debug_level > 0 THEN
596 oe_debug_pub.add('Final blanket num :'
597 ||p_x_line_rec.blanket_number) ;
598 oe_debug_pub.add('Final blanket line num :'
599 ||p_x_line_rec.blanket_line_number) ;
600 oe_debug_pub.add('Final blanket version num :'
601 ||p_x_line_rec.blanket_version_number) ;
602 END IF;
603
604 IF p_default_record = 'Y' THEN
605 IF l_debug_level > 0 THEN
606 oe_debug_pub.add('RE-CALLING ONT_LINE_DEF_HDLR.DEFAULT_RECORD') ;
607 END IF;
608 ONT_LINE_Def_Hdlr.Default_Record
609 (p_x_rec => p_x_line_rec
610 ,p_initial_rec => l_line_rec
611 ,p_in_old_rec => p_old_line_rec
612 );
613 END IF;
614
615 IF l_debug_level > 0 THEN
616 oe_debug_pub.add('EXIT Clear_And_Re_Default') ;
617 END IF;
618
619 END Clear_And_Re_Default;
620
621 PROCEDURE Default_Blanket_Values
622 ( p_blanket_number IN NUMBER,
623 p_cust_po_number IN VARCHAR2,
624 p_ordered_item_id IN NUMBER DEFAULT NULL,--bug6826787
625 p_ordered_item IN VARCHAR2,
626 p_inventory_item_id IN NUMBER,
627 p_item_identifier_type IN VARCHAR2,
628 p_request_date IN DATE,
629 p_sold_to_org_id IN NUMBER,
630 x_blanket_number OUT NOCOPY NUMBER,
631 x_blanket_line_number OUT NOCOPY NUMBER,
632 x_blanket_version_number OUT NOCOPY NUMBER,
633 x_blanket_request_date OUT NOCOPY DATE
634 )
635 IS
636 --
637 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
638 l_blanket_number NUMBER;
639 --
640 l_item_validation_org NUMBER :=
641 OE_Sys_Parameters.Value('MASTER_ORGANIZATION_ID');
642 BEGIN
643
644 IF p_request_date IS NOT NULL AND
645 p_request_date <> FND_API.G_MISS_DATE THEN
646 x_blanket_request_date := p_request_date;
647 ELSE
648 x_blanket_request_date := sysdate;
649 END IF;
650
651 IF p_blanket_number = FND_API.G_MISS_NUM THEN
652 l_blanket_number := NULL;
653 ELSE
654 l_blanket_number := p_blanket_number;
655 END IF;
656
657 if l_debug_level > 0 then
658 oe_debug_pub.add('Enter Default_Blanket_Values');
659 oe_debug_pub.add('Request Date :'||x_blanket_request_date);
660 end if;
661
662 --derive bl line # and bl revis #
663 --first sorts by inventory_item_id, then by ordered_item_id (category)
664 --to select most specific of effective blanket lines
665
666 -- added for bug 4246913
667 if l_blanket_number is null AND
668 (p_cust_po_number = FND_API.G_MISS_CHAR OR
669 p_cust_po_number IS NULL) then
670
671 if l_debug_level > 0 then
672 oe_debug_pub.add('No blanket or customer po number on line, returning');
673 end if;
674
675 RETURN;
676 end if;
677
678 --bug6826787 First look for an exact match (inventory_item_id,item_identifier_type,ordered_item_id)
679 --If not found then look for atleast inventory_item_id match,if not found then look for category level match,
680 --if not found look for ALL Items level
681
682 BEGIN --bug6826787 Exact match
683 IF l_blanket_number is null THEN
684
685 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
686 INTO x_blanket_number,
687 x_blanket_version_number,
688 x_blanket_line_number
689 FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
690 OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
691 WHERE BH.HEADER_ID = BL.HEADER_ID
692 AND BL.CUST_PO_NUMBER = p_cust_po_number
693 AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
694 AND BHE.ON_HOLD_FLAG = 'N'
695 AND trunc(x_blanket_request_date)
696 BETWEEN trunc(BLE.START_DATE_ACTIVE)
697 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
698 AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
699 AND BL.item_identifier_type = p_item_identifier_type
700 AND decode(BL.item_identifier_type,'INT',to_char(BL.inventory_item_id),
701 'CUST',to_char(ordered_item_id),
702 NVL(BL.ordered_item,'XXXX') )= decode ( p_item_identifier_type,'INT', to_char(p_inventory_item_id)
703 , 'CUST', to_char(p_ordered_item_id)
704 , NVL(p_ordered_item,'XXXX') )
705 AND BL.ITEM_IDENTIFIER_TYPE NOT IN ('CAT','ALL')
706 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
707 AND BL.LINE_ID = BLE.LINE_ID
708 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
709
710 ELSE
711
712 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
713 INTO x_blanket_number,
714 x_blanket_version_number,
715 x_blanket_line_number
716 FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
717 OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
718 WHERE BH.HEADER_ID = BL.HEADER_ID
719 AND BH.ORDER_NUMBER = l_blanket_number
720 -- Do not match customer if blanket number is supplied
721 -- With 11.5.10, customer on blanket could be
722 -- related customer or it could be a null customer
723 -- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
724 AND BHE.ON_HOLD_FLAG = 'N'
725 AND trunc(x_blanket_request_date)
726 BETWEEN trunc(BLE.START_DATE_ACTIVE)
727 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
728 AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
729 AND BL.item_identifier_type = p_item_identifier_type
730 AND decode(BL.item_identifier_type,'INT',to_char(BL.inventory_item_id),
731 'CUST',to_char(ordered_item_id),
732 NVL(BL.ordered_item,'XXXX') )= decode ( p_item_identifier_type,'INT', to_char(p_inventory_item_id)
733 , 'CUST', to_char(p_ordered_item_id)
734 , NVL(p_ordered_item,'XXXX') )
735 AND BL.ITEM_IDENTIFIER_TYPE NOT IN ('CAT','ALL')
736 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
737 AND BL.LINE_ID = BLE.LINE_ID
738 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
739
740 END IF;
741
742 Exception
743
744 WHEN TOO_MANY_ROWS THEN
745
746 x_blanket_number := p_blanket_number;
747 x_blanket_version_number := NULL;
748 x_blanket_line_number := NULL;
749
750 IF l_debug_level > 0 THEN
751 oe_debug_pub.add('Multiple blankets exist for customer po number--no defaulting of blanket values');
752 END IF;
753 RETURN;
754
755 WHEN NO_DATA_FOUND THEN
756
757 BEGIN --internal items
758 IF l_blanket_number is null THEN
759
760 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
761 INTO x_blanket_number,
762 x_blanket_version_number,
763 x_blanket_line_number
764 FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
765 OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
766 WHERE BH.HEADER_ID = BL.HEADER_ID
767 AND BL.CUST_PO_NUMBER = p_cust_po_number
768 AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
769 AND BHE.ON_HOLD_FLAG = 'N'
770 AND trunc(x_blanket_request_date)
771 BETWEEN trunc(BLE.START_DATE_ACTIVE)
772 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
773 AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
774 AND BL.ITEM_IDENTIFIER_TYPE ='INT' --bug6826787
775 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
776 AND BL.LINE_ID = BLE.LINE_ID
777 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
778
779 ELSE
780
781 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
782 INTO x_blanket_number,
783 x_blanket_version_number,
784 x_blanket_line_number
785 FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
786 OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
787 WHERE BH.HEADER_ID = BL.HEADER_ID
788 AND BH.ORDER_NUMBER = l_blanket_number
789 -- Do not match customer if blanket number is supplied
790 -- With 11.5.10, customer on blanket could be
791 -- related customer or it could be a null customer
792 -- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
793 AND BHE.ON_HOLD_FLAG = 'N'
794 AND trunc(x_blanket_request_date)
795 BETWEEN trunc(BLE.START_DATE_ACTIVE)
796 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
797 AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
798 AND BL.ITEM_IDENTIFIER_TYPE ='INT' --bug6826787
799 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
800 AND BL.LINE_ID = BLE.LINE_ID
801 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
802
803 END IF;
804
805 EXCEPTION
806 WHEN TOO_MANY_ROWS THEN
807
808 x_blanket_number := p_blanket_number;
809 x_blanket_version_number := NULL;
810 x_blanket_line_number := NULL;
811
812 IF l_debug_level > 0 THEN
813 oe_debug_pub.add('Multiple blankets exist for customer po number--no defaulting of blanket values');
814 END IF;
815 RETURN;
816
817 WHEN NO_DATA_FOUND THEN
818
819 BEGIN --item categories
820
821 IF l_blanket_number is null THEN
822
823 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
824 INTO x_blanket_number,
825 x_blanket_version_number,
826 x_blanket_line_number
827 FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL, MTL_ITEM_CATEGORIES IC,
828 OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
829 WHERE BH.HEADER_ID = BL.HEADER_ID
830 AND BL.CUST_PO_NUMBER = p_cust_po_number
831 AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
832 AND BHE.ON_HOLD_FLAG = 'N'
833 AND trunc(x_blanket_request_date)
834 BETWEEN trunc(BLE.START_DATE_ACTIVE)
835 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
836 AND BL.ITEM_IDENTIFIER_TYPE = 'CAT'
837 AND IC.ORGANIZATION_ID = l_item_validation_org
838 AND IC.INVENTORY_ITEM_ID = p_inventory_item_id
839 AND BL.INVENTORY_ITEM_ID = IC.CATEGORY_ID
840 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
841 AND BL.LINE_ID = BLE.LINE_ID
842 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
843
844 ELSE
845
846 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
847 INTO x_blanket_number,
848 x_blanket_version_number,
849 x_blanket_line_number
850 FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL, MTL_ITEM_CATEGORIES IC
851 ,OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
852 WHERE BH.HEADER_ID = BL.HEADER_ID
853 AND BH.ORDER_NUMBER = l_blanket_number
854 -- Do not match customer if blanket number is supplied
855 -- With 11.5.10, customer on blanket could be
856 -- related customer or it could be a null customer
857 -- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
858 AND BHE.ON_HOLD_FLAG = 'N'
859 AND trunc(x_blanket_request_date)
860 BETWEEN trunc(BLE.START_DATE_ACTIVE)
861 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
862 AND BL.ITEM_IDENTIFIER_TYPE = 'CAT'
863 AND IC.ORGANIZATION_ID = l_item_validation_org
864 AND IC.INVENTORY_ITEM_ID = p_inventory_item_id
865 AND BL.INVENTORY_ITEM_ID = IC.CATEGORY_ID
866 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
867 AND BL.LINE_ID = BLE.LINE_ID
868 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
869
870 END IF;
871
872 EXCEPTION
873 WHEN TOO_MANY_ROWS THEN
874 x_blanket_number := p_blanket_number;
875 x_blanket_version_number := NULL;
876 x_blanket_line_number := NULL;
877
878 IF l_debug_level > 0 THEN
879 oe_debug_pub.add('Multiple blankets exist for customer po number--no defaulting of blanket values');
880 END IF;
881 RETURN;
882
883 WHEN NO_DATA_FOUND THEN
884
885 BEGIN --all items
886
887 IF l_blanket_number is null THEN
888
889 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
890 INTO x_blanket_number,
891 x_blanket_version_number,
892 x_blanket_line_number
893 FROM OE_BLANKET_HEADERS BH, OE_BLANKET_LINES_ALL BL,
894 OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
895 WHERE BH.HEADER_ID = BL.HEADER_ID
896 AND BL.CUST_PO_NUMBER = p_cust_po_number
897 AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
898 AND BHE.ON_HOLD_FLAG = 'N'
899 AND trunc(x_blanket_request_date)
900 BETWEEN trunc(BLE.START_DATE_ACTIVE)
901 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
902 AND BL.ITEM_IDENTIFIER_TYPE = 'ALL'
903 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
904 AND BL.LINE_ID = BLE.LINE_ID
905 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
906
907 ELSE
908
909 SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
910 INTO x_blanket_number,
911 x_blanket_version_number,
912 x_blanket_line_number
913 FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
914 OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
915 WHERE BH.HEADER_ID = BL.HEADER_ID
916 AND BH.ORDER_NUMBER = l_blanket_number
917 -- Do not match customer if blanket number is supplied
918 -- With 11.5.10, customer on blanket could be
919 -- related customer or it could be a null customer
920 -- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
921 AND BHE.ON_HOLD_FLAG = 'N'
922 AND trunc(x_blanket_request_date)
923 BETWEEN trunc(BLE.START_DATE_ACTIVE)
924 AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
925 AND BL.ITEM_IDENTIFIER_TYPE = 'ALL'
926 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
927 AND BL.LINE_ID = BLE.LINE_ID
928 AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
929
930 END IF;
931
932 EXCEPTION
933 WHEN TOO_MANY_ROWS THEN
934 x_blanket_number := p_blanket_number;
935 x_blanket_version_number := NULL;
936 x_blanket_line_number := NULL;
937
938 IF l_debug_level > 0 THEN
939 oe_debug_pub.add('Multiple blankets exist for customer po number--no defaulting of blanket values');
940 END IF;
941 RETURN;
942 WHEN NO_DATA_FOUND THEN
943 IF l_debug_level > 0 THEN
944 oe_debug_pub.add('No Blanket Number exists for this customer,item :'||p_sold_to_org_id,2);
945 oe_debug_pub.add('Error will be raised in Entity level validation',3);
946 END IF;
947 END; --all items
948 END; --item categories
949 END; --internal, customer, generic items
950 END; --bug6826787 Exact match
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
955 THEN
956 OE_MSG_PUB.Add_Exc_Msg
957 ( G_PKG_NAME ,
958 'Default_Blanket_Values'
959 );
960 END IF;
961 IF l_debug_level > 0 THEN
962 oe_debug_pub.add('others in default_blanket_values', 1);
963 END IF;
964 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
965
966 End Default_Blanket_Values;
967
968
969 PROCEDURE Perform_Blanket_Functions
970 (p_x_line_rec IN OUT NOCOPY OE_AK_ORDER_LINES_V%ROWTYPE
971 ,p_old_line_rec IN OE_AK_ORDER_LINES_V%ROWTYPE
972 ,p_default_record IN VARCHAR2 DEFAULT 'N'
973 ,x_blanket_request_date OUT NOCOPY /* file.sql.39 change */ DATE
974 )
975 IS
976 l_blanket_number NUMBER;
977 l_blanket_line_number NUMBER;
978 l_blanket_version_number NUMBER;
979 l_blanket_request_date DATE;
980 --
981 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
982 --
983 BEGIN
984
985 IF (p_x_line_rec.blanket_number IS NOT NULL
986 AND p_x_line_rec.blanket_number <> FND_API.G_MISS_NUM)
987 OR ( p_x_line_rec.cust_po_number IS NOT NULL
988 AND p_x_line_rec.cust_po_number <> FND_API.G_MISS_CHAR
989 -- Bug 2818494
990 -- Default blanket from customer PO only if either
991 -- customer PO or item is updated on order line.
992 AND (NOT OE_GLOBALS.EQUAL(p_x_line_rec.cust_po_number
993 ,p_old_line_rec.cust_po_number)
994 OR NOT OE_GLOBALS.EQUAL(p_x_line_rec.inventory_item_id
995 ,p_old_line_rec.inventory_item_id)
996 )
997 )
998 THEN
999
1000 if l_debug_level > 0 then
1001 oe_debug_pub.add('ENTER Perform_Blanket_Functions');
1002 oe_debug_pub.add('Blanket Num :'||
1003 p_x_line_rec.blanket_number);
1004 oe_debug_pub.add('Blanket Line Num :'||
1005 p_x_line_rec.blanket_line_number);
1006 oe_debug_pub.add('Cust PO :'||
1007 p_x_line_rec.cust_po_number);
1008 oe_debug_pub.add('Old Blanket Num :'||
1009 p_old_line_rec.blanket_number);
1010 oe_debug_pub.add('Old Blanket Line Num :'||
1011 p_old_line_rec.blanket_line_number);
1012 oe_debug_pub.add('Old Cust PO :'||
1013 p_old_line_rec.cust_po_number);
1014 end if;
1015
1016 -- Bug 2737082 => If blanket line number exists, removed the
1017 -- AND clause for operation and version number check and moved
1018 -- it inside the IF.
1019 -- Otherwise, the ELSE part of this IF statement was being
1020 -- executed for all release lines even if there was a blanket
1021 -- line number which could result in over-riding an existing
1022 -- blanket line number value.
1023 IF p_x_line_rec.blanket_line_number IS NOT NULL
1024 AND p_x_line_rec.blanket_line_number <> FND_API.G_MISS_NUM
1025 THEN
1026
1027
1028 --Redefault the blanket_line_number even when the blanket_line_number is NOT NULL
1029 --on the line if the request_date/ordered item is changed
1030 --This would fix the bug 6368131 also
1031
1032 IF ( (trunc(p_x_line_rec.request_date) <> trunc(p_old_line_rec.request_date))
1033 OR (p_x_line_rec.inventory_item_id <> p_old_line_rec.inventory_item_id )
1034 OR (p_x_line_rec.ordered_item_id <> p_old_line_rec.ordered_item_id)
1035 OR (p_x_line_rec.ordered_item <> p_old_line_rec.ordered_item)) THEN
1036
1037
1038 IF ( p_x_line_rec.sold_to_org_id IS NOT NULL
1039 AND p_x_line_rec.sold_to_org_id <> FND_API.G_MISS_NUM
1040 AND p_x_line_rec.inventory_item_id IS NOT NULL
1041 AND p_x_line_rec.inventory_item_id <> FND_API.G_MISS_NUM
1042 AND (p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD
1043 OR (p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT
1044 AND p_x_line_rec.top_model_line_id = p_x_line_rec.line_id))) THEN
1045
1046 Default_Blanket_Values
1047 ( p_blanket_number => p_x_line_rec.blanket_number,
1048 p_cust_po_number => p_x_line_rec.cust_po_number,
1049 p_ordered_item_id =>p_x_line_rec.ordered_item_id,
1050 p_ordered_item =>p_x_line_rec.ordered_item, --bug6826787
1051 p_inventory_item_id => p_x_line_rec.inventory_item_id,
1052 p_item_identifier_type => p_x_line_rec.item_identifier_type,
1053 p_request_date => p_x_line_rec.request_date,
1054 p_sold_to_org_id => p_x_line_rec.sold_to_org_id,
1055 x_blanket_number => l_blanket_number,
1056 x_blanket_line_number => l_blanket_line_number,
1057 x_blanket_version_number => l_blanket_version_number,
1058 x_blanket_request_date => x_blanket_request_date
1059 );
1060
1061 IF (l_blanket_number IS NOT NULL
1062 AND NOT OE_GLOBALS.EQUAL(l_blanket_number
1063 ,p_x_line_rec.blanket_number))
1064 OR (l_blanket_line_number IS NOT NULL
1065 AND NOT OE_GLOBALS.EQUAL(l_blanket_line_number
1066 ,p_x_line_rec.blanket_line_number))
1067 THEN
1068 Clear_And_Re_Default
1069 (p_blanket_number => l_blanket_number
1070 ,p_blanket_line_number => l_blanket_line_number
1071 ,p_blanket_version_number => l_blanket_version_number
1072 ,p_x_line_rec => p_x_line_rec
1073 ,p_old_line_rec => p_old_line_rec
1074 ,p_default_record => p_default_record
1075 );
1076 END IF;
1077
1078
1079
1080 END IF;
1081
1082
1083 END IF;
1084
1085
1086 IF (p_x_line_rec.operation = OE_GLOBALS.G_OPR_CREATE
1087 OR p_x_line_rec.blanket_version_number = FND_API.G_MISS_NUM)
1088 THEN
1089
1090 if l_debug_level > 0 then
1091 oe_debug_pub.add('Default Blanket Version Number');
1092 end if;
1093
1094 -- Derive blanket_version_number if blanket number
1095 -- ,line number are provided
1096
1097 BEGIN
1098
1099 SELECT /* MOAC_SQL_CHANGE */ BH.VERSION_NUMBER
1100 INTO l_blanket_version_number
1101 FROM OE_BLANKET_LINES_ALL BL,OE_BLANKET_LINES_EXT BLE,
1102 OE_BLANKET_HEADERS BH
1103 WHERE BLE.ORDER_NUMBER = p_x_line_rec.blanket_number
1104 AND BLE.LINE_NUMBER = p_x_line_rec.blanket_line_number
1105 AND BL.LINE_ID = BLE.LINE_ID
1106 AND BH.HEADER_ID = BL.HEADER_ID
1107 AND BL.SALES_DOCUMENT_TYPE_CODE = 'B';
1108
1109 p_x_line_rec.blanket_version_number := l_blanket_version_number;
1110
1111 EXCEPTION
1112 WHEN NO_DATA_FOUND THEN
1113 oe_debug_pub.add('Blanket Values combination is not valid: Blanket #:'||p_x_line_rec.blanket_number || ', Blanket Line #:'||p_x_line_rec.blanket_line_number, 2);
1114 FND_MESSAGE.SET_NAME('ONT', 'OE_BLKT_INVALID_BLANKET');
1115 fnd_message.set_token('BLANKET_NUMBER',p_x_line_rec.blanket_number);
1116 fnd_message.set_token('BLANKET_LINE_NUMBER',p_x_line_rec.blanket_line_number);
1117 OE_MSG_PUB.Add;
1118 END;
1119
1120 END IF; -- default version number
1121
1122 -- Bug 2737082 => Only if blanket line number is null or missing,
1123 -- then default if required fields are available.
1124 ELSIF (p_x_line_rec.sold_to_org_id IS NOT NULL
1125 AND p_x_line_rec.sold_to_org_id <> FND_API.G_MISS_NUM
1126 AND p_x_line_rec.inventory_item_id IS NOT NULL
1127 AND p_x_line_rec.inventory_item_id <> FND_API.G_MISS_NUM
1128 AND (p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD
1129 OR (p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT
1130 AND p_x_line_rec.top_model_line_id = p_x_line_rec.line_id)
1131 )
1132 -- Bug 2769562 => If blanket line number is being cleared by user
1133 -- (value for blanket line number existed in old rec), blanket
1134 -- fields should NOT be re-defaulted.
1135 AND (p_old_line_rec.blanket_line_number IS NULL
1136 OR p_old_line_rec.blanket_line_number = FND_API.G_MISS_NUM
1137 )) OR (p_x_line_rec.sold_to_org_id IS NOT NULL
1138 --Bug 3228828
1139 --Defaulting of the Blanket Line Number for Config and Service Items.
1140 AND p_x_line_rec.sold_to_org_id <> FND_API.G_MISS_NUM
1141 AND p_x_line_rec.inventory_item_id IS NOT NULL
1142 AND p_x_line_rec.inventory_item_id <> FND_API.G_MISS_NUM
1143 AND oe_code_control.get_code_release_level >= '110510'
1144 -- Bug 2769562 => If blanket line number is being cleared by user
1145 -- (value for blanket line number existed in old rec), blanket
1146 -- fields should NOT be re-defaulted.
1147 AND (p_old_line_rec.blanket_line_number IS NULL
1148 OR p_old_line_rec.blanket_line_number = FND_API.G_MISS_NUM
1149 ) OR p_x_line_rec.blanket_line_number <> p_old_line_rec.blanket_line_number
1150 --OR (p_x_line_rec.blanket_line_number IS NOT NULL --6368131
1151 --AND trunc(p_x_line_rec.request_date) <>
1152 --trunc(p_old_line_rec.request_date))
1153 )--bug6497015
1154 THEN
1155
1156 if l_debug_level > 0 then
1157 oe_debug_pub.add('Blkt Number : '||p_x_line_rec.blanket_number);
1158 oe_debug_pub.add('Cust PO : '||p_x_line_rec.cust_po_number);
1159 oe_debug_pub.add('Item : '||p_x_line_rec.inventory_item_id);
1160 oe_debug_pub.add('Item Type : '||p_x_line_rec.item_type_code);
1161 end if;
1162
1163 -- Default Blanket Fields
1164 Default_Blanket_Values
1165 ( p_blanket_number => p_x_line_rec.blanket_number,
1166 p_cust_po_number => p_x_line_rec.cust_po_number,
1167 p_ordered_item_id =>p_x_line_rec.ordered_item_id,--bug8344368
1168 p_ordered_item =>p_x_line_rec.ordered_item,
1169 p_inventory_item_id => p_x_line_rec.inventory_item_id,
1170 p_item_identifier_type => p_x_line_rec.item_identifier_type,
1171 p_request_date => p_x_line_rec.request_date,
1172 p_sold_to_org_id => p_x_line_rec.sold_to_org_id,
1173 x_blanket_number => l_blanket_number,
1174 x_blanket_line_number => l_blanket_line_number,
1175 x_blanket_version_number => l_blanket_version_number,
1176 x_blanket_request_date => x_blanket_request_date
1177 );
1178
1179 IF (l_blanket_number IS NOT NULL
1180 AND NOT OE_GLOBALS.EQUAL(l_blanket_number
1181 ,p_x_line_rec.blanket_number))
1182 OR (l_blanket_line_number IS NOT NULL
1183 AND NOT OE_GLOBALS.EQUAL(l_blanket_line_number
1184 ,p_x_line_rec.blanket_line_number))
1185 THEN
1186 Clear_And_Re_Default
1187 (p_blanket_number => l_blanket_number
1188 ,p_blanket_line_number => l_blanket_line_number
1189 ,p_blanket_version_number => l_blanket_version_number
1190 ,p_x_line_rec => p_x_line_rec
1191 ,p_old_line_rec => p_old_line_rec
1192 ,p_default_record => p_default_record
1193 );
1194 END IF;
1195
1196 END IF;
1197
1198 if l_debug_level > 0 then
1199 oe_debug_pub.add('EXIT Perform_Blanket_Functions');
1200 end if;
1201
1202 END IF; -- if blanket number or cust po exists
1203
1204 EXCEPTION
1205 WHEN FND_API.G_EXC_ERROR THEN
1206 RAISE FND_API.G_EXC_ERROR;
1207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1209 WHEN OTHERS THEN
1210 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1211 THEN
1212 OE_MSG_PUB.Add_Exc_Msg
1213 ( G_PKG_NAME ,
1214 'Perform_Blanket_Functions'
1215 );
1216 END IF;
1217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1218 END Perform_Blanket_Functions;
1219
1220 --- Added for 11510 pack J to get the BSA Line Number and Version Number
1221 --- For given CONFIG and SERVICE items. srini
1222 Procedure Get_Blanket_number_svc_config
1223 ( p_blanket_number IN OUT NOCOPY /* file.sql.39 change */ NUMBER
1224 ,p_inventory_item_id IN NUMBER
1225 ,x_blanket_line_number OUT NOCOPY NUMBER
1226 ,x_blanket_version_number OUT NOCOPY NUMBER
1227 ) is
1228 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1229 BEGIN
1230
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'BSA: ENTERING Get_Blanket_Number API' ) ;
1233 oe_debug_pub.add( 'BSA: Blanket Number in Get_Blanket_Number: '||p_blanket_number ) ;
1234 oe_debug_pub.add( 'BSA: Inventory Item Id in Get_Blanket_Number : '||p_INVENTORY_ITEM_ID ) ;
1235 END IF;
1236
1237 SELECT /* MOAC_SQL_CHANGE */
1238 BL.LINE_NUMBER,
1239 BH.VERSION_NUMBER
1240 INTO
1241 x_blanket_line_number,
1242 x_blanket_version_number
1243
1244 FROM OE_BLANKET_LINES BL,
1245 OE_BLANKET_HEADERS_ALL BH,
1246 OE_BLANKET_HEADERS_EXT BHE,
1247 OE_BLANKET_LINES_EXT BLE
1248 WHERE BH.ORDER_NUMBER = p_blanket_number
1249 AND BL.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
1250 AND BH.HEADER_ID = BL.HEADER_ID
1251 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
1252 AND BL.LINE_ID = BLE.LINE_ID
1253 AND BHE.ON_HOLD_FLAG = 'N'
1254 AND trunc(sysdate) BETWEEN trunc(BLE.START_DATE_ACTIVE)
1255 AND trunc(nvl(BLE.END_DATE_ACTIVE, sysdate))
1256 AND BL.ITEM_IDENTIFIER_TYPE NOT IN ('ALL')
1257 AND p_blanket_number is not null
1258 AND BL.SALES_DOCUMENT_TYPE_CODE = 'B';
1259
1260
1261 IF (x_blanket_line_number is null and x_blanket_version_number is null) then
1262
1263 SELECT /* MOAC_SQL_CHANGE */
1264 BL.LINE_NUMBER,
1265 BH.VERSION_NUMBER
1266 INTO
1267 x_blanket_line_number,
1268 x_blanket_version_number
1269
1270 FROM OE_BLANKET_LINES BL,
1271 OE_BLANKET_HEADERS_ALL BH,
1272 OE_BLANKET_HEADERS_EXT BHE,
1273 OE_BLANKET_LINES_EXT BLE
1274 WHERE BH.ORDER_NUMBER = p_blanket_number
1275 AND BH.HEADER_ID = BL.HEADER_ID
1276 AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
1277 AND BL.LINE_ID = BLE.LINE_ID
1278 AND BHE.ON_HOLD_FLAG = 'N'
1279 AND trunc(sysdate) BETWEEN trunc(BLE.START_DATE_ACTIVE)
1280 AND trunc(nvl(BLE.END_DATE_ACTIVE, sysdate))
1281 AND BL.ITEM_IDENTIFIER_TYPE = 'ALL'
1282 AND p_blanket_number is not null
1283 AND BL.SALES_DOCUMENT_TYPE_CODE = 'B';
1284
1285
1286 END IF;
1287
1288 IF l_debug_level > 0 THEN
1289 oe_debug_pub.add( 'BSA: LEAVING Get_Blanket_Number API' ) ;
1290 oe_debug_pub.add( 'BSA: Blanket line Number in Get_Blanket_Number: '||p_blanket_number ) ;
1291 oe_debug_pub.add( 'BSA: Blanket line Number in Get_Blanket_Number: '||x_blanket_line_number ) ;
1292 oe_debug_pub.add( 'BSA: Blanket Version Number in Get_Blanket_Number : '||x_blanket_version_number
1293 ) ;
1294 END IF;
1295
1296 EXCEPTION
1297
1298 WHEN NO_DATA_FOUND THEN
1299 x_blanket_line_number := null;
1300 x_blanket_version_number := null;
1301 p_blanket_number := null;
1302 IF l_debug_level > 0 THEN
1303 oe_debug_pub.add( 'BSA:LEAVING Get_Blanket_Number API: NO_DATA_FOUND' ) ;
1304 END IF;
1305 WHEN TOO_MANY_ROWS THEN
1306 x_blanket_line_number := null;
1307 x_blanket_version_number := null;
1308 p_blanket_number := null;
1309 IF l_debug_level > 0 THEN
1310 oe_debug_pub.add( 'BSA:LEAVING Get_Blanket_Number API: TOO_MANY_ROWS' ) ;
1311 END IF;
1312 WHEN OTHERS THEN
1313 x_blanket_line_number := null;
1314 x_blanket_version_number := null;
1315 p_blanket_number := null;
1316 IF l_debug_level > 0 THEN
1317 oe_debug_pub.add( 'BSA:LEAVING Get_Blanket_Number API: OTEHRS' ) ;
1318 END IF;
1319
1320 End Get_Blanket_number_svc_config;
1321
1322 -- END: Blankets Code Merge
1323
1324 /*----------------------------------------------------------------
1325 This procedure is used to default certain columns of children of
1326 top level model i.e ATO model, PTO model, SMC-PTO Model. and
1327 ato_line_id for top parent as well as oprion/class/config etc.
1328 shippable_flag??
1329 Before the control comes here, g_line_rec should have following
1330 attributes :
1331
1332 1) line_id,
1333 2) top_model_line_id,
1334 3) item_type_code,
1335 4) ship_model_complete_flag of top parent
1336
1337 ONT's item_type_code of ato under pto or ato under ato is 'CLASS'
1338 even though its bom_item_type is 1.
1339
1340 Change Record:
1341 2150536 : moved the ato/smc/set specific defaulting to
1342 default_child_line.
1343 -----------------------------------------------------------------*/
1344 PROCEDURE Model_Option_Defaulting
1345 IS
1346 l_top_model_line_rec OE_ORDER_PUB.line_rec_type;
1347 l_parent_line_id NUMBER;
1348 l_return_status VARCHAR2(1);
1349 --
1350 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1351 --
1352 l_top_container VARCHAR2(1);
1353 l_part_of_container VARCHAR2(1);
1354 BEGIN
1355
1356 IF l_debug_level > 0 THEN
1357 oe_debug_pub.add( 'ENTERING MODEL_OPTION_DEFAULTING' , 1 ) ;
1358 oe_debug_pub.add( 'LINE_ID TO DEFAULT: '|| G_LINE_REC.LINE_ID , 1 ) ;
1359 END IF;
1360
1361 /* If the top model is in a fulfillment set then we must push all its
1362 children into same fulfillment set. We exclude service item and the
1363 top model itself*/
1364 -- 4118431
1365 IF (g_line_rec.line_id <> g_line_rec.top_model_line_id AND
1366 g_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_SERVICE AND
1367 g_line_rec.top_modeL_line_id IS NOT NULL AND
1368 g_line_rec.line_id > 0 ) THEN
1369
1370 Insert_into_set
1371 (p_line_id => g_line_rec.top_model_line_id,
1372 p_child_line_id => g_line_rec.line_id,
1373 x_return_status => l_return_status);
1374
1375 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1377 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1378 RAISE FND_API.G_EXC_ERROR;
1379 END IF;
1380 END IF;
1381
1382
1383 /* We do not do any defaulting for service items and kits.
1384 * We also do not default anything special for included items,
1385 * since user dose not enter them.They are created in the
1386 * process_included_items procedure and all the fields to be populated
1387 * from the parent line, are populated there. */
1388
1389 IF g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE OR
1390 g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED OR
1391 (g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT AND
1392 g_line_rec.line_id = g_line_rec.top_modeL_line_id)
1393 THEN
1394 IF l_debug_level > 0 THEN
1395 oe_debug_pub.add( 'RETURNING FOR SERVICE , INCLUDED , TOP LEVEL KITS' , 1 ) ;
1396 END IF;
1397 RETURN;
1398 END IF;
1399
1400
1401 IF g_line_rec.ato_line_id = FND_API.G_MISS_NUM THEN
1402 g_line_rec.ato_line_id := Get_ATO_Line;
1403 IF l_debug_level > 0 THEN
1404 oe_debug_pub.add( 'GET_ATO_LINE SUCCESSFUL' , 1 ) ;
1405 END IF;
1406 END IF;
1407
1408
1409 /* After getting ato_line_id, for top level parents and ato items,
1410 * we don't default any other columns for lines with item_type MODEL
1411 * and STANDARD */
1412
1413 IF g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
1414 g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD
1415 THEN
1416 --{ bug3601544 starts
1417 IF l_debug_level > 0 THEN
1418 OE_DEBUG_PUB.Add('IB Owner: '||g_line_rec.ib_owner,3);
1419 OE_DEBUG_PUB.Add('IB Install: '||g_line_rec.ib_installed_at_location,3);
1420 OE_DEBUG_PUB.Add('IB Current: '||g_line_rec.ib_current_location,3);
1421 END IF;
1422
1423 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
1424 ( p_line_id => g_line_rec.line_id
1425 ,p_top_model_line_id => g_line_rec.top_model_line_id
1426 ,p_ato_line_id => g_line_rec.ato_line_id
1427 ,p_inventory_item_id => g_line_rec.inventory_item_id
1428 ,x_top_container_model => l_top_container
1429 ,x_part_of_container => l_part_of_container );
1430
1431 IF l_top_container = 'Y' THEN
1432 g_line_rec.ib_owner := NULL;
1433 g_line_rec.ib_installed_at_location := NULL;
1434 g_line_rec.ib_current_location := NULL;
1435
1436 IF l_debug_level > 0 THEN
1437 OE_DEBUG_PUB.Add('IB Fields set to NULL for Top Container Line',3);
1438 END IF;
1439 END IF;
1440 -- bug3601544 ends }
1441
1442 IF l_debug_level > 0 THEN
1443 oe_debug_pub.add( 'RETURNING FOR ITEM TYPE MODEL AND STANDARD' , 1 ) ;
1444 END IF;
1445 RETURN;
1446 END IF;
1447
1448
1449
1450 /* If we are here, it means item_type_code is CLASS, OPTION, KIT under
1451 * a model,or CONFIG. Load top parent, so that we know if it is ato,
1452 * smc pto or nonsmc pto */
1453
1454 IF g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
1455 l_parent_line_id := g_line_rec.ato_line_id;
1456 ELSE
1457 l_parent_line_id := g_line_rec.top_model_line_id;
1458 END IF;
1459 --2605065 : commented. This has been added in procedure Attributes.
1460 --OE_Order_Cache.clear_top_model_line(l_parent_line_id);
1461
1462 l_top_model_line_rec := OE_Order_Cache.Load_Top_Model_Line
1463 (l_parent_line_id );
1464
1465
1466 /* We are introducing a new procedure Default_Child_Line in oe_config_util
1467 * to default all appropriate values from top model to its children.
1468 * In future if you need to copy anything from parent to children,
1469 * add code in oe_config_util */
1470
1471 oe_config_util.default_child_line
1472 (p_parent_line_rec => l_top_model_line_rec,
1473 p_x_child_line_rec => g_line_rec,
1474 x_return_status => l_return_status);
1475
1476 IF l_debug_level > 0 THEN
1477 oe_debug_pub.add( 'RETURN STATUS || ' || L_RETURN_STATUS , 3 ) ;
1478 END IF;
1479
1480 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1483 RAISE FND_API.G_EXC_ERROR;
1484 END IF;
1485
1486 IF l_debug_level > 0 THEN
1487 oe_debug_pub.add( 'EXITING MODEL_OPTION_DEFAULTING' , 1 ) ;
1488 END IF;
1489
1490 EXCEPTION
1491
1492 WHEN NO_DATA_FOUND THEN
1493 IF l_debug_level > 0 THEN
1494 oe_debug_pub.add( 'NO DATA FOUND IN MODEL_OPTION_DEFAULTING' , 1 ) ;
1495 END IF;
1496 RAISE FND_API.G_EXC_ERROR;
1497
1498 WHEN OTHERS THEN
1499 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1500 THEN
1501 OE_MSG_PUB.Add_Exc_Msg
1502 ( G_PKG_NAME ,
1503 'Model_Option_Defaulting'
1504 );
1505 END IF;
1506 IF l_debug_level > 0 THEN
1507 oe_debug_pub.add( 'OTHERS IN MODEL_OPTION_DEFAULTING' , 1 ) ;
1508 END IF;
1509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1510
1511 END Model_Option_Defaulting;
1512
1513
1514 /* -------------------------------------------------------------
1515 This procedure will be used to default data from their model
1516 in case of updates
1517 ----------------------------------------------------------------*/
1518
1519 PROCEDURE Model_Option_update
1520 (p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type)
1521 IS
1522 l_top_model_line_rec OE_ORDER_PUB.line_rec_type;
1523 l_parent_line_id NUMBER;
1524 l_return_status VARCHAR2(1);
1525 --
1526 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1527 --
1528 BEGIN
1529
1530 IF l_debug_level > 0 THEN
1531 oe_debug_pub.add( 'ENTERING MODEL_OPTION_UPDATE' , 1 ) ;
1532 oe_debug_pub.add( 'LINE_ID TO DEFAULT: '|| P_X_LINE_REC.LINE_ID , 1 ) ;
1533 END IF;
1534
1535 /* These updates are valid only for Options/clasees/included items */
1536
1537 IF p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE OR
1538 p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
1539 p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD OR
1540 (p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT AND
1541 p_x_line_rec.line_id = p_x_line_rec.top_modeL_line_id)
1542 THEN
1543
1544 IF l_debug_level > 0 THEN
1545 oe_debug_pub.add( 'RETURNING FOR ITEM TYPE SERVICE , MODEL , STANDARD AND TOP LEVEL KITS' , 1 ) ;
1546 END IF;
1547
1548 RETURN;
1549 END IF;
1550
1551 IF p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
1552 l_parent_line_id := p_x_line_rec.ato_line_id;
1553 ELSE
1554 l_parent_line_id := p_x_line_rec.top_model_line_id;
1555 END IF;
1556
1557 IF l_debug_level > 0 THEN
1558 oe_debug_pub.add( 'TOP MODEL LINE ID FOR TOP MODEL' || L_PARENT_LINE_ID , 1 ) ;
1559 END IF;
1560 -- 2605065 : Commented. This has been done in procedure Attributes.
1561 --OE_Order_Cache.clear_top_model_line(l_parent_line_id);
1562 l_top_model_line_rec := OE_Order_Cache.Load_Top_Model_Line
1563 (l_parent_line_id );
1564
1565 p_x_line_rec.ship_tolerance_above := l_top_model_line_rec.ship_tolerance_above;
1566 p_x_line_rec.ship_tolerance_below := l_top_model_line_rec.ship_tolerance_below;
1567
1568 IF l_debug_level > 0 THEN
1569 oe_debug_pub.add( 'EXITING MODEL_OPTION_UPDATE' , 1 ) ;
1570 END IF;
1571
1572 EXCEPTION
1573
1574 WHEN NO_DATA_FOUND THEN
1575 IF l_debug_level > 0 THEN
1576 oe_debug_pub.add( 'NO DATA FOUND IN MODEL_OPTION_UPDATE' , 1 ) ;
1577 END IF;
1578
1579 RAISE FND_API.G_EXC_ERROR;
1580
1581 WHEN OTHERS THEN
1582
1583 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1584 THEN
1585 OE_MSG_PUB.Add_Exc_Msg
1586 ( G_PKG_NAME ,
1587 'Model_Option_update'
1588 );
1589 END IF;
1590 IF l_debug_level > 0 THEN
1591 oe_debug_pub.add( 'OTHERS IN MODEL_OPTION_UPDATE' , 1 ) ;
1592 END IF;
1593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1594
1595 END Model_Option_Update;
1596
1597 /*-------------------------------------------------------------
1598 this procedure gets ato_line_id for top level ato model,
1599 ato_item and all children top ato model. It requires
1600 1) item_type_code
1601 2) line_id
1602 3) top_model_line_id
1603
1604 Change Record:
1605 bug 1894331
1606 the select statement for getting ato_line_id in case of
1607 pto+ato case is modified. look at the bug for more details.
1608 also made same change in OEXVCFGB.pls:update_ato_line_attribs.
1609 Bug 2513840
1610 Added Code to handle TOO_MANY_ROWS in Exception
1611 --------------------------------------------------------------*/
1612 FUNCTION Get_ATO_Line
1613 RETURN NUMBER
1614 IS
1615 l_ato_line_id NUMBER;
1616 l_temp_ato_line_id NUMBER;
1617 l_replenish_to_order_flag VARCHAR2(1);
1618 l_build_in_wip_flag VARCHAR2(1);
1619 l_bom_item_type NUMBER;
1620 l_ato_config_item_id NUMBER;
1621 --
1622 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1623 --
1624 BEGIN
1625
1626 IF l_debug_level > 0 THEN
1627 oe_debug_pub.add( 'IN GET_ATO_LINE , ITEM_TYPE_CODE :' || G_LINE_REC.ITEM_TYPE_CODE , 1 ) ;
1628 END IF;
1629
1630 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' AND
1631 g_line_rec.item_type_code = 'CONFIG' THEN
1632 IF l_debug_level > 0 THEN
1633 oe_debug_pub.add( 'PACK H MI , CONFIG LINE '|| G_LINE_REC.ATO_LINE_ID ) ;
1634 END IF;
1635 RETURN g_line_rec.ato_line_id;
1636 END IF;
1637
1638 IF ( g_line_rec.inventory_item_id is NULL OR
1639 g_line_rec.inventory_item_id = FND_API.G_MISS_NUM)
1640 THEN
1641 RETURN NULL;
1642 END IF;
1643
1644 IF g_line_rec.line_category_code =
1645 OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
1646 RETURN NULL;
1647 END IF;
1648
1649 OE_ORDER_CACHE.Load_Item
1650 (p_key1 => g_line_rec.inventory_item_id
1651 ,p_key2 => g_line_rec.ship_from_org_id);
1652
1653 l_replenish_to_order_flag :=
1654 OE_ORDER_CACHE.g_item_rec.replenish_to_order_flag;
1655 l_build_in_wip_flag := OE_ORDER_CACHE.g_item_rec.build_in_wip_flag;
1656 l_bom_item_type := OE_ORDER_CACHE.g_item_rec.bom_item_type;
1657
1658 IF l_debug_level > 0 THEN
1659 oe_debug_pub.add( 'GET_ATO_LINE: '||L_REPLENISH_TO_ORDER_FLAG||L_BUILD_IN_WIP_FLAG , 1 ) ;
1660 END IF;
1661
1662 -- top level ATO model and ato item.
1663 -- build in wip flag, see if req?
1664
1665 IF (g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL AND
1666 l_replenish_to_order_flag = 'Y') OR
1667 (g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD AND
1668 l_replenish_to_order_flag = 'Y' AND
1669 l_build_in_wip_flag = 'Y')OR
1670 (g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND
1671 l_replenish_to_order_flag = 'Y' AND --9775352
1672 l_build_in_wip_flag = 'Y' AND
1673 Nvl(fnd_profile.Value('ONT_HON_ATO_FOR_INC'),'N')='Y')
1674 THEN
1675 IF l_debug_level > 0 THEN
1676 oe_debug_pub.add( '1. ATO_LINE_ID: '||G_LINE_REC.LINE_ID , 1 ) ;
1677 END IF;
1678 l_ato_line_id := g_line_rec.line_id;
1679 RETURN (l_ato_line_id);
1680
1681 ELSE
1682 /* we have to set ato_line_id for all options
1683 * classes, config item which are under top ato model
1684 * ato model (ont: item_type_code is CLASS, bom_item_type = 1)
1685 * under top ato model will have ato_line_id = line_id
1686 * of top ato parent. kit can not be under an ATO */
1687
1688 IF g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
1689 g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1690 g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
1691
1692 OE_Order_Cache.Load_Top_Model_Line
1693 (g_line_rec.top_model_line_id );
1694 l_ato_line_id := OE_Order_Cache.g_top_model_line_rec.ato_line_id;
1695
1696 IF l_ato_line_id is NULL THEN
1697
1698 IF l_debug_level > 0 THEN
1699 oe_debug_pub.add( 'TOP MODEL IS PTO' , 3 ) ;
1700 END IF;
1701
1702 IF (g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS AND
1703 l_replenish_to_order_flag = 'Y' AND
1704 l_bom_item_type = 1) THEN
1705
1706 IF l_debug_level > 0 THEN
1707 oe_debug_pub.add( 'MAYBE ATO LINE '||G_LINE_REC.LINE_ID , 3 ) ;
1708 END IF;
1709 l_temp_ato_line_id := g_line_rec.line_id;
1710 END IF;
1711
1712
1713 BEGIN
1714
1715 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508' AND
1716 g_line_rec.config_header_id is not NULL AND
1717 g_line_rec.config_header_id <> FND_API.G_MISS_NUM AND
1718 g_line_rec.configuration_id is not NULL AND
1719 g_line_rec.configuration_id <> FND_API.G_MISS_NUM
1720 THEN
1721 IF l_debug_level > 0 THEN
1722 oe_debug_pub.add( 'GET_ATO: PACK H NEW LOGIC MI ' || G_LINE_REC.CONFIG_HEADER_ID , 1 ) ;
1723 oe_debug_pub.add( 'CONFIGN ID ' || G_LINE_REC.CONFIGURATION_ID , 1 ) ;
1724 END IF;
1725
1726 SELECT ato_config_item_id
1727 INTO l_ato_config_item_id
1728 FROM cz_config_details_v
1729 WHERE config_hdr_id = g_line_rec.config_header_id
1730 AND config_rev_nbr = g_line_rec.config_rev_nbr
1731 AND config_item_id = g_line_rec.configuration_id
1732 AND inventory_item_id = g_line_rec.inventory_item_id;
1733 IF l_debug_level > 0 THEN
1734 oe_debug_pub.add( 'GET_ATO: MI ' || L_ATO_CONFIG_ITEM_ID , 1 ) ;
1735 END IF;
1736
1737 IF l_ato_config_item_id is NOT NULL THEN
1738 SELECT line_id
1739 INTO l_ato_line_id
1740 FROM OE_ORDER_LINES_ALL OEOPT
1741 WHERE line_id =
1742 (SELECT line_id
1743 FROM oe_order_lines OEATO
1744 WHERE OEOPT.top_model_line_id = OEATO.top_model_line_id
1745 AND OEATO.configuration_id = l_ato_config_item_id
1746 AND OEATO.open_flag = 'Y')
1747 AND top_model_line_id = g_line_rec.top_model_line_id;
1748
1749 ELSE
1750 IF l_debug_level > 0 THEN
1751 oe_debug_pub.add('CONFIG_ITEM_ID NOT RETURNED FROM CZ');
1752 END IF;
1753 IF g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION
1754 AND
1755 l_replenish_to_order_flag = 'Y' AND
1756 l_build_in_wip_flag = 'Y'
1757 THEN
1758 IF l_debug_level > 0 THEN
1759 oe_debug_pub.add( 'ATO ITEM UNDER PTO MODEL' , 1 ) ;
1760 END IF;
1761 RETURN g_line_rec.line_id;
1762 END IF;
1763 END IF;
1764
1765 IF l_debug_level > 0 THEN
1766 oe_debug_pub.add( 'PTO+ATO SELECT '||L_ATO_LINE_ID , 1 ) ;
1767 END IF;
1768
1769 ELSE
1770
1771 IF l_debug_level > 0 THEN
1772 oe_debug_pub.add( 'USE OE_ORDER_LINES' , 3 ) ;
1773 END IF;
1774
1775 SELECT line_id
1776 INTO l_ato_line_id
1777 FROM OE_ORDER_LINES_ALL
1778 WHERE top_model_line_id = g_line_rec.top_model_line_id
1779 AND item_type_code = 'CLASS'
1780 AND component_code =
1781 SUBSTR( g_line_rec.component_code, 1,
1782 LENGTH(component_code))
1783 AND ato_line_id is not null
1784 AND open_flag = 'Y'
1785 AND component_code =
1786 ( SELECT MIN(OEMIN.component_code)
1787 FROM OE_ORDER_LINES_ALL OEMIN
1788 WHERE OEMIN.top_model_line_id
1789 = g_line_rec.top_model_line_id
1790 AND OEMIN.component_code =
1791 SUBSTR( g_line_rec.component_code, 1,
1792 LENGTH( OEMIN.component_code))
1793 AND OEMIN.ato_line_id is not null
1794 AND OEMIN.open_flag = 'Y')
1795 AND (SUBSTR(g_line_rec.component_code,
1796 LENGTH(component_code) + 1, 1) = '-' OR
1797 SUBSTR(g_line_rec.component_code,
1798 LENGTH(component_code) + 1, 1) is NULL);
1799
1800
1801 IF l_debug_level > 0 THEN
1802 oe_debug_pub.add( 'PTO+ATO SELECT '||L_ATO_LINE_ID , 1 ) ;
1803 END IF;
1804 END IF;
1805
1806 EXCEPTION
1807 WHEN no_data_found THEN
1808 IF l_debug_level > 0 THEN
1809 oe_debug_pub.add( 'NO DATA FOUND PTO CASE '|| L_TEMP_ATO_LINE_ID , 3 ) ;
1810 END IF;
1811
1812 -- ## 1820608 ato item under a top pto model
1813 -- should have line_id = ato_line_id, if ato_item is
1814 -- under a ato sub config, its ato_line_id = line_id
1815 -- of the ato sub config.
1816
1817 IF g_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION
1818 AND
1819 l_replenish_to_order_flag = 'Y' AND
1820 l_build_in_wip_flag = 'Y'
1821 THEN
1822 IF l_debug_level > 0 THEN
1823 oe_debug_pub.add( 'ATO ITEM UNDER PTO MODEL' , 1 ) ;
1824 END IF;
1825 RETURN g_line_rec.line_id;
1826 ELSE
1827 RETURN l_temp_ato_line_id;
1828 END IF;
1829
1830 WHEN too_many_rows THEN
1831 -- Added for Bug-2367800
1832 FND_MESSAGE.Set_Name('ONT', 'OE_CONFIG_DUPLICATE_COMPONENT');
1833 FND_MESSAGE.Set_Token('ITEM', nvl(g_line_rec.ordered_item,g_line_rec.inventory_item_id));
1834 OE_Msg_Pub.Add;
1835 IF l_debug_level > 0 THEN
1836 oe_debug_pub.add( 'TOO MANY ROWS CASE '|| G_LINE_REC.INVENTORY_ITEM_ID , 1 ) ;
1837 END IF;
1838 RAISE;
1839
1840 WHEN OTHERS THEN
1841 IF l_debug_level > 0 THEN
1842 oe_debug_pub.add( 'ATO LINE EXCEPTION ' , 3 ) ;
1843 END IF;
1844 RAISE;
1845 END;
1846 END IF;
1847
1848 IF l_debug_level > 0 THEN
1849 oe_debug_pub.add( '2. ATO LINE ID : '|| L_ATO_LINE_ID , 3 ) ;
1850 END IF;
1851
1852 RETURN l_ato_line_id;
1853
1854 ELSE
1855 IF l_debug_level > 0 THEN
1856 oe_debug_pub.add( 'RETURN ATO_LINE_ID AS NULL' , 1 ) ;
1857 END IF;
1858 RETURN null;
1859 END IF;
1860 END IF;
1861
1862 IF l_debug_level > 0 THEN
1863 oe_debug_pub.add( 'GG:LINE_ID : ' || G_LINE_REC.LINE_ID , 1 ) ;
1864 oe_debug_pub.add( 'GG:ATO LINE ID : ' || G_LINE_REC.ATO_LINE_ID , 1 ) ;
1865 END IF;
1866
1867 EXCEPTION
1868
1869 WHEN TOO_MANY_ROWS THEN
1870 RAISE FND_API.G_EXC_ERROR;
1871
1872 WHEN OTHERS THEN
1873
1874 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1875 THEN
1876 OE_MSG_PUB.Add_Exc_Msg
1877 ( G_PKG_NAME ,
1878 'Get_ATO_Line'
1879 );
1880 END IF;
1881
1882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1883
1884
1885 END Get_ATO_line;
1886
1887 /*---------------------------------------------------------------------
1888 PROCEDURE Insert_into_set
1889 This procedure will insert children of model into fulfillment
1890 set id if the parent is part of a set.
1891 Parent might exists in multiple fulfillment sets,
1892 so get all the set_id's that
1893 parent belong to and insert the children in all sets.
1894 ---------------------------------------------------------------------*/
1895 PROCEDURE Insert_Into_set
1896 ( p_line_id IN NUMBER
1897 ,p_child_line_id IN NUMBER
1898 ,x_return_status OUT NOCOPY VARCHAR2)
1899
1900 IS
1901
1902 CURSOR parent_sets IS
1903 Select set_id
1904 From oe_line_sets
1905 Where line_id = p_line_id;
1906 --
1907 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1908 --
1909 BEGIN
1910
1911 IF l_debug_level > 0 THEN
1912 oe_debug_pub.add( 'ENTERING INTO INSERT_INTO_SET' , 1 ) ;
1913 oe_debug_pub.add( 'TOP MODEL IS ' || P_LINE_ID , 1 ) ;
1914 oe_debug_pub.add( 'CHILD LINE IS ' || P_CHILD_LINE_ID , 1 ) ;
1915 END IF;
1916 FOR i IN parent_sets LOOP
1917 IF l_debug_level > 0 THEN
1918 oe_debug_pub.add( 'ENTERING INTO LOOP -SET' ||I.SET_ID , 1 ) ;
1919 END IF;
1920
1921
1922
1923 OE_SET_UTIL.Create_Fulfillment_set
1924 (p_line_id => p_child_line_id,
1925 p_set_id => i.set_id);
1926
1927
1928
1929 END LOOP;
1930
1931 x_return_status := FND_API.G_RET_STS_SUCCESS;
1932 IF l_debug_level > 0 THEN
1933 oe_debug_pub.add( 'EXITING FROM INSERT_INTO_SET' , 1 ) ;
1934 END IF;
1935
1936 EXCEPTION
1937
1938 WHEN OTHERS THEN
1939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1940
1941 END Insert_Into_set;
1942
1943
1944 FUNCTION Get_Fulfilled_Quantity
1945 RETURN NUMBER
1946 IS
1947 --
1948 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1949 --
1950 BEGIN
1951
1952 RETURN NULL;
1953
1954 END Get_Fulfilled_Quantity;
1955
1956 --Procedure to check change in item_type_code
1957 PROCEDURE Check_Item_Type(p_line_rec OE_ORDER_PUB.Line_Rec_Type,
1958 p_old_line_rec OE_ORDER_PUB.Line_Rec_Type,
1959 p_item_type_code VARCHAR2)
1960 IS
1961 --
1962 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1963 --
1964 BEGIN
1965 IF l_debug_level > 0 THEN
1966 oe_debug_pub.add( 'ENTERING CHECK ITEM TYPE' ) ;
1967 oe_debug_pub.add( 'ITEM TYPE '|| P_ITEM_TYPE_CODE ) ;
1968 END IF;
1969 IF p_line_rec.operation = oe_globals.g_opr_update THEN
1970 IF (p_old_line_rec.item_type_code <> FND_API.G_MISS_CHAR AND
1971 p_old_line_rec.item_type_code IS NOT NULL) THEN
1972
1973 IF NOT OE_GLOBALS.EQUAL(p_old_line_rec.item_type_code,
1974 p_item_type_code) THEN
1975
1976 FND_MESSAGE.SET_NAME('ONT','OE_ITEM_TYPE_CONST');
1977 OE_MSG_PUB.ADD;
1978 IF l_debug_level > 0 THEN
1979 oe_debug_pub.add( 'ITEM_TYPE_CODE CONSTRAINED' ) ;
1980 END IF;
1981 RAISE FND_API.G_EXC_ERROR;
1982
1983 END IF;
1984 END IF;
1985 END IF;
1986
1987 IF l_debug_level > 0 THEN
1988 oe_debug_pub.add( 'ITEM_TYPE_CODE : OPERATION IS CREATE ' ) ;
1989 END IF;
1990
1991 END Check_Item_Type;
1992
1993 FUNCTION Get_Item_Type(p_line_rec OE_ORDER_PUB.Line_Rec_Type,
1994 p_old_line_rec OE_ORDER_PUB.Line_Rec_Type)
1995 RETURN VARCHAR2
1996 IS
1997 l_item_type_code VARCHAR2(30) := NULL;
1998 l_bom_item_type VARCHAR2(30);
1999 l_service_item_flag VARCHAR2(1);
2000 l_pick_components_flag VARCHAR2(1);
2001 --
2002 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2003 --
2004 BEGIN
2005
2006 IF l_debug_level > 0 THEN
2007 oe_debug_pub.add( 'IN DEFAULTING: FUNCTION GET_ITEM_TYPE' , 1 ) ;
2008 oe_debug_pub.add( 'THE INV ITEM IS'||TO_CHAR ( G_LINE_REC.INVENTORY_ITEM_ID ) , 1 ) ;
2009 --6933507
2010 oe_debug_pub.add( ' line_category_code = '|| p_line_rec.line_category_code , 1 ) ;
2011 oe_debug_pub.add( ' retrobill_request_id = '|| p_line_rec.retrobill_request_id , 1 ) ;
2012 oe_debug_pub.add( ' item_type_code = '|| p_line_rec.item_type_code , 1 ) ;
2013 --6933507
2014 END IF;
2015
2016 IF ( g_line_rec.inventory_item_id is NULL OR
2017 g_line_rec.inventory_item_id = FND_API.G_MISS_NUM )
2018 THEN
2019 -- Bug 4721305 condition added to ignore when inventory item is nulled
2020 IF p_line_rec.ITEM_TYPE_CODE IS NOT NULL AND
2021 p_line_rec.ITEM_TYPE_CODE <> FND_API.G_MISS_CHAR THEN
2022 Check_Item_Type(p_line_rec,
2023 p_old_line_rec,
2024 NULL);
2025 END IF;
2026 RETURN NULL;
2027 END IF;
2028
2029
2030 IF p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
2031 g_line_rec.subscription_enable_flag := 'N'; -- sol_ord_er #16014165
2032 RETURN OE_GLOBALS.G_ITEM_STANDARD;
2033 --- BUG#6933507 : retrun STANDARD in case of retrobill SO (type = ORDER)
2034 ELSIF p_line_rec.line_category_code = 'ORDER'
2035 and p_line_rec.retrobill_request_id is NOT NULL
2036 and p_line_rec.retrobill_request_id <> FND_API.G_MISS_NUM THEN
2037
2038 RETURN OE_GLOBALS.G_ITEM_STANDARD;
2039 --- BUG#6933507
2040 /*ELSIF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG OR
2041 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN*/
2042 -- sol_ord_er #16014165 commented above as Included Item can be Subscription Service */
2043 -- LABEL: SOL_ORD 100
2044 ELSIF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
2045 RETURN p_line_rec.item_type_code;
2046 END IF;
2047
2048
2049 OE_Order_Cache.Load_Item (g_line_rec.inventory_item_id
2050 ,g_line_rec.ship_from_org_id);
2051 l_bom_item_type := OE_ORDER_CACHE.g_item_rec.bom_item_type;
2052 l_service_item_flag := OE_ORDER_CACHE.g_item_rec.service_item_flag;
2053 l_pick_components_flag := OE_ORDER_CACHE.g_item_rec.pick_components_flag;
2054
2055 IF l_debug_level > 0 THEN
2056 oe_debug_pub.add( 'BOM ITEM TYPE IS ' || L_BOM_ITEM_TYPE ) ;
2057 END IF;
2058 -- sol_ord_er #16014165
2059 If(OE_ORDER_CACHE.g_item_rec.contract_item_type_code = OE_GLOBALS.G_ITEM_SUBSCRIPTION)
2060 THEN
2061 /* If System Parameter is set to default behaviour of subscription Item as Service */
2062 If( OE_Sys_Parameters.VALUE('OM_DEF_SUBSCRIPTION_BEHAVIOUR',p_line_rec.org_id) = 'SUB')
2063 AND p_line_rec.subscription_enable_flag <> 'N' THEN
2064 g_line_rec.subscription_enable_flag := 'Y';
2065 IF l_debug_level > 0 THEN
2066 oe_debug_pub.add('SK OEXDLINB.pls SUBSCRIPTION1 Y');
2067 END IF;
2068 Else
2069 /* If default system parameter is NULL or Standard but via PO API value passed is Y then store Y */
2070 If p_line_rec.subscription_enable_flag = 'Y' then
2071 IF l_debug_level > 0 THEN
2072 oe_debug_pub.add('SK OEXDLINB.pls SUBSCRIPTION2 Y');
2073 End If;
2074 g_line_rec.subscription_enable_flag := 'Y';
2075 else
2076 g_line_rec.subscription_enable_flag := 'N';
2077 IF l_debug_level > 0 THEN
2078 oe_debug_pub.add('SK OEXDLINB.pls SUBSCRIPTION3 N');
2079 END IF;
2080 end if;
2081 end if;
2082 /* If the Item is not a Subscription Item */
2083 else
2084 g_line_rec.subscription_enable_flag := 'N';
2085 IF l_debug_level > 0 THEN
2086 oe_debug_pub.add('SK OEXDLINB.pls SUBSCRIPTION4 N');
2087 End If;
2088 End If;
2089 /* Logic commented at LABEL: SOL_ORD 100 is implemented here */
2090 If p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
2091 RETURN p_line_rec.item_type_code;
2092 End If;
2093 -- sol_ord_er #16014165 end
2094
2095 IF l_bom_item_type = 1
2096 -- MODEL items and ato's under pto have bom_item_type = 1
2097 THEN
2098
2099 IF nvl(g_line_rec.top_model_line_id, 0) <> nvl(g_line_rec.line_id, 0)
2100 -- OR
2101 -- nvl(g_line_rec.top_model_line_index, 0) <> nvl(g_line_rec.line_index,0)
2102 -- line_rec dose not have line_index
2103 THEN
2104 IF l_debug_level > 0 THEN
2105 oe_debug_pub.add( 'RETURNING CLASS AS THE ITEM TYPE FOR ATO SUBCONFIG' , 1 ) ;
2106 END IF;
2107 --Procedure to check change in item_type_code
2108 Check_Item_Type(p_line_rec,
2109 p_old_line_rec,
2110 OE_GLOBALS.G_ITEM_CLASS);
2111 RETURN OE_GLOBALS.G_ITEM_CLASS;
2112 END IF;
2113
2114 IF l_debug_level > 0 THEN
2115 oe_debug_pub.add( 'RETURNING MODEL AS THE ITEM TYPE' , 1 ) ;
2116 END IF;
2117 --Procedure to check change in item_type_code
2118 Check_Item_Type(p_line_rec,
2119 p_old_line_rec,
2120 OE_GLOBALS.G_ITEM_MODEL);
2121 RETURN OE_GLOBALS.G_ITEM_MODEL;
2122
2123 ELSIF l_bom_item_type = 2
2124 THEN
2125 IF l_debug_level > 0 THEN
2126 oe_debug_pub.add( 'RETURNING CLASS AS THE ITEM TYPE' , 1 ) ;
2127 END IF;
2128 -- Only CLASS items have bom_item_type = 2
2129 --Procedure to check change in item_type_code
2130 Check_Item_Type(p_line_rec,
2131 p_old_line_rec,
2132 OE_GLOBALS.G_ITEM_CLASS);
2133 RETURN OE_GLOBALS.G_ITEM_CLASS;
2134
2135 ELSIF l_bom_item_type = 4 and
2136 l_service_item_flag = 'N'
2137 THEN
2138
2139 IF l_debug_level > 0 THEN
2140 oe_debug_pub.add( 'BOM 4 AND FLAG = N' ) ;
2141 END IF;
2142 -- Following 3 items can have bom_item_type = 4 :
2143 -- STANDARD item, OPTION item and a KIT
2144 -- We will distinguish an item to be a kit by seeing if
2145 -- it has a record in bom_bill_of_materials.
2146 -- All options MUST have the top_model_line_id populated
2147 -- before they come to defaulting. Thus we use it to distinguish
2148 -- between a standard and an option item.
2149 -- ato_item's item_type_code will be standard
2150
2151 IF l_pick_components_flag = 'Y'
2152 --16559475 start commented below part of code from IF statement
2153 THEN
2154 --AND nvl(g_line_rec.top_model_line_id, 0) = nvl(g_line_rec.line_id, 0) THEN-- sol_ord_er #16014165
2155 --16559475 end
2156 l_item_type_code := OE_GLOBALS.G_ITEM_KIT;
2157 ELSIF (g_line_rec.top_model_line_id is not null AND
2158 g_line_rec.top_model_line_id <> FND_API.G_MISS_NUM)
2159 THEN
2160 IF l_debug_level > 0 THEN
2161 oe_debug_pub.add( 'GET_ITEM_TYPE NO DATA FOUND , BOM_ITEM_TYPE : 4' , 1 ) ;
2162 END IF;
2163 l_item_type_code := OE_GLOBALS.G_ITEM_OPTION;
2164 -- sol_ord_er #16014165
2165 ELSIF g_line_rec.subscription_enable_flag = 'Y' then
2166 IF l_debug_level > 0 THEN
2167 oe_debug_pub.add('SK OEXDLINB.pls 7.1 Returning Service');
2168 End If;
2169 l_item_type_code := OE_GLOBALS.G_ITEM_SERVICE;
2170 -- sol_ord_er #16014165 end
2171 ELSE
2172 l_item_type_code := OE_GLOBALS.G_ITEM_STANDARD;
2173 END IF;
2174
2175 IF l_debug_level > 0 THEN
2176 oe_debug_pub.add( ' BEFORE CALLING CHECK 1' ) ;
2177 END IF;
2178 --Procedure to check change in item_type_code
2179 Check_Item_Type(p_line_rec,
2180 p_old_line_rec,
2181 l_item_type_code);
2182 RETURN l_item_type_code;
2183
2184 ELSIF l_service_item_flag = 'Y' and
2185 l_bom_item_type = 4
2186 THEN
2187 IF l_debug_level > 0 THEN
2188 oe_debug_pub.add( 'SERVICE ITEM FLAG IS: ' || L_SERVICE_ITEM_FLAG ) ;
2189 END IF;
2190 --Procedure to check change in item_type_code
2191 Check_Item_Type(p_line_rec,
2192 p_old_line_rec,
2193 OE_GLOBALS.G_ITEM_SERVICE);
2194 RETURN OE_GLOBALS.G_ITEM_SERVICE;
2195
2196 END IF;
2197
2198 RETURN null;
2199
2200 IF l_debug_level > 0 THEN
2201 oe_debug_pub.add( 'EXITING DEFAULTING: FUNCTION GET_ITEM_TYPE' ) ;
2202 END IF;
2203
2204 EXCEPTION
2205
2206 WHEN NO_DATA_FOUND THEN
2207 IF l_debug_level > 0 THEN
2208 oe_debug_pub.add( ' BEFORE CALLING CHECK 4' ) ;
2209 END IF;
2210 l_item_type_code := OE_GLOBALS.G_ITEM_STANDARD;
2211 --Procedure to check change in item_type_code
2212 Check_Item_Type(p_line_rec,
2213 p_old_line_rec,
2214 l_item_type_code);
2215 RETURN l_item_type_code;
2216
2217 WHEN OTHERS THEN
2218
2219 -- 4594675
2220 /*
2221 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2222 THEN
2223 OE_MSG_PUB.Add_Exc_Msg
2224 ( G_PKG_NAME ,
2225 'Get_Item_Type'
2226 );
2227 END IF;
2228 */
2229
2230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2231
2232 END Get_Item_Type;
2233
2234
2235 FUNCTION Get_Line
2236 RETURN NUMBER
2237 IS
2238 l_line_id NUMBER := NULL;
2239 --
2240 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2241 --
2242 BEGIN
2243
2244 SELECT OE_ORDER_LINES_S.NEXTVAL
2245 INTO l_line_id
2246 FROM DUAL;
2247
2248 RETURN l_line_id;
2249
2250 END Get_Line;
2251
2252 FUNCTION Get_Orig_Sys_Doc_Ref
2253 RETURN VARCHAR2
2254 IS
2255 --
2256 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2257 --
2258 BEGIN
2259
2260 OE_ORDER_CACHE.Load_Order_Header(g_line_rec.header_id);
2261 RETURN (OE_ORDER_CACHE.g_header_rec.Orig_Sys_Document_Ref);
2262
2263 END Get_Orig_Sys_Doc_Ref;
2264
2265
2266 FUNCTION Get_Org
2267 RETURN NUMBER
2268 IS
2269 l_Org_id NUMBER := NULL;
2270 --
2271 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2272 --
2273 BEGIN
2274
2275 OE_GLOBALS.Set_Context;
2276 l_org_id := OE_GLOBALS.G_ORG_ID;
2277
2278 RETURN l_Org_Id;
2279
2280 END Get_Org;
2281
2282 FUNCTION Get_Line_Category(p_line_rec OE_ORDER_PUB.Line_Rec_Type,
2283 p_old_line_rec OE_ORDER_PUB.Line_Rec_Type)
2284 RETURN VARCHAR2
2285 IS
2286 l_order_category varchar2(30);
2287 l_category varchar2(30) := NULL;
2288 --
2289 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2290 --
2291 BEGIN
2292 IF l_debug_level > 0 THEN
2293 oe_debug_pub.add( 'IN DEFAULTING: FUNCTION GET_LINE_CATEGORY' , 1 ) ;
2294 END IF;
2295
2296 /* replaced with the following IF for 2421909
2297 IF (p_line_rec.operation = oe_globals.g_opr_create) and
2298 (p_line_rec.line_type_id IS NULL OR
2299 p_line_rec.line_type_id = FND_API.G_MISS_NUM) THEN
2300 */
2301
2302 IF (p_line_rec.line_type_id IS NULL OR -- 2421909
2303 p_line_rec.line_type_id = FND_API.G_MISS_NUM) THEN
2304
2305 IF l_debug_level > 0 THEN
2306 oe_debug_pub.add( 'AK IN DEFAULTING: WHEN LINE_TYPE_ID IS NULL' , 1 ) ;
2307 oe_debug_pub.add( 'AK IN DEFAULTING:' || P_LINE_REC.HEADER_ID , 1 ) ;
2308 END IF;
2309
2310 /* Replaced with the following IF statement for 2421909
2311 OE_ORDER_CACHE.Load_Order_Header(p_line_rec.header_id);
2312 l_order_category := OE_ORDER_CACHE.g_header_rec.ORDER_CATEGORY_CODE;
2313 */
2314
2315 IF (p_line_rec.operation = oe_globals.g_opr_create)
2316 THEN -- 2421909ND
2317
2318 IF l_debug_level > 0 THEN
2319 oe_debug_pub.add( 'AK IN DEFAULTING: OPERATION IS CREATE' , 1 ) ;
2320 END IF;
2321 OE_ORDER_CACHE.Load_Order_Header(p_line_rec.header_id);
2322 l_order_category := OE_ORDER_CACHE.g_header_rec.ORDER_CATEGORY_CODE;
2323 ELSE
2324 l_order_category := p_old_line_rec.line_category_code;
2325 END IF;
2326
2327 ELSE
2328
2329 IF l_debug_level > 0 THEN
2330 oe_debug_pub.add( 'AK IN DEFAULTING: WHEN LINE_TYPE_ID IS NOT NULL' , 1 ) ;
2331 END IF;
2332 OE_ORDER_CACHE.Load_Line_Type(p_line_rec.line_type_id);
2333 l_order_category := OE_ORDER_CACHE.g_line_type_rec.ORDER_CATEGORY_CODE;
2334
2335 END IF;
2336
2337 IF l_order_category = 'RETURN' THEN
2338 l_category := 'RETURN';
2339 ELSE
2340 l_category := 'ORDER';
2341 END IF;
2342 --retro{In the case of price increase for original line, the initial
2343 --retrobill line will be created with line_category return and has to be
2344 --updated to order
2345 IF (p_line_rec.operation = oe_globals.g_opr_update AND
2346 p_old_line_rec.line_category_code = 'RETURN' AND
2347 p_line_rec.line_category_code = 'ORDER' AND
2348 p_line_rec.order_source_id = 27 ) THEN
2349 l_category := 'ORDER';
2350 IF l_debug_level > 0 THEN
2351 oe_debug_pub.add( 'Retrobill price increase Line Category ' || L_CATEGORY ) ;
2352 END IF;
2353 END IF;
2354 --retro}
2355 IF l_debug_level > 0 THEN
2356 oe_debug_pub.add( 'LINE CATEGORY: ' || L_CATEGORY ) ;
2357 END IF;
2358
2359 IF p_line_rec.operation = oe_globals.g_opr_update THEN
2360
2361 IF (p_old_line_rec.line_category_code <> FND_API.G_MISS_CHAR AND
2362 p_old_line_rec.line_category_code IS NOT NULL) THEN
2363
2364 IF NOT OE_GLOBALS.EQUAL(p_old_line_rec.line_category_code,
2365 l_category) THEN
2366 --retro{In the case of price increase for original line, the initial
2367 --retrobill line will be created with line_category return and has to be
2368 --updated to order.The original flow doesn't allow and raises exception,To
2369 --prevent the exception a if loop is added in the case of retrobilling
2370 IF (p_line_rec.operation = oe_globals.g_opr_update AND
2371 p_old_line_rec.line_category_code = 'RETURN' AND
2372 p_line_rec.line_category_code = 'ORDER' AND
2373 p_line_rec.order_source_id = 27 AND
2374 p_line_rec.retrobill_request_id is not null) THEN
2375 null;
2376 else
2377 FND_MESSAGE.SET_NAME('ONT', 'OE_LINE_CAT_CONST');
2378 OE_MSG_PUB.ADD;
2379 IF l_debug_level > 0 THEN
2380 oe_debug_pub.add( 'LINE CATEGORY CONSTRINED' ) ;
2381 END IF;
2382 RAISE FND_API.G_EXC_ERROR;
2383 END IF;
2384 END IF;
2385
2386 END IF;
2387
2388 END IF;
2389
2390
2391 IF l_debug_level > 0 THEN
2392 oe_debug_pub.add( 'IN DEFAULTING: RETURNLINECATEROY' , 1 ) ;
2393 END IF;
2394 RETURN l_category;
2395
2396 EXCEPTION
2397
2398 WHEN NO_DATA_FOUND THEN
2399 IF l_debug_level > 0 THEN
2400 oe_debug_pub.add( 'IN DEFAULTING: NO DATAFOUND' , 1 ) ;
2401 END IF;
2402 l_category := 'ORDER';
2403 IF p_line_rec.operation = oe_globals.g_opr_update THEN
2404
2405 IF (p_old_line_rec.line_category_code <> FND_API.G_MISS_CHAR AND
2406 p_old_line_rec.line_category_code IS NOT NULL) THEN
2407
2408 IF NOT OE_GLOBALS.EQUAL(p_old_line_rec.line_category_code,
2409 l_category) THEN
2410 FND_MESSAGE.SET_NAME('ONT', 'OE_LINE_CAT_CONST');
2411 OE_MSG_PUB.ADD;
2412 IF l_debug_level > 0 THEN
2413 oe_debug_pub.add( 'LINE CATEGORY CONSTRINED' ) ;
2414 END IF;
2415 RAISE FND_API.G_EXC_ERROR;
2416 END IF;
2417
2418 END IF;
2419 END IF;
2420 RETURN l_category;
2421
2422 WHEN OTHERS THEN
2423
2424 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2425 THEN
2426 OE_MSG_PUB.Add_Exc_Msg
2427 ( G_PKG_NAME ,
2428 'Line_Category'
2429 );
2430 END IF;
2431
2432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2433
2434
2435 END Get_Line_Category;
2436
2437
2438
2439 FUNCTION Get_Line_Number
2440 RETURN NUMBER
2441 IS
2442 l_line_number NUMBER := NULL;
2443 --
2444 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2445 --
2446 BEGIN
2447
2448 IF l_debug_level > 0 THEN
2449 oe_debug_pub.add( 'IN PKG OE_DEFAULT_LINE: FUNCTION GET_LINE_NUMBER' ) ;
2450 END IF;
2451
2452 IF g_line_rec.top_model_line_id IS NULL
2453 OR g_line_rec.top_model_line_id = FND_API.G_MISS_NUM
2454 OR g_line_rec.line_id = g_line_rec.top_model_line_id
2455 THEN
2456
2457 SELECT NVL(MAX(LINE_NUMBER)+1,1)
2458 INTO l_line_number
2459 FROM OE_ORDER_LINES_ALL
2460 WHERE HEADER_ID = g_line_rec.header_id;
2461
2462 RETURN (l_line_number);
2463
2464 ELSE
2465
2466 IF l_debug_level > 0 THEN
2467 oe_debug_pub.add( 'LOADING TOP_MODEL_LINE_ID: ' || G_LINE_REC.TOP_MODEL_LINE_ID ) ;
2468 END IF;
2469 OE_Order_Cache.Load_top_model_line
2470 (g_line_rec.top_model_line_id );
2471 l_line_number := OE_Order_Cache.g_top_model_line_rec.line_number;
2472
2473 RETURN (l_line_number);
2474
2475 IF l_debug_level > 0 THEN
2476 oe_debug_pub.add( 'LOADDED TOP_MODEL_LINE_ID ' ) ;
2477 END IF;
2478
2479 END IF;
2480
2481 EXCEPTION
2482
2483 WHEN OTHERS THEN
2484
2485 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2486 THEN
2487 OE_MSG_PUB.Add_Exc_Msg
2488 ( G_PKG_NAME ,
2489 'Get_Line_Number'
2490 );
2491 END IF;
2492
2493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2494
2495 END Get_Line_Number;
2496
2497 FUNCTION Get_Latest_Acceptable_Date(p_request_date IN DATE)
2498 RETURN DATE
2499 IS
2500 l_latest_acceptable_date DATE;
2501 --
2502 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2503 --
2504 BEGIN
2505
2506 OE_ORDER_CACHE.Load_Order_Header(g_line_rec.header_id);
2507
2508 l_latest_acceptable_date := p_request_date +
2509 OE_ORDER_CACHE.g_header_rec.latest_schedule_limit;
2510
2511 RETURN l_latest_acceptable_date;
2512
2513 EXCEPTION
2514 WHEN OTHERS THEN
2515 l_latest_acceptable_date := null;
2516 RETURN l_latest_acceptable_date;
2517 END Get_Latest_Acceptable_Date;
2518
2519 FUNCTION Get_Pricing_Quantity
2520 RETURN NUMBER
2521 IS
2522 --
2523 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2524 --
2525 BEGIN
2526
2527 IF g_line_rec.ordered_quantity = FND_API.G_MISS_NUM then
2528 RETURN NULL;
2529 ELSE
2530 IF g_line_rec.pricing_quantity_uom is not null
2531 AND g_line_rec.pricing_quantity_uom <> FND_API.G_MISS_CHAR
2532 THEN
2533 RETURN (OE_Order_Misc_Util.convert_uom(g_line_rec.inventory_item_id,
2534 g_line_rec.order_quantity_uom,
2535 g_line_rec.pricing_quantity_uom,
2536 g_line_rec.ordered_quantity));
2537 ELSE
2538 RETURN NULL;
2539 END IF;
2540 END IF;
2541
2542 EXCEPTION
2543
2544 WHEN OTHERS THEN
2545
2546 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2547 THEN
2548 OE_MSG_PUB.Add_Exc_Msg
2549 ( G_PKG_NAME ,
2550 'Get_Pricing_Quantity'
2551 );
2552 END IF;
2553
2554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2555
2556 END Get_Pricing_Quantity;
2557
2558 FUNCTION Get_Shipment_Number
2559 RETURN NUMBER
2560 IS
2561 l_ship_number NUMBER := NULL;
2562 --
2563 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2564 --
2565 BEGIN
2566
2567 IF l_debug_level > 0 THEN
2568 oe_debug_pub.add( 'IN PKG OE_DEFAULT_LINE: FUNCTION GET_SHIPMENT_NUMBER' ) ;
2569 END IF;
2570
2571 IF g_line_rec.top_model_line_id IS NULL
2572 OR g_line_rec.top_model_line_id = FND_API.G_MISS_NUM
2573 OR g_line_rec.line_id = g_line_rec.top_model_line_id
2574 THEN
2575
2576 -- Bug 1929163: shipment number is 1 for non-split lines
2577 IF g_line_rec.split_from_line_id IS NULL
2578 OR g_line_rec.split_from_line_id = FND_API.G_MISS_NUM THEN
2579
2580 l_ship_number := 1;
2581
2582 ELSE
2583
2584 SELECT NVL(MAX(SHIPMENT_NUMBER)+1,1)
2585 INTO l_ship_number
2586 FROM OE_ORDER_LINES
2587 WHERE HEADER_ID = g_line_rec.header_id
2588 AND LINE_NUMBER = g_line_rec.line_number;
2589
2590 END IF;
2591
2592 RETURN l_ship_number;
2593
2594 ELSE
2595
2596 OE_Order_Cache.Load_Top_Model_Line(g_line_rec.top_model_line_id );
2597 l_ship_number := OE_ORDER_CACHE.g_top_model_line_rec.shipment_number;
2598 RETURN l_ship_number;
2599
2600 END IF;
2601
2602 EXCEPTION
2603
2604 WHEN OTHERS THEN
2605
2606 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2607 THEN
2608 OE_MSG_PUB.Add_Exc_Msg
2609 ( G_PKG_NAME ,
2610 'Get_Shipment_Number'
2611 );
2612 END IF;
2613
2614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2615
2616 END Get_Shipment_Number;
2617
2618 FUNCTION Get_Shipping_Interfaced
2619 RETURN VARCHAR2
2620 IS
2621 --
2622 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2623 --
2624 BEGIN
2625
2626 RETURN 'N';
2627
2628 END Get_Shipping_Interfaced;
2629
2630 FUNCTION Get_Source_Type(p_source_type IN VARCHAR2,
2631 p_line_type_id IN NUMBER)
2632 RETURN VARCHAR2
2633 IS
2634 l_source_type VARCHAR2(30) := OE_GLOBALS.G_SOURCE_INTERNAL;
2635 --
2636 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2637 --
2638 BEGIN
2639
2640 IF l_debug_level > 0 THEN
2641 oe_debug_pub.add( 'DEFAULTING SOURCE TYPE' ) ;
2642 END IF;
2643
2644 IF p_line_type_id is not null AND
2645 p_line_type_id <> FND_API.G_MISS_NUM THEN
2646
2647 BEGIN
2648
2649 OE_ORDER_CACHE.Load_Line_Type(p_line_type_id);
2650 l_source_type := OE_ORDER_CACHE.g_line_type_rec.ship_source_type_code;
2651
2652 IF l_source_type is null THEN
2653 RETURN p_source_type;
2654 END IF;
2655
2656 IF l_source_type <> OE_GLOBALS.G_SOURCE_EXTERNAL AND
2657 l_source_type <> OE_GLOBALS.G_SOURCE_INTERNAL THEN
2658 l_source_type := p_source_type;
2659 END IF;
2660
2661 EXCEPTION
2662 WHEN OTHERS THEN
2663 l_source_type := p_source_type;
2664 END;
2665 END IF;
2666
2667
2668 IF l_debug_level > 0 THEN
2669 oe_debug_pub.add( 'DEFAULTING SOURCE TYPE AS || L_SOURCE_TYPE' ) ;
2670 END IF;
2671
2672 IF l_source_type <> p_source_type THEN
2673 RETURN l_source_type;
2674 ELSE
2675 RETURN p_source_type;
2676 END IF;
2677
2678 END Get_Source_Type;
2679
2680
2681 FUNCTION Get_Shippable
2682 ( p_line_id IN NUMBER
2683 ,p_inventory_item_id IN NUMBER
2684 ,p_ship_from_org_id IN NUMBER
2685 ,p_ato_line_id IN NUMBER
2686 ,p_item_type_code IN VARCHAR2)
2687 RETURN VARCHAR2
2688 IS
2689 --
2690 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2691 --
2692 BEGIN
2693
2694 IF ( p_inventory_item_id is NULL OR
2695 p_inventory_item_id = FND_API.G_MISS_NUM )
2696 THEN
2697 RETURN NULL;
2698 END IF;
2699
2700 IF (p_item_type_code = OE_GLOBALS.G_ITEM_CONFIG) THEN
2701 RETURN 'Y';
2702 END IF;
2703
2704 IF l_debug_level > 0 THEN
2705 oe_debug_pub.add( '1 , ATO_LINE_ID: '|| P_ATO_LINE_ID , 1 ) ;
2706 END IF;
2707
2708
2709 IF (p_ato_line_id is not null) AND
2710 (p_ato_line_id <> FND_API.G_MISS_NUM)
2711 THEN
2712
2713 -- ##1820608, ato_item can be under a pto model.
2714 IF NOT OE_GLOBALS.Equal(p_item_type_code,
2715 OE_GLOBALS.G_ITEM_STANDARD) AND
2716 NOT (p_item_type_code = OE_GLOBALS.G_ITEM_OPTION AND
2717 p_line_id = p_ato_line_id )AND
2718 NOT (p_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND -- 9775352
2719 p_line_id = p_ato_line_id )
2720 THEN
2721
2722 IF l_debug_level > 0 THEN
2723 oe_debug_pub.add( 'ATO MODEL OR OPTION' , 1 ) ;
2724 END IF;
2725 RETURN 'N';
2726
2727 END IF;
2728
2729 END IF;
2730
2731
2732 OE_Order_Cache.Load_Item (p_inventory_item_id
2733 ,p_ship_from_org_id);
2734
2735 IF l_debug_level > 0 THEN
2736 oe_debug_pub.add( 'SHIPPABLE FLAG: ' || OE_ORDER_CACHE.G_ITEM_REC.SHIPPABLE_ITEM_FLAG , 1 ) ;
2737 END IF;
2738
2739 RETURN OE_ORDER_CACHE.g_item_rec.shippable_item_flag;
2740
2741 END Get_Shippable;
2742
2743
2744
2745 FUNCTION Get_SMC_Flag
2746 RETURN VARCHAR2
2747 IS
2748 --
2749 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2750 --
2751 BEGIN
2752
2753 IF l_debug_level > 0 THEN
2754 oe_debug_pub.add( 'DEFAULTING SMC FLAG ' , 1 ) ;
2755 END IF;
2756
2757 IF ( g_line_rec.inventory_item_id is NULL OR
2758 g_line_rec.inventory_item_id = FND_API.G_MISS_NUM )
2759 THEN
2760 RETURN NULL;
2761 IF l_debug_level > 0 THEN
2762 oe_debug_pub.add( 'SMC1 FLAG VALUE IS NULL' , 1 ) ;
2763 END IF;
2764 END IF;
2765
2766 IF l_debug_level > 0 THEN
2767 oe_debug_pub.add( 'GET SMC: TOP MODEL LINE ID :' || G_LINE_REC.TOP_MODEL_LINE_ID , 1 ) ;
2768 END IF;
2769
2770 -- SMC PTO flag is only for PTO's and Kits
2771
2772 IF (g_line_rec.top_model_line_id = g_line_rec.line_id) AND
2773 (g_line_rec.top_model_line_id <> nvl(g_line_rec.ato_line_id,0)) THEN
2774 OE_Order_Cache.Load_Item (g_line_rec.inventory_item_id
2775 ,g_line_rec.ship_from_org_id);
2776
2777 IF l_debug_level > 0 THEN
2778 oe_debug_pub.add( 'SMC FLAG ' || OE_ORDER_CACHE.G_ITEM_REC.SHIP_MODEL_COMPLETE_FLAG , 3 ) ;
2779 END IF;
2780 RETURN OE_ORDER_CACHE.g_item_rec.ship_model_complete_flag;
2781
2782 ELSIF
2783 g_line_rec.ship_model_complete_flag is not null AND
2784 g_line_rec.ship_model_complete_flag <> FND_API.G_MISS_CHAR AND
2785 g_line_rec.top_model_line_id is not NULL
2786 THEN
2787 IF l_debug_level > 0 THEN
2788 oe_debug_pub.add( 'SMC4 FLAG ' || G_LINE_REC.SHIP_MODEL_COMPLETE_FLAG , 3 ) ;
2789 END IF;
2790 return g_line_rec.ship_model_complete_flag;
2791 ELSE
2792 RETURN null;
2793 IF l_debug_level > 0 THEN
2794 oe_debug_pub.add( 'SMC5 FLAG VALUE IS NULL' , 3 ) ;
2795 END IF;
2796 END IF;
2797
2798 END Get_SMC_Flag;
2799
2800
2801
2802 FUNCTION Get_Defaulting_Invoice_Line
2803 (p_return_context IN VARCHAR2,
2804 p_return_attribute1 IN VARCHAR2,
2805 p_return_attribute2 IN VARCHAR2
2806 ) RETURN NUMBER
2807 IS
2808 l_invoice_line_id NUMBER := NULL;
2809 l_order_line_id NUMBER := NULL;
2810 --
2811 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2812 --
2813 BEGIN
2814
2815 IF (p_return_context = 'INVOICE') THEN
2816
2817 RETURN to_number(p_return_attribute2);
2818
2819 ELSIF p_return_context in ('SERIAL') THEN
2820
2821 IF l_debug_level > 0 THEN
2822 oe_debug_pub.add( 'ATTR1 ' || P_RETURN_ATTRIBUTE1 , 1 ) ;
2823 oe_debug_pub.add( 'ATTR2 ' || P_RETURN_ATTRIBUTE2 , 1 ) ;
2824 END IF;
2825 BEGIN
2826
2827 SELECT l.line_id
2828 INTO l_order_line_id
2829 FROM oe_order_lines l,
2830 mtl_unit_transactions_all_v u,
2831 mtl_material_transactions m
2832 WHERE l.Inventory_item_id = to_number(p_return_attribute1)
2833 AND m.transaction_source_type_id=2
2834 AND m.trx_source_line_id=l.line_id
2835 AND m.transaction_id = u.transaction_id
2836 AND u.serial_number = p_return_attribute2
2837 AND u.inventory_item_id = to_number(p_return_attribute1)
2838 AND rownum = 1;
2839
2840 IF l_debug_level > 0 THEN
2841 oe_debug_pub.add( 'LINE ' || TO_CHAR ( L_ORDER_LINE_ID ) , 1 ) ;
2842 END IF;
2843
2844 IF l_order_line_id IS NOT NULL THEN
2845 SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
2846 INTO l_invoice_line_id
2847 FROM ra_customer_trx_lines_all rctl,
2848 ra_customer_trx rct,
2849 ar_lookups arlup
2850 WHERE rct.status_trx = arlup.lookup_code
2851 AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
2852 AND rct.customer_trx_id = rctl.customer_trx_id
2853 AND rctl.interface_line_context='ORDER ENTRY'
2854 AND rctl.interface_line_attribute6 = to_char(l_order_line_id)
2855 AND rctl.line_type = 'LINE'
2856 AND rctl.interface_line_attribute11 = '0' --Bug2721441
2857 AND rctl.org_id=rct.org_id
2858 AND rownum = 1;
2859 IF l_debug_level > 0 THEN
2860 oe_debug_pub.add( 'INV LINE ' || TO_CHAR ( L_INVOICE_LINE_ID ) , 1 ) ;
2861 END IF;
2862 END IF;
2863 RETURN l_invoice_line_id;
2864 EXCEPTION
2865 WHEN NO_DATA_FOUND THEN
2866 -- not invoiced yet, return NULL
2867 IF l_debug_level > 0 THEN
2868 oe_debug_pub.add( 'IN NO DATA ' , 1 ) ;
2869 END IF;
2870 RETURN NULL;
2871 END;
2872
2873 ELSIF p_return_context in ('PO','ORDER') THEN
2874
2875 BEGIN
2876 SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
2877 INTO l_invoice_line_id
2878 FROM ra_customer_trx_lines_all rctl,
2879 ra_customer_trx rct,
2880 ar_lookups arlup
2881 WHERE rct.status_trx = arlup.lookup_code
2882 AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
2883 AND rct.customer_trx_id = rctl.customer_trx_id
2884 AND rctl.interface_line_context='ORDER ENTRY'
2885 AND rctl.interface_line_attribute6 = p_return_attribute2
2886 AND rctl.line_type = 'LINE'
2887 AND rctl.interface_line_attribute11 = '0' --Bug2721441
2888 AND rctl.org_id=rct.org_id
2889 AND rownum = 1;
2890 RETURN l_invoice_line_id;
2891 EXCEPTION
2892 WHEN NO_DATA_FOUND THEN
2893 -- not invoiced yet, return NULL
2894 RETURN NULL;
2895 END;
2896
2897 END IF;
2898 RETURN NULL;
2899 END Get_Defaulting_Invoice_Line;
2900
2901 FUNCTION Get_Def_Invoice_Line_Int
2902 (p_return_context IN VARCHAR2,
2903 p_return_attribute1 IN VARCHAR2,
2904 p_return_attribute2 IN VARCHAR2,
2905 p_sold_to_org_id IN NUMBER,
2906 p_curr_code IN VARCHAR2,
2907 p_ref_line_id OUT NOCOPY NUMBER
2908
2909 ) RETURN NUMBER
2910 IS
2911 l_invoice_line_id NUMBER := NULL;
2912 l_order_line_id NUMBER := NULL;
2913 l_order_number NUMBER;
2914 l_trxn_type_name VARCHAR2(30);
2915 l_lot_control_flag VARCHAR2(1);
2916 l_inventory_item_id NUMBER := TO_NUMBER(p_return_attribute1);
2917
2918 CURSOR C_REF_LINE(attr1 VARCHAR2, attr2 VARCHAR2) IS
2919 SELECT /* MOAC_SQL_CHANGE */ DISTINCT l.line_id line_id
2920 FROM oe_order_lines_all l,
2921 mtl_unit_transactions_all_v u,
2922 mtl_material_transactions m,
2923 oe_order_headers h
2924 WHERE l.Inventory_item_id = to_number(attr1)
2925 AND m.transaction_source_type_id=2
2926 AND m.trx_source_line_id=l.line_id
2927 AND m.transaction_id = u.transaction_id
2928 AND m.transaction_type_id IN (33,34,50,62)
2929 AND u.serial_number = attr2
2930 AND u.inventory_item_id = to_number(attr1)
2931 AND l.ship_from_org_id = m.organization_id
2932 AND l.inventory_item_id = m.inventory_item_id
2933 AND l.header_id = h.header_id
2934 AND h.sold_to_org_id = p_sold_to_org_id
2935 -- 6916542 AND h.transactional_curr_code = p_curr_code
2936 AND l.cancelled_flag <> 'Y'
2937 order by l.line_id;
2938
2939 CURSOR C_LOT_REF_LINE(attr1 VARCHAR2, attr2 VARCHAR2) IS
2940 SELECT /* MOAC_SQL_CHANGE */ DISTINCT l.line_id line_id
2941 FROM mtl_material_transactions m,
2942 mtl_transaction_lot_val_v t,
2943 mtl_unit_transactions_all_v u,
2944 oe_order_lines_all l,
2945 oe_order_headers h
2946 WHERE u.Inventory_item_id = to_number(attr1)
2947 AND u.ORGANIZATION_ID = t.ORGANIZATION_ID
2948 AND u.serial_number = attr2
2949 AND t.serial_transaction_id = u.transaction_id
2950 AND m.transaction_id = t.transaction_id
2951 AND t.ORGANIZATION_ID = u.ORGANIZATION_ID
2952 AND t.inventory_item_id = u.inventory_item_id
2953 AND m.INVENTORY_ITEM_ID = l.inventory_item_id
2954 AND m.ORGANIZATION_ID = l.ship_from_org_id
2955 AND m.trx_source_line_id=l.line_id
2956 AND m.transaction_source_type_id = 2
2957 AND m.transaction_type_id IN (33,34,50,62)
2958 AND l.cancelled_flag <> 'Y'
2959 AND l.header_id = h.header_id
2960 AND h.sold_to_org_id = p_sold_to_org_id
2961 -- 6916542 AND h.transactional_curr_code = p_curr_code
2962 order by l.line_id;
2963
2964 -- bug#5452691:
2965 -- Adding cancelled_flag condition to filter out all the cancelled lines.
2966 -- otherwise no-data-found error will be thrown when the cursor is iterated
2967 -- and removed it from query when cursor is opened.
2968
2969 CURSOR C_LOT_SERIAL(p_serial_num VARCHAR2) IS
2970 SELECT ls.line_id,ls.line_set_id,ls.from_serial_number,ls.to_serial_number
2971 FROM oe_lot_serial_numbers ls, oe_order_lines ol
2972 WHERE ls.line_id = ol.line_id
2973 AND nvl(ol.cancelled_flag,'N') <> 'Y'
2974 AND (ls.from_serial_number = p_serial_num OR ls.to_serial_number = p_serial_num );
2975 /*
2976 CURSOR C_LOT_SERIAL(p_serial_num VARCHAR2) IS
2977 SELECT line_id,line_set_id,from_serial_number,to_serial_number
2978 FROM oe_lot_serial_numbers
2979 WHERE from_serial_number = p_serial_num
2980 OR to_serial_number = p_serial_num;
2981 */
2982 -- bug#5452691
2983
2984 CURSOR C_ORDER_INFO(ord_line_id NUMBER) IS
2985 SELECT /* MOAC_SQL_CHANGE */ ooh.order_number, ott.name
2986 FROM oe_order_lines_all ool,
2987 oe_order_headers ooh,
2988 oe_transaction_types_tl ott
2989 WHERE ool.line_id = ord_line_id
2990 AND ooh.header_id = ool.header_id
2991 AND ott.transaction_type_id = ooh.order_type_id
2992 -- 6916542 AND ooh.transactional_curr_code = p_curr_code
2993 AND ooh.sold_to_org_id = p_sold_to_org_id
2994 AND ott.language =
2995 (select language_code
2996 from fnd_languages
2997 where installed_flag = 'B');
2998
2999 -- With the addition of logic to support lot-serial controlled item
3000 -- we are adding this new code to figure out the line_id for the
3001 -- specified item and serial number combination.
3002
3003 CURSOR control_codes IS
3004 SELECT decode(msi.lot_control_code,2,'Y','N')
3005 FROM mtl_system_items msi
3006 WHERE msi.inventory_item_id = l_inventory_item_id
3007 AND msi.organization_id =
3008 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID');
3009
3010 TYPE line_tbl_type IS TABLE OF NUMBER
3011 INDEX BY BINARY_INTEGER;
3012 l_line_tbl line_tbl_type;
3013 l_ref_line_tbl line_tbl_type;
3014 l_index1 NUMBER;
3015 l_index2 NUMBER;
3016 l_match VARCHAR2(1);
3017
3018 --
3019 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3020 --
3021 BEGIN
3022 IF l_debug_level > 0 THEN
3023 oe_debug_pub.add( 'ATTR1 ' || P_RETURN_ATTRIBUTE1 , 1 ) ;
3024 oe_debug_pub.add( 'ATTR2 ' || P_RETURN_ATTRIBUTE2 , 1 ) ;
3025 END IF;
3026
3027 IF (p_return_context = 'INVOICE') THEN
3028 BEGIN
3029 /* Modified the following query to put the ivoice currency check for the bug 6916542 */
3030
3031 SELECT /* MOAC_SQL_CHANGE */ to_number(rctl.interface_line_attribute6)
3032 INTO p_ref_line_id
3033 FROM ra_customer_trx_lines_all rctl,
3034 ra_customer_trx_all rct,
3035 oe_order_lines_all l,
3036 oe_order_headers h
3037 WHERE to_number(p_return_attribute2) = rctl.customer_trx_line_id
3038 and rctl.customer_trx_id = rct.customer_trx_id
3039 AND l.line_id = rctl.interface_line_attribute6
3040 AND l.header_id = h.header_id
3041 AND p_curr_code = rct.invoice_currency_code
3042 AND h.sold_to_org_id = p_sold_to_org_id
3043 -- 6916542 AND h.transactional_curr_code = p_curr_code
3044 and rctl.org_id=h.org_id;
3045
3046 EXCEPTION
3047
3048 WHEN OTHERS THEN
3049 -- this should not be possible
3050 IF l_debug_level > 0 THEN
3051 oe_debug_pub.add( 'ERROR WHILE GETTING THE REFERENCE LINE FOR THE INVOICE LINE' , 2 ) ;
3052 END IF;
3053 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3054 THEN
3055 OE_MSG_PUB.Add_Exc_Msg
3056 ( G_PKG_NAME ,
3057 'Get_Def_Invoice_Line_Int'
3058 );
3059 END IF;
3060
3061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3062
3063 END;
3064
3065 RETURN to_number(p_return_attribute2);
3066
3067 ELSIF p_return_context in ('SERIAL') THEN
3068
3069 IF l_debug_level > 0 THEN
3070 oe_debug_pub.add( 'IN SERIAL ' ) ;
3071 END IF;
3072 -- Check to find whether the item is LOT-SERIAL controlled
3073
3074 OPEN control_codes;
3075 FETCH control_codes INTO l_lot_control_flag;
3076 IF control_codes%notfound THEN
3077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3078 END IF;
3079 CLOSE control_codes;
3080
3081 BEGIN
3082 IF l_lot_control_flag = 'Y' THEN
3083 IF l_debug_level > 0 THEN
3084 oe_debug_pub.add( 'IN LOT-SERIAL ' ) ;
3085 END IF;
3086
3087 -- If the item is LOT-SERIAL Controlled
3088
3089 l_index2:=1;
3090 FOR C_LINE IN C_LOT_REF_LINE(p_return_attribute1,
3091 p_return_attribute2)
3092 LOOP
3093 l_line_tbl(l_index2) := C_LINE.line_id;
3094 l_index2:= l_index2+1;
3095 IF l_debug_level > 0 THEN
3096 oe_debug_pub.add( 'IN LOT-SERIAL12 ' ) ;
3097 END IF;
3098
3099 -- Check to see if any referenced return exists for the line.
3100 l_index1 := 0;
3101 SELECT count(*)
3102 INTO l_index1
3103 FROM OE_ORDER_LINES
3104 WHERE reference_line_id = C_LINE.line_id
3105 AND line_category_code = 'RETURN'
3106 AND cancelled_flag <> 'Y';
3107
3108 IF l_debug_level > 0 THEN
3109 oe_debug_pub.add( 'IN LOT-SERIAL2 '||TO_CHAR ( L_INDEX1 ) ) ;
3110 END IF;
3111 -- If there is no referenced return for this line then return
3112 -- this line as a referenced_line_id.
3113
3114 IF l_index1 = 0 THEN
3115 IF l_debug_level > 0 THEN
3116 oe_debug_pub.add( 'IN SERIAL3 '||TO_CHAR ( L_INDEX1 ) ) ;
3117 END IF;
3118 l_order_line_id := C_LINE.line_id;
3119 GOTO GET_INVOICE_LINE;
3120 END IF;
3121 END LOOP;
3122
3123 ELSE
3124 -- If the item is LOT Controlled
3125 l_index2:=1;
3126 FOR C_LINE IN C_REF_LINE(p_return_attribute1, p_return_attribute2)LOOP
3127 IF l_debug_level > 0 THEN
3128 oe_debug_pub.add( 'IN SERIAL1 ' ) ;
3129 END IF;
3130 l_line_tbl(l_index2) := C_LINE.line_id;
3131 l_index2:= l_index2+1;
3132 IF l_debug_level > 0 THEN
3133 oe_debug_pub.add( 'IN SERIAL12 ' ) ;
3134 END IF;
3135
3136 -- Check to see if any referenced return exists for the line.
3137 l_index1 := 0;
3138 SELECT count(*)
3139 INTO l_index1
3140 FROM OE_ORDER_LINES
3141 WHERE reference_line_id = C_LINE.line_id
3142 AND line_category_code = 'RETURN'
3143 AND cancelled_flag <> 'Y';
3144
3145 IF l_debug_level > 0 THEN
3146 oe_debug_pub.add( 'IN SERIAL2 '||TO_CHAR ( L_INDEX1 ) ) ;
3147 END IF;
3148 -- If there is no referenced return for this line then return
3149 -- this line as a referenced_line_id.
3150
3151 IF l_index1 = 0 THEN
3152 IF l_debug_level > 0 THEN
3153 oe_debug_pub.add( 'IN SERIAL3 '||TO_CHAR ( L_INDEX1 ) ) ;
3154 END IF;
3155 l_order_line_id := C_LINE.line_id;
3156 GOTO GET_INVOICE_LINE;
3157 END IF;
3158 END LOOP;
3159 END IF; -- IF item is LOT-SERIAL controlled.
3160
3161 -- If there are no outbound lines which refers the entered Serial
3162 -- Number then raise error with message.
3163
3164 IF l_debug_level > 0 THEN
3165 oe_debug_pub.add( 'IN SERIAL4 '||TO_CHAR ( L_LINE_TBL.COUNT ) ) ;
3166 END IF;
3167 IF l_line_tbl.count = 0 THEN
3168 FND_Message.Set_Name('ONT', 'OE_NO_LINES_FOR_SERIAL_NUMBER');
3169 oe_msg_pub.add;
3170 RAISE FND_API.G_EXC_ERROR;
3171 END IF;
3172
3173 -- For all outbound lines which are referring to this serial number,
3174 -- one or more referenced RMA exists.
3175
3176 l_index2 := 1;
3177
3178 -- Check the OE_LOT_SERIAL_NUMBERS table for the entered Serial Number.
3179
3180 IF l_debug_level > 0 THEN
3181 oe_debug_pub.add( 'IN SERIAL5 ' ) ;
3182 END IF;
3183 FOR C2 IN C_LOT_SERIAL(p_return_attribute2) LOOP
3184
3185 -- If record exists in oe_lot_serial_numbers for the entered SN,
3186 -- check the line_set_id on it. There will be a value for line_set_id
3187 -- if the RMA line has got split. Get the reference line_id from the
3188 -- following queries.
3189
3190 IF C2.line_set_id is not null THEN
3191 select distinct reference_line_id
3192 into l_ref_line_tbl(l_index2)
3193 from oe_line_sets a,
3194 oe_order_lines b
3195 where a.set_id = C2.line_set_id
3196 and a.line_id = b.line_id
3197 and b.cancelled_flag <> 'Y';
3198 ELSE
3199 select reference_line_id
3200 into l_ref_line_tbl(l_index2)
3201 from oe_order_lines
3202 where line_id = C2.line_id;
3203 -- bug#5452691
3204 -- and cancelled_flag <> 'Y';
3205 END IF;
3206 IF l_debug_level > 0 THEN
3207 oe_debug_pub.add( 'IN SERIAL6 THE REF LINE IS '|| TO_CHAR ( L_REF_LINE_TBL ( L_INDEX2 ) ) ) ;
3208 END IF;
3209 l_index2 := l_index2+1;
3210 END LOOP;
3211
3212 -- There can not be more than one outbound line referring the entered
3213 -- SN and no RMA referring it.
3214
3215 IF l_debug_level > 0 THEN
3216 oe_debug_pub.add( 'IN SERIAL7 ' ) ;
3217 END IF;
3218 IF l_ref_line_tbl.COUNT = 0 AND
3219 l_line_tbl.count > 1 THEN
3220 IF l_debug_level > 0 THEN
3221 oe_debug_pub.add( 'IN SERIAL71 ' ) ;
3222 END IF;
3223 FND_Message.Set_Name('ONT', 'OE_DUPLICATE_LINES_FOR_SAME_SN');
3224 oe_msg_pub.add;
3225 RAISE FND_API.G_EXC_ERROR;
3226 END IF;
3227
3228 IF l_debug_level > 0 THEN
3229 oe_debug_pub.add( 'IN SERIAL8 ' ) ;
3230 END IF;
3231 IF l_ref_line_tbl.COUNT = 0 AND
3232 l_line_tbl.count = 1 THEN
3233 IF l_debug_level > 0 THEN
3234 oe_debug_pub.add( 'IN SERIAL81 ' ) ;
3235 END IF;
3236 l_order_line_id := l_line_tbl(1);
3237 END IF;
3238
3239 -- Check for the outbound line referring the entered SN and
3240 -- which is not returned yet
3241
3242 l_index1 := 0;
3243 l_index2 := 0;
3244
3245 IF l_ref_line_tbl.COUNT > 0 THEN
3246 l_index1 := l_line_tbl.FIRST;
3247 WHILE l_index1 IS NOT NULL LOOP
3248 l_match := 'N';
3249 l_index2 := l_ref_line_tbl.FIRST;
3250 WHILE l_index2 IS NOT NULL LOOP
3251 IF l_line_tbl(l_index1) = l_ref_line_tbl(l_index2)
3252 THEN
3253 l_match := 'Y';
3254 IF l_debug_level > 0 THEN
3255 oe_debug_pub.add( 'MATCH FOUND ' ) ;
3256 END IF;
3257 GOTO END_OF_INDEX1_LOOP;
3258 END IF;
3259 l_index2 := l_ref_line_tbl.NEXT(l_index2);
3260 END LOOP;
3261 IF l_match = 'N' THEN
3262 l_order_line_id := l_line_tbl(l_index1);
3263 GOTO GET_INVOICE_LINE;
3264 END IF;
3265 << END_OF_INDEX1_LOOP >>
3266 l_index1 := l_line_tbl.NEXT(l_index1);
3267 END LOOP;
3268 END IF;
3269
3270 << GET_INVOICE_LINE >>
3271 -- oe_debug_pub.add('Line ' || to_char(l_order_line_id),1);
3272
3273 -- If there is no valid outbound line to be referenced for the entered
3274 -- SN then raise error.
3275
3276 IF l_order_line_id IS NULL THEN
3277 IF l_debug_level > 0 THEN
3278 oe_debug_pub.add( 'NO LINES AVAILABLE FOR RETURN' ) ;
3279 END IF;
3280 FND_Message.Set_Name('ONT', 'OE_NO_LINES_FOR_SERIAL_NUMBER');
3281 oe_msg_pub.add;
3282 RAISE FND_API.G_EXC_ERROR;
3283 ELSE
3284 IF l_debug_level > 0 THEN
3285 oe_debug_pub.add('LINE ' || TO_CHAR(L_ORDER_LINE_ID),1);
3286 END IF;
3287 END IF;
3288
3289 FOR c_info_rec1 in C_ORDER_INFO(l_order_line_id) LOOP
3290 l_order_number := c_info_rec1.order_number;
3291 l_trxn_type_name:= c_info_rec1.name;
3292 END LOOP;
3293
3294 p_ref_line_id := l_order_line_id;
3295 IF l_debug_level > 0 THEN
3296 oe_debug_pub.add( 'IN SERIAL9 ' ) ;
3297 END IF;
3298 BEGIN
3299 SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
3300 INTO l_invoice_line_id
3301 FROM ra_customer_trx_lines_all rctl,
3302 ra_customer_trx rct,
3303 ar_lookups arlup
3304 WHERE rct.status_trx = arlup.lookup_code
3305 AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
3306 AND rct.customer_trx_id = rctl.customer_trx_id
3307 AND rctl.interface_line_context = 'ORDER ENTRY'
3308 AND rctl.interface_line_attribute1 = to_char(l_order_number)
3309 AND rctl.interface_line_attribute2 = l_trxn_type_name
3310 AND rctl.interface_line_attribute6 = to_char(l_order_line_id)
3311 AND rctl.line_type = 'LINE'
3312 AND rctl.interface_line_attribute11 = '0' --Bug2721441
3313 AND rctl.org_id=rct.org_id
3314 AND rownum = 1;
3315 IF l_debug_level > 0 THEN
3316 oe_debug_pub.add( 'INVOICE LINE ' || TO_CHAR ( L_INVOICE_LINE_ID ) , 1 ) ;
3317 END IF;
3318
3319 EXCEPTION
3320
3321 WHEN OTHERS THEN
3322 -- not invoiced yet, return NULL
3323 IF l_debug_level > 0 THEN
3324 oe_debug_pub.add( 'NOT INVOICED YET' , 1 ) ;
3325 END IF;
3326 RETURN NULL;
3327
3328 END;
3329 RETURN l_invoice_line_id;
3330 IF l_debug_level > 0 THEN
3331 oe_debug_pub.add( 'IN SERIAL10' ) ;
3332 END IF;
3333
3334 EXCEPTION
3335 WHEN FND_API.G_EXC_ERROR THEN
3336 RAISE FND_API.G_EXC_ERROR;
3337 WHEN OTHERS THEN
3338 IF l_debug_level > 0 THEN
3339 oe_debug_pub.add( 'IN OTHERS EXCEPTION FOR SERIAL' , 2 ) ;
3340 END IF;
3341 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3342 THEN
3343 OE_MSG_PUB.Add_Exc_Msg
3344 ( G_PKG_NAME ,
3345 'OE_Default_Line.Get_Def_Invoice_Line_Int'
3346 );
3347 END IF;
3348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3349
3350 END;
3351
3352 ELSIF p_return_context in ('PO','ORDER') THEN
3353
3354 FOR c_info_rec2 in C_ORDER_INFO(p_return_attribute2) LOOP
3355 l_order_number := c_info_rec2.order_number;
3356 l_trxn_type_name:= c_info_rec2.name;
3357 END LOOP;
3358
3359 IF l_order_number IS NULL THEN
3360 oe_debug_pub.add('Invalid Return Reference',1);
3361 fnd_message.set_name('ONT', 'OE_RETURN_INVALID_SO_LINE');
3362 OE_MSG_PUB.Add;
3363 RAISE FND_API.G_EXC_ERROR;
3364 END IF;
3365
3366 BEGIN
3367
3368 SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
3369 INTO l_invoice_line_id
3370 FROM ra_customer_trx_lines_all rctl,
3371 ra_customer_trx rct,
3372 ar_lookups arlup
3373 WHERE rct.status_trx = arlup.lookup_code
3374 AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
3375 AND rct.customer_trx_id = rctl.customer_trx_id
3376 AND p_return_attribute2 = rctl.interface_line_attribute6
3377 AND rctl.interface_line_context = 'ORDER ENTRY'
3378 AND rctl.interface_line_attribute1 = to_char(l_order_number)
3379 AND rctl.interface_line_attribute2 = l_trxn_type_name
3380 AND rctl.line_type = 'LINE'
3381 AND rctl.interface_line_attribute11 = '0' --Bug2721441
3382 and rctl.org_id=rct.org_id
3383 AND rownum = 1;
3384
3385 p_ref_line_id := p_return_attribute2;
3386 RETURN l_invoice_line_id;
3387
3388 EXCEPTION
3389
3390 WHEN OTHERS THEN
3391 IF l_debug_level > 0 THEN
3392 oe_debug_pub.add( 'IN OTHERS EXCEPTION FOR CONTEXT OF PO/ORDER' , 2 ) ;
3393 END IF;
3394 -- not invoiced yet, return NULL
3395 p_ref_line_id := p_return_attribute2;
3396 RETURN NULL;
3397
3398 END;
3399
3400 END IF;
3401 p_ref_line_id := p_return_attribute2;
3402 RETURN NULL;
3403 END Get_Def_Invoice_Line_Int;
3404
3405
3406 FUNCTION Get_Defaulting_Order_Line
3407 (p_return_context VARCHAR2,
3408 p_return_attribute1 VARCHAR2,
3409 p_return_attribute2 VARCHAR2
3410 ) RETURN NUMBER
3411 IS
3412 l_order_line_id NUMBER := NULL;
3413 --
3414 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3415 --
3416 BEGIN
3417
3418 IF l_debug_level > 0 THEN
3419 oe_debug_pub.add( 'ENTERING GET_DEFAULTING_ORDER_LINE' ) ;
3420 END IF;
3421
3422 IF (p_return_context = 'ORDER') THEN
3423
3424 RETURN to_number(p_return_attribute2);
3425
3426 ELSIF (p_return_context = 'PO') THEN
3427
3428 RETURN to_number(p_return_attribute2);
3429
3430 ELSIF (p_return_context = 'SERIAL') THEN
3431
3432 BEGIN
3433
3434 SELECT l.line_id
3435 INTO l_order_line_id
3436 FROM oe_order_lines l,
3437 mtl_unit_transactions_all_v u,
3438 mtl_material_transactions m
3439 WHERE L.Inventory_item_id = to_number(p_return_attribute1)
3440 AND m.transaction_id = u.transaction_id
3441 AND l.line_category_code = 'ORDER'
3442 AND m.transaction_source_type_id=2
3443 AND m.trx_source_line_id=l.line_id
3444 AND u.serial_number = p_return_attribute2
3445 AND u.inventory_item_id = to_number(p_return_attribute1)
3446 AND rownum = 1;
3447
3448 RETURN l_order_line_id;
3449
3450 EXCEPTION
3451
3452
3453 WHEN NO_DATA_FOUND THEN
3454
3455 -- this should not be possible
3456 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3457 THEN
3458 OE_MSG_PUB.Add_Exc_Msg
3459 ( G_PKG_NAME ,
3460 'Get_Defaulting_Order_Line'
3461 );
3462 END IF;
3463
3464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3465
3466 END;
3467
3468 ELSE
3469
3470 BEGIN
3471
3472 SELECT to_number(rctl.interface_line_attribute6)
3473 INTO l_order_line_id
3474 FROM ra_customer_trx_lines rctl
3475 WHERE to_number(p_return_attribute2) = rctl.customer_trx_line_id;
3476
3477 RETURN l_order_line_id;
3478
3479 EXCEPTION
3480
3481 WHEN NO_DATA_FOUND THEN
3482 -- this should not be possible
3483 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3484 THEN
3485 OE_MSG_PUB.Add_Exc_Msg
3486 ( G_PKG_NAME ,
3487 'Get_Defaulting_Order_Line'
3488 );
3489 END IF;
3490
3491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3492
3493 END;
3494
3495 END IF;-- return context
3496
3497 END Get_Defaulting_Order_Line;
3498
3499 Procedure Attributes_From_Invoice_Line
3500 ( p_invoice_line_id IN NUMBER
3501 , p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
3502 )
3503 IS
3504 l_quantity NUMBER;
3505 l_uom_code VARCHAR2(3);
3506 l_tax_exempt_flag VARCHAR2(1);
3507 l_tax_exempt_reason_code VARCHAR2(30);
3508 l_tax_exempt_number VARCHAR2(80);
3509 --
3510 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3511 --
3512 BEGIN
3513
3514 IF (p_invoice_line_id IS NOT NULL) THEN
3515 -- get attributes from invoice line
3516 BEGIN
3517 SELECT /* MOAC_SQL_CHANGE */ rctl.quantity_invoiced,
3518 rctl.tax_exempt_flag,
3519 rctl.tax_exempt_reason_code,
3520 rctl.tax_exempt_number,
3521 rctl.uom_code
3522 INTO l_quantity,
3523 l_tax_exempt_flag,
3524 l_tax_exempt_reason_code,
3525 l_tax_exempt_number,
3526 l_uom_code
3527 FROM ra_customer_trx_lines_all rctl,
3528 oe_order_lines l
3529 WHERE rctl.customer_trx_line_id = p_invoice_line_id
3530 AND to_number(rctl.interface_line_attribute6) = l.line_id;
3531 EXCEPTION
3532 WHEN NO_DATA_FOUND THEN
3533 -- this should not be possible
3534 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3535 THEN
3536 OE_MSG_PUB.Add_Exc_Msg
3537 ( G_PKG_NAME ,
3538 'Attributes_From_Invoice_Line'
3539 );
3540 END IF;
3541
3542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3543 END;
3544
3545 -- use this attributes as default if not overriden
3546 /* We can not copy the invoiced_quantity to ordered_quantity */
3547 /*
3548 IF (p_x_line_rec.ordered_quantity IS NULL OR
3549 p_x_line_rec.ordered_quantity = FND_API.G_MISS_NUM) THEN
3550 p_x_line_rec.ordered_quantity := l_quantity;
3551 p_x_line_rec.order_quantity_uom := l_uom_code;
3552 ELSE
3553 NULL;
3554 END IF;
3555 */
3556
3557
3558 IF (p_x_line_rec.tax_exempt_flag IS NULL OR
3559 p_x_line_rec.tax_exempt_flag = FND_API.G_MISS_CHAR) THEN
3560 p_x_line_rec.tax_exempt_flag := l_tax_exempt_flag;
3561 END IF;
3562
3563 IF (p_x_line_rec.tax_exempt_reason_code IS NULL OR
3564 p_x_line_rec.tax_exempt_reason_code = FND_API.G_MISS_CHAR) THEN
3565 p_x_line_rec.tax_exempt_reason_code := l_tax_exempt_reason_code;
3566 END IF;
3567
3568 IF (p_x_line_rec.tax_exempt_number IS NULL OR
3569 p_x_line_rec.tax_exempt_number = FND_API.G_MISS_CHAR) THEN
3570 p_x_line_rec.tax_exempt_number := l_tax_exempt_number;
3571 END IF;
3572
3573 IF (p_x_line_rec.reference_customer_trx_line_id IS NULL OR
3574 p_x_line_rec.reference_customer_trx_line_id = FND_API.G_MISS_NUM) THEN
3575 p_x_line_rec.reference_customer_trx_line_id := p_invoice_line_id;
3576 END IF;
3577
3578 IF (p_x_line_rec.credit_invoice_line_id IS NULL OR
3579 p_x_line_rec.credit_invoice_line_id = FND_API.G_MISS_NUM) THEN
3580 p_x_line_rec.credit_invoice_line_id := p_invoice_line_id;
3581 END IF;
3582
3583 END IF; -- exists invoice line
3584
3585 IF l_debug_level > 0 THEN
3586 oe_debug_pub.add( 'EXITING ATTRIBUTES_FROM_INVOICE_LINE' , 1 ) ;
3587 END IF;
3588
3589 END Attributes_From_Invoice_Line;
3590
3591 Procedure Attributes_From_Order_Line
3592 ( p_order_line_id IN NUMBER
3593 , p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
3594 )
3595 IS
3596 l_ref_line_rec OE_ORDER_PUB.Line_Rec_Type;
3597 l_revision_controlled VARCHAR2(1);
3598 x_item_rec OE_Order_Cache.Item_Rec_Type;
3599 --
3600 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3601 --
3602 l_overship_invoice_basis varchar2(30) := null; --- bug# 6617423
3603
3604 BEGIN
3605
3606 IF l_debug_level > 0 THEN
3607 oe_debug_pub.add( 'ENTERING ATTRIBUTES_FROM_ORDER_LINE with p_order_line_id = ' ||p_order_line_id , 1 ) ;
3608 END IF;
3609
3610 IF (p_order_line_id IS NOT NULL AND
3611 p_order_line_id<>FND_API.G_MISS_NUM) THEN
3612
3613 oe_line_util.query_row
3614 (p_line_id => p_order_line_id
3615 ,x_line_rec => l_ref_line_rec
3616 );
3617
3618 /* assign the referenced fields */
3619 IF ((p_x_line_rec.ordered_item IS NULL OR
3620 p_x_line_rec.ordered_item = FND_API.G_MISS_CHAR)
3621 AND (p_x_line_rec.inventory_item_id IS NULL OR
3622 p_x_line_rec.inventory_item_id = FND_API.G_MISS_NUM)
3623 AND (p_x_line_rec.ordered_item_id IS NULL OR
3624 p_x_line_rec.ordered_item_id = FND_API.G_MISS_NUM)) THEN
3625 p_x_line_rec.ordered_item := l_ref_line_rec.ordered_item ;
3626 p_x_line_rec.inventory_item_id := l_ref_line_rec.inventory_item_id;
3627 p_x_line_rec.item_identifier_type := l_ref_line_rec.item_identifier_type;
3628 p_x_line_rec.ordered_item_id := l_ref_line_rec.ordered_item_id;
3629 p_x_line_rec.item_type_code := OE_GLOBALS.G_ITEM_STANDARD;
3630 ELSE
3631 NULL;
3632 END IF;
3633
3634 IF (p_x_line_rec.return_context = 'SERIAL') THEN
3635 p_x_line_rec.ordered_quantity := 1;
3636 p_x_line_rec.order_quantity_uom := l_ref_line_rec.order_quantity_uom;
3637 END IF;
3638
3639 IF (p_x_line_rec.ordered_quantity IS NULL OR
3640 p_x_line_rec.ordered_quantity = fnd_api.g_miss_num) THEN
3641
3642 -- p_x_line_rec.ordered_quantity := l_ref_line_rec.ordered_quantity; -- bug# 6617423
3643 -- bug# 6617423 : Start ---------
3644 oe_debug_pub.add( ' p_x_line_rec.org_id = '||p_x_line_rec.org_id ,5);
3645 IF p_x_line_rec.org_id = FND_API.G_MISS_NUM THEN
3646 l_overship_invoice_basis := oe_sys_parameters.value('OE_OVERSHIP_INVOICE_BASIS',NULL);
3647 ELSE
3648 l_overship_invoice_basis := oe_sys_parameters.value('OE_OVERSHIP_INVOICE_BASIS',p_x_line_rec.org_id);
3649 END IF;
3650 oe_debug_pub.add( ' l_overship_invoice_basis = '|| l_overship_invoice_basis ,5 ) ;
3651 oe_debug_pub.add( ' l_ref_line_rec.invoiced_quantity = '|| l_ref_line_rec.invoiced_quantity ,5 ) ;
3652 oe_debug_pub.add( ' l_ref_line_rec.ordered_quantity = '|| l_ref_line_rec.ordered_quantity,5 ) ;
3653
3654 IF l_overship_invoice_basis = 'SHIPPED' then
3655 p_x_line_rec.ordered_quantity := nvl(l_ref_line_rec.shipped_quantity, l_ref_line_rec.ordered_quantity);
3656 ELSE
3657 p_x_line_rec.ordered_quantity := l_ref_line_rec.ordered_quantity;
3658 end if;
3659 oe_debug_pub.add( ' p_x_line_rec.ordered_quantity = '|| p_x_line_rec.ordered_quantity, 5 ) ;
3660 -- bug# 6617423 : End
3661
3662 p_x_line_rec.order_quantity_uom := l_ref_line_rec.order_quantity_uom;
3663
3664 ELSE
3665 NULL;
3666 END IF;
3667
3668
3669 IF (p_x_line_rec.unit_cost IS NULL OR
3670 p_x_line_rec.unit_cost = fnd_api.g_miss_num) THEN
3671 p_x_line_rec.unit_cost := l_ref_line_rec.unit_cost;
3672 END IF;
3673
3674 IF (p_x_line_rec.order_quantity_uom IS NULL OR
3675 p_x_line_rec.order_quantity_uom = fnd_api.g_miss_char) THEN
3676 p_x_line_rec.order_quantity_uom := l_ref_line_rec.order_quantity_uom;
3677 END IF;
3678
3679 -- 09/07/2001 OPM BEGIN - Default process attributes as appropriate from an order line ref
3680 -- ===============================================================
3681 IF (p_x_line_rec.ordered_quantity_uom2 = FND_API.G_MISS_CHAR)
3682 OR (p_x_line_rec.ordered_quantity_uom2 IS NULL) THEN
3683 p_x_line_rec.ordered_quantity_uom2 :=
3684 l_ref_line_rec.ordered_quantity_uom2 ;
3685 END IF;
3686
3687 IF (p_x_line_rec.preferred_grade = FND_API.G_MISS_CHAR)
3688 OR (p_x_line_rec.preferred_grade IS NULL) THEN
3689 p_x_line_rec.preferred_grade :=
3690 l_ref_line_rec.preferred_grade ;
3691 END IF;
3692
3693 IF (p_x_line_rec.ordered_quantity2 = FND_API.G_MISS_NUM) THEN
3694 --Code added for 13481605
3695 IF (l_overship_invoice_basis = 'SHIPPED') THEN
3696 p_x_line_rec.ordered_quantity2 := nvl(l_ref_line_rec.shipped_quantity2, l_ref_line_rec.ordered_quantity2);
3697 ELSE
3698 p_x_line_rec.ordered_quantity2 := l_ref_line_rec.ordered_quantity2;
3699 END IF;
3700 --Code changes ended here for 13481605
3701 END IF;
3702
3703 -- 09/07/2001 OPM END
3704 -- =====================
3705
3706 IF (p_x_line_rec.reference_line_id IS NULL OR
3707 p_x_line_rec.reference_line_id = FND_API.G_MISS_NUM) THEN
3708 p_x_line_rec.reference_header_id := l_ref_line_rec.header_id;
3709 p_x_line_rec.reference_line_id := l_ref_line_rec.line_id;
3710 END IF;
3711
3712 /* Start : Tax Reference Info */
3713 IF (p_x_line_rec.tax_code IS NULL OR
3714 p_x_line_rec.tax_code = FND_API.G_MISS_CHAR)
3715 THEN
3716 p_x_line_rec.tax_code := l_ref_line_rec.tax_code;
3717 p_x_line_rec.tax_date := l_ref_line_rec.tax_date;
3718 END IF;
3719
3720 IF (p_x_line_rec.tax_exempt_flag IS NULL OR
3721 p_x_line_rec.tax_exempt_flag = FND_API.G_MISS_CHAR)
3722 THEN
3723 p_x_line_rec.tax_exempt_flag := l_ref_line_rec.tax_exempt_flag;
3724 END IF;
3725
3726 IF (p_x_line_rec.tax_exempt_number IS NULL OR
3727 p_x_line_rec.tax_exempt_number = FND_API.G_MISS_CHAR)
3728 AND p_x_line_rec.tax_exempt_flag = 'E' --Added for bug 10380995
3729 THEN
3730 p_x_line_rec.tax_exempt_number := l_ref_line_rec.tax_exempt_number;
3731 END IF;
3732
3733 IF (p_x_line_rec.tax_exempt_reason_code IS NULL OR
3734 p_x_line_rec.tax_exempt_reason_code = FND_API.G_MISS_CHAR)
3735 AND p_x_line_rec.tax_exempt_flag = 'E' --Added for bug 10380995
3736 THEN
3737 p_x_line_rec.tax_exempt_reason_code :=
3738 l_ref_line_rec.tax_exempt_reason_code;
3739 END IF;
3740
3741 /* End: Tax Reference Info */
3742
3743 IF (p_x_line_rec.pricing_quantity IS NULL OR
3744 p_x_line_rec.pricing_quantity = FND_API.G_MISS_NUM) THEN
3745 IF p_x_line_rec.return_context = 'SERIAL' OR
3746 OE_GLOBALS.G_RETURN_CHILDREN_MODE = 'Y' THEN
3747 p_x_line_rec.pricing_quantity_uom :=
3748 l_ref_line_rec.pricing_quantity_uom;
3749 p_x_line_rec.pricing_quantity := OE_Order_Misc_Util.convert_uom(
3750 p_x_line_rec.inventory_item_id,
3751 p_x_line_rec.order_quantity_uom,
3752 p_x_line_rec.pricing_quantity_uom,
3753 p_x_line_rec.ordered_quantity
3754 );
3755 ELSE
3756 p_x_line_rec.pricing_quantity := l_ref_line_rec.pricing_quantity;
3757 p_x_line_rec.pricing_quantity_uom :=
3758 l_ref_line_rec.pricing_quantity_uom;
3759 END IF;
3760 ELSE
3761 NULL;
3762 END IF;
3763
3764 IF (p_x_line_rec.pricing_date IS NULL OR
3765 p_x_line_rec.pricing_date = fnd_api.g_miss_date) THEN
3766 p_x_line_rec.pricing_date := l_ref_line_rec.pricing_date ;
3767 ELSE
3768 NULL;
3769 END IF;
3770
3771 IF (p_x_line_rec.unit_selling_price IS NULL OR
3772 p_x_line_rec.unit_selling_price = FND_API.G_MISS_NUM) THEN
3773 p_x_line_rec.unit_selling_price := l_ref_line_rec.unit_selling_price ;
3774 IF (l_ref_line_rec.pricing_context IS NOT NULL) THEN
3775 p_x_line_rec.pricing_context := l_ref_line_rec.pricing_context;
3776 p_x_line_rec.pricing_attribute1 := l_ref_line_rec.pricing_attribute1;
3777 p_x_line_rec.pricing_attribute2 := l_ref_line_rec.pricing_attribute2;
3778 p_x_line_rec.pricing_attribute3 := l_ref_line_rec.pricing_attribute3;
3779 p_x_line_rec.pricing_attribute4 := l_ref_line_rec.pricing_attribute4;
3780 p_x_line_rec.pricing_attribute5 := l_ref_line_rec.pricing_attribute5;
3781 p_x_line_rec.pricing_attribute6 := l_ref_line_rec.pricing_attribute6;
3782 p_x_line_rec.pricing_attribute7 := l_ref_line_rec.pricing_attribute7;
3783 p_x_line_rec.pricing_attribute8 := l_ref_line_rec.pricing_attribute8;
3784 p_x_line_rec.pricing_attribute9 := l_ref_line_rec.pricing_attribute9;
3785 p_x_line_rec.pricing_attribute10 := l_ref_line_rec.pricing_attribute10;
3786 END IF;
3787 ELSE
3788 NULL;
3789 END IF;
3790
3791 IF (p_x_line_rec.unit_percent_base_price IS NULL OR
3792 p_x_line_rec.unit_percent_base_price = FND_API.G_MISS_NUM) THEN
3793 p_x_line_rec.unit_percent_base_price := l_ref_line_rec.unit_percent_base_price ;
3794 ELSE
3795 NULL;
3796 END IF;
3797
3798 IF (p_x_line_rec.unit_list_price IS NULL OR
3799 p_x_line_rec.unit_list_price = FND_API.G_MISS_NUM) THEN
3800 p_x_line_rec.unit_list_price := l_ref_line_rec.unit_list_price ;
3801 END IF;
3802
3803 --RT{
3804 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
3805 oe_debug_pub.add('Retro:ref_head_id:'||l_ref_line_rec.header_id||' line_id:'||l_ref_line_rec.line_id);
3806 Oe_Retrobill_Pvt.Get_Return_Price(p_header_id=> l_ref_line_rec.header_id,
3807 p_line_id => l_ref_line_rec.line_id,
3808 p_ordered_qty => p_x_line_rec.ordered_quantity, --bug3540728
3809 p_pricing_qty => p_x_line_rec.pricing_quantity, --bug3540728
3810 p_usp => l_ref_line_rec.unit_selling_price,
3811 p_ulp => l_ref_line_rec.unit_list_price,
3812 x_usp => p_x_line_rec.unit_selling_price,
3813 x_ulp => p_x_line_rec.unit_list_price,
3814 x_ulp_ppqty => p_x_line_rec.unit_list_price_per_pqty, --bug3540728
3815 x_usp_ppqty => p_x_line_rec.unit_selling_price_per_pqty); --bug3540728
3816 p_x_line_rec.retrobill_request_id:=NULL;
3817 END IF;
3818 --RT}
3819
3820 -- Start: bug 1769612
3821 IF (p_x_line_rec.unit_list_price_per_pqty IS NULL OR
3822 p_x_line_rec.unit_list_price_per_pqty = FND_API.G_MISS_NUM) THEN
3823 p_x_line_rec.unit_list_price_per_pqty := l_ref_line_rec.unit_list_price_per_pqty ;
3824 END IF;
3825
3826 IF (p_x_line_rec.unit_selling_price_per_pqty IS NULL OR
3827 p_x_line_rec.unit_selling_price_per_pqty = FND_API.G_MISS_NUM) THEN
3828
3829 p_x_line_rec.unit_selling_price_per_pqty := l_ref_line_rec.unit_selling_price_per_pqty;
3830 END IF;
3831 -- end of bug 1769612
3832
3833 IF (p_x_line_rec.price_list_id IS NULL OR
3834 p_x_line_rec.price_list_id = FND_API.G_MISS_NUM) THEN
3835 p_x_line_rec.price_list_id := l_ref_line_rec.price_list_id ;
3836 ELSE
3837 NULL;
3838 END IF;
3839
3840 IF (p_x_line_rec.invoice_to_org_id IS NULL OR
3841 p_x_line_rec.invoice_to_org_id = FND_API.G_MISS_NUM) THEN
3842 p_x_line_rec.invoice_to_org_id := l_ref_line_rec.invoice_to_org_id ;
3843 END IF;
3844
3845 IF (p_x_line_rec.ship_to_contact_id IS NULL OR
3846 p_x_line_rec.ship_to_contact_id = FND_API.G_MISS_NUM) THEN
3847 p_x_line_rec.ship_to_contact_id := l_ref_line_rec.ship_to_contact_id ;
3848 END IF;
3849
3850 IF (p_x_line_rec.intermed_ship_to_org_id IS NULL OR
3851 p_x_line_rec.intermed_ship_to_org_id = FND_API.G_MISS_NUM) THEN
3852 p_x_line_rec.intermed_ship_to_org_id := l_ref_line_rec.intermed_ship_to_org_id ;
3853 END IF;
3854
3855 IF (p_x_line_rec.intermed_ship_to_contact_id IS NULL OR
3856 p_x_line_rec.intermed_ship_to_contact_id = FND_API.G_MISS_NUM) THEN
3857 p_x_line_rec.intermed_ship_to_contact_id
3858 := l_ref_line_rec.intermed_ship_to_contact_id ;
3859 END IF;
3860
3861 IF (p_x_line_rec.deliver_to_contact_id IS NULL OR
3862 p_x_line_rec.deliver_to_contact_id = FND_API.G_MISS_NUM) THEN
3863 p_x_line_rec.deliver_to_contact_id
3864 := l_ref_line_rec.deliver_to_contact_id ;
3865 END IF;
3866
3867 IF (p_x_line_rec.invoice_to_contact_id IS NULL OR
3868 p_x_line_rec.invoice_to_contact_id = FND_API.G_MISS_NUM) THEN
3869 p_x_line_rec.invoice_to_contact_id := l_ref_line_rec.invoice_to_contact_id ;
3870 END IF;
3871
3872 IF (p_x_line_rec.sold_to_org_id IS NULL OR
3873 p_x_line_rec.sold_to_org_id = FND_API.G_MISS_NUM) THEN
3874 p_x_line_rec.sold_to_org_id := l_ref_line_rec.sold_to_org_id ;
3875 ELSE
3876 NULL;
3877 END IF;
3878
3879 IF (p_x_line_rec.ship_from_org_id IS NULL OR
3880 p_x_line_rec.ship_from_org_id = FND_API.G_MISS_NUM) THEN
3881 p_x_line_rec.ship_from_org_id := l_ref_line_rec.ship_from_org_id ;
3882 ELSE
3883 NULL;
3884 END IF;
3885
3886 -- Pack J catchweight
3887 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' AND
3888 --bug3420941
3889 (l_ref_line_rec.source_type_code <> 'EXTERNAL') AND
3890 (p_x_line_rec. ordered_quantity2 IS NULL OR
3891 p_x_line_rec. ordered_quantity2 = FND_API.G_MISS_NUM) AND
3892 (p_x_line_rec. ordered_quantity_uom2 IS NULL OR
3893 --p_x_line_rec. ordered_quantity_uom2 = FND_API.G_MISS_NUM) THEN -- Deleted for Bug# 6521073
3894 p_x_line_rec. ordered_quantity_uom2 = FND_API.G_MISS_CHAR) THEN -- Added for Bug# 6521073
3895
3896 IF (p_x_line_rec.inventory_item_id IS NOT NULL AND
3897 p_x_line_rec.inventory_item_id <> FND_API.G_MISS_NUM) AND
3898 (p_x_line_rec.ship_from_org_id IS NOT NULL AND
3899 p_x_line_rec.ship_from_org_id <> FND_API.G_MISS_NUM) THEN
3900 x_item_rec := OE_Order_Cache.Load_Item (p_x_line_rec.inventory_item_id
3901 ,p_x_line_rec.ship_from_org_id);
3902 -- IF x_item_rec.ont_pricing_qty_source = 1 THEN INVCONV
3903 IF x_item_rec.ont_pricing_qty_source = 'S' THEN -- INVCONV
3904 -- x_item_rec.tracking_quantity_ind = 'P' AND -- INVCONV - TAKE OUT AS OPENED UP TO ANY ITEM AND ORG
3905 -- x_item_rec.wms_enabled_flag = 'Y' THEN
3906 IF l_debug_level > 0 THEN
3907 oe_debug_pub.add( 'Discrete Catchweight enabled. l_ref_line_rec.shipped_quantity2: '|| l_ref_line_rec.shipped_quantity2||
3908 ': l_ref_line_rec.ordered_quantity:'|| l_ref_line_rec.ordered_quantity||': l_ref_line_rec.pricing_quantity_uom:'|| l_ref_line_rec.pricing_quantity_uom);
3909 END IF;
3910 p_x_line_rec.ordered_quantity2 := l_ref_line_rec.shipped_quantity2/ l_ref_line_rec.ordered_quantity * p_x_line_rec.ordered_quantity;
3911 p_x_line_rec.ordered_quantity_uom2 := x_item_rec.secondary_uom_code;
3912 IF l_ref_line_rec.pricing_quantity_uom = l_ref_line_rec.ordered_quantity_uom2 THEN
3913 p_x_line_rec.pricing_quantity := p_x_line_rec.ordered_quantity2;
3914 ELSE
3915 p_x_line_rec.pricing_quantity := OE_Order_Misc_Util.convert_uom(
3916 p_x_line_rec.inventory_item_id,
3917 p_x_line_rec.ordered_quantity_uom2,
3918 p_x_line_rec.pricing_quantity_uom,
3919 p_x_line_rec.ordered_quantity2
3920 );
3921 IF l_debug_level > 0 THEN
3922 oe_debug_pub.add( 'p_x_line_rec.pricing_quantity:'|| p_x_line_rec.pricing_quantity);
3923 END IF;
3924 END IF; -- end check for pricing uom, shipping uom2
3925 END IF; -- check for discrete catchweight
3926 END IF; -- end checks for item org existence
3927 END IF; -- end checks for qty2/uom2 existence
3928 -- Pack J catchweight
3929
3930 IF (p_x_line_rec.ship_to_org_id IS NULL OR
3931 p_x_line_rec.ship_to_org_id = FND_API.G_MISS_NUM) THEN
3932 p_x_line_rec.ship_to_org_id := l_ref_line_rec.ship_to_org_id ;
3933 ELSE
3934 NULL;
3935 END IF;
3936
3937 IF (p_x_line_rec.deliver_to_org_id IS NULL OR
3938 p_x_line_rec.deliver_to_org_id = FND_API.G_MISS_NUM) THEN
3939 p_x_line_rec.deliver_to_org_id := l_ref_line_rec.deliver_to_org_id;
3940 ELSE
3941 NULL;
3942 END IF;
3943
3944
3945 IF (p_x_line_rec.project_id IS NULL OR
3946 p_x_line_rec.project_id = FND_API.G_MISS_NUM) THEN
3947 p_x_line_rec.project_id := l_ref_line_rec.project_id;
3948 END IF;
3949
3950 IF (p_x_line_rec.task_id IS NULL OR
3951 p_x_line_rec.task_id = FND_API.G_MISS_NUM) THEN
3952 p_x_line_rec.task_id := l_ref_line_rec.task_id;
3953 END IF;
3954
3955 IF (p_x_line_rec.end_item_unit_number IS NULL OR
3956 p_x_line_rec.end_item_unit_number = FND_API.G_MISS_CHAR) THEN
3957 p_x_line_rec.end_item_unit_number := l_ref_line_rec.end_item_unit_number;
3958 END IF;
3959
3960 /* Added for the bug fix 1720066 */
3961 IF (p_x_line_rec.shippable_flag IS NULL OR
3962 p_x_line_rec.shippable_flag = FND_API.G_MISS_CHAR)
3963 THEN
3964 p_x_line_rec.shippable_flag := l_ref_line_rec.shippable_flag;
3965 END IF;
3966
3967 -- bug 2509121
3968 IF (p_x_line_rec.user_item_description IS NULL OR
3969 p_x_line_rec.user_item_description = FND_API.G_MISS_CHAR)
3970 THEN
3971 p_x_line_rec.user_item_description := l_ref_line_rec.user_item_description;
3972 END IF;
3973
3974
3975 /* Fix Bug 2429989: Returning Revision Controlled Items */
3976
3977 IF p_x_line_rec.inventory_item_id IS NOT NULL AND
3978 p_x_line_rec.inventory_item_id <> FND_API.G_MISS_NUM THEN
3979
3980 Begin
3981 select decode(revision_qty_control_code, 2, 'Y', 'N')
3982 into l_revision_controlled
3983 from mtl_system_items
3984 where inventory_item_id = p_x_line_rec.inventory_item_id
3985 and organization_id = OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID');
3986 Exception
3987 When NO_DATA_FOUND Then
3988 l_revision_controlled := 'N';
3989 End;
3990
3991 IF l_revision_controlled = 'Y' THEN
3992
3993 IF l_debug_level > 0 THEN
3994 oe_debug_pub.add( 'THE ITEM IS REVISION CONTROLLED' ) ;
3995 END IF;
3996
3997 Begin
3998 select distinct revision
3999 into p_x_line_rec.item_revision
4000 from mtl_material_transactions
4001 where transaction_source_type_id = 2
4002 and transaction_type_id = 33
4003 and trx_source_line_id = p_x_line_rec.reference_line_id
4004 and inventory_item_id = p_x_line_rec.inventory_item_id
4005 and organization_id = (select ship_from_org_id
4006 from oe_order_lines_all
4007 where line_id = p_x_line_rec.reference_line_id);
4008 Exception
4009 When No_Data_Found Then
4010 Null;
4011 When Too_Many_Rows Then
4012 p_x_line_rec.item_revision := NULL;
4013 End;
4014 IF l_debug_level > 0 THEN
4015 oe_debug_pub.add( 'THE ITEM REVISION IS: '|| P_X_LINE_REC.ITEM_REVISION ) ;
4016 END IF;
4017 END IF;
4018
4019 END IF;
4020
4021 END IF;
4022
4023 -- Bug 2849656 => Copy blanket fields from referenced order line
4024 -- to RMA line.
4025
4026 IF (p_x_line_rec.blanket_number IS NULL OR
4027 p_x_line_rec.blanket_number = FND_API.G_MISS_NUM) THEN
4028 p_x_line_rec.blanket_number := l_ref_line_rec.blanket_number;
4029 END IF;
4030
4031 IF (p_x_line_rec.blanket_line_number IS NULL OR
4032 p_x_line_rec.blanket_line_number = FND_API.G_MISS_NUM) THEN
4033 p_x_line_rec.blanket_line_number := l_ref_line_rec.blanket_line_number;
4034 END IF;
4035
4036 -- End fix for Bug 2849656
4037
4038 -- Override List Price
4039 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
4040 IF OE_ORDER_COPY_UTIL.G_LINE_PRICE_MODE = OE_ORDER_COPY_UTIL.G_CPY_REPRICE THEN
4041 p_x_line_rec.original_list_price := NULL;
4042 ELSE
4043 IF (p_x_line_rec.original_list_price IS NULL OR
4044 p_x_line_rec.original_list_price = FND_API.G_MISS_NUM) THEN
4045 p_x_line_rec.original_list_price := l_ref_line_rec.original_list_price;
4046 END IF;
4047 END IF;
4048
4049 END IF;
4050 -- Override List Price
4051
4052 IF l_debug_level > 0 THEN
4053 oe_debug_pub.add( 'EXITING ATTRIBUTES_FROM_ORDER_LINE' , 1 ) ;
4054 END IF;
4055
4056 END Attributes_From_Order_Line;
4057
4058 Procedure Return_Attributes
4059 ( p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
4060 , p_old_line_rec IN OE_Order_PUB.Line_Rec_Type
4061 )
4062 IS
4063 l_defaulting_invoice_line_id NUMBER := NULL;
4064 l_defaulting_order_line_id NUMBER := NULL;
4065 l_line_rec oe_order_pub.line_rec_type;
4066 --
4067 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4068 l_return_status VARCHAR2(1);
4069 l_sold_to_org_id NUMBER;
4070 l_currency_code VARCHAR2(15);
4071
4072 -- l_overship_invoice_basis VARCHAR2(30):= NULL; -- bug#6617423 --bug#9854947: Commenting this variable
4073
4074 BEGIN
4075
4076 IF l_debug_level > 0 THEN
4077 oe_debug_pub.add( 'INSIDE DEFAULTING RMA' , 1 ) ;
4078 oe_debug_pub.add( 'RMA OPERATION IS'||P_X_LINE_REC.OPERATION , 1 ) ;
4079 END IF;
4080
4081 IF NOT OE_GLOBALS.Equal(p_x_line_rec.return_attribute2, p_old_line_rec.return_attribute2)
4082 THEN
4083 IF l_debug_level > 0 THEN
4084 oe_debug_pub.add( 'CLEARING RMA_ATTRIBUTES' , 1 ) ;
4085 oe_debug_pub.add( ' OLD P_OLD_LINE_REC.RETURN_CONTEXT = '||P_OLD_LINE_REC.RETURN_CONTEXT , 1 ) ;
4086 oe_debug_pub.add( ' OLD P_OLD_LINE_REC.RETURN_ATTRIBUTE1 = '||P_OLD_LINE_REC.RETURN_ATTRIBUTE1 , 1 ) ;
4087 oe_debug_pub.add( ' OLD P_OLD_LINE_REC.RETURN_ATTRIBUTE2 = '||P_OLD_LINE_REC.RETURN_ATTRIBUTE2 , 1 ) ;
4088 oe_debug_pub.add( ' OLD P_OLD_LINE_REC.RETURN_ATTRIBUTE3 = '||P_OLD_LINE_REC.RETURN_ATTRIBUTE3 , 1 ) ;
4089
4090 oe_debug_pub.add( ' NEW P_X_LINE_REC.RETURN_CONTEXT = '||P_X_LINE_REC.RETURN_CONTEXT , 1 ) ;
4091 oe_debug_pub.add( ' NEW P_X_LINE_REC.RETURN_ATTRIBUTE1 = '||P_X_LINE_REC.RETURN_ATTRIBUTE1 , 1 ) ;
4092 oe_debug_pub.add( ' NEW P_X_LINE_REC.RETURN_ATTRIBUTE2 = '||P_X_LINE_REC.RETURN_ATTRIBUTE2 , 1 ) ;
4093 oe_debug_pub.add( ' NEW P_X_LINE_REC.RETURN_ATTRIBUTE2 = '||P_X_LINE_REC.RETURN_ATTRIBUTE3 , 1 ) ;
4094
4095 oe_debug_pub.add( ' NEW p_x_line_rec.source_document_id = '|| p_x_line_rec.source_document_id , 1 ) ;
4096 oe_debug_pub.add( ' NEW p_x_line_rec.source_document_line_id = '|| p_x_line_rec.source_document_line_id , 1 ) ;
4097 oe_debug_pub.add( ' NEW p_x_line_rec.orig_sys_document_ref = '|| p_x_line_rec.orig_sys_document_ref , 1 ) ;
4098 oe_debug_pub.add( ' NEW p_x_line_rec.orig_sys_line_ref = '||p_x_line_rec.orig_sys_line_ref , 1 ) ;
4099 END IF;
4100
4101 -- Backup the passed in record.
4102 l_line_rec := p_x_line_rec;
4103
4104 -- Set the line rec to MISSING so that all attributes are redefaulted
4105 p_x_line_rec := OE_Order_PUB.G_MISS_LINE_REC;
4106
4107 -- Reset the pre-defaulted values from backup
4108 p_x_line_rec.line_id := l_line_rec.line_id;
4109 p_x_line_rec.customer_line_number :=l_line_rec.customer_line_number; --added for bug 5569557
4110 p_x_line_rec.ship_from_org_id := l_line_rec.ship_from_org_id; --Added for bug 5649747
4111 p_x_line_rec.line_number := l_line_rec.line_number;
4112 p_x_line_rec.header_id := l_line_rec.header_id;
4113 p_x_line_rec.item_type_code := l_line_rec.item_type_code;
4114 p_x_line_rec.line_type_id := l_line_rec.line_type_id;
4115 p_x_line_rec.line_category_code := l_line_rec.line_category_code;
4116 p_x_line_rec.return_reason_code := l_line_rec.return_reason_code;
4117 p_x_line_rec.org_id := l_line_rec.org_id;
4118 p_x_line_rec.sold_to_org_id := l_line_rec.sold_to_org_id;
4119 p_x_line_rec.CUST_PO_NUMBER := l_line_rec.CUST_PO_NUMBER;
4120 p_x_line_rec.return_context := l_line_rec.return_context;
4121 p_x_line_rec.return_attribute1 := l_line_rec.return_attribute1;
4122 p_x_line_rec.return_attribute2 := l_line_rec.return_attribute2;
4123 p_x_line_rec.shipment_number := l_line_rec.shipment_number;
4124 p_x_line_rec.creation_date := l_line_rec.creation_date;
4125 p_x_line_rec.created_by := l_line_rec.created_by;
4126 p_x_line_rec.operation := l_line_rec.operation;
4127 p_x_line_rec.db_flag := l_line_rec.db_flag;
4128 p_x_line_rec.source_document_type_id :=
4129 l_line_rec.source_document_type_id;
4130 p_x_line_rec.context := l_line_rec.context; --Bug#7380336
4131 /*Bug2816576*/
4132 p_x_line_rec.source_document_id := l_line_rec.source_document_id;
4133 p_x_line_rec.source_document_line_id := l_line_rec.source_document_line_id;
4134 /*Bug2816576*/
4135 p_x_line_rec.orig_sys_document_ref := l_line_rec.orig_sys_document_ref;
4136 p_x_line_rec.orig_sys_line_ref := l_line_rec.orig_sys_line_ref;
4137 p_x_line_rec.orig_sys_shipment_ref := l_line_rec.orig_sys_shipment_ref;
4138 p_x_line_rec.change_sequence := l_line_rec.change_sequence;
4139
4140 /* Fix Bug # 2605825:
4141 ** Need to preserve Booked Flag and Flow Status Code, which might have
4142 ** already been defaulted based on the header level booking status.
4143 */
4144 p_x_line_rec.booked_flag := l_line_rec.booked_flag;
4145 p_x_line_rec.flow_status_code := l_line_rec.flow_status_code;
4146
4147 /* Bug # 2834750 : Need to preserve Fulfillment Set */
4148 p_x_line_rec.fulfillment_set := l_line_rec.fulfillment_set;
4149
4150 /* Need to preserve the sold_from_org_id */
4151 p_x_line_rec.sold_from_org_id := l_line_rec.sold_from_org_id;
4152 /* Need to preserve request_date */
4153 p_x_line_rec.request_date := l_line_rec.request_date;
4154
4155 -- Quoting Changes Start
4156 -- Copy transaction phase from order header to return line
4157 OE_Order_Cache.Load_Order_Header(p_x_line_rec.header_id);
4158 p_x_line_rec.transaction_phase_code :=
4159 OE_Order_Cache.g_header_rec.transaction_phase_code;
4160 -- Quoting Changes End
4161
4162 /* Fix for the bug 1777243
4163 ** Copy the Ordered Quantity from the set value in
4164 ** Insert_Rma_Options_Included and derive the pricing quantity
4165 */
4166
4167
4168 IF l_debug_level > 0 THEN
4169 oe_debug_pub.add( ' l_line_rec.ordered_quantity = '||l_line_rec.ordered_quantity,5 ) ;
4170 oe_debug_pub.add( ' l_line_rec.shipped_quantity = '||l_line_rec.shipped_quantity,5 ) ;
4171 oe_debug_pub.add( ' l_line_rec.invoiced_quantity = '||l_line_rec.invoiced_quantity,5 ) ;
4172 oe_debug_pub.add( ' l_line_rec.order_quantity_uom = '||l_line_rec.order_quantity_uom,5 ) ;
4173 oe_debug_pub.add( ' l_line_rec.pricing_quantity = '||l_line_rec.pricing_quantity,5 ) ;
4174 oe_debug_pub.add( ' l_line_rec.pricing_quantity_uom = '||l_line_rec.pricing_quantity_uom,5 ) ;
4175 oe_debug_pub.add( ' p_x_line_rec.org_id = '||p_x_line_rec.org_id ,5) ;
4176 oe_debug_pub.add( ' p_x_line_rec.ordered_quantity = '|| p_x_line_rec.ordered_quantity,5 ) ;
4177 END IF;
4178
4179
4180 IF OE_GLOBALS.G_RETURN_CHILDREN_MODE = 'Y' OR NOT (OE_GLOBALS.G_UI_FLAG) THEN
4181 p_x_line_rec.ordered_quantity := l_line_rec.ordered_quantity; -- bug# 6617423 -----bug#9854947: Uncommenting the code
4182 ---bug#9854947 : commenting the following ER's code : this is unwanted
4183 /*
4184 -- bug# 6617423 : start
4185 oe_debug_pub.add( ' <in Return_Attributes> p_x_line_rec.org_id = '|| p_x_line_rec.org_id , 5 ) ;
4186 IF p_x_line_rec.org_id = FND_API.G_MISS_NUM THEN
4187 l_overship_invoice_basis := oe_sys_parameters.value('OE_OVERSHIP_INVOICE_BASIS',NULL);
4188 ELSE
4189 l_overship_invoice_basis := oe_sys_parameters.value('OE_OVERSHIP_INVOICE_BASIS',p_x_line_rec.org_id);
4190 END IF;
4191 oe_debug_pub.add( ' <in Return_Attributes> l_overship_invoice_basis = '|| l_overship_invoice_basis ,5) ;
4192
4193 IF l_overship_invoice_basis = 'SHIPPED' then
4194 p_x_line_rec.ordered_quantity := nvl(l_line_rec.shipped_quantity, l_line_rec.ordered_quantity);
4195 ELSE
4196 p_x_line_rec.ordered_quantity := l_line_rec.ordered_quantity;
4197 end if;
4198 oe_debug_pub.add( ' p_x_line_rec.ordered_quantity = '|| p_x_line_rec.ordered_quantity , 5 ) ;
4199 -- bug# 6617423 : End
4200 */
4201 ---bug#9854947 : End
4202
4203 p_x_line_rec.order_quantity_uom := l_line_rec.order_quantity_uom;
4204 p_x_line_rec.pricing_quantity := l_line_rec.pricing_quantity;
4205 p_x_line_rec.pricing_quantity_uom := l_line_rec.pricing_quantity_uom;
4206 IF l_debug_level > 0 THEN
4207 oe_debug_pub.add( 'THE ORDERED QTY IS SET = '||p_x_line_rec.ordered_quantity ) ;
4208 END IF;
4209 END IF;
4210
4211 IF p_x_line_rec.source_document_type_id = 2 THEN
4212 p_x_line_rec.order_source_id := 2;
4213 END IF;
4214
4215 IF p_x_line_rec.source_document_type_id = 2 OR OE_GLOBALS.G_RETURN_CHILDREN_MODE = 'Y' THEN
4216 p_x_line_rec.calculate_price_flag := l_line_rec.calculate_price_flag;
4217 p_x_line_rec.pricing_date := l_line_rec.pricing_date;
4218 IF l_debug_level > 0 THEN
4219 oe_debug_pub.add( 'CALCULATE PRICE FLAG : '||P_X_LINE_REC.CALCULATE_PRICE_FLAG ) ;
4220 oe_debug_pub.add( 'PRICING DATE : '||P_X_LINE_REC.PRICING_DATE ) ;
4221 END IF;
4222 END IF;
4223
4224 -- Fix for the issue 2347377. Retain the flex values if sent in
4225 -- by NON-UI call like (OrderImport) / COPY.
4226
4227 IF NOT (OE_GLOBALS.G_UI_FLAG) THEN
4228 IF l_debug_level > 0 THEN
4229 oe_debug_pub.add('Context is' ||P_X_LINE_REC.context);
4230 oe_debug_pub.add('Attribute 1 is : '||P_X_LINE_REC.attribute1);
4231 END IF;
4232
4233 -- Retain the Line DFF info
4234 p_x_line_rec.context := l_line_rec.context;
4235 p_x_line_rec.attribute1 := l_line_rec.attribute1;
4236 p_x_line_rec.attribute2 := l_line_rec.attribute2;
4237 p_x_line_rec.attribute3 := l_line_rec.attribute3;
4238 p_x_line_rec.attribute4 := l_line_rec.attribute4;
4239 p_x_line_rec.attribute5 := l_line_rec.attribute5;
4240 p_x_line_rec.attribute6 := l_line_rec.attribute6;
4241 p_x_line_rec.attribute7 := l_line_rec.attribute7;
4242 p_x_line_rec.attribute8 := l_line_rec.attribute8;
4243 p_x_line_rec.attribute9 := l_line_rec.attribute9;
4244 p_x_line_rec.attribute10 := l_line_rec.attribute10;
4245 p_x_line_rec.attribute11 := l_line_rec.attribute11;
4246 p_x_line_rec.attribute12 := l_line_rec.attribute12;
4247 p_x_line_rec.attribute13 := l_line_rec.attribute13;
4248 p_x_line_rec.attribute14 := l_line_rec.attribute14;
4249 p_x_line_rec.attribute15 := l_line_rec.attribute15;
4250 p_x_line_rec.attribute16 := l_line_rec.attribute16;
4251 p_x_line_rec.attribute17 := l_line_rec.attribute17;
4252 p_x_line_rec.attribute18 := l_line_rec.attribute18;
4253 p_x_line_rec.attribute19 := l_line_rec.attribute19;
4254 p_x_line_rec.attribute20 := l_line_rec.attribute20;
4255
4256 -- Retain the Global DFF Info
4257 p_x_line_rec.global_attribute_category
4258 := l_line_rec.global_attribute_category;
4259 p_x_line_rec.global_attribute1 := l_line_rec.global_attribute1;
4260 p_x_line_rec.global_attribute2 := l_line_rec.global_attribute2;
4261 p_x_line_rec.global_attribute3 := l_line_rec.global_attribute3;
4262 p_x_line_rec.global_attribute4 := l_line_rec.global_attribute4;
4263 p_x_line_rec.global_attribute5 := l_line_rec.global_attribute5;
4264 p_x_line_rec.global_attribute6 := l_line_rec.global_attribute6;
4265 p_x_line_rec.global_attribute7 := l_line_rec.global_attribute7;
4266 p_x_line_rec.global_attribute8 := l_line_rec.global_attribute8;
4267 p_x_line_rec.global_attribute9 := l_line_rec.global_attribute9;
4268 p_x_line_rec.global_attribute10 := l_line_rec.global_attribute10;
4269 p_x_line_rec.global_attribute11 := l_line_rec.global_attribute11;
4270 p_x_line_rec.global_attribute12 := l_line_rec.global_attribute12;
4271 p_x_line_rec.global_attribute13 := l_line_rec.global_attribute13;
4272 p_x_line_rec.global_attribute14 := l_line_rec.global_attribute14;
4273 p_x_line_rec.global_attribute15 := l_line_rec.global_attribute15;
4274 p_x_line_rec.global_attribute16 := l_line_rec.global_attribute16;
4275 p_x_line_rec.global_attribute17 := l_line_rec.global_attribute17;
4276 p_x_line_rec.global_attribute18 := l_line_rec.global_attribute18;
4277 p_x_line_rec.global_attribute19 := l_line_rec.global_attribute19;
4278 p_x_line_rec.global_attribute20 := l_line_rec.global_attribute20;
4279
4280 -- Retain the Industry DFF Info
4281 p_x_line_rec.industry_context := l_line_rec.industry_context;
4282 p_x_line_rec.industry_attribute1 := l_line_rec.industry_attribute1;
4283 p_x_line_rec.industry_attribute2 := l_line_rec.industry_attribute2;
4284 p_x_line_rec.industry_attribute3 := l_line_rec.industry_attribute3;
4285 p_x_line_rec.industry_attribute4 := l_line_rec.industry_attribute4;
4286 p_x_line_rec.industry_attribute5 := l_line_rec.industry_attribute5;
4287 p_x_line_rec.industry_attribute6 := l_line_rec.industry_attribute6;
4288 p_x_line_rec.industry_attribute7 := l_line_rec.industry_attribute7;
4289 p_x_line_rec.industry_attribute8 := l_line_rec.industry_attribute8;
4290 p_x_line_rec.industry_attribute9 := l_line_rec.industry_attribute9;
4291 p_x_line_rec.industry_attribute10 := l_line_rec.industry_attribute10;
4292 --Begin of Bug Fix 6626305
4293 p_x_line_rec.industry_attribute11 := l_line_rec.industry_attribute11;
4294 p_x_line_rec.industry_attribute12 := l_line_rec.industry_attribute12;
4295 p_x_line_rec.industry_attribute13 := l_line_rec.industry_attribute13;
4296 p_x_line_rec.industry_attribute14 := l_line_rec.industry_attribute14;
4297 p_x_line_rec.industry_attribute15 := l_line_rec.industry_attribute15;
4298 p_x_line_rec.industry_attribute16 := l_line_rec.industry_attribute16;
4299 p_x_line_rec.industry_attribute17 := l_line_rec.industry_attribute17;
4300 p_x_line_rec.industry_attribute18 := l_line_rec.industry_attribute18;
4301 p_x_line_rec.industry_attribute19 := l_line_rec.industry_attribute19;
4302 p_x_line_rec.industry_attribute20 := l_line_rec.industry_attribute20;
4303 p_x_line_rec.industry_attribute21 := l_line_rec.industry_attribute21;
4304 p_x_line_rec.industry_attribute22 := l_line_rec.industry_attribute22;
4305 p_x_line_rec.industry_attribute23 := l_line_rec.industry_attribute23;
4306 p_x_line_rec.industry_attribute24 := l_line_rec.industry_attribute24;
4307 p_x_line_rec.industry_attribute25 := l_line_rec.industry_attribute25;
4308 p_x_line_rec.industry_attribute26 := l_line_rec.industry_attribute26;
4309 p_x_line_rec.industry_attribute27 := l_line_rec.industry_attribute27;
4310 p_x_line_rec.industry_attribute28 := l_line_rec.industry_attribute28;
4311 p_x_line_rec.industry_attribute29 := l_line_rec.industry_attribute29;
4312 p_x_line_rec.industry_attribute30 := l_line_rec.industry_attribute30;
4313
4314
4315 p_x_line_rec.return_attribute3 := l_line_rec.return_attribute3;
4316 p_x_line_rec.return_attribute4 := l_line_rec.return_attribute4;
4317 p_x_line_rec.return_attribute5 := l_line_rec.return_attribute5;
4318 p_x_line_rec.return_attribute6 := l_line_rec.return_attribute6;
4319 p_x_line_rec.return_attribute7 := l_line_rec.return_attribute7;
4320 p_x_line_rec.return_attribute8 := l_line_rec.return_attribute8;
4321 p_x_line_rec.return_attribute9 := l_line_rec.return_attribute9;
4322 p_x_line_rec.return_attribute10 := l_line_rec.return_attribute10;
4323 p_x_line_rec.return_attribute11 := l_line_rec.return_attribute11;
4324 p_x_line_rec.return_attribute12 := l_line_rec.return_attribute12;
4325 p_x_line_rec.return_attribute13 := l_line_rec.return_attribute13;
4326 p_x_line_rec.return_attribute14 := l_line_rec.return_attribute14;
4327 p_x_line_rec.return_attribute15 := l_line_rec.return_attribute15;
4328 --End of Bug Fix 6626305
4329
4330
4331 -- Retain the Trading Partner DFF Info
4332 p_x_line_rec.tp_context := l_line_rec.tp_context;
4333 p_x_line_rec.tp_attribute1 := l_line_rec.tp_attribute1;
4334 p_x_line_rec.tp_attribute2 := l_line_rec.tp_attribute2;
4335 p_x_line_rec.tp_attribute3 := l_line_rec.tp_attribute3;
4336 p_x_line_rec.tp_attribute4 := l_line_rec.tp_attribute4;
4337 p_x_line_rec.tp_attribute5 := l_line_rec.tp_attribute5;
4338 p_x_line_rec.tp_attribute6 := l_line_rec.tp_attribute6;
4339 p_x_line_rec.tp_attribute7 := l_line_rec.tp_attribute7;
4340 p_x_line_rec.tp_attribute8 := l_line_rec.tp_attribute8;
4341 p_x_line_rec.tp_attribute9 := l_line_rec.tp_attribute9;
4342 p_x_line_rec.tp_attribute10 := l_line_rec.tp_attribute10;
4343 p_x_line_rec.tp_attribute11 := l_line_rec.tp_attribute11;
4344 p_x_line_rec.tp_attribute12 := l_line_rec.tp_attribute12;
4345 p_x_line_rec.tp_attribute13 := l_line_rec.tp_attribute13;
4346 p_x_line_rec.tp_attribute14 := l_line_rec.tp_attribute14;
4347 p_x_line_rec.tp_attribute15 := l_line_rec.tp_attribute15;
4348
4349 END IF;
4350
4351 END IF; --IF NOT OE_GLOBALS.Equal(p_x_line_rec.return_attribute2, p_old_line_rec.return_attribute2) THEN
4352
4353
4354 IF p_x_line_rec.return_attribute2 is NOT NULL AND
4355 p_x_line_rec.return_attribute2 <> FND_API.G_MISS_CHAR
4356 THEN
4357
4358 IF l_debug_level > 0 THEN
4359 oe_debug_pub.add( 'IN DEFAULT RETURN ATTRIBUTES' , 1 ) ;
4360 END IF;
4361
4362 -- reprice when pricing attributes change
4363 -- also if the flag is passed in, keep the original flag
4364 IF (p_x_line_rec.calculate_price_flag IS NULL OR
4365 p_x_line_rec.calculate_price_flag = FND_API.G_MISS_CHAR)
4366 THEN
4367 IF l_debug_level > 0 THEN
4368 oe_debug_pub.add( 'SETTING CALCULATE PRICE FLAG' , 1 ) ;
4369 END IF;
4370 p_x_line_rec.calculate_price_flag := 'N';
4371
4372 END IF;
4373
4374 IF l_debug_level > 0 THEN
4375 oe_debug_pub.add( 'IN DEFAULT RETURN ATTRIBUTES -1' , 1 ) ;
4376 END IF;
4377
4378 IF NOT OE_GLOBALS.Equal(p_x_line_rec.return_attribute2, p_old_line_rec.return_attribute2) THEN
4379
4380 -- Get the values of l_currency_code and l_sold_to_org_id
4381 OE_ORDER_CACHE.Load_Order_Header(p_x_line_rec.header_id);
4382 l_sold_to_org_id := OE_ORDER_CACHE.g_header_rec.SOLD_TO_ORG_ID;
4383 l_currency_code := OE_ORDER_CACHE.g_header_rec.transactional_curr_code;
4384
4385 IF l_debug_level > 0 THEN
4386 oe_debug_pub.add( 'IN DEFAULT RETURN ATTRIBUTES 0' , 1 ) ;
4387 oe_debug_pub.add('Sold To Org Id IS '|| l_sold_to_org_id , 1 ) ;
4388 oe_debug_pub.add('Header Currency IS '||l_currency_code , 1 ) ;
4389 END IF;
4390 -- default attributes from invoice line
4391 l_defaulting_invoice_line_id := Get_Def_Invoice_Line_Int
4392 (p_x_line_rec.return_context,
4393 p_x_line_rec.return_attribute1,
4394 p_x_line_rec.return_attribute2,
4395 l_sold_to_org_id,
4396 l_currency_code,
4397 l_defaulting_order_line_id);
4398 IF l_debug_level > 0 THEN
4399 oe_debug_pub.add( 'IN DEFAULT RETURN ATTRIBUTES 1' , 1 ) ;
4400 END IF;
4401
4402 -- Since only one serial number can be entered from the reference
4403 -- set the Ordered_quantity to 1.
4404
4405 /*
4406 ** Fix for Bug # 1686920
4407 ** Commented following as it's being taken care of at a later stage.
4408 IF p_x_line_rec.return_context = 'SERIAL' THEN
4409 p_x_line_rec.ordered_quantity := 1;
4410 END IF;
4411 */
4412
4413 IF l_defaulting_invoice_line_id IS NOT NULL THEN
4414 Attributes_From_Invoice_Line
4415 (p_invoice_line_id => l_defaulting_invoice_line_id,
4416 p_x_line_rec => p_x_line_rec);
4417 END IF;
4418
4419 IF l_debug_level > 0 THEN
4420 oe_debug_pub.add( 'IN DEFAULT RETURN ATTRIBUTES 2' , 1 ) ;
4421 END IF;
4422
4423 Attributes_From_Order_Line
4424 (p_order_line_id => l_defaulting_order_line_id,
4425 p_x_line_rec => p_x_line_rec);
4426
4427 IF l_debug_level > 0 THEN
4428 oe_debug_pub.add( 'IN DEFAULT RETURN ATTRIBUTES 4' , 1 ) ;
4429 END IF;
4430
4431 -- Clear attributes that do not make sense to returns
4432 p_x_line_rec.shipped_quantity := NULL;
4433 p_x_line_rec.reserved_quantity := NULL;
4434 p_x_line_rec.shipping_quantity := NULL;
4435 p_x_line_rec.shipping_quantity_uom := NULL;
4436
4437 -- INVCONV
4438 p_x_line_rec.shipped_quantity2 := NULL;
4439 p_x_line_rec.reserved_quantity2 := NULL;
4440 p_x_line_rec.shipping_quantity2 := NULL;
4441 p_x_line_rec.shipping_quantity_uom2 := NULL;
4442 p_x_line_rec.fulfilled_quantity2 := NULL;
4443
4444 /* Need to copy shippable_flag from the reference line */
4445 --p_x_line_rec.shippable_flag := NULL;
4446 p_x_line_rec.actual_shipment_date := NULL;
4447 -- source type code for RMA lines will always be set to internal. If
4448 -- in future we plan to change the design then please comment out the
4449 -- following code.
4450 p_x_line_rec.source_type_code := OE_GLOBALS.G_SOURCE_INTERNAL;
4451
4452 p_x_line_rec.over_ship_reason_code := NULL;
4453 p_x_line_rec.over_ship_resolved_flag := NULL;
4454 p_x_line_rec.shipping_interfaced_flag := NULL;
4455 p_x_line_rec.top_model_line_id := NULL;
4456 -- Commented following stmt to fix Bug # 1580182.
4457 -- p_x_line_rec.booked_flag := 'N';
4458 p_x_line_rec.fulfilled_quantity := NULL;
4459 p_x_line_rec.option_number := NULL;
4460
4461 -- For bug3327250
4462 -- CAll OE_Validate_Line.Attributes
4463 /* OE_Validate_Line.Attributes(
4464 x_return_status => l_return_status
4465 , p_x_line_rec => p_x_line_rec
4466 , p_validation_level => OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF); */ --bug 14698931
4467 ---Start bug# 14698931
4468 if OE_GLOBALS.g_validate_desc_flex ='Y' then
4469 if l_debug_level>0 then
4470 oe_debug_pub.add(' do not validate DFF , setting OE_GLOBALS.G_validate_desc_flex ');
4471 end if;
4472
4473 OE_GLOBALS.g_validate_desc_flex := 'N' ;
4474 OE_Validate_Line.Attributes(
4475 x_return_status => l_return_status
4476 , p_x_line_rec => p_x_line_rec
4477 , p_validation_level => OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF);
4478 OE_GLOBALS.g_validate_desc_flex := 'Y' ;
4479
4480 else
4481 OE_Validate_Line.Attributes(
4482 x_return_status => l_return_status
4483 , p_x_line_rec => p_x_line_rec
4484 , p_validation_level => OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF);
4485
4486 end if ;
4487 ---End bug# 14698931
4488
4489 IF l_debug_level > 0 THEN
4490 oe_debug_pub.add( 'THE ORDERED QTY IS '||P_X_LINE_REC.ORDERED_QUANTITY ) ;
4491 oe_debug_pub.add( 'THE PRICING QTY IS '||P_X_LINE_REC.PRICING_QUANTITY ) ;
4492 END IF;
4493 END IF; --IF NOT OE_GLOBALS.Equal(p_x_line_rec.return_attribute2,....
4494
4495 END IF; --- IF p_x_line_rec.return_attribute2 is NOT NULL
4496
4497 END Return_Attributes;
4498
4499 -- Procedure Attributes
4500
4501 PROCEDURE Attributes
4502 ( p_x_Line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
4503 , p_old_Line_rec IN OE_Order_PUB.Line_Rec_Type
4504 , p_iteration IN NUMBER := 1
4505 )
4506
4507 IS
4508 l_in_old_rec OE_AK_ORDER_LINES_V%ROWTYPE;
4509 l_in_rec OE_AK_ORDER_LINES_V%ROWTYPE;
4510 l_rec OE_AK_ORDER_LINES_V%ROWTYPE;
4511 g_multiple_shipments VARCHAR2(3);
4512 l_set_tolerance_below VARCHAR2(1) := 'N';
4513 l_set_tolerance_above VARCHAR2(1) := 'N';
4514
4515 l_blanket_number NUMBER := NULL;
4516 l_blanket_version_number NUMBER := NULL;
4517 l_blanket_line_number NUMBER := NULL;
4518 l_blanket_request_date DATE;
4519
4520 l_exists VARCHAR2(1);
4521 l_party_type VARCHAR2(30);
4522 --
4523 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4524 --
4525 BEGIN
4526
4527 IF l_debug_level > 0 THEN
4528 oe_debug_pub.add( 'ENTER OE_DEFAULT_LINE.ATTRIBUTES' , 1 ) ;
4529 END IF;
4530 fnd_profile.get('ONT_IMP_MULTIPLE_SHIPMENTS', g_multiple_shipments);
4531 g_multiple_shipments := nvl(g_multiple_shipments, 'NO');
4532
4533 /* IF (p_x_line_rec.operation = oe_globals.g_opr_create and */
4534 IF p_x_line_rec.return_context is NOT NULL AND
4535 p_x_line_rec.return_context <> FND_API.G_MISS_CHAR THEN
4536 Return_Attributes
4537 ( p_x_line_rec => p_x_line_rec
4538 , p_old_line_rec => p_old_line_rec
4539 );
4540 END IF;
4541 g_line_rec := p_x_line_rec;
4542
4543 -- bug 4668200
4544 IF (g_line_rec.header_id IS NOT NULL AND
4545 g_line_rec.header_id <> FND_API.G_MISS_NUM) THEN
4546 Set_Header_Def_Hdlr_Rec (g_line_rec.header_id);
4547 END IF ;
4548 -- end
4549
4550 IF p_x_line_rec.unit_cost = FND_API.G_MISS_NUM THEN
4551 p_x_line_rec.unit_cost := p_old_line_rec.unit_cost;
4552 END IF;
4553
4554 -- For some fields, get hardcoded defaults
4555
4556 -- IMPORTANT: For defaulting to work correctly, these fields should
4557 -- A) Not be dependent on any other field (Refer OEXUDEPB.pls for the
4558 -- list of dependencies)
4559 -- B) Not be enabled for security constraints as there is no security
4560 -- check for these fields from here.
4561
4562 -- ***************IMPORTANT ********************
4563 -- get item_type is dependent on get_top_model
4564 -- get ato_line , get_shippbale etc are dependent on get_item_type
4565 -- please do not changes their sequence.
4566
4567 IF g_line_rec.operation = oe_globals.g_opr_create THEN
4568 g_line_rec.org_id := Get_Org;
4569
4570 -- QUOTING change
4571 -- Initialize flow status to DRAFT for lines in negotiation phase
4572 IF g_line_rec.transaction_phase_code = 'N' THEN
4573 g_line_rec.flow_status_code := 'DRAFT';
4574 END IF;
4575
4576 END IF;
4577
4578 IF g_line_rec.created_by = FND_API.G_MISS_NUM THEN
4579 g_line_rec.created_by := FND_GLOBAL.USER_ID;
4580 END IF;
4581
4582 --key Transaction Dates
4583 IF g_line_rec.creation_date = FND_API.G_MISS_DATE THEN
4584 g_line_rec.creation_date := sysdate;
4585 END IF;
4586 --end
4587
4588 IF g_line_rec.line_id = FND_API.G_MISS_NUM THEN
4589 g_line_rec.line_id := Get_Line;
4590 END IF;
4591 -- Fix for 2362210
4592 IF l_debug_level > 0 THEN
4593 oe_debug_pub.add( 'LINE ID = '||G_LINE_REC.LINE_ID ) ;
4594 oe_debug_pub.add( 'LINE SYS = '||G_LINE_REC.ORIG_SYS_LINE_REF ) ;
4595 oe_debug_pub.add( 'SOURCE_DOCUMENT_ID = '|| G_LINE_REC.SOURCE_DOCUMENT_ID ) ;
4596 END IF;
4597 IF ((g_line_rec.orig_sys_line_ref = FND_API.G_MISS_CHAR
4598 OR g_line_rec.orig_sys_line_ref IS NULL)
4599 AND
4600 nvl(g_line_rec.source_document_id,-999) <> 10) THEN
4601 g_line_rec.orig_sys_line_ref := 'OE_ORDER_LINES_ALL'||g_line_rec.line_id;
4602 END IF;
4603
4604 --{ bug3664313 FP start: added NULL check
4605 IF (g_line_rec.orig_sys_document_ref = FND_API.G_MISS_CHAR OR
4606 g_line_rec.orig_sys_document_ref IS NULL) THEN
4607 g_line_rec.orig_sys_document_ref := Get_Orig_Sys_Doc_Ref;
4608 END IF;
4609
4610 IF g_line_rec.line_category_code = FND_API.G_MISS_CHAR THEN
4611 g_line_rec.line_category_code :=
4612 Get_line_category(g_line_rec,p_old_line_rec);
4613 IF l_debug_level > 0 THEN
4614 oe_debug_pub.add( 'AFTER CALLING LINE CATEGORY1' || G_LINE_REC.LINE_CATEGORY_CODE ) ;
4615 END IF;
4616
4617 /* Added for the BUG #3257965.
4618 For update operation need to raise an error for line_category_code. */
4619
4620 ELSIF g_line_rec.operation = oe_globals.g_opr_update
4621 AND NOT OE_GLOBALS.EQUAL(g_line_rec.line_category_code
4622 ,p_old_line_rec.line_category_code)
4623 THEN
4624 --3365705For retrobill we change the order type from return to order
4625 -- and the exception shouldn't be raised
4626
4627 IF (
4628 g_line_rec.order_source_id = 27 AND
4629 g_line_rec.retrobill_request_id is NOT NULL ) THEN
4630 null;
4631 ELSE
4632 FND_MESSAGE.SET_NAME('ONT', 'OE_LINE_CAT_CONST');
4633 OE_MSG_PUB.ADD;
4634 IF l_debug_level > 0 THEN
4635 oe_debug_pub.add( 'LINE CATEGORY CONSTRINED' ) ;
4636 END IF;
4637 RAISE FND_API.G_EXC_ERROR;
4638 END IF;
4639 END IF;
4640
4641 -- BUG 3646340: Return_reason is a defaultable field and it needs to be
4642 -- defaulted if the line category changes to RETURN.
4643
4644 IF g_line_rec.operation = oe_globals.g_opr_create AND
4645 g_line_rec.line_category_code = 'RETURN' AND
4646 g_line_rec.return_reason_code IS NULL AND
4647 NOT OE_GLOBALS.Equal(g_line_rec.line_category_code,
4648 p_old_line_rec.line_category_code)
4649 THEN
4650 IF l_debug_level > 0 THEN
4651 oe_debug_pub.add('LINE CATEGORY CHANGED - REASON set to miss') ;
4652 END IF;
4653 g_line_rec.return_reason_code := FND_API.G_MISS_CHAR;
4654 END IF;
4655
4656 IF g_line_rec.top_model_line_id = FND_API.G_MISS_NUM OR
4657 NOT OE_GLOBALS.Equal(g_line_rec.line_category_code,
4658 p_old_line_rec.line_category_code)
4659 THEN
4660 g_line_rec.top_model_line_id := Get_Top_Model_Line;
4661 END IF;
4662 -- 2605065
4663 IF g_line_rec.top_model_line_id IS NOT NULL THEN
4664 OE_Order_Cache.clear_top_model_line(g_line_rec.top_model_line_id);
4665 END IF;
4666
4667 IF l_debug_level > 0 THEN
4668 oe_debug_pub.add( 'BEFORE ITEM_TYPE' ) ;
4669 END IF;
4670 IF g_line_rec.item_type_code = FND_API.G_MISS_CHAR
4671 or NOT OE_GLOBALS.Equal(g_line_rec.line_category_code,
4672 p_old_line_rec.line_category_code)
4673 THEN
4674 g_line_rec.item_type_code :=
4675 Get_Item_Type(g_line_rec, p_old_line_rec);
4676 END IF;
4677
4678 -- smc flag defaulting is dependent on get_item_type.
4679 -- we are not checking for miss_char, because there is
4680 -- no clear_dep for smc_flag. and we don ot want to
4681 -- do that because of the way options defaulting work.
4682
4683 IF NOT OE_GLOBALS.Equal(p_old_line_rec.inventory_item_id,
4684 g_line_rec.inventory_item_id) THEN
4685 IF l_debug_level > 0 THEN
4686 oe_debug_pub.add( 'CALLING GET_SMC' , 3 ) ;
4687 END IF;
4688 g_line_rec.ship_model_complete_flag := Get_SMC_Flag;
4689 IF l_debug_level > 0 THEN
4690 oe_debug_pub.add( 'CALLING GET_ATO' , 3 ) ;
4691 END IF;
4692 g_line_rec.ato_line_id := Get_Ato_Line;
4693 END IF;
4694
4695 IF g_line_rec.ship_model_complete_flag = FND_API.G_MISS_CHAR THEN
4696 g_line_rec.ship_model_complete_flag := Get_SMC_Flag;
4697 END IF;
4698
4699 -- model_option_defaulting is dependent on get_top_model_line
4700 -- get_item_type and get_smc_flag
4701
4702 IF (g_line_rec.item_type_code <> FND_API.G_MISS_CHAR OR
4703 g_line_rec.item_type_code is not null) AND
4704 g_line_rec.operation = OE_GLOBALS.G_OPR_CREATE
4705 THEN
4706 model_option_defaulting;
4707 END IF;
4708
4709 IF (g_line_rec.line_id = FND_API.G_MISS_NUM) OR
4710 (g_line_rec.line_id IS NULL) THEN
4711 g_line_rec.org_id := OE_GLOBALS.G_ORG_ID;
4712 END IF;
4713
4714 IF g_line_rec.booked_flag = FND_API.G_MISS_CHAR THEN
4715 g_line_rec.booked_flag := Get_Booked;
4716 END IF;
4717
4718 IF g_line_rec.model_remnant_flag = FND_API.G_MISS_CHAR THEN
4719 g_line_rec.model_remnant_flag := NULL;
4720 END IF;
4721
4722 IF g_line_rec.cancelled_flag = FND_API.G_MISS_CHAR THEN
4723 g_line_rec.cancelled_flag := Get_Cancelled;
4724 END IF;
4725
4726 IF g_line_rec.cancelled_quantity = FND_API.G_MISS_NUM THEN
4727 g_line_rec.cancelled_quantity := Get_Cancelled_Quantity;
4728 END IF;
4729
4730 IF g_line_rec.component_code = FND_API.G_MISS_CHAR THEN
4731 g_line_rec.component_code := Get_Component;
4732 END IF;
4733
4734 IF g_line_rec.fulfilled_quantity = FND_API.G_MISS_NUM THEN
4735 g_line_rec.fulfilled_quantity := Get_Fulfilled_Quantity;
4736 END IF;
4737
4738 IF g_line_rec.line_number = FND_API.G_MISS_NUM THEN
4739 g_line_rec.line_number := Get_Line_Number;
4740 END IF;
4741
4742 IF g_line_rec.open_flag = FND_API.G_MISS_CHAR THEN
4743 g_line_rec.open_flag := Get_Open;
4744 END IF;
4745
4746 /* Added the following lines to fix the bug 2823553 */
4747
4748 IF g_line_rec.unit_list_price_per_pqty = FND_API.G_MISS_NUM THEN
4749 g_line_rec.unit_list_price_per_pqty := NULL;
4750 END IF;
4751
4752 IF g_line_rec.unit_selling_price_per_pqty = FND_API.G_MISS_NUM THEN
4753 g_line_rec.unit_selling_price_per_pqty := NULL;
4754 END IF;
4755
4756 -- Bug 3737773
4757 -- Moved the below code after call to defaulting FrameWork.
4758 -- Start of Code change for Bug 3671715
4759
4760 --IF g_line_rec.pricing_quantity = FND_API.G_MISS_NUM THEN
4761 -- g_line_rec.pricing_quantity := Get_Pricing_Quantity;
4762 --END IF;
4763
4764 IF g_line_rec.shipment_number = FND_API.G_MISS_NUM THEN
4765 g_line_rec.shipment_number := Get_Shipment_Number;
4766 END IF;
4767
4768 IF g_line_rec.shipping_interfaced_flag = FND_API.G_MISS_CHAR THEN
4769 g_line_rec.shipping_interfaced_flag := Get_Shipping_Interfaced;
4770 END IF;
4771
4772 IF l_debug_level > 0 THEN
4773 oe_debug_pub.add( 'AK BEFORE LINE CATEGORY1' ) ;
4774 END IF;
4775
4776 /* IF g_line_rec.line_category_code = FND_API.G_MISS_CHAR THEN
4777 g_line_rec.line_category_code :=
4778 Get_line_category(g_line_rec,p_old_line_rec);
4779 END IF;*/
4780
4781 /* btea begin This code is commented out to fix bug 1821024 Value should not get
4782 set before
4783 calling defaulting frame work. This value will be set after the defaulting f
4784 ramework
4785
4786 IF g_line_rec.calculate_price_flag = FND_API.G_MISS_CHAR THEN
4787 g_line_rec.calculate_price_flag := 'Y';
4788 END IF;
4789 btea end
4790 */
4791
4792
4793 -- Fixed bug 1206047: if user provides a value for the customer (sold_to)
4794 -- then override it with the value of sold_to from the header
4795 -- For the initial release, customer should be common on all lines of
4796 -- an order.
4797 IF NOT OE_GLOBALS.EQUAL( g_line_rec.sold_to_org_id
4798 ,p_old_line_rec.sold_to_org_id )
4799 THEN
4800 g_line_rec.sold_to_org_id := Get_Sold_To;
4801 END IF;
4802
4803 /* Start Fix for 2420456*/
4804
4805 IF l_debug_level > 0 THEN
4806 oe_debug_pub.add( 'TOLERANCE BELOW : '||G_LINE_REC.SHIP_TOLERANCE_BELOW , 3 ) ;
4807 oe_debug_pub.add( 'TOLERANCE BELOW : '||P_OLD_LINE_REC.SHIP_TOLERANCE_BELOW , 3 ) ;
4808 END IF;
4809
4810 IF nvl(g_line_rec.top_model_line_id,0) <> nvl(g_line_rec.ato_line_id,0) AND
4811 g_line_rec.top_model_line_id IS NOT NULL THEN
4812
4813 /* Change for bug 2276993 */
4814 --p_x_line_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
4815
4816 IF g_line_rec.ship_tolerance_below IS NULL OR
4817 g_line_rec.ship_tolerance_below = FND_API.G_MISS_NUM OR
4818 g_line_rec.ship_tolerance_below = p_old_Line_rec.ship_tolerance_below THEN
4819 g_line_rec.ship_tolerance_below := 0;
4820 l_set_tolerance_below := 'Y';
4821 IF l_debug_level > 0 THEN
4822 oe_debug_pub.add( 'SET THE TOLERANCES BELOW TO 0 ' , 3 ) ;
4823 END IF;
4824
4825 END IF;
4826
4827 IF g_line_rec.ship_tolerance_above IS NULL OR
4828 g_line_rec.ship_tolerance_above = FND_API.G_MISS_NUM OR
4829 g_line_rec.ship_tolerance_above = p_old_Line_rec.ship_tolerance_above THEN
4830 g_line_rec.ship_tolerance_above := 0;
4831 l_set_tolerance_above := 'Y';
4832 IF l_debug_level > 0 THEN
4833 oe_debug_pub.add( 'SET THE TOLERANCES ABOVE TO 0 ' , 3 ) ;
4834 END IF;
4835
4836 END IF;
4837
4838 END IF;
4839
4840 IF (nvl(g_line_rec.top_model_line_id,-1) <> nvl(g_line_rec.ato_line_id,-1) AND
4841 g_line_rec.top_model_line_id IS NOT NULL) AND
4842 (nvl(g_line_rec.ship_tolerance_below,0) <> 0 OR
4843 nvl(g_line_rec.ship_tolerance_above,0) <> 0 )THEN
4844
4845 IF l_debug_level > 0 THEN
4846 oe_debug_pub.add( 'SHIP TOLERANCES CAN NOT BE SPECIFIED ON PTOS' , 3 ) ;
4847 END IF;
4848 fnd_message.set_name('ONT','OE_NO_TOL_FOR_PTO');
4849 OE_MSG_PUB.Add;
4850 RAISE FND_API.G_EXC_ERROR;
4851
4852 END IF;
4853
4854 /* END Fix for 2420456*/
4855
4856 -- Due to incompatibilities in the record type structure
4857 -- copy the data to a rowtype record format
4858
4859 OE_LINE_UTIL_EXT.API_Rec_To_Rowtype_Rec
4860 (p_line_rec => g_line_rec
4861 ,x_rowtype_rec => l_in_rec);
4862 OE_LINE_UTIL_EXT.API_Rec_To_Rowtype_Rec
4863 (p_line_rec => p_old_line_rec
4864 ,x_rowtype_rec => l_in_old_rec);
4865
4866 --Perform blanket defaulting
4867 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
4868
4869 IF ( l_in_rec.operation = OE_GLOBALS.G_OPR_CREATE -- 7152122
4870 AND trunc( l_in_rec.request_date ) <> trunc(l_in_old_rec.request_date)
4871 AND l_in_rec.blanket_line_number IS NOT NULL )THEN
4872 l_in_rec.blanket_line_number := FND_API.G_MISS_NUM;
4873 END IF;
4874
4875 Perform_Blanket_Functions
4876 (p_x_line_rec => l_in_rec
4877 ,p_old_line_rec => l_in_old_rec
4878 ,p_default_record => 'N'
4879 ,x_blanket_request_date => l_blanket_request_date
4880 );
4881
4882 END IF; --pack i
4883
4884 -- Call the default handler framework to default the missing attributes
4885
4886 IF l_debug_level > 0 THEN
4887 oe_debug_pub.add( 'IN2 LINE NUMBER :'||L_IN_REC.LINE_NUMBER ) ;
4888 END IF;
4889
4890 l_rec := l_in_rec;
4891
4892 -- add the code below to populate party_type if pay now is enabled and
4893 -- there exists any defaulting condition template using party_type.
4894 -- the check here is to avoid performace overhead, so that party_type
4895 -- information is only loaded when needed.
4896 IF OE_Prepayment_Util.Get_Installment_Options = 'ENABLE_PAY_NOW'
4897 AND l_in_rec.sold_to_org_id IS NOT NULL
4898 AND l_in_rec.sold_to_org_id <> FND_API.G_MISS_NUM
4899 THEN
4900 BEGIN
4901 SELECT 'Y'
4902 INTO l_exists
4903 FROM oe_def_condn_elems
4904 WHERE value_string = 'ORGANIZATION'
4905 AND attribute_code = 'PARTY_TYPE'
4906 AND rownum = 1;
4907 EXCEPTION WHEN NO_DATA_FOUND THEN
4908 null;
4909 END;
4910
4911 IF l_exists = 'Y' THEN
4912 BEGIN
4913 SELECT party.party_type
4914 INTO l_party_type
4915 FROM hz_cust_accounts cust_acct,
4916 hz_parties party
4917 WHERE party.party_id = cust_acct.party_id
4918 AND cust_acct.cust_account_id = l_in_rec.sold_to_org_id;
4919 EXCEPTION WHEN NO_DATA_FOUND THEN
4920 null;
4921 END;
4922
4923 l_rec.party_type := l_party_type;
4924
4925 IF l_debug_level > 0 then
4926 oe_debug_pub.add('party type in defaulting is: '||l_party_type, 3);
4927 END IF;
4928 END IF;
4929 END IF;
4930
4931
4932 ONT_LINE_Def_Hdlr.Default_Record
4933 (p_x_rec => l_rec
4934 , p_initial_rec => l_in_rec
4935 , p_in_old_rec => l_in_old_rec
4936 );
4937
4938 -- More blanket defaulting
4939 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
4940
4941 IF l_rec.blanket_number IS NOT NULL
4942 AND (NOT OE_GLOBALS.EQUAL(l_in_rec.blanket_number,l_rec.blanket_number)
4943 OR trunc(l_blanket_request_date) <> trunc(l_rec.request_date)
4944 )
4945 THEN
4946
4947 if l_debug_level > 0 then
4948 oe_debug_pub.add('Blkt Num or Request Date changed');
4949 end if;
4950
4951 Perform_Blanket_Functions
4952 (p_x_line_rec => l_rec
4953 ,p_old_line_rec => l_in_old_rec
4954 ,p_default_record => 'Y'
4955 ,x_blanket_request_date => l_blanket_request_date
4956 );
4957
4958 END IF; --If Blanket Number is not null and changed after defaulting
4959
4960 END IF; --pack I or greater
4961
4962 -- copy the data back to a format that is compatible with the API architecture
4963
4964 OE_LINE_UTIL_EXT.RowType_Rec_to_API_Rec
4965 (p_record => l_rec
4966 ,x_api_rec => p_x_line_rec);
4967 -- sol_ord_er #16014165
4968 If p_x_Line_rec.subscription_enable_flag is NULL OR
4969 p_x_Line_rec.subscription_enable_flag = FND_API.G_MISS_CHAR THEN
4970 p_x_Line_rec.subscription_enable_flag := g_line_rec.subscription_enable_flag;
4971 if l_debug_level > 0 then
4972 oe_debug_pub.add( 'SK OEXDLINB.pls 16 after RowType_Rec_to_API_Rec, '||p_x_Line_rec.subscription_enable_flag);
4973 End If;
4974 End If;
4975 -- sol_ord_er #16014165 end
4976
4977 -- 2707939 --
4978 IF g_line_rec.override_atp_date_code <> FND_API.G_MISS_CHAR THEN
4979 p_x_line_rec.override_atp_date_code := g_line_rec.override_atp_date_code;
4980 END IF;
4981 IF g_line_rec.firm_demand_flag <> FND_API.G_MISS_CHAR THEN
4982 p_x_line_rec.firm_demand_flag := g_line_rec.firm_demand_flag;
4983 END IF;
4984
4985 /* Bug 2154960 Added call to default_active_agr_revision() to default
4986 active Agreement Revision. This API will call process order for Line entity
4987 again after defaulting Active Agreement Revision to default dependent attributes. */
4988
4989 IF p_x_line_rec.agreement_id IS NOT NULL AND
4990 p_x_line_rec.agreement_id <> FND_API.G_MISS_NUM THEN
4991
4992 IF NOT oe_globals.equal(p_x_line_rec.pricing_date,
4993 p_old_line_rec.pricing_date)
4994 OR
4995 not oe_globals.equal(p_x_line_rec.agreement_id,
4996 p_old_line_rec.agreement_id) THEN
4997
4998 IF p_x_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
4999 Default_Active_Agr_Revision
5000 ( p_x_line_rec => p_x_line_rec,
5001 p_old_line_rec => p_old_line_rec);
5002 END IF;
5003
5004 End If;
5005 End If;
5006
5007
5008 IF l_debug_level > 0 THEN
5009 oe_debug_pub.add( 'AFTER DEFAULTING LINE CATEGORY1' || P_X_LINE_REC.LINE_CATEGORY_CODE ) ;
5010 END IF;
5011
5012 IF p_x_line_rec.line_category_code = FND_API.G_MISS_CHAR OR -- added for 2421909
5013 p_x_line_rec.line_category_code IS NULL THEN
5014 p_x_line_rec.line_category_code := Get_line_category(p_x_line_rec,p_old_line_rec);
5015 IF l_debug_level > 0 THEN
5016 oe_debug_pub.add( 'AFTER CALLING LINE CATEGORY2' || P_X_LINE_REC.LINE_CATEGORY_CODE , 1 ) ;
5017 END IF;
5018 END IF;
5019
5020 -- Copy the value back to the out record for marketing source code.
5021 -- These columns are not enabled in the AK tables
5022
5023 if (p_x_line_rec.marketing_source_code_id = FND_API.G_MISS_NUM) then
5024 p_x_line_rec.marketing_source_code_id := NULL;
5025 else
5026 p_x_line_rec.marketing_source_code_id := p_x_line_rec.marketing_source_code_id;
5027 end if;
5028
5029
5030 --Code moved for bug 3737773 -starts here
5031 IF l_debug_level > 0 THEN
5032 oe_debug_pub.add( ' Before Defaulting P_Qty and P_Qty_Uom Values:' , 1 ) ;
5033 oe_debug_pub.add(' Pricing Qty: '|| p_x_line_rec.pricing_quantity ,1);
5034 oe_debug_pub.add(' Pricing UOM: '|| p_x_line_rec.pricing_quantity_uom,1 );
5035 oe_debug_pub.add(' Ordered Qty: '|| p_x_line_rec.ordered_quantity ,1);
5036 oe_debug_pub.add(' Ordered UOM: '|| p_x_line_rec.order_quantity_uom,1);
5037 END IF;
5038
5039 -- The code below is not required when order created from UI, but is required for the orders created/update othe sources like Process Order
5040 -- and Order Import. The Pricing Quantity and UOM is anyways returned by Pricing Engine in case UI. Added condition accordingly for bug 7675652.
5041
5042 --IF NOT (oe_globals.g_ui_flag) THEN -- added for bug 7675652 --commented for bug 11778250
5043 IF (p_x_line_rec.pricing_quantity = FND_API.G_MISS_NUM
5044 OR p_x_line_rec.pricing_quantity is NULL
5045 OR p_x_line_rec.pricing_quantity = -99999) THEN
5046 IF (p_x_line_rec.pricing_quantity_uom = FND_API.G_MISS_CHAR
5047 OR p_x_line_rec.pricing_quantity_uom is NULL) THEN
5048 p_x_line_rec.pricing_quantity := p_x_line_rec.ordered_quantity;
5049 p_x_line_rec.pricing_quantity_uom := p_x_line_rec.order_quantity_uom;
5050 ELSE --Pricing UOM has value but P_QTY is not populated
5051 p_x_line_rec.pricing_quantity := Get_Pricing_Quantity;
5052 --Added the message after review. May be value -99999 is returned when no conversion exists.
5053 if(p_x_line_rec.pricing_quantity = -99999) Then
5054 IF l_debug_level > 0 THEN
5055 oe_debug_pub.add(' Pricing Qty '|| p_x_line_rec.pricing_quantity ,1);
5056 oe_debug_pub.add(' Pricing UOM '|| p_x_line_rec.pricing_quantity_uom,1 );
5057 oe_debug_pub.add(' Ordered Qty '|| p_x_line_rec.ordered_quantity ,1);
5058 oe_debug_pub.add(' Ordered UOM '|| p_x_line_rec.order_quantity_uom,1);
5059 oe_debug_pub.add( ' Conversion does not exists' , 1 ) ;
5060 END IF;
5061
5062 FND_MESSAGE.SET_NAME('ONT', 'ONT_PRC_INVALID_UOM_CONVERSION');
5063 fnd_message.set_token('UOM_TEXT',p_x_line_rec.pricing_quantity_uom);
5064 OE_MSG_PUB.Add;
5065 RAISE FND_API.G_EXC_ERROR;
5066 END IF;
5067 -- End of code addition after review.
5068 END IF; -- End of Pricing UOM check
5069 ELSE -- Pricing Quantity has a valid Value
5070 IF (p_x_line_rec.pricing_quantity_uom = FND_API.G_MISS_CHAR
5071 OR p_x_line_rec.pricing_quantity_uom is NULL) THEN
5072 IF (p_x_line_rec.pricing_quantity = p_x_line_rec.ordered_quantity) THEN
5073 p_x_line_rec.pricing_quantity_uom := p_x_line_rec.order_quantity_uom;
5074 ELSE --P_QTY is not equal to O_QTY and P_UOM is Not Populated
5075 --RAISE ERROR
5076 IF l_debug_level > 0 THEN
5077 oe_debug_pub.add(' Pricing Qty '|| p_x_line_rec.pricing_quantity ,1);
5078 oe_debug_pub.add(' Pricing UOM '|| p_x_line_rec.pricing_quantity_uom,1 );
5079 oe_debug_pub.add(' Ordered Qty '|| p_x_line_rec.ordered_quantity ,1);
5080 oe_debug_pub.add(' Ordered UOM '|| p_x_line_rec.order_quantity_uom,1);
5081 oe_debug_pub.add( ' Pricing Qty is not equal to Ord Qty and P_UOM is not populated' , 1 ) ;
5082 END IF;
5083 --commented for bug 11778250
5084 /*IF p_x_line_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
5085 --Added the message after review.
5086 FND_MESSAGE.SET_NAME('ONT', 'ONT_INVALID_ORD_QTY_PRC_QTY');
5087 OE_MSG_PUB.Add;
5088 RAISE FND_API.G_EXC_ERROR;
5089 ELSE --BUG 4135361 */
5090 p_x_line_rec.pricing_quantity := p_x_line_rec.ordered_quantity;
5091 p_x_line_rec.pricing_quantity_uom := p_x_line_rec.order_quantity_uom;
5092 --END IF; commented for bug 11778250
5093 END IF;
5094 ELSE -- BOTH P_UOM and P_QTY has valid values Do Nothing
5095 NULL;
5096 END IF;
5097 END IF;
5098 -- end bug fix 3737773
5099 --END IF; -- added for bug 7675652 --commented for bug 11778250
5100
5101 --Btea begin fix bug 1821024,
5102 if (p_x_line_rec.calculate_price_flag = FND_API.G_MISS_CHAR or
5103 p_x_line_rec.calculate_price_flag is Null) Then
5104 p_x_line_rec.calculate_price_flag := 'Y';
5105 End If;
5106 --Btea end
5107
5108 -- Copy the value back to the out record for order source id.
5109 if (p_x_line_rec.order_source_id = FND_API.G_MISS_NUM) then
5110 IF l_debug_level > 0 THEN
5111 oe_debug_pub.add( 'OEXDLINB - AKSINGH - CHECK FOR G_MISS_NUM' ) ;
5112 END IF;
5113 p_x_line_rec.order_source_id := Get_Order_Source_Id;
5114 else
5115 IF l_debug_level > 0 THEN
5116 oe_debug_pub.add( 'OEXDLINB - AKSINGH - CHECK FOR ELSE' ) ;
5117 END IF;
5118 p_x_line_rec.order_source_id := p_x_line_rec.order_source_id;
5119 end if;
5120
5121 -- Copy the value back to the out record for Commitment_Id.
5122 if (p_x_line_rec.commitment_id = FND_API.G_MISS_NUM) then
5123 IF l_debug_level > 0 THEN
5124 oe_debug_pub.add( 'OEXDLINB - COMMITMENT_ID - CHECK FOR G_MISS_NUM' ) ;
5125 END IF;
5126 p_x_line_rec.commitment_id := NULL;
5127 else
5128 IF l_debug_level > 0 THEN
5129 oe_debug_pub.add( 'OEXDLINB - COMMITMENT_ID - CHECK FOR ELSE' ) ;
5130 END IF;
5131 p_x_line_rec.commitment_id := p_x_line_rec.commitment_id;
5132 end if;
5133
5134 /* 1581620 start */
5135
5136 IF (p_x_line_rec.tp_context = FND_API.G_MISS_CHAR) THEN
5137 p_x_line_rec.tp_context := NULL;
5138 END IF;
5139 IF (p_x_line_rec.tp_attribute1 = FND_API.G_MISS_CHAR) THEN
5140 p_x_line_rec.tp_attribute1 := NULL;
5141 END IF;
5142
5143 IF (p_x_line_rec.tp_attribute2 = FND_API.G_MISS_CHAR) THEN
5144 p_x_line_rec.tp_attribute2 := NULL;
5145 END IF;
5146
5147 IF (p_x_line_rec.tp_attribute3 = FND_API.G_MISS_CHAR) THEN
5148 p_x_line_rec.tp_attribute3 := NULL;
5149 END IF;
5150
5151 IF (p_x_line_rec.tp_attribute4 = FND_API.G_MISS_CHAR) THEN
5152 p_x_line_rec.tp_attribute4 := NULL;
5153 END IF;
5154
5155 IF (p_x_line_rec.tp_attribute5 = FND_API.G_MISS_CHAR) THEN
5156 p_x_line_rec.tp_attribute5 := NULL;
5157 END IF;
5158
5159 IF (p_x_line_rec.tp_attribute6 = FND_API.G_MISS_CHAR) THEN
5160 p_x_line_rec.tp_attribute6 := NULL;
5161 END IF;
5162
5163 IF (p_x_line_rec.tp_attribute7 = FND_API.G_MISS_CHAR) THEN
5164 p_x_line_rec.tp_attribute7 := NULL;
5165 END IF;
5166
5167 IF (p_x_line_rec.tp_attribute8 = FND_API.G_MISS_CHAR) THEN
5168 p_x_line_rec.tp_attribute8 := NULL;
5169 END IF;
5170
5171 IF (p_x_line_rec.tp_attribute9 = FND_API.G_MISS_CHAR) THEN
5172 p_x_line_rec.tp_attribute9 := NULL;
5173 END IF;
5174
5175 IF (p_x_line_rec.tp_attribute10 = FND_API.G_MISS_CHAR) THEN
5176 p_x_line_rec.tp_attribute10 := NULL;
5177 END IF;
5178
5179 IF (p_x_line_rec.tp_attribute11 = FND_API.G_MISS_CHAR) THEN
5180 p_x_line_rec.tp_attribute11 := NULL;
5181 END IF;
5182
5183 IF (p_x_line_rec.tp_attribute12 = FND_API.G_MISS_CHAR) THEN
5184 p_x_line_rec.tp_attribute12 := NULL;
5185 END IF;
5186
5187 IF (p_x_line_rec.tp_attribute13 = FND_API.G_MISS_CHAR) THEN
5188 p_x_line_rec.tp_attribute13 := NULL;
5189 END IF;
5190
5191 IF (p_x_line_rec.tp_attribute14 = FND_API.G_MISS_CHAR) THEN
5192 p_x_line_rec.tp_attribute14 := NULL;
5193 END IF;
5194
5195 IF (p_x_line_rec.tp_attribute15 = FND_API.G_MISS_CHAR) THEN
5196 p_x_line_rec.tp_attribute15 := NULL;
5197 END IF;
5198
5199 -- Commented for bug 8626559
5200 /*
5201 IF (p_x_line_rec.flow_status_code = FND_API.G_MISS_CHAR) THEN
5202 p_x_line_rec.flow_status_code := NULL;
5203 END IF;
5204 */
5205 -- Bug 8626559: Start
5206 IF (p_x_line_rec.flow_status_code = Fnd_Api.G_Miss_Char) THEN
5207 IF (p_x_line_rec.operation = Oe_Globals.G_Opr_Create) THEN
5208 IF p_x_line_rec.transaction_phase_code = 'N' THEN
5209 p_x_line_rec.flow_status_code := 'DRAFT';
5210 ELSE
5211 p_x_line_rec.flow_status_code := 'ENTERED';
5212 END IF; -- check on p_x_line_rec.transaction_phase_code
5213 ELSE
5214 p_x_line_rec.flow_status_code := NULL;
5215 END IF; -- check on p_x_line_rec.operation
5216 END IF;
5217 -- Bug 8626559: End
5218
5219
5220 IF (p_x_line_rec.drop_ship_flag = FND_API.G_MISS_CHAR) THEN
5221 p_x_line_rec.drop_ship_flag := NULL;
5222 END IF;
5223
5224 -- OR condition added for 3200019 so orig_sys_shipment_ref
5225 -- would not get defaulted incorrectly when entering multiple
5226 -- lines due to caching in SO UI like bug 2362210
5227 IF (p_x_line_rec.orig_sys_shipment_ref = FND_API.G_MISS_CHAR
5228 OR p_x_line_rec.orig_sys_shipment_ref IS NULL) AND
5229 (nvl(p_x_line_rec.source_document_id,0) <> 10) THEN
5230 IF (OE_CODE_CONTROL.Get_Code_Release_Level >= '110508') AND
5231 (g_multiple_shipments = 'YES') THEN
5232 p_x_line_rec.orig_sys_shipment_ref := 'OE_ORDER_LINES_ALL'||p_x_line_rec.line_id||'.'||'1';
5233 IF l_debug_level > 0 THEN
5234 oe_debug_pub.add( 'SHIP SYS = '||P_X_LINE_REC.ORIG_SYS_SHIPMENT_REF ) ;
5235 END IF;
5236 ELSE
5237 p_x_line_rec.orig_sys_shipment_ref := NULL;
5238 END IF;
5239 END IF;
5240
5241 if (p_x_line_rec.change_sequence = FND_API.G_MISS_CHAR) then
5242 p_x_line_rec.change_sequence := NULL;
5243 end if;
5244
5245 if (p_x_line_rec.customer_line_number = FND_API.G_MISS_CHAR) then
5246 p_x_line_rec.customer_line_number := NULL;
5247 end if;
5248
5249 if (p_x_line_rec.customer_shipment_number = FND_API.G_MISS_CHAR) then
5250 p_x_line_rec.customer_shipment_number := NULL;
5251 end if;
5252
5253 if (p_x_line_rec.customer_item_net_price = FND_API.G_MISS_NUM) then
5254 p_x_line_rec.customer_item_net_price := NULL;
5255 end if;
5256
5257 if (p_x_line_rec.customer_payment_term_id = FND_API.G_MISS_NUM) then
5258 p_x_line_rec.customer_payment_term_id := NULL;
5259 end if;
5260
5261 if (p_x_line_rec.reference_customer_trx_line_id = FND_API.G_MISS_NUM) then
5262 p_x_line_rec.reference_customer_trx_line_id := NULL;
5263 end if;
5264
5265 if (p_x_line_rec.sold_from_org_id = FND_API.G_MISS_NUM) then
5266 p_x_line_rec.sold_from_org_id := NULL;
5267 end if;
5268
5269 if (p_x_line_rec.mfg_lead_time = FND_API.G_MISS_NUM) then
5270 p_x_line_rec.mfg_lead_time := NULL;
5271 end if;
5272
5273 if (p_x_line_rec.lock_control = FND_API.G_MISS_NUM) then
5274 p_x_line_rec.lock_control := NULL;
5275 end if;
5276
5277 if (p_x_line_rec.re_source_flag = FND_API.G_MISS_CHAR) then
5278 p_x_line_rec.re_source_flag := NULL;
5279 end if;
5280
5281 if (p_x_line_rec.model_remnant_flag = FND_API.G_MISS_CHAR) then
5282 p_x_line_rec.model_remnant_flag := NULL;
5283 end if;
5284
5285 if (p_x_line_rec.shippable_flag = FND_API.G_MISS_CHAR) then
5286 p_x_line_rec.shippable_flag := NULL;
5287 end if;
5288
5289 /* 1581620 end */
5290
5291 -- Bug # 5490345
5292
5293 if (p_x_line_rec.minisite_id = FND_API.G_MISS_NUM) then
5294 p_x_line_rec.minisite_id := NULL;
5295 end if;
5296
5297
5298 --Distributor Orders
5299 if (p_x_line_rec.End_Customer_ID = FND_API.G_MISS_NUM) then
5300 p_x_line_rec.End_Customer_id := NULL;
5301 end if;
5302 if (p_x_line_rec.End_Customer_Contact_ID = FND_API.G_MISS_NUM) then
5303 p_x_line_rec.End_Customer_Contact_id := NULL;
5304 end if;
5305 if (p_x_line_rec.End_Customer_Site_Use_ID = FND_API.G_MISS_NUM) then
5306 p_x_line_rec.End_Customer_site_use_id := NULL;
5307 end if;
5308 if (p_x_line_rec.IB_OWNER = FND_API.G_MISS_CHAR) then
5309 p_x_line_rec.IB_OWNER := NULL;
5310 end if;
5311 if (p_x_line_rec.IB_CURRENT_LOCATION = FND_API.G_MISS_CHAR) then
5312 p_x_line_rec.IB_CURRENT_LOCATION := NULL;
5313 end if;
5314 if (p_x_line_rec.IB_INSTALLED_AT_LOCATION = FND_API.G_MISS_CHAR) then
5315 p_x_line_rec.IB_INSTALLED_AT_LOCATION := NULL;
5316 end if;
5317 --
5318 if (p_x_line_rec.blanket_number = FND_API.G_MISS_NUM) then
5319 p_x_line_rec.blanket_number := NULL;
5320 end if;
5321
5322 if (p_x_line_rec.blanket_line_number = FND_API.G_MISS_NUM) then
5323 p_x_line_rec.blanket_line_number := NULL;
5324 end if;
5325
5326 if (p_x_line_rec.blanket_version_number = FND_API.G_MISS_NUM) then
5327 p_x_line_rec.blanket_version_number := NULL;
5328 end if;
5329
5330 /* 1783766 start */
5331
5332 if (p_x_line_rec.fulfillment_set = FND_API.G_MISS_CHAR) then
5333 p_x_line_rec.fulfillment_set := NULL;
5334 end if;
5335
5336 if (p_x_line_rec.fulfillment_set_id = FND_API.G_MISS_NUM) then
5337 p_x_line_rec.fulfillment_set_id := NULL;
5338 end if;
5339
5340 /* 1783766 end */
5341
5342 -- OPM 02/JUN/00 BEGIN - Default process attributes as appropriate
5343 -- ===============================================================
5344 IF (p_x_line_rec.ordered_quantity_uom2 = FND_API.G_MISS_CHAR)
5345 OR (p_x_line_rec.ordered_quantity_uom2 IS NULL) THEN
5346 p_x_line_rec.ordered_quantity_uom2 :=
5347 Get_Dual_Uom(p_line_rec => p_x_line_rec); -- INVCONV
5348 END IF;
5349 -- INVCONV -- NORMAL DEFAULTING IS USED NOW SO TAKE OUT
5350 /* -- OPM bug 2553805 do not re-default the preferred_grade if this is a copied order
5351 IF ( (p_x_line_rec.preferred_grade = FND_API.G_MISS_CHAR)
5352 OR (p_x_line_rec.preferred_grade IS NULL) )
5353 and
5354 (nvl( p_x_line_rec.source_document_type_id, 0 ) <> 2 ) -- added line for 2553805
5355 THEN
5356 p_x_line_rec.preferred_grade :=
5357 OE_Line_Util.Get_Preferred_Grade(p_line_rec => p_x_line_rec,
5358 p_old_line_rec => p_old_line_rec);
5359 END IF; */
5360
5361 IF (p_x_line_rec.ordered_quantity2 = FND_API.G_MISS_NUM) THEN
5362 p_x_line_rec.ordered_quantity2 := NULL;
5363 END IF;
5364
5365 --bug 8563297 kbanddyo FP for 12.1.1 for bug 4065790
5366 /* yannamal Begin OPM Bug 4065790 14/12/04 */
5367 -- For Dual Uom Item, If ordered_quantity2 is null and ordered_quantity_uom2 is not null and ordered_quantity is not null
5368 -- after clearing, then at this point of re-defaulting, Added call to routine Oe_Line_Util.Calculate Ordered quantity2 to populate ordered_quantity2.
5369
5370 IF (p_x_line_rec.ordered_quantity2 IS NULL AND p_x_line_rec.ordered_quantity_uom2 IS NOT NULL AND
5371 p_x_line_rec.ordered_quantity_uom2 <> FND_API.G_MISS_CHAR AND
5372 p_x_line_rec.ordered_quantity IS NOT NULL AND p_x_line_rec.ordered_quantity <> FND_API.G_MISS_NUM) THEN
5373 -- p_x_line_rec.ordered_quantity2 := OE_LINE_UTIL.Calculate_ordered_quantity2(p_line_rec => p_x_line_rec) ;
5374 OE_LINE_UTIL.sync_dual_qty(P_X_LINE_REC => p_x_line_rec,P_OLD_LINE_REC=>p_old_line_rec);
5375 END IF ;
5376
5377 /* yannamal End OPM Bug 4065790 14/12/04 */
5378
5379
5380 -- OPM 03/MAY/00 END
5381 -- =================
5382
5383 -- Since we are moving to ship method we allways default freight carrier
5384 -- from ship method and make sure to overide whatever user sends in
5385
5386 p_x_line_rec.freight_carrier_code :=
5387 Get_Freight_Carrier(p_line_rec => p_x_line_rec,
5388 p_old_line_rec => p_old_line_rec);
5389
5390 -- when order import do not pass item_identifier_type, default to INT
5391
5392 IF ((p_x_line_rec.item_identifier_type = FND_API.G_MISS_CHAR
5393 OR p_x_line_rec.item_identifier_type is null) AND
5394 p_x_line_rec.inventory_item_id is not null AND
5395 p_x_line_rec.inventory_item_id <> FND_API.G_MISS_NUM)
5396 THEN
5397 -- Re-default to INT only if item_identifier_type was previously null
5398 -- otherwise retain the old value. For example: If item identifier was
5399 -- CUST and now became null due to dependency on sold_to we should keep the
5400 -- value as CUST (should not over-write to INT)
5401
5402 IF p_old_line_rec.item_identifier_type IS NULL THEN
5403 IF l_debug_level > 0 THEN
5404 oe_debug_pub.add( 'ITEM_IDENTIFIER_TYPE IS NULL , DEFAULT TO INT' ) ;
5405 END IF;
5406 p_x_line_rec.item_identifier_type := 'INT';
5407 ELSE
5408 IF l_debug_level > 0 THEN
5409 oe_debug_pub.add( 'ASSIGNING ITEM_IDENTIFIER_TYPE FROM P_OLD_LINE_REC: '||P_OLD_LINE_REC.ITEM_IDENTIFIER_TYPE ) ;
5410 END IF;
5411 p_x_line_rec.item_identifier_type := p_old_line_rec.item_identifier_type;
5412 END IF;
5413 END IF;
5414
5415 IF p_x_line_rec.source_type_code is null OR
5416 p_x_line_rec.source_type_code = FND_API.G_MISS_CHAR THEN
5417 p_x_line_rec.source_type_code := OE_GLOBALS.G_SOURCE_INTERNAL;
5418 END IF;
5419
5420 -- Bug 5708174
5421 IF p_x_line_rec.source_type_code = OE_GLOBALS.G_SOURCE_EXTERNAL THEN
5422 p_x_line_rec.subinventory := NULL;
5423 END IF;
5424 -- Added to fix the issue in bug 2894486
5425 IF p_x_line_rec.line_category_code = 'RETURN' THEN
5426 p_x_line_rec.source_type_code := OE_GLOBALS.G_SOURCE_INTERNAL;
5427 p_x_line_rec.ato_line_id := NULL;
5428 END IF;
5429
5430 -- Bug 5331971, internal orders shall not be externally sourced
5431 IF p_x_line_rec.order_source_id = 10 THEN
5432 p_x_line_rec.source_type_code := OE_GLOBALS.G_SOURCE_INTERNAL;
5433 END IF;
5434 /* commented for bug 13088960
5435 -- This is the new condition aksingh changed on 04/22/01
5436 IF NOT OE_GLOBALS.Equal(p_x_line_rec.request_date,
5437 p_old_line_rec.request_date)
5438 THEN
5439 IF l_debug_level > 0 THEN
5440 oe_debug_pub.add( 'OEXDLINB -1- CHECK FOR G_MISS_DATE FOR REQUEST ' ) ;
5441 END IF;
5442 IF p_x_line_rec.request_date <> FND_API.G_MISS_DATE THEN
5443 -- aksingh added this if for the bug 1745501
5444 IF l_debug_level > 0 THEN
5445 oe_debug_pub.add( 'OEXDLINB -2- CHECK FOR G_MISS_DATE FOR LATEST ' ) ;
5446 END IF;
5447 IF OE_GLOBALS.Equal(p_x_line_rec.latest_acceptable_date,
5448 p_old_line_rec.latest_acceptable_date)
5449 OR p_x_line_rec.latest_acceptable_date = FND_API.G_MISS_DATE
5450 THEN
5451 IF l_debug_level > 0 THEN
5452 oe_debug_pub.add( 'OEXDLINB -3- AFTER CHECK FOR G_MISS_DATE FOR LATEST ' ) ;
5453 END IF;
5454 p_x_line_rec.latest_acceptable_date :=
5455 Get_Latest_Acceptable_Date(p_x_line_rec.request_date);
5456 END IF;
5457 END IF;
5458 END IF;
5459 --end of comments for bug 13088960
5460 */
5461 -- Item Substitution
5462 IF p_x_line_rec.Original_Inventory_Item_Id = FND_API.G_MISS_NUM THEN
5463 p_x_line_rec.Original_Inventory_Item_Id := Null;
5464 END IF;
5465
5466 IF p_x_line_rec.Original_item_identifier_Type = FND_API.G_MISS_CHAR THEN
5467 p_x_line_rec.Original_item_identifier_Type := Null;
5468 END IF;
5469
5470 IF p_x_line_rec.Original_ordered_item_id = FND_API.G_MISS_NUM THEN
5471 p_x_line_rec.Original_ordered_item_id := Null;
5472 END IF;
5473
5474 IF p_x_line_rec.Original_ordered_item = FND_API.G_MISS_CHAR THEN
5475 p_x_line_rec.Original_ordered_item := Null;
5476 END IF;
5477
5478 IF p_x_line_rec.Item_relationship_type = FND_API.G_MISS_NUM THEN
5479 p_x_line_rec.Item_relationship_type := Null;
5480 END IF;
5481
5482 IF p_x_line_rec.Item_substitution_type_code = FND_API.G_MISS_CHAR THEN
5483 p_x_line_rec.Item_substitution_type_code := Null;
5484 END IF;
5485
5486 IF p_x_line_rec.Late_Demand_Penalty_Factor = FND_API.G_MISS_NUM THEN
5487 p_x_line_rec.Late_Demand_Penalty_Factor := Null;
5488 END IF;
5489
5490 IF p_x_line_rec.Override_atp_date_code = FND_API.G_MISS_CHAR THEN
5491 p_x_line_rec.Override_atp_date_code := Null;
5492 END IF;
5493
5494 IF p_x_line_rec.firm_demand_flag = FND_API.G_MISS_CHAR THEN
5495 p_x_line_rec.firm_demand_flag := Null;
5496 END IF;
5497 --retro{
5498 IF (p_x_line_rec.retrobill_request_id = FND_API.G_MISS_NUM) THEN
5499 p_x_line_rec.retrobill_request_id := NULL;
5500 END IF;
5501 --retro}
5502
5503 --Customer Acceptance
5504 IF p_x_line_rec.CONTINGENCY_ID = FND_API.G_MISS_NUM THEN
5505 p_x_line_rec.CONTINGENCY_ID := NULL ;
5506 END IF;
5507 IF p_x_line_rec.REVREC_EVENT_CODE = FND_API.G_MISS_CHAR THEN
5508 p_x_line_rec.REVREC_EVENT_CODE:= NULL ;
5509 END IF;
5510 IF p_x_line_rec.REVREC_EXPIRATION_DAYS = FND_API.G_MISS_NUM THEN
5511 p_x_line_rec.REVREC_EXPIRATION_DAYS:= NULL ;
5512 END IF;
5513 IF p_x_line_rec.ACCEPTED_QUANTITY = FND_API.G_MISS_NUM THEN
5514 p_x_line_rec.ACCEPTED_QUANTITY:= NULL ;
5515 END IF;
5516 IF p_x_line_rec.REVREC_COMMENTS = FND_API.G_MISS_CHAR THEN
5517 p_x_line_rec.REVREC_COMMENTS:= NULL ;
5518 END IF;
5519 IF p_x_line_rec.REVREC_SIGNATURE = FND_API.G_MISS_CHAR THEN
5520 p_x_line_rec.REVREC_SIGNATURE:= NULL ;
5521 END IF;
5522 IF p_x_line_rec.REVREC_SIGNATURE_DATE = FND_API.G_MISS_DATE THEN
5523 p_x_line_rec.REVREC_SIGNATURE_DATE:= NULL ;
5524 END IF;
5525 IF p_x_line_rec.ACCEPTED_BY = FND_API.G_MISS_NUM THEN
5526 p_x_line_rec.ACCEPTED_BY:= NULL ;
5527 END IF;
5528 IF p_x_line_rec.REVREC_REFERENCE_DOCUMENT = FND_API.G_MISS_CHAR THEN
5529 p_x_line_rec.REVREC_REFERENCE_DOCUMENT:= NULL ;
5530 END IF;
5531 IF p_x_line_rec.REVREC_IMPLICIT_FLAG = FND_API.G_MISS_CHAR THEN
5532 p_x_line_rec.REVREC_IMPLICIT_FLAG := NULL ;
5533 END IF;
5534 -- sol_ord_er #16014165
5535 IF p_x_line_rec.service_bill_option_code = FND_API.G_MISS_CHAR THEN
5536 p_x_line_rec.service_bill_option_code := NULL;
5537 END IF;
5538 IF p_x_line_rec.service_bill_profile_id = FND_API.G_MISS_NUM THEN
5539 p_x_line_rec.service_bill_profile_id := NULL;
5540 END IF;
5541 IF p_x_line_rec.service_cov_template_id = FND_API.G_MISS_NUM THEN
5542 p_x_line_rec.service_cov_template_id := NULL;
5543 END IF;
5544 IF p_x_line_rec.service_subs_template_id = FND_API.G_MISS_NUM THEN
5545 p_x_line_rec.service_subs_template_id := NULL;
5546 END IF;
5547 IF p_x_line_rec.service_first_period_amount = FND_API.G_MISS_NUM THEN
5548 p_x_line_rec.service_first_period_amount := NULL;
5549 END IF;
5550 IF p_x_line_rec.service_first_period_enddate = FND_API.G_MISS_DATE THEN
5551 p_x_line_rec.service_first_period_enddate := NULL;
5552 END IF;
5553 -- sol_ord_er #16014165
5554
5555 -- Bug 14125505 (DOO Integration related attributes): Start
5556 IF p_x_line_rec.bypass_sch_flag = FND_API.G_MISS_CHAR THEN
5557 p_x_line_rec.bypass_sch_flag := NULL;
5558 END IF;
5559 IF p_x_line_rec.pre_exploded_flag = FND_API.G_MISS_CHAR THEN
5560 p_x_line_rec.pre_exploded_flag := NULL;
5561 END IF;
5562 -- Bug 14125505 (DOO Integration related attributes): End
5563
5564 --Customer Acceptance
5565 -- bug 4203691 recurring charges
5566 IF p_x_line_rec.charge_periodicity_code = FND_API.G_MISS_CHAR THEN
5567 p_x_line_rec.charge_periodicity_code := NULL ;
5568 END IF;
5569
5570 /* The following lines are commented to fix the bug 1409036 */
5571 /*
5572 IF NOT OE_GLOBALS.Equal(p_x_line_rec.line_type_id,
5573 p_old_line_rec.line_type_id)
5574 THEN
5575 p_x_line_rec.source_type_code :=
5576 Get_Source_Type(p_source_type => p_x_line_rec.source_type_code,
5577 p_line_type_id => p_x_line_rec.line_type_id);
5578 END IF;
5579 */
5580
5581 -- get shippable is dependent on model_option_defaulting.
5582 -- ## bug fix: 1609895, shippable flag from warehouse
5583 -- sol_ord_er #16014165
5584 -- It is possible to default the Subsscription Item as Standard and then change to Subscription
5585 IF l_debug_level > 0 THEN
5586 oe_debug_pub.add( 'SK OEXDLINB.pls 2255 Subscription= '||p_x_line_rec.subscription_enable_flag
5587 ||' Old Subscription= '||p_old_line_rec.subscription_enable_flag,5 );
5588 oe_debug_pub.add( ' Shippable= '|| p_x_line_rec.shippable_flag
5589 ||' Old Shippable= '||p_old_line_rec.shippable_flag);
5590 oe_debug_pub.add( ' Item Type= '||p_x_line_rec.item_type_code
5591 ||' Old Item Type= '|| p_old_line_rec.item_type_code);
5592 End If;
5593 -- sol_ord_er #16014165 end
5594 IF (p_x_line_rec.shippable_flag is NULL OR
5595 p_x_line_rec.shippable_flag = FND_API.G_MISS_CHAR )
5596 OR NOT OE_GLOBALS.Equal(p_x_Line_rec.subscription_enable_flag,
5597 p_old_line_rec.subscription_enable_flag) THEN -- sol_ord_er #16014165
5598 IF l_debug_level > 0 THEN
5599 oe_debug_pub.add( 'CALLING GET SHIPPABLE_FLAG ' , 1 ) ;
5600 END IF;
5601 -- sol_ord_er #16014165
5602 /* For Subscription Line no Shipping Required */
5603 If p_x_Line_rec.subscription_enable_flag ='Y' then
5604 IF l_debug_level > 0 THEN
5605 oe_debug_pub.add( 'SK OEXDLINB.pls 2244 Subscription Line, Not Shippable ' );
5606 End If;
5607 p_x_line_rec.shippable_flag := 'N';
5608 else
5609 -- sol_ord_er #16014165 end
5610 p_x_line_rec.shippable_flag :=
5611 Get_Shippable( p_line_id => p_x_line_rec.line_id
5612 ,p_inventory_item_id => p_x_line_rec.inventory_item_id
5613 ,p_ship_from_org_id => p_x_line_rec.ship_from_org_id
5614 ,p_ato_line_id => p_x_line_rec.ato_line_id
5615 ,p_item_type_code => p_x_line_rec.item_type_code );
5616 end if; --sol_ord_er #16014165
5617 END IF;
5618 -- When a Subscription Service Line is converted into Return Line, Dependent Fields on flag has to be cleared
5619 -- This is only required if the Item Type changes to Standard but with the old flag value as Y(where it cant be Standard)
5620 If NVL(p_old_line_rec.subscription_enable_flag,'N')='Y' and p_x_line_rec.item_type_code = 'STANDARD'
5621 THEN
5622 IF l_debug_level > 0 THEN
5623 oe_debug_pub.add(' SK OEXDLINB.pls 2255.1 Clearing Dependency for Subscription Service');
5624 End If;
5625 p_x_line_rec.service_Start_date := '';
5626 p_x_line_rec.service_end_date := '';
5627 p_x_line_rec.service_period := '';
5628 p_x_line_rec.service_duration := '';
5629 END IF;
5630
5631 IF p_x_line_rec.schedule_status_code is null
5632 AND NOT OE_GLOBALS.Equal(p_old_line_rec.ship_from_org_id,
5633 p_x_line_rec.ship_from_org_id) THEN
5634 IF p_x_line_rec.ship_from_org_id is not null
5635 THEN
5636 IF l_debug_level > 0 THEN
5637 oe_debug_pub.add( 'SETTING RE_SOURCE_FLAG TO N' , 1 ) ;
5638 END IF;
5639 p_x_line_rec.re_source_flag := 'N';
5640 ELSE
5641 IF l_debug_level > 0 THEN
5642 oe_debug_pub.add( '1.SETTING RE_SOURCE_FLAG TO NULL' , 1 ) ;
5643 END IF;
5644 p_x_line_rec.re_source_flag := '';
5645 END IF;
5646 END IF;
5647
5648 /* With the new set and scheduling functionality the set id is created when a
5649 line is requested into a set and also gets cascaded if the operation is update
5650 and the children of the model has been already created
5651 this logic fires only when the scheduling branch profiel is set to Yes */
5652 -- 4118431
5653
5654 --IF NVL(FND_PROFILE.VALUE('ONT_BRANCH_SCHEDULING'),'N') = 'Y'--Bug4504362
5655 IF p_x_line_rec.line_id > 0 THEN
5656
5657 oe_Set_util.Default_line_set
5658 (p_x_line_rec => p_x_line_rec,
5659 p_old_line_rec => p_old_line_rec);
5660
5661 IF p_x_line_rec.line_category_code = 'RETURN'
5662 OR p_x_line_rec.source_type_code = 'EXTERNAL' THEN
5663
5664 p_x_line_rec.ship_set_id := NULL;
5665 p_x_line_rec.ship_set := NULL;
5666 p_x_line_rec.arrival_set_id := NULL;
5667 p_x_line_rec.arrival_set := NULL;
5668
5669 END IF;
5670 END IF;
5671
5672
5673 IF p_x_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE
5674 THEN
5675 model_option_update (p_x_line_rec => p_x_line_rec);
5676 END IF;
5677
5678 /* Please do not put any code after the following IF fix for 2116098*/
5679
5680 IF nvl(p_x_line_rec.top_model_line_id,0) <> nvl(p_x_line_rec.ato_line_id,0) AND
5681 p_x_line_rec.top_model_line_id IS NOT NULL THEN
5682
5683 /* Change for bug 2276993 */
5684 --p_x_line_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
5685
5686 /* Fix for bug 2420456 */
5687 IF l_set_tolerance_below = 'Y' THEN
5688
5689 IF l_debug_level > 0 THEN
5690 oe_debug_pub.add( 'TOLERANCE BELOW : '||P_X_LINE_REC.SHIP_TOLERANCE_BELOW , 3 ) ;
5691 END IF;
5692 p_x_line_rec.ship_tolerance_below := 0;
5693 l_set_tolerance_below := 'N';
5694 IF l_debug_level > 0 THEN
5695 oe_debug_pub.add( 'SET THE TOLERANCES BELOW TO 0 ' , 3 ) ;
5696 END IF;
5697
5698 END IF;
5699
5700 IF l_set_tolerance_above = 'Y' THEN
5701
5702 IF l_debug_level > 0 THEN
5703 oe_debug_pub.add( 'TOLERANCE BELOW : '||P_X_LINE_REC.SHIP_TOLERANCE_ABOVE , 3 ) ;
5704 END IF;
5705 p_x_line_rec.ship_tolerance_above := 0;
5706 l_set_tolerance_above := 'N';
5707 IF l_debug_level > 0 THEN
5708 oe_debug_pub.add( 'SET THE TOLERANCES ABOVE TO 0 ' , 3 ) ;
5709 END IF;
5710
5711 END IF;
5712
5713 END IF;
5714
5715 IF p_x_line_rec.user_item_description = FND_API.G_MISS_CHAR THEN
5716 p_x_line_rec.user_item_description := Null;
5717 END IF;
5718
5719 IF p_x_line_rec.fulfillment_base = FND_API.G_MISS_CHAR THEN
5720 p_x_line_rec.fulfillment_base := Null;--ER#14763609
5721 END IF;
5722
5723 -- to clear out user_item_description if item changes
5724 -- and user_item_description is not changing.
5725 IF NOT OE_GLOBALS.Equal(p_x_line_rec.inventory_item_id,
5726 p_old_line_rec.inventory_item_id)
5727 AND OE_GLOBALS.Equal(p_x_line_rec.user_item_description,
5728 p_old_line_rec.user_item_description)
5729 AND p_old_line_rec.user_item_description IS NOT NULL THEN
5730
5731 p_x_line_rec.user_item_description := NULL;
5732 FND_MESSAGE.Set_Name('ONT', 'ONT_USER_ITEM_DESC_CLEARED');
5733 IF l_debug_level > 0 THEN
5734 oe_debug_pub.add( 'CLEAR OUT USER_ITEM_DESCRIPTION WHEN ITEM CHANGES.' , 3 ) ;
5735 END IF;
5736
5737 END IF;
5738
5739 -- Override List Price
5740 IF (OE_CODE_CONTROL.Get_Code_Release_Level >= '110510') THEN
5741 IF p_x_line_rec.original_list_price = FND_API.G_MISS_NUM THEN
5742 p_x_line_rec.original_list_price := NULL;
5743 END IF;
5744 END IF;
5745 -- Override List Price
5746
5747 -- Bug 9790479 Start
5748 IF p_x_line_rec.price_request_code = FND_API.G_MISS_CHAR THEN
5749 p_x_line_rec.price_request_code := NULL;
5750 END IF;
5751 -- Bug 9790479 End
5752
5753 -- Bug 14340558 Start
5754 IF p_x_line_rec.earliest_ship_date = FND_API.G_MISS_DATE THEN
5755 p_x_line_rec.earliest_ship_date := NULL;
5756 END IF;
5757 -- Bug 14340558 End
5758
5759 IF l_debug_level > 0 THEN
5760 oe_debug_pub.add( 'EXIT OE_DEFAULT_LINE.ATTRIBUTES' , 1 ) ;
5761 END IF;
5762 EXCEPTION
5763
5764 WHEN FND_API.G_EXC_ERROR THEN
5765
5766 RAISE FND_API.G_EXC_ERROR;
5767
5768 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5769
5770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5771
5772 WHEN OTHERS THEN
5773
5774 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5775 THEN
5776 OE_MSG_PUB.Add_Exc_Msg
5777 ( G_PKG_NAME ,
5778 'Attributes'
5779 );
5780 END IF;
5781
5782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5783
5784 END Attributes;
5785
5786 /*----------------------------------------------------------
5787 FUNCTION Get_Dual_Uom
5788 ----------------------------------------------------------- INVCONV REMOVEd from OE_line_util
5789 */
5790
5791 FUNCTION Get_Dual_Uom(p_line_rec OE_ORDER_PUB.Line_Rec_Type)
5792 RETURN VARCHAR2
5793 IS
5794 -- l_APPS_UOM2 VARCHAR2(3) := NULL; INVCONV
5795 l_status VARCHAR2(1);
5796 l_msg_count NUMBER;
5797 l_msg_data VARCHAR2(2000);
5798 l_item_rec OE_ORDER_CACHE.item_rec_type;
5799
5800 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5801
5802 BEGIN
5803 if l_debug_level > 0 then
5804 oe_debug_pub.add('Enter Get dual uom');
5805 end if;
5806
5807 IF oe_line_util.dual_uom_control -- INVCONV Process_Characteristics
5808 (p_line_rec.inventory_item_id,p_line_rec.ship_from_org_id,l_item_rec) THEN
5809 IF l_item_rec.tracking_quantity_ind = 'PS' THEN -- INVCONV
5810 if l_debug_level > 0 then
5811 oe_debug_pub.add('Get dual uom - tracking in P and S ');
5812 end if;
5813 /* convert 4 digit apps OPM codes to equivalent 3 byte APPS codes */
5814 /* Primary UM
5815 GMI_Reservation_Util.Get_AppsUOM_from_OPMUOM
5816 (p_OPM_UOM => l_item_rec.opm_item_um2
5817 ,x_Apps_UOM => l_APPS_UOM2
5818 ,x_return_status => l_status
5819 ,x_msg_count => l_msg_count
5820 ,x_msg_data => l_msg_data); */
5821 RETURN l_item_rec.secondary_uom_code;
5822
5823
5824
5825 else -- INVCONV
5826 return NULL;
5827 END IF; -- IF l_item_rec.tracking_quantity_ind = 'PS' THEN -- INVCONV
5828
5829
5830 else
5831
5832 return null;
5833
5834 END IF; -- IF oe_line_util.dual_uom_control -- INVCONV Process_Characteristics
5835
5836
5837 if l_debug_level > 0 then
5838 oe_debug_pub.add('Get Dual Uom returns dual UM of ' || l_item_rec.secondary_uom_code);
5839 end if;
5840
5841 EXCEPTION
5842
5843 WHEN NO_DATA_FOUND THEN
5844
5845 if l_debug_level > 0 then
5846 oe_debug_pub.add('No Data Found Get Dual Uom' );
5847 end if;
5848 RETURN NULL;
5849
5850 WHEN OTHERS THEN
5851 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5852 THEN
5853 OE_MSG_PUB.Add_Exc_Msg
5854 ( G_PKG_NAME ,
5855 'Get_Dual_Uom'
5856 );
5857 END IF;
5858 if l_debug_level > 0 then
5859 oe_debug_pub.add('others in get_dual uom', 1);
5860 end if;
5861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5862
5863
5864 END Get_Dual_Uom;
5865
5866 -- Added Set_Header_Def_Hdlr_rec for bug 4668200
5867 -- This procedure will set the ONT_Header_Def_Hdlr.g_record with the information in header record.
5868 -- An attribute on line can be defaulted based on a PL/SQL API
5869 -- The API can also refer to ONT_Header_Def_Hdlr.g_record
5870 PROCEDURE Set_Header_Def_Hdlr_Rec (p_header_id IN NUMBER)
5871 IS
5872 l_header_rec OE_Order_PUB.Header_Rec_Type ;
5873 l_rowtype_header_rec OE_AK_ORDER_HEADERS_V%ROWTYPE;
5874 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5875 BEGIN
5876
5877 if l_debug_level >0 then
5878 oe_debug_pub.add(' Entering in OE_Default_Test.Set_Header_Def_Hdlr_Rec with Header Id: '|| p_header_id);
5879 end if ;
5880
5881 IF ( ONT_Header_Def_Hdlr.g_record.header_id IS NULL OR
5882 ONT_Header_Def_Hdlr.g_record.header_id <> p_header_id)
5883 THEN
5884 if OE_ORDER_CACHE.g_header_rec.header_id = p_header_id then
5885 l_header_rec := OE_ORDER_CACHE.g_header_rec ;
5886 else
5887 OE_Header_Util.Query_Row
5888 ( p_header_id => p_header_id
5889 , x_header_rec => l_header_rec );
5890 end if ;
5891
5892 OE_Header_UTIL.API_Rec_To_Rowtype_Rec
5893 ( p_header_rec => l_header_rec
5894 ,x_rowtype_rec => l_rowtype_header_rec);
5895
5896 ONT_Header_Def_Hdlr.g_record := l_rowtype_header_rec ;
5897
5898 if l_debug_level >0 then
5899 oe_debug_pub.add('ONT_Header_Def_Hdlr.g_record.Header_id: '||ONT_Header_Def_Hdlr.g_record.Header_id);
5900 end if ;
5901
5902 END IF ;
5903
5904 if l_debug_level >0 then
5905 oe_debug_pub.add(' Exiting OE_Default_Test.Set_Header_Def_Hdlr_Rec ');
5906 end if ;
5907 EXCEPTION
5908 When Others Then
5909 if l_debug_level >0 then
5910 oe_debug_pub.add(' Exception in OE_Default_Test.Set_Header_Def_Hdlr_Rec ');
5911 end if ;
5912 END Set_Header_Def_Hdlr_Rec ;
5913
5914 END OE_Default_Line;