[Home] [Help]
PACKAGE BODY: APPS.AHL_WF_MAPPING_PVT
Source
1 PACKAGE BODY AHL_WF_MAPPING_PVT AS
2 /* $Header: AHLVWFMB.pls 120.4 2006/08/23 09:40:48 sathapli noship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AHL_Wf_Mapping_PVT
7 --
8 -- PROCEDURES
9 -- AHL_Wf_Mapping:
10 -- Check_Wf_Mapping_Req_Items
11 -- Check_Wf_Mapping_UK_Items
12 -- Check_Process_Name
13 -- Check_Default
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 -- 20-Jan-2002 shbhanda Created.
19 -----------------------------------------------------------
20 --
21 -- Global CONSTANTS
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_Wf_Mapping_PVT';
23 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
24 G_VALID_NAME CONSTANT NUMBER:= 50;
25
26 -- Check_Wf_Mapping_Req_Items
27 PROCEDURE Check_Wf_Mapping_Req_Items (
28 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
29 x_return_status OUT NOCOPY VARCHAR2
30 );
31
32 PROCEDURE Check_Appl_Usg_Code(
33 p_wf_mapping_rec IN Wf_Mapping_Rec_Type,
34 x_return_status OUT NOCOPY VARCHAR2
35 );
36
37 -- Check_Wf_Mapping_UK_Items
38 PROCEDURE Check_Wf_Mapping_UK_Items (
39 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
40 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
41 x_return_status OUT NOCOPY VARCHAR2
42 );
43
44 -- Check_Process_Name when the value is not selected from LOV's
45 PROCEDURE Check_Process_Name(
46 p_wf_mapping_rec IN wf_mapping_Rec_Type,
47 x_item_type OUT NOCOPY VARCHAR2,
48 x_process_name OUT NOCOPY VARCHAR2,
49 x_return_status OUT NOCOPY VARCHAR2
50 );
51
52 -- Check_Default there can be only one
53 PROCEDURE Check_Default (
54 p_wf_mapping_rec IN wf_mapping_Rec_Type,
55 p_complete_rec IN wf_mapping_Rec_Type := NULL,
56 x_return_status OUT NOCOPY VARCHAR2
57 );
58
59 --------------------------------------------------------------------
60 ----- Wf_Mapping -----
61 --------------------------------------------------------------------
62
63 --------------------------------------------------------------------
64 -- PROCEDURE
65 -- Process_Wf_Mapping
66 --
67 --------------------------------------------------------------------
68 PROCEDURE Process_Wf_Mapping (
69 p_api_version IN NUMBER := 1.0,
70 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
71 p_commit IN VARCHAR2 := Fnd_Api.g_false,
72 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
73 p_x_Wf_Mapping_tbl IN OUT NOCOPY Wf_Mapping_tbl,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2
77 )
78 IS
79 L_API_VERSION CONSTANT NUMBER := 1.0;
80 L_API_NAME CONSTANT VARCHAR2(30) := 'Process_Wf_Mapping';
81 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
82 l_x_Wf_Mapping_tbl Wf_Mapping_tbl := p_x_Wf_Mapping_tbl;
83 l_dummy NUMBER;
84 l_return_status VARCHAR2(1);
85 p_object_version VARCHAR2(1) := 1;
86 x_WF_Mapping_ID NUMBER;
87
88 BEGIN
89 --------------------- initialize -----------------------
90 SAVEPOINT Process_Wf_Mapping;
91 -- Check if API is called in debug mode. If yes, enable debug.
92 IF G_DEBUG='Y' THEN
93 Ahl_Debug_Pub.enable_debug;
94 END IF;
95 -- Debug info.
96 IF G_DEBUG='Y' THEN
97 Ahl_Debug_Pub.debug( l_full_name ||':Start');
98 END IF;
99
100 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
101 Fnd_Msg_Pub.initialize;
102 END IF;
103 IF NOT Fnd_Api.compatible_api_call (
104 L_API_VERSION,
105 p_api_version,
106 L_API_NAME,
107 G_PKG_NAME
108 ) THEN
109 RAISE Fnd_Api.g_exc_unexpected_error;
110 END IF;
111 x_return_status := Fnd_Api.g_ret_sts_success;
112
113 ----------------------- validate -----------------------
114 IF G_DEBUG='Y' THEN
115 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
116 END IF;
117
118 IF (l_x_Wf_Mapping_tbl.COUNT > 0) THEN
119 FOR i IN l_x_Wf_Mapping_tbl.FIRST..l_x_Wf_Mapping_tbl.LAST LOOP
120 IF (l_x_Wf_Mapping_tbl(i).operation_flag = 'C' or l_x_Wf_Mapping_tbl(i).operation_flag = 'c') THEN
121 -- For creation of AHL_WF_MAPPING
122 Create_Wf_Mapping (
123 p_api_version,
124 p_init_msg_list,
125 p_commit,
126 p_validation_level,
127 l_x_Wf_Mapping_tbl(i),
128 x_return_status ,
129 x_msg_count,
130 x_msg_data,
131 x_WF_Mapping_ID);
132
133 p_x_Wf_Mapping_tbl(i).Wf_Mapping_Id := x_WF_Mapping_ID;
134
135 END IF;
136 IF (p_x_Wf_Mapping_tbl(i).operation_flag = 'U' or l_x_Wf_Mapping_tbl(i).operation_flag = 'u') THEN
137 -- For updation of AHL_WF_MAPPING
138 Update_Wf_Mapping (
139 p_api_version ,
140 p_init_msg_list,
141 p_commit,
142 p_validation_level,
143 l_x_Wf_Mapping_tbl(i),
144 x_return_status,
145 x_msg_count,
146 x_msg_data);
147 END IF;
148 IF (p_x_Wf_Mapping_tbl(i).operation_flag = 'D' or l_x_Wf_Mapping_tbl(i).operation_flag = 'd') THEN
149 -- For deletion of AHL_WF_MAPPING
150 Delete_wf_mapping (
151 p_api_version,
152 p_init_msg_list,
153 p_commit,
154 p_validation_level,
155 x_return_status,
156 x_msg_count,
157 x_msg_data,
158 p_x_Wf_Mapping_tbl(i).Wf_Mapping_Id,
159 p_object_version);
160 END IF;
161 END LOOP;
162 END IF;
163
164 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
165 RAISE Fnd_Api.g_exc_error;
166 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
167 RAISE Fnd_Api.g_exc_unexpected_error;
168 END IF;
169
170 --
171 -- END of API body.
172 --
173 -- Standard check of p_commit.
174 IF Fnd_Api.To_Boolean ( p_commit ) THEN
175 COMMIT WORK;
176 END IF;
177 Fnd_Msg_Pub.count_and_get(
178 p_encoded => Fnd_Api.g_false,
179 p_count => x_msg_count,
180 p_data => x_msg_data
181 );
182 IF G_DEBUG='Y' THEN
183 Ahl_Debug_Pub.debug( l_full_name ||':End');
184 END IF;
185 -- Check if API is called in debug mode. If yes, disable debug.
186 IF G_DEBUG='Y' THEN
187 Ahl_Debug_Pub.disable_debug;
188 END IF;
189
190 EXCEPTION
191 WHEN Fnd_Api.g_exc_error THEN
192 ROLLBACK TO Process_Wf_Mapping;
193 x_return_status := Fnd_Api.g_ret_sts_error;
194 Fnd_Msg_Pub.count_and_get(
195 p_encoded => Fnd_Api.g_false,
196 p_count => x_msg_count,
197 p_data => x_msg_data
198 );
199 WHEN Fnd_Api.g_exc_unexpected_error THEN
200 ROLLBACK TO Process_Wf_Mapping;
201 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
202 Fnd_Msg_Pub.count_and_get (
203 p_encoded => Fnd_Api.g_false,
204 p_count => x_msg_count,
205 p_data => x_msg_data
206 );
207 WHEN OTHERS THEN
208 ROLLBACK TO Process_Wf_Mapping;
209 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
210 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
211 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
212 END IF;
213 Fnd_Msg_Pub.count_and_get (
214 p_encoded => Fnd_Api.g_false,
215 p_count => x_msg_count,
216 p_data => x_msg_data
217 );
218
219 END Process_Wf_Mapping;
220
221
222 --------------------------------------------------------------------
223 -- PROCEDURE
224 -- Create_Wf_Mapping
225 --
226 --------------------------------------------------------------------
227
228 PROCEDURE Create_Wf_Mapping (
229 p_api_version IN NUMBER := 1.0,
230 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
231 p_commit IN VARCHAR2 := Fnd_Api.g_false,
232 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
233 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
234
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_count OUT NOCOPY NUMBER,
237 x_msg_data OUT NOCOPY VARCHAR2,
238 x_Wf_Mapping_ID OUT NOCOPY NUMBER
239 )
240 IS
241 L_API_VERSION CONSTANT NUMBER := 1.0;
242 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Wf_Mapping';
243 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
244 l_Wf_Mapping_rec Wf_Mapping_Rec_Type := p_Wf_Mapping_rec;
245 l_dummy NUMBER;
246 l_return_status VARCHAR2(1);
247 l_item_type VARCHAR2(8);
248 l_rowid VARCHAR2(30);
249 l_object_version_number NUMBER := 1;
250 l_seed varchar(1) := 'N';
251 l_process_name varchar2(30);
252
253 CURSOR c_seq IS
254 SELECT Ahl_Wf_Mapping_S.NEXTVAL
255 FROM dual;
256
257 CURSOR c_id_exists (x_id IN NUMBER) IS
258 SELECT 1
259 FROM dual
260 WHERE EXISTS (SELECT 1
261 FROM AHL_Wf_Mapping
262 WHERE WF_Mapping_ID = x_id);
263
264 -- To retrieve item type for the entered process name not selected from LOV's
265 /*cursor c_item_type Is
266 select ITEM_TYPE
267 from WF_RUNNABLE_PROCESSES_V
268 where PROCESS_NAME = p_wf_mapping_rec.WF_PROCESS_NAME;
269 */
270
271 CURSOR c_appl_usg IS
272 SELECT LOOKUP_CODE
273 FROM FND_LOOKUPS
274 WHERE MEANING = l_Wf_Mapping_rec.APPLICATION_USG
275 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';
276
277
278 BEGIN
279 --------------------- initialize -----------------------
280 SAVEPOINT Create_Wf_Mapping;
281 -- Check if API is called in debug mode. If yes, enable debug.
282 IF G_DEBUG='Y' THEN
283 Ahl_Debug_Pub.enable_debug;
284 END IF;
285 -- Debug info.
286 IF G_DEBUG='Y' THEN
287 Ahl_Debug_Pub.debug( l_full_name ||':Start');
288 END IF;
289
290 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
291 Fnd_Msg_Pub.initialize;
292 END IF;
293 IF NOT Fnd_Api.compatible_api_call (
294 L_API_VERSION,
295 p_api_version,
296 L_API_NAME,
297 G_PKG_NAME
298 ) THEN
299 RAISE Fnd_Api.g_exc_unexpected_error;
300 END IF;
301 x_return_status := Fnd_Api.g_ret_sts_success;
302 ----------------------- validate -----------------------
303 IF G_DEBUG='Y' THEN
304 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
305 END IF;
306
307 -- Check for default approval obeject in default workflow process
308 --
309 IF l_Wf_Mapping_rec.Approval_Object IS NULL THEN
310 Check_Default (
311 p_wf_mapping_rec => p_wf_mapping_rec,
312 p_complete_rec => l_wf_mapping_rec,
313 x_return_status => l_return_status
314 );
315 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
316 RAISE Fnd_Api.g_exc_unexpected_error;
317 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
318 RAISE Fnd_Api.g_exc_error;
319 END IF;
320 END IF;
321
322 Validate_Wf_Mapping (
323 p_api_version => l_api_version,
324 p_init_msg_list => p_init_msg_list,
325 p_commit => p_commit,
326 p_validation_level => p_validation_level,
327 x_return_status => l_return_status,
328 x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data,
330 p_Wf_Mapping_rec => l_Wf_Mapping_rec
331 );
332
333 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
334 RAISE Fnd_Api.g_exc_error;
335 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
336 RAISE Fnd_Api.g_exc_unexpected_error;
337 END IF;
338
339 --
340 -- Check for the ID.
341 --
342 IF l_Wf_Mapping_rec.WF_Mapping_ID = fnd_api.g_miss_num OR l_Wf_Mapping_rec.WF_Mapping_ID is null THEN
343 --IF l_Wf_Mapping_rec.WF_Mapping_ID is null THEN
344 LOOP
345 --
346 -- If the ID is not passed into the API, then grab a value from the sequence.
347 OPEN c_seq;
348 FETCH c_seq INTO l_Wf_Mapping_rec.WF_Mapping_ID;
349 CLOSE c_seq;
350 --
351 -- Check to be sure that the sequence does not exist.
352 OPEN c_id_exists (l_Wf_Mapping_rec.WF_Mapping_ID);
353 FETCH c_id_exists INTO l_dummy;
354 CLOSE c_id_exists;
355 --
356 -- If the value for the ID already exists, then l_dummy would be populated
357 -- with '1', otherwise, it receives NULL.
358 EXIT WHEN l_dummy IS NULL;
359 END LOOP;
360 END IF;
361
362 -- Check whether procees name entered is valid not selected from LOV's
363 --
364 IF l_Wf_Mapping_rec.Wf_display_Name IS NOT NULL THEN --and l_Wf_Mapping_rec.Item_type IS NULL THEN
365 Check_Process_Name (
366 p_wf_mapping_rec => p_wf_mapping_rec,
367 x_item_type => l_item_type,
368 x_process_name => l_process_name,
369 x_return_status => l_return_status
370 );
371 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
372 RAISE Fnd_Api.g_exc_unexpected_error;
373 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
374 RAISE Fnd_Api.g_exc_error;
375 ELSE
376 --if process name is valid then grab value of item type from c_item_type cursor
377 --
378 /*OPEN c_item_type;
379 FETCH c_item_type INTO l_Wf_Mapping_rec.Item_type;
380 CLOSE c_item_type;*/
381 l_Wf_Mapping_rec.Item_type := l_item_type;
382 l_wf_mapping_rec.wf_process_name := UPPER(l_process_name);
383 END IF;
384 END IF;
385
386 /*IF p_validation_level >= G_VALID_NAME THEN
387 Check_Appl_Usg_Code (p_wf_mapping_rec => p_wf_mapping_rec,
388 x_return_status => l_return_status
389 );
390
391 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
392 RAISE Fnd_Api.g_exc_unexpected_error;
393 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
394 RAISE Fnd_Api.g_exc_error;
395 ELSE
396 OPEN c_appl_usg;
397 FETCH c_appl_usg INTO l_Wf_Mapping_rec.Application_Usg_Code;
398 IF c_appl_usg%NOTFOUND THEN
399 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
400 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
401 Fnd_Msg_Pub.ADD;
402 END IF;
403 RAISE Fnd_Api.g_exc_unexpected_error;
404 END IF;
405 END IF;
406 END IF;
407 */
408 -------------------------- insert --------------------------
409 IF G_DEBUG='Y' THEN
410 Ahl_Debug_Pub.debug( l_full_name ||':Insert');
411 END IF;
412
413 -- Invoke the table handler to create a record
414 --
418 X_WF_Mapping_ID => l_Wf_Mapping_rec.WF_Mapping_ID,
415 -- l_process_name := UPPER(l_Wf_Mapping_rec.WF_PROCESS_NAME) ;
416 Ahl_Wf_Mapping_Pkg.insert_row (
417 X_ROWID => l_rowid,
419 X_OBJECT_VERSION_NUMBER => 1,
420 X_CREATION_DATE => SYSDATE,
421 X_CREATED_BY => Fnd_Global.USER_ID,
422 X_LAST_UPDATE_DATE => SYSDATE,
423 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
424 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
425 X_ACTIVE_FLAG => l_Wf_Mapping_rec.ACTIVE_FLAG,
426 X_WF_PROCESS_NAME => l_wf_mapping_rec.wf_process_name,
427 X_APPROVAL_OBJECT => l_Wf_Mapping_rec.APPROVAL_OBJECT,
428 X_ITEM_TYPE => l_Wf_Mapping_rec.ITEM_TYPE,
429 X_APPLICATION_USG_CODE => l_Wf_Mapping_rec.APPLICATION_USG_CODE
430 );
431
432 ------------------------- finish -------------------------------
433
434 -- set OUT value
435 x_WF_Mapping_ID := l_Wf_Mapping_rec.WF_Mapping_ID;
436 --
437 -- END of API body.
438 --
439 -- Standard check of p_commit.
440 IF Fnd_Api.To_Boolean ( p_commit ) THEN
441 COMMIT WORK;
442 END IF;
443 Fnd_Msg_Pub.count_and_get(
444 p_encoded => Fnd_Api.g_false,
445 p_count => x_msg_count,
446 p_data => x_msg_data
447 );
448 IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
449 Ahl_Debug_Pub.debug( l_full_name ||':End');
450 END IF;
451 -- Check if API is called in debug mode. If yes, disable debug.
452 Ahl_Debug_Pub.disable_debug;
453
454 EXCEPTION
455 WHEN Fnd_Api.g_exc_error THEN
456 ROLLBACK TO Create_Wf_Mapping;
457 x_return_status := Fnd_Api.g_ret_sts_error;
458 Fnd_Msg_Pub.count_and_get(
459 p_encoded => Fnd_Api.g_false,
460 p_count => x_msg_count,
461 p_data => x_msg_data
462 );
463 WHEN Fnd_Api.g_exc_unexpected_error THEN
464 ROLLBACK TO Create_Wf_Mapping;
465 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
466 Fnd_Msg_Pub.count_and_get (
467 p_encoded => Fnd_Api.g_false,
468 p_count => x_msg_count,
469 p_data => x_msg_data
470 );
471 WHEN OTHERS THEN
472 ROLLBACK TO Create_Wf_Mapping;
473 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
474 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
475 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
476 END IF;
477 Fnd_Msg_Pub.count_and_get (
478 p_encoded => Fnd_Api.g_false,
479 p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482 END Create_Wf_Mapping;
483
484 --------------------------------------------------------------------
485 -- PROCEDURE
486 -- Update_Wf_Mapping
487 --
488 --------------------------------------------------------------------
489 PROCEDURE Update_Wf_Mapping (
490 p_api_version IN NUMBER := 1.0,
491 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
492 p_commit IN VARCHAR2 := Fnd_Api.g_false,
493 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
494 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
495
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_count OUT NOCOPY NUMBER,
498 x_msg_data OUT NOCOPY VARCHAR2
499 )
500 IS
501 L_API_VERSION CONSTANT NUMBER := 1.0;
502 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Wf_Mapping';
503 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
504
505 l_Wf_Mapping_rec Wf_Mapping_Rec_Type := p_Wf_Mapping_rec;
506 l_dummy NUMBER;
507 l_return_status VARCHAR2(1);
508 l_process_name VARCHAR2(30);
509 l_item_type VARCHAR2(8);
510
511 -- To retrieve item type for the entered process name not selected from LOV's
512 cursor c_item_type Is
513 select ITEM_TYPE
514 from WF_RUNNABLE_PROCESSES_V
515 where PROCESS_NAME = p_wf_mapping_rec.WF_PROCESS_NAME;
516
517 CURSOR c_appl_usg IS
518 SELECT LOOKUP_CODE
519 FROM FND_LOOKUPS
520 WHERE MEANING = l_Wf_Mapping_rec.APPLICATION_USG
521 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';
522
523
524 BEGIN
525 --------------------- initialize -----------------------
526 SAVEPOINT Update_Wf_Mapping;
527 -- Check if API is called in debug mode. If yes, enable debug.
528 IF G_DEBUG='Y' THEN
529 Ahl_Debug_Pub.enable_debug;
530 END IF;
531 -- Debug info.
532 IF G_DEBUG='Y' THEN
533 Ahl_Debug_Pub.debug( l_full_name ||':Start');
534 END IF;
535 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
536 Fnd_Msg_Pub.initialize;
537 END IF;
538 IF NOT Fnd_Api.compatible_api_call(
539 l_api_version,
540 p_api_version,
541 l_api_name,
542 g_pkg_name
543 ) THEN
544 RAISE Fnd_Api.g_exc_unexpected_error;
545 END IF;
546 x_return_status := Fnd_Api.g_ret_sts_success;
547 ----------------------- validate ----------------------
551
548 IF G_DEBUG='Y' THEN
549 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
550 END IF;
552 --Check for default approval obeject in default workflow process
553 --
554 IF l_Wf_Mapping_rec.Approval_Object IS NULL THEN
555 Check_Default (
556 p_wf_mapping_rec => p_wf_mapping_rec,
557 p_complete_rec => l_wf_mapping_rec,
558 x_return_status => l_return_status
559 );
560 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
561 RAISE Fnd_Api.g_exc_unexpected_error;
562 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
563 RAISE Fnd_Api.g_exc_error;
564 END IF;
565 END IF;
566
567 -- replace g_miss_char/num/date with current column values
568 Complete_Wf_Mapping_Rec (p_Wf_Mapping_rec, l_Wf_Mapping_rec);
569
570 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
571 Check_Wf_Mapping_Items (
572 p_Wf_Mapping_rec => p_Wf_Mapping_rec,
573 p_validation_mode => Jtf_Plsql_Api.g_update,
574 x_return_status => l_return_status
575
576 );
577 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
578 RAISE Fnd_Api.g_exc_unexpected_error;
579 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
580 RAISE Fnd_Api.g_exc_error;
581 END IF;
582 END IF;
583
584 -- Check whether procees name entered is valid not selected from LOV's
585 --
586 IF l_Wf_Mapping_rec.Wf_display_Name IS NOT NULL THEN --and l_Wf_Mapping_rec.Item_type IS NULL THEN
587 Check_Process_Name (
588 p_wf_mapping_rec => p_wf_mapping_rec,
589 x_item_type => l_item_type,
590 x_process_name => l_process_name,
591 x_return_status => l_return_status
592 );
593 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
594 RAISE Fnd_Api.g_exc_unexpected_error;
595 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
596 RAISE Fnd_Api.g_exc_error;
597 ELSE
598 --if process name is valid then grab value of item type from c_item_type cursor
599 --
600 /* OPEN c_item_type;
601 FETCH c_item_type INTO l_Wf_Mapping_rec.Item_type;
602 CLOSE c_item_type;
603 */
604 l_Wf_Mapping_rec.Item_type := l_item_type;
605 l_Wf_Mapping_rec.wf_process_name := UPPER(l_process_name);
606 END IF;
607 END IF;
608 /*IF p_validation_level >= G_VALID_NAME THEN
609 Check_Appl_Usg_Code (
610 p_wf_mapping_rec => p_wf_mapping_rec,
611 x_return_status => l_return_status
612 );
613 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
614 RAISE Fnd_Api.g_exc_unexpected_error;
615 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
616 RAISE Fnd_Api.g_exc_error;
617 ELSE
618 OPEN c_appl_usg;
619 FETCH c_appl_usg INTO l_Wf_Mapping_rec.Application_Usg_Code;
620 IF c_appl_usg%NOTFOUND THEN
621 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
622 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
623 Fnd_Msg_Pub.ADD;
624 END IF;
625 RAISE Fnd_Api.g_exc_unexpected_error;
626 END IF;
627 END IF;
628 END IF;
629 */
630 -------------------------- update --------------------
631 IF G_DEBUG='Y' THEN
632 Ahl_Debug_Pub.debug( l_full_name ||':Update');
633 END IF;
634
635 --l_process_name := UPPER(l_Wf_Mapping_rec.WF_PROCESS_NAME);
636 Ahl_Wf_Mapping_Pkg. UPDATE_ROW (
637 X_WF_Mapping_ID => l_Wf_Mapping_rec.WF_Mapping_ID,
638 X_OBJECT_VERSION_NUMBER => l_Wf_Mapping_rec.OBJECT_VERSION_NUMBER + 1,
639 X_LAST_UPDATE_DATE => SYSDATE,
640 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
641 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
642 X_ACTIVE_FLAG => l_Wf_Mapping_rec.ACTIVE_FLAG,
643 X_WF_PROCESS_NAME => l_process_name,
644 X_APPROVAL_OBJECT => l_Wf_Mapping_rec.APPROVAL_OBJECT,
645 X_ITEM_TYPE => l_Wf_Mapping_rec.item_type,
646 X_APPLICATION_USG_CODE => l_Wf_Mapping_rec.APPLICATION_USG_CODE
647 );
648
649 -------------------- finish --------------------------
650 IF Fnd_Api.to_boolean (p_commit) THEN
651 COMMIT;
652 END IF;
653 Fnd_Msg_Pub.count_and_get (
654 p_encoded => Fnd_Api.g_false,
655 p_count => x_msg_count,
656 p_data => x_msg_data
657 );
658 IF G_DEBUG='Y' THEN
659 Ahl_Debug_Pub.debug( l_full_name ||':End');
660 END IF;
661 -- Check if API is called in debug mode. If yes, disable debug.
662 IF G_DEBUG='Y' THEN
663 Ahl_Debug_Pub.disable_debug;
664 END IF;
665 EXCEPTION
666 WHEN Fnd_Api.g_exc_error THEN
667 ROLLBACK TO Update_Wf_Mapping;
668 x_return_status := Fnd_Api.g_ret_sts_error;
669 Fnd_Msg_Pub.count_and_get (
670 p_encoded => Fnd_Api.g_false,
671 p_count => x_msg_count,
672 p_data => x_msg_data
673 );
674 WHEN Fnd_Api.g_exc_unexpected_error THEN
675 ROLLBACK TO Update_Wf_Mapping;
679 p_count => x_msg_count,
676 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
677 Fnd_Msg_Pub.count_and_get (
678 p_encoded => Fnd_Api.g_false,
680 p_data => x_msg_data
681 );
682 WHEN OTHERS THEN
683 ROLLBACK TO Update_Wf_Mapping;
684 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
685 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
686 THEN
687 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
688 END IF;
689 Fnd_Msg_Pub.count_and_get (
690 p_encoded => Fnd_Api.g_false,
691 p_count => x_msg_count,
692 p_data => x_msg_data
693 );
694 END Update_Wf_Mapping;
695
696 --------------------------------------------------------------------
697 -- PROCEDURE
698 -- Check_Process_Name
699 -- Check process name if present in WF_RUNNABLE_PROCESSES_V view or
700 -- raise an error message
701 --------------------------------------------------------------------
702
703 PROCEDURE Check_Process_Name(
704 p_wf_mapping_rec IN wf_mapping_Rec_Type,
705 x_item_type OUT NOCOPY VARCHAR2,
706 x_process_name OUT NOCOPY VARCHAR2,
707 x_return_status OUT NOCOPY VARCHAR2
708 )
709 IS
710 l_count number;
711 cursor cur_process_name Is
712 -- SATHAPLI::Bug# 4919061 fix::SQL Id 14401778
713 /*
714 select item_type, process_name from WF_RUNNABLE_PROCESSES_V
715 where UPPER(display_name) = UPPER(p_wf_mapping_rec.wf_display_name);
716 */
717 SELECT /* PARALLEL (B) +*/
718 B.ITEM_TYPE item_type,
719 B.NAME process_name
720 FROM WF_ACTIVITIES B,
721 WF_ACTIVITIES_TL T
722 WHERE B.ITEM_TYPE = T.ITEM_TYPE
723 AND B.NAME = T.NAME
724 AND B.VERSION = T.VERSION
725 AND T.LANGUAGE = USERENV('LANG')
726 AND B.RUNNABLE_FLAG = 'Y'
727 AND B.TYPE = 'PROCESS'
728 AND SYSDATE BETWEEN B.BEGIN_DATE AND NVL(B.END_DATE, SYSDATE)
729 -- SATHAPLI::Bug# 5359954 fix
730 /*
731 AND UPPER(T.DISPLAY_NAME) = UPPER(p_wf_mapping_rec.wf_display_name);
732 */
733 AND UPPER(T.NAME) = UPPER(p_wf_mapping_rec.wf_process_name);
734
735 cursor cur_process_name_type is
736 select item_type, process_name from wf_runnable_processes_v
737 -- SATHAPLI::Bug# 5359954 fix
738 /*
739 where UPPER(display_name) = UPPER(p_wf_mapping_rec.wf_display_name)
740 */
741 where item_type = p_wf_mapping_rec.item_type
742 and UPPER(process_name) = UPPER( p_wf_mapping_rec.wf_process_name) ;
743
744 cur_process_name_rec cur_process_name%rowtype;
745
746 BEGIN
747 IF G_DEBUG='Y' THEN
748 Ahl_Debug_Pub.debug('inside check_process_name LANG = '||USERENV('LANG')||
749 ' p_wf_mapping_rec.wf_display_name = '||p_wf_mapping_rec.wf_display_name||
750 ' p_wf_mapping_rec.item_type = '||p_wf_mapping_rec.item_type||
751 ' p_wf_mapping_rec.wf_process_name = '||p_wf_mapping_rec.wf_process_name);
752 END IF;
753
754 OPEN cur_process_name_type;
755 FETCH cur_process_name_type into cur_process_name_rec;
756 IF cur_process_name_type%NOTFOUND THEN
757 Open cur_process_name;
758 LOOP
759 EXIT WHEN cur_process_name%NOTFOUND;
760 Fetch cur_process_name into cur_process_name_rec;
761 END LOOP;
762 IF cur_process_name%ROWCOUNT = 0 THEN
763 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
764 Fnd_Message.set_name ('AHL', 'AHL_WF_NOT_PROCESS');
765 Fnd_Msg_Pub.ADD;
766 END IF;
767 x_return_status := Fnd_Api.g_ret_sts_error;
768 RETURN;
769 ELSIF cur_process_name%ROWCOUNT > 1 THEN
770 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
771 Fnd_Message.set_name ('AHL', 'AHL_WF_PROCESS_USELOV');
772 Fnd_Msg_Pub.ADD;
773 END IF;
774 x_return_status := Fnd_Api.g_ret_sts_error;
775 RETURN;
776 END IF;
777 close cur_process_name;
778 END IF;
779 close cur_process_name_type;
780 x_item_type := cur_process_name_rec.item_type;
781 x_process_name := cur_process_name_rec.process_name;
782
783 END Check_Process_Name;
784
785 --------------------------------------------------------------------
786 -- PROCEDURE
787 -- Check_Default
788 -- Check is Default workflow process is more than one, when approval object is null
789 --
790 --------------------------------------------------------------------
791
792 PROCEDURE Check_Default(
793 p_wf_mapping_rec IN wf_mapping_Rec_Type,
794 p_complete_rec IN wf_mapping_Rec_Type := NULL,
795 x_return_status OUT NOCOPY VARCHAR2
796 )
797 IS
798 l_count number;
799 l_wf_id number;
800 l_application_usg_code VARCHAR2(30);
801 cursor check_object(c_appl_usg_code IN VARCHAR2) Is
802 select 1 from AHL_WF_MAPPING
803 where APPROVAL_OBJECT is NULL
804 AND APPLICATION_USG_CODE = c_appl_usg_code;
805
806 CURSOR Cur_object (c_appl_usg_code IN VARCHAR2) IS
807 select wf_mapping_id from AHL_WF_MAPPING where APPROVAL_OBJECT is NULL
811 select lookup_code from fnd_lookup_values_vl
808 AND APPLICATION_USG_CODE = c_appl_usg_code;
809
810 /* CURSOR appl_usg_code (c_appl_usg IN VARCHAR2) IS
812 where lookup_type like 'AHL_APPLICATION_USAGE_CODE'
813 and meaning like c_appl_usg;*/
814 BEGIN
815 /*open appl_usg_code(p_wf_mapping_rec.application_usg);
816 fetch appl_usg_code into l_application_usg_code;
817 close appl_usg_code;*/
818
819 l_application_usg_code := p_wf_mapping_rec.application_usg_code;
820
821 Open Cur_object(nvl(l_application_usg_code, 'AHL'));
822 Fetch Cur_object into l_wf_id;
823 Close Cur_object;
824 IF l_wf_id <> p_wf_mapping_rec.wf_mapping_id THEN
825 Open check_object(nvl(l_application_usg_code, 'AHL'));
826 Fetch check_object into l_count;
827 IF check_object%found THEN
828 Close check_object ;
829 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
830 Fnd_Message.set_name ('AHL', 'AHL_WF_DEFAULT_NOT_TWO');
831 Fnd_Msg_Pub.ADD;
832 END IF;
833 x_return_status := Fnd_Api.g_ret_sts_error;
834 RETURN;
835 ELSE
836 Close check_object ;
837 END IF;
838 END IF;
839
840 END Check_Default;
841
842 --------------------------------------------------------------------
843 -- PROCEDURE
844 -- Delete_wf_mapping
845 --
846 --------------------------------------------------------------------
847
848 PROCEDURE Delete_wf_mapping (
849 p_api_version IN NUMBER := 1.0,
850 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
851 p_commit IN VARCHAR2 := Fnd_Api.g_false,
852 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2,
856 p_wf_mapping_id IN NUMBER,
857 p_object_version IN NUMBER
858 )
859
860 IS
861 CURSOR c_wf_mapping IS
862 SELECT *
863 FROM Ahl_wf_mapping
864 WHERE wf_mapping_id = p_wf_mapping_id;
865 --
866 -- This is the only exception for using %ROWTYPE.
867 -- We are selecting from the VL view, which may
868 -- have some denormalized columns as compared to
869 -- the base tables.
870 l_wf_mapping_rec c_wf_mapping%ROWTYPE;
871 l_api_version CONSTANT NUMBER := 1.0;
872 l_api_name CONSTANT VARCHAR2(30) := 'Delete_wf_mapping';
873 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_Api_name;
874 BEGIN
875
876 --------------------- initialize -----------------------
877 SAVEPOINT Delete_wf_mapping;
878 -- Check if API is called in debug mode. If yes, enable debug.
879 IF G_DEBUG='Y' THEN
880 Ahl_Debug_Pub.enable_debug;
881 END IF;
882 -- Debug info.
883 IF G_DEBUG='Y' THEN
884 Ahl_Debug_Pub.debug( l_full_name ||':Start');
885 END IF;
886 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
887 Fnd_Msg_Pub.initialize;
888 END IF;
889 IF NOT Fnd_Api.compatible_api_call (
890 l_api_version,
891 p_api_version,
892 l_api_name,
893 g_pkg_name
894 ) THEN
895 RAISE Fnd_Api.g_exc_unexpected_error;
896 END IF;
897 x_return_status := Fnd_Api.g_ret_sts_success;
898
899 ------------------------ delete ------------------------
900 IF G_DEBUG='Y' THEN
901 Ahl_Debug_Pub.debug( l_full_name ||':Delete');
902 END IF;
903
904 OPEN c_wf_mapping;
905 FETCH c_wf_mapping INTO l_wf_mapping_rec;
906 IF c_wf_mapping%NOTFOUND THEN
907 CLOSE c_wf_mapping;
908 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
909 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
910 Fnd_Msg_Pub.ADD;
911 END IF;
912 RAISE Fnd_Api.g_exc_error;
913 END IF;
914 CLOSE c_wf_mapping;
915 -- Delete TL data
916
917 DELETE FROM Ahl_wf_mapping
918 WHERE wf_mapping_id = p_wf_mapping_id;
919 IF (SQL%NOTFOUND) THEN
920 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error)
921 THEN
922 Fnd_Message.set_name ('AHL', 'AHL_API_RECORD_NOT_FOUND');
923 Fnd_Msg_Pub.ADD;
924 END IF;
925 RAISE Fnd_Api.g_exc_error;
926 END IF;
927
928 -------------------- finish --------------------------
929 IF Fnd_Api.to_boolean (p_commit) THEN
930 COMMIT;
931 END IF;
932 Fnd_Msg_Pub.count_and_get (
933 p_encoded => Fnd_Api.g_false,
934 p_count => x_msg_count,
935 p_data => x_msg_data
936 );
937 IF G_DEBUG='Y' THEN
938 Ahl_Debug_Pub.debug( l_full_name ||':End');
939 END IF;
940 -- Check if API is called in debug mode. If yes, disable debug.
941 IF G_DEBUG='Y' THEN
942 Ahl_Debug_Pub.disable_debug;
943 END IF;
944 EXCEPTION
945 WHEN Fnd_Api.g_exc_error THEN
946 ROLLBACK TO Delete_wf_mapping;
947 x_return_status := Fnd_Api.g_ret_sts_error;
948 Fnd_Msg_Pub.count_and_get (
949 p_encoded => Fnd_Api.g_false,
950 p_count => x_msg_count,
951 p_data => x_msg_data
955 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
952 );
953 WHEN Fnd_Api.g_exc_unexpected_error THEN
954 ROLLBACK TO Delete_wf_mapping;
956 Fnd_Msg_Pub.count_and_get (
957 p_encoded => Fnd_Api.g_false,
958 p_count => x_msg_count,
959 p_data => x_msg_data
960 );
961 WHEN OTHERS THEN
962 ROLLBACK TO Delete_wf_mapping;
963 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
964 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
965 THEN
966 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
967 END IF;
968 Fnd_Msg_Pub.count_and_get (
969 p_encoded => Fnd_Api.g_false,
970 p_count => x_msg_count,
971 p_data => x_msg_data
972 );
973 END Delete_wf_mapping;
974
975 --------------------------------------------------------------------
976 -- PROCEDURE
977 -- Validate_Wf_Mapping
978 --
979 --------------------------------------------------------------------
980 PROCEDURE Validate_Wf_Mapping (
981 p_api_version IN NUMBER := 1.0,
982 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
983 p_commit IN VARCHAR2 := Fnd_Api.g_false,
984 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
985 x_return_status OUT NOCOPY VARCHAR2,
986 x_msg_count OUT NOCOPY NUMBER,
987 x_msg_data OUT NOCOPY VARCHAR2,
988 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type
989 )
990
991 IS
992 L_API_VERSION CONSTANT NUMBER := 1.0;
993 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Wf_Mapping';
994 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
995 l_return_status VARCHAR2(1);
996 BEGIN
997 --------------------- initialize -----------------------
998 -- Check if API is called in debug mode. If yes, enable debug.
999 IF G_DEBUG='Y' THEN
1000 Ahl_Debug_Pub.enable_debug;
1001 END IF;
1002 -- Debug info.
1003 IF G_DEBUG='Y' THEN
1004 Ahl_Debug_Pub.debug( l_full_name ||':Start');
1005 END IF;
1006 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1007 Fnd_Msg_Pub.initialize;
1008 END IF;
1009 IF NOT Fnd_Api.compatible_api_call (
1010 l_api_version,
1011 p_api_version,
1012 l_api_name,
1013 g_pkg_name
1014 ) THEN
1015 RAISE Fnd_Api.g_exc_unexpected_error;
1016 END IF;
1017 x_return_status := Fnd_Api.g_ret_sts_success;
1018 ---------------------- validate ------------------------
1019 IF G_DEBUG='Y' THEN
1020 Ahl_Debug_Pub.debug( l_full_name ||':Check items');
1021 END IF;
1022
1023 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
1024 Check_Wf_Mapping_Items (
1025 p_Wf_Mapping_rec => p_Wf_Mapping_rec,
1026 p_validation_mode => Jtf_Plsql_Api.g_create,
1027 x_return_status => l_return_status
1028 );
1029 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1030 RAISE Fnd_Api.g_exc_unexpected_error;
1031 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1032 RAISE Fnd_Api.g_exc_error;
1033 END IF;
1034 END IF;
1035 IF G_DEBUG='Y' THEN
1036 Ahl_Debug_Pub.debug( l_full_name ||':Check record');
1037 END IF;
1038
1039 -------------------- finish --------------------------
1040 Fnd_Msg_Pub.count_and_get (
1041 p_encoded => Fnd_Api.g_false,
1042 p_count => x_msg_count,
1043 p_data => x_msg_data
1044 );
1045 IF G_DEBUG='Y' THEN
1046 Ahl_Debug_Pub.debug( l_full_name ||':End');
1047 END IF;
1048 -- Check if API is called in debug mode. If yes, disable debug.
1049 IF G_DEBUG='Y' THEN
1050 Ahl_Debug_Pub.disable_debug;
1051 END IF;
1052 EXCEPTION
1053 WHEN Fnd_Api.g_exc_error THEN
1054 x_return_status := Fnd_Api.g_ret_sts_error;
1055 Fnd_Msg_Pub.count_and_get (
1056 p_encoded => Fnd_Api.g_false,
1057 p_count => x_msg_count,
1058 p_data => x_msg_data
1059 );
1060 WHEN Fnd_Api.g_exc_unexpected_error THEN
1061 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1062 Fnd_Msg_Pub.count_and_get (
1063 p_encoded => Fnd_Api.g_false,
1064 p_count => x_msg_count,
1065 p_data => x_msg_data
1066 );
1067 WHEN OTHERS THEN
1068 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
1069 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1070 THEN
1071 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
1072 END IF;
1073 Fnd_Msg_Pub.count_and_get (
1074 p_encoded => Fnd_Api.g_false,
1075 p_count => x_msg_count,
1076 p_data => x_msg_data
1077 );
1078 END Validate_Wf_Mapping;
1079
1080 ---------------------------------------------------------------------
1081 -- PROCEDURE
1082 -- Check_Wf_Mapping_Items
1083 --
1084 ---------------------------------------------------------------------
1085 PROCEDURE Check_Wf_Mapping_Items (
1086
1087 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
1088 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1092 BEGIN
1089 x_return_status OUT NOCOPY VARCHAR2
1090 )
1091 IS
1093 --
1094 -- Validate required items.
1095 Check_Wf_Mapping_Req_Items (
1096 p_Wf_Mapping_rec => p_Wf_Mapping_rec,
1097 x_return_status => x_return_status
1098 );
1099 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1100 RETURN;
1101 END IF;
1102 --
1103 -- Validate uniqueness.
1104 Check_Wf_Mapping_UK_Items (
1105 p_Wf_Mapping_rec => p_Wf_Mapping_rec,
1106 p_validation_mode => p_validation_mode,
1107 x_return_status => x_return_status
1108 );
1109 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1110 RETURN;
1111 END IF;
1112
1113 END Check_Wf_Mapping_Items;
1114
1115 PROCEDURE Check_Appl_Usg_Code(
1116 p_wf_mapping_rec IN Wf_Mapping_Rec_Type,
1117 x_return_status OUT NOCOPY VARCHAR2
1118 )
1119 IS
1120 l_count NUMBER;
1121 CURSOR chk_appl_usg_code IS
1122 SELECT 1 FROM FND_LOOKUPS
1123 WHERE meaning = p_wf_mapping_rec.Application_Usg
1124 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';
1125 BEGIN
1126 OPEN chk_appl_usg_code ;
1127 FETCH chk_appl_usg_code INTO l_count;
1128 IF chk_appl_usg_code%NOTFOUND THEN
1129 CLOSE chk_appl_usg_code;
1130 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1131 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
1132 Fnd_Msg_Pub.ADD;
1133 END IF;
1134 x_return_status := Fnd_Api.g_ret_sts_error;
1135 RETURN;
1136 ELSE
1137 CLOSE chk_appl_usg_code;
1138 END IF;
1139 END Check_Appl_Usg_Code;
1140
1141 ---------------------------------------------------------------------
1142 -- PROCEDURE
1143 -- Init_Wf_Mapping_Rec
1144 --
1145 ---------------------------------------------------------------------
1146 /*PROCEDURE Init_Wf_Mapping_Rec (
1147 x_Wf_Mapping_rec OUT NOCOPY Wf_Mapping_Rec_Type
1148 )
1149 IS
1150 BEGIN
1151 x_Wf_Mapping_rec.object_version_number := Fnd_Api.g_miss_num;
1152 x_Wf_Mapping_rec.active_flag := Fnd_Api.g_miss_char;
1153 x_Wf_Mapping_rec.wf_process_name := Fnd_Api.g_miss_char;
1154 x_Wf_Mapping_rec.approval_object := Fnd_Api.g_miss_char;
1155 x_Wf_Mapping_rec.item_type := Fnd_Api.g_miss_char;
1156
1157 END Init_Wf_Mapping_Rec;
1158 */
1159 ---------------------------------------------------------------------
1160 -- PROCEDURE
1161 -- Complete_Wf_Mapping_Rec
1162 --
1163 ---------------------------------------------------------------------
1164 PROCEDURE Complete_Wf_Mapping_Rec (
1165 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
1166 x_complete_rec OUT NOCOPY Wf_Mapping_Rec_Type
1167 )
1168 IS
1169 CURSOR c_Wf_Mapping IS
1170 SELECT *
1171 FROM AHL_Wf_Mapping
1172 WHERE WF_Mapping_ID = p_Wf_Mapping_rec.WF_Mapping_ID;
1173 --
1174 -- This is the only exception for using %ROWTYPE.
1175 --
1176
1177 l_Wf_Mapping_rec c_Wf_Mapping%ROWTYPE;
1178 BEGIN
1179 x_complete_rec := p_Wf_Mapping_rec;
1180 OPEN c_Wf_Mapping;
1181 FETCH c_Wf_Mapping INTO l_Wf_Mapping_rec;
1182 IF c_Wf_Mapping%NOTFOUND THEN
1183 CLOSE c_Wf_Mapping;
1184 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1185 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
1186 Fnd_Msg_Pub.ADD;
1187 END IF;
1188 RAISE Fnd_Api.g_exc_error;
1189 END IF;
1190 CLOSE c_Wf_Mapping;
1191
1192 --
1193 -- OBJECT VERSION NUMBER
1194 IF p_Wf_Mapping_rec.object_version_number = Fnd_Api.g_miss_num THEN
1195 x_complete_rec.object_version_number := l_Wf_Mapping_rec.object_version_number;
1196 END IF;
1197 --
1198 -- ACTIVE FLAG
1199 IF p_Wf_Mapping_rec.active_flag = Fnd_Api.g_miss_char THEN
1200 x_complete_rec.active_flag := l_Wf_Mapping_rec.active_flag;
1201 END IF;
1202 --
1203 -- WF_PROCESS_NAME
1204 IF p_Wf_Mapping_rec.wf_process_name = Fnd_Api.g_miss_char THEN
1205 x_complete_rec.wf_process_name := l_Wf_Mapping_rec.wf_process_name;
1206 END IF;
1207
1208 -- APPROVAL OBJECT
1209 IF p_Wf_Mapping_rec.approval_object = Fnd_Api.g_miss_char THEN
1210 x_complete_rec.approval_object := l_Wf_Mapping_rec.approval_object;
1211 END IF;
1212 --
1213 -- ITEM TYPE
1214 IF p_Wf_Mapping_rec.item_type = Fnd_Api.g_miss_char THEN
1215 x_complete_rec.item_type := l_Wf_Mapping_rec.item_type;
1216 END IF;
1217
1218 END Complete_Wf_Mapping_Rec;
1219
1220
1221 -- Check_Wf_Mapping_Req_Items
1222 PROCEDURE Check_Wf_Mapping_Req_Items (
1223 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
1224 x_return_status OUT NOCOPY VARCHAR2
1225 )
1226 IS
1227 BEGIN
1228 -- FOR PROCESS NAME IF ACTIVE_FLAG IS 'YES' THEN CANNOT BE NULL
1229 IF UPPER(p_Wf_Mapping_rec.ACTIVE_FLAG) = 'Y' THEN
1230 IF p_Wf_Mapping_rec.WF_DISPLAY_NAME IS NULL THEN
1231 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1232 Fnd_Message.set_name ('AHL', 'AHL_WF_PROCESS_NAME_MISSING');
1233 Fnd_Msg_Pub.ADD;
1234 END IF;
1238 END IF;
1235 x_return_status := Fnd_Api.g_ret_sts_error;
1236 RETURN;
1237 END IF;
1239 IF p_Wf_Mapping_rec.APPLICATION_USG_CODE IS NULL THEN
1240 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1241 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_ISNULL');
1242 Fnd_Msg_Pub.ADD;
1243 END IF;
1244 x_return_status := Fnd_Api.g_ret_sts_error;
1245 RETURN;
1246 END IF;
1247
1248 END Check_Wf_Mapping_Req_Items;
1249
1250
1251 -- Check_Wf_Mapping_UK_Items
1252 PROCEDURE Check_Wf_Mapping_UK_Items (
1253 p_Wf_Mapping_rec IN Wf_Mapping_Rec_Type,
1254 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1255 x_return_status OUT NOCOPY VARCHAR2
1256 )
1257 IS
1258 l_valid_flag VARCHAR2(1);
1259 --l_application_usg_code VARCHAR2(30);
1260 /* CURSOR c_appl_usg IS
1261 SELECT LOOKUP_CODE
1262 FROM FND_LOOKUPS
1263 WHERE MEANING = p_Wf_Mapping_rec.application_usg
1264 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';*/
1265 BEGIN
1266 x_return_status := Fnd_Api.g_ret_sts_success;
1267 -- MEDIA_ID
1268 -- For Create_Wf_Mapping, when ID is passed in, we need to
1269 -- check if this ID is unique.
1270 IF p_validation_mode = Jtf_Plsql_Api.g_create
1271 AND p_Wf_Mapping_rec.WF_Mapping_ID IS NOT NULL
1272 THEN
1273 IF Ahl_Utility_Pvt.check_uniqueness(
1274 'AHL_Wf_Mapping',
1275 'WF_Mapping_ID = ' || p_Wf_Mapping_rec.WF_Mapping_ID
1276 ) = Fnd_Api.g_false
1277 THEN
1278 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1279 Fnd_Message.set_name ('AHL', 'AHL_WF_MAPPING_DUP_ID');
1280 Fnd_Msg_Pub.ADD;
1281 END IF;
1282 x_return_status := Fnd_Api.g_ret_sts_error;
1283 RETURN;
1284 END IF;
1285 END IF;
1286
1287 -- Reema:
1288 -- Retrieve the application usage code
1289 -- from fnd_lookups
1290 /*OPEN c_appl_usg;
1291 FETCH c_appl_usg INTO l_application_usg_code;
1292 IF c_appl_usg%NOTFOUND THEN
1293 l_application_usg_code := NULL;
1294 END IF;
1295 CLOSE c_appl_usg;*/
1296
1297 -- check if APPROVAL OBJECT is UNIQUE
1298 IF p_validation_mode = Jtf_Plsql_Api.g_create THEN
1299 l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
1300 'AHL_Wf_Mapping',
1301 'APPROVAL_OBJECT = ''' || p_Wf_Mapping_rec.approval_object ||
1302 ''' AND APPLICATION_USG_CODE = ''' || p_Wf_Mapping_rec.application_usg_code || '''');
1303 ELSE
1304 l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
1305 'AHL_Wf_Mapping',
1306 'APPROVAL_OBJECT = ''' || p_Wf_Mapping_rec.approval_object ||
1307 ''' AND APPLICATION_USG_CODE = ''' || p_Wf_Mapping_rec.application_usg_code ||
1308 ''' AND WF_Mapping_ID <> ' || p_Wf_Mapping_rec.WF_Mapping_ID );
1309 END IF;
1310 IF l_valid_flag = Fnd_Api.g_false THEN
1311 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1312 Fnd_Message.set_name ('AHL', 'AHL_WF_OBJECT_NOT_UNIQUE');
1313 Fnd_Msg_Pub.ADD;
1314 END IF;
1315 x_return_status := Fnd_Api.g_ret_sts_error;
1316 RETURN;
1317 END IF;
1318 END Check_Wf_Mapping_UK_Items;
1319
1320 END Ahl_Wf_Mapping_Pvt;