[Home] [Help]
339: SELECT addr.address_line1 address_line1
340: ,addr.address_line2 address_line2
341: ,addr.address_line3 address_line3
342: ,addr.town_or_city town_or_city
343: ,hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
344: ,addr.postal_code postal_code
345: FROM per_addresses addr
346: WHERE addr.person_id = p_person_id
347: AND addr.primary_flag = 'Y'
390: ,p_effective_date IN DATE
391: ) IS
392: --
393: CURSOR csr_employer_addr(c_organization_id NUMBER) IS
394: SELECT addr.address_line_1||' - '||hr_general.decode_lookup('HR_ES_LOCATION_TYPES',addr.address_line_1) address_line1
395: ,addr.address_line_3 address_line3
396: ,addr.town_or_city town_or_city
397: ,hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
398: ,addr.postal_code postal_code
393: CURSOR csr_employer_addr(c_organization_id NUMBER) IS
394: SELECT addr.address_line_1||' - '||hr_general.decode_lookup('HR_ES_LOCATION_TYPES',addr.address_line_1) address_line1
395: ,addr.address_line_3 address_line3
396: ,addr.town_or_city town_or_city
397: ,hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
398: ,addr.postal_code postal_code
399: ,addr.telephone_number_1 telephone_number
400: FROM hr_organization_units hou,
401: hr_locations_all addr
455: ,pps.leaving_reason leaving_reason
456: ,hoi.organization_id legal_employer
457: ,hr.segment2 work_center_id
458: ,hr.segment5 cont_group
459: ,hr_general.decode_lookup('ES_PROFESSIONAL_CAT'
460: ,paa.employee_category) prof_catg
461: ,paa.soft_coding_keyflex_id sc_key_id
462: ,'T' type
463: , 0 abs_attn_id
503: ,pps.leaving_reason leaving_reason
504: ,hoi.organization_id legal_employer
505: ,hr.segment2 work_center_id
506: ,hr.segment5 cont_group
507: ,hr_general.decode_lookup('ES_PROFESSIONAL_CAT'
508: ,paa.employee_category) prof_catg
509: ,paa.soft_coding_keyflex_id sc_key_id
510: ,'S' Type
511: ,paat.absence_attendance_id abs_attn_id
551:
552:
553: CURSOR csr_contract_data(c_person_id number, c_effective_date date) IS
554: SELECT pcf.contract_id contract_id
555: ,hr_general.decode_lookup('CONTRACT_TYPE',pcf.type) contract_type
556: ,hr_contract_api.get_active_end_date (pcf.contract_id
557: ,p_effective_date,pcf.status) contract_end_date
558: FROM per_contracts_f pcf
559: where pcf.person_id = c_person_id
597: l_sickness_start_date date;
598:
599:
600: CURSOR csr_stat_leav_reas_bgspec(c_business_group_id NUMBER) IS
601: SELECT hr_general.decode_lookup('STAT_TERM_REASONS',information1) prov
602: FROM per_shared_types
603: WHERE lookup_type ='LEAV_REAS'
604: AND system_type_cd = l_employee_data.leaving_reason
605: AND business_group_id = c_business_group_id;
604: AND system_type_cd = l_employee_data.leaving_reason
605: AND business_group_id = c_business_group_id;
606:
607: CURSOR csr_stat_leav_reas IS
608: SELECT hr_general.decode_lookup('STAT_TERM_REASONS',information1) prov
609: FROM per_shared_types
610: WHERE lookup_type ='LEAV_REAS'
611: AND system_type_cd = l_employee_data.leaving_reason
612: AND business_group_id IS NULL;
617: WHERE jbt.language = userenv('LANG')
618: AND jbt.job_id = c_job_id;
619:
620: CURSOR csr_get_wc_prov(c_wc_id NUMBER) IS
621: SELECT hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
622: FROM hr_organization_units hou,
623: hr_locations_all addr
624: WHERE hou.organization_id = c_wc_id
625: AND hou.location_id = addr.location_id;
732: CURSOR csr_Contribution_base(c_assignment_id number, c_effective_date date) IS
733: SELECT pee.rowid row_id
734: ,pee.element_entry_id
735: ,min(decode(piv.name, 'Year', eev.screen_entry_value, null)) year
736: ,min(decode(piv.name, 'Month', hr_general.decode_lookup('ES_MONTH_NAMES',eev.screen_entry_value), null)) month
737: ,min(decode(piv.name, 'Contribution Days', eev.screen_entry_value, null)) contribution_days
738: ,min(decode(piv.name, 'Regular Situation Base', eev.screen_entry_value, null)) rs_cont_base
739: ,min(decode(piv.name, 'IA ID Contribution', eev.screen_entry_value, null)) ia_id_contribution
740: ,min(decode(piv.name, 'Note', eev.screen_entry_value, null)) note