1 Package PQP_US_SRS_Extracts AUTHID CURRENT_USER As
2 -- $Header: pqpussrs.pkh 120.1 2005/06/09 15:10:46 rpinjala noship $
3 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4 -- ~ Global Variables ~
5 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
6
7 g_proc_name varchar2(65):= 'PQP_US_SRS_Extracts.';
8 g_assignment_id per_all_assignments_f.assignment_id%Type;
9 g_business_group_id per_all_assignments_f.business_group_id%TYPE;
10 g_legislation_code varchar2(20);
11 g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
12 g_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
13 g_ext_dfn_id pqp_extract_attributes.ext_dfn_id%TYPE;
14 g_effective_date date;
15 g_extract_start_date date;
16 g_extract_end_date date;
17 g_extract_pay_date date;
18 g_payroll_frequency varchar(150);
19
20 g_eligible_comp_balance_C varchar2(150) := ' Eligible Comp';
21 g_SRS_balance_C varchar2(150) := ' SRS Plan Name';
22 g_ER_balance_C varchar2(150) := ' ER Contribution';
23 g_AT_Contribution_C varchar2(150) := ' AT';
24 g_BuyBack_Balance_C varchar2(150) := ' Buy Back';
25 g_Additional_Balance_C varchar2(150) := ' Addl EE Contr Amt';
26 g_ER_Additional_C varchar2(150) := ' Addl ER Contr Amt';
27
28 g_srs_balance varchar2(150);
29 g_eligible_comp_balance varchar2(150);
30 g_ER_balance varchar2(150);
31 g_AT_Contribution varchar2(150);
32 g_BuyBack_Balance varchar2(150);
33 g_Additional_Balance varchar2(150);
34 g_ER_Additional varchar2(150);
35
36 g_dimension_name varchar2(100);
37
38 g_plan_person_identifier varchar2(150);
39 g_qualifies_10yr_rule varchar2(150);
40 g_qualifies_GrdFathering varchar2(150);
41 g_plan_start_date date;
42 g_plan_end_date date;
43 --
44 Type g_srs_plan_rec Is Record
45 ( plan_name varchar2(150)
46 ,assignment_id per_all_assignments_f.assignment_id%TYPE);
47 Type t_srs_plan_type Is Table Of g_srs_plan_rec Index By Binary_Integer;
48 g_extract_plan_names t_srs_plan_type ;
49 g_extract_plan_name varchar2(150);
50 --
51 --
52 Type g_srs_payroll_rec Is Record
53 (payroll_name varchar2(150) );
54 Type t_srs_payroll_type Is Table Of g_srs_payroll_rec Index By Binary_Integer;
55 g_extract_payroll_names t_srs_payroll_type ;
56 --
57 --
58 Type g_payroll_details Is Record
59 (payroll_name pay_payrolls_f.payroll_name%TYPE
60 ,period_type pay_payrolls_f.period_type%TYPE
61 ,payroll_start_date date
62 ,payroll_end_date date
63 ,actual_pay_date date
64 );
65 Type t_payroll_details_type Is Table Of g_payroll_details Index By Binary_Integer;
66 g_payroll_names t_payroll_details_type;
67 --
68 --
69 -- ============================
70 -- ~ Cursors Declarations ~
71 -- ============================
72 --
73 -- Cursor to get the extract record id
74 --
75 Cursor csr_ext_rcd_id(c_hide_flag In Varchar2
76 ,c_rcd_type_cd In Varchar2
77 ) Is
78 select rcd.ext_rcd_id
79 from ben_ext_rcd rcd
80 ,ben_ext_rcd_in_file rin
81 ,ben_ext_dfn dfn
82 Where dfn.ext_dfn_id = ben_ext_thread.g_ext_dfn_id -- The extract executing currently
83 And rin.ext_file_id = dfn.ext_file_id
84 And rin.hide_flag = c_hide_flag -- Y=Hidden, N=Not Hidden
85 And rin.ext_rcd_id = rcd.ext_rcd_id
86 And rcd.rcd_type_cd = c_rcd_type_cd; -- D=Detail,H=Header,F=Footer
87
88 --
89 -- Cursor to get the defined balance id for a given balance and dimension
90 --
91 cursor csr_defined_bal ( c_balance_name in varchar2
92 ,c_dimension_name in varchar2
93 ,c_business_group_id in number) is
94 select db.defined_balance_id
95 from pay_balance_types pbt
96 ,pay_defined_balances db
97 ,pay_balance_dimensions bd
98 where pbt.balance_name = c_balance_name
99 and pbt.balance_type_id = db.balance_type_id
100 and bd.balance_dimension_id = db.balance_dimension_id
101 and bd.dimension_name = c_dimension_name
102 and (pbt.business_group_id = c_business_group_id or
103 pbt.legislation_code = g_legislation_code)
104 and (db.business_group_id = pbt.business_group_id or
105 db.legislation_code = g_legislation_code);
106 --
107 -- Get the Payroll Id for the given assignment id, used in case the extract is PTD type
108 --
109 Cursor csr_get_payroll_id
110 ( c_assignment_id In per_all_assignments_f.assignment_id%TYPE
111 ,c_effective_date In date) Is
112 Select paa.payroll_id
113 ,paa.effective_start_date
114 ,paa.effective_end_date
115 from per_all_assignments_f paa
116 where paa.assignment_id = c_assignment_id
117 and ((c_effective_date between paa.effective_start_date
118 and paa.effective_end_date
119 )
120 Or
121 (paa.effective_start_date =( select max(pax.effective_start_date)
122 from per_all_assignments_f pax
123 where pax.assignment_id = c_assignment_id)
124 )
125 );
126 --
127 -- Get the payroll_name and payroll frequency
128 --
129 Cursor csr_payroll_name (c_payroll_id In per_all_assignments_f.payroll_id%TYPE
130 ,c_effective_date In date ) Is
131 Select payroll_name, period_type
132 from pay_payrolls_f
133 Where c_effective_date Between effective_start_date
134 and effective_end_date
135 and payroll_id = c_payroll_id;
136
137 --
138 -- Cursor to get the SRS Plans assignment extra info
139 --
140 Cursor csr_assig_extra_info
141 ( c_assignment_id In per_all_assignments_f.assignment_id%TYPE
142 ) Is
143 Select *
144 From per_assignment_extra_info pae
145 Where pae.assignment_id = c_assignment_id
146 and pae.information_type = 'PQP_US_SRS_PLAN_ASG_INFO'
147 and pae.aei_information_category = 'PQP_US_SRS_PLAN_ASG_INFO' ;
148
149 -- Based on the Payroll id and the effective date of the extract get the start
150 -- and end date of the period. Used only in case of the PTD as each assign. may have
151 -- different payroll frequency i.e. Monthly, Semi-Month, Weekly etc..
152 Cursor csr_time_period ( c_payroll_id In pay_payrolls_f.payroll_id%TYPE
153 ,c_effective_date In date) Is
154 Select start_date, end_date
155 from per_time_periods
156 where payroll_id = c_payroll_id
157 and c_effective_date between start_date
158 and end_date;
159 --
160 -- Cursor to get the primary assig. records active as of the given period
161 --
162 Cursor csr_assig_rec
163 ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE
164 ,c_business_group_id in per_all_assignments_f.business_group_id%TYPE
165 ,c_effective_date in date
166 ,c_extract_start_date in date
167 ,c_extract_end_date in date ) Is
168 select *
169 from per_all_assignments_f paa
170 where paa.business_group_id = c_business_group_id
171 and paa.assignment_id = c_assignment_id
172 and paa.primary_flag = 'Y'
173 and ((c_effective_date between paa.effective_start_date
174 and paa.effective_end_date
175 )
176 Or
177 ( paa.effective_end_date = ( Select max(asx.effective_end_date)
178 from per_all_assignments_f asx
179 Where asx.assignment_id = paa.assignment_id
180 and asx.primary_flag = 'Y'
181 and ((asx.effective_end_date between c_extract_start_date
182 and c_extract_end_date
183 )
184 Or
185 (asx.effective_start_date between c_extract_start_date
186 and c_extract_end_date
187 )
188 )
189 )
190 )
191 );
192
193 Type t_pri_asg_type Is Table of csr_assig_rec%ROWTYPE Index By BINARY_INTEGER;
194 g_primary_asg t_pri_asg_type;
195 --
196 -- Cursor to get the secondary assig. records active as of the given period.
197 --
198 Cursor csr_sec_assignments
199 (c_primary_assignment_id In per_all_assignments_f.assignment_id%TYPE
200 ,c_person_id In per_all_people_f.person_id%TYPE
201 ,c_effective_date In date
202 ,c_extract_start_date In date
203 ,c_extract_end_date In Date ) Is
204 Select *
205 From per_all_assignments_f asg
206 Where asg.person_id = c_person_id
207 And asg.assignment_id <> c_primary_assignment_id
208 And asg.assignment_type ='E'
209 And (( c_effective_date Between asg.effective_start_date
210 And asg.effective_end_date
211 )
212 Or
213 ( asg.effective_end_date = ( Select max(asx.effective_end_date)
214 from per_all_assignments_f asx
215 Where asx.assignment_id = asg.assignment_id
216 and asx.person_id = c_person_id
217 and asx.assignment_type ='E'
218 and ((asx.effective_end_date between c_extract_start_date
219 and c_extract_end_date
220 )
221 Or
222 (asx.effective_start_date between c_extract_start_date
223 and c_extract_end_date
224 )
225 )
226 )
227 )
228 )
229 ORDER BY asg.effective_start_date ASC; -- effective first then future rows
230 TYPE t_sec_asgs_type IS TABLE OF csr_sec_assignments%ROWTYPE INDEX BY BINARY_INTEGER;
231 g_all_sec_asgs t_sec_asgs_type;
232
233 -- ============================
234 -- ~ Functions Declarations ~
235 -- ============================
236 Function Get_Header_Information
237 (p_header_type In varchar2
238 ,p_header_name In out nocopy Varchar2) Return Number;
239
240 Function Get_Payroll_Names
241 (p_effective_date In Date
242 ,p_payroll_name In varchar2) Return Varchar2;
243 Function Get_Payroll_Name
244 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
245 ,p_payroll_name In out nocopy varchar2) Return Number;
246
247 Function Get_Payroll_Start_Date
248 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
249 ,p_start_date In out nocopy Varchar2) Return Number;
250
251 Function Get_Payroll_End_Date
252 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
253 ,p_end_date In out nocopy Varchar2) Return Number;
254
255 Function Get_Actual_Pay_Date
256 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
257 ,p_pay_date In out nocopy Varchar2) Return Number;
258
259 Function Get_SRS_Plan_Name
260 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
261 ,p_SRS_Plan_Name In out nocopy Varchar2) Return Number;
262
263 Function Get_Separation_Date
264 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
265 ,p_Separation_Date In out nocopy Varchar2) Return Number;
266
267 Function Get_Assig_Status
268 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
269 ,p_status_code In out nocopy Varchar2) Return Number;
270
271 Function Get_Person_Indentifier
272 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE -- context
273 ,p_person_identifier In out nocopy Varchar2 ) Return Number;
274
275 Function Get_SRS_Deduction_Balances
276 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
277 ,p_balance_name In pay_balance_types.balance_name%TYPE
278 ,p_balance_amount In out nocopy Number
279 ) Return Number;
280
281 Function Get_Balance_Value
282 (p_business_group_id In per_all_assignments_f.business_group_id%TYPE
283 ,p_assignment_id In per_all_assignments_f.assignment_id%TYPE
284 ,p_effective_date In date
285 ,p_balance_name In varchar2
286 ,p_dimension_name In varchar2
287 ) Return Number;
288
289 Function Get_Plan_Names
290 (p_effective_date In Date
291 ,p_extract_name In varchar2 ) Return Varchar2;
292
293 Function Pay_US_SRS_Main_Criteria
294 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
295 ,p_effective_date In date
296 ,p_business_group_id In per_all_assignments_f.business_group_id%TYPE
297 ,p_extract_plan_name In Varchar2
298 ,p_extract_payroll_name In Varchar2 Default Null
299 ) Return Varchar2;
300
301 Function Create_Secondary_Assig_Lines
302 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
303 ) Return Varchar2;
304 Procedure Create_New_Lines
305 (p_pri_assignment_id In per_all_assignments_f.assignment_id%TYPE
306 ,p_sec_assignment_id In per_all_assignments_f.assignment_id%TYPE
307 ,p_person_id In per_all_people_f.person_id%TYPE
308 ,p_record_name In Varchar2
309 );
310
311 Function Get_Secondary_Assignments
312 (p_primary_assignment_id In per_all_assignments_f.assignment_id%TYPE
313 ,p_person_id In per_all_people_f.person_id%TYPE
314 ,p_effective_date In date
315 ,p_extract_start_date In date
316 ,p_extract_end_date In date ) Return Varchar2;
317
318 Function Check_Assig_Extra_Info
319 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
320 ,p_extract_plan_name In varchar2
321 ,p_extract_start_date In date
322 ,p_extract_end_date In date
323 ) Return Varchar2 ;
324
325 Function Del_Service_Detail_Recs
326 (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
327 )Return Number;
328
329 ---Added new function to get EE DCP contribution Limit
330 Function get_dcp_limit
331 (p_effective_date date
332 ) Return number;
333
334 ---------------------------
335 -- ============================
336 -- ~ Procedures ~
337 -- ============================
338 Procedure Update_Record_Values
339 (p_ext_rcd_id In ben_ext_rcd.ext_rcd_id%TYPE
340 ,p_ext_data_element_name In ben_ext_data_elmt.name%TYPE
341 ,p_data_element_value In ben_ext_rslt_dtl.val_01%TYPE
342 ,p_data_ele_seqnum In Number Default Null
343 ,p_ext_dtl_rec In out nocopy ben_ext_rslt_dtl%ROWTYPE
344 );
345
346 Function Get_PTD_Start_End_Date
347 (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
348 ,p_effective_date In Date
349 ) Return Varchar2;
350
351 Procedure Ins_Rslt_Dtl
352 (p_dtl_rec In out nocopy ben_ext_rslt_dtl%ROWTYPE );
353
354
355 End PQP_US_SRS_Extracts;
[Home] [Help]