[Home] [Help]
PACKAGE BODY: APPS.BSC_SYS_CALENDARS_PKG
Source
1 package body BSC_SYS_CALENDARS_PKG as
2 /* $Header: BSCCALDB.pls 115.5 2003/01/10 23:57:05 meastmon ship $ */
3 procedure INSERT_ROW (
4 X_CALENDAR_ID in NUMBER,
5 X_EDW_FLAG in NUMBER,
6 X_FISCAL_YEAR in NUMBER,
7 X_FISCAL_CHANGE in NUMBER,
8 X_RANGE_YR_MOD in NUMBER,
9 X_CURRENT_YEAR in NUMBER,
10 X_START_MONTH in NUMBER,
11 X_START_DAY in NUMBER,
12 X_NAME in VARCHAR2,
13 X_HELP in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 begin
21 insert into BSC_SYS_CALENDARS_B (
22 CALENDAR_ID,
23 EDW_FLAG,
24 FISCAL_YEAR,
25 FISCAL_CHANGE,
26 RANGE_YR_MOD ,
27 CURRENT_YEAR ,
28 START_MONTH ,
29 START_DAY ,
30 CREATION_DATE ,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY ,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_CALENDAR_ID,
37 X_EDW_FLAG,
38 X_FISCAL_YEAR,
39 X_FISCAL_CHANGE,
40 X_RANGE_YR_MOD ,
41 X_CURRENT_YEAR ,
42 X_START_MONTH ,
43 X_START_DAY ,
44 X_CREATION_DATE ,
45 X_CREATED_BY,
46 X_LAST_UPDATE_DATE,
47 X_LAST_UPDATED_BY ,
48 X_LAST_UPDATE_LOGIN
49 );
50
51 insert into BSC_SYS_CALENDARS_TL (
52 CALENDAR_ID,
53 NAME,
54 HELP,
55 CREATION_DATE ,
56 CREATED_BY,
57 LAST_UPDATE_DATE,
58 LAST_UPDATED_BY ,
59 LAST_UPDATE_LOGIN,
60 LANGUAGE,
61 SOURCE_LANG
62 ) select
63 X_CALENDAR_ID,
64 X_NAME,
65 X_HELP,
66 X_CREATION_DATE ,
67 X_CREATED_BY,
68 X_LAST_UPDATE_DATE,
69 X_LAST_UPDATED_BY ,
70 X_LAST_UPDATE_LOGIN,
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 BSC_SYS_CALENDARS_TL T
78 where T.CALENDAR_ID = X_CALENDAR_ID
79 and T.LANGUAGE = L.LANGUAGE_CODE);
80
81 end INSERT_ROW;
82
83 procedure TRANSLATE_ROW(
84 X_CALENDAR_ID in NUMBER,
85 X_NAME in VARCHAR2,
86 X_HELP in VARCHAR2)
87 is
88 begin
89 update BSC_SYS_CALENDARS_TL set
90 NAME = NVL(X_NAME,NAME),
91 HELP = NVL(X_HELP, HELP),
92 SOURCE_LANG = userenv('LANG')
93 where
94 userenv('LANG') in (LANGUAGE, SOURCE_LANG)
95 and CALENDAR_ID = X_CALENDAR_ID;
96 end TRANSLATE_ROW;
97
98 procedure LOCK_ROW (
99 X_CALENDAR_ID in NUMBER,
100 X_EDW_FLAG in NUMBER,
101 X_FISCAL_YEAR in NUMBER,
102 X_FISCAL_CHANGE in NUMBER,
103 X_RANGE_YR_MOD in NUMBER,
104 X_CURRENT_YEAR in NUMBER,
105 X_START_MONTH in NUMBER,
106 X_START_DAY in NUMBER,
107 X_NAME in VARCHAR2,
108 X_HELP in VARCHAR2
109 ) is
110 cursor c is select
111 CALENDAR_ID,
112 EDW_FLAG,
113 FISCAL_YEAR,
114 FISCAL_CHANGE,
115 RANGE_YR_MOD ,
116 CURRENT_YEAR ,
117 START_MONTH ,
118 START_DAY
119 from BSC_SYS_CALENDARS_B
120 where CALENDAR_ID = X_CALENDAR_ID
121 for update of CALENDAR_ID nowait;
122 recinfo c%rowtype;
123
124 cursor c1 is select
125 NAME,
126 HELP,
127 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
128 from BSC_SYS_CALENDARS_TL
129 where CALENDAR_ID = X_CALENDAR_ID
130 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
131 for update of CALENDAR_ID nowait;
132 begin
133 open c;
134 fetch c into recinfo;
135 if (c%notfound) then
136 close c;
137 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
138 app_exception.raise_exception;
139 end if;
140 close c;
141 if ( (recinfo.EDW_FLAG = X_EDW_FLAG)
142 AND (recinfo.FISCAL_YEAR = X_FISCAL_YEAR)
143 AND (recinfo.FISCAL_CHANGE = X_FISCAL_CHANGE)
144 AND (recinfo.RANGE_YR_MOD = X_RANGE_YR_MOD)
145 AND ((recinfo.CURRENT_YEAR = X_CURRENT_YEAR)
146 OR ((recinfo.CURRENT_YEAR is null) AND (X_CURRENT_YEAR is null)))
147 AND ((recinfo.START_MONTH = X_START_MONTH)
148 OR ((recinfo.START_MONTH is null) AND (X_START_MONTH is null)))
149 AND ((recinfo.START_DAY = X_START_DAY)
150 OR ((recinfo.START_DAY is null) AND (X_START_DAY is null)))
151 ) then
152 null;
153 else
154 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
155 app_exception.raise_exception;
156 end if;
157
158 for tlinfo in c1 loop
159 if (tlinfo.BASELANG = 'Y') then
160 if ( (tlinfo.NAME = X_NAME)
161 AND (tlinfo.HELP = X_HELP)
162 ) then
163 null;
164 else
165 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
166 app_exception.raise_exception;
167 end if;
168 end if;
169 end loop;
170 return;
171 end LOCK_ROW;
172
173 procedure UPDATE_ROW (
174 X_CALENDAR_ID in NUMBER,
175 X_EDW_FLAG in NUMBER,
176 X_FISCAL_YEAR in NUMBER,
177 X_FISCAL_CHANGE in NUMBER,
178 X_RANGE_YR_MOD in NUMBER,
179 X_CURRENT_YEAR in NUMBER,
180 X_START_MONTH in NUMBER,
181 X_START_DAY in NUMBER,
182 X_NAME in VARCHAR2,
183 X_HELP in VARCHAR2,
184 X_CREATION_DATE in DATE,
185 X_CREATED_BY in NUMBER,
186 X_LAST_UPDATE_DATE in DATE,
187 X_LAST_UPDATED_BY in NUMBER,
188 X_LAST_UPDATE_LOGIN in NUMBER
189 ) is
190 begin
191 update BSC_SYS_CALENDARS_B set
192 EDW_FLAG = X_EDW_FLAG,
193 FISCAL_YEAR = DECODE(LAST_UPDATED_BY,1,X_FISCAL_YEAR,FISCAL_YEAR),
194 FISCAL_CHANGE =DECODE(LAST_UPDATED_BY,1,X_FISCAL_CHANGE,FISCAL_CHANGE),
195 RANGE_YR_MOD =DECODE(LAST_UPDATED_BY,1,X_RANGE_YR_MOD,RANGE_YR_MOD),
196 CURRENT_YEAR =DECODE(LAST_UPDATED_BY,1,X_CURRENT_YEAR,CURRENT_YEAR),
197 START_MONTH = DECODE(LAST_UPDATED_BY,1,X_START_MONTH,START_MONTH),
198 START_DAY = DECODE(LAST_UPDATED_BY,1,X_START_DAY,START_DAY),
199 LAST_UPDATE_DATE = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_DATE,LAST_UPDATE_DATE),
200 LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATED_BY,LAST_UPDATED_BY),
201 LAST_UPDATE_LOGIN = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_LOGIN,LAST_UPDATE_LOGIN)
202 WHERE CALENDAR_ID = X_CALENDAR_ID;
203 if (sql%notfound) then
204 raise no_data_found;
205 end if;
206
207 update BSC_SYS_CALENDARS_TL set
208 NAME = X_NAME,
209 HELP = X_HELP,
210 SOURCE_LANG = userenv('LANG')
211 where CALENDAR_ID = X_CALENDAR_ID
212 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
213
214 if (sql%notfound) then
215 raise no_data_found;
216 end if;
217 end UPDATE_ROW;
218
219 procedure DELETE_ROW (
220 X_CALENDAR_ID in NUMBER
221 ) is
222 begin
223 delete from BSC_SYS_CALENDARS_TL
224 where CALENDAR_ID = X_CALENDAR_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229
230 delete from BSC_SYS_CALENDARS_B
231 where CALENDAR_ID = X_CALENDAR_ID;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236 end DELETE_ROW;
237
238 procedure ADD_LANGUAGE
239 is
240 begin
241 delete from BSC_SYS_CALENDARS_TL T
242 where not exists
243 (select NULL
244 from BSC_SYS_CALENDARS_B B
245 where B.CALENDAR_ID = T.CALENDAR_ID
246 );
247
248 update BSC_SYS_CALENDARS_TL T set (
249 NAME,
250 HELP
251 ) = (select
252 B.NAME,
253 B.HELP
254 from BSC_SYS_CALENDARS_TL B
255 where B.CALENDAR_ID = T.CALENDAR_ID
256 and B.LANGUAGE = T.SOURCE_LANG)
257 where (
258 T.CALENDAR_ID,
259 T.LANGUAGE
260 ) in (select
261 SUBT.CALENDAR_ID,
262 SUBT.LANGUAGE
263 from BSC_SYS_CALENDARS_TL SUBB, BSC_SYS_CALENDARS_TL SUBT
264 where SUBB.CALENDAR_ID = SUBT.CALENDAR_ID
265 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
266 and (SUBB.NAME <> SUBT.NAME
267 or SUBB.HELP <> SUBT.HELP
268 ));
269
270 insert into BSC_SYS_CALENDARS_TL (
271 CALENDAR_ID,
272 NAME,
273 HELP,
274 LANGUAGE,
275 SOURCE_LANG,
276 CREATION_DATE ,
277 CREATED_BY,
278 LAST_UPDATE_DATE,
279 LAST_UPDATED_BY
280 ) select
281 B.CALENDAR_ID,
282 B.NAME,
283 B.HELP,
284 L.LANGUAGE_CODE,
285 B.SOURCE_LANG,
286 B.CREATION_DATE ,
287 B.CREATED_BY,
288 B.LAST_UPDATE_DATE,
289 B.LAST_UPDATED_BY
290 from BSC_SYS_CALENDARS_TL B, FND_LANGUAGES L
291 where L.INSTALLED_FLAG in ('I', 'B')
292 and B.LANGUAGE = userenv('LANG')
293 and not exists
294 (select NULL
295 from BSC_SYS_CALENDARS_TL T
296 where T.CALENDAR_ID = B.CALENDAR_ID
297 and T.LANGUAGE = L.LANGUAGE_CODE);
298 end ADD_LANGUAGE;
299
300 end BSC_SYS_CALENDARS_PKG;