1 PACKAGE BODY PER_ABV_PKG as
2 /* $Header: peabv01t.pkb 115.2 99/07/17 18:23:43 porting ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8
9 --
10 -- 110.1 31-MAR-1998 SASmith Change to add two new procedures related to datetracking
11 -- of table per_assignment_budget_values.
12 -- 1. get_parent_max_end_date. This procedure will return the
13 -- maximum end date for the assignment id. This is required to
14 -- be used to set the end date for the child record(s)
15 -- 2. check_for_duplicate_record. If the current record has effective dates
16 -- which 'fall' within that of an existing record then signal error as this
17 -- is a duplicate record.
18 --
19 --
20 -- ======================================================================================================
21
22
23
24 procedure check_unique_row(p_assignment_id number
25 ,p_unit varchar2
26 ,p_rowid varchar2);
27 --
28 function get_unique_id return number;
29 --
30 procedure check_unique_row(p_assignment_id number
31 ,p_unit varchar2
32 ,p_rowid varchar2) is
33 cursor c is
34 select 'x'
35 from per_assignment_budget_values
36 where assignment_id = p_assignment_id
37 and unit = p_unit
38 AND ((p_rowid IS NULL)
39 OR (p_rowid is not null and
40 rowid <> chartorowid(p_rowid))
41 );
42 l_exists varchar2(1);
43 begin
44 open c;
45 fetch c into l_exists;
46 if c%found then
47 close c;
48 hr_utility.set_message(801,'HR_6433_EMP_ASS_BUDGET');
49 hr_utility.raise_error;
50 end if;
51 close c;
52 end check_unique_row;
53 --
54 --
55 function get_unique_id return number is
56 cursor c is
57 select per_assignment_budget_values_s.nextval
58 from sys.dual;
59 --
60 l_id number;
61 --
62 begin
63 open c;
64 fetch c into l_id;
65 close c;
66 return(l_id);
67 end get_unique_id;
68 --
69 --
70 procedure pre_commit_checks(p_assignment_id number
71 ,p_unit varchar2
72 ,p_rowid varchar2
73 ,p_unique_id IN OUT number) is
74 begin
75 check_unique_row(p_assignment_id
76 ,p_unit
77 ,p_rowid);
78 --
79 if p_rowid is null then
80 p_unique_id := get_unique_id;
81 end if;
82 end pre_commit_checks;
83 --
84 --
85 procedure populate_fields(p_unit varchar2
86 ,p_unit_meaning IN OUT varchar2) is
87 cursor c is
88 select meaning
89 from hr_lookups
90 where lookup_type = 'BUDGET_MEASUREMENT_TYPE'
91 and lookup_code = p_unit;
92 begin
93 open c;
94 fetch c into p_unit_meaning;
95 close c;
96 end populate_fields;
97 --
98 --
99
100 --------------------------------------------------------------------------------------------
101 --------------------------------------------------------------------------------------------
102 -- REQUIRED EXTRA VALIDATION FOR DATE TRACK CODE
103 -- retireve the maximum effective end date from the assignment to be used to
104 -- set the effective end date for the budget values record being created.
105 -- This is because the child record (budget values) cannot have an end date
106 -- greater than it's parent (assignment).
107 --
108 -- SASmith 31-MAR-1998
109 --------------------------------------------------------------------------------------------
110
111 procedure get_parent_max_end_date(p_assignment_id IN number
112 ,p_end_date IN OUT date) is
113 cursor c_end is
114 select MAX(effective_end_date)
115 from per_all_assignments_f
116 where assignment_id = p_assignment_id;
117
118 begin
119 open c_end;
120 fetch c_end into p_end_date;
121 close c_end;
122
123 end get_parent_max_end_date;
124
125
126
127
128
129 --------------------------------------------------------------------------------------------
130 --------------------------------------------------------------------------------------------
131 -- REQUIRED EXTRA VALIDATION FOR DATE TRACK CODE
132 -- This procedure tests for an existence of a budget value with the same id
133 -- which overlaps on any day within the lifetime of the existing row.
134 -- If this occurs then the record can not be inserted/updated
135 --
136 --
137 -- SASmith 31-Mar-98
138 --
139
140 --------------------------------------------------------------------------------------------
141 --------------------------------------------------------------------------------------------
142
143 procedure check_for_duplicate_record(p_assignment_id number
144 ,p_unit varchar2
145 ,p_start_date date
146 ,p_end_date date ) is
147
148 cursor C_EXIST_1 is
149 select null
150 from PER_ASSIGNMENT_BUDGET_VALUES_F
151 where assignment_id = p_assignment_id
152 and unit = p_unit
153 and effective_start_date <= p_end_date
154 and effective_end_date >= p_start_date;
155
156 --
157
158 --
159
160 d_dummy varchar2(1);
161 --
162 BEGIN
163 open C_EXIST_1;
164 fetch C_EXIST_1 into d_dummy;
165 if C_EXIST_1%found then
166 close C_EXIST_1;
167
168 hr_utility.set_message(800,'HR_52404_ASS_BUD_VAL_DUPL');
169 hr_utility.raise_error;
170
171 end if;
172 close C_EXIST_1;
173
174 END check_for_duplicate_record;
175
176 --
177 --
178 END PER_ABV_PKG;
[Home] [Help]