[Home] [Help]
PACKAGE BODY: APPS.PAY_SLA_PKG
Source
1 PACKAGE BODY pay_sla_pkg AS
2 /* $Header: pysla.pkb 120.17 2011/05/17 13:08:31 nchinnam ship $ */
3 --
4 /*
5 * ***************************************************************************
6 --
7 Copyright (c) Oracle Corporation (UK) Ltd 1993,1994. All Rights Reserved.
8 --
9 PRODUCT
10 Oracle*Payroll
11 --
12 NAME
13 PAY_SLA_PKG - Payroll support for SLA (Sub Ledger Accounting)
14 --
15 --
16 DESCRIPTION
17 --
18 MODIFIED DD-MON-YYYY
19 120.13.12000000.4 kskoduri 03-Mar-2011 Bug 11810540
20 Unable to find the gl_set_of_books_id while rollback
21 of Transfer to SLA ,When the locked costing of payment
22 Process has no payroll_id in its payroll_action
23 120.13.12000000.3 kskoduri 23-Dec-2010 Bug 9787069
24 Accounting date for Retrocosting process is calculated
25 as the effective date of the retrocosting process
26 120.13.12000000.2 priupadh 14-Aug-2008 Bug 7283055
27 Function get_conversion_type Return CONVERSION_TYPE in
28 place of USER_CONVERSION_TYPE
29 120.13 A.Logue 18-Oct-2006 Handle un-specified payroll ids
30 in Costing processes in delete_event.
31 120.12 A.Logue 16-Oct-2006 Handle un-specified payroll ids
32 in Costing processes.
33 120.11 A.Logue 13-Oct-2006 Subtle change to
34 ESITMATE_COST_REVERSAL event type
35 code.
36 Improved rule names to include
37 segment names.
38 120.10 A.Logue 05-Oct-2006 Use Chart of Account name in
39 create_custom_adrs.
40 120.9 A.Logue 04-Oct-2006 Added create_custom_adrs.
41 Bug 5531729.
42 120.8 A.Logue 04-Sep-2006 Added delete_event to support rollback
43 of a Transfer to SLA (if events not
44 completed).
45 Also simplified Postprocessing.
46 Bug 5510388.
47 120.7 A.Logue 15-Aug-2006 Correct entity_type_code passed to
48 XLA_EVENTS_PUB_PKG.create_event, and
49 initialise event_status in pay_xla_events.
50 120.6 A.Logue 15-Jun-2006 Fix GSCC error.
51 120.5 A.Logue 25-May-2006 Mark payroll action as complete if
52 all actions are complete in Postprocessing.
56 120.1 A.Logue 16-Nov-2005 Added various procedures and functions.
53 120.4 A.Logue 25-Nov-2005 Added stub preaccounting, postaccounting
54 and extract procedures for unused SLA hooks.
55 120.3 A.Logue 22-Nov-2005 Postprocessing procedure.
57 115.0 A.Logue 07-Oct-2005 Created.
58 --
59 DESCRIPTION
60 Payroll support for SLA (Sub Ledger Accounting)
61
62 SLA is new in R12. This file exists to avoid breaking dual maintenance on
63 TGL files between 11.5 and R12.
64
65 */
66 --
67 -- Caches for get_conversion_type
68 --
69 g_business_group_id pay_payroll_actions.business_group_id%TYPE := null;
70 g_currency_type gl_daily_rates.conversion_type%TYPE := null;
71 g_conversion_date date := null;
72 --
73 -- Caches for get_accounting_date
74 --
75 g_revb_acc_date_mode pay_action_parameter_values.parameter_value%TYPE := null;
76 g_tgl_date_used pay_action_parameter_values.parameter_value%TYPE := null;
77 --
78 -- Caches for get_ecost_accounting_date
79 --
80 g_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
81 g_cost_date date := null;
82 g_accounting_date date := null;
83 --
84 PROCEDURE trans_asg_costs
85 (i_assignment_action_id NUMBER)
86 IS
87 --
88 -- Cursor to et distinct accounting dates for a Payment Cost action
89 --
90 CURSOR payment_account_dates (c_action_id NUMBER)
91 IS
92 SELECT DISTINCT accounting_date
93 FROM pay_payment_costs
94 WHERE assignment_action_id = c_action_id;
95 --
96 t_payroll_action_id NUMBER;
97 c_assignment_action_id NUMBER;
98 r_assignment_action_id NUMBER;
99 c_payroll_action_id NUMBER;
100 r_payroll_action_id NUMBER;
101 r_action_type pay_payroll_actions.action_type%TYPE;
102 r_assignment_id NUMBER;
103 c_action_type pay_payroll_actions.action_type%TYPE;
104 c_effective_date DATE;
105 l_ledger_id pay_all_payrolls_f.gl_set_of_books_id%TYPE;
106 l_accounting_date DATE;
107 l_rev_accounting_date DATE;
108 l_event_id NUMBER;
109 --
110 l_event_source_info xla_events_pub_pkg.t_event_source_info;
111 l_security_context xla_events_pub_pkg.t_security;
112 BEGIN
113 --
114 hr_utility.set_location('pay_sla_pkg.trans_asg_costs',10);
115 --
116 SELECT pa.payroll_action_id,
117 pa1.assignment_id,
118 pa1.assignment_action_id,
119 ppa1.payroll_action_id,
120 ppa1.action_type,
121 ppa1.effective_date
122 INTO t_payroll_action_id,
123 r_assignment_id,
124 c_assignment_action_id,
125 c_payroll_action_id,
126 c_action_type,
127 c_effective_date
128 FROM pay_assignment_actions pa, -- TGL assignment action
129 pay_action_interlocks pi, -- interlock to cost
130 pay_assignment_actions pa1, -- cost assignment action
131 pay_payroll_actions ppa1 -- cost payroll action
132 WHERE pa.assignment_action_id = i_assignment_action_id
133 AND pi.locking_action_id = pa.assignment_action_id
134 AND pa1.assignment_action_id = pi.locked_action_id
135 AND ppa1.payroll_action_id = pa1.payroll_action_id
136 AND ppa1.action_type IN ('C', 'S', 'EC', 'CP');
137
138 -- initialise xla structures
139 l_event_source_info.application_id := 801;
140 l_event_source_info.legal_entity_id := null;
141 l_event_source_info.transaction_number := to_char(i_assignment_action_id);
142 l_event_source_info.entity_type_code := 'ASSIGNMENTS';
143
144 --
145 -- Calls to XLA_EVENTS_PUB_PKG.create_event
146 -- (or XLA_EVENTS_PUB_PKG.create_bulk_events)
147 --
148
149 if (c_action_type IN ('C', 'S')) then
150 --
151 -- Costing or Retrocosting : event class COSTS
152 --
153 hr_utility.set_location('pytrgl.trans_ass_costs',20);
154 --
155 SELECT pa2.assignment_action_id,
156 ppa2.payroll_action_id,
157 ppa2.action_type,
158 pap.gl_set_of_books_id,
159 pay_sla_pkg.get_accounting_date
160 (ppa2.action_type, c_effective_date,
161 ppa2.effective_date, ppa2.date_earned)
162 INTO r_assignment_action_id,
163 r_payroll_action_id,
164 r_action_type,
165 l_ledger_id,
166 l_accounting_date
167 FROM pay_assignment_actions pa, -- TGL assignment action
168 pay_action_interlocks pi2, -- interlock to run
169 pay_assignment_actions pa2, -- run assignment action
170 pay_payroll_actions ppa2,-- run payroll action
171 pay_all_payrolls_f pap
172 WHERE pa.assignment_action_id = i_assignment_action_id
173 AND pi2.locking_action_id = pa.assignment_action_id
174 AND pa2.assignment_action_id = pi2.locked_action_id
175 AND ppa2.payroll_action_id = pa2.payroll_action_id
176 AND ppa2.action_type NOT IN ('C', 'S', 'EC')
177 AND pap.payroll_id = ppa2.payroll_id
178 AND ppa2.effective_date BETWEEN pap.effective_start_date
179 AND pap.effective_end_date;
180
181 l_event_source_info.ledger_id := l_ledger_id;
182
183 if (c_action_type = 'C') then
184 --
185 -- Raise COST event type event
186 -- on tid i_assignment_action_id, date l_accounting_date
187 --
188
189 l_event_source_info.source_id_int_1 := i_assignment_action_id;
190 --l_event_source_info.source_id_date_1 := l_accounting_date;
191 l_event_source_info.source_id_char_1 := to_char(l_accounting_date, 'YYYY/MM/DD');
192
193 l_event_id := XLA_EVENTS_PUB_PKG.create_event (
194 p_event_source_info => l_event_source_info,
195 p_event_type_code => 'COST',
196 p_event_date => l_accounting_date,
197 p_event_status_code => 'U',
198 p_valuation_method => null,
199 p_security_context => l_security_context
200 );
201 --
202 -- create entry in pay_xla_events
203 --
204 insert into pay_xla_events (assignment_action_id, event_id,
205 accounting_date, event_status)
206 values (i_assignment_action_id, l_event_id, l_accounting_date, 'U');
207 --
208 else
209 --
210 -- Raise RETRO_COST event type event
211 -- on tid i_assignment_action_id, date l_accounting_date
212 --
213
214 l_event_source_info.source_id_int_1 := i_assignment_action_id;
215 --l_event_source_info.source_id_date_1 := l_accounting_date;
216 -- Bug 9787069
217 -- For Retrocosting accounting date is taken as the effective date of retrocosting process
218 l_accounting_date := c_effective_date ;
219 l_event_source_info.source_id_char_1 := to_char(l_accounting_date, 'YYYY/MM/DD');
220
221 l_event_id := XLA_EVENTS_PUB_PKG.create_event (
222 p_event_source_info => l_event_source_info,
223 p_event_type_code => 'RETRO_COST',
224 p_event_date => l_accounting_date,
225 p_event_status_code => 'U',
226 p_valuation_method => null,
227 p_security_context => l_security_context
228 );
229
230 --
231 -- create entry in pay_xla_events
232 --
233 insert into pay_xla_events (assignment_action_id, event_id,
234 accounting_date, event_status)
235 values (i_assignment_action_id, l_event_id, l_accounting_date, 'U');
236 --
237 end if;
238
239 elsif (c_action_type = 'EC') then
240 --
241 -- Estimate Costing : event class ESTIMATE_COSTS
242 --
243 hr_utility.set_location('pytrgl.trans_ass_costs',30);
244 --
245 SELECT pay_sla_pkg.get_ecost_accounting_date
246 (pera.payroll_id, c_effective_date),
247 pap.gl_set_of_books_id
248 INTO l_rev_accounting_date,
249 l_ledger_id
250 FROM per_all_assignments_f pera,
251 pay_all_payrolls_f pap
252 WHERE pera.assignment_id = r_assignment_id
253 AND c_effective_date BETWEEN pera.effective_start_date
254 AND pera.effective_end_date
255 AND pap.payroll_id = pera.payroll_id
256 AND c_effective_date BETWEEN pap.effective_start_date
257 AND pap.effective_end_date;
258
259 l_event_source_info.ledger_id := l_ledger_id;
260
261 --
262 -- Raise ESTIMATE_COST event type event
263 -- on tid i_assignment_action_id, date c_effective_date
264 --
265
266 l_event_source_info.source_id_int_1 := i_assignment_action_id;
267 --l_event_source_info.source_id_date_1 := c_effective_date;
268 l_event_source_info.source_id_char_1 := to_char(c_effective_date, 'YYYY/MM/DD');
269
270 l_event_id := XLA_EVENTS_PUB_PKG.create_event (
271 p_event_source_info => l_event_source_info,
272 p_event_type_code => 'ESTIMATE_COST',
273 p_event_date => c_effective_date,
274 p_event_status_code => 'U',
275 p_valuation_method => null,
276 p_security_context => l_security_context
277 );
278
279 --
280 -- create entry in pay_xla_events
281 --
282 insert into pay_xla_events (assignment_action_id, event_id,
283 accounting_date, event_status)
284 values (i_assignment_action_id, l_event_id, c_effective_date, 'U');
285 --
286 -- Raise ESTIMATE_COST_REVERSAL event type event
287 -- on tid i_assignment_action_id, date l_rev_accounting_date
288 --
289
290 l_event_source_info.source_id_int_1 := i_assignment_action_id;
291 --l_event_source_info.source_id_date_1 := l_rev_accounting_date;
295 l_event_id := XLA_EVENTS_PUB_PKG.create_event (
292 l_event_source_info.source_id_char_1 := to_char(l_rev_accounting_date,
293 'YYYY/MM/DD');
294
296 p_event_source_info => l_event_source_info,
297 p_event_type_code => 'ESITMATE_COST_REVERSAL',
298 p_event_date => l_rev_accounting_date,
299 p_event_status_code => 'U',
300 p_valuation_method => null,
301 p_security_context => l_security_context
302 );
303
304 --
305 --
306 -- create entry in pay_xla_events
307 --
308 insert into pay_xla_events (assignment_action_id, event_id,
309 accounting_date, event_status)
310 values (i_assignment_action_id, l_event_id, l_rev_accounting_date, 'U');
311 --
312 else
313 --
314 -- Payment Costs
315 --
316 -- create distinct ones for each different date for payment costs
317 --
318
319 SELECT pap.gl_set_of_books_id
323 WHERE pera.assignment_id = r_assignment_id
320 INTO l_ledger_id
321 FROM per_all_assignments_f pera,
322 pay_all_payrolls_f pap
324 AND c_effective_date BETWEEN pera.effective_start_date
325 AND pera.effective_end_date
326 AND pap.payroll_id = pera.payroll_id
327 AND c_effective_date BETWEEN pap.effective_start_date
328 AND pap.effective_end_date;
329
330 l_event_source_info.ledger_id := l_ledger_id;
331 l_event_source_info.source_id_int_1 := i_assignment_action_id;
332
333 for account_date in payment_account_dates (c_assignment_action_id) loop
334
335 --
336 -- Raise PAYMENT_COST event type event
337 -- on tid i_assignment_action_id, date account_date.accouting_date
338 --
339
340 --l_event_source_info.source_id_date_1 := account_date.accounting_date;
341 l_event_source_info.source_id_char_1 :=
342 to_char(account_date.accounting_date, 'YYYY/MM/DD');
343
344 l_event_id := XLA_EVENTS_PUB_PKG.create_event (
345 p_event_source_info => l_event_source_info,
346 p_event_type_code => 'PAYMENT_COST',
347 p_event_date => account_date.accounting_date,
348 p_event_status_code => 'U',
349 p_valuation_method => null,
350 p_security_context => l_security_context
351 );
352
353 --
354 -- create entry in pay_xla_events (assignment_action_id, event_id, date)
355 --
356 insert into pay_xla_events (assignment_action_id, event_id,
357 accounting_date, event_status)
358 values (i_assignment_action_id, l_event_id, account_date.accounting_date, 'U');
359
360 end loop;
361
362 end if;
363 --
364 END trans_asg_costs;
365 --
366 PROCEDURE postprocessing
367 (p_application_id NUMBER,
368 p_accounting_mode VARCHAR2)
369 IS
370
371 CURSOR xla_events_cur IS
372 SELECT XPAE.event_id event_id,
373 XPAE.event_type_code event_type_code,
374 XPAE.SOURCE_ID_INT_1 event_aa_id,
375 XPAE.ledger_id ledger_id
376 FROM XLA_POST_ACCTG_EVENTS_V XPAE,
377 GL_SETS_OF_BOOKS GSOB,
378 PAY_XLA_EVENTS PAYE
379 WHERE XPAE.ledger_id = GSOB.set_of_books_id
380 AND PAYE.event_id = XPAE.event_id
381 AND PAYE.event_status <> 'C';
382
383 BEGIN
384 --
385 hr_utility.set_location('pay_sla_pkg.post_process_event',10);
386 --
387 IF (p_application_id <> 801) THEN
388 RETURN;
389 END IF;
390 --
391 --
392 -- If not in Final Mode do nothing (eg Draft)
393 --
394 IF (p_accounting_mode <> 'F') THEN
395 RETURN;
396 END IF;
397 --
398 for xlarec in xla_events_cur loop
399 --
400 hr_utility.set_location('pay_sla_pkg.post_process_event',10);
401 --
402 --
403 -- Mark the Events as complete
404 --
405 UPDATE pay_xla_events
406 SET event_status = 'C'
407 WHERE event_id = xlarec.event_id;
408 --
409 end loop;
410 --
411 END postprocessing;
412 --
413 FUNCTION get_conversion_type
414 (i_business_group_id NUMBER,
415 i_conversion_date DATE)
416 RETURN VARCHAR2 IS
417
418 CURSOR get_conv_type(p_user_currency_type gl_daily_conversion_types.user_conversion_type%type) IS
419 SELECT conversion_type
420 FROM gl_daily_conversion_types gdct
421 WHERE gdct.user_conversion_type = p_user_currency_type;
422
423 l_user_currency_type VARCHAR2(30);
424 l_currency_type VARCHAR2(30);
425 BEGIN
426 --
427 hr_utility.set_location('Entering pay_sla_pkg.get_conversion_type',10);
428 --
429 if (g_business_group_id is not null) and
430 (i_business_group_id = g_business_group_id) and
431 (i_conversion_date = g_conversion_date) then
432 --
433 l_currency_type := g_currency_type;
434 --
435 else
436 --
437 l_user_currency_type := hruserdt.get_table_value(i_business_group_id,
438 'EXCHANGE_RATE_TYPES', 'Conversion Rate Type',
439 'PAY',i_conversion_date);
440
441 hr_utility.set_location('In pay_sla_pkg.get_conversion_type l_user_currency_type '||l_user_currency_type,15);
442 /*Begin Bug 7283055 USER_CONVERSION_TYPE was getting returned need to return Conversion Type */
443 open get_conv_type(l_user_currency_type);
444 fetch get_conv_type into l_currency_type;
445 close get_conv_type;
446
447 g_currency_type := l_currency_type;
448 g_business_group_id := i_business_group_id;
449 g_conversion_date := i_conversion_date;
450 --
451 end if;
452 hr_utility.set_location('In pay_sla_pkg.get_conversion_type l_currency_type '||l_currency_type,20);
453 hr_utility.set_location('In pay_sla_pkg.get_conversion_type g_currency_type '||g_currency_type,30);
454 hr_utility.set_location('Leaving pay_sla_pkg.get_conversion_type',40);
455 --
456 return(l_currency_type);
457 --
458 END get_conversion_type;
459 --
460 FUNCTION get_accounting_date
461 (run_action_type VARCHAR2,
462 cost_effective_date DATE,
463 run_effective_date DATE,
464 run_date_earned DATE)
465 RETURN DATE IS
469 hr_utility.set_location('pay_sla_pkg.get_accounting_date',10);
466 l_accounting_date DATE;
467 BEGIN
468 --
470 --
471 if (run_action_type in ('B', 'V')) then
472 --
473 -- For Reversals and Balance Adjustments
474 -- eff date of cost if TGL_REVB_ACC_DATE = 'E'
475 -- otherwise eff date of rev/ba
476 if (g_revb_acc_date_mode is null) then
477 begin
478 select parameter_value
479 into g_revb_acc_date_mode
480 from pay_action_parameters
481 where parameter_name = 'TGL_REVB_ACC_DATE';
482 exception
483 when others then
484 g_revb_acc_date_mode := 'P';
485 end;
486 end if;
487
488 if g_revb_acc_date_mode = 'C' then
489 l_accounting_date := cost_effective_date;
490 else
491 l_accounting_date := run_effective_date;
492 end if;
493 --
494 else
495 --
496 -- For Runs and Quickpays
497 -- run date earned if TGL_DATE_USED = 'E'
498 -- otherwise eff date of run
499 --
500 if (g_tgl_date_used is null) then
501 begin
502 select parameter_value
503 into g_tgl_date_used
504 from pay_action_parameters
505 where parameter_name = 'TGL_DATE_USED';
506 exception
507 when others then
508 g_tgl_date_used := 'P';
509 end;
510 end if;
511
512 if g_tgl_date_used = 'E' then
513 l_accounting_date := run_date_earned;
514 else
515 l_accounting_date := run_effective_date;
516 end if;
517 end if;
518 --
519 return(l_accounting_date);
520 --
521 END get_accounting_date;
522 --
523 FUNCTION get_ecost_accounting_date
524 (ecost_payroll_id NUMBER,
525 cost_effective_date DATE)
526 RETURN DATE IS
527 l_accounting_date DATE;
528 BEGIN
529 --
530 hr_utility.set_location('pay_sla_pkg.get_ecost_accounting_date',10);
531 --
532 --
533 -- For Estimate Costs
534 -- negation deltas at end of payroll period only
535 -- ecost period end date of TGL_DATE_USED = 'E' or = 'EVE'
536 -- otherwise period pay_advice_date + payroll pay_date_offset(!)
537 --
538 if (g_payroll_id is not null) and
539 (ecost_payroll_id = g_payroll_id) and
540 (cost_effective_date = g_cost_date) then
541 l_accounting_date := g_accounting_date;
542 else
543 --
544 if (g_tgl_date_used is null) then
545 begin
546 select parameter_value
547 into g_tgl_date_used
548 from pay_action_parameters
549 where parameter_name = 'TGL_DATE_USED';
550 exception
551 when others then
552 g_tgl_date_used := 'P';
553 end;
554 end if;
555
556 SELECT /*+ ORDERED */
557 decode(g_tgl_date_used, 'E', ptp.end_date,
558 'EVE', ptp.end_date,
559 ptp.pay_advice_date + pay.pay_date_offset)
560 INTO l_accounting_date
561 FROM pay_all_payrolls_f pay,
562 per_time_periods ptp
563 WHERE pay.payroll_id = ecost_payroll_id
564 AND cost_effective_date BETWEEN pay.effective_start_date
565 AND pay.effective_end_date
566 AND ptp.payroll_id = pay.payroll_id
567 AND cost_effective_date BETWEEN ptp.start_date
568 AND ptp.end_date;
569
570 g_payroll_id := ecost_payroll_id;
571 g_cost_date := cost_effective_date;
572 g_accounting_date := l_accounting_date;
573 --
574 end if;
575 --
576 return(l_accounting_date);
577 --
578 END get_ecost_accounting_date;
579 --
580 -- Stub for XLA preaccounting hook
581 --
582 PROCEDURE preaccounting
583 (p_application_id NUMBER,
584 p_ledger_id NUMBER,
585 p_process_category VARCHAR2,
586 p_end_date DATE,
587 p_accounting_mode VARCHAR2,
588 p_valuation_method VARCHAR2,
589 p_security_id_int_1 NUMBER,
590 p_security_id_int_2 NUMBER,
591 p_security_id_int_3 NUMBER,
592 p_security_id_char_1 VARCHAR2,
593 p_security_id_char_2 VARCHAR2,
594 p_security_id_char_3 VARCHAR2,
595 p_report_request_id NUMBER)
596 IS
597 BEGIN
598 --
599 hr_utility.set_location('pay_sla_pkg.preaccounting',10);
600 --
601 END preaccounting;
602 --
603 -- Stub for XLA postaccounting hook
604 --
605 PROCEDURE postaccounting
606 (p_application_id NUMBER,
607 p_ledger_id NUMBER,
608 p_process_category VARCHAR2,
609 p_end_date DATE,
610 p_accounting_mode VARCHAR2,
611 p_valuation_method VARCHAR2,
612 p_security_id_int_1 NUMBER,
613 p_security_id_int_2 NUMBER,
614 p_security_id_int_3 NUMBER,
615 p_security_id_char_1 VARCHAR2,
616 p_security_id_char_2 VARCHAR2,
620 BEGIN
617 p_security_id_char_3 VARCHAR2,
618 p_report_request_id NUMBER)
619 IS
621 --
622 hr_utility.set_location('pay_sla_pkg.postaccounting',10);
623 --
624 END postaccounting;
625 --
626 --
627 -- Stub for XLA extract hook
628 --
629 PROCEDURE extract
630 (p_application_id NUMBER,
631 p_accounting_mode VARCHAR2)
632 IS
633 BEGIN
634 --
635 hr_utility.set_location('pay_sla_pkg.extract',10);
636 --
637 END extract;
638 --
639 -- Delet_event : called form rollback code
640 --
641 PROCEDURE delete_event
642 (i_assignment_action_id NUMBER)
643 IS
644 CURSOR xla_events_cur IS
645 SELECT PAYE.event_id event_id,
646 PAYE.accounting_date
647 FROM PAY_XLA_EVENTS PAYE
648 WHERE PAYE.assignment_action_id = i_assignment_action_id;
649
650 l_completed_events number;
654
651 l_ledger_id pay_all_payrolls_f.gl_set_of_books_id%TYPE;
652 l_event_source_info xla_events_pub_pkg.t_event_source_info;
653 l_security_context xla_events_pub_pkg.t_security;
655 BEGIN
656 --
657 hr_utility.set_location('pay_sla_pkg.delete_event',10);
658 --
659 -- Confirm no Completed events for this action
660 --
661 SELECT count(*)
662 INTO l_completed_events
663 FROM pay_xla_events
664 WHERE assignment_action_id = i_assignment_action_id
665 AND event_status = 'C';
666
667 IF l_completed_events <> 0 THEN
668
669 --
670 -- SLA has processed the event and passed to GL
671 --
672 hr_utility.trace('delete_event failed more than 1 C event');
673 hr_utility.set_message (801, 'HR_7507_ACTION_UNDO_INTLOCK');
674 hr_utility.raise_error;
675
676 ELSE
677
678 SELECT pap.gl_set_of_books_id
679 INTO l_ledger_id
680 FROM pay_assignment_actions pa, -- TGL assignment action
681 pay_action_interlocks pi, -- interlock to cost
682 pay_assignment_actions pa1, -- cost assignment action
683 pay_payroll_actions ppa1,-- cost payroll action
684 pay_all_payrolls_f pap
685 WHERE pa.assignment_action_id = i_assignment_action_id
686 AND pi.locking_action_id = pa.assignment_action_id
687 AND pa1.assignment_action_id = pi.locked_action_id
688 AND ppa1.payroll_action_id = pa1.payroll_action_id
689 AND ppa1.action_type IN ('C', 'S', 'EC', 'CP')
690 AND ppa1.payroll_id = pap.payroll_id (+)
691 AND ppa1.effective_date BETWEEN pap.effective_start_date (+)
692 AND pap.effective_end_date (+);
693
694 hr_utility.trace('Set of books id is'||l_ledger_id);
695
696 IF l_ledger_id is null then
697
698 SELECT pap.gl_set_of_books_id
699 INTO l_ledger_id
700 FROM pay_assignment_actions pa, -- TGL assignment action
701 pay_action_interlocks pi, -- interlock to run
702 pay_assignment_actions pa1, -- run assignment action
703 pay_payroll_actions ppa1,-- run payroll action
704 pay_all_payrolls_f pap,
705 per_all_assignments_f paf
706 WHERE pa.assignment_action_id = i_assignment_action_id
707 AND pi.locking_action_id = pa.assignment_action_id
708 AND pa1.assignment_action_id = pi.locked_action_id
709 AND ppa1.payroll_action_id = pa1.payroll_action_id
710 AND ppa1.action_type IN ('R', 'Q', 'V','B','CP')
711 AND paf.assignment_id = pa.assignment_id
712 AND ppa1.effective_date BETWEEN paf.effective_start_date
713 AND paf.effective_end_date
714 AND paf.payroll_id = pap.payroll_id
718
715 AND ppa1.effective_date BETWEEN pap.effective_start_date
716 AND pap.effective_end_date;
717
719 END IF;
720
721 -- initialise xla structures
722 l_event_source_info.application_id := 801;
723 l_event_source_info.legal_entity_id := null;
724 l_event_source_info.ledger_id := l_ledger_id;
725 l_event_source_info.transaction_number := to_char(i_assignment_action_id);
726 l_event_source_info.entity_type_code := 'ASSIGNMENTS';
727 l_event_source_info.source_id_int_1 := i_assignment_action_id;
728
729 for xlarec in xla_events_cur loop
730
731 l_event_source_info.source_id_char_1 := to_char(xlarec.accounting_date,
732 'YYYY/MM/DD');
733
734 hr_utility.trace('Before XLA_EVENTS_PUB_PKG.delete_event');
735 -- delete the event in SLA
736 XLA_EVENTS_PUB_PKG.delete_event (
737 p_event_source_info => l_event_source_info,
738 p_event_id => xlarec.event_id,
739 p_valuation_method => null,
740 p_security_context => l_security_context
741 );
742 hr_utility.trace('After XLA_EVENTS_PUB_PKG.delete_event');
743 end loop;
744
745 -- delete the event from pay_xla_events
746 DELETE from pay_xla_events
747 WHERE assignment_action_id = i_assignment_action_id;
748
749 END IF;
750 --
751 END delete_event;
752 --
753 -- Procedure to create custom ADRs based on their existing PAY-GL flex map
754 -- Creates a rule (in xla_rules_t) per Chart of Accounts Segment
755 -- Creates a rule detail (in xla_rule_details_t) for each Payroll Segment
756 -- mapped to this COA Segment
757 -- Creats 1 condition (in xla_conditions_t) for each rule detail
758 -- specifying the Payroll name
759 --
760 PROCEDURE create_custom_adrs
761 IS
762 --
763 -- cursor to get mapping info
764 --
765 CURSOR segmaps IS
766 SELECT fm.payroll_id, fm.gl_set_of_books_id, gl.chart_of_accounts_id,
767 fm.gl_account_segment, fm.payroll_cost_segment,
768 fs.id_flex_structure_name coa_name,
769 fseg.segment_name coa_seg_name
770 FROM pay_payroll_gl_flex_maps fm,
771 gl_sets_of_books gl,
772 fnd_id_flex_structures_vl fs,
773 fnd_id_flex_segments_vl fseg
774 WHERE fm.gl_set_of_books_id = gl.set_of_books_id
775 AND fs.application_id = 101
776 AND fs.id_flex_code = 'GL#'
777 AND fs.id_flex_num = gl.chart_of_accounts_id
778 AND fseg.application_id = 101
779 AND fseg.id_flex_code = 'GL#'
780 AND fseg.id_flex_num = gl.chart_of_accounts_id
781 AND fseg.application_column_name = fm.gl_account_segment
782 ORDER BY gl.chart_of_accounts_id, fm.gl_account_segment;
783 --
784 l_segment_rule_code xla_rules_t.segment_rule_code%type;
785 l_coa_id xla_rules_t.accounting_coa_id%type;
786 l_acc_segment xla_rules_t.flexfield_segment_code%type;
787 l_pay_segment xla_rules_t.flexfield_segment_code%type;
788 l_prev_payroll_id pay_all_payrolls_f.payroll_id%type := -1;
789 l_payroll_name pay_all_payrolls_f.payroll_name%type;
790 l_rule_det_seq xla_rule_details_t.user_sequence%type;
791 --
792 BEGIN
793 --
794 hr_utility.set_location('pay_sla_pkg.create_custom_adrs',10);
795 --
796 for segmap in segmaps loop
797
798 l_acc_segment := segmap.gl_account_segment;
799 l_pay_segment := segmap.payroll_cost_segment;
800 l_coa_id := segmap.chart_of_accounts_id;
801
805 select payroll_name
802 l_segment_rule_code := l_acc_segment||'_'||l_coa_id;
803
804 if (segmap.payroll_id <> l_prev_payroll_id) then
806 into l_payroll_name
807 from pay_all_payrolls_f
811 l_prev_payroll_id := segmap.payroll_id;
808 where payroll_id = segmap.payroll_id
809 and rownum < 2;
810
812 end if;
813
814 -- Create the rule if it doesn't exist
815
816 insert into xla_rules_t (
817 application_id,
818 amb_context_code,
819 segment_rule_type_code,
820 segment_rule_code,
821 accounting_coa_id,
822 flexfield_assign_mode_code,
823 flexfield_segment_code,
824 enabled_flag,
825 name,
826 description,
827 error_value)
828 select
829 801,
830 'DEFAULT',
831 'S',
832 l_segment_rule_code,
833 l_coa_id,
834 'S',
835 l_acc_segment,
836 'Y',
837 'Rule for '||segmap.coa_seg_name|| ' in '||segmap.coa_name,
838 'Rule for '||segmap.coa_seg_name|| ' in '||segmap.coa_name,
839 0
840 from dual
841 where not exists
842 (select 1
843 from xla_rules_t
844 where application_id = 801
845 and segment_rule_code = l_segment_rule_code);
846
847
848 -- Create the rule detail if it doesn't exist
849
850 select count(*)
851 into l_rule_det_seq
852 from xla_rule_details_t
853 where application_id = 801
854 and segment_rule_code = l_segment_rule_code;
855
856 insert into xla_rule_details_t (
857 application_id,
858 amb_context_code,
859 segment_rule_type_code,
860 segment_rule_code,
861 segment_rule_detail_id,
862 user_sequence,
863 value_type_code,
864 value_source_application_id,
865 value_source_type_code,
866 value_source_code,
867 error_value
868 )
869 select
870 801,
871 'DEFAULT',
872 'S',
873 l_segment_rule_code,
874 xla_seg_rule_details_s.nextval,
875 l_rule_det_seq + 1,
876 'S',
877 801,
878 'S',
879 l_pay_segment,
880 0
881 from dual
882 where not exists
883 (select 1
884 from xla_rule_details_t xrd,
885 xla_conditions_t xc
886 where xrd.application_id = 801
887 and xrd.segment_rule_code = l_segment_rule_code
888 and xrd.value_source_code = l_pay_segment
889 and xc.segment_rule_detail_id = xrd.segment_rule_detail_id
890 and xc.value_constant = l_payroll_name);
891
892 -- create the condition if a rule detail was created
893
894 if SQL%ROWCOUNT > 0 then
895
896 insert into xla_conditions_t (
897 condition_id,
898 application_id,
899 amb_context_code,
900 segment_rule_detail_id,
901 user_sequence,
902 value_type_code,
903 source_application_id,
904 source_type_code,
905 source_code,
906 line_operator_code,
907 value_constant,
908 error_value)
909 select
910 xla_conditions_s.nextval,
911 801,
912 'DEFAULT',
913 xla_seg_rule_details_s.currval,
914 1,
915 'C',
916 801,
917 'S',
918 'PAYROLL_NAME',
919 'E',
920 l_payroll_name,
921 0
922 from dual
923 where not exists
924 (select 1
925 from xla_rule_details_t xrd,
926 xla_conditions_t xc
927 where xrd.application_id = 801
928 and xrd.segment_rule_code = l_segment_rule_code
929 and xrd.value_source_code = l_pay_segment
930 and xc.segment_rule_detail_id = xrd.segment_rule_detail_id
931 and xc.value_constant = l_payroll_name);
932 end if;
933
934 end loop;
935
936 --
937 -- Call XLA API To transfer data
938 --
939 xla_adr_interface_pkg.upload_rules;
940 --
941 END create_custom_adrs;
942 --
943 END pay_sla_pkg;