42: FROM hz_cust_site_uses -- Bug 2138398
43: WHERE site_use_id = p_hold_entity_id;
44: EXCEPTION
45: WHEN no_data_found then
46: --x_return_status := FND_API.G_RET_STS_ERROR;
47: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
48: OE_MSG_PUB.ADD;
49: fnd_message.set_token('SITE_USE_ID',
50: to_char(p_hold_entity_id));
50: to_char(p_hold_entity_id));
51: IF l_debug_level > 0 THEN
52: oe_debug_pub.add( 'HOLD_SITE_CODE:INVALID SITE USE ID' , 1 ) ;
53: END IF;
54: RAISE FND_API.G_EXC_ERROR;
55: END;
56: IF l_site_use_code = 'BILL_TO' THEN
57: l_hold_site_code := 'B';
58: ELSE
89: l_org_id := MO_GLOBAL.get_current_org_id;
90: IF l_org_id IS NULL THEN
91: -- org_id is null, raise an error.
92: oe_debug_pub.add('Org_Id is NULL',1);
93: x_return_status := FND_API.G_RET_STS_ERROR ;
94: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
95: FND_MSG_PUB.ADD;
96: RAISE FND_API.G_EXC_ERROR;
97: END IF;
92: oe_debug_pub.add('Org_Id is NULL',1);
93: x_return_status := FND_API.G_RET_STS_ERROR ;
94: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
95: FND_MSG_PUB.ADD;
96: RAISE FND_API.G_EXC_ERROR;
97: END IF;
98:
99: x_return_status := FND_API.G_RET_STS_SUCCESS;
100: /* Either hold_source_id should be passed in OR
95: FND_MSG_PUB.ADD;
96: RAISE FND_API.G_EXC_ERROR;
97: END IF;
98:
99: x_return_status := FND_API.G_RET_STS_SUCCESS;
100: /* Either hold_source_id should be passed in OR
101: Hold_entity_code and Hold_entity_id should be passed in */
102:
103: If p_hold_source_rec.HOLD_SOURCE_ID is NOT NULL THEN
153: else
154: IF l_debug_level > 0 THEN
155: oe_debug_pub.add( 'OE_HOLDS_PUB.UPDATE_HOLD_COMMENTS:' || 'EITHER PASS HOLD_SOURCE_ID OR HOLD_ENTITY_CODE/HOLD_ENTITY_ID' ) ;
156: END IF;
157: RAISE FND_API.G_EXC_ERROR;
158: end if;
159:
160: EXCEPTION /* Procedure exception handler */
161:
158: end if;
159:
160: EXCEPTION /* Procedure exception handler */
161:
162: WHEN FND_API.G_EXC_ERROR THEN
163: x_return_status := FND_API.G_RET_STS_ERROR ;
164: IF l_debug_level > 0 THEN
165: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
166: END IF;
159:
160: EXCEPTION /* Procedure exception handler */
161:
162: WHEN FND_API.G_EXC_ERROR THEN
163: x_return_status := FND_API.G_RET_STS_ERROR ;
164: IF l_debug_level > 0 THEN
165: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
166: END IF;
167: OE_MSG_PUB.Count_And_Get
168: ( p_count => x_msg_count,
169: p_data => x_msg_data
170: );
171:
172: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
174: IF l_debug_level > 0 THEN
175: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
176: END IF;
169: p_data => x_msg_data
170: );
171:
172: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
173: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
174: IF l_debug_level > 0 THEN
175: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
176: END IF;
177: OE_MSG_PUB.Count_And_Get
179: p_data => x_msg_data
180: );
181:
182: WHEN OTHERS THEN
183: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
184: IF l_debug_level > 0 THEN
185: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
186: END IF;
187: IF OE_MSG_PUB.Check_Msg_Level
235: IF l_debug_level > 0 THEN
236: oe_debug_pub.add( 'IN RELEASE EXPIRED HOLDS' ) ;
237: oe_debug_pub.add( 'p_org_id:' || to_char(p_org_id) ) ;
238: END IF;
239: l_return_status := FND_API.G_RET_STS_SUCCESS;
240:
241: If p_org_id IS Not Null Then
242: -- Set Single Org access
243: MO_GLOBAL.SET_POLICY_CONTEXT('S', p_org_id);
282: IF l_debug_level > 0 THEN
283: oe_debug_pub.add( 'X_RETURN_STATUS:' || L_RETURN_STATUS , 1 ) ;
284: END IF;
285:
286: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
287: IF l_debug_level > 0 THEN
288: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS UNEXPECTED FAILURE' ) ;
289: END IF;
290: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
287: IF l_debug_level > 0 THEN
288: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS UNEXPECTED FAILURE' ) ;
289: END IF;
290: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
292: IF l_debug_level > 0 THEN
293: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS EXPECTED FAILURE' ) ;
294: END IF;
287: IF l_debug_level > 0 THEN
288: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS UNEXPECTED FAILURE' ) ;
289: END IF;
290: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
292: IF l_debug_level > 0 THEN
293: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS EXPECTED FAILURE' ) ;
294: END IF;
295: RAISE FND_API.G_EXC_ERROR;
291: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
292: IF l_debug_level > 0 THEN
293: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS EXPECTED FAILURE' ) ;
294: END IF;
295: RAISE FND_API.G_EXC_ERROR;
296: END IF;
297:
298: end loop;
299:
296: END IF;
297:
298: end loop;
299:
300: l_return_status := FND_API.G_RET_STS_SUCCESS;
301: -- Get message count and data
302: OE_MSG_PUB.Count_And_Get
303: ( p_count => l_msg_count
304: , p_data => l_msg_data
305: );
306:
307: EXCEPTION /* Procedure exception handler */
308:
309: WHEN FND_API.G_EXC_ERROR THEN
310: l_return_status := FND_API.G_RET_STS_ERROR ;
311: OE_MSG_PUB.Count_And_Get
312: ( p_count => l_msg_count,
313: p_data => l_msg_data
306:
307: EXCEPTION /* Procedure exception handler */
308:
309: WHEN FND_API.G_EXC_ERROR THEN
310: l_return_status := FND_API.G_RET_STS_ERROR ;
311: OE_MSG_PUB.Count_And_Get
312: ( p_count => l_msg_count,
313: p_data => l_msg_data
314: );
312: ( p_count => l_msg_count,
313: p_data => l_msg_data
314: );
315:
316: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
317: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
318: OE_MSG_PUB.Count_And_Get
319: ( p_count => l_msg_count,
320: p_data => l_msg_data
313: p_data => l_msg_data
314: );
315:
316: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
317: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
318: OE_MSG_PUB.Count_And_Get
319: ( p_count => l_msg_count,
320: p_data => l_msg_data
321: );
320: p_data => l_msg_data
321: );
322:
323: WHEN OTHERS THEN
324: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
325: IF OE_MSG_PUB.Check_Msg_Level
326: (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
327: THEN
328: OE_MSG_PUB.Add_Exc_Msg
360: oe_debug_pub.add( 'IN OE_HOLDS_PUB.VALIDATEORDER' ) ;
361: END IF;
362:
363: -- Initialize API return status to success
364: x_return_status := FND_API.G_RET_STS_SUCCESS;
365:
366: BEGIN
367:
368: IF p_line_id IS NOT NULL THEN
394: -- XXXXvalidation data based on Customer based holds -- Not needed at the line level
395: null;
396: ELSE
397: -- add error message
398: RAISE FND_API.G_EXC_ERROR;
399: END IF;
400:
401: -------------------------------
402: -- Check the Second entity --
428: -- XXXXvalidation data based on Customer based holds -- Not needed at the line level
429: null;
430: ELSE
431: -- add error message
432: RAISE FND_API.G_EXC_ERROR;
433: END IF;
434: ------------------------------
435:
436: ELSIF p_line_id IS NULL THEN
437:
438: IF p_hold_entity_code = 'O' THEN
439: -- XXX
440: IF (p_header_id <> p_hold_entity_id) THEN
441: RAISE FND_API.G_EXC_ERROR;
442: END IF;
443:
444: SELECT 'Valid Entity'
445: INTO l_dummy
465: WHERE HEADER_ID = p_header_id
466: AND SHIP_TO_ORG_ID = p_hold_entity_id
467: AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
468: ELSE
469: RAISE FND_API.G_EXC_ERROR;
470: END IF;
471: ------------------------------
472: -- Check the Second Entity --
473: ------------------------------
474: IF p_hold_entity_code2 is not null THEN
475: IF p_hold_entity_code2 = 'O' THEN
476:
477: IF (p_header_id <> p_hold_entity_id2) THEN
478: RAISE FND_API.G_EXC_ERROR;
479: END IF;
480:
481: SELECT 'Valid Entity'
482: INTO l_dummy
502: WHERE HEADER_ID = p_header_id
503: AND SHIP_TO_ORG_ID = p_hold_entity_id2
504: AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
505: ELSE
506: RAISE FND_API.G_EXC_ERROR;
507: END IF;
508: END IF;
509: ------------------------------
510: END IF;
510: END IF;
511:
512: EXCEPTION
513: WHEN NO_DATA_FOUND THEN
514: RAISE FND_API.G_EXC_ERROR;
515: END;
516:
517:
518: EXCEPTION
515: END;
516:
517:
518: EXCEPTION
519: WHEN FND_API.G_EXC_ERROR THEN
520: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTITY_NOT_ON_ORDER_OR_LINE');
521: OE_MSG_PUB.ADD;
522: IF l_debug_level > 0 THEN
523: oe_debug_pub.add( 'EXPECTED ERROR IN VALIDATEORDER' ) ;
521: OE_MSG_PUB.ADD;
522: IF l_debug_level > 0 THEN
523: oe_debug_pub.add( 'EXPECTED ERROR IN VALIDATEORDER' ) ;
524: END IF;
525: x_return_status := FND_API.G_RET_STS_ERROR;
526: WHEN OTHERS THEN
527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528: IF FND_MSG_PUB.Check_Msg_Level
529: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
523: oe_debug_pub.add( 'EXPECTED ERROR IN VALIDATEORDER' ) ;
524: END IF;
525: x_return_status := FND_API.G_RET_STS_ERROR;
526: WHEN OTHERS THEN
527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528: IF FND_MSG_PUB.Check_Msg_Level
529: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
530: THEN
531: FND_MSG_PUB.Add_Exc_Msg
544: ---------------------------------------------------------------
545:
546: Procedure Apply_Holds (
547: p_api_version IN NUMBER,
548: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
549: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
550: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
551: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
552: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
545:
546: Procedure Apply_Holds (
547: p_api_version IN NUMBER,
548: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
549: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
550: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
551: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
552: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
553: p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE DEFAULT NULL,
546: Procedure Apply_Holds (
547: p_api_version IN NUMBER,
548: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
549: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
550: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
551: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
552: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
553: p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE DEFAULT NULL,
554: p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE DEFAULT NULL,
570: l_org_id := MO_GLOBAL.get_current_org_id;
571: IF l_org_id IS NULL THEN
572: -- org_id is null, raise an error.
573: oe_debug_pub.add('Org_Id is NULL',1);
574: x_return_status := FND_API.G_RET_STS_ERROR;
575: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
576: FND_MSG_PUB.ADD;
577: RAISE FND_API.G_EXC_ERROR;
578: END IF;
573: oe_debug_pub.add('Org_Id is NULL',1);
574: x_return_status := FND_API.G_RET_STS_ERROR;
575: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
576: FND_MSG_PUB.ADD;
577: RAISE FND_API.G_EXC_ERROR;
578: END IF;
579:
580: -- Initialize API return status to success
581: x_return_status := FND_API.G_RET_STS_SUCCESS;
577: RAISE FND_API.G_EXC_ERROR;
578: END IF;
579:
580: -- Initialize API return status to success
581: x_return_status := FND_API.G_RET_STS_SUCCESS;
582:
583: oe_holds_pvt.apply_holds(
584: p_order_tbl => p_order_tbl,
585: p_hold_id => p_hold_id,
589: x_msg_count => x_msg_count,
590: x_msg_data => x_msg_data
591: );
592: EXCEPTION
593: WHEN FND_API.G_EXC_ERROR THEN
594: ROLLBACK TO APPLY_HOLDS_PUB;
595: IF l_debug_level > 0 THEN
596: oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
597: END IF;
594: ROLLBACK TO APPLY_HOLDS_PUB;
595: IF l_debug_level > 0 THEN
596: oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
597: END IF;
598: x_return_status := FND_API.G_RET_STS_ERROR;
599: FND_MSG_PUB.Count_And_Get
600: ( p_count => x_msg_count
601: , p_data => x_msg_data
602: );
599: FND_MSG_PUB.Count_And_Get
600: ( p_count => x_msg_count
601: , p_data => x_msg_data
602: );
603: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605: FND_MSG_PUB.Count_And_Get
606: ( p_count => x_msg_count
607: , p_data => x_msg_data
600: ( p_count => x_msg_count
601: , p_data => x_msg_data
602: );
603: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605: FND_MSG_PUB.Count_And_Get
606: ( p_count => x_msg_count
607: , p_data => x_msg_data
608: );
606: ( p_count => x_msg_count
607: , p_data => x_msg_data
608: );
609: WHEN OTHERS THEN
610: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611: IF FND_MSG_PUB.Check_Msg_Level
612: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
613: THEN
614: FND_MSG_PUB.Add_Exc_Msg
624: END Apply_Holds;
625:
626: Procedure Apply_Holds(
627: p_api_version IN NUMBER,
628: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
629: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
630: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
631: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
632: DEFAULT OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC,
625:
626: Procedure Apply_Holds(
627: p_api_version IN NUMBER,
628: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
629: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
630: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
631: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
632: DEFAULT OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC,
633: p_hold_existing_flg IN VARCHAR2 DEFAULT 'Y',
626: Procedure Apply_Holds(
627: p_api_version IN NUMBER,
628: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
629: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
630: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
631: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
632: DEFAULT OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC,
633: p_hold_existing_flg IN VARCHAR2 DEFAULT 'Y',
634: p_hold_future_flg IN VARCHAR2 DEFAULT 'Y',
649: l_org_id := MO_GLOBAL.get_current_org_id;
650: IF l_org_id IS NULL THEN
651: -- org_id is null, raise an error.
652: oe_debug_pub.add('Org_Id is NULL',1);
653: x_return_status := FND_API.G_RET_STS_ERROR;
654: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
655: FND_MSG_PUB.ADD;
656: RAISE FND_API.G_EXC_ERROR;
657: END IF;
652: oe_debug_pub.add('Org_Id is NULL',1);
653: x_return_status := FND_API.G_RET_STS_ERROR;
654: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
655: FND_MSG_PUB.ADD;
656: RAISE FND_API.G_EXC_ERROR;
657: END IF;
658:
659: -- Initialize API return status to success
660: x_return_status := FND_API.G_RET_STS_SUCCESS;
656: RAISE FND_API.G_EXC_ERROR;
657: END IF;
658:
659: -- Initialize API return status to success
660: x_return_status := FND_API.G_RET_STS_SUCCESS;
661:
662: --dbms_output.put_line ('IN PUB.ApplyHolds'); -- delete
663: oe_holds_pvt.apply_Holds(
664: p_hold_source_rec => p_hold_source_rec
672: oe_debug_pub.add( 'OE_HOLDS_PUB.APPLY_HOLDS , HOLD SOURCE:' ||X_RETURN_STATUS ) ;
673: END IF;
674:
675: EXCEPTION
676: WHEN FND_API.G_EXC_ERROR THEN
677: IF l_debug_level > 0 THEN
678: oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
679: END IF;
680: x_return_status := FND_API.G_RET_STS_ERROR;
676: WHEN FND_API.G_EXC_ERROR THEN
677: IF l_debug_level > 0 THEN
678: oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
679: END IF;
680: x_return_status := FND_API.G_RET_STS_ERROR;
681: FND_MSG_PUB.Count_And_Get
682: ( p_count => x_msg_count
683: , p_data => x_msg_data
684: );
681: FND_MSG_PUB.Count_And_Get
682: ( p_count => x_msg_count
683: , p_data => x_msg_data
684: );
685: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687: FND_MSG_PUB.Count_And_Get
688: ( p_count => x_msg_count
689: , p_data => x_msg_data
682: ( p_count => x_msg_count
683: , p_data => x_msg_data
684: );
685: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687: FND_MSG_PUB.Count_And_Get
688: ( p_count => x_msg_count
689: , p_data => x_msg_data
690: );
688: ( p_count => x_msg_count
689: , p_data => x_msg_data
690: );
691: WHEN OTHERS THEN
692: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693: IF FND_MSG_PUB.Check_Msg_Level
694: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
695: THEN
696: FND_MSG_PUB.Add_Exc_Msg
709: -- New Release Holds Spec --
710: --------------------------------
711: Procedure Release_Holds (
712: p_api_version IN NUMBER DEFAULT 1.0,
713: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
714: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
715: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
716: p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
717: p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
710: --------------------------------
711: Procedure Release_Holds (
712: p_api_version IN NUMBER DEFAULT 1.0,
713: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
714: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
715: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
716: p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
717: p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
718: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
711: Procedure Release_Holds (
712: p_api_version IN NUMBER DEFAULT 1.0,
713: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
714: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
715: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
716: p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
717: p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
718: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
719: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
732: l_org_id := MO_GLOBAL.get_current_org_id;
733: IF l_org_id IS NULL THEN
734: -- org_id is null, raise an error.
735: oe_debug_pub.add('Org_Id is NULL',1);
736: x_return_status := FND_API.G_RET_STS_ERROR;
737: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
738: FND_MSG_PUB.ADD;
739: RAISE FND_API.G_EXC_ERROR;
740: END IF;
735: oe_debug_pub.add('Org_Id is NULL',1);
736: x_return_status := FND_API.G_RET_STS_ERROR;
737: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
738: FND_MSG_PUB.ADD;
739: RAISE FND_API.G_EXC_ERROR;
740: END IF;
741:
742: -- Initialize API return status to success
743: x_return_status := FND_API.G_RET_STS_SUCCESS;
739: RAISE FND_API.G_EXC_ERROR;
740: END IF;
741:
742: -- Initialize API return status to success
743: x_return_status := FND_API.G_RET_STS_SUCCESS;
744:
745: -- dbms_output.put_line ('IN RELEASE_HOLDS->'); -- delete
746: oe_holds_pvt.Release_Holds(
747: p_hold_source_rec => p_hold_source_rec
750: ,x_msg_count => x_msg_count
751: ,x_msg_data => x_msg_data
752: );
753: EXCEPTION
754: WHEN FND_API.G_EXC_ERROR THEN
755: IF l_debug_level > 0 THEN
756: oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
757: END IF;
758: x_return_status := FND_API.G_RET_STS_ERROR;
754: WHEN FND_API.G_EXC_ERROR THEN
755: IF l_debug_level > 0 THEN
756: oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
757: END IF;
758: x_return_status := FND_API.G_RET_STS_ERROR;
759: FND_MSG_PUB.Count_And_Get
760: ( p_count => x_msg_count
761: , p_data => x_msg_data
762: );
759: FND_MSG_PUB.Count_And_Get
760: ( p_count => x_msg_count
761: , p_data => x_msg_data
762: );
763: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
764: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765: FND_MSG_PUB.Count_And_Get
766: ( p_count => x_msg_count
767: , p_data => x_msg_data
760: ( p_count => x_msg_count
761: , p_data => x_msg_data
762: );
763: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
764: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765: FND_MSG_PUB.Count_And_Get
766: ( p_count => x_msg_count
767: , p_data => x_msg_data
768: );
766: ( p_count => x_msg_count
767: , p_data => x_msg_data
768: );
769: WHEN OTHERS THEN
770: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771: IF FND_MSG_PUB.Check_Msg_Level
772: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
773: THEN
774: FND_MSG_PUB.Add_Exc_Msg
784: END Release_Holds;
785:
786: Procedure Release_Holds (
787: p_api_version IN NUMBER DEFAULT 1.0,
788: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
789: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
790: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
791: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
792: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
785:
786: Procedure Release_Holds (
787: p_api_version IN NUMBER DEFAULT 1.0,
788: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
789: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
790: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
791: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
792: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
793: DEFAULT NULL,
786: Procedure Release_Holds (
787: p_api_version IN NUMBER DEFAULT 1.0,
788: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
789: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
790: p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
791: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
792: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
793: DEFAULT NULL,
794: p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE,
815: l_org_id := MO_GLOBAL.get_current_org_id;
816: IF l_org_id IS NULL THEN
817: -- org_id is null, raise an error.
818: oe_debug_pub.add('Org_Id is NULL',1);
819: x_return_status := FND_API.G_RET_STS_ERROR;
820: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
821: FND_MSG_PUB.ADD;
822: RAISE FND_API.G_EXC_ERROR;
823: END IF;
818: oe_debug_pub.add('Org_Id is NULL',1);
819: x_return_status := FND_API.G_RET_STS_ERROR;
820: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
821: FND_MSG_PUB.ADD;
822: RAISE FND_API.G_EXC_ERROR;
823: END IF;
824:
825: -- Initialize API return status to success
826: x_return_status := FND_API.G_RET_STS_SUCCESS;
822: RAISE FND_API.G_EXC_ERROR;
823: END IF;
824:
825: -- Initialize API return status to success
826: x_return_status := FND_API.G_RET_STS_SUCCESS;
827:
828: for j in 1..p_order_tbl.COUNT loop
829: if p_order_tbl(j).header_id is NULL AND
830: p_order_tbl(j).line_id is NULL THEN
829: if p_order_tbl(j).header_id is NULL AND
830: p_order_tbl(j).line_id is NULL THEN
831: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
832: OE_MSG_PUB.ADD;
833: RAISE FND_API.G_EXC_ERROR;
834: ELSIF p_order_tbl(j).header_id is NULL THEN
835: SELECT header_id
836: INTO l_header_id
837: FROM OE_ORDER_LINES
854: x_msg_data => x_msg_data
855: );
856:
857: EXCEPTION
858: WHEN FND_API.G_EXC_ERROR THEN
859: IF l_debug_level > 0 THEN
860: oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
861: END IF;
862: x_return_status := FND_API.G_RET_STS_ERROR;
858: WHEN FND_API.G_EXC_ERROR THEN
859: IF l_debug_level > 0 THEN
860: oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
861: END IF;
862: x_return_status := FND_API.G_RET_STS_ERROR;
863: FND_MSG_PUB.Count_And_Get
864: ( p_count => x_msg_count
865: , p_data => x_msg_data
866: );
863: FND_MSG_PUB.Count_And_Get
864: ( p_count => x_msg_count
865: , p_data => x_msg_data
866: );
867: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869: FND_MSG_PUB.Count_And_Get
870: ( p_count => x_msg_count
871: , p_data => x_msg_data
864: ( p_count => x_msg_count
865: , p_data => x_msg_data
866: );
867: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869: FND_MSG_PUB.Count_And_Get
870: ( p_count => x_msg_count
871: , p_data => x_msg_data
872: );
870: ( p_count => x_msg_count
871: , p_data => x_msg_data
872: );
873: WHEN OTHERS THEN
874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875: IF FND_MSG_PUB.Check_Msg_Level
876: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
877: THEN
878: FND_MSG_PUB.Add_Exc_Msg
904: --------------------------------------------------------------------------
905:
906: PROCEDURE Apply_Holds
907: ( p_api_version IN NUMBER
908: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
909: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
910: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
911: , p_header_id IN NUMBER DEFAULT NULL
912: , p_line_id IN NUMBER DEFAULT NULL
905:
906: PROCEDURE Apply_Holds
907: ( p_api_version IN NUMBER
908: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
909: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
910: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
911: , p_header_id IN NUMBER DEFAULT NULL
912: , p_line_id IN NUMBER DEFAULT NULL
913: , p_hold_source_id IN NUMBER DEFAULT NULL
906: PROCEDURE Apply_Holds
907: ( p_api_version IN NUMBER
908: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
909: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
910: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
911: , p_header_id IN NUMBER DEFAULT NULL
912: , p_line_id IN NUMBER DEFAULT NULL
913: , p_hold_source_id IN NUMBER DEFAULT NULL
914: , p_hold_source_rec IN OE_Hold_Sources_Pvt.Hold_Source_REC
944: l_org_id := MO_GLOBAL.get_current_org_id;
945: IF l_org_id IS NULL THEN
946: -- org_id is null, raise an error.
947: oe_debug_pub.add('Org_Id is NULL',1);
948: x_return_status := FND_API.G_RET_STS_ERROR;
949: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
950: FND_MSG_PUB.ADD;
951: RAISE FND_API.G_EXC_ERROR;
952: END IF;
947: oe_debug_pub.add('Org_Id is NULL',1);
948: x_return_status := FND_API.G_RET_STS_ERROR;
949: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
950: FND_MSG_PUB.ADD;
951: RAISE FND_API.G_EXC_ERROR;
952: END IF;
953:
954: IF l_debug_level > 0 THEN
955: oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS OLD' ) ;
957:
958: SAVEPOINT APPLY_HOLDS_PUB;
959:
960: -- Initialize API return status to success
961: x_return_status := FND_API.G_RET_STS_SUCCESS;
962:
963: Utilities(l_user_id);
964:
965:
972: oe_debug_pub.add( 'USING INPUT HOLD SOURCE ID' ) ;
973: END IF;
974: l_hold_source_id := p_hold_source_id;
975:
976: --IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
977:
978: -- Check if the hold source ID is valid
979: BEGIN
980:
994: END IF;
995: FND_MESSAGE.SET_NAME('ONT','OE_INVALID_HOLD_SOURCE_ID');
996: FND_MESSAGE.SET_TOKEN('HOLD_SOURCE_ID' , p_hold_source_id);
997: OE_MSG_PUB.ADD;
998: RAISE FND_API.G_EXC_ERROR;
999: END;
1000:
1001: -- END IF;
1002:
1007: -- Check for Missing Values
1008: IF p_hold_source_rec.hold_id IS NULL THEN
1009: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_ID');
1010: OE_MSG_PUB.ADD;
1011: RAISE FND_API.G_EXC_ERROR;
1012: END IF;
1013:
1014: IF p_hold_source_rec.hold_entity_code IS NULL THEN
1015: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
1013:
1014: IF p_hold_source_rec.hold_entity_code IS NULL THEN
1015: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
1016: OE_MSG_PUB.ADD;
1017: RAISE FND_API.G_EXC_ERROR;
1018: END IF;
1019:
1020: IF p_hold_source_rec.hold_entity_id IS NULL THEN
1021: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
1019:
1020: IF p_hold_source_rec.hold_entity_id IS NULL THEN
1021: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
1022: OE_MSG_PUB.ADD;
1023: RAISE FND_API.G_EXC_ERROR;
1024: END IF;
1025: -- NOTE: No need to check for HOLD_ENTITY_CODE2 and HOLD_ENTITY_ID2
1026: -- cos, its optional.
1027: ----------------------------------
1043: IF p_hold_source_rec.hold_entity_code = 'O' THEN
1044: IF p_line_id IS NULL AND p_header_id IS NULL THEN
1045: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
1046: OE_MSG_PUB.ADD;
1047: RAISE FND_API.G_EXC_ERROR;
1048: ELSIF p_header_id IS NULL THEN
1049: SELECT header_id
1050: INTO l_header_id
1051: FROM OE_ORDER_LINES
1071: IF (sql%found) THEN
1072: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
1073: OE_MSG_PUB.ADD;
1074: OE_Debug_PUB.Add('Duplicate Hold');
1075: RAISE FND_API.G_EXC_ERROR;
1076: END IF;
1077: EXCEPTION
1078: WHEN NO_DATA_FOUND THEN
1079: null;
1090: , p_hold_entity_id2 => l_entity_id2
1091: , x_return_status => x_return_status
1092: );
1093:
1094: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1095: OE_Debug_PUB.Add('Validate Order not successful');
1096: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1097: RAISE FND_API.G_EXC_ERROR;
1098: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1092: );
1093:
1094: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1095: OE_Debug_PUB.Add('Validate Order not successful');
1096: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1097: RAISE FND_API.G_EXC_ERROR;
1098: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1099: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100: END IF;
1093:
1094: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1095: OE_Debug_PUB.Add('Validate Order not successful');
1096: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1097: RAISE FND_API.G_EXC_ERROR;
1098: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1099: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100: END IF;
1101: ELSE
1094: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1095: OE_Debug_PUB.Add('Validate Order not successful');
1096: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1097: RAISE FND_API.G_EXC_ERROR;
1098: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1099: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100: END IF;
1101: ELSE
1102: OE_Debug_PUB.Add('Validate Order successful');
1095: OE_Debug_PUB.Add('Validate Order not successful');
1096: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1097: RAISE FND_API.G_EXC_ERROR;
1098: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1099: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100: END IF;
1101: ELSE
1102: OE_Debug_PUB.Add('Validate Order successful');
1103: END IF;
1139: IF l_debug_level > 0 THEN
1140: oe_debug_pub.add( 'OE_HOLDS_PVT.APPLY_HOLDS STATUS:' || X_RETURN_STATUS ) ;
1141: END IF;
1142: --dbms_output.put_line('AH-x_return_status' || x_return_status ); -- delete
1143: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1144: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1145: RAISE FND_API.G_EXC_ERROR;
1146: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1140: oe_debug_pub.add( 'OE_HOLDS_PVT.APPLY_HOLDS STATUS:' || X_RETURN_STATUS ) ;
1141: END IF;
1142: --dbms_output.put_line('AH-x_return_status' || x_return_status ); -- delete
1143: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1144: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1145: RAISE FND_API.G_EXC_ERROR;
1146: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148: END IF;
1141: END IF;
1142: --dbms_output.put_line('AH-x_return_status' || x_return_status ); -- delete
1143: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1144: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1145: RAISE FND_API.G_EXC_ERROR;
1146: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148: END IF;
1149: END IF;
1142: --dbms_output.put_line('AH-x_return_status' || x_return_status ); -- delete
1143: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1144: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1145: RAISE FND_API.G_EXC_ERROR;
1146: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148: END IF;
1149: END IF;
1150:
1143: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1144: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1145: RAISE FND_API.G_EXC_ERROR;
1146: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148: END IF;
1149: END IF;
1150:
1151: EXCEPTION
1148: END IF;
1149: END IF;
1150:
1151: EXCEPTION
1152: WHEN FND_API.G_EXC_ERROR THEN
1153: ROLLBACK TO APPLY_HOLDS_PUB;
1154: IF l_debug_level > 0 THEN
1155: oe_debug_pub.add( 'FROM DUPLICATE HOLD EXPECTED ERROR' ) ;
1156: END IF;
1153: ROLLBACK TO APPLY_HOLDS_PUB;
1154: IF l_debug_level > 0 THEN
1155: oe_debug_pub.add( 'FROM DUPLICATE HOLD EXPECTED ERROR' ) ;
1156: END IF;
1157: x_return_status := FND_API.G_RET_STS_ERROR;
1158: FND_MSG_PUB.Count_And_Get
1159: ( p_count => x_msg_count
1160: , p_data => x_msg_data
1161: );
1158: FND_MSG_PUB.Count_And_Get
1159: ( p_count => x_msg_count
1160: , p_data => x_msg_data
1161: );
1162: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1163: ROLLBACK TO APPLY_HOLDS_PUB;
1164: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165: FND_MSG_PUB.Count_And_Get
1166: ( p_count => x_msg_count
1160: , p_data => x_msg_data
1161: );
1162: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1163: ROLLBACK TO APPLY_HOLDS_PUB;
1164: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165: FND_MSG_PUB.Count_And_Get
1166: ( p_count => x_msg_count
1167: , p_data => x_msg_data
1168: );
1167: , p_data => x_msg_data
1168: );
1169: WHEN OTHERS THEN
1170: ROLLBACK TO APPLY_HOLDS_PUB;
1171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1172: IF FND_MSG_PUB.Check_Msg_Level
1173: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1174: THEN
1175: FND_MSG_PUB.Add_Exc_Msg
1219: IF l_debug_level > 0 THEN
1220: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_LINE:' || P_LINE_ID ) ;
1221: END IF;
1222: -- Initialize API return status to success
1223: x_return_status := FND_API.G_RET_STS_SUCCESS;
1224:
1225: -- Initialize result to TRUE i.e. holds are found
1226: x_result_out := FND_API.G_TRUE;
1227:
1222: -- Initialize API return status to success
1223: x_return_status := FND_API.G_RET_STS_SUCCESS;
1224:
1225: -- Initialize result to TRUE i.e. holds are found
1226: x_result_out := FND_API.G_TRUE;
1227:
1228: /*
1229: ** Fix Bug # 1920064
1230: ** The SQL below was modified. To improve the performance, the scan on
1278: nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1279:
1280: EXCEPTION
1281: WHEN NO_DATA_FOUND THEN
1282: x_result_out := FND_API.G_FALSE;
1283: IF l_debug_level > 0 THEN
1284: oe_debug_pub.add( 'NO HOLDS FOUND FOR LINE ID: ' || P_LINE_ID ) ;
1285: END IF;
1286: WHEN TOO_MANY_ROWS THEN
1291: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_LINE' ) ;
1292: END IF;
1293:
1294: EXCEPTION
1295: WHEN FND_API.G_EXC_ERROR THEN
1296: x_return_status := FND_API.G_RET_STS_ERROR;
1297: x_result_out := FND_API.G_FALSE;
1298: FND_MSG_PUB.Count_And_Get
1299: ( p_count => x_msg_count
1292: END IF;
1293:
1294: EXCEPTION
1295: WHEN FND_API.G_EXC_ERROR THEN
1296: x_return_status := FND_API.G_RET_STS_ERROR;
1297: x_result_out := FND_API.G_FALSE;
1298: FND_MSG_PUB.Count_And_Get
1299: ( p_count => x_msg_count
1300: , p_data => x_msg_data
1293:
1294: EXCEPTION
1295: WHEN FND_API.G_EXC_ERROR THEN
1296: x_return_status := FND_API.G_RET_STS_ERROR;
1297: x_result_out := FND_API.G_FALSE;
1298: FND_MSG_PUB.Count_And_Get
1299: ( p_count => x_msg_count
1300: , p_data => x_msg_data
1301: );
1298: FND_MSG_PUB.Count_And_Get
1299: ( p_count => x_msg_count
1300: , p_data => x_msg_data
1301: );
1302: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1303: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1304: x_result_out := FND_API.G_FALSE;
1305: FND_MSG_PUB.Count_And_Get
1306: ( p_count => x_msg_count
1299: ( p_count => x_msg_count
1300: , p_data => x_msg_data
1301: );
1302: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1303: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1304: x_result_out := FND_API.G_FALSE;
1305: FND_MSG_PUB.Count_And_Get
1306: ( p_count => x_msg_count
1307: , p_data => x_msg_data
1300: , p_data => x_msg_data
1301: );
1302: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1303: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1304: x_result_out := FND_API.G_FALSE;
1305: FND_MSG_PUB.Count_And_Get
1306: ( p_count => x_msg_count
1307: , p_data => x_msg_data
1308: );
1306: ( p_count => x_msg_count
1307: , p_data => x_msg_data
1308: );
1309: WHEN OTHERS THEN
1310: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1311: x_result_out := FND_API.G_FALSE;
1312: IF FND_MSG_PUB.Check_Msg_Level
1313: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1314: THEN
1307: , p_data => x_msg_data
1308: );
1309: WHEN OTHERS THEN
1310: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1311: x_result_out := FND_API.G_FALSE;
1312: IF FND_MSG_PUB.Check_Msg_Level
1313: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1314: THEN
1315: FND_MSG_PUB.Add_Exc_Msg
1356: IF l_debug_level > 0 THEN
1357: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1358: END IF;
1359: -- Initialize API return status to success
1360: x_return_status := FND_API.G_RET_STS_SUCCESS;
1361:
1362: -- Initialize result to TRUE i.e. holds are found
1363: x_result_out := FND_API.G_TRUE;
1364:
1359: -- Initialize API return status to success
1360: x_return_status := FND_API.G_RET_STS_SUCCESS;
1361:
1362: -- Initialize result to TRUE i.e. holds are found
1363: x_result_out := FND_API.G_TRUE;
1364:
1365: /*
1366: ** Checking for ATO level generic and activity-specific holds
1367: */
1405: nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1406:
1407: EXCEPTION
1408: WHEN NO_DATA_FOUND THEN
1409: x_result_out := FND_API.G_FALSE;
1410: IF l_debug_level > 0 THEN
1411: oe_debug_pub.add( 'NO HOLDS FOUND FOR ATO LINE ID: ' || P_ATO_LINE_ID ) ;
1412: END IF;
1413: WHEN TOO_MANY_ROWS THEN
1418: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1419: END IF;
1420:
1421: EXCEPTION
1422: WHEN FND_API.G_EXC_ERROR THEN
1423: x_return_status := FND_API.G_RET_STS_ERROR;
1424: x_result_out := FND_API.G_FALSE;
1425: FND_MSG_PUB.Count_And_Get
1426: ( p_count => x_msg_count
1419: END IF;
1420:
1421: EXCEPTION
1422: WHEN FND_API.G_EXC_ERROR THEN
1423: x_return_status := FND_API.G_RET_STS_ERROR;
1424: x_result_out := FND_API.G_FALSE;
1425: FND_MSG_PUB.Count_And_Get
1426: ( p_count => x_msg_count
1427: , p_data => x_msg_data
1420:
1421: EXCEPTION
1422: WHEN FND_API.G_EXC_ERROR THEN
1423: x_return_status := FND_API.G_RET_STS_ERROR;
1424: x_result_out := FND_API.G_FALSE;
1425: FND_MSG_PUB.Count_And_Get
1426: ( p_count => x_msg_count
1427: , p_data => x_msg_data
1428: );
1425: FND_MSG_PUB.Count_And_Get
1426: ( p_count => x_msg_count
1427: , p_data => x_msg_data
1428: );
1429: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1430: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431: x_result_out := FND_API.G_FALSE;
1432: FND_MSG_PUB.Count_And_Get
1433: ( p_count => x_msg_count
1426: ( p_count => x_msg_count
1427: , p_data => x_msg_data
1428: );
1429: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1430: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431: x_result_out := FND_API.G_FALSE;
1432: FND_MSG_PUB.Count_And_Get
1433: ( p_count => x_msg_count
1434: , p_data => x_msg_data
1427: , p_data => x_msg_data
1428: );
1429: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1430: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431: x_result_out := FND_API.G_FALSE;
1432: FND_MSG_PUB.Count_And_Get
1433: ( p_count => x_msg_count
1434: , p_data => x_msg_data
1435: );
1433: ( p_count => x_msg_count
1434: , p_data => x_msg_data
1435: );
1436: WHEN OTHERS THEN
1437: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438: x_result_out := FND_API.G_FALSE;
1439: IF FND_MSG_PUB.Check_Msg_Level
1440: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1441: THEN
1434: , p_data => x_msg_data
1435: );
1436: WHEN OTHERS THEN
1437: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438: x_result_out := FND_API.G_FALSE;
1439: IF FND_MSG_PUB.Check_Msg_Level
1440: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1441: THEN
1442: FND_MSG_PUB.Add_Exc_Msg
1483: IF l_debug_level > 0 THEN
1484: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1485: END IF;
1486: -- Initialize API return status to success
1487: x_return_status := FND_API.G_RET_STS_SUCCESS;
1488:
1489: -- Initialize result to TRUE i.e. holds are found
1490: x_result_out := FND_API.G_TRUE;
1491:
1486: -- Initialize API return status to success
1487: x_return_status := FND_API.G_RET_STS_SUCCESS;
1488:
1489: -- Initialize result to TRUE i.e. holds are found
1490: x_result_out := FND_API.G_TRUE;
1491:
1492: /*
1493: ** Checking for SMC level generic and activity-specific holds
1494: */
1531: nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1532:
1533: EXCEPTION
1534: WHEN NO_DATA_FOUND THEN
1535: x_result_out := FND_API.G_FALSE;
1536: IF l_debug_level > 0 THEN
1537: oe_debug_pub.add( 'NO HOLDS FOUND FOR TOP MODEL LINE ID: ' || P_TOP_MODEL_LINE_ID ) ;
1538: END IF;
1539: WHEN TOO_MANY_ROWS THEN
1544: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1545: END IF;
1546:
1547: EXCEPTION
1548: WHEN FND_API.G_EXC_ERROR THEN
1549: x_return_status := FND_API.G_RET_STS_ERROR;
1550: x_result_out := FND_API.G_FALSE;
1551: FND_MSG_PUB.Count_And_Get
1552: ( p_count => x_msg_count
1545: END IF;
1546:
1547: EXCEPTION
1548: WHEN FND_API.G_EXC_ERROR THEN
1549: x_return_status := FND_API.G_RET_STS_ERROR;
1550: x_result_out := FND_API.G_FALSE;
1551: FND_MSG_PUB.Count_And_Get
1552: ( p_count => x_msg_count
1553: , p_data => x_msg_data
1546:
1547: EXCEPTION
1548: WHEN FND_API.G_EXC_ERROR THEN
1549: x_return_status := FND_API.G_RET_STS_ERROR;
1550: x_result_out := FND_API.G_FALSE;
1551: FND_MSG_PUB.Count_And_Get
1552: ( p_count => x_msg_count
1553: , p_data => x_msg_data
1554: );
1551: FND_MSG_PUB.Count_And_Get
1552: ( p_count => x_msg_count
1553: , p_data => x_msg_data
1554: );
1555: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557: x_result_out := FND_API.G_FALSE;
1558: FND_MSG_PUB.Count_And_Get
1559: ( p_count => x_msg_count
1552: ( p_count => x_msg_count
1553: , p_data => x_msg_data
1554: );
1555: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557: x_result_out := FND_API.G_FALSE;
1558: FND_MSG_PUB.Count_And_Get
1559: ( p_count => x_msg_count
1560: , p_data => x_msg_data
1553: , p_data => x_msg_data
1554: );
1555: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557: x_result_out := FND_API.G_FALSE;
1558: FND_MSG_PUB.Count_And_Get
1559: ( p_count => x_msg_count
1560: , p_data => x_msg_data
1561: );
1559: ( p_count => x_msg_count
1560: , p_data => x_msg_data
1561: );
1562: WHEN OTHERS THEN
1563: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564: x_result_out := FND_API.G_FALSE;
1565: IF FND_MSG_PUB.Check_Msg_Level
1566: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1567: THEN
1560: , p_data => x_msg_data
1561: );
1562: WHEN OTHERS THEN
1563: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564: x_result_out := FND_API.G_FALSE;
1565: IF FND_MSG_PUB.Check_Msg_Level
1566: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1567: THEN
1568: FND_MSG_PUB.Add_Exc_Msg
1595: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1596: --
1597: BEGIN
1598: -- Initialize API return status to success
1599: x_return_status := FND_API.G_RET_STS_SUCCESS;
1600:
1601: -- Initialize result to TRUE i.e. holds are found
1602: x_hold_rec.x_result_out := FND_API.G_TRUE;
1603:
1598: -- Initialize API return status to success
1599: x_return_status := FND_API.G_RET_STS_SUCCESS;
1600:
1601: -- Initialize result to TRUE i.e. holds are found
1602: x_hold_rec.x_result_out := FND_API.G_TRUE;
1603:
1604: BEGIN
1605:
1606: SELECT 'ANY_LINE_HOLD'
1638: AND NVL(hs.hold_entity_id2, -99) =
1639: NVL(x_hold_rec.hold_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1640: EXCEPTION
1641: WHEN NO_DATA_FOUND THEN
1642: x_hold_rec.x_result_out := FND_API.G_FALSE;
1643: IF l_debug_level > 0 THEN
1644: oe_debug_pub.add( 'NO HOLDS FOUND FOR ANY OF THE LINES' ) ;
1645: END IF;
1646: WHEN TOO_MANY_ROWS THEN
1648: END;
1649:
1650:
1651: EXCEPTION
1652: WHEN FND_API.G_EXC_ERROR THEN
1653: x_return_status := FND_API.G_RET_STS_ERROR;
1654: x_hold_rec.x_result_out := FND_API.G_FALSE;
1655: FND_MSG_PUB.Count_And_Get
1656: ( p_count => x_msg_count
1649:
1650:
1651: EXCEPTION
1652: WHEN FND_API.G_EXC_ERROR THEN
1653: x_return_status := FND_API.G_RET_STS_ERROR;
1654: x_hold_rec.x_result_out := FND_API.G_FALSE;
1655: FND_MSG_PUB.Count_And_Get
1656: ( p_count => x_msg_count
1657: , p_data => x_msg_data
1650:
1651: EXCEPTION
1652: WHEN FND_API.G_EXC_ERROR THEN
1653: x_return_status := FND_API.G_RET_STS_ERROR;
1654: x_hold_rec.x_result_out := FND_API.G_FALSE;
1655: FND_MSG_PUB.Count_And_Get
1656: ( p_count => x_msg_count
1657: , p_data => x_msg_data
1658: );
1655: FND_MSG_PUB.Count_And_Get
1656: ( p_count => x_msg_count
1657: , p_data => x_msg_data
1658: );
1659: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1661: x_hold_rec.x_result_out := FND_API.G_FALSE;
1662: FND_MSG_PUB.Count_And_Get
1663: ( p_count => x_msg_count
1656: ( p_count => x_msg_count
1657: , p_data => x_msg_data
1658: );
1659: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1661: x_hold_rec.x_result_out := FND_API.G_FALSE;
1662: FND_MSG_PUB.Count_And_Get
1663: ( p_count => x_msg_count
1664: , p_data => x_msg_data
1657: , p_data => x_msg_data
1658: );
1659: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1661: x_hold_rec.x_result_out := FND_API.G_FALSE;
1662: FND_MSG_PUB.Count_And_Get
1663: ( p_count => x_msg_count
1664: , p_data => x_msg_data
1665: );
1663: ( p_count => x_msg_count
1664: , p_data => x_msg_data
1665: );
1666: WHEN OTHERS THEN
1667: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1668: x_hold_rec.x_result_out := FND_API.G_FALSE;
1669: IF FND_MSG_PUB.Check_Msg_Level
1670: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1671: THEN
1664: , p_data => x_msg_data
1665: );
1666: WHEN OTHERS THEN
1667: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1668: x_hold_rec.x_result_out := FND_API.G_FALSE;
1669: IF FND_MSG_PUB.Check_Msg_Level
1670: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1671: THEN
1672: FND_MSG_PUB.Add_Exc_Msg
1693: -- if included item flag is set appropriately in the hold definition.
1694: ------------------------------------------------------------------------
1695: PROCEDURE Check_Holds
1696: ( p_api_version IN NUMBER
1697: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1698: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1699: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1700: , p_header_id IN NUMBER DEFAULT NULL
1701: , p_line_id IN NUMBER DEFAULT NULL
1694: ------------------------------------------------------------------------
1695: PROCEDURE Check_Holds
1696: ( p_api_version IN NUMBER
1697: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1698: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1699: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1700: , p_header_id IN NUMBER DEFAULT NULL
1701: , p_line_id IN NUMBER DEFAULT NULL
1702: , p_hold_id IN NUMBER DEFAULT NULL
1695: PROCEDURE Check_Holds
1696: ( p_api_version IN NUMBER
1697: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1698: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1699: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1700: , p_header_id IN NUMBER DEFAULT NULL
1701: , p_line_id IN NUMBER DEFAULT NULL
1702: , p_hold_id IN NUMBER DEFAULT NULL
1703: , p_wf_item IN VARCHAR2 DEFAULT NULL
1752: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS' ) ;
1753: END IF;
1754:
1755: -- Initialize API return status to success
1756: x_return_status := FND_API.G_RET_STS_SUCCESS;
1757:
1758: -- Initialize result to TRUE i.e. holds are found
1759: x_result_out := FND_API.G_TRUE;
1760:
1755: -- Initialize API return status to success
1756: x_return_status := FND_API.G_RET_STS_SUCCESS;
1757:
1758: -- Initialize result to TRUE i.e. holds are found
1759: x_result_out := FND_API.G_TRUE;
1760:
1761: -- Check for Missing Input Parameters
1762: IF p_header_id IS NULL AND p_line_id IS NULL THEN
1763:
1762: IF p_header_id IS NULL AND p_line_id IS NULL THEN
1763:
1764: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
1765: OE_MSG_PUB.ADD;
1766: RAISE FND_API.G_EXC_ERROR;
1767:
1768: END IF;
1769:
1770: /*
1782: FROM oe_order_lines_all
1783: WHERE line_id = p_line_id;
1784: EXCEPTION
1785: WHEN NO_DATA_FOUND THEN
1786: x_result_out := FND_API.G_FALSE;
1787: IF l_debug_level > 0 THEN
1788: oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 1: ' || P_LINE_ID ) ;
1789: END IF;
1790: End;
1805: FROM oe_order_headers_all
1806: WHERE header_id=p_hdr_id;
1807: EXCEPTION
1808: WHEN NO_DATA_FOUND THEN
1809: x_result_out := FND_API.G_FALSE;
1810: END;
1811: END IF;
1812:
1813: l_cascade_hold_non_smc := NVL(OE_SYS_PARAMETERS.VALUE('ONT_CASCADE_HOLD_NONSMC_PTO',l_org_id),'N');
1853: nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1854:
1855: EXCEPTION
1856: WHEN NO_DATA_FOUND THEN
1857: x_result_out := FND_API.G_FALSE;
1858: IF l_debug_level > 0 THEN
1859: oe_debug_pub.add( 'NO HOLDS FOUND FOR HEADER ID: ' || P_HDR_ID ) ;
1860: END IF;
1861: WHEN TOO_MANY_ROWS THEN
1862: null;
1863: END;
1864:
1865: -- Return TRUE if Header Level Hold exists
1866: IF x_result_out = FND_API.G_TRUE THEN
1867: IF l_debug_level > 0 THEN
1868: oe_debug_pub.add( 'HEADER LEVEL HOLD EXISTS' ) ;
1869: END IF;
1870: RETURN;
1890: ,x_msg_data => l_msg_data
1891: );
1892:
1893: -- Raise if the l_return_status is unexpected error
1894: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1895: IF l_debug_level > 0 THEN
1896: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
1897: END IF;
1898: RAISE FND_API.G_EXC_ERROR;
1894: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1895: IF l_debug_level > 0 THEN
1896: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
1897: END IF;
1898: RAISE FND_API.G_EXC_ERROR;
1899: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1900: THEN
1901: IF l_debug_level > 0 THEN
1902: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
1895: IF l_debug_level > 0 THEN
1896: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
1897: END IF;
1898: RAISE FND_API.G_EXC_ERROR;
1899: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1900: THEN
1901: IF l_debug_level > 0 THEN
1902: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
1903: END IF;
1900: THEN
1901: IF l_debug_level > 0 THEN
1902: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
1903: END IF;
1904: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1905: END IF;
1906:
1907: /* Proceed only if there is no hold on the line */
1908: IF x_result_out = FND_API.G_FALSE THEN
1904: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1905: END IF;
1906:
1907: /* Proceed only if there is no hold on the line */
1908: IF x_result_out = FND_API.G_FALSE THEN
1909:
1910: /* Check to see if the line is a part of ATO model, SMC, etc. */
1911: BEGIN
1912: IF l_debug_level > 0 THEN
1920: FROM oe_order_lines_all
1921: WHERE line_id = p_line_id;
1922: EXCEPTION
1923: WHEN NO_DATA_FOUND THEN
1924: x_result_out := FND_API.G_FALSE;
1925: IF l_debug_level > 0 THEN
1926: oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 2: ' || P_LINE_ID ) ;
1927: END IF;
1928: END;
1939: END IF;
1940: END IF;
1941:
1942: /* If Line is part of ATO Model */
1943: IF l_ato_line_id is NOT NULL AND x_result_out = FND_API.G_FALSE AND
1944: NOT (l_ato_line_id = p_line_id AND l_item_type_code = OE_GLOBALS.G_ITEM_OPTION) THEN
1945:
1946: IF l_debug_level > 0 THEN
1947: oe_debug_pub.add( 'CHECK_HOLDS:ATO MODEL LINE: ' || L_ATO_LINE_ID ) ;
1950: /*
1951: ** Fix Bug # 2984023
1952: ** Following replaced by a single call to Check_Holds_ATO
1953:
1954: x_result_out := FND_API.G_FALSE;
1955:
1956: OPEN ato_model_lines;
1957: loop
1958: FETCH ato_model_lines into l_line_id;
1956: OPEN ato_model_lines;
1957: loop
1958: FETCH ato_model_lines into l_line_id;
1959: exit when ato_model_lines%NOTFOUND OR
1960: (x_result_out = FND_API.G_TRUE);
1961: IF l_debug_level > 0 THEN
1962: oe_debug_pub.add( 'CHECK_HOLDS:CHECKING LINEID' || L_LINE_ID ) ;
1963: END IF;
1964:
1997: ,x_msg_count => l_msg_count
1998: ,x_msg_data => l_msg_data
1999: );
2000: -- Raise if the l_return_status is unexpected error
2001: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2002: IF l_debug_level > 0 THEN
2003: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_ERROR') ;
2004: END IF;
2005: RAISE FND_API.G_EXC_ERROR;
2001: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2002: IF l_debug_level > 0 THEN
2003: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_ERROR') ;
2004: END IF;
2005: RAISE FND_API.G_EXC_ERROR;
2006: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2007: IF l_debug_level > 0 THEN
2008: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_UNEXP_ERROR') ;
2009: END IF;
2002: IF l_debug_level > 0 THEN
2003: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_ERROR') ;
2004: END IF;
2005: RAISE FND_API.G_EXC_ERROR;
2006: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2007: IF l_debug_level > 0 THEN
2008: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_UNEXP_ERROR') ;
2009: END IF;
2010: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2006: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2007: IF l_debug_level > 0 THEN
2008: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_UNEXP_ERROR') ;
2009: END IF;
2010: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2011: END IF;
2012:
2013: END IF; /* l_ato_line_id is NOT NULL */
2014:
2011: END IF;
2012:
2013: END IF; /* l_ato_line_id is NOT NULL */
2014:
2015: IF NVL(l_smc_flag, 'N') = 'Y' AND x_result_out = FND_API.G_FALSE THEN
2016:
2017: IF l_debug_level > 0 THEN
2018: oe_debug_pub.add( 'CHECK_HOLDS:TOP MODEL LINE ID: ' || L_TOP_MODEL_LINE_ID ) ;
2019: END IF;
2025: OPEN smc_lines;
2026: loop
2027:
2028: FETCH smc_lines into l_line_id;
2029: exit when smc_lines%NOTFOUND OR (x_result_out = FND_API.G_TRUE);
2030:
2031: IF l_debug_level > 0 THEN
2032: oe_debug_pub.add( 'CHECK_HOLDS: CHECKING SMC LINEID' || L_LINE_ID ) ;
2033: END IF;
2067: ,x_msg_count => l_msg_count
2068: ,x_msg_data => l_msg_data
2069: );
2070: -- Raise if the l_return_status is unexpected error
2071: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2072: IF l_debug_level > 0 THEN
2073: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_ERROR') ;
2074: END IF;
2075: RAISE FND_API.G_EXC_ERROR;
2071: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2072: IF l_debug_level > 0 THEN
2073: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_ERROR') ;
2074: END IF;
2075: RAISE FND_API.G_EXC_ERROR;
2076: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2077: IF l_debug_level > 0 THEN
2078: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_UNEXP_ERROR') ;
2079: END IF;
2072: IF l_debug_level > 0 THEN
2073: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_ERROR') ;
2074: END IF;
2075: RAISE FND_API.G_EXC_ERROR;
2076: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2077: IF l_debug_level > 0 THEN
2078: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_UNEXP_ERROR') ;
2079: END IF;
2080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2076: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2077: IF l_debug_level > 0 THEN
2078: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_UNEXP_ERROR') ;
2079: END IF;
2080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2081: END IF;
2082:
2083: END IF; /* l_smc_flag = 'Y' */
2084: --5737464
2081: END IF;
2082:
2083: END IF; /* l_smc_flag = 'Y' */
2084: --5737464
2085: IF NVL(l_smc_flag, 'N') = 'N' AND x_result_out = FND_API.G_FALSE THEN
2086: IF l_debug_level > 0 THEN
2087: oe_debug_pub.add( 'CHECK_HOLDS: CHECKING FOR CONFIG VALIDATION HOLD' || L_TOP_MODEL_LINE_ID ) ;
2088: END IF;
2089: IF l_cascade_hold_non_smc <> 'Y' THEN --ER#7479609
2126:
2127: END IF;--NON SMC Config Validation Hold
2128: --5737464
2129:
2130: IF l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND x_result_out = FND_API.G_FALSE THEN
2131:
2132: IF l_debug_level > 0 THEN
2133: oe_debug_pub.add( 'CHECK_HOLDS: CHECKING HOLD ON LINK TO LINE ID: ' || L_LINK_TO_LINE_ID ) ;
2134: END IF;
2150: ,x_msg_count => l_msg_count
2151: ,x_msg_data => l_msg_data
2152: );
2153: -- Raise if the l_return_status is unexpected error
2154: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2155: IF l_debug_level > 0 THEN
2156: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
2157: END IF;
2158: RAISE FND_API.G_EXC_ERROR;
2154: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2155: IF l_debug_level > 0 THEN
2156: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
2157: END IF;
2158: RAISE FND_API.G_EXC_ERROR;
2159: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2160: IF l_debug_level > 0 THEN
2161: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
2162: END IF;
2155: IF l_debug_level > 0 THEN
2156: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
2157: END IF;
2158: RAISE FND_API.G_EXC_ERROR;
2159: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2160: IF l_debug_level > 0 THEN
2161: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
2162: END IF;
2163: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2159: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2160: IF l_debug_level > 0 THEN
2161: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
2162: END IF;
2163: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2164: END IF;
2165:
2166: END IF; /* l_item_type_code = 'INCLUDED' */
2167:
2173: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS' ) ;
2174: END IF;
2175:
2176: EXCEPTION
2177: WHEN FND_API.G_EXC_ERROR THEN
2178: x_return_status := FND_API.G_RET_STS_ERROR;
2179: x_result_out := FND_API.G_FALSE;
2180: FND_MSG_PUB.Count_And_Get
2181: ( p_count => x_msg_count
2174: END IF;
2175:
2176: EXCEPTION
2177: WHEN FND_API.G_EXC_ERROR THEN
2178: x_return_status := FND_API.G_RET_STS_ERROR;
2179: x_result_out := FND_API.G_FALSE;
2180: FND_MSG_PUB.Count_And_Get
2181: ( p_count => x_msg_count
2182: , p_data => x_msg_data
2175:
2176: EXCEPTION
2177: WHEN FND_API.G_EXC_ERROR THEN
2178: x_return_status := FND_API.G_RET_STS_ERROR;
2179: x_result_out := FND_API.G_FALSE;
2180: FND_MSG_PUB.Count_And_Get
2181: ( p_count => x_msg_count
2182: , p_data => x_msg_data
2183: );
2180: FND_MSG_PUB.Count_And_Get
2181: ( p_count => x_msg_count
2182: , p_data => x_msg_data
2183: );
2184: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2185: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2186: x_result_out := FND_API.G_FALSE;
2187: FND_MSG_PUB.Count_And_Get
2188: ( p_count => x_msg_count
2181: ( p_count => x_msg_count
2182: , p_data => x_msg_data
2183: );
2184: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2185: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2186: x_result_out := FND_API.G_FALSE;
2187: FND_MSG_PUB.Count_And_Get
2188: ( p_count => x_msg_count
2189: , p_data => x_msg_data
2182: , p_data => x_msg_data
2183: );
2184: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2185: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2186: x_result_out := FND_API.G_FALSE;
2187: FND_MSG_PUB.Count_And_Get
2188: ( p_count => x_msg_count
2189: , p_data => x_msg_data
2190: );
2188: ( p_count => x_msg_count
2189: , p_data => x_msg_data
2190: );
2191: WHEN OTHERS THEN
2192: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193: x_result_out := FND_API.G_FALSE;
2194: IF FND_MSG_PUB.Check_Msg_Level
2195: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2196: THEN
2189: , p_data => x_msg_data
2190: );
2191: WHEN OTHERS THEN
2192: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193: x_result_out := FND_API.G_FALSE;
2194: IF FND_MSG_PUB.Check_Msg_Level
2195: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2196: THEN
2197: FND_MSG_PUB.Add_Exc_Msg
2213: * Expects at least the hold_entity_code or hold_entity_id *
2214: ******************************************************************/
2215: PROCEDURE Check_Hold_Sources
2216: ( p_api_version IN NUMBER
2217: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2218: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2219: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2220: , p_hold_id IN NUMBER DEFAULT NULL
2221: , p_wf_item IN VARCHAR2 DEFAULT NULL
2214: ******************************************************************/
2215: PROCEDURE Check_Hold_Sources
2216: ( p_api_version IN NUMBER
2217: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2218: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2219: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2220: , p_hold_id IN NUMBER DEFAULT NULL
2221: , p_wf_item IN VARCHAR2 DEFAULT NULL
2222: , p_wf_activity IN VARCHAR2 DEFAULT NULL
2215: PROCEDURE Check_Hold_Sources
2216: ( p_api_version IN NUMBER
2217: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2218: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2219: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2220: , p_hold_id IN NUMBER DEFAULT NULL
2221: , p_wf_item IN VARCHAR2 DEFAULT NULL
2222: , p_wf_activity IN VARCHAR2 DEFAULT NULL
2223: , p_hold_entity_code IN VARCHAR2 DEFAULT NULL
2248: IF l_debug_level > 0 THEN
2249: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLD_SOURCES' ) ;
2250: END IF;
2251: -- Initialize API return status to success
2252: x_return_status := FND_API.G_RET_STS_SUCCESS;
2253:
2254: -- Initialize result to TRUE i.e. holds are found
2255: x_result_out := FND_API.G_TRUE;
2256:
2251: -- Initialize API return status to success
2252: x_return_status := FND_API.G_RET_STS_SUCCESS;
2253:
2254: -- Initialize result to TRUE i.e. holds are found
2255: x_result_out := FND_API.G_TRUE;
2256:
2257:
2258: -- Check for Missing Input Parameters
2259: IF p_hold_entity_code IS NULL AND p_hold_entity_id IS NULL THEN
2262: END IF;
2263: /* TO_DO: Seed a more meaningfull message */
2264: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
2265: OE_MSG_PUB.ADD;
2266: RAISE FND_API.G_EXC_ERROR;
2267:
2268: END IF;
2269:
2270: /******************************/
2300: nvl(p_hold_entity_id2, NVL(hs.hold_entity_id2, -99 ) );
2301:
2302: EXCEPTION
2303: WHEN NO_DATA_FOUND THEN
2304: x_result_out := FND_API.G_FALSE;
2305: WHEN TOO_MANY_ROWS THEN
2306: null;
2307: END;
2308:
2307: END;
2308:
2309:
2310: EXCEPTION
2311: WHEN FND_API.G_EXC_ERROR THEN
2312: x_return_status := FND_API.G_RET_STS_ERROR;
2313: x_result_out := FND_API.G_FALSE;
2314: FND_MSG_PUB.Count_And_Get
2315: ( p_count => x_msg_count
2308:
2309:
2310: EXCEPTION
2311: WHEN FND_API.G_EXC_ERROR THEN
2312: x_return_status := FND_API.G_RET_STS_ERROR;
2313: x_result_out := FND_API.G_FALSE;
2314: FND_MSG_PUB.Count_And_Get
2315: ( p_count => x_msg_count
2316: , p_data => x_msg_data
2309:
2310: EXCEPTION
2311: WHEN FND_API.G_EXC_ERROR THEN
2312: x_return_status := FND_API.G_RET_STS_ERROR;
2313: x_result_out := FND_API.G_FALSE;
2314: FND_MSG_PUB.Count_And_Get
2315: ( p_count => x_msg_count
2316: , p_data => x_msg_data
2317: );
2314: FND_MSG_PUB.Count_And_Get
2315: ( p_count => x_msg_count
2316: , p_data => x_msg_data
2317: );
2318: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2319: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2320: x_result_out := FND_API.G_FALSE;
2321: FND_MSG_PUB.Count_And_Get
2322: ( p_count => x_msg_count
2315: ( p_count => x_msg_count
2316: , p_data => x_msg_data
2317: );
2318: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2319: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2320: x_result_out := FND_API.G_FALSE;
2321: FND_MSG_PUB.Count_And_Get
2322: ( p_count => x_msg_count
2323: , p_data => x_msg_data
2316: , p_data => x_msg_data
2317: );
2318: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2319: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2320: x_result_out := FND_API.G_FALSE;
2321: FND_MSG_PUB.Count_And_Get
2322: ( p_count => x_msg_count
2323: , p_data => x_msg_data
2324: );
2322: ( p_count => x_msg_count
2323: , p_data => x_msg_data
2324: );
2325: WHEN OTHERS THEN
2326: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2327: x_result_out := FND_API.G_FALSE;
2328: IF FND_MSG_PUB.Check_Msg_Level
2329: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2330: THEN
2323: , p_data => x_msg_data
2324: );
2325: WHEN OTHERS THEN
2326: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2327: x_result_out := FND_API.G_FALSE;
2328: IF FND_MSG_PUB.Check_Msg_Level
2329: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2330: THEN
2331: FND_MSG_PUB.Add_Exc_Msg
2352: -- ALL NEW Callers should call the new api structure
2353: --------------------------------------------------------------------------
2354: PROCEDURE Release_Holds
2355: ( p_api_version IN NUMBER DEFAULT 1.0
2356: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2357: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2358: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2359: , p_header_id IN NUMBER DEFAULT NULL
2360: , p_line_id IN NUMBER DEFAULT NULL
2353: --------------------------------------------------------------------------
2354: PROCEDURE Release_Holds
2355: ( p_api_version IN NUMBER DEFAULT 1.0
2356: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2357: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2358: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2359: , p_header_id IN NUMBER DEFAULT NULL
2360: , p_line_id IN NUMBER DEFAULT NULL
2361: , p_hold_id IN NUMBER DEFAULT NULL
2354: PROCEDURE Release_Holds
2355: ( p_api_version IN NUMBER DEFAULT 1.0
2356: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2357: , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2358: , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2359: , p_header_id IN NUMBER DEFAULT NULL
2360: , p_line_id IN NUMBER DEFAULT NULL
2361: , p_hold_id IN NUMBER DEFAULT NULL
2362: , p_entity_code IN VARCHAR2 DEFAULT NULL
2418: l_org_id := MO_GLOBAL.get_current_org_id;
2419: IF l_org_id IS NULL THEN
2420: -- org_id is null, raise an error.
2421: oe_debug_pub.add('Org_Id is NULL',1);
2422: x_return_status := FND_API.G_RET_STS_ERROR;
2423: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2424: FND_MSG_PUB.ADD;
2425: RAISE FND_API.G_EXC_ERROR;
2426: END IF;
2421: oe_debug_pub.add('Org_Id is NULL',1);
2422: x_return_status := FND_API.G_RET_STS_ERROR;
2423: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2424: FND_MSG_PUB.ADD;
2425: RAISE FND_API.G_EXC_ERROR;
2426: END IF;
2427:
2428: -- Standard Start of API savepoint
2429: SAVEPOINT Release_holds_PUB;
2428: -- Standard Start of API savepoint
2429: SAVEPOINT Release_holds_PUB;
2430:
2431: -- Initialize API return status to success
2432: x_return_status := FND_API.G_RET_STS_SUCCESS;
2433:
2434:
2435: Utilities(l_user_id);
2436:
2456: EXCEPTION
2457: WHEN NO_DATA_FOUND THEN
2458: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_SOURCE');
2459: OE_MSG_PUB.ADD;
2460: RAISE FND_API.G_EXC_ERROR;
2461: WHEN OTHERS THEN
2462: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2463: END;
2464:
2458: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_SOURCE');
2459: OE_MSG_PUB.ADD;
2460: RAISE FND_API.G_EXC_ERROR;
2461: WHEN OTHERS THEN
2462: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2463: END;
2464:
2465: -- Retrieving order hold information
2466: OPEN order_hold;
2467: FETCH order_hold INTO l_order_hold_id;
2468: IF order_hold%notfound THEN
2469: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD');
2470: OE_MSG_PUB.ADD;
2471: RAISE FND_API.G_EXC_ERROR;
2472: END IF;
2473: CLOSE order_hold;
2474:
2475: -------------------------------------------------------------------
2484: -- Check for Missing Values
2485: IF p_hold_id IS NULL THEN
2486: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_ID');
2487: OE_MSG_PUB.ADD;
2488: RAISE FND_API.G_EXC_ERROR;
2489: END IF;
2490:
2491: IF p_entity_code IS NULL THEN
2492: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
2490:
2491: IF p_entity_code IS NULL THEN
2492: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
2493: OE_MSG_PUB.ADD;
2494: RAISE FND_API.G_EXC_ERROR;
2495: ELSE
2496: l_entity_code := p_entity_code;
2497: END IF;
2498:
2498:
2499: IF p_entity_id IS NULL THEN
2500: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
2501: OE_MSG_PUB.ADD;
2502: RAISE FND_API.G_EXC_ERROR;
2503: END IF;
2504:
2505: /*
2506: IF p_line_id IS NULL AND p_header_id IS NULL THEN
2505: /*
2506: IF p_line_id IS NULL AND p_header_id IS NULL THEN
2507: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HEADER_AND_LINE_ID');
2508: OE_MSG_PUB.ADD;
2509: RAISE FND_API.G_EXC_ERROR;
2510: END IF;
2511: */
2512:
2513: -- Retrieving hold source and order hold information
2516: FETCH hold_source INTO l_hold_source_id, l_order_hold_id;
2517: IF hold_source%notfound THEN
2518: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD');
2519: OE_MSG_PUB.ADD;
2520: RAISE FND_API.G_EXC_ERROR;
2521: END IF;
2522: CLOSE hold_source;
2523: */
2524:
2553: IF l_debug_level > 0 THEN
2554: oe_debug_pub.add( 'OE_HOLDS_PVT.RELEASE_HOLDS STATUS:' || X_RETURN_STATUS ) ;
2555: END IF;
2556:
2557: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2558: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2559: RAISE FND_API.G_EXC_ERROR;
2560: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2561: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2554: oe_debug_pub.add( 'OE_HOLDS_PVT.RELEASE_HOLDS STATUS:' || X_RETURN_STATUS ) ;
2555: END IF;
2556:
2557: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2558: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2559: RAISE FND_API.G_EXC_ERROR;
2560: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2561: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562: END IF;
2555: END IF;
2556:
2557: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2558: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2559: RAISE FND_API.G_EXC_ERROR;
2560: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2561: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562: END IF;
2563: END IF;
2556:
2557: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2558: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2559: RAISE FND_API.G_EXC_ERROR;
2560: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2561: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562: END IF;
2563: END IF;
2564:
2557: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2558: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2559: RAISE FND_API.G_EXC_ERROR;
2560: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2561: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562: END IF;
2563: END IF;
2564:
2565: EXCEPTION
2562: END IF;
2563: END IF;
2564:
2565: EXCEPTION
2566: WHEN FND_API.G_EXC_ERROR THEN
2567: ROLLBACK TO RELEASE_HOLDS_PUB;
2568: x_return_status := FND_API.G_RET_STS_ERROR;
2569: FND_MSG_PUB.Count_And_Get
2570: ( p_count => x_msg_count
2564:
2565: EXCEPTION
2566: WHEN FND_API.G_EXC_ERROR THEN
2567: ROLLBACK TO RELEASE_HOLDS_PUB;
2568: x_return_status := FND_API.G_RET_STS_ERROR;
2569: FND_MSG_PUB.Count_And_Get
2570: ( p_count => x_msg_count
2571: , p_data => x_msg_data
2572: );
2569: FND_MSG_PUB.Count_And_Get
2570: ( p_count => x_msg_count
2571: , p_data => x_msg_data
2572: );
2573: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2574: ROLLBACK TO RELEASE_HOLDS_PUB;
2575: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2576: FND_MSG_PUB.Count_And_Get
2577: ( p_count => x_msg_count
2571: , p_data => x_msg_data
2572: );
2573: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2574: ROLLBACK TO RELEASE_HOLDS_PUB;
2575: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2576: FND_MSG_PUB.Count_And_Get
2577: ( p_count => x_msg_count
2578: , p_data => x_msg_data
2579: );
2578: , p_data => x_msg_data
2579: );
2580: WHEN OTHERS THEN
2581: ROLLBACK TO RELEASE_HOLDS_PUB;
2582: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2583: IF FND_MSG_PUB.Check_Msg_Level
2584: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2585: THEN
2586: FND_MSG_PUB.Add_Exc_Msg
2606: -- not used by an existing hold or hold source, then deletes the
2607: -- release record also from OE_HOLD_RELEASES;
2608: ----------------------------------------------------------------------------
2609: PROCEDURE Delete_Holds
2610: ( p_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2611: ,p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2612: )
2613: IS
2614: l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HOLDS';
2607: -- release record also from OE_HOLD_RELEASES;
2608: ----------------------------------------------------------------------------
2609: PROCEDURE Delete_Holds
2610: ( p_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2611: ,p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2612: )
2613: IS
2614: l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HOLDS';
2615: l_api_version CONSTANT NUMBER := 1.0;
2656: l_org_id := MO_GLOBAL.get_current_org_id;
2657: IF l_org_id IS NULL THEN
2658: -- org_id is null, raise an error.
2659: oe_debug_pub.add('Org_Id is NULL',1);
2660: x_return_status := FND_API.G_RET_STS_ERROR;
2661: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2662: FND_MSG_PUB.ADD;
2663: RAISE FND_API.G_EXC_ERROR;
2664: END IF;
2659: oe_debug_pub.add('Org_Id is NULL',1);
2660: x_return_status := FND_API.G_RET_STS_ERROR;
2661: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2662: FND_MSG_PUB.ADD;
2663: RAISE FND_API.G_EXC_ERROR;
2664: END IF;
2665:
2666: --SAVEPOINT DELETE_HOLDS_PUB;
2667:
2665:
2666: --SAVEPOINT DELETE_HOLDS_PUB;
2667:
2668: -- Initialize API return status to success
2669: x_return_status := FND_API.G_RET_STS_SUCCESS;
2670:
2671: -- Missing Input arguments
2672:
2673: IF (p_header_id = FND_API.G_MISS_NUM AND
2669: x_return_status := FND_API.G_RET_STS_SUCCESS;
2670:
2671: -- Missing Input arguments
2672:
2673: IF (p_header_id = FND_API.G_MISS_NUM AND
2674: p_line_id = FND_API.G_MISS_NUM) THEN
2675:
2676: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
2677: OE_MSG_PUB.ADD;
2670:
2671: -- Missing Input arguments
2672:
2673: IF (p_header_id = FND_API.G_MISS_NUM AND
2674: p_line_id = FND_API.G_MISS_NUM) THEN
2675:
2676: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
2677: OE_MSG_PUB.ADD;
2678: RAISE FND_API.G_EXC_ERROR;
2674: p_line_id = FND_API.G_MISS_NUM) THEN
2675:
2676: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
2677: OE_MSG_PUB.ADD;
2678: RAISE FND_API.G_EXC_ERROR;
2679:
2680: END IF;
2681:
2682: -- Delete the hold records corr. to this order or line in OE_ORDER_HOLDS
2679:
2680: END IF;
2681:
2682: -- Delete the hold records corr. to this order or line in OE_ORDER_HOLDS
2683: IF p_line_id = FND_API.G_MISS_NUM THEN
2684:
2685: -- Delete order hold records
2686: OPEN order_hold;
2687: LOOP
2812: END IF;
2813:
2814:
2815: EXCEPTION
2816: WHEN FND_API.G_EXC_ERROR THEN
2817: IF (order_hold%isopen) THEN
2818: CLOSE order_hold;
2819: END IF;
2820: IF (hold_source%isopen) THEN
2823: IF (line_hold%isopen) THEN
2824: CLOSE line_hold;
2825: END IF;
2826: --ROLLBACK TO DELETE_HOLDS_PUB;
2827: x_return_status := FND_API.G_RET_STS_ERROR;
2828: FND_MSG_PUB.Count_And_Get
2829: ( p_count => x_msg_count
2830: , p_data => x_msg_data
2831: );
2839: IF (line_hold%isopen) THEN
2840: CLOSE line_hold;
2841: END IF;
2842: --ROLLBACK TO DELETE_HOLDS_PUB;
2843: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2844: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2845: THEN
2846: FND_MSG_PUB.Add_Exc_Msg
2847: ( G_PKG_NAME
2913: --ER#7479609 SELECT HS.hold_entity_id, OH.order_hold_id
2914: SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code --ER#7479609
2915: FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2916: WHERE OH.HEADER_ID = l_header_id
2917: AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
2918: NVL(l_line_id,FND_API.G_MISS_NUM)
2919: AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2920: AND HS.HOLD_ENTITY_ID = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID) --ER#7479609
2921: AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
2914: SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code --ER#7479609
2915: FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2916: WHERE OH.HEADER_ID = l_header_id
2917: AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
2918: NVL(l_line_id,FND_API.G_MISS_NUM)
2919: AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2920: AND HS.HOLD_ENTITY_ID = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID) --ER#7479609
2921: AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
2922: AND HS.RELEASED_FLAG = 'N';
2926: --ER#7479609 SELECT HS.hold_entity_id, OH.order_hold_id
2927: SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code2 --ER#7479609
2928: FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2929: WHERE OH.HEADER_ID = l_header_id
2930: AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
2931: NVL(l_line_id,FND_API.G_MISS_NUM)
2932: AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2933: AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code
2934: AND HS.HOLD_ENTITY_ID2 = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID2) --ER#7479609
2927: SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code2 --ER#7479609
2928: FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2929: WHERE OH.HEADER_ID = l_header_id
2930: AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
2931: NVL(l_line_id,FND_API.G_MISS_NUM)
2932: AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2933: AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code
2934: AND HS.HOLD_ENTITY_ID2 = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID2) --ER#7479609
2935: AND HS.HOLD_ENTITY_CODE <> 'O'
2984: pay_idx NUMBER :=0;
2985: l_all_del_pay varchar2(1) := 'Y';
2986: --ER#7479609 end
2987: BEGIN
2988: x_return_status := FND_API.G_RET_STS_SUCCESS;
2989:
2990: IF l_debug_level > 0 THEN
2991: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS' , 1 ) ;
2992: END IF;
3506: IF l_debug_level > 0 THEN
3507: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
3508: END IF;
3509:
3510: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3511: IF l_debug_level > 0 THEN
3512: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3513: END IF;
3514: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3510: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3511: IF l_debug_level > 0 THEN
3512: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3513: END IF;
3514: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3515: RAISE FND_API.G_EXC_ERROR;
3516: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3517: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3518: END IF;
3511: IF l_debug_level > 0 THEN
3512: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3513: END IF;
3514: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3515: RAISE FND_API.G_EXC_ERROR;
3516: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3517: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3518: END IF;
3519: --ER#7479609 start
3512: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3513: END IF;
3514: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3515: RAISE FND_API.G_EXC_ERROR;
3516: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3517: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3518: END IF;
3519: --ER#7479609 start
3520: IF x_return_status = 'PAY_HOLD_NA' THEN
3513: END IF;
3514: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3515: RAISE FND_API.G_EXC_ERROR;
3516: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3517: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3518: END IF;
3519: --ER#7479609 start
3520: IF x_return_status = 'PAY_HOLD_NA' THEN
3521: G_PAYMENT_HOLD_APPLIED := 'Y';
3773: IF l_debug_level > 0 THEN
3774: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
3775: END IF;
3776:
3777: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3778: IF l_debug_level > 0 THEN
3779: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3780: END IF;
3781: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3777: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3778: IF l_debug_level > 0 THEN
3779: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3780: END IF;
3781: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3782: RAISE FND_API.G_EXC_ERROR;
3783: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3784: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3785: END IF;
3778: IF l_debug_level > 0 THEN
3779: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3780: END IF;
3781: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3782: RAISE FND_API.G_EXC_ERROR;
3783: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3784: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3785: END IF;
3786:
3779: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3780: END IF;
3781: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3782: RAISE FND_API.G_EXC_ERROR;
3783: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3784: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3785: END IF;
3786:
3787: --ER#7479609 start
3780: END IF;
3781: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3782: RAISE FND_API.G_EXC_ERROR;
3783: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3784: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3785: END IF;
3786:
3787: --ER#7479609 start
3788: IF x_return_status = 'PAY_HOLD_NA' THEN
3906: END IF;
3907:
3908: EXCEPTION
3909:
3910: WHEN FND_API.G_EXC_ERROR THEN
3911: IF (prev_hold%isopen) THEN
3912: CLOSE prev_hold;
3913: END IF;
3914: IF (curr_hold_source%isopen) THEN
3913: END IF;
3914: IF (curr_hold_source%isopen) THEN
3915: CLOSE curr_hold_source;
3916: END IF;
3917: x_return_status := FND_API.G_RET_STS_ERROR;
3918:
3919: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3920: IF (prev_hold%isopen) THEN
3921: CLOSE prev_hold;
3915: CLOSE curr_hold_source;
3916: END IF;
3917: x_return_status := FND_API.G_RET_STS_ERROR;
3918:
3919: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3920: IF (prev_hold%isopen) THEN
3921: CLOSE prev_hold;
3922: END IF;
3923: IF (curr_hold_source%isopen) THEN
3922: END IF;
3923: IF (curr_hold_source%isopen) THEN
3924: CLOSE curr_hold_source;
3925: END IF;
3926: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3927:
3928: WHEN OTHERS THEN
3929: IF (prev_hold%isopen) THEN
3930: CLOSE prev_hold;
3931: END IF;
3932: IF (curr_hold_source%isopen) THEN
3933: CLOSE curr_hold_source;
3934: END IF;
3935: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3936:
3937: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3938: THEN
3939: OE_MSG_PUB.Add_Exc_Msg
4014: --
4015: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4016: --
4017: BEGIN
4018: x_return_status := FND_API.G_RET_STS_SUCCESS;
4019:
4020: IF l_debug_level > 0 THEN
4021: oe_debug_pub.add( 'ENTERING EVAL_POST_WRITE_HEADER' , 1 ) ;
4022: END IF;
4083: IF l_debug_level > 0 THEN
4084: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
4085: END IF;
4086:
4087: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4088: IF l_debug_level > 0 THEN
4089: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4090: END IF;
4091: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4087: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4088: IF l_debug_level > 0 THEN
4089: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4090: END IF;
4091: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4092: RAISE FND_API.G_EXC_ERROR;
4093: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4095: END IF;
4088: IF l_debug_level > 0 THEN
4089: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4090: END IF;
4091: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4092: RAISE FND_API.G_EXC_ERROR;
4093: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4095: END IF;
4096: ELSE
4089: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4090: END IF;
4091: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4092: RAISE FND_API.G_EXC_ERROR;
4093: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4095: END IF;
4096: ELSE
4097: IF l_debug_level > 0 THEN
4090: END IF;
4091: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4092: RAISE FND_API.G_EXC_ERROR;
4093: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4095: END IF;
4096: ELSE
4097: IF l_debug_level > 0 THEN
4098: oe_debug_pub.add( 'HOLD APPLIED' ) ;
4114: END IF;
4115:
4116: EXCEPTION
4117:
4118: WHEN FND_API.G_EXC_ERROR THEN
4119: IF (curr_hold_source%isopen) THEN
4120: CLOSE curr_hold_source;
4121: END IF;
4122: x_return_status := FND_API.G_RET_STS_ERROR;
4118: WHEN FND_API.G_EXC_ERROR THEN
4119: IF (curr_hold_source%isopen) THEN
4120: CLOSE curr_hold_source;
4121: END IF;
4122: x_return_status := FND_API.G_RET_STS_ERROR;
4123:
4124: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4125: IF (curr_hold_source%isopen) THEN
4126: CLOSE curr_hold_source;
4120: CLOSE curr_hold_source;
4121: END IF;
4122: x_return_status := FND_API.G_RET_STS_ERROR;
4123:
4124: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4125: IF (curr_hold_source%isopen) THEN
4126: CLOSE curr_hold_source;
4127: END IF;
4128: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4124: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4125: IF (curr_hold_source%isopen) THEN
4126: CLOSE curr_hold_source;
4127: END IF;
4128: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4129:
4130: WHEN OTHERS THEN
4131: IF (curr_hold_source%isopen) THEN
4132: CLOSE curr_hold_source;
4130: WHEN OTHERS THEN
4131: IF (curr_hold_source%isopen) THEN
4132: CLOSE curr_hold_source;
4133: END IF;
4134: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4135:
4136: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4137: THEN
4138: OE_MSG_PUB.Add_Exc_Msg
4280: --
4281: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4282: --
4283: BEGIN
4284: x_return_status := FND_API.G_RET_STS_SUCCESS;
4285:
4286: IF l_debug_level > 0 THEN
4287: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
4288: END IF;
4682: IF l_debug_level > 0 THEN
4683: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
4684: END IF;
4685:
4686: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4687: IF l_debug_level > 0 THEN
4688: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4689: END IF;
4690: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4686: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4687: IF l_debug_level > 0 THEN
4688: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4689: END IF;
4690: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4691: RAISE FND_API.G_EXC_ERROR;
4692: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4694: END IF;
4687: IF l_debug_level > 0 THEN
4688: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4689: END IF;
4690: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4691: RAISE FND_API.G_EXC_ERROR;
4692: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4694: END IF;
4695: ELSE
4688: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4689: END IF;
4690: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4691: RAISE FND_API.G_EXC_ERROR;
4692: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4694: END IF;
4695: ELSE
4696: IF l_debug_level > 0 THEN
4689: END IF;
4690: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4691: RAISE FND_API.G_EXC_ERROR;
4692: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4694: END IF;
4695: ELSE
4696: IF l_debug_level > 0 THEN
4697: oe_debug_pub.add( 'HOLD APPLIED' ) ;
4871: );
4872:
4873: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
4874:
4875: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4876: OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
4877: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4878: RAISE FND_API.G_EXC_ERROR;
4879: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4873: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
4874:
4875: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4876: OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
4877: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4878: RAISE FND_API.G_EXC_ERROR;
4879: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4880: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4881: END IF;
4874:
4875: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4876: OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
4877: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4878: RAISE FND_API.G_EXC_ERROR;
4879: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4880: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4881: END IF;
4882: ELSE
4875: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4876: OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
4877: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4878: RAISE FND_API.G_EXC_ERROR;
4879: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4880: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4881: END IF;
4882: ELSE
4883: OE_Debug_PUB.Add('Hold applied');
4876: OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
4877: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4878: RAISE FND_API.G_EXC_ERROR;
4879: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4880: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4881: END IF;
4882: ELSE
4883: OE_Debug_PUB.Add('Hold applied');
4884:
4935: END IF;
4936:
4937: EXCEPTION
4938:
4939: WHEN FND_API.G_EXC_ERROR THEN
4940: IF (curr_hold_source%isopen) THEN
4941: CLOSE curr_hold_source;
4942: END IF;
4943: x_return_status := FND_API.G_RET_STS_ERROR;
4939: WHEN FND_API.G_EXC_ERROR THEN
4940: IF (curr_hold_source%isopen) THEN
4941: CLOSE curr_hold_source;
4942: END IF;
4943: x_return_status := FND_API.G_RET_STS_ERROR;
4944:
4945: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4946: IF (curr_hold_source%isopen) THEN
4947: CLOSE curr_hold_source;
4941: CLOSE curr_hold_source;
4942: END IF;
4943: x_return_status := FND_API.G_RET_STS_ERROR;
4944:
4945: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4946: IF (curr_hold_source%isopen) THEN
4947: CLOSE curr_hold_source;
4948: END IF;
4949: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4945: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4946: IF (curr_hold_source%isopen) THEN
4947: CLOSE curr_hold_source;
4948: END IF;
4949: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4950:
4951: WHEN OTHERS THEN
4952: IF (curr_hold_source%isopen) THEN
4953: CLOSE curr_hold_source;
4951: WHEN OTHERS THEN
4952: IF (curr_hold_source%isopen) THEN
4953: CLOSE curr_hold_source;
4954: END IF;
4955: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4956:
4957: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4958: THEN
4959: OE_MSG_PUB.Add_Exc_Msg
5046:
5047: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
5048: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
5049:
5050: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5051: l_org_id NUMBER := null;
5052: l_hold_source_id NUMBER := null;
5053: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5054:
5100:
5101: OPEN c_hold_orgs;
5102: LOOP
5103: FETCH c_hold_orgs into l_org_id;
5104: EXIT WHEN c_hold_orgs%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5105: -- can l_org_id be null??? No.
5106:
5107: IF l_debug_level > 0 THEN
5108: oe_debug_pub.add('Applying Hold in ORG_ID ' || l_org_id, 1);
5133: OPEN c_cust_holds;
5134: LOOP
5135:
5136: FETCH c_cust_holds into l_org_id,l_hold_source_id;
5137: EXIT WHEN c_cust_holds%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5138:
5139: IF l_debug_level > 0 THEN
5140: oe_debug_pub.add('Releasing Hold in ORG_ID ' || l_org_id, 1);
5141: END IF;
5163: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5164: END IF;
5165:
5166: EXCEPTION
5167: WHEN FND_API.G_EXC_ERROR THEN
5168:
5169: IF l_debug_level > 0 THEN
5170: oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5171: END IF;
5172: IF x_msg_count is not null then
5173: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5174: p_data => x_msg_data);
5175: end if;
5176: RAISE FND_API.G_EXC_ERROR;
5177:
5178: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5179: IF l_debug_level > 0 THEN
5180: oe_debug_pub.add( 'API UNEXPECTED ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5174: p_data => x_msg_data);
5175: end if;
5176: RAISE FND_API.G_EXC_ERROR;
5177:
5178: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5179: IF l_debug_level > 0 THEN
5180: oe_debug_pub.add( 'API UNEXPECTED ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5181: END IF;
5182: IF x_msg_count is not null then
5184: ( p_count => x_msg_count
5185: , p_data => x_msg_data
5186: );
5187: END IF;
5188: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5189:
5190: WHEN OTHERS THEN
5191: IF l_debug_level > 0 THEN
5192: oe_debug_pub.add( 'ERROR in oe_holds_pub.Process_Holds_Customer' , 1 ) ;
5213:
5214: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
5215: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
5216: l_hold_entity_code varchar2(1);
5217: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5218: l_hold_exists VARCHAR2(1) := 'N';
5219: l_org_id NUMBER := null;
5220: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5221: BEGIN
5240: raise no_data_found; -- Should not occur at all
5241: END IF;
5242: EXCEPTION
5243: WHEN no_data_found then
5244: --x_return_status := FND_API.G_RET_STS_ERROR;
5245: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
5246: OE_MSG_PUB.ADD;
5247: fnd_message.set_token('SITE_USE_ID', to_char(p_hold_entity_id));
5248:
5245: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
5246: OE_MSG_PUB.ADD;
5247: fnd_message.set_token('SITE_USE_ID', to_char(p_hold_entity_id));
5248:
5249: RAISE FND_API.G_EXC_ERROR;
5250: END;
5251:
5252: IF l_debug_level > 0 THEN
5253: oe_debug_pub.add('Process_Holds_Site:ORG_ID: ' || l_org_id, 1);
5302: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5303: END IF;
5304: END IF; -- APPLY OR RELEASE
5305: EXCEPTION
5306: WHEN FND_API.G_EXC_ERROR THEN
5307:
5308: IF l_debug_level > 0 THEN
5309: oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5310: END IF;
5313: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5314: p_data => x_msg_data);
5315: end if;
5316:
5317: RAISE FND_API.G_EXC_ERROR;
5318:
5319: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5320: IF l_debug_level > 0 THEN
5321: oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5315: end if;
5316:
5317: RAISE FND_API.G_EXC_ERROR;
5318:
5319: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5320: IF l_debug_level > 0 THEN
5321: oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5322: END IF;
5323:
5325: ( p_count => x_msg_count
5326: , p_data => x_msg_data
5327: );
5328:
5329: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5330:
5331: WHEN OTHERS THEN
5332: IF l_debug_level > 0 THEN
5333: oe_debug_pub.add( 'ERROR in oe_holds_pub.process_holds' , 1 ) ;
5350:
5351: */
5352: PROCEDURE Process_Holds
5353: ( p_api_version IN NUMBER
5354: , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
5355: , p_hold_entity_code IN VARCHAR2
5356: --ER#7479609 , p_hold_entity_id IN NUMBER
5357: , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5358: , p_hold_id IN NUMBER DEFAULT 1
5364: ) IS
5365:
5366: l_orig_org NUMBER := null;
5367:
5368: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5369: l_msg_count NUMBER := null;
5370: l_msg_data VARCHAR2(2000) := null;
5371: l_valid_acct VARCHAR2(1) := 'N';
5372:
5388: IF p_action IS NULL OR p_action NOT IN ('APPLY','RELEASE') THEN
5389: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ACTION'); -- message taken from oe_holds
5390: fnd_message.set_token('ACTION',p_action);
5391: OE_MSG_PUB.ADD;
5392: RAISE FND_API.G_EXC_ERROR;
5393: END IF;
5394:
5395: IF p_hold_entity_code IS NULL OR p_hold_entity_code NOT IN ('S','C') THEN
5396: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_HOLD_ENTITY_CODE'); -- message need to be seeded or use any existing one
5394:
5395: IF p_hold_entity_code IS NULL OR p_hold_entity_code NOT IN ('S','C') THEN
5396: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_HOLD_ENTITY_CODE'); -- message need to be seeded or use any existing one
5397: OE_MSG_PUB.ADD;
5398: RAISE FND_API.G_EXC_ERROR;
5399: END IF;
5400: -- hold the original org id being used by programs when calling process_holds.
5401: -- set the org org context to this original org when leaving process_holds.
5402: -- sHOULD IT BE SET EVEN WHEN EXITING WITH AN EXCEPTION??
5415: , x_msg_count => l_msg_count
5416: , x_msg_data => l_msg_data
5417: );
5418:
5419: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5420:
5421: IF l_debug_level > 0 THEN
5422: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5423: END IF;
5421: IF l_debug_level > 0 THEN
5422: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5423: END IF;
5424:
5425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5426: RAISE FND_API.G_EXC_ERROR;
5427: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5428: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5429: END IF;
5422: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5423: END IF;
5424:
5425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5426: RAISE FND_API.G_EXC_ERROR;
5427: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5428: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5429: END IF;
5430: END IF;
5423: END IF;
5424:
5425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5426: RAISE FND_API.G_EXC_ERROR;
5427: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5428: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5429: END IF;
5430: END IF;
5431:
5424:
5425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5426: RAISE FND_API.G_EXC_ERROR;
5427: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5428: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5429: END IF;
5430: END IF;
5431:
5432: ELSIF p_hold_entity_code = 'C' THEN
5438: IF l_valid_acct <> 'Y' THEN
5439: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_CUST_ACCOUNT'); -- get the correct message new or existing??
5440: -- fnd_message.set_token('',p_action);
5441: OE_MSG_PUB.ADD;
5442: RAISE FND_API.G_EXC_ERROR;
5443: END IF;
5444:
5445: -- call process_holds_customer
5446:
5454: , x_msg_count => l_msg_count
5455: , x_msg_data => l_msg_data
5456: );
5457:
5458: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5459:
5460: IF l_debug_level > 0 THEN
5461: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5462: END IF;
5460: IF l_debug_level > 0 THEN
5461: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5462: END IF;
5463:
5464: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5465: RAISE FND_API.G_EXC_ERROR;
5466: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5467: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5468: END IF;
5461: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5462: END IF;
5463:
5464: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5465: RAISE FND_API.G_EXC_ERROR;
5466: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5467: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5468: END IF;
5469: END IF;
5462: END IF;
5463:
5464: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5465: RAISE FND_API.G_EXC_ERROR;
5466: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5467: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5468: END IF;
5469: END IF;
5470:
5463:
5464: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5465: RAISE FND_API.G_EXC_ERROR;
5466: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5467: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5468: END IF;
5469: END IF;
5470:
5471: END IF;
5481: IF l_debug_level > 0 THEN
5482: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5483: END IF;
5484: EXCEPTION
5485: WHEN FND_API.G_EXC_ERROR THEN
5486:
5487: IF l_debug_level > 0 THEN
5488: oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5489: END IF;
5498: IF l_debug_level > 0 THEN
5499: oe_debug_pub.add( 'Error Message: '||l_msg_data , 1 ) ;
5500: END IF;
5501: ROLLBACK TO SAVEPOINT oe_process_holds;
5502: RAISE FND_API.G_EXC_ERROR;
5503:
5504: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5505:
5506: IF l_debug_level > 0 THEN
5500: END IF;
5501: ROLLBACK TO SAVEPOINT oe_process_holds;
5502: RAISE FND_API.G_EXC_ERROR;
5503:
5504: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5505:
5506: IF l_debug_level > 0 THEN
5507: oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5508: END IF;
5516: IF l_debug_level > 0 THEN
5517: oe_debug_pub.add( 'Error Message: '||l_msg_data , 1 ) ;
5518: END IF;
5519: ROLLBACK TO SAVEPOINT oe_process_holds;
5520: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5521:
5522: WHEN OTHERS THEN
5523: IF l_debug_level > 0 THEN
5524: oe_debug_pub.add( 'ERROR in oe_holds_pub.process_holds' , 1 ) ;