[Home] [Help]
PACKAGE BODY: APPS.OE_HOLDS_PUB
Source
1 PACKAGE BODY OE_Holds_PUB AS
2 /* $Header: OEXPHLDB.pls 120.34.12020000.2 2012/07/03 10:00:52 amallik 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 --ER 12363706, WHO Columns added
106 UPDATE OE_HOLD_SOURCES
107 SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
108 LAST_UPDATE_DATE = SYSDATE,
109 LAST_UPDATED_BY = FND_GLOBAL.user_id
110 WHERE HOLD_SOURCE_ID = p_hold_source_rec.HOLD_SOURCE_ID;
111
112 elsif (p_hold_source_rec.HOLD_ENTITY_CODE is NOT NULL AND
113 p_hold_source_rec.HOLD_ENTITY_ID is NOT NULL) THEN
114
115 if (p_hold_source_rec.HOLD_ENTITY_CODE2 is NOT NULL AND
116 p_hold_source_rec.HOLD_ENTITY_ID2 is NOT NULL) THEN
117 --ER 12363706, WHO Columns added
118 UPDATE OE_HOLD_SOURCES
119 SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
120 LAST_UPDATE_DATE = SYSDATE,
121 LAST_UPDATED_BY = FND_GLOBAL.user_id
122 WHERE HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
123 AND HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
124 AND HOLD_ENTITY_CODE2 = p_hold_source_rec.HOLD_ENTITY_CODE2
125 AND HOLD_ENTITY_ID2 = p_hold_source_rec.HOLD_ENTITY_ID2
126 AND HOLD_ID = p_hold_source_rec.hold_id
127 AND RELEASED_FLAG = 'N'
128 AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
129
130 else
131 /* Check to see if its a line-level hold */
132 if p_hold_source_rec.line_id is not null then
133 --ER 12363706, WHO Columns added
134 UPDATE OE_HOLD_SOURCES HS
135 SET HS.HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
136 HS.LAST_UPDATE_DATE = SYSDATE,
137 HS.LAST_UPDATED_BY = FND_GLOBAL.user_id
138
139 WHERE HS.HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
140 AND HS.HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
141 AND HS.HOLD_ENTITY_CODE2 is null
142 AND HS.HOLD_ENTITY_ID2 is null
143 AND HS.HOLD_ID = p_hold_source_rec.hold_id
144 AND HS.RELEASED_FLAG = 'N'
145 AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
146 AND exists (SELECT 'x'
147 FROM OE_ORDER_HOLDS OH
148 WHERE OH.LINE_ID = p_hold_source_rec.line_id
149 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID);
150 else
151 --ER 12363706, WHO Columns added
152 UPDATE OE_HOLD_SOURCES
153 SET HOLD_COMMENT = p_hold_source_rec.HOLD_COMMENT,
154 LAST_UPDATE_DATE = SYSDATE,
155 LAST_UPDATED_BY = FND_GLOBAL.user_id
156 WHERE HOLD_ENTITY_CODE = p_hold_source_rec.HOLD_ENTITY_CODE
157 AND HOLD_ENTITY_ID = p_hold_source_rec.HOLD_ENTITY_ID
158 AND HOLD_ENTITY_CODE2 is null
159 AND HOLD_ENTITY_ID2 is null
160 AND HOLD_ID = p_hold_source_rec.hold_id
161 AND RELEASED_FLAG = 'N'
162 AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
163 end if;
164 end if;
165
166 else
167 IF l_debug_level > 0 THEN
168 oe_debug_pub.add( 'OE_HOLDS_PUB.UPDATE_HOLD_COMMENTS:' || 'EITHER PASS HOLD_SOURCE_ID OR HOLD_ENTITY_CODE/HOLD_ENTITY_ID' ) ;
169 END IF;
170 RAISE FND_API.G_EXC_ERROR;
171 end if;
172
173 EXCEPTION /* Procedure exception handler */
174
175 WHEN FND_API.G_EXC_ERROR THEN
176 x_return_status := FND_API.G_RET_STS_ERROR ;
177 IF l_debug_level > 0 THEN
178 oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
179 END IF;
180 OE_MSG_PUB.Count_And_Get
181 ( p_count => x_msg_count,
182 p_data => x_msg_data
183 );
184
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
187 IF l_debug_level > 0 THEN
188 oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
189 END IF;
190 OE_MSG_PUB.Count_And_Get
191 ( p_count => x_msg_count,
192 p_data => x_msg_data
193 );
194
195 WHEN OTHERS THEN
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197 IF l_debug_level > 0 THEN
198 oe_debug_pub.add( 'EXPECTED ERROR IN UPDATE_HOLD_COMMENTS ; ' ) ;
199 END IF;
200 IF OE_MSG_PUB.Check_Msg_Level
201 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
202 THEN
203 OE_MSG_PUB.Add_Exc_Msg
204 (G_PKG_NAME,
205 l_api_name
206 );
207 END IF;
208 OE_MSG_PUB.Count_And_Get
209 ( p_count => x_msg_count,
210 p_data => x_msg_data);
211
212
213 END UPDATE_HOLD_COMMENTS;
214
215 /*
216 This procedure gets called from the concurrant manager. It will release
217 all the holds that have expired.
218 */
219 PROCEDURE RELEASE_EXPIRED_HOLDS
220 (
221 p_dummy1 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
222 p_dummy2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
223 p_org_id IN NUMBER
224 )
225 IS
226 l_api_name CONSTANT VARCHAR2(30) := 'release_expired_holds';
227 l_api_version CONSTANT NUMBER := 1.0;
228 l_hold_source_id NUMBER := 0;
229
230 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
231 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
232
233 l_return_status VARCHAR2(30);
234 l_msg_count NUMBER;
235 l_msg_data VARCHAR2(240);
236 l_org_id number;
237 l_curr_org_id number;
238
239 CURSOR expired_holds_cur IS
240 select hold_source_id, ORG_ID
241 from oe_hold_sources
242 where HOLD_UNTIL_DATE <= sysdate
243 and released_flag = 'N';
244 --
245 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
246 --
247 BEGIN
248 IF l_debug_level > 0 THEN
249 oe_debug_pub.add( 'IN RELEASE EXPIRED HOLDS' ) ;
250 oe_debug_pub.add( 'p_org_id:' || to_char(p_org_id) ) ;
251 END IF;
252 l_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 If p_org_id IS Not Null Then
255 -- Set Single Org access
256 MO_GLOBAL.SET_POLICY_CONTEXT('S', p_org_id);
257 l_curr_org_id := p_org_id;
258 Else
259 MO_GLOBAL.set_policy_context('M', '');
260 END IF;
261
262
263 open expired_holds_cur;
264 LOOP
265 fetch expired_holds_cur into l_hold_source_id, l_org_id;
266 if (expired_holds_cur%notfound) then
267 oe_debug_pub.add('Exiting expired_holds_cur%notfound:') ;
268 exit;
269 end if;
270 IF l_debug_level > 0 THEN
271 oe_debug_pub.add('RELEASE EXPIRED HOLD FOR:' || TO_CHAR (L_HOLD_SOURCE_ID) ||
272 ', ORG ID:' || TO_CHAR(l_org_id) );
273 END IF;
274
275 if l_org_id <> nvl(l_curr_org_id, -99)
276 then
277 oe_debug_pub.add('Setting Policy Context for:' || TO_CHAR(l_org_id) ) ;
278 MO_GLOBAL.SET_POLICY_CONTEXT('S', l_org_id);
279 l_curr_org_id := l_org_id;
280 oe_debug_pub.add('MO_GLOBAL.get_current_org_id;:' || to_char(MO_GLOBAL.get_current_org_id));
281 End if;
282
283 l_hold_source_rec.hold_source_id := l_hold_source_id;
284 l_hold_release_rec.RELEASE_REASON_CODE := 'EXPIRE';
285 l_hold_release_rec.RELEASE_COMMENT :=
286 'Expired Hold, Automatically Released';
287
288 oe_holds_pvt.Release_Holds(
289 p_hold_source_rec => l_hold_source_rec
290 ,p_hold_release_rec => l_hold_release_rec
291 ,x_return_status => l_return_status
292 ,x_msg_count => l_msg_count
293 ,x_msg_data => l_msg_data
294 );
295 IF l_debug_level > 0 THEN
296 oe_debug_pub.add( 'X_RETURN_STATUS:' || L_RETURN_STATUS , 1 ) ;
297 END IF;
298
299 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
300 IF l_debug_level > 0 THEN
301 oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS UNEXPECTED FAILURE' ) ;
302 END IF;
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
305 IF l_debug_level > 0 THEN
306 oe_debug_pub.add( 'RELEASE_EXPIRED_HOLDS EXPECTED FAILURE' ) ;
307 END IF;
308 RAISE FND_API.G_EXC_ERROR;
309 END IF;
310
311 end loop;
312
313 l_return_status := FND_API.G_RET_STS_SUCCESS;
314 -- Get message count and data
315 OE_MSG_PUB.Count_And_Get
316 ( p_count => l_msg_count
317 , p_data => l_msg_data
318 );
319
320 EXCEPTION /* Procedure exception handler */
321
322 WHEN FND_API.G_EXC_ERROR THEN
323 l_return_status := FND_API.G_RET_STS_ERROR ;
324 OE_MSG_PUB.Count_And_Get
325 ( p_count => l_msg_count,
326 p_data => l_msg_data
327 );
328
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
331 OE_MSG_PUB.Count_And_Get
332 ( p_count => l_msg_count,
333 p_data => l_msg_data
334 );
335
336 WHEN OTHERS THEN
337 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
338 IF OE_MSG_PUB.Check_Msg_Level
339 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
340 THEN
341 OE_MSG_PUB.Add_Exc_Msg
342 (G_PKG_NAME,
343 l_api_name
344 );
345 END IF;
346 OE_MSG_PUB.Count_And_Get
347 ( p_count => l_msg_count,
348 p_data => l_msg_data);
349
350 END release_expired_holds;
351
352 ----------------------------
353 PROCEDURE ValidateOrder
354 (p_header_id IN NUMBER DEFAULT NULL
355 , p_line_id IN NUMBER DEFAULT NULL
356 , p_hold_entity_code IN VARCHAR2
357 --ER#7479609 , p_hold_entity_id IN NUMBER
358 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
359 , p_hold_entity_code2 IN VARCHAR2
360 --ER#7479609 , p_hold_entity_id2 IN NUMBER
361 , p_hold_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE --ER#7479609
362 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
363 )
364 IS
365 l_api_name CONSTANT VARCHAR2(30) := 'ValidateOrder';
366 l_dummy VARCHAR2(30);
367 --
368 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
369 --
370 BEGIN
371
372 IF l_debug_level > 0 THEN
373 oe_debug_pub.add( 'IN OE_HOLDS_PUB.VALIDATEORDER' ) ;
374 END IF;
375
376 -- Initialize API return status to success
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378
379 BEGIN
380
381 IF p_line_id IS NOT NULL THEN
382
383 IF p_hold_entity_code = 'O' THEN
384
385 SELECT 'Valid Entity'
386 INTO l_dummy
387 FROM OE_ORDER_LINES
388 WHERE LINE_ID = p_line_id
389 AND HEADER_ID = p_hold_entity_id
390 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
391
392 ELSIF p_hold_entity_code = 'I' THEN
393
394 SELECT 'Valid Entity'
395 INTO l_dummy
396 FROM OE_ORDER_LINES
397 WHERE LINE_ID = p_line_id
398 AND INVENTORY_ITEM_ID = p_hold_entity_id
399 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
400
401 --ER# 13331078 start
402 ELSIF p_hold_entity_code IN('IC') THEN
403 -- Added Exception Handling through 13895629
404 BEGIN
405 SELECT 'Valid Entity'
406 INTO l_dummy
407 FROM OE_ORDER_LINES l,
408 mtl_item_categories ic
409 WHERE l.LINE_ID = p_line_id
410 AND ic.category_id = p_hold_entity_id
411 AND l.INVENTORY_ITEM_ID = ic.inventory_item_id
412 AND ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
413 AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 oe_debug_pub.add('In No Data Found for 13331078 HEI= '||p_hold_entity_id||' LineId= '||p_line_id);
417 END;
418
419
420 --ER# 13331078 end
421
422 --ER 12571983 start
423 ELSIF p_hold_entity_code IN('EC','EN') THEN
424
425 SELECT 'Valid Entity'
426 INTO l_dummy
427 FROM OE_ORDER_LINES l
428 WHERE l.LINE_ID = p_line_id
429 AND l.end_customer_id =p_hold_entity_id
430 AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
431 --ER 12571983 end
432
433 ELSIF p_hold_entity_code = 'S' THEN
434
435 -- validation data based on bill-to or ship-to site to be inserted here.
436 null;
437
438 --ELSIF p_hold_entity_code = 'C' THEN -- ER# 11824468
439 ELSIF p_hold_entity_code IN ('C','CN') THEN -- ER# 11824468
440 -- XXXXvalidation data based on Customer based holds -- Not needed at the line level
441 null;
442 ELSE
443 -- add error message
444 RAISE FND_API.G_EXC_ERROR;
445 END IF;
446
447 -------------------------------
448 -- Check the Second entity --
449 -------------------------------
450 IF p_hold_entity_code2 = 'O' THEN
451
452 SELECT 'Valid Entity'
453 INTO l_dummy
454 FROM OE_ORDER_LINES
455 WHERE LINE_ID = p_line_id
456 AND HEADER_ID = p_hold_entity_id2
457 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
458
459 ELSIF p_hold_entity_code2 = 'I' THEN
460
461 SELECT 'Valid Entity'
462 INTO l_dummy
463 FROM OE_ORDER_LINES
464 WHERE LINE_ID = p_line_id
465 AND INVENTORY_ITEM_ID = p_hold_entity_id2
466 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
467
468 --ER 12571983 start
469 ELSIF p_hold_entity_code2 IN('EC','EN') THEN
470
471 SELECT 'Valid Entity'
472 INTO l_dummy
473 FROM OE_ORDER_LINES l
474 WHERE l.LINE_ID = p_line_id
475 AND l.end_customer_id =p_hold_entity_id2
476 AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
477
478 ELSIF p_hold_entity_code2 IN('EL') THEN
479
480 SELECT 'Valid Entity'
481 INTO l_dummy
482 FROM OE_ORDER_LINES l
483 WHERE l.LINE_ID = p_line_id
484 AND l.end_customer_id =p_hold_entity_id
485 AND l.end_customer_site_use_id =p_hold_entity_id2
486 AND nvl(l.TRANSACTION_PHASE_CODE,'F') = 'F';
487 --ER 12571983 end
488
489 ELSIF p_hold_entity_code2 = 'S' THEN
490
491 -- validation data based on bill-to or ship-to site to be inserted here.
492 null;
493
494 -- ELSIF p_hold_entity_code2 = 'C' THEN -- ER# 11824468
495 ELSIF p_hold_entity_code2 IN( 'C','CN') THEN -- ER# 11824468
496 -- XXXXvalidation data based on Customer based holds -- Not needed at the line level
497 null;
498 ELSE
499 -- add error message
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502 ------------------------------
503
504 ELSIF p_line_id IS NULL THEN
505
506 IF p_hold_entity_code = 'O' THEN
507 -- XXX
508 IF (p_header_id <> p_hold_entity_id) THEN
509 RAISE FND_API.G_EXC_ERROR;
510 END IF;
511
512 SELECT 'Valid Entity'
513 INTO l_dummy
514 FROM OE_ORDER_HEADERS
515 WHERE HEADER_ID = p_header_id
516 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
517
518
519 -- ELSIF p_hold_entity_code = 'C' THEN -- ER# 11824468
520 ELSIF p_hold_entity_code IN ('C','CN') THEN -- ER# 11824468
521
522 SELECT 'Valid Entity'
523 INTO l_dummy
524 FROM OE_ORDER_HEADERS
525 WHERE HEADER_ID = p_header_id
526 AND SOLD_TO_ORG_ID = p_hold_entity_id
527 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
528
529 ELSIF p_hold_entity_code = 'S' THEN
530 -- XXX Confirm this code
531 SELECT 'Valid Entity'
532 INTO l_dummy
533 FROM OE_ORDER_HEADERS
534 WHERE HEADER_ID = p_header_id
535 AND SHIP_TO_ORG_ID = p_hold_entity_id
536 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
537 ELSE
538 RAISE FND_API.G_EXC_ERROR;
539 END IF;
540 ------------------------------
541 -- Check the Second Entity --
542 ------------------------------
543 IF p_hold_entity_code2 is not null THEN
544 IF p_hold_entity_code2 = 'O' THEN
545
546 IF (p_header_id <> p_hold_entity_id2) THEN
547 RAISE FND_API.G_EXC_ERROR;
548 END IF;
549
550 SELECT 'Valid Entity'
551 INTO l_dummy
552 FROM OE_ORDER_HEADERS
553 WHERE HEADER_ID = p_hold_entity_id2
554 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
555
556
557 --ELSIF p_hold_entity_code2 = 'C' THEN -- ER# 11824468
558 ELSIF p_hold_entity_code2 IN('C','CN') THEN -- ER# 11824468
559
560 SELECT 'Valid Entity'
561 INTO l_dummy
562 FROM OE_ORDER_HEADERS
563 WHERE HEADER_ID = p_header_id
564 AND SOLD_TO_ORG_ID = p_hold_entity_id2
565 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
566
567 ELSIF p_hold_entity_code2 = 'S' THEN
568 -- XXX Confirm this code
569 SELECT 'Valid Entity'
570 INTO l_dummy
571 FROM OE_ORDER_HEADERS
572 WHERE HEADER_ID = p_header_id
573 AND SHIP_TO_ORG_ID = p_hold_entity_id2
574 AND nvl(TRANSACTION_PHASE_CODE,'F') = 'F';
575 ELSE
576 RAISE FND_API.G_EXC_ERROR;
577 END IF;
578 END IF;
579 ------------------------------
580 END IF;
581
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 RAISE FND_API.G_EXC_ERROR;
585 END;
586
587
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTITY_NOT_ON_ORDER_OR_LINE');
591 OE_MSG_PUB.ADD;
592 IF l_debug_level > 0 THEN
593 oe_debug_pub.add( 'EXPECTED ERROR IN VALIDATEORDER' ) ;
594 END IF;
595 x_return_status := FND_API.G_RET_STS_ERROR;
596 WHEN OTHERS THEN
597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
598 IF FND_MSG_PUB.Check_Msg_Level
599 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
600 THEN
601 FND_MSG_PUB.Add_Exc_Msg
602 ( G_PKG_NAME
603 , l_api_name
604 );
605 END IF;
606 END ValidateOrder;
607 -- END OF LOCAL PROCEDURES
608
609
610 ------------------
611 -- APPLY_HOLDS --
612 ------------------
613 -- This is and overloaded procedure that calls the new Holds API
614 ---------------------------------------------------------------
615
616 Procedure Apply_Holds (
617 p_api_version IN NUMBER,
618 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
619 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
620 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
621 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
622 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
623 p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE DEFAULT NULL,
624 p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE DEFAULT NULL,
625 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
626 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
627 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
628 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
629 IS
630 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Holds';
631 --
632 l_org_id number;
633 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
634 --
635 l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
636 BEGIN
637 l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
638 IF l_debug_level > 0 THEN
639 oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS' ) ;
640 END IF;
641
642 SAVEPOINT APPLY_HOLDS_PUB; -- 11803186 Adding a new SAVEPOINT to get the new Apply_Holds proc in synch with the Old
643
644 l_org_id := MO_GLOBAL.get_current_org_id;
645 IF l_org_id IS NULL THEN
646 -- org_id is null, raise an error.
647 oe_debug_pub.add('Org_Id is NULL',1);
648 x_return_status := FND_API.G_RET_STS_ERROR;
649 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
650 FND_MSG_PUB.ADD;
651 RAISE FND_API.G_EXC_ERROR;
652 END IF;
653
654 -- Initialize API return status to success
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656
657 oe_holds_pvt.apply_holds(
658 p_order_tbl => p_order_tbl,
659 p_hold_id => p_hold_id,
660 p_hold_until_date => p_hold_until_date,
661 p_hold_comment => p_hold_comment,
662 p_check_authorization_flag => l_check_authorization_flag, -- 8477694
663 x_return_status => x_return_status,
664 x_msg_count => x_msg_count,
665 x_msg_data => x_msg_data
666 );
667 EXCEPTION
668 WHEN FND_API.G_EXC_ERROR THEN
669 ROLLBACK TO APPLY_HOLDS_PUB;
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
672 END IF;
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 FND_MSG_PUB.Count_And_Get
675 ( p_count => x_msg_count
676 , p_data => x_msg_data
677 );
678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679 ROLLBACK TO APPLY_HOLDS_PUB; --11803186
680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
681 FND_MSG_PUB.Count_And_Get
682 ( p_count => x_msg_count
683 , p_data => x_msg_data
684 );
685 WHEN OTHERS THEN
686 ROLLBACK TO APPLY_HOLDS_PUB; --11803186
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 IF FND_MSG_PUB.Check_Msg_Level
689 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
690 THEN
691 FND_MSG_PUB.Add_Exc_Msg
692 ( G_PKG_NAME
693 , l_api_name
694 );
695 END IF;
696 FND_MSG_PUB.Count_And_Get
697 ( p_count => x_msg_count
698 , p_data => x_msg_data
699 );
700
701 END Apply_Holds;
702
703 Procedure Apply_Holds(
704 p_api_version IN NUMBER,
705 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
706 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
707 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
708 p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
709 DEFAULT OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC,
710 p_hold_existing_flg IN VARCHAR2 DEFAULT 'Y',
711 p_hold_future_flg IN VARCHAR2 DEFAULT 'Y',
712 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
713 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
714 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
715 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
716 IS
717 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Holds';
718 --
719 l_org_id number;
720 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
721 --
722 l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
723 BEGIN
724 l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
725 IF l_debug_level > 0 THEN
726 oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS , CREATING HOLD SOURCE' ) ;
727 END IF;
728 l_org_id := MO_GLOBAL.get_current_org_id;
729 IF l_org_id IS NULL THEN
730 -- org_id is null, raise an error.
731 oe_debug_pub.add('Org_Id is NULL',1);
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
734 FND_MSG_PUB.ADD;
735 RAISE FND_API.G_EXC_ERROR;
736 END IF;
737
738 -- Initialize API return status to success
739 x_return_status := FND_API.G_RET_STS_SUCCESS;
740
741 --dbms_output.put_line ('IN PUB.ApplyHolds'); -- delete
742 oe_holds_pvt.apply_Holds(
743 p_hold_source_rec => p_hold_source_rec
744 ,p_hold_existing_flg => p_hold_existing_flg
745 ,p_hold_future_flg => p_hold_future_flg
746 ,p_check_authorization_flag => l_check_authorization_flag -- 8477694
747 ,x_return_status => x_return_status
748 ,x_msg_count => x_msg_count
749 ,x_msg_data => x_msg_data
750 );
751 IF l_debug_level > 0 THEN
752 oe_debug_pub.add( 'OE_HOLDS_PUB.APPLY_HOLDS , HOLD SOURCE:' ||X_RETURN_STATUS ) ;
753 END IF;
754
755 EXCEPTION
756 WHEN FND_API.G_EXC_ERROR THEN
757 IF l_debug_level > 0 THEN
758 oe_debug_pub.add( 'APPLY HOLD EXPECTED ERROR' ) ;
759 END IF;
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 FND_MSG_PUB.Count_And_Get
762 ( p_count => x_msg_count
763 , p_data => x_msg_data
764 );
765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
767 FND_MSG_PUB.Count_And_Get
768 ( p_count => x_msg_count
769 , p_data => x_msg_data
770 );
771 WHEN OTHERS THEN
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 IF FND_MSG_PUB.Check_Msg_Level
774 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
775 THEN
776 FND_MSG_PUB.Add_Exc_Msg
777 ( G_PKG_NAME
778 , l_api_name
779 );
780 END IF;
781 FND_MSG_PUB.Count_And_Get
782 ( p_count => x_msg_count
783 , p_data => x_msg_data
784 );
785
786 END Apply_Holds;
787
788 ---------------------------------
789 -- New Release Holds Spec --
790 --------------------------------
791 Procedure Release_Holds (
792 p_api_version IN NUMBER DEFAULT 1.0,
793 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
794 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
795 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
796 p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
797 p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
798 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
799 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
800 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
801 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
802 IS
803 l_api_name CONSTANT VARCHAR2(30) := 'release_holds';
804 --
805 l_org_id number;
806 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
807 --
808 l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
809 BEGIN
810 l_check_authorization_flag:=p_check_authorization_flag; --8477694
811 IF l_debug_level > 0 THEN
812 oe_debug_pub.add( 'IN OE_HOLDS_PUB.RELEASE_HOLDS' ) ;
813 END IF;
814 l_org_id := MO_GLOBAL.get_current_org_id;
815 IF l_org_id IS NULL THEN
816 -- org_id is null, raise an error.
817 oe_debug_pub.add('Org_Id is NULL',1);
818 x_return_status := FND_API.G_RET_STS_ERROR;
819 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
820 FND_MSG_PUB.ADD;
821 RAISE FND_API.G_EXC_ERROR;
822 END IF;
823
824 -- Initialize API return status to success
825 x_return_status := FND_API.G_RET_STS_SUCCESS;
826
827 -- dbms_output.put_line ('IN RELEASE_HOLDS->'); -- delete
828 oe_holds_pvt.Release_Holds(
829 p_hold_source_rec => p_hold_source_rec
830 ,p_hold_release_rec => p_hold_release_rec
831 ,p_check_authorization_flag => l_check_authorization_flag -- bug 8477694
832 ,x_return_status => x_return_status
833 ,x_msg_count => x_msg_count
834 ,x_msg_data => x_msg_data
835 );
836 EXCEPTION
837 WHEN FND_API.G_EXC_ERROR THEN
838 IF l_debug_level > 0 THEN
839 oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
840 END IF;
841 x_return_status := FND_API.G_RET_STS_ERROR;
842 FND_MSG_PUB.Count_And_Get
843 ( p_count => x_msg_count
844 , p_data => x_msg_data
845 );
846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
848 FND_MSG_PUB.Count_And_Get
849 ( p_count => x_msg_count
850 , p_data => x_msg_data
851 );
852 WHEN OTHERS THEN
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 IF FND_MSG_PUB.Check_Msg_Level
855 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
856 THEN
857 FND_MSG_PUB.Add_Exc_Msg
858 ( G_PKG_NAME
859 , l_api_name
860 );
861 END IF;
862 FND_MSG_PUB.Count_And_Get
863 ( p_count => x_msg_count
864 , p_data => x_msg_data
865 );
866
867 END Release_Holds;
868
869 Procedure Release_Holds (
870 p_api_version IN NUMBER DEFAULT 1.0,
871 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
872 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
873 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
874 p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
875 p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
876 DEFAULT NULL,
877 p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE,
878 p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE
879 DEFAULT NULL,
880 p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
881 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
882 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
883 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
884 IS
885 l_api_name CONSTANT VARCHAR2(30) := 'Release_holds';
886 l_header_id NUMBER DEFAULT NULL;
887 j NUMBER;
888 l_order_tbl OE_HOLDS_PVT.order_tbl_type;
889 l_org_id number;
890 --
891 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
892 --
893 l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
894 BEGIN
895 l_check_authorization_flag :=p_check_authorization_flag; -- bug 8477694
896 IF l_debug_level > 0 THEN
897 oe_debug_pub.add( 'IN OE_HOLDS_PUB.RELEASE_HOLDS' ) ;
898 END IF;
899
900 l_org_id := MO_GLOBAL.get_current_org_id;
901 IF l_org_id IS NULL THEN
902 -- org_id is null, raise an error.
903 oe_debug_pub.add('Org_Id is NULL',1);
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
906 FND_MSG_PUB.ADD;
907 RAISE FND_API.G_EXC_ERROR;
908 END IF;
909
910 -- Initialize API return status to success
911 x_return_status := FND_API.G_RET_STS_SUCCESS;
912
913 for j in 1..p_order_tbl.COUNT loop
914 if p_order_tbl(j).header_id is NULL AND
915 p_order_tbl(j).line_id is NULL THEN
916 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
917 OE_MSG_PUB.ADD;
918 RAISE FND_API.G_EXC_ERROR;
919 ELSIF p_order_tbl(j).header_id is NULL THEN
920 SELECT header_id
921 INTO l_header_id
922 FROM OE_ORDER_LINES
923 WHERE LINE_ID = p_order_tbl(j).line_id;
924 l_order_tbl(j).header_id := l_header_id;
925 l_order_tbl(j).line_id := p_order_tbl(j).line_id;
926 ELSE
927 l_order_tbl(j).header_id := p_order_tbl(j).header_id;
928 l_order_tbl(j).line_id := p_order_tbl(j).line_id;
929 END IF;
930 end loop;
931
932 oe_holds_pvt.release_holds(
933 p_order_tbl => l_order_tbl,
934 p_hold_id => p_hold_id,
935 p_release_reason_code => p_release_reason_code,
936 p_release_comment => p_release_comment,
937 p_check_authorization_flag => l_check_authorization_flag, -- bug 8477694
938 x_return_status => x_return_status,
939 x_msg_count => x_msg_count,
940 x_msg_data => x_msg_data
941 );
942
943 EXCEPTION
944 WHEN FND_API.G_EXC_ERROR THEN
945 IF l_debug_level > 0 THEN
946 oe_debug_pub.add( 'RELEASE HOLD EXPECTED ERROR' ) ;
947 END IF;
948 x_return_status := FND_API.G_RET_STS_ERROR;
949 FND_MSG_PUB.Count_And_Get
950 ( p_count => x_msg_count
951 , p_data => x_msg_data
952 );
953 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
955 FND_MSG_PUB.Count_And_Get
956 ( p_count => x_msg_count
957 , p_data => x_msg_data
958 );
959 WHEN OTHERS THEN
960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
961 IF FND_MSG_PUB.Check_Msg_Level
962 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
963 THEN
964 FND_MSG_PUB.Add_Exc_Msg
965 ( G_PKG_NAME
966 , l_api_name
967 );
968 END IF;
969 FND_MSG_PUB.Count_And_Get
970 ( p_count => x_msg_count
971 , p_data => x_msg_data
972 );
973
974 END Release_Holds;
975
976
977 --------------------------------------------------------------------------
978 -- APPLY HOLDS
979 -- This procedure can be used to apply holds for the following two cases:
980 -- 1. Hold Source has already been created. Pass just the hold source ID
981 -- (p_hold_source_id) and use that hold source to place the order
982 -- (p_header_id) or the order line (p_line_id) on hold.
983 -- 2. Check if the hold source exists (p_hold_id, p_entity_code,
984 -- p_entity_id should be passed). If it exists, use that hold source to
985 -- place the hold . If it doesn't, create a new hold source and
986 -- then put the order or line on hold.
987 -- Note: Leaving this call for backward compatibility. AR's Customer form
988 -- still calls the old oe_holds (OEXOHAPB.pls) which in turns calls
989 -- this api.
990 --------------------------------------------------------------------------
991
992 PROCEDURE Apply_Holds
993 ( p_api_version IN NUMBER
994 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
995 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
996 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
997 , p_header_id IN NUMBER DEFAULT NULL
998 , p_line_id IN NUMBER DEFAULT NULL
999 , p_hold_source_id IN NUMBER DEFAULT NULL
1000 , p_hold_source_rec IN OE_Hold_Sources_Pvt.Hold_Source_REC
1001 DEFAULT OE_Hold_Sources_Pvt.G_MISS_Hold_Source_REC
1002 , p_check_authorization_flag IN VARCHAR2 DEFAULT 'N' -- bug 8477694
1003 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1004 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1005 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1006 )
1007 IS
1008 l_api_version CONSTANT NUMBER := 1.0;
1009 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_HOLDS';
1010 l_user_id NUMBER;
1011 l_hold_source_id NUMBER := 0;
1012 l_dummy VARCHAR2(30);
1013 l_order_holds_s NUMBER := 0;
1014 --ER#7479609 l_entity_code VARCHAR2(1);
1015 l_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
1016 l_entity_id NUMBER;
1017 --
1018 --ER#7479609 l_entity_code2 VARCHAR2(1);
1019 l_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
1020 l_entity_id2 NUMBER;
1021 l_header_id NUMBER DEFAULT NULL;
1022
1023 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
1024 l_site_use_code VARCHAR2(30);
1025 l_org_id number;
1026 --
1027 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1028 --
1029 l_check_authorization_flag varchar2(1):= 'N'; -- bug 8477694
1030 BEGIN
1031 l_check_authorization_flag:= p_check_authorization_flag; -- 8477694
1032 l_org_id := MO_GLOBAL.get_current_org_id;
1033 IF l_org_id IS NULL THEN
1034 -- org_id is null, raise an error.
1035 oe_debug_pub.add('Org_Id is NULL',1);
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
1038 FND_MSG_PUB.ADD;
1039 RAISE FND_API.G_EXC_ERROR;
1040 END IF;
1041
1042 IF l_debug_level > 0 THEN
1043 oe_debug_pub.add( 'IN OE_HOLDS_PUB.APPLY_HOLDS OLD' ) ;
1044 END IF;
1045
1046 SAVEPOINT APPLY_HOLDS_PUB;
1047
1048 -- Initialize API return status to success
1049 x_return_status := FND_API.G_RET_STS_SUCCESS;
1050
1051 Utilities(l_user_id);
1052
1053
1054 ----------------------------------------------------------------
1055 -- CASE I: Hold Source ID is KNOWN
1056 ----------------------------------------------------------------
1057 IF p_hold_source_id IS NOT NULL THEN
1058
1059 IF l_debug_level > 0 THEN
1060 oe_debug_pub.add( 'USING INPUT HOLD SOURCE ID' ) ;
1061 END IF;
1062 l_hold_source_id := p_hold_source_id;
1063
1064 --IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
1065
1066 -- Check if the hold source ID is valid
1067 BEGIN
1068
1069 SELECT HOLD_ENTITY_CODE, HOLD_ENTITY_ID,
1070 HOLD_ENTITY_CODE2, HOLD_ENTITY_ID2
1071 INTO l_entity_code, l_entity_id,
1072 l_entity_code2, l_entity_id2
1073 FROM OE_HOLD_SOURCES
1074 WHERE HOLD_SOURCE_ID = p_hold_source_id
1075 AND RELEASED_FLAG = 'N'
1076 AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
1077
1078 EXCEPTION
1079 WHEN NO_DATA_FOUND THEN
1080 IF l_debug_level > 0 THEN
1081 oe_debug_pub.add( 'INVALID HOLD SOURCE ID' ) ;
1082 END IF;
1083 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_HOLD_SOURCE_ID');
1084 FND_MESSAGE.SET_TOKEN('HOLD_SOURCE_ID' , p_hold_source_id);
1085 OE_MSG_PUB.ADD;
1086 RAISE FND_API.G_EXC_ERROR;
1087 END;
1088
1089 -- END IF;
1090
1091 -------------------------------------------------------------
1092 -- CASE II: Hold Source ID is NOT AVAILABLE
1093 -------------------------------------------------------------
1094 ELSE
1095 -- Check for Missing Values
1096 IF p_hold_source_rec.hold_id IS NULL THEN
1097 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_ID');
1098 OE_MSG_PUB.ADD;
1099 RAISE FND_API.G_EXC_ERROR;
1100 END IF;
1101
1102 IF p_hold_source_rec.hold_entity_code IS NULL THEN
1103 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
1104 OE_MSG_PUB.ADD;
1105 RAISE FND_API.G_EXC_ERROR;
1106 END IF;
1107
1108 IF p_hold_source_rec.hold_entity_id IS NULL THEN
1109 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
1110 OE_MSG_PUB.ADD;
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113 -- NOTE: No need to check for HOLD_ENTITY_CODE2 and HOLD_ENTITY_ID2
1114 -- cos, its optional.
1115 ----------------------------------
1116 -- Check to see if the Site code is Bill_to OR Ship_to
1117 -- ONLY needed coz AR still calls the old holds api (oe_holds) with
1118 -- S as hold_entity_code for Bill To.
1119 if p_hold_source_rec.hold_entity_code = 'S' THEN
1120 IF l_debug_level > 0 THEN
1121 oe_debug_pub.add( 'CHECKING FOR SITE CODE , BILL TO OR SHIP TO' , 1 ) ;
1122 END IF;
1123 l_entity_code := Hold_Site_Code(p_hold_source_rec.hold_entity_id);
1124 ELSE
1125 l_entity_code := p_hold_source_rec.hold_entity_code;
1126 END IF;
1127 l_entity_id := p_hold_source_rec.hold_entity_id;
1128
1129 END IF; -- END of check to see WHETHER HOLD SOURCE ID is passed.
1130 /*
1131 IF p_hold_source_rec.hold_entity_code = 'O' THEN
1132 IF p_line_id IS NULL AND p_header_id IS NULL THEN
1133 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
1134 OE_MSG_PUB.ADD;
1135 RAISE FND_API.G_EXC_ERROR;
1136 ELSIF p_header_id IS NULL THEN
1137 SELECT header_id
1138 INTO l_header_id
1139 FROM OE_ORDER_LINES
1140 WHERE LINE_ID = p_line_id;
1141 ELSE
1142 l_header_id := p_header_id;
1143 END IF;
1144 END IF; -- p_hold_source_rec.hold_entity_code = 'O'
1145 */
1146
1147 /* IF l_hold_source_id <> 0 THEN
1148 -- Check for duplicate hold
1149 BEGIN
1150 SELECT 'Duplicate Hold'
1151 INTO l_dummy
1152 FROM OE_ORDER_HOLDS
1153 WHERE hold_source_id = l_hold_source_id
1154 AND HEADER_ID = l_header_id
1155 AND NVL(LINE_ID, NVL(p_line_id,0)) = NVL(p_line_id, 0)
1156 AND HOLD_RELEASE_ID IS NULL
1157 AND ROWNUM = 1;
1158
1159 IF (sql%found) THEN
1160 FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
1161 OE_MSG_PUB.ADD;
1162 OE_Debug_PUB.Add('Duplicate Hold');
1163 RAISE FND_API.G_EXC_ERROR;
1164 END IF;
1165 EXCEPTION
1166 WHEN NO_DATA_FOUND THEN
1167 null;
1168 END;
1169 END IF; -- l_hold_source_id <> 0
1170 */
1171 /*
1172 IF p_hold_source_rec.hold_entity_code = 'O' THEN
1173 ValidateOrder(p_header_id => p_header_id
1174 , p_line_id => p_line_id
1175 , p_hold_entity_code => l_entity_code
1176 , p_hold_entity_id => l_entity_id
1177 , p_hold_entity_code2 => l_entity_code2
1178 , p_hold_entity_id2 => l_entity_id2
1179 , x_return_status => x_return_status
1180 );
1181
1182 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1183 OE_Debug_PUB.Add('Validate Order not successful');
1184 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1185 RAISE FND_API.G_EXC_ERROR;
1186 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1188 END IF;
1189 ELSE
1190 OE_Debug_PUB.Add('Validate Order successful');
1191 END IF;
1192 END IF; -- p_hold_source_rec.hold_entity_code = 'O'
1193 */
1194
1195 l_hold_source_rec.hold_entity_code := l_entity_code;
1196 l_hold_source_rec.hold_entity_id := l_entity_id;
1197 l_hold_source_rec.hold_entity_code2 := p_hold_source_rec.hold_entity_code2;
1198 l_hold_source_rec.hold_entity_id2 := p_hold_source_rec.hold_entity_id2;
1199 l_hold_source_rec.hold_id := p_hold_source_rec.hold_id;
1200
1201 /* In case the p_header_id and p_line_id is not null. This will only be the case
1202 if the old holds api (oe_holds) is being called to apply a header or line
1203 level hold. These are part of hold_source_rec npw.
1204 REMOVE this after it has been verified. */
1205 IF p_hold_source_rec.hold_entity_code = 'O' THEN
1206 IF p_header_id IS NOT NULL THEN
1207 l_hold_source_rec.header_id := p_header_id;
1208 ELSIF p_line_id IS NOT NULL THEN
1209 l_hold_source_rec.line_id := p_line_id;
1210 END IF;
1211 END IF;
1212
1213
1214 --dbms_output.put_line ('AH-hold_id' ||to_char(p_hold_source_rec.hold_id)); -- delete
1215 --dbms_output.put_line ('AH-B4ApplyHolds' ); -- delete
1216 IF l_debug_level > 0 THEN
1217 oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.APPLY_HOLDS' ) ;
1218 END IF;
1219 oe_holds_pvt.apply_Holds(
1220 p_hold_source_rec => l_hold_source_rec
1221 ,p_hold_existing_flg => 'Y'
1222 ,p_hold_future_flg => 'Y'
1223 ,p_check_authorization_flag => l_check_authorization_flag -- 8477694
1224 ,x_return_status => x_return_status
1225 ,x_msg_count => x_msg_count
1226 ,x_msg_data => x_msg_data
1227 );
1228 IF l_debug_level > 0 THEN
1229 oe_debug_pub.add( 'OE_HOLDS_PVT.APPLY_HOLDS STATUS:' || X_RETURN_STATUS ) ;
1230 END IF;
1231 --dbms_output.put_line('AH-x_return_status' || x_return_status ); -- delete
1232 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1233 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1234 RAISE FND_API.G_EXC_ERROR;
1235 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238 END IF;
1239
1240 EXCEPTION
1241 WHEN FND_API.G_EXC_ERROR THEN
1242 ROLLBACK TO APPLY_HOLDS_PUB;
1243 IF l_debug_level > 0 THEN
1244 oe_debug_pub.add( 'FROM DUPLICATE HOLD EXPECTED ERROR' ) ;
1245 END IF;
1246 x_return_status := FND_API.G_RET_STS_ERROR;
1247 FND_MSG_PUB.Count_And_Get
1248 ( p_count => x_msg_count
1249 , p_data => x_msg_data
1250 );
1251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1252 ROLLBACK TO APPLY_HOLDS_PUB;
1253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1254 FND_MSG_PUB.Count_And_Get
1255 ( p_count => x_msg_count
1256 , p_data => x_msg_data
1257 );
1258 WHEN OTHERS THEN
1259 ROLLBACK TO APPLY_HOLDS_PUB;
1260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1261 IF FND_MSG_PUB.Check_Msg_Level
1262 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1263 THEN
1264 FND_MSG_PUB.Add_Exc_Msg
1265 ( G_PKG_NAME
1266 , l_api_name
1267 );
1268 END IF;
1269 FND_MSG_PUB.Count_And_Get
1270 ( p_count => x_msg_count
1271 , p_data => x_msg_data
1272 );
1273 END Apply_Holds;
1274
1275 /**************************************************************/
1276
1277 /* An additional parameter 'p_hdr_id' was passed to this procedure.
1278 This was to improve the performance of a query in this procedure.
1279 Refer Bug1920064.
1280 */
1281
1282 PROCEDURE Check_Holds_line (
1283 p_hdr_id IN NUMBER
1284 , p_line_id IN NUMBER DEFAULT NULL
1285 , p_hold_id IN NUMBER DEFAULT NULL
1286 , p_wf_item IN VARCHAR2 DEFAULT NULL
1287 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1288 , p_entity_code IN VARCHAR2 DEFAULT NULL
1289 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1290 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1291 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1292 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1293 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1294 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1295 , p_ii_parent_flag IN VARCHAR2 DEFAULT 'N'
1296 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1297 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1298 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1299 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1300 )
1301 IS
1302 l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_line';
1303 l_dummy VARCHAR2(30);
1304 --
1305 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1306 --
1307 BEGIN
1308 IF l_debug_level > 0 THEN
1309 oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_LINE:' || P_LINE_ID ) ;
1310 END IF;
1311 -- Initialize API return status to success
1312 x_return_status := FND_API.G_RET_STS_SUCCESS;
1313
1314 -- Initialize result to TRUE i.e. holds are found
1315 x_result_out := FND_API.G_TRUE;
1316
1317 /*
1318 ** Fix Bug # 1920064
1319 ** The SQL below was modified. To improve the performance, the scan on
1320 ** the table OE_ORDER_LINES_ALL was avoided as the header_id was being
1321 ** passed as a parameter to this procedure. The UNION was removed and
1322 ** the whole SQL was re-written as below.
1323 ** Fix Bug # 2984023
1324 ** Modified the query to check for only line level holds
1325 */
1326
1327 /*
1328 ** Checking for LINE level generic and activity-specific holds
1329 */
1330 BEGIN
1331
1332 SELECT 'ANY_LINE_HOLD'
1333 INTO l_dummy
1334 FROM oe_order_holds_all oh
1335 WHERE oh.header_id = p_hdr_id
1336 and oh.line_id = p_line_id
1337 and oh.hold_release_id is null
1338 AND EXISTS
1339 (SELECT 1
1340 FROM oe_hold_sources_all hs,
1341 oe_hold_definitions h
1342 WHERE oh.hold_source_id = hs.hold_source_id
1343 AND hs.hold_id = h.hold_id
1344 AND NVL(h.item_type,
1345 DECODE(p_chk_act_hold_only,
1346 'Y', 'XXXXX',
1347 NVL(p_wf_item, 'NO ITEM')) ) =
1348 NVL(p_wf_item, 'NO ITEM')
1349 AND NVL(H.ACTIVITY_NAME,
1350 DECODE(p_chk_act_hold_only,
1351 'Y', 'XXXXX',
1352 NVL(p_wf_activity, 'NO ACT')) ) =
1353 NVL(p_wf_activity,'NO ACT')
1354 AND DECODE(p_ii_parent_flag, 'Y',
1355 nvl(h.hold_included_items_flag, 'N'), 'XXXXX') =
1356 DECODE(p_ii_parent_flag, 'Y', 'Y', 'XXXXX')
1357 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1358 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1359 AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
1360 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1361 ROUND( SYSDATE )
1362 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1363 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1364 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1365 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1366 AND NVL(hs.hold_entity_id2, -99) =
1367 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1368
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN
1371 x_result_out := FND_API.G_FALSE;
1372 IF l_debug_level > 0 THEN
1373 oe_debug_pub.add( 'NO HOLDS FOUND FOR LINE ID: ' || P_LINE_ID ) ;
1374 END IF;
1375 WHEN TOO_MANY_ROWS THEN
1376 null;
1377 END;
1378
1379 IF l_debug_level > 0 THEN
1380 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_LINE' ) ;
1381 END IF;
1382
1383 EXCEPTION
1384 WHEN FND_API.G_EXC_ERROR THEN
1385 x_return_status := FND_API.G_RET_STS_ERROR;
1386 x_result_out := FND_API.G_FALSE;
1387 FND_MSG_PUB.Count_And_Get
1388 ( p_count => x_msg_count
1389 , p_data => x_msg_data
1390 );
1391 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1393 x_result_out := FND_API.G_FALSE;
1394 FND_MSG_PUB.Count_And_Get
1395 ( p_count => x_msg_count
1396 , p_data => x_msg_data
1397 );
1398 WHEN OTHERS THEN
1399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1400 x_result_out := FND_API.G_FALSE;
1401 IF FND_MSG_PUB.Check_Msg_Level
1402 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1403 THEN
1404 FND_MSG_PUB.Add_Exc_Msg
1405 ( G_PKG_NAME
1406 , l_api_name
1407 );
1408 END IF;
1409 FND_MSG_PUB.Count_And_Get
1410 ( p_count => x_msg_count
1411 , p_data => x_msg_data
1412 );
1413
1414 END CHECK_HOLDS_LINE;
1415
1416 /*
1417 ** Procedure to check holds on ANY line part of ATO Model.
1418 */
1419 PROCEDURE Check_Holds_ATO (
1420 p_hdr_id IN NUMBER
1421 , p_ato_line_id IN NUMBER DEFAULT NULL
1422 , p_top_model_line_id IN NUMBER DEFAULT NULL
1423 , p_hold_id IN NUMBER DEFAULT NULL
1424 , p_wf_item IN VARCHAR2 DEFAULT NULL
1425 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1426 , p_entity_code IN VARCHAR2 DEFAULT NULL
1427 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1428 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1429 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1430 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1431 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1432 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1433 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1434 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1435 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1436 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1437 )
1438 IS
1439 l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_ATO';
1440 l_dummy VARCHAR2(30);
1441 --
1442 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1443 --
1444 BEGIN
1445 IF l_debug_level > 0 THEN
1446 oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1447 END IF;
1448 -- Initialize API return status to success
1449 x_return_status := FND_API.G_RET_STS_SUCCESS;
1450
1451 -- Initialize result to TRUE i.e. holds are found
1452 x_result_out := FND_API.G_TRUE;
1453
1454 /*
1455 ** Checking for ATO level generic and activity-specific holds
1456 */
1457 BEGIN
1458
1459 SELECT /* MOAC_SQL_CHANGE */ 'ANY_ATO_LINE_HOLD'
1460 INTO l_dummy
1461 FROM oe_order_holds_all oh
1462 WHERE oh.header_id = p_hdr_id
1463 and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1464 where ol.header_id = oh.header_id
1465 and ol.ato_line_id = p_ato_line_id
1466 and ol.top_model_line_id = p_top_model_line_id)
1467 and oh.hold_release_id is null
1468 AND EXISTS
1469 (SELECT 1
1470 FROM oe_hold_sources_all hs,
1471 oe_hold_definitions h
1472 WHERE oh.hold_source_id = hs.hold_source_id
1473 AND hs.hold_id = h.hold_id
1474 AND NVL(h.item_type,
1475 DECODE(p_chk_act_hold_only,
1476 'Y', 'XXXXX',
1477 NVL(p_wf_item, 'NO ITEM')) ) =
1478 NVL(p_wf_item, 'NO ITEM')
1479 AND NVL(H.ACTIVITY_NAME,
1480 DECODE(p_chk_act_hold_only,
1481 'Y', 'XXXXX',
1482 NVL(p_wf_activity, 'NO ACT')) ) =
1483 NVL(p_wf_activity,'NO ACT')
1484 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1485 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1486 AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
1487 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1488 ROUND( SYSDATE )
1489 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1490 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1491 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1492 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1493 AND NVL(hs.hold_entity_id2, -99) =
1494 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1495
1496 EXCEPTION
1497 WHEN NO_DATA_FOUND THEN
1498 x_result_out := FND_API.G_FALSE;
1499 IF l_debug_level > 0 THEN
1500 oe_debug_pub.add( 'NO HOLDS FOUND FOR ATO LINE ID: ' || P_ATO_LINE_ID ) ;
1501 END IF;
1502 WHEN TOO_MANY_ROWS THEN
1503 null;
1504 END;
1505
1506 IF l_debug_level > 0 THEN
1507 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_ATO' ) ;
1508 END IF;
1509
1510 EXCEPTION
1511 WHEN FND_API.G_EXC_ERROR THEN
1512 x_return_status := FND_API.G_RET_STS_ERROR;
1513 x_result_out := FND_API.G_FALSE;
1514 FND_MSG_PUB.Count_And_Get
1515 ( p_count => x_msg_count
1516 , p_data => x_msg_data
1517 );
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 x_result_out := FND_API.G_FALSE;
1521 FND_MSG_PUB.Count_And_Get
1522 ( p_count => x_msg_count
1523 , p_data => x_msg_data
1524 );
1525 WHEN OTHERS THEN
1526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527 x_result_out := FND_API.G_FALSE;
1528 IF FND_MSG_PUB.Check_Msg_Level
1529 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1530 THEN
1531 FND_MSG_PUB.Add_Exc_Msg
1532 ( G_PKG_NAME
1533 , l_api_name
1534 );
1535 END IF;
1536 FND_MSG_PUB.Count_And_Get
1537 ( p_count => x_msg_count
1538 , p_data => x_msg_data
1539 );
1540
1541 END CHECK_HOLDS_ATO;
1542
1543 /*
1544 ** Procedure to check holds on ANY line part of an SMC.
1545 */
1546
1547 PROCEDURE Check_Holds_SMC (
1548 p_hdr_id IN NUMBER
1549 , p_top_model_line_id IN NUMBER DEFAULT NULL
1550 , p_hold_id IN NUMBER DEFAULT NULL
1551 , p_wf_item IN VARCHAR2 DEFAULT NULL
1552 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1553 , p_entity_code IN VARCHAR2 DEFAULT NULL
1554 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1555 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1556 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1557 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1558 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1559 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1560 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1561 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1562 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1563 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1564 )
1565 IS
1566 l_api_name CONSTANT VARCHAR2(30) := 'Check_Holds_SMC';
1567 l_dummy VARCHAR2(30);
1568 --
1569 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1570 --
1571 BEGIN
1572 IF l_debug_level > 0 THEN
1573 oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1574 END IF;
1575 -- Initialize API return status to success
1576 x_return_status := FND_API.G_RET_STS_SUCCESS;
1577
1578 -- Initialize result to TRUE i.e. holds are found
1579 x_result_out := FND_API.G_TRUE;
1580
1581 /*
1582 ** Checking for SMC level generic and activity-specific holds
1583 */
1584 BEGIN
1585
1586 SELECT /* MOAC_SQL_CHANGE */ 'ANY_SMC_LINE_HOLD'
1587 INTO l_dummy
1588 FROM oe_order_holds_all oh
1589 WHERE oh.header_id = p_hdr_id
1590 and oh.line_id in (select ol.line_id from oe_order_lines_all ol
1591 where ol.header_id = oh.header_id
1592 and ol.top_model_line_id = p_top_model_line_id)
1593 and oh.hold_release_id is null
1594 AND EXISTS
1595 (SELECT 1
1596 FROM oe_hold_sources_all hs,
1597 oe_hold_definitions h
1598 WHERE oh.hold_source_id = hs.hold_source_id
1599 AND hs.hold_id = h.hold_id
1600 AND NVL(h.item_type,
1601 DECODE(p_chk_act_hold_only,
1602 'Y', 'XXXXX',
1603 NVL(p_wf_item, 'NO ITEM')) ) =
1604 NVL(p_wf_item, 'NO ITEM')
1605 AND NVL(H.ACTIVITY_NAME,
1606 DECODE(p_chk_act_hold_only,
1607 'Y', 'XXXXX',
1608 NVL(p_wf_activity, 'NO ACT')) ) =
1609 NVL(p_wf_activity,'NO ACT')
1610 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1611 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1612 AND HS.HOLD_ID = NVL(p_hold_id,HS.HOLD_ID)
1613 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1614 ROUND( SYSDATE )
1615 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1616 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1617 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1618 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1619 AND NVL(hs.hold_entity_id2, -99) =
1620 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1621
1622 EXCEPTION
1623 WHEN NO_DATA_FOUND THEN
1624 x_result_out := FND_API.G_FALSE;
1625 IF l_debug_level > 0 THEN
1626 oe_debug_pub.add( 'NO HOLDS FOUND FOR TOP MODEL LINE ID: ' || P_TOP_MODEL_LINE_ID ) ;
1627 END IF;
1628 WHEN TOO_MANY_ROWS THEN
1629 null;
1630 END;
1631
1632 IF l_debug_level > 0 THEN
1633 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS_SMC' ) ;
1634 END IF;
1635
1636 EXCEPTION
1637 WHEN FND_API.G_EXC_ERROR THEN
1638 x_return_status := FND_API.G_RET_STS_ERROR;
1639 x_result_out := FND_API.G_FALSE;
1640 FND_MSG_PUB.Count_And_Get
1641 ( p_count => x_msg_count
1642 , p_data => x_msg_data
1643 );
1644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1646 x_result_out := FND_API.G_FALSE;
1647 FND_MSG_PUB.Count_And_Get
1648 ( p_count => x_msg_count
1649 , p_data => x_msg_data
1650 );
1651 WHEN OTHERS THEN
1652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1653 x_result_out := FND_API.G_FALSE;
1654 IF FND_MSG_PUB.Check_Msg_Level
1655 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1656 THEN
1657 FND_MSG_PUB.Add_Exc_Msg
1658 ( G_PKG_NAME
1659 , l_api_name
1660 );
1661 END IF;
1662 FND_MSG_PUB.Count_And_Get
1663 ( p_count => x_msg_count
1664 , p_data => x_msg_data
1665 );
1666
1667 END CHECK_HOLDS_SMC;
1668
1669 /**************************************************************************/
1670
1671 -- Created for bug 2673236
1672 -- Check If any line in the order is on hold
1673
1674 PROCEDURE Check_Any_Line_Hold (
1675 x_hold_rec IN OUT NOCOPY OE_HOLDS_PUB.Any_Line_Hold_rec
1676 , x_return_status OUT NOCOPY VARCHAR2
1677 , x_msg_count OUT NOCOPY NUMBER
1678 , x_msg_data OUT NOCOPY VARCHAR2
1679 )
1680 IS
1681 l_api_name CONSTANT VARCHAR2(30) := 'Check_Any_Line_Hold';
1682 l_dummy VARCHAR2(30);
1683 --
1684 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1685 --
1686 BEGIN
1687 -- Initialize API return status to success
1688 x_return_status := FND_API.G_RET_STS_SUCCESS;
1689
1690 -- Initialize result to TRUE i.e. holds are found
1691 x_hold_rec.x_result_out := FND_API.G_TRUE;
1692
1693 BEGIN
1694
1695 SELECT 'ANY_LINE_HOLD'
1696 INTO l_dummy
1697 FROM oe_order_holds_all oh
1698 WHERE oh.header_id = x_hold_rec.header_id
1699 and OH.LINE_ID is not null
1700 and OH.HOLD_RELEASE_ID IS NULL
1701 and ROWNUM = 1
1702 AND EXISTS
1703 (SELECT 1
1704 FROM oe_hold_sources_all hs,
1705 oe_hold_definitions h
1706 WHERE oh.hold_source_id = hs.hold_source_id
1707 AND hs.hold_id = h.hold_id
1708 AND NVL(h.item_type,
1709 DECODE(x_hold_rec.p_chk_act_hold_only,
1710 'Y', 'XXXXX',
1711 NVL(x_hold_rec.wf_item_type, 'NO ITEM')) ) =
1712 NVL(x_hold_rec.wf_item_type, 'NO ITEM')
1713 AND NVL(H.ACTIVITY_NAME,
1714 DECODE(x_hold_rec.p_chk_act_hold_only,
1715 'Y', 'XXXXX',
1716 NVL(x_hold_rec.wf_activity_name, 'NO ACT')) ) =
1717 NVL(x_hold_rec.wf_activity_name,'NO ACT')
1718 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1719 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1720 AND HS.HOLD_ID = NVL(x_hold_rec.hold_id,HS.HOLD_ID)
1721 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1722 ROUND( SYSDATE )
1723 AND hs.hold_entity_code = NVL(x_hold_rec.hold_entity_code, hs.hold_entity_code)
1724 AND hs.hold_entity_id = NVL(x_hold_rec.hold_entity_id, hs.hold_entity_id)
1725 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1726 NVL(x_hold_rec.hold_entity_code2, NVL(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1727 AND NVL(hs.hold_entity_id2, -99) =
1728 NVL(x_hold_rec.hold_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1729 EXCEPTION
1730 WHEN NO_DATA_FOUND THEN
1731 x_hold_rec.x_result_out := FND_API.G_FALSE;
1732 IF l_debug_level > 0 THEN
1733 oe_debug_pub.add( 'NO HOLDS FOUND FOR ANY OF THE LINES' ) ;
1734 END IF;
1735 WHEN TOO_MANY_ROWS THEN
1736 null;
1737 END;
1738
1739
1740 EXCEPTION
1741 WHEN FND_API.G_EXC_ERROR THEN
1742 x_return_status := FND_API.G_RET_STS_ERROR;
1743 x_hold_rec.x_result_out := FND_API.G_FALSE;
1744 FND_MSG_PUB.Count_And_Get
1745 ( p_count => x_msg_count
1746 , p_data => x_msg_data
1747 );
1748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1750 x_hold_rec.x_result_out := FND_API.G_FALSE;
1751 FND_MSG_PUB.Count_And_Get
1752 ( p_count => x_msg_count
1753 , p_data => x_msg_data
1754 );
1755 WHEN OTHERS THEN
1756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1757 x_hold_rec.x_result_out := FND_API.G_FALSE;
1758 IF FND_MSG_PUB.Check_Msg_Level
1759 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1760 THEN
1761 FND_MSG_PUB.Add_Exc_Msg
1762 ( G_PKG_NAME
1763 , l_api_name
1764 );
1765 END IF;
1766 FND_MSG_PUB.Count_And_Get
1767 ( p_count => x_msg_count
1768 , p_data => x_msg_data
1769 );
1770
1771 END Check_Any_Line_Hold;
1772
1773 /**************************************************************/
1774 -----------------------------------------------------------------------
1775 -- Check_Holds
1776 --
1777 -- Checks if there are any holds on the order or order line. If
1778 -- order line, then checks for holds on the order that it belongs to.
1779 -- If ATO line, then checks for holds on other lines belonging to the
1780 -- same ATO model. If SMC line, then checks for other lines in the SMC.
1781 -- If included item line then checks for hold on its immediate parent
1782 -- if included item flag is set appropriately in the hold definition.
1783 ------------------------------------------------------------------------
1784 PROCEDURE Check_Holds
1785 ( p_api_version IN NUMBER
1786 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1787 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1788 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1789 , p_header_id IN NUMBER DEFAULT NULL
1790 , p_line_id IN NUMBER DEFAULT NULL
1791 , p_hold_id IN NUMBER DEFAULT NULL
1792 , p_wf_item IN VARCHAR2 DEFAULT NULL
1793 , p_wf_activity IN VARCHAR2 DEFAULT NULL
1794 , p_entity_code IN VARCHAR2 DEFAULT NULL
1795 --ER#7479609, p_entity_id IN NUMBER DEFAULT NULL
1796 , p_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
1797 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
1798 --ER#7479609, p_entity_id2 IN NUMBER DEFAULT NULL
1799 , p_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
1800 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
1801 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1802 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1803 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1804 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1805 )
1806 IS
1807 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_HOLDS';
1808 l_api_version CONSTANT NUMBER := 1.0;
1809 l_dummy VARCHAR2(30);
1810
1811 l_line_id NUMBER;
1812 l_ato_line_id NUMBER;
1813 l_top_model_line_id NUMBER;
1814 l_smc_flag VARCHAR2(1);
1815 l_item_type_code VARCHAR2(30);
1816 l_link_to_line_id NUMBER;
1817
1818 l_return_status VARCHAR2(30);
1819 l_msg_count NUMBER;
1820 l_msg_data VARCHAR2(2000);
1821 p_hdr_id NUMBER;
1822
1823 CURSOR ato_model_lines IS
1824 select line_id
1825 from oe_order_lines_all
1826 where ato_line_id = l_ato_line_id
1827 and top_model_line_id = l_top_model_line_id;
1828
1829 CURSOR smc_lines IS
1830 select line_id
1831 from oe_order_lines_all
1832 where top_model_line_id = l_top_model_line_id;
1833 --
1834 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1835 --
1836 --7832836 l_cascade_hold_non_smc VARCHAR2(1) := NVL(OE_SYS_PARAMETERS.VALUE('ONT_CASCADE_HOLD_NONSMC_PTO'),'N'); --ER#7479609
1837 l_cascade_hold_non_smc VARCHAR2(1); -- 7832836
1838 l_org_id NUMBER; -- 7832836
1839 BEGIN
1840 IF l_debug_level > 0 THEN
1841 oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLDS' ) ;
1842 END IF;
1843
1844 -- Initialize API return status to success
1845 x_return_status := FND_API.G_RET_STS_SUCCESS;
1846
1847 -- Initialize result to TRUE i.e. holds are found
1848 x_result_out := FND_API.G_TRUE;
1849
1850 -- Check for Missing Input Parameters
1851 IF p_header_id IS NULL AND p_line_id IS NULL THEN
1852
1853 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
1854 OE_MSG_PUB.ADD;
1855 RAISE FND_API.G_EXC_ERROR;
1856
1857 END IF;
1858
1859 /*
1860 ** Fix Bug # 1920064, 2984023
1861 ** The following 'if' condition was added to select the header_id
1862 ** into a local variable 'p_hdr_id'. This variable is passed as a
1863 ** parameter to the procedure CHECK_HOLDS_LINE. This ensures that
1864 ** header_id is always passed as a not null parameter.
1865 */
1866
1867 IF p_header_id IS NULL THEN
1868 Begin
1869 SELECT header_id
1870 INTO p_hdr_id
1871 FROM oe_order_lines_all
1872 WHERE line_id = p_line_id;
1873 EXCEPTION
1874 WHEN NO_DATA_FOUND THEN
1875 x_result_out := FND_API.G_FALSE;
1876 IF l_debug_level > 0 THEN
1877 oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 1: ' || P_LINE_ID ) ;
1878 END IF;
1879 End;
1880 ELSE
1881 p_hdr_id := p_header_id;
1882 END IF;
1883
1884 IF l_debug_level > 0 THEN
1885 oe_debug_pub.add( 'CHECKING HOLD ON HEADER ID: ' || P_HDR_ID ) ;
1886 END IF;
1887
1888 --7832836 start
1889 l_org_id := MO_GLOBAL.get_current_org_id;
1890 IF l_org_id IS NULL THEN
1891 BEGIN
1892 SELECT org_id
1893 INTO l_org_id
1894 FROM oe_order_headers_all
1895 WHERE header_id=p_hdr_id;
1896 EXCEPTION
1897 WHEN NO_DATA_FOUND THEN
1898 x_result_out := FND_API.G_FALSE;
1899 END;
1900 END IF;
1901
1902 l_cascade_hold_non_smc := NVL(OE_SYS_PARAMETERS.VALUE('ONT_CASCADE_HOLD_NONSMC_PTO',l_org_id),'N');
1903 --7832836 end
1904
1905 /*
1906 ** Checking for HEADER level generic holds and activity specific holds
1907 */
1908 BEGIN
1909
1910 SELECT 'ANY_HEADER_HOLD'
1911 INTO l_dummy
1912 FROM oe_order_holds_all oh
1913 WHERE oh.header_id = p_hdr_id
1914 AND oh.line_id IS NULL
1915 AND oh.hold_release_id IS NULL
1916 AND EXISTS
1917 (SELECT 1
1918 FROM oe_hold_sources_all hs,
1919 oe_hold_definitions h
1920 WHERE oh.hold_source_id = hs.hold_source_id
1921 AND hs.hold_id = h.hold_id
1922 AND NVL(h.item_type,
1923 DECODE(p_chk_act_hold_only,
1924 'Y', 'XXXXX',
1925 NVL(p_wf_item, 'NO ITEM')) ) =
1926 NVL(p_wf_item, 'NO ITEM')
1927 AND NVL(h.activity_name,
1928 DECODE(p_chk_act_hold_only,
1929 'Y', 'XXXXX',
1930 NVL(p_wf_activity, 'NO ACT')) ) =
1931 NVL(p_wf_activity, 'NO ACT')
1932 AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
1933 AND NVL( H.END_DATE_ACTIVE, SYSDATE )
1934 AND hs.hold_id = NVL(p_hold_id, hs.hold_id)
1935 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
1936 ROUND( SYSDATE )
1937 AND hs.hold_entity_code = NVL(p_entity_code, hs.hold_entity_code)
1938 AND hs.hold_entity_id = NVL(p_entity_id, hs.hold_entity_id)
1939 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
1940 NVL(p_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
1941 AND NVL(hs.hold_entity_id2, -99) =
1942 nvl(p_entity_id2, NVL(hs.hold_entity_id2, -99 )) );
1943
1944 EXCEPTION
1945 WHEN NO_DATA_FOUND THEN
1946 x_result_out := FND_API.G_FALSE;
1947 IF l_debug_level > 0 THEN
1948 oe_debug_pub.add( 'NO HOLDS FOUND FOR HEADER ID: ' || P_HDR_ID ) ;
1949 END IF;
1950 WHEN TOO_MANY_ROWS THEN
1951 null;
1952 END;
1953
1954 -- Return TRUE if Header Level Hold exists
1955 IF x_result_out = FND_API.G_TRUE THEN
1956 IF l_debug_level > 0 THEN
1957 oe_debug_pub.add( 'HEADER LEVEL HOLD EXISTS' ) ;
1958 END IF;
1959 RETURN;
1960 END IF;
1961
1962 IF p_line_id IS NOT NULL THEN
1963
1964 /* Check if the Line is on Hold */
1965 Check_Holds_line (
1966 p_hdr_id => p_hdr_id
1967 ,p_line_id => p_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
1982 -- Raise if the l_return_status is unexpected error
1983 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1984 IF l_debug_level > 0 THEN
1985 oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
1986 END IF;
1987 RAISE FND_API.G_EXC_ERROR;
1988 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1989 THEN
1990 IF l_debug_level > 0 THEN
1991 oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
1992 END IF;
1993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1994 END IF;
1995
1996 /* Proceed only if there is no hold on the line */
1997 IF x_result_out = FND_API.G_FALSE THEN
1998
1999 /* Check to see if the line is a part of ATO model, SMC, etc. */
2000 BEGIN
2001 IF l_debug_level > 0 THEN
2002 oe_debug_pub.add( 'CHECKING IF LINE IS PART OF ATO MODEL , SMC' ) ;
2003 END IF;
2004 SELECT ATO_LINE_ID, TOP_MODEL_LINE_ID,
2005 SHIP_MODEL_COMPLETE_FLAG, ITEM_TYPE_CODE,
2006 LINK_TO_LINE_ID
2007 INTO l_ato_line_id, l_top_model_line_id,
2008 l_smc_flag, l_item_type_code, l_link_to_line_id
2009 FROM oe_order_lines_all
2010 WHERE line_id = p_line_id;
2011 EXCEPTION
2012 WHEN NO_DATA_FOUND THEN
2013 x_result_out := FND_API.G_FALSE;
2014 IF l_debug_level > 0 THEN
2015 oe_debug_pub.add( 'LINE ID DOES NOT EXISTS OR IS INVALID - 2: ' || P_LINE_ID ) ;
2016 END IF;
2017 END;
2018
2019 IF l_debug_level > 0 THEN
2020 IF l_ato_line_id IS NOT NULL OR NVL(l_smc_flag, 'N') = 'Y' THEN
2021 oe_debug_pub.add( 'ATO_LINE_ID: '||L_ATO_LINE_ID );
2022 oe_debug_pub.add( 'TOP_MODE_LINE_ID: '||L_TOP_MODEL_LINE_ID );
2023 oe_debug_pub.add( 'SHIP_MODEL_COMPLETE_FLAG: '||L_SMC_FLAG );
2024 oe_debug_pub.add( 'ITEM_TYPE_CODE: '||L_ITEM_TYPE_CODE );
2025 oe_debug_pub.add( 'LINK_TO_LINE_ID: '||L_LINK_TO_LINE_ID );
2026 ELSE
2027 oe_debug_pub.add( 'LINE IS NOT PART OF ATO MODEL OR SMC' ) ;
2028 END IF;
2029 END IF;
2030
2031 /* If Line is part of ATO Model */
2032 IF l_ato_line_id is NOT NULL AND x_result_out = FND_API.G_FALSE AND
2033 NOT (l_ato_line_id = p_line_id AND l_item_type_code = OE_GLOBALS.G_ITEM_OPTION) THEN
2034
2035 IF l_debug_level > 0 THEN
2036 oe_debug_pub.add( 'CHECK_HOLDS:ATO MODEL LINE: ' || L_ATO_LINE_ID ) ;
2037 END IF;
2038
2039 /*
2040 ** Fix Bug # 2984023
2041 ** Following replaced by a single call to Check_Holds_ATO
2042
2043 x_result_out := FND_API.G_FALSE;
2044
2045 OPEN ato_model_lines;
2046 loop
2047 FETCH ato_model_lines into l_line_id;
2048 exit when ato_model_lines%NOTFOUND OR
2049 (x_result_out = FND_API.G_TRUE);
2050 IF l_debug_level > 0 THEN
2051 oe_debug_pub.add( 'CHECK_HOLDS:CHECKING LINEID' || L_LINE_ID ) ;
2052 END IF;
2053
2054 Check_Holds_line (
2055 p_hdr_id => p_hdr_id
2056 ,p_line_id => l_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 end loop;
2071 */
2072 Check_Holds_ATO (
2073 p_hdr_id => p_hdr_id
2074 ,p_ato_line_id => l_ato_line_id
2075 ,p_top_model_line_id => l_top_model_line_id
2076 ,p_hold_id => p_hold_id
2077 ,p_wf_item => p_wf_item
2078 ,p_wf_activity => p_wf_activity
2079 ,p_entity_code => p_entity_code
2080 ,p_entity_id => p_entity_id
2081 ,p_entity_code2 => p_entity_code2
2082 ,p_entity_id2 => p_entity_id2
2083 ,p_chk_act_hold_only => p_chk_act_hold_only
2084 ,x_result_out => x_result_out
2085 ,x_return_status => l_return_status
2086 ,x_msg_count => l_msg_count
2087 ,x_msg_data => l_msg_data
2088 );
2089 -- Raise if the l_return_status is unexpected error
2090 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2091 IF l_debug_level > 0 THEN
2092 oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_ERROR') ;
2093 END IF;
2094 RAISE FND_API.G_EXC_ERROR;
2095 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2096 IF l_debug_level > 0 THEN
2097 oe_debug_pub.add('Check_Holds_ATO:G_RET_STS_UNEXP_ERROR') ;
2098 END IF;
2099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2100 END IF;
2101
2102 END IF; /* l_ato_line_id is NOT NULL */
2103
2104 IF NVL(l_smc_flag, 'N') = 'Y' AND x_result_out = FND_API.G_FALSE THEN
2105
2106 IF l_debug_level > 0 THEN
2107 oe_debug_pub.add( 'CHECK_HOLDS:TOP MODEL LINE ID: ' || L_TOP_MODEL_LINE_ID ) ;
2108 END IF;
2109
2110 /*
2111 ** Fix Bug # 2984023
2112 ** Following replaced by a single call to Check_Holds_SMC
2113
2114 OPEN smc_lines;
2115 loop
2116
2117 FETCH smc_lines into l_line_id;
2118 exit when smc_lines%NOTFOUND OR (x_result_out = FND_API.G_TRUE);
2119
2120 IF l_debug_level > 0 THEN
2121 oe_debug_pub.add( 'CHECK_HOLDS: CHECKING SMC LINEID' || L_LINE_ID ) ;
2122 END IF;
2123
2124 Check_Holds_line (
2125 p_hdr_id => p_hdr_id
2126 ,p_line_id => l_line_id
2127 ,p_hold_id => p_hold_id
2128 ,p_wf_item => p_wf_item
2129 ,p_wf_activity => p_wf_activity
2130 ,p_entity_code => p_entity_code
2131 ,p_entity_id => p_entity_id
2132 ,p_entity_code2 => p_entity_code2
2133 ,p_entity_id2 => p_entity_id2
2134 ,p_chk_act_hold_only => p_chk_act_hold_only
2135 ,x_result_out => x_result_out
2136 ,x_return_status => l_return_status
2137 ,x_msg_count => l_msg_count
2138 ,x_msg_data => l_msg_data
2139 );
2140 end loop;
2141 */
2142
2143 Check_Holds_SMC (
2144 p_hdr_id => p_hdr_id
2145 ,p_top_model_line_id => l_top_model_line_id
2146 ,p_hold_id => p_hold_id
2147 ,p_wf_item => p_wf_item
2148 ,p_wf_activity => p_wf_activity
2149 ,p_entity_code => p_entity_code
2150 ,p_entity_id => p_entity_id
2151 ,p_entity_code2 => p_entity_code2
2152 ,p_entity_id2 => p_entity_id2
2153 ,p_chk_act_hold_only => p_chk_act_hold_only
2154 ,x_result_out => x_result_out
2155 ,x_return_status => l_return_status
2156 ,x_msg_count => l_msg_count
2157 ,x_msg_data => l_msg_data
2158 );
2159 -- Raise if the l_return_status is unexpected error
2160 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2161 IF l_debug_level > 0 THEN
2162 oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_ERROR') ;
2163 END IF;
2164 RAISE FND_API.G_EXC_ERROR;
2165 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2166 IF l_debug_level > 0 THEN
2167 oe_debug_pub.add('Check_Holds_SMC:G_RET_STS_UNEXP_ERROR') ;
2168 END IF;
2169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2170 END IF;
2171
2172 END IF; /* l_smc_flag = 'Y' */
2173 --5737464
2174 IF NVL(l_smc_flag, 'N') = 'N' AND x_result_out = FND_API.G_FALSE THEN
2175 IF l_debug_level > 0 THEN
2176 oe_debug_pub.add( 'CHECK_HOLDS: CHECKING FOR CONFIG VALIDATION HOLD' || L_TOP_MODEL_LINE_ID ) ;
2177 END IF;
2178 IF l_cascade_hold_non_smc <> 'Y' THEN --ER#7479609
2179 Check_Holds_SMC (
2180 p_hdr_id => p_hdr_id
2181 ,p_top_model_line_id => l_top_model_line_id
2182 ,p_hold_id => 3
2183 ,p_wf_item => p_wf_item
2184 ,p_wf_activity => p_wf_activity
2185 ,p_entity_code => p_entity_code
2186 ,p_entity_id => p_entity_id
2187 ,p_entity_code2 => p_entity_code2
2188 ,p_entity_id2 => p_entity_id2
2189 ,p_chk_act_hold_only => p_chk_act_hold_only
2190 ,x_result_out => x_result_out
2191 ,x_return_status => l_return_status
2192 ,x_msg_count => l_msg_count
2193 ,x_msg_data => l_msg_data
2194 );
2195 --ER#7479609 start
2196 ELSE
2197 Check_Holds_SMC (
2198 p_hdr_id => p_hdr_id
2199 ,p_top_model_line_id => l_top_model_line_id
2200 ,p_hold_id => p_hold_id
2201 ,p_wf_item => p_wf_item
2202 ,p_wf_activity => p_wf_activity
2203 ,p_entity_code => p_entity_code
2204 ,p_entity_id => p_entity_id
2205 ,p_entity_code2 => p_entity_code2
2206 ,p_entity_id2 => p_entity_id2
2207 ,p_chk_act_hold_only => p_chk_act_hold_only
2208 ,x_result_out => x_result_out
2209 ,x_return_status => l_return_status
2210 ,x_msg_count => l_msg_count
2211 ,x_msg_data => l_msg_data
2212 );
2213 END IF;
2214 --ER#7479609 end
2215
2216 END IF;--NON SMC Config Validation Hold
2217 --5737464
2218
2219 IF l_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED AND x_result_out = FND_API.G_FALSE THEN
2220
2221 IF l_debug_level > 0 THEN
2222 oe_debug_pub.add( 'CHECK_HOLDS: CHECKING HOLD ON LINK TO LINE ID: ' || L_LINK_TO_LINE_ID ) ;
2223 END IF;
2224
2225 Check_Holds_line (
2226 p_hdr_id => p_hdr_id
2227 ,p_line_id => l_link_to_line_id
2228 ,p_hold_id => p_hold_id
2229 ,p_wf_item => p_wf_item
2230 ,p_wf_activity => p_wf_activity
2231 ,p_entity_code => p_entity_code
2232 ,p_entity_id => p_entity_id
2233 ,p_entity_code2 => p_entity_code2
2234 ,p_entity_id2 => p_entity_id2
2235 ,p_chk_act_hold_only => p_chk_act_hold_only
2236 ,p_ii_parent_flag => 'Y'
2237 ,x_result_out => x_result_out
2238 ,x_return_status => l_return_status
2239 ,x_msg_count => l_msg_count
2240 ,x_msg_data => l_msg_data
2241 );
2242 -- Raise if the l_return_status is unexpected error
2243 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2244 IF l_debug_level > 0 THEN
2245 oe_debug_pub.add('Check_Holds_line:G_RET_STS_ERROR') ;
2246 END IF;
2247 RAISE FND_API.G_EXC_ERROR;
2248 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2249 IF l_debug_level > 0 THEN
2250 oe_debug_pub.add('Check_Holds_line:G_RET_STS_UNEXP_ERROR') ;
2251 END IF;
2252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2253 END IF;
2254
2255 END IF; /* l_item_type_code = 'INCLUDED' */
2256
2257 END IF; /* Proceed only if there is no hold on the line */
2258
2259 END IF; /* IF LINE ID IS NOT NULL */
2260
2261 IF l_debug_level > 0 THEN
2262 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.CHECK_HOLDS' ) ;
2263 END IF;
2264
2265 EXCEPTION
2266 WHEN FND_API.G_EXC_ERROR THEN
2267 x_return_status := FND_API.G_RET_STS_ERROR;
2268 x_result_out := FND_API.G_FALSE;
2269 FND_MSG_PUB.Count_And_Get
2270 ( p_count => x_msg_count
2271 , p_data => x_msg_data
2272 );
2273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2275 x_result_out := FND_API.G_FALSE;
2276 FND_MSG_PUB.Count_And_Get
2277 ( p_count => x_msg_count
2278 , p_data => x_msg_data
2279 );
2280 WHEN OTHERS THEN
2281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2282 x_result_out := FND_API.G_FALSE;
2283 IF FND_MSG_PUB.Check_Msg_Level
2284 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2285 THEN
2286 FND_MSG_PUB.Add_Exc_Msg
2287 ( G_PKG_NAME
2288 , l_api_name
2289 );
2290 END IF;
2291 FND_MSG_PUB.Count_And_Get
2292 ( p_count => x_msg_count
2293 , p_data => x_msg_data
2294 );
2295
2296 END Check_Holds;
2297
2298
2299 /******************************************************************
2300 * CHECK HOLD_SOURCES *
2301 * Checks if there are any holds for a Hold entity combination. *
2302 * Expects at least the hold_entity_code or hold_entity_id *
2303 ******************************************************************/
2304 PROCEDURE Check_Hold_Sources
2305 ( p_api_version IN NUMBER
2306 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2307 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2308 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2309 , p_hold_id IN NUMBER DEFAULT NULL
2310 , p_wf_item IN VARCHAR2 DEFAULT NULL
2311 , p_wf_activity IN VARCHAR2 DEFAULT NULL
2312 , p_hold_entity_code IN VARCHAR2 DEFAULT NULL
2313 --ER#7479609 , p_hold_entity_id IN NUMBER DEFAULT NULL
2314 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE DEFAULT NULL --ER#7479609
2315 , p_hold_entity_code2 IN VARCHAR2 DEFAULT NULL
2316 --ER#7479609 , p_hold_entity_id2 IN NUMBER DEFAULT NULL
2317 , p_hold_entity_id2 IN oe_hold_sources_all.hold_entity_id2%TYPE DEFAULT NULL --ER#7479609
2318 , p_chk_act_hold_only IN VARCHAR2 DEFAULT 'N'
2319 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2320 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2321 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2322 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2323 )
2324 IS
2325 l_api_name CONSTANT VARCHAR2(30) := 'Check_Hold_Sources';
2326 l_api_version CONSTANT NUMBER := 1.0;
2327 l_dummy VARCHAR2(30);
2328
2329 l_return_status VARCHAR2(30);
2330 l_msg_count NUMBER;
2331 l_msg_data VARCHAR2(2000);
2332
2333 --
2334 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2335 --
2336 BEGIN
2337 IF l_debug_level > 0 THEN
2338 oe_debug_pub.add( 'IN OE_HOLDS_PUB.CHECK_HOLD_SOURCES' ) ;
2339 END IF;
2340 -- Initialize API return status to success
2341 x_return_status := FND_API.G_RET_STS_SUCCESS;
2342
2343 -- Initialize result to TRUE i.e. holds are found
2344 x_result_out := FND_API.G_TRUE;
2345
2346
2347 -- Check for Missing Input Parameters
2348 IF p_hold_entity_code IS NULL AND p_hold_entity_id IS NULL THEN
2349 IF l_debug_level > 0 THEN
2350 oe_debug_pub.add( 'ENTER HOLD_ENTITY_CODE OR HOLD_ENTITY_ID' ) ;
2351 END IF;
2352 /* TO_DO: Seed a more meaningfull message */
2353 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
2354 OE_MSG_PUB.ADD;
2355 RAISE FND_API.G_EXC_ERROR;
2356
2357 END IF;
2358
2359 /******************************/
2360 /* CHECKING FOR HOLDS SOURCES */
2361 /******************************************************************
2362 ** Checking for any generic holds and activity_specific holds **
2363 ** Sources **
2364 ******************************************************************/
2365 BEGIN
2366
2367 SELECT 'ANY_HOLD_SOURCE'
2368 INTO l_dummy
2369 FROM oe_hold_sources_all hs,
2370 oe_hold_definitions h
2371 WHERE hs.hold_id = h.hold_id
2372 AND NVL(h.item_type,
2373 DECODE(p_chk_act_hold_only,
2374 'Y', 'XXXXX',
2375 NVL(p_wf_item, 'NO ITEM')) ) =
2376 NVL(p_wf_item, 'NO ITEM')
2377 AND NVL(h.activity_name,
2378 DECODE(p_chk_act_hold_only,
2379 'Y', 'XXXXX',
2380 NVL(p_wf_activity, 'NO ACT')) ) =
2381 NVL(p_wf_activity, 'NO ACT')
2382 AND hs.hold_id = NVL(p_hold_id, hs.hold_id)
2383 AND hs.RELEASED_FLAG = 'N'
2384 AND hs.hold_entity_code = NVL(p_hold_entity_code, hs.hold_entity_code)
2385 AND hs.hold_entity_id = NVL(p_hold_entity_id, hs.hold_entity_id)
2386 AND NVL(hs.hold_entity_code2, 'NO_ENTITY_CODE2') =
2387 NVL(p_hold_entity_code2, nvl(hs.hold_entity_code2,'NO_ENTITY_CODE2') )
2388 AND NVL(hs.hold_entity_id2, -99) =
2389 nvl(p_hold_entity_id2, NVL(hs.hold_entity_id2, -99 ) );
2390
2391 EXCEPTION
2392 WHEN NO_DATA_FOUND THEN
2393 x_result_out := FND_API.G_FALSE;
2394 WHEN TOO_MANY_ROWS THEN
2395 null;
2396 END;
2397
2398
2399 EXCEPTION
2400 WHEN FND_API.G_EXC_ERROR THEN
2401 x_return_status := FND_API.G_RET_STS_ERROR;
2402 x_result_out := FND_API.G_FALSE;
2403 FND_MSG_PUB.Count_And_Get
2404 ( p_count => x_msg_count
2405 , p_data => x_msg_data
2406 );
2407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2409 x_result_out := FND_API.G_FALSE;
2410 FND_MSG_PUB.Count_And_Get
2411 ( p_count => x_msg_count
2412 , p_data => x_msg_data
2413 );
2414 WHEN OTHERS THEN
2415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2416 x_result_out := FND_API.G_FALSE;
2417 IF FND_MSG_PUB.Check_Msg_Level
2418 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2419 THEN
2420 FND_MSG_PUB.Add_Exc_Msg
2421 ( G_PKG_NAME
2422 , l_api_name
2423 );
2424 END IF;
2425 FND_MSG_PUB.Count_And_Get
2426 ( p_count => x_msg_count
2427 , p_data => x_msg_data
2428 );
2429
2430 END Check_Hold_Sources;
2431
2432
2433
2434
2435 --------------------------------------------------------------------------
2436 -- RELEASE HOLDS
2437 -- Take Release Action on a Hold.
2438 -- Note: Leaving this call for backward compatibility. AR's Customer form
2439 -- still calls the old oe_holds (OEXOHAPB.pls) which in turns calls
2440 -- this api.
2441 -- ALL NEW Callers should call the new api structure
2442 --------------------------------------------------------------------------
2443 PROCEDURE Release_Holds
2444 ( p_api_version IN NUMBER DEFAULT 1.0
2445 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
2446 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2447 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2448 , p_header_id IN NUMBER DEFAULT NULL
2449 , p_line_id IN NUMBER DEFAULT NULL
2450 , p_hold_id IN NUMBER DEFAULT NULL
2451 , p_entity_code IN VARCHAR2 DEFAULT NULL
2452 , p_entity_id IN NUMBER DEFAULT NULL
2453 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
2454 , p_entity_id2 IN NUMBER DEFAULT NULL
2455 , p_hold_release_rec IN OE_Hold_Sources_Pvt.Hold_Release_REC
2456 , p_check_authorization_flag IN VARCHAR2 DEFAULT 'N' -- bug 8477694
2457 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2458 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2459 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2460 )
2461 IS
2462 l_api_name CONSTANT VARCHAR2(30) := 'RELEASE_HOLDS';
2463 l_api_version CONSTANT NUMBER := 1.0;
2464 l_user_id NUMBER;
2465 l_dummy VARCHAR2(30);
2466 l_hold_release_id NUMBER;
2467 l_hold_source_id NUMBER := 0;
2468 l_order_hold_id NUMBER := 0;
2469 --ER#7479609 l_entity_code VARCHAR2(1);
2470 l_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
2471
2472 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
2473 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
2474 l_org_id number;
2475 l_check_authorization_flag varchar2(1):='N'; -- bug 8477694
2476 -- Define Cursors
2477 CURSOR hold_source IS
2478 SELECT HS.HOLD_SOURCE_ID,OH.ORDER_HOLD_ID
2479 FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
2480 WHERE HS.HOLD_ID = p_hold_id
2481 AND HS.RELEASED_FLAG = 'N'
2482 AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2483 AND HS.HOLD_ENTITY_CODE = p_entity_code
2484 AND HS.HOLD_ENTITY_ID = p_entity_id
2485 AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
2486 nvl(p_entity_code2, 'NO_ENTITY_CODE2')
2487 AND nvl(HS.HOLD_ENTITY_ID2, -99) =
2488 nvl(p_entity_id2, -99)
2489 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
2490 AND NVL(OH.HEADER_ID, 0) = NVL(NVL(p_header_id, OH.HEADER_ID), 0)
2491 AND NVL(OH.LINE_ID, 0) = NVL(NVL(p_line_id, OH.LINE_ID), 0)
2492 AND OH.HOLD_RELEASE_ID IS NULL;
2493 CURSOR order_hold IS
2494 SELECT OH.ORDER_HOLD_ID
2495 FROM OE_ORDER_HOLDS OH
2496 WHERE OH.HOLD_SOURCE_ID = l_hold_source_id
2497 AND NVL(OH.HEADER_ID, 0) = NVL(NVL(p_header_id, OH.HEADER_ID), 0)
2498 AND NVL(OH.LINE_ID, 0) = NVL(NVL(p_line_id, OH.LINE_ID), 0)
2499 AND OH.HOLD_RELEASE_ID IS NULL;
2500 --
2501 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2502 --
2503 BEGIN
2504 l_check_authorization_flag := p_check_authorization_flag; -- bug 8477694
2505 IF l_debug_level > 0 THEN
2506 oe_debug_pub.add( 'IN RELEASE_HOLDS , OLD' ) ;
2507 END IF;
2508
2509 l_org_id := MO_GLOBAL.get_current_org_id;
2510 IF l_org_id IS NULL THEN
2511 -- org_id is null, raise an error.
2512 oe_debug_pub.add('Org_Id is NULL',1);
2513 x_return_status := FND_API.G_RET_STS_ERROR;
2514 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2515 FND_MSG_PUB.ADD;
2516 RAISE FND_API.G_EXC_ERROR;
2517 END IF;
2518
2519 -- Standard Start of API savepoint
2520 SAVEPOINT Release_holds_PUB;
2521
2522 -- Initialize API return status to success
2523 x_return_status := FND_API.G_RET_STS_SUCCESS;
2524
2525
2526 Utilities(l_user_id);
2527
2528 -------------------------------------------------------------------
2529 -- Hold Source ID is KNOWN. Release the hold for this order or
2530 -- line that uses this hold source.
2531 -------------------------------------------------------------------
2532 IF p_hold_release_rec.hold_source_id IS NOT NULL THEN
2533
2534 IF l_debug_level > 0 THEN
2535 oe_debug_pub.add( 'USING HOLD SOURCE ID' ) ;
2536 END IF;
2537 l_hold_source_id := p_hold_release_rec.hold_source_id;
2538
2539 BEGIN
2540 -- Retrieving the entity code for this hold source
2541 -- Checking if its a valid hold source id
2542 SELECT hold_entity_code
2543 INTO l_entity_code
2544 FROM OE_HOLD_SOURCES
2545 WHERE hold_source_id = l_hold_source_id;
2546
2547 EXCEPTION
2548 WHEN NO_DATA_FOUND THEN
2549 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_SOURCE');
2550 OE_MSG_PUB.ADD;
2551 RAISE FND_API.G_EXC_ERROR;
2552 WHEN OTHERS THEN
2553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2554 END;
2555
2556 -- Retrieving order hold information
2557 OPEN order_hold;
2558 FETCH order_hold INTO l_order_hold_id;
2559 IF order_hold%notfound THEN
2560 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD');
2561 OE_MSG_PUB.ADD;
2562 RAISE FND_API.G_EXC_ERROR;
2563 END IF;
2564 CLOSE order_hold;
2565
2566 -------------------------------------------------------------------
2567 -- Hold Source ID is NOT KNOWN. Query up the hold source
2568 -- and then release the order hold using this hold source.
2569 -------------------------------------------------------------------
2570
2571 ELSE
2572 IF l_debug_level > 0 THEN
2573 oe_debug_pub.add( 'HOLD SOURCE ID IS NOT PASSED' ) ;
2574 END IF;
2575 -- Check for Missing Values
2576 IF p_hold_id IS NULL THEN
2577 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_ID');
2578 OE_MSG_PUB.ADD;
2579 RAISE FND_API.G_EXC_ERROR;
2580 END IF;
2581
2582 IF p_entity_code IS NULL THEN
2583 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
2584 OE_MSG_PUB.ADD;
2585 RAISE FND_API.G_EXC_ERROR;
2586 ELSE
2587 l_entity_code := p_entity_code;
2588 END IF;
2589
2590 IF p_entity_id IS NULL THEN
2591 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
2592 OE_MSG_PUB.ADD;
2593 RAISE FND_API.G_EXC_ERROR;
2594 END IF;
2595
2596 /*
2597 IF p_line_id IS NULL AND p_header_id IS NULL THEN
2598 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HEADER_AND_LINE_ID');
2599 OE_MSG_PUB.ADD;
2600 RAISE FND_API.G_EXC_ERROR;
2601 END IF;
2602 */
2603
2604 -- Retrieving hold source and order hold information
2605 /*
2606 OPEN hold_source;
2607 FETCH hold_source INTO l_hold_source_id, l_order_hold_id;
2608 IF hold_source%notfound THEN
2609 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD');
2610 OE_MSG_PUB.ADD;
2611 RAISE FND_API.G_EXC_ERROR;
2612 END IF;
2613 CLOSE hold_source;
2614 */
2615
2616 END IF; -- End of check to see if hold source ID is passed
2617
2618 -- Check to see if the Site code is Bill_to OR Ship_to
2619 l_hold_source_rec.hold_id := p_hold_id;
2620 IF p_entity_code = 'S' THEN
2621 IF l_debug_level > 0 THEN
2622 oe_debug_pub.add( 'CHECKING FOR SITE CODE , BILL TO OR SHIP TO' , 1 ) ;
2623 END IF;
2624 l_hold_source_rec.hold_entity_code := Hold_Site_Code(p_entity_id);
2625 ELSE
2626 l_hold_source_rec.hold_entity_code := p_entity_code;
2627 END IF;
2628
2629 l_hold_source_rec.hold_entity_id := p_entity_id;
2630 l_hold_release_rec.RELEASE_REASON_CODE :=
2631 p_hold_release_rec.RELEASE_REASON_CODE;
2632 l_hold_release_rec.RELEASE_COMMENT := p_hold_release_rec.RELEASE_COMMENT;
2633
2634 IF l_debug_level > 0 THEN
2635 oe_debug_pub.add( 'CALLING RELEASE HOLDS , OLD' ) ;
2636 END IF;
2637 oe_holds_pvt.Release_Holds(
2638 p_hold_source_rec => l_hold_source_rec
2639 ,p_hold_release_rec => l_hold_release_rec
2640 ,p_check_authorization_flag => l_check_authorization_flag -- bug 8477694
2641 ,x_return_status => x_return_status
2642 ,x_msg_count => x_msg_count
2643 ,x_msg_data => x_msg_data
2644 );
2645 IF l_debug_level > 0 THEN
2646 oe_debug_pub.add( 'OE_HOLDS_PVT.RELEASE_HOLDS STATUS:' || X_RETURN_STATUS ) ;
2647 END IF;
2648
2649 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2650 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2651 RAISE FND_API.G_EXC_ERROR;
2652 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2654 END IF;
2655 END IF;
2656
2657 EXCEPTION
2658 WHEN FND_API.G_EXC_ERROR THEN
2659 ROLLBACK TO RELEASE_HOLDS_PUB;
2660 x_return_status := FND_API.G_RET_STS_ERROR;
2661 FND_MSG_PUB.Count_And_Get
2662 ( p_count => x_msg_count
2663 , p_data => x_msg_data
2664 );
2665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2666 ROLLBACK TO RELEASE_HOLDS_PUB;
2667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2668 FND_MSG_PUB.Count_And_Get
2669 ( p_count => x_msg_count
2670 , p_data => x_msg_data
2671 );
2672 WHEN OTHERS THEN
2673 ROLLBACK TO RELEASE_HOLDS_PUB;
2674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2675 IF FND_MSG_PUB.Check_Msg_Level
2676 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2677 THEN
2678 FND_MSG_PUB.Add_Exc_Msg
2679 ( G_PKG_NAME
2680 , l_api_name
2681 );
2682 END IF;
2683 FND_MSG_PUB.Count_And_Get
2684 ( p_count => x_msg_count
2685 , p_data => x_msg_data
2686 );
2687
2688 END Release_Holds;
2689
2690
2691 ----------------------------------------------------------------------------
2692 -- Delete Holds
2693 -- Deletes from OE_ORDER_HOLDS all hold records for an order (p_header_id)
2694 -- or for a line (p_line_id).
2695 -- Also, if there are ORDER hold sources (hold_entity_code = 'O') for this
2696 -- order, deletes hold source records from OE_HOLD_SOURCES.
2697 -- If the hold or hold source was released and the same release record is
2698 -- not used by an existing hold or hold source, then deletes the
2699 -- release record also from OE_HOLD_RELEASES;
2700 ----------------------------------------------------------------------------
2701 PROCEDURE Delete_Holds
2702 ( p_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2703 ,p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
2704 )
2705 IS
2706 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_HOLDS';
2707 l_api_version CONSTANT NUMBER := 1.0;
2708 l_order_hold_id NUMBER;
2709 l_hold_source_id NUMBER;
2710 l_hold_release_id NUMBER := 0;
2711 x_RETURN_STATUS varchar2(56);
2712 x_MSG_COUNT number;
2713 x_msg_data varchar2(500);
2714 l_org_id number;
2715
2716 CURSOR order_hold IS
2717 SELECT order_hold_id, NVL(hold_release_id,0)
2718 FROM OE_ORDER_HOLDS
2719 WHERE HEADER_ID = p_header_id;
2720 CURSOR hold_source IS
2721 SELECT hold_source_id, NVL(hold_release_id,0)
2722 FROM OE_HOLD_SOURCES
2723 WHERE HOLD_ENTITY_CODE = 'O'
2724 AND HOLD_ENTITY_ID = p_header_id;
2725 CURSOR line_hold IS
2726 SELECT order_hold_id, NVL(hold_release_id,0)
2727 FROM OE_ORDER_HOLDS
2728 WHERE LINE_ID = p_line_id;
2729
2730 --ER#7479609 start
2731 CURSOR line_hold_opt_item(p_top_model_line_id NUMBER,p_inventory_item_id NUMBER) IS
2732 SELECT OH.order_hold_id, NVL(OH.hold_release_id,0)
2733 FROM OE_ORDER_HOLDS OH,OE_HOLD_SOURCES HS,OE_ORDER_LINES OL
2734 WHERE OH.LINE_ID = p_top_model_line_id
2735 AND OH.LINE_ID = OL.LINE_ID
2736 AND OH.hold_source_id=HS.hold_source_id
2737 AND HS.hold_entity_id=OL.inventory_item_id
2738 AND HS.hold_entity_id2=p_inventory_item_id;
2739
2740 l_top_model_line_id OE_ORDER_LINES_ALL.TOP_MODEL_LINE_ID%TYPE;
2741 l_inventory_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
2742 --ER#7479609 end
2743
2744 --
2745 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2746 --
2747 BEGIN
2748 l_org_id := MO_GLOBAL.get_current_org_id;
2749 IF l_org_id IS NULL THEN
2750 -- org_id is null, raise an error.
2751 oe_debug_pub.add('Org_Id is NULL',1);
2752 x_return_status := FND_API.G_RET_STS_ERROR;
2753 FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2754 FND_MSG_PUB.ADD;
2755 RAISE FND_API.G_EXC_ERROR;
2756 END IF;
2757
2758 --SAVEPOINT DELETE_HOLDS_PUB;
2759
2760 -- Initialize API return status to success
2761 x_return_status := FND_API.G_RET_STS_SUCCESS;
2762
2763 -- Missing Input arguments
2764
2765 IF (p_header_id = FND_API.G_MISS_NUM AND
2766 p_line_id = FND_API.G_MISS_NUM) THEN
2767
2768 FND_MESSAGE.SET_NAME('ONT', 'OE_ENTER_HEADER_OR_LINE_ID');
2769 OE_MSG_PUB.ADD;
2770 RAISE FND_API.G_EXC_ERROR;
2771
2772 END IF;
2773
2774 -- Delete the hold records corr. to this order or line in OE_ORDER_HOLDS
2775 IF p_line_id = FND_API.G_MISS_NUM THEN
2776
2777 -- Delete order hold records
2778 OPEN order_hold;
2779 LOOP
2780 FETCH order_hold INTO l_order_hold_id, l_hold_release_id;
2781 IF (order_hold%notfound) THEN
2782 EXIT;
2783 END IF;
2784
2785 IF l_debug_level > 0 THEN
2786 oe_debug_pub.add( 'DELETING ORDER HOLD RECORD' ) ;
2787 END IF;
2788
2789 DELETE FROM OE_ORDER_HOLDS
2790 WHERE order_hold_id = l_order_hold_id;
2791
2792 IF l_debug_level > 0 THEN
2793 oe_debug_pub.add( 'DELETING HOLD RELEASE RECORD' ) ;
2794 END IF;
2795 DELETE FROM OE_HOLD_RELEASES
2796 WHERE HOLD_RELEASE_ID = l_hold_release_id
2797 AND ORDER_HOLD_ID = l_order_hold_id;
2798
2799 /* DELETE FROM OE_HOLD_RELEASES
2800 WHERE HOLD_RELEASE_ID = l_hold_release_id
2801 AND HOLD_RELEASE_ID NOT IN (SELECT NVL(HOLD_RELEASE_ID,0)
2802 FROM OE_ORDER_HOLDS
2803 UNION
2804 SELECT NVL(HOLD_RELEASE_ID,0)
2805 FROM OE_HOLD_SOURCES
2806 ); */
2807 END LOOP;
2808
2809 CLOSE order_hold;
2810
2811 -- Delete hold source records
2812
2813 OPEN hold_source;
2814 LOOP
2815 FETCH hold_source INTO l_hold_source_id, l_hold_release_id;
2816 IF (hold_source%notfound) THEN
2817 EXIT;
2818 END IF;
2819
2820 IF l_debug_level > 0 THEN
2821 oe_debug_pub.add( 'DELETING HOLD SOURCE RECORD' ) ;
2822 END IF;
2823 DELETE FROM OE_HOLD_SOURCES
2824 WHERE HOLD_SOURCE_ID = l_hold_source_id;
2825
2826 IF l_debug_level > 0 THEN
2827 oe_debug_pub.add( 'DELETING HOLD RELEASE RECORD' ) ;
2828 END IF;
2829 DELETE FROM OE_HOLD_RELEASES
2830 WHERE HOLD_RELEASE_ID = l_hold_release_id;
2831
2832 END LOOP;
2833 CLOSE hold_source;
2834
2835 ELSE
2836 -- Delete line hold records
2837
2838 OPEN line_hold;
2839
2840 LOOP
2841 FETCH line_hold INTO l_order_hold_id, l_hold_release_id;
2842 IF (line_hold%notfound) THEN
2843 EXIT;
2844 END IF;
2845
2846 IF l_debug_level > 0 THEN
2847 oe_debug_pub.add( 'DELETING LINE HOLD RECORD' ) ;
2848 END IF;
2849
2850 DELETE FROM OE_ORDER_HOLDS
2851 WHERE order_hold_id = l_order_hold_id;
2852
2853 DELETE FROM OE_HOLD_RELEASES
2854 WHERE HOLD_RELEASE_ID = l_hold_release_id
2855 AND ORDER_HOLD_ID = l_order_hold_id;
2856
2857 /* AND HOLD_RELEASE_ID NOT IN
2858 (SELECT NVL(HOLD_RELEASE_ID,0)
2859 FROM OE_ORDER_HOLDS
2860 UNION
2861 SELECT NVL(HOLD_RELEASE_ID,0)
2862 FROM OE_HOLD_SOURCES
2863 ); */
2864 END LOOP;
2865
2866 CLOSE line_hold;
2867
2868 --ER#7479609 start
2869 BEGIN
2870 select top_model_line_id,inventory_item_id
2871 into l_top_model_line_id,l_inventory_item_id
2872 from oe_order_lines
2873 where line_id=p_line_id
2874 and item_type_code in ('OPTION','CLASS','INCLUDED');
2875
2876 OPEN line_hold_opt_item(l_top_model_line_id,l_inventory_item_id);
2877
2878 LOOP
2879 FETCH line_hold_opt_item INTO l_order_hold_id, l_hold_release_id;
2880 IF (line_hold_opt_item%notfound) THEN
2881 EXIT;
2882 END IF;
2883
2884 IF l_debug_level > 0 THEN
2885 oe_debug_pub.add( 'DELETING LINE HOLD RECORD FOR TOP MODEL WHEN OPTION ITEM LINE IS DELETED' ) ;
2886 END IF;
2887
2888 DELETE FROM OE_ORDER_HOLDS
2889 WHERE order_hold_id = l_order_hold_id;
2890
2891 DELETE FROM OE_HOLD_RELEASES
2892 WHERE HOLD_RELEASE_ID = l_hold_release_id
2893 AND ORDER_HOLD_ID = l_order_hold_id;
2894 END LOOP;
2895
2896 CLOSE line_hold_opt_item;
2897
2898 EXCEPTION
2899 WHEN OTHERS THEN
2900 NULL;
2901 END;
2902 --ER#7479609 end
2903
2904 END IF;
2905
2906
2907 EXCEPTION
2908 WHEN FND_API.G_EXC_ERROR THEN
2909 IF (order_hold%isopen) THEN
2910 CLOSE order_hold;
2911 END IF;
2912 IF (hold_source%isopen) THEN
2913 CLOSE hold_source;
2914 END IF;
2915 IF (line_hold%isopen) THEN
2916 CLOSE line_hold;
2917 END IF;
2918 --ROLLBACK TO DELETE_HOLDS_PUB;
2919 x_return_status := FND_API.G_RET_STS_ERROR;
2920 FND_MSG_PUB.Count_And_Get
2921 ( p_count => x_msg_count
2922 , p_data => x_msg_data
2923 );
2924 WHEN OTHERS THEN
2925 IF (order_hold%isopen) THEN
2926 CLOSE order_hold;
2927 END IF;
2928 IF (hold_source%isopen) THEN
2929 CLOSE hold_source;
2930 END IF;
2931 IF (line_hold%isopen) THEN
2932 CLOSE line_hold;
2933 END IF;
2934 --ROLLBACK TO DELETE_HOLDS_PUB;
2935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2936 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2937 THEN
2938 FND_MSG_PUB.Add_Exc_Msg
2939 ( G_PKG_NAME
2940 , l_api_name
2941 );
2942 END IF;
2943 FND_MSG_PUB.Count_And_Get
2944 ( p_count => x_msg_count
2945 , p_data => x_msg_data
2946 );
2947 END Delete_Holds;
2948
2949
2950 -------------------------------------------------------------------
2951 -- Procedure: EVAL_HOLD_SOURCE
2952 -- Applies or removes holds if a hold source entity is updated
2953 -- on the order or line.
2954 -------------------------------------------------------------------
2955
2956 PROCEDURE evaluate_holds
2957 ( p_entity_code IN VARCHAR2
2958 , p_entity_id IN NUMBER
2959 , p_hold_entity_code IN VARCHAR2
2960 --ER#7479609 , p_hold_entity_id IN NUMBER
2961 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
2962 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2963 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2964 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2965 )
2966 IS
2967 l_hold_source_id NUMBER DEFAULT NULL;
2968 l_order_hold_id NUMBER DEFAULT NULL;
2969 l_return_status VARCHAR2(30);
2970 l_msg_count NUMBER;
2971 l_msg_data VARCHAR2(2000);
2972 --l_hold_release_rec OE_Hold_Sources_Pvt.Hold_Release_REC;
2973 temp NUMBER DEFAULT NULL;
2974 --l_attribute VARCHAR2(30); --ER#12571983
2975 l_attribute VARCHAR2(60); --ER#12571983
2976
2977 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
2978 --ER#7479609 l_hold_entity_code VARCHAR2(1);
2979 l_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
2980 --ER#7479609 l_hold_entity_id NUMBER;
2981 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
2982 --ER#7479609 l_hold_entity_code2 VARCHAR2(1);
2983 l_hold_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
2984 --ER#7479609 l_hold_entity_id2 NUMBER;
2985 l_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
2986 l_hold_id NUMBER;
2987
2988 l_create_order_hold_flag VARCHAR2(1) := 'Y';
2989 --l_line_rec OE_Order_PUB.Line_Rec_Type;
2990
2991 l_header_id NUMBER DEFAULT NULL;
2992 l_line_id NUMBER DEFAULT NULL;
2993 l_line_number NUMBER DEFAULT NULL;
2994
2995 l_sold_to_org_id NUMBER DEFAULT NULL;
2996 l_invoice_to_org_id NUMBER DEFAULT NULL;
2997 l_ship_to_org_id NUMBER DEFAULT NULL;
2998 l_ship_from_org_id NUMBER DEFAULT NULL;
2999 l_inventory_item_id NUMBER DEFAULT NULL;
3000 l_blanket_number NUMBER DEFAULT NULL;
3001 l_blanket_line_number NUMBER DEFAULT NULL;
3002
3003 -- ER#3667551 start
3004 l_bth_entity_code VARCHAR2(10):= '';
3005 l_bth_hold_id NUMBER := '';
3006 -- ER#3667551 end
3007
3008 --
3009 --ER#7479609 CURSOR prev_hold IS
3010 CURSOR prev_hold(l_all_del_pay VARCHAR2) IS --ER#7479609
3011 --ER#7479609 SELECT HS.hold_entity_id, OH.order_hold_id
3012 SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code --ER#7479609
3013 FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
3014 WHERE OH.HEADER_ID = l_header_id
3015 AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
3016 NVL(l_line_id,FND_API.G_MISS_NUM)
3017 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
3018 AND HS.HOLD_ENTITY_ID = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID) --ER#7479609
3019 --AND HS.HOLD_ENTITY_CODE = p_hold_entity_code -- ER#3667551
3020 AND HS.HOLD_ENTITY_CODE = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
3021 AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
3022 AND HS.RELEASED_FLAG = 'N';
3023
3024 --ER#7479609 CURSOR prev_hold_entity2 IS
3025 CURSOR prev_hold_entity2(l_all_del_pay VARCHAR2) IS --ER#7479609
3026 --ER#7479609 SELECT HS.hold_entity_id, OH.order_hold_id
3027 SELECT HS.hold_entity_id, OH.order_hold_id,HS.hold_entity_code2 --ER#7479609
3028 FROM OE_HOLD_SOURCES HS, OE_ORDER_HOLDS OH
3029 WHERE OH.HEADER_ID = l_header_id
3030 AND NVL(OH.LINE_ID,FND_API.G_MISS_NUM) =
3031 NVL(l_line_id,FND_API.G_MISS_NUM)
3032 AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
3033 --AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code -- ER#3667551
3034 AND HS.HOLD_ENTITY_CODE2 = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
3035 AND HS.HOLD_ENTITY_ID2 = DECODE(l_all_del_pay,'N',p_hold_entity_id,HS.HOLD_ENTITY_ID2) --ER#7479609
3036 AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
3037 AND HS.HOLD_ENTITY_CODE <> 'O'
3038 AND HS.RELEASED_FLAG = 'N';
3039
3040 CURSOR curr_hold_source IS
3041 SELECT HS.hold_source_id, hs.hold_id,
3042 hs.hold_entity_code, hs.hold_entity_id,
3043 hs.hold_entity_code2,hs.hold_entity_id2
3044 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
3045 --WHERE HS.HOLD_ENTITY_CODE = p_hold_entity_code -- ER#3667551
3046 WHERE HS.HOLD_ENTITY_CODE = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
3047 AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
3048 AND HS.HOLD_ENTITY_ID = p_hold_entity_id
3049 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
3050 ROUND( SYSDATE )
3051 AND HS.RELEASED_FLAG = 'N'
3052 AND HLD.HOLD_ID = HS.HOLD_ID
3053 AND SYSDATE
3054 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
3055 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE );
3056
3057 CURSOR curr_hold_source_entity2 IS
3058 SELECT HS.hold_source_id, hs.hold_id,
3059 hs.hold_entity_code, hs.hold_entity_id,
3060 hs.hold_entity_code2,hs.hold_entity_id2
3061 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
3062 --WHERE HS.HOLD_ENTITY_CODE2 = p_hold_entity_code -- ER#3667551
3063 WHERE HS.HOLD_ENTITY_CODE2 = NVL(l_bth_entity_code,p_hold_entity_code) -- ER#3667551
3064 AND HS.HOLD_ID = NVL(l_bth_hold_id,HS.HOLD_ID) -- ER#3667551
3065 AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id
3066 AND HS.HOLD_ENTITY_CODE <> 'O'
3067 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
3068 ROUND( SYSDATE )
3069 AND HS.RELEASED_FLAG = 'N'
3070 AND HLD.HOLD_ID = HS.HOLD_ID
3071 AND SYSDATE
3072 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
3073 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE );
3074
3075 --
3076 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3077 --
3078 --ER#7479609 start
3079 l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
3080 l_line_rec OE_ORDER_LINES_ALL%rowtype;
3081 TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
3082 entity_id oe_hold_sources_all.hold_entity_id%TYPE);
3083
3084 TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;
3085
3086 l_entity_tab entity_tab;
3087
3088 TYPE payment_type_tab IS TABLE OF OE_PAYMENTS.PAYMENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
3089 l_payment_type_tab payment_type_tab;
3090 pay_idx NUMBER :=0;
3091 l_all_del_pay varchar2(1) := 'Y';
3092 --ER#7479609 end
3093 l_valid_itemcat CHAR(1) := ''; --ER#13331078
3094
3095 BEGIN
3096 x_return_status := FND_API.G_RET_STS_SUCCESS;
3097
3098 IF l_debug_level > 0 THEN
3099 oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS' , 1 ) ;
3100 END IF;
3101
3102 IF l_debug_level > 0 THEN
3103 oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
3104 END IF;
3105 IF l_debug_level > 0 THEN
3106 oe_debug_pub.add( 'HOLD ENTITY: '|| P_HOLD_ENTITY_CODE ||'/' || P_HOLD_ENTITY_ID ) ;
3107 END IF;
3108 -- ER#3667551 start
3109 -- When a delayed request is logged for update of Bill To Customer Of Order header level
3110 -- the code is passed as 'BTH' instead of 'C'. This is done because both Custom hold for Sold to Customer
3111 -- and Credit Hold for Bill To Customer are created with hold_entity_code as 'C'.
3112 -- For update of Bill To Customer we only need to re-evaluate Credit Hold and not Custom Holds.
3113 If p_hold_entity_code = 'BTH' AND p_entity_code = OE_Globals.G_ENTITY_HEADER then
3114 l_bth_entity_code := 'C';
3115 l_bth_hold_id := 1;
3116 ElsIf p_hold_entity_code = 'BTL' AND p_entity_code = OE_Globals.G_ENTITY_LINE then
3117 l_bth_entity_code := 'C';
3118 l_bth_hold_id := 1;
3119 Else
3120 l_bth_entity_code := '';
3121 l_bth_hold_id := '';
3122 END If;
3123 -- ER#3667551 end
3124
3125
3126 --ER#7479609 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3127 IF (p_entity_code = OE_Globals.G_ENTITY_HEADER OR
3128 p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_PAYMENT) THEN --ER#7479609
3129 -- Indicates Header Level action
3130 l_header_id := p_entity_id;
3131 --ER#7479609 start
3132 BEGIN
3133 SELECT * INTO l_header_rec
3134 FROM oe_order_headers
3135 WHERE header_id=l_header_id;
3136
3137
3138 EXCEPTION
3139 WHEN OTHERS THEN
3140 IF l_debug_level > 0 THEN
3141 oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
3142 END IF;
3143 RAISE NO_DATA_FOUND;
3144 END;
3145 --ER#7479609 end
3146 IF l_debug_level > 0 THEN
3147 oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3148 END IF;
3149 --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3150 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3151 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3152 l_line_id := p_entity_id;
3153 IF l_debug_level > 0 THEN
3154 oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3155 END IF;
3156 --ER#7479609 start
3157 BEGIN
3158 SELECT OH.* INTO l_header_rec
3159 FROM oe_order_headers OH,oe_order_lines OL
3160 WHERE OH.header_id=OL.header_id
3161 AND OL.line_id=l_line_id;
3162
3163 l_header_id := l_header_rec.header_id;
3164
3165 EXCEPTION
3166 WHEN OTHERS THEN
3167 IF l_debug_level > 0 THEN
3168 oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
3169 END IF;
3170 RAISE NO_DATA_FOUND;
3171 END;
3172 --ER#7479609 end
3173
3174 BEGIN
3175 /*ER#7479609 start
3176 SELECT sold_to_org_id,
3177 invoice_to_org_id,
3178 ship_to_org_id,
3179 ship_from_org_id,
3180 inventory_item_id,
3181 line_number,
3182 Blanket_number,
3183 Blanket_line_number,
3184 header_id
3185 INTO l_sold_to_org_id,
3186 l_invoice_to_org_id,
3187 l_ship_to_org_id,
3188 l_ship_from_org_id,
3189 l_inventory_item_id,
3190 l_line_number,
3191 l_blanket_number,
3192 l_blanket_line_number,
3193 l_header_id
3194 FROM oe_order_lines
3195 WHERE line_id = l_line_id;
3196 ER#7479609 end*/
3197
3198 --ER#7479609 start
3199 SELECT * INTO l_line_rec
3200 FROM oe_order_lines
3201 WHERE line_id = l_line_id;
3202 --ER#7479609 end
3203
3204 --OE_LINE_UTIL.Query_Row(p_line_id => l_line_id,
3205 -- x_line_rec => l_line_rec);
3206
3207 EXCEPTION
3208 WHEN OTHERS THEN
3209 IF l_debug_level > 0 THEN
3210 oe_debug_pub.add( 'NO HEADER ID FOR THIS LINE' ) ;
3211 END IF;
3212 --RAISE NO_DATA_FOUND;
3213 null;
3214 END;
3215 --l_header_id := l_line_rec.header_id;
3216 IF l_debug_level > 0 THEN
3217 oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
3218 END IF;
3219 IF l_debug_level > 0 THEN
3220 oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
3221 END IF;
3222 END IF;
3223
3224 --ER#7479609 start
3225 IF p_entity_code = OE_GLOBALS.G_ENTITY_HEADER_PAYMENT THEN
3226 G_PAYMENT_HOLD_APPLIED := 'N';
3227 BEGIN
3228 l_payment_type_tab.delete;
3229
3230 Select payment_type_code payment_type
3231 BULK COLLECT INTO l_payment_type_tab
3232 FROM OE_PAYMENTS
3233 WHERE header_id=l_header_rec.header_id
3234 AND line_id IS NULL;
3235
3236 EXCEPTION
3237 WHEN OTHERS THEN
3238 null;
3239 END;
3240
3241 FOR i in 1 .. l_payment_type_tab.count LOOP
3242 evaluate_holds
3243 (p_entity_code => OE_Globals.G_ENTITY_HEADER
3244 , p_entity_id => l_header_rec.header_id
3245 , p_hold_entity_code => 'P'
3246 , p_hold_entity_id => l_payment_type_tab(i)
3247 , x_return_status =>l_return_status
3248 , x_msg_count => l_msg_count
3249 , x_msg_data => l_msg_data
3250 );
3251 END LOOP;
3252 l_payment_type_tab.delete;
3253 RETURN;
3254 END IF;
3255 --ER#7479609 end
3256
3257 --ER#7479609 start
3258 l_all_del_pay := 'Y';
3259
3260 -- ER#3667551 , commented due to Internal testing,
3261 -- When Payment Type is updated at Order Header Level then
3262 -- existing Payment related holds were not being released due to
3263 -- the below if condition
3264 /*IF G_HDR_PAYMENT = 'Y' and p_hold_entity_code = 'P' THEN
3265 l_all_del_pay := 'N';
3266 END IF;*/
3267 -- ER#3667551 end
3268 --ER#7479609 end
3269
3270 --ER#7479609 OPEN prev_hold;
3271 OPEN prev_hold(l_all_del_pay); --ER#7479609
3272 LOOP
3273 IF l_debug_level > 0 THEN
3274 oe_debug_pub.add( 'RETRIEVING PREV. HOLD RECORD' , 1 ) ;
3275 END IF;
3276
3277 -- FETCH prev_hold INTO l_hold_entity_id, l_hold_source_id;
3278 --ER#7479609 FETCH prev_hold INTO l_hold_entity_id, l_order_hold_id;
3279 FETCH prev_hold INTO l_hold_entity_id, l_order_hold_id,l_hold_entity_code; --ER#7479609
3280
3281 IF (prev_hold%notfound) THEN
3282 IF l_debug_level > 0 THEN
3283 oe_debug_pub.add( 'PREV_HOLD NOT FOUND , EXITING.' , 1 ) ;
3284 END IF;
3285 EXIT;
3286 END IF;
3287
3288 IF l_hold_entity_id = p_hold_entity_id THEN
3289 IF l_debug_level > 0 THEN
3290 oe_debug_pub.add( 'SAME ENTITY ID: NEITHER APPLY NOR REMOVE' ) ;
3291 END IF;
3292 --RETURN;
3293 exit;
3294 END IF;
3295
3296 --ER#7479609 start
3297 IF (l_hold_entity_code='P' and G_PAYMENT_HOLD_APPLIED = 'Y' and p_entity_code = OE_GLOBALS.G_ENTITY_HEADER) THEN
3298 IF l_debug_level > 0 THEN
3299 oe_debug_pub.add( 'NOT DELETING HOLD' , 1 ) ;
3300 END IF;
3301 ELSE
3302 --ER#7479609 end
3303 IF l_debug_level > 0 THEN
3304 oe_debug_pub.add( 'DELETING HOLD' , 1 ) ;
3305 END IF;
3306 DELETE FROM OE_ORDER_HOLDS
3307 WHERE ORDER_HOLD_ID = l_order_hold_id;
3308
3309 IF l_debug_level > 0 THEN
3310 oe_debug_pub.add( '(1)L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3311 END IF;
3312 IF l_debug_level > 0 THEN
3313 oe_debug_pub.add( 'HOLD REMOVED' ) ;
3314 END IF;
3315 END IF; --ER#7479609
3316 --fnd_message.set_name('ONT','OE_HOLD_REMOVED');
3317 --OE_MSG_PUB.ADD;
3318 END LOOP;
3319 CLOSE prev_hold;
3320
3321 -- prev_hold_entity2
3322
3323 -- Check for previous hold if the second entity is ('C','S','B','W','H','L')
3324 --ER#7479609 if p_hold_entity_code in ('C','S','B','W','H','L') then
3325 --9927494 if p_hold_entity_code in ('B','CD','C','I','O','OT','P','PL','PR','H','SC','S','SM','TM','W','D') then --ER#7479609
3326 if p_hold_entity_code in ('B','CB','C','D','LT','OI','OT','PT','P','PL','PR','L','H','SC','SM','S','ST','T','TC','W','CN','EC','EN','EL') then --9927494 -- ER# 11824468, added 'CN' --ER# 12571983 added 'EC' 'EN'
3327 IF l_debug_level > 0 THEN
3328 oe_debug_pub.add( 'CHECKING PREV HOLD SOURCES FOR SECOND ENTITY' ) ;
3329 END IF;
3330
3331 --ER#7479609 start
3332 l_all_del_pay := 'Y';
3333 -- ER#3667551 , commented due to Internal testing,
3334 -- When Payment Type is updated at Order Header Level then
3335 -- existing Payment related holds were not being released due to
3336 -- the below if condition
3337
3338 /*IF G_HDR_PAYMENT = 'Y' and p_hold_entity_code = 'P' THEN
3339 l_all_del_pay := 'N';
3340 END IF;*/
3341 --ER#3667551
3342 --ER#7479609 end
3343 --ER#7479609 OPEN prev_hold_entity2;
3344 OPEN prev_hold_entity2(l_all_del_pay); --ER#7479609
3345 LOOP
3346 IF l_debug_level > 0 THEN
3347 oe_debug_pub.add( 'RETRIEVING PREV. HOLD RECORD FOR ENTITY2' , 1 ) ;
3348 END IF;
3349
3350 -- FETCH prev_hold INTO l_hold_entity_id, l_hold_source_id;
3351 --ER#7479609 FETCH prev_hold_entity2 INTO l_hold_entity_id, l_order_hold_id;
3352 FETCH prev_hold_entity2 INTO l_hold_entity_id, l_order_hold_id,l_hold_entity_code; --ER#7479609
3353
3354 IF (prev_hold_entity2%notfound) THEN
3355 IF l_debug_level > 0 THEN
3356 oe_debug_pub.add( 'PREV_HOLD_ENTITY2 NOT FOUND , EXITING' , 1 ) ;
3357 END IF;
3358 EXIT;
3359 END IF;
3360
3361 IF l_hold_entity_id = p_hold_entity_id THEN
3362 IF l_debug_level > 0 THEN
3363 oe_debug_pub.add( 'SAME ENTITY ID2: NEITHER APPLY NOR REMOVE' ) ;
3364 END IF;
3365 --RETURN;
3366 EXIT;
3367 END IF;
3368
3369 --ER#7479609 start
3370 IF (l_hold_entity_code = 'P' and G_PAYMENT_HOLD_APPLIED = 'Y' and p_entity_code = OE_GLOBALS.G_ENTITY_HEADER) THEN
3371 IF l_debug_level > 0 THEN
3372 oe_debug_pub.add( 'NOT DELETING HOLD' , 1 ) ;
3373 END IF;
3374 ELSE
3375 --ER#7479609 end
3376 IF l_debug_level > 0 THEN
3377 oe_debug_pub.add( 'DELETING HOLD' , 1 ) ;
3378 END IF;
3379 DELETE FROM OE_ORDER_HOLDS
3380 WHERE ORDER_HOLD_ID = l_order_hold_id;
3381
3382 IF l_debug_level > 0 THEN
3383 oe_debug_pub.add( '(2)L_HOLD_ENTITY_ID/P_HOLD_ENTITY_ID/L_ORDER_HOLD_ID:' || L_HOLD_ENTITY_ID || '/' || P_HOLD_ENTITY_ID || '/' || L_ORDER_HOLD_ID ) ;
3384 END IF;
3385 IF l_debug_level > 0 THEN
3386 oe_debug_pub.add( 'HOLD REMOVED FOR SECOND ENTITY' ) ;
3387 END IF;
3388 END IF; --ER#7479609
3389 --fnd_message.set_name('ONT','OE_HOLD_REMOVED');
3390 --OE_MSG_PUB.ADD;
3391 END LOOP;
3392 CLOSE prev_hold_entity2;
3393
3394 end if; -- p_hold_entity_code in ('C','S','B','W')
3395
3396 G_HDR_PAYMENT := 'N'; --ER#7479609
3397
3398 OPEN curr_hold_source;
3399 LOOP
3400 IF l_debug_level > 0 THEN
3401 oe_debug_pub.add( '(1)RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
3402 END IF;
3403 FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
3404 l_hold_entity_code,l_hold_entity_id,
3405 l_hold_entity_code2,l_hold_entity_id2;
3406
3407 IF (curr_hold_source%notfound) THEN
3408 IF l_debug_level > 0 THEN
3409 oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
3410 END IF;
3411 EXIT;
3412 END IF;
3413
3414 IF l_debug_level > 0 THEN
3415 oe_debug_pub.add( '(3)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3416 oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
3417 END IF;
3418 -- If second entity is not null, check if order/line is eligible for hold
3419 l_create_order_hold_flag := 'Y';
3420 IF l_hold_entity_code2 is not null THEN
3421 l_create_order_hold_flag := 'N';
3422 /*ER#7479609 start
3423 IF l_hold_entity_code2 = 'C' THEN
3424 IF l_sold_to_org_id = l_hold_entity_id2 THEN
3425 l_create_order_hold_flag := 'Y';
3426 END IF;
3427 ELSIF l_hold_entity_code2 = 'B' THEN
3428 IF l_invoice_to_org_id = l_hold_entity_id2 THEN
3429 l_create_order_hold_flag := 'Y';
3430 END IF;
3431 ELSIF l_hold_entity_code2 = 'S' THEN
3432 IF l_ship_to_org_id = l_hold_entity_id2 THEN
3433 l_create_order_hold_flag := 'Y';
3434 END IF;
3435 ELSIF l_hold_entity_code2 = 'W' THEN
3436 IF l_ship_from_org_id = l_hold_entity_id2 THEN
3437 l_create_order_hold_flag := 'Y';
3438 END IF;
3439 ELSIF l_hold_entity_code2 = 'H' THEN
3440 IF l_blanket_number = l_hold_entity_id2 THEN
3441 l_create_order_hold_flag := 'Y';
3442 END IF;
3443 ELSIF l_hold_entity_code2 = 'L' THEN
3444 IF l_blanket_line_number = l_hold_entity_id2 THEN
3445 l_create_order_hold_flag := 'Y';
3446 END IF;
3447 END IF;
3448 ER#7479609 end*/
3449
3450 --ER#7479609 start
3451 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3452
3453 BEGIN
3454 l_payment_type_tab.delete;
3455
3456 SELECT V.payment_type
3457 BULK COLLECT INTO l_payment_type_tab
3458 FROM
3459 (Select payment_type_code payment_type
3460 FROM OE_PAYMENTS
3461 WHERE header_id=l_header_rec.header_id
3462 AND line_id IS NULL
3463 UNION
3464 SELECT payment_type_code payment_type
3465 FROM OE_ORDER_HEADERS_ALL
3466 WHERE header_id=l_header_rec.header_id) V;
3467
3468 EXCEPTION
3469 WHEN OTHERS THEN
3470 null;
3471 END;
3472
3473 IF l_hold_entity_code2 = 'OT' THEN
3474 IF l_header_rec.order_type_id = l_hold_entity_id2 THEN
3475 l_create_order_hold_flag := 'Y';
3476 END IF;
3477 /*9927494 start
3478 ELSIF l_hold_entity_code2 = 'PT' THEN
3479 -- IF l_header_rec.payment_type_code = l_hold_entity_id2 THEN -- commneted for bug 9927494
3480 IF l_header_rec.payment_term_id = l_hold_entity_id2 THEN -- Added for bug 9927494
3481 l_create_order_hold_flag := 'Y';
3482 END IF;
3483 9927494 end*/
3484 ELSIF l_hold_entity_code2 = 'TC' THEN
3485 IF l_header_rec.transactional_curr_code = l_hold_entity_id2 THEN
3486 l_create_order_hold_flag := 'Y';
3487 END IF;
3488 ELSIF l_hold_entity_code2 = 'SC' THEN
3489 IF l_header_rec.sales_channel_code = l_hold_entity_id2 THEN
3490 l_create_order_hold_flag := 'Y';
3491 END IF;
3492 ELSIF l_hold_entity_code2 = 'P' THEN
3493 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
3494 IF (l_payment_type_tab(pay_idx) = l_hold_entity_id2) THEN
3495 l_create_order_hold_flag := 'Y';
3496 EXIT;
3497 END IF;
3498 END LOOP;
3499 END IF;
3500
3501 --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3502 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3503 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
3504
3505 BEGIN
3506 l_payment_type_tab.delete;
3507
3508 Select payment_type_code payment_type
3509 BULK COLLECT INTO l_payment_type_tab
3510 FROM OE_PAYMENTS
3511 WHERE header_id=l_header_rec.header_id
3512 AND line_id IS NOT NULL;
3513
3514 EXCEPTION
3515 WHEN OTHERS THEN
3516 null;
3517 END;
3518
3519 IF l_hold_entity_code2 = 'C' THEN
3520 IF l_line_rec.sold_to_org_id = l_hold_entity_id2 THEN
3521 l_create_order_hold_flag := 'Y';
3522 END IF;
3523 --ER# 11824468 start
3524 ELSIF l_hold_entity_code2 = 'CN' THEN
3525 IF l_line_rec.sold_to_org_id = l_hold_entity_id2 THEN
3526 l_create_order_hold_flag := 'Y';
3527 END IF;
3528 --ER# 11824468 end
3529 --ER#12571983 start 'EC' 'EN'
3530 ELSIF l_hold_entity_code2 IN ('EC','EN') THEN
3531 IF l_line_rec.end_customer_id = l_hold_entity_id2 THEN
3532 l_create_order_hold_flag := 'Y';
3533 END IF;
3534 ELSIF l_hold_entity_code2 ='EL' THEN
3535 IF l_line_rec.end_customer_site_use_id = l_hold_entity_id2 THEN
3536 l_create_order_hold_flag := 'Y';
3537 END IF;
3538 --ER#12571983 end 'EC' 'EN'
3539 ELSIF l_hold_entity_code2 = 'B' THEN
3540 IF l_line_rec.invoice_to_org_id = l_hold_entity_id2 THEN
3541 l_create_order_hold_flag := 'Y';
3542 END IF;
3543 ELSIF l_hold_entity_code2 = 'S' THEN
3544 IF l_line_rec.ship_to_org_id = l_hold_entity_id2 THEN
3545 l_create_order_hold_flag := 'Y';
3546 END IF;
3547 ELSIF l_hold_entity_code2 = 'W' THEN
3548 IF l_line_rec.ship_from_org_id = l_hold_entity_id2 THEN
3549 l_create_order_hold_flag := 'Y';
3550 END IF;
3551 ELSIF l_hold_entity_code2 = 'O' THEN
3552 IF l_line_rec.header_id = l_hold_entity_id2 THEN
3553 l_create_order_hold_flag := 'Y';
3554 END IF;
3555 ELSIF l_hold_entity_code2 = 'H' THEN
3556 IF l_line_rec.blanket_number = l_hold_entity_id2 THEN
3557 l_create_order_hold_flag := 'Y';
3558 END IF;
3559 ELSIF l_hold_entity_code2 = 'L' THEN
3560 IF l_line_rec.blanket_line_number = l_hold_entity_id2 THEN
3561 l_create_order_hold_flag := 'Y';
3562 END IF;
3563
3564 ELSIF l_hold_entity_code2 = 'LT' THEN
3565 IF l_line_rec.line_type_id = l_hold_entity_id2 THEN
3566 l_create_order_hold_flag := 'Y';
3567 END IF;
3568 ELSIF l_hold_entity_code2 = 'SM' THEN
3569 IF l_line_rec.shipping_method_code = l_hold_entity_id2 THEN
3570 l_create_order_hold_flag := 'Y';
3571 END IF;
3572 ELSIF l_hold_entity_code2 = 'D' THEN
3573 IF l_line_rec.deliver_to_org_id = l_hold_entity_id2 THEN
3574 l_create_order_hold_flag := 'Y';
3575 END IF;
3576 ELSIF l_hold_entity_code2 = 'ST' THEN
3577 IF l_line_rec.source_type_code = l_hold_entity_id2 THEN
3578 l_create_order_hold_flag := 'Y';
3579 END IF;
3580 ELSIF l_hold_entity_code2 = 'PL' THEN
3581 IF l_line_rec.price_list_id = l_hold_entity_id2 THEN
3582 l_create_order_hold_flag := 'Y';
3583 END IF;
3584 ELSIF l_hold_entity_code2 = 'PR' THEN
3585 IF l_line_rec.project_id = l_hold_entity_id2 THEN
3586 l_create_order_hold_flag := 'Y';
3587 END IF;
3588 ELSIF l_hold_entity_code2 = 'PT' THEN
3589 IF l_line_rec.payment_term_id = l_hold_entity_id2 THEN
3590 l_create_order_hold_flag := 'Y';
3591 END IF;
3592
3593 ELSIF l_hold_entity_code2 = 'OI' THEN
3594 IF l_line_rec.inventory_item_id = l_hold_entity_id2 THEN
3595 l_create_order_hold_flag := 'Y';
3596 END IF;
3597 ELSIF l_hold_entity_code2 = 'T' THEN
3598 IF l_line_rec.task_id = l_hold_entity_id2 THEN
3599 l_create_order_hold_flag := 'Y';
3600 END IF;
3601 ELSIF l_hold_entity_code2 = 'CB' THEN
3602 IF l_line_rec.created_by = l_hold_entity_id2 THEN
3603 l_create_order_hold_flag := 'Y';
3604 END IF;
3605 ELSIF l_hold_entity_code2 = 'P' and p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT THEN --ER#7479609
3606 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
3607 IF (l_payment_type_tab(pay_idx) = l_hold_entity_id2) THEN
3608 l_create_order_hold_flag := 'Y';
3609 EXIT;
3610 END IF;
3611 END LOOP;
3612 END IF;
3613
3614 END IF;
3615 --ER#7479609 end
3616 END IF; -- l_hold_entity_code2 is not null
3617 IF l_debug_level > 0 THEN
3618 oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
3619 END IF;
3620
3621 --ER#7479609 start
3622 IF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3623 -- IF l_hold_entity_code = 'C' and --ER# 11824468
3624 IF l_hold_entity_code IN('C','CN') and --ER# 11824468 added 'CN',
3625 (l_hold_entity_code2 IS NULL OR
3626 l_hold_entity_code2 = 'OT' OR
3627 --9927494 l_hold_entity_code2 = 'PT' OR
3628 l_hold_entity_code2 = 'P' OR --9927494
3629 l_hold_entity_code2 = 'TC' OR
3630 l_hold_entity_code2 = 'SC'
3631 ) THEN
3632 l_create_order_hold_flag := 'N';
3633 --ER 3667551 start
3634 If p_hold_entity_code = 'BTL' and l_hold_entity_code ='C' THEN
3635 l_create_order_hold_flag := 'Y';
3636 End If;
3637 --ER 3667551 end
3638 END IF;
3639
3640 IF l_hold_entity_code = 'OT' and (l_hold_entity_code2 = 'TC' OR l_hold_entity_code2 IS NULL)
3641 THEN
3642 l_create_order_hold_flag := 'N';
3643 END IF;
3644
3645 END IF;
3646 --ER#7479609 end
3647
3648
3649 IF l_create_order_hold_flag = 'Y' THEN
3650 l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
3651 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
3652 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
3653 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
3654 l_hold_source_rec.HOLD_ID := l_hold_id;
3655 l_hold_source_rec.hold_source_id := l_hold_source_id;
3656 l_hold_source_rec.header_id := l_header_id;
3657 l_hold_source_rec.line_id := l_line_id;
3658
3659 IF l_debug_level > 0 THEN
3660 oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3661 END IF;
3662 oe_holds_pvt.Create_Order_Holds (
3663 p_hold_source_rec => l_hold_source_rec
3664 ,x_return_status => x_return_status
3665 ,x_msg_count => x_msg_count
3666 ,x_msg_data => x_msg_data
3667 );
3668
3669 IF l_debug_level > 0 THEN
3670 oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
3671 END IF;
3672
3673 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3674 IF l_debug_level > 0 THEN
3675 oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3676 END IF;
3677 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3678 RAISE FND_API.G_EXC_ERROR;
3679 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3681 END IF;
3682 --ER#7479609 start
3683 IF x_return_status = '0' THEN
3684 G_PAYMENT_HOLD_APPLIED := 'Y';
3685 END IF;
3686 --ER#7479609 end
3687 ELSE
3688
3689 IF l_debug_level > 0 THEN
3690 oe_debug_pub.add( 'HOLD APPLIED' ) ;
3691 END IF;
3692
3693 IF p_hold_entity_code = 'C' THEN
3694 l_attribute := 'Customer';
3695 --ER# 11824468 start
3696 ELSIF p_hold_entity_code ='CN' THEN
3697 l_attribute := 'Customer';
3698 --ER# 11824468 end
3699 --ER# 3667551 start
3700 ELSIF p_hold_entity_code ='BTH' THEN
3701 l_attribute := 'Header Level Bill To Customer';
3702 ELSIF p_hold_entity_code ='BTL' THEN
3703 l_attribute := 'Line Level Bill To Customer';
3704 --ER# 3667551 end
3705 --ER#12571983 added 'EC' 'EN'
3706 ELSIF p_hold_entity_code IN ('EC','EN') THEN
3707 l_attribute := 'End Customer';
3708 --ER#12571983 added 'EC' 'EN'
3709 ELSIF p_hold_entity_code = 'I' THEN
3710 l_attribute := 'Item';
3711 --ER# 13331078 start
3712 ELSIF p_hold_entity_code = 'IC' THEN
3713 l_attribute := 'Item Category';
3714 --ER# 13331078 end
3715 ELSIF p_hold_entity_code = 'S' THEN
3716 l_attribute := 'Ship to Site';
3717 ELSIF p_hold_entity_code = 'B' THEN
3718 l_attribute := 'Bill to Site';
3719 ELSIF p_hold_entity_code = 'O' then
3720 l_attribute := 'Order';
3721 ELSIF p_hold_entity_code = 'W' then
3722 l_attribute := 'Warehouse';
3723 ELSIF p_hold_entity_code = 'H' then
3724 l_attribute := 'Blanket Number';
3725 --ER#7479609 start
3726 ELSIF l_hold_entity_code = 'TM' THEN
3727 l_attribute := 'Top Model';
3728 ELSIF l_hold_entity_code = 'PR' then
3729 l_attribute := 'Project Number';
3730 ELSIF l_hold_entity_code = 'PL' then
3731 l_attribute := 'Price List';
3732 ELSIF l_hold_entity_code = 'OT' then
3733 l_attribute := 'Order Type';
3734 ELSIF l_hold_entity_code = 'CD' THEN
3735 l_attribute := 'Creation Date';
3736 ELSIF l_hold_entity_code = 'SC' then
3737 l_attribute := 'Sales Channel Code';
3738 ELSIF l_hold_entity_code = 'P' then
3739 G_PAYMENT_HOLD_APPLIED := 'Y';
3740 l_attribute := 'Payment Type';
3741 ELSIF l_hold_entity_code = 'SM' then
3742 l_attribute := 'Shipping Method Code';
3743 --8254521 start
3744 ELSIF l_hold_entity_code = 'D' then
3745 l_attribute := 'Deliver to Site';
3746 --8254521 end
3747 --ER#7479609 end
3748 END IF;
3749 IF l_hold_entity_code2 is not null then
3750 IF l_hold_entity_code2 = 'C' THEN
3751 l_attribute := l_attribute || '/' || 'Customer';
3752 --ER# 11824468 start
3753 ELSIF l_hold_entity_code2 = 'CN' THEN
3754 l_attribute := l_attribute || '/' || 'Customer';
3755 --ER# 11824468 end
3756 --ER#12571983 added 'EC' 'EN'
3757 ELSIF l_hold_entity_code2 IN ('EC','EN') THEN
3758 l_attribute := l_attribute || '/' || 'End Customer';
3759 ELSIF l_hold_entity_code2 ='EL' THEN
3760 l_attribute := l_attribute || '/' || 'End Customer Location';
3761 --ER#12571983 added 'EC' 'EN'
3762 ELSIF l_hold_entity_code2 = 'S' THEN
3763 l_attribute := l_attribute || '/' || 'Ship to Site';
3764 ELSIF l_hold_entity_code2 = 'B' THEN
3765 l_attribute := l_attribute || '/' || 'Bill to Site';
3766 ELSIF l_hold_entity_code2 = 'W' then
3767 l_attribute := l_attribute || '/' || 'Warehouse';
3768 ELSIF l_hold_entity_code2 = 'H' THEN
3769 l_attribute := l_attribute || '/' || 'Blanket Number';
3770 ELSIF l_hold_entity_code2 = 'L' THEN
3771 l_attribute := l_attribute || '/' || 'Bl Line Number';
3772 --ER#7479609 start
3773 ELSIF l_hold_entity_code2 = 'LT' THEN
3774 l_attribute := l_attribute || '/' || 'Line Type';
3775 ELSIF l_hold_entity_code2 = 'SM' THEN
3776 l_attribute := l_attribute || '/' || 'Shipping Method Code';
3777 ELSIF l_hold_entity_code2 = 'D' then
3778 l_attribute := l_attribute || '/' || 'Deliver to Site';
3779 ELSIF l_hold_entity_code2 = 'ST' then
3780 l_attribute := l_attribute || '/' || 'Source Type Code';
3781 ELSIF l_hold_entity_code2 = 'PL' THEN
3782 l_attribute := l_attribute || '/' || 'Price List';
3783 ELSIF l_hold_entity_code2 = 'PR' THEN
3784 l_attribute := l_attribute || '/' || 'Project Number';
3785 ELSIF l_hold_entity_code2 = 'PT' THEN
3786 l_attribute := l_attribute || '/' || 'Payment Term';
3787 ELSIF l_hold_entity_code2 = 'OI' THEN
3788 l_attribute := l_attribute || '/' || 'Option Item';
3789 ELSIF l_hold_entity_code2 = 'T' then
3790 l_attribute := l_attribute || '/' || 'Task Number';
3791 ELSIF l_hold_entity_code2 = 'OT' then
3792 l_attribute := l_attribute || '/' || 'Order Type';
3793 ELSIF l_hold_entity_code2 = 'P' THEN
3794 G_PAYMENT_HOLD_APPLIED := 'Y';
3795 l_attribute := l_attribute || '/' || 'Payment Type';
3796 ELSIF l_hold_entity_code2 = 'TC' THEN
3797 l_attribute := l_attribute || '/' || 'Currency';
3798 ELSIF l_hold_entity_code2 = 'SC' then
3799 l_attribute := l_attribute || '/' || 'Sales Channel Code';
3800 ELSIF l_hold_entity_code2 = 'CB' THEN
3801 l_attribute := l_attribute || '/' || 'Created By';
3802 --ER#7479609 end
3803 END IF;
3804 end if;
3805
3806 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3807 fnd_message.set_name('ONT','OE_HLD_APPLIED');
3808 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
3809 OE_MSG_PUB.ADD;
3810 --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
3811 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3812 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN
3813 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
3814 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
3815 -- Get the line number from the line record
3816 --SELECT line_number
3817 -- INTO l_line_number
3818 -- FROM OE_ORDER_LINES
3819 -- WHERE LINE_ID = p_entity_id;
3820 --FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
3821 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_rec.line_number); --8254521
3822 OE_MSG_PUB.ADD;
3823 END IF;
3824 END IF; -- if create_order_hold was successful
3825 END IF; -- l_create_order_hold_flag = 'Y'
3826 END LOOP;
3827 CLOSE curr_hold_source;
3828
3829 -- Check for Current second entity hold if the entity is ('C','S','B','W','H')
3830 --ER#7479609 if p_hold_entity_code in ('C','S','B','W','H') then
3831 if p_hold_entity_code in ('B','CB','C','D','LT','OI','OT','PT','P','PL','PR','L','H','SC','SM','S','ST','T','TC','W','CN','EC','EN','EL') then --ER#7479609 ----ER# 11824468, added 'CN' --ER#12571983 added 'EC' 'EN'
3832 -- ADD and p_entity_code = G_ENTITY_LINE ????
3833 OPEN curr_hold_source_entity2;
3834 LOOP
3835 IF l_debug_level > 0 THEN
3836 oe_debug_pub.add( 'RETRIEVING NEW HOLD SOURCE RECORD FOR ENTITY2' , 1 ) ;
3837 END IF;
3838 FETCH curr_hold_source_entity2 INTO l_hold_source_id, l_hold_id,
3839 l_hold_entity_code,l_hold_entity_id,
3840 l_hold_entity_code2,l_hold_entity_id2;
3841
3842 IF (curr_hold_source_entity2%notfound) THEN
3843 IF l_debug_level > 0 THEN
3844 oe_debug_pub.add( 'NO HOLD SOURCE FOUND , EXISTING' , 1 ) ;
3845 END IF;
3846 EXIT;
3847 END IF;
3848
3849 IF l_debug_level > 0 THEN
3850 oe_debug_pub.add( '(4)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
3851 oe_debug_pub.add( ' '||L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
3852 END IF;
3853
3854 -- If second entity is not null, The First entity can only be I or W
3855 -- Check if order/line is eligible for hold
3856 l_create_order_hold_flag := 'N';
3857 /*ER#7479609 start
3858 IF l_hold_entity_code = 'I' THEN
3859 IF l_inventory_item_id = l_hold_entity_id THEN
3860 l_create_order_hold_flag := 'Y';
3861 END IF;
3862 ELSIF l_hold_entity_code = 'W' THEN
3863 IF l_ship_from_org_id = l_hold_entity_id THEN
3864 l_create_order_hold_flag := 'Y';
3865 END IF;
3866 ELSIF l_hold_entity_code = 'H' THEN
3867 IF l_blanket_number = l_hold_entity_id THEN
3868 l_create_order_hold_flag := 'Y';
3869 END IF;
3870 END IF;
3871 ER#7479609 end*/
3872
3873 --ER#7479609 start
3874 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
3875 IF l_hold_entity_code = 'C' THEN
3876 IF l_header_rec.sold_to_org_id = l_hold_entity_id THEN
3877 l_create_order_hold_flag := 'Y';
3878 END IF;
3879 --ER# 11824468 start
3880 ELSIF l_hold_entity_code = 'CN' THEN
3881 IF l_header_rec.sold_to_org_id = l_hold_entity_id THEN
3882 l_create_order_hold_flag := 'Y';
3883 END IF;
3884 --ER# 11824468 end
3885 ELSIF l_hold_entity_code = 'PL' THEN
3886 IF l_header_rec.price_list_id = l_hold_entity_id THEN
3887 l_create_order_hold_flag := 'Y';
3888 END IF;
3889 ELSIF l_hold_entity_code = 'OT' THEN
3890 IF l_header_rec.order_type_id = l_hold_entity_id THEN
3891 l_create_order_hold_flag := 'Y';
3892 END IF;
3893 END IF;
3894
3895 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
3896 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN
3897 --ER#7479609 end
3898 IF l_hold_entity_code = 'B' THEN
3899 IF l_line_rec.invoice_to_org_id = l_hold_entity_id THEN
3900 l_create_order_hold_flag := 'Y';
3901 END IF;
3902 ELSIF l_hold_entity_code = 'CD' THEN
3903 IF l_line_rec.creation_date = l_hold_entity_id THEN
3904 l_create_order_hold_flag := 'Y';
3905 END IF;
3906 ELSIF l_hold_entity_code = 'C' THEN
3907 IF l_line_rec.sold_to_org_id = l_hold_entity_id THEN
3908 l_create_order_hold_flag := 'Y';
3909 END IF;
3910 --ER# 11824468 start
3911 ELSIF l_hold_entity_code = 'CN' THEN
3912 IF l_line_rec.sold_to_org_id = l_hold_entity_id THEN
3913 l_create_order_hold_flag := 'Y';
3914 END IF;
3915 --ER# 11824468 end
3916 --ER#12571983 start 'EC' 'EN'
3917 ELSIF l_hold_entity_code in( 'EC','EN') THEN
3918 IF l_line_rec.end_customer_id = l_hold_entity_id THEN
3919 l_create_order_hold_flag := 'Y';
3920 END IF;
3921 --ER#12571983 end 'EC' 'EN'
3922 ELSIF l_hold_entity_code = 'I' THEN
3923 IF l_line_rec.inventory_item_id = l_hold_entity_id THEN
3924 l_create_order_hold_flag := 'Y';
3925 END IF;
3926 --ER# 13331078 start
3927 ELSIF l_hold_entity_code = 'IC' THEN
3928 BEGIN
3929 select 'Y' into l_valid_itemcat
3930 from mtl_item_categories mic,
3931 mtl_default_category_sets mdc
3932 where mic.inventory_item_id = l_line_rec.inventory_item_id
3933 and mic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
3934 and mic.category_id = l_hold_entity_id
3935 AND mdc.functional_area_id=7
3936 AND mdc.category_set_id = mic.category_set_id;
3937 EXCEPTION WHEN OTHERS THEN
3938 l_valid_itemcat := 'N';
3939 END;
3940 oe_debug_pub.add('IC- '||l_hold_entity_id||' Item- '||l_line_rec.inventory_item_id||' -Org- '||l_line_rec.ship_from_org_id||' valid-'||l_valid_itemcat);
3941 IF l_valid_itemcat = 'Y' THEN
3942 l_create_order_hold_flag := 'Y';
3943 END IF;
3944 --ER# 13331078 end
3945 ELSIF l_hold_entity_code = 'OT' THEN
3946 IF l_header_rec.order_type_id = l_hold_entity_id THEN
3947 l_create_order_hold_flag := 'Y';
3948 END IF;
3949 ELSIF l_hold_entity_code = 'PL' THEN
3950 IF l_line_rec.price_list_id = l_hold_entity_id THEN
3951 l_create_order_hold_flag := 'Y';
3952 END IF;
3953 ELSIF l_hold_entity_code = 'PR' THEN
3954 IF l_line_rec.project_id = l_hold_entity_id THEN
3955 l_create_order_hold_flag := 'Y';
3956 END IF;
3957 ELSIF l_hold_entity_code = 'H' THEN
3958 IF l_line_rec.blanket_number = l_hold_entity_id THEN
3959 l_create_order_hold_flag := 'Y';
3960 END IF;
3961 ELSIF l_hold_entity_code = 'S' THEN
3962 IF l_line_rec.ship_to_org_id = l_hold_entity_id THEN
3963 l_create_order_hold_flag := 'Y';
3964 END IF;
3965 ELSIF l_hold_entity_code = 'TM' THEN
3966 IF l_line_rec.inventory_item_id = l_hold_entity_id THEN
3967 l_create_order_hold_flag := 'Y';
3968 END IF;
3969 ELSIF l_hold_entity_code = 'W' THEN
3970 IF l_line_rec.ship_from_org_id = l_hold_entity_id THEN
3971 l_create_order_hold_flag := 'Y';
3972 END IF;
3973 ELSIF l_hold_entity_code = 'D' THEN
3974 IF l_line_rec.deliver_to_org_id = l_hold_entity_id THEN
3975 l_create_order_hold_flag := 'Y';
3976 END IF;
3977 END IF;
3978 END IF;
3979 --ER#7479609 end
3980
3981 IF l_debug_level > 0 THEN
3982 oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
3983 END IF;
3984
3985 IF l_create_order_hold_flag = 'Y' THEN
3986 l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
3987 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
3988 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
3989 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
3990 l_hold_source_rec.HOLD_ID := l_hold_id;
3991 l_hold_source_rec.hold_source_id := l_hold_source_id;
3992 l_hold_source_rec.header_id := l_header_id;
3993 l_hold_source_rec.line_id := l_line_id;
3994
3995 IF l_debug_level > 0 THEN
3996 oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
3997 END IF;
3998 oe_holds_pvt.Create_Order_Holds (
3999 p_hold_source_rec => l_hold_source_rec
4000 ,x_return_status => x_return_status
4001 ,x_msg_count => x_msg_count
4002 ,x_msg_data => x_msg_data
4003 );
4004
4005 IF l_debug_level > 0 THEN
4006 oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
4007 END IF;
4008
4009 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4010 IF l_debug_level > 0 THEN
4011 oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4012 END IF;
4013 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4014 RAISE FND_API.G_EXC_ERROR;
4015 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4017 END IF;
4018
4019 --ER#7479609 start
4020 IF x_return_status = '0' THEN
4021 G_PAYMENT_HOLD_APPLIED := 'Y';
4022 END IF;
4023 --ER#7479609 end
4024
4025 ELSE
4026 IF l_debug_level > 0 THEN
4027 oe_debug_pub.add( 'HOLD APPLIED' ) ;
4028 END IF;
4029
4030 IF l_hold_entity_code = 'C' THEN
4031 l_attribute := 'Customer';
4032 --ER# 11824468 start
4033 ELSIF l_hold_entity_code = 'CN' THEN
4034 l_attribute := 'Customer';
4035 --ER# 11824468 end
4036 ELSIF l_hold_entity_code = 'I' THEN
4037 l_attribute := 'Item';
4038 --ER# 13331078 start
4039 ELSIF l_hold_entity_code = 'IC' THEN
4040 l_attribute := 'Item Category';
4041 --ER# 13331078 end
4042 --ER#12571983 start
4043 ELSIF l_hold_entity_code IN('EC','EN') THEN
4044 l_attribute := 'End Customer';
4045 --ER#12571983 end
4046 ELSIF l_hold_entity_code = 'S' THEN
4047 l_attribute := 'Site Use';
4048 ELSIF l_hold_entity_code = 'B' THEN
4049 l_attribute := 'Bill to Site';
4050 ELSIF l_hold_entity_code = 'O' then
4051 l_attribute := 'Order';
4052 ELSIF l_hold_entity_code = 'W' then
4053 l_attribute := 'Warehouse';
4054 ELSIF l_hold_entity_code = 'H' then
4055 l_attribute := 'Blanket Number';
4056 --ER#7479609 start
4057 ELSIF l_hold_entity_code = 'TM' THEN
4058 l_attribute := 'Top Model';
4059 ELSIF l_hold_entity_code = 'PR' then
4060 l_attribute := 'Project Number';
4061 ELSIF l_hold_entity_code = 'PL' then
4062 l_attribute := 'Price List';
4063 ELSIF l_hold_entity_code = 'OT' then
4064 l_attribute := 'Order Type';
4065 ELSIF l_hold_entity_code = 'CD' THEN
4066 l_attribute := 'Creation Date';
4067 ELSIF l_hold_entity_code = 'SC' then
4068 l_attribute := 'Sales Channel Code';
4069 ELSIF l_hold_entity_code = 'P' then
4070 G_PAYMENT_HOLD_APPLIED := 'Y';
4071 l_attribute := 'Payment Type';
4072 ELSIF l_hold_entity_code = 'SM' then
4073 l_attribute := 'Shipping Method Code';
4074 --8254521 start
4075 ELSIF l_hold_entity_code = 'D' then
4076 l_attribute := 'Deliver to Site';
4077 --8254521 end
4078 --ER#7479609 end
4079 END IF;
4080
4081 IF l_hold_entity_code2 is not null then
4082 IF l_hold_entity_code2 = 'C' THEN
4083 l_attribute := l_attribute || '/' || 'Customer';
4084 --ER# 11824468 start
4085 ELSIF l_hold_entity_code2 = 'CN' THEN
4086 l_attribute := l_attribute || '/' || 'Customer';
4087 --ER# 11824468 end
4088 --ER#12571983 start
4089 ELSIF l_hold_entity_code2 in ('EC','EN') THEN
4090 l_attribute := l_attribute || '/' || 'End Customer';
4091 ELSIF l_hold_entity_code2 in ('EL') THEN
4092 l_attribute := l_attribute || '/' || 'End Customer Location';
4093 --ER#12571983 end
4094 ELSIF l_hold_entity_code2 = 'S' THEN
4095 l_attribute := l_attribute || '/' || 'Ship to Site';
4096 ELSIF l_hold_entity_code2 = 'B' THEN
4097 l_attribute := l_attribute || '/' || 'Bill to Site';
4098 ELSIF l_hold_entity_code2 = 'W' then
4099 l_attribute := l_attribute || '/' || 'Warehouse';
4100 ELSIF l_hold_entity_code2 = 'H' then
4101 l_attribute := l_attribute || '/' || 'Blanket Number';
4102 ELSIF l_hold_entity_code2 = 'L' then
4103 l_attribute := l_attribute || '/' || 'Bl Line Number';
4104 --ER#7479609 start
4105 ELSIF l_hold_entity_code2 = 'LT' THEN
4106 l_attribute := l_attribute || '/' || 'Line Type';
4107 ELSIF l_hold_entity_code2 = 'SM' THEN
4108 l_attribute := l_attribute || '/' || 'Shipping Method Code';
4109 ELSIF l_hold_entity_code2 = 'D' then
4110 l_attribute := l_attribute || '/' || 'Deliver to Site';
4111 ELSIF l_hold_entity_code2 = 'ST' then
4112 l_attribute := l_attribute || '/' || 'Source Type Code';
4113 ELSIF l_hold_entity_code2 = 'PL' THEN
4114 l_attribute := l_attribute || '/' || 'Price List';
4115 ELSIF l_hold_entity_code2 = 'PR' THEN
4116 l_attribute := l_attribute || '/' || 'Project Number';
4117 ELSIF l_hold_entity_code2 = 'PT' THEN
4118 l_attribute := l_attribute || '/' || 'Payment Term';
4119 ELSIF l_hold_entity_code2 = 'OI' THEN
4120 l_attribute := l_attribute || '/' || 'Option Item';
4121 ELSIF l_hold_entity_code2 = 'T' then
4122 l_attribute := l_attribute || '/' || 'Task Number';
4123 ELSIF l_hold_entity_code2 = 'OT' then
4124 l_attribute := l_attribute || '/' || 'Order Type';
4125 ELSIF l_hold_entity_code2 = 'P' THEN
4126 G_PAYMENT_HOLD_APPLIED := 'Y';
4127 l_attribute := l_attribute || '/' || 'Payment Type';
4128 ELSIF l_hold_entity_code2 = 'TC' THEN
4129 l_attribute := l_attribute || '/' || 'Currency';
4130 ELSIF l_hold_entity_code2 = 'SC' then
4131 l_attribute := l_attribute || '/' || 'Sales Channel Code';
4132 ELSIF l_hold_entity_code2 = 'CB' THEN
4133 l_attribute := l_attribute || '/' || 'Created By';
4134 --ER#7479609 end
4135 END IF;
4136 end if;
4137
4138 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4139 fnd_message.set_name('ONT','OE_HLD_APPLIED');
4140 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4141 OE_MSG_PUB.ADD;
4142 --ER#7479609 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4143 ELSIF (p_entity_code = OE_Globals.G_ENTITY_LINE OR
4144 p_entity_code = OE_GLOBALS.G_ENTITY_LINE_PAYMENT) THEN --ER#7479609
4145 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
4146 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4147 -- Get the line number from the line record
4148 --SELECT line_number
4149 -- INTO l_line_number
4150 -- FROM OE_ORDER_LINES
4151 -- WHERE LINE_ID = p_entity_id;
4152 --FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
4153 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_rec.line_number); --8254521
4154 OE_MSG_PUB.ADD;
4155 END IF;
4156 END IF; -- if apply hold was successful
4157 END IF; -- l_create_order_hold_flag = 'Y'
4158 END LOOP;
4159 CLOSE curr_hold_source_entity2;
4160 end if;
4161
4162 IF l_debug_level > 0 THEN
4163 oe_debug_pub.add( 'EXITING EVALUATE_HOLDS' , 1 ) ;
4164 END IF;
4165
4166 EXCEPTION
4167
4168 WHEN FND_API.G_EXC_ERROR THEN
4169 IF (prev_hold%isopen) THEN
4170 CLOSE prev_hold;
4171 END IF;
4172 IF (curr_hold_source%isopen) THEN
4173 CLOSE curr_hold_source;
4174 END IF;
4175 x_return_status := FND_API.G_RET_STS_ERROR;
4176
4177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4178 IF (prev_hold%isopen) THEN
4179 CLOSE prev_hold;
4180 END IF;
4181 IF (curr_hold_source%isopen) THEN
4182 CLOSE curr_hold_source;
4183 END IF;
4184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4185
4186 WHEN OTHERS THEN
4187 IF (prev_hold%isopen) THEN
4188 CLOSE prev_hold;
4189 END IF;
4190 IF (curr_hold_source%isopen) THEN
4191 CLOSE curr_hold_source;
4192 END IF;
4193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4194
4195 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4196 THEN
4197 OE_MSG_PUB.Add_Exc_Msg
4198 ( G_PKG_NAME
4199 , 'evaluate_holds'
4200 );
4201 END IF;
4202
4203 END evaluate_holds;
4204
4205
4206
4207 /********************************/
4208 /* EVALUATE_HOLDS_POST_WRITE */
4209 /********************************/
4210 PROCEDURE eval_post_write_header
4211 ( p_entity_code IN VARCHAR2
4212 , p_entity_id IN NUMBER
4213 , p_hold_entity_code IN VARCHAR2
4214 --ER#7479609 , p_hold_entity_id IN NUMBER
4215 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
4216 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4217 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
4218 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4219 )
4220 IS
4221 l_header_id NUMBER DEFAULT NULL;
4222 l_line_id NUMBER DEFAULT NULL;
4223 l_hold_source_id NUMBER DEFAULT NULL;
4224 l_order_hold_id NUMBER DEFAULT NULL;
4225 l_line_number NUMBER DEFAULT NULL;
4226 l_return_status VARCHAR2(30);
4227 l_msg_count NUMBER;
4228 l_msg_data VARCHAR2(2000);
4229 l_attribute VARCHAR2(30);
4230 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
4231 l_hold_entity_code VARCHAR2(1);
4232 --ER#7479609 l_hold_entity_id NUMBER;
4233 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
4234 --ER#7479609 l_hold_entity_code2 VARCHAR2(1);
4235 l_hold_entity_code2 oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4236 --ER#7479609 l_hold_entity_id2 NUMBER;
4237 l_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
4238 l_hold_id NUMBER;
4239
4240 --ER#7479609 m_hold_entity_code VARCHAR2(1);
4241 m_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4242 --ER#7479609 m_hold_entity_id NUMBER;
4243 m_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
4244 --ER#7479609 m_hold_entity_code2 VARCHAR2(1);
4245 m_hold_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
4246 --ER#7479609 m_hold_entity_id2 NUMBER;
4247 m_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
4248 m_counter NUMBER;
4249
4250
4251 l_create_order_hold_flag VARCHAR2(1) := 'Y';
4252 --
4253 CURSOR curr_hold_source(p_hold_entity_code VARCHAR2,
4254 p_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE) IS --ER#7479609
4255 --ER#7479609 p_hold_entity_id NUMBER) IS
4256 SELECT HS.hold_source_id, hs.hold_id,
4257 hs.hold_entity_code, hs.hold_entity_id,
4258 hs.hold_entity_code2,hs.hold_entity_id2
4259 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4260 WHERE HLD.HOLD_ID = HS.HOLD_ID
4261 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4262 ROUND( SYSDATE )
4263 AND HS.RELEASED_FLAG = 'N'
4264 AND SYSDATE
4265 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4266 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4267 AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
4268 AND HS.HOLD_ENTITY_ID = p_hold_entity_id;
4269 --6766981 AND rownum=1; --5999405
4270
4271
4272 --
4273 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4274 --
4275 BEGIN
4276 x_return_status := FND_API.G_RET_STS_SUCCESS;
4277
4278 IF l_debug_level > 0 THEN
4279 oe_debug_pub.add( 'ENTERING EVAL_POST_WRITE_HEADER' , 1 ) ;
4280 END IF;
4281
4282
4283 IF l_debug_level > 0 THEN
4284 oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4285 END IF;
4286
4287 l_header_id := p_entity_id;
4288
4289 IF l_debug_level > 0 THEN
4290 oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4291 END IF;
4292
4293 m_hold_entity_code := p_hold_entity_code;
4294 m_hold_entity_id := p_hold_entity_id;
4295
4296 IF l_debug_level > 0 THEN
4297 oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4298 END IF;
4299 IF m_hold_entity_id IS NOT NULL THEN
4300 OPEN curr_hold_source(m_hold_entity_code, m_hold_entity_id);
4301 LOOP
4302 IF l_debug_level > 0 THEN
4303 oe_debug_pub.add( '(2)RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
4304 END IF;
4305 FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
4306 l_hold_entity_code,l_hold_entity_id,
4307 l_hold_entity_code2,l_hold_entity_id2;
4308
4309 IF (curr_hold_source%notfound) THEN
4310 IF l_debug_level > 0 THEN
4311 oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4312 END IF;
4313 EXIT;
4314 END IF;
4315
4316 IF l_debug_level > 0 THEN
4317 oe_debug_pub.add( '(5)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4318 oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
4319 END IF;
4320
4321 l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
4322 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
4323 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
4324 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
4325 l_hold_source_rec.HOLD_ID := l_hold_id;
4326 l_hold_source_rec.hold_source_id := l_hold_source_id;
4327 l_hold_source_rec.header_id := l_header_id;
4328 l_hold_source_rec.line_id := l_line_id;
4329
4330 IF l_debug_level > 0 THEN
4331 oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4332 END IF;
4333 oe_holds_pvt.Create_Order_Holds (
4334 p_hold_source_rec => l_hold_source_rec
4335 ,x_return_status => x_return_status
4336 ,x_msg_count => x_msg_count
4337 ,x_msg_data => x_msg_data
4338 );
4339
4340
4341 IF l_debug_level > 0 THEN
4342 oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
4343 END IF;
4344
4345 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4346 IF l_debug_level > 0 THEN
4347 oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
4348 END IF;
4349 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4350 RAISE FND_API.G_EXC_ERROR;
4351 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4353 END IF;
4354 ELSE
4355 IF l_debug_level > 0 THEN
4356 oe_debug_pub.add( 'HOLD APPLIED' ) ;
4357 END IF;
4358
4359 l_attribute := 'Customer';
4360
4361
4362 fnd_message.set_name('ONT','OE_HLD_APPLIED');
4363 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
4364 OE_MSG_PUB.ADD;
4365 END IF; -- if create_order_hold was successful
4366 END LOOP;
4367 CLOSE curr_hold_source;
4368 END IF; /* IF m_hold_entity_id IS NOT NULL THEN */
4369
4370 IF l_debug_level > 0 THEN
4371 oe_debug_pub.add( 'EXITING EVAL_POST_WRITE_HEADER' , 1 ) ;
4372 END IF;
4373
4374 EXCEPTION
4375
4376 WHEN FND_API.G_EXC_ERROR THEN
4377 IF (curr_hold_source%isopen) THEN
4378 CLOSE curr_hold_source;
4379 END IF;
4380 x_return_status := FND_API.G_RET_STS_ERROR;
4381
4382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4383 IF (curr_hold_source%isopen) THEN
4384 CLOSE curr_hold_source;
4385 END IF;
4386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4387
4388 WHEN OTHERS THEN
4389 IF (curr_hold_source%isopen) THEN
4390 CLOSE curr_hold_source;
4391 END IF;
4392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4393
4394 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4395 THEN
4396 OE_MSG_PUB.Add_Exc_Msg
4397 ( G_PKG_NAME
4398 , 'eval_post_write_header'
4399 );
4400 END IF;
4401
4402 END eval_post_write_header;
4403
4404
4405
4406 /********************************/
4407 /* EVALUATE_HOLDS_POST_WRITE */
4408 /********************************/
4409
4410 PROCEDURE evaluate_holds_post_write
4411 ( p_entity_code IN VARCHAR2
4412 , p_entity_id IN NUMBER
4413 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4414 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
4415 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4416 )
4417 IS
4418 l_header_id NUMBER DEFAULT NULL;
4419 l_line_id NUMBER DEFAULT NULL;
4420 l_hold_source_id NUMBER DEFAULT NULL;
4421 l_order_hold_id NUMBER DEFAULT NULL;
4422 l_line_number NUMBER DEFAULT NULL;
4423 l_return_status VARCHAR2(30);
4424 l_msg_count NUMBER;
4425 l_msg_data VARCHAR2(2000);
4426 temp NUMBER DEFAULT NULL;
4427 l_attribute VARCHAR2(50);
4428 --
4429 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
4430 --ER#7479609 l_hold_entity_code VARCHAR2(1);
4431 l_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4432 --ER#7479609 l_hold_entity_id NUMBER;
4433 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
4434 --ER#7479609
4435 l_hold_entity_code2 oe_hold_sources_all.hold_entity_code2%TYPE; --ER#7479609
4436
4437 --ER#7479609 l_hold_entity_id2 NUMBER;
4438 l_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
4439 l_hold_id NUMBER;
4440 l_inventory_item_id NUMBER;
4441 l_sold_to_org_id NUMBER;
4442 l_invoice_to_org_id NUMBER;
4443 l_ship_to_org_id NUMBER;
4444 l_ship_from_org_id NUMBER;
4445
4446 --ER#7479609 m_hold_entity_code VARCHAR2(1);
4447 m_hold_entity_code oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4448 --ER#7479609 m_hold_entity_id NUMBER;
4449 m_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
4450 --ER#7479609 m_hold_entity_code2 VARCHAR2(1);
4451 m_hold_entity_code2 oe_hold_sources_all.hold_entity_code%TYPE; --ER#7479609
4452 --ER#7479609 m_hold_entity_id2 NUMBER;
4453 m_hold_entity_id2 oe_hold_sources_all.hold_entity_id2%TYPE; --ER#7479609
4454 m_counter NUMBER;
4455 l_blanket_number NUMBER;
4456 l_blanket_line_number NUMBER;
4457
4458
4459
4460 l_create_order_hold_flag VARCHAR2(1) := 'Y';
4461 --
4462 /*8602364 start
4463 CURSOR curr_hold_source(p_hold_entity_code VARCHAR2,
4464 p_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE) IS --ER#7479609
4465 --ER#7479609 p_hold_entity_id NUMBER) IS
4466 SELECT HS.hold_source_id, hs.hold_id,
4467 hs.hold_entity_code, hs.hold_entity_id,
4468 hs.hold_entity_code2,hs.hold_entity_id2
4469 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4470 WHERE HLD.HOLD_ID = HS.HOLD_ID
4471 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4472 ROUND( SYSDATE )
4473 AND HS.RELEASED_FLAG = 'N'
4474 AND SYSDATE
4475 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4476 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4477 --ER#7479609 start
4478 AND DECODE(p_hold_entity_code,'OI',HS.HOLD_ENTITY_CODE2,HS.HOLD_ENTITY_CODE) = p_hold_entity_code
4479 AND DECODE(p_hold_entity_code,'OI',HS.HOLD_ENTITY_ID2,HS.HOLD_ENTITY_ID) = p_hold_entity_id;
4480 --ER#7479609 end
4481 8602364 end*/
4482 /*ER#7479609 start
4483 AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
4484 AND HS.HOLD_ENTITY_ID = p_hold_entity_id;
4485 ER#7479609 end*/
4486
4487 --8602364 start
4488 CURSOR curr_hold_source(p_hold_entity_code VARCHAR2,
4489 p_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE) IS
4490 SELECT HS.hold_source_id, hs.hold_id,
4491 hs.hold_entity_code, hs.hold_entity_id,
4492 hs.hold_entity_code2,hs.hold_entity_id2
4493 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4494 WHERE HLD.HOLD_ID = HS.HOLD_ID
4495 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4496 ROUND( SYSDATE )
4497 AND HS.RELEASED_FLAG = 'N'
4498 AND SYSDATE
4499 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4500 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4501 AND HS.HOLD_ENTITY_CODE = p_hold_entity_code
4502 AND HS.HOLD_ENTITY_ID = p_hold_entity_id;
4503
4504
4505 CURSOR curr_hold_source_2(p_hold_entity_code VARCHAR2,
4506 p_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE) IS
4507 SELECT HS.hold_source_id, hs.hold_id,
4508 hs.hold_entity_code, hs.hold_entity_id,
4509 hs.hold_entity_code2,hs.hold_entity_id2
4510 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4511 WHERE HLD.HOLD_ID = HS.HOLD_ID
4512 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4513 ROUND( SYSDATE )
4514 AND HS.RELEASED_FLAG = 'N'
4515 AND SYSDATE
4516 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4517 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4518 AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code
4519 AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id;
4520
4521 --8602364 end
4522
4523
4524 /****************************************************************************
4525 Valid Entity Combination
4526 ^^^^^^^^^^^^^^^^^^^^^^^^
4527 > Item - Customer
4528 > Item - Customer Ship to Site
4529 > Item - Customer Bill to Site
4530 > Item - Warehouse
4531 > Warehouse - Customer
4532 > Warehouse - Customer Ship to Site
4533 > Warehouse - Customer Bill to Site
4534 > Order - Site (Bill To) (Used by Line level Credit Checking only)
4535
4536 > Item - Blanket Number
4537 > Blanket Number
4538 > Blanket Number - Customer Ship to Site
4539 > Blanket Number - Customer Bill to Site
4540 > Blanket Number - Warehouse
4541 > Blanket Number - Blanket Line Number
4542
4543 ***************************************************************************/
4544 /*
4545 CURSOR curr_hold_source_entity2(p_hold_entity_code2 VARCHAR2,
4546 p_hold_entity_id2 NUMBER) IS
4547 SELECT HS.hold_source_id, hs.hold_id,
4548 hs.hold_entity_code, hs.hold_entity_id,
4549 hs.hold_entity_code2,hs.hold_entity_id2
4550 FROM OE_HOLD_SOURCES HS, OE_HOLD_definitions HLD
4551 WHERE HLD.HOLD_ID = HS.HOLD_ID
4552 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
4553 ROUND( SYSDATE )
4554 AND HS.RELEASED_FLAG = 'N'
4555 AND SYSDATE
4556 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
4557 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
4558 AND HS.HOLD_ENTITY_CODE2 = p_hold_entity_code2
4559 AND HS.HOLD_ENTITY_ID2 = p_hold_entity_id2
4560 AND HS.HOLD_ENTITY_CODE <> 'O';
4561 */
4562 --ER#7479609 start
4563 l_header_rec OE_ORDER_HEADERS_ALL%rowtype;
4564 l_line_rec OE_ORDER_LINES_ALL%rowtype;
4565 TYPE entity_rec IS RECORD (entity_code oe_hold_sources_all.hold_entity_code%TYPE,
4566 entity_id oe_hold_sources_all.hold_entity_id%TYPE);
4567
4568 TYPE entity_tab IS TABLE OF entity_rec INDEX BY BINARY_INTEGER;
4569
4570 l_entity_tab entity_tab;
4571
4572 TYPE payment_type_tab IS TABLE OF OE_PAYMENTS.PAYMENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
4573 l_payment_type_tab payment_type_tab;
4574 pay_idx NUMBER :=0;
4575
4576 --ER#7479609 end
4577 --
4578 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4579 --
4580 -- ER#3667551 START to hold System Parameter value
4581 l_credithold_cust VARCHAR2(10) := NVL(OE_SYS_PARAMETERS.value('ONT_CREDITHOLD_TYPE'),'S') ;
4582 l_new_tbl_entry CHAR:='';
4583 l_bill_to_orgid NUMBER := 0;
4584 l_ch_level varchar2(10) := '';
4585 --ER# 3667551 END
4586 BEGIN
4587 x_return_status := FND_API.G_RET_STS_SUCCESS;
4588
4589 IF l_debug_level > 0 THEN
4590 oe_debug_pub.add( 'ENTERING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
4591 END IF;
4592 IF l_debug_level > 0 THEN
4593 oe_debug_pub.add( 'ENTITY: '|| P_ENTITY_CODE ||'/' || P_ENTITY_ID ) ;
4594 END IF;
4595
4596
4597 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4598 -- Indicates Header Level action
4599 l_header_id := p_entity_id;
4600
4601 --ER#7479609 start
4602 BEGIN
4603 SELECT * INTO l_header_rec
4604 FROM oe_order_headers
4605 WHERE header_id=l_header_id;
4606
4607 EXCEPTION
4608 WHEN OTHERS THEN
4609 IF l_debug_level > 0 THEN
4610 oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
4611 END IF;
4612 RAISE NO_DATA_FOUND;
4613 END;
4614 --ER#7479609 end
4615 IF l_debug_level > 0 THEN
4616 oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4617 END IF;
4618
4619
4620 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4621 l_line_id := p_entity_id;
4622 IF l_debug_level > 0 THEN
4623 oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4624 END IF;
4625
4626 --ER#7479609 start
4627 BEGIN
4628 SELECT OH.* INTO l_header_rec
4629 FROM oe_order_headers OH,oe_order_lines OL
4630 WHERE OH.header_id=OL.header_id
4631 AND OL.line_id=l_line_id;
4632
4633 EXCEPTION
4634 WHEN OTHERS THEN
4635 IF l_debug_level > 0 THEN
4636 oe_debug_pub.add( 'NO HEADER EXISTS' ) ;
4637 END IF;
4638 RAISE NO_DATA_FOUND;
4639 END;
4640 --ER#7479609 end
4641
4642 BEGIN
4643 /*ER#7479609 start
4644 SELECT sold_to_org_id,
4645 invoice_to_org_id,
4646 ship_to_org_id,
4647 ship_from_org_id,
4648 inventory_item_id,
4649 line_number,
4650 blanket_number,
4651 blanket_line_number,
4652 header_id
4653 INTO l_sold_to_org_id,
4654 l_invoice_to_org_id,
4655 l_ship_to_org_id,
4656 l_ship_from_org_id,
4657 l_inventory_item_id,
4658 l_line_number,
4659 l_blanket_number,
4660 l_blanket_line_number,
4661 l_header_id
4662 FROM oe_order_lines
4663 WHERE line_id = l_line_id;
4664 ER#7479609 end*/
4665 --ER#7479609 start
4666 SELECT * INTO l_line_rec
4667 FROM oe_order_lines
4668 WHERE line_id = l_line_id;
4669 --ER#7479609 end
4670
4671 EXCEPTION
4672 WHEN OTHERS THEN
4673 IF l_debug_level > 0 THEN
4674 oe_debug_pub.add( 'NO HEADER ID FOR THIS LINE' ) ;
4675 END IF;
4676 RAISE NO_DATA_FOUND;
4677 END;
4678 IF l_debug_level > 0 THEN
4679 oe_debug_pub.add( 'HEADER ID: '|| L_HEADER_ID ) ;
4680 END IF;
4681 IF l_debug_level > 0 THEN
4682 oe_debug_pub.add( 'LINE ID: '|| L_LINE_ID ) ;
4683 END IF;
4684 END IF;
4685
4686 -- FOR m_hold_entity_code IN ('B', 'S', 'W', 'I','H') LOOP
4687 --ER#7479609 start
4688 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4689
4690 BEGIN
4691 l_payment_type_tab.delete;
4692
4693 SELECT V.payment_type
4694 BULK COLLECT INTO l_payment_type_tab
4695 FROM
4696 (Select payment_type_code payment_type
4697 FROM OE_PAYMENTS
4698 WHERE header_id=l_header_rec.header_id
4699 AND line_id IS NULL
4700 UNION
4701 SELECT payment_type_code payment_type
4702 FROM OE_ORDER_HEADERS_ALL
4703 WHERE header_id=l_header_rec.header_id) V;
4704 EXCEPTION
4705 WHEN OTHERS THEN
4706 null;
4707 END;
4708 --FOR m_counter IN 1..4 LOOP --ER11824468
4709 FOR m_counter IN 1..6 LOOP --ER#11824468,-- ER#3667551
4710 IF m_counter = 1 THEN
4711 l_entity_tab(m_counter).entity_code := 'C';
4712 l_entity_tab(m_counter).entity_id := l_header_rec.sold_to_org_id;
4713 ELSIF m_counter = 2 THEN
4714 l_entity_tab(m_counter).entity_code := 'PL';
4715 l_entity_tab(m_counter).entity_id := l_header_rec.price_list_id;
4716 ELSIF m_counter = 3 THEN
4717 l_entity_tab(m_counter).entity_code := 'OT';
4718 l_entity_tab(m_counter).entity_id := l_header_rec.order_type_id;
4719 ELSIF m_counter = 4 THEN
4720 l_entity_tab(m_counter).entity_code := 'SC';
4721 l_entity_tab(m_counter).entity_id := l_header_rec.sales_channel_code;
4722 --ER# 11824468 start
4723 ELSIF m_counter = 5 THEN
4724 l_entity_tab(m_counter).entity_code := 'CN';
4725 l_entity_tab(m_counter).entity_id := l_header_rec.sold_to_org_id;
4726 --ER# 11824468 end
4727 -- ER#3667551 start
4728 -- IMP: This has to be the last entry, as the table entry might not be created
4729 --- based on the system parameter and this might lead to index miss if not kept as last entry
4730 -- If header Bill To ORg not equals Sold to org, (it is possible for Bill to Org
4731 -- Hold Source Enabled).. this is required due to 'BTH'
4732 ELSIF m_counter = 6 THEN
4733 l_bill_to_orgid:=OE_Bulk_Holds_PVT.CustAcctID_func(p_in_site_id => l_header_rec.invoice_to_org_id,
4734 p_out_IDfound=> l_new_tbl_entry);
4735 If(l_credithold_cust='BTH' AND l_header_rec.sold_to_org_id <> l_bill_to_orgid) then
4736 l_entity_tab(m_counter).entity_code := 'C';
4737 l_entity_tab(m_counter).entity_id:= l_bill_to_orgid;
4738 ELSE
4739 l_new_tbl_entry:='N';
4740 END IF;
4741 -- ER#3667551 end
4742 END IF;
4743 END LOOP;
4744 -- m_counter := 4; --ER11824468
4745 m_counter := l_entity_tab.count; --ER11824468, -- ER#3667551
4746 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
4747 m_counter := m_counter + 1;
4748 l_entity_tab(m_counter).entity_code := 'P';
4749 l_entity_tab(m_counter).entity_id := l_payment_type_tab(pay_idx);
4750 END LOOP;
4751
4752 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
4753 --ER#7479609 end
4754
4755 BEGIN
4756 l_payment_type_tab.delete;
4757
4758 Select payment_type_code payment_type
4759 BULK COLLECT INTO l_payment_type_tab
4760 FROM OE_PAYMENTS
4761 WHERE header_id=l_header_rec.header_id
4762 AND line_id IS NOT NULL;
4763 EXCEPTION
4764 WHEN OTHERS THEN
4765 null;
4766 END;
4767
4768
4769 -- FOR m_counter IN 1..13 LOOP --ER# 11824468
4770 FOR m_counter IN 1..19 LOOP --ER# 11824468 --ER# 13331078 --ER#12571983
4771 IF m_counter = 1 THEN
4772 l_entity_tab(m_counter).entity_code := 'B';
4773 l_entity_tab(m_counter).entity_id := l_line_rec.invoice_to_org_id;
4774 ELSIF m_counter = 2 THEN
4775 l_entity_tab(m_counter).entity_code := 'S';
4776 l_entity_tab(m_counter).entity_id := l_line_rec.ship_to_org_id;
4777 ELSIF m_counter = 3 THEN
4778 l_entity_tab(m_counter).entity_code := 'W';
4779 l_entity_tab(m_counter).entity_id := l_line_rec.ship_from_org_id;
4780 ELSIF m_counter = 4 THEN
4781 l_entity_tab(m_counter).entity_code := 'I';
4782 l_entity_tab(m_counter).entity_id := l_line_rec.inventory_item_id;
4783 ELSIF m_counter = 5 THEN
4784 l_entity_tab(m_counter).entity_code := 'H';
4785 l_entity_tab(m_counter).entity_id := l_line_rec.blanket_number;
4786 --ER#7479609 start
4787 ELSIF m_counter = 6 THEN
4788 l_entity_tab(m_counter).entity_code := 'C';
4789 l_entity_tab(m_counter).entity_id := l_header_rec.sold_to_org_id;
4790 ELSIF m_counter = 7 THEN
4791 l_entity_tab(m_counter).entity_code := 'TM';
4792 l_entity_tab(m_counter).entity_id := l_line_rec.inventory_item_id;
4793 ELSIF m_counter = 8 THEN
4794 l_entity_tab(m_counter).entity_code := 'PR';
4795 l_entity_tab(m_counter).entity_id := l_line_rec.project_id;
4796 ELSIF m_counter = 9 THEN
4797 l_entity_tab(m_counter).entity_code := 'OT';
4798 l_entity_tab(m_counter).entity_id := l_header_rec.order_type_id;
4799 ELSIF m_counter = 10 THEN
4800 l_entity_tab(m_counter).entity_code := 'CD';
4801 l_entity_tab(m_counter).entity_id := to_char(l_line_rec.creation_date,'DD-MON-RRRR');
4802 ELSIF m_counter = 11 THEN
4803 l_entity_tab(m_counter).entity_code := 'SM';
4804 l_entity_tab(m_counter).entity_id := l_line_rec.shipping_method_code;
4805 ELSIF m_counter = 12 THEN
4806 l_entity_tab(m_counter).entity_code := 'OI';
4807 l_entity_tab(m_counter).entity_id := l_line_rec.inventory_item_id;
4808 --8254521 start
4809 ELSIF m_counter = 13 THEN
4810 l_entity_tab(m_counter).entity_code := 'D';
4811 l_entity_tab(m_counter).entity_id := l_line_rec.deliver_to_org_id;
4812 --8254521 end
4813
4814 --ER# 11824468 start
4815 ELSIF m_counter = 14 THEN
4816 l_entity_tab(m_counter).entity_code := 'CN';
4817 l_entity_tab(m_counter).entity_id := l_header_rec.sold_to_org_id;
4818 --ER# 11824468 end
4819
4820 --ER# 13331078 start
4821 ELSIF m_counter = 15 THEN
4822 l_entity_tab(m_counter).entity_code := 'IC';
4823 -- Added Exception Handling through 13895629
4824 BEGIN
4825 select mic.category_id
4826 into l_entity_tab(m_counter).entity_id
4827 from mtl_item_categories mic,
4828 mtl_default_category_sets mdc
4829 where mic.inventory_item_id = l_line_rec.inventory_item_id
4830 AND mic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
4831 AND mdc.functional_area_id=7
4832 AND mdc.category_set_id = mic.category_set_id;
4833 EXCEPTION
4834 WHEN NO_DATA_FOUND THEN
4835 oe_debug_pub.add('In No Data Found for 13331078 Item= '||l_line_rec.inventory_item_id);
4836 END;
4837
4838
4839 --ER# 13331078 end
4840
4841 --ER#12571983 start
4842 ELSIF m_counter = 16 THEN
4843 l_entity_tab(m_counter).entity_code := 'EC';
4844 l_entity_tab(m_counter).entity_id := l_line_rec.end_customer_id;
4845 ELSIF m_counter = 17 THEN
4846 l_entity_tab(m_counter).entity_code := 'EN';
4847 l_entity_tab(m_counter).entity_id := l_line_rec.end_customer_id;
4848 ELSIF m_counter = 18 THEN
4849 l_entity_tab(m_counter).entity_code := 'EL';
4850 l_entity_tab(m_counter).entity_id := l_line_rec.end_customer_site_use_id;
4851 --ER#12571983 end
4852
4853 -- ER#3667551 start
4854 -- IMP: This has to be the last entry, as the table entry might not be created
4855 --- based on the system parameter and this might lead to index miss if not kept as last entry
4856 -- If line Bill To ORg not equals Sold to org, (it is possible for Bill to Org
4857 -- Hold Source Enabled).. this is required due to 'BTL'
4858 ELSIF m_counter = 19 THEN
4859 l_bill_to_orgid:=OE_Bulk_Holds_PVT.CustAcctID_func(p_in_site_id => l_line_rec.invoice_to_org_id,
4860 p_out_IDfound=> l_new_tbl_entry);
4861 If(l_credithold_cust='BTL' AND l_header_rec.sold_to_org_id <> l_bill_to_orgid) then
4862 l_entity_tab(m_counter).entity_code := 'C';
4863 l_entity_tab(m_counter).entity_id:= l_bill_to_orgid;
4864 ELSE
4865 l_new_tbl_entry:='N';
4866 END IF;
4867 -- ER#3667551 end
4868
4869 END IF;
4870 END LOOP;
4871
4872 -- m_counter := 13; --ER# 11824468
4873 m_counter := l_entity_tab.count; --ER# 11824468 --ER# 13331078,-- ER#3667551
4874 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
4875 m_counter := m_counter + 1;
4876 l_entity_tab(m_counter).entity_code := 'P';
4877 l_entity_tab(m_counter).entity_id := l_payment_type_tab(pay_idx);
4878 END LOOP;
4879 --ER#7479609 end
4880 END IF; --ER#7479609
4881
4882 FOR i IN l_entity_tab.first .. l_entity_tab.last LOOP --ER#7479609
4883 IF l_debug_level > 0 THEN
4884 -- oe_debug_pub.add( 'M_HOLD_ENTITY_CODE/M_HOLD_ENTITY_ID:' || M_HOLD_ENTITY_CODE || '/' || M_HOLD_ENTITY_ID , 1 ) ;
4885 oe_debug_pub.add( 'HOLD_ENTITY_CODE/HOLD_ENTITY_ID:' || l_entity_tab(i).entity_code || '/' || l_entity_tab(i).entity_id , 1 ) ;
4886 END IF;
4887
4888 --ER#7479609 IF m_hold_entity_id IS NOT NULL THEN
4889 IF l_entity_tab(i).entity_id IS NOT NULL THEN --ER#7479609
4890 --ER#7479609 OPEN curr_hold_source(m_hold_entity_code, m_hold_entity_id);
4891 --8602364 start
4892 IF l_entity_tab(i).entity_code = 'OI' THEN
4893 OPEN curr_hold_source_2(l_entity_tab(i).entity_code, l_entity_tab(i).entity_id);
4894 ELSE
4895 --8602364 end
4896 OPEN curr_hold_source(l_entity_tab(i).entity_code, l_entity_tab(i).entity_id); --ER#7479609
4897 END IF; --8602364
4898 LOOP
4899 IF l_debug_level > 0 THEN
4900 oe_debug_pub.add( '(3)RETRIEVING NEW HOLD SOURCE RECORD' , 1 ) ;
4901 END IF;
4902
4903 --8602364 start
4904 IF l_entity_tab(i).entity_code = 'OI' THEN
4905 FETCH curr_hold_source_2 INTO l_hold_source_id, l_hold_id,
4906 l_hold_entity_code,l_hold_entity_id,
4907 l_hold_entity_code2,l_hold_entity_id2;
4908 IF (curr_hold_source_2%notfound) THEN
4909 IF l_debug_level > 0 THEN
4910 oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4911 END IF;
4912
4913 EXIT;
4914 END IF;
4915 ELSE
4916 --8602364 end
4917 FETCH curr_hold_source INTO l_hold_source_id, l_hold_id,
4918 l_hold_entity_code,l_hold_entity_id,
4919 l_hold_entity_code2,l_hold_entity_id2;
4920 IF (curr_hold_source%notfound) THEN
4921 IF l_debug_level > 0 THEN
4922 oe_debug_pub.add( 'HOLD SOURCE RECORD NOT FOUND , EXITING' , 1 ) ;
4923 END IF;
4924
4925 EXIT;
4926 END IF;
4927 END IF; --8602364
4928
4929 IF l_debug_level > 0 THEN
4930 oe_debug_pub.add( '(6)L_HOLD_ENTITY_CODE/L_HOLD_ENTITY_ID/' || 'L_HOLD_ENTITY_CODE2/L_HOLD_ENTITY_ID2/L_HOLD_SOURCE_ID:');
4931 oe_debug_pub.add( ' ' || L_HOLD_ENTITY_CODE || '/' || L_HOLD_ENTITY_ID || '/' || L_HOLD_ENTITY_CODE2 || '/' || L_HOLD_ENTITY_ID2 || '/' || L_HOLD_SOURCE_ID , 1 ) ;
4932 END IF;
4933
4934 -- If second entity is not null, check if order/line is eligible for hold
4935
4936 l_create_order_hold_flag := 'Y';
4937
4938 -- ER#3667551 start
4939 -- If System parameter "Apply Credit Hold Based On" is set to 'Bill To Customer Line'
4940 -- and if this flow is for Credit Hold (hold id 1) with Entity Code 'C' (Account Level Credit Hold)
4941 -- then Line Level hold has to be applied for the Bill To Customer
4942 l_ch_level := '';
4943 If(l_hold_entity_code2 is null and p_entity_code = OE_Globals.G_ENTITY_HEADER
4944 and l_hold_entity_code = 'C' and l_hold_id=1 ) then
4945 oe_debug_pub.add( 'l_bill_to_orgid='||l_bill_to_orgid||'l_hold_entity_id'||l_hold_entity_id
4946 ||'STO-'||l_header_rec.sold_to_org_id);
4947 If (l_credithold_cust='BTL') then
4948 l_create_order_hold_flag := 'N';
4949 oe_debug_pub.add( 'Do not Create order hold , system parameter is set Bill To Line');
4950 -- Do Not Create Hold for the iteration where Bill To Customer not equals Sold to Customer
4951 -- but the hold entity id holds the Sold to Customer for a BTH case
4952 -- because hold should be created only for Bill To Customer not equals Sold To
4953 elsIf (l_credithold_cust='BTH' and l_bill_to_orgid <> l_header_rec.sold_to_org_id
4954 and l_hold_entity_id = l_header_rec.sold_to_org_id) then
4955 l_create_order_hold_flag := 'N';
4956 oe_debug_pub.add( 'Do not Create Sold To Customer order hold , system parameter is set Bill To Header');
4957 --
4958 -- If it is a Bill to Header Level Credit Hold update the level so that
4959 -- appropriate message can be displayed
4960 elsif (l_credithold_cust='BTH' and l_hold_entity_id = l_bill_to_orgid) then
4961 l_ch_level := 'BTH';
4962 end if;
4963 -- If there is a Customer level hold active for the BTC of the order,
4964 -- then due to m_counter =6 in l_entity_tab the hold message will be displayed,
4965 -- even though the hold would not be applied. But to stop the message this is required
4966 ElsIf(l_hold_entity_code2 is null and p_entity_code = OE_Globals.G_ENTITY_HEADER
4967 and l_hold_entity_code = 'C' and l_hold_id<>1 and l_hold_entity_id <> l_header_rec.sold_to_org_id) then
4968 l_create_order_hold_flag := 'N';
4969 end if;
4970 -- ER#3667551 end
4971
4972 IF l_hold_entity_code2 is not null THEN
4973 l_create_order_hold_flag := 'N';
4974 --ER#7479609 start
4975 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
4976
4977 IF l_hold_entity_code2 = 'OT' THEN
4978 IF l_header_rec.order_type_id = l_hold_entity_id2 THEN
4979 l_create_order_hold_flag := 'Y';
4980 END IF;
4981 /*9927494 start
4982 ELSIF l_hold_entity_code2 = 'PT' THEN
4983 IF l_header_rec.payment_type_code = l_hold_entity_id2 THEN
4984 l_create_order_hold_flag := 'Y';
4985 END IF;
4986 9927494 end*/
4987 ELSIF l_hold_entity_code2 = 'TC' THEN
4988 IF l_header_rec.transactional_curr_code = l_hold_entity_id2 THEN
4989 l_create_order_hold_flag := 'Y';
4990 END IF;
4991 ELSIF l_hold_entity_code2 = 'SC' THEN
4992 IF l_header_rec.sales_channel_code = l_hold_entity_id2 THEN
4993 l_create_order_hold_flag := 'Y';
4994 END IF;
4995 ELSIF l_hold_entity_code2 = 'P' THEN
4996 FOR pay_idx in 1 .. l_payment_type_tab.count LOOP
4997 IF l_payment_type_tab(pay_idx) = l_hold_entity_id2 THEN
4998 l_create_order_hold_flag := 'Y';
4999 EXIT;
5000 END IF;
5001 END LOOP;
5002 END IF;
5003
5004 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
5005 --ER#7479609 end
5006 IF l_hold_entity_code2 = 'C' THEN
5007 IF l_line_rec.sold_to_org_id = l_hold_entity_id2 THEN
5008 l_create_order_hold_flag := 'Y';
5009 END IF;
5010
5011 --ER# 11824468 start
5012 ELSIF l_hold_entity_code2 = 'CN' THEN
5013 IF l_line_rec.sold_to_org_id = l_hold_entity_id2 THEN
5014 l_create_order_hold_flag := 'Y';
5015 END IF;
5016 --ER# 11824468 end
5017
5018 --ER#12571983 start
5019 ELSIF l_hold_entity_code2 = 'EL' THEN
5020 IF l_line_rec.end_customer_site_use_id = l_hold_entity_id2 THEN
5021 l_create_order_hold_flag := 'Y';
5022 END IF;
5023 ELSIF l_hold_entity_code2 in('EC','EN') THEN
5024 IF l_line_rec.end_customer_id = l_hold_entity_id2 THEN
5025 l_create_order_hold_flag := 'Y';
5026 END IF;
5027 --ELSIF l_hold_entity_code in('EC','EN') and l_line_rec.end_customer_id = l_hold_entity_id THEN --13895629
5028 -- Modified ELSIF as below, --13895629
5029 ELSIF l_hold_entity_code in('EC','EN') and l_hold_entity_code2 IN ('SC' , 'TC' ,'OT','P')
5030 and l_line_rec.end_customer_id = l_hold_entity_id THEN
5031 IF l_hold_entity_code2 ='SC' AND l_header_rec.sales_channel_code = l_hold_entity_id2 THEN
5032 l_create_order_hold_flag := 'Y';
5033 ELSIF l_hold_entity_code2 = 'TC' AND l_header_rec.transactional_curr_code = l_hold_entity_id2 THEN
5034 l_create_order_hold_flag := 'Y';
5035 ELSIF l_hold_entity_code2 = 'OT' AND l_header_rec.order_type_id = l_hold_entity_id2 THEN
5036 l_create_order_hold_flag := 'Y';
5037 ELSIF l_hold_entity_code2 = 'P' THEN
5038 FOR pay_idx in 1 .. l_payment_type_tab.count
5039 LOOP
5040 IF l_payment_type_tab(pay_idx) = l_hold_entity_id2 THEN
5041 l_create_order_hold_flag := 'Y';
5042 EXIT;
5043 END IF;
5044 END LOOP;
5045 END IF;-- end if of entity code 2 check
5046 --ER#12571983 end
5047
5048 ELSIF l_hold_entity_code2 = 'B' THEN
5049 IF l_line_rec.invoice_to_org_id = l_hold_entity_id2 THEN
5050 l_create_order_hold_flag := 'Y';
5051 END IF;
5052 ELSIF l_hold_entity_code2 = 'S' THEN
5053 IF l_line_rec.ship_to_org_id = l_hold_entity_id2 THEN
5054 l_create_order_hold_flag := 'Y';
5055 END IF;
5056 ELSIF l_hold_entity_code2 = 'W' THEN
5057 IF l_line_rec.ship_from_org_id = l_hold_entity_id2 THEN
5058 l_create_order_hold_flag := 'Y';
5059 END IF;
5060 ELSIF l_hold_entity_code2 = 'O' THEN
5061 IF l_line_rec.header_id = l_hold_entity_id2 THEN
5062 l_create_order_hold_flag := 'Y';
5063 END IF;
5064 ELSIF l_hold_entity_code2 = 'H' THEN
5065 IF l_line_rec.blanket_number = l_hold_entity_id2 THEN
5066 l_create_order_hold_flag := 'Y';
5067 END IF;
5068 ELSIF l_hold_entity_code2 = 'L' THEN
5069 IF l_line_rec.blanket_line_number = l_hold_entity_id2 THEN
5070 l_create_order_hold_flag := 'Y';
5071 END IF;
5072
5073 ELSIF l_hold_entity_code2 = 'LT' THEN
5074 IF l_line_rec.line_type_id = l_hold_entity_id2 THEN
5075 l_create_order_hold_flag := 'Y';
5076 END IF;
5077 ELSIF l_hold_entity_code2 = 'SM' THEN
5078 IF l_line_rec.shipping_method_code = l_hold_entity_id2 THEN
5079 l_create_order_hold_flag := 'Y';
5080 END IF;
5081 ELSIF l_hold_entity_code2 = 'D' THEN
5082 IF l_line_rec.deliver_to_org_id = l_hold_entity_id2 THEN
5083 l_create_order_hold_flag := 'Y';
5084 END IF;
5085 ELSIF l_hold_entity_code2 = 'ST' THEN
5086 IF l_line_rec.source_type_code = l_hold_entity_id2 THEN
5087 l_create_order_hold_flag := 'Y';
5088 END IF;
5089 ELSIF l_hold_entity_code2 = 'PL' THEN
5090 IF l_line_rec.price_list_id = l_hold_entity_id2 THEN
5091 l_create_order_hold_flag := 'Y';
5092 END IF;
5093 ELSIF l_hold_entity_code2 = 'PR' THEN
5094 IF l_line_rec.project_id = l_hold_entity_id2 THEN
5095 l_create_order_hold_flag := 'Y';
5096 END IF;
5097 ELSIF l_hold_entity_code2 = 'PT' THEN
5098 IF l_line_rec.payment_term_id = l_hold_entity_id2 THEN
5099 l_create_order_hold_flag := 'Y';
5100 END IF;
5101
5102 ELSIF l_hold_entity_code2 = 'OI' THEN
5103 IF l_line_rec.inventory_item_id = l_hold_entity_id2 THEN
5104 l_create_order_hold_flag := 'Y';
5105 END IF;
5106 ELSIF l_hold_entity_code2 = 'T' THEN
5107 IF l_line_rec.task_id = l_hold_entity_id2 THEN
5108 l_create_order_hold_flag := 'Y';
5109 END IF;
5110 ELSIF l_hold_entity_code2 = 'CB' THEN
5111 IF l_line_rec.created_by = l_hold_entity_id2 THEN
5112 l_create_order_hold_flag := 'Y';
5113 END IF;
5114 END IF;
5115 END IF; --ER#7479609
5116
5117 END IF; -- l_hold_entity_code2 is not null
5118
5119 IF l_debug_level > 0 THEN
5120 oe_debug_pub.add( 'L_CREATE_ORDER_HOLD_FLAG:' || L_CREATE_ORDER_HOLD_FLAG , 1 ) ;
5121 END IF;
5122
5123 --ER#7479609 start
5124 IF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
5125 -- IF l_hold_entity_code = 'C' and --ER# 11824468
5126 IF l_hold_entity_code IN('C','CN') and --ER# 11824468
5127 (l_hold_entity_code2 IS NULL OR
5128 l_hold_entity_code2 = 'OT' OR
5129 --9927494 l_hold_entity_code2 = 'PT' OR
5130 l_hold_entity_code2 = 'P' OR --9927494
5131 l_hold_entity_code2 = 'TC' OR
5132 l_hold_entity_code2 = 'SC'
5133 ) THEN
5134 -- ER#3667551, start
5135 -- For Entity Line, based on System parameter "Apply Credit Hold Based On" set to 'Bill To Customer Line'
5136 -- credit check failure hold(hold id 1) should be created at line level
5137 If (l_hold_id=1 and l_credithold_cust='BTL'and l_hold_entity_code = 'C' and l_bill_to_orgid = l_hold_entity_id) then
5138 l_create_order_hold_flag := 'Y';
5139 l_ch_level := 'BTL'; --set the level for proper message to be displayed
5140 else
5141 -- ER#3667551, end
5142 l_create_order_hold_flag := 'N';
5143 end if;-- ER#3667551, added end if;
5144 END IF;
5145
5146 IF l_hold_entity_code = 'OT' and (l_hold_entity_code2 = 'TC' OR l_hold_entity_code2 IS NULL)
5147 THEN
5148 l_create_order_hold_flag := 'N';
5149 END IF;
5150
5151 END IF;
5152 --ER#7479609 end
5153
5154 IF l_create_order_hold_flag = 'Y' THEN
5155 l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
5156 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
5157 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
5158 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
5159 l_hold_source_rec.HOLD_ID := l_hold_id;
5160 l_hold_source_rec.hold_source_id := l_hold_source_id;
5161 l_hold_source_rec.header_id := l_header_id;
5162 l_hold_source_rec.line_id := l_line_id;
5163
5164 IF l_debug_level > 0 THEN
5165 oe_debug_pub.add( 'CALLING OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
5166 END IF;
5167 oe_holds_pvt.Create_Order_Holds (
5168 p_hold_source_rec => l_hold_source_rec
5169 ,x_return_status => x_return_status
5170 ,x_msg_count => x_msg_count
5171 ,x_msg_data => x_msg_data
5172 );
5173
5174
5175 IF l_debug_level > 0 THEN
5176 oe_debug_pub.add( 'X_RETURN_STATUS:' || X_RETURN_STATUS , 1 ) ;
5177 END IF;
5178
5179 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5180 IF l_debug_level > 0 THEN
5181 oe_debug_pub.add( 'ERROR AFTER OE_HOLDS_PVT.CREATE_ORDER_HOLDS' ) ;
5182 END IF;
5183 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5184 RAISE FND_API.G_EXC_ERROR;
5185 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5187 END IF;
5188 ELSE
5189 IF l_debug_level > 0 THEN
5190 oe_debug_pub.add( 'HOLD APPLIED' ) ;
5191 END IF;
5192
5193 IF l_hold_entity_code = 'C' THEN
5194 l_attribute := 'Customer';
5195 -- ER#3667551, start
5196 If( l_ch_level = 'BTL') then
5197 l_attribute := 'Line Level Bill To Customer';
5198 elsif( l_ch_level = 'BTH')then
5199 l_attribute := 'Header Level Bill To Customer';
5200 end if;
5201 -- ER#3667551, end
5202 --ER# 11824468 start
5203 ELSIF l_hold_entity_code = 'CN' THEN
5204 l_attribute := 'Customer';
5205 --ER# 11824468 end
5206 ELSIF l_hold_entity_code = 'I' THEN
5207 l_attribute := 'Item';
5208 --ER# 13331078 start
5209 ELSIF l_hold_entity_code = 'IC' THEN
5210 l_attribute := 'Item Category';
5211 --ER# 13331078 end
5212 --ER#12571983 start
5213 ELSIF l_hold_entity_code IN('EC', 'EN') THEN
5214 l_attribute := 'End Customer';
5215 --ER#12571983 end
5216 ELSIF l_hold_entity_code = 'S' THEN
5217 l_attribute := 'Ship to Site';
5218 ELSIF l_hold_entity_code = 'B' THEN
5219 l_attribute := 'Bill to Site';
5220 ELSIF l_hold_entity_code = 'O' then
5221 l_attribute := 'Order';
5222 ELSIF l_hold_entity_code = 'W' then
5223 l_attribute := 'Warehouse';
5224 ELSIF l_hold_entity_code = 'H' then
5225 l_attribute := 'Blanket Number';
5226 --ER#7479609 start
5227 ELSIF l_hold_entity_code = 'TM' THEN
5228 l_attribute := 'Top Model';
5229 ELSIF l_hold_entity_code = 'PR' then
5230 l_attribute := 'Project Number';
5231 ELSIF l_hold_entity_code = 'PL' then
5232 l_attribute := 'Price List';
5233 ELSIF l_hold_entity_code = 'OT' then
5234 l_attribute := 'Order Type';
5235 ELSIF l_hold_entity_code = 'CD' THEN
5236 l_attribute := 'Creation Date';
5237 ELSIF l_hold_entity_code = 'SC' then
5238 l_attribute := 'Sales Channel Code';
5239 ELSIF l_hold_entity_code = 'P' then
5240 l_attribute := 'Payment Type';
5241 ELSIF l_hold_entity_code = 'SM' then
5242 l_attribute := 'Shipping Method Code';
5243 --8254521 start
5244 ELSIF l_hold_entity_code = 'D' THEN
5245 l_attribute := 'Deliver to Site';
5246 --8254521 end
5247 --ER#7479609 end
5248 END IF;
5249 IF l_hold_entity_code2 is not null then
5250 IF l_hold_entity_code2 = 'C' THEN
5251 l_attribute := l_attribute || '/' || 'Customer';
5252 --ER# 11824468 start
5253 ELSIF l_hold_entity_code2 = 'CN' THEN
5254 l_attribute := l_attribute || '/' || 'Customer';
5255 --ER# 11824468 end
5256 --ER#12571983 start
5257 ELSIF l_hold_entity_code2 IN('EC', 'EN') THEN
5258 l_attribute := l_attribute || '/' ||'End Customer';
5259 ELSIF l_hold_entity_code2 ='EL' THEN
5260 l_attribute := l_attribute || '/' ||'End Customer Location';
5261 --ER#12571983 end
5262 ELSIF l_hold_entity_code2 = 'S' THEN
5263 l_attribute := l_attribute || '/' || 'Ship to Site';
5264 ELSIF l_hold_entity_code2 = 'B' THEN
5265 l_attribute := l_attribute || '/' || 'Bill to Site';
5266 ELSIF l_hold_entity_code2 = 'W' then
5267 l_attribute := l_attribute || '/' || 'Warehouse';
5268 ELSIF l_hold_entity_code2 = 'O' then
5269 l_attribute := l_attribute || '/' || 'Order';
5270 ELSIF l_hold_entity_code2 = 'H' THEN
5271 l_attribute := l_attribute || '/' || 'Blanket Number';
5272 ELSIF l_hold_entity_code2 = 'L' THEN
5273 l_attribute := l_attribute || '/' || 'Blanket Line Number';
5274 --ER#7479609 start
5275 ELSIF l_hold_entity_code2 = 'LT' THEN
5276 l_attribute := l_attribute || '/' || 'Line Type';
5277 ELSIF l_hold_entity_code2 = 'SM' THEN
5278 l_attribute := l_attribute || '/' || 'Shipping Method Code';
5279 ELSIF l_hold_entity_code2 = 'D' then
5280 l_attribute := l_attribute || '/' || 'Deliver to Site';
5281 ELSIF l_hold_entity_code2 = 'ST' then
5282 l_attribute := l_attribute || '/' || 'Source Type Code';
5283 ELSIF l_hold_entity_code2 = 'PL' THEN
5284 l_attribute := l_attribute || '/' || 'Price List';
5285 ELSIF l_hold_entity_code2 = 'PR' THEN
5286 l_attribute := l_attribute || '/' || 'Project Number';
5287 ELSIF l_hold_entity_code2 = 'PT' THEN
5288 l_attribute := l_attribute || '/' || 'Payment Term';
5289 ELSIF l_hold_entity_code2 = 'OI' THEN
5290 l_attribute := l_attribute || '/' || 'Option Item';
5291 ELSIF l_hold_entity_code2 = 'T' then
5292 l_attribute := l_attribute || '/' || 'Task Number';
5293 ELSIF l_hold_entity_code2 = 'OT' then
5294 l_attribute := l_attribute || '/' || 'Order Type';
5295 ELSIF l_hold_entity_code2 = 'P' THEN
5296 l_attribute := l_attribute || '/' || 'Payment Type';
5297 ELSIF l_hold_entity_code2 = 'TC' THEN
5298 l_attribute := l_attribute || '/' || 'Currency';
5299 ELSIF l_hold_entity_code2 = 'SC' then
5300 l_attribute := l_attribute || '/' || 'Sales Channel Code';
5301 ELSIF l_hold_entity_code2 = 'CB' THEN
5302 l_attribute := l_attribute || '/' || 'Created By';
5303 --ER#7479609 end
5304 END IF;
5305 end if;
5306
5307
5308 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
5309 fnd_message.set_name('ONT','OE_HLD_APPLIED');
5310 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
5311 OE_MSG_PUB.ADD;
5312 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
5313 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
5314 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
5315 --ER#7479609 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
5316 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_rec.line_number); --ER#7479609
5317 OE_MSG_PUB.ADD;
5318 END IF;
5319
5320 END IF; -- if create_order_hold was successful
5321 END IF; -- l_create_order_hold_flag = 'Y'
5322 END LOOP;
5323 --8602364 start
5324 IF l_entity_tab(i).entity_code = 'OI' THEN
5325 CLOSE curr_hold_source_2;
5326 ELSE
5327 --8602364 end
5328 CLOSE curr_hold_source;
5329 END IF; --8602364
5330 END IF; -- IF m_hold_entity_id IS NOT NULL THEN
5331 END LOOP;
5332
5333 -- Check for Current second entity hold if the entity is ('C','B','S','W')
5334 /*
5335 FOR m_counter IN 1..4 LOOP
5336 IF m_counter = 1 THEN
5337 m_hold_entity_code2 := 'C';
5338 m_hold_entity_id2 := l_sold_to_org_id;
5339 ELSIF m_counter = 2 THEN
5340 m_hold_entity_code2 := 'B';
5341 m_hold_entity_id2 := l_invoice_to_org_id;
5342 ELSIF m_counter = 3 THEN
5343 m_hold_entity_code2 := 'S';
5344 m_hold_entity_id2 := l_ship_to_org_id;
5345 ELSIF m_counter = 4 THEN
5346 m_hold_entity_code2 := 'W';
5347 m_hold_entity_id2 := l_ship_from_org_id;
5348 END IF;
5349 OE_Debug_PUB.Add('m_hold_entity_code2/m_hold_entity_id2:' ||
5350 m_hold_entity_code2 || '/' || m_hold_entity_id2, 1);
5351 IF m_hold_entity_id2 IS NOT NULL THEN
5352
5353 OPEN curr_hold_source_entity2(m_hold_entity_code2,m_hold_entity_id2);
5354 LOOP
5355 OE_Debug_PUB.Add('Retrieving new hold source record for Entity2', 1);
5356 FETCH curr_hold_source_entity2 INTO l_hold_source_id, l_hold_id,
5357 l_hold_entity_code,l_hold_entity_id,
5358 l_hold_entity_code2,l_hold_entity_id2;
5359
5360 IF (curr_hold_source_entity2%notfound) THEN
5361 OE_Debug_PUB.Add('No Hold Source found, existing', 1);
5362 EXIT;
5363 END IF;
5364
5365 OE_Debug_PUB.Add('l_hold_entity_code/l_hold_entity_id/' ||
5366 'l_hold_entity_code2/l_hold_entity_id2/l_hold_source_id:' ||
5367 l_hold_entity_code || '/' ||
5368 l_hold_entity_id || '/' ||
5369 l_hold_entity_code2 || '/' ||
5370 l_hold_entity_id2 || '/' ||
5371 l_hold_source_id, 1);
5372
5373 -- If second entity is not null, The First entity can only be I or W
5374 -- Check if order/line is eligible for hold
5375 l_create_order_hold_flag := 'N';
5376 IF l_hold_entity_code = 'I' THEN
5377 IF l_inventory_item_id = l_hold_entity_id THEN
5378 l_create_order_hold_flag := 'Y';
5379 END IF;
5380 ELSIF l_hold_entity_code = 'W' THEN
5381 IF l_ship_from_org_id = l_hold_entity_id THEN
5382 l_create_order_hold_flag := 'Y';
5383 END IF;
5384 END IF;
5385 OE_DEBUG_PUB.Add('l_create_order_hold_flag:' || l_create_order_hold_flag,1);
5386
5387 IF l_create_order_hold_flag = 'Y' THEN
5388 l_hold_source_rec.HOLD_ENTITY_CODE := l_hold_entity_code;
5389 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
5390 l_hold_source_rec.HOLD_ENTITY_CODE2 := l_hold_entity_code2;
5391 l_hold_source_rec.HOLD_ENTITY_ID2 := l_hold_entity_id2;
5392 l_hold_source_rec.HOLD_ID := l_hold_id;
5393 l_hold_source_rec.hold_source_id := l_hold_source_id;
5394 l_hold_source_rec.header_id := l_header_id;
5395 l_hold_source_rec.line_id := l_line_id;
5396
5397 oe_debug_pub.add('Calling oe_holds_pvt.create_order_holds');
5398 oe_holds_pvt.Create_Order_Holds (
5399 p_hold_source_rec => l_hold_source_rec
5400 ,x_return_status => x_return_status
5401 ,x_msg_count => x_msg_count
5402 ,x_msg_data => x_msg_data
5403 );
5404
5405 OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5406
5407 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5408 OE_Debug_PUB.Add('Error After oe_holds_pvt.Create_Order_Holds');
5409 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5410 RAISE FND_API.G_EXC_ERROR;
5411 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5413 END IF;
5414 ELSE
5415 OE_Debug_PUB.Add('Hold applied');
5416
5417 IF l_hold_entity_code = 'C' THEN
5418 l_attribute := 'Customer';
5419 ELSIF l_hold_entity_code = 'I' THEN
5420 l_attribute := 'Item';
5421 ELSIF l_hold_entity_code = 'S' THEN
5422 l_attribute := 'Site Use';
5423 ELSIF l_hold_entity_code = 'B' THEN
5424 l_attribute := 'Bill to Site';
5425 ELSIF l_hold_entity_code = 'O' then
5426 l_attribute := 'Order';
5427 ELSIF l_hold_entity_code = 'W' then
5428 l_attribute := 'Warehouse';
5429 END IF;
5430 IF l_hold_entity_code2 is not null then
5431 IF l_hold_entity_code2 = 'C' THEN
5432 l_attribute := l_attribute || '/' || 'Customer';
5433 ELSIF l_hold_entity_code2 = 'S' THEN
5434 l_attribute := l_attribute || '/' || 'Ship to Site';
5435 ELSIF l_hold_entity_code2 = 'B' THEN
5436 l_attribute := l_attribute || '/' || 'Bill to Site';
5437 ELSIF l_hold_entity_code2 = 'W' then
5438 l_attribute := l_attribute || '/' || 'Warehouse';
5439 END IF;
5440 end if;
5441
5442 IF p_entity_code = OE_Globals.G_ENTITY_HEADER THEN
5443 fnd_message.set_name('ONT','OE_HLD_APPLIED');
5444 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
5445 OE_MSG_PUB.ADD;
5446 ELSIF p_entity_code = OE_Globals.G_ENTITY_LINE THEN
5447 fnd_message.set_name('ONT','OE_HLD_APPLIED_LINE');
5448 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
5449 -- Get the line number from the line record
5450 --SELECT line_number
5451 -- INTO l_line_number
5452 -- FROM OE_ORDER_LINES
5453 -- WHERE LINE_ID = p_entity_id;
5454 --FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_number);
5455 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_line_rec.line_number); --8254521
5456 OE_MSG_PUB.ADD;
5457 END IF;
5458 END IF; -- if apply hold was successful
5459 END IF; -- l_create_order_hold_flag = 'Y'
5460 END LOOP;
5461 CLOSE curr_hold_source_entity2;
5462 END IF;
5463 END LOOP;
5464 */
5465 IF l_debug_level > 0 THEN
5466 oe_debug_pub.add( 'EXITING EVALUATE_HOLDS_POST_WRITE' , 1 ) ;
5467 END IF;
5468
5469 EXCEPTION
5470
5471 WHEN FND_API.G_EXC_ERROR THEN
5472 IF (curr_hold_source%isopen) THEN
5473 CLOSE curr_hold_source;
5474 END IF;
5475 --8602364 start
5476 IF (curr_hold_source_2%isopen) THEN
5477 CLOSE curr_hold_source_2;
5478 END IF;
5479 --8602364 end
5480 x_return_status := FND_API.G_RET_STS_ERROR;
5481
5482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5483 IF (curr_hold_source%isopen) THEN
5484 CLOSE curr_hold_source;
5485 END IF;
5486 --8602364 start
5487 IF (curr_hold_source_2%isopen) THEN
5488 CLOSE curr_hold_source_2;
5489 END IF;
5490 --8602364 end
5491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5492
5493 WHEN OTHERS THEN
5494 IF (curr_hold_source%isopen) THEN
5495 CLOSE curr_hold_source;
5496 END IF;
5497 --8602364 start
5498 IF (curr_hold_source_2%isopen) THEN
5499 CLOSE curr_hold_source_2;
5500 END IF;
5501 --8602364 end
5502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5503
5504 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5505 THEN
5506 OE_MSG_PUB.Add_Exc_Msg
5507 ( G_PKG_NAME
5508 , 'evaluate_holds_post_write'
5509 );
5510 END IF;
5511
5512 END evaluate_holds_post_write;
5513 /*******************************/
5514
5515 FUNCTION Hold_exists
5516 ( p_hold_entity_code IN VARCHAR2
5517 --ER#7479609 , p_hold_entity_id IN NUMBER
5518 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5519 , p_hold_id IN NUMBER DEFAULT 1
5520 , p_org_id IN NUMBER DEFAULT NULL
5521 ) RETURN boolean IS
5522
5523 l_hold_exists VARCHAR2(1) := 'N';
5524
5525 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5526
5527 BEGIN
5528 IF l_debug_level > 0 THEN
5529 oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Hold_Exists',1 ) ;
5530 END IF;
5531 IF p_org_id IS NULL THEN
5532
5533 SELECT 'Y'
5534 INTO l_hold_exists
5535 FROM OE_HOLD_SOURCES_ALL
5536 WHERE hold_entity_code = p_hold_entity_code
5537 AND HOLD_ENTITY_ID = p_hold_entity_id
5538 AND hold_id = p_hold_id
5539 AND nvl(RELEASED_FLAG, 'N') = 'N'
5540 AND ORG_ID is null;
5541 ELSE
5542
5543 SELECT 'Y'
5544 INTO l_hold_exists
5545 FROM OE_HOLD_SOURCES_ALL
5546 WHERE hold_entity_code = p_hold_entity_code
5547 AND HOLD_ENTITY_ID = p_hold_entity_id
5548 AND hold_id = p_hold_id
5549 AND nvl(RELEASED_FLAG, 'N') = 'N'
5550 AND ORG_ID = p_org_id;
5551
5552 END IF;
5553 IF l_debug_level > 0 THEN
5554 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Hold_Exists',1 ) ;
5555 END IF;
5556
5557 IF l_hold_exists = 'Y' THEN
5558 return true;
5559 ELSE
5560 return false;
5561 END IF;
5562
5563 EXCEPTION
5564 WHEN NO_DATA_FOUND THEN
5565 return false;
5566 WHEN TOO_MANY_ROWS THEN
5567 IF l_debug_level > 0 THEN
5568 oe_debug_pub.add( 'TOO_MANY_ROWS exception in OE_HOLDS_PUB.Hold_Exists' ) ;
5569 END IF;
5570 return true;
5571 WHEN others THEN
5572 IF l_debug_level > 0 THEN
5573 oe_debug_pub.add( 'Exception in OE_HOLDS_PUB.Hold_Exists' ) ;
5574 END IF;
5575 return false;
5576 END Hold_exists;
5577
5578 /* This processes the holds (apply,release) on a customer account.
5579 Called from process_holds procedure in this package
5580
5581 */
5582 PROCEDURE Process_Holds_Customer
5583 ( p_hold_entity_code IN VARCHAR2
5584 --ER#7479609 , p_hold_entity_id IN NUMBER
5585 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5586 , p_hold_id IN NUMBER DEFAULT 1
5587 , p_release_reason_code IN VARCHAR2 DEFAULT NULL
5588 , p_action IN VARCHAR2
5589 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5590 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5591 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5592 ) IS
5593
5594 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
5595 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
5596
5597 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5598 l_org_id NUMBER := null;
5599 l_hold_source_id NUMBER := null;
5600 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5601
5602 CURSOR c_hold_orgs IS
5603 SELECT DISTINCT org_id
5604 FROM HZ_CUST_ACCT_SITES_ALL acct
5605 WHERE acct.cust_account_id = p_hold_entity_id
5606 AND NOT EXISTS (SELECT 1
5607 FROM OE_HOLD_SOURCES_ALL src
5608 WHERE acct.cust_account_id = src.hold_entity_id
5609 AND src.hold_entity_code = 'C'
5610 AND acct.org_id = src.org_id
5611 AND src.hold_id = p_hold_id
5612 AND nvl(src.released_flag, 'N') = 'N')
5613 UNION
5614
5615 SELECT DISTINCT org_id
5616 FROM OE_ORDER_HEADERS_ALL hdr
5617 WHERE sold_to_org_id = p_hold_entity_id
5618 AND NOT EXISTS (select 1
5619 from oe_hold_sources_all
5620 where hold_entity_id = p_hold_entity_id
5621 and hold_entity_code = 'C'
5622 and hold_id = p_hold_id
5623 and nvl(RELEASED_FLAG, 'N') = 'N')
5624 AND NOT EXISTS (select 1
5625 from HZ_CUST_ACCT_SITES_ALL hzcas
5626 where hzcas.cust_account_id = p_hold_entity_id
5627 and hzcas.cust_account_id = hdr.sold_to_org_id);
5628
5629
5630 CURSOR c_cust_holds IS
5631 SELECT org_id, hold_source_id
5632 FROM OE_HOLD_SOURCES_ALL
5633 WHERE hold_entity_id = p_hold_entity_id
5634 AND hold_entity_code = 'C'
5635 AND nvl(released_flag,'N') = 'N';
5636
5637 BEGIN
5638 IF l_debug_level > 0 THEN
5639 oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5640 oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||' EntityID:'|| p_hold_entity_id||' HoldId: ' || p_hold_id||' p_Action: ' || p_action, 1);
5641 END IF;
5642 l_hold_source_rec.hold_entity_code := p_hold_entity_code;
5643 l_hold_source_rec.hold_entity_id := p_hold_entity_id;
5644 l_hold_source_rec.hold_id := p_hold_id;
5645
5646 IF p_action = 'APPLY' THEN
5647
5648 OPEN c_hold_orgs;
5649 LOOP
5650 FETCH c_hold_orgs into l_org_id;
5651 EXIT WHEN c_hold_orgs%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5652 -- can l_org_id be null??? No.
5653
5654 IF l_debug_level > 0 THEN
5655 oe_debug_pub.add('Applying Hold in ORG_ID ' || l_org_id, 1);
5656 END IF;
5657
5658 MO_GLOBAL.set_policy_context('S',l_org_id);
5659
5660 oe_holds_pvt.apply_Holds(
5661 p_hold_source_rec => l_hold_source_rec
5662 ,p_hold_existing_flg => 'Y'
5663 ,p_hold_future_flg => 'Y'
5664 ,x_return_status => x_return_status
5665 ,x_msg_count => x_msg_count
5666 ,x_msg_data => x_msg_data );
5667
5668 IF l_debug_level > 0 THEN
5669 oe_debug_pub.add('oe_holds_pvt.apply_Holds:x_return_status ' || x_return_status, 1);
5670 END IF;
5671
5672 l_return_status := x_return_status;
5673
5674 END LOOP;
5675 CLOSE c_hold_orgs;
5676
5677 ELSIF p_action = 'RELEASE' THEN
5678
5679 l_hold_release_rec.release_reason_code := p_release_reason_code;
5680 OPEN c_cust_holds;
5681 LOOP
5682
5683 FETCH c_cust_holds into l_org_id,l_hold_source_id;
5684 EXIT WHEN c_cust_holds%NOTFOUND OR l_return_status <> FND_API.G_RET_STS_SUCCESS;
5685
5686 IF l_debug_level > 0 THEN
5687 oe_debug_pub.add('Releasing Hold in ORG_ID ' || l_org_id, 1);
5688 END IF;
5689
5690 l_hold_source_rec.hold_source_id := l_hold_source_id;
5691
5692 MO_GLOBAL.set_policy_context('S',l_org_id);
5693
5694 oe_holds_pvt.Release_Holds(
5695 p_hold_source_rec => l_hold_source_rec
5696 ,p_hold_release_rec => l_hold_release_rec
5697 ,x_return_status => x_return_status
5698 ,x_msg_count => x_msg_count
5699 ,x_msg_data => x_msg_data);
5700
5701 IF l_debug_level > 0 THEN
5702 oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status ' || x_return_status, 1);
5703 END IF;
5704
5705 l_return_status := x_return_status;
5706
5707 END LOOP;
5708 END IF;
5709 IF l_debug_level > 0 THEN
5710 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5711 END IF;
5712
5713 EXCEPTION
5714 WHEN FND_API.G_EXC_ERROR THEN
5715
5716 IF l_debug_level > 0 THEN
5717 oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5718 END IF;
5719 IF x_msg_count is not null then
5720 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5721 p_data => x_msg_data);
5722 end if;
5723 RAISE FND_API.G_EXC_ERROR;
5724
5725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5726 IF l_debug_level > 0 THEN
5727 oe_debug_pub.add( 'API UNEXPECTED ERROR IN OE_HOLDS_PUB.Process_Holds_Customer' , 1 ) ;
5728 END IF;
5729 IF x_msg_count is not null then
5730 OE_MSG_PUB.Count_And_Get
5731 ( p_count => x_msg_count
5732 , p_data => x_msg_data
5733 );
5734 END IF;
5735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5736
5737 WHEN OTHERS THEN
5738 IF l_debug_level > 0 THEN
5739 oe_debug_pub.add( 'ERROR in oe_holds_pub.Process_Holds_Customer' , 1 ) ;
5740 END IF;
5741 RAISE;
5742
5743 END Process_Holds_Customer;
5744
5745 /* This processes the holds (apply,release) on a site.
5746 Called from process_holds procedure in this package
5747
5748 */
5749 PROCEDURE Process_Holds_Site
5750 ( p_hold_entity_code IN VARCHAR2
5751 --ER#7479609 , p_hold_entity_id IN NUMBER
5752 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5753 , p_hold_id IN NUMBER DEFAULT 1
5754 , p_release_reason_code IN VARCHAR2 DEFAULT NULL
5755 , p_action IN VARCHAR2
5756 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5757 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5758 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5759 ) IS
5760
5761 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
5762 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
5763 l_hold_entity_code varchar2(1);
5764 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5765 l_hold_exists VARCHAR2(1) := 'N';
5766 l_org_id NUMBER := null;
5767 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5768 BEGIN
5769 IF l_debug_level > 0 THEN
5770 oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5771 oe_debug_pub.add('EntityCode: ' || p_hold_entity_code||'::EntityID: ' || p_hold_entity_id||'::HoldId: ' || p_hold_id||'::p_Action: ' || p_action, 1);
5772 END IF;
5773
5774 -- Fetch the org_id of the site that is being sent to put on hold
5775 -- The reason, the caller may not have set the correct org
5776
5777 BEGIN
5778
5779 SELECT org_id into l_org_id
5780 FROM hz_cust_site_uses_all
5781 WHERE site_use_id = p_hold_entity_id;
5782
5783 IF l_org_id is null then
5784 IF l_debug_level > 0 THEN
5785 oe_debug_pub.add( 'ORG_ID for the site is null. Invalid site ||p_hold_entity_id' , 1 ) ;
5786 END IF;
5787 raise no_data_found; -- Should not occur at all
5788 END IF;
5789 EXCEPTION
5790 WHEN no_data_found then
5791 --x_return_status := FND_API.G_RET_STS_ERROR;
5792 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
5793 OE_MSG_PUB.ADD;
5794 fnd_message.set_token('SITE_USE_ID', to_char(p_hold_entity_id));
5795
5796 RAISE FND_API.G_EXC_ERROR;
5797 END;
5798
5799 IF l_debug_level > 0 THEN
5800 oe_debug_pub.add('Process_Holds_Site:ORG_ID: ' || l_org_id, 1);
5801 END IF;
5802
5803 MO_GLOBAL.set_policy_context('S',l_org_id);
5804
5805 -- Validate hold entity (site) and get correct code for Ship To, Bill To
5806 l_hold_entity_code := Hold_Site_Code(p_hold_entity_id);
5807
5808 l_hold_source_rec.hold_entity_code := l_hold_entity_code;
5809 l_hold_source_rec.hold_entity_id := p_hold_entity_id;
5810 l_hold_source_rec.hold_id := p_hold_id;
5811 l_hold_release_rec.release_reason_code := p_release_reason_code;
5812
5813 IF p_action = 'APPLY' THEN
5814
5815 /* APPLY the hold iff there is no hold already. But if there is a hold already existing,
5816 should we give a message to the user? Right now, the API does nothing.
5817
5818 */
5819 IF NOT Hold_exists(p_hold_entity_code,p_hold_entity_id,p_hold_id,l_org_id) THEN
5820 oe_holds_pvt.apply_Holds(
5821 p_hold_source_rec => l_hold_source_rec
5822 ,p_hold_existing_flg => 'Y' -- Hold all existing orders
5823 ,p_hold_future_flg => 'Y' -- hold new orders also
5824 ,x_return_status => x_return_status
5825 ,x_msg_count => x_msg_count
5826 ,x_msg_data => x_msg_data );
5827
5828 IF l_debug_level > 0 THEN
5829 oe_debug_pub.add('oe_holds_pvt.apply:x_return_status: ' || x_return_status, 1);
5830 END IF;
5831
5832 -- Exceptions and return status are checked in process_holds
5833 END IF; -- hold not exists
5834 ELSIF p_action = 'RELEASE' THEN
5835
5836 oe_holds_pvt.Release_Holds(
5837 p_hold_source_rec => l_hold_source_rec
5838 ,p_hold_release_rec => l_hold_release_rec
5839 ,x_return_status => x_return_status
5840 ,x_msg_count => x_msg_count
5841 ,x_msg_data => x_msg_data);
5842
5843
5844 IF l_debug_level > 0 THEN
5845 oe_debug_pub.add('oe_holds_pvt.Release_Holds:x_return_status: ' || x_return_status, 1);
5846 END IF;
5847
5848 IF l_debug_level > 0 THEN
5849 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.Process_Holds_Site' , 1 ) ;
5850 END IF;
5851 END IF; -- APPLY OR RELEASE
5852 EXCEPTION
5853 WHEN FND_API.G_EXC_ERROR THEN
5854
5855 IF l_debug_level > 0 THEN
5856 oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5857 END IF;
5858
5859 IF x_msg_count is not null then
5860 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5861 p_data => x_msg_data);
5862 end if;
5863
5864 RAISE FND_API.G_EXC_ERROR;
5865
5866 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5867 IF l_debug_level > 0 THEN
5868 oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5869 END IF;
5870
5871 FND_MSG_PUB.Count_And_Get
5872 ( p_count => x_msg_count
5873 , p_data => x_msg_data
5874 );
5875
5876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5877
5878 WHEN OTHERS THEN
5879 IF l_debug_level > 0 THEN
5880 oe_debug_pub.add( 'ERROR in oe_holds_pub.process_holds' , 1 ) ;
5881 END IF;
5882 RAISE;
5883 END Process_Holds_Site;
5884
5885 /* This procedure is called by AR/TCA/IEX to put a Hold on the customer/site
5886
5887 Note: This procedure as opposed to the previous API called by AR/TCA/IEX.
5888 Oe_Holds.Hold_API is a function and returns a number as return status. This new API Process_Holds
5889 doesn't pass back any arguments, return status etc. is this OK with the calling programs??
5890 This API throws exceptions
5891
5892 Need to fix the appropriate error messages
5893
5894 Validate the commit process
5895
5896 continue to process remaining holds or throw API error if an occurs when processing the holds in a loop??
5897
5898 */
5899 PROCEDURE Process_Holds
5900 ( p_api_version IN NUMBER
5901 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
5902 , p_hold_entity_code IN VARCHAR2
5903 --ER#7479609 , p_hold_entity_id IN NUMBER
5904 , p_hold_entity_id IN oe_hold_sources_all.hold_entity_id%TYPE --ER#7479609
5905 , p_hold_id IN NUMBER DEFAULT 1
5906 , p_release_reason_code IN VARCHAR2 DEFAULT NULL
5907 , p_action IN VARCHAR2
5908 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5909 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5910 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5911 ) IS
5912
5913 l_orig_org NUMBER := null;
5914
5915 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
5916 l_msg_count NUMBER := null;
5917 l_msg_data VARCHAR2(2000) := null;
5918 l_valid_acct VARCHAR2(1) := 'N';
5919
5920 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5921
5922 CURSOR c_valid_acct IS
5923 SELECT 'Y'
5924 FROM HZ_CUST_ACCOUNTS_ALL
5925 WHERE cust_account_id = p_hold_entity_id;
5926
5927 BEGIN
5928
5929 IF l_debug_level > 0 THEN
5930 oe_debug_pub.add( 'ENTERED OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
5931 END IF;
5932
5933 SAVEPOINT oe_process_holds;
5934
5935 IF p_action IS NULL OR p_action NOT IN ('APPLY','RELEASE') THEN
5936 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ACTION'); -- message taken from oe_holds
5937 fnd_message.set_token('ACTION',p_action);
5938 OE_MSG_PUB.ADD;
5939 RAISE FND_API.G_EXC_ERROR;
5940 END IF;
5941
5942 IF p_hold_entity_code IS NULL OR p_hold_entity_code NOT IN ('S','C') THEN
5943 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_HOLD_ENTITY_CODE'); -- message need to be seeded or use any existing one
5944 OE_MSG_PUB.ADD;
5945 RAISE FND_API.G_EXC_ERROR;
5946 END IF;
5947 -- hold the original org id being used by programs when calling process_holds.
5948 -- set the org org context to this original org when leaving process_holds.
5949 -- sHOULD IT BE SET EVEN WHEN EXITING WITH AN EXCEPTION??
5950
5951 l_orig_org := MO_GLOBAL.get_current_org_id;
5952
5953 IF p_hold_entity_code = 'S' THEN
5954
5955 Process_Holds_Site
5956 ( p_hold_entity_code => p_hold_entity_code
5957 , p_hold_entity_id => p_hold_entity_id
5958 , p_hold_id => p_hold_id
5959 , p_release_reason_code => p_release_reason_code
5960 , p_action => p_action
5961 , x_return_status => l_return_status
5962 , x_msg_count => l_msg_count
5963 , x_msg_data => l_msg_data
5964 );
5965
5966 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5967
5968 IF l_debug_level > 0 THEN
5969 oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
5970 END IF;
5971
5972 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5973 RAISE FND_API.G_EXC_ERROR;
5974 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5976 END IF;
5977 END IF;
5978
5979 ELSIF p_hold_entity_code = 'C' THEN
5980 -- validate the account
5981 OPEN c_valid_acct;
5982 FETCH c_valid_acct into l_valid_acct;
5983 CLOSE c_valid_acct;
5984
5985 IF l_valid_acct <> 'Y' THEN
5986 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_CUST_ACCOUNT'); -- get the correct message new or existing??
5987 -- fnd_message.set_token('',p_action);
5988 OE_MSG_PUB.ADD;
5989 RAISE FND_API.G_EXC_ERROR;
5990 END IF;
5991
5992 -- call process_holds_customer
5993
5994 Process_Holds_Customer
5995 ( p_hold_entity_code => p_hold_entity_code
5996 , p_hold_entity_id => p_hold_entity_id
5997 , p_hold_id => p_hold_id
5998 , p_release_reason_code => p_release_reason_code
5999 , p_action => p_action
6000 , x_return_status => l_return_status
6001 , x_msg_count => l_msg_count
6002 , x_msg_data => l_msg_data
6003 );
6004
6005 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6006
6007 IF l_debug_level > 0 THEN
6008 oe_debug_pub.add( 'ERROR AFTER PROCESS_HOLDS_SITE IN OE_HOLDS_PUB.PROCESS_HOLDS' ) ;
6009 END IF;
6010
6011 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
6012 RAISE FND_API.G_EXC_ERROR;
6013 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6015 END IF;
6016 END IF;
6017
6018 END IF;
6019
6020 IF l_orig_org IS NULL THEN
6021 MO_GLOBAL.set_policy_context('M',null);
6022 ELSE
6023 MO_GLOBAL.set_policy_context('S',l_orig_org);
6024 END IF;
6025
6026 x_return_status := l_return_status;
6027
6028 IF l_debug_level > 0 THEN
6029 oe_debug_pub.add( 'EXITING OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
6030 END IF;
6031 EXCEPTION
6032 WHEN FND_API.G_EXC_ERROR THEN
6033
6034 IF l_debug_level > 0 THEN
6035 oe_debug_pub.add( 'API EXECUTION ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
6036 END IF;
6037 x_return_status := l_return_status;
6038 x_msg_count := l_msg_count;
6039 x_msg_data := l_msg_data;
6040 IF l_msg_count is not null then
6041 OE_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
6042 p_data => l_msg_data);
6043 end if;
6044
6045 IF l_debug_level > 0 THEN
6046 oe_debug_pub.add( 'Error Message: '||l_msg_data , 1 ) ;
6047 END IF;
6048 ROLLBACK TO SAVEPOINT oe_process_holds;
6049
6050 -- Start Bug 14040669
6051 IF l_orig_org IS NULL THEN
6052 MO_GLOBAL.set_policy_context('M',null);
6053 ELSE
6054 MO_GLOBAL.set_policy_context('S',l_orig_org);
6055 END IF;
6056 -- End Bug 14040669
6057 RAISE FND_API.G_EXC_ERROR;
6058
6059 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6060
6061 IF l_debug_level > 0 THEN
6062 oe_debug_pub.add( 'API G_EXC_UNEXPECTED_ERROR IN OE_HOLDS_PUB.PROCESS_HOLDS' , 1 ) ;
6063 END IF;
6064 x_return_status := l_return_status;
6065 x_msg_count := l_msg_count;
6066 x_msg_data := l_msg_data;
6067 FND_MSG_PUB.Count_And_Get
6068 ( p_count => l_msg_count
6069 , p_data => l_msg_data
6070 );
6071 IF l_debug_level > 0 THEN
6072 oe_debug_pub.add( 'Error Message: '||l_msg_data , 1 ) ;
6073 END IF;
6074 ROLLBACK TO SAVEPOINT oe_process_holds;
6075
6076 -- Start Bug 14040669
6077 IF l_orig_org IS NULL THEN
6078 MO_GLOBAL.set_policy_context('M',null);
6079 ELSE
6080 MO_GLOBAL.set_policy_context('S',l_orig_org);
6081 END IF;
6082 -- End Bug 14040669
6083
6084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6085
6086 WHEN OTHERS THEN
6087 IF l_debug_level > 0 THEN
6088 oe_debug_pub.add( 'ERROR in oe_holds_pub.process_holds' , 1 ) ;
6089 END IF;
6090 x_return_status := l_return_status;
6091 x_msg_count := l_msg_count;
6092 x_msg_data := l_msg_data;
6093 ROLLBACK TO SAVEPOINT oe_process_holds;
6094
6095 -- Start Bug 14040669
6096 IF l_orig_org IS NULL THEN
6097 MO_GLOBAL.set_policy_context('M',null);
6098 ELSE
6099 MO_GLOBAL.set_policy_context('S',l_orig_org);
6100 END IF;
6101 -- End Bug 14040669
6102
6103 RAISE;
6104 END Process_Holds;
6105
6106 END OE_Holds_PUB;