[Home] [Help]
3091: g_package || 'pqp_windstar_balance_read';
3092: l_balance_name pay_balance_types.balance_name%type ;
3093: l_dimension_name pay_balance_dimensions.dimension_name%type ;
3094: l_state varchar2(100) ;
3095: l_last_name per_all_people_f.last_name%type ;
3096: l_first_name per_all_people_f.first_name%type;
3097: l_middle_names per_all_people_f.middle_names%type ;
3098: l_national_identifier per_all_people_f.national_identifier%type ;
3099: l_tax_residence_country_code varchar2(100) ;
3092: l_balance_name pay_balance_types.balance_name%type ;
3093: l_dimension_name pay_balance_dimensions.dimension_name%type ;
3094: l_state varchar2(100) ;
3095: l_last_name per_all_people_f.last_name%type ;
3096: l_first_name per_all_people_f.first_name%type;
3097: l_middle_names per_all_people_f.middle_names%type ;
3098: l_national_identifier per_all_people_f.national_identifier%type ;
3099: l_tax_residence_country_code varchar2(100) ;
3100: l_description varchar2(250) ;
3093: l_dimension_name pay_balance_dimensions.dimension_name%type ;
3094: l_state varchar2(100) ;
3095: l_last_name per_all_people_f.last_name%type ;
3096: l_first_name per_all_people_f.first_name%type;
3097: l_middle_names per_all_people_f.middle_names%type ;
3098: l_national_identifier per_all_people_f.national_identifier%type ;
3099: l_tax_residence_country_code varchar2(100) ;
3100: l_description varchar2(250) ;
3101:
3094: l_state varchar2(100) ;
3095: l_last_name per_all_people_f.last_name%type ;
3096: l_first_name per_all_people_f.first_name%type;
3097: l_middle_names per_all_people_f.middle_names%type ;
3098: l_national_identifier per_all_people_f.national_identifier%type ;
3099: l_tax_residence_country_code varchar2(100) ;
3100: l_description varchar2(250) ;
3101:
3102: l_out_mesg out_mesg_type ;
3111: l_assignment_id per_all_assignments_f.assignment_id%type ;
3112: j number ;
3113: i number ;
3114: l_err_count number ;
3115: l_person_id per_all_people_f.person_id%type ;
3116: l_income_code_count number := 0 ;
3117: l_process_event_id number ;
3118: l_object_version_number per_all_people_f.object_version_number%type ;
3119: l_prev_amount number ;
3114: l_err_count number ;
3115: l_person_id per_all_people_f.person_id%type ;
3116: l_income_code_count number := 0 ;
3117: l_process_event_id number ;
3118: l_object_version_number per_all_people_f.object_version_number%type ;
3119: l_prev_amount number ;
3120:
3121: l_year_start date ;
3122: l_year_end date ;
3183: ,c_year_end in date ) is
3184: select distinct
3185: nvl(pet.element_information1, ' ') income_code
3186: from per_all_assignments_f paf
3187: ,per_all_people_f ppf
3188: ,pay_element_entries_f pee
3189: ,pay_element_links_f pel
3190: ,pay_element_types_f pet
3191: ,pay_element_classifications pec
3952: p_effective_date in date ,
3953: t_visa_tab out NOCOPY t_visa_tab_type
3954: ) is
3955:
3956: l_last_name per_all_people_f.last_name%type ;
3957: l_first_name per_all_people_f.first_name%type ;
3958: l_middle_names per_all_people_f.middle_names%type ;
3959: l_national_identifier per_all_people_f.national_identifier%type ;
3960: l_tax_residence_country_code varchar2(100) ;
3953: t_visa_tab out NOCOPY t_visa_tab_type
3954: ) is
3955:
3956: l_last_name per_all_people_f.last_name%type ;
3957: l_first_name per_all_people_f.first_name%type ;
3958: l_middle_names per_all_people_f.middle_names%type ;
3959: l_national_identifier per_all_people_f.national_identifier%type ;
3960: l_tax_residence_country_code varchar2(100) ;
3961: l_description varchar2(250) ;
3954: ) is
3955:
3956: l_last_name per_all_people_f.last_name%type ;
3957: l_first_name per_all_people_f.first_name%type ;
3958: l_middle_names per_all_people_f.middle_names%type ;
3959: l_national_identifier per_all_people_f.national_identifier%type ;
3960: l_tax_residence_country_code varchar2(100) ;
3961: l_description varchar2(250) ;
3962: l_proc varchar2(72) := g_package||'pqp_windstar_visa_read' ;
3955:
3956: l_last_name per_all_people_f.last_name%type ;
3957: l_first_name per_all_people_f.first_name%type ;
3958: l_middle_names per_all_people_f.middle_names%type ;
3959: l_national_identifier per_all_people_f.national_identifier%type ;
3960: l_tax_residence_country_code varchar2(100) ;
3961: l_description varchar2(250) ;
3962: l_proc varchar2(72) := g_package||'pqp_windstar_visa_read' ;
3963: l_primary_activity varchar2(30) ;
3967: l_01jan_date date ;
3968: l_31dec_date date ;
3969: l_prev_end_date date ;
3970: l_out_mesg out_mesg_type ;
3971: l_person_id per_all_people_f.person_id%type ;
3972: l_process_event_id number ;
3973: l_object_version_number per_all_people_f.object_version_number%type ;
3974: l_assignment_id number ;
3975: i number ;
3969: l_prev_end_date date ;
3970: l_out_mesg out_mesg_type ;
3971: l_person_id per_all_people_f.person_id%type ;
3972: l_process_event_id number ;
3973: l_object_version_number per_all_people_f.object_version_number%type ;
3974: l_assignment_id number ;
3975: i number ;
3976: j number := 1 ;
3977: l_err_count number ;
4883: -- The following cursor select the person_id for a given SSN
4884: --
4885: cursor c_person_ssn(p_social_security_number in varchar2) is
4886: select person_id
4887: from per_all_people_f
4888: where national_identifier = p_social_security_number
4889: and rownum =1;
4890: --
4891: -- The following cursor verifies whether an assignment exists in the given tax
5002: pee.effective_start_date entry_start_date,
5003: nvl(pet.element_information1, ' ') element_income_code
5004:
5005: from per_all_assignments_f paf,
5006: per_all_people_f ppf,
5007: pay_element_entries_f pee,
5008: pay_element_links_f pel,
5009: pay_element_types_f pet,
5010: pay_element_classifications pec