[Home] [Help]
PACKAGE BODY: APPS.BNE_CONTENT_UTILS
Source
1 PACKAGE BODY BNE_CONTENT_UTILS AS
2 /* $Header: bnecontb.pls 120.3 2005/07/27 03:17:22 dagroves noship $ */
3 --------------------------------------------------------------------------------
4 -- PACKAGE: BNE_CONTENT_UTILS --
5 -- --
6 -- DESCRIPTION: --
7 -- --
8 -- MODIFICATION HISTORY --
9 -- Date Username Description --
10 -- 05-JUN-2002 KPEET Created --
11 -- 16-JUL-2002 KPEET Changed CREATE_CONTENT_COLS_FROM_VIEW to select --
12 -- from ALL_TAB_COLUMNS instead of FND_VIEWS and --
13 -- FND_VIEW_COLUMNS as custom views are not created --
14 -- in the FND tables. --
15 -- 22-JUL-2002 KPEET Removed reference to REPORTING_INTERFACE_ID in --
16 -- from insert into BNE_TEXT_IMPORT_DETAILS in --
17 -- procedure CREATE_REPORTING_MAPPING. --
18 -- 13-AUG-2002 KPEET Removed all references to DBMS_OUTPUT for GSCC --
19 -- Compliance. --
20 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
21 -- 04-NOV-2002 KPEET Added procedure ASSIGN_PARAM_LIST_TO_CONTENT. --
22 -- 11-NOV-2002 KPEET Updated procedure CREATE_CONTENT_TEXT. --
23 --------------------------------------------------------------------------------
24
25
26 --------------------------------------------------------------------------------
27 -- PROCEDURE: UPSERT_CONTENT_COL --
28 -- --
29 -- DESCRIPTION: Procedure inserts or updates a single column in the --
30 -- BNE_CONTENT_COLS_B/_TL table. --
31 -- This procedure will only update the COL_NAME, --
32 -- and USER_NAME column values. --
33 -- The column to be inserted/updated will be determined by --
34 -- the APPLICATION_ID, CONTENT_CODE and SEQUENCE_NUM --
35 -- passed to this procedure. --
36 -- --
37 -- MODIFICATION HISTORY --
38 -- Date Username Description --
39 -- 16-JUN-2002 KPEET CREATED --
40 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
41 -- 27-JUL-2004 DAGROVES Added P_READ_ONLY_FLAG --
42 --------------------------------------------------------------------------------
43 PROCEDURE UPSERT_CONTENT_COL(P_APPLICATION_ID IN NUMBER,
44 P_CONTENT_CODE IN VARCHAR2,
45 P_SEQUENCE_NUM IN NUMBER,
46 P_COL_NAME IN VARCHAR2,
47 P_LANGUAGE IN VARCHAR2,
48 P_SOURCE_LANGUAGE IN VARCHAR2,
49 P_DESCRIPTION IN VARCHAR2,
50 P_USER_ID IN NUMBER,
51 P_READ_ONLY_FLAG IN VARCHAR2)
52 IS
53 VN_NO_RECORD_FLAG NUMBER;
54 BEGIN
55 -- Check the BNE_CONTENT_COLS_B table to ensure that the Content Column
56 -- does not already exist
57
58 VN_NO_RECORD_FLAG := 0;
59
60 BEGIN
61 SELECT 1
62 INTO VN_NO_RECORD_FLAG
63 FROM BNE_CONTENT_COLS_B
64 WHERE APPLICATION_ID = P_APPLICATION_ID
65 AND CONTENT_CODE = P_CONTENT_CODE
66 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN NULL;
69 END;
70
71 -- If the Content Column was not found then insert it
72
73 IF (VN_NO_RECORD_FLAG = 0) THEN
74
75 -- Insert the required row in BNE_CONTENT_COLS_B
76
77 INSERT INTO BNE_CONTENT_COLS_B
78 (APPLICATION_ID, CONTENT_CODE, SEQUENCE_NUM, OBJECT_VERSION_NUMBER, COL_NAME,
79 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
80 READ_ONLY_FLAG)
81 VALUES
82 (P_APPLICATION_ID, P_CONTENT_CODE, P_SEQUENCE_NUM, 1, P_COL_NAME,
83 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE, P_USER_ID,
84 P_READ_ONLY_FLAG);
85
86 -- Insert the required row in BNE_CONTENT_COLS_TL only where P_LANGUAGE is populated
87
88 IF (P_LANGUAGE IS NOT NULL) THEN
89
90 INSERT INTO BNE_CONTENT_COLS_TL
91 (APPLICATION_ID, CONTENT_CODE, SEQUENCE_NUM, LANGUAGE, SOURCE_LANG, USER_NAME,
92 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
93 VALUES
94 (P_APPLICATION_ID, P_CONTENT_CODE, P_SEQUENCE_NUM, P_LANGUAGE, P_SOURCE_LANGUAGE, P_DESCRIPTION,
95 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE, P_USER_ID);
96
97 END IF;
98 ELSE
99 -- Update the required row in BNE_CONTENT_COLS_B
100
101 UPDATE BNE_CONTENT_COLS_B
102 SET OBJECT_VERSION_NUMBER = (OBJECT_VERSION_NUMBER + 1),
103 COL_NAME = P_COL_NAME,
104 LAST_UPDATED_BY = P_USER_ID,
105 LAST_UPDATE_DATE = SYSDATE,
106 LAST_UPDATE_LOGIN = P_USER_ID,
107 READ_ONLY_FLAG = P_READ_ONLY_FLAG
108 WHERE APPLICATION_ID = P_APPLICATION_ID
109 AND CONTENT_CODE = P_CONTENT_CODE
110 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
111
112 -- Update the required row in BNE_CONTENT_COLS_TL ONLY WHERE P_LANGUAGE POPULATED
113
114 IF (P_LANGUAGE IS NOT NULL) THEN
115
116 UPDATE BNE_CONTENT_COLS_TL
117 SET USER_NAME = P_DESCRIPTION,
118 LAST_UPDATED_BY = P_USER_ID,
119 LAST_UPDATE_DATE = SYSDATE,
120 LAST_UPDATE_LOGIN = P_USER_ID
121 WHERE APPLICATION_ID = P_APPLICATION_ID
122 AND CONTENT_CODE = P_CONTENT_CODE
123 AND P_SEQUENCE_NUM = P_SEQUENCE_NUM
124 AND LANGUAGE = P_LANGUAGE
125 AND SOURCE_LANG = P_SOURCE_LANGUAGE;
126
127 END IF;
128
129 END IF;
130
131 END UPSERT_CONTENT_COL;
132
133
134 --------------------------------------------------------------------------------
135 -- PROCEDURE: CREATE_CONTENT --
136 -- --
137 -- DESCRIPTION: Creates the Content Object and Associates it with --
138 -- the supplied Integrator. --
139 -- --
140 -- MODIFICATION HISTORY --
141 -- Date Username Description --
142 -- 22-APR-2002 JRICHARD CREATED --
143 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
144 --------------------------------------------------------------------------------
145 PROCEDURE CREATE_CONTENT(P_APPLICATION_ID IN NUMBER,
146 P_OBJECT_CODE IN VARCHAR2,
147 P_INTEGRATOR_CODE IN VARCHAR2,
148 P_DESCRIPTION IN VARCHAR2,
149 P_LANGUAGE IN VARCHAR2,
150 P_SOURCE_LANGUAGE IN VARCHAR2,
151 P_CONTENT_CLASS IN VARCHAR2,
152 P_USER_ID IN NUMBER,
153 P_CONTENT_CODE OUT NOCOPY VARCHAR2,
154 P_ONCE_ONLY_DOWNLOAD_FLAG IN VARCHAR2 DEFAULT 'N')
155 IS
156 VV_CONTENT_CODE BNE_CONTENTS_B.CONTENT_CODE%TYPE;
157
158 BEGIN
159
160 -- Only create the Content if the OBJECT_CODE supplied is VALID.
161
162 IF BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(P_APPLICATION_ID) AND
163 BNE_INTEGRATOR_UTILS.IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
164
165 -- Check to see if the Content has already been created/seeded
166 -- for P_APPLICATION_ID with the same CONTENT_CODE.
167
168 -- *** NOTE: This DOES NOT check the Content Name in the USER_NAME column in the TL table.
169
170 VV_CONTENT_CODE := NULL;
171 P_CONTENT_CODE := P_OBJECT_CODE||'_CNT';
172
173 BEGIN
174 SELECT CONTENT_CODE
175 INTO VV_CONTENT_CODE
176 FROM BNE_CONTENTS_B
177 WHERE APPLICATION_ID = P_APPLICATION_ID
178 AND CONTENT_CODE = P_CONTENT_CODE
179 AND INTEGRATOR_APP_ID = P_APPLICATION_ID
180 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN NULL;
183 END;
184
185 -- If this is a new Content, create it, otherwise Update it.
186
187 IF (VV_CONTENT_CODE IS NULL) THEN
188
189 -- Insert a new record into BNE_CONTENTS_B
190
191 INSERT INTO BNE_CONTENTS_B
192 (APPLICATION_ID, CONTENT_CODE, INTEGRATOR_APP_ID, INTEGRATOR_CODE, OBJECT_VERSION_NUMBER,
193 CONTENT_CLASS, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, ONCE_ONLY_DOWNLOAD_FLAG)
194 VALUES
195 (P_APPLICATION_ID, P_CONTENT_CODE, P_APPLICATION_ID, P_INTEGRATOR_CODE, 1,
196 P_CONTENT_CLASS, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE, P_ONCE_ONLY_DOWNLOAD_FLAG);
197
198 -- Insert a new record into BNE_CONTENTS_TL
199
200 INSERT INTO BNE_CONTENTS_TL
201 (APPLICATION_ID, CONTENT_CODE, LANGUAGE, SOURCE_LANG, USER_NAME, CREATED_BY, CREATION_DATE,
202 LAST_UPDATED_BY, LAST_UPDATE_DATE)
203 VALUES
204 (P_APPLICATION_ID, P_CONTENT_CODE, P_LANGUAGE, P_SOURCE_LANGUAGE, P_DESCRIPTION, P_USER_ID, SYSDATE,
205 P_USER_ID, SYSDATE);
206 ELSE
207 -- Update table BNE_CONTENTS_B
208
209 UPDATE BNE_CONTENTS_B
210 SET OBJECT_VERSION_NUMBER = (OBJECT_VERSION_NUMBER + 1),
211 CONTENT_CLASS = P_CONTENT_CLASS,
212 LAST_UPDATED_BY = P_USER_ID,
213 LAST_UPDATE_DATE = SYSDATE
214 WHERE APPLICATION_ID = P_APPLICATION_ID
215 AND CONTENT_CODE = P_CONTENT_CODE;
216
217 -- Update table BNE_CONTENTS_TL
218
219 UPDATE BNE_CONTENTS_TL
220 SET LANGUAGE = P_LANGUAGE,
221 SOURCE_LANG = P_SOURCE_LANGUAGE,
222 USER_NAME = P_DESCRIPTION,
223 LAST_UPDATED_BY = P_USER_ID,
224 LAST_UPDATE_DATE = SYSDATE
225 WHERE APPLICATION_ID = P_APPLICATION_ID
226 AND CONTENT_CODE = P_CONTENT_CODE;
227 END IF;
228 ELSE
229 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
230 END IF;
231 END CREATE_CONTENT;
232
233 --------------------------------------------------------------------------------
234 -- PROCEDURE: CREATE_CONTENT_TEXT --
235 -- --
236 -- DESCRIPTION: Inserts or updates records in the BNE_CONTENTS_B/TL --
237 -- tables and inserts records into the --
238 -- BNE_CONTENT_COLS_B/TL tables if columns do not --
239 -- already exist for the APPLICATION_ID and --
240 -- CONTENT_CODE. --
241 -- --
242 -- MODIFICATION HISTORY --
243 -- Date Username Description --
244 -- 16-JUN-2002 KPEET CREATED --
245 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
246 -- 11-NOV-2002 KPEET Updated by removing IN parameters: --
247 -- P_PARAM_LIST_APP_ID and P_PARAM_LIST_CODE and --
248 -- setting the Text File parameter list to the --
249 -- Web ADI Parameter list: 'CONT_TEXT_FILE1'. --
250 --------------------------------------------------------------------------------
251 PROCEDURE CREATE_CONTENT_TEXT (P_APPLICATION_ID IN NUMBER,
252 P_OBJECT_CODE IN VARCHAR2,
253 P_INTEGRATOR_CODE IN VARCHAR2,
254 P_CONTENT_DESC IN VARCHAR2,
255 P_NO_OF_COLS IN NUMBER,
256 P_COL_PREFIX IN VARCHAR2,
257 P_LANGUAGE IN VARCHAR2,
258 P_SOURCE_LANGUAGE IN VARCHAR2,
259 P_USER_ID IN NUMBER,
260 P_CONTENT_CODE OUT NOCOPY VARCHAR2)
261 IS
262 VV_CONTENT_CODE VARCHAR2(30);
263 VN_COL_NUM NUMBER;
264 VV_COL_NAME VARCHAR2(240);
265 VN_PARAM_LIST_APP_ID NUMBER;
266 VV_PARAM_LIST_CODE VARCHAR2(30);
267 BEGIN
268 P_CONTENT_CODE := NULL;
269 VN_PARAM_LIST_APP_ID := 231;
270 VV_PARAM_LIST_CODE := 'CONT_TEXT_FILE1';
271
272 -- Create or update the record in the BNE_CONTENTS_B/TL tables
273
274 CREATE_CONTENT(P_APPLICATION_ID,
275 P_OBJECT_CODE,
276 P_INTEGRATOR_CODE,
277 P_CONTENT_DESC,
278 P_LANGUAGE,
279 P_SOURCE_LANGUAGE,
280 'oracle.apps.bne.webui.control.BneFileDownloadControl',
281 P_USER_ID,
282 P_CONTENT_CODE);
283
284 -- Check for existing Content Columns
285
286 VV_CONTENT_CODE := NULL;
287
288 BEGIN
289 SELECT DISTINCT A.CONTENT_CODE
290 INTO VV_CONTENT_CODE
291 FROM BNE_CONTENT_COLS_B A, BNE_CONTENT_COLS_TL B
292 WHERE A.APPLICATION_ID = B.APPLICATION_ID
293 AND A.CONTENT_CODE = B.CONTENT_CODE
294 AND A.APPLICATION_ID = P_APPLICATION_ID
295 AND A.CONTENT_CODE = P_CONTENT_CODE
296 AND B.LANGUAGE = P_LANGUAGE;
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN NULL;
299 END;
300
301 -- If this is a new Content, the create the content columns
302
303 IF ( VV_CONTENT_CODE IS NULL ) THEN
304
308 WHILE VN_COL_NUM <= P_NO_OF_COLS LOOP
305 VN_COL_NUM := 1;
306 VV_COL_NAME := NULL;
307
309
310 -- the COL_NAME consists of the column prefix and column number, e.g. Column 20
311 VV_COL_NAME := P_COL_PREFIX||' '||TO_CHAR(VN_COL_NUM);
312
313 UPSERT_CONTENT_COL(P_APPLICATION_ID,
314 P_CONTENT_CODE,
315 VN_COL_NUM,
316 TO_CHAR(VN_COL_NUM),
317 P_LANGUAGE,
318 P_SOURCE_LANGUAGE,
319 VV_COL_NAME,
320 P_USER_ID);
321
322 VN_COL_NUM := VN_COL_NUM + 1;
323
324 END LOOP;
325
326 END IF;
327
328 ASSIGN_PARAM_LIST_TO_CONTENT(P_CONTENT_APP_ID => P_APPLICATION_ID,
329 P_CONTENT_CODE => P_CONTENT_CODE,
330 P_PARAM_LIST_APP_ID => VN_PARAM_LIST_APP_ID,
331 P_PARAM_LIST_CODE => VV_PARAM_LIST_CODE);
332
333 END CREATE_CONTENT_TEXT;
334
335 --------------------------------------------------------------------------------
336 -- PROCEDURE: CREATE_CONTENT_STORED_SQL --
337 -- --
338 -- DESCRIPTION: Calls CREATE_CONTENT_DYNAMIC_SQL passing in the --
339 -- class for the Stored SQL Content Component. --
340 -- --
341 -- PARAMETERS: --
342 -- --
343 -- MODIFICATION HISTORY --
344 -- Date Username Description --
345 -- 17-JUN-2002 KPEET CREATED --
346 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
347 --------------------------------------------------------------------------------
348 PROCEDURE CREATE_CONTENT_STORED_SQL (P_APPLICATION_ID IN NUMBER,
349 P_OBJECT_CODE IN VARCHAR2,
350 P_INTEGRATOR_CODE IN VARCHAR2,
351 P_CONTENT_DESC IN VARCHAR2,
352 P_COL_LIST IN VARCHAR2,
353 P_QUERY IN VARCHAR2,
354 P_LANGUAGE IN VARCHAR2,
355 P_SOURCE_LANGUAGE IN VARCHAR2,
356 P_USER_ID IN NUMBER,
357 P_CONTENT_CODE OUT NOCOPY VARCHAR2,
358 P_ONCE_ONLY_DOWNLOAD_FLAG IN VARCHAR2 DEFAULT 'N')
359 IS
360 BEGIN
361 P_CONTENT_CODE := NULL;
362
363 CREATE_CONTENT_DYNAMIC_SQL (P_APPLICATION_ID,
364 P_OBJECT_CODE,
365 P_INTEGRATOR_CODE,
366 P_CONTENT_DESC,
367 'oracle.apps.bne.webui.control.BneStoredSQLControl',
368 P_COL_LIST,
369 P_LANGUAGE,
370 P_SOURCE_LANGUAGE,
371 P_USER_ID,
372 P_CONTENT_CODE,
373 P_ONCE_ONLY_DOWNLOAD_FLAG);
374
375 UPSERT_STORED_SQL_STATEMENT (P_APPLICATION_ID,
376 P_CONTENT_CODE,
377 P_QUERY,
378 P_USER_ID);
379
380 END CREATE_CONTENT_STORED_SQL;
381
382 --------------------------------------------------------------------------------
383 -- PROCEDURE: CREATE_CONTENT_PASSED_SQL --
384 -- --
385 -- DESCRIPTION: Calls CREATE_CONTENT_DYNAMIC_SQL passing in the --
386 -- class for the Passed SQL Content Component. --
387 -- --
388 -- PARAMETERS: --
389 -- --
390 -- MODIFICATION HISTORY --
391 -- Date Username Description --
392 -- 17-JUN-2002 KPEET CREATED --
393 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
394 --------------------------------------------------------------------------------
395 PROCEDURE CREATE_CONTENT_PASSED_SQL (P_APPLICATION_ID IN NUMBER,
396 P_OBJECT_CODE IN VARCHAR2,
397 P_INTEGRATOR_CODE IN VARCHAR2,
398 P_CONTENT_DESC IN VARCHAR2,
399 P_COL_LIST IN VARCHAR2,
400 P_LANGUAGE IN VARCHAR2,
401 P_SOURCE_LANGUAGE IN VARCHAR2,
402 P_USER_ID IN NUMBER,
403 P_CONTENT_CODE OUT NOCOPY VARCHAR2,
407 P_CONTENT_CODE := NULL;
404 P_ONCE_ONLY_DOWNLOAD_FLAG IN VARCHAR2 DEFAULT 'N')
405 IS
406 BEGIN
408
409 CREATE_CONTENT_DYNAMIC_SQL (P_APPLICATION_ID,
410 P_OBJECT_CODE,
411 P_INTEGRATOR_CODE,
412 P_CONTENT_DESC,
413 'oracle.apps.bne.webui.control.BnePassedSQLControl',
414 P_COL_LIST,
415 P_LANGUAGE,
416 P_SOURCE_LANGUAGE,
417 P_USER_ID,
418 P_CONTENT_CODE,
419 P_ONCE_ONLY_DOWNLOAD_FLAG);
420
421 END CREATE_CONTENT_PASSED_SQL;
422
423 --------------------------------------------------------------------------------
424 -- PROCEDURE: CREATE_CONTENT_DYNAMIC_SQL --
425 -- --
426 -- DESCRIPTION: Inserts or updates records in the BNE_CONTENTS_B/TL --
427 -- tables and inserts records into the --
428 -- BNE_CONTENT_COLS_B/TL tables from a comma delimited --
429 -- list of columns. Columns are only inserted if they --
430 -- do not already exist for the APPLICATION_ID and --
431 -- CONTENT_CODE. --
432 -- --
433 -- Columns are not updated by this procedure, use --
434 -- UPSERT_CONTENT_COL to update columns. --
435 -- --
436 -- PARAMETERS: --
437 -- --
438 -- MODIFICATION HISTORY --
439 -- Date Username Description --
440 -- 16-JUN-2002 KPEET CREATED --
441 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
442 --------------------------------------------------------------------------------
443 PROCEDURE CREATE_CONTENT_DYNAMIC_SQL (P_APPLICATION_ID IN NUMBER,
444 P_OBJECT_CODE IN VARCHAR2,
445 P_INTEGRATOR_CODE IN VARCHAR2,
446 P_CONTENT_DESC IN VARCHAR2,
447 P_CONTENT_CLASS IN VARCHAR2,
448 P_COL_LIST IN VARCHAR2,
449 P_LANGUAGE IN VARCHAR2,
450 P_SOURCE_LANGUAGE IN VARCHAR2,
451 P_USER_ID IN NUMBER,
452 P_CONTENT_CODE OUT NOCOPY VARCHAR2,
453 P_ONCE_ONLY_DOWNLOAD_FLAG IN VARCHAR2 DEFAULT 'N')
454 IS
455 VV_CONTENT_CODE BNE_CONTENTS_B.CONTENT_CODE%TYPE;
456 VN_COL_NUM NUMBER;
457 VN_CURR_POS NUMBER;
458 VN_PREV_POS NUMBER;
459 VN_START_POS NUMBER;
460 VN_LIST_LENGTH NUMBER;
461 VV_COL_NAME VARCHAR2(240);
462
463 BEGIN
464 P_CONTENT_CODE := NULL;
465
466 -- Create or update the record in the BNE_CONTENTS_B/TL tables
467
468 CREATE_CONTENT(P_APPLICATION_ID,
469 P_OBJECT_CODE,
470 P_INTEGRATOR_CODE,
471 P_CONTENT_DESC,
472 P_LANGUAGE,
473 P_SOURCE_LANGUAGE,
474 P_CONTENT_CLASS,
475 P_USER_ID,
476 P_CONTENT_CODE,
477 P_ONCE_ONLY_DOWNLOAD_FLAG);
478
479 -- Check for existing Content columns
480
481 VV_CONTENT_CODE := NULL;
482
483 BEGIN
484 SELECT DISTINCT A.CONTENT_CODE
485 INTO VV_CONTENT_CODE
486 FROM BNE_CONTENT_COLS_B A, BNE_CONTENT_COLS_TL B
487 WHERE A.APPLICATION_ID = B.APPLICATION_ID
488 AND A.CONTENT_CODE = B.CONTENT_CODE
489 AND A.APPLICATION_ID = P_APPLICATION_ID
490 AND A.CONTENT_CODE = P_CONTENT_CODE
491 AND B.LANGUAGE = P_LANGUAGE;
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN NULL;
494 END;
495
496 -- If new Content, then create Content columns
497
498 IF ( VV_CONTENT_CODE IS NULL ) THEN
499
500 VN_COL_NUM := 1;
501 VN_CURR_POS := 1;
502 VN_PREV_POS := 0;
503 VN_START_POS := 1;
504 VN_LIST_LENGTH := 0;
505 VV_COL_NAME := NULL;
506
507 -- determine the length of the comma delimited list of columns
508
509 SELECT LENGTH(P_COL_LIST)
510 INTO VN_LIST_LENGTH
511 FROM SYS.DUAL;
512
513 WHILE VN_START_POS <= VN_LIST_LENGTH LOOP
514
515 -- find the position of the next comma delimiter in the column list
516
517 SELECT INSTR(P_COL_LIST, ',', VN_START_POS)
518 INTO VN_CURR_POS
519 FROM SYS.DUAL;
520
521 -- If there are no more comma delimiters, set the current position
522 -- to be the greater than the list length - so the loop will terminate
523
524 IF (VN_CURR_POS = 0) THEN
528 -- get the column name and trim all spaces from the left and right of the string
525 VN_CURR_POS := VN_LIST_LENGTH + 1;
526 END IF;
527
529
530 SELECT TRIM(' ' FROM (SUBSTR(P_COL_LIST, VN_START_POS, (VN_CURR_POS - VN_PREV_POS - 1))))
531 INTO VV_COL_NAME
532 FROM SYS.DUAL;
533
534 -- insert the column into BNE_CONTENT_COLS_B and BNE_CONTENT_COLS_TL
535
536 UPSERT_CONTENT_COL(P_APPLICATION_ID,
537 P_CONTENT_CODE,
538 VN_COL_NUM,
539 VV_COL_NAME,
540 P_LANGUAGE,
541 P_SOURCE_LANGUAGE,
542 VV_COL_NAME,
543 P_USER_ID);
544
545 VN_COL_NUM := VN_COL_NUM + 1;
546
547 -- set the start position to be the first position after the last comma delimiter found
548
549 VN_START_POS := VN_CURR_POS+1;
550
551 -- set the previous position to be the position of the last comma delimiter found
552
553 VN_PREV_POS := VN_CURR_POS;
554
555 END LOOP;
556
557 END IF;
558
559 END CREATE_CONTENT_DYNAMIC_SQL;
560
561 --------------------------------------------------------------------------------
562 -- PROCEDURE: CREATE_CONTENT_COLS_FROM_VIEW --
563 -- --
564 -- DESCRIPTION: Creates the Content Columns for the Content Object --
565 -- the supplied Integrator. --
566 -- --
567 -- PARAMETERS: --
568 -- --
569 -- MODIFICATION HISTORY --
570 -- Date Username Description --
571 -- 23-MAY-2002 KPEET CREATED --
572 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
573 -- 31-OCT-2002 KPEET Was inserting SEQUENCE_NUM into --
574 -- OBJECT_VERSION_NUMBER and vice-versa. Fixed. --
575 --------------------------------------------------------------------------------
576 PROCEDURE CREATE_CONTENT_COLS_FROM_VIEW (P_APPLICATION_ID IN NUMBER,
577 P_CONTENT_CODE IN VARCHAR2,
578 P_VIEW_NAME IN VARCHAR2,
579 P_LANGUAGE IN VARCHAR2,
580 P_SOURCE_LANGUAGE IN VARCHAR2,
581 P_USER_ID IN NUMBER)
582 IS
583 VV_CONTENT_CODE BNE_CONTENT_COLS_B.CONTENT_CODE%TYPE;
584 VV_ORACLE_USER VARCHAR2(20);
585 BEGIN
586
587 -- Check for existing Content Cols for the Content
588
589 VV_CONTENT_CODE := NULL;
590 BEGIN
591 SELECT DISTINCT A.CONTENT_CODE
592 INTO VV_CONTENT_CODE
593 FROM BNE_CONTENT_COLS_B A, BNE_CONTENT_COLS_TL B
594 WHERE A.APPLICATION_ID = B.APPLICATION_ID
595 AND A.CONTENT_CODE = B.CONTENT_CODE
596 AND A.SEQUENCE_NUM = B.SEQUENCE_NUM
597 AND A.APPLICATION_ID = P_APPLICATION_ID
598 AND A.CONTENT_CODE = P_CONTENT_CODE
599 AND B.LANGUAGE = P_LANGUAGE;
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN NULL;
602 END;
603
604 -- Determine ORACLE_USERNAME - usually APPS - need to limit selections
605 -- from the ALL_TAB_COLUMNS table using this user because there can be multiple entries.
606
607 VV_ORACLE_USER := NULL;
608 BEGIN
609 SELECT ORACLE_USERNAME
610 INTO VV_ORACLE_USER
611 FROM FND_ORACLE_USERID
612 WHERE ORACLE_ID = 900;
613 EXCEPTION
614 WHEN NO_DATA_FOUND THEN
615 VV_ORACLE_USER := 'APPS';
616 END;
617
618 -- If new Content, then Create
619 IF (VV_CONTENT_CODE IS NULL) THEN
620
621 -- Insert new record into BNE_CONTENT_COLS_B table
622
623 INSERT INTO BNE_CONTENT_COLS_B
624 (APPLICATION_ID, CONTENT_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, COL_NAME,
625 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
626 SELECT P_APPLICATION_ID APPLICATION_ID,
627 P_CONTENT_CODE CONTENT_CODE,
628 1 OBJECT_VERSION_NUMBER,
629 ATC.COLUMN_ID SEQUENCE_NUM,
630 ATC.COLUMN_NAME COL_NAME,
631 P_USER_ID CREATED_BY,
632 SYSDATE CREATION_DATE,
633 P_USER_ID LAST_UPDATED_BY,
634 SYSDATE LAST_UPDATE_DATE
635 FROM ALL_TAB_COLUMNS ATC
636 WHERE ATC.OWNER = VV_ORACLE_USER
637 AND ATC.TABLE_NAME = P_VIEW_NAME;
638
639
640 -- Insert new record into BNE_CONTENT_COLS_TL table
641
642 INSERT INTO BNE_CONTENT_COLS_TL
643 (APPLICATION_ID, CONTENT_CODE, SEQUENCE_NUM, LANGUAGE, SOURCE_LANG, USER_NAME,
644 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
645 SELECT P_APPLICATION_ID APPLICATION_ID,
649 P_SOURCE_LANGUAGE SOURCE_LANG,
646 P_CONTENT_CODE CONTENT_CODE,
647 ATC.COLUMN_ID SEQUENCE_NUM,
648 P_LANGUAGE LANGUAGE,
650 UPPER(REPLACE(ATC.COLUMN_NAME,'_',' ')) USER_NAME,
651 P_USER_ID CREATED_BY,
652 SYSDATE CREATION_DATE,
653 P_USER_ID LAST_UPDATED_BY,
654 SYSDATE LAST_UPDATE_DATE
655 FROM ALL_TAB_COLUMNS ATC
656 WHERE ATC.OWNER = VV_ORACLE_USER
657 AND ATC.TABLE_NAME = P_VIEW_NAME;
658
659 END IF;
660
661 END CREATE_CONTENT_COLS_FROM_VIEW;
662
663
664 --------------------------------------------------------------------------------
665 -- PROCEDURE: UPSERT_STORED_SQL_STATEMENT --
666 -- --
667 -- DESCRIPTION: Procedure inserts or updates a single row in the --
668 -- BNE_STORED_SQL table. --
669 -- This procedure will only update the QUERY column value. --
670 -- The record to be inserted/updated will be determined by --
671 -- the APPLICATION_ID and CONTENT_CODE parameters. --
672 -- --
673 -- MODIFICATION HISTORY --
674 -- Date Username Description --
675 -- 02-JUL-2002 KPEET CREATED --
676 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
677 --------------------------------------------------------------------------------
678 PROCEDURE UPSERT_STORED_SQL_STATEMENT (P_APPLICATION_ID IN NUMBER,
679 P_CONTENT_CODE IN VARCHAR2,
680 P_QUERY IN VARCHAR2,
681 P_USER_ID IN NUMBER)
682 IS
683 VN_NO_RECORD_FLAG NUMBER;
684 BEGIN
685 -- Check the BNE_STORED_SQL table to ensure that the SQL Query for this
686 -- APPLICATION_ID and CONTENT_CODE does not already exist
687
688 VN_NO_RECORD_FLAG := 0;
689
690 BEGIN
691 SELECT 1
692 INTO VN_NO_RECORD_FLAG
693 FROM BNE_STORED_SQL
694 WHERE APPLICATION_ID = P_APPLICATION_ID
695 AND CONTENT_CODE = P_CONTENT_CODE;
696 EXCEPTION
697 WHEN NO_DATA_FOUND THEN NULL;
698 END;
699
700 -- If the SQL query was not found then insert it
701
702 IF (VN_NO_RECORD_FLAG = 0) THEN
703
704 -- Insert the required row in BNE_STORED_SQL
705
706 INSERT INTO BNE_STORED_SQL
707 (APPLICATION_ID, CONTENT_CODE, OBJECT_VERSION_NUMBER, QUERY, CREATED_BY, CREATION_DATE,
708 LAST_UPDATED_BY, LAST_UPDATE_DATE)
709 VALUES
710 (P_APPLICATION_ID, P_CONTENT_CODE, 1, P_QUERY, P_USER_ID, SYSDATE,
711 P_USER_ID, SYSDATE);
712
713 ELSE
714
715 -- Update the required row in BNE_STORED_SQL
716
717 UPDATE BNE_STORED_SQL
718 SET OBJECT_VERSION_NUMBER = (OBJECT_VERSION_NUMBER + 1),
719 QUERY = P_QUERY,
720 LAST_UPDATED_BY = P_USER_ID,
721 LAST_UPDATE_DATE = SYSDATE,
722 LAST_UPDATE_LOGIN = P_USER_ID
723 WHERE APPLICATION_ID = P_APPLICATION_ID
724 AND CONTENT_CODE = P_CONTENT_CODE;
725
726 END IF;
727
728 END UPSERT_STORED_SQL_STATEMENT;
729
730 --------------------------------------------------------------------------------
731 -- PROCEDURE: ENABLE_CONTENT_FOR_REPORTING --
732 -- --
733 -- DESCRIPTION: Copies the Content and Content Columns to create an --
734 -- Interface and Interface Columns, then creates a --
735 -- Mapping between the Content Cols and the Interface --
736 -- Cols. --
737 -- Both the Interface name and the Mapping Name will --
738 -- match the Content USER_NAME in the BNE_CONTENTS_TL --
739 -- table. --
740 -- --
741 -- MODIFICATION HISTORY --
742 -- Date Username Description --
743 -- 02-JUL-2002 KPEET CREATED --
744 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
745 --------------------------------------------------------------------------------
746 PROCEDURE ENABLE_CONTENT_FOR_REPORTING
747 (P_APPLICATION_ID IN NUMBER,
748 P_OBJECT_CODE IN VARCHAR2,
749 P_INTEGRATOR_CODE IN VARCHAR2,
750 P_CONTENT_CODE IN VARCHAR2,
751 P_LANGUAGE IN VARCHAR2,
752 P_SOURCE_LANGUAGE IN VARCHAR2,
753 P_USER_ID IN NUMBER,
754 P_INTERFACE_CODE OUT NOCOPY VARCHAR2,
755 P_MAPPING_CODE OUT NOCOPY VARCHAR2)
756 IS
757 VV_CONTENT_CODE BNE_CONTENTS_B.CONTENT_CODE%TYPE;
758 BEGIN
762
759
760 IF BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(P_APPLICATION_ID) AND
761 BNE_INTEGRATOR_UTILS.IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
763 -- Check for an existing Content Code for this Integrator
764
765 VV_CONTENT_CODE := NULL;
766
767 BEGIN
768 SELECT CONTENT_CODE
769 INTO VV_CONTENT_CODE
770 FROM BNE_CONTENTS_B
771 WHERE APPLICATION_ID = P_APPLICATION_ID
772 AND CONTENT_CODE = P_CONTENT_CODE
773 AND INTEGRATOR_APP_ID = P_APPLICATION_ID
774 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
775 EXCEPTION
776 WHEN NO_DATA_FOUND THEN
777 NULL;
778 END;
779
780 -- If the Content exists, then enable it for Reporting
781
782 IF ( VV_CONTENT_CODE IS NOT NULL ) THEN
783
784 -- Create the Interface
785
786 P_INTERFACE_CODE := NULL;
787
788 BNE_INTEGRATOR_UTILS.CREATE_INTERFACE_FOR_CONTENT
789 (P_APPLICATION_ID,
790 P_OBJECT_CODE,
791 P_CONTENT_CODE,
792 P_INTEGRATOR_CODE,
793 P_LANGUAGE,
794 P_SOURCE_LANGUAGE,
795 P_USER_ID,
796 P_INTERFACE_CODE);
797
798 -- Create the Mapping between the Content Cols and the Interface Cols
799
800 CREATE_REPORTING_MAPPING (P_APPLICATION_ID,
801 P_OBJECT_CODE,
802 P_INTEGRATOR_CODE,
803 P_CONTENT_CODE,
804 P_INTERFACE_CODE,
805 P_LANGUAGE,
806 P_SOURCE_LANGUAGE,
807 P_USER_ID,
808 P_MAPPING_CODE);
809 END IF;
810
811 ELSE
812 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
813 END IF;
814
815 EXCEPTION
816 WHEN OTHERS THEN
817 NULL;
818 END ENABLE_CONTENT_FOR_REPORTING;
819
820 --------------------------------------------------------------------------------
821 -- PROCEDURE: CREATE_REPORTING_MAPPING --
822 -- --
823 -- DESCRIPTION: Creates a Mapping between the Content Cols and the --
824 -- Interface Cols. --
825 -- --
826 -- PARAMETERS: --
827 -- --
828 -- MODIFICATION HISTORY --
829 -- Date Username Description --
830 -- 02-JUL-2002 KPEET CREATED --
831 -- 22-OCT-2002 KPEET Updated to reflect 8.3 schema changes. --
832 -- 08-NOV-2002 KPEET Updated queries to restrict by LANGUAGE. --
833 --------------------------------------------------------------------------------
834 PROCEDURE CREATE_REPORTING_MAPPING (P_APPLICATION_ID IN NUMBER,
835 P_OBJECT_CODE IN VARCHAR2,
836 P_INTEGRATOR_CODE IN VARCHAR2,
837 P_CONTENT_CODE IN VARCHAR2,
838 P_INTERFACE_CODE IN VARCHAR2,
839 P_LANGUAGE IN VARCHAR2,
840 P_SOURCE_LANGUAGE IN VARCHAR2,
841 P_USER_ID IN NUMBER,
842 P_MAPPING_CODE OUT NOCOPY VARCHAR2)
843 IS
844
845 CURSOR MAPPING_COLS_C (CP_APPLICATION_ID IN NUMBER,
846 CP_CONTENT_CODE IN VARCHAR2,
847 CP_INTERFACE_CODE IN VARCHAR2) IS
848 SELECT CC.APPLICATION_ID CONTENT_APP_ID,
849 CC.CONTENT_CODE,
850 CC.SEQUENCE_NUM CONTENT_SEQ_NUM,
851 IC.APPLICATION_ID INTERFACE_APP_ID,
852 IC.INTERFACE_CODE,
853 IC.SEQUENCE_NUM INTERFACE_SEQ_NUM
854 FROM BNE_CONTENT_COLS_B CC,
855 BNE_INTERFACE_COLS_B IC
856 WHERE CC.APPLICATION_ID = IC.APPLICATION_ID
857 AND CC.APPLICATION_ID = CP_APPLICATION_ID
858 AND CC.COL_NAME = IC.INTERFACE_COL_NAME
859 AND CC.CONTENT_CODE = CP_CONTENT_CODE
860 AND IC.INTERFACE_CODE = CP_INTERFACE_CODE
861 ORDER BY CC.SEQUENCE_NUM ASC;
862
863 VV_MAPPING_USER_NAME BNE_MAPPINGS_TL.USER_NAME%TYPE;
864 VV_MAPPING_CODE BNE_MAPPINGS_B.MAPPING_CODE%TYPE;
865 VN_SEQUENCE NUMBER;
866 BEGIN
867
868 IF BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(P_APPLICATION_ID) AND
869 BNE_INTEGRATOR_UTILS.IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
870
871 VV_MAPPING_CODE := NULL;
872 P_MAPPING_CODE := P_OBJECT_CODE||'_MAP';
873
874
875 -- Check that the Mapping Code does not exist
876
877 BEGIN
878 SELECT MAPPING_CODE
879 INTO VV_MAPPING_CODE
880 FROM BNE_MAPPINGS_B
881 WHERE APPLICATION_ID = P_APPLICATION_ID
885 END;
882 AND MAPPING_CODE = P_MAPPING_CODE;
883 EXCEPTION
884 WHEN NO_DATA_FOUND THEN NULL;
886
887
888 IF (VV_MAPPING_CODE IS NULL) THEN
889
890 -- Generate Mapping Name from the Content User Name - Concatenate the word Mapping on the end
891
892 VV_MAPPING_USER_NAME := NULL;
893
894 BEGIN
895 SELECT USER_NAME||' '||'Mapping'
896 INTO VV_MAPPING_USER_NAME
897 FROM BNE_CONTENTS_TL
898 WHERE APPLICATION_ID = P_APPLICATION_ID
899 AND CONTENT_CODE = P_CONTENT_CODE
900 AND LANGUAGE = P_LANGUAGE;
901 EXCEPTION
902 WHEN NO_DATA_FOUND THEN NULL;
903 END;
904
905 -- Create the Mapping record in the BNE_MAPPINGS_B table
906
907 INSERT INTO BNE_MAPPINGS_B
908 (APPLICATION_ID, MAPPING_CODE, OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID, INTEGRATOR_CODE,
909 REPORTING_FLAG, REPORTING_INTERFACE_APP_ID, REPORTING_INTERFACE_CODE,
910 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
911 VALUES
912 (P_APPLICATION_ID, P_MAPPING_CODE, 1, P_APPLICATION_ID, P_INTEGRATOR_CODE,
913 'Y', P_APPLICATION_ID, P_INTERFACE_CODE,
914 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
915
916
917 -- Create the Mapping record in the BNE_MAPPINGS_TL table
918
919 INSERT INTO BNE_MAPPINGS_TL
920 (APPLICATION_ID, MAPPING_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
921 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
922 VALUES
923 (P_APPLICATION_ID, P_MAPPING_CODE, P_LANGUAGE, P_SOURCE_LANGUAGE, VV_MAPPING_USER_NAME,
924 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
925
926
927 VN_SEQUENCE := 0;
928
929 FOR MAPPING_COLS_REC IN MAPPING_COLS_C(P_APPLICATION_ID,
930 P_CONTENT_CODE,
931 P_INTERFACE_CODE) LOOP
932
933 VN_SEQUENCE := VN_SEQUENCE + 1;
934
935 -- Create the Mapping records in the BNE_MAPPING_LINES table
936
937 INSERT INTO BNE_MAPPING_LINES
938 (APPLICATION_ID, MAPPING_CODE, SEQUENCE_NUM, CONTENT_APP_ID, CONTENT_CODE, CONTENT_SEQ_NUM,
939 INTERFACE_APP_ID, INTERFACE_CODE, INTERFACE_SEQ_NUM, OBJECT_VERSION_NUMBER,
940 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
941 VALUES
942 (P_APPLICATION_ID,
943 P_MAPPING_CODE,
944 VN_SEQUENCE,
945 MAPPING_COLS_REC.CONTENT_APP_ID,
946 MAPPING_COLS_REC.CONTENT_CODE,
947 MAPPING_COLS_REC.CONTENT_SEQ_NUM,
948 MAPPING_COLS_REC.INTERFACE_APP_ID,
949 MAPPING_COLS_REC.INTERFACE_CODE,
950 MAPPING_COLS_REC.INTERFACE_SEQ_NUM,
951 1,
952 P_USER_ID,
953 SYSDATE,
954 P_USER_ID,
955 SYSDATE);
956
957 EXIT WHEN MAPPING_COLS_C%NOTFOUND;
958
959 END LOOP;
960
961 END IF;
962
963 ELSE
964 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
965 END IF;
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 NULL;
970 END CREATE_REPORTING_MAPPING;
971
972 --------------------------------------------------------------------------------
973 -- PROCEDURE: CREATE_CONTENT_TO_API_MAP --
974 -- --
975 -- DESCRIPTION: Creates a Mapping between the Content Cols and the --
976 -- Interface Cols. Content cols are mapped to --
977 -- interface_cols of the same name, or the same name --
978 -- pre-pended with "p_". --
979 -- --
980 -- PARAMETERS: --
981 -- --
982 -- MODIFICATION HISTORY --
983 -- Date Username Description --
984 -- 12-NOV-2002 SMCMILLA Copied and modified version of --
985 -- create_reporting_mapping --
986 --------------------------------------------------------------------------------
987 PROCEDURE CREATE_CONTENT_TO_API_MAP (P_APPLICATION_ID IN NUMBER,
988 P_OBJECT_CODE IN VARCHAR2,
989 P_INTEGRATOR_CODE IN VARCHAR2,
990 P_CONTENT_CODE IN VARCHAR2,
991 P_INTERFACE_CODE IN VARCHAR2,
992 P_LANGUAGE IN VARCHAR2,
993 P_SOURCE_LANGUAGE IN VARCHAR2,
994 P_USER_ID IN NUMBER,
995 P_MAPPING_CODE OUT NOCOPY VARCHAR2)
996 IS
997
998 CURSOR MAPPING_COLS_C (CP_APPLICATION_ID IN NUMBER,
999 CP_CONTENT_CODE IN VARCHAR2,
1000 CP_INTERFACE_CODE IN VARCHAR2) IS
1001 SELECT CC.APPLICATION_ID CONTENT_APP_ID,
1005 IC.INTERFACE_CODE,
1002 CC.CONTENT_CODE,
1003 CC.SEQUENCE_NUM CONTENT_SEQ_NUM,
1004 IC.APPLICATION_ID INTERFACE_APP_ID,
1006 IC.SEQUENCE_NUM INTERFACE_SEQ_NUM
1007 FROM BNE_CONTENT_COLS_B CC,
1008 BNE_INTERFACE_COLS_B IC
1009 WHERE CC.APPLICATION_ID = IC.APPLICATION_ID
1010 AND CC.APPLICATION_ID = CP_APPLICATION_ID
1011 AND ( CC.COL_NAME = IC.INTERFACE_COL_NAME
1012 OR CC.COL_NAME = SUBSTR(IC.INTERFACE_COL_NAME,3))
1013 AND CC.CONTENT_CODE = CP_CONTENT_CODE
1014 AND IC.INTERFACE_CODE = CP_INTERFACE_CODE
1015 ORDER BY CC.SEQUENCE_NUM ASC;
1016
1017 VV_MAPPING_USER_NAME BNE_MAPPINGS_TL.USER_NAME%TYPE;
1018 VV_MAPPING_CODE BNE_MAPPINGS_B.MAPPING_CODE%TYPE;
1019 VN_SEQUENCE NUMBER;
1020 BEGIN
1021
1022 IF BNE_INTEGRATOR_UTILS.IS_VALID_APPL_ID(P_APPLICATION_ID) AND
1023 BNE_INTEGRATOR_UTILS.IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
1024
1025 VV_MAPPING_CODE := NULL;
1026 P_MAPPING_CODE := P_OBJECT_CODE||'_MAP';
1027
1028
1029 -- Check that the Mapping Code does not exist
1030
1031 BEGIN
1032 SELECT MAPPING_CODE
1033 INTO VV_MAPPING_CODE
1034 FROM BNE_MAPPINGS_B
1035 WHERE APPLICATION_ID = P_APPLICATION_ID
1036 AND MAPPING_CODE = P_MAPPING_CODE;
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND THEN NULL;
1039 END;
1040
1041
1042 IF (VV_MAPPING_CODE IS NULL) THEN
1043
1044 -- Generate Mapping Name from the Content User Name - Concatenate the word Mapping on the end
1045
1046 VV_MAPPING_USER_NAME := NULL;
1047
1048 BEGIN
1049 SELECT USER_NAME||' '||'Mapping'
1050 INTO VV_MAPPING_USER_NAME
1051 FROM BNE_CONTENTS_TL
1052 WHERE APPLICATION_ID = P_APPLICATION_ID
1053 AND CONTENT_CODE = P_CONTENT_CODE
1054 AND LANGUAGE = P_LANGUAGE;
1055 EXCEPTION
1056 WHEN NO_DATA_FOUND THEN NULL;
1057 END;
1058
1059 -- Create the Mapping record in the BNE_MAPPINGS_B table
1060
1061 INSERT INTO BNE_MAPPINGS_B
1062 (APPLICATION_ID, MAPPING_CODE, OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID, INTEGRATOR_CODE,
1063 REPORTING_FLAG, REPORTING_INTERFACE_APP_ID, REPORTING_INTERFACE_CODE,
1064 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1065 VALUES
1066 (P_APPLICATION_ID, P_MAPPING_CODE, 1, P_APPLICATION_ID, P_INTEGRATOR_CODE,
1067 'N', NULL, NULL, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
1068
1069
1070 -- Create the Mapping record in the BNE_MAPPINGS_TL table
1071
1072 INSERT INTO BNE_MAPPINGS_TL
1073 (APPLICATION_ID, MAPPING_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
1074 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1075 VALUES
1076 (P_APPLICATION_ID, P_MAPPING_CODE, P_LANGUAGE, P_SOURCE_LANGUAGE, VV_MAPPING_USER_NAME,
1077 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
1078
1079
1080 VN_SEQUENCE := 0;
1081
1082 FOR MAPPING_COLS_REC IN MAPPING_COLS_C(P_APPLICATION_ID,
1083 P_CONTENT_CODE,
1084 P_INTERFACE_CODE) LOOP
1085
1086 VN_SEQUENCE := VN_SEQUENCE + 1;
1087
1088 -- Create the Mapping records in the BNE_MAPPING_LINES table
1089
1090 INSERT INTO BNE_MAPPING_LINES
1091 (APPLICATION_ID, MAPPING_CODE, SEQUENCE_NUM, CONTENT_APP_ID, CONTENT_CODE, CONTENT_SEQ_NUM,
1092 INTERFACE_APP_ID, INTERFACE_CODE, INTERFACE_SEQ_NUM, OBJECT_VERSION_NUMBER,
1093 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1094 VALUES
1095 (P_APPLICATION_ID,
1096 P_MAPPING_CODE,
1097 VN_SEQUENCE,
1098 MAPPING_COLS_REC.CONTENT_APP_ID,
1099 MAPPING_COLS_REC.CONTENT_CODE,
1100 MAPPING_COLS_REC.CONTENT_SEQ_NUM,
1101 MAPPING_COLS_REC.INTERFACE_APP_ID,
1102 MAPPING_COLS_REC.INTERFACE_CODE,
1103 MAPPING_COLS_REC.INTERFACE_SEQ_NUM,
1104 1,
1105 P_USER_ID,
1106 SYSDATE,
1107 P_USER_ID,
1108 SYSDATE);
1109
1110 EXIT WHEN MAPPING_COLS_C%NOTFOUND;
1111
1112 END LOOP;
1113
1114 END IF;
1115
1116 ELSE
1117 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
1118 END IF;
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 NULL;
1123 END CREATE_CONTENT_TO_API_MAP;
1124
1125
1126 --------------------------------------------------------------------------------
1127 -- PROCEDURE: ASSIGN_PARAM_LIST_TO_CONTENT --
1128 -- --
1129 -- DESCRIPTION: Links a Parameter List to a Content. --
1130 -- --
1131 -- PARAMETERS: --
1132 -- --
1133 -- MODIFICATION HISTORY --
1134 -- Date Username Description --
1135 -- 04-NOV-2002 KPEET Created. --
1136 --------------------------------------------------------------------------------
1137 PROCEDURE ASSIGN_PARAM_LIST_TO_CONTENT
1138 (P_CONTENT_APP_ID IN NUMBER,
1139 P_CONTENT_CODE IN VARCHAR2,
1140 P_PARAM_LIST_APP_ID IN NUMBER,
1141 P_PARAM_LIST_CODE IN VARCHAR2)
1142 IS
1143 VV_CONTENT_CODE VARCHAR2(30);
1144 VV_PARAM_LIST_CODE VARCHAR2(30);
1145 BEGIN
1146
1147 -- Initialize variables to NULL
1148
1149 VV_CONTENT_CODE := NULL;
1150 VV_PARAM_LIST_CODE := NULL;
1151
1152
1153 -- Check that the Content exists
1154
1155 BEGIN
1156 SELECT CONTENT_CODE
1157 INTO VV_CONTENT_CODE
1158 FROM BNE_CONTENTS_B
1159 WHERE APPLICATION_ID = P_CONTENT_APP_ID
1160 AND CONTENT_CODE = P_CONTENT_CODE;
1161 EXCEPTION
1162 WHEN NO_DATA_FOUND THEN NULL;
1163 END;
1164
1165
1166 -- Check that the Parameter List exists
1167
1168 BEGIN
1169 SELECT PARAM_LIST_CODE
1170 INTO VV_PARAM_LIST_CODE
1171 FROM BNE_PARAM_LISTS_B
1172 WHERE APPLICATION_ID = P_PARAM_LIST_APP_ID
1173 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
1174 EXCEPTION
1175 WHEN NO_DATA_FOUND THEN NULL;
1176 END;
1177
1178
1179 -- Only assign the Parameter List to the Content if they both exist
1180
1181 IF (VV_CONTENT_CODE IS NOT NULL) AND (VV_PARAM_LIST_CODE IS NOT NULL) THEN
1182
1183 UPDATE BNE_CONTENTS_B
1187 WHERE APPLICATION_ID = P_CONTENT_APP_ID
1184 SET PARAM_LIST_APP_ID = P_PARAM_LIST_APP_ID,
1185 PARAM_LIST_CODE = P_PARAM_LIST_CODE,
1186 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
1188 AND CONTENT_CODE = P_CONTENT_CODE;
1189
1190 END IF;
1191
1192 END ASSIGN_PARAM_LIST_TO_CONTENT;
1193
1194 ------------------------------------------------------------------------
1195
1196 END BNE_CONTENT_UTILS;