[Home] [Help]
PACKAGE BODY: APPS.OE_HOLDS_PUB
Source
1 PACKAGE BODY OE_Holds_PUB AS
2 /* $Header: OEXPHLDB.pls 120.10.12010000.9 2009/01/22 13:48:28 cpati ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Holds_PUB';
7
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
17 END Utilities;
18
19
20 FUNCTION Hold_Site_Code (
21 --ER#7479609 p_hold_entity_id IN NUMBER
22 p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
23 )
24 RETURN VARCHAR2
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
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 */
39 BEGIN
40 SELECT site_use_code
41 INTO l_site_use_code
42 FROM hz_cust_site_uses -- Bug 2138398
43 WHERE site_use_id = p_hold_entity_id;
44 EXCEPTION
45 WHEN no_data_found then
46 --x_return_status := FND_API.G_RET_STS_ERROR;
47 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
48 OE_MSG_PUB.ADD;
49 fnd_message.set_token('SITE_USE_ID',
50 to_char(p_hold_entity_id));
51 IF l_debug_level > 0 THEN
52 oe_debug_pub.add( 'HOLD_SITE_CODE:INVALID SITE USE ID' , 1 ) ;
53 END IF;
54 RAISE FND_API.G_EXC_ERROR;
55 END;
56 IF l_site_use_code = 'BILL_TO' THEN
57 l_hold_site_code := 'B';
58 ELSE
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;
67
68
69 /*
70
71 */
72 PROCEDURE UPDATE_HOLD_COMMENTS (
73 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
74 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
75 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
76 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
77
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' ) ;
87 END IF;
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;
97 END IF;
98
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100 /* Either hold_source_id should be passed in OR
101 Hold_entity_code and Hold_entity_id should be passed in */
102
103 If p_hold_source_rec.HOLD_SOURCE_ID is NOT NULL THEN
104
105 UPDATE OE_HOLD_SOURCES
106 SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT
107 WHERE HOLD_SOURCE_ID = p_hold_source_rec.HOLD_SOURCE_ID;
108
109 elsif (p_hold_source_rec.HOLD_ENTITY_CODE is NOT NULL AND
110 p_hold_source_rec.HOLD_ENTITY_ID is NOT NULL) THEN
111
112 if (p_hold_source_rec.HOLD_ENTITY_CODE2 is NOT NULL AND
113 p_hold_source_rec.HOLD_ENTITY_ID2 is NOT NULL) THEN
114 UPDATE OE_HOLD_SOURCES
115 SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT
116 WHERE HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
117 AND HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
118 AND HOLD_ENTITY_CODE2 = p_hold_source_rec.HOLD_ENTITY_CODE2
119 AND HOLD_ENTITY_ID2 = p_hold_source_rec.HOLD_ENTITY_ID2
120 AND HOLD_ID = p_hold_source_rec.hold_id
121 AND RELEASED_FLAG = 'N'
122 AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
123
124 else
125 /* Check to see if its a line-level hold */
126 if p_hold_source_rec.line_id is not null then
127 UPDATE OE_HOLD_SOURCES HS
128 SET HS.HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT
129 WHERE HS.HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
130 AND HS.HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
131 AND HS.HOLD_ENTITY_CODE2 is null
132 AND HS.HOLD_ENTITY_ID2 is null
133 AND HS.HOLD_ID = p_hold_source_rec.hold_id
134 AND HS.RELEASED_FLAG = 'N'
135 AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
136 AND exists (SELECT 'x'
137 FROM OE_ORDER_HOLDS OH
138 WHERE OH.LINE_ID = p_hold_source_rec.line_id
139 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID);
140 else
141 UPDATE OE_HOLD_SOURCES
142 SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT
143 WHERE HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
144 AND HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
145 AND HOLD_ENTITY_CODE2 is null
146 AND HOLD_ENTITY_ID2 is null
147 AND HOLD_ID = p_hold_source_rec.hold_id
148 AND RELEASED_FLAG = 'N'
149 AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
150 end if;
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
160 EXCEPTION /* Procedure exception handler */
161
162 WHEN FND_API.G_EXC_ERROR THEN
163 x_return_status := FND_API.G_RET_STS_ERROR ;
164 IF l_debug_level > 0 THEN
165 oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
166 END IF;
167 OE_MSG_PUB.Count_And_Get
168 ( p_count => x_msg_count,
169 p_data => x_msg_data
170 );
171
172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
174 IF l_debug_level > 0 THEN
175 oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
176 END IF;
177 OE_MSG_PUB.Count_And_Get
178 ( p_count => x_msg_count,
179 p_data => x_msg_data
180 );
181
182 WHEN OTHERS THEN
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
184 IF l_debug_level > 0 THEN
185 oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
186 END IF;
187 IF OE_MSG_PUB.Check_Msg_Level
188 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
189 THEN
190 OE_MSG_PUB.Add_Exc_Msg
191 (G_PKG_NAME,
192 l_api_name
193 );
194 END IF;
195 OE_MSG_PUB.Count_And_Get
196 ( p_count => x_msg_count,
197 p_data => x_msg_data);
198
199
200 END UPDATE_HOLD_COMMENTS;
201
202 /*
203 This procedure gets called from the concurrant manager. It will release
204 all the holds that have expired.
205 */
206 PROCEDURE RELEASE_EXPIRED_HOLDS
207 (
208 p_dummy1 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
209 p_dummy2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
210 p_org_id IN NUMBER
211 )
212 IS
213 l_api_name CONSTANT VARCHAR2(30) := 'release_expired_holds';
214 l_api_version CONSTANT NUMBER := 1.0;
215 l_hold_source_id NUMBER := 0;
216
217 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
218 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
219
220 l_return_status VARCHAR2(30);
221 l_msg_count NUMBER;
222 l_msg_data VARCHAR2(240);
223 l_org_id number;
224 l_curr_org_id number;
225
226 CURSOR expired_holds_cur IS
227 select hold_source_id, ORG_ID
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' ) ;
237 oe_debug_pub.add( 'p_org_id:' || to_char(p_org_id) ) ;
238 END IF;
239 l_return_status := FND_API.G_RET_STS_SUCCESS;
240
241 If p_org_id IS Not Null Then
242 -- Set Single Org access
243 MO_GLOBAL.SET_POLICY_CONTEXT('S', p_org_id);
244 l_curr_org_id := p_org_id;
245 Else
246 MO_GLOBAL.set_policy_context('M', '');
247 END IF;
248
249
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) ||
259 ', ORG ID:' || TO_CHAR(l_org_id) );
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;
269
270 l_hold_source_rec.hold_source_id := l_hold_source_id;
271 l_hold_release_rec.RELEASE_REASON_CODE := 'EXPIRE';
272 l_hold_release_rec.RELEASE_COMMENT :=
273 'Expired Hold, Automatically Released';
274
275 oe_holds_pvt.Release_Holds(
276 p_hold_source_rec => l_hold_source_rec
277 ,p_hold_release_rec => l_hold_release_rec
278 ,x_return_status => l_return_status
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
288 oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS UNEXPECTED FAILURE' ) ;
289 END IF;
290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
291 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
292 IF l_debug_level > 0 THEN
293 oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS EXPECTED FAILURE' ) ;
294 END IF;
295 RAISE FND_API.G_EXC_ERROR;
296 END IF;
297
298 end loop;
299
300 l_return_status := FND_API.G_RET_STS_SUCCESS;
301 -- Get message count and data
302 OE_MSG_PUB.Count_And_Get
303 ( p_count => l_msg_count
304 , p_data => l_msg_data
305 );
306
307 EXCEPTION /* Procedure exception handler */
308
309 WHEN FND_API.G_EXC_ERROR THEN
310 l_return_status := FND_API.G_RET_STS_ERROR ;
311 OE_MSG_PUB.Count_And_Get
312 ( p_count => l_msg_count,
313 p_data => l_msg_data
314 );
315
316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
317 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
318 OE_MSG_PUB.Count_And_Get
319 ( p_count => l_msg_count,
320 p_data => l_msg_data
321 );
322
323 WHEN OTHERS THEN
324 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
325 IF OE_MSG_PUB.Check_Msg_Level
326 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
327 THEN
328 OE_MSG_PUB.Add_Exc_Msg
329 (G_PKG_NAME,
330 l_api_name
331 );
332 END IF;
333 OE_MSG_PUB.Count_And_Get
334 ( p_count => l_msg_count,
335 p_data => l_msg_data);
336
337 END release_expired_holds;
338
339 ----------------------------
340 PROCEDURE ValidateOrder
341 (p_header_id IN NUMBER DEFAULT NULL
342 , p_line_id IN NUMBER DEFAULT NULL
343 , p_hold_entity_code IN VARCHAR2
344 --ER#7479609 , p_hold_entity_id IN NUMBER
345 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
346 , p_hold_entity_code2 IN VARCHAR2
347 --ER#7479609 , p_hold_entity_id2 IN NUMBER
348 , p_hold_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE --ER#7479609
349 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
350 )
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
360 oe_debug_pub.add( 'IN OE_HOLDS_PUB.VALIDATEORDER' ) ;
361 END IF;
362
363 -- Initialize API return status to success
364 x_return_status := FND_API.G_RET_STS_SUCCESS;
365
366 BEGIN
367
368 IF p_line_id IS NOT NULL THEN
369
370 IF p_hold_entity_code = 'O' THEN
371
372 SELECT 'Valid Entity'
373 INTO l_dummy
374 FROM OE_ORDER_LINES
375 WHERE LINE_ID = p_line_id
376 AND HEADER_ID = p_hold_entity_id
377 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
378
379 ELSIF p_hold_entity_code = 'I' THEN
380
381 SELECT 'Valid Entity'
382 INTO l_dummy
383 FROM OE_ORDER_LINES
384 WHERE LINE_ID = p_line_id
385 AND INVENTORY_ITEM_ID = p_hold_entity_id
386 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
387
388 ELSIF p_hold_entity_code = 'S' THEN
389
390 -- validation data based on bill-to or ship-to site to be inserted here.
391 null;
392
393 ELSIF p_hold_entity_code = 'C' THEN
394 -- XXXXvalidation data based on Customer based holds -- Not needed at the line level
395 null;
396 ELSE
397 -- add error message
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401 -------------------------------
402 -- Check the Second entity --
403 -------------------------------
404 IF p_hold_entity_code2 = 'O' THEN
405
406 SELECT 'Valid Entity'
407 INTO l_dummy
408 FROM OE_ORDER_LINES
409 WHERE LINE_ID = p_line_id
410 AND HEADER_ID = p_hold_entity_id2
411 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
412
413 ELSIF p_hold_entity_code2 = 'I' THEN
414
415 SELECT 'Valid Entity'
416 INTO l_dummy
417 FROM OE_ORDER_LINES
418 WHERE LINE_ID = p_line_id
419 AND INVENTORY_ITEM_ID = p_hold_entity_id2
420 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
421
422 ELSIF p_hold_entity_code2 = 'S' THEN
423
424 -- validation data based on bill-to or ship-to site to be inserted here.
425 null;
426
427 ELSIF p_hold_entity_code2 = 'C' THEN
428 -- XXXXvalidation data based on Customer based holds -- Not needed at the line level
429 null;
430 ELSE
431 -- add error message
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434 ------------------------------
435
436 ELSIF p_line_id IS NULL THEN
437
438 IF p_hold_entity_code = 'O' THEN
439 -- XXX
440 IF (p_header_id <> p_hold_entity_id) THEN
441 RAISE FND_API.G_EXC_ERROR;
442 END IF;
443
444 SELECT 'Valid Entity'
445 INTO l_dummy
446 FROM OE_ORDER_HEADERS
447 WHERE HEADER_ID = p_header_id
448 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
449
450
451 ELSIF p_hold_entity_code = 'C' THEN
452
453 SELECT 'Valid Entity'
454 INTO l_dummy
455 FROM OE_ORDER_HEADERS
456 WHERE HEADER_ID = p_header_id
457 AND SOLD_TO_ORG_ID = p_hold_entity_id
458 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
459
460 ELSIF p_hold_entity_code = 'S' THEN
461 -- XXX Confirm this code
462 SELECT 'Valid Entity'
463 INTO l_dummy
464 FROM OE_ORDER_HEADERS
465 WHERE HEADER_ID = p_header_id
466 AND SHIP_TO_ORG_ID = p_hold_entity_id
467 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
468 ELSE
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471 ------------------------------
472 -- Check the Second Entity --
473 ------------------------------
474 IF p_hold_entity_code2 is not null THEN
475 IF p_hold_entity_code2 = 'O' THEN
476
477 IF (p_header_id <> p_hold_entity_id2) THEN
478 RAISE FND_API.G_EXC_ERROR;
479 END IF;
480
481 SELECT 'Valid Entity'
482 INTO l_dummy
483 FROM OE_ORDER_HEADERS
484 WHERE HEADER_ID = p_hold_entity_id2
485 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
486
487
488 ELSIF p_hold_entity_code2 = 'C' THEN
489
490 SELECT 'Valid Entity'
491 INTO l_dummy
492 FROM OE_ORDER_HEADERS
493 WHERE HEADER_ID = p_header_id
494 AND SOLD_TO_ORG_ID = p_hold_entity_id2
495 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
496
497 ELSIF p_hold_entity_code2 = 'S' THEN
498 -- XXX Confirm this code
499 SELECT 'Valid Entity'
500 INTO l_dummy
501 FROM OE_ORDER_HEADERS
502 WHERE HEADER_ID = p_header_id
503 AND SHIP_TO_ORG_ID = p_hold_entity_id2
504 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
505 ELSE
506 RAISE FND_API.G_EXC_ERROR;
507 END IF;
508 END IF;
509 ------------------------------
510 END IF;
511
512 EXCEPTION
513 WHEN NO_DATA_FOUND THEN
514 RAISE FND_API.G_EXC_ERROR;
515 END;
516
517
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTITY_NOT_ON_ORDER_OR_LINE');
521 OE_MSG_PUB.ADD;
522 IF l_debug_level > 0 THEN
523 oe_debug_pub.add( 'EXPECTED ERROR IN VALIDATEORDER' ) ;
524 END IF;
525 x_return_status := FND_API.G_RET_STS_ERROR;
526 WHEN OTHERS THEN
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528 IF FND_MSG_PUB.Check_Msg_Level
529 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
530 THEN
531 FND_MSG_PUB.Add_Exc_Msg
532 ( G_PKG_NAME
533 , l_api_name
534 );
535 END IF;
536 END ValidateOrder;
537 -- END OF LOCAL PROCEDURES
538
539
540 ------------------
541 -- APPLY_HOLDS --
542 ------------------
543 -- This is and overloaded procedure that calls the new Holds API
544 ---------------------------------------------------------------
545
546 Procedure Apply_Holds (
547 p_api_version IN NUMBER,
548 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
549 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
550 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
551 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
552 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
553 p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE DEFAULT NULL,
554 p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE DEFAULT NULL,
555 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
556 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
557 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
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
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
572 -- org_id is null, raise an error.
573 oe_debug_pub.add('Org_Id is NULL',1);
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
576 FND_MSG_PUB.ADD;
577 RAISE FND_API.G_EXC_ERROR;
578 END IF;
579
580 -- Initialize API return status to success
581 x_return_status := FND_API.G_RET_STS_SUCCESS;
582
583 oe_holds_pvt.apply_holds(
584 p_order_tbl => p_order_tbl,
585 p_hold_id => p_hold_id,
586 p_hold_until_date => p_hold_until_date,
587 p_hold_comment => p_hold_comment,
588 x_return_status => x_return_status,
589 x_msg_count => x_msg_count,
590 x_msg_data => x_msg_data
591 );
592 EXCEPTION
593 WHEN FND_API.G_EXC_ERROR THEN
594 ROLLBACK TO APPLY_HOLDS_PUB;
595 IF l_debug_level > 0 THEN
596 oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
597 END IF;
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 FND_MSG_PUB.Count_And_Get
600 ( p_count => x_msg_count
601 , p_data => x_msg_data
602 );
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605 FND_MSG_PUB.Count_And_Get
606 ( p_count => x_msg_count
607 , p_data => x_msg_data
608 );
609 WHEN OTHERS THEN
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 IF FND_MSG_PUB.Check_Msg_Level
612 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
613 THEN
614 FND_MSG_PUB.Add_Exc_Msg
615 ( G_PKG_NAME
616 , l_api_name
617 );
618 END IF;
619 FND_MSG_PUB.Count_And_Get
620 ( p_count => x_msg_count
621 , p_data => x_msg_data
622 );
623
624 END Apply_Holds;
625
626 Procedure Apply_Holds(
627 p_api_version IN NUMBER,
628 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
629 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
630 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
631 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
632 DEFAULT OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC,
633 p_hold_existing_flg IN VARCHAR2 DEFAULT 'Y',
634 p_hold_future_flg IN VARCHAR2 DEFAULT 'Y',
635 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
636 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
637 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
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
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.
652 oe_debug_pub.add('Org_Id is NULL',1);
653 x_return_status := FND_API.G_RET_STS_ERROR;
654 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
655 FND_MSG_PUB.ADD;
656 RAISE FND_API.G_EXC_ERROR;
657 END IF;
658
659 -- Initialize API return status to success
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 --dbms_output.put_line ('IN PUB.ApplyHolds'); -- delete
663 oe_holds_pvt.apply_Holds(
664 p_hold_source_rec => p_hold_source_rec
665 ,p_hold_existing_flg => p_hold_existing_flg
666 ,p_hold_future_flg => p_hold_future_flg
667 ,x_return_status => x_return_status
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
677 IF l_debug_level > 0 THEN
678 oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
679 END IF;
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 FND_MSG_PUB.Count_And_Get
682 ( p_count => x_msg_count
683 , p_data => x_msg_data
684 );
685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687 FND_MSG_PUB.Count_And_Get
688 ( p_count => x_msg_count
689 , p_data => x_msg_data
690 );
691 WHEN OTHERS THEN
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 IF FND_MSG_PUB.Check_Msg_Level
694 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
695 THEN
696 FND_MSG_PUB.Add_Exc_Msg
697 ( G_PKG_NAME
698 , l_api_name
699 );
700 END IF;
701 FND_MSG_PUB.Count_And_Get
702 ( p_count => x_msg_count
703 , p_data => x_msg_data
704 );
705
706 END Apply_Holds;
707
708 ---------------------------------
709 -- New Release Holds Spec --
710 --------------------------------
711 Procedure Release_Holds (
712 p_api_version IN NUMBER DEFAULT 1.0,
713 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
714 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
715 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
716 p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
717 p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
718 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
719 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
720 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
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
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.
735 oe_debug_pub.add('Org_Id is NULL',1);
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
738 FND_MSG_PUB.ADD;
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741
742 -- Initialize API return status to success
743 x_return_status := FND_API.G_RET_STS_SUCCESS;
744
745 -- dbms_output.put_line ('IN RELEASE_HOLDS->'); -- delete
746 oe_holds_pvt.Release_Holds(
747 p_hold_source_rec => p_hold_source_rec
748 ,p_hold_release_rec => p_hold_release_rec
749 ,x_return_status => x_return_status
750 ,x_msg_count => x_msg_count
751 ,x_msg_data => x_msg_data
752 );
753 EXCEPTION
754 WHEN FND_API.G_EXC_ERROR THEN
755 IF l_debug_level > 0 THEN
756 oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
757 END IF;
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 FND_MSG_PUB.Count_And_Get
760 ( p_count => x_msg_count
761 , p_data => x_msg_data
762 );
763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765 FND_MSG_PUB.Count_And_Get
766 ( p_count => x_msg_count
767 , p_data => x_msg_data
768 );
769 WHEN OTHERS THEN
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771 IF FND_MSG_PUB.Check_Msg_Level
772 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
773 THEN
774 FND_MSG_PUB.Add_Exc_Msg
775 ( G_PKG_NAME
776 , l_api_name
777 );
778 END IF;
779 FND_MSG_PUB.Count_And_Get
780 ( p_count => x_msg_count
781 , p_data => x_msg_data
782 );
783
784 END Release_Holds;
785
786 Procedure Release_Holds (
787 p_api_version IN NUMBER DEFAULT 1.0,
788 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
789 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
790 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
791 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
792 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
793 DEFAULT NULL,
794 p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE,
795 p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
796 DEFAULT NULL,
797 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
798 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
799 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
800 IS
801 l_api_name CONSTANT VARCHAR2(30) := 'Release_holds';
802 l_header_id NUMBER DEFAULT NULL;
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
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
817 -- org_id is null, raise an error.
818 oe_debug_pub.add('Org_Id is NULL',1);
819 x_return_status := FND_API.G_RET_STS_ERROR;
820 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
821 FND_MSG_PUB.ADD;
822 RAISE FND_API.G_EXC_ERROR;
823 END IF;
824
825 -- Initialize API return status to success
826 x_return_status := FND_API.G_RET_STS_SUCCESS;
827
828 for j in 1..p_order_tbl.COUNT loop
829 if p_order_tbl(j).header_id is NULL AND
830 p_order_tbl(j).line_id is NULL THEN
831 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
832 OE_MSG_PUB.ADD;
833 RAISE FND_API.G_EXC_ERROR;
834 ELSIF p_order_tbl(j).header_id is NULL THEN
835 SELECT header_id
836 INTO l_header_id
837 FROM OE_ORDER_LINES
838 WHERE LINE_ID = p_order_tbl(j).line_id;
839 l_order_tbl(j).header_id := l_header_id;
840 l_order_tbl(j).line_id := p_order_tbl(j).line_id;
841 ELSE
842 l_order_tbl(j).header_id := p_order_tbl(j).header_id;
843 l_order_tbl(j).line_id := p_order_tbl(j).line_id;
844 END IF;
845 end loop;
846
847 oe_holds_pvt.release_holds(
848 p_order_tbl => l_order_tbl,
849 p_hold_id => p_hold_id,
850 p_release_reason_code => p_release_reason_code,
851 p_release_comment => p_release_comment,
852 x_return_status => x_return_status,
853 x_msg_count => x_msg_count,
854 x_msg_data => x_msg_data
855 );
856
857 EXCEPTION
858 WHEN FND_API.G_EXC_ERROR THEN
859 IF l_debug_level > 0 THEN
860 oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
861 END IF;
862 x_return_status := FND_API.G_RET_STS_ERROR;
863 FND_MSG_PUB.Count_And_Get
864 ( p_count => x_msg_count
865 , p_data => x_msg_data
866 );
867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869 FND_MSG_PUB.Count_And_Get
870 ( p_count => x_msg_count
871 , p_data => x_msg_data
872 );
873 WHEN OTHERS THEN
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875 IF FND_MSG_PUB.Check_Msg_Level
876 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
877 THEN
878 FND_MSG_PUB.Add_Exc_Msg
879 ( G_PKG_NAME
880 , l_api_name
881 );
882 END IF;
883 FND_MSG_PUB.Count_And_Get
884 ( p_count => x_msg_count
885 , p_data => x_msg_data
886 );
887
888 END Release_Holds;
889
890
891 --------------------------------------------------------------------------
892 -- APPLY HOLDS
893 -- This procedure can be used to apply holds for the following two cases:
894 -- 1. Hold Source has already been created. Pass just the hold source ID
895 -- (p_hold_source_id) and use that hold source to place the order
896 -- (p_header_id) or the order line (p_line_id) on hold.
897 -- 2. Check if the hold source exists (p_hold_id, p_entity_code,
898 -- p_entity_id should be passed). If it exists, use that hold source to
899 -- place the hold . If it doesn't, create a new hold source and
900 -- then put the order or line on hold.
901 -- Note: Leaving this call for backward compatibility. AR's Customer form
902 -- still calls the old oe_holds (OEXOHAPB.pls) which in turns calls
903 -- this api.
904 --------------------------------------------------------------------------
905
906 PROCEDURE Apply_Holds
907 ( p_api_version IN NUMBER
908 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
909 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
910 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
911 , p_header_id IN NUMBER DEFAULT NULL
912 , p_line_id IN NUMBER DEFAULT NULL
913 , p_hold_source_id IN NUMBER DEFAULT NULL
914 , p_hold_source_rec IN OE_Hold_Sources_Pvt.Hold_Source_REC
915 DEFAULT OE_Hold_Sources_Pvt.G_MISS_Hold_Source_REC
916 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
917 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
918 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
919 )
920 IS
921 l_api_version CONSTANT NUMBER := 1.0;
922 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_HOLDS';
923 l_user_id NUMBER;
924 l_hold_source_id NUMBER := 0;
925 l_dummy VARCHAR2(30);
926 l_order_holds_s NUMBER := 0;
927 --ER#7479609 l_entity_code VARCHAR2(1);
928 l_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
929 l_entity_id NUMBER;
930 --
931 --ER#7479609 l_entity_code2 VARCHAR2(1);
932 l_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
933 l_entity_id2 NUMBER;
934 l_header_id NUMBER DEFAULT NULL;
935
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;
945 IF l_org_id IS NULL THEN
946 -- org_id is null, raise an error.
947 oe_debug_pub.add('Org_Id is NULL',1);
948 x_return_status := FND_API.G_RET_STS_ERROR;
949 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
950 FND_MSG_PUB.ADD;
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
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
960 -- Initialize API return status to success
961 x_return_status := FND_API.G_RET_STS_SUCCESS;
962
963 Utilities(l_user_id);
964
965
966 ----------------------------------------------------------------
967 -- CASE I: Hold Source ID is KNOWN
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
977
978 -- Check if the hold source ID is valid
979 BEGIN
980
981 SELECT HOLD_ENTITY_CODE, HOLD_ENTITY_ID,
982 HOLD_ENTITY_CODE2, HOLD_ENTITY_ID2
983 INTO l_entity_code, l_entity_id,
984 l_entity_code2, l_entity_id2
985 FROM OE_HOLD_SOURCES
986 WHERE HOLD_SOURCE_ID = p_hold_source_id
987 AND RELEASED_FLAG = 'N'
988 AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
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;
998 RAISE FND_API.G_EXC_ERROR;
999 END;
1000
1001 -- END IF;
1002
1003 -------------------------------------------------------------
1004 -- CASE II: Hold Source ID is NOT AVAILABLE
1005 -------------------------------------------------------------
1006 ELSE
1007 -- Check for Missing Values
1008 IF p_hold_source_rec.hold_id IS NULL THEN
1009 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_ID');
1010 OE_MSG_PUB.ADD;
1011 RAISE FND_API.G_EXC_ERROR;
1012 END IF;
1013
1014 IF p_hold_source_rec.hold_entity_code IS NULL THEN
1015 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
1016 OE_MSG_PUB.ADD;
1017 RAISE FND_API.G_EXC_ERROR;
1018 END IF;
1019
1020 IF p_hold_source_rec.hold_entity_id IS NULL THEN
1021 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
1022 OE_MSG_PUB.ADD;
1023 RAISE FND_API.G_EXC_ERROR;
1024 END IF;
1025 -- NOTE: No need to check for HOLD_ENTITY_CODE2 and HOLD_ENTITY_ID2
1026 -- cos, its optional.
1027 ----------------------------------
1028 -- Check to see if the Site code is Bill_to OR Ship_to
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;
1038 END IF;
1039 l_entity_id := p_hold_source_rec.hold_entity_id;
1040
1041 END IF; -- END of check to see WHETHER HOLD SOURCE ID is passed.
1042 /*
1043 IF p_hold_source_rec.hold_entity_code = 'O' THEN
1044 IF p_line_id IS NULL AND p_header_id IS NULL THEN
1045 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
1046 OE_MSG_PUB.ADD;
1047 RAISE FND_API.G_EXC_ERROR;
1048 ELSIF p_header_id IS NULL THEN
1049 SELECT header_id
1050 INTO l_header_id
1051 FROM OE_ORDER_LINES
1052 WHERE LINE_ID = p_line_id;
1053 ELSE
1054 l_header_id := p_header_id;
1055 END IF;
1056 END IF; -- p_hold_source_rec.hold_entity_code = 'O'
1057 */
1058
1059 /* IF l_hold_source_id <> 0 THEN
1060 -- Check for duplicate hold
1061 BEGIN
1062 SELECT 'Duplicate Hold'
1063 INTO l_dummy
1064 FROM OE_ORDER_HOLDS
1065 WHERE hold_source_id = l_hold_source_id
1066 AND HEADER_ID = l_header_id
1067 AND NVL(LINE_ID, NVL(p_line_id,0)) = NVL(p_line_id, 0)
1068 AND HOLD_RELEASE_ID IS NULL
1069 AND ROWNUM = 1;
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
1079 null;
1080 END;
1081 END IF; -- l_hold_source_id <> 0
1082 */
1083 /*
1084 IF p_hold_source_rec.hold_entity_code = 'O' THEN
1085 ValidateOrder(p_header_id => p_header_id
1086 , p_line_id => p_line_id
1087 , p_hold_entity_code => l_entity_code
1088 , p_hold_entity_id => l_entity_id
1089 , p_hold_entity_code2 => l_entity_code2
1090 , p_hold_entity_id2 => l_entity_id2
1091 , x_return_status => x_return_status
1092 );
1093
1094 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1095 OE_Debug_PUB.Add('Validate Order not successful');
1096 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1097 RAISE FND_API.G_EXC_ERROR;
1098 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
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
1107 l_hold_source_rec.hold_entity_code := l_entity_code;
1108 l_hold_source_rec.hold_entity_id := l_entity_id;
1109 l_hold_source_rec.hold_entity_code2 := p_hold_source_rec.hold_entity_code2;
1110 l_hold_source_rec.hold_entity_id2 := p_hold_source_rec.hold_entity_id2;
1111 l_hold_source_rec.hold_id := p_hold_source_rec.hold_id;
1112
1113 /* In case the p_header_id and p_line_id is not null. This will only be the case
1114 if the old holds api (oe_holds) is being called to apply a header or line
1115 level hold. These are part of hold_source_rec npw.
1116 REMOVE this after it has been verified. */
1117 IF p_hold_source_rec.hold_entity_code = 'O' THEN
1118 IF p_header_id IS NOT NULL THEN
1119 l_hold_source_rec.header_id := p_header_id;
1120 ELSIF p_line_id IS NOT NULL THEN
1121 l_hold_source_rec.line_id := p_line_id;
1122 END IF;
1123 END IF;
1124
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'
1134 ,p_hold_future_flg => 'Y'
1135 ,x_return_status => x_return_status
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
1145 RAISE FND_API.G_EXC_ERROR;
1146 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1148 END IF;
1149 END IF;
1150
1151 EXCEPTION
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
1160 , p_data => x_msg_data
1161 );
1162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1163 ROLLBACK TO APPLY_HOLDS_PUB;
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165 FND_MSG_PUB.Count_And_Get
1166 ( p_count => x_msg_count
1167 , p_data => x_msg_data
1168 );
1169 WHEN OTHERS THEN
1170 ROLLBACK TO APPLY_HOLDS_PUB;
1171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1172 IF FND_MSG_PUB.Check_Msg_Level
1173 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1174 THEN
1175 FND_MSG_PUB.Add_Exc_Msg
1176 ( G_PKG_NAME
1177 , l_api_name
1178 );
1179 END IF;
1180 FND_MSG_PUB.Count_And_Get
1181 ( p_count => x_msg_count
1182 , p_data => x_msg_data
1183 );
1184 END Apply_Holds;
1185
1186 /**************************************************************/
1187
1188 /* An additional parameter 'p_hdr_id' was passed to this procedure.
1189 This was to improve the performance of a query in this procedure.
1190 Refer Bug1920064.
1191 */
1192
1193 PROCEDURE Check_Holds_line (
1194 p_hdr_id IN NUMBER
1195 , p_line_id IN NUMBER DEFAULT NULL
1196 , p_hold_id IN NUMBER DEFAULT NULL
1197 , p_wf_item IN VARCHAR2 DEFAULT NULL
1198 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1199 , p_entity_code IN VARCHAR2 DEFAULT NULL
1200 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1201 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1202 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1203 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1204 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1205 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1206 , p_ii_parent_flag IN VARCHAR2 DEFAULT 'N'
1207 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1208 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1209 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1210 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1211 )
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 ) ;
1221 END IF;
1222 -- Initialize API return status to success
1223 x_return_status := FND_API.G_RET_STS_SUCCESS;
1224
1225 -- Initialize result to TRUE i.e. holds are found
1226 x_result_out := FND_API.G_TRUE;
1227
1228 /*
1229 ** Fix Bug # 1920064
1230 ** The SQL below was modified. To improve the performance, the scan on
1231 ** the table OE_ORDER_LINES_ALL was avoided as the header_id was being
1232 ** passed as a parameter to this procedure. The UNION was removed and
1233 ** the whole SQL was re-written as below.
1234 ** Fix Bug # 2984023
1235 ** Modified the query to check for only line level holds
1236 */
1237
1238 /*
1239 ** Checking for LINE level generic and activity-specific holds
1240 */
1241 BEGIN
1242
1243 SELECT 'ANY_LINE_HOLD'
1244 INTO l_dummy
1245 FROM oe_order_holds_all oh
1246 WHERE oh.header_id = p_hdr_id
1247 and oh.line_id = p_line_id
1248 and oh.hold_release_id is null
1249 AND EXISTS
1250 (SELECT 1
1251 FROM oe_hold_sources_all hs,
1252 oe_hold_definitions h
1253 WHERE oh.hold_source_id = hs.hold_source_id
1254 AND hs.hold_id = h.hold_id
1255 AND NVL(h.item_type,
1256 DECODE(p_chk_act_hold_only,
1257 'Y', 'XXXXX',
1258 NVL(p_wf_item, 'NO ITEM')) ) =
1259 NVL(p_wf_item, 'NO ITEM')
1260 AND NVL(H.ACTIVITY_NAME,
1261 DECODE(p_chk_act_hold_only,
1262 'Y', 'XXXXX',
1263 NVL(p_wf_activity, 'NO ACT')) ) =
1264 NVL(p_wf_activity,'NO ACT')
1265 AND DECODE(p_ii_parent_flag, 'Y',
1266 nvl(h.hold_included_items_flag, 'N'), 'XXXXX') =
1267 DECODE(p_ii_parent_flag, 'Y', 'Y', 'XXXXX')
1268 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1269 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1270 AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
1271 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1272 ROUND( SYSDATE )
1273 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1274 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1275 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1276 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1277 AND NVL(hs.hold_entity_id2, -99) =
1278 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1279
1280 EXCEPTION
1281 WHEN NO_DATA_FOUND THEN
1282 x_result_out := FND_API.G_FALSE;
1283 IF l_debug_level > 0 THEN
1284 oe_debug_pub.add( 'NO HOLDS FOUND FOR LINE ID: ' || P_LINE_ID ) ;
1285 END IF;
1286 WHEN TOO_MANY_ROWS THEN
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
1296 x_return_status := FND_API.G_RET_STS_ERROR;
1297 x_result_out := FND_API.G_FALSE;
1298 FND_MSG_PUB.Count_And_Get
1299 ( p_count => x_msg_count
1300 , p_data => x_msg_data
1301 );
1302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1304 x_result_out := FND_API.G_FALSE;
1305 FND_MSG_PUB.Count_And_Get
1306 ( p_count => x_msg_count
1307 , p_data => x_msg_data
1308 );
1309 WHEN OTHERS THEN
1310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1311 x_result_out := FND_API.G_FALSE;
1312 IF FND_MSG_PUB.Check_Msg_Level
1313 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1314 THEN
1315 FND_MSG_PUB.Add_Exc_Msg
1316 ( G_PKG_NAME
1317 , l_api_name
1318 );
1319 END IF;
1320 FND_MSG_PUB.Count_And_Get
1321 ( p_count => x_msg_count
1322 , p_data => x_msg_data
1323 );
1324
1325 END CHECK_HOLDS_LINE;
1326
1327 /*
1328 ** Procedure to check holds on ANY line part of ATO Model.
1329 */
1330 PROCEDURE Check_Holds_ATO (
1331 p_hdr_id IN NUMBER
1332 , p_ato_line_id IN NUMBER DEFAULT NULL
1333 , p_top_model_line_id IN NUMBER DEFAULT NULL
1334 , p_hold_id IN NUMBER DEFAULT NULL
1335 , p_wf_item IN VARCHAR2 DEFAULT NULL
1336 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1337 , p_entity_code IN VARCHAR2 DEFAULT NULL
1338 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1339 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1340 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1341 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1342 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1343 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1344 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1345 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1346 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1347 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1348 )
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' ) ;
1358 END IF;
1359 -- Initialize API return status to success
1360 x_return_status := FND_API.G_RET_STS_SUCCESS;
1361
1362 -- Initialize result to TRUE i.e. holds are found
1363 x_result_out := FND_API.G_TRUE;
1364
1365 /*
1366 ** Checking for ATO level generic and activity-specific holds
1367 */
1368 BEGIN
1369
1370 SELECT /* MOAC_SQL_CHANGE */ 'ANY_ATO_LINE_HOLD'
1371 INTO l_dummy
1372 FROM oe_order_holds_all oh
1373 WHERE oh.header_id = p_hdr_id
1374 and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1375 where ol.header_id = oh.header_id
1376 and ol.ato_line_id = p_ato_line_id
1377 and ol.top_model_line_id = p_top_model_line_id)
1378 and oh.hold_release_id is null
1379 AND EXISTS
1380 (SELECT 1
1381 FROM oe_hold_sources_all hs,
1382 oe_hold_definitions h
1383 WHERE oh.hold_source_id = hs.hold_source_id
1384 AND hs.hold_id = h.hold_id
1385 AND NVL(h.item_type,
1386 DECODE(p_chk_act_hold_only,
1387 'Y', 'XXXXX',
1388 NVL(p_wf_item, 'NO ITEM')) ) =
1389 NVL(p_wf_item, 'NO ITEM')
1390 AND NVL(H.ACTIVITY_NAME,
1391 DECODE(p_chk_act_hold_only,
1392 'Y', 'XXXXX',
1393 NVL(p_wf_activity, 'NO ACT')) ) =
1394 NVL(p_wf_activity,'NO ACT')
1395 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1396 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1397 AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
1398 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1399 ROUND( SYSDATE )
1400 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1401 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1402 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1403 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1404 AND NVL(hs.hold_entity_id2, -99) =
1405 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1406
1407 EXCEPTION
1408 WHEN NO_DATA_FOUND THEN
1409 x_result_out := FND_API.G_FALSE;
1410 IF l_debug_level > 0 THEN
1411 oe_debug_pub.add( 'NO HOLDS FOUND FOR ATO LINE ID: ' || P_ATO_LINE_ID ) ;
1412 END IF;
1413 WHEN TOO_MANY_ROWS THEN
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
1423 x_return_status := FND_API.G_RET_STS_ERROR;
1424 x_result_out := FND_API.G_FALSE;
1425 FND_MSG_PUB.Count_And_Get
1426 ( p_count => x_msg_count
1427 , p_data => x_msg_data
1428 );
1429 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431 x_result_out := FND_API.G_FALSE;
1432 FND_MSG_PUB.Count_And_Get
1433 ( p_count => x_msg_count
1434 , p_data => x_msg_data
1435 );
1436 WHEN OTHERS THEN
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 x_result_out := FND_API.G_FALSE;
1439 IF FND_MSG_PUB.Check_Msg_Level
1440 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1441 THEN
1442 FND_MSG_PUB.Add_Exc_Msg
1443 ( G_PKG_NAME
1444 , l_api_name
1445 );
1446 END IF;
1447 FND_MSG_PUB.Count_And_Get
1448 ( p_count => x_msg_count
1449 , p_data => x_msg_data
1450 );
1451
1452 END CHECK_HOLDS_ATO;
1453
1454 /*
1455 ** Procedure to check holds on ANY line part of an SMC.
1456 */
1457
1458 PROCEDURE Check_Holds_SMC (
1459 p_hdr_id IN NUMBER
1460 , p_top_model_line_id IN NUMBER DEFAULT NULL
1461 , p_hold_id IN NUMBER DEFAULT NULL
1462 , p_wf_item IN VARCHAR2 DEFAULT NULL
1463 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1464 , p_entity_code IN VARCHAR2 DEFAULT NULL
1465 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1466 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1467 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1468 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1469 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1470 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1471 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1472 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1473 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1474 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1475 )
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' ) ;
1485 END IF;
1486 -- Initialize API return status to success
1487 x_return_status := FND_API.G_RET_STS_SUCCESS;
1488
1489 -- Initialize result to TRUE i.e. holds are found
1490 x_result_out := FND_API.G_TRUE;
1491
1492 /*
1493 ** Checking for SMC level generic and activity-specific holds
1494 */
1495 BEGIN
1496
1497 SELECT /* MOAC_SQL_CHANGE */ 'ANY_SMC_LINE_HOLD'
1498 INTO l_dummy
1499 FROM oe_order_holds_all oh
1500 WHERE oh.header_id = p_hdr_id
1501 and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1502 where ol.header_id = oh.header_id
1503 and ol.top_model_line_id = p_top_model_line_id)
1504 and oh.hold_release_id is null
1505 AND EXISTS
1506 (SELECT 1
1507 FROM oe_hold_sources_all hs,
1508 oe_hold_definitions h
1509 WHERE oh.hold_source_id = hs.hold_source_id
1510 AND hs.hold_id = h.hold_id
1511 AND NVL(h.item_type,
1512 DECODE(p_chk_act_hold_only,
1513 'Y', 'XXXXX',
1514 NVL(p_wf_item, 'NO ITEM')) ) =
1515 NVL(p_wf_item, 'NO ITEM')
1516 AND NVL(H.ACTIVITY_NAME,
1517 DECODE(p_chk_act_hold_only,
1518 'Y', 'XXXXX',
1519 NVL(p_wf_activity, 'NO ACT')) ) =
1520 NVL(p_wf_activity,'NO ACT')
1521 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1522 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1523 AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
1524 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1525 ROUND( SYSDATE )
1526 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1527 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1528 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1529 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1530 AND NVL(hs.hold_entity_id2, -99) =
1531 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1532
1533 EXCEPTION
1534 WHEN NO_DATA_FOUND THEN
1535 x_result_out := FND_API.G_FALSE;
1536 IF l_debug_level > 0 THEN
1537 oe_debug_pub.add( 'NO HOLDS FOUND FOR TOP MODEL LINE ID: ' || P_TOP_MODEL_LINE_ID ) ;
1538 END IF;
1539 WHEN TOO_MANY_ROWS THEN
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
1549 x_return_status := FND_API.G_RET_STS_ERROR;
1550 x_result_out := FND_API.G_FALSE;
1551 FND_MSG_PUB.Count_And_Get
1552 ( p_count => x_msg_count
1553 , p_data => x_msg_data
1554 );
1555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 x_result_out := FND_API.G_FALSE;
1558 FND_MSG_PUB.Count_And_Get
1559 ( p_count => x_msg_count
1560 , p_data => x_msg_data
1561 );
1562 WHEN OTHERS THEN
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564 x_result_out := FND_API.G_FALSE;
1565 IF FND_MSG_PUB.Check_Msg_Level
1566 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1567 THEN
1568 FND_MSG_PUB.Add_Exc_Msg
1569 ( G_PKG_NAME
1570 , l_api_name
1571 );
1572 END IF;
1573 FND_MSG_PUB.Count_And_Get
1574 ( p_count => x_msg_count
1575 , p_data => x_msg_data
1576 );
1577
1578 END CHECK_HOLDS_SMC;
1579
1580 /**************************************************************************/
1581
1582 -- Created for bug 2673236
1583 -- Check If any line in the order is on hold
1584
1585 PROCEDURE Check_Any_Line_Hold (
1586 x_hold_rec IN OUT NOCOPY OE_HOLDS_PUB.Any_Line_Hold_rec
1587 , x_return_status OUT NOCOPY VARCHAR2
1588 , x_msg_count OUT NOCOPY NUMBER
1589 , x_msg_data OUT NOCOPY VARCHAR2
1590 )
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;
1600
1601 -- Initialize result to TRUE i.e. holds are found
1602 x_hold_rec.x_result_out := FND_API.G_TRUE;
1603
1604 BEGIN
1605
1606 SELECT 'ANY_LINE_HOLD'
1607 INTO l_dummy
1608 FROM oe_order_holds_all oh
1609 WHERE oh.header_id = x_hold_rec.header_id
1610 and OH.LINE_ID is not null
1611 and OH.HOLD_RELEASE_ID IS NULL
1612 and ROWNUM = 1
1613 AND EXISTS
1614 (SELECT 1
1615 FROM oe_hold_sources_all hs,
1616 oe_hold_definitions h
1617 WHERE oh.hold_source_id = hs.hold_source_id
1618 AND hs.hold_id = h.hold_id
1619 AND NVL(h.item_type,
1620 DECODE(x_hold_rec.p_chk_act_hold_only,
1621 'Y', 'XXXXX',
1622 NVL(x_hold_rec.wf_item_type, 'NO ITEM')) ) =
1623 NVL(x_hold_rec.wf_item_type, 'NO ITEM')
1624 AND NVL(H.ACTIVITY_NAME,
1625 DECODE(x_hold_rec.p_chk_act_hold_only,
1626 'Y', 'XXXXX',
1627 NVL(x_hold_rec.wf_activity_name, 'NO ACT')) ) =
1628 NVL(x_hold_rec.wf_activity_name,'NO ACT')
1629 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1630 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1631 AND HS.HOLD_ID = NVL(x_hold_rec.hold_id,HS.HOLD_ID)
1632 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1633 ROUND( SYSDATE )
1634 AND hs.hold_entity_code = NVL(x_hold_rec.hold_entity_code, hs.hold_entity_code)
1635 AND hs.hold_entity_id = NVL(x_hold_rec.hold_entity_id, hs.hold_entity_id)
1636 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1637 NVL(x_hold_rec.hold_entity_code2, NVL(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1638 AND NVL(hs.hold_entity_id2, -99) =
1639 NVL(x_hold_rec.hold_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1640 EXCEPTION
1641 WHEN NO_DATA_FOUND THEN
1642 x_hold_rec.x_result_out := FND_API.G_FALSE;
1643 IF l_debug_level > 0 THEN
1644 oe_debug_pub.add( 'NO HOLDS FOUND FOR ANY OF THE LINES' ) ;
1645 END IF;
1646 WHEN TOO_MANY_ROWS THEN
1647 null;
1648 END;
1649
1650
1651 EXCEPTION
1652 WHEN FND_API.G_EXC_ERROR THEN
1653 x_return_status := FND_API.G_RET_STS_ERROR;
1654 x_hold_rec.x_result_out := FND_API.G_FALSE;
1655 FND_MSG_PUB.Count_And_Get
1656 ( p_count => x_msg_count
1657 , p_data => x_msg_data
1658 );
1659 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1661 x_hold_rec.x_result_out := FND_API.G_FALSE;
1662 FND_MSG_PUB.Count_And_Get
1663 ( p_count => x_msg_count
1664 , p_data => x_msg_data
1665 );
1666 WHEN OTHERS THEN
1667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1668 x_hold_rec.x_result_out := FND_API.G_FALSE;
1669 IF FND_MSG_PUB.Check_Msg_Level
1670 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1671 THEN
1672 FND_MSG_PUB.Add_Exc_Msg
1673 ( G_PKG_NAME
1674 , l_api_name
1675 );
1676 END IF;
1677 FND_MSG_PUB.Count_And_Get
1678 ( p_count => x_msg_count
1679 , p_data => x_msg_data
1680 );
1681
1682 END Check_Any_Line_Hold;
1683
1684 /**************************************************************/
1685 -----------------------------------------------------------------------
1686 -- Check_Holds
1687 --
1688 -- Checks if there are any holds on the order or order line. If
1689 -- order line, then checks for holds on the order that it belongs to.
1690 -- If ATO line, then checks for holds on other lines belonging to the
1691 -- same ATO model. If SMC line, then checks for other lines in the SMC.
1692 -- If included item line then checks for hold on its immediate parent
1693 -- if included item flag is set appropriately in the hold definition.
1694 ------------------------------------------------------------------------
1695 PROCEDURE Check_Holds
1696 ( p_api_version IN NUMBER
1697 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1698 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1699 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1700 , p_header_id IN NUMBER DEFAULT NULL
1701 , p_line_id IN NUMBER DEFAULT NULL
1702 , p_hold_id IN NUMBER DEFAULT NULL
1703 , p_wf_item IN VARCHAR2 DEFAULT NULL
1704 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1705 , p_entity_code IN VARCHAR2 DEFAULT NULL
1706 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1707 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1708 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1709 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1710 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1711 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1712 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1713 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1714 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1715 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1716 )
1717 IS
1718 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_HOLDS';
1719 l_api_version CONSTANT NUMBER := 1.0;
1720 l_dummy VARCHAR2(30);
1721
1722 l_line_id NUMBER;
1723 l_ato_line_id NUMBER;
1724 l_top_model_line_id NUMBER;
1725 l_smc_flag VARCHAR2(1);
1726 l_item_type_code VARCHAR2(30);
1727 l_link_to_line_id NUMBER;
1728
1729 l_return_status VARCHAR2(30);
1730 l_msg_count NUMBER;
1731 l_msg_data VARCHAR2(2000);
1732 p_hdr_id NUMBER;
1733
1734 CURSOR ato_model_lines IS
1735 select line_id
1736 from oe_order_lines_all
1737 where ato_line_id = l_ato_line_id
1738 and top_model_line_id = l_top_model_line_id;
1739
1740 CURSOR smc_lines IS
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
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;
1757
1758 -- Initialize result to TRUE i.e. holds are found
1759 x_result_out := FND_API.G_TRUE;
1760
1761 -- Check for Missing Input Parameters
1762 IF p_header_id IS NULL AND p_line_id IS NULL THEN
1763
1764 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
1765 OE_MSG_PUB.ADD;
1766 RAISE FND_API.G_EXC_ERROR;
1767
1768 END IF;
1769
1770 /*
1771 ** Fix Bug # 1920064, 2984023
1772 ** The following 'if' condition was added to select the header_id
1773 ** into a local variable 'p_hdr_id'. This variable is passed as a
1774 ** parameter to the procedure CHECK_HOLDS_LINE. This ensures that
1775 ** header_id is always passed as a not null parameter.
1776 */
1777
1778 IF p_header_id IS NULL THEN
1779 Begin
1780 SELECT header_id
1781 INTO p_hdr_id
1782 FROM oe_order_lines_all
1783 WHERE line_id = p_line_id;
1784 EXCEPTION
1785 WHEN NO_DATA_FOUND THEN
1786 x_result_out := FND_API.G_FALSE;
1787 IF l_debug_level > 0 THEN
1788 oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 1: ' || P_LINE_ID ) ;
1789 END IF;
1790 End;
1791 ELSE
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;
1801 IF l_org_id IS NULL THEN
1802 BEGIN
1803 SELECT org_id
1804 INTO l_org_id
1805 FROM oe_order_headers_all
1806 WHERE header_id=p_hdr_id;
1807 EXCEPTION
1808 WHEN NO_DATA_FOUND THEN
1809 x_result_out := FND_API.G_FALSE;
1810 END;
1811 END IF;
1812
1813 l_cascade_hold_non_smc := NVL(OE_SYS_PARAMETERS.VALUE('ONT_CASCADE_HOLD_NONSMC_PTO',l_org_id),'N');
1814 --7832836 end
1815
1816 /*
1817 ** Checking for HEADER level generic holds and activity specific holds
1818 */
1819 BEGIN
1820
1821 SELECT 'ANY_HEADER_HOLD'
1822 INTO l_dummy
1823 FROM oe_order_holds_all oh
1824 WHERE oh.header_id = p_hdr_id
1825 AND oh.line_id IS NULL
1826 AND oh.hold_release_id IS NULL
1827 AND EXISTS
1828 (SELECT 1
1829 FROM oe_hold_sources_all hs,
1830 oe_hold_definitions h
1831 WHERE oh.hold_source_id = hs.hold_source_id
1832 AND hs.hold_id = h.hold_id
1833 AND NVL(h.item_type,
1834 DECODE(p_chk_act_hold_only,
1835 'Y', 'XXXXX',
1836 NVL(p_wf_item, 'NO ITEM')) ) =
1837 NVL(p_wf_item, 'NO ITEM')
1838 AND NVL(h.activity_name,
1839 DECODE(p_chk_act_hold_only,
1840 'Y', 'XXXXX',
1841 NVL(p_wf_activity, 'NO ACT')) ) =
1842 NVL(p_wf_activity, 'NO ACT')
1843 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1844 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1845 AND hs.hold_id = NVL(p_hold_id, hs.hold_id)
1846 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1847 ROUND( SYSDATE )
1848 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1849 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1850 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1851 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1852 AND NVL(hs.hold_entity_id2, -99) =
1853 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1854
1855 EXCEPTION
1856 WHEN NO_DATA_FOUND THEN
1857 x_result_out := FND_API.G_FALSE;
1858 IF l_debug_level > 0 THEN
1859 oe_debug_pub.add( 'NO HOLDS FOUND FOR HEADER ID: ' || P_HDR_ID ) ;
1860 END IF;
1861 WHEN TOO_MANY_ROWS THEN
1862 null;
1863 END;
1864
1865 -- Return TRUE if Header Level Hold exists
1866 IF x_result_out = FND_API.G_TRUE THEN
1867 IF l_debug_level > 0 THEN
1868 oe_debug_pub.add( 'HEADER LEVEL HOLD EXISTS' ) ;
1869 END IF;
1870 RETURN;
1871 END IF;
1872
1873 IF p_line_id IS NOT NULL THEN
1874
1875 /* Check if the Line is on Hold */
1876 Check_Holds_line (
1877 p_hdr_id => p_hdr_id
1878 ,p_line_id => p_line_id
1879 ,p_hold_id => p_hold_id
1880 ,p_wf_item => p_wf_item
1881 ,p_wf_activity => p_wf_activity
1882 ,p_entity_code => p_entity_code
1883 ,p_entity_id => p_entity_id
1884 ,p_entity_code2 => p_entity_code2
1885 ,p_entity_id2 => p_entity_id2
1886 ,p_chk_act_hold_only => p_chk_act_hold_only
1887 ,x_result_out => x_result_out
1888 ,x_return_status => l_return_status
1889 ,x_msg_count => l_msg_count
1890 ,x_msg_data => l_msg_data
1891 );
1892
1893 -- Raise if the l_return_status is unexpected error
1894 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1895 IF l_debug_level > 0 THEN
1896 oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
1897 END IF;
1898 RAISE FND_API.G_EXC_ERROR;
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
1907 /* Proceed only if there is no hold on the line */
1908 IF x_result_out = FND_API.G_FALSE THEN
1909
1910 /* Check to see if the line is a part of ATO model, SMC, etc. */
1911 BEGIN
1912 IF l_debug_level > 0 THEN
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
1918 INTO l_ato_line_id, l_top_model_line_id,
1919 l_smc_flag, l_item_type_code, l_link_to_line_id
1920 FROM oe_order_lines_all
1921 WHERE line_id = p_line_id;
1922 EXCEPTION
1923 WHEN NO_DATA_FOUND THEN
1924 x_result_out := FND_API.G_FALSE;
1925 IF l_debug_level > 0 THEN
1926 oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 2: ' || P_LINE_ID ) ;
1927 END IF;
1928 END;
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
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
1952 ** Following replaced by a single call to Check_Holds_ATO
1953
1954 x_result_out := FND_API.G_FALSE;
1955
1956 OPEN ato_model_lines;
1957 loop
1958 FETCH ato_model_lines into l_line_id;
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
1967 ,p_line_id => l_line_id
1968 ,p_hold_id => p_hold_id
1969 ,p_wf_item => p_wf_item
1970 ,p_wf_activity => p_wf_activity
1971 ,p_entity_code => p_entity_code
1972 ,p_entity_id => p_entity_id
1973 ,p_entity_code2 => p_entity_code2
1974 ,p_entity_id2 => p_entity_id2
1975 ,p_chk_act_hold_only => p_chk_act_hold_only
1976 ,x_result_out => x_result_out
1977 ,x_return_status => l_return_status
1978 ,x_msg_count => l_msg_count
1979 ,x_msg_data => l_msg_data
1980 );
1981 end loop;
1982 */
1983 Check_Holds_ATO (
1984 p_hdr_id => p_hdr_id
1985 ,p_ato_line_id => l_ato_line_id
1986 ,p_top_model_line_id => l_top_model_line_id
1987 ,p_hold_id => p_hold_id
1988 ,p_wf_item => p_wf_item
1989 ,p_wf_activity => p_wf_activity
1990 ,p_entity_code => p_entity_code
1991 ,p_entity_id => p_entity_id
1992 ,p_entity_code2 => p_entity_code2
1993 ,p_entity_id2 => p_entity_id2
1994 ,p_chk_act_hold_only => p_chk_act_hold_only
1995 ,x_result_out => x_result_out
1996 ,x_return_status => l_return_status
1997 ,x_msg_count => l_msg_count
1998 ,x_msg_data => l_msg_data
1999 );
2000 -- Raise if the l_return_status is unexpected error
2001 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2002 IF l_debug_level > 0 THEN
2003 oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_ERROR') ;
2004 END IF;
2005 RAISE FND_API.G_EXC_ERROR;
2006 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2007 IF l_debug_level > 0 THEN
2008 oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_UNEXP_ERROR') ;
2009 END IF;
2010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2011 END IF;
2012
2013 END IF; /* l_ato_line_id is NOT NULL */
2014
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
2023 ** Following replaced by a single call to Check_Holds_SMC
2024
2025 OPEN smc_lines;
2026 loop
2027
2028 FETCH smc_lines into l_line_id;
2029 exit when smc_lines%NOTFOUND OR (x_result_out = FND_API.G_TRUE);
2030
2031 IF l_debug_level > 0 THEN
2032 oe_debug_pub.add( 'CHECK_HOLDS: CHECKING SMC LINEID' || L_LINE_ID ) ;
2033 END IF;
2034
2035 Check_Holds_line (
2036 p_hdr_id => p_hdr_id
2037 ,p_line_id => l_line_id
2038 ,p_hold_id => p_hold_id
2039 ,p_wf_item => p_wf_item
2040 ,p_wf_activity => p_wf_activity
2041 ,p_entity_code => p_entity_code
2042 ,p_entity_id => p_entity_id
2043 ,p_entity_code2 => p_entity_code2
2044 ,p_entity_id2 => p_entity_id2
2045 ,p_chk_act_hold_only => p_chk_act_hold_only
2046 ,x_result_out => x_result_out
2047 ,x_return_status => l_return_status
2048 ,x_msg_count => l_msg_count
2049 ,x_msg_data => l_msg_data
2050 );
2051 end loop;
2052 */
2053
2054 Check_Holds_SMC (
2055 p_hdr_id => p_hdr_id
2056 ,p_top_model_line_id => l_top_model_line_id
2057 ,p_hold_id => p_hold_id
2058 ,p_wf_item => p_wf_item
2059 ,p_wf_activity => p_wf_activity
2060 ,p_entity_code => p_entity_code
2061 ,p_entity_id => p_entity_id
2062 ,p_entity_code2 => p_entity_code2
2063 ,p_entity_id2 => p_entity_id2
2064 ,p_chk_act_hold_only => p_chk_act_hold_only
2065 ,x_result_out => x_result_out
2066 ,x_return_status => l_return_status
2067 ,x_msg_count => l_msg_count
2068 ,x_msg_data => l_msg_data
2069 );
2070 -- Raise if the l_return_status is unexpected error
2071 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2072 IF l_debug_level > 0 THEN
2073 oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_ERROR') ;
2074 END IF;
2075 RAISE FND_API.G_EXC_ERROR;
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
2092 ,p_top_model_line_id => l_top_model_line_id
2093 ,p_hold_id => 3
2094 ,p_wf_item => p_wf_item
2095 ,p_wf_activity => p_wf_activity
2096 ,p_entity_code => p_entity_code
2097 ,p_entity_id => p_entity_id
2098 ,p_entity_code2 => p_entity_code2
2099 ,p_entity_id2 => p_entity_id2
2100 ,p_chk_act_hold_only => p_chk_act_hold_only
2101 ,x_result_out => x_result_out
2102 ,x_return_status => l_return_status
2103 ,x_msg_count => l_msg_count
2104 ,x_msg_data => l_msg_data
2105 );
2106 --ER#7479609 start
2107 ELSE
2108 Check_Holds_SMC (
2109 p_hdr_id => p_hdr_id
2110 ,p_top_model_line_id => l_top_model_line_id
2111 ,p_hold_id => p_hold_id
2112 ,p_wf_item => p_wf_item
2113 ,p_wf_activity => p_wf_activity
2114 ,p_entity_code => p_entity_code
2115 ,p_entity_id => p_entity_id
2116 ,p_entity_code2 => p_entity_code2
2117 ,p_entity_id2 => p_entity_id2
2118 ,p_chk_act_hold_only => p_chk_act_hold_only
2119 ,x_result_out => x_result_out
2120 ,x_return_status => l_return_status
2121 ,x_msg_count => l_msg_count
2122 ,x_msg_data => l_msg_data
2123 );
2124 END IF;
2125 --ER#7479609 end
2126
2127 END IF;--NON SMC Config Validation Hold
2128 --5737464
2129
2130 IF l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND x_result_out = FND_API.G_FALSE THEN
2131
2132 IF l_debug_level > 0 THEN
2133 oe_debug_pub.add( 'CHECK_HOLDS: CHECKING HOLD ON LINK TO LINE ID: ' || L_LINK_TO_LINE_ID ) ;
2134 END IF;
2135
2136 Check_Holds_line (
2137 p_hdr_id => p_hdr_id
2138 ,p_line_id => l_link_to_line_id
2139 ,p_hold_id => p_hold_id
2140 ,p_wf_item => p_wf_item
2141 ,p_wf_activity => p_wf_activity
2142 ,p_entity_code => p_entity_code
2143 ,p_entity_id => p_entity_id
2144 ,p_entity_code2 => p_entity_code2
2145 ,p_entity_id2 => p_entity_id2
2146 ,p_chk_act_hold_only => p_chk_act_hold_only
2147 ,p_ii_parent_flag => 'Y'
2148 ,x_result_out => x_result_out
2149 ,x_return_status => l_return_status
2150 ,x_msg_count => l_msg_count
2151 ,x_msg_data => l_msg_data
2152 );
2153 -- Raise if the l_return_status is unexpected error
2154 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2155 IF l_debug_level > 0 THEN
2156 oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
2157 END IF;
2158 RAISE FND_API.G_EXC_ERROR;
2159 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2160 IF l_debug_level > 0 THEN
2161 oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
2162 END IF;
2163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2164 END IF;
2165
2166 END IF; /* l_item_type_code = 'INCLUDED' */
2167
2168 END IF; /* Proceed only if there is no hold on the line */
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
2178 x_return_status := FND_API.G_RET_STS_ERROR;
2179 x_result_out := FND_API.G_FALSE;
2180 FND_MSG_PUB.Count_And_Get
2181 ( p_count => x_msg_count
2182 , p_data => x_msg_data
2183 );
2184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2186 x_result_out := FND_API.G_FALSE;
2187 FND_MSG_PUB.Count_And_Get
2188 ( p_count => x_msg_count
2189 , p_data => x_msg_data
2190 );
2191 WHEN OTHERS THEN
2192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2193 x_result_out := FND_API.G_FALSE;
2194 IF FND_MSG_PUB.Check_Msg_Level
2195 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2196 THEN
2197 FND_MSG_PUB.Add_Exc_Msg
2198 ( G_PKG_NAME
2199 , l_api_name
2200 );
2201 END IF;
2202 FND_MSG_PUB.Count_And_Get
2203 ( p_count => x_msg_count
2204 , p_data => x_msg_data
2205 );
2206
2207 END Check_Holds;
2208
2209
2210 /******************************************************************
2211 * CHECK HOLD_SOURCES *
2212 * Checks if there are any holds for a Hold entity combination. *
2213 * Expects at least the hold_entity_code or hold_entity_id *
2214 ******************************************************************/
2215 PROCEDURE Check_Hold_Sources
2216 ( p_api_version IN NUMBER
2217 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2218 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2219 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2220 , p_hold_id IN NUMBER DEFAULT NULL
2221 , p_wf_item IN VARCHAR2 DEFAULT NULL
2222 , p_wf_activity IN VARCHAR2 DEFAULT NULL
2223 , p_hold_entity_code IN VARCHAR2 DEFAULT NULL
2224 --ER#7479609 , p_hold_entity_id IN NUMBER DEFAULT NULL
2225 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
2226 , p_hold_entity_code2 IN VARCHAR2 DEFAULT NULL
2227 --ER#7479609 , p_hold_entity_id2 IN NUMBER DEFAULT NULL
2228 , p_hold_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
2229 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
2230 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2231 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2232 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2233 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2234 )
2235 IS
2236 l_api_name CONSTANT VARCHAR2(30) := 'Check_Hold_Sources';
2237 l_api_version CONSTANT NUMBER := 1.0;
2238 l_dummy VARCHAR2(30);
2239
2240 l_return_status VARCHAR2(30);
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' ) ;
2250 END IF;
2251 -- Initialize API return status to success
2252 x_return_status := FND_API.G_RET_STS_SUCCESS;
2253
2254 -- Initialize result to TRUE i.e. holds are found
2255 x_result_out := FND_API.G_TRUE;
2256
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;
2266 RAISE FND_API.G_EXC_ERROR;
2267
2268 END IF;
2269
2270 /******************************/
2271 /* CHECKING FOR HOLDS SOURCES */
2272 /******************************************************************
2273 ** Checking for any generic holds and activity_specific holds **
2274 ** Sources **
2275 ******************************************************************/
2276 BEGIN
2277
2278 SELECT 'ANY_HOLD_SOURCE'
2279 INTO l_dummy
2280 FROM oe_hold_sources_all hs,
2281 oe_hold_definitions h
2282 WHERE hs.hold_id = h.hold_id
2283 AND NVL(h.item_type,
2284 DECODE(p_chk_act_hold_only,
2285 'Y', 'XXXXX',
2286 NVL(p_wf_item, 'NO ITEM')) ) =
2287 NVL(p_wf_item, 'NO ITEM')
2288 AND NVL(h.activity_name,
2289 DECODE(p_chk_act_hold_only,
2290 'Y', 'XXXXX',
2291 NVL(p_wf_activity, 'NO ACT')) ) =
2292 NVL(p_wf_activity, 'NO ACT')
2293 AND hs.hold_id = NVL(p_hold_id, hs.hold_id)
2294 AND hs.RELEASED_FLAG = 'N'
2295 AND hs.hold_entity_code = NVL(p_hold_entity_code, hs.hold_entity_code)
2296 AND hs.hold_entity_id = NVL(p_hold_entity_id, hs.hold_entity_id)
2297 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
2298 NVL(p_hold_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
2299 AND NVL(hs.hold_entity_id2, -99) =
2300 nvl(p_hold_entity_id2, NVL(hs.hold_entity_id2, -99 ) );
2301
2302 EXCEPTION
2303 WHEN NO_DATA_FOUND THEN
2304 x_result_out := FND_API.G_FALSE;
2305 WHEN TOO_MANY_ROWS THEN
2306 null;
2307 END;
2308
2309
2310 EXCEPTION
2311 WHEN FND_API.G_EXC_ERROR THEN
2312 x_return_status := FND_API.G_RET_STS_ERROR;
2313 x_result_out := FND_API.G_FALSE;
2314 FND_MSG_PUB.Count_And_Get
2315 ( p_count => x_msg_count
2316 , p_data => x_msg_data
2317 );
2318 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2320 x_result_out := FND_API.G_FALSE;
2321 FND_MSG_PUB.Count_And_Get
2322 ( p_count => x_msg_count
2323 , p_data => x_msg_data
2324 );
2325 WHEN OTHERS THEN
2326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2327 x_result_out := FND_API.G_FALSE;
2328 IF FND_MSG_PUB.Check_Msg_Level
2329 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2330 THEN
2331 FND_MSG_PUB.Add_Exc_Msg
2332 ( G_PKG_NAME
2333 , l_api_name
2334 );
2335 END IF;
2336 FND_MSG_PUB.Count_And_Get
2337 ( p_count => x_msg_count
2338 , p_data => x_msg_data
2339 );
2340
2341 END Check_Hold_Sources;
2342
2343
2344
2345
2346 --------------------------------------------------------------------------
2347 -- RELEASE HOLDS
2348 -- Take Release Action on a Hold.
2349 -- Note: Leaving this call for backward compatibility. AR's Customer form
2350 -- still calls the old oe_holds (OEXOHAPB.pls) which in turns calls
2351 -- this api.
2352 -- ALL NEW Callers should call the new api structure
2353 --------------------------------------------------------------------------
2354 PROCEDURE Release_Holds
2355 ( p_api_version IN NUMBER DEFAULT 1.0
2356 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2357 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2358 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2359 , p_header_id IN NUMBER DEFAULT NULL
2360 , p_line_id IN NUMBER DEFAULT NULL
2361 , p_hold_id IN NUMBER DEFAULT NULL
2362 , p_entity_code IN VARCHAR2 DEFAULT NULL
2363 , p_entity_id IN NUMBER DEFAULT NULL
2364 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
2365 , p_entity_id2 IN NUMBER DEFAULT NULL
2366 , p_hold_release_rec IN OE_Hold_Sources_Pvt.Hold_Release_REC
2367 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2368 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2369 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2370 )
2371 IS
2372 l_api_name CONSTANT VARCHAR2(30) := 'RELEASE_HOLDS';
2373 l_api_version CONSTANT NUMBER := 1.0;
2374 l_user_id NUMBER;
2375 l_dummy VARCHAR2(30);
2376 l_hold_release_id NUMBER;
2377 l_hold_source_id NUMBER := 0;
2378 l_order_hold_id NUMBER := 0;
2379 --ER#7479609 l_entity_code VARCHAR2(1);
2380 l_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
2381
2382 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
2383 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
2384 l_org_id number;
2385
2386 -- Define Cursors
2387 CURSOR hold_source IS
2388 SELECT HS.HOLD_SOURCE_ID,OH.ORDER_HOLD_ID
2389 FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2390 WHERE HS.HOLD_ID = p_hold_id
2391 AND HS.RELEASED_FLAG = 'N'
2392 AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2393 AND HS.HOLD_ENTITY_CODE = p_entity_code
2394 AND HS.HOLD_ENTITY_ID = p_entity_id
2395 AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
2396 nvl(p_entity_code2, 'NO_ENTITY_CODE2')
2397 AND nvl(HS.HOLD_ENTITY_ID2, -99) =
2398 nvl(p_entity_id2, -99)
2399 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2400 AND NVL(OH.HEADER_ID, 0) = NVL(NVL(p_header_id, OH.HEADER_ID), 0)
2401 AND NVL(OH.LINE_ID, 0) = NVL(NVL(p_line_id, OH.LINE_ID), 0)
2402 AND OH.HOLD_RELEASE_ID IS NULL;
2403 CURSOR order_hold IS
2404 SELECT OH.ORDER_HOLD_ID
2405 FROM OE_ORDER_HOLDS OH
2406 WHERE OH.HOLD_SOURCE_ID = l_hold_source_id
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' ) ;
2416 END IF;
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;
2426 END IF;
2427
2428 -- Standard Start of API savepoint
2429 SAVEPOINT Release_holds_PUB;
2430
2431 -- Initialize API return status to success
2432 x_return_status := FND_API.G_RET_STS_SUCCESS;
2433
2434
2435 Utilities(l_user_id);
2436
2437 -------------------------------------------------------------------
2438 -- Hold Source ID is KNOWN. Release the hold for this order or
2439 -- line that uses this hold source.
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
2449 -- Retrieving the entity code for this hold source
2450 -- Checking if its a valid hold source id
2451 SELECT hold_entity_code
2452 INTO l_entity_code
2453 FROM OE_HOLD_SOURCES
2454 WHERE hold_source_id = l_hold_source_id;
2455
2456 EXCEPTION
2457 WHEN NO_DATA_FOUND THEN
2458 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_SOURCE');
2459 OE_MSG_PUB.ADD;
2460 RAISE FND_API.G_EXC_ERROR;
2461 WHEN OTHERS THEN
2462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2463 END;
2464
2465 -- Retrieving order hold information
2466 OPEN order_hold;
2467 FETCH order_hold INTO l_order_hold_id;
2468 IF order_hold%notfound THEN
2469 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD');
2470 OE_MSG_PUB.ADD;
2471 RAISE FND_API.G_EXC_ERROR;
2472 END IF;
2473 CLOSE order_hold;
2474
2475 -------------------------------------------------------------------
2476 -- Hold Source ID is NOT KNOWN. Query up the hold source
2477 -- and then release the order hold using this hold source.
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');
2487 OE_MSG_PUB.ADD;
2488 RAISE FND_API.G_EXC_ERROR;
2489 END IF;
2490
2491 IF p_entity_code IS NULL THEN
2492 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
2493 OE_MSG_PUB.ADD;
2494 RAISE FND_API.G_EXC_ERROR;
2495 ELSE
2496 l_entity_code := p_entity_code;
2497 END IF;
2498
2499 IF p_entity_id IS NULL THEN
2500 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
2501 OE_MSG_PUB.ADD;
2502 RAISE FND_API.G_EXC_ERROR;
2503 END IF;
2504
2505 /*
2506 IF p_line_id IS NULL AND p_header_id IS NULL THEN
2507 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HEADER_AND_LINE_ID');
2508 OE_MSG_PUB.ADD;
2509 RAISE FND_API.G_EXC_ERROR;
2510 END IF;
2511 */
2512
2513 -- Retrieving hold source and order hold information
2514 /*
2515 OPEN hold_source;
2516 FETCH hold_source INTO l_hold_source_id, l_order_hold_id;
2517 IF hold_source%notfound THEN
2518 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD');
2519 OE_MSG_PUB.ADD;
2520 RAISE FND_API.G_EXC_ERROR;
2521 END IF;
2522 CLOSE hold_source;
2523 */
2524
2525 END IF; -- End of check to see if hold source ID is passed
2526
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;
2536 END IF;
2537
2538 l_hold_source_rec.hold_entity_id := p_entity_id;
2539 l_hold_release_rec.RELEASE_REASON_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
2549 ,x_return_status => x_return_status
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
2559 RAISE FND_API.G_EXC_ERROR;
2560 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562 END IF;
2563 END IF;
2564
2565 EXCEPTION
2566 WHEN FND_API.G_EXC_ERROR THEN
2567 ROLLBACK TO RELEASE_HOLDS_PUB;
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 FND_MSG_PUB.Count_And_Get
2570 ( p_count => x_msg_count
2571 , p_data => x_msg_data
2572 );
2573 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2574 ROLLBACK TO RELEASE_HOLDS_PUB;
2575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2576 FND_MSG_PUB.Count_And_Get
2577 ( p_count => x_msg_count
2578 , p_data => x_msg_data
2579 );
2580 WHEN OTHERS THEN
2581 ROLLBACK TO RELEASE_HOLDS_PUB;
2582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2583 IF FND_MSG_PUB.Check_Msg_Level
2584 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2585 THEN
2586 FND_MSG_PUB.Add_Exc_Msg
2587 ( G_PKG_NAME
2588 , l_api_name
2589 );
2590 END IF;
2591 FND_MSG_PUB.Count_And_Get
2592 ( p_count => x_msg_count
2593 , p_data => x_msg_data
2594 );
2595
2596 END Release_Holds;
2597
2598
2599 ----------------------------------------------------------------------------
2600 -- Delete Holds
2601 -- Deletes from OE_ORDER_HOLDS all hold records for an order (p_header_id)
2602 -- or for a line (p_line_id).
2603 -- Also, if there are ORDER hold sources (hold_entity_code = 'O') for this
2604 -- order, deletes hold source records from OE_HOLD_SOURCES.
2605 -- If the hold or hold source was released and the same release record is
2606 -- not used by an existing hold or hold source, then deletes the
2607 -- release record also from OE_HOLD_RELEASES;
2608 ----------------------------------------------------------------------------
2609 PROCEDURE Delete_Holds
2610 ( p_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2611 ,p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2612 )
2613 IS
2614 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HOLDS';
2615 l_api_version CONSTANT NUMBER := 1.0;
2616 l_order_hold_id NUMBER;
2617 l_hold_source_id NUMBER;
2618 l_hold_release_id NUMBER := 0;
2619 x_RETURN_STATUS varchar2(56);
2620 x_MSG_COUNT number;
2621 x_msg_data varchar2(500);
2622 l_org_id number;
2623
2624 CURSOR order_hold IS
2625 SELECT order_hold_id, NVL(hold_release_id,0)
2626 FROM OE_ORDER_HOLDS
2627 WHERE HEADER_ID = p_header_id;
2628 CURSOR hold_source IS
2629 SELECT hold_source_id, NVL(hold_release_id,0)
2630 FROM OE_HOLD_SOURCES
2631 WHERE HOLD_ENTITY_CODE = 'O'
2632 AND HOLD_ENTITY_ID = p_header_id;
2633 CURSOR line_hold IS
2634 SELECT order_hold_id, NVL(hold_release_id,0)
2635 FROM OE_ORDER_HOLDS
2636 WHERE LINE_ID = p_line_id;
2637
2638 --ER#7479609 start
2639 CURSOR line_hold_opt_item(p_top_model_line_id NUMBER,p_inventory_item_id NUMBER) IS
2640 SELECT OH.order_hold_id, NVL(OH.hold_release_id,0)
2641 FROM OE_ORDER_HOLDS OH,OE_HOLD_SOURCES HS,OE_ORDER_LINES OL
2642 WHERE OH.LINE_ID = p_top_model_line_id
2643 AND OH.LINE_ID = OL.LINE_ID
2644 AND OH.hold_source_id=HS.hold_source_id
2645 AND HS.hold_entity_id=OL.inventory_item_id
2646 AND HS.hold_entity_id2=p_inventory_item_id;
2647
2648 l_top_model_line_id OE_ORDER_LINES_ALL.TOP_MODEL_LINE_ID%TYPE;
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
2658 -- org_id is null, raise an error.
2659 oe_debug_pub.add('Org_Id is NULL',1);
2660 x_return_status := FND_API.G_RET_STS_ERROR;
2661 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2662 FND_MSG_PUB.ADD;
2663 RAISE FND_API.G_EXC_ERROR;
2664 END IF;
2665
2666 --SAVEPOINT DELETE_HOLDS_PUB;
2667
2668 -- Initialize API return status to success
2669 x_return_status := FND_API.G_RET_STS_SUCCESS;
2670
2671 -- Missing Input arguments
2672
2673 IF (p_header_id = FND_API.G_MISS_NUM AND
2674 p_line_id = FND_API.G_MISS_NUM) THEN
2675
2676 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
2677 OE_MSG_PUB.ADD;
2678 RAISE FND_API.G_EXC_ERROR;
2679
2680 END IF;
2681
2682 -- Delete the hold records corr. to this order or line in OE_ORDER_HOLDS
2683 IF p_line_id = FND_API.G_MISS_NUM THEN
2684
2685 -- Delete order hold records
2686 OPEN order_hold;
2687 LOOP
2688 FETCH order_hold INTO l_order_hold_id, l_hold_release_id;
2689 IF (order_hold%notfound) THEN
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;
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;
2706
2707 /* DELETE FROM OE_HOLD_RELEASES
2708 WHERE HOLD_RELEASE_ID = l_hold_release_id
2709 AND HOLD_RELEASE_ID NOT IN (SELECT NVL(HOLD_RELEASE_ID,0)
2710 FROM OE_ORDER_HOLDS
2711 UNION
2712 SELECT NVL(HOLD_RELEASE_ID,0)
2713 FROM OE_HOLD_SOURCES
2714 ); */
2715 END LOOP;
2716
2717 CLOSE order_hold;
2718
2719 -- Delete hold source records
2720
2721 OPEN hold_source;
2722 LOOP
2723 FETCH hold_source INTO l_hold_source_id, l_hold_release_id;
2724 IF (hold_source%notfound) THEN
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
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
2740 END LOOP;
2741 CLOSE hold_source;
2742
2743 ELSE
2744 -- Delete line hold records
2745
2746 OPEN line_hold;
2747
2748 LOOP
2749 FETCH line_hold INTO l_order_hold_id, l_hold_release_id;
2750 IF (line_hold%notfound) THEN
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;
2760
2761 DELETE FROM OE_HOLD_RELEASES
2762 WHERE HOLD_RELEASE_ID = l_hold_release_id
2763 AND ORDER_HOLD_ID = l_order_hold_id;
2764
2765 /* AND HOLD_RELEASE_ID NOT IN
2766 (SELECT NVL(HOLD_RELEASE_ID,0)
2767 FROM OE_ORDER_HOLDS
2768 UNION
2769 SELECT NVL(HOLD_RELEASE_ID,0)
2770 FROM OE_HOLD_SOURCES
2771 ); */
2772 END LOOP;
2773
2774 CLOSE line_hold;
2775
2776 --ER#7479609 start
2777 BEGIN
2778 select top_model_line_id,inventory_item_id
2779 into l_top_model_line_id,l_inventory_item_id
2780 from oe_order_lines
2781 where line_id=p_line_id
2782 and item_type_code in ('OPTION','CLASS','INCLUDED');
2783
2784 OPEN line_hold_opt_item(l_top_model_line_id,l_inventory_item_id);
2785
2786 LOOP
2787 FETCH line_hold_opt_item INTO l_order_hold_id, l_hold_release_id;
2788 IF (line_hold_opt_item%notfound) THEN
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;
2798
2799 DELETE FROM OE_HOLD_RELEASES
2800 WHERE HOLD_RELEASE_ID = l_hold_release_id
2801 AND ORDER_HOLD_ID = l_order_hold_id;
2802 END LOOP;
2803
2804 CLOSE line_hold_opt_item;
2805
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 NULL;
2809 END;
2810 --ER#7479609 end
2811
2812 END IF;
2813
2814
2815 EXCEPTION
2816 WHEN FND_API.G_EXC_ERROR THEN
2817 IF (order_hold%isopen) THEN
2818 CLOSE order_hold;
2819 END IF;
2820 IF (hold_source%isopen) THEN
2821 CLOSE hold_source;
2822 END IF;
2823 IF (line_hold%isopen) THEN
2824 CLOSE line_hold;
2825 END IF;
2826 --ROLLBACK TO DELETE_HOLDS_PUB;
2827 x_return_status := FND_API.G_RET_STS_ERROR;
2828 FND_MSG_PUB.Count_And_Get
2829 ( p_count => x_msg_count
2830 , p_data => x_msg_data
2831 );
2832 WHEN OTHERS THEN
2833 IF (order_hold%isopen) THEN
2834 CLOSE order_hold;
2835 END IF;
2836 IF (hold_source%isopen) THEN
2837 CLOSE hold_source;
2838 END IF;
2839 IF (line_hold%isopen) THEN
2840 CLOSE line_hold;
2841 END IF;
2842 --ROLLBACK TO DELETE_HOLDS_PUB;
2843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2844 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2845 THEN
2846 FND_MSG_PUB.Add_Exc_Msg
2847 ( G_PKG_NAME
2848 , l_api_name
2849 );
2850 END IF;
2851 FND_MSG_PUB.Count_And_Get
2852 ( p_count => x_msg_count
2853 , p_data => x_msg_data
2854 );
2855 END Delete_Holds;
2856
2857
2858 -------------------------------------------------------------------
2859 -- Procedure: EVAL_HOLD_SOURCE
2860 -- Applies or removes holds if a hold source entity is updated
2861 -- on the order or line.
2862 -------------------------------------------------------------------
2863
2864 PROCEDURE evaluate_holds
2865 ( p_entity_code IN VARCHAR2
2866 , p_entity_id IN NUMBER
2867 , p_hold_entity_code IN VARCHAR2
2868 --ER#7479609 , p_hold_entity_id IN NUMBER
2869 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
2870 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2871 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2872 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2873 )
2874 IS
2875 l_hold_source_id NUMBER DEFAULT NULL;
2876 l_order_hold_id NUMBER DEFAULT NULL;
2877 l_return_status VARCHAR2(30);
2878 l_msg_count NUMBER;
2879 l_msg_data VARCHAR2(2000);
2880 --l_hold_release_rec OE_Hold_Sources_Pvt.Hold_Release_REC;
2881 temp NUMBER DEFAULT NULL;
2882 l_attribute VARCHAR2(30);
2883
2884 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
2885 --ER#7479609 l_hold_entity_code VARCHAR2(1);
2886 l_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
2887 --ER#7479609 l_hold_entity_id NUMBER;
2888 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
2889 --ER#7479609 l_hold_entity_code2 VARCHAR2(1);
2890 l_hold_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
2891 --ER#7479609 l_hold_entity_id2 NUMBER;
2892 l_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
2893 l_hold_id NUMBER;
2894
2895 l_create_order_hold_flag VARCHAR2(1) := 'Y';
2896 --l_line_rec OE_Order_PUB.Line_Rec_Type;
2897
2898 l_header_id NUMBER DEFAULT NULL;
2899 l_line_id NUMBER DEFAULT NULL;
2900 l_line_number NUMBER DEFAULT NULL;
2901
2902 l_sold_to_org_id NUMBER DEFAULT NULL;
2903 l_invoice_to_org_id NUMBER DEFAULT NULL;
2904 l_ship_to_org_id NUMBER DEFAULT NULL;
2905 l_ship_from_org_id NUMBER DEFAULT NULL;
2906 l_inventory_item_id NUMBER DEFAULT NULL;
2907 l_blanket_number NUMBER DEFAULT NULL;
2908 l_blanket_line_number NUMBER DEFAULT NULL;
2909
2910 --
2911 --ER#7479609 CURSOR prev_hold IS
2912 CURSOR prev_hold(l_all_del_pay VARCHAR2) IS --ER#7479609
2913 --ER#7479609 SELECT HS.hold_entity_id, OH.order_hold_id
2914 SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code --ER#7479609
2915 FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2916 WHERE OH.HEADER_ID = l_header_id
2917 AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
2918 NVL(l_line_id,FND_API.G_MISS_NUM)
2919 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2920 AND HS.HOLD_ENTITY_ID = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID) --ER#7479609
2921 AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
2922 AND HS.RELEASED_FLAG = 'N';
2923
2924 --ER#7479609 CURSOR prev_hold_entity2 IS
2925 CURSOR prev_hold_entity2(l_all_del_pay VARCHAR2) IS --ER#7479609
2926 --ER#7479609 SELECT HS.hold_entity_id, OH.order_hold_id
2927 SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code2 --ER#7479609
2928 FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2929 WHERE OH.HEADER_ID = l_header_id
2930 AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
2931 NVL(l_line_id,FND_API.G_MISS_NUM)
2932 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2933 AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code
2934 AND HS.HOLD_ENTITY_ID2 = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID2) --ER#7479609
2935 AND HS.HOLD_ENTITY_CODE <> 'O'
2936 AND HS.RELEASED_FLAG = 'N';
2937
2938 CURSOR curr_hold_source IS
2939 SELECT HS.hold_source_id, hs.hold_id,
2940 hs.hold_entity_code, hs.hold_entity_id,
2941 hs.hold_entity_code2,hs.hold_entity_id2
2942 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
2943 WHERE HS.HOLD_ENTITY_CODE = p_hold_entity_code
2944 AND HS.HOLD_ENTITY_ID = p_hold_entity_id
2945 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
2946 ROUND( SYSDATE )
2947 AND HS.RELEASED_FLAG = 'N'
2948 AND HLD.HOLD_ID = HS.HOLD_ID
2949 AND SYSDATE
2950 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
2951 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE );
2952
2953 CURSOR curr_hold_source_entity2 IS
2954 SELECT HS.hold_source_id, hs.hold_id,
2955 hs.hold_entity_code, hs.hold_entity_id,
2956 hs.hold_entity_code2,hs.hold_entity_id2
2957 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
2958 WHERE HS.HOLD_ENTITY_CODE2 = p_hold_entity_code
2959 AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id
2960 AND HS.HOLD_ENTITY_CODE <> 'O'
2961 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
2962 ROUND( SYSDATE )
2963 AND HS.RELEASED_FLAG = 'N'
2964 AND HLD.HOLD_ID = HS.HOLD_ID
2965 AND SYSDATE
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;
2975 TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
2976 entity_id oe_hold_sources_all.hold_entity_id%TYPE);
2977
2978 TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;
2979
2980 l_entity_tab entity_tab;
2981
2982 TYPE payment_type_tab IS TABLE OF OE_PAYMENTS.PAYMENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
2983 l_payment_type_tab payment_type_tab;
2984 pay_idx NUMBER :=0;
2985 l_all_del_pay varchar2(1) := 'Y';
2986 --ER#7479609 end
2987 BEGIN
2988 x_return_status := FND_API.G_RET_STS_SUCCESS;
2989
2990 IF l_debug_level > 0 THEN
2991 oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS' , 1 ) ;
2992 END IF;
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;
3000
3001
3002 --ER#7479609 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3003 IF (p_entity_code = OE_Globals.G_ENTITY_HEADER OR
3004 p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_PAYMENT) THEN --ER#7479609
3005 -- Indicates Header Level action
3006 l_header_id := p_entity_id;
3007 --ER#7479609 start
3008 BEGIN
3009 SELECT * INTO l_header_rec
3010 FROM oe_order_headers
3011 WHERE header_id=l_header_id;
3012
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
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
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
3035 FROM oe_order_headers OH,oe_order_lines OL
3036 WHERE OH.header_id=OL.header_id
3037 AND OL.line_id=l_line_id;
3038
3039 l_header_id := l_header_rec.header_id;
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
3049
3050 BEGIN
3051 /*ER#7479609 start
3052 SELECT sold_to_org_id,
3053 invoice_to_org_id,
3054 ship_to_org_id,
3055 ship_from_org_id,
3056 inventory_item_id,
3057 line_number,
3058 Blanket_number,
3059 Blanket_line_number,
3060 header_id
3061 INTO l_sold_to_org_id,
3062 l_invoice_to_org_id,
3063 l_ship_to_org_id,
3064 l_ship_from_org_id,
3065 l_inventory_item_id,
3066 l_line_number,
3067 l_blanket_number,
3068 l_blanket_line_number,
3069 l_header_id
3070 FROM oe_order_lines
3071 WHERE line_id = l_line_id;
3072 ER#7479609 end*/
3073
3074 --ER#7479609 start
3075 SELECT * INTO l_line_rec
3076 FROM oe_order_lines
3077 WHERE line_id = l_line_id;
3078 --ER#7479609 end
3079
3080 --OE_LINE_UTIL.Query_Row(p_line_id => l_line_id,
3081 -- x_line_rec => l_line_rec);
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;
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;
3098 END IF;
3099
3100 --ER#7479609 start
3101 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_PAYMENT THEN
3102 G_PAYMENT_HOLD_APPLIED := 'N';
3103 BEGIN
3104 l_payment_type_tab.delete;
3105
3106 Select payment_type_code payment_type
3107 BULK COLLECT INTO l_payment_type_tab
3108 FROM OE_PAYMENTS
3109 WHERE header_id=l_header_rec.header_id
3110 AND line_id IS NULL;
3111
3112 EXCEPTION
3113 WHEN OTHERS THEN
3114 null;
3115 END;
3116
3117 FOR i in 1 .. l_payment_type_tab.count LOOP
3118 evaluate_holds
3119 (p_entity_code => OE_Globals.G_ENTITY_HEADER
3120 , p_entity_id => l_header_rec.header_id
3121 , p_hold_entity_code => 'P'
3122 , p_hold_entity_id => l_payment_type_tab(i)
3123 , x_return_status =>l_return_status
3124 , x_msg_count => l_msg_count
3125 , x_msg_data => l_msg_data
3126 );
3127 END LOOP;
3128 l_payment_type_tab.delete;
3129 RETURN;
3130 END IF;
3131 --ER#7479609 end
3132
3133 --ER#7479609 start
3134 l_all_del_pay := 'Y';
3135 IF G_HDR_PAYMENT = 'Y' and p_hold_entity_code = 'P' THEN
3136 l_all_del_pay := 'N';
3137 END IF;
3138 --ER#7479609 end
3139
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
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
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
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;
3188 END LOOP;
3189 CLOSE prev_hold;
3190
3191 -- prev_hold_entity2
3192
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';
3202 IF G_HDR_PAYMENT = 'Y' and p_hold_entity_code = 'P' THEN
3203 l_all_del_pay := 'N';
3204 END IF;
3205 --ER#7479609 end
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
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
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
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;
3254 END LOOP;
3255 CLOSE prev_hold_entity2;
3256
3257 end if; -- p_hold_entity_code in ('C','S','B','W')
3258
3259 G_HDR_PAYMENT := 'N'; --ER#7479609
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;
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
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
3284 l_create_order_hold_flag := 'N';
3285 /*ER#7479609 start
3286 IF l_hold_entity_code2 = 'C' THEN
3287 IF l_sold_to_org_id = l_hold_entity_id2 THEN
3288 l_create_order_hold_flag := 'Y';
3289 END IF;
3290 ELSIF l_hold_entity_code2 = 'B' THEN
3291 IF l_invoice_to_org_id = l_hold_entity_id2 THEN
3292 l_create_order_hold_flag := 'Y';
3293 END IF;
3294 ELSIF l_hold_entity_code2 = 'S' THEN
3295 IF l_ship_to_org_id = l_hold_entity_id2 THEN
3296 l_create_order_hold_flag := 'Y';
3297 END IF;
3298 ELSIF l_hold_entity_code2 = 'W' THEN
3299 IF l_ship_from_org_id = l_hold_entity_id2 THEN
3300 l_create_order_hold_flag := 'Y';
3301 END IF;
3302 ELSIF l_hold_entity_code2 = 'H' THEN
3303 IF l_blanket_number = l_hold_entity_id2 THEN
3304 l_create_order_hold_flag := 'Y';
3305 END IF;
3306 ELSIF l_hold_entity_code2 = 'L' THEN
3307 IF l_blanket_line_number = l_hold_entity_id2 THEN
3308 l_create_order_hold_flag := 'Y';
3309 END IF;
3310 END IF;
3311 ER#7479609 end*/
3312
3313 --ER#7479609 start
3314 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3315
3316 BEGIN
3317 l_payment_type_tab.delete;
3318
3319 SELECT V.payment_type
3320 BULK COLLECT INTO l_payment_type_tab
3321 FROM
3322 (Select payment_type_code payment_type
3323 FROM OE_PAYMENTS
3324 WHERE header_id=l_header_rec.header_id
3325 AND line_id IS NULL
3326 UNION
3327 SELECT payment_type_code payment_type
3328 FROM OE_ORDER_HEADERS_ALL
3329 WHERE header_id=l_header_rec.header_id) V;
3330
3331 EXCEPTION
3332 WHEN OTHERS THEN
3333 null;
3334 END;
3335
3336 IF l_hold_entity_code2 = 'OT' THEN
3337 IF l_header_rec.order_type_id = l_hold_entity_id2 THEN
3338 l_create_order_hold_flag := 'Y';
3339 END IF;
3340 ELSIF l_hold_entity_code2 = 'PT' THEN
3341 IF l_header_rec.payment_type_code = l_hold_entity_id2 THEN
3342 l_create_order_hold_flag := 'Y';
3343 END IF;
3344 ELSIF l_hold_entity_code2 = 'TC' THEN
3345 IF l_header_rec.transactional_curr_code = l_hold_entity_id2 THEN
3346 l_create_order_hold_flag := 'Y';
3347 END IF;
3348 ELSIF l_hold_entity_code2 = 'SC' THEN
3349 IF l_header_rec.sales_channel_code = l_hold_entity_id2 THEN
3350 l_create_order_hold_flag := 'Y';
3351 END IF;
3352 ELSIF l_hold_entity_code2 = 'P' THEN
3353 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
3354 IF (l_payment_type_tab(pay_idx) = l_hold_entity_id2) THEN
3355 l_create_order_hold_flag := 'Y';
3356 EXIT;
3357 END IF;
3358 END LOOP;
3359 END IF;
3360
3361 --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3362 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3363 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3364
3365 BEGIN
3366 l_payment_type_tab.delete;
3367
3368 Select payment_type_code payment_type
3369 BULK COLLECT INTO l_payment_type_tab
3370 FROM OE_PAYMENTS
3371 WHERE header_id=l_header_rec.header_id
3372 AND line_id IS NOT NULL;
3373
3374 EXCEPTION
3375 WHEN OTHERS THEN
3376 null;
3377 END;
3378
3379 IF l_hold_entity_code2 = 'C' THEN
3380 IF l_line_rec.sold_to_org_id = l_hold_entity_id2 THEN
3381 l_create_order_hold_flag := 'Y';
3382 END IF;
3383 ELSIF l_hold_entity_code2 = 'B' THEN
3384 IF l_line_rec.invoice_to_org_id = l_hold_entity_id2 THEN
3385 l_create_order_hold_flag := 'Y';
3386 END IF;
3387 ELSIF l_hold_entity_code2 = 'S' THEN
3388 IF l_line_rec.ship_to_org_id = l_hold_entity_id2 THEN
3389 l_create_order_hold_flag := 'Y';
3390 END IF;
3391 ELSIF l_hold_entity_code2 = 'W' THEN
3392 IF l_line_rec.ship_from_org_id = l_hold_entity_id2 THEN
3393 l_create_order_hold_flag := 'Y';
3394 END IF;
3395 ELSIF l_hold_entity_code2 = 'O' THEN
3396 IF l_line_rec.header_id = l_hold_entity_id2 THEN
3397 l_create_order_hold_flag := 'Y';
3398 END IF;
3399 ELSIF l_hold_entity_code2 = 'H' THEN
3400 IF l_line_rec.blanket_number = l_hold_entity_id2 THEN
3401 l_create_order_hold_flag := 'Y';
3402 END IF;
3403 ELSIF l_hold_entity_code2 = 'L' THEN
3404 IF l_line_rec.blanket_line_number = l_hold_entity_id2 THEN
3405 l_create_order_hold_flag := 'Y';
3406 END IF;
3407
3408 ELSIF l_hold_entity_code2 = 'LT' THEN
3409 IF l_line_rec.line_type_id = l_hold_entity_id2 THEN
3410 l_create_order_hold_flag := 'Y';
3411 END IF;
3412 ELSIF l_hold_entity_code2 = 'SM' THEN
3413 IF l_line_rec.shipping_method_code = l_hold_entity_id2 THEN
3414 l_create_order_hold_flag := 'Y';
3415 END IF;
3416 ELSIF l_hold_entity_code2 = 'D' THEN
3417 IF l_line_rec.deliver_to_org_id = l_hold_entity_id2 THEN
3418 l_create_order_hold_flag := 'Y';
3419 END IF;
3420 ELSIF l_hold_entity_code2 = 'ST' THEN
3421 IF l_line_rec.source_type_code = l_hold_entity_id2 THEN
3422 l_create_order_hold_flag := 'Y';
3423 END IF;
3424 ELSIF l_hold_entity_code2 = 'PL' THEN
3425 IF l_line_rec.price_list_id = l_hold_entity_id2 THEN
3426 l_create_order_hold_flag := 'Y';
3427 END IF;
3428 ELSIF l_hold_entity_code2 = 'PR' THEN
3429 IF l_line_rec.project_id = l_hold_entity_id2 THEN
3430 l_create_order_hold_flag := 'Y';
3431 END IF;
3432 ELSIF l_hold_entity_code2 = 'PT' THEN
3433 IF l_line_rec.payment_term_id = l_hold_entity_id2 THEN
3434 l_create_order_hold_flag := 'Y';
3435 END IF;
3436
3437 ELSIF l_hold_entity_code2 = 'OI' THEN
3438 IF l_line_rec.inventory_item_id = l_hold_entity_id2 THEN
3439 l_create_order_hold_flag := 'Y';
3440 END IF;
3441 ELSIF l_hold_entity_code2 = 'T' THEN
3442 IF l_line_rec.task_id = l_hold_entity_id2 THEN
3443 l_create_order_hold_flag := 'Y';
3444 END IF;
3445 ELSIF l_hold_entity_code2 = 'CB' THEN
3446 IF l_line_rec.created_by = l_hold_entity_id2 THEN
3447 l_create_order_hold_flag := 'Y';
3448 END IF;
3449 ELSIF l_hold_entity_code2 = 'P' and p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT THEN --ER#7479609
3450 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
3451 IF (l_payment_type_tab(pay_idx) = l_hold_entity_id2) THEN
3452 l_create_order_hold_flag := 'Y';
3453 EXIT;
3454 END IF;
3455 END LOOP;
3456 END IF;
3457
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
3467 IF l_hold_entity_code = 'C' and
3468 (l_hold_entity_code2 IS NULL OR
3469 l_hold_entity_code2 = 'OT' OR
3470 l_hold_entity_code2 = 'PT' OR
3471 l_hold_entity_code2 = 'TC' OR
3472 l_hold_entity_code2 = 'SC'
3473 ) THEN
3474 l_create_order_hold_flag := 'N';
3475 END IF;
3476
3477 IF l_hold_entity_code = 'OT' and (l_hold_entity_code2 = 'TC' OR l_hold_entity_code2 IS NULL)
3478 THEN
3479 l_create_order_hold_flag := 'N';
3480 END IF;
3481
3482 END IF;
3483 --ER#7479609 end
3484
3485
3486 IF l_create_order_hold_flag = 'Y' THEN
3487 l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
3488 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
3489 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
3490 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
3491 l_hold_source_rec.HOLD_ID := l_hold_id;
3492 l_hold_source_rec.hold_source_id := l_hold_source_id;
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
3502 ,x_msg_count => x_msg_count
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
3512 oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3513 END IF;
3514 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3515 RAISE FND_API.G_EXC_ERROR;
3516 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3518 END IF;
3519 --ER#7479609 start
3520 IF x_return_status = 'PAY_HOLD_NA' THEN
3521 G_PAYMENT_HOLD_APPLIED := 'Y';
3522 END IF;
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';
3532 ELSIF p_hold_entity_code = 'I' THEN
3533 l_attribute := 'Item';
3534 ELSIF p_hold_entity_code = 'S' THEN
3535 l_attribute := 'Ship to Site';
3536 ELSIF p_hold_entity_code = 'B' THEN
3537 l_attribute := 'Bill to Site';
3538 ELSIF p_hold_entity_code = 'O' then
3539 l_attribute := 'Order';
3540 ELSIF p_hold_entity_code = 'W' then
3541 l_attribute := 'Warehouse';
3542 ELSIF p_hold_entity_code = 'H' then
3543 l_attribute := 'Blanket Number';
3544 --ER#7479609 start
3545 ELSIF l_hold_entity_code = 'TM' THEN
3546 l_attribute := 'Top Model';
3547 ELSIF l_hold_entity_code = 'PR' then
3548 l_attribute := 'Project Number';
3549 ELSIF l_hold_entity_code = 'PL' then
3550 l_attribute := 'Price List';
3551 ELSIF l_hold_entity_code = 'OT' then
3552 l_attribute := 'Order Type';
3553 ELSIF l_hold_entity_code = 'CD' THEN
3554 l_attribute := 'Creation Date';
3555 ELSIF l_hold_entity_code = 'SC' then
3556 l_attribute := 'Sales Channel Code';
3557 ELSIF l_hold_entity_code = 'P' then
3558 G_PAYMENT_HOLD_APPLIED := 'Y';
3559 l_attribute := 'Payment Type';
3560 ELSIF l_hold_entity_code = 'SM' then
3561 l_attribute := 'Shipping Method Code';
3562 --ER#7479609 end
3563 END IF;
3564 IF l_hold_entity_code2 is not null then
3565 IF l_hold_entity_code2 = 'C' THEN
3566 l_attribute := l_attribute || '/' || 'Customer';
3567 ELSIF l_hold_entity_code2 = 'S' THEN
3568 l_attribute := l_attribute || '/' || 'Ship to Site';
3569 ELSIF l_hold_entity_code2 = 'B' THEN
3570 l_attribute := l_attribute || '/' || 'Bill to Site';
3571 ELSIF l_hold_entity_code2 = 'W' then
3572 l_attribute := l_attribute || '/' || 'Warehouse';
3573 ELSIF l_hold_entity_code2 = 'H' THEN
3574 l_attribute := l_attribute || '/' || 'Blanket Number';
3575 ELSIF l_hold_entity_code2 = 'L' THEN
3576 l_attribute := l_attribute || '/' || 'Bl Line Number';
3577 --ER#7479609 start
3578 ELSIF l_hold_entity_code2 = 'LT' THEN
3579 l_attribute := l_attribute || '/' || 'Line Type';
3580 ELSIF l_hold_entity_code2 = 'SM' THEN
3581 l_attribute := l_attribute || '/' || 'Shipping Method Code';
3582 ELSIF l_hold_entity_code2 = 'D' then
3583 l_attribute := l_attribute || '/' || 'Deliver to Site';
3584 ELSIF l_hold_entity_code2 = 'ST' then
3585 l_attribute := l_attribute || '/' || 'Source Type Code';
3586 ELSIF l_hold_entity_code2 = 'PL' THEN
3587 l_attribute := l_attribute || '/' || 'Price List';
3588 ELSIF l_hold_entity_code2 = 'PR' THEN
3589 l_attribute := l_attribute || '/' || 'Project Number';
3590 ELSIF l_hold_entity_code2 = 'PT' THEN
3591 l_attribute := l_attribute || '/' || 'Payment Term';
3592 ELSIF l_hold_entity_code2 = 'OI' THEN
3593 l_attribute := l_attribute || '/' || 'Option Item';
3594 ELSIF l_hold_entity_code2 = 'T' then
3595 l_attribute := l_attribute || '/' || 'Task Number';
3596 ELSIF l_hold_entity_code2 = 'OT' then
3597 l_attribute := l_attribute || '/' || 'Order Type';
3598 ELSIF l_hold_entity_code2 = 'P' THEN
3599 G_PAYMENT_HOLD_APPLIED := 'Y';
3600 l_attribute := l_attribute || '/' || 'Payment Type';
3601 ELSIF l_hold_entity_code2 = 'TC' THEN
3602 l_attribute := l_attribute || '/' || 'Currency';
3603 ELSIF l_hold_entity_code2 = 'SC' then
3604 l_attribute := l_attribute || '/' || 'Sales Channel Code';
3605 ELSIF l_hold_entity_code2 = 'CB' THEN
3606 l_attribute := l_attribute || '/' || 'Created By';
3607 --ER#7479609 end
3608 END IF;
3609 end if;
3610
3611 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3612 fnd_message.set_name('ONT','OE_HLD_APPLIED');
3613 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
3614 OE_MSG_PUB.ADD;
3615 --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3616 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3617 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN
3618 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
3619 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
3620 -- Get the line number from the line record
3621 --SELECT line_number
3622 -- INTO l_line_number
3623 -- FROM OE_ORDER_LINES
3624 -- WHERE LINE_ID = p_entity_id;
3625 --FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
3626 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
3627 OE_MSG_PUB.ADD;
3628 END IF;
3629 END IF; -- if create_order_hold was successful
3630 END IF; -- l_create_order_hold_flag = 'Y'
3631 END LOOP;
3632 CLOSE curr_hold_source;
3633
3634 -- Check for Current second entity hold if the entity is ('C','S','B','W','H')
3635 --ER#7479609 if p_hold_entity_code in ('C','S','B','W','H') then
3636 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') then --ER#7479609
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;
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
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
3661 l_create_order_hold_flag := 'N';
3662 /*ER#7479609 start
3663 IF l_hold_entity_code = 'I' THEN
3664 IF l_inventory_item_id = l_hold_entity_id THEN
3665 l_create_order_hold_flag := 'Y';
3666 END IF;
3667 ELSIF l_hold_entity_code = 'W' THEN
3668 IF l_ship_from_org_id = l_hold_entity_id THEN
3669 l_create_order_hold_flag := 'Y';
3670 END IF;
3671 ELSIF l_hold_entity_code = 'H' THEN
3672 IF l_blanket_number = l_hold_entity_id THEN
3673 l_create_order_hold_flag := 'Y';
3674 END IF;
3675 END IF;
3676 ER#7479609 end*/
3677
3678 --ER#7479609 start
3679 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3680 IF l_hold_entity_code = 'C' THEN
3681 IF l_header_rec.sold_to_org_id = l_hold_entity_id THEN
3682 l_create_order_hold_flag := 'Y';
3683 END IF;
3684 ELSIF l_hold_entity_code = 'PL' THEN
3685 IF l_header_rec.price_list_id = l_hold_entity_id THEN
3686 l_create_order_hold_flag := 'Y';
3687 END IF;
3688 ELSIF l_hold_entity_code = 'OT' THEN
3689 IF l_header_rec.order_type_id = l_hold_entity_id THEN
3690 l_create_order_hold_flag := 'Y';
3691 END IF;
3692 END IF;
3693
3694 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3695 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN
3696 --ER#7479609 end
3697 IF l_hold_entity_code = 'B' THEN
3698 IF l_line_rec.invoice_to_org_id = l_hold_entity_id THEN
3699 l_create_order_hold_flag := 'Y';
3700 END IF;
3701 ELSIF l_hold_entity_code = 'CD' THEN
3702 IF l_line_rec.creation_date = l_hold_entity_id THEN
3703 l_create_order_hold_flag := 'Y';
3704 END IF;
3705 ELSIF l_hold_entity_code = 'C' THEN
3706 IF l_line_rec.sold_to_org_id = l_hold_entity_id THEN
3707 l_create_order_hold_flag := 'Y';
3708 END IF;
3709 ELSIF l_hold_entity_code = 'I' THEN
3710 IF l_line_rec.inventory_item_id = l_hold_entity_id THEN
3711 l_create_order_hold_flag := 'Y';
3712 END IF;
3713 ELSIF l_hold_entity_code = 'OT' THEN
3714 IF l_header_rec.order_type_id = l_hold_entity_id THEN
3715 l_create_order_hold_flag := 'Y';
3716 END IF;
3717 ELSIF l_hold_entity_code = 'PL' THEN
3718 IF l_line_rec.price_list_id = l_hold_entity_id THEN
3719 l_create_order_hold_flag := 'Y';
3720 END IF;
3721 ELSIF l_hold_entity_code = 'PR' THEN
3722 IF l_line_rec.project_id = l_hold_entity_id THEN
3723 l_create_order_hold_flag := 'Y';
3724 END IF;
3725 ELSIF l_hold_entity_code = 'H' THEN
3726 IF l_line_rec.blanket_number = l_hold_entity_id THEN
3727 l_create_order_hold_flag := 'Y';
3728 END IF;
3729 ELSIF l_hold_entity_code = 'S' THEN
3730 IF l_line_rec.ship_to_org_id = l_hold_entity_id THEN
3731 l_create_order_hold_flag := 'Y';
3732 END IF;
3733 ELSIF l_hold_entity_code = 'TM' THEN
3734 IF l_line_rec.inventory_item_id = l_hold_entity_id THEN
3735 l_create_order_hold_flag := 'Y';
3736 END IF;
3737 ELSIF l_hold_entity_code = 'W' THEN
3738 IF l_line_rec.ship_from_org_id = l_hold_entity_id THEN
3739 l_create_order_hold_flag := 'Y';
3740 END IF;
3741 ELSIF l_hold_entity_code = 'D' THEN
3742 IF l_line_rec.deliver_to_org_id = l_hold_entity_id THEN
3743 l_create_order_hold_flag := 'Y';
3744 END IF;
3745 END IF;
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;
3755 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
3756 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
3757 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
3758 l_hold_source_rec.HOLD_ID := l_hold_id;
3759 l_hold_source_rec.hold_source_id := l_hold_source_id;
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
3769 ,x_msg_count => x_msg_count
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
3779 oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3780 END IF;
3781 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3782 RAISE FND_API.G_EXC_ERROR;
3783 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3785 END IF;
3786
3787 --ER#7479609 start
3788 IF x_return_status = 'PAY_HOLD_NA' THEN
3789 G_PAYMENT_HOLD_APPLIED := 'Y';
3790 END IF;
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';
3800 ELSIF l_hold_entity_code = 'I' THEN
3801 l_attribute := 'Item';
3802 ELSIF l_hold_entity_code = 'S' THEN
3803 l_attribute := 'Site Use';
3804 ELSIF l_hold_entity_code = 'B' THEN
3805 l_attribute := 'Bill to Site';
3806 ELSIF l_hold_entity_code = 'O' then
3807 l_attribute := 'Order';
3808 ELSIF l_hold_entity_code = 'W' then
3809 l_attribute := 'Warehouse';
3810 ELSIF l_hold_entity_code = 'H' then
3811 l_attribute := 'Blanket Number';
3812 --ER#7479609 start
3813 ELSIF l_hold_entity_code = 'TM' THEN
3814 l_attribute := 'Top Model';
3815 ELSIF l_hold_entity_code = 'PR' then
3816 l_attribute := 'Project Number';
3817 ELSIF l_hold_entity_code = 'PL' then
3818 l_attribute := 'Price List';
3819 ELSIF l_hold_entity_code = 'OT' then
3820 l_attribute := 'Order Type';
3821 ELSIF l_hold_entity_code = 'CD' THEN
3822 l_attribute := 'Creation Date';
3823 ELSIF l_hold_entity_code = 'SC' then
3824 l_attribute := 'Sales Channel Code';
3825 ELSIF l_hold_entity_code = 'P' then
3826 G_PAYMENT_HOLD_APPLIED := 'Y';
3827 l_attribute := 'Payment Type';
3828 ELSIF l_hold_entity_code = 'SM' then
3829 l_attribute := 'Shipping Method Code';
3830 --ER#7479609 end
3831 END IF;
3832
3833 IF l_hold_entity_code2 is not null then
3834 IF l_hold_entity_code2 = 'C' THEN
3835 l_attribute := l_attribute || '/' || 'Customer';
3836 ELSIF l_hold_entity_code2 = 'S' THEN
3837 l_attribute := l_attribute || '/' || 'Ship to Site';
3838 ELSIF l_hold_entity_code2 = 'B' THEN
3839 l_attribute := l_attribute || '/' || 'Bill to Site';
3840 ELSIF l_hold_entity_code2 = 'W' then
3841 l_attribute := l_attribute || '/' || 'Warehouse';
3842 ELSIF l_hold_entity_code2 = 'H' then
3843 l_attribute := l_attribute || '/' || 'Blanket Number';
3844 ELSIF l_hold_entity_code2 = 'L' then
3845 l_attribute := l_attribute || '/' || 'Bl Line Number';
3846 --ER#7479609 start
3847 ELSIF l_hold_entity_code2 = 'LT' THEN
3848 l_attribute := l_attribute || '/' || 'Line Type';
3849 ELSIF l_hold_entity_code2 = 'SM' THEN
3850 l_attribute := l_attribute || '/' || 'Shipping Method Code';
3851 ELSIF l_hold_entity_code2 = 'D' then
3852 l_attribute := l_attribute || '/' || 'Deliver to Site';
3853 ELSIF l_hold_entity_code2 = 'ST' then
3854 l_attribute := l_attribute || '/' || 'Source Type Code';
3855 ELSIF l_hold_entity_code2 = 'PL' THEN
3856 l_attribute := l_attribute || '/' || 'Price List';
3857 ELSIF l_hold_entity_code2 = 'PR' THEN
3858 l_attribute := l_attribute || '/' || 'Project Number';
3859 ELSIF l_hold_entity_code2 = 'PT' THEN
3860 l_attribute := l_attribute || '/' || 'Payment Term';
3861 ELSIF l_hold_entity_code2 = 'OI' THEN
3862 l_attribute := l_attribute || '/' || 'Option Item';
3863 ELSIF l_hold_entity_code2 = 'T' then
3864 l_attribute := l_attribute || '/' || 'Task Number';
3865 ELSIF l_hold_entity_code2 = 'OT' then
3866 l_attribute := l_attribute || '/' || 'Order Type';
3867 ELSIF l_hold_entity_code2 = 'P' THEN
3868 G_PAYMENT_HOLD_APPLIED := 'Y';
3869 l_attribute := l_attribute || '/' || 'Payment Type';
3870 ELSIF l_hold_entity_code2 = 'TC' THEN
3871 l_attribute := l_attribute || '/' || 'Currency';
3872 ELSIF l_hold_entity_code2 = 'SC' then
3873 l_attribute := l_attribute || '/' || 'Sales Channel Code';
3874 ELSIF l_hold_entity_code2 = 'CB' THEN
3875 l_attribute := l_attribute || '/' || 'Created By';
3876 --ER#7479609 end
3877 END IF;
3878 end if;
3879
3880 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3881 fnd_message.set_name('ONT','OE_HLD_APPLIED');
3882 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
3883 OE_MSG_PUB.ADD;
3884 --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3885 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3886 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3887 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
3888 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
3889 -- Get the line number from the line record
3890 --SELECT line_number
3891 -- INTO l_line_number
3892 -- FROM OE_ORDER_LINES
3893 -- WHERE LINE_ID = p_entity_id;
3894 --FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
3895 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
3896 OE_MSG_PUB.ADD;
3897 END IF;
3898 END IF; -- if apply hold was successful
3899 END IF; -- l_create_order_hold_flag = 'Y'
3900 END LOOP;
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
3910 WHEN FND_API.G_EXC_ERROR THEN
3911 IF (prev_hold%isopen) THEN
3912 CLOSE prev_hold;
3913 END IF;
3914 IF (curr_hold_source%isopen) THEN
3915 CLOSE curr_hold_source;
3916 END IF;
3917 x_return_status := FND_API.G_RET_STS_ERROR;
3918
3919 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3920 IF (prev_hold%isopen) THEN
3921 CLOSE prev_hold;
3922 END IF;
3923 IF (curr_hold_source%isopen) THEN
3924 CLOSE curr_hold_source;
3925 END IF;
3926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3927
3928 WHEN OTHERS THEN
3929 IF (prev_hold%isopen) THEN
3930 CLOSE prev_hold;
3931 END IF;
3932 IF (curr_hold_source%isopen) THEN
3933 CLOSE curr_hold_source;
3934 END IF;
3935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3936
3937 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3938 THEN
3939 OE_MSG_PUB.Add_Exc_Msg
3940 ( G_PKG_NAME
3941 , 'evaluate_holds'
3942 );
3943 END IF;
3944
3945 END evaluate_holds;
3946
3947
3948
3949 /********************************/
3950 /* EVALUATE_HOLDS_POST_WRITE */
3951 /********************************/
3952 PROCEDURE eval_post_write_header
3953 ( p_entity_code IN VARCHAR2
3954 , p_entity_id IN NUMBER
3955 , p_hold_entity_code IN VARCHAR2
3956 --ER#7479609 , p_hold_entity_id IN NUMBER
3957 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
3958 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3959 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
3960 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3961 )
3962 IS
3963 l_header_id NUMBER DEFAULT NULL;
3964 l_line_id NUMBER DEFAULT NULL;
3965 l_hold_source_id NUMBER DEFAULT NULL;
3966 l_order_hold_id NUMBER DEFAULT NULL;
3967 l_line_number NUMBER DEFAULT NULL;
3968 l_return_status VARCHAR2(30);
3969 l_msg_count NUMBER;
3970 l_msg_data VARCHAR2(2000);
3971 l_attribute VARCHAR2(30);
3972 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
3973 l_hold_entity_code VARCHAR2(1);
3974 --ER#7479609 l_hold_entity_id NUMBER;
3975 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
3976 --ER#7479609 l_hold_entity_code2 VARCHAR2(1);
3977 l_hold_entity_code2 oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
3978 --ER#7479609 l_hold_entity_id2 NUMBER;
3979 l_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
3980 l_hold_id NUMBER;
3981
3982 --ER#7479609 m_hold_entity_code VARCHAR2(1);
3983 m_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
3984 --ER#7479609 m_hold_entity_id NUMBER;
3985 m_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
3986 --ER#7479609 m_hold_entity_code2 VARCHAR2(1);
3987 m_hold_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
3988 --ER#7479609 m_hold_entity_id2 NUMBER;
3989 m_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
3990 m_counter NUMBER;
3991
3992
3993 l_create_order_hold_flag VARCHAR2(1) := 'Y';
3994 --
3995 CURSOR curr_hold_source(p_hold_entity_code VARCHAR2,
3996 p_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE) IS --ER#7479609
3997 --ER#7479609 p_hold_entity_id NUMBER) IS
3998 SELECT HS.hold_source_id, hs.hold_id,
3999 hs.hold_entity_code, hs.hold_entity_id,
4000 hs.hold_entity_code2,hs.hold_entity_id2
4001 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4002 WHERE HLD.HOLD_ID = HS.HOLD_ID
4003 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4004 ROUND( SYSDATE )
4005 AND HS.RELEASED_FLAG = 'N'
4006 AND SYSDATE
4007 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4008 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4009 AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
4010 AND HS.HOLD_ENTITY_ID = p_hold_entity_id;
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
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
4026 oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4027 END IF;
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;
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
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;
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
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;
4065 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
4066 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
4067 l_hold_source_rec.HOLD_ID := l_hold_id;
4068 l_hold_source_rec.hold_source_id := l_hold_source_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
4078 ,x_msg_count => x_msg_count
4079 ,x_msg_data => x_msg_data
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
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
4103
4104 fnd_message.set_name('ONT','OE_HLD_APPLIED');
4105 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4106 OE_MSG_PUB.ADD;
4107 END IF; -- if create_order_hold was successful
4108 END LOOP;
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
4118 WHEN FND_API.G_EXC_ERROR THEN
4119 IF (curr_hold_source%isopen) THEN
4120 CLOSE curr_hold_source;
4121 END IF;
4122 x_return_status := FND_API.G_RET_STS_ERROR;
4123
4124 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4125 IF (curr_hold_source%isopen) THEN
4126 CLOSE curr_hold_source;
4127 END IF;
4128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4129
4130 WHEN OTHERS THEN
4131 IF (curr_hold_source%isopen) THEN
4132 CLOSE curr_hold_source;
4133 END IF;
4134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4135
4136 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4137 THEN
4138 OE_MSG_PUB.Add_Exc_Msg
4139 ( G_PKG_NAME
4140 , 'eval_post_write_header'
4141 );
4142 END IF;
4143
4144 END eval_post_write_header;
4145
4146
4147
4148 /********************************/
4149 /* EVALUATE_HOLDS_POST_WRITE */
4150 /********************************/
4151
4152 PROCEDURE evaluate_holds_post_write
4153 ( p_entity_code IN VARCHAR2
4154 , p_entity_id IN NUMBER
4155 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4156 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
4157 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4158 )
4159 IS
4160 l_header_id NUMBER DEFAULT NULL;
4161 l_line_id NUMBER DEFAULT NULL;
4162 l_hold_source_id NUMBER DEFAULT NULL;
4163 l_order_hold_id NUMBER DEFAULT NULL;
4164 l_line_number NUMBER DEFAULT NULL;
4165 l_return_status VARCHAR2(30);
4166 l_msg_count NUMBER;
4167 l_msg_data VARCHAR2(2000);
4168 temp NUMBER DEFAULT NULL;
4169 l_attribute VARCHAR2(50);
4170 --
4171 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
4172 --ER#7479609 l_hold_entity_code VARCHAR2(1);
4173 l_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4174 --ER#7479609 l_hold_entity_id NUMBER;
4175 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
4176 --ER#7479609
4177 l_hold_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
4178
4179 --ER#7479609 l_hold_entity_id2 NUMBER;
4180 l_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
4181 l_hold_id NUMBER;
4182 l_inventory_item_id NUMBER;
4183 l_sold_to_org_id NUMBER;
4184 l_invoice_to_org_id NUMBER;
4185 l_ship_to_org_id NUMBER;
4186 l_ship_from_org_id NUMBER;
4187
4188 --ER#7479609 m_hold_entity_code VARCHAR2(1);
4189 m_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4190 --ER#7479609 m_hold_entity_id NUMBER;
4191 m_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
4192 --ER#7479609 m_hold_entity_code2 VARCHAR2(1);
4193 m_hold_entity_code2 oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4194 --ER#7479609 m_hold_entity_id2 NUMBER;
4195 m_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
4196 m_counter NUMBER;
4197 l_blanket_number NUMBER;
4198 l_blanket_line_number NUMBER;
4199
4200
4201
4202 l_create_order_hold_flag VARCHAR2(1) := 'Y';
4203 --
4204 CURSOR curr_hold_source(p_hold_entity_code VARCHAR2,
4205 p_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE) IS --ER#7479609
4206 --ER#7479609 p_hold_entity_id NUMBER) IS
4207 SELECT HS.hold_source_id, hs.hold_id,
4208 hs.hold_entity_code, hs.hold_entity_id,
4209 hs.hold_entity_code2,hs.hold_entity_id2
4210 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4211 WHERE HLD.HOLD_ID = HS.HOLD_ID
4212 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4213 ROUND( SYSDATE )
4214 AND HS.RELEASED_FLAG = 'N'
4215 AND SYSDATE
4216 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4217 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4218 --ER#7479609 start
4219 AND DECODE(p_hold_entity_code,'OI',HS.HOLD_ENTITY_CODE2,HS.HOLD_ENTITY_CODE) = p_hold_entity_code
4220 AND DECODE(p_hold_entity_code,'OI',HS.HOLD_ENTITY_ID2,HS.HOLD_ENTITY_ID) = p_hold_entity_id;
4221 --ER#7479609 end
4222 /*ER#7479609 start
4223 AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
4224 AND HS.HOLD_ENTITY_ID = p_hold_entity_id;
4225 ER#7479609 end*/
4226
4227 /****************************************************************************
4228 Valid Entity Combination
4229 ^^^^^^^^^^^^^^^^^^^^^^^^
4230 > Item - Customer
4231 > Item - Customer Ship to Site
4232 > Item - Customer Bill to Site
4233 > Item - Warehouse
4234 > Warehouse - Customer
4235 > Warehouse - Customer Ship to Site
4236 > Warehouse - Customer Bill to Site
4237 > Order - Site (Bill To) (Used by Line level Credit Checking only)
4238
4239 > Item - Blanket Number
4240 > Blanket Number
4241 > Blanket Number - Customer Ship to Site
4242 > Blanket Number - Customer Bill to Site
4243 > Blanket Number - Warehouse
4244 > Blanket Number - Blanket Line Number
4245
4246 ***************************************************************************/
4247 /*
4248 CURSOR curr_hold_source_entity2(p_hold_entity_code2 VARCHAR2,
4249 p_hold_entity_id2 NUMBER) IS
4250 SELECT HS.hold_source_id, hs.hold_id,
4251 hs.hold_entity_code, hs.hold_entity_id,
4252 hs.hold_entity_code2,hs.hold_entity_id2
4253 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4254 WHERE HLD.HOLD_ID = HS.HOLD_ID
4255 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4256 ROUND( SYSDATE )
4257 AND HS.RELEASED_FLAG = 'N'
4258 AND SYSDATE
4259 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4260 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4261 AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code2
4262 AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id2
4263 AND HS.HOLD_ENTITY_CODE <> 'O';
4264 */
4265 --ER#7479609 start
4266 l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
4267 l_line_rec OE_ORDER_LINES_ALL%rowtype;
4268 TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
4269 entity_id oe_hold_sources_all.hold_entity_id%TYPE);
4270
4271 TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;
4272
4273 l_entity_tab entity_tab;
4274
4275 TYPE payment_type_tab IS TABLE OF OE_PAYMENTS.PAYMENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
4276 l_payment_type_tab payment_type_tab;
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
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
4295 -- Indicates Header Level action
4296 l_header_id := p_entity_id;
4297
4298 --ER#7479609 start
4299 BEGIN
4300 SELECT * INTO l_header_rec
4301 FROM oe_order_headers
4302 WHERE header_id=l_header_id;
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
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
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
4325 SELECT OH.* INTO l_header_rec
4326 FROM oe_order_headers OH,oe_order_lines OL
4327 WHERE OH.header_id=OL.header_id
4328 AND OL.line_id=l_line_id;
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
4338
4339 BEGIN
4340 /*ER#7479609 start
4341 SELECT sold_to_org_id,
4342 invoice_to_org_id,
4343 ship_to_org_id,
4344 ship_from_org_id,
4345 inventory_item_id,
4346 line_number,
4347 blanket_number,
4348 blanket_line_number,
4349 header_id
4350 INTO l_sold_to_org_id,
4351 l_invoice_to_org_id,
4352 l_ship_to_org_id,
4353 l_ship_from_org_id,
4354 l_inventory_item_id,
4355 l_line_number,
4356 l_blanket_number,
4357 l_blanket_line_number,
4358 l_header_id
4359 FROM oe_order_lines
4360 WHERE line_id = l_line_id;
4361 ER#7479609 end*/
4362 --ER#7479609 start
4363 SELECT * INTO l_line_rec
4364 FROM oe_order_lines
4365 WHERE line_id = l_line_id;
4366 --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
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
4384 --ER#7479609 start
4385 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4386
4387 BEGIN
4388 l_payment_type_tab.delete;
4389
4390 SELECT V.payment_type
4391 BULK COLLECT INTO l_payment_type_tab
4392 FROM
4393 (Select payment_type_code payment_type
4394 FROM OE_PAYMENTS
4395 WHERE header_id=l_header_rec.header_id
4396 AND line_id IS NULL
4397 UNION
4398 SELECT payment_type_code payment_type
4399 FROM OE_ORDER_HEADERS_ALL
4400 WHERE header_id=l_header_rec.header_id) V;
4401 EXCEPTION
4402 WHEN OTHERS THEN
4403 null;
4404 END;
4405 FOR m_counter IN 1..4 LOOP
4406 IF m_counter = 1 THEN
4407 l_entity_tab(m_counter).entity_code := 'C';
4408 l_entity_tab(m_counter).entity_id := l_header_rec.sold_to_org_id;
4409 ELSIF m_counter = 2 THEN
4410 l_entity_tab(m_counter).entity_code := 'PL';
4411 l_entity_tab(m_counter).entity_id := l_header_rec.price_list_id;
4412 ELSIF m_counter = 3 THEN
4413 l_entity_tab(m_counter).entity_code := 'OT';
4414 l_entity_tab(m_counter).entity_id := l_header_rec.order_type_id;
4415 ELSIF m_counter = 4 THEN
4416 l_entity_tab(m_counter).entity_code := 'SC';
4417 l_entity_tab(m_counter).entity_id := l_header_rec.sales_channel_code;
4418 END IF;
4419 END LOOP;
4420 m_counter := 4;
4421 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
4422 m_counter := m_counter + 1;
4423 l_entity_tab(m_counter).entity_code := 'P';
4424 l_entity_tab(m_counter).entity_id := l_payment_type_tab(pay_idx);
4425 END LOOP;
4426
4427 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4428 --ER#7479609 end
4429
4430 BEGIN
4431 l_payment_type_tab.delete;
4432
4433 Select payment_type_code payment_type
4434 BULK COLLECT INTO l_payment_type_tab
4435 FROM OE_PAYMENTS
4436 WHERE header_id=l_header_rec.header_id
4437 AND line_id IS NOT NULL;
4438 EXCEPTION
4439 WHEN OTHERS THEN
4440 null;
4441 END;
4442
4443
4444 FOR m_counter IN 1..12 LOOP
4445 IF m_counter = 1 THEN
4446 l_entity_tab(m_counter).entity_code := 'B';
4447 l_entity_tab(m_counter).entity_id := l_line_rec.invoice_to_org_id;
4448 ELSIF m_counter = 2 THEN
4449 l_entity_tab(m_counter).entity_code := 'S';
4450 l_entity_tab(m_counter).entity_id := l_line_rec.ship_to_org_id;
4451 ELSIF m_counter = 3 THEN
4452 l_entity_tab(m_counter).entity_code := 'W';
4453 l_entity_tab(m_counter).entity_id := l_line_rec.ship_from_org_id;
4454 ELSIF m_counter = 4 THEN
4455 l_entity_tab(m_counter).entity_code := 'I';
4456 l_entity_tab(m_counter).entity_id := l_line_rec.inventory_item_id;
4457 ELSIF m_counter = 5 THEN
4458 l_entity_tab(m_counter).entity_code := 'H';
4459 l_entity_tab(m_counter).entity_id := l_line_rec.blanket_number;
4460 --ER#7479609 start
4461 ELSIF m_counter = 6 THEN
4462 l_entity_tab(m_counter).entity_code := 'C';
4463 l_entity_tab(m_counter).entity_id := l_header_rec.sold_to_org_id;
4464 ELSIF m_counter = 7 THEN
4465 l_entity_tab(m_counter).entity_code := 'TM';
4466 l_entity_tab(m_counter).entity_id := l_line_rec.inventory_item_id;
4467 ELSIF m_counter = 8 THEN
4468 l_entity_tab(m_counter).entity_code := 'PR';
4469 l_entity_tab(m_counter).entity_id := l_line_rec.project_id;
4470 ELSIF m_counter = 9 THEN
4471 l_entity_tab(m_counter).entity_code := 'OT';
4472 l_entity_tab(m_counter).entity_id := l_header_rec.order_type_id;
4473 ELSIF m_counter = 10 THEN
4474 l_entity_tab(m_counter).entity_code := 'CD';
4475 l_entity_tab(m_counter).entity_id := to_char(l_line_rec.creation_date,'DD-MON-RRRR');
4476 ELSIF m_counter = 11 THEN
4477 l_entity_tab(m_counter).entity_code := 'SM';
4478 l_entity_tab(m_counter).entity_id := l_line_rec.shipping_method_code;
4479 ELSIF m_counter = 12 THEN
4480 l_entity_tab(m_counter).entity_code := 'OI';
4481 l_entity_tab(m_counter).entity_id := l_line_rec.inventory_item_id;
4482 END IF;
4483 END LOOP;
4484
4485 m_counter := 12;
4486 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
4487 m_counter := m_counter + 1;
4488 l_entity_tab(m_counter).entity_code := 'P';
4489 l_entity_tab(m_counter).entity_id := l_payment_type_tab(pay_idx);
4490 END LOOP;
4491 --ER#7479609 end
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
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;
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
4529 l_create_order_hold_flag := 'Y';
4530 IF l_hold_entity_code2 is not null THEN
4531 l_create_order_hold_flag := 'N';
4532 --ER#7479609 start
4533 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4534
4535 IF l_hold_entity_code2 = 'OT' THEN
4536 IF l_header_rec.order_type_id = l_hold_entity_id2 THEN
4537 l_create_order_hold_flag := 'Y';
4538 END IF;
4539 ELSIF l_hold_entity_code2 = 'PT' THEN
4540 IF l_header_rec.payment_type_code = l_hold_entity_id2 THEN
4541 l_create_order_hold_flag := 'Y';
4542 END IF;
4543 ELSIF l_hold_entity_code2 = 'TC' THEN
4544 IF l_header_rec.transactional_curr_code = l_hold_entity_id2 THEN
4545 l_create_order_hold_flag := 'Y';
4546 END IF;
4547 ELSIF l_hold_entity_code2 = 'SC' THEN
4548 IF l_header_rec.sales_channel_code = l_hold_entity_id2 THEN
4549 l_create_order_hold_flag := 'Y';
4550 END IF;
4551 ELSIF l_hold_entity_code2 = 'P' THEN
4552 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
4553 IF l_payment_type_tab(pay_idx) = l_hold_entity_id2 THEN
4554 l_create_order_hold_flag := 'Y';
4555 EXIT;
4556 END IF;
4557 END LOOP;
4558 END IF;
4559
4560 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4561 --ER#7479609 end
4562 IF l_hold_entity_code2 = 'C' THEN
4563 IF l_line_rec.sold_to_org_id = l_hold_entity_id2 THEN
4564 l_create_order_hold_flag := 'Y';
4565 END IF;
4566 ELSIF l_hold_entity_code2 = 'B' THEN
4567 IF l_line_rec.invoice_to_org_id = l_hold_entity_id2 THEN
4568 l_create_order_hold_flag := 'Y';
4569 END IF;
4570 ELSIF l_hold_entity_code2 = 'S' THEN
4571 IF l_line_rec.ship_to_org_id = l_hold_entity_id2 THEN
4572 l_create_order_hold_flag := 'Y';
4573 END IF;
4574 ELSIF l_hold_entity_code2 = 'W' THEN
4575 IF l_line_rec.ship_from_org_id = l_hold_entity_id2 THEN
4576 l_create_order_hold_flag := 'Y';
4577 END IF;
4578 ELSIF l_hold_entity_code2 = 'O' THEN
4579 IF l_line_rec.header_id = l_hold_entity_id2 THEN
4580 l_create_order_hold_flag := 'Y';
4581 END IF;
4582 ELSIF l_hold_entity_code2 = 'H' THEN
4583 IF l_line_rec.blanket_number = l_hold_entity_id2 THEN
4584 l_create_order_hold_flag := 'Y';
4585 END IF;
4586 ELSIF l_hold_entity_code2 = 'L' THEN
4587 IF l_line_rec.blanket_line_number = l_hold_entity_id2 THEN
4588 l_create_order_hold_flag := 'Y';
4589 END IF;
4590
4591 ELSIF l_hold_entity_code2 = 'LT' THEN
4592 IF l_line_rec.line_type_id = l_hold_entity_id2 THEN
4593 l_create_order_hold_flag := 'Y';
4594 END IF;
4595 ELSIF l_hold_entity_code2 = 'SM' THEN
4596 IF l_line_rec.shipping_method_code = l_hold_entity_id2 THEN
4597 l_create_order_hold_flag := 'Y';
4598 END IF;
4599 ELSIF l_hold_entity_code2 = 'D' THEN
4600 IF l_line_rec.deliver_to_org_id = l_hold_entity_id2 THEN
4601 l_create_order_hold_flag := 'Y';
4602 END IF;
4603 ELSIF l_hold_entity_code2 = 'ST' THEN
4604 IF l_line_rec.source_type_code = l_hold_entity_id2 THEN
4605 l_create_order_hold_flag := 'Y';
4606 END IF;
4607 ELSIF l_hold_entity_code2 = 'PL' THEN
4608 IF l_line_rec.price_list_id = l_hold_entity_id2 THEN
4609 l_create_order_hold_flag := 'Y';
4610 END IF;
4611 ELSIF l_hold_entity_code2 = 'PR' THEN
4612 IF l_line_rec.project_id = l_hold_entity_id2 THEN
4613 l_create_order_hold_flag := 'Y';
4614 END IF;
4615 ELSIF l_hold_entity_code2 = 'PT' THEN
4616 IF l_line_rec.payment_term_id = l_hold_entity_id2 THEN
4617 l_create_order_hold_flag := 'Y';
4618 END IF;
4619
4620 ELSIF l_hold_entity_code2 = 'OI' THEN
4621 IF l_line_rec.inventory_item_id = l_hold_entity_id2 THEN
4622 l_create_order_hold_flag := 'Y';
4623 END IF;
4624 ELSIF l_hold_entity_code2 = 'T' THEN
4625 IF l_line_rec.task_id = l_hold_entity_id2 THEN
4626 l_create_order_hold_flag := 'Y';
4627 END IF;
4628 ELSIF l_hold_entity_code2 = 'CB' THEN
4629 IF l_line_rec.created_by = l_hold_entity_id2 THEN
4630 l_create_order_hold_flag := 'Y';
4631 END IF;
4632 END IF;
4633 END IF; --ER#7479609
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
4643 IF l_hold_entity_code = 'C' and
4644 (l_hold_entity_code2 IS NULL OR
4645 l_hold_entity_code2 = 'OT' OR
4646 l_hold_entity_code2 = 'PT' OR
4647 l_hold_entity_code2 = 'TC' OR
4648 l_hold_entity_code2 = 'SC'
4649 ) THEN
4650 l_create_order_hold_flag := 'N';
4651 END IF;
4652
4653 IF l_hold_entity_code = 'OT' and (l_hold_entity_code2 = 'TC' OR l_hold_entity_code2 IS NULL)
4654 THEN
4655 l_create_order_hold_flag := 'N';
4656 END IF;
4657
4658 END IF;
4659 --ER#7479609 end
4660
4661 IF l_create_order_hold_flag = 'Y' THEN
4662 l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
4663 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
4664 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
4665 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
4666 l_hold_source_rec.HOLD_ID := l_hold_id;
4667 l_hold_source_rec.hold_source_id := l_hold_source_id;
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
4677 ,x_msg_count => x_msg_count
4678 ,x_msg_data => x_msg_data
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
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';
4702 ELSIF l_hold_entity_code = 'I' THEN
4703 l_attribute := 'Item';
4704 ELSIF l_hold_entity_code = 'S' THEN
4705 l_attribute := 'Ship to Site';
4706 ELSIF l_hold_entity_code = 'B' THEN
4707 l_attribute := 'Bill to Site';
4708 ELSIF l_hold_entity_code = 'O' then
4709 l_attribute := 'Order';
4710 ELSIF l_hold_entity_code = 'W' then
4711 l_attribute := 'Warehouse';
4712 ELSIF l_hold_entity_code = 'H' then
4713 l_attribute := 'Blanket Number';
4714 --ER#7479609 start
4715 ELSIF l_hold_entity_code = 'TM' THEN
4716 l_attribute := 'Top Model';
4717 ELSIF l_hold_entity_code = 'PR' then
4718 l_attribute := 'Project Number';
4719 ELSIF l_hold_entity_code = 'PL' then
4720 l_attribute := 'Price List';
4721 ELSIF l_hold_entity_code = 'OT' then
4722 l_attribute := 'Order Type';
4723 ELSIF l_hold_entity_code = 'CD' THEN
4724 l_attribute := 'Creation Date';
4725 ELSIF l_hold_entity_code = 'SC' then
4726 l_attribute := 'Sales Channel Code';
4727 ELSIF l_hold_entity_code = 'P' then
4728 l_attribute := 'Payment Type';
4729 ELSIF l_hold_entity_code = 'SM' then
4730 l_attribute := 'Shipping Method Code';
4731 --ER#7479609 end
4732 END IF;
4733 IF l_hold_entity_code2 is not null then
4734 IF l_hold_entity_code2 = 'C' THEN
4735 l_attribute := l_attribute || '/' || 'Customer';
4736 ELSIF l_hold_entity_code2 = 'S' THEN
4737 l_attribute := l_attribute || '/' || 'Ship to Site';
4738 ELSIF l_hold_entity_code2 = 'B' THEN
4739 l_attribute := l_attribute || '/' || 'Bill to Site';
4740 ELSIF l_hold_entity_code2 = 'W' then
4741 l_attribute := l_attribute || '/' || 'Warehouse';
4742 ELSIF l_hold_entity_code2 = 'O' then
4743 l_attribute := l_attribute || '/' || 'Order';
4744 ELSIF l_hold_entity_code2 = 'H' THEN
4745 l_attribute := l_attribute || '/' || 'Blanket Number';
4746 ELSIF l_hold_entity_code2 = 'L' THEN
4747 l_attribute := l_attribute || '/' || 'Blanket Line Number';
4748 --ER#7479609 start
4749 ELSIF l_hold_entity_code2 = 'LT' THEN
4750 l_attribute := l_attribute || '/' || 'Line Type';
4751 ELSIF l_hold_entity_code2 = 'SM' THEN
4752 l_attribute := l_attribute || '/' || 'Shipping Method Code';
4753 ELSIF l_hold_entity_code2 = 'D' then
4754 l_attribute := l_attribute || '/' || 'Deliver to Site';
4755 ELSIF l_hold_entity_code2 = 'ST' then
4756 l_attribute := l_attribute || '/' || 'Source Type Code';
4757 ELSIF l_hold_entity_code2 = 'PL' THEN
4758 l_attribute := l_attribute || '/' || 'Price List';
4759 ELSIF l_hold_entity_code2 = 'PR' THEN
4760 l_attribute := l_attribute || '/' || 'Project Number';
4761 ELSIF l_hold_entity_code2 = 'PT' THEN
4762 l_attribute := l_attribute || '/' || 'Payment Term';
4763 ELSIF l_hold_entity_code2 = 'OI' THEN
4764 l_attribute := l_attribute || '/' || 'Option Item';
4765 ELSIF l_hold_entity_code2 = 'T' then
4766 l_attribute := l_attribute || '/' || 'Task Number';
4767 ELSIF l_hold_entity_code2 = 'OT' then
4768 l_attribute := l_attribute || '/' || 'Order Type';
4769 ELSIF l_hold_entity_code2 = 'P' THEN
4770 l_attribute := l_attribute || '/' || 'Payment Type';
4771 ELSIF l_hold_entity_code2 = 'TC' THEN
4772 l_attribute := l_attribute || '/' || 'Currency';
4773 ELSIF l_hold_entity_code2 = 'SC' then
4774 l_attribute := l_attribute || '/' || 'Sales Channel Code';
4775 ELSIF l_hold_entity_code2 = 'CB' THEN
4776 l_attribute := l_attribute || '/' || 'Created By';
4777 --ER#7479609 end
4778 END IF;
4779 end if;
4780
4781
4782 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4783 fnd_message.set_name('ONT','OE_HLD_APPLIED');
4784 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4785 OE_MSG_PUB.ADD;
4786 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4787 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
4788 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4789 --ER#7479609 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
4790 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_rec.line_number); --ER#7479609
4791 OE_MSG_PUB.ADD;
4792 END IF;
4793
4794 END IF; -- if create_order_hold was successful
4795 END IF; -- l_create_order_hold_flag = 'Y'
4796 END LOOP;
4797 CLOSE curr_hold_source;
4798 END IF; -- IF m_hold_entity_id IS NOT NULL THEN
4799 END LOOP;
4800
4801 -- Check for Current second entity hold if the entity is ('C','B','S','W')
4802 /*
4803 FOR m_counter IN 1..4 LOOP
4804 IF m_counter = 1 THEN
4805 m_hold_entity_code2 := 'C';
4806 m_hold_entity_id2 := l_sold_to_org_id;
4807 ELSIF m_counter = 2 THEN
4808 m_hold_entity_code2 := 'B';
4809 m_hold_entity_id2 := l_invoice_to_org_id;
4810 ELSIF m_counter = 3 THEN
4811 m_hold_entity_code2 := 'S';
4812 m_hold_entity_id2 := l_ship_to_org_id;
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);
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
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/' ||
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 || '/' ||
4838 l_hold_entity_id2 || '/' ||
4839 l_hold_source_id, 1);
4840
4841 -- If second entity is not null, The First entity can only be I or W
4842 -- Check if order/line is eligible for hold
4843 l_create_order_hold_flag := 'N';
4844 IF l_hold_entity_code = 'I' THEN
4845 IF l_inventory_item_id = l_hold_entity_id THEN
4846 l_create_order_hold_flag := 'Y';
4847 END IF;
4848 ELSIF l_hold_entity_code = 'W' THEN
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;
4858 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
4859 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
4860 l_hold_source_rec.HOLD_ID := l_hold_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
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
4878 RAISE FND_API.G_EXC_ERROR;
4879 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4881 END IF;
4882 ELSE
4883 OE_Debug_PUB.Add('Hold applied');
4884
4885 IF l_hold_entity_code = 'C' THEN
4886 l_attribute := 'Customer';
4887 ELSIF l_hold_entity_code = 'I' THEN
4888 l_attribute := 'Item';
4889 ELSIF l_hold_entity_code = 'S' THEN
4890 l_attribute := 'Site Use';
4891 ELSIF l_hold_entity_code = 'B' THEN
4892 l_attribute := 'Bill to Site';
4893 ELSIF l_hold_entity_code = 'O' then
4894 l_attribute := 'Order';
4895 ELSIF l_hold_entity_code = 'W' then
4896 l_attribute := 'Warehouse';
4897 END IF;
4898 IF l_hold_entity_code2 is not null then
4899 IF l_hold_entity_code2 = 'C' THEN
4900 l_attribute := l_attribute || '/' || 'Customer';
4901 ELSIF l_hold_entity_code2 = 'S' THEN
4902 l_attribute := l_attribute || '/' || 'Ship to Site';
4903 ELSIF l_hold_entity_code2 = 'B' THEN
4904 l_attribute := l_attribute || '/' || 'Bill to Site';
4905 ELSIF l_hold_entity_code2 = 'W' then
4906 l_attribute := l_attribute || '/' || 'Warehouse';
4907 END IF;
4908 end if;
4909
4910 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4911 fnd_message.set_name('ONT','OE_HLD_APPLIED');
4912 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4913 OE_MSG_PUB.ADD;
4914 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4915 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
4916 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4917 -- Get the line number from the line record
4918 --SELECT line_number
4919 -- INTO l_line_number
4920 -- FROM OE_ORDER_LINES
4921 -- WHERE LINE_ID = p_entity_id;
4922 --FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
4923 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
4924 OE_MSG_PUB.ADD;
4925 END IF;
4926 END IF; -- if apply hold was successful
4927 END IF; -- l_create_order_hold_flag = 'Y'
4928 END LOOP;
4929 CLOSE curr_hold_source_entity2;
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
4939 WHEN FND_API.G_EXC_ERROR THEN
4940 IF (curr_hold_source%isopen) THEN
4941 CLOSE curr_hold_source;
4942 END IF;
4943 x_return_status := FND_API.G_RET_STS_ERROR;
4944
4945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4946 IF (curr_hold_source%isopen) THEN
4947 CLOSE curr_hold_source;
4948 END IF;
4949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4950
4951 WHEN OTHERS THEN
4952 IF (curr_hold_source%isopen) THEN
4953 CLOSE curr_hold_source;
4954 END IF;
4955 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4956
4957 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4958 THEN
4959 OE_MSG_PUB.Add_Exc_Msg
4960 ( G_PKG_NAME
4961 , 'evaluate_holds_post_write'
4962 );
4963 END IF;
4964
4965 END evaluate_holds_post_write;
4966 /*******************************/
4967
4968 FUNCTION Hold_exists
4969 ( p_hold_entity_code IN VARCHAR2
4970 --ER#7479609 , p_hold_entity_id IN NUMBER
4971 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
4972 , p_hold_id IN NUMBER DEFAULT 1
4973 , p_org_id IN NUMBER DEFAULT NULL
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 ) ;
4983 END IF;
4984 IF p_org_id IS NULL THEN
4985
4986 SELECT 'Y'
4987 INTO l_hold_exists
4988 FROM OE_HOLD_SOURCES_ALL
4989 WHERE hold_entity_code = p_hold_entity_code
4990 AND HOLD_ENTITY_ID = p_hold_entity_id
4991 AND hold_id = p_hold_id
4992 AND nvl(RELEASED_FLAG, 'N') = 'N'
4993 AND ORG_ID is null;
4994 ELSE
4995
4996 SELECT 'Y'
4997 INTO l_hold_exists
4998 FROM OE_HOLD_SOURCES_ALL
4999 WHERE hold_entity_code = p_hold_entity_code
5000 AND HOLD_ENTITY_ID = p_hold_entity_id
5001 AND hold_id = p_hold_id
5002 AND nvl(RELEASED_FLAG, 'N') = 'N'
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;
5012 ELSE
5013 return false;
5014 END IF;
5015
5016 EXCEPTION
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
5026 oe_debug_pub.add( 'Exception in OE_HOLDS_PUB.Hold_Exists' ) ;
5027 END IF;
5028 return false;
5029 END Hold_exists;
5030
5031 /* This processes the holds (apply,release) on a customer account.
5032 Called from process_holds procedure in this package
5033
5034 */
5035 PROCEDURE Process_Holds_Customer
5036 ( p_hold_entity_code IN VARCHAR2
5037 --ER#7479609 , p_hold_entity_id IN NUMBER
5038 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5039 , p_hold_id IN NUMBER DEFAULT 1
5040 , p_release_reason_code IN VARCHAR2 DEFAULT NULL
5041 , p_action IN VARCHAR2
5042 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5043 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5044 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5045 ) IS
5046
5047 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
5048 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
5049
5050 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5051 l_org_id NUMBER := null;
5052 l_hold_source_id NUMBER := null;
5053 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5054
5055 CURSOR c_hold_orgs IS
5056 SELECT DISTINCT org_id
5057 FROM HZ_CUST_ACCT_SITES_ALL acct
5058 WHERE acct.cust_account_id = p_hold_entity_id
5059 AND NOT EXISTS (SELECT 1
5060 FROM OE_HOLD_SOURCES_ALL src
5061 WHERE acct.cust_account_id = src.hold_entity_id
5062 AND src.hold_entity_code = 'C'
5063 AND acct.org_id = src.org_id
5064 AND src.hold_id = p_hold_id
5065 AND nvl(src.released_flag, 'N') = 'N')
5066 UNION
5067
5068 SELECT DISTINCT org_id
5069 FROM OE_ORDER_HEADERS_ALL hdr
5070 WHERE sold_to_org_id = p_hold_entity_id
5071 AND NOT EXISTS (select 1
5072 from oe_hold_sources_all
5073 where hold_entity_id = p_hold_entity_id
5074 and hold_entity_code = 'C'
5075 and hold_id = p_hold_id
5076 and nvl(RELEASED_FLAG, 'N') = 'N')
5077 AND NOT EXISTS (select 1
5078 from HZ_CUST_ACCT_SITES_ALL hzcas
5079 where hzcas.cust_account_id = p_hold_entity_id
5080 and hzcas.cust_account_id = hdr.sold_to_org_id);
5081
5082
5083 CURSOR c_cust_holds IS
5084 SELECT org_id, hold_source_id
5085 FROM OE_HOLD_SOURCES_ALL
5086 WHERE hold_entity_id = p_hold_entity_id
5087 AND hold_entity_code = 'C'
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;
5097 l_hold_source_rec.hold_id := p_hold_id;
5098
5099 IF p_action = 'APPLY' THEN
5100
5101 OPEN c_hold_orgs;
5102 LOOP
5103 FETCH c_hold_orgs into l_org_id;
5104 EXIT WHEN c_hold_orgs%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5105 -- can l_org_id be null??? No.
5106
5107 IF l_debug_level > 0 THEN
5108 oe_debug_pub.add('Applying Hold in ORG_ID ' || l_org_id, 1);
5109 END IF;
5110
5111 MO_GLOBAL.set_policy_context('S',l_org_id);
5112
5113 oe_holds_pvt.apply_Holds(
5114 p_hold_source_rec => l_hold_source_rec
5115 ,p_hold_existing_flg => 'Y'
5116 ,p_hold_future_flg => 'Y'
5117 ,x_return_status => x_return_status
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
5127 END LOOP;
5128 CLOSE c_hold_orgs;
5129
5130 ELSIF p_action = 'RELEASE' THEN
5131
5132 l_hold_release_rec.release_reason_code := p_release_reason_code;
5133 OPEN c_cust_holds;
5134 LOOP
5135
5136 FETCH c_cust_holds into l_org_id,l_hold_source_id;
5137 EXIT WHEN c_cust_holds%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5138
5139 IF l_debug_level > 0 THEN
5140 oe_debug_pub.add('Releasing Hold in ORG_ID ' || l_org_id, 1);
5141 END IF;
5142
5143 l_hold_source_rec.hold_source_id := l_hold_source_id;
5144
5145 MO_GLOBAL.set_policy_context('S',l_org_id);
5146
5147 oe_holds_pvt.Release_Holds(
5148 p_hold_source_rec => l_hold_source_rec
5149 ,p_hold_release_rec => l_hold_release_rec
5150 ,x_return_status => x_return_status
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
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
5168
5169 IF l_debug_level > 0 THEN
5170 oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5171 END IF;
5172 IF x_msg_count is not null then
5173 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5174 p_data => x_msg_data);
5175 end if;
5176 RAISE FND_API.G_EXC_ERROR;
5177
5178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5179 IF l_debug_level > 0 THEN
5180 oe_debug_pub.add( 'API UNEXPECTED ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
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
5185 , p_data => x_msg_data
5186 );
5187 END IF;
5188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5189
5190 WHEN OTHERS THEN
5191 IF l_debug_level > 0 THEN
5192 oe_debug_pub.add( 'ERROR in oe_holds_pub.Process_Holds_Customer' , 1 ) ;
5193 END IF;
5194 RAISE;
5195
5196 END Process_Holds_Customer;
5197
5198 /* This processes the holds (apply,release) on a site.
5199 Called from process_holds procedure in this package
5200
5201 */
5202 PROCEDURE Process_Holds_Site
5203 ( p_hold_entity_code IN VARCHAR2
5204 --ER#7479609 , p_hold_entity_id IN NUMBER
5205 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5206 , p_hold_id IN NUMBER DEFAULT 1
5207 , p_release_reason_code IN VARCHAR2 DEFAULT NULL
5208 , p_action IN VARCHAR2
5209 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5210 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5211 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5212 ) IS
5213
5214 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
5215 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
5216 l_hold_entity_code varchar2(1);
5217 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5218 l_hold_exists VARCHAR2(1) := 'N';
5219 l_org_id NUMBER := null;
5220 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5221 BEGIN
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
5229
5230 BEGIN
5231
5232 SELECT org_id into l_org_id
5233 FROM hz_cust_site_uses_all
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
5243 WHEN no_data_found then
5244 --x_return_status := FND_API.G_RET_STS_ERROR;
5245 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
5246 OE_MSG_PUB.ADD;
5247 fnd_message.set_token('SITE_USE_ID', to_char(p_hold_entity_id));
5248
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
5258 -- Validate hold entity (site) and get correct code for Ship To, Bill To
5259 l_hold_entity_code := Hold_Site_Code(p_hold_entity_id);
5260
5261 l_hold_source_rec.hold_entity_code := l_hold_entity_code;
5262 l_hold_source_rec.hold_entity_id := p_hold_entity_id;
5263 l_hold_source_rec.hold_id := p_hold_id;
5264 l_hold_release_rec.release_reason_code := p_release_reason_code;
5265
5266 IF p_action = 'APPLY' THEN
5267
5268 /* APPLY the hold iff there is no hold already. But if there is a hold already existing,
5269 should we give a message to the user? Right now, the API does nothing.
5270
5271 */
5272 IF NOT Hold_exists(p_hold_entity_code,p_hold_entity_id,p_hold_id,l_org_id) THEN
5273 oe_holds_pvt.apply_Holds(
5274 p_hold_source_rec => l_hold_source_rec
5275 ,p_hold_existing_flg => 'Y' -- Hold all existing orders
5276 ,p_hold_future_flg => 'Y' -- hold new orders also
5277 ,x_return_status => x_return_status
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
5287 ELSIF p_action = 'RELEASE' THEN
5288
5289 oe_holds_pvt.Release_Holds(
5290 p_hold_source_rec => l_hold_source_rec
5291 ,p_hold_release_rec => l_hold_release_rec
5292 ,x_return_status => x_return_status
5293 ,x_msg_count => x_msg_count
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 ) ;
5303 END IF;
5304 END IF; -- APPLY OR RELEASE
5305 EXCEPTION
5306 WHEN FND_API.G_EXC_ERROR THEN
5307
5308 IF l_debug_level > 0 THEN
5309 oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5310 END IF;
5311
5312 IF x_msg_count is not null then
5313 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5314 p_data => x_msg_data);
5315 end if;
5316
5317 RAISE FND_API.G_EXC_ERROR;
5318
5319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5320 IF l_debug_level > 0 THEN
5321 oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5322 END IF;
5323
5324 FND_MSG_PUB.Count_And_Get
5325 ( p_count => x_msg_count
5326 , p_data => x_msg_data
5327 );
5328
5329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5330
5331 WHEN OTHERS THEN
5332 IF l_debug_level > 0 THEN
5333 oe_debug_pub.add( 'ERROR in oe_holds_pub.process_holds' , 1 ) ;
5334 END IF;
5335 RAISE;
5336 END Process_Holds_Site;
5337
5338 /* This procedure is called by AR/TCA/IEX to put a Hold on the customer/site
5339
5340 Note: This procedure as opposed to the previous API called by AR/TCA/IEX.
5341 Oe_Holds.Hold_API is a function and returns a number as return status. This new API Process_Holds
5342 doesn't pass back any arguments, return status etc. is this OK with the calling programs??
5343 This API throws exceptions
5344
5345 Need to fix the appropriate error messages
5346
5347 Validate the commit process
5348
5349 continue to process remaining holds or throw API error if an occurs when processing the holds in a loop??
5350
5351 */
5352 PROCEDURE Process_Holds
5353 ( p_api_version IN NUMBER
5354 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
5355 , p_hold_entity_code IN VARCHAR2
5356 --ER#7479609 , p_hold_entity_id IN NUMBER
5357 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5358 , p_hold_id IN NUMBER DEFAULT 1
5359 , p_release_reason_code IN VARCHAR2 DEFAULT NULL
5360 , p_action IN VARCHAR2
5361 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5362 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5363 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5364 ) IS
5365
5366 l_orig_org NUMBER := null;
5367
5368 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5369 l_msg_count NUMBER := null;
5370 l_msg_data VARCHAR2(2000) := null;
5371 l_valid_acct VARCHAR2(1) := 'N';
5372
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
5378 WHERE cust_account_id = p_hold_entity_id;
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
5388 IF p_action IS NULL OR p_action NOT IN ('APPLY','RELEASE') THEN
5389 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ACTION'); -- message taken from oe_holds
5390 fnd_message.set_token('ACTION',p_action);
5391 OE_MSG_PUB.ADD;
5392 RAISE FND_API.G_EXC_ERROR;
5393 END IF;
5394
5395 IF p_hold_entity_code IS NULL OR p_hold_entity_code NOT IN ('S','C') THEN
5396 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_HOLD_ENTITY_CODE'); -- message need to be seeded or use any existing one
5397 OE_MSG_PUB.ADD;
5398 RAISE FND_API.G_EXC_ERROR;
5399 END IF;
5400 -- hold the original org id being used by programs when calling process_holds.
5401 -- set the org org context to this original org when leaving process_holds.
5402 -- sHOULD IT BE SET EVEN WHEN EXITING WITH AN EXCEPTION??
5403
5404 l_orig_org := MO_GLOBAL.get_current_org_id;
5405
5406 IF p_hold_entity_code = 'S' THEN
5407
5408 Process_Holds_Site
5409 ( p_hold_entity_code => p_hold_entity_code
5410 , p_hold_entity_id => p_hold_entity_id
5411 , p_hold_id => p_hold_id
5412 , p_release_reason_code => p_release_reason_code
5413 , p_action => p_action
5414 , x_return_status => l_return_status
5415 , x_msg_count => l_msg_count
5416 , x_msg_data => l_msg_data
5417 );
5418
5419 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5420
5421 IF l_debug_level > 0 THEN
5422 oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5423 END IF;
5424
5425 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5426 RAISE FND_API.G_EXC_ERROR;
5427 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5429 END IF;
5430 END IF;
5431
5432 ELSIF p_hold_entity_code = 'C' THEN
5433 -- validate the account
5434 OPEN c_valid_acct;
5435 FETCH c_valid_acct into l_valid_acct;
5436 CLOSE c_valid_acct;
5437
5438 IF l_valid_acct <> 'Y' THEN
5439 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_CUST_ACCOUNT'); -- get the correct message new or existing??
5440 -- fnd_message.set_token('',p_action);
5441 OE_MSG_PUB.ADD;
5442 RAISE FND_API.G_EXC_ERROR;
5443 END IF;
5444
5445 -- call process_holds_customer
5446
5447 Process_Holds_Customer
5448 ( p_hold_entity_code => p_hold_entity_code
5449 , p_hold_entity_id => p_hold_entity_id
5450 , p_hold_id => p_hold_id
5451 , p_release_reason_code => p_release_reason_code
5452 , p_action => p_action
5453 , x_return_status => l_return_status
5454 , x_msg_count => l_msg_count
5455 , x_msg_data => l_msg_data
5456 );
5457
5458 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5459
5460 IF l_debug_level > 0 THEN
5461 oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5462 END IF;
5463
5464 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5465 RAISE FND_API.G_EXC_ERROR;
5466 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5468 END IF;
5469 END IF;
5470
5471 END IF;
5472
5473 IF l_orig_org IS NULL THEN
5474 MO_GLOBAL.set_policy_context('M',null);
5475 ELSE
5476 MO_GLOBAL.set_policy_context('S',l_orig_org);
5477 END IF;
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
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;
5493 IF l_msg_count is not null then
5494 OE_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
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
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;
5512 FND_MSG_PUB.Count_And_Get
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
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;
5529 ROLLBACK TO SAVEPOINT oe_process_holds;
5530 RAISE;
5531 END Process_Holds;
5532
5533 END OE_Holds_PUB;