[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.
155: p_sql OUT nocopy Varchar2) is
156: Begin
157: hr_utility.set_location('Start of range_code',1);
158: p_sql := 'SELECT distinct person_id ' ||
159: 'FROM per_people_f ppf, ' ||
160: 'pay_payroll_actions ppa ' ||
161: 'WHERE ppa.payroll_action_id = :payroll_action_id ' ||
162: 'AND ppa.business_group_id = ppf.business_group_id ' ||
163: 'ORDER BY ppf.person_id';
437: pcr.primary_contact_flag primary_contact_flag,
438: NVL(pcr.contact_person_id,0) person_id, -- used in spouse cursor
439: pcr.contact_type contact_type, -- used in spouse cursor
440: pcr.date_start date_start
441: FROM per_people_f papf,
442: per_assignments_f paaf,
443: fnd_territories_tl ftv, /* Bug No : 3059915 */
444: per_contact_relationships pcr,
445: per_addresses pad,
498: papf_spouse.national_identifier spouse_hk_id,
499: papf_spouse.per_information1
500: ||DECODE(papf_spouse.per_information2,NULL, NULL, ' '
501: || ftv.territory_short_name) passport_info
502: FROM per_people_f papf_spouse,
503: fnd_territories_tl ftv /* Bug No : 3059915 */
504: WHERE papf_spouse.person_id = c_person_id
505: AND papf_spouse.business_group_id = p_business_group_id
506: AND papf_spouse.per_information2 = ftv.territory_code(+)
1265: DECODE(papf.marital_status, 'M',
1266: DECODE(sex, 'F', NVL(previous_last_name, last_name), last_name)
1267: ,last_name) last_name,
1268: TRIM(papf.first_name||' '||papf.middle_names) other_name
1269: FROM per_people_f papf,
1270: per_assignments_f paaf,
1271: per_periods_of_service pps
1272: WHERE paaf.person_id = papf.person_id
1273: AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
1379: IS
1380: SELECT pap.employee_number
1381: FROM per_assignments_f a,
1382: hr_soft_coding_keyflex sck,
1383: per_people_f pap
1384: WHERE a.assignment_id = p_assignment_id
1385: AND a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1386: AND pap.person_id = a.person_id
1387: AND TO_DATE('31/03'||p_reporting_year,'DD/MM/YYYY')