[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_HEADER_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_HEADER_PVT AS
2 /* $Header: AHLVMRHB.pls 120.4.12010000.2 2008/12/29 01:01:46 sracha ship $ */
3
4 G_PKG_NAME VARCHAR2(30):='AHL_FMP_MR_HEADER_PVT';
5 G_APPLN_USAGE VARCHAR2(30):=RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
6 G_DEBUG VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
7
8 PROCEDURE DEFAULT_MISSING_ATTRIBS
9 (p_x_mr_header_rec IN OUT NOCOPY AHL_FMP_MR_HEADER_PVT.MR_HEADER_REC)
10 AS
11 CURSOR CurHeaderDet(c_mr_header_id NUMBER) IS
12 SELECT MR_HEADER_ID,
13 OBJECT_VERSION_NUMBER,
14 LAST_UPDATE_DATE,
15 LAST_UPDATED_BY,
16 CREATION_DATE,
17 CREATED_BY,
18 LAST_UPDATE_LOGIN,
19 TITLE,
20 VERSION_NUMBER,
21 PRECEDING_MR_HEADER_ID,
22 PRECEDING_MR_TITLE,
23 PRECEDING_MR_REVISION,
24 CATEGORY_CODE,
25 CATEGORY,
26 SERVICE_TYPE_CODE,
27 SERVICE_TYPE,
28 MR_STATUS_CODE,
29 STATUS,
30 IMPLEMENT_STATUS_CODE,
31 IMPLEMENT_STATUS,
32 REPETITIVE_FLAG,
33 REPETITIVE,
34 SHOW_REPETITIVE_CODE,
35 SHOW_REPETITIVE,
36 WHICHEVER_FIRST_CODE,
37 WHICHEVER_FIRST,
38 COPY_ACCOMPLISHMENT_FLAG,
39 COPY_ACCOMPLISHMENT,
40 PROGRAM_TYPE_CODE,
41 PROGRAM_TYPE,
42 PROGRAM_SUBTYPE_CODE,
43 PROGRAM_SUBTYPE,
44 EFFECTIVE_FROM,
45 EFFECTIVE_TO,
46 REVISION,
47 DESCRIPTION,
48 COMMENTS,
49 SERVICE_REQUEST_TEMPLATE_ID,
50 TYPE_CODE,
51 TYPE,
52 DOWN_TIME,
53 UOM_CODE,
54 UOM,
55 SPACE_CATEGORY_CODE,
56 SPACE_CATEGORY,
57 BILLING_ITEM_ID,
58 BILLING_ORG_ID,
59 BILLING_ITEM,
60 QA_INSPECTION_TYPE_CODE,
61 QA_INSPECTION_TYPE,
62 ATTRIBUTE_CATEGORY,
63 ATTRIBUTE1,
64 ATTRIBUTE2,
65 ATTRIBUTE3,
66 ATTRIBUTE4,
67 ATTRIBUTE5,
68 ATTRIBUTE6,
69 ATTRIBUTE7,
70 ATTRIBUTE8,
71 ATTRIBUTE9,
72 ATTRIBUTE10,
73 ATTRIBUTE11,
74 ATTRIBUTE12,
75 ATTRIBUTE13,
76 ATTRIBUTE14,
77 ATTRIBUTE15,
78 APPLICATION_USG_CODE,
79 AUTO_SIGNOFF_FLAG,
80 COPY_INIT_ACCOMPL_FLAG,
81 COPY_DEFERRALS_FLAG
82 FROM AHL_MR_HEADERS_V
83 WHERE MR_HEADER_ID=c_mr_header_id;
84
85 l_MR_header_rec CurHeaderDet%ROWTYPE;
86 BEGIN
87 IF G_DEBUG='Y' THEN
88 AHL_DEBUG_PUB.enable_debug;
89 END IF;
90
91 OPEN CurHeaderDet(p_x_mr_header_rec.mr_header_id);
92 FETCH CurHeaderDet INTO l_mr_header_rec;
93 CLOSE CurHeaderDet;
94
95 IF p_x_mr_header_rec.OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
96 THEN
97 p_x_mr_header_rec.OBJECT_VERSION_NUMBER:=NULL;
98 ELSE
99 p_x_mr_header_rec.OBJECT_VERSION_NUMBER:=l_mr_header_rec.OBJECT_VERSION_NUMBER;
100 END IF;
101
102 IF p_x_mr_header_rec.TITLE= FND_API.G_MISS_CHAR
103 THEN
104 p_x_mr_header_rec.TITLE:=NULL;
105 ELSIF p_x_mr_header_rec.TITLE IS NULL
106 THEN
107 p_x_mr_header_rec.TITLE:=l_mr_header_rec.TITLE;
108 END IF;
109
110 IF p_x_mr_header_rec.REVISION= FND_API.G_MISS_CHAR
111 THEN
112 p_x_mr_header_rec.REVISION:=NULL;
113 ELSIF p_x_mr_header_rec.REVISION IS NULL
114 THEN
115 p_x_mr_header_rec.REVISION:=l_mr_header_Rec.REVISION;
116 END IF;
117 IF p_x_mr_header_rec.VERSION_NUMBER= FND_API.G_MISS_NUM
118 THEN
119 p_x_mr_header_rec.VERSION_NUMBER:=NULL;
120 ELSIF p_x_mr_header_rec.VERSION_NUMBER IS NULL
121 THEN
122 p_x_mr_header_rec.VERSION_NUMBER:=l_mr_header_Rec.VERSION_NUMBER;
123 END IF;
124
125 IF p_x_mr_header_rec.CATEGORY_CODE= FND_API.G_MISS_CHAR
126 THEN
127 p_x_mr_header_rec.CATEGORY_CODE:=NULL;
128 ELSIF p_x_mr_header_rec.CATEGORY_CODE IS NULL
129 THEN
130 p_x_mr_header_rec.CATEGORY_CODE
131 :=l_mr_header_Rec.CATEGORY_CODE;
132 END IF;
133 IF p_x_mr_header_rec.CATEGORY= FND_API.G_MISS_CHAR
134 THEN
135 p_x_mr_header_rec.CATEGORY:=NULL;
136 ELSIF p_x_mr_header_rec.CATEGORY IS NULL
137 THEN
138 p_x_mr_header_rec.CATEGORY
139 :=l_mr_header_Rec.CATEGORY;
140 END IF;
141
142 IF p_x_mr_header_rec.PROGRAM_TYPE_CODE= FND_API.G_MISS_CHAR
143 THEN
144 p_x_mr_header_rec.PROGRAM_TYPE_CODE:=NULL;
145 ELSIF p_x_mr_header_rec.PROGRAM_TYPE_CODE IS NULL
146 THEN
147 p_x_mr_header_rec.PROGRAM_TYPE_CODE
148 :=l_mr_header_Rec.PROGRAM_TYPE_CODE;
149 END IF;
150 IF p_x_mr_header_rec.PROGRAM_TYPE= FND_API.G_MISS_CHAR
151 THEN
152 p_x_mr_header_rec.PROGRAM_TYPE:=NULL;
153 ELSIF p_x_mr_header_rec.PROGRAM_TYPE IS NULL
154 THEN
155 p_x_mr_header_rec.PROGRAM_TYPE
156 :=l_mr_header_Rec.PROGRAM_TYPE;
157 END IF;
158
159 IF p_x_mr_header_rec.PROGRAM_SUBTYPE= FND_API.G_MISS_CHAR
160 THEN
161 p_x_mr_header_rec.PROGRAM_SUBTYPE:=NULL;
162 ELSIF p_x_mr_header_rec.PROGRAM_SUBTYPE IS NULL
163 THEN
164 p_x_mr_header_rec.PROGRAM_SUBTYPE:=
165 l_mr_header_Rec.PROGRAM_SUBTYPE;
166 END IF;
167
168 IF p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE= FND_API.G_MISS_CHAR
169 THEN
170 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=NULL;
171 ELSIF p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE IS NULL
172 THEN
173 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=l_mr_header_Rec.PROGRAM_SUBTYPE_CODE;
174 END IF;
175
176
177 IF p_x_mr_header_rec.SERVICE_TYPE_CODE= FND_API.G_MISS_CHAR
178 THEN
179 p_x_mr_header_rec.SERVICE_TYPE_CODE:=NULL;
180 ELSIF p_x_mr_header_rec.SERVICE_TYPE_CODE IS NULL
181 THEN
182 p_x_mr_header_rec.SERVICE_TYPE_CODE:=l_mr_header_Rec.SERVICE_TYPE_CODE;
183 END IF;
184 IF p_x_mr_header_rec.SERVICE_TYPE= FND_API.G_MISS_CHAR
185 THEN
186 p_x_mr_header_rec.SERVICE_TYPE:=NULL;
187 ELSIF p_x_mr_header_rec.SERVICE_TYPE IS NULL
188 THEN
189 p_x_mr_header_rec.SERVICE_TYPE:=l_mr_header_Rec.SERVICE_TYPE;
190 END IF;
191
192 IF p_x_mr_header_rec.MR_STATUS_CODE= FND_API.G_MISS_CHAR
193 THEN
194 p_x_mr_header_rec.MR_STATUS_CODE:=NULL;
195 ELSIF p_x_mr_header_rec.MR_STATUS_CODE IS NULL
196 THEN
197 p_x_mr_header_rec.MR_STATUS_CODE:=l_mr_header_Rec.MR_STATUS_CODE;
198 END IF;
199 IF p_x_mr_header_rec.IMPLEMENT_STATUS_CODE= FND_API.G_MISS_CHAR
200 THEN
201 p_x_mr_header_rec.IMPLEMENT_STATUS_CODE:=NULL;
202 ELSIF p_x_mr_header_rec.IMPLEMENT_STATUS_CODE IS NULL
203 THEN
204 p_x_mr_header_rec.IMPLEMENT_STATUS_CODE:=l_mr_header_Rec.IMPLEMENT_STATUS_CODE;
205 END IF;
206 IF p_x_mr_header_rec.IMPLEMENT_STATUS= FND_API.G_MISS_CHAR
207 THEN
208 p_x_mr_header_rec.IMPLEMENT_STATUS:=NULL;
209 ELSIF p_x_mr_header_rec.IMPLEMENT_STATUS IS NULL
210 THEN
211 p_x_mr_header_rec.IMPLEMENT_STATUS
212 :=l_mr_header_Rec.IMPLEMENT_STATUS;
213 END IF;
214 IF p_x_mr_header_rec.EFFECTIVE_FROM=FND_API.G_MISS_DATE
215 THEN
216 p_x_mr_header_rec.EFFECTIVE_FROM:=NULL;
217 ELSIF p_x_mr_header_rec.EFFECTIVE_FROM IS NULL
218 THEN
219 p_x_mr_header_rec.EFFECTIVE_FROM:=l_mr_header_Rec.EFFECTIVE_FROM;
220 END IF;
221 IF p_x_mr_header_rec.EFFECTIVE_TO=FND_API.G_MISS_DATE
222 THEN
223 p_x_mr_header_rec.EFFECTIVE_TO:=NULL;
224 ELSIF p_x_mr_header_rec.EFFECTIVE_TO IS NULL
225 THEN
226 p_x_mr_header_rec.EFFECTIVE_TO:=l_mr_header_Rec.EFFECTIVE_TO;
227 END IF;
228 IF p_x_mr_header_rec.REPETITIVE_FLAG= FND_API.G_MISS_CHAR
229 THEN
230 p_x_mr_header_rec.REPETITIVE_FLAG:=NULL;
231 ELSIF p_x_mr_header_rec.REPETITIVE_FLAG IS NULL
232 THEN
233 p_x_mr_header_rec.REPETITIVE_FLAG:=l_mr_header_Rec.REPETITIVE_FLAG;
234 END IF;
235 IF p_x_mr_header_rec.REPETITIVE= FND_API.G_MISS_CHAR
236 THEN
237 p_x_mr_header_rec.REPETITIVE:=NULL;
238 ELSIF p_x_mr_header_rec.REPETITIVE IS NULL
239 THEN
240 p_x_mr_header_rec.REPETITIVE:=l_mr_header_Rec.REPETITIVE;
241 END IF;
242
243 IF p_x_mr_header_rec.SHOW_REPETITIVE_CODE= FND_API.G_MISS_CHAR
244 THEN
245 p_x_mr_header_rec.SHOW_REPETITIVE_CODE:=NULL;
246 ELSIF p_x_mr_header_rec.SHOW_REPETITIVE_CODE IS NULL
247 THEN
248 p_x_mr_header_rec.SHOW_REPETITIVE_CODE:=l_mr_header_Rec.SHOW_REPETITIVE_CODE;
249 END IF;
250
251 IF p_x_mr_header_rec.SHOW_REPETITIVE= FND_API.G_MISS_CHAR
252 THEN
253 p_x_mr_header_rec.SHOW_REPETITIVE:=NULL;
254 ELSIF p_x_mr_header_rec.SHOW_REPETITIVE IS NULL
255 THEN
256 p_x_mr_header_rec.SHOW_REPETITIVE:=l_mr_header_Rec.SHOW_REPETITIVE;
257 END IF;
258 IF p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG= FND_API.G_MISS_CHAR
259 THEN
260 p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG:=NULL;
261 ELSIF p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG IS NULL
262 THEN
263 p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG:=l_mr_header_Rec.COPY_ACCOMPLISHMENT_FLAG;
264 END IF;
265
266 IF p_x_mr_header_rec.COPY_ACCOMPLISHMENT= FND_API.G_MISS_CHAR
267 THEN
268 p_x_mr_header_rec.COPY_ACCOMPLISHMENT:=NULL;
269 ELSIF p_x_mr_header_rec.COPY_ACCOMPLISHMENT IS NULL
270 THEN
271 p_x_mr_header_rec.COPY_ACCOMPLISHMENT:=l_mr_header_Rec.COPY_ACCOMPLISHMENT;
272 END IF;
273
274 IF p_x_mr_header_rec.WHICHEVER_FIRST_CODE= FND_API.G_MISS_CHAR
275 THEN
276 p_x_mr_header_rec.WHICHEVER_FIRST_CODE:=NULL;
277 ELSIF p_x_mr_header_rec.WHICHEVER_FIRST_CODE IS NULL
278 THEN
279 p_x_mr_header_rec.WHICHEVER_FIRST_CODE:=l_mr_header_Rec.WHICHEVER_FIRST_CODE;
280 END IF;
281
282 IF p_x_mr_header_rec.WHICHEVER_FIRST= FND_API.G_MISS_CHAR
283 THEN
284 p_x_mr_header_rec.WHICHEVER_FIRST:=NULL;
285 ELSIF p_x_mr_header_rec.WHICHEVER_FIRST IS NULL
286 THEN
287 p_x_mr_header_rec.WHICHEVER_FIRST:=l_mr_header_Rec.WHICHEVER_FIRST;
288 END IF;
289
290 IF p_x_mr_header_rec.PRECEDING_MR_HEADER_ID= FND_API.G_MISS_NUM
291 THEN
292 p_x_mr_header_rec.PRECEDING_MR_HEADER_ID:=NULL;
293 ELSIF p_x_mr_header_rec.PRECEDING_MR_HEADER_ID IS NULL
294 THEN
295 p_x_mr_header_rec.PRECEDING_MR_HEADER_ID:=l_mr_header_Rec.PRECEDING_MR_HEADER_ID;
296 END IF;
297
298 IF p_x_mr_header_rec.PRECEDING_MR_TITLE= FND_API.G_MISS_CHAR
299 THEN
300 p_x_mr_header_rec.PRECEDING_MR_TITLE:=NULL;
301 ELSIF p_x_mr_header_rec.PRECEDING_MR_TITLE IS NULL
302 THEN
303 p_x_mr_header_rec.PRECEDING_MR_TITLE:=l_mr_header_Rec.PRECEDING_MR_TITLE;
304 END IF;
305
306 IF p_x_mr_header_rec.PRECEDING_MR_REVISION= FND_API.G_MISS_CHAR
307 THEN
308 p_x_mr_header_rec.PRECEDING_MR_REVISION:=NULL;
309 ELSIF p_x_mr_header_rec.PRECEDING_MR_REVISION IS NULL
310 THEN
311 p_x_mr_header_rec.PRECEDING_MR_REVISION:=l_mr_header_Rec.PRECEDING_MR_REVISION;
312 END IF;
313
314 IF p_x_mr_header_rec.SERVICE_REQUEST_TEMPLATE_ID= FND_API.G_MISS_NUM
315 THEN
316 p_x_mr_header_rec.SERVICE_REQUEST_TEMPLATE_ID:=NULL;
317 ELSIF p_x_mr_header_rec.SERVICE_REQUEST_TEMPLATE_ID IS NULL
318 THEN
319 p_x_mr_header_rec.SERVICE_REQUEST_TEMPLATE_ID:=l_mr_header_Rec.SERVICE_REQUEST_TEMPLATE_ID;
320 END IF;
321
322 IF p_x_mr_header_rec.DOWN_TIME= FND_API.G_MISS_NUM
323 THEN
324 p_x_mr_header_rec.DOWN_TIME:=NULL;
325 ELSIF p_x_mr_header_rec.DOWN_TIME IS NULL
326 THEN
327 p_x_mr_header_rec.DOWN_TIME:=l_mr_header_Rec.DOWN_TIME;
328 END IF;
329
330 IF p_x_mr_header_rec.TYPE_CODE= FND_API.G_MISS_CHAR
331 THEN
332 p_x_mr_header_rec.TYPE_CODE:=NULL;
333 ELSIF p_x_mr_header_rec.TYPE_CODE IS NULL
334 THEN
335 p_x_mr_header_rec.TYPE_CODE:=l_mr_header_Rec.TYPE_CODE;
336 END IF;
337
338 IF p_x_mr_header_rec.AUTO_SIGNOFF_FLAG= FND_API.G_MISS_CHAR
339 THEN
340 p_x_mr_header_rec.AUTO_SIGNOFF_FLAG:=NULL;
341 ELSIF p_x_mr_header_rec.AUTO_SIGNOFF_FLAG IS NULL
342 THEN
343 p_x_mr_header_rec.AUTO_SIGNOFF_FLAG:=l_mr_header_Rec.AUTO_SIGNOFF_FLAG;
344 END IF;
345
346 IF p_x_mr_header_rec.COPY_INIT_ACCOMPL_FLAG= FND_API.G_MISS_CHAR
347 THEN
348 p_x_mr_header_rec.COPY_INIT_ACCOMPL_FLAG:=NULL;
349 ELSIF p_x_mr_header_rec.COPY_INIT_ACCOMPL_FLAG IS NULL
350 THEN
351 p_x_mr_header_rec.COPY_INIT_ACCOMPL_FLAG:=l_mr_header_Rec.COPY_INIT_ACCOMPL_FLAG;
352 END IF;
353
354 IF p_x_mr_header_rec.COPY_DEFERRALS_FLAG= FND_API.G_MISS_CHAR
355 THEN
356 p_x_mr_header_rec.COPY_DEFERRALS_FLAG:=NULL;
357 ELSIF p_x_mr_header_rec.COPY_DEFERRALS_FLAG IS NULL
358 THEN
359 p_x_mr_header_rec.COPY_DEFERRALS_FLAG:=l_mr_header_Rec.COPY_DEFERRALS_FLAG;
360 END IF;
361
362 IF p_x_mr_header_rec.UOM_CODE= FND_API.G_MISS_CHAR
363 THEN
364 p_x_mr_header_rec.UOM_CODE:=NULL;
365 ELSIF p_x_mr_header_rec.UOM_CODE IS NULL
366 THEN
367 p_x_mr_header_rec.UOM_CODE:=l_mr_header_Rec.UOM_CODE;
368 END IF;
369
370 IF p_x_mr_header_rec.DESCRIPTION= FND_API.G_MISS_CHAR
371 THEN
372 p_x_mr_header_rec.DESCRIPTION:=NULL;
373 ELSIF p_x_mr_header_rec.DESCRIPTION IS NULL
374 THEN
375 p_x_mr_header_rec.DESCRIPTION:=l_mr_header_Rec.DESCRIPTION;
376 END IF;
377 IF p_x_mr_header_rec.COMMENTS= FND_API.G_MISS_CHAR
378 THEN
382 p_x_mr_header_rec.COMMENTS:=l_mr_header_Rec.COMMENTS;
379 p_x_mr_header_rec.COMMENTS:=NULL;
380 ELSIF p_x_mr_header_rec.COMMENTS IS NULL
381 THEN
383 END IF;
384 IF p_x_mr_header_rec.ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
385 THEN
386 p_x_mr_header_rec.ATTRIBUTE_CATEGORY:=NULL;
387 ELSIF p_x_mr_header_rec.ATTRIBUTE_CATEGORY IS NULL
388 THEN
389 p_x_mr_header_rec.ATTRIBUTE_CATEGORY:=l_mr_header_Rec.ATTRIBUTE_CATEGORY;
390 END IF;
391
392 IF p_x_mr_header_rec.ATTRIBUTE1= FND_API.G_MISS_CHAR
393 THEN
394 p_x_mr_header_rec.ATTRIBUTE1:=NULL;
395 ELSIF p_x_mr_header_rec.ATTRIBUTE1 IS NULL
396 THEN
397 p_x_mr_header_rec.ATTRIBUTE1:=l_mr_header_Rec.ATTRIBUTE1;
398 END IF;
399
400 IF p_x_mr_header_rec.ATTRIBUTE2= FND_API.G_MISS_CHAR
401 THEN
402 p_x_mr_header_rec.ATTRIBUTE2:=NULL;
403 ELSIF p_x_mr_header_rec.ATTRIBUTE2 IS NULL
404 THEN
405 p_x_mr_header_rec.ATTRIBUTE2:=l_mr_header_Rec.ATTRIBUTE2;
406 END IF;
407
408 IF p_x_mr_header_rec.ATTRIBUTE3= FND_API.G_MISS_CHAR
409 THEN
410 p_x_mr_header_rec.ATTRIBUTE3:=NULL;
411 ELSIF p_x_mr_header_rec.ATTRIBUTE3 IS NULL
412 THEN
413 p_x_mr_header_rec.ATTRIBUTE3:=l_mr_header_Rec.ATTRIBUTE3;
414 END IF;
415
416 IF p_x_mr_header_rec.ATTRIBUTE4= FND_API.G_MISS_CHAR
417 THEN
418 p_x_mr_header_rec.ATTRIBUTE4:=NULL;
419 ELSIF p_x_mr_header_rec.ATTRIBUTE4 IS NULL
420 THEN
421 p_x_mr_header_rec.ATTRIBUTE4:=l_mr_header_Rec.ATTRIBUTE4;
422 END IF;
423
424 IF p_x_mr_header_rec.ATTRIBUTE5= FND_API.G_MISS_CHAR
425 THEN
426 p_x_mr_header_rec.ATTRIBUTE5:=NULL;
427 ELSIF p_x_mr_header_rec.ATTRIBUTE5 IS NULL
428 THEN
429 p_x_mr_header_rec.ATTRIBUTE5:=l_mr_header_Rec.ATTRIBUTE5;
430 END IF;
431
432 IF p_x_mr_header_rec.ATTRIBUTE6= FND_API.G_MISS_CHAR
433 THEN
434 p_x_mr_header_rec.ATTRIBUTE6:=NULL;
435 ELSIF p_x_mr_header_rec.ATTRIBUTE6 IS NULL
436 THEN
437 p_x_mr_header_rec.ATTRIBUTE6:=l_mr_header_Rec.ATTRIBUTE6;
438 END IF;
439
440 IF p_x_mr_header_rec.ATTRIBUTE7= FND_API.G_MISS_CHAR
441 THEN
442 p_x_mr_header_rec.ATTRIBUTE7:=NULL;
443 ELSIF p_x_mr_header_rec.ATTRIBUTE7 IS NULL
444 THEN
445 p_x_mr_header_rec.ATTRIBUTE7 :=l_mr_header_Rec.ATTRIBUTE7;
446 END IF;
447
448 IF p_x_mr_header_rec.ATTRIBUTE8= FND_API.G_MISS_CHAR
449 THEN
450 p_x_mr_header_rec.ATTRIBUTE8:=NULL;
451 ELSIF p_x_mr_header_rec.ATTRIBUTE8 IS NULL
452 THEN
453 p_x_mr_header_rec.ATTRIBUTE8:=l_mr_header_Rec.ATTRIBUTE8;
454 END IF;
455
456 IF p_x_mr_header_rec.ATTRIBUTE9= FND_API.G_MISS_CHAR
457 THEN
458 p_x_mr_header_rec.ATTRIBUTE9:=NULL;
459 ELSIF p_x_mr_header_rec.ATTRIBUTE9 IS NULL
460 THEN
461 p_x_mr_header_rec.ATTRIBUTE9:=l_mr_header_Rec.ATTRIBUTE9;
462 END IF;
463
464 IF p_x_mr_header_rec.ATTRIBUTE10= FND_API.G_MISS_CHAR
465 THEN
466 p_x_mr_header_rec.ATTRIBUTE10:=NULL;
467 ELSIF p_x_mr_header_rec.ATTRIBUTE10 IS NULL
468 THEN
469 p_x_mr_header_rec.ATTRIBUTE10:=l_mr_header_Rec.ATTRIBUTE10;
470 END IF;
471 IF p_x_mr_header_rec.ATTRIBUTE11= FND_API.G_MISS_CHAR
472 THEN
473 p_x_mr_header_rec.ATTRIBUTE11:=NULL;
474 ELSIF p_x_mr_header_rec.ATTRIBUTE11 IS NULL
475 THEN
476 p_x_mr_header_rec.ATTRIBUTE11:=l_mr_header_Rec.ATTRIBUTE11;
477 END IF;
478
479 IF p_x_mr_header_rec.ATTRIBUTE12= FND_API.G_MISS_CHAR
480 THEN
481 p_x_mr_header_rec.ATTRIBUTE12:=NULL;
482 ELSIF p_x_mr_header_rec.ATTRIBUTE12 IS NULL
483 THEN
484 p_x_mr_header_rec.ATTRIBUTE12:=l_mr_header_Rec.ATTRIBUTE12;
485 END IF;
486
487 IF p_x_mr_header_rec.ATTRIBUTE13= FND_API.G_MISS_CHAR
488 THEN
489 p_x_mr_header_rec.ATTRIBUTE13:=NULL;
490 ELSIF p_x_mr_header_rec.ATTRIBUTE13 IS NULL
491 THEN
492 p_x_mr_header_rec.ATTRIBUTE13:=l_mr_header_Rec.ATTRIBUTE13;
493 END IF;
494
495 IF p_x_mr_header_rec.ATTRIBUTE14= FND_API.G_MISS_CHAR
496 THEN
497 p_x_mr_header_rec.ATTRIBUTE14:=NULL;
498 ELSIF p_x_mr_header_rec.ATTRIBUTE14 IS NULL
499 THEN
500 p_x_mr_header_rec.ATTRIBUTE14
501 :=l_mr_header_Rec.ATTRIBUTE14;
502 END IF;
503
504 IF p_x_mr_header_rec.ATTRIBUTE15= FND_API.G_MISS_CHAR
505 THEN
506 p_x_mr_header_rec.ATTRIBUTE15:=NULL;
507 ELSIF p_x_mr_header_rec.ATTRIBUTE15 IS NULL
508 THEN
509 p_x_mr_header_rec.ATTRIBUTE15:=l_mr_header_Rec.ATTRIBUTE15;
510 END IF;
514 IF p_x_mr_header_rec.billing_item= FND_API.G_MISS_CHAR
511
512
513 --Billing Item
515 THEN
516 p_x_mr_header_rec.billing_item:=NULL;
517 ELSIF p_x_mr_header_rec.billing_item IS NULL
518 THEN
519 p_x_mr_header_rec.billing_item:=l_mr_header_Rec.billing_item;
520 END IF;
521 --Billing Item Id
522 IF p_x_mr_header_rec.billing_item_id= FND_API.G_MISS_NUM
523 THEN
524 p_x_mr_header_rec.billing_item_id:=NULL;
525 ELSIF p_x_mr_header_rec.billing_item_id IS NULL
526 THEN
527 p_x_mr_header_rec.billing_item_id:=l_mr_header_Rec.billing_item_id;
528 END IF;
529
530 --qa_inspection_type
531 IF p_x_mr_header_rec.qa_inspection_type= FND_API.G_MISS_CHAR
532 THEN
533 p_x_mr_header_rec.qa_inspection_type:=NULL;
534 ELSIF p_x_mr_header_rec.qa_inspection_type IS NULL
535 THEN
536 p_x_mr_header_rec.qa_inspection_type:=l_mr_header_Rec.qa_inspection_type;
537 END IF;
538
539 IF p_x_mr_header_rec.qa_inspection_type_code= FND_API.G_MISS_CHAR
540 THEN
541 p_x_mr_header_rec.qa_inspection_type_code:=NULL;
542 ELSIF p_x_mr_header_rec.qa_inspection_type_code IS NULL
543 THEN
544 p_x_mr_header_rec.qa_inspection_type_code:=l_mr_header_Rec.qa_inspection_type_code;
545 END IF;
546
547 --space_category_code
548 IF p_x_mr_header_rec.space_category_code= FND_API.G_MISS_CHAR
549 THEN
550 p_x_mr_header_rec.space_category_code:=NULL;
551 ELSIF p_x_mr_header_rec.space_category_code IS NULL
552 THEN
553 p_x_mr_header_rec.space_category_code:=l_mr_header_Rec.space_category_code;
554 END IF;
555
556 IF p_x_mr_header_rec.space_category= FND_API.G_MISS_CHAR
557 THEN
558 p_x_mr_header_rec.space_category:=NULL;
559 ELSIF p_x_mr_header_rec.space_category IS NULL
560 THEN
561 p_x_mr_header_rec.space_category:=l_mr_header_Rec.space_category;
562 END IF;
563 END;
564
565
566 PROCEDURE CHECK_LOOKUP_CODE
567 (
568 x_return_status OUT NOCOPY VARCHAR2,
569 p_lookup_code IN VARCHAR2,
570 p_lookup_TYPE IN VARCHAR2
571 )
572 as
573 CURSOR get_lookup_type_code(c_lookup_code VARCHAR2,c_lookup_type VARCHAR2)
574 IS
575 SELECT lookup_code
576 FROM FND_LOOKUP_VALUES_VL
577 WHERE lookup_code = c_lookup_code
578 AND lookup_type = c_lookup_type
579 AND sysdate between nvl(start_date_active,sysdate)
580 AND nvl(end_date_active,sysdate);
581
582 l_lookup_code VARCHAR2(30):=null;
583 begin
584 OPEN get_lookup_type_code(p_lookup_code,p_lookup_type);
585 FETCH get_lookup_type_code INTO l_lookup_code;
586 IF get_lookup_type_code%NOTFOUND
587 THEN
588 x_return_Status:= FND_API.G_RET_STS_UNEXP_ERROR;
589 END IF;
590 CLOSE get_lookup_type_code;
591 end;
592
593 PROCEDURE TRANSLATE_VALUE_ID
594 (
595 x_return_status OUT NOCOPY VARCHAR2,
596 p_x_mr_header_rec IN OUT NOCOPY AHL_FMP_MR_HEADER_PVT.MR_HEADER_REC
597 )
598 as
599 CURSOR get_lookup_meaning_to_code(c_lookup_type VARCHAR2,c_meaning VARCHAR2)
600 IS
601 SELECT lookup_code
602 FROM FND_LOOKUP_VALUES_VL
603 WHERE lookup_type= c_lookup_type
604 AND upper(meaning) =upper(c_meaning)
605 AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate);
606
607 CURSOR get_mr_title(c_mr_header_id NUMBER)
608 IS
609 SELECT title,object_version_number
610 FROM AHL_MR_HEADERS_APP_V
611 WHERE mr_header_id= c_mr_header_id;
612
613 CURSOR get_mr_header(c_title VARCHAR2)
614 IS
615 SELECT mr_header_id,title,object_version_number
616 FROM AHL_MR_HEADERS_APP_V
617 WHERE UPPER(TITLE)=UPPER(LTRIM(RTRIM(c_title)))
618 AND trunc(nvl(effective_to,sysdate+1)) >trunc(sysdate);
619
620 CURSOR get_titlecount(c_title VARCHAR2)
621 IS
622 SELECT COUNT(*)
623 FROM AHL_MR_HEADERS_APP_V
624 WHERE UPPER(TITLE)=UPPER(c_title)
625 AND trunc(nvl(effective_to,sysdate+1)) >trunc(sysdate);
626
627 CURSOR get_prec_mrheader_info(c_mr_header_id NUMBER)
628 IS
629 SELECT mr_header_id,title,object_version_number,revision
630 FROM AHL_MR_HEADERS_APP_V
631 WHERE MR_HEADER_ID=C_MR_HEADER_ID
632 AND trunc(nvl(effective_to,sysdate+1)) >trunc(sysdate);
633
634 CURSOR get_mrItemId(c_billing_item VARCHAR2)
635 IS
636 --AMSRINIV. Bug 4916286. Removing 'upper' to improve performance.
637
638 SELECT inventory_item_id
639 FROM mtl_system_items_kfv
640 WHERE STOCK_ENABLED_FLAG='N'
641 AND MTL_TRANSACTIONS_ENABLED_FLAG='N'
642 AND concatenated_segments= ltrim(rtrim(c_billing_item))
643 and rownum <2;
644
645 /*SELECT inventory_item_id
646 FROM mtl_system_items_kfv
647 WHERE STOCK_ENABLED_FLAG='N'
648 AND MTL_TRANSACTIONS_ENABLED_FLAG='N'
649 AND upper(concatenated_segments)= upper(ltrim(rtrim(c_billing_item)))
650 and rownum <2;*/
651
652 CURSOR get_qainspection_type_code(p_qa_inspection_type_desc VARCHAR2)
653 IS
657 AND upper(description) = upper(ltrim(rtrim(p_qa_inspection_type_desc)));
654 SELECT short_code
655 FROM qa_char_value_lookups_v
656 WHERE char_id = 87
658
659 l_prec_mr_info get_prec_mrheader_info%rowtype;
660
661 l_check_impl_status NUMBER;
662 l_title VARCHAR2(255);
663 l_title_counter NUMBER:=0;
664 l_lookup_code VARCHAR2(30);
665 l_api_name CONSTANT VARCHAR2(30) := 'TRANSLATE_VALUE_ID';
666 l_api_version CONSTANT NUMBER := 1.0;
667 l_num_rec NUMBER;
668 l_msg_count NUMBER;
669 l_msg_data VARCHAR2(2000);
670 l_return_status VARCHAR2(1);
671 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
672 l_mrItemId NUMBER:=0;
673 l_lookup_var varchar2(1);
674 l_object_version_number NUMBER;
675 l_check_flag VARCHAR2(1):='Y';
676 l_program_type_code_ind VARCHAR2(1):='N';
677 BEGIN
678
679 IF G_DEBUG='Y' THEN
680 AHL_DEBUG_PUB.enable_debug;
681 AHL_DEBUG_PUB.debug('Trans TYPE CODE '||p_x_mr_header_rec.TYPE_CODE,'+HEADERS+');
682 END IF;
683
684 x_return_status:=FND_API.G_RET_STS_SUCCESS;
685
686 IF p_x_mr_header_rec.PROGRAM_TYPE IS NULL OR p_x_mr_header_rec.PROGRAM_TYPE=FND_API.G_MISS_CHAR
687 THEN
688 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGTYPE_NULL');
689 FND_MSG_PUB.ADD;
690 l_check_flag:='N';
691 END IF;
692
693
694 IF l_check_flag='Y'
695 THEN
696 OPEN get_lookup_meaning_to_code('AHL_FMP_MR_PROGRAM_TYPE',p_x_mr_header_rec.PROGRAM_TYPE);
697 FETCH get_lookup_meaning_to_code INTO l_lookup_code;
698 IF get_lookup_meaning_to_code%NOTFOUND
699 THEN
700 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGTYPE_INVALID');
701 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_header_rec.PROGRAM_TYPE,false);
702 FND_MSG_PUB.ADD;
703 l_check_flag:='N';
704 ELSE
705 l_program_type_code_ind:='N';
706 p_x_mr_header_rec.PROGRAM_TYPE_CODE:=l_lookup_code;
707 l_check_flag:='Y';
708 END IF;
709
710 CLOSE get_lookup_meaning_to_code;
711 END IF;
712
713
714 -- Program Sub type
715
716 IF (p_x_mr_header_rec.PROGRAM_SUBTYPE IS NULL OR
717 p_x_mr_header_rec.PROGRAM_SUBTYPE=FND_API.G_MISS_CHAR)
718 THEN
719 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=FND_API.G_MISS_CHAR;
720 ELSE
721 IF l_check_flag='Y'
722 THEN
723
724 OPEN get_lookup_meaning_to_code('AHL_FMP_MR_PROGRAM_SUBTYPE',
725 p_x_mr_header_rec.PROGRAM_SUBTYPE);
726 FETCH get_lookup_meaning_to_code INTO l_lookup_code;
727
728 IF get_lookup_meaning_to_code%NOTFOUND
729 THEN
730 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGSUBTYPE_INVALID');
731 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE,false);
732 FND_MSG_PUB.ADD;
733 ELSE
734 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=l_lookup_code;
735 END IF;
736
737 CLOSE get_lookup_meaning_to_code;
738 END IF;
739 END IF;
740
741
742 --Billing Item Id
743
744
745 IF (p_x_mr_header_rec.BILLING_ITEM IS NULL OR p_x_mr_header_rec.BILLING_ITEM=FND_API.G_MISS_CHAR)
746 THEN
747 p_x_mr_header_rec.BILLING_ITEM:=FND_API.G_MISS_CHAR;
748 ELSE
749 IF l_check_flag='Y'
750 THEN
751
752 OPEN get_mrItemId(p_x_mr_header_rec.BILLING_ITEM);
753 FETCH get_mrItemId INTO p_x_mr_header_rec.BILLING_ITEM_ID;
754
755 IF get_mrItemId%NOTFOUND
756 THEN
757 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INVALID_BITEM');
758 FND_MESSAGE.SET_TOKEN('BILLING_ITEM',p_x_mr_header_rec.BILLING_ITEM,false);
759 FND_MSG_PUB.ADD;
760 END IF;
761
762 CLOSE get_mrItemId;
763 END IF;
764 END IF;
765 --Implent Status and QA INSPECTION TYPE
766 --Chack for the Valid QAInspection Type
767
768 IF (p_x_mr_header_rec.QA_INSPECTION_TYPE IS NULL
769 OR p_x_mr_header_rec.QA_INSPECTION_TYPE=FND_API.G_MISS_CHAR)
770 THEN
771 p_x_mr_header_rec.QA_INSPECTION_TYPE:=FND_API.G_MISS_CHAR;
772 ELSE
773 IF l_check_flag='Y'
774 THEN
775
776 OPEN get_qainspection_type_code(p_x_mr_header_rec.QA_INSPECTION_TYPE);
777 FETCH get_qainspection_type_code INTO p_x_mr_header_rec.QA_INSPECTION_TYPE_CODE;
778
779 IF get_qainspection_type_code%NOTFOUND
780 THEN
781 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_QA_INSP');
785 ELSE
782 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_header_rec.QA_INSPECTION_TYPE,false);
783 FND_MSG_PUB.ADD;
784 /* Commented the Qa inspection type depedency on IMPLEMENTAT_STATUS_CODE as per ER#3822674
786 IF p_x_mr_header_rec.IMPLEMENT_STATUS_CODE IS NOT NULL
787 OR p_x_mr_header_rec.IMPLEMENT_STATUS_CODE <>FND_API.G_MISS_CHAR
788 THEN
789 IF p_x_mr_header_rec.IMPLEMENT_STATUS_CODE = 'OPTIONAL_DO_NOT_IMPLEMENT'
790 AND (p_x_mr_header_rec.qa_inspection_type IS NOT NULL
791 OR p_x_mr_header_rec.qa_inspection_type <>FND_API.G_MISS_CHAR)
792 THEN
793 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_IMPL_QA');
794 FND_MSG_PUB.ADD;
795 END IF;
796 End if;
797 */
798
799 END IF;
800 CLOSE get_qainspection_type_code;
801 END IF;
802 END IF;
803
804 -- IF PROGRAM TYPE IS NON-ROUTINE THEN IMPLMENTATION STATUS HAS TO BE OPTIONAL DO NOT IMPLEMENT
805
806 IF p_x_mr_header_rec.PROGRAM_TYPE_CODE= 'NON-ROUTINE' AND
807 p_x_mr_header_rec.IMPLEMENT_STATUS_CODE <> 'OPTIONAL_DO_NOT_IMPLEMENT'
808 THEN
809 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_IMPL_AND_PROGRAM_TYPE');
810 FND_MSG_PUB.ADD;
811 END IF;
812
813 IF p_x_mr_header_rec.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT' AND
814 p_x_mr_header_rec.dml_operation='U'
815 THEN
816 Select count(*) INTO l_check_impl_status
817 From AHL_MR_HEADERS_B
818 WHERE Implement_status_code = 'OPTIONAL_DO_NOT_IMPLEMENT'
819 AND MR_HEADER_ID=p_x_mr_header_rec.MR_HEADER_ID;
820 IF nvl(l_check_impl_status,0) >0
821 THEN
822 l_check_impl_status:=0;
823
824 Select count(*) INTO l_check_impl_status
825 From AHL_MR_VISIT_TYPES
826 WHERE MR_HEADER_ID=p_x_mr_header_rec.MR_HEADER_ID;
827
828 IF nvl(l_check_impl_status,0) >0
829 THEN
830 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_VTYPE_EXIST');
831 FND_MSG_PUB.ADD;
832 -- Cannot modify if implementation status from unplanned/optional do not implement to some thing else if visit types are defined.
833 END IF;
834 END IF;
835 END IF;
836
837
838 -- Preceding MR_header_id
839
840 IF p_x_mr_header_rec.PRECEDING_MR_TITLE IS NOT NULL AND p_x_mr_header_rec.PRECEDING_MR_TITLE<>FND_API.G_MISS_CHAR
841 THEN
842 OPEN get_titlecount(p_x_mr_header_rec.PRECEDING_MR_TITLE);
843 FETCH get_titlecount INTO l_title_counter;
844 Close get_titlecount;
845
846 IF nvl(l_title_counter,0)=0
847 THEN
848 IF G_DEBUG='Y' THEN
849 AHL_DEBUG_PUB.debug( 'PRECEDING MR_TITLE COUNTER');
850 END IF;
851 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PREC_MR_ID_INVALID');
852 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_header_rec.PRECEDING_MR_TITLE,false);
853 FND_MSG_PUB.ADD;
854 ELSIF nvl(l_title_counter,0)=1
855 THEN
856 OPEN get_mr_header(p_x_mr_header_rec.PRECEDING_MR_TITLE);
857
858 FETCH get_mr_header INTO p_x_mr_header_rec.PRECEDING_MR_HEADER_ID,l_title,l_object_version_number;
859
860 IF get_mr_header%NOTFOUND
861 THEN
862 IF G_DEBUG='Y' THEN
863 AHL_DEBUG_PUB.debug(' Preceding MRHEADER_ID NOT FOUND2','+HEADERS+');
864 END IF;
865 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PREC_MR_ID_INVALID');
866 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_header_rec.PRECEDING_MR_TITLE,false);
867 FND_MSG_PUB.ADD;
868 END IF;
869 Close get_mr_header;
870 ELSIF NVL(l_title_counter,0)>1
871 THEN
872 OPEN get_prec_mrheader_info(p_x_mr_header_rec.PRECEDING_MR_HEADER_ID);
873 FETCH get_prec_mrheader_info INTO l_prec_mr_info;
874
875 IF G_DEBUG='Y' THEN
876 AHL_DEBUG_PUB.debug( 'PRECEDING .. MR>1 '||p_x_mr_header_rec.PRECEDING_MR_HEADER_ID,'+HEADERS+');
877 END IF;
878
879 IF get_prec_mrheader_info%NOTFOUND
880 THEN
881 IF G_DEBUG='Y' THEN
882 AHL_DEBUG_PUB.debug('Preceding mr title err','+HEADERS+');
883 END IF;
884
885 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PREC_MR_ID_INVALID');
889 ELSIF (l_prec_mr_info.title<>p_x_mr_header_rec.PRECEDING_MR_TITLE
886 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_header_rec.PRECEDING_MR_TITLE,false);
887 FND_MSG_PUB.ADD;
888
890 and l_prec_mr_info.REVISION<>p_x_mr_header_rec.PRECEDING_MR_REVISION)
891 THEN
892 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PREC_MR_ID_SELECT_LOV');
893 FND_MSG_PUB.ADD;
894 END IF;
895
896 Close get_prec_mrheader_info;
897 ELSIF NVL(l_title_counter,0)=0
898 THEN
899 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PREC_MR_ID_INVALID');
900 FND_MSG_PUB.ADD;
901 END IF;
902 END IF;
903
904 IF p_x_mr_header_rec.DML_OPERATION='U'
905 THEN
906
907 IF p_x_mr_header_rec.version_number=FND_API.G_MISS_NUM OR
908 p_x_mr_header_rec.version_number IS NULL
909 THEN
910 select version_number into p_x_mr_header_rec.version_number
911 from AHL_MR_HEADERS_APP_V
912 where mr_header_id=p_x_mr_header_rec.mr_header_id;
913 END IF;
914
915 END IF;
916 END;
917
918 -- Start of Validate
919 PROCEDURE VALIDATE_MR_TYPE
920 (
921 x_return_status OUT NOCOPY VARCHAR2,
922 p_mr_header_rec IN AHL_FMP_MR_HEADER_PVT.MR_HEADER_REC
923 )
924 as
925 Cursor GetMrdet(C_MR_HEADER_ID NUMBER)
926 IS
927 SELECT * FROM AHL_MR_HEADERS_APP_V
928 WHERE MR_HEADER_ID=C_MR_HEADER_ID;
929 l_mr_rec GetMrdet%rowtype;
930 l_counter NUMBER:=0;
931 BEGIN
932 x_return_status:=FND_API.G_RET_STS_SUCCESS;
933
934 IF p_mr_header_rec.MR_HEADER_ID IS NOT NULL OR p_mr_header_rec.MR_HEADER_ID<>FND_API.G_MISS_NUM
935 THEN
936
937
938 OPEN GetMrDet(p_mr_header_rec.MR_HEADER_ID);
939 FETCH GetMrDet into l_mr_rec;
940 IF GetMrDet%NOTFOUND
941 THEN
942 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
943 FND_MSG_PUB.ADD;
944 ELSE
945
946 IF p_mr_header_rec.TYPE_CODE<>l_mr_rec.type_code
947 THEN
948 SELECT COUNT(*) INTO l_counter
949 FROM AHL_MR_RELATIONSHIPS
950 WHERE MR_HEADER_ID=p_mr_header_rec.MR_HEADER_ID;
951 IF l_counter >0
952 THEN
953 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_REL_SHOULDNOT_EXIST');
954 FND_MSG_PUB.ADD;
955 END IF;
956
957 SELECT COUNT(*) INTO l_counter
958 FROM AHL_MR_EFFECTIVITIES
959 WHERE MR_HEADER_ID=p_mr_header_rec.MR_HEADER_ID
960 AND ((PROGRAM_DURATION IS NOT NULL OR PROGRAM_DURATION_UOM_CODE IS NOT NULL)
961 AND THRESHOLD_DATE IS NOT NULL);
962
963
964 IF l_counter >0
965 THEN
966 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EFF_SHOULDNOT_EXIST');
967 FND_MSG_PUB.ADD;
968 END IF;
969
970 SELECT COUNT(B.MR_INTERVAL_ID) INTO l_counter
971 FROM AHL_MR_EFFECTIVITIES A,AHL_MR_INTERVALS B
972 WHERE A.MR_HEADER_ID=p_mr_header_rec.MR_HEADER_ID
973 AND A.MR_EFFECTIVITY_ID=B.MR_EFFECTIVITY_ID;
974
975 IF l_counter >0
976 THEN
977 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INT_SHOULDNOT_EXIST');
978 FND_MSG_PUB.ADD;
979 END IF;
980 END IF;
981
982 IF p_mr_header_rec.TYPE_CODE='PROGRAM' and l_mr_rec.type_code='ACTIVITY'
983 THEN
984 SELECT COUNT(*) INTO l_counter
985 FROM AHL_MR_ROUTES
986 WHERE MR_HEADER_ID=p_mr_header_rec.MR_HEADER_ID;
987
988 IF l_counter >0
989 THEN
990 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTS_SHOULDNOT_EXIST');
991 FND_MSG_PUB.ADD;
992 END IF;
993
994 SELECT COUNT(*) INTO l_counter
995 FROM AHL_MR_ACTIONS_B
996 WHERE MR_HEADER_ID=p_mr_header_rec.MR_HEADER_ID;
997 IF l_counter >0
998 THEN
999 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ACTNS_SHOULDNOT_EXIST');
1000 FND_MSG_PUB.ADD;
1001 END IF;
1002
1003 SELECT COUNT(*) INTO l_counter
1004 FROM AHL_DOC_TITLE_ASSOS_B
1005 WHERE ASO_OBJECT_ID=p_mr_header_rec.MR_HEADER_ID
1006 AND ASO_OBJECT_TYPE_CODE='MR';
1007
1008 IF l_counter >0
1009 THEN
1010 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_DOC_SHOULDNOT_EXIST');
1011 FND_MSG_PUB.ADD;
1012 END IF;
1013 END IF;
1014 END IF;
1015 CLOSE GetMrDet;
1016 END IF;
1017 END;
1018
1019
1020 PROCEDURE VALIDATE_MR_HEADER
1021 (
1022 x_return_status OUT NOCOPY VARCHAR2,
1023 p_mr_header_rec IN AHL_FMP_MR_HEADER_PVT.MR_HEADER_REC
1024 )
1025 as
1026
1027 CURSOR get_lookup_meaning_to_code(c_lookup_type VARCHAR2,c_meaning VARCHAR2)
1028 IS
1029 SELECT lookup_code
1030 FROM FND_LOOKUP_VALUES_VL
1031 WHERE lookup_type= c_lookup_type
1032 AND upper(meaning) =upper(c_meaning)
1033 AND sysdate between start_date_active
1034 AND nvl(end_date_active,sysdate);
1035
1036 CURSOR get_mr_title(c_mr_header_id number)
1037 IS
1038 SELECT title,object_version_number
1039 FROM AHL_MR_HEADERS_APP_V
1040 WHERE mr_header_id= c_mr_header_id;
1041
1042 CURSOR
1043 check_prog_subtype(C_PROGRAM_TYPE_CODE VARCHAR2,C_PROGRAM_SUBTYPE_CODE VARCHAR2)
1044 IS
1045 SELECT count(*)
1046 FROM AHL_PROG_TYPE_SUBTYPES
1047 WHERE
1048 PROGRAM_TYPE_CODE =C_PROGRAM_TYPE_CODE AND
1049 PROGRAM_SUBTYPE_CODE=C_PROGRAM_SUBTYPE_CODE;
1050
1051 --Super user cannot edit these fields
1052 CURSOR get_super_non_edit(c_mr_header_id number)
1053 IS
1054 --AMSRINIV.Bug 4916286. Tuning below commented query for improving performance
1055 SELECT
1056 mr.implement_status_code,
1057 mr.repetitive_flag,
1058 mr.show_repetitive_code,
1059 mr.whichever_first_code,
1060 mr.effective_from,
1061 mr.copy_accomplishment_flag,
1062 mr.type_code,
1063 mtl.concatenated_segments billing_item,
1064 mr.billing_item_id,
1065 mr.qa_inspection_type qa_inspection_type_code,
1066 qa.description qa_inspection_type,
1067 mr.space_category_code,
1068 mr.down_time,
1069 mr.uom_code
1070 FROM
1071 ahl_mr_headers_b mr,
1072 qa_char_value_lookups qa,
1073 mtl_system_items_kfv mtl
1074 WHERE
1075 mr_header_id= c_mr_header_id AND
1076 qa.short_code(+) = mr.qa_inspection_type AND
1077 qa.char_id(+) = 87 AND
1078 mtl.inventory_item_id(+) = mr.billing_item_id AND
1079 mtl.organization_id(+) = mr.billing_org_id AND
1080 mr.application_usg_code =
1081 RTRIM(LTRIM(fnd_profile.value('AHL_APPLN_USAGE')));
1082
1083 /*SELECT IMPLEMENT_STATUS_CODE,
1084 REPETITIVE_FLAG,
1085 SHOW_REPETITIVE_CODE,
1086 WHICHEVER_FIRST_CODE,
1087 EFFECTIVE_FROM,
1088 COPY_ACCOMPLISHMENT_FLAG,
1089 TYPE_CODE,
1090 BILLING_ITEM,
1091 BILLING_ITEM_ID,
1092 QA_INSPECTION_TYPE_CODE,
1093 QA_INSPECTION_TYPE,
1094 SPACE_CATEGORY_CODE,
1095 DOWN_TIME,
1096 UOM_CODE
1097 FROM AHL_MR_HEADERS_V
1098 WHERE mr_header_id= c_mr_header_id;*/
1099
1100 l_rec get_super_non_edit%rowtype;
1101
1102 Cursor GetHeaderInfo(C_MR_HEADER_ID NUMBER)
1103 IS
1104 SELECT MR_HEADER_ID,
1105 TITLE,
1106 VERSION_NUMBER,
1107 MR_STATUS_CODE,
1108 EFFECTIVE_FROM
1109 FROM AHL_MR_HEADERS_APP_V
1110 WHERE MR_HEADER_ID=C_MR_HEADER_ID
1111 AND object_version_number=p_mr_header_rec.object_Version_number;
1112
1113 l_mr_rec GetHeaderInfo%ROWTYPE;
1114
1115
1116 Cursor GetHeaderInfo1(C_TITLE VARCHAR2,C_VERSION_NUMBER NUMBER)
1117 IS
1118 SELECT MR_HEADER_ID,TITLE,VERSION_NUMBER,MR_STATUS_CODE,EFFECTIVE_FROM
1119 FROM AHL_MR_HEADERS_APP_V
1120 WHERE upper(TITLE)=upper(C_TITLE)
1121 and version_number=c_version_number-1;
1122
1123 l_mr_rec1 GetHeaderInfo1%ROWTYPE;
1124
1125 l_title VARCHAR2(255);
1126 l_object_version_number NUMBER;
1127 l_lookup_code VARCHAR2(30);
1128 l_appln_code VARCHAR2(80);
1129 l_msg_count NUMBER;
1130 l_msg_data1 VARCHAR2(2000);
1131 l_return_status VARCHAR2(1);
1132 l_mr_header_rec AHL_FMP_MR_HEADER_PVT.mr_header_Rec:=p_mr_header_rec;
1133 l_lookup_var varchar2(1);
1134 l_title_counter NUMBER:=0;
1135 l_check_flag VARCHAR2(1):='Y';
1136 l_counter number:=0;
1137 l_prev_ver_date DATE;
1138
1139 -- Tamal [MEL/CDL] -- Begin changes
1140 CURSOR check_route_mo_proc
1141 (
1142 c_mr_header_id number
1143 )
1144 IS
1145 select 'x'
1146 from ahl_mr_routes mrr, ahl_routes_b rm
1147 where mrr.route_id = rm.route_id and
1148 nvl(rm.route_type_code, 'X') not in ('M_PROC','O_PROC') and
1149 mrr.mr_header_id = c_mr_header_id;
1150
1151 CURSOR check_eff_exists
1152 (
1153 c_mr_header_id number
1154 )
1155 IS
1156 select 'x'
1157 from ahl_mr_effectivities
1158 where mr_header_id = c_mr_header_id;
1159
1160 l_dummy_char varchar2(1);
1161 l_old_prog_type varchar2(30);
1162 -- Tamal [MEL/CDL] -- End changes
1163
1164 BEGIN
1165 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1166
1167 IF G_DEBUG='Y' THEN
1168 AHL_DEBUG_PUB.enable_debug;
1169 AHL_DEBUG_PUB.debug('Application Usage code:'||G_APPLN_USAGE);
1170 AHL_DEBUG_PUB.debug('TYPE CODE '||p_mr_header_rec.TYPE_CODE,'+HEADERS+');
1171 END IF;
1172
1173 IF G_APPLN_USAGE IS NULL
1174 THEN
1175 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
1176 FND_MSG_PUB.ADD;
1177 RETURN;
1178 END IF;
1179
1180 IF p_mr_header_rec.PROGRAM_TYPE_CODE IS NULL OR
1181 p_mr_header_rec.PROGRAM_TYPE_CODE=FND_API.G_MISS_CHAR
1182 THEN
1183 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGTYPE_NULL');
1184 FND_MSG_PUB.ADD;
1185 l_check_flag:='N';
1186 END IF;
1187
1188 IF p_mr_header_rec.AUTO_SIGNOFF_FLAG<>'Y' AND
1189 nvl(p_mr_header_rec.AUTO_SIGNOFF_FLAG,'N')<>'N'
1190 THEN
1191 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_AUTOSIGNOFF_INVALID');
1192 FND_MSG_PUB.ADD;
1193 l_check_flag:='N';
1194 END IF;
1195
1196
1197 IF G_APPLN_USAGE='PM'
1198 THEN
1199 IF p_mr_header_rec.TYPE_CODE IS NULL OR p_mr_header_rec.TYPE_CODE=FND_API.G_MISS_CHAR
1200 THEN
1201 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TYPE_CODE_NULL');
1202 FND_MSG_PUB.ADD;
1203 l_check_flag:='N';
1204 END IF;
1205 END IF;
1206
1207
1208 IF p_mr_header_Rec.PROGRAM_SUBTYPE_CODE IS NULL OR p_mr_header_Rec.PROGRAM_SUBTYPE_CODE=FND_API.G_MISS_char
1209 THEN
1210 l_check_flag:='N';
1211 END IF;
1212
1213 IF l_check_flag='Y'
1214 THEN
1215 OPEN check_prog_subtype(p_mr_header_rec.PROGRAM_TYPE_CODE,p_mr_header_rec.PROGRAM_SUBTYPE_CODE);
1216 FETCH check_prog_subtype INTO l_counter;
1217 IF check_prog_subtype%FOUND and l_counter=0
1218 THEN
1219 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGTYPESUBTYPE_INV');
1220 FND_MESSAGE.SET_TOKEN('FIELD1',p_mr_header_rec.PROGRAM_SUBTYPE_CODE,false);
1221 FND_MESSAGE.SET_TOKEN('FIELD2',p_mr_header_rec.PROGRAM_TYPE_CODE,false);
1222 FND_MSG_PUB.ADD;
1223 END IF;
1224 CLOSE check_prog_subtype;
1225 END IF;
1226
1227 -- Service Type
1228 IF G_APPLN_USAGE<>'PM'
1229 THEN
1230
1231 IF p_mr_header_rec.service_type_code IS NOT NULL OR p_mr_header_rec.service_type_code<>FND_API.G_MISS_CHAR
1232 THEN
1233
1234 CHECK_LOOKUP_CODE
1235 (
1236 x_return_status =>l_return_status,
1237 p_lookup_code =>p_mr_header_rec.service_type_code,
1238 p_lookup_TYPE =>'AHL_FMP_MR_SERVICE_TYPE'
1239 );
1240 IF l_return_status<>'S'
1241 THEN
1242 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_SERVICE_INVALID');
1243 FND_MESSAGE.SET_TOKEN('FIELD',p_mr_header_rec.service_type_code,false);
1244 FND_MSG_PUB.ADD;
1245 END IF;
1246 END IF;
1247
1248 ELSIF G_APPLN_USAGE<>'PM'
1249 THEN
1250 CHECK_LOOKUP_CODE
1251 (
1252 x_return_status => l_return_status,
1253 p_lookup_code => p_mr_header_rec.uom_code,
1254 p_lookup_TYPE =>'AHL_FMP_PM_DOWNTIME_UOM'
1255 );
1256 IF l_return_status<>'S'
1257 THEN
1258 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_UOM_CODE_INV');
1259 FND_MSG_PUB.ADD;
1260 END IF;
1261 END IF;
1262
1263 -- Implement Status
1264
1265
1266 CHECK_LOOKUP_CODE
1267 (
1268 x_return_status =>l_return_status,
1269 p_lookup_code =>p_mr_header_rec.implement_status_code,
1270 p_lookup_TYPE =>'AHL_FMP_MR_IMPLEMENT_STATUS'
1271 );
1272 IF l_return_status<>'S'
1273 THEN
1274 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_IMPLMNTSTAT_INVALID');
1275 FND_MESSAGE.SET_TOKEN('FIELD',p_mr_header_rec.implement_status_code,false);
1276 FND_MSG_PUB.ADD;
1277 END IF;
1278
1279 -- Qa Inspection type and autosignoff
1280
1281 IF p_mr_header_rec.AUTO_SIGNOFF_FLAG='Y'
1282 THEN
1283
1284 IF p_mr_header_rec.QA_INSPECTION_TYPE IS NOT NULL
1285 and p_mr_header_rec.QA_INSPECTION_TYPE <>FND_API.G_MISS_CHAR
1286 THEN
1287 IF G_DEBUG='Y' THEN
1288 AHL_DEBUG_PUB.debug('Error at AHL_FMP_AUTSIGNOFF_QA_INV');
1289 END IF;
1290 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_AUTSIGNOFF_QA_INV');
1291 FND_MSG_PUB.ADD;
1292 END IF;
1293 END IF;
1294
1295
1296 /*
1297 -- This validation removed based on er 2972124/3822674
1298 IF p_mr_header_rec.implement_status_code IS NOT NULL
1299 and p_mr_header_rec.implement_status_code <>FND_API.G_MISS_CHAR
1300 THEN
1301 IF p_mr_header_rec.implement_status_code = 'OPTIONAL_DO_NOT_IMPLEMENT'
1302 AND (p_mr_header_rec.qa_inspection_type IS NOT NULL
1303 AND p_mr_header_rec.qa_inspection_type <>FND_API.G_MISS_CHAR)
1304 THEN
1305 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_IMPL_QA');
1306 FND_MSG_PUB.ADD;
1307 END IF;
1308 END IF;
1309 */
1310 -- Repetitive Flag
1311 CHECK_LOOKUP_CODE
1312 (
1313 x_return_status =>l_return_status,
1314 p_lookup_code =>p_mr_header_rec.repetitive_flag,
1315 p_lookup_TYPE =>'AHL_YES_NO_TYPE'
1316 );
1317
1318 IF l_return_status<>'S'
1319 THEN
1320 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_REPETITIVE_INVALID');
1321 FND_MESSAGE.SET_TOKEN('FIELD',p_mr_header_rec.repetitive_flag,false);
1322 FND_MSG_PUB.ADD;
1323 ELSE
1324 IF p_mr_header_rec.repetitive_flag ='N'
1325 and (p_mr_header_rec.SHOW_REPETITIVE_CODE IS NOT NULL OR p_mr_header_rec.SHOW_REPETITIVE_CODE<>FND_API.G_MISS_CHAR)
1326 THEN
1327 IF p_mr_header_rec.SHOW_REPETITIVE_CODE<>'NEXT'
1328 THEN
1329 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_REPITITVE_NO');
1330 FND_MSG_PUB.ADD;
1331 END IF;
1332 END IF;
1333
1334 if p_mr_header_rec.dml_operation<>'C'
1335 then
1336
1337 IF p_mr_header_rec.SUPERUSER_ROLE<>'Y'
1338 THEN
1339
1340 AHL_FMP_COMMON_PVT.validate_mr_interval_threshold
1341 (
1342 x_return_status=>l_return_status,
1343 x_msg_data=>l_msg_data1,
1344 p_mr_header_id=>p_mr_header_rec.mr_header_id,
1345 p_repetitive_flag=>p_mr_header_rec.repetitive_flag
1346 );
1347
1348 IF l_return_Status<>FND_API.G_RET_STS_SUCCESS
1349 THEN
1350 FND_MESSAGE.SET_NAME('AHL',l_msg_data1);
1351 FND_MSG_PUB.ADD;
1352 END IF;
1353 END IF;
1354 end if;
1355 END IF;
1356
1357 -- Whichever_first_code
1358 CHECK_LOOKUP_CODE
1359 (
1360 x_return_status =>l_return_status,
1361 p_lookup_code =>p_mr_header_rec.whichever_first_code,
1362 p_lookup_TYPE =>'AHL_FMP_THRESHOLD_FIRST'
1363 );
1364
1365 IF l_return_status<>'S'
1366 THEN
1367 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_WHICHEVER_INVALID');
1368 FND_MESSAGE.SET_TOKEN('FIELD',p_mr_header_rec.whichever_first_code,false);
1369 FND_MSG_PUB.ADD;
1370 END IF;
1371
1372 --Space Category
1373
1374 IF p_mr_header_rec.space_category_code is not null or p_mr_header_rec.space_category_code<>fnd_api.g_miss_char
1375 THEN
1376 CHECK_LOOKUP_CODE
1377 (
1378 x_return_status =>l_return_status,
1379 p_lookup_code =>p_mr_header_rec.space_category_code,
1380 p_lookup_TYPE =>'AHL_LTP_SPACE_CATEGORY'
1381 );
1382
1383 IF l_return_status<>'S'
1384 THEN
1385 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_VISIT_CATEGORY');
1386 FND_MESSAGE.SET_TOKEN('FIELD',p_mr_header_rec.space_category_code,false);
1387 FND_MSG_PUB.ADD;
1388 END IF;
1389 END IF;
1390
1391
1392
1393
1394
1395 -- Effective From Validate Of Date
1396 IF l_mr_header_rec.MR_STATUS_CODE='DRAFT' -- AND nvl(l_mr_header_rec.SUPERUSER_ROLE,'N')='N'
1397 THEN
1398 IF p_mr_header_rec.effective_from is null or p_mr_header_rec.effective_from=fnd_api.g_miss_date
1399 THEN
1400 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_EFFECTIVE_FROM_NULL');
1401 FND_MSG_PUB.ADD;
1402 ELSIF TRUNC(p_mr_header_rec.effective_from)<TRUNC(SYSDATE)
1403 THEN
1404 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_EFFE_FROM_INVALID');
1405 FND_MSG_PUB.ADD;
1406 END IF;
1407 END IF;
1408 IF P_MR_HEADER_REC.DML_OPERATION='U'
1409 THEN
1410 IF l_mr_header_rec.MR_STATUS_CODE='DRAFT' -- AND nvl(l_mr_header_rec.SUPERUSER_ROLE,'N')='N'
1411 THEN
1412 open GetHeaderInfo(P_MR_HEADER_REC.mr_header_id);
1413 fetch GetHeaderInfo into l_mr_rec;
1414 close GetHeaderInfo;
1415
1416 if l_mr_rec.version_number>1
1417 then
1418 open GetHeaderInfo1(l_mr_rec.TITLE,l_mr_rec.VERSION_NUMBER);
1419 fetch GetHeaderInfo1 into l_mr_rec1;
1420 IF GetHeaderInfo1%FOUND
1421 THEN
1422
1423 IF trunc(l_mr_Rec.effective_from) < trunc(l_mr_Rec1.effective_from)
1424 THEN
1425 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ST_DATE_LESSER');
1426 FND_MESSAGE.SET_TOKEN('FIELD',l_mr_Rec.effective_from,false);
1427 FND_MSG_PUB.ADD;
1428 END IF;
1429 END IF;
1430 close GetHeaderInfo1;
1431 end if;
1432 end if;
1433 END IF;
1434 -- Super User
1435 IF G_DEBUG='Y'
1436 THEN
1437 AHL_DEBUG_PUB.debug('superusermode is ...:'||l_MR_HEADER_REC.SUPERUSER_ROLE);
1438 AHL_DEBUG_PUB.debug('billing item :'||nvl(l_MR_HEADER_REC.BILLING_ITEM,'X'));
1439 AHL_DEBUG_PUB.debug('billing item :'||nvl(l_REC.BILLING_ITEM,'X'));
1440 AHL_DEBUG_PUB.debug('billing item :'||nvl(p_mr_header_rec.mr_header_id,0));
1441 END IF;
1442 IF l_mr_header_rec.MR_STATUS_CODE='COMPLETE' AND nvl(l_mr_header_rec.SUPERUSER_ROLE,'N')='N'
1443 THEN
1444 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_INVALID_EDIT');
1445 FND_MSG_PUB.ADD;
1446 ELSIF l_mr_header_rec.MR_STATUS_CODE='COMPLETE' AND l_mr_header_rec.SUPERUSER_ROLE='Y'
1447 THEN
1448
1449
1450 OPEN get_super_non_edit(p_mr_header_rec.MR_HEADER_ID);
1451 FETCH get_super_non_edit INTO l_rec;
1452 IF G_DEBUG='Y'
1453 THEN
1454 AHL_DEBUG_PUB.debug(l_rec.REPETITIVE_FLAG ||'---'||(p_mr_header_rec.REPETITIVE_FLAG));
1455 AHL_DEBUG_PUB.debug(l_rec.SHOW_REPETITIVE_CODE||'---'|| (p_mr_header_rec.SHOW_REPETITIVE_CODE));
1456 AHL_DEBUG_PUB.debug(l_rec.WHICHEVER_FIRST_CODE||'---'|| (p_mr_header_rec.WHICHEVER_FIRST_CODE));
1457 AHL_DEBUG_PUB.debug(l_rec.EFFECTIVE_FROM||'---'||(p_mr_header_rec.EFFECTIVE_FROM));
1458 AHL_DEBUG_PUB.debug(l_rec.BILLING_ITEM_ID||'---'||(p_mr_header_rec.BILLING_ITEM_ID));
1459 AHL_DEBUG_PUB.debug(l_rec.BILLING_ITEM);
1460 AHL_DEBUG_PUB.debug(l_rec.qa_inspection_type_code||'---'||(p_mr_header_rec.qa_inspection_type_code));
1461 AHL_DEBUG_PUB.debug(l_rec.space_category_code||'---'||(P_mr_header_rec.space_category_code));
1462 AHL_DEBUG_PUB.debug(l_rec.DOWN_TIME||'---'||(p_mr_header_rec.DOWN_TIME));
1463 AHL_DEBUG_PUB.debug(l_rec.UOM_CODE||'---'||(p_mr_header_rec.uom_code));
1464 END IF;
1465
1466 IF nvl(l_rec.REPETITIVE_FLAG,'X') <> nvl(p_mr_header_rec.REPETITIVE_FLAG,'X')
1467 or nvl(l_rec.SHOW_REPETITIVE_CODE,'X')<> nvl(p_mr_header_rec.SHOW_REPETITIVE_CODE,'X')
1468 or nvl(l_rec.WHICHEVER_FIRST_CODE,'X')<> nvl(p_mr_header_rec.WHICHEVER_FIRST_CODE,'X')
1469 or trunc(l_rec.EFFECTIVE_FROM)<>trunc(p_mr_header_rec.EFFECTIVE_FROM)
1470 or nvl(l_rec.BILLING_ITEM_ID,0)<>nvl(p_mr_header_rec.BILLING_ITEM_ID,0)
1471 or nvl(l_rec.qa_inspection_type_code,'X')<>nvl(p_mr_header_rec.qa_inspection_type_code,'X')
1472 or nvl(l_rec.space_category_code,'X')<>nvl(P_mr_header_rec.space_category_code,'X')
1473 or NVL(l_rec.DOWN_TIME,0)<>NVL(p_mr_header_rec.DOWN_TIME,0)
1474 or nvl(l_rec.UOM_CODE,'X')<>nvl(p_mr_header_rec.uom_code,'X')
1475 THEN
1476 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_SUPER_NONEDIT_COLS');
1477 FND_MSG_PUB.ADD;
1478 AHL_DEBUG_PUB.debug('SUPERUSER NONEDIT:'||L_MR_HEADER_REC.SUPERUSER_ROLE);
1479 l_check_flag:='N';
1480 END IF;
1481 CLOSE get_super_non_edit;
1482 END IF;
1483
1484 -- Validating null items
1485
1486 IF p_mr_header_rec.SHOW_REPETITIVE_CODE IS NULL OR p_mr_header_rec.SHOW_REPETITIVE_CODE=FND_API.G_MISS_char
1487 THEN
1488 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_REPETITIVE_INVALID');
1489 FND_MSG_PUB.ADD;
1490 END IF;
1491
1492 IF p_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG IS NULL
1493 OR p_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG=FND_API.G_MISS_CHAR
1494 THEN
1495 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ACCMPLSHMNT_NULL');
1496 FND_MSG_PUB.ADD;
1497 END IF;
1498
1499 IF p_mr_header_rec.CATEGORY_CODE IS NULL
1500 OR p_mr_header_rec.CATEGORY_CODE=FND_API.G_MISS_CHAR
1501 THEN
1502 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_CATEGORY_NULL');
1503 FND_MSG_PUB.ADD;
1504 END IF;
1505
1506 IF G_APPLN_USAGE='PM'
1507 THEN
1508 IF p_mr_header_rec.TYPE_CODE IS NULL
1509 OR p_mr_header_rec.TYPE_CODE=FND_API.G_MISS_CHAR
1510 THEN
1511 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_TYPECODE_NULL');
1512 FND_MSG_PUB.ADD;
1513 ELSE
1514 CHECK_LOOKUP_CODE
1515 (
1516 x_return_status =>l_return_status,
1517 p_lookup_code =>p_mr_header_rec.type_code,
1518 p_lookup_TYPE =>'AHL_FMP_MR_TYPE'
1519 );
1520 IF l_return_status<>'S'
1521 THEN
1522 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_TYPE_CODE_INVALID');
1523 FND_MSG_PUB.ADD;
1524 END IF;
1525 END IF;
1526
1527
1528 IF NVL(p_mr_header_rec.TYPE_CODE,'X')='PROGRAM'
1529 and NVL(p_mr_header_rec.DOWN_TIME,0)>0
1530 THEN
1531 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_DOWNTIME_ZERO');
1532 FND_MSG_PUB.ADD;
1533 ELSIF NVL(p_mr_header_rec.TYPE_CODE,'X')<>'PROGRAM'
1534 and NVL(p_mr_header_rec.DOWN_TIME,0)=0
1535 THEN
1536 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_DOWNTIME_NO_Z');
1537 FND_MSG_PUB.ADD;
1538 END IF;
1539
1540 IF NVL(p_mr_header_rec.TYPE_CODE,'X')='PROGRAM'
1541 and NVL(p_mr_header_rec.SERVICE_REQUEST_TEMPLATE_ID,0)>0
1542 THEN
1543 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_SRVREQ_TEMPLT');
1544 FND_MSG_PUB.ADD;
1545 END IF;
1546
1547 IF p_mr_header_rec.DML_OPERATION='U'
1548 THEN
1549 AHL_DEBUG_PUB.debug('Error.Before calling validate mr_type :'||L_MR_HEADER_REC.SUPERUSER_ROLE);
1550 VALIDATE_MR_TYPE
1551 (
1552 x_return_status =>x_return_Status,
1553 p_mr_header_rec =>l_mr_header_rec
1554 );
1555
1556 END IF;
1557 else
1558 IF (p_mr_header_rec.DOWN_TIME IS NOT NULL AND
1559 p_mr_header_rec.DOWN_TIME<>FND_API.G_MISS_NUM)
1560 AND p_mr_header_rec.DOWN_TIME <=0
1561 THEN
1562 -- FOR CMRO MODE
1563 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PM_DOWNTIME_NO_Z');
1564 FND_MSG_PUB.ADD;
1565 END IF;
1566
1567 END IF;
1568
1569 -- Tamal [MEL/CDL] -- Begin changes
1570 IF (p_mr_header_rec.dml_operation <> 'D')
1571 THEN
1572 IF (p_mr_header_rec.program_type_code = 'MO_PROC' AND p_mr_header_rec.implement_status_code <> 'OPTIONAL_DO_NOT_IMPLEMENT')
1573 THEN
1574 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MR_MOPROC_IMPL_INV');
1575 -- Maintenance Requirements of (M) and (O) procedure program type must be unplanned.
1576 FND_MSG_PUB.ADD;
1577 END IF;
1578 END IF;
1579 -- Tamal [MEL/CDL] -- End changes
1580
1581 -- DML Operation Create
1582
1583 IF p_mr_header_rec.dml_operation='C' THEN
1584 IF p_mr_header_rec.title is null or p_mr_header_rec.title=fnd_api.g_miss_char
1585 THEN
1586 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TITLE_NULL');
1587 FND_MSG_PUB.ADD;
1588 ELSIF p_mr_header_rec.title is not null or p_mr_header_rec.title<>fnd_api.g_miss_char
1589 THEN
1590
1591 -- Title Should not Repeat in Create mode
1592
1593 SELECT COUNT(*) INTO l_title_counter
1594 FROM AHL_MR_HEADERS_APP_V
1595 WHERE upper(ltrim(rtrim(TITLE)))=upper(ltrim(rtrim(l_mr_header_rec.title)));
1596 IF l_title_counter >0 then
1597 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TITLE_INVALID');
1598 FND_MSG_PUB.ADD;
1599 END IF;
1600 END IF;
1601
1602 ELSIF p_mr_header_rec.dml_operation<>'U' THEN
1603 IF p_mr_header_rec.title is null or p_mr_header_rec.title=fnd_api.g_miss_char
1604 THEN
1605 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TITLE_NULL');
1606 FND_MSG_PUB.ADD;
1607 END IF;
1608 IF p_mr_header_rec.PRECEDING_MR_HEADER_ID=p_mr_header_rec.MR_HEADER_ID
1609 THEN
1610 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PREC_MR_ID_INVALID');
1611 FND_MSG_PUB.ADD;
1612 END IF;
1613
1614 IF p_mr_header_rec.mr_header_id is null or p_mr_header_rec.mr_header_id=fnd_api.g_miss_num
1615 THEN
1616 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
1617 FND_MSG_PUB.ADD;
1618 END IF;
1619
1620 IF p_mr_header_rec.SERVICE_TYPE_CODE IS NULL OR p_mr_header_rec.SERVICE_TYPE_CODE=FND_API.G_MISS_char
1621 THEN
1622 IF G_APPLN_USAGE<>'PM'
1623 THEN
1624 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_SERVICE_NULL');
1625 FND_MSG_PUB.ADD;
1626 END IF;
1627 ELSE
1628 OPEN get_mr_title(p_mr_header_rec.mr_header_id);
1629 FETCH get_mr_title INTO l_title,l_object_version_number;
1630
1631 IF get_mr_title%NOTFOUND
1632 THEN
1633 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_INVALID');
1634 FND_MSG_PUB.ADD;
1635 END IF;
1636
1637 IF p_mr_header_rec.object_version_number <> l_object_version_number or l_object_version_number is null or l_object_version_number=fnd_api.g_miss_num
1638 THEN
1639 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1640 FND_MSG_PUB.ADD;
1641 END IF;
1642 IF p_mr_header_rec.title <> l_title
1643 THEN
1644 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TITLE_NOT_EDITABLE');
1645 FND_MSG_PUB.ADD;
1646 END IF;
1647 CLOSE get_mr_title;
1648 END IF;
1649 END IF;
1650
1651 -- Tamal [MEL/CDL] -- Begin changes
1652 IF (p_mr_header_rec.dml_operation = 'U')
1653 THEN
1654 IF (p_mr_header_rec.program_type_code='MO_PROC')
1655 THEN
1656 OPEN check_route_mo_proc(p_mr_header_rec.mr_header_id);
1657 FETCH check_route_mo_proc INTO l_dummy_char;
1658 IF (check_route_mo_proc%FOUND)
1659 THEN
1660 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MR_TYPE_ROUTE_INV');
1661 -- Cannot modify program type to (M) and (O) procedure since routes of non (M), (O) procedure type are already associated.
1662 FND_MSG_PUB.ADD;
1663 END IF;
1664 CLOSE check_route_mo_proc;
1665 END IF;
1666
1667 SELECT program_type_code INTO l_old_prog_type FROM ahl_mr_headers_b WHERE mr_header_id = p_mr_header_rec.mr_header_id;
1668 IF (l_old_prog_type <> p_mr_header_rec.program_type_code AND 'MO_PROC' IN (l_old_prog_type, p_mr_header_rec.program_type_code))
1669 THEN
1670 OPEN check_eff_exists(p_mr_header_rec.mr_header_id);
1671 FETCH check_eff_exists INTO l_dummy_char;
1672 IF (check_eff_exists%FOUND)
1673 THEN
1674 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MR_TYPE_EFF_INV');
1675 -- Cannot modify program type to / from(M) and (O) procedure since effectivities already exist.
1676 FND_MSG_PUB.ADD;
1677 END IF;
1678 CLOSE check_eff_exists;
1679 END IF;
1680 END IF;
1681 -- Tamal [MEL/CDL] -- End changes
1682
1683 l_msg_count := FND_MSG_PUB.count_msg;
1684
1685 IF l_msg_count > 0
1686 THEN
1687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1688 END IF;
1689
1690 END;
1691
1692 PROCEDURE CHECK_CYCLIC_ASSOCIATION
1693 (
1694 p_api_version IN NUMBER:=1.0,
1695 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE ,
1696 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
1697 x_return_status OUT NOCOPY VARCHAR2,
1698 x_msg_count OUT NOCOPY NUMBER,
1699 x_msg_data OUT NOCOPY VARCHAR2,
1700 P_MR_HEADER_ID IN NUMBER,
1701 P_PREC_HEADER_ID IN NUMBER,
1702 P_RELATED_MR_TITLE IN VARCHAR2
1703 )
1704 AS
1705 l_cyclic_loop EXCEPTION;
1706 pragma EXCEPTION_INIT(l_cyclic_loop,-1436);
1707 l_counter NUMBER;
1708 BEGIN
1709 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1710
1711 SELECT COUNT(*) INTO l_counter
1712 FROM AHL_MR_HEADERS_B
1713 WHERE APPLICATION_USG_CODE=G_APPLN_USAGE
1714 START WITH MR_HEADER_ID=P_MR_HEADER_ID
1715 CONNECT BY PRIOR MR_HEADER_ID=PRECEDING_MR_HEADER_ID;
1716
1717 EXCEPTION
1718 WHEN l_cyclic_loop THEN
1719 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PREC_MR_H_ID_CYCLIC');
1720 FND_MESSAGE.SET_TOKEN('FIELD',P_RELATED_MR_TITLE,false);
1721 FND_MSG_PUB.ADD;
1722 x_return_status := FND_API.G_RET_STS_ERROR;
1723 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1724 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1726 p_count => x_msg_count,
1727 p_data => x_msg_data);
1728
1729 WHEN FND_API.G_EXC_ERROR THEN
1730 x_return_status := FND_API.G_RET_STS_ERROR;
1731 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1732 p_count => x_msg_count,
1733 p_data => X_msg_data);
1734 WHEN OTHERS THEN
1735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1736 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1737 THEN
1738 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_MR_ASSOCIATIONS_PVT',
1739 p_procedure_name => 'CHECK_CYCLIC_ASSOCIATION',
1740 p_error_text => SUBSTR(SQLERRM,1,240));
1741 END IF;
1742 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1743 p_count => x_msg_count,
1744 p_data => X_msg_data);
1745 END;
1746
1747
1748 PROCEDURE CREATE_MR_HEADER
1749 (
1750 p_api_version IN NUMBER:=1.0,
1751 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
1752 p_commit IN VARCHAR2:= FND_API.G_FALSE,
1753 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
1754 p_default IN VARCHAR2:= FND_API.G_FALSE ,
1755 p_module_type IN VARCHAR2:=NULL,
1756 x_return_status OUT NOCOPY VARCHAR2,
1757 x_msg_count OUT NOCOPY NUMBER,
1758 x_msg_data OUT NOCOPY VARCHAR2,
1759 p_x_mr_header_rec IN OUT NOCOPY mr_header_Rec
1760 )
1761 as
1762 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MR_HEADER';
1763 l_api_version CONSTANT NUMBER := 1.0;
1764 l_num_rec NUMBER;
1765 l_msg_count NUMBER;
1766 l_msg_data VARCHAR2(2000);
1767 l_return_status VARCHAR2(1);
1768 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
1769 l_commit VARCHAR2(1):= FND_API.G_FALSE;
1770 l_mr_header_id number:=0;
1771 l_rowid varchar2(30);
1772 BEGIN
1773 SAVEPOINT CREATE_MR_HEADER_PVT;
1774
1775 -- Initialize message list if p_init_msg_list is set to TRUE.
1776
1777 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1778 p_api_version,
1779 l_api_name,G_PKG_NAME)
1780 THEN
1781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1782 END IF;
1783
1784
1785 IF FND_API.to_boolean(l_init_msg_list) THEN
1786 FND_MSG_PUB.initialize;
1787 END IF;
1788
1789
1790 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1791
1792
1793 IF G_DEBUG='Y' THEN
1794 AHL_DEBUG_PUB.enable_debug;
1795 END IF;
1796
1797
1798 IF p_module_type = 'JSP' THEN
1799 p_x_mr_header_rec.PROGRAM_TYPE_CODE:=NULL;
1800 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=NULL;
1801 p_x_mr_header_rec.BILLING_ITEM_ID:=NULL;
1802 p_x_mr_header_rec.QA_INSPECTION_TYPE_CODE:=NULL;
1803 p_x_mr_header_rec.PROGRAM_TYPE_CODE:=NULL;
1804 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=NULL;
1805 p_x_mr_header_rec.PRECEDING_MR_HEADER_ID:=NULL;
1806 END IF;
1807 AHL_DEBUG_PUB.debug('p_x_mr_header_rec.billing_item_id :'||p_x_mr_header_rec.billing_item_id);
1808 -- Debug info.
1809
1810 IF G_DEBUG='Y' THEN
1811 AHL_DEBUG_PUB.debug( 'AHL_FMP_MR_HEADERS_PVT.','+CREATE_MR_HEADER+');
1812 END IF;
1813
1814 IF FND_API.to_boolean(p_default)
1815 THEN
1816 DEFAULT_MISSING_ATTRIBS
1817 (
1818 p_x_mr_header_rec =>p_x_mr_header_rec
1819 );
1820 END IF;
1821
1822
1823 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
1824 THEN
1825 TRANSLATE_VALUE_ID
1826 (
1827 x_return_status =>x_return_Status,
1828 p_x_mr_header_rec =>p_x_mr_header_rec);
1829 END IF;
1830
1831 l_msg_count := FND_MSG_PUB.count_msg;
1832 IF l_msg_count > 0
1833 THEN
1834 x_msg_count := l_msg_count;
1835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1836 RAISE FND_API.G_EXC_ERROR;
1837 END IF;
1838
1839 IF p_x_mr_header_rec.DML_OPERATION='C'
1840 THEN
1841 p_x_mr_header_rec.mr_status_code:='DRAFT';
1842 p_x_mr_header_rec.VERSION_NUMBER:=1;
1843 p_x_mr_header_rec.copy_accomplishment_flag:='N';
1844 END IF;
1845
1846 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
1847 THEN
1848 VALIDATE_MR_HEADER
1849 (
1850 x_return_status =>x_return_Status,
1851 p_mr_header_rec =>p_x_mr_header_rec);
1852 END IF;
1853
1854 l_msg_count := FND_MSG_PUB.count_msg;
1855
1856 IF l_msg_count > 0
1857 THEN
1858 x_msg_count := l_msg_count;
1859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1860 RAISE FND_API.G_EXC_ERROR;
1861 END IF;
1862
1863
1864 -- insert process goes here
1865 /*
1866 IF p_x_mr_header_rec.EFFECTIVE_FROM is null or p_x_mr_header_rec.EFFECTIVE_FROM=FND_API.G_MISS_DATE
1867 THEN
1868 p_x_mr_header_rec.EFFECTIVE_FROM:=sysdate;
1869 END IF;
1870 */
1871 select AHL_MR_HEADERS_B_S.NEXTVAL INTO p_x_mr_header_rec.mr_header_id from dual;
1872
1873
1874 /*
1875 IF p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG IS NULL OR p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG=FND_API.G_MISS_char
1876 THEN
1877 p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG:='N';
1878 END IF;
1879 */
1880 AHL_MR_HEADERS_PKG.INSERT_ROW (
1881 X_MR_HEADER_ID =>p_x_mr_header_rec.mr_header_id,
1882 X_OBJECT_VERSION_NUMBER =>1,
1883 X_TITLE =>p_x_mr_header_rec.TITLE,
1884 X_VERSION_NUMBER =>1,
1885 X_EFFECTIVE_FROM =>p_x_mr_header_rec.EFFECTIVE_FROM,
1886 X_EFFECTIVE_TO =>NULL,
1887 X_REVISION =>p_x_mr_header_rec.REVISION,
1888 X_CATEGORY_CODE =>p_x_mr_header_rec.CATEGORY_CODE,
1889 X_SERVICE_TYPE_CODE =>p_x_mr_header_rec.SERVICE_TYPE_CODE,
1890 X_MR_STATUS_CODE =>p_x_mr_header_rec.MR_STATUS_CODE,
1891 X_IMPLEMENT_STATUS_CODE =>p_x_mr_header_rec.IMPLEMENT_STATUS_CODE,
1892 X_REPETITIVE_FLAG =>p_x_mr_header_rec.REPETITIVE_FLAG,
1893 X_SHOW_REPETITIVE_CODE =>p_x_mr_header_rec.SHOW_REPETITIVE_CODE,
1894 X_WHICHEVER_FIRST_CODE =>p_x_mr_header_rec.WHICHEVER_FIRST_CODE,
1895 X_COPY_ACCOMPLISHMENT_FLAG =>nvl(p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG,'N'),
1896 X_PROGRAM_TYPE_CODE =>p_x_mr_header_rec.PROGRAM_TYPE_CODE ,
1897 X_PROGRAM_SUBTYPE_CODE =>p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE,
1898 X_ATTRIBUTE_CATEGORY =>p_x_mr_header_rec.ATTRIBUTE_CATEGORY,
1899 X_PRECEDING_MR_HEADER_ID =>p_x_mr_header_rec.PRECEDING_MR_HEADER_ID,
1900 X_SERVICE_REQUEST_TEMPLATE_ID =>p_x_mr_header_rec.SERVICE_REQUEST_TEMPLATE_ID,
1901 X_TYPE_CODE =>p_x_mr_header_rec.TYPE_CODE,
1902 X_DOWN_TIME =>p_x_mr_header_rec.DOWN_TIME,
1903 X_UOM_CODE =>p_x_mr_header_rec.UOM_CODE,
1904 X_DESCRIPTION =>p_x_mr_header_rec.DESCRIPTION,
1905 X_COMMENTS =>p_x_mr_header_rec.COMMENTS,
1906 X_SPACE_CATEGORY_CODE =>p_x_mr_header_rec.SPACE_CATEGORY_CODE,
1907 X_QA_INSPECTION_TYPE_CODE =>p_x_mr_header_rec.QA_INSPECTION_TYPE_CODE,
1908 X_BILLING_ITEM_ID =>p_x_mr_header_rec.BILLING_ITEM_ID,
1909 X_AUTO_SIGNOFF_FLAG =>nvl(p_x_mr_header_rec.AUTO_SIGNOFF_FLAG,'N'),
1910 X_COPY_INIT_ACCOMPL_FLAG =>nvl(p_x_mr_header_rec.COPY_INIT_ACCOMPL_FLAG,'N'),
1911 X_COPY_DEFERRALS_FLAG =>nvl(p_x_mr_header_rec.COPY_DEFERRALS_FLAG,'N'),
1912 X_ATTRIBUTE1 =>p_x_mr_header_rec.ATTRIBUTE1,
1913 X_ATTRIBUTE2 =>p_x_mr_header_rec.ATTRIBUTE2,
1914 X_ATTRIBUTE3 =>p_x_mr_header_rec.ATTRIBUTE3,
1915 X_ATTRIBUTE4 =>p_x_mr_header_rec.ATTRIBUTE4,
1916 X_ATTRIBUTE5 =>p_x_mr_header_rec.ATTRIBUTE5,
1917 X_ATTRIBUTE6 =>p_x_mr_header_rec.ATTRIBUTE6,
1918 X_ATTRIBUTE7 =>p_x_mr_header_rec.ATTRIBUTE7,
1919 X_ATTRIBUTE8 =>p_x_mr_header_rec.ATTRIBUTE8,
1920 X_ATTRIBUTE9 =>p_x_mr_header_rec.ATTRIBUTE9,
1921 X_ATTRIBUTE10 =>p_x_mr_header_rec.ATTRIBUTE10,
1922 X_ATTRIBUTE11 =>p_x_mr_header_rec.ATTRIBUTE11,
1923 X_ATTRIBUTE12 =>p_x_mr_header_rec.ATTRIBUTE12,
1924 X_ATTRIBUTE13 =>p_x_mr_header_rec.ATTRIBUTE13,
1925 X_ATTRIBUTE14 =>p_x_mr_header_rec.ATTRIBUTE14,
1926 X_ATTRIBUTE15 =>p_x_mr_header_rec.ATTRIBUTE15,
1927 X_CREATION_DATE =>sysdate,
1928 X_CREATED_BY =>fnd_global.user_id,
1929 X_LAST_UPDATE_DATE =>sysdate,
1930 X_LAST_UPDATED_BY =>fnd_global.user_id,
1931 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
1932
1933 IF FND_API.TO_BOOLEAN(p_commit) THEN
1934 COMMIT;
1935 END IF;
1936
1937 IF G_DEBUG='Y' THEN
1938 AHL_DEBUG_PUB.disable_debug;
1939 END IF;
1940
1941 EXCEPTION
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943 ROLLBACK TO CREATE_MR_HEADER_PVT;
1944 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1945 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1946 p_count => x_msg_count,
1947 p_data => x_msg_data);
1948 IF G_DEBUG='Y' THEN
1949 AHL_DEBUG_PUB.disable_debug;
1950 END IF;
1951 WHEN FND_API.G_EXC_ERROR THEN
1952 ROLLBACK TO CREATE_MR_HEADER_PVT;
1953 X_return_status := FND_API.G_RET_STS_ERROR;
1954 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1955 p_count => x_msg_count,
1956 p_data => X_msg_data);
1957 IF G_DEBUG='Y' THEN
1958 AHL_DEBUG_PUB.disable_debug;
1959 END IF;
1960
1961 WHEN OTHERS THEN
1962 ROLLBACK TO CREATE_MR_HEADER_PVT;
1963 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1964 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1965 THEN
1966 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_HEADER_PVT',
1967 p_procedure_name => 'CREATE_MR_HEADER',
1968 p_error_text => SUBSTR(SQLERRM,1,240));
1969
1970 END IF;
1971 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1972 p_count => x_msg_count,
1973 p_data => X_msg_data);
1974 IF G_DEBUG='Y' THEN
1975 AHL_DEBUG_PUB.disable_debug;
1976 END IF;
1977 END;
1978
1979
1980 PROCEDURE UPDATE_MR_HEADER
1981 (
1982 p_api_version IN NUMBER:=1.0,
1983 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
1984 p_commit IN VARCHAR2:= FND_API.G_FALSE,
1985 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
1986 p_default IN VARCHAR2:= FND_API.G_FALSE ,
1987 p_module_type IN VARCHAR2:=NULL,
1988 x_return_status OUT NOCOPY VARCHAR2,
1989 x_msg_count OUT NOCOPY NUMBER,
1990 x_msg_data OUT NOCOPY VARCHAR2,
1991 p_x_mr_header_rec IN OUT NOCOPY MR_HEADER_REC)
1992 as
1993 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MR_HEADER';
1994 l_api_version CONSTANT NUMBER := 1.0;
1995 l_num_rec NUMBER;
1996 l_msg_count NUMBER;
1997 l_msg_data VARCHAR2(2000);
1998 l_return_status VARCHAR2(1);
1999 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
2000 BEGIN
2001 SAVEPOINT UPDATE_MR_HEADER_PVT;
2002
2003 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2004 p_api_version,
2005 l_api_name,G_PKG_NAME) THEN
2006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2007 END IF;
2008
2009 IF FND_API.to_boolean(p_init_msg_list)
2010 THEN
2011 FND_MSG_PUB.initialize;
2012 END IF;
2013
2014
2015 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2016
2017 IF G_DEBUG='Y' THEN
2018 AHL_DEBUG_PUB.enable_debug;
2019 AHL_DEBUG_PUB.debug('Service Category CODE:'||p_x_mr_header_Rec.SPACE_CATEGORY_CODE);
2020 AHL_DEBUG_PUB.debug('Service Category MEANING :'||p_x_mr_header_Rec.SPACE_CATEGORY);
2021 END IF;
2022 --11.5.10 public API change.
2023 IF p_x_mr_header_rec.mr_header_id IS NULL THEN
2024 -- Function to convert mr_title,mr_version_number to id
2025 AHL_FMP_COMMON_PVT.mr_title_version_to_id(
2026 p_mr_title => p_x_mr_header_rec.title,
2027 p_mr_version_number => p_x_mr_header_rec.version_number,
2028 x_mr_header_id => p_x_mr_header_rec.mr_header_id,
2029 x_return_status => x_return_status
2030 );
2031
2032 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2033 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
2034 fnd_log.string
2035 (
2036 fnd_log.level_error,
2037 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2038 'Title,version to id conversion failed.....'
2039 );
2040 END IF;
2041 RAISE FND_API.G_EXC_ERROR;
2042 END IF;
2043 END IF;
2044
2045 IF FND_API.to_boolean( p_default )
2046 THEN
2047 DEFAULT_MISSING_ATTRIBS
2048 (
2049 p_x_mr_header_rec =>p_x_mr_header_rec
2050 );
2051 END IF;
2052
2053 -- Set lov id to null if module is jsp
2054
2055
2056 IF p_module_type = 'JSP'
2057 and (p_x_mr_header_rec.MR_Status_code='DRAFT' OR
2058 p_x_mr_header_rec.MR_Status_code='APPROVAL_REJECTED')
2059 THEN
2060 p_x_mr_header_rec.PROGRAM_TYPE_CODE:=NULL;
2061 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=NULL;
2062 p_x_mr_header_rec.BILLING_ITEM_ID:=NULL;
2063 p_x_mr_header_rec.QA_INSPECTION_TYPE_CODE:=NULL;
2064 p_x_mr_header_rec.PROGRAM_TYPE_CODE:=NULL;
2065 p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE:=NULL;
2066 p_x_mr_header_rec.PRECEDING_MR_HEADER_ID:=NULL;
2067 END IF;
2068
2069 IF p_x_mr_header_rec.DML_OPERATION='U' and p_x_mr_header_rec.MR_STATUS_CODE='APPROVAL_REJECTED'
2070 THEN
2071 p_x_mr_header_rec.mr_status_code:='DRAFT';
2072 END IF;
2073
2074 -- Convert Value to id.
2075
2076 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
2077 THEN
2078
2079 TRANSLATE_VALUE_ID
2080 (
2081 x_return_status =>x_return_Status,
2082 p_x_mr_header_rec =>p_x_mr_header_rec);
2083 END IF;
2084
2085 l_msg_count := FND_MSG_PUB.count_msg;
2086
2087 IF l_msg_count > 0
2088 THEN
2089 x_msg_count := l_msg_count;
2090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2091 RAISE FND_API.G_EXC_ERROR;
2092 END IF;
2093
2094
2095
2096 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
2097 THEN
2098
2099 VALIDATE_MR_HEADER
2100 (
2101 x_return_status =>x_return_Status,
2102 p_mr_header_rec =>p_x_mr_header_rec
2103 );
2104
2105 END IF;
2106
2107 l_msg_count := FND_MSG_PUB.count_msg;
2108
2109 IF l_msg_count > 0
2110 THEN
2111 x_msg_count := l_msg_count;
2112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2113 RAISE FND_API.G_EXC_ERROR;
2114 END IF;
2115
2116
2117 AHL_MR_HEADERS_PKG.UPDATE_ROW (
2118 X_MR_HEADER_ID =>p_x_mr_header_rec.mr_header_id,
2119 X_OBJECT_VERSION_NUMBER =>p_x_mr_header_rec.object_version_number,
2120 X_CATEGORY_CODE =>p_x_mr_header_rec.CATEGORY_CODE,
2121 X_SERVICE_TYPE_CODE =>p_x_mr_header_rec.SERVICE_TYPE_CODE,
2122 X_MR_STATUS_CODE =>p_x_mr_header_rec.MR_STATUS_CODE,
2123 X_IMPLEMENT_STATUS_CODE =>p_x_mr_header_rec.IMPLEMENT_STATUS_CODE,
2124 X_REPETITIVE_FLAG =>p_x_mr_header_rec.REPETITIVE_FLAG,
2125 X_SHOW_REPETITIVE_CODE =>p_x_mr_header_rec.SHOW_REPETITIVE_CODE,
2126 X_WHICHEVER_FIRST_CODE =>p_x_mr_header_rec.WHICHEVER_FIRST_CODE,
2127 X_COPY_ACCOMPLISHMENT_FLAG =>p_x_mr_header_rec.COPY_ACCOMPLISHMENT_FLAG,
2128 X_PROGRAM_TYPE_CODE =>p_x_mr_header_rec.PROGRAM_TYPE_CODE ,
2129 X_PROGRAM_SUBTYPE_CODE =>p_x_mr_header_rec.PROGRAM_SUBTYPE_CODE,
2130 X_EFFECTIVE_FROM =>p_x_mr_header_rec.EFFECTIVE_FROM,
2131 X_EFFECTIVE_TO =>NVL(p_x_mr_header_rec.EFFECTIVE_TO,NULL),
2132 X_REVISION =>p_x_mr_header_rec.REVISION,
2133 X_ATTRIBUTE_CATEGORY =>p_x_mr_header_rec.ATTRIBUTE_CATEGORY,
2134 X_ATTRIBUTE1 =>p_x_mr_header_rec.ATTRIBUTE1,
2135 X_ATTRIBUTE2 =>p_x_mr_header_rec.ATTRIBUTE2,
2136 X_ATTRIBUTE3 =>p_x_mr_header_rec.ATTRIBUTE3,
2137 X_ATTRIBUTE4 =>p_x_mr_header_rec.ATTRIBUTE4,
2138 X_ATTRIBUTE5 =>p_x_mr_header_rec.ATTRIBUTE5,
2139 X_ATTRIBUTE6 =>p_x_mr_header_rec.ATTRIBUTE6,
2140 X_ATTRIBUTE7 =>p_x_mr_header_rec.ATTRIBUTE7,
2141 X_ATTRIBUTE8 =>p_x_mr_header_rec.ATTRIBUTE8,
2142 X_ATTRIBUTE9 =>p_x_mr_header_rec.ATTRIBUTE9,
2143 X_ATTRIBUTE10 =>p_x_mr_header_rec.ATTRIBUTE10,
2144 X_ATTRIBUTE11 =>p_x_mr_header_rec.ATTRIBUTE11,
2145 X_ATTRIBUTE12 =>p_x_mr_header_rec.ATTRIBUTE12,
2146 X_ATTRIBUTE13 =>p_x_mr_header_rec.ATTRIBUTE13,
2147 X_ATTRIBUTE14 =>p_x_mr_header_rec.ATTRIBUTE14,
2148 X_ATTRIBUTE15 =>p_x_mr_header_rec.ATTRIBUTE15,
2149 X_TITLE =>p_x_mr_header_rec.TITLE,
2150 X_VERSION_NUMBER =>p_x_mr_header_rec.VERSION_NUMBER,
2151 X_PRECEDING_MR_HEADER_ID =>p_x_mr_header_rec.PRECEDING_MR_HEADER_ID,
2152 X_SERVICE_REQUEST_TEMPLATE_ID =>p_x_mr_header_rec.SERVICE_REQUEST_TEMPLATE_ID,
2153 X_TYPE_CODE =>p_x_mr_header_rec.TYPE_CODE,
2154 X_DOWN_TIME =>p_x_mr_header_rec.DOWN_TIME,
2155 X_UOM_CODE =>p_x_mr_header_rec.UOM_CODE,
2156 X_DESCRIPTION =>p_x_mr_header_rec.DESCRIPTION,
2157 X_COMMENTS =>p_x_mr_header_rec.COMMENTS,
2158 X_SPACE_CATEGORY_CODE =>p_x_mr_header_rec.SPACE_CATEGORY_CODE,
2159 X_QA_INSPECTION_TYPE_CODE =>p_x_mr_header_rec.QA_INSPECTION_TYPE_CODE,
2160 X_BILLING_ITEM_ID =>p_x_mr_header_rec.BILLING_ITEM_ID,
2161 X_AUTO_SIGNOFF_FLAG =>p_x_mr_header_rec.AUTO_SIGNOFF_FLAG,
2162 X_COPY_INIT_ACCOMPL_FLAG =>p_x_mr_header_rec.COPY_INIT_ACCOMPL_FLAG,
2163 X_COPY_DEFERRALS_FLAG =>p_x_mr_header_rec.COPY_DEFERRALS_FLAG,
2164 X_LAST_UPDATE_DATE =>sysdate,
2165 X_LAST_UPDATED_BY =>fnd_global.user_id,
2166 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
2167
2168 l_msg_count := FND_MSG_PUB.count_msg;
2169 IF l_msg_count > 0 THEN
2170 X_msg_count := l_msg_count;
2171 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2172 RAISE FND_API.G_EXC_ERROR;
2173 END IF;
2174
2175 -- Check For Cycli Association
2176
2177 IF (p_x_mr_header_rec.PRECEDING_MR_HEADER_ID IS NOT NULL OR p_x_mr_header_rec.PRECEDING_MR_HEADER_ID<>fnd_api.g_miss_num) and p_x_mr_header_rec.dml_operation<>'D'
2178 THEN
2179 CHECK_CYCLIC_ASSOCIATION
2180 (
2181 p_api_version,
2182 p_init_msg_list,
2183 p_validation_level,
2184 x_return_status,
2185 x_msg_count,
2186 x_msg_data,
2187 p_x_mr_header_rec.MR_HEADER_ID,
2188 p_x_mr_header_rec.PRECEDING_MR_HEADER_ID,
2189 p_x_mr_header_rec.PRECEDING_MR_TITLE
2190 );
2191 END IF;
2192
2193 l_msg_count := FND_MSG_PUB.count_msg;
2194 IF l_msg_count > 0 THEN
2195 X_msg_count := l_msg_count;
2196 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2197 RAISE FND_API.G_EXC_ERROR;
2198 END IF;
2199
2200
2201 IF FND_API.TO_BOOLEAN(p_commit) THEN
2202 COMMIT;
2203 END IF;
2204 -- Debug info
2205
2206 -- AHL_DEBUG_PUB.debug( 'End of Private api UPDATE_MR_HEADER','+MR_header+');
2207
2208 -- Check if API is called in debug mode. If yes, disable debug.
2209
2210 IF G_DEBUG='Y' THEN
2211 AHL_DEBUG_PUB.disable_debug;
2212 END IF;
2213
2214 EXCEPTION
2215
2216 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2217 ROLLBACK TO UPDATE_MR_HEADER_PVT;
2218 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2219 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2220 p_count => x_msg_count,
2221 p_data => x_msg_data);
2222 IF G_DEBUG='Y' THEN
2223 AHL_DEBUG_PUB.disable_debug;
2224 END IF;
2225
2226 WHEN FND_API.G_EXC_ERROR THEN
2227 ROLLBACK TO UPDATE_MR_HEADER_PVT;
2228 X_return_status := FND_API.G_RET_STS_ERROR;
2229 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2230 p_count => x_msg_count,
2231 p_data => X_msg_data);
2232 IF G_DEBUG='Y' THEN
2233 AHL_DEBUG_PUB.disable_debug;
2234 END IF;
2235 WHEN OTHERS THEN
2236 ROLLBACK TO UPDATE_MR_HEADER_PVT;
2237 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2238 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2239 THEN
2240 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
2241 p_procedure_name =>l_api_name,
2242 p_error_text => SUBSTR(SQLERRM,1,240));
2243 END IF;
2244 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2245 p_count => x_msg_count,
2246 p_data => X_msg_data);
2247 IF G_DEBUG='Y' THEN
2248 AHL_DEBUG_PUB.disable_debug;
2249 END IF;
2250 END;
2251
2252 PROCEDURE VALIDATE_DEL_MR_HEADER
2253 (
2254 p_mr_header_id IN NUMBER,
2255 p_object_version_number IN NUMBER,
2256 x_return_status OUT NOCOPY VARCHAR2
2257 )
2258 As
2259 L_ERR_FLAG NUMBER(2);
2260 L_RETURN_STATUS VARCHAR2(1):='S';
2261 CURSOR GetHeaderDet(C_MR_HEADER_ID NUMBER,C_OBJECT_VERSION_NUMBER NUMBER)
2262 IS
2263 --AMSRINIV.Bug 4916286. Tuning below commented query for improving performance
2264 SELECT
2265 mr_status_code
2266 FROM
2267 ahl_mr_headers_b
2268 WHERE
2269 application_usg_code = RTRIM(LTRIM(fnd_profile.value('AHL_APPLN_USAGE'))) AND
2270 mr_header_id=C_MR_HEADER_ID AND
2271 object_version_number=C_OBJECT_VERSION_NUMBER;
2272
2273 /*SELECT MR_STATUS_CODE
2274 FROM AHL_MR_HEADERS_V
2275 WHERE MR_HEADER_ID=C_MR_HEADER_ID
2276 AND OBJECT_VERSION_NUMBER=C_OBJECT_VERSION_NUMBER;*/
2277 l_rec GetHeaderDet%rowtype;
2278 l_appln_code VARCHAR2(30);
2279 BEGIN
2280
2281 IF G_APPLN_USAGE IS NULL
2282 THEN
2283 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
2284 FND_MSG_PUB.ADD;
2285 RETURN;
2286 END IF;
2287
2288 IF P_MR_HEADER_ID IS NULL OR P_MR_HEADER_ID=FND_API.G_MISS_NUM
2289 THEN
2290 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
2291 FND_MSG_PUB.ADD;
2292 L_ERR_FLAG:=1;
2293 END IF;
2294
2295 IF P_OBJECT_VERSION_NUMBER IS NULL OR P_OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM
2296 THEN
2297 FND_MESSAGE.SET_NAME('AHL','AHL_COM_OBJECT_VERS_NUM_NULL');
2298 FND_MSG_PUB.ADD;
2299 L_ERR_FLAG:=1;
2300 END IF;
2301
2302 IF L_ERR_FLAG=1
2303 THEN
2304 RETURN;
2305 END IF;
2306 Open GetHeaderDet(P_MR_HEADER_ID,P_OBJECT_VERSION_NUMBER);
2307 Fetch GetHeaderDet into l_rec;
2308 If GetHeaderDet%NotFound Then
2309 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_INVALID');
2310 FND_MSG_PUB.ADD;
2311 Elsif GetHeaderDet%Found and (l_rec.mr_status_code<>'DRAFT' AND
2312 l_rec.mr_status_code<>'APPROVAL_REJECTED')
2313 Then
2314 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INVALID_MR_STATUS');
2315 FND_MSG_PUB.ADD;
2316 End if;
2317 Close GetHeaderDet;
2318 END;
2319 --
2320
2321 PROCEDURE DELETE_MR_HEADER
2322 (
2323 p_api_version IN NUMBER:=1.0,
2324 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
2325 p_commit IN VARCHAR2:= FND_API.G_FALSE,
2326 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
2327 p_default IN VARCHAR2:= FND_API.G_FALSE ,
2328 p_module_type IN VARCHAR2:=NULL,
2329 x_return_status OUT NOCOPY VARCHAR2,
2330 x_msg_count OUT NOCOPY NUMBER,
2331 x_msg_data OUT NOCOPY VARCHAR2,
2332 p_mr_header_id IN NUMBER,
2333 p_object_version_number IN NUMBER)
2334 as
2335 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_MR_HEADER';
2336 l_api_version CONSTANT NUMBER := 1.0;
2337 l_num_rec NUMBER;
2338 l_msg_count NUMBER;
2339 l_msg_data VARCHAR2(2000);
2340 l_return_status VARCHAR2(1);
2341 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
2342 Cursor CurGetRoutes(c_mr_header_id number)
2343 Is
2344 Select * from ahl_mr_Routes
2345 Where mr_header_id=c_mr_header_id;
2346 l_mr_route_rec CurGetRoutes%rowtype;
2347
2348 Cursor curGetMrEffects(c_mr_header_id number)
2349 Is
2350 Select * from ahl_mr_Effectivities
2351 Where mr_header_id=c_mr_header_id;
2352 L_MR_EFFECT_REC curGetMrEffects%ROWTYPE;
2353
2354 Cursor curGetMrEffDetls(c_mr_effectivity_id number)
2355 Is
2356 Select * from ahl_mr_effectivity_dtls
2357 Where mr_effectivity_id=c_mr_effectivity_id;
2358
2359 Cursor curGetMrEffIntervals(c_mr_effectivity_id number)
2360 Is
2361 Select * from ahl_mr_intervals
2362 Where mr_effectivity_id=c_mr_effectivity_id;
2363
2364 Cursor curGetMrdocAssociations(c_mr_header_id number)
2365 Is
2366 Select * from AHL_DOC_TITLE_ASSOS_B
2367 Where ASO_OBJECT_ID=C_MR_HEADER_ID
2368 And ASO_OBJECT_TYPE_CODE='MR';
2369 l_mr_doc_rec curGetMrdocAssociations%rowtype;
2370
2371 BEGIN
2372 SAVEPOINT DELETE_MR_HEADER_PVT;
2373
2374 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2375 p_api_version,
2376 l_api_name,G_PKG_NAME) THEN
2377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2378 END IF;
2379
2380 IF FND_API.to_boolean(P_init_msg_list)
2381 THEN
2382 FND_MSG_PUB.initialize;
2383 END IF;
2384
2385
2386 x_return_status:=FND_API.G_RET_STS_SUCCESS;
2387
2388 IF G_DEBUG='Y' THEN
2389 AHL_DEBUG_PUB.enable_debug;
2390 END IF;
2391
2392 -- Debug info.
2393 IF G_DEBUG='Y' THEN
2394 AHL_DEBUG_PUB.debug( 'AHL_FMP_MR_HEADERS_PVT.','+DELETE_MR_HEADER+');
2395 END IF;
2396
2397 -- IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
2398 -- THEN
2399 VALIDATE_DEL_MR_HEADER
2400 (
2401 p_mr_header_id =>p_mr_header_id,
2402 p_object_version_number =>p_object_version_number,
2403 x_return_status =>L_return_Status
2404 );
2405
2406 l_msg_count := FND_MSG_PUB.count_msg;
2407 IF l_msg_count > 0
2408 THEN
2409 x_msg_count := l_msg_count;
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2411 RAISE FND_API.G_EXC_ERROR;
2412 END IF;
2413
2414 -- END IF;
2415
2416 Open CurGetRoutes(p_mr_header_id);
2417 Loop
2418 fetch CurGetRoutes into l_mr_route_rec;
2419 if CurGetRoutes%NotFound
2420 Then
2421 Exit;
2422 else
2423 Delete AHL_MR_ROUTE_SEQUENCES
2424 WHERE MR_ROUTE_ID=l_mr_route_rec.mr_route_id or
2425 RELATED_MR_ROUTE_ID=l_mr_route_rec.mr_route_id;
2426
2427 Delete AHL_MR_ROUTES
2428 WHERE MR_ROUTE_ID=l_mr_route_rec.mr_route_id;
2429 End if;
2430 End loop;
2431 Close CurGetRoutes;
2432
2433 Open CurGetMrEffects(p_mr_header_id);
2434 Loop
2435 Fetch CurGetMrEffects into l_mr_effect_Rec;
2436 If CurGetMrEffects%NotFound
2437 Then
2438 Exit;
2439 Else
2440 Delete AHL_MR_EFFECTIVITY_DTLS
2441 Where MR_effectivity_id=l_mr_effect_rec.MR_EFFECTIVITY_ID;
2442
2443 Delete AHL_MR_INTERVALS
2444 Where MR_effectivity_id=l_mr_effect_rec.mr_effectivity_id;
2445 Delete AHL_MR_EFFECTIVITIES
2446 Where mr_effectivity_id=l_mr_effect_rec.mr_effectivity_id;
2447 End if;
2448
2449 End loop;
2450 Close CurGetMrEffects;
2451
2452 Open curGetMrdocAssociations(p_mr_header_id);
2453 loop
2454 Fetch curGetMrdocAssociations into l_mr_doc_rec;
2455 If curGetMrdocAssociations%Found
2456 Then
2457 Delete from AHL_DOC_TITLE_ASSOS_TL
2458 Where DOC_TITLE_ASSO_ID = l_mr_doc_rec.DOC_TITLE_ASSO_ID;
2459
2460 Delete from AHL_DOC_TITLE_ASSOS_B
2461 Where DOC_TITLE_ASSO_ID = l_mr_doc_rec.DOC_TITLE_ASSO_ID;
2462 Elsif curGetMrdocAssociations%NotFound
2463 Then
2464 Exit;
2465 End if;
2466 End loop;
2467 Close curGetMrdocAssociations;
2468
2469 Delete AHL_MR_RELATIONSHIPS
2470 Where mr_header_id=p_mr_header_id
2471 or RELATED_MR_HEADER_ID=P_MR_HEADER_ID;
2472
2473 l_msg_count := FND_MSG_PUB.count_msg;
2474 IF l_msg_count > 0
2475 THEN
2476 x_msg_count := l_msg_count;
2477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2478 RAISE FND_API.G_EXC_ERROR;
2479 END IF;
2480
2481 AHL_MR_HEADERS_PKG.DELETE_ROW
2482 (
2483 X_MR_HEADER_ID =>p_mr_header_id
2484 );
2485
2486 l_msg_count := FND_MSG_PUB.count_msg;
2487 IF l_msg_count > 0 THEN
2488 X_msg_count := l_msg_count;
2489 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2490 RAISE FND_API.G_EXC_ERROR;
2491 END IF;
2492
2493 IF G_DEBUG='Y' THEN
2494 AHL_DEBUG_PUB.disable_debug;
2495 END IF;
2496
2497 IF FND_API.TO_BOOLEAN(p_commit) THEN
2498 COMMIT;
2499 END IF;
2500 EXCEPTION
2501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2502 ROLLBACK TO DELETE_MR_HEADER_PVT;
2503 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2504 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2505 p_count => x_msg_count,
2506 p_data => x_msg_data);
2507 IF G_DEBUG='Y' THEN
2508 AHL_DEBUG_PUB.disable_debug;
2509 END IF;
2510
2511 WHEN FND_API.G_EXC_ERROR THEN
2512 ROLLBACK TO DELETE_MR_HEADER_PVT;
2513 X_return_status := FND_API.G_RET_STS_ERROR;
2514 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2515 p_count => x_msg_count,
2516 p_data => X_msg_data);
2517 IF G_DEBUG='Y' THEN
2518 AHL_DEBUG_PUB.disable_debug;
2519 END IF;
2520
2521 WHEN OTHERS THEN
2522 ROLLBACK TO DELETE_MR_HEADER_PVT;
2523 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2524 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2525 THEN
2526 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
2527 p_procedure_name =>l_api_name,
2528 p_error_text => SUBSTR(SQLERRM,1,240));
2529 END IF;
2530 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2531 p_count => x_msg_count,
2532 p_data => X_msg_data);
2533 IF G_DEBUG='Y' THEN
2534 AHL_DEBUG_PUB.disable_debug;
2535 END IF;
2536
2537 END DELETE_MR_HEADER;
2538
2539 END AHL_FMP_MR_HEADER_PVT;