[Home] [Help]
PACKAGE BODY: APPS.AR_BPA_RULES_PKG
Source
1 package body AR_BPA_RULES_PKG as
2 /* $Header: ARBPRULB.pls 120.3 2005/10/30 04:13:43 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_SAME_PRINTING_TEMPLATE_FLAG in VARCHAR2,
7 X_PRIMARY_APP_ID in NUMBER,
8 X_SECONDARY_APP_ID in NUMBER,
9 X_RULE_SEARCH_ORDER in NUMBER,
10 X_MATCH_ALL_ATTRIBUTES in VARCHAR2,
11 X_SEEDED_FLAG in VARCHAR2,
12 X_RULE_NAME in VARCHAR2,
13 X_RULE_DESCRIPTION in VARCHAR2,
14 X_PRINT_RULE_SEARCH_ORDER in NUMBER,
15 X_CM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
16 X_DM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
17 X_CB_SAME_PRT_TMPLT_FLAG in VARCHAR2,
18 X_DEP_SAME_PRT_TMPLT_FLAG in VARCHAR2,
19 X_GUAR_SAME_PRT_TMPLT_FLAG in VARCHAR2,
20 X_CREATION_DATE in DATE,
21 X_CREATED_BY in NUMBER,
22 X_LAST_UPDATE_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_LOGIN in NUMBER
25 ) is
26 cursor C is select ROWID from AR_BPA_RULES_B
27 where RULE_ID = X_RULE_ID
28 ;
29 begin
30 insert into AR_BPA_RULES_B (
31 SAME_PRINTING_TEMPLATE_FLAG,
32 PRINT_RULE_SEARCH_ORDER,
33 RULE_ID,
34 PRIMARY_APP_ID,
35 SECONDARY_APP_ID,
36 RULE_SEARCH_ORDER,
37 MATCH_ALL_ATTRIBUTES,
38 SEEDED_FLAG,
39 CM_SAME_PRT_TMPLT_FLAG,
40 DM_SAME_PRT_TMPLT_FLAG,
41 CB_SAME_PRT_TMPLT_FLAG,
42 DEP_SAME_PRT_TMPLT_FLAG,
43 GUAR_SAME_PRT_TMPLT_FLAG,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_LOGIN
49 ) values (
50 X_SAME_PRINTING_TEMPLATE_FLAG,
51 X_PRINT_RULE_SEARCH_ORDER,
52 X_RULE_ID,
53 X_PRIMARY_APP_ID,
54 X_SECONDARY_APP_ID,
55 X_RULE_SEARCH_ORDER,
56 X_MATCH_ALL_ATTRIBUTES,
57 X_SEEDED_FLAG,
58 X_CM_SAME_PRT_TMPLT_FLAG,
59 X_DM_SAME_PRT_TMPLT_FLAG,
60 X_CB_SAME_PRT_TMPLT_FLAG,
61 X_DEP_SAME_PRT_TMPLT_FLAG,
62 X_GUAR_SAME_PRT_TMPLT_FLAG,
63 X_CREATION_DATE,
64 X_CREATED_BY,
65 X_LAST_UPDATE_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN
68 );
69
70 insert into AR_BPA_RULES_TL (
71 RULE_ID,
72 RULE_NAME,
73 RULE_DESCRIPTION,
74 CREATED_BY,
75 CREATION_DATE,
76 LAST_UPDATED_BY,
77 LAST_UPDATE_DATE,
78 LAST_UPDATE_LOGIN,
79 LANGUAGE,
80 SOURCE_LANG
81 ) select
82 X_RULE_ID,
83 X_RULE_NAME,
84 X_RULE_DESCRIPTION,
85 X_CREATED_BY,
86 X_CREATION_DATE,
87 X_LAST_UPDATED_BY,
88 X_LAST_UPDATE_DATE,
89 X_LAST_UPDATE_LOGIN,
90 L.LANGUAGE_CODE,
91 userenv('LANG')
92 from FND_LANGUAGES L
93 where L.INSTALLED_FLAG in ('I', 'B')
94 and not exists
95 (select NULL
96 from AR_BPA_RULES_TL T
97 where T.RULE_ID = X_RULE_ID
98 and T.LANGUAGE = L.LANGUAGE_CODE);
99
100 open c;
101 fetch c into X_ROWID;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107
108 end INSERT_ROW;
109
110 procedure LOCK_ROW (
111 X_RULE_ID in NUMBER,
112 X_SAME_PRINTING_TEMPLATE_FLAG in VARCHAR2,
113 X_PRIMARY_APP_ID in NUMBER,
114 X_SECONDARY_APP_ID in NUMBER,
115 X_RULE_SEARCH_ORDER in NUMBER,
116 X_MATCH_ALL_ATTRIBUTES in VARCHAR2,
117 X_SEEDED_FLAG in VARCHAR2,
118 X_RULE_NAME in VARCHAR2,
119 X_RULE_DESCRIPTION in VARCHAR2,
120 X_PRINT_RULE_SEARCH_ORDER IN NUMBER,
121 X_CM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
122 X_DM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
123 X_CB_SAME_PRT_TMPLT_FLAG in VARCHAR2,
124 X_DEP_SAME_PRT_TMPLT_FLAG in VARCHAR2,
125 X_GUAR_SAME_PRT_TMPLT_FLAG in VARCHAR2
126 ) is
127 cursor c is select
128 SAME_PRINTING_TEMPLATE_FLAG,
129 PRINT_RULE_SEARCH_ORDER,
130 PRIMARY_APP_ID,
131 SECONDARY_APP_ID,
132 RULE_SEARCH_ORDER,
133 MATCH_ALL_ATTRIBUTES,
134 SEEDED_FLAG,
135 CM_SAME_PRT_TMPLT_FLAG,
136 DM_SAME_PRT_TMPLT_FLAG,
137 CB_SAME_PRT_TMPLT_FLAG,
138 DEP_SAME_PRT_TMPLT_FLAG,
139 GUAR_SAME_PRT_TMPLT_FLAG
140 from AR_BPA_RULES_B
141 where RULE_ID = X_RULE_ID
142 for update of RULE_ID nowait;
143 recinfo c%rowtype;
144
145 cursor c1 is select
146 RULE_NAME,
147 RULE_DESCRIPTION,
148 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
149 from AR_BPA_RULES_TL
150 where RULE_ID = X_RULE_ID
151 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
152 for update of RULE_ID nowait;
153 begin
154 open c;
155 fetch c into recinfo;
156 if (c%notfound) then
157 close c;
158 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
159 app_exception.raise_exception;
160 end if;
161 close c;
162 if ( ((recinfo.SAME_PRINTING_TEMPLATE_FLAG = X_SAME_PRINTING_TEMPLATE_FLAG)
163 OR ((recinfo.SAME_PRINTING_TEMPLATE_FLAG is null) AND (X_SAME_PRINTING_TEMPLATE_FLAG is null)))
164 AND ((recinfo.CM_SAME_PRT_TMPLT_FLAG = X_CM_SAME_PRT_TMPLT_FLAG)
165 OR ((recinfo.CM_SAME_PRT_TMPLT_FLAG is null) AND (X_CM_SAME_PRT_TMPLT_FLAG is null)))
166 AND ((recinfo.DM_SAME_PRT_TMPLT_FLAG = X_DM_SAME_PRT_TMPLT_FLAG)
167 OR ((recinfo.DM_SAME_PRT_TMPLT_FLAG is null) AND (X_DM_SAME_PRT_TMPLT_FLAG is null)))
168 AND ((recinfo.CB_SAME_PRT_TMPLT_FLAG = X_CB_SAME_PRT_TMPLT_FLAG)
169 OR ((recinfo.CB_SAME_PRT_TMPLT_FLAG is null) AND (X_CB_SAME_PRT_TMPLT_FLAG is null)))
170 AND ((recinfo.DEP_SAME_PRT_TMPLT_FLAG = X_DEP_SAME_PRT_TMPLT_FLAG)
171 OR ((recinfo.DEP_SAME_PRT_TMPLT_FLAG is null) AND (X_DEP_SAME_PRT_TMPLT_FLAG is null)))
172 AND ((recinfo.GUAR_SAME_PRT_TMPLT_FLAG = X_GUAR_SAME_PRT_TMPLT_FLAG)
173 OR ((recinfo.GUAR_SAME_PRT_TMPLT_FLAG is null) AND (X_GUAR_SAME_PRT_TMPLT_FLAG is null)))
174 AND (recinfo.PRIMARY_APP_ID = X_PRIMARY_APP_ID)
175 AND ((recinfo.SECONDARY_APP_ID = X_SECONDARY_APP_ID)
176 OR ((recinfo.SECONDARY_APP_ID is null) AND (X_SECONDARY_APP_ID is null)))
177 AND ( (recinfo.RULE_SEARCH_ORDER = X_RULE_SEARCH_ORDER )
178 OR ((recinfo.RULE_SEARCH_ORDER is null) AND (X_RULE_SEARCH_ORDER is null)))
179 AND (recinfo.MATCH_ALL_ATTRIBUTES = X_MATCH_ALL_ATTRIBUTES)
180 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
181 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
182 AND ((recinfo.PRINT_RULE_SEARCH_ORDER= X_PRINT_RULE_SEARCH_ORDER)
183 OR ((recinfo.PRINT_RULE_SEARCH_ORDER is null) AND (X_PRINT_RULE_SEARCH_ORDER is null)))
184
185 ) then
186 null;
187 else
188 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
189 app_exception.raise_exception;
190 end if;
191
192 for tlinfo in c1 loop
193 if (tlinfo.BASELANG = 'Y') then
194 if ( (tlinfo.RULE_NAME = X_RULE_NAME)
195 AND ((tlinfo.RULE_DESCRIPTION = X_RULE_DESCRIPTION)
196 OR ((tlinfo.RULE_DESCRIPTION is null) AND (X_RULE_DESCRIPTION is null)))
197 ) then
198 null;
199 else
200 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
201 app_exception.raise_exception;
202 end if;
203 end if;
204 end loop;
205 return;
206 end LOCK_ROW;
207
208 procedure UPDATE_ROW (
209 X_RULE_ID in NUMBER,
210 X_SAME_PRINTING_TEMPLATE_FLAG in VARCHAR2,
211 X_PRIMARY_APP_ID in NUMBER,
212 X_SECONDARY_APP_ID in NUMBER,
213 X_RULE_SEARCH_ORDER in NUMBER,
214 X_MATCH_ALL_ATTRIBUTES in VARCHAR2,
215 X_SEEDED_FLAG in VARCHAR2,
216 X_RULE_NAME in VARCHAR2,
217 X_RULE_DESCRIPTION in VARCHAR2,
218 X_PRINT_RULE_SEARCH_ORDER IN NUMBER,
219 X_CM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
220 X_DM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
221 X_CB_SAME_PRT_TMPLT_FLAG in VARCHAR2,
222 X_DEP_SAME_PRT_TMPLT_FLAG in VARCHAR2,
223 X_GUAR_SAME_PRT_TMPLT_FLAG in VARCHAR2,
224 X_LAST_UPDATE_DATE in DATE,
225 X_LAST_UPDATED_BY in NUMBER,
226 X_LAST_UPDATE_LOGIN in NUMBER
227 ) is
228 begin
229 update AR_BPA_RULES_B set
230 SAME_PRINTING_TEMPLATE_FLAG = X_SAME_PRINTING_TEMPLATE_FLAG,
231 PRINT_RULE_SEARCH_ORDER = X_PRINT_RULE_SEARCH_ORDER,
232 PRIMARY_APP_ID = X_PRIMARY_APP_ID,
233 SECONDARY_APP_ID = X_SECONDARY_APP_ID,
234 RULE_SEARCH_ORDER = X_RULE_SEARCH_ORDER,
235 MATCH_ALL_ATTRIBUTES = X_MATCH_ALL_ATTRIBUTES,
236 SEEDED_FLAG = X_SEEDED_FLAG,
237 CM_SAME_PRT_TMPLT_FLAG = X_CM_SAME_PRT_TMPLT_FLAG,
238 DM_SAME_PRT_TMPLT_FLAG = X_DM_SAME_PRT_TMPLT_FLAG,
239 CB_SAME_PRT_TMPLT_FLAG = X_CB_SAME_PRT_TMPLT_FLAG,
240 DEP_SAME_PRT_TMPLT_FLAG = X_DEP_SAME_PRT_TMPLT_FLAG,
241 GUAR_SAME_PRT_TMPLT_FLAG = X_GUAR_SAME_PRT_TMPLT_FLAG,
242 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
243 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
244 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
245 where RULE_ID = X_RULE_ID;
246
247 if (sql%notfound) then
248 raise no_data_found;
249 end if;
250
251 update AR_BPA_RULES_TL set
252 RULE_NAME = X_RULE_NAME,
253 RULE_DESCRIPTION = X_RULE_DESCRIPTION,
254 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
255 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
256 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
257 SOURCE_LANG = userenv('LANG')
258 where RULE_ID = X_RULE_ID
259 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
260
261 if (sql%notfound) then
262 raise no_data_found;
263 end if;
264 end UPDATE_ROW;
265
266 procedure DELETE_ROW (
267 X_RULE_ID in NUMBER
268 ) is
269 begin
270 delete from AR_BPA_RULES_TL
271 where RULE_ID = X_RULE_ID;
272
273 if (sql%notfound) then
274 raise no_data_found;
275 end if;
276
277 delete from AR_BPA_RULES_B
278 where RULE_ID = X_RULE_ID;
279
280 if (sql%notfound) then
281 raise no_data_found;
282 end if;
283 end DELETE_ROW;
284
285 procedure ADD_LANGUAGE
286 is
287 begin
288 delete from AR_BPA_RULES_TL T
289 where not exists
290 (select NULL
291 from AR_BPA_RULES_B B
292 where B.RULE_ID = T.RULE_ID
293 );
294
295 update AR_BPA_RULES_TL T set (
296 RULE_NAME,
297 RULE_DESCRIPTION
298 ) = (select
299 B.RULE_NAME,
300 B.RULE_DESCRIPTION
301 from AR_BPA_RULES_TL B
302 where B.RULE_ID = T.RULE_ID
303 and B.LANGUAGE = T.SOURCE_LANG)
304 where (
305 T.RULE_ID,
306 T.LANGUAGE
307 ) in (select
308 SUBT.RULE_ID,
309 SUBT.LANGUAGE
310 from AR_BPA_RULES_TL SUBB, AR_BPA_RULES_TL SUBT
311 where SUBB.RULE_ID = SUBT.RULE_ID
312 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
313 and (SUBB.RULE_NAME <> SUBT.RULE_NAME
314 or SUBB.RULE_DESCRIPTION <> SUBT.RULE_DESCRIPTION
315 or (SUBB.RULE_DESCRIPTION is null and SUBT.RULE_DESCRIPTION is not null)
316 or (SUBB.RULE_DESCRIPTION is not null and SUBT.RULE_DESCRIPTION is null)
317 ));
318
319 insert into AR_BPA_RULES_TL (
320 RULE_ID,
321 RULE_NAME,
322 RULE_DESCRIPTION,
323 CREATED_BY,
324 CREATION_DATE,
325 LAST_UPDATED_BY,
326 LAST_UPDATE_DATE,
327 LAST_UPDATE_LOGIN,
328 LANGUAGE,
329 SOURCE_LANG
330 ) select /*+ ORDERED */
331 B.RULE_ID,
332 B.RULE_NAME,
333 B.RULE_DESCRIPTION,
334 B.CREATED_BY,
335 B.CREATION_DATE,
336 B.LAST_UPDATED_BY,
337 B.LAST_UPDATE_DATE,
338 B.LAST_UPDATE_LOGIN,
339 L.LANGUAGE_CODE,
340 B.SOURCE_LANG
341 from AR_BPA_RULES_TL B, FND_LANGUAGES L
342 where L.INSTALLED_FLAG in ('I', 'B')
343 and B.LANGUAGE = userenv('LANG')
344 and not exists
345 (select NULL
346 from AR_BPA_RULES_TL T
347 where T.RULE_ID = B.RULE_ID
348 and T.LANGUAGE = L.LANGUAGE_CODE);
349 end ADD_LANGUAGE;
350
351 procedure TRANSLATE_ROW (
352 X_RULE_ID in NUMBER,
353 X_RULE_NAME in VARCHAR2,
354 X_RULE_DESCRIPTION in VARCHAR2,
355 X_OWNER in VARCHAR2) IS
356 begin
357
358 update AR_BPA_RULES_TL
359 set RULE_NAME = X_RULE_NAME,
360 RULE_DESCRIPTION = X_RULE_DESCRIPTION,
361 source_lang = userenv('LANG'),
362 last_update_date = sysdate,
363 last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
364 last_update_login = 0
365 where RULE_ID = X_RULE_ID
366 and userenv('LANG') in (language, source_lang);
367
368 end TRANSLATE_ROW;
369
370 procedure LOAD_ROW (
371 X_RULE_ID in NUMBER,
372 X_PRIMARY_APP_ID in NUMBER,
373 X_SECONDARY_APP_ID in NUMBER,
374 X_RULE_SEARCH_ORDER in NUMBER,
375 X_MATCH_ALL_ATTRIBUTES in VARCHAR2,
376 X_SEEDED_FLAG in VARCHAR2,
377 X_RULE_NAME in VARCHAR2,
378 X_RULE_DESCRIPTION in VARCHAR2,
379 X_PRINT_RULE_SEARCH_ORDER in NUMBER,
380 X_SAME_PRINTING_TEMPLATE_FLAG IN VARCHAR2,
381 X_CM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
382 X_DM_SAME_PRT_TMPLT_FLAG in VARCHAR2,
383 X_CB_SAME_PRT_TMPLT_FLAG in VARCHAR2,
384 X_DEP_SAME_PRT_TMPLT_FLAG in VARCHAR2,
385 X_GUAR_SAME_PRT_TMPLT_FLAG in VARCHAR2,
386 X_OWNER in VARCHAR2
387 ) IS
388 begin
389 declare
390 user_id number := 0;
391 row_id varchar2(64);
392 begin
393 if (X_OWNER = 'SEED') then
394 user_id := 1;
395 end if;
396
397 AR_BPA_RULES_PKG.UPDATE_ROW (
398 X_RULE_ID => X_RULE_ID,
399 X_PRIMARY_APP_ID => X_PRIMARY_APP_ID,
400 X_SECONDARY_APP_ID => X_SECONDARY_APP_ID,
401 X_RULE_SEARCH_ORDER => X_RULE_SEARCH_ORDER,
402 X_MATCH_ALL_ATTRIBUTES => X_MATCH_ALL_ATTRIBUTES,
403 X_SEEDED_FLAG => X_SEEDED_FLAG,
404 X_RULE_NAME => X_RULE_NAME,
405 X_RULE_DESCRIPTION => X_RULE_DESCRIPTION,
406 X_PRINT_RULE_SEARCH_ORDER => X_PRINT_RULE_SEARCH_ORDER,
407 X_SAME_PRINTING_TEMPLATE_FLAG => X_SAME_PRINTING_TEMPLATE_FLAG ,
408 X_CM_SAME_PRT_TMPLT_FLAG => X_CM_SAME_PRT_TMPLT_FLAG ,
409 X_DM_SAME_PRT_TMPLT_FLAG => X_DM_SAME_PRT_TMPLT_FLAG ,
410 X_CB_SAME_PRT_TMPLT_FLAG => X_CB_SAME_PRT_TMPLT_FLAG ,
411 X_DEP_SAME_PRT_TMPLT_FLAG => X_DEP_SAME_PRT_TMPLT_FLAG ,
412 X_GUAR_SAME_PRT_TMPLT_FLAG => X_GUAR_SAME_PRT_TMPLT_FLAG ,
413 X_LAST_UPDATE_DATE => sysdate,
414 X_LAST_UPDATED_BY => user_id,
415 X_LAST_UPDATE_LOGIN => 0);
416 exception
417 when NO_DATA_FOUND then
418 AR_BPA_RULES_PKG.INSERT_ROW (
419 X_ROWID => row_id,
420 X_RULE_ID => X_RULE_ID,
421 X_PRIMARY_APP_ID => X_PRIMARY_APP_ID,
422 X_SECONDARY_APP_ID => X_SECONDARY_APP_ID,
423 X_RULE_SEARCH_ORDER => X_RULE_SEARCH_ORDER,
424 X_MATCH_ALL_ATTRIBUTES => X_MATCH_ALL_ATTRIBUTES,
425 X_SEEDED_FLAG => X_SEEDED_FLAG,
426 X_RULE_NAME => X_RULE_NAME,
427 X_RULE_DESCRIPTION => X_RULE_DESCRIPTION,
428 X_PRINT_RULE_SEARCH_ORDER => X_PRINT_RULE_SEARCH_ORDER,
429 X_SAME_PRINTING_TEMPLATE_FLAG => X_SAME_PRINTING_TEMPLATE_FLAG ,
430 X_CM_SAME_PRT_TMPLT_FLAG => X_CM_SAME_PRT_TMPLT_FLAG ,
431 X_DM_SAME_PRT_TMPLT_FLAG => X_DM_SAME_PRT_TMPLT_FLAG ,
432 X_CB_SAME_PRT_TMPLT_FLAG => X_CB_SAME_PRT_TMPLT_FLAG ,
433 X_DEP_SAME_PRT_TMPLT_FLAG => X_DEP_SAME_PRT_TMPLT_FLAG ,
434 X_GUAR_SAME_PRT_TMPLT_FLAG => X_GUAR_SAME_PRT_TMPLT_FLAG ,
435 X_CREATION_DATE => sysdate,
436 X_CREATED_BY => user_id,
437 X_LAST_UPDATE_DATE => sysdate,
438 X_LAST_UPDATED_BY => user_id,
439 X_LAST_UPDATE_LOGIN => 0);
440 end;
441 end LOAD_ROW;
442
443 end AR_BPA_RULES_PKG;