[Home] [Help]
PACKAGE BODY: APPS.IRC_IRT_SHD
Source
1 Package Body irc_irt_shd as
2 /* $Header: irirtrhi.pkb 120.0 2005/07/26 15:10 mbocutt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_irt_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 = 'IRC_ALL_RECRUITING_SITES_TL_PK') 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_recruiting_site_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 recruiting_site_id
49 ,language
50 ,source_lang
51 ,site_name
52 ,redirection_url
53 ,''
54 ,posting_url
55 ,''
56 from irc_all_recruiting_sites_tl
57 where recruiting_site_id = p_recruiting_site_id
58 and language = p_language;
59 --
60 l_fct_ret boolean;
61 --
62 Begin
63 --
64 If (p_recruiting_site_id is null or
65 p_language is null
66 ) Then
67 --
68 -- One of the primary key arguments is null therefore we must
69 -- set the returning function value to false
70 --
71 l_fct_ret := false;
72 Else
73 If (p_recruiting_site_id
74 = irc_irt_shd.g_old_rec.recruiting_site_id and
75 p_language
76 = irc_irt_shd.g_old_rec.language
77 ) Then
78 --
79 -- The g_old_rec is current therefore we must
80 -- set the returning function to true
81 --
82 l_fct_ret := true;
83 Else
84 --
85 -- Select the current row into g_old_rec
86 --
87 Open C_Sel1;
88 Fetch C_Sel1 Into irc_irt_shd.g_old_rec;
89 If C_Sel1%notfound Then
90 Close C_Sel1;
91 --
92 -- The primary key is invalid therefore we must error
93 --
94 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
95 fnd_message.raise_error;
96 End If;
97 Close C_Sel1;
98 --
99 irc_irt_shd.g_old_rec.redirection_url
100 := dbms_lob.substr(irc_irt_shd.g_old_rec.redirection_url_c);
101 irc_irt_shd.g_old_rec.posting_url
102 := dbms_lob.substr(irc_irt_shd.g_old_rec.posting_url_c);
103 l_fct_ret := true;
104 End If;
105 End If;
106 Return (l_fct_ret);
107 --
108 End api_updating;
109 --
110 -- ----------------------------------------------------------------------------
111 -- |---------------------------------< lck >----------------------------------|
112 -- ----------------------------------------------------------------------------
113 Procedure lck
114 (p_recruiting_site_id in number
115 ,p_language in varchar2
116 ) is
117 --
118 -- Cursor selects the 'current' row from the HR Schema
119 --
120 Cursor C_Sel1 is
121 select
122 recruiting_site_id
123 ,language
124 ,source_lang
125 ,site_name
126 ,redirection_url
127 ,''
128 ,posting_url
129 ,''
130 from irc_all_recruiting_sites_tl
131 where recruiting_site_id = p_recruiting_site_id
132 and language = p_language
133 for update nowait;
134 --
135 l_proc varchar2(72) := g_package||'lck';
136 --
137 Begin
138 hr_utility.set_location('Entering:'||l_proc, 5);
139 --
140 hr_api.mandatory_arg_error
141 (p_api_name => l_proc
142 ,p_argument => 'RECRUITING_SITE_ID'
143 ,p_argument_value => p_recruiting_site_id
144 );
145 hr_utility.set_location(l_proc,6);
146 hr_api.mandatory_arg_error
147 (p_api_name => l_proc
148 ,p_argument => 'LANGUAGE'
149 ,p_argument_value => p_language
150 );
151 --
152 Open C_Sel1;
153 Fetch C_Sel1 Into irc_irt_shd.g_old_rec;
154 If C_Sel1%notfound then
155 Close C_Sel1;
156 --
157 -- The primary key is invalid therefore we must error
158 --
159 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
160 fnd_message.raise_error;
161 End If;
162 Close C_Sel1;
163 --
164 irc_irt_shd.g_old_rec.redirection_url
165 := dbms_lob.substr(irc_irt_shd.g_old_rec.redirection_url_c);
166 irc_irt_shd.g_old_rec.posting_url
167 := dbms_lob.substr(irc_irt_shd.g_old_rec.posting_url_c);
168 --
169 hr_utility.set_location(' Leaving:'||l_proc, 10);
170 --
171 -- We need to trap the ORA LOCK exception
172 --
173 Exception
174 When HR_Api.Object_Locked then
175 --
176 -- The object is locked therefore we need to supply a meaningful
177 -- error message.
178 --
179 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
180 fnd_message.set_token('TABLE_NAME', 'irc_all_recruiting_sites_tl');
181 fnd_message.raise_error;
182 End lck;
183 --
184 -- ----------------------------------------------------------------------------
185 -- |----------------------------< add_language >------------------------------|
186 -- ----------------------------------------------------------------------------
187 -- ----------------------------------------------------------------------------
188 PROCEDURE add_language IS
189 Begin
190 delete from IRC_ALL_RECRUITING_SITES_TL T
191 where not exists
192 (select NULL
193 from IRC_ALL_RECRUITING_SITES B
194 where B.RECRUITING_SITE_ID = T.RECRUITING_SITE_ID
195 );
196
197 update IRC_ALL_RECRUITING_SITES_TL T set (
198 SITE_NAME,
199 REDIRECTION_URL,
200 POSTING_URL
201 ) = (select
202 B.SITE_NAME,
203 B.REDIRECTION_URL,
204 B.POSTING_URL
205 from IRC_ALL_RECRUITING_SITES_TL B
206 where B.RECRUITING_SITE_ID = T.RECRUITING_SITE_ID
207 and B.LANGUAGE = T.SOURCE_LANG)
208 where (
209 T.RECRUITING_SITE_ID,
210 T.LANGUAGE
211 ) in (select
212 SUBT.RECRUITING_SITE_ID,
213 SUBT.LANGUAGE
214 from IRC_ALL_RECRUITING_SITES_TL SUBB, IRC_ALL_RECRUITING_SITES_TL SUBT
215 where SUBB.RECRUITING_SITE_ID = SUBT.RECRUITING_SITE_ID
216 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
217 and (SUBB.SITE_NAME <> SUBT.SITE_NAME
218 or dbms_lob.compare(SUBB.REDIRECTION_URL,SUBT.REDIRECTION_URL) <> 0
219 or (SUBB.REDIRECTION_URL is null and SUBT.REDIRECTION_URL is not null)
220 or (SUBB.REDIRECTION_URL is not null and SUBT.REDIRECTION_URL is null)
221 or dbms_lob.compare(SUBB.POSTING_URL,SUBT.POSTING_URL) <> 0
222 or (SUBB.POSTING_URL is null and SUBT.POSTING_URL is not null)
223 or (SUBB.POSTING_URL is not null and SUBT.POSTING_URL is null)
224 ));
225
226 insert into IRC_ALL_RECRUITING_SITES_TL (
227 SITE_NAME,
228 REDIRECTION_URL,
229 POSTING_URL,
230 RECRUITING_SITE_ID,
231 LANGUAGE,
232 SOURCE_LANG
233 ) select /*+ ORDERED */
234 B.SITE_NAME,
235 B.REDIRECTION_URL,
236 B.POSTING_URL,
237 B.RECRUITING_SITE_ID,
238 L.LANGUAGE_CODE,
239 B.SOURCE_LANG
240 from IRC_ALL_RECRUITING_SITES_TL B, FND_LANGUAGES L
241 where L.INSTALLED_FLAG in ('I', 'B')
242 and B.LANGUAGE = userenv('LANG')
243 and not exists
244 (select NULL
245 from IRC_ALL_RECRUITING_SITES_TL T
246 where T.RECRUITING_SITE_ID = B.RECRUITING_SITE_ID
247 and T.LANGUAGE = L.LANGUAGE_CODE);
248 --
249 End add_language;
250 --
251 -- ----------------------------------------------------------------------------
252 -- |-----------------------------< convert_args >-----------------------------|
253 -- ----------------------------------------------------------------------------
254 Function convert_args
255 (p_recruiting_site_id in number
256 ,p_language in varchar2
257 ,p_source_lang in varchar2
258 ,p_site_name in varchar2
259 ,p_redirection_url in varchar2
260 ,p_posting_url in varchar2
261 )
262 Return g_rec_type is
263 --
264 l_rec g_rec_type;
265 --
266 Begin
267 --
268 -- Convert arguments into local l_rec structure.
269 --
270 l_rec.recruiting_site_id := p_recruiting_site_id;
271 l_rec.language := p_language;
272 l_rec.source_lang := p_source_lang;
273 l_rec.site_name := p_site_name;
274 l_rec.redirection_url := p_redirection_url;
275 l_rec.posting_url := p_posting_url;
276 --
277 -- Return the plsql record structure.
278 --
279 Return(l_rec);
280 --
281 End convert_args;
282 --
283 end irc_irt_shd;