[Home] [Help]
98: -- removed w.r.t earlier fix (Bug# 2942797)
99: -- 08 May 2003 srrajago 2853776 Removed the financial year condition check from the main part of the
100: -- cursor quarters_info and included the same in the sub-query.
101: -- 30 May 2003 kaverma 2920731 Replaced tables per_all_assignments_f and per_all_people_f by secured views
102: -- per_assignments_f and per_people_f respectively form the queries
103: -- 05 Jun 2003 puchil 2949952 Archived the country value in X_HK_RES_COUNTRY
104: -- 21 Jul 2003 srrajago 3055512 Employees terminated on 31-MAR-YYYY should not be included in the archive
105: -- run for the year YYYY. Hence modified the cursor process_assignments by
106: -- including the join with actual_termination_date <> 31-Mar-YYYY.
157: p_sql OUT nocopy Varchar2) is
158: Begin
159: hr_utility.set_location('Start of range_code',1);
160: p_sql := 'SELECT distinct person_id ' ||
161: 'FROM per_people_f ppf, ' ||
162: 'pay_payroll_actions ppa ' ||
163: 'WHERE ppa.payroll_action_id = :payroll_action_id ' ||
164: 'AND ppa.business_group_id = ppf.business_group_id ' ||
165: 'ORDER BY ppf.person_id';
439: pcr.primary_contact_flag primary_contact_flag,
440: NVL(pcr.contact_person_id,0) person_id, -- used in spouse cursor
441: pcr.contact_type contact_type, -- used in spouse cursor
442: pcr.date_start date_start
443: FROM per_people_f papf,
444: per_assignments_f paaf,
445: fnd_territories_tl ftv, /* Bug No : 3059915 */
446: per_contact_relationships pcr,
447: per_addresses pad,
533: papf_spouse.national_identifier spouse_hk_id,
534: papf_spouse.per_information1
535: ||DECODE(papf_spouse.per_information2,NULL, NULL, ' '
536: || ftv.territory_short_name) passport_info
537: FROM per_people_f papf_spouse,
538: fnd_territories_tl ftv /* Bug No : 3059915 */
539: WHERE papf_spouse.person_id = c_person_id
540: AND papf_spouse.business_group_id = p_business_group_id
541: AND papf_spouse.per_information2 = ftv.territory_code(+)
1334: DECODE(papf.marital_status, 'M',
1335: DECODE(sex, 'F', NVL(previous_last_name, last_name), last_name)
1336: ,last_name) last_name,
1337: TRIM(papf.first_name||' '||papf.middle_names) other_name
1338: FROM per_people_f papf,
1339: per_assignments_f paaf,
1340: per_periods_of_service pps
1341: WHERE paaf.person_id = papf.person_id
1342: AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
1448: IS
1449: SELECT pap.employee_number
1450: FROM per_assignments_f a,
1451: hr_soft_coding_keyflex sck,
1452: per_people_f pap
1453: WHERE a.assignment_id = p_assignment_id
1454: AND a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1455: AND pap.person_id = a.person_id
1456: AND TO_DATE('31/03'||p_reporting_year,'DD/MM/YYYY')