[Home] [Help]
PACKAGE BODY: APPS.BNE_DUPLICATES_UTILS
Source
1 PACKAGE BODY BNE_DUPLICATES_UTILS AS
2 /* $Header: bneduputilsb.pls 120.2 2005/06/29 03:39:54 dvayro noship $ */
3
4 PROCEDURE VALIDATE_KEYS
5 (p_application_id IN NUMBER,
6 p_code IN VARCHAR2
7 )
8 IS
9 BEGIN
10 IF NOT BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(p_application_id) THEN
11 RAISE_APPLICATION_ERROR(-20000, TO_CHAR(p_application_id)||' is not a valid application id');
12 END IF;
13 IF NOT BNE_INTEGRATOR_UTILS.IS_VALID_OBJECT_CODE(p_code) THEN
14 RAISE_APPLICATION_ERROR(-20001, p_code||' is not a valid code. Use A-Z, 0-9, _');
15 END IF;
16 END VALIDATE_KEYS;
17
18
19 PROCEDURE VALIDATE_INTERFACE_KEY
20 (p_application_id IN NUMBER,
21 p_interface_code IN VARCHAR2
22 )
23 IS
24 l_interface_exist NUMBER;
25 BEGIN
26 VALIDATE_KEYS(p_application_id, p_interface_code);
27 -- Check the interface code exists
28 SELECT COUNT(*) INTO l_interface_exist
29 FROM BNE_INTERFACES_B
30 WHERE APPLICATION_ID = p_application_id
31 AND INTERFACE_CODE = p_interface_code;
32
33 IF l_interface_exist = 0 THEN
34 RAISE_APPLICATION_ERROR(-20002, p_application_id||':'||p_interface_code||' is not a valid interface key.');
35 END IF;
36 END VALIDATE_INTERFACE_KEY;
37
38
39 PROCEDURE VALIDATE_INTEGRATOR_KEY
40 (p_application_id IN NUMBER,
41 p_integrator_code IN VARCHAR2
42 )
43 IS
44 l_integrator_exist NUMBER;
45 BEGIN
46 VALIDATE_KEYS(p_application_id, p_integrator_code);
47 -- Check the interface code exists
48 SELECT COUNT(*) INTO l_integrator_exist
49 FROM BNE_INTEGRATORS_B
50 WHERE APPLICATION_ID = p_application_id
51 AND INTEGRATOR_CODE = p_integrator_code;
52
53 IF l_integrator_exist = 0 THEN
54 RAISE_APPLICATION_ERROR(-20002, p_application_id||':'||p_integrator_code||' is not a valid integrator key.');
55 END IF;
56
57 END VALIDATE_INTEGRATOR_KEY;
58
59
60 PROCEDURE VALIDATE_DUP_PROFILE_KEY
61 (p_application_id IN NUMBER,
62 p_dup_profile_code IN VARCHAR2
63 )
64 IS
65 l_dup_profile_exist NUMBER;
66 BEGIN
67 VALIDATE_KEYS(p_application_id, p_dup_profile_code);
68 -- Check the interface code exists
69 SELECT COUNT(*) INTO l_dup_profile_exist
70 FROM BNE_DUPLICATE_PROFILES_B
71 WHERE APPLICATION_ID = p_application_id
72 AND DUP_PROFILE_CODE = p_dup_profile_code;
73
74 IF l_dup_profile_exist = 0 THEN
75 RAISE_APPLICATION_ERROR(-20002, p_application_id||':'||p_dup_profile_code||' is not a valid duplicate profile key.');
76 END IF;
77
78 END VALIDATE_DUP_PROFILE_KEY;
79
80
81 FUNCTION GET_INTERFACE_COL
82 (p_application_id IN NUMBER,
83 p_interface_code IN VARCHAR2,
84 p_interface_col_name IN VARCHAR2
85 )
86 RETURN NUMBER
87 IS
88 l_interface_col_seq_num NUMBER;
89 BEGIN
90 VALIDATE_INTERFACE_KEY(p_application_id, p_interface_code);
91 -- Check the interface column exists
92 BEGIN
93 SELECT SEQUENCE_NUM INTO l_interface_col_seq_num
94 FROM BNE_INTERFACE_COLS_B
95 WHERE APPLICATION_ID = p_application_id
96 AND INTERFACE_CODE = p_interface_code
97 AND INTERFACE_COL_NAME = p_interface_col_name;
98 EXCEPTION
99 WHEN OTHERS THEN
100 IF SQLCODE = 06512 THEN
101 l_interface_col_seq_num := NULL;
102 END IF;
103 END;
104
105 IF l_interface_col_seq_num = NULL THEN
106 RAISE_APPLICATION_ERROR(
107 -20003,
108 'Column '||p_interface_col_name||' does not exist in interface'||
109 p_application_id||':'||p_interface_code||'.');
110 END IF;
111
112 RETURN l_interface_col_seq_num;
113
114 END GET_INTERFACE_COL;
115
116
117 --------------------------------------------------------------------------------
118 -- PROCEDURE: ENABLE_DUPLICATE_DETECT --
119 -- --
120 -- DESCRIPTION: Enables duplicate detection on an interface by creating a --
121 -- default duplicate unique key for the interface. --
122 -- No Commit is done. --
123 -- Throws application error 20000 if p_application_id is invalid--
124 -- Throws application error 20001 if p_interface_code is invalid--
125 -- Throws application error 20002 if there is no interface key --
126 -- matching p_application_id:p_interface_code --
127 -- --
128 -- PARAMETERS: --
129 -- --
130 -- MODIFICATION HISTORY --
131 -- Date Username Description --
132 -- 25-May-04 CNOLAN CREATED --
133 --------------------------------------------------------------------------------
134 PROCEDURE ENABLE_DUPLICATE_DETECT
135 (p_application_id IN NUMBER,
136 p_interface_code IN VARCHAR2,
137 p_user_id IN NUMBER,
138 p_key_class IN VARCHAR2 default 'oracle.apps.bne.integrator.upload.BneTableInterfaceKey'
139 )
140 IS
141 l_rowid VARCHAR2(2000);
142 BEGIN
143 VALIDATE_INTERFACE_KEY(p_application_id, p_interface_code);
144 BNE_INTERFACE_KEYS_PKG.INSERT_ROW(
145 X_ROWID => l_rowid,
146 X_APPLICATION_ID => p_application_id,
147 X_KEY_CODE => p_interface_code||'_U1',
148 X_OBJECT_VERSION_NUMBER => 1,
149 X_INTERFACE_APP_ID => p_application_id,
150 X_INTERFACE_CODE => p_interface_code,
151 X_KEY_TYPE => 'DUP_UNIQUE',
152 X_KEY_CLASS => p_key_class,
153 X_CREATED_BY => p_user_id,
154 X_CREATION_DATE => SYSDATE,
155 X_LAST_UPDATED_BY => p_user_id,
156 X_LAST_UPDATE_LOGIN => 0,
157 X_LAST_UPDATE_DATE => SYSDATE);
158
159 END ENABLE_DUPLICATE_DETECT;
160
161
162 --------------------------------------------------------------------------------
163 -- PROCEDURE: ADD_COLUMN_TO_DUPLICATE_KEY --
164 -- --
165 -- DESCRIPTION: Adds a single column to an already existing duplicate unique --
166 -- key. --
167 -- No Commit is done. --
168 -- Throws application error 20000 if p_application_id is invalid--
169 -- Throws application error 20001 if p_interface_code is invalid--
170 -- Throws application error 20002 if there is no interface key --
171 -- matching p_interface_app_id:p_interface_code. --
172 -- Throws application error 20003 if there is no column in the --
173 -- supplied interface with name p_interface_col_name --
174 -- --
175 -- PARAMETERS: --
176 -- --
177 -- MODIFICATION HISTORY --
178 -- Date Username Description --
179 -- 25-May-04 CNOLAN CREATED --
180 --------------------------------------------------------------------------------
181 PROCEDURE ADD_COLUMN_TO_DUPLICATE_KEY
182 (p_application_id IN NUMBER,
183 p_interface_code IN VARCHAR2,
184 p_interface_col_name IN VARCHAR2,
185 p_user_id IN NUMBER
186 )
187 IS
188 l_interface_col_seq_num NUMBER;
189 l_sequence_num NUMBER;
190 l_rowid VARCHAR2(2000);
191 BEGIN
192 l_interface_col_seq_num := GET_INTERFACE_COL(p_application_id, p_interface_code, p_interface_col_name);
193
194 SELECT NVL((MAX(SEQUENCE_NUM) + 1), 1) INTO l_sequence_num
195 FROM BNE_INTERFACE_KEY_COLS
196 WHERE INTERFACE_APP_ID = p_application_id
197 AND INTERFACE_CODE = p_interface_code;
198
199 BNE_INTERFACE_KEY_COLS_PKG.INSERT_ROW(
200 X_ROWID => l_rowid,
201 X_APPLICATION_ID => p_application_id,
202 X_KEY_CODE => p_interface_code||'_U1',
203 X_SEQUENCE_NUM => l_sequence_num,
204 X_OBJECT_VERSION_NUMBER => 1,
205 X_INTERFACE_APP_ID => p_application_id,
206 X_INTERFACE_CODE => p_interface_code,
207 X_INTERFACE_SEQ_NUM => l_interface_col_seq_num,
208 X_CREATED_BY => p_user_id,
209 X_CREATION_DATE => SYSDATE,
210 X_LAST_UPDATED_BY => p_user_id,
211 X_LAST_UPDATE_LOGIN => 0,
212 X_LAST_UPDATE_DATE => SYSDATE);
213
214 END ADD_COLUMN_TO_DUPLICATE_KEY;
215
216
217 --------------------------------------------------------------------------------
218 -- PROCEDURE: CREATE_DUPLICATE_PROFILE --
219 -- --
220 -- DESCRIPTION: Creates a new duplicate profile for the specified integrator,--
221 -- and creates duplicate interface profiles for all interfaces --
222 -- defined in the integrator. --
223 -- No Commit is done. --
224 -- Throws application error 20000 if p_integrator_app_id is --
225 -- invalid --
226 -- Throws application error 20001 if p_integrator_code is --
227 -- invalid --
228 -- Throws application error 20002 if there is no integrator key --
229 -- matching p_integrator_id:p_integrator_code --
230 -- --
231 -- PARAMETERS: --
232 -- --
233 -- MODIFICATION HISTORY --
234 -- Date Username Description --
235 -- 25-May-04 CNOLAN CREATED --
236 --------------------------------------------------------------------------------
237 PROCEDURE CREATE_DUPLICATE_PROFILE
238 (p_integrator_app_id IN NUMBER,
239 p_integrator_code IN VARCHAR2,
240 p_dup_profile_app_id IN NUMBER,
241 p_dup_profile_code IN VARCHAR2,
242 p_user_name IN VARCHAR2,
243 p_dup_handling_code IN VARCHAR2,
244 p_default_resolver_classname IN VARCHAR2,
245 p_user_id IN NUMBER
246 )
247 IS
248 CURSOR x_interface_cur(p_app_id IN NUMBER, p_code IN VARCHAR2) IS
249 SELECT application_id, interface_code
250 FROM bne_interfaces_b
251 WHERE integrator_app_id = P_APP_ID
252 AND integrator_code = P_CODE;
253
254 l_rowid VARCHAR2(2000);
255
256 BEGIN
257 VALIDATE_INTEGRATOR_KEY(p_integrator_app_id, p_integrator_code);
258
259 -- Valid key, create the profile
260 BNE_DUPLICATE_PROFILES_PKG.INSERT_ROW(
261 X_ROWID => l_rowid,
262 X_APPLICATION_ID => p_dup_profile_app_id,
263 X_DUP_PROFILE_CODE => P_dup_profile_code,
264 X_OBJECT_VERSION_NUMBER => 1,
265 X_INTEGRATOR_APP_ID => p_integrator_app_id,
266 X_INTEGRATOR_CODE => p_integrator_code,
267 X_USER_NAME => p_user_name,
268 X_CREATED_BY => p_user_id,
269 X_CREATION_DATE => SYSDATE,
270 X_LAST_UPDATED_BY => p_user_id,
271 X_LAST_UPDATE_LOGIN => 0,
272 X_LAST_UPDATE_DATE => SYSDATE);
273
274 -- Get all the interfaces for this integrator and create a duplicate
275 -- interface profile for each
276 FOR interface_rec IN x_interface_cur(p_integrator_app_id, p_integrator_code)
277 LOOP
278 BNE_DUP_INTERFACE_PROFILES_PKG.INSERT_ROW(
279 X_ROWID => l_rowid,
280 X_INTERFACE_APP_ID => interface_rec.application_id,
281 X_INTERFACE_CODE => interface_rec.interface_code,
282 X_DUP_PROFILE_APP_ID => p_dup_profile_app_id,
283 X_DUP_PROFILE_CODE => p_dup_profile_code,
284 X_OBJECT_VERSION_NUMBER => 1,
285 X_DUP_HANDLING_CODE => p_dup_handling_code,
286 X_DEFAULT_RESOLVER_CLASSNAME => p_default_resolver_classname,
287 X_CREATED_BY => p_user_id,
288 X_CREATION_DATE => SYSDATE,
289 X_LAST_UPDATED_BY => p_user_id,
290 X_LAST_UPDATE_LOGIN => 0,
291 X_LAST_UPDATE_DATE => SYSDATE);
292 END LOOP;
293
294 END CREATE_DUPLICATE_PROFILE;
295
296
297 --------------------------------------------------------------------------------
298 -- PROCEDURE: SET_DUPLICATE_RESOLVER --
299 -- --
300 -- DESCRIPTION: Sets an explicitly named resolver for a particular interface --
301 -- column for a single duplicate profile. --
302 -- No Commit is done. --
303 -- Throws application error 20000 if either p_interface_app_id --
304 -- or p_dup_profile_app_id are invalid. --
305 -- Throws application error 20001 if either p_interface_code or --
306 -- p_dup_profile_code are invalid. --
307 -- Throws application error 20002 if there is no interface key --
308 -- matching p_interface_app_id:p_interface_code or there is --
309 -- no duplicate profile key matching --
310 -- p_dup_profile_app_id:p_dup_profile_code --
311 -- Throws application error 20003 if there is no column in the --
312 -- supplied interface with name p_interface_col_name --
313 -- --
314 -- PARAMETERS: --
315 -- --
316 -- MODIFICATION HISTORY --
317 -- Date Username Description --
321 (p_interface_app_id IN NUMBER,
318 -- 25-May-04 CNOLAN CREATED --
319 --------------------------------------------------------------------------------
320 PROCEDURE SET_DUPLICATE_RESOLVER
322 p_interface_code IN VARCHAR2,
323 p_interface_col_name IN VARCHAR2,
324 p_dup_profile_app_id IN NUMBER,
325 p_dup_profile_code IN VARCHAR2,
326 p_resolver_classname IN VARCHAR2,
327 p_user_id IN NUMBER
328 )
329 IS
330 l_interface_col_seq_num NUMBER;
331 l_object_version_number NUMBER;
332 l_rowid VARCHAR2(2000);
333
334 BEGIN
335 VALIDATE_DUP_PROFILE_KEY(p_dup_profile_app_id, p_dup_profile_code);
336 l_interface_col_seq_num := GET_INTERFACE_COL(p_interface_app_id, p_interface_code, p_interface_col_name);
337
338 BEGIN
339 SELECT OBJECT_VERSION_NUMBER INTO l_object_version_number
340 FROM BNE_DUP_INTERFACE_COLS
341 WHERE INTERFACE_APP_ID = p_interface_app_id
342 AND INTERFACE_CODE = p_interface_code
343 AND DUP_PROFILE_APP_ID = p_dup_profile_app_id
344 AND DUP_PROFILE_CODE = p_dup_profile_code
345 AND INTERFACE_SEQ_NUM = l_interface_col_seq_num;
346 EXCEPTION
347 WHEN OTHERS THEN
348 IF SQLCODE = 06512 THEN
349 l_object_version_number := 0;
350 END IF;
351 END;
352
353 IF l_object_version_number > 0 THEN
354 BNE_DUP_INTERFACE_COLS_PKG.UPDATE_ROW(
355 X_INTERFACE_APP_ID => p_interface_app_id,
356 X_INTERFACE_CODE => p_interface_code,
357 X_DUP_PROFILE_APP_ID => p_dup_profile_app_id,
358 X_DUP_PROFILE_CODE => p_dup_profile_code,
359 X_INTERFACE_SEQ_NUM => l_interface_col_seq_num,
360 X_OBJECT_VERSION_NUMBER => l_object_version_number + 1,
361 X_RESOLVER_CLASSNAME => p_resolver_classname,
362 X_LAST_UPDATED_BY => p_user_id,
363 X_LAST_UPDATE_LOGIN => 0,
364 X_LAST_UPDATE_DATE => SYSDATE);
365 ELSE
366 BNE_DUP_INTERFACE_COLS_PKG.INSERT_ROW(
367 X_ROWID => l_rowid,
368 X_INTERFACE_APP_ID => p_interface_app_id,
369 X_INTERFACE_CODE => p_interface_code,
370 X_DUP_PROFILE_APP_ID => p_dup_profile_app_id,
371 X_DUP_PROFILE_CODE => p_dup_profile_code,
372 X_INTERFACE_SEQ_NUM => l_interface_col_seq_num,
373 X_OBJECT_VERSION_NUMBER => 1,
374 X_RESOLVER_CLASSNAME => p_resolver_classname,
375 X_CREATED_BY => p_user_id,
376 X_CREATION_DATE => SYSDATE,
377 X_LAST_UPDATED_BY => p_user_id,
378 X_LAST_UPDATE_LOGIN => 0,
379 X_LAST_UPDATE_DATE => SYSDATE);
380 END IF;
381
382 END SET_DUPLICATE_RESOLVER;
383
384
385 --------------------------------------------------------------------------------
386 -- PROCEDURE: DELETE_DUPLICATE_PROFILE --
387 -- --
388 -- DESCRIPTION: Deletes the duplicate profile by removing all duplicate --
389 -- interface column entries, all duplicate interface profiles --
390 -- entries and all duplicate profile entries from the --
391 -- appropriate tables. --
392 -- --
393 -- PARAMETERS: --
394 -- --
395 -- MODIFICATION HISTORY --
396 -- Date Username Description --
397 -- 25-May-04 CNOLAN CREATED --
398 --------------------------------------------------------------------------------
399 PROCEDURE DELETE_DUPLICATE_PROFILE
400 (p_dup_profile_app_id IN NUMBER,
401 p_dup_profile_code IN VARCHAR2
402 )
403 IS
404 BEGIN
405 VALIDATE_DUP_PROFILE_KEY(p_dup_profile_app_id, p_dup_profile_code);
406
407 DELETE FROM BNE_DUP_INTERFACE_COLS
408 WHERE DUP_PROFILE_APP_ID = p_dup_profile_app_id
409 AND DUP_PROFILE_CODE = p_dup_profile_code;
410
411 DELETE FROM BNE_DUP_INTERFACE_PROFILES
412 WHERE DUP_PROFILE_APP_ID = p_dup_profile_app_id
413 AND DUP_PROFILE_CODE = p_dup_profile_code;
414
415 BNE_DUPLICATE_PROFILES_PKG.DELETE_ROW(
416 X_APPLICATION_ID => p_dup_profile_app_id,
417 X_DUP_PROFILE_CODE => p_dup_profile_code);
418
419 END DELETE_DUPLICATE_PROFILE;
420
421
422 --------------------------------------------------------------------------------
423 -- PROCEDURE: REMOVE_DUPLICATE_DETECT --
424 -- --
425 -- DESCRIPTION: Disables duplicate detection by deleting duplicate interface --
426 -- keys and respective key columns from the appropriate tables. --
427 -- --
428 -- PARAMETERS: --
429 -- --
430 -- MODIFICATION HISTORY --
431 -- Date Username Description --
432 -- 25-May-04 CNOLAN CREATED --
433 --------------------------------------------------------------------------------
434 PROCEDURE REMOVE_DUPLICATE_DETECT
438 IS
435 (p_interface_app_id IN NUMBER,
436 p_interface_code IN VARCHAR2
437 )
439 BEGIN
440 VALIDATE_INTERFACE_KEY(p_interface_app_id, p_interface_code);
441
442 DELETE FROM BNE_INTERFACE_KEY_COLS
443 WHERE INTERFACE_APP_ID = p_interface_app_id
444 AND INTERFACE_CODE = p_interface_code;
445
446 DELETE FROM BNE_INTERFACE_KEYS
447 WHERE INTERFACE_APP_ID = p_interface_app_id
448 AND INTERFACE_CODE = p_interface_code;
449
450 END REMOVE_DUPLICATE_DETECT;
451
452
453 END BNE_DUPLICATES_UTILS;