1 PACKAGE BODY hr_de_validate_pkg AS
2 /* $Header: pedevald.pkb 120.1 2007/04/05 09:28:58 spendhar noship $ */
3
4
5 PROCEDURE create_element_entry_validate
6 (p_effective_date IN DATE
7 ,p_assignment_id IN NUMBER
8 ,p_entry_information_category IN VARCHAR2
9 ,p_entry_information1 IN VARCHAR2) IS
10
11 l_table PAY_ELEMENT_ENTRIES_F.ENTRY_INFORMATION9%TYPE;
12
13 CURSOR csr_get_table IS
14 SELECT entry_information9
15 FROM pay_element_entries_f entry
16 WHERE entry.assignment_id = p_assignment_id
17 AND p_effective_date between entry.effective_start_date and entry.effective_end_date
18 AND entry.element_type_id = (SELECT element_type_id
19 FROM pay_element_types_f ele
20 WHERE p_effective_date between ele.effective_start_date and ele.effective_end_date
21 AND ele.element_name = 'Tax Information'
22 AND ele.legislation_code = 'DE')
23 AND entry.entry_information_category = 'DE_TAX INFORMATION';
24
25 BEGIN
26
27 /* Added for GSI Bug 5472781 */
28 IF hr_utility.chk_product_install('Oracle Human Resources', 'DE') THEN
29
30 OPEN csr_get_table;
31 FETCH csr_get_table INTO l_table;
32 CLOSE csr_get_table;
33
34 If P_ENTRY_INFORMATION_CATEGORY ='DE_SOCIAL INSURANCE' then
35 If P_ENTRY_INFORMATION1 = '0000' and l_table = 'DE_TAX_TABLE_A' then
36 hr_utility.set_message(800, 'HR_78912_DE_NO_PENSION_CONT');
37 hr_utility.raise_error;
38 End If;
39 End If;
40
41 END IF;
42
43 END create_element_entry_validate;
44
45
46 PROCEDURE update_element_entry_validate
47 (p_effective_date IN DATE
48 ,p_element_entry_id IN NUMBER
49 ,p_entry_information_category IN VARCHAR2
50 ,p_entry_information1 IN VARCHAR2) IS
51
52 l_table PAY_ELEMENT_ENTRIES_F.ENTRY_INFORMATION9%TYPE;
53 l_assignment_id number;
54
55 CURSOR csr_get_asg_id IS
56 SELECT assignment_id
57 FROM pay_element_entries_f ENTRY
58 WHERE p_effective_date between entry.effective_start_date and entry.effective_end_date
59 AND entry.element_entry_id = p_element_entry_id;
60
61 CURSOR csr_get_table (l_assignment_id number) IS
62 SELECT entry_information9
63 FROM pay_element_entries_f entry
64 WHERE entry.assignment_id = l_assignment_id
65 AND p_effective_date between entry.effective_start_date and entry.effective_end_date
66 AND entry.element_type_id = (SELECT element_type_id
67 FROM pay_element_types_f ele
68 WHERE p_effective_date between ele.effective_start_date and ele.effective_end_date
69 AND ele.element_name = 'Tax Information'
70 AND ele.legislation_code = 'DE')
71 AND entry.entry_information_category = 'DE_TAX INFORMATION';
72
73 BEGIN
74
75 /* Added for GSI Bug 5472781 */
76 IF hr_utility.chk_product_install('Oracle Human Resources', 'DE') THEN
77
78 OPEN csr_get_asg_id;
79 FETCH csr_get_asg_id INTO l_assignment_id;
80 CLOSE csr_get_asg_id;
81
82 OPEN csr_get_table(l_assignment_id);
83 FETCH csr_get_table INTO l_table;
84 CLOSE csr_get_table;
85
86 If P_ENTRY_INFORMATION_CATEGORY ='DE_SOCIAL INSURANCE' then
87 If P_ENTRY_INFORMATION1 = '0000' and l_table = 'DE_TAX_TABLE_A' then
88 hr_utility.set_message(800, 'HR_78912_DE_NO_PENSION_CONT');
89 hr_utility.raise_error;
90 End If;
91 End If;
92
93 END IF;
94
95 END update_element_entry_validate;
96
97 END hr_de_validate_pkg;