1 package body hxc_deposit_process_pkg as
2 /* $Header: hxcdeppr.pkb 120.3 2006/03/02 20:57:00 mbhammar noship $ */
3
4 g_package varchar2(33) := ' hxc_deposit_process_pkg.';
5
6 -- procedure
7 -- execute_deposit_process
8 --
9 -- description
10 -- main wrapper process for depositing time information into the time
11 -- store. accepts a 'timecard' in the form of a pl/sql record structure,
12 -- along with all associated header information, and splits the data into
13 -- suitable components prior to insertion into the following storage tables:
14 --
15 -- HXC_TIME_BUILDING_BLOCKS
16 -- HXC_TIME_ATTRIBUTES
17 -- HXC_TIME_ATTRIBUTE_USAGES
18 --
19 -- parameters
20 -- p_time_building_block_id - time building block id
21 -- p_object_version_number - ovn of time building block
22 -- p_process_name - deposit process name
23 -- p_source_name - time source name
24 -- p_effective_date - effective date of deposit
25 -- p_type - building block type, (R)ange or (D)uration
26 -- p_measure - magnitude of time unit
27 -- p_unit_of_measure - time unit
28 -- p_start_time - time in
29 -- p_stop_time - time out
30 -- p_parent_building_block_id - id of parent building block
31 -- p_parent_building_block_ovn - ovn of parent building block
32 -- p_scope - scope of building block
33 -- p_approval_style_id - approval style id
34 -- p_approval_status - approval status code
35 -- p_resource_id - resource id (fk dependent on p_resource_type)
36 -- p_resource_type - person, machine, room, etc...
37 -- p_comment_text - comment text
38 -- p_application_set_id - Application Set Id
39 -- p_timecard - time attributes in pl/sql table structure
40
41 procedure execute_deposit_process
42 (p_time_building_block_id in out nocopy number
43 ,p_object_version_number in out nocopy number
44 ,p_process_name in varchar2
45 ,p_source_name in varchar2
46 ,p_effective_date in date
47 ,p_type in varchar2
48 ,p_measure in number
49 ,p_unit_of_measure in varchar2
50 ,p_start_time in date
51 ,p_stop_time in date
52 ,p_parent_building_block_id in number
53 ,p_parent_building_block_ovn in number
54 ,p_scope in varchar2
55 ,p_approval_style_id in number
56 ,p_approval_status in varchar2
57 ,p_resource_id in number
58 ,p_resource_type in varchar2
59 ,p_comment_text in varchar2
60 ,p_application_set_id in number default null
61 ,p_timecard in hxc_time_attributes_api.timecard
62 ) is
63
64 l_time_building_block_id number;
65 l_object_version_number number;
66 l_time_attribute_id number;
67 l_tbb_ovn number;
68 l_tat_ovn number;
69 l_time_building_block_id_o number;
70
71 l_process_id hxc_deposit_processes.deposit_process_id%TYPE;
72
73 e_process_not_registered exception;
74 e_ovn_not_latest exception;
75
76 begin
77
78 -- get the deposit process id
79
80 -- check that we are using a valid deposit process
81 l_process_id := deposit_process_registered
82 (p_source_name => p_source_name
83 ,p_process_name => p_process_name
84 );
85 IF ( l_process_id IS NULL )
86 then
87 raise e_process_not_registered;
88 end if;
89
90 if p_object_version_number is null then
91
92 -- store the time_card_building_block_id that was passed in
93 l_time_building_block_id_o := p_time_building_block_id;
94
95 savepoint pre_create;
96
97 -- first, create a building block in HXC_TIME_BUILDING_BLOCKS
98 hxc_building_block_api.create_building_block
99 (p_effective_date => p_effective_date
100 ,p_type => p_type
101 ,p_measure => p_measure
102 ,p_unit_of_measure => p_unit_of_measure
103 ,p_start_time => p_start_time
104 ,p_stop_time => p_stop_time
105 ,p_parent_building_block_id => p_parent_building_block_id
106 ,p_parent_building_block_ovn => p_parent_building_block_ovn
107 ,p_scope => p_scope
108 ,p_approval_style_id => p_approval_style_id
109 ,p_approval_status => p_approval_status
110 ,p_resource_id => p_resource_id
111 ,p_resource_type => p_resource_type
112 ,p_comment_text => p_comment_text
113 ,p_application_set_id => p_application_set_id
114 ,p_translation_display_key => null
115 ,p_time_building_block_id => p_time_building_block_id
116 ,p_object_version_number => p_object_version_number
117 );
118
119 l_time_building_block_id := p_time_building_block_id;
120 l_object_version_number := p_object_version_number;
121
122 -- second, insert attribute data into HXC_TIME_ATTRIBUTES
123 -- if there are any attributes to insert
124
125 IF ( p_timecard.count <> 0 )
126 THEN
127
128 hxc_time_attributes_api.create_attributes
129 (p_timecard => p_timecard
130 ,p_process_id => l_process_id
131 ,p_time_building_block_id => nvl(l_time_building_block_id_o
132 ,l_time_building_block_id)
133 ,p_tbb_ovn => l_object_version_number --l_tbb_ovn
134 ,p_time_attribute_id => l_time_attribute_id
135 ,p_object_version_number => l_tat_ovn
136 );
137
138 END IF;
139
140 -- set out parameter
141 p_time_building_block_id := l_time_building_block_id;
142 p_object_version_number := l_object_version_number;
143
144 else -- if p_object_version_number is not null then
145 -- check that if object_version_number is not null then it is the latest
146 -- time building block that is being updated
147 if not latest_ovn
148 (p_time_building_block_id => p_time_building_block_id
149 ,p_object_version_number => p_object_version_number
150 ) then
151 raise e_ovn_not_latest;
152 else
153 -- store the time_card_building_block_id that was passed in
154 l_time_building_block_id_o := p_time_building_block_id;
155
156 savepoint pre_create;
157
158 -- first, create a building block in HXC_TIME_BUILDING_BLOCKS
159 hxc_building_block_api.create_building_block
160 (p_effective_date => p_effective_date
161 ,p_type => p_type
162 ,p_measure => p_measure
163 ,p_unit_of_measure => p_unit_of_measure
164 ,p_start_time => p_start_time
165 ,p_stop_time => p_stop_time
166 ,p_parent_building_block_id => p_parent_building_block_id
167 ,p_parent_building_block_ovn => p_parent_building_block_ovn
168 ,p_scope => p_scope
169 ,p_approval_style_id => p_approval_style_id
170 ,p_approval_status => p_approval_status
171 ,p_resource_id => p_resource_id
172 ,p_resource_type => p_resource_type
173 ,p_comment_text => p_comment_text
174 ,p_application_set_id => p_application_set_id
175 ,p_translation_display_key => null
176 ,p_time_building_block_id => p_time_building_block_id
177 ,p_object_version_number => p_object_version_number
178 );
179
180 l_time_building_block_id := p_time_building_block_id;
181 l_object_version_number := p_object_version_number;
182
183 -- second, insert attribute data into HXC_TIME_ATTRIBUTES
184 hxc_time_attributes_api.create_attributes
185 (p_timecard => p_timecard
186 ,p_process_id => l_process_id
187 ,p_time_building_block_id => nvl(l_time_building_block_id_o
188 ,l_time_building_block_id)
189 ,p_tbb_ovn => l_object_version_number --l_tbb_ovn
190 ,p_time_attribute_id => l_time_attribute_id
191 ,p_object_version_number => l_tat_ovn
192 );
193
194 -- set out parameter
195 p_time_building_block_id := l_time_building_block_id;
196 p_object_version_number := l_object_version_number;
197 end if;
198 end if;
199
200 exception
201 when e_process_not_registered then
202 fnd_message.set_name('HXC', 'HXC_DEP_PROCESS_NOT_REGISTERED');
203 fnd_message.raise_error;
204
205 when e_ovn_not_latest then
206 fnd_message.set_name('HXC','HXC_TIME_BLD_BLK_NOT_LATEST');
207 fnd_message.raise_error;
208
209 when others then
210 rollback to pre_create;
211 raise;
212
213 end execute_deposit_process;
214
215
216 -- function
217 -- deposit_process_registered
218 --
219 -- description
220 -- returns deposit process id depending on
221 -- whether or not a deposit process is registered in the time
222 -- store for a given time source name and deposit name
223 --
224 -- parameters
225 -- p_source_name - the id of the time source
226 -- p_process_name - the id of the deposit process
227
228 FUNCTION deposit_process_registered
229 (p_source_name in varchar2
230 ,p_process_name in varchar2
231 ) RETURN number is
232
233 CURSOR csr_get_time_source_id IS
234 SELECT ts.time_source_id
235 FROM hxc_time_sources ts
236 WHERE name = p_source_name;
237
238 l_time_source_id hxc_time_sources.time_source_id%TYPE := NULL;
239
240 cursor c_process is
241 select deposit_process_id
242 from hxc_deposit_processes
243 where time_source_id = l_time_source_id
244 and name = p_process_name;
245
246 l_deposit_process_id hxc_deposit_processes.deposit_process_id%TYPE := NULL;
247
248 begin
249
250 OPEN csr_get_time_source_id;
251 FETCH csr_get_time_source_id INTO l_time_source_id;
252 CLOSE csr_get_time_source_id;
253
254 open c_process;
255 fetch c_process into l_deposit_process_id;
256 close c_process;
257
258 RETURN l_deposit_process_id;
259
260 end deposit_process_registered;
261
262
263
264 -- function
265 -- latest_ovn
266 --
267 -- description
268 -- returns true or false depending on whether or not the object version number
269 -- passed to the deposit api is the latest one.
270 --
271 -- parameters
272 -- p_time_building_block_id - the id of the time building block
273 -- p_object_version_number - ovn of the time building block
274
275 function latest_ovn
276 (p_time_building_block_id in number
277 ,p_object_version_number in number
278 ) return boolean is
279
280 -- You can only have one object version number of a block with date to of hr_general.end_of_time
281 cursor c_latest_ovn is
282 select null
283 from hxc_time_building_blocks tbb
284 where tbb.time_building_block_id = p_time_building_block_id
285 and tbb.object_version_number = p_object_version_number
286 and tbb.date_to = hr_general.end_of_time;
287 /*cursor c_latest_ovn is
288 select null
289 from hxc_time_building_blocks tbb
290 where tbb.time_building_block_id = p_time_building_block_id
291 and p_object_version_number = (select max(object_version_number) from hxc_time_building_blocks where time_building_block_id = tbb.time_building_block_id)
292 and tbb.date_to = to_date('31/12/4712','DD/MM/YYYY');
293 */
294 /*cursor c_latest_ovn is
295 select null
296 from hxc_time_building_blocks tbb
297 where tbb.time_building_block_id = p_time_building_block_id
298 and ((select max(object_version_number)
299 from hxc_time_building_blocks
300 where time_building_block_id = tbb.time_building_block_id)
301 = p_object_version_number)
302 and tbb.date_to = to_date('31/12/4712','DD/MM/YYYY');
303 */
304
305 l_null varchar2(1);
306
307 begin
308
309 open c_latest_ovn;
310 fetch c_latest_ovn into l_null;
311 if c_latest_ovn%found then
312 close c_latest_ovn;
313 return true;
314 else
315 close c_latest_ovn;
316 return false;
317 end if;
318
319 end latest_ovn;
320
321 end hxc_deposit_process_pkg;
[Home] [Help]