[Home] [Help]
PACKAGE BODY: APPS.AHL_APPR_DEPT_SHIFTS_PUB
Source
1 PACKAGE BODY AHL_APPR_DEPT_SHIFTS_PUB AS
2 /* $Header: AHLPDSHB.pls 120.1 2007/12/05 20:40:00 rbhavsar ship $ */
3 --
4 G_PKG_NAME VARCHAR2(30):='AHL_APPR_DEPT_SHIFTS_PUB';
5
6 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
7 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_APPR_DEPT_SHIFTS_PUB';
8 --
9 PROCEDURE DEFAULT_MISSING_ATTRIBS
10 (
11 p_x_appr_deptshift_rec IN OUT NOCOPY AHL_APPR_DEPT_SHIFTS_PUB.appr_deptshift_rec
12 )
13 AS
14 BEGIN
15 IF p_x_appr_deptshift_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM
16 THEN
17 p_x_appr_deptshift_rec.OBJECT_VERSION_NUMBER:=NULL;
18 END IF;
19
20 IF p_x_appr_deptshift_rec.DEPARTMENT_ID= FND_API.G_MISS_NUM
21 THEN
22 p_x_appr_deptshift_rec.DEPARTMENT_ID:=NULL;
23 END IF;
24
25 IF p_x_appr_deptshift_rec.SHIFT_NUM= FND_API.G_MISS_NUM
26 THEN
27 p_x_appr_deptshift_rec.SHIFT_NUM:=NULL;
28 END IF;
29
30 IF p_x_appr_deptshift_rec.SEQ_NUM= FND_API.G_MISS_NUM
31 THEN
32 p_x_appr_deptshift_rec.SEQ_NUM:=NULL;
33 END IF;
34
35 IF p_x_appr_deptshift_rec.CALENDAR_CODE= FND_API.G_MISS_CHAR
36 THEN
37 p_x_appr_deptshift_rec.CALENDAR_CODE:=NULL;
38 END IF;
39
40 IF p_x_appr_deptshift_Rec.ATTRIBUTE_CATEGORY IS NULL OR p_x_appr_deptshift_Rec.ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
41 THEN
42 p_x_appr_deptshift_Rec.ATTRIBUTE_CATEGORY:=NULL;
43 END IF;
44
45 IF p_x_appr_deptshift_Rec.ATTRIBUTE1=FND_API.G_MISS_CHAR
46 THEN
47 p_x_appr_deptshift_Rec.ATTRIBUTE1:=NULL;
48 END IF;
49
50 IF p_x_appr_deptshift_Rec.ATTRIBUTE2=FND_API.G_MISS_CHAR
51 THEN
52 p_x_appr_deptshift_Rec.ATTRIBUTE2:=NULL;
53 END IF;
54
55 IF p_x_appr_deptshift_Rec.ATTRIBUTE3=FND_API.G_MISS_CHAR
56 THEN
57 p_x_appr_deptshift_Rec.ATTRIBUTE3:=NULL;
58 END IF;
59
60 IF p_x_appr_deptshift_Rec.ATTRIBUTE4 IS NULL OR p_x_appr_deptshift_Rec.ATTRIBUTE4=FND_API.G_MISS_CHAR
61 THEN
62 p_x_appr_deptshift_Rec.ATTRIBUTE4:=NULL;
63 END IF;
64
65 IF p_x_appr_deptshift_Rec.ATTRIBUTE5=FND_API.G_MISS_CHAR
66 THEN
67 p_x_appr_deptshift_Rec.ATTRIBUTE5:=NULL;
68 END IF;
69
70 IF p_x_appr_deptshift_Rec.ATTRIBUTE6=FND_API.G_MISS_CHAR
71 THEN
72 p_x_appr_deptshift_Rec.ATTRIBUTE6:=NULL;
73 END IF;
74
75 IF p_x_appr_deptshift_Rec.ATTRIBUTE7=FND_API.G_MISS_CHAR
76 THEN
77 p_x_appr_deptshift_Rec.ATTRIBUTE7:=NULL;
78 END IF;
79
80 IF p_x_appr_deptshift_Rec.ATTRIBUTE8=FND_API.G_MISS_CHAR
81 THEN
82 p_x_appr_deptshift_Rec.ATTRIBUTE8:=NULL;
83 END IF;
84
85 IF p_x_appr_deptshift_Rec.ATTRIBUTE9=FND_API.G_MISS_CHAR
86 THEN
87 p_x_appr_deptshift_Rec.ATTRIBUTE9:=NULL;
88 END IF;
89
90 IF p_x_appr_deptshift_Rec.ATTRIBUTE10=FND_API.G_MISS_CHAR
91 THEN
92 p_x_appr_deptshift_Rec.ATTRIBUTE10:=NULL;
93 END IF;
94
95 IF p_x_appr_deptshift_Rec.ATTRIBUTE11=FND_API.G_MISS_CHAR
96 THEN
97 p_x_appr_deptshift_Rec.ATTRIBUTE11:=NULL;
98 END IF;
99
100 IF p_x_appr_deptshift_Rec.ATTRIBUTE12 IS NULL OR p_x_appr_deptshift_Rec.ATTRIBUTE12=FND_API.G_MISS_CHAR
101 THEN
102 p_x_appr_deptshift_Rec.ATTRIBUTE12:=NULL;
103 END IF;
104
105 IF p_x_appr_deptshift_Rec.ATTRIBUTE13=FND_API.G_MISS_CHAR
106 THEN
107 p_x_appr_deptshift_Rec.ATTRIBUTE13:=NULL;
108 END IF;
109
110 IF p_x_appr_deptshift_Rec.ATTRIBUTE14=FND_API.G_MISS_CHAR
111 THEN
112 p_x_appr_deptshift_Rec.ATTRIBUTE14:=NULL;
113 END IF;
114
115 IF p_x_appr_deptshift_Rec.ATTRIBUTE15=FND_API.G_MISS_CHAR
116 THEN
117 p_x_appr_deptshift_Rec.ATTRIBUTE15:=NULL;
118 END IF;
119
120 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
121 IF p_x_appr_deptshift_rec.SUBINVENTORY = FND_API.G_MISS_CHAR
122 THEN
123 p_x_appr_deptshift_rec.SUBINVENTORY := NULL;
124 END IF;
125
126 IF p_x_appr_deptshift_rec.INV_LOCATOR_ID = FND_API.G_MISS_NUM
127 THEN
128 p_x_appr_deptshift_rec.INV_LOCATOR_ID := NULL;
129 END IF;
130
131 IF p_x_appr_deptshift_rec.LOCATOR_SEGMENTS = FND_API.G_MISS_CHAR
132 THEN
133 p_x_appr_deptshift_rec.LOCATOR_SEGMENTS := NULL;
134 END IF;
135 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
136
137 END;
138
139 PROCEDURE TRANSLATE_VALUE_ID
140 (
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 p_x_appr_deptshift_rec IN OUT NOCOPY AHL_APPR_DEPT_SHIFTS_PUB.APPR_DEPTSHIFT_REC
145 )
146 as
147 CURSOR get_org_id(C_NAME VARCHAR2)
148 IS
149 SELECT ORGANIZATION_ID
150 FROM HR_ALL_ORGANIZATION_UNITS
151 WHERE NAME=C_NAME;
152
153 CURSOR get_org_dept_id(C_ORG_ID NUMBER,C_DESCRIPTION VARCHAR2)
154 IS
155 SELECT DEPARTMENT_ID
156 FROM BOM_DEPARTMENTS_V
157 WHERE ORGANIZATION_ID=C_ORG_ID
158 AND DESCRIPTION=C_DESCRIPTION;
159
160 CURSOR get_bom_calendar(C_CALENDAR VARCHAR2)
161 IS
162 SELECT CALENDAR_CODE
163 FROM BOM_CALENDARS
164 WHERE DESCRIPTION=C_CALENDAR;
165
166
167 CURSOR get_bom_shift_num(C_CALENDAR_CODE VARCHAR2,C_SHIFT_NUM NUMBER)
168 IS
169 SELECT SHIFT_NUM
170 FROM BOM_SHIFT_TIMES
171 WHERE CALENDAR_CODE=C_CALENDAR_CODE
172 AND SHIFT_NUM=C_SHIFT_NUM;
173
174 CURSOR get_bom_workdays(C_CALENDAR_CODE VARCHAR2,C_SHIFT NUMBER,C_DESCRIPTION VARCHAR2)
175 IS
176 SELECT SEQ_NUM
177 FROM BOM_WORKDAY_PATTERNS
178 WHERE CALENDAR_CODE=C_CALENDAR_CODE
179 AND SHIFT_NUM=C_SHIFT
180 AND DESCRIPTION=C_DESCRIPTION;
181
182 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
183 CURSOR get_inv_loc_id(C_ORGANIZATION_ID NUMBER, C_SUBINVENTORY VARCHAR2, C_LOCATOR_SEGMENTS VARCHAR2)
184 IS
185 SELECT INVENTORY_LOCATION_ID
186 FROM MTL_ITEM_LOCATIONS_KFV
187 WHERE ORGANIZATION_ID = C_ORGANIZATION_ID
188 AND SUBINVENTORY_CODE = C_SUBINVENTORY
189 AND CONCATENATED_SEGMENTS = C_LOCATOR_SEGMENTS;
190 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
191 l_lookup_code VARCHAR2(30);
192 l_num_rec NUMBER;
193 l_msg_count NUMBER;
194 l_msg_data VARCHAR2(2000);
195 l_return_status VARCHAR2(1);
196 l_mr_header_id number:=0;
197 l_lookup_var varchar2(1);
198 l_object_version_number NUMBER;
199 l_check_flag VARCHAR2(1):='N';
200 l_counter NUMBER:=0;
201 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
202 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.TRANSLATE_VALUE_ID';
203 BEGIN
204 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
205 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
206 END IF;
207 x_return_status:=FND_API.G_RET_STS_SUCCESS;
208 IF p_x_appr_deptshift_rec.ORGANIZATION_NAME IS NULL OR p_x_appr_deptshift_rec.ORGANIZATION_NAME=FND_API.G_MISS_CHAR
209 THEN
210 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_ORG_NAME_NULL');
211 FND_MSG_PUB.ADD;
212 l_check_flag:='N';
213 ELSE
214 l_check_flag:='Y';
215 OPEN get_org_id(p_x_appr_deptshift_rec.ORGANIZATION_NAME);
216 FETCH get_org_id INTO p_x_appr_deptshift_rec.ORGANIZATION_ID;
217 IF get_org_id%NOTFOUND
218 THEN
219 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_ORG_NAME_INVALID');
220 FND_MSG_PUB.ADD;
221 l_check_flag:='N';
222 END IF;
223 CLOSE get_org_id;
224 END IF;
225
226 l_check_flag:='N';
227 IF p_x_appr_deptshift_rec.DEPT_DESCRIPTION IS NULL OR p_x_appr_deptshift_rec.DEPT_DESCRIPTION=FND_API.G_MISS_CHAR
228 THEN
229 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_DEPT_NAME_NULL');
230 FND_MSG_PUB.ADD;
231 l_check_flag:='N';
232 ELSE
233 l_check_flag:='Y';
234 END IF;
235
236 IF l_check_flag='Y'
237 THEN
238 OPEN get_org_dept_id(p_x_appr_deptshift_rec.ORGANIZATION_ID,p_x_appr_deptshift_rec.DEPT_DESCRIPTION);
239 FETCH get_org_dept_id INTO p_x_appr_deptshift_rec.DEPARTMENT_ID;
240 IF get_org_dept_id%NOTFOUND
241 THEN
242 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_DEPT_NAME_INVALID');
243 FND_MSG_PUB.ADD;
244 l_check_flag:='N';
245 END IF;
246 CLOSE get_org_dept_id;
247 END IF;
248
249
250 l_check_flag:='N';
251 IF p_x_appr_deptshift_rec.CALENDAR_DESCRIPTION IS NULL OR p_x_appr_deptshift_rec.CALENDAR_DESCRIPTION=FND_API.G_MISS_CHAR
252 THEN
253 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_CALENDER_NAME_NULL');
254 FND_MSG_PUB.ADD;
255 l_check_flag:='N';
256 ELSE
257 l_check_flag:='Y';
258 END IF;
259
260
261 IF l_check_flag='Y'
262 THEN
263 OPEN get_bom_calendar(p_x_appr_deptshift_rec.CALENDAR_DESCRIPTION);
264 FETCH get_bom_calendar INTO p_x_appr_deptshift_rec.CALENDAR_CODE;
265 IF get_bom_calendar%NOTFOUND
266 THEN
267 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_CALENDER_INVALID');
268 FND_MSG_PUB.ADD;
269 l_check_flag:='N';
270 ELSE
271 l_check_flag:='Y';
272 END IF;
273 CLOSE get_bom_calendar;
274 END IF;
275
276
277 l_check_flag:='N';
278 IF p_x_appr_deptshift_rec.SHIFT_NUM IS NULL OR p_x_appr_deptshift_rec.SHIFT_NUM=FND_API.G_MISS_NUM
279 THEN
280 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_SHIFT_NUMBER_NULL');
281 FND_MSG_PUB.ADD;
282 l_check_flag:='N';
283 ELSE
284 l_check_flag:='Y';
285 END IF;
286
287 IF l_check_flag='Y'
288 THEN
289 OPEN get_bom_shift_num(p_x_appr_deptshift_rec.CALENDAR_CODE,p_x_appr_deptshift_rec.SHIFT_NUM);
290 FETCH get_bom_shift_num INTO p_x_appr_deptshift_rec.SHIFT_NUM;
291 IF get_bom_shift_num%NOTFOUND
292 THEN
293 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_SHIFT_NUMBER_INVALID');
294 FND_MSG_PUB.ADD;
295 l_check_flag:='N';
296 ELSE
297 l_check_flag:='Y';
298 END IF;
299 CLOSE get_bom_shift_num;
300 END IF;
301
302
303 l_check_flag:='N';
304 IF p_x_appr_deptshift_rec.SEQ_NAME IS NULL OR p_x_appr_deptshift_rec.SEQ_NAME=FND_API.G_MISS_CHAR
305 THEN
306 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_WORK_DAYS_NULL');
307 FND_MSG_PUB.ADD;
308 l_check_flag:='N';
309 ELSE
310 l_check_flag:='Y';
311 END IF;
312 IF l_check_flag='Y'
313 THEN
314 OPEN get_bom_workdays(p_x_appr_deptshift_rec.CALENDAR_CODE,p_x_appr_deptshift_rec.SHIFT_NUM,p_x_appr_deptshift_rec.SEQ_NAME);
315 FETCH get_bom_workdays INTO p_x_appr_deptshift_rec.SEQ_NUM;
316 IF get_bom_workdays%NOTFOUND
317 THEN
318 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_SEQ_DESCRIP_INVALID');
319 FND_MSG_PUB.ADD;
320 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
321 l_check_flag:='N';
322 END IF;
323 CLOSE get_bom_workdays;
324 END IF;
325
326 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
327 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_x_appr_deptshift_rec.ORGANIZATION_ID = ' || p_x_appr_deptshift_rec.ORGANIZATION_ID ||
329 ', p_x_appr_deptshift_rec.SUBINVENTORY = ' || p_x_appr_deptshift_rec.SUBINVENTORY ||
330 ', p_x_appr_deptshift_rec.INV_LOCATOR_ID = ' || p_x_appr_deptshift_rec.INV_LOCATOR_ID ||
331 ', p_x_appr_deptshift_rec.LOCATOR_SEGMENTS = ' || p_x_appr_deptshift_rec.LOCATOR_SEGMENTS);
332 END IF;
333 -- Convert G_MISS to NULL since this API is called only for creating
334 IF (p_x_appr_deptshift_rec.SUBINVENTORY = FND_API.G_MISS_CHAR) THEN
335 p_x_appr_deptshift_rec.SUBINVENTORY := NULL;
336 END IF;
337 IF (p_x_appr_deptshift_rec.LOCATOR_SEGMENTS = FND_API.G_MISS_CHAR) THEN
338 p_x_appr_deptshift_rec.LOCATOR_SEGMENTS := NULL;
339 END IF;
340 IF (p_x_appr_deptshift_rec.INV_LOCATOR_ID = FND_API.G_MISS_NUM) THEN
341 p_x_appr_deptshift_rec.INV_LOCATOR_ID := NULL;
342 END IF;
343 IF (l_check_flag = 'Y' AND
344 p_x_appr_deptshift_rec.ORGANIZATION_ID IS NOT NULL AND
345 p_x_appr_deptshift_rec.SUBINVENTORY IS NOT NULL AND
346 p_x_appr_deptshift_rec.INV_LOCATOR_ID IS NULL AND
347 p_x_appr_deptshift_rec.LOCATOR_SEGMENTS IS NOT NULL) THEN
348 OPEN get_inv_loc_id(C_ORGANIZATION_ID => p_x_appr_deptshift_rec.ORGANIZATION_ID,
349 C_SUBINVENTORY => p_x_appr_deptshift_rec.SUBINVENTORY,
350 C_LOCATOR_SEGMENTS => p_x_appr_deptshift_rec.LOCATOR_SEGMENTS);
351 FETCH get_inv_loc_id INTO p_x_appr_deptshift_rec.INV_LOCATOR_ID;
352 IF (get_inv_loc_id%NOTFOUND) THEN
353 p_x_appr_deptshift_rec.INV_LOCATOR_ID := 0;
354 END IF;
355 CLOSE get_inv_loc_id;
356 END IF;
357 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_x_appr_deptshift_rec.INV_LOCATOR_ID = ' || p_x_appr_deptshift_rec.INV_LOCATOR_ID);
359 END IF;
360
361 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
362 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
363 END IF;
364 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
365
366 EXCEPTION
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
369 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
373 WHEN FND_API.G_EXC_ERROR THEN
370 p_count => x_msg_count,
371 p_data => x_msg_data);
372
374 X_return_status := FND_API.G_RET_STS_ERROR;
375 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
376 p_count => x_msg_count,
377 p_data => X_msg_data);
378 WHEN OTHERS THEN
379 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
381 THEN
382 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
383 p_procedure_name => 'TRANSLATE_VALUE_ID',
384 p_error_text => SUBSTR(SQLERRM,1,240));
385 END IF;
386 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
387 p_count => x_msg_count,
388 p_data => x_msg_data);
389 END;
390
391 -- Start of Validate
392
393 PROCEDURE VALIDATE_APPR_DEPT_SHIFT
394 (
395 x_return_status OUT NOCOPY VARCHAR2,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2,
398 p_appr_deptshift_rec IN AHL_APPR_DEPT_SHIFTS_PUB.appr_deptshift_rec
399 )
400 as
401 l_counter NUMBER:=0;
402 l_prim_key NUMBER:=0;
403
404 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
405 CURSOR validate_subinventory(C_ORGANIZATION_ID NUMBER, C_SUBINVENTORY VARCHAR2) IS
406 SELECT status_id
407 FROM MTL_SECONDARY_INVENTORIES
408 WHERE organization_id = C_ORGANIZATION_ID
409 AND secondary_inventory_name = C_SUBINVENTORY;
410
411 CURSOR validate_inv_loc_id(C_ORGANIZATION_ID NUMBER, C_SUBINVENTORY VARCHAR2, C_INVENTORY_LOCATION_ID NUMBER) IS
412 SELECT 1
413 FROM MTL_ITEM_LOCATIONS
414 WHERE ORGANIZATION_ID = C_ORGANIZATION_ID
415 AND SUBINVENTORY_CODE = C_SUBINVENTORY
416 AND INVENTORY_LOCATION_ID = C_INVENTORY_LOCATION_ID
417 AND SEGMENT19 IS NULL
418 AND SEGMENT20 IS NULL;
419
420 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.VALIDATE_APPR_DEPT_SHIFT';
421 l_temp_num NUMBER;
422 l_status_id NUMBER;
423 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
424
425 BEGIN
426 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
428 END IF;
429 x_return_status:=FND_API.G_RET_STS_SUCCESS;
430
431 IF p_appr_deptshift_rec.DML_OPERATION='D'
432 THEN
433 IF p_appr_deptshift_rec.OBJECT_VERSION_NUMBER IS NULL OR p_appr_deptshift_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM
434 THEN
435 FND_MESSAGE.SET_NAME('AHL','AHL_COM_OBJECT_VERS_NUM_NULL');
436 FND_MSG_PUB.ADD;
437 END IF;
438
439 IF p_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID IS NULL OR p_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID=FND_API.G_MISS_NUM
440 THEN
441 FND_MESSAGE.SET_NAME('AHL','AHL_DEPARTMENT_SHIFTS_ID_NULL');
442 FND_MSG_PUB.ADD;
443 ELSE
444 SELECT AHL_DEPARTMENT_SHIFTS_ID INTO l_prim_key
445 FROM AHL_DEPARTMENT_SHIFTS
446 WHERE AHL_DEPARTMENT_SHIFTS_ID=p_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID;
447 END IF;
448 ELSE
452
449 Select COUNT(DEPARTMENT_ID) INTO l_counter
450 FROM AHL_DEPARTMENT_SHIFTS
451 WHERE DEPARTMENT_ID=p_appr_deptshift_rec.DEPARTMENT_ID;
453 IF l_counter>0
454 THEN
455 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_DEPT_EXISTS');
456 FND_MSG_PUB.ADD;
457 END IF;
458 END IF;
459
460 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
461 IF p_appr_deptshift_rec.DML_OPERATION='C' THEN
462 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
463 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_appr_deptshift_rec.ORGANIZATION_ID = ' || p_appr_deptshift_rec.ORGANIZATION_ID ||
464 ',p_appr_deptshift_rec.SUBINVENTORY = ' || p_appr_deptshift_rec.SUBINVENTORY ||
465 ',p_appr_deptshift_rec.INV_LOCATOR_ID = ' || p_appr_deptshift_rec.INV_LOCATOR_ID ||
466 ',p_appr_deptshift_rec.LOCATOR_SEGMENTS = ' || p_appr_deptshift_rec.LOCATOR_SEGMENTS);
467 END IF;
468 IF (p_appr_deptshift_rec.SUBINVENTORY IS NOT NULL) THEN
469 OPEN validate_subinventory(C_ORGANIZATION_ID => p_appr_deptshift_rec.ORGANIZATION_ID,
470 C_SUBINVENTORY => p_appr_deptshift_rec.SUBINVENTORY);
471 FETCH validate_subinventory INTO l_status_id;
472 IF (validate_subinventory%NOTFOUND) THEN
473 CLOSE validate_subinventory;
474 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_SUBINVENTORY');
475 FND_MESSAGE.Set_Token('INV', p_appr_deptshift_rec.SUBINVENTORY);
476 FND_MSG_PUB.ADD;
477 RAISE FND_API.G_EXC_ERROR;
478 ELSE
479 IF l_status_id in (NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE'), -1), NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'), -1)) THEN
480 CLOSE validate_subinventory;
481 FND_MESSAGE.SET_NAME('AHL', 'AHL_SUBINVENTORY_NOT_SVC');
482 FND_MESSAGE.Set_Token('INV', p_appr_deptshift_rec.SUBINVENTORY);
483 FND_MSG_PUB.ADD;
484 RAISE FND_API.G_EXC_ERROR;
485 END IF;
486 END IF;
487 CLOSE validate_subinventory;
488 END IF;
489
490 IF (p_appr_deptshift_rec.SUBINVENTORY IS NULL AND
491 (p_appr_deptshift_rec.INV_LOCATOR_ID IS NOT NULL OR p_appr_deptshift_rec.LOCATOR_SEGMENTS IS NOT NULL)) THEN
492 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_PC_SUBINV_MANDATORY');
493 FND_MSG_PUB.ADD;
494 RAISE FND_API.G_EXC_ERROR;
495 END IF;
496
497 IF (p_appr_deptshift_rec.INV_LOCATOR_ID IS NOT NULL) THEN
501 FETCH validate_inv_loc_id INTO l_temp_num;
498 OPEN validate_inv_loc_id(C_ORGANIZATION_ID => p_appr_deptshift_rec.ORGANIZATION_ID,
499 C_SUBINVENTORY => p_appr_deptshift_rec.SUBINVENTORY,
500 C_INVENTORY_LOCATION_ID => p_appr_deptshift_rec.INV_LOCATOR_ID);
502 IF (validate_inv_loc_id%NOTFOUND) THEN
503 CLOSE validate_inv_loc_id;
504 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_LOCATOR');
505 FND_MESSAGE.Set_Token('LOC', p_appr_deptshift_rec.INV_LOCATOR_ID);
506 FND_MSG_PUB.ADD;
507 RAISE FND_API.G_EXC_ERROR;
508 END IF;
509 CLOSE validate_inv_loc_id;
510 END IF;
511 END IF; -- DML_OPERATION='C'
512
513 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
514 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
515 END IF;
516 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
517
518 EXCEPTION
519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
520 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
522 p_count => x_msg_count,
523 p_data => x_msg_data);
524
525 WHEN FND_API.G_EXC_ERROR THEN
526 X_return_status := FND_API.G_RET_STS_ERROR;
527 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
528 p_count => x_msg_count,
529 p_data => X_msg_data);
530
531 WHEN OTHERS THEN
532 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
533 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
534 THEN
535 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
536 p_procedure_name => 'VALIDATE_APPR_DEPT_SHIFT',
537 p_error_text => SUBSTR(SQLERRM,1,240));
538 END IF;
539 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
540 p_count => x_msg_count,
541 p_data => X_msg_data);
542
543 END;
544
545 PROCEDURE CREATE_APPR_DEPT_SHIFTS
546 (
547 p_api_version IN NUMBER:= 1.0,
548 p_init_msg_list IN VARCHAR2,
549 p_commit IN VARCHAR2,
550 p_validation_level IN NUMBER,
551 p_default IN VARCHAR2 := FND_API.G_FALSE,
552 p_module_type IN VARCHAR2,
553 x_return_status OUT NOCOPY VARCHAR2,
554 x_msg_count OUT NOCOPY NUMBER,
555 x_msg_data OUT NOCOPY VARCHAR2,
556 p_x_appr_deptshift_rec IN OUT NOCOPY APPR_DEPTSHIFT_REC
557 )
558 AS
559 l_appr_deptshift_rec APPR_DEPTSHIFT_REC:=p_x_appr_deptshift_rec;
560 l_lookup_code VARCHAR2(30);
561 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_APPR_DEPT_SHIFTS';
562 l_api_version CONSTANT NUMBER := 1.0;
563 l_msg_count NUMBER:=0;
564 l_msg_data VARCHAR2(2000);
565 l_return_status VARCHAR2(1);
566 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
567 l_department_shifts_id number:=0;
568 l_object_version_number NUMBER;
569 l_check_flag VARCHAR2(1):='Y';
570 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
571 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.CREATE_APPR_DEPT_SHIFTS';
572 BEGIN
573 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
574 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
575 END IF;
576
577 SAVEPOINT CREATE_APPR_DEPT_SHIFTS;
578
579 -- Initialize message list if p_init_msg_list is set to TRUE.
580
581 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
582 p_api_version,
583 l_api_name,G_PKG_NAME)
584 THEN
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586 END IF;
587
588 IF FND_API.to_boolean(l_init_msg_list) THEN
589 FND_MSG_PUB.initialize;
590 END IF;
591
592 x_return_status:=FND_API.G_RET_STS_SUCCESS;
593
594
595 IF p_module_type = 'JSP'
596 THEN
597 l_appr_deptshift_rec.ORGANIZATION_ID:=NULL;
598 l_appr_deptshift_rec.DEPARTMENT_ID:=NULL;
599 l_appr_deptshift_rec.CALENDAR_CODE:=NULL;
600 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
601 l_appr_deptshift_rec.INV_LOCATOR_ID := NULL;
602 END IF;
603
604 -- Debug info.
605 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
606 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call TRANSLATE_VALUE_ID');
607 END IF;
608
609 TRANSLATE_VALUE_ID
610 (
611 x_return_status =>x_return_Status,
612 x_msg_count =>l_msg_count,
613 x_msg_data =>l_msg_data,
614 p_x_appr_deptshift_rec =>l_appr_deptshift_rec
615 );
616 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
617 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from TRANSLATE_VALUE_ID. x_return_status = ' || x_return_status);
618 END IF;
619 l_msg_count := FND_MSG_PUB.count_msg;
620 IF l_msg_count > 0
621 THEN
622 x_msg_count := l_msg_count;
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
624 RAISE FND_API.G_EXC_ERROR;
625 END IF;
626
627
631 (
628 IF FND_API.to_boolean(p_default)
629 THEN
630 DEFAULT_MISSING_ATTRIBS
632 p_x_appr_deptshift_rec =>l_appr_deptshift_rec
633 );
634 END IF;
635
636 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
637 THEN
638
639 VALIDATE_APPR_DEPT_SHIFT
640 (
641 x_return_status =>x_return_status,
642 x_msg_count =>l_msg_count,
643 x_msg_data =>l_msg_data,
644 p_appr_deptshift_rec =>l_appr_deptshift_rec);
645 END IF;
646 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from VALIDATE_APPR_DEPT_SHIFT. x_return_status = ' || x_return_status);
648 END IF;
649
650 l_msg_count := FND_MSG_PUB.count_msg;
651
652 IF l_msg_count > 0
653 THEN
654 x_msg_count := l_msg_count;
655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
656 RAISE FND_API.G_EXC_ERROR;
657 END IF;
658
659 -- insert process goes here
660 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
661 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to insert into table.');
662 END IF;
663 IF l_appr_deptshift_rec.DML_OPERATION='C'
664 THEN
665 Select AHL_DEPARTMENT_SHIFTS_S.nextval into
666 l_department_shifts_id
667 from dual;
668
669 l_appr_deptshift_rec.OBJECT_VERSION_NUMBER:=1;
670 l_appr_deptshift_rec.LAST_UPDATE_DATE:=sysdate;
671 l_appr_deptshift_rec.LAST_UPDATED_BY:=fnd_global.user_id;
672 l_appr_deptshift_rec.CREATION_DATE:=sysdate;
673 l_appr_deptshift_rec.CREATED_BY:=fnd_global.user_id;
674 l_appr_deptshift_rec.LAST_UPDATE_LOGIN:=fnd_global.user_id;
675 l_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID:=l_department_shifts_id;
676
677 INSERT INTO AHL_DEPARTMENT_SHIFTS
678 (
679 AHL_DEPARTMENT_SHIFTS_ID,
680 OBJECT_VERSION_NUMBER,
681 LAST_UPDATE_DATE,
682 LAST_UPDATED_BY,
683 CREATION_DATE,
684 CREATED_BY,
685 LAST_UPDATE_LOGIN,
686 DEPARTMENT_ID,
687 CALENDAR_CODE,
688 SHIFT_NUM,
689 SEQ_NUM,
690 -- Following two columns added by rbhavsar on Nov 27, 2007 for ER 5854712
691 SUBINVENTORY,
692 INV_LOCATOR_ID,
693 ATTRIBUTE_CATEGORY,
694 ATTRIBUTE1,
695 ATTRIBUTE2,
696 ATTRIBUTE3,
697 ATTRIBUTE4,
698 ATTRIBUTE5,
699 ATTRIBUTE6,
700 ATTRIBUTE7,
701 ATTRIBUTE8,
702 ATTRIBUTE9,
703 ATTRIBUTE10,
704 ATTRIBUTE11,
705 ATTRIBUTE12,
706 ATTRIBUTE13,
707 ATTRIBUTE14,
708 ATTRIBUTE15
709 )
710 VALUES
711 (
712 l_DEPARTMENT_SHIFTS_ID,
713 l_appr_deptshift_rec.OBJECT_VERSION_NUMBER,
714 l_appr_deptshift_rec.LAST_UPDATE_DATE,
715 l_appr_deptshift_rec.LAST_UPDATED_BY,
716 l_appr_deptshift_rec.CREATION_DATE,
717 l_appr_deptshift_rec.CREATED_BY,
718 l_appr_deptshift_rec.LAST_UPDATE_LOGIN,
719 l_appr_deptshift_rec.DEPARTMENT_ID,
720 l_appr_deptshift_rec.CALENDAR_CODE,
721 l_appr_deptshift_rec.SHIFT_NUM,
722 l_appr_deptshift_rec.SEQ_NUM,
723 -- Following two columns added by rbhavsar on Nov 27, 2007 for ER 5854712
724 l_appr_deptshift_rec.SUBINVENTORY,
725 l_appr_deptshift_rec.INV_LOCATOR_ID,
726 l_appr_deptshift_rec.ATTRIBUTE_CATEGORY,
727 l_appr_deptshift_rec.ATTRIBUTE1,
728 l_appr_deptshift_rec.ATTRIBUTE2,
729 l_appr_deptshift_rec.ATTRIBUTE3,
730 l_appr_deptshift_rec.ATTRIBUTE4,
731 l_appr_deptshift_rec.ATTRIBUTE5,
732 l_appr_deptshift_rec.ATTRIBUTE6,
733 l_appr_deptshift_rec.ATTRIBUTE7,
734 l_appr_deptshift_rec.ATTRIBUTE8,
735 l_appr_deptshift_rec.ATTRIBUTE9,
736 l_appr_deptshift_rec.ATTRIBUTE10,
737 l_appr_deptshift_rec.ATTRIBUTE11,
738 l_appr_deptshift_rec.ATTRIBUTE12,
739 l_appr_deptshift_rec.ATTRIBUTE13,
740 l_appr_deptshift_rec.ATTRIBUTE14,
741 l_appr_deptshift_rec.ATTRIBUTE15);
742 END IF;
743
744 IF FND_API.TO_BOOLEAN(p_commit) THEN
745 COMMIT;
746 END IF;
747 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
748 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
749 END IF;
750
751 EXCEPTION
752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
753 ROLLBACK TO CREATE_APPR_DEPT_SHIFTS;
754 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
755 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
756 p_count => x_msg_count,
757 p_data => x_msg_data);
758 WHEN FND_API.G_EXC_ERROR THEN
759 ROLLBACK TO CREATE_APPR_DEPT_SHIFTS;
763 p_data => X_msg_data);
760 X_return_status := FND_API.G_RET_STS_ERROR;
761 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
762 p_count => x_msg_count,
764 WHEN OTHERS THEN
765 ROLLBACK TO CREATE_APPR_DEPT_SHIFTS;
766 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
767 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
768 THEN
769 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
770 p_procedure_name => 'CREATE_APPR_DEPT_SHIFTS',
771 p_error_text => SUBSTR(SQLERRM,1,240));
772
773 END IF;
774 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
775 p_count => x_msg_count,
776 p_data => X_msg_data);
777 END;
778
779 PROCEDURE DELETE_APPR_DEPT_SHIFTS
780 (
781 p_api_version IN NUMBER:= 1.0,
782 p_init_msg_list IN VARCHAR2,
783 p_commit IN VARCHAR2,
784 p_validation_level IN NUMBER,
785 p_default IN VARCHAR2 := FND_API.G_FALSE,
786 p_module_type IN VARCHAR2,
787 x_return_status OUT NOCOPY VARCHAR2,
788 x_msg_count OUT NOCOPY NUMBER,
789 x_msg_data OUT NOCOPY VARCHAR2,
790 p_x_appr_deptshift_rec IN OUT NOCOPY APPR_DEPTSHIFT_REC
791 )
792 AS
793 l_appr_deptshift_rec APPR_DEPTSHIFT_REC:=p_x_appr_deptshift_rec;
794 l_lookup_code VARCHAR2(30);
795 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_APPR_DEPT_SHIFTS';
796 l_api_version CONSTANT NUMBER := 1.0;
797 l_num_rec NUMBER;
798 l_msg_count NUMBER;
799 l_msg_data VARCHAR2(2000);
800 l_return_status VARCHAR2(1);
801 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
802 l_department_shifts_id number:=0;
803 l_lookup_var varchar2(1);
804 l_object_version_number NUMBER;
805 l_check_flag VARCHAR2(1):='Y';
806 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
807 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.DELETE_APPR_DEPT_SHIFTS';
808
809 BEGIN
810 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
811 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
812 END IF;
813
814 SAVEPOINT DELETE_DEPT_SHIFTS;
815
816 -- Initialize message list if p_init_msg_list is set to TRUE.
817
818 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
819 p_api_version,
820 l_api_name,G_PKG_NAME)
821 THEN
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END IF;
824
825
826 IF FND_API.to_boolean(l_init_msg_list) THEN
827 FND_MSG_PUB.initialize;
828 END IF;
829
830
831 x_return_status:=FND_API.G_RET_STS_SUCCESS;
832
833
834 IF p_module_type = 'JSP'
835 THEN
836 l_appr_deptshift_rec.ORGANIZATION_ID:=NULL;
837 l_appr_deptshift_rec.DEPARTMENT_ID:=NULL;
838 l_appr_deptshift_rec.CALENDAR_CODE:=NULL;
839 l_appr_deptshift_rec.SHIFT_NUM:=NULL;
840 END IF;
841
842
843
844
845
846 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
847 THEN
848 VALIDATE_APPR_DEPT_SHIFT
849 (
850 x_return_status =>x_return_Status,
851 x_msg_count =>l_msg_count,
852 x_msg_data =>l_msg_data,
853 p_appr_deptshift_rec =>l_appr_deptshift_rec);
854 END IF;
855
856 l_msg_count := FND_MSG_PUB.count_msg;
857
858 IF l_msg_count > 0
859 THEN
860 x_msg_count := l_msg_count;
861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 -- DELETE GOES HERE
866
867 IF l_appr_deptshift_rec.DML_OPERATION='D'
868 THEN
869 DELETE AHL_DEPARTMENT_SHIFTS
870 WHERE AHL_DEPARTMENT_SHIFTS_ID=l_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID
871 AND OBJECT_VERSION_NUMBER=l_appr_deptshift_rec.OBJECT_VERSION_NUMBER;
872 IF (sql%ROWCOUNT=0)
873 THEN
874 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
875 FND_MSG_PUB.ADD;
876 END IF;
877 END IF;
878
879 IF FND_API.TO_BOOLEAN(p_commit) THEN
880 COMMIT;
881 END IF;
882 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
883 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
884 END IF;
885
886 EXCEPTION
887 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
888 ROLLBACK TO DELETE_DEPT_SHIFTS;
889 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
890 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
891 p_count => x_msg_count,
892 p_data => x_msg_data);
893 WHEN FND_API.G_EXC_ERROR THEN
894 ROLLBACK TO DELETE_DEPT_SHIFTS;
895 X_return_status := FND_API.G_RET_STS_ERROR;
896 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
897 p_count => x_msg_count,
898 p_data => X_msg_data);
899
900 WHEN OTHERS THEN
904 THEN
901 ROLLBACK TO DELETE_DEPT_SHIFTS;
902 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
905 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
906 p_procedure_name => 'CREATE_APPR_DEPT_SHIFTS',
907 p_error_text => SUBSTR(SQLERRM,1,240));
908
909 END IF;
910 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
911 p_count => x_msg_count,
912 p_data => X_msg_data);
913 END;
914
915 END AHL_APPR_DEPT_SHIFTS_PUB;