1 package body CSD_RETURN_RULES_PKG as
2 /* $Header: csdtrrlb.pls 120.1 2011/07/06 10:05:52 subhat noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_SOURCE_ID in NUMBER,
8 X_SOURCE_TYPE in VARCHAR2,
9 X_PRECEDENCE in NUMBER,
10 X_VALUE in VARCHAR2,
11 X_NAME in VARCHAR2,
12 X_DESCRIPTION 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_RULES_B
20 where RULE_ID = X_RULE_ID
21 ;
22 begin
23 insert into CSD_RETURN_RULES_B (
24 OBJECT_VERSION_NUMBER,
25 SOURCE_ID,
26 SOURCE_TYPE,
27 PRECEDENCE,
28 VALUE,
29 RULE_ID,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_OBJECT_VERSION_NUMBER,
37 X_SOURCE_ID,
38 X_SOURCE_TYPE,
39 X_PRECEDENCE,
40 X_VALUE,
41 X_RULE_ID,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into CSD_RETURN_RULES_TL (
50 NAME,
51 DESCRIPTION,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_DATE,
56 LAST_UPDATE_LOGIN,
57 RULE_ID,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_NAME,
62 X_DESCRIPTION,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_LAST_UPDATE_LOGIN,
68 X_RULE_ID,
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_RULES_TL T
76 where T.RULE_ID = X_RULE_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_RULE_ID in NUMBER,
91 X_OBJECT_VERSION_NUMBER in NUMBER,
92 X_SOURCE_ID in NUMBER,
93 X_SOURCE_TYPE in VARCHAR2,
94 X_PRECEDENCE in NUMBER,
95 X_VALUE in VARCHAR2,
96 X_NAME in VARCHAR2,
97 X_DESCRIPTION in VARCHAR2
98 ) is
99 cursor c is select
100 OBJECT_VERSION_NUMBER,
101 SOURCE_ID,
102 SOURCE_TYPE,
103 PRECEDENCE,
104 VALUE
105 from CSD_RETURN_RULES_B
106 where RULE_ID = X_RULE_ID
107 for update of RULE_ID nowait;
108 recinfo c%rowtype;
109
110 cursor c1 is select
111 NAME,
112 DESCRIPTION,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from CSD_RETURN_RULES_TL
115 where RULE_ID = X_RULE_ID
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of RULE_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.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
128 AND (recinfo.SOURCE_ID = X_SOURCE_ID)
129 AND (recinfo.SOURCE_TYPE = X_SOURCE_TYPE)
130 AND (recinfo.PRECEDENCE = X_PRECEDENCE)
131 AND ((recinfo.VALUE = X_VALUE)
132 OR ((recinfo.VALUE is null) AND (X_VALUE is null)))
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 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
144 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
145 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
146 ) then
147 null;
148 else
149 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
150 app_exception.raise_exception;
151 end if;
152 end if;
153 end loop;
154 return;
155 end LOCK_ROW;
156
157 procedure UPDATE_ROW (
158 X_RULE_ID in NUMBER,
159 X_OBJECT_VERSION_NUMBER in NUMBER,
160 X_SOURCE_ID in NUMBER,
161 X_SOURCE_TYPE in VARCHAR2,
162 X_PRECEDENCE in NUMBER,
163 X_VALUE in VARCHAR2,
164 X_NAME in VARCHAR2,
165 X_DESCRIPTION in VARCHAR2,
166 X_LAST_UPDATE_DATE in DATE,
167 X_LAST_UPDATED_BY in NUMBER,
168 X_LAST_UPDATE_LOGIN in NUMBER
169 ) is
170 begin
171 update CSD_RETURN_RULES_B set
172 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
173 SOURCE_ID = X_SOURCE_ID,
174 SOURCE_TYPE = X_SOURCE_TYPE,
175 PRECEDENCE = X_PRECEDENCE,
176 VALUE = X_VALUE,
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 RULE_ID = X_RULE_ID;
181
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185
186 update CSD_RETURN_RULES_TL set
187 NAME = X_NAME,
188 DESCRIPTION = X_DESCRIPTION,
189 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
190 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
191 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
192 SOURCE_LANG = userenv('LANG')
193 where RULE_ID = X_RULE_ID
194 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199 end UPDATE_ROW;
200
201 procedure DELETE_ROW (
202 X_RULE_ID in NUMBER
203 ) is
204 begin
205 delete from CSD_RETURN_RULES_TL
206 where RULE_ID = X_RULE_ID;
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211
212 delete from CSD_RETURN_RULES_B
213 where RULE_ID = X_RULE_ID;
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218 end DELETE_ROW;
219
220 procedure ADD_LANGUAGE
221 is
222 begin
223 delete from CSD_RETURN_RULES_TL T
224 where not exists
225 (select NULL
226 from CSD_RETURN_RULES_B B
227 where B.RULE_ID = T.RULE_ID
228 );
229
230 update CSD_RETURN_RULES_TL T set (
231 NAME,
232 DESCRIPTION
233 ) = (select
234 B.NAME,
235 B.DESCRIPTION
236 from CSD_RETURN_RULES_TL B
237 where B.RULE_ID = T.RULE_ID
238 and B.LANGUAGE = T.SOURCE_LANG)
239 where (
240 T.RULE_ID,
241 T.LANGUAGE
242 ) in (select
243 SUBT.RULE_ID,
244 SUBT.LANGUAGE
245 from CSD_RETURN_RULES_TL SUBB, CSD_RETURN_RULES_TL SUBT
246 where SUBB.RULE_ID = SUBT.RULE_ID
247 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
248 and (SUBB.NAME <> SUBT.NAME
249 or (SUBB.NAME is null and SUBT.NAME is not null)
250 or (SUBB.NAME is not null and SUBT.NAME is null)
251 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
252 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
253 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
254 ));
255
256 insert into CSD_RETURN_RULES_TL (
257 NAME,
258 DESCRIPTION,
259 CREATION_DATE,
260 CREATED_BY,
261 LAST_UPDATED_BY,
262 LAST_UPDATE_DATE,
263 LAST_UPDATE_LOGIN,
264 RULE_ID,
265 LANGUAGE,
266 SOURCE_LANG
267 ) select /*+ ORDERED */
268 B.NAME,
269 B.DESCRIPTION,
270 B.CREATION_DATE,
271 B.CREATED_BY,
272 B.LAST_UPDATED_BY,
273 B.LAST_UPDATE_DATE,
274 B.LAST_UPDATE_LOGIN,
275 B.RULE_ID,
276 L.LANGUAGE_CODE,
277 B.SOURCE_LANG
278 from CSD_RETURN_RULES_TL B, FND_LANGUAGES L
279 where L.INSTALLED_FLAG in ('I', 'B')
280 and B.LANGUAGE = userenv('LANG')
281 and not exists
282 (select NULL
283 from CSD_RETURN_RULES_TL T
284 where T.RULE_ID = B.RULE_ID
285 and T.LANGUAGE = L.LANGUAGE_CODE);
286 end ADD_LANGUAGE;
287
288 end CSD_RETURN_RULES_PKG;
[Home] [Help]