[Home] [Help]
8: PROCEDURE Utilities
9: ( p_user_id OUT NOCOPY /* file.sql.39 change */ NUMBER)
10: IS
11: --
12: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
13: --
14: BEGIN
15: p_user_id := NVL(FND_GLOBAL.USER_ID, -1);
16:
25: IS
26: l_site_use_code varchar2(30);
27: l_hold_site_code varchar2(1);
28: --
29: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
30: --
31: BEGIN
32:
33: IF l_debug_level > 0 THEN
30: --
31: BEGIN
32:
33: IF l_debug_level > 0 THEN
34: oe_debug_pub.add( 'OEXPHLDB:IN PROCEDURE HOLD_SITE_CODE' , 1 ) ;
35: END IF;
36: -- Check to see if the Site code is Bill_to oe Ship_to
37: /* Backward compatible view ra_site_uses in following sql is replaced
38: by hz_cust_site_uses all for bug 1874065 */
48: OE_MSG_PUB.ADD;
49: fnd_message.set_token('SITE_USE_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
58: ELSE
59: l_hold_site_code := 'S';
60: END IF;
61: IF l_debug_level > 0 THEN
62: oe_debug_pub.add( 'HOLD_SITE_CODE , L_HOLD_SITE_CODE:' || L_HOLD_SITE_CODE , 1 ) ;
63: END IF;
64: RETURN l_hold_site_code;
65:
66: END HOLD_SITE_CODE;
78: IS
79: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_HOLD_COMMENTS';
80: --
81: l_org_id number;
82: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
83: --
84: BEGIN
85: IF l_debug_level > 0 THEN
86: oe_debug_pub.add( 'IN UPDATE_HOLD_COMMENTS' ) ;
82: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
83: --
84: BEGIN
85: IF l_debug_level > 0 THEN
86: oe_debug_pub.add( 'IN UPDATE_HOLD_COMMENTS' ) ;
87: END IF;
88:
89: l_org_id := MO_GLOBAL.get_current_org_id;
90: IF l_org_id IS NULL THEN
88:
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;
151: end if;
152:
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:
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
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
178: ( p_count => x_msg_count,
179: p_data => x_msg_data
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
188: (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
189: THEN
228: from oe_hold_sources
229: where HOLD_UNTIL_DATE <= sysdate
230: and released_flag = 'N';
231: --
232: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
233: --
234: BEGIN
235: IF l_debug_level > 0 THEN
236: oe_debug_pub.add( 'IN RELEASE EXPIRED HOLDS' ) ;
232: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
233: --
234: BEGIN
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:
233: --
234: BEGIN
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
250: open expired_holds_cur;
251: LOOP
252: fetch expired_holds_cur into l_hold_source_id, l_org_id;
253: if (expired_holds_cur%notfound) then
254: oe_debug_pub.add('Exiting expired_holds_cur%notfound:') ;
255: exit;
256: end if;
257: IF l_debug_level > 0 THEN
258: oe_debug_pub.add('RELEASE EXPIRED HOLD FOR:' || TO_CHAR (L_HOLD_SOURCE_ID) ||
254: oe_debug_pub.add('Exiting expired_holds_cur%notfound:') ;
255: exit;
256: end if;
257: IF l_debug_level > 0 THEN
258: oe_debug_pub.add('RELEASE EXPIRED HOLD FOR:' || TO_CHAR (L_HOLD_SOURCE_ID) ||
259: ', ORG ID:' || TO_CHAR(l_org_id) );
260: END IF;
261:
262: if l_org_id <> nvl(l_curr_org_id, -99)
260: END IF;
261:
262: if l_org_id <> nvl(l_curr_org_id, -99)
263: then
264: oe_debug_pub.add('Setting Policy Context for:' || TO_CHAR(l_org_id) ) ;
265: MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id);
266: l_curr_org_id := l_org_id;
267: oe_debug_pub.add('MO_GLOBAL.get_current_org_id;:' || to_char(MO_GLOBAL.get_current_org_id));
268: End if;
263: then
264: oe_debug_pub.add('Setting Policy Context for:' || TO_CHAR(l_org_id) ) ;
265: MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id);
266: l_curr_org_id := l_org_id;
267: oe_debug_pub.add('MO_GLOBAL.get_current_org_id;:' || to_char(MO_GLOBAL.get_current_org_id));
268: End if;
269:
270: l_hold_source_rec.hold_source_id := l_hold_source_id;
271: l_hold_release_rec.RELEASE_REASON_CODE := 'EXPIRE';
279: ,x_msg_count => l_msg_count
280: ,x_msg_data => l_msg_data
281: );
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
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;
291: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
292: IF l_debug_level > 0 THEN
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;
296: END IF;
297:
351: IS
352: l_api_name CONSTANT VARCHAR2(30) := 'ValidateOrder';
353: l_dummy VARCHAR2(30);
354: --
355: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
356: --
357: BEGIN
358:
359: IF l_debug_level > 0 THEN
356: --
357: BEGIN
358:
359: IF l_debug_level > 0 THEN
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;
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' ) ;
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;
558: IS
559: l_api_name CONSTANT VARCHAR2(30) := 'Apply_Holds';
560: --
561: l_org_id number;
562: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
563: --
564: BEGIN
565:
566: IF l_debug_level > 0 THEN
563: --
564: BEGIN
565:
566: IF l_debug_level > 0 THEN
567: oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS' ) ;
568: END IF;
569:
570: l_org_id := MO_GLOBAL.get_current_org_id;
571: IF l_org_id IS NULL THEN
569:
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;
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;
598: x_return_status := FND_API.G_RET_STS_ERROR;
599: FND_MSG_PUB.Count_And_Get
600: ( p_count => x_msg_count
638: IS
639: l_api_name CONSTANT VARCHAR2(30) := 'Apply_Holds';
640: --
641: l_org_id number;
642: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
643: --
644: BEGIN
645:
646: IF l_debug_level > 0 THEN
643: --
644: BEGIN
645:
646: IF l_debug_level > 0 THEN
647: oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS , CREATING HOLD SOURCE' ) ;
648: END IF;
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.
648: END IF;
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;
668: ,x_msg_count => x_msg_count
669: ,x_msg_data => x_msg_data
670: );
671: IF l_debug_level > 0 THEN
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
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;
681: FND_MSG_PUB.Count_And_Get
682: ( p_count => x_msg_count
721: IS
722: l_api_name CONSTANT VARCHAR2(30) := 'release_holds';
723: --
724: l_org_id number;
725: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
726: --
727: BEGIN
728:
729: IF l_debug_level > 0 THEN
726: --
727: BEGIN
728:
729: IF l_debug_level > 0 THEN
730: oe_debug_pub.add( 'IN OE_HOLDS_PUB.RELEASE_HOLDS' ) ;
731: END IF;
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.
731: END IF;
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;
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;
759: FND_MSG_PUB.Count_And_Get
760: ( p_count => x_msg_count
803: j NUMBER;
804: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
805: l_org_id number;
806: --
807: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
808: --
809: BEGIN
810:
811: IF l_debug_level > 0 THEN
808: --
809: BEGIN
810:
811: IF l_debug_level > 0 THEN
812: oe_debug_pub.add( 'IN OE_HOLDS_PUB.RELEASE_HOLDS' ) ;
813: END IF;
814:
815: l_org_id := MO_GLOBAL.get_current_org_id;
816: IF l_org_id IS NULL THEN
814:
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;
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;
863: FND_MSG_PUB.Count_And_Get
864: ( p_count => x_msg_count
936: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
937: l_site_use_code VARCHAR2(30);
938: l_org_id number;
939: --
940: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
941: --
942: BEGIN
943:
944: l_org_id := MO_GLOBAL.get_current_org_id;
943:
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;
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' ) ;
956: END IF;
957:
958: SAVEPOINT APPLY_HOLDS_PUB;
959:
968: ----------------------------------------------------------------
969: IF p_hold_source_id IS NOT NULL THEN
970:
971: IF l_debug_level > 0 THEN
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
989:
990: EXCEPTION
991: WHEN NO_DATA_FOUND THEN
992: IF l_debug_level > 0 THEN
993: oe_debug_pub.add( 'INVALID HOLD SOURCE ID' ) ;
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;
1029: -- ONLY needed coz AR still calls the old holds api (oe_holds) with
1030: -- S as hold_entity_code for Bill To.
1031: if p_hold_source_rec.hold_entity_code = 'S' THEN
1032: IF l_debug_level > 0 THEN
1033: oe_debug_pub.add( 'CHECKING FOR SITE CODE , BILL TO OR SHIP TO' , 1 ) ;
1034: END IF;
1035: l_entity_code := Hold_Site_Code(p_hold_source_rec.hold_entity_id);
1036: ELSE
1037: l_entity_code := p_hold_source_rec.hold_entity_code;
1070:
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
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
1099: RAISE FND_API.G_EXC_UNEXPECTED_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;
1104: END IF; -- p_hold_source_rec.hold_entity_code = 'O'
1105: */
1106:
1125:
1126: --dbms_output.put_line ('AH-hold_id' ||to_char(p_hold_source_rec.hold_id)); -- delete
1127: --dbms_output.put_line ('AH-B4ApplyHolds' ); -- delete
1128: IF l_debug_level > 0 THEN
1129: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.APPLY_HOLDS' ) ;
1130: END IF;
1131: oe_holds_pvt.apply_Holds(
1132: p_hold_source_rec => l_hold_source_rec
1133: ,p_hold_existing_flg => 'Y'
1136: ,x_msg_count => x_msg_count
1137: ,x_msg_data => x_msg_data
1138: );
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
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;
1157: x_return_status := FND_API.G_RET_STS_ERROR;
1158: FND_MSG_PUB.Count_And_Get
1159: ( p_count => x_msg_count
1212: IS
1213: l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_line';
1214: l_dummy VARCHAR2(30);
1215: --
1216: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1217: --
1218: BEGIN
1219: IF l_debug_level > 0 THEN
1220: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_LINE:' || P_LINE_ID ) ;
1216: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1217: --
1218: BEGIN
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:
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
1287: null;
1288: END;
1287: null;
1288: END;
1289:
1290: IF l_debug_level > 0 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
1349: IS
1350: l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_ATO';
1351: l_dummy VARCHAR2(30);
1352: --
1353: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1354: --
1355: BEGIN
1356: IF l_debug_level > 0 THEN
1357: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1353: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1354: --
1355: BEGIN
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:
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
1414: null;
1415: END;
1414: null;
1415: END;
1416:
1417: IF l_debug_level > 0 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
1476: IS
1477: l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_SMC';
1478: l_dummy VARCHAR2(30);
1479: --
1480: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1481: --
1482: BEGIN
1483: IF l_debug_level > 0 THEN
1484: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1480: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1481: --
1482: BEGIN
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:
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
1540: null;
1541: END;
1540: null;
1541: END;
1542:
1543: IF l_debug_level > 0 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
1591: IS
1592: l_api_name CONSTANT VARCHAR2(30) := 'Check_Any_Line_Hold';
1593: l_dummy VARCHAR2(30);
1594: --
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;
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
1647: null;
1648: END;
1741: select line_id
1742: from oe_order_lines_all
1743: where top_model_line_id = l_top_model_line_id;
1744: --
1745: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1746: --
1747: --7832836 l_cascade_hold_non_smc VARCHAR2(1) := NVL(OE_SYS_PARAMETERS.VALUE('ONT_CASCADE_HOLD_NONSMC_PTO'),'N'); --ER#7479609
1748: l_cascade_hold_non_smc VARCHAR2(1); -- 7832836
1749: l_org_id NUMBER; -- 7832836
1748: l_cascade_hold_non_smc VARCHAR2(1); -- 7832836
1749: l_org_id NUMBER; -- 7832836
1750: BEGIN
1751: IF l_debug_level > 0 THEN
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;
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;
1791: ELSE
1792: p_hdr_id := p_header_id;
1792: p_hdr_id := p_header_id;
1793: END IF;
1794:
1795: IF l_debug_level > 0 THEN
1796: oe_debug_pub.add( 'CHECKING HOLD ON HEADER ID: ' || P_HDR_ID ) ;
1797: END IF;
1798:
1799: --7832836 start
1800: l_org_id := MO_GLOBAL.get_current_org_id;
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;
1871: END IF;
1872:
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;
1899: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1900: THEN
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;
1904: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1905: END IF;
1906:
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
1913: oe_debug_pub.add( 'CHECKING IF LINE IS PART OF ATO MODEL , SMC' ) ;
1914: END IF;
1915: SELECT ATO_LINE_ID, TOP_MODEL_LINE_ID,
1916: SHIP_MODEL_COMPLETE_FLAG, ITEM_TYPE_CODE,
1917: LINK_TO_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;
1929:
1930: IF l_debug_level > 0 THEN
1928: END;
1929:
1930: IF l_debug_level > 0 THEN
1931: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
1932: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
1933: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
1934: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
1935: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
1936: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
1929:
1930: IF l_debug_level > 0 THEN
1931: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
1932: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
1933: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
1934: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
1935: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
1936: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
1937: ELSE
1930: IF l_debug_level > 0 THEN
1931: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
1932: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
1933: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
1934: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
1935: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
1936: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
1937: ELSE
1938: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
1931: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
1932: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
1933: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
1934: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
1935: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
1936: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
1937: ELSE
1938: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
1939: END IF;
1932: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
1933: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
1934: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
1935: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
1936: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
1937: ELSE
1938: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
1939: END IF;
1940: END IF;
1934: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
1935: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
1936: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
1937: ELSE
1938: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
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 ) ;
1948: END IF;
1949:
1950: /*
1951: ** Fix Bug # 2984023
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:
1965: Check_Holds_line (
1966: p_hdr_id => p_hdr_id
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;
2006: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2007: IF l_debug_level > 0 THEN
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;
2011: END IF;
2012:
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;
2020:
2021: /*
2022: ** Fix Bug # 2984023
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;
2034:
2035: Check_Holds_line (
2036: p_hdr_id => p_hdr_id
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;
2076: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2077: IF l_debug_level > 0 THEN
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;
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
2090: Check_Holds_SMC (
2091: p_hdr_id => p_hdr_id
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;
2135:
2136: Check_Holds_line (
2137: p_hdr_id => p_hdr_id
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;
2159: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2160: IF l_debug_level > 0 THEN
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;
2164: END IF;
2165:
2169:
2170: END IF; /* IF LINE ID IS NOT NULL */
2171:
2172: IF l_debug_level > 0 THEN
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
2241: l_msg_count NUMBER;
2242: l_msg_data VARCHAR2(2000);
2243:
2244: --
2245: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2246: --
2247: BEGIN
2248: IF l_debug_level > 0 THEN
2249: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLD_SOURCES' ) ;
2245: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2246: --
2247: BEGIN
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:
2257:
2258: -- Check for Missing Input Parameters
2259: IF p_hold_entity_code IS NULL AND p_hold_entity_id IS NULL THEN
2260: IF l_debug_level > 0 THEN
2261: oe_debug_pub.add( 'ENTER HOLD_ENTITY_CODE OR HOLD_ENTITY_ID' ) ;
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;
2407: AND NVL(OH.HEADER_ID, 0) = NVL(NVL(p_header_id, OH.HEADER_ID), 0)
2408: AND NVL(OH.LINE_ID, 0) = NVL(NVL(p_line_id, OH.LINE_ID), 0)
2409: AND OH.HOLD_RELEASE_ID IS NULL;
2410: --
2411: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2412: --
2413: BEGIN
2414: IF l_debug_level > 0 THEN
2415: oe_debug_pub.add( 'IN RELEASE_HOLDS , OLD' ) ;
2411: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2412: --
2413: BEGIN
2414: IF l_debug_level > 0 THEN
2415: oe_debug_pub.add( 'IN RELEASE_HOLDS , OLD' ) ;
2416: END IF;
2417:
2418: l_org_id := MO_GLOBAL.get_current_org_id;
2419: IF l_org_id IS NULL THEN
2417:
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;
2440: -------------------------------------------------------------------
2441: IF p_hold_release_rec.hold_source_id IS NOT NULL THEN
2442:
2443: IF l_debug_level > 0 THEN
2444: oe_debug_pub.add( 'USING HOLD SOURCE ID' ) ;
2445: END IF;
2446: l_hold_source_id := p_hold_release_rec.hold_source_id;
2447:
2448: BEGIN
2478: -------------------------------------------------------------------
2479:
2480: ELSE
2481: IF l_debug_level > 0 THEN
2482: oe_debug_pub.add( 'HOLD SOURCE ID IS NOT PASSED' ) ;
2483: END IF;
2484: -- Check for Missing Values
2485: IF p_hold_id IS NULL THEN
2486: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_ID');
2527: -- Check to see if the Site code is Bill_to OR Ship_to
2528: l_hold_source_rec.hold_id := p_hold_id;
2529: IF p_entity_code = 'S' THEN
2530: IF l_debug_level > 0 THEN
2531: oe_debug_pub.add( 'CHECKING FOR SITE CODE , BILL TO OR SHIP TO' , 1 ) ;
2532: END IF;
2533: l_hold_source_rec.hold_entity_code := Hold_Site_Code(p_entity_id);
2534: ELSE
2535: l_hold_source_rec.hold_entity_code := p_entity_code;
2540: p_hold_release_rec.RELEASE_REASON_CODE;
2541: l_hold_release_rec.RELEASE_COMMENT := p_hold_release_rec.RELEASE_COMMENT;
2542:
2543: IF l_debug_level > 0 THEN
2544: oe_debug_pub.add( 'CALLING RELEASE HOLDS , OLD' ) ;
2545: END IF;
2546: oe_holds_pvt.Release_Holds(
2547: p_hold_source_rec => l_hold_source_rec
2548: ,p_hold_release_rec => l_hold_release_rec
2550: ,x_msg_count => x_msg_count
2551: ,x_msg_data => x_msg_data
2552: );
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
2649: l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
2650: --ER#7479609 end
2651:
2652: --
2653: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2654: --
2655: BEGIN
2656: l_org_id := MO_GLOBAL.get_current_org_id;
2657: IF l_org_id IS NULL THEN
2655: BEGIN
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;
2690: EXIT;
2691: END IF;
2692:
2693: IF l_debug_level > 0 THEN
2694: oe_debug_pub.add( 'DELETING ORDER HOLD RECORD' ) ;
2695: END IF;
2696:
2697: DELETE FROM OE_ORDER_HOLDS
2698: WHERE order_hold_id = l_order_hold_id;
2697: DELETE FROM OE_ORDER_HOLDS
2698: WHERE order_hold_id = l_order_hold_id;
2699:
2700: IF l_debug_level > 0 THEN
2701: oe_debug_pub.add( 'DELETING HOLD RELEASE RECORD' ) ;
2702: END IF;
2703: DELETE FROM OE_HOLD_RELEASES
2704: WHERE HOLD_RELEASE_ID = l_hold_release_id
2705: AND ORDER_HOLD_ID = l_order_hold_id;
2725: EXIT;
2726: END IF;
2727:
2728: IF l_debug_level > 0 THEN
2729: oe_debug_pub.add( 'DELETING HOLD SOURCE RECORD' ) ;
2730: END IF;
2731: DELETE FROM OE_HOLD_SOURCES
2732: WHERE HOLD_SOURCE_ID = l_hold_source_id;
2733:
2731: DELETE FROM OE_HOLD_SOURCES
2732: WHERE HOLD_SOURCE_ID = l_hold_source_id;
2733:
2734: IF l_debug_level > 0 THEN
2735: oe_debug_pub.add( 'DELETING HOLD RELEASE RECORD' ) ;
2736: END IF;
2737: DELETE FROM OE_HOLD_RELEASES
2738: WHERE HOLD_RELEASE_ID = l_hold_release_id;
2739:
2751: EXIT;
2752: END IF;
2753:
2754: IF l_debug_level > 0 THEN
2755: oe_debug_pub.add( 'DELETING LINE HOLD RECORD' ) ;
2756: END IF;
2757:
2758: DELETE FROM OE_ORDER_HOLDS
2759: WHERE order_hold_id = l_order_hold_id;
2789: EXIT;
2790: END IF;
2791:
2792: IF l_debug_level > 0 THEN
2793: oe_debug_pub.add( 'DELETING LINE HOLD RECORD FOR TOP MODEL WHEN OPTION ITEM LINE IS DELETED' ) ;
2794: END IF;
2795:
2796: DELETE FROM OE_ORDER_HOLDS
2797: WHERE order_hold_id = l_order_hold_id;
2966: BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
2967: AND NVL( HLD.END_DATE_ACTIVE, SYSDATE );
2968:
2969: --
2970: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2971: --
2972: --ER#7479609 start
2973: l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
2974: l_line_rec OE_ORDER_LINES_ALL%rowtype;
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;
2993:
2994: IF l_debug_level > 0 THEN
2995: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
2991: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS' , 1 ) ;
2992: END IF;
2993:
2994: IF l_debug_level > 0 THEN
2995: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
2996: END IF;
2997: IF l_debug_level > 0 THEN
2998: oe_debug_pub.add( 'HOLD ENTITY: '|| P_HOLD_ENTITY_CODE ||'/' || P_HOLD_ENTITY_ID ) ;
2999: END IF;
2994: IF l_debug_level > 0 THEN
2995: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
2996: END IF;
2997: IF l_debug_level > 0 THEN
2998: oe_debug_pub.add( 'HOLD ENTITY: '|| P_HOLD_ENTITY_CODE ||'/' || P_HOLD_ENTITY_ID ) ;
2999: END IF;
3000:
3001:
3002: --ER#7479609 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3013:
3014: EXCEPTION
3015: WHEN OTHERS THEN
3016: IF l_debug_level > 0 THEN
3017: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
3018: END IF;
3019: RAISE NO_DATA_FOUND;
3020: END;
3021: --ER#7479609 end
3019: RAISE NO_DATA_FOUND;
3020: END;
3021: --ER#7479609 end
3022: IF l_debug_level > 0 THEN
3023: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3024: END IF;
3025: --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3026: ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3027: p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3026: ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3027: p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3028: l_line_id := p_entity_id;
3029: IF l_debug_level > 0 THEN
3030: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3031: END IF;
3032: --ER#7479609 start
3033: BEGIN
3034: SELECT OH.* INTO l_header_rec
3040:
3041: EXCEPTION
3042: WHEN OTHERS THEN
3043: IF l_debug_level > 0 THEN
3044: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
3045: END IF;
3046: RAISE NO_DATA_FOUND;
3047: END;
3048: --ER#7479609 end
3082:
3083: EXCEPTION
3084: WHEN OTHERS THEN
3085: IF l_debug_level > 0 THEN
3086: oe_debug_pub.add( 'NO HEADER ID FOR THIS LINE' ) ;
3087: END IF;
3088: --RAISE NO_DATA_FOUND;
3089: null;
3090: END;
3089: null;
3090: END;
3091: --l_header_id := l_line_rec.header_id;
3092: IF l_debug_level > 0 THEN
3093: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3094: END IF;
3095: IF l_debug_level > 0 THEN
3096: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3097: END IF;
3092: IF l_debug_level > 0 THEN
3093: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3094: END IF;
3095: IF l_debug_level > 0 THEN
3096: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3097: END IF;
3098: END IF;
3099:
3100: --ER#7479609 start
3140: --ER#7479609 OPEN prev_hold;
3141: OPEN prev_hold(l_all_del_pay); --ER#7479609
3142: LOOP
3143: IF l_debug_level > 0 THEN
3144: oe_debug_pub.add( 'RETRIEVING PREV. HOLD RECORD' , 1 ) ;
3145: END IF;
3146:
3147: -- FETCH prev_hold INTO l_hold_entity_id, l_hold_source_id;
3148: --ER#7479609 FETCH prev_hold INTO l_hold_entity_id, l_order_hold_id;
3149: FETCH prev_hold INTO l_hold_entity_id, l_order_hold_id,l_hold_entity_code; --ER#7479609
3150:
3151: IF (prev_hold%notfound) THEN
3152: IF l_debug_level > 0 THEN
3153: oe_debug_pub.add( 'PREV_HOLD NOT FOUND , EXITING.' , 1 ) ;
3154: END IF;
3155: EXIT;
3156: END IF;
3157:
3156: END IF;
3157:
3158: IF l_hold_entity_id = p_hold_entity_id THEN
3159: IF l_debug_level > 0 THEN
3160: oe_debug_pub.add( 'SAME ENTITY ID: NEITHER APPLY NOR REMOVE' ) ;
3161: END IF;
3162: --RETURN;
3163: exit;
3164: END IF;
3165:
3166: --ER#7479609 start
3167: IF (l_hold_entity_code='P' and G_PAYMENT_HOLD_APPLIED = 'Y' and p_entity_code = OE_GLOBALS.G_ENTITY_HEADER) THEN
3168: IF l_debug_level > 0 THEN
3169: oe_debug_pub.add( 'NOT DELETING HOLD' , 1 ) ;
3170: END IF;
3171: ELSE
3172: --ER#7479609 end
3173: IF l_debug_level > 0 THEN
3170: END IF;
3171: ELSE
3172: --ER#7479609 end
3173: IF l_debug_level > 0 THEN
3174: oe_debug_pub.add( 'DELETING HOLD' , 1 ) ;
3175: END IF;
3176: DELETE FROM OE_ORDER_HOLDS
3177: WHERE ORDER_HOLD_ID = l_order_hold_id;
3178:
3176: DELETE FROM OE_ORDER_HOLDS
3177: WHERE ORDER_HOLD_ID = l_order_hold_id;
3178:
3179: IF l_debug_level > 0 THEN
3180: oe_debug_pub.add( 'L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3181: END IF;
3182: IF l_debug_level > 0 THEN
3183: oe_debug_pub.add( 'HOLD REMOVED' ) ;
3184: END IF;
3179: IF l_debug_level > 0 THEN
3180: oe_debug_pub.add( 'L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3181: END IF;
3182: IF l_debug_level > 0 THEN
3183: oe_debug_pub.add( 'HOLD REMOVED' ) ;
3184: END IF;
3185: END IF; --ER#7479609
3186: --fnd_message.set_name('ONT','OE_HOLD_REMOVED');
3187: --OE_MSG_PUB.ADD;
3193: -- Check for previous hold if the second entity is ('C','S','B','W','H','L')
3194: --ER#7479609 if p_hold_entity_code in ('C','S','B','W','H','L') then
3195: if p_hold_entity_code in ('B','CD','C','I','O','OT','P','PL','PR','H','SC','S','SM','TM','W','D') then --ER#7479609
3196: IF l_debug_level > 0 THEN
3197: oe_debug_pub.add( 'CHECKING PREV HOLD SOURCES FOR SECOND ENTITY' ) ;
3198: END IF;
3199:
3200: --ER#7479609 start
3201: l_all_del_pay := 'Y';
3206: --ER#7479609 OPEN prev_hold_entity2;
3207: OPEN prev_hold_entity2(l_all_del_pay); --ER#7479609
3208: LOOP
3209: IF l_debug_level > 0 THEN
3210: oe_debug_pub.add( 'RETRIEVING PREV. HOLD RECORD FOR ENTITY2' , 1 ) ;
3211: END IF;
3212:
3213: -- FETCH prev_hold INTO l_hold_entity_id, l_hold_source_id;
3214: --ER#7479609 FETCH prev_hold_entity2 INTO l_hold_entity_id, l_order_hold_id;
3215: FETCH prev_hold_entity2 INTO l_hold_entity_id, l_order_hold_id,l_hold_entity_code; --ER#7479609
3216:
3217: IF (prev_hold_entity2%notfound) THEN
3218: IF l_debug_level > 0 THEN
3219: oe_debug_pub.add( 'PREV_HOLD_ENTITY2 NOT FOUND , EXITING' , 1 ) ;
3220: END IF;
3221: EXIT;
3222: END IF;
3223:
3222: END IF;
3223:
3224: IF l_hold_entity_id = p_hold_entity_id THEN
3225: IF l_debug_level > 0 THEN
3226: oe_debug_pub.add( 'SAME ENTITY ID2: NEITHER APPLY NOR REMOVE' ) ;
3227: END IF;
3228: --RETURN;
3229: EXIT;
3230: END IF;
3231:
3232: --ER#7479609 start
3233: IF (l_hold_entity_code = 'P' and G_PAYMENT_HOLD_APPLIED = 'Y' and p_entity_code = OE_GLOBALS.G_ENTITY_HEADER) THEN
3234: IF l_debug_level > 0 THEN
3235: oe_debug_pub.add( 'NOT DELETING HOLD' , 1 ) ;
3236: END IF;
3237: ELSE
3238: --ER#7479609 end
3239: IF l_debug_level > 0 THEN
3236: END IF;
3237: ELSE
3238: --ER#7479609 end
3239: IF l_debug_level > 0 THEN
3240: oe_debug_pub.add( 'DELETING HOLD' , 1 ) ;
3241: END IF;
3242: DELETE FROM OE_ORDER_HOLDS
3243: WHERE ORDER_HOLD_ID = l_order_hold_id;
3244:
3242: DELETE FROM OE_ORDER_HOLDS
3243: WHERE ORDER_HOLD_ID = l_order_hold_id;
3244:
3245: IF l_debug_level > 0 THEN
3246: oe_debug_pub.add( 'L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3247: END IF;
3248: IF l_debug_level > 0 THEN
3249: oe_debug_pub.add( 'HOLD REMOVED FOR SECOND ENTITY' ) ;
3250: END IF;
3245: IF l_debug_level > 0 THEN
3246: oe_debug_pub.add( 'L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3247: END IF;
3248: IF l_debug_level > 0 THEN
3249: oe_debug_pub.add( 'HOLD REMOVED FOR SECOND ENTITY' ) ;
3250: END IF;
3251: END IF; --ER#7479609
3252: --fnd_message.set_name('ONT','OE_HOLD_REMOVED');
3253: --OE_MSG_PUB.ADD;
3260:
3261: OPEN curr_hold_source;
3262: LOOP
3263: IF l_debug_level > 0 THEN
3264: oe_debug_pub.add( 'RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
3265: END IF;
3266: FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
3267: l_hold_entity_code,l_hold_entity_id,
3268: l_hold_entity_code2,l_hold_entity_id2;
3268: l_hold_entity_code2,l_hold_entity_id2;
3269:
3270: IF (curr_hold_source%notfound) THEN
3271: IF l_debug_level > 0 THEN
3272: oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
3273: END IF;
3274: EXIT;
3275: END IF;
3276:
3274: EXIT;
3275: END IF;
3276:
3277: IF l_debug_level > 0 THEN
3278: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3279: oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
3280: END IF;
3281: -- If second entity is not null, check if order/line is eligible for hold
3282: l_create_order_hold_flag := 'Y';
3275: END IF;
3276:
3277: IF l_debug_level > 0 THEN
3278: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3279: oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
3280: END IF;
3281: -- If second entity is not null, check if order/line is eligible for hold
3282: l_create_order_hold_flag := 'Y';
3283: IF l_hold_entity_code2 is not null THEN
3458: END IF;
3459: --ER#7479609 end
3460: END IF; -- l_hold_entity_code2 is not null
3461: IF l_debug_level > 0 THEN
3462: oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
3463: END IF;
3464:
3465: --ER#7479609 start
3466: IF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3493: l_hold_source_rec.header_id := l_header_id;
3494: l_hold_source_rec.line_id := l_line_id;
3495:
3496: IF l_debug_level > 0 THEN
3497: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3498: END IF;
3499: oe_holds_pvt.Create_Order_Holds (
3500: p_hold_source_rec => l_hold_source_rec
3501: ,x_return_status => x_return_status
3503: ,x_msg_data => x_msg_data
3504: );
3505:
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
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
3515: RAISE FND_API.G_EXC_ERROR;
3516: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3523: --ER#7479609 end
3524: ELSE
3525:
3526: IF l_debug_level > 0 THEN
3527: oe_debug_pub.add( 'HOLD APPLIED' ) ;
3528: END IF;
3529:
3530: IF p_hold_entity_code = 'C' THEN
3531: l_attribute := 'Customer';
3637: -- ADD and p_entity_code = G_ENTITY_LINE ????
3638: OPEN curr_hold_source_entity2;
3639: LOOP
3640: IF l_debug_level > 0 THEN
3641: oe_debug_pub.add( 'RETRIEVING NEW HOLD SOURCE RECORD FOR ENTITY2' , 1 ) ;
3642: END IF;
3643: FETCH curr_hold_source_entity2 INTO l_hold_source_id, l_hold_id,
3644: l_hold_entity_code,l_hold_entity_id,
3645: l_hold_entity_code2,l_hold_entity_id2;
3645: l_hold_entity_code2,l_hold_entity_id2;
3646:
3647: IF (curr_hold_source_entity2%notfound) THEN
3648: IF l_debug_level > 0 THEN
3649: oe_debug_pub.add( 'NO HOLD SOURCE FOUND , EXISTING' , 1 ) ;
3650: END IF;
3651: EXIT;
3652: END IF;
3653:
3651: EXIT;
3652: END IF;
3653:
3654: IF l_debug_level > 0 THEN
3655: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3656: oe_debug_pub.add( ' '||L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
3657: END IF;
3658:
3659: -- If second entity is not null, The First entity can only be I or W
3652: END IF;
3653:
3654: IF l_debug_level > 0 THEN
3655: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3656: oe_debug_pub.add( ' '||L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
3657: END IF;
3658:
3659: -- If second entity is not null, The First entity can only be I or W
3660: -- Check if order/line is eligible for hold
3746: END IF;
3747: --ER#7479609 end
3748:
3749: IF l_debug_level > 0 THEN
3750: oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
3751: END IF;
3752:
3753: IF l_create_order_hold_flag = 'Y' THEN
3754: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
3760: l_hold_source_rec.header_id := l_header_id;
3761: l_hold_source_rec.line_id := l_line_id;
3762:
3763: IF l_debug_level > 0 THEN
3764: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3765: END IF;
3766: oe_holds_pvt.Create_Order_Holds (
3767: p_hold_source_rec => l_hold_source_rec
3768: ,x_return_status => x_return_status
3770: ,x_msg_data => x_msg_data
3771: );
3772:
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
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
3782: RAISE FND_API.G_EXC_ERROR;
3783: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3791: --ER#7479609 end
3792:
3793: ELSE
3794: IF l_debug_level > 0 THEN
3795: oe_debug_pub.add( 'HOLD APPLIED' ) ;
3796: END IF;
3797:
3798: IF l_hold_entity_code = 'C' THEN
3799: l_attribute := 'Customer';
3901: CLOSE curr_hold_source_entity2;
3902: end if;
3903:
3904: IF l_debug_level > 0 THEN
3905: oe_debug_pub.add( 'EXITING EVALUATE_HOLDS' , 1 ) ;
3906: END IF;
3907:
3908: EXCEPTION
3909:
4011: --6766981 AND rownum=1; --5999405
4012:
4013:
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:
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;
4023:
4024:
4025: IF l_debug_level > 0 THEN
4022: END IF;
4023:
4024:
4025: IF l_debug_level > 0 THEN
4026: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4027: END IF;
4028:
4029: l_header_id := p_entity_id;
4030:
4028:
4029: l_header_id := p_entity_id;
4030:
4031: IF l_debug_level > 0 THEN
4032: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4033: END IF;
4034:
4035: m_hold_entity_code := p_hold_entity_code;
4036: m_hold_entity_id := p_hold_entity_id;
4035: m_hold_entity_code := p_hold_entity_code;
4036: m_hold_entity_id := p_hold_entity_id;
4037:
4038: IF l_debug_level > 0 THEN
4039: oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4040: END IF;
4041: IF m_hold_entity_id IS NOT NULL THEN
4042: OPEN curr_hold_source(m_hold_entity_code, m_hold_entity_id);
4043: LOOP
4041: IF m_hold_entity_id IS NOT NULL THEN
4042: OPEN curr_hold_source(m_hold_entity_code, m_hold_entity_id);
4043: LOOP
4044: IF l_debug_level > 0 THEN
4045: oe_debug_pub.add( 'RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
4046: END IF;
4047: FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
4048: l_hold_entity_code,l_hold_entity_id,
4049: l_hold_entity_code2,l_hold_entity_id2;
4049: l_hold_entity_code2,l_hold_entity_id2;
4050:
4051: IF (curr_hold_source%notfound) THEN
4052: IF l_debug_level > 0 THEN
4053: oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4054: END IF;
4055: EXIT;
4056: END IF;
4057:
4055: EXIT;
4056: END IF;
4057:
4058: IF l_debug_level > 0 THEN
4059: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4060: oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
4061: END IF;
4062:
4063: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
4056: END IF;
4057:
4058: IF l_debug_level > 0 THEN
4059: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4060: oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
4061: END IF;
4062:
4063: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
4064: l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
4069: l_hold_source_rec.header_id := l_header_id;
4070: l_hold_source_rec.line_id := l_line_id;
4071:
4072: IF l_debug_level > 0 THEN
4073: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4074: END IF;
4075: oe_holds_pvt.Create_Order_Holds (
4076: p_hold_source_rec => l_hold_source_rec
4077: ,x_return_status => x_return_status
4080: );
4081:
4082:
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
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
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' ) ;
4099: END IF;
4100:
4101: l_attribute := 'Customer';
4102:
4109: CLOSE curr_hold_source;
4110: END IF; /* IF m_hold_entity_id IS NOT NULL THEN */
4111:
4112: IF l_debug_level > 0 THEN
4113: oe_debug_pub.add( 'EXITING EVAL_POST_WRITE_HEADER' , 1 ) ;
4114: END IF;
4115:
4116: EXCEPTION
4117:
4277: pay_idx NUMBER :=0;
4278:
4279: --ER#7479609 end
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:
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;
4289: IF l_debug_level > 0 THEN
4290: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4291: END IF;
4286: IF l_debug_level > 0 THEN
4287: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
4288: END IF;
4289: IF l_debug_level > 0 THEN
4290: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4291: END IF;
4292:
4293:
4294: IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4303:
4304: EXCEPTION
4305: WHEN OTHERS THEN
4306: IF l_debug_level > 0 THEN
4307: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
4308: END IF;
4309: RAISE NO_DATA_FOUND;
4310: END;
4311: --ER#7479609 end
4309: RAISE NO_DATA_FOUND;
4310: END;
4311: --ER#7479609 end
4312: IF l_debug_level > 0 THEN
4313: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4314: END IF;
4315:
4316:
4317: ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4316:
4317: ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4318: l_line_id := p_entity_id;
4319: IF l_debug_level > 0 THEN
4320: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4321: END IF;
4322:
4323: --ER#7479609 start
4324: BEGIN
4329:
4330: EXCEPTION
4331: WHEN OTHERS THEN
4332: IF l_debug_level > 0 THEN
4333: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
4334: END IF;
4335: RAISE NO_DATA_FOUND;
4336: END;
4337: --ER#7479609 end
4367:
4368: EXCEPTION
4369: WHEN OTHERS THEN
4370: IF l_debug_level > 0 THEN
4371: oe_debug_pub.add( 'NO HEADER ID FOR THIS LINE' ) ;
4372: END IF;
4373: RAISE NO_DATA_FOUND;
4374: END;
4375: IF l_debug_level > 0 THEN
4372: END IF;
4373: RAISE NO_DATA_FOUND;
4374: END;
4375: IF l_debug_level > 0 THEN
4376: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4377: END IF;
4378: IF l_debug_level > 0 THEN
4379: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4380: END IF;
4375: IF l_debug_level > 0 THEN
4376: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4377: END IF;
4378: IF l_debug_level > 0 THEN
4379: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4380: END IF;
4381: END IF;
4382:
4383: -- FOR m_hold_entity_code IN ('B', 'S', 'W', 'I','H') LOOP
4492: END IF; --ER#7479609
4493:
4494: FOR i IN l_entity_tab.first .. l_entity_tab.last LOOP --ER#7479609
4495: IF l_debug_level > 0 THEN
4496: -- oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4497: oe_debug_pub.add( 'HOLD_ENTITY_CODE/HOLD_ENTITY_ID:' || l_entity_tab(i).entity_code || '/' || l_entity_tab(i).entity_id , 1 ) ;
4498: END IF;
4499:
4500: --ER#7479609 IF m_hold_entity_id IS NOT NULL THEN
4493:
4494: FOR i IN l_entity_tab.first .. l_entity_tab.last LOOP --ER#7479609
4495: IF l_debug_level > 0 THEN
4496: -- oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4497: oe_debug_pub.add( 'HOLD_ENTITY_CODE/HOLD_ENTITY_ID:' || l_entity_tab(i).entity_code || '/' || l_entity_tab(i).entity_id , 1 ) ;
4498: END IF;
4499:
4500: --ER#7479609 IF m_hold_entity_id IS NOT NULL THEN
4501: IF l_entity_tab(i).entity_id IS NOT NULL THEN --ER#7479609
4502: --ER#7479609 OPEN curr_hold_source(m_hold_entity_code, m_hold_entity_id);
4503: OPEN curr_hold_source(l_entity_tab(i).entity_code, l_entity_tab(i).entity_id); --ER#7479609
4504: LOOP
4505: IF l_debug_level > 0 THEN
4506: oe_debug_pub.add( 'RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
4507: END IF;
4508:
4509: FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
4510: l_hold_entity_code,l_hold_entity_id,
4511: l_hold_entity_code2,l_hold_entity_id2;
4512:
4513: IF (curr_hold_source%notfound) THEN
4514: IF l_debug_level > 0 THEN
4515: oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4516: END IF;
4517:
4518: EXIT;
4519: END IF;
4519: END IF;
4520:
4521:
4522: IF l_debug_level > 0 THEN
4523: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4524: oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
4525: END IF;
4526:
4527: -- If second entity is not null, check if order/line is eligible for hold
4520:
4521:
4522: IF l_debug_level > 0 THEN
4523: oe_debug_pub.add( 'L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4524: oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
4525: END IF;
4526:
4527: -- If second entity is not null, check if order/line is eligible for hold
4528:
4634:
4635: END IF; -- l_hold_entity_code2 is not null
4636:
4637: IF l_debug_level > 0 THEN
4638: oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
4639: END IF;
4640:
4641: --ER#7479609 start
4642: IF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4668: l_hold_source_rec.header_id := l_header_id;
4669: l_hold_source_rec.line_id := l_line_id;
4670:
4671: IF l_debug_level > 0 THEN
4672: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4673: END IF;
4674: oe_holds_pvt.Create_Order_Holds (
4675: p_hold_source_rec => l_hold_source_rec
4676: ,x_return_status => x_return_status
4679: );
4680:
4681:
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
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
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' ) ;
4698: END IF;
4699:
4700: IF l_hold_entity_code = 'C' THEN
4701: l_attribute := 'Customer';
4813: ELSIF m_counter = 4 THEN
4814: m_hold_entity_code2 := 'W';
4815: m_hold_entity_id2 := l_ship_from_org_id;
4816: END IF;
4817: OE_Debug_PUB.Add('m_hold_entity_code2/m_hold_entity_id2:' ||
4818: m_hold_entity_code2 || '/' || m_hold_entity_id2, 1);
4819: IF m_hold_entity_id2 IS NOT NULL THEN
4820:
4821: OPEN curr_hold_source_entity2(m_hold_entity_code2,m_hold_entity_id2);
4819: IF m_hold_entity_id2 IS NOT NULL THEN
4820:
4821: OPEN curr_hold_source_entity2(m_hold_entity_code2,m_hold_entity_id2);
4822: LOOP
4823: OE_Debug_PUB.Add('Retrieving new hold source record for Entity2', 1);
4824: FETCH curr_hold_source_entity2 INTO l_hold_source_id, l_hold_id,
4825: l_hold_entity_code,l_hold_entity_id,
4826: l_hold_entity_code2,l_hold_entity_id2;
4827:
4825: l_hold_entity_code,l_hold_entity_id,
4826: l_hold_entity_code2,l_hold_entity_id2;
4827:
4828: IF (curr_hold_source_entity2%notfound) THEN
4829: OE_Debug_PUB.Add('No Hold Source found, existing', 1);
4830: EXIT;
4831: END IF;
4832:
4833: OE_Debug_PUB.Add('l_hold_entity_code/l_hold_entity_id/' ||
4829: OE_Debug_PUB.Add('No Hold Source found, existing', 1);
4830: EXIT;
4831: END IF;
4832:
4833: OE_Debug_PUB.Add('l_hold_entity_code/l_hold_entity_id/' ||
4834: 'l_hold_entity_code2/l_hold_entity_id2/l_hold_source_id:' ||
4835: l_hold_entity_code || '/' ||
4836: l_hold_entity_id || '/' ||
4837: l_hold_entity_code2 || '/' ||
4849: IF l_ship_from_org_id = l_hold_entity_id THEN
4850: l_create_order_hold_flag := 'Y';
4851: END IF;
4852: END IF;
4853: OE_DEBUG_PUB.Add('l_create_order_hold_flag:' || l_create_order_hold_flag,1);
4854:
4855: IF l_create_order_hold_flag = 'Y' THEN
4856: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
4857: l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
4861: l_hold_source_rec.hold_source_id := l_hold_source_id;
4862: l_hold_source_rec.header_id := l_header_id;
4863: l_hold_source_rec.line_id := l_line_id;
4864:
4865: oe_debug_pub.add('Calling oe_holds_pvt.create_order_holds');
4866: oe_holds_pvt.Create_Order_Holds (
4867: p_hold_source_rec => l_hold_source_rec
4868: ,x_return_status => x_return_status
4869: ,x_msg_count => x_msg_count
4869: ,x_msg_count => x_msg_count
4870: ,x_msg_data => x_msg_data
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
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
4880: RAISE FND_API.G_EXC_UNEXPECTED_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:
4885: IF l_hold_entity_code = 'C' THEN
4886: l_attribute := 'Customer';
4887: ELSIF l_hold_entity_code = 'I' THEN
4930: END IF;
4931: END LOOP;
4932: */
4933: IF l_debug_level > 0 THEN
4934: oe_debug_pub.add( 'EXITING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
4935: END IF;
4936:
4937: EXCEPTION
4938:
4974: ) RETURN boolean IS
4975:
4976: l_hold_exists VARCHAR2(1) := 'N';
4977:
4978: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4979:
4980: BEGIN
4981: IF l_debug_level > 0 THEN
4982: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Hold_Exists',1 ) ;
4978: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4979:
4980: BEGIN
4981: IF l_debug_level > 0 THEN
4982: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Hold_Exists',1 ) ;
4983: END IF;
4984: IF p_org_id IS NULL THEN
4985:
4986: SELECT 'Y'
5003: AND ORG_ID = p_org_id;
5004:
5005: END IF;
5006: IF l_debug_level > 0 THEN
5007: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Hold_Exists',1 ) ;
5008: END IF;
5009:
5010: IF l_hold_exists = 'Y' THEN
5011: return true;
5017: WHEN NO_DATA_FOUND THEN
5018: return false;
5019: WHEN TOO_MANY_ROWS THEN
5020: IF l_debug_level > 0 THEN
5021: oe_debug_pub.add( 'TOO_MANY_ROWS exception in OE_HOLDS_PUB.Hold_Exists' ) ;
5022: END IF;
5023: return true;
5024: WHEN others THEN
5025: IF l_debug_level > 0 THEN
5022: END IF;
5023: return true;
5024: WHEN others THEN
5025: IF l_debug_level > 0 THEN
5026: oe_debug_pub.add( 'Exception in OE_HOLDS_PUB.Hold_Exists' ) ;
5027: END IF;
5028: return false;
5029: END Hold_exists;
5030:
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:
5055: CURSOR c_hold_orgs IS
5056: SELECT DISTINCT org_id
5057: FROM HZ_CUST_ACCT_SITES_ALL acct
5088: AND nvl(released_flag,'N') = 'N';
5089:
5090: BEGIN
5091: IF l_debug_level > 0 THEN
5092: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5093: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||' EntityID:'|| p_hold_entity_id||' HoldId: ' || p_hold_id||' p_Action: ' || p_action, 1);
5094: END IF;
5095: l_hold_source_rec.hold_entity_code := p_hold_entity_code;
5096: l_hold_source_rec.hold_entity_id := p_hold_entity_id;
5089:
5090: BEGIN
5091: IF l_debug_level > 0 THEN
5092: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5093: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||' EntityID:'|| p_hold_entity_id||' HoldId: ' || p_hold_id||' p_Action: ' || p_action, 1);
5094: END IF;
5095: l_hold_source_rec.hold_entity_code := p_hold_entity_code;
5096: l_hold_source_rec.hold_entity_id := p_hold_entity_id;
5097: l_hold_source_rec.hold_id := p_hold_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);
5109: END IF;
5110:
5111: MO_GLOBAL.set_policy_context('S',l_org_id);
5112:
5118: ,x_msg_count => x_msg_count
5119: ,x_msg_data => x_msg_data );
5120:
5121: IF l_debug_level > 0 THEN
5122: oe_debug_pub.add('oe_holds_pvt.apply_Holds:x_return_status ' || x_return_status, 1);
5123: END IF;
5124:
5125: l_return_status := x_return_status;
5126:
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;
5142:
5143: l_hold_source_rec.hold_source_id := l_hold_source_id;
5144:
5151: ,x_msg_count => x_msg_count
5152: ,x_msg_data => x_msg_data);
5153:
5154: IF l_debug_level > 0 THEN
5155: oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status ' || x_return_status, 1);
5156: END IF;
5157:
5158: l_return_status := x_return_status;
5159:
5159:
5160: END LOOP;
5161: END IF;
5162: IF l_debug_level > 0 THEN
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
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);
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
5183: OE_MSG_PUB.Count_And_Get
5184: ( p_count => x_msg_count
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 ) ;
5193: END IF;
5194: RAISE;
5195:
5196: END Process_Holds_Customer;
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
5222: IF l_debug_level > 0 THEN
5223: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5224: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||'::EntityID: ' || p_hold_entity_id||'::HoldId: ' || p_hold_id||'::p_Action: ' || p_action, 1);
5219: l_org_id NUMBER := null;
5220: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5221: BEGIN
5222: IF l_debug_level > 0 THEN
5223: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5224: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||'::EntityID: ' || p_hold_entity_id||'::HoldId: ' || p_hold_id||'::p_Action: ' || p_action, 1);
5225: END IF;
5226:
5227: -- Fetch the org_id of the site that is being sent to put on hold
5220: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5221: BEGIN
5222: IF l_debug_level > 0 THEN
5223: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5224: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||'::EntityID: ' || p_hold_entity_id||'::HoldId: ' || p_hold_id||'::p_Action: ' || p_action, 1);
5225: END IF;
5226:
5227: -- Fetch the org_id of the site that is being sent to put on hold
5228: -- The reason, the caller may not have set the correct org
5234: WHERE site_use_id = p_hold_entity_id;
5235:
5236: IF l_org_id is null then
5237: IF l_debug_level > 0 THEN
5238: oe_debug_pub.add( 'ORG_ID for the site is null. Invalid site ||p_hold_entity_id' , 1 ) ;
5239: END IF;
5240: raise no_data_found; -- Should not occur at all
5241: END IF;
5242: EXCEPTION
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);
5254: END IF;
5255:
5256: MO_GLOBAL.set_policy_context('S',l_org_id);
5257:
5278: ,x_msg_count => x_msg_count
5279: ,x_msg_data => x_msg_data );
5280:
5281: IF l_debug_level > 0 THEN
5282: oe_debug_pub.add('oe_holds_pvt.apply:x_return_status: ' || x_return_status, 1);
5283: END IF;
5284:
5285: -- Exceptions and return status are checked in process_holds
5286: END IF; -- hold not exists
5294: ,x_msg_data => x_msg_data);
5295:
5296:
5297: IF l_debug_level > 0 THEN
5298: oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status: ' || x_return_status, 1);
5299: END IF;
5300:
5301: IF l_debug_level > 0 THEN
5302: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5298: oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status: ' || x_return_status, 1);
5299: END IF;
5300:
5301: IF l_debug_level > 0 THEN
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
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;
5311:
5312: IF x_msg_count is not null then
5313: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
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:
5324: FND_MSG_PUB.Count_And_Get
5325: ( p_count => x_msg_count
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 ) ;
5334: END IF;
5335: RAISE;
5336: END Process_Holds_Site;
5337:
5369: l_msg_count NUMBER := null;
5370: l_msg_data VARCHAR2(2000) := null;
5371: l_valid_acct VARCHAR2(1) := 'N';
5372:
5373: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5374:
5375: CURSOR c_valid_acct IS
5376: SELECT 'Y'
5377: FROM HZ_CUST_ACCOUNTS_ALL
5379:
5380: BEGIN
5381:
5382: IF l_debug_level > 0 THEN
5383: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5384: END IF;
5385:
5386: SAVEPOINT oe_process_holds;
5387:
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;
5424:
5425: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5426: RAISE FND_API.G_EXC_ERROR;
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;
5463:
5464: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5465: RAISE FND_API.G_EXC_ERROR;
5478:
5479: x_return_status := l_return_status;
5480:
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:
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;
5490: x_return_status := l_return_status;
5491: x_msg_count := l_msg_count;
5492: x_msg_data := l_msg_data;
5495: p_data => l_msg_data);
5496: end if;
5497:
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:
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;
5509: x_return_status := l_return_status;
5510: x_msg_count := l_msg_count;
5511: x_msg_data := l_msg_data;
5513: ( p_count => l_msg_count
5514: , p_data => l_msg_data
5515: );
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:
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 ) ;
5525: END IF;
5526: x_return_status := l_return_status;
5527: x_msg_count := l_msg_count;
5528: x_msg_data := l_msg_data;