1 PACKAGE BODY pay_ca_bee AS
2 /* $Header: pycabee.pkb 115.5 2003/04/23 16:45:18 tclewis ship $ */
3 /*
4
5 Name : pay_us_bee
6 Description : Canandian Legislative Hook for BEE.
7 Author : A.Logue
8 Date Created : 05-Jul-99
9 Contents : line_check_supported, validate_line.
10
11 Change List
12 -----------
13 Date Name Vers Bug No Description
14 +-----------+--------------+--------+---------+-----------------------+
15 23-APR-2003 T.Lewis 115.3 2712265 Procedure will now return
16 success (ie valid) when
17 checking for base element
18 if BEE is processing the
19 a Special Inputs element
20 and Base element does not
21 exist. This is to make it
22 consistent with EE form.
23 07-FEB-2002 R.Sirigiri 115.2 GSCC Compliance
24 20-MAR-2001 A.Logue 115.2 Correct derivation of base
25 element name and assume
26 legislation_code is 'US'!
27 23-JUL-1999 A.Logue 115.1 Added commit.
28 05-JUL-1999 A.Logue 115.0 First Created.
29 No Header validation.
30 Line Validation: checks
31 that the base element
32 entry exists for a
33 Special Input Entry.
34 +-----------+--------------+--------+---------+-----------------------+
35 */
36
37 function line_check_supported
38 return number
39 is
40 begin
41 return (0);
42 end line_check_supported;
43
44 procedure validate_line(batch_line_id in number,
45 valid out nocopy number,
46 leg_message out nocopy varchar2,
47 line_changed out nocopy number) is
48
49 CURSOR csr_base_entry_exists (p_element_link_id number, p_assignment_id number,
50 p_effective_date date) IS
51 SELECT 'Y'
52 FROM pay_element_entries_f pee
53 WHERE pee.element_link_id = p_element_link_id
54 AND pee.assignment_id = p_assignment_id
55 AND p_effective_date between pee.effective_start_date
56 and pee.effective_end_date;
57
58 g_line_record pay_batch_lines%ROWTYPE;
59 g_header_record pay_batch_headers%ROWTYPE;
60 l_batch_line_id NUMBER;
61 l_element_name VARCHAR2(80);
62 l_base_element_name VARCHAR2(80);
63 l_base_element_type_id NUMBER;
64 l_element_link_id NUMBER;
65 p_entry_exists VARCHAR2(1);
66 begin
67 -- nb have passed core validation + thus make certain assumptions
68
69 valid := 0;
70 line_changed := 1;
71 l_batch_line_id := batch_line_id;
72
73 select *
74 into g_line_record
75 from pay_batch_lines
76 where batch_line_id = l_batch_line_id;
77
78 select *
79 into g_header_record
80 from pay_batch_headers
81 where batch_id = g_line_record.batch_id;
82
83 --
84 -- Test if element is a Special Input
85 -- If so we check that the underlying base element has
86 -- an existant entry
87 --
88
89 --
90 -- Get element name
91 --
92 if (g_line_record.element_name is null) then
93
94 -- have to use element_type_id to get element_name
95
96 select elt.element_name
97 into l_element_name
98 from pay_element_types_f elt
99 where elt.element_type_id = g_line_record.element_type_id
100 and (elt.business_group_id +0 = g_header_record.business_group_id
101 or (elt.business_group_id is null
102 and elt.legislation_code = 'CA'))
103 and g_line_record.effective_date between elt.effective_start_date
104 and elt.effective_end_date;
105
106 else
107
108 l_element_name := g_line_record.element_name;
109
110 end if;
111 --
112 --
113 -- Test if element is a Special Input
114 --
115 IF l_element_name like '% Special Inputs' THEN
116
117 --
118 -- If so we check that the underlying base element has
119 -- an existant entry
120 --
121 p_entry_exists := 'N';
122 l_base_element_name := substr(l_element_name, 1, instr(l_element_name, ' Special Inputs') -1);
123
124 begin
125
126 select element_type_id
127 into l_base_element_type_id
128 from pay_element_types_f pet
129 where pet.element_name = l_base_element_name
130 and pet.processing_type = 'R'
131 and (pet.business_group_id = g_header_record.business_group_id
132 or (pet.business_group_id IS NULL
133 and pet.legislation_code = 'CA'))
134 and g_line_record.effective_date between pet.effective_start_date
135 and pet.effective_end_date;
136
137 l_element_link_id := hr_entry_api.get_link(g_line_record.assignment_id,
138 l_base_element_type_id,
139 g_line_record.effective_date);
140
141 if l_element_link_id is not null then
142
143 OPEN csr_base_entry_exists(l_element_link_id, g_line_record.assignment_id,
144 g_line_record.effective_date);
145 FETCH csr_base_entry_exists INTO p_entry_exists;
146 CLOSE csr_base_entry_exists;
147
148 end if;
149
150 exception
151 when others then
152 p_entry_exists := 'N';
153 end;
154
155 /* bug 2712661 Will return success if this condition is met to be consistent
156 with EE form */
157
158 /* IF p_entry_exists <> 'Y' THEN
159 leg_message := 'Special Inputs Base Element but does not exist.';
160 valid := 1;
161 END IF;
162 */
163
164 END IF;
165
166 end validate_line;
167
168 end pay_ca_bee;
[Home] [Help]