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