1 PACKAGE PAY_NL_TAXOFFICE_ARCHIVE as
2 /* $Header: pynltosa.pkh 120.0.12000000.2 2007/04/19 09:58:01 abhgangu noship $ */
3 level_cnt NUMBER;
4
5 TYPE UserBalRec IS RECORD(
6 BalName VARCHAR2(1000),
7 TagName VARCHAR2(1000));
8
9 TYPE tUserBalTable IS TABLE OF UserBalRec INDEX BY BINARY_INTEGER;
10 vUserBalTable tUserBalTable;
11
12 /*------------------------------------------------------------------------------
13 |Name : GET_PARAMETER |
14 |Type : Function |
15 |Description : Funtion to get the parameters of the archive process |
16 -------------------------------------------------------------------------------*/
17
18 Function get_parameter(
19 p_parameter_string in varchar2
20 ,p_token in varchar2
21 ,p_segment_number in number default null )RETURN varchar2;
22
23 /*-------------------------------------------------------------------------------
24 |Name : RANGE_CODE |
25 |Type : Procedure |
26 |Description : This procedure returns a sql string to select a range of |
27 | assignments eligible for archival |
28 -------------------------------------------------------------------------------*/
29
30 Procedure RANGE_CODE (pactid IN NUMBER
31 ,sqlstr OUT NOCOPY VARCHAR2);
32
33
34 /*--------------------------------------------------------------------
35 |Name : ASSIGNMENT_ACTION_CODE |
36 |Type : Procedure |
37 |Description: This procedure Fetches,validates and archives |
38 | information in the newly created context |
39 | NL ATS EMPLOYEE DETAILS |
40 ----------------------------------------------------------------------*/
41
42 Procedure ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
43 ,p_start_person_id in number
44 ,p_end_person_id in number
45 ,p_chunk in number);
46
47
48 /*----------------------------------------------------------------------------------
49 |Name : ARCHIVE_INIT |
50 |Type : Procedure |
51 |Description : Initialization Code for Archiver |
52 -----------------------------------------------------------------------------------*/
53
54 Procedure ARCHIVE_INIT(p_payroll_action_id IN NUMBER);
55
56 /*-------------------------------------------------------------------------------
57 |Name : ARCHIVE_CODE |
58 |Type : Procedure |
59 |Description : Archival code for archiver |
60 -------------------------------------------------------------------------------*/
61
62 Procedure ARCHIVE_CODE (p_assignment_action_id IN NUMBER
63 ,p_effective_date IN DATE);
64
65
66
67 /*-----------------------------------------------------------------------------
68 |Name : GET_ALL_PARAMETERS |
69 |Type : Procedure |
70 |Description: Procedure which returns all the parameters of the archive process|
71 -------------------------------------------------------------------------------*/
72
73 PROCEDURE get_all_parameters (
74 p_payroll_action_id IN NUMBER
75 ,p_business_group_id OUT NOCOPY NUMBER
76 ,p_effective_date OUT NOCOPY DATE
77 ,p_tax_year OUT NOCOPY DATE
78 ,p_employer OUT NOCOPY number ) ;
79 -- ,p_org_struct_id OUT NOCOPY number ) ;
80
81 /*-----------------------------------------------------------------------------
82 |Name : get_max_assgt_act_id |
83 |Type : Function |
84 |Description: Function which returns the max. assignment_action_id for a given |
85 | assignment_id between a given start and end date |
86 -------------------------------------------------------------------------------*/
87
88
89 function get_max_assgt_act_id(p_assignment_id number
90 ,p_date_from date
91 ,p_date_to date)RETURN number;
92
93
94 /*-----------------------------------------------------------------------------
95 |Name : get_context_id |
96 |Type : Function |
97 |Description: Function which returns the context id for a given context neme |
98 -------------------------------------------------------------------------------*/
99
100
101
102 function get_context_id(p_context_name VARCHAR2)return number;
103
104
105
106 /*-----------------------------------------------------------------------------
107 |Name : get_wage |
108 |Type : Function |
109 |Description: Function which returns the wage for a given assignment action |
110 -------------------------------------------------------------------------------*/
111
112 function get_wage(p_assgt_act_id number)RETURN number;
113
114
115
116 /*-----------------------------------------------------------------------------
117 |Name : get_taxable_income |
118 |Type : Function |
119 |Description: Function which returns the taxable income for a given assignment |
120 | action |
121 -------------------------------------------------------------------------------*/
122
123 function get_taxable_income(p_assgt_act_id number)RETURN number;
124
125
126
127 /*-----------------------------------------------------------------------------
128 |Name : get_deduct_wage_tax_si_cont |
129 |Type : Function |
130 |Description: Function which returns the deduct_wage_tax value |
131 | for a given assignment action |
132 -------------------------------------------------------------------------------*/
133
134 function get_deduct_wage_tax_si_cont(p_assgt_act_id number) return number;
135
136
137 /*-----------------------------------------------------------------------------
138 |Name : get_labour_discount |
139 |Type : Function |
140 |Description: Function which returns the labour discount value |
141 | for a given assignment action |
142 -------------------------------------------------------------------------------*/
143
144 function get_labour_discount(p_assgt_act_id number) return number;
145
146
147
148 /*-----------------------------------------------------------------------------
149 |Name : get_special_indicators |
150 |Type : Procedure |
151 |Description: Procedure which returns the special indicators string |
152 -------------------------------------------------------------------------------*/
153
154
155 Procedure get_special_indicators(p_assgt_act_id in number
156 ,p_assignment_id in number
157 ,p_tax_year_start_date in date
158 ,p_tax_year_end_date in date
159 ,p_special_indicator out nocopy varchar2
160 ,p_Amount_Special_indicator out nocopy varchar2);
161
162 /*-----------------------------------------------------------------------------
163 |Name : get_ZFW_PHI_contributions |
164 |Type : Function |
165 |Description: Function which returns the ZFW PHI contributions |
166 -------------------------------------------------------------------------------*/
167
168 function get_ZFW_PHI_contributions(p_assgt_act_id number) return number;
169
170
171 /*-----------------------------------------------------------------------------
172 |Name : get_PRIVATE_USE_CAR |
173 |Type : Function |
174 |Description: Function which returns the private use car balance value |
175 -------------------------------------------------------------------------------*/
176
177 function get_PRIVATE_USE_CAR(p_assgt_act_id number) return number;
178
179 /*-----------------------------------------------------------------------------
180 |Name : get_NET_EXPENSE_ALLOWANCE |
181 |Type : Function |
182 |Description: Function which returns the NET EXPENSE ALLOWANCE balance value |
183 -------------------------------------------------------------------------------*/
184
185 function get_NET_EXPENSE_ALLOWANCE(p_assgt_act_id number) return number;
186
187 /*-----------------------------------------------------------------------------
188 |Name : get_wage_tax_discount |
189 |Type : Function |
190 |Description: Function which returns the wage tax discount value |
191 -------------------------------------------------------------------------------*/
192
193
194 function get_wage_tax_discount(p_assignment_id number
195 ,p_tax_year_start_date date
196 ,p_tax_year_end_date date) return varchar2;
197
198 /*-----------------------------------------------------------------------------
199 |Name : get_wage_tax_table_code |
200 |Type : Function |
201 |Description: Function which returns the wage tax table code |
202 -------------------------------------------------------------------------------*/
203
204
205 function get_wage_tax_table_code(p_assignment_id number
206 ,p_tax_year_start_date date
207 ,p_tax_year_end_date date) return varchar2;
208
209 /*-----------------------------------------------------------------------------
210 |Name : get_si_insured_flag |
211 |Type : Function |
212 |Description: Function which returns the si insured flag string |
213 -------------------------------------------------------------------------------*/
214
215 function get_si_insured_flag(p_assignment_id number
216 ,p_tax_year_start_date date
217 ,p_tax_year_end_date date) return varchar2;
218
219 /*-----------------------------------------------------------------------------
220 |Name : get_income_code |
221 |Type : Function |
222 |Description: Function which returns the income code |
223 -------------------------------------------------------------------------------*/
224
225 function get_income_code(p_assignment_id number
226 ,p_tax_year_start_date date
227 ,p_tax_year_end_date date)return varchar2;
228
229
230 /*-----------------------------------------------------------------------------
231 |Name : get_org_hierarchy |
232 |Type : Function |
233 |Description: Function which returns organization structure version id |
234 -------------------------------------------------------------------------------*/
235
236 function get_org_hierarchy(p_org_struct_id varchar2
237 ,p_tax_year_end_date date) return number;
238
239 /*-----------------------------------------------------------------------------
240 |Name : get_IZA_contributions |
241 |Type : Function |
242 |Description: Function which returns sum of IZA balances |
243 -------------------------------------------------------------------------------*/
244
245 function get_IZA_contributions(p_assgt_act_id number) return number;
246
247 /*-----------------------------------------------------------------------------
248 |Name : get_ZVW_basis |
249 |Type : Function |
253
250 |Description: Function which returns the ZVW basis |
251 -------------------------------------------------------------------------------*/
252 function get_ZVW_basis(p_assgt_act_id number) return number;
254 /*-----------------------------------------------------------------------------
255 |Name : get_ZVW_contributions |
256 |Type : Function |
257 |Description: Function which returns the ZVW contributions |
258 -------------------------------------------------------------------------------*/
259
260 function get_ZVW_contributions(p_assgt_act_id number) return number;
261
262 /*-----------------------------------------------------------------------------
263 |Name : get_VALUE_PRIVATE_USE_CAR |
264 |Type : Function |
265 |Description: Function which returns the value private use car balance value |
266 -------------------------------------------------------------------------------*/
267
268 function get_VALUE_PRIVATE_USE_CAR(p_assgt_act_id number) return number;
269
270 /*-----------------------------------------------------------------------------
271 |Name : get_LSS_Saved_Amount |
272 |Type : Function |
273 |Description: Function which returns the saved amount for life saving scheme |
274 -------------------------------------------------------------------------------*/
275
276 function get_LSS_Saved_Amount(p_assgt_act_id number) return number;
277
278 /*-----------------------------------------------------------------------------
279 |Name : get_Employer_Part_Child_Care |
280 |Type : Function |
281 |Description: Function which returns the Employer part Child Care balance value|
282 -------------------------------------------------------------------------------*/
283
284 function get_Employer_Part_Child_Care(p_assgt_act_id number) return number;
285
286 /*-----------------------------------------------------------------------------
287 |Name : get_Allowance_On_Disability |
288 |Type : Function |
289 |Description: Function which returns the paid allowance on Disability Allowance|
290 -------------------------------------------------------------------------------*/
291
292 function get_Allowance_On_Disability(p_assgt_act_id number) return number;
293
294 /*-----------------------------------------------------------------------------
295 |Name : get_Applied_LCLD |
296 |Type : Function |
297 |Description: Function which returns the Applied Life Cycle Leave Discount |
298 -------------------------------------------------------------------------------*/
299
300 function get_Applied_LCLD(p_assgt_act_id number) return number;
301
302 /*-----------------------------------------------------------------------------
303 |Name : populate_UserBal |
304 |Type : Procedure |
305 |Description: Procedure which populates pl/sql table with user defined balance |
306 | names and tag names |
307 -------------------------------------------------------------------------------*/
308
309 PROCEDURE populate_UserBal(p_bg_id number, p_effective_date DATE);
310
311 /*-----------------------------------------------------------------------------
312 |Name : get_User_Balances |
313 |Type : Procedure |
314 |Description: Procedure which returns the User Defined Balances |
315 -------------------------------------------------------------------------------*/
316
317 PROCEDURE get_User_Balances (p_assgt_act_id in number
318 ,p_bg_id in number
319 ,p_User_Bal_String out nocopy varchar2);
320
321 /*-----------------------------------------------------------------------------
322 |Name : get_User_Defined_Balance_Id |
323 |Type : Procedure |
324 |Description: Procedure which returns the User Defined Balance Id |
325 -------------------------------------------------------------------------------*/
326
327 FUNCTION get_User_Defined_Balance_Id (p_user_name IN VARCHAR2, p_bg_id IN NUMBER) RETURN NUMBER;
328
329
330 END PAY_NL_TAXOFFICE_ARCHIVE;