[Home] [Help]
PACKAGE BODY: APPS.INV_STAGED_RESERVATION_UTIL
Source
1 PACKAGE BODY inv_staged_reservation_util AS
2 /* $Header: INVRSV9B.pls 120.1 2005/06/20 11:27:20 appldev ship $*/
3 --
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_STAGED_RESERVATION_UTIL';
5 --
6 PROCEDURE query_staged_flag
7 ( x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY NUMBER,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 x_staged_flag OUT NOCOPY VARCHAR2,
11 p_reservation_id IN NUMBER)
12 is
13 l_api_name CONSTANT VARCHAR2(30) := 'query_staged_flag';
14 BEGIN
15 x_return_status := fnd_api.g_ret_sts_success;
16
17 SAVEPOINT query_stage_sa;
18
19 select nvl(staged_flag,'N')
20 into x_staged_flag
21 from mtl_reservations
22 where reservation_id = p_reservation_id;
23
24 EXCEPTION
25 WHEN fnd_api.g_exc_error THEN
26 ROLLBACK TO query_stage_sa;
27 x_return_status := fnd_api.g_ret_sts_error ;
28
29 -- Get message count and data
30 fnd_msg_pub.count_and_get
31 ( p_count => x_msg_count
32 , p_data => x_msg_data
33 );
34 WHEN fnd_api.g_exc_unexpected_error THEN
35 ROLLBACK TO query_stage_sa;
36 x_return_status := fnd_api.g_ret_sts_unexp_error ;
37
38 -- Get message count and data
39 fnd_msg_pub.count_and_get
40 ( p_count => x_msg_count
41 , p_data => x_msg_data
42 );
43 WHEN OTHERS THEN
44 ROLLBACK TO query_stage_sa;
45 x_return_status := fnd_api.g_ret_sts_unexp_error ;
46
47 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
48 THEN
49 fnd_msg_pub.add_exc_msg
50 ( g_pkg_name
51 , l_api_name
52 );
53 END IF;
54 -- Get message count and data
55 fnd_msg_pub.count_and_get
56 ( p_count => x_msg_count
57 , p_data => x_msg_data
58 );
59
60 END query_staged_flag;
61
62 PROCEDURE update_staged_flag
63 ( x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2,
66 p_reservation_id IN NUMBER,
67 p_staged_flag IN VARCHAR2)
68 is
69 l_api_name CONSTANT VARCHAR2(30) := 'update_staged_flag';
70 l_ship_ready_flag NUMBER; -- Bug #2816312
71 BEGIN
72 x_return_status := fnd_api.g_ret_sts_success;
73 -- Bug #2816312. +5 lines.
74 -- Also updating the ship_ready_flag along with the staged_flag.
75 IF p_staged_flag = 'Y' THEN
76 l_ship_ready_flag := 1;
77 ELSIF (p_staged_flag = 'N' OR p_staged_flag is null) THEN
78 l_ship_ready_flag := 2;
79 END IF;
80
81 SAVEPOINT update_stage_sa;
82
83 update mtl_reservations
84 set staged_flag = p_staged_flag
85 ,ship_ready_flag = l_ship_ready_flag -- Bug #2816312
86 where reservation_id = p_reservation_id;
87
88 EXCEPTION
89 WHEN fnd_api.g_exc_error THEN
90 ROLLBACK TO update_stage_sa;
91 x_return_status := fnd_api.g_ret_sts_error ;
92
93 -- Get message count and data
94 fnd_msg_pub.count_and_get
95 ( p_count => x_msg_count
96 , p_data => x_msg_data
97 );
98 WHEN fnd_api.g_exc_unexpected_error THEN
99 ROLLBACK TO update_stage_sa;
100 x_return_status := fnd_api.g_ret_sts_unexp_error ;
101
102 -- Get message count and data
103 fnd_msg_pub.count_and_get
104 ( p_count => x_msg_count
105 , p_data => x_msg_data
106 );
107 WHEN OTHERS THEN
108 ROLLBACK TO update_stage_sa;
109 x_return_status := fnd_api.g_ret_sts_unexp_error ;
110
111 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
112 THEN
113 fnd_msg_pub.add_exc_msg
114 ( g_pkg_name
115 , l_api_name
116 );
117 END IF;
118 -- Get message count and data
119 fnd_msg_pub.count_and_get
120 ( p_count => x_msg_count
121 , p_data => x_msg_data
122 );
123
124 END update_staged_flag;
125 END inv_staged_reservation_util;