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