1 PACKAGE BODY pay_us_bee AS
2 /* $Header: pyusbee.pkb 115.12 2003/04/23 15:18:12 tclewis ship $ */
3 --
4 function line_check_supported
5 return number
6 is
7 begin
8 return (0);
9 end line_check_supported;
10
11 procedure validate_line(p_batch_line_id in number,
12 valid out NOCOPY number,
13 leg_message out NOCOPY varchar2,
14 line_changed out NOCOPY number) is
15
16 CURSOR csr_base_entry_exists (p_element_link_id number,
17 p_assignment_id number,
18 p_effective_date date) IS
19 SELECT 'Y'
20 FROM pay_element_entries_f pee
21 WHERE pee.element_link_id = p_element_link_id
22 AND pee.assignment_id = p_assignment_id
23 AND p_effective_date between pee.effective_start_date
24 and pee.effective_end_date;
25
26 g_line_record pay_batch_lines%ROWTYPE;
27 g_header_record pay_batch_headers%ROWTYPE;
28 l_element_name VARCHAR2(80);
29 l_base_element_name VARCHAR2(80);
30 l_base_element_type_id NUMBER;
31 l_element_link_id NUMBER;
32 p_entry_exists VARCHAR2(1);
33 begin
34 -- nb have passed core validation + thus make certain assumptions
35
36 valid := 0;
37 line_changed := 1;
38
39 select *
40 into g_line_record
41 from pay_batch_lines
42 where batch_line_id = p_batch_line_id;
43
44 select *
45 into g_header_record
46 from pay_batch_headers
47 where batch_id = g_line_record.batch_id;
48
49 --
50 -- Test if element is a Special Input
51 -- If so we check that the underlying base element has
52 -- an existant entry
53 --
54
55 --
56 -- Get element name
57 --
58 if (g_line_record.element_name is null) then
59
60 -- have to use element_type_id to get element_name
61
62 select elt.element_name
63 into l_element_name
64 from pay_element_types_f elt
65 where elt.element_type_id = g_line_record.element_type_id
66 and (elt.business_group_id +0 = g_header_record.business_group_id
67 or (elt.business_group_id is null
68 and elt.legislation_code = 'US'))
69 and g_line_record.effective_date between elt.effective_start_date
70 and elt.effective_end_date;
71
72 else
73
74 l_element_name := g_line_record.element_name;
75
76 end if;
77 --
78 --
79 -- Test if element is a Special Input
80 --
81 IF l_element_name like '% Special Inputs' THEN
82
83 --
84 -- If so we check that the underlying base element has
85 -- an existant entry
86 --
87 p_entry_exists := 'N';
88 l_base_element_name := substr(l_element_name, 1, instr(l_element_name, ' Special Inputs') -1);
89
90 begin
91
92 select element_type_id
93 into l_base_element_type_id
94 from pay_element_types_f pet
95 where pet.element_name = l_base_element_name
96 and pet.processing_type = 'R'
97 and (pet.business_group_id = g_header_record.business_group_id
98 or (pet.business_group_id IS NULL
99 and pet.legislation_code = 'US'))
100 and g_line_record.effective_date between pet.effective_start_date
101 and pet.effective_end_date;
102
103 l_element_link_id := hr_entry_api.get_link(g_line_record.assignment_id,
104 l_base_element_type_id,
105 g_line_record.effective_date);
106
107 if l_element_link_id is not null then
108
109 OPEN csr_base_entry_exists(l_element_link_id, g_line_record.assignment_id,
110 g_line_record.effective_date);
111 FETCH csr_base_entry_exists INTO p_entry_exists;
112 CLOSE csr_base_entry_exists;
113
114 end if;
115
116 exception
117 when others then
118 p_entry_exists := 'N';
119 end;
120
121 /* bug 2712661 Will return success if this condition is met to be consistent
122 with EE form */
123
124 /* IF p_entry_exists <> 'Y' THEN
125 leg_message := 'Special Inputs Base Element but does not exist.';
126 valid := 1;
127 END IF;
128 */
129
130 END IF;
131
132 end validate_line;
133
134 end pay_us_bee;