1 PACKAGE BODY pn_space_assign_emp_pkg AS
2 /* $Header: PNSPEMPB.pls 120.7 2005/12/01 03:32:06 appldev ship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : Insert_Row
6 -- DESCRIPTION : inserts a row in pn_space_assign_emp_all
7 -- SCOPE : PUBLIC
8 -- INVOKED FROM :
9 -- RETURNS : NONE
10 -- HISTORY :
11 -- 14-DEC-04 STripath o Modified for Portfolio Status Enh BUG# 4030816.
12 -- Added code to check loc is contigious assignable
13 -- betn assign start and end dates.
14 -- 28-APR-05 piagrawa o Bug 4284035 - Replaced pn_space_assign_emp with _ALL
15 -- 28-NOV-05 pikhar o fetched org_id using cursor
16 -------------------------------------------------------------------------------
17 PROCEDURE Insert_Row (
18 x_rowid IN OUT NOCOPY VARCHAR2,
19 x_emp_space_assign_id IN OUT NOCOPY NUMBER,
20 x_attribute1 IN VARCHAR2,
21 x_attribute2 IN VARCHAR2,
22 x_attribute3 IN VARCHAR2,
23 x_attribute4 IN VARCHAR2,
24 x_attribute5 IN VARCHAR2,
25 x_attribute6 IN VARCHAR2,
26 x_attribute7 IN VARCHAR2,
27 x_attribute8 IN VARCHAR2,
28 x_attribute9 IN VARCHAR2,
29 x_attribute10 IN VARCHAR2,
30 x_attribute11 IN VARCHAR2,
31 x_attribute12 IN VARCHAR2,
32 x_attribute13 IN VARCHAR2,
33 x_attribute14 IN VARCHAR2,
34 x_attribute15 IN VARCHAR2,
35 x_location_id IN NUMBER,
36 x_person_id IN NUMBER,
37 x_project_id IN NUMBER,
38 x_task_id IN NUMBER,
39 x_emp_assign_start_date IN DATE,
40 x_emp_assign_end_date IN DATE,
41 x_cost_center_code IN VARCHAR2,
42 x_allocated_area_pct IN NUMBER,
43 x_allocated_area IN NUMBER,
44 x_utilized_area IN NUMBER,
45 x_emp_space_comments IN VARCHAR2,
46 x_attribute_category IN VARCHAR2,
47 x_creation_date IN DATE,
48 x_created_by IN NUMBER,
49 x_last_update_date IN DATE,
50 x_last_updated_by IN NUMBER,
51 x_last_update_login IN NUMBER,
52 x_org_id IN NUMBER,
53 x_source IN VARCHAR2
54 )
55 IS
56
57 CURSOR c IS
58 SELECT ROWID
59 FROM pn_space_assign_emp_all
60 WHERE emp_space_assign_id = x_emp_space_assign_id;
61
62 l_err_msg VARCHAR2(30);
63 l_asgn_mode VARCHAR2(30);
64
65 CURSOR org_cur IS
66 SELECT org_id
67 FROM pn_locations_all
68 WHERE location_id = x_location_id;
69
70 l_org_id NUMBER;
71
72 BEGIN
73
74 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.INSERT_ROW (+) SpcAsgnId: '
75 ||x_emp_space_assign_id||', LocId: '||x_location_id
76 ||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
77 ||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
78 ||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
79
80 -- Check if location is contigious Employee/CC Assignable betn assign start and end dates.
81 IF x_person_id IS NOT NULL THEN
82 l_asgn_mode := 'EMP';
83 ELSE
84 l_asgn_mode := 'CC';
85 END IF;
86 pnt_locations_pkg.Check_Location_Gaps(
87 p_loc_id => x_location_id
88 ,p_str_dt => x_emp_assign_start_date
89 ,p_end_dt => NVL(x_emp_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'))
90 ,p_asgn_mode => l_asgn_mode
91 ,p_err_msg => l_err_msg
92 );
93
94 IF l_err_msg IS NOT NULL THEN
95 fnd_message.set_name('PN', 'PN_INVALID_SPACE_ASSGN_DATE');
96 app_exception.raise_exception;
97 END IF;
98
99 pn_space_assign_emp_pkg.check_dupemp_assign(p_person_id => x_person_id,
100 p_cost_cntr_code => x_cost_center_code,
101 p_loc_id => x_location_id,
102 p_assgn_str_dt => x_emp_assign_start_date);
103
104 -------------------------------------------------------
105 -- Select the nextval for emp space assign id
106 -------------------------------------------------------
107
108 IF x_org_id IS NULL THEN
109 FOR rec IN org_cur LOOP
110 l_org_id := rec.org_id;
111 END LOOP;
112 ELSE
113 l_org_id := x_org_id;
114 END IF;
115
116 IF (x_emp_space_assign_id IS NULL) THEN
117
118 SELECT pn_space_assign_emp_s.NEXTVAL
119 INTO x_emp_space_assign_id
120 FROM DUAL;
121 END IF;
122
123 INSERT INTO pn_space_assign_emp_all
124 (
125 attribute1,
126 attribute2,
127 attribute3,
128 attribute4,
129 attribute5,
130 attribute6,
131 attribute7,
132 attribute8,
133 attribute9,
134 attribute10,
135 attribute11,
136 attribute12,
137 attribute13,
138 attribute14,
139 attribute15,
140 emp_space_assign_id,
141 location_id,
142 person_id,
143 project_id,
144 task_id,
145 emp_assign_start_date,
146 emp_assign_end_date,
147 cost_center_code,
148 allocated_area_pct,
149 allocated_area,
150 utilized_area,
151 emp_space_comments,
152 last_update_date,
153 last_updated_by,
154 creation_date,
155 created_by,
156 last_update_login,
157 attribute_category,
158 org_id,
159 source
160 )
161 VALUES
162 (
163 x_attribute1,
164 x_attribute2,
165 x_attribute3,
166 x_attribute4,
167 x_attribute5,
168 x_attribute6,
169 x_attribute7,
170 x_attribute8,
171 x_attribute9,
172 x_attribute10,
173 x_attribute11,
174 x_attribute12,
175 x_attribute13,
176 x_attribute14,
177 x_attribute15,
178 x_emp_space_assign_id,
179 x_location_id,
180 x_person_id,
181 x_project_id,
182 x_task_id,
183 x_emp_assign_start_date,
184 x_emp_assign_end_date,
185 x_cost_center_code,
186 x_allocated_area_pct,
187 x_allocated_area,
188 x_utilized_area,
189 x_emp_space_comments,
190 x_last_update_date,
191 x_last_updated_by,
192 x_creation_date,
193 x_created_by,
194 x_last_update_login,
195 x_attribute_category,
196 l_org_id,
197 x_source
198 );
199
200 OPEN c;
201 FETCH c INTO x_rowid;
202 IF (c%NOTFOUND) THEN
203 CLOSE c;
204 RAISE NO_DATA_FOUND;
205 END IF;
206 CLOSE C;
207
208 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.INSERT_ROW (-) SpcAsgnId: '
209 ||x_emp_space_assign_id||', LocId: '||x_location_id
210 ||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
211 ||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
212 ||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
213
214 END Insert_Row;
215
216 -----------------------------------------------------------------------
217 -- PROCDURE : Lock_Row
218 -----------------------------------------------------------------------
219
220 PROCEDURE Lock_Row (
221 x_emp_space_assign_id IN NUMBER,
222 x_attribute1 IN VARCHAR2,
223 x_attribute2 IN VARCHAR2,
224 x_attribute3 IN VARCHAR2,
225 x_attribute4 IN VARCHAR2,
226 x_attribute5 IN VARCHAR2,
227 x_attribute6 IN VARCHAR2,
228 x_attribute7 IN VARCHAR2,
229 x_attribute8 IN VARCHAR2,
230 x_attribute9 IN VARCHAR2,
231 x_attribute10 IN VARCHAR2,
232 x_attribute11 IN VARCHAR2,
233 x_attribute12 IN VARCHAR2,
234 x_attribute13 IN VARCHAR2,
235 x_attribute14 IN VARCHAR2,
236 x_attribute15 IN VARCHAR2,
237 x_location_id IN NUMBER,
238 x_person_id IN NUMBER,
239 x_project_id IN NUMBER,
240 x_task_id IN NUMBER,
241 x_emp_assign_start_date IN DATE,
242 x_emp_assign_end_date IN DATE,
243 x_cost_center_code IN VARCHAR2,
244 x_allocated_area_pct IN NUMBER,
245 x_allocated_area IN NUMBER,
246 x_utilized_area IN NUMBER,
247 x_emp_space_comments IN VARCHAR2,
248 x_attribute_category IN VARCHAR2)
249 IS
250
251 CURSOR c1 IS
252 SELECT *
253 FROM pn_space_assign_emp_all
254 WHERE emp_space_assign_id = x_emp_space_assign_id
255 FOR UPDATE OF emp_space_assign_id NOWAIT;
256
257 BEGIN
258
259 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.LOCK_ROW (+) SpcAsgnId: '
260 ||x_emp_space_assign_id);
261
262 OPEN c1;
263 FETCH c1 INTO tlempinfo;
264 IF (c1%NOTFOUND) THEN
265 CLOSE c1;
266 RETURN;
267 END IF;
268 CLOSE c1;
269
270 IF NOT (tlempinfo.EMP_SPACE_ASSIGN_ID = X_EMP_SPACE_ASSIGN_ID) THEN
271 pn_var_rent_pkg.lock_row_exception('EMP_SPACE_ASSIGN_ID',tlempinfo.EMP_SPACE_ASSIGN_ID);
272 END IF;
273
274 IF NOT ((tlempinfo.location_id = x_location_id)
275 OR ((tlempinfo.location_id IS NULL) AND (x_location_id IS NULL))) THEN
276 pn_var_rent_pkg.lock_row_exception('LOCATION_ID',tlempinfo.location_id);
277 END IF;
278
279 IF NOT ((TLEMPINFO.person_id = x_person_id)
280 OR ((tlempinfo.person_id IS NULL) AND (x_person_id IS NULL))) THEN
281 pn_var_rent_pkg.lock_row_exception('PERSON_ID',tlempinfo.person_id);
282 END IF;
283
284 IF NOT ((tlempinfo.project_id = x_project_id)
285 OR ((tlempinfo.project_id IS NULL) AND (x_project_id IS NULL))) THEN
286 pn_var_rent_pkg.lock_row_exception('PROJECT_ID',tlempinfo.project_id);
287 END IF;
288
289 IF NOT ((tlempinfo.task_id = x_task_id)
290 OR ((tlempinfo.task_id IS NULL) AND (x_task_id IS NULL))) THEN
291 pn_var_rent_pkg.lock_row_exception('TASK_ID',tlempinfo.task_id);
292 END IF;
293
294 IF NOT ((trunc(tlempinfo.emp_assign_end_date) = TRUNC(x_emp_assign_end_date))
295 OR ((tlempinfo.emp_assign_end_date IS NULL) AND (x_emp_assign_end_date IS NULL))) THEN
296 pn_var_rent_pkg.lock_row_exception('EMP_ASSIGN_END_DATE',tlempinfo.emp_assign_end_date);
297 END IF;
298
299 IF NOT ((tlempinfo.cost_center_code = x_cost_center_code)
300 OR ((tlempinfo.cost_center_code IS NULL) AND (x_cost_center_code IS NULL))) THEN
301 pn_var_rent_pkg.lock_row_exception('COST_CENTER_CODE',tlempinfo.cost_center_code);
302 END IF;
303
304 IF NOT ((tlempinfo.allocated_area_pct = x_allocated_area_pct)
305 OR ((tlempinfo.allocated_area_pct IS NULL) AND (x_allocated_area_pct IS NULL))) THEN
306 pn_var_rent_pkg.lock_row_exception('ALLOCATED_AREA_PCT',tlempinfo.allocated_area_pct);
307 END IF;
308
309 IF NOT ((tlempinfo.allocated_area = x_allocated_area)
310 OR ((tlempinfo.allocated_area IS NULL) AND (x_allocated_area IS NULL))) THEN
314 IF NOT ((tlempinfo.utilized_area = x_utilized_area)
311 pn_var_rent_pkg.lock_row_exception('ALLOCATED_AREA',tlempinfo.allocated_area);
312 END IF;
313
315 OR ((tlempinfo.utilized_area IS NULL) AND (x_utilized_area IS NULL))) THEN
316 pn_var_rent_pkg.lock_row_exception('UTILIZED_AREA',tlempinfo.utilized_area);
317 END IF;
318
319 IF NOT ((tlempinfo.emp_space_comments = x_emp_space_comments)
320 OR ((tlempinfo.emp_space_comments IS NULL) AND (x_emp_space_comments IS NULL))) THEN
321 pn_var_rent_pkg.lock_row_exception('EMP_SPACE_COMMENTS',tlempinfo.emp_space_comments);
322 END IF;
323
324 IF NOT ((tlempinfo.attribute_category = x_attribute_category)
325 OR ((tlempinfo.attribute_category IS NULL) AND (x_attribute_category IS NULL))) THEN
326 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',tlempinfo.attribute_category);
327 END IF;
328
329 IF NOT ((tlempinfo.attribute1 = x_attribute1)
330 OR ((tlempinfo.attribute1 IS NULL) AND (x_attribute1 IS NULL))) THEN
331 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1',tlempinfo.attribute1);
332 END IF;
333
334 IF NOT ((tlempinfo.attribute2 = x_attribute2)
335 OR ((tlempinfo.attribute2 IS NULL) AND (x_attribute2 IS NULL))) THEN
336 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2',tlempinfo.attribute2);
337 END IF;
338
339 IF NOT ((tlempinfo.attribute3 = x_attribute3)
340 OR ((tlempinfo.attribute3 IS NULL) AND (x_attribute3 IS NULL))) THEN
341 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3',tlempinfo.attribute3);
342 END IF;
343
344 IF NOT ((tlempinfo.attribute4 = x_attribute4)
345 OR ((tlempinfo.attribute4 IS NULL) AND (x_attribute4 IS NULL))) THEN
346 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4',tlempinfo.attribute4);
347 END IF;
348
349 IF NOT ((tlempinfo.attribute5 = x_attribute5)
350 OR ((tlempinfo.attribute5 IS NULL) AND (x_attribute5 IS NULL))) THEN
351 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5',tlempinfo.attribute5);
352 END IF;
353
354 IF NOT ((tlempinfo.attribute6 = x_attribute6)
355 OR ((tlempinfo.attribute6 IS NULL) AND (x_attribute6 IS NULL))) THEN
356 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6',tlempinfo.attribute6);
357 END IF;
358
359 IF NOT ((tlempinfo.attribute7 = x_attribute7)
360 OR ((tlempinfo.attribute7 IS NULL) AND (x_attribute7 IS NULL))) THEN
361 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7',tlempinfo.attribute7);
362 END IF;
363
364 IF NOT ((tlempinfo.attribute8 = x_attribute8)
365 OR ((tlempinfo.attribute8 IS NULL) AND (x_attribute8 IS NULL))) THEN
366 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8',tlempinfo.attribute8);
367 END IF;
368
369 IF NOT ((tlempinfo.attribute9 = x_attribute9)
370 OR ((tlempinfo.attribute9 IS NULL) AND (x_attribute9 IS NULL))) THEN
371 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9',tlempinfo.attribute9);
372 END IF;
373
374 IF NOT ((tlempinfo.attribute10 = x_attribute10)
375 OR ((tlempinfo.attribute10 IS NULL) AND (x_attribute10 IS NULL))) THEN
376 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10',tlempinfo.attribute10);
377 END IF;
378
379 IF NOT ((tlempinfo.attribute11 = x_attribute11)
380 OR ((tlempinfo.attribute11 IS NULL) AND (x_attribute11 IS NULL))) THEN
381 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11',tlempinfo.attribute11);
382 END IF;
383
384 IF NOT ((tlempinfo.attribute12 = x_attribute12)
385 OR ((tlempinfo.attribute12 IS NULL) AND (x_attribute12 IS NULL))) THEN
386 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12',tlempinfo.attribute12);
387 END IF;
388
389 IF NOT ((tlempinfo.attribute13 = x_attribute13)
390 OR ((tlempinfo.attribute13 IS NULL) AND (x_attribute13 IS NULL))) THEN
391 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13',tlempinfo.attribute13);
392 END IF;
393
394 IF NOT ((tlempinfo.attribute14 = x_attribute14)
395 OR ((tlempinfo.attribute14 IS NULL) AND (x_attribute14 IS NULL))) THEN
396 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14',tlempinfo.attribute14);
397 END IF;
398
399 IF NOT ((tlempinfo.attribute15 = x_attribute15)
400 OR ((tlempinfo.attribute15 IS NULL) AND (x_attribute15 IS NULL))) THEN
401 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15',tlempinfo.attribute15);
402 END IF;
403
404 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.LOCK_ROW (-) SpcAsgnId: '
405 ||x_emp_space_assign_id);
406
407 END Lock_Row;
408
409 -------------------------------------------------------------------------------
410 -- PROCDURE : Update_Row
411 -- DESCRIPTION : updates a row in pn_space_assign_emp_all
412 -- SCOPE : PUBLIC
413 -- INVOKED FROM :
414 -- RETURNS : NONE
415 -- HISTORY :
416 -- 14-DEC-04 STripath o Modified for Portfolio Status Enh BUG# 4030816.
417 -- Added code to check loc is contigious assignable
418 -- betn assign start and end dates.
419 -- 01-JUL-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp
420 -- with _ALL table.
421 -- 08-SEP-05 Hareesha o Modified insert statement to include org_id.
422 -------------------------------------------------------------------------------
423 PROCEDURE Update_Row (
424 x_emp_space_assign_id IN NUMBER,
425 x_attribute1 IN VARCHAR2,
429 x_attribute5 IN VARCHAR2,
426 x_attribute2 IN VARCHAR2,
427 x_attribute3 IN VARCHAR2,
428 x_attribute4 IN VARCHAR2,
430 x_attribute6 IN VARCHAR2,
431 x_attribute7 IN VARCHAR2,
432 x_attribute8 IN VARCHAR2,
433 x_attribute9 IN VARCHAR2,
434 x_attribute10 IN VARCHAR2,
435 x_attribute11 IN VARCHAR2,
436 x_attribute12 IN VARCHAR2,
437 x_attribute13 IN VARCHAR2,
438 x_attribute14 IN VARCHAR2,
439 x_attribute15 IN VARCHAR2,
440 x_location_id IN NUMBER,
441 x_person_id IN NUMBER,
442 x_project_id IN NUMBER,
443 x_task_id IN NUMBER,
444 x_emp_assign_start_date IN DATE,
445 x_emp_assign_end_date IN DATE,
446 x_cost_center_code IN VARCHAR2,
447 x_allocated_area_pct IN NUMBER,
448 x_allocated_area IN NUMBER,
449 x_utilized_area IN NUMBER,
450 x_emp_space_comments IN VARCHAR2,
451 x_attribute_category IN VARCHAR2,
452 x_last_update_date IN DATE,
453 x_last_updated_by IN NUMBER,
454 x_last_update_login IN NUMBER,
455 x_update_correct_option IN VARCHAR2,
456 x_changed_start_date OUT NOCOPY DATE,
457 x_source IN VARCHAR2
458 )
459 IS
460
461 l_emp_space_assign_id NUMBER;
462 l_err_msg VARCHAR2(30);
463 l_asgn_mode VARCHAR2(30);
464
465 BEGIN
466
467 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW (+) SpcAsgnId: '
468 ||x_emp_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
469 ||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
470 ||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
471 ||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
472
473 -- Check if location is contigious Employee/CC Assignable betn assign start and end dates.
474 IF (x_location_id <> tlempinfo.location_id) OR
475 (x_emp_assign_start_date <> tlempinfo.emp_assign_start_date) OR
476 (NVL(x_emp_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')) <>
477 NVL(tlempinfo.emp_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')))
478 THEN
479 IF x_person_id IS NOT NULL THEN
480 l_asgn_mode := 'EMP';
481 ELSE
482 l_asgn_mode := 'CC';
483 END IF;
484 pnt_locations_pkg.Check_Location_Gaps(
485 p_loc_id => x_location_id
486 ,p_str_dt => x_emp_assign_start_date
487 ,p_end_dt => NVL(x_emp_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'))
488 ,p_asgn_mode => l_asgn_mode
489 ,p_err_msg => l_err_msg
490 );
491
492 IF l_err_msg IS NOT NULL THEN
493 fnd_message.set_name('PN', 'PN_INVALID_SPACE_ASSGN_DATE');
494 app_exception.raise_exception;
495 END IF;
496 END IF;
497
498 IF ((x_person_id IS NULL AND
499 x_cost_center_code <> tlempinfo.cost_center_code) OR
500 (x_person_id IS NOT NULL AND
501 x_person_id <> tlempinfo.person_id)) THEN
502
503 pn_space_assign_emp_pkg.check_dupemp_assign(p_person_id => x_person_id,
504 p_cost_cntr_code => x_cost_center_code,
505 p_loc_id => x_location_id,
506 p_assgn_str_dt => x_emp_assign_start_date);
507 END IF;
508
509 IF x_update_correct_option = 'UPDATE' THEN
510
511
512 SELECT pn_space_assign_emp_s.NEXTVAL
513 INTO l_emp_space_assign_id
514 FROM DUAL;
515
516 INSERT INTO pn_space_assign_emp_all
517 (
518 emp_space_assign_id,
519 location_id,
520 person_id,
521 project_id,
522 task_id,
523 emp_assign_start_date,
524 emp_assign_end_date,
525 cost_center_code,
526 allocated_area_pct,
527 allocated_area,
528 utilized_area,
529 emp_space_comments,
530 last_update_date,
531 last_updated_by,
532 creation_date,
533 created_by,
534 last_update_login,
535 attribute_category,
536 attribute1,
537 attribute2,
538 attribute3,
539 attribute4,
540 attribute5,
541 attribute6,
542 attribute7,
546 attribute11,
543 attribute8,
544 attribute9,
545 attribute10,
547 attribute12,
548 attribute13,
549 attribute14,
550 attribute15,
551 source,
552 org_id
553 )
554 VALUES
555 (
556 l_emp_space_assign_id,
557 tlempinfo.location_id,
558 tlempinfo.person_id,
559 tlempinfo.project_id,
560 tlempinfo.task_id,
561 tlempinfo.emp_assign_start_date,
562 (x_emp_assign_start_date - 1),
563 tlempinfo.cost_center_code,
564 tlempinfo.allocated_area_pct,
565 tlempinfo.allocated_area,
566 tlempinfo.utilized_area,
567 tlempinfo.emp_space_comments,
568 tlempinfo.last_update_date,
569 tlempinfo.last_updated_by,
570 tlempinfo.creation_date,
571 tlempinfo.created_by,
572 tlempinfo.last_update_login,
573 tlempinfo.attribute_category,
574 tlempinfo.attribute1,
575 tlempinfo.attribute2,
576 tlempinfo.attribute3,
577 tlempinfo.attribute4,
578 tlempinfo.attribute5,
579 tlempinfo.attribute6,
580 tlempinfo.attribute7,
581 tlempinfo.attribute8,
582 tlempinfo.attribute9,
583 tlempinfo.attribute10,
584 tlempinfo.attribute11,
585 tlempinfo.attribute12,
586 tlempinfo.attribute13,
587 tlempinfo.attribute14,
588 tlempinfo.attribute15,
589 tlempinfo.source,
590 tlempinfo.org_id
591 );
592
593 END IF;
594
595 UPDATE pn_space_assign_emp_all
596 SET attribute1 = x_attribute1,
597 attribute2 = x_attribute2,
598 attribute3 = x_attribute3,
599 attribute4 = x_attribute4,
600 attribute5 = x_attribute5,
601 attribute6 = x_attribute6,
602 attribute7 = x_attribute7,
603 attribute8 = x_attribute8,
604 attribute9 = x_attribute9,
605 attribute10 = x_attribute10,
606 attribute11 = x_attribute11,
607 attribute12 = x_attribute12,
608 attribute13 = x_attribute13,
609 attribute14 = x_attribute14,
610 attribute15 = x_attribute15,
611 location_id = x_location_id,
612 person_id = x_person_id,
613 project_id = x_project_id,
614 task_id = x_task_id,
615 emp_assign_start_date = x_emp_assign_start_date,
616 emp_assign_end_date = x_emp_assign_end_date,
617 cost_center_code = x_cost_center_code,
618 allocated_area_pct = x_allocated_area_pct,
619 allocated_area = x_allocated_area,
620 utilized_area = x_utilized_area,
624 last_update_date = x_last_update_date,
621 emp_space_comments = x_emp_space_comments,
622 attribute_category = x_attribute_category,
623 emp_space_assign_id = x_emp_space_assign_id,
625 last_updated_by = x_last_updated_by,
626 last_update_login = x_last_update_login,
627 source = x_source
628 WHERE emp_space_assign_id = x_emp_space_assign_id;
629
630 x_changed_start_date := x_emp_assign_start_date ;
631
632 IF (SQL%NOTFOUND) THEN
633 RAISE NO_DATA_FOUND;
634 END IF;
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 app_exception.raise_exception;
639
640 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW (-) SpcAsgnId: '
641 ||x_emp_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
642 ||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
643 ||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
644 ||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
645
646 END Update_Row;
647
648 -------------------------------------------------------------------------------
649 -- PROCDURE : Delete_Row
650 -- DESCRIPTION : deletes a row from pn_space_assign_emp_all
651 -- SCOPE : PUBLIC
652 -- INVOKED FROM :
653 -- RETURNS : NONE
654 -- HISTORY :
655 -- 01-JUL-05 hareesha o Bug 4284035 - Replaced pn_space_assign_emp
656 -- with _ALL table.
657 -- 25-Aug-05 hareesha o Bug 4551258 - Addeed cursor emp_space_assign_id_exists
658 -- Only if a record exists with the given
659 -- emp_space_assign_id does the delete is called.
660 -- 30-AUG-05 Hareesha o Bug 4551258 - Removed the cursor emp_space_assign_id_exists
661 -- which was added previously inorder to fix the
662 -- no_data_found error coming up.
663 -------------------------------------------------------------------------------
664 PROCEDURE delete_row(x_emp_space_assign_id IN NUMBER) IS
665
666 BEGIN
667
668 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.DELETE_ROW (+) SpcAsgnId: '
669 ||x_emp_space_assign_id);
670
671 DELETE FROM pn_space_assign_emp_all
672 WHERE emp_space_assign_id = x_emp_space_assign_id;
673
674
675 IF (SQL%NOTFOUND) THEN
676 RAISE NO_DATA_FOUND;
677 END IF;
678
679 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.DELETE_ROW (-) SpcAsgnId: '
680 ||x_emp_space_assign_id);
681
682 END delete_row;
683
684 -------------------------------------------------------------------------------
685 -- PROCEDURE : check_office_assign_gaps
686 -- PURPOSE : This procedure is being called from INSERT_ROW, UPDATE_ROW
687 -- of employee_fdr_blk, customer_fdr_blk of PNTSPACE form.
688 -- It checks for the gaps between office definition and stops
689 -- the user from assinging an office during that gap interval.
690 -- IN PARAM : Location Id, Actice_start_date, Active_end_date.
691 -- History :
692 -- 27-DEC-02 Mrinal Misra o Mrinal Misra
693 -- 10-JAN-03 Mrinal Misra o Modified to run FOR LOOP one lesser
694 -- count by 1..loctn_tab.count-1.
695 -- 01-JUL-05 hrodda o Bug 4284035 - Replaced pn_locations
696 -- with _ALL table.
697 -------------------------------------------------------------------------------
698 PROCEDURE check_office_assign_gaps(p_loc_id IN NUMBER,
699 p_str_dt IN DATE,
700 p_end_dt IN DATE) IS
701
702 TYPE loctn_rec IS RECORD(
703 location_id pn_locations.location_id%TYPE,
704 active_start_date DATE,
705 active_end_date DATE);
706
707 TYPE loc_type IS
708 TABLE OF loctn_rec
709 INDEX BY BINARY_INTEGER;
710
711 loctn_tab loc_type;
712 l_rec_num NUMBER;
713 l_diff NUMBER;
714 l_date DATE;
715 l_err_flag VARCHAR2(1);
716
717 CURSOR get_loctn_dates IS
718 SELECT location_id,
719 active_start_date,
720 active_end_date
721 FROM pn_locations_all
722 WHERE active_end_date >= p_str_dt
723 AND active_start_date <= p_end_dt
724 AND location_id = p_loc_id
725 ORDER BY active_start_date;
726
727 CURSOR check_loctn_gap(l_date IN DATE) IS
728 SELECT 'Y'
729 FROM DUAL
730 WHERE NOT EXISTS (SELECT NULL
731 FROM pn_locations_all
732 WHERE l_date BETWEEN active_start_date AND active_end_date
733 AND location_id = p_loc_id);
734
735 BEGIN
736
737 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.check_office_assign_gaps(+)');
738
739 loctn_tab.delete;
740 l_rec_num := 0;
741 l_err_flag := 'N';
742
743 IF p_str_dt IS NOT NULL THEN
744 OPEN check_loctn_gap(p_str_dt);
745 FETCH check_loctn_gap INTO l_err_flag;
746 CLOSE check_loctn_gap;
747
748 IF l_err_flag = 'Y' THEN
749 fnd_message.set_name('PN', 'PN_ASGN_LOCN_NOT_EFFC_MSG');
750 app_exception.raise_exception;
751 END IF;
752 END IF;
753
754 IF p_end_dt IS NOT NULL THEN
755 OPEN check_loctn_gap(p_end_dt);
756 FETCH check_loctn_gap INTO l_err_flag;
757 CLOSE check_loctn_gap;
758
759 IF l_err_flag = 'Y' THEN
760 fnd_message.set_name('PN', 'PN_ASGN_LOCN_NOT_EFFC_MSG');
761 app_exception.raise_exception;
762 END IF;
763 END IF;
764
765 IF p_end_dt IS NOT NULL AND p_str_dt IS NOT NULL THEN
766 FOR loc_rec IN get_loctn_dates LOOP
767 l_rec_num := NVL(loctn_tab.COUNT,0) + 1;
768 loctn_tab(l_rec_num) := loc_rec;
769 END LOOP;
770
771 IF NVL(l_rec_num,0) > 1 THEN
772 FOR i in 1..loctn_tab.COUNT-1 LOOP
773
774 SELECT loctn_tab(i+1).active_start_date -
775 loctn_tab(i).active_end_date
776 INTO l_diff
777 FROM DUAL;
778
779 IF l_diff > 1 THEN
780 l_err_flag := 'Y';
781 EXIT;
782 END IF;
783 END LOOP;
784
785 ELSIF NVL(l_rec_num,0) = 0 THEN
786 l_err_flag := 'Y';
787 END IF;
788
789 IF l_err_flag = 'Y' THEN
790 fnd_message.set_name('PN', 'PN_ASGN_LOCN_NOT_EFFC_MSG');
791 app_exception.raise_exception;
792 END IF;
793 END IF;
794
795 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.check_office_assign_gaps(-)');
796
797 END check_office_assign_gaps;
798
799 -------------------------------------------------------------------------------
800 -- PROCEDURE : check_dupemp_assign
801 -- PURPOSE : The procedure checks to see if there exists another assignment
802 -- record for person id/cost center code, for the same date range.
803 -- If there exists one then it stops user from doing the assignment.
804 -- IN PARAM : Person Id, Cost Center Code, Location Id,
805 -- Employee Assignment Start Date.
806 -- History :
807 -- 16-JAN-03 Mrinal o Created.
808 -- 22-JAN-03 Mrinal o Modified WHERE clause of check_emp_assignment,
809 -- check_ccd_assignment cursor to check for
810 -- exisitng assignment.
811 -- 22-FEB-05 ftanudja o Removed check for duplicate cost center code.#4116762
812 -------------------------------------------------------------------------------
813 PROCEDURE check_dupemp_assign(p_person_id IN NUMBER,
814 p_cost_cntr_code IN VARCHAR2,
815 p_loc_id IN NUMBER,
816 p_assgn_str_dt IN DATE) IS
817
818 l_err_flag VARCHAR2(1) := 'N';
819
820 CURSOR check_emp_assignment IS
821 SELECT 'Y'
822 FROM DUAL
823 WHERE EXISTS (SELECT NULL
824 FROM pn_space_assign_emp_all
825 WHERE person_id = p_person_id
826 AND location_id = p_loc_id
827 AND cost_center_code = p_cost_cntr_code
828 AND emp_assign_start_date <= p_assgn_str_dt
829 AND NVL(emp_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
830 >= p_assgn_str_dt);
831
832 CURSOR check_ccd_assignment IS
833 SELECT 'Y'
834 FROM DUAL
835 WHERE EXISTS (SELECT NULL
836 FROM pn_space_assign_emp_all
837 WHERE cost_center_code = p_cost_cntr_code
838 AND location_id = p_loc_id
839 AND emp_assign_start_date <= p_assgn_str_dt
840 AND person_id is null
841 AND NVL(emp_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
842 >= p_assgn_str_dt);
843 BEGIN
847 FETCH check_emp_assignment INTO l_err_flag;
844 l_err_flag := 'N';
845
846 OPEN check_emp_assignment;
848 CLOSE check_emp_assignment;
849
850 IF NVL(l_err_flag,'N') = 'Y' THEN
851 fnd_message.set_name('PN', 'PN_SPASGN_EMPLOYEE_OVRLAP_MSG');
852 app_exception.raise_exception;
853 END IF;
854
855 END check_dupemp_assign;
856
857 -------------------------------------------------------------------------------
858 -- PROCDURE : get_Least_st_date_assignment
859 -- PURPOSE : Returns the emp_space_assign_id having the least_st_date to
860 -- diff between the original-assignment and
861 -- system-generated assignment
862 -- HISTORY :
863 -- 20-JUL-05 hareesha o created bug #4116645
864 -------------------------------------------------------------------------------
865 FUNCTION get_least_st_date_assignment
866 (p_loc_id IN NUMBER,
867 p_emp_id IN NUMBER,
868 p_cc_code IN VARCHAR2)
869 RETURN NUMBER IS
870
871 l_emp_space_assign_id NUMBER := -1;
872
873 CURSOR get_person_assign_id( p_loc IN NUMBER
874 ,p_person IN NUMBER) IS
875 SELECT emp_space_assign_id
876 FROM pn_space_assign_emp_all
877 WHERE location_id = p_loc
878 AND person_id = p_person
879 AND emp_assign_start_date =
880 (SELECT MIN(emp_assign_start_date)
881 FROM pn_space_assign_emp_all
882 WHERE location_id = p_loc
883 AND person_id = p_person);
884
885 CURSOR get_cc_assign_id( p_loc IN NUMBER
886 ,p_cc IN VARCHAR2) IS
887 SELECT emp_space_assign_id
888 FROM pn_space_assign_emp_all
889 WHERE location_id = p_loc
890 AND cost_center_code = p_cc
891 AND emp_assign_start_date =
892 (SELECT MIN(emp_assign_start_date)
893 FROM pn_space_assign_emp_all
894 WHERE location_id = p_loc
895 AND cost_center_code = p_cc);
896
897 BEGIN
898 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.GET_LEAST_ST_DATE_ASSIGNMENT(+)');
899
900 IF p_emp_id IS NOT NULL THEN
901 FOR emp IN get_person_assign_id(p_loc_id, p_emp_id) LOOP
902 l_emp_space_assign_id := emp.emp_space_assign_id;
903 END LOOP;
904 ELSIF p_cc_code IS NOT NULL THEN
905 FOR emp IN get_cc_assign_id(p_loc_id, p_cc_code) LOOP
906 l_emp_space_assign_id := emp.emp_space_assign_id;
907 END LOOP;
908 END IF;
909
910 pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.GET_LEAST_ST_DATE_ASSIGNMENT(-)');
911
912 RETURN l_emp_space_assign_id;
913
914 END get_Least_st_date_assignment;
915
916 END pn_space_assign_emp_pkg;
[Home] [Help]