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