[Home] [Help]
3095: g_package || 'pqp_windstar_balance_read';
3096: l_balance_name pay_balance_types.balance_name%type ;
3097: l_dimension_name pay_balance_dimensions.dimension_name%type ;
3098: l_state varchar2(100) ;
3099: l_last_name per_all_people_f.last_name%type ;
3100: l_first_name per_all_people_f.first_name%type;
3101: l_middle_names per_all_people_f.middle_names%type ;
3102: l_national_identifier per_all_people_f.national_identifier%type ;
3103: l_tax_residence_country_code varchar2(100) ;
3096: l_balance_name pay_balance_types.balance_name%type ;
3097: l_dimension_name pay_balance_dimensions.dimension_name%type ;
3098: l_state varchar2(100) ;
3099: l_last_name per_all_people_f.last_name%type ;
3100: l_first_name per_all_people_f.first_name%type;
3101: l_middle_names per_all_people_f.middle_names%type ;
3102: l_national_identifier per_all_people_f.national_identifier%type ;
3103: l_tax_residence_country_code varchar2(100) ;
3104: l_description varchar2(250) ;
3097: l_dimension_name pay_balance_dimensions.dimension_name%type ;
3098: l_state varchar2(100) ;
3099: l_last_name per_all_people_f.last_name%type ;
3100: l_first_name per_all_people_f.first_name%type;
3101: l_middle_names per_all_people_f.middle_names%type ;
3102: l_national_identifier per_all_people_f.national_identifier%type ;
3103: l_tax_residence_country_code varchar2(100) ;
3104: l_description varchar2(250) ;
3105:
3098: l_state varchar2(100) ;
3099: l_last_name per_all_people_f.last_name%type ;
3100: l_first_name per_all_people_f.first_name%type;
3101: l_middle_names per_all_people_f.middle_names%type ;
3102: l_national_identifier per_all_people_f.national_identifier%type ;
3103: l_tax_residence_country_code varchar2(100) ;
3104: l_description varchar2(250) ;
3105:
3106: l_out_mesg out_mesg_type ;
3115: l_assignment_id per_all_assignments_f.assignment_id%type ;
3116: j number ;
3117: i number ;
3118: l_err_count number ;
3119: l_person_id per_all_people_f.person_id%type ;
3120: l_income_code_count number := 0 ;
3121: l_process_event_id number ;
3122: l_object_version_number per_all_people_f.object_version_number%type ;
3123: l_prev_amount number ;
3118: l_err_count number ;
3119: l_person_id per_all_people_f.person_id%type ;
3120: l_income_code_count number := 0 ;
3121: l_process_event_id number ;
3122: l_object_version_number per_all_people_f.object_version_number%type ;
3123: l_prev_amount number ;
3124:
3125: l_year_start date ;
3126: l_year_end date ;
3187: ,c_year_end in date ) is
3188: select distinct
3189: nvl(pet.element_information1, ' ') income_code
3190: from per_all_assignments_f paf
3191: ,per_all_people_f ppf
3192: ,pay_element_entries_f pee
3193: ,pay_element_links_f pel
3194: ,pay_element_types_f pet
3195: ,pay_element_classifications pec
3967: p_effective_date in date ,
3968: t_visa_tab out NOCOPY t_visa_tab_type
3969: ) is
3970:
3971: l_last_name per_all_people_f.last_name%type ;
3972: l_first_name per_all_people_f.first_name%type ;
3973: l_middle_names per_all_people_f.middle_names%type ;
3974: l_national_identifier per_all_people_f.national_identifier%type ;
3975: l_tax_residence_country_code varchar2(100) ;
3968: t_visa_tab out NOCOPY t_visa_tab_type
3969: ) is
3970:
3971: l_last_name per_all_people_f.last_name%type ;
3972: l_first_name per_all_people_f.first_name%type ;
3973: l_middle_names per_all_people_f.middle_names%type ;
3974: l_national_identifier per_all_people_f.national_identifier%type ;
3975: l_tax_residence_country_code varchar2(100) ;
3976: l_description varchar2(250) ;
3969: ) is
3970:
3971: l_last_name per_all_people_f.last_name%type ;
3972: l_first_name per_all_people_f.first_name%type ;
3973: l_middle_names per_all_people_f.middle_names%type ;
3974: l_national_identifier per_all_people_f.national_identifier%type ;
3975: l_tax_residence_country_code varchar2(100) ;
3976: l_description varchar2(250) ;
3977: l_proc varchar2(72) := g_package||'pqp_windstar_visa_read' ;
3970:
3971: l_last_name per_all_people_f.last_name%type ;
3972: l_first_name per_all_people_f.first_name%type ;
3973: l_middle_names per_all_people_f.middle_names%type ;
3974: l_national_identifier per_all_people_f.national_identifier%type ;
3975: l_tax_residence_country_code varchar2(100) ;
3976: l_description varchar2(250) ;
3977: l_proc varchar2(72) := g_package||'pqp_windstar_visa_read' ;
3978: l_primary_activity varchar2(30) ;
3982: l_01jan_date date ;
3983: l_31dec_date date ;
3984: l_prev_end_date date ;
3985: l_out_mesg out_mesg_type ;
3986: l_person_id per_all_people_f.person_id%type ;
3987: l_process_event_id number ;
3988: l_object_version_number per_all_people_f.object_version_number%type ;
3989: l_assignment_id number ;
3990: i number ;
3984: l_prev_end_date date ;
3985: l_out_mesg out_mesg_type ;
3986: l_person_id per_all_people_f.person_id%type ;
3987: l_process_event_id number ;
3988: l_object_version_number per_all_people_f.object_version_number%type ;
3989: l_assignment_id number ;
3990: i number ;
3991: j number := 1 ;
3992: l_err_count number ;
4898: -- The following cursor select the person_id for a given SSN
4899: --
4900: cursor c_person_ssn(p_social_security_number in varchar2) is
4901: select person_id
4902: from per_all_people_f
4903: where national_identifier = p_social_security_number
4904: and rownum =1;
4905: --
4906: -- The following cursor verifies whether an assignment exists in the given tax
5017: pee.effective_start_date entry_start_date,
5018: nvl(pet.element_information1, ' ') element_income_code
5019:
5020: from per_all_assignments_f paf,
5021: per_all_people_f ppf,
5022: pay_element_entries_f pee,
5023: pay_element_links_f pel,
5024: pay_element_types_f pet,
5025: pay_element_classifications pec