1 PACKAGE BODY pay_trgl_pkg AS
2 /* $Header: pytrangl.pkb 120.15.12010000.2 2008/09/16 11:44:49 pparate 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_TRGL_PKG - Procedure to transfer pay costs to General Ledger.
14 --
15 --
16 DESCRIPTION
17 The procedure sums are costs for each cost centre for all payroll runs
18 which occurr within the tansfer period. The costs are summed across
19 assignments but distinct entries are created for debit and credit for
20 each currency.
21 --
22 MODIFIED DD-MON-YYYY
23 115.48 pparate 16-SEP-2008 Added an OR condition for binding sql
24 variables in case of costing of payments.
25 (Fix for bug 7401269).
26 115.47 pkagrawa 06-FEB-2008 Added ledger_id column for insert into
27 gl_interface for R12
28 115.46 alogue 04-JUL-2007 Avoid Payment Costings populating
29 gl_interface.segment columns.
30 Bug 6169000.
31 115.45 alogue 04-MAY-2007 Avoid double postings for consolidation
32 set payment costs. Bug 6027376.
33 115.44 alogue 30-JAN-2007 Avoid double posting for tax_unit_id
34 balance adjustments. Bug 5854583.
35 115.43 alogue 04-JAN-2007 Avoid gscc error in last change.
36 115.42 alogue 04-JAN-2007 Avoid gscc error in last change.
37 115.41 alogue 03-JAN-2007 Re-implement Bug 5606113 as string
38 concatenation to avoid needless
39 performance loss.
40 115.40 alogue 20-NOV-2006 Performance fixes (hints) to gl_interface
41 insert statement. Bug 5671609.
42 115.39 alogue 03-NOV-2006 Avoid double postings for consolidation
43 set estimate costs. Bug 5606113.
44 115.38 alogue 06-JUN-2006 Performance fixes (hints) to cursor
45 transfer_payrolls. Bug 5243949.
46 115.37 alogue 15-MAY-2006 Handle External Manual Payments to be
47 excluded (not transferred). Bug 5208522.
48 115.36 alogue 14-MAR-2006 Accounting Date in gl_interface should
49 have null time fields. Bug 5089908.
50 115.35 alogue 02-NOV-2005 Post Estimate Reversals on period end
51 date if TGL_DATE_USED set to 'EVE'.
52 Bug 4709735.
53 115.34 alogue 07-OCT-2005 Support of Sub Ledger Accouting (SLA).
54 115.33 alogue 22-AUG-2005 Support of Payment Costs.
55 115.32 alogue 15-NOV-2004 Use of transfer_to_gl_flag in pay_costs.
56 Bug 4013881.
57 115.31 alogue 03-NOV-2004 Always get transfer_to_gl_flag from
58 distributed element for distributed
59 costs. Bug 3972448.
60 115.30 alogue 05-OCT-2004 Support of original entry id balance
61 adjustments. Bug 3911521.
62 115.29 alogue 09-DEC-2003 Populate gl_interface.reference25
63 with Runs' payroll_action_id.
64 Bug 3306461.
65 115.28 alogue 30-OCT-2003 Use per_all_assignments_f,
66 pay_all_payrolls and
67 per_business_groups_perf.
68 115.27 alogue 23-JUL-2003 Enhance to process run actions for the
69 specified payroll regardless of
70 whether assignment is still on that
71 payroll. Bug 2396819.
72 115.26 alogue 02-JUN-2003 Support of TGL_REVB_ACC_DATE action
73 parameter. Population of reference24
74 in gl_interface. Bug 2987235.
75 115.25 alogue 24-APR-2003 Hints in gl_interface insert statement
76 in trans_pay_costs_mt. Bug 2919000.
77 115.24 alogue 10-FEB-2003 Support of Estimate Cost Process.
78 Bug 2794030.
79 115.23 alogue 21-NOV-2002 Support of grandchild (and further)
80 Run child actions.
81 115.22 alogue 20-NOV-2002 Hints for Performance Improvements.
82 Bug 2676232.
83 115.21 alogue 20-SEP-2002 dbdrv lines.
84 115.20 alogue 20-SEP-2002 Support of transfer of prorated
85 elements. Corresponding fix for
86 changes to Costing under bug 2437171.
87 Bug 2574990.
88 115.19 alogue 16-NOV-2001 Fix to last change to multi-threaded
89 solution. Bug 2110560.
90 115.17 alogue 24-AUG-2001 Performance enhancement : use of hint
91 in single-threaded solution. Iterative
92 Engine support whereby TGL aa only
93 interlocks master aas from run.
94 115.16 alogue 26-APR-2001 Performance enhancement : use of hints
95 in multi-threaded solution.
96 115.15 alogue 26-APR-2001 Performance enhancement : use of hints
97 in single-threaded solution.
98 115.14 alogue 18-JAN-2001 Enhanced Multi-threaded version to
99 include all latest enhancements.
100 Bug 1561507.
101 115.13 alogue 17-NOV-2000 Handle non existant Conversion Rate Type
102 Bug 1504406.
103 115.12 alogue 10-OCT-2000 Population of gl_interface.group_id
104 with tgl payroll_action_id if
105 TGL_GROUP_ID pay_action_parameter
106 is set to Y. Bug 1073147.
107 115.11 alogue 21-SEP-2000 Join to per_assignments_f at runs
108 effective_date in main cursor to
109 ensure assignments costs are transferred
110 when assignemnt has changed payroll
111 between runs date_earned and
112 effective_date. Bug 1322332.
113 115.10 alogue 04-SEP-2000 EFC support : foreign currency
114 handling by population of
115 user_currency_conversion_type
116 and currency_conversion_date in
117 gl_interface. These columns are
118 ignored if GL is in the same currency.
119 115.9 alogue 29-JUN-2000 Support of TGL_DATE_USED
120 pay_action_parameter to switch to
121 using date_earned for accounting_date.
122 Bug 1343096.
123 115.8 alogue 01-FEB-2000 Transferal of retrocosting results
124 performance enhancement.
125 115.7 alogue 24-JAN-2000 Transferal of retrocosting results.
126 115.6 alogue 10-NOV-1999 Get user_je_source_name from
127 gl_je_sources_vl and user_je_category_name
128 from gl_je_categories_vl instead of
129 using PAY_PAYROLL lookup. Bug 1066820.
130 115.5 alogue 25-OCT-1999 Use of PAY_PAYROLL lookup to avoid
131 entering of hard-coded untranslated
132 values into user_je_source_name and
133 user_je_category_name of gl_interface.
134 Bug 893879.
135 110.9 alogue 25-AUG-1998 Multi-threaded Implementation.
136 110.8 alogue 09-JUL-1998 Performance improvement of the main
137 insert statement, disabled business
138 group index on element link.
139 110.7 alogue 14-MAY-1998 Performance improvement of the
140 aa update statement, disabled
141 payroll index.
142 110.6 alogue 10-FEB-1998 Performance Enhancement. Bug 633879.
143 110.5 alogue 10-FEB-1998 Performance Enhancement. Bug 625189.
144 110.4 alogue 02-JAN-1998 Support for transferal of balance
145 adjustments.
146 110.3 alogue 26-NOV-1997 Bug 589335. Now handles legislative
147 indirect element entries with links set to
148 link_to_all_payrolls.
149 110.2 alogue 18-NOV-1997 Bug 587439 transferring non-default
150 consolidation set fix.
151 40.9 alogue 25-JUN-1997 Bug 486556 reverse backport change.
152 40.8 alogue 25-JUN-1997 Bug 486556 prod-14 backport. Add
153 pay_basis_id and employment_category to
154 link qualification in element deleted part
155 of 'INSERT INTO gl_interface' cursor.
156 40.7 alogue 19-JUN-1997 Catered for pay_run_results source_id
157 is reversed run_result_id in case of
158 a reversal. (Result of bug 507602 fix.)
159 40.6 jalloun 30-JUL-1996 Added error handling.
160 40.5 nbristow 19-JUL-1996 Bug 368244. The costing process, thus
161 the transfer to gl process has had to be
162 changed to cost as of date earned.
163 40.4 nbristow 14-JUN-1996 The UNION in the sql to insert into the
164 gl_interface table was incorrectly
165 returning indirects on both sides of the
166 union.
167 40.3 nbristow 14-JUN-1996 Bug 374389. Run results for reversal of
168 indirect results now have a source type
169 of V.
170 40.1 mwcallag 16-Jun-1995 The select for insert into the GL interface
171 table has been modified to check
172 the transfer to GL flag held on the element
173 link table, and also to deal with costed
174 hours correctly. More details are given
175 in the notes below.
176 40.0 A.Frith 14-May-1994 Created.
177 --
178 --
179 DESCRIPTION
180 Transfer to GL procedure.
181 --
182 NOTES
183 Transfer to GL flag
184 -------------------
185 The Transfer to GL flag is a check box on the element link screen, and
186 held on the table pay_element_links_f. In release 9 this flag used to be
187 copied down to the costing table. However, this creates problems if the
188 user subsequently goes back and modifies the flag. So, for release 10,
189 it is only held on the link table. The SQL needed to track back to the
190 element link table from pay_run_results is very similar to the SQL for the
191 costing process (file pycos.lpc). The SQL consists of a union, with one
192 half reaching the element link table via the element entry table. The
193 other half is needed for when the element entry has been deleted following
194 a run. In this case the element link table is reached via the element
195 type table using the partial matching code.
196 --
197 Costed Hours
198 ------------
199 The costing process will only cost run results whose unit of measure are
200 either money or hours. For money, the currency code value is used for the
201 currency code column in the GL interface table. For costed hours, we
202 enter 'STAT' in the currency code column (statistical information). This
203 ensures the entries are kept separate, otherwise it would be possible for
204 a money value of 100 pounds and a time of 10:15 which both have the same
205 cost flex to be added together as 110.25.
206 --
207 Retrocosting Results.
208 ---------------------
209 Correction results created by the Retrocosting process populate reference23
210 with the date of the original entry that was in error and is being corrected.
211 --
212 Foreign Currency Units.
213 ------------------------
214 The GL may be working in a different currency than Payroll and hence we
215 always populate user_currency_conversion_type and currency_conversion_date
216 in gl_interface so that GL takes care of currency coversion if necessary.
217 These columns are ignored if GL is in the same currency.
218 --
219 Populating Group_id.
220 --------------------
221 The group_Id column is populated with the TGL payroll_action_id only if
222 the TGL_GROUP_ID pay_action_parameter is defined with a value of 'Y'.
223 --
224 Populating Reference25.
225 -----------------------
226 The reference25 column is populated with the Run payroll_action_id so that
227 an audit trail exists from GL all the way back to element entry, assignment,
228 etc.
229 */
230 --
231 -- date used cache for value of TGL_DATE_USED pay_action_parameter
232 g_date_used VARCHAR2(80) := null;
233
234 /* SINGLE-THREADED SOLUTION */
235 --
236 PROCEDURE trans_pay_costs
237 (i_payroll_action_id NUMBER)
238 IS
239 --
240 --
241 -- Cursor to get the payroll run actions which are processed by this
242 -- transfer to general ledger action. An assignment action will exist
243 -- for each assignment action in a payroll run payroll action which
244 -- has been costed for this transfer to general ledger payroll action.
245 --
246 CURSOR transfer_payrolls (i_action_id NUMBER)
247 IS
248 SELECT DISTINCT ppa2.payroll_action_id,
249 ppa1.payroll_action_id,
250 pp.payroll_id,
251 pp.gl_set_of_books_id
252 FROM pay_payroll_actions ppa1, -- Cost pay actions
253 pay_assignment_actions pa1, -- Cost asg actions.
254 pay_action_interlocks pi3, -- Cost - Run
255 pay_action_interlocks pi1, -- Cost - Trans GL
256 pay_all_payrolls_f pp,
257 pay_action_classifications pac,
258 pay_payroll_actions ppa2, -- Payroll run actions.
259 pay_assignment_actions pa2, -- Payroll run asg actions.
260 pay_action_interlocks pi2, -- Run - Trans GL
261 pay_assignment_actions pa, -- Trans GL asg actions
262 pay_payroll_actions ppa -- Trans GL pay actions
263 WHERE ppa.payroll_action_id = i_action_id
264 AND pa.payroll_action_id = ppa.payroll_action_id
265 AND pa.action_status <> 'C'
266 AND pi2.locking_action_id = pa.assignment_action_id
267 AND pa2.assignment_action_id = pi2.locked_action_id
268 AND ppa2.payroll_action_id = pa2.payroll_action_id
269 AND ppa2.consolidation_set_id +0 = ppa.consolidation_set_id
270 AND pac.action_type = ppa2.action_type
271 AND pac.classification_name = 'COSTED'
272 AND pp.payroll_id = ppa2.payroll_id
273 AND pi1.locking_action_id = pa.assignment_action_id
274 AND pa1.assignment_action_id = pi1.locked_action_id
275 AND pa1.assignment_action_id <> pa2.assignment_action_id
276 AND pi3.locking_action_id = pa1.assignment_action_id
277 AND pa2.assignment_action_id = pi3.locked_action_id
278 AND ppa1.payroll_action_id = pa1.payroll_action_id
279 AND ppa.effective_date
280 BETWEEN pp.effective_start_date
281 AND pp.effective_end_date
282 ORDER by pp.payroll_id;
283 --
284 --
285 -- Cursor to get the map of pay cost segments to gl account segments
286 -- for the payroll. The set of books for the payroll may be date
287 -- effectively changed so the set of books id is the one which is
288 -- date effective at the effective run date.
289 --
290 CURSOR flex_segments (i_payroll_id NUMBER,
291 i_gl_sets_of_books_id NUMBER)
292 IS
293 SELECT gl_account_segment,
294 payroll_cost_segment
295 FROM pay_payroll_gl_flex_maps
296 WHERE payroll_id = i_payroll_id
297 AND gl_set_of_books_id = i_gl_sets_of_books_id;
298 --
299 --
300 pay_segment_list VARCHAR2(1200); -- Dynamically built varchar
301 -- used in the select statement.
302 gl_segment_list VARCHAR2(930); -- Dynamically built varchar
303 -- for insert statement.
304 sql_curs NUMBER; -- For dynamic sql statement.
305 rows_processed INTEGER;
306 prev_payroll_id NUMBER := 0;
307 prev_sob_id NUMBER := 0;
308 l_currency_type VARCHAR2(30);
309 c_run_action_id NUMBER;
310 c_cost_action_id NUMBER;
311 c_payroll_id NUMBER;
312 c_accounting_date DATE;
313 c_conversion_date DATE;
314 c_run_date DATE;
315 c_run_date_earned DATE;
316 l_bus_grp_id NUMBER;
317 c_set_of_books_id NUMBER;
318 l_source_name VARCHAR2(25);
319 l_category_name VARCHAR2(25);
320 l_date_used VARCHAR2(80);
321 l_rvb_acc_date VARCHAR2(80);
322 l_group_id VARCHAR2(80);
323 --
324 BEGIN
325 --
326 hr_utility.set_location('pytrgl.trans_pay_costs',10);
327 --
328 sql_curs :=dbms_sql.open_cursor;
329 --
330 OPEN transfer_payrolls (i_payroll_action_id);
331 --
332 hr_utility.set_location('pytrgl.trans_pay_costs',20);
333 --
334 -- Bug 1066820 avoid passing in hard coded strings.
335 --
336 select user_je_source_name
337 into l_source_name
338 from gl_je_sources_vl
339 where je_source_name = 'Payroll';
340 --
341 hr_utility.set_location('pytrgl.trans_pay_costs',25);
342 --
343 select user_je_category_name
344 into l_category_name
345 from gl_je_categories_vl
346 where je_category_name = 'Payroll';
347 --
348 hr_utility.set_location('pytrgl.trans_pay_costs',27);
349 --
350 -- Find if use an accouting date of date_earned
351 -- (default is effective_date)
352 --
353 begin
354 select parameter_value
355 into l_date_used
356 from pay_action_parameters
357 where parameter_name = 'TGL_DATE_USED';
358 exception
359 when others then
360 l_date_used := 'P';
361 end;
362 --
363 hr_utility.set_location('pytrgl.trans_pay_costs',28);
364 --
365 -- Find if use an accouting date of date_earned
366 -- (default is effective_date)
367 --
368 begin
369 select parameter_value
370 into l_rvb_acc_date
371 from pay_action_parameters
372 where parameter_name = 'TGL_REVB_ACC_DATE';
373 exception
374 when others then
375 l_rvb_acc_date := 'P';
376 end;
377 --
378 hr_utility.set_location('pytrgl.trans_pay_costs',29);
379 --
380 -- Find if should populate gl_interface.group_id
381 -- (default is to leave this column blank)
382 -- If so we populate it with the TGL payroll_action_id
383 --
384 begin
385 select parameter_value
386 into l_group_id
387 from pay_action_parameters
388 where parameter_name = 'TGL_GROUP_ID';
389 exception
390 when others then
391 l_group_id := 'N';
392 end;
393 --
394 hr_utility.set_location('pytrgl.trans_pay_costs',30);
395 --
396 -- Process each run action in turn. Each run action may be for
397 -- several payrolls. Each payroll will be processed separately.
398 LOOP
399 --
400 FETCH transfer_payrolls INTO
401 c_run_action_id, -- payroll run action
402 c_cost_action_id, -- cost action
403 c_payroll_id,
404 c_set_of_books_id;
405 --
406 hr_utility.set_location('pytrgl.trans_pay_costs',31);
407 --
408 EXIT WHEN transfer_payrolls%NOTFOUND;
409 --
410 hr_utility.set_location('pytrgl.trans_pay_costs',32);
411 --
412 select decode(ppa1.action_type, 'S', ppa1.effective_date,
413 decode(ppa2.action_type, 'B', decode(l_rvb_acc_date, 'C', ppa1.effective_date,
414 ppa2.effective_date),
415 'V', decode(l_rvb_acc_date, 'C', ppa1.effective_date,
416 ppa2.effective_date),
417 decode(l_date_used, 'E', ppa2.date_earned,
418 ppa2.effective_date))),
419 ppa2.effective_date,
420 ppa2.date_earned,
421 ppa2.business_group_id
422 into c_accounting_date,
423 c_run_date,
424 c_run_date_earned,
425 l_bus_grp_id
426 from pay_payroll_actions ppa1, -- Cost pay actions
427 pay_payroll_actions ppa2 -- Payroll run action
428 where ppa1.payroll_action_id = c_cost_action_id
429 and ppa2.payroll_action_id = c_run_action_id;
430 --
431 hr_utility.set_location('pytrgl.trans_pay_costs',35);
432 --
433 -- Get payrolls currency conversion rate type
434 -- handle fact that it may be null
435 --
436 begin
437 l_currency_type := hruserdt.get_table_value(l_bus_grp_id,
438 'EXCHANGE_RATE_TYPES', 'Conversion Rate Type',
439 'PAY',c_accounting_date);
440 c_conversion_date := c_accounting_date;
441
442 exception
443 when no_data_found then
444 hr_utility.set_location('pytrgl.trans_pay_costs',37);
445 l_currency_type := null;
446 c_conversion_date := null;
447 end;
448 --
449 -- Only bother to rebuild segment lists if they are different from
450 -- previous iteration. This avoids rebuilding when a run has been costed
451 -- and then retrocosted.
452 --
453 if (c_payroll_id <> prev_payroll_id or
454 c_set_of_books_id <> prev_sob_id) then
455 --
456 pay_segment_list := NULL;
457 gl_segment_list := NULL;
458 --
459 hr_utility.set_location('pytrgl.trans_pay_costs',40);
460 --
461 -- Dynamically build up the segment lists for the payroll.
462 FOR flex_segs IN flex_segments ( c_payroll_id, c_set_of_books_id )
463 LOOP
464 --
465 hr_utility.set_location('pytrgl.trans_pay_costs',50);
466 --
467 gl_segment_list := gl_segment_list ||
468 flex_segs.gl_account_segment ||',';
469 --
470 hr_utility.set_location('pytrgl.trans_pay_costs',60);
471 --
472 -- Payroll segment list needs to MIN() function as not grouped
473 -- by each segment.
474 --
475 pay_segment_list := pay_segment_list || 'MIN(caf.' ||
476 flex_segs.payroll_cost_segment||'),';
477 --
478 END LOOP;
479 --
480 end if;
481 --
482 prev_payroll_id := c_payroll_id;
483 prev_sob_id := c_set_of_books_id;
484 --
485 hr_utility.set_location('pytrgl.trans_pay_costs',70);
486 --
487 -- Put the statement into the cursor and parse. Don't know how long
488 -- the segment list is so we cannot bind to variables.
489 --
490
491 -- For Post 11i
492 if (PAY_ADHOC_UTILS_PKG.chk_post_r11i = 'Y') then
493 dbms_sql.parse(sql_curs,
494 'INSERT INTO gl_interface
495 (status,
496 ledger_id,
497 set_of_books_id,
498 user_je_source_name,
499 user_je_category_name,
500 accounting_date,
501 currency_code,
502 group_id,
503 date_created,
504 created_by,
505 actual_flag,'||
506 gl_segment_list||'
507 reference21,
508 reference22,
509 reference23,
510 reference24,
511 reference25,
512 user_currency_conversion_type,
513 currency_conversion_date,
514 entered_dr,
515 entered_cr)
516 SELECT /*+ ORDERED */
517 ''NEW'',
518 :c_set_of_books_id,
519 :c_set_of_books_id,
520 :l_source_name,
521 :l_category_name,
522 :c_accounting_date,
523 decode (IV.uom, ''M'', et.output_currency_code, ''STAT''),
524 decode (:l_group_id, ''Y'', :i_payroll_action_id),
525 trunc(sysdate),
526 801,
527 ''A'','||
528 pay_segment_list||'
529 :i_payroll_action_id,
530 cst.cost_allocation_keyflex_id,
531 :c_run_date,
532 :c_run_date_earned,
533 :c_run_action_id,
534 :l_currency_type,
535 :c_conversion_date,
536 SUM(DECODE(cst.debit_or_credit,''D'',cst.costed_value,0)),
537 SUM(DECODE(cst.debit_or_credit,''C'',cst.costed_value,0))
538 FROM pay_payroll_actions ppa, -- Run payroll action
539 pay_payroll_actions ppa1, -- Cost payroll action
540 per_all_assignments_f per,
541 pay_assignment_actions pa, -- TGL assignment action
542 pay_action_interlocks pi, -- interlock to costing
543 pay_assignment_actions pa1, -- Cost assignment action
544 pay_action_interlocks pi2, -- interlock to run
545 pay_assignment_actions pa3, -- run master assignment action
546 pay_assignment_actions pa2, -- run assignment action
547 pay_costs cst,
548 pay_cost_allocation_keyflex caf,
549 pay_run_results rr,
550 pay_input_values_f IV,
551 pay_element_types_f et
552 WHERE pa.payroll_action_id = :i_payroll_action_id
553 AND pa.assignment_id = per.assignment_id
554 AND per.payroll_id = :c_payroll_id
555 AND pi.locking_action_id = pa.assignment_action_id
556 AND pa1.assignment_action_id = pi.locked_action_id
557 AND ppa1.payroll_action_id = pa1.payroll_action_id
558 AND ppa1.payroll_action_id = :c_cost_action_id
559 AND cst.assignment_action_id = pa1.assignment_action_id
560 AND pa2.payroll_action_id = :c_run_action_id
561 AND ppa.payroll_action_id = :c_run_action_id
562 AND pa3.payroll_action_id = :c_run_action_id
563 AND pi2.locking_action_id = pa.assignment_action_id
564 AND pa2.assignment_id = pa.assignment_id
565 AND pa3.assignment_id = pa.assignment_id
566 and pa3.source_action_id is null
567 AND pa3.assignment_action_id = pi2.locked_action_id
568 AND rr.assignment_action_id = pa2.assignment_action_id
569 AND cst.run_result_id = rr.run_result_id
570 and RR.element_type_id = IV.element_type_id
571 AND IV.input_value_id = CST.input_value_id
572 and ppa.date_earned between IV.effective_start_date
573 and IV.effective_end_date
574 AND EXISTS
575 (select RR1.run_result_id
576 from pay_run_results RR1
577 , pay_run_results RR2
578 , pay_element_entries_f EE1
579 , pay_element_links_f EL1
580 where RR1.assignment_action_id = pa2.assignment_action_id
581 and RR1.source_id = RR2.run_result_id
582 and RR1.source_type NOT IN (''E'', ''I'', ''V'')
583 and RR2.source_id = EE1.element_entry_id
584 and least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
585 between EE1.effective_start_date
586 and EE1.effective_end_date
587 and EL1.element_link_id = EE1.element_link_id
588 and ppa.date_earned between EL1.effective_start_date
589 and EL1.effective_end_date
590 and EL1.transfer_to_gl_flag = ''Y''
591 and RR1.run_result_id = nvl(CST.distributed_run_result_id,
592 CST.run_result_id)
593 UNION ALL
594 select RR1.run_result_id
595 from pay_run_results RR1
596 , pay_element_entries_f EE1
597 , pay_element_links_f EL1
598 where RR1.assignment_action_id = pa2.assignment_action_id
599 and RR1.source_id = EE1.element_entry_id
600 and RR1.source_type NOT IN (''R'', ''I'', ''V'')
601 and least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
602 between EE1.effective_start_date
603 and EE1.effective_end_date
604 and EL1.element_link_id = EE1.element_link_id
605 and ppa.date_earned between EL1.effective_start_date
606 and EL1.effective_end_date
607 and EL1.transfer_to_gl_flag = ''Y''
608 and RR1.run_result_id = nvl(CST.distributed_run_result_id,
609 CST.run_result_id)
610 UNION ALL
611 select RR1.run_result_id
612 FROM pay_run_results RR1,
613 per_all_assignments_f PERA,
614 pay_element_types_f ET1,
615 pay_element_classifications EC,
616 pay_element_links_f EL1
617 WHERE RR1.assignment_action_id = pa2.assignment_action_id
618 AND RR1.run_result_id = nvl(CST.distributed_run_result_id,
619 CST.run_result_id)
620 AND (
621 (NOT EXISTS /* look for deleted element entries */
622 (SELECT null
623 FROM pay_element_entries_f EE1
624 WHERE RR1.source_id = EE1.element_entry_id
625 AND RR1.source_type IN (''E'', ''I'')
626 AND least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
627 BETWEEN EE1.effective_start_date
628 AND EE1.effective_end_date
629 )
630 AND NOT EXISTS
631 (SELECT null
632 FROM pay_run_results RR2,
633 pay_element_entries_f EE1
634 WHERE RR2.source_id = EE1.element_entry_id
635 AND RR1.source_type IN (''R'', ''V'')
636 AND RR1.source_id = RR2.run_result_id
637 AND least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
638 BETWEEN EE1.effective_start_date
639 AND EE1.effective_end_date
640 ))
641 OR RR1.source_type IN (''I'', ''V'')
642 )
643 AND RR1.assignment_action_id = pa2.assignment_action_id
644 AND RR1.element_type_id = ET1.element_type_id
645 AND ppa.date_earned BETWEEN ET1.effective_start_date
646 AND ET1.effective_end_date
647 AND ET1.classification_id = EC.classification_id
648 AND ET1.element_type_id = EL1.element_type_id
649 AND ppa.date_earned BETWEEN EL1.effective_start_date
650 AND EL1.effective_end_date
651 AND EL1.costable_type <> ''N''
652 and EL1.transfer_to_gl_flag = ''Y''
653 AND PERA.assignment_id = pa2.assignment_id
654 AND PERA.business_group_id = EL1.business_group_id +0
655 AND ppa.date_earned BETWEEN PERA.effective_start_date
656 AND PERA.effective_end_date
657 AND ((EL1.payroll_id IS NOT NULL
658 AND EL1.payroll_id = PERA.payroll_id)
659 OR (EL1.link_to_all_payrolls_flag = ''Y''
660 AND PERA.payroll_id IS NOT NULL)
661 OR EL1.payroll_id IS NULL)
662 AND (EL1.organization_id = PERA.organization_id
663 OR EL1.organization_id IS NULL)
664 AND (EL1.position_id = PERA.position_id
665 OR EL1.position_id IS NULL)
666 AND (EL1.job_id = PERA.job_id
667 OR EL1.job_id IS NULL)
668 AND (EL1.grade_id = PERA.grade_id
669 OR EL1.grade_id IS NULL)
670 AND (EL1.location_id = PERA.location_id
671 OR EL1.location_id IS NULL)
672 AND (EL1.pay_basis_id = PERA.pay_basis_id
673 OR EL1.pay_basis_id IS NULL)
674 AND (EL1.employment_category = PERA.employment_category
675 OR EL1.employment_category IS NULL)
676 AND (EL1.people_group_id IS NULL
677 OR EXISTS
678 (SELECT 1
679 FROM pay_assignment_link_usages_f PAL
680 WHERE PAL.assignment_id = PERA.assignment_id
681 AND PAL.element_link_id = EL1.element_link_id
682 AND ppa.date_earned BETWEEN PAL.effective_start_date
683 AND PAL.effective_end_date))
684 )
685 AND et.element_type_id = rr.element_type_id
686 AND caf.cost_allocation_keyflex_id = cst.cost_allocation_keyflex_id
687 AND ppa.effective_date
688 BETWEEN per.effective_start_date
689 AND per.effective_end_date
690 AND ppa.date_earned
691 BETWEEN et.effective_start_date
692 AND et.effective_end_date
693 GROUP BY cst.cost_allocation_keyflex_id,
694 cst.debit_or_credit,
695 decode (IV.uom, ''M'', et.output_currency_code, ''STAT'')',
696 dbms_sql.v7);
697
698 else
699 dbms_sql.parse(sql_curs,
700 'INSERT INTO gl_interface
701 (status,
702 set_of_books_id,
703 user_je_source_name,
704 user_je_category_name,
705 accounting_date,
706 currency_code,
707 group_id,
708 date_created,
709 created_by,
710 actual_flag,'||
711 gl_segment_list||'
712 reference21,
713 reference22,
714 reference23,
715 reference24,
716 reference25,
717 user_currency_conversion_type,
718 currency_conversion_date,
719 entered_dr,
720 entered_cr)
721 SELECT /*+ ORDERED */
722 ''NEW'',
723 :c_set_of_books_id,
724 :l_source_name,
725 :l_category_name,
726 :c_accounting_date,
727 decode (IV.uom, ''M'', et.output_currency_code, ''STAT''),
728 decode (:l_group_id, ''Y'', :i_payroll_action_id),
729 trunc(sysdate),
730 801,
731 ''A'','||
732 pay_segment_list||'
733 :i_payroll_action_id,
734 cst.cost_allocation_keyflex_id,
735 :c_run_date,
736 :c_run_date_earned,
737 :c_run_action_id,
738 :l_currency_type,
739 :c_conversion_date,
740 SUM(DECODE(cst.debit_or_credit,''D'',cst.costed_value,0)),
741 SUM(DECODE(cst.debit_or_credit,''C'',cst.costed_value,0))
742 FROM pay_payroll_actions ppa, -- Run payroll action
743 pay_payroll_actions ppa1, -- Cost payroll action
744 per_all_assignments_f per,
745 pay_assignment_actions pa, -- TGL assignment action
746 pay_action_interlocks pi, -- interlock to costing
747 pay_assignment_actions pa1, -- Cost assignment action
748 pay_action_interlocks pi2, -- interlock to run
749 pay_assignment_actions pa3, -- run master assignment action
750 pay_assignment_actions pa2, -- run assignment action
751 pay_costs cst,
752 pay_cost_allocation_keyflex caf,
753 pay_run_results rr,
754 pay_input_values_f IV,
755 pay_element_types_f et
756 WHERE pa.payroll_action_id = :i_payroll_action_id
757 AND pa.assignment_id = per.assignment_id
758 AND per.payroll_id = :c_payroll_id
759 AND pi.locking_action_id = pa.assignment_action_id
760 AND pa1.assignment_action_id = pi.locked_action_id
761 AND ppa1.payroll_action_id = pa1.payroll_action_id
762 AND ppa1.payroll_action_id = :c_cost_action_id
763 AND cst.assignment_action_id = pa1.assignment_action_id
764 AND pa2.payroll_action_id = :c_run_action_id
765 AND ppa.payroll_action_id = :c_run_action_id
766 AND pa3.payroll_action_id = :c_run_action_id
767 AND pi2.locking_action_id = pa.assignment_action_id
768 AND pa2.assignment_id = pa.assignment_id
769 AND pa3.assignment_id = pa.assignment_id
770 and pa3.source_action_id is null
771 AND pa3.assignment_action_id = pi2.locked_action_id
772 AND rr.assignment_action_id = pa2.assignment_action_id
773 AND cst.run_result_id = rr.run_result_id
774 and RR.element_type_id = IV.element_type_id
775 AND IV.input_value_id = CST.input_value_id
776 and ppa.date_earned between IV.effective_start_date
777 and IV.effective_end_date
778 AND EXISTS
779 (select RR1.run_result_id
780 from pay_run_results RR1
781 , pay_run_results RR2
782 , pay_element_entries_f EE1
783 , pay_element_links_f EL1
784 where RR1.assignment_action_id = pa2.assignment_action_id
785 and RR1.source_id = RR2.run_result_id
786 and RR1.source_type NOT IN (''E'', ''I'', ''V'')
787 and RR2.source_id = EE1.element_entry_id
788 and least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
789 between EE1.effective_start_date
790 and EE1.effective_end_date
791 and EL1.element_link_id = EE1.element_link_id
792 and ppa.date_earned between EL1.effective_start_date
793 and EL1.effective_end_date
794 and EL1.transfer_to_gl_flag = ''Y''
795 and RR1.run_result_id = nvl(CST.distributed_run_result_id,
796 CST.run_result_id)
797 UNION ALL
798 select RR1.run_result_id
799 from pay_run_results RR1
800 , pay_element_entries_f EE1
801 , pay_element_links_f EL1
802 where RR1.assignment_action_id = pa2.assignment_action_id
803 and RR1.source_id = EE1.element_entry_id
804 and RR1.source_type NOT IN (''R'', ''I'', ''V'')
805 and least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
806 between EE1.effective_start_date
807 and EE1.effective_end_date
808 and EL1.element_link_id = EE1.element_link_id
809 and ppa.date_earned between EL1.effective_start_date
810 and EL1.effective_end_date
811 and EL1.transfer_to_gl_flag = ''Y''
812 and RR1.run_result_id = nvl(CST.distributed_run_result_id,
813 CST.run_result_id)
814 UNION ALL
815 select RR1.run_result_id
816 FROM pay_run_results RR1,
817 per_all_assignments_f PERA,
818 pay_element_types_f ET1,
819 pay_element_classifications EC,
820 pay_element_links_f EL1
821 WHERE RR1.assignment_action_id = pa2.assignment_action_id
822 AND RR1.run_result_id = nvl(CST.distributed_run_result_id,
823 CST.run_result_id)
824 AND (
825 (NOT EXISTS /* look for deleted element entries */
826 (SELECT null
827 FROM pay_element_entries_f EE1
828 WHERE RR1.source_id = EE1.element_entry_id
829 AND RR1.source_type IN (''E'', ''I'')
830 AND least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
831 BETWEEN EE1.effective_start_date
832 AND EE1.effective_end_date
833 )
834 AND NOT EXISTS
835 (SELECT null
836 FROM pay_run_results RR2,
837 pay_element_entries_f EE1
838 WHERE RR2.source_id = EE1.element_entry_id
839 AND RR1.source_type IN (''R'', ''V'')
840 AND RR1.source_id = RR2.run_result_id
841 AND least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
842 BETWEEN EE1.effective_start_date
843 AND EE1.effective_end_date
844 ))
845 OR RR1.source_type IN (''I'', ''V'')
846 )
847 AND RR1.assignment_action_id = pa2.assignment_action_id
848 AND RR1.element_type_id = ET1.element_type_id
849 AND ppa.date_earned BETWEEN ET1.effective_start_date
850 AND ET1.effective_end_date
851 AND ET1.classification_id = EC.classification_id
852 AND ET1.element_type_id = EL1.element_type_id
853 AND ppa.date_earned BETWEEN EL1.effective_start_date
854 AND EL1.effective_end_date
855 AND EL1.costable_type <> ''N''
856 and EL1.transfer_to_gl_flag = ''Y''
857 AND PERA.assignment_id = pa2.assignment_id
858 AND PERA.business_group_id = EL1.business_group_id +0
859 AND ppa.date_earned BETWEEN PERA.effective_start_date
860 AND PERA.effective_end_date
861 AND ((EL1.payroll_id IS NOT NULL
862 AND EL1.payroll_id = PERA.payroll_id)
863 OR (EL1.link_to_all_payrolls_flag = ''Y''
864 AND PERA.payroll_id IS NOT NULL)
865 OR EL1.payroll_id IS NULL)
866 AND (EL1.organization_id = PERA.organization_id
867 OR EL1.organization_id IS NULL)
868 AND (EL1.position_id = PERA.position_id
869 OR EL1.position_id IS NULL)
870 AND (EL1.job_id = PERA.job_id
871 OR EL1.job_id IS NULL)
872 AND (EL1.grade_id = PERA.grade_id
873 OR EL1.grade_id IS NULL)
874 AND (EL1.location_id = PERA.location_id
875 OR EL1.location_id IS NULL)
876 AND (EL1.pay_basis_id = PERA.pay_basis_id
877 OR EL1.pay_basis_id IS NULL)
878 AND (EL1.employment_category = PERA.employment_category
879 OR EL1.employment_category IS NULL)
880 AND (EL1.people_group_id IS NULL
881 OR EXISTS
882 (SELECT 1
883 FROM pay_assignment_link_usages_f PAL
884 WHERE PAL.assignment_id = PERA.assignment_id
885 AND PAL.element_link_id = EL1.element_link_id
886 AND ppa.date_earned BETWEEN PAL.effective_start_date
887 AND PAL.effective_end_date))
888 )
889 AND et.element_type_id = rr.element_type_id
890 AND caf.cost_allocation_keyflex_id = cst.cost_allocation_keyflex_id
891 AND ppa.effective_date
892 BETWEEN per.effective_start_date
893 AND per.effective_end_date
894 AND ppa.date_earned
895 BETWEEN et.effective_start_date
896 AND et.effective_end_date
897 GROUP BY cst.cost_allocation_keyflex_id,
898 cst.debit_or_credit,
899 decode (IV.uom, ''M'', et.output_currency_code, ''STAT'')',
900 dbms_sql.v7);
901 end if;
902 --
903 hr_utility.set_location('pytrgl.trans_pay_costs',80);
904 --
905 -- Bind the variable values to the cursor values.
906 --
907 dbms_sql.bind_variable(sql_curs,'c_run_action_id',
908 c_run_action_id);
909 dbms_sql.bind_variable(sql_curs,'c_cost_action_id',
910 c_cost_action_id);
911 dbms_sql.bind_variable(sql_curs,'c_payroll_id', c_payroll_id);
912 dbms_sql.bind_variable(sql_curs,'l_currency_type', l_currency_type);
913 dbms_sql.bind_variable(sql_curs,'c_conversion_date',
914 c_conversion_date);
915 dbms_sql.bind_variable(sql_curs,'c_accounting_date',
916 c_accounting_date);
917 dbms_sql.bind_variable(sql_curs,'c_run_date',
918 c_run_date);
919 dbms_sql.bind_variable(sql_curs,'c_run_date_earned',
920 c_run_date_earned);
921 dbms_sql.bind_variable(sql_curs,'c_set_of_books_id',
922 c_set_of_books_id);
923 dbms_sql.bind_variable(sql_curs,'i_payroll_action_id',
924 i_payroll_action_id);
925 dbms_sql.bind_variable(sql_curs,'l_source_name',
926 l_source_name);
927 dbms_sql.bind_variable(sql_curs,'l_category_name',
928 l_category_name);
929 dbms_sql.bind_variable(sql_curs,'l_group_id',
930 l_group_id);
931 --
932 -- Execute the insert statment.
933 --
934 rows_processed := dbms_sql.execute(sql_curs);
935 --
936 hr_utility.set_location('pytrgl.trans_pay_costs',90);
937 --
938 -- Update the assignment actions. Mark as processed.
939 --
940 UPDATE pay_assignment_actions pa
941 SET pa.action_status = 'C'
942 WHERE pa.action_status <> 'C'
943 AND pa.payroll_action_id = i_payroll_action_id
944 AND EXISTS
945 (SELECT /*+ ORDERED
946 USE_NL (ppa pi2 pa2 per) */
947 NULL
948 FROM
949 pay_payroll_actions ppa,
950 pay_action_interlocks pi2, -- Run - Trans GL
951 pay_assignment_actions pa2, -- Payroll run actions.
952 pay_payroll_actions ppa2,
953 per_all_assignments_f per
954 WHERE pi2.locking_action_id = pa.assignment_action_id
955 AND pa.assignment_id = per.assignment_id
956 AND per.payroll_id +0 = c_payroll_id
957 AND pa2.assignment_action_id = pi2.locked_action_id
958 AND pa2.payroll_action_id = c_run_action_id
959 AND ppa2.payroll_action_id = c_run_action_id
960 AND ppa.payroll_action_id = pa.payroll_action_id
961 AND ppa2.effective_date
962 BETWEEN per.effective_start_date
963 AND per.effective_end_date);
964 --
965 hr_utility.set_location('pytrgl.trans_pay_costs', 100);
966 --
967 COMMIT;
968 --
969 END LOOP;
970 --
971 hr_utility.set_location('pytrgl.trans_pay_costs',110);
972 --
973 CLOSE transfer_payrolls;
974 --
975 dbms_sql.close_cursor(sql_curs);
976 --
977 hr_utility.set_location('pytrgl.trans_pay_costs',120);
978 --
979 END trans_pay_costs;
980
981 --
982 /* MULTI-THREADED SOLUTION */
983 --
984 PROCEDURE trans_pay_costs_mt
985 (i_payroll_action_id NUMBER)
986 IS
987 --
988 --
989 -- Cursor to get the payroll run actions which are processed by this
990 -- transfer to general ledger action. An assignment action will exist
991 -- for each assignment action in a payroll run payroll action which
992 -- has been costed for this transfer to general ledger payroll action.
993 --
994 CURSOR transfer_payrolls (i_action_id NUMBER)
995 IS
996 SELECT /*+ ORDERED*/
997 DISTINCT ppa2.payroll_action_id,
998 ppa1.payroll_action_id,
999 ppa1.action_type,
1000 pp.payroll_id,
1001 pp.gl_set_of_books_id,
1002 ppa1.payroll_id
1003 FROM pay_payroll_actions ppa, -- Trans GL pay actions
1004 pay_assignment_actions pa, -- Trans GL asg actions
1005 pay_action_interlocks pi2, -- Run - Trans GL
1006 pay_assignment_actions pa2, -- Payroll run asg actions.
1007 pay_payroll_actions ppa2, -- Payroll run actions.
1008 pay_action_classifications pac,
1009 pay_all_payrolls_f pp,
1010 pay_action_interlocks pi1, -- Cost - Trans GL
1011 pay_assignment_actions pa1, -- Cost asg actions.
1012 pay_action_interlocks pi3, -- Cost - Run
1013 pay_payroll_actions ppa1 -- Cost pay actions
1014 WHERE ppa.payroll_action_id = i_action_id
1015 AND pa.payroll_action_id = ppa.payroll_action_id
1016 --AND pa.action_status <> 'C'
1017 AND pi2.locking_action_id = pa.assignment_action_id
1018 AND pa2.assignment_action_id = pi2.locked_action_id
1019 AND ppa2.payroll_action_id = pa2.payroll_action_id
1020 AND ppa2.consolidation_set_id +0 = ppa.consolidation_set_id
1021 AND pac.action_type = ppa2.action_type
1022 AND pac.classification_name = 'COSTED'
1023 AND pp.payroll_id = ppa2.payroll_id
1024 AND pi1.locking_action_id = pa.assignment_action_id
1025 AND pa1.assignment_action_id = pi1.locked_action_id
1026 AND pa1.assignment_action_id <> pa2.assignment_action_id
1027 AND pi3.locking_action_id = pa1.assignment_action_id
1028 AND pa2.assignment_action_id = pi3.locked_action_id
1029 AND ppa1.payroll_action_id = pa1.payroll_action_id
1030 AND ppa1.action_type IN ('C', 'S')
1031 AND ppa.effective_date
1032 BETWEEN pp.effective_start_date
1033 AND pp.effective_end_date
1034 UNION ALL
1035 SELECT /*+ ORDERED*/
1036 DISTINCT ppa1.payroll_action_id,
1037 ppa1.payroll_action_id,
1038 ppa1.action_type,
1039 pp.payroll_id,
1040 pp.gl_set_of_books_id,
1041 ppa1.payroll_id
1042 FROM pay_payroll_actions ppa, -- Trans GL pay actions
1043 pay_assignment_actions pa, -- Trans GL asg actions
1044 pay_action_interlocks pi1, -- Cost - Trans GL
1045 pay_assignment_actions pa1, -- Cost asg actions
1046 pay_payroll_actions ppa1, -- Cost pay actions
1047 per_all_assignments_f pera,
1048 pay_all_payrolls_f pp
1049 WHERE ppa.payroll_action_id = i_action_id
1050 AND pa.payroll_action_id = ppa.payroll_action_id
1051 AND pi1.locking_action_id = pa.assignment_action_id
1052 AND pa1.assignment_action_id = pi1.locked_action_id
1053 AND ppa1.payroll_action_id = pa1.payroll_action_id
1054 AND ppa1.action_type in ('EC', 'CP')
1055 AND pera.assignment_id = pa.assignment_id
1056 AND ppa1.effective_date
1057 BETWEEN pera.effective_start_date
1058 AND pera.effective_end_date
1059 AND pp.payroll_id = pera.payroll_id
1060 AND ppa.effective_date
1061 BETWEEN pp.effective_start_date
1062 AND pp.effective_end_date
1063 ORDER by 4; -- pp.payroll_id
1064 --
1065 --
1066 -- Cursor to get the map of pay cost segments to gl account segments
1067 -- for the payroll. The set of books for the payroll may be date
1068 -- effectively changed so the set of books id is the one which is
1069 -- date effective at the effective run date.
1070 --
1071 CURSOR flex_segments (i_payroll_id NUMBER,
1072 i_gl_sets_of_books_id NUMBER)
1073 IS
1074 SELECT gl_account_segment,
1075 payroll_cost_segment
1076 FROM pay_payroll_gl_flex_maps
1077 WHERE payroll_id = i_payroll_id
1078 AND gl_set_of_books_id = i_gl_sets_of_books_id;
1079 --
1080 --
1081 pay_segment_list VARCHAR2(1200); -- Dynamically built varchar
1082 -- used in the select statement.
1083 l_pay_segment_list VARCHAR2(1200);
1084 gl_segment_list VARCHAR2(930); -- Dynamically built varchar
1085 -- for insert statement.
1086 l_gl_segment_list VARCHAR2(930);
1087 sql_curs NUMBER; -- For dynamic sql statement.
1088 rows_processed INTEGER;
1089 prev_payroll_id NUMBER := 0;
1090 prev_sob_id NUMBER := 0;
1091 l_currency_type VARCHAR2(30);
1092 c_run_action_id NUMBER;
1093 c_cost_action_id NUMBER;
1094 c_action_type pay_payroll_actions.action_type%TYPE;
1095 c_payroll_id NUMBER;
1096 c_pay_id NUMBER;
1097 c_accounting_date DATE;
1098 c_conversion_date DATE;
1099 c_run_date DATE;
1100 c_run_date_earned DATE;
1101 l_bus_grp_id NUMBER;
1102 c_set_of_books_id NUMBER;
1103 l_source_name VARCHAR2(25);
1104 l_category_name VARCHAR2(25);
1105 l_bus_currency_code VARCHAR2(150);
1106 l_date_used VARCHAR2(80);
1107 l_rvb_acc_date VARCHAR2(80);
1108 l_group_id VARCHAR2(80);
1109 l_asg_tab VARCHAR2(100);
1110 l_asg_join VARCHAR2(500);
1111 --
1112 BEGIN
1113 --
1114 hr_utility.set_location('pytrgl.trans_pay_costs_mt',10);
1115 --
1116 sql_curs := dbms_sql.open_cursor;
1117 --
1118 OPEN transfer_payrolls (i_payroll_action_id);
1119 --
1120 hr_utility.set_location('pytrgl.trans_pay_costs_mt',20);
1121 --
1122 -- Bug 1066820 avoid passing in hard coded strings.
1123 --
1124 select user_je_source_name
1125 into l_source_name
1126 from gl_je_sources_vl
1127 where je_source_name = 'Payroll';
1128 --
1129 hr_utility.set_location('pytrgl.trans_pay_costs_mt',25);
1130 --
1131 select user_je_category_name
1132 into l_category_name
1133 from gl_je_categories_vl
1134 where je_category_name = 'Payroll';
1135 --
1136 hr_utility.set_location('pytrgl.trans_pay_costs_mt',27);
1137 --
1138 select bus.currency_code, bus.business_group_id
1139 into l_bus_currency_code, l_bus_grp_id
1140 from per_business_groups_perf bus,
1141 pay_payroll_actions ppa
1142 where ppa.payroll_action_id = i_payroll_action_id
1143 and bus.business_group_id = ppa.business_group_id;
1144 --
1145 hr_utility.set_location('pytrgl.trans_pay_costs',28);
1146 --
1147 -- Find if use an accouting date of date_earned
1148 -- (default is effective_date)
1149 --
1150 begin
1151 select parameter_value
1152 into l_date_used
1153 from pay_action_parameters
1154 where parameter_name = 'TGL_DATE_USED';
1155 exception
1156 when others then
1157 l_date_used := 'P';
1158 end;
1159 --
1160 -- Find if use an accouting date of date_earned
1161 -- (default is effective_date)
1162 --
1163 begin
1164 select parameter_value
1165 into l_rvb_acc_date
1166 from pay_action_parameters
1167 where parameter_name = 'TGL_REVB_ACC_DATE';
1168 exception
1169 when others then
1170 l_rvb_acc_date := 'P';
1171 end;
1172 --
1173 hr_utility.set_location('pytrgl.trans_pay_costs',29);
1174 --
1175 -- Find if should populate gl_interface.group_id
1176 -- (default is to leave this column blank)
1177 -- If so we populate it with the TGL payroll_action_id
1178 --
1179 begin
1180 select parameter_value
1181 into l_group_id
1182 from pay_action_parameters
1183 where parameter_name = 'TGL_GROUP_ID';
1184 exception
1185 when others then
1186 l_group_id := 'N';
1187 end;
1188 --
1189 -- Process each run action in turn. Each run action may be for
1190 -- several payrolls. Each payroll will be processed separately.
1191 LOOP
1192 --
1193 FETCH transfer_payrolls INTO
1194 c_run_action_id, -- payroll run action
1195 c_cost_action_id, -- cost action
1196 c_action_type, -- cost action_type 'C', 'S or 'EC'
1197 c_payroll_id,
1198 c_set_of_books_id,
1199 c_pay_id;
1200 --
1201 hr_utility.set_location('pytrgl.trans_pay_costs_mt',30);
1202 --
1203 EXIT WHEN transfer_payrolls%NOTFOUND;
1204 --
1205 hr_utility.set_location('pytrgl.trans_pay_costs_mt',32);
1206 --
1207 if (c_action_type in ('C', 'S')) then
1208
1209 select decode(ppa1.action_type, 'S', ppa1.effective_date,
1210 decode(ppa2.action_type, 'B', decode(l_rvb_acc_date, 'C', ppa1.effective_date,
1211 ppa2.effective_date),
1212 'V', decode(l_rvb_acc_date, 'C', ppa1.effective_date,
1213 ppa2.effective_date),
1214 decode(l_date_used, 'E', ppa2.date_earned,
1215 ppa2.effective_date))),
1216 ppa2.effective_date,
1217 ppa2.date_earned
1218 into c_accounting_date,
1219 c_run_date,
1220 c_run_date_earned
1221 from pay_payroll_actions ppa1, -- Cost pay actions
1222 pay_payroll_actions ppa2 -- Payroll run action
1223 where ppa1.payroll_action_id = c_cost_action_id
1224 and ppa2.payroll_action_id = c_run_action_id;
1225
1226 else
1227 -- estimate costs : nb accounting_date for reversal
1228 -- costs overriden by contents of pay_gl_interface.accouting_date
1229 --
1230 select ppa1.effective_date, ppa1.effective_date
1231 into c_accounting_date, c_run_date
1232 from pay_payroll_actions ppa1 -- Estimate Cost pay action
1233 where ppa1.payroll_action_id = c_cost_action_id;
1234 end if;
1235
1236 -- Avoid double postings for consolidation set estimate costs. Bug 5606113.
1237 -- Join to per_all_assignments_f on Effective date confirming on
1238 -- required payroll. Use string concatenation method to avoid performance hit
1239 -- ie only join to per_all_assignments_f if have to.
1240 if ((c_action_type = 'EC' or c_action_type = 'CP') and
1241 c_pay_id is null) then
1242 hr_utility.set_location('pytrgl.trans_pay_costs_mt',33);
1243 l_asg_tab := 'per_all_assignments_f paf,';
1244 l_asg_join :=
1245 'AND paf.assignment_id = '||'p'||'a.assignment_id
1246 AND :c_run_date between paf.effective_start_date
1247 and paf.effective_end_date
1248 AND paf.payroll_id = :c_payroll_id';
1249 else
1250 hr_utility.set_location('pytrgl.trans_pay_costs_mt',34);
1251 l_asg_tab := '';
1252 l_asg_join := '';
1253 end if;
1254
1255 --
1256 hr_utility.set_location('pytrgl.trans_pay_costs_mt',35);
1257 --
1258 -- Get payrolls currency conversion rate type
1259 -- handle fact that it may be null
1260 --
1261 begin
1262 l_currency_type := hruserdt.get_table_value(l_bus_grp_id,
1263 'EXCHANGE_RATE_TYPES', 'Conversion Rate Type',
1264 'PAY',c_accounting_date);
1265 c_conversion_date := c_accounting_date;
1266
1267 exception
1268 when no_data_found then
1269 hr_utility.set_location('pytrgl.trans_pay_costs',37);
1270 l_currency_type := null;
1271 c_conversion_date := null;
1272 end;
1273 --
1274 -- Only bother to rebuild segment lists if they are different from
1275 -- previous iteration. This avoids rebuilding when a run has been costed
1276 -- and then retrocosted.
1277 --
1278 if (c_payroll_id <> prev_payroll_id or
1279 c_set_of_books_id <> prev_sob_id) then
1280 --
1281 pay_segment_list := NULL;
1282 gl_segment_list := NULL;
1283 --
1284 hr_utility.set_location('pytrgl.trans_pay_costs_mt',40);
1285 --
1286 -- Dynamically build up the segment lists for the payroll.
1287 FOR flex_segs IN flex_segments ( c_payroll_id, c_set_of_books_id )
1288 LOOP
1289 --
1290 hr_utility.set_location('pytrgl.trans_pay_costs_mt',50);
1291 --
1292 gl_segment_list := gl_segment_list ||
1293 flex_segs.gl_account_segment ||',';
1294 --
1295 hr_utility.set_location('pytrgl.trans_pay_costs_mt',60);
1296 --
1297 -- Payroll segment list needs to MIN() function as not grouped
1298 -- by each segment.
1299 --
1300 pay_segment_list := pay_segment_list || 'MIN(caf.' ||
1301 flex_segs.payroll_cost_segment||'),';
1302 --
1303 END LOOP;
1304 --
1305 end if;
1306 --
1307 prev_payroll_id := c_payroll_id;
1308 prev_sob_id := c_set_of_books_id;
1309 --
1310 hr_utility.set_location('pytrgl.trans_pay_costs_mt',65);
1311 --
1312 -- For Payment Costs should not populate segment columns
1313 -- in gl_interface : bug 6169000
1314 --
1315 if (c_action_type = 'CP') then
1316 l_pay_segment_list := null;
1317 l_gl_segment_list := null;
1318 else
1319 l_pay_segment_list := pay_segment_list;
1320 l_gl_segment_list := gl_segment_list;
1321 end if;
1322 --
1323 hr_utility.set_location('pytrgl.trans_pay_costs_mt',70);
1324 --
1325 -- Put the statement into the cursor and parse. Don't know how long
1326 -- the segment list is so we cannot bind to variables.
1327 --
1328 -- For post 11i
1329 if (PAY_ADHOC_UTILS_PKG.chk_post_r11i = 'Y') then
1330 dbms_sql.parse(sql_curs,
1331 'INSERT INTO gl_interface
1332 (status,
1333 ledger_id,
1334 set_of_books_id,
1335 user_je_source_name,
1336 user_je_category_name,
1337 accounting_date,
1338 currency_code,
1339 group_id,
1340 date_created,
1341 created_by,
1342 actual_flag,'||
1343 l_gl_segment_list||'
1344 code_combination_id,
1345 reference21,
1346 reference22,
1347 reference23,
1348 reference24,
1349 reference25,
1350 user_currency_conversion_type,
1351 currency_conversion_date,
1352 entered_dr,
1353 entered_cr)
1354 SELECT /*+ ORDERED
1355 INDEX(paf PER_ASSIGNMENTS_F_PK)
1356 INDEX(int PAY_ACTION_INTERLOCKS_PK)
1357 INDEX(pgl PAY_GL_INTERFACE_N1)
1358 USE_NL(pgl int pa pa2 paf) */
1359 ''NEW'',
1360 :c_set_of_books_id,
1361 :c_set_of_books_id,
1362 :l_source_name,
1363 :l_category_name,
1364 trunc(nvl(pgl.accounting_date, :c_accounting_date)),
1365 pgl.currency_code,
1366 decode (:l_group_id, ''Y'', :i_payroll_action_id),
1367 trunc(sysdate),
1368 801,
1369 ''A'','||
1370 l_pay_segment_list||'
1371 decode(:c_action_type, ''CP'', pgl.cost_allocation_keyflex_id),
1372 :i_payroll_action_id,
1373 pgl.cost_allocation_keyflex_id,
1374 :c_run_date,
1375 :c_run_date_earned,
1376 :c_run_action_id,
1377 :l_currency_type,
1378 :c_conversion_date,
1379 SUM(pgl.entered_dr),
1380 SUM(pgl.entered_cr)
1381 FROM pay_assignment_actions pa,
1382 '|| l_asg_tab ||'
1383 pay_action_interlocks int,
1384 pay_assignment_actions pa2,
1385 pay_gl_interface pgl,
1386 pay_cost_allocation_keyflex caf
1387 WHERE pgl.run_payroll_action_id = :c_run_action_id
1388 '|| l_asg_join ||'
1389 AND caf.cost_allocation_keyflex_id (+) = pgl.cost_allocation_keyflex_id
1390 AND pa.assignment_action_id = pgl.assignment_action_id
1391 AND pa.payroll_action_id = :i_payroll_action_id
1392 AND int.locking_action_id = pa.assignment_action_id
1393 AND int.locked_action_id = pa2.assignment_action_id
1394 AND pa2.payroll_action_id = :c_cost_action_id
1395 GROUP BY pgl.cost_allocation_keyflex_id,
1396 decode(pgl.entered_dr, 0, 0, 1),
1397 nvl(pgl.accounting_date, :c_accounting_date),
1398 pgl.currency_code',
1399 dbms_sql.v7);
1400 else
1401 dbms_sql.parse(sql_curs,
1402 'INSERT INTO gl_interface
1403 (status,
1404 set_of_books_id,
1405 user_je_source_name,
1406 user_je_category_name,
1407 accounting_date,
1408 currency_code,
1409 group_id,
1410 date_created,
1411 created_by,
1412 actual_flag,'||
1413 l_gl_segment_list||'
1414 code_combination_id,
1415 reference21,
1416 reference22,
1417 reference23,
1418 reference24,
1419 reference25,
1420 user_currency_conversion_type,
1421 currency_conversion_date,
1422 entered_dr,
1423 entered_cr)
1424 SELECT /*+ ORDERED
1425 INDEX(paf PER_ASSIGNMENTS_F_PK)
1426 INDEX(int PAY_ACTION_INTERLOCKS_PK)
1427 INDEX(pgl PAY_GL_INTERFACE_N1)
1428 USE_NL(pgl int pa pa2 paf) */
1429 ''NEW'',
1430 :c_set_of_books_id,
1431 :l_source_name,
1432 :l_category_name,
1433 trunc(nvl(pgl.accounting_date, :c_accounting_date)),
1434 pgl.currency_code,
1435 decode (:l_group_id, ''Y'', :i_payroll_action_id),
1436 trunc(sysdate),
1437 801,
1438 ''A'','||
1439 l_pay_segment_list||'
1440 decode(:c_action_type, ''CP'', pgl.cost_allocation_keyflex_id),
1441 :i_payroll_action_id,
1442 pgl.cost_allocation_keyflex_id,
1443 :c_run_date,
1444 :c_run_date_earned,
1445 :c_run_action_id,
1446 :l_currency_type,
1447 :c_conversion_date,
1448 SUM(pgl.entered_dr),
1449 SUM(pgl.entered_cr)
1450 FROM pay_assignment_actions pa,
1451 '|| l_asg_tab ||'
1452 pay_action_interlocks int,
1453 pay_assignment_actions pa2,
1454 pay_gl_interface pgl,
1455 pay_cost_allocation_keyflex caf
1456 WHERE pgl.run_payroll_action_id = :c_run_action_id
1457 '|| l_asg_join ||'
1458 AND caf.cost_allocation_keyflex_id (+) = pgl.cost_allocation_keyflex_id
1459 AND pa.assignment_action_id = pgl.assignment_action_id
1460 AND pa.payroll_action_id = :i_payroll_action_id
1461 AND int.locking_action_id = pa.assignment_action_id
1462 AND int.locked_action_id = pa2.assignment_action_id
1463 AND pa2.payroll_action_id = :c_cost_action_id
1464 GROUP BY pgl.cost_allocation_keyflex_id,
1465 decode(pgl.entered_dr, 0, 0, 1),
1466 nvl(pgl.accounting_date, :c_accounting_date),
1467 pgl.currency_code',
1468 dbms_sql.v7);
1469 end if;
1470 --
1471 hr_utility.set_location('pytrgl.trans_pay_costs_mt',80);
1472 --
1473 -- Bind the variable values to the cursor values.
1474 --
1475 dbms_sql.bind_variable(sql_curs,'c_run_action_id',
1476 c_run_action_id);
1477 dbms_sql.bind_variable(sql_curs,'c_cost_action_id',
1478 c_cost_action_id);
1479 dbms_sql.bind_variable(sql_curs,'c_action_type',
1480 c_action_type);
1481 dbms_sql.bind_variable(sql_curs,'c_accounting_date',
1482 c_accounting_date);
1483 dbms_sql.bind_variable(sql_curs,'c_run_date',
1484 c_run_date);
1485 dbms_sql.bind_variable(sql_curs,'c_run_date_earned',
1486 c_run_date_earned);
1487 dbms_sql.bind_variable(sql_curs,'c_set_of_books_id',
1488 c_set_of_books_id);
1489 dbms_sql.bind_variable(sql_curs,'i_payroll_action_id',
1490 i_payroll_action_id);
1491 dbms_sql.bind_variable(sql_curs,'l_source_name',
1492 l_source_name);
1493 dbms_sql.bind_variable(sql_curs,'l_category_name',
1494 l_category_name);
1495 dbms_sql.bind_variable(sql_curs,'l_group_id',
1496 l_group_id);
1497 dbms_sql.bind_variable(sql_curs,'l_currency_type', l_currency_type);
1498 dbms_sql.bind_variable(sql_curs,'c_conversion_date',
1499 c_conversion_date);
1500
1501 /* Bug 7401269: Variable wasn't bound for costing of payments.
1502 Added an OR condition for binding variable in case of
1503 costing of payment run before transfer to GL */
1504
1505 if ((c_action_type = 'EC' or c_action_type = 'CP') and
1506 c_pay_id is null) then
1507 dbms_sql.bind_variable(sql_curs,'c_payroll_id',
1508 c_payroll_id);
1509 end if;
1510 --
1511 -- Execute the insert statment.
1512 --
1513 rows_processed := dbms_sql.execute(sql_curs);
1514 --
1515 hr_utility.set_location('pytrgl.trans_pay_costs_mt',90);
1516 --
1517 END LOOP;
1518 --
1519 hr_utility.set_location('pytrgl.trans_pay_costs_mt',100);
1520 --
1521 CLOSE transfer_payrolls;
1522 --
1523 dbms_sql.close_cursor(sql_curs);
1524 --
1525 hr_utility.set_location('pytrgl.trans_pay_costs_mt',110);
1526 --
1527 COMMIT;
1528 --
1529 hr_utility.set_location('pytrgl.trans_pay_costs_mt',120);
1530 --
1531 END trans_pay_costs_mt;
1532 --
1533 PROCEDURE trans_ass_costs
1534 (i_assignment_action_id NUMBER,
1535 sla_mode NUMBER)
1536 IS
1537 t_payroll_action_id NUMBER;
1538 c_assignment_action_id NUMBER;
1539 r_assignment_action_id NUMBER;
1540 c_payroll_action_id NUMBER;
1541 r_payroll_action_id NUMBER;
1542 r_action_type pay_payroll_actions.action_type%TYPE;
1543 r_assignment_id NUMBER;
1544 r_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
1545 c_action_type pay_payroll_actions.action_type%TYPE;
1546 l_date_used VARCHAR2(80);
1547 l_accounting_date DATE;
1548 BEGIN
1549 --
1550 hr_utility.set_location('pytrgl.trans_ass_costs',10);
1551 --
1552 if (sla_mode = 1) then
1553 --
1554 hr_utility.set_location('pytrgl.trans_ass_costs',14);
1555 --
1556 pay_sla_pkg.trans_asg_costs(i_assignment_action_id);
1557 --
1558 hr_utility.set_location('pytrgl.trans_ass_costs',16);
1559 --
1560 else
1561 --
1562 hr_utility.set_location('pytrgl.trans_ass_costs',18);
1563 --
1564 SELECT pa.payroll_action_id,
1565 pa1.assignment_id,
1566 pa1.assignment_action_id,
1567 ppa1.payroll_action_id,
1568 ppa1.action_type
1569 INTO t_payroll_action_id,
1570 r_assignment_id,
1571 c_assignment_action_id,
1572 c_payroll_action_id,
1573 c_action_type
1574 FROM pay_assignment_actions pa, -- TGL assignment action
1575 pay_action_interlocks pi, -- interlock to cost
1576 pay_assignment_actions pa1, -- cost assignment action
1577 pay_payroll_actions ppa1 -- cost payroll action
1578 WHERE pa.assignment_action_id = i_assignment_action_id
1579 AND pi.locking_action_id = pa.assignment_action_id
1580 AND pa1.assignment_action_id = pi.locked_action_id
1581 AND ppa1.payroll_action_id = pa1.payroll_action_id
1582 AND ppa1.action_type IN ('C', 'S', 'EC', 'CP');
1583 --
1584 if (c_action_type IN ('C', 'S')) then
1585 --
1586 -- Costing or Retrocostong hence know run result populated
1587 -- in pay_costs.
1588 --
1589 hr_utility.set_location('pytrgl.trans_ass_costs',20);
1590 --
1591 SELECT pa2.assignment_action_id,
1592 ppa2.payroll_action_id,
1593 ppa2.action_type,
1594 pa2.tax_unit_id
1595 INTO r_assignment_action_id,
1596 r_payroll_action_id,
1597 r_action_type,
1598 r_tax_unit_id
1599 FROM pay_assignment_actions pa, -- TGL assignment action
1600 pay_action_interlocks pi2, -- interlock to run
1601 pay_assignment_actions pa2, -- run assignment action
1602 pay_payroll_actions ppa2 -- run payroll action
1603 WHERE pa.assignment_action_id = i_assignment_action_id
1604 AND pi2.locking_action_id = pa.assignment_action_id
1605 AND pa2.assignment_action_id = pi2.locked_action_id
1606 AND ppa2.payroll_action_id = pa2.payroll_action_id
1607 AND ppa2.action_type NOT IN ('C', 'S', 'EC');
1608 --
1609 hr_utility.set_location('pytrgl.trans_ass_costs',30);
1610 --
1611 INSERT INTO pay_gl_interface
1612 ( assignment_action_id,
1613 run_payroll_action_id,
1614 cost_allocation_keyflex_id,
1615 currency_code,
1616 entered_dr,
1617 entered_cr)
1618 SELECT /*+ ORDERED USE_NL(cst rr) */
1619 i_assignment_action_id,
1620 r_payroll_action_id,
1621 cst.cost_allocation_keyflex_id,
1622 decode (IV.uom, 'M', et.output_currency_code, 'STAT'),
1623 SUM(DECODE(cst.debit_or_credit,'D',cst.costed_value,0)),
1624 SUM(DECODE(cst.debit_or_credit,'C',cst.costed_value,0))
1625 FROM pay_payroll_actions ppa, -- run payroll action
1626 pay_assignment_actions raa,
1627 pay_costs cst,
1628 pay_run_results rr,
1629 pay_element_types_f et,
1630 pay_input_values_f IV
1631 WHERE cst.assignment_action_id = c_assignment_action_id
1632 AND raa.payroll_action_id = r_payroll_action_id
1633 AND raa.assignment_id = r_assignment_id
1634 AND decode(r_action_type, 'B', nvl(raa.tax_unit_id, -999),
1635 -999) =
1636 decode(r_action_type, 'B', nvl(r_tax_unit_id, -999),
1637 -999)
1638 AND rr.assignment_action_id = raa.assignment_action_id
1639 AND ppa.payroll_action_id = r_payroll_action_id
1640 AND cst.run_result_id = rr.run_result_id
1641 AND RR.element_type_id = IV.element_type_id
1642 AND ET.element_type_id = RR.element_type_id
1643 AND ppa.date_earned between et.effective_start_date
1644 and et.effective_end_date
1645 AND IV.input_value_id = CST.input_value_id
1646 AND ppa.date_earned between IV.effective_start_date
1647 and IV.effective_end_date
1648 AND ((CST.transfer_to_gl_flag is not null
1649 AND CST.transfer_to_gl_flag = 'Y')
1650 OR
1651 (CST.transfer_to_gl_flag is null
1652 AND EXISTS
1653 (select RR1.run_result_id
1654 from pay_run_results RR1
1655 , pay_run_results RR2
1656 , pay_element_entries_f EE1
1657 , pay_element_links_f EL1
1658 where RR1.assignment_action_id = raa.assignment_action_id
1659 and RR1.source_id = RR2.run_result_id
1660 and RR1.source_type = 'R'
1661 and RR2.source_id = EE1.element_entry_id
1662 and RR2.source_type = 'E'
1663 and least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
1664 between EE1.effective_start_date
1665 and EE1.effective_end_date
1666 and EL1.element_link_id = EE1.element_link_id
1667 and ppa.date_earned between EL1.effective_start_date
1668 and EL1.effective_end_date
1669 and EL1.transfer_to_gl_flag = 'Y'
1670 and RR1.run_result_id = nvl(CST.distributed_run_result_id,
1671 CST.run_result_id)
1672 UNION ALL
1673 select RR1.run_result_id
1674 from pay_run_results RR1
1675 , pay_element_entries_f EE1
1676 , pay_element_links_f EL1
1677 where RR1.assignment_action_id = raa.assignment_action_id
1678 and decode(r_action_type, 'B', nvl(RR1.element_entry_id, RR1.source_id),
1679 RR1.source_id)
1680 = EE1.element_entry_id
1681 and RR1.source_type = 'E'
1682 and least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
1683 between EE1.effective_start_date
1684 and EE1.effective_end_date
1685 and EL1.element_link_id = EE1.element_link_id
1686 and ppa.date_earned between EL1.effective_start_date
1687 and EL1.effective_end_date
1688 and EL1.transfer_to_gl_flag = 'Y'
1689 and RR1.run_result_id = nvl(CST.distributed_run_result_id,
1690 CST.run_result_id)
1691 UNION ALL
1692 select /*+ ORDERED */
1693 RR1.run_result_id
1694 FROM pay_run_results RR1,
1695 pay_element_types_f ET1,
1696 pay_element_links_f EL1,
1697 per_all_assignments_f PERA
1698 WHERE RR1.assignment_action_id = raa.assignment_action_id
1699 AND RR1.run_result_id = nvl(CST.distributed_run_result_id,
1700 CST.run_result_id)
1701 AND (
1702 (NOT EXISTS /* look for deleted element entries */
1703 (SELECT null
1704 FROM pay_element_entries_f EE1
1705 WHERE RR1.source_id = EE1.element_entry_id
1706 AND RR1.source_type IN ('E', 'I')
1707 AND least(nvl(RR1.end_date, ppa.date_earned), ppa.date_earned)
1708 BETWEEN EE1.effective_start_date
1709 AND EE1.effective_end_date
1710 )
1711 AND NOT EXISTS
1712 (SELECT null
1713 FROM pay_element_entries_f EE1,
1714 pay_run_results RR2
1715 WHERE RR2.source_id = EE1.element_entry_id
1716 AND RR1.source_type IN ('R', 'V')
1717 AND RR1.source_id = RR2.run_result_id
1718 AND least(nvl(RR2.end_date, ppa.date_earned), ppa.date_earned)
1719 BETWEEN EE1.effective_start_date
1720 AND EE1.effective_end_date
1721 ))
1722 OR RR1.source_type IN ('I', 'V')
1723 )
1724 AND RR1.assignment_action_id = raa.assignment_action_id
1725 AND RR1.element_type_id = ET1.element_type_id
1726 AND ppa.date_earned BETWEEN ET1.effective_start_date
1727 AND ET1.effective_end_date
1728 AND ET1.element_type_id = EL1.element_type_id
1729 AND ppa.effective_date BETWEEN EL1.effective_start_date
1730 AND EL1.effective_end_date
1731 AND EL1.costable_type <> 'N'
1732 AND EL1.transfer_to_gl_flag = 'Y'
1733 AND PERA.assignment_id = r_assignment_id
1734 AND PERA.business_group_id = EL1.business_group_id +0
1735 AND ppa.date_earned BETWEEN PERA.effective_start_date
1736 AND PERA.effective_end_date
1737 AND ((EL1.payroll_id IS NOT NULL
1738 AND EL1.payroll_id = PERA.payroll_id)
1739 OR (EL1.link_to_all_payrolls_flag = 'Y'
1740 AND PERA.payroll_id IS NOT NULL)
1741 OR EL1.payroll_id IS NULL)
1742 AND (EL1.organization_id = PERA.organization_id
1743 OR EL1.organization_id IS NULL)
1744 AND (EL1.position_id = PERA.position_id
1745 OR EL1.position_id IS NULL)
1746 AND (EL1.job_id = PERA.job_id
1747 OR EL1.job_id IS NULL)
1748 AND (EL1.grade_id = PERA.grade_id
1749 OR EL1.grade_id IS NULL)
1750 AND (EL1.location_id = PERA.location_id
1751 OR EL1.location_id IS NULL)
1752 AND (EL1.pay_basis_id = PERA.pay_basis_id
1753 OR EL1.pay_basis_id IS NULL)
1754 AND (EL1.employment_category = PERA.employment_category
1755 OR EL1.employment_category IS NULL)
1756 AND (EL1.people_group_id IS NULL
1757 OR EXISTS
1758 (SELECT 1
1759 FROM pay_assignment_link_usages_f PAL
1760 WHERE PAL.assignment_id = PERA.assignment_id
1761 AND PAL.element_link_id = EL1.element_link_id
1762 AND ppa.date_earned BETWEEN PAL.effective_start_date
1763 AND PAL.effective_end_date))
1764 )))
1765 GROUP BY cst.cost_allocation_keyflex_id,
1766 cst.debit_or_credit,
1767 decode (IV.uom, 'M', et.output_currency_code, 'STAT');
1768 --
1769 hr_utility.set_location('pytrgl.trans_ass_costs',40);
1770 --
1771 elsif (c_action_type = 'EC') then
1772 --
1773 -- Estimate Costing hence run result not populated
1774 -- in pay_costs.
1775 --
1776 hr_utility.set_location('pytrgl.trans_ass_costs',50);
1777 --
1778 --
1779 -- Find if use an accouting date of date_earned
1780 -- (default is effective_date)
1781 --
1782 if (g_date_used is null) then
1783 begin
1784 select parameter_value
1785 into g_date_used
1786 from pay_action_parameters
1787 where parameter_name = 'TGL_DATE_USED';
1788 exception
1789 when others then
1790 g_date_used := 'P';
1791 end;
1792 end if;
1793 l_date_used := g_date_used;
1794 --
1795 hr_utility.set_location('pytrgl.trans_pay_costs',29);
1796 --
1797 -- get accounting_date according to TGL_DATE_USED
1798 -- for use with the reversal costs
1799 --
1800 SELECT /*+ ORDERED */
1801 decode(l_date_used, 'E', ptp.end_date,
1802 'EVE', ptp.end_date,
1803 ptp.pay_advice_date + pay.pay_date_offset)
1804 INTO l_accounting_date
1805 FROM pay_payroll_actions ppa,
1806 per_all_assignments_f pera,
1807 pay_all_payrolls_f pay,
1808 per_time_periods ptp
1809 WHERE ppa.payroll_action_id = c_payroll_action_id
1810 AND pera.assignment_id = r_assignment_id
1811 AND ppa.effective_date BETWEEN pera.effective_start_date
1812 AND pera.effective_end_date
1813 AND pay.payroll_id = pera.payroll_id
1814 AND ppa.effective_date BETWEEN pay.effective_start_date
1815 AND pay.effective_end_date
1816 AND ptp.payroll_id = pera.payroll_id
1817 AND ppa.effective_date BETWEEN ptp.start_date
1818 AND ptp.end_date;
1819 --
1820 hr_utility.set_location('pytrgl.trans_ass_costs',50);
1821 --
1822 INSERT INTO pay_gl_interface
1823 ( assignment_action_id,
1824 run_payroll_action_id,
1825 cost_allocation_keyflex_id,
1826 currency_code,
1827 entered_dr,
1828 entered_cr,
1829 accounting_date)
1830 SELECT /*+ ORDERED USE_NL(cst) */
1831 i_assignment_action_id,
1832 c_payroll_action_id,
1833 cst.cost_allocation_keyflex_id,
1834 decode (IV.uom, 'M', et.output_currency_code, 'STAT'),
1835 SUM(DECODE(cst.debit_or_credit,'D',cst.costed_value,0)),
1836 SUM(DECODE(cst.debit_or_credit,'C',cst.costed_value,0)),
1837 decode (CST.source_id, null, null, l_accounting_date)
1838 FROM pay_payroll_actions ppa, -- TGL payroll action
1839 pay_costs cst,
1840 pay_input_values_f IV,
1841 pay_element_types_f et
1842 WHERE ppa.payroll_action_id = t_payroll_action_id
1843 AND cst.assignment_action_id = c_assignment_action_id
1844 AND IV.input_value_id = nvl(CST.distributed_input_value_id,
1845 CST.input_value_id)
1846 AND ppa.effective_date BETWEEN IV.effective_start_date
1847 AND IV.effective_end_date
1848 AND ET.element_type_id = IV.element_type_id
1849 AND ppa.effective_date BETWEEN ET.effective_start_date
1850 AND ET.effective_end_date
1851 AND ((CST.transfer_to_gl_flag is not null
1852 AND CST.transfer_to_gl_flag = 'Y')
1853 OR
1854 (CST.transfer_to_gl_flag is null
1855 AND EXISTS
1856 (select /*+ ORDERED */
1857 1
1858 FROM per_all_assignments_f PERA,
1859 pay_element_links_f EL1
1860 WHERE EL1.element_type_id = ET.element_type_id
1861 AND ppa.effective_date BETWEEN EL1.effective_start_date
1862 AND EL1.effective_end_date
1863 AND EL1.costable_type <> 'N'
1864 AND EL1.transfer_to_gl_flag = 'Y'
1865 AND PERA.assignment_id = r_assignment_id
1866 AND PERA.business_group_id = EL1.business_group_id +0
1867 AND ppa.effective_date BETWEEN PERA.effective_start_date
1868 AND PERA.effective_end_date
1869 AND ((EL1.payroll_id IS NOT NULL
1870 AND EL1.payroll_id = PERA.payroll_id)
1871 OR (EL1.link_to_all_payrolls_flag = 'Y'
1872 AND PERA.payroll_id IS NOT NULL)
1873 OR EL1.payroll_id IS NULL)
1874 AND (EL1.organization_id = PERA.organization_id
1875 OR EL1.organization_id IS NULL)
1876 AND (EL1.position_id = PERA.position_id
1877 OR EL1.position_id IS NULL)
1878 AND (EL1.job_id = PERA.job_id
1879 OR EL1.job_id IS NULL)
1880 AND (EL1.grade_id = PERA.grade_id
1881 OR EL1.grade_id IS NULL)
1882 AND (EL1.location_id = PERA.location_id
1883 OR EL1.location_id IS NULL)
1884 AND (EL1.pay_basis_id = PERA.pay_basis_id
1885 OR EL1.pay_basis_id IS NULL)
1886 AND (EL1.employment_category = PERA.employment_category
1887 OR EL1.employment_category IS NULL)
1888 AND (EL1.people_group_id IS NULL
1889 OR EXISTS
1890 (SELECT 1
1891 FROM pay_assignment_link_usages_f PAL
1892 WHERE PAL.assignment_id = PERA.assignment_id
1893 AND PAL.element_link_id = EL1.element_link_id
1894 AND ppa.effective_date BETWEEN PAL.effective_start_date
1895 AND PAL.effective_end_date))
1896 )))
1897 GROUP BY cst.cost_allocation_keyflex_id,
1898 cst.debit_or_credit,
1899 decode (IV.uom, 'M', et.output_currency_code, 'STAT'),
1900 decode (CST.source_id, null, null, l_accounting_date);
1901 --
1902 hr_utility.set_location('pytrgl.trans_ass_costs',60);
1903 --
1904 else
1905 --
1906 -- Payment Costing processes
1907 --
1908 INSERT INTO pay_gl_interface
1909 ( assignment_action_id,
1910 run_payroll_action_id,
1911 cost_allocation_keyflex_id,
1912 currency_code,
1913 entered_dr,
1914 entered_cr,
1915 accounting_date)
1916 SELECT i_assignment_action_id,
1917 c_payroll_action_id,
1918 ppc.account_id,
1919 ppc.currency_code,
1920 SUM(DECODE(ppc.debit_or_credit,'D',ppc.value,0)),
1921 SUM(DECODE(ppc.debit_or_credit,'C',ppc.value,0)),
1922 ppc.accounting_date
1923 FROM pay_payment_costs ppc
1924 WHERE ppc.assignment_action_id = c_assignment_action_id
1925 AND ppc.transfer_to_gl_flag = 'Y'
1926 AND (ppc.source_type not in ('P', 'U')
1927 OR (ppc.source_type in ('P', 'U')
1928 AND NOT EXISTS
1929 (SELECT 1
1930 FROM pay_assignment_actions aa,
1931 pay_payroll_actions pa,
1932 pay_pre_payments ppp,
1933 pay_org_payment_methods_f pom
1934 WHERE aa.pre_payment_id = ppc.pre_payment_id
1935 AND pa.payroll_action_id = aa.payroll_action_id
1936 AND pa.action_type = 'E'
1937 AND ppp.pre_payment_id = ppc.pre_payment_id
1938 AND pom.org_payment_method_id = ppp.org_payment_method_id
1939 AND pa.effective_date BETWEEN pom.effective_start_date
1940 AND pom.effective_end_date
1941 AND pom.exclude_manual_payment = 'Y')))
1942 GROUP BY ppc.accounting_date,
1943 ppc.account_id,
1944 ppc.debit_or_credit,
1945 ppc.currency_code;
1946 --
1947 hr_utility.set_location('pytrgl.trans_ass_costs',70);
1948 --
1949 end if;
1950 --
1951 end if;
1952 --
1953 END trans_ass_costs;
1954 --
1955 END pay_trgl_pkg;
[Home] [Help]