1 PACKAGE BODY AHL_FMP_MR_ACTION_PVT AS
2 /* $Header: AHLVMRAB.pls 115.19 2003/10/20 19:36:45 sikumar noship $ */
3 G_PKG_NAME VARCHAR2(30):= 'AHL_FMP_MR_ACTION_PVT';
4 G_PM_INSTALL VARCHAR2(30):=ahl_util_pkg.is_pm_installed;
5 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7
8 PROCEDURE DEFAULT_MISSING_ATTRIBS(p_x_mr_action_tbl IN OUT NOCOPY AHL_FMP_MR_ACTION_PVT.MR_ACTION_TBL)
9 AS
10 CURSOR CurAction(C_MR_ACTION_ID NUMBER)
11 IS
12 SELECT * FROM AHL_MR_ACTIONS_V
13 WHERE MR_ACTION_ID=C_MR_ACTION_ID;
14
15 l_action_rec CurAction%rowtype;
16 BEGIN
17 IF G_DEBUG='Y' THEN
18 AHL_DEBUG_PUB.enable_debug;
19 END IF;
20 IF P_X_MR_ACTION_TBL.COUNT >0
21 THEN
22 FOR i IN P_X_MR_ACTION_TBL.FIRST.. P_X_MR_ACTION_TBL.LAST
23 LOOP
24 IF p_x_mr_action_TBL(i).DML_OPERATION<>'D'
25 THEN
26
27 OPEN CurAction (p_x_mr_action_TBL(i).MR_ACTION_ID);
28 fetch CurAction into l_action_rec;
29 CLOSE CurAction;
30
31 IF p_x_mr_action_TBL(I).MR_HEADER_ID= FND_API.G_MISS_NUM
32 THEN
33 p_x_mr_action_TBL(I).MR_HEADER_ID:=NULL;
34 ELSIF p_x_mr_action_TBL(I).MR_HEADER_ID IS NULL
35 THEN
36 p_x_mr_action_TBL(I).MR_HEADER_ID:=l_action_rec.MR_HEADER_ID;
37 END IF;
38
39 IF p_x_mr_action_TBL(I).MR_ACTION_CODE= FND_API.G_MISS_CHAR
40 THEN
41 p_x_mr_action_TBL(I).MR_ACTION_CODE:=NULL;
42 ELSIF p_x_mr_action_TBL(I).MR_ACTION_CODE IS NULL
43 THEN
44 p_x_mr_action_TBL(I).MR_ACTION_CODE:=l_action_rec.MR_ACTION_CODE;
45 END IF;
46
47 IF p_x_mr_action_TBL(I).MR_ACTION= FND_API.G_MISS_CHAR
48 THEN
49 p_x_mr_action_TBL(I).MR_ACTION:=NULL;
50 ELSIF p_x_mr_action_TBL(I).MR_ACTION IS NULL
51 THEN
52 p_x_mr_action_TBL(I).MR_ACTION:=l_action_rec.MR_ACTION;
53 END IF;
54
55 IF p_x_mr_action_TBL(I).MR_ACTION_ID= FND_API.G_MISS_NUM
56 THEN
57 p_x_mr_action_TBL(I).MR_ACTION_ID:=NULL;
58 END IF;
59
60 IF p_x_mr_action_TBL(I).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
61 THEN
62 p_x_mr_action_TBL(I).OBJECT_VERSION_NUMBER:=null;
63 ELSIF p_x_mr_action_TBL(I).OBJECT_VERSION_NUMBER IS NULL
64 THEN
65 p_x_mr_action_TBL(I).OBJECT_VERSION_NUMBER:=l_action_rec.OBJECT_VERSION_NUMBER;
66 END IF;
67
68 IF p_x_mr_action_TBL(I).PLAN= FND_API.G_MISS_CHAR
69 THEN
70 p_x_mr_action_TBL(I).PLAN:=NULL;
71 ELSIF p_x_mr_action_TBL(I).PLAN IS NULL
72 THEN
73 p_x_mr_action_TBL(I).PLAN:=l_action_rec.PLAN_name;
74 END IF;
75
76
77 IF p_x_mr_action_TBL(I).PLAN_ID= FND_API.G_MISS_NUM
78 THEN
79 p_x_mr_action_TBL(I).PLAN_ID:=NULL;
80 ELSIF p_x_mr_action_TBL(I).PLAN_ID IS NULL
81 THEN
82 p_x_mr_action_TBL(I).PLAN_ID:=l_action_rec.PLAN_ID;
83 END IF;
84
85 IF p_x_mr_action_TBL(I).PLAN= FND_API.G_MISS_CHAR
86 THEN
87 p_x_mr_action_TBL(I).PLAN:=NULL;
88 ELSIF p_x_mr_action_TBL(I).PLAN IS NULL
89 THEN
90 p_x_mr_action_TBL(I).PLAN:=l_action_rec.PLAN_NAME;
91 END IF;
92
93 IF p_x_mr_action_TBL(I).DESCRIPTION= FND_API.G_MISS_CHAR
94 THEN
95 p_x_mr_action_TBL(I).DESCRIPTION:=NULL;
96 ELSIF p_x_mr_action_TBL(I).DESCRIPTION IS NULL
97 THEN
98 p_x_mr_action_TBL(I).DESCRIPTION:=l_action_rec.DESCRIPTION;
99 END IF;
100
101 IF p_x_mr_action_TBL(I).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
102 THEN
103 p_x_mr_action_TBL(I).ATTRIBUTE_CATEGORY:=NULL;
104 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE_CATEGORY IS NULL
105 THEN
106 p_x_mr_action_TBL(I).ATTRIBUTE_CATEGORY:=l_action_rec.ATTRIBUTE_CATEGORY;
107 END IF;
108
109 IF p_x_mr_action_TBL(I).ATTRIBUTE1= FND_API.G_MISS_CHAR
110 THEN
111 p_x_mr_action_TBL(I).ATTRIBUTE1:=NULL;
112 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE1 IS NULL
113 THEN
114 p_x_mr_action_TBL(I).ATTRIBUTE1:=l_action_rec.ATTRIBUTE1;
115 END IF;
116
117 IF p_x_mr_action_TBL(I).ATTRIBUTE2= FND_API.G_MISS_CHAR
118 THEN
119 p_x_mr_action_TBL(I).ATTRIBUTE2:=NULL;
120 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE2 IS NULL
121 THEN
122 p_x_mr_action_TBL(I).ATTRIBUTE2:=l_action_rec.ATTRIBUTE2;
123 END IF;
124
125 IF p_x_mr_action_TBL(I).ATTRIBUTE3= FND_API.G_MISS_CHAR
126 THEN
127 p_x_mr_action_TBL(I).ATTRIBUTE3:=NULL;
128 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE3 IS NULL
129 THEN
130 p_x_mr_action_TBL(I).ATTRIBUTE3:=l_action_rec.ATTRIBUTE3;
131 END IF;
132
133 IF p_x_mr_action_TBL(I).ATTRIBUTE4= FND_API.G_MISS_CHAR
134 THEN
135 p_x_mr_action_TBL(I).ATTRIBUTE4:=NULL;
136 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE4 IS NULL
137 THEN
138 p_x_mr_action_TBL(I).ATTRIBUTE4:=l_action_rec.ATTRIBUTE4;
139 END IF;
140
141 IF p_x_mr_action_TBL(I).ATTRIBUTE5= FND_API.G_MISS_CHAR
142 THEN
143 p_x_mr_action_TBL(I).ATTRIBUTE5:=NULL;
144 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE5 IS NULL
145 THEN
146 p_x_mr_action_TBL(I).ATTRIBUTE5:=l_action_rec.ATTRIBUTE5;
147 END IF;
148
149 IF p_x_mr_action_TBL(I).ATTRIBUTE6= FND_API.G_MISS_CHAR
150 THEN
151 p_x_mr_action_TBL(I).ATTRIBUTE6:=NULL;
152 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE6 IS NULL
153 THEN
154 p_x_mr_action_TBL(I).ATTRIBUTE6:=l_action_rec.ATTRIBUTE6;
155 END IF;
156
157 IF p_x_mr_action_TBL(I).ATTRIBUTE7= FND_API.G_MISS_CHAR
158 THEN
159 p_x_mr_action_TBL(I).ATTRIBUTE7:=NULL;
160 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE7 IS NULL
161 THEN
162 p_x_mr_action_TBL(I).ATTRIBUTE7:=l_action_rec.ATTRIBUTE7;
163 END IF;
164
165 IF p_x_mr_action_TBL(I).ATTRIBUTE8= FND_API.G_MISS_CHAR
166 THEN
167 p_x_mr_action_TBL(I).ATTRIBUTE8:=NULL;
168 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE8 IS NULL
169 THEN
170 p_x_mr_action_TBL(I).ATTRIBUTE8:=l_action_rec.ATTRIBUTE8;
171 END IF;
172
173 IF p_x_mr_action_TBL(I).ATTRIBUTE9= FND_API.G_MISS_CHAR
174 THEN
175 p_x_mr_action_TBL(I).ATTRIBUTE9:=NULL;
176 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE9 IS NULL
177 THEN
178 p_x_mr_action_TBL(I).ATTRIBUTE9:=l_action_rec.ATTRIBUTE9;
179 END IF;
180
181 IF p_x_mr_action_TBL(I).ATTRIBUTE10= FND_API.G_MISS_CHAR
182 THEN
183 p_x_mr_action_TBL(I).ATTRIBUTE10:=NULL;
184 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE10 IS NULL
185 THEN
186 p_x_mr_action_TBL(I).ATTRIBUTE10:=l_action_rec.ATTRIBUTE10;
187 END IF;
188
189 IF p_x_mr_action_TBL(I).ATTRIBUTE11= FND_API.G_MISS_CHAR
190 THEN
191 p_x_mr_action_TBL(I).ATTRIBUTE11:=NULL;
192 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE11 IS NULL
193 THEN
194 p_x_mr_action_TBL(I).ATTRIBUTE11:=l_action_rec.ATTRIBUTE11;
195 END IF;
196
197 IF p_x_mr_action_TBL(I).ATTRIBUTE12= FND_API.G_MISS_CHAR
198 THEN
199 p_x_mr_action_TBL(I).ATTRIBUTE12:=NULL;
200 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE12 IS NULL
201 THEN
202 p_x_mr_action_TBL(I).ATTRIBUTE12:=l_action_rec.ATTRIBUTE12;
203 END IF;
204
205 IF p_x_mr_action_TBL(I).ATTRIBUTE13= FND_API.G_MISS_CHAR
206 THEN
207 p_x_mr_action_TBL(I).ATTRIBUTE13:=NULL;
208 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE13 IS NULL
209 THEN
210 p_x_mr_action_TBL(I).ATTRIBUTE13:=l_action_rec.ATTRIBUTE13;
211 END IF;
212
213 IF p_x_mr_action_TBL(I).ATTRIBUTE14= FND_API.G_MISS_CHAR
214 THEN
215 p_x_mr_action_TBL(I).ATTRIBUTE14:=NULL;
216 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE14 IS NULL
217 THEN
218 p_x_mr_action_TBL(I).ATTRIBUTE14:=l_action_rec.ATTRIBUTE14;
219 END IF;
220
221 IF p_x_mr_action_TBL(I).ATTRIBUTE15= FND_API.G_MISS_CHAR
222 THEN
223 p_x_mr_action_TBL(I).ATTRIBUTE15:=NULL;
224 ELSIF p_x_mr_action_TBL(I).ATTRIBUTE15 IS NULL
225 THEN
226 p_x_mr_action_TBL(I).ATTRIBUTE15:=l_action_rec.ATTRIBUTE15;
227 END IF;
228 END IF;
229 END LOOP;
230 END IF;
231 END;
232
233 --Tranlate Value to id.
234
235 PROCEDURE TRANS_VALUE_ID
236 (
237 x_return_status OUT NOCOPY VARCHAR2,
238 p_x_mr_action_rec IN OUT NOCOPY MR_ACTION_REC
239 )
240 as
241 CURSOR get_lookup_meaning_to_code(c_lookup_type VARCHAR2,c_meaning VARCHAR2)
242 IS
243 SELECT lookup_code
244 FROM FND_LOOKUP_VALUES_VL
245 WHERE lookup_type= c_lookup_type
246 AND upper(meaning)=upper(c_meaning)
247 AND sysdate between start_date_active
248 AND nvl(end_date_active,sysdate);
249
250 CURSOR get_planid_frm_name(c_plan_name VARCHAR2)
251 IS
252 SELECT plan_id
253 FROM QA_PLANS
254 WHERE upper(name)=upper(c_plan_name)
255 AND sysdate between EFFECTIVE_FROM
256 AND nvl(EFFECTIVE_TO,sysdate);
257 BEGIN
258
259 x_return_status:=FND_API.G_RET_STS_SUCCESS;
260
261 IF G_DEBUG='Y' THEN
262 AHL_DEBUG_PUB.enable_debug;
263 END IF;
264
265 IF p_x_mr_action_rec.mr_action is null OR p_x_mr_action_rec.mr_action=FND_API.G_MISS_CHAR
266 THEN
267 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ACTION_CODE_NULL');
268 FND_MSG_PUB.ADD;
269 ELSE
270 OPEN get_lookup_meaning_to_code('AHL_FMP_MR_ACTION',p_x_mr_action_rec.MR_ACTION);
271 FETCH get_lookup_meaning_to_code INTO p_x_mr_action_rec.MR_ACTION_CODE;
272
273 IF get_lookup_meaning_to_code%NOTFOUND
274 THEN
275 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ACTION_CODE_INVALID');
276 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_action_rec.MR_ACTION,false);
277 FND_MESSAGE.SET_TOKEN('RECORD',nvl(p_x_mr_action_rec.MR_ACTION,'')||'-'||NVL(p_x_mr_action_rec.description,'')||'-'||NVL(p_x_mr_action_rec.plan,''),false);
278 FND_MSG_PUB.ADD;
279 END IF;
280 CLOSE get_lookup_meaning_to_code;
281 END IF;
282
283 IF (p_x_mr_action_rec.plan is null OR p_x_mr_action_rec.plan=FND_API.G_MISS_CHAR)
284 THEN
285 p_x_mr_action_rec.PLAN:=FND_API.G_MISS_CHAR;
286 p_x_mr_action_rec.PLAN_ID:=FND_API.G_MISS_NUM;
287 ELSE
288 OPEN get_planid_frm_name(p_x_mr_action_rec.PLAN);
289 FETCH get_planid_frm_name INTO p_x_mr_action_rec.PLAN_ID;
290 IF get_planid_frm_name%NOTFOUND
291 THEN
292 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PLAN_ID_INVALID');
293 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_action_rec.MR_ACTION,false);
294 FND_MESSAGE.SET_TOKEN('RECORD',NVL(p_x_mr_action_rec.MR_ACTION,'')||'-'||NVL(p_x_mr_action_rec.description,'')||'-'||NVL(p_x_mr_action_rec.plan,''),false);
295 FND_MSG_PUB.ADD;
296 END IF;
297 CLOSE get_planid_frm_name;
298
299 END IF;
300 IF G_DEBUG='Y' THEN
301 AHL_DEBUG_PUB.disable_debug;
302 END IF;
303 END;
304
305 PROCEDURE VALIDATE_MR_ACTION
306 (
307 x_return_status OUT NOCOPY VARCHAR2,
308 p_mr_action_rec IN MR_ACTION_REC
309 )
310 as
311 CURSOR get_ACTION_id(c_mr_action_id NUMBER)
312 IS
313 SELECT MR_ACTION_ID
314 FROM AHL_MR_ACTIONS_B
315 WHERE MR_ACTION_ID=c_mr_action_id;
316
317 CURSOR GetMrDet(c_mr_header_id NUMBER )
318 IS
319 SELECT MR_STATUS_CODE,nvl(TYPE_CODE,'X') TYPE_CODE
320 FROM AHL_MR_HEADERS_B
321 WHERE MR_HEADER_ID=c_mr_header_id
322 and MR_STATUS_CODE IN('DRAFT','APPROVAL_REJECTED');
323
324 l_mr_rec GetMrDet%rowtype;
325
326 CURSOR CHECK_DUP_ACTION_CODE(c_mr_action_code VARCHAR2,c_mr_header_id NUMBER)
327 IS
328 SELECT *
329 FROM AHL_MR_ACTIONS_B
330 WHERE MR_HEADER_ID=c_mr_header_id
331 and MR_ACTION_CODE=c_MR_ACTION_CODE;
332
333 l_act_rec CHECK_DUP_ACTION_CODE%ROWTYPE;
334 l_mr_action_id NUMBER:=0;
335 BEGIN
336
337 IF G_DEBUG='Y' THEN
338 AHL_DEBUG_PUB.enable_debug;
339 END IF;
340
341 x_return_status:=FND_API.G_RET_STS_SUCCESS;
342
343 IF p_mr_action_rec.dml_operation<>'C'
344 THEN
345 IF (p_mr_action_rec.MR_ACTION_ID IS NULL OR p_mr_action_rec.MR_ACTION_ID=FND_API.G_MISS_NUM)
346 THEN
347 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ACTION_ID_NULL');
348 FND_MESSAGE.SET_TOKEN('RECORD',NVL(p_mr_action_rec.MR_ACTION,'')||'-'||NVL(p_mr_action_rec.description,'')||'-'||NVL(p_mr_action_rec.plan,''),false);
349 FND_MSG_PUB.ADD;
350 END IF;
351 END IF;
352
353 IF (p_mr_action_rec.MR_ACTION_ID IS NOT NULL OR p_mr_action_rec.MR_ACTION_ID<>FND_API.G_MISS_NUM) AND p_mr_action_rec.dml_operation<>'C'
354 THEN
355 OPEN get_ACTION_id(p_mr_action_rec.mr_action_id) ;
356 FETCH get_ACTION_id INTO l_mr_action_id;
357
358 IF GET_ACTION_ID%NOTFOUND
359 THEN
360 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_RECORD_CHANGED');
361 FND_MESSAGE.SET_TOKEN('RECORD',NVL(p_mr_action_rec.MR_ACTION,'')||'-'||NVL(p_mr_action_rec.description,'')||'-'||NVL(p_mr_action_rec.plan,''),false);
362 FND_MSG_PUB.ADD;
363 END IF;
364 CLOSE get_ACTION_id;
365 END IF;
366
367 IF (p_mr_action_rec.OBJECT_VERSION_NUMBER IS NULL OR p_mr_action_rec.OBJECT_vERSION_NUMBER=FND_API.G_MISS_num)
368 THEN
369 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRA_OBJ_VERSION_NULL');
370 FND_MESSAGE.SET_TOKEN('RECORD',NVL(p_mr_action_rec.MR_ACTION,'')||'-'||NVL(p_mr_action_rec.description,'')||'-'||NVL(p_mr_action_rec.plan,''),false);
371 FND_MSG_PUB.ADD;
372 END IF;
373
374 IF p_mr_action_rec.dml_operation<>'D'
375 THEN
376
377 IF p_mr_action_rec.MR_HEADER_ID IS NULL OR p_mr_action_rec.MR_HEADER_ID=FND_API.G_MISS_NUM
378 THEN
379 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
380 FND_MSG_PUB.ADD;
381 ELSE
382 OPEN GetMrDet(p_mr_action_rec.MR_HEADER_ID);
383
384 FETCH GetMrDet into l_mr_rec;
385
386 IF GetMrDet%NOTFOUND
387 THEN
388 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
389 FND_MSG_PUB.ADD;
390 ELSE
391 IF G_PM_INSTALL='Y'
392 THEN
393 IF l_mr_rec.TYPE_CODE='PROGRAM'
394 THEN
395 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TYPE_CODE_PROGRAM');
396 FND_MSG_PUB.ADD;
397 END IF;
398 END IF;
399 END IF;
400 CLOSE GetMrDet;
401 END IF;
402
403 OPEN CHECK_DUP_ACTION_CODE(p_mr_action_rec.MR_ACTION_CODE,p_mr_action_rec.MR_HEADER_ID);
404 FETCH CHECK_DUP_ACTION_CODE into l_act_Rec;
405
406 IF CHECK_DUP_ACTION_CODE%FOUND
407 THEN
408 IF p_mr_action_rec.dml_operation='C'
409 THEN
410 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ACTION_CODE_DUP');
411 FND_MESSAGE.SET_TOKEN('RECORD',NVL(p_mr_action_rec.MR_ACTION,'')||'-'||NVL(p_mr_action_rec.description,'')||'-'||NVL(p_mr_action_rec.plan,''),false);
412 FND_MSG_PUB.ADD;
413 ELSIF p_mr_action_rec.dml_operation='U'
414 and l_act_Rec.mr_action_id<>p_mr_action_rec.MR_ACTION_ID
415 THEN
416 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ACTION_CODE_DUP');
417 FND_MESSAGE.SET_TOKEN('ACTION',p_mr_action_rec.MR_ACTION,false);
418 FND_MSG_PUB.ADD;
419 END IF;
420 END IF;
421 CLOSE CHECK_DUP_ACTION_CODE;
422 END IF;
423 END;
424
425 PROCEDURE PROCESS_MR_ACTION
426 (
427 p_api_version IN NUMBER := 1.0,
428 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
429 p_commit IN VARCHAR2 := FND_API.G_FALSE,
430 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
431 p_default IN VARCHAR2 := FND_API.G_FALSE,
432 p_module_type IN VARCHAR2 := NULL,
433 x_return_status OUT NOCOPY VARCHAR2,
434 x_msg_count OUT NOCOPY NUMBER,
435 x_msg_data OUT NOCOPY VARCHAR2,
436 p_x_mr_ACTION_TBL IN OUT NOCOPY MR_ACTION_TBL
437 )
438 as
439 l_api_name CONSTANT VARCHAR2(30):= 'PROCESS_MR_ACTION';
440 l_api_version CONSTANT NUMBER:= 1.0;
441 l_num_rec NUMBER;
442 l_msg_count NUMBER;
443 l_msg_data VARCHAR2(2000);
444 l_return_status VARCHAR2(1);
445 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
446 l_commit VARCHAR2(1):= FND_API.G_FALSE;
447 l_rowid VARCHAR2(30):=fnd_api.g_miss_char;
448 l_MR_ACTION_ID NUMBER:=0;
449 l_mr_action_rec MR_ACTION_REC;
450 BEGIN
451 SAVEPOINT PROCESS_MR_ACTION;
452
453 -- Standard call to check for call compatibility.
454
455 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
456 p_api_version,
457 l_api_name,G_PKG_NAME) THEN
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459 END IF;
460
461 -- Initialize message list if p_init_msg_list is set to TRUE.
462
463 IF FND_API.to_boolean(l_init_msg_list) THEN
464 FND_MSG_PUB.initialize;
465 END IF;
466
467 -- Initialize API return status to success
468
469 x_return_status:=FND_API.G_RET_STS_SUCCESS;
470
471 -- Enable Debug
472
473 IF G_DEBUG='Y' THEN
474 AHL_DEBUG_PUB.enable_debug;
475 AHL_DEBUG_PUB.debug( 'Enter PROCESS_MR_ACTION','+FMP_ACTION+');
476 END IF;
477
478 IF p_module_type = 'JSP'
479 THEN
480 FOR i IN P_X_MR_ACTION_TBL.FIRST.. P_X_MR_ACTION_TBL.LAST
481 LOOP
482 p_x_mr_ACTION_TBL(i).mr_action_code:=FND_API.G_MISS_CHAR;
483 p_x_mr_ACTION_TBL(i).plan_id:=FND_API.G_MISS_NUM;
484 if p_x_mr_ACTION_TBL(i).dml_operation='C'
485 then
486 p_x_mr_ACTION_TBL(i).OBJECT_VERSION_NUMBER:=1;
487 end if;
488 END LOOP;
489 END IF;
490
491 -- No need to translate meaning to id if dml operation is delete.
492 FOR i IN P_X_MR_ACTION_TBL.FIRST.. P_X_MR_ACTION_TBL.LAST
493 LOOP
494
495 IF p_x_mr_ACTION_TBL(i).DML_OPERATION<>'D'
496 THEN
497 l_mr_action_rec:=p_x_mr_ACTION_TBL(i);
498
499 TRANS_VALUE_ID
500 (
501 x_return_status =>x_return_Status,
502 p_x_mr_action_rec =>l_mr_action_rec);
503
504 p_x_mr_ACTION_TBL(i).mr_Action_code:=l_mr_action_rec.mr_Action_code;
505 p_x_mr_ACTION_TBL(i).plan_id:=l_mr_action_rec.plan_id;
506 END IF;
507
508
509 END LOOP;
510
511 l_msg_count := FND_MSG_PUB.count_msg;
512 IF l_msg_count > 0 THEN
513 X_msg_count := l_msg_count;
514 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518
519 IF FND_API.to_boolean(p_default)
520 THEN
521 DEFAULT_MISSING_ATTRIBS
522 (
523 p_x_mr_action_tbl =>p_x_mr_action_tbl
524 );
525 END IF;
526
527
528 --Start of API Body
529
530 FOR i IN P_X_MR_ACTION_TBL.FIRST.. P_X_MR_ACTION_TBL.LAST
531 LOOP
532
533 x_return_status:=FND_API.G_RET_STS_SUCCESS;
534
535 IF l_mr_action_rec.dml_operation<>'D'
536 THEN
537
538 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
539 THEN
540
541 VALIDATE_MR_ACTION
542 (
543 x_return_status =>x_return_Status,
544 p_mr_action_rec =>P_X_MR_ACTION_TBL(I));
545
546 END IF;
547
548 END IF;
549
550 l_msg_count := FND_MSG_PUB.count_msg;
551 IF l_msg_count > 0 THEN
552 X_msg_count := l_msg_count;
553 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
554 END IF;
555
556 IF p_x_mr_ACTION_TBL(i).DML_OPERATION='D' then
557
558 delete from AHL_MR_ACTIONS_TL
559 where MR_ACTION_ID = p_x_mr_ACTION_TBL(i).MR_ACTION_ID;
560
561 if (sql%ROWCOUNT=0)
562 then
563 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
564 FND_MSG_PUB.ADD;
565 else
566 delete from AHL_MR_ACTIONS_B
567 where MR_ACTION_ID = p_x_mr_ACTION_TBL(i).MR_ACTION_ID
568 AND OBJECT_VERSION_NUMBER=p_x_mr_ACTION_TBL(i).OBJECT_VERSION_NUMBER;
569
570 if (sql%ROWCOUNT=0) then
571 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
572 FND_MSG_PUB.ADD;
573 end if;
574 end if;
575 ELSIF p_x_mr_ACTION_TBL(i).DML_operation='U' then
576
577 IF x_return_status=FND_API.G_RET_STS_SUCCESS
578 THEN
579 AHL_MR_ACTIONS_PKG.UPDATE_ROW (
580 X_MR_ACTION_ID =>p_x_mr_ACTION_TBL(i).MR_ACTION_ID,
581 X_OBJECT_VERSION_NUMBER =>p_x_mr_ACTION_TBL(i).OBJECT_VERSION_NUMBER,
582 X_MR_HEADER_ID =>p_x_mr_ACTION_TBL(i).MR_HEADER_ID,
583 X_MR_ACTION_CODE =>p_x_mr_ACTION_TBL(i).MR_ACTION_CODE,
584 X_PLAN_ID =>p_x_mr_ACTION_TBL(i).PLAN_ID,
585 X_DESCRIPTION =>p_x_mr_ACTION_TBL(i).DESCRIPTION,
586 X_ATTRIBUTE_CATEGORY =>p_x_mr_ACTION_TBL(i).ATTRIBUTE_CATEGORY,
587 X_ATTRIBUTE1 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE1,
588 X_ATTRIBUTE2 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE2,
589 X_ATTRIBUTE3 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE3,
590 X_ATTRIBUTE4 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE4,
591 X_ATTRIBUTE5 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE5,
592 X_ATTRIBUTE6 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE6,
593 X_ATTRIBUTE7 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE7,
594 X_ATTRIBUTE8 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE8,
595 X_ATTRIBUTE9 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE9,
596 X_ATTRIBUTE10 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE10,
597 X_ATTRIBUTE11 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE11,
598 X_ATTRIBUTE12 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE12,
599 X_ATTRIBUTE13 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE13,
600 X_ATTRIBUTE14 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE14,
601 X_ATTRIBUTE15 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE15,
602 X_LAST_UPDATE_DATE =>sysdate,
603 X_LAST_UPDATED_BY =>fnd_global.user_id,
604 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
605 END IF;
606 ELSIF p_x_mr_ACTION_TBL(i).DML_operation='C' then
607
608 SELECT AHL_MR_ACTIONS_B_S.NEXTVAL
609 INTO l_MR_ACTION_ID
610 FROM DUAL;
611 IF x_return_status=FND_API.G_RET_STS_SUCCESS
612 THEN
613 AHL_MR_ACTIONS_PKG.INSERT_ROW (
614 X_ROWID =>l_ROWID,
615 X_MR_ACTION_ID =>l_MR_ACTION_ID,
616 X_OBJECT_VERSION_NUMBER =>1,
617 X_MR_HEADER_ID =>p_x_mr_ACTION_TBL(i).MR_HEADER_ID,
618 X_MR_ACTION_CODE =>p_x_mr_ACTION_TBL(i).MR_ACTION_CODE,
619 X_PLAN_ID =>p_x_mr_ACTION_TBL(i).PLAN_ID,
620 X_DESCRIPTION =>p_x_mr_ACTION_TBL(i).DESCRIPTION,
621 X_ATTRIBUTE_CATEGORY =>p_x_mr_ACTION_TBL(i).ATTRIBUTE_CATEGORY,
622 X_ATTRIBUTE1 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE1,
623 X_ATTRIBUTE2 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE2,
624 X_ATTRIBUTE3 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE3,
625 X_ATTRIBUTE4 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE4,
626 X_ATTRIBUTE5 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE5,
627 X_ATTRIBUTE6 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE6,
628 X_ATTRIBUTE7 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE7,
629 X_ATTRIBUTE8 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE8,
630 X_ATTRIBUTE9 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE9,
631 X_ATTRIBUTE10 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE10,
632 X_ATTRIBUTE11 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE11,
633 X_ATTRIBUTE12 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE12,
634 X_ATTRIBUTE13 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE13,
635 X_ATTRIBUTE14 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE14,
636 X_ATTRIBUTE15 =>p_x_mr_ACTION_TBL(i).ATTRIBUTE15,
637 X_CREATION_DATE =>sysdate,
638 X_CREATED_BY =>fnd_global.user_id,
639 X_LAST_UPDATE_DATE =>sysdate,
640 X_LAST_UPDATED_BY =>fnd_global.user_id,
641 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
642 END IF;
643 END IF;
644 END LOOP;
645
646 l_msg_count := FND_MSG_PUB.count_msg;
647 IF l_msg_count > 0 THEN
648 X_msg_count := l_msg_count;
649 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652
653
654 IF FND_API.TO_BOOLEAN(p_commit) THEN
655 COMMIT;
656 END IF;
657
658 -- Debug info
659
660 IF G_DEBUG='Y' THEN
661 AHL_DEBUG_PUB.debug( 'End of Private api '||l_api_name,'+debug+');
662 END IF;
663
664 IF G_DEBUG='Y' THEN
665 AHL_DEBUG_PUB.disable_debug;
666 END IF;
667
668 EXCEPTION
669 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
670 ROLLBACK TO PROCESS_MR_ACTION;
671 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
673 p_count => x_msg_count,
674 p_data => x_msg_data);
675
676 WHEN FND_API.G_EXC_ERROR THEN
677 ROLLBACK TO PROCESS_MR_ACTION;
678 X_return_status := FND_API.G_RET_STS_ERROR;
679 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
680 p_count => x_msg_count,
681 p_data => X_msg_data);
682 WHEN OTHERS THEN
683 ROLLBACK TO PROCESS_MR_ACTION;
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(p_pkg_name =>G_PKG_NAME,
688 p_procedure_name =>l_api_name,
689 p_error_text =>SUBSTR(SQLERRM,1,240)
690 );
691 END IF;
692 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
693 p_count => x_msg_count,
694 p_data => X_msg_data);
695 END;
696 END AHL_FMP_MR_ACTION_PVT;
[Home] [Help]