1 Package body ben_asg_budget_values as
2 /* $Header: bebudapi.pkb 120.0 2005/05/28 00:53:24 appldev noship $ */
3
4 --
5
6 Procedure create_budget_values
7 (p_assignment_id in number,
8 p_effective_date in date,
9 p_unit in varchar2,
10 p_business_group_id in number,
11 p_value in number,
12 p_datetrack_mode in varchar2
13 ) is
14 cursor c1 is
15 select
16 assignment_budget_value_id,
17 effective_start_date,
18 effective_end_date,
19 business_group_id,
20 assignment_id,
21 unit,
22 value,
23 rowid
24 from per_assignment_budget_values_f
25 where
26 assignment_id = p_assignment_id and
27 p_effective_date between effective_start_date and effective_end_date and
28 business_group_id = p_business_group_id and
29 unit = p_unit;
30 l_rec c1%ROWTYPE;
31
32
33 BEGIN
34 open c1;
35 fetch c1 into l_rec;
36
37 IF c1%notfound then
38
39 insert_budget_values
40 (p_assignment_id=>p_assignment_id,
41 p_effective_date=>p_effective_date,
42 p_unit=>p_unit,
43 p_business_group_id=>p_business_group_id,
44 p_value=>p_value,
45 p_rowid=>null,
46 p_assignment_budget_value_id=>null);
47
48 ELSE
49 -- check if the value in database is different than the value passed in
50 if p_value <> l_rec.value then
51 IF p_datetrack_mode='CORRECTION' OR p_effective_date = l_rec.effective_start_date THEN
52 update_dml
53 (P_ASSIGNMENT_BUDGET_VALUE_ID=>l_rec.assignment_budget_value_id,
54 P_EFFECTIVE_END_DATE=>l_rec.effective_end_date,
55 P_BUSINESS_GROUP_ID=>p_business_group_id,
56 P_ASSIGNMENT_ID=>p_assignment_id,
57 P_VALUE=>p_value
58 );
59 ELSIF p_datetrack_mode='UPDATE' THEN
60 -- First end date the current Row
61 update_dml
62 (P_ASSIGNMENT_BUDGET_VALUE_ID=>l_rec.assignment_budget_value_id,
63 P_EFFECTIVE_END_DATE=>(p_effective_date-1),
64 P_BUSINESS_GROUP_ID=>p_business_group_id,
65 P_ASSIGNMENT_ID=>p_assignment_id,
66 P_VALUE=>l_rec.value
67 );
68
69 -- Create another row with
70 insert_budget_values
71 (p_assignment_id=>p_assignment_id,
72 p_effective_date=>p_effective_date,
73 p_unit=>p_unit,
74 p_business_group_id=>p_business_group_id,
75 p_value=>p_value,
76 p_rowid=>l_rec.rowid,
77 p_assignment_budget_value_id=>l_rec.assignment_budget_value_id);
78 END IF;
79
80 end if;
81 END IF;
82 close c1;
83 commit;
84 end create_budget_values;
85
86 ---
87
88 procedure update_dml(P_ASSIGNMENT_BUDGET_VALUE_ID IN NUMBER,
89 P_EFFECTIVE_END_DATE IN DATE,
90 P_BUSINESS_GROUP_ID IN NUMBER,
91 P_ASSIGNMENT_ID IN NUMBER,
92 P_VALUE IN NUMBER
93 ) is
94 BEGIN
95 update per_assignment_budget_values_f
96 set
97 effective_end_date = p_effective_end_date,
98 value = p_value
99 where assignment_budget_value_id = p_assignment_budget_value_id
100 and business_group_id = p_business_group_id
101 and assignment_id = p_assignment_id and
102 p_effective_end_date between effective_start_date and effective_end_date;
103 end update_dml;
104 --
105 --
106
107 PROCEDURE insert_budget_values
108 (p_assignment_id in number,
109 p_effective_date in date,
110 p_unit in varchar2,
111 p_business_group_id in number,
112 p_value in number,
113 p_rowid in varchar2,
114 p_assignment_budget_value_id in number) is
115
116 cursor c2 is
117 select effective_start_date, rowid from per_assignment_budget_values_f
118 where
119 assignment_id = p_assignment_id and
120 business_group_id = p_business_group_id
121 order by effective_start_date;
122
123 l2_rec c2%ROWTYPE;
124 l_parent_effective_end_date date;
125
126 l_assignment_budget_value_id per_assignment_budget_values_f.ASSIGNMENT_BUDGET_VALUE_ID%TYPE;
127 --
128 begin
129
130 -- Date track coding,get the maximum effective end date from the assignment
131 -- table (parent record) to ensure that budget values can not go pass
132 -- this date.
133 per_abv_pkg.get_parent_max_end_date(p_assignment_id => p_assignment_id
134 ,p_end_date => l_parent_effective_end_date);
135 -- if the session date is greater than the parent assignment end date then raise an error
136 -- as a record can not be inserted after the end of the parent assignment.
137 if p_effective_date > l_parent_effective_end_date then
138 fnd_message.set_name('PER','HR_6645_ASS_COST_DUPL');
139 fnd_message.raise_error;
140 end if;
141
142 -- check if the records exist with an effective start date later than effective date
143
144 open c2;
145 fetch c2 into l2_rec;
146 if p_effective_date < l2_rec.effective_start_date then
147 l_parent_effective_end_date := (l2_rec.effective_start_date-1);
148 --p_rowid := l2_rec.rowid;
149 end if;
150 close c2;
151
152 -- Date track coding, check for an existing record,using the parent end date to determine
153 -- if a record exists.
154 per_abv_pkg.check_for_duplicate_record(p_assignment_id => p_assignment_id
155 ,p_unit => p_unit
156 ,p_start_date => p_effective_date
157 ,p_end_date => l_parent_effective_end_date);
158 per_abv_pkg.pre_commit_checks
159 (p_assignment_id => p_assignment_id
160 ,p_unit => p_unit
161 ,p_rowid => p_rowid
162 ,p_unique_id => l_assignment_budget_value_id);
163
164 if l_assignment_budget_value_id is null then
165 insert_dml
166 (P_ASSIGNMENT_BUDGET_VALUE_ID=>p_assignment_budget_value_id,
167 p_EFFECTIVE_START_DATE=>p_effective_date,
168 P_EFFECTIVE_END_DATE=>l_parent_effective_end_date,
169 P_BUSINESS_GROUP_ID=>p_business_group_id,
170 P_ASSIGNMENT_ID=>p_assignment_id,
171 P_UNIT=>p_unit,
172 P_VALUE=>p_value
173 );
174 else
175 insert_dml
176 (P_ASSIGNMENT_BUDGET_VALUE_ID=>l_assignment_budget_value_id,
177 p_EFFECTIVE_START_DATE=>p_effective_date,
178 P_EFFECTIVE_END_DATE=>l_parent_effective_end_date,
179 P_BUSINESS_GROUP_ID=>p_business_group_id,
180 P_ASSIGNMENT_ID=>p_assignment_id,
181 P_UNIT=>p_unit,
182 P_VALUE=>p_value
183 );
184 end if;
185 end insert_budget_values;
186 procedure insert_dml
187 (P_ASSIGNMENT_BUDGET_VALUE_ID IN NUMBER,
188 p_EFFECTIVE_START_DATE IN DATE,
189 P_EFFECTIVE_END_DATE IN DATE,
190 P_BUSINESS_GROUP_ID IN NUMBER,
191 P_ASSIGNMENT_ID IN NUMBER,
192 P_UNIT IN VARCHAR2,
193 P_VALUE IN NUMBER
194 ) IS
195 BEGIN
196 insert into per_assignment_budget_values_f
197 (ASSIGNMENT_BUDGET_VALUE_ID,
198 EFFECTIVE_START_DATE,
199 EFFECTIVE_END_DATE,
200 BUSINESS_GROUP_ID,
201 ASSIGNMENT_ID,
202 UNIT,
203 VALUE
204 )
205 values
206 (P_ASSIGNMENT_BUDGET_VALUE_ID,
207 p_EFFECTIVE_START_DATE,
208 P_EFFECTIVE_END_DATE,
209 P_BUSINESS_GROUP_ID,
210 P_ASSIGNMENT_ID,
211 P_UNIT,
212 P_VALUE
213 );
214 end insert_dml;
215 end ben_asg_budget_values;
[Home] [Help]