[Home] [Help]
PACKAGE BODY: APPS.AS_FORECAST_CATEGORIES_PKG
Source
1 PACKAGE BODY AS_FORECAST_CATEGORIES_PKG as
2 /* $Header: asxtfcab.pls 115.12 2004/03/16 05:01:58 sumahali ship $ */
3 -- Start of Comments
4 -- Package name : AS_FORECAST_CATEGORIES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_FORECAST_CATEGORIES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtfcab.pls';
13
14 PROCEDURE Insert_Row(
15 px_FORECAST_CATEGORY_ID IN OUT NOCOPY NUMBER,
16 p_CREATED_BY NUMBER,
17 p_CREATION_DATE DATE,
18 p_LAST_UPDATED_BY NUMBER,
19 p_LAST_UPDATE_DATE DATE,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_FORECAST_CATEGORY_NAME VARCHAR2,
22 p_START_DATE_ACTIVE DATE,
23 p_END_DATE_ACTIVE DATE)
24
25 IS
26 CURSOR C2 IS SELECT AS_FORECAST_CATEGORIES_S.nextval FROM sys.dual;
27 BEGIN
28 If (px_FORECAST_CATEGORY_ID IS NULL) OR (px_FORECAST_CATEGORY_ID = FND_API.G_MISS_NUM) then
29 OPEN C2;
30 FETCH C2 INTO px_FORECAST_CATEGORY_ID;
31 CLOSE C2;
32 End If;
33
34 INSERT INTO AS_FORECAST_CATEGORIES_B(
35 FORECAST_CATEGORY_ID,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATE_LOGIN,
41 START_DATE_ACTIVE,
42 END_DATE_ACTIVE
43 ) VALUES (
44 px_FORECAST_CATEGORY_ID,
45 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
46 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
47 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
48 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
49 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
50 decode( p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_START_DATE_ACTIVE),
51 decode( p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_END_DATE_ACTIVE));
52
53 INSERT INTO AS_FORECAST_CATEGORIES_TL(
54 FORECAST_CATEGORY_ID,
55 CREATED_BY,
56 CREATION_DATE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_DATE,
59 LAST_UPDATE_LOGIN,
60 FORECAST_CATEGORY_NAME,
61 LANGUAGE,
62 SOURCE_LANG
63 ) select
64 px_FORECAST_CATEGORY_ID,
65 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
66 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
67 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
68 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
69 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
70 decode( p_FORECAST_CATEGORY_NAME, FND_API.G_MISS_CHAR, NULL, p_FORECAST_CATEGORY_NAME),
71 L.LANGUAGE_CODE,
72 userenv('LANG')
73 from FND_LANGUAGES L
74 where L.INSTALLED_FLAG in ('I', 'B')
75 and not exists
76 (select NULL
77 from AS_FORECAST_CATEGORIES_TL T
78 where T.FORECAST_CATEGORY_ID = px_FORECAST_CATEGORY_ID
79 and T.LANGUAGE = L.LANGUAGE_CODE);
80
81
82 End Insert_Row;
83
84 PROCEDURE Update_Row(
85 p_FORECAST_CATEGORY_ID NUMBER,
86 p_CREATED_BY NUMBER,
87 p_CREATION_DATE DATE,
88 p_LAST_UPDATED_BY NUMBER,
89 p_LAST_UPDATE_DATE DATE,
90 p_LAST_UPDATE_LOGIN NUMBER,
91 p_FORECAST_CATEGORY_NAME VARCHAR2,
92 p_START_DATE_ACTIVE DATE,
93 p_END_DATE_ACTIVE DATE)
94
95 IS
96 BEGIN
97 Update AS_FORECAST_CATEGORIES_B
98 SET
99 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
100 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
101 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
102 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
103 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
104 START_DATE_ACTIVE = decode( p_START_DATE_ACTIVE, FND_API.G_MISS_DATE, START_DATE_ACTIVE, p_START_DATE_ACTIVE),
105 END_DATE_ACTIVE = decode( p_END_DATE_ACTIVE, FND_API.G_MISS_DATE, END_DATE_ACTIVE, p_END_DATE_ACTIVE)
106 where FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID;
107
108 If (SQL%NOTFOUND) then
109 RAISE NO_DATA_FOUND;
110 End If;
111
112 Update AS_FORECAST_CATEGORIES_TL
113 SET
114 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
115 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
116 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
117 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
118 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
119 FORECAST_CATEGORY_NAME = decode( p_FORECAST_CATEGORY_NAME, FND_API.G_MISS_CHAR, FORECAST_CATEGORY_NAME, p_FORECAST_CATEGORY_NAME),
120 SOURCE_LANG = userenv('LANG')
121 where FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID
122 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
123
124 If (SQL%NOTFOUND) then
125 RAISE NO_DATA_FOUND;
126 End If;
127
128 END Update_Row;
129
130 PROCEDURE Delete_Row(
131 p_FORECAST_CATEGORY_ID NUMBER)
132 IS
133 BEGIN
134 DELETE FROM AS_FORECAST_CATEGORIES_B
135 WHERE FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID;
136
137 If (SQL%NOTFOUND) then
138 RAISE NO_DATA_FOUND;
139 End If;
140
141 DELETE FROM AS_FORECAST_CATEGORIES_TL
142 WHERE FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID;
143
144 If (SQL%NOTFOUND) then
145 RAISE NO_DATA_FOUND;
146 End If;
147
148 END Delete_Row;
149
150 PROCEDURE Lock_Row(
151 p_FORECAST_CATEGORY_ID NUMBER,
152 p_CREATED_BY NUMBER,
153 p_CREATION_DATE DATE,
154 p_LAST_UPDATED_BY NUMBER,
155 p_LAST_UPDATE_DATE DATE,
156 p_LAST_UPDATE_LOGIN NUMBER,
157 p_FORECAST_CATEGORY_NAME VARCHAR2,
158 p_START_DATE_ACTIVE DATE,
159 p_END_DATE_ACTIVE DATE)
160
161 IS
162 CURSOR C IS
163 SELECT *
164 FROM AS_FORECAST_CATEGORIES_B
165 WHERE FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID
166 FOR UPDATE of FORECAST_CATEGORY_ID NOWAIT;
167
168 Recinfo C%ROWTYPE;
169
170 CURSOR C1 IS
171 SELECT FORECAST_CATEGORY_NAME,
172 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
173
174 FROM AS_FORECAST_CATEGORIES_TL
175 WHERE FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID
176 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
177 FOR UPDATE of FORECAST_CATEGORY_ID NOWAIT;
178
179 BEGIN
180 OPEN C;
181 FETCH C INTO Recinfo;
182 If (C%NOTFOUND) then
183 CLOSE C;
184 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 End If;
187 CLOSE C;
188 if (
189 ( Recinfo.FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID)
190 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
191 OR ( ( Recinfo.CREATED_BY IS NULL )
192 AND ( p_CREATED_BY IS NULL )))
193 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
194 OR ( ( Recinfo.CREATION_DATE IS NULL )
195 AND ( p_CREATION_DATE IS NULL )))
196 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
197 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
198 AND ( p_LAST_UPDATED_BY IS NULL )))
199 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
200 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
201 AND ( p_LAST_UPDATE_DATE IS NULL )))
202 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
203 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
204 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
205 AND ( ( Recinfo.START_DATE_ACTIVE = p_START_DATE_ACTIVE)
206 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
207 AND ( p_START_DATE_ACTIVE IS NULL )))
208 AND ( ( Recinfo.END_DATE_ACTIVE = p_END_DATE_ACTIVE)
209 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
210 AND ( p_END_DATE_ACTIVE IS NULL )))
211 ) then
212 return;
213 else
214 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 End If;
217 /*
218 for tlinfo in c1 loop
219 if (tlinfo.BASELANG = 'Y') then
220 if ( ((tlinfo.FORECAST_CATEGORY_NAME = p_FORECAST_CATEGORY_NAME)
221 OR ((tlinfo.FORECAST_CATEGORY_NAME is null) AND (p_FORECAST_CATEGORY_NAME is null)))
222 ) then
223 null;
224 else
225 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
226 app_exception.raise_exception;
227 end if;
228 end if;
229 end loop;
230 --return;
231 */
232
233 END Lock_Row;
234
235 procedure ADD_LANGUAGE
236 is
237 begin
238 delete from AS_FORECAST_CATEGORIES_TL T
239 where not exists
240 (select NULL
241 from AS_FORECAST_CATEGORIES_B B
242 where B.FORECAST_CATEGORY_ID = T.FORECAST_CATEGORY_ID
243 );
244
245 update AS_FORECAST_CATEGORIES_TL T set (
246 FORECAST_CATEGORY_NAME
247 ) = (select
248 FORECAST_CATEGORY_NAME
249 from AS_FORECAST_CATEGORIES_TL B
250 where B.FORECAST_CATEGORY_ID = T.FORECAST_CATEGORY_ID
251 and B.LANGUAGE = T.SOURCE_LANG)
252 where (
253 T.FORECAST_CATEGORY_ID,
254 T.LANGUAGE
255 ) in (select
256 SUBT.FORECAST_CATEGORY_ID,
257 SUBT.LANGUAGE
258 from AS_FORECAST_CATEGORIES_TL SUBB, AS_FORECAST_CATEGORIES_TL SUBT
259 where SUBB.FORECAST_CATEGORY_ID = SUBT.FORECAST_CATEGORY_ID
260 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
261 and ( SUBB.FORECAST_CATEGORY_NAME <> SUBT.FORECAST_CATEGORY_NAME
262 or (SUBB.FORECAST_CATEGORY_NAME is null and SUBT.FORECAST_CATEGORY_NAME is not null)
263 or (SUBB.FORECAST_CATEGORY_NAME is not null and SUBT.FORECAST_CATEGORY_NAME is null)
264 ));
265
266 insert into AS_FORECAST_CATEGORIES_TL (
267 FORECAST_CATEGORY_ID,
268 LAST_UPDATE_DATE,
269 LAST_UPDATED_BY,
270 CREATION_DATE,
271 CREATED_BY,
272 LAST_UPDATE_LOGIN,
273 FORECAST_CATEGORY_NAME,
274 LANGUAGE,
275 SOURCE_LANG
276 ) select
277 B.FORECAST_CATEGORY_ID,
278 B.LAST_UPDATE_DATE,
279 B.LAST_UPDATED_BY,
280 B.CREATION_DATE,
281 B.CREATED_BY,
282 B.LAST_UPDATE_LOGIN,
283 B.FORECAST_CATEGORY_NAME,
284 L.LANGUAGE_CODE,
285 B.SOURCE_LANG
286 from AS_FORECAST_CATEGORIES_TL B, FND_LANGUAGES L
287 where L.INSTALLED_FLAG in ('I', 'B')
288 and B.LANGUAGE = userenv('LANG')
289 and not exists
290 (select NULL
291 from AS_FORECAST_CATEGORIES_TL T
292 where T.FORECAST_CATEGORY_ID = B.FORECAST_CATEGORY_ID
293 and T.LANGUAGE = L.LANGUAGE_CODE);
294 end ADD_LANGUAGE;
295
296
297 PROCEDURE Load_Row(
298 X_FORECAST_CATEGORY_ID in NUMBER,
299 X_FORECAST_CATEGORY_NAME in VARCHAR2,
300 X_OWNER in VARCHAR2,
301 x_START_DATE_ACTIVE in DATE,
302 x_END_DATE_ACTIVE in DATE)
303 IS
304 l_forecast_category_id as_forecast_categories_b.forecast_category_id%Type;
305 li_forecast_category_id NUMBER := 0;
306
307 BEGIN
308 declare
309
310 user_id number := 0;
311
312 cursor custom_exist(p_forecast_category_id NUMBER) is
313 select 'Y'
314 from AS_FORECAST_CATEGORIES_B
315 where last_updated_by <> 1
316 and FORECAST_CATEGORY_ID = p_forecast_category_id;
317
318 l_custom_exist varchar2(1) := 'N';
319
320 begin
321 OPEN custom_exist(X_FORECAST_CATEGORY_ID);
322 FETCH custom_exist into l_custom_exist;
323 CLOSE custom_exist;
324
325 IF nvl(l_custom_exist,'N') = 'N' THEN
326
327 if (X_OWNER = 'SEED') then
328 user_id := 1;
329 end if;
330
331 begin
332
333 l_forecast_category_id := X_FORECAST_CATEGORY_ID;
334
335 AS_FORECAST_CATEGORIES_PKG.UPDATE_ROW(
336 p_FORECAST_CATEGORY_ID => X_FORECAST_CATEGORY_ID,
337 p_CREATED_BY => 0,
338 p_CREATION_DATE => sysdate,
339 p_LAST_UPDATED_BY => user_id,
340 p_LAST_UPDATE_DATE => sysdate,
341 p_LAST_UPDATE_LOGIN => 0,
342 p_FORECAST_CATEGORY_NAME => X_FORECAST_CATEGORY_NAME,--FND_API.G_MISS_CHAR,
343 p_START_DATE_ACTIVE => x_start_date_active,
344 p_END_DATE_ACTIVE => x_end_date_active
345 );
346
347 exception
348 when NO_DATA_FOUND then
349
350 AS_FORECAST_CATEGORIES_PKG.INSERT_ROW(
351 px_FORECAST_CATEGORY_ID => l_FORECAST_CATEGORY_ID,
352 p_CREATED_BY => 0,
353 p_CREATION_DATE => sysdate,
354 p_LAST_UPDATED_BY => user_id,
355 p_LAST_UPDATE_DATE => sysdate,
356 p_LAST_UPDATE_LOGIN => 0 ,
357 p_FORECAST_CATEGORY_NAME => X_FORECAST_CATEGORY_NAME,
358 p_START_DATE_ACTIVE => x_start_date_active,
359 p_END_DATE_ACTIVE => x_end_date_active
360 );
361
362
363 end;
364
365 END IF; --customer not exist
366 end;
367 END Load_Row;
368
369 procedure TRANSLATE_ROW (
370 p_FORECAST_CATEGORY_ID in NUMBER,
371 p_FORECAST_CATEGORY_NAME in VARCHAR2,
372 p_OWNER in VARCHAR2)IS
373
374 begin
375 -- only update rows that have not been altered by user
376 update AS_FORECAST_CATEGORIES_TL
377 set FORECAST_CATEGORY_NAME = p_FORECAST_CATEGORY_NAME,
378 source_lang = userenv('LANG'),
379 last_update_date = sysdate,
380 last_updated_by = decode(p_OWNER, 'SEED', 1, 0),
381 last_update_login = 0
382 where FORECAST_CATEGORY_ID = p_FORECAST_CATEGORY_ID
383 and userenv('LANG') in (language, source_lang);
384 end TRANSLATE_ROW;
385
386 End AS_FORECAST_CATEGORIES_PKG;