[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: -- ===========================================================================
1172:
1173: l_proc constant varchar2(150) := g_package||'Insert_Pay_Process_Events';
1174:
1175: begin
1176: hr_utility.set_location('Entering:'||l_proc, 5);
1177: --
1178: -- Get the start and end date of year for the effective date passed.
1179: --
1180: l_start_date
1183: := to_date(('12/31/'||to_char(p_effective_date,'YYYY')),'MM/DD/YYYY');
1184:
1185: if p_type = 'ALL' then
1186:
1187: hr_utility.set_location(l_proc, 10);
1188: --
1189: for apfc in all_people_f_cursor_n
1190: (c_start_date => l_start_date
1191: ,c_end_date => l_end_date
1194:
1195: loop
1196:
1197: begin
1198: hr_utility.set_location(l_proc, 20);
1199: pay_ppe_api.create_process_event
1200: (p_validate => false
1201: ,p_assignment_id => apfc.assignment_id
1202: ,p_effective_date => apfc.effective_start_date
1205: ,p_description => '| Inserted thru PL/SQL Code |'
1206: ,p_process_event_id => l_process_event_id
1207: ,p_object_version_number => l_object_version_number
1208: );
1209: hr_utility.set_location(l_proc, 30);
1210: exception
1211: when others then
1212: hr_utility.set_location(l_proc, 40);
1213: null;
1208: );
1209: hr_utility.set_location(l_proc, 30);
1210: exception
1211: when others then
1212: hr_utility.set_location(l_proc, 40);
1213: null;
1214: end;
1215:
1216: end loop;
1223: ,c_effective_date => p_effective_date)
1224: loop
1225:
1226: begin
1227: hr_utility.set_location(l_proc, 50);
1228: pay_ppe_api.update_process_event
1229: (p_validate => false
1230: ,p_status => 'N'
1231: ,p_description => null
1231: ,p_description => null
1232: ,p_process_event_id => apfc.process_event_id
1233: ,p_object_version_number => apfc.object_version_number
1234: );
1235: hr_utility.set_location(l_proc, 60);
1236: exception
1237: when others then
1238: hr_utility.set_location(l_proc, 70);
1239: null;
1234: );
1235: hr_utility.set_location(l_proc, 60);
1236: exception
1237: when others then
1238: hr_utility.set_location(l_proc, 70);
1239: null;
1240: end;
1241:
1242: end loop;
1240: end;
1241:
1242: end loop;
1243: else
1244: hr_utility.set_location(l_proc, 80);
1245: --
1246: for c1 in ssn_cursor_n (c_start_date => l_start_date
1247: ,c_end_date => l_end_date
1248: ,c_national_indentifier => p_type
1249: ,c_effective_date => p_effective_date)
1250: loop
1251:
1252: begin
1253: hr_utility.set_location(l_proc, 90);
1254: pay_ppe_api.create_process_event
1255: (p_validate => false
1256: ,p_assignment_id => c1.assignment_id
1257: ,p_effective_date => c1.effective_start_date
1260: ,p_description => '| Inserted thru PL/SQL Code |'
1261: ,p_process_event_id => l_process_event_id
1262: ,p_object_version_number => l_object_version_number
1263: );
1264: hr_utility.set_location(l_proc, 100);
1265: exception
1266: when others then
1267: hr_utility.set_location(l_proc, 110);
1268: null;
1263: );
1264: hr_utility.set_location(l_proc, 100);
1265: exception
1266: when others then
1267: hr_utility.set_location(l_proc, 110);
1268: null;
1269: end;
1270:
1271: end loop;
1277: ,c_effective_date => p_effective_date)
1278: loop
1279:
1280: begin
1281: hr_utility.set_location(l_proc, 120);
1282: pay_ppe_api.update_process_event
1283: (p_validate => false
1284: ,p_status => 'N'
1285: ,p_description => null
1285: ,p_description => null
1286: ,p_process_event_id => c1.process_event_id
1287: ,p_object_version_number => c1.object_version_number
1288: );
1289: hr_utility.set_location(l_proc, 130);
1290: exception
1291: when others then
1292: hr_utility.set_location(l_proc, 140);
1293: null;
1288: );
1289: hr_utility.set_location(l_proc, 130);
1290: exception
1291: when others then
1292: hr_utility.set_location(l_proc, 140);
1293: null;
1294: end;
1295:
1296: end loop;
1296: end loop;
1297: --
1298: end if;
1299:
1300: hr_utility.set_location('Leaving:'||l_proc, 150);
1301:
1302: exception
1303: when others then
1304: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1300: hr_utility.set_location('Leaving:'||l_proc, 150);
1301:
1302: exception
1303: when others then
1304: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1305: hr_utility.set_message_token('2',
1306: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1307: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1308: hr_utility.set_location('Leaving :'||l_proc, 160);
1301:
1302: exception
1303: when others then
1304: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1305: hr_utility.set_message_token('2',
1306: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1307: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1308: hr_utility.set_location('Leaving :'||l_proc, 160);
1309: hr_utility.raise_error;
1304: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
1305: hr_utility.set_message_token('2',
1306: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1307: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1308: hr_utility.set_location('Leaving :'||l_proc, 160);
1309: hr_utility.raise_error;
1310:
1311: end insert_pay_process_events;
1312:
1305: hr_utility.set_message_token('2',
1306: 'Error in pqp_alien_expat_taxation_pkg.insert_pay_process_'||
1307: 'events. Error Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
1308: hr_utility.set_location('Leaving :'||l_proc, 160);
1309: hr_utility.raise_error;
1310:
1311: end insert_pay_process_events;
1312:
1313: /**************************************************************************
1411: and NVL(hrsckf.segment18,assign.location_id) = hrlock.location_id;
1412:
1413: l_proc varchar2(72) := g_package||'address_select' ;
1414: begin --}{
1415: hr_utility.set_location('Entering:'||l_proc, 10);
1416:
1417: /* Person Address Details */
1418: if (UPPER(p_work_home) = 'HOME') then
1419: hr_utility.set_location(l_proc, 20);
1415: hr_utility.set_location('Entering:'||l_proc, 10);
1416:
1417: /* Person Address Details */
1418: if (UPPER(p_work_home) = 'HOME') then
1419: hr_utility.set_location(l_proc, 20);
1420: open home_address_cur;
1421: fetch home_address_cur into
1422: p_county ,
1423: p_state ,
1428: p_telephone_number_1 ,
1429: p_telephone_number_2 ,
1430: p_telephone_number_3 ,
1431: p_postal_code ;
1432: hr_utility.set_location('Entering:'||l_proc, 30);
1433: if (home_address_cur%notfound) then
1434: p_county := '' ;
1435: p_state := '' ;
1436: p_city := '' ;
1441: p_telephone_number_2 := '' ;
1442: p_telephone_number_3 := '' ;
1443: p_postal_code := '' ;
1444: p_work_home := 'NOT_FOUND';
1445: hr_utility.set_location(l_proc, 40);
1446: end if;
1447: close home_address_cur;
1448: elsif (UPPER(p_work_home) = 'WORK') then
1449: hr_utility.set_location(l_proc, 50);
1445: hr_utility.set_location(l_proc, 40);
1446: end if;
1447: close home_address_cur;
1448: elsif (UPPER(p_work_home) = 'WORK') then
1449: hr_utility.set_location(l_proc, 50);
1450: open work_address_cur;
1451: fetch work_address_cur into
1452: p_county ,
1453: p_state ,
1458: p_telephone_number_1 ,
1459: p_telephone_number_2 ,
1460: p_telephone_number_3 ,
1461: p_postal_code ;
1462: hr_utility.set_location(l_proc, 60);
1463: if (work_address_cur%notfound) then
1464: p_county := '' ;
1465: p_state := '' ;
1466: p_city := '' ;
1471: p_telephone_number_2 := '' ;
1472: p_telephone_number_3 := '' ;
1473: p_postal_code := '' ;
1474: p_work_home := 'NOT_FOUND' ;
1475: hr_utility.set_location(l_proc, 70);
1476: end if;
1477: close work_address_cur;
1478: end if;
1479: hr_utility.set_location('Leaving:'||l_proc, 80);
1475: hr_utility.set_location(l_proc, 70);
1476: end if;
1477: close work_address_cur;
1478: end if;
1479: hr_utility.set_location('Leaving:'||l_proc, 80);
1480: exception
1481: when OTHERS then
1482: hr_utility.set_location('Entering exc:'||l_proc, 90);
1483: p_county := '' ;
1478: end if;
1479: hr_utility.set_location('Leaving:'||l_proc, 80);
1480: exception
1481: when OTHERS then
1482: hr_utility.set_location('Entering exc:'||l_proc, 90);
1483: p_county := '' ;
1484: p_state := '' ;
1485: p_city := '' ;
1486: p_address_line1 := '' ;
1549: is
1550: l_spouse_here varchar2(1) := 'N';
1551: l_proc varchar2(72) := g_package||'spouse_here';
1552: begin -- {
1553: hr_utility.set_location('Entering '||l_proc, 10);
1554: for csh1 in c_person_visit_spouse_info(p_person_id ,
1555: p_effective_date)
1556:
1557: loop
1555: p_effective_date)
1556:
1557: loop
1558: l_spouse_here := csh1.spouse_accompanied;
1559: hr_utility.set_location('Leaving '||l_proc, 20);
1560: end loop;
1561: hr_utility.set_location('Leaving '||l_proc, 30);
1562: return l_spouse_here;
1563: end spouse_here;
1557: loop
1558: l_spouse_here := csh1.spouse_accompanied;
1559: hr_utility.set_location('Leaving '||l_proc, 20);
1560: end loop;
1561: hr_utility.set_location('Leaving '||l_proc, 30);
1562: return l_spouse_here;
1563: end spouse_here;
1564: -- =============================================================================
1565: -- name : PQP_Windstar_Person_Read
1957: l_year_start_date date;
1958: l_year_end_date date;
1959:
1960: begin
1961: hr_utility.set_location('Entering '||l_proc, 5);
1962:
1963: l_year_start_date := to_date(('01/01/'||to_char(p_effective_date, 'YYYY'))
1964: ,'MM/DD/YYYY');
1965: l_year_end_date := to_date(('12/31/'||to_char(p_effective_date, 'YYYY'))
1970: -- procedure. The Error is to show user that a blank/Null Source
1971: -- Type has been passed.
1972: --
1973: if (p_source_type is null) then
1974: hr_utility.set_location('Entering '||l_proc, 6);
1975: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1976: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1977: hr_utility.set_message_token
1978: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1971: -- Type has been passed.
1972: --
1973: if (p_source_type is null) then
1974: hr_utility.set_location('Entering '||l_proc, 6);
1975: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1976: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1977: hr_utility.set_message_token
1978: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1979: hr_utility.raise_error;
1972: --
1973: if (p_source_type is null) then
1974: hr_utility.set_location('Entering '||l_proc, 6);
1975: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1976: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1977: hr_utility.set_message_token
1978: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1979: hr_utility.raise_error;
1980:
1973: if (p_source_type is null) then
1974: hr_utility.set_location('Entering '||l_proc, 6);
1975: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1976: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1977: hr_utility.set_message_token
1978: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1979: hr_utility.raise_error;
1980:
1981: elsif (p_source_type <> 'PQP_US_ALIEN_WINDSTAR') then
1975: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
1976: hr_utility.set_message_token('ARGUMENT', 'Source Type');
1977: hr_utility.set_message_token
1978: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1979: hr_utility.raise_error;
1980:
1981: elsif (p_source_type <> 'PQP_US_ALIEN_WINDSTAR') then
1982: hr_utility.set_location('Entering '||l_proc, 6);
1983: --
1978: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1979: hr_utility.raise_error;
1980:
1981: elsif (p_source_type <> 'PQP_US_ALIEN_WINDSTAR') then
1982: hr_utility.set_location('Entering '||l_proc, 6);
1983: --
1984: -- raise error message as this package caters to PQP_US_ALIEN_WINDSTAR
1985: -- only as of now. Error is to show user that Invalid Source
1986: -- Type has been passed.
1984: -- raise error message as this package caters to PQP_US_ALIEN_WINDSTAR
1985: -- only as of now. Error is to show user that Invalid Source
1986: -- Type has been passed.
1987: --
1988: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1989: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1990: hr_utility.set_message_token
1991: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1992: hr_utility.raise_error;
1985: -- only as of now. Error is to show user that Invalid Source
1986: -- Type has been passed.
1987: --
1988: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1989: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1990: hr_utility.set_message_token
1991: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1992: hr_utility.raise_error;
1993: end if;
1986: -- Type has been passed.
1987: --
1988: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1989: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1990: hr_utility.set_message_token
1991: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1992: hr_utility.raise_error;
1993: end if;
1994:
1988: hr_utility.set_message(800, 'HR_7462_PLK_INVLD_VALUE');
1989: hr_utility.set_message_token('COLUMN_NAME', 'Source Type');
1990: hr_utility.set_message_token
1991: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1992: hr_utility.raise_error;
1993: end if;
1994:
1995: hr_utility.set_location(l_proc, 10);
1996: --
1991: ('API_NAME','pqp_alien_expat_taxation_pkg.pqp_windstar_person_read');
1992: hr_utility.raise_error;
1993: end if;
1994:
1995: hr_utility.set_location(l_proc, 10);
1996: --
1997: -- The following BEGIN...END block is used so that the error generated
1998: -- due to the above error condition is not trapped
1999: --
1997: -- The following BEGIN...END block is used so that the error generated
1998: -- due to the above error condition is not trapped
1999: --
2000: if (p_source_type = 'PQP_US_ALIEN_WINDSTAR') then
2001: hr_utility.set_location(l_proc, 15);
2002:
2003: if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2004: hr_utility.set_location(l_proc, 20);
2005: open pay_process_events_cursor
2000: if (p_source_type = 'PQP_US_ALIEN_WINDSTAR') then
2001: hr_utility.set_location(l_proc, 15);
2002:
2003: if (p_selection_criterion = 'PAY_PROCESS_EVENTS' ) then
2004: hr_utility.set_location(l_proc, 20);
2005: open pay_process_events_cursor
2006: (c_year_start_date => l_year_start_date
2007: ,c_year_end_date => l_year_end_date
2008: ,p_source_type => p_source_type);
2007: ,c_year_end_date => l_year_end_date
2008: ,p_source_type => p_source_type);
2009:
2010: elsif (p_selection_criterion = 'ALL' ) then
2011: hr_utility.set_location(l_proc, 25);
2012: open per_people_f_cursor(p_effective_date);
2013:
2014: else
2015: hr_utility.set_location(l_proc, 30);
2011: hr_utility.set_location(l_proc, 25);
2012: open per_people_f_cursor(p_effective_date);
2013:
2014: else
2015: hr_utility.set_location(l_proc, 30);
2016: --
2017: -- Else executes when p_selection_criterion is neither 'ALL' nor
2018: -- 'PAY_PROCESS_EVENTS'. Program flow assumes that the
2019: -- NOT NULL string present in the p_selection_criterion is a valid
2024: p_selection_criterion );
2025: end if;
2026: end if;
2027:
2028: hr_utility.set_location(l_proc, 35);
2029:
2030: -- Counter for the t_people_tab - PL/SQL table
2031: i := 1;
2032: -- Counter for the t_error_tab - PL/SQL table
2033: l_err_count := 0;
2034:
2035: if (p_selection_criterion = 'ALL') then
2036:
2037: hr_utility.set_location(l_proc, 40);
2038: --
2039: -- Insert_Pay_Process_Events procedure inserts into pay_process_events
2040: -- table. The records are inserted in this table for the
2041: -- reconciliation purpose.
2080: ,l_suffix
2081: ,l_marital_status
2082: ,l_person_id;
2083:
2084: hr_utility.set_location(l_proc, 50);
2085:
2086: exit when per_people_f_cursor%notfound;
2087:
2088: l_person_read_count := l_person_read_count + 1;
2102: l_telephone_number_2 ,
2103: l_telephone_number_3 ,
2104: l_postal_code);
2105:
2106: hr_utility.set_location(l_proc, 60);
2107:
2108: t_people_tab(i).last_name := l_last_name ;
2109: t_people_tab(i).first_name := l_first_name ;
2110: t_people_tab(i).middle_names := l_middle_names ;
2131: for c_passport in c_person_passport_info(l_person_id)
2132: loop
2133: t_people_tab(i).passport_number := c_passport.passport_number;
2134: end loop;
2135: hr_utility.set_location(l_proc, 70);
2136:
2137: for c_additional in c_person_additional_info(l_person_id)
2138: loop
2139: t_people_tab(i).citizenship_c_code
2159: t_people_tab(i).date_first_entered_us :=
2160: fnd_date.canonical_to_date(c_additional.first_entry_date);
2161: end loop;
2162:
2163: hr_utility.set_location(l_proc, 80);
2164: --
2165: -- to fetch the complete non us address
2166: --
2167: for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2176: t_people_tab(i).non_us_region_postal_cd := c_non_us_addr.non_us_region_postal_cd;
2177: t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2178: end loop;
2179:
2180: hr_utility.set_location(l_proc, 90);
2181: --
2182: -- After a row in PL/SQL table t_people_tab is populated, we pass
2183: -- the just filled row of PL/SQL table to the validation proc
2184: -- pqp_windstar_person_validate
2187: (p_in_data_rec => t_people_tab(i)
2188: ,p_effective_date => p_effective_date
2189: ,p_out_mesg => l_out_mesg
2190: );
2191: hr_utility.set_location(l_proc, 100);
2192: --
2193: -- t_people_tab PL/SQL table cannot be modified in
2194: -- pqp_windstar_person_validate procedure. c_country_code_xlat_cursor
2195: -- cursor will translate the Oracle Application country code to
2229: end loop;
2230: end if;
2231: end if;
2232:
2233: hr_utility.set_location(l_proc, 110);
2234: --
2235: -- A warning message is appended to the description field of the
2236: -- pay_process_events table, if the Non US Country code is not a
2237: -- valid tax country code
2254: t_people_tab(i).non_us_country_code ||
2255: '] may be Invalid |';
2256: end if;
2257: end if;
2258: hr_utility.set_location(l_proc, 120);
2259: --
2260: -- Delete the current row in the PL/SQL table. Update the status
2261: -- in the pay_process_events table to reflect the status as
2262: -- DATA_VALIDATION_FAILED. The row is deleted as we do not want
2275: l_process_event_id ,
2276: l_object_version_number ,
2277: l_assignment_id ,
2278: l_description ;
2279: hr_utility.set_location(l_proc, 130);
2280:
2281: exit when pay_process_events_ovn_cursor%notfound;
2282:
2283: if (l_out_mesg is null) then
2296: ,p_description1 => substr(l_description || l_warn_mesg, 1, 240)
2297: );
2298: end if;
2299:
2300: hr_utility.set_location(l_proc, 140);
2301: -- If the warning message is NOT null, then we do not
2302: -- change the status to D as this is just a warning
2303: else
2304: --
2313: ,p_description1 => SUBSTR(l_out_mesg || l_warn_mesg ||
2314: l_description, 1, 240)
2315: );
2316: end if;
2317: hr_utility.set_location(l_proc, 150);
2318:
2319: end loop;
2320:
2321: close pay_process_events_ovn_cursor;
2321: close pay_process_events_ovn_cursor;
2322:
2323: if (l_out_mesg is null) then
2324: i := i + 1;
2325: hr_utility.set_location(l_proc, 160);
2326: else
2327: hr_utility.set_location(l_proc, 170);
2328: --
2329: -- for wf notification consolidation
2323: if (l_out_mesg is null) then
2324: i := i + 1;
2325: hr_utility.set_location(l_proc, 160);
2326: else
2327: hr_utility.set_location(l_proc, 170);
2328: --
2329: -- for wf notification consolidation
2330: --
2331: if l_process_event_id is not null then
2344: end if; -- if (l_out_mesg
2345:
2346: exception
2347: when others then
2348: hr_utility.set_location(l_proc, 180);
2349: l_person_id := t_people_tab(i).person_id;
2350: l_out_mesg := SUBSTR('Oracle Error ' || TO_CHAR(sqlcode) ||
2351: sqlerrm, 1, 240);
2352: if (t_people_tab.exists(i)) then
2368: ,l_object_version_number
2369: ,l_assignment_id
2370: ,l_description;
2371:
2372: hr_utility.set_location(l_proc, 190);
2373:
2374: exit when pay_process_events_ovn_cursor%notfound;
2375:
2376: pqp_process_events_errorlog
2437: ,l_suffix
2438: ,l_marital_status
2439: ,l_person_id;
2440:
2441: hr_utility.set_location(l_proc, 200);
2442:
2443: exit when pay_process_events_cursor%notfound;
2444:
2445: l_person_read_count := l_person_read_count + 1;
2458: l_telephone_number_2 ,
2459: l_telephone_number_3 ,
2460: l_postal_code);
2461:
2462: hr_utility.set_location(l_proc, 210);
2463:
2464: t_people_tab(i).last_name := l_last_name ;
2465: t_people_tab(i).first_name := l_first_name ;
2466: t_people_tab(i).middle_names := l_middle_names ;
2487: loop
2488: t_people_tab(i).passport_number := c_passport.passport_number;
2489: end loop;
2490:
2491: hr_utility.set_location(l_proc, 220);
2492:
2493: for c_additional in c_person_additional_info(l_person_id)
2494: loop
2495: t_people_tab(i).citizenship_c_code:=
2515: t_people_tab(i).date_first_entered_us :=
2516: fnd_date.canonical_to_date(c_additional.first_entry_date);
2517: end loop;
2518:
2519: hr_utility.set_location(l_proc, 230);
2520:
2521: -- to fetch the complete non us address
2522:
2523: for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2531: t_people_tab(i).non_us_region := c_non_us_addr.non_us_region;
2532: t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2533: end loop;
2534:
2535: hr_utility.set_location(l_proc, 240);
2536: -- After a row in PL/SQL table t_people_tab is populated, we pass
2537: -- the just filled row of PL/SQL table to the validation proc
2538: -- pqp_windstar_person_validate
2539: --
2542: ,p_effective_date => p_effective_date
2543: ,p_out_mesg => l_out_mesg
2544: );
2545:
2546: hr_utility.set_location(l_proc, 250);
2547: -- t_people_tab PL/SQL table cannot be modified in pqp_windstar_person_validate
2548: -- procedure. c_country_code_xlat_cursor cursor will translate the
2549: -- Oracle Application country code to a valid IRS country code
2550: --
2561: := SUBSTR(c1_xlat.meaning,5,length(c1_xlat.meaning)) ;
2562:
2563: end loop;
2564: end if;
2565: hr_utility.set_location(l_proc, 260);
2566: -- A warning message is appended to the description field of
2567: -- the pay_process_events table, if the Non US Country code
2568: -- is not a valid tax country code
2569:
2584: t_people_tab(i).non_us_country_code ||
2585: '] may be Invalid |';
2586: end if;
2587: end if;
2588: hr_utility.set_location(l_proc, 270);
2589: -- Delete the current row in the PL/SQL table. Update the status in
2590: -- the pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2591: -- The row is deleted as we do not want to insert the row containing
2592: -- an error/validation failure in indv_rev1_temp table.
2603: l_process_event_id ,
2604: l_object_version_number ,
2605: l_assignment_id ,
2606: l_description ;
2607: hr_utility.set_location(l_proc, 280);
2608:
2609: exit when pay_process_events_ovn_cursor%notfound;
2610:
2611: if (l_out_mesg is null) then
2608:
2609: exit when pay_process_events_ovn_cursor%notfound;
2610:
2611: if (l_out_mesg is null) then
2612: hr_utility.set_location(l_proc, 290);
2613: -- l_out_mesg = NULL means that there was no failure.
2614: -- Increment the counter and proceed for fetching of the
2615: -- next row from the respective cursor. We therefore do
2616: -- NOT change the status. The status is changed from N to R
2616: -- NOT change the status. The status is changed from N to R
2617: -- after a row in inserted in ten42s_state_temp table
2618:
2619: if (l_warn_mesg is not null) then
2620: hr_utility.set_location(l_proc, 300);
2621:
2622: pqp_process_events_errorlog
2623: (p_process_event_id1 => l_process_event_id
2624: ,p_object_version_number1 => l_object_version_number
2627: l_warn_mesg, 1, 240)
2628: );
2629: end if;
2630: else
2631: hr_utility.set_location(l_proc, 310);
2632: -- Since l_out_mesg is NOT NULL, that means an error was
2633: -- detected. We therefore change the status of the
2634: -- pay_process_events table to 'D' meaning DATA_VALIDATION_FAILED
2635: --
2644: end loop;
2645: close pay_process_events_ovn_cursor;
2646: if (l_out_mesg is null) then
2647: i := i + 1;
2648: hr_utility.set_location(l_proc, 320);
2649: else
2650: -- for wf notification consolidation
2651: if l_process_event_id is not null then
2652: l_err_count := l_err_count+1;
2658: t_people_tab.delete(i) ;
2659: l_person_err_count := l_person_err_count + 1;
2660: l_out_mesg := null;
2661: l_warn_mesg := null;
2662: hr_utility.set_location(l_proc, 330);
2663:
2664: end if;
2665: exception
2666: when others then
2663:
2664: end if;
2665: exception
2666: when others then
2667: hr_utility.set_location(l_proc, 340);
2668: l_person_id := t_people_tab(i).person_id;
2669: --
2670: if (t_people_tab.exists(i)) then
2671: t_people_tab.delete(i) ;
2687: l_process_event_id ,
2688: l_object_version_number ,
2689: l_assignment_id ,
2690: l_description ;
2691: hr_utility.set_location(l_proc, 350);
2692:
2693: exit when pay_process_events_ovn_cursor%notfound;
2694: pqp_process_events_errorlog
2695: (
2716: close pay_process_events_cursor;
2717:
2718: else
2719: -- Executing the code for a particular National Identifier
2720: hr_utility.set_location(l_proc, 360);
2721: /******
2722: insert_pay_process_events procedure inserts into pay_process_events
2723: table. the records are inserted in this table for the reconciliation purpose.
2724: *****/
2759: l_title ,
2760: l_suffix ,
2761: l_marital_status ,
2762: l_person_id ;
2763: hr_utility.set_location(l_proc, 370);
2764:
2765: exit when national_identifier_cursor%notfound;
2766: l_person_read_count := l_person_read_count + 1;
2767:
2779: l_telephone_number_1 ,
2780: l_telephone_number_2 ,
2781: l_telephone_number_3 ,
2782: l_postal_code );
2783: hr_utility.set_location(l_proc, 380);
2784:
2785: t_people_tab(i).last_name := l_last_name ;
2786: t_people_tab(i).first_name := l_first_name ;
2787: t_people_tab(i).middle_names := l_middle_names ;
2806:
2807: for c_passport in c_person_passport_info(l_person_id) loop
2808: t_people_tab(i).passport_number := c_passport.passport_number;
2809: end loop;
2810: hr_utility.set_location(l_proc, 390);
2811:
2812: for c_additional in c_person_additional_info(l_person_id) loop
2813: t_people_tab(i).citizenship_c_code:=
2814: c_additional.tax_res_country_code;
2832:
2833: t_people_tab(i).date_first_entered_us :=
2834: fnd_date.canonical_to_date(c_additional.first_entry_date);
2835: end loop;
2836: hr_utility.set_location(l_proc, 400);
2837:
2838: /* Added the code to fetch the complete non us address - tmehra 15-OCT-2001 */
2839:
2840: for c_non_us_addr in c_non_us_address_cur(l_person_id ,
2847: t_people_tab(i).non_us_city := c_non_us_addr.non_us_city;
2848: t_people_tab(i).non_us_region := c_non_us_addr.non_us_region;
2849: t_people_tab(i).non_us_country_code := c_non_us_addr.non_us_cc;
2850: end loop;
2851: hr_utility.set_location(l_proc, 410);
2852: /*****
2853: after a row in PL/sql table t_people_tab is populated, we pass the just
2854: filled row of PL/sql table to the validation proc pqp_windstar_person_validate
2855: *****/
2858: p_in_data_rec => t_people_tab(i) ,
2859: p_effective_date => p_effective_date ,
2860: p_out_mesg => l_out_mesg
2861: );
2862: hr_utility.set_location(l_proc, 420);
2863: /*****
2864: t_people_tab PL/sql table cannot be modified in pqp_windstar_person_validate
2865: procedure. c_country_code_xlat_cursor cursor will translate the Oracle
2866: Application country code to a valid IRS country code
2880: length(c1_xlat.meaning)) ;
2881:
2882: end loop;
2883: end if;
2884: hr_utility.set_location(l_proc, 430);
2885: /*****
2886: A warning message is appended to the description field of the pay_process_events
2887: table, if the Non US Country code is not a valid tax country code
2888: *****/
2901: t_people_tab(i).non_us_country_code ||
2902: '] may be Invalid |';
2903: end if;
2904: end if;
2905: hr_utility.set_location(l_proc, 440);
2906:
2907: /*****
2908: delete the current row in the PL/sql table. update the status in the
2909: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED.
2922: l_process_event_id ,
2923: l_object_version_number ,
2924: l_assignment_id ,
2925: l_description ;
2926: hr_utility.set_location(l_proc, 450);
2927:
2928: exit when pay_process_events_ovn_cursor%notfound;
2929:
2930: if (l_out_mesg is null) then
2927:
2928: exit when pay_process_events_ovn_cursor%notfound;
2929:
2930: if (l_out_mesg is null) then
2931: hr_utility.set_location(l_proc, 460);
2932: /*****
2933: l_out_mesg = null means that there was no failure. increment the counter
2934: and proceed for fetching of the next row from the respective cursor.
2935: We therefore do not change the status. the status is changed from N to R
2936: after a row in inserted in ten42s_state_temp table
2937: *****/
2938:
2939: if (l_warn_mesg is not null) then
2940: hr_utility.set_location(l_proc, 470);
2941: pqp_process_events_errorlog
2942: (
2943: p_process_event_id1 => l_process_event_id ,
2944: p_object_version_number1 => l_object_version_number,
2947: SUBSTR(l_description || l_warn_mesg, 1, 240)
2948: );
2949: end if;
2950: else
2951: hr_utility.set_location(l_proc, 480);
2952: /*****
2953: Since l_out_mesg is not null, that means an error was detected. We therefore
2954: change the status of the pay_process_events table to 'D' meaning
2955: DATA_VALIDATION_FAILED
2968:
2969: close pay_process_events_ovn_cursor;
2970: if (l_out_mesg is null) then
2971: i := i + 1;
2972: hr_utility.set_location(l_proc, 490);
2973: else
2974: /* Added by tmehra for wf notification consolidation */
2975: if l_process_event_id is not null then
2976: l_err_count := l_err_count+1;
2983: t_people_tab.delete(i) ;
2984: l_person_err_count := l_person_err_count + 1;
2985: l_out_mesg := null;
2986: l_warn_mesg := null;
2987: hr_utility.set_location(l_proc, 500);
2988: end if;
2989: exception
2990: when OTHERS then
2991: hr_utility.set_location(l_proc, 510);
2987: hr_utility.set_location(l_proc, 500);
2988: end if;
2989: exception
2990: when OTHERS then
2991: hr_utility.set_location(l_proc, 510);
2992: l_person_id := t_people_tab(i).person_id;
2993: if (t_people_tab.exists(i)) then
2994: t_people_tab.delete(i) ;
2995: l_person_err_count := l_person_err_count + 1;
3010: l_process_event_id ,
3011: l_object_version_number ,
3012: l_assignment_id ,
3013: l_description ;
3014: hr_utility.set_location(l_proc, 520);
3015:
3016: exit when pay_process_events_ovn_cursor%notfound;
3017: pqp_process_events_errorlog
3018: (
3038: end;
3039: end loop;
3040: close national_identifier_cursor;
3041: end if;
3042: hr_utility.set_location(l_proc, 530);
3043: p_person_read_count := l_person_read_count ;
3044: p_person_err_count := l_person_err_count ;
3045: hr_utility.set_location('Leaving '||l_proc, 540);
3046: exception
3041: end if;
3042: hr_utility.set_location(l_proc, 530);
3043: p_person_read_count := l_person_read_count ;
3044: p_person_err_count := l_person_err_count ;
3045: hr_utility.set_location('Leaving '||l_proc, 540);
3046: exception
3047: when OTHERS then
3048: p_person_read_count := l_person_read_count ;
3049: p_person_err_count := l_person_err_count ;
3046: exception
3047: when OTHERS then
3048: p_person_read_count := l_person_read_count ;
3049: p_person_err_count := l_person_err_count ;
3050: hr_utility.set_location('Leaving '||l_proc, 550);
3051: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3052: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3053: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3054: ' ' || sqlerrm);
3047: when OTHERS then
3048: p_person_read_count := l_person_read_count ;
3049: p_person_err_count := l_person_err_count ;
3050: hr_utility.set_location('Leaving '||l_proc, 550);
3051: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3052: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3053: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3054: ' ' || sqlerrm);
3055: hr_utility.raise_error;
3048: p_person_read_count := l_person_read_count ;
3049: p_person_err_count := l_person_err_count ;
3050: hr_utility.set_location('Leaving '||l_proc, 550);
3051: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3052: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3053: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3054: ' ' || sqlerrm);
3055: hr_utility.raise_error;
3056: end pqp_windstar_person_read;
3051: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3052: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3053: || 'pqp_windstar_person_read. Error Code = ' || TO_CHAR(sqlcode) ||
3054: ' ' || sqlerrm);
3055: hr_utility.raise_error;
3056: end pqp_windstar_person_read;
3057: /***************************************************************************
3058: name : pqp_windstar_balance_read
3059: Purpose : the following procedure is called from the main procedure. This
3393: and ppt.system_person_type ='EX_EMP'
3394: and paf.person_id = ppf.person_id ;
3395:
3396: begin
3397: hr_utility.set_location('Entering '||l_proc, 5);
3398: l_dimension_name := 'Assignment within Government Reporting Entity Year to Date';
3399:
3400: l_year := to_number(to_char(p_effective_date, 'YYYY'));
3401: l_year_start:= to_date('01/01/'||to_char(p_effective_date, 'YYYY'),'MM/DD/YYYY');
3407:
3408: j := 1;
3409:
3410: if l_count >= 1 then
3411: hr_utility.set_location(l_proc, 10);
3412:
3413: l_sit_flag := true;
3414: for i in 1..l_count
3415: loop
3413: l_sit_flag := true;
3414: for i in 1..l_count
3415: loop
3416: begin
3417: hr_utility.set_location(l_proc, 20);
3418: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0') then
3419:
3420: hr_utility.set_location(l_proc, 30);
3421:
3416: begin
3417: hr_utility.set_location(l_proc, 20);
3418: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0') then
3419:
3420: hr_utility.set_location(l_proc, 30);
3421:
3422: l_sit_flag := true;
3423: l_person_id := t_people_tab(i).person_id;
3424: l_last_name := t_people_tab(i).last_name;
3430: for c_additional in c_person_additional_info(l_person_id)
3431: loop
3432: l_tax_residence_country_code :=
3433: c_additional.tax_res_country_code;
3434: hr_utility.set_location(l_proc, 40);
3435: end loop;
3436:
3437: for c_ass in c_person_assignment(l_person_id)
3438: loop
3436:
3437: for c_ass in c_person_assignment(l_person_id)
3438: loop
3439: l_assignment_id := c_ass.assignment_id;
3440: hr_utility.set_location(l_proc, 50);
3441: end loop;
3442:
3443: for c_state in c_work_state_cur(l_assignment_id)
3444: loop
3442:
3443: for c_state in c_work_state_cur(l_assignment_id)
3444: loop
3445: l_state := c_state.state;
3446: hr_utility.set_location(l_proc, 60);
3447: end loop;
3448:
3449: open c_income_code_cursor( l_person_id,
3450: l_year_start,
3478:
3479: l_income_code_count := 1;
3480:
3481: begin
3482: hr_utility.set_location(l_proc, 70);
3483: l_income_code := '';
3484: --
3485: -- The sql below decides if the respective earning entries
3486: -- attached to the assignment. Decide if the request is for
3503: ,l_withholding_rate
3504: ,l_income_code_sub_type
3505: ,l_constant_addl_tax;
3506:
3507: hr_utility.set_location(l_proc, 80);
3508:
3509: l_balance := 0;
3510:
3511: t_balance_tab(j).person_id := l_person_id;
3525: loop
3526: l_prev_amount := c_payment.prev_er_treaty_ben_amt;
3527: end loop;
3528:
3529: hr_utility.set_location(l_proc, 90);
3530:
3531: if (length(l_c_income_code) > 2) then
3532: l_sub_type := substr(l_c_income_code, 3, 1);
3533: end if;
3530:
3531: if (length(l_c_income_code) > 2) then
3532: l_sub_type := substr(l_c_income_code, 3, 1);
3533: end if;
3534: hr_utility.set_location(l_proc, 100);
3535:
3536: if (IsPayrollRun
3537: (l_person_id
3538: ,TO_DATE('31/12/'|| TO_CHAR(l_year),'DD/MM/YYYY')
3537: (l_person_id
3538: ,TO_DATE('31/12/'|| TO_CHAR(l_year),'DD/MM/YYYY')
3539: ,l_income_code) = false) then
3540:
3541: hr_utility.set_location(l_proc, 110);
3542:
3543: l_flag := 'F';
3544: l_balance := pqp_forecasted_balance
3545: (l_person_id ,
3576: p_effective_date => p_effective_date ,
3577: p_out_mesg => l_out_mesg ,
3578: p_forecasted => true
3579: );
3580: hr_utility.set_location(l_proc, 120);
3581: else
3582: hr_utility.set_location(l_proc, 130);
3583: l_flag := 'A';
3584:
3578: p_forecasted => true
3579: );
3580: hr_utility.set_location(l_proc, 120);
3581: else
3582: hr_utility.set_location(l_proc, 130);
3583: l_flag := 'A';
3584:
3585: l_effective_date :=
3586: to_date('31/12/'||to_char(l_year),'DD/MM/YYYY');
3651: t_balance_tab(j).last_date_of_earnings :=
3652: cdpe.date_earned ;
3653: end loop;
3654:
3655: hr_utility.set_location(l_proc, 140);
3656:
3657: t_balance_tab(j).tax_year := l_year ;
3658:
3659: if (l_sit_flag = true) then
3657: t_balance_tab(j).tax_year := l_year ;
3658:
3659: if (l_sit_flag = true) then
3660:
3661: hr_utility.set_location(l_proc, 150);
3662:
3663: l_balance := 0;
3664: l_balance := pqp_balance
3665: (p_income_code => null
3673: t_balance_tab(j).state_withheld_amount := l_balance;
3674: l_sit_flag := false;
3675:
3676: else
3677: hr_utility.set_location(l_proc, 160);
3678:
3679: t_balance_tab(j).state_withheld_amount := 0;
3680: end if;
3681:
3701: t_balance_tab(j).no_of_days_in_cycle
3702: := cdic.days_in_cycle;
3703: end loop;
3704:
3705: hr_utility.set_location(l_proc, 170);
3706:
3707: pqp_windstar_balance_validate
3708: (p_in_data_rec => t_balance_tab(j)
3709: ,p_effective_date => p_effective_date
3710: ,p_out_mesg => l_out_mesg
3711: ,p_forecasted => false
3712: );
3713:
3714: hr_utility.set_location(l_proc, 180);
3715: end if;
3716: if (l_out_mesg is null) then
3717: --
3718: -- l_out_mesg means there is no failure. increment the
3719: -- counter and proceed for fetching of the next row from
3720: -- the respective cursor
3721: --
3722: j := j + 1;
3723: hr_utility.set_location(l_proc, 190);
3724: else --ELSE4}{
3725: hr_utility.set_location(l_proc, 200);
3726: -- Delete the current row in the PL/sql table. update the
3727: -- status in the pay_process_events table to reflect the
3721: --
3722: j := j + 1;
3723: hr_utility.set_location(l_proc, 190);
3724: else --ELSE4}{
3725: hr_utility.set_location(l_proc, 200);
3726: -- Delete the current row in the PL/sql table. update the
3727: -- status in the pay_process_events table to reflect the
3728: -- status as DATA_VALIDATION_FAILED
3729: --
3742: ,l_object_version_number
3743: ,l_assignment_id
3744: ,l_description;
3745:
3746: hr_utility.set_location(l_proc, 210);
3747:
3748: exit when pay_process_events_ovn_cursor%notfound;
3749:
3750: pqp_process_events_errorlog
3777: l_out_mesg := null;
3778: t_people_tab(i).validation_flag := '0';
3779: /* 0 indicates an Error */
3780: exit;
3781: hr_utility.set_location(l_proc, 260);
3782: end if; --ENDIF4}
3783: close c_analyzed_data;
3784: exception
3785: when OTHERS then
3782: end if; --ENDIF4}
3783: close c_analyzed_data;
3784: exception
3785: when OTHERS then
3786: hr_utility.set_location(l_proc, 270);
3787: if (pay_process_events_ovn_cursor%isopen = true) then
3788: close pay_process_events_ovn_cursor;
3789: end if;
3790: if (c_analyzed_data%isopen = true) then
3804: l_object_version_number ,
3805: l_assignment_id ,
3806: l_description ;
3807: exit when pay_process_events_ovn_cursor%notfound;
3808: hr_utility.set_location(l_proc, 280);
3809:
3810: /* Update pay_process_events table with a status of 'D' */
3811:
3812: pqp_process_events_errorlog
3864: l_object_version_number ,
3865: l_assignment_id ,
3866: l_description ;
3867: exit when pay_process_events_ovn_cursor%notfound;
3868: hr_utility.set_location(l_proc, 280);
3869:
3870: /* Update pay_process_events table with a status of 'D' */
3871:
3872: pqp_process_events_errorlog
3896: l_out_mesg := null;
3897:
3898: end if;
3899:
3900: hr_utility.set_location(l_proc, 290);
3901:
3902: end if; --ENDIF2} validation_flag = 0
3903:
3904:
3903:
3904:
3905: exception
3906: when OTHERS then
3907: hr_utility.set_location(l_proc, 300);
3908: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
3909: t_people_tab(i).validation_flag := '0';
3910: t_people_tab(i).error_mesg :=
3911: SUBSTR('Error while processing 1042s details' ||
3913: l_out_mesg := null;
3914: end; --END2}
3915: end loop; --ENDLOOP1}
3916: end if; /* END IF # 1 */ --ENDIF}
3917: hr_utility.set_location('Leaving '||l_proc, 310);
3918: exception --EXC1}{
3919: when OTHERS then
3920: hr_utility.set_location('Entering exc'||l_proc, 320);
3921: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3916: end if; /* END IF # 1 */ --ENDIF}
3917: hr_utility.set_location('Leaving '||l_proc, 310);
3918: exception --EXC1}{
3919: when OTHERS then
3920: hr_utility.set_location('Entering exc'||l_proc, 320);
3921: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3922: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3923: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3924: ' ' || sqlerrm);
3917: hr_utility.set_location('Leaving '||l_proc, 310);
3918: exception --EXC1}{
3919: when OTHERS then
3920: hr_utility.set_location('Entering exc'||l_proc, 320);
3921: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3922: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3923: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3924: ' ' || sqlerrm);
3925: hr_utility.raise_error;
3918: exception --EXC1}{
3919: when OTHERS then
3920: hr_utility.set_location('Entering exc'||l_proc, 320);
3921: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3922: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3923: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3924: ' ' || sqlerrm);
3925: hr_utility.raise_error;
3926: end pqp_windstar_balance_read; --END1}
3921: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
3922: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
3923: || 'pqp_windstar_balance_read. Error Code = ' || TO_CHAR(sqlcode) ||
3924: ' ' || sqlerrm);
3925: hr_utility.raise_error;
3926: end pqp_windstar_balance_read; --END1}
3927: /***************************************************************************
3928: name : pqp_windstar_visa_read
3929: Purpose : the following procedure is called from the main procedure. This
4056: );
4057:
4058: begin
4059:
4060: hr_utility.set_location('Entering '||l_proc, 5);
4061: l_count := t_people_tab.COUNT;
4062: l_err_count := t_error_tab.COUNT;
4063: l_01jan_date := TO_DATE('01/01/'|| TO_CHAR(p_effective_date, 'YYYY'),
4064: 'DD/MM/YYYY');
4067: for i in 1..l_count
4068: loop --LOOP1{
4069: begin
4070:
4071: hr_utility.set_location(l_proc, 10);
4072:
4073: -- Get the errornous record count, Skip this person and raise the notification
4074:
4075: l_skip_person := false;
4091: else
4092: l_skip_person := false;
4093: end if;
4094:
4095: hr_utility.set_location(l_proc, 20);
4096:
4097: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0'
4098: and l_skip_person = false) then --IF1{
4099: hr_utility.set_location(l_proc, 30);
4095: hr_utility.set_location(l_proc, 20);
4096:
4097: if (NVL(t_people_tab(i).validation_flag, ' ') <> '0'
4098: and l_skip_person = false) then --IF1{
4099: hr_utility.set_location(l_proc, 30);
4100:
4101: l_person_id := '' ;
4102: l_last_name := '' ;
4103: l_first_name := '' ;
4114: for c_additional in c_person_additional_info(l_person_id)
4115: loop
4116: l_tax_residence_country_code := c_additional.tax_res_country_code;
4117: end loop;
4118: hr_utility.set_location(l_proc, 40);
4119: l_prev_end_date := null;
4120:
4121: l_visa_found := 'NONE';
4122:
4133: -- means Visa details are available
4134: l_visa_found := 'VISA';
4135:
4136: begin
4137: hr_utility.set_location(l_proc, 50);
4138: t_visa_tab(j).person_id := l_person_id ;
4139: t_visa_tab(j).last_name := l_last_name ;
4140: t_visa_tab(j).first_name := l_first_name ;
4141: t_visa_tab(j).middle_names := l_middle_names ;
4167: /* Means there was no failure. Increment the counter and proceed for
4168: fetching of the next row from the respective cursor */
4169: l_prev_end_date := t_visa_tab(j).visa_end_date;
4170: j := j + 1;
4171: hr_utility.set_location(l_proc, 60);
4172: else --ELSE3}{
4173: /* Delete the current row in the PL/SQL table. Update the status in the
4174: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED
4175: */
4172: else --ELSE3}{
4173: /* Delete the current row in the PL/SQL table. Update the status in the
4174: pay_process_events table to reflect the status as DATA_VALIDATION_FAILED
4175: */
4176: hr_utility.set_location(l_proc, 70);
4177:
4178: l_process_event_id := null;
4179:
4180: open pay_process_events_ovn_cursor(l_person_id ,
4185: l_process_event_id ,
4186: l_object_version_number ,
4187: l_assignment_id ,
4188: l_description ;
4189: hr_utility.set_location(l_proc, 80);
4190: exit when pay_process_events_ovn_cursor%notfound;
4191: /* Update pay_process_events table */
4192: pqp_process_events_errorlog
4193: (
4195: p_object_version_number1 => l_object_version_number ,
4196: p_status1 => 'D' ,
4197: p_description1 => SUBSTR(l_out_mesg, 1, 240)
4198: );
4199: hr_utility.set_location(l_proc, 90);
4200: hr_utility.set_location(l_proc, 100);
4201: end loop;
4202: close pay_process_events_ovn_cursor;
4203: if (t_visa_tab.exists(j)) then
4196: p_status1 => 'D' ,
4197: p_description1 => SUBSTR(l_out_mesg, 1, 240)
4198: );
4199: hr_utility.set_location(l_proc, 90);
4200: hr_utility.set_location(l_proc, 100);
4201: end loop;
4202: close pay_process_events_ovn_cursor;
4203: if (t_visa_tab.exists(j)) then
4204: t_visa_tab.delete(j) ;
4221: ***/
4222: end if; --ENDIF}
4223: exception
4224: when OTHERS then
4225: hr_utility.set_location(l_proc, 110);
4226: if (pay_process_events_ovn_cursor%isopen = true) then
4227: close pay_process_events_ovn_cursor;
4228: end if;
4229: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4238: l_process_event_id ,
4239: l_object_version_number ,
4240: l_assignment_id ,
4241: l_description ;
4242: hr_utility.set_location(l_proc, 120);
4243: exit when pay_process_events_ovn_cursor%notfound;
4244:
4245: /* Update pay_process_events table with a status of 'D' */
4246:
4250: p_object_version_number1 => l_object_version_number ,
4251: p_status1 => 'D' ,
4252: p_description1 => SUBSTR(l_out_mesg, 1, 240)
4253: );
4254: hr_utility.set_location(l_proc, 130);
4255:
4256: end loop;
4257: close pay_process_events_ovn_cursor;
4258: if (t_visa_tab.exists(j)) then
4257: close pay_process_events_ovn_cursor;
4258: if (t_visa_tab.exists(j)) then
4259: t_visa_tab.delete(j) ;
4260: end if;
4261: hr_utility.set_location(l_proc, 140);
4262:
4263: /* Added by tmehra for wf notification consolidation */
4264: if l_process_event_id is not null then
4265: l_err_count := l_err_count+1;
4303: l_object_version_number ,
4304: l_assignment_id ,
4305: l_description ;
4306: exit when pay_process_events_ovn_cursor%notfound;
4307: hr_utility.set_location(l_proc, 280);
4308:
4309: /* Update pay_process_events table with a status of 'D' */
4310:
4311: pqp_process_events_errorlog
4357: l_object_version_number ,
4358: l_assignment_id ,
4359: l_description ;
4360: exit when pay_process_events_ovn_cursor%notfound;
4361: hr_utility.set_location(l_proc, 280);
4362:
4363: /* Update pay_process_events table with a status of 'D' */
4364:
4365: pqp_process_events_errorlog
4394:
4395: end if; --ENDIF1}
4396: exception
4397: when OTHERS then
4398: hr_utility.set_location(l_proc, 150);
4399: l_out_mesg := SUBSTR(TO_CHAR(SQLCODE) || SQLERRM, 1, 240);
4400: t_people_tab(i).validation_flag := '0';
4401: t_people_tab(i).error_mesg :=
4402: SUBSTR('Error while processing visa details' || l_out_mesg,
4404: l_out_mesg := null;
4405: end;
4406:
4407: end loop; --LOOP1 }
4408: hr_utility.set_location('Leaving '||l_proc, 160);
4409: exception
4410: when OTHERS then
4411: hr_utility.set_location('Entering exc'||l_proc, 170);
4412: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4407: end loop; --LOOP1 }
4408: hr_utility.set_location('Leaving '||l_proc, 160);
4409: exception
4410: when OTHERS then
4411: hr_utility.set_location('Entering exc'||l_proc, 170);
4412: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4413: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4414: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4415: ' ' || sqlerrm);
4408: hr_utility.set_location('Leaving '||l_proc, 160);
4409: exception
4410: when OTHERS then
4411: hr_utility.set_location('Entering exc'||l_proc, 170);
4412: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4413: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4414: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4415: ' ' || sqlerrm);
4416: hr_utility.raise_error;
4409: exception
4410: when OTHERS then
4411: hr_utility.set_location('Entering exc'||l_proc, 170);
4412: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4413: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4414: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4415: ' ' || sqlerrm);
4416: hr_utility.raise_error;
4417: end pqp_windstar_visa_read;
4412: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4413: hr_utility.set_message_token('2', 'Error in pqp_alien_expat_taxation_pkg.'
4414: || 'pqp_windstar_visa_read. Error Code = ' || TO_CHAR(sqlcode) ||
4415: ' ' || sqlerrm);
4416: hr_utility.raise_error;
4417: end pqp_windstar_visa_read;
4418: /****************************************************************************
4419: name : pqp_read_public
4420: Purpose : the following is the main procedure that is called from a
4462: -- added by tmehra for wf notification consolidation
4463: t_error_tab t_error_tab_type ;
4464:
4465: begin
4466: hr_utility.set_location('Entering:'||l_proc, 5);
4467:
4468: /*****
4469: raise error message as Selection Criterion cannot be null
4470: ******/
4469: raise error message as Selection Criterion cannot be null
4470: ******/
4471:
4472: if (p_selection_criterion is null) then
4473: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4474: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4475: hr_utility.set_message_token('API_NAME',
4476: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4477: hr_utility.raise_error;
4470: ******/
4471:
4472: if (p_selection_criterion is null) then
4473: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4474: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4475: hr_utility.set_message_token('API_NAME',
4476: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4477: hr_utility.raise_error;
4478: end if;
4471:
4472: if (p_selection_criterion is null) then
4473: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4474: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4475: hr_utility.set_message_token('API_NAME',
4476: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4477: hr_utility.raise_error;
4478: end if;
4479: begin
4473: hr_utility.set_message(800, 'HR_7207_API_MANDATORY_ARG');
4474: hr_utility.set_message_token('ARGUMENT', 'Selection Criterion');
4475: hr_utility.set_message_token('API_NAME',
4476: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4477: hr_utility.raise_error;
4478: end if;
4479: begin
4480: hr_utility.set_location(l_proc, 10);
4481: /*****
4476: 'pqp_alien_expat_taxation_pkg.pqp_read_public');
4477: hr_utility.raise_error;
4478: end if;
4479: begin
4480: hr_utility.set_location(l_proc, 10);
4481: /*****
4482: call pqp_windstar_person_read procedure to read all the information about
4483: the person into PL/sql t_people_tab table.
4484: ******/
4489: t_people_tab => t_people_tab ,
4490: t_error_tab => t_error_tab ,
4491: p_person_read_count => l_person_read_count ,
4492: p_person_err_count => l_person_err_count );
4493: hr_utility.set_location(l_proc, 20);
4494:
4495: p_person_read_count := l_person_read_count;
4496: p_person_err_count := l_person_err_count;
4497:
4507: t_error_tab ,
4508: 'PQP_US_ALIEN_WINDSTAR' ,
4509: p_effective_date ,
4510: t_visa_tab );
4511: hr_utility.set_location(l_proc, 30);
4512:
4513: /*****
4514: call pqp_windstar_balance_read procedure to read all the information about
4515: the balance into PL/sql t_balance_tab table.
4518: t_error_tab ,
4519: 'PQP_US_ALIEN_WINDSTAR' ,
4520: p_effective_date ,
4521: t_balance_tab );
4522: hr_utility.set_location(l_proc, 40);
4523:
4524: l_count := t_error_tab.COUNT;
4525:
4526: /*****
4542: end loop;
4543:
4544: exception
4545: when OTHERS then
4546: hr_utility.set_location('Entering exception:'||l_proc, 50);
4547: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4548: hr_utility.set_message_token('2', 'Error in '
4549: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4550: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4543:
4544: exception
4545: when OTHERS then
4546: hr_utility.set_location('Entering exception:'||l_proc, 50);
4547: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4548: hr_utility.set_message_token('2', 'Error in '
4549: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4550: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4551: hr_utility.raise_error;
4544: exception
4545: when OTHERS then
4546: hr_utility.set_location('Entering exception:'||l_proc, 50);
4547: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4548: hr_utility.set_message_token('2', 'Error in '
4549: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4550: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4551: hr_utility.raise_error;
4552: end;
4547: hr_utility.set_message(800, 'DTU10_GENERAL_ORACLE_ERROR');
4548: hr_utility.set_message_token('2', 'Error in '
4549: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4550: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4551: hr_utility.raise_error;
4552: end;
4553: hr_utility.set_location(l_proc, 60);
4554:
4555: begin
4549: || 'pqp_alien_expat_taxation_pkg.pqp_read_public. Error '
4550: || 'Code = ' || TO_CHAR(Sqlcode) || ' ' || sqlerrm);
4551: hr_utility.raise_error;
4552: end;
4553: hr_utility.set_location(l_proc, 60);
4554:
4555: begin
4556: open c_pay_action_parameter;
4557: l_batch_size := null;
4561: exit when c_pay_action_parameter%notfound;
4562: end loop;
4563: p_batch_size := l_batch_size;
4564: close c_pay_action_parameter;
4565: hr_utility.set_location('Leaving:'||l_proc, 70);
4566: exception
4567: when OTHERS then
4568: hr_utility.set_location(l_proc, 80);
4569: p_batch_size := null;
4564: close c_pay_action_parameter;
4565: hr_utility.set_location('Leaving:'||l_proc, 70);
4566: exception
4567: when OTHERS then
4568: hr_utility.set_location(l_proc, 80);
4569: p_batch_size := null;
4570: end;
4571: end pqp_read_public;
4572: /********************************************************************
4600: l_description varchar2(250);
4601: l_proc varchar2(72) := g_package||'update_pay_process_events' ;
4602:
4603: begin
4604: hr_utility.set_location('Entering:'||l_proc, 5);
4605: if (pay_process_events_ovn_cursor%isopen = true) then
4606: close pay_process_events_ovn_cursor;
4607: end if;
4608: hr_utility.set_location(l_proc, 10);
4604: hr_utility.set_location('Entering:'||l_proc, 5);
4605: if (pay_process_events_ovn_cursor%isopen = true) then
4606: close pay_process_events_ovn_cursor;
4607: end if;
4608: hr_utility.set_location(l_proc, 10);
4609: for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4610: p_source_type ,
4611: p_effective_date)
4612: loop
4609: for ppeoc1 in pay_process_events_ovn_cursor(p_person_id ,
4610: p_source_type ,
4611: p_effective_date)
4612: loop
4613: hr_utility.set_location(l_proc, 20);
4614: l_process_event_id := ppeoc1.process_event_id ;
4615: l_object_version_number := ppeoc1.object_version_number;
4616: l_assignment_id := ppeoc1.assignment_id ;
4617: l_description := ppeoc1.description ;
4624: SUBSTR('Record Read | '|| p_desc || l_description, 1, 240),
4625: p_process_event_id => l_process_event_id ,
4626: p_object_version_number => l_object_version_number
4627: );
4628: hr_utility.set_location(l_proc, 30);
4629: end loop;
4630: hr_utility.set_location('Leaving:'||l_proc, 50);
4631: end update_pay_process_events;
4632: /****************************************************************************
4626: p_object_version_number => l_object_version_number
4627: );
4628: hr_utility.set_location(l_proc, 30);
4629: end loop;
4630: hr_utility.set_location('Leaving:'||l_proc, 50);
4631: end update_pay_process_events;
4632: /****************************************************************************
4633: name : pqp_windstar_reconcile
4634: Purpose : This procedure reconciles data in pay_process_events table.
4676: l_process_event_id number;
4677: l_object_version_number number;
4678: l_proc varchar2(72) := g_package || 'pqp_windstar_reconcile';
4679: begin
4680: hr_utility.set_location('Entering ' || l_proc, 10);
4681: l_process_event_id := null;
4682: for cppe in c_pay_process_events(p_assignment_id ,
4683: p_effective_date ,
4684: p_source_type )
4682: for cppe in c_pay_process_events(p_assignment_id ,
4683: p_effective_date ,
4684: p_source_type )
4685: loop
4686: hr_utility.set_location(l_proc, 20);
4687: l_process_event_id := cppe.process_event_id ;
4688: l_object_version_number := cppe.object_version_number;
4689: end loop;
4690: hr_utility.set_location('Leaving ' || l_proc, 30);
4686: hr_utility.set_location(l_proc, 20);
4687: l_process_event_id := cppe.process_event_id ;
4688: l_object_version_number := cppe.object_version_number;
4689: end loop;
4690: hr_utility.set_location('Leaving ' || l_proc, 30);
4691: p_process_event_id := l_process_event_id ;
4692: p_object_version_number := l_object_version_number;
4693:
4694: -- Added by tmehra for nocopy changes Feb'03
4694: -- Added by tmehra for nocopy changes Feb'03
4695:
4696: exception
4697: when OTHERS then
4698: hr_utility.set_location('Entering excep:'||l_proc, 35);
4699: p_process_event_id := null;
4700: p_object_version_number := null;
4701:
4702: end pqp_windstar_reconcile;
5090: and change_type = p_source_type
5091: and status in ('N', 'D');
5092:
5093: begin
5094: hr_utility.set_location('Entering:'||l_proc, 5);
5095: begin
5096: l_logic_state := ' while validating data selected from payment_export: ';
5097: --This loop selects Non read records from payment_export table
5098: l_error_message := null;
5184:
5185: l_person_id := null;
5186:
5187: if (p_institution_indiv_id is null) then
5188: hr_utility.set_location(l_proc, 10);
5189: if (p_social_security_number is not null) then
5190: for cps in c_person_ssn(p_social_security_number)
5191: loop
5192: l_person_id := cps.person_id ;
5196: l_person_id := p_institution_indiv_id;
5197: end if;
5198:
5199: if (l_person_id is null) then
5200: hr_utility.set_location(l_proc, 20);
5201: l_error_message := l_error_message ||
5202: 'Person Id could not be determined';
5203: end if;
5204:
5207: -- =========================================================
5208:
5209: l_period_type := null;
5210: if (p_amount_of_addl_wh_type is not null) then
5211: hr_utility.set_location(l_proc, 30);
5212: for cwopp in
5213: c_winstar_oracle_pay_period(p_amount_of_addl_wh_type)
5214: loop
5215: l_period_type := cwopp.meaning;
5226: -- | L | Lump sump | Y | Year |
5227: -- +--------------+--------------+----------------+-----------------+
5228: --
5229: if (l_period_type is null) then
5230: hr_utility.set_location(l_proc, 40);
5231: l_error_message := l_error_message || ' Pay Period is Invalid';
5232: --
5233: -- l_period_type will be null if the value in
5234: -- p_addtnl_wthldng_amt_period_type is not either of M, W, S, B, L.
5253: if (ascii(p_personal_exemption) < 48 or
5254: ascii(p_personal_exemption) > 49 ) then
5255: l_error_message := l_error_message || '(' || 'personal_exemption = '
5256: || p_personal_exemption || ' is invalid.)';
5257: hr_utility.set_location(l_proc, 50);
5258: end if;
5259:
5260: for cpas in c_pri_assgn(l_person_id,
5261: to_date('01/01'||to_char(p_taxyear),'DD/MM/YYYY'))
5259:
5260: for cpas in c_pri_assgn(l_person_id,
5261: to_date('01/01'||to_char(p_taxyear),'DD/MM/YYYY'))
5262: loop
5263: hr_utility.set_location(l_proc, 60);
5264: l_pri_assgn := cpas.assignment_id;
5265: end loop;
5266:
5267: l_maximum_benefit := p_maximum_benefit;
5347: ,p_person_id => l_person_id
5348: ,p_effective_date =>
5349: TO_DATE('01/01' || TO_CHAR(p_taxyear), 'DD/MM/YYYY')
5350: );
5351: hr_utility.set_location(l_proc, 70);
5352: if (l_error_message is null and
5353: pqp_atd_bus.g_error_message is null) then
5354:
5355: hr_utility.set_location(l_proc, 80);
5351: hr_utility.set_location(l_proc, 70);
5352: if (l_error_message is null and
5353: pqp_atd_bus.g_error_message is null) then
5354:
5355: hr_utility.set_location(l_proc, 80);
5356: -- ==============================================
5357: -- W4 record creation
5358: -- ==============================================
5359: -- A W4 record will be created under the following conditions.
5415: NVL(p_constant_additional_tax,0);
5416: end if;
5417:
5418:
5419: hr_utility.set_location(l_proc, 100);
5420: pay_us_web_w4.update_alien_tax_records
5421: -- pay_us_otf_util_web.update_tax_records
5422: (p_filing_status_code => '01'
5423: ,p_allowances => (nvl(p_add_exemptions_allowed, 0) +
5431: ,p_effective_date => p_date_w4_signed
5432: ,p_source_name => 'PQP_US_ALIEN_WINDSTAR'
5433: );
5434:
5435: hr_utility.set_location(l_proc, 110);
5436:
5437: end if;
5438: end if;
5439: --
5454: 'PQP_US_ALIEN_WINDSTAR'
5455: );
5456: fetch c_person_assgn into l_cpa_assignment_id;
5457:
5458: hr_utility.set_location(l_proc, 120);
5459:
5460: if (c_person_assgn%found) then
5461: loop
5462: hr_utility.set_location(l_proc, 130);
5458: hr_utility.set_location(l_proc, 120);
5459:
5460: if (c_person_assgn%found) then
5461: loop
5462: hr_utility.set_location(l_proc, 130);
5463: -- The following cursor checks whether an assignment exists in
5464: -- pqp_alien_data table for a given year or not
5465: --
5466: l_logic_state := ' while inserting in PQP_ANALYZED_ALIEN_DATA:';
5480: -- the pqp_analyzed_alien_data_api table for the given year or not
5481: --
5482: fetch c_assign_exists into l_analyzed_data_id ,
5483: l_analyzed_data_ovn ;
5484: hr_utility.set_location(l_proc, 140);
5485:
5486: if (c_assign_exists%notfound) then
5487:
5488: hr_utility.set_location(l_proc, 150);
5484: hr_utility.set_location(l_proc, 140);
5485:
5486: if (c_assign_exists%notfound) then
5487:
5488: hr_utility.set_location(l_proc, 150);
5489: --
5490: -- if the row does not exist then create a row in
5491: -- analyzed_alien_data table
5492: --
5519: ,p_date_8233_signed => l_date_8233_signed
5520: ,p_effective_date => to_date('01/01/' ||
5521: p_taxyear, 'DD/MM/YYYY')
5522: );
5523: hr_utility.set_location(l_proc, 160);
5524:
5525: else
5526:
5527: hr_utility.set_location(l_proc, 170);
5523: hr_utility.set_location(l_proc, 160);
5524:
5525: else
5526:
5527: hr_utility.set_location(l_proc, 170);
5528:
5529: if (c_assign_exists%isopen = true) then
5530: close c_assign_exists;
5531: end if;
5555: ,p_effective_date => to_date('01/01/' ||
5556: p_taxyear, 'DD/MM/YYYY')
5557: );
5558:
5559: hr_utility.set_location(l_proc, 180);
5560:
5561: end if;
5562: --
5563: -- Alien Details
5575: l_analyzed_det_ovn,
5576: l_retro_lose_ben_amt_flag_old,
5577: l_retro_lose_ben_date_flag_old ;
5578:
5579: hr_utility.set_location(l_proc, 190);
5580: --
5581: -- The following code raises a notification if the actual income
5582: -- code is different from the forecast income code. The notification
5583: -- would be send only if no analyzed data is available for this
5613:
5614:
5615: if (c_analyzed_det_exists%notfound) then
5616:
5617: hr_utility.set_location(l_proc, 200);
5618: if (c_analyzed_det_exists%isopen = true) then
5619: close c_analyzed_det_exists;
5620: end if;
5621:
5649: ,p_effective_date => to_date('01/01/' ||
5650: p_taxyear, 'DD/MM/YYYY')
5651: );
5652:
5653: hr_utility.set_location(l_proc, 210);
5654:
5655: else
5656:
5657: hr_utility.set_location(l_proc, 220);
5653: hr_utility.set_location(l_proc, 210);
5654:
5655: else
5656:
5657: hr_utility.set_location(l_proc, 220);
5658:
5659: if (c_analyzed_det_exists%isopen = true) then
5660: close c_analyzed_det_exists;
5661: end if;
5703: p_treaty_benefits_start_date => p_treatybenefit_startdate ,
5704: p_object_version_number => l_analyzed_det_ovn ,
5705: p_effective_date => TO_DATE('01/01/' || p_taxyear, 'DD/MM/YYYY')
5706: );
5707: hr_utility.set_location(l_proc, 230);
5708: end if;
5709:
5710: if l_current_analysis = 'N' then
5711:
5786: p_source_type => 'PQP_US_ALIEN_WINDSTAR' ,
5787: p_process_event_id => l_process_event_id ,
5788: p_object_version_number => l_process_ovn
5789: );
5790: hr_utility.set_location(l_proc, 240);
5791:
5792: if (l_process_event_id is null and
5793: l_reco_flag = false) then
5794:
5791:
5792: if (l_process_event_id is null and
5793: l_reco_flag = false) then
5794:
5795: hr_utility.set_location(l_proc, 250);
5796: -- l_process_event_id will be null if an assignment id does
5797: -- not exist in the pay_process_events table for PQP_US_WINSTAR
5798: -- or such an assignment exists in the pay_process_events
5799: -- table, but the status of such record is not read
5807: 'Assignment not Reconciled'
5808: ,p_effective_date => TO_DATE('01/01/' ||
5809: p_taxyear, 'DD/MM/YYYY')
5810: );
5811: hr_utility.set_location(l_proc, 260);
5812:
5813: p_error_message := ' Assignment not Reconciled ';
5814:
5815: l_reco_flag := true;
5823: ,p_process_name => 'WIN_PRC'
5824: ,p_process_event_id => null
5825: ) ;
5826:
5827: hr_utility.set_location(l_proc, 270);
5828:
5829: elsif(l_process_event_id is not null and p_reconcile = true)
5830: then --ELSE7}{
5831: hr_utility.set_location(l_proc, 280);
5827: hr_utility.set_location(l_proc, 270);
5828:
5829: elsif(l_process_event_id is not null and p_reconcile = true)
5830: then --ELSE7}{
5831: hr_utility.set_location(l_proc, 280);
5832:
5833: pay_ppe_api.update_process_event
5834: (p_validate => false
5835: ,p_change_type => 'PQP_US_ALIEN_WINDSTAR'
5837: ,p_status => 'C'
5838: ,p_process_event_id => l_process_event_id
5839: ,p_object_version_number=> l_process_ovn
5840: );
5841: hr_utility.set_location(l_proc, 290);
5842: --
5843: -- There might be other open records with a status of 'R' for
5844: -- the same assignment as windstar read process might have
5845: -- read it twice. ie. there could be more than one record with
5858: where ppe.assignment_id = l_pri_assgn
5859: and change_type = 'PQP_US_ALIEN_WINDSTAR'
5860: and ppe.status = 'R';
5861: --
5862: hr_utility.set_location(l_proc, 295);
5863: end;
5864:
5865: begin
5866: select retro_loss_notification_sent
5937: ,p_retro_loss_notification_sent => 'Y'
5938: ,p_object_version_number => l_transaction_ovn
5939: );
5940:
5941: hr_utility.set_location(l_proc, 296);
5942: end if;
5943:
5944: end;
5945: -- Workflow Notification: The control will come to this point
5954: exit when c_person_assgn%notfound;
5955: end loop;
5956:
5957: else
5958: hr_utility.set_location(l_proc, 300);
5959: -- Means no assignment with read/complete status was present in
5960: -- pay_process_events table
5961: pqp_alien_trans_data_api.update_alien_trans_data
5962: (p_validate => false
5965: ,p_error_indicator => 'ERROR : NOT_RECONCILED 2'
5966: ,p_error_text => l_error_message||'Assignment not Reconciled'
5967: ,p_effective_date => TO_DATE('01/01/'||p_taxyear, 'DD/MM/YYYY')
5968: );
5969: hr_utility.set_location(l_proc, 310);
5970:
5971: p_error_message := ' Assignment not Reconciled ';
5972:
5973: pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5978: p_itemtype => 'PQPALNTF' ,
5979: p_process_name => 'WIN_PRC' ,
5980: p_process_event_id => null
5981: ) ;
5982: hr_utility.set_location(l_proc, 320);
5983:
5984: end if;
5985: close c_person_assgn;
5986: else
5983:
5984: end if;
5985: close c_person_assgn;
5986: else
5987: hr_utility.set_location(l_proc, 330);
5988: p_error_message := l_error_message ||pqp_atd_bus.g_error_message;
5989:
5990: pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
5991: (p_alien_transaction_id => l_alien_transaction_id ,
5995: p_itemtype => 'PQPALNTF' ,
5996: p_process_name => 'WIN_PRC' ,
5997: p_process_event_id => null
5998: ) ;
5999: hr_utility.set_location(l_proc, 340);
6000: end if;
6001: exception
6002: when OTHERS then
6003: hr_utility.set_location(l_proc, 350);
5999: hr_utility.set_location(l_proc, 340);
6000: end if;
6001: exception
6002: when OTHERS then
6003: hr_utility.set_location(l_proc, 350);
6004: l_atd_ovn := null;
6005: l_error_message := l_error_message ||TO_CHAR(SQLCODE) ||
6006: SQLERRM ||l_logic_state;
6007: p_error_message := p_error_message || l_error_message;
6012: end loop;
6013:
6014: if (l_atd_ovn is not null) then
6015:
6016: hr_utility.set_location(l_proc, 360);
6017:
6018: pqp_alien_trans_data_api.update_alien_trans_data
6019: (p_validate => false
6020: ,p_alien_transaction_id => l_alien_transaction_id
6024: ,p_effective_date => to_date('01/01/' ||
6025: p_taxyear, 'DD/MM/YYYY')
6026: );
6027:
6028: hr_utility.set_location(l_proc, 370);
6029:
6030: pqp_alien_expat_wf_pkg.StartAlienExpatWFProcess
6031: (p_alien_transaction_id => l_alien_transaction_id
6032: ,p_assignment_id => l_pri_assgn
6036: ,p_process_name => 'WIN_PRC'
6037: ,p_process_event_id => null
6038: );
6039:
6040: hr_utility.set_location(l_proc, 380);
6041: end if;
6042: end;
6043:
6044: hr_utility.set_location('Leaving ' || l_proc, 390);
6040: hr_utility.set_location(l_proc, 380);
6041: end if;
6042: end;
6043:
6044: hr_utility.set_location('Leaving ' || l_proc, 390);
6045:
6046: exception
6047: when others then
6048: hr_utility.set_location('Entering excep:'||l_proc, 395);
6044: hr_utility.set_location('Leaving ' || l_proc, 390);
6045:
6046: exception
6047: when others then
6048: hr_utility.set_location('Entering excep:'||l_proc, 395);
6049: p_error_message := p_error_message || sqlerrm;
6050:
6051: end pqp_write_public;
6052:
6064: l_batch_size number;
6065: l_proc constant varchar2(72) := g_package || 'pqp_batch_size';
6066:
6067: begin
6068: hr_utility.set_location('Entering :' || l_proc, 10);
6069:
6070: if (c_pay_action_parameter%isopen = true) then
6071: close c_pay_action_parameter;
6072: end if;
6079: exit when c_pay_action_parameter%notfound;
6080: end loop;
6081: close c_pay_action_parameter;
6082:
6083: hr_utility.set_location(l_proc, 20);
6084:
6085: p_batch_size := l_batch_size;
6086:
6087: if (l_batch_size is null) then
6087: if (l_batch_size is null) then
6088: p_batch_size := null;
6089: end if;
6090:
6091: hr_utility.set_location('Leaving : ' || l_proc, 30);
6092:
6093: exception
6094: when OTHERS then
6095: hr_utility.set_location('Entering excep:'||l_proc, 35);
6091: hr_utility.set_location('Leaving : ' || l_proc, 30);
6092:
6093: exception
6094: when OTHERS then
6095: hr_utility.set_location('Entering excep:'||l_proc, 35);
6096: p_batch_size := null;
6097:
6098: end pqp_batch_size;
6099:
6112: l_proc constant varchar2(72) := g_package || 'ResetForReadAPI';
6113:
6114: begin
6115: l_ovn := -1;
6116: hr_utility.set_location('Entering:'||l_proc, 5);
6117:
6118: for cop in c_ovn_ppe(p_process_event_id)
6119: loop
6120: l_ovn := cop.object_version_number;
6120: l_ovn := cop.object_version_number;
6121: end loop;
6122: if (l_ovn is not null and l_ovn <> -1) then
6123:
6124: hr_utility.set_location(l_proc, 10);
6125:
6126: pay_ppe_api.update_process_event
6127: (p_validate => false
6128: ,p_status => 'N'
6132: );
6133:
6134: end if;
6135:
6136: hr_utility.set_location('Leaving:'||l_proc, 20);
6137:
6138: end ResetForReadAPI;
6139:
6140: -- =============================================================================
6152: l_proc constant varchar2(72) := g_package || 'AbortReadAPI';
6153:
6154: begin
6155:
6156: hr_utility.set_location('Entering:'||l_proc, 5);
6157:
6158: for cop in c_ovn_ppe(p_process_event_id)
6159: loop
6160: l_ovn := cop.object_version_number;
6161: end loop;
6162:
6163: if (l_ovn is not null and l_ovn <> -1) then
6164:
6165: hr_utility.set_location(l_proc, 10);
6166:
6167: pay_ppe_api.update_process_event
6168: (p_validate => false
6169: ,p_status => 'C'
6173: );
6174:
6175: end if;
6176:
6177: hr_utility.set_location('Leaving:' ||l_proc, 20);
6178:
6179: end AbortReadAPI;
6180:
6181: