[Home] [Help]
PACKAGE BODY: APPS.OTA_UGT_SHD
Source
1 Package Body ota_ugt_shd as
2 /* $Header: otugtrhi.pkb 120.2 2008/03/28 06:47:57 pekasi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_ugt_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'OTA_USER_GROUPS_TL_UK1') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 Else
27 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30 fnd_message.raise_error;
31 End If;
32 --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39 (p_user_group_id in number
40 ,p_language in varchar2
41 ) Return Boolean Is
42 --
43 --
44 -- Cursor selects the 'current' row from the HR Schema
45 --
46 Cursor C_Sel1 is
47 select
48 user_group_id
49 ,language
50 ,user_group_name
51 ,description
52 ,source_lang
53 from ota_user_groups_tl
54 where user_group_id = p_user_group_id
55 and language = p_language;
56 --
57 l_fct_ret boolean;
58 --
59 Begin
60 --
61 If (p_user_group_id is null or
62 p_language is null
63 ) Then
64 --
65 -- One of the primary key arguments is null therefore we must
66 -- set the returning function value to false
67 --
68 l_fct_ret := false;
69 Else
70 If (p_user_group_id = ota_ugt_shd.g_old_rec.user_group_id and
71 p_language = ota_ugt_shd.g_old_rec.language ) Then
72 --
73 -- The g_old_rec is current therefore we must
74 -- set the returning function to true
75 --
76 l_fct_ret := true;
77 Else
78 --
79 -- Select the current row into g_old_rec
80 --
81 Open C_Sel1;
82 Fetch C_Sel1 Into ota_ugt_shd.g_old_rec;
83 If C_Sel1%notfound Then
84 Close C_Sel1;
85 --
86 -- The primary key is invalid therefore we must error
87 --
88 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
89 fnd_message.raise_error;
90 End If;
91 Close C_Sel1;
92 --
93 l_fct_ret := true;
94 End If;
95 End If;
96 Return (l_fct_ret);
97 --
98 End api_updating;
99 --
100 -- ----------------------------------------------------------------------------
101 -- |---------------------------------< lck >----------------------------------|
102 -- ----------------------------------------------------------------------------
103 Procedure lck
104 (p_user_group_id in number
105 ,p_language in varchar2
106 ) is
107 --
108 -- Cursor selects the 'current' row from the HR Schema
109 --
110 Cursor C_Sel1 is
111 select
112 user_group_id
113 ,language
114 ,user_group_name
115 ,description
116 ,source_lang
117 from ota_user_groups_tl
118 where user_group_id = p_user_group_id
119 and language = p_language
120 for update nowait;
121 --
122 l_proc varchar2(72) := g_package||'lck';
123 --
124 Begin
125 hr_utility.set_location('Entering:'||l_proc, 5);
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'user_group_id'
130 ,p_argument_value => p_user_group_id
131 );
132 hr_utility.set_location(l_proc,6);
133 hr_api.mandatory_arg_error
134 (p_api_name => l_proc
135 ,p_argument => 'LANGUAGE'
136 ,p_argument_value => p_language
137 );
138 --
139 Open C_Sel1;
140 Fetch C_Sel1 Into ota_ugt_shd.g_old_rec;
141 If C_Sel1%notfound then
142 Close C_Sel1;
143 --
144 -- The primary key is invalid therefore we must error
145 --
146 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
147 fnd_message.raise_error;
148 End If;
149 Close C_Sel1;
150 --
151 --
152 hr_utility.set_location(' Leaving:'||l_proc, 10);
153 --
154 -- We need to trap the ORA LOCK exception
155 --
156 Exception
157 When HR_Api.Object_Locked then
158 --
159 -- The object is locked therefore we need to supply a meaningful
160 -- error message.
161 --
162 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
163 fnd_message.set_token('TABLE_NAME', 'ota_user_groups_tl');
164 fnd_message.raise_error;
165 End lck;
166 --
167 -- ----------------------------------------------------------------------------
168 -- |----------------------------< add_language >------------------------------|
169 -- ----------------------------------------------------------------------------
170 --
171 -- Executed AOL's tltblgen(UNIX) program to generate the
172 -- ADD_LANGUAGE procedure.
173 --
174 -- ----------------------------------------------------------------------------
175 procedure ADD_LANGUAGE
176 is
177 begin
178 delete from ota_user_groups_tl T
179 where not exists
180 (select NULL
181 from OTA_USER_GROUPS_B B
182 where B.user_group_id = T.user_group_id
183 );
184
185 update ota_user_groups_tl T set (
186 USER_GROUP_NAME,
187 DESCRIPTION
188 ) = (select
189 B.USER_GROUP_NAME,
190 B.DESCRIPTION
191 from ota_user_groups_tl B
192 where B.user_group_id = T.user_group_id
193 and B.LANGUAGE = T.SOURCE_LANG)
194 where (
195 T.user_group_id,
196 T.LANGUAGE
197 ) in (select
198 SUBT.user_group_id,
199 SUBT.LANGUAGE
200 from ota_user_groups_tl SUBB, ota_user_groups_tl SUBT
201 where SUBB.user_group_id = SUBT.user_group_id
202 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
203 and (SUBB.USER_GROUP_NAME <> SUBT.USER_GROUP_NAME
204 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
205 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
206 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
207 ));
208
209 insert into ota_user_groups_tl (
210 user_group_id,
211 USER_GROUP_NAME,
212 DESCRIPTION,
213 CREATED_BY,
214 CREATION_DATE,
215 LAST_UPDATE_DATE,
216 LAST_UPDATED_BY,
217 LAST_UPDATE_LOGIN,
218 LANGUAGE,
219 SOURCE_LANG
220 ) select
221 B.user_group_id,
222 B.USER_GROUP_NAME,
223 B.DESCRIPTION,
224 B.CREATED_BY,
225 B.CREATION_DATE,
226 B.LAST_UPDATE_DATE,
227 B.LAST_UPDATED_BY,
228 B.LAST_UPDATE_LOGIN,
229 L.LANGUAGE_CODE,
230 B.SOURCE_LANG
231 from ota_user_groups_tl B, FND_LANGUAGES L
232 where L.INSTALLED_FLAG in ('I', 'B')
233 and B.LANGUAGE = userenv('LANG')
234 and not exists
235 (select NULL
236 from ota_user_groups_tl T
237 where T.user_group_id = B.user_group_id
238 and T.LANGUAGE = L.LANGUAGE_CODE);
239 end ADD_LANGUAGE;
240 --
241 -- ----------------------------------------------------------------------------
242 -- |-----------------------------< convert_args >-----------------------------|
243 -- ----------------------------------------------------------------------------
244 Function convert_args
245 (p_user_group_id in number
246 ,p_language in varchar2
247 ,p_user_group_name in varchar2
248 ,p_description in varchar2
249 ,p_source_lang in varchar2
250 )
251 Return g_rec_type is
252 --
253 l_rec g_rec_type;
254 --
255 Begin
256 --
257 -- Convert arguments into local l_rec structure.
258 --
259 l_rec.user_group_id := p_user_group_id;
260 l_rec.language := p_language;
261 l_rec.user_group_name := p_user_group_name;
262 l_rec.description := p_description;
263 l_rec.source_lang := p_source_lang;
264 --
265 -- Return the plsql record structure.
266 --
267 Return(l_rec);
268 --
269 End convert_args;
270 --
271 end ota_ugt_shd;