[Home] [Help]
42: , SUM (NVL(PBS.WK_RATE_CUTOFF,0)) sum_wk_rate_cutoff
43: , SUM(NVL(PBS.MTH_TAX_CREDIT,0)) sum_mth_tax_credit
44: , SUM(NVL(PBS.WK_TAX_CREDIT,0)) sum_wk_tax_credit
45: FROM PAY_IE_TAX_HEADER_INTERFACE phs
46: , PAY_IE_TAX_BODY_INTERFACE pbs
47: WHERE PHS.EMPLOYER_NUMBER = PBS.EMPLOYER_NUMBER
48: AND PHS.TAX_YEAR = p_tax_year
49: AND PBS.EMPLOYER_NUMBER = p_employer_number;
50:
93: FND_FILE.PUT_LINE(fnd_file.log,'Error occured while deleting exisiting rows in
94: PAY_IE_TAX_ERROR table');
95: end;
96:
97: UPDATE PAY_IE_TAX_BODY_INTERFACE
98: SET EMPLOYER_NUMBER =
99: (SELECT EMPLOYER_NUMBER
100: FROM PAY_IE_TAX_HEADER_INTERFACE);
101:
132: THEN
133: retcode := 0;
134: fnd_file.put_line( fnd_file.log, 'FND - CONC-COMPLETION TEXT:NORMAL');
135:
136: update pay_ie_tax_body_interface
137: set process_flag = 'Y'
138: where EMPLOYER_NUMBER = p_employer_number;
139: Commit;
140:
157: retcode := 2;
158: IF l_count_mth_taxcredit <> l_count_emp_recno THEN
159: FND_FILE.NEW_LINE(fnd_file.log, 1);
160: FND_FILE.PUT_LINE(fnd_file.log,
161: 'The total number of record in pay_ie_tax_body_interface is '
162: || TO_CHAR (l_count_mth_taxcredit));
163: FND_FILE.PUT_LINE (fnd_file.log,
164: 'The value in pay_ie_tax_trailer_interface.record_no is '
165: || TO_CHAR(l_count_emp_recno));
163: FND_FILE.PUT_LINE (fnd_file.log,
164: 'The value in pay_ie_tax_trailer_interface.record_no is '
165: || TO_CHAR(l_count_emp_recno));
166: FND_FILE.PUT_LINE(fnd_file.log,
167: 'Error - Retcode = 2, total number of record in pay_ie_tax_body_interface');
168: FND_FILE.PUT_LINE(fnd_file.log,
169: 'does not match the value in pay_ie_tax_trailer_interface.record_no');
170: END IF;
171:
171:
172: IF l_sum_mth_rate_cutoff <> l_total_mth_cutoff THEN
173: FND_FILE.NEW_LINE(fnd_file.log, 1);
174: FND_FILE.PUT_LINE(fnd_file.log,
175: 'The sum of pay_ie_tax_body_interface.mth_rate_cutoff is '
176: || TO_CHAR (l_sum_mth_rate_cutoff));
177: FND_FILE.PUT_LINE (fnd_file.log,
178: 'The total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff is '
179: || TO_CHAR(l_total_mth_cutoff));
177: FND_FILE.PUT_LINE (fnd_file.log,
178: 'The total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff is '
179: || TO_CHAR(l_total_mth_cutoff));
180: FND_FILE.PUT_LINE(fnd_file.log,
181: 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_rate_cutoff');
182: FND_FILE.PUT_LINE(fnd_file.log,
183: 'does not match the total monthly cutoff in pay_ie_tax_trailer_interface.total_mth_rate_cutoff');
184: END IF;
185: IF l_sum_wk_rate_cutoff <> l_total_wk_cutoff THEN
184: END IF;
185: IF l_sum_wk_rate_cutoff <> l_total_wk_cutoff THEN
186: FND_FILE.NEW_LINE(fnd_file.log, 1);
187: FND_FILE.PUT_LINE(fnd_file.log,
188: 'The sum of pay_ie_tax_body_interface.wk_rate_cutoff is '
189: || TO_CHAR(l_sum_wk_rate_cutoff));
190: FND_FILE.PUT_LINE(fnd_file.log,
191: 'The total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff is '
192: || TO_CHAR(l_total_wk_cutoff));
190: FND_FILE.PUT_LINE(fnd_file.log,
191: 'The total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff is '
192: || TO_CHAR(l_total_wk_cutoff));
193: FND_FILE.PUT_LINE(fnd_file.log,
194: 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.wk_rate_cutoff');
195: FND_FILE.PUT_LINE(fnd_file.log,
196: 'does not match the the total weekly cutoff in pay_ie_tax_trailer_interface.total_wk_rate_cutoff');
197: END IF;
198: IF l_sum_mth_tax_credit <> l_total_mth_credit THEN
197: END IF;
198: IF l_sum_mth_tax_credit <> l_total_mth_credit THEN
199: FND_FILE.NEW_LINE(fnd_file.log, 1);
200: FND_FILE.PUT_LINE(fnd_file.log,
201: 'The sum of pay_ie_tax_body_interface.mth_tax_credit is '
202: || TO_CHAR(l_sum_mth_tax_credit));
203: FND_FILE.PUT_LINE(fnd_file.log,
204: 'The total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit is '
205: || TO_CHAR(l_total_mth_credit));
203: FND_FILE.PUT_LINE(fnd_file.log,
204: 'The total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit is '
205: || TO_CHAR(l_total_mth_credit));
206: FND_FILE.PUT_LINE(fnd_file.log,
207: 'Error - Retcode = 2, the sum of pay_ie_tax_body_interface.mth_tax_credit');
208: FND_FILE.PUT_LINE(fnd_file.log,
209: 'does not match the total monthly credit in pay_ie_tax_trailer_interface.total_mth_tax_credit');
210: END IF;
211: IF BodyRec.sum_wk_tax_credit <> TrailRec.total_wk_credit THEN
210: END IF;
211: IF BodyRec.sum_wk_tax_credit <> TrailRec.total_wk_credit THEN
212: FND_FILE.NEW_LINE(fnd_file.log, 1);
213: FND_FILE.PUT_LINE(fnd_file.log,
214: 'The sum of pay_ie_tax_body_interface.wk_tax_credit is '
215: || TO_CHAR(l_sum_wk_tax_credit));
216: FND_FILE.PUT_LINE(fnd_file.log,
217: 'The total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit is '
218: || TO_CHAR(l_total_wk_credit));
216: FND_FILE.PUT_LINE(fnd_file.log,
217: 'The total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit is '
218: || TO_CHAR(l_total_wk_credit));
219: FND_FILE.PUT_LINE(fnd_file.log,
220: 'Error - Retcode = 2 because the sum of pay_ie_tax_body_interface.wk_tax_credit');
221: FND_FILE.PUT_LINE(fnd_file.log,
222: 'does not match the total weekly credit in pay_ie_tax_trailer_interface.total_wk_tax_credit');
223: END IF;
224:
245: l_error_stack := errbuf;
246: l_error := SQLERRM;
247: retcode := 2;
248: FND_FILE.PUT_LINE (fnd_file.log, 'Error raised in loading data into one or all of the follo
249: wing tables: pay_ie_tax_header_interface, pay_ie_tax_body_interface, pay_ie_tax_trailer_interface');
250:
251: INSERT INTO pay_ie_tax_error ( pps_number
252: , employee_number
253: , full_name
288: ,count(distinct(asg.ASSIGNMENT_NUMBER)) no_of_assg
289: from per_all_assignments_f asg,
290: per_all_people_f per,
291: pay_all_payrolls_f pay,
292: pay_ie_tax_body_interface tbi,
293: per_periods_of_service pps
294: where per.national_identifier = tbi.pps_number
295: --AND pay.payroll_id = nvl(p_payroll_id,pay.payroll_id)
296: --AND asg.payroll_id = pay.payroll_id
309: group by per.person_id,per.NATIONAL_IDENTIFIER;
310:
311: /* Cursor check_pps(p_pps_no varchar) is
312: Select 1 from per_all_people_f per
313: ,pay_ie_tax_body_interface tbi
314: Where per.effective_start_date <= to_date('31/12/'||to_char(tbi.cert_start_date,'YYYY'),'dd/mm/yyyy')
315: AND per.effective_end_date >= trunc(tbi.cert_start_date)
316: and per.national_identifier=p_pps_no
317: AND tbi.process_flag = 'Y'
357: , per_all_assignments_f asg
358: , per_all_people_f per
359: , pay_all_payrolls_f pay
360: --, pay_ie_paye_details_f ppd
361: , pay_ie_tax_body_interface tbi
362: , pay_ie_tax_header_interface thi
363: WHERE per.person_id = asg.person_id
364: AND per.national_identifier = tbi.pps_number
365: AND asg.business_group_id = hou.business_group_id
423: , per_all_assignments_f asg
424: , per_all_people_f per
425: , pay_all_payrolls_f pay
426: --, pay_ie_paye_details_f ppd
427: , pay_ie_tax_body_interface tbi
428: , pay_ie_tax_header_interface thi,
429: per_periods_of_service pps
430: WHERE per.person_id = asg.person_id
431: AND per.national_identifier = tbi.pps_number
458: -- cursor get the skipped assignments. Assignments that didnt get processed
459: -- in the process
460: --bug 6376140 cursor modified for processing single assg
461: /* cursor csr_skipped_asg is
462: select pps_number, works_number from pay_ie_tax_body_interface
463: minus
464: SELECT distinct per.national_identifier pps_number, asg.assignment_number works_number
465: FROM hr_organization_information hoi
466: , hr_organization_units hou
467: , per_all_assignments_f asg
468: , per_all_people_f per
469: , pay_all_payrolls_f pay
470: --, pay_ie_paye_details_f ppd
471: , pay_ie_tax_body_interface tbi
472: , pay_ie_tax_header_interface thi
473: WHERE per.person_id = asg.person_id
474: AND asg.business_group_id = hou.business_group_id
475: AND hou.organization_id = hoi.organization_id
491: AND pay.effective_end_date >= trunc(tbi.cert_start_date); */
492:
493: cursor csr_skipped_assignments is
494: select distinct pps_number pps_number,works_number,last_name,first_name
495: from pay_ie_tax_body_interface;
496:
497: /* check to see if any single paye details exists */
498: cursor get_paye_details (p_assignment_id number) is
499: select count(*) from pay_ie_paye_details_f where
584: l_datetrack_mode VARCHAR2(12);
585: l_tax_basis pay_ie_paye_details_f.tax_basis%TYPE; -- bug 5766372
586: l_header_count NUMBER := 0;
587: l_record_count NUMBER := 0;
588: l_std_rate_of_tax pay_ie_tax_body_interface.std_rate_of_tax%TYPE;
589: l_higher_rate_of_tax pay_ie_tax_body_interface.higher_rate_of_tax%TYPE;
590:
591: --Declare output parameters from api row handlersb
592: l_ins_paye_details_id NUMBER;
585: l_tax_basis pay_ie_paye_details_f.tax_basis%TYPE; -- bug 5766372
586: l_header_count NUMBER := 0;
587: l_record_count NUMBER := 0;
588: l_std_rate_of_tax pay_ie_tax_body_interface.std_rate_of_tax%TYPE;
589: l_higher_rate_of_tax pay_ie_tax_body_interface.higher_rate_of_tax%TYPE;
590:
591: --Declare output parameters from api row handlersb
592: l_ins_paye_details_id NUMBER;
593: l_ins_object_version_no NUMBER;
929: /*Update interface table and set processed flag to 'Yes' to record that record has been updated or
930: inserted into payroll tables successfully using the row handler APIs.*/
931:
932: IF (p_validate_mode = 'IE_VALIDATE_COMMIT') THEN
933: UPDATE pay_ie_tax_body_interface
934: SET processed_flag = 'Y'
935: WHERE pps_number = r_pay.pps_number_int;
936: END IF;
937: hr_utility.set_location('Assignment Number..'||r_pay.employee_no_int,107);
1437: /*Update interface table and set processed flag to 'No' to record that record has not been updated
1438: or inserted into payroll tables*/
1439:
1440: IF p_validate_mode = 'IE_VALIDATE_COMMIT' THEN
1441: UPDATE pay_ie_tax_body_interface
1442: SET processed_flag = 'N'
1443: WHERE pps_number = r_pay.pps_number_int;
1444: END IF;
1445: -- The following command will be used to output the exception details to an output file: