[Home] [Help]
PACKAGE BODY: APPS.PSP_ST_EXT
Source
1 PACKAGE BODY PSP_ST_EXT as
2 /* $Header: PSPTREXB.pls 120.4 2006/12/22 19:35:44 vdharmap noship $ */
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,
296 ATTRIBUTE28,
293 ATTRIBUTE25,
294 ATTRIBUTE26,
295 ATTRIBUTE27,
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,
446 STATUS_CODE,
443 DISTRIBUTION_DATE,
444 EFFECTIVE_DATE,
445 DISTRIBUTION_AMOUNT,
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 pdl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30,
494 pdl.STATUS_CODE,
495 pdl.DEFAULT_REASON_CODE,
496 pdl.SUSPENSE_REASON_CODE,
497 pdl.EFFORT_REPORT_ID,
498 pdl.PAYROLL_SUB_LINE_ID,
499 pdl.SCHEDULE_LINE_ID,
500 pdl.DEFAULT_ORG_ACCOUNT_ID,
501 pdl.SUSPENSE_ORG_ACCOUNT_ID,
502 pdl.ELEMENT_ACCOUNT_ID,
503 pdl.ORG_SCHEDULE_ID,
504 pdl.GL_PROJECT_FLAG,
505 pdl.REVERSAL_ENTRY_FLAG,
506 pdl.PRE_DISTRIBUTION_RUN_FLAG,
507 psl.SUMMARY_LINE_ID,
508 pdl.SET_OF_BOOKS_ID,
509 pdl.VERSION_NUM,
510 pdl.USER_DEFINED_FIELD,
511 pdl.AUTO_EXPENDITURE_TYPE,
512 pdl.AUTO_GL_CODE_COMBINATION_ID,
513 pdl.BUSINESS_GROUP_ID,
514 pdl.ATTRIBUTE_CATEGORY,
515 pdl.ATTRIBUTE1,
516 pdl.ATTRIBUTE2,
517 pdl.ATTRIBUTE3,
518 pdl.ATTRIBUTE4,
519 pdl.ATTRIBUTE5,
520 pdl.ATTRIBUTE6,
521 pdl.ATTRIBUTE7,
522 pdl.ATTRIBUTE8,
523 pdl.ATTRIBUTE9,
524 pdl.DISTRIBUTION_LINE_ID, -- storing in attribute10
525 pdl.CAP_EXCESS_DIST_LINE_ID,
526 pdl.CAP_EXCESS_GLCCID,
527 pdl.CAP_EXCESS_AWARD_ID,
528 pdl.CAP_EXCESS_PROJECT_ID,
529 pdl.CAP_EXCESS_TASK_ID,
530 pdl.CAP_EXCESS_EXP_ORG_ID,
531 pdl.CAP_EXCESS_EXP_TYPE,
532 pdl.FUNDING_SOURCE_CODE,
533 pdl.ANNUAL_SALARY_CAP,
534 pdl.SUSPENSE_AUTO_GLCCID,
535 pdl.SUSPENSE_AUTO_EXP_TYPE,
536 pdl.ADJ_ACCOUNT_FLAG
537 FROM psp_distribution_lines pdl,
538 psp_summary_lines psl
539 WHERE pdl.summary_line_id = psl.attribute29
540 AND psl.gms_batch_name = p_gms_batch_name
541 AND (source_type = 'O'
542 OR source_type = 'N');
543
544 -- adjust the rounding off amount on the last distribution line within a summary line
545 OPEN get_unmatched_summary_lines;
546 LOOP
547 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
548 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
549 IF get_unmatched_summary_lines%NOTFOUND THEN
550 CLOSE get_unmatched_summary_lines;
551 EXIT;
552 END IF;
553
554 hr_utility.trace('get_unmatched_summary_lines ='|| l_summary_line_id||','|| l_sum_summary_amt||','|| l_sum_dist_amt);
555
556 IF l_sum_summary_amt != l_sum_dist_amt THEN
557 UPDATE psp_distribution_lines pdl
558 SET distribution_amount = distribution_amount+l_sum_summary_amt-l_sum_dist_amt
559 WHERE distribution_line_id = (SELECT max(distribution_line_id)
560 FROM psp_distribution_lines pdl1
561 WHERE pdl.summary_line_id = l_summary_line_id);
562 END IF;
563 END LOOP;
564
565 -- create pre-gen lines for the new summary lines if source_type = 'P'
566 INSERT INTO psp_pre_gen_dist_lines(
567 PRE_GEN_DIST_LINE_ID,
568 DISTRIBUTION_INTERFACE_ID,
569 PERSON_ID,
570 ASSIGNMENT_ID,
571 ELEMENT_TYPE_ID,
572 DISTRIBUTION_DATE,
573 EFFECTIVE_DATE,
574 DISTRIBUTION_AMOUNT,
575 DR_CR_FLAG,
576 PAYROLL_CONTROL_ID,
577 SOURCE_TYPE,
578 SOURCE_CODE,
579 TIME_PERIOD_ID,
580 BATCH_NAME,
581 STATUS_CODE,
582 SET_OF_BOOKS_ID,
583 GL_CODE_COMBINATION_ID,
584 PROJECT_ID,
585 EXPENDITURE_ORGANIZATION_ID,
586 EXPENDITURE_TYPE,
587 TASK_ID,
588 AWARD_ID,
589 SUSPENSE_ORG_ACCOUNT_ID,
590 SUSPENSE_REASON_CODE,
591 EFFORT_REPORT_ID,
592 VERSION_NUM,
593 SUMMARY_LINE_ID,
594 REVERSAL_ENTRY_FLAG,
595 USER_DEFINED_FIELD,
596 BUSINESS_GROUP_ID,
597 ATTRIBUTE_CATEGORY,
598 ATTRIBUTE1,
599 ATTRIBUTE2,
600 ATTRIBUTE3,
601 ATTRIBUTE4,
602 ATTRIBUTE5,
603 ATTRIBUTE6,
604 ATTRIBUTE7,
605 ATTRIBUTE8,
606 ATTRIBUTE9,
607 ATTRIBUTE10,
608 SUSPENSE_AUTO_GLCCID,
609 SUSPENSE_AUTO_EXP_TYPE)
610 SELECT
611 PSP_DISTRIBUTION_LINES_S.NEXTVAL,
615 ppgl.ELEMENT_TYPE_ID,
612 ppgl.DISTRIBUTION_INTERFACE_ID,
613 ppgl.PERSON_ID,
614 ppgl.ASSIGNMENT_ID,
616 ppgl.DISTRIBUTION_DATE,
617 ppgl.EFFECTIVE_DATE,
618 ppgl.DISTRIBUTION_AMOUNT*psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30,
619 ppgl.DR_CR_FLAG,
620 ppgl.PAYROLL_CONTROL_ID,
621 ppgl.SOURCE_TYPE,
622 ppgl.SOURCE_CODE,
623 ppgl.TIME_PERIOD_ID,
624 ppgl.BATCH_NAME,
625 ppgl.STATUS_CODE,
626 ppgl.SET_OF_BOOKS_ID,
627 ppgl.GL_CODE_COMBINATION_ID,
628 ppgl.PROJECT_ID,
629 ppgl.EXPENDITURE_ORGANIZATION_ID,
630 ppgl.EXPENDITURE_TYPE,
631 ppgl.TASK_ID,
632 psl.AWARD_ID, -- update the new award_id
633 ppgl.SUSPENSE_ORG_ACCOUNT_ID,
634 ppgl.SUSPENSE_REASON_CODE,
635 ppgl.EFFORT_REPORT_ID,
636 ppgl.VERSION_NUM,
637 psl.SUMMARY_LINE_ID, -- update the new summary_line_id
638 ppgl.REVERSAL_ENTRY_FLAG,
639 ppgl.USER_DEFINED_FIELD,
640 ppgl.BUSINESS_GROUP_ID,
641 ppgl.ATTRIBUTE_CATEGORY,
642 ppgl.ATTRIBUTE1,
643 ppgl.ATTRIBUTE2,
644 ppgl.ATTRIBUTE3,
645 ppgl.ATTRIBUTE4,
646 ppgl.ATTRIBUTE5,
647 ppgl.ATTRIBUTE6,
648 ppgl.ATTRIBUTE7,
649 ppgl.ATTRIBUTE8,
650 ppgl.PRE_GEN_DIST_LINE_ID, -- attribute9
651 ppgl.DISTRIBUTION_AMOUNT, -- attribute10
652 ppgl.SUSPENSE_AUTO_GLCCID,
653 ppgl.SUSPENSE_AUTO_EXP_TYPE
654 FROM psp_pre_gen_dist_lines ppgl,
655 psp_summary_lines psl
656 WHERE ppgl.summary_line_id = psl.attribute29
657 AND psl.payroll_control_id = p_payroll_control_id
658 AND psl.source_type = 'P';
659
660 -- adjust the rounding off amount on the last pre gen distribution line within a summary line
661 OPEN get_unmatched_pg_summary_lines;
662 LOOP
663 FETCH get_unmatched_pg_summary_lines INTO l_pg_summary_line_id,
664 l_pg_sum_summary_amt, l_pg_sum_dist_amt;
665 IF get_unmatched_pg_summary_lines%NOTFOUND THEN
666 CLOSE get_unmatched_pg_summary_lines;
667 EXIT;
668 END IF;
669
670 IF l_pg_sum_summary_amt != l_pg_sum_dist_amt THEN
671 UPDATE psp_pre_gen_dist_lines
672 SET distribution_amount = distribution_amount+l_pg_sum_summary_amt-l_pg_sum_dist_amt
673 WHERE pre_gen_dist_line_id = (SELECT max(pre_gen_dist_line_id)
674 FROM psp_pre_gen_dist_lines
675 WHERE summary_line_id = l_pg_summary_line_id);
676 END IF;
677 END LOOP;
678
679 DELETE psp_distribution_lines
680 WHERE summary_line_id in
681 (select summary_line_id
682 from psp_summary_lines
683 where gms_batch_name = p_gms_batch_name
684 and award_id = l_default_dist_award_id
685 and status_code = 'S');
686
687 -- delete the original pre-gen distribution lines
688 DELETE psp_pre_gen_dist_lines
689 WHERE summary_line_id in
690 (select summary_line_id
691 from psp_summary_lines
692 where gms_batch_name = p_gms_batch_name
693 and award_id = l_default_dist_award_id
694 and status_code = 'S');
695
696 DELETE psp_summary_lines
697 WHERE gms_batch_name = p_gms_batch_name
698 and status_code = 'S';
699 else
700 hr_utility.trace('tieback_actual userhook..no default awards found to process');
701 end if; --- default awards involved in PRC import
702 exception
703 when others then
704 psp_sum_trans.g_error_api_path := 'TIEBACK_ACTUAL'||psp_sum_trans.g_error_api_path;
705 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TIEBACK_ACTUAL');
706 raise;
707
708 END;
709 */
710
711 ----- new procedure for 5463110
712 PROCEDURE tieback_adjustment(p_payroll_control_id IN NUMBER,
713 p_adjutment_batch_name in varchar2,
714 p_gms_batch_name IN VARCHAR2,
715 p_business_group_id IN NUMBER,
716 p_set_of_books_id IN NUMBER) is
717 begin
718 -- Write the user extension code here;
719 --
720 null;
721 exception
722 when others then
723 psp_sum_trans.g_error_api_path := 'TIEBACK_ACTUAL'||psp_sum_trans.g_error_api_path;
724 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TIEBACK_ADJUSTMENTL');
725 raise;
726 end;
727
728 /*
729 --- following code is for Award Funding pattern implementation in LD.
730 --- this code should be moved into tieback_adjustments.
731
732 l_default_dist_award_id NUMBER;
733 l_summary_line_id NUMBER;
734 l_sum_summary_amt NUMBER;
735 l_sum_dist_amt NUMBER;
736
737 -- get the summary_line_id's where adj line amount does not match with summary amount
738 CURSOR get_unmatched_summary_lines is
739 SELECT pal.summary_line_id, psl.summary_amount, sum(pal.distribution_amount)
740 FROM psp_adjustment_lines pal,
741 psp_summary_lines psl
742 WHERE pal.summary_line_id = psl.summary_line_id
743 AND psl.payroll_control_id = p_payroll_control_id
744 AND psl.status_code = 'N'
745 AND psl.attribute29 is not null
746 ANd psl.attribute30 is not null
747 GROUP BY pal.summary_line_id, psl.summary_amount
748 HAVING summary_amount - sum(distribution_amount) <> 0;
749
750 CURSOR get_unmatched_summary_splits is
751 SELECT to_number(attribute30) old_summary_amount,
752 attribute29 old_summary_line_id,
753 sum(summary_amount) new_summary_amount,
754 max(summary_line_id) new_summary_line_id
755 FROM psp_summary_lines
756 WHERE attribute29 is not null
760 GROUP BY attribute29, attribute30
757 AND attribute30 is not null
758 AND gms_batch_name = p_gms_batch_name
759 AND status_code <> 'S'
761 HAVING sum(summary_amount) <> to_number(attribute30);
762
763 summary_split_rec get_unmatched_summary_splits%rowtype;
764
765 BEGIN
766
767 hr_utility.trace( 'ENTERING TIEBACK_ADJUSTMENT GMS_BATCH_NAME = '||p_gms_batch_name);
768
769 SELECT default_dist_award_id
770 INTO l_default_dist_award_id
771 FROM gms_implementations;
772 hr_utility.trace( ' adjustment tieback userhook l_default_dist_award_id='|| l_default_dist_award_id);
773
774 -- create new summary lines from PA/GMS interface table for award distribution lines
775
776 INSERT INTO psp_summary_lines
777 (SUMMARY_LINE_ID,
778 SOURCE_TYPE,
779 SOURCE_CODE,
780 TIME_PERIOD_ID,
781 INTERFACE_BATCH_NAME,
782 PERSON_ID,
783 ASSIGNMENT_ID,
784 EFFECTIVE_DATE,
785 PAYROLL_CONTROL_ID,
786 GL_CODE_COMBINATION_ID,
787 PROJECT_ID,
788 EXPENDITURE_ORGANIZATION_ID,
789 EXPENDITURE_TYPE,
790 TASK_ID,
791 AWARD_ID,
792 SUMMARY_AMOUNT,
793 DR_CR_FLAG,
794 GROUP_ID,
795 INTERFACE_STATUS,
796 ATTRIBUTE_CATEGORY,
797 ATTRIBUTE1,
798 ATTRIBUTE2,
799 ATTRIBUTE3,
800 ATTRIBUTE4,
801 ATTRIBUTE5,
802 ATTRIBUTE6,
803 ATTRIBUTE7,
804 ATTRIBUTE8,
805 ATTRIBUTE9,
806 ATTRIBUTE10,
807 ATTRIBUTE11,
808 ATTRIBUTE12,
809 ATTRIBUTE13,
810 ATTRIBUTE14,
811 ATTRIBUTE15,
812 ATTRIBUTE16,
813 ATTRIBUTE17,
814 ATTRIBUTE18,
815 ATTRIBUTE19,
816 ATTRIBUTE20,
817 ATTRIBUTE21,
818 ATTRIBUTE22,
819 ATTRIBUTE23,
820 ATTRIBUTE24,
821 ATTRIBUTE25,
822 ATTRIBUTE26,
823 ATTRIBUTE27,
824 ATTRIBUTE28,
825 ATTRIBUTE29,
826 ATTRIBUTE30,
827 LAST_UPDATE_DATE,
828 LAST_UPDATED_BY,
829 LAST_UPDATE_LOGIN,
830 CREATED_BY,
831 CREATION_DATE,
832 SET_OF_BOOKS_ID,
833 BUSINESS_GROUP_ID,
834 STATUS_CODE,
835 GMS_BATCH_NAME,
836 GMS_POSTING_EFFECTIVE_DATE,
837 EXPENDITURE_ENDING_DATE ,
838 EXPENDITURE_ID,
839 INTERFACE_ID,
840 EXPENDITURE_ITEM_ID,
841 TXN_INTERFACE_ID,
842 ACTUAL_SUMMARY_AMOUNT,
843 ACCOUNTING_DATE,
844 EXCHANGE_RATE_TYPE)
845 SELECT
846 PSP_SUMMARY_LINES_S.NEXTVAL,
847 psl.SOURCE_TYPE,
848 psl.SOURCE_CODE,
849 psl.TIME_PERIOD_ID,
850 psl.INTERFACE_BATCH_NAME,
851 psl.PERSON_ID,
852 psl.ASSIGNMENT_ID,
853 psl.EFFECTIVE_DATE,
854 psl.PAYROLL_CONTROL_ID,
855 psl.GL_CODE_COMBINATION_ID,
856 psl.PROJECT_ID,
857 psl.EXPENDITURE_ORGANIZATION_ID,
858 psl.EXPENDITURE_TYPE,
859 psl.TASK_ID,
860 gti.AWARD_ID,
861 pti.denom_raw_cost, -- putting new value in the summary_amount column
862 psl.DR_CR_FLAG,
863 psl.GROUP_ID,
864 psl.INTERFACE_STATUS,
865 psl.ATTRIBUTE_CATEGORY,
866 psl.ATTRIBUTE1,
867 psl.ATTRIBUTE2,
868 psl.ATTRIBUTE3,
869 psl.ATTRIBUTE4,
870 psl.ATTRIBUTE5,
871 psl.ATTRIBUTE6,
872 psl.ATTRIBUTE7,
873 psl.ATTRIBUTE8,
874 psl.ATTRIBUTE9,
875 psl.ATTRIBUTE10,
876 psl.ATTRIBUTE11,
877 psl.ATTRIBUTE12,
878 psl.ATTRIBUTE13,
879 psl.ATTRIBUTE14,
880 psl.ATTRIBUTE15,
881 psl.ATTRIBUTE16,
882 psl.ATTRIBUTE17,
883 psl.ATTRIBUTE18,
884 psl.ATTRIBUTE19,
885 psl.ATTRIBUTE20,
886 psl.ATTRIBUTE21,
887 psl.ATTRIBUTE22,
888 psl.ATTRIBUTE23,
889 psl.ATTRIBUTE24,
890 psl.ATTRIBUTE25,
891 psl.ATTRIBUTE26,
892 psl.ATTRIBUTE27,
893 psl.ATTRIBUTE28,
894 psl.SUMMARY_LINE_ID, -- storing in attribute29
895 psl.SUMMARY_AMOUNT, -- storing in attribute30
896 psl.LAST_UPDATE_DATE,
897 psl.LAST_UPDATED_BY,
898 psl.LAST_UPDATE_LOGIN,
899 psl.CREATED_BY,
900 psl.CREATION_DATE,
901 psl.SET_OF_BOOKS_ID,
902 psl.BUSINESS_GROUP_ID,
903 psl.STATUS_CODE,
904 psl.GMS_BATCH_NAME,
905 psl.GMS_POSTING_EFFECTIVE_DATE,
906 psl.EXPENDITURE_ENDING_DATE ,
907 psl.EXPENDITURE_ID,
908 psl.INTERFACE_ID,
909 psl.EXPENDITURE_ITEM_ID,
910 pti.TXN_INTERFACE_ID, -- insert the new txn_reference_id from pa_transaction_interface_all
911 psl.ACTUAL_SUMMARY_AMOUNT,
912 psl.ACCOUNTING_DATE,
913 psl.EXCHANGE_RATE_TYPE
914 FROM pa_transaction_interface_all pti,
915 gms_transaction_interface_all gti,
916 psp_summary_lines psl
917 WHERE pti.txn_interface_id = gti.txn_interface_id
918 AND pti.orig_transaction_reference = psl.summary_line_id
919 AND psl.award_id = l_default_dist_award_id
920 AND pti.batch_name = p_gms_batch_name
921 AND pti.transaction_source = 'GOLD';
922
923 if sql%rowcount > 0 then
924
925 UPDATE psp_summary_lines
926 SET status_code = 'S'
927 WHERE award_id = l_default_dist_award_id
928 AND summary_line_id IN (SELECT orig_transaction_reference
929 FROM pa_transaction_interface_all pti
930 WHERE pti.batch_name = p_gms_batch_name
931 AND pti.transaction_source = 'GOLD');
932
933 hr_utility.trace('summary line split rounding fixes ADJ');
934 open get_unmatched_summary_splits;
935 hr_utility.trace('summary line split rounding fixes ADJ2');
936 loop
937 hr_utility.trace('summary line split rounding fixes ADJ2.2');
938 fetch get_unmatched_summary_splits into summary_split_rec;
939 hr_utility.trace('summary line split rounding fixes ADJ3');
940 if get_unmatched_summary_splits%notfound then
941 close get_unmatched_summary_splits;
942 exit;
943 end if;
944 hr_utility.trace('summary line split rounding fixes ADJ4');
945 update psp_summary_lines
946 set summary_amount = summary_split_rec.old_summary_amount - summary_split_rec.new_summary_amount + summary_amount
947 where summary_line_id = summary_split_rec.new_summary_line_id;
948 null;
949 end loop;
950
951 hr_utility.trace('Update of pa xface table');
952 -- update the new orig_transaction_reference in pa_transaction_interface_all table
953 -- gms_tie_back procedure in Summarize and Transfer process expects one record in pa/gms interface table for one summary line
954 UPDATE pa_transaction_interface_all pti
955 SET pti.orig_transaction_reference = (SELECT summary_line_id FROM psp_summary_lines psl
956 WHERE psl.txn_interface_id = pti.txn_interface_id
957 AND psl.status_code = 'N'
958 AND psl.gms_batch_name = p_gms_batch_name)
959 WHERE pti.batch_name = p_gms_batch_name
960 AND pti.transaction_source = 'GOLD'
961 AND pti.orig_transaction_reference in (SELECT to_char(summary_line_id)
962 FROM psp_summary_lines
963 WHERE status_code = 'S'
964 AND gms_batch_name = p_gms_batch_name) ;
965
966 INSERT INTO PSP_ADJUSTMENT_LINES(
967 ADJUSTMENT_LINE_ID ,
968 PERSON_ID ,
969 ASSIGNMENT_ID ,
970 ELEMENT_TYPE_ID ,
971 DISTRIBUTION_DATE ,
972 EFFECTIVE_DATE ,
973 DISTRIBUTION_AMOUNT ,
974 DR_CR_FLAG ,
975 SOURCE_CODE ,
976 SOURCE_TYPE ,
977 TIME_PERIOD_ID ,
978 BATCH_NAME ,
979 STATUS_CODE ,
980 SET_OF_BOOKS_ID ,
981 GL_CODE_COMBINATION_ID ,
982 PROJECT_ID ,
983 EXPENDITURE_ORGANIZATION_ID,
984 EXPENDITURE_TYPE ,
985 TASK_ID ,
986 AWARD_ID ,
987 SUSPENSE_ORG_ACCOUNT_ID,
988 SUSPENSE_REASON_CODE ,
989 EFFORT_REPORT_ID ,
990 VERSION_NUM ,
991 SUMMARY_LINE_ID ,
992 REVERSAL_ENTRY_FLAG ,
993 ORIGINAL_LINE_FLAG ,
994 USER_DEFINED_FIELD ,
995 PERCENT ,
996 ORIG_SOURCE_TYPE ,
997 ORIG_LINE_ID ,
998 ATTRIBUTE_CATEGORY ,
999 ATTRIBUTE1 ,
1000 ATTRIBUTE2 ,
1001 ATTRIBUTE3 ,
1002 ATTRIBUTE4 ,
1003 ATTRIBUTE5 ,
1004 ATTRIBUTE6 ,
1005 ATTRIBUTE7 ,
1006 ATTRIBUTE8 ,
1007 ATTRIBUTE9 ,
1008 ATTRIBUTE10 ,
1009 ATTRIBUTE11 ,
1010 ATTRIBUTE12 ,
1011 ATTRIBUTE13 ,
1012 ATTRIBUTE14 ,
1013 ATTRIBUTE15 ,
1014 LAST_UPDATE_DATE ,
1015 LAST_UPDATED_BY ,
1016 LAST_UPDATE_LOGIN ,
1017 CREATED_BY ,
1018 CREATION_DATE ,
1019 PAYROLL_CONTROL_ID ,
1020 BUSINESS_GROUP_ID)
1021 SELECT
1022 PSP_ADJUSTMENT_LINES_S.NEXTVAL,
1023 pal.PERSON_ID ,
1024 pal.ASSIGNMENT_ID ,
1025 pal.ELEMENT_TYPE_ID ,
1026 pal.DISTRIBUTION_DATE ,
1027 pal.EFFECTIVE_DATE ,
1028 pal.DISTRIBUTION_AMOUNT * psl.SUMMARY_AMOUNT/psl.ATTRIBUTE30,
1029 pal.DR_CR_FLAG ,
1030 pal.SOURCE_CODE ,
1031 pal.SOURCE_TYPE ,
1032 pal.TIME_PERIOD_ID ,
1033 pal.BATCH_NAME ,
1034 pal.STATUS_CODE ,
1035 pal.SET_OF_BOOKS_ID ,
1036 pal.GL_CODE_COMBINATION_ID ,
1037 pal.PROJECT_ID ,
1038 pal.EXPENDITURE_ORGANIZATION_ID,
1039 pal.EXPENDITURE_TYPE ,
1040 pal.TASK_ID ,
1041 psl.AWARD_ID , --- new award_id
1042 pal.SUSPENSE_ORG_ACCOUNT_ID,
1043 pal.SUSPENSE_REASON_CODE ,
1044 pal.EFFORT_REPORT_ID ,
1045 pal.VERSION_NUM ,
1046 psl.SUMMARY_LINE_ID , -- new sum line_id
1047 pal.REVERSAL_ENTRY_FLAG ,
1048 pal.ORIGINAL_LINE_FLAG ,
1049 pal.USER_DEFINED_FIELD ,
1050 pal.PERCENT ,
1051 pal.ORIG_SOURCE_TYPE ,
1052 pal.ORIG_LINE_ID ,
1053 pal.ATTRIBUTE_CATEGORY ,
1054 pal.ATTRIBUTE1 ,
1055 pal.ATTRIBUTE2 ,
1056 pal.ATTRIBUTE3 ,
1057 pal.ATTRIBUTE4 ,
1058 pal.ATTRIBUTE5 ,
1059 pal.ATTRIBUTE6 ,
1060 pal.ATTRIBUTE7 ,
1061 pal.ATTRIBUTE8 ,
1062 pal.ATTRIBUTE9 ,
1063 pal.ATTRIBUTE10 ,
1064 pal.ATTRIBUTE11 ,
1065 pal.ATTRIBUTE12,
1066 pal.ATTRIBUTE13,
1067 pal.adjustment_LINE_ID, --- line_id
1068 pal.distribution_amount, -- dist_amount
1069 pal.LAST_UPDATE_DATE ,
1070 pal.LAST_UPDATED_BY ,
1071 pal.LAST_UPDATE_LOGIN ,
1072 pal.CREATED_BY ,
1073 pal.CREATION_DATE ,
1074 pal.PAYROLL_CONTROL_ID ,
1075 pal.BUSINESS_GROUP_ID
1076 FROM psp_adjustment_lines pal,
1077 psp_summary_lines psl
1078 WHERE pal.summary_line_id = psl.attribute29
1079 AND psl.gms_batch_name = p_gms_batch_name;
1080
1081 -- adjust the rounding off amount on the last distribution line within a summary line
1082 hr_utility.trace('GEt unmatched summary lines csr');
1083 OPEN get_unmatched_summary_lines;
1084 hr_utility.trace('GEt unmatched summary lines csr2');
1085 LOOP
1086 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
1087 FETCH get_unmatched_summary_lines INTO l_summary_line_id, l_sum_summary_amt, l_sum_dist_amt;
1088 IF get_unmatched_summary_lines%NOTFOUND THEN
1089 CLOSE get_unmatched_summary_lines;
1090 EXIT;
1091 END IF;
1092
1093 IF l_sum_summary_amt != l_sum_dist_amt THEN
1094 UPDATE psp_adjustment_lines pal
1095 SET distribution_amount = distribution_amount+l_sum_summary_amt-l_sum_dist_amt
1096 WHERE adjustment_line_id = (SELECT max(adjustment_line_id)
1097 FROM psp_adjustment_lines pal1
1098 WHERE pal1.summary_line_id = l_summary_line_id);
1099 END IF;
1100 END LOOP;
1101 hr_utility.trace('GEt unmatched summary lines csr4');
1102
1103 --- delete the original adjustment lines
1104 DELETE psp_adjustment_lines
1105 where summary_line_id IN (SELECT summary_line_id FROM psp_summary_lines
1106 WHERE payroll_control_id = p_payroll_control_id
1107 AND status_code = 'S');
1108
1109 DELETE psp_summary_lines
1110 WHERE status_code = 'S'
1111 and payroll_control_id = p_payroll_control_id;
1112 else
1113 hr_utility.trace('tieback_actual userhook..no default awards found to process');
1114 end if;
1115 exception
1116 when others then
1117 psp_sum_trans.g_error_api_path := 'TIEBACK_ADJUSTMENT'||psp_sum_trans.g_error_api_path;
1118 fnd_msg_pub.add_exc_msg('PSP_SUM_TRANS','TIEBACK_ADJUSTMENT');
1119 raise;
1120 END tieback_adjustment;
1121 */
1122 END PSP_ST_EXT;