[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;
164: end if;
165:
166: else
167: IF l_debug_level > 0 THEN
168: oe_debug_pub.add( 'OE_HOLDS_PUB.UPDATE_HOLD_COMMENTS:' || 'EITHER PASS HOLD_SOURCE_ID OR HOLD_ENTITY_CODE/HOLD_ENTITY_ID' ) ;
169: END IF;
170: RAISE FND_API.G_EXC_ERROR;
171: end if;
172:
174:
175: WHEN FND_API.G_EXC_ERROR THEN
176: x_return_status := FND_API.G_RET_STS_ERROR ;
177: IF l_debug_level > 0 THEN
178: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
179: END IF;
180: OE_MSG_PUB.Count_And_Get
181: ( p_count => x_msg_count,
182: p_data => x_msg_data
184:
185: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
187: IF l_debug_level > 0 THEN
188: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
189: END IF;
190: OE_MSG_PUB.Count_And_Get
191: ( p_count => x_msg_count,
192: p_data => x_msg_data
194:
195: WHEN OTHERS THEN
196: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197: IF l_debug_level > 0 THEN
198: oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
199: END IF;
200: IF OE_MSG_PUB.Check_Msg_Level
201: (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
202: THEN
241: from oe_hold_sources
242: where HOLD_UNTIL_DATE <= sysdate
243: and released_flag = 'N';
244: --
245: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
246: --
247: BEGIN
248: IF l_debug_level > 0 THEN
249: oe_debug_pub.add( 'IN RELEASE EXPIRED HOLDS' ) ;
245: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
246: --
247: BEGIN
248: IF l_debug_level > 0 THEN
249: oe_debug_pub.add( 'IN RELEASE EXPIRED HOLDS' ) ;
250: oe_debug_pub.add( 'p_org_id:' || to_char(p_org_id) ) ;
251: END IF;
252: l_return_status := FND_API.G_RET_STS_SUCCESS;
253:
246: --
247: BEGIN
248: IF l_debug_level > 0 THEN
249: oe_debug_pub.add( 'IN RELEASE EXPIRED HOLDS' ) ;
250: oe_debug_pub.add( 'p_org_id:' || to_char(p_org_id) ) ;
251: END IF;
252: l_return_status := FND_API.G_RET_STS_SUCCESS;
253:
254: If p_org_id IS Not Null Then
263: open expired_holds_cur;
264: LOOP
265: fetch expired_holds_cur into l_hold_source_id, l_org_id;
266: if (expired_holds_cur%notfound) then
267: oe_debug_pub.add('Exiting expired_holds_cur%notfound:') ;
268: exit;
269: end if;
270: IF l_debug_level > 0 THEN
271: oe_debug_pub.add('RELEASE EXPIRED HOLD FOR:' || TO_CHAR (L_HOLD_SOURCE_ID) ||
267: oe_debug_pub.add('Exiting expired_holds_cur%notfound:') ;
268: exit;
269: end if;
270: IF l_debug_level > 0 THEN
271: oe_debug_pub.add('RELEASE EXPIRED HOLD FOR:' || TO_CHAR (L_HOLD_SOURCE_ID) ||
272: ', ORG ID:' || TO_CHAR(l_org_id) );
273: END IF;
274:
275: if l_org_id <> nvl(l_curr_org_id, -99)
273: END IF;
274:
275: if l_org_id <> nvl(l_curr_org_id, -99)
276: then
277: oe_debug_pub.add('Setting Policy Context for:' || TO_CHAR(l_org_id) ) ;
278: MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id);
279: l_curr_org_id := l_org_id;
280: oe_debug_pub.add('MO_GLOBAL.get_current_org_id;:' || to_char(MO_GLOBAL.get_current_org_id));
281: End if;
276: then
277: oe_debug_pub.add('Setting Policy Context for:' || TO_CHAR(l_org_id) ) ;
278: MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id);
279: l_curr_org_id := l_org_id;
280: oe_debug_pub.add('MO_GLOBAL.get_current_org_id;:' || to_char(MO_GLOBAL.get_current_org_id));
281: End if;
282:
283: l_hold_source_rec.hold_source_id := l_hold_source_id;
284: l_hold_release_rec.RELEASE_REASON_CODE := 'EXPIRE';
292: ,x_msg_count => l_msg_count
293: ,x_msg_data => l_msg_data
294: );
295: IF l_debug_level > 0 THEN
296: oe_debug_pub.add( 'X_RETURN_STATUS:' || L_RETURN_STATUS , 1 ) ;
297: END IF;
298:
299: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
300: IF l_debug_level > 0 THEN
297: END IF;
298:
299: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
300: IF l_debug_level > 0 THEN
301: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS UNEXPECTED FAILURE' ) ;
302: END IF;
303: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
305: IF l_debug_level > 0 THEN
302: END IF;
303: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
305: IF l_debug_level > 0 THEN
306: oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS EXPECTED FAILURE' ) ;
307: END IF;
308: RAISE FND_API.G_EXC_ERROR;
309: END IF;
310:
364: IS
365: l_api_name CONSTANT VARCHAR2(30) := 'ValidateOrder';
366: l_dummy VARCHAR2(30);
367: --
368: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
369: --
370: BEGIN
371:
372: IF l_debug_level > 0 THEN
369: --
370: BEGIN
371:
372: IF l_debug_level > 0 THEN
373: oe_debug_pub.add( 'IN OE_HOLDS_PUB.VALIDATEORDER' ) ;
374: END IF;
375:
376: -- Initialize API return status to success
377: x_return_status := FND_API.G_RET_STS_SUCCESS;
412: AND ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
413: AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
414: EXCEPTION
415: WHEN NO_DATA_FOUND THEN
416: oe_debug_pub.add('In No Data Found for 13331078 HEI= '||p_hold_entity_id||' LineId= '||p_line_id);
417: END;
418:
419:
420: --ER# 13331078 end
589: WHEN FND_API.G_EXC_ERROR THEN
590: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTITY_NOT_ON_ORDER_OR_LINE');
591: OE_MSG_PUB.ADD;
592: IF l_debug_level > 0 THEN
593: oe_debug_pub.add( 'EXPECTED ERROR IN VALIDATEORDER' ) ;
594: END IF;
595: x_return_status := FND_API.G_RET_STS_ERROR;
596: WHEN OTHERS THEN
597: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629: IS
630: l_api_name CONSTANT VARCHAR2(30) := 'Apply_Holds';
631: --
632: l_org_id number;
633: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
634: --
635: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
636: BEGIN
637: l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
635: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
636: BEGIN
637: l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
638: IF l_debug_level > 0 THEN
639: oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS' ) ;
640: END IF;
641:
642: SAVEPOINT APPLY_HOLDS_PUB; -- 11803186 Adding a new SAVEPOINT to get the new Apply_Holds proc in synch with the Old
643:
643:
644: l_org_id := MO_GLOBAL.get_current_org_id;
645: IF l_org_id IS NULL THEN
646: -- org_id is null, raise an error.
647: oe_debug_pub.add('Org_Id is NULL',1);
648: x_return_status := FND_API.G_RET_STS_ERROR;
649: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
650: FND_MSG_PUB.ADD;
651: RAISE FND_API.G_EXC_ERROR;
667: EXCEPTION
668: WHEN FND_API.G_EXC_ERROR THEN
669: ROLLBACK TO APPLY_HOLDS_PUB;
670: IF l_debug_level > 0 THEN
671: oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
672: END IF;
673: x_return_status := FND_API.G_RET_STS_ERROR;
674: FND_MSG_PUB.Count_And_Get
675: ( p_count => x_msg_count
716: IS
717: l_api_name CONSTANT VARCHAR2(30) := 'Apply_Holds';
718: --
719: l_org_id number;
720: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
721: --
722: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
723: BEGIN
724: l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
722: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
723: BEGIN
724: l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
725: IF l_debug_level > 0 THEN
726: oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS , CREATING HOLD SOURCE' ) ;
727: END IF;
728: l_org_id := MO_GLOBAL.get_current_org_id;
729: IF l_org_id IS NULL THEN
730: -- org_id is null, raise an error.
727: END IF;
728: l_org_id := MO_GLOBAL.get_current_org_id;
729: IF l_org_id IS NULL THEN
730: -- org_id is null, raise an error.
731: oe_debug_pub.add('Org_Id is NULL',1);
732: x_return_status := FND_API.G_RET_STS_ERROR;
733: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
734: FND_MSG_PUB.ADD;
735: RAISE FND_API.G_EXC_ERROR;
748: ,x_msg_count => x_msg_count
749: ,x_msg_data => x_msg_data
750: );
751: IF l_debug_level > 0 THEN
752: oe_debug_pub.add( 'OE_HOLDS_PUB.APPLY_HOLDS , HOLD SOURCE:' ||X_RETURN_STATUS ) ;
753: END IF;
754:
755: EXCEPTION
756: WHEN FND_API.G_EXC_ERROR THEN
754:
755: EXCEPTION
756: WHEN FND_API.G_EXC_ERROR THEN
757: IF l_debug_level > 0 THEN
758: oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
759: END IF;
760: x_return_status := FND_API.G_RET_STS_ERROR;
761: FND_MSG_PUB.Count_And_Get
762: ( p_count => x_msg_count
802: IS
803: l_api_name CONSTANT VARCHAR2(30) := 'release_holds';
804: --
805: l_org_id number;
806: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
807: --
808: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
809: BEGIN
810: l_check_authorization_flag:=p_check_authorization_flag; --8477694
808: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
809: BEGIN
810: l_check_authorization_flag:=p_check_authorization_flag; --8477694
811: IF l_debug_level > 0 THEN
812: oe_debug_pub.add( 'IN OE_HOLDS_PUB.RELEASE_HOLDS' ) ;
813: END IF;
814: l_org_id := MO_GLOBAL.get_current_org_id;
815: IF l_org_id IS NULL THEN
816: -- org_id is null, raise an error.
813: END IF;
814: l_org_id := MO_GLOBAL.get_current_org_id;
815: IF l_org_id IS NULL THEN
816: -- org_id is null, raise an error.
817: oe_debug_pub.add('Org_Id is NULL',1);
818: x_return_status := FND_API.G_RET_STS_ERROR;
819: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
820: FND_MSG_PUB.ADD;
821: RAISE FND_API.G_EXC_ERROR;
835: );
836: EXCEPTION
837: WHEN FND_API.G_EXC_ERROR THEN
838: IF l_debug_level > 0 THEN
839: oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
840: END IF;
841: x_return_status := FND_API.G_RET_STS_ERROR;
842: FND_MSG_PUB.Count_And_Get
843: ( p_count => x_msg_count
887: j NUMBER;
888: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
889: l_org_id number;
890: --
891: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
892: --
893: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
894: BEGIN
895: l_check_authorization_flag :=p_check_authorization_flag; -- bug 8477694
893: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
894: BEGIN
895: l_check_authorization_flag :=p_check_authorization_flag; -- bug 8477694
896: IF l_debug_level > 0 THEN
897: oe_debug_pub.add( 'IN OE_HOLDS_PUB.RELEASE_HOLDS' ) ;
898: END IF;
899:
900: l_org_id := MO_GLOBAL.get_current_org_id;
901: IF l_org_id IS NULL THEN
899:
900: l_org_id := MO_GLOBAL.get_current_org_id;
901: IF l_org_id IS NULL THEN
902: -- org_id is null, raise an error.
903: oe_debug_pub.add('Org_Id is NULL',1);
904: x_return_status := FND_API.G_RET_STS_ERROR;
905: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
906: FND_MSG_PUB.ADD;
907: RAISE FND_API.G_EXC_ERROR;
942:
943: EXCEPTION
944: WHEN FND_API.G_EXC_ERROR THEN
945: IF l_debug_level > 0 THEN
946: oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
947: END IF;
948: x_return_status := FND_API.G_RET_STS_ERROR;
949: FND_MSG_PUB.Count_And_Get
950: ( p_count => x_msg_count
1023: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
1024: l_site_use_code VARCHAR2(30);
1025: l_org_id number;
1026: --
1027: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1028: --
1029: l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
1030: BEGIN
1031: l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
1031: l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
1032: l_org_id := MO_GLOBAL.get_current_org_id;
1033: IF l_org_id IS NULL THEN
1034: -- org_id is null, raise an error.
1035: oe_debug_pub.add('Org_Id is NULL',1);
1036: x_return_status := FND_API.G_RET_STS_ERROR;
1037: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
1038: FND_MSG_PUB.ADD;
1039: RAISE FND_API.G_EXC_ERROR;
1039: RAISE FND_API.G_EXC_ERROR;
1040: END IF;
1041:
1042: IF l_debug_level > 0 THEN
1043: oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS OLD' ) ;
1044: END IF;
1045:
1046: SAVEPOINT APPLY_HOLDS_PUB;
1047:
1056: ----------------------------------------------------------------
1057: IF p_hold_source_id IS NOT NULL THEN
1058:
1059: IF l_debug_level > 0 THEN
1060: oe_debug_pub.add( 'USING INPUT HOLD SOURCE ID' ) ;
1061: END IF;
1062: l_hold_source_id := p_hold_source_id;
1063:
1064: --IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
1077:
1078: EXCEPTION
1079: WHEN NO_DATA_FOUND THEN
1080: IF l_debug_level > 0 THEN
1081: oe_debug_pub.add( 'INVALID HOLD SOURCE ID' ) ;
1082: END IF;
1083: FND_MESSAGE.SET_NAME('ONT','OE_INVALID_HOLD_SOURCE_ID');
1084: FND_MESSAGE.SET_TOKEN('HOLD_SOURCE_ID' , p_hold_source_id);
1085: OE_MSG_PUB.ADD;
1117: -- ONLY needed coz AR still calls the old holds api (oe_holds) with
1118: -- S as hold_entity_code for Bill To.
1119: if p_hold_source_rec.hold_entity_code = 'S' THEN
1120: IF l_debug_level > 0 THEN
1121: oe_debug_pub.add( 'CHECKING FOR SITE CODE , BILL TO OR SHIP TO' , 1 ) ;
1122: END IF;
1123: l_entity_code := Hold_Site_Code(p_hold_source_rec.hold_entity_id);
1124: ELSE
1125: l_entity_code := p_hold_source_rec.hold_entity_code;
1158:
1159: IF (sql%found) THEN
1160: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
1161: OE_MSG_PUB.ADD;
1162: OE_Debug_PUB.Add('Duplicate Hold');
1163: RAISE FND_API.G_EXC_ERROR;
1164: END IF;
1165: EXCEPTION
1166: WHEN NO_DATA_FOUND THEN
1179: , x_return_status => x_return_status
1180: );
1181:
1182: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1183: OE_Debug_PUB.Add('Validate Order not successful');
1184: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1185: RAISE FND_API.G_EXC_ERROR;
1186: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1187: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1186: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1187: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1188: END IF;
1189: ELSE
1190: OE_Debug_PUB.Add('Validate Order successful');
1191: END IF;
1192: END IF; -- p_hold_source_rec.hold_entity_code = 'O'
1193: */
1194:
1213:
1214: --dbms_output.put_line ('AH-hold_id' ||to_char(p_hold_source_rec.hold_id)); -- delete
1215: --dbms_output.put_line ('AH-B4ApplyHolds' ); -- delete
1216: IF l_debug_level > 0 THEN
1217: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.APPLY_HOLDS' ) ;
1218: END IF;
1219: oe_holds_pvt.apply_Holds(
1220: p_hold_source_rec => l_hold_source_rec
1221: ,p_hold_existing_flg => 'Y'
1225: ,x_msg_count => x_msg_count
1226: ,x_msg_data => x_msg_data
1227: );
1228: IF l_debug_level > 0 THEN
1229: oe_debug_pub.add( 'OE_HOLDS_PVT.APPLY_HOLDS STATUS:' || X_RETURN_STATUS ) ;
1230: END IF;
1231: --dbms_output.put_line('AH-x_return_status' || x_return_status ); -- delete
1232: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1233: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1240: EXCEPTION
1241: WHEN FND_API.G_EXC_ERROR THEN
1242: ROLLBACK TO APPLY_HOLDS_PUB;
1243: IF l_debug_level > 0 THEN
1244: oe_debug_pub.add( 'FROM DUPLICATE HOLD EXPECTED ERROR' ) ;
1245: END IF;
1246: x_return_status := FND_API.G_RET_STS_ERROR;
1247: FND_MSG_PUB.Count_And_Get
1248: ( p_count => x_msg_count
1301: IS
1302: l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_line';
1303: l_dummy VARCHAR2(30);
1304: --
1305: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1306: --
1307: BEGIN
1308: IF l_debug_level > 0 THEN
1309: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_LINE:' || P_LINE_ID ) ;
1305: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1306: --
1307: BEGIN
1308: IF l_debug_level > 0 THEN
1309: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_LINE:' || P_LINE_ID ) ;
1310: END IF;
1311: -- Initialize API return status to success
1312: x_return_status := FND_API.G_RET_STS_SUCCESS;
1313:
1369: EXCEPTION
1370: WHEN NO_DATA_FOUND THEN
1371: x_result_out := FND_API.G_FALSE;
1372: IF l_debug_level > 0 THEN
1373: oe_debug_pub.add( 'NO HOLDS FOUND FOR LINE ID: ' || P_LINE_ID ) ;
1374: END IF;
1375: WHEN TOO_MANY_ROWS THEN
1376: null;
1377: END;
1376: null;
1377: END;
1378:
1379: IF l_debug_level > 0 THEN
1380: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_LINE' ) ;
1381: END IF;
1382:
1383: EXCEPTION
1384: WHEN FND_API.G_EXC_ERROR THEN
1438: IS
1439: l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_ATO';
1440: l_dummy VARCHAR2(30);
1441: --
1442: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1443: --
1444: BEGIN
1445: IF l_debug_level > 0 THEN
1446: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1442: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1443: --
1444: BEGIN
1445: IF l_debug_level > 0 THEN
1446: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1447: END IF;
1448: -- Initialize API return status to success
1449: x_return_status := FND_API.G_RET_STS_SUCCESS;
1450:
1496: EXCEPTION
1497: WHEN NO_DATA_FOUND THEN
1498: x_result_out := FND_API.G_FALSE;
1499: IF l_debug_level > 0 THEN
1500: oe_debug_pub.add( 'NO HOLDS FOUND FOR ATO LINE ID: ' || P_ATO_LINE_ID ) ;
1501: END IF;
1502: WHEN TOO_MANY_ROWS THEN
1503: null;
1504: END;
1503: null;
1504: END;
1505:
1506: IF l_debug_level > 0 THEN
1507: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1508: END IF;
1509:
1510: EXCEPTION
1511: WHEN FND_API.G_EXC_ERROR THEN
1565: IS
1566: l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_SMC';
1567: l_dummy VARCHAR2(30);
1568: --
1569: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1570: --
1571: BEGIN
1572: IF l_debug_level > 0 THEN
1573: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1569: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1570: --
1571: BEGIN
1572: IF l_debug_level > 0 THEN
1573: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1574: END IF;
1575: -- Initialize API return status to success
1576: x_return_status := FND_API.G_RET_STS_SUCCESS;
1577:
1622: EXCEPTION
1623: WHEN NO_DATA_FOUND THEN
1624: x_result_out := FND_API.G_FALSE;
1625: IF l_debug_level > 0 THEN
1626: oe_debug_pub.add( 'NO HOLDS FOUND FOR TOP MODEL LINE ID: ' || P_TOP_MODEL_LINE_ID ) ;
1627: END IF;
1628: WHEN TOO_MANY_ROWS THEN
1629: null;
1630: END;
1629: null;
1630: END;
1631:
1632: IF l_debug_level > 0 THEN
1633: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1634: END IF;
1635:
1636: EXCEPTION
1637: WHEN FND_API.G_EXC_ERROR THEN
1680: IS
1681: l_api_name CONSTANT VARCHAR2(30) := 'Check_Any_Line_Hold';
1682: l_dummy VARCHAR2(30);
1683: --
1684: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1685: --
1686: BEGIN
1687: -- Initialize API return status to success
1688: x_return_status := FND_API.G_RET_STS_SUCCESS;
1729: EXCEPTION
1730: WHEN NO_DATA_FOUND THEN
1731: x_hold_rec.x_result_out := FND_API.G_FALSE;
1732: IF l_debug_level > 0 THEN
1733: oe_debug_pub.add( 'NO HOLDS FOUND FOR ANY OF THE LINES' ) ;
1734: END IF;
1735: WHEN TOO_MANY_ROWS THEN
1736: null;
1737: END;
1830: select line_id
1831: from oe_order_lines_all
1832: where top_model_line_id = l_top_model_line_id;
1833: --
1834: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1835: --
1836: --7832836 l_cascade_hold_non_smc VARCHAR2(1) := NVL(OE_SYS_PARAMETERS.VALUE('ONT_CASCADE_HOLD_NONSMC_PTO'),'N'); --ER#7479609
1837: l_cascade_hold_non_smc VARCHAR2(1); -- 7832836
1838: l_org_id NUMBER; -- 7832836
1837: l_cascade_hold_non_smc VARCHAR2(1); -- 7832836
1838: l_org_id NUMBER; -- 7832836
1839: BEGIN
1840: IF l_debug_level > 0 THEN
1841: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS' ) ;
1842: END IF;
1843:
1844: -- Initialize API return status to success
1845: x_return_status := FND_API.G_RET_STS_SUCCESS;
1873: EXCEPTION
1874: WHEN NO_DATA_FOUND THEN
1875: x_result_out := FND_API.G_FALSE;
1876: IF l_debug_level > 0 THEN
1877: oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 1: ' || P_LINE_ID ) ;
1878: END IF;
1879: End;
1880: ELSE
1881: p_hdr_id := p_header_id;
1881: p_hdr_id := p_header_id;
1882: END IF;
1883:
1884: IF l_debug_level > 0 THEN
1885: oe_debug_pub.add( 'CHECKING HOLD ON HEADER ID: ' || P_HDR_ID ) ;
1886: END IF;
1887:
1888: --7832836 start
1889: l_org_id := MO_GLOBAL.get_current_org_id;
1944: EXCEPTION
1945: WHEN NO_DATA_FOUND THEN
1946: x_result_out := FND_API.G_FALSE;
1947: IF l_debug_level > 0 THEN
1948: oe_debug_pub.add( 'NO HOLDS FOUND FOR HEADER ID: ' || P_HDR_ID ) ;
1949: END IF;
1950: WHEN TOO_MANY_ROWS THEN
1951: null;
1952: END;
1953:
1954: -- Return TRUE if Header Level Hold exists
1955: IF x_result_out = FND_API.G_TRUE THEN
1956: IF l_debug_level > 0 THEN
1957: oe_debug_pub.add( 'HEADER LEVEL HOLD EXISTS' ) ;
1958: END IF;
1959: RETURN;
1960: END IF;
1961:
1981:
1982: -- Raise if the l_return_status is unexpected error
1983: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1984: IF l_debug_level > 0 THEN
1985: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
1986: END IF;
1987: RAISE FND_API.G_EXC_ERROR;
1988: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1989: THEN
1987: RAISE FND_API.G_EXC_ERROR;
1988: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1989: THEN
1990: IF l_debug_level > 0 THEN
1991: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
1992: END IF;
1993: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1994: END IF;
1995:
1998:
1999: /* Check to see if the line is a part of ATO model, SMC, etc. */
2000: BEGIN
2001: IF l_debug_level > 0 THEN
2002: oe_debug_pub.add( 'CHECKING IF LINE IS PART OF ATO MODEL , SMC' ) ;
2003: END IF;
2004: SELECT ATO_LINE_ID, TOP_MODEL_LINE_ID,
2005: SHIP_MODEL_COMPLETE_FLAG, ITEM_TYPE_CODE,
2006: LINK_TO_LINE_ID
2011: EXCEPTION
2012: WHEN NO_DATA_FOUND THEN
2013: x_result_out := FND_API.G_FALSE;
2014: IF l_debug_level > 0 THEN
2015: oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 2: ' || P_LINE_ID ) ;
2016: END IF;
2017: END;
2018:
2019: IF l_debug_level > 0 THEN
2017: END;
2018:
2019: IF l_debug_level > 0 THEN
2020: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
2021: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
2022: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
2023: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
2024: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
2025: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
2018:
2019: IF l_debug_level > 0 THEN
2020: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
2021: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
2022: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
2023: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
2024: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
2025: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
2026: ELSE
2019: IF l_debug_level > 0 THEN
2020: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
2021: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
2022: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
2023: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
2024: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
2025: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
2026: ELSE
2027: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
2020: IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
2021: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
2022: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
2023: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
2024: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
2025: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
2026: ELSE
2027: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
2028: END IF;
2021: oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
2022: oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
2023: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
2024: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
2025: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
2026: ELSE
2027: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
2028: END IF;
2029: END IF;
2023: oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
2024: oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
2025: oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
2026: ELSE
2027: oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
2028: END IF;
2029: END IF;
2030:
2031: /* If Line is part of ATO Model */
2032: IF l_ato_line_id is NOT NULL AND x_result_out = FND_API.G_FALSE AND
2033: NOT (l_ato_line_id = p_line_id AND l_item_type_code = OE_GLOBALS.G_ITEM_OPTION) THEN
2034:
2035: IF l_debug_level > 0 THEN
2036: oe_debug_pub.add( 'CHECK_HOLDS:ATO MODEL LINE: ' || L_ATO_LINE_ID ) ;
2037: END IF;
2038:
2039: /*
2040: ** Fix Bug # 2984023
2047: FETCH ato_model_lines into l_line_id;
2048: exit when ato_model_lines%NOTFOUND OR
2049: (x_result_out = FND_API.G_TRUE);
2050: IF l_debug_level > 0 THEN
2051: oe_debug_pub.add( 'CHECK_HOLDS:CHECKING LINEID' || L_LINE_ID ) ;
2052: END IF;
2053:
2054: Check_Holds_line (
2055: p_hdr_id => p_hdr_id
2088: );
2089: -- Raise if the l_return_status is unexpected error
2090: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2091: IF l_debug_level > 0 THEN
2092: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_ERROR') ;
2093: END IF;
2094: RAISE FND_API.G_EXC_ERROR;
2095: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2096: IF l_debug_level > 0 THEN
2093: END IF;
2094: RAISE FND_API.G_EXC_ERROR;
2095: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2096: IF l_debug_level > 0 THEN
2097: oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_UNEXP_ERROR') ;
2098: END IF;
2099: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2100: END IF;
2101:
2103:
2104: IF NVL(l_smc_flag, 'N') = 'Y' AND x_result_out = FND_API.G_FALSE THEN
2105:
2106: IF l_debug_level > 0 THEN
2107: oe_debug_pub.add( 'CHECK_HOLDS:TOP MODEL LINE ID: ' || L_TOP_MODEL_LINE_ID ) ;
2108: END IF;
2109:
2110: /*
2111: ** Fix Bug # 2984023
2117: FETCH smc_lines into l_line_id;
2118: exit when smc_lines%NOTFOUND OR (x_result_out = FND_API.G_TRUE);
2119:
2120: IF l_debug_level > 0 THEN
2121: oe_debug_pub.add( 'CHECK_HOLDS: CHECKING SMC LINEID' || L_LINE_ID ) ;
2122: END IF;
2123:
2124: Check_Holds_line (
2125: p_hdr_id => p_hdr_id
2158: );
2159: -- Raise if the l_return_status is unexpected error
2160: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2161: IF l_debug_level > 0 THEN
2162: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_ERROR') ;
2163: END IF;
2164: RAISE FND_API.G_EXC_ERROR;
2165: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2166: IF l_debug_level > 0 THEN
2163: END IF;
2164: RAISE FND_API.G_EXC_ERROR;
2165: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2166: IF l_debug_level > 0 THEN
2167: oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_UNEXP_ERROR') ;
2168: END IF;
2169: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2170: END IF;
2171:
2172: END IF; /* l_smc_flag = 'Y' */
2173: --5737464
2174: IF NVL(l_smc_flag, 'N') = 'N' AND x_result_out = FND_API.G_FALSE THEN
2175: IF l_debug_level > 0 THEN
2176: oe_debug_pub.add( 'CHECK_HOLDS: CHECKING FOR CONFIG VALIDATION HOLD' || L_TOP_MODEL_LINE_ID ) ;
2177: END IF;
2178: IF l_cascade_hold_non_smc <> 'Y' THEN --ER#7479609
2179: Check_Holds_SMC (
2180: p_hdr_id => p_hdr_id
2218:
2219: IF l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND x_result_out = FND_API.G_FALSE THEN
2220:
2221: IF l_debug_level > 0 THEN
2222: oe_debug_pub.add( 'CHECK_HOLDS: CHECKING HOLD ON LINK TO LINE ID: ' || L_LINK_TO_LINE_ID ) ;
2223: END IF;
2224:
2225: Check_Holds_line (
2226: p_hdr_id => p_hdr_id
2241: );
2242: -- Raise if the l_return_status is unexpected error
2243: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2244: IF l_debug_level > 0 THEN
2245: oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
2246: END IF;
2247: RAISE FND_API.G_EXC_ERROR;
2248: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2249: IF l_debug_level > 0 THEN
2246: END IF;
2247: RAISE FND_API.G_EXC_ERROR;
2248: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2249: IF l_debug_level > 0 THEN
2250: oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
2251: END IF;
2252: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2253: END IF;
2254:
2258:
2259: END IF; /* IF LINE ID IS NOT NULL */
2260:
2261: IF l_debug_level > 0 THEN
2262: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS' ) ;
2263: END IF;
2264:
2265: EXCEPTION
2266: WHEN FND_API.G_EXC_ERROR THEN
2330: l_msg_count NUMBER;
2331: l_msg_data VARCHAR2(2000);
2332:
2333: --
2334: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2335: --
2336: BEGIN
2337: IF l_debug_level > 0 THEN
2338: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLD_SOURCES' ) ;
2334: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2335: --
2336: BEGIN
2337: IF l_debug_level > 0 THEN
2338: oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLD_SOURCES' ) ;
2339: END IF;
2340: -- Initialize API return status to success
2341: x_return_status := FND_API.G_RET_STS_SUCCESS;
2342:
2346:
2347: -- Check for Missing Input Parameters
2348: IF p_hold_entity_code IS NULL AND p_hold_entity_id IS NULL THEN
2349: IF l_debug_level > 0 THEN
2350: oe_debug_pub.add( 'ENTER HOLD_ENTITY_CODE OR HOLD_ENTITY_ID' ) ;
2351: END IF;
2352: /* TO_DO: Seed a more meaningfull message */
2353: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
2354: OE_MSG_PUB.ADD;
2497: AND NVL(OH.HEADER_ID, 0) = NVL(NVL(p_header_id, OH.HEADER_ID), 0)
2498: AND NVL(OH.LINE_ID, 0) = NVL(NVL(p_line_id, OH.LINE_ID), 0)
2499: AND OH.HOLD_RELEASE_ID IS NULL;
2500: --
2501: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2502: --
2503: BEGIN
2504: l_check_authorization_flag := p_check_authorization_flag; -- bug 8477694
2505: IF l_debug_level > 0 THEN
2502: --
2503: BEGIN
2504: l_check_authorization_flag := p_check_authorization_flag; -- bug 8477694
2505: IF l_debug_level > 0 THEN
2506: oe_debug_pub.add( 'IN RELEASE_HOLDS , OLD' ) ;
2507: END IF;
2508:
2509: l_org_id := MO_GLOBAL.get_current_org_id;
2510: IF l_org_id IS NULL THEN
2508:
2509: l_org_id := MO_GLOBAL.get_current_org_id;
2510: IF l_org_id IS NULL THEN
2511: -- org_id is null, raise an error.
2512: oe_debug_pub.add('Org_Id is NULL',1);
2513: x_return_status := FND_API.G_RET_STS_ERROR;
2514: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2515: FND_MSG_PUB.ADD;
2516: RAISE FND_API.G_EXC_ERROR;
2531: -------------------------------------------------------------------
2532: IF p_hold_release_rec.hold_source_id IS NOT NULL THEN
2533:
2534: IF l_debug_level > 0 THEN
2535: oe_debug_pub.add( 'USING HOLD SOURCE ID' ) ;
2536: END IF;
2537: l_hold_source_id := p_hold_release_rec.hold_source_id;
2538:
2539: BEGIN
2569: -------------------------------------------------------------------
2570:
2571: ELSE
2572: IF l_debug_level > 0 THEN
2573: oe_debug_pub.add( 'HOLD SOURCE ID IS NOT PASSED' ) ;
2574: END IF;
2575: -- Check for Missing Values
2576: IF p_hold_id IS NULL THEN
2577: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_ID');
2618: -- Check to see if the Site code is Bill_to OR Ship_to
2619: l_hold_source_rec.hold_id := p_hold_id;
2620: IF p_entity_code = 'S' THEN
2621: IF l_debug_level > 0 THEN
2622: oe_debug_pub.add( 'CHECKING FOR SITE CODE , BILL TO OR SHIP TO' , 1 ) ;
2623: END IF;
2624: l_hold_source_rec.hold_entity_code := Hold_Site_Code(p_entity_id);
2625: ELSE
2626: l_hold_source_rec.hold_entity_code := p_entity_code;
2631: p_hold_release_rec.RELEASE_REASON_CODE;
2632: l_hold_release_rec.RELEASE_COMMENT := p_hold_release_rec.RELEASE_COMMENT;
2633:
2634: IF l_debug_level > 0 THEN
2635: oe_debug_pub.add( 'CALLING RELEASE HOLDS , OLD' ) ;
2636: END IF;
2637: oe_holds_pvt.Release_Holds(
2638: p_hold_source_rec => l_hold_source_rec
2639: ,p_hold_release_rec => l_hold_release_rec
2642: ,x_msg_count => x_msg_count
2643: ,x_msg_data => x_msg_data
2644: );
2645: IF l_debug_level > 0 THEN
2646: oe_debug_pub.add( 'OE_HOLDS_PVT.RELEASE_HOLDS STATUS:' || X_RETURN_STATUS ) ;
2647: END IF;
2648:
2649: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2650: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2741: l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
2742: --ER#7479609 end
2743:
2744: --
2745: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2746: --
2747: BEGIN
2748: l_org_id := MO_GLOBAL.get_current_org_id;
2749: IF l_org_id IS NULL THEN
2747: BEGIN
2748: l_org_id := MO_GLOBAL.get_current_org_id;
2749: IF l_org_id IS NULL THEN
2750: -- org_id is null, raise an error.
2751: oe_debug_pub.add('Org_Id is NULL',1);
2752: x_return_status := FND_API.G_RET_STS_ERROR;
2753: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2754: FND_MSG_PUB.ADD;
2755: RAISE FND_API.G_EXC_ERROR;
2782: EXIT;
2783: END IF;
2784:
2785: IF l_debug_level > 0 THEN
2786: oe_debug_pub.add( 'DELETING ORDER HOLD RECORD' ) ;
2787: END IF;
2788:
2789: DELETE FROM OE_ORDER_HOLDS
2790: WHERE order_hold_id = l_order_hold_id;
2789: DELETE FROM OE_ORDER_HOLDS
2790: WHERE order_hold_id = l_order_hold_id;
2791:
2792: IF l_debug_level > 0 THEN
2793: oe_debug_pub.add( 'DELETING HOLD RELEASE RECORD' ) ;
2794: END IF;
2795: DELETE FROM OE_HOLD_RELEASES
2796: WHERE HOLD_RELEASE_ID = l_hold_release_id
2797: AND ORDER_HOLD_ID = l_order_hold_id;
2817: EXIT;
2818: END IF;
2819:
2820: IF l_debug_level > 0 THEN
2821: oe_debug_pub.add( 'DELETING HOLD SOURCE RECORD' ) ;
2822: END IF;
2823: DELETE FROM OE_HOLD_SOURCES
2824: WHERE HOLD_SOURCE_ID = l_hold_source_id;
2825:
2823: DELETE FROM OE_HOLD_SOURCES
2824: WHERE HOLD_SOURCE_ID = l_hold_source_id;
2825:
2826: IF l_debug_level > 0 THEN
2827: oe_debug_pub.add( 'DELETING HOLD RELEASE RECORD' ) ;
2828: END IF;
2829: DELETE FROM OE_HOLD_RELEASES
2830: WHERE HOLD_RELEASE_ID = l_hold_release_id;
2831:
2843: EXIT;
2844: END IF;
2845:
2846: IF l_debug_level > 0 THEN
2847: oe_debug_pub.add( 'DELETING LINE HOLD RECORD' ) ;
2848: END IF;
2849:
2850: DELETE FROM OE_ORDER_HOLDS
2851: WHERE order_hold_id = l_order_hold_id;
2881: EXIT;
2882: END IF;
2883:
2884: IF l_debug_level > 0 THEN
2885: oe_debug_pub.add( 'DELETING LINE HOLD RECORD FOR TOP MODEL WHEN OPTION ITEM LINE IS DELETED' ) ;
2886: END IF;
2887:
2888: DELETE FROM OE_ORDER_HOLDS
2889: WHERE order_hold_id = l_order_hold_id;
3072: BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
3073: AND NVL( HLD.END_DATE_ACTIVE, SYSDATE );
3074:
3075: --
3076: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3077: --
3078: --ER#7479609 start
3079: l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
3080: l_line_rec OE_ORDER_LINES_ALL%rowtype;
3095: BEGIN
3096: x_return_status := FND_API.G_RET_STS_SUCCESS;
3097:
3098: IF l_debug_level > 0 THEN
3099: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS' , 1 ) ;
3100: END IF;
3101:
3102: IF l_debug_level > 0 THEN
3103: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
3099: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS' , 1 ) ;
3100: END IF;
3101:
3102: IF l_debug_level > 0 THEN
3103: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
3104: END IF;
3105: IF l_debug_level > 0 THEN
3106: oe_debug_pub.add( 'HOLD ENTITY: '|| P_HOLD_ENTITY_CODE ||'/' || P_HOLD_ENTITY_ID ) ;
3107: END IF;
3102: IF l_debug_level > 0 THEN
3103: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
3104: END IF;
3105: IF l_debug_level > 0 THEN
3106: oe_debug_pub.add( 'HOLD ENTITY: '|| P_HOLD_ENTITY_CODE ||'/' || P_HOLD_ENTITY_ID ) ;
3107: END IF;
3108: -- ER#3667551 start
3109: -- When a delayed request is logged for update of Bill To Customer Of Order header level
3110: -- the code is passed as 'BTH' instead of 'C'. This is done because both Custom hold for Sold to Customer
3137:
3138: EXCEPTION
3139: WHEN OTHERS THEN
3140: IF l_debug_level > 0 THEN
3141: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
3142: END IF;
3143: RAISE NO_DATA_FOUND;
3144: END;
3145: --ER#7479609 end
3143: RAISE NO_DATA_FOUND;
3144: END;
3145: --ER#7479609 end
3146: IF l_debug_level > 0 THEN
3147: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3148: END IF;
3149: --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3150: ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3151: p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3150: ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3151: p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3152: l_line_id := p_entity_id;
3153: IF l_debug_level > 0 THEN
3154: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3155: END IF;
3156: --ER#7479609 start
3157: BEGIN
3158: SELECT OH.* INTO l_header_rec
3164:
3165: EXCEPTION
3166: WHEN OTHERS THEN
3167: IF l_debug_level > 0 THEN
3168: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
3169: END IF;
3170: RAISE NO_DATA_FOUND;
3171: END;
3172: --ER#7479609 end
3206:
3207: EXCEPTION
3208: WHEN OTHERS THEN
3209: IF l_debug_level > 0 THEN
3210: oe_debug_pub.add( 'NO HEADER ID FOR THIS LINE' ) ;
3211: END IF;
3212: --RAISE NO_DATA_FOUND;
3213: null;
3214: END;
3213: null;
3214: END;
3215: --l_header_id := l_line_rec.header_id;
3216: IF l_debug_level > 0 THEN
3217: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3218: END IF;
3219: IF l_debug_level > 0 THEN
3220: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3221: END IF;
3216: IF l_debug_level > 0 THEN
3217: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3218: END IF;
3219: IF l_debug_level > 0 THEN
3220: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3221: END IF;
3222: END IF;
3223:
3224: --ER#7479609 start
3270: --ER#7479609 OPEN prev_hold;
3271: OPEN prev_hold(l_all_del_pay); --ER#7479609
3272: LOOP
3273: IF l_debug_level > 0 THEN
3274: oe_debug_pub.add( 'RETRIEVING PREV. HOLD RECORD' , 1 ) ;
3275: END IF;
3276:
3277: -- FETCH prev_hold INTO l_hold_entity_id, l_hold_source_id;
3278: --ER#7479609 FETCH prev_hold INTO l_hold_entity_id, l_order_hold_id;
3279: FETCH prev_hold INTO l_hold_entity_id, l_order_hold_id,l_hold_entity_code; --ER#7479609
3280:
3281: IF (prev_hold%notfound) THEN
3282: IF l_debug_level > 0 THEN
3283: oe_debug_pub.add( 'PREV_HOLD NOT FOUND , EXITING.' , 1 ) ;
3284: END IF;
3285: EXIT;
3286: END IF;
3287:
3286: END IF;
3287:
3288: IF l_hold_entity_id = p_hold_entity_id THEN
3289: IF l_debug_level > 0 THEN
3290: oe_debug_pub.add( 'SAME ENTITY ID: NEITHER APPLY NOR REMOVE' ) ;
3291: END IF;
3292: --RETURN;
3293: exit;
3294: END IF;
3295:
3296: --ER#7479609 start
3297: IF (l_hold_entity_code='P' and G_PAYMENT_HOLD_APPLIED = 'Y' and p_entity_code = OE_GLOBALS.G_ENTITY_HEADER) THEN
3298: IF l_debug_level > 0 THEN
3299: oe_debug_pub.add( 'NOT DELETING HOLD' , 1 ) ;
3300: END IF;
3301: ELSE
3302: --ER#7479609 end
3303: IF l_debug_level > 0 THEN
3300: END IF;
3301: ELSE
3302: --ER#7479609 end
3303: IF l_debug_level > 0 THEN
3304: oe_debug_pub.add( 'DELETING HOLD' , 1 ) ;
3305: END IF;
3306: DELETE FROM OE_ORDER_HOLDS
3307: WHERE ORDER_HOLD_ID = l_order_hold_id;
3308:
3306: DELETE FROM OE_ORDER_HOLDS
3307: WHERE ORDER_HOLD_ID = l_order_hold_id;
3308:
3309: IF l_debug_level > 0 THEN
3310: oe_debug_pub.add( '(1)L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3311: END IF;
3312: IF l_debug_level > 0 THEN
3313: oe_debug_pub.add( 'HOLD REMOVED' ) ;
3314: END IF;
3309: IF l_debug_level > 0 THEN
3310: oe_debug_pub.add( '(1)L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3311: END IF;
3312: IF l_debug_level > 0 THEN
3313: oe_debug_pub.add( 'HOLD REMOVED' ) ;
3314: END IF;
3315: END IF; --ER#7479609
3316: --fnd_message.set_name('ONT','OE_HOLD_REMOVED');
3317: --OE_MSG_PUB.ADD;
3324: --ER#7479609 if p_hold_entity_code in ('C','S','B','W','H','L') then
3325: --9927494 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
3326: if p_hold_entity_code in ('B','CB','C','D','LT','OI','OT','PT','P','PL','PR','L','H','SC','SM','S','ST','T','TC','W','CN','EC','EN','EL') then --9927494 -- ER# 11824468, added 'CN' --ER# 12571983 added 'EC' 'EN'
3327: IF l_debug_level > 0 THEN
3328: oe_debug_pub.add( 'CHECKING PREV HOLD SOURCES FOR SECOND ENTITY' ) ;
3329: END IF;
3330:
3331: --ER#7479609 start
3332: l_all_del_pay := 'Y';
3343: --ER#7479609 OPEN prev_hold_entity2;
3344: OPEN prev_hold_entity2(l_all_del_pay); --ER#7479609
3345: LOOP
3346: IF l_debug_level > 0 THEN
3347: oe_debug_pub.add( 'RETRIEVING PREV. HOLD RECORD FOR ENTITY2' , 1 ) ;
3348: END IF;
3349:
3350: -- FETCH prev_hold INTO l_hold_entity_id, l_hold_source_id;
3351: --ER#7479609 FETCH prev_hold_entity2 INTO l_hold_entity_id, l_order_hold_id;
3352: FETCH prev_hold_entity2 INTO l_hold_entity_id, l_order_hold_id,l_hold_entity_code; --ER#7479609
3353:
3354: IF (prev_hold_entity2%notfound) THEN
3355: IF l_debug_level > 0 THEN
3356: oe_debug_pub.add( 'PREV_HOLD_ENTITY2 NOT FOUND , EXITING' , 1 ) ;
3357: END IF;
3358: EXIT;
3359: END IF;
3360:
3359: END IF;
3360:
3361: IF l_hold_entity_id = p_hold_entity_id THEN
3362: IF l_debug_level > 0 THEN
3363: oe_debug_pub.add( 'SAME ENTITY ID2: NEITHER APPLY NOR REMOVE' ) ;
3364: END IF;
3365: --RETURN;
3366: EXIT;
3367: END IF;
3368:
3369: --ER#7479609 start
3370: IF (l_hold_entity_code = 'P' and G_PAYMENT_HOLD_APPLIED = 'Y' and p_entity_code = OE_GLOBALS.G_ENTITY_HEADER) THEN
3371: IF l_debug_level > 0 THEN
3372: oe_debug_pub.add( 'NOT DELETING HOLD' , 1 ) ;
3373: END IF;
3374: ELSE
3375: --ER#7479609 end
3376: IF l_debug_level > 0 THEN
3373: END IF;
3374: ELSE
3375: --ER#7479609 end
3376: IF l_debug_level > 0 THEN
3377: oe_debug_pub.add( 'DELETING HOLD' , 1 ) ;
3378: END IF;
3379: DELETE FROM OE_ORDER_HOLDS
3380: WHERE ORDER_HOLD_ID = l_order_hold_id;
3381:
3379: DELETE FROM OE_ORDER_HOLDS
3380: WHERE ORDER_HOLD_ID = l_order_hold_id;
3381:
3382: IF l_debug_level > 0 THEN
3383: oe_debug_pub.add( '(2)L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3384: END IF;
3385: IF l_debug_level > 0 THEN
3386: oe_debug_pub.add( 'HOLD REMOVED FOR SECOND ENTITY' ) ;
3387: END IF;
3382: IF l_debug_level > 0 THEN
3383: oe_debug_pub.add( '(2)L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3384: END IF;
3385: IF l_debug_level > 0 THEN
3386: oe_debug_pub.add( 'HOLD REMOVED FOR SECOND ENTITY' ) ;
3387: END IF;
3388: END IF; --ER#7479609
3389: --fnd_message.set_name('ONT','OE_HOLD_REMOVED');
3390: --OE_MSG_PUB.ADD;
3397:
3398: OPEN curr_hold_source;
3399: LOOP
3400: IF l_debug_level > 0 THEN
3401: oe_debug_pub.add( '(1)RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
3402: END IF;
3403: FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
3404: l_hold_entity_code,l_hold_entity_id,
3405: l_hold_entity_code2,l_hold_entity_id2;
3405: l_hold_entity_code2,l_hold_entity_id2;
3406:
3407: IF (curr_hold_source%notfound) THEN
3408: IF l_debug_level > 0 THEN
3409: oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
3410: END IF;
3411: EXIT;
3412: END IF;
3413:
3411: EXIT;
3412: END IF;
3413:
3414: IF l_debug_level > 0 THEN
3415: oe_debug_pub.add( '(3)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3416: 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 ) ;
3417: END IF;
3418: -- If second entity is not null, check if order/line is eligible for hold
3419: l_create_order_hold_flag := 'Y';
3412: END IF;
3413:
3414: IF l_debug_level > 0 THEN
3415: oe_debug_pub.add( '(3)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3416: 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 ) ;
3417: END IF;
3418: -- If second entity is not null, check if order/line is eligible for hold
3419: l_create_order_hold_flag := 'Y';
3420: IF l_hold_entity_code2 is not null THEN
3614: END IF;
3615: --ER#7479609 end
3616: END IF; -- l_hold_entity_code2 is not null
3617: IF l_debug_level > 0 THEN
3618: oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
3619: END IF;
3620:
3621: --ER#7479609 start
3622: IF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3656: l_hold_source_rec.header_id := l_header_id;
3657: l_hold_source_rec.line_id := l_line_id;
3658:
3659: IF l_debug_level > 0 THEN
3660: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3661: END IF;
3662: oe_holds_pvt.Create_Order_Holds (
3663: p_hold_source_rec => l_hold_source_rec
3664: ,x_return_status => x_return_status
3666: ,x_msg_data => x_msg_data
3667: );
3668:
3669: IF l_debug_level > 0 THEN
3670: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
3671: END IF;
3672:
3673: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3674: IF l_debug_level > 0 THEN
3671: END IF;
3672:
3673: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3674: IF l_debug_level > 0 THEN
3675: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3676: END IF;
3677: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3678: RAISE FND_API.G_EXC_ERROR;
3679: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3686: --ER#7479609 end
3687: ELSE
3688:
3689: IF l_debug_level > 0 THEN
3690: oe_debug_pub.add( 'HOLD APPLIED' ) ;
3691: END IF;
3692:
3693: IF p_hold_entity_code = 'C' THEN
3694: l_attribute := 'Customer';
3832: -- ADD and p_entity_code = G_ENTITY_LINE ????
3833: OPEN curr_hold_source_entity2;
3834: LOOP
3835: IF l_debug_level > 0 THEN
3836: oe_debug_pub.add( 'RETRIEVING NEW HOLD SOURCE RECORD FOR ENTITY2' , 1 ) ;
3837: END IF;
3838: FETCH curr_hold_source_entity2 INTO l_hold_source_id, l_hold_id,
3839: l_hold_entity_code,l_hold_entity_id,
3840: l_hold_entity_code2,l_hold_entity_id2;
3840: l_hold_entity_code2,l_hold_entity_id2;
3841:
3842: IF (curr_hold_source_entity2%notfound) THEN
3843: IF l_debug_level > 0 THEN
3844: oe_debug_pub.add( 'NO HOLD SOURCE FOUND , EXISTING' , 1 ) ;
3845: END IF;
3846: EXIT;
3847: END IF;
3848:
3846: EXIT;
3847: END IF;
3848:
3849: IF l_debug_level > 0 THEN
3850: oe_debug_pub.add( '(4)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3851: 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 ) ;
3852: END IF;
3853:
3854: -- If second entity is not null, The First entity can only be I or W
3847: END IF;
3848:
3849: IF l_debug_level > 0 THEN
3850: oe_debug_pub.add( '(4)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3851: 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 ) ;
3852: END IF;
3853:
3854: -- If second entity is not null, The First entity can only be I or W
3855: -- Check if order/line is eligible for hold
3936: AND mdc.category_set_id = mic.category_set_id;
3937: EXCEPTION WHEN OTHERS THEN
3938: l_valid_itemcat := 'N';
3939: END;
3940: oe_debug_pub.add('IC- '||l_hold_entity_id||' Item- '||l_line_rec.inventory_item_id||' -Org- '||l_line_rec.ship_from_org_id||' valid-'||l_valid_itemcat);
3941: IF l_valid_itemcat = 'Y' THEN
3942: l_create_order_hold_flag := 'Y';
3943: END IF;
3944: --ER# 13331078 end
3978: END IF;
3979: --ER#7479609 end
3980:
3981: IF l_debug_level > 0 THEN
3982: oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
3983: END IF;
3984:
3985: IF l_create_order_hold_flag = 'Y' THEN
3986: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
3992: l_hold_source_rec.header_id := l_header_id;
3993: l_hold_source_rec.line_id := l_line_id;
3994:
3995: IF l_debug_level > 0 THEN
3996: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3997: END IF;
3998: oe_holds_pvt.Create_Order_Holds (
3999: p_hold_source_rec => l_hold_source_rec
4000: ,x_return_status => x_return_status
4002: ,x_msg_data => x_msg_data
4003: );
4004:
4005: IF l_debug_level > 0 THEN
4006: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
4007: END IF;
4008:
4009: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4010: IF l_debug_level > 0 THEN
4007: END IF;
4008:
4009: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4010: IF l_debug_level > 0 THEN
4011: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4012: END IF;
4013: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4014: RAISE FND_API.G_EXC_ERROR;
4015: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4023: --ER#7479609 end
4024:
4025: ELSE
4026: IF l_debug_level > 0 THEN
4027: oe_debug_pub.add( 'HOLD APPLIED' ) ;
4028: END IF;
4029:
4030: IF l_hold_entity_code = 'C' THEN
4031: l_attribute := 'Customer';
4159: CLOSE curr_hold_source_entity2;
4160: end if;
4161:
4162: IF l_debug_level > 0 THEN
4163: oe_debug_pub.add( 'EXITING EVALUATE_HOLDS' , 1 ) ;
4164: END IF;
4165:
4166: EXCEPTION
4167:
4269: --6766981 AND rownum=1; --5999405
4270:
4271:
4272: --
4273: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4274: --
4275: BEGIN
4276: x_return_status := FND_API.G_RET_STS_SUCCESS;
4277:
4275: BEGIN
4276: x_return_status := FND_API.G_RET_STS_SUCCESS;
4277:
4278: IF l_debug_level > 0 THEN
4279: oe_debug_pub.add( 'ENTERING EVAL_POST_WRITE_HEADER' , 1 ) ;
4280: END IF;
4281:
4282:
4283: IF l_debug_level > 0 THEN
4280: END IF;
4281:
4282:
4283: IF l_debug_level > 0 THEN
4284: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4285: END IF;
4286:
4287: l_header_id := p_entity_id;
4288:
4286:
4287: l_header_id := p_entity_id;
4288:
4289: IF l_debug_level > 0 THEN
4290: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4291: END IF;
4292:
4293: m_hold_entity_code := p_hold_entity_code;
4294: m_hold_entity_id := p_hold_entity_id;
4293: m_hold_entity_code := p_hold_entity_code;
4294: m_hold_entity_id := p_hold_entity_id;
4295:
4296: IF l_debug_level > 0 THEN
4297: oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4298: END IF;
4299: IF m_hold_entity_id IS NOT NULL THEN
4300: OPEN curr_hold_source(m_hold_entity_code, m_hold_entity_id);
4301: LOOP
4299: IF m_hold_entity_id IS NOT NULL THEN
4300: OPEN curr_hold_source(m_hold_entity_code, m_hold_entity_id);
4301: LOOP
4302: IF l_debug_level > 0 THEN
4303: oe_debug_pub.add( '(2)RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
4304: END IF;
4305: FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
4306: l_hold_entity_code,l_hold_entity_id,
4307: l_hold_entity_code2,l_hold_entity_id2;
4307: l_hold_entity_code2,l_hold_entity_id2;
4308:
4309: IF (curr_hold_source%notfound) THEN
4310: IF l_debug_level > 0 THEN
4311: oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4312: END IF;
4313: EXIT;
4314: END IF;
4315:
4313: EXIT;
4314: END IF;
4315:
4316: IF l_debug_level > 0 THEN
4317: oe_debug_pub.add( '(5)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4318: 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 ) ;
4319: END IF;
4320:
4321: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
4314: END IF;
4315:
4316: IF l_debug_level > 0 THEN
4317: oe_debug_pub.add( '(5)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4318: 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 ) ;
4319: END IF;
4320:
4321: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
4322: l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
4327: l_hold_source_rec.header_id := l_header_id;
4328: l_hold_source_rec.line_id := l_line_id;
4329:
4330: IF l_debug_level > 0 THEN
4331: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4332: END IF;
4333: oe_holds_pvt.Create_Order_Holds (
4334: p_hold_source_rec => l_hold_source_rec
4335: ,x_return_status => x_return_status
4338: );
4339:
4340:
4341: IF l_debug_level > 0 THEN
4342: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
4343: END IF;
4344:
4345: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4346: IF l_debug_level > 0 THEN
4343: END IF;
4344:
4345: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4346: IF l_debug_level > 0 THEN
4347: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4348: END IF;
4349: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4350: RAISE FND_API.G_EXC_ERROR;
4351: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4352: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4353: END IF;
4354: ELSE
4355: IF l_debug_level > 0 THEN
4356: oe_debug_pub.add( 'HOLD APPLIED' ) ;
4357: END IF;
4358:
4359: l_attribute := 'Customer';
4360:
4367: CLOSE curr_hold_source;
4368: END IF; /* IF m_hold_entity_id IS NOT NULL THEN */
4369:
4370: IF l_debug_level > 0 THEN
4371: oe_debug_pub.add( 'EXITING EVAL_POST_WRITE_HEADER' , 1 ) ;
4372: END IF;
4373:
4374: EXCEPTION
4375:
4574: pay_idx NUMBER :=0;
4575:
4576: --ER#7479609 end
4577: --
4578: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4579: --
4580: -- ER#3667551 START to hold System Parameter value
4581: l_credithold_cust VARCHAR2(10) := NVL(OE_SYS_PARAMETERS.value('ONT_CREDITHOLD_TYPE'),'S') ;
4582: l_new_tbl_entry CHAR:='';
4586: BEGIN
4587: x_return_status := FND_API.G_RET_STS_SUCCESS;
4588:
4589: IF l_debug_level > 0 THEN
4590: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
4591: END IF;
4592: IF l_debug_level > 0 THEN
4593: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4594: END IF;
4589: IF l_debug_level > 0 THEN
4590: oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
4591: END IF;
4592: IF l_debug_level > 0 THEN
4593: oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4594: END IF;
4595:
4596:
4597: IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4606:
4607: EXCEPTION
4608: WHEN OTHERS THEN
4609: IF l_debug_level > 0 THEN
4610: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
4611: END IF;
4612: RAISE NO_DATA_FOUND;
4613: END;
4614: --ER#7479609 end
4612: RAISE NO_DATA_FOUND;
4613: END;
4614: --ER#7479609 end
4615: IF l_debug_level > 0 THEN
4616: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4617: END IF;
4618:
4619:
4620: ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4619:
4620: ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4621: l_line_id := p_entity_id;
4622: IF l_debug_level > 0 THEN
4623: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4624: END IF;
4625:
4626: --ER#7479609 start
4627: BEGIN
4632:
4633: EXCEPTION
4634: WHEN OTHERS THEN
4635: IF l_debug_level > 0 THEN
4636: oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
4637: END IF;
4638: RAISE NO_DATA_FOUND;
4639: END;
4640: --ER#7479609 end
4670:
4671: EXCEPTION
4672: WHEN OTHERS THEN
4673: IF l_debug_level > 0 THEN
4674: oe_debug_pub.add( 'NO HEADER ID FOR THIS LINE' ) ;
4675: END IF;
4676: RAISE NO_DATA_FOUND;
4677: END;
4678: IF l_debug_level > 0 THEN
4675: END IF;
4676: RAISE NO_DATA_FOUND;
4677: END;
4678: IF l_debug_level > 0 THEN
4679: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4680: END IF;
4681: IF l_debug_level > 0 THEN
4682: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4683: END IF;
4678: IF l_debug_level > 0 THEN
4679: oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4680: END IF;
4681: IF l_debug_level > 0 THEN
4682: oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4683: END IF;
4684: END IF;
4685:
4686: -- FOR m_hold_entity_code IN ('B', 'S', 'W', 'I','H') LOOP
4831: AND mdc.functional_area_id=7
4832: AND mdc.category_set_id = mic.category_set_id;
4833: EXCEPTION
4834: WHEN NO_DATA_FOUND THEN
4835: oe_debug_pub.add('In No Data Found for 13331078 Item= '||l_line_rec.inventory_item_id);
4836: END;
4837:
4838:
4839: --ER# 13331078 end
4880: END IF; --ER#7479609
4881:
4882: FOR i IN l_entity_tab.first .. l_entity_tab.last LOOP --ER#7479609
4883: IF l_debug_level > 0 THEN
4884: -- oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4885: oe_debug_pub.add( 'HOLD_ENTITY_CODE/HOLD_ENTITY_ID:' || l_entity_tab(i).entity_code || '/' || l_entity_tab(i).entity_id , 1 ) ;
4886: END IF;
4887:
4888: --ER#7479609 IF m_hold_entity_id IS NOT NULL THEN
4881:
4882: FOR i IN l_entity_tab.first .. l_entity_tab.last LOOP --ER#7479609
4883: IF l_debug_level > 0 THEN
4884: -- oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4885: oe_debug_pub.add( 'HOLD_ENTITY_CODE/HOLD_ENTITY_ID:' || l_entity_tab(i).entity_code || '/' || l_entity_tab(i).entity_id , 1 ) ;
4886: END IF;
4887:
4888: --ER#7479609 IF m_hold_entity_id IS NOT NULL THEN
4889: IF l_entity_tab(i).entity_id IS NOT NULL THEN --ER#7479609
4896: OPEN curr_hold_source(l_entity_tab(i).entity_code, l_entity_tab(i).entity_id); --ER#7479609
4897: END IF; --8602364
4898: LOOP
4899: IF l_debug_level > 0 THEN
4900: oe_debug_pub.add( '(3)RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
4901: END IF;
4902:
4903: --8602364 start
4904: IF l_entity_tab(i).entity_code = 'OI' THEN
4906: l_hold_entity_code,l_hold_entity_id,
4907: l_hold_entity_code2,l_hold_entity_id2;
4908: IF (curr_hold_source_2%notfound) THEN
4909: IF l_debug_level > 0 THEN
4910: oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4911: END IF;
4912:
4913: EXIT;
4914: END IF;
4918: l_hold_entity_code,l_hold_entity_id,
4919: l_hold_entity_code2,l_hold_entity_id2;
4920: IF (curr_hold_source%notfound) THEN
4921: IF l_debug_level > 0 THEN
4922: oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4923: END IF;
4924:
4925: EXIT;
4926: END IF;
4926: END IF;
4927: END IF; --8602364
4928:
4929: IF l_debug_level > 0 THEN
4930: oe_debug_pub.add( '(6)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4931: 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 ) ;
4932: END IF;
4933:
4934: -- If second entity is not null, check if order/line is eligible for hold
4927: END IF; --8602364
4928:
4929: IF l_debug_level > 0 THEN
4930: oe_debug_pub.add( '(6)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4931: 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 ) ;
4932: END IF;
4933:
4934: -- If second entity is not null, check if order/line is eligible for hold
4935:
4941: -- then Line Level hold has to be applied for the Bill To Customer
4942: l_ch_level := '';
4943: If(l_hold_entity_code2 is null and p_entity_code = OE_Globals.G_ENTITY_HEADER
4944: and l_hold_entity_code = 'C' and l_hold_id=1 ) then
4945: oe_debug_pub.add( 'l_bill_to_orgid='||l_bill_to_orgid||'l_hold_entity_id'||l_hold_entity_id
4946: ||'STO-'||l_header_rec.sold_to_org_id);
4947: If (l_credithold_cust='BTL') then
4948: l_create_order_hold_flag := 'N';
4949: oe_debug_pub.add( 'Do not Create order hold , system parameter is set Bill To Line');
4945: oe_debug_pub.add( 'l_bill_to_orgid='||l_bill_to_orgid||'l_hold_entity_id'||l_hold_entity_id
4946: ||'STO-'||l_header_rec.sold_to_org_id);
4947: If (l_credithold_cust='BTL') then
4948: l_create_order_hold_flag := 'N';
4949: oe_debug_pub.add( 'Do not Create order hold , system parameter is set Bill To Line');
4950: -- Do Not Create Hold for the iteration where Bill To Customer not equals Sold to Customer
4951: -- but the hold entity id holds the Sold to Customer for a BTH case
4952: -- because hold should be created only for Bill To Customer not equals Sold To
4953: elsIf (l_credithold_cust='BTH' and l_bill_to_orgid <> l_header_rec.sold_to_org_id
4952: -- because hold should be created only for Bill To Customer not equals Sold To
4953: elsIf (l_credithold_cust='BTH' and l_bill_to_orgid <> l_header_rec.sold_to_org_id
4954: and l_hold_entity_id = l_header_rec.sold_to_org_id) then
4955: l_create_order_hold_flag := 'N';
4956: oe_debug_pub.add( 'Do not Create Sold To Customer order hold , system parameter is set Bill To Header');
4957: --
4958: -- If it is a Bill to Header Level Credit Hold update the level so that
4959: -- appropriate message can be displayed
4960: elsif (l_credithold_cust='BTH' and l_hold_entity_id = l_bill_to_orgid) then
5116:
5117: END IF; -- l_hold_entity_code2 is not null
5118:
5119: IF l_debug_level > 0 THEN
5120: oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
5121: END IF;
5122:
5123: --ER#7479609 start
5124: IF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
5161: l_hold_source_rec.header_id := l_header_id;
5162: l_hold_source_rec.line_id := l_line_id;
5163:
5164: IF l_debug_level > 0 THEN
5165: oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
5166: END IF;
5167: oe_holds_pvt.Create_Order_Holds (
5168: p_hold_source_rec => l_hold_source_rec
5169: ,x_return_status => x_return_status
5172: );
5173:
5174:
5175: IF l_debug_level > 0 THEN
5176: oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
5177: END IF;
5178:
5179: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5180: IF l_debug_level > 0 THEN
5177: END IF;
5178:
5179: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5180: IF l_debug_level > 0 THEN
5181: oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
5182: END IF;
5183: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5184: RAISE FND_API.G_EXC_ERROR;
5185: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5187: END IF;
5188: ELSE
5189: IF l_debug_level > 0 THEN
5190: oe_debug_pub.add( 'HOLD APPLIED' ) ;
5191: END IF;
5192:
5193: IF l_hold_entity_code = 'C' THEN
5194: l_attribute := 'Customer';
5345: ELSIF m_counter = 4 THEN
5346: m_hold_entity_code2 := 'W';
5347: m_hold_entity_id2 := l_ship_from_org_id;
5348: END IF;
5349: OE_Debug_PUB.Add('m_hold_entity_code2/m_hold_entity_id2:' ||
5350: m_hold_entity_code2 || '/' || m_hold_entity_id2, 1);
5351: IF m_hold_entity_id2 IS NOT NULL THEN
5352:
5353: OPEN curr_hold_source_entity2(m_hold_entity_code2,m_hold_entity_id2);
5351: IF m_hold_entity_id2 IS NOT NULL THEN
5352:
5353: OPEN curr_hold_source_entity2(m_hold_entity_code2,m_hold_entity_id2);
5354: LOOP
5355: OE_Debug_PUB.Add('Retrieving new hold source record for Entity2', 1);
5356: FETCH curr_hold_source_entity2 INTO l_hold_source_id, l_hold_id,
5357: l_hold_entity_code,l_hold_entity_id,
5358: l_hold_entity_code2,l_hold_entity_id2;
5359:
5357: l_hold_entity_code,l_hold_entity_id,
5358: l_hold_entity_code2,l_hold_entity_id2;
5359:
5360: IF (curr_hold_source_entity2%notfound) THEN
5361: OE_Debug_PUB.Add('No Hold Source found, existing', 1);
5362: EXIT;
5363: END IF;
5364:
5365: OE_Debug_PUB.Add('l_hold_entity_code/l_hold_entity_id/' ||
5361: OE_Debug_PUB.Add('No Hold Source found, existing', 1);
5362: EXIT;
5363: END IF;
5364:
5365: OE_Debug_PUB.Add('l_hold_entity_code/l_hold_entity_id/' ||
5366: 'l_hold_entity_code2/l_hold_entity_id2/l_hold_source_id:' ||
5367: l_hold_entity_code || '/' ||
5368: l_hold_entity_id || '/' ||
5369: l_hold_entity_code2 || '/' ||
5381: IF l_ship_from_org_id = l_hold_entity_id THEN
5382: l_create_order_hold_flag := 'Y';
5383: END IF;
5384: END IF;
5385: OE_DEBUG_PUB.Add('l_create_order_hold_flag:' || l_create_order_hold_flag,1);
5386:
5387: IF l_create_order_hold_flag = 'Y' THEN
5388: l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
5389: l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
5393: l_hold_source_rec.hold_source_id := l_hold_source_id;
5394: l_hold_source_rec.header_id := l_header_id;
5395: l_hold_source_rec.line_id := l_line_id;
5396:
5397: oe_debug_pub.add('Calling oe_holds_pvt.create_order_holds');
5398: oe_holds_pvt.Create_Order_Holds (
5399: p_hold_source_rec => l_hold_source_rec
5400: ,x_return_status => x_return_status
5401: ,x_msg_count => x_msg_count
5401: ,x_msg_count => x_msg_count
5402: ,x_msg_data => x_msg_data
5403: );
5404:
5405: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5406:
5407: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5408: OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
5409: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5404:
5405: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5406:
5407: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5408: OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
5409: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5410: RAISE FND_API.G_EXC_ERROR;
5411: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5412: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5411: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5412: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5413: END IF;
5414: ELSE
5415: OE_Debug_PUB.Add('Hold applied');
5416:
5417: IF l_hold_entity_code = 'C' THEN
5418: l_attribute := 'Customer';
5419: ELSIF l_hold_entity_code = 'I' THEN
5462: END IF;
5463: END LOOP;
5464: */
5465: IF l_debug_level > 0 THEN
5466: oe_debug_pub.add( 'EXITING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
5467: END IF;
5468:
5469: EXCEPTION
5470:
5521: ) RETURN boolean IS
5522:
5523: l_hold_exists VARCHAR2(1) := 'N';
5524:
5525: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5526:
5527: BEGIN
5528: IF l_debug_level > 0 THEN
5529: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Hold_Exists',1 ) ;
5525: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5526:
5527: BEGIN
5528: IF l_debug_level > 0 THEN
5529: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Hold_Exists',1 ) ;
5530: END IF;
5531: IF p_org_id IS NULL THEN
5532:
5533: SELECT 'Y'
5550: AND ORG_ID = p_org_id;
5551:
5552: END IF;
5553: IF l_debug_level > 0 THEN
5554: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Hold_Exists',1 ) ;
5555: END IF;
5556:
5557: IF l_hold_exists = 'Y' THEN
5558: return true;
5564: WHEN NO_DATA_FOUND THEN
5565: return false;
5566: WHEN TOO_MANY_ROWS THEN
5567: IF l_debug_level > 0 THEN
5568: oe_debug_pub.add( 'TOO_MANY_ROWS exception in OE_HOLDS_PUB.Hold_Exists' ) ;
5569: END IF;
5570: return true;
5571: WHEN others THEN
5572: IF l_debug_level > 0 THEN
5569: END IF;
5570: return true;
5571: WHEN others THEN
5572: IF l_debug_level > 0 THEN
5573: oe_debug_pub.add( 'Exception in OE_HOLDS_PUB.Hold_Exists' ) ;
5574: END IF;
5575: return false;
5576: END Hold_exists;
5577:
5596:
5597: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5598: l_org_id NUMBER := null;
5599: l_hold_source_id NUMBER := null;
5600: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5601:
5602: CURSOR c_hold_orgs IS
5603: SELECT DISTINCT org_id
5604: FROM HZ_CUST_ACCT_SITES_ALL acct
5635: AND nvl(released_flag,'N') = 'N';
5636:
5637: BEGIN
5638: IF l_debug_level > 0 THEN
5639: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5640: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||' EntityID:'|| p_hold_entity_id||' HoldId: ' || p_hold_id||' p_Action: ' || p_action, 1);
5641: END IF;
5642: l_hold_source_rec.hold_entity_code := p_hold_entity_code;
5643: l_hold_source_rec.hold_entity_id := p_hold_entity_id;
5636:
5637: BEGIN
5638: IF l_debug_level > 0 THEN
5639: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5640: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||' EntityID:'|| p_hold_entity_id||' HoldId: ' || p_hold_id||' p_Action: ' || p_action, 1);
5641: END IF;
5642: l_hold_source_rec.hold_entity_code := p_hold_entity_code;
5643: l_hold_source_rec.hold_entity_id := p_hold_entity_id;
5644: l_hold_source_rec.hold_id := p_hold_id;
5651: EXIT WHEN c_hold_orgs%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5652: -- can l_org_id be null??? No.
5653:
5654: IF l_debug_level > 0 THEN
5655: oe_debug_pub.add('Applying Hold in ORG_ID ' || l_org_id, 1);
5656: END IF;
5657:
5658: MO_GLOBAL.set_policy_context('S',l_org_id);
5659:
5665: ,x_msg_count => x_msg_count
5666: ,x_msg_data => x_msg_data );
5667:
5668: IF l_debug_level > 0 THEN
5669: oe_debug_pub.add('oe_holds_pvt.apply_Holds:x_return_status ' || x_return_status, 1);
5670: END IF;
5671:
5672: l_return_status := x_return_status;
5673:
5683: FETCH c_cust_holds into l_org_id,l_hold_source_id;
5684: EXIT WHEN c_cust_holds%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5685:
5686: IF l_debug_level > 0 THEN
5687: oe_debug_pub.add('Releasing Hold in ORG_ID ' || l_org_id, 1);
5688: END IF;
5689:
5690: l_hold_source_rec.hold_source_id := l_hold_source_id;
5691:
5698: ,x_msg_count => x_msg_count
5699: ,x_msg_data => x_msg_data);
5700:
5701: IF l_debug_level > 0 THEN
5702: oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status ' || x_return_status, 1);
5703: END IF;
5704:
5705: l_return_status := x_return_status;
5706:
5706:
5707: END LOOP;
5708: END IF;
5709: IF l_debug_level > 0 THEN
5710: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5711: END IF;
5712:
5713: EXCEPTION
5714: WHEN FND_API.G_EXC_ERROR THEN
5713: EXCEPTION
5714: WHEN FND_API.G_EXC_ERROR THEN
5715:
5716: IF l_debug_level > 0 THEN
5717: oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5718: END IF;
5719: IF x_msg_count is not null then
5720: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5721: p_data => x_msg_data);
5723: RAISE FND_API.G_EXC_ERROR;
5724:
5725: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5726: IF l_debug_level > 0 THEN
5727: oe_debug_pub.add( 'API UNEXPECTED ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5728: END IF;
5729: IF x_msg_count is not null then
5730: OE_MSG_PUB.Count_And_Get
5731: ( p_count => x_msg_count
5735: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5736:
5737: WHEN OTHERS THEN
5738: IF l_debug_level > 0 THEN
5739: oe_debug_pub.add( 'ERROR in oe_holds_pub.Process_Holds_Customer' , 1 ) ;
5740: END IF;
5741: RAISE;
5742:
5743: END Process_Holds_Customer;
5763: l_hold_entity_code varchar2(1);
5764: l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5765: l_hold_exists VARCHAR2(1) := 'N';
5766: l_org_id NUMBER := null;
5767: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5768: BEGIN
5769: IF l_debug_level > 0 THEN
5770: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5771: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||'::EntityID: ' || p_hold_entity_id||'::HoldId: ' || p_hold_id||'::p_Action: ' || p_action, 1);
5766: l_org_id NUMBER := null;
5767: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5768: BEGIN
5769: IF l_debug_level > 0 THEN
5770: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5771: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||'::EntityID: ' || p_hold_entity_id||'::HoldId: ' || p_hold_id||'::p_Action: ' || p_action, 1);
5772: END IF;
5773:
5774: -- Fetch the org_id of the site that is being sent to put on hold
5767: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5768: BEGIN
5769: IF l_debug_level > 0 THEN
5770: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5771: oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||'::EntityID: ' || p_hold_entity_id||'::HoldId: ' || p_hold_id||'::p_Action: ' || p_action, 1);
5772: END IF;
5773:
5774: -- Fetch the org_id of the site that is being sent to put on hold
5775: -- The reason, the caller may not have set the correct org
5781: WHERE site_use_id = p_hold_entity_id;
5782:
5783: IF l_org_id is null then
5784: IF l_debug_level > 0 THEN
5785: oe_debug_pub.add( 'ORG_ID for the site is null. Invalid site ||p_hold_entity_id' , 1 ) ;
5786: END IF;
5787: raise no_data_found; -- Should not occur at all
5788: END IF;
5789: EXCEPTION
5796: RAISE FND_API.G_EXC_ERROR;
5797: END;
5798:
5799: IF l_debug_level > 0 THEN
5800: oe_debug_pub.add('Process_Holds_Site:ORG_ID: ' || l_org_id, 1);
5801: END IF;
5802:
5803: MO_GLOBAL.set_policy_context('S',l_org_id);
5804:
5825: ,x_msg_count => x_msg_count
5826: ,x_msg_data => x_msg_data );
5827:
5828: IF l_debug_level > 0 THEN
5829: oe_debug_pub.add('oe_holds_pvt.apply:x_return_status: ' || x_return_status, 1);
5830: END IF;
5831:
5832: -- Exceptions and return status are checked in process_holds
5833: END IF; -- hold not exists
5841: ,x_msg_data => x_msg_data);
5842:
5843:
5844: IF l_debug_level > 0 THEN
5845: oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status: ' || x_return_status, 1);
5846: END IF;
5847:
5848: IF l_debug_level > 0 THEN
5849: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5845: oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status: ' || x_return_status, 1);
5846: END IF;
5847:
5848: IF l_debug_level > 0 THEN
5849: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5850: END IF;
5851: END IF; -- APPLY OR RELEASE
5852: EXCEPTION
5853: WHEN FND_API.G_EXC_ERROR THEN
5852: EXCEPTION
5853: WHEN FND_API.G_EXC_ERROR THEN
5854:
5855: IF l_debug_level > 0 THEN
5856: oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5857: END IF;
5858:
5859: IF x_msg_count is not null then
5860: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5864: RAISE FND_API.G_EXC_ERROR;
5865:
5866: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5867: IF l_debug_level > 0 THEN
5868: oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5869: END IF;
5870:
5871: FND_MSG_PUB.Count_And_Get
5872: ( p_count => x_msg_count
5876: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5877:
5878: WHEN OTHERS THEN
5879: IF l_debug_level > 0 THEN
5880: oe_debug_pub.add( 'ERROR in oe_holds_pub.process_holds' , 1 ) ;
5881: END IF;
5882: RAISE;
5883: END Process_Holds_Site;
5884:
5916: l_msg_count NUMBER := null;
5917: l_msg_data VARCHAR2(2000) := null;
5918: l_valid_acct VARCHAR2(1) := 'N';
5919:
5920: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5921:
5922: CURSOR c_valid_acct IS
5923: SELECT 'Y'
5924: FROM HZ_CUST_ACCOUNTS_ALL
5926:
5927: BEGIN
5928:
5929: IF l_debug_level > 0 THEN
5930: oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5931: END IF;
5932:
5933: SAVEPOINT oe_process_holds;
5934:
5965:
5966: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5967:
5968: IF l_debug_level > 0 THEN
5969: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5970: END IF;
5971:
5972: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5973: RAISE FND_API.G_EXC_ERROR;
6004:
6005: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6006:
6007: IF l_debug_level > 0 THEN
6008: oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
6009: END IF;
6010:
6011: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
6012: RAISE FND_API.G_EXC_ERROR;
6025:
6026: x_return_status := l_return_status;
6027:
6028: IF l_debug_level > 0 THEN
6029: oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
6030: END IF;
6031: EXCEPTION
6032: WHEN FND_API.G_EXC_ERROR THEN
6033:
6031: EXCEPTION
6032: WHEN FND_API.G_EXC_ERROR THEN
6033:
6034: IF l_debug_level > 0 THEN
6035: oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
6036: END IF;
6037: x_return_status := l_return_status;
6038: x_msg_count := l_msg_count;
6039: x_msg_data := l_msg_data;
6042: p_data => l_msg_data);
6043: end if;
6044:
6045: IF l_debug_level > 0 THEN
6046: oe_debug_pub.add( 'Error Message: '||l_msg_data , 1 ) ;
6047: END IF;
6048: ROLLBACK TO SAVEPOINT oe_process_holds;
6049:
6050: -- Start Bug 14040669
6058:
6059: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6060:
6061: IF l_debug_level > 0 THEN
6062: oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
6063: END IF;
6064: x_return_status := l_return_status;
6065: x_msg_count := l_msg_count;
6066: x_msg_data := l_msg_data;
6068: ( p_count => l_msg_count
6069: , p_data => l_msg_data
6070: );
6071: IF l_debug_level > 0 THEN
6072: oe_debug_pub.add( 'Error Message: '||l_msg_data , 1 ) ;
6073: END IF;
6074: ROLLBACK TO SAVEPOINT oe_process_holds;
6075:
6076: -- Start Bug 14040669
6084: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6085:
6086: WHEN OTHERS THEN
6087: IF l_debug_level > 0 THEN
6088: oe_debug_pub.add( 'ERROR in oe_holds_pub.process_holds' , 1 ) ;
6089: END IF;
6090: x_return_status := l_return_status;
6091: x_msg_count := l_msg_count;
6092: x_msg_data := l_msg_data;