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