[Home] [Help]
183: l_proc constant varchar2(150) := g_package||'IsPayrollRun';
184:
185: begin
186:
187: hr_utility.set_location('Entering: '||l_proc, 5);
188:
189: l_temp_var := 'N';
190:
191: open IsPayrollRun(p_person_id,
198: else
199: return false;
200: end if;
201:
202: hr_utility.set_location('Leaving: '||l_proc, 80);
203:
204: end IsPayrollRun;
205:
206: -- ===========================================================================
234: l_proc constant varchar2(150) := g_package||'PQP_Balance';
235:
236: begin
237:
238: hr_utility.set_location('Entering: '||l_proc, 5);
239:
240: l_balance_amount := pqp_us_ff_functions.get_alien_bal
241: (p_assignment_id => p_assignment_id
242: ,p_effective_date => p_effective_date
247: ,p_state_code => p_state_code
248: ,p_fit_wh_bal_flag => p_fit_wh_bal_flag
249: );
250:
251: hr_utility.set_location('Leaving: '||l_proc, 10);
252:
253: return l_balance_amount;
254:
255: exception
253: return l_balance_amount;
254:
255: exception
256: when others then
257: hr_utility.set_location('Leaving: '||l_proc, 15);
258: return 0;
259:
260: end PQP_Balance;
261:
297: l_proc constant varchar2(72) := g_package||'PQP_Forecasted_Balance';
298:
299: begin
300:
301: hr_utility.set_location('Entering: '||l_proc, 5);
302: lnum := 0;
303:
304: for c2_cur in c2
305: loop
303:
304: for c2_cur in c2
305: loop
306: lnum := c2_cur.amount;
307: hr_utility.set_location(l_proc, 6);
308: end loop;
309:
310: hr_utility.set_location('Leaving: '||l_proc, 10);
311:
306: lnum := c2_cur.amount;
307: hr_utility.set_location(l_proc, 6);
308: end loop;
309:
310: hr_utility.set_location('Leaving: '||l_proc, 10);
311:
312: return lnum;
313: exception
314: when others then
311:
312: return lnum;
313: exception
314: when others then
315: hr_utility.set_location('Leaving: '||l_proc, 15);
316: return 0;
317:
318: end PQP_Forecasted_Balance;
319:
349: l_non_us_country_code varchar2(100);
350: l_proc constant varchar2(150):= g_package||'PQP_Windstar_Person_Validate';
351:
352: begin
353: hr_utility.set_location('Entering:'||l_proc, 5);
354:
355: p_out_mesg := 'ERROR ==> ';
356:
357: if (rtrim(ltrim(p_in_data_rec.last_name)) is null) then
383: p_in_data_rec.citizenship_c_code = ' ' ) then
384: p_out_mesg := p_out_mesg || l_temp_prefix || 'Citizenship Code is NULL';
385: null;
386: else
387: hr_utility.set_location(l_proc, 6);
388: open c_tax_country_code_cursor(p_in_data_rec.citizenship_c_code ,
389: p_effective_date );
390: fetch c_tax_country_code_cursor into l_count;
391: hr_utility.set_location(l_proc, 7);
387: hr_utility.set_location(l_proc, 6);
388: open c_tax_country_code_cursor(p_in_data_rec.citizenship_c_code ,
389: p_effective_date );
390: fetch c_tax_country_code_cursor into l_count;
391: hr_utility.set_location(l_proc, 7);
392: close c_tax_country_code_cursor;
393: if (l_count = 0) then
394: p_out_mesg := p_out_mesg || l_temp_prefix ||
395: 'citizenship code is invalid';
393: if (l_count = 0) then
394: p_out_mesg := p_out_mesg || l_temp_prefix ||
395: 'citizenship code is invalid';
396: end if;
397: hr_utility.set_location(l_proc, 8);
398: end if;
399: if (p_out_mesg = 'ERROR ==> ') then
400: p_out_mesg := null;
401: end if;
398: end if;
399: if (p_out_mesg = 'ERROR ==> ') then
400: p_out_mesg := null;
401: end if;
402: hr_utility.set_location('Leaving: '||l_proc, 10);
403:
404: exception
405: when others then
406: hr_utility.set_location('Leaving: '||l_proc, 15);
402: hr_utility.set_location('Leaving: '||l_proc, 10);
403:
404: exception
405: when others then
406: hr_utility.set_location('Leaving: '||l_proc, 15);
407:
408: p_out_mesg := SUBSTR(p_out_mesg || TO_CHAR(SQLCODE) || SQLERRM, 1, 240) ;
409:
410: end PQP_Windstar_Person_Validate;
431: l_temp_prefix varchar2(45) := ':';
432:
433: begin
434:
435: hr_utility.set_location('Entering:'||l_proc, 5);
436:
437: p_out_mesg := 'ERROR ==> ';
438: if (ltrim(rtrim(p_in_data_rec.visa_type)) is null) then
439: p_out_mesg := p_out_mesg || l_temp_prefix || 'Visa Type is NULL';
470: if (p_out_mesg = 'ERROR ==> ') then
471: p_out_mesg := null;
472: end if;
473:
474: hr_utility.set_location('Leaving:'||l_proc, 10);
475:
476: exception
477: when others then
478: hr_utility.set_location('Entering excep:'||l_proc, 15);
474: hr_utility.set_location('Leaving:'||l_proc, 10);
475:
476: exception
477: when others then
478: hr_utility.set_location('Entering excep:'||l_proc, 15);
479: p_out_mesg := SUBSTR(p_out_mesg || TO_CHAR(SQLCODE) || SQLERRM, 1, 240) ;
480:
481: end PQP_Windstar_Visa_Validate;
482:
502: l_proc constant varchar2(72) := g_package||'PQP_Windstar_Balance_Validate';
503: l_temp_prefix varchar2(45) := ':';
504: --
505: begin
506: hr_utility.set_location('Entering: '||l_proc, 5);
507:
508: p_out_mesg := 'ERROR ==> ';
509:
510: if (rtrim(ltrim(p_in_data_rec.income_code)) is null) then
588: ,p_lookup_code => p_in_data_rec.income_code ||
589: p_in_data_rec.income_code_sub_type
590: ,p_effective_date => p_effective_date) then
591:
592: hr_utility.set_location(l_proc, 6);
593:
594: p_out_mesg := p_out_mesg || l_temp_prefix ||
595: 'Invalid combination of Income code and scholarship code :'
596: || p_in_data_rec.income_code ||
600: if (p_out_mesg = 'ERROR ==> ') then
601: p_out_mesg := null;
602: end if;
603:
604: hr_utility.set_location('Leaving:'||l_proc, 10);
605: exception
606: when OTHERS then
607: hr_utility.set_location('Entering excep:'||l_proc, 15);
608: p_out_mesg := SUBSTR(p_out_mesg || TO_CHAR(SQLCODE) || SQLERRM, 1, 240) ;
603:
604: hr_utility.set_location('Leaving:'||l_proc, 10);
605: exception
606: when OTHERS then
607: hr_utility.set_location('Entering excep:'||l_proc, 15);
608: p_out_mesg := SUBSTR(p_out_mesg || TO_CHAR(SQLCODE) || SQLERRM, 1, 240) ;
609:
610: end PQP_Windstar_Balance_Validate;
611:
641: --
642: -- The procedure pqp_process_events_errorlog creates a record in pay_process_events
643: -- table, if a record for an assignment does not already exist
644: --
645: hr_utility.set_location('Entering:'||l_proc, 5);
646:
647: --
648: -- The following procedure pay_ppe_api.create_process_event creates a record
649: -- in the pay_process_events table
657: ,p_description => SUBSTR(p_description1, 1, 240)
658: ,p_process_event_id => l_process_event_id
659: ,p_object_version_number => l_object_version_number
660: );
661: hr_utility.set_location('Leaving:'||l_proc, 10);
662: exception
663: when OTHERS then
664: hr_utility.set_location('Entering exception:'||l_proc, 15);
665: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
660: );
661: hr_utility.set_location('Leaving:'||l_proc, 10);
662: exception
663: when OTHERS then
664: hr_utility.set_location('Entering exception:'||l_proc, 15);
665: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
666: hr_utility.set_message_token('2', 'Error in '
667: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(create). Error '
668: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
661: hr_utility.set_location('Leaving:'||l_proc, 10);
662: exception
663: when OTHERS then
664: hr_utility.set_location('Entering exception:'||l_proc, 15);
665: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
666: hr_utility.set_message_token('2', 'Error in '
667: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(create). Error '
668: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
669: hr_utility.raise_error;
662: exception
663: when OTHERS then
664: hr_utility.set_location('Entering exception:'||l_proc, 15);
665: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
666: hr_utility.set_message_token('2', 'Error in '
667: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(create). Error '
668: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
669: hr_utility.raise_error;
670:
665: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
666: hr_utility.set_message_token('2', 'Error in '
667: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(create). Error '
668: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
669: hr_utility.raise_error;
670:
671: end PQP_Process_Events_ErrorLog;
672:
673: -- ===========================================================================
710: l_proc varchar2(72) := g_package||'PQP_Process_Events_ErrorLog';
711:
712: begin
713:
714: hr_utility.set_location('Entering:'||l_proc, 5);
715:
716: l_object_version_number := p_object_version_number1;
717:
718: pay_ppe_api.update_process_event
722: ,p_process_event_id => p_process_event_id1
723: ,p_object_version_number => l_object_version_number
724: );
725:
726: hr_utility.set_location('Leaving:'||l_proc, 10);
727:
728: exception
729: when others then
730: hr_utility.set_location('Entering exception:'||l_proc, 15);
726: hr_utility.set_location('Leaving:'||l_proc, 10);
727:
728: exception
729: when others then
730: hr_utility.set_location('Entering exception:'||l_proc, 15);
731: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
732: hr_utility.set_message_token('2', 'Error in '
733: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(Update). Error '
734: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
727:
728: exception
729: when others then
730: hr_utility.set_location('Entering exception:'||l_proc, 15);
731: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
732: hr_utility.set_message_token('2', 'Error in '
733: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(Update). Error '
734: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
735: hr_utility.raise_error;
728: exception
729: when others then
730: hr_utility.set_location('Entering exception:'||l_proc, 15);
731: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
732: hr_utility.set_message_token('2', 'Error in '
733: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(Update). Error '
734: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
735: hr_utility.raise_error;
736:
731: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
732: hr_utility.set_message_token('2', 'Error in '
733: || 'pqp_alien_expat_taxation_pkg.pqp_process_events_errorlog(Update). Error '
734: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
735: hr_utility.raise_error;
736:
737: end PQP_Process_Events_ErrorLog;
738:
739: -- ===========================================================================
1176:
1177: l_proc constant varchar2(150) := g_package||'Insert_Pay_Process_Events';
1178:
1179: begin
1180: hr_utility.set_location('Entering:'||l_proc, 5);
1181: --
1182: -- Get the start and end date of year for the effective date passed.
1183: --
1184: l_start_date
1187: := to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY');
1188:
1189: if p_type = 'ALL' then
1190:
1191: hr_utility.set_location(l_proc, 10);
1192: --
1193: for apfc in all_people_f_cursor_n
1194: (c_start_date => l_start_date
1195: ,c_end_date => l_end_date
1198:
1199: loop
1200:
1201: begin
1202: hr_utility.set_location(l_proc, 20);
1203: pay_ppe_api.create_process_event
1204: (p_validate => false
1205: ,p_assignment_id => apfc.assignment_id
1206: ,p_effective_date => apfc.effective_start_date
1209: ,p_description => '| Inserted thru PL/SQL Code |'
1210: ,p_process_event_id => l_process_event_id
1211: ,p_object_version_number => l_object_version_number
1212: );
1213: hr_utility.set_location(l_proc, 30);
1214: exception
1215: when others then
1216: hr_utility.set_location(l_proc, 40);
1217: null;
1212: );
1213: hr_utility.set_location(l_proc, 30);
1214: exception
1215: when others then
1216: hr_utility.set_location(l_proc, 40);
1217: null;
1218: end;
1219:
1220: end loop;
1227: ,c_effective_date => p_effective_date)
1228: loop
1229:
1230: begin
1231: hr_utility.set_location(l_proc, 50);
1232: pay_ppe_api.update_process_event
1233: (p_validate => false
1234: ,p_status => 'N'
1235: ,p_description => null
1235: ,p_description => null
1236: ,p_process_event_id => apfc.process_event_id
1237: ,p_object_version_number => apfc.object_version_number
1238: );
1239: hr_utility.set_location(l_proc, 60);
1240: exception
1241: when others then
1242: hr_utility.set_location(l_proc, 70);
1243: null;
1238: );
1239: hr_utility.set_location(l_proc, 60);
1240: exception
1241: when others then
1242: hr_utility.set_location(l_proc, 70);
1243: null;
1244: end;
1245:
1246: end loop;
1244: end;
1245:
1246: end loop;
1247: else
1248: hr_utility.set_location(l_proc, 80);
1249: --
1250: for c1 in ssn_cursor_n (c_start_date => l_start_date
1251: ,c_end_date => l_end_date
1252: ,c_national_indentifier => p_type
1253: ,c_effective_date => p_effective_date)
1254: loop
1255:
1256: begin
1257: hr_utility.set_location(l_proc, 90);
1258: pay_ppe_api.create_process_event
1259: (p_validate => false
1260: ,p_assignment_id => c1.assignment_id
1261: ,p_effective_date => c1.effective_start_date
1264: ,p_description => '| Inserted thru PL/SQL Code |'
1265: ,p_process_event_id => l_process_event_id
1266: ,p_object_version_number => l_object_version_number
1267: );
1268: hr_utility.set_location(l_proc, 100);
1269: exception
1270: when others then
1271: hr_utility.set_location(l_proc, 110);
1272: null;
1267: );
1268: hr_utility.set_location(l_proc, 100);
1269: exception
1270: when others then
1271: hr_utility.set_location(l_proc, 110);
1272: null;
1273: end;
1274:
1275: end loop;
1281: ,c_effective_date => p_effective_date)
1282: loop
1283:
1284: begin
1285: hr_utility.set_location(l_proc, 120);
1286: pay_ppe_api.update_process_event
1287: (p_validate => false
1288: ,p_status => 'N'
1289: ,p_description => null
1289: ,p_description => null
1290: ,p_process_event_id => c1.process_event_id
1291: ,p_object_version_number => c1.object_version_number
1292: );
1293: hr_utility.set_location(l_proc, 130);
1294: exception
1295: when others then
1296: hr_utility.set_location(l_proc, 140);
1297: null;
1292: );
1293: hr_utility.set_location(l_proc, 130);
1294: exception
1295: when others then
1296: hr_utility.set_location(l_proc, 140);
1297: null;
1298: end;
1299:
1300: end loop;
1300: end loop;
1301: --
1302: end if;
1303:
1304: hr_utility.set_location('Leaving:'||l_proc, 150);
1305:
1306: exception
1307: when others then
1308: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1304: hr_utility.set_location('Leaving:'||l_proc, 150);
1305:
1306: exception
1307: when others then
1308: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1309: hr_utility.set_message_token('2',
1310: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1311: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1312: hr_utility.set_location('Leaving :'||l_proc, 160);
1305:
1306: exception
1307: when others then
1308: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1309: hr_utility.set_message_token('2',
1310: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1311: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1312: hr_utility.set_location('Leaving :'||l_proc, 160);
1313: hr_utility.raise_error;
1308: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1309: hr_utility.set_message_token('2',
1310: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1311: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1312: hr_utility.set_location('Leaving :'||l_proc, 160);
1313: hr_utility.raise_error;
1314:
1315: end insert_pay_process_events;
1316:
1309: hr_utility.set_message_token('2',
1310: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1311: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1312: hr_utility.set_location('Leaving :'||l_proc, 160);
1313: hr_utility.raise_error;
1314:
1315: end insert_pay_process_events;
1316:
1317: /**************************************************************************
1415: and NVL(hrsckf.segment18,assign.location_id) = hrlock.location_id;
1416:
1417: l_proc varchar2(72) := g_package||'address_select' ;
1418: begin --}{
1419: hr_utility.set_location('Entering:'||l_proc, 10);
1420:
1421: /* Person Address Details */
1422: if (UPPER(p_work_home) = 'HOME') then
1423: hr_utility.set_location(l_proc, 20);
1419: hr_utility.set_location('Entering:'||l_proc, 10);
1420:
1421: /* Person Address Details */
1422: if (UPPER(p_work_home) = 'HOME') then
1423: hr_utility.set_location(l_proc, 20);
1424: open home_address_cur;
1425: fetch home_address_cur into
1426: p_county ,
1427: p_state ,
1432: p_telephone_number_1 ,
1433: p_telephone_number_2 ,
1434: p_telephone_number_3 ,
1435: p_postal_code ;
1436: hr_utility.set_location('Entering:'||l_proc, 30);
1437: if (home_address_cur%notfound) then
1438: p_county := '' ;
1439: p_state := '' ;
1440: p_city := '' ;
1445: p_telephone_number_2 := '' ;
1446: p_telephone_number_3 := '' ;
1447: p_postal_code := '' ;
1448: p_work_home := 'NOT_FOUND';
1449: hr_utility.set_location(l_proc, 40);
1450: end if;
1451: close home_address_cur;
1452: elsif (UPPER(p_work_home) = 'WORK') then
1453: hr_utility.set_location(l_proc, 50);
1449: hr_utility.set_location(l_proc, 40);
1450: end if;
1451: close home_address_cur;
1452: elsif (UPPER(p_work_home) = 'WORK') then
1453: hr_utility.set_location(l_proc, 50);
1454: open work_address_cur;
1455: fetch work_address_cur into
1456: p_county ,
1457: p_state ,
1462: p_telephone_number_1 ,
1463: p_telephone_number_2 ,
1464: p_telephone_number_3 ,
1465: p_postal_code ;
1466: hr_utility.set_location(l_proc, 60);
1467: if (work_address_cur%notfound) then
1468: p_county := '' ;
1469: p_state := '' ;
1470: p_city := '' ;
1475: p_telephone_number_2 := '' ;
1476: p_telephone_number_3 := '' ;
1477: p_postal_code := '' ;
1478: p_work_home := 'NOT_FOUND' ;
1479: hr_utility.set_location(l_proc, 70);
1480: end if;
1481: close work_address_cur;
1482: end if;
1483: hr_utility.set_location('Leaving:'||l_proc, 80);
1479: hr_utility.set_location(l_proc, 70);
1480: end if;
1481: close work_address_cur;
1482: end if;
1483: hr_utility.set_location('Leaving:'||l_proc, 80);
1484: exception
1485: when OTHERS then
1486: hr_utility.set_location('Entering exc:'||l_proc, 90);
1487: p_county := '' ;
1482: end if;
1483: hr_utility.set_location('Leaving:'||l_proc, 80);
1484: exception
1485: when OTHERS then
1486: hr_utility.set_location('Entering exc:'||l_proc, 90);
1487: p_county := '' ;
1488: p_state := '' ;
1489: p_city := '' ;
1490: p_address_line1 := '' ;
1553: is
1554: l_spouse_here varchar2(1) := 'N';
1555: l_proc varchar2(72) := g_package||'spouse_here';
1556: begin -- {
1557: hr_utility.set_location('Entering '||l_proc, 10);
1558: for csh1 in c_person_visit_spouse_info(p_person_id ,
1559: p_effective_date)
1560:
1561: loop
1559: p_effective_date)
1560:
1561: loop
1562: l_spouse_here := csh1.spouse_accompanied;
1563: hr_utility.set_location('Leaving '||l_proc, 20);
1564: end loop;
1565: hr_utility.set_location('Leaving '||l_proc, 30);
1566: return l_spouse_here;
1567: end spouse_here;
1561: loop
1562: l_spouse_here := csh1.spouse_accompanied;
1563: hr_utility.set_location('Leaving '||l_proc, 20);
1564: end loop;
1565: hr_utility.set_location('Leaving '||l_proc, 30);
1566: return l_spouse_here;
1567: end spouse_here;
1568: -- =============================================================================
1569: -- name : PQP_Windstar_Person_Read
1961: l_year_start_date date;
1962: l_year_end_date date;
1963:
1964: begin
1965: hr_utility.set_location('Entering '||l_proc, 5);
1966:
1967: l_year_start_date := to_date(('01/01/'||to_char(p_effective_date, 'YYYY'))
1968: ,'MM/DD/YYYY');
1969: l_year_end_date := to_date(('12/31/'||to_char(p_effective_date, 'YYYY'))
1974: -- procedure. The Error is to show user that a blank/Null Source
1975: -- Type has been passed.
1976: --
1977: if (p_source_type is null) then
1978: hr_utility.set_location('Entering '||l_proc, 6);
1979: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1980: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1981: hr_utility.set_message_token
1982: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1975: -- Type has been passed.
1976: --
1977: if (p_source_type is null) then
1978: hr_utility.set_location('Entering '||l_proc, 6);
1979: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1980: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1981: hr_utility.set_message_token
1982: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1983: hr_utility.raise_error;
1976: --
1977: if (p_source_type is null) then
1978: hr_utility.set_location('Entering '||l_proc, 6);
1979: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1980: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1981: hr_utility.set_message_token
1982: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1983: hr_utility.raise_error;
1984:
1977: if (p_source_type is null) then
1978: hr_utility.set_location('Entering '||l_proc, 6);
1979: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1980: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1981: hr_utility.set_message_token
1982: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1983: hr_utility.raise_error;
1984:
1985: elsif (p_source_type <> 'PQP_US_ALIEN_WINDSTAR') then
1979: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1980: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1981: hr_utility.set_message_token
1982: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1983: hr_utility.raise_error;
1984:
1985: elsif (p_source_type <> 'PQP_US_ALIEN_WINDSTAR') then
1986: hr_utility.set_location('Entering '||l_proc, 6);
1987: --
1982: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1983: hr_utility.raise_error;
1984:
1985: elsif (p_source_type <> 'PQP_US_ALIEN_WINDSTAR') then
1986: hr_utility.set_location('Entering '||l_proc, 6);
1987: --
1988: -- raise error message as this package caters to PQP_US_ALIEN_WINDSTAR
1989: -- only as of now. Error is to show user that Invalid Source
1990: -- Type has been passed.
1988: -- raise error message as this package caters to PQP_US_ALIEN_WINDSTAR
1989: -- only as of now. Error is to show user that Invalid Source
1990: -- Type has been passed.
1991: --
1992: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1993: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1994: hr_utility.set_message_token
1995: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1996: hr_utility.raise_error;
1989: -- only as of now. Error is to show user that Invalid Source
1990: -- Type has been passed.
1991: --
1992: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1993: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1994: hr_utility.set_message_token
1995: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1996: hr_utility.raise_error;
1997: end if;
1990: -- Type has been passed.
1991: --
1992: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1993: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1994: hr_utility.set_message_token
1995: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1996: hr_utility.raise_error;
1997: end if;
1998:
1992: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1993: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1994: hr_utility.set_message_token
1995: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1996: hr_utility.raise_error;
1997: end if;
1998:
1999: hr_utility.set_location(l_proc, 10);
2000: --
1995: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1996: hr_utility.raise_error;
1997: end if;
1998:
1999: hr_utility.set_location(l_proc, 10);
2000: --
2001: -- The following BEGIN...END block is used so that the error generated
2002: -- due to the above error condition is not trapped
2003: --
2001: -- The following BEGIN...END block is used so that the error generated
2002: -- due to the above error condition is not trapped
2003: --
2004: if (p_source_type = 'PQP_US_ALIEN_WINDSTAR') then
2005: hr_utility.set_location(l_proc, 15);
2006:
2007: if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2008: hr_utility.set_location(l_proc, 20);
2009: open pay_process_events_cursor
2004: if (p_source_type = 'PQP_US_ALIEN_WINDSTAR') then
2005: hr_utility.set_location(l_proc, 15);
2006:
2007: if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2008: hr_utility.set_location(l_proc, 20);
2009: open pay_process_events_cursor
2010: (c_year_start_date => l_year_start_date
2011: ,c_year_end_date => l_year_end_date
2012: ,p_source_type => p_source_type);
2011: ,c_year_end_date => l_year_end_date
2012: ,p_source_type => p_source_type);
2013:
2014: elsif (p_selection_criterion = 'ALL' ) then
2015: hr_utility.set_location(l_proc, 25);
2016: open per_people_f_cursor(p_effective_date);
2017:
2018: else
2019: hr_utility.set_location(l_proc, 30);
2015: hr_utility.set_location(l_proc, 25);
2016: open per_people_f_cursor(p_effective_date);
2017:
2018: else
2019: hr_utility.set_location(l_proc, 30);
2020: --
2021: -- Else executes when p_selection_criterion is neither 'ALL' nor
2022: -- 'PAY_PROCESS_EVENTS'. Program flow assumes that the
2023: -- NOT NULL string present in the p_selection_criterion is a valid
2028: p_selection_criterion );
2029: end if;
2030: end if;
2031:
2032: hr_utility.set_location(l_proc, 35);
2033:
2034: -- Counter for the t_people_tab - PL/SQL table
2035: i := 1;
2036: -- Counter for the t_error_tab - PL/SQL table
2037: l_err_count := 0;
2038:
2039: if (p_selection_criterion = 'ALL') then
2040:
2041: hr_utility.set_location(l_proc, 40);
2042: --
2043: -- Insert_Pay_Process_Events procedure inserts into pay_process_events
2044: -- table. The records are inserted in this table for the
2045: -- reconciliation purpose.
2084: ,l_suffix
2085: ,l_marital_status
2086: ,l_person_id;
2087:
2088: hr_utility.set_location(l_proc, 50);
2089:
2090: exit when per_people_f_cursor%notfound;
2091:
2092: l_person_read_count := l_person_read_count + 1;
2106: l_telephone_number_2 ,
2107: l_telephone_number_3 ,
2108: l_postal_code);
2109:
2110: hr_utility.set_location(l_proc, 60);
2111:
2112: t_people_tab(i).last_name := l_last_name ;
2113: t_people_tab(i).first_name := l_first_name ;
2114: t_people_tab(i).middle_names := l_middle_names ;
2135: for c_passport in c_person_passport_info(l_person_id)
2136: loop
2137: t_people_tab(i).passport_number := c_passport.passport_number;
2138: end loop;
2139: hr_utility.set_location(l_proc, 70);
2140:
2141: for c_additional in c_person_additional_info(l_person_id)
2142: loop
2143: t_people_tab(i).citizenship_c_code
2163: t_people_tab(i).date_first_entered_us :=
2164: fnd_date.canonical_to_date(c_additional.first_entry_date);
2165: end loop;
2166:
2167: hr_utility.set_location(l_proc, 80);
2168: --
2169: -- to fetch the complete non us address
2170: --
2171: for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2180: t_people_tab(i).non_us_region_postal_cd := c_non_us_addr.non_us_region_postal_cd;
2181: t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2182: end loop;
2183:
2184: hr_utility.set_location(l_proc, 90);
2185: --
2186: -- After a row in PL/SQL table t_people_tab is populated, we pass
2187: -- the just filled row of PL/SQL table to the validation proc
2188: -- pqp_windstar_person_validate
2191: (p_in_data_rec => t_people_tab(i)
2192: ,p_effective_date => p_effective_date
2193: ,p_out_mesg => l_out_mesg
2194: );
2195: hr_utility.set_location(l_proc, 100);
2196: --
2197: -- t_people_tab PL/SQL table cannot be modified in
2198: -- pqp_windstar_person_validate procedure. c_country_code_xlat_cursor
2199: -- cursor will translate the Oracle Application country code to
2233: end loop;
2234: end if;
2235: end if;
2236:
2237: hr_utility.set_location(l_proc, 110);
2238: --
2239: -- A warning message is appended to the description field of the
2240: -- pay_process_events table, if the Non US Country code is not a
2241: -- valid tax country code
2258: t_people_tab(i).non_us_country_code ||
2259: '] may be Invalid |';
2260: end if;
2261: end if;
2262: hr_utility.set_location(l_proc, 120);
2263: --
2264: -- Delete the current row in the PL/SQL table. Update the status
2265: -- in the pay_process_events table to reflect the status as
2266: -- DATA_VALIDATION_FAILED. The row is deleted as we do not want
2279: l_process_event_id ,
2280: l_object_version_number ,
2281: l_assignment_id ,
2282: l_description ;
2283: hr_utility.set_location(l_proc, 130);
2284:
2285: exit when pay_process_events_ovn_cursor%notfound;
2286:
2287: if (l_out_mesg is null) then
2300: ,p_description1 => substr(l_description || l_warn_mesg, 1, 240)
2301: );
2302: end if;
2303:
2304: hr_utility.set_location(l_proc, 140);
2305: -- If the warning message is NOT null, then we do not
2306: -- change the status to D as this is just a warning
2307: else
2308: --
2317: ,p_description1 => SUBSTR(l_out_mesg || l_warn_mesg ||
2318: l_description, 1, 240)
2319: );
2320: end if;
2321: hr_utility.set_location(l_proc, 150);
2322:
2323: end loop;
2324:
2325: close pay_process_events_ovn_cursor;
2325: close pay_process_events_ovn_cursor;
2326:
2327: if (l_out_mesg is null) then
2328: i := i + 1;
2329: hr_utility.set_location(l_proc, 160);
2330: else
2331: hr_utility.set_location(l_proc, 170);
2332: --
2333: -- for wf notification consolidation
2327: if (l_out_mesg is null) then
2328: i := i + 1;
2329: hr_utility.set_location(l_proc, 160);
2330: else
2331: hr_utility.set_location(l_proc, 170);
2332: --
2333: -- for wf notification consolidation
2334: --
2335: if l_process_event_id is not null then
2348: end if; -- if (l_out_mesg
2349:
2350: exception
2351: when others then
2352: hr_utility.set_location(l_proc, 180);
2353: l_person_id := t_people_tab(i).person_id;
2354: l_out_mesg := SUBSTR('Oracle Error ' || TO_CHAR(sqlcode) ||
2355: sqlerrm, 1, 240);
2356: if (t_people_tab.exists(i)) then
2372: ,l_object_version_number
2373: ,l_assignment_id
2374: ,l_description;
2375:
2376: hr_utility.set_location(l_proc, 190);
2377:
2378: exit when pay_process_events_ovn_cursor%notfound;
2379:
2380: pqp_process_events_errorlog
2441: ,l_suffix
2442: ,l_marital_status
2443: ,l_person_id;
2444:
2445: hr_utility.set_location(l_proc, 200);
2446:
2447: exit when pay_process_events_cursor%notfound;
2448:
2449: l_person_read_count := l_person_read_count + 1;
2462: l_telephone_number_2 ,
2463: l_telephone_number_3 ,
2464: l_postal_code);
2465:
2466: hr_utility.set_location(l_proc, 210);
2467:
2468: t_people_tab(i).last_name := l_last_name ;
2469: t_people_tab(i).first_name := l_first_name ;
2470: t_people_tab(i).middle_names := l_middle_names ;
2491: loop
2492: t_people_tab(i).passport_number := c_passport.passport_number;
2493: end loop;
2494:
2495: hr_utility.set_location(l_proc, 220);
2496:
2497: for c_additional in c_person_additional_info(l_person_id)
2498: loop
2499: t_people_tab(i).citizenship_c_code:=
2519: t_people_tab(i).date_first_entered_us :=
2520: fnd_date.canonical_to_date(c_additional.first_entry_date);
2521: end loop;
2522:
2523: hr_utility.set_location(l_proc, 230);
2524:
2525: -- to fetch the complete non us address
2526:
2527: for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2535: t_people_tab(i).non_us_region := c_non_us_addr.non_us_region;
2536: t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2537: end loop;
2538:
2539: hr_utility.set_location(l_proc, 240);
2540: -- After a row in PL/SQL table t_people_tab is populated, we pass
2541: -- the just filled row of PL/SQL table to the validation proc
2542: -- pqp_windstar_person_validate
2543: --
2546: ,p_effective_date => p_effective_date
2547: ,p_out_mesg => l_out_mesg
2548: );
2549:
2550: hr_utility.set_location(l_proc, 250);
2551: -- t_people_tab PL/SQL table cannot be modified in pqp_windstar_person_validate
2552: -- procedure. c_country_code_xlat_cursor cursor will translate the
2553: -- Oracle Application country code to a valid IRS country code
2554: --
2565: := SUBSTR(c1_xlat.meaning,5,length(c1_xlat.meaning)) ;
2566:
2567: end loop;
2568: end if;
2569: hr_utility.set_location(l_proc, 260);
2570: -- A warning message is appended to the description field of
2571: -- the pay_process_events table, if the Non US Country code
2572: -- is not a valid tax country code
2573:
2588: t_people_tab(i).non_us_country_code ||
2589: '] may be Invalid |';
2590: end if;
2591: end if;
2592: hr_utility.set_location(l_proc, 270);
2593: -- Delete the current row in the PL/SQL table. Update the status in
2594: -- the pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2595: -- The row is deleted as we do not want to insert the row containing
2596: -- an error/validation failure in indv_rev1_temp table.
2607: l_process_event_id ,
2608: l_object_version_number ,
2609: l_assignment_id ,
2610: l_description ;
2611: hr_utility.set_location(l_proc, 280);
2612:
2613: exit when pay_process_events_ovn_cursor%notfound;
2614:
2615: if (l_out_mesg is null) then
2612:
2613: exit when pay_process_events_ovn_cursor%notfound;
2614:
2615: if (l_out_mesg is null) then
2616: hr_utility.set_location(l_proc, 290);
2617: -- l_out_mesg = NULL means that there was no failure.
2618: -- Increment the counter and proceed for fetching of the
2619: -- next row from the respective cursor. We therefore do
2620: -- NOT change the status. The status is changed from N to R
2620: -- NOT change the status. The status is changed from N to R
2621: -- after a row in inserted in ten42s_state_temp table
2622:
2623: if (l_warn_mesg is not null) then
2624: hr_utility.set_location(l_proc, 300);
2625:
2626: pqp_process_events_errorlog
2627: (p_process_event_id1 => l_process_event_id
2628: ,p_object_version_number1 => l_object_version_number
2631: l_warn_mesg, 1, 240)
2632: );
2633: end if;
2634: else
2635: hr_utility.set_location(l_proc, 310);
2636: -- Since l_out_mesg is NOT NULL, that means an error was
2637: -- detected. We therefore change the status of the
2638: -- pay_process_events table to 'D' meaning DATA_VALIDATION_FAILED
2639: --
2648: end loop;
2649: close pay_process_events_ovn_cursor;
2650: if (l_out_mesg is null) then
2651: i := i + 1;
2652: hr_utility.set_location(l_proc, 320);
2653: else
2654: -- for wf notification consolidation
2655: if l_process_event_id is not null then
2656: l_err_count := l_err_count+1;
2662: t_people_tab.delete(i) ;
2663: l_person_err_count := l_person_err_count + 1;
2664: l_out_mesg := null;
2665: l_warn_mesg := null;
2666: hr_utility.set_location(l_proc, 330);
2667:
2668: end if;
2669: exception
2670: when others then
2667:
2668: end if;
2669: exception
2670: when others then
2671: hr_utility.set_location(l_proc, 340);
2672: l_person_id := t_people_tab(i).person_id;
2673: --
2674: if (t_people_tab.exists(i)) then
2675: t_people_tab.delete(i) ;
2691: l_process_event_id ,
2692: l_object_version_number ,
2693: l_assignment_id ,
2694: l_description ;
2695: hr_utility.set_location(l_proc, 350);
2696:
2697: exit when pay_process_events_ovn_cursor%notfound;
2698: pqp_process_events_errorlog
2699: (
2720: close pay_process_events_cursor;
2721:
2722: else
2723: -- Executing the code for a particular National Identifier
2724: hr_utility.set_location(l_proc, 360);
2725: /******
2726: insert_pay_process_events procedure inserts into pay_process_events
2727: table. the records are inserted in this table for the reconciliation purpose.
2728: *****/
2763: l_title ,
2764: l_suffix ,
2765: l_marital_status ,
2766: l_person_id ;
2767: hr_utility.set_location(l_proc, 370);
2768:
2769: exit when national_identifier_cursor%notfound;
2770: l_person_read_count := l_person_read_count + 1;
2771:
2783: l_telephone_number_1 ,
2784: l_telephone_number_2 ,
2785: l_telephone_number_3 ,
2786: l_postal_code );
2787: hr_utility.set_location(l_proc, 380);
2788:
2789: t_people_tab(i).last_name := l_last_name ;
2790: t_people_tab(i).first_name := l_first_name ;
2791: t_people_tab(i).middle_names := l_middle_names ;
2810:
2811: for c_passport in c_person_passport_info(l_person_id) loop
2812: t_people_tab(i).passport_number := c_passport.passport_number;
2813: end loop;
2814: hr_utility.set_location(l_proc, 390);
2815:
2816: for c_additional in c_person_additional_info(l_person_id) loop
2817: t_people_tab(i).citizenship_c_code:=
2818: c_additional.tax_res_country_code;
2836:
2837: t_people_tab(i).date_first_entered_us :=
2838: fnd_date.canonical_to_date(c_additional.first_entry_date);
2839: end loop;
2840: hr_utility.set_location(l_proc, 400);
2841:
2842: /* Added the code to fetch the complete non us address - tmehra 15-OCT-2001 */
2843:
2844: for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2851: t_people_tab(i).non_us_city := c_non_us_addr.non_us_city;
2852: t_people_tab(i).non_us_region := c_non_us_addr.non_us_region;
2853: t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2854: end loop;
2855: hr_utility.set_location(l_proc, 410);
2856: /*****
2857: after a row in PL/sql table t_people_tab is populated, we pass the just
2858: filled row of PL/sql table to the validation proc pqp_windstar_person_validate
2859: *****/
2862: p_in_data_rec => t_people_tab(i) ,
2863: p_effective_date => p_effective_date ,
2864: p_out_mesg => l_out_mesg
2865: );
2866: hr_utility.set_location(l_proc, 420);
2867: /*****
2868: t_people_tab PL/sql table cannot be modified in pqp_windstar_person_validate
2869: procedure. c_country_code_xlat_cursor cursor will translate the Oracle
2870: Application country code to a valid IRS country code
2884: length(c1_xlat.meaning)) ;
2885:
2886: end loop;
2887: end if;
2888: hr_utility.set_location(l_proc, 430);
2889: /*****
2890: A warning message is appended to the description field of the pay_process_events
2891: table, if the Non US Country code is not a valid tax country code
2892: *****/
2905: t_people_tab(i).non_us_country_code ||
2906: '] may be Invalid |';
2907: end if;
2908: end if;
2909: hr_utility.set_location(l_proc, 440);
2910:
2911: /*****
2912: delete the current row in the PL/sql table. update the status in the
2913: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2926: l_process_event_id ,
2927: l_object_version_number ,
2928: l_assignment_id ,
2929: l_description ;
2930: hr_utility.set_location(l_proc, 450);
2931:
2932: exit when pay_process_events_ovn_cursor%notfound;
2933:
2934: if (l_out_mesg is null) then
2931:
2932: exit when pay_process_events_ovn_cursor%notfound;
2933:
2934: if (l_out_mesg is null) then
2935: hr_utility.set_location(l_proc, 460);
2936: /*****
2937: l_out_mesg = null means that there was no failure. increment the counter
2938: and proceed for fetching of the next row from the respective cursor.
2939: We therefore do not change the status. the status is changed from N to R
2940: after a row in inserted in ten42s_state_temp table
2941: *****/
2942:
2943: if (l_warn_mesg is not null) then
2944: hr_utility.set_location(l_proc, 470);
2945: pqp_process_events_errorlog
2946: (
2947: p_process_event_id1 => l_process_event_id ,
2948: p_object_version_number1 => l_object_version_number,
2951: SUBSTR(l_description || l_warn_mesg, 1, 240)
2952: );
2953: end if;
2954: else
2955: hr_utility.set_location(l_proc, 480);
2956: /*****
2957: Since l_out_mesg is not null, that means an error was detected. We therefore
2958: change the status of the pay_process_events table to 'D' meaning
2959: DATA_VALIDATION_FAILED
2972:
2973: close pay_process_events_ovn_cursor;
2974: if (l_out_mesg is null) then
2975: i := i + 1;
2976: hr_utility.set_location(l_proc, 490);
2977: else
2978: /* Added by tmehra for wf notification consolidation */
2979: if l_process_event_id is not null then
2980: l_err_count := l_err_count+1;
2987: t_people_tab.delete(i) ;
2988: l_person_err_count := l_person_err_count + 1;
2989: l_out_mesg := null;
2990: l_warn_mesg := null;
2991: hr_utility.set_location(l_proc, 500);
2992: end if;
2993: exception
2994: when OTHERS then
2995: hr_utility.set_location(l_proc, 510);
2991: hr_utility.set_location(l_proc, 500);
2992: end if;
2993: exception
2994: when OTHERS then
2995: hr_utility.set_location(l_proc, 510);
2996: l_person_id := t_people_tab(i).person_id;
2997: if (t_people_tab.exists(i)) then
2998: t_people_tab.delete(i) ;
2999: l_person_err_count := l_person_err_count + 1;
3014: l_process_event_id ,
3015: l_object_version_number ,
3016: l_assignment_id ,
3017: l_description ;
3018: hr_utility.set_location(l_proc, 520);
3019:
3020: exit when pay_process_events_ovn_cursor%notfound;
3021: pqp_process_events_errorlog
3022: (
3042: end;
3043: end loop;
3044: close national_identifier_cursor;
3045: end if;
3046: hr_utility.set_location(l_proc, 530);
3047: p_person_read_count := l_person_read_count ;
3048: p_person_err_count := l_person_err_count ;
3049: hr_utility.set_location('Leaving '||l_proc, 540);
3050: exception
3045: end if;
3046: hr_utility.set_location(l_proc, 530);
3047: p_person_read_count := l_person_read_count ;
3048: p_person_err_count := l_person_err_count ;
3049: hr_utility.set_location('Leaving '||l_proc, 540);
3050: exception
3051: when OTHERS then
3052: p_person_read_count := l_person_read_count ;
3053: p_person_err_count := l_person_err_count ;
3050: exception
3051: when OTHERS then
3052: p_person_read_count := l_person_read_count ;
3053: p_person_err_count := l_person_err_count ;
3054: hr_utility.set_location('Leaving '||l_proc, 550);
3055: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3056: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3057: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3058: ' ' || sqlerrm);
3051: when OTHERS then
3052: p_person_read_count := l_person_read_count ;
3053: p_person_err_count := l_person_err_count ;
3054: hr_utility.set_location('Leaving '||l_proc, 550);
3055: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3056: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3057: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3058: ' ' || sqlerrm);
3059: hr_utility.raise_error;
3052: p_person_read_count := l_person_read_count ;
3053: p_person_err_count := l_person_err_count ;
3054: hr_utility.set_location('Leaving '||l_proc, 550);
3055: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3056: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3057: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3058: ' ' || sqlerrm);
3059: hr_utility.raise_error;
3060: end pqp_windstar_person_read;
3055: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3056: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3057: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3058: ' ' || sqlerrm);
3059: hr_utility.raise_error;
3060: end pqp_windstar_person_read;
3061: /***************************************************************************
3062: name : pqp_windstar_balance_read
3063: Purpose : the following procedure is called from the main procedure. This
3408: AND pptu.effective_start_date BETWEEN ppf.effective_start_date
3409: AND ppf.effective_end_date;
3410:
3411: begin
3412: hr_utility.set_location('Entering '||l_proc, 5);
3413: l_dimension_name := 'Assignment within Government Reporting Entity Year to Date';
3414:
3415: l_year := to_number(to_char(p_effective_date, 'YYYY'));
3416: l_year_start:= to_date('01/01/'||to_char(p_effective_date, 'YYYY'),'MM/DD/YYYY');
3422:
3423: j := 1;
3424:
3425: if l_count >= 1 then
3426: hr_utility.set_location(l_proc, 10);
3427:
3428: l_sit_flag := true;
3429: for i in 1..l_count
3430: loop
3428: l_sit_flag := true;
3429: for i in 1..l_count
3430: loop
3431: begin
3432: hr_utility.set_location(l_proc, 20);
3433: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0') then
3434:
3435: hr_utility.set_location(l_proc, 30);
3436:
3431: begin
3432: hr_utility.set_location(l_proc, 20);
3433: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0') then
3434:
3435: hr_utility.set_location(l_proc, 30);
3436:
3437: l_sit_flag := true;
3438: l_person_id := t_people_tab(i).person_id;
3439: l_last_name := t_people_tab(i).last_name;
3445: for c_additional in c_person_additional_info(l_person_id)
3446: loop
3447: l_tax_residence_country_code :=
3448: c_additional.tax_res_country_code;
3449: hr_utility.set_location(l_proc, 40);
3450: end loop;
3451:
3452: for c_ass in c_person_assignment(l_person_id)
3453: loop
3451:
3452: for c_ass in c_person_assignment(l_person_id)
3453: loop
3454: l_assignment_id := c_ass.assignment_id;
3455: hr_utility.set_location(l_proc, 50);
3456: end loop;
3457:
3458: for c_state in c_work_state_cur(l_assignment_id)
3459: loop
3457:
3458: for c_state in c_work_state_cur(l_assignment_id)
3459: loop
3460: l_state := c_state.state;
3461: hr_utility.set_location(l_proc, 60);
3462: end loop;
3463:
3464: open c_income_code_cursor( l_person_id,
3465: l_year_start,
3493:
3494: l_income_code_count := 1;
3495:
3496: begin
3497: hr_utility.set_location(l_proc, 70);
3498: l_income_code := '';
3499: --
3500: -- The sql below decides if the respective earning entries
3501: -- attached to the assignment. Decide if the request is for
3518: ,l_withholding_rate
3519: ,l_income_code_sub_type
3520: ,l_constant_addl_tax;
3521:
3522: hr_utility.set_location(l_proc, 80);
3523:
3524: l_balance := 0;
3525:
3526: t_balance_tab(j).person_id := l_person_id;
3540: loop
3541: l_prev_amount := c_payment.prev_er_treaty_ben_amt;
3542: end loop;
3543:
3544: hr_utility.set_location(l_proc, 90);
3545:
3546: if (length(l_c_income_code) > 2) then
3547: l_sub_type := substr(l_c_income_code, 3, 1);
3548: end if;
3545:
3546: if (length(l_c_income_code) > 2) then
3547: l_sub_type := substr(l_c_income_code, 3, 1);
3548: end if;
3549: hr_utility.set_location(l_proc, 100);
3550:
3551: if (IsPayrollRun
3552: (l_person_id
3553: ,TO_DATE('31/12/'|| TO_CHAR(l_year),'DD/MM/YYYY')
3552: (l_person_id
3553: ,TO_DATE('31/12/'|| TO_CHAR(l_year),'DD/MM/YYYY')
3554: ,l_income_code) = false) then
3555:
3556: hr_utility.set_location(l_proc, 110);
3557:
3558: l_flag := 'F';
3559: l_balance := pqp_forecasted_balance
3560: (l_person_id ,
3591: p_effective_date => p_effective_date ,
3592: p_out_mesg => l_out_mesg ,
3593: p_forecasted => true
3594: );
3595: hr_utility.set_location(l_proc, 120);
3596: else
3597: hr_utility.set_location(l_proc, 130);
3598: l_flag := 'A';
3599:
3593: p_forecasted => true
3594: );
3595: hr_utility.set_location(l_proc, 120);
3596: else
3597: hr_utility.set_location(l_proc, 130);
3598: l_flag := 'A';
3599:
3600: l_effective_date :=
3601: to_date('31/12/'||to_char(l_year),'DD/MM/YYYY');
3666: t_balance_tab(j).last_date_of_earnings :=
3667: cdpe.date_earned ;
3668: end loop;
3669:
3670: hr_utility.set_location(l_proc, 140);
3671:
3672: t_balance_tab(j).tax_year := l_year ;
3673:
3674: if (l_sit_flag = true) then
3672: t_balance_tab(j).tax_year := l_year ;
3673:
3674: if (l_sit_flag = true) then
3675:
3676: hr_utility.set_location(l_proc, 150);
3677:
3678: l_balance := 0;
3679: l_balance := pqp_balance
3680: (p_income_code => null
3688: t_balance_tab(j).state_withheld_amount := l_balance;
3689: l_sit_flag := false;
3690:
3691: else
3692: hr_utility.set_location(l_proc, 160);
3693:
3694: t_balance_tab(j).state_withheld_amount := 0;
3695: end if;
3696:
3716: t_balance_tab(j).no_of_days_in_cycle
3717: := cdic.days_in_cycle;
3718: end loop;
3719:
3720: hr_utility.set_location(l_proc, 170);
3721:
3722: pqp_windstar_balance_validate
3723: (p_in_data_rec => t_balance_tab(j)
3724: ,p_effective_date => p_effective_date
3725: ,p_out_mesg => l_out_mesg
3726: ,p_forecasted => false
3727: );
3728:
3729: hr_utility.set_location(l_proc, 180);
3730: end if;
3731: if (l_out_mesg is null) then
3732: --
3733: -- l_out_mesg means there is no failure. increment the
3734: -- counter and proceed for fetching of the next row from
3735: -- the respective cursor
3736: --
3737: j := j + 1;
3738: hr_utility.set_location(l_proc, 190);
3739: else --ELSE4}{
3740: hr_utility.set_location(l_proc, 200);
3741: -- Delete the current row in the PL/sql table. update the
3742: -- status in the pay_process_events table to reflect the
3736: --
3737: j := j + 1;
3738: hr_utility.set_location(l_proc, 190);
3739: else --ELSE4}{
3740: hr_utility.set_location(l_proc, 200);
3741: -- Delete the current row in the PL/sql table. update the
3742: -- status in the pay_process_events table to reflect the
3743: -- status as DATA_VALIDATION_FAILED
3744: --
3757: ,l_object_version_number
3758: ,l_assignment_id
3759: ,l_description;
3760:
3761: hr_utility.set_location(l_proc, 210);
3762:
3763: exit when pay_process_events_ovn_cursor%notfound;
3764:
3765: pqp_process_events_errorlog
3792: l_out_mesg := null;
3793: t_people_tab(i).validation_flag := '0';
3794: /* 0 indicates an Error */
3795: exit;
3796: hr_utility.set_location(l_proc, 260);
3797: end if; --ENDIF4}
3798: close c_analyzed_data;
3799: exception
3800: when OTHERS then
3797: end if; --ENDIF4}
3798: close c_analyzed_data;
3799: exception
3800: when OTHERS then
3801: hr_utility.set_location(l_proc, 270);
3802: if (pay_process_events_ovn_cursor%isopen = true) then
3803: close pay_process_events_ovn_cursor;
3804: end if;
3805: if (c_analyzed_data%isopen = true) then
3819: l_object_version_number ,
3820: l_assignment_id ,
3821: l_description ;
3822: exit when pay_process_events_ovn_cursor%notfound;
3823: hr_utility.set_location(l_proc, 280);
3824:
3825: /* Update pay_process_events table with a status of 'D' */
3826:
3827: pqp_process_events_errorlog
3879: l_object_version_number ,
3880: l_assignment_id ,
3881: l_description ;
3882: exit when pay_process_events_ovn_cursor%notfound;
3883: hr_utility.set_location(l_proc, 280);
3884:
3885: /* Update pay_process_events table with a status of 'D' */
3886:
3887: pqp_process_events_errorlog
3911: l_out_mesg := null;
3912:
3913: end if;
3914:
3915: hr_utility.set_location(l_proc, 290);
3916:
3917: end if; --ENDIF2} validation_flag = 0
3918:
3919:
3918:
3919:
3920: exception
3921: when OTHERS then
3922: hr_utility.set_location(l_proc, 300);
3923: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
3924: t_people_tab(i).validation_flag := '0';
3925: t_people_tab(i).error_mesg :=
3926: SUBSTR('Error while processing 1042s details' ||
3928: l_out_mesg := null;
3929: end; --END2}
3930: end loop; --ENDLOOP1}
3931: end if; /* END IF # 1 */ --ENDIF}
3932: hr_utility.set_location('Leaving '||l_proc, 310);
3933: exception --EXC1}{
3934: when OTHERS then
3935: hr_utility.set_location('Entering exc'||l_proc, 320);
3936: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3931: end if; /* END IF # 1 */ --ENDIF}
3932: hr_utility.set_location('Leaving '||l_proc, 310);
3933: exception --EXC1}{
3934: when OTHERS then
3935: hr_utility.set_location('Entering exc'||l_proc, 320);
3936: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3937: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3938: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3939: ' ' || sqlerrm);
3932: hr_utility.set_location('Leaving '||l_proc, 310);
3933: exception --EXC1}{
3934: when OTHERS then
3935: hr_utility.set_location('Entering exc'||l_proc, 320);
3936: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3937: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3938: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3939: ' ' || sqlerrm);
3940: hr_utility.raise_error;
3933: exception --EXC1}{
3934: when OTHERS then
3935: hr_utility.set_location('Entering exc'||l_proc, 320);
3936: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3937: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3938: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3939: ' ' || sqlerrm);
3940: hr_utility.raise_error;
3941: end pqp_windstar_balance_read; --END1}
3936: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3937: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3938: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3939: ' ' || sqlerrm);
3940: hr_utility.raise_error;
3941: end pqp_windstar_balance_read; --END1}
3942: /***************************************************************************
3943: name : pqp_windstar_visa_read
3944: Purpose : the following procedure is called from the main procedure. This
4071: );
4072:
4073: begin
4074:
4075: hr_utility.set_location('Entering '||l_proc, 5);
4076: l_count := t_people_tab.COUNT;
4077: l_err_count := t_error_tab.COUNT;
4078: l_01jan_date := TO_DATE('01/01/'|| TO_CHAR(p_effective_date, 'YYYY'),
4079: 'DD/MM/YYYY');
4082: for i in 1..l_count
4083: loop --LOOP1{
4084: begin
4085:
4086: hr_utility.set_location(l_proc, 10);
4087:
4088: -- Get the errornous record count, Skip this person and raise the notification
4089:
4090: l_skip_person := false;
4106: else
4107: l_skip_person := false;
4108: end if;
4109:
4110: hr_utility.set_location(l_proc, 20);
4111:
4112: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0'
4113: and l_skip_person = false) then --IF1{
4114: hr_utility.set_location(l_proc, 30);
4110: hr_utility.set_location(l_proc, 20);
4111:
4112: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0'
4113: and l_skip_person = false) then --IF1{
4114: hr_utility.set_location(l_proc, 30);
4115:
4116: l_person_id := '' ;
4117: l_last_name := '' ;
4118: l_first_name := '' ;
4129: for c_additional in c_person_additional_info(l_person_id)
4130: loop
4131: l_tax_residence_country_code := c_additional.tax_res_country_code;
4132: end loop;
4133: hr_utility.set_location(l_proc, 40);
4134: l_prev_end_date := null;
4135:
4136: l_visa_found := 'NONE';
4137:
4148: -- means Visa details are available
4149: l_visa_found := 'VISA';
4150:
4151: begin
4152: hr_utility.set_location(l_proc, 50);
4153: t_visa_tab(j).person_id := l_person_id ;
4154: t_visa_tab(j).last_name := l_last_name ;
4155: t_visa_tab(j).first_name := l_first_name ;
4156: t_visa_tab(j).middle_names := l_middle_names ;
4182: /* Means there was no failure. Increment the counter and proceed for
4183: fetching of the next row from the respective cursor */
4184: l_prev_end_date := t_visa_tab(j).visa_end_date;
4185: j := j + 1;
4186: hr_utility.set_location(l_proc, 60);
4187: else --ELSE3}{
4188: /* Delete the current row in the PL/SQL table. Update the status in the
4189: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED
4190: */
4187: else --ELSE3}{
4188: /* Delete the current row in the PL/SQL table. Update the status in the
4189: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED
4190: */
4191: hr_utility.set_location(l_proc, 70);
4192:
4193: l_process_event_id := null;
4194:
4195: open pay_process_events_ovn_cursor(l_person_id ,
4200: l_process_event_id ,
4201: l_object_version_number ,
4202: l_assignment_id ,
4203: l_description ;
4204: hr_utility.set_location(l_proc, 80);
4205: exit when pay_process_events_ovn_cursor%notfound;
4206: /* Update pay_process_events table */
4207: pqp_process_events_errorlog
4208: (
4210: p_object_version_number1 => l_object_version_number ,
4211: p_status1 => 'D' ,
4212: p_description1 => SUBSTR(l_out_mesg, 1, 240)
4213: );
4214: hr_utility.set_location(l_proc, 90);
4215: hr_utility.set_location(l_proc, 100);
4216: end loop;
4217: close pay_process_events_ovn_cursor;
4218: if (t_visa_tab.exists(j)) then
4211: p_status1 => 'D' ,
4212: p_description1 => SUBSTR(l_out_mesg, 1, 240)
4213: );
4214: hr_utility.set_location(l_proc, 90);
4215: hr_utility.set_location(l_proc, 100);
4216: end loop;
4217: close pay_process_events_ovn_cursor;
4218: if (t_visa_tab.exists(j)) then
4219: t_visa_tab.delete(j) ;
4236: ***/
4237: end if; --ENDIF}
4238: exception
4239: when OTHERS then
4240: hr_utility.set_location(l_proc, 110);
4241: if (pay_process_events_ovn_cursor%isopen = true) then
4242: close pay_process_events_ovn_cursor;
4243: end if;
4244: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4253: l_process_event_id ,
4254: l_object_version_number ,
4255: l_assignment_id ,
4256: l_description ;
4257: hr_utility.set_location(l_proc, 120);
4258: exit when pay_process_events_ovn_cursor%notfound;
4259:
4260: /* Update pay_process_events table with a status of 'D' */
4261:
4265: p_object_version_number1 => l_object_version_number ,
4266: p_status1 => 'D' ,
4267: p_description1 => SUBSTR(l_out_mesg, 1, 240)
4268: );
4269: hr_utility.set_location(l_proc, 130);
4270:
4271: end loop;
4272: close pay_process_events_ovn_cursor;
4273: if (t_visa_tab.exists(j)) then
4272: close pay_process_events_ovn_cursor;
4273: if (t_visa_tab.exists(j)) then
4274: t_visa_tab.delete(j) ;
4275: end if;
4276: hr_utility.set_location(l_proc, 140);
4277:
4278: /* Added by tmehra for wf notification consolidation */
4279: if l_process_event_id is not null then
4280: l_err_count := l_err_count+1;
4318: l_object_version_number ,
4319: l_assignment_id ,
4320: l_description ;
4321: exit when pay_process_events_ovn_cursor%notfound;
4322: hr_utility.set_location(l_proc, 280);
4323:
4324: /* Update pay_process_events table with a status of 'D' */
4325:
4326: pqp_process_events_errorlog
4372: l_object_version_number ,
4373: l_assignment_id ,
4374: l_description ;
4375: exit when pay_process_events_ovn_cursor%notfound;
4376: hr_utility.set_location(l_proc, 280);
4377:
4378: /* Update pay_process_events table with a status of 'D' */
4379:
4380: pqp_process_events_errorlog
4409:
4410: end if; --ENDIF1}
4411: exception
4412: when OTHERS then
4413: hr_utility.set_location(l_proc, 150);
4414: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4415: t_people_tab(i).validation_flag := '0';
4416: t_people_tab(i).error_mesg :=
4417: SUBSTR('Error while processing visa details' || l_out_mesg,
4419: l_out_mesg := null;
4420: end;
4421:
4422: end loop; --LOOP1 }
4423: hr_utility.set_location('Leaving '||l_proc, 160);
4424: exception
4425: when OTHERS then
4426: hr_utility.set_location('Entering exc'||l_proc, 170);
4427: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4422: end loop; --LOOP1 }
4423: hr_utility.set_location('Leaving '||l_proc, 160);
4424: exception
4425: when OTHERS then
4426: hr_utility.set_location('Entering exc'||l_proc, 170);
4427: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4428: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4429: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4430: ' ' || sqlerrm);
4423: hr_utility.set_location('Leaving '||l_proc, 160);
4424: exception
4425: when OTHERS then
4426: hr_utility.set_location('Entering exc'||l_proc, 170);
4427: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4428: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4429: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4430: ' ' || sqlerrm);
4431: hr_utility.raise_error;
4424: exception
4425: when OTHERS then
4426: hr_utility.set_location('Entering exc'||l_proc, 170);
4427: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4428: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4429: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4430: ' ' || sqlerrm);
4431: hr_utility.raise_error;
4432: end pqp_windstar_visa_read;
4427: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4428: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4429: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4430: ' ' || sqlerrm);
4431: hr_utility.raise_error;
4432: end pqp_windstar_visa_read;
4433: /****************************************************************************
4434: name : pqp_read_public
4435: Purpose : the following is the main procedure that is called from a
4477: -- added by tmehra for wf notification consolidation
4478: t_error_tab t_error_tab_type ;
4479:
4480: begin
4481: hr_utility.set_location('Entering:'||l_proc, 5);
4482:
4483: /*****
4484: raise error message as Selection Criterion cannot be null
4485: ******/
4484: raise error message as Selection Criterion cannot be null
4485: ******/
4486:
4487: if (p_selection_criterion is null) then
4488: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4489: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4490: hr_utility.set_message_token('API_NAME',
4491: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4492: hr_utility.raise_error;
4485: ******/
4486:
4487: if (p_selection_criterion is null) then
4488: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4489: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4490: hr_utility.set_message_token('API_NAME',
4491: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4492: hr_utility.raise_error;
4493: end if;
4486:
4487: if (p_selection_criterion is null) then
4488: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4489: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4490: hr_utility.set_message_token('API_NAME',
4491: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4492: hr_utility.raise_error;
4493: end if;
4494: begin
4488: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4489: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4490: hr_utility.set_message_token('API_NAME',
4491: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4492: hr_utility.raise_error;
4493: end if;
4494: begin
4495: hr_utility.set_location(l_proc, 10);
4496: /*****
4491: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4492: hr_utility.raise_error;
4493: end if;
4494: begin
4495: hr_utility.set_location(l_proc, 10);
4496: /*****
4497: call pqp_windstar_person_read procedure to read all the information about
4498: the person into PL/sql t_people_tab table.
4499: ******/
4504: t_people_tab => t_people_tab ,
4505: t_error_tab => t_error_tab ,
4506: p_person_read_count => l_person_read_count ,
4507: p_person_err_count => l_person_err_count );
4508: hr_utility.set_location(l_proc, 20);
4509:
4510: p_person_read_count := l_person_read_count;
4511: p_person_err_count := l_person_err_count;
4512:
4522: t_error_tab ,
4523: 'PQP_US_ALIEN_WINDSTAR' ,
4524: p_effective_date ,
4525: t_visa_tab );
4526: hr_utility.set_location(l_proc, 30);
4527:
4528: /*****
4529: call pqp_windstar_balance_read procedure to read all the information about
4530: the balance into PL/sql t_balance_tab table.
4533: t_error_tab ,
4534: 'PQP_US_ALIEN_WINDSTAR' ,
4535: p_effective_date ,
4536: t_balance_tab );
4537: hr_utility.set_location(l_proc, 40);
4538:
4539: l_count := t_error_tab.COUNT;
4540:
4541: /*****
4557: end loop;
4558:
4559: exception
4560: when OTHERS then
4561: hr_utility.set_location('Entering exception:'||l_proc, 50);
4562: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4563: hr_utility.set_message_token('2', 'Error in '
4564: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4565: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4558:
4559: exception
4560: when OTHERS then
4561: hr_utility.set_location('Entering exception:'||l_proc, 50);
4562: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4563: hr_utility.set_message_token('2', 'Error in '
4564: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4565: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4566: hr_utility.raise_error;
4559: exception
4560: when OTHERS then
4561: hr_utility.set_location('Entering exception:'||l_proc, 50);
4562: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4563: hr_utility.set_message_token('2', 'Error in '
4564: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4565: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4566: hr_utility.raise_error;
4567: end;
4562: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4563: hr_utility.set_message_token('2', 'Error in '
4564: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4565: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4566: hr_utility.raise_error;
4567: end;
4568: hr_utility.set_location(l_proc, 60);
4569:
4570: begin
4564: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4565: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4566: hr_utility.raise_error;
4567: end;
4568: hr_utility.set_location(l_proc, 60);
4569:
4570: begin
4571: open c_pay_action_parameter;
4572: l_batch_size := null;
4576: exit when c_pay_action_parameter%notfound;
4577: end loop;
4578: p_batch_size := l_batch_size;
4579: close c_pay_action_parameter;
4580: hr_utility.set_location('Leaving:'||l_proc, 70);
4581: exception
4582: when OTHERS then
4583: hr_utility.set_location(l_proc, 80);
4584: p_batch_size := null;
4579: close c_pay_action_parameter;
4580: hr_utility.set_location('Leaving:'||l_proc, 70);
4581: exception
4582: when OTHERS then
4583: hr_utility.set_location(l_proc, 80);
4584: p_batch_size := null;
4585: end;
4586: end pqp_read_public;
4587: /********************************************************************
4615: l_description varchar2(250);
4616: l_proc varchar2(72) := g_package||'update_pay_process_events' ;
4617:
4618: begin
4619: hr_utility.set_location('Entering:'||l_proc, 5);
4620: if (pay_process_events_ovn_cursor%isopen = true) then
4621: close pay_process_events_ovn_cursor;
4622: end if;
4623: hr_utility.set_location(l_proc, 10);
4619: hr_utility.set_location('Entering:'||l_proc, 5);
4620: if (pay_process_events_ovn_cursor%isopen = true) then
4621: close pay_process_events_ovn_cursor;
4622: end if;
4623: hr_utility.set_location(l_proc, 10);
4624: for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4625: p_source_type ,
4626: p_effective_date)
4627: loop
4624: for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4625: p_source_type ,
4626: p_effective_date)
4627: loop
4628: hr_utility.set_location(l_proc, 20);
4629: l_process_event_id := ppeoc1.process_event_id ;
4630: l_object_version_number := ppeoc1.object_version_number;
4631: l_assignment_id := ppeoc1.assignment_id ;
4632: l_description := ppeoc1.description ;
4639: SUBSTR('Record Read | '|| p_desc || l_description, 1, 240),
4640: p_process_event_id => l_process_event_id ,
4641: p_object_version_number => l_object_version_number
4642: );
4643: hr_utility.set_location(l_proc, 30);
4644: end loop;
4645: hr_utility.set_location('Leaving:'||l_proc, 50);
4646: end update_pay_process_events;
4647: /****************************************************************************
4641: p_object_version_number => l_object_version_number
4642: );
4643: hr_utility.set_location(l_proc, 30);
4644: end loop;
4645: hr_utility.set_location('Leaving:'||l_proc, 50);
4646: end update_pay_process_events;
4647: /****************************************************************************
4648: name : pqp_windstar_reconcile
4649: Purpose : This procedure reconciles data in pay_process_events table.
4691: l_process_event_id number;
4692: l_object_version_number number;
4693: l_proc varchar2(72) := g_package || 'pqp_windstar_reconcile';
4694: begin
4695: hr_utility.set_location('Entering ' || l_proc, 10);
4696: l_process_event_id := null;
4697: for cppe in c_pay_process_events(p_assignment_id ,
4698: p_effective_date ,
4699: p_source_type )
4697: for cppe in c_pay_process_events(p_assignment_id ,
4698: p_effective_date ,
4699: p_source_type )
4700: loop
4701: hr_utility.set_location(l_proc, 20);
4702: l_process_event_id := cppe.process_event_id ;
4703: l_object_version_number := cppe.object_version_number;
4704: end loop;
4705: hr_utility.set_location('Leaving ' || l_proc, 30);
4701: hr_utility.set_location(l_proc, 20);
4702: l_process_event_id := cppe.process_event_id ;
4703: l_object_version_number := cppe.object_version_number;
4704: end loop;
4705: hr_utility.set_location('Leaving ' || l_proc, 30);
4706: p_process_event_id := l_process_event_id ;
4707: p_object_version_number := l_object_version_number;
4708:
4709: -- Added by tmehra for nocopy changes Feb'03
4709: -- Added by tmehra for nocopy changes Feb'03
4710:
4711: exception
4712: when OTHERS then
4713: hr_utility.set_location('Entering excep:'||l_proc, 35);
4714: p_process_event_id := null;
4715: p_object_version_number := null;
4716:
4717: end pqp_windstar_reconcile;
5105: and change_type = p_source_type
5106: and status in ('N', 'D');
5107:
5108: begin
5109: hr_utility.set_location('Entering:'||l_proc, 5);
5110: begin
5111: l_logic_state := ' while validating data selected from payment_export: ';
5112: --This loop selects Non read records from payment_export table
5113: l_error_message := null;
5199:
5200: l_person_id := null;
5201:
5202: if (p_institution_indiv_id is null) then
5203: hr_utility.set_location(l_proc, 10);
5204: if (p_social_security_number is not null) then
5205: for cps in c_person_ssn(p_social_security_number)
5206: loop
5207: l_person_id := cps.person_id ;
5211: l_person_id := p_institution_indiv_id;
5212: end if;
5213:
5214: if (l_person_id is null) then
5215: hr_utility.set_location(l_proc, 20);
5216: l_error_message := l_error_message ||
5217: 'Person Id could not be determined';
5218: end if;
5219:
5222: -- =========================================================
5223:
5224: l_period_type := null;
5225: if (p_amount_of_addl_wh_type is not null) then
5226: hr_utility.set_location(l_proc, 30);
5227: for cwopp in
5228: c_winstar_oracle_pay_period(p_amount_of_addl_wh_type)
5229: loop
5230: l_period_type := cwopp.meaning;
5241: -- | L | Lump sump | Y | Year |
5242: -- +--------------+--------------+----------------+-----------------+
5243: --
5244: if (l_period_type is null) then
5245: hr_utility.set_location(l_proc, 40);
5246: l_error_message := l_error_message || ' Pay Period is Invalid';
5247: --
5248: -- l_period_type will be null if the value in
5249: -- p_addtnl_wthldng_amt_period_type is not either of M, W, S, B, L.
5268: if (ascii(p_personal_exemption) < 48 or
5269: ascii(p_personal_exemption) > 49 ) then
5270: l_error_message := l_error_message || '(' || 'personal_exemption = '
5271: || p_personal_exemption || ' is invalid.)';
5272: hr_utility.set_location(l_proc, 50);
5273: end if;
5274:
5275: for cpas in c_pri_assgn(l_person_id,
5276: to_date('01/01'||to_char(p_taxyear),'DD/MM/YYYY'))
5274:
5275: for cpas in c_pri_assgn(l_person_id,
5276: to_date('01/01'||to_char(p_taxyear),'DD/MM/YYYY'))
5277: loop
5278: hr_utility.set_location(l_proc, 60);
5279: l_pri_assgn := cpas.assignment_id;
5280: end loop;
5281:
5282: l_maximum_benefit := p_maximum_benefit;
5362: ,p_person_id => l_person_id
5363: ,p_effective_date =>
5364: TO_DATE('01/01' || TO_CHAR(p_taxyear), 'DD/MM/YYYY')
5365: );
5366: hr_utility.set_location(l_proc, 70);
5367: if (l_error_message is null and
5368: pqp_atd_bus.g_error_message is null) then
5369:
5370: hr_utility.set_location(l_proc, 80);
5366: hr_utility.set_location(l_proc, 70);
5367: if (l_error_message is null and
5368: pqp_atd_bus.g_error_message is null) then
5369:
5370: hr_utility.set_location(l_proc, 80);
5371: -- ==============================================
5372: -- W4 record creation
5373: -- ==============================================
5374: -- A W4 record will be created under the following conditions.
5430: NVL(p_constant_additional_tax,0);
5431: end if;
5432:
5433:
5434: hr_utility.set_location(l_proc, 100);
5435: pay_us_web_w4.update_alien_tax_records
5436: -- pay_us_otf_util_web.update_tax_records
5437: (p_filing_status_code => '01'
5438: ,p_allowances => (nvl(p_add_exemptions_allowed, 0) +
5446: ,p_effective_date => p_date_w4_signed
5447: ,p_source_name => 'PQP_US_ALIEN_WINDSTAR'
5448: );
5449:
5450: hr_utility.set_location(l_proc, 110);
5451:
5452: end if;
5453: end if;
5454: --
5469: 'PQP_US_ALIEN_WINDSTAR'
5470: );
5471: fetch c_person_assgn into l_cpa_assignment_id;
5472:
5473: hr_utility.set_location(l_proc, 120);
5474:
5475: if (c_person_assgn%found) then
5476: loop
5477: hr_utility.set_location(l_proc, 130);
5473: hr_utility.set_location(l_proc, 120);
5474:
5475: if (c_person_assgn%found) then
5476: loop
5477: hr_utility.set_location(l_proc, 130);
5478: -- The following cursor checks whether an assignment exists in
5479: -- pqp_alien_data table for a given year or not
5480: --
5481: l_logic_state := ' while inserting in PQP_ANALYZED_ALIEN_DATA:';
5495: -- the pqp_analyzed_alien_data_api table for the given year or not
5496: --
5497: fetch c_assign_exists into l_analyzed_data_id ,
5498: l_analyzed_data_ovn ;
5499: hr_utility.set_location(l_proc, 140);
5500:
5501: if (c_assign_exists%notfound) then
5502:
5503: hr_utility.set_location(l_proc, 150);
5499: hr_utility.set_location(l_proc, 140);
5500:
5501: if (c_assign_exists%notfound) then
5502:
5503: hr_utility.set_location(l_proc, 150);
5504: --
5505: -- if the row does not exist then create a row in
5506: -- analyzed_alien_data table
5507: --
5534: ,p_date_8233_signed => l_date_8233_signed
5535: ,p_effective_date => to_date('01/01/' ||
5536: p_taxyear, 'DD/MM/YYYY')
5537: );
5538: hr_utility.set_location(l_proc, 160);
5539:
5540: else
5541:
5542: hr_utility.set_location(l_proc, 170);
5538: hr_utility.set_location(l_proc, 160);
5539:
5540: else
5541:
5542: hr_utility.set_location(l_proc, 170);
5543:
5544: if (c_assign_exists%isopen = true) then
5545: close c_assign_exists;
5546: end if;
5570: ,p_effective_date => to_date('01/01/' ||
5571: p_taxyear, 'DD/MM/YYYY')
5572: );
5573:
5574: hr_utility.set_location(l_proc, 180);
5575:
5576: end if;
5577: --
5578: -- Alien Details
5590: l_analyzed_det_ovn,
5591: l_retro_lose_ben_amt_flag_old,
5592: l_retro_lose_ben_date_flag_old ;
5593:
5594: hr_utility.set_location(l_proc, 190);
5595: --
5596: -- The following code raises a notification if the actual income
5597: -- code is different from the forecast income code. The notification
5598: -- would be send only if no analyzed data is available for this
5628:
5629:
5630: if (c_analyzed_det_exists%notfound) then
5631:
5632: hr_utility.set_location(l_proc, 200);
5633: if (c_analyzed_det_exists%isopen = true) then
5634: close c_analyzed_det_exists;
5635: end if;
5636:
5664: ,p_effective_date => to_date('01/01/' ||
5665: p_taxyear, 'DD/MM/YYYY')
5666: );
5667:
5668: hr_utility.set_location(l_proc, 210);
5669:
5670: else
5671:
5672: hr_utility.set_location(l_proc, 220);
5668: hr_utility.set_location(l_proc, 210);
5669:
5670: else
5671:
5672: hr_utility.set_location(l_proc, 220);
5673:
5674: if (c_analyzed_det_exists%isopen = true) then
5675: close c_analyzed_det_exists;
5676: end if;
5718: p_treaty_benefits_start_date => p_treatybenefit_startdate ,
5719: p_object_version_number => l_analyzed_det_ovn ,
5720: p_effective_date => TO_DATE('01/01/' || p_taxyear, 'DD/MM/YYYY')
5721: );
5722: hr_utility.set_location(l_proc, 230);
5723: end if;
5724:
5725: if l_current_analysis = 'N' then
5726:
5801: p_source_type => 'PQP_US_ALIEN_WINDSTAR' ,
5802: p_process_event_id => l_process_event_id ,
5803: p_object_version_number => l_process_ovn
5804: );
5805: hr_utility.set_location(l_proc, 240);
5806:
5807: if (l_process_event_id is null and
5808: l_reco_flag = false) then
5809:
5806:
5807: if (l_process_event_id is null and
5808: l_reco_flag = false) then
5809:
5810: hr_utility.set_location(l_proc, 250);
5811: -- l_process_event_id will be null if an assignment id does
5812: -- not exist in the pay_process_events table for PQP_US_WINSTAR
5813: -- or such an assignment exists in the pay_process_events
5814: -- table, but the status of such record is not read
5822: 'Assignment not Reconciled'
5823: ,p_effective_date => TO_DATE('01/01/' ||
5824: p_taxyear, 'DD/MM/YYYY')
5825: );
5826: hr_utility.set_location(l_proc, 260);
5827:
5828: p_error_message := ' Assignment not Reconciled ';
5829:
5830: l_reco_flag := true;
5838: ,p_process_name => 'WIN_PRC'
5839: ,p_process_event_id => null
5840: ) ;
5841:
5842: hr_utility.set_location(l_proc, 270);
5843:
5844: elsif(l_process_event_id is not null and p_reconcile = true)
5845: then --ELSE7}{
5846: hr_utility.set_location(l_proc, 280);
5842: hr_utility.set_location(l_proc, 270);
5843:
5844: elsif(l_process_event_id is not null and p_reconcile = true)
5845: then --ELSE7}{
5846: hr_utility.set_location(l_proc, 280);
5847:
5848: pay_ppe_api.update_process_event
5849: (p_validate => false
5850: ,p_change_type => 'PQP_US_ALIEN_WINDSTAR'
5852: ,p_status => 'C'
5853: ,p_process_event_id => l_process_event_id
5854: ,p_object_version_number=> l_process_ovn
5855: );
5856: hr_utility.set_location(l_proc, 290);
5857: --
5858: -- There might be other open records with a status of 'R' for
5859: -- the same assignment as windstar read process might have
5860: -- read it twice. ie. there could be more than one record with
5873: where ppe.assignment_id = l_pri_assgn
5874: and change_type = 'PQP_US_ALIEN_WINDSTAR'
5875: and ppe.status = 'R';
5876: --
5877: hr_utility.set_location(l_proc, 295);
5878: end;
5879:
5880: begin
5881: select retro_loss_notification_sent
5952: ,p_retro_loss_notification_sent => 'Y'
5953: ,p_object_version_number => l_transaction_ovn
5954: );
5955:
5956: hr_utility.set_location(l_proc, 296);
5957: end if;
5958:
5959: end;
5960: -- Workflow Notification: The control will come to this point
5969: exit when c_person_assgn%notfound;
5970: end loop;
5971:
5972: else
5973: hr_utility.set_location(l_proc, 300);
5974: -- Means no assignment with read/complete status was present in
5975: -- pay_process_events table
5976: pqp_alien_trans_data_api.update_alien_trans_data
5977: (p_validate => false
5980: ,p_error_indicator => 'ERROR : NOT_RECONCILED 2'
5981: ,p_error_text => l_error_message||'Assignment not Reconciled'
5982: ,p_effective_date => TO_DATE('01/01/'||p_taxyear, 'DD/MM/YYYY')
5983: );
5984: hr_utility.set_location(l_proc, 310);
5985:
5986: p_error_message := ' Assignment not Reconciled ';
5987:
5988: pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5993: p_itemtype => 'PQPALNTF' ,
5994: p_process_name => 'WIN_PRC' ,
5995: p_process_event_id => null
5996: ) ;
5997: hr_utility.set_location(l_proc, 320);
5998:
5999: end if;
6000: close c_person_assgn;
6001: else
5998:
5999: end if;
6000: close c_person_assgn;
6001: else
6002: hr_utility.set_location(l_proc, 330);
6003: p_error_message := l_error_message ||pqp_atd_bus.g_error_message;
6004:
6005: pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
6006: (p_alien_transaction_id => l_alien_transaction_id ,
6010: p_itemtype => 'PQPALNTF' ,
6011: p_process_name => 'WIN_PRC' ,
6012: p_process_event_id => null
6013: ) ;
6014: hr_utility.set_location(l_proc, 340);
6015: end if;
6016: exception
6017: when OTHERS then
6018: hr_utility.set_location(l_proc, 350);
6014: hr_utility.set_location(l_proc, 340);
6015: end if;
6016: exception
6017: when OTHERS then
6018: hr_utility.set_location(l_proc, 350);
6019: l_atd_ovn := null;
6020: l_error_message := l_error_message ||TO_CHAR(SQLCODE) ||
6021: SQLERRM ||l_logic_state;
6022: p_error_message := p_error_message || l_error_message;
6027: end loop;
6028:
6029: if (l_atd_ovn is not null) then
6030:
6031: hr_utility.set_location(l_proc, 360);
6032:
6033: pqp_alien_trans_data_api.update_alien_trans_data
6034: (p_validate => false
6035: ,p_alien_transaction_id => l_alien_transaction_id
6039: ,p_effective_date => to_date('01/01/' ||
6040: p_taxyear, 'DD/MM/YYYY')
6041: );
6042:
6043: hr_utility.set_location(l_proc, 370);
6044:
6045: pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
6046: (p_alien_transaction_id => l_alien_transaction_id
6047: ,p_assignment_id => l_pri_assgn
6051: ,p_process_name => 'WIN_PRC'
6052: ,p_process_event_id => null
6053: );
6054:
6055: hr_utility.set_location(l_proc, 380);
6056: end if;
6057: end;
6058:
6059: hr_utility.set_location('Leaving ' || l_proc, 390);
6055: hr_utility.set_location(l_proc, 380);
6056: end if;
6057: end;
6058:
6059: hr_utility.set_location('Leaving ' || l_proc, 390);
6060:
6061: exception
6062: when others then
6063: hr_utility.set_location('Entering excep:'||l_proc, 395);
6059: hr_utility.set_location('Leaving ' || l_proc, 390);
6060:
6061: exception
6062: when others then
6063: hr_utility.set_location('Entering excep:'||l_proc, 395);
6064: p_error_message := p_error_message || sqlerrm;
6065:
6066: end pqp_write_public;
6067:
6079: l_batch_size number;
6080: l_proc constant varchar2(72) := g_package || 'pqp_batch_size';
6081:
6082: begin
6083: hr_utility.set_location('Entering :' || l_proc, 10);
6084:
6085: if (c_pay_action_parameter%isopen = true) then
6086: close c_pay_action_parameter;
6087: end if;
6094: exit when c_pay_action_parameter%notfound;
6095: end loop;
6096: close c_pay_action_parameter;
6097:
6098: hr_utility.set_location(l_proc, 20);
6099:
6100: p_batch_size := l_batch_size;
6101:
6102: if (l_batch_size is null) then
6102: if (l_batch_size is null) then
6103: p_batch_size := null;
6104: end if;
6105:
6106: hr_utility.set_location('Leaving : ' || l_proc, 30);
6107:
6108: exception
6109: when OTHERS then
6110: hr_utility.set_location('Entering excep:'||l_proc, 35);
6106: hr_utility.set_location('Leaving : ' || l_proc, 30);
6107:
6108: exception
6109: when OTHERS then
6110: hr_utility.set_location('Entering excep:'||l_proc, 35);
6111: p_batch_size := null;
6112:
6113: end pqp_batch_size;
6114:
6127: l_proc constant varchar2(72) := g_package || 'ResetForReadAPI';
6128:
6129: begin
6130: l_ovn := -1;
6131: hr_utility.set_location('Entering:'||l_proc, 5);
6132:
6133: for cop in c_ovn_ppe(p_process_event_id)
6134: loop
6135: l_ovn := cop.object_version_number;
6135: l_ovn := cop.object_version_number;
6136: end loop;
6137: if (l_ovn is not null and l_ovn <> -1) then
6138:
6139: hr_utility.set_location(l_proc, 10);
6140:
6141: pay_ppe_api.update_process_event
6142: (p_validate => false
6143: ,p_status => 'N'
6147: );
6148:
6149: end if;
6150:
6151: hr_utility.set_location('Leaving:'||l_proc, 20);
6152:
6153: end ResetForReadAPI;
6154:
6155: -- =============================================================================
6167: l_proc constant varchar2(72) := g_package || 'AbortReadAPI';
6168:
6169: begin
6170:
6171: hr_utility.set_location('Entering:'||l_proc, 5);
6172:
6173: for cop in c_ovn_ppe(p_process_event_id)
6174: loop
6175: l_ovn := cop.object_version_number;
6176: end loop;
6177:
6178: if (l_ovn is not null and l_ovn <> -1) then
6179:
6180: hr_utility.set_location(l_proc, 10);
6181:
6182: pay_ppe_api.update_process_event
6183: (p_validate => false
6184: ,p_status => 'C'
6188: );
6189:
6190: end if;
6191:
6192: hr_utility.set_location('Leaving:' ||l_proc, 20);
6193:
6194: end AbortReadAPI;
6195:
6196: