[Home] [Help]
PACKAGE BODY: APPS.PA_RES_TYPES_PVT
Source
1 package body PA_RES_TYPES_PVT as
2 /* $Header: PARRTPVB.pls 120.0 2005/05/30 20:14:45 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY ROWID,
5 P_RES_TYPE_ID in NUMBER,
6 P_RES_TYPE_CODE in VARCHAR2,
7 P_ENABLED_FLAG in VARCHAR2,
8 P_NAME in VARCHAR2,
9 P_DESCRIPTION in VARCHAR2,
10 P_CREATION_DATE in DATE ,
11 P_CREATED_BY in NUMBER ,
12 P_LAST_UPDATE_DATE in DATE ,
13 P_LAST_UPDATED_BY in NUMBER ,
14 P_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16
17 l_res_type_id pa_res_types_b.res_type_id%type;
18
19
20 cursor C is select ROWID from pa_res_types_b
21 where res_type_id = l_res_type_id;
22 begin
23
24 select nvl(P_RES_TYPE_ID,PA_RES_TYPES_S.nextval)
25 into l_res_type_id
26 from dual;
27
28 insert into pa_res_types_b (
29 RES_TYPE_ID ,
30 RES_TYPE_CODE ,
31 ENABLED_FLAG ,
32 CREATION_DATE ,
33 CREATED_BY ,
34 LAST_UPDATE_DATE ,
35 LAST_UPDATED_BY ,
36 LAST_UPDATE_LOGIN
37 ) values (
38 L_RES_TYPE_ID ,
39 P_RES_TYPE_CODE ,
40 P_ENABLED_FLAG ,
41 P_CREATION_DATE ,
42 P_CREATED_BY ,
43 P_LAST_UPDATE_DATE ,
44 P_LAST_UPDATED_BY ,
45 P_LAST_UPDATE_LOGIN
46 );
47
48 insert into pa_res_types_tl (
49 LAST_UPDATE_LOGIN,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATED_BY,
54 RES_TYPE_ID,
55 NAME,
56 DESCRIPTION,
57 LANGUAGE,
58 SOURCE_LANG
59 ) select
60 P_LAST_UPDATE_LOGIN,
61 P_CREATION_DATE,
62 P_CREATED_BY,
63 P_LAST_UPDATE_DATE,
64 P_LAST_UPDATED_BY,
65 L_RES_TYPE_ID,
66 P_NAME,
67 P_DESCRIPTION,
68 L.LANGUAGE_CODE,
69 userenv('LANG')
70 from FND_LANGUAGES L
71 where L.INSTALLED_FLAG in ('I', 'B')
72 and not exists
73 (select NULL
74 from pa_res_types_tl T
75 where T.RES_TYPE_ID = L_RES_TYPE_ID
76 and T.LANGUAGE = L.LANGUAGE_CODE);
77
78 open c;
79 fetch c into X_ROWID;
80 if (c%notfound) then
81 close c;
82 raise no_data_found;
83 end if;
84 close c;
85
86 end INSERT_ROW;
87
88 procedure LOCK_ROW (
89 P_RES_TYPE_ID in NUMBER
90 ) is
91 cursor c is select
92 RES_TYPE_CODE
93 from pa_res_types_b
94 where RES_TYPE_ID = P_RES_TYPE_ID
95 for update of RES_TYPE_ID nowait;
96 recinfo c%rowtype;
97
98 cursor c1 is select
99 NAME,
100 DESCRIPTION,
101 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
102 from pa_res_types_tl
103 where RES_TYPE_ID = P_RES_TYPE_ID
104 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
105 for update of RES_TYPE_ID nowait;
106 begin
107
108 open c;
109 fetch c into recinfo;
110 if (c%notfound) then
111 close c;
112 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
113 app_exception.raise_exception;
114 end if;
115 close c;
116
117 return;
118
119 end LOCK_ROW;
120
121 procedure UPDATE_ROW (
122 P_RES_TYPE_ID in NUMBER,
123 P_RES_TYPE_CODE in VARCHAR2,
124 P_ENABLED_FLAG in VARCHAR2,
125 P_NAME in VARCHAR2,
126 P_DESCRIPTION in VARCHAR2,
127 P_LAST_UPDATE_DATE in DATE ,
128 P_LAST_UPDATED_BY in NUMBER ,
129 P_LAST_UPDATE_LOGIN in NUMBER
130 ) is
131 begin
132 update pa_res_types_b set
133 RES_TYPE_CODE = P_RES_TYPE_CODE,
134 ENABLED_FLAG = P_ENABLED_FLAG,
135 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
136 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
138 where res_type_id = P_RES_TYPE_ID;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143
144 update pa_res_types_tl set
145 NAME = P_NAME,
146 DESCRIPTION = P_DESCRIPTION,
147 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
148 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
149 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
150 SOURCE_LANG = userenv('LANG')
151 where RES_TYPE_ID = P_RES_TYPE_ID
152 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
153
154 if (sql%notfound) then
155 raise no_data_found;
156 end if;
157 end UPDATE_ROW;
158
159 procedure DELETE_ROW (
160 P_RES_TYPE_ID in NUMBER
161 ) is
162 begin
163 delete from pa_res_types_tl
164 where RES_TYPE_ID = P_RES_TYPE_ID;
165
166 if (sql%notfound) then
167 raise no_data_found;
168 end if;
169
170 delete from pa_res_types_b
171 where RES_TYPE_ID = P_RES_TYPE_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end DELETE_ROW;
177
178 procedure ADD_LANGUAGE
179 is
180 begin
181 delete from pa_res_types_tl T
182 where not exists
183 (select NULL
184 from PA_RES_TYPES_B B
185 where B.RES_TYPE_ID = T.res_type_id
186 );
187
188 update pa_res_types_tl T set (
189 NAME,
190 DESCRIPTION
191 ) = (select
192 B.NAME,
193 B.DESCRIPTION
194 from pa_res_types_tl B
195 where B.RES_TYPE_ID = T.RES_TYPE_ID
196 and B.LANGUAGE = T.SOURCE_LANG)
197 where (
198 T.RES_TYPE_ID,
199 T.LANGUAGE
200 ) in (select
201 SUBT.RES_TYPE_ID,
202 SUBT.LANGUAGE
203 from pa_res_types_tl SUBB, pa_res_types_tl SUBT
204 where SUBB.RES_TYPE_ID = SUBT.RES_TYPE_ID
205 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
206 and (SUBB.NAME <> SUBT.NAME
207 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
208 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
209 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
210 ));
211
212 insert into pa_res_types_tl (
213 LAST_UPDATE_LOGIN,
214 CREATION_DATE,
215 CREATED_BY,
216 LAST_UPDATE_DATE,
217 LAST_UPDATED_BY,
218 res_type_id,
219 NAME,
220 DESCRIPTION,
221 LANGUAGE,
222 SOURCE_LANG
223 ) select
224 B.LAST_UPDATE_LOGIN,
225 B.CREATION_DATE,
226 B.CREATED_BY,
227 B.LAST_UPDATE_DATE,
228 B.LAST_UPDATED_BY,
229 B.RES_TYPE_ID,
230 B.NAME,
231 B.DESCRIPTION,
232 L.LANGUAGE_CODE,
233 B.SOURCE_LANG
234 from pa_res_types_tl B, FND_LANGUAGES L
235 where L.INSTALLED_FLAG in ('I', 'B')
236 and B.LANGUAGE = userenv('LANG')
237 and not exists
238 (select NULL
239 from pa_res_types_tl T
240 where T.RES_TYPE_ID = B.RES_TYPE_ID
241 and T.LANGUAGE = L.LANGUAGE_CODE);
242 end ADD_LANGUAGE;
243
244 procedure TRANSLATE_ROW(
245 P_RES_TYPE_ID in NUMBER ,
246 P_OWNER in VARCHAR2 ,
247 P_NAME in VARCHAR2 ,
248 P_DESCRIPTION in VARCHAR2
249 ) is
250 begin
251
252 update pa_res_types_tl set
253 NAME = P_NAME,
254 DESCRIPTION = P_DESCRIPTION,
255 LAST_UPDATE_DATE = sysdate,
256 LAST_UPDATED_BY = decode(P_OWNER, 'SEED', 1, 0),
257 LAST_UPDATE_LOGIN = 0,
258 SOURCE_LANG = userenv('LANG') --For bug 4129599
259 where res_type_id = P_RES_TYPE_ID
260 /*Bug4129599- Changes Start */
261 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
262 -- (select LANGUAGE_CODE from FND_LANGUAGES where INSTALLED_FLAG = 'B');
263 /*Bug4129599- Changes End */
264
265 if (sql%notfound) then
266 raise no_data_found;
267 end if;
268
269 end TRANSLATE_ROW;
270
271 procedure LOAD_ROW(
272 P_RES_TYPE_ID in NUMBER,
273 P_RES_TYPE_CODE in VARCHAR2,
274 P_ENABLED_FLAG in VARCHAR2,
275 P_NAME in VARCHAR2,
276 P_DESCRIPTION in VARCHAR2,
277 P_OWNER in VARCHAR2
278 ) is
279
280 user_id NUMBER;
281 l_rowid VARCHAR2(64);
282
283 begin
284
285 if (P_OWNER = 'SEED')then
286 user_id := 1;
287 else
288 user_id :=0;
289 end if;
290
291 PA_RES_TYPES_PVT.UPDATE_ROW (
292 P_RES_TYPE_ID => P_RES_TYPE_ID ,
293 P_RES_TYPE_CODE => P_RES_TYPE_CODE ,
294 P_ENABLED_FLAG => P_ENABLED_FLAG ,
295 P_NAME => P_NAME ,
296 P_DESCRIPTION => P_DESCRIPTION ,
297 P_LAST_UPDATE_DATE => sysdate ,
298 P_LAST_UPDATED_BY => user_id ,
299 P_LAST_UPDATE_LOGIN => 0 );
300
301 EXCEPTION
302 WHEN no_data_found then
303 PA_RES_TYPES_PVT.INSERT_ROW (
304 X_ROWID => l_rowid ,
305 P_RES_TYPE_ID => P_RES_TYPE_ID ,
306 P_RES_TYPE_CODE => P_RES_TYPE_CODE ,
307 P_ENABLED_FLAG => P_ENABLED_FLAG ,
308 P_NAME => P_NAME ,
309 P_DESCRIPTION => P_DESCRIPTION ,
310 P_CREATION_DATE => sysdate ,
311 P_CREATED_BY => user_id ,
312 P_LAST_UPDATE_DATE => sysdate ,
313 P_LAST_UPDATED_BY => user_id ,
314 P_LAST_UPDATE_LOGIN => 0 );
315 end LOAD_ROW;
316
317 end PA_RES_TYPES_PVT;