[Home] [Help]
PACKAGE BODY: APPS.OE_HOLD_SOURCES_PVT
Source
1 PACKAGE BODY OE_Hold_Sources_Pvt AS
2 /* $Header: OEXVHLSB.pls 120.2 2005/08/10 12:08:44 zbutt noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Hold_Sources_Pvt';
7
8
9 PROCEDURE Utilities
10 ( p_user_id OUT NOCOPY NUMBER)
11
12 IS
13 --
14 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
15 --
16 BEGIN
17 p_user_id := NVL(FND_GLOBAL.USER_ID, -1);
18
19 END Utilities;
20
21 -------------------------------------------------------------------
22 -- ValidateHoldSource
23 -- Validates all the components that form a hold source
24 -- i.e. hold ID, hold entity code and hold entity ID.
25 --------------------------------------------------------------------
26 PROCEDURE ValidateHoldSource
27 ( p_hold_id IN NUMBER
28 , p_entity_code IN VARCHAR2
29 , p_entity_id IN NUMBER
30 , p_entity_code2 IN VARCHAR2
31 , p_entity_id2 IN NUMBER
32 , x_return_status OUT NOCOPY VARCHAR2
33
34 )
35 IS
36 l_dummy VARCHAR2(30) DEFAULT NULL;
37 --
38 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
39 --
40 BEGIN
41
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43
44
45 -- Validate Hold ID
46
47 BEGIN
48
49 SELECT 'x'
50 INTO l_dummy
51 FROM OE_HOLD_DEFINITIONS
52 WHERE HOLD_ID = p_hold_id
53 AND SYSDATE
54 BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
55 AND NVL(END_DATE_ACTIVE, SYSDATE );
56
57 EXCEPTION
58
59 WHEN NO_DATA_FOUND THEN
60 IF l_debug_level > 0 THEN
61 oe_debug_pub.add( 'INVALID HOLD ID' ) ;
62 END IF;
63 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_HOLD_ID');
64 FND_MESSAGE.SET_TOKEN('HOLD_ID',p_hold_id);
65 FND_MSG_PUB.ADD;
66 x_return_status := FND_API.G_RET_STS_ERROR;
67
68 END; -- Validate Hold ID
69
70
71 -- Validate Entity Code
72
73 IF p_entity_code NOT IN ('C','S','I','O') THEN
74 IF l_debug_level > 0 THEN
75 oe_debug_pub.add( 'INVALID ENTITY CODE' ) ;
76 END IF;
77 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CODE');
78 FND_MESSAGE.SET_TOKEN('ENTITY_CODE',p_entity_code);
79 FND_MSG_PUB.ADD;
80 x_return_status := FND_API.G_RET_STS_ERROR;
81 END IF; -- Validate Entity Code
82
83 -- Validate Entity ID
84
85 BEGIN
86
87 IF p_entity_code = 'C' THEN
88
89 SELECT 'x'
90 INTO l_dummy
91 FROM OE_SOLD_TO_ORGS_V
92 WHERE ORGANIZATION_ID = p_entity_id;
93
94 ELSIF p_entity_code = 'S' THEN
95
96 SELECT 'x'
97 INTO l_dummy
98 FROM OE_SHIP_TO_ORGS_V
99 WHERE ORGANIZATION_ID = p_entity_id
100 UNION
101 SELECT 'x'
102 FROM OE_INVOICE_TO_ORGS_V
103 WHERE ORGANIZATION_ID = p_entity_id;
104
105 ELSIF p_entity_code = 'I' THEN
106
107 SELECT 'x'
108 INTO l_dummy
109 FROM MTL_SYSTEM_ITEMS
110 WHERE inventory_item_id = p_entity_id;
111
112 ELSIF p_entity_code = 'O' THEN
113
114 SELECT 'x'
115 INTO l_dummy
116 FROM OE_ORDER_HEADERS
117 WHERE header_id = p_entity_id;
118
119 END IF; -- Validate Entity ID
120
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 IF l_debug_level > 0 THEN
124 oe_debug_pub.add( 'INVALID ENTITY ID' ) ;
125 END IF;
126 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_ID');
127 FND_MESSAGE.SET_TOKEN('ENTITY_ID',p_entity_id);
128 FND_MSG_PUB.ADD;
129 RAISE FND_API.G_EXC_ERROR;
130 -- too many rows maybe raised if the same entity id
131 -- e.g. an item exists in two orgs.
132 WHEN TOO_MANY_ROWS THEN
133 null;
134
135 END; -- Validate Entity ID
136 ----------------------------------------------
137
138 -- Validate Second Entity Code.
139 -- Note second entity is OPTIONAL and may not be passed in
140 IF p_entity_code2 is not NULL THEN
141
142 IF p_entity_code2 NOT IN ('C','S','I','O') THEN
143 IF l_debug_level > 0 THEN
144 oe_debug_pub.add( 'INVALID SECOND ENTITY CODE' ) ;
145 END IF;
146 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CODE');
147 FND_MESSAGE.SET_TOKEN('ENTITY_CODE',p_entity_code2);
148 FND_MSG_PUB.ADD;
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 END IF; -- Validate Entity Code
151
152 -- Validate Entity ID
153
154 BEGIN
155
156 IF p_entity_code2 = 'C' THEN
157
158 SELECT 'x'
159 INTO l_dummy
160 FROM OE_SOLD_TO_ORGS_V
161 WHERE ORGANIZATION_ID = p_entity_id2;
162
163 ELSIF p_entity_code2 = 'S' THEN
164
165 SELECT 'x'
166 INTO l_dummy
167 FROM OE_SHIP_TO_ORGS_V
168 WHERE ORGANIZATION_ID = p_entity_id2
169 UNION
170 SELECT 'x'
171 FROM OE_INVOICE_TO_ORGS_V
172 WHERE ORGANIZATION_ID = p_entity_id2;
173
174 ELSIF p_entity_code2 = 'I' THEN
175
176 SELECT 'x'
177 INTO l_dummy
178 FROM MTL_SYSTEM_ITEMS
179 WHERE inventory_item_id = p_entity_id2;
180
181 ELSIF p_entity_code2 = 'O' THEN
182
183 SELECT 'x'
184 INTO l_dummy
185 FROM OE_ORDER_HEADERS
186 WHERE header_id = p_entity_id2;
187
188 END IF; -- Validate Entity ID
189
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 IF l_debug_level > 0 THEN
193 oe_debug_pub.add( 'INVALID SECOND ENTITY ID' ) ;
194 END IF;
195 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_ID');
196 FND_MESSAGE.SET_TOKEN('ENTITY_ID',p_entity_id2);
197 FND_MSG_PUB.ADD;
198 RAISE FND_API.G_EXC_ERROR;
199 -- too many rows maybe raised if the same entity id
200 -- e.g. an item exists in two orgs.
201 WHEN TOO_MANY_ROWS THEN
202 null;
203
204 END; -- Validate Second Entity ID
205
206 END IF; -- p_entity_code2 is not NULL THEN
207 ----------------------------------------------
208
209 EXCEPTION
210 WHEN FND_API.G_EXC_ERROR THEN
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 WHEN OTHERS THEN
213 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
214 FND_MSG_PUB.Add_Exc_Msg
215 (G_PKG_NAME
216 ,'ValidateHoldSource');
217 END IF;
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
219
220 END ValidateHoldSource;
221
222
223 PROCEDURE Create_Hold_Source
224 ( p_hold_source_rec IN OE_Hold_Sources_Pvt.Hold_Source_REC
225 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
226 , x_hold_source_id OUT NOCOPY NUMBER
227
228 , x_return_status OUT NOCOPY VARCHAR2
229
230 , x_msg_count OUT NOCOPY NUMBER
231
232 , x_msg_data OUT NOCOPY VARCHAR2
233
234 )
235 IS
236 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_HOLD_SOURCE';
237 l_user_id NUMBER;
238 l_count NUMBER;
239 l_org_id NUMBER;
240 --
241 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
242 --
243 BEGIN
244
245 IF l_debug_level > 0 THEN
246 oe_debug_pub.add( 'IN OE_HOLD_SOURCES_PVT.CREATE_HOLD_SOURCE' ) ;
247 END IF;
248 SAVEPOINT Create_Hold_Source;
249
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 -- Retrieve user id
253
254 Utilities(l_user_id);
255
256 -- Get the ORG ID - XXXXX Check this
257 l_org_id := OE_GLOBALS.G_ORG_ID;
258 if l_org_id IS NULL THEN
259 OE_GLOBALS.Set_Context;
260 l_org_id := OE_GLOBALS.G_ORG_ID;
261 end if;
262
263 --dbms_output.put_line ('IN Create_Hold_Source');
264 -- VALIDATION
265
266 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
267
268 IF l_debug_level > 0 THEN
269 oe_debug_pub.add( 'CALLING VALIDATEHOLDSOURCE' ) ;
270 END IF;
271 -- Validation of input arguments
272 ValidateHoldSource
273 ( p_hold_id => p_hold_source_rec.hold_id
274 , p_entity_code => p_hold_source_rec.hold_entity_code
275 , p_entity_id => p_hold_source_rec.hold_entity_id
276 , p_entity_code2 => p_hold_source_rec.hold_entity_code2
277 , p_entity_id2 => p_hold_source_rec.hold_entity_id2
278 , x_return_status => x_return_status
279 );
280
281 --dbms_output.put_line ('ValidateHoldSource->x_return_status->' || x_return_status );
282 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
283 IF l_debug_level > 0 THEN
284 oe_debug_pub.add( 'VALIDATION NOT SUCCESSFUL' ) ;
285 END IF;
286 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
287 RAISE FND_API.G_EXC_ERROR;
288 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290 END IF;
291 END IF;
292
293 -- Check for duplicate hold source
294 SELECT count(*)
295 INTO l_count
296 FROM OE_HOLD_SOURCES
297 WHERE hold_id = p_hold_source_rec.hold_id
298 AND hold_entity_code = p_hold_source_rec.hold_entity_code
299 AND hold_entity_id = p_hold_source_rec.hold_entity_id
300 AND nvl(hold_entity_code2, 'NO_ENTITY_CODE2') =
301 nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
302 AND nvl(hold_entity_id2, -99) =
303 nvl(p_hold_source_rec.hold_entity_id2, -99)
304 AND NVL(released_flag, 'N') = 'N';
305
306 IF l_count > 0 THEN
307 --dbms_output.put_line ('Duplicate Hold Source');
308 IF l_debug_level > 0 THEN
309 oe_debug_pub.add( 'DUPLICATE HOLD SOURCE' ) ;
310 END IF;
311 FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD_SOURCE');
312 FND_MSG_PUB.ADD;
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315
316 END IF; -- End of VALIDATION
317
318
319
320 -- Inserting a NEW HOLD SOURCE record
321
322 SELECT OE_HOLD_SOURCES_S.NEXTVAL
323 INTO x_hold_source_id
324 FROM DUAL;
325
326
327 INSERT INTO OE_HOLD_SOURCES_ALL
328 ( HOLD_SOURCE_ID
329 , LAST_UPDATE_DATE
330 , LAST_UPDATED_BY
331 , CREATION_DATE
332 , CREATED_BY
333 , LAST_UPDATE_LOGIN
334 , PROGRAM_APPLICATION_ID
335 , PROGRAM_ID
336 , PROGRAM_UPDATE_DATE
337 , REQUEST_ID
338 , HOLD_ID
339 , HOLD_ENTITY_CODE
340 , HOLD_ENTITY_ID
341 , HOLD_UNTIL_DATE
342 , RELEASED_FLAG
343 , HOLD_COMMENT
344 , ORG_ID
345 , CONTEXT
346 , ATTRIBUTE1
347 , ATTRIBUTE2
348 , ATTRIBUTE3
349 , ATTRIBUTE4
350 , ATTRIBUTE5
351 , ATTRIBUTE6
352 , ATTRIBUTE7
353 , ATTRIBUTE8
354 , ATTRIBUTE9
355 , ATTRIBUTE10
356 , ATTRIBUTE11
357 , ATTRIBUTE12
358 , ATTRIBUTE13
359 , ATTRIBUTE14
360 , ATTRIBUTE15
361 , HOLD_RELEASE_ID
362 ,HOLD_ENTITY_CODE2
363 ,HOLD_ENTITY_ID2
364 )
365 VALUES
366 ( x_hold_source_id
367 , sysdate
368 , l_user_id
369 , sysdate
370 , l_user_id
371 , p_hold_source_rec.LAST_UPDATE_LOGIN
372 , p_hold_source_rec.PROGRAM_APPLICATION_ID
373 , p_hold_source_rec.PROGRAM_ID
374 , p_hold_source_rec.PROGRAM_UPDATE_DATE
375 , p_hold_source_rec.REQUEST_ID
376 , p_hold_source_rec.HOLD_ID
377 , p_hold_source_rec.HOLD_ENTITY_CODE
378 , p_hold_source_rec.HOLD_ENTITY_ID
379 , p_hold_source_rec.HOLD_UNTIL_DATE
380 , p_hold_source_rec.RELEASED_FLAG
381 , p_hold_source_rec.HOLD_COMMENT
382 , l_org_id
383 , p_hold_source_rec.CONTEXT
384 , p_hold_source_rec.ATTRIBUTE1
385 , p_hold_source_rec.ATTRIBUTE2
386 , p_hold_source_rec.ATTRIBUTE3
387 , p_hold_source_rec.ATTRIBUTE4
388 , p_hold_source_rec.ATTRIBUTE5
389 , p_hold_source_rec.ATTRIBUTE6
390 , p_hold_source_rec.ATTRIBUTE7
391 , p_hold_source_rec.ATTRIBUTE8
392 , p_hold_source_rec.ATTRIBUTE9
393 , p_hold_source_rec.ATTRIBUTE10
394 , p_hold_source_rec.ATTRIBUTE11
395 , p_hold_source_rec.ATTRIBUTE12
396 , p_hold_source_rec.ATTRIBUTE13
397 , p_hold_source_rec.ATTRIBUTE14
398 , p_hold_source_rec.ATTRIBUTE15
399 , p_hold_source_rec.HOLD_RELEASE_ID
400 ,p_hold_source_rec.HOLD_ENTITY_CODE2
401 ,p_hold_source_rec.HOLD_ENTITY_ID2
402 );
403
404 --dbms_output.put_line ('End CreateHold_source');
405 EXCEPTION
406 WHEN FND_API.G_EXC_ERROR THEN
407 ROLLBACK TO Create_Hold_Source;
408 x_return_status := FND_API.G_RET_STS_ERROR;
409 FND_MSG_PUB.Count_And_Get
410 ( p_count => x_msg_count
411 , p_data => x_msg_data
412 );
413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
414 ROLLBACK TO Create_Hold_Source;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 FND_MSG_PUB.Count_And_Get
417 ( p_count => x_msg_count
418 , p_data => x_msg_data
419 );
420 WHEN OTHERS THEN
421 ROLLBACK TO Create_Hold_Source;
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 IF FND_MSG_PUB.Check_Msg_Level
424 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
425 THEN
426 FND_MSG_PUB.Add_Exc_Msg
427 ( G_PKG_NAME
428 , l_api_name
429 );
430 END IF;
431 FND_MSG_PUB.Count_And_Get
432 ( p_count => x_msg_count
433 , p_data => x_msg_data
434 );
435 END Create_Hold_Source;
436
437
438 PROCEDURE Release_Hold_Source
439 ( p_hold_id IN NUMBER DEFAULT NULL
440 , p_entity_code IN VARCHAR2 DEFAULT NULL
441 , p_entity_id IN NUMBER DEFAULT NULL
442 , p_entity_code2 IN VARCHAR2 DEFAULT NULL
443 , p_entity_id2 IN NUMBER DEFAULT NULL
444 , p_hold_release_rec IN OE_Hold_Sources_Pvt.Hold_Release_REC
445 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
446 , x_return_status OUT NOCOPY VARCHAR2
447
448 , x_msg_count OUT NOCOPY NUMBER
449
450 , x_msg_data OUT NOCOPY VARCHAR2
451
452 )
453 IS
454 l_user_id NUMBER;
455 l_hold_source_id NUMBER;
456 l_hold_release_id NUMBER;
457 l_hold_release_rec OE_Hold_Sources_Pvt.Hold_Release_REC;
458 CURSOR hold_source IS
459 SELECT HS.HOLD_SOURCE_ID
460 FROM OE_HOLD_SOURCES HS
461 WHERE HS.HOLD_ID = p_hold_id
462 AND HS.RELEASED_FLAG = 'N'
463 AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
464 AND HS.HOLD_ENTITY_CODE = p_entity_code
465 AND HS.HOLD_ENTITY_ID = p_entity_id
466 AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =
467 nvl(p_entity_code2, 'NO_ENTITY_CODE2')
468 AND nvl(HS.HOLD_ENTITY_ID2, -99) =
469 nvl(p_entity_id2, -99);
470 --
471 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
472 --
473 BEGIN
474
475 SAVEPOINT release_hold_source;
476
477 -- Retrieve user id
478
479 Utilities(l_user_id);
480
481 x_return_status := FND_API.G_RET_STS_SUCCESS;
482
483
484 l_hold_release_rec := p_hold_release_rec;
485
486 -- Retrieving hold source ID if not passed
487 IF l_hold_release_rec.hold_source_id IS NULL THEN
488
489 OPEN hold_source;
490 FETCH hold_source INTO l_hold_release_rec.hold_source_id;
491 IF (hold_source%NOTFOUND) THEN
492 FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_SOURCE');
493 FND_MSG_PUB.ADD;
494 RAISE FND_API.G_EXC_ERROR;
495 END IF;
496 CLOSE hold_source;
497
498 END IF;
499
500
501 -- Inserting record into the hold releases table
502 OE_Hold_Sources_Pvt.Insert_Hold_Release
503 ( p_hold_release_rec => l_hold_release_rec
504 , p_validation_level => p_validation_level
505 , x_hold_release_id => l_hold_release_id
506 , x_return_status => x_return_status
507 );
508 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
509 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
510 RAISE FND_API.G_EXC_ERROR;
511 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
513 END IF;
514 END IF;
515
516
517 -- Flag all orders and order line holds for this hold source
518 -- as released
519 UPDATE oe_order_holds_all
520 SET hold_release_id = l_hold_release_id
521 , LAST_UPDATED_BY = l_user_id
522 , LAST_UPDATE_DATE = sysdate
523 WHERE hold_source_id = l_hold_source_id
524 AND hold_release_id IS NULL;
525
526 -- Completing CHECK_HOLD activities in related flows
527 -- XXXXXX Complete this later
528 Release_Hold_Source_WF
529 ( p_entity_code => p_entity_code
530 , p_entity_id => p_entity_id
531 , x_return_status => x_return_status
532 );
533
534 -- Flag the hold source as released
535 UPDATE oe_hold_sources
536 SET hold_release_id = l_hold_release_id
537 , released_flag = 'Y'
538 , LAST_UPDATED_BY = l_user_id
539 , LAST_UPDATE_DATE = sysdate
540 WHERE hold_source_id = l_hold_source_id;
541
542 EXCEPTION
543 WHEN FND_API.G_EXC_ERROR THEN
544 ROLLBACK TO release_hold_source;
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
547 ROLLBACK TO release_hold_source;
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 WHEN OTHERS THEN
550 ROLLBACK TO release_hold_source;
551 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
552 FND_MSG_PUB.Add_Exc_Msg
553 (G_PKG_NAME
554 ,'Release_Hold_Source');
555 END IF;
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557
558 END Release_Hold_Source;
559
560
561 PROCEDURE Query_Hold_Source
562 ( p_header_id IN NUMBER
563 , x_hold_source_tbl OUT NOCOPY OE_Hold_Sources_PVT.Hold_Source_TBL
564
565 , x_return_status OUT NOCOPY VARCHAR2
566
567 )
568 IS
569 l_hold_source_REC OE_Hold_Sources_Pvt.Hold_Source_REC;
570 i BINARY_INTEGER := 0;
571 CURSOR hold_source_REC IS
572 SELECT
573 HOLD_SOURCE_ID
574 , LAST_UPDATE_DATE
575 , LAST_UPDATED_BY
576 , CREATION_DATE
577 , CREATED_BY
578 , LAST_UPDATE_LOGIN
579 , PROGRAM_APPLICATION_ID
580 , PROGRAM_ID
581 , PROGRAM_UPDATE_DATE
582 , REQUEST_ID
583 , HOLD_ID
584 , HOLD_ENTITY_CODE
585 , HOLD_ENTITY_ID
586 , HOLD_UNTIL_DATE
587 , RELEASED_FLAG
588 , HOLD_COMMENT
589 , CONTEXT
590 , ATTRIBUTE1
591 , ATTRIBUTE2
592 , ATTRIBUTE3
593 , ATTRIBUTE4
594 , ATTRIBUTE5
595 , ATTRIBUTE6
596 , ATTRIBUTE7
597 , ATTRIBUTE8
598 , ATTRIBUTE9
599 , ATTRIBUTE10
600 , ATTRIBUTE11
601 , ATTRIBUTE12
602 , ATTRIBUTE13
603 , ATTRIBUTE14
604 , ATTRIBUTE15
605 , ORG_ID
606 , HOLD_RELEASE_ID
607 , HOLD_ENTITY_CODE2
608 , HOLD_ENTITY_ID2
609 FROM OE_HOLD_SOURCES
610 WHERE hold_source_id IN (SELECT hold_source_id
611 FROM OE_ORDER_HOLDS
612 WHERE header_id = p_header_id
613 AND line_id IS NULL
614 AND hold_release_id IS NULL
615 );
616 --
617 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
618 --
619 BEGIN
620
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623
624 FOR l_hold_source_REC IN hold_source_REC
625 LOOP
626
627 i := i+1;
628 x_hold_source_tbl(i) := l_hold_source_REC;
629
630 END LOOP;
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
635 FND_MSG_PUB.Add_Exc_Msg
636 (G_PKG_NAME
637 ,'Query_Hold_Source');
638 END IF;
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640
641 END Query_Hold_Source;
642
643
644 PROCEDURE Query_Line__Hold_Source
645 ( p_line_id IN NUMBER
646 , x_hold_source_tbl OUT NOCOPY OE_Hold_Sources_PVT.Hold_Source_TBL
647 , x_return_status OUT NOCOPY VARCHAR2
648
649 )
650 IS
651 l_hold_source_REC OE_Hold_Sources_Pvt.Hold_Source_REC;
652 i BINARY_INTEGER := 0;
653 CURSOR hold_source_REC IS
654 SELECT
655 HOLD_SOURCE_ID
656 , LAST_UPDATE_DATE
657 , LAST_UPDATED_BY
658 , CREATION_DATE
659 , CREATED_BY
660 , LAST_UPDATE_LOGIN
661 , PROGRAM_APPLICATION_ID
662 , PROGRAM_ID
663 , PROGRAM_UPDATE_DATE
664 , REQUEST_ID
665 , HOLD_ID
666 , HOLD_ENTITY_CODE
667 , HOLD_ENTITY_ID
668 , HOLD_UNTIL_DATE
669 , RELEASED_FLAG
670 , HOLD_COMMENT
671 , CONTEXT
672 , ATTRIBUTE1
673 , ATTRIBUTE2
674 , ATTRIBUTE3
675 , ATTRIBUTE4
676 , ATTRIBUTE5
677 , ATTRIBUTE6
678 , ATTRIBUTE7
679 , ATTRIBUTE8
680 , ATTRIBUTE9
681 , ATTRIBUTE10
682 , ATTRIBUTE11
683 , ATTRIBUTE12
684 , ATTRIBUTE13
685 , ATTRIBUTE14
686 , ATTRIBUTE15
687 , ORG_ID
688 , HOLD_RELEASE_ID
689 , HOLD_ENTITY_CODE2
690 , HOLD_ENTITY_ID2
691 FROM OE_HOLD_SOURCES
692 WHERE hold_source_id IN (SELECT hold_source_id
693 FROM OE_ORDER_HOLDS
694 WHERE line_id = p_line_id
695 AND hold_release_id IS NULL
696 );
697
698 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
699 BEGIN
700
701 x_return_status := FND_API.G_RET_STS_SUCCESS;
702
703
704 FOR l_hold_source_REC IN hold_source_REC
705 LOOP
706
707 i := i+1;
708 x_hold_source_tbl(i) := l_hold_source_REC;
709
710 END LOOP;
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
715 FND_MSG_PUB.Add_Exc_Msg
716 (G_PKG_NAME
717 ,'Query_Hold_Source');
718 END IF;
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720
721 END Query_Line__Hold_Source;
722
723
724
725 PROCEDURE Insert_Hold_Release
726 ( p_hold_release_rec IN OE_Hold_Sources_Pvt.Hold_Release_Rec
727 , p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL
728 , x_hold_release_id OUT NOCOPY NUMBER
729
730 , x_return_status OUT NOCOPY VARCHAR2
731
732 )
733 IS
734 --l_hold_entity_id NUMBER;
735 --l_hold_entity_code VARCHAR2(1);
736 l_dummy VARCHAR2(30);
737 l_user_id NUMBER;
738 --
739 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
740 --
741 BEGIN
742
743 IF l_debug_level > 0 THEN
744 oe_debug_pub.add( 'IN INSERT_HOLD_RELEASE' ) ;
745 END IF;
746
747 SAVEPOINT insert_hold_release;
748
749 x_return_status := FND_API.G_RET_STS_SUCCESS;
750
751 Utilities(l_user_id);
752
753 ------------------------------------------------------------------
754 -- Validate Input Parameters
755 ------------------------------------------------------------------
756
757 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
758
759 -- Validate Reason Code
760
761 BEGIN
762
763 SELECT 'x'
764 INTO l_dummy
765 FROM OE_LOOKUPS
766 WHERE LOOKUP_TYPE = 'RELEASE_REASON'
767 AND LOOKUP_CODE = p_hold_release_rec.release_reason_code;
768
769 EXCEPTION
770
771 WHEN NO_DATA_FOUND THEN
772 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_REASON_CODE');
773 FND_MESSAGE.SET_TOKEN('REASON_CODE',p_hold_release_rec.release_reason_code);
774 FND_MSG_PUB.ADD;
775 RAISE FND_API.G_EXC_ERROR;
776
777 END; -- Validate Reason Code
778
779 END IF; -- End of Validation
780
781 -- To_do : Remove the following code when the redundant columns hold_entity_id
782 -- and hold_entity_code are removed from OE_HOLD_RELEASES.
783 --SELECT hold_entity_id, hold_entity_code
784 --INTO l_hold_entity_id, l_hold_entity_code
785 --FROM oe_hold_sources
786 --WHERE hold_source_id = p_hold_release_rec.hold_source_id;
787
788
789 IF l_debug_level > 0 THEN
790 oe_debug_pub.add( 'BEFORE INSERT' ) ;
791 END IF;
792
793
794 SELECT OE_HOLD_RELEASES_S.NEXTVAL
795 INTO x_hold_release_id
796 FROM DUAL;
797
798 INSERT INTO OE_HOLD_RELEASES
799 ( HOLD_RELEASE_ID
800 , CREATION_DATE
801 , CREATED_BY
802 , LAST_UPDATE_DATE
803 , LAST_UPDATED_BY
804 , LAST_UPDATE_LOGIN
805 , PROGRAM_APPLICATION_ID
806 , PROGRAM_ID
807 , PROGRAM_UPDATE_DATE
808 , REQUEST_ID
809 , HOLD_SOURCE_ID
810 -- , HOLD_ENTITY_ID
811 -- , HOLD_ENTITY_CODE
812 , RELEASE_REASON_CODE
813 , RELEASE_COMMENT
814 , CONTEXT
815 , ATTRIBUTE1
816 , ATTRIBUTE2
817 , ATTRIBUTE3
818 , ATTRIBUTE4
819 , ATTRIBUTE5
820 , ATTRIBUTE6
821 , ATTRIBUTE7
822 , ATTRIBUTE8
823 , ATTRIBUTE9
824 , ATTRIBUTE10
825 , ATTRIBUTE11
826 , ATTRIBUTE12
827 , ATTRIBUTE13
828 , ATTRIBUTE14
829 , ATTRIBUTE15
830 )
831 VALUES
832 ( x_hold_release_id
833 , sysdate
834 , l_user_id
835 , sysdate
836 , l_user_id
837 , p_hold_release_rec.LAST_UPDATE_LOGIN
838 , p_hold_release_rec.PROGRAM_APPLICATION_ID
839 , p_hold_release_rec.PROGRAM_ID
840 , p_hold_release_rec.PROGRAM_UPDATE_DATE
841 , p_hold_release_rec.REQUEST_ID
842 , p_hold_release_rec.HOLD_SOURCE_ID
843 -- To_do : Remove the following code when the redundant columns hold_entity_id
844 -- and hold_entity_code are removed from OE_HOLD_RELEASES.
845 -- , l_hold_entity_id
846 -- , l_hold_entity_code
847 , p_hold_release_rec.RELEASE_REASON_CODE
848 , p_hold_release_rec.RELEASE_COMMENT
849 , p_hold_release_rec.CONTEXT
850 , p_hold_release_rec.ATTRIBUTE1
851 , p_hold_release_rec.ATTRIBUTE2
852 , p_hold_release_rec.ATTRIBUTE3
853 , p_hold_release_rec.ATTRIBUTE4
854 , p_hold_release_rec.ATTRIBUTE5
855 , p_hold_release_rec.ATTRIBUTE6
856 , p_hold_release_rec.ATTRIBUTE7
857 , p_hold_release_rec.ATTRIBUTE8
858 , p_hold_release_rec.ATTRIBUTE9
859 , p_hold_release_rec.ATTRIBUTE10
860 , p_hold_release_rec.ATTRIBUTE11
861 , p_hold_release_rec.ATTRIBUTE12
862 , p_hold_release_rec.ATTRIBUTE13
863 , p_hold_release_rec.ATTRIBUTE14
864 , p_hold_release_rec.ATTRIBUTE15
865 );
866
867 IF l_debug_level > 0 THEN
868 oe_debug_pub.add( 'AFTER INSERT' ) ;
869 END IF;
870 EXCEPTION
871 WHEN FND_API.G_EXC_ERROR THEN
872 x_return_status := FND_API.G_RET_STS_ERROR;
873 ROLLBACK TO insert_hold_release;
874 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
876 ROLLBACK TO insert_hold_release;
877 WHEN OTHERS THEN
878 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
879 FND_MSG_PUB.Add_Exc_Msg
880 (G_PKG_NAME
881 ,'Insert_Hold_Release');
882 END IF;
883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
884 ROLLBACK TO insert_hold_release;
885 END Insert_Hold_Release;
886
887
888
889 PROCEDURE Release_Hold_Source_WF
890 ( p_entity_code IN VARCHAR2
891 , p_entity_id IN NUMBER
892 , x_return_status OUT NOCOPY VARCHAR2
893
894 )
895 IS
896 l_site_code VARCHAR2(30);
897 --CURSOR customer_wf IS
898 -- SELECT wf.item_type item_type, wf.item_key item_key
899 -- FROM wf_item_activity_statuses_v wf, oe_order_headers h,
900 -- oe_order_lines_all l
901 -- WHERE activity_name = 'CHECK_HOLDS'
902 -- AND activity_status_code = 'NOTIFIED'
903 -- AND ( (item_type = 'OEOH'
904 -- AND item_key = to_char(h.header_id))
905 -- OR (item_type = 'OEOL'
906 -- AND item_key = to_char(l.line_id)
907 -- AND l.header_id = h.header_id)
908 -- )
909 -- AND h.sold_to_org_id = p_entity_id;
910 --CURSOR bill_to_site_wf IS
911 -- SELECT wf.item_type item_type, wf.item_key item_key
912 -- FROM wf_item_activity_statuses_v wf,
913 -- oe_order_lines_all l
914 -- WHERE activity_name = 'CHECK_HOLDS'
915 -- AND activity_status_code = 'NOTIFIED'
916 -- AND item_type = 'OEOL'
917 -- AND item_key = to_char(l.line_id)
918 -- AND l.invoice_to_org_id = p_entity_id;
919 --CURSOR ship_to_site_wf IS
920 -- SELECT wf.item_type item_type, wf.item_key item_key
921 -- FROM wf_item_activity_statuses_v wf,
922 -- oe_order_lines_all l
923 -- WHERE activity_name = 'CHECK_HOLDS'
924 -- AND activity_status_code = 'NOTIFIED'
925 -- AND item_type = 'OEOL'
926 -- AND item_key = to_char(l.line_id)
927 -- AND l.ship_to_org_id = p_entity_id;
928 --CURSOR item_wf IS
929 -- SELECT wf.item_type item_type, wf.item_key item_key
930 -- FROM wf_item_activity_statuses_v wf,
931 -- oe_order_lines_all l
932 -- WHERE activity_name = 'CHECK_HOLDS'
933 -- AND activity_status_code = 'NOTIFIED'
934 -- AND item_type = 'OEOL'
935 -- AND item_key = to_char(l.line_id)
936 -- AND l.inventory_item_id = p_entity_id;
937 --CURSOR order_wf IS
938 -- SELECT wf.item_type item_type, wf.item_key item_key
939 -- FROM wf_item_activity_statuses_v wf, oe_order_headers h,
940 -- oe_order_lines_all l
941 -- WHERE activity_name = 'CHECK_HOLDS'
942 -- AND activity_status_code = 'NOTIFIED'
943 -- AND ( (item_type = 'OEOH'
944 -- AND item_key = to_char(p_entity_id))
945 -- OR (item_type = 'OEOL'
946 -- AND item_key = to_char(l.line_id)
947 -- AND l.header_id = p_entity_id)
948 -- );
949 -- --
950 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
951 --
952 BEGIN
953
954 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
955
956 -- CUSTOMER HOLD SOURCE
957 /* IF p_entity_code = 'C' THEN
958
959 FOR curr_wf IN customer_wf LOOP
960
961 WF_ENGINE.CompleteActivity( curr_wf.item_type
962 , curr_wf.item_key
963 , 'CHECK_HOLDS'
964 , 'HOLD_RELEASED'
965 );
966 END LOOP;
967 */
968 -- SITE HOLD SOURCE
969 /* ELSIF p_entity_code = 'S' THEN
970
971 SELECT SITE.SITE_USE_CODE
972 INTO l_site_code
973 FROM HZ_CUST_SITE_USES SITE, -- Bug 2138398
974 HR_ORGANIZATION_INFORMATION INFO
975 WHERE INFO.ORGANIZATION_ID = p_entity_id
976 AND INFO.ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
977 AND SITE.SITE_USE_ID = TO_NUMBER ( INFO.ORG_INFORMATION2 );
978 */
979 -- Bill-to site hold source
980 /* IF l_site_code = 'BILL_TO' THEN
981
982 FOR curr_wf IN bill_to_site_wf LOOP
983
984 WF_ENGINE.CompleteActivity( curr_wf.item_type
985 , curr_wf.item_key
986 , 'CHECK_HOLDS'
987 , 'HOLD_RELEASED'
988 );
989 END LOOP;
990 */
991 -- Ship-to site hold source
992 /* ELSIF l_site_code = 'SHIP_TO' THEN
993
994 FOR curr_wf IN ship_to_site_wf LOOP
995
996 WF_ENGINE.CompleteActivity( curr_wf.item_type
997 , curr_wf.item_key
998 , 'CHECK_HOLDS'
999 , 'HOLD_RELEASED'
1000 );
1001 END LOOP;
1002
1003 END IF;
1004
1005 */
1006 -- ITEM HOLD SOURCE
1007 /* ELSIF p_entity_code = 'I' THEN
1008
1009 FOR curr_wf IN item_wf LOOP
1010
1011 WF_ENGINE.CompleteActivity( curr_wf.item_type
1012 , curr_wf.item_key
1013 , 'CHECK_HOLDS'
1014 , 'HOLD_RELEASED'
1015 );
1016 END LOOP;
1017 */
1018 -- ORDER HOLD SOURCE
1019 /* ELSIF p_entity_code = 'O' THEN
1020
1021 FOR curr_wf IN order_wf LOOP
1022
1023 WF_ENGINE.CompleteActivity( curr_wf.item_type
1024 , curr_wf.item_key
1025 , 'CHECK_HOLDS'
1026 , 'HOLD_RELEASED'
1027 );
1028 END LOOP;
1029
1030 END IF;
1031 */
1032 null;
1033
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 ROLLBACK TO release_hold_source_wf;
1037 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1038 FND_MSG_PUB.Add_Exc_Msg
1039 (G_PKG_NAME
1040 ,'Release_Hold_Source_WF');
1041 END IF;
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043
1044 END Release_Hold_Source_WF;
1045
1046
1047 END OE_Hold_Sources_Pvt;