[Home] [Help]
PACKAGE: APPS.PAY_IN_24QC_ARCHIVE
Source
1 PACKAGE pay_in_24qc_archive AUTHID CURRENT_USER AS
2 /* $Header: pyin24qc.pkh 120.1.12010000.2 2009/11/04 05:45:25 mdubasi 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_regular_file_date VARCHAR2(15);
154 g_old_format Varchar2(1);
155 g_package CONSTANT VARCHAR2(100) := 'pay_in_24qc_archive';
156
157 g_count_sal_delete NUMBER := 1;
158 g_count_sal_addition NUMBER := 1;
159 g_count_sal_update NUMBER := 1;
160
161 g_sal_data_rec_del t_salary_data_rec;
162 g_sal_data_rec_add t_salary_data_rec;
163 g_sal_data_rec_upd t_salary_data_rec;
164
165
166 g_debug BOOLEAN;
167 g_action BOOLEAN := TRUE;
168 g_sal_action BOOLEAN := TRUE;
169
170 --------------------------------------------------------------------------
171 -- --
172 -- Name : CHECK_C5_CHANGE_ONLY --
173 -- Type : FUNCTION --
174 -- Access : Public --
175 -- Description : This function is used to determine C5 change --
176 -- --
177 -- Parameters : --
178 -- IN : p_element_entry_id --
179 -- : p_action_context_id --
180 -- : p_assignment_id --
181 -- OUT : BOOLEAN --
182 -- --
183 -- Change History : --
184 --------------------------------------------------------------------------
185 -- Rev# Date Userid Description --
186 --------------------------------------------------------------------------
187 -- 115.0 5-Jan-2006 aaagarwa Initial Version --
188 --------------------------------------------------------------------------
189 FUNCTION check_c5_change_only
190 (
191 p_element_entry_id IN NUMBER
192 ,p_action_context_id IN NUMBER
193 ,p_assignment_id IN NUMBER
194 )
195 RETURN BOOLEAN;
196
197 --------------------------------------------------------------------------
198 -- --
199 -- Name : REMOVE_CURR_FORMAT --
200 -- Type : FUNCTION --
201 -- Access : Public --
202 -- Description : This function is used to remove currency formatting --
203 -- --
204 -- Parameters : --
205 -- IN : p_value --
206 -- OUT : VARCHAR2 --
207 -- --
208 -- Change History : --
209 --------------------------------------------------------------------------
210 -- Rev# Date Userid Description --
211 --------------------------------------------------------------------------
212 -- 115.0 5-Jan-2006 aaagarwa Initial Version --
213 --------------------------------------------------------------------------
214 FUNCTION remove_curr_format(p_value IN VARCHAR2)
215 RETURN VARCHAR2;
216
217 --------------------------------------------------------------------------
218 -- --
219 -- Name : GET_24Q_VALUES --
220 -- Type : FUNCTION --
221 -- Access : Public --
222 -- Description : This function returns the values corresponding to --
223 -- the F16 Balances --
224 -- Parameters : --
225 -- IN : p_category VARCHAR2 --
226 -- p_component_name VARCHAR2 --
227 -- p_context_id NUMBER --
228 -- p_source_id NUMBER --
229 -- p_segment_num NUMBER --
230 --------------------------------------------------------------------------
231 FUNCTION get_24Q_values (p_category IN VARCHAR2
232 ,p_component_name IN VARCHAR2
233 ,p_context_id IN NUMBER
234 ,p_source_id IN NUMBER
235 ,p_segment_num IN NUMBER
236 )
237 RETURN VARCHAR2;
238
239 --------------------------------------------------------------------------
240 -- --
241 -- Name : GET_FORMAT_VALUE --
242 -- Type : FUNCTION --
243 -- Access : Public --
244 -- Description : This function returns value with precision --
245 -- of two decimal place --
246 -- --
247 -- Parameters : --
248 -- IN : p_value VARCHAR2 --
249 --------------------------------------------------------------------------
250 FUNCTION get_format_value(p_value IN VARCHAR2)
251 RETURN VARCHAR2;
252
253 --------------------------------------------------------------------------
254 -- --
255 -- Name : RANGE_CODE --
256 -- Type : PROCEDURE --
257 -- Access : Public --
258 -- Description : This procedure returns a sql string to select a --
259 -- range of assignments eligible for archival. --
260 -- --
261 -- Parameters : --
262 -- IN : p_payroll_action_id NUMBER --
263 -- OUT : p_sql VARCHAR2 --
264 -- --
265 --------------------------------------------------------------------------
266 PROCEDURE range_code
267 (
268 p_payroll_action_id IN NUMBER
269 ,p_sql OUT NOCOPY VARCHAR2
270 );
271
272 --------------------------------------------------------------------------
273 -- --
274 -- Name : INITIALIZATION_CODE --
275 -- Type : PROCEDURE --
276 -- Access : Public --
277 -- Description : This procedure is used to set global contexts. --
278 -- Parameters : --
279 -- IN : p_payroll_action_id NUMBER --
280 -- OUT : N/A --
281 -- --
282 --------------------------------------------------------------------------
283 PROCEDURE initialization_code
284 (
285 p_payroll_action_id IN NUMBER
286 );
287
288 --------------------------------------------------------------------------
289 -- --
290 -- Name : ASSIGNMENT_ACTION_CODE --
291 -- Type : PROCEDURE --
292 -- Access : Public --
293 -- Description : This procedure further restricts the assignment_id's--
294 -- returned by range_code --
295 -- --
296 -- Parameters : --
297 -- IN : p_payroll_action_id NUMBER --
298 -- p_start_person NUMBER --
299 -- p_end_person NUMBER --
300 -- p_chunk NUMBER --
301 -- OUT : N/A --
302 --------------------------------------------------------------------------
303 PROCEDURE assignment_action_code
304 (
305 p_payroll_action_id IN NUMBER
306 ,p_start_person IN NUMBER
307 ,p_end_person IN NUMBER
308 ,p_chunk IN NUMBER
309 );
310
311 --------------------------------------------------------------------------
312 -- --
313 -- Name : ASSIGNMENT_END_DATE --
314 -- Type : FUNCTION --
315 -- Access : Private --
316 -- Description : This function returns the end date of an assignment --
317 -- : subjected to Quarter end date --
318 -- Parameters : --
319 -- IN : p_assignment_id NUMBER --
320 -- OUT : p_end_date DATE --
321 -- --
322 -- Change History : --
323 --------------------------------------------------------------------------
324 -- Rev# Date Userid Description --
328 FUNCTION assignment_end_date(p_assignment_id IN NUMBER
325 --------------------------------------------------------------------------
326 -- 115.0 13-Mar-2006 aaagarwa Initial Version --
327 --------------------------------------------------------------------------
329 )
330 RETURN DATE;
331
332 --------------------------------------------------------------------------
333 -- --
334 -- Name : ARCHIVE_CODE --
335 -- Type : PROCEDURE --
336 -- Access : Public --
340 -- Parameters : --
337 -- Description : Procedure to call the internal procedures to --
338 -- actually archive the data. --
339 -- --
341 -- IN : p_assignment_action_id NUMBER --
342 -- p_effective_date DATE --
343 -- OUT : N/A --
344 --------------------------------------------------------------------------
345 PROCEDURE archive_code
346 (
347 p_assignment_action_id IN NUMBER
348 ,p_effective_date IN DATE
349 );
350
351 --------------------------------------------------------------------------
352 -- --
353 -- Name : DEINITIALIZATION_CODE --
354 -- Type : PROCEDURE --
355 -- Access : Public --
356 -- Description : --
357 -- Parameters : --
361 --------------------------------------------------------------------------
358 -- IN : p_payroll_action_id NUMBER --
359 -- --
360 -- OUT : N/A --
362 PROCEDURE deinitialization_code(p_payroll_action_id IN NUMBER);
363
364 END pay_in_24qc_archive;