[Home] [Help]
1: package body PER_SPINAL_POINT_STEPS_PKG as
2: /* $Header: pesps01t.pkb 120.0 2005/05/31 21:35:53 appldev noship $ */
3:
4: PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5: X_Step_Id IN OUT NOCOPY NUMBER,
44: X_Information29 VARCHAR2,
45: X_Information30 VARCHAR2,
46: X_Information_category VARCHAR2
47: ) IS
48: CURSOR C IS SELECT rowid FROM per_spinal_point_steps
49: WHERE step_id = X_step_id;
50:
51: CURSOR C2 IS SELECT per_spinal_point_steps_s.nextval FROM sys.dual;
52: BEGIN
47: ) IS
48: CURSOR C IS SELECT rowid FROM per_spinal_point_steps
49: WHERE step_id = X_step_id;
50:
51: CURSOR C2 IS SELECT per_spinal_point_steps_s.nextval FROM sys.dual;
52: BEGIN
53: if (X_step_id is NULL) then
54: OPEN C2;
55: FETCH C2 INTO X_step_id;
54: OPEN C2;
55: FETCH C2 INTO X_step_id;
56: CLOSE C2;
57: end if;
58: INSERT INTO per_spinal_point_steps(
59: step_id,
60: effective_start_date,
61: effective_end_date,
62: business_group_id,
199: X_Information_category VARCHAR2
200: ) IS
201: CURSOR C IS
202: SELECT *
203: FROM per_spinal_point_steps
204: WHERE rowid = chartorowid(X_Rowid)
205: FOR UPDATE of step_id NOWAIT;
206: Recinfo C%ROWTYPE;
207: BEGIN
299: X_Information30 VARCHAR2,
300: X_Information_category VARCHAR2
301: ) IS
302: BEGIN
303: UPDATE per_spinal_point_steps
304: SET
305: step_id = X_Step_Id,
306: effective_start_date = X_Effective_Start_Date,
307: effective_end_date = X_Effective_End_Date,
355:
356:
357: PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
358: BEGIN
359: DELETE FROM per_spinal_point_steps
360: WHERE rowid = chartorowid(X_Rowid);
361:
362: if (SQL%NOTFOUND) then
363: RAISE NO_DATA_FOUND;
387: and p_sess < effective_end_date;
388: --
389: begin
390: --
391: hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_del',1);
392: --
393: open c1;
394: --
395: fetch c1 into l_exists1;
400: END IF;
401: --
402: close c1;
403: --
404: hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_del',2);
405: --
406: open c2;
407: --
408: fetch c2 into l_exists2;
434: and special_ceiling_step_id is not null;
435: --
436: begin
437: --
438: hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_zap',1);
439: --
440: open c3;
441: --
442: fetch c3 into l_exists;
447: END IF;
448: --
449: close c3;
450: --
451: hr_utility.set_location('per_spinal_point_steps_pkg.del_chks_zap',2);
452: --
453: open c4;
454: --
455: fetch c4 into l_exists2;
474: select 'x'
475: from sys.dual
476: where exists
477: (select null
478: from per_spinal_point_steps_f sp
479: -- per_grade_spines_f gs Bug fix:3648542
480: where sp.grade_spine_id = p_gspine_id
481: and sp.spinal_point_id = p_spoint_id
482: and sp.step_id <> p_step_id);
482: and sp.step_id <> p_step_id);
483: --
484: begin
485: --
486: hr_utility.set_location('per_spinal_point_steps_pkg.chk_unq_step_point',1);
487: --
488: open c5;
489: --
490: fetch c5 into l_exists;
508: cursor c6 is
509: select count(*)
510: from per_spinal_points p1,
511: per_spinal_points p2,
512: per_spinal_point_steps_f s2
513: where s2.spinal_point_id = p2.spinal_point_id
514: and p1.sequence >= p2.sequence
515: and p_sess between
516: s2.effective_start_date and s2.effective_end_date
521: l_count NUMBER;
522: --
523: begin
524: --
525: hr_utility.set_location('per_spinal_point_steps_pkg.pop_flds',1);
526: --
527: open c6;
528: --
529: fetch c6 into l_count;
541:
542:
543:
544:
545: end PER_SPINAL_POINT_STEPS_PKG;