[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_ITAX_PKG
Source
1 package body PAY_JP_ITAX_PKG as
2 /* $Header: pyjpitax.pkb 120.1 2006/05/30 09:12:07 sgottipa noship $ */
3 c_action_info_category_person CONSTANT pay_action_information.action_information_category%type := 'JP_ITAX_PERSON';
4 c_action_info_category_arch CONSTANT pay_action_information.action_information_category%type := 'JP_ITAX_ARCH';
5 c_action_info_category_tax CONSTANT pay_action_information.action_information_category%type := 'JP_ITAX_TAX';
6 c_action_info_category_other CONSTANT pay_action_information.action_information_category%type := 'JP_ITAX_OTHER';
7 c_action_info_category_other2 CONSTANT pay_action_information.action_information_category%type := 'JP_ITAX_OTHER2';
8 c_action_context_type CONSTANT pay_action_information.action_context_type%type := 'AAP';
9 c_first_object_version_number CONSTANT pay_action_information.object_version_number%type := 1;
10 c_enabled CONSTANT VARCHAR2(80) := 'Enabled';
11 c_disabled CONSTANT VARCHAR2(80) := 'Disabled';
12
13 FUNCTION NEXT_ACTION_INFORMATION_ID
14 RETURN number
15 IS
16 l_action_information_id number;
17 BEGIN
18 SELECT pay_action_information_s.nextval
19 INTO l_action_information_id
20 FROM dual;
21
22 RETURN l_action_information_id;
23 END NEXT_ACTION_INFORMATION_ID;
24
25 PROCEDURE CREATE_PERSON(
26 p_assignment_action_id in number,
27 p_effective_date in date,
28 p_assignment_id in number,
29 p_value in value_rec
30 )
31 IS
32 l_action_information_id number;
33 BEGIN
34 l_action_information_id := NEXT_ACTION_INFORMATION_ID;
35
36 INSERT INTO PAY_JP_ITAX_PERSON_V
37 (
38 ACTION_INFORMATION_ID,
39 ACTION_CONTEXT_ID,
40 ACTION_CONTEXT_TYPE,
41 EFFECTIVE_DATE,
42 ASSIGNMENT_ID,
43 OBJECT_VERSION_NUMBER,
44 ACTION_INFORMATION_CATEGORY,
45 EMPLOYEE_NUMBER,
46 LAST_NAME_KANA,
47 FIRST_NAME_KANA,
48 LAST_NAME_KANJI,
49 FIRST_NAME_KANJI,
50 SEX,
51 ADDRESS_ID,
52 ADDRESS_KANA,
53 ADDRESS_KANJI,
54 COUNTRY,
55 JP_DATE_START,
56 LEAVING_REASON,
57 DATE_OF_BIRTH,
58 DATE_OF_BIRTH_MEIJI,
59 DATE_OF_BIRTH_TAISHOU,
60 DATE_OF_BIRTH_SHOUWA,
61 DATE_OF_BIRTH_HEISEI,
62 DATE_OF_BIRTH_YEAR,
63 DATE_OF_BIRTH_MONTH,
64 DATE_OF_BIRTH_DAY,
65 EMPLOYMENT_DATE_YEAR,
66 EMPLOYMENT_DATE_MONTH,
67 EMPLOYMENT_DATE_DAY,
68 ITAX_ORGANIZATION_ID,
69 ACTUAL_TERMINATION_DATE,
70 ORG_NAME,
71 PAYROLL_NAME,
72 DISTRICT_CODE,
73 OPEN_DATE,
74 CLOSE_DATE
75 )
79 p_assignment_action_id,
76 VALUES
77 (
78 l_action_information_id,
80 c_action_context_type,
81 p_effective_date,
82 p_assignment_id,
83 c_first_object_version_number,
84 c_action_info_category_person,
85 p_value.p_action_information1,
86 p_value.p_action_information2,
87 p_value.p_action_information3,
88 p_value.p_action_information4,
89 p_value.p_action_information5,
90 p_value.p_action_information6,
91 p_value.p_action_information7,
92 p_value.p_action_information8,
93 p_value.p_action_information9,
94 p_value.p_action_information10,
95 p_value.p_action_information11,
96 p_value.p_action_information12,
97 p_value.p_action_information13,
98 p_value.p_action_information14,
99 p_value.p_action_information15,
100 p_value.p_action_information16,
101 p_value.p_action_information17,
102 p_value.p_action_information18,
103 p_value.p_action_information19,
104 p_value.p_action_information20,
105 p_value.p_action_information21,
106 p_value.p_action_information22,
107 p_value.p_action_information23,
108 p_value.p_action_information24,
109 p_value.p_action_information25,
110 p_value.p_action_information26,
111 p_value.p_action_information27,
112 p_value.p_action_information28,
113 p_value.p_action_information29,
114 p_value.p_action_information30
115 );
116 END CREATE_PERSON;
117
118 PROCEDURE CREATE_ARCH(
119 p_assignment_action_id in number,
120 p_effective_date in date,
121 p_assignment_id in number,
122 p_value in value_rec
123 )
124 IS
125 l_action_information_id number;
126 BEGIN
127 l_action_information_id := NEXT_ACTION_INFORMATION_ID;
128
129 INSERT INTO PAY_JP_ITAX_ARCH_V
130 (
131 ACTION_INFORMATION_ID,
132 ACTION_CONTEXT_ID,
133 ACTION_CONTEXT_TYPE,
134 EFFECTIVE_DATE,
135 ASSIGNMENT_ID,
136 OBJECT_VERSION_NUMBER,
137 ACTION_INFORMATION_CATEGORY,
138 PERSON_ID,
139 ASSIGNMENT_ACTION_ID,
140 PAYROLL_ID,
141 REFERENCE_NUMBER,
142 REFERENCE_NUMBER1,
143 REFERENCE_NUMBER2,
144 ACTION_SEQUENCE,
145 EMPLOYER_ADDRESS,
146 EMPLOYER_NAME,
147 EMPLOYER_TELEPHONE_NUMBER,
148 TAX_OFFICE_NUMBER,
149 DATE_EARNED,
150 EMPLOYMENT_CATEGORY,
151 ITAX_CATEGORY,
152 ITAX_YEA_CATEGORY,
153 DESCRIPTION1,
154 DESCRIPTION2,
155 DESCRIPTION3,
156 DESCRIPTION4,
157 DESCRIPTION5,
158 FILE_DESCRIPTION1,
159 FILE_DESCRIPTION2,
160 FILE_DESCRIPTION3,
161 FILE_DESCRIPTION4,
162 FILE_DESCRIPTION5,
163 DESCRIPTION_KANJI,
164 DESCRIPTION_KANA,
165 FOR_FILE_DESCRIPTION_KANJI,
166 FOR_FILE_DESCRIPTION_KANA,
167 SUBMISSION_REQUIRED_FLAG
168 )
169 VALUES
170 (
171 l_action_information_id,
172 p_assignment_action_id,
173 c_action_context_type,
174 p_effective_date,
175 p_assignment_id,
176 c_first_object_version_number,
177 c_action_info_category_arch,
178 p_value.p_action_information1,
179 p_value.p_action_information2,
180 p_value.p_action_information3,
181 p_value.p_action_information4,
182 p_value.p_action_information5,
183 p_value.p_action_information6,
184 p_value.p_action_information7,
185 p_value.p_action_information8,
186 p_value.p_action_information9,
187 p_value.p_action_information10,
188 p_value.p_action_information11,
189 p_value.p_action_information12,
190 p_value.p_action_information13,
191 p_value.p_action_information14,
192 p_value.p_action_information15,
193 p_value.p_action_information16,
194 p_value.p_action_information17,
195 p_value.p_action_information18,
196 p_value.p_action_information19,
197 p_value.p_action_information20,
198 p_value.p_action_information21,
199 p_value.p_action_information22,
200 p_value.p_action_information23,
201 p_value.p_action_information24,
202 p_value.p_action_information25,
203 p_value.p_action_information26,
204 p_value.p_action_information27,
205 p_value.p_action_information28,
206 p_value.p_action_information29,
207 p_value.p_action_information30
208 );
209 END CREATE_ARCH;
210
211 PROCEDURE CREATE_TAX(
212 p_assignment_action_id in number,
213 p_effective_date in date,
214 p_assignment_id in number,
215 p_value in value_rec
216 )
217 IS
218 l_action_information_id number;
219 BEGIN
220 l_action_information_id := NEXT_ACTION_INFORMATION_ID;
221
222 INSERT INTO PAY_JP_ITAX_TAX_V
223 (
224 ACTION_INFORMATION_ID,
225 ACTION_CONTEXT_ID,
226 ACTION_CONTEXT_TYPE,
227 EFFECTIVE_DATE,
228 ASSIGNMENT_ID,
229 OBJECT_VERSION_NUMBER,
230 ACTION_INFORMATION_CATEGORY,
231 TAXABLE_INCOME,
232 NET_TAXABLE_INCOME,
233 TOTAL_INCOME_EXEMPT,
234 WITHHOLDING_ITAX,
235 WITHHOLDING_ITAX2,
236 SPOUSE_SPECIAL_EXEMPT,
237 SOCIAL_INSURANCE_PREMIUM,
238 MUTUAL_AID_PREMIUM,
239 LIFE_INSURANCE_PREMIUM_EXEMPT,
240 DAMAGE_INSURANCE_PREMIUM_EXEM,
241 HOUSING_TAX_REDUCTION,
242 ITAX_ADJUSTMENT2,
243 SPOUSE_NET_TAXABLE_INCOME,
244 PRIVATE_PENSION_PREMIUM,
245 LONG_DAMAGE_INSURANCE_PREMIUM,
246 DISASTER_TAX_REDUCTION,
247 PREV_JOB_EMPLOYER_ADD_KANA,
248 PREV_JOB_EMPLOYER_ADD_KANJI,
249 PREV_JOB_EMPLOYER_NAME_KANA,
250 PREV_JOB_EMPLOYER_NAME_KANJI,
254 PREV_JOB_SI_PREM,
251 PREV_JOB_FOREIGN_ADDRESS,
252 PREV_JOB_TAXABLE_INCOME,
253 PREV_JOB_ITAX,
255 PREV_JOB_TERMINATION_YEAR,
256 PREV_JOB_TERMINATION_MONTH,
257 PREV_JOB_TERMINATION_DAY,
258 HOUSING_RESIDENCE_YEAR,
259 HOUSING_RESIDENCE_MONTH,
260 HOUSING_RESIDENCE_DAY
261 )
262 VALUES
263 (
264 l_action_information_id,
265 p_assignment_action_id,
266 c_action_context_type,
267 p_effective_date,
268 p_assignment_id,
269 c_first_object_version_number,
270 c_action_info_category_tax,
271 p_value.p_action_information1,
272 p_value.p_action_information2,
273 p_value.p_action_information3,
274 p_value.p_action_information4,
275 p_value.p_action_information5,
276 p_value.p_action_information6,
277 p_value.p_action_information7,
278 p_value.p_action_information8,
279 p_value.p_action_information9,
280 p_value.p_action_information10,
281 p_value.p_action_information11,
282 p_value.p_action_information12,
283 p_value.p_action_information13,
284 p_value.p_action_information14,
285 p_value.p_action_information15,
286 p_value.p_action_information16,
287 p_value.p_action_information17,
288 p_value.p_action_information18,
289 p_value.p_action_information19,
290 p_value.p_action_information20,
291 p_value.p_action_information21,
292 p_value.p_action_information22,
293 p_value.p_action_information23,
294 p_value.p_action_information24,
295 p_value.p_action_information25,
296 p_value.p_action_information26,
297 p_value.p_action_information27,
298 p_value.p_action_information28,
299 p_value.p_action_information29,
300 p_value.p_action_information30
301 );
302 END CREATE_TAX;
303
304 PROCEDURE CREATE_OTHER(
305 p_assignment_action_id in number,
306 p_effective_date in date,
307 p_assignment_id in number,
308 p_value in value_rec
309 )
310 IS
311 l_action_information_id number;
312 BEGIN
313 l_action_information_id := NEXT_ACTION_INFORMATION_ID;
314
315 INSERT INTO PAY_JP_ITAX_OTHER_V
316 (
317 ACTION_INFORMATION_ID,
318 ACTION_CONTEXT_ID,
319 ACTION_CONTEXT_TYPE,
320 EFFECTIVE_DATE,
321 ASSIGNMENT_ID,
322 OBJECT_VERSION_NUMBER,
323 ACTION_INFORMATION_CATEGORY,
324 DEPENDENT_SPOUSE_EXISTS_KOU,
325 DEPENDENT_SPOUSE_NO_EXIST_KOU,
326 DEPENDENT_SPOUSE_EXISTS_OTSU,
327 DEPENDENT_SPOUSE_NO_EXIST_OTSU,
328 AGED_SPOUSE_EXISTS,
329 NUM_SPECIFIEDS_KOU,
330 NUM_SPECIFIEDS_OTSU,
331 NUM_AGED_PARENTS_PARTIAL,
332 NUM_AGEDS_KOU,
333 NUM_AGEDS_OTSU,
334 NUM_DEPENDENTS_KOU,
335 NUM_DEPENDENTS_OTSU,
336 NUM_SPECIAL_DISABLEDS_PARTIAL,
337 NUM_SPECIAL_DISABLEDS,
338 NUM_DISABLEDS,
339 HUSBAND_EXISTS,
340 MINOR,
341 OTSU,
342 SPECIAL_DISABLED,
343 DISABLED,
344 AGED,
345 WIDOW,
346 SPECIAL_WIDOW,
347 WIDOWER,
348 WORKING_STUDENT,
349 DECEASED_TERMINATION,
350 DISASTERED,
351 FOREIGNER,
352 EMPLOYED,
353 UNEMPLOYED
354 )
355 VALUES
356 (
357 l_action_information_id,
358 p_assignment_action_id,
359 c_action_context_type,
360 p_effective_date,
361 p_assignment_id,
362 c_first_object_version_number,
363 c_action_info_category_other,
364 p_value.p_action_information1,
365 p_value.p_action_information2,
366 p_value.p_action_information3,
367 p_value.p_action_information4,
368 p_value.p_action_information5,
369 p_value.p_action_information6,
370 p_value.p_action_information7,
371 p_value.p_action_information8,
372 p_value.p_action_information9,
373 p_value.p_action_information10,
374 p_value.p_action_information11,
375 p_value.p_action_information12,
376 p_value.p_action_information13,
377 p_value.p_action_information14,
378 p_value.p_action_information15,
379 p_value.p_action_information16,
380 p_value.p_action_information17,
381 p_value.p_action_information18,
382 p_value.p_action_information19,
383 p_value.p_action_information20,
384 p_value.p_action_information21,
385 p_value.p_action_information22,
386 p_value.p_action_information23,
387 p_value.p_action_information24,
388 p_value.p_action_information25,
389 p_value.p_action_information26,
390 p_value.p_action_information27,
391 p_value.p_action_information28,
392 p_value.p_action_information29,
393 p_value.p_action_information30
394 );
395 END CREATE_OTHER;
396
397 PROCEDURE CREATE_OTHER2(
398 p_assignment_action_id in number,
399 p_effective_date in date,
400 p_assignment_id in number,
401 p_value in value_rec
402 )
403 IS
404 l_action_information_id number;
405 BEGIN
406 l_action_information_id := NEXT_ACTION_INFORMATION_ID;
407
408 INSERT INTO PAY_JP_ITAX_OTHER_V2
409 (
410 ACTION_INFORMATION_ID,
411 ACTION_CONTEXT_ID,
412 ACTION_CONTEXT_TYPE,
413 EFFECTIVE_DATE,
414 ASSIGNMENT_ID,
415 OBJECT_VERSION_NUMBER,
416 ACTION_INFORMATION_CATEGORY,
417 BUSINESS_GROUP_ID,
418 PROCESS_FLAG,
419 NEW_DESCRIPTION1,
420 NEW_DESCRIPTION2,
421 NEW_DESCRIPTION3,
422 NEW_DESCRIPTION4,
423 NEW_DESCRIPTION5,
424 NEW_FILE_DESCRIPTION1,
425 NEW_FILE_DESCRIPTION2,
426 NEW_FILE_DESCRIPTION3,
427 NEW_FILE_DESCRIPTION4,
428 NEW_FILE_DESCRIPTION5,
429 DESC_OVERRIDE_FLAG,
430 FILE_DESC_OVERRIDE_FLAG,
431 DESCRIPTION_KANJI_1,
432 DESCRIPTION_KANJI_2,
433 DESCRIPTION_KANA_1,
434 DESCRIPTION_KANA_2,
435 FILE_DESCRIPTION_KANJI_1,
436 FILE_DESCRIPTION_KANJI_2,
437 FILE_DESCRIPTION_KANA_1,
438 FILE_DESCRIPTION_KANA_2,
439 DESC_LINE1_KANJI,
440 DESC_LINE1_KANA,
441 ACTION_INFORMATION25,
442 ACTION_INFORMATION26,
443 ACTION_INFORMATION27,
444 ACTION_INFORMATION28,
445 ACTION_INFORMATION29,
446 ACTION_INFORMATION30 )
447 VALUES
448 (
449 l_action_information_id,
450 p_assignment_action_id,
451 c_action_context_type,
452 p_effective_date,
453 p_assignment_id,
454 c_first_object_version_number,
455 c_action_info_category_other2,
456 p_value.p_action_information1,
457 p_value.p_action_information2,
458 p_value.p_action_information3,
459 p_value.p_action_information4,
460 p_value.p_action_information5,
461 p_value.p_action_information6,
462 p_value.p_action_information7,
463 p_value.p_action_information8,
464 p_value.p_action_information9,
465 p_value.p_action_information10,
466 p_value.p_action_information11,
467 p_value.p_action_information12,
468 p_value.p_action_information13,
469 p_value.p_action_information14,
470 p_value.p_action_information15,
471 p_value.p_action_information16,
472 p_value.p_action_information17,
473 p_value.p_action_information18,
474 p_value.p_action_information19,
475 p_value.p_action_information20,
476 p_value.p_action_information21,
477 p_value.p_action_information22,
478 p_value.p_action_information23,
479 p_value.p_action_information24,
480 p_value.p_action_information25,
481 p_value.p_action_information26,
482 p_value.p_action_information27,
483 p_value.p_action_information28,
484 p_value.p_action_information29,
485 p_value.p_action_information30
486 );
487 END CREATE_OTHER2;
488
489 PROCEDURE DELETE_ITAX(
490 p_assignment_id IN NUMBER,
491 p_effective_date IN DATE,
492 p_swot_id IN NUMBER,
493 p_tax_type IN VARCHAR2
494 )
495 IS
496 BEGIN
497 -- Fine Tuned the query to fix Bug# 5202835.
498 DELETE PAY_ACTION_INFORMATION PAI
499 WHERE PAI.ACTION_INFORMATION_CATEGORY in ('JP_ITAX_PERSON','JP_ITAX_ARCH','JP_ITAX_TAX','JP_ITAX_OTHER','JP_ITAX_OTHER2')
500 AND PAI.ASSIGNMENT_ID = p_assignment_id
501 AND EXISTS ( SELECT NULL
502 FROM PAY_ACTION_INFORMATION PERSON,
503 PAY_ACTION_INFORMATION ARCH,
504 PAY_ACTION_INFORMATION TAX,
505 PAY_ACTION_INFORMATION OTHER,
506 PAY_ACTION_INFORMATION OTHER2
507 WHERE OTHER2.ACTION_INFORMATION_CATEGORY = 'JP_ITAX_OTHER2'
508 AND OTHER2.ACTION_CONTEXT_TYPE = 'AAP'
509 AND OTHER2.ASSIGNMENT_ID = p_assignment_id
510 AND to_char(OTHER2.EFFECTIVE_DATE,'YYYY') = to_char(p_effective_date,'YYYY')
511 AND OTHER2.EFFECTIVE_DATE <= p_effective_date
512 AND OTHER2.EFFECTIVE_DATE = PAI.EFFECTIVE_DATE
513 AND PERSON.ACTION_INFORMATION_CATEGORY = 'JP_ITAX_PERSON'
514 AND PERSON.ACTION_CONTEXT_TYPE = 'AAP'
515 AND PERSON.action_information24 = to_char(p_swot_id)
516 AND ARCH.ACTION_INFORMATION_CATEGORY = 'JP_ITAX_ARCH'
517 AND ARCH.ACTION_CONTEXT_TYPE = 'AAP'
518 AND ARCH.action_information14 = p_tax_type
519 AND PERSON.ACTION_CONTEXT_ID = ARCH.ACTION_CONTEXT_ID
520 AND PERSON.EFFECTIVE_DATE = ARCH.EFFECTIVE_DATE
521 AND TAX.ACTION_INFORMATION_CATEGORY = 'JP_ITAX_TAX'
522 AND TAX.ACTION_CONTEXT_TYPE = 'AAP'
523 AND PERSON.ACTION_CONTEXT_ID = TAX.ACTION_CONTEXT_ID
524 AND PERSON.EFFECTIVE_DATE = TAX.EFFECTIVE_DATE
525 AND OTHER.ACTION_INFORMATION_CATEGORY = 'JP_ITAX_OTHER'
526 AND OTHER.ACTION_CONTEXT_TYPE = 'AAP'
527 AND PERSON.ACTION_CONTEXT_ID = OTHER.ACTION_CONTEXT_ID
528 AND PERSON.EFFECTIVE_DATE = OTHER.EFFECTIVE_DATE
529 AND PERSON.ACTION_CONTEXT_ID = OTHER2.ACTION_CONTEXT_ID
530 AND PERSON.EFFECTIVE_DATE = OTHER2.EFFECTIVE_DATE
531 );
532
533 END DELETE_ITAX;
534
535 END;