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