1 package body pay_us_validate_info as
2 /* $Header: pymwsval.pkb 120.1 2005/10/05 03:47:57 sackumar noship $ */
3
4 procedure validate_worksite_transmitter
5 (
6 p_business_group_id in number ,
7 p_context in varchar2,
8 p_err_code out nocopy number,
9 p_err_msg out nocopy varchar2
10 ) is
11
12 l_trans_flag varchar2(4);
13
14 begin
15 /* Check to see that only one transmitter GRE has been identified
16 for MWS Reporting */
17
18 l_code := 1;
19
20 select hoi.org_information1
21 into l_trans_flag
22 from HR_ORGANIZATION_INFORMATION hoi
23 where hoi.org_information_context = p_context
24 and hoi.org_information1 = 'Y'
25 and exists (select null
26 from hr_organization_information hoi1
27 where hoi1.organization_id = hoi.organization_id
28 and hoi1.org_information_context = 'CLASS'
29 and hoi1.org_information1 = 'HR_LEGAL'
30 and hoi1.org_information2 = 'Y')
31 and exists (select hou.organization_id
32 from HR_ORGANIZATION_UNITS hou
33 where hou.organization_id = hoi.organization_id
34 and hou.business_group_id = p_business_group_id);
35
36
37 p_err_code := 0;
38 p_err_msg := 'Only one transmitter has been defined for MWS Reporting';
39
40 return;
41
42 end validate_worksite_transmitter;
43
44 procedure validate_worksite
45 (
46 p_business_group_id in number ,
47 p_context in varchar2,
48 p_err_code out nocopy number,
49 p_err_msg out nocopy varchar2
50 ) is
51
52 l_organization_id number(15);
53
54 cursor get_worksite is
55 select distinct hoi.org_information2,
56 hoi.org_information3
57 from HR_ORGANIZATION_INFORMATION hoi
58 where hoi.org_information_context = p_context
59 and exists (select null
60 from hr_organization_information hoi1
61 where hoi1.organization_id = hoi.organization_id
62 and hoi1.org_information_context = 'CLASS'
63 and hoi1.org_information1 = 'HR_ESTAB'
64 and hoi1.org_information2 = 'Y')
65 and exists (select hou.organization_id
66 from HR_ORGANIZATION_UNITS hou
67 where hou.organization_id = hoi.organization_id
68 and hou.business_group_id = p_business_group_id);
69
70 begin
71
72 /* Check to see if a primary organization has been assigned for each
73 of the worksites to be reported . We will also check to see that
74 one and only one primary organization has been assigned to a
75 worksite */
76
77 l_code := 2;
78
79 /* Open the cursor to get the worksites */
80
81 open get_worksite;
82
83 /* Get each of the worksites that have been defined and check to see
84 if a primary organization has been assigned to each of the
85 worksites or not */
86
87 p_err_code := 0;
88 p_err_msg := ' Primary Org. assigned for every worksite';
89
90 loop
91
92
93 /* Get the SUI + RUN */
94 fetch get_worksite into l_sui_no,
95 l_run;
96
97 exit when get_worksite%NOTFOUND;
98
99 /* Check if a primary organization has been assigned for the
100 SUI + RUN combination */
101
102 select hoi.organization_id
103 into l_organization_id
104 from HR_ORGANIZATION_INFORMATION hoi
105 where hoi.org_information2 = l_sui_no
106 and hoi.org_information3 = l_run
107 and hoi.org_information1 = 'Y'
108 and hoi.org_information_context = p_context
109 and exists (select hou.organization_id
110 from HR_ORGANIZATION_UNITS hou
111 where hou.organization_id = hoi.organization_id
112 and hou.business_group_id = p_business_group_id);
113
114
115 end loop;
116
117 close get_worksite;
118
119 return;
120
121 end validate_worksite;
122
123 procedure validate
124 (
125 p_business_group_id in number ,
126 p_context in varchar2,
127 p_legislative_code in varchar2,
128 p_err_code out nocopy number,
129 p_err_msg out nocopy varchar2
130 ) is
131
132 l_context varchar2(240);
133
134 begin
135
136 if p_context = 'Multiple Worksite Reporting'
137 then
138
139 validate_worksite_transmitter(p_business_group_id,
140 p_context,
141 p_err_code,
142 p_err_msg);
143
144 elsif p_context = 'Worksite Filing'
145 then
146
147 validate_worksite(p_business_group_id,
148 p_context,
149 p_err_code,
150 p_err_msg);
151
152 end if;
153
154 return;
155
156 exception
157 when no_data_found then
158 if l_code = 1 then
159 p_err_code := 1;
160 p_err_msg := 'Transmitter GRE for MWS has not been defined ';
161 end if;
162
163 if l_code = 2 then
164 p_err_code := 2;
165 p_err_msg := 'Primary Org. not defined for SUI : ' ||
166 l_sui_no || ' and RUN : ' || l_run;
167 end if;
168 return;
169
170 when too_many_rows then
171 if l_code = 1 then
172 p_err_code := 1;
173 p_err_msg := 'There can be only one transmitter for MWS Reporting';
174 end if;
175
176 if l_code = 2 then
177 p_err_code := 2;
178 p_err_msg := 'Multiple Primary Org. for SUI : '
179 || l_sui_no || ' and RUN : ' || l_run;
180 end if;
181 return;
182
183 when others then
184 p_err_code := sqlcode;
185
186 if l_code = 1 then
187 p_err_msg := 'Getting transmitter :' ||substr(sqlerrm,1,80);
188 end if;
189
190 if l_code = 2 then
191 p_err_msg := 'Getting Primary Org. :' ||substr(sqlerrm,1,80);
192 end if;
193 return;
194 end validate;
195
196 end pay_us_validate_info;
[Home] [Help]