[Home] [Help]
PACKAGE BODY: APPS.AHL_VWP_DEPT_SHIFTS_PUB
Source
1 PACKAGE BODY AHL_VWP_DEPT_SHIFTS_PUB AS
2 /* $Header: AHLPDSHB.pls 115.9 2002/12/24 18:34:24 ssurapan noship $ */
3 --
4 G_PKG_NAME VARCHAR2(30):='AHL_VWP_DEPT_SHIFTS_PUB';
5 G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
6 --
7 PROCEDURE DEFAULT_MISSING_ATTRIBS
8 (
9 p_x_vwp_deptshift_rec IN OUT NOCOPY AHL_VWP_DEPT_SHIFTS_PUB.vwp_deptshift_rec
10 )
11 AS
12 BEGIN
13 IF p_x_vwp_deptshift_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM
14 THEN
15 p_x_vwp_deptshift_rec.OBJECT_VERSION_NUMBER:=NULL;
16 END IF;
17
18 IF p_x_vwp_deptshift_rec.DEPARTMENT_ID= FND_API.G_MISS_NUM
19 THEN
20 p_x_vwp_deptshift_rec.DEPARTMENT_ID:=NULL;
21 END IF;
22
23 IF p_x_vwp_deptshift_rec.SHIFT_NUM= FND_API.G_MISS_NUM
24 THEN
25 p_x_vwp_deptshift_rec.SHIFT_NUM:=NULL;
26 END IF;
27
28 IF p_x_vwp_deptshift_rec.SEQ_NUM= FND_API.G_MISS_NUM
29 THEN
30 p_x_vwp_deptshift_rec.SEQ_NUM:=NULL;
31 END IF;
32
33 IF p_x_vwp_deptshift_rec.CALENDAR_CODE= FND_API.G_MISS_CHAR
34 THEN
35 p_x_vwp_deptshift_rec.CALENDAR_CODE:=NULL;
36 END IF;
37
38 IF p_x_vwp_deptshift_Rec.ATTRIBUTE_CATEGORY IS NULL OR p_x_vwp_deptshift_Rec.ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
39 THEN
40 p_x_vwp_deptshift_Rec.ATTRIBUTE_CATEGORY:=NULL;
41 END IF;
42
43 IF p_x_vwp_deptshift_Rec.ATTRIBUTE1=FND_API.G_MISS_CHAR
44 THEN
45 p_x_vwp_deptshift_Rec.ATTRIBUTE1:=NULL;
46 END IF;
47
48 IF p_x_vwp_deptshift_Rec.ATTRIBUTE2=FND_API.G_MISS_CHAR
49 THEN
50 p_x_vwp_deptshift_Rec.ATTRIBUTE2:=NULL;
51 END IF;
52
53 IF p_x_vwp_deptshift_Rec.ATTRIBUTE3=FND_API.G_MISS_CHAR
54 THEN
55 p_x_vwp_deptshift_Rec.ATTRIBUTE3:=NULL;
56 END IF;
57
58 IF p_x_vwp_deptshift_Rec.ATTRIBUTE4 IS NULL OR p_x_vwp_deptshift_Rec.ATTRIBUTE4=FND_API.G_MISS_CHAR
59 THEN
60 p_x_vwp_deptshift_Rec.ATTRIBUTE4:=NULL;
61 END IF;
62
63 IF p_x_vwp_deptshift_Rec.ATTRIBUTE5=FND_API.G_MISS_CHAR
64 THEN
65 p_x_vwp_deptshift_Rec.ATTRIBUTE5:=NULL;
66 END IF;
67
68 IF p_x_vwp_deptshift_Rec.ATTRIBUTE6=FND_API.G_MISS_CHAR
69 THEN
70 p_x_vwp_deptshift_Rec.ATTRIBUTE6:=NULL;
71 END IF;
72
73 IF p_x_vwp_deptshift_Rec.ATTRIBUTE7=FND_API.G_MISS_CHAR
74 THEN
75 p_x_vwp_deptshift_Rec.ATTRIBUTE7:=NULL;
76 END IF;
77
78 IF p_x_vwp_deptshift_Rec.ATTRIBUTE8=FND_API.G_MISS_CHAR
79 THEN
80 p_x_vwp_deptshift_Rec.ATTRIBUTE8:=NULL;
81 END IF;
82
83 IF p_x_vwp_deptshift_Rec.ATTRIBUTE9=FND_API.G_MISS_CHAR
84 THEN
85 p_x_vwp_deptshift_Rec.ATTRIBUTE9:=NULL;
86 END IF;
87
88 IF p_x_vwp_deptshift_Rec.ATTRIBUTE10=FND_API.G_MISS_CHAR
89 THEN
90 p_x_vwp_deptshift_Rec.ATTRIBUTE10:=NULL;
91 END IF;
92
93 IF p_x_vwp_deptshift_Rec.ATTRIBUTE11=FND_API.G_MISS_CHAR
94 THEN
95 p_x_vwp_deptshift_Rec.ATTRIBUTE11:=NULL;
96 END IF;
97
98 IF p_x_vwp_deptshift_Rec.ATTRIBUTE12 IS NULL OR p_x_vwp_deptshift_Rec.ATTRIBUTE12=FND_API.G_MISS_CHAR
99 THEN
100 p_x_vwp_deptshift_Rec.ATTRIBUTE12:=NULL;
101 END IF;
102
103 IF p_x_vwp_deptshift_Rec.ATTRIBUTE13=FND_API.G_MISS_CHAR
104 THEN
105 p_x_vwp_deptshift_Rec.ATTRIBUTE13:=NULL;
106 END IF;
107
108 IF p_x_vwp_deptshift_Rec.ATTRIBUTE14=FND_API.G_MISS_CHAR
109 THEN
110 p_x_vwp_deptshift_Rec.ATTRIBUTE14:=NULL;
111 END IF;
112
113 IF p_x_vwp_deptshift_Rec.ATTRIBUTE15=FND_API.G_MISS_CHAR
114 THEN
115 p_x_vwp_deptshift_Rec.ATTRIBUTE15:=NULL;
116 END IF;
117 END;
118
119 PROCEDURE TRANSLATE_VALUE_ID
120 (
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2,
124 p_x_vwp_deptshift_rec IN OUT NOCOPY AHL_VWP_DEPT_SHIFTS_PUB.VWP_DEPTSHIFT_REC
125 )
126 as
127 CURSOR get_org_id(C_NAME VARCHAR2)
128 IS
129 SELECT ORGANIZATION_ID
130 FROM HR_ALL_ORGANIZATION_UNITS
131 WHERE NAME=C_NAME;
132
133 CURSOR get_org_dept_id(C_ORG_ID NUMBER,C_DESCRIPTION VARCHAR2)
134 IS
135 SELECT DEPARTMENT_ID
136 FROM BOM_DEPARTMENTS_V
137 WHERE ORGANIZATION_ID=C_ORG_ID
138 AND DESCRIPTION=C_DESCRIPTION;
139
140 CURSOR get_bom_calendar(C_CALENDAR VARCHAR2)
141 IS
142 SELECT CALENDAR_CODE
143 FROM BOM_CALENDARS
144 WHERE DESCRIPTION=C_CALENDAR;
145
146
147 CURSOR get_bom_shift_num(C_CALENDAR_CODE VARCHAR2,C_SHIFT_NUM NUMBER)
148 IS
149 SELECT SHIFT_NUM
150 FROM BOM_SHIFT_TIMES
151 WHERE CALENDAR_CODE=C_CALENDAR_CODE
152 AND SHIFT_NUM=C_SHIFT_NUM;
153
154 CURSOR get_bom_workdays(C_CALENDAR_CODE VARCHAR2,C_SHIFT NUMBER,C_DESCRIPTION VARCHAR2)
155 IS
156 SELECT SEQ_NUM
157 FROM BOM_WORKDAY_PATTERNS
158 WHERE CALENDAR_CODE=C_CALENDAR_CODE
159 AND SHIFT_NUM=C_SHIFT
160 AND DESCRIPTION=C_DESCRIPTION;
161
162 l_lookup_code VARCHAR2(30);
163 l_num_rec NUMBER;
164 l_msg_count NUMBER;
165 l_msg_data VARCHAR2(2000);
166 l_return_status VARCHAR2(1);
167 l_mr_header_id number:=0;
168 l_lookup_var varchar2(1);
169 l_object_version_number NUMBER;
170 l_check_flag VARCHAR2(1):='N';
171 l_counter NUMBER:=0;
172 BEGIN
173 IF G_DEBUG='Y' THEN
174 AHL_DEBUG_PUB.enable_debug;
175 END IF;
176 x_return_status:=FND_API.G_RET_STS_SUCCESS;
177 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside translate1 -- l_check_flag' || l_check_flag);
178 IF p_x_vwp_deptshift_rec.ORGANIZATION_NAME IS NULL OR p_x_vwp_deptshift_rec.ORGANIZATION_NAME=FND_API.G_MISS_CHAR
179 THEN
180 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside organization-- l_check_flag' || l_check_flag);
181 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_ORG_NAME_NULL');
182 FND_MSG_PUB.ADD;
183 l_check_flag:='N';
184 ELSE
185 l_check_flag:='Y';
186 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside org cursor -- l_check_flag' || l_check_flag);
187 OPEN get_org_id(p_x_vwp_deptshift_rec.ORGANIZATION_NAME);
188 FETCH get_org_id INTO p_x_vwp_deptshift_rec.ORGANIZATION_ID;
189 IF get_org_id%NOTFOUND
190 THEN
191 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_ORG_NAME_INVALID');
192 FND_MSG_PUB.ADD;
193 l_check_flag:='N';
194 END IF;
195 CLOSE get_org_id;
196 END IF;
197
198 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside translate3 -- l_check_flag' || l_check_flag);
199 l_check_flag:='N';
200 IF p_x_vwp_deptshift_rec.DEPT_DESCRIPTION IS NULL OR p_x_vwp_deptshift_rec.DEPT_DESCRIPTION=FND_API.G_MISS_CHAR
201 THEN
202 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside department-- l_check_flag' || l_check_flag);
203 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_DEPT_NAME_NULL');
204 FND_MSG_PUB.ADD;
205 l_check_flag:='N';
206 ELSE
207 l_check_flag:='Y';
208 END IF;
209
210 IF l_check_flag='Y'
211 THEN
212 OPEN get_org_dept_id(p_x_vwp_deptshift_rec.ORGANIZATION_ID,p_x_vwp_deptshift_rec.DEPT_DESCRIPTION);
213 FETCH get_org_dept_id INTO p_x_vwp_deptshift_rec.DEPARTMENT_ID;
214 IF get_org_dept_id%NOTFOUND
215 THEN
216 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_DEPT_NAME_INVALID');
217 FND_MSG_PUB.ADD;
218 l_check_flag:='N';
219 END IF;
220 CLOSE get_org_dept_id;
221 END IF;
222
223 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside translate2 -- l_check_flag' || l_check_flag);
224
225 l_check_flag:='N';
226 IF p_x_vwp_deptshift_rec.CALENDAR_DESCRIPTION IS NULL OR p_x_vwp_deptshift_rec.CALENDAR_DESCRIPTION=FND_API.G_MISS_CHAR
227 THEN
228 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside calender-- l_check_flag' || l_check_flag);
229 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_CALENDER_NAME_NULL');
230 FND_MSG_PUB.ADD;
231 l_check_flag:='N';
232 ELSE
233 l_check_flag:='Y';
234 END IF;
235
236
237 IF l_check_flag='Y'
238 THEN
239 OPEN get_bom_calendar(p_x_vwp_deptshift_rec.CALENDAR_DESCRIPTION);
240 FETCH get_bom_calendar INTO p_x_vwp_deptshift_rec.CALENDAR_CODE;
241 IF get_bom_calendar%NOTFOUND
242 THEN
243 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_CALENDER_INVALID');
244 FND_MSG_PUB.ADD;
245 l_check_flag:='N';
246 ELSE
247 l_check_flag:='Y';
248 END IF;
249 CLOSE get_bom_calendar;
250 END IF;
251
252 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside translate2 -- l_check_flag' || l_check_flag);
253
254 l_check_flag:='N';
255 IF p_x_vwp_deptshift_rec.SHIFT_NUM IS NULL OR p_x_vwp_deptshift_rec.SHIFT_NUM=FND_API.G_MISS_NUM
256 THEN
257 --AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside shift number-- l_check_flag' || l_check_flag);
258 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_SHIFT_NUMBER_NULL');
259 FND_MSG_PUB.ADD;
260 l_check_flag:='N';
261 ELSE
262 l_check_flag:='Y';
263 END IF;
264
265 IF l_check_flag='Y'
266 THEN
267 OPEN get_bom_shift_num(p_x_vwp_deptshift_rec.CALENDAR_CODE,p_x_vwp_deptshift_rec.SHIFT_NUM);
268 FETCH get_bom_shift_num INTO p_x_vwp_deptshift_rec.SHIFT_NUM;
269 IF get_bom_shift_num%NOTFOUND
270 THEN
271 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_SHIFT_NUMBER_INVALID');
272 FND_MSG_PUB.ADD;
273 l_check_flag:='N';
274 ELSE
275 l_check_flag:='Y';
276 END IF;
277 CLOSE get_bom_shift_num;
278 END IF;
279
280 IF G_DEBUG='Y' THEN
281 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside translate2 -- l_check_flag' || l_check_flag);
282 END IF;
283
284 l_check_flag:='N';
285 IF p_x_vwp_deptshift_rec.SEQ_NAME IS NULL OR p_x_vwp_deptshift_rec.SEQ_NAME=FND_API.G_MISS_CHAR
286 THEN
287 IF G_DEBUG='Y' THEN
288 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside sequence number-- l_check_flag' || l_check_flag);
289 END IF;
290 --
291 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_WORK_DAYS_NULL');
292 FND_MSG_PUB.ADD;
293 l_check_flag:='N';
294 ELSE
295 l_check_flag:='Y';
296 END IF;
297 IF G_DEBUG='Y' THEN
298 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside translate2 -- l_check_flag' || l_check_flag);
299 END IF;
300 IF l_check_flag='Y'
301 THEN
302 IF G_DEBUG='Y' THEN
303 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- inside sequence number ***** -- l_check_flag' || l_check_flag);
304 END IF;
305 OPEN get_bom_workdays(p_x_vwp_deptshift_rec.CALENDAR_CODE,p_x_vwp_deptshift_rec.SHIFT_NUM,p_x_vwp_deptshift_rec.SEQ_NAME);
306 FETCH get_bom_workdays INTO p_x_vwp_deptshift_rec.SEQ_NUM;
307 IF get_bom_workdays%NOTFOUND
308 THEN
309 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_SEQ_DESCRIP_INVALID');
310 FND_MSG_PUB.ADD;
311 END IF;
312 CLOSE get_bom_workdays;
313 END IF;
314 IF G_DEBUG='Y' THEN
315 AHL_DEBUG_PUB.disable_debug;
316 END IF;
317
318 EXCEPTION
319 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
322 p_count => x_msg_count,
323 p_data => x_msg_data);
324
325 WHEN FND_API.G_EXC_ERROR THEN
326 X_return_status := FND_API.G_RET_STS_ERROR;
327 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
328 p_count => x_msg_count,
329 p_data => X_msg_data);
330 WHEN OTHERS THEN
331 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
332 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
333 THEN
334 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_VWP_DEPT_SHIFTS_PUB',
335 p_procedure_name => 'TRANSLATE_VALUE_ID',
336 p_error_text => SUBSTR(SQLERRM,1,240));
337 END IF;
338 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
339 p_count => x_msg_count,
340 p_data => x_msg_data);
341 END;
342
343 -- Start of Validate
344
345 PROCEDURE VALIDATE_VWP_DEPT_SHIFT
346 (
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2,
350 p_vwp_deptshift_rec IN AHL_VWP_DEPT_SHIFTS_PUB.vwp_deptshift_rec
351 )
352 as
353 l_counter NUMBER:=0;
354 l_prim_key NUMBER:=0;
355 BEGIN
356 x_return_status:=FND_API.G_RET_STS_SUCCESS;
357
358 IF p_vwp_deptshift_rec.DML_OPERATION='D'
359 THEN
360 IF p_vwp_deptshift_rec.OBJECT_VERSION_NUMBER IS NULL OR p_vwp_deptshift_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM
361 THEN
362 FND_MESSAGE.SET_NAME('AHL','AHL_COM_OBJECT_VERS_NUM_NULL');
363 FND_MSG_PUB.ADD;
364 END IF;
365
366 IF p_vwp_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID IS NULL OR p_vwp_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID=FND_API.G_MISS_NUM
367 THEN
368 FND_MESSAGE.SET_NAME('AHL','AHL_DEPARTMENT_SHIFTS_ID_NULL');
369 FND_MSG_PUB.ADD;
370 ELSE
371 SELECT AHL_DEPARTMENT_SHIFTS_ID INTO l_prim_key
372 FROM AHL_DEPARTMENT_SHIFTS
373 WHERE AHL_DEPARTMENT_SHIFTS_ID=p_vwp_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID;
374 END IF;
375 ELSE
376 Select COUNT(DEPARTMENT_ID) INTO l_counter
377 FROM AHL_DEPARTMENT_SHIFTS
378 WHERE DEPARTMENT_ID=p_vwp_deptshift_rec.DEPARTMENT_ID;
379
380 IF l_counter>0
381 THEN
382 FND_MESSAGE.SET_NAME('AHL','AHL_VWP_DEPT_EXISTS');
383 FND_MSG_PUB.ADD;
384 END IF;
385 END IF;
386
387 EXCEPTION
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
392 p_data => x_msg_data);
389 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
390 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
391 p_count => x_msg_count,
393
394 WHEN FND_API.G_EXC_ERROR THEN
395 X_return_status := FND_API.G_RET_STS_ERROR;
396 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
397 p_count => x_msg_count,
398 p_data => X_msg_data);
399
400 WHEN OTHERS THEN
401 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
402 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
403 THEN
404 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_VWP_DEPT_SHIFTS_PUB',
405 p_procedure_name => 'VALIDATE_VWP_DEPT_SHIFT',
406 p_error_text => SUBSTR(SQLERRM,1,240));
407 END IF;
408 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
409 p_count => x_msg_count,
410 p_data => X_msg_data);
411
412 END;
413
414 PROCEDURE CREATE_VWP_DEPT_SHIFTS
415 (
416 p_api_version IN NUMBER:= 1.0,
417 p_init_msg_list IN VARCHAR2,
418 p_commit IN VARCHAR2,
419 p_validation_level IN NUMBER,
420 p_default IN VARCHAR2 := FND_API.G_FALSE,
421 p_module_type IN VARCHAR2,
422 x_return_status OUT NOCOPY VARCHAR2,
423 x_msg_count OUT NOCOPY NUMBER,
424 x_msg_data OUT NOCOPY VARCHAR2,
425 p_x_vwp_deptshift_rec IN OUT NOCOPY VWP_DEPTSHIFT_REC
426 )
427 AS
428 l_vwp_deptshift_rec VWP_DEPTSHIFT_REC:=p_x_vwp_deptshift_rec;
429 l_lookup_code VARCHAR2(30);
430 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_VWP_DEPT_SHIFTS';
431 l_api_version CONSTANT NUMBER := 1.0;
432 l_msg_count NUMBER:=0;
433 l_msg_data VARCHAR2(2000);
434 l_return_status VARCHAR2(1);
435 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
436 l_department_shifts_id number:=0;
437 l_object_version_number NUMBER;
438 l_check_flag VARCHAR2(1):='Y';
439 BEGIN
440
441 SAVEPOINT CREATE_VWP_DEPT_SHIFTS;
442
443 -- Initialize message list if p_init_msg_list is set to TRUE.
444
445 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
446 p_api_version,
447 l_api_name,G_PKG_NAME)
448 THEN
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452 IF FND_API.to_boolean(l_init_msg_list) THEN
453 FND_MSG_PUB.initialize;
454 END IF;
455
456 x_return_status:=FND_API.G_RET_STS_SUCCESS;
457
458 IF G_DEBUG='Y' THEN
459 AHL_DEBUG_PUB.enable_debug;
460 END IF;
461
462 IF p_module_type = 'JSP'
463 THEN
464 l_vwp_deptshift_rec.ORGANIZATION_ID:=NULL;
465 l_vwp_deptshift_rec.DEPARTMENT_ID:=NULL;
466 l_vwp_deptshift_rec.CALENDAR_CODE:=NULL;
467 END IF;
468
469 -- Debug info.
470
471 IF G_DEBUG='Y' THEN
472 AHL_DEBUG_PUB.debug( 'AHL_VWP_DEPT_SHIFTS_PUB.','+CREATE_VWP_DEPT_SHIFTS+');
473 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- before translate shift_num ' || l_vwp_deptshift_rec.SHIFT_NUM);
474 END IF;
475
476 TRANSLATE_VALUE_ID
477 (
478 x_return_status =>x_return_Status,
479 x_msg_count =>l_msg_count,
480 x_msg_data =>l_msg_data,
481 p_x_vwp_deptshift_rec =>l_vwp_deptshift_rec
482 );
483 IF G_DEBUG='Y' THEN
484 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- after transalate shift_num' || l_vwp_deptshift_rec.SHIFT_NUM);
485 END IF;
486 l_msg_count := FND_MSG_PUB.count_msg;
487 IF l_msg_count > 0
488 THEN
489 x_msg_count := l_msg_count;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493
494
495 IF FND_API.to_boolean(p_default)
496 THEN
497 DEFAULT_MISSING_ATTRIBS
498 (
499 p_x_vwp_deptshift_rec =>l_vwp_deptshift_rec
500 );
501 END IF;
502
503 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
504 THEN
505
506 VALIDATE_VWP_DEPT_SHIFT
507 (
508 x_return_status =>x_return_status,
509 x_msg_count =>l_msg_count,
510 x_msg_data =>l_msg_data,
511 p_vwp_deptshift_rec =>l_vwp_deptshift_rec);
512 END IF;
513 IF G_DEBUG='Y' THEN
514 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- after validation shift_num' || l_vwp_deptshift_rec.SHIFT_NUM);
515 END IF;
516
517 l_msg_count := FND_MSG_PUB.count_msg;
518
519 IF l_msg_count > 0
520 THEN
524 END IF;
521 x_msg_count := l_msg_count;
522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
523 RAISE FND_API.G_EXC_ERROR;
525
526 -- insert process goes here
527 IF G_DEBUG='Y' THEN
528 AHL_DEBUG_PUB.debug('AHL_VWP_DEPT_SHIFTS_PUB -- before insert shift_num' || l_vwp_deptshift_rec.SHIFT_NUM);
529 END IF;
530 IF l_vwp_deptshift_rec.DML_OPERATION='C'
531 THEN
532 Select AHL_DEPARTMENT_SHIFTS_S.nextval into
533 l_department_shifts_id
534 from dual;
535
536 l_vwp_deptshift_rec.OBJECT_VERSION_NUMBER:=1;
537 l_vwp_deptshift_rec.LAST_UPDATE_DATE:=sysdate;
538 l_vwp_deptshift_rec.LAST_UPDATED_BY:=fnd_global.user_id;
539 l_vwp_deptshift_rec.CREATION_DATE:=sysdate;
540 l_vwp_deptshift_rec.CREATED_BY:=fnd_global.user_id;
541 l_vwp_deptshift_rec.LAST_UPDATE_LOGIN:=fnd_global.user_id;
542 l_vwp_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID:=l_department_shifts_id;
543
544 INSERT INTO AHL_DEPARTMENT_SHIFTS
545 (
546 AHL_DEPARTMENT_SHIFTS_ID,
547 OBJECT_VERSION_NUMBER,
548 LAST_UPDATE_DATE,
549 LAST_UPDATED_BY,
550 CREATION_DATE,
551 CREATED_BY,
552 LAST_UPDATE_LOGIN,
553 DEPARTMENT_ID,
554 CALENDAR_CODE,
555 SHIFT_NUM,
556 SEQ_NUM,
557 ATTRIBUTE_CATEGORY,
558 ATTRIBUTE1,
559 ATTRIBUTE2,
560 ATTRIBUTE3,
561 ATTRIBUTE4,
562 ATTRIBUTE5,
563 ATTRIBUTE6,
564 ATTRIBUTE7,
565 ATTRIBUTE8,
566 ATTRIBUTE9,
567 ATTRIBUTE10,
568 ATTRIBUTE11,
569 ATTRIBUTE12,
570 ATTRIBUTE13,
571 ATTRIBUTE14,
572 ATTRIBUTE15
573 )
574 VALUES
575 (
576 l_DEPARTMENT_SHIFTS_ID,
577 l_vwp_deptshift_rec.OBJECT_VERSION_NUMBER,
578 l_vwp_deptshift_rec.LAST_UPDATE_DATE,
579 l_vwp_deptshift_rec.LAST_UPDATED_BY,
580 l_vwp_deptshift_rec.CREATION_DATE,
581 l_vwp_deptshift_rec.CREATED_BY,
582 l_vwp_deptshift_rec.LAST_UPDATE_LOGIN,
583 l_vwp_deptshift_rec.DEPARTMENT_ID,
584 l_vwp_deptshift_rec.CALENDAR_CODE,
585 l_vwp_deptshift_rec.SHIFT_NUM,
586 l_vwp_deptshift_rec.SEQ_NUM,
587 l_vwp_deptshift_rec.ATTRIBUTE_CATEGORY,
588 l_vwp_deptshift_rec.ATTRIBUTE1,
589 l_vwp_deptshift_rec.ATTRIBUTE2,
590 l_vwp_deptshift_rec.ATTRIBUTE3,
591 l_vwp_deptshift_rec.ATTRIBUTE4,
592 l_vwp_deptshift_rec.ATTRIBUTE5,
593 l_vwp_deptshift_rec.ATTRIBUTE6,
594 l_vwp_deptshift_rec.ATTRIBUTE7,
595 l_vwp_deptshift_rec.ATTRIBUTE8,
596 l_vwp_deptshift_rec.ATTRIBUTE9,
597 l_vwp_deptshift_rec.ATTRIBUTE10,
598 l_vwp_deptshift_rec.ATTRIBUTE11,
599 l_vwp_deptshift_rec.ATTRIBUTE12,
600 l_vwp_deptshift_rec.ATTRIBUTE13,
601 l_vwp_deptshift_rec.ATTRIBUTE14,
602 l_vwp_deptshift_rec.ATTRIBUTE15);
603 END IF;
604
605 IF FND_API.TO_BOOLEAN(p_commit) THEN
606 COMMIT;
607 END IF;
608 IF G_DEBUG='Y' THEN
609 AHL_DEBUG_PUB.disable_debug;
610 END IF;
611
612 EXCEPTION
613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
614 ROLLBACK TO CREATE_VWP_DEPT_SHIFTS;
615 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
616 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
617 p_count => x_msg_count,
618 p_data => x_msg_data);
619 WHEN FND_API.G_EXC_ERROR THEN
620 ROLLBACK TO CREATE_VWP_DEPT_SHIFTS;
621 X_return_status := FND_API.G_RET_STS_ERROR;
622 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
623 p_count => x_msg_count,
624 p_data => X_msg_data);
625 WHEN OTHERS THEN
626 ROLLBACK TO CREATE_VWP_DEPT_SHIFTS;
627 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
628 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
629 THEN
630 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_VWP_DEPT_SHIFTS_PUB',
631 p_procedure_name => 'CREATE_VWP_DEPT_SHIFTS',
632 p_error_text => SUBSTR(SQLERRM,1,240));
633
634 END IF;
635 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
636 p_count => x_msg_count,
637 p_data => X_msg_data);
638 END;
639
640 PROCEDURE DELETE_VWP_DEPT_SHIFTS
641 (
645 p_validation_level IN NUMBER,
642 p_api_version IN NUMBER:= 1.0,
643 p_init_msg_list IN VARCHAR2,
644 p_commit IN VARCHAR2,
646 p_default IN VARCHAR2 := FND_API.G_FALSE,
647 p_module_type IN VARCHAR2,
648 x_return_status OUT NOCOPY VARCHAR2,
649 x_msg_count OUT NOCOPY NUMBER,
650 x_msg_data OUT NOCOPY VARCHAR2,
651 p_x_vwp_deptshift_rec IN OUT NOCOPY VWP_DEPTSHIFT_REC
652 )
653 AS
654 l_vwp_deptshift_rec VWP_DEPTSHIFT_REC:=p_x_vwp_deptshift_rec;
655 l_lookup_code VARCHAR2(30);
656 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_VWP_DEPT_SHIFTS';
657 l_api_version CONSTANT NUMBER := 1.0;
658 l_num_rec NUMBER;
659 l_msg_count NUMBER;
660 l_msg_data VARCHAR2(2000);
661 l_return_status VARCHAR2(1);
662 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
663 l_department_shifts_id number:=0;
664 l_lookup_var varchar2(1);
665 l_object_version_number NUMBER;
666 l_check_flag VARCHAR2(1):='Y';
667
668 BEGIN
669
670 SAVEPOINT DELETE_DEPT_SHIFTS;
671
672 -- Initialize message list if p_init_msg_list is set to TRUE.
673
674 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
675 p_api_version,
676 l_api_name,G_PKG_NAME)
677 THEN
678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
679 END IF;
680
681
682 IF FND_API.to_boolean(l_init_msg_list) THEN
683 FND_MSG_PUB.initialize;
684 END IF;
685
686
687 x_return_status:=FND_API.G_RET_STS_SUCCESS;
688
689 IF G_DEBUG='Y' THEN
690 AHL_DEBUG_PUB.enable_debug;
691 END IF;
692
693 IF p_module_type = 'JSP'
694 THEN
695 l_vwp_deptshift_rec.ORGANIZATION_ID:=NULL;
696 l_vwp_deptshift_rec.DEPARTMENT_ID:=NULL;
697 l_vwp_deptshift_rec.CALENDAR_CODE:=NULL;
698 l_vwp_deptshift_rec.SHIFT_NUM:=NULL;
699 END IF;
700
701 -- Debug info.
702
703 IF G_DEBUG='Y' THEN
704 AHL_DEBUG_PUB.debug( 'AHL_VWP_DEPT_SHITS_PUB.','+delete_VWP_DEPT_SHIFTS+');
705 END IF;
706
707
708
709 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
710 THEN
711 VALIDATE_VWP_DEPT_SHIFT
712 (
713 x_return_status =>x_return_Status,
714 x_msg_count =>l_msg_count,
715 x_msg_data =>l_msg_data,
716 p_vwp_deptshift_rec =>l_vwp_deptshift_rec);
717 END IF;
718
719 l_msg_count := FND_MSG_PUB.count_msg;
720
721 IF l_msg_count > 0
722 THEN
723 x_msg_count := l_msg_count;
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727
728 -- DELETE GOES HERE
729
730 IF l_vwp_deptshift_rec.DML_OPERATION='D'
731 THEN
732 DELETE AHL_DEPARTMENT_SHIFTS
733 WHERE AHL_DEPARTMENT_SHIFTS_ID=l_vwp_deptshift_rec.AHL_DEPARTMENT_SHIFTS_ID
734 AND OBJECT_VERSION_NUMBER=l_vwp_deptshift_rec.OBJECT_VERSION_NUMBER;
735 IF (sql%ROWCOUNT=0)
736 THEN
737 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
738 FND_MSG_PUB.ADD;
739 END IF;
740 END IF;
741
742 IF FND_API.TO_BOOLEAN(p_commit) THEN
743 COMMIT;
744 END IF;
745 IF G_DEBUG='Y' THEN
746 AHL_DEBUG_PUB.disable_debug;
747 END IF;
748
749 EXCEPTION
750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
751 ROLLBACK TO DELETE_DEPT_SHIFTS;
752 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
754 p_count => x_msg_count,
755 p_data => x_msg_data);
756 WHEN FND_API.G_EXC_ERROR THEN
757 ROLLBACK TO DELETE_DEPT_SHIFTS;
758 X_return_status := FND_API.G_RET_STS_ERROR;
759 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
760 p_count => x_msg_count,
761 p_data => X_msg_data);
762
763 WHEN OTHERS THEN
764 ROLLBACK TO DELETE_DEPT_SHIFTS;
765 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
767 THEN
768 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_VWP_DEPT_SHIFTS_PUB',
769 p_procedure_name => 'CREATE_VWP_DEPT_SHIFTS',
770 p_error_text => SUBSTR(SQLERRM,1,240));
771
772 END IF;
773 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
774 p_count => x_msg_count,
775 p_data => X_msg_data);
776 END;
777
778 END AHL_VWP_DEPT_SHIFTS_PUB;