[Home] [Help]
PACKAGE BODY: APPS.PSP_ST_EXT
Source
1 PACKAGE BODY PSP_ST_EXT as
2 /* $Header: PSPTREXB.pls 120.6 2011/07/20 04:47:00 lkodaman ship $ */
3
4 /* PROCEDURE summary_extension AS
5 BEGIN
6 -- Write the user extension here
7 null;
8 END summary_extension; */
9
10
11 --- 2968684: commented above procedure and created following 4 procedures.
12 --
13 -- WARNING:
14 -- 1) Please note that any PL/SQL statements that cause Commit/Rollback
15 -- are not allowed in the user extension code. Commit/Rollback's
16 -- will interfere with the Commit cycle of the main
17 -- process and Restart/Recover process will not work properly.
18 --
19 -- 2) If you raise user defined exception in any of the user hook (except
20 -- in function get_enc_amount) please populate the error string g_api_error_path,
21 -- this helps in trouble shooting in case of problems. See the example code
22 -- below. You cannot use the variable g_error_api_path in function get_enc_amount,
23 -- since the variable is not defined in the scope of this function.
24 --
25 -- Example code to show usage of "g_error_api_path"
26 -- Procedure summary_ext_actual
27 -- -------------------------------------------------------
28 -- :
29 -- :
30 -- if <CONDITION> then
31 -- raise <USER_DEFINED_EXCEPTION>
32 -- end if;
33 -- :
34 -- :
35 -- exception
36 -- when <USER_DEFINED_EXCEPTION> then
37 -- psp_sum_trans.g_error_api_path := 'SUMMARY_EXT_ACTUAL'||psp_sum_trans.g_error_api_path;
38 -- raise;
39 -- when others then
40 -- psp_sum_trans.g_error_api_path := 'SUMMARY_EXT_ACTUAL'||psp_sum_trans.g_error_api_path;
41 -- fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','SUMMARY_EXT_ADJUSTMENT');
42 -- raise;
43 -- end;
44 -- ------------------------------------------------------
45 --
46 --
47 --
48
49 --====== Extension for Summarize And Transfer Adjustments =======
50 procedure summary_ext_adjustment(p_adj_sum_batch_name IN VARCHAR2,
51 p_business_group_id IN NUMBER,
52 p_set_of_books_id IN NUMBER) as
53 begin
54 -- Write the user extension code here;
55 --
56 -- The parameter "p_adj_sum_batch_name" has the batch name entered
57 -- while submitting the Summarize and Transfer Adjustments process.
58 null;
59 exception
60 when others then
61 psp_sum_adj.g_error_api_path := 'SUMMARY_EXT_ADJUSTMENT'||psp_sum_adj.g_error_api_path;
62 fnd_msg_pub.add_exc_msg('PSP_SUM_ADJ','SUMMARY_EXT_ADJUSTMENT');
63 raise;
64 end;
65
66
67 ---===== Extension for Summarize and Transfer Payroll Distributions =======
68 procedure summary_ext_actual(p_source_type IN VARCHAR2,
69 p_source_code IN VARCHAR2,
70 p_payroll_id IN NUMBER,
71 p_time_period_id IN NUMBER,
72 p_batch_name IN VARCHAR2,
73 p_business_group_id IN NUMBER,
74 p_set_of_books_id IN NUMBER) as
75 begin
76 -- Write the user extension code here;
77 --
78 -- parameters:
79 -- p_source_type is 'O' for oracle, 'N' for Non-Oracle, 'P' for Pre-Gen
80 -- p_source_code is user defined source code for the source type
81 -- p_payroll_id is payroll id.
82 -- p_time_period_id is time period id.
83 -- p_batch_name is Non-Oracle or Pre-gen Batch name
84 --
85 -- WARNING: Only 2 parameters (business group, set of books) are always guaranteed
86 -- to be NOT NULL, rest of the paramters may have null values.
87 --
88 -- For example if user submits the process without entering any parameters,
89 -- then only the implicit parameters business group, and set of books are
90 -- available, all the remaining parameters have null values.
91 --
92 --
93 null;
94 exception
95 when others then
96 psp_sum_trans.g_error_api_path := 'SUMMARY_EXT_ACTUAL'||psp_sum_trans.g_error_api_path;
97 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','SUMMARY_EXT_ADJUSTMENT');
98 raise;
99 end;
100
101 --======= Extension for Summarize and Transfer Encumbrances =====
102 procedure summary_ext_encumber(p_payroll_id IN NUMBER,
103 p_business_group_id IN NUMBER,
104 p_set_of_books_id IN NUMBER) as
105 begin
106 -- Write the user extension code here;
107 null;
108 exception
109 when others then
110 psp_enc_sum_tran.g_error_api_path := 'SUMMARY_EXT_ENCUMBER'||psp_enc_sum_tran.g_error_api_path;
111 fnd_msg_pub.add_exc_msg('PSP_ENC_SUM_TRAN','SUMMARY_EXT_ENCUMBER');
112 raise;
113 end;
114
115 --====== Extension for Encumbrance Liquidation ==============
116 procedure summary_ext_encumber_liq(p_payroll_id IN NUMBER,
117 p_action_type IN VARCHAR2,
118 p_business_group_id IN NUMBER,
119 p_set_of_books_id IN NUMBER) as
120 begin
121 -- Write the user extension code here;
122 --
123 -- parameter p_action_type has value L for Liquidation,
124 -- U if Liquidation is called from Update Encumbrance
125 -- Q if Liquidation is called from Quick Update Encumbrance
126 null;
127 exception
128 when others then
129 psp_enc_liq_tran.g_error_api_path := 'SUMMARY_EXT_ENCUMBER_LIQ'||psp_enc_liq_tran.g_error_api_path;
130 fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','SUMMARY_EXT_ENCUMBER_LIQ');
131 raise;
132 end;
133
134
135 FUNCTION get_enc_amount( p_assignment_id IN NUMBER ,
136 p_element_type_id IN NUMBER,
137 p_time_period_id IN NUMBER,
138 p_asg_start_date IN DATE,
139 p_asg_end_date IN DATE )
140
141 return NUMBER IS
142 BEGIN
143 Return (0);
144 -- Write the user extension here
145 -- It is mandatory for the function to return a NOT NULL value.
146 exception
147 when others then
148 fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','SUMMARY_EXT_ENCUMBER_LIQ');
149 raise;
150 END get_enc_amount;
151
152
153 PROCEDURE get_labor_enc_dates (p_project_id IN NUMBER,
154 p_task_id IN NUMBER,
155 p_award_id IN NUMBER,
156 p_expenditure_type IN VARCHAR2,
157 p_expenditure_organization_id IN NUMBER,
158 p_payroll_id IN NUMBER,
159 p_start_date OUT NOCOPY DATE,
160 p_end_date OUT NOCOPY DATE) IS
161 BEGIN
162 p_start_date := fnd_date.canonical_to_date('1800/01/01');
163 p_end_date := fnd_date.canonical_to_date('4712/12/31');
164 END get_labor_enc_dates;
165
166 --- added following procedure for 5643110
167 PROCEDURE tieback_actual(p_payroll_control_id IN NUMBER,
168 p_source_type IN VARCHAR2,
169 p_period_end_date IN DATE,
170 p_gms_batch_name IN VARCHAR2,
171 p_txn_source in varchar2,
172 p_business_group_id IN NUMBER,
173 p_set_of_books_id IN NUMBER) is
174 begin
175 -- Write the user extension code here;
176 --
177 null;
178 exception
179 when others then
180 psp_sum_trans.g_error_api_path := 'TIEBACK_ACTUAL'||psp_sum_trans.g_error_api_path;
181 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TIEBACK_ACTUAL');
182 raise;
183 end;
184 /*
185 --- following code is for Award Funding pattern implementation in LD.
186 -- this code should be moved into the tieback_actual procedure.
187
188 l_default_dist_award_id NUMBER;
189 l_summary_line_id NUMBER;
190 l_sum_summary_amt NUMBER;
191 l_sum_dist_amt NUMBER;
192 l_pg_summary_line_id NUMBER;
193 l_pg_sum_summary_amt NUMBER;
194 l_pg_sum_dist_amt NUMBER;
195
196 -- get the summary_line_id's where distribution amount does not match with summary amount
197 CURSOR get_unmatched_summary_lines is
198 SELECT pdl.summary_line_id, psl.summary_amount,
199 sum(pdl.distribution_amount)
200 FROM psp_distribution_lines pdl,
201 psp_summary_lines psl
202 WHERE pdl.summary_line_id = psl.summary_line_id
203 AND psl.payroll_control_id = p_payroll_control_id
204 AND psl.status_code = 'N'
205 AND psl.attribute29 is not null
206 ANd psl.attribute30 is not null
207 GROUP BY pdl.summary_line_id, psl.summary_amount
208 HAVING summary_amount - sum(distribution_amount) <> 0;
209
210 -- get the summary_line_id's where pre gen distribution amount does not match with summary amount
211 CURSOR get_unmatched_pg_summary_lines is
212 SELECT ppgl.summary_line_id, psl.summary_amount,
213 sum(ppgl.distribution_amount)
214 FROM psp_pre_gen_dist_lines ppgl,
215 psp_summary_lines psl
216 WHERE ppgl.summary_line_id = psl.summary_line_id
217 AND psl.payroll_control_id = p_payroll_control_id
218 AND psl.status_code = 'N'
219 AND psl.attribute29 is not null
220 ANd psl.attribute30 is not null
221 GROUP BY ppgl.summary_line_id, psl.summary_amount
222 HAVING summary_amount - sum(distribution_amount) <> 0;
223
224 CURSOR get_unmatched_summary_splits is
225 SELECT to_number(attribute30) old_summary_amount,
226 attribute29 old_summary_line_id,
227 sum(summary_amount) new_summary_amount,
228 max(summary_line_id) new_summary_line_id
229 FROM psp_summary_lines
230 WHERE attribute29 is not null
231 AND attribute30 is not null
232 AND gms_batch_name = p_gms_batch_name
233 AND status_code <> 'S'
234 GROUP BY attribute29, attribute30
235 HAVING sum(summary_amount) <> to_number(attribute30);
236
237 summary_split_rec get_unmatched_summary_splits%rowtype;
238
239 BEGIN
240
241 SELECT default_dist_award_id
242 INTO l_default_dist_award_id
243 FROM gms_implementations;
244
245 -- create new summary lines from PA/GMS interface table for award distribution lines
246 hr_utility.trace('Actual Tieback userhook before inserting into Summary lines');
247
248 INSERT INTO psp_summary_lines
249 ( SUMMARY_LINE_ID,
250 SOURCE_TYPE,
251 SOURCE_CODE,
252 TIME_PERIOD_ID,
253 INTERFACE_BATCH_NAME,
254 PERSON_ID,
255 ASSIGNMENT_ID,
256 EFFECTIVE_DATE,
257 PAYROLL_CONTROL_ID,
258 GL_CODE_COMBINATION_ID,
259 PROJECT_ID,
260 EXPENDITURE_ORGANIZATION_ID,
261 EXPENDITURE_TYPE,
262 TASK_ID,
263 AWARD_ID,
264 SUMMARY_AMOUNT,
265 DR_CR_FLAG,
266 GROUP_ID,
267 INTERFACE_STATUS,
268 ATTRIBUTE_CATEGORY,
269 ATTRIBUTE1,
270 ATTRIBUTE2,
271 ATTRIBUTE3,
272 ATTRIBUTE4,
273 ATTRIBUTE5,
274 ATTRIBUTE6,
275 ATTRIBUTE7,
276 ATTRIBUTE8,
277 ATTRIBUTE9,
278 ATTRIBUTE10,
279 ATTRIBUTE11,
280 ATTRIBUTE12,
281 ATTRIBUTE13,
282 ATTRIBUTE14,
283 ATTRIBUTE15,
284 ATTRIBUTE16,
285 ATTRIBUTE17,
286 ATTRIBUTE18,
287 ATTRIBUTE19,
288 ATTRIBUTE20,
289 ATTRIBUTE21,
290 ATTRIBUTE22,
291 ATTRIBUTE23,
292 ATTRIBUTE24,
293 ATTRIBUTE25,
294 ATTRIBUTE26,
295 ATTRIBUTE27,
296 ATTRIBUTE28,
297 ATTRIBUTE29,
298 ATTRIBUTE30,
299 LAST_UPDATE_DATE,
300 LAST_UPDATED_BY,
301 LAST_UPDATE_LOGIN,
302 CREATED_BY,
303 CREATION_DATE,
304 SET_OF_BOOKS_ID,
305 BUSINESS_GROUP_ID,
306 STATUS_CODE,
307 GMS_BATCH_NAME,
308 GMS_POSTING_EFFECTIVE_DATE,
309 EXPENDITURE_ENDING_DATE ,
310 EXPENDITURE_ID,
311 INTERFACE_ID,
312 EXPENDITURE_ITEM_ID,
313 TXN_INTERFACE_ID,
314 ACTUAL_SUMMARY_AMOUNT,
315 ACCOUNTING_DATE,
316 EXCHANGE_RATE_TYPE)
317 SELECT
318 PSP_SUMMARY_LINES_S.NEXTVAL,
319 psl.SOURCE_TYPE,
320 psl.SOURCE_CODE,
321 psl.TIME_PERIOD_ID,
322 psl.INTERFACE_BATCH_NAME,
323 psl.PERSON_ID,
324 psl.ASSIGNMENT_ID,
325 psl.EFFECTIVE_DATE,
326 psl.PAYROLL_CONTROL_ID,
327 psl.GL_CODE_COMBINATION_ID,
328 psl.PROJECT_ID,
329 psl.EXPENDITURE_ORGANIZATION_ID,
330 psl.EXPENDITURE_TYPE,
331 psl.TASK_ID,
332 gti.AWARD_ID,
333 pti.denom_raw_cost, -- putting new value in the summary_amount column
334 psl.DR_CR_FLAG,
335 psl.GROUP_ID,
336 psl.INTERFACE_STATUS,
337 psl.ATTRIBUTE_CATEGORY,
338 psl.ATTRIBUTE1,
339 psl.ATTRIBUTE2,
340 psl.ATTRIBUTE3,
341 psl.ATTRIBUTE4,
342 psl.ATTRIBUTE5,
343 psl.ATTRIBUTE6,
344 psl.ATTRIBUTE7,
345 psl.ATTRIBUTE8,
346 psl.ATTRIBUTE9,
347 psl.ATTRIBUTE10,
348 psl.ATTRIBUTE11,
349 psl.ATTRIBUTE12,
350 psl.ATTRIBUTE13,
351 psl.ATTRIBUTE14,
352 psl.ATTRIBUTE15,
353 psl.ATTRIBUTE16,
354 psl.ATTRIBUTE17,
355 psl.ATTRIBUTE18,
356 psl.ATTRIBUTE19,
357 psl.ATTRIBUTE20,
358 psl.ATTRIBUTE21,
359 psl.ATTRIBUTE22,
360 psl.ATTRIBUTE23,
361 psl.ATTRIBUTE24,
362 psl.ATTRIBUTE25,
363 psl.ATTRIBUTE26,
364 psl.ATTRIBUTE27,
365 psl.ATTRIBUTE28,
366 psl.SUMMARY_LINE_ID, -- storing in attribute29
367 psl.SUMMARY_AMOUNT, -- storing in attribute30
368 psl.LAST_UPDATE_DATE,
369 psl.LAST_UPDATED_BY,
370 psl.LAST_UPDATE_LOGIN,
371 psl.CREATED_BY,
372 psl.CREATION_DATE,
373 psl.SET_OF_BOOKS_ID,
374 psl.BUSINESS_GROUP_ID,
375 psl.STATUS_CODE,
376 psl.GMS_BATCH_NAME,
377 psl.GMS_POSTING_EFFECTIVE_DATE,
378 psl.EXPENDITURE_ENDING_DATE ,
379 psl.EXPENDITURE_ID,
380 psl.INTERFACE_ID,
381 psl.EXPENDITURE_ITEM_ID,
382 pti.TXN_INTERFACE_ID, -- insert the new txn_reference_id from pa_transaction_interface_all
383 psl.ACTUAL_SUMMARY_AMOUNT,
384 psl.ACCOUNTING_DATE,
385 psl.EXCHANGE_RATE_TYPE
386 FROM pa_transaction_interface_all pti,
387 gms_transaction_interface_all gti,
388 psp_summary_lines psl
389 WHERE pti.txn_interface_id = gti.txn_interface_id
390 AND pti.orig_transaction_reference = psl.summary_line_id
391 AND psl.award_id = l_default_dist_award_id
392 AND pti.batch_name = p_gms_batch_name
393 AND pti.transaction_source = 'GOLD';
394
395 --- subsequent code applicable only if Default Award is involved.
396 if sql%rowcount > 0 then
397
398 UPDATE psp_summary_lines
399 SET status_code = 'S'
400 WHERE award_id = l_default_dist_award_id
401 AND summary_line_id IN (SELECT orig_transaction_reference
402 FROM pa_transaction_interface_all pti
403 WHERE pti.batch_name = p_gms_batch_name
404 AND pti.transaction_source = 'GOLD');
405
406 hr_utility.trace('summary line split rounding fixes');
407 open get_unmatched_summary_splits;
408 hr_utility.trace('summary line split rounding fixes-2');
409 loop
410 fetch get_unmatched_summary_splits into summary_split_rec;
411 if get_unmatched_summary_splits%notfound then
412 close get_unmatched_summary_splits;
413 exit;
414 end if;
415 update psp_summary_lines
416 set summary_amount = summary_split_rec.old_summary_amount - summary_split_rec.new_summary_amount + summary_amount
417 where summary_line_id = summary_split_rec.new_summary_line_id;
418 end loop;
419
420
421 hr_utility.trace('Updating xface table');
422
423 -- update the new orig_transaction_reference in pa_transaction_interface_all table
424 -- gms_tie_back procedure in Summarize and Transfer process expects one record in pa/gms interface table for one summary line
425 UPDATE pa_transaction_interface_all pti
426 SET pti.orig_transaction_reference = (SELECT summary_line_id
427 FROM psp_summary_lines psl
428 WHERE psl.txn_interface_id = pti.txn_interface_id
429 AND psl.status_code = 'N'
430 AND psl.gms_batch_name = p_gms_batch_name)
431 WHERE pti.batch_name = p_gms_batch_name
432 AND pti.transaction_source = 'GOLD'
433 AND pti.orig_transaction_reference in (SELECT to_char(summary_line_id)
434 FROM psp_summary_lines
435 WHERE status_code = 'S'
436 AND gms_batch_name = p_gms_batch_name) ;
437 hr_utility.trace('After Updating xface table');
438
439 --create distribution lines for the new summary lines if source_type = 'O' or 'N'
440
441 INSERT INTO PSP_DISTRIBUTION_LINES(
442 DISTRIBUTION_LINE_ID,
443 DISTRIBUTION_DATE,
444 EFFECTIVE_DATE,
445 DISTRIBUTION_AMOUNT,
446 STATUS_CODE,
447 DEFAULT_REASON_CODE,
448 SUSPENSE_REASON_CODE,
449 EFFORT_REPORT_ID,
450 PAYROLL_SUB_LINE_ID,
451 SCHEDULE_LINE_ID,
452 DEFAULT_ORG_ACCOUNT_ID,
453 SUSPENSE_ORG_ACCOUNT_ID,
454 ELEMENT_ACCOUNT_ID,
455 ORG_SCHEDULE_ID,
456 GL_PROJECT_FLAG,
457 REVERSAL_ENTRY_FLAG,
458 PRE_DISTRIBUTION_RUN_FLAG,
459 SUMMARY_LINE_ID,
460 SET_OF_BOOKS_ID,
461 VERSION_NUM,
462 USER_DEFINED_FIELD,
463 AUTO_EXPENDITURE_TYPE,
464 AUTO_GL_CODE_COMBINATION_ID,
465 BUSINESS_GROUP_ID,
466 ATTRIBUTE_CATEGORY,
467 ATTRIBUTE1,
468 ATTRIBUTE2,
469 ATTRIBUTE3,
470 ATTRIBUTE4,
471 ATTRIBUTE5,
472 ATTRIBUTE6,
473 ATTRIBUTE7,
474 ATTRIBUTE8,
475 ATTRIBUTE9,
476 ATTRIBUTE10,
477 CAP_EXCESS_DIST_LINE_ID,
478 CAP_EXCESS_GLCCID,
479 CAP_EXCESS_AWARD_ID,
480 CAP_EXCESS_PROJECT_ID,
481 CAP_EXCESS_TASK_ID,
482 CAP_EXCESS_EXP_ORG_ID,
483 CAP_EXCESS_EXP_TYPE,
484 FUNDING_SOURCE_CODE,
485 ANNUAL_SALARY_CAP,
486 SUSPENSE_AUTO_GLCCID,
487 SUSPENSE_AUTO_EXP_TYPE,
488 ADJ_ACCOUNT_FLAG)
489 SELECT
490 PSP_DISTRIBUTION_LINES_S.NEXTVAL,
491 pdl.DISTRIBUTION_DATE,
492 pdl.EFFECTIVE_DATE,
493 decode(psl.attribute30,0,0,((pdl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT)/psl.ATTRIBUTE30)),
494 --pdl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30, --Bug 12761630
495 pdl.STATUS_CODE,
496 pdl.DEFAULT_REASON_CODE,
497 pdl.SUSPENSE_REASON_CODE,
498 pdl.EFFORT_REPORT_ID,
499 pdl.PAYROLL_SUB_LINE_ID,
500 pdl.SCHEDULE_LINE_ID,
501 pdl.DEFAULT_ORG_ACCOUNT_ID,
502 pdl.SUSPENSE_ORG_ACCOUNT_ID,
503 pdl.ELEMENT_ACCOUNT_ID,
504 pdl.ORG_SCHEDULE_ID,
505 pdl.GL_PROJECT_FLAG,
506 pdl.REVERSAL_ENTRY_FLAG,
507 pdl.PRE_DISTRIBUTION_RUN_FLAG,
508 psl.SUMMARY_LINE_ID,
509 pdl.SET_OF_BOOKS_ID,
510 pdl.VERSION_NUM,
511 pdl.USER_DEFINED_FIELD,
512 pdl.AUTO_EXPENDITURE_TYPE,
513 pdl.AUTO_GL_CODE_COMBINATION_ID,
514 pdl.BUSINESS_GROUP_ID,
515 pdl.ATTRIBUTE_CATEGORY,
516 pdl.ATTRIBUTE1,
517 pdl.ATTRIBUTE2,
518 pdl.ATTRIBUTE3,
519 pdl.ATTRIBUTE4,
520 pdl.ATTRIBUTE5,
521 pdl.ATTRIBUTE6,
522 pdl.ATTRIBUTE7,
523 pdl.ATTRIBUTE8,
524 pdl.ATTRIBUTE9,
525 pdl.DISTRIBUTION_LINE_ID, -- storing in attribute10
526 pdl.CAP_EXCESS_DIST_LINE_ID,
527 pdl.CAP_EXCESS_GLCCID,
528 pdl.CAP_EXCESS_AWARD_ID,
529 pdl.CAP_EXCESS_PROJECT_ID,
530 pdl.CAP_EXCESS_TASK_ID,
531 pdl.CAP_EXCESS_EXP_ORG_ID,
532 pdl.CAP_EXCESS_EXP_TYPE,
533 pdl.FUNDING_SOURCE_CODE,
534 pdl.ANNUAL_SALARY_CAP,
535 pdl.SUSPENSE_AUTO_GLCCID,
536 pdl.SUSPENSE_AUTO_EXP_TYPE,
537 pdl.ADJ_ACCOUNT_FLAG
538 FROM psp_distribution_lines pdl,
539 psp_summary_lines psl
540 WHERE pdl.summary_line_id = psl.attribute29
541 AND psl.gms_batch_name = p_gms_batch_name
542 AND (source_type = 'O'
543 OR source_type = 'N');
544
545 -- adjust the rounding off amount on the last distribution line within a summary line
546 OPEN get_unmatched_summary_lines;
547 LOOP
548 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
549 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
550 IF get_unmatched_summary_lines%NOTFOUND THEN
551 CLOSE get_unmatched_summary_lines;
552 EXIT;
553 END IF;
554
555 hr_utility.trace('get_unmatched_summary_lines ='|| l_summary_line_id||','|| l_sum_summary_amt||','|| l_sum_dist_amt);
556
557 IF l_sum_summary_amt != l_sum_dist_amt THEN
558 UPDATE psp_distribution_lines pdl
559 SET distribution_amount = distribution_amount+l_sum_summary_amt-l_sum_dist_amt
560 WHERE distribution_line_id = (SELECT max(distribution_line_id)
561 FROM psp_distribution_lines pdl1
562 WHERE pdl.summary_line_id = l_summary_line_id);
563 END IF;
564 END LOOP;
565
566 -- create pre-gen lines for the new summary lines if source_type = 'P'
567 INSERT INTO psp_pre_gen_dist_lines(
568 PRE_GEN_DIST_LINE_ID,
569 DISTRIBUTION_INTERFACE_ID,
570 PERSON_ID,
571 ASSIGNMENT_ID,
572 ELEMENT_TYPE_ID,
573 DISTRIBUTION_DATE,
574 EFFECTIVE_DATE,
575 DISTRIBUTION_AMOUNT,
576 DR_CR_FLAG,
577 PAYROLL_CONTROL_ID,
578 SOURCE_TYPE,
579 SOURCE_CODE,
580 TIME_PERIOD_ID,
581 BATCH_NAME,
582 STATUS_CODE,
583 SET_OF_BOOKS_ID,
584 GL_CODE_COMBINATION_ID,
585 PROJECT_ID,
586 EXPENDITURE_ORGANIZATION_ID,
587 EXPENDITURE_TYPE,
588 TASK_ID,
589 AWARD_ID,
590 SUSPENSE_ORG_ACCOUNT_ID,
591 SUSPENSE_REASON_CODE,
592 EFFORT_REPORT_ID,
593 VERSION_NUM,
594 SUMMARY_LINE_ID,
595 REVERSAL_ENTRY_FLAG,
596 USER_DEFINED_FIELD,
597 BUSINESS_GROUP_ID,
598 ATTRIBUTE_CATEGORY,
599 ATTRIBUTE1,
600 ATTRIBUTE2,
601 ATTRIBUTE3,
602 ATTRIBUTE4,
603 ATTRIBUTE5,
604 ATTRIBUTE6,
605 ATTRIBUTE7,
606 ATTRIBUTE8,
607 ATTRIBUTE9,
608 ATTRIBUTE10,
609 SUSPENSE_AUTO_GLCCID,
610 SUSPENSE_AUTO_EXP_TYPE)
611 SELECT
612 PSP_DISTRIBUTION_LINES_S.NEXTVAL,
613 ppgl.DISTRIBUTION_INTERFACE_ID,
614 ppgl.PERSON_ID,
615 ppgl.ASSIGNMENT_ID,
616 ppgl.ELEMENT_TYPE_ID,
617 ppgl.DISTRIBUTION_DATE,
618 ppgl.EFFECTIVE_DATE,
619 --ppgl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30, --Bug 12761630
620 decode(psl.attribute30,0,0,((ppgl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT)/psl.ATTRIBUTE30)),
621 ppgl.DR_CR_FLAG,
622 ppgl.PAYROLL_CONTROL_ID,
623 ppgl.SOURCE_TYPE,
624 ppgl.SOURCE_CODE,
625 ppgl.TIME_PERIOD_ID,
626 ppgl.BATCH_NAME,
627 ppgl.STATUS_CODE,
628 ppgl.SET_OF_BOOKS_ID,
629 ppgl.GL_CODE_COMBINATION_ID,
630 ppgl.PROJECT_ID,
631 ppgl.EXPENDITURE_ORGANIZATION_ID,
632 ppgl.EXPENDITURE_TYPE,
633 ppgl.TASK_ID,
634 psl.AWARD_ID, -- update the new award_id
635 ppgl.SUSPENSE_ORG_ACCOUNT_ID,
636 ppgl.SUSPENSE_REASON_CODE,
637 ppgl.EFFORT_REPORT_ID,
638 ppgl.VERSION_NUM,
639 psl.SUMMARY_LINE_ID, -- update the new summary_line_id
640 ppgl.REVERSAL_ENTRY_FLAG,
641 ppgl.USER_DEFINED_FIELD,
642 ppgl.BUSINESS_GROUP_ID,
643 ppgl.ATTRIBUTE_CATEGORY,
644 ppgl.ATTRIBUTE1,
645 ppgl.ATTRIBUTE2,
646 ppgl.ATTRIBUTE3,
647 ppgl.ATTRIBUTE4,
648 ppgl.ATTRIBUTE5,
649 ppgl.ATTRIBUTE6,
650 ppgl.ATTRIBUTE7,
651 ppgl.ATTRIBUTE8,
652 ppgl.PRE_GEN_DIST_LINE_ID, -- attribute9
653 ppgl.DISTRIBUTION_AMOUNT, -- attribute10
654 ppgl.SUSPENSE_AUTO_GLCCID,
655 ppgl.SUSPENSE_AUTO_EXP_TYPE
656 FROM psp_pre_gen_dist_lines ppgl,
657 psp_summary_lines psl
658 WHERE ppgl.summary_line_id = psl.attribute29
659 AND psl.payroll_control_id = p_payroll_control_id
660 AND psl.source_type = 'P';
661
662 -- adjust the rounding off amount on the last pre gen distribution line within a summary line
663 OPEN get_unmatched_pg_summary_lines;
664 LOOP
665 FETCH get_unmatched_pg_summary_lines INTO l_pg_summary_line_id,
666 l_pg_sum_summary_amt, l_pg_sum_dist_amt;
667 IF get_unmatched_pg_summary_lines%NOTFOUND THEN
668 CLOSE get_unmatched_pg_summary_lines;
669 EXIT;
670 END IF;
671
672 IF l_pg_sum_summary_amt != l_pg_sum_dist_amt THEN
673 UPDATE psp_pre_gen_dist_lines
674 SET distribution_amount = distribution_amount+l_pg_sum_summary_amt-l_pg_sum_dist_amt
675 WHERE pre_gen_dist_line_id = (SELECT max(pre_gen_dist_line_id)
676 FROM psp_pre_gen_dist_lines
677 WHERE summary_line_id = l_pg_summary_line_id);
678 END IF;
679 END LOOP;
680
681 DELETE psp_distribution_lines
682 WHERE summary_line_id in
683 (select summary_line_id
684 from psp_summary_lines
685 where gms_batch_name = p_gms_batch_name
686 and award_id = l_default_dist_award_id
687 and status_code = 'S');
688
689 -- delete the original pre-gen distribution lines
690 DELETE psp_pre_gen_dist_lines
691 WHERE summary_line_id in
692 (select summary_line_id
693 from psp_summary_lines
694 where gms_batch_name = p_gms_batch_name
695 and award_id = l_default_dist_award_id
696 and status_code = 'S');
697
698 DELETE psp_summary_lines
699 WHERE gms_batch_name = p_gms_batch_name
700 and status_code = 'S';
701 else
702 hr_utility.trace('tieback_actual userhook..no default awards found to process');
703 end if; --- default awards involved in PRC import
704 exception
705 when others then
706 psp_sum_trans.g_error_api_path := 'TIEBACK_ACTUAL'||psp_sum_trans.g_error_api_path;
707 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TIEBACK_ACTUAL');
708 raise;
709
710 END;
711 */
712
713 ----- new procedure for 5463110
714 PROCEDURE tieback_adjustment(p_payroll_control_id IN NUMBER,
715 p_adjutment_batch_name in varchar2,
716 p_gms_batch_name IN VARCHAR2,
717 p_business_group_id IN NUMBER,
718 p_set_of_books_id IN NUMBER) is
719 begin
720 -- Write the user extension code here;
721 --
722 null;
723 exception
724 when others then
725 psp_sum_trans.g_error_api_path := 'TIEBACK_ACTUAL'||psp_sum_trans.g_error_api_path;
726 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TIEBACK_ADJUSTMENTL');
727 raise;
728 end;
729
730 /*
731 --- following code is for Award Funding pattern implementation in LD.
732 --- this code should be moved into tieback_adjustments.
733
734 l_default_dist_award_id NUMBER;
735 l_summary_line_id NUMBER;
736 l_sum_summary_amt NUMBER;
737 l_sum_dist_amt NUMBER;
738
739 -- get the summary_line_id's where adj line amount does not match with summary amount
740 CURSOR get_unmatched_summary_lines is
741 SELECT pal.summary_line_id, psl.summary_amount, sum(pal.distribution_amount)
742 FROM psp_adjustment_lines pal,
743 psp_summary_lines psl
744 WHERE pal.summary_line_id = psl.summary_line_id
745 AND psl.payroll_control_id = p_payroll_control_id
746 AND psl.status_code = 'N'
747 AND psl.attribute29 is not null
748 ANd psl.attribute30 is not null
749 GROUP BY pal.summary_line_id, psl.summary_amount
750 HAVING summary_amount - sum(distribution_amount) <> 0;
751
752 CURSOR get_unmatched_summary_splits is
753 SELECT to_number(attribute30) old_summary_amount,
754 attribute29 old_summary_line_id,
755 sum(summary_amount) new_summary_amount,
756 max(summary_line_id) new_summary_line_id
757 FROM psp_summary_lines
758 WHERE attribute29 is not null
759 AND attribute30 is not null
760 AND gms_batch_name = p_gms_batch_name
761 AND status_code <> 'S'
762 GROUP BY attribute29, attribute30
763 HAVING sum(summary_amount) <> to_number(attribute30);
764
765 summary_split_rec get_unmatched_summary_splits%rowtype;
766
767 BEGIN
768
769 hr_utility.trace( 'ENTERING TIEBACK_ADJUSTMENT GMS_BATCH_NAME = '||p_gms_batch_name);
770
771 SELECT default_dist_award_id
772 INTO l_default_dist_award_id
773 FROM gms_implementations;
774 hr_utility.trace( ' adjustment tieback userhook l_default_dist_award_id='|| l_default_dist_award_id);
775
776 -- create new summary lines from PA/GMS interface table for award distribution lines
777
778 INSERT INTO psp_summary_lines
779 (SUMMARY_LINE_ID,
780 SOURCE_TYPE,
781 SOURCE_CODE,
782 TIME_PERIOD_ID,
783 INTERFACE_BATCH_NAME,
784 PERSON_ID,
785 ASSIGNMENT_ID,
786 EFFECTIVE_DATE,
787 PAYROLL_CONTROL_ID,
788 GL_CODE_COMBINATION_ID,
789 PROJECT_ID,
790 EXPENDITURE_ORGANIZATION_ID,
791 EXPENDITURE_TYPE,
792 TASK_ID,
793 AWARD_ID,
794 SUMMARY_AMOUNT,
795 DR_CR_FLAG,
796 GROUP_ID,
797 INTERFACE_STATUS,
798 ATTRIBUTE_CATEGORY,
799 ATTRIBUTE1,
800 ATTRIBUTE2,
801 ATTRIBUTE3,
802 ATTRIBUTE4,
803 ATTRIBUTE5,
804 ATTRIBUTE6,
805 ATTRIBUTE7,
806 ATTRIBUTE8,
807 ATTRIBUTE9,
808 ATTRIBUTE10,
809 ATTRIBUTE11,
810 ATTRIBUTE12,
811 ATTRIBUTE13,
812 ATTRIBUTE14,
813 ATTRIBUTE15,
814 ATTRIBUTE16,
815 ATTRIBUTE17,
816 ATTRIBUTE18,
817 ATTRIBUTE19,
818 ATTRIBUTE20,
819 ATTRIBUTE21,
820 ATTRIBUTE22,
821 ATTRIBUTE23,
822 ATTRIBUTE24,
823 ATTRIBUTE25,
824 ATTRIBUTE26,
825 ATTRIBUTE27,
826 ATTRIBUTE28,
827 ATTRIBUTE29,
828 ATTRIBUTE30,
829 LAST_UPDATE_DATE,
830 LAST_UPDATED_BY,
831 LAST_UPDATE_LOGIN,
832 CREATED_BY,
833 CREATION_DATE,
834 SET_OF_BOOKS_ID,
835 BUSINESS_GROUP_ID,
836 STATUS_CODE,
837 GMS_BATCH_NAME,
838 GMS_POSTING_EFFECTIVE_DATE,
839 EXPENDITURE_ENDING_DATE ,
840 EXPENDITURE_ID,
841 INTERFACE_ID,
842 EXPENDITURE_ITEM_ID,
843 TXN_INTERFACE_ID,
844 ACTUAL_SUMMARY_AMOUNT,
845 ACCOUNTING_DATE,
846 EXCHANGE_RATE_TYPE)
847 SELECT
848 PSP_SUMMARY_LINES_S.NEXTVAL,
849 psl.SOURCE_TYPE,
850 psl.SOURCE_CODE,
851 psl.TIME_PERIOD_ID,
852 psl.INTERFACE_BATCH_NAME,
853 psl.PERSON_ID,
854 psl.ASSIGNMENT_ID,
855 psl.EFFECTIVE_DATE,
856 psl.PAYROLL_CONTROL_ID,
857 psl.GL_CODE_COMBINATION_ID,
858 psl.PROJECT_ID,
859 psl.EXPENDITURE_ORGANIZATION_ID,
860 psl.EXPENDITURE_TYPE,
861 psl.TASK_ID,
862 gti.AWARD_ID,
863 pti.denom_raw_cost, -- putting new value in the summary_amount column
864 psl.DR_CR_FLAG,
865 psl.GROUP_ID,
866 psl.INTERFACE_STATUS,
867 psl.ATTRIBUTE_CATEGORY,
868 psl.ATTRIBUTE1,
869 psl.ATTRIBUTE2,
870 psl.ATTRIBUTE3,
871 psl.ATTRIBUTE4,
872 psl.ATTRIBUTE5,
873 psl.ATTRIBUTE6,
874 psl.ATTRIBUTE7,
875 psl.ATTRIBUTE8,
876 psl.ATTRIBUTE9,
877 psl.ATTRIBUTE10,
878 psl.ATTRIBUTE11,
879 psl.ATTRIBUTE12,
880 psl.ATTRIBUTE13,
881 psl.ATTRIBUTE14,
882 psl.ATTRIBUTE15,
883 psl.ATTRIBUTE16,
884 psl.ATTRIBUTE17,
885 psl.ATTRIBUTE18,
886 psl.ATTRIBUTE19,
887 psl.ATTRIBUTE20,
888 psl.ATTRIBUTE21,
889 psl.ATTRIBUTE22,
890 psl.ATTRIBUTE23,
891 psl.ATTRIBUTE24,
892 psl.ATTRIBUTE25,
893 psl.ATTRIBUTE26,
894 psl.ATTRIBUTE27,
895 psl.ATTRIBUTE28,
896 psl.SUMMARY_LINE_ID, -- storing in attribute29
897 psl.SUMMARY_AMOUNT, -- storing in attribute30
898 psl.LAST_UPDATE_DATE,
899 psl.LAST_UPDATED_BY,
900 psl.LAST_UPDATE_LOGIN,
901 psl.CREATED_BY,
902 psl.CREATION_DATE,
903 psl.SET_OF_BOOKS_ID,
904 psl.BUSINESS_GROUP_ID,
905 psl.STATUS_CODE,
906 psl.GMS_BATCH_NAME,
907 psl.GMS_POSTING_EFFECTIVE_DATE,
908 psl.EXPENDITURE_ENDING_DATE ,
909 psl.EXPENDITURE_ID,
910 psl.INTERFACE_ID,
911 psl.EXPENDITURE_ITEM_ID,
912 pti.TXN_INTERFACE_ID, -- insert the new txn_reference_id from pa_transaction_interface_all
913 psl.ACTUAL_SUMMARY_AMOUNT,
914 psl.ACCOUNTING_DATE,
915 psl.EXCHANGE_RATE_TYPE
916 FROM pa_transaction_interface_all pti,
917 gms_transaction_interface_all gti,
918 psp_summary_lines psl
919 WHERE pti.txn_interface_id = gti.txn_interface_id
920 AND pti.orig_transaction_reference = psl.summary_line_id
921 AND psl.award_id = l_default_dist_award_id
922 AND pti.batch_name = p_gms_batch_name
923 AND pti.transaction_source = 'GOLD';
924
925 if sql%rowcount > 0 then
926
927 UPDATE psp_summary_lines
928 SET status_code = 'S'
929 WHERE award_id = l_default_dist_award_id
930 AND summary_line_id IN (SELECT orig_transaction_reference
931 FROM pa_transaction_interface_all pti
932 WHERE pti.batch_name = p_gms_batch_name
933 AND pti.transaction_source = 'GOLD');
934
935 hr_utility.trace('summary line split rounding fixes ADJ');
936 open get_unmatched_summary_splits;
937 hr_utility.trace('summary line split rounding fixes ADJ2');
938 loop
939 hr_utility.trace('summary line split rounding fixes ADJ2.2');
940 fetch get_unmatched_summary_splits into summary_split_rec;
941 hr_utility.trace('summary line split rounding fixes ADJ3');
942 if get_unmatched_summary_splits%notfound then
943 close get_unmatched_summary_splits;
944 exit;
945 end if;
946 hr_utility.trace('summary line split rounding fixes ADJ4');
947 update psp_summary_lines
948 set summary_amount = summary_split_rec.old_summary_amount - summary_split_rec.new_summary_amount + summary_amount
949 where summary_line_id = summary_split_rec.new_summary_line_id;
950 null;
951 end loop;
952
953 hr_utility.trace('Update of pa xface table');
954 -- update the new orig_transaction_reference in pa_transaction_interface_all table
955 -- gms_tie_back procedure in Summarize and Transfer process expects one record in pa/gms interface table for one summary line
956 UPDATE pa_transaction_interface_all pti
957 SET pti.orig_transaction_reference = (SELECT summary_line_id FROM psp_summary_lines psl
958 WHERE psl.txn_interface_id = pti.txn_interface_id
959 AND psl.status_code = 'N'
960 AND psl.gms_batch_name = p_gms_batch_name)
961 WHERE pti.batch_name = p_gms_batch_name
962 AND pti.transaction_source = 'GOLD'
963 AND pti.orig_transaction_reference in (SELECT to_char(summary_line_id)
964 FROM psp_summary_lines
965 WHERE status_code = 'S'
966 AND gms_batch_name = p_gms_batch_name) ;
967
968 INSERT INTO PSP_ADJUSTMENT_LINES(
969 ADJUSTMENT_LINE_ID ,
970 PERSON_ID ,
971 ASSIGNMENT_ID ,
972 ELEMENT_TYPE_ID ,
973 DISTRIBUTION_DATE ,
974 EFFECTIVE_DATE ,
975 DISTRIBUTION_AMOUNT ,
976 DR_CR_FLAG ,
977 SOURCE_CODE ,
978 SOURCE_TYPE ,
979 TIME_PERIOD_ID ,
980 BATCH_NAME ,
981 STATUS_CODE ,
982 SET_OF_BOOKS_ID ,
983 GL_CODE_COMBINATION_ID ,
984 PROJECT_ID ,
985 EXPENDITURE_ORGANIZATION_ID,
986 EXPENDITURE_TYPE ,
987 TASK_ID ,
988 AWARD_ID ,
989 SUSPENSE_ORG_ACCOUNT_ID,
990 SUSPENSE_REASON_CODE ,
991 EFFORT_REPORT_ID ,
992 VERSION_NUM ,
993 SUMMARY_LINE_ID ,
994 REVERSAL_ENTRY_FLAG ,
995 ORIGINAL_LINE_FLAG ,
996 USER_DEFINED_FIELD ,
997 PERCENT ,
998 ORIG_SOURCE_TYPE ,
999 ORIG_LINE_ID ,
1000 ATTRIBUTE_CATEGORY ,
1001 ATTRIBUTE1 ,
1002 ATTRIBUTE2 ,
1003 ATTRIBUTE3 ,
1004 ATTRIBUTE4 ,
1005 ATTRIBUTE5 ,
1006 ATTRIBUTE6 ,
1007 ATTRIBUTE7 ,
1008 ATTRIBUTE8 ,
1009 ATTRIBUTE9 ,
1010 ATTRIBUTE10 ,
1011 ATTRIBUTE11 ,
1012 ATTRIBUTE12 ,
1013 ATTRIBUTE13 ,
1014 ATTRIBUTE14 ,
1015 ATTRIBUTE15 ,
1016 LAST_UPDATE_DATE ,
1017 LAST_UPDATED_BY ,
1018 LAST_UPDATE_LOGIN ,
1019 CREATED_BY ,
1020 CREATION_DATE ,
1021 PAYROLL_CONTROL_ID ,
1022 BUSINESS_GROUP_ID)
1023 SELECT
1024 PSP_ADJUSTMENT_LINES_S.NEXTVAL,
1025 pal.PERSON_ID ,
1026 pal.ASSIGNMENT_ID ,
1027 pal.ELEMENT_TYPE_ID ,
1028 pal.DISTRIBUTION_DATE ,
1029 pal.EFFECTIVE_DATE ,
1030 --pal.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30, --Bug 12761630
1031 decode(psl.attribute30,0,0,((pal.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT)/psl.ATTRIBUTE30)),
1032 pal.DR_CR_FLAG ,
1033 pal.SOURCE_CODE ,
1034 pal.SOURCE_TYPE ,
1035 pal.TIME_PERIOD_ID ,
1036 pal.BATCH_NAME ,
1037 pal.STATUS_CODE ,
1038 pal.SET_OF_BOOKS_ID ,
1039 pal.GL_CODE_COMBINATION_ID ,
1040 pal.PROJECT_ID ,
1041 pal.EXPENDITURE_ORGANIZATION_ID,
1042 pal.EXPENDITURE_TYPE ,
1043 pal.TASK_ID ,
1044 psl.AWARD_ID , --- new award_id
1045 pal.SUSPENSE_ORG_ACCOUNT_ID,
1046 pal.SUSPENSE_REASON_CODE ,
1047 pal.EFFORT_REPORT_ID ,
1048 pal.VERSION_NUM ,
1049 psl.SUMMARY_LINE_ID , -- new sum line_id
1050 pal.REVERSAL_ENTRY_FLAG ,
1051 pal.ORIGINAL_LINE_FLAG ,
1052 pal.USER_DEFINED_FIELD ,
1053 pal.PERCENT ,
1054 pal.ORIG_SOURCE_TYPE ,
1055 pal.ORIG_LINE_ID ,
1056 pal.ATTRIBUTE_CATEGORY ,
1057 pal.ATTRIBUTE1 ,
1058 pal.ATTRIBUTE2 ,
1059 pal.ATTRIBUTE3 ,
1060 pal.ATTRIBUTE4 ,
1061 pal.ATTRIBUTE5 ,
1062 pal.ATTRIBUTE6 ,
1063 pal.ATTRIBUTE7 ,
1064 pal.ATTRIBUTE8 ,
1065 pal.ATTRIBUTE9 ,
1066 pal.ATTRIBUTE10 ,
1067 pal.ATTRIBUTE11 ,
1068 pal.ATTRIBUTE12,
1069 pal.ATTRIBUTE13,
1070 pal.adjustment_LINE_ID, --- line_id
1071 pal.distribution_amount, -- dist_amount
1072 pal.LAST_UPDATE_DATE ,
1073 pal.LAST_UPDATED_BY ,
1074 pal.LAST_UPDATE_LOGIN ,
1075 pal.CREATED_BY ,
1076 pal.CREATION_DATE ,
1077 pal.PAYROLL_CONTROL_ID ,
1078 pal.BUSINESS_GROUP_ID
1079 FROM psp_adjustment_lines pal,
1080 psp_summary_lines psl
1081 WHERE pal.summary_line_id = psl.attribute29
1082 AND psl.gms_batch_name = p_gms_batch_name;
1083
1084 -- adjust the rounding off amount on the last distribution line within a summary line
1085 hr_utility.trace('GEt unmatched summary lines csr');
1086 OPEN get_unmatched_summary_lines;
1087 hr_utility.trace('GEt unmatched summary lines csr2');
1088 LOOP
1089 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
1090 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
1091 IF get_unmatched_summary_lines%NOTFOUND THEN
1092 CLOSE get_unmatched_summary_lines;
1093 EXIT;
1094 END IF;
1095
1096 IF l_sum_summary_amt != l_sum_dist_amt THEN
1097 UPDATE psp_adjustment_lines pal
1098 SET distribution_amount = distribution_amount+l_sum_summary_amt-l_sum_dist_amt
1099 WHERE adjustment_line_id = (SELECT max(adjustment_line_id)
1100 FROM psp_adjustment_lines pal1
1101 WHERE pal1.summary_line_id = l_summary_line_id);
1102 END IF;
1103 END LOOP;
1104 hr_utility.trace('GEt unmatched summary lines csr4');
1105
1106 --- delete the original adjustment lines
1107 DELETE psp_adjustment_lines
1108 where summary_line_id IN (SELECT summary_line_id FROM psp_summary_lines
1109 WHERE payroll_control_id = p_payroll_control_id
1110 AND status_code = 'S');
1111
1112 DELETE psp_summary_lines
1113 WHERE status_code = 'S'
1114 and payroll_control_id = p_payroll_control_id;
1115 else
1116 hr_utility.trace('tieback_actual userhook..no default awards found to process');
1117 end if;
1118 exception
1119 when others then
1120 psp_sum_trans.g_error_api_path := 'TIEBACK_ADJUSTMENT'||psp_sum_trans.g_error_api_path;
1121 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TIEBACK_ADJUSTMENT');
1122 raise;
1123 END tieback_adjustment;
1124 */
1125 END PSP_ST_EXT;