[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.12020000.2 2012/12/07 00:10:16 sareepar 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
38 END IF;
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;
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 -- Added by sansatpa for nr analysis
138 IF p_x_appr_deptshift_rec.MAX_SERVICE_CATEGORY = FND_API.G_MISS_NUM
139 THEN
140 p_x_appr_deptshift_rec.MAX_SERVICE_CATEGORY := NULL;
141 END IF;
142
143 IF p_x_appr_deptshift_rec.DESCRIPTION = FND_API.G_MISS_CHAR
144 THEN
145 p_x_appr_deptshift_rec.DESCRIPTION := NULL;
146 END IF;
147
148 -- end changes by sansatpa for nr analysis
149
150 END;
151
152 PROCEDURE TRANSLATE_VALUE_ID
153 (
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157 p_x_appr_deptshift_rec IN OUT NOCOPY AHL_APPR_DEPT_SHIFTS_PUB.APPR_DEPTSHIFT_REC
158 )
159 as
160 CURSOR get_org_id(C_NAME VARCHAR2)
161 IS
162 SELECT ORGANIZATION_ID
163 FROM HR_ALL_ORGANIZATION_UNITS
164 WHERE NAME=C_NAME;
165
166 CURSOR get_org_dept_id(C_ORG_ID NUMBER,C_DESCRIPTION VARCHAR2)
167 IS
168 SELECT DEPARTMENT_ID
169 FROM BOM_DEPARTMENTS_V
170 WHERE ORGANIZATION_ID=C_ORG_ID
171 AND DESCRIPTION=C_DESCRIPTION;
172
173 CURSOR get_bom_calendar(C_CALENDAR VARCHAR2)
174 IS
175 SELECT CALENDAR_CODE
176 FROM BOM_CALENDARS
177 WHERE DESCRIPTION=C_CALENDAR;
178
179
180 CURSOR get_bom_shift_num(C_CALENDAR_CODE VARCHAR2,C_SHIFT_NUM NUMBER)
181 IS
182 SELECT SHIFT_NUM
183 FROM BOM_SHIFT_TIMES
184 WHERE CALENDAR_CODE=C_CALENDAR_CODE
185 AND SHIFT_NUM=C_SHIFT_NUM;
186
187 CURSOR get_bom_workdays(C_CALENDAR_CODE VARCHAR2,C_SHIFT NUMBER,C_DESCRIPTION VARCHAR2)
188 IS
189 SELECT SEQ_NUM
190 FROM BOM_WORKDAY_PATTERNS
191 WHERE CALENDAR_CODE=C_CALENDAR_CODE
195 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
192 AND SHIFT_NUM=C_SHIFT
193 AND DESCRIPTION=C_DESCRIPTION;
194
196 CURSOR get_inv_loc_id(C_ORGANIZATION_ID NUMBER, C_SUBINVENTORY VARCHAR2, C_LOCATOR_SEGMENTS VARCHAR2)
197 IS
198 SELECT INVENTORY_LOCATION_ID
199 FROM MTL_ITEM_LOCATIONS_KFV
200 WHERE ORGANIZATION_ID = C_ORGANIZATION_ID
201 AND SUBINVENTORY_CODE = C_SUBINVENTORY
202 AND CONCATENATED_SEGMENTS = C_LOCATOR_SEGMENTS;
203 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
204 l_lookup_code VARCHAR2(30);
205 l_num_rec NUMBER;
206 l_msg_count NUMBER;
207 l_msg_data VARCHAR2(2000);
208 l_return_status VARCHAR2(1);
209 l_mr_header_id number:=0;
210 l_lookup_var varchar2(1);
211 l_object_version_number NUMBER;
212 l_check_flag VARCHAR2(1):='N';
213 l_counter NUMBER:=0;
214 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
215 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.TRANSLATE_VALUE_ID';
216 BEGIN
217 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
218 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
219 END IF;
220 x_return_status:=FND_API.G_RET_STS_SUCCESS;
221 IF p_x_appr_deptshift_rec.ORGANIZATION_NAME IS NULL OR p_x_appr_deptshift_rec.ORGANIZATION_NAME=FND_API.G_MISS_CHAR
222 THEN
223 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_ORG_NAME_NULL');
224 FND_MSG_PUB.ADD;
225 l_check_flag:='N';
226 ELSE
227 l_check_flag:='Y';
228 OPEN get_org_id(p_x_appr_deptshift_rec.ORGANIZATION_NAME);
229 FETCH get_org_id INTO p_x_appr_deptshift_rec.ORGANIZATION_ID;
230 IF get_org_id%NOTFOUND
231 THEN
232 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_ORG_NAME_INVALID');
233 FND_MSG_PUB.ADD;
234 l_check_flag:='N';
235 END IF;
236 CLOSE get_org_id;
237 END IF;
238
239 l_check_flag:='N';
240 IF p_x_appr_deptshift_rec.DEPT_DESCRIPTION IS NULL OR p_x_appr_deptshift_rec.DEPT_DESCRIPTION=FND_API.G_MISS_CHAR
241 THEN
242 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_DEPT_NAME_NULL');
243 FND_MSG_PUB.ADD;
244 l_check_flag:='N';
245 ELSE
246 l_check_flag:='Y';
247 END IF;
248
249 IF l_check_flag='Y'
250 THEN
251 OPEN get_org_dept_id(p_x_appr_deptshift_rec.ORGANIZATION_ID,p_x_appr_deptshift_rec.DEPT_DESCRIPTION);
252 FETCH get_org_dept_id INTO p_x_appr_deptshift_rec.DEPARTMENT_ID;
253 IF get_org_dept_id%NOTFOUND
254 THEN
255 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_DEPT_NAME_INVALID');
256 FND_MSG_PUB.ADD;
257 l_check_flag:='N';
258 END IF;
259 CLOSE get_org_dept_id;
260 END IF;
261
262
263 l_check_flag:='N';
264 IF p_x_appr_deptshift_rec.CALENDAR_DESCRIPTION IS NULL OR p_x_appr_deptshift_rec.CALENDAR_DESCRIPTION=FND_API.G_MISS_CHAR
265 THEN
266 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_CALENDER_NAME_NULL');
267 FND_MSG_PUB.ADD;
268 l_check_flag:='N';
269 ELSE
270 l_check_flag:='Y';
271 END IF;
272
273
274 IF l_check_flag='Y'
275 THEN
276 OPEN get_bom_calendar(p_x_appr_deptshift_rec.CALENDAR_DESCRIPTION);
277 FETCH get_bom_calendar INTO p_x_appr_deptshift_rec.CALENDAR_CODE;
278 IF get_bom_calendar%NOTFOUND
279 THEN
280 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_CALENDER_INVALID');
281 FND_MSG_PUB.ADD;
282 l_check_flag:='N';
283 ELSE
284 l_check_flag:='Y';
285 END IF;
286 CLOSE get_bom_calendar;
287 END IF;
288
289
290 l_check_flag:='N';
291 IF p_x_appr_deptshift_rec.SHIFT_NUM IS NULL OR p_x_appr_deptshift_rec.SHIFT_NUM=FND_API.G_MISS_NUM
292 THEN
293 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_SHIFT_NUMBER_NULL');
294 FND_MSG_PUB.ADD;
295 l_check_flag:='N';
296 ELSE
297 l_check_flag:='Y';
298 END IF;
299
300 IF l_check_flag='Y'
301 THEN
302 OPEN get_bom_shift_num(p_x_appr_deptshift_rec.CALENDAR_CODE,p_x_appr_deptshift_rec.SHIFT_NUM);
303 FETCH get_bom_shift_num INTO p_x_appr_deptshift_rec.SHIFT_NUM;
304 IF get_bom_shift_num%NOTFOUND
305 THEN
306 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_SHIFT_NUMBER_INVALID');
307 FND_MSG_PUB.ADD;
308 l_check_flag:='N';
309 ELSE
313 END IF;
310 l_check_flag:='Y';
311 END IF;
312 CLOSE get_bom_shift_num;
314
315
316 l_check_flag:='N';
317 IF p_x_appr_deptshift_rec.SEQ_NAME IS NULL OR p_x_appr_deptshift_rec.SEQ_NAME=FND_API.G_MISS_CHAR
318 THEN
319 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_WORK_DAYS_NULL');
320 FND_MSG_PUB.ADD;
321 l_check_flag:='N';
322 ELSE
323 l_check_flag:='Y';
324 END IF;
325 IF l_check_flag='Y'
326 THEN
327 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);
328 FETCH get_bom_workdays INTO p_x_appr_deptshift_rec.SEQ_NUM;
329 IF get_bom_workdays%NOTFOUND
330 THEN
331 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_SEQ_DESCRIP_INVALID');
332 FND_MSG_PUB.ADD;
333 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
334 l_check_flag:='N';
335 END IF;
336 CLOSE get_bom_workdays;
337 END IF;
338
339 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
340 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
341 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_x_appr_deptshift_rec.ORGANIZATION_ID = ' || p_x_appr_deptshift_rec.ORGANIZATION_ID ||
342 ', p_x_appr_deptshift_rec.SUBINVENTORY = ' || p_x_appr_deptshift_rec.SUBINVENTORY ||
343 ', p_x_appr_deptshift_rec.INV_LOCATOR_ID = ' || p_x_appr_deptshift_rec.INV_LOCATOR_ID ||
344 ', p_x_appr_deptshift_rec.LOCATOR_SEGMENTS = ' || p_x_appr_deptshift_rec.LOCATOR_SEGMENTS);
345 END IF;
346 -- Convert G_MISS to NULL since this API is called only for creating
347 IF (p_x_appr_deptshift_rec.SUBINVENTORY = FND_API.G_MISS_CHAR) THEN
348 p_x_appr_deptshift_rec.SUBINVENTORY := NULL;
349 END IF;
350 IF (p_x_appr_deptshift_rec.LOCATOR_SEGMENTS = FND_API.G_MISS_CHAR) THEN
351 p_x_appr_deptshift_rec.LOCATOR_SEGMENTS := NULL;
352 END IF;
353 IF (p_x_appr_deptshift_rec.INV_LOCATOR_ID = FND_API.G_MISS_NUM) THEN
354 p_x_appr_deptshift_rec.INV_LOCATOR_ID := NULL;
355 END IF;
356 IF (l_check_flag = 'Y' AND
357 p_x_appr_deptshift_rec.ORGANIZATION_ID IS NOT NULL AND
358 p_x_appr_deptshift_rec.SUBINVENTORY IS NOT NULL AND
359 p_x_appr_deptshift_rec.INV_LOCATOR_ID IS NULL AND
360 p_x_appr_deptshift_rec.LOCATOR_SEGMENTS IS NOT NULL) THEN
361 OPEN get_inv_loc_id(C_ORGANIZATION_ID => p_x_appr_deptshift_rec.ORGANIZATION_ID,
362 C_SUBINVENTORY => p_x_appr_deptshift_rec.SUBINVENTORY,
363 C_LOCATOR_SEGMENTS => p_x_appr_deptshift_rec.LOCATOR_SEGMENTS);
364 FETCH get_inv_loc_id INTO p_x_appr_deptshift_rec.INV_LOCATOR_ID;
365 IF (get_inv_loc_id%NOTFOUND) THEN
366 p_x_appr_deptshift_rec.INV_LOCATOR_ID := 0;
367 END IF;
368 CLOSE get_inv_loc_id;
369 END IF;
370 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
371 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);
372 END IF;
373
374 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
375 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
376 END IF;
377 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
381 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
383 p_count => x_msg_count,
384 p_data => x_msg_data);
385
386 WHEN FND_API.G_EXC_ERROR THEN
387 X_return_status := FND_API.G_RET_STS_ERROR;
388 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
389 p_count => x_msg_count,
390 p_data => X_msg_data);
391 WHEN OTHERS THEN
392 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
394 THEN
395 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
396 p_procedure_name => 'TRANSLATE_VALUE_ID',
397 p_error_text => SUBSTR(SQLERRM,1,240));
398 END IF;
399 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
400 p_count => x_msg_count,
401 p_data => x_msg_data);
402 END;
403
404 -- Start of Validate
405
406 PROCEDURE VALIDATE_APPR_DEPT_SHIFT
407 (
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2,
411 p_appr_deptshift_rec IN AHL_APPR_DEPT_SHIFTS_PUB.appr_deptshift_rec
412 )
413 as
414 l_counter NUMBER:=0;
415 l_prim_key NUMBER:=0;
416
417 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
418 CURSOR validate_subinventory(C_ORGANIZATION_ID NUMBER, C_SUBINVENTORY VARCHAR2) IS
419 SELECT status_id
420 FROM MTL_SECONDARY_INVENTORIES
421 WHERE organization_id = C_ORGANIZATION_ID
422 AND secondary_inventory_name = C_SUBINVENTORY;
423
424 CURSOR validate_inv_loc_id(C_ORGANIZATION_ID NUMBER, C_SUBINVENTORY VARCHAR2, C_INVENTORY_LOCATION_ID NUMBER) IS
425 SELECT 1
426 FROM MTL_ITEM_LOCATIONS
427 WHERE ORGANIZATION_ID = C_ORGANIZATION_ID
428 AND SUBINVENTORY_CODE = C_SUBINVENTORY
432
429 AND INVENTORY_LOCATION_ID = C_INVENTORY_LOCATION_ID
430 AND SEGMENT19 IS NULL
431 AND SEGMENT20 IS NULL;
433 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.VALIDATE_APPR_DEPT_SHIFT';
434 l_temp_num NUMBER;
435 l_status_id NUMBER;
436 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
437
438 BEGIN
439 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
440 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
441 END IF;
442 x_return_status:=FND_API.G_RET_STS_SUCCESS;
443
444 IF p_appr_deptshift_rec.DML_OPERATION='C' or p_appr_deptshift_rec.DML_OPERATION='U' THEN
445 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
446 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
447 'ORGANIZATION_NAME = ' || p_appr_deptshift_rec.ORGANIZATION_NAME ||
448 ', DEPT_DESCRIPTION = ' || p_appr_deptshift_rec.DEPT_DESCRIPTION ||
449 ', CALENDAR_DESCRIPTION = ' || p_appr_deptshift_rec.CALENDAR_DESCRIPTION ||
450 ', SHIFT_NUM = ' || p_appr_deptshift_rec.SHIFT_NUM ||
451 ', WORKDAY_ID = ' || p_appr_deptshift_rec.BOM_WORKDAY_PATTERNS_ID ||
452 ', MAX_SERVICE_CATEGORY = ' || p_appr_deptshift_rec.MAX_SERVICE_CATEGORY);
453 end if;
454 end if;
455
456 IF p_appr_deptshift_rec.DML_OPERATION='D'
457 THEN
458 IF p_appr_deptshift_rec.OBJECT_VERSION_NUMBER IS NULL OR p_appr_deptshift_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM
459 THEN
460 FND_MESSAGE.SET_NAME('AHL','AHL_COM_OBJECT_VERS_NUM_NULL');
461 FND_MSG_PUB.ADD;
462 END IF;
463
464 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
465 THEN
466 FND_MESSAGE.SET_NAME('AHL','AHL_DEPARTMENT_SHIFTS_ID_NULL');
467 FND_MSG_PUB.ADD;
468 ELSE
469 SELECT AHL_DEPARTMENT_SHIFTS_ID INTO l_prim_key
470 FROM AHL_DEPARTMENT_SHIFTS
471 WHERE AHL_DEPARTMENT_SHIFTS_ID=p_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID;
472 END IF;
473 ELSIF p_appr_deptshift_rec.DML_OPERATION <> 'U'
474 THEN
475 Select COUNT(DEPARTMENT_ID) INTO l_counter
476 FROM AHL_DEPARTMENT_SHIFTS
477 WHERE DEPARTMENT_ID=p_appr_deptshift_rec.DEPARTMENT_ID;
478
479 IF l_counter>0
480 THEN
481 FND_MESSAGE.SET_NAME('AHL','AHL_APPR_DEPT_EXISTS');
482 FND_MSG_PUB.ADD;
483 END IF;
484 END IF;
485
486 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
487 IF p_appr_deptshift_rec.DML_OPERATION='C' or p_appr_deptshift_rec.DML_OPERATION='U' THEN
488 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
489 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_appr_deptshift_rec.ORGANIZATION_ID = ' || p_appr_deptshift_rec.ORGANIZATION_ID ||
490 ',p_appr_deptshift_rec.SUBINVENTORY = ' || p_appr_deptshift_rec.SUBINVENTORY ||
491 ',p_appr_deptshift_rec.INV_LOCATOR_ID = ' || p_appr_deptshift_rec.INV_LOCATOR_ID ||
492 ',p_appr_deptshift_rec.LOCATOR_SEGMENTS = ' || p_appr_deptshift_rec.LOCATOR_SEGMENTS);
493 END IF;
494 IF (p_appr_deptshift_rec.SUBINVENTORY IS NOT NULL) THEN
495 OPEN validate_subinventory(C_ORGANIZATION_ID => p_appr_deptshift_rec.ORGANIZATION_ID,
496 C_SUBINVENTORY => p_appr_deptshift_rec.SUBINVENTORY);
497 FETCH validate_subinventory INTO l_status_id;
498 IF (validate_subinventory%NOTFOUND) THEN
499 CLOSE validate_subinventory;
500 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_SUBINVENTORY');
501 FND_MESSAGE.Set_Token('INV', p_appr_deptshift_rec.SUBINVENTORY);
502 FND_MSG_PUB.ADD;
503 RAISE FND_API.G_EXC_ERROR;
504 ELSE
505 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
506 CLOSE validate_subinventory;
507 FND_MESSAGE.SET_NAME('AHL', 'AHL_SUBINVENTORY_NOT_SVC');
508 FND_MESSAGE.Set_Token('INV', p_appr_deptshift_rec.SUBINVENTORY);
509 FND_MSG_PUB.ADD;
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512 END IF;
513 CLOSE validate_subinventory;
514 END IF;
515
516 IF (p_appr_deptshift_rec.SUBINVENTORY IS NULL AND
517 (p_appr_deptshift_rec.INV_LOCATOR_ID IS NOT NULL OR p_appr_deptshift_rec.LOCATOR_SEGMENTS IS NOT NULL)) THEN
518 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_PC_SUBINV_MANDATORY');
519 FND_MSG_PUB.ADD;
520 RAISE FND_API.G_EXC_ERROR;
521 END IF;
522
523 IF (p_appr_deptshift_rec.INV_LOCATOR_ID IS NOT NULL) THEN
524 OPEN validate_inv_loc_id(C_ORGANIZATION_ID => p_appr_deptshift_rec.ORGANIZATION_ID,
525 C_SUBINVENTORY => p_appr_deptshift_rec.SUBINVENTORY,
526 C_INVENTORY_LOCATION_ID => p_appr_deptshift_rec.INV_LOCATOR_ID);
527 FETCH validate_inv_loc_id INTO l_temp_num;
528 IF (validate_inv_loc_id%NOTFOUND) THEN
529 CLOSE validate_inv_loc_id;
530 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_LOCATOR');
531 FND_MESSAGE.Set_Token('LOC', p_appr_deptshift_rec.INV_LOCATOR_ID);
532 FND_MSG_PUB.ADD;
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535 CLOSE validate_inv_loc_id;
536 END IF;
537 END IF; -- DML_OPERATION='C'
538
542 -- End Changes by rbhavsar on Nov 27, 2007 for ER 5854712
539 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
540 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
541 END IF;
543
544 EXCEPTION
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
548 p_count => x_msg_count,
549 p_data => x_msg_data);
550
551 WHEN FND_API.G_EXC_ERROR THEN
552 X_return_status := FND_API.G_RET_STS_ERROR;
553 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
554 p_count => x_msg_count,
555 p_data => X_msg_data);
556
557 WHEN OTHERS THEN
558 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
559 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
560 THEN
561 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
562 p_procedure_name => 'VALIDATE_APPR_DEPT_SHIFT',
563 p_error_text => SUBSTR(SQLERRM,1,240));
564 END IF;
565 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
566 p_count => x_msg_count,
567 p_data => X_msg_data);
568
569 END;
570
571 PROCEDURE CREATE_APPR_DEPT_SHIFTS
572 (
573 p_api_version IN NUMBER:= 1.0,
574 p_init_msg_list IN VARCHAR2,
575 p_commit IN VARCHAR2,
576 p_validation_level IN NUMBER,
577 p_default IN VARCHAR2 := FND_API.G_FALSE,
578 p_module_type IN VARCHAR2,
579 x_return_status OUT NOCOPY VARCHAR2,
580 x_msg_count OUT NOCOPY NUMBER,
581 x_msg_data OUT NOCOPY VARCHAR2,
582 p_x_appr_deptshift_rec IN OUT NOCOPY APPR_DEPTSHIFT_REC
583 )
584 AS
585 l_appr_deptshift_rec APPR_DEPTSHIFT_REC:= p_x_appr_deptshift_rec;
586 l_lookup_code VARCHAR2(30);
587 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_APPR_DEPT_SHIFTS';
588 l_api_version CONSTANT NUMBER := 1.0;
589 l_msg_count NUMBER:=0;
590 l_msg_data VARCHAR2(2000);
591 l_return_status VARCHAR2(1);
592 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
593 l_department_shifts_id number:=0;
594 l_object_version_number NUMBER;
595 l_check_flag VARCHAR2(1):='Y';
596 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
597 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.CREATE_APPR_DEPT_SHIFTS';
598 BEGIN
599 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
600 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
601 END IF;
602
603 SAVEPOINT CREATE_APPR_DEPT_SHIFTS;
604
605 -- Initialize message list if p_init_msg_list is set to TRUE.
606
607 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
608 p_api_version,
609 l_api_name,G_PKG_NAME)
610 THEN
611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
612 END IF;
613
614 IF FND_API.to_boolean(l_init_msg_list) THEN
615 FND_MSG_PUB.initialize;
616 END IF;
617
618 x_return_status:=FND_API.G_RET_STS_SUCCESS;
619
620
621 IF p_module_type = 'JSP'
622 THEN
623 l_appr_deptshift_rec.ORGANIZATION_ID:=NULL;
624 l_appr_deptshift_rec.DEPARTMENT_ID:=NULL;
625 l_appr_deptshift_rec.CALENDAR_CODE:=NULL;
626 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
627 l_appr_deptshift_rec.INV_LOCATOR_ID := NULL;
628 END IF;
629
630 -- Debug info.
631 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
632 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call TRANSLATE_VALUE_ID');
633 END IF;
634
635 TRANSLATE_VALUE_ID
636 (
637 x_return_status =>x_return_Status,
638 x_msg_count =>l_msg_count,
639 x_msg_data =>l_msg_data,
640 p_x_appr_deptshift_rec =>l_appr_deptshift_rec
641 );
642 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
643 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from TRANSLATE_VALUE_ID. x_return_status = ' || x_return_status);
644 END IF;
645 l_msg_count := FND_MSG_PUB.count_msg;
646 IF l_msg_count > 0
647 THEN
648 x_msg_count := l_msg_count;
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652
653
654 IF FND_API.to_boolean(p_default)
655 THEN
656 DEFAULT_MISSING_ATTRIBS
657 (
661
658 p_x_appr_deptshift_rec =>l_appr_deptshift_rec
659 );
660 END IF;
662 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
663 THEN
664
665 VALIDATE_APPR_DEPT_SHIFT
666 (
667 x_return_status =>x_return_status,
668 x_msg_count =>l_msg_count,
669 x_msg_data =>l_msg_data,
670 p_appr_deptshift_rec =>l_appr_deptshift_rec);
671 END IF;
672 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
673 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from VALIDATE_APPR_DEPT_SHIFT. x_return_status = ' || x_return_status);
674 END IF;
675
676 l_msg_count := FND_MSG_PUB.count_msg;
677
678 IF l_msg_count > 0
679 THEN
680 x_msg_count := l_msg_count;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 RAISE FND_API.G_EXC_ERROR;
683 END IF;
684
685 -- insert process goes here
686 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to insert into table.');
688 END IF;
689 IF l_appr_deptshift_rec.DML_OPERATION='C'
690 THEN
691 Select AHL_DEPARTMENT_SHIFTS_S.nextval into
692 l_department_shifts_id
693 from dual;
694
695 l_appr_deptshift_rec.OBJECT_VERSION_NUMBER:=1;
696 l_appr_deptshift_rec.LAST_UPDATE_DATE:=sysdate;
697 l_appr_deptshift_rec.LAST_UPDATED_BY:=fnd_global.user_id;
698 l_appr_deptshift_rec.CREATION_DATE:=sysdate;
699 l_appr_deptshift_rec.CREATED_BY:=fnd_global.user_id;
700 l_appr_deptshift_rec.LAST_UPDATE_LOGIN:=fnd_global.user_id;
701 l_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID:=l_department_shifts_id;
702
703 INSERT INTO AHL_DEPARTMENT_SHIFTS
704 (
705 AHL_DEPARTMENT_SHIFTS_ID,
706 OBJECT_VERSION_NUMBER,
707 LAST_UPDATE_DATE,
708 LAST_UPDATED_BY,
709 CREATION_DATE,
710 CREATED_BY,
711 LAST_UPDATE_LOGIN,
712 DEPARTMENT_ID,
713 CALENDAR_CODE,
714 SHIFT_NUM,
715 SEQ_NUM,
716 -- Following two columns added by rbhavsar on Nov 27, 2007 for ER 5854712
717 SUBINVENTORY,
718 INV_LOCATOR_ID,
719 MAX_SERVICE_CATEGORY, --added by sansatpa
720 DESCRIPTION, --added by sansatpa
721 ATTRIBUTE_CATEGORY,
722 ATTRIBUTE1,
723 ATTRIBUTE2,
724 ATTRIBUTE3,
725 ATTRIBUTE4,
726 ATTRIBUTE5,
727 ATTRIBUTE6,
728 ATTRIBUTE7,
729 ATTRIBUTE8,
730 ATTRIBUTE9,
731 ATTRIBUTE10,
732 ATTRIBUTE11,
733 ATTRIBUTE12,
734 ATTRIBUTE13,
735 ATTRIBUTE14,
736 ATTRIBUTE15
737 )
738 VALUES
739 (
740 l_DEPARTMENT_SHIFTS_ID,
741 l_appr_deptshift_rec.OBJECT_VERSION_NUMBER,
742 l_appr_deptshift_rec.LAST_UPDATE_DATE,
743 l_appr_deptshift_rec.LAST_UPDATED_BY,
744 l_appr_deptshift_rec.CREATION_DATE,
745 l_appr_deptshift_rec.CREATED_BY,
746 l_appr_deptshift_rec.LAST_UPDATE_LOGIN,
747 l_appr_deptshift_rec.DEPARTMENT_ID,
748 l_appr_deptshift_rec.CALENDAR_CODE,
749 l_appr_deptshift_rec.SHIFT_NUM,
750 l_appr_deptshift_rec.SEQ_NUM,
751 -- Following two columns added by rbhavsar on Nov 27, 2007 for ER 5854712
752 l_appr_deptshift_rec.SUBINVENTORY,
753 l_appr_deptshift_rec.INV_LOCATOR_ID,
754 l_appr_deptshift_rec.MAX_SERVICE_CATEGORY, --added by sansatpa
755 l_appr_deptshift_rec.DESCRIPTION, --added by sansatpa
756 l_appr_deptshift_rec.ATTRIBUTE_CATEGORY,
757 l_appr_deptshift_rec.ATTRIBUTE1,
758 l_appr_deptshift_rec.ATTRIBUTE2,
759 l_appr_deptshift_rec.ATTRIBUTE3,
760 l_appr_deptshift_rec.ATTRIBUTE4,
761 l_appr_deptshift_rec.ATTRIBUTE5,
762 l_appr_deptshift_rec.ATTRIBUTE6,
763 l_appr_deptshift_rec.ATTRIBUTE7,
764 l_appr_deptshift_rec.ATTRIBUTE8,
765 l_appr_deptshift_rec.ATTRIBUTE9,
766 l_appr_deptshift_rec.ATTRIBUTE10,
767 l_appr_deptshift_rec.ATTRIBUTE11,
768 l_appr_deptshift_rec.ATTRIBUTE12,
769 l_appr_deptshift_rec.ATTRIBUTE13,
770 l_appr_deptshift_rec.ATTRIBUTE14,
771 l_appr_deptshift_rec.ATTRIBUTE15)
772 returning l_DEPARTMENT_SHIFTS_ID into p_x_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID;
773
774 --p_x_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID := l_DEPARTMENT_SHIFTS_ID;
775
776 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
777 THEN
778 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
779 ' AHL_DEPARTMENT_SHIFTS_ID = ' || p_x_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID ||
780 ' OBJECT_VERSION_NUMBER = ' || p_x_appr_deptshift_rec.OBJECT_VERSION_NUMBER ||
784 ' SEQ_NUM = ' || p_x_appr_deptshift_rec.SEQ_NUM ||
781 ' DEPARTMENT_ID = ' || p_x_appr_deptshift_rec.DEPARTMENT_ID ||
782 ' CALENDAR_CODE = ' || p_x_appr_deptshift_rec.CALENDAR_CODE ||
783 ' SHIFT_NUM = ' || p_x_appr_deptshift_rec.SHIFT_NUM ||
785 ' MAX_SERVICE_CATEGORY = ' || p_x_appr_deptshift_rec.MAX_SERVICE_CATEGORY ||
786 ' DESCRIPTION = ' || p_x_appr_deptshift_rec.DESCRIPTION);
787 END IF;
788 END IF;
789
790 IF FND_API.TO_BOOLEAN(p_commit) THEN
791 COMMIT;
792 END IF;
793 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
794 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
795 END IF;
796
797 EXCEPTION
798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
799 ROLLBACK TO CREATE_APPR_DEPT_SHIFTS;
800 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
801 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
802 p_count => x_msg_count,
803 p_data => x_msg_data);
804 WHEN FND_API.G_EXC_ERROR THEN
805 ROLLBACK TO CREATE_APPR_DEPT_SHIFTS;
806 X_return_status := FND_API.G_RET_STS_ERROR;
807 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
808 p_count => x_msg_count,
809 p_data => X_msg_data);
810 WHEN OTHERS THEN
811 ROLLBACK TO CREATE_APPR_DEPT_SHIFTS;
812 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
813 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
814 THEN
815 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
816 p_procedure_name => 'CREATE_APPR_DEPT_SHIFTS',
817 p_error_text => SUBSTR(SQLERRM,1,240));
818
819 END IF;
820 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
821 p_count => x_msg_count,
822 p_data => X_msg_data);
823 END;
824
825 PROCEDURE DELETE_APPR_DEPT_SHIFTS
826 (
827 p_api_version IN NUMBER:= 1.0,
828 p_init_msg_list IN VARCHAR2,
829 p_commit IN VARCHAR2,
830 p_validation_level IN NUMBER,
831 p_default IN VARCHAR2 := FND_API.G_FALSE,
832 p_module_type IN VARCHAR2,
833 x_return_status OUT NOCOPY VARCHAR2,
834 x_msg_count OUT NOCOPY NUMBER,
835 x_msg_data OUT NOCOPY VARCHAR2,
836 p_x_appr_deptshift_rec IN OUT NOCOPY APPR_DEPTSHIFT_REC
837 )
838 AS
839 l_appr_deptshift_rec APPR_DEPTSHIFT_REC:=p_x_appr_deptshift_rec;
840 l_lookup_code VARCHAR2(30);
841 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_APPR_DEPT_SHIFTS';
842 l_api_version CONSTANT NUMBER := 1.0;
843 l_num_rec NUMBER;
844 l_msg_count NUMBER;
845 l_msg_data VARCHAR2(2000);
846 l_return_status VARCHAR2(1);
847 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
848 l_department_shifts_id number:=0;
849 l_lookup_var varchar2(1);
850 l_object_version_number NUMBER;
851 l_check_flag VARCHAR2(1):='Y';
852 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
853 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.DELETE_APPR_DEPT_SHIFTS';
854
855 BEGIN
856 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
858 END IF;
859
860 SAVEPOINT DELETE_DEPT_SHIFTS;
861
862 -- Initialize message list if p_init_msg_list is set to TRUE.
863
864 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
865 p_api_version,
866 l_api_name,G_PKG_NAME)
867 THEN
868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
869 END IF;
870
871
872 IF FND_API.to_boolean(l_init_msg_list) THEN
873 FND_MSG_PUB.initialize;
874 END IF;
875
876
877 x_return_status:=FND_API.G_RET_STS_SUCCESS;
878
879
880 IF p_module_type = 'JSP'
881 THEN
882 l_appr_deptshift_rec.ORGANIZATION_ID:=NULL;
883 l_appr_deptshift_rec.DEPARTMENT_ID:=NULL;
884 l_appr_deptshift_rec.CALENDAR_CODE:=NULL;
885 l_appr_deptshift_rec.SHIFT_NUM:=NULL;
886 END IF;
887
888
889
890
891
892 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
893 THEN
894 VALIDATE_APPR_DEPT_SHIFT
895 (
896 x_return_status =>x_return_Status,
897 x_msg_count =>l_msg_count,
898 x_msg_data =>l_msg_data,
899 p_appr_deptshift_rec =>l_appr_deptshift_rec);
900 END IF;
901
902 l_msg_count := FND_MSG_PUB.count_msg;
903
904 IF l_msg_count > 0
905 THEN
906 x_msg_count := l_msg_count;
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910
911 -- DELETE GOES HERE
912
913 IF l_appr_deptshift_rec.DML_OPERATION='D'
914 THEN
915 DELETE AHL_DEPARTMENT_SHIFTS
916 WHERE AHL_DEPARTMENT_SHIFTS_ID=l_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID
920 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
917 AND OBJECT_VERSION_NUMBER=l_appr_deptshift_rec.OBJECT_VERSION_NUMBER;
918 IF (sql%ROWCOUNT=0)
919 THEN
921 FND_MSG_PUB.ADD;
922 END IF;
923 END IF;
924
925 IF FND_API.TO_BOOLEAN(p_commit) THEN
926 COMMIT;
927 END IF;
928 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
929 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
930 END IF;
931
932 EXCEPTION
933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
934 ROLLBACK TO DELETE_DEPT_SHIFTS;
935 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
936 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
937 p_count => x_msg_count,
938 p_data => x_msg_data);
939 WHEN FND_API.G_EXC_ERROR THEN
940 ROLLBACK TO DELETE_DEPT_SHIFTS;
941 X_return_status := FND_API.G_RET_STS_ERROR;
942 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
943 p_count => x_msg_count,
944 p_data => X_msg_data);
945
946 WHEN OTHERS THEN
947 ROLLBACK TO DELETE_DEPT_SHIFTS;
948 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
949 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
950 THEN
951 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
952 p_procedure_name => 'CREATE_APPR_DEPT_SHIFTS',
953 p_error_text => SUBSTR(SQLERRM,1,240));
954
955 END IF;
956 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
957 p_count => x_msg_count,
958 p_data => X_msg_data);
959 END;
960
961 --sansatpa added for nr analysis
962 PROCEDURE EDIT_APPR_DEPT_SHIFTS
963 (
964 p_api_version IN NUMBER:= 1.0,
965 p_init_msg_list IN VARCHAR2,
966 p_commit IN VARCHAR2,
967 p_validation_level IN NUMBER,
968 p_default IN VARCHAR2 := FND_API.G_FALSE,
969 p_module_type IN VARCHAR2,
970 x_return_status OUT NOCOPY VARCHAR2,
971 x_msg_count OUT NOCOPY NUMBER,
972 x_msg_data OUT NOCOPY VARCHAR2,
973 p_x_appr_deptshift_rec IN OUT NOCOPY APPR_DEPTSHIFT_REC
974 )
975 AS
976 l_appr_deptshift_rec APPR_DEPTSHIFT_REC := p_x_appr_deptshift_rec;
977 l_lookup_code VARCHAR2(30);
978 l_api_name CONSTANT VARCHAR2(30) := 'EDIT_APPR_DEPT_SHIFTS';
979 l_api_version CONSTANT NUMBER := 1.0;
980 l_msg_count NUMBER:=0;
981 l_msg_data VARCHAR2(2000);
982 l_return_status VARCHAR2(1);
983 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
984 l_department_shifts_id number:=0;
985 l_object_version_number NUMBER;
986 l_check_flag VARCHAR2(1):='Y';
987 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
988 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.CREATE_APPR_DEPT_SHIFTS';
989 BEGIN
990 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
991 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure.');
992 END IF;
993
994 SAVEPOINT EDIT_APPR_DEPT_SHIFTS;
995
996 -- Initialize message list if p_init_msg_list is set to TRUE.
997
998 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
999 p_api_version,
1000 l_api_name,G_PKG_NAME)
1001 THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 END IF;
1004
1005 IF FND_API.to_boolean(l_init_msg_list) THEN
1006 FND_MSG_PUB.initialize;
1007 END IF;
1008
1009 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1010
1011
1012 IF p_module_type = 'JSP'
1013 THEN
1014 l_appr_deptshift_rec.ORGANIZATION_ID:=NULL;
1015 l_appr_deptshift_rec.DEPARTMENT_ID:=NULL;
1016 l_appr_deptshift_rec.CALENDAR_CODE:=NULL;
1017 -- Added by rbhavsar on Nov 27, 2007 for ER 5854712
1018 l_appr_deptshift_rec.INV_LOCATOR_ID := NULL;
1019 END IF;
1020
1021 -- Debug info.
1022 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1023 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call TRANSLATE_VALUE_ID');
1024 END IF;
1025
1026 TRANSLATE_VALUE_ID
1027 (
1028 x_return_status =>x_return_Status,
1029 x_msg_count =>l_msg_count,
1030 x_msg_data =>l_msg_data,
1031 p_x_appr_deptshift_rec =>l_appr_deptshift_rec
1032 );
1033 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1034 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from TRANSLATE_VALUE_ID. x_return_status = ' || x_return_status);
1035 END IF;
1036 l_msg_count := FND_MSG_PUB.count_msg;
1037 IF l_msg_count > 0
1038 THEN
1039 x_msg_count := l_msg_count;
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 RAISE FND_API.G_EXC_ERROR;
1042 END IF;
1043
1044
1045 IF FND_API.to_boolean(p_default)
1046 THEN
1047 DEFAULT_MISSING_ATTRIBS
1048 (
1049 p_x_appr_deptshift_rec =>l_appr_deptshift_rec
1050 );
1051 END IF;
1052
1056 VALIDATE_APPR_DEPT_SHIFT
1053 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
1054 THEN
1055
1057 (
1058 x_return_status =>x_return_status,
1059 x_msg_count =>l_msg_count,
1060 x_msg_data =>l_msg_data,
1061 p_appr_deptshift_rec =>l_appr_deptshift_rec);
1062 END IF;
1063 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1064 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from VALIDATE_APPR_DEPT_SHIFT. x_return_status = ' || x_return_status);
1065 END IF;
1066
1067 l_msg_count := FND_MSG_PUB.count_msg;
1068
1069 IF l_msg_count > 0
1070 THEN
1071 x_msg_count := l_msg_count;
1072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1073 RAISE FND_API.G_EXC_ERROR;
1074 END IF;
1075
1076 -- insert process goes here
1077 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1078 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to insert into table.');
1079 END IF;
1080 IF l_appr_deptshift_rec.DML_OPERATION = 'U'
1081 THEN
1082 /*Select AHL_DEPARTMENT_SHIFTS_S.nextval into
1083 l_department_shifts_id
1084 from dual; */
1085
1086 l_appr_deptshift_rec.LAST_UPDATE_DATE:=sysdate;
1087 l_appr_deptshift_rec.LAST_UPDATED_BY:=fnd_global.user_id;
1088 l_appr_deptshift_rec.CREATION_DATE:=sysdate;
1089 l_appr_deptshift_rec.CREATED_BY:=fnd_global.user_id;
1090 l_appr_deptshift_rec.LAST_UPDATE_LOGIN:=fnd_global.user_id;
1091 --l_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID:=l_department_shifts_id;
1092
1093 UPDATE AHL_DEPARTMENT_SHIFTS SET
1094 OBJECT_VERSION_NUMBER = l_appr_deptshift_rec.OBJECT_VERSION_NUMBER + 1,
1095 LAST_UPDATE_DATE = l_appr_deptshift_rec.LAST_UPDATE_DATE,
1096 LAST_UPDATED_BY = l_appr_deptshift_rec.LAST_UPDATED_BY,
1097 CREATION_DATE = l_appr_deptshift_rec.CREATION_DATE,
1098 CREATED_BY = l_appr_deptshift_rec.CREATED_BY,
1099 LAST_UPDATE_LOGIN = l_appr_deptshift_rec.LAST_UPDATE_LOGIN,
1100 DEPARTMENT_ID = l_appr_deptshift_rec.DEPARTMENT_ID,
1101 CALENDAR_CODE = l_appr_deptshift_rec.CALENDAR_CODE,
1102 SHIFT_NUM = l_appr_deptshift_rec.SHIFT_NUM,
1103 SEQ_NUM = l_appr_deptshift_rec.SEQ_NUM,
1104 SUBINVENTORY = l_appr_deptshift_rec.SUBINVENTORY,
1105 INV_LOCATOR_ID = l_appr_deptshift_rec.INV_LOCATOR_ID,
1106 MAX_SERVICE_CATEGORY = l_appr_deptshift_rec.MAX_SERVICE_CATEGORY,
1107 DESCRIPTION = l_appr_deptshift_rec.DESCRIPTION,
1108 ATTRIBUTE_CATEGORY = l_appr_deptshift_rec.ATTRIBUTE_CATEGORY,
1109 ATTRIBUTE1 = l_appr_deptshift_rec.ATTRIBUTE1,
1110 ATTRIBUTE2 = l_appr_deptshift_rec.ATTRIBUTE2,
1111 ATTRIBUTE3 = l_appr_deptshift_rec.ATTRIBUTE3,
1112 ATTRIBUTE4 = l_appr_deptshift_rec.ATTRIBUTE4,
1113 ATTRIBUTE5 = l_appr_deptshift_rec.ATTRIBUTE5,
1114 ATTRIBUTE6 = l_appr_deptshift_rec.ATTRIBUTE6,
1115 ATTRIBUTE7 = l_appr_deptshift_rec.ATTRIBUTE7,
1116 ATTRIBUTE8 = l_appr_deptshift_rec.ATTRIBUTE8,
1117 ATTRIBUTE9 = l_appr_deptshift_rec.ATTRIBUTE9,
1118 ATTRIBUTE10 = l_appr_deptshift_rec.ATTRIBUTE10,
1119 ATTRIBUTE11 = l_appr_deptshift_rec.ATTRIBUTE11,
1120 ATTRIBUTE12 = l_appr_deptshift_rec.ATTRIBUTE12,
1121 ATTRIBUTE13 = l_appr_deptshift_rec.ATTRIBUTE13,
1122 ATTRIBUTE14 = l_appr_deptshift_rec.ATTRIBUTE14,
1123 ATTRIBUTE15 = l_appr_deptshift_rec.ATTRIBUTE15
1124 where
1125 AHL_DEPARTMENT_SHIFTS_ID = l_appr_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID
1126 and OBJECT_VERSION_NUMBER =l_appr_deptshift_rec.OBJECT_VERSION_NUMBER;
1127 END IF;
1128
1129 -- If the record does not exist, then, abort API.
1130 IF ( SQL%ROWCOUNT = 0 ) THEN
1131 FND_MESSAGE.set_name('AHL','AHL_FMP_RECORD_CHANGED');
1132 --FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_appr_deptshift_rec ) );
1133 FND_MSG_PUB.add;
1134 END IF;
1135
1136 IF FND_API.TO_BOOLEAN(p_commit) THEN
1137 COMMIT;
1138 END IF;
1139 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1140 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
1141 END IF;
1142
1143 EXCEPTION
1144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1145 ROLLBACK TO EDIT_APPR_DEPT_SHIFTS;
1146 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1147 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1148 p_count => x_msg_count,
1149 p_data => x_msg_data);
1150 WHEN FND_API.G_EXC_ERROR THEN
1151 ROLLBACK TO EDIT_APPR_DEPT_SHIFTS;
1152 X_return_status := FND_API.G_RET_STS_ERROR;
1153 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1154 p_count => x_msg_count,
1155 p_data => X_msg_data);
1156 WHEN OTHERS THEN
1157 ROLLBACK TO EDIT_APPR_DEPT_SHIFTS;
1158 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1159 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1160 THEN
1161 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_APPR_DEPT_SHIFTS_PUB',
1162 p_procedure_name => 'CREATE_APPR_DEPT_SHIFTS',
1163 p_error_text => SUBSTR(SQLERRM,1,240));
1164
1165 END IF;
1166 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1167 p_count => x_msg_count,
1168 p_data => X_msg_data);
1169 END;
1170
1171 END AHL_APPR_DEPT_SHIFTS_PUB;