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