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