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