1 package body OZF_WORKSHEET_HEADERS_PKG as
2 /* $Header: ozftwkhb.pls 120.0 2005/05/31 23:57:59 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_WORKSHEET_HEADER_ID in NUMBER,
6 X_FORECAST_START_DATE in DATE,
7 X_FORECAST_END_DATE in DATE,
8 X_CURRENCY_CODE in VARCHAR2,
9 X_FORECAST_GENERATED in VARCHAR2,
10 X_FORECAST_BASIS in VARCHAR2,
11 X_OFFER_CODE in VARCHAR2,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_START_DATE_ACTIVE in DATE,
14 X_END_DATE_ACTIVE in DATE,
15 X_CUSTOM_SETUP_ID in NUMBER,
16 X_OWNER_ID in NUMBER,
17 X_ACTIVITY_ID in NUMBER,
18 X_TIME_SPREAD in VARCHAR2,
19 X_PRICE_LIST_ID in NUMBER,
20 X_FORECAST_UOM_CODE in VARCHAR2,
21 X_INCREMENT_QUOTA in VARCHAR2,
22 X_OFFER_TYPE in VARCHAR2,
23 X_NAME in VARCHAR2,
24 X_DESCRIPTION in VARCHAR2,
25 X_VERSION_NO in VARCHAR2,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER
31 ) is
32 cursor C is select ROWID from OZF_WORKSHEET_HEADERS_B
33 where WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID
34 ;
35 begin
36 insert into OZF_WORKSHEET_HEADERS_B (
37 FORECAST_START_DATE,
38 FORECAST_END_DATE,
39 CURRENCY_CODE,
40 FORECAST_GENERATED,
41 FORECAST_BASIS,
42 OFFER_CODE,
43 OBJECT_VERSION_NUMBER,
44 START_DATE_ACTIVE,
45 END_DATE_ACTIVE,
46 CUSTOM_SETUP_ID,
47 OWNER_ID,
48 ACTIVITY_ID,
49 TIME_SPREAD,
50 PRICE_LIST_ID,
51 FORECAST_UOM_CODE,
52 INCREMENT_QUOTA,
53 OFFER_TYPE,
54 WORKSHEET_HEADER_ID,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_LOGIN
60 ) values (
61 X_FORECAST_START_DATE,
62 X_FORECAST_END_DATE,
63 X_CURRENCY_CODE,
64 X_FORECAST_GENERATED,
65 X_FORECAST_BASIS,
66 X_OFFER_CODE,
67 X_OBJECT_VERSION_NUMBER,
68 X_START_DATE_ACTIVE,
69 X_END_DATE_ACTIVE,
70 X_CUSTOM_SETUP_ID,
71 X_OWNER_ID,
72 X_ACTIVITY_ID,
73 X_TIME_SPREAD,
74 X_PRICE_LIST_ID,
75 X_FORECAST_UOM_CODE,
76 X_INCREMENT_QUOTA,
77 X_OFFER_TYPE,
78 X_WORKSHEET_HEADER_ID,
79 X_CREATION_DATE,
80 X_CREATED_BY,
81 X_LAST_UPDATE_DATE,
82 X_LAST_UPDATED_BY,
83 X_LAST_UPDATE_LOGIN
84 );
85
86 insert into OZF_WORKSHEET_HEADERS_TL (
87 WORKSHEET_HEADER_ID,
88 CREATION_DATE,
89 CREATED_BY,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN,
93 NAME,
94 DESCRIPTION,
95 VERSION_NO,
96 LANGUAGE,
97 SOURCE_LANG
98 ) select
99 X_WORKSHEET_HEADER_ID,
100 X_CREATION_DATE,
101 X_CREATED_BY,
102 X_LAST_UPDATE_DATE,
103 X_LAST_UPDATED_BY,
104 X_LAST_UPDATE_LOGIN,
105 X_NAME,
106 X_DESCRIPTION,
107 X_VERSION_NO,
108 L.LANGUAGE_CODE,
109 userenv('LANG')
110 from FND_LANGUAGES L
111 where L.INSTALLED_FLAG in ('I', 'B')
112 and not exists
113 (select NULL
114 from OZF_WORKSHEET_HEADERS_TL T
115 where T.WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID
116 and T.LANGUAGE = L.LANGUAGE_CODE);
117
118 open c;
119 fetch c into X_ROWID;
120 if (c%notfound) then
121 close c;
122 raise no_data_found;
123 end if;
124 close c;
125
126 end INSERT_ROW;
127
128 procedure LOCK_ROW (
129 X_WORKSHEET_HEADER_ID in NUMBER,
130 X_FORECAST_START_DATE in DATE,
131 X_FORECAST_END_DATE in DATE,
132 X_CURRENCY_CODE in VARCHAR2,
133 X_FORECAST_GENERATED in VARCHAR2,
134 X_FORECAST_BASIS in VARCHAR2,
135 X_OFFER_CODE in VARCHAR2,
136 X_OBJECT_VERSION_NUMBER in NUMBER,
137 X_START_DATE_ACTIVE in DATE,
138 X_END_DATE_ACTIVE in DATE,
139 X_CUSTOM_SETUP_ID in NUMBER,
140 X_OWNER_ID in NUMBER,
141 X_ACTIVITY_ID in NUMBER,
142 X_TIME_SPREAD in VARCHAR2,
143 X_PRICE_LIST_ID in NUMBER,
144 X_FORECAST_UOM_CODE in VARCHAR2,
145 X_INCREMENT_QUOTA in VARCHAR2,
146 X_OFFER_TYPE in VARCHAR2,
147 X_NAME in VARCHAR2,
148 X_DESCRIPTION in VARCHAR2,
149 X_VERSION_NO in VARCHAR2
150 ) is
151 cursor c is select
152 FORECAST_START_DATE,
153 FORECAST_END_DATE,
154 CURRENCY_CODE,
155 FORECAST_GENERATED,
156 FORECAST_BASIS,
157 OFFER_CODE,
158 OBJECT_VERSION_NUMBER,
159 START_DATE_ACTIVE,
160 END_DATE_ACTIVE,
161 CUSTOM_SETUP_ID,
162 OWNER_ID,
163 ACTIVITY_ID,
164 TIME_SPREAD,
165 PRICE_LIST_ID,
166 FORECAST_UOM_CODE,
167 INCREMENT_QUOTA,
168 OFFER_TYPE
169 from OZF_WORKSHEET_HEADERS_B
170 where WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID
171 for update of WORKSHEET_HEADER_ID nowait;
172 recinfo c%rowtype;
173
174 cursor c1 is select
175 NAME,
176 DESCRIPTION,
177 VERSION_NO,
178 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
179 from OZF_WORKSHEET_HEADERS_TL
180 where WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID
181 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
182 for update of WORKSHEET_HEADER_ID nowait;
183 begin
184 open c;
185 fetch c into recinfo;
186 if (c%notfound) then
187 close c;
188 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
189 app_exception.raise_exception;
190 end if;
191 close c;
192 if ( ((recinfo.FORECAST_START_DATE = X_FORECAST_START_DATE)
193 OR ((recinfo.FORECAST_START_DATE is null) AND (X_FORECAST_START_DATE is null)))
194 AND ((recinfo.FORECAST_END_DATE = X_FORECAST_END_DATE)
195 OR ((recinfo.FORECAST_END_DATE is null) AND (X_FORECAST_END_DATE is null)))
196 AND ((recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
197 OR ((recinfo.CURRENCY_CODE is null) AND (X_CURRENCY_CODE is null)))
198 AND ((recinfo.FORECAST_GENERATED = X_FORECAST_GENERATED)
199 OR ((recinfo.FORECAST_GENERATED is null) AND (X_FORECAST_GENERATED is null)))
200 AND ((recinfo.FORECAST_BASIS = X_FORECAST_BASIS)
201 OR ((recinfo.FORECAST_BASIS is null) AND (X_FORECAST_BASIS is null)))
202 AND ((recinfo.OFFER_CODE = X_OFFER_CODE)
203 OR ((recinfo.OFFER_CODE is null) AND (X_OFFER_CODE is null)))
204 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
205 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
206 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
207 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
208 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
209 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
210 AND (recinfo.CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID)
211 AND ((recinfo.OWNER_ID = X_OWNER_ID)
212 OR ((recinfo.OWNER_ID is null) AND (X_OWNER_ID is null)))
213 AND ((recinfo.ACTIVITY_ID = X_ACTIVITY_ID)
214 OR ((recinfo.ACTIVITY_ID is null) AND (X_ACTIVITY_ID is null)))
215 AND ((recinfo.TIME_SPREAD = X_TIME_SPREAD)
216 OR ((recinfo.TIME_SPREAD is null) AND (X_TIME_SPREAD is null)))
217 AND (recinfo.PRICE_LIST_ID = X_PRICE_LIST_ID)
218 AND ((recinfo.FORECAST_UOM_CODE = X_FORECAST_UOM_CODE)
219 OR ((recinfo.FORECAST_UOM_CODE is null) AND (X_FORECAST_UOM_CODE is null)))
220 AND ((recinfo.INCREMENT_QUOTA = X_INCREMENT_QUOTA)
221 OR ((recinfo.INCREMENT_QUOTA is null) AND (X_INCREMENT_QUOTA is null)))
222 AND ((recinfo.OFFER_TYPE = X_OFFER_TYPE)
223 OR ((recinfo.OFFER_TYPE is null) AND (X_OFFER_TYPE is null)))
224 ) then
225 null;
226 else
227 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
228 app_exception.raise_exception;
229 end if;
230
231 for tlinfo in c1 loop
232 if (tlinfo.BASELANG = 'Y') then
233 if ( (tlinfo.NAME = X_NAME)
234 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
235 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
236 AND ((tlinfo.VERSION_NO = X_VERSION_NO)
237 OR ((tlinfo.VERSION_NO is null) AND (X_VERSION_NO is null)))
238 ) then
239 null;
240 else
241 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
242 app_exception.raise_exception;
243 end if;
244 end if;
245 end loop;
246 return;
247 end LOCK_ROW;
248
249 procedure UPDATE_ROW (
250 X_WORKSHEET_HEADER_ID in NUMBER,
251 X_FORECAST_START_DATE in DATE,
252 X_FORECAST_END_DATE in DATE,
253 X_CURRENCY_CODE in VARCHAR2,
254 X_FORECAST_GENERATED in VARCHAR2,
255 X_FORECAST_BASIS in VARCHAR2,
256 X_OFFER_CODE in VARCHAR2,
257 X_OBJECT_VERSION_NUMBER in NUMBER,
258 X_START_DATE_ACTIVE in DATE,
259 X_END_DATE_ACTIVE in DATE,
260 X_CUSTOM_SETUP_ID in NUMBER,
261 X_OWNER_ID in NUMBER,
262 X_ACTIVITY_ID in NUMBER,
263 X_TIME_SPREAD in VARCHAR2,
264 X_PRICE_LIST_ID in NUMBER,
265 X_FORECAST_UOM_CODE in VARCHAR2,
266 X_INCREMENT_QUOTA in VARCHAR2,
267 X_OFFER_TYPE in VARCHAR2,
268 X_NAME in VARCHAR2,
269 X_DESCRIPTION in VARCHAR2,
270 X_VERSION_NO in VARCHAR2,
271 X_LAST_UPDATE_DATE in DATE,
272 X_LAST_UPDATED_BY in NUMBER,
273 X_LAST_UPDATE_LOGIN in NUMBER
274 ) is
275 begin
276 update OZF_WORKSHEET_HEADERS_B set
277 FORECAST_START_DATE = X_FORECAST_START_DATE,
278 FORECAST_END_DATE = X_FORECAST_END_DATE,
279 CURRENCY_CODE = X_CURRENCY_CODE,
280 FORECAST_GENERATED = X_FORECAST_GENERATED,
281 FORECAST_BASIS = X_FORECAST_BASIS,
282 OFFER_CODE = X_OFFER_CODE,
283 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
284 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
285 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
286 CUSTOM_SETUP_ID = X_CUSTOM_SETUP_ID,
287 OWNER_ID = X_OWNER_ID,
288 ACTIVITY_ID = X_ACTIVITY_ID,
289 TIME_SPREAD = X_TIME_SPREAD,
290 PRICE_LIST_ID = X_PRICE_LIST_ID,
291 FORECAST_UOM_CODE = X_FORECAST_UOM_CODE,
292 INCREMENT_QUOTA = X_INCREMENT_QUOTA,
293 OFFER_TYPE = X_OFFER_TYPE,
294 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
295 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
296 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
297 where WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID;
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302
303 update OZF_WORKSHEET_HEADERS_TL set
304 NAME = X_NAME,
305 DESCRIPTION = X_DESCRIPTION,
306 VERSION_NO = X_VERSION_NO,
307 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
308 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
309 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
310 SOURCE_LANG = userenv('LANG')
311 where WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID
312 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
313
314 if (sql%notfound) then
315 raise no_data_found;
316 end if;
317 end UPDATE_ROW;
318
319 procedure DELETE_ROW (
320 X_WORKSHEET_HEADER_ID in NUMBER
321 ) is
322 begin
323 delete from OZF_WORKSHEET_HEADERS_TL
324 where WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID;
325
326 if (sql%notfound) then
327 raise no_data_found;
328 end if;
329
330 delete from OZF_WORKSHEET_HEADERS_B
331 where WORKSHEET_HEADER_ID = X_WORKSHEET_HEADER_ID;
332
333 if (sql%notfound) then
334 raise no_data_found;
335 end if;
336 end DELETE_ROW;
337
338 procedure ADD_LANGUAGE
339 is
340 begin
341 delete from OZF_WORKSHEET_HEADERS_TL T
342 where not exists
343 (select NULL
344 from OZF_WORKSHEET_HEADERS_B B
345 where B.WORKSHEET_HEADER_ID = T.WORKSHEET_HEADER_ID
346 );
347
348 update OZF_WORKSHEET_HEADERS_TL T set (
349 NAME,
350 DESCRIPTION,
351 VERSION_NO
352 ) = (select
353 B.NAME,
354 B.DESCRIPTION,
355 B.VERSION_NO
356 from OZF_WORKSHEET_HEADERS_TL B
357 where B.WORKSHEET_HEADER_ID = T.WORKSHEET_HEADER_ID
358 and B.LANGUAGE = T.SOURCE_LANG)
359 where (
360 T.WORKSHEET_HEADER_ID,
361 T.LANGUAGE
362 ) in (select
363 SUBT.WORKSHEET_HEADER_ID,
364 SUBT.LANGUAGE
365 from OZF_WORKSHEET_HEADERS_TL SUBB, OZF_WORKSHEET_HEADERS_TL SUBT
366 where SUBB.WORKSHEET_HEADER_ID = SUBT.WORKSHEET_HEADER_ID
367 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
368 and (SUBB.NAME <> SUBT.NAME
369 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
370 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
371 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
372 or SUBB.VERSION_NO <> SUBT.VERSION_NO
373 or (SUBB.VERSION_NO is null and SUBT.VERSION_NO is not null)
374 or (SUBB.VERSION_NO is not null and SUBT.VERSION_NO is null)
375 ));
376
377 insert into OZF_WORKSHEET_HEADERS_TL (
378 WORKSHEET_HEADER_ID,
379 CREATION_DATE,
380 CREATED_BY,
381 LAST_UPDATE_DATE,
382 LAST_UPDATED_BY,
383 LAST_UPDATE_LOGIN,
384 NAME,
385 DESCRIPTION,
386 VERSION_NO,
387 LANGUAGE,
388 SOURCE_LANG
389 ) select /*+ ORDERED */
390 B.WORKSHEET_HEADER_ID,
391 B.CREATION_DATE,
392 B.CREATED_BY,
393 B.LAST_UPDATE_DATE,
394 B.LAST_UPDATED_BY,
395 B.LAST_UPDATE_LOGIN,
396 B.NAME,
397 B.DESCRIPTION,
398 B.VERSION_NO,
399 L.LANGUAGE_CODE,
400 B.SOURCE_LANG
401 from OZF_WORKSHEET_HEADERS_TL B, FND_LANGUAGES L
402 where L.INSTALLED_FLAG in ('I', 'B')
403 and B.LANGUAGE = userenv('LANG')
404 and not exists
405 (select NULL
406 from OZF_WORKSHEET_HEADERS_TL T
407 where T.WORKSHEET_HEADER_ID = B.WORKSHEET_HEADER_ID
408 and T.LANGUAGE = L.LANGUAGE_CODE);
409 end ADD_LANGUAGE;
410
411 end OZF_WORKSHEET_HEADERS_PKG;
[Home] [Help]