1 PACKAGE PQP_NL_PGGM_PENSION_EXTRACTS AS
2 /* $Header: pqpnlpggmpext.pkh 120.2 2006/08/29 17:26:30 sashriva noship $ */
3 g_conc_request_id NUMBER;
4 g_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
5 g_action_effective_date DATE;
6 g_action_type VARCHAR2(50);
7 g_asgrun_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
8 g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
9 g_person_id per_all_assignments_f.person_id%TYPE;
10 g_retro_ptp_count NUMBER := 0;
11 g_debug BOOLEAN := FALSE;
12 g_legislation_code per_business_groups.legislation_code%TYPE;
13 g_business_group_id per_business_groups.business_group_id%TYPE;
14 g_ptp_index NUMBER:=0;
15 g_ptp_chg_date DATE;
16 g_ptp_chg_screen_value VARCHAR2(50);
17
18 g_rec_060_count NUMBER:=0;
19 g_060_index NUMBER:=0;
20 g_rec060_mult_flag VARCHAR2(1):='N';
21
22 g_rec_080_type1_count NUMBER:=0;
23 g_rec_080_type2_count NUMBER:=0;
24 g_rec_080_type3_count NUMBER:=0;
25 g_rec_080_type4_count NUMBER:=0;
26 g_080_index NUMBER:=0;
27 g_rec080_mult_flag VARCHAR2(1):='N';
28 g_080_display_flag varchaR2(1):='N';
29 --Index for storing result detail ids which will be delete in post process
30 g_index_rslt_dtl NUMBER:=0;
31 g_pggm_employer_num varchar2(6):='000000';
32 g_main_rec_081 varchar2(1):='N';
33 g_rec_081_type varchar2(1):='C';
34 g_rec_081_count NUMBER:=0;
35 g_081_index NUMBER:=0;
36
37
38
39
40 TYPE extract_params IS RECORD
41 (session_id NUMBER
42 ,ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE
43 ,business_group_id per_business_groups.business_group_id%TYPE
44 ,legislation_code per_business_groups.legislation_code%TYPE
45 ,currency_code per_business_groups.currency_code%TYPE
46 ,concurrent_req_id ben_ext_rslt.request_id%TYPE
47 ,ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE
48 ,element_set_id pay_element_sets.element_set_id%TYPE
49 ,element_type_id pay_element_types_f.element_type_id%TYPE
50 ,payroll_id pay_payrolls_f.payroll_id%TYPE
51 ,org_id hr_all_organization_units.organization_id%TYPE
52 ,gre_org_id hr_all_organization_units.organization_id%TYPE
53 ,con_set_id pay_consolidation_sets.consolidation_set_id%TYPE
54 ,selection_criteria VARCHAR2(90)
55 ,reporting_dimension VARCHAR2(90)
56 ,extract_start_date DATE
57 ,extract_end_date DATE
58 ,extract_type VARCHAR2(1)
59 );
60 TYPE t_extract_params IS TABLE OF extract_params INDEX BY Binary_Integer;
61 g_extract_params t_extract_params;
62
63 TYPE org_details IS RECORD
64 (business_group_id per_business_groups.business_group_id%TYPE
65 ,legislation_code per_business_groups.legislation_code%TYPE
66 ,gre_org_id hr_all_organization_units.organization_id%TYPE
67 );
68 TYPE t_org_details IS TABLE OF org_details INDEX BY Binary_Integer;
69 g_ord_details t_org_details;
70 g_ord_details1 t_org_details;
71
72 TYPE assig_details IS RECORD
73 (person_id per_all_assignments_f.person_id%TYPE
74 ,organization_id per_all_assignments_f.organization_id%TYPE
75 ,assignment_type per_all_assignments_f.assignment_type%TYPE
76 ,effective_start_date DATE
77 ,effective_end_date DATE
78 ,assignment_status per_assignment_status_types.user_status%TYPE
79 ,employment_category hr_lookups.meaning%TYPE
80 ,date_start DATE
81 ,termination_date DATE
82 ,payroll_id pay_payrolls_f.payroll_id%TYPE
83 ,pggm_er_num VARCHAR2(10)
84 ,ee_num per_all_people_f.employee_number%TYPE
85 ,asg_seq_num per_all_assignments_f.assignment_sequence%TYPE
86 ,ni_num per_all_people_f.national_identifier%TYPE
87 ,per_ln per_all_people_f.last_name%TYPE
88 ,per_initials per_all_people_f.per_information1%TYPE
89 ,per_prefix per_all_people_f.pre_name_adjunct%TYPE
90 ,gender per_all_people_f.sex%TYPE
91 ,dob per_all_people_f.date_of_birth%TYPE
92 ,partner_last_name per_all_people_f.last_name%TYPE
93 ,partner_prefix per_all_people_f.pre_name_adjunct%TYPE
94 ,address_fem_ee per_all_people_f.per_information14%TYPE
95 ,marital_status per_all_people_f.marital_status%TYPE
96 ,primary_flag per_all_assignments_f.primary_flag%TYPE
97 );
98
99 TYPE t_assig_details IS TABLE OF assig_details INDEX BY BINARY_INTEGER;
100 g_primary_assig t_assig_details;
101
102 --Retro Hire
103 TYPE retro_hires IS RECORD
104 (person_id number
105 ,new_hire date
106 ,old_hire date
107 ,type number);
108
109 TYPE t_retro_hires IS TABLE OF retro_hires INDEX BY BINARY_INTEGER;
110 g_retro_hires t_retro_hires;
111
112 --Extract Records
113 TYPE ben_ext_rcds IS RECORD
114 (record_number Varchar2(3)
115 ,record_seqnum ben_ext_rcd_in_file.seq_num%TYPE
116 ,hide_flag ben_ext_rcd_in_file.hide_flag%TYPE
117 ,ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE
118 ,rcd_type_cd ben_ext_rcd.rcd_type_cd%TYPE
119 );
120 TYPE t_g_ext_rcds IS TABLE OF ben_ext_rcds INDEX BY Binary_Integer;
121 g_ext_rcds t_g_ext_rcds;
122
123 --Balance Details
124 TYPE balance_details IS RECORD
125 ( balance_name pay_balance_types.balance_name%TYPE
126 ,balance_type_id pay_balance_types.balance_type_id%TYPE
127 ,defined_balance_id pay_defined_balances.defined_balance_id%TYPE
128 );
129 TYPE t_balance_details IS TABLE OF balance_details INDEX BY Binary_Integer;
130 g_balance_detls t_balance_details;
131
132 --Record ID s with Seq numbers
133 TYPE rcd_dtls IS RECORD
134 ( ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE
135 );
136 TYPE t_rcd_dtls IS TABLE OF rcd_dtls INDEX BY Binary_Integer;
137 g_rcd_dtls t_rcd_dtls;
138 g_retro_rcd t_rcd_dtls;
139
140 --Added for concurrent program parameter
141 TYPE conc_prog_details IS RECORD
142 ( extract_name ben_ext_dfn.name%TYPE
143 ,reporting_options hr_lookups.meaning%TYPE
144 ,selection_criteria hr_lookups.meaning%TYPE
145 ,elementset pay_element_sets.element_set_name%TYPE
146 ,elementname pay_element_types_f.element_name%TYPE
147 ,beginningdt DATE
148 ,endingdt DATE
149 ,extract_type VARCHAR2(1)
150 ,payrollname pay_payrolls_f.payroll_name%TYPE
151 ,consolset pay_consolidation_sets.consolidation_set_name%TYPE
152 ,orgname hr_all_organization_units.name%TYPE
153 ,orgid hr_all_organization_units.organization_id%TYPE
154 );
155
156 TYPE t_conc_prog_details IS TABLE OF conc_prog_details INDEX BY Binary_Integer;
157 g_conc_prog_details t_conc_prog_details;
158
159 --Child organizations
160 TYPE org_list IS RECORD
161 ( org_id hr_all_organization_units.organization_id%TYPE);
162
163 TYPE t_org_list IS TABLE OF org_list INDEX BY Binary_Integer;
164 g_org_list t_org_list;
165
166 --Record 040 details
167 TYPE rcd_040 IS RECORD
168 ( address_dt_chg DATE
169 );
170 TYPE t_rcd_040 IS TABLE OF rcd_040 INDEX BY Binary_Integer;
171 g_rcd_040 t_rcd_040;
172
173 --Record 060 details
174 TYPE rcd_060 IS RECORD
175 ( pension_sal_amount Number,
176 pension_sal_dt_change DATE,
177 element_type varchar2(1)
178 );
179 TYPE t_rcd_060 IS TABLE OF rcd_060 INDEX BY Binary_Integer;
180 g_rcd_060 t_rcd_060;
181
182
183 --Record 080 details
184 TYPE rcd_080 IS RECORD
185 ( part_time_pct_dt_change DATE,
186 part_time_factor Number,
187 incidental_code Number
188 );
189 TYPE t_rcd_080 IS TABLE OF rcd_080 INDEX BY Binary_Integer;
190 g_rcd_080 t_rcd_080;
191
192 --Record 081 details
193 TYPE rcd_081 IS RECORD
194 ( year_of_change varchar2(10)
195 );
196 TYPE t_rcd_081 IS TABLE OF rcd_081 INDEX BY Binary_Integer;
197 g_rcd_081 t_rcd_081;
198
199 --Delete Result Records Detail
200 TYPE delete_rslt_dtl IS RECORD
201 ( ext_rslt_dtl_id NUMBER
202 );
203 TYPE t_delete_rslt_dtl IS TABLE OF delete_rslt_dtl INDEX BY Binary_Integer;
204 g_delete_rslt_dtl t_delete_rslt_dtl;
205
206
207
208 --Delete Result Records Detail
209 TYPE per_details IS RECORD
210 ( national_identifier varchar2(9),
211 date_of_birth varchar2(6),
212 last_name varchar2(18),
213 prefix varchar2(7)
214 );
215 TYPE t_per_details IS TABLE OF per_details INDEX BY Binary_Integer;
216 g_per_details t_per_details;
217
218 -- =============================================================================
219 -- Pension_Extract_Process:This function is called from Concurrent Request
220 -- =============================================================================
221 PROCEDURE Pension_Extract_Process
222 (errbuf OUT NOCOPY VARCHAR2
223 ,retcode OUT NOCOPY VARCHAR2
224 ,p_benefit_action_id IN NUMBER
225 ,p_ext_dfn_id IN NUMBER
226 ,p_org_id IN NUMBER
227 ,p_payroll_id IN NUMBER
228 ,p_start_date IN VARCHAR2
229 ,p_end_date IN VARCHAR2
230 ,p_extract_type IN VARCHAR2
231 ,p_business_group_id IN NUMBER
232 ,p_consolidation_set IN NUMBER
233 ,p_ext_rslt_id IN NUMBER DEFAULT NULL
234 );
235
236 -- =============================================================================
237 -- Pension_Criteria_Full_Profile: The Main extract criteria that would be used
238 -- for the pension extract.
239 -- =============================================================================
240 FUNCTION Pension_Criteria_Full_Profile
241 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
242 ,p_effective_date IN date
243 ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
244 ,p_warning_message OUT NOCOPY VARCHAR2
245 ,p_error_message OUT NOCOPY VARCHAR2
246 ) RETURN VARCHAR2;
247 -- ====================================================================
248 -- pqp_nl_get_data_element_value:
249 -- ====================================================================
250 FUNCTION PQP_NL_Get_Data_Element_Value
251 ( p_assignment_id IN NUMBER
252 ,p_business_group_id IN NUMBER
253 ,p_date_earned IN DATE
254 ,p_data_element_cd IN VARCHAR2
255 ,p_error_message OUT NOCOPY VARCHAR2
256 ,p_data_element_value OUT NOCOPY VARCHAR2
257 ) RETURN NUMBER ;
258 -- =============================================================================
259 -- Chk_If_Req_To_Extract: For a given assignment check to see the record needs to
260 -- be extracted or not.
261 -- =============================================================================
262 FUNCTION Chk_If_Req_To_Extract
263 (p_assignment_id IN Number
264 ,p_business_group_id IN Number
265 ,p_effective_date IN Date
266 ,p_record_num IN Varchar2
267 ,p_error_message OUT NOCOPY Varchar2) RETURN Varchar2;
268
269 -- =============================================================================
270 -- Get_Conc_Prog_Information: Get Header Information
271 -- =============================================================================
272 FUNCTION Get_Conc_Prog_Information
273 (p_header_type IN Varchar2
274 ,p_error_message OUT NOCOPY Varchar2
275 ,p_data_element_value OUT NOCOPY Varchar2)
276 RETURN Number;
277 -- ====================================================================
278 -- Post Process
279 -- ====================================================================
280 FUNCTION Sort_Post_Process
281 (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
282 )RETURN Number;
283 -- ====================================================================
284 -- Raise_Extract_Warning:
285 -- ====================================================================
286 FUNCTION Raise_Extract_Warning
287 (p_assignment_id IN NUMBER -- context
288 ,p_error_text IN VARCHAR2
289 ,p_error_number IN NUMBER DEFAULT NULL
290 ) RETURN NUMBER;
291
292 END PQP_NL_PGGM_Pension_Extracts;
[Home] [Help]