[Home] [Help]
PACKAGE BODY: APPS.PAY_ASSG_COST_SS
Source
1 package body PAY_ASSG_COST_SS as
2 /* $Header: pyacosss.pkb 120.0.12010000.3 2009/02/04 05:58:40 pgongada noship $ */
3 -- Package Variables
4 --
5 g_package varchar2(33) := ' PAY_ASSG_COST_SS.';
6 g_debug boolean := hr_utility.debug_enabled;
7 PROCEDURE CREATE_ASSG_COST(
8 P_ITEM_TYPE IN VARCHAR2
9 ,P_ITEM_KEY IN VARCHAR2
10 ,P_ACTID IN NUMBER
11 ,P_LOGIN_PERSON_ID IN NUMBER
12 ,P_EFFECTIVE_DATE IN DATE
13 ,P_ASSIGNMENT_ID IN NUMBER
14 ,P_BUSINESS_GROUP_ID IN NUMBER
15 ,P_PROPORTION IN NUMBER
16 ,P_COST_ALLOCATION_KEYFLEX_ID IN NUMBER
17 ,P_SEGMENT1 IN VARCHAR2 DEFAULT NULL
18 ,P_SEGMENT2 IN VARCHAR2 DEFAULT NULL
19 ,P_SEGMENT3 IN VARCHAR2 DEFAULT NULL
20 ,P_SEGMENT4 IN VARCHAR2 DEFAULT NULL
21 ,P_SEGMENT5 IN VARCHAR2 DEFAULT NULL
22 ,P_SEGMENT6 IN VARCHAR2 DEFAULT NULL
23 ,P_SEGMENT7 IN VARCHAR2 DEFAULT NULL
24 ,P_SEGMENT8 IN VARCHAR2 DEFAULT NULL
25 ,P_SEGMENT9 IN VARCHAR2 DEFAULT NULL
26 ,P_SEGMENT10 IN VARCHAR2 DEFAULT NULL
27 ,P_SEGMENT11 IN VARCHAR2 DEFAULT NULL
28 ,P_SEGMENT12 IN VARCHAR2 DEFAULT NULL
29 ,P_SEGMENT13 IN VARCHAR2 DEFAULT NULL
30 ,P_SEGMENT14 IN VARCHAR2 DEFAULT NULL
31 ,P_SEGMENT15 IN VARCHAR2 DEFAULT NULL
32 ,P_SEGMENT16 IN VARCHAR2 DEFAULT NULL
33 ,P_SEGMENT17 IN VARCHAR2 DEFAULT NULL
34 ,P_SEGMENT18 IN VARCHAR2 DEFAULT NULL
35 ,P_SEGMENT19 IN VARCHAR2 DEFAULT NULL
36 ,P_SEGMENT20 IN VARCHAR2 DEFAULT NULL
37 ,P_SEGMENT21 IN VARCHAR2 DEFAULT NULL
38 ,P_SEGMENT22 IN VARCHAR2 DEFAULT NULL
39 ,P_SEGMENT23 IN VARCHAR2 DEFAULT NULL
40 ,P_SEGMENT24 IN VARCHAR2 DEFAULT NULL
41 ,P_SEGMENT25 IN VARCHAR2 DEFAULT NULL
42 ,P_SEGMENT26 IN VARCHAR2 DEFAULT NULL
43 ,P_SEGMENT27 IN VARCHAR2 DEFAULT NULL
44 ,P_SEGMENT28 IN VARCHAR2 DEFAULT NULL
45 ,P_SEGMENT29 IN VARCHAR2 DEFAULT NULL
46 ,P_SEGMENT30 IN VARCHAR2 DEFAULT NULL
47 ,P_CONCATENATED_SEGMENTS IN VARCHAR2 DEFAULT NULL
48 ,P_EFFECTIVE_START_DATE IN DATE DEFAULT NULL
49 ,P_EFFECTIVE_END_DATE IN DATE DEFAULT NULL
50 ,P_TRANSACTION_ID OUT NOCOPY NUMBER
51 ,P_TRANSACTION_STEP_ID OUT NOCOPY NUMBER
52 ) is
53 l_proc varchar2(100) := g_package||'CREATE_ASSG_COST';
54
55 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
56 l_transaction_step_id HR_API_TRANSACTION_STEPS.TRANSACTION_STEP_ID%TYPE;
57 l_trs_object_version_number HR_API_TRANSACTION_STEPS.OBJECT_VERSION_NUMBER%TYPE;
58 l_transaction_table HR_TRANSACTION_SS.TRANSACTION_TABLE;
59 l_count NUMBER;
60 l_result VARCHAR2(100);
61 L_EFFECTIVE_DATE DATE;
62 BEGIN
63 IF g_debug THEN
64 hr_utility.set_location('Entering '||l_proc, 10);
65 hr_utility.set_location('Calling hr_transaction_ss.create_transaction_step', 100);
66 END IF;
67
68 IF P_EFFECTIVE_DATE IS NULL THEN
69 L_EFFECTIVE_DATE := SYSDATE;
70 ELSE
71 L_EFFECTIVE_DATE := P_EFFECTIVE_DATE;
72 END IF;
73 hr_transaction_ss.create_transaction_step(
74 p_item_type => p_item_type
75 ,p_item_key => p_item_key
76 ,p_actid => 0 --p_actid
77 ,p_login_person_id => p_login_person_id
78 ,p_api_name => g_package||'CREATE_DATA'
79 ,p_transaction_step_id => l_transaction_step_id
80 ,p_object_version_number => l_trs_object_version_number);
81 l_transaction_id:= hr_transaction_ss.get_transaction_id
82 (p_item_type => p_item_type
83 ,p_item_key => p_item_key);
84 IF g_debug THEN
85 hr_utility.set_location('Populating the user defined table structure hr_transaction_ss.transaction_table', 110);
86 END IF;
87
88 /*Populating the table structure HR_TRANSACTION_SS.TRANSACTION_TABLE with
89 the user specified values. This data will be used to populate the trasaction
90 tables and subsequently into the base tables once approved.*/
91 --
92 --TRANSACTION_ID
93 --
94 l_count := 1;
95 l_transaction_table(l_count).param_name := 'P_ITEM_TYPE';
96 l_transaction_table(l_count).param_value := p_item_type;
97 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
98 --
99 l_count := l_count + 1;
100 l_transaction_table(l_count).param_name := 'P_ITEM_KEY';
101 l_transaction_table(l_count).param_value := p_item_key;
102 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
103 --
104 l_count := l_count + 1;
105 l_transaction_table(l_count).param_name := 'P_ACTIVITY_ID';
106 l_transaction_table(l_count).param_value := p_actid;
107 l_transaction_table(l_count).param_data_type := 'NUMBER';
108 --
109 l_count := l_count + 1;
110 l_transaction_table(l_count).param_name := 'P_LOGIN_PERSON_ID';
111 l_transaction_table(l_count).param_value := P_LOGIN_PERSON_ID;
112 l_transaction_table(l_count).param_data_type := 'NUMBER';
113 --
114 l_count := l_count + 1;
115 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
116 l_transaction_table(l_count).param_value := to_char(L_effective_date,
117 hr_transaction_ss.g_date_format);
118 l_transaction_table(l_count).param_data_type := 'DATE';
119 --
120 l_count := l_count + 1;
121 l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
122 l_transaction_table(l_count).param_value := NULL;
123 l_transaction_table(l_count).param_data_type := 'NUMBER';
124 --
125 l_count := l_count + 1;
126 l_transaction_table(l_count).param_name := 'P_ASSIGNMENT_ID';
127 l_transaction_table(l_count).param_value := P_ASSIGNMENT_ID;
128 l_transaction_table(l_count).param_data_type := 'NUMBER';
129 --
130 l_count := l_count + 1;
131 l_transaction_table(l_count).param_name := 'P_COST_ALLOCATION_ID';
132 l_transaction_table(l_count).param_value := NULL;
133 l_transaction_table(l_count).param_data_type := 'NUMBER';
134 --
135 l_count := l_count + 1;
136 l_transaction_table(l_count).param_name := 'P_BUSINESS_GROUP_ID';
137 l_transaction_table(l_count).param_value := P_BUSINESS_GROUP_ID;
138 l_transaction_table(l_count).param_data_type := 'NUMBER';
139 --
140 l_count := l_count + 1;
141 l_transaction_table(l_count).param_name := 'P_COST_ALLOCATION_KEYFLEX_ID';
142 l_transaction_table(l_count).param_value := P_COST_ALLOCATION_KEYFLEX_ID;
143 l_transaction_table(l_count).param_data_type := 'NUMBER';
144 --
145 l_count := l_count + 1;
146 l_transaction_table(l_count).param_name := 'P_PROPORTION';
147 l_transaction_table(l_count).param_value := P_PROPORTION;
148 l_transaction_table(l_count).param_data_type := 'NUMBER';
149 --
150 l_count := l_count + 1;
151 l_transaction_table(l_count).param_name := 'P_SEGMENT1';
152 l_transaction_table(l_count).param_value := P_SEGMENT1;
153 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
154 --
155 l_count := l_count + 1;
156 l_transaction_table(l_count).param_name := 'P_SEGMENT2';
157 l_transaction_table(l_count).param_value := P_SEGMENT2;
158 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
159 --
160 l_count := l_count + 1;
161 l_transaction_table(l_count).param_name := 'P_SEGMENT3';
162 l_transaction_table(l_count).param_value := P_SEGMENT3;
163 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
164 --
165 l_count := l_count + 1;
166 l_transaction_table(l_count).param_name := 'P_SEGMENT4';
167 l_transaction_table(l_count).param_value := P_SEGMENT4;
168 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
169 --
170 l_count := l_count + 1;
171 l_transaction_table(l_count).param_name := 'P_SEGMENT5';
172 l_transaction_table(l_count).param_value := P_SEGMENT5;
173 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
174 --
175 l_count := l_count + 1;
176 l_transaction_table(l_count).param_name := 'P_SEGMENT6';
177 l_transaction_table(l_count).param_value := P_SEGMENT6;
178 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
179 --
180 l_count := l_count + 1;
181 l_transaction_table(l_count).param_name := 'P_SEGMENT7';
182 l_transaction_table(l_count).param_value := P_SEGMENT7;
183 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
184 --
185 l_count := l_count + 1;
186 l_transaction_table(l_count).param_name := 'P_SEGMENT8';
187 l_transaction_table(l_count).param_value := P_SEGMENT8;
188 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
189 --
190 l_count := l_count + 1;
191 l_transaction_table(l_count).param_name := 'P_SEGMENT9';
192 l_transaction_table(l_count).param_value := P_SEGMENT9;
193 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
194 --
195 l_count := l_count + 1;
196 l_transaction_table(l_count).param_name := 'P_SEGMENT10';
197 l_transaction_table(l_count).param_value := P_SEGMENT10;
198 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
199 --
200 l_count := l_count + 1;
201 l_transaction_table(l_count).param_name := 'P_SEGMENT11';
202 l_transaction_table(l_count).param_value := P_SEGMENT11;
203 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
204 --
205 l_count := l_count + 1;
206 l_transaction_table(l_count).param_name := 'P_SEGMENT12';
207 l_transaction_table(l_count).param_value := P_SEGMENT12;
208 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
209 --
210 l_count := l_count + 1;
211 l_transaction_table(l_count).param_name := 'P_SEGMENT13';
212 l_transaction_table(l_count).param_value := P_SEGMENT13;
213 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
214 --
215 l_count := l_count + 1;
216 l_transaction_table(l_count).param_name := 'P_SEGMENT14';
217 l_transaction_table(l_count).param_value := P_SEGMENT14;
218 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
219 --
220 l_count := l_count + 1;
221 l_transaction_table(l_count).param_name := 'P_SEGMENT15';
222 l_transaction_table(l_count).param_value := P_SEGMENT15;
223 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
224 --
225 l_count := l_count + 1;
226 l_transaction_table(l_count).param_name := 'P_SEGMENT16';
227 l_transaction_table(l_count).param_value := P_SEGMENT16;
228 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
229 --
230 l_count := l_count + 1;
231 l_transaction_table(l_count).param_name := 'P_SEGMENT17';
232 l_transaction_table(l_count).param_value := P_SEGMENT17;
233 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
234 --
235 l_count := l_count + 1;
236 l_transaction_table(l_count).param_name := 'P_SEGMENT18';
237 l_transaction_table(l_count).param_value := P_SEGMENT18;
238 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
239 --
240 l_count := l_count + 1;
241 l_transaction_table(l_count).param_name := 'P_SEGMENT19';
242 l_transaction_table(l_count).param_value := P_SEGMENT19;
243 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
244 --
245 l_count := l_count + 1;
246 l_transaction_table(l_count).param_name := 'P_SEGMENT20';
247 l_transaction_table(l_count).param_value := P_SEGMENT20;
248 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
249 --
250 l_count := l_count + 1;
251 l_transaction_table(l_count).param_name := 'P_SEGMENT21';
252 l_transaction_table(l_count).param_value := P_SEGMENT21;
253 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
254 --
255 l_count := l_count + 1;
256 l_transaction_table(l_count).param_name := 'P_SEGMENT22';
257 l_transaction_table(l_count).param_value := P_SEGMENT22;
258 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
259 --
260 l_count := l_count + 1;
261 l_transaction_table(l_count).param_name := 'P_SEGMENT23';
262 l_transaction_table(l_count).param_value := P_SEGMENT23;
263 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
264 --
265 l_count := l_count + 1;
266 l_transaction_table(l_count).param_name := 'P_SEGMENT24';
267 l_transaction_table(l_count).param_value := P_SEGMENT24;
268 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
269 --
270 l_count := l_count + 1;
271 l_transaction_table(l_count).param_name := 'P_SEGMENT25';
272 l_transaction_table(l_count).param_value := P_SEGMENT25;
273 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
274 --
275 l_count := l_count + 1;
276 l_transaction_table(l_count).param_name := 'P_SEGMENT26';
277 l_transaction_table(l_count).param_value := P_SEGMENT26;
278 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
279 --
280 l_count := l_count + 1;
281 l_transaction_table(l_count).param_name := 'P_SEGMENT27';
282 l_transaction_table(l_count).param_value := P_SEGMENT27;
283 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
284 --
285 l_count := l_count + 1;
286 l_transaction_table(l_count).param_name := 'P_SEGMENT28';
287 l_transaction_table(l_count).param_value := P_SEGMENT28;
288 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
289 --
290 l_count := l_count + 1;
291 l_transaction_table(l_count).param_name := 'P_SEGMENT29';
292 l_transaction_table(l_count).param_value := P_SEGMENT29;
293 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
294 --
295 l_count := l_count + 1;
296 l_transaction_table(l_count).param_name := 'P_SEGMENT30';
297 l_transaction_table(l_count).param_value := P_SEGMENT30;
298 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
299 --
300 l_count := l_count + 1;
301 l_transaction_table(l_count).param_name := 'P_CONCATENATED_SEGMENTS';
302 l_transaction_table(l_count).param_value := P_CONCATENATED_SEGMENTS;
303 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
304 --
305 l_count := l_count + 1;
306 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_START_DATE';
307 if P_EFFECTIVE_START_DATE IS NULL THEN
308 l_transaction_table(l_count).param_value := NULL;
309 else
310 l_transaction_table(l_count).param_value := to_char(P_EFFECTIVE_START_DATE,
311 hr_transaction_ss.g_date_format);
312 end if;
313 l_transaction_table(l_count).param_data_type := 'DATE';
314 --
315 l_count := l_count + 1;
316 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_END_DATE';
317 if P_EFFECTIVE_END_DATE IS NULL THEN
318 l_transaction_table(l_count).param_value := NULL;
319 else
320 l_transaction_table(l_count).param_value := to_char(P_EFFECTIVE_END_DATE,
321 hr_transaction_ss.g_date_format);
322 end if;
323 l_transaction_table(l_count).param_data_type := 'DATE';
324 --
325 IF g_debug THEN
326 hr_utility.set_location('Calling HR_TRANSACTION_SS.SAVE_TRANSACTION_STEP', 120);
327 END IF;
328 /*Save data into transaction tables*/
329 hr_transaction_ss.save_transaction_step
330 (p_item_type => p_item_type
331 ,p_item_key => p_item_key
332 ,p_actid => 0 --p_actid
333 ,p_login_person_id => p_login_person_id
334 ,p_transaction_step_id => l_transaction_step_id
335 ,p_api_name => g_package || 'CREATE_DATA'
336 ,p_transaction_data => l_transaction_table);
337 IF g_debug THEN
338 hr_utility.set_location('After Calling HR_TRANSACTION_SS.SAVE_TRANSACTION_STEP', 130);
339 hr_utility.set_location('Leaving '||l_proc, 1000);
340 END IF;
341 p_transaction_id := l_transaction_id;
342 p_transaction_step_id := l_transaction_step_id;
343 END CREATE_ASSG_COST;
344
345 /*Once the WF is approved then we have to call this to insert the data into actual table*/
346 PROCEDURE CREATE_DATA(
347 P_VALIDATE IN BOOLEAN DEFAULT FALSE
348 ,P_TRANSACTION_STEP_ID IN NUMBER
349 )
350 IS
351 l_proc varchar2(100) := g_package||'CREATE_DATA';
352 l_effective_date date;
353 l_effective_start_date date;
354 l_effective_end_date date;
355 l_cost_allocation_keyflex_id PAY_COST_ALLOCATION_KEYFLEX.COST_ALLOCATION_KEYFLEX_ID%TYPE;
356 l_object_version_number PAY_COST_ALLOCATIONS_F.OBJECT_VERSION_NUMBER%TYPE;
357 l_combination_name PAY_COST_ALLOCATION_KEYFLEX.CONCATENATED_SEGMENTS%TYPE;
358 l_cost_allocation_id PAY_COST_ALLOCATIONS_F.COST_ALLOCATION_ID%TYPE;
359 l_login_person_id HR_API_TRANSACTIONS.CREATOR_PERSON_ID%TYPE;
360
361 BEGIN
362 IF g_debug THEN
363 hr_utility.set_location('Entering '||l_proc,10);
364 END IF;
365 /*Create save point before starting database operation*/
366 SAVEPOINT create_date;
367
368 if p_validate = false then
369 --
370 l_effective_date := hr_transaction_api.get_date_value(p_transaction_step_id,'P_EFFECTIVE_DATE');
371 if l_effective_date is null then
372 l_effective_date := sysdate;
373 end if;
374 if g_debug then
375 hr_utility.set_location('Get P_COST_ALLOCATION_KEYFLEX_ID and P_OBJECT_VERSION_NUMBER', 20);
376 end if;
377 /*Get the parameters to be used in the API call*/
378 l_cost_allocation_keyflex_id := hr_transaction_api.get_number_value
379 (p_transaction_step_id => p_transaction_step_id
380 ,p_name => 'P_COST_ALLOCATION_KEYFLEX_ID');
381 l_object_version_number := hr_transaction_api.get_number_value
382 (p_transaction_step_id => p_transaction_step_id
383 ,p_name => 'P_OBJECT_VERSION_NUMBER');
384 if g_debug then
385 hr_utility.set_location('P_COST_ALLOCATION_KEYFLEX_ID => '||NVL(l_cost_allocation_keyflex_id,-1), 30);
386 hr_utility.set_location('P_OBJECT_VERSION_NUMBER => '||NVL(l_object_version_number,-1), 40);
387 end if;
388
389 if g_debug then
390 hr_utility.set_location('Calling PAY_COST_ALLOCATION_API.CREATE_COST_ALLOCATION', 50);
391 end if;
392 /*Now call the API to create cost allocations*/
393 PAY_COST_ALLOCATION_API.CREATE_COST_ALLOCATION
394 (p_validate => p_validate
395 ,p_effective_date => l_effective_date
396 ,p_assignment_id => hr_transaction_api.get_number_value
397 (p_transaction_step_id => p_transaction_step_id
398 ,p_name => 'P_ASSIGNMENT_ID')
399 ,p_proportion => hr_transaction_api.get_number_value
400 (p_transaction_step_id => p_transaction_step_id
401 ,p_name => 'P_PROPORTION')/100
402 ,p_business_group_id => hr_transaction_api.get_number_value
403 (p_transaction_step_id => p_transaction_step_id
404 ,p_name => 'P_BUSINESS_GROUP_ID')
405 ,p_segment1 => hr_transaction_api.get_varchar2_value
409 (p_transaction_step_id => p_transaction_step_id
406 (p_transaction_step_id => p_transaction_step_id
407 ,p_name => 'P_SEGMENT1')
408 ,p_segment2 => hr_transaction_api.get_varchar2_value
410 ,p_name => 'P_SEGMENT2')
411 ,p_segment3 => hr_transaction_api.get_varchar2_value
412 (p_transaction_step_id => p_transaction_step_id
413 ,p_name => 'P_SEGMENT3')
414 ,p_segment4 => hr_transaction_api.get_varchar2_value
415 (p_transaction_step_id => p_transaction_step_id
416 ,p_name => 'P_SEGMENT4')
417 ,p_segment5 => hr_transaction_api.get_varchar2_value
418 (p_transaction_step_id => p_transaction_step_id
419 ,p_name => 'P_SEGMENT5')
420 ,p_segment6 => hr_transaction_api.get_varchar2_value
421 (p_transaction_step_id => p_transaction_step_id
422 ,p_name => 'P_SEGMENT6')
423 ,p_segment7 => hr_transaction_api.get_varchar2_value
424 (p_transaction_step_id => p_transaction_step_id
425 ,p_name => 'P_SEGMENT7')
426 ,p_segment8 => hr_transaction_api.get_varchar2_value
427 (p_transaction_step_id => p_transaction_step_id
428 ,p_name => 'P_SEGMENT8')
429 ,p_segment9 => hr_transaction_api.get_varchar2_value
430 (p_transaction_step_id => p_transaction_step_id
431 ,p_name => 'P_SEGMENT9')
432 ,p_segment10 => hr_transaction_api.get_varchar2_value
433 (p_transaction_step_id => p_transaction_step_id
434 ,p_name => 'P_SEGMENT10')
435 ,p_segment11 => hr_transaction_api.get_varchar2_value
436 (p_transaction_step_id => p_transaction_step_id
437 ,p_name => 'P_SEGMENT11')
438 ,p_segment12 => hr_transaction_api.get_varchar2_value
439 (p_transaction_step_id => p_transaction_step_id
440 ,p_name => 'P_SEGMENT12')
441 ,p_segment13 => hr_transaction_api.get_varchar2_value
442 (p_transaction_step_id => p_transaction_step_id
443 ,p_name => 'P_SEGMENT13')
444 ,p_segment14 => hr_transaction_api.get_varchar2_value
445 (p_transaction_step_id => p_transaction_step_id
446 ,p_name => 'P_SEGMENT14')
447 ,p_segment15 => hr_transaction_api.get_varchar2_value
448 (p_transaction_step_id => p_transaction_step_id
449 ,p_name => 'P_SEGMENT15')
450 ,p_segment16 => hr_transaction_api.get_varchar2_value
451 (p_transaction_step_id => p_transaction_step_id
452 ,p_name => 'P_SEGMENT16')
453 ,p_segment17 => hr_transaction_api.get_varchar2_value
454 (p_transaction_step_id => p_transaction_step_id
455 ,p_name => 'P_SEGMENT17')
456 ,p_segment18 => hr_transaction_api.get_varchar2_value
457 (p_transaction_step_id => p_transaction_step_id
458 ,p_name => 'P_SEGMENT18')
459 ,p_segment19 => hr_transaction_api.get_varchar2_value
460 (p_transaction_step_id => p_transaction_step_id
461 ,p_name => 'P_SEGMENT19')
462 ,p_segment20 => hr_transaction_api.get_varchar2_value
463 (p_transaction_step_id => p_transaction_step_id
464 ,p_name => 'P_SEGMENT20')
465 ,p_segment21 => hr_transaction_api.get_varchar2_value
466 (p_transaction_step_id => p_transaction_step_id
467 ,p_name => 'P_SEGMENT21')
468 ,p_segment22 => hr_transaction_api.get_varchar2_value
469 (p_transaction_step_id => p_transaction_step_id
470 ,p_name => 'P_SEGMENT22')
471 ,p_segment23 => hr_transaction_api.get_varchar2_value
472 (p_transaction_step_id => p_transaction_step_id
473 ,p_name => 'P_SEGMENT23')
474 ,p_segment24 => hr_transaction_api.get_varchar2_value
475 (p_transaction_step_id => p_transaction_step_id
476 ,p_name => 'P_SEGMENT24')
477 ,p_segment25 => hr_transaction_api.get_varchar2_value
478 (p_transaction_step_id => p_transaction_step_id
479 ,p_name => 'P_SEGMENT25')
480 ,p_segment26 => hr_transaction_api.get_varchar2_value
481 (p_transaction_step_id => p_transaction_step_id
482 ,p_name => 'P_SEGMENT26')
483 ,p_segment27 => hr_transaction_api.get_varchar2_value
484 (p_transaction_step_id => p_transaction_step_id
485 ,p_name => 'P_SEGMENT27')
486 ,p_segment28 => hr_transaction_api.get_varchar2_value
487 (p_transaction_step_id => p_transaction_step_id
488 ,p_name => 'P_SEGMENT28')
489 ,p_segment29 => hr_transaction_api.get_varchar2_value
493 (p_transaction_step_id => p_transaction_step_id
490 (p_transaction_step_id => p_transaction_step_id
491 ,p_name => 'P_SEGMENT29')
492 ,p_segment30 => hr_transaction_api.get_varchar2_value
494 ,p_name => 'P_SEGMENT30')
495 ,p_combination_name => l_combination_name
496 ,p_cost_allocation_id => l_cost_allocation_id
497 ,p_effective_start_date => l_effective_start_date
498 ,p_effective_end_date => l_effective_end_date
499 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
500 ,p_object_version_number => l_object_version_number);
501
502 if g_debug then
503 hr_utility.set_location('After Calling PAY_COST_ALLOCATION_API.CREATE_COST_ALLOCATION', 60);
504 end if;
505
506
507 end if;
511 --
508 if g_debug then
509 hr_utility.set_location('Leaving '||l_proc,1000);
510 end if;
512 EXCEPTION
513 WHEN hr_utility.hr_error THEN
514 -- -----------------------------------------------------------------
515 -- An application error has been raised by the API so we must set
516 -- the error.
517 -- -----------------------------------------------------------------
518 hr_utility.set_location('Exception:hr_utility.hr_error THEN'||l_proc,555);
519 hr_utility.set_location('Rolling back the data',666);
520 ROLLBACK TO CREATE_DATA;
521 hr_utility.set_location('Leaving '||l_proc,1000);
522 RAISE;
523 WHEN OTHERS THEN
524 hr_utility.set_location('Unknown error occurred....Rolling back the data',777);
525 ROLLBACK TO CREATE_DATA;
526 hr_utility.set_location('Leaving '||l_proc,1000);
527 END CREATE_DATA;
528
529 PROCEDURE UPDATE_ASSG_COST(
530 P_ITEM_TYPE IN VARCHAR2
531 ,P_ITEM_KEY IN VARCHAR2
532 ,P_ACTID IN NUMBER
533 ,P_LOGIN_PERSON_ID IN NUMBER
534 ,P_UPDATE_MODE IN VARCHAR2 DEFAULT 'UPDATE'
535 ,P_EFFECTIVE_DATE IN DATE DEFAULT SYSDATE
536 ,P_ASSIGNMENT_ID IN NUMBER
537 ,P_COST_ALLOCATION_ID IN NUMBER
538 ,P_BUSINESS_GROUP_ID IN NUMBER
539 ,P_COST_ALLOCATION_KEYFLEX_ID IN NUMBER
540 ,P_OBJECT_VERSION_NUMBER IN NUMBER
541 ,P_PROPORTION IN NUMBER default hr_api.g_number
542 ,P_SEGMENT1 IN VARCHAR2 default hr_api.g_varchar2
543 ,P_SEGMENT2 IN VARCHAR2 default hr_api.g_varchar2
544 ,P_SEGMENT3 IN VARCHAR2 default hr_api.g_varchar2
545 ,P_SEGMENT4 IN VARCHAR2 default hr_api.g_varchar2
546 ,P_SEGMENT5 IN VARCHAR2 default hr_api.g_varchar2
547 ,P_SEGMENT6 IN VARCHAR2 default hr_api.g_varchar2
548 ,P_SEGMENT7 IN VARCHAR2 default hr_api.g_varchar2
549 ,P_SEGMENT8 IN VARCHAR2 default hr_api.g_varchar2
550 ,P_SEGMENT9 IN VARCHAR2 default hr_api.g_varchar2
551 ,P_SEGMENT10 IN VARCHAR2 default hr_api.g_varchar2
552 ,P_SEGMENT11 IN VARCHAR2 default hr_api.g_varchar2
553 ,P_SEGMENT12 IN VARCHAR2 default hr_api.g_varchar2
554 ,P_SEGMENT13 IN VARCHAR2 default hr_api.g_varchar2
555 ,P_SEGMENT14 IN VARCHAR2 default hr_api.g_varchar2
556 ,P_SEGMENT15 IN VARCHAR2 default hr_api.g_varchar2
557 ,P_SEGMENT16 IN VARCHAR2 default hr_api.g_varchar2
558 ,P_SEGMENT17 IN VARCHAR2 default hr_api.g_varchar2
559 ,P_SEGMENT18 IN VARCHAR2 default hr_api.g_varchar2
560 ,P_SEGMENT19 IN VARCHAR2 default hr_api.g_varchar2
561 ,P_SEGMENT20 IN VARCHAR2 default hr_api.g_varchar2
562 ,P_SEGMENT21 IN VARCHAR2 default hr_api.g_varchar2
563 ,P_SEGMENT22 IN VARCHAR2 default hr_api.g_varchar2
567 ,P_SEGMENT26 IN VARCHAR2 default hr_api.g_varchar2
564 ,P_SEGMENT23 IN VARCHAR2 default hr_api.g_varchar2
565 ,P_SEGMENT24 IN VARCHAR2 default hr_api.g_varchar2
566 ,P_SEGMENT25 IN VARCHAR2 default hr_api.g_varchar2
568 ,P_SEGMENT27 IN VARCHAR2 default hr_api.g_varchar2
569 ,P_SEGMENT28 IN VARCHAR2 default hr_api.g_varchar2
570 ,P_SEGMENT29 IN VARCHAR2 default hr_api.g_varchar2
571 ,P_SEGMENT30 IN VARCHAR2 default hr_api.g_varchar2
572 ,P_CONCATENATED_SEGMENTS IN VARCHAR2 DEFAULT HR_API.G_VARCHAR2
573 ,P_EFFECTIVE_START_DATE IN DATE DEFAULT NULL
574 ,P_EFFECTIVE_END_DATE IN DATE DEFAULT NULL
575 ,P_TRANSACTION_ID OUT NOCOPY NUMBER
576 ,P_TRANSACTION_STEP_ID OUT NOCOPY NUMBER
577 ) as
578 l_proc varchar2(100) := g_package||'UPDATE_ASSG_COST';
579
580 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
581 l_transaction_step_id HR_API_TRANSACTION_STEPS.TRANSACTION_STEP_ID%TYPE;
582 l_trs_object_version_number HR_API_TRANSACTION_STEPS.OBJECT_VERSION_NUMBER%TYPE;
583 l_transaction_table HR_TRANSACTION_SS.TRANSACTION_TABLE;
584 l_count NUMBER;
585 l_effective_date date;
586 l_result varchar2(2000);
587 BEGIN
588 IF g_debug THEN
589 hr_utility.set_location('Entering '||l_proc, 10);
590 END IF;
591 IF P_EFFECTIVE_DATE IS NULL THEN
592 l_EFFECTIVE_DATE := SYSDATE;
593 else
594 l_EFFECTIVE_DATE := P_EFFECTIVE_DATE;
595 END IF;
596 IF g_debug THEN
597 hr_utility.set_location('Calling hr_transaction_ss.create_transaction_step', 100);
598 END IF;
599 /*This CREATE_TRANSACTION_STEP() find out of any transaction created with
600 the given ITEM TYPE and ITEM KEY. If exists then it creates transaction
601 step if not it creates a transactions and transaction step as well.*/
602 hr_transaction_ss.create_transaction_step(
603 p_item_type => p_item_type
604 ,p_item_key => p_item_key
605 ,p_actid => 0 --p_actid
606 ,p_login_person_id => p_login_person_id
607 ,p_api_name => g_package||'UPDATE_DATA'
608 ,p_transaction_step_id => l_transaction_step_id
609 ,p_object_version_number => l_trs_object_version_number);
610 IF g_debug THEN
611 hr_utility.set_location('Populating the user defined table structure hr_transaction_ss.transaction_table', 100);
612 END IF;
613 /*Populating the table structure HR_TRANSACTION_SS.TRANSACTION_TABLE with
614 the user specified values. This data will be used to populate the trasaction
615 tables and subsequently into the base tables.
616 */
617 l_count := 1;
618 l_transaction_table(l_count).param_name := 'P_ITEM_TYPE';
619 l_transaction_table(l_count).param_value := p_item_type;
620 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
621
622 --
623 l_count := l_count + 1;
624 l_transaction_table(l_count).param_name := 'P_ITEM_KEY';
625 l_transaction_table(l_count).param_value := p_item_key;
626 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
627 --
628 l_count := l_count + 1;
629 l_transaction_table(l_count).param_name := 'P_ACTIVITY_ID';
630 l_transaction_table(l_count).param_value := p_actid;
631 l_transaction_table(l_count).param_data_type := 'NUMBER';
632 --
633 l_count := l_count + 1;
634 l_transaction_table(l_count).param_name := 'P_LOGIN_PERSON_ID';
635 l_transaction_table(l_count).param_value := P_LOGIN_PERSON_ID;
636 l_transaction_table(l_count).param_data_type := 'NUMBER';
637 --
638 l_count := l_count + 1;
639 l_transaction_table(l_count).param_name := 'P_UPDATE_MODE';
640 l_transaction_table(l_count).param_value := P_UPDATE_MODE;
641 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
642 --
643 l_count := l_count + 1;
644 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
645 l_transaction_table(l_count).param_value := to_char(l_effective_date,
646 hr_transaction_ss.g_date_format);
647 l_transaction_table(l_count).param_data_type := 'DATE';
648 --
649 l_count := l_count + 1;
650 l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
651 l_transaction_table(l_count).param_value := P_OBJECT_VERSION_NUMBER;
652 l_transaction_table(l_count).param_data_type := 'NUMBER';
653 --
654 l_count := l_count + 1;
655 l_transaction_table(l_count).param_name := 'P_ASSIGNMENT_ID';
656 l_transaction_table(l_count).param_value := P_ASSIGNMENT_ID;
657 l_transaction_table(l_count).param_data_type := 'NUMBER';
658 --
659 l_count := l_count + 1;
660 l_transaction_table(l_count).param_name := 'P_COST_ALLOCATION_ID';
661 l_transaction_table(l_count).param_value := P_COST_ALLOCATION_ID;
662 l_transaction_table(l_count).param_data_type := 'NUMBER';
663 --
664 l_count := l_count + 1;
665 l_transaction_table(l_count).param_name := 'P_BUSINESS_GROUP_ID';
666 l_transaction_table(l_count).param_value := P_BUSINESS_GROUP_ID;
667 l_transaction_table(l_count).param_data_type := 'NUMBER';
668 --
669 l_count := l_count + 1;
670 l_transaction_table(l_count).param_name := 'P_COST_ALLOCATION_KEYFLEX_ID';
671 l_transaction_table(l_count).param_value := P_COST_ALLOCATION_KEYFLEX_ID;
672 l_transaction_table(l_count).param_data_type := 'NUMBER';
673 --
674 l_count := l_count + 1;
675 l_transaction_table(l_count).param_name := 'P_PROPORTION';
676 l_transaction_table(l_count).param_value := P_PROPORTION;
677 l_transaction_table(l_count).param_data_type := 'NUMBER';
678 --
679 l_count := l_count + 1;
680 l_transaction_table(l_count).param_name := 'P_SEGMENT1';
681 l_transaction_table(l_count).param_value := P_SEGMENT1;
682 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
683 --
684 l_count := l_count + 1;
685 l_transaction_table(l_count).param_name := 'P_SEGMENT2';
686 l_transaction_table(l_count).param_value := P_SEGMENT2;
687 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
688 --
689 l_count := l_count + 1;
690 l_transaction_table(l_count).param_name := 'P_SEGMENT3';
691 l_transaction_table(l_count).param_value := P_SEGMENT3;
692 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
693 --
694 l_count := l_count + 1;
695 l_transaction_table(l_count).param_name := 'P_SEGMENT4';
696 l_transaction_table(l_count).param_value := P_SEGMENT4;
697 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
698 --
699 l_count := l_count + 1;
700 l_transaction_table(l_count).param_name := 'P_SEGMENT5';
701 l_transaction_table(l_count).param_value := P_SEGMENT5;
702 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
703 --
704 l_count := l_count + 1;
705 l_transaction_table(l_count).param_name := 'P_SEGMENT6';
706 l_transaction_table(l_count).param_value := P_SEGMENT6;
707 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
708 --
709 l_count := l_count + 1;
710 l_transaction_table(l_count).param_name := 'P_SEGMENT7';
711 l_transaction_table(l_count).param_value := P_SEGMENT7;
712 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
713 --
714 l_count := l_count + 1;
715 l_transaction_table(l_count).param_name := 'P_SEGMENT8';
716 l_transaction_table(l_count).param_value := P_SEGMENT8;
717 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
718 --
719 l_count := l_count + 1;
720 l_transaction_table(l_count).param_name := 'P_SEGMENT9';
721 l_transaction_table(l_count).param_value := P_SEGMENT9;
722 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
723 --
724 l_count := l_count + 1;
725 l_transaction_table(l_count).param_name := 'P_SEGMENT10';
726 l_transaction_table(l_count).param_value := P_SEGMENT10;
727 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
728 --
729 l_count := l_count + 1;
730 l_transaction_table(l_count).param_name := 'P_SEGMENT11';
731 l_transaction_table(l_count).param_value := P_SEGMENT11;
732 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
733 --
734 l_count := l_count + 1;
735 l_transaction_table(l_count).param_name := 'P_SEGMENT12';
736 l_transaction_table(l_count).param_value := P_SEGMENT12;
737 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
738 --
739 l_count := l_count + 1;
740 l_transaction_table(l_count).param_name := 'P_SEGMENT13';
741 l_transaction_table(l_count).param_value := P_SEGMENT13;
742 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
743 --
744 l_count := l_count + 1;
745 l_transaction_table(l_count).param_name := 'P_SEGMENT14';
746 l_transaction_table(l_count).param_value := P_SEGMENT14;
747 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
748 --
749 l_count := l_count + 1;
750 l_transaction_table(l_count).param_name := 'P_SEGMENT15';
751 l_transaction_table(l_count).param_value := P_SEGMENT15;
752 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
753 --
754 l_count := l_count + 1;
755 l_transaction_table(l_count).param_name := 'P_SEGMENT16';
756 l_transaction_table(l_count).param_value := P_SEGMENT16;
757 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
758 --
759 l_count := l_count + 1;
760 l_transaction_table(l_count).param_name := 'P_SEGMENT17';
761 l_transaction_table(l_count).param_value := P_SEGMENT17;
762 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
763 --
764 l_count := l_count + 1;
765 l_transaction_table(l_count).param_name := 'P_SEGMENT18';
766 l_transaction_table(l_count).param_value := P_SEGMENT18;
767 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
768 --
769 l_count := l_count + 1;
770 l_transaction_table(l_count).param_name := 'P_SEGMENT19';
771 l_transaction_table(l_count).param_value := P_SEGMENT19;
772 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
773 --
774 l_count := l_count + 1;
775 l_transaction_table(l_count).param_name := 'P_SEGMENT20';
776 l_transaction_table(l_count).param_value := P_SEGMENT20;
777 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
778 --
779 l_count := l_count + 1;
780 l_transaction_table(l_count).param_name := 'P_SEGMENT21';
781 l_transaction_table(l_count).param_value := P_SEGMENT21;
782 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
783 --
784 l_count := l_count + 1;
785 l_transaction_table(l_count).param_name := 'P_SEGMENT22';
786 l_transaction_table(l_count).param_value := P_SEGMENT22;
787 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
788 --
789 l_count := l_count + 1;
790 l_transaction_table(l_count).param_name := 'P_SEGMENT23';
791 l_transaction_table(l_count).param_value := P_SEGMENT23;
792 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
793 --
794 l_count := l_count + 1;
795 l_transaction_table(l_count).param_name := 'P_SEGMENT24';
796 l_transaction_table(l_count).param_value := P_SEGMENT24;
797 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
798 --
799 l_count := l_count + 1;
800 l_transaction_table(l_count).param_name := 'P_SEGMENT25';
801 l_transaction_table(l_count).param_value := P_SEGMENT25;
802 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
803 --
804 l_count := l_count + 1;
805 l_transaction_table(l_count).param_name := 'P_SEGMENT26';
806 l_transaction_table(l_count).param_value := P_SEGMENT26;
807 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
808 --
809 l_count := l_count + 1;
810 l_transaction_table(l_count).param_name := 'P_SEGMENT27';
811 l_transaction_table(l_count).param_value := P_SEGMENT27;
812 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
813 --
814 l_count := l_count + 1;
815 l_transaction_table(l_count).param_name := 'P_SEGMENT28';
816 l_transaction_table(l_count).param_value := P_SEGMENT28;
817 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
818 --
819 l_count := l_count + 1;
820 l_transaction_table(l_count).param_name := 'P_SEGMENT29';
821 l_transaction_table(l_count).param_value := P_SEGMENT29;
822 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
823 --
824 l_count := l_count + 1;
825 l_transaction_table(l_count).param_name := 'P_SEGMENT30';
826 l_transaction_table(l_count).param_value := P_SEGMENT30;
827 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
828 --
829 l_count := l_count + 1;
830 l_transaction_table(l_count).param_name := 'P_CONCATENATED_SEGMENTS';
831 l_transaction_table(l_count).param_value := P_CONCATENATED_SEGMENTS;
832 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
833 --
834 l_count := l_count + 1;
835 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_START_DATE';
836 if P_EFFECTIVE_START_DATE IS NULL THEN
837 l_transaction_table(l_count).param_value := NULL;
838 else
839 l_transaction_table(l_count).param_value := to_char(P_EFFECTIVE_START_DATE,
840 hr_transaction_ss.g_date_format);
841 end if;
842 l_transaction_table(l_count).param_data_type := 'DATE';
843 --
844 l_count := l_count + 1;
845 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_END_DATE';
846 if P_EFFECTIVE_END_DATE IS NULL THEN
847 l_transaction_table(l_count).param_value := NULL;
848 else
849 l_transaction_table(l_count).param_value := to_char(P_EFFECTIVE_END_DATE,
850 hr_transaction_ss.g_date_format);
851 end if;
852 l_transaction_table(l_count).param_data_type := 'DATE';
853 --
854 /*Save data into transaction tables*/
855 if g_debug then
856 hr_utility.set_location('Calling HR_TRANSACTION_SS.SAVE_TRANSACTION_STEP', 110);
857 end if;
858 hr_transaction_ss.save_transaction_step
859 (p_item_type => p_item_type
860 ,p_item_key => p_item_key
861 ,p_actid => 0 --p_actid
862 ,p_login_person_id => p_login_person_id
863 ,p_transaction_step_id => l_transaction_step_id
864 ,p_api_name => g_package || 'UPDATE_DATA'
865 ,p_transaction_data => l_transaction_table);
866 p_transaction_id := l_transaction_id;
867 p_transaction_step_id := l_transaction_step_id;
868 if g_debug then
869 hr_utility.set_location('After Calling HR_TRANSACTION_SS.SAVE_TRANSACTION_STEP', 110);
870 hr_utility.set_location('Leaving '||l_proc,1000);
871 end if;
872 END UPDATE_ASSG_COST;
873
874 PROCEDURE UPDATE_DATA(
875 P_VALIDATE IN BOOLEAN DEFAULT FALSE
876 ,P_TRANSACTION_STEP_ID IN NUMBER
877 )
878 IS
879 l_proc varchar2(100) := g_package||'UPDATE_DATA';
880 l_effective_date date;
881 l_effective_start_date date default null;
882 l_effective_end_date date default null;
883 l_cost_allocation_keyflex_id PAY_COST_ALLOCATION_KEYFLEX.COST_ALLOCATION_KEYFLEX_ID%TYPE;
884 l_object_version_number PAY_COST_ALLOCATIONS_F.OBJECT_VERSION_NUMBER%TYPE;
885 l_combination_name PAY_COST_ALLOCATION_KEYFLEX.CONCATENATED_SEGMENTS%TYPE;
886 l_cost_allocation_id PAY_COST_ALLOCATIONS_F.COST_ALLOCATION_ID%TYPE;
887 l_login_person_id HR_API_TRANSACTIONS.CREATOR_PERSON_ID%TYPE;
888 l_update_datetrack_mode VARCHAR2(20);
889 BEGIN
890
891 if g_debug then
892 hr_utility.set_location('Entering '||l_proc, 10);
893 end if;
894
895 SAVEPOINT UPDATE_DATA;
896 if p_validate = false then
897 l_effective_date := hr_transaction_api.get_date_value(p_transaction_step_id,'P_EFFECTIVE_DATE');
898 if l_effective_date is null then
899 l_effective_date := sysdate;
900 end if;
901 if g_debug then
902 hr_utility.set_location('Get P_COST_ALLOCATION_KEYFLEX_ID and P_OBJECT_VERSION_NUMBER', 20);
903 end if;
904 l_cost_allocation_keyflex_id := hr_transaction_api.get_number_value
905 (p_transaction_step_id => p_transaction_step_id
906 ,p_name => 'P_COST_ALLOCATION_KEYFLEX_ID');
907 l_object_version_number := hr_transaction_api.get_number_value
908 (p_transaction_step_id => p_transaction_step_id
909 ,p_name => 'P_OBJECT_VERSION_NUMBER');
910 l_update_datetrack_mode := hr_transaction_api.get_varchar2_value(
911 p_transaction_step_id => p_transaction_step_id,
912 p_name => 'P_UPDATE_MODE');
913 if g_debug then
914 hr_utility.set_location('P_COST_ALLOCATION_KEYFLEX_ID => '||NVL(l_cost_allocation_keyflex_id,-1), 30);
918
915 hr_utility.set_location('P_OBJECT_VERSION_NUMBER => '||NVL(l_object_version_number,-1), 40);
916 end if;
917 --
919 if g_debug then
920 hr_utility.set_location('Calling PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION', 50);
921 end if;
922
923 BEGIN
924 PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION
925 (p_validate => p_validate
926 ,p_effective_date => l_effective_date
927 ,p_datetrack_update_mode => nvl(l_update_datetrack_mode,'UPDATE')
928 ,p_cost_allocation_id => hr_transaction_api.get_number_value
929 (p_transaction_step_id => p_transaction_step_id
930 ,p_name => 'P_COST_ALLOCATION_ID')
931 ,p_object_version_number => l_object_version_number
932 ,p_proportion => hr_transaction_api.get_number_value
933 (p_transaction_step_id => p_transaction_step_id
934 ,p_name => 'P_PROPORTION')/100
935 ,p_segment1 => hr_transaction_api.get_varchar2_value
936 (p_transaction_step_id => p_transaction_step_id
937 ,p_name => 'P_SEGMENT1')
938 ,p_segment2 => hr_transaction_api.get_varchar2_value
939 (p_transaction_step_id => p_transaction_step_id
940 ,p_name => 'P_SEGMENT2')
941 ,p_segment3 => hr_transaction_api.get_varchar2_value
942 (p_transaction_step_id => p_transaction_step_id
943 ,p_name => 'P_SEGMENT3')
944 ,p_segment4 => hr_transaction_api.get_varchar2_value
945 (p_transaction_step_id => p_transaction_step_id
946 ,p_name => 'P_SEGMENT4')
947 ,p_segment5 => hr_transaction_api.get_varchar2_value
948 (p_transaction_step_id => p_transaction_step_id
949 ,p_name => 'P_SEGMENT5')
950 ,p_segment6 => hr_transaction_api.get_varchar2_value
951 (p_transaction_step_id => p_transaction_step_id
952 ,p_name => 'P_SEGMENT6')
953 ,p_segment7 => hr_transaction_api.get_varchar2_value
954 (p_transaction_step_id => p_transaction_step_id
955 ,p_name => 'P_SEGMENT7')
956 ,p_segment8 => hr_transaction_api.get_varchar2_value
957 (p_transaction_step_id => p_transaction_step_id
958 ,p_name => 'P_SEGMENT8')
959 ,p_segment9 => hr_transaction_api.get_varchar2_value
960 (p_transaction_step_id => p_transaction_step_id
961 ,p_name => 'P_SEGMENT9')
962 ,p_segment10 => hr_transaction_api.get_varchar2_value
963 (p_transaction_step_id => p_transaction_step_id
964 ,p_name => 'P_SEGMENT10')
965 ,p_segment11 => hr_transaction_api.get_varchar2_value
966 (p_transaction_step_id => p_transaction_step_id
967 ,p_name => 'P_SEGMENT11')
968 ,p_segment12 => hr_transaction_api.get_varchar2_value
969 (p_transaction_step_id => p_transaction_step_id
970 ,p_name => 'P_SEGMENT12')
971 ,p_segment13 => hr_transaction_api.get_varchar2_value
972 (p_transaction_step_id => p_transaction_step_id
973 ,p_name => 'P_SEGMENT13')
974 ,p_segment14 => hr_transaction_api.get_varchar2_value
975 (p_transaction_step_id => p_transaction_step_id
976 ,p_name => 'P_SEGMENT14')
977 ,p_segment15 => hr_transaction_api.get_varchar2_value
978 (p_transaction_step_id => p_transaction_step_id
979 ,p_name => 'P_SEGMENT15')
980 ,p_segment16 => hr_transaction_api.get_varchar2_value
981 (p_transaction_step_id => p_transaction_step_id
982 ,p_name => 'P_SEGMENT16')
983 ,p_segment17 => hr_transaction_api.get_varchar2_value
984 (p_transaction_step_id => p_transaction_step_id
985 ,p_name => 'P_SEGMENT17')
986 ,p_segment18 => hr_transaction_api.get_varchar2_value
987 (p_transaction_step_id => p_transaction_step_id
988 ,p_name => 'P_SEGMENT18')
989 ,p_segment19 => hr_transaction_api.get_varchar2_value
990 (p_transaction_step_id => p_transaction_step_id
991 ,p_name => 'P_SEGMENT19')
992 ,p_segment20 => hr_transaction_api.get_varchar2_value
993 (p_transaction_step_id => p_transaction_step_id
994 ,p_name => 'P_SEGMENT20')
995 ,p_segment21 => hr_transaction_api.get_varchar2_value
996 (p_transaction_step_id => p_transaction_step_id
997 ,p_name => 'P_SEGMENT21')
998 ,p_segment22 => hr_transaction_api.get_varchar2_value
999 (p_transaction_step_id => p_transaction_step_id
1000 ,p_name => 'P_SEGMENT22')
1001 ,p_segment23 => hr_transaction_api.get_varchar2_value
1002 (p_transaction_step_id => p_transaction_step_id
1003 ,p_name => 'P_SEGMENT23')
1007 ,p_segment25 => hr_transaction_api.get_varchar2_value
1004 ,p_segment24 => hr_transaction_api.get_varchar2_value
1005 (p_transaction_step_id => p_transaction_step_id
1006 ,p_name => 'P_SEGMENT24')
1008 (p_transaction_step_id => p_transaction_step_id
1009 ,p_name => 'P_SEGMENT25')
1010 ,p_segment26 => hr_transaction_api.get_varchar2_value
1011 (p_transaction_step_id => p_transaction_step_id
1012 ,p_name => 'P_SEGMENT26')
1013 ,p_segment27 => hr_transaction_api.get_varchar2_value
1014 (p_transaction_step_id => p_transaction_step_id
1015 ,p_name => 'P_SEGMENT27')
1016 ,p_segment28 => hr_transaction_api.get_varchar2_value
1017 (p_transaction_step_id => p_transaction_step_id
1018 ,p_name => 'P_SEGMENT28')
1019 ,p_segment29 => hr_transaction_api.get_varchar2_value
1020 (p_transaction_step_id => p_transaction_step_id
1021 ,p_name => 'P_SEGMENT29')
1022 ,p_segment30 => hr_transaction_api.get_varchar2_value
1023 (p_transaction_step_id => p_transaction_step_id
1024 ,p_name => 'P_SEGMENT30')
1025 ,p_combination_name => l_combination_name
1026 ,p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id
1027 ,p_effective_start_date => l_effective_start_date
1028 ,p_effective_end_date => l_effective_end_date);
1029 end;
1030 if g_debug then
1031 hr_utility.set_location('After Calling PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION', 60);
1032 end if;
1033 else
1034 hr_utility.set_location('p_validate is true...so nothing to do',120);
1035 end if;
1036 if g_debug then
1037 hr_utility.set_location('Leaving '||l_proc,1000);
1038 end if;
1039 EXCEPTION
1040 WHEN hr_utility.hr_error THEN
1041 -- -----------------------------------------------------------------
1042 -- An application error has been raised by the API so we must set
1043 -- the error.
1044 -- -----------------------------------------------------------------
1045 hr_utility.set_location('Exception:hr_utility.hr_error THEN'||l_proc,555);
1046 hr_utility.set_location('Rolling back the data',666);
1047 ROLLBACK TO UPDATE_DATA;
1048 hr_utility.set_location('Leaving '||l_proc,1000);
1049 RAISE;
1050 WHEN OTHERS THEN
1051 hr_utility.set_location('Unknown error occurred....Rolling back the data',777);
1052 ROLLBACK TO UPDATE_DATA;
1053 hr_utility.set_location('Leaving '||l_proc,1000);
1054 END UPDATE_DATA;
1055
1056 PROCEDURE DELETE_ASSG_COST(
1057 P_ITEM_TYPE IN VARCHAR2
1058 ,P_ITEM_KEY IN VARCHAR2
1059 ,P_ACTID IN NUMBER
1060 ,P_LOGIN_PERSON_ID IN NUMBER
1061 ,P_DELETE_MODE IN VARCHAR2 DEFAULT 'DELETE'
1062 ,P_EFFECTIVE_DATE IN DATE
1063 ,P_ASSIGNMENT_ID IN NUMBER
1064 ,P_BUSINESS_GROUP_ID IN NUMBER
1065 ,P_COST_ALLOCATION_ID IN NUMBER
1066 ,P_OBJECT_VERSION_NUMBER IN NUMBER
1067 ,P_CONCATENATED_SEGMENTS IN VARCHAR2 DEFAULT NULL
1068 ,P_EFFECTIVE_START_DATE IN DATE DEFAULT NULL
1069 ,P_EFFECTIVE_END_DATE IN DATE DEFAULT NULL
1070 ,P_TRANSACTION_ID OUT NOCOPY NUMBER
1071 ,P_TRANSACTION_STEP_ID OUT NOCOPY NUMBER
1072 ) is
1073 l_proc varchar2(100) := g_package||'DELETE_ASSG_COST';
1074
1075 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
1076 l_transaction_step_id HR_API_TRANSACTION_STEPS.TRANSACTION_STEP_ID%TYPE;
1077 l_trs_object_version_number HR_API_TRANSACTION_STEPS.OBJECT_VERSION_NUMBER%TYPE;
1078 l_transaction_table HR_TRANSACTION_SS.TRANSACTION_TABLE;
1079 l_count NUMBER;
1080 l_result VARCHAR2(100);
1081 L_EFFECTIVE_DATE DATE;
1082 BEGIN
1083 IF g_debug THEN
1084 hr_utility.set_location('Entering '||l_proc, 10);
1085 hr_utility.set_location('Calling hr_transaction_ss.create_transaction_step', 100);
1086 END IF;
1087
1088 IF P_EFFECTIVE_DATE IS NULL THEN
1089 L_EFFECTIVE_DATE := SYSDATE;
1090 ELSE
1091 L_EFFECTIVE_DATE := P_EFFECTIVE_DATE;
1092 END IF;
1093
1094 /*This CREATE_TRANSACTION_STEP() find out of any transaction created with
1095 the given ITEM TYPE and ITEM KEY. If exists then it creates transaction
1096 step if not it creates a transactions and transaction step as well.*/
1097
1098 hr_transaction_ss.create_transaction_step(
1099 p_item_type => p_item_type
1100 ,p_item_key => p_item_key
1101 ,p_actid => 0 --p_actid
1102 ,p_login_person_id => p_login_person_id
1103 ,p_api_name => g_package||'DELETE_DATA'
1104 ,p_transaction_step_id => l_transaction_step_id
1105 ,p_object_version_number => l_trs_object_version_number);
1106 IF g_debug THEN
1107 hr_utility.set_location('After Calling HR_TRANSACTION_SS.SAVE_TRANSACTION_STEP', 130);
1108 hr_utility.set_location('Leaving '||l_proc, 1000);
1109 END IF;
1110
1111 /*Populating the table structure HR_TRANSACTION_SS.TRANSACTION_TABLE with
1112 the user specified values. This data will be used to populate the trasaction
1113 tables and subsequently into the base tables.
1117 l_transaction_table(l_count).param_value := p_item_type;
1114 */
1115 l_count := 1;
1116 l_transaction_table(l_count).param_name := 'P_ITEM_TYPE';
1118 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1119
1120 --
1121 l_count := l_count + 1;
1122 l_transaction_table(l_count).param_name := 'P_ITEM_KEY';
1123 l_transaction_table(l_count).param_value := p_item_key;
1124 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1125 --
1126 l_count := l_count + 1;
1127 l_transaction_table(l_count).param_name := 'P_ACTIVITY_ID';
1128 l_transaction_table(l_count).param_value := p_actid;
1129 l_transaction_table(l_count).param_data_type := 'NUMBER';
1130 --
1131 l_count := l_count + 1;
1132 l_transaction_table(l_count).param_name := 'P_LOGIN_PERSON_ID';
1133 l_transaction_table(l_count).param_value := P_LOGIN_PERSON_ID;
1134 l_transaction_table(l_count).param_data_type := 'NUMBER';
1135 --
1136 l_count := l_count + 1;
1137 l_transaction_table(l_count).param_name := 'P_DELETE_MODE';
1138 l_transaction_table(l_count).param_value := P_DELETE_MODE;
1139 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
1140 --
1141 l_count := l_count + 1;
1142 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
1143 l_transaction_table(l_count).param_value := to_char(l_effective_date,
1144 hr_transaction_ss.g_date_format);
1145 l_transaction_table(l_count).param_data_type := 'DATE';
1146 --
1147 l_count := l_count + 1;
1148 l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
1149 l_transaction_table(l_count).param_value := P_OBJECT_VERSION_NUMBER;
1150 l_transaction_table(l_count).param_data_type := 'NUMBER';
1151 --
1152 l_count := l_count + 1;
1153 l_transaction_table(l_count).param_name := 'P_ASSIGNMENT_ID';
1154 l_transaction_table(l_count).param_value := P_ASSIGNMENT_ID;
1155 l_transaction_table(l_count).param_data_type := 'NUMBER';
1156 --
1157 l_count := l_count + 1;
1158 l_transaction_table(l_count).param_name := 'P_COST_ALLOCATION_ID';
1159 l_transaction_table(l_count).param_value := P_COST_ALLOCATION_ID;
1160 l_transaction_table(l_count).param_data_type := 'NUMBER';
1161 --
1162 l_count := l_count + 1;
1163 l_transaction_table(l_count).param_name := 'P_BUSINESS_GROUP_ID';
1164 l_transaction_table(l_count).param_value := P_BUSINESS_GROUP_ID;
1165 l_transaction_table(l_count).param_data_type := 'NUMBER';
1166 --
1167 l_count := l_count + 1;
1168 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_START_DATE';
1169 if P_EFFECTIVE_START_DATE IS NULL THEN
1170 l_transaction_table(l_count).param_value := NULL;
1171 else
1172 l_transaction_table(l_count).param_value := to_char(P_EFFECTIVE_START_DATE,
1173 hr_transaction_ss.g_date_format);
1174 end if;
1175 l_transaction_table(l_count).param_data_type := 'DATE';
1176 --
1177 l_count := l_count + 1;
1178 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_END_DATE';
1179 if P_EFFECTIVE_END_DATE IS NULL THEN
1180 l_transaction_table(l_count).param_value := NULL;
1181 else
1182 l_transaction_table(l_count).param_value := to_char(P_EFFECTIVE_END_DATE,
1183 hr_transaction_ss.g_date_format);
1184 end if;
1185 l_transaction_table(l_count).param_data_type := 'DATE';
1186 /*Save data into transaction tables*/
1187 if g_debug then
1188 hr_utility.set_location('Calling HR_TRANSACTION_SS.SAVE_TRANSACTION_STEP', 110);
1189 end if;
1190 hr_transaction_ss.save_transaction_step
1191 (p_item_type => p_item_type
1192 ,p_item_key => p_item_key
1193 ,p_actid => 0 --p_actid
1194 ,p_login_person_id => p_login_person_id
1195 ,p_transaction_step_id => l_transaction_step_id
1196 ,p_api_name => g_package || 'DELETE_DATA'
1197 ,p_transaction_data => l_transaction_table);
1198
1199 p_transaction_id := l_transaction_id;
1200 p_transaction_step_id := l_transaction_step_id;
1201 END DELETE_ASSG_COST;
1202
1203 PROCEDURE DELETE_DATA(
1204 P_VALIDATE IN BOOLEAN DEFAULT FALSE
1205 ,P_TRANSACTION_STEP_ID IN NUMBER
1206 )
1207 IS
1208 l_proc varchar2(100) := g_package||'DELETE_DATA';
1209 l_effective_date date;
1210 l_effective_start_date date;
1211 l_effective_end_date date;
1212 l_object_version_number PAY_COST_ALLOCATIONS_F.OBJECT_VERSION_NUMBER%TYPE;
1213 l_combination_name PAY_COST_ALLOCATION_KEYFLEX.CONCATENATED_SEGMENTS%TYPE;
1214 l_cost_allocation_id PAY_COST_ALLOCATIONS_F.COST_ALLOCATION_ID%TYPE;
1215 l_login_person_id HR_API_TRANSACTIONS.CREATOR_PERSON_ID%TYPE;
1216 l_datetrack_delete_mode VARCHAR2(20);
1217
1218 BEGIN
1219 IF g_debug THEN
1220 hr_utility.set_location('Entering '||l_proc,10);
1221 END IF;
1222 /*Create save point before starting database operation*/
1223 SAVEPOINT create_date;
1224 if p_validate = false then
1225 --
1226 l_effective_date := hr_transaction_api.get_date_value(p_transaction_step_id,'P_EFFECTIVE_DATE');
1227 if l_effective_date is null then
1228 l_effective_date := sysdate;
1229 end if;
1230
1231 if g_debug then
1232 hr_utility.set_location('Get P_COST_ALLOCATION_KEYFLEX_ID and P_OBJECT_VERSION_NUMBER', 20);
1233 end if;
1234
1235 /*Get the values used to call the API*/
1236 l_cost_allocation_id := hr_transaction_api.get_number_value
1237 (p_transaction_step_id => p_transaction_step_id
1238 ,p_name => 'P_COST_ALLOCATION_ID');
1239 l_object_version_number := hr_transaction_api.get_number_value
1240 (p_transaction_step_id => p_transaction_step_id
1241 ,p_name => 'P_OBJECT_VERSION_NUMBER');
1242 l_datetrack_delete_mode := hr_transaction_api.get_varchar2_value
1243 (p_transaction_step_id => p_transaction_step_id
1244 ,p_name => 'P_DELETE_MODE');
1245
1246 /*Now Call the API to delete the cost allocations.*/
1247 PAY_COST_ALLOCATION_API.DELETE_COST_ALLOCATION
1248 (p_validate => P_VALIDATE
1249 ,p_effective_date => l_effective_date
1250 ,p_datetrack_delete_mode => nvl(l_datetrack_delete_mode,'DELETE')
1251 ,p_cost_allocation_id => hr_transaction_api.get_number_value
1252 (p_transaction_step_id => p_transaction_step_id
1253 ,p_name => 'P_COST_ALLOCATION_ID')
1254 ,p_object_version_number => l_object_version_number
1255 ,p_effective_start_date => l_effective_start_date
1256 ,p_effective_end_date => l_effective_end_date);
1257
1258 if g_debug then
1259 hr_utility.set_location('After Calling PAY_COST_ALLOCATION_API.UPDATE_COST_ALLOCATION', 60);
1260 end if;
1261 end if;
1262 IF g_debug THEN
1263 hr_utility.set_location('Leaving '||l_proc,10);
1264 END IF;
1265 END DELETE_DATA;
1266
1267 END PAY_ASSG_COST_SS;