[Home] [Help]
PACKAGE BODY: APPS.IEC_O_RECYCLING_ALGS_PKG
Source
1 package body IEC_O_RECYCLING_ALGS_PKG as
2 /* $Header: IECHRCYB.pls 115.10 2004/02/12 18:41:16 jcmoore ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_ALGORITHM_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_SOURCE_TYPE_CODE in VARCHAR2,
8 X_ALGORITHM_NAME in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER,
14 X_PARENT_ID in NUMBER
15 ) is
16 cursor C is select ROWID from IEC_O_RECYCLING_ALGS_B
17 where ALGORITHM_ID = X_ALGORITHM_ID
18 ;
19 begin
20 insert into IEC_O_RECYCLING_ALGS_B (
21 OBJECT_VERSION_NUMBER,
22 ALGORITHM_ID,
23 SOURCE_TYPE_CODE,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN,
29 PARENT_ID
30 ) values (
31 X_OBJECT_VERSION_NUMBER,
32 X_ALGORITHM_ID,
33 X_SOURCE_TYPE_CODE,
34 X_CREATION_DATE,
35 X_CREATED_BY,
36 X_LAST_UPDATE_DATE,
37 X_LAST_UPDATED_BY,
38 X_LAST_UPDATE_LOGIN,
39 X_PARENT_ID
40 );
41
42 insert into IEC_O_RECYCLING_ALGS_TL (
43 OBJECT_VERSION_NUMBER,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN,
46 LAST_UPDATED_BY,
47 CREATED_BY,
48 CREATION_DATE,
49 ALGORITHM_ID,
50 ALGORITHM_NAME,
51 LANGUAGE,
52 SOURCE_LANG
53 ) select
54 X_OBJECT_VERSION_NUMBER,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATE_LOGIN,
57 X_LAST_UPDATED_BY,
58 X_CREATED_BY,
59 X_CREATION_DATE,
60 X_ALGORITHM_ID,
61 X_ALGORITHM_NAME,
62 L.LANGUAGE_CODE,
63 userenv('LANG')
64 from FND_LANGUAGES L
65 where L.INSTALLED_FLAG in ('I', 'B')
66 and not exists
67 (select NULL
68 from IEC_O_RECYCLING_ALGS_TL T
69 where T.ALGORITHM_ID = X_ALGORITHM_ID
70 and T.LANGUAGE = L.LANGUAGE_CODE);
71
72 open c;
73 fetch c into X_ROWID;
74 if (c%notfound) then
75 close c;
76 raise no_data_found;
77 end if;
78 close c;
79
80 end INSERT_ROW;
81
82 procedure LOCK_ROW (
83 X_ALGORITHM_ID in NUMBER,
84 X_OBJECT_VERSION_NUMBER in NUMBER,
85 X_SOURCE_TYPE_CODE in VARCHAR2,
86 X_ALGORITHM_NAME in VARCHAR2
87 ) is
88 cursor c is select
89 OBJECT_VERSION_NUMBER,
90 SOURCE_TYPE_CODE
91 from IEC_O_RECYCLING_ALGS_B
92 where ALGORITHM_ID = X_ALGORITHM_ID
93 for update of ALGORITHM_ID nowait;
94 recinfo c%rowtype;
95
96 cursor c1 is select
97 ALGORITHM_NAME,
98 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
99 from IEC_O_RECYCLING_ALGS_TL
100 where ALGORITHM_ID = X_ALGORITHM_ID
101 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
102 for update of ALGORITHM_ID nowait;
103 begin
104 open c;
105 fetch c into recinfo;
106 if (c%notfound) then
107 close c;
108 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
109 app_exception.raise_exception;
110 end if;
111 close c;
112 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
113 AND ((recinfo.SOURCE_TYPE_CODE = X_SOURCE_TYPE_CODE)
114 OR ((recinfo.SOURCE_TYPE_CODE is null) AND (X_SOURCE_TYPE_CODE is null)))
115 ) then
116 null;
117 else
118 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
119 app_exception.raise_exception;
120 end if;
121
122 for tlinfo in c1 loop
123 if (tlinfo.BASELANG = 'Y') then
124 if ( (tlinfo.ALGORITHM_NAME = X_ALGORITHM_NAME)
125 ) then
126 null;
127 else
128 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
129 app_exception.raise_exception;
130 end if;
131 end if;
132 end loop;
133 return;
134 end LOCK_ROW;
135
136 procedure UPDATE_ROW (
137 X_ALGORITHM_ID in NUMBER,
138 X_OBJECT_VERSION_NUMBER in NUMBER,
139 X_SOURCE_TYPE_CODE in VARCHAR2,
140 X_ALGORITHM_NAME in VARCHAR2,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) is
145 begin
146 update IEC_O_RECYCLING_ALGS_B set
147 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
148 SOURCE_TYPE_CODE = X_SOURCE_TYPE_CODE,
149 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
150 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
152 where ALGORITHM_ID = X_ALGORITHM_ID;
153
154 if (sql%notfound) then
155 raise no_data_found;
156 end if;
157
158 update IEC_O_RECYCLING_ALGS_TL set
159 ALGORITHM_NAME = X_ALGORITHM_NAME,
160 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
161 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
163 SOURCE_LANG = userenv('LANG')
164 where ALGORITHM_ID = X_ALGORITHM_ID
165 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
166
167 if (sql%notfound) then
168 raise no_data_found;
169 end if;
170 end UPDATE_ROW;
171
172 procedure DELETE_ROW (
173 X_ALGORITHM_ID in NUMBER
174 ) is
175 begin
176 delete from IEC_O_RECYCLING_ALGS_TL
177 where ALGORITHM_ID = X_ALGORITHM_ID;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182
183 delete from IEC_O_RECYCLING_ALGS_B
184 where ALGORITHM_ID = X_ALGORITHM_ID;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189 end DELETE_ROW;
190
191 procedure ADD_LANGUAGE
192 is
193 begin
194 delete from IEC_O_RECYCLING_ALGS_TL T
195 where not exists
196 (select NULL
197 from IEC_O_RECYCLING_ALGS_B B
198 where B.ALGORITHM_ID = T.ALGORITHM_ID
199 );
200
201 update IEC_O_RECYCLING_ALGS_TL T set (
202 ALGORITHM_NAME
203 ) = (select
204 B.ALGORITHM_NAME
205 from IEC_O_RECYCLING_ALGS_TL B
206 where B.ALGORITHM_ID = T.ALGORITHM_ID
207 and B.LANGUAGE = T.SOURCE_LANG)
208 where (
209 T.ALGORITHM_ID,
210 T.LANGUAGE
211 ) in (select
212 SUBT.ALGORITHM_ID,
213 SUBT.LANGUAGE
214 from IEC_O_RECYCLING_ALGS_TL SUBB, IEC_O_RECYCLING_ALGS_TL SUBT
215 where SUBB.ALGORITHM_ID = SUBT.ALGORITHM_ID
216 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
217 and (SUBB.ALGORITHM_NAME <> SUBT.ALGORITHM_NAME
218 ));
219
220 insert into IEC_O_RECYCLING_ALGS_TL (
221 OBJECT_VERSION_NUMBER,
222 LAST_UPDATE_DATE,
223 LAST_UPDATE_LOGIN,
224 LAST_UPDATED_BY,
225 CREATED_BY,
226 CREATION_DATE,
227 ALGORITHM_ID,
228 ALGORITHM_NAME,
229 LANGUAGE,
230 SOURCE_LANG
231 ) select /*+ ORDERED */
232 B.OBJECT_VERSION_NUMBER,
233 B.LAST_UPDATE_DATE,
234 B.LAST_UPDATE_LOGIN,
235 B.LAST_UPDATED_BY,
236 B.CREATED_BY,
237 B.CREATION_DATE,
238 B.ALGORITHM_ID,
239 B.ALGORITHM_NAME,
240 L.LANGUAGE_CODE,
241 B.SOURCE_LANG
242 from IEC_O_RECYCLING_ALGS_TL B, FND_LANGUAGES L
243 where L.INSTALLED_FLAG in ('I', 'B')
244 and B.LANGUAGE = userenv('LANG')
245 and not exists
246 (select NULL
247 from IEC_O_RECYCLING_ALGS_TL T
248 where T.ALGORITHM_ID = B.ALGORITHM_ID
249 and T.LANGUAGE = L.LANGUAGE_CODE);
250 end ADD_LANGUAGE;
251
252 end IEC_O_RECYCLING_ALGS_PKG;