[Home] [Help]
PACKAGE: APPS.PAY_IN_24QC_ARCHIVE
Source
1 PACKAGE pay_in_24qc_archive AS
2 /* $Header: pyin24qc.pkh 120.1.12010000.1 2008/07/27 22:52:17 appldev ship $ */
3
4 TYPE t_org_data_rec IS RECORD
5 (gre_id NUMBER
6 ,last_action_context_id NUMBER
7 );
8
9 TYPE t_org_data_tab_type IS TABLE OF t_org_data_rec
10 INDEX BY binary_integer;
11
12 TYPE t_element_entry_rec IS RECORD
13 (assignment_id NUMBER
14 ,element_entry_id NUMBER
15 ,deductee_mode VARCHAR2(5)
16 ,last_action_context_id NUMBER
17 );
18
19 TYPE t_element_entry_data_rec IS TABLE OF t_element_entry_rec
20 INDEX BY binary_integer;
21
22 TYPE t_salary_rec IS RECORD
23 (assignment_id NUMBER
24 ,source_id NUMBER
25 ,salary_mode VARCHAR2(5)
26 ,last_action_context_id NUMBER
27 );
28
29 TYPE t_salary_data_rec IS TABLE OF t_salary_rec
30 INDEX BY binary_integer;
31
32 TYPE t_asg_rec IS RECORD
33 (gre_id NUMBER
34 ,start_date DATE
35 ,end_date DATE
36 );
37
38 TYPE t_asg_tab IS TABLE OF t_asg_rec
39 INDEX BY BINARY_INTEGER;
40
41 TYPE t_bal_name_rec IS RECORD
42 (
43 balance_name VARCHAR2(240)
44 );
45
46 TYPE t_bal_name_tab IS TABLE OF t_bal_name_rec
47 INDEX BY BINARY_INTEGER;
48
49 g_asg_tab t_asg_tab;
50 g_fin_start_date DATE;
51 g_fin_end_date DATE;
52
53 type t_balance_value_rec is record
54 (
55 balance_name VARCHAR2(240),
56 balance_value NUMBER
57 );
58
59 TYPE t_balance_value_tab IS TABLE OF t_balance_value_rec
60 INDEX BY BINARY_INTEGER;
61
62
63 TYPE t_challan_entry_rec IS RECORD
64 (transfer_voucher_number VARCHAR2(240)
65 ,transfer_voucher_date VARCHAR2(240)
66 ,amount VARCHAR2(240)
67 ,surcharge VARCHAR2(240)
68 ,education_cess VARCHAR2(240)
69 ,interest VARCHAR2(240)
70 ,other VARCHAR2(240)
71 ,bank_branch_code VARCHAR2(240)
72 ,cheque_dd_num VARCHAR2(240)
73 ,org_information_id NUMBER
74 ,modes VARCHAR2(5)
75 ,book_entry VARCHAR2(240)
76 );
77
78 TYPE t_challan_data_rec IS TABLE OF t_challan_entry_rec
79 INDEX BY binary_integer;
80
81 TYPE t_screen_entry_value_rec IS RECORD
82 (
83 challan_number VARCHAR2(240)
84 ,payment_date VARCHAR2(240)
85 ,amount_deposited VARCHAR2(240)
86 ,surcharge VARCHAR2(240)
87 ,education_cess VARCHAR2(240)
88 ,income_tax VARCHAR2(240)
89 ,taxable_income VARCHAR2(240)
90 );
91
92 TYPE t_screen_entry_table_data IS TABLE OF t_screen_entry_value_rec
93 INDEX BY binary_integer;
94
95 TYPE t_person_data_rec IS RECORD
96 (person_id per_all_people_f.person_id%TYPE
97 ,pan_number per_all_people_f.per_information14%TYPE
98 ,pan_ref_number per_all_people_f.per_information14%TYPE
99 ,full_name per_all_people_f.full_name%TYPE
100 ,tax_rate per_assignment_extra_info.aei_information2 %TYPE
101 );
102
103 TYPE t_person_record IS TABLE OF t_person_data_rec
104 INDEX BY binary_integer;
105
106 TYPE t_person_data_sal_rec IS RECORD
107 (person_id per_all_people_f.person_id%TYPE
108 ,pan_number per_all_people_f.per_information14%TYPE
109 ,pan_ref_number per_all_people_f.per_information14%TYPE
110 ,full_name per_all_people_f.full_name%TYPE
111 ,start_date DATE
112 ,end_date DATE
113 );
114
115 TYPE t_person_sal_record IS TABLE OF t_person_data_sal_rec
116 INDEX BY binary_integer;
117
118 g_ee_data_rec_del t_element_entry_data_rec;
119 g_ee_data_rec_add t_element_entry_data_rec;
120 g_ee_data_rec_upd t_element_entry_data_rec;
121 g_challan_data_add t_challan_data_rec;
122 g_challan_data_upd t_challan_data_rec;
123 g_challan_data_noc t_challan_data_rec;
124 g_org_data t_org_data_tab_type;
125
126 g_payroll_action_id NUMBER;
127 g_24q_payroll_act_id NUMBER;
128 g_24qc_payroll_act_id NUMBER;
129 g_chln_element_id NUMBER;
130 g_count_ee_delete NUMBER := 1;
131 g_count_ee_addition NUMBER := 1;
132 g_count_ee_update NUMBER := 1;
133 g_count_challan_add NUMBER := 1;
134 g_count_challan_upd NUMBER := 1;
135 g_count_challan_noc NUMBER := 1;
136 g_count_org NUMBER := 1;
137
138 g_qr_start_date DATE;
139 g_start_date DATE;
140 g_end_date DATE;
141 g_qr_end_date DATE;
142 g_session_date DATE;
143
144 g_correction_mode VARCHAR2(5);
145 g_24qc_empr_change VARCHAR2(5);
146 g_24qc_rep_adr_chg VARCHAR2(5);
147 g_quarter VARCHAR2(5);
148 g_tax_year VARCHAR2(20);
149 g_year VARCHAR2(20);
150 g_gre_id VARCHAR2(20);
151 g_cancel_ref_number VARCHAR2(250);
152 g_24qc_reference VARCHAR2(250);
153 g_package CONSTANT VARCHAR2(100) := 'pay_in_24qc_archive';
154
155 g_count_sal_delete NUMBER := 1;
156 g_count_sal_addition NUMBER := 1;
157 g_count_sal_update NUMBER := 1;
158
159 g_sal_data_rec_del t_salary_data_rec;
160 g_sal_data_rec_add t_salary_data_rec;
161 g_sal_data_rec_upd t_salary_data_rec;
162
163
164 g_debug BOOLEAN;
165 g_action BOOLEAN := TRUE;
166 g_sal_action BOOLEAN := TRUE;
167
168 --------------------------------------------------------------------------
169 -- --
170 -- Name : CHECK_C5_CHANGE_ONLY --
171 -- Type : FUNCTION --
172 -- Access : Public --
173 -- Description : This function is used to determine C5 change --
174 -- --
175 -- Parameters : --
176 -- IN : p_element_entry_id --
177 -- : p_action_context_id --
178 -- : p_assignment_id --
179 -- OUT : BOOLEAN --
180 -- --
181 -- Change History : --
182 --------------------------------------------------------------------------
183 -- Rev# Date Userid Description --
184 --------------------------------------------------------------------------
185 -- 115.0 5-Jan-2006 aaagarwa Initial Version --
186 --------------------------------------------------------------------------
187 FUNCTION check_c5_change_only
188 (
189 p_element_entry_id IN NUMBER
190 ,p_action_context_id IN NUMBER
191 ,p_assignment_id IN NUMBER
192 )
193 RETURN BOOLEAN;
194
195 --------------------------------------------------------------------------
196 -- --
197 -- Name : REMOVE_CURR_FORMAT --
198 -- Type : FUNCTION --
199 -- Access : Public --
200 -- Description : This function is used to remove currency formatting --
201 -- --
202 -- Parameters : --
203 -- IN : p_value --
204 -- OUT : VARCHAR2 --
205 -- --
206 -- Change History : --
207 --------------------------------------------------------------------------
208 -- Rev# Date Userid Description --
209 --------------------------------------------------------------------------
210 -- 115.0 5-Jan-2006 aaagarwa Initial Version --
211 --------------------------------------------------------------------------
212 FUNCTION remove_curr_format(p_value IN VARCHAR2)
213 RETURN VARCHAR2;
214
215 --------------------------------------------------------------------------
216 -- --
217 -- Name : GET_24Q_VALUES --
218 -- Type : FUNCTION --
219 -- Access : Public --
220 -- Description : This function returns the values corresponding to --
221 -- the F16 Balances --
222 -- Parameters : --
223 -- IN : p_category VARCHAR2 --
224 -- p_component_name VARCHAR2 --
225 -- p_context_id NUMBER --
226 -- p_source_id NUMBER --
227 -- p_segment_num NUMBER --
228 --------------------------------------------------------------------------
229 FUNCTION get_24Q_values (p_category IN VARCHAR2
230 ,p_component_name IN VARCHAR2
231 ,p_context_id IN NUMBER
232 ,p_source_id IN NUMBER
233 ,p_segment_num IN NUMBER
234 )
235 RETURN VARCHAR2;
236
237 --------------------------------------------------------------------------
238 -- --
239 -- Name : GET_FORMAT_VALUE --
240 -- Type : FUNCTION --
241 -- Access : Public --
242 -- Description : This function returns value with precision --
243 -- of two decimal place --
244 -- --
245 -- Parameters : --
246 -- IN : p_value VARCHAR2 --
247 --------------------------------------------------------------------------
248 FUNCTION get_format_value(p_value IN VARCHAR2)
249 RETURN VARCHAR2;
250
251 --------------------------------------------------------------------------
252 -- --
253 -- Name : RANGE_CODE --
254 -- Type : PROCEDURE --
255 -- Access : Public --
256 -- Description : This procedure returns a sql string to select a --
257 -- range of assignments eligible for archival. --
258 -- --
259 -- Parameters : --
260 -- IN : p_payroll_action_id NUMBER --
261 -- OUT : p_sql VARCHAR2 --
262 -- --
263 --------------------------------------------------------------------------
264 PROCEDURE range_code
265 (
266 p_payroll_action_id IN NUMBER
267 ,p_sql OUT NOCOPY VARCHAR2
268 );
269
270 --------------------------------------------------------------------------
271 -- --
272 -- Name : INITIALIZATION_CODE --
273 -- Type : PROCEDURE --
274 -- Access : Public --
275 -- Description : This procedure is used to set global contexts. --
276 -- Parameters : --
277 -- IN : p_payroll_action_id NUMBER --
278 -- OUT : N/A --
279 -- --
280 --------------------------------------------------------------------------
281 PROCEDURE initialization_code
282 (
283 p_payroll_action_id IN NUMBER
284 );
285
286 --------------------------------------------------------------------------
287 -- --
288 -- Name : ASSIGNMENT_ACTION_CODE --
289 -- Type : PROCEDURE --
290 -- Access : Public --
291 -- Description : This procedure further restricts the assignment_id's--
292 -- returned by range_code --
293 -- --
294 -- Parameters : --
295 -- IN : p_payroll_action_id NUMBER --
296 -- p_start_person NUMBER --
297 -- p_end_person NUMBER --
298 -- p_chunk NUMBER --
299 -- OUT : N/A --
300 --------------------------------------------------------------------------
301 PROCEDURE assignment_action_code
302 (
303 p_payroll_action_id IN NUMBER
304 ,p_start_person IN NUMBER
305 ,p_end_person IN NUMBER
306 ,p_chunk IN NUMBER
307 );
308
309 --------------------------------------------------------------------------
310 -- --
311 -- Name : ASSIGNMENT_END_DATE --
312 -- Type : FUNCTION --
313 -- Access : Private --
314 -- Description : This function returns the end date of an assignment --
315 -- : subjected to Quarter end date --
316 -- Parameters : --
317 -- IN : p_assignment_id NUMBER --
318 -- OUT : p_end_date DATE --
319 -- --
320 -- Change History : --
321 --------------------------------------------------------------------------
322 -- Rev# Date Userid Description --
323 --------------------------------------------------------------------------
324 -- 115.0 13-Mar-2006 aaagarwa Initial Version --
325 --------------------------------------------------------------------------
326 FUNCTION assignment_end_date(p_assignment_id IN NUMBER
327 )
328 RETURN DATE;
329
330 --------------------------------------------------------------------------
331 -- --
332 -- Name : ARCHIVE_CODE --
333 -- Type : PROCEDURE --
334 -- Access : Public --
335 -- Description : Procedure to call the internal procedures to --
336 -- actually archive the data. --
337 -- --
338 -- Parameters : --
339 -- IN : p_assignment_action_id NUMBER --
340 -- p_effective_date DATE --
341 -- OUT : N/A --
342 --------------------------------------------------------------------------
343 PROCEDURE archive_code
344 (
345 p_assignment_action_id IN NUMBER
346 ,p_effective_date IN DATE
347 );
348
349 --------------------------------------------------------------------------
350 -- --
351 -- Name : DEINITIALIZATION_CODE --
352 -- Type : PROCEDURE --
353 -- Access : Public --
354 -- Description : --
355 -- Parameters : --
356 -- IN : p_payroll_action_id NUMBER --
357 -- --
358 -- OUT : N/A --
359 --------------------------------------------------------------------------
360 PROCEDURE deinitialization_code(p_payroll_action_id IN NUMBER);
361
362 END pay_in_24qc_archive;