1 PACKAGE PER_FR_D2_PKG
2 /* $Header: pefrd2rp.pkh 120.0 2005/05/31 08:54:26 appldev noship $ */
3 AUTHID CURRENT_USER AS
4 --
5 type table_of_number is table of number index by binary_integer;
6 -- #4068197
7 type table_of_varchar is table of varchar2(30) index by binary_integer;
8 -- #4068197
9
10 --
11 type block_record is record (
12 per_type_id per_all_people_f.person_type_id%type := null,
13 person_type_usages varchar2(32000),
14 asg_id per_all_assignments_f.assignment_id%type := null,
15 asg_status per_all_assignments_f.assignment_status_type_id%type := null,
16 asg_primary per_all_assignments_f.primary_flag%type := null,
17 asg_employment_category per_all_assignments_f.employment_category%type := null,
18 asg_freq per_all_assignments_f.frequency%type := null,
19 asg_hours per_all_assignments_f.normal_hours%type := null,
20 asg_type per_all_assignments_f.assignment_type%type,
21 ctr_type per_contracts_f.type%type := null,
22 ctr_fr_person_replaced per_contracts_f.ctr_information5%type := null,
23 ctr_status per_contracts_f.status%type := null,
24 ass_employee_category hr_soft_coding_keyflex.segment2%type := null,
25 asg_full_time_freq per_all_positions.frequency%type := null,
26 asg_full_time_hours per_all_positions.working_hours%type := null,
27 asg_fte_value per_assignment_budget_values_f.value%type := null,
28 block_start_date date := null,
29 block_end_date date := null );
30 --
31 type table_of_block is table of block_record index by binary_integer;
32
33 -- Removed nvl operators (Bug 2662236)
34 --
35 cursor csr_get_emp_year (p_establishment_id in NUMBER,
36 p_1jan in DATE,
37 p_31dec in date)
38 is
39 -- scan all employees in the given year
40 select distinct a.person_id
41 from per_assignment_status_types t,
42 per_all_assignments_f a
43 where t.assignment_status_type_id = a.assignment_status_type_id
44 and t.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN'
45 ,'ACTIVE_CWK','SUSP_CWK_ASG')
46 and a.establishment_id = p_establishment_id
47 and a.assignment_type in ('E','C')
48 and a.effective_end_date >= p_1jan
49 and a.effective_start_date <= p_31dec;
50 --
51 cursor csr_get_asg_emp (p_establishment_id in number,
52 p_1jan in date,
53 p_31dec in date,
54 p_person_id in number)
55 is
56 -- scan all assignments for the given employee
57 select distinct a.assignment_id asg_id
58 from per_assignment_status_types t,
59 per_all_assignments_f a
60 where a.person_id = p_person_id
61 and t.assignment_status_type_id = a.assignment_status_type_id
62 and nvl(t.per_system_status,' ') in ('ACTIVE_ASSIGN','SUSP_ASSIGN'
63 ,'ACTIVE_CWK','SUSP_CWK_ASG')
64 and nvl(a.establishment_id,-1) = p_establishment_id
65 and a.assignment_type in ('E','C')
66 and a.effective_end_date >= p_1jan
67 and a.effective_start_date <= p_31dec
68 order by asg_id;
69 --
70 cursor csr_get_disabled (p_establishment_id in number,
71 p_1jan in date,
72 p_31dec in date)
73 is
74 -- works out IDs of disabled
75 select distinct id from
76 (select asg.person_id id,
77 pdf.effective_start_date date_from,
78 pdf.effective_end_date date_to,
79 asg.effective_start_date asg_from,
80 asg.effective_end_date asg_to
81 from
82 per_disabilities_f pdf,
83 per_assignment_status_types typ,
84 per_all_assignments_f asg
85 where asg.person_id = pdf.person_id
86 and asg.assignment_type = 'E'
87 and typ.assignment_status_type_id = asg.assignment_status_type_id
88 and typ.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
89 and asg.establishment_id = p_establishment_id
90 and (pdf.category in ('A','B','C') or
91 pdf.reason = 'OCC_INC' or
92 pdf.dis_information1 in ('CIVIL','MILITARY','MILITARY_EQUIVALENT'))
93 and pdf.DIS_INFORMATION_CATEGORY = 'FR'
94 and asg.effective_start_date <= p_31dec
95 and asg.effective_end_date >= p_1jan)
96 where date_from <= least(p_31dec,asg_to)
97 and date_to >= greatest(p_1jan,asg_from)
98 order by id;
99 --
100 --
101 function set_headcounts (p_establishment_id in number,
102 p_1jan in date,
103 p_31dec in date,
104 p_headcount_obligation out nocopy number,
105 p_headcount_particular out nocopy number,
106 p_basis_obligation out nocopy number,
107 p_obligation out nocopy number,
108 p_breakdown_particular out nocopy varchar2,
109 p_count_disabled out nocopy varchar2,
110 p_disabled_where_clause out nocopy varchar2)
111 return integer;
112 -- compute headcounts for the establishment
113 --
114 procedure get_extra_units (p_establishment_id in number,
115 p_effective_date in date,
116 p_base_unit out nocopy number,
117 p_xcot_a out nocopy number,
118 p_xcot_b out nocopy number,
119 p_xcot_c out nocopy number,
120 p_xcot_young_age out nocopy number,
121 p_xcot_old_age out nocopy number,
122 p_xcot_age_units out nocopy number,
123 p_xcot_training_hours out nocopy number,
124 p_xcot_training_units out nocopy number,
125 p_xcot_ap out nocopy number,
126 p_xcot_impro out nocopy number,
127 p_xcot_cat out nocopy number,
128 p_xcot_cdtd out nocopy number,
129 p_xcot_cfp out nocopy number,
130 p_xipp_low_rate out nocopy number,
131 p_xipp_medium_rate out nocopy number,
132 p_xipp_high_rate out nocopy number,
133 p_xipp_low_units out nocopy number,
134 p_xipp_medium_units out nocopy number,
135 p_xipp_high_units out nocopy number,
136 p_hire_units out nocopy number);
137 -- get values from fr_d2_rates
138 --
139 function include_this_person_type (p_user_person_types in varchar2,
140 p_business_group_id in number,
141 p_effective_date in date)
142 return boolean;
143 -- decide wether the person type is reported in the D2 or not
144 --
145 procedure trunc_list_disabled (p_person_id in number,
146 p_list in out nocopy varchar2);
147 -- remove person from list of disabled
148 --
149 function contract_prorated (p_block in block_record,
150 p_business_group_id in number,
151 p_estab_hours in number,
152 p_31dec in date,
153 p_tmp_total in out nocopy number,
154 p_formula_id in number,
155 p_formula_start_date in date)
156 return integer;
157 -- update non-integer headcount (eg. 0.5 for half-time assignment)
158 --
159 -- Get the job valid pcs code
160 procedure get_pcs_code (p_report_qualifier in varchar2
161 ,p_job_id in per_jobs.job_id%type default null
162 ,p_job_name in per_jobs.name%type default null
163 ,p_pcs_code in out nocopy varchar2
164 ,p_effective_date in date);
165
166 procedure get_job_info (p_establishment_id in number,
167 p_person_id in number,
168 p_1jan in date,
169 p_31dec in date,
170 p_year in number,
171 p_pcs_code out nocopy varchar2,
172 p_job_title out nocopy varchar2,
173 p_hours_training out nocopy number,
174 p_hire_year out nocopy number,
175 p_year_became_permanent out nocopy number);
176 -- retrieves info on job
177 --pragma restrict_references (get_job_info,WNDS,WNPS);
178 --
179 function get_estab_hours (p_establishment_id in number)
180 return number;
181 -- return reference monthly hours
182 --
183 function list_disabled (p_establishment_id in number,
184 p_1jan in date,
185 p_31dec in date)
186 return varchar2;
187 -- return SQL statement to select disabled employees ID
188 --
189 procedure update_particular (p_establishment_id in number,
190 p_person_id in number,
191 p_1jan in date,
192 p_31dec in date,
193 p_business_group_id in number,
194 p_employee_count in number,
195 p_headcount_particular in out nocopy number,
196 p_pcs_count in out nocopy table_of_number,
197 p_pcs_codes in out nocopy table_of_varchar);
198 -- update headcount for particular pcs_codes
199 --
200 -- #4068197
201 function string_of_particular (p_pcs_count in table_of_number,
202 p_pcs_codes in table_of_varchar)
203 return varchar2;
204 -- #4068197
205 -- change table (for breakdown) into SQL string to pass to the report
206 --
207 procedure update_count_disabled (p_person_id in number,
208 p_list in varchar2,
209 p_employee_count in number,
210 p_count_disabled in out nocopy varchar2);
211 -- update string of disabled employees (eg. '123=1;436=0.5;677=1;')
212 --
213 procedure get_formula_ref (p_effective_date in date,
214 p_business_group_id in number,
215 p_formula_id out nocopy number,
216 p_formula_start_date out nocopy date);
217 -- give references of 'Contract Prorated' fast formula
218 --
219 function relevant_change (block1 in block_record,
220 block2 in block_record)
221 return boolean;
222 -- work out if 2 consecutive small blocks must be considered
223 -- as 1 consolidated block or 2 distinct blocks
224 --
225 function posid_in_list (p_id in number,
226 p_list in varchar2)
227 return integer;
228 -- return position of id in list (or 0 if not in list)
229 -- eg. 78 is a substring of 178 but 78 is not an id in '12,178,2200'
230 --
231 procedure populate_blocks_table (p_establishment_id in number,
232 p_1jan in date,
233 p_31dec in date,
234 p_person_id in number,
235 p_blocks out nocopy table_of_block);
236 -- populate the table of blocks for an employee
237 --
238 function latest_block (p_assignment_id in number,
239 p_establishment_id in number,
240 p_start_period in date,
241 p_end_period in date)
242 return date;
243 -- get the end-date of the latest block
244 -- which meets the criteria in the given period
245 --
246 function beginning_of_block (p_assignment_id in number,
247 p_end_date in date,
248 p_1jan in date)
249 return date;
250 -- get the start-date of the block which end date is known
251 --
252 procedure add_block_row (p_block_table in out nocopy table_of_block,
253 p_assignment_id in number,
254 p_start_date in date,
255 p_end_date in date);
256 -- add a record into the table of blocks
257 --
258 END PER_FR_D2_PKG;
[Home] [Help]