[Home] [Help]
PACKAGE BODY: APPS.PA_PAY_INTERFACE
Source
1 PACKAGE BODY PA_PAY_INTERFACE AS
2 -- $Header: PAPAYIMPB.pls 120.39 2012/02/17 08:52:31 arbandyo noship $
3
4 PROCEDURE DERIVE_PRIORITY ;
5 PROCEDURE convert_to_fc ;
6 PROCEDURE get_orig_denom_amount (
7 P_SOURCE_EXPENDITURE_ITEM_ID NUMBER ,
8 P_EXPENDITURE_ITEM_DATE IN DATE,
9 P_DENOM_CURR_CODE IN VARCHAR ,
10 P_ORIG_AMOUNT IN NUMBER,
11 P_ORIG_CURR_CODE IN VARCHAR,
12 x_denom_orig_amount OUT NOCOPY NUMBER
13 ) ;
14 PROCEDURE process_burden;
15 PROCEDURE process_misc ;
16 PROCEDURE LOAD_PA_PROJ_TEMP
17 ( P_FROM_DATE IN DATE
18 , P_TO_DATE IN DATE
19 , P_PAYROLL_ID IN NUMBER
20 , P_EMP_ORG_ID IN NUMBER
21 , P_PERSON_ID IN NUMBER
22 );
23
24 PROCEDURE load_source_amounts (P_INTERFACE_RUN_ID NUMBER,
25 P_ELEMENT_TYPE_ID NUMBER,
26 P_PAYROLL_PROJ_SEGMENT VARCHAR2,
27 P_PAYROLL_TASK_SEGMENT VARCHAR2,
28 P_PAYROLL_EXP_ORG_SEGMENT VARCHAR2
29 );
30
31 PROCEDURE load_source_amounts_ext;
32
33 PROCEDURE CreateReverseCdl ( X_exp_item_id IN NUMBER,
34 X_backout_id IN NUMBER,
35 X_user IN NUMBER,
36 X_status OUT NOCOPY NUMBER) ;
37
38
39
40
41
42 PROCEDURE stage_one_validate ;
43
44
45 PROCEDURE get_person_TC
46 (
47 P_START_DATE DATE ,
48 P_END_DATE DATE
49 ) ;
50
51
52 PROCEDURE PROCESS_NONE;
53 PROCEDURE PROCESS_TC_ELEMENTS;
54
55 PROCEDURE PROCESS_NON_TC_ELEMENTS;
56 PROCEDURE PROCESS_AMOUNT_BASED ;
57 PROCEDURE PROCESS_TOTAL_RAW;
58 PROCEDURE REJECT_PAY_ELEMENT( P_PAY_SOURCE_ID NUMBER,P_PAY_ELEMENT_TYPE_ID NUMBER,P_PAY_ELEMENT_TYPE_CODE VARCHAR2, P_REJECTION_CODE VARCHAR2);
59 PROCEDURE REJECT_RELATED_PE;
60 PROCEDURE REJECT_PE_NO_SETUP ;
61 PROCEDURE ALLOCATE_PAYROLL;
62
63 /*Start changes for bug# 13579969*/
64
65 PROCEDURE REJECT_PAY_ELEMENT1( P_PAY_SOURCE_ID NUMBER, P_REJECTION_CODE VARCHAR2);
66
67 /*End changes for bug# 13579969*/
68
69 PROCEDURE INSERT_AUDIT_RECORDS
70 (
71 P_DATE_START DATE,
72 P_DATE_END DATE ,
73 P_PAYROLL_ID NUMBER,
74 P_EMP_ORG_ID NUMBER,
75 P_PERSON_ID NUMBER);
76
77 --PROCEDURE cleanup_distributions;
78 PROCEDURE mark_processed;
79 PROCEDURE ins_pay_proj;
80
81 PROCEDURE process_tc_misc( p_pay_source_id pa_pay_source_amounts.pay_source_id%TYPE);
82
83
84 PROCEDURE get_fc_rates(
85 X_ACCT_RATE_TYPE OUT NOCOPY VARCHAR2 ,
86 X_ACCT_RATE_DATE_CODE OUT NOCOPY VARCHAR2 ,
87 x_err_stage OUT NOCOPY NUMBER ,
88 x_err_code OUT NOCOPY VARCHAR2 ) ;
89
90 PROCEDURE WRITE_LOG ( P_MESSAGE_TYPE IN NUMBER,
91 P_MESSAGE IN VARCHAR2);
92
93 PROCEDURE write_log (
94 p_message_type IN NUMBER,
95 p_message IN VARCHAR2) IS
96
97 buffer_overflow EXCEPTION;
98 PRAGMA EXCEPTION_INIT(buffer_overflow, -20000);
99
100 BEGIN
101 --FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
102 IF (p_message_type = 1 ) THEN
103 pa_debug.write_file('LOG', 'pa.plsql.PA_PAY_INTERFACE : '|| p_message , 1);
104 ELSIF ((p_message_type = 2) AND ( G_DEBUG_MODE = 'Y') ) THEN
105 pa_debug.write_file('LOG', 'pa.plsql.PA_PAY_INTERFACE : '|| p_message , 1);
106 END IF;
107
108 EXCEPTION /* When exception occurs, program needs to be aborted. */
109 WHEN OTHERS THEN
110 raise;
111
112 END write_log;
113
114 /*
115 Procedure : INSERT_AUDIT_RECORDS
116 Type : Private
117 Purpose :
118 Generates the interface_run_id which is the unique identifier for
119 each payroll batch .
120 Makes an entry in PA_PAY_AUDIT table.
121
122 */
123
124 PROCEDURE INSERT_AUDIT_RECORDS
125 (P_DATE_START DATE,
126 P_DATE_END DATE ,
127 P_PAYROLL_ID NUMBER,
128 P_EMP_ORG_ID NUMBER,
129 P_PERSON_ID NUMBER
130 )
131 IS
132 L_RUN_SEQUENCE NUMBER;
133 L_MAX_RUN_ID NUMBER;
134 BEGIN
135 /* Generate interface run id and insert audit record for EBS Payroll */
136
137 FOR X IN
138 ( SELECT DISTINCT
139 PPIT.PAYROLL_ACTION_ID ,
140 PPIT.PAYROLL_ID ,
141 PPIT.TIME_PERIOD_START_DATE,
142 PPIT.TIME_PERIOD_END_DATE ,
143 PPIT.TIME_PERIOD_ID ,
144 PPIT.SOURCE_START_DATE, /* bug 12690224*/
145 PPIT.SOURCE_END_DATE
146 FROM
147 PA_PAY_INTERFACE_TEMP PPIT
148 ORDER BY
149 PAYROLL_ACTION_ID
150
151 )
152 LOOP
153
154 BEGIN
155 SELECT
156 NVL(MAX(RUN_SEQUENCE),0) +1 ,
157 MAX(INTERFACE_RUN_ID)
158 INTO
159 L_RUN_SEQUENCE ,
160 L_MAX_RUN_ID
161 FROM
162 PA_PAY_AUDIT_ALL
163 WHERE
164 BATCH_ID = X.PAYROLL_ACTION_ID AND
165 PAYROLL_ID = X.PAYROLL_ID AND
166 TIME_PERIOD_ID = X.TIME_PERIOD_ID ;
167 EXCEPTION
168 WHEN OTHERS THEN
169 L_RUN_SEQUENCE := 1;
170 L_MAX_RUN_ID := NULL;
171
172 END;
173
174 INSERT
175 INTO
176 PA_PAY_AUDIT
177 (
178 ORG_ID ,
179 BATCH_ID ,
180 PAYROLL_ID ,
181 INT_EXT_INDICATOR ,
182 PAY_PERIOD_START_DATE,
183 PAY_PERIOD_END_DATE ,
184 SOURCE_START_DATE , /* bug 12690224*/
185 SOURCE_END_DATE,
186 INTERFACE_RUN_ID ,
187 TIME_PERIOD_ID ,
188 PAYROLL_STATUS_FLAG ,
189 RUN_SEQUENCE ,
190 PREVIOUS_RUN_ID ,
191 LAST_UPDATE_DATE ,
192 LAST_UPDATED_BY ,
193 CREATION_DATE ,
194 CREATED_BY ,
195 REQUEST_ID
196 )
197 VALUES
198 (
199 G_ORG_ID ,
200 X.PAYROLL_ACTION_ID ,
201 X.PAYROLL_ID ,
202 'INT' ,
203 X.TIME_PERIOD_START_DATE ,
204 X.TIME_PERIOD_END_DATE ,
205 X.SOURCE_START_DATE , /* bug 12690224*/
206 X.SOURCE_END_DATE,
207 PA_PAY_INTERFACE_RUN_ID_S.NEXTVAL,
208 X.TIME_PERIOD_ID ,
209 'Y' ,
210 L_RUN_SEQUENCE ,
211 L_MAX_RUN_ID ,
212 G_RUN_DATE ,
213 G_USER_ID ,
214 G_RUN_DATE ,
215 G_USER_ID ,
216 G_REQUEST_ID
217 );
218 END LOOP;
219 write_log (LOG, 'Count of Run Ids generated for EBS payroll- ' ||sql%ROWCOUNT );
220 /* Generate interface run id and insert audit record for Third party Payroll */
221 FOR X IN
222 ( SELECT DISTINCT
223 PPEI.PAYROLL_BATCH_ID ,
224 PPEI.PAYROLL_ID ,
225 PPEI.TIME_PERIOD_START_DATE,
226 PPEI.TIME_PERIOD_END_DATE ,
227 PPEI.TIME_PERIOD_ID ,
228 PPEI.RETRO_PERIOD_START_DATE ,
229 PPEI.RETRO_PERIOD_END_DATE,
230 PPEP.PRIORITY
231 FROM
232 PA_PAY_EXT_INTERAFACE_V PPEI,
233 PA_PAY_EXTERNAL_PAYROLL PPEP
234 WHERE PPEP.PAYROLL_ID = PPEI.PAYROLL_ID AND
235 Trunc(PPEI.TIME_PERIOD_END_DATE) BETWEEN P_DATE_START AND P_DATE_END
236 AND PPEI.PAYROLL_ID = Nvl(P_PAYROLL_ID,PPEI.PAYROLL_ID)
237 AND PPEI.PERSON_ID = Nvl(P_PERSON_ID,PPEI.PERSON_ID)
238 AND PPEI.EMP_ORGANIZATION_ID = Nvl(P_EMP_ORG_ID,PPEI.EMP_ORGANIZATION_ID)
239 AND TRANSFER_STATUS_FLAG = 'P'
240 ORDER BY
241 PPEI.PAYROLL_BATCH_ID,
242 PPEP.PRIORITY
243 )
244 LOOP
245
246 BEGIN
247 SELECT
248 NVL(MAX(RUN_SEQUENCE),0) +1 ,
249 MAX(INTERFACE_RUN_ID)
250 INTO
251 L_RUN_SEQUENCE ,
252 L_MAX_RUN_ID
253 FROM
254 PA_PAY_AUDIT_ALL
255 WHERE
256 BATCH_ID = X.PAYROLL_BATCH_ID AND
257 PAYROLL_ID = X.PAYROLL_ID AND
258 TIME_PERIOD_ID = X.TIME_PERIOD_ID ;
259 EXCEPTION
260 WHEN OTHERS THEN
261 L_RUN_SEQUENCE := 1;
262 L_MAX_RUN_ID := NULL;
263
264 END;
265 write_log (LOG, 'Checking for third Party payrolls' );
266 INSERT
267 INTO
268 PA_PAY_AUDIT
269 (
270 ORG_ID ,
271 BATCH_ID ,
272 PAYROLL_ID ,
273 INT_EXT_INDICATOR ,
274 PAY_PERIOD_START_DATE,
275 PAY_PERIOD_END_DATE ,
276 INTERFACE_RUN_ID ,
277 TIME_PERIOD_ID ,
278 PAYROLL_STATUS_FLAG ,
279 RUN_SEQUENCE ,
280 PREVIOUS_RUN_ID ,
281 LAST_UPDATE_DATE ,
282 LAST_UPDATED_BY ,
283 CREATION_DATE ,
284 CREATED_BY ,
285 REQUEST_ID
286 )
287 VALUES
288 (
289 G_ORG_ID ,
290 X.PAYROLL_BATCH_ID ,
291 X.PAYROLL_ID ,
292 'EXT' ,
293 X.TIME_PERIOD_START_DATE ,
294 X.TIME_PERIOD_END_DATE ,
295 PA_PAY_INTERFACE_RUN_ID_S.NEXTVAL,
296 X.TIME_PERIOD_ID ,
297 'Y' ,
298 L_RUN_SEQUENCE ,
299 L_MAX_RUN_ID ,
300 G_RUN_DATE ,
301 G_USER_ID ,
302 G_RUN_DATE ,
303 G_USER_ID ,
304 G_REQUEST_ID
305 );
306
307 write_log (LOG, 'No of third party Payroll run ids generated - ' ||sql%ROWCOUNT );
308 END LOOP;
309 EXCEPTION
310 WHEN OTHERS THEN
311 write_log(Log,SQLERRM);
312 RAISE;
313 END INSERT_AUDIT_RECORDS;
314
315 /*
316 Procedure : INTERFACE_PAY
317 Type : Private
318 Purpose :
319 This is the main procedure called from the CP.
320 At the end of processing payroll , this procedure will spawn
321 an Audit report and the distribution processes(based on streamline)
322 option selected for CP
323 */
324
325
326 PROCEDURE INTERFACE_PAY
327 ( errbuf OUT NOCOPY VARCHAR2
328 ,retcode OUT NOCOPY VARCHAR2
329 ,P_FROM_DATE IN VARCHAR2
330 , P_TO_DATE IN VARCHAR2
331 ,P_STREAMLINE IN VARCHAR2
332 , P_PAYROLL_ID IN NUMBER
333 , P_EMP_ORG_ID IN NUMBER
334 , P_PERSON_ID IN NUMBER
335 ) IS
336
337 l_date_from DATE;
338 l_date_to DATE;
339 xml_layout BOOLEAN;
340 req_id NUMBER;
341
342 l_max_labor_date NUMBER;
343 l_pj_labor_date NUMBER;
344 l_btc_labor_date NUMBER;
345 l_error_code NUMBER;
346 l_rej_count PLS_INTEGER; /* Bug 12678472 */
347
348
349 CURSOR c_get_proj_segments IS
350 SELECT DISTINCT PAA.INTERFACE_RUN_ID,
351 PAA.PAYROLL_ID,
352 PAA.TIME_PERIOD_ID,
353 PAA.PAY_PERIOD_END_DATE,
354 PIT.ELEMENT_TYPE_ID,
355 PEM.PAYROLL_PROJ_SEGMENT,
356 PEM.PAYROLL_TASK_SEGMENT,
357 PEM.PAYROLL_EXP_ORG_SEGMENT
358 FROM PA_PAY_AUDIT PAA,
359 (select distinct
360 PAYROLL_ACTION_ID ,
361 PAYROLL_ID,
362 TIME_PERIOD_ID,
363 TIME_PERIOD_END_DATE,
364 ELEMENT_TYPE_ID
365 from
366 PA_PAY_INTERFACE_TEMP ) PIT,
367 PA_PAY_ELEMENT_MAPPING PEM
368 WHERE
369 PAA.BATCH_ID = PIT.PAYROLL_ACTION_ID
370 AND PAA.PAYROLL_ID = PIT.PAYROLL_ID
371 AND PAA.TIME_PERIOD_ID = PIT.TIME_PERIOD_ID
372 AND PAA.org_id = PEM.ORG_ID
373 AND PIT.TIME_PERIOD_END_DATE between pem.start_date_active and Nvl(pem.end_date_active,PIT.TIME_PERIOD_END_DATE +1)
374 and PIT.ELEMENT_TYPE_ID = pem.pay_element_type_id;
375
376
377 BEGIN
378 pa_debug.init_err_stack ('Interface Payroll Actuals');
379 pa_debug.set_process(
380 x_process => 'PLSQL',
381 x_debug_mode => 'Yes');
382
383
384 write_log (LOG, '---------------PARAMETERS----------- ');
385 write_log (LOG, 'P_FROM_DATE -> ' || P_FROM_DATE);
386 write_log (LOG, 'P_TO_DATE -> ' || P_TO_DATE);
387 write_log (LOG, 'P_STREAMLINE -> ' || P_STREAMLINE);
388
389 write_log (LOG, 'P_PAYROLL_ID -> ' || P_PAYROLL_ID);
390 write_log (LOG, 'P_EMP_ORG_ID -> ' || P_EMP_ORG_ID);
391 write_log (LOG, 'P_PERSON_ID -> ' || P_PERSON_ID);
392
393
394 -- SELECT org_id INTO G_ORG_ID FROM pa_implementations;
395 SELECT sysdate INTO G_RUN_DATE FROM dual;
396
397 G_ORG_ID := FND_GLOBAL.ORG_ID;
398 G_USER_ID :=FND_GLOBAL.USER_ID;
399 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID();
400 G_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
401 G_PROG_APPL_ID := FND_GLOBAL.PROG_APPL_ID;
402 G_FUNC_CURR_CODE := PA_CURRENCY.GET_CURRENCY_CODE;
403 G_LOGIN_ID := FND_GLOBAL.CONC_LOGIN_ID();
404 G_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
405 G_RETRO := 'N';
406
407 l_date_from := fnd_date.canonical_to_date(P_FROM_DATE);
408 l_date_to := fnd_date.canonical_to_date(P_TO_DATE);
409
410
411 pa_pay_validate.Validate_details(P_FROM_DATE,P_TO_DATE,P_PERSON_ID,P_PAYROLL_ID,l_error_code);
412 write_log(Log,'Third party payroll data in the interface data validated' );
413
414
415 LOAD_PA_PROJ_TEMP( l_date_from
416 , l_date_to
417 , P_PAYROLL_ID
418 , P_EMP_ORG_ID
419 , P_PERSON_ID
420 );
421
422 write_log(Log,'EBS Payroll data loaded to GTT' );
423
424 INSERT_AUDIT_RECORDS (l_date_from,l_date_to,P_PAYROLL_ID,P_EMP_ORG_ID,P_PERSON_ID);
425
426 -- Source amounts for EBS Payroll should be loaded by forming dynamic sql
427 -- based on Costing Segments selected in Pay Element Mapping set up
428
429 FOR i IN c_get_proj_segments
430 LOOP
431 write_log(Log,'Loading source amounts for ' ||i.ELEMENT_TYPE_ID);
432 load_source_amounts (i.INTERFACE_RUN_ID , i.ELEMENT_TYPE_ID ,
433 i.PAYROLL_PROJ_SEGMENT , i.PAYROLL_TASK_SEGMENT ,
434 i.PAYROLL_EXP_ORG_SEGMENT );
435 END LOOP;
436 write_log(Log,'Completed loading source amounts for EBS Payroll' );
437
438 load_source_amounts_ext;
439 DERIVE_PRIORITY ; /* Derive the processing priority for the Pay elements based on priority matrix*/
440 stage_one_validate; /* Common Validations fired on all payroll data that is being interfaced*/
441
442
443 /*
444 Interface Run Ids (Batches ) have to processed one by one in order.
445 This logic should not change with out PM Approval
446 */
447 FOR X IN (SELECT * FROM PA_PAY_AUDIT WHERE request_id = G_REQUEST_ID ORDER BY INTERFACE_RUN_ID)
448 LOOP
449 write_log( Log, '=========== Processing starts for =========== ' ) ;
450 write_log( Log, 'Payroll Id -' || X.PAYROLL_ID ) ;
451 write_log( Log, 'Pay Period Start -' || X.PAY_PERIOD_START_DATE );
452 write_log( Log, 'Pay Period End -' ||X.PAY_PERIOD_END_DATE );
453 write_log( Log, 'Source Period Start -' ||X.SOURCE_START_DATE ) ;
454 write_log( Log, 'Source Period End -' ||X.SOURCE_END_DATE ) ;
455 write_log( Log, 'Interface Run -' ||X.INTERFACE_RUN_ID ) ;
456
457
458 G_INTERFACE_RUN_ID := X.INTERFACE_RUN_ID;
459 G_PAY_PERIOD_START := X.PAY_PERIOD_START_DATE;
460 G_PAY_PERIOD_END := X.PAY_PERIOD_END_DATE;
461
462 /* Load Timecards for all Employees */
463
464 IF (X.SOURCE_START_DATE IS NULL) THEN
465 get_person_TC(X.PAY_PERIOD_START_DATE,X.PAY_PERIOD_END_DATE);
466 G_RETRO := 'N';
467 ELSE
468 get_person_TC(X.SOURCE_START_DATE,X.SOURCE_END_DATE);
469 G_RETRO := 'Y';
470 END IF;
471
472
473 PROCESS_NONE;
474 process_tc_elements; /*Process Hour based elements with TC Element set to YES*/
475 process_non_tc_elements; /*Process Hour based elements with TC Element set to NO*/
476 convert_to_fc;
477 process_amount_based; /*Process pay elements where distribution is based on Amount*/
478 convert_to_fc;
479 process_total_raw; /*Process pay elements where distribution is based on Total Raw Cost*/
480
481
482 DELETE FROM pa_pay_dist_lines
483 WHERE interface_run_id = G_INTERFACE_RUN_ID AND
484 person_id IN (SELECT DISTINCT PPS.PERSON_ID
485 FROM PA_PAY_REJECTIONS_ALL PPR , PA_PAY_SOURCE_AMOUNTS PPS
486 WHERE PPR.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
487 AND PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
488
489 ) ;
490
491 REJECT_RELATED_PE; /* If One Element is rejected, reject all other pay elements for each assignment */
492
493
494
495 FOR PPL IN
496 (
497 SELECT DISTINCT PERSON_ID,ASSIGNMENT_ID
498 FROM PA_PAY_SOURCE_AMOUNTS
499 WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
500 AND PERSON_ID NOT IN ( SELECT DISTINCT PPS.PERSON_ID
501 FROM PA_PAY_REJECTIONS_ALL PPR , PA_PAY_SOURCE_AMOUNTS PPS
502 WHERE PPR.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
503 AND PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
504
505 )
506 )
507 LOOP
508
509 /* Added for bug 13681581 */
510 BEGIN
511 SAVEPOINT process_emp_pay;
512 /* Added for bug 13681581 */
513
514 G_PERSON_ID := PPL.PERSON_ID;
515 G_ASSIGNMENT_ID := PPL.ASSIGNMENT_ID; -- Bug 13681581
516 /* Distribution over. Create EI */
517 process_burden;
518 process_misc;
519 allocate_payroll;
520 /* Cost Moved to EI. */
521 reject_pe_no_setup ; /*Reject Pay elements with no setup*/
522 mark_processed; /*Mark Payroll record as Processed*/
523 ins_pay_proj;
524
525 /* Added for bug 13681581 */
526 EXCEPTION
527 WHEN REJECT_EMP_PAY THEN
528 write_log (LOG, 'Rejecting Employee Pay');
529 ROLLBACK TO process_emp_pay;
530 reject_related_pe;
531 reject_pe_no_setup;
532
533 END ;
534 /* Added for bug 13681581 */
535
536 END LOOP;
537
538 END LOOP;-- X Loop
539
540 write_log(Log,'about to commit' );
541 COMMIT;
542
543
544 FND_REQUEST.set_org_id(G_ORG_ID);
545 write_log(LOG , 'Launching process to generate Audit Report');
546 xml_layout := FND_REQUEST.ADD_LAYOUT('PA','PAPAYAUD','en','US','PDF');
547 req_id := FND_REQUEST.SUBMIT_REQUEST('PA', 'PAPAYAUD', '', '', FALSE,P_FROM_DATE,P_TO_DATE,P_STREAMLINE,P_PAYROLL_ID,P_EMP_ORG_ID,P_PERSON_ID,G_REQUEST_ID );
548 write_log (LOG,'Submitted Request Id' ||req_id );
549
550
551 IF P_STREAMLINE = 'Y' THEN
552
553 FND_REQUEST.set_org_id(G_ORG_ID);
554 req_id := FND_REQUEST.SUBMIT_REQUEST('PA', 'PALDLC', '', '',FALSE,'','', '', '', '', '', '', '', '','' , '', '', 'Y', G_REQUEST_ID );
555
556 FND_REQUEST.set_org_id(G_ORG_ID);
557 req_id := FND_REQUEST.SUBMIT_REQUEST('PA', 'PASDUC', '', '',FALSE,'','' , '','' , '','' ,'' ,'' ,'' ,'' , 'Y', G_REQUEST_ID ) ;
558
559 FND_REQUEST.set_org_id(G_ORG_ID);
560 req_id := FND_REQUEST.SUBMIT_REQUEST('PA', 'PASDUC', '', '',FALSE,'','' ,'' ,'' , '', 'BTC','' ,'' , '', '', 'Y', G_REQUEST_ID);
561
562 END IF;
563
564 END INTERFACE_PAY;
565
566 /*
567 Procedure : LOAD_PA_PROJ_TEMP
568 Type : Private
569 Purpose :
570 Below procedure loads the payroll data to a GTT PA_PAY_INTERFACE_TEMP.
571 The is done so as to avoid acessing the Payroll tables for each employee.
572 */
573
574
575 PROCEDURE LOAD_PA_PROJ_TEMP
576 ( P_FROM_DATE IN DATE
577 , P_TO_DATE IN DATE
578 , P_PAYROLL_ID IN NUMBER
579 , P_EMP_ORG_ID IN NUMBER
580 , P_PERSON_ID IN NUMBER
581 )
582 IS
583 BEGIN
584
585
586 INSERT
587 INTO PA_PAY_INTERFACE_TEMP
588 (
589 COST_TYPE,
590 EFFECTIVE_DATE,
591 BUSINESS_GROUP_ID,
592 CONSOLIDATION_SET_NAME,
593 PAYROLL_NAME,
594 ORGANIZATION_NAME,
595 LOCATION_CODE,
596 FULL_NAME,
597 FIRST_NAME,
598 LAST_NAME,
599 MIDDLE_NAMES,
600 EMPLOYEE_NUMBER,
601 NATIONAL_IDENTIFIER,
602 ASSIGNMENT_NUMBER,
603 ELEMENT_NAME,
604 REPORTING_NAME,
605 CLASSIFICATION_NAME,
606 INPUT_VALUE_NAME,
607 UOM,
608 OUTPUT_CURRENCY_CODE,
609 CREDIT_AMOUNT,
610 DEBIT_AMOUNT,
611 CONCATENATED_SEGMENTS,
612 SEGMENT1,
613 SEGMENT2,
614 SEGMENT3,
615 SEGMENT4,
616 SEGMENT5,
617 SEGMENT6,
618 SEGMENT7,
619 SEGMENT8,
620 SEGMENT9,
621 SEGMENT10,
622 SEGMENT11,
623 SEGMENT12,
624 SEGMENT13,
625 SEGMENT14,
626 SEGMENT15,
627 SEGMENT16,
628 SEGMENT17,
629 SEGMENT18,
630 SEGMENT19,
631 SEGMENT20,
632 SEGMENT21,
633 SEGMENT22,
634 SEGMENT23,
635 SEGMENT24,
636 SEGMENT25,
637 SEGMENT26,
638 SEGMENT27,
639 SEGMENT28,
640 SEGMENT29,
641 SEGMENT30,
642 PAYROLL_ACTION_ID,
643 ASSIGNMENT_ACTION_ID,
644 CONSOLIDATION_SET_ID,
645 PAYROLL_ID,
646 TAX_UNIT_ID,
647 ORGANIZATION_ID,
648 LOCATION_ID,
649 PERSON_ID,
650 ASSIGNMENT_ID,
651 CLASSIFICATION_ID,
652 ELEMENT_TYPE_ID,
653 INPUT_VALUE_ID,
654 RUN_RESULT_ID,
655 GRE_NAME,
656 COST_ID,
657 TRANSFERED_TO_PRJ,
658 TIME_PERIOD_ID,
659 TIME_PERIOD_START_DATE,
660 TIME_PERIOD_END_DATE,
661 SOURCE_CREATOR_TYPE,
662 SOURCE_START_DATE,
663 SOURCE_END_DATE
664 )
665 SELECT COST_TYPE,
666 EFFECTIVE_DATE,
667 PCPV.BUSINESS_GROUP_ID,
668 CONSOLIDATION_SET_NAME,
669 PAYROLL_NAME,
670 ORGANIZATION_NAME,
671 LOCATION_CODE,
672 FULL_NAME,
673 FIRST_NAME,
674 LAST_NAME,
675 MIDDLE_NAMES,
676 PCPV.EMPLOYEE_NUMBER,
677 NATIONAL_IDENTIFIER,
678 PCPV.ASSIGNMENT_NUMBER,
679 ELEMENT_NAME,
680 REPORTING_NAME,
681 CLASSIFICATION_NAME,
682 INPUT_VALUE_NAME,
683 UOM,
684 OUTPUT_CURRENCY_CODE,
685 CREDIT_AMOUNT,
686 DEBIT_AMOUNT,
687 CONCATENATED_SEGMENTS,
688 SEGMENT1,
689 SEGMENT2,
690 SEGMENT3,
691 SEGMENT4,
692 SEGMENT5,
693 SEGMENT6,
694 SEGMENT7,
695 SEGMENT8,
696 SEGMENT9,
697 SEGMENT10,
698 SEGMENT11,
699 SEGMENT12,
700 SEGMENT13,
701 SEGMENT14,
702 SEGMENT15,
703 SEGMENT16,
704 SEGMENT17,
705 SEGMENT18,
706 SEGMENT19,
707 SEGMENT20,
708 SEGMENT21,
709 SEGMENT22,
710 SEGMENT23,
711 SEGMENT24,
712 SEGMENT25,
713 SEGMENT26,
714 SEGMENT27,
715 SEGMENT28,
716 SEGMENT29,
717 SEGMENT30,
718 PAYROLL_ACTION_ID,
719 ASSIGNMENT_ACTION_ID,
720 CONSOLIDATION_SET_ID,
721 PCPV.PAYROLL_ID,
722 TAX_UNIT_ID,
723 PCPV.ORGANIZATION_ID,
724 PCPV.LOCATION_ID,
725 PCPV.PERSON_ID,
726 PCPV.ASSIGNMENT_ID,
727 CLASSIFICATION_ID,
728 ELEMENT_TYPE_ID,
729 INPUT_VALUE_ID,
730 RUN_RESULT_ID,
731 GRE_NAME,
732 COST_ID,
733 TRANSFERED_TO_PRJ,
734 TIME_PERIOD_ID,
735 TIME_PERIOD_START_DATE,
736 TIME_PERIOD_END_DATE,
737 SOURCE_CREATOR_TYPE,
738 (CASE WHEN SOURCE_CREATOR_TYPE IN ('R','RR','EE','PR','NR') THEN SOURCE_START_DATE
739 ELSE NULL
740 END), /* bug 12690224*/
741 (CASE WHEN SOURCE_CREATOR_TYPE IN ('R','RR','EE','PR','NR') THEN SOURCE_END_DATE
742 ELSE NULL
743 END)
744 FROM
745 PAY_COSTING_PROJECTS_V PCPV ,
746 PER_ALL_ASSIGNMENTS_F PAAF ,
747 hr_organization_information HOI
748 WHERE
749 PCPV.PERSON_ID = PAAF.PERSON_ID AND
750 PCPV.ASSIGNMENT_NUMBER = PAAF.ASSIGNMENT_NUMBER AND
751 HOI.organization_id = PAAF.organization_id AND
752 HOI.ORG_INFORMATION1 = To_Char(FND_GLOBAL.ORG_ID) AND
753 PCPV.TIME_PERIOD_END_DATE BETWEEN (P_FROM_DATE ) AND ( P_TO_DATE )
754 AND PCPV.PAYROLL_ID = Nvl(P_PAYROLL_ID,PCPV.PAYROLL_ID)
755 AND PCPV.PERSON_ID = Nvl(P_PERSON_ID,PCPV.PERSON_ID)
756 AND PCPV.ORGANIZATION_ID = Nvl(P_EMP_ORG_ID,PCPV.ORGANIZATION_ID)
757 AND BALANCE_OR_COST = 'C'
758 AND Nvl(TRANSFERED_TO_PRJ,'N' ) = 'N';
759
760 write_log(Log,'Loaded EBS Payroll data - records ' || SQL%RowCount );
761 END LOAD_PA_PROJ_TEMP;
762
763
764 /*
765 Procedure : LOAD_SOURCE_AMOUNTS
766 Type : Private
767 Purpose : Below procedure loads the EBS payroll data to PA_PAY_SOURCE_AMOUNTS.
768 Payroll data will be grouped as per the Pay Element Rule setups in PA_PAY_ELEMENT_MAPPING
769 and the org/project/task segment values coming from Payroll
770 Dynamic SQL is built and executed for each pay element
771 Note : Applicable only for EBS Payroll.
772 : PA_PAY_SOURCE_AMOUNTS will show the amount associates with each
773 pay element and the status of the setups in PA_PAY_ELEMENT_MAPPING
774 at that time when distribution was run.
775 */
776
777 PROCEDURE load_source_amounts (P_INTERFACE_RUN_ID NUMBER,
778 P_ELEMENT_TYPE_ID NUMBER,
779 P_PAYROLL_PROJ_SEGMENT VARCHAR2,
780 P_PAYROLL_TASK_SEGMENT VARCHAR2,
781 P_PAYROLL_EXP_ORG_SEGMENT VARCHAR2
782 ) IS
783
784 l_insert_fixed VARCHAR2(2000);
785 l_insert_random VARCHAR2(2000);
786 l_sql_sel_fixed VARCHAR2(2000);
787 l_sql_sel_random VARCHAR2(2000);
788 l_sql_from_where VARCHAR2(2000);
789 l_sql_goup_Fixed VARCHAR2(2000);
790 l_sql_goup_random VARCHAR2(2000);
791 l_sql_final VARCHAR2(4000);
792 c_int INTEGER;
793 l_row_processed INTEGER;
794
795 BEGIN
796
797
798
799 l_insert_fixed :=
800 'INSERT
801 INTO PA_PAY_SOURCE_AMOUNTS
802 ( PAY_SOURCE_ID,
803 INTERFACE_RUN_ID,
804 PERSON_ID,
805 ASSIGNMENT_ID,
806 EMP_ORGANIZATION_ID,
807 PAY_ELEMENT_TYPE_ID,
808 PAY_ELEMENT_MAP_ID,
809 COST_TYPE_CODE,
810 DISTRIBUTION_BASIS_CODE,
811 TIMECARD_ELEMENT,
812 TIMECARD_EXP_TYPE,
813 ALLOW_MISC_FLAG,
814 EXPENDITURE_TYPE,
815 PROJ_COST_REQD,
816 PAYROLL_EXP_ORG_SEGMENT,
817 PAYROLL_PROJ_SEGMENT,
818 PAYROLL_TASK_SEGMENT,
819 PAY_CURRENCY_CODE,
820 ORG_ID,
821 REQUEST_ID,
822 LAST_UPDATE_DATE,
823 LAST_UPDATED_BY,
824 CREATION_DATE,
825 CREATED_BY' ;
826
827 -- Add PEM.PAY_ELEMENT_MAP_ID,
828 l_sql_sel_fixed :=
829 ' SELECT
830 PA_PAY_SOURCE_ID_S.nextval ,INNER.* from
831 (Select
832 PAA.INTERFACE_RUN_ID,
833 PIT.PERSON_ID,
834 PIT.ASSIGNMENT_ID,
835 PIT.ORGANIZATION_ID ,
836 PIT.ELEMENT_TYPE_ID,
837 PEM.PAY_ELEMENT_MAP_ID,
838 PEM.COST_TYPE_CODE,
839 PEM.DISTRIBUTION_BASIS_CODE,
840 PEM.TIMECARD_ELEMENT,
841 PEM.TIMECARD_EXP_TYPE,
842 PEM.ALLOW_MISC_FLAG,
843 PEM.EXPENDITURE_TYPE,
844 PEM.PROJ_COST_REQD,
845 PEM.PAYROLL_EXP_ORG_SEGMENT,
846 PEM.PAYROLL_PROJ_SEGMENT,
847 PEM.PAYROLL_TASK_SEGMENT,
848 PIT.OUTPUT_CURRENCY_CODE,
849 458 ORG,
850 FND_GLOBAL.CONC_REQUEST_ID,
851 trunc(sysdate)L_UPDATE_DATE,
852 fnd_global.user_id L_UPDATE_BY,
853 trunc(sysdate) C_UPDATE_DATE,
854 fnd_global.user_id C_UPDATE_BY,';
855
856 IF P_PAYROLL_PROJ_SEGMENT IS NOT NULL THEN
857 l_insert_random := ' , PAY_SOURCE_PROJECT_ID';
858 l_sql_sel_random := P_PAYROLL_PROJ_SEGMENT || ' , ';
859 l_sql_goup_random := ' , ' || P_PAYROLL_PROJ_SEGMENT ;
860 END IF;
861
862 IF P_PAYROLL_TASK_SEGMENT IS NOT NULL THEN
863 l_insert_random := l_insert_random || ' , PAY_SOURCE_TASK_ID';
864 l_sql_sel_random := l_sql_sel_random || P_PAYROLL_TASK_SEGMENT || ' , ';
865 l_sql_goup_random := l_sql_goup_random || ' , ' || P_PAYROLL_TASK_SEGMENT ;
866 END IF;
867
868 IF P_PAYROLL_EXP_ORG_SEGMENT IS NOT NULL THEN
869 l_insert_random := l_insert_random || ' , PAY_SOURCE_ORGANIZATION_ID';
870 l_sql_sel_random := l_sql_sel_random || P_PAYROLL_EXP_ORG_SEGMENT || ' , ';
871 l_sql_goup_random := l_sql_goup_random || ' , ' || P_PAYROLL_EXP_ORG_SEGMENT ;
872 END IF;
873
874
875 l_insert_random := l_insert_random || ' , PAY_AMOUNT )';
876 l_sql_sel_random := l_sql_sel_random || ' SUM(PIT.DEBIT_AMOUNT- PIT.CREDIT_AMOUNT) AMOUNT';
877
878 l_sql_from_where :=
879 ' FROM PA_PAY_AUDIT PAA,
880 PA_PAY_INTERFACE_TEMP PIT,
881 PA_PAY_ELEMENT_MAPPING PEM
882 WHERE PAA.INTERFACE_RUN_ID = :C_INTERFACE_RUN_ID
883 AND PAA.BATCH_ID = PIT.PAYROLL_ACTION_ID
884 AND PAA.PAYROLL_ID = PIT.PAYROLL_ID
885 AND PAA.TIME_PERIOD_ID = PIT.TIME_PERIOD_ID
886 AND ((PAA.SOURCE_START_DATE IS NULL and PIT.SOURCE_START_DATE IS NULL) OR
887 ( (PAA.SOURCE_START_DATE = PIT.SOURCE_START_DATE) AND
888 ( PAA.SOURCE_END_DATE = PIT.SOURCE_END_DATE)
889 ) ) /* bug 12690224*/
890 AND PIT.ELEMENT_TYPE_ID = PEM.PAY_ELEMENT_TYPE_ID
891 AND PIT.ELEMENT_TYPE_ID = :C_ELEMENT_TYPE_ID
892 AND (PIT.TIME_PERIOD_END_DATE BETWEEN PEM.START_DATE_ACTIVE AND NVL(PEM.END_DATE_ACTIVE,PIT.TIME_PERIOD_END_DATE +1))' ;
893
894 l_sql_goup_Fixed :=
895 ' GROUP BY
896 PAA.INTERFACE_RUN_ID,
897 PIT.PERSON_ID,
898 PIT.ASSIGNMENT_ID,
899 PIT.ORGANIZATION_ID ,
900 PIT.ELEMENT_TYPE_ID,
901 PEM.PAY_ELEMENT_MAP_ID,
902 PEM.COST_TYPE_CODE,
903 PEM.DISTRIBUTION_BASIS_CODE,
904 PEM.TIMECARD_ELEMENT,
905 PEM.TIMECARD_EXP_TYPE,
906 PEM.ALLOW_MISC_FLAG,
907 PEM.EXPENDITURE_TYPE,
908 PEM.PROJ_COST_REQD,
909 PEM.PAYROLL_EXP_ORG_SEGMENT,
910 PEM.PAYROLL_PROJ_SEGMENT,
911 PEM.PAYROLL_TASK_SEGMENT,
912 PIT.OUTPUT_CURRENCY_CODE ';
913
914
915 l_sql_final := l_insert_fixed ||l_insert_random || l_sql_sel_fixed ||l_sql_sel_random || l_sql_from_where ||l_sql_goup_Fixed || l_sql_goup_random|| ') INNER';
916
917 write_log(DEBUG,l_sql_final );
918
919
920
921 c_int := DBMS_SQL.OPEN_CURSOR;
922 DBMS_SQL.PARSE(c_int, l_sql_final , dbms_sql.native);
923 DBMS_SQL.BIND_VARIABLE(c_int, ':C_INTERFACE_RUN_ID', P_INTERFACE_RUN_ID);
924 DBMS_SQL.BIND_VARIABLE(c_int, ':C_ELEMENT_TYPE_ID', P_ELEMENT_TYPE_ID);
925 l_row_processed := dbms_sql.execute(c_int);
926
927 write_log(Log,'P_INTERFACE_RUN_ID - ' || P_INTERFACE_RUN_ID || ' P_ELEMENT_TYPE_ID - '|| P_ELEMENT_TYPE_ID || 'Records processed- ' || l_row_processed );
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 write_log(Log,SQLERRM);
932 RAISE;
933 END;
934
935
936
937 /*
938 Procedure : LOAD_SOURCE_AMOUNTS_EXT
939 Type : Private
940 Purpose :
941 Below procedure loads the Third Party payroll data to PA_PAY_SOURCE_AMOUNTS.
942 Payroll data will be grouped as per the Pay Element Rule set-ups in PA_PAY_ELEMENT_MAPPING
943 and the org/project/task segment values
944 Note : Applicable only for External Payroll.
945
946 */
947
948
949 PROCEDURE load_source_amounts_ext IS
950 BEGIN
951
952 INSERT
953 INTO
954 PA_PAY_SOURCE_AMOUNTS
955 (
956 PAY_SOURCE_ID ,
957 INTERFACE_RUN_ID ,
958 PERSON_ID ,
959 ASSIGNMENT_ID ,
960 EMP_ORGANIZATION_ID ,
961 PAY_ELEMENT_TYPE_CODE ,
962 PAY_ELEMENT_MAP_ID ,
963 COST_TYPE_CODE ,
964 DISTRIBUTION_BASIS_CODE,
965 TIMECARD_ELEMENT ,
966 TIMECARD_EXP_TYPE ,
967 ALLOW_MISC_FLAG ,
968 EXPENDITURE_TYPE ,
969 PROJ_COST_REQD ,
970 PAY_SOURCE_ORGANIZATION_ID ,
971 PAY_SOURCE_PROJECT_ID ,
972 PAY_SOURCE_TASK_ID ,
973 PAY_CURRENCY_CODE ,
974 ORG_ID ,
975 REQUEST_ID ,
976 LAST_UPDATE_DATE ,
977 LAST_UPDATED_BY ,
978 CREATION_DATE ,
979 CREATED_BY ,
980 PAY_AMOUNT
981 )
982 SELECT
983 PA_PAY_SOURCE_ID_S.nextval ,
984 INNER.*
985 FROM
986 (
987 SELECT
988 PAA.INTERFACE_RUN_ID ,
989 PPEI.PERSON_ID ,
990 PPEI.ASSIGNMENT_ID ,
991 PPEI.EMP_ORGANIZATION_ID ,
992 PPEI.PAY_ELEMENT_TYPE_CODE ,
993 PEM.PAY_ELEMENT_MAP_ID ,
994 PEM.COST_TYPE_CODE ,
995 PEM.DISTRIBUTION_BASIS_CODE ,
996 PEM.TIMECARD_ELEMENT ,
997 PEM.TIMECARD_EXP_TYPE ,
998 PEM.ALLOW_MISC_FLAG ,
999 PEM.EXPENDITURE_TYPE ,
1000 PEM.PROJ_COST_REQD ,
1001 PPEI.PAY_SOURCE_ORGANIZATION_ID ,
1002 PPEI.PAY_SOURCE_PROJECT_ID ,
1003 PPEI.PAY_SOURCE_TASK_ID ,
1004 PPEI.PAY_CURRENCY_CODE ,
1005 G_ORG_ID ,
1006 FND_GLOBAL.CONC_REQUEST_ID ,
1007 TRUNC(sysdate)L_UPDATE_DATE ,
1008 fnd_global.user_id L_UPDATE_BY,
1009 TRUNC(sysdate) C_UPDATE_DATE ,
1010 fnd_global.user_id C_UPDATE_BY,
1011 SUM(PAY_AMOUNT) PAY_AMOUNT
1012 FROM
1013 PA_PAY_AUDIT PAA ,
1014 PA_PAY_EXT_INTERAFACE_V PPEI,
1015 PA_PAY_ELEMENT_MAPPING PEM
1016 WHERE
1017 PAA.REQUEST_ID = G_REQUEST_ID AND
1018 PAA.BATCH_ID = PPEI.PAYROLL_BATCH_ID AND
1019 PAA.PAYROLL_ID = PPEI.PAYROLL_ID AND
1020 PAA.TIME_PERIOD_ID = PPEI.TIME_PERIOD_ID AND
1021 PPEI.PAY_ELEMENT_TYPE_CODE = PEM.PAY_ELEMENT_CODE AND
1022 /*PPEI.ELEMENT_TYPE_ID = :C_ELEMENT_TYPE_ID AND */
1023 --For third party , every thign shd run at one shot
1024 (
1025 PPEI.TIME_PERIOD_END_DATE BETWEEN PEM.START_DATE_ACTIVE
1026 AND NVL(PEM.END_DATE_ACTIVE,PPEI.TIME_PERIOD_END_DATE +
1027 1 )
1028 )
1029 GROUP BY
1030 PAA.INTERFACE_RUN_ID ,
1031 PPEI.PERSON_ID ,
1032 PPEI.ASSIGNMENT_ID ,
1033 PPEI.EMP_ORGANIZATION_ID ,
1034 PPEI.PAY_ELEMENT_TYPE_CODE ,
1035 PEM.PAY_ELEMENT_MAP_ID ,
1036 PEM.COST_TYPE_CODE ,
1037 PEM.DISTRIBUTION_BASIS_CODE,
1038 PEM.TIMECARD_ELEMENT ,
1039 PEM.TIMECARD_EXP_TYPE ,
1040 PEM.ALLOW_MISC_FLAG ,
1041 PEM.EXPENDITURE_TYPE ,
1042 PEM.PROJ_COST_REQD ,
1043 PPEI.PAY_SOURCE_ORGANIZATION_ID ,
1044 PPEI.PAY_SOURCE_PROJECT_ID ,
1045 PPEI.PAY_SOURCE_TASK_ID ,
1046 PPEI.PAY_CURRENCY_CODE
1047 )
1048 INNER;
1049
1050 write_log(Log,'Source Amounts for External Payrolls loaded ' );
1051
1052 END load_source_amounts_ext;
1053
1054
1055 /*
1056 Procedure : STAGE_ONE_VALIDATE
1057 Type : Private
1058 Purpose :
1059 Basic validations that are done on the incoming data before processing
1060 them for each employee
1061 Note : Applicable only all Payrolls (EBS and External).
1062
1063 */
1064
1065
1066 PROCEDURE stage_one_validate
1067 IS
1068 BEGIN
1069
1070 /*RC10 Project Costing required */
1071 INSERT
1072 INTO
1073 PA_PAY_REJECTIONS_ALL
1074 (
1075 PAY_REJECTION_ID ,
1076 INTERFACE_RUN_ID ,
1077 PERSON_ID ,
1078 ASSIGNMENT_ID ,
1079 PAY_SOURCE_ID ,
1080 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1081 REJECTION_CODE ,
1082 LAST_UPDATE_DATE ,
1083 LAST_UPDATED_BY ,
1084 CREATION_DATE ,
1085 CREATED_BY
1086 )
1087 SELECT
1088 PA_PAY_REJECTION_ID_S.NEXTVAL,
1089 INTERFACE_RUN_ID ,
1090 PERSON_ID ,
1091 ASSIGNMENT_ID ,
1092 Pay_source_id ,
1093 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1094 'RC 10' ,
1095 G_RUN_DATE ,
1096 G_USER_ID ,
1097 G_RUN_DATE ,
1098 G_USER_ID
1099 FROM
1100 PA_PAY_SOURCE_AMOUNTS
1101 WHERE
1102 PROJ_COST_REQD = 'YES' AND
1103 /* PAYROLL_PROJ_SEGMENT IS NULL AND Bug 12678472 */
1104 PAY_SOURCE_PROJECT_ID IS NULL AND
1105 REQUEST_ID = G_REQUEST_ID;
1106
1107
1108 /*RC-15 Distribution Method NONE - Project/Task information missing */
1109 INSERT
1110 INTO
1111 PA_PAY_REJECTIONS_ALL
1112 (
1113 PAY_REJECTION_ID ,
1114 INTERFACE_RUN_ID ,
1115 PERSON_ID ,
1116 ASSIGNMENT_ID ,
1117 PAY_SOURCE_ID ,
1118 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1119 REJECTION_CODE ,
1120 LAST_UPDATE_DATE ,
1121 LAST_UPDATED_BY ,
1122 CREATION_DATE ,
1123 CREATED_BY
1124 )
1125 SELECT
1126 PA_PAY_REJECTION_ID_S.NEXTVAL,
1127 INTERFACE_RUN_ID ,
1128 PERSON_ID ,
1129 ASSIGNMENT_ID ,
1130 PAY_SOURCE_ID ,
1131 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1132 'RC 15' ,
1133 G_RUN_DATE ,
1134 G_USER_ID ,
1135 G_RUN_DATE ,
1136 G_USER_ID
1137 FROM
1138 PA_PAY_SOURCE_AMOUNTS
1139 WHERE
1140 DISTRIBUTION_BASIS_CODE = 'NONE' AND
1141 (
1142 PAY_SOURCE_PROJECT_ID IS NULL OR
1143 PAY_SOURCE_TASK_ID IS NULL
1144 )
1145 AND
1146 REQUEST_ID = G_REQUEST_ID;
1147
1148
1149 /*RC-24 Negative pay amount */ /*Start changes for bug# 13579969*/
1150
1151 INSERT
1152 INTO
1153 PA_PAY_REJECTIONS_ALL
1154 (
1155 PAY_REJECTION_ID ,
1156 INTERFACE_RUN_ID ,
1157 PERSON_ID ,
1158 ASSIGNMENT_ID ,
1159 PAY_SOURCE_ID ,
1160 PAY_ELEMENT_TYPE_ID ,
1161 REJECTION_CODE ,
1162 LAST_UPDATE_DATE ,
1163 LAST_UPDATED_BY ,
1164 CREATION_DATE ,
1165 CREATED_BY
1166 )
1167 SELECT
1168 PA_PAY_REJECTION_ID_S.NEXTVAL,
1169 PPS.INTERFACE_RUN_ID ,
1170 PERSON_ID ,
1171 ASSIGNMENT_ID ,
1172 Pay_source_id ,
1173 PAY_ELEMENT_TYPE_ID ,
1174 'RC 24' ,
1175 G_RUN_DATE ,
1176 G_USER_ID ,
1177 G_RUN_DATE ,
1178 G_USER_ID
1179 FROM
1180 PA_PAY_SOURCE_AMOUNTS PPS, PA_PAY_AUDIT PPA
1181 WHERE
1182 PPS.INTERFACE_RUN_ID = PPA.INTERFACE_RUN_ID AND
1183 PPA.SOURCE_START_DATE IS NULL AND
1184 PPS.PAY_AMOUNT < 0 AND
1185 NVL(PPS.ALLOW_MISC_FLAG,'NO') ='NO' AND /*Added for bug#13595751*/
1186 PPS.REQUEST_ID = G_REQUEST_ID;
1187
1188 /*End changes for bug# 13579969*/
1189
1190
1191 /*RC-70 Invalid Project and Task values */
1192 INSERT
1193 INTO
1194 PA_PAY_REJECTIONS_ALL
1195 (
1196 PAY_REJECTION_ID ,
1197 INTERFACE_RUN_ID ,
1198 PERSON_ID ,
1199 ASSIGNMENT_ID ,
1200 PAY_SOURCE_ID ,
1201 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1202 REJECTION_CODE ,
1203 LAST_UPDATE_DATE ,
1204 LAST_UPDATED_BY ,
1205 CREATION_DATE ,
1206 CREATED_BY
1207 )
1208 SELECT
1209 PA_PAY_REJECTION_ID_S.NEXTVAL,
1210 INTERFACE_RUN_ID ,
1211 PERSON_ID ,
1212 ASSIGNMENT_ID ,
1213 Pay_source_id ,
1214 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1215 'RC 70' ,
1216 G_RUN_DATE ,
1217 G_USER_ID ,
1218 G_RUN_DATE ,
1219 G_USER_ID
1220 FROM
1221 PA_PAY_SOURCE_AMOUNTS PPSA
1222 WHERE
1223 PAY_SOURCE_PROJECT_ID IS NOT NULL AND
1224 PAY_SOURCE_TASK_ID IS NOT NULL AND
1225 NOT EXISTS
1226 (
1227 SELECT
1228 1
1229 FROM
1230 PA_PROJECTS PPA ,
1231 PA_TASKS PTA
1232 WHERE
1233 PPA.PROJECT_ID = PPSA.PAY_SOURCE_PROJECT_ID AND
1234 PTA.TASK_ID = PPSA.PAY_SOURCE_TASK_ID AND
1235 PTA.PROJECT_ID = PPA.PROJECT_ID AND /*12824209 */
1236 PA_TASK_UTILS.CHECK_CHILD_EXISTS(PTA.TASK_ID) = 0
1237 )
1238 AND
1239 REQUEST_ID = G_REQUEST_ID;
1240
1241
1242
1243 /*RC 40 Reject the Employees Payroll when unmatched negative transactions exist.*/
1244 /*RC 45 Reject the Employee when costing method cannot be derived*/
1245 /*RC 50 Reject the Employees when costing method mismatch*/
1246
1247 FOR X IN
1248 (SELECT
1249 INTERFACE_RUN_ID ,
1250 TRUNC(Nvl(SOURCE_START_DATE,PAY_PERIOD_START_DATE))START_DATE ,
1251 TRUNC(Nvl(SOURCE_END_DATE,PAY_PERIOD_END_DATE)) END_DATE
1252 FROM
1253 PA_PAY_AUDIT
1254 WHERE
1255 REQUEST_ID = G_REQUEST_ID
1256 )
1257 LOOP
1258
1259 -- Bug 12783959
1260 -- RC 16
1261 -- Reject the Payroll if there exists a TC with Payroll amount and that is not yet costed.
1262
1263 write_log(Log,'Checking uncosted payroll amounts in ' || X.START_DATE || '-' || X.END_DATE);
1264
1265 INSERT
1266 INTO
1267 PA_PAY_REJECTIONS_ALL
1268 (
1269 PAY_REJECTION_ID ,
1270 INTERFACE_RUN_ID ,
1271 PERSON_ID ,
1272 ASSIGNMENT_ID ,
1273 PAY_SOURCE_ID ,
1274 PAY_ELEMENT_TYPE_ID ,
1275 REJECTION_CODE ,
1276 LAST_UPDATE_DATE ,
1277 LAST_UPDATED_BY ,
1278 CREATION_DATE ,
1279 CREATED_BY
1280 )
1281 SELECT
1282 PA_PAY_REJECTION_ID_S.NEXTVAL,
1283 INTERFACE_RUN_ID ,
1284 PERSON_ID ,
1285 ASSIGNMENT_ID ,
1286 PAY_SOURCE_ID ,
1287 PAY_ELEMENT_TYPE_ID ,
1288 'RC 16' ,
1289 G_RUN_DATE ,
1290 G_USER_ID ,
1291 G_RUN_DATE ,
1292 G_USER_ID
1293 FROM
1294 PA_PAY_SOURCE_AMOUNTS
1295 WHERE
1296 PERSON_ID IN
1297 (
1298 SELECT
1299 EXP.INCURRED_BY_PERSON_ID
1300 FROM
1301 PA_EXPENDITURE_ITEMS ITEM,
1302 PA_EXPENDITURES EXP
1303 WHERE
1304 EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1305 ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
1306 EXP.INCURRED_BY_PERSON_ID IN
1307 (
1308 SELECT DISTINCT
1309 PERSON_ID
1310 FROM
1311 PA_PAY_SOURCE_AMOUNTS
1312 WHERE
1313 INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
1314 )
1315 AND
1316 (
1317 Trunc(ITEM.EXPENDITURE_ITEM_DATE) BETWEEN X.START_DATE AND X.END_DATE
1318 )
1319 AND
1320 (
1321 ITEM.INTERFACE_RUN_ID IS NOT NULL AND
1322 Nvl(ITEM.COST_DISTRIBUTED_FLAG,'N') = 'N'
1323 -- Nvl(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
1324 )
1325 )
1326 AND INTERFACE_RUN_ID = X.INTERFACE_RUN_ID ;
1327
1328 write_log (LOG, 'Count Rejected RC 16 ' ||sql%ROWCOUNT );
1329
1330 -- RC 20
1331
1332 INSERT
1333 INTO
1334 PA_PAY_REJECTIONS_ALL
1335 (
1336 PAY_REJECTION_ID ,
1337 INTERFACE_RUN_ID ,
1338 PERSON_ID ,
1339 ASSIGNMENT_ID ,
1340 PAY_SOURCE_ID ,
1341 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1342 REJECTION_CODE ,
1343 LAST_UPDATE_DATE ,
1344 LAST_UPDATED_BY ,
1345 CREATION_DATE ,
1346 CREATED_BY
1347 )
1348 SELECT
1349 PA_PAY_REJECTION_ID_S.NEXTVAL,
1350 INTERFACE_RUN_ID ,
1351 PERSON_ID ,
1352 ASSIGNMENT_ID ,
1353 Pay_source_id ,
1354 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1355 'RC 20' , /*Bug 13551455*/
1356 G_RUN_DATE ,
1357 G_USER_ID ,
1358 G_RUN_DATE ,
1359 G_USER_ID
1360 FROM
1361 PA_PAY_SOURCE_AMOUNTS
1362 WHERE
1363 PERSON_ID IN
1364 (
1365 SELECT
1366 EXP.INCURRED_BY_PERSON_ID
1367 FROM
1368 PA_EXPENDITURE_ITEMS ITEM,
1369 PA_EXPENDITURES EXP
1370 WHERE
1371 EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1372 EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
1373 ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
1374 NVL(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y' AND
1375 ITEM.QUANTITY < 0 AND
1376 EXP.INCURRED_BY_PERSON_ID IN
1377 (
1378 SELECT DISTINCT
1379 PERSON_ID
1380 FROM
1381 PA_PAY_SOURCE_AMOUNTS
1382 WHERE
1383 INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
1384 )
1385 AND
1386 (
1387 EXP.EXPENDITURE_ENDING_DATE BETWEEN X.START_DATE AND
1388 X.END_DATE
1389 )
1390 )
1391 AND interface_run_id = X.INTERFACE_RUN_ID;
1392
1393
1394
1395 --RC45
1396
1397 INSERT
1398 INTO
1399 PA_PAY_REJECTIONS_ALL
1400 (
1401 PAY_REJECTION_ID ,
1402 INTERFACE_RUN_ID ,
1403 PERSON_ID ,
1404 ASSIGNMENT_ID ,
1405 PAY_SOURCE_ID ,
1406 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1407 REJECTION_CODE ,
1408 LAST_UPDATE_DATE ,
1409 LAST_UPDATED_BY ,
1410 CREATION_DATE ,
1411 CREATED_BY
1412 )
1413 SELECT
1414 PA_PAY_REJECTION_ID_S.NEXTVAL,
1415 INTERFACE_RUN_ID ,
1416 PERSON_ID ,
1417 ASSIGNMENT_ID ,
1418 PAY_SOURCE_ID ,
1419 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1420 'RC 45' ,
1421 G_RUN_DATE ,
1422 G_USER_ID ,
1423 G_RUN_DATE ,
1424 G_USER_ID
1425 FROM
1426 PA_PAY_SOURCE_AMOUNTS
1427 WHERE
1428 PERSON_ID IN
1429 (
1430 SELECT
1431 EXP.INCURRED_BY_PERSON_ID
1432 FROM
1433 PA_EXPENDITURE_ITEMS ITEM,
1434 PA_EXPENDITURES EXP
1435 WHERE
1436 EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1437 EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
1438 ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
1439 NVL(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y' AND
1440 EXP.INCURRED_BY_PERSON_ID IN
1441 (
1442 SELECT DISTINCT
1443 PERSON_ID
1444 FROM
1445 PA_PAY_SOURCE_AMOUNTS
1446 WHERE
1447 INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
1448 )
1449 AND
1450 (
1451 EXP.EXPENDITURE_ENDING_DATE BETWEEN X.START_DATE AND
1452 X.END_DATE
1453 )
1454 AND
1455 get_cost_method(ITEM.expenditure_item_id,
1456 ITEM.expenditure_item_date)IS NULL
1457 ) ;
1458
1459 --RC50
1460
1461 INSERT
1462 INTO
1463 PA_PAY_REJECTIONS_ALL
1464 (
1465 PAY_REJECTION_ID ,
1466 INTERFACE_RUN_ID ,
1467 PERSON_ID ,
1468 ASSIGNMENT_ID ,
1469 PAY_SOURCE_ID ,
1470 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1471 REJECTION_CODE ,
1472 LAST_UPDATE_DATE ,
1473 LAST_UPDATED_BY ,
1474 CREATION_DATE ,
1475 CREATED_BY
1476 )
1477 SELECT
1478 PA_PAY_REJECTION_ID_S.NEXTVAL,
1479 INTERFACE_RUN_ID ,
1480 PERSON_ID ,
1481 ASSIGNMENT_ID ,
1482 PAY_SOURCE_ID ,
1483 PAY_ELEMENT_TYPE_ID , /* Bug 12678472 */
1484 'RC 50' ,
1485 G_RUN_DATE ,
1486 G_USER_ID ,
1487 G_RUN_DATE ,
1488 G_USER_ID
1489 FROM
1490 PA_PAY_SOURCE_AMOUNTS
1491 WHERE
1492 PERSON_ID IN
1493 (
1494 SELECT
1495 EXP.INCURRED_BY_PERSON_ID
1496 FROM
1497 PA_EXPENDITURE_ITEMS ITEM,
1498 PA_EXPENDITURES EXP
1499 WHERE
1500 EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1501 EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
1502 ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
1503 NVL(ITEM.NET_ZERO_ADJUSTMENT_FLAG,'N') <> 'Y' AND
1504 EXP.INCURRED_BY_PERSON_ID IN
1505 (
1506 SELECT DISTINCT
1507 PERSON_ID
1508 FROM
1509 PA_PAY_SOURCE_AMOUNTS
1510 WHERE
1511 INTERFACE_RUN_ID = X.INTERFACE_RUN_ID
1512 )
1513 AND
1514 (
1515 EXP.EXPENDITURE_ENDING_DATE BETWEEN X.START_DATE AND
1516 X.END_DATE
1517 )
1518 AND
1519 (
1520 COSTING_METHOD IS NOT NULL AND
1521 COSTING_METHOD <> get_cost_method(
1522 ITEM.expenditure_item_id,ITEM.expenditure_item_date)
1523 )
1524 ) ;
1525
1526
1527
1528
1529
1530
1531
1532
1533 END LOOP;
1534
1535
1536
1537 /*RC55 Invalid Expenditure Organization
1538 Needs to eb tuned further
1539 INSERT
1540 INTO
1541 PA_PAY_REJECTIONS_ALL
1542 (
1543 INTERFACE_RUN_ID ,
1544 PERSON_ID ,
1545 ASSIGNMENT_ID ,
1546 PAY_SOURCE_ID ,
1547 PAY_ELEMENT_TYPE_ID,
1548 REJECTION_CODE ,
1549 LAST_UPDATE_DATE ,
1550 LAST_UPDATED_BY ,
1551 CREATION_DATE ,
1552 CREATED_BY
1553 )
1554 SELECT
1555 INTERFACE_RUN_ID ,
1556 PERSON_ID ,
1557 ASSIGNMENT_ID ,
1558 Pay_source_id ,
1559 PAY_ELEMENT_TYPE_ID ,
1560 'RC50' ,
1561 G_RUN_DATE ,
1562 G_USER_ID ,
1563 G_RUN_DATE ,
1564 G_USER_ID
1565 FROM
1566 PA_PAY_SOURCE_AMOUNTS PPSA,
1567 PA_PAY_AUDIT PPA
1568 WHERE
1569 PPSA.INTERFACE_RUN_ID = PPA.INTERFACE_RUN_ID AND
1570 PAY_SOURCE_ORGANIZATION_ID IS NOT NULL AND
1571 PPSA.DISTRIBUTION_BASIS_CODE in ('ST_AMT',
1572 'OT_AMT',
1573 'ST_OT_AMT',
1574 'NONE')
1575 NOT EXISTS
1576 (SELECT
1577 'Y'
1578 FROM
1579 pa_organizations_expend_v paev
1580 WHERE
1581 organization_id = PPSA.PAY_SOURCE_ORGANIZATION_ID AND
1582 AND PPA.PAY_PERIOD_END_DATE between paev.date_from and paev.date_to )
1583 REQUEST_ID = p_request_id; */
1584
1585 UPDATE PA_PAY_SOURCE_AMOUNTS PPS
1586 SET PAY_REJECTION_ID =
1587 ( SELECT MAX(PAY_REJECTION_ID)
1588 FROM PA_PAY_REJECTIONS_ALL PPR
1589 WHERE PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
1590 )
1591 WHERE REQUEST_ID = G_REQUEST_ID
1592 AND PAY_SOURCE_ID IN
1593 (SELECT PAY_SOURCE_ID
1594 FROM PA_PAY_REJECTIONS_ALL
1595 WHERE REQUEST_ID = G_REQUEST_ID
1596 ) ;
1597
1598 write_log(Log, 'Stamp Stage 1 rejection ids back on Pay Source - ' || SQL%rowcount);
1599
1600
1601
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 write_log(Log,SQLERRM);
1605 END stage_one_validate;
1606
1607 /*
1608 Procedure : PROCESS_NONE
1609 Type : Private
1610 Purpose : Pay element with distribution method of 'NONE'
1611 are processed here .
1612 Entry made in PA_PAY_DIST_LINES.
1613 */
1614
1615
1616 PROCEDURE PROCESS_NONE IS
1617 BEGIN
1618
1619 write_log(Log, 'Processing NONE ');
1620
1621 INSERT
1622 INTO PA_PAY_DIST_LINES
1623 ( PAY_DIST_ID,
1624 PAY_SOURCE_ID,
1625 INTERFACE_RUN_ID,
1626 PERSON_ID,
1627 ASSIGNMENT_ID,
1628 ORGANIZATION_ID,
1629 PROJECT_ID,
1630 TASK_ID,
1631 EXPENDITURE_TYPE,
1632 SYSTEM_LINKAGE_FUNCTION,
1633 EXPENDITURE_ITEM_DATE,
1634 PAY_ELEMENT_TYPE_ID,
1635 PAY_ELEMENT_TYPE_CODE,
1636 QUANTITY,
1637 DENOM_CURRENCY_CODE,
1638 DENOM_RAW_COST,
1639 DENOM_BURDEN_COST,
1640 ORG_ID,
1641 LAST_UPDATE_DATE,
1642 LAST_UPDATED_BY,
1643 CREATION_DATE,
1644 CREATED_BY
1645
1646 )
1647 SELECT
1648 pa_pay_dist_id_s.NEXTVAL ,
1649 PAY_SOURCE_ID,
1650 INTERFACE_RUN_ID,
1651 PERSON_ID,
1652 ASSIGNMENT_ID,
1653 EMP_ORGANIZATION_ID,
1654 PAY_SOURCE_PROJECT_ID,
1655 PAY_SOURCE_TASK_ID,
1656 EXPENDITURE_TYPE,
1657 DECODE(COST_TYPE_CODE,'RAW','PJ','BURDEN','BTC',NULL) ,
1658 G_PAY_PERIOD_END,
1659 PAY_ELEMENT_TYPE_ID,
1660 PAY_ELEMENT_TYPE_CODE,
1661 0,--Quantity ,
1662 PAY_CURRENCY_CODE,
1663 DECODE(COST_TYPE_CODE,'RAW',PAY_AMOUNT,0),
1664 DECODE(COST_TYPE_CODE,'BURDEN',PAY_AMOUNT,0),
1665 ORG_ID,
1666 trunc(sysdate),
1667 fnd_global.user_id,
1668 trunc(sysdate),
1669 fnd_global.user_id
1670 from PA_PAY_SOURCE_AMOUNTS
1671 WHERE PAY_REJECTION_ID IS NULL
1672 AND DISTRIBUTION_BASIS_CODE = 'NONE'
1673 AND PAY_SOURCE_PROJECT_ID >0
1674 AND PAY_SOURCE_TASK_ID > 0
1675 AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID ;
1676 /* AND PERSON_ID = G_PERSON_ID */
1677 write_log(Log, 'Processing Distribution Basis - NONE . Distribution Lines created - ' || SQL%rowcount);
1678
1679 EXCEPTION
1680 WHEN OTHERS THEN
1681 write_log(Log, 'PROCESS_NONE' || SQLERRM);
1682 RAISE;
1683
1684 END PROCESS_NONE;
1685
1686 /*
1687 Procedure : DERIVE_PRIORITY
1688 Type : Private
1689 Purpose : Derives priority for the processng of pay elements
1690 Priority is derived and stamped on the source record.
1691 */
1692
1693 PROCEDURE DERIVE_PRIORITY IS
1694 BEGIN
1695
1696 write_log(Log, 'Deriving priorities for request Id' || G_REQUEST_ID);
1697
1698 /* Bug 12804239
1699 UPDATE PA_PAY_SOURCE_AMOUNTS
1700 SET PRIORITY =
1701 ( Decode(PAY_CURRENCY_CODE,G_FUNC_CURR_CODE,10000,0)+
1702 (Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*1000) +
1703 (Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*100) +
1704 (Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 10) +
1705 (Decode(TIMECARD_EXP_TYPE,NULL , 0,1)*1))
1706 WHERE
1707 Upper(TIMECARD_ELEMENT) = 'YES'
1708 AND REQUEST_ID = G_REQUEST_ID;
1709 */
1710
1711 UPDATE PA_PAY_SOURCE_AMOUNTS
1712 SET PRIORITY =
1713 (
1714
1715 (Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*10000) +
1716 (Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*1000) +
1717 (Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 100) +
1718 (Decode(TIMECARD_EXP_TYPE,NULL , 0,1)*10) +
1719 Decode(PAY_CURRENCY_CODE,G_FUNC_CURR_CODE,1,0)
1720 )
1721 WHERE
1722 distribution_basis_code IN ('STHOURS','OTHOURS','TOTAL_HOURS') AND /*
1723 Added for bug#12975007 */
1724 Upper(TIMECARD_ELEMENT) = 'YES'
1725 AND REQUEST_ID = G_REQUEST_ID;
1726
1727 /* Added for bug#12975007 */
1728 UPDATE PA_PAY_SOURCE_AMOUNTS
1729 SET PRIORITY =
1730 (
1731
1732 (Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*20000) +
1733 (Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*2000) +
1734 (Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 200)
1735 )
1736 WHERE
1737 distribution_basis_code IN ('STHOURS','OTHOURS','TOTAL_HOURS') AND
1738 Upper(Nvl(TIMECARD_ELEMENT,'NO')) = 'NO'
1739 AND REQUEST_ID = G_REQUEST_ID;
1740
1741
1742
1743 UPDATE PA_PAY_SOURCE_AMOUNTS
1744 SET PRIORITY =
1745 (
1746
1747 (Decode(PAY_SOURCE_PROJECT_ID,NULL , 0,1)*30000) +
1748 (Decode(PAY_SOURCE_TASK_ID,NULL , 0,1)*3000) +
1749 (Decode(PAY_SOURCE_ORGANIZATION_ID,NULL , 0,1)* 300)
1750 )
1751 WHERE
1752 distribution_basis_code IN ('ST_AMT','OT_AMT','ST_OT_AMT')
1753 AND REQUEST_ID = G_REQUEST_ID;
1754
1755
1756 /* Added for bug#12975007 */
1757 write_log(Log, 'Priority derived for all source amounts');
1758
1759 EXCEPTION
1760 WHEN OTHERS THEN
1761 write_log(Log, 'DERIVE_PRIORITY' || SQLERRM);
1762
1763 END DERIVE_PRIORITY;
1764
1765
1766 /*
1767 Procedure : GET_PERSON_TC
1768 Type : Private
1769 Purpose : Loads the eligible timecards of the Employee for the Pay period
1770 into a GTT PA_PAY_EXP_TEMP. This is done to reduce the hits on PA_EXPENDITURE_ITEMS
1771 in further processing.
1772 During distribution of pay elements , we would refer to only PA_PAY_EXP_TEMP
1773
1774 */
1775
1776 PROCEDURE get_person_TC
1777 (
1778
1779 P_START_DATE DATE ,
1780 P_END_DATE DATE
1781 )
1782 IS
1783 BEGIN
1784
1785 write_log(Log, 'Loading EI ' || ' between ' || P_START_DATE || ' and ' || P_END_DATE);
1786
1787
1788 DELETE
1789 FROM PA_PAY_EXP_TEMP ;
1790
1791
1792 INSERT
1793 INTO PA_PAY_EXP_TEMP
1794 (
1795 INCURRED_BY_PERSON_ID,
1796 EXPENDITURE_ITEM_ID ,
1797 EXPENDITURE_ITEM_DATE,
1798 EXPENDITURE_TYPE ,
1799 ORGANIZATION_ID ,
1800 OVERRIDE_TO_ORGANIZATION_ID ,
1801 ORG_ID ,
1802 PROJECT_ID ,
1803 TASK_ID ,
1804 SYSTEM_LINKAGE_FUNCTION ,
1805 COST_DISTRIBUTED_FLAG,
1806 ACCT_RAW_COST,
1807 QUANTITY ,
1808 INTERFACE_RUN_ID,
1809 PAYROLL_ACCRUAL_FLAG,
1810 TRANSFERRED_FROM_EXP_ITEM_ID)
1811 (SELECT EI.INCURRED_BY_PERSON_ID,
1812 EII.EXPENDITURE_ITEM_ID ,
1813 EII.EXPENDITURE_ITEM_DATE,
1814 EII.EXPENDITURE_TYPE ,
1815 EI.INCURRED_BY_ORGANIZATION_ID ,
1816 EII.OVERRIDE_TO_ORGANIZATION_ID ,
1817 EII.ORG_ID ,
1818 EII.PROJECT_ID ,
1819 EII.TASK_ID ,
1820 EII.SYSTEM_LINKAGE_FUNCTION ,
1821 EII.COST_DISTRIBUTED_FLAG,
1822 EII.ACCT_RAW_COST,
1823 EII.QUANTITY,
1824 EII.INTERFACE_RUN_ID,
1825 EII.PAYROLL_ACCRUAL_FLAG,
1826 EII.TRANSFERRED_FROM_EXP_ITEM_ID
1827 FROM PA_EXPENDITURE_ITEMS EII,
1828 PA_EXPENDITURES EI
1829 WHERE EI.INCURRED_BY_PERSON_ID IN (SELECT DISTINCT PERSON_ID FROM PA_PAY_SOURCE_AMOUNTS
1830 WHERE
1831 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID)
1832 AND EI.EXPENDITURE_ID = EII.EXPENDITURE_ID
1833 AND EII.expenditure_item_date BETWEEN P_START_DATE AND P_END_DATE
1834 AND net_zero_adjustment_flag <> 'Y'
1835 AND (( costing_method = 'ACTUAL' AND
1836 payroll_accrual_flag = 'N' AND
1837 COST_DISTRIBUTED_FLAG = 'Y' AND INTERFACE_RUN_ID IS NOT NULL
1838 AND system_linkage_function IN ('ST','OT','PJ','BTC')
1839 )
1840 OR
1841 ( costing_method = 'ACTUAL' AND
1842 payroll_accrual_flag = 'Y' AND
1843 --COST_DISTRIBUTED_FLAG = 'Y' AND /*Accrual line Marked for re-calc also should get picked up*/
1844 INTERFACE_RUN_ID IS NULL
1845 AND system_linkage_function IN ('ST','OT')
1846 )
1847 OR
1848 (COST_DISTRIBUTED_FLAG = 'N' AND
1849 get_cost_method(eii.expenditure_item_id,eii.expenditure_item_date) = 'ACTUAL' AND
1850 Nvl(COSTING_METHOD,'ACTUAL') <> 'STANDARD'AND
1851 system_linkage_function IN ('ST','OT'))
1852 )
1853
1854 ) ;
1855
1856 write_log(Log,'Eis loaded -' || To_char(SQL%RowCount) );
1857
1858 /* Case of TC adjustment after the actuals was interfaced on that. Amount should get carried over*/
1859 --Re-writing the LOOP below .
1860 -- FOR X IN
1861 -- (
1862 -- SELECT temp.expenditure_item_id,
1863 -- ei.acct_currency_code,
1864 -- ei.acct_raw_cost
1865 -- FROM PA_PAY_EXP_TEMP temp ,
1866 -- pa_expenditure_items_all ei
1867 -- WHERE (
1868 -- ( /* uncosted ei picked up for stamping cost on that*/
1869 -- temp.payroll_accrual_flag IS NULL
1870 -- AND NVL(temp.cost_distributed_flag,'N') = 'N'
1871 -- )
1872 -- OR
1873 -- ( /* accrual ei picked up for reversing*/
1874 -- temp.payroll_accrual_flag = 'Y'
1875 -- )
1876 -- )
1877 -- AND temp.transferred_from_exp_item_id IS NOT NULL /* If coming via an adjustment */
1878 -- AND NOT EXISTS
1879 -- (
1880 -- /* and that adjustment is not PAXREVTXN reversal process , then it should be a TC adjustment */
1881 -- SELECT expenditure_item_id
1882 -- FROM PA_EXPEND_ITEM_ADJ_ACTIVITIES adj
1883 -- WHERE adj.expenditure_item_id = temp.expenditure_item_id
1884 -- AND MODULE_CODE = 'PAXREVTXN'
1885 -- AND EXCEPTION_ACTIVITY_CODE NOT
1886 -- IN ('PA_SUM_PROJECT_CLOSED','PA_EMP_COSTSET_REJECT','PA_TR_APE_NO_ADJUST')
1887 -- )
1888 -- AND temp.transferred_from_exp_item_id= ei.expenditure_item_id
1889
1890 -- )
1891
1892 -- LOOP
1893
1894 -- UPDATE PA_PAY_EXP_TEMP t
1895 -- SET ORIG_CURR_CODE = x.acct_currency_code
1896 -- , ORIG_AMOUNT = x.acct_raw_cost
1897 -- WHERE t.expenditure_item_id = x.expenditure_item_id;
1898
1899 -- END LOOP;
1900
1901 /* bug 12690224 Above loop re-written below*/
1902 /*
1903 FOR X IN
1904 (
1905 SELECT temp.expenditure_item_id,
1906 ei1.acct_currency_code,
1907 ei1.acct_raw_cost
1908 FROM PA_PAY_EXP_TEMP temp ,
1909 pa_expenditure_items_all ei1,
1910 pa_expenditure_items_all ei2,
1911 pa_expenditure_items_all ei3
1912 WHERE
1913 temp.expenditure_item_id= ei3.expenditure_item_id
1914 AND ei3.transaction_source = 'ORACLE TIME AND LABOR'
1915 AND ei2.orig_transaction_reference = ei3.orig_transaction_reference
1916 AND ei2.system_linkage_function IN ('ST','OT')
1917 AND ei2.transaction_source = 'ORACLE TIME AND LABOR'
1918 AND ei2.COSTING_METHOD = 'ACTUAL'
1919 AND ei2.net_zero_adjustment_flag = 'Y'
1920 AND ei2.adjusted_expenditure_item_id = ei1.expenditure_item_id
1921 AND ei1.transaction_source = 'ORACLE TIME AND LABOR'
1922 AND ei1.COSTING_METHOD = 'ACTUAL'
1923 AND ei1.system_linkage_function IN ('ST','OT')
1924 AND ei1.net_zero_adjustment_flag = 'Y'
1925 )
1926
1927 LOOP
1928
1929 UPDATE PA_PAY_EXP_TEMP t
1930 SET ORIG_CURR_CODE = x.acct_currency_code
1931 , ORIG_AMOUNT = x.acct_raw_cost
1932 WHERE t.expenditure_item_id = x.expenditure_item_id;
1933
1934 END LOOP;
1935
1936 */
1937 /* Bug 12779047 Above logic re-written below */
1938 UPDATE PA_PAY_EXP_TEMP T
1939 SET
1940 (
1941 ORIG_CURR_CODE ,
1942 ORIG_AMOUNT
1943 )
1944 =
1945 (SELECT EI3.ACCT_CURRENCY_CODE,
1946 EI3.ACCT_RAW_COST
1947 FROM PA_EXPENDITURE_ITEMS EI3
1948 WHERE EXPENDITURE_ITEM_ID =
1949 (SELECT MAX(EI5.EXPENDITURE_ITEM_ID)
1950 FROM PA_EXPENDITURE_ITEMS EI4 ,
1951 PA_EXPENDITURE_ITEMS EI5 ,
1952 PA_PAY_AUDIT AUD
1953 WHERE EI4.EXPENDITURE_ITEM_ID = T.EXPENDITURE_ITEM_ID
1954 AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1955 AND EI5.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1956 AND EI5.INTERFACE_RUN_ID IS NOT NULL
1957 AND EI5.INTERFACE_RUN_ID = AUD.INTERFACE_RUN_ID
1958 AND AUD.BATCH_ID <> (SELECT BATCH_ID FROM PA_PAY_AUDIT WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID)
1959 AND SUBSTR(EI5.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI5.ORIG_TRANSACTION_REFERENCE,':') - 1) = SUBSTR(EI4.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI4.ORIG_TRANSACTION_REFERENCE,':') - 1)
1960 AND EI5.COST_DISTRIBUTED_FLAG = 'Y'
1961 AND EI5.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
1962 )
1963 )
1964 WHERE EXPENDITURE_ITEM_ID IN
1965 (SELECT T.EXPENDITURE_ITEM_ID
1966 FROM PA_EXPENDITURE_ITEMS EI1 ,
1967 PA_PAY_EXP_TEMP T
1968 WHERE T.EXPENDITURE_ITEM_ID = EI1.EXPENDITURE_ITEM_ID
1969 AND EI1.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1970 AND EXISTS
1971 (SELECT 1
1972 FROM PA_EXPENDITURE_ITEMS EI2
1973 ,PA_PAY_AUDIT AUD2
1974 WHERE EI2.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1975 AND EI2.INTERFACE_RUN_ID IS NOT NULL
1976 AND EI2.INTERFACE_RUN_ID = AUD2.INTERFACE_RUN_ID
1977 AND AUD2.BATCH_ID <> (SELECT BATCH_ID FROM PA_PAY_AUDIT WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID)
1978 AND SUBSTR(EI2.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1) = SUBSTR(EI1.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI1.ORIG_TRANSACTION_REFERENCE,':') - 1)
1979 AND COST_DISTRIBUTED_FLAG = 'Y'
1980 AND ADJUSTED_EXPENDITURE_ITEM_ID IS NULL )
1981 ) ;
1982
1983
1984 END get_person_TC;
1985
1986 /*
1987 Procedure : PROCESS_TC_ELEMENTS
1988 Type : Private
1989 Purpose :
1990 Process the pay elements with distribution method of ST HOURS / OT HOURS / ALL HOURS .
1991 If no timecards found , and allow_misc is no , then processing of that employee assignment is rejected .
1992 If allow_misc is yes , Misc item will be created.
1993 Elements are processed in the order of priority.
1994 Rules/Assumptions
1995 Pay Element amounts of different priorities cannot contribute to the same EI .
1996 Pay Element amounts of different currencies cannot contribute to the same EI .
1997 Pay Element with Functional Currency as Pay currency will have a higher priority in Priority Matrix itself.
1998 All other currencies have equal priority and processed random.
1999
2000
2001 */
2002
2003 PROCEDURE process_tc_elements
2004 IS
2005 BEGIN
2006
2007 write_log(LOG , 'Processing Hour Based TC-Yes');
2008
2009
2010 FOR Z IN
2011 (
2012 SELECT DISTINCT
2013 PRIORITY ,
2014 PAY_CURRENCY_CODE
2015 FROM
2016 PA_PAY_SOURCE_AMOUNTS
2017 WHERE
2018 DISTRIBUTION_BASIS_CODE IN ('STHOURS', 'OTHOURS', 'TOTAL_HOURS')
2019 AND
2020 TIMECARD_ELEMENT = 'YES' AND
2021 /* PERSON_ID = P_PERSON_ID AND */
2022 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2023 ORDER BY
2024 PRIORITY DESC
2025 )
2026 LOOP
2027 INSERT
2028 INTO
2029 PA_PAY_DIST_LINES
2030 (
2031 PAY_DIST_ID ,
2032 PAY_SOURCE_ID ,
2033 INTERFACE_RUN_ID ,
2034 PERSON_ID ,
2035 ORGANIZATION_ID ,
2036 PROJECT_ID ,
2037 TASK_ID ,
2038 EXPENDITURE_TYPE ,
2039 SYSTEM_LINKAGE_FUNCTION ,
2040 EXPENDITURE_ITEM_DATE ,
2041 PAY_ELEMENT_TYPE_ID ,
2042 PAY_ELEMENT_TYPE_CODE ,
2043 DENOM_CURRENCY_CODE ,
2044 QUANTITY ,
2045 DENOM_RAW_COST ,
2046 SOURCE_EXPENDITURE_ITEM_ID,
2047 ORG_ID ,
2048 LAST_UPDATE_DATE ,
2049 LAST_UPDATED_BY ,
2050 CREATION_DATE ,
2051 CREATED_BY ,
2052 LAST_UPDATE_LOGIN ,
2053 REQUEST_ID
2054 )
2055 SELECT
2056 PA_PAY_DIST_ID_S.NEXTVAL,
2057 PSA.PAY_SOURCE_ID ,
2058 G_INTERFACE_RUN_ID ,
2059 PSA.PERSON_ID ,
2060 NVL(EII.OVERRIDE_TO_ORGANIZATION_ID , EII.ORGANIZATION_ID),
2061 EII.PROJECT_ID ,
2062 EII.TASK_ID ,
2063 EII.EXPENDITURE_TYPE ,
2064 EII.SYSTEM_LINKAGE_FUNCTION,
2065 EII.EXPENDITURE_ITEM_DATE ,
2066 PSA.PAY_ELEMENT_TYPE_ID ,
2067 PSA.PAY_ELEMENT_TYPE_CODE ,
2068 PSA.PAY_CURRENCY_CODE ,
2069 EII.QUANTITY ,
2070 RATIO_TO_REPORT(EII.QUANTITY) over (PARTITION BY PAY_SOURCE_ID) * PSA.PAY_AMOUNT AS AMOUNT ,
2071 EII.EXPENDITURE_ITEM_ID ,
2072 EII.ORG_ID ,
2073 TRUNC(sysdate) ,
2074 fnd_global.user_id ,
2075 TRUNC(sysdate) ,
2076 fnd_global.user_id ,
2077 G_LOGIN_ID ,
2078 G_REQUEST_ID
2079 FROM
2080 PA_PAY_EXP_TEMP EII , -- change to PA_PAY_EXP_TEMP danger
2081 PA_PAY_SOURCE_AMOUNTS PSA
2082 WHERE
2083 PSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
2084 PSA.PRIORITY = Z.PRIORITY AND
2085 PSA.PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE AND
2086 --PSA.PERSON_ID = p_person_id AND
2087 PSA.PERSON_ID = EII.INCURRED_BY_PERSON_ID AND
2088 ((PSA.PAY_AMOUNT >0 AND G_RETRO ='N') OR (G_RETRO ='Y')) AND /*Added
2089 for bug#13595751*/
2090 -- PSA.PAY_SOURCE_ID = p_pay_source_id AND
2091 EII.expenditure_type = NVL(PSA.TIMECARD_EXP_TYPE, EII.expenditure_type) AND
2092 /*pick the same pay source record */
2093 PSA.PERSON_ID = EII.INCURRED_BY_PERSON_ID AND
2094 EII.PROJECT_ID = NVL( PSA.PAY_SOURCE_PROJECT_ID,EII.PROJECT_ID )AND
2095 EII.TASK_ID = NVL(PSA.PAY_SOURCE_TASK_ID ,EII.TASK_ID) AND
2096 NVL(EII.OVERRIDE_TO_ORGANIZATION_ID , EII.ORGANIZATION_ID) =
2097 COALESCE(PSA.PAY_SOURCE_ORGANIZATION_ID,
2098 EII.OVERRIDE_TO_ORGANIZATION_ID , EII.ORGANIZATION_ID) AND
2099 EII.QUANTITY <> 0 AND
2100 EII.SYSTEM_LINKAGE_FUNCTION IN
2101 (
2102 SELECT
2103 DECODE(psa.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST',
2104 'OTHOURS','OT','TOTAL_HOURS','ST')
2105 FROM
2106 dual
2107 UNION
2108 SELECT
2109 DECODE(psa.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST',
2110 'OTHOURS','OT','TOTAL_HOURS','OT')
2111 FROM
2112 dual
2113 )
2114 AND NVL(EII.PROCESSED_FLAG,'N') <> 'Y' -- not processed in current run
2115 AND EII.INTERFACE_RUN_ID IS NULL; -- not an EI which has actual cost
2116
2117 write_log(Log , 'Record processed ' || SQL%RowCount );
2118 write_log(LOG , 'Processed Priority ' || Z.Priority || ' For ' || Z.pay_currency_code);
2119
2120
2121
2122
2123 /*Reject those elements that did not create any distributions*/
2124 write_log(LOG , 'Rejecting TC-Yes elements which did not find TC to distribute- Misc not allowed ');
2125
2126
2127
2128 INSERT
2129 INTO
2130 PA_PAY_REJECTIONS_ALL
2131 (
2132 PAY_REJECTION_ID ,
2133 INTERFACE_RUN_ID ,
2134 PERSON_ID ,
2135 ASSIGNMENT_ID ,
2136 PAY_SOURCE_ID ,
2137 PAY_ELEMENT_TYPE_ID,
2138 PAY_ELEMENT_TYPE_CODE,
2139 REJECTION_CODE ,
2140 LAST_UPDATE_DATE ,
2141 LAST_UPDATED_BY ,
2142 CREATION_DATE ,
2143 CREATED_BY
2144 )
2145 SELECT
2146 PA_PAY_REJECTION_ID_S.nextval,
2147 G_INTERFACE_RUN_ID ,
2148 PS.PERSON_ID ,
2149 PS.ASSIGNMENT_ID ,
2150 PS.PAY_SOURCE_ID ,
2151 PS.PAY_ELEMENT_TYPE_ID ,
2152 PS.PAY_ELEMENT_TYPE_CODE ,
2153 'RC 25' ,
2154 G_RUN_DATE ,
2155 G_USER_ID ,
2156 G_RUN_DATE ,
2157 G_USER_ID
2158 FROM
2159 PA_PAY_SOURCE_AMOUNTS PS
2160 Where
2161 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
2162 PRIORITY = Z.PRIORITY AND
2163 PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE AND
2164 Nvl(PS.ALLOW_MISC_FLAG,'NO') = 'NO' AND
2165 NOT EXISTS
2166 (
2167 SELECT 1 FROM PA_PAY_DIST_LINES PPD
2168 WHERE
2169 PPD.PAY_SOURCE_ID = PS.PAY_SOURCE_ID
2170 )
2171 ;
2172
2173 write_log(Log, ' Rejected Record count ' || SQL%RowCount );
2174
2175 /* If Misc enabled , create Misc transactions for those Pay source record for which no EI was found */
2176 write_log(Log, ' Processing TC - Yes - No TC Found - Misc enabled' );
2177
2178 INSERT
2179 INTO
2180 PA_PAY_DIST_LINES
2181 (
2182 PAY_DIST_ID ,
2183 PAY_SOURCE_ID ,
2184 INTERFACE_RUN_ID ,
2185 PERSON_ID ,
2186 ASSIGNMENT_ID ,
2187 ORGANIZATION_ID ,
2188 PROJECT_ID ,
2189 TASK_ID ,
2190 EXPENDITURE_TYPE ,
2191 SYSTEM_LINKAGE_FUNCTION,
2192 EXPENDITURE_ITEM_DATE ,
2193 PAY_ELEMENT_TYPE_ID ,
2194 PAY_ELEMENT_TYPE_CODE ,
2195 QUANTITY ,
2196 DENOM_CURRENCY_CODE ,
2197 DENOM_RAW_COST ,
2198 DENOM_BURDEN_COST ,
2199 ORG_ID ,
2200 LAST_UPDATE_DATE ,
2201 LAST_UPDATED_BY ,
2202 CREATION_DATE ,
2203 CREATED_BY ,
2204 LAST_UPDATE_LOGIN ,
2205 REQUEST_ID
2206 )
2207 SELECT
2208 PA_PAY_DIST_ID_S.NEXTVAL ,
2209 HOURS_TOTAL.PAY_SOURCE_ID ,
2210 G_INTERFACE_RUN_ID ,
2211 HOURS_TOTAL.PERSON_ID ,
2212 HOURS_TOTAL.ASSIGNMENT_ID ,
2213 HOURS_TOTAL.ORGANIZATION_ID ,
2214 HOURS_TOTAL.PROJECT_ID ,
2215 HOURS_TOTAL.TASK_ID ,
2216 HOURS_TOTAL.EXPENDITURE_TYPE ,
2217 'PJ' ,
2218 G_PAY_PERIOD_END ,
2219 HOURS_TOTAL.PAY_ELEMENT_TYPE_ID ,
2220 HOURS_TOTAL.PAY_ELEMENT_TYPE_CODE ,
2221 0 ,
2222 HOURS_TOTAL.PAY_CURRENCY_CODE ,
2223 HOURS_TOTAL.PAY_AMOUNT * RATIO_TO_REPORT(HOURS_TOTAL.HOURS) OVER (PARTITION BY HOURS_TOTAL.PAY_SOURCE_ID),
2224 0 ,
2225 G_ORG_ID ,
2226 G_RUN_DATE ,
2227 G_USER_ID ,
2228 G_RUN_DATE ,
2229 G_USER_ID ,
2230 G_LOGIN_ID ,
2231 G_REQUEST_ID
2232 FROM
2233 (
2234 SELECT
2235 PAY_SOURCE_ID ,
2236 PERSON_ID ,
2237 ASSIGNMENT_ID ,
2238 NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ORGANIZATION_ID ,
2239 PROJECT_ID ,
2240 TASK_ID ,
2241 PPSA.EXPENDITURE_TYPE,
2242 PAY_ELEMENT_TYPE_ID,
2243 PAY_ELEMENT_TYPE_CODE ,
2244 PAY_CURRENCY_CODE ,
2245 PAY_AMOUNT,
2246 SUM(QUANTITY) AS HOURS
2247 FROM
2248 PA_PAY_EXP_TEMP EI,
2249 PA_PAY_SOURCE_AMOUNTS PPSA
2250 WHERE
2251 PPSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
2252 PPSA.PRIORITY = Z.PRIORITY AND
2253 PPSA.PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE AND
2254 PPSA.DISTRIBUTION_BASIS_CODE IN ('STHOURS', 'OTHOURS','TOTAL_HOURS') AND
2255 PPSA.TIMECARD_ELEMENT = 'YES' AND
2256 PPSA.PAY_SOURCE_ID NOT IN
2257 ( SELECT
2258 PAY_SOURCE_ID
2259 FROM
2260 PA_PAY_DIST_LINES PPD
2261 WHERE
2262 PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2263 )AND
2264 NVL(PPSA.ALLOW_MISC_FLAG,'NO') = 'YES' AND
2265 PPSA.PERSON_ID = EI.INCURRED_BY_PERSON_ID AND
2266 COALESCE(EI.ORGANIZATION_ID ,EI.OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(PPSA.PAY_SOURCE_ORGANIZATION_ID, EI.ORGANIZATION_ID ,EI.OVERRIDE_TO_ORGANIZATION_ID,-99) AND
2267 EI.PROJECT_ID = NVL(PPSA.PAY_SOURCE_PROJECT_ID,PROJECT_ID) AND
2268 EI.TASK_ID = NVL(PPSA.PAY_SOURCE_TASK_ID,TASK_ID)
2269 /*AND COST_DISTRIBUTED_FLAG <> 'Y'
2270 -- No unprocessed timecard found .. shd go on already
2271 -- processed in prior runs*/
2272 AND
2273 EI.SYSTEM_LINKAGE_FUNCTION IN
2274 (
2275 SELECT
2276 DECODE(PPSA.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST', 'OTHOURS','OT','ALLHOURS','ST')
2277 FROM
2278 DUAL
2279 UNION
2280 SELECT
2281 DECODE(PPSA.DISTRIBUTION_BASIS_CODE,'STHOURS' ,'ST', 'OTHOURS','OT','ALLHOURS','OT')
2282 FROM
2283 DUAL
2284 )
2285 AND
2286 EI.EXPENDITURE_TYPE NOT IN
2287 (
2288 SELECT
2289 EXPENDITURE_TYPE
2290 FROM
2291 PA_PAY_EXCLUDE_EXP_TYPES
2292 WHERE
2293 PAY_ELEMENT_MAP_ID = PPSA.PAY_ELEMENT_MAP_ID
2294 )
2295 GROUP BY
2296 PAY_SOURCE_ID ,
2297 PERSON_ID ,
2298 ASSIGNMENT_ID ,
2299 NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ,
2300 PROJECT_ID ,
2301 TASK_ID ,
2302 PPSA.EXPENDITURE_TYPE,
2303 PAY_ELEMENT_TYPE_ID,
2304 PAY_ELEMENT_TYPE_CODE ,
2305 PAY_CURRENCY_CODE ,
2306 PAY_AMOUNT
2307 )
2308 HOURS_TOTAL ;
2309
2310 write_log(Log, 'Record count ' || SQL%RowCount );
2311
2312
2313 write_log(Log, ' Processing Rejections on TC - Yes - No TC Found - Misc enabled' );
2314
2315 INSERT
2316 INTO PA_PAY_REJECTIONS_ALL
2317 (
2318 PAY_REJECTION_ID ,
2319 INTERFACE_RUN_ID ,
2320 PERSON_ID ,
2321 ASSIGNMENT_ID ,
2322 PAY_SOURCE_ID ,
2323 PAY_ELEMENT_TYPE_ID ,
2324 PAY_ELEMENT_TYPE_CODE,
2325 REJECTION_CODE ,
2326 LAST_UPDATE_DATE ,
2327 LAST_UPDATED_BY ,
2328 CREATION_DATE ,
2329 CREATED_BY
2330 )
2331 SELECT PA_PAY_REJECTION_ID_S.NEXTVAL,
2332 G_INTERFACE_RUN_ID ,
2333 PS.PERSON_ID ,
2334 PS.ASSIGNMENT_ID ,
2335 PS.PAY_SOURCE_ID ,
2336 PS.PAY_ELEMENT_TYPE_ID ,
2337 PS.PAY_ELEMENT_TYPE_CODE ,
2338 'RC 30' ,
2339 G_RUN_DATE ,
2340 G_USER_ID ,
2341 G_RUN_DATE ,
2342 G_USER_ID
2343 FROM PA_PAY_SOURCE_AMOUNTS PS
2344 WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2345 AND PRIORITY = Z.PRIORITY
2346 AND PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE
2347 AND NVL(PS.ALLOW_MISC_FLAG,'NO') = 'YES'
2348 AND PS.PAY_SOURCE_ID NOT IN
2349 ( SELECT PAY_SOURCE_ID
2350 FROM PA_PAY_DIST_LINES PPD
2351 WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2352 ) ;
2353
2354 write_log(Log, ' Rejected Record count for Enable Misc processing ' || SQL%RowCount );
2355
2356 /* Mark the EI as processed so that pay elements of a different priority / currency will not contribute to the same EI */
2357
2358 UPDATE PA_PAY_EXP_TEMP
2359 SET PROCESSED_FLAG = 'Y'
2360 WHERE EXPENDITURE_ITEM_ID IN
2361 (SELECT DIST.SOURCE_EXPENDITURE_ITEM_ID
2362 FROM PA_PAY_DIST_LINES DIST,
2363 PA_PAY_SOURCE_AMOUNTS SOURCE
2364 WHERE SOURCE.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2365 AND SOURCE.PRIORITY = Z.PRIORITY
2366 AND SOURCE.PAY_CURRENCY_CODE = Z.PAY_CURRENCY_CODE
2367 AND DIST.PAY_SOURCE_ID = SOURCE.PAY_SOURCE_ID
2368 ) ;
2369
2370
2371
2372 write_log(Log,'EI marked done count ' || SQL%RowCount );
2373 write_log(LOG , '==============================');
2374
2375 END LOOP;
2376
2377
2378 EXCEPTION
2379 WHEN OTHERS THEN
2380 write_log(Log, 'ERROR in PROCESS_TC_ELEMENTS' || SQLERRM);
2381 RAISE ;
2382 END process_tc_elements;
2383
2384
2385
2386 /*
2387 Procedure : PROCESS_TC_MISC
2388 Type : Private
2389 Purpose :
2390 Process the pay elements with distribution method of ST HOURS / OT HOURS / ALL HOURS .
2391 when Allow_misc option is Yes and no unprocessed timecard found for the pay period.
2392 */
2393
2394
2395 PROCEDURE process_tc_misc
2396 (
2397 p_pay_source_id pa_pay_source_amounts.pay_source_id%TYPE
2398 )
2399 IS
2400 l_row_count PLS_INTEGER := 0;
2401 ALLOW_MISC_TC_NOT_FOUND EXCEPTION;
2402 BEGIN
2403
2404 write_log(Log, 'Processing allow Misc for time card not found');
2405 write_log(Log, 'p_pay_source_id' || p_pay_source_id);
2406
2407
2408
2409 FOR X IN ( SELECT * FROM pa_pay_source_amounts WHERE pay_source_id =p_pay_source_id )
2410 Loop
2411 INSERT
2412 INTO PA_PAY_DIST_LINES
2413 ( PAY_DIST_ID,
2414 PAY_SOURCE_ID,
2415 INTERFACE_RUN_ID ,
2416 PERSON_ID ,
2417 ASSIGNMENT_ID ,
2418 ORGANIZATION_ID ,
2419 PROJECT_ID ,
2420 TASK_ID ,
2421 EXPENDITURE_TYPE ,
2422 SYSTEM_LINKAGE_FUNCTION,
2423 EXPENDITURE_ITEM_DATE ,
2424 PAY_ELEMENT_TYPE_ID ,
2425 PAY_ELEMENT_TYPE_CODE ,
2426 QUANTITY ,
2427 denom_CURRENCY_CODE,
2428 denom_RAW_COST ,
2429 denom_BURDEN_COST ,
2430 ORG_ID ,
2431 LAST_UPDATE_DATE ,
2432 LAST_UPDATED_BY ,
2433 CREATION_DATE ,
2434 CREATED_BY ,
2435 LAST_UPDATE_LOGIN,
2436 REQUEST_ID
2437 )
2438 SELECT pa_pay_dist_id_s.NEXTVAL ,
2439 X.PAY_SOURCE_ID,
2440 X.INTERFACE_RUN_ID ,
2441 X.PERSON_ID ,
2442 X.ASSIGNMENT_ID ,
2443 Hours_total.organization_id ,
2444 Hours_total.project_id ,
2445 Hours_total.task_id ,
2446 X.expenditure_type ,
2447 'PJ' ,
2448 G_PAY_PERIOD_END ,
2449 X.PAY_ELEMENT_TYPE_ID ,
2450 X.PAY_ELEMENT_TYPE_CODE ,
2451 0 ,
2452 X.PAY_CURRENCY_CODE,
2453 X.PAY_AMOUNT * Ratio_To_Report(Hours_total.hours) over (),
2454 0 ,
2455 G_ORG_ID ,
2456 G_RUN_DATE ,
2457 G_USER_ID ,
2458 G_RUN_DATE ,
2459 G_USER_ID ,
2460 G_LOGIN_ID,
2461 G_REQUEST_ID
2462 FROM ( SELECT NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id ,
2463 project_id ,
2464 task_id ,
2465 SUM(quantity) AS hours
2466 FROM PA_PAY_EXP_TEMP
2467 WHERE COALESCE(organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(X.PAY_SOURCE_ORGANIZATION_ID,organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99)
2468 AND project_id = Nvl(X.PAY_SOURCE_PROJECT_ID,project_id)
2469 AND task_id = Nvl(X.PAY_SOURCE_TASK_ID,task_id)
2470 --AND COST_DISTRIBUTED_FLAG <> 'Y'
2471 -- No unprocessed timecard found .. shd go on already processed in prior runs
2472 AND SYSTEM_LINKAGE_FUNCTION IN
2473 (SELECT DECODE(X.distribution_basis_code,'STHOURS' ,'ST','OTHOURS','OT','ALLHOURS','ST')
2474 FROM dual
2475
2476 UNION
2477
2478 SELECT DECODE(X.distribution_basis_code,'STHOURS' ,'ST','OTHOURS','OT','ALLHOURS','OT')
2479 FROM dual
2480 )
2481 AND expenditure_type NOT IN (SELECT expenditure_type FROM PA_PAY_EXCLUDE_EXP_TYPES
2482 WHERE pay_element_map_id = X.pay_element_map_id )
2483 GROUP BY NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) ,
2484 project_id ,
2485 task_id
2486 )
2487 Hours_total ;
2488
2489 l_row_count := SQL%ROWCOUNT;
2490 IF l_row_count > 0 THEN
2491 write_log(Log,'No of Misc item created = ' ||SQL%rowcount );
2492 ELSE
2493 write_log(Log,'Could not process allow miscellaneaous' );
2494 reject_pay_element(X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 30');
2495 RAISE ALLOW_MISC_TC_NOT_FOUND;
2496 END IF;
2497
2498 END LOOP;
2499
2500 EXCEPTION
2501 WHEN ALLOW_MISC_TC_NOT_FOUND THEN
2502 RAISE REJECT_EMP_PAY;
2503
2504 WHEN OTHERS THEN
2505 write_log(Log, 'Error process_tc_misc' || SQLERRM);
2506 RAISE REJECT_EMP_PAY;
2507 END process_tc_misc;
2508
2509 /*
2510 Procedure : PROCESS_NON_TC_ELEMENTS
2511 Type : Private
2512 Purpose :
2513 Process the pay elements with distribution method of ST HOURS / OT HOURS / ALL HOURS .
2514 when set up says that it should not go on a timecard .
2515 This procedure distributes the pay elements based on hours and finally creates a
2516 PJ/BTC record in PA_PAY_DIST_LINES
2517 */
2518
2519
2520 PROCEDURE process_non_tc_elements
2521 IS
2522
2523 BEGIN
2524
2525 write_log(LOG , 'Processing Hour based , TC Element -NO');
2526
2527
2528 INSERT INTO
2529 PA_PAY_DIST_LINES
2530 ( PAY_DIST_ID,
2531 PAY_SOURCE_ID,
2532 INTERFACE_RUN_ID ,
2533 PERSON_ID ,
2534 ASSIGNMENT_ID ,
2535 ORGANIZATION_ID ,
2536 PROJECT_ID ,
2537 TASK_ID ,
2538 EXPENDITURE_TYPE ,
2539 SYSTEM_LINKAGE_FUNCTION,
2540 EXPENDITURE_ITEM_DATE ,
2541 PAY_ELEMENT_TYPE_ID ,
2542 PAY_ELEMENT_TYPE_CODE,
2543 QUANTITY ,
2544 DENOM_CURRENCY_CODE,
2545 DENOM_RAW_COST ,
2546 DENOM_BURDEN_COST ,
2547 ORG_ID ,
2548 LAST_UPDATE_DATE ,
2549 LAST_UPDATED_BY ,
2550 CREATION_DATE ,
2551 CREATED_BY ,
2552 LAST_UPDATE_LOGIN,
2553 REQUEST_ID
2554 )
2555 SELECT
2556 PA_PAY_DIST_ID_S.NEXTVAL
2557 , SOURCE.PAY_SOURCE_ID
2558 , SOURCE.INTERFACE_RUN_ID
2559 , SOURCE.PERSON_ID
2560 , SOURCE.ASSIGNMENT_ID
2561 , Hours_total.organization_id
2562 , Hours_total.project_id
2563 , Hours_total.task_id
2564 , SOURCE.EXPENDITURE_TYPE
2565 , DECODE(SOURCE.COST_TYPE_CODE
2566 ,'RAW','PJ'
2567 ,'BURDEN','BTC')
2568 , G_PAY_PERIOD_END
2569 , SOURCE.PAY_ELEMENT_TYPE_ID
2570 , SOURCE.PAY_ELEMENT_TYPE_CODE
2571 , 0
2572 , SOURCE.PAY_CURRENCY_CODE
2573 , DECODE(SOURCE.COST_TYPE_CODE
2574 ,'RAW',SOURCE.PAY_AMOUNT * Ratio_To_Report(Hours_total.hours) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
2575 , DECODE(SOURCE.COST_TYPE_CODE
2576 ,'BURDEN',SOURCE.PAY_AMOUNT * Ratio_To_Report(Hours_total.hours) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
2577 , G_ORG_ID
2578 , G_RUN_DATE
2579 , G_USER_ID
2580 , G_RUN_DATE
2581 , G_USER_ID
2582 , G_LOGIN_ID
2583 , G_REQUEST_ID
2584 FROM ( SELECT Pay_source_id
2585 , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
2586 , project_id
2587 , task_id
2588 , SUM(NVL(quantity,0)) AS hours
2589 FROM PA_PAY_EXP_TEMP TEMP
2590 , PA_PAY_SOURCE_AMOUNTS S2
2591 WHERE S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2592 AND DISTRIBUTION_BASIS_CODE IN ('STHOURS'
2593 , 'OTHOURS'
2594 , 'TOTAL_HOURS')
2595 AND NVL(TIMECARD_ELEMENT,'NO') = 'NO'
2596 AND S2.PERSON_ID = TEMP.INCURRED_BY_PERSON_ID
2597 AND COALESCE(organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S2.PAY_SOURCE_ORGANIZATION_ID,organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99)
2598 AND project_id = NVL(S2.PAY_SOURCE_PROJECT_ID,project_id)
2599 AND task_id = NVL(S2.PAY_SOURCE_TASK_ID,task_id)
2600 AND
2601 (
2602 (
2603 S2.distribution_basis_code <> 'TOTAL_HOURS'
2604 AND TEMP.SYSTEM_LINKAGE_FUNCTION = DECODE(S2.distribution_basis_code
2605 ,'STHOURS' ,'ST'
2606 ,'OTHOURS','OT' )
2607 )
2608 OR
2609 (
2610 S2.distribution_basis_code = 'TOTAL_HOURS'
2611 AND TEMP.SYSTEM_LINKAGE_FUNCTION IN ('ST'
2612 , 'OT')
2613 )
2614 )
2615 AND TEMP.expenditure_type NOT IN (SELECT expenditure_type
2616 FROM PA_PAY_EXCLUDE_EXP_TYPES
2617 WHERE pay_element_map_id = S2.pay_element_map_id
2618 )
2619 GROUP BY Pay_source_id
2620 , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID)
2621 , project_id
2622 , task_id
2623 )
2624 Hours_total
2625 , PA_PAY_SOURCE_AMOUNTS SOURCE
2626 WHERE hours_total.pay_source_id = source.pay_source_id;
2627
2628
2629 INSERT
2630 INTO PA_PAY_REJECTIONS_ALL
2631 (
2632 PAY_REJECTION_ID
2633 , INTERFACE_RUN_ID
2634 , PERSON_ID
2635 , ASSIGNMENT_ID
2636 , PAY_SOURCE_ID
2637 , PAY_ELEMENT_TYPE_ID
2638 , PAY_ELEMENT_TYPE_CODE
2639 , REJECTION_CODE
2640 , LAST_UPDATE_DATE
2641 , LAST_UPDATED_BY
2642 , CREATION_DATE
2643 , CREATED_BY
2644 )
2645 SELECT PA_PAY_REJECTION_ID_S.NEXTVAL
2646 , G_INTERFACE_RUN_ID
2647 , PS.PERSON_ID
2648 , PS.ASSIGNMENT_ID
2649 , PS.PAY_SOURCE_ID
2650 , PS.PAY_ELEMENT_TYPE_ID
2651 , PS.PAY_ELEMENT_TYPE_CODE
2652 , 'RC 30'
2653 , G_RUN_DATE
2654 , G_USER_ID
2655 , G_RUN_DATE
2656 , G_USER_ID
2657 FROM PA_PAY_SOURCE_AMOUNTS PS
2658 WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2659 AND DISTRIBUTION_BASIS_CODE IN ('STHOURS'
2660 , 'OTHOURS'
2661 , 'TOTAL_HOURS')
2662 AND NVL(TIMECARD_ELEMENT,'NO') = 'NO'
2663 AND PS.PAY_SOURCE_ID NOT IN ( SELECT PAY_SOURCE_ID
2664 FROM PA_PAY_DIST_LINES PPD
2665 WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2666 ) ;
2667
2668
2669
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 write_log(Log, 'Errir in process_non_tc_elements' || SQLERRM);
2673 RAISE REJECT_EMP_PAY;
2674
2675 END process_non_tc_elements;
2676
2677
2678 /*
2679 Procedure : PROCESS_AMOUNT_BASED
2680 Type : Private
2681 Purpose :
2682 Process the pay elements with distribution method of ST AMOUNT / OT AMOUNT / ST-OT AMOUNT .
2683 This procedure distributes the pay element based on amounts .
2684 */
2685
2686 /*
2687 Procedure : PROCESS_AMOUNT_BASED
2688 Type : Private
2689 Purpose :
2690 Process the pay elements with distribution method of ST AMOUNT / OT AMOUNT / ST-OT AMOUNT .
2691 This procedure distributes the pay element based on amounts .
2692 */
2693
2694 PROCEDURE process_amount_based
2695 IS
2696
2697
2698 BEGIN
2699
2700 INSERT
2701 INTO PA_PAY_DIST_LINES
2702 (
2703 PAY_DIST_ID
2704 , PAY_SOURCE_ID
2705 , INTERFACE_RUN_ID
2706 , PERSON_ID
2707 , ASSIGNMENT_ID
2708 , ORGANIZATION_ID
2709 , PROJECT_ID
2710 , TASK_ID
2711 , EXPENDITURE_TYPE
2712 , SYSTEM_LINKAGE_FUNCTION
2713 , EXPENDITURE_ITEM_DATE
2714 , PAY_ELEMENT_TYPE_ID
2715 , PAY_ELEMENT_TYPE_CODE
2716 , QUANTITY
2717 , DENOM_CURRENCY_CODE
2718 , DENOM_RAW_COST
2719 , DENOM_BURDEN_COST
2720 , ORG_ID
2721 , LAST_UPDATE_DATE
2722 , LAST_UPDATED_BY
2723 , CREATION_DATE
2724 , CREATED_BY
2725 )
2726 SELECT pa_pay_dist_id_s.NEXTVAL
2727 , SOURCE.PAY_SOURCE_ID
2728 , G_INTERFACE_RUN_ID
2729 , SOURCE.PERSON_ID
2730 , SOURCE.ASSIGNMENT_ID
2731 , SUMM_AMOUNTS.organization_id
2732 , SUMM_AMOUNTS.project_id
2733 , SUMM_AMOUNTS.task_id
2734 , SOURCE.EXPENDITURE_TYPE
2735 , DECODE(SOURCE.COST_TYPE_CODE
2736 ,'RAW','PJ'
2737 ,'BURDEN','BTC')
2738 , G_PAY_PERIOD_END
2739 , SOURCE.PAY_ELEMENT_TYPE_ID
2740 , SOURCE.PAY_ELEMENT_TYPE_CODE
2741 , 0
2742 , SOURCE.PAY_CURRENCY_CODE
2743 , DECODE(SOURCE.COST_TYPE_CODE
2744 ,'RAW',SOURCE.PAY_AMOUNT * Ratio_To_Report(SUMM_AMOUNTS.amount) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
2745 , DECODE(SOURCE.COST_TYPE_CODE
2746 ,'BURDEN',SOURCE.PAY_AMOUNT * Ratio_To_Report(SUMM_AMOUNTS.amount) over (PARTITION BY SOURCE.PAY_SOURCE_ID))
2747 , G_ORG_ID
2748 , G_RUN_DATE
2749 , G_USER_ID
2750 , G_RUN_DATE
2751 , G_USER_ID
2752 FROM (
2753 SELECT pay_source_id
2754 , organization_id
2755 , project_id
2756 , task_id
2757 , SUM(amount) amount
2758 FROM ( SELECT S1.pay_source_id
2759 , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
2760 , project_id
2761 , task_id
2762 , SUM(ACCT_RAW_COST) AS amount
2763 FROM PA_PAY_DIST_LINES DIST
2764 ,PA_PAY_SOURCE_AMOUNTS S1
2765 WHERE S1.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2766 AND DISTRIBUTION_BASIS_CODE IN ('ST_AMT'
2767 ,'OT_AMT'
2768 , 'ST_OT_AMT')
2769 AND DIST.PERSON_ID = S1.PERSON_ID
2770 AND DIST.INTERFACE_RUN_ID = S1.INTERFACE_RUN_ID
2771 AND COALESCE(DIST.organization_id ,DIST.OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S1.PAY_SOURCE_ORGANIZATION_ID,DIST.organization_id ,DIST.OVERRIDE_TO_ORGANIZATION_ID,-99)
2772 AND project_id = NVL(S1.PAY_SOURCE_PROJECT_ID,project_id)
2773 AND task_id = NVL(S1.PAY_SOURCE_TASK_ID,task_id)
2774 AND
2775 (
2776 (
2777 S1.distribution_basis_code <> 'ST_OT_AMT'
2778 AND DIST.SYSTEM_LINKAGE_FUNCTION = DECODE(S1.distribution_basis_code
2779 ,'ST_AMT' ,'ST'
2780 ,'OT_AMT','OT' )
2781 )
2782 OR
2783 (
2784 S1.distribution_basis_code = 'ST_OT_AMT'
2785 AND DIST.SYSTEM_LINKAGE_FUNCTION IN ('ST'
2786 ,'OT')
2787 )
2788 )
2789 AND DIST.expenditure_type NOT IN (SELECT expenditure_type
2790 FROM PA_PAY_EXCLUDE_EXP_TYPES
2791 WHERE pay_element_map_id = S1.pay_element_map_id
2792 )
2793 GROUP BY S1.pay_source_id
2794 , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID)
2795 , project_id
2796 , task_id
2797
2798 UNION
2799
2800 SELECT S2.pay_source_id
2801 , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
2802 , project_id
2803 , task_id
2804 , SUM(acct_raw_cost) AS amount
2805 FROM PA_PAY_EXP_TEMP TEMP
2806 , PA_PAY_SOURCE_AMOUNTS S2
2807 WHERE S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2808 AND DISTRIBUTION_BASIS_CODE IN ('ST_AMT'
2809 ,'OT_AMT'
2810 , 'ST_OT_AMT')
2811 AND S2.PERSON_ID = TEMP.INCURRED_BY_PERSON_ID
2812 AND COALESCE(organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S2.PAY_SOURCE_ORGANIZATION_ID,organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99)
2813 AND project_id = NVL(S2.PAY_SOURCE_PROJECT_ID,project_id)
2814 AND task_id = NVL(S2.PAY_SOURCE_TASK_ID,task_id)
2815 AND COST_DISTRIBUTED_FLAG = 'Y'
2816 AND NVL(PAYROLL_ACCRUAL_FLAG,'N') <> 'Y' -- Bug 12813753
2817 AND
2818 (
2819 (
2820 S2.distribution_basis_code <> 'ST_OT_AMT'
2821 AND TEMP.SYSTEM_LINKAGE_FUNCTION = DECODE(S2.distribution_basis_code
2822 ,'ST_AMT' ,'ST'
2823 ,'OT_AMT','OT' )
2824 )
2825 OR
2826 (
2827 S2.distribution_basis_code = 'ST_OT_AMT'
2828 AND TEMP.SYSTEM_LINKAGE_FUNCTION IN ('ST'
2829 ,'OT')
2830 )
2831 )
2832 AND TEMP.expenditure_type NOT IN (SELECT expenditure_type
2833 FROM PA_PAY_EXCLUDE_EXP_TYPES
2834 WHERE pay_element_map_id = S2.pay_element_map_id
2835 )
2836 GROUP BY S2.pay_source_id
2837 , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID)
2838 , project_id
2839 , task_id
2840 )
2841 GROUP BY
2842 pay_source_id
2843 , organization_id
2844 , project_id
2845 , task_id ) SUMM_AMOUNTS , pa_Pay_source_amounts SOURCE WHERE SUMM_AMOUNTS.PAY_SOURCE_ID = SOURCE.PAY_SOURCE_ID ;
2846
2847 INSERT
2848 INTO PA_PAY_REJECTIONS_ALL
2849 (
2850 PAY_REJECTION_ID
2851 , INTERFACE_RUN_ID
2852 , PERSON_ID
2853 , ASSIGNMENT_ID
2854 , PAY_SOURCE_ID
2855 , PAY_ELEMENT_TYPE_ID
2856 , PAY_ELEMENT_TYPE_CODE
2857 , REJECTION_CODE
2858 , LAST_UPDATE_DATE
2859 , LAST_UPDATED_BY
2860 , CREATION_DATE
2861 , CREATED_BY
2862 )
2863 SELECT PA_PAY_REJECTION_ID_S.NEXTVAL
2864 , G_INTERFACE_RUN_ID
2865 , PS.PERSON_ID
2866 , PS.ASSIGNMENT_ID
2867 , PS.PAY_SOURCE_ID
2868 , PS.PAY_ELEMENT_TYPE_ID
2869 , PS.PAY_ELEMENT_TYPE_CODE
2870 , 'RC 35'
2871 , G_RUN_DATE
2872 , G_USER_ID
2873 , G_RUN_DATE
2874 , G_USER_ID
2875 FROM PA_PAY_SOURCE_AMOUNTS PS
2876 WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2877 AND DISTRIBUTION_BASIS_CODE IN ('ST_AMT'
2878 ,'OT_AMT'
2879 , 'ST_OT_AMT')
2880 AND PS.PAY_SOURCE_ID NOT IN ( SELECT PAY_SOURCE_ID
2881 FROM PA_PAY_DIST_LINES PPD
2882 WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2883 ) ;
2884
2885 EXCEPTION
2886 WHEN OTHERS THEN
2887 write_log(Log, 'Error in processing amount based elements -' || SQLERRM);
2888 RAISE ;
2889 END process_amount_based;
2890
2891
2892 /*
2893 Procedure : PROCESS_TOTAL_RAW
2894 Type : Private
2895 Purpose :
2896 Process the pay elements with distribution method of Total Raw .
2897 This procedure distributes the pay element based on amounts .
2898 Here the difference is that the amounts in PA_PAY_DIST_LINES
2899 from the current run is also considered.
2900 */
2901
2902
2903
2904 PROCEDURE process_total_raw
2905 IS
2906
2907
2908 BEGIN
2909
2910
2911 INSERT
2912 INTO
2913 PA_PAY_DIST_LINES
2914 (
2915 PAY_DIST_ID ,
2916 PAY_SOURCE_ID ,
2917 INTERFACE_RUN_ID ,
2918 PERSON_ID ,
2919 ASSIGNMENT_ID ,
2920 ORGANIZATION_ID ,
2921 PROJECT_ID ,
2922 TASK_ID ,
2923 EXPENDITURE_TYPE ,
2924 SYSTEM_LINKAGE_FUNCTION,
2925 EXPENDITURE_ITEM_DATE ,
2926 PAY_ELEMENT_TYPE_ID ,
2927 PAY_ELEMENT_TYPE_CODE ,
2928 QUANTITY ,
2929 DENOM_CURRENCY_CODE ,
2930 DENOM_RAW_COST ,
2931 DENOM_BURDEN_COST ,
2932 ORG_ID ,
2933 LAST_UPDATE_DATE ,
2934 LAST_UPDATED_BY ,
2935 CREATION_DATE ,
2936 CREATED_BY
2937 )
2938 SELECT
2939 PA_PAY_DIST_ID_S.NEXTVAL ,
2940 SOURCE.PAY_SOURCE_ID ,
2941 G_INTERFACE_RUN_ID ,
2942 SOURCE.PERSON_ID ,
2943 SOURCE.ASSIGNMENT_ID ,
2944 SUMM_AMOUNTS.ORGANIZATION_ID ,
2945 SUMM_AMOUNTS.PROJECT_ID ,
2946 SUMM_AMOUNTS.TASK_ID ,
2947 SOURCE.EXPENDITURE_TYPE ,
2948 DECODE(SOURCE.COST_TYPE_CODE,'RAW','PJ','BURDEN','BTC') ,
2949 G_PAY_PERIOD_END ,
2950 SOURCE.PAY_ELEMENT_TYPE_ID ,
2951 SOURCE.PAY_ELEMENT_TYPE_CODE ,
2952 0 ,
2953 SOURCE.PAY_CURRENCY_CODE ,
2954 DECODE(SOURCE.COST_TYPE_CODE,'RAW',SOURCE.PAY_AMOUNT * RATIO_TO_REPORT(
2955 SUMM_AMOUNTS.AMOUNT) OVER (PARTITION BY SOURCE.PAY_SOURCE_ID)),
2956 DECODE(SOURCE.COST_TYPE_CODE,'BURDEN',SOURCE.PAY_AMOUNT * RATIO_TO_REPORT
2957 (SUMM_AMOUNTS.AMOUNT) OVER (PARTITION BY SOURCE.PAY_SOURCE_ID)),
2958 G_ORG_ID ,
2959 G_RUN_DATE ,
2960 G_USER_ID ,
2961 G_RUN_DATE ,
2962 G_USER_ID
2963 FROM
2964 (
2965 SELECT
2966 PAY_SOURCE_ID ,
2967 ORGANIZATION_ID ,
2968 PROJECT_ID ,
2969 TASK_ID ,
2970 SUM(AMOUNT) AMOUNT
2971 FROM
2972 (
2973 SELECT
2974 S1.PAY_SOURCE_ID,
2975 NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ORGANIZATION_ID ,
2976 PROJECT_ID ,
2977 TASK_ID ,
2978 SUM(ACCT_RAW_COST) AS AMOUNT
2979 FROM
2980 PA_PAY_DIST_LINES DIST ,
2981 PA_PAY_SOURCE_AMOUNTS S1
2982 WHERE
2983 S1.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
2984 DISTRIBUTION_BASIS_CODE = 'TOTAL_RAW' AND
2985 DIST.PERSON_ID = S1.PERSON_ID AND
2986 DIST.INTERFACE_RUN_ID = S1.INTERFACE_RUN_ID AND
2987 COALESCE(DIST.ORGANIZATION_ID ,
2988 DIST.OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE( S1.PAY_SOURCE_ORGANIZATION_ID, DIST.ORGANIZATION_ID , DIST.OVERRIDE_TO_ORGANIZATION_ID,-99) AND
2989 PROJECT_ID = NVL(S1.PAY_SOURCE_PROJECT_ID, PROJECT_ID) AND
2990 TASK_ID = NVL(S1.PAY_SOURCE_TASK_ID,TASK_ID) AND
2991 DIST.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT','PJ') AND
2992 DIST.EXPENDITURE_TYPE NOT IN
2993 (
2994 SELECT
2995 EXPENDITURE_TYPE
2996 FROM
2997 PA_PAY_EXCLUDE_EXP_TYPES
2998 WHERE
2999 PAY_ELEMENT_MAP_ID = S1.PAY_ELEMENT_MAP_ID
3000 )
3001 GROUP BY
3002 S1.PAY_SOURCE_ID,
3003 NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID)
3004 ,
3005 PROJECT_ID ,
3006 TASK_ID
3007 UNION
3008 SELECT
3009 S2.PAY_SOURCE_ID,
3010 NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID) ORGANIZATION_ID ,
3011 PROJECT_ID ,
3012 TASK_ID ,
3013 SUM(ACCT_RAW_COST) AS AMOUNT
3014 FROM
3015 PA_PAY_EXP_TEMP TEMP ,
3016 PA_PAY_SOURCE_AMOUNTS S2
3017 WHERE
3018 S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3019 DISTRIBUTION_BASIS_CODE = 'TOTAL_RAW' AND
3020 S2.PERSON_ID = TEMP.INCURRED_BY_PERSON_ID AND
3021 COALESCE(ORGANIZATION_ID ,
3022 OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(S2.PAY_SOURCE_ORGANIZATION_ID,ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID,-99) AND
3023 PROJECT_ID = NVL(S2.PAY_SOURCE_PROJECT_ID,PROJECT_ID) AND
3024 TASK_ID = NVL(S2.PAY_SOURCE_TASK_ID,TASK_ID) AND
3025 COST_DISTRIBUTED_FLAG = 'Y' AND
3026 NVL(PAYROLL_ACCRUAL_FLAG,'N') <> 'Y' AND
3027 TEMP.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT','PJ') AND
3028 TEMP.EXPENDITURE_TYPE NOT IN
3029 (
3030 SELECT
3031 EXPENDITURE_TYPE
3032 FROM
3033 PA_PAY_EXCLUDE_EXP_TYPES
3034 WHERE
3035 PAY_ELEMENT_MAP_ID = S2.PAY_ELEMENT_MAP_ID
3036 )
3037 GROUP BY
3038 S2.PAY_SOURCE_ID,
3039 NVL(ORGANIZATION_ID ,OVERRIDE_TO_ORGANIZATION_ID),
3040 PROJECT_ID ,
3041 TASK_ID
3042 )
3043 GROUP BY
3044 PAY_SOURCE_ID ,
3045 ORGANIZATION_ID ,
3046 PROJECT_ID ,
3047 TASK_ID
3048 )
3049 SUMM_AMOUNTS ,
3050 PA_PAY_SOURCE_AMOUNTS SOURCE
3051 WHERE
3052 SUMM_AMOUNTS.PAY_SOURCE_ID = SOURCE.PAY_SOURCE_ID ;
3053
3054
3055 INSERT
3056 INTO PA_PAY_REJECTIONS_ALL
3057 (
3058 PAY_REJECTION_ID
3059 , INTERFACE_RUN_ID
3060 , PERSON_ID
3061 , ASSIGNMENT_ID
3062 , PAY_SOURCE_ID
3063 , PAY_ELEMENT_TYPE_ID
3064 , PAY_ELEMENT_TYPE_CODE
3065 , REJECTION_CODE
3066 , LAST_UPDATE_DATE
3067 , LAST_UPDATED_BY
3068 , CREATION_DATE
3069 , CREATED_BY
3070 )
3071 SELECT PA_PAY_REJECTION_ID_S.NEXTVAL
3072 , G_INTERFACE_RUN_ID
3073 , PS.PERSON_ID
3074 , PS.ASSIGNMENT_ID
3075 , PS.PAY_SOURCE_ID
3076 , PS.PAY_ELEMENT_TYPE_ID
3077 , PS.PAY_ELEMENT_TYPE_CODE
3078 , 'RC 35'
3079 , G_RUN_DATE
3080 , G_USER_ID
3081 , G_RUN_DATE
3082 , G_USER_ID
3083 FROM PA_PAY_SOURCE_AMOUNTS PS
3084 WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
3085 AND DISTRIBUTION_BASIS_CODE = 'TOTAL_RAW'
3086 AND PS.PAY_SOURCE_ID NOT IN ( SELECT PAY_SOURCE_ID
3087 FROM PA_PAY_DIST_LINES PPD
3088 WHERE PPD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
3089 ) ;
3090 END process_total_raw;
3091
3092
3093 /* Start changes for bug#12975007*/
3094
3095 function get_cost_method1(
3096 p_exp_id IN NUMBER,
3097 p_job_id IN NUMBER,
3098 p_org_id IN NUMBER,
3099 p_organization_id IN NUMBER,
3100 p_exp_item_id IN NUMBER,
3101 p_exp_item_date IN DATE)
3102 RETURN VARCHAR2 IS
3103
3104
3105 x_exp_id pa_expenditure_items_all.expenditure_id%type;
3106 x_exp_item_date pa_expenditure_items_all.expenditure_item_date%type;
3107
3108
3109
3110 cursor get_emp_override_rule is
3111 select --ei.org_id ORG_ID
3112 --,TO_CHAR(ei.expenditure_item_date,'YYYY/MM/DD') expenditure_item_date
3113 --,nvl(ei.override_to_organization_id
3114 -- ,exp.incurred_by_organization_id) orgj_id
3115 -- ,
3116 exp.incurred_by_person_id incurred_by_person_id
3117 -- ,ei.job_id JOB_ID
3118 ,detail.compensation_rule_set
3119 ,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
3120 ,to_char(detail.rate_schedule_id)
3121 ,detail.override_type
3122 ,detail.acct_rate_type
3123 ,detail.acct_rate_date_code
3124 ,detail.acct_exchange_rate
3125 ,detail.base_hours
3126 from pa_compensation_details detail
3127 -- ,pa_expenditure_items ei
3128 ,pa_expenditures exp
3129 where trunc(p_exp_item_date) between trunc(detail.start_date_active)
3130 and trunc(nvl(detail.end_date_active,p_exp_item_date))
3131 and detail.person_id = exp.incurred_by_person_id
3132 and exp.expenditure_id = x_exp_id
3133 --and ei.source_expenditure_item_id is null
3134 -- and ei.expenditure_item_id = p_exp_item_id
3135 -- and ei.po_line_id IS NULL
3136 ;
3137
3138 cursor get_exp_details is
3139 select
3140 p_job_id,
3141 p_org_id ORG_ID
3142 ,TO_CHAR(x_exp_item_date,'YYYY/MM/DD') expenditure_item_date
3143 ,p_organization_id orgj_id
3144 from dual;
3145
3146 l_org_id_tab pa_plsql_datatypes.IdTabTyp;
3147 l_organization_id_tab pa_plsql_datatypes.IdTabTyp;
3148 l_person_id_tab pa_plsql_datatypes.IdTabTyp;
3149 l_job_id_tab pa_plsql_datatypes.IdTabTyp;
3150 l_txn_date_tab pa_plsql_datatypes.Char30TabTyp;
3151 l_override_type_tab pa_plsql_datatypes.Char150TabTyp;
3152 l_calling_module varchar2(50) default 'STAFFED';
3153 l_Called_From varchar2(1) DEFAULT 'O';
3154 l_org_lab_sch_rule_id_tab pa_plsql_datatypes.IdTabTyp;
3155 l_costing_rule_tab pa_plsql_datatypes.Char150TabTyp;
3156 l_rate_sch_id_tab pa_plsql_datatypes.IdTabTyp;
3157 l_ot_project_id_tab pa_plsql_datatypes.IdTabTyp;
3158 l_ot_task_id_tab pa_plsql_datatypes.IdTabTyp;
3159 l_base_hours_tab pa_plsql_datatypes.IdTabTyp; /* 12.2 payroll intg ..ttc */
3160 l_rbc_elem_type_tab pa_plsql_datatypes.IdTabTyp;
3161 l_cost_rate_curr_code_tab pa_plsql_datatypes.Char150TabTyp;
3162 l_acct_rate_type_tab pa_plsql_datatypes.Char150TabTyp;
3163 l_acct_rate_date_code_tab pa_plsql_datatypes.Char150TabTyp;
3164 l_acct_exch_rate_tab pa_plsql_datatypes.Char30TabTyp;
3165 l_err_stage_tab pa_plsql_datatypes.NumTabTyp;
3166 l_err_code_tab pa_plsql_datatypes.Char150TabTyp;
3167 l_debug_mode varchar2(1);
3168 x_costing_method varchar2(30);
3169 x_rate_source_code varchar2(30);
3170
3171
3172 begin
3173
3174 x_exp_id := p_exp_id;
3175 x_exp_item_date := p_exp_item_date ;
3176
3177 IF (x_exp_id is not null) THEN
3178 --{
3179
3180 open get_exp_details;
3181 fetch get_exp_details bulk collect into
3182 l_job_id_tab
3183 ,l_org_id_tab
3184 ,l_txn_date_tab
3185 ,l_organization_id_tab
3186 ;
3187 close get_exp_details;
3188
3189
3190 if pa_cc_utils.g_debug_mode then
3191 l_debug_mode := 'Y';
3192 else
3193 l_debug_mode := 'N';
3194 end if;
3195
3196 if ( l_debug_mode = 'Y' ) THEN
3197 pa_debug.set_process( x_process => 'PLSQL'
3198 ,x_debug_mode => l_debug_mode
3199 );
3200 pa_cc_utils.set_curr_function('get_rate_source_cost_method');
3201 pa_cc_utils.log_message('Start ');
3202 end if;
3203
3204
3205
3206 open get_emp_override_rule;
3207 fetch get_emp_override_rule bulk collect into
3208 -- l_org_id_tab
3209 -- ,l_txn_date_tab
3210 -- ,l_organization_id_tab
3211 -- ,
3212 l_person_id_tab
3213 -- ,l_job_id_tab
3214 ,l_costing_rule_tab
3215 ,l_cost_rate_curr_code_tab
3216 ,l_rate_sch_id_tab
3217 ,l_override_type_tab
3218 ,l_acct_rate_type_tab
3219 ,l_acct_rate_date_code_tab
3220 ,l_acct_exch_rate_tab
3221 ,l_base_hours_tab;
3222 close get_emp_override_rule;
3223
3224
3225
3226 /* obtain the default rules defined at the organization and operating unit level also. */
3227 pa_cost_rate_pub.get_orgn_lvl_cst_info_set
3228 ( p_org_id_tab => l_org_id_tab
3229 ,p_organization_id_tab => l_organization_id_tab
3230 ,p_person_id_tab => l_person_id_tab
3231 ,p_job_id_tab => l_job_id_tab
3232 ,p_txn_date_tab => l_txn_date_tab
3233 ,p_override_type_tab => l_override_type_tab
3234 ,x_org_labor_sch_rule_id_tab => l_org_lab_sch_rule_id_tab
3235 ,x_costing_rule_tab => l_costing_rule_tab
3236 ,x_rate_sch_id_tab => l_rate_sch_id_tab
3237 ,x_ot_project_id_tab => l_ot_project_id_tab
3238 ,x_ot_task_id_tab => l_ot_task_id_tab
3239 ,x_base_hours_tab => l_base_hours_tab
3240 ,x_rbc_elem_type_tab => l_rbc_elem_type_tab
3241 ,x_cost_rate_curr_code_tab => l_cost_rate_curr_code_tab
3242 ,x_acct_rate_type_tab => l_acct_rate_type_tab
3243 ,x_acct_rate_date_code_tab => l_acct_rate_date_code_tab
3244 ,x_acct_exch_rate_tab => l_acct_exch_rate_tab
3245 ,x_err_stage_tab => l_err_stage_tab
3246 ,x_err_code_tab => l_err_code_tab
3247 );
3248
3249 if l_costing_rule_tab(1) is null then
3250 x_costing_method := null;
3251 x_rate_source_code := null;
3252 end if;
3253
3254
3255
3256 if l_costing_rule_tab(1) is not null then
3257
3258 select rule.costing_method, rule.rate_source_code
3259 into x_costing_method, x_rate_source_code
3260 from pa_compensation_rule_sets rule
3261 where rule.compensation_rule_set = l_costing_rule_tab(1);
3262
3263 end if;
3264
3265 ELSE
3266 --{
3267 x_costing_method := null;
3268 --}
3269 END IF;
3270
3271 RETURN x_costing_method;
3272 exception
3273 when others then
3274 x_costing_method := null;
3275 RETURN x_costing_method;
3276 end get_cost_method1;
3277
3278 /* End changes for bug#12975007*/
3279
3280 function get_cost_method(p_exp_item_id IN NUMBER,
3281 p_exp_item_date IN DATE)
3282 RETURN VARCHAR2 IS
3283
3284
3285 cursor get_emp_override_rule is
3286 select ei.org_id ORG_ID
3287 ,TO_CHAR(ei.expenditure_item_date,'YYYY/MM/DD') expenditure_item_date
3288 ,nvl(ei.override_to_organization_id
3289 ,exp.incurred_by_organization_id) orgj_id
3290 ,exp.incurred_by_person_id incurred_by_person_id
3291 ,ei.job_id JOB_ID
3292 ,detail.compensation_rule_set
3293 ,nvl(detail.cost_rate_currency_code,pa_currency.get_currency_code)
3294 ,to_char(detail.rate_schedule_id)
3295 ,detail.override_type
3296 ,detail.acct_rate_type
3297 ,detail.acct_rate_date_code
3298 ,detail.acct_exchange_rate
3299 ,detail.base_hours
3300 from pa_compensation_details detail
3301 ,pa_expenditure_items ei
3302 ,pa_expenditures exp
3303 where trunc(p_exp_item_date) between trunc(detail.start_date_active)
3304 and trunc(nvl(detail.end_date_active,p_exp_item_date))
3305 and detail.person_id = exp.incurred_by_person_id
3306 and exp.expenditure_id = ei.expenditure_id
3307 --and ei.source_expenditure_item_id is null
3308 and ei.expenditure_item_id = p_exp_item_id
3309 and ei.po_line_id IS NULL;
3310
3311 l_org_id_tab pa_plsql_datatypes.IdTabTyp;
3312 l_organization_id_tab pa_plsql_datatypes.IdTabTyp;
3313 l_person_id_tab pa_plsql_datatypes.IdTabTyp;
3314 l_job_id_tab pa_plsql_datatypes.IdTabTyp;
3315 l_txn_date_tab pa_plsql_datatypes.Char30TabTyp;
3316 l_override_type_tab pa_plsql_datatypes.Char150TabTyp;
3317 l_calling_module varchar2(50) default 'STAFFED';
3318 l_Called_From varchar2(1) DEFAULT 'O';
3319 l_org_lab_sch_rule_id_tab pa_plsql_datatypes.IdTabTyp;
3320 l_costing_rule_tab pa_plsql_datatypes.Char150TabTyp;
3321 l_rate_sch_id_tab pa_plsql_datatypes.IdTabTyp;
3322 l_ot_project_id_tab pa_plsql_datatypes.IdTabTyp;
3323 l_ot_task_id_tab pa_plsql_datatypes.IdTabTyp;
3324 l_base_hours_tab pa_plsql_datatypes.IdTabTyp; /* 12.2 payroll intg ..ttc */
3325 l_rbc_elem_type_tab pa_plsql_datatypes.IdTabTyp;
3326 l_cost_rate_curr_code_tab pa_plsql_datatypes.Char150TabTyp;
3327 l_acct_rate_type_tab pa_plsql_datatypes.Char150TabTyp;
3328 l_acct_rate_date_code_tab pa_plsql_datatypes.Char150TabTyp;
3329 l_acct_exch_rate_tab pa_plsql_datatypes.Char30TabTyp;
3330 l_err_stage_tab pa_plsql_datatypes.NumTabTyp;
3331 l_err_code_tab pa_plsql_datatypes.Char150TabTyp;
3332 l_debug_mode varchar2(1);
3333 x_costing_method varchar2(30);
3334 x_rate_source_code varchar2(30);
3335
3336
3337 begin
3338
3339 if pa_cc_utils.g_debug_mode then
3340 l_debug_mode := 'Y';
3341 else
3342 l_debug_mode := 'N';
3343 end if;
3344
3345 if ( l_debug_mode = 'Y' ) THEN
3346 pa_debug.set_process( x_process => 'PLSQL'
3347 ,x_debug_mode => l_debug_mode
3348 );
3349 pa_cc_utils.set_curr_function('get_rate_source_cost_method');
3350 pa_cc_utils.log_message('Start ');
3351 end if;
3352
3353 open get_emp_override_rule;
3354 fetch get_emp_override_rule bulk collect into
3355 l_org_id_tab
3356 ,l_txn_date_tab
3357 ,l_organization_id_tab
3358 ,l_person_id_tab
3359 ,l_job_id_tab
3360 ,l_costing_rule_tab
3361 ,l_cost_rate_curr_code_tab
3362 ,l_rate_sch_id_tab
3363 ,l_override_type_tab
3364 ,l_acct_rate_type_tab
3365 ,l_acct_rate_date_code_tab
3366 ,l_acct_exch_rate_tab
3367 ,l_base_hours_tab;
3368 close get_emp_override_rule;
3369
3370 /* obtain the default rules defined at the organization and operating unit level also. */
3371 pa_cost_rate_pub.get_orgn_lvl_cst_info_set
3372 ( p_org_id_tab => l_org_id_tab
3373 ,p_organization_id_tab => l_organization_id_tab
3374 ,p_person_id_tab => l_person_id_tab
3375 ,p_job_id_tab => l_job_id_tab
3376 ,p_txn_date_tab => l_txn_date_tab
3377 ,p_override_type_tab => l_override_type_tab
3378 ,x_org_labor_sch_rule_id_tab => l_org_lab_sch_rule_id_tab
3379 ,x_costing_rule_tab => l_costing_rule_tab
3380 ,x_rate_sch_id_tab => l_rate_sch_id_tab
3381 ,x_ot_project_id_tab => l_ot_project_id_tab
3382 ,x_ot_task_id_tab => l_ot_task_id_tab
3383 ,x_base_hours_tab => l_base_hours_tab
3384 ,x_rbc_elem_type_tab => l_rbc_elem_type_tab
3385 ,x_cost_rate_curr_code_tab => l_cost_rate_curr_code_tab
3386 ,x_acct_rate_type_tab => l_acct_rate_type_tab
3387 ,x_acct_rate_date_code_tab => l_acct_rate_date_code_tab
3388 ,x_acct_exch_rate_tab => l_acct_exch_rate_tab
3389 ,x_err_stage_tab => l_err_stage_tab
3390 ,x_err_code_tab => l_err_code_tab
3391 );
3392
3393 if l_costing_rule_tab(1) is null then
3394 x_costing_method := null;
3395 x_rate_source_code := null;
3396 end if;
3397
3398 if l_costing_rule_tab(1) is not null then
3399
3400 select rule.costing_method, rule.rate_source_code
3401 into x_costing_method, x_rate_source_code
3402 from pa_compensation_rule_sets rule
3403 where rule.compensation_rule_set = l_costing_rule_tab(1);
3404
3405 end if;
3406
3407 RETURN x_costing_method;
3408
3409 exception
3410 when others then
3411 x_costing_method := null;
3412 RETURN x_costing_method;
3413 end get_cost_method;
3414
3415
3416 PROCEDURE convert_to_fc
3417 IS
3418 l_pay_amount NUMBER(22,5);
3419 l_acct_pay_amount NUMBER(22,5);
3420 l_pay_amount_curr_code
3421 pa_compensation_details.cost_rate_currency_code%type;
3422 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
3423 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
3424 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
3425 l_err_code VARCHAR2(200) DEFAULT NULL;
3426 l_err_stage NUMBER;
3427 l_acct_currency_code VARCHAR2(15);
3428 l_conversion_date DATE;
3429 l_numerator NUMBER;
3430 l_denominator NUMBER;
3431 CURR_ATTR_NOT_FOUND EXCEPTION;
3432 BEGIN
3433
3434 UPDATE
3435 pa_pay_dist_lines
3436 SET
3437 ACCT_RAW_COST = DENOM_RAW_COST ,
3438 ACCT_CURRENCY_CODE = DENOM_CURRENCY_CODE
3439 WHERE
3440 DENOM_CURRENCY_CODE = G_FUNC_CURR_CODE AND
3441 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID;
3442 /* AND
3443 PERSON_ID = G_PERSON_ID; */
3444
3445
3446 write_log(Log,'updated ACCT amounts for records ' || SQL%rowcount);
3447 FOR X IN
3448 (
3449 SELECT
3450 pay_dist_id ,
3451 pay_source_id ,
3452 pay_element_type_id ,
3453 pay_element_type_code ,
3454 person_id ,
3455 TRUNC(expenditure_item_date)expenditure_item_date,
3456 denom_currency_code ,
3457 denom_raw_cost
3458 FROM
3459 pa_pay_dist_lines
3460 WHERE
3461 interface_run_id = G_INTERFACE_RUN_ID AND
3462 /* person_id = G_PERSON_ID AND */
3463 DENOM_CURRENCY_CODE <> G_FUNC_CURR_CODE
3464 )
3465 LOOP
3466 write_log(Log,'Getting conversion attributes');
3467 get_fc_rates( l_acct_rate_type ,l_acct_rate_date_code , l_err_stage , l_err_code ) ;
3468 IF (l_err_code IS NOT NULL) THEN
3469 -- reject_pay_element (X.PAY_SOURCE_ID, X.PAY_ELEMENT_TYPE_ID, X.PAY_ELEMENT_TYPE_CODE , 'RC 80' ) ; /*Commented for bug# 13579969*/
3470 reject_pay_element1 (X.PAY_SOURCE_ID, 'RC 80' ) ; /*Added for bug# 13579969*/
3471 write_log(Log,'l_err_code - ' || l_err_code);
3472 RAISE CURR_ATTR_NOT_FOUND;
3473 END IF ;
3474
3475 write_log(Log,'l_acct_rate_type - ' || l_acct_rate_type);
3476 write_log(Log,'l_acct_rate_date_code - ' || l_acct_rate_date_code);
3477
3478 IF l_acct_rate_date_code = 'P' THEN
3479 l_conversion_date := pa_utils2.get_pa_date(
3480 X.EXPENDITURE_ITEM_DATE,sysdate, G_ORG_ID);
3481 ELSE
3482 l_conversion_date := X.EXPENDITURE_ITEM_DATE;
3483 END IF;
3484
3485
3486
3487 pa_multi_currency.convert_amount ( P_from_currency => X.DENOM_CURRENCY_CODE,
3488 P_to_currency => G_FUNC_CURR_CODE,
3489 P_conversion_date =>l_conversion_date,
3490 P_conversion_type =>l_acct_rate_type,
3491 P_amount =>X.DENOM_RAW_COST,
3492 P_user_validate_flag =>'N',
3493 P_handle_exception_flag =>'Y',
3494 P_converted_amount => l_acct_pay_amount,
3495 P_denominator =>l_denominator,
3496 P_numerator => l_numerator,
3497 P_rate =>l_acct_exch_rate,
3498 X_status =>l_err_code ) ;
3499
3500 write_log(Log,'l_acct_exch_rate - ' || l_acct_exch_rate);
3501 write_log(Log,'l_acct_pay_amount - ' || l_acct_pay_amount);
3502
3503
3504 IF (l_err_code IS NOT NULL) THEN
3505 -- reject_pay_element (X.PAY_SOURCE_ID, X.PAY_ELEMENT_TYPE_ID, X.PAY_ELEMENT_TYPE_CODE , 'RC 80' ) ; /*Commented for bug# 13579969*/
3506 reject_pay_element1 (X.PAY_SOURCE_ID, 'RC 80' ) ; /*Added for bug# 13579969*/
3507 write_log(Log,'l_err_code - ' || l_err_code);
3508 RAISE CURR_ATTR_NOT_FOUND;
3509 END IF ;
3510
3511 UPDATE
3512 pa_pay_dist_lines
3513 SET
3514 ACCT_RAW_COST = l_acct_pay_amount ,
3515 ACCT_CURRENCY_CODE = G_FUNC_CURR_CODE ,
3516 ACCT_EXCHANGE_RATE = l_acct_exch_rate ,
3517 ACCT_RATE_DATE = l_conversion_date,
3518 ACCT_RATE_TYPE = l_acct_rate_type
3519 WHERE
3520 pay_dist_id = X.PAY_DIST_ID;
3521
3522 END LOOP;
3523 EXCEPTION
3524 WHEN CURR_ATTR_NOT_FOUND THEN
3525 write_log(Log,
3526 'No Currrecy attributes found . Rejecting Employee pay for Person ' ||
3527 G_PERSON_ID);
3528 RAISE REJECT_EMP_PAY;
3529 WHEN OTHERS THEN
3530 write_log(Log, 'ERROR in convert_to_fc ' || SQLERRM);
3531 RAISE REJECT_EMP_PAY;
3532 END convert_to_fc;
3533
3534
3535 /*
3536 Procedure : REJECT_PAY_ELEMENT
3537 Type : Private
3538 Purpose :
3539 Reject a pay element and makes an entry in PA_PAY_REJECTIONS_ALL.
3540 This is called internally from other procedures which do the distribution.
3541 */
3542
3543
3544
3545 PROCEDURE reject_pay_element
3546 (
3547
3548 P_PAY_SOURCE_ID NUMBER,
3549 P_PAY_ELEMENT_TYPE_ID NUMBER,
3550 P_PAY_ELEMENT_TYPE_CODE VARCHAR2,
3551 P_REJECTION_CODE VARCHAR2
3552 )
3553 IS
3554 PRAGMA AUTONOMOUS_TRANSACTION; -- Bug 12685945
3555 BEGIN
3556
3557 INSERT
3558 INTO
3559 PA_PAY_REJECTIONS_ALL
3560 (
3561 PAY_REJECTION_ID ,
3562 INTERFACE_RUN_ID ,
3563 PERSON_ID ,
3564 ASSIGNMENT_ID ,
3565 PAY_SOURCE_ID ,
3566 PAY_ELEMENT_TYPE_ID,
3567 PAY_ELEMENT_TYPE_CODE,
3568 REJECTION_CODE ,
3569 LAST_UPDATE_DATE ,
3570 LAST_UPDATED_BY ,
3571 CREATION_DATE ,
3572 CREATED_BY
3573 )
3574 VALUES
3575 (
3576 PA_PAY_REJECTION_ID_S.nextval,
3577 G_INTERFACE_RUN_ID ,
3578 G_PERSON_ID ,
3579 G_ASSIGNMENT_ID ,
3580 P_PAY_SOURCE_ID ,
3581 P_PAY_ELEMENT_TYPE_ID ,
3582 P_PAY_ELEMENT_TYPE_CODE ,
3583 P_REJECTION_CODE ,
3584 G_RUN_DATE ,
3585 G_USER_ID ,
3586 G_RUN_DATE ,
3587 G_USER_ID
3588 );
3589
3590 write_log (LOG, 'Reject pay Element Count' || SQL%rowcount);
3591 COMMIT;
3592 END reject_pay_element;
3593
3594 /*Start changes for bug# 13579969*/
3595
3596 /*
3597 Procedure : REJECT_PAY_ELEMENT1
3598 Type : Private
3599 Purpose :
3600 Reject a pay element and makes an entry in PA_PAY_REJECTIONS_ALL.
3601 This is called internally from other procedures which do the distribution.
3602 */
3603
3604 PROCEDURE reject_pay_element1
3605 (
3606 P_PAY_SOURCE_ID NUMBER,
3607 -- P_PAY_ELEMENT_TYPE_ID NUMBER,
3608 -- P_PAY_ELEMENT_TYPE_CODE VARCHAR2,
3609 P_REJECTION_CODE VARCHAR2
3610 )
3611 IS
3612 PRAGMA AUTONOMOUS_TRANSACTION; -- Bug 12685945
3613 BEGIN
3614
3615 INSERT
3616 INTO
3617 PA_PAY_REJECTIONS_ALL
3618 (
3619 PAY_REJECTION_ID ,
3620 INTERFACE_RUN_ID ,
3621 PERSON_ID ,
3622 ASSIGNMENT_ID ,
3623 PAY_SOURCE_ID ,
3624 PAY_ELEMENT_TYPE_ID,
3625 PAY_ELEMENT_TYPE_CODE,
3626 REJECTION_CODE ,
3627 LAST_UPDATE_DATE ,
3628 LAST_UPDATED_BY ,
3629 CREATION_DATE ,
3630 CREATED_BY
3631 )
3632 select PA_PAY_REJECTION_ID_S.nextval,
3633 INTERFACE_RUN_ID ,
3634 PERSON_ID ,
3635 ASSIGNMENT_ID ,
3636 P_PAY_SOURCE_ID ,
3637 PAY_ELEMENT_TYPE_ID ,
3638 PAY_ELEMENT_TYPE_CODE ,
3639 P_REJECTION_CODE ,
3640 G_RUN_DATE ,
3641 G_USER_ID ,
3642 G_RUN_DATE ,
3643 G_USER_ID
3644 from pa_pay_source_amounts where pay_source_id = P_PAY_SOURCE_ID;
3645
3646 write_log (LOG, 'Reject pay Element Count' || SQL%rowcount);
3647 COMMIT;
3648 END reject_pay_element1;
3649
3650 /*End changes for bug# 13579969*/
3651
3652 /*
3653 Procedure : REJECT_RELATED_PE
3654 Type : Private
3655 Purpose :
3656 If one pay element is rejected, the payroll processing for that employee assignment for that pay period
3657 is rejected.
3658
3659 */
3660
3661 PROCEDURE reject_related_pe IS
3662 L_PARENT_REJECTION_ID NUMBER;
3663 BEGIN
3664
3665
3666 /*
3667 SELECT
3668 MIN(PAY_REJECTION_ID)
3669 INTO
3670 L_PARENT_REJECTION_ID
3671 FROM
3672 PA_PAY_REJECTIONS_ALL
3673 WHERE
3674 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3675 PERSON_ID = G_PERSON_ID ;
3676
3677 */
3678
3679 FOR X IN (
3680 SELECT PPS.PERSON_ID , Min(PPR.PAY_REJECTION_ID) PARENT_REJECTION_ID
3681 FROM PA_PAY_REJECTIONS_ALL PPR , PA_PAY_SOURCE_AMOUNTS PPS
3682 WHERE PPR.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
3683 AND PPR.INTERFACE_RUN_ID = PPS.INTERFACE_RUN_ID
3684 AND PPR.PAY_SOURCE_ID = PPS.PAY_SOURCE_ID
3685 GROUP BY PPS.PERSON_ID
3686 )
3687
3688 LOOP
3689
3690 INSERT
3691 INTO
3692 PA_PAY_REJECTIONS_ALL
3693 (
3694 PAY_REJECTION_ID ,
3695 INTERFACE_RUN_ID ,
3696 PERSON_ID ,
3697 ASSIGNMENT_ID ,
3698 PAY_SOURCE_ID ,
3699 PAY_ELEMENT_TYPE_ID,
3700 PAY_ELEMENT_TYPE_CODE,
3701 PARENT_REJECTION_ID,
3702 REJECTION_CODE ,
3703 LAST_UPDATE_DATE ,
3704 LAST_UPDATED_BY ,
3705 CREATION_DATE ,
3706 CREATED_BY
3707 )
3708 SELECT
3709 PA_PAY_REJECTION_ID_S.NEXTVAL,
3710 G_INTERFACE_RUN_ID ,
3711 PERSON_ID ,
3712 ASSIGNMENT_ID ,
3713 PAY_SOURCE_ID ,
3714 PAY_ELEMENT_TYPE_ID,
3715 PAY_ELEMENT_TYPE_CODE,
3716 X.PARENT_REJECTION_ID ,
3717 'RC 85' ,
3718 G_RUN_DATE ,
3719 G_USER_ID ,
3720 G_RUN_DATE ,
3721 G_USER_ID
3722 FROM
3723 PA_PAY_SOURCE_AMOUNTS
3724 WHERE
3725 PERSON_ID = X.PERSON_ID AND
3726 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3727 PAY_source_id NOT IN
3728 (
3729 SELECT DISTINCT
3730 PAY_source_id
3731 FROM
3732 PA_PAY_REJECTIONS_ALL
3733 WHERE
3734 PERSON_ID = X.PERSON_ID AND
3735 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
3736 ) ;
3737
3738 write_log (LOG, SQL%ROWCOUNT || ' related elements rejected for Person_id ' || X.PERSON_ID );
3739 END LOOP ;
3740 EXCEPTION
3741 WHEN OTHERS THEN
3742 write_log(Log,SQLERRM);
3743 RAISE;
3744 END reject_related_pe;
3745
3746 /*
3747 Procedure : REJECT_PE_NO_SETUP
3748 Type : Private
3749 Purpose :
3750 For elements that are having no setup.
3751
3752
3753 */
3754
3755 PROCEDURE reject_pe_no_setup IS
3756 l_int_ext_flag pa_pay_audit.INT_EXT_INDICATOR%TYPE;
3757
3758 BEGIN
3759
3760
3761 SELECT INT_EXT_INDICATOR
3762 INTO
3763 l_int_ext_flag
3764 FROM pa_pay_audit_all
3765 WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID ;
3766
3767 IF l_int_ext_flag = 'INT' THEN
3768
3769 INSERT
3770 INTO
3771 PA_PAY_REJECTIONS_ALL
3772 (PAY_REJECTION_ID ,
3773 INTERFACE_RUN_ID ,
3774 PERSON_ID ,
3775 ASSIGNMENT_ID ,
3776 PAY_ELEMENT_TYPE_ID,
3777 REJECTION_CODE ,
3778 LAST_UPDATE_DATE ,
3779 LAST_UPDATED_BY ,
3780 CREATION_DATE ,
3781 CREATED_BY
3782 )
3783 SELECT
3784 PA_PAY_REJECTION_ID_S.NEXTVAL, rej.*
3785 from
3786 (select DISTINCT
3787 G_INTERFACE_RUN_ID INTERFACE_RUN_ID,
3788 G_PERSON_ID PERSON_ID,
3789 G_ASSIGNMENT_ID ASSIGNMENT_ID,
3790 ELEMENT_TYPE_ID ,
3791 'RC 05' REJECTION_CODE,
3792 G_RUN_DATE LAST_UPDATE_DATE ,
3793 G_USER_ID LAST_UPDATED_BY ,
3794 G_RUN_DATE CREATION_DATE ,
3795 G_USER_ID CREATED_BY
3796 FROM
3797 PA_PAY_INTERFACE_TEMP TEMP, PA_PAY_AUDIT AUD
3798 WHERE
3799 AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3800 -- TEMP.TIME_PERIOD_ID = AUD.TIME_PERIOD_ID AND
3801 TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
3802 AND TRUNC(NVL(TEMP.SOURCE_END_DATE,TEMP.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE)) /* bug 12690224*/
3803 AND TEMP.PERSON_ID = G_PERSON_ID AND
3804 ELEMENT_TYPE_ID NOT IN
3805 (
3806 SELECT
3807 PAY_ELEMENT_TYPE_ID
3808 FROM
3809 PA_PAY_SOURCE_AMOUNTS PSA
3810 WHERE
3811 PSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3812 PSA.PERSON_ID = G_PERSON_ID /*AND
3813 PAY_SOURCE_ID IN
3814 (
3815 SELECT
3816 PAY_SOURCE_ID
3817 FROM
3818 PA_PAY_REJECTIONS_ALL
3819 WHERE
3820 PERSON_ID = G_PERSON_ID AND
3821 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
3822 )*/
3823 ))rej ;
3824
3825 write_log (LOG, 'No of elements missing setup ' || SQL%rowcount);
3826
3827 ELSIF l_int_ext_flag = 'EXT' THEN
3828
3829 -- For third party payroll
3830 INSERT
3831 INTO
3832 PA_PAY_REJECTIONS_ALL
3833 (PAY_REJECTION_ID ,
3834 INTERFACE_RUN_ID ,
3835 PERSON_ID ,
3836 ASSIGNMENT_ID ,
3837 PAY_ELEMENT_TYPE_CODE,
3838 REJECTION_CODE ,
3839 LAST_UPDATE_DATE ,
3840 LAST_UPDATED_BY ,
3841 CREATION_DATE ,
3842 CREATED_BY
3843 )
3844 SELECT
3845 PA_PAY_REJECTION_ID_S.NEXTVAL, rej.*
3846 from
3847 (select DISTINCT
3848 G_INTERFACE_RUN_ID INTERFACE_RUN_ID,
3849 G_PERSON_ID PERSON_ID,
3850 G_ASSIGNMENT_ID ASSIGNMENT_ID,
3851 PAY_ELEMENT_TYPE_CODE ,
3852 'RC 05' REJECTION_CODE,
3853 G_RUN_DATE LAST_UPDATE_DATE ,
3854 G_USER_ID LAST_UPDATED_BY ,
3855 G_RUN_DATE CREATION_DATE ,
3856 G_USER_ID CREATED_BY
3857 FROM
3858 PA_PAY_EXT_INTERAFACE_V EXT, PA_PAY_AUDIT AUD
3859 WHERE
3860 AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3861 EXT.TIME_PERIOD_ID = AUD.TIME_PERIOD_ID AND
3862 EXT.PERSON_ID = G_PERSON_ID AND
3863 PAY_ELEMENT_TYPE_CODE NOT IN
3864 (
3865 SELECT
3866 PAY_ELEMENT_TYPE_CODE
3867 FROM
3868 PA_PAY_SOURCE_AMOUNTS PSA
3869 WHERE
3870 PSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3871 PSA.PERSON_ID = G_PERSON_ID
3872 ))rej ;
3873 write_log (LOG, 'No of elements missing setup ' || SQL%rowcount);
3874
3875 END IF ;
3876
3877 EXCEPTION
3878 WHEN OTHERS THEN
3879 write_log(Log,SQLERRM);
3880 RAISE;
3881 END reject_pe_no_setup;
3882
3883
3884 /*
3885 Procedure : PROCESS_BURDEN
3886 Type : Private
3887 Purpose :
3888 Creates BTC expenditure items from distributions created in PA_PAY_DIST_LINES
3889 */
3890
3891 PROCEDURE process_burden IS
3892 l_exp_group pa_expenditure_groups_all.expenditure_group%type;
3893 l_expenditure_id pa_expenditures_all.expenditure_id%type;
3894 l_exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
3895 i PLS_INTEGER;
3896 l_status VARCHAR2(100);
3897 l_msg_application VARCHAR2(10);
3898 l_msg_type VARCHAR2(1);
3899 l_msg_token1 VARCHAR2(2000);
3900 l_msg_token2 VARCHAR2(2000);
3901 l_msg_token3 VARCHAR2(2000);
3902 l_msg_count NUMBER;
3903 l_burden_exists VARCHAR2(1) := 'N';
3904 EI_PROCESS_FAILED EXCEPTION;
3905 l_billable_flag pa_expenditure_items_all.billable_flag%type;
3906 BEGIN
3907
3908
3909 BEGIN
3910
3911 SELECT 'Y' INTO l_burden_exists FROM dual
3912 WHERE EXISTS (SELECT 1
3913 FROM pa_pay_dist_lines
3914 WHERE SYSTEM_LINKAGE_FUNCTION = 'BTC'
3915 AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
3916 AND PERSON_ID = G_PERSON_ID ) ;
3917
3918 write_log(LOG,'Processing burden');
3919 Exception
3920 When NO_DATA_FOUND THEN
3921 l_burden_exists := 'N';
3922 write_log(LOG,'No burden items to process');
3923 END;
3924
3925 IF l_burden_exists = 'Y' THEN
3926 -- If burden exists, create a expenditure group
3927 l_exp_group := 'PA-PAY-BURDEN-' || G_INTERFACE_RUN_ID || '-' || G_PERSON_ID;
3928 pa_transactions.InsertExpGroup (
3929 l_exp_group,
3930 'RELEASED',
3931 G_PAY_PERIOD_END,
3932 'BTC',
3933 0,
3934 NULL,
3935 NULL,
3936 G_ORG_ID);
3937 write_log(LOG,'Expenditure Group Created -' || l_exp_group);
3938 END IF; -- If Burden Exists
3939
3940 -- For each organization and denom currency , create a expenditure
3941 FOR X IN
3942 (SELECT DISTINCT organization_id ,
3943 denom_currency_code
3944 FROM pa_pay_dist_lines
3945 WHERE SYSTEM_LINKAGE_FUNCTION = 'BTC'
3946 AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
3947 AND PERSON_ID = G_PERSON_ID
3948 )
3949 LOOP
3950 select pa_expenditures_s.nextval
3951 into l_expenditure_id from dual;
3952
3953 write_log(LOG,'Calling InsertExp' );
3954
3955 pa_transactions.InsertExp(
3956 x_expenditure_id =>l_expenditure_id,
3957 x_expend_status =>'APPROVED',
3958 x_expend_ending => pa_utils.NewGetWeekEnding((G_PAY_PERIOD_END)), --Bug 2236707,3551106
3959 x_expend_class => 'BT',
3960 x_inc_by_person => G_PERSON_ID ,
3961 x_inc_by_org => X.organization_id,
3962 x_expend_group => l_exp_group,
3963 x_entered_by_id =>X.organization_id,
3964 x_created_by_id =>0,
3965 x_attribute_category => null,
3966 x_attribute1 => null,
3967 x_attribute2 => null,
3968 x_attribute3 => null,
3969 x_attribute4 => null,
3970 x_attribute5 => null,
3971 x_attribute6 => null,
3972 x_attribute7 => null,
3973 x_attribute8 => null,
3974 x_attribute9 => null,
3975 x_attribute10=> null,
3976 x_description=> null,
3977 x_control_total=> null,
3978 x_denom_currency_code =>X.denom_currency_code,
3979 x_acct_currency_code => G_FUNC_CURR_CODE,
3980 x_acct_rate_type => null,
3981 x_acct_rate_date => null,
3982 x_acct_exchange_rate=> null
3983 ,X_person_type => null
3984 ,P_Org_Id => G_ORG_ID
3985 ,X_vendor_id => null
3986 );
3987 write_log(LOG,'After InsertExp' );
3988
3989 write_log(LOG,'Expenditure Created -' || l_expenditure_id);
3990
3991 i := 0;
3992 -- For each organization and denom currency above , insert all items
3993 FOR Y IN
3994 (
3995 SELECT
3996 DIST. ORGANIZATION_ID ,
3997 DIST.PROJECT_ID ,
3998 DIST.TASK_ID ,
3999 DIST.EXPENDITURE_TYPE ,
4000 DIST.EXPENDITURE_ITEM_DATE ,
4001 PA.PROJECT_CURRENCY_CODE ,
4002 PA.PROJFUNC_CURRENCY_CODE ,
4003 DIST.DENOM_CURRENCY_CODE ,
4004 DIST.ACCT_CURRENCY_CODE ,
4005 SUM(DIST.DENOM_BURDEN_COST) DENOM_BURDEN_COST
4006 FROM
4007 PA_PAY_DIST_LINES DIST ,
4008 PA_PROJECTS_ALL PA
4009 WHERE
4010 DIST.PROJECT_ID = PA.PROJECT_ID AND
4011 SYSTEM_LINKAGE_FUNCTION = 'BTC' AND
4012 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4013 PERSON_ID = G_PERSON_ID AND
4014 ORGANIZATION_ID = X.ORGANIZATION_ID AND
4015 DENOM_CURRENCY_CODE = X.DENOM_CURRENCY_CODE
4016 GROUP BY
4017 DIST. ORGANIZATION_ID ,
4018 DIST.PROJECT_ID ,
4019 DIST.TASK_ID ,
4020 DIST.EXPENDITURE_TYPE ,
4021 DIST.EXPENDITURE_ITEM_DATE ,
4022 PA.PROJECT_CURRENCY_CODE ,
4023 PA.PROJFUNC_CURRENCY_CODE ,
4024 DIST.DENOM_CURRENCY_CODE ,
4025 DIST.ACCT_CURRENCY_CODE
4026 ORDER BY
4027 PROJECT_ID ,
4028 TASK_ID
4029 )
4030 LOOP
4031
4032 i := i+1;
4033 select pa_expenditure_items_s.nextval
4034 into l_exp_item_id from dual;
4035
4036
4037 --Validate
4038 write_log(LOG,'Calling Validate transaction for ' || Y.project_id || '-'|| Y.task_id|| '-'||Y.expenditure_type );
4039
4040 pa_transactions_pub.validate_transaction(
4041 x_project_id => Y.project_id,
4042 x_task_id => Y.task_id,
4043 x_ei_date => Y.expenditure_item_date,
4044 x_expenditure_type => Y.expenditure_type,
4045 x_non_labor_resource => NULL,
4046 x_person_id => G_PERSON_ID,
4047 x_quantity => 0,
4048 x_denom_currency_code => Y.denom_currency_code,
4049 x_acct_currency_code =>Y.acct_currency_code,
4050 x_denom_raw_cost => null,
4051 x_acct_raw_cost => null,
4052 x_acct_rate_type => NULL, --x.acct_rate_type,
4053 x_acct_rate_date => NULL,--x.acct_rate_date,
4054 x_acct_exchange_rate => NULL,--x.acct_exchange_rate,
4055 x_transfer_ei => NULL,
4056 x_incurred_by_org_id => Y.organization_id,
4057 x_nl_resource_org_id => Y.organization_id,
4058 x_transaction_source => NULL,
4059 x_calling_module => 'PAXTREPE',
4060 x_vendor_id => NULL,
4061 x_entered_by_user_id => G_USER_ID,
4062 x_attribute_category => NULL,
4063 x_attribute1 => NULL,
4064 x_attribute2 => NULL,
4065 x_attribute3 => NULL,
4066 x_attribute4 => NULL,
4067 x_attribute6 => NULL,
4068 x_attribute5 => NULL,
4069 x_attribute7 => NULL,
4070 x_attribute8 => NULL,
4071 x_attribute9 => NULL,
4072 x_attribute10 => NULL,
4073 x_attribute11 => null,
4074 x_attribute12 => null,
4075 x_attribute13 => null,
4076 x_attribute14 => null,
4077 x_attribute15 => null,
4078 x_msg_application => l_msg_application,
4079 x_msg_type => l_msg_type,
4080 x_msg_token1 => l_msg_token1,
4081 x_msg_token2 => l_msg_token2,
4082 x_msg_token3 => l_msg_token3,
4083 x_msg_count => l_msg_count,
4084 x_msg_data => l_status,
4085 x_billable_flag => l_billable_flag,
4086 P_PROJFUNC_CURRENCY_CODE => NULL,
4087 P_PROJFUNC_COST_RATE_TYPE => NULL,
4088 P_PROJFUNC_COST_RATE_DATE => NULL,
4089 P_PROJFUNC_COST_EXCHG_RATE => NULL,
4090 P_ASSIGNMENT_ID => G_ASSIGNMENT_ID,
4091 P_WORK_TYPE_ID => NULL,
4092 p_sys_link_function => 'BTC',
4093 p_person_type => NULL
4094 );
4095
4096
4097
4098 IF (( l_status IS NOT NULL) AND (l_status IN ('PA_EXP_TASK_TC','PA_TR_EPE_TASK_TXN_CTRLS'))) THEN
4099 /* Raise RC 75 Task level expenditure control validations have been violated. */
4100 write_log(LOG,'Validate Transaction failed at task level for Project - ' || Y.Project_id || ' Task - ' || Y.task_id );
4101 write_log(LOG,'Status from Validate transaction ' || l_status );
4102
4103 FOR X IN (SELECT DISTINCT
4104 PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
4105 FROM
4106 PA_PAY_DIST_LINES
4107 WHERE
4108 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4109 PERSON_ID = G_PERSON_ID AND
4110 PROJECT_ID = Y.PROJECT_ID AND
4111 TASK_ID = Y.TASK_ID AND
4112 SYSTEM_LINKAGE_FUNCTION = 'BTC' AND
4113 EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
4114 LOOP
4115 reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 75');
4116 END LOOP;
4117 RAISE EI_PROCESS_FAILED;
4118
4119 ELSIF ( l_status IS NOT NULL) THEN
4120 /* RC 65 Project level expenditure control validations have been violated. */
4121 write_log(LOG,'Validate Transaction failed at Project level for Project - ' || Y.Project_id || ' Task - ' || Y.task_id );
4122 write_log(LOG,'Status from Validate transaction ' || l_status );
4123
4124 FOR X IN (SELECT DISTINCT
4125 PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
4126 FROM
4127 PA_PAY_DIST_LINES
4128 WHERE
4129 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4130 PERSON_ID = G_PERSON_ID AND
4131 PROJECT_ID = Y.PROJECT_ID AND
4132 TASK_ID = Y.TASK_ID AND
4133 SYSTEM_LINKAGE_FUNCTION = 'BTC' AND
4134 EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
4135 LOOP
4136 reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 65');
4137 END LOOP;
4138 RAISE EI_PROCESS_FAILED;
4139
4140 END IF;
4141
4142
4143 /*
4144 pa_transactions.LoadEi(
4145 x_expenditure_item_id =>l_exp_item_id,
4146 x_expenditure_id =>l_expenditure_id,
4147 x_expenditure_item_date =>Y.expenditure_item_date ,
4148 x_project_id => Y.project_id,
4149 x_task_id => Y.task_id ,
4150 x_expenditure_type =>Y.expenditure_type,
4151 x_non_labor_resource =>null,
4152 x_nl_resource_org_id =>null ,
4153 x_quantity =>0,
4154 x_raw_cost =>0,
4155 x_raw_cost_rate =>0,
4156 x_override_to_org_id =>null,
4157 x_billable_flag =>l_billable_flag, -- DANGER needs to be corercted JJG
4158 x_bill_hold_flag =>'N',
4159 x_orig_transaction_ref =>NULL ,
4160 x_transferred_from_ei =>NULL ,
4161 x_adj_expend_item_id =>NULL,
4162 x_attribute_category =>null ,
4163 x_attribute1 =>null ,
4164 x_attribute2 =>null ,
4165 x_attribute3 =>null ,
4166 x_attribute4 =>null ,
4167 x_attribute5 =>null ,
4168 x_attribute6 =>null ,
4169 x_attribute7 =>null ,
4170 x_attribute8 =>null ,
4171 x_attribute9 =>null ,
4172 x_attribute10 =>null ,
4173 x_ei_comment =>NULL ,
4174 x_transaction_source =>NULL ,
4175 x_source_exp_item_id =>NULL ,
4176 i => i ,
4177 x_job_id =>null ,
4178 x_org_id =>G_ORG_ID ,
4179 x_labor_cost_multiplier_name => NULL,
4180 x_drccid =>NULL ,
4181 x_crccid =>NULL ,
4182 x_cdlsr1 =>NULL ,
4183 x_cdlsr2 =>NULL ,
4184 x_cdlsr3 =>NULL ,
4185 x_gldate =>NULL ,
4186 x_bcost =>NULL ,
4187 x_bcostrate =>NULL ,
4188 x_etypeclass => 'BTC',
4189 x_burden_sum_dest_run_id =>NULL,
4190 x_burden_compile_set_id =>null,
4191 x_receipt_currency_amount =>null,
4192 x_receipt_currency_code =>null,
4193 x_receipt_exchange_rate =>null,
4194 x_denom_currency_code =>Y.denom_currency_code,
4195 x_denom_raw_cost =>null,
4196 x_denom_burdened_cost =>Y.denom_burden_cost,
4197 x_acct_currency_code =>G_FUNC_CURR_CODE,
4198 x_acct_rate_date =>NULL,
4199 x_acct_rate_type =>NULL,
4200 x_acct_exchange_rate =>NULL,
4201 x_acct_raw_cost =>null,
4202 x_acct_burdened_cost =>null,
4203 x_acct_exchange_rounding_limit =>null,
4204 x_project_currency_code =>Y.PROJECT_CURRENCY_CODE,
4205 x_project_rate_date =>null,
4206 x_project_rate_type =>null,
4207 x_project_exchange_rate =>null,
4208 p_project_raw_cost =>null,
4209 p_project_burdened_cost =>null,
4210 p_projfunc_currency_code => Y.PROJFUNC_CURRENCY_CODE,
4211 p_projfunc_cost_rate_date => null,
4212 p_projfunc_cost_rate_type => null,
4213 p_projfunc_cost_exchange_rate => null,
4214 p_work_type_id => null,
4215 X_Cross_Charge_Code => 'X',
4216 x_recv_operating_unit => G_ORG_ID
4217 ,p_Po_Line_Id => null
4218 ,p_adjustment_type => null
4219 ,p_Wip_Resource_Id => null
4220 ,p_Inventory_Item_Id => null
4221 ,p_src_system_linkage_function => null
4222 ,p_vendor_id => null
4223 ); */
4224
4225 INSERT
4226 INTO
4227 PA_EXPENDITURE_ITEMS_ALL
4228 (
4229 EXPENDITURE_ITEM_ID ,
4230 LAST_UPDATE_DATE ,
4231 LAST_UPDATED_BY ,
4232 CREATION_DATE ,
4233 CREATED_BY ,
4234 EXPENDITURE_ID ,
4235 TASK_ID ,
4236 EXPENDITURE_ITEM_DATE ,
4237 EXPENDITURE_TYPE ,
4238 COST_DISTRIBUTED_FLAG ,
4239 REVENUE_DISTRIBUTED_FLAG ,
4240 BILLABLE_FLAG ,
4241 BILL_HOLD_FLAG ,
4242 QUANTITY ,
4243 NET_ZERO_ADJUSTMENT_FLAG ,
4244 LAST_UPDATE_LOGIN ,
4245 REQUEST_ID ,
4246 PROGRAM_APPLICATION_ID ,
4247 PROGRAM_ID ,
4248 PROGRAM_UPDATE_DATE ,
4249 PROJECT_ID ,
4250 ORG_ID ,
4251 SYSTEM_LINKAGE_FUNCTION ,
4252 DENOM_CURRENCY_CODE ,
4253 /*DENOM_RAW_COST ,*/
4254 DENOM_BURDENED_COST,
4255 ACCT_CURRENCY_CODE ,
4256 PROJECT_CURRENCY_CODE ,
4257 ASSIGNMENT_ID ,
4258 PROJFUNC_CURRENCY_CODE ,
4259 HISTORICAL_FLAG ,
4260 COSTING_METHOD ,
4261 PAYROLL_ACCRUAL_FLAG ,
4262 INTERFACE_RUN_ID ,
4263 COST_BURDEN_DISTRIBUTED_FLAG,
4264 CC_CROSS_CHARGE_TYPE ,
4265 CC_BL_DISTRIBUTED_CODE ,
4266 CC_IC_PROCESSED_CODE ,
4267 CC_PRVDR_COST_RECLASS_CODE
4268 )
4269 VALUES
4270 (
4271 l_exp_item_id ,
4272 G_RUN_DATE , -- LAST_UPDATE_DATE
4273 G_USER_ID , -- LAST_UPDATED_BY
4274 G_RUN_DATE , -- CREATION_DATE
4275 G_USER_ID , -- CREATED_BY
4276 l_expenditure_id ,
4277 Y.TASK_ID ,
4278 Y.EXPENDITURE_ITEM_DATE ,
4279 Y.EXPENDITURE_TYPE ,
4280 'N' , --COST_DISTRIBUTED_FLAG
4281 'N' ,--REVENUE_DISTRIBUTED_FLAG
4282 l_billable_flag ,--BILLABLE_FLAG
4283 'N' ,--BILL_HOLD_FLAG
4284 0 ,--QUANTITY
4285 'N' , --NET_ZERO_ADJUSTMENT_FLAG
4286 G_LOGIN_ID ,--LAST_UPDATE_LOGIN
4287 G_REQUEST_ID ,--REQUEST_ID
4288 275 ,
4289 G_PROGRAM_ID ,
4290 G_RUN_DATE ,--PROGRAM_UPDATE_DATE
4291 Y.PROJECT_ID ,
4292 G_ORG_ID ,
4293 'BTC' ,
4294 Y.denom_CURRENCY_CODE , --DENOM_CURRENCY_CODE
4295 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1( Y.denom_burden_cost, Y.denom_CURRENCY_CODE) ,--DENOM_BURDENED_COST
4296 G_FUNC_CURR_CODE ,
4297 Y.PROJECT_CURRENCY_CODE ,
4298 G_ASSIGNMENT_ID ,
4299 Y.PROJFUNC_CURRENCY_CODE ,
4300 'N' ,--HISTORICAL_FLAG
4301 'ACTUAL' ,
4302 'N' ,
4303 G_INTERFACE_RUN_ID ,
4304 'X' ,
4305 'NO' ,
4306 'X' ,
4307 'X' ,
4308 'N'
4309 ) ;
4310
4311 /*Added for bug#12975007*/
4312 /*PAPAY_REP*/
4313 UPDATE PA_PAY_DIST_LINES
4314 SET TARGET_EXPENDITURE_ITEM_ID = l_exp_item_id
4315 WHERE ORGANIZATION_ID = Y. ORGANIZATION_ID
4316 AND PROJECT_ID = Y.PROJECT_ID
4317 AND TASK_ID = Y.TASK_ID
4318 AND EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE
4319 AND EXPENDITURE_ITEM_DATE = Y.EXPENDITURE_ITEM_DATE
4320 AND DENOM_CURRENCY_CODE = Y.DENOM_CURRENCY_CODE
4321 AND ACCT_CURRENCY_CODE = Y.ACCT_CURRENCY_CODE ;
4322 /*PAPAY_REP*/
4323
4324 write_log(LOG,'Burden expenditure Item created' || l_exp_item_id);
4325
4326 END LOOP; -- End Y loop .. Exp Items
4327 -- pa_transactions.InsItems(1,0,'BTC','BTC',i, l_status,'N');
4328
4329 /* UPDATE pa_expenditure_items_all
4330 SET interface_run_id = G_INTERFACE_RUN_ID
4331 ,COSTING_METHOD = 'ACTUAL'
4332 ,PAYROLL_ACCRUAL_FLAG = 'N'
4333 WHERE expenditure_id = l_expenditure_id ; */
4334
4335 -- write_log(LOG,'Expenditure Items Created -' || i || ' burden amounts processed ');
4336
4337
4338 -- write_log(LOG,'After InsItems' );
4339 -- pa_transactions.FlushEiTabs;
4340
4341
4342 END LOOP; -- X Loop Expenditures
4343
4344 EXCEPTION
4345
4346 WHEN EI_PROCESS_FAILED THEN
4347 write_log(Log, 'EI creation failed while trying to create burden items');
4348 RAISE REJECT_EMP_PAY;
4349
4350 WHEN OTHERS THEN
4351 write_log(Log, 'process_burden' || SQLERRM);
4352 RAISE REJECT_EMP_PAY;
4353 END process_burden;
4354 /*
4355 Procedure : PROCESS_MISC
4356 Type : Private
4357 Purpose :
4358 Creates PJ expenditure items from distributions created in PA_PAY_DIST_LINES
4359 */
4360
4361 PROCEDURE process_misc IS
4362 l_exp_group pa_expenditure_groups_all.expenditure_group%type;
4363 l_expenditure_id pa_expenditures_all.expenditure_id%type;
4364 l_exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
4365 i PLS_INTEGER;
4366 l_status VARCHAR2(100);
4367 l_msg_application VARCHAR2(10);
4368 l_msg_type VARCHAR2(1);
4369 l_msg_token1 VARCHAR2(2000);
4370 l_msg_token2 VARCHAR2(2000);
4371 l_msg_token3 VARCHAR2(2000);
4372 l_msg_count NUMBER;
4373 l_misc_exists VARCHAR2(1) := 'N';
4374 EI_PROCESS_FAILED EXCEPTION;
4375 l_billable_flag pa_expenditure_items_all.billable_flag%TYPE;
4376
4377
4378
4379 BEGIN
4380
4381 BEGIN
4382 SELECT 'Y' INTO l_misc_exists FROM dual
4383 WHERE EXISTS (SELECT 1
4384 FROM pa_pay_dist_lines
4385 WHERE SYSTEM_LINKAGE_FUNCTION = 'PJ'
4386 AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
4387 AND PERSON_ID = G_PERSON_ID ) ;
4388
4389 write_log(Log, 'Processing Misc ');
4390 EXCEPTION
4391 WHEN NO_DATA_FOUND THEN
4392 l_misc_exists := 'N';
4393 write_log(Log, 'No Misc items to process ');
4394 END;
4395
4396
4397
4398
4399 IF l_misc_exists = 'Y' THEN
4400
4401 -- If PJ exists, create a expenditure group
4402
4403 l_exp_group := 'PA-PAY-MISC-' || G_INTERFACE_RUN_ID || '-' || G_PERSON_ID;
4404 pa_transactions.InsertExpGroup (
4405 l_exp_group,
4406 'RELEASED',
4407 G_PAY_PERIOD_END,
4408 'PJ',
4409 0,
4410 NULL,
4411 NULL,
4412 G_ORG_ID);
4413
4414
4415 write_log(LOG,'Expenditure Group Created -' || l_exp_group);
4416 END IF; -- If PJ Exists
4417
4418
4419
4420
4421 -- For each organization and denom currency , create a expenditure
4422 FOR X IN
4423 (SELECT DISTINCT organization_id ,
4424 denom_currency_code
4425 FROM pa_pay_dist_lines
4426 WHERE SYSTEM_LINKAGE_FUNCTION = 'PJ'
4427 AND INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
4428 AND PERSON_ID = G_PERSON_ID
4429 )
4430 LOOP
4431
4432 select pa_expenditures_s.nextval
4433 into l_expenditure_id from dual;
4434
4435 write_log(LOG,'Calling InsertExp for ' || X.organization_id || ' - ' || x.denom_currency_code );
4436
4437 pa_transactions.InsertExp(
4438 x_expenditure_id =>l_expenditure_id,
4439 x_expend_status =>'APPROVED',
4440 x_expend_ending => pa_utils.NewGetWeekEnding((G_PAY_PERIOD_END)), --Bug 2236707,3551106
4441 x_expend_class => 'PU',
4442 x_inc_by_person => G_PERSON_ID ,
4443 x_inc_by_org => X.organization_id,
4444 x_expend_group => l_exp_group,
4445 x_entered_by_id =>X.organization_id,
4446 x_created_by_id =>0,
4447 x_attribute_category => null,
4448 x_attribute1 => null,
4449 x_attribute2 => null,
4450 x_attribute3 => null,
4451 x_attribute4 => null,
4452 x_attribute5 => null,
4453 x_attribute6 => null,
4454 x_attribute7 => null,
4455 x_attribute8 => null,
4456 x_attribute9 => null,
4457 x_attribute10=> null,
4458 x_description=> null,
4459 x_control_total=> null,
4460 x_denom_currency_code =>X.denom_currency_code,
4461 x_acct_currency_code => G_FUNC_CURR_CODE,
4462 x_acct_rate_type => null,
4463 x_acct_rate_date => null,
4464 x_acct_exchange_rate=> null
4465 ,X_person_type => null
4466 ,P_Org_Id => G_ORG_ID
4467 ,X_vendor_id => null
4468 );
4469 write_log(LOG,'After InsertExp' );
4470
4471
4472 write_log(LOG,'Expenditure Created -' || l_expenditure_id);
4473
4474
4475 i := 0;
4476 -- For each organization and denom currency above , insert all items
4477 FOR Y IN
4478 (
4479 SELECT
4480 DIST.ORGANIZATION_ID ,
4481 DIST.PROJECT_ID ,
4482 DIST.TASK_ID ,
4483 DIST.EXPENDITURE_TYPE ,
4484 DIST.EXPENDITURE_ITEM_DATE ,
4485 PA.PROJECT_CURRENCY_CODE ,
4486 PA.PROJFUNC_CURRENCY_CODE ,
4487 DIST.DENOM_CURRENCY_CODE ,
4488 DIST.acct_currency_code,
4489 SUM(DIST.DENOM_RAW_COST) DENOM_RAW_COST
4490 FROM
4491 PA_PAY_DIST_LINES DIST,
4492 PA_PROJECTS_ALL PA
4493 WHERE
4494 DIST.PROJECT_ID = PA.PROJECT_ID AND
4495 SYSTEM_LINKAGE_FUNCTION = 'PJ' AND
4496 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4497 PERSON_ID = G_PERSON_ID AND
4498 ORGANIZATION_ID = X.ORGANIZATION_ID AND
4499 DENOM_CURRENCY_CODE = X.DENOM_CURRENCY_CODE
4500 GROUP BY
4501 DIST.ORGANIZATION_ID ,
4502 DIST.PROJECT_ID ,
4503 DIST.TASK_ID ,
4504 DIST.EXPENDITURE_TYPE ,
4505 DIST.EXPENDITURE_ITEM_DATE ,
4506 PA.PROJECT_CURRENCY_CODE ,
4507 PA.PROJFUNC_CURRENCY_CODE ,
4508 DIST.DENOM_CURRENCY_CODE ,
4509 DIST.acct_currency_code
4510 ORDER BY
4511 PROJECT_ID ,
4512 TASK_ID
4513 )
4514 LOOP
4515
4516 i := i+1;
4517 select pa_expenditure_items_s.nextval
4518 into l_exp_item_id from dual;
4519
4520
4521
4522
4523 --Validate
4524
4525 pa_transactions_pub.validate_transaction(
4526 x_project_id => Y.project_id,
4527 x_task_id => Y.task_id,
4528 x_ei_date => Y.expenditure_item_date,
4529 x_expenditure_type => Y.expenditure_type,
4530 x_non_labor_resource => NULL,
4531 x_person_id => G_PERSON_ID,
4532 x_quantity => 0,
4533 x_denom_currency_code => Y.denom_currency_code,
4534 x_acct_currency_code =>Y.acct_currency_code,
4535 x_denom_raw_cost => null,
4536 x_acct_raw_cost => null,
4537 x_acct_rate_type => NULL, --x.acct_rate_type,
4538 x_acct_rate_date => NULL,--x.acct_rate_date,
4539 x_acct_exchange_rate => NULL,--x.acct_exchange_rate,
4540 x_transfer_ei => NULL,
4541 x_incurred_by_org_id => Y.organization_id,
4542 x_nl_resource_org_id => Y.organization_id,
4543 x_transaction_source => NULL,
4544 x_calling_module => 'PAXTREPE',
4545 x_vendor_id => NULL,
4546 x_entered_by_user_id => G_USER_ID,
4547 x_attribute_category => NULL,
4548 x_attribute1 => NULL,
4549 x_attribute2 => NULL,
4550 x_attribute3 => NULL,
4551 x_attribute4 => NULL,
4552 x_attribute6 => NULL,
4553 x_attribute5 => NULL,
4554 x_attribute7 => NULL,
4555 x_attribute8 => NULL,
4556 x_attribute9 => NULL,
4557 x_attribute10 => NULL,
4558 x_attribute11 => null,
4559 x_attribute12 => null,
4560 x_attribute13 => null,
4561 x_attribute14 => null,
4562 x_attribute15 => null,
4563 x_msg_application => l_msg_application,
4564 x_msg_type => l_msg_type,
4565 x_msg_token1 => l_msg_token1,
4566 x_msg_token2 => l_msg_token2,
4567 x_msg_token3 => l_msg_token3,
4568 x_msg_count => l_msg_count,
4569 x_msg_data => l_status,
4570 x_billable_flag => l_billable_flag,
4571 P_PROJFUNC_CURRENCY_CODE => NULL,
4572 P_PROJFUNC_COST_RATE_TYPE => NULL,
4573 P_PROJFUNC_COST_RATE_DATE => NULL,
4574 P_PROJFUNC_COST_EXCHG_RATE => NULL,
4575 P_ASSIGNMENT_ID => G_ASSIGNMENT_ID,
4576 P_WORK_TYPE_ID => NULL,
4577 p_sys_link_function => 'PJ',
4578 p_person_type => NULL
4579 );
4580
4581 IF (( l_status IS NOT NULL) AND (l_status IN ('PA_EXP_TASK_TC','PA_TR_EPE_TASK_TXN_CTRLS'))) THEN
4582 /* Raise RC 75 Task level expenditure control validations have been violated. */
4583 write_log(LOG,'Validate Transaction failed at task level for Project - ' || Y.Project_id || ' Task - ' || Y.task_id );
4584 write_log(LOG,'Status from validate transaction ' || l_status );
4585
4586 FOR X IN (SELECT DISTINCT
4587 PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
4588 FROM
4589 PA_PAY_DIST_LINES
4590 WHERE
4591 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4592 PERSON_ID = G_PERSON_ID AND
4593 PROJECT_ID = Y.PROJECT_ID AND
4594 TASK_ID = Y.TASK_ID AND
4595 SYSTEM_LINKAGE_FUNCTION = 'PJ' AND
4596 EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
4597 LOOP
4598 reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 75');
4599 END LOOP;
4600
4601 RAISE EI_PROCESS_FAILED;
4602
4603 ELSIF ( l_status IS NOT NULL) THEN
4604 /* RC 65 Project level expenditure control validations have been violated. */
4605 write_log(LOG,'Validate Transaction failed at Project level for Project - ' || Y.Project_id || ' Task - ' || Y.task_id );
4606 write_log(LOG,'Status from validate transaction ' || l_status );
4607
4608 FOR X IN (SELECT DISTINCT
4609 PAY_SOURCE_ID,PAY_ELEMENT_TYPE_ID,PAY_ELEMENT_TYPE_CODE
4610 FROM
4611 PA_PAY_DIST_LINES
4612 WHERE
4613 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4614 PERSON_ID = G_PERSON_ID AND
4615 PROJECT_ID = Y.PROJECT_ID AND
4616 TASK_ID = Y.TASK_ID AND
4617 SYSTEM_LINKAGE_FUNCTION = 'PJ' AND
4618 EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE)
4619 LOOP
4620 reject_pay_element ( X.PAY_SOURCE_ID,X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE,'RC 65');
4621 END LOOP;
4622
4623 RAISE EI_PROCESS_FAILED;
4624
4625 END IF;
4626 /*
4627
4628 pa_transactions.LoadEi(
4629 x_expenditure_item_id =>l_exp_item_id,
4630 x_expenditure_id =>l_expenditure_id,
4631 x_expenditure_item_date =>Y.expenditure_item_date ,
4632 x_project_id => Y.project_id,
4633 x_task_id => Y.task_id ,
4634 x_expenditure_type =>Y.expenditure_type,
4635 x_non_labor_resource =>null,
4636 x_nl_resource_org_id =>null ,
4637 x_quantity =>0,
4638 x_raw_cost =>0,
4639 x_raw_cost_rate =>0,
4640 x_override_to_org_id =>null,
4641 x_billable_flag => l_billable_flag, -- DANGER needs to be corercted JJG
4642 x_bill_hold_flag =>'N',
4643 x_orig_transaction_ref =>NULL ,
4644 x_transferred_from_ei =>NULL ,
4645 x_adj_expend_item_id =>NULL,
4646 x_attribute_category =>null ,
4647 x_attribute1 =>null ,
4648 x_attribute2 =>null ,
4649 x_attribute3 =>null ,
4650 x_attribute4 =>null ,
4651 x_attribute5 =>null ,
4652 x_attribute6 =>null ,
4653 x_attribute7 =>null ,
4654 x_attribute8 =>null ,
4655 x_attribute9 =>null ,
4656 x_attribute10 =>null ,
4657 x_ei_comment =>NULL ,
4658 x_transaction_source =>NULL ,
4659 x_source_exp_item_id =>NULL ,
4660 i => i ,
4661 x_job_id =>null ,
4662 x_org_id =>G_ORG_ID ,
4663 x_labor_cost_multiplier_name => NULL,
4664 x_drccid =>NULL ,
4665 x_crccid =>NULL ,
4666 x_cdlsr1 =>NULL ,
4667 x_cdlsr2 =>NULL ,
4668 x_cdlsr3 =>NULL ,
4669 x_gldate =>NULL ,
4670 x_bcost =>NULL ,
4671 x_bcostrate =>NULL ,
4672 x_etypeclass => 'PJ',
4673 x_burden_sum_dest_run_id =>NULL,
4674 x_burden_compile_set_id =>null,
4675 x_receipt_currency_amount =>null,
4676 x_receipt_currency_code =>null,
4677 x_receipt_exchange_rate =>null,
4678 x_denom_currency_code =>Y.denom_currency_code,
4679 x_denom_raw_cost =>Y.denom_raw_cost,
4680 x_denom_burdened_cost =>null,
4681 x_acct_currency_code =>G_FUNC_CURR_CODE,
4682 x_acct_rate_date =>NULL,
4683 x_acct_rate_type =>NULL,
4684 x_acct_exchange_rate =>NULL,
4685 x_acct_raw_cost =>null,
4686 x_acct_burdened_cost =>null,
4687 x_acct_exchange_rounding_limit =>null,
4688 x_project_currency_code =>Y.PROJECT_CURRENCY_CODE,
4689 x_project_rate_date =>null,
4690 x_project_rate_type =>null,
4691 x_project_exchange_rate =>null,
4692 p_project_raw_cost =>null,
4693 p_project_burdened_cost =>null,
4694 p_projfunc_currency_code => Y.PROJFUNC_CURRENCY_CODE,
4695 p_projfunc_cost_rate_date => null,
4696 p_projfunc_cost_rate_type => null,
4697 p_projfunc_cost_exchange_rate => null,
4698 p_work_type_id => null,
4699 X_Cross_Charge_Code => 'X',
4700 x_recv_operating_unit => G_ORG_ID
4701 ,p_Po_Line_Id => null
4702 ,p_adjustment_type => null
4703 ,p_Wip_Resource_Id => null
4704 ,p_Inventory_Item_Id => null
4705 ,p_src_system_linkage_function => null
4706 ,p_vendor_id => null
4707 );
4708
4709 */
4710
4711 INSERT
4712 INTO
4713 PA_EXPENDITURE_ITEMS_ALL
4714 (
4715 EXPENDITURE_ITEM_ID ,
4716 LAST_UPDATE_DATE ,
4717 LAST_UPDATED_BY ,
4718 CREATION_DATE ,
4719 CREATED_BY ,
4720 EXPENDITURE_ID ,
4721 TASK_ID ,
4722 EXPENDITURE_ITEM_DATE ,
4723 EXPENDITURE_TYPE ,
4724 COST_DISTRIBUTED_FLAG ,
4725 REVENUE_DISTRIBUTED_FLAG ,
4726 BILLABLE_FLAG ,
4727 BILL_HOLD_FLAG ,
4728 QUANTITY ,
4729 NET_ZERO_ADJUSTMENT_FLAG ,
4730 LAST_UPDATE_LOGIN ,
4731 REQUEST_ID ,
4732 PROGRAM_APPLICATION_ID ,
4733 PROGRAM_ID ,
4734 PROGRAM_UPDATE_DATE ,
4735 PROJECT_ID ,
4736 ORG_ID ,
4737 SYSTEM_LINKAGE_FUNCTION ,
4738 DENOM_CURRENCY_CODE ,
4739 DENOM_RAW_COST ,
4740 /*DENOM_BURDENED_COST*/
4741 ACCT_CURRENCY_CODE ,
4742 PROJECT_CURRENCY_CODE ,
4743 ASSIGNMENT_ID ,
4744 PROJFUNC_CURRENCY_CODE ,
4745 HISTORICAL_FLAG ,
4746 COSTING_METHOD ,
4747 PAYROLL_ACCRUAL_FLAG ,
4748 INTERFACE_RUN_ID ,
4749 COST_BURDEN_DISTRIBUTED_FLAG,
4750 CC_CROSS_CHARGE_TYPE ,
4751 CC_BL_DISTRIBUTED_CODE ,
4752 CC_IC_PROCESSED_CODE ,
4753 CC_PRVDR_COST_RECLASS_CODE
4754 )
4755 VALUES
4756 (
4757 l_exp_item_id ,
4758 G_RUN_DATE , -- LAST_UPDATE_DATE
4759 G_USER_ID , -- LAST_UPDATED_BY
4760 G_RUN_DATE , -- CREATION_DATE
4761 G_USER_ID , -- CREATED_BY
4762 l_expenditure_id ,
4763 Y.TASK_ID ,
4764 Y.EXPENDITURE_ITEM_DATE ,
4765 Y.EXPENDITURE_TYPE ,
4766 'N' , --COST_DISTRIBUTED_FLAG
4767 'N' ,--REVENUE_DISTRIBUTED_FLAG
4768 l_billable_flag ,--BILLABLE_FLAG
4769 'N' ,--BILL_HOLD_FLAG
4770 0 ,--QUANTITY
4771 'N' , --NET_ZERO_ADJUSTMENT_FLAG
4772 G_LOGIN_ID ,--LAST_UPDATE_LOGIN
4773 G_REQUEST_ID ,--REQUEST_ID
4774 275 ,
4775 G_PROGRAM_ID ,
4776 G_RUN_DATE ,--PROGRAM_UPDATE_DATE
4777 Y.PROJECT_ID ,
4778 G_ORG_ID ,
4779 'PJ' ,
4780 Y.denom_CURRENCY_CODE , --DENOM_CURRENCY_CODE
4781 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1( Y.denom_raw_cost, Y.denom_CURRENCY_CODE) ,--DENOM_RAW_COST
4782 G_FUNC_CURR_CODE ,
4783 Y.PROJECT_CURRENCY_CODE ,
4784 G_ASSIGNMENT_ID ,
4785 Y.PROJFUNC_CURRENCY_CODE ,
4786 'N' ,--HISTORICAL_FLAG
4787 'ACTUAL' ,
4788 'N' ,
4789 G_INTERFACE_RUN_ID ,
4790 'X' ,
4791 'NO' ,
4792 'X' ,
4793 'X' ,
4794 'N'
4795 );
4796
4797 /*Added for bug#12975007*/
4798 /*PAPAY_REP*/
4799 UPDATE PA_PAY_DIST_LINES
4800 SET TARGET_EXPENDITURE_ITEM_ID = l_exp_item_id
4801 WHERE ORGANIZATION_ID = Y. ORGANIZATION_ID
4802 AND PROJECT_ID = Y.PROJECT_ID
4803 AND TASK_ID = Y.TASK_ID
4804 AND EXPENDITURE_TYPE = Y.EXPENDITURE_TYPE
4805 AND EXPENDITURE_ITEM_DATE = Y.EXPENDITURE_ITEM_DATE
4806 --AND PROJECT_CURRENCY_CODE = Y.PROJECT_CURRENCY_CODE
4807 --AND PROJFUNC_CURRENCY_CODE = Y.PROJFUNC_CURRENCY_CODE
4808 AND DENOM_CURRENCY_CODE = Y.DENOM_CURRENCY_CODE
4809 AND ACCT_CURRENCY_CODE = Y.ACCT_CURRENCY_CODE ;
4810 /*PAPAY_REP*/
4811
4812 write_log(LOG,' Misc expenditure Item created -' || l_exp_item_id);
4813
4814
4815 -- write_log(LOG,'After Load Ei' );
4816
4817
4818 END LOOP; -- End Y loop .. Exp Items
4819 /* -- pa_transactions.InsItems(1,0,'PJ','PJ',i, l_status,'N');
4820
4821 UPDATE pa_expenditure_items_all
4822 SET interface_run_id = G_INTERFACE_RUN_ID
4823 ,COSTING_METHOD = 'ACTUAL'
4824 ,PAYROLL_ACCRUAL_FLAG = 'N'
4825 WHERE expenditure_id = l_expenditure_id ;
4826
4827 write_log(LOG,'After InsItems' );
4828
4829 -- pa_transactions.FlushEiTabs;
4830
4831 write_log(LOG,'Expenditure Items Created -' || i || ' Misc amounts processed '); */
4832 END LOOP; -- X Loop Expenditures
4833
4834 EXCEPTION
4835 WHEN EI_PROCESS_FAILED THEN
4836 write_log(Log, 'EI creation failed while tryin to create Misc items');
4837 RAISE REJECT_EMP_PAY;
4838
4839 WHEN OTHERS THEN
4840 write_log(Log, 'process_misc' || SQLERRM);
4841 RAISE REJECT_EMP_PAY;
4842 END process_misc;
4843
4844 /*
4845 Procedure : ALLOCATE_PAYROLL
4846 Type : Private
4847 Purpose :
4848 Stamp the Payroll cost on the TC if uncosted.
4849 Reverse and reate a new TC if already costed .
4850 */
4851
4852 PROCEDURE allocate_payroll IS
4853
4854 l_status VARCHAR2(100) := NULL;
4855 --paid_ei Eid_TabType;
4856 --paid_ei_for_update Eid_TabType;
4857 l_transfer_id NUMBER(15);
4858 l_reversal_id NUMBER(15);
4859 l_item_comment pa_expenditure_comments.expenditure_comment%TYPE;
4860 l_denom_raw_cost pa_expenditure_items_all.denom_raw_cost%TYPE;
4861 l_denom_orig_amount pa_expenditure_items_all.denom_raw_cost%TYPE;
4862
4863 EI_PROCESS_FAILED EXCEPTION;
4864
4865
4866 BEGIN
4867
4868
4869 FOR X IN ( SELECT DISTINCT
4870 SOURCE_EXPENDITURE_ITEM_ID ,
4871 EI.EXPENDITURE_ITEM_DATE,
4872 DENOM_CURRENCY_CODE,
4873 ORIG_CURR_CODE ,
4874 Sum(ORIG_AMOUNT) ORIG_AMOUNT ,
4875 SUM(DENOM_RAW_COST) DENOM_RAW_COST
4876 FROM
4877 PA_PAY_DIST_LINES DIST , PA_PAY_EXP_TEMP EI
4878 WHERE
4879 DIST.SOURCE_EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID AND
4880 DIST.SOURCE_EXPENDITURE_ITEM_ID IS NOT NULL AND
4881 DIST.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4882 DIST.PERSON_ID = G_PERSON_ID AND
4883 EI.COST_DISTRIBUTED_FLAG = 'N' AND
4884 EI.PAYROLL_ACCRUAL_FLAG is NULL
4885 GROUP BY
4886 DIST.SOURCE_EXPENDITURE_ITEM_ID ,
4887 EI.EXPENDITURE_ITEM_DATE,
4888 DIST.DENOM_CURRENCY_CODE,
4889 EI.ORIG_CURR_CODE
4890
4891 )
4892 LOOP
4893
4894 l_denom_orig_amount := NULL;
4895
4896 write_log(LOG,'DENOM_CURRENCY_CODE - '||X.DENOM_CURRENCY_CODE);
4897 write_log(LOG,'ORIG_CURR_CODE - '||X.ORIG_CURR_CODE);
4898 l_denom_raw_cost := X.DENOM_RAW_COST;
4899
4900 IF (X.ORIG_CURR_CODE IS NOT NULL AND (X.DENOM_CURRENCY_CODE = X.ORIG_CURR_CODE)) THEN
4901 l_denom_raw_cost := Nvl(X.ORIG_AMOUNT,0) + X.DENOM_RAW_COST;
4902 write_log(LOG,'X.ORIG_AMOUNT ' ||X.ORIG_AMOUNT) ;
4903 write_log(LOG,'X.DENOM_RAW_COST ' ||X.DENOM_RAW_COST) ;
4904
4905 write_log(LOG,'Case of carry over amounts ') ;
4906 write_log(LOG,'l_denom_raw_cost' || l_denom_raw_cost) ;
4907
4908
4909
4910
4911 ELSIF (X.ORIG_CURR_CODE IS NOT NULL AND (X.DENOM_CURRENCY_CODE <> X.ORIG_CURR_CODE)) THEN
4912 write_log(LOG,'Case of carry over amounts with conversion') ;
4913
4914 --Convert the Functional Amount on the original ei to denorm CURR on current ei
4915 get_orig_denom_amount(
4916 X.SOURCE_EXPENDITURE_ITEM_ID,
4917 X.EXPENDITURE_ITEM_DATE ,
4918 X.DENOM_CURRENCY_CODE ,
4919 X.ORIG_AMOUNT ,
4920 X.ORIG_CURR_CODE ,
4921 l_denom_orig_amount );
4922
4923 l_denom_raw_cost := X.DENOM_RAW_COST + Nvl(l_denom_orig_amount,0);
4924 END IF;
4925 write_log(LOG,'l_denom_raw_cost' || l_denom_raw_cost) ;
4926
4927
4928 UPDATE
4929 PA_EXPENDITURE_ITEMS_ALL EI
4930 SET DENOM_CURRENCY_CODE = X.DENOM_CURRENCY_CODE
4931 ,DENOM_RAW_COST = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_denom_raw_cost, X.DENOM_CURRENCY_CODE)
4932 ,REQUEST_ID = G_REQUEST_ID
4933 ,INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
4934 ,COSTING_METHOD = 'ACTUAL'
4935 ,PAYROLL_ACCRUAL_FLAG = 'N'
4936 WHERE
4937 EXPENDITURE_ITEM_ID = X.SOURCE_EXPENDITURE_ITEM_ID;
4938
4939 /*Added for bug#12975007*/
4940 /*PAPAY_REP*/
4941 UPDATE PA_PAY_DIST_LINES
4942 SET TARGET_EXPENDITURE_ITEM_ID = SOURCE_EXPENDITURE_ITEM_ID
4943 WHERE SOURCE_EXPENDITURE_ITEM_ID = X.SOURCE_EXPENDITURE_ITEM_ID;
4944
4945 write_log(LOG,'Case of uncosted EIs .. updated expendture item '|| X.SOURCE_EXPENDITURE_ITEM_ID);
4946
4947 END LOOP;
4948
4949
4950 FOR X IN (SELECT DISTINCT
4951 DIST.SOURCE_EXPENDITURE_ITEM_ID,DIST.DENOM_CURRENCY_CODE,
4952 TEMP.COST_DISTRIBUTED_FLAG,TEMP.PAYROLL_ACCRUAL_FLAG , TEMP.ORIG_CURR_CODE,
4953 TEMP.ORIG_AMOUNT,TEMP.EXPENDITURE_ITEM_DATE
4954 FROM
4955 PA_PAY_DIST_LINES DIST , PA_PAY_EXP_TEMP TEMP
4956 WHERE
4957
4958 DIST.SOURCE_EXPENDITURE_ITEM_ID IS NOT NULL AND
4959 DIST.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4960 DIST.PERSON_ID = G_PERSON_ID AND
4961 DIST.SOURCE_EXPENDITURE_ITEM_ID = TEMP.EXPENDITURE_ITEM_ID AND
4962 TEMP. PAYROLL_ACCRUAL_FLAG = 'Y')
4963 LOOP
4964
4965 -- Accrual EI , but was marked for re-calc.
4966 -- Mark them back as costed and copy amounts from CDL
4967 IF ((X.COST_DISTRIBUTED_FLAG = 'N') AND (X.PAYROLL_ACCRUAL_FLAG = 'Y')) THEN
4968 UPDATE PA_EXPENDITURE_ITEMS EI
4969 SET
4970 EI.COST_DISTRIBUTED_FLAG='Y',
4971 (EI.RAW_COST , EI.RAW_COST_RATE, EI.BURDEN_COST, EI.BURDEN_COST_RATE,
4972 EI.ACCT_RAW_COST, EI.ACCT_BURDENED_COST, EI.DENOM_RAW_COST,
4973 EI.DENOM_BURDENED_COST, EI.PROJECT_RAW_COST, EI.PROJECT_BURDENED_COST) =
4974 (
4975 SELECT
4976 CDL.AMOUNT,
4977 (CDL.DENOM_RAW_COST/DECODE(CDL.QUANTITY, NULL, 1,0, 1,
4978 CDL.QUANTITY)) ,
4979 CDL.BURDENED_COST ,
4980 (CDL.DENOM_BURDENED_COST/DECODE(CDL.QUANTITY, NULL,1,0,1,
4981 CDL.QUANTITY)) ,
4982 CDL.ACCT_RAW_COST ,
4983 CDL.ACCT_BURDENED_COST ,
4984 CDL.DENOM_RAW_COST ,
4985 CDL.DENOM_BURDENED_COST ,
4986 CDL.PROJECT_RAW_COST ,
4987 CDL.PROJECT_BURDENED_COST
4988 FROM
4989 PA_COST_DISTRIBUTION_LINES CDL
4990 WHERE
4991 CDL.EXPENDITURE_ITEM_ID =EI.EXPENDITURE_ITEM_ID AND
4992 CDL.LINE_TYPE ='R' AND
4993 NVL(CDL.REVERSED_FLAG,'N')='N' AND
4994 CDL.LINE_NUM_REVERSED IS NULL
4995 )
4996 WHERE
4997 EXPENDITURE_ITEM_ID =X.SOURCE_EXPENDITURE_ITEM_ID AND
4998 EI.COST_DISTRIBUTED_FLAG='N' AND
4999 EI.ADJUSTMENT_TYPE IS NOT NULL AND
5000 EI.COSTING_METHOD IS NOT NULL AND
5001 EXISTS
5002 (
5003 SELECT
5004 1
5005 FROM
5006 PA_COST_DISTRIBUTION_LINES CDL2
5007 WHERE
5008 CDL2.EXPENDITURE_ITEM_ID =EI.EXPENDITURE_ITEM_ID
5009 ) ;
5010
5011 END IF;
5012
5013
5014
5015
5016 write_log(Log, 'Calling backout for ' || X.SOURCE_EXPENDITURE_ITEM_ID);
5017 pa_adjustments.backoutitem(
5018 X_exp_item_id => X.SOURCE_EXPENDITURE_ITEM_ID,
5019 X_expenditure_id => NULL,
5020 X_adj_activity => 'BACKOUT_ACCRUAL',
5021 X_module => 'PAPAY',
5022 X_user => G_USER_ID ,
5023 X_login => G_LOGIN_ID ,
5024 X_status => l_status );
5025
5026 IF l_status <> 0 THEN
5027 write_log(LOG, 'Backout unseccesfull for eid=> ' || X.SOURCE_EXPENDITURE_ITEM_ID);
5028 RAISE EI_PROCESS_FAILED;
5029 ELSE
5030 write_log(LOG, 'Backout completed for eid=> ' || X.SOURCE_EXPENDITURE_ITEM_ID);
5031
5032 END IF;
5033
5034 -- Find the reversal expenditure item and create CDL for that .
5035 BEGIN
5036
5037 SELECT ei.expenditure_item_id INTO l_reversal_id
5038 FROM pa_expenditure_items ei
5039 WHERE ei.adjusted_expenditure_item_id= X.SOURCE_EXPENDITURE_ITEM_ID
5040 AND ei.net_zero_adjustment_flag = 'Y'
5041 AND ei.cost_distributed_flag='N';
5042
5043 CreateReverseCdl( X_exp_item_id => X.SOURCE_EXPENDITURE_ITEM_ID , --ei1
5044 X_backout_id => l_reversal_id, --e12
5045 X_user => G_USER_ID,
5046 X_status => l_status);
5047 IF l_status <> 0 THEN
5048 RAISE EI_PROCESS_FAILED;
5049 END IF;
5050 EXCEPTION
5051 WHEN No_Data_Found THEN
5052 NULL;
5053 END;
5054
5055 -- Check if there are any carry over amounts
5056 l_denom_orig_amount := 0;
5057
5058 IF (X.ORIG_CURR_CODE IS NOT NULL AND (X.DENOM_CURRENCY_CODE = X.ORIG_CURR_CODE)) THEN
5059 l_denom_orig_amount := X.ORIG_AMOUNT ;
5060
5061 ELSIF (X.ORIG_CURR_CODE IS NOT NULL AND (X.DENOM_CURRENCY_CODE <> X.ORIG_CURR_CODE)) THEN
5062 write_log(LOG,'Case of carry over amounts') ;
5063
5064 --Convert the Functional Amount on the original ei to denorm CURR on current ei
5065 get_orig_denom_amount(
5066 X.SOURCE_EXPENDITURE_ITEM_ID,
5067 X.EXPENDITURE_ITEM_DATE ,
5068 X.DENOM_CURRENCY_CODE ,
5069 X.ORIG_AMOUNT ,
5070 X.ORIG_CURR_CODE ,
5071 l_denom_orig_amount );
5072
5073 END IF;
5074 write_log(LOG,'l_denom_orig_amount -' || l_denom_orig_amount);
5075
5076 -- create the new EI ..
5077
5078
5079 l_transfer_id := pa_utils.GetNextEiId;
5080
5081
5082
5083 INSERT
5084 INTO
5085 PA_EXPENDITURE_ITEMS_ALL
5086 (
5087 EXPENDITURE_ITEM_ID ,
5088 LAST_UPDATE_DATE ,
5089 LAST_UPDATED_BY ,
5090 CREATION_DATE ,
5091 CREATED_BY ,
5092 EXPENDITURE_ID ,
5093 TASK_ID ,
5094 EXPENDITURE_ITEM_DATE ,
5095 EXPENDITURE_TYPE ,
5096 COST_DISTRIBUTED_FLAG ,
5097 REVENUE_DISTRIBUTED_FLAG ,
5098 BILLABLE_FLAG ,
5099 BILL_HOLD_FLAG ,
5100 QUANTITY ,
5101 /*NON_LABOR_RESOURCE ,
5102 ORGANIZATION_ID , */
5103 OVERRIDE_TO_ORGANIZATION_ID ,
5104 /*RAW_COST ,
5105 RAW_COST_RATE ,
5106 BURDEN_COST ,
5107 BURDEN_COST_RATE ,
5108 COST_DIST_REJECTION_CODE ,
5109 LABOR_COST_MULTIPLIER_NAME ,
5110 RAW_REVENUE ,
5111 BILL_RATE ,
5112 ACCRUED_REVENUE ,
5113 ACCRUAL_RATE ,
5114 ADJUSTED_REVENUE ,
5115 ADJUSTED_RATE ,
5116 BILL_AMOUNT ,
5117 FORECAST_REVENUE ,
5118 BILL_RATE_MULTIPLIER ,
5119 REV_DIST_REJECTION_CODE ,
5120 EVENT_NUM ,
5121 EVENT_TASK_ID ,
5122 BILL_JOB_ID ,
5123 BILL_JOB_BILLING_TITLE ,
5124 BILL_EMPLOYEE_BILLING_TITLE ,
5125 ADJUSTED_EXPENDITURE_ITEM_ID , */
5126 NET_ZERO_ADJUSTMENT_FLAG ,
5127 TRANSFERRED_FROM_EXP_ITEM_ID ,
5128 CONVERTED_FLAG ,
5129 LAST_UPDATE_LOGIN ,
5130 REQUEST_ID ,
5131 PROGRAM_APPLICATION_ID ,
5132 PROGRAM_ID ,
5133 PROGRAM_UPDATE_DATE ,
5134 ATTRIBUTE_CATEGORY ,
5135 ATTRIBUTE1 ,
5136 ATTRIBUTE2 ,
5137 ATTRIBUTE3 ,
5138 ATTRIBUTE4 ,
5139 ATTRIBUTE5 ,
5140 ATTRIBUTE6 ,
5141 ATTRIBUTE7 ,
5142 ATTRIBUTE8 ,
5143 ATTRIBUTE9 ,
5144 ATTRIBUTE10 ,
5145 /* COST_IND_COMPILED_SET_ID ,
5146 REV_IND_COMPILED_SET_ID ,
5147 INV_IND_COMPILED_SET_ID ,
5148 COST_BURDEN_DISTRIBUTED_FLAG ,
5149 IND_COST_DIST_REJECTION_CODE ,*/
5150 ORIG_TRANSACTION_REFERENCE ,
5151 TRANSACTION_SOURCE ,
5152 PROJECT_ID ,
5153 SOURCE_EXPENDITURE_ITEM_ID ,
5154 JOB_ID ,
5155 ORG_ID ,
5156 SYSTEM_LINKAGE_FUNCTION ,
5157 BURDEN_SUM_DEST_RUN_ID ,
5158 /* RECEIPT_CURRENCY_AMOUNT ,
5159 RECEIPT_CURRENCY_CODE ,
5160 RECEIPT_EXCHANGE_RATE ,*/
5161 DENOM_CURRENCY_CODE ,
5162 DENOM_RAW_COST ,
5163 /*DENOM_BURDENED_COST ,*/
5164 ACCT_CURRENCY_CODE ,
5165 /* ACCT_RATE_DATE ,
5166 ACCT_RATE_TYPE ,
5167 ACCT_EXCHANGE_RATE ,
5168 ACCT_RAW_COST ,
5169 ACCT_BURDENED_COST ,
5170 ACCT_EXCHANGE_ROUNDING_LIMIT ,*/
5171 PROJECT_CURRENCY_CODE ,
5172 /*PROJECT_RATE_DATE ,
5173 PROJECT_RATE_TYPE ,
5174 PROJECT_EXCHANGE_RATE ,
5175 DENORM_ID ,
5176 CC_CROSS_CHARGE_CODE , */
5177 CC_PRVDR_ORGANIZATION_ID ,
5178 CC_RECVR_ORGANIZATION_ID ,
5179 /* CC_REJECTION_CODE ,
5180 DENOM_TP_CURRENCY_CODE ,
5181 DENOM_TRANSFER_PRICE ,
5182 ACCT_TP_RATE_TYPE ,
5183 ACCT_TP_RATE_DATE ,
5184 ACCT_TP_EXCHANGE_RATE ,
5185 ACCT_TRANSFER_PRICE ,
5186 PROJACCT_TRANSFER_PRICE ,
5187 CC_MARKUP_BASE_CODE ,
5188 TP_BASE_AMOUNT ,
5189 CC_CROSS_CHARGE_TYPE ,*/
5190 RECVR_ORG_ID ,
5191 /*CC_BL_DISTRIBUTED_CODE ,
5192 CC_IC_PROCESSED_CODE ,
5193 TP_IND_COMPILED_SET_ID ,
5194 TP_BILL_RATE ,
5195 TP_BILL_MARKUP_PERCENTAGE ,
5196 TP_SCHEDULE_LINE_PERCENTAGE ,
5197 TP_RULE_PERCENTAGE ,
5198 CC_PRVDR_COST_RECLASS_CODE ,
5199 CRL_ASSET_CREATION_STATUS_CODE,
5200 CRL_ASSET_CREATION_REJ_CODE ,
5201 COST_JOB_ID ,
5202 TP_JOB_ID ,
5203 PROV_PROJ_BILL_JOB_ID ,
5204 COST_DIST_WARNING_CODE ,
5205 PROJECT_TP_RATE_DATE ,
5206 PROJECT_TP_RATE_TYPE ,
5207 PROJECT_TP_EXCHANGE_RATE ,
5208 PROJFUNC_TP_RATE_DATE ,
5209 PROJFUNC_TP_RATE_TYPE ,
5210 PROJFUNC_TP_EXCHANGE_RATE ,
5211 PROJFUNC_TRANSFER_PRICE ,
5212 BILL_TRANS_FORECAST_CURR_CODE ,
5213 BILL_TRANS_FORECAST_REVENUE ,
5214 PROJFUNC_REV_RATE_DATE ,
5215 PROJFUNC_REV_EXCHANGE_RATE ,
5216 PROJFUNC_COST_RATE_TYPE ,
5217 PROJFUNC_COST_RATE_DATE ,
5218 PROJFUNC_COST_EXCHANGE_RATE ,
5219 PROJECT_RAW_COST ,
5220 PROJECT_BURDENED_COST ,*/
5221 ASSIGNMENT_ID ,
5222 WORK_TYPE_ID ,
5223 /* PROJFUNC_RAW_REVENUE ,
5224 PROJECT_BILL_AMOUNT ,*/
5225 PROJFUNC_CURRENCY_CODE ,
5226 /* PROJECT_RAW_REVENUE ,
5227 PROJECT_TRANSFER_PRICE ,
5228 TP_AMT_TYPE_CODE ,
5229 BILL_TRANS_CURRENCY_CODE ,
5230 BILL_TRANS_RAW_REVENUE ,
5231 BILL_TRANS_BILL_AMOUNT ,
5232 BILL_TRANS_ADJUSTED_REVENUE ,
5233 REVPROC_CURRENCY_CODE ,
5234 REVPROC_RATE_TYPE ,
5235 REVPROC_RATE_DATE ,
5236 REVPROC_EXCHANGE_RATE ,
5237 INVPROC_CURRENCY_CODE ,
5238 INVPROC_RATE_TYPE ,
5239 INVPROC_RATE_DATE ,
5240 DISCOUNT_PERCENTAGE ,
5241 LABOR_MULTIPLIER ,
5242 AMOUNT_CALCULATION_CODE ,
5243 BILL_MARKUP_PERCENTAGE ,
5244 RATE_SOURCE_ID ,
5245 INVPROC_EXCHANGE_RATE ,
5246 INV_GEN_REJECTION_CODE ,
5247 PROJFUNC_BILL_AMOUNT ,
5248 PROJECT_REV_RATE_TYPE ,
5249 PROJECT_REV_RATE_DATE ,
5250 PROJECT_REV_EXCHANGE_RATE ,
5251 PROJFUNC_REV_RATE_TYPE ,
5252 PROJFUNC_INV_RATE_TYPE ,
5253 PROJFUNC_INV_RATE_DATE ,
5254 PROJFUNC_INV_EXCHANGE_RATE ,
5255 PROJECT_INV_RATE_TYPE ,
5256 PROJECT_INV_RATE_DATE ,
5257 PROJECT_INV_EXCHANGE_RATE ,
5258 PROJFUNC_FCST_RATE_TYPE ,
5259 PROJFUNC_FCST_RATE_DATE ,
5260 PROJFUNC_FCST_EXCHANGE_RATE ,
5261 PRVDR_ACCRUAL_DATE ,
5262 RECVR_ACCRUAL_DATE ,
5263 RATE_DISC_REASON_CODE ,
5264 POSTED_DENOM_BURDENED_COST ,
5265 POSTED_PROJECT_BURDENED_COST ,
5266 POSTED_PROJFUNC_BURDENED_COST ,
5267 POSTED_ACCT_BURDENED_COST ,
5268 ADJUSTMENT_TYPE ,
5269 CAPITAL_EVENT_ID ,
5270 PO_LINE_ID ,
5271 PO_PRICE_TYPE ,
5272 WIP_RESOURCE_ID ,
5273 INVENTORY_ITEM_ID ,
5274 UNIT_OF_MEASURE ,
5275 SRC_SYSTEM_LINKAGE_FUNCTION ,
5276 DOCUMENT_HEADER_ID ,
5277 DOCUMENT_DISTRIBUTION_ID ,
5278 DOCUMENT_LINE_NUMBER ,
5279 DOCUMENT_PAYMENT_ID ,
5280 VENDOR_ID ,
5281 DOCUMENT_TYPE ,
5282 DOCUMENT_DISTRIBUTION_TYPE ,*/
5283 HISTORICAL_FLAG ,
5284 LOCATION_ID ,
5285 PAY_ELEMENT_TYPE_ID ,
5286 COSTING_METHOD ,
5287 /* RATE_SOURCE_CODE */
5288 PAYROLL_ACCRUAL_FLAG,
5289 INTERFACE_RUN_ID
5290 )
5291 SELECT
5292 l_transfer_id ,
5293 G_RUN_DATE , -- LAST_UPDATE_DATE
5294 G_USER_ID , -- LAST_UPDATED_BY
5295 G_RUN_DATE , -- CREATION_DATE
5296 G_USER_ID , -- CREATED_BY
5297 EXPENDITURE_ID ,
5298 TASK_ID ,
5299 EXPENDITURE_ITEM_DATE ,
5300 EXPENDITURE_TYPE ,
5301 'N' , --COST_DISTRIBUTED_FLAG
5302 'N' ,--REVENUE_DISTRIBUTED_FLAG
5303 BILLABLE_FLAG ,
5304 BILL_HOLD_FLAG ,
5305 QUANTITY ,
5306 /*NON_LABOR_RESOURCE ,
5307 ORGANIZATION_ID , */
5308 OVERRIDE_TO_ORGANIZATION_ID ,
5309 /* RAW_COST ,
5310 RAW_COST_RATE ,
5311 BURDEN_COST ,
5312 BURDEN_COST_RATE ,
5313 COST_DIST_REJECTION_CODE ,
5314 LABOR_COST_MULTIPLIER_NAME ,
5315 RAW_REVENUE ,
5316 BILL_RATE ,
5317 ACCRUED_REVENUE ,
5318 ACCRUAL_RATE ,
5319 ADJUSTED_REVENUE ,
5320 ADJUSTED_RATE ,
5321 BILL_AMOUNT ,
5322 FORECAST_REVENUE ,
5323 BILL_RATE_MULTIPLIER ,
5324 REV_DIST_REJECTION_CODE ,
5325 EVENT_NUM ,
5326 EVENT_TASK_ID ,
5327 BILL_JOB_ID ,
5328 BILL_JOB_BILLING_TITLE ,
5329 BILL_EMPLOYEE_BILLING_TITLE ,
5330 ADJUSTED_EXPENDITURE_ITEM_ID , */
5331 'N' , --NET_ZERO_ADJUSTMENT_FLAG
5332 EXPENDITURE_ITEM_ID , --TRANSFERRED_FROM_EXP_ITEM_ID
5333 CONVERTED_FLAG ,
5334 G_LOGIN_ID ,--LAST_UPDATE_LOGIN
5335 G_REQUEST_ID ,--REQUEST_ID
5336 PROGRAM_APPLICATION_ID ,
5337 PROGRAM_ID ,
5338 G_RUN_DATE ,--PROGRAM_UPDATE_DATE
5339 ATTRIBUTE_CATEGORY ,
5340 ATTRIBUTE1 ,
5341 ATTRIBUTE2 ,
5342 ATTRIBUTE3 ,
5343 ATTRIBUTE4 ,
5344 ATTRIBUTE5 ,
5345 ATTRIBUTE6 ,
5346 ATTRIBUTE7 ,
5347 ATTRIBUTE8 ,
5348 ATTRIBUTE9 ,
5349 ATTRIBUTE10 ,
5350 /* COST_IND_COMPILED_SET_ID ,
5351 REV_IND_COMPILED_SET_ID ,
5352 INV_IND_COMPILED_SET_ID ,
5353 COST_BURDEN_DISTRIBUTED_FLAG ,
5354 IND_COST_DIST_REJECTION_CODE ,*/
5355 ORIG_TRANSACTION_REFERENCE ,
5356 TRANSACTION_SOURCE ,
5357 PROJECT_ID ,
5358 SOURCE_EXPENDITURE_ITEM_ID ,
5359 JOB_ID ,
5360 ORG_ID ,
5361 SYSTEM_LINKAGE_FUNCTION ,
5362 NULL ,--BURDEN_SUM_DEST_RUN_ID
5363 /*RECEIPT_CURRENCY_AMOUNT ,
5364 RECEIPT_CURRENCY_CODE ,
5365 RECEIPT_EXCHANGE_RATE , */
5366 dist.denom_CURRENCY_CODE , --DENOM_CURRENCY_CODE
5367 --dist.denom_raw_cost + l_denom_orig_amount , --DENOM_RAW_COST
5368 PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(dist.denom_raw_cost + l_denom_orig_amount,dist.denom_CURRENCY_CODE ) ,
5369 /* DENOM_BURDENED_COST ,*/
5370 ACCT_CURRENCY_CODE ,
5371 /* ACCT_RATE_DATE ,
5372 ACCT_RATE_TYPE ,
5373 ACCT_EXCHANGE_RATE ,
5374 ACCT_RAW_COST ,
5375 ACCT_BURDENED_COST ,
5376 ACCT_EXCHANGE_ROUNDING_LIMIT ,*/
5377 PROJECT_CURRENCY_CODE ,
5378 /* PROJECT_RATE_DATE ,
5379 PROJECT_RATE_TYPE ,
5380 PROJECT_EXCHANGE_RATE ,
5381 DENORM_ID ,
5382 CC_CROSS_CHARGE_CODE , */
5383 CC_PRVDR_ORGANIZATION_ID ,
5384 CC_RECVR_ORGANIZATION_ID ,
5385 /* CC_REJECTION_CODE ,
5386 DENOM_TP_CURRENCY_CODE ,
5387 DENOM_TRANSFER_PRICE ,
5388 ACCT_TP_RATE_TYPE ,
5389 ACCT_TP_RATE_DATE ,
5390 ACCT_TP_EXCHANGE_RATE ,
5391 ACCT_TRANSFER_PRICE ,
5392 PROJACCT_TRANSFER_PRICE ,
5393 CC_MARKUP_BASE_CODE ,
5394 TP_BASE_AMOUNT ,
5395 CC_CROSS_CHARGE_TYPE ,*/
5396 RECVR_ORG_ID ,
5397 /*CC_BL_DISTRIBUTED_CODE ,
5398 CC_IC_PROCESSED_CODE ,
5399 TP_IND_COMPILED_SET_ID ,
5400 TP_BILL_RATE ,
5401 TP_BILL_MARKUP_PERCENTAGE ,
5402 TP_SCHEDULE_LINE_PERCENTAGE ,
5403 TP_RULE_PERCENTAGE ,
5404 CC_PRVDR_COST_RECLASS_CODE ,
5405 CRL_ASSET_CREATION_STATUS_CODE,
5406 CRL_ASSET_CREATION_REJ_CODE ,
5407 COST_JOB_ID ,
5408 TP_JOB_ID ,
5409 PROV_PROJ_BILL_JOB_ID ,
5410 COST_DIST_WARNING_CODE ,
5411 PROJECT_TP_RATE_DATE ,
5412 PROJECT_TP_RATE_TYPE ,
5413 PROJECT_TP_EXCHANGE_RATE ,
5414 PROJFUNC_TP_RATE_DATE ,
5415 PROJFUNC_TP_RATE_TYPE ,
5416 PROJFUNC_TP_EXCHANGE_RATE ,
5417 PROJFUNC_TRANSFER_PRICE ,
5418 BILL_TRANS_FORECAST_CURR_CODE ,
5419 BILL_TRANS_FORECAST_REVENUE ,
5420 PROJFUNC_REV_RATE_DATE ,
5421 PROJFUNC_REV_EXCHANGE_RATE ,
5422 PROJFUNC_COST_RATE_TYPE ,
5423 PROJFUNC_COST_RATE_DATE ,
5424 PROJFUNC_COST_EXCHANGE_RATE ,
5425 PROJECT_RAW_COST ,
5426 PROJECT_BURDENED_COST ,*/
5427 ASSIGNMENT_ID ,
5428 WORK_TYPE_ID ,
5429 /*PROJFUNC_RAW_REVENUE ,
5430 PROJECT_BILL_AMOUNT ,*/
5431 PROJFUNC_CURRENCY_CODE ,
5432 /* PROJECT_RAW_REVENUE ,
5433 PROJECT_TRANSFER_PRICE ,
5434 TP_AMT_TYPE_CODE ,
5435 BILL_TRANS_CURRENCY_CODE ,
5436 BILL_TRANS_RAW_REVENUE ,
5437 BILL_TRANS_BILL_AMOUNT ,
5438 BILL_TRANS_ADJUSTED_REVENUE ,
5439 REVPROC_CURRENCY_CODE ,
5440 REVPROC_RATE_TYPE ,
5441 REVPROC_RATE_DATE ,
5442 REVPROC_EXCHANGE_RATE ,
5443 INVPROC_CURRENCY_CODE ,
5444 INVPROC_RATE_TYPE ,
5445 INVPROC_RATE_DATE ,
5446 DISCOUNT_PERCENTAGE ,
5447 LABOR_MULTIPLIER ,
5448 AMOUNT_CALCULATION_CODE ,
5449 BILL_MARKUP_PERCENTAGE ,
5450 RATE_SOURCE_ID ,
5451 INVPROC_EXCHANGE_RATE ,
5452 INV_GEN_REJECTION_CODE ,
5453 PROJFUNC_BILL_AMOUNT ,
5454 PROJECT_REV_RATE_TYPE ,
5455 PROJECT_REV_RATE_DATE ,
5456 PROJECT_REV_EXCHANGE_RATE ,
5457 PROJFUNC_REV_RATE_TYPE ,
5458 PROJFUNC_INV_RATE_TYPE ,
5459 PROJFUNC_INV_RATE_DATE ,
5460 PROJFUNC_INV_EXCHANGE_RATE ,
5461 PROJECT_INV_RATE_TYPE ,
5462 PROJECT_INV_RATE_DATE ,
5463 PROJECT_INV_EXCHANGE_RATE ,
5464 PROJFUNC_FCST_RATE_TYPE ,
5465 PROJFUNC_FCST_RATE_DATE ,
5466 PROJFUNC_FCST_EXCHANGE_RATE ,
5467 PRVDR_ACCRUAL_DATE ,
5468 RECVR_ACCRUAL_DATE ,
5469 RATE_DISC_REASON_CODE ,
5470 POSTED_DENOM_BURDENED_COST ,
5471 POSTED_PROJECT_BURDENED_COST ,
5472 POSTED_PROJFUNC_BURDENED_COST ,
5473 POSTED_ACCT_BURDENED_COST ,
5474 ADJUSTMENT_TYPE ,
5475 CAPITAL_EVENT_ID ,
5476 PO_LINE_ID ,
5477 PO_PRICE_TYPE ,
5478 WIP_RESOURCE_ID ,
5479 INVENTORY_ITEM_ID ,
5480 UNIT_OF_MEASURE ,
5481 SRC_SYSTEM_LINKAGE_FUNCTION ,
5482 DOCUMENT_HEADER_ID ,
5483 DOCUMENT_DISTRIBUTION_ID ,
5484 DOCUMENT_LINE_NUMBER ,
5485 DOCUMENT_PAYMENT_ID ,
5486 VENDOR_ID ,
5487 DOCUMENT_TYPE ,
5488 DOCUMENT_DISTRIBUTION_TYPE ,*/
5489 'N' ,--HISTORICAL_FLAG
5490 LOCATION_ID ,
5491 PAY_ELEMENT_TYPE_ID ,
5492 'ACTUAL' ,
5493 /* RATE_SOURCE_CODE , */
5494 'N',
5495 G_INTERFACE_RUN_ID
5496 FROM
5497 PA_EXPENDITURE_ITEMS_ALL ei,
5498 ( SELECT source_expenditure_item_id seid ,denom_CURRENCY_CODE,Sum(denom_raw_cost) denom_raw_cost, Sum(denom_raw_cost) acct_raw_cost FROM
5499 pa_pay_dist_lines
5500 WHERE
5501 source_expenditure_item_id IS NOT NULL
5502 AND interface_run_id = G_INTERFACE_RUN_ID
5503 AND person_id = G_PERSON_ID
5504 AND source_expenditure_item_id = X.SOURCE_EXPENDITURE_ITEM_ID
5505 GROUP BY source_expenditure_item_id ,denom_CURRENCY_CODE
5506 ) dist
5507 WHERE dist.seid = ei.expenditure_item_id ;
5508
5509 write_log(Log, 'New item inserted ' || l_transfer_id);
5510
5511 /*Added for bug#12975007*/
5512 /*PAPAY_REP*/
5513 UPDATE PA_PAY_DIST_LINES
5514 SET TARGET_EXPENDITURE_ITEM_ID = l_transfer_id
5515 WHERE SOURCE_EXPENDITURE_ITEM_ID = X.SOURCE_EXPENDITURE_ITEM_ID;
5516
5517 -- Copy the comments
5518 BEGIN
5519 SELECT
5520 ec.expenditure_comment
5521 INTO
5522 l_item_comment
5523 FROM
5524 pa_expenditure_comments ec
5525 WHERE
5526 ec.expenditure_item_id = X.SOURCE_EXPENDITURE_ITEM_ID;
5527 EXCEPTION
5528 WHEN NO_DATA_FOUND THEN
5529 NULL;
5530 END;
5531
5532 IF ( l_item_comment IS NOT NULL ) THEN
5533 pa_transactions.InsItemComment
5534 ( X_ei_id => l_transfer_id ,
5535 X_ei_comment => l_item_comment ,
5536 X_user => G_USER_ID ,
5537 X_login => G_LOGIN_ID ,
5538 X_status => l_status );
5539
5540 IF l_status <> 0 THEN
5541 RAISE EI_PROCESS_FAILED;
5542 END IF;
5543 END IF;
5544
5545 -- Insert the Audit record
5546 PA_ADJUSTMENTS.InsAuditRec( l_transfer_id ,
5547 'BACKOUT_ACCRUAL' ,
5548 'PAPAY', --X_module
5549 G_USER_ID,
5550 G_LOGIN_ID ,
5551 l_status,
5552 G_REQUEST_ID ,
5553 G_PROGRAM_ID ,
5554 G_PROG_APPL_ID ,
5555 G_RUN_DATE );
5556 write_log(LOG, 'Audit record Inserted');
5557
5558 UPDATE pa_expenditure_items
5559 SET interface_run_id = G_INTERFACE_RUN_ID
5560 ,COSTING_METHOD = 'ACTUAL'
5561 ,PAYROLL_ACCRUAL_FLAG = 'N'
5562 WHERE expenditure_id = l_transfer_id ;
5563
5564 END LOOP;
5565
5566 /*
5567
5568 UPDATE PA_EXPENDITURE_ITEMS_ALL EI2
5569 SET EI2.DENOM_RAW_COST = 0 ,
5570 EI2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5571 WHERE EI2.EXPENDITURE_ITEM_ID IN
5572 ( SELECT EI.EXPENDITURE_ITEM_ID
5573 FROM PA_EXPENDITURE_ITEMS_ALL EI ,
5574 PA_EXPENDITURES_ALL E,
5575 PA_PAY_AUDIT AUD
5576 WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5577 AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5578 AND EI.EXPENDITURE_ID = E.EXPENDITURE_ID
5579 AND Trunc(EXPENDITURE_ITEM_DATE) BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND Trunc( Nvl(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
5580 AND EI.DENOM_RAW_COST IS NULL
5581 AND get_cost_method(EI.expenditure_item_id,EI.expenditure_item_date) = 'ACTUAL'
5582 AND NVL(EI.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'Y'
5583 ) ;
5584
5585
5586 */
5587
5588 /* Start changes for bug#12975007*/
5589
5590 /* Update all netzero OTL EI's between pay start and dates with zero payroll cost */
5591
5592 UPDATE PA_EXPENDITURE_ITEMS Ei2 SET
5593 (EI2.DENOM_RAW_COST,Ei2.INTERFACE_RUN_ID,Ei2.
5594 COSTING_METHOD,Ei2.PAYROLL_ACCRUAL_FLAG,Ei2.REQUEST_ID) = (SELECT
5595 NVL((-1*EI.DENOM_RAW_COST),0),
5596 G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID FROM
5597 PA_EXPENDITURE_ITEMS Ei,
5598 PA_EXPENDITURES E,
5599 PA_PAY_AUDIT AUD
5600 WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5601 AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5602 AND EI2.EXPENDITURE_ID = E.EXPENDITURE_ID
5603 AND EI2.EXPENDITURE_ITEM_DATE BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
5604 AND Trunc( NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
5605 AND EI.EXPENDITURE_ITEM_ID = nvl(EI2.ADJUSTED_EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_ID)
5606 AND PA_PAY_INTERFACE.GET_COST_METHOD1(EI2.EXPENDITURE_ID,EI2.JOB_ID,EI2.ORG_ID,
5607 NVL(EI2.OVERRIDE_to_ORGANIZATION_ID
5608 ,E.INCURRED_BY_ORGANIZATION_ID),EI2.EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_DATE) = 'ACTUAL'
5609 AND EXISTS (
5610 SELECT 1 FROM PA_EXPENDITURE_ITEMS EI3
5611 WHERE EI3.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5612 AND SUBSTR(EI3.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI3.ORIG_TRANSACTION_REFERENCE,':')-1) =
5613 SUBSTR(EI.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI.ORIG_TRANSACTION_REFERENCE,':')-1)
5614 ))
5615 WHERE NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'Y'
5616 AND EI2.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT')
5617 AND EI2.TRANSACTIOn_SOURCE ='ORACLE TIME AND LABOR'
5618 AND EI2.DENOM_RAW_COST IS NULL;
5619
5620 /* End changes for bug#12975007*/
5621
5622 /* Bug 12779047 pdate all netzero EI between pay start and dates with zero payroll cost */
5623
5624 /* Start changes for bug#12975007*/
5625
5626 FOR X IN (SELECT EI2.EXPENDITURE_ITEM_ID,EI2.TRANSACTION_SOURCE,EI2.ADJUSTED_EXPENDITURE_ITEM_ID
5627 FROM PA_EXPENDITURE_ITEMS EI2 ,
5628 PA_EXPENDITURES E,
5629 PA_PAY_AUDIT AUD
5630 WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5631 AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5632 AND EI2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5633 AND EI2.EXPENDITURE_ID = E.EXPENDITURE_ID
5634 AND EI2.EXPENDITURE_ITEM_DATE BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND Trunc( Nvl(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
5635 AND pa_pay_interface.get_cost_method(EI2.EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_DATE) = 'ACTUAL'
5636 AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
5637 AND EI2.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT')
5638 AND NVL(EI2.TRANSACTION_SOURCE,' ') <>'ORACLE TIME AND LABOR'
5639 )
5640
5641 LOOP
5642 --{
5643 UPDATE PA_EXPENDITURE_ITEMS EI2
5644 SET (EI2.DENOM_RAW_COST,EI2.INTERFACE_RUN_ID,
5645 EI2.COSTING_METHOD,
5646 EI2.PAYROLL_ACCRUAL_FLAG,
5647 EI2.REQUEST_ID) = (SELECT
5648 /*(NVL ( ( SELECT (-1*EI1.DENOM_RAW_COST) FROM PA_EXPENDITURE_ITEMS EI1 WHERE
5649 EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),0 ) ),G_INTERFACE_RUN_ID,'ACTUAL',
5650 (NVL ( ( SELECT EI1.PAYROLL_ACCRUAL_FLAG FROM
5651 PA_EXPENDITURE_ITEMS EI1 WHERE
5652 EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),'N' )),
5653 --'N',
5654 G_REQUEST_ID FROM DUAL)*/
5655 NVL((-1*EI1.DENOM_RAW_COST),0),G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI1.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID
5656 FROM PA_EXPENDITURE_ITEMS EI1
5657 WHERE
5658 EI1.EXPENDITURE_ITEM_ID=NVL(EI2.ADJUSTED_EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_ID))
5659 WHERE EI2.EXPENDITURE_ITEM_ID IN
5660 (SELECT EXPENDITURE_ITEM_ID
5661 FROM PA_EXPENDITURE_ITEMS Ei3
5662 ,PA_EXPENDITURES E,
5663 PA_PAY_AUDIT AUD
5664 WHERE EI3.EXPENDITURE_ITEM_ID IN
5665 (SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
5666 CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=EXPENDITURE_ITEM_ID
5667 UNION
5668 SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
5669 CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=ADJUSTED_EXPENDITURE_ITEM_ID
5670 )
5671 AND E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5672 AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5673 AND EI3.EXPENDITURE_ID = E.EXPENDITURE_ID
5674 AND EI3.EXPENDITURE_ITEM_DATE BETWEEN
5675 Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
5676 AND Trunc( NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
5677 AND
5678 PA_PAY_INTERFACE.GET_COST_METHOD1(EI3.EXPENDITURE_ID,EI3.JOB_ID,EI3.ORG_ID,
5679 NVL(EI3.OVERRIDE_to_ORGANIZATION_ID
5680 ,E.INCURRED_BY_ORGANIZATION_ID),EI3.EXPENDITURE_ITEM_ID,EI3.EXPENDITURE_ITEM_DATE)
5681 = 'ACTUAL'
5682 AND Ei3.INTERFACE_RUN_ID IS NULL
5683 AND NVL(EI3.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'Y'
5684 AND EI3.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT')
5685 AND NVL(EI3.TRANSACTION_SOURCE,' ') <>'ORACLE TIME AND LABOR'
5686 AND EI3.DENOM_RAW_COST IS NULL
5687 );
5688
5689 write_log (LOG, 'Net Zero raleted Ei with zero Payroll Cost -' || X.EXPENDITURE_ITEM_ID );
5690
5691 /* End changes for bug#12975007*/
5692
5693 --}
5694 END LOOP;
5695
5696
5697
5698 write_log (DEBUG, 'No of adjustment ei updated' || SQL%rowcount);
5699
5700
5701 EXCEPTION
5702 WHEN EI_PROCESS_FAILED THEN
5703 write_log(Log, 'EI creation failed while trying to allocate payroll to timecards of '|| G_PERSON_ID);
5704
5705 RAISE REJECT_EMP_PAY;
5706 WHEN OTHERS THEN
5707 write_log(Log, 'Processing timecards ' || SQLERRM);
5708 RAISE REJECT_EMP_PAY;
5709 END allocate_payroll;
5710
5711
5712
5713
5714 /* Not used any more
5715 PROCEDURE cleanup_distributions IS
5716 BEGIN
5717
5718 DELETE FROM pa_pay_dist_lines
5719 WHERE interface_run_id = G_INTERFACE_RUN_ID AND
5720 person_id = G_PERSON_ID ;
5721
5722 END cleanup_distributions;
5723 */
5724
5725 /*
5726 Procedure : Primary_rejection
5727 Type : Private
5728 Purpose :
5729 Used in PRC: Process Payroll Actuals Exception Report
5730 to get the Primary Rejection Code for a pay element.
5731
5732 */
5733
5734 FUNCTION Primary_rejection(
5735 p_interface_run_id IN NUMBER ,
5736 p_int_ext_indicator IN VARCHAR,
5737 p_person_id IN NUMBER ,
5738 p_pay_element_type_id IN NUMBER ,
5739 p_pay_element_type_code IN VARCHAR)
5740 RETURN VARCHAR2
5741 IS
5742 l_rejection_code VARCHAR2(30);
5743 l_priority NUMBER;
5744 BEGIN
5745 SELECT REJECTION_CODE
5746 INTO
5747 l_rejection_code
5748 FROM pa_pay_rejection_priority
5749 WHERE PRIORITY = (
5750 SELECT
5751 MIN(pprp.priority)
5752 FROM
5753 PA_PAY_REJECTIONS_ALL ppr ,
5754 pa_pay_rejection_priority pprp
5755 WHERE
5756 ppr.interface_run_id = p_interface_run_id AND
5757 ppr.person_id = p_person_id AND
5758 ( (p_int_ext_indicator = 'INT' AND (ppr.pay_element_type_id = p_pay_element_type_id )
5759 )OR
5760 ( p_int_ext_indicator = 'EXT' AND (ppr.pay_element_type_code = p_pay_element_type_code)
5761 )
5762 ) AND
5763 ppr.rejection_code = pprp.rejection_code
5764 GROUP BY
5765 ppr.pay_element_type_id);
5766 RETURN l_rejection_code;
5767 EXCEPTION
5768 WHEN OTHERS THEN
5769 RETURN NULL;
5770 END Primary_rejection;
5771
5772
5773 /*
5774 Procedure : ADDITIONAL_REJECTIONS
5775 Type : Private
5776 Purpose :
5777 Used in PRC: Process Payroll Actuals Exception Report
5778 to get the additional Rejection Code for a pay element.
5779
5780 */
5781
5782 FUNCTION ADDITIONAL_REJECTIONS(
5783 P_INTERFACE_RUN_ID IN NUMBER ,
5784 p_int_ext_indicator IN VARCHAR,
5785 P_PERSON_ID IN NUMBER ,
5786 P_PAY_ELEMENT_TYPE_ID IN NUMBER ,
5787 p_pay_element_type_code IN VARCHAR)
5788 RETURN VARCHAR2
5789 IS
5790 L_REJECTION_CODE VARCHAR2(1000);
5791 L_COUNTER NUMBER := 0;
5792 BEGIN
5793 FOR X IN
5794 (
5795 SELECT
5796 PPR.REJECTION_CODE,
5797 MAX(PPRP.PRIORITY) PRTY
5798 FROM
5799 PA_PAY_REJECTIONS_ALL PPR ,
5800 PA_PAY_REJECTION_PRIORITY PPRP
5801 WHERE
5802 PPR.INTERFACE_RUN_ID = P_INTERFACE_RUN_ID AND
5803 PPR.PERSON_ID = P_PERSON_ID AND
5804 ( (p_int_ext_indicator = 'INT' AND (ppr.pay_element_type_id = p_pay_element_type_id )
5805 )OR
5806 (p_int_ext_indicator = 'EXT' AND (ppr.pay_element_type_code = p_pay_element_type_code)
5807 )
5808 ) AND
5809 PPR.REJECTION_CODE = PPRP.REJECTION_CODE
5810 GROUP BY
5811 PPR.REJECTION_CODE
5812 ORDER BY
5813 PRTY ASC
5814 )
5815 LOOP
5816 L_COUNTER := L_COUNTER +1;
5817 IF (L_COUNTER >1) THEN
5818
5819 IF L_REJECTION_CODE is not null THEN
5820 L_REJECTION_CODE := L_REJECTION_CODE || ',';
5821 END IF;
5822
5823 L_REJECTION_CODE := L_REJECTION_CODE || X.REJECTION_CODE;
5824 END IF;
5825 END LOOP;
5826 RETURN L_REJECTION_CODE;
5827 EXCEPTION
5828 WHEN OTHERS THEN
5829 RETURN NULL;
5830 END ADDITIONAL_REJECTIONS;
5831
5832 /*
5833 Procedure : MARK_PROCESSED
5834 Type : Private
5835 Purpose :
5836 if the interface process is sucessfull,
5837 call the payroll api to mark the record as processed.
5838
5839 */
5840
5841
5842 PROCEDURE mark_processed
5843 IS
5844 l_status VARCHAR2(30);
5845 l_int_ext_flag pa_pay_audit.INT_EXT_INDICATOR%TYPE;
5846 BEGIN
5847
5848
5849 SELECT INT_EXT_INDICATOR
5850 INTO
5851 l_int_ext_flag
5852 FROM pa_pay_audit_all
5853 WHERE INTERFACE_RUN_ID = G_INTERFACE_RUN_ID ;
5854
5855 IF l_int_ext_flag = 'INT' THEN
5856
5857 write_log(Log, 'Marking payroll record after the processing is over ' );
5858 FOR X IN
5859 (
5860 SELECT
5861 PAYROLL_ACTION_ID ,
5862 ASSIGNMENT_ACTION_ID ,
5863 SRC.PAY_ELEMENT_TYPE_ID ,
5864 'Y' STATUS
5865 FROM
5866 PA_PAY_AUDIT AUD ,
5867 PA_PAY_INTERFACE_TEMP TEMP ,
5868 PA_PAY_SOURCE_AMOUNTS SRC
5869 WHERE
5870 AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
5871 AUD.INTERFACE_RUN_ID = SRC.INTERFACE_RUN_ID AND
5872 TEMP.PERSON_ID = SRC.PERSON_ID AND
5873 SRC.PERSON_ID = G_PERSON_ID AND
5874 TEMP.ASSIGNMENT_ID = SRC.ASSIGNMENT_ID AND
5875 TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND
5876 TRUNC(NVL(TEMP.SOURCE_END_DATE,TEMP.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))/* bug 12690224*/
5877 UNION
5878 SELECT
5879 PAYROLL_ACTION_ID ,
5880 ASSIGNMENT_ACTION_ID ,
5881 TEMP.ELEMENT_TYPE_ID PAY_ELEMENT_TYPE_ID ,
5882 'Z' STATUS
5883 FROM
5884 PA_PAY_AUDIT AUD ,
5885 PA_PAY_INTERFACE_TEMP TEMP
5886 WHERE
5887 AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
5888 TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND
5889 TRUNC(NVL(TEMP.SOURCE_END_DATE,TEMP.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE)) AND /* bug 12690224*/
5890 TEMP.PERSON_ID = G_PERSON_ID AND
5891 TEMP.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
5892 NOT EXISTS
5893 (
5894 SELECT
5895 'Y'
5896 FROM
5897 PA_PAY_SOURCE_AMOUNTS PPSA
5898 WHERE
5899 PPSA.PERSON_ID = G_PERSON_ID AND
5900 PPSA.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
5901 PPSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
5902 PPSA.PAY_ELEMENT_TYPE_ID = TEMP.ELEMENT_TYPE_ID
5903 ))
5904 LOOP
5905 pay_core_utils.update_prj_flag (X.pay_element_type_id , X.assignment_action_id, X.STATUS, l_status ) ;
5906 write_log(Log, 'Marking payroll record ' || X.pay_element_type_id || '-'||X.assignment_action_id || '-' || X.STATUS);
5907
5908 END LOOP;
5909
5910
5911 ELSIF l_int_ext_flag = 'EXT' THEN
5912
5913 -- Third party payrolls .. marking items as processed
5914
5915 Update PA_PAY_EXT_INTERFACE_DETAILS
5916 set TRANSFER_STATUS_FLAG = 'A'
5917 where INTERFACE_LINE_ID in
5918 (
5919
5920 SELECT
5921 EXT.INTERFACE_LINE_ID
5922 FROM
5923 PA_PAY_AUDIT AUD ,
5924 PA_PAY_EXT_INTERAFACE_V EXT ,
5925 PA_PAY_SOURCE_AMOUNTS SRC
5926 WHERE
5927 AUD.INT_EXT_INDICATOR = 'EXT' AND
5928 AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
5929 AUD.INTERFACE_RUN_ID = SRC.INTERFACE_RUN_ID AND
5930 SRC.PERSON_ID = G_PERSON_ID AND
5931 SRC.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
5932 EXT.PERSON_ID = SRC.PERSON_ID AND
5933 EXT.ASSIGNMENT_ID = SRC.ASSIGNMENT_ID AND
5934 Trunc(EXT.TIME_PERIOD_START_DATE) = Trunc(AUD.PAY_PERIOD_START_DATE )AND
5935 Trunc(EXT.TIME_PERIOD_END_DATE) = Trunc(AUD.PAY_PERIOD_END_DATE) AND
5936 EXT.PAY_ELEMENT_TYPE_CODE = SRC.PAY_ELEMENT_TYPE_CODE
5937 );
5938
5939 write_log (LOG, 'No of lines processed marked to Y ' || SQL%rowcount);
5940
5941
5942 UPDATE PA_PAY_EXT_INTERFACE_DETAILS EXTD
5943 SET TRANSFER_STATUS_FLAG = 'X'
5944 WHERE
5945 INTERFACE_LINE_ID IN
5946 (
5947 SELECT
5948 EXT.INTERFACE_LINE_ID
5949 FROM
5950 PA_PAY_EXT_INTERAFACE_V EXT,
5951 PA_PAY_AUDIT AUD
5952 WHERE
5953 AUD.INT_EXT_INDICATOR = 'EXT' AND
5954 AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
5955 Trunc(EXT.TIME_PERIOD_START_DATE) = Trunc(AUD.PAY_PERIOD_START_DATE) AND
5956 Trunc(EXT.TIME_PERIOD_END_DATE) = Trunc(AUD.PAY_PERIOD_END_DATE) AND
5957 NOT EXISTS
5958 (
5959 SELECT
5960 PAY_ELEMENT_TYPE_CODE
5961 FROM
5962 PA_PAY_SOURCE_AMOUNTS SRC
5963 WHERE
5964 SRC.INTERFACE_RUN_ID = AUD.INTERFACE_RUN_ID AND
5965 SRC.PERSON_ID = G_PERSON_ID AND
5966 SRC.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
5967 SRC.PAY_ELEMENT_TYPE_CODE = EXT.PAY_ELEMENT_TYPE_CODE
5968 ));
5969 write_log (LOG, 'No of lines missing setup marked to X ' || SQL%rowcount);
5970 END IF;
5971 END mark_processed;
5972
5973 /*
5974 Procedure : INS_PAY_PROJ
5975 Type : Private
5976 Purpose :
5977 Make an entry in PA_PAY_PROJ_STATUS_ALL
5978 for each employee assignment that got processed.
5979
5980 */
5981
5982 PROCEDURE ins_pay_proj
5983 IS
5984 BEGIN
5985
5986 write_log (LOG,'Insert Pay Proj record ' );
5987 INSERT
5988 INTO
5989 PA_PAY_PROJ_STATUS_ALL
5990 (
5991 INTERFACE_RUN_ID ,
5992 PERSON_ID ,
5993 ASSIGNMENT_ID ,
5994 PAYROLL_STATUS_FLAG ,
5995 PROJECT_ROLLBACK_STATUS_IND ,
5996 LAST_UPDATE_DATE ,
5997 LAST_UPDATED_BY ,
5998 CREATION_DATE ,
5999 CREATED_BY ,
6000 LAST_UPDATE_LOGIN ,
6001 REQUEST_ID ,
6002 ASSIGNMENT_ACTION_ID
6003 )
6004 SELECT DISTINCT
6005 G_INTERFACE_RUN_ID,
6006 G_PERSON_ID ,
6007 G_ASSIGNMENT_ID ,
6008 NULL ,
6009 NULL ,
6010 G_RUN_DATE ,
6011 G_USER_ID ,
6012 G_RUN_DATE ,
6013 G_USER_ID ,
6014 G_LOGIN_ID,
6015 G_REQUEST_ID ,
6016 ASSIGNMENT_ACTION_ID
6017 FROM
6018 PA_PAY_INTERFACE_TEMP TEMP ,
6019 PA_PAY_AUDIT AUD
6020 WHERE
6021 AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
6022 TEMP.PAYROLL_ACTION_ID = AUD.BATCH_ID AND
6023 TEMP.PERSON_ID = G_PERSON_ID AND
6024 TEMP.ASSIGNMENT_ID = G_ASSIGNMENT_ID AND
6025 TEMP.TIME_PERIOD_ID = AUD.TIME_PERIOD_ID ;
6026 END ins_pay_proj;
6027
6028
6029
6030
6031
6032 /* ========================================================================
6033 PROCEDURE CreateReverseCdl:Creates the reversal cdl for backout items
6034 and mark the CDF on pa_expenditure_items_all
6035 ========================================================================*/
6036
6037 PROCEDURE CreateReverseCdl ( X_exp_item_id IN NUMBER,
6038 X_backout_id IN NUMBER,
6039 X_user IN NUMBER,
6040 X_status OUT NOCOPY NUMBER)
6041 IS
6042 p_amount pa_cost_distribution_lines.amount%TYPE;
6043 p_dr_ccid pa_cost_distribution_lines.dr_code_combination_id%TYPE;
6044 p_cr_ccid pa_cost_distribution_lines.cr_code_combination_id%TYPE;
6045 p_transfer_status_code pa_cost_distribution_lines.transfer_status_code%TYPE;
6046 p_quantity pa_cost_distribution_lines.quantity%TYPE;
6047 p_billable_flag pa_cost_distribution_lines.billable_flag%TYPE;
6048 p_request_id pa_cost_distribution_lines.request_id%TYPE;
6049 p_program_application_id pa_cost_distribution_lines.program_application_id%TYPE;
6050 p_program_id pa_cost_distribution_lines.program_id%TYPE;
6051 p_program_update_date pa_cost_distribution_lines.program_update_date%TYPE;
6052 p_pa_date pa_cost_distribution_lines.pa_date%TYPE;
6053 p_recvr_pa_date pa_cost_distribution_lines.pa_date%TYPE; /**CBGA**/
6054 p_gl_date pa_cost_distribution_lines.gl_date%TYPE;
6055 p_transferred_date pa_cost_distribution_lines.transferred_date%TYPE;
6056 p_transfer_rejection_reason pa_cost_distribution_lines.transfer_rejection_reason%TYPE;
6057 p_line_type pa_cost_distribution_lines.line_type%TYPE;
6058 p_ind_complied_set_id pa_cost_distribution_lines.ind_compiled_set_id%TYPE;
6059 p_burdened_cost pa_cost_distribution_lines.burdened_cost%TYPE;
6060 p_line_num_reversed pa_cost_distribution_lines.line_num_reversed%TYPE;
6061 p_reversed_flag pa_cost_distribution_lines.reversed_flag%TYPE;
6062 p_cdlsr1 pa_cost_distribution_lines.system_reference1%TYPE;
6063 p_cdlsr2 pa_cost_distribution_lines.system_reference2%TYPE;
6064 p_cdlsr3 pa_cost_distribution_lines.system_reference3%TYPE;
6065 p_denom_currency_code pa_cost_distribution_lines.denom_currency_code%TYPE;
6066 p_denom_raw_cost pa_cost_distribution_lines.denom_raw_cost%TYPE;
6067 p_denom_burdened_cost pa_cost_distribution_lines.denom_burdened_cost%TYPE;
6068 p_acct_currency_code pa_cost_distribution_lines.acct_currency_code%TYPE;
6069 p_acct_rate_date pa_cost_distribution_lines.acct_rate_date%TYPE;
6070 p_acct_rate_type pa_cost_distribution_lines.acct_rate_type%TYPE;
6071 p_acct_exchange_rate pa_cost_distribution_lines.acct_exchange_rate%TYPE;
6072 p_acct_raw_cost pa_cost_distribution_lines.acct_raw_cost%TYPE;
6073 p_acct_burdened_cost pa_cost_distribution_lines.acct_burdened_cost%TYPE;
6074 p_project_currency_code pa_cost_distribution_lines.project_currency_code%TYPE;
6075 p_project_rate_date pa_cost_distribution_lines.project_rate_date%TYPE;
6076 p_project_rate_type pa_cost_distribution_lines.project_rate_type%TYPE;
6077 p_project_exchange_rate pa_cost_distribution_lines.project_exchange_rate%TYPE;
6078 p_project_id pa_cost_distribution_lines.project_id%TYPE;
6079 p_task_id pa_cost_distribution_lines.task_id%TYPE;
6080 p_parent_adjusted_id pa_expenditure_items.adjusted_expenditure_item_id%TYPE;
6081 p_parent_transferred_id pa_expenditure_items.transferred_from_exp_item_id%TYPE;
6082 p_gl_accounted_flag pa_transaction_sources.gl_accounted_flag%TYPE;
6083 p_transaction_source pa_transaction_sources.transaction_source%TYPE;
6084 l_si_assets_addition_flag pa_cost_distribution_lines.si_assets_addition_flag%TYPE ;
6085 p_err_code NUMBER;
6086 p_err_stage VARCHAR2(1000);
6087 p_err_stack VARCHAR2(1000);
6088 e_cdl_error EXCEPTION;
6089 -- Start EPP Changes
6090 p_pa_period_name VARCHAR2(15);
6091 p_gl_period_name VARCHAR2(15);
6092 p_recvr_gl_date DATE;
6093 p_recvr_gl_period_name VARCHAR2(15);
6094 p_recvr_pa_period_name VARCHAR2(15);
6095 -- End EPP Changes
6096 -- Start Project Currency/ EI Attribute Changes
6097 p_projfunc_currency_code VARCHAR2(15);
6098 p_projfunc_cost_rate_type VARCHAR2(30);
6099 p_projfunc_cost_rate_date date;
6100 p_projfunc_cost_exchange_rate NUMBER;
6101 p_work_type_id NUMBER;
6102 p_project_raw_cost NUMBER;
6103 p_project_burdened_cost NUMBER;
6104 -- End Project Currency/ EI Attribute Changes
6105 -- AP Discounts
6106 p_cdlsr4 pa_cost_distribution_lines.system_reference4%TYPE;
6107 p_cdlsr5 pa_cost_distribution_lines.system_reference5%TYPE;
6108
6109 l_pa_date DATE ;
6110 l_recvr_pa_date DATE ;
6111 l_ei_date DATE ;
6112 l_org_id pa_expenditure_items.org_id%type;
6113
6114 actual_cdl_line_num pa_cost_distribution_lines.line_num%TYPE;
6115
6116
6117 l_recvr_org_id pa_expenditure_items.org_id%type;
6118 l_gl_date DATE;
6119 l_recvr_gl_date DATE;
6120 l_exp_id NUMBER;
6121 l_sob_id NUMBER;
6122 l_recvr_sob_id NUMBER;
6123 l_sys_link_function VARCHAR2(3);
6124 l_err_stage NUMBER;
6125 l_status NUMBER;
6126 l_err_code VARCHAR2(100);
6127 l_rate_source_code varchar2(30);
6128 l_costing_method varchar2(150);
6129
6130 BEGIN
6131 SELECT ITEMS.adjusted_expenditure_item_id,
6132 ITEMS.transferred_from_exp_item_id,
6133 TRN.gl_accounted_flag,
6134 TRN.transaction_source
6135 ,ITEMS.expenditure_item_date
6136 ,ITEMS.org_id
6137 ,NVL(ITEMS.recvr_org_id,ITEMS.ORG_ID)
6138 ,ITEMS.system_linkage_function
6139 ,ITEMS.expenditure_id
6140
6141 INTO p_parent_adjusted_id,
6142 p_parent_transferred_id,
6143 p_gl_accounted_flag,
6144 p_transaction_source
6145 ,l_ei_date
6146 ,l_org_id
6147 ,l_recvr_org_id
6148 ,l_sys_link_function
6149 ,l_exp_id
6150
6151 FROM pa_expenditure_items_All ITEMS,
6152 pa_transaction_sources TRN
6153 WHERE ITEMS.transaction_source = TRN.transaction_source (+)
6154 AND ITEMS.expenditure_item_id = X_exp_item_id;
6155
6156 SELECT imp1.set_of_books_id, imp2.set_of_books_id
6157 INTO l_sob_id, l_recvr_sob_id
6158 FROM pa_implementations_all imp1, pa_implementations_all imp2
6159 WHERE imp1.org_id = l_org_id
6160 AND imp2.org_id = l_recvr_org_id;
6161
6162 IF l_sys_link_function not in ('VI','ER') THEN
6163
6164 /* Added for Bug 2378505 The reversing line should take the details from the latest CDL
6165 and not from line_num = 1 as there may be some attributes which could differ which in
6166 this case is the billable_flag */
6167
6168 SELECT max(cdl.line_num)
6169 INTO actual_cdl_line_num
6170 FROM pa_cost_distribution_lines cdl
6171 WHERE cdl.expenditure_item_id = X_exp_item_id and cdl.line_type = 'R';
6172 /* Addition for bug 2378505 ends */
6173
6174 SELECT amount
6175 , dr_code_combination_id
6176 , cr_code_combination_id
6177 , transfer_status_code
6178 , quantity
6179 , billable_flag
6180 , request_id
6181 , program_application_id
6182 , program_id
6183 , program_update_date
6184 , pa_date
6185 , gl_date
6186 , transferred_date
6187 , transfer_rejection_reason
6188 , line_type
6189 , ind_compiled_set_id
6190 , nvl(burdened_cost,0) + nvl(projfunc_burdened_change,0)
6191 , line_num_reversed
6192 , reversed_flag
6193 , system_reference1
6194 , system_reference2
6195 , system_reference3
6196 , denom_currency_code
6197 , denom_raw_cost
6198 , NVL(denom_burdened_cost,0) + nvl(denom_burdened_change,0)
6199 , acct_currency_code
6200 , acct_rate_date
6201 , acct_rate_type
6202 , acct_exchange_rate
6203 , acct_raw_cost
6204 , NVL(acct_burdened_cost,0) + nvl(acct_burdened_change,0)
6205 , project_currency_code
6206 , project_rate_date
6207 , project_rate_type
6208 , project_exchange_rate
6209 , project_id
6210 , task_id,
6211 recvr_gl_date
6212 , Projfunc_currency_code
6213 , Projfunc_cost_rate_date
6214 , Projfunc_cost_rate_type
6215 , Projfunc_cost_exchange_rate
6216 , Project_raw_cost
6217 , NVL(Project_burdened_cost,0) + nvl(project_burdened_change,0)
6218 , Work_type_id
6219 , system_reference4
6220 , system_reference5
6221 , decode(si_assets_addition_flag, 'R','T', 'O', 'T', 'Y', 'T', 'N', 'T',si_assets_addition_flag )
6222 , rate_source_code
6223 , costing_method
6224 INTO p_amount,
6225 p_dr_ccid,
6226 p_cr_ccid,
6227 p_transfer_status_code,
6228 p_quantity,
6229 p_billable_flag,
6230 p_request_id,
6231 p_program_application_id,
6232 p_program_id,
6233 p_program_update_date,
6234 p_pa_date,
6235 p_gl_date,
6236 p_transferred_date,
6237 p_transfer_rejection_reason,
6238 p_line_type,
6239 p_ind_complied_set_id,
6240 p_burdened_cost,
6241 p_line_num_reversed,
6242 p_reversed_flag,
6243 p_cdlsr1,
6244 p_cdlsr2,
6245 p_cdlsr3,
6246 p_denom_currency_code,
6247 p_denom_raw_cost,
6248 p_denom_burdened_cost,
6249 p_acct_currency_code,
6250 p_acct_rate_date,
6251 p_acct_rate_type,
6252 p_acct_exchange_rate,
6253 p_acct_raw_cost,
6254 p_acct_burdened_cost,
6255 p_project_currency_code,
6256 p_project_rate_date,
6257 p_project_rate_type,
6258 p_project_exchange_rate,
6259 p_project_id,
6260 p_task_id
6261 , p_recvr_gl_date
6262 , p_Projfunc_currency_code
6263 , p_Projfunc_cost_rate_date
6264 , p_Projfunc_cost_rate_type
6265 , p_Projfunc_cost_exchange_rate
6266 , p_Project_raw_cost
6267 , p_Project_burdened_cost
6268 , p_Work_type_id
6269 , p_cdlsr4
6270 , p_cdlsr5
6271 , l_si_assets_addition_flag
6272 , l_rate_source_code
6273 , l_costing_method
6274 FROM pa_cost_distribution_lines_All -- 12i MOAC changes
6275 WHERE expenditure_item_id = X_exp_item_id
6276 AND line_num = actual_cdl_line_num; -- bug2378505
6277 /* Getting pa and gl period information for 2661921 */
6278 PA_UTILS2.get_period_information(
6279 p_expenditure_item_date => l_ei_date
6280 ,p_expenditure_id => l_exp_id
6281 ,p_system_linkage_function => l_sys_link_function
6282 ,p_line_type => p_line_type
6283 ,p_prvdr_raw_pa_date => p_pa_date
6284 ,p_recvr_raw_pa_date => p_recvr_pa_date
6285 ,p_prvdr_raw_gl_date => p_gl_date
6286 ,p_recvr_raw_gl_date => p_recvr_gl_date
6287 ,p_prvdr_org_id => l_org_id
6288 ,p_recvr_org_id => l_recvr_org_id
6289 ,p_prvdr_sob_id => l_sob_id
6290 ,p_recvr_sob_id => l_recvr_sob_id
6291 ,p_calling_module => 'CDL'
6292 ,x_prvdr_pa_date => l_pa_date
6293 ,x_prvdr_pa_period_name => p_pa_period_name
6294 ,x_prvdr_gl_date => l_gl_date
6295 ,x_prvdr_gl_period_name => p_gl_period_name
6296 ,x_recvr_pa_date => l_recvr_pa_date
6297 ,x_recvr_pa_period_name => p_recvr_pa_period_name
6298 ,x_recvr_gl_date => l_recvr_gl_date
6299 ,x_recvr_gl_period_name => p_recvr_gl_period_name
6300 ,x_error_code => l_err_code
6301 ,x_return_status => l_status
6302 ,x_error_stage => l_err_stage );
6303 /* pa and gl period information fetched for 2661921*/
6304 /* 2661921 */
6305 IF p_err_code IS NOT NULL THEN
6306 raise e_cdl_error;
6307 END IF;
6308 /* 2661921 */
6309 PA_COSTING.CREATENEWCDL(
6310 X_expenditure_item_id => X_backout_id
6311 , X_amount => -p_amount
6312 , X_dr_ccid => p_dr_ccid
6313 , X_cr_ccid => p_cr_ccid
6314 , X_transfer_status_code => 'P' /* bug 2361495 p_transfer_status_code */
6315 , X_quantity => -p_quantity
6316 , X_billable_flag => p_billable_flag
6317 , X_request_id => p_request_id
6318 , X_program_application_id => p_program_application_id
6319 , x_program_id => p_program_id
6320 , x_program_update_date => p_program_update_date
6321 , X_pa_date => l_pa_date /* bug 2361495 p_pa_date */
6322 , X_recvr_pa_date => l_recvr_pa_date /** bug 2361495 p_recvr_pa_date CBGA **/
6323 , X_gl_date => l_gl_date /* bug 2661921 p_gl_date */
6324 , X_transferred_date => NULL /* bug 2361495 p_transferred_date */
6325 , X_transfer_rejection_reason => NULL /* bug 2361495 p_transfer_rejection_reason */
6326 , X_line_type => p_line_type
6327 , X_ind_compiled_set_id => p_ind_complied_set_id
6328 , X_burdened_cost => -p_burdened_cost
6329 , X_line_num_reversed => p_line_num_reversed
6330 , X_reverse_flag => p_reversed_flag
6331 , X_user => X_user
6332 , X_err_code => p_err_code
6333 , X_err_stage => p_err_stage
6334 , X_err_stack => p_err_stack
6335 , X_project_id => p_project_id
6336 , X_task_id => p_task_id
6337 , X_cdlsr1 => p_cdlsr1
6338 , X_cdlsr2 => p_cdlsr2
6339 , X_cdlsr3 => p_cdlsr3
6340 , X_denom_currency_code => p_denom_currency_code
6341 , X_denom_raw_cost => -p_denom_raw_cost
6342 , X_denom_burden_cost => -p_denom_burdened_cost
6343 , X_acct_currency_code => p_acct_currency_code
6344 , X_acct_rate_date => p_acct_rate_date
6345 , X_acct_rate_type => p_acct_rate_type
6346 , X_acct_exchange_rate => p_acct_exchange_rate
6347 , X_acct_raw_cost => -p_acct_raw_cost
6348 , X_acct_burdened_cost => -p_acct_burdened_cost
6349 , X_project_currency_code => p_project_currency_code
6350 , X_project_rate_date => p_project_rate_date
6351 , X_project_rate_type => p_project_rate_type
6352 , X_project_exchange_rate => p_project_exchange_rate
6353 , P_PaPeriodName => P_Pa_Period_Name
6354 , P_RecvrPaPeriodName => P_Recvr_Pa_Period_Name
6355 , P_GlPeriodName => P_Gl_Period_Name
6356 , P_RecvrGlDate => l_recvr_gl_date /* bug 2661921 P_Recvr_Gl_Date */
6357 , P_RecvrGlPeriodName => P_Recvr_Gl_Period_Name
6358 , P_Projfunc_currency_code => P_Projfunc_currency_code
6359 , P_Projfunc_cost_rate_date => P_Projfunc_cost_rate_date
6360 , P_Projfunc_cost_rate_type => P_Projfunc_cost_rate_type
6361 , P_Projfunc_cost_exchange_rate => P_Projfunc_cost_exchange_rate
6362 , P_Project_Raw_Cost => -P_Project_Raw_Cost --Bug 3315099
6363 , P_Project_Burdened_Cost => -P_Project_Burdened_Cost --Bug 3315099
6364 , P_Work_Type_Id => P_Work_Type_Id
6365 , p_cdlsr4 => p_cdlsr4
6366 , p_si_assets_addition_flag => l_si_assets_addition_flag
6367 , p_cdlsr5 => p_cdlsr5
6368 , P_Parent_Line_Num => actual_cdl_line_num
6369 /* 12.2 payroll intg .. start */
6370 , p_rate_source_code => l_rate_source_code
6371 , p_costing_method => l_costing_method);
6372 /* 12.2 payroll intg .. end */
6373 IF p_err_code IS NOT NULL THEN
6374 raise e_cdl_error;
6375 END IF;
6376 UPDATE pa_expenditure_items
6377 SET cost_distributed_flag = 'Y'
6378 WHERE expenditure_item_id = X_backout_id;
6379 END IF;
6380 X_status := 0;
6381 EXCEPTION
6382 WHEN NO_DATA_FOUND THEN
6383 NULL;
6384 WHEN e_cdl_error THEN
6385 X_status := p_err_code;
6386 WHEN OTHERS THEN
6387 X_status := SQLCODE;
6388 RAISE;
6389 END CreateReverseCdl;
6390
6391
6392 PROCEDURE get_fc_rates(
6393 X_ACCT_RATE_TYPE OUT NOCOPY VARCHAR2 ,
6394 X_ACCT_RATE_DATE_CODE OUT NOCOPY VARCHAR2 ,
6395 x_err_stage OUT NOCOPY NUMBER ,
6396 x_err_code OUT NOCOPY VARCHAR2 )
6397 IS
6398
6399 USER_EXCEPTION EXCEPTION;
6400 BEGIN
6401 BEGIN
6402
6403 SELECT DEFAULT_RATE_TYPE,
6404 DEFAULT_RATE_DATE_CODE
6405 INTO
6406 X_ACCT_RATE_TYPE,
6407 X_ACCT_RATE_DATE_CODE
6408 FROM pa_implementations ;
6409
6410 EXCEPTION
6411 WHEN NO_DATA_FOUND THEN
6412 X_ACCT_RATE_TYPE := NULL;
6413 X_ACCT_RATE_DATE_CODE := NULL;
6414 X_ERR_CODE:= 'NO_RATE_FOUND';
6415 WHEN TOO_MANY_ROWS THEN
6416 X_ERR_CODE := 'DUP_REC';
6417 END;
6418 IF ( x_err_code IS NOT NULL ) THEN
6419 raise USER_EXCEPTION;
6420 END IF;
6421
6422
6423 EXCEPTION
6424 WHEN OTHERS THEN
6425 x_err_code := 'NO_RATE_FOUND';
6426 END get_fc_rates;
6427
6428
6429 PROCEDURE get_orig_denom_amount
6430 ( P_SOURCE_EXPENDITURE_ITEM_ID IN NUMBER,
6431 P_EXPENDITURE_ITEM_DATE IN DATE,
6432 P_DENOM_CURR_CODE IN VARCHAR ,
6433 P_ORIG_AMOUNT IN NUMBER,
6434 P_ORIG_CURR_CODE IN VARCHAR,
6435 x_denom_orig_amount OUT NOCOPY NUMBER
6436 )
6437 IS
6438 l_acct_rate_type pa_implementations_all.DEFAULT_RATE_TYPE%type;
6439 l_acct_rate_date_code pa_implementations_all.DEFAULT_RATE_DATE_CODE%type;
6440 l_acct_exch_rate NUMBER DEFAULT NULL;
6441 l_err_code VARCHAR2(200) DEFAULT NULL;
6442 l_err_stage NUMBER;
6443 l_acct_currency_code VARCHAR2(15);
6444 l_conversion_date DATE;
6445 l_numerator NUMBER;
6446 l_denominator NUMBER;
6447 CURR_ATTR_NOT_FOUND EXCEPTION;
6448 BEGIN
6449 write_log(Log,'Converting to FC');
6450
6451 get_fc_rates( l_acct_rate_type , l_acct_rate_date_code ,l_err_stage ,l_err_code ) ;
6452 IF (l_err_code IS NOT NULL) THEN
6453 FOR X IN
6454 (
6455 SELECT DISTINCT
6456 PAY_SOURCE_ID ,
6457 PAY_ELEMENT_TYPE_ID,
6458 PAY_ELEMENT_TYPE_CODE
6459 FROM
6460 PA_PAY_DIST_LINES
6461 WHERE
6462 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
6463 PERSON_ID = G_PERSON_ID AND
6464 SOURCE_EXPENDITURE_ITEM_ID = P_SOURCE_EXPENDITURE_ITEM_ID
6465 )
6466 LOOP
6467 reject_pay_element (X.PAY_SOURCE_ID, X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE , 'RC 80' ) ;
6468 write_log(Log,'l_err_code - ' || l_err_code);
6469 RAISE CURR_ATTR_NOT_FOUND;
6470 END LOOP;
6471 END IF ;
6472
6473 write_log(Log,'l_acct_rate_type - ' || l_acct_rate_type);
6474 write_log(Log,'l_acct_rate_date_code - ' || l_acct_rate_date_code);
6475
6476
6477 IF l_acct_rate_date_code = 'P' THEN
6478 l_conversion_date := pa_utils2.get_pa_date( P_EXPENDITURE_ITEM_DATE ,SYSDATE, G_ORG_ID);
6479 ELSE
6480 l_conversion_date :=P_EXPENDITURE_ITEM_DATE;
6481 END IF;
6482
6483 pa_multi_currency.convert_amount ( P_from_currency => P_ORIG_CURR_CODE,
6484 P_to_currency => P_DENOM_CURR_CODE,
6485 P_conversion_date =>l_conversion_date ,
6486 P_conversion_type =>l_acct_rate_type,
6487 P_amount =>P_ORIG_AMOUNT,
6488 P_user_validate_flag =>'N',
6489 P_handle_exception_flag =>'Y',
6490 P_converted_amount =>X_denom_orig_amount,
6491 P_denominator =>l_denominator,
6492 P_numerator =>l_numerator,
6493 P_rate =>l_acct_exch_rate,
6494 X_status => l_err_code ) ;
6495 IF (l_err_code IS NOT NULL) THEN
6496 FOR X IN
6497 (
6498 SELECT DISTINCT
6499 PAY_SOURCE_ID ,
6500 PAY_ELEMENT_TYPE_ID,
6501 PAY_ELEMENT_TYPE_CODE
6502 FROM
6503 PA_PAY_DIST_LINES
6504 WHERE
6505 INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
6506 PERSON_ID = G_PERSON_ID AND
6507 SOURCE_EXPENDITURE_ITEM_ID = P_SOURCE_EXPENDITURE_ITEM_ID
6508 )
6509 LOOP
6510 reject_pay_element (X.PAY_SOURCE_ID, X.PAY_ELEMENT_TYPE_ID,X.PAY_ELEMENT_TYPE_CODE , 'RC 80' ) ;
6511 write_log(Log,'l_err_code - ' || l_err_code);
6512 RAISE CURR_ATTR_NOT_FOUND;
6513 END LOOP;
6514 END IF ;
6515
6516 write_log(Log,'l_acct_exch_rate - ' || l_acct_exch_rate);
6517 write_log(Log,'X_denom_orig_amount - ' || X_denom_orig_amount);
6518 EXCEPTION
6519 WHEN CURR_ATTR_NOT_FOUND THEN
6520 write_log(Log,
6521 'No Currrecy attributes found . Rejecting Employee pay for Person ' || G_PERSON_ID);
6522 RAISE REJECT_EMP_PAY;
6523 WHEN OTHERS THEN
6524 write_log(Log, 'ERROR in convert_to_fc ' || SQLERRM);
6525 RAISE REJECT_EMP_PAY;
6526 END get_orig_denom_amount;
6527
6528 END;