1 package body FND_CONC_STATE_LOOKUP_TYPES_PG as
2 /* $Header: AFCPSC6B.pls 120.2 2005/08/19 11:36:55 ddhulla ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_LOOKUP_TYPE_ID in NUMBER,
7 X_LOOKUP_TYPE_NAME in VARCHAR2,
8 X_ENABLED_FLAG in VARCHAR2,
9 X_START_DATE_ACTIVE in DATE,
10 X_END_DATE_ACTIVE in DATE,
11 X_USER_LOOKUP_TYPE_NAME in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from FND_CONC_STATE_LOOKUP_TYPES
20 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
21 ;
22 begin
23 insert into FND_CONC_STATE_LOOKUP_TYPES (
24 LOOKUP_TYPE_ID,
25 LOOKUP_TYPE_NAME,
26 ENABLED_FLAG,
27 START_DATE_ACTIVE,
28 END_DATE_ACTIVE,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN
34 ) values (
35 X_LOOKUP_TYPE_ID,
36 X_LOOKUP_TYPE_NAME,
37 X_ENABLED_FLAG,
38 X_START_DATE_ACTIVE,
39 X_END_DATE_ACTIVE,
40 X_CREATION_DATE,
41 X_CREATED_BY,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_LAST_UPDATE_LOGIN
45 );
46
47 insert into FND_CONC_STATE_LOOKUP_TYPES_TL (
48 LOOKUP_TYPE_ID,
49 USER_LOOKUP_TYPE_NAME,
50 DESCRIPTION,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN,
54 CREATION_DATE,
55 CREATED_BY,
56 LANGUAGE,
57 SOURCE_LANG
58 ) select
59 X_LOOKUP_TYPE_ID,
60 X_USER_LOOKUP_TYPE_NAME,
61 X_DESCRIPTION,
62 X_LAST_UPDATE_DATE,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_LOGIN,
65 X_CREATION_DATE,
66 X_CREATED_BY,
67 L.LANGUAGE_CODE,
68 userenv('LANG')
69 from FND_LANGUAGES L
70 where L.INSTALLED_FLAG in ('I', 'B')
71 and not exists
72 (select NULL
73 from FND_CONC_STATE_LOOKUP_TYPES_TL T
74 where T.LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
75 and T.LANGUAGE = L.LANGUAGE_CODE);
76
77 open c;
78 fetch c into X_ROWID;
79 if (c%notfound) then
80 close c;
81 raise no_data_found;
82 end if;
83 close c;
84
85 end INSERT_ROW;
86
87 procedure LOCK_ROW (
88 X_LOOKUP_TYPE_ID in NUMBER,
89 X_LOOKUP_TYPE_NAME in VARCHAR2,
90 X_ENABLED_FLAG in VARCHAR2,
91 X_START_DATE_ACTIVE in DATE,
92 X_END_DATE_ACTIVE in DATE,
93 X_USER_LOOKUP_TYPE_NAME in VARCHAR2,
94 X_DESCRIPTION in VARCHAR2
95 ) is
96 cursor c is select
97 LOOKUP_TYPE_NAME,
98 ENABLED_FLAG,
99 START_DATE_ACTIVE,
100 END_DATE_ACTIVE
101 from FND_CONC_STATE_LOOKUP_TYPES
102 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
103 for update of LOOKUP_TYPE_ID nowait;
104 recinfo c%rowtype;
105
106 cursor c1 is select
107 USER_LOOKUP_TYPE_NAME,
108 DESCRIPTION
109 from FND_CONC_STATE_LOOKUP_TYPES_TL
110 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
111 and LANGUAGE = userenv('LANG')
112 for update of LOOKUP_TYPE_ID nowait;
113 tlinfo c1%rowtype;
114
115 begin
116 open c;
117 fetch c into recinfo;
118 if (c%notfound) then
119 close c;
120 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
121 app_exception.raise_exception;
122 end if;
123 close c;
124 if ( (recinfo.LOOKUP_TYPE_NAME = X_LOOKUP_TYPE_NAME)
125 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
126 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
127 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
128 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
129 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
130 ) then
131 null;
132 else
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136
137 open c1;
138 fetch c1 into tlinfo;
139 if (c1%notfound) then
140 close c1;
141 return;
142 end if;
143 close c1;
144
145 if ( (tlinfo.USER_LOOKUP_TYPE_NAME = X_USER_LOOKUP_TYPE_NAME)
146 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
147 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
148 ) then
149 null;
150 else
151 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
152 app_exception.raise_exception;
153 end if;
154 return;
155 end LOCK_ROW;
156
157 procedure UPDATE_ROW (
158 X_LOOKUP_TYPE_ID in NUMBER,
159 X_LOOKUP_TYPE_NAME in VARCHAR2,
160 X_ENABLED_FLAG in VARCHAR2,
161 X_START_DATE_ACTIVE in DATE,
162 X_END_DATE_ACTIVE in DATE,
163 X_USER_LOOKUP_TYPE_NAME in VARCHAR2,
164 X_DESCRIPTION in VARCHAR2,
165 X_LAST_UPDATE_DATE in DATE,
166 X_LAST_UPDATED_BY in NUMBER,
167 X_LAST_UPDATE_LOGIN in NUMBER
168 ) is
169 begin
170 update FND_CONC_STATE_LOOKUP_TYPES set
171 LOOKUP_TYPE_NAME = X_LOOKUP_TYPE_NAME,
172 ENABLED_FLAG = X_ENABLED_FLAG,
173 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
174 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
175 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
176 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
177 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
178 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID;
179
180 if (sql%notfound) then
181 raise no_data_found;
182 end if;
183
184 update FND_CONC_STATE_LOOKUP_TYPES_TL set
185 USER_LOOKUP_TYPE_NAME = X_USER_LOOKUP_TYPE_NAME,
186 DESCRIPTION = X_DESCRIPTION,
187 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
188 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
189 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
190 SOURCE_LANG = userenv('LANG')
191 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID
192 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end UPDATE_ROW;
198
199 procedure DELETE_ROW (
200 X_LOOKUP_TYPE_ID in NUMBER
201 ) is
202 begin
203 delete from FND_CONC_STATE_LOOKUP_TYPES
204 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 delete from FND_CONC_STATE_LOOKUP_TYPES_TL
211 where LOOKUP_TYPE_ID = X_LOOKUP_TYPE_ID;
212
213 if (sql%notfound) then
214 raise no_data_found;
215 end if;
216 end DELETE_ROW;
217
218 procedure ADD_LANGUAGE
219 is
220 begin
221
222 /* Mar/19/03 requested by Ric Ginsberg */
223 /* The following delete and update statements are commented out */
224 /* as a quick workaround to fix the time-consuming table handler issue */
225 /* Eventually we'll need to turn them into a separate fix_language procedure */
226 /*
227
228 delete from FND_CONC_STATE_LOOKUP_TYPES_TL T
229 where not exists
230 (select NULL
231 from FND_CONC_STATE_LOOKUP_TYPES B
232 where B.LOOKUP_TYPE_ID = T.LOOKUP_TYPE_ID
233 );
234
235 update FND_CONC_STATE_LOOKUP_TYPES_TL T set (
236 USER_LOOKUP_TYPE_NAME,
237 DESCRIPTION
238 ) = (select
239 B.USER_LOOKUP_TYPE_NAME,
240 B.DESCRIPTION
241 from FND_CONC_STATE_LOOKUP_TYPES_TL B
242 where B.LOOKUP_TYPE_ID = T.LOOKUP_TYPE_ID
243 and B.LANGUAGE = T.SOURCE_LANG)
244 where (
245 T.LOOKUP_TYPE_ID,
246 T.LANGUAGE
247 ) in (select
248 SUBT.LOOKUP_TYPE_ID,
249 SUBT.LANGUAGE
250 from FND_CONC_STATE_LOOKUP_TYPES_TL SUBB, FND_CONC_STATE_LOOKUP_TYPES_TL SUBT
251 where SUBB.LOOKUP_TYPE_ID = SUBT.LOOKUP_TYPE_ID
252 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
253 and (SUBB.USER_LOOKUP_TYPE_NAME <> SUBT.USER_LOOKUP_TYPE_NAME
254 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
255 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
256 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
257 ));
258 */
259
260 insert into FND_CONC_STATE_LOOKUP_TYPES_TL (
261 LOOKUP_TYPE_ID,
262 USER_LOOKUP_TYPE_NAME,
263 DESCRIPTION,
264 LAST_UPDATE_DATE,
265 LAST_UPDATED_BY,
266 LAST_UPDATE_LOGIN,
267 CREATION_DATE,
268 CREATED_BY,
269 LANGUAGE,
270 SOURCE_LANG
271 ) select
272 B.LOOKUP_TYPE_ID,
273 B.USER_LOOKUP_TYPE_NAME,
274 B.DESCRIPTION,
275 B.LAST_UPDATE_DATE,
276 B.LAST_UPDATED_BY,
277 B.LAST_UPDATE_LOGIN,
278 B.CREATION_DATE,
279 B.CREATED_BY,
280 L.LANGUAGE_CODE,
281 B.SOURCE_LANG
282 from FND_CONC_STATE_LOOKUP_TYPES_TL B, FND_LANGUAGES L
283 where L.INSTALLED_FLAG in ('I', 'B')
284 and B.LANGUAGE = userenv('LANG')
285 and not exists
286 (select NULL
287 from FND_CONC_STATE_LOOKUP_TYPES_TL T
288 where T.LOOKUP_TYPE_ID = B.LOOKUP_TYPE_ID
289 and T.LANGUAGE = L.LANGUAGE_CODE);
290 end ADD_LANGUAGE;
291
292 end FND_CONC_STATE_LOOKUP_TYPES_PG;
[Home] [Help]