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