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