[Home] [Help]
PACKAGE BODY: APPS.AMS_STATUS_PVT
Source
1 PACKAGE BODY AMS_STATUS_PVT as
2 /* $Header: amsvstsb.pls 115.7 2002/12/05 00:57:25 dbiswas ship $ */
3
4 --
5 -- NAME
6 -- AMS_STATUS_PVT
7 --
8 -- HISTORY
9 -- 10/26/1999 holiu CREATED
10 -- 11/19/1999 ptendulk MODIFIED for User Statuses
11 --
12 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_STATUS_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvstsb.pls';
14 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
15 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
16 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
17
18 -- Debug mode
19 --g_debug boolean := FALSE;
20 --g_debug boolean := TRUE;
21
22 /*************************** PRIVATE ROUTINES *********************************/
23
24 -----------------------------------------------------------
25 -- NAME
26 -- get_status_lookup_type
27 --
28 -- USAGE
29 -- Given the arc qualifier for a certain area such as
30 -- 'PROM', RETURN the lookup type for the statuses
31 -- IN that area, such as 'AMS_CAMPAIGN_STATUS'.
32 --
33 -- RETURN NULL IF no corresponding lookup type.
34 --
35 -----------------------------------------------------------
36 FUNCTION get_status_lookup_type(p_arc_status_for IN VARCHAR2)
37 RETURN VARCHAR2 IS
38 BEGIN
39 IF p_arc_status_for = 'CAMP' THEN
40 RETURN 'AMS_CAMPAIGN_STATUS';
41 ELSIF p_arc_status_for = 'CSCH' THEN
42 RETURN 'AMS_CAMPAIGN_SCHEDULE_STATUS';
43 ELSIF p_arc_status_for = 'EVEH' THEN
44 RETURN 'AMS_EVENT_STATUS';
45 ELSIF p_arc_status_for = 'EVEO' THEN
46 RETURN 'AMS_EVENT_STATUS';
47 ELSIF p_arc_status_for = 'DELV' THEN
48 RETURN 'AMS_DELIV_STATUS';
49 ELSE
50 RETURN NULL;
51 END IF;
52 END;
53
54
55 -----------------------------------------------------------
56 -- NAME
57 -- get_lookup_meaning
58 --
59 -- USAGE
60 -- Given the lookup type AND lookup code, get the meaning.
61 -- RETURN NULL IF invalid type or code provided.
62 --
63 -----------------------------------------------------------
64 FUNCTION get_lookup_meaning(
65 p_lookup_type IN VARCHAR2,
66 p_lookup_code IN VARCHAR2
67 )
68 RETURN VARCHAR2 IS
69
70 l_meaning VARCHAR2(80);
71
72 CURSOR c_ams_lookups IS
73 SELECT meaning
74 FROM ams_lookups
75 WHERE lookup_type = p_lookup_type
76 AND lookup_code = p_lookup_code;
77
78 BEGIN
79
80 OPEN c_ams_lookups;
81 FETCH c_ams_lookups INTO l_meaning;
82 CLOSE c_ams_lookups;
83
84 RETURN l_meaning;
85
86 END;
87
88
89 --------------- start of comments --------------------------
90 -- NAME
91 -- Is_Approval_Needed
92 --
93 -- USAGE
94 -- Check IF a certain type of approval IS needed for an
95 -- object area (AND activity type).
96 --
97 --------------- END of comments ----------------------------
98
99 FUNCTION Is_Approval_Needed(
100 p_arc_approval_for IN VARCHAR2,
101 p_approval_type IN VARCHAR2,
102 p_activity_type_code IN VARCHAR2 := NULL
103 )
104 RETURN VARCHAR2 IS
105
106 l_count NUMBER;
107
108 CURSOR c_approval_rules IS
109 SELECT count(*)
110 FROM ams_approval_rules
111 WHERE arc_approval_for_object = p_arc_approval_for
112 AND approval_type = p_approval_type
113 AND (activity_type_code = p_activity_type_code
114 or activity_type_code IS NULL);
115
116 BEGIN
117
118 OPEN c_approval_rules;
119 FETCH c_approval_rules INTO l_count;
120 CLOSE c_approval_rules;
121
122 IF l_count > 0 THEN
123 RETURN fnd_api.g_true;
124 ELSE
125 RETURN fnd_api.g_false;
126 END IF;
127
128 END is_approval_needed;
129
130
131 ---------------------- start of comments --------------------------
132 -- NAME
133 -- Get_Next_Statuses
134 --
135 -- USAGE
136 -- For a certain status IN an object area, RETURN all the
137 -- valid next statuses IN a PL/SQL table.
138 --
139 -- The client side may use it to populate list items.
140 --
141 ----------------------- END of comments ----------------------------
142 PROCEDURE Get_Next_Statuses(
143 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
144 x_msg_count OUT NOCOPY NUMBER,
145 x_msg_data OUT NOCOPY VARCHAR2,
146
147 p_arc_status_for IN VARCHAR2,
148 p_current_status_id IN NUMBER,
149 p_activity_type_code IN VARCHAR2 := NULL,
150 x_next_status_tbl OUT NOCOPY next_status_tbl_type,
151 x_return_status OUT NOCOPY VARCHAR2
152 )
153 IS
154 CURSOR c_system_stat IS
155 SELECT system_status_code,system_status_type
156 FROM ams_user_statuses_vl
157 WHERE user_status_id = p_current_status_id ;
158
159 CURSOR c_next_statuses(l_stat_code VARCHAR2,l_stat_type VARCHAR2) IS
160 SELECT next_status_code
161 FROM ams_status_order_rules
162 WHERE current_status_code = l_stat_code
163 AND system_status_type = l_stat_type
164 AND show_in_lov_flag = 'Y' ;
165
166 CURSOR c_user_statuses(l_stat_code VARCHAR2,l_stat_type VARCHAR2) IS
167 SELECT user_status_id,system_status_type,system_status_code,
168 default_flag,name
169 FROM ams_user_statuses_vl
170 WHERE system_status_code = l_stat_code
171 AND system_status_type = l_stat_type
172 AND start_date_active < SYSDATE
173 AND (end_date_active IS NULL OR
174 end_date_active > SYSDATE) ;
175
176 TYPE temp_status_tbl_type IS TABLE OF c_next_statuses%ROWTYPE
177 INDEX BY BINARY_INTEGER;
178
179 l_next_status_tbl temp_status_tbl_type ;
180 l_temp_status_rec c_next_statuses%ROWTYPE;
181 l_system_stat_code VARCHAR2(30);
182 l_system_stat_type VARCHAR2(30);
183 l_user_stat_rec next_status_rec_type;
184
185 l_index BINARY_INTEGER;
186 l_total BINARY_INTEGER;
187
188 l_approval_flag VARCHAR2(1); -- fnd_api.g_true, fnd_api.g_false
189 l_lookup_type VARCHAR(30);
190 l_api_name CONSTANT VARCHAR2(30) := 'get_next_statuses';
191
192 BEGIN
193
194 ----------------------- initialize -----------------------------
195 x_return_status := fnd_api.g_ret_sts_success;
196
197 l_lookup_type := get_status_lookup_type(p_arc_status_for);
198 IF l_lookup_type IS NULL THEN
199 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
200 fnd_message.set_name('AMS', 'INVALID_ARC_QUALIFIER');
201 fnd_message.set_token('ARG', p_arc_status_for, FALSE);
202 fnd_msg_pub.add;
203 END IF;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 --------------------- Get System Status Code --------------------
205 RETURN ;
206 END IF;
207
209 OPEN c_system_stat ;
210 FETCH c_system_stat INTO l_system_stat_code,l_system_stat_type ;
211 CLOSE c_system_stat ;
212
213 --------------------- find next system statuses -----------------
214 l_total := 0;
215
216 OPEN c_next_statuses(l_system_stat_code,l_system_stat_type);
217 LOOP
218 FETCH c_next_statuses INTO l_temp_status_rec ;
219 EXIT WHEN c_next_statuses%NOTFOUND ;
220 l_next_status_tbl(l_total) := l_temp_status_rec ;
221 l_total := l_total + 1 ;
222 END LOOP;
223 CLOSE c_next_statuses ;
224
225
226 -- OPEN c_next_statuses(l_system_stat_code,l_system_stat_type);
227 -- LOOP
228 -- FETCH c_next_statuses INTO l_temp_status_rec ;
229 -- EXIT WHEN c_next_statuses%NOTFOUND;
230 -- l_total := l_total + 1;
231 -- l_temp_status_tbl(l_total) := l_temp_status_rec;
232 -- IF l_temp_status_rec.approval_type IS NOT NULL THEN
233 -- l_index := l_total;
234 -- END IF;
235 -- END LOOP;
236 --
237 -- IF l_total = 0 THEN -- no valid next status, e.g. 'ARCHIVED'
238 -- RETURN;
239 -- END IF;
240 --
241 -- IF l_index = 0 THEN -- no approval needed
242 -- l_approval_flag := fnd_api.g_false;
243 -- ELSE
244 -- l_approval_flag := Is_Approval_Needed(
245 -- p_arc_status_for,
246 -- l_temp_status_tbl(l_index).approval_type,
247 -- p_activity_type_code
248 -- );
249 -- END IF;
250
251 -------------- Find Next System Statuses based on Approval Results -----------
252
253 -- l_index := 0; -- the index for l_next_status_tbl
254 -- FOR i IN 1..l_total LOOP
255 -- IF (l_temp_status_tbl(i).no_approval_flag = 'Y'
256 -- AND l_approval_flag = fnd_api.g_false)
257 -- or (l_temp_status_tbl(i).approval_flag = 'Y'
258 -- AND l_approval_flag = fnd_api.g_true)
259 -- THEN
260 -- l_index := l_index + 1;
261 -- l_next_status_tbl(l_index) := l_temp_status_tbl(i);
262 -- END IF;
263 --- END LOOP;
264 --
265 ------------------------- find User Statuses ---------------------------------
266 l_index := 0; -- the index for x_next_status_tbl
267 FOR i IN 1..l_next_status_tbl.last
268 LOOP
269 OPEN c_user_statuses(l_next_status_tbl(i).next_status_code,l_system_stat_type) ;
270 LOOP
271 FETCH c_user_statuses INTO l_user_stat_rec;
272 EXIT WHEN c_user_statuses%NOTFOUND ;
273 l_index := l_index + 1;
274 x_next_status_tbl(l_index) := l_user_stat_rec ;
275 END LOOP;
276 CLOSE c_user_statuses ;
277 END LOOP;
278
279
280 END get_next_statuses;
281
282
283 --------------- start of comments --------------------------
284 -- NAME
285 -- validate_status_change
286 --
287 -- USAGE
288 -- Check whether a status change IS valid or not.
289 -- Server side API may use it for validate status changes.
290 --
291 --------------- END of comments ----------------------------
292
293 PROCEDURE validate_status_change(
294 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
295 x_msg_count OUT NOCOPY NUMBER,
296 x_msg_data OUT NOCOPY VARCHAR2,
297
298 p_arc_status_for IN VARCHAR2,
299 p_current_status_id IN VARCHAR2,
300 p_next_status_id IN VARCHAR2,
301 p_activity_type_code IN VARCHAR2 := NULL,
302 x_valid_flag OUT NOCOPY VARCHAR2, -- fnd_api.g_true, fnd_api.g_false
303 x_return_status OUT NOCOPY VARCHAR2
304 )
305 IS
306
307 l_temp_status_tbl next_status_tbl_type;
308
309 l_index binary_integer;
310 l_total binary_integer;
311
312 l_lookup_type ams_lookups.lookup_type%type;
313 l_api_name constant VARCHAR2(30) := 'validate_status_change';
314
315 BEGIN
316
317 x_return_status := fnd_api.g_ret_sts_success;
318 x_valid_flag := fnd_api.g_false;
319
320 l_lookup_type := get_status_lookup_type(p_arc_status_for);
321 IF l_lookup_type IS NULL THEN
322 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
323 fnd_message.set_name('AMS', 'INVALID_ARC_QUALIFIER');
324 fnd_message.set_token('ARG', p_arc_status_for, FALSE);
325 fnd_msg_pub.add;
326 END IF;
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
328 RETURN ;
329 END IF;
330
331 -- Get the Valid Next Statuses
332 get_next_statuses(
333 p_init_msg_list => p_init_msg_list,
334 x_msg_count => x_msg_count,
335 x_msg_data => x_msg_data,
336 p_arc_status_for => p_arc_status_for,
337 p_current_status_id => p_current_status_id,
338 p_activity_type_code => p_activity_type_code,
339 x_next_status_tbl => l_temp_status_tbl,
340 x_return_status => x_return_status
341 );
342
343 IF x_return_status <> FND_API.g_ret_sts_success THEN
344 RETURN;
345 END IF;
346
347 FOR i IN 1..l_temp_status_tbl.last LOOP
348 IF (l_temp_status_tbl(i).user_status_id = p_next_status_id)
349 THEN
350 x_valid_flag := fnd_api.g_true;
351 RETURN;
352 END IF;
353 END LOOP;
354
355 END validate_status_change;
356
357 END AMS_STATUS_PVT;