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