[Home] [Help]
PACKAGE BODY: APPS.PAY_BEE_SS
Source
1 PACKAGE BODY PAY_BEE_SS as
2 /* $Header: pybeess.pkb 120.0.12020000.2 2013/03/22 09:02:09 bklingam noship $ */
3
4 g_package varchar2(33) := 'PAY_BEE_SS.';
5 g_debug boolean := hr_utility.debug_enabled;
6 g_data_error exception;
7 g_item_key number;
8 g_block_id number;
9
10 procedure create_batch(p_batch_name in varchar2 default null
11 ,p_effective_date in varchar2 default null
12 ,p_action_if_exists in varchar2 default null
13 ,p_date_effective_changes in varchar2 default null
14 ,p_purge_after_transfer in varchar2 default null
15 ,p_reject_if_future_changes in varchar2 default null
16 ,p_item_key in varchar2
17 ,p_activity_id in varchar2
18 ,p_login_person_id in number
19 ,p_business_group_id in number
20 ,p_batch_status_flag in varchar2 default'Unprocessed'
21
22 ) is
23
24 l_transaction_id number default null;
25 l_transaction_step_id number default null;
26 l_trans_obj_vers_num number default null;
27 l_count integer default 0;
28 l_transaction_table hr_transaction_ss.transaction_table;
29 l_result varchar2(50);
30 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
31 l_api_names hr_util_web.g_varchar2_tab_type;
32 l_trans_step_rows NUMBER ;
33 l_proc varchar2(72) := g_package||'create_batch';
34 l_comments varchar2(100) := hr_api.g_varchar2;
35 p_batch_id number;
36 p_item_type varchar2(50) := 'HRSSA';
37 -- p_item_key number;
38 p_actid number :=100;
39 -- p_login_person_id number := 125;
40
41 begin
42
43 g_item_key :=p_item_key;
44 p_batch_id :=0;
45
46 -- Start a transaction
47
48 l_transaction_id := hr_transaction_ss.get_transaction_id
49 (p_item_type => p_item_type
50 ,p_item_key => p_item_key);
51
52
53 -- -- -- bee_trace('item key==' ||p_item_key);
54 -- l_transaction_id := null;
55
56 --
57 IF l_transaction_id is null THEN
58 -- Start a Transaction
59 hr_utility.set_location('l_transaction_id is null THEN:'||l_proc,105);
60 hr_transaction_ss.start_transaction
61 (itemtype => p_item_type
62 ,itemkey => p_item_key
63 ,actid => p_actid
64 ,funmode => 'RUN'
65 ,p_login_person_id => p_login_person_id --nvl(p_login_person_id, p_parent_id)
66 ,result => l_result);
67
68 l_transaction_id := hr_transaction_ss.get_transaction_id
69 (p_item_type => p_item_type
70 ,p_item_key => p_item_key);
71 END IF;
72 ------------------
73
74 -- First check step id already exists, which happens when user navigates
75 -- back from review or page after this page.
76 --
77 hr_transaction_api.get_transaction_step_info
78 (p_item_type => p_item_type
79 ,p_item_key => p_item_key
80 ,p_activity_id => p_actid
81 ,p_transaction_step_id => l_trans_step_ids
82 ,p_api_name => l_api_names
83 ,p_rows => l_trans_step_rows);
84 --
85
86
87
88 if l_transaction_step_id is null then
89 --
90 -- Create a transaction step
91 --
92 hr_utility.set_location('l_transaction_step_id is null then:'||l_proc,120);
93 hr_transaction_api.create_transaction_step
94 (p_validate => false
95 ,p_creator_person_id => p_login_person_id
96 --nvl(p_login_person_id, p_parent_id)
97 ,p_transaction_id => l_transaction_id
98 ,p_api_name => g_package || 'PROCESS_DUMMY_API'
99 ,p_item_type => p_item_type
100 ,p_item_key => p_item_key
101 ,p_activity_id => p_actid
102 ,p_transaction_step_id => l_transaction_step_id
103 ,p_object_version_number => l_trans_obj_vers_num);
104 --
105 end if;
106
107 l_count := 1;
108 l_transaction_table(l_count).param_name := 'P_ITEM_TYPE';
109 l_transaction_table(l_count).param_value := p_item_type;
110 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
111 --
112 l_count := l_count + 1;
113 l_transaction_table(l_count).param_name := 'P_ITEM_KEY';
114 l_transaction_table(l_count).param_value := p_item_key;
115 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
116 --
117
118 l_count := l_count + 1;
119 l_transaction_table(l_count).param_name := 'P_BATCH_NAME';
120 l_transaction_table(l_count).param_value := p_batch_name;
121 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
122
123 l_count := l_count + 1;
124 l_transaction_table(l_count).param_name := 'P_BATCH_STATUS_FLAG';
125 l_transaction_table(l_count).param_value := p_batch_status_flag;
126 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
127
128
129 l_count := l_count + 1;
130 l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
131 l_transaction_table(l_count).param_value := 'PayBatchElementEntry';
132 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
133
134 l_count := l_count + 1;
135 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
136 l_transaction_table(l_count).param_value := p_activity_id;
137 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
138
139
140 l_count := l_count + 1;
141 l_transaction_table(l_count).param_name := 'P_ACTION_IF_EXISTS';
142 l_transaction_table(l_count).param_value := p_action_if_exists;
143 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
144
145
146
147 l_count := l_count + 1;
148 l_transaction_table(l_count).param_name := 'P_DATE_EFFECTIVE_CHANGES';
149 l_transaction_table(l_count).param_value := p_date_effective_changes;
150 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
151
152 l_count := l_count + 1;
153 l_transaction_table(l_count).param_name := 'P_PURGE_AFTER_TRANSFER';
154 l_transaction_table(l_count).param_value := p_purge_after_transfer;
155 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
156
157 l_count := l_count + 1;
158 l_transaction_table(l_count).param_name := 'P_REJECT_IF_FUTURE_CHANGES';
159 l_transaction_table(l_count).param_value := p_reject_if_future_changes;
160 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
161
162 -- -- -- bee_trace('Date in create batch '||TO_CHAR(to_date(p_effective_date),'RRRR/MM/DD'));
163
164 -- -- -- bee_trace('Date in create batch with to_date '||to_date(p_effective_date));
165 l_count := l_count + 1;
166 l_transaction_table(l_count).param_name := 'P_BATCH_ID';
167 l_transaction_table(l_count).param_value := p_batch_id;
168 l_transaction_table(l_count).param_data_type := 'NUMBER';
169
170 l_count := l_count + 1;
171 l_transaction_table(l_count).param_name := 'P_BUSINESS_GROUP_ID';
172 l_transaction_table(l_count).param_value := p_business_group_id;
173 l_transaction_table(l_count).param_data_type := 'NUMBER';
174
175 l_count := l_count + 1;
176 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
177 l_transaction_table(l_count).param_value := TO_CHAR(to_date(p_effective_date),'RRRR/MM/DD');
178 l_transaction_table(l_count).param_data_type := 'DATE';
179
180
181
182 hr_transaction_ss.save_transaction_step
183 (p_item_type => p_item_type
184 ,p_item_key => p_item_key
185 ,p_actid => p_actid
186 ,p_login_person_id => p_login_person_id
187 ,p_transaction_step_id => l_transaction_step_id
188 ,p_api_name => g_package || 'PROCESS_API'
189 ,p_transaction_data => l_transaction_table);
190
191 EXCEPTION
192 WHEN g_data_error THEN
193 hr_utility.set_location('Exception:g_data_error THEN'||l_proc,560);
194 -- -- bee_trace('Exception in create bach '||sqlerrm);
195 -- p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
196 -- p_error_message => p_error_message);
197 WHEN others THEN
198 hr_utility.set_location('Exception:Others'||l_proc,555);
199 -- -- -- bee_trace(sqlerrm);
200 -- This should be included as an out param for the procedure "create_person".
201 -- Along with this change include also the appropriate changes in the java that calls this api.
202 --
203 -- p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
204 -- p_error_message => p_error_message);
205
206 END create_batch;
207
208
209
210 procedure create_batch_assignment_lines(p_element_name in varchar2,
211 p_element_type in varchar2,
212 p_effective_date in varchar2,
213 p_batch_name in varchar2,
214 p_employee_name in varchar2,
215 p_assignment_no in varchar2,
216 p_iterator in varchar2,
217 p_costing_id in varchar2,
218 p_delete_flag in varchar2 default 'N',
219 p_assignment_id in varchar2
220 ,p_login_person_id in number
221 ) as
222
223
224 l_transaction_id number default null;
225 l_transaction_step_id number default null;
226 l_trans_obj_vers_num number default null;
227 l_count integer default 0;
228 l_transaction_table hr_transaction_ss.transaction_table;
229 l_result varchar2(50);
230 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
231 l_api_names hr_util_web.g_varchar2_tab_type;
232 l_trans_step_rows NUMBER ;
233 l_proc varchar2(72) := g_package||'create_batch_assignment_lines';
234 l_comments varchar2(100) := hr_api.g_varchar2;
235 p_item_type varchar2(50) := 'HRSSA';
236 p_item_key number := g_item_key;
237 p_actid number :=101;
238 -- p_login_person_id number := 125;
239 l_flag number;
240
241 begin
242 -- -- -- bee_trace('Exception:Others 12'||l_proc);
243 l_transaction_id := hr_transaction_ss.get_transaction_id
244 (p_item_type => p_item_type
245 ,p_item_key => p_item_key);
246 -- -- -- bee_trace('Exception:Others 13'||l_proc);
247
248 hr_transaction_api.get_transaction_step_info
249 (p_item_type => p_item_type
250 ,p_item_key => p_item_key
251 ,p_activity_id => p_actid
252 ,p_transaction_step_id => l_trans_step_ids
253 ,p_api_name => l_api_names
254 ,p_rows => l_trans_step_rows);
255 --
256
257 -- -- -- bee_trace('Exception:Others 14'||l_proc);
258
259 FOR i in 0..l_trans_step_rows-1 LOOP
260 IF(l_api_names(i) = 'PAY_BEE_SS.PROCESS_API') THEN
261 l_transaction_step_id := l_trans_step_ids(i);
262 END IF;
263 END LOOP;
264
265 if l_transaction_step_id is null then
266 --
267 -- Create a transaction step
268 --
269 hr_utility.set_location('l_transaction_step_id is null then:'||l_proc,120);
270 -- -- -- bee_trace('l_transaction_step_id is null');
271
272 hr_transaction_api.create_transaction_step
273 (p_validate => false
274 ,p_creator_person_id => p_login_person_id
275 --nvl(p_login_person_id, p_parent_id)
276 ,p_transaction_id => l_transaction_id
277 ,p_api_name => g_package || 'PROCESS_API'
278 ,p_item_type => p_item_type
279 ,p_item_key => p_item_key
280 ,p_activity_id => p_actid
281 ,p_transaction_step_id => l_transaction_step_id
282 ,p_object_version_number => l_trans_obj_vers_num);
283 --
284 end if;
285
286 -- -- -- bee_trace('Exception:Others 111'||l_proc);
287
288
289 l_count := 0;
290 -- -- -- bee_trace('Entered procedure 123');
291 select count(*) into l_flag from hr_api_transaction_values where name = p_assignment_no||'@P_EMPLOYEE_NAME' and transaction_step_id = l_transaction_step_id;
292
293 -- -- bee_trace('Entered procedure 123');
294 if (l_flag = 0) then
295 l_count := l_count + 1;
296 l_transaction_table(l_count).param_name := p_assignment_no||'@P_EMPLOYEE_NAME';
297 l_transaction_table(l_count).param_value := p_employee_name;
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_assignment_no||'@P_ASSIGNMENT_NUMBER';
302 l_transaction_table(l_count).param_value := p_assignment_no;
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_BATCH_TYPE';
307 l_transaction_table(l_count).param_value := 'BatchByAssignment';
308 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
309
310 l_count := l_count + 1;
311 l_transaction_table(l_count).param_name := p_assignment_no||'@P_ASSIGNMENT_ID';
312 l_transaction_table(l_count).param_value := to_number(p_assignment_id);
313 l_transaction_table(l_count).param_data_type := 'NUMBER';
314
315 end if;
316
317 l_count := l_count + 1;
318 l_transaction_table(l_count).param_name := p_assignment_no||'@P_ELEMENT_NAME_'||p_iterator;
319 l_transaction_table(l_count).param_value := p_element_name;
320 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
321 --
322
323 -- -- -- bee_trace('Exception:Others 222'||l_proc);
324 l_count := l_count + 1;
325 l_transaction_table(l_count).param_name := p_assignment_no||'@P_ELEMENT_TYPE_'||p_iterator;
326 l_transaction_table(l_count).param_value := p_element_type;
327 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
328
329
330
331
332
333 -- -- -- bee_trace('Exception:Others 444'||l_proc);
334 l_count := l_count + 1;
335 l_transaction_table(l_count).param_name := p_assignment_no||'@P_COSTING_ID_'||p_iterator;
336 l_transaction_table(l_count).param_value := p_costing_id;
337 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
338
339 -- -- -- bee_trace('Exception:Others 555'||l_proc);
340 l_count := l_count + 1;
341 l_transaction_table(l_count).param_name := p_assignment_no||'@P_DELETE_FLAG_'||p_iterator;
342 l_transaction_table(l_count).param_value := p_delete_flag;
343 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
344
345
346
347
348
349 -- -- -- bee_trace('Exception:Others 333'||l_proc);
350 l_count := l_count + 1;
351 l_transaction_table(l_count).param_name := p_assignment_no||'@P_EFFECTIVE_DATE_'||p_iterator;
352 -- -- -- bee_trace(p_effective_date);
353 l_transaction_table(l_count).param_value := TO_CHAR(to_date(p_effective_date,'YYYY-MM-DD'),'RRRR/MM/DD');
354 l_transaction_table(l_count).param_data_type := 'DATE';
355
356
357 hr_transaction_ss.save_transaction_step
358 (p_item_type => p_item_type
359 ,p_item_key => p_item_key
360 ,p_actid => p_actid
361 ,p_login_person_id => p_login_person_id
362 ,p_transaction_step_id => l_transaction_step_id
363 ,p_api_name => g_package || 'PROCESS_API'
364 ,p_transaction_data => l_transaction_table);
365
366 -- -- -- bee_trace('Exception:Others 16'||l_proc);
367
368
369
370 END create_batch_assignment_lines;
371
372 -- new method create_batch_element_lines start
373 procedure create_batch_element_lines(p_element_name in varchar2,
374 p_element_type in varchar2,
375 p_effective_date in varchar2,
376 p_batch_name in varchar2,
377 p_employee_name in varchar2,
378 p_assignment_no in varchar2,
379 p_iterator in varchar2,
380 p_costing_id in varchar2,
381 p_element_id in varchar2,
382 p_delete_flag in varchar2 default 'N'
383 ,p_login_person_id in number
384 ) as
385
386
387 l_transaction_id number default null;
388 l_transaction_step_id number default null;
389 l_trans_obj_vers_num number default null;
390 l_count integer default 0;
391 l_transaction_table hr_transaction_ss.transaction_table;
392 l_result varchar2(50);
393 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
394 l_api_names hr_util_web.g_varchar2_tab_type;
395 l_trans_step_rows NUMBER ;
396 l_proc varchar2(72) := g_package||'create_batch_element_lines';
397 l_comments varchar2(100) := hr_api.g_varchar2;
398 p_item_type varchar2(50) := 'HRSSA';
399 p_item_key number := g_item_key;
400 p_actid number :=102;
401 -- p_login_person_id number := 125;
402 l_flag number;
403
404 begin
405 ---- -- bee_trace('Exception:Others 12'||l_proc);
406 l_transaction_id := hr_transaction_ss.get_transaction_id
407 (p_item_type => p_item_type
408 ,p_item_key => p_item_key);
409 ---- -- bee_trace('Exception:Others 13'||l_proc);
410
411 UPDATE hr_api_transaction_values
412 SET varchar2_value ='Unprocessed'
413 WHERE transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id =l_transaction_id)
414 and name = 'P_BATCH_STATUS_FLAG';
415
416 hr_transaction_api.get_transaction_step_info
417 (p_item_type => p_item_type
418 ,p_item_key => p_item_key
419 ,p_activity_id => p_actid
420 ,p_transaction_step_id => l_trans_step_ids
421 ,p_api_name => l_api_names
422 ,p_rows => l_trans_step_rows);
423 --
424
425 ---- -- bee_trace('Exception:Others 14'||l_proc);
426
427 FOR i in 0..l_trans_step_rows-1 LOOP
428 IF(l_api_names(i) = 'PAY_BEE_SS.PROCESS_API') THEN
429 l_transaction_step_id := l_trans_step_ids(i);
430 END IF;
431 END LOOP;
432
433 if l_transaction_step_id is null then
434 --
435 -- Create a transaction step
436 --
437 hr_utility.set_location('l_transaction_step_id is null then:'||l_proc,120);
438 ---- -- bee_trace('l_transaction_step_id is null');
439
440 hr_transaction_api.create_transaction_step
441 (p_validate => false
442 ,p_creator_person_id => p_login_person_id
443 --nvl(p_login_person_id, p_parent_id)
444 ,p_transaction_id => l_transaction_id
445 ,p_api_name => g_package || 'PROCESS_API'
446 ,p_item_type => p_item_type
447 ,p_item_key => p_item_key
448 ,p_activity_id => p_actid
449 ,p_transaction_step_id => l_transaction_step_id
450 ,p_object_version_number => l_trans_obj_vers_num);
451 --
452 end if;
453
454 ---- -- bee_trace('Exception:Others 111'||l_proc);
455
456
457 l_count := 0;
458
459 select count(*) into l_flag from hr_api_transaction_values where name = 'P_BATCH_TYPE' and transaction_step_id = l_transaction_step_id;
460
461 if (l_flag = 0) then
462 ---- -- bee_trace('Exception:Others 000'||l_proc);
463 l_count := l_count + 1;
464 l_transaction_table(l_count).param_name :='P_BATCH_TYPE';
465 l_transaction_table(l_count).param_value := 'BatchByElement';
466 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
467
468 end if;
469
470 l_count := l_count + 1;
471 l_transaction_table(l_count).param_name := 'P_EMPLOYEE_NAME_'||p_iterator;
472 l_transaction_table(l_count).param_value := p_employee_name;
473 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
474
475 l_count := l_count + 1;
479
476 l_transaction_table(l_count).param_name := 'P_ASSIGNMENT_NUMBER_'||p_iterator;
477 l_transaction_table(l_count).param_value := p_assignment_no;
478 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
480
481 l_count := l_count + 1;
482 l_transaction_table(l_count).param_name := 'P_ELEMENT_NAME_'||p_iterator;
483 l_transaction_table(l_count).param_value := p_element_name;
484 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
485
486
487 ---- -- bee_trace('Exception:Others 222'||l_proc);
488 l_count := l_count + 1;
489 l_transaction_table(l_count).param_name := 'P_ELEMENT_TYPE_'||p_iterator;
490 l_transaction_table(l_count).param_value := p_element_type;
491 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
492
493 ---- -- bee_trace('Exception:Others 444'||l_proc);
494 l_count := l_count + 1;
495 l_transaction_table(l_count).param_name := 'P_COSTING_ID_'||p_iterator;
496 l_transaction_table(l_count).param_value := p_costing_id;
497 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
498
499 ---- -- bee_trace('Exception:Others 444'||l_proc);
500 l_count := l_count + 1;
501 l_transaction_table(l_count).param_name := 'P_ELEMENT_ID_'||p_iterator;
502 l_transaction_table(l_count).param_value := p_element_id;
503 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
504
505
506 -- -- -- bee_trace('Exception:Others 555'||l_proc);
507 l_count := l_count + 1;
508 l_transaction_table(l_count).param_name := 'P_DELETE_FLAG_'||p_iterator;
509 l_transaction_table(l_count).param_value := p_delete_flag;
510 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
511
512 -- -- -- bee_trace('Exception:Others 333'||l_proc);
513 l_count := l_count + 1;
514 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE_'||p_iterator;
515 -- -- bee_trace(p_effective_date);
516 l_transaction_table(l_count).param_value := TO_CHAR(to_date(p_effective_date,'YYYY-MM-DD'),'RRRR/MM/DD');
517 l_transaction_table(l_count).param_data_type := 'DATE';
518
519 ---- -- bee_trace('Exception:Others 15'||l_proc);
520 hr_transaction_ss.save_transaction_step
521 (p_item_type => p_item_type
522 ,p_item_key => p_item_key
523 ,p_actid => p_actid
524 ,p_login_person_id => p_login_person_id
525 ,p_transaction_step_id => l_transaction_step_id
526 ,p_api_name => g_package || 'PROCESS_API'
527 ,p_transaction_data => l_transaction_table);
528
529 ---- -- bee_trace('Exception:Others 25'||l_proc);
530
531
532 END create_batch_element_lines;
533
534 -- new method create_batch_element_lines end
535
536
537 procedure set_input_values(p_input_value_name in varchar2,
538 p_input_value in varchar2,
539 p_iterator in varchar2,
540 p_element_name in varchar2,
541 p_assignment_no in varchar2
542 ,p_login_person_id in number ) is
543
544 l_transaction_id number default null;
545 l_transaction_step_id number default null;
546 l_trans_obj_vers_num number default null;
547 l_count integer default 0;
548 l_transaction_table hr_transaction_ss.transaction_table;
549 l_result varchar2(50);
550 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
551 l_api_names hr_util_web.g_varchar2_tab_type;
552 l_trans_step_rows NUMBER ;
553 l_proc varchar2(200) := g_package||'set_input_values';
554 l_comments varchar2(100) := hr_api.g_varchar2;
555 p_item_type varchar2(50) := 'HRSSA';
556 p_item_key number := g_item_key;
557 p_actid number :=101;
558 -- p_login_person_id number := 125;
559 l_flag number;
560 l_element_type_id number;
561 l_input_value_id number;
562
563 begin
564 l_transaction_id := hr_transaction_ss.get_transaction_id
565 (p_item_type => p_item_type
566 ,p_item_key => p_item_key);
567 -- -- -- bee_trace('Exception:Others 13'||l_proc);
568
569 hr_transaction_api.get_transaction_step_info
570 (p_item_type => p_item_type
571 ,p_item_key => p_item_key
572 ,p_activity_id => p_actid
573 ,p_transaction_step_id => l_trans_step_ids
574 ,p_api_name => l_api_names
575 ,p_rows => l_trans_step_rows);
576 --
577
578 -- -- -- bee_trace('Exception:Others 14'||l_proc);
579
580 FOR i in 0..l_trans_step_rows-1 LOOP
581 IF(l_api_names(i) = 'PAY_BEE_SS.PROCESS_API') THEN
582 l_transaction_step_id := l_trans_step_ids(i);
583 END IF;
584 END LOOP;
585
586 if l_transaction_step_id is null then
587 --
588 -- Create a transaction step
589 --
590 hr_utility.set_location('l_transaction_step_id is null then:'||l_proc,120);
591 -- -- -- bee_trace('l_transaction_step_id is null');
592
593 hr_transaction_api.create_transaction_step
594 (p_validate => false
595 ,p_creator_person_id => p_login_person_id
596 --nvl(p_login_person_id, p_parent_id)
597 ,p_transaction_id => l_transaction_id
598 ,p_api_name => g_package || 'PROCESS_API'
599 ,p_item_type => p_item_type
600 ,p_item_key => p_item_key
601 ,p_activity_id => p_actid
605 end if;
602 ,p_transaction_step_id => l_transaction_step_id
603 ,p_object_version_number => l_trans_obj_vers_num);
604 --
606
607
608 select ELEMENT_TYPE_ID into l_element_type_id
609 from pay_element_types_f
610 where ELEMENT_NAME = p_element_name;
611
612 select INPUT_VALUE_ID into l_input_value_id
613 from pay_input_values_f
614 where ELEMENT_TYPE_ID = l_element_type_id
615 and NAME = p_input_value_name;
616
617
618
619 -- -- -- bee_trace('Exception:Others 15'||l_proc);
620 l_count := 1;
621 l_transaction_table(l_count).param_name := p_assignment_no||'@'||l_element_type_id||'_'||l_input_value_id;
622 l_transaction_table(l_count).param_value := to_char(p_input_value);
623 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
624
625 -- -- -- bee_trace('Exception:Others 16'||to_char(p_input_value));
626
627 hr_transaction_ss.save_transaction_step
628 (p_item_type => p_item_type
629 ,p_item_key => p_item_key
630 ,p_actid => p_actid
631 ,p_login_person_id => p_login_person_id
632 ,p_transaction_step_id => l_transaction_step_id
633 ,p_api_name => g_package || 'PROCESS_API'
634 ,p_transaction_data => l_transaction_table);
635
636
637 END set_input_values;
638
639 -- procedure set_element_input_values start
640 procedure set_element_input_values(p_input_value_name in varchar2,
641 p_input_value in varchar2,
642 p_iterator in varchar2
643 ,p_login_person_id in number ) is
644
645 l_transaction_id number default null;
646 l_transaction_step_id number default null;
647 l_trans_obj_vers_num number default null;
648 l_count integer default 0;
649 l_transaction_table hr_transaction_ss.transaction_table;
650 l_result varchar2(50);
651 l_trans_step_ids hr_util_web.g_varchar2_tab_type;
652 l_api_names hr_util_web.g_varchar2_tab_type;
653 l_trans_step_rows NUMBER ;
654 l_proc varchar2(200) := g_package||'set_element_input_values';
655 l_comments varchar2(100) := hr_api.g_varchar2;
656 p_item_type varchar2(50) := 'HRSSA';
657 p_item_key number := g_item_key;
658 p_actid number :=103;
659 -- p_login_person_id number := 125;
660 l_flag number;
661 l_element_type_id number;
662 l_input_value_id number;
663
664 begin
665 l_transaction_id := hr_transaction_ss.get_transaction_id
666 (p_item_type => p_item_type
667 ,p_item_key => p_item_key);
668 -- -- -- bee_trace('Exception:Others 13'||l_proc);
669
670 hr_transaction_api.get_transaction_step_info
671 (p_item_type => p_item_type
672 ,p_item_key => p_item_key
673 ,p_activity_id => p_actid
674 ,p_transaction_step_id => l_trans_step_ids
675 ,p_api_name => l_api_names
676 ,p_rows => l_trans_step_rows);
677 --
678
679 -- -- -- bee_trace('Exception:Others 14'||l_proc);
680
681 FOR i in 0..l_trans_step_rows-1 LOOP
682 IF(l_api_names(i) = 'PAY_BEE_SS.PROCESS_API') THEN
683 l_transaction_step_id := l_trans_step_ids(i);
684 END IF;
685 END LOOP;
686
687 if l_transaction_step_id is null then
688 --
689 -- Create a transaction step
690 --
691 hr_utility.set_location('l_transaction_step_id is null then:'||l_proc,120);
692 -- -- -- bee_trace('l_transaction_step_id is null');
693
694 hr_transaction_api.create_transaction_step
695 (p_validate => false
696 ,p_creator_person_id => p_login_person_id
697 --nvl(p_login_person_id, p_parent_id)
698 ,p_transaction_id => l_transaction_id
699 ,p_api_name => g_package || 'PROCESS_API'
700 ,p_item_type => p_item_type
701 ,p_item_key => p_item_key
702 ,p_activity_id => p_actid
703 ,p_transaction_step_id => l_transaction_step_id
704 ,p_object_version_number => l_trans_obj_vers_num);
705 --
706 end if;
707
708
709 /* select ELEMENT_TYPE_ID into l_element_type_id
710 from pay_element_types_f
711 where ELEMENT_NAME = p_element_name;
712
713 select INPUT_VALUE_ID into l_input_value_id
714 from pay_input_values_f
715 where ELEMENT_TYPE_ID = l_element_type_id
716 and NAME = p_input_value_name; */
717
718
719
720 -- -- -- bee_trace('Exception:Others 15'||l_proc);
721 l_count := 1;
722 l_transaction_table(l_count).param_name := p_input_value_name;
723 l_transaction_table(l_count).param_value := to_char(p_input_value);
724 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
725
726 -- -- -- bee_trace('Exception:Others 16'||to_char(p_input_value));
727
728 hr_transaction_ss.save_transaction_step
729 (p_item_type => p_item_type
730 ,p_item_key => p_item_key
731 ,p_actid => p_actid
732 ,p_login_person_id => p_login_person_id
733 ,p_transaction_step_id => l_transaction_step_id
734 ,p_api_name => g_package || 'PROCESS_API'
735 ,p_transaction_data => l_transaction_table);
736
737
738
739 END set_element_input_values;
740 -- procedure set_element_input_values end
741
742
743
744 function get_item_key return number is
745 l_item_key number;
746 begin
750
747 return g_item_key;
748
749 end get_item_key;
751 procedure set_item_key(p_item_key in number) is
752
753 begin
754
755 -- -- -- bee_trace('Setting item key1');
756 g_item_key := p_item_key;
757 -- -- -- bee_trace('After1 Setting item key1');
758 end set_item_key;
759
760 function get_transaction_id_batch_name(p_batch_name in varchar2) return number is
761 l_transaction_id number;
762 begin
763 select transaction_id into l_transaction_id from hr_api_transaction_steps where transaction_step_id in (select TRANSACTION_STEP_ID from hr_api_transaction_values where name='P_BATCH_NAME' and VARCHAR2_VALUE=p_batch_name);
764 return l_transaction_id;
765 end get_transaction_id_batch_name;
766
767 procedure update_delete_flag(p_transaction_id in number,p_name in varchar2) is
768 --pragma autonomous_transaction;
769 begin
770 -- -- -- bee_trace('Name is '||p_name);
771 -- -- -- bee_trace('Transaction Id is '||p_transaction_id);
772 update hr_api_transaction_values set varchar2_value='Y' where name=p_name and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=p_transaction_id);
773 --commit;
774 end update_delete_flag;
775
776
777 procedure update_batch_header_table(p_batch_name in varchar2,
778 p_business_group_id in number) is
779
780 --pragma autonomous_transaction;
781 l_transaction_id number;
782 l_transaction_step_id number;
783 l_effective_date date;
784 l_action_if_exists varchar2(30);
785 l_date_effective_changes varchar2(30);
786 l_purge_after_transfer varchar2(30);
787 l_reject_if_future_changes varchar2(30);
788 l_batch_id number;
789 begin
790
791 -- -- -- bee_trace('Name is '||p_batch_name);
792
793 select pay_batch_headers_s.nextval into l_batch_id from dual;
794
795 select transaction_step_id into l_transaction_step_id
796 from hr_api_transaction_values
797 where name = 'P_BATCH_NAME'
798 and varchar2_value = p_batch_name;
799
800 l_effective_date:=hr_transaction_api.get_date_value
801 (p_transaction_step_id => l_transaction_step_id
802 ,p_name => 'P_EFFECTIVE_DATE');
803
804 l_action_if_exists:=hr_transaction_api.get_varchar2_value
805 (p_transaction_step_id => l_transaction_step_id
806 ,p_name => 'P_ACTION_IF_EXISTS');
807
808 l_date_effective_changes:=hr_transaction_api.get_varchar2_value
809 (p_transaction_step_id => l_transaction_step_id
810 ,p_name => 'P_DATE_EFFECTIVE_CHANGES');
811
812 l_purge_after_transfer:=hr_transaction_api.get_varchar2_value
813 (p_transaction_step_id => l_transaction_step_id
814 ,p_name => 'P_PURGE_AFTER_TRANSFER');
815
816 l_reject_if_future_changes:=hr_transaction_api.get_varchar2_value
817 (p_transaction_step_id => l_transaction_step_id
818 ,p_name => 'P_REJECT_IF_FUTURE_CHANGES');
819
820 insert into pay_batch_headers(BATCH_ID,BUSINESS_GROUP_ID,BATCH_NAME,batch_status,ACTION_IF_EXISTS,PURGE_AFTER_TRANSFER,REJECT_IF_FUTURE_CHANGES,DATE_EFFECTIVE_CHANGES)
821 values(l_batch_id,p_business_group_id,p_batch_name,'U',l_action_if_exists,l_purge_after_transfer,l_reject_if_future_changes,l_date_effective_changes);
822
823 update hr_api_transaction_values set number_value=l_batch_id
824 where name='P_BATCH_ID'
825 and transaction_step_id=l_transaction_step_id;
826
827 commit;
828 end update_batch_header_table;
829
830 procedure update_batch_lines_table(p_batch_name in varchar2,
831 p_business_group_id in number) is
832
833 --pragma autonomous_transaction;
834
835 l_transaction_id number;
836 l_assignment_id number;
837 l_transaction_step_id number;
838 l_transaction_step_id_1 number;
839 l_assignment_number varchar2(30);
840 l_element_name varchar2(100);
841 l_element_type varchar2(100);
842 l_element_type_id number;
843 l_effective_date date;
844 l_costing_flex_id number;
845 l_batch_id number;
846 l_batch_line_id number;
847 l_object_version_number number;
848 l_temp1 varchar2(50);
849 l_temp2 varchar2(50);
850 l_temp3 varchar2(50);
851 l_temp4 varchar2(50);
852 l_temp5 varchar2(50);
853 l_temp6 varchar2(50);
854 l_temp7 varchar2(50);
855 l_temp8 varchar2(50);
856 l_temp9 varchar2(50);
857 l_temp10 varchar2(50);
858 l_batch_sequence number :=1;
859 type input_values_array is varray(15) of varchar2(50);
860 input_values input_values_array := input_values_array(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
861 l_input_value_attribute varchar2(50);
862 l_input_value_id number;
863 l_count number :=1;
864 l_request_id number;
865
866 cursor asg_cur is
867 select varchar2_value from hr_api_transaction_values
868 where name like '%P_ASSIGNMENT_NUMBER%'
869 and transaction_step_id=l_transaction_step_id;
870
871 cursor asg_entries_cur is
872 select max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp1, VARCHAR2_VALUE, null)),
873 max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp2, VARCHAR2_VALUE, null)),
874 max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp3, DATE_VALUE, null)),
875 max(decode(substr(name,1,instr(name,'@'))||regexp_replace(substr(name,instr(name,'@')+1),'[0-9]',''),l_temp4, VARCHAR2_VALUE, null))
876 from hr_api_transaction_values
877 where (name like l_temp5
878 or name like l_temp6
882 group by regexp_replace(substr(name,instr(name,'@')+1), '[A-Z]','')
879 or name like l_temp7
880 or name like l_temp8)
881 AND TRANSACTION_STEP_ID in (select TRANSACTION_STEP_ID from hr_api_transaction_steps where TRANSACTION_ID=l_transaction_id)
883 order by regexp_replace(substr(name,instr(name,'@')+1), '[A-Z]','');
884
885 cursor input_values_cur is
886 select input_value_id from pay_input_values_f
887 where element_type_id=l_element_type_id
888 order by display_sequence;
889
890 begin
891
892 select transaction_id into l_transaction_id
893 from hr_api_transaction_steps
894 where transaction_step_id = (select transaction_step_id
895 from hr_api_transaction_values
896 where name = 'P_BATCH_NAME'
897 and varchar2_value = p_batch_name);
898
899 select distinct transaction_step_id into l_transaction_step_id
900 from hr_api_transaction_values
901 where name like '%P_EMPLOYEE_NAME%'
902 and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);
903
904 select batch_id into l_batch_id from pay_batch_headers
905 where batch_name = p_batch_name;
906
907
908
909 open asg_cur;
910 loop
911 fetch asg_cur into l_assignment_number;
912 exit when asg_cur%notfound;
913 -- dbms_output.put_line('l_assignment_number Name is '||l_assignment_number);
914
915 l_temp1 := l_assignment_number||'@P_ELEMENT_NAME_';
916 l_temp2 := l_assignment_number||'@P_ELEMENT_TYPE_';
917 l_temp3 := l_assignment_number||'@P_EFFECTIVE_DATE_';
918 l_temp4 := l_assignment_number||'@P_COSTING_ID_';
919 l_temp5 := l_assignment_number||'@P_ELEMENT_NAME_%';
920 l_temp6 := l_assignment_number||'@P_ELEMENT_TYPE_%';
921 l_temp7 := l_assignment_number||'@P_EFFECTIVE_DATE_%';
922 l_temp8 := l_assignment_number||'@P_COSTING_ID_%';
923
924 open asg_entries_cur;
925 loop
926 fetch asg_entries_cur into l_element_name,l_element_type,l_effective_date,l_costing_flex_id;
927 exit when asg_entries_cur%notfound;
928 -- dbms_output.put_line('Element Name is '||l_element_name);
929
930 select element_type_id into l_element_type_id from pay_element_types_f
931 where element_name = l_element_name
932 and business_group_id=p_business_group_id;
933
934
935 l_temp9 := l_assignment_number||'@'||l_element_type_id||'_%';
936
937 l_temp10 := l_assignment_number||'@P_ASSIGNMENT_ID';
938
939 l_assignment_id:=hr_transaction_api.get_number_value(p_transaction_step_id => l_transaction_step_id,p_name=>l_temp10);
940
941 /* select distinct transaction_step_id into l_transaction_step_id_1
942 from hr_api_transaction_values
943 where name like l_temp9
944 and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id); */
945
946
947
948 for l_count in 1..15 loop
949 input_values(l_count):=null;
950 end loop;
951
952 l_count :=1;
953 open input_values_cur;
954 loop
955 fetch input_values_cur into l_input_value_id;
956 exit when input_values_cur%notfound;
957 l_input_value_attribute := l_assignment_number||'@'||l_element_type_id||'_'||l_input_value_id;
958 input_values(l_count) := hr_transaction_api.get_varchar2_value(p_transaction_step_id =>l_transaction_step_id,p_name=>l_input_value_attribute);
959 l_count := l_count+1;
960 end loop;
961 close input_values_cur;
962 /* pay_batch_element_entry_api.create_batch_line(p_session_date => sysdate,
963 p_batch_id => l_batch_id,
964 p_assignment_number => l_assignment_number,
965 p_cost_allocation_keyflex_id => l_costing_flex_id,
966 p_effective_date => l_effective_date,
967 p_element_name => l_element_name,
968 p_element_type_id => l_element_type_id,
969 p_assignment_id => l_assignment_id,
970 p_value_1 => input_values(1)
971 ,p_value_2 => input_values(2)
972 ,p_value_3 => input_values(3)
973 ,p_value_4 => input_values(4)
974 ,p_value_5 => input_values(5)
975 ,p_value_6 => input_values(6)
976 ,p_value_7 => input_values(7)
977 ,p_value_8 => input_values(8)
978 ,p_value_9 => input_values(9)
979 ,p_value_10 => input_values(10)
980 ,p_value_11 => input_values(11)
981 ,p_value_12 => input_values(12)
982 ,p_value_13 => input_values(13)
983 ,p_value_14 => input_values(14)
984 ,p_value_15 => input_values(15),
985 p_batch_line_id => l_batch_line_id,
986 p_object_version_number => l_object_version_number); */
987 insert into pay_batch_lines (BATCH_LINE_ID,
988 COST_ALLOCATION_KEYFLEX_ID,
992 BATCH_LINE_STATUS,
989 ELEMENT_TYPE_ID,
990 ASSIGNMENT_ID,
991 BATCH_ID,
993 ASSIGNMENT_NUMBER,
994 BATCH_SEQUENCE,
995 EFFECTIVE_DATE,
996 ELEMENT_NAME,
997 ENTRY_TYPE,
998 VALUE_1,
999 VALUE_2,
1000 VALUE_3,
1001 VALUE_4,
1002 VALUE_5,
1003 VALUE_6,
1004 VALUE_7,
1005 VALUE_8,
1006 VALUE_9,
1007 VALUE_10,
1008 VALUE_11,
1009 VALUE_12,
1010 VALUE_13,
1011 VALUE_14,
1012 VALUE_15,
1013 OBJECT_VERSION_NUMBER)
1014 values(pay_batch_lines_s.nextval,
1015 l_costing_flex_id,
1016 l_element_type_id,
1017 l_assignment_id,
1018 l_batch_id,
1019 'U',
1020 l_assignment_number,
1021 l_batch_sequence,
1022 l_effective_date,
1023 l_element_name,
1024 'E',
1025 input_values(1),
1026 input_values(2),
1027 input_values(3),
1028 input_values(4),
1029 input_values(5),
1030 input_values(6),
1031 input_values(7),
1032 input_values(8),
1033 input_values(9),
1034 input_values(10),
1035 input_values(11),
1036 input_values(12),
1037 input_values(13),
1038 input_values(14),
1039 input_values(15),
1040 1);
1041 l_batch_sequence :=l_batch_sequence+1;
1042
1043 end loop;
1044 close asg_entries_cur;
1045 end loop;
1046 close asg_cur;
1047
1048
1049 --commit;
1050
1051
1052
1053 end update_batch_lines_table;
1054
1055 procedure validate_batch(errbuf OUT nocopy VARCHAR2, retcode OUT nocopy NUMBER,p_batch_name in varchar2,p_business_group_id in number) is
1056 l_request_id number;
1057 l_batch_id number;
1058 l_phase_code varchar2(10);
1059 l_batch_line_id number;
1060 l_transaction_id number;
1061 l_source_id number;
1062 l_batch_id_1 number;
1063 l_source_type_meaning varchar2(4000);
1064 l_line_text varchar2(4000);
1065 l_message_level varchar2(200);
1066 l_assignment_number varchar2(200);
1067 l_element_name varchar2(200);
1068 l_source_type varchar2(100);
1069 l_action_information_id number;
1070 l_ovn number;
1071 l_batch_status varchar2(100);
1072 l_validation_count number :=0;
1073
1074
1075 cursor batch_lines_cur is
1076 select batch_line_id from pay_batch_lines where batch_id=l_batch_id;
1077
1078 cursor batch_validation_messages_cur is
1079 SELECT source_type_meaning
1080 , line_text
1081 , message_level
1082 , assignment_number
1083 , element_name
1084 , batch_id
1085 , source_type
1086 , source_id
1087 FROM pay_paywsqee_messages
1088 where batch_id = l_batch_id;
1089
1090 begin
1091
1092 l_transaction_id := get_transaction_id_batch_name(p_batch_name=>p_batch_name);
1093 retcode :=0;
1094 -- bee_trace('Entered validate batch');
1095 select batch_id into l_batch_id from pay_batch_headers
1096 where batch_name = p_batch_name;
1097
1098 -- bee_trace('Batch id is '||l_batch_id);
1099 l_request_id := pay_paywsqee_pkg.paylink_request_id(p_business_group_id=>p_business_group_id,
1100 p_mode => 'VALIDATE'
1101 ,p_batch_id =>l_batch_id);
1102
1103 -- bee_trace('Request id for validation is '||l_request_id);
1104 -- bee_trace('Request id is ' || l_request_id );
1105 select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;
1106
1107 -- bee_trace('Initial phase cose is '||l_phase_code);
1108
1109 --if l_phase_code='P' then
1110 --UPDATE hr_api_transaction_values
1111 --SET varchar2_value ='Processing'
1112 --WHERE transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id =l_transaction_id)
1113 --and name = 'P_BATCH_STATUS_FLAG';
1114 --end if;
1115 while (l_phase_code<>'C')
1116 loop
1117 dbms_lock.sleep(5);
1118 select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;
1119 -- bee_trace('Phase code is '||l_phase_code);
1120 end loop;
1121
1122 open batch_validation_messages_cur;
1123 loop
1124
1125 fetch batch_validation_messages_cur into l_source_type_meaning, l_line_text, l_message_level, l_assignment_number, l_element_name, l_batch_id_1, l_source_type, l_source_id;
1126 exit when batch_validation_messages_cur%notfound;
1127 pay_action_information_api.create_action_information
1128 (p_action_context_id => l_batch_id_1
1129 ,p_action_context_type => 'BEE'
1133 ,p_action_information2 => l_line_text
1130 ,p_action_information_category => 'PAY_BEE_SS'
1131 ,p_source_id => l_source_id
1132 ,p_action_information1 => l_source_type_meaning
1134 ,p_action_information3 => l_message_level
1135 ,p_action_information4 => l_assignment_number
1136 ,p_action_information5 => l_element_name
1137 ,p_action_information6 => l_source_type
1138 ,p_action_information_id => l_action_information_id
1139 ,p_object_version_number => l_ovn
1140 );
1141 l_validation_count := l_validation_count+1;
1142 end loop;
1143
1144 if l_validation_count=0 then
1145 UPDATE hr_api_transaction_values
1146 SET varchar2_value ='Valid'
1147 WHERE transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id =l_transaction_id)
1148 and name = 'P_BATCH_STATUS_FLAG';
1149 elsif l_validation_count <> 0 then
1150 UPDATE hr_api_transaction_values
1151 SET varchar2_value ='Validation Failed'
1152 WHERE transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id =l_transaction_id)
1153 and name = 'P_BATCH_STATUS_FLAG';
1154 end if;
1155
1156 -- bee_trace('Deleting batch');
1157
1158 open batch_lines_cur;
1159 loop
1160 fetch batch_lines_cur into l_batch_line_id;
1161
1162 -- bee_trace('Batch line id is '||l_batch_line_id);
1163 exit when batch_lines_cur%notfound;
1164 -- bee_trace('Deleting batch line');
1165 --pay_batch_element_entry_api.delete_batch_line(p_batch_line_id=>l_batch_line_id,p_object_version_number=>1);
1166
1167 delete from pay_batch_lines where batch_line_id=l_batch_line_id;
1168 end loop;
1169
1170 close batch_lines_cur;
1171
1172 -- -- -- bee_trace('Deleting batch header');
1173
1174 --pay_batch_element_entry_api.delete_batch_header(p_batch_id=>l_batch_id,p_object_version_number=>1);
1175 SELECT batch_status into l_batch_status
1176 FROM pay_batch_headers
1177 WHERE batch_name =p_batch_name;
1178 --if l_validation_count=0 then
1179 --UPDATE hr_api_transaction_values
1180 --SET varchar2_value ='Valid'
1181 --WHERE transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id =l_transaction_id)
1182 --and name = 'P_BATCH_STATUS_FLAG';
1183 --elsif l_validation_count <> 0 then
1184 --UPDATE hr_api_transaction_values
1185 --SET varchar2_value ='Validation Failed'
1186 --WHERE transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id =l_transaction_id)
1187 --and name = 'P_BATCH_STATUS_FLAG';
1188 --end if;
1189
1190
1191
1192 delete from pay_batch_headers where batch_id=l_batch_id;
1193
1194
1195 --commit;
1196
1197
1198
1199 end validate_batch;
1200 --batch element entries
1201
1202 procedure call_validate_batch(p_batch_name in varchar2,p_business_group_id in number) is
1203 l_request_id number;
1204 l_transaction_id number;
1205
1206 begin
1207 l_transaction_id := get_transaction_id_batch_name(p_batch_name=>p_batch_name);
1208
1209 UPDATE hr_api_transaction_values
1210 SET varchar2_value ='Processing'
1211 WHERE transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id =l_transaction_id)
1212 and name = 'P_BATCH_STATUS_FLAG';
1213
1214 -- -- bee_trace('Entered call validate batch');
1215 l_request_id := fnd_request.submit_request (
1216 --
1217 'PAY',
1218 'PAYBEESSVALIDATE',
1219 null,
1220 null,
1221 null,
1222 p_batch_name,
1223 p_business_group_id);
1224
1225 --commit;
1226
1227 -- bee_trace('Request id for our validation is '||l_request_id);
1228
1229 end call_validate_batch;
1230
1231 procedure update_element_lines_table(p_batch_name in varchar2,
1232 p_business_group_id in number
1233 ) is
1234
1235 --pragma autonomous_transaction;
1236
1237 l_transaction_id number;
1238 l_assignment_id number;
1239 l_transaction_step_id number;
1240 l_transaction_step_id_1 number;
1241 l_assignment_number varchar2(30);
1242 l_element_name varchar2(100);
1243 l_element_type varchar2(100);
1244 l_element_type_id number;
1245 l_effective_date date;
1246 l_costing_flex_id number;
1247 l_batch_id number;
1248 l_batch_line_id number;
1249 l_object_version_number number;
1250 l_temp1 varchar2(50);
1251 l_temp2 varchar2(50);
1252 l_temp3 varchar2(50);
1253 l_temp4 varchar2(50);
1254 l_temp5 varchar2(50);
1255 l_temp6 varchar2(50);
1256 l_temp7 varchar2(50);
1257 l_temp8 varchar2(50);
1258 l_temp9 varchar2(50);
1259 l_temp10 varchar2(50);
1260 type input_values_array is varray(15) of varchar2(50);
1261 input_values input_values_array := input_values_array(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
1262 l_input_value_attribute varchar2(50);
1263 l_input_value_id number;
1264 l_count number :=1;
1265 l_request_id number;
1266 l_business_group_id number;
1267 l_batch_sequence number :=1;
1268
1269
1270
1271
1272
1273 cursor asg_cur is
1274 select varchar2_value from hr_api_transaction_values
1275 where name like '%P_ASSIGNMENT_NUMBER%'
1276 and transaction_step_id=l_transaction_step_id;
1277
1281 max(decode(substr(name,1,15),'P_ELEMENT_TYPE_', VARCHAR2_VALUE, null)),
1278
1279 cursor asg_entries_cur is
1280 select max(decode(substr(name,1,15),'P_ELEMENT_NAME_', VARCHAR2_VALUE, null)),
1282 max(decode(substr(name,1,17),'P_EFFECTIVE_DATE_', DATE_VALUE, null)),
1283 max(decode(substr(name,1,13),'P_COSTING_ID_', VARCHAR2_VALUE, null)),
1284 max(decode(substr(name,1,13),'P_ELEMENT_ID_', VARCHAR2_VALUE, null)),
1285 max(decode(substr(name,1,20),'P_ASSIGNMENT_NUMBER_', VARCHAR2_VALUE, null))
1286 from hr_api_transaction_values
1287 where (name like 'P_ELEMENT_NAME_%'
1288 or name like 'P_ELEMENT_TYPE_%'
1289 or name like 'P_EFFECTIVE_DATE_%'
1290 or name like 'P_COSTING_ID_%'
1291 or name like 'P_ELEMENT_ID_%'
1292 or name like 'P_ASSIGNMENT_NUMBER_%')
1293 AND TRANSACTION_STEP_ID in (select TRANSACTION_STEP_ID from hr_api_transaction_steps where TRANSACTION_ID=l_transaction_id)
1294 group by regexp_replace(name, '[A-Z]','')
1295 order by regexp_replace(name, '[A-Z]','');
1296 cursor input_values_cur is
1297 select input_value_id from pay_input_values_f
1298 where element_type_id=l_element_type_id
1299 order by display_sequence;
1300
1301 begin
1302
1303 select transaction_id into l_transaction_id
1304 from hr_api_transaction_steps
1305 where transaction_step_id = (select transaction_step_id
1306 from hr_api_transaction_values
1307 where name = 'P_BATCH_NAME'
1308 and varchar2_value = p_batch_name);
1309
1310 -- bee_trace('bharath1'||l_transaction_id);
1311 select number_value into l_business_group_id
1312 from hr_api_transaction_values
1313 where name = 'P_BUSINESS_GROUP_ID'
1314 and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);
1315
1316 select distinct transaction_step_id into l_transaction_step_id
1317 from hr_api_transaction_values
1318 where name like 'P_EMPLOYEE_NAME%'
1319 and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);
1320
1321 delete from hr_api_transaction_values where transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id)
1322 and name like 'P_EFFECTIVE_DATE_OPTION';
1323
1324 -- bee_trace('bharath2'||l_transaction_step_id);
1325 -- bee_trace('p_batch_name'||p_batch_name);
1326
1327 select batch_id into l_batch_id from pay_batch_headers
1328 where batch_name = p_batch_name;
1329 -- bee_trace('bharath3'||l_batch_id);
1330
1331 open asg_entries_cur;
1332 loop
1333
1334
1335 fetch asg_entries_cur into l_element_name,l_element_type,l_effective_date,l_costing_flex_id,l_element_type_id,l_assignment_number;
1336 exit when asg_entries_cur%notfound;
1337 -- dbms_output.put_line('Element Name is '||l_element_name);
1338
1339
1340 SELECT assignment_id into l_assignment_id
1341 FROM per_all_assignments_f paf
1342 WHERE SYSDATE
1343 BETWEEN paf.effective_start_date
1344 AND paf.effective_end_date
1345 and paf.business_group_id=l_business_group_id
1346 and paf.assignment_number=l_assignment_number;
1347
1348 -- bee_trace('l_assignment_id'||l_assignment_id);
1349
1350 l_temp9 := l_assignment_number||'@'||l_element_type_id||'_%';
1351
1352 -- bee_trace('l_temp9'||l_temp9);
1353 select distinct transaction_step_id into l_transaction_step_id_1
1354 from hr_api_transaction_values
1355 where name like '%@%'
1356 and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);
1357
1358 -- bee_trace('l_transaction_step_id_1'||l_transaction_step_id_1);
1359
1360 for l_count in 1..15 loop
1361 input_values(l_count):=null;
1362 end loop;
1363
1364 l_count :=1;
1365 open input_values_cur;
1366 loop
1367 fetch input_values_cur into l_input_value_id;
1368 exit when input_values_cur%notfound;
1369 l_input_value_attribute := l_assignment_number||'@'||l_element_type_id||'_'||l_input_value_id;
1370
1371
1372 -- bee_trace('l_input_value_attribute'||l_input_value_attribute);
1373 -- bee_trace('stepid is'||l_transaction_step_id_1);
1374 input_values(l_count) := hr_transaction_api.get_varchar2_value(p_transaction_step_id =>l_transaction_step_id_1,p_name=>l_input_value_attribute);
1375 -- bee_trace('inputvalues are ---'||input_values(l_count));
1376 l_count := l_count+1;
1377 end loop;
1378 close input_values_cur;
1379
1380
1381 /* pay_batch_element_entry_api.create_batch_line(p_session_date => sysdate,
1382 p_batch_id => l_batch_id,
1383 p_assignment_number => l_assignment_number,
1384 p_cost_allocation_keyflex_id => l_costing_flex_id,
1385 p_effective_date => l_effective_date,
1386 p_element_name => l_element_name,
1387 p_element_type_id => l_element_type_id,
1388 p_assignment_id => l_assignment_id,
1389 p_value_1 => input_values(1)
1393 ,p_value_5 => input_values(5)
1390 ,p_value_2 => input_values(2)
1391 ,p_value_3 => input_values(3)
1392 ,p_value_4 => input_values(4)
1394 ,p_value_6 => input_values(6)
1395 ,p_value_7 => input_values(7)
1396 ,p_value_8 => input_values(8)
1397 ,p_value_9 => input_values(9)
1398 ,p_value_10 => input_values(10)
1399 ,p_value_11 => input_values(11)
1400 ,p_value_12 => input_values(12)
1401 ,p_value_13 => input_values(13)
1402 ,p_value_14 => input_values(14)
1403 ,p_value_15 => input_values(15),
1404 p_batch_line_id => l_batch_line_id,
1405 p_object_version_number => l_object_version_number); */
1406
1407 insert into pay_batch_lines (BATCH_LINE_ID,
1408 COST_ALLOCATION_KEYFLEX_ID,
1409 ELEMENT_TYPE_ID,
1410 ASSIGNMENT_ID,
1411 BATCH_ID,
1412 BATCH_LINE_STATUS,
1413 ASSIGNMENT_NUMBER,
1414 BATCH_SEQUENCE,
1415 EFFECTIVE_DATE,
1416 ELEMENT_NAME,
1417 ENTRY_TYPE,
1418 VALUE_1,
1419 VALUE_2,
1420 VALUE_3,
1421 VALUE_4,
1422 VALUE_5,
1423 VALUE_6,
1424 VALUE_7,
1425 VALUE_8,
1426 VALUE_9,
1427 VALUE_10,
1428 VALUE_11,
1429 VALUE_12,
1430 VALUE_13,
1431 VALUE_14,
1432 VALUE_15,
1433 OBJECT_VERSION_NUMBER)
1434 values(pay_batch_lines_s.nextval,
1435 l_costing_flex_id,
1436 l_element_type_id,
1437 l_assignment_id,
1438 l_batch_id,
1439 'U',
1440 l_assignment_number,
1441 l_batch_sequence,
1442 l_effective_date,
1443 l_element_name,
1444 'E',
1445 input_values(1),
1446 input_values(2),
1447 input_values(3),
1448 input_values(4),
1449 input_values(5),
1450 input_values(6),
1451 input_values(7),
1452 input_values(8),
1453 input_values(9),
1454 input_values(10),
1455 input_values(11),
1456 input_values(12),
1457 input_values(13),
1458 input_values(14),
1459 input_values(15),
1460 1);
1461 l_batch_sequence :=l_batch_sequence+1;
1462
1463
1464
1465
1466
1467
1468
1469 end loop;
1470 close asg_entries_cur;
1471
1472
1473
1474
1475
1476
1477
1478
1479 end update_element_lines_table;
1480 --batch element entries
1481
1482 --batch element entries
1483
1484 procedure get_previous_page
1485 (itemtype in varchar2
1486 ,itemkey in varchar2
1487 ,actid in number
1488 ,funmode in varchar2
1489 ,result out nocopy varchar2
1490 )
1491 is
1492 --local variables
1493
1494 begin
1495
1496 result := 'COMPLETE:Default';
1497
1498 end get_previous_page;
1499
1500 procedure release_approval_block(p_item_key in varchar2) is
1501 l_actid number;
1502 l_temp varchar2(500);
1503 begin
1504
1505 -- -- -- bee_trace('p item key in node is '||p_item_key);
1506
1507 /* wf_engine.setitemattrtext('HRSSA'
1508 ,p_item_key
1509 ,'TRAN_SUBMIT'
1510 ,'Y');
1511
1512 l_actid :=wf_engine.GetItemAttrNumber('HRSSA',
1513 p_item_key,
1514 'HR_APPROVAL_BLOCK_ID_ATTR',
1515 false);
1516 -- -- -- bee_trace('l actid in node is '||l_actid);
1517 l_temp := wf_engine.getactivitylabel(l_actid);
1518 -- -- -- bee_trace('Act label '||l_temp);
1519
1520 wf_engine.CompleteActivity(
1521 'HRSSA'
1522 , p_item_key
1523 , wf_engine.getactivitylabel(l_actid)
1524 , wf_engine.eng_trans_any) ; */
1525
1526 wf_engine.ResumeProcess(itemtype =>'HRSSA',
1527 itemkey =>p_item_key,
1528 process =>'PAY_BEE_JSP_PRC:HR_NOTIFICATION_PRC');
1529
1530
1531 end release_approval_block;
1532
1533 procedure update_transaction_id(p_item_key in varchar2,p_batch_name in varchar2,p_login_person_id in number) is
1534 l_old_item_key varchar2(4000);
1535 l_old_transaction_id varchar2(4000);
1536 l_transaction_id number default null;
1537 p_item_type varchar2(50) := 'HRSSA';
1538 p_actid number :=100;
1539 --p_login_person_id number ;
1540 l_result varchar2(50);
1541
1542 begin
1543
1544 l_transaction_id := hr_transaction_ss.get_transaction_id
1545 (p_item_type => p_item_type
1546 ,p_item_key => p_item_key);
1547
1548
1549
1550 select transaction_id into l_old_transaction_id
1551 from hr_api_transaction_steps
1552 where transaction_step_id = (select transaction_step_id
1553 from hr_api_transaction_values
1554 where name = 'P_BATCH_NAME'
1555 and varchar2_value = p_batch_name);
1556
1557
1558 --select CREATOR_PERSON_ID into p_login_person_id
1559 --from hr_api_transactions
1560 --where transaction_id=l_old_transaction_id;
1561
1562
1563 -- -- -- bee_trace('item key==' ||p_item_key);
1564 -- l_transaction_id := null;
1565
1566 --
1567 IF l_transaction_id is null THEN
1568 -- Start a Transaction
1569 -- hr_utility.set_location('l_transaction_id is null THEN:'||l_proc,105);
1570 hr_transaction_ss.start_transaction
1571 (itemtype => p_item_type
1572 ,itemkey => p_item_key
1573 ,actid => p_actid
1574 ,funmode => 'RUN'
1575 ,p_login_person_id => p_login_person_id --nvl(p_login_person_id, p_parent_id)
1576 ,result => l_result);
1577
1578 l_transaction_id := hr_transaction_ss.get_transaction_id
1579 (p_item_type => p_item_type
1580 ,p_item_key => p_item_key);
1581 END IF;
1582
1583 --p_transaction_id := l_transaction_id;
1584
1585
1586
1587 select varchar2_value into l_old_item_key
1588 from hr_api_transaction_values
1589 where name = 'P_ITEM_KEY'
1590 and transaction_step_id = (select transaction_step_id
1591 from hr_api_transaction_values
1592 where name = 'P_BATCH_NAME'
1593 and varchar2_value = p_batch_name);
1594
1595
1596
1597 update hr_api_transaction_values set varchar2_value=p_item_key where
1598 name = 'P_ITEM_KEY'
1599 and transaction_step_id = (select transaction_step_id
1600 from hr_api_transaction_values
1601 where name = 'P_BATCH_NAME'
1602 and varchar2_value = p_batch_name);
1603
1604 update hr_api_transaction_steps set transaction_id=l_transaction_id,item_key=p_item_key
1605 where item_type='HRSSA'
1606 and item_key=l_old_item_key
1607 and transaction_id = l_old_transaction_id;
1608
1609
1610 update PQH_SS_TRANS_STATE_HISTORY set transaction_history_id=l_transaction_id
1611 where transaction_history_id=l_old_transaction_id;
1612
1613 update PQH_SS_APPROVAL_HISTORY set transaction_history_id=l_transaction_id
1614 where transaction_history_id=l_old_transaction_id;
1615
1616
1617
1618 end update_transaction_id;
1619
1620 procedure delete_assignment_information(p_batch_name in varchar2,p_assignment_no in varchar2) is
1621
1622 l_transaction_id number;
1623 l_transaction_step_id number;
1624 l_temp varchar2(200);
1625 l_flag number;
1626 begin
1627
1628 l_transaction_id :=get_transaction_id_batch_name(p_batch_name);
1629 l_temp:=p_assignment_no||'@P%';
1630
1631 select count(*) into l_flag
1632 from hr_api_transaction_values
1633 where name = p_assignment_no||'@P_ASSIGNMENT_NUMBER'
1634 and varchar2_value =p_assignment_no
1635 and transaction_step_id in(select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );
1636
1637 -- -- bee_trace('l_flag is ='||l_flag);
1638
1639 if(l_flag>0) then
1640
1641
1642 select transaction_step_id into l_transaction_step_id from hr_api_transaction_values
1643 where name = p_assignment_no||'@P_ASSIGNMENT_NUMBER'
1644 and varchar2_value =p_assignment_no
1645 and transaction_step_id in(select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );
1646
1647 -- -- bee_trace('Transaction step id is '||l_transaction_step_id);
1648
1649 delete from hr_api_transaction_values
1650 where name like l_temp
1651 and transaction_step_id =l_transaction_step_id;
1652
1653 commit;
1654 end if;
1655
1656
1657 end delete_assignment_information;
1658
1659 PROCEDURE process_api
1660 (p_validate in boolean default false
1661 ,p_transaction_step_id in number
1662 ,p_effective_date in varchar2 default null
1663 ) is
1664
1665 l_row_count number;
1666 l_exception_message varchar2(50);
1667 l_batch_name varchar2(50);
1668 l_batch_id number;
1669 l_batch_type varchar2(50);
1670 l_transaction_id number;
1671 l_request_id number;
1672 l_business_group_id number;
1673 l_status_wf varchar2(50);
1674 l_resp_id number ;
1675 l_user_id number;
1676 begin
1677 hr_utility.set_location('Process api',1);
1678 -- bee_trace('Entered process api');
1679 select transaction_id into l_transaction_id
1680 from hr_api_transaction_steps
1681 where transaction_step_id=p_transaction_step_id;
1682
1683 -- bee_trace('Transaction id is '||l_transaction_id);
1684
1685 select varchar2_value into l_batch_type
1686 from hr_api_transaction_values
1687 where name = 'P_BATCH_TYPE'
1688 and transaction_step_id = p_transaction_step_id;
1689
1690 -- bee_trace('Batch type is '||l_batch_type);
1691
1692 select varchar2_value into l_batch_name
1693 from hr_api_transaction_values
1694 where name = 'P_BATCH_NAME'
1695 and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);
1696
1697 select number_value into l_business_group_id
1698 from hr_api_transaction_values
1699 where name = 'P_BUSINESS_GROUP_ID'
1700 and transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id);
1701
1702 select status into l_status_wf
1703 from hr_api_transactions
1704 where transaction_id=l_transaction_id;
1705
1706 -- bee_trace('Batch name is '||l_batch_name);
1707 select created_by into l_user_id
1708 from hr_api_transactions
1709 where transaction_id=l_transaction_id;
1710 select responsibility_id into l_resp_id from fnd_user_resp_groups_direct where user_id=l_user_id and rownum=1;
1711 --bee_trace('l_resp_id is '||l_resp_id);
1712 if (FND_GLOBAL.resp_id= -1) then
1713 fnd_global.apps_initialize(l_user_id,l_resp_id,0);
1714 end if;
1715
1716 if l_status_wf not like 'R%' then
1717 -- bee_trace('l_status_wf name is '||l_status_wf);
1718 if l_batch_type = 'BatchByAssignment' then
1719 -- bee_trace('l_batch_type name is '||l_batch_type);
1720 update_batch_header_table(p_batch_name =>l_batch_name,
1721 p_business_group_id =>l_business_group_id);
1722
1723 update_batch_lines_table(p_batch_name=>l_batch_name,
1724 p_business_group_id =>l_business_group_id);
1725
1726 elsif l_batch_type = 'BatchByElement' then
1727 -- bee_trace('l_batch_type name is '||l_batch_type);
1728 update_batch_header_table(p_batch_name =>l_batch_name,
1729 p_business_group_id =>l_business_group_id);
1730 -- bee_trace('after update header '||l_batch_type);
1731 update_element_lines_table(p_batch_name=>l_batch_name,
1732 p_business_group_id =>l_business_group_id);
1733 -- bee_trace('after element lines '||l_batch_type);
1734 -- bee_trace('Batch name is bharath '||l_batch_name);
1735
1736 end if;
1737
1738 select batch_id into l_batch_id
1739 from pay_batch_headers
1740 where batch_name=l_batch_name;
1741
1742 -- bee_trace('Batch id is '||l_batch_id);
1743
1744 l_request_id := fnd_request.submit_request (
1745
1746 'PAY',
1747 'PAYBEESSTRANSFER',
1748 null,
1749 null,
1750 null,
1751 l_batch_name,
1752 l_business_group_id);
1753
1754 -- bee_trace('Concurrent request id is '||l_request_id);
1755
1756 commit;
1757
1758
1759 end if;
1760
1761 exception when others then
1762 null;
1763 end process_api;
1764
1765 procedure transfer_batch(errbuf OUT nocopy VARCHAR2, retcode OUT nocopy NUMBER,p_batch_name in varchar2,p_business_group_id in number) is
1766 l_batch_id number;
1767 l_request_id number;
1768 l_phase_code varchar2(10);
1769 l_transaction_id number;
1770 begin
1771
1772 -- bee_trace('Batch Name is in transfer '||p_batch_name);
1773 -- bee_trace('Bg Id is in transfer '||p_business_group_id);
1774 --l_transaction_id := get_transaction_id_batch_name(p_batch_name=>p_batch_name);
1775
1776 -- bee_trace('Entered transfer batch');
1777
1778 select batch_id into l_batch_id
1779 from pay_batch_headers
1780 where batch_name=p_batch_name;
1781
1782 -- bee_trace('Batch Id is '||l_batch_id);
1783 l_request_id := pay_paywsqee_pkg.paylink_request_id(p_business_group_id=>p_business_group_id,
1784 p_mode => 'TRANSFER'
1785 ,p_batch_id =>l_batch_id);
1786
1787 -- bee_trace('Req id of actual transfer is '||l_request_id);
1788
1789
1790 select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;
1791
1792 -- bee_trace('Initial phase cose is '||l_phase_code);
1793
1794
1795
1796 while (l_phase_code<>'C')
1797 loop
1798 dbms_lock.sleep(5);
1799 select PHASE_CODE into l_phase_code from fnd_concurrent_requests where request_id=l_request_id;
1800 -- bee_trace('Phase code is '||l_phase_code);
1801 end loop;
1802
1803
1804
1805 commit;
1806
1807
1808
1809
1810 end transfer_batch;
1811
1812
1813 PROCEDURE process_dummy_api
1814 (p_validate in boolean default false
1815 ,p_transaction_step_id in number
1816 ,p_effective_date in varchar2 default null
1817 ) is
1818 begin
1819 hr_utility.set_location('Process dummy api',1);
1820 ---- -- bee_trace('Entered process api');
1821
1822 end process_dummy_api;
1823
1824 procedure delete_batch_on_cancel(p_batch_name in varchar2) is
1825 l_transaction_id number;
1826 l_flag number;
1827 begin
1828 l_transaction_id :=get_transaction_id_batch_name(p_batch_name);
1829
1830
1831 select count(*) into l_flag
1832 from hr_api_transaction_values
1833 where name = 'P_BATCH_TYPE'
1834 and transaction_step_id in(select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );
1835
1836 -- bee_trace('flag val is '||l_flag);
1837 if l_flag=0 then
1838 delete from hr_api_transaction_values
1839 where transaction_step_id in (select transaction_step_id from hr_api_transaction_steps where transaction_id=l_transaction_id );
1840
1841 -- bee_trace('deleted from hr_api_transaction_values');
1842 delete from hr_api_transaction_steps
1843 where transaction_id=l_transaction_id;
1844
1845 -- bee_trace('deleted from hr_api_transaction_steps');
1846 delete from hr_api_transactions
1847 where transaction_id=l_transaction_id;
1848
1849 -- bee_trace('deleted from hr_api_transactions');
1850 end if;
1851
1852
1853 end delete_batch_on_cancel;
1854
1855 END PAY_BEE_SS;