[Home] [Help]
PACKAGE BODY: APPS.BNE_INTEGRATOR_UTILS
Source
1 PACKAGE BODY BNE_INTEGRATOR_UTILS AS
2 /* $Header: bneintgb.pls 120.9 2008/01/22 07:37:47 seramali ship $ */
3 --------------------------------------------------------------------------------
4 -- PACKAGE: BNE_INTEGRATOR_UTILS --
5 -- --
6 -- DESCRIPTION: --
7 -- --
8 -- MODIFICATION HISTORY --
9 -- Date Username Description --
10 -- 22-APR-2002 JRICHARD Created. --
11 -- 16-SEP-2002 KPEET Removed Procedure CREATE_OBJECT. --
12 -- Updated package due to 8.3 schema changes. --
13 -- 29-OCT-2002 KPEET Added IS_VALID_APPL_ID due to 8.3 schema changes. --
14 -- 11-NOV-2002 KPEET Updated procedure CREATE_INTERFACE_FOR_CONTENT. --
15 -- 29-NOV-2002 KPEET Updated procedure CREATE_INTERFACE_FOR_API. --
16 -- 01-OCT-2003 TOBERMEI Updated procedure CREATE_INTERFACE_FOR_API. --
17 -- 01-OCT-2003 TOBERMEI Updated procedure CREATE_API_PARAMETER_LIST. --
18 -- 19-JAN-2004 DGROVES Bug 3059157 --
19 -- 25-MAR-2004 DGROVES Bug 3510393 Changed DBA_OBJECTS to USER_OBJECTS --
20 -- 16-FEB-2005 DGROVES Bug 4187173 Added new columns to UPSERT_INTERFACE_COLUMN
21 -- 28-FEB-2005 DGROVES Bug 4046464 default mandatory flag columns. --
22 -- 07-JUL-2005 DVAYRO Bug 4477511 Added new column for NE_LAYOUT_COLS_PKG--
23 -- 26-JUL-2006 DAGROVES Bug 4447161 Added P_USE_FND_METADATA flag to CREATE_INTERFACE_FOR_TABLE(),
24 -- Added CREATE%LOV() methods. Added DELETE%() methods.
25 -- 14-AUG-2006 DAGROVES Bug 5464481 - CREATE SCRIPTS FOR FLEXFIELD COLUMNS --
26 -- 17-APR-2007 JRICHARD Bug 5728544 - UNABLE TO UPLOAD DATA FOR 'WEB ADI - --
27 -- UPDATE INTERFACE COLUMN PROMPTS --
28 -- 30-MAY-2007 DAGROVES Bug 5682057 - BNE_INTEGRATOR_UTILS.DELETE_INTEGRATOR API DOESN'T WORK AS EXPECTED
29 --------------------------------------------------------------------------------
30
31 TYPE BNEKEY IS RECORD (
32 APP_ID NUMBER(15),
33 CODE VARCHAR2(200)
34 );
35
36 TYPE BNEKEY_TAB IS TABLE OF BNEKEY
37 INDEX BY BINARY_INTEGER;
38
39 --------------------------------------------------------------------------------
40 -- FUNCTION: IS_VALID_APPL_ID --
41 -- --
42 -- DESCRIPTION: Validates the APPLICATION_ID to ensure the --
43 -- Application is defined in Oracle Applications. --
44 -- --
45 -- --
46 -- MODIFICATION HISTORY --
47 -- Date Username Description --
48 -- 29-OCT-2002 KPEET Created due to 8.3 schema changes. --
49 --------------------------------------------------------------------------------
50 FUNCTION IS_VALID_APPL_ID (P_APPLICATION_ID IN NUMBER) RETURN BOOLEAN
51 IS
52 VN_APPLICATION_ID NUMBER;
53 BEGIN
54
55 VN_APPLICATION_ID := 0;
56
57 BEGIN
58 SELECT APPLICATION_ID
59 INTO VN_APPLICATION_ID
60 FROM FND_APPLICATION
61 WHERE APPLICATION_ID = P_APPLICATION_ID;
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN NULL;
64 END;
65
66 IF (VN_APPLICATION_ID = 0) THEN
67
68 -- if the APPLICATION_ID was not found.
69
70 RETURN FALSE;
71
72 ELSE
73 -- the Application is defined in Oracle Applications.
74
75 RETURN TRUE;
76
77 END IF;
78
79 END IS_VALID_APPL_ID;
80
81 --------------------------------------------------------------------------------
82 -- FUNCTION: IS_VALID_OBJECT_CODE --
83 -- --
84 -- DESCRIPTION: Validates the new code for the --
85 -- new object being created. --
86 -- --
87 -- NOTE: --
88 -- This function does not check if the code already exists, this is done --
89 -- in all other procedures that attempt to create new objects in the BNE --
90 -- schema. --
91 -- --
92 -- PARAMETERS: --
93 -- --
94 -- MODIFICATION HISTORY --
95 -- Date Username Description --
96 -- 17-SEP-2002 KPEET Created due to 8.3 schema changes. --
97 --------------------------------------------------------------------------------
98 FUNCTION IS_VALID_OBJECT_CODE (P_OBJECT_CODE IN VARCHAR2) RETURN BOOLEAN
99 IS
100 BEGIN
101 RETURN IS_VALID_OBJECT_CODE (P_OBJECT_CODE , 20);
102 END IS_VALID_OBJECT_CODE;
103
104 --------------------------------------------------------------------------------
105 -- FUNCTION: IS_VALID_OBJECT_CODE --
106 -- --
107 -- DESCRIPTION: Validates the new code for the --
108 -- new object being created. --
109 -- --
110 -- NOTE: --
111 -- This function does not check if the code already exists, this is done --
112 -- in all other procedures that attempt to create new objects in the BNE --
113 -- schema. --
114 -- --
115 -- PARAMETERS: --
116 -- P_OBJECT_CODE The String to check. --
117 -- P_MAX_CODE_LENGTH Maximum length P_OBJECT_CODE is allowed to be. --
118 -- --
119 -- MODIFICATION HISTORY --
120 -- Date Username Description --
121 -- 21-Sep-2004 DAGROVES Created from single arg version. --
122 --------------------------------------------------------------------------------
123 FUNCTION IS_VALID_OBJECT_CODE (P_OBJECT_CODE IN VARCHAR2,
124 P_MAX_CODE_LENGTH IN NUMBER) RETURN BOOLEAN
125 IS
126 VV_VALID_FLAG VARCHAR2(1);
127 VV_VALID_CHARS VARCHAR2(40);
128 VV_TEMP_VALID_CHARS VARCHAR2(40);
129 VN_CODE_LENGTH NUMBER;
130 BEGIN
131 VV_VALID_FLAG := 'N';
132 VV_VALID_CHARS := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_*';
133 VV_TEMP_VALID_CHARS := '*************************************&';
134 VN_CODE_LENGTH := 0;
135
136 -- Check that the OBJECT_CODE consists of valid characters only.
137 -- Valid characters include: 'A'..'Z', '0'..'9', '_'
138
139 -- Set VV_VALID_FLAG equal to 'Y'(Contains only valid chars) or
140 -- 'N' (Contains one or more invalid chars).
141
142 SELECT LENGTH(P_OBJECT_CODE),
143 DECODE(LENGTH(RTRIM(TRANSLATE(P_OBJECT_CODE,
144 VV_VALID_CHARS,
145 VV_TEMP_VALID_CHARS),
146 '*')),
147 NULL, 'Y', -- set to Y (VALID) if all characters are within the specified range.
148 'N') -- set to N (INVALID) if any other characters are contained in the string.
149 INTO VN_CODE_LENGTH, VV_VALID_FLAG
150 FROM DUAL;
151
152
153 ------------------------------------------------------------------------------------------------
154 -- Logic in above SQL explained:
155 --
156 -- The TRANSLATE function converts all instances of valid characters in P_OBJECT_CODE into '*'
157 -- and all instances of '*' in P_OBJECT_CODE into '&'.
158 --
159 -- The RTRIM function will trim all instances of '*' from the right hand side of the string
160 -- resulting from the TRANSLATE function.
161 --
162 -- The LENGTH function measures the length of the string resulting from the RTRIM funcion.
163 -- If the string consists entirely of '*' the entire string would have been trimmed and no
164 -- characters will remain. The string length will be NULL.
165 -- If the string contains any characters other than '*', the string length will be equal to 1
166 -- or greater. (The RTRIM function will trim '*' from the RHS of the string until it finds
167 -- a different character. It will stop trimming at this character.)
168 ------------------------------------------------------------------------------------------------
169
170
171 IF (VN_CODE_LENGTH > P_MAX_CODE_LENGTH) THEN
172 -- if the length of the OBJECT_CODE exceeds the max length, fail the validation.
173 RETURN FALSE;
174
175 ELSIF (VV_VALID_FLAG = 'Y') THEN
176 -- The length of the OBJECT_CODE was OK, now check if the OBJECT_CODE contained only valid chars.
177 -- If the VALID_FLAG is 'Y', pass the validation
178 RETURN TRUE;
179
180 ELSE
181 RETURN FALSE;
182
183 END IF;
184
185 -- NOTE:
186 --
187 -- This function does not check if the code already exists, this is done in
188 -- all other procedures that attempt to create new objects in the BNE schema.
189
190 END IS_VALID_OBJECT_CODE;
191
192 --------------------------------------------------------------------------------
193 -- PROCEDURE: LINK_LIST_TO_INTERFACE --
194 -- --
195 -- DESCRIPTION: Links the Parameter List for the API to the --
196 -- Interface. Updates table BNE_INTERFACES_B. --
197 -- --
198 -- PARAMETERS: --
199 -- --
200 -- MODIFICATION HISTORY --
201 -- Date Username Description --
202 -- 23-MAY-2002 KPEET CREATED --
203 -- 16-SEP-2002 KPEET Updated to reference the new primary keys, new --
204 -- table name and increment object_version_number due --
205 -- to 8.3 schema changes. --
206 --------------------------------------------------------------------------------
207 PROCEDURE LINK_LIST_TO_INTERFACE (P_PARAM_LIST_APP_ID IN NUMBER,
208 P_PARAM_LIST_CODE IN VARCHAR2,
209 P_INTERFACE_APP_ID IN NUMBER,
210 P_INTERFACE_CODE IN VARCHAR2)
211 IS
212 BEGIN
213 -- if there is already an upload parameter list linked to the interface, this will be overwritten
214
215 UPDATE BNE_INTERFACES_B
216 SET UPLOAD_PARAM_LIST_APP_ID = P_PARAM_LIST_APP_ID,
217 UPLOAD_PARAM_LIST_CODE = P_PARAM_LIST_CODE,
218 OBJECT_VERSION_NUMBER = (OBJECT_VERSION_NUMBER + 1)
219 WHERE APPLICATION_ID = P_INTERFACE_APP_ID
220 AND INTERFACE_CODE = P_INTERFACE_CODE;
221
222 END LINK_LIST_TO_INTERFACE;
223
224 --------------------------------------------------------------------------------
225 -- PROCEDURE: CREATE_API_PARAMETER_LIST --
226 -- --
227 -- DESCRIPTION: Creates the Parameter List for the API, --
228 -- the Attribute for the API Call, the Attributes for --
229 -- the API Parameters and the Parameter List Items for --
230 -- the API Parameters. --
231 -- If the Parameter List already exists, no new seed --
232 -- data will be generated. --
233 -- Inserts into tables BNE_PARAM_LISTS_B/TL, --
234 -- BNE_ATTRIBUTES and BNE_PARAM_LIST_ITEMS. --
235 -- --
236 -- PARAMETERS: --
237 -- --
238 -- MODIFICATION HISTORY --
239 -- Date Username Description --
240 -- 22-MAY-2002 KPEET CREATED --
241 -- 17-JUL-2002 KPEET Updated so that it will only create the new API --
242 -- seed data but will not Update any data. --
243 -- 28-JUL-2002 KPEET Updated to use cursor API_PARAMS_C therefore --
244 -- removing the need to use ATTRIBUTE30 to store the --
245 -- INTERFACE_ID (This was previously a fix that gave --
246 -- us a way of linking the Attributes to the Param --
247 -- List Items in order to insert ATTRIBUTE_ID in the --
248 -- BNE_PARAM_LIST_ITEMS table. --
249 -- 30-JUL-2002 KPEET Removed check for existing API call in --
250 -- BNE_ATTRIBUTES table as customers will sometimes --
251 -- need to define overloaded APIs. The check against --
252 -- BNE_PARAM_LIST is sufficient as this is based on --
253 -- the Integrator. --
254 -- 31-JUL-2002 KPEET Added ATTRIBUTE_ID to the insert statement which --
255 -- creates the record in the BNE_PARAM_LIST table. --
256 -- 22-OCT-2002 KPEET Updated to use new primary keys in 8.3 schema. --
257 -- 07-NOV-2002 KPEET Renamed procedure to be CREATE_API_PARAMETER_LIST. --
258 -- 01-OCT-2003 TOBERMEI Changed decode of A.TYPE# to use 'varchar2' as --
259 -- default if no matching value found --
260 --------------------------------------------------------------------------------
261 PROCEDURE CREATE_API_PARAMETER_LIST
262 (P_PARAM_LIST_NAME IN VARCHAR2,
263 P_API_PACKAGE_NAME IN VARCHAR2,
264 P_API_PROCEDURE_NAME IN VARCHAR2,
265 P_API_TYPE IN VARCHAR2,
266 P_API_RETURN_TYPE IN VARCHAR2,
267 P_LANGUAGE IN VARCHAR2,
268 P_SOURCE_LANG IN VARCHAR2,
269 P_USER_ID IN NUMBER,
270 P_OVERLOAD IN NUMBER,
271 P_APPLICATION_ID IN NUMBER,
272 P_OBJECT_CODE IN VARCHAR2,
273 P_PARAM_LIST_CODE OUT NOCOPY VARCHAR2)
274 IS
275
276 CURSOR API_PARAMS_C (CP_API_PACKAGE_NAME IN VARCHAR2,
277 CP_API_PROCEDURE_NAME IN VARCHAR2,
278 CP_OVERLOAD IN NUMBER,
279 CP_APPLICATION_ID IN NUMBER,
280 CP_OBJECT_CODE IN VARCHAR2,
281 CP_USER_ID IN NUMBER) IS
282 SELECT CP_APPLICATION_ID APPLICATION_ID,
283 CP_OBJECT_CODE||'_P'||TO_CHAR(A.SEQUENCE#)||'_ATT' ATTRIBUTE_CODE,
284 A.ARGUMENT PARAM_NAME,
285 DECODE(A.TYPE#, 252, 'boolean',
286 12, 'date',
287 2, 'number',
288 1, 'varchar2',
289 'varchar2') ATTRIBUTE2,
290 DECODE(A.IN_OUT,1,'OUT',2,'INOUT','IN') ATTRIBUTE3,
291 'N' ATTRIBUTE4,
292 DECODE(A.TYPE#, 252, NULL,
293 12, NULL,
294 2, NULL,
295 1, '2000') ATTRIBUTE6,
296 CP_OBJECT_CODE PARAM_LIST_CODE,
297 A.SEQUENCE# SEQ_NUM,
298 CP_USER_ID CREATED_BY,
299 SYSDATE CREATION_DATE,
300 CP_USER_ID LAST_UPDATED_BY,
301 SYSDATE LAST_UPDATE_DATE
302 FROM SYS.ARGUMENT$ A,
303 USER_OBJECTS B
304 WHERE A.OBJ# = B.OBJECT_ID
305 AND B.OBJECT_NAME = CP_API_PACKAGE_NAME
306 AND A.PROCEDURE$ = CP_API_PROCEDURE_NAME
307 AND A.LEVEL# = 0
308 AND A.OVERLOAD# = CP_OVERLOAD;
309
310 VV_ATTRIBUTE_CODE BNE_ATTRIBUTES.ATTRIBUTE_CODE%TYPE;
311 VV_TEMP_ATTRIBUTE_CODE BNE_ATTRIBUTES.ATTRIBUTE_CODE%TYPE;
312 VV_PERSISTENT BNE_PARAM_LISTS_B.PERSISTENT_FLAG%TYPE;
313 BEGIN
314 P_PARAM_LIST_CODE := NULL;
315 VV_ATTRIBUTE_CODE := NULL;
316 VV_TEMP_ATTRIBUTE_CODE := NULL;
317 VV_PERSISTENT := 'Y';
318
319 -- Only create the API Parameter List and Attributes if the OBJECT_CODE supplied is VALID.
320
321 IF IS_VALID_APPL_ID(P_APPLICATION_ID) AND IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
322
323 -- Check to see if the Param List Code already exists. (The Param List Code will
324 -- always use the OBJECT_CODE as it is passed, therefore no temporary variables
325 -- need to be defined.
326
327 -- *** NOTE: This DOES NOT check the Parameter List Name in the USER_NAME column
328 -- in the TL table.
329
330 BEGIN
331 SELECT A.PARAM_LIST_CODE
332 INTO P_PARAM_LIST_CODE
333 FROM BNE_PARAM_LISTS_B A, BNE_PARAM_LISTS_TL B
334 WHERE A.APPLICATION_ID = B.APPLICATION_ID
335 AND A.PARAM_LIST_CODE = B.PARAM_LIST_CODE
336 AND B.LANGUAGE = P_LANGUAGE
337 AND A.APPLICATION_ID = P_APPLICATION_ID
338 AND A.PARAM_LIST_CODE = P_OBJECT_CODE;
339 EXCEPTION
340 WHEN NO_DATA_FOUND THEN NULL;
341 END;
342
343 -- If this is a new Parameter List, then create it and
344 -- the associated Attributes and Parameter List Items.
345
346 IF ( P_PARAM_LIST_CODE IS NULL ) THEN
347
348 -- Set the PARAM_LIST_CODE
349
350 P_PARAM_LIST_CODE := P_OBJECT_CODE;
351
352 -- Derive the ATTRIBUTE_CODE for the API Call - derived here
353 -- so it can be inserted as part of the parameter list
354 -- As the ATTRIBUTE_CODE for the API parameters includes the API Parameter sequence number,
355 -- the ATTRIBUTE_CODE for the API Call will use '0' (zero), e.g. 'PARAM_LIST_CODE_P0_ATT'.
356
357 VV_ATTRIBUTE_CODE := P_OBJECT_CODE||'_P0_ATT';
358
359 -- Check to see if the ATTRIBUTE_CODE is unique
360
361 BEGIN
362 SELECT DISTINCT ATTRIBUTE_CODE
363 INTO VV_TEMP_ATTRIBUTE_CODE
364 FROM BNE_ATTRIBUTES
365 WHERE APPLICATION_ID = P_APPLICATION_ID
366 AND ATTRIBUTE_CODE = VV_ATTRIBUTE_CODE;
367 EXCEPTION
368 WHEN NO_DATA_FOUND THEN NULL;
369 END;
370
371 IF (VV_TEMP_ATTRIBUTE_CODE IS NULL) THEN
372
373 -- Insert a new record into the BNE_PARAM_LISTS_B table
374
375 INSERT INTO BNE_PARAM_LISTS_B
376 (APPLICATION_ID, PARAM_LIST_CODE, OBJECT_VERSION_NUMBER, PERSISTENT_FLAG, COMMENTS,
377 ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, CREATED_BY, CREATION_DATE,
378 LAST_UPDATED_BY, LAST_UPDATE_DATE)
379 VALUES
380 (P_APPLICATION_ID, P_PARAM_LIST_CODE, 1, VV_PERSISTENT, P_PARAM_LIST_NAME,
381 P_APPLICATION_ID, VV_ATTRIBUTE_CODE, P_USER_ID, SYSDATE,
382 P_USER_ID, SYSDATE);
383
384 -- Insert a new record into the BNE_PARAM_LISTS_TL table
385
386 INSERT INTO BNE_PARAM_LISTS_TL
387 (APPLICATION_ID, PARAM_LIST_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
388 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
389 VALUES
390 (P_APPLICATION_ID, P_PARAM_LIST_CODE, P_LANGUAGE, P_SOURCE_LANG, P_PARAM_LIST_NAME,
391 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
392
393
394 -- Insert the Attribute for the API Call
395
396 INSERT INTO BNE_ATTRIBUTES
397 (APPLICATION_ID,
398 ATTRIBUTE_CODE,
399 OBJECT_VERSION_NUMBER,
400 ATTRIBUTE1,
401 ATTRIBUTE2,
402 ATTRIBUTE3,
403 ATTRIBUTE4,
404 ATTRIBUTE5,
405 CREATED_BY,
406 CREATION_DATE,
407 LAST_UPDATED_BY,
408 LAST_UPDATE_DATE)
409 VALUES
410 (P_APPLICATION_ID,
411 VV_ATTRIBUTE_CODE,
412 1,
413 P_API_TYPE,
414 P_API_PACKAGE_NAME||'.'||P_API_PROCEDURE_NAME,
415 P_API_RETURN_TYPE,
416 'N',
417 'Y',
418 P_USER_ID,
419 SYSDATE,
420 P_USER_ID,
421 SYSDATE);
422
423 FOR API_PARAMS_REC IN API_PARAMS_C(P_API_PACKAGE_NAME,
424 P_API_PROCEDURE_NAME,
425 P_OVERLOAD,
426 P_APPLICATION_ID,
427 P_OBJECT_CODE,
428 P_USER_ID) LOOP
429
430
431 -- Generate the Attributes for the API Parameters
432
433 INSERT INTO BNE_ATTRIBUTES
434 (APPLICATION_ID,
435 ATTRIBUTE_CODE,
436 OBJECT_VERSION_NUMBER,
437 ATTRIBUTE1,
438 ATTRIBUTE2,
439 ATTRIBUTE3,
440 ATTRIBUTE4,
441 ATTRIBUTE6,
442 CREATED_BY,
443 CREATION_DATE,
444 LAST_UPDATED_BY,
445 LAST_UPDATE_DATE)
446 VALUES
447 (API_PARAMS_REC.APPLICATION_ID,
448 API_PARAMS_REC.ATTRIBUTE_CODE,
449 1,
450 API_PARAMS_REC.PARAM_NAME,
451 API_PARAMS_REC.ATTRIBUTE2,
452 API_PARAMS_REC.ATTRIBUTE3,
453 API_PARAMS_REC.ATTRIBUTE4,
454 API_PARAMS_REC.ATTRIBUTE6,
455 API_PARAMS_REC.CREATED_BY,
456 API_PARAMS_REC.CREATION_DATE,
457 API_PARAMS_REC.LAST_UPDATED_BY,
458 API_PARAMS_REC.LAST_UPDATE_DATE);
459
460 -- Generate the Parameter List Items
461
462 INSERT INTO BNE_PARAM_LIST_ITEMS
463 (APPLICATION_ID,
464 PARAM_LIST_CODE,
465 SEQUENCE_NUM,
466 OBJECT_VERSION_NUMBER,
467 PARAM_NAME,
468 ATTRIBUTE_APP_ID,
469 ATTRIBUTE_CODE,
470 CREATED_BY,
471 CREATION_DATE,
472 LAST_UPDATED_BY,
473 LAST_UPDATE_DATE)
474 VALUES
475 (API_PARAMS_REC.APPLICATION_ID,
476 API_PARAMS_REC.PARAM_LIST_CODE,
477 API_PARAMS_REC.SEQ_NUM,
478 1,
479 API_PARAMS_REC.PARAM_NAME,
480 API_PARAMS_REC.APPLICATION_ID,
481 API_PARAMS_REC.ATTRIBUTE_CODE,
482 API_PARAMS_REC.CREATED_BY,
483 API_PARAMS_REC.CREATION_DATE,
484 API_PARAMS_REC.LAST_UPDATED_BY,
485 API_PARAMS_REC.LAST_UPDATE_DATE);
486 EXIT WHEN API_PARAMS_C%NOTFOUND;
487
488 END LOOP;
489
490 END IF;
491 ELSE
492 -- If the ATTRIBUTE_CODE is non-unique, ie. already exists...return an error message.
493 NULL;
494 END IF;
495 ELSE
496 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
497
498 END IF;
499
500 END CREATE_API_PARAMETER_LIST;
501
502
503 --------------------------------------------------------------------------------
504 -- PROCEDURE: CREATE_INTEGRATOR --
505 -- --
506 -- DESCRIPTION: Procedure creates a Web ADI Integrator. Also --
507 -- creates a Content of "None" for the new Integrator. --
508 -- A Content of "None" is required by customers not --
509 -- using the Web ADI Download functionality. --
510 -- --
511 -- PARAMETERS: --
512 -- --
513 -- MODIFICATION HISTORY --
514 -- Date Username Description --
515 -- 22-APR-02 JRICHARD CREATED --
516 -- 17-SEP-02 KPEET Updated to reflect new 8.3 schema changes. --
517 -- Parameter P_INTEGRATOR_CODE replaces parameter --
518 -- P_INTEGRATOR_NAME. --
519 -- Parameter P_INTEGRATOR_USER_NAME replaces parameter --
520 -- P_USER_INTEGRATOR_NAME. --
521 --------------------------------------------------------------------------------
522 PROCEDURE CREATE_INTEGRATOR(P_APPLICATION_ID IN NUMBER,
523 P_OBJECT_CODE IN VARCHAR2,
524 P_INTEGRATOR_USER_NAME IN VARCHAR2,
525 P_LANGUAGE IN VARCHAR2,
526 P_SOURCE_LANGUAGE IN VARCHAR2,
527 P_USER_ID IN NUMBER,
528 P_INTEGRATOR_CODE OUT NOCOPY VARCHAR2)
529 IS
530 VV_INTEGRATOR_CODE BNE_INTEGRATORS_B.INTEGRATOR_CODE%TYPE;
531 VV_CONTENT_CODE BNE_CONTENTS_B.CONTENT_CODE%TYPE;
532 BEGIN
533
534 -- Only create the Integrator and Content if the OBJECT_CODE supplied is VALID.
535
536 IF NOT IS_VALID_APPL_ID(P_APPLICATION_ID) THEN
537 RAISE_APPLICATION_ERROR(-20000,'The supplied application id: ' || P_APPLICATION_ID || ' is invalid.');
538
539 ELSIF NOT IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
540 RAISE_APPLICATION_ERROR(-20000,'The object code: ' || P_OBJECT_CODE || ' is invalid.');
541
542 ELSE
543
544 -- Check to see if the Integrator has already been created/seeded
545 -- for P_APPLICATION_ID with the same INTEGRATOR_CODE.
546
547 -- *** NOTE: This DOES NOT check the Integrator Name in the USER_NAME column in the TL table.
548
549 VV_INTEGRATOR_CODE := NULL;
550 P_INTEGRATOR_CODE := P_OBJECT_CODE||'_INTG';
551
552 BEGIN
553 SELECT INTEGRATOR_CODE
554 INTO VV_INTEGRATOR_CODE
555 FROM BNE_INTEGRATORS_B
556 WHERE APPLICATION_ID = P_APPLICATION_ID
557 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
558 EXCEPTION
559 WHEN NO_DATA_FOUND THEN NULL;
560 END;
561
562 -- If the Integrator does not exist then
563
564 IF ( VV_INTEGRATOR_CODE IS NULL) THEN
565
566 -- Add the Integrator
567
568 INSERT INTO BNE_INTEGRATORS_B
569 (APPLICATION_ID, INTEGRATOR_CODE, OBJECT_VERSION_NUMBER, DATE_FORMAT,
570 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, ENABLED_FLAG)
571 VALUES
572 (P_APPLICATION_ID, P_INTEGRATOR_CODE, 1, 'yyyy-MM-dd',
573 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE, 'Y');
574
575
576 INSERT INTO BNE_INTEGRATORS_TL
577 (APPLICATION_ID, INTEGRATOR_CODE, LANGUAGE, SOURCE_LANG, USER_NAME, UPLOAD_HEADER,
578 UPLOAD_TITLE_BAR, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
579 VALUES
580 (P_APPLICATION_ID, P_INTEGRATOR_CODE, P_LANGUAGE, P_SOURCE_LANGUAGE, P_INTEGRATOR_USER_NAME,
581 'Upload Parameters', 'Upload Parameters', P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
582
583 -- Create Content Object for Content of "None"
584
585 BNE_CONTENT_UTILS.CREATE_CONTENT(P_APPLICATION_ID,
586 P_OBJECT_CODE,
587 P_INTEGRATOR_CODE,
588 'None',
589 P_LANGUAGE,
590 P_SOURCE_LANGUAGE,
591 '',
592 P_USER_ID,
593 VV_CONTENT_CODE);
594
595 ELSE
596 RAISE_APPLICATION_ERROR(-20000,'An integrator for the supplied application id: ' || P_APPLICATION_ID || ' and object code:' || P_OBJECT_CODE || ' already exists.');
597
598 END IF;
599 END IF;
600 END CREATE_INTEGRATOR;
601
602 --------------------------------------------------------------------------------
603 -- PROCEDURE: CREATE_INTEGRATOR_NO_CONTENT --
604 -- --
605 -- DESCRIPTION: Procedure creates a Web ADI Integrator. --
606 -- This procedure is to be used by Integrator --
607 -- Developers who plan to define their own Contents. --
608 -- --
609 -- PARAMETERS: --
610 -- --
611 -- MODIFICATION HISTORY --
612 -- Date Username Description --
613 -- 10-JUL-2002 KPEET CREATED --
614 -- 17-SEP-2002 KPEET Updated to reflect new 8.3 schema changes. --
615 -- Parameter P_INTEGRATOR_CODE replaces parameter --
616 -- P_INTEGRATOR_NAME. --
617 -- Parameter P_INTEGRATOR_USER_NAME replaces --
618 -- parameter P_USER_INTEGRATOR_NAME. --
619 -- 28-AUG-2003 KDOBINSO Added P_LANGUAGE and P_SOURCE_LANGUAGE
620 --------------------------------------------------------------------------------
621 PROCEDURE CREATE_INTEGRATOR_NO_CONTENT(P_APPLICATION_ID IN NUMBER,
622 P_OBJECT_CODE IN VARCHAR2,
623 P_INTEGRATOR_USER_NAME IN VARCHAR2,
624 P_USER_ID IN NUMBER,
625 P_LANGUAGE IN VARCHAR2,
626 P_SOURCE_LANGUAGE IN VARCHAR2,
627 P_INTEGRATOR_CODE OUT NOCOPY VARCHAR2
628 )
629
630 IS
631 VV_INTEGRATOR_CODE BNE_INTEGRATORS_B.INTEGRATOR_CODE%TYPE;
632 VV_CONTENT_CODE BNE_CONTENTS_B.CONTENT_CODE%TYPE;
633 BEGIN
634
635 -- Only create the Integrator if the OBJECT_CODE supplied is VALID.
636
637 IF IS_VALID_APPL_ID(P_APPLICATION_ID) AND IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
638
639 -- Check to see if the Integrator has already been created/seeded
640 -- for P_APPLICATION_ID with the same INTEGRATOR_CODE.
641
642 -- *** NOTE: This DOES NOT check the Integrator Name in the USER_NAME column in the TL table.
643
644 VV_INTEGRATOR_CODE := NULL;
645 P_INTEGRATOR_CODE := P_OBJECT_CODE||'_INTG';
646
647 BEGIN
648 SELECT INTEGRATOR_CODE
649 INTO VV_INTEGRATOR_CODE
650 FROM BNE_INTEGRATORS_B
651 WHERE APPLICATION_ID = P_APPLICATION_ID
652 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
653 EXCEPTION
654 WHEN NO_DATA_FOUND THEN NULL;
655 END;
656
657 -- If the Integrator does not exist then
658
659 IF (VV_INTEGRATOR_CODE IS NULL) THEN
660
661 -- Add the Integrator
662
663 INSERT INTO BNE_INTEGRATORS_B
664 (APPLICATION_ID, INTEGRATOR_CODE, OBJECT_VERSION_NUMBER, DATE_FORMAT,
665 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, ENABLED_FLAG)
666 VALUES
667 (P_APPLICATION_ID, P_INTEGRATOR_CODE, 1, 'yyyy-MM-dd',
668 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE, 'Y');
669
670
671 INSERT INTO BNE_INTEGRATORS_TL
672 (APPLICATION_ID, INTEGRATOR_CODE, LANGUAGE, SOURCE_LANG, USER_NAME, UPLOAD_HEADER,
673 UPLOAD_TITLE_BAR, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
674 VALUES
675 (P_APPLICATION_ID, P_INTEGRATOR_CODE, P_LANGUAGE, P_SOURCE_LANGUAGE, P_INTEGRATOR_USER_NAME,
676 'Upload Parameters', 'Upload Parameters', P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
677
678 END IF;
679
680 ELSE
681 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
682
683
684 END IF;
685
686 END CREATE_INTEGRATOR_NO_CONTENT;
687
688 --------------------------------------------------------------------------------
689 -- PROCEDURE: CREATE_INTERFACE_FOR_TABLE --
690 -- --
691 -- DESCRIPTION: Procedure creates an interface in the Web ADI --
692 -- repository for the first time. Including the columns --
693 -- --
694 -- PARAMETERS: --
695 -- --
696 -- MODIFICATION HISTORY --
697 -- Date Username Description --
698 -- 22-APR-2002 JRICHARD CREATED --
699 -- 22-OCT-2002 KPEET Updated to use new primary keys in 8.3 schema. --
700 -- 08-NOV-2002 KPEET Updated queries to restrict by LANGUAGE. --
701 -- 28-AUG-2003 KDOBINSO Field Size is 0 when the column type is a date --
702 --------------------------------------------------------------------------------
703 PROCEDURE CREATE_INTERFACE_FOR_TABLE (P_APPLICATION_ID IN NUMBER,
704 P_OBJECT_CODE IN VARCHAR2,
705 P_INTEGRATOR_CODE IN VARCHAR2,
706 P_INTERFACE_TABLE_NAME IN VARCHAR2,
707 P_INTERFACE_USER_NAME IN VARCHAR2,
708 P_LANGUAGE IN VARCHAR2,
709 P_SOURCE_LANG IN VARCHAR2,
710 P_USER_ID IN NUMBER,
711 P_INTERFACE_CODE OUT NOCOPY VARCHAR2,
712 P_USE_FND_METADATA IN BOOLEAN,
713 P_INTERFACE_TABLE_OWNER IN VARCHAR2)
714 IS
715 CURSOR TABLE_COLS_FND(CP_APPLICATION_ID IN NUMBER,
716 CP_INTERFACE_CODE IN VARCHAR2,
717 CP_INTERFACE_TABLE_NAME IN VARCHAR2,
718 CP_LANGUAGE IN VARCHAR2,
719 CP_SOURCE_LANG IN VARCHAR2,
720 CP_USER_ID IN NUMBER) IS
721 SELECT CP_APPLICATION_ID APPLICATION_ID,
722 CP_INTERFACE_CODE INTERFACE_CODE,
723 1 OBJECT_VERSION_NUMBER,
724 A.COLUMN_SEQUENCE SEQUENCE_NUM,
725 1 INTERFACE_COL_TYPE,
726 A.COLUMN_NAME INTERFACE_COL_NAME,
727 'Y' ENABLED_FLAG,
728 DECODE(A.NULL_ALLOWED_FLAG,'N','Y','Y','N') REQUIRED_FLAG,
729 'Y' DISPLAY_FLAG,
730 'N' READ_ONLY_FLAG,
731 DECODE(A.NULL_ALLOWED_FLAG,'N','Y','Y','N') NOT_NULL_FLAG,
732 'N' SUMMARY_FLAG,
733 'Y' MAPPING_ENABLED_FLAG,
734 DECODE(A.COLUMN_TYPE,'N',1,'V',2,'D',3) DATA_TYPE,
735 DECODE(A.COLUMN_TYPE,'N',A.WIDTH,'V',A.WIDTH,'D',0) FIELD_SIZE,
736 (A.COLUMN_SEQUENCE * 10) DISPLAY_ORDER,
737 CP_LANGUAGE LANGUAGE,
738 CP_SOURCE_LANG SOURCE_LANG,
739 A.COLUMN_NAME PROMPT_LEFT,
740 A.COLUMN_NAME PROMPT_ABOVE,
741 CP_USER_ID CREATED_BY,
742 SYSDATE CREATION_DATE,
743 CP_USER_ID LAST_UPDATED_BY,
744 SYSDATE LAST_UPDATE_DATE
745 FROM FND_COLUMNS A,
746 FND_TABLES B
747 WHERE A.TABLE_ID = B.TABLE_ID
748 AND B.TABLE_NAME = CP_INTERFACE_TABLE_NAME
749 ORDER BY A.COLUMN_SEQUENCE;
750
751 CURSOR TABLE_COLS_DB(CP_APPLICATION_ID IN NUMBER,
752 CP_INTERFACE_CODE IN VARCHAR2,
753 CP_INTERFACE_TABLE_NAME IN VARCHAR2,
754 CP_LANGUAGE IN VARCHAR2,
755 CP_SOURCE_LANG IN VARCHAR2,
756 CP_USER_ID IN NUMBER,
757 CP_ORACLE_USER IN VARCHAR2) IS
758 SELECT CP_APPLICATION_ID APPLICATION_ID,
759 CP_INTERFACE_CODE INTERFACE_CODE,
760 1 OBJECT_VERSION_NUMBER,
761 A.COLUMN_ID SEQUENCE_NUM,
762 1 INTERFACE_COL_TYPE,
763 A.COLUMN_NAME INTERFACE_COL_NAME,
764 'Y' ENABLED_FLAG,
765 DECODE(A.NULLABLE,'N','Y','Y','N') REQUIRED_FLAG,
766 'Y' DISPLAY_FLAG,
767 'N' READ_ONLY_FLAG,
768 DECODE(A.NULLABLE,'N','Y','Y','N') NOT_NULL_FLAG,
769 'N' SUMMARY_FLAG,
770 'Y' MAPPING_ENABLED_FLAG,
771 DECODE(A.DATA_TYPE,'NUMBER',1,'VARCHAR2',2,'DATE',3) DATA_TYPE,
772 DECODE(A.DATA_TYPE,'NUMBER',A.DATA_LENGTH,'VARCHAR2',A.DATA_LENGTH,'DATE',0) FIELD_SIZE,
773 A.COLUMN_ID DISPLAY_ORDER,
774 CP_LANGUAGE LANGUAGE,
775 CP_SOURCE_LANG SOURCE_LANG,
776 A.COLUMN_NAME PROMPT_LEFT,
777 A.COLUMN_NAME PROMPT_ABOVE,
778 CP_USER_ID CREATED_BY,
779 SYSDATE CREATION_DATE,
780 CP_USER_ID LAST_UPDATED_BY,
781 SYSDATE LAST_UPDATE_DATE
782 FROM ALL_TAB_COLUMNS A,
783 ALL_TABLES B
784 WHERE A.TABLE_NAME = B.TABLE_NAME
785 AND A.OWNER = CP_ORACLE_USER
786 AND B.OWNER = CP_ORACLE_USER
787 AND B.TABLE_NAME = CP_INTERFACE_TABLE_NAME
788 ORDER BY COLUMN_ID;
789
790
791 VN_INTERFACE_EXISTS NUMBER;
792 VN_STANDARD_WHO_COUNT NUMBER;
793 VV_INTERFACE_CODE BNE_INTERFACES_B.INTERFACE_CODE%TYPE;
794
795 BEGIN
796 --
797
798 VV_INTERFACE_CODE := NULL;
799 P_INTERFACE_CODE := P_OBJECT_CODE||'_INTF';
800 VN_INTERFACE_EXISTS := 0;
801
802 IF NOT IS_VALID_APPL_ID(P_APPLICATION_ID) THEN
803 RAISE_APPLICATION_ERROR(-20000,'The supplied application id: ' || P_APPLICATION_ID || ' is invalid.');
804
805 ELSIF NOT IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
806 RAISE_APPLICATION_ERROR(-20000,'The object code: ' || P_OBJECT_CODE || ' is invalid.');
807
808 ELSE
809
810 -- Check that the OBJECT_CODE for this Interface is unique for the Application ID.
811
812 BEGIN
813 SELECT INTERFACE_CODE
814 INTO VV_INTERFACE_CODE
815 FROM BNE_INTERFACES_B
816 WHERE APPLICATION_ID = P_APPLICATION_ID
817 AND INTERFACE_CODE = P_INTERFACE_CODE;
818 EXCEPTION
819 WHEN NO_DATA_FOUND THEN NULL;
820 END;
821
822
823 -- Check to see if the Interface already exists for this Integrator
824 -- by querying for the Interface table name.
825
826 BEGIN
827 SELECT 1
828 INTO VN_INTERFACE_EXISTS
829 FROM BNE_INTERFACES_B BIB, BNE_INTERFACES_TL BIT
830 WHERE BIB.APPLICATION_ID = BIT.APPLICATION_ID
831 AND BIB.INTERFACE_CODE = BIT.INTERFACE_CODE
832 AND BIB.APPLICATION_ID = P_APPLICATION_ID
833 AND BIT.LANGUAGE = P_LANGUAGE
834 AND BIB.INTEGRATOR_APP_ID = P_APPLICATION_ID
835 AND BIB.INTEGRATOR_CODE = P_INTEGRATOR_CODE
836 AND BIB.INTERFACE_NAME = P_INTERFACE_TABLE_NAME;
837 EXCEPTION
838 WHEN NO_DATA_FOUND THEN NULL;
839 END;
840
841 IF ( VN_INTERFACE_EXISTS = 0) AND (VV_INTERFACE_CODE IS NULL) THEN
842
843 -- If the Interface Code is unique AND an Interface does not exist for this Interface table,
844 -- create the new Interface and Interface Columns.
845
846 -- Insert into the BNE_INTERFACES_B table
847
848 INSERT INTO BNE_INTERFACES_B
849 (APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID, INTEGRATOR_CODE,
850 INTERFACE_NAME, UPLOAD_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
851 VALUES
852 (P_APPLICATION_ID, P_INTERFACE_CODE, 1, P_APPLICATION_ID, P_INTEGRATOR_CODE,
853 P_INTERFACE_TABLE_NAME, 1, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
854
855 -- Insert into BNE_INTERFACES_TL table
856
857 INSERT INTO BNE_INTERFACES_TL
858 (APPLICATION_ID, INTERFACE_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
859 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
860 VALUES
861 (P_APPLICATION_ID, P_INTERFACE_CODE, P_LANGUAGE, P_SOURCE_LANG, P_INTERFACE_USER_NAME,
862 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
863
864
865 IF P_USE_FND_METADATA
866 THEN
867 FOR TABLE_COLUMN_REC IN TABLE_COLS_FND(P_APPLICATION_ID,
868 P_INTERFACE_CODE,
869 P_INTERFACE_TABLE_NAME,
870 P_LANGUAGE,
871 P_SOURCE_LANG,
872 P_USER_ID)
873 LOOP
874
875 -- Generate the interface columns in the BNE_INTERFACE_COLS_B and BNE_INTERFACE_COLS_TL tables
876
877 INSERT INTO BNE_INTERFACE_COLS_B
878 (APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, INTERFACE_COL_TYPE,
879 INTERFACE_COL_NAME, ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG, READ_ONLY_FLAG, NOT_NULL_FLAG,
880 SUMMARY_FLAG, MAPPING_ENABLED_FLAG, DATA_TYPE, FIELD_SIZE, DISPLAY_ORDER,
881 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
882 VALUES
883 (TABLE_COLUMN_REC.APPLICATION_ID,
884 TABLE_COLUMN_REC.INTERFACE_CODE,
885 TABLE_COLUMN_REC.OBJECT_VERSION_NUMBER,
886 TABLE_COLUMN_REC.SEQUENCE_NUM,
887 TABLE_COLUMN_REC.INTERFACE_COL_TYPE,
888 TABLE_COLUMN_REC.INTERFACE_COL_NAME,
889 TABLE_COLUMN_REC.ENABLED_FLAG,
890 TABLE_COLUMN_REC.REQUIRED_FLAG,
891 TABLE_COLUMN_REC.DISPLAY_FLAG,
892 TABLE_COLUMN_REC.READ_ONLY_FLAG,
893 TABLE_COLUMN_REC.NOT_NULL_FLAG,
894 TABLE_COLUMN_REC.SUMMARY_FLAG,
895 TABLE_COLUMN_REC.MAPPING_ENABLED_FLAG,
896 TABLE_COLUMN_REC.DATA_TYPE,
897 TABLE_COLUMN_REC.FIELD_SIZE,
898 TABLE_COLUMN_REC.DISPLAY_ORDER,
899 TABLE_COLUMN_REC.CREATED_BY,
900 TABLE_COLUMN_REC.CREATION_DATE,
901 TABLE_COLUMN_REC.LAST_UPDATED_BY,
902 TABLE_COLUMN_REC.LAST_UPDATE_DATE);
903
904 -- Generate the BNE_INTEFACE_COLS_TL columns
905
906 INSERT INTO BNE_INTERFACE_COLS_TL
907 (APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, LANGUAGE, SOURCE_LANG, PROMPT_LEFT,
908 PROMPT_ABOVE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
909 VALUES
910 (TABLE_COLUMN_REC.APPLICATION_ID,
911 TABLE_COLUMN_REC.INTERFACE_CODE,
912 TABLE_COLUMN_REC.SEQUENCE_NUM,
913 TABLE_COLUMN_REC.LANGUAGE,
914 TABLE_COLUMN_REC.SOURCE_LANG,
915 TABLE_COLUMN_REC.PROMPT_LEFT,
916 TABLE_COLUMN_REC.PROMPT_ABOVE,
917 TABLE_COLUMN_REC.CREATED_BY,
918 TABLE_COLUMN_REC.CREATION_DATE,
919 TABLE_COLUMN_REC.LAST_UPDATED_BY,
920 TABLE_COLUMN_REC.LAST_UPDATE_DATE);
921
922 EXIT WHEN TABLE_COLS_FND%NOTFOUND;
923 END LOOP;
924 ELSE
925 FOR TABLE_COLUMN_REC IN TABLE_COLS_DB(P_APPLICATION_ID,
926 P_INTERFACE_CODE,
927 P_INTERFACE_TABLE_NAME,
928 P_LANGUAGE,
929 P_SOURCE_LANG,
930 P_USER_ID,
931 P_INTERFACE_TABLE_OWNER)
932 LOOP
933
934 -- Generate the interface columns in the BNE_INTERFACE_COLS_B and BNE_INTERFACE_COLS_TL tables
935
936 INSERT INTO BNE_INTERFACE_COLS_B
937 (APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, INTERFACE_COL_TYPE,
938 INTERFACE_COL_NAME, ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG, READ_ONLY_FLAG, NOT_NULL_FLAG,
939 SUMMARY_FLAG, MAPPING_ENABLED_FLAG, DATA_TYPE, FIELD_SIZE, DISPLAY_ORDER,
940 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
941 VALUES
942 (TABLE_COLUMN_REC.APPLICATION_ID,
943 TABLE_COLUMN_REC.INTERFACE_CODE,
944 TABLE_COLUMN_REC.OBJECT_VERSION_NUMBER,
945 TABLE_COLUMN_REC.SEQUENCE_NUM,
946 TABLE_COLUMN_REC.INTERFACE_COL_TYPE,
947 TABLE_COLUMN_REC.INTERFACE_COL_NAME,
948 TABLE_COLUMN_REC.ENABLED_FLAG,
949 TABLE_COLUMN_REC.REQUIRED_FLAG,
950 TABLE_COLUMN_REC.DISPLAY_FLAG,
951 TABLE_COLUMN_REC.READ_ONLY_FLAG,
952 TABLE_COLUMN_REC.NOT_NULL_FLAG,
953 TABLE_COLUMN_REC.SUMMARY_FLAG,
954 TABLE_COLUMN_REC.MAPPING_ENABLED_FLAG,
955 TABLE_COLUMN_REC.DATA_TYPE,
956 TABLE_COLUMN_REC.FIELD_SIZE,
957 TABLE_COLUMN_REC.DISPLAY_ORDER,
958 TABLE_COLUMN_REC.CREATED_BY,
959 TABLE_COLUMN_REC.CREATION_DATE,
960 TABLE_COLUMN_REC.LAST_UPDATED_BY,
961 TABLE_COLUMN_REC.LAST_UPDATE_DATE);
962
963 -- Generate the BNE_INTEFACE_COLS_TL columns
964
965 INSERT INTO BNE_INTERFACE_COLS_TL
966 (APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, LANGUAGE, SOURCE_LANG, PROMPT_LEFT,
967 PROMPT_ABOVE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
968 VALUES
969 (TABLE_COLUMN_REC.APPLICATION_ID,
970 TABLE_COLUMN_REC.INTERFACE_CODE,
971 TABLE_COLUMN_REC.SEQUENCE_NUM,
972 TABLE_COLUMN_REC.LANGUAGE,
973 TABLE_COLUMN_REC.SOURCE_LANG,
974 TABLE_COLUMN_REC.PROMPT_LEFT,
975 TABLE_COLUMN_REC.PROMPT_ABOVE,
976 TABLE_COLUMN_REC.CREATED_BY,
977 TABLE_COLUMN_REC.CREATION_DATE,
978 TABLE_COLUMN_REC.LAST_UPDATED_BY,
979 TABLE_COLUMN_REC.LAST_UPDATE_DATE);
980
981 EXIT WHEN TABLE_COLS_DB%NOTFOUND;
982 END LOOP;
983 END IF;
984
985 BEGIN
986 SELECT COUNT(*)
987 INTO VN_STANDARD_WHO_COUNT
988 FROM BNE_INTERFACE_COLS_B BIC
989 WHERE APPLICATION_ID = P_APPLICATION_ID
990 AND INTERFACE_CODE = P_INTERFACE_CODE
991 AND INTERFACE_COL_NAME IN
992 ('CREATED_BY','LAST_UPDATED_BY','LAST_UPDATE_LOGIN','CREATION_DATE','LAST_UPDATE_DATE');
993 EXCEPTION
994 WHEN NO_DATA_FOUND THEN NULL;
995 END;
996
997 IF VN_STANDARD_WHO_COUNT = 5 THEN
998 UPDATE BNE_INTERFACE_COLS_B
999 SET DISPLAY_FLAG = 'N'
1000 ,REQUIRED_FLAG = 'Y'
1001 ,DEFAULT_TYPE = 'ENVIRONMENT'
1002 ,DEFAULT_VALUE = 'OAUSER.ID'
1003 WHERE APPLICATION_ID = P_APPLICATION_ID
1004 AND INTERFACE_CODE = P_INTERFACE_CODE
1005 AND INTERFACE_COL_NAME IN
1006 ('CREATED_BY','LAST_UPDATED_BY','LAST_UPDATE_LOGIN');
1007
1008 UPDATE BNE_INTERFACE_COLS_B
1009 SET DISPLAY_FLAG = 'N'
1010 ,REQUIRED_FLAG = 'Y'
1011 ,DEFAULT_TYPE = 'ENVIRONMENT'
1012 ,DEFAULT_VALUE = 'SYSDATE'
1013 WHERE APPLICATION_ID = P_APPLICATION_ID
1014 AND INTERFACE_CODE = P_INTERFACE_CODE
1015 AND INTERFACE_COL_NAME IN ('CREATION_DATE','LAST_UPDATE_DATE');
1016 END IF;
1017
1018 ELSE
1019 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_INTERFACE_CODE || ' has already been created');
1020
1021 END IF;
1022
1023 END IF;
1024
1025 END CREATE_INTERFACE_FOR_TABLE;
1026
1027 --------------------------------------------------------------------------------
1028 -- PROCEDURE: CREATE_INTERFACE_FOR_API --
1029 -- --
1030 -- DESCRIPTION: Procedure creates an interface in the Web ADI --
1031 -- repository for the first time. The Interface consists --
1032 -- of API Parameters. --
1033 -- This procedure inserts records into the BNE_INTERFACES, --
1034 -- BNE_INTERFACE_COLS/TL tables. --
1035 -- --
1036 -- --
1037 -- MODIFICATION HISTORY --
1038 -- Date Username Description --
1039 -- 22-MAY-2002 KPEET CREATED --
1040 -- 18-JUN-2002 KPEET Updated to call CREATE_PARAMETER_LIST. --
1041 -- 17-JUL-2002 KPEET Updated to call LINK_LIST_TO_INTERFACE. --
1042 -- 22-OCT-2002 KPEET Updated to use new primary keys in 8.3 schema. --
1043 -- 07-NOV-2002 KPEET Updated call to CREATE_PARAMETER_LIST to call --
1044 -- the same procedure by its new name: --
1045 -- CREATE_API_PARAMETER_LIST. --
1046 -- Updated to include P_UPLOAD_TYPE as a parameter. --
1047 -- Updated query that checks for existing Interface --
1048 -- to restrict by Integrator and Language. --
1049 -- 29-NOV-2002 KPEET Updated to set NOT_NULL_FLAG to 'N' instead of --
1050 -- DECODE(A.DEFAULT#,1,'N','Y'). This must be 'N' --
1051 -- because a NULL value can be passed in a required --
1052 -- API parameter. --
1053 -- 01-OCT-2003 TOBERMEI Changed decode of A.TYPE# to use '2' as default if --
1054 -- no matching value found --
1055 --------------------------------------------------------------------------------
1056
1057 PROCEDURE CREATE_INTERFACE_FOR_API (P_APPLICATION_ID IN NUMBER,
1058 P_OBJECT_CODE IN VARCHAR2,
1059 P_INTEGRATOR_CODE IN VARCHAR2,
1060 P_API_PACKAGE_NAME IN VARCHAR2,
1061 P_API_PROCEDURE_NAME IN VARCHAR2,
1062 P_INTERFACE_USER_NAME IN VARCHAR2,
1063 P_PARAM_LIST_NAME IN VARCHAR2,
1064 P_API_TYPE IN VARCHAR2,
1065 P_API_RETURN_TYPE IN VARCHAR2,
1066 P_UPLOAD_TYPE IN NUMBER,
1067 P_LANGUAGE IN VARCHAR2,
1068 P_SOURCE_LANG IN VARCHAR2,
1069 P_USER_ID IN NUMBER,
1070 P_PARAM_LIST_CODE OUT NOCOPY VARCHAR2,
1071 P_INTERFACE_CODE OUT NOCOPY VARCHAR2)
1072 IS
1073 CURSOR API_PARAMS_C (CP_APPLICATION_ID IN NUMBER,
1074 CP_INTERFACE_CODE IN VARCHAR2,
1075 CP_API_PACKAGE_NAME IN VARCHAR2,
1076 CP_API_PROCEDURE_NAME IN VARCHAR2,
1077 CP_OVERLOAD IN NUMBER,
1078 CP_LANGUAGE IN VARCHAR2,
1079 CP_SOURCE_LANG IN VARCHAR2,
1080 CP_USER_ID IN NUMBER) IS
1081 SELECT CP_APPLICATION_ID APPLICATION_ID,
1082 CP_INTERFACE_CODE INTERFACE_CODE,
1083 1 OBJECT_VERSION_NUMBER,
1084 A.SEQUENCE# SEQUENCE_NUM,
1085 1 INTERFACE_COL_TYPE,
1086 DECODE(A.TYPE#, 252, '2',
1087 12, '3',
1088 2, '1',
1089 1, '2',
1090 '2') DATA_TYPE,
1091 A.ARGUMENT INTERFACE_COL_NAME,
1092 'N' NOT_NULL_FLAG,
1093 'N' SUMMARY_FLAG,
1094 'Y' ENABLED_FLAG,
1095 'Y' DISPLAY_FLAG,
1096 'Y' MAPPING_ENABLED_FLAG,
1097 DECODE(DEFAULT#,NULL,DECODE(IN_OUT,NULL,'Y','N'),'N') REQUIRED_FLAG,
1098 'N' READ_ONLY_FLAG,
1099 (A.SEQUENCE# * 10) DISPLAY_ORDER,
1100 A.SEQUENCE# UPLOAD_PARAM_LIST_ITEM_NUM,
1101 SUBSTR(A.ARGUMENT,3) PROMPT_LEFT,
1102 SUBSTR(A.ARGUMENT,3) PROMPT_ABOVE,
1103 CP_LANGUAGE LANGUAGE,
1104 CP_SOURCE_LANG SOURCE_LANG,
1105 CP_USER_ID CREATED_BY,
1106 SYSDATE CREATION_DATE,
1107 CP_USER_ID LAST_UPDATED_BY,
1108 SYSDATE LAST_UPDATE_DATE
1109 FROM SYS.ARGUMENT$ A, USER_OBJECTS B
1110 WHERE A.OBJ# = B.OBJECT_ID
1111 AND B.OBJECT_NAME = CP_API_PACKAGE_NAME
1112 AND A.PROCEDURE$ = CP_API_PROCEDURE_NAME
1113 AND A.LEVEL# = 0
1114 AND A.OVERLOAD# = CP_OVERLOAD;
1115
1116 VV_INTERFACE_CODE BNE_INTERFACES_B.INTERFACE_CODE%TYPE;
1117 VN_INTERFACE_EXISTS NUMBER;
1118 VN_OVERLOAD NUMBER;
1119 BEGIN
1120 IF IS_VALID_APPL_ID(P_APPLICATION_ID) AND IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
1121
1122 VV_INTERFACE_CODE := NULL;
1123 P_INTERFACE_CODE := P_OBJECT_CODE||'_INTF';
1124 VN_INTERFACE_EXISTS := 0;
1125 P_PARAM_LIST_CODE := NULL;
1126
1127 -- Check that the OBJECT_CODE for this Interface is unique for the Application ID.
1128
1129 BEGIN
1130 SELECT INTERFACE_CODE
1131 INTO VV_INTERFACE_CODE
1132 FROM BNE_INTERFACES_B
1133 WHERE APPLICATION_ID = P_APPLICATION_ID
1134 AND INTERFACE_CODE = P_INTERFACE_CODE;
1135 EXCEPTION
1136 WHEN NO_DATA_FOUND THEN NULL;
1137 END;
1138
1139 -- Check to see if the Interface already exists by querying on the API Procedure name
1140
1141 BEGIN
1142 SELECT 1
1143 INTO VN_INTERFACE_EXISTS
1144 FROM BNE_INTERFACES_B BIB, BNE_INTERFACES_TL BIT
1145 WHERE BIB.APPLICATION_ID = BIT.APPLICATION_ID
1146 AND BIB.INTERFACE_CODE = BIT.INTERFACE_CODE
1147 AND BIB.INTEGRATOR_APP_ID = P_APPLICATION_ID
1148 AND BIB.INTEGRATOR_CODE = P_INTEGRATOR_CODE
1149 AND BIT.SOURCE_LANG = P_SOURCE_LANG
1150 AND BIT.LANGUAGE = P_LANGUAGE
1151 AND BIB.APPLICATION_ID = P_APPLICATION_ID
1152 AND BIB.INTERFACE_NAME = P_API_PROCEDURE_NAME;
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN NULL;
1155 END;
1156
1157 IF (VN_INTERFACE_EXISTS = 0) AND (VV_INTERFACE_CODE IS NULL) THEN
1158
1159 -- Retrieve the minimum Overload for the package.procedure
1160
1161 VN_OVERLOAD := 0;
1162 BEGIN
1163 SELECT MIN(A.OVERLOAD#)
1164 INTO VN_OVERLOAD
1165 FROM SYS.ARGUMENT$ A,
1166 USER_OBJECTS B
1167 WHERE A.OBJ# = B.OBJECT_ID
1168 AND B.OBJECT_NAME = P_API_PACKAGE_NAME
1169 AND A.PROCEDURE$ = P_API_PROCEDURE_NAME
1170 AND A.LEVEL# = 0;
1171 EXCEPTION
1172 WHEN NO_DATA_FOUND THEN NULL;
1173 END;
1174
1175 -- create the API parameter list
1176
1177 CREATE_API_PARAMETER_LIST (P_PARAM_LIST_NAME,
1178 P_API_PACKAGE_NAME,
1179 P_API_PROCEDURE_NAME,
1180 P_API_TYPE,
1181 P_API_RETURN_TYPE,
1182 P_LANGUAGE,
1183 P_SOURCE_LANG,
1184 P_USER_ID,
1185 VN_OVERLOAD,
1186 P_APPLICATION_ID,
1187 P_OBJECT_CODE,
1188 P_PARAM_LIST_CODE);
1189
1190 -- Create the interface in the BNE_INTERFACES_B table
1191
1192 INSERT INTO BNE_INTERFACES_B
1193 (APPLICATION_ID,
1194 INTERFACE_CODE,
1195 OBJECT_VERSION_NUMBER,
1196 INTEGRATOR_APP_ID,
1197 INTEGRATOR_CODE,
1198 INTERFACE_NAME,
1199 UPLOAD_TYPE,
1200 UPLOAD_PARAM_LIST_APP_ID,
1201 UPLOAD_PARAM_LIST_CODE,
1202 CREATED_BY,
1203 CREATION_DATE,
1204 LAST_UPDATED_BY,
1205 LAST_UPDATE_DATE)
1206 VALUES
1207 (P_APPLICATION_ID,
1208 P_INTERFACE_CODE,
1209 1,
1210 P_APPLICATION_ID,
1211 P_INTEGRATOR_CODE,
1212 P_API_PROCEDURE_NAME,
1213 P_UPLOAD_TYPE,
1214 P_APPLICATION_ID,
1215 P_PARAM_LIST_CODE,
1216 P_USER_ID,
1217 SYSDATE,
1218 P_USER_ID,
1219 SYSDATE);
1220
1221 -- Create the interface in the BNE_INTERFACES_TL table
1222
1223 INSERT INTO BNE_INTERFACES_TL
1224 (APPLICATION_ID,
1225 INTERFACE_CODE,
1226 LANGUAGE,
1227 SOURCE_LANG,
1228 USER_NAME,
1229 CREATED_BY,
1230 CREATION_DATE,
1231 LAST_UPDATED_BY,
1232 LAST_UPDATE_DATE)
1233 VALUES
1234 (P_APPLICATION_ID,
1235 P_INTERFACE_CODE,
1236 P_LANGUAGE,
1237 P_SOURCE_LANG,
1238 P_INTERFACE_USER_NAME,
1239 P_USER_ID,
1240 SYSDATE,
1241 P_USER_ID,
1242 SYSDATE);
1243
1244 FOR API_PARAM_REC IN API_PARAMS_C(P_APPLICATION_ID,
1245 P_INTERFACE_CODE,
1246 P_API_PACKAGE_NAME,
1247 P_API_PROCEDURE_NAME,
1248 VN_OVERLOAD,
1249 P_LANGUAGE,
1250 P_SOURCE_LANG,
1251 P_USER_ID) LOOP
1252
1253 -- Generate the Interface Columns in table BNE_INTERFACE_COLS_B
1254
1255 INSERT INTO BNE_INTERFACE_COLS_B
1256 (APPLICATION_ID,
1257 INTERFACE_CODE,
1258 OBJECT_VERSION_NUMBER,
1259 SEQUENCE_NUM,
1260 INTERFACE_COL_TYPE,
1261 DATA_TYPE,
1262 INTERFACE_COL_NAME,
1263 NOT_NULL_FLAG,
1264 SUMMARY_FLAG,
1265 ENABLED_FLAG,
1266 DISPLAY_FLAG,
1267 MAPPING_ENABLED_FLAG,
1268 REQUIRED_FLAG,
1269 READ_ONLY_FLAG,
1270 CREATED_BY,
1271 CREATION_DATE,
1272 LAST_UPDATED_BY,
1273 LAST_UPDATE_DATE,
1274 DISPLAY_ORDER,
1275 UPLOAD_PARAM_LIST_ITEM_NUM)
1276 VALUES
1277 (API_PARAM_REC.APPLICATION_ID,
1278 API_PARAM_REC.INTERFACE_CODE,
1279 API_PARAM_REC.OBJECT_VERSION_NUMBER,
1280 API_PARAM_REC.SEQUENCE_NUM,
1281 API_PARAM_REC.INTERFACE_COL_TYPE,
1282 API_PARAM_REC.DATA_TYPE,
1283 API_PARAM_REC.INTERFACE_COL_NAME,
1284 API_PARAM_REC.NOT_NULL_FLAG,
1285 API_PARAM_REC.SUMMARY_FLAG,
1286 API_PARAM_REC.ENABLED_FLAG,
1287 API_PARAM_REC.DISPLAY_FLAG,
1288 API_PARAM_REC.MAPPING_ENABLED_FLAG,
1289 API_PARAM_REC.REQUIRED_FLAG,
1290 API_PARAM_REC.READ_ONLY_FLAG,
1291 API_PARAM_REC.CREATED_BY,
1292 API_PARAM_REC.CREATION_DATE,
1293 API_PARAM_REC.LAST_UPDATED_BY,
1294 API_PARAM_REC.LAST_UPDATE_DATE,
1295 API_PARAM_REC.DISPLAY_ORDER,
1296 API_PARAM_REC.UPLOAD_PARAM_LIST_ITEM_NUM);
1297
1298 -- Generate the Interface columns in table BNE_INTERFACE_COLS_TL
1299
1300 INSERT INTO BNE_INTERFACE_COLS_TL
1301 (APPLICATION_ID,
1302 INTERFACE_CODE,
1303 SEQUENCE_NUM,
1304 LANGUAGE,
1305 SOURCE_LANG,
1306 PROMPT_LEFT,
1307 PROMPT_ABOVE,
1308 CREATED_BY,
1309 CREATION_DATE,
1310 LAST_UPDATED_BY,
1311 LAST_UPDATE_DATE)
1312 VALUES
1313 (API_PARAM_REC.APPLICATION_ID,
1314 API_PARAM_REC.INTERFACE_CODE,
1315 API_PARAM_REC.SEQUENCE_NUM,
1316 API_PARAM_REC.LANGUAGE,
1317 API_PARAM_REC.SOURCE_LANG,
1318 API_PARAM_REC.PROMPT_LEFT,
1319 API_PARAM_REC.PROMPT_ABOVE,
1320 API_PARAM_REC.CREATED_BY,
1321 API_PARAM_REC.CREATION_DATE,
1322 API_PARAM_REC.LAST_UPDATED_BY,
1323 API_PARAM_REC.LAST_UPDATE_DATE);
1324
1325 EXIT WHEN API_PARAMS_C%NOTFOUND;
1326
1327 END LOOP;
1328
1329 LINK_LIST_TO_INTERFACE (P_APPLICATION_ID,
1330 P_PARAM_LIST_CODE,
1331 P_APPLICATION_ID,
1332 P_INTERFACE_CODE);
1333
1334 END IF;
1335
1336 ELSE
1337 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
1338
1339
1340 END IF;
1341
1342 END CREATE_INTERFACE_FOR_API;
1343
1344 --------------------------------------------------------------------------------
1345 -- PROCEDURE: CREATE_INTERFACE_FOR_CONTENT --
1346 -- --
1347 -- DESCRIPTION: Procedure creates an interface in the Web ADI --
1348 -- repository for the first time. Including the columns --
1349 -- --
1350 -- PARAMETERS: --
1351 -- --
1352 -- MODIFICATION HISTORY --
1353 -- Date Username Description --
1354 -- 03-JUL-2002 KPEET CREATED --
1355 -- 22-OCT-2002 KPEET Updated to use new primary keys in 8.3 schema. --
1356 -- 31-OCT-2002 KPEET Updated query to check for existing Interface to --
1357 -- restrict query by CONTENT_CODE. --
1358 -- 08-NOV-2002 KPEET Updated queries to restrict by LANGUAGE. --
1359 -- 11-NOV-2002 KPEET Added query to check that the Content passed to --
1360 -- the procedure belongs to the same Integrator that --
1361 -- the Interface will be created for. --
1362 -- Updated the query that checks if the Interface --
1363 -- exists so that it restricts by Integrator as well. --
1364 --------------------------------------------------------------------------------
1365 PROCEDURE CREATE_INTERFACE_FOR_CONTENT (P_APPLICATION_ID IN NUMBER,
1366 P_OBJECT_CODE IN VARCHAR2,
1367 P_CONTENT_CODE IN VARCHAR2,
1368 P_INTEGRATOR_CODE IN VARCHAR2,
1369 P_LANGUAGE IN VARCHAR2,
1370 P_SOURCE_LANG IN VARCHAR2,
1371 P_USER_ID IN NUMBER,
1372 P_INTERFACE_CODE OUT NOCOPY VARCHAR2)
1373 IS
1374 VV_INTERFACE_CODE BNE_INTERFACES_B.INTERFACE_CODE%TYPE;
1375 VN_INTERFACE_EXISTS NUMBER;
1376 VN_VALID_CONTENT NUMBER;
1377 VV_DESCRIPTION VARCHAR2(240);
1378 BEGIN
1379 IF IS_VALID_APPL_ID(P_APPLICATION_ID) AND IS_VALID_OBJECT_CODE(P_OBJECT_CODE) THEN
1380
1381 VN_VALID_CONTENT := 0;
1382 VV_INTERFACE_CODE := NULL;
1383 P_INTERFACE_CODE := P_OBJECT_CODE||'_INTF';
1384 VN_INTERFACE_EXISTS := 0;
1385
1386 -- Ensure that the Content belongs to the Integrator that the Interface
1387 -- is being created for.
1388
1389 BEGIN
1390 SELECT 1
1391 INTO VN_VALID_CONTENT
1392 FROM BNE_CONTENTS_B
1393 WHERE APPLICATION_ID = P_APPLICATION_ID
1394 AND CONTENT_CODE = P_CONTENT_CODE
1395 AND INTEGRATOR_APP_ID = P_APPLICATION_ID
1396 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
1397 EXCEPTION
1398 WHEN NO_DATA_FOUND THEN NULL;
1399 END;
1400
1401
1402 -- Check that the OBJECT_CODE for this Interface is unique for the Application ID.
1403
1404 BEGIN
1405 SELECT INTERFACE_CODE
1406 INTO VV_INTERFACE_CODE
1407 FROM BNE_INTERFACES_B
1408 WHERE APPLICATION_ID = P_APPLICATION_ID
1409 AND INTERFACE_CODE = P_INTERFACE_CODE;
1410 EXCEPTION
1411 WHEN NO_DATA_FOUND THEN NULL;
1412 END;
1413
1414 -- Check to see if an Interface already exists for this Integrator by querying on the user name of the
1415 -- Interface, matching the Content Description
1416
1417 BEGIN
1418 SELECT 1
1419 INTO VN_INTERFACE_EXISTS
1420 FROM BNE_INTERFACES_B BI,
1421 BNE_INTERFACES_TL BITL,
1422 BNE_CONTENTS_B BC,
1423 BNE_CONTENTS_TL BCTL
1424 WHERE BI.APPLICATION_ID = P_APPLICATION_ID
1425 AND BI.APPLICATION_ID = BC.APPLICATION_ID
1426 AND BC.CONTENT_CODE = P_CONTENT_CODE
1427 AND BI.APPLICATION_ID = BITL.APPLICATION_ID
1428 AND BI.INTERFACE_CODE = BITL.INTERFACE_CODE
1429 AND BI.INTEGRATOR_APP_ID = P_APPLICATION_ID
1430 AND BI.INTEGRATOR_CODE = P_INTEGRATOR_CODE
1431 AND BC.APPLICATION_ID = BCTL.APPLICATION_ID
1432 AND BC.CONTENT_CODE = BCTL.CONTENT_CODE
1433 AND BITL.USER_NAME = BCTL.USER_NAME
1434 AND BCTL.LANGUAGE = P_LANGUAGE
1435 AND BITL.LANGUAGE = P_LANGUAGE;
1436 EXCEPTION
1437 WHEN NO_DATA_FOUND THEN NULL;
1438 END;
1439
1440 IF (VN_INTERFACE_EXISTS = 0) AND (VV_INTERFACE_CODE IS NULL) AND (VN_VALID_CONTENT = 1) THEN
1441
1442 -- Get the Description of the Content to use as the INTERFACE_NAME
1443
1444 BEGIN
1445 SELECT USER_NAME
1446 INTO VV_DESCRIPTION
1447 FROM BNE_CONTENTS_TL
1448 WHERE APPLICATION_ID = P_APPLICATION_ID
1449 AND CONTENT_CODE = P_CONTENT_CODE
1450 AND LANGUAGE = P_LANGUAGE;
1451 EXCEPTION
1452 WHEN NO_DATA_FOUND THEN NULL;
1453 END;
1454
1455 -- Insert into BNE_INTERFACES_B
1456 -- UPLOAD_TYPE of 4 is used to reference the constant BNE_UPLOAD_TYPE_REPORTING
1457
1458 INSERT INTO BNE_INTERFACES_B
1459 (APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID, INTEGRATOR_CODE,
1460 INTERFACE_NAME, UPLOAD_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1461 VALUES
1462 (P_APPLICATION_ID, P_INTERFACE_CODE, 1, P_APPLICATION_ID, P_INTEGRATOR_CODE,
1463 P_INTERFACE_CODE, 4, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
1464
1465 -- Insert into BNE_INTERFACES_TL
1466
1467 INSERT INTO BNE_INTERFACES_TL
1468 (APPLICATION_ID, INTERFACE_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
1469 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1470 VALUES
1471 (P_APPLICATION_ID, P_INTERFACE_CODE, P_LANGUAGE, P_SOURCE_LANG, VV_DESCRIPTION,
1472 P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
1473
1474 -- Generate the Interface Columns in the BNE_INTERFACE_COLS_B table.
1475
1476 INSERT INTO BNE_INTERFACE_COLS_B
1477 (APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, INTERFACE_COL_TYPE, INTERFACE_COL_NAME,
1478 ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG, READ_ONLY_FLAG, NOT_NULL_FLAG, SUMMARY_FLAG, DATA_TYPE, MAPPING_ENABLED_FLAG, DISPLAY_ORDER,
1479 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1480 SELECT P_APPLICATION_ID APPLICATION_ID,
1481 P_INTERFACE_CODE INTERFACE_CODE,
1482 1 OBJECT_VERSION_NUMBER,
1483 BCC.SEQUENCE_NUM SEQUENCE_NUM,
1484 1 INTERFACE_COL_TYPE,
1485 BCC.COL_NAME INTERFACE_COL_NAME,
1486 'Y' ENABLED_FLAG,
1487 'N' REQUIRED_FLAG,
1488 'Y' DISPLAY_FLAG,
1489 'N' READ_ONLY_FLAG,
1490 'N' NOT_NULL_FLAG,
1491 'N' SUMMARY_FLAG,
1492 2 DATA_TYPE,
1493 'Y' MAPPING_ENABLED_FLAG,
1494 (BCC.SEQUENCE_NUM * 10) DISPLAY_ORDER,
1495 P_USER_ID CREATED_BY,
1496 SYSDATE CREATION_DATE,
1497 P_USER_ID LAST_UPDATED_BY,
1498 SYSDATE LAST_UPDATE_DATE
1499 FROM BNE_CONTENT_COLS_B BCC
1500 WHERE BCC.APPLICATION_ID = P_APPLICATION_ID
1501 AND BCC.CONTENT_CODE = P_CONTENT_CODE;
1502
1503 -- Generate the Interface Columns in the BNE_INTERFACE_COLS_TL table
1504
1505 INSERT INTO BNE_INTERFACE_COLS_TL
1506 (APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, PROMPT_LEFT, PROMPT_ABOVE, LANGUAGE, SOURCE_LANG,
1507 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1508 SELECT P_APPLICATION_ID APPLICATION_ID,
1509 P_INTERFACE_CODE INTERFACE_CODE,
1510 BCC.SEQUENCE_NUM SEQUENCE_NUM,
1511 NVL(BCCTL.USER_NAME, BCC.COL_NAME) PROMPT_LEFT,
1512 NVL(BCCTL.USER_NAME, BCC.COL_NAME) PROMPT_ABOVE,
1513 P_LANGUAGE LANGUAGE,
1514 P_SOURCE_LANG SOURCE_LANG,
1515 P_USER_ID CREATED_BY,
1516 SYSDATE CREATION_DATE,
1517 P_USER_ID LAST_UPDATED_BY,
1518 SYSDATE LAST_UPDATE_DATE
1519 FROM BNE_CONTENT_COLS_B BCC,
1520 BNE_CONTENT_COLS_TL BCCTL
1521 WHERE BCC.APPLICATION_ID = BCCTL.APPLICATION_ID
1522 AND BCC.CONTENT_CODE = BCCTL.CONTENT_CODE
1523 AND BCC.SEQUENCE_NUM = BCCTL.SEQUENCE_NUM
1524 AND BCC.APPLICATION_ID = P_APPLICATION_ID
1525 AND BCC.CONTENT_CODE = P_CONTENT_CODE
1526 AND BCCTL.LANGUAGE = P_LANGUAGE;
1527
1528 END IF;
1529 ELSE
1530 RAISE_APPLICATION_ERROR(-20000,'Object code invalid, Integrator: ' || P_APPLICATION_ID || ':' || P_OBJECT_CODE || ' has already been created');
1531
1532
1533 END IF;
1534 END CREATE_INTERFACE_FOR_CONTENT;
1535
1536
1537
1538 --------------------------------------------------------------------------------
1539 -- PROCEDURE: UPSERT_INTERFACE_COLUMN --
1540 -- --
1541 -- DESCRIPTION: Procedure creates or updates a column in the --
1542 -- BNE_INTERFACE_COLS/_TL table. --
1543 -- --
1544 -- PARAMETERS: --
1545 -- --
1546 -- MODIFICATION HISTORY --
1547 -- Date Username Description --
1548 -- 22-APR-2002 JRICHARD CREATED --
1549 -- 22-OCT-2002 KPEET Updated to use new primary keys in 8.3 schema. --
1550 -- 24-OCT-2002 KPEET Removed VAL_MSG_COL as this columns has been --
1551 -- removed from the BNE_INTERFACE_COLS_B table. --
1552 -- 16-FEB-2005 DGROVES Bug 4187173 Added new columns. --
1553 --------------------------------------------------------------------------------
1554 PROCEDURE UPSERT_INTERFACE_COLUMN
1555 (P_APPLICATION_ID IN NUMBER, P_INTERFACE_CODE IN VARCHAR2,
1556 P_SEQUENCE_NUM IN NUMBER, P_INTERFACE_COL_TYPE IN NUMBER,
1557 P_INTERFACE_COL_NAME IN VARCHAR2, P_ENABLED_FLAG IN VARCHAR2,
1558 P_REQUIRED_FLAG IN VARCHAR2, P_DISPLAY_FLAG IN VARCHAR2,
1559 P_FIELD_SIZE IN NUMBER, P_DEFAULT_TYPE IN VARCHAR2,
1560 P_DEFAULT_VALUE IN VARCHAR2, P_SEGMENT_NUMBER IN NUMBER,
1561 P_GROUP_NAME IN VARCHAR2, P_OA_FLEX_CODE IN VARCHAR2,
1562 P_OA_CONCAT_FLEX IN VARCHAR2, P_READ_ONLY_FLAG IN VARCHAR2,
1563 P_VAL_TYPE IN VARCHAR2, P_VAL_ID_COL IN VARCHAR2,
1564 P_VAL_MEAN_COL IN VARCHAR2, P_VAL_DESC_COL IN VARCHAR2,
1565 P_VAL_OBJ_NAME IN VARCHAR2, P_VAL_ADDL_W_C IN VARCHAR2,
1566 P_DATA_TYPE IN NUMBER, P_NOT_NULL_FLAG IN VARCHAR2,
1567 P_VAL_COMPONENT_APP_ID IN NUMBER, P_VAL_COMPONENT_CODE IN VARCHAR2,
1568 P_SUMMARY_FLAG IN VARCHAR2, P_MAPPING_ENABLED_FLAG IN VARCHAR2,
1569 P_PROMPT_LEFT IN VARCHAR2, P_PROMPT_ABOVE IN VARCHAR2,
1570 P_USER_HINT IN VARCHAR2, P_USER_HELP_TEXT IN VARCHAR2,
1571 P_LANGUAGE IN VARCHAR2, P_SOURCE_LANG IN VARCHAR2,
1572 P_OA_FLEX_NUM IN VARCHAR2, P_OA_FLEX_APPLICATION_ID IN NUMBER,
1573 P_DISPLAY_ORDER IN NUMBER, P_UPLOAD_PARAM_LIST_ITEM_NUM IN NUMBER,
1574 P_EXPANDED_SQL_QUERY IN VARCHAR2, P_LOV_TYPE IN VARCHAR2,
1575 P_OFFLINE_LOV_ENABLED_FLAG IN VARCHAR2, P_VARIABLE_DATA_TYPE_CLASS IN VARCHAR2,
1576 P_USER_ID IN NUMBER)
1577 IS
1578 VN_NO_INTERFACE_COL_FLAG NUMBER;
1579 BEGIN
1580 -- Check the BNE_INTERFACE_COLS_B table to ensure that the record
1581 -- does not already exist
1582
1583 VN_NO_INTERFACE_COL_FLAG := 0;
1584
1585 BEGIN
1586 SELECT 1
1587 INTO VN_NO_INTERFACE_COL_FLAG
1588 FROM BNE_INTERFACE_COLS_B
1589 WHERE APPLICATION_ID = P_APPLICATION_ID
1590 AND INTERFACE_CODE = P_INTERFACE_CODE
1591 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
1592 EXCEPTION
1593 WHEN NO_DATA_FOUND THEN NULL;
1594 END;
1595
1596 -- If the Interface Column was not found then create
1597
1598 IF (VN_NO_INTERFACE_COL_FLAG = 0) THEN
1599
1600 -- Insert the required row in BNE_INTERFACE_COLS_B
1601
1602 INSERT INTO BNE_INTERFACE_COLS_B
1603 (APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, INTERFACE_COL_TYPE, INTERFACE_COL_NAME,
1604 ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG, READ_ONLY_FLAG, NOT_NULL_FLAG, SUMMARY_FLAG, MAPPING_ENABLED_FLAG,
1605 DATA_TYPE, FIELD_SIZE, DEFAULT_TYPE, DEFAULT_VALUE, SEGMENT_NUMBER, GROUP_NAME, OA_FLEX_CODE, OA_CONCAT_FLEX,
1606 VAL_TYPE, VAL_ID_COL, VAL_MEAN_COL, VAL_DESC_COL, VAL_OBJ_NAME, VAL_ADDL_W_C, VAL_COMPONENT_APP_ID,
1607 VAL_COMPONENT_CODE, OA_FLEX_NUM, OA_FLEX_APPLICATION_ID, DISPLAY_ORDER, UPLOAD_PARAM_LIST_ITEM_NUM,
1608 EXPANDED_SQL_QUERY, LOV_TYPE, OFFLINE_LOV_ENABLED_FLAG, VARIABLE_DATA_TYPE_CLASS, CREATED_BY, CREATION_DATE,
1609 LAST_UPDATED_BY, LAST_UPDATE_DATE)
1610 VALUES
1611 (P_APPLICATION_ID, P_INTERFACE_CODE, 1, P_SEQUENCE_NUM, P_INTERFACE_COL_TYPE, P_INTERFACE_COL_NAME,
1612 P_ENABLED_FLAG, P_REQUIRED_FLAG, P_DISPLAY_FLAG, NVL(P_READ_ONLY_FLAG,'N'), P_NOT_NULL_FLAG, NVL(P_SUMMARY_FLAG,'N'), P_MAPPING_ENABLED_FLAG,
1613 P_DATA_TYPE, P_FIELD_SIZE, P_DEFAULT_TYPE, P_DEFAULT_VALUE, P_SEGMENT_NUMBER, P_GROUP_NAME, P_OA_FLEX_CODE, P_OA_CONCAT_FLEX,
1614 P_VAL_TYPE, P_VAL_ID_COL, P_VAL_MEAN_COL, P_VAL_DESC_COL, P_VAL_OBJ_NAME, P_VAL_ADDL_W_C, P_VAL_COMPONENT_APP_ID,
1615 P_VAL_COMPONENT_CODE, P_OA_FLEX_NUM, P_OA_FLEX_APPLICATION_ID, P_DISPLAY_ORDER, P_UPLOAD_PARAM_LIST_ITEM_NUM,
1616 P_EXPANDED_SQL_QUERY, P_LOV_TYPE, P_OFFLINE_LOV_ENABLED_FLAG, P_VARIABLE_DATA_TYPE_CLASS, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
1617
1618 -- Insert the required row in BNE_INTERFACE_COLS_TL only if P_LANGUAGE is populated
1619
1620 IF (P_LANGUAGE IS NOT NULL) THEN
1621 INSERT INTO BNE_INTERFACE_COLS_TL
1622 (APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, LANGUAGE, SOURCE_LANG, USER_HINT, PROMPT_LEFT,
1623 USER_HELP_TEXT, PROMPT_ABOVE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
1624 VALUES
1625 (P_APPLICATION_ID, P_INTERFACE_CODE, P_SEQUENCE_NUM, P_LANGUAGE, P_SOURCE_LANG, P_USER_HINT, P_PROMPT_LEFT,
1626 P_USER_HELP_TEXT, P_PROMPT_ABOVE, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
1627 END IF;
1628 ELSE
1629 -- Update the required row in BNE_INTERFACE_COLS_B
1630
1631 UPDATE BNE_INTERFACE_COLS_B
1632 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1633 INTERFACE_COL_TYPE = P_INTERFACE_COL_TYPE,
1634 INTERFACE_COL_NAME = P_INTERFACE_COL_NAME,
1635 ENABLED_FLAG = P_ENABLED_FLAG,
1636 REQUIRED_FLAG = P_REQUIRED_FLAG,
1637 DISPLAY_FLAG = P_DISPLAY_FLAG,
1638 READ_ONLY_FLAG = NVL(P_READ_ONLY_FLAG,'N'),
1639 NOT_NULL_FLAG = P_NOT_NULL_FLAG,
1640 SUMMARY_FLAG = NVL(P_SUMMARY_FLAG,'N'),
1641 MAPPING_ENABLED_FLAG = P_MAPPING_ENABLED_FLAG,
1642 DATA_TYPE = P_DATA_TYPE,
1643 FIELD_SIZE = P_FIELD_SIZE,
1644 DEFAULT_TYPE = P_DEFAULT_TYPE,
1645 DEFAULT_VALUE = P_DEFAULT_VALUE,
1646 SEGMENT_NUMBER = P_SEGMENT_NUMBER,
1647 GROUP_NAME = P_GROUP_NAME,
1648 OA_FLEX_CODE = P_OA_FLEX_CODE,
1649 OA_CONCAT_FLEX = P_OA_CONCAT_FLEX,
1650 VAL_TYPE = P_VAL_TYPE,
1651 VAL_ID_COL = P_VAL_ID_COL,
1652 VAL_MEAN_COL = P_VAL_MEAN_COL,
1653 VAL_DESC_COL = P_VAL_DESC_COL,
1654 VAL_OBJ_NAME = P_VAL_OBJ_NAME,
1655 VAL_ADDL_W_C = P_VAL_ADDL_W_C,
1656 VAL_COMPONENT_APP_ID = P_VAL_COMPONENT_APP_ID,
1657 VAL_COMPONENT_CODE = P_VAL_COMPONENT_CODE,
1658 OA_FLEX_NUM = P_OA_FLEX_NUM,
1659 OA_FLEX_APPLICATION_ID = P_OA_FLEX_APPLICATION_ID,
1660 DISPLAY_ORDER = P_DISPLAY_ORDER,
1661 UPLOAD_PARAM_LIST_ITEM_NUM = P_UPLOAD_PARAM_LIST_ITEM_NUM,
1662 EXPANDED_SQL_QUERY = P_EXPANDED_SQL_QUERY,
1663 LOV_TYPE = P_LOV_TYPE,
1664 OFFLINE_LOV_ENABLED_FLAG = P_OFFLINE_LOV_ENABLED_FLAG,
1665 VARIABLE_DATA_TYPE_CLASS = P_VARIABLE_DATA_TYPE_CLASS,
1666 LAST_UPDATED_BY = P_USER_ID,
1667 LAST_UPDATE_LOGIN = P_USER_ID,
1668 LAST_UPDATE_DATE = SYSDATE
1669 WHERE APPLICATION_ID = P_APPLICATION_ID
1670 AND INTERFACE_CODE = P_INTERFACE_CODE
1671 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
1672
1673 -- Update the required row in BNE_INTERFACE_COLS_TL only where P_LANGUAGE is populated
1674
1675 IF (P_LANGUAGE IS NOT NULL) THEN
1676 UPDATE BNE_INTERFACE_COLS_TL
1677 SET USER_HINT = P_USER_HINT,
1678 PROMPT_LEFT = P_PROMPT_LEFT,
1679 USER_HELP_TEXT = P_USER_HELP_TEXT,
1680 PROMPT_ABOVE = P_PROMPT_ABOVE,
1681 LAST_UPDATED_BY = P_USER_ID,
1682 LAST_UPDATE_LOGIN = P_USER_ID,
1683 LAST_UPDATE_DATE = SYSDATE
1684 WHERE APPLICATION_ID = P_APPLICATION_ID
1685 AND INTERFACE_CODE = P_INTERFACE_CODE
1686 AND SEQUENCE_NUM = P_SEQUENCE_NUM
1687 AND LANGUAGE = P_LANGUAGE
1688 AND SOURCE_LANG = P_SOURCE_LANG;
1689 END IF;
1690 END IF;
1691 END UPSERT_INTERFACE_COLUMN;
1692
1693
1694 --------------------------------------------------------------------------------
1695 -- PROCEDURE: CREATE_INTERFACE_ALIAS_COLS --
1696 -- --
1697 -- DESCRIPTION: Procedure creates interface columns for view columns --
1698 -- which cannot be mapped to existing interface columns --
1699 -- for a given view and given interface. --
1700 -- --
1701 -- PARAMETERS: --
1702 -- --
1703 -- MODIFICATION HISTORY --
1704 -- Date Username Description --
1705 -- 04-DEC-2002 smcmilla Created. --
1706 -- 09-DEC-2002 kpeet Updated data type for P_USER_ID and CP_USER_ID to --
1707 -- be NUMBER instead of VARCHAR2. --
1708 --------------------------------------------------------------------------------
1709 PROCEDURE CREATE_INTERFACE_ALIAS_COLS
1710 (P_APPLICATION_ID IN NUMBER,
1711 P_INTERFACE_CODE IN VARCHAR2,
1712 P_LANGUAGE IN VARCHAR2,
1713 P_SOURCE_LANG IN VARCHAR2,
1714 P_USER_ID IN NUMBER,
1715 P_VIEW_NAME IN VARCHAR2,
1716 P_CONTENT_CODE IN VARCHAR2) IS
1717
1718 CURSOR VIEW_COLS_C (CP_APPLICATION_ID IN NUMBER
1719 ,CP_INTERFACE_CODE IN VARCHAR2
1720 ,CP_LANGUAGE IN VARCHAR2
1721 ,CP_SOURCE_LANG IN VARCHAR2
1722 ,CP_USER_ID IN NUMBER
1723 ,CP_ORACLE_USER IN VARCHAR2
1724 ,CP_VIEW_NAME IN VARCHAR2
1725 ) IS
1726 SELECT CP_APPLICATION_ID APPLICATION_ID,
1727 CP_INTERFACE_CODE INTERFACE_CODE,
1728 1 OBJECT_VERSION_NUMBER,
1729 2 INTERFACE_COL_TYPE,
1730 DECODE(ATC.DATA_TYPE, 'BOOLEAN', '2',
1731 'DATE', '3',
1732 'NUMBER', '1',
1733 'VARCHAR2', '2') DATA_TYPE,
1734 ATC.COLUMN_NAME INTERFACE_COL_NAME,
1735 'N' NOT_NULL_FLAG,
1736 'N' SUMMARY_FLAG,
1737 'Y' ENABLED_FLAG,
1738 'Y' DISPLAY_FLAG,
1739 'Y' MAPPING_ENABLED_FLAG,
1740 'N' REQUIRED_FLAG,
1741 'Y' READ_ONLY_FLAG,
1742 ATC.COLUMN_NAME PROMPT_LEFT,
1743 ATC.COLUMN_NAME PROMPT_ABOVE,
1744 CP_LANGUAGE LANGUAGE,
1745 CP_SOURCE_LANG SOURCE_LANG,
1746 CP_USER_ID CREATED_BY,
1747 SYSDATE CREATION_DATE,
1748 CP_USER_ID LAST_UPDATED_BY,
1749 SYSDATE LAST_UPDATE_DATE
1750 FROM ALL_TAB_COLUMNS ATC
1751 WHERE ATC.OWNER = CP_ORACLE_USER
1752 AND ATC.TABLE_NAME = CP_VIEW_NAME
1753 AND NOT EXISTS
1754 (SELECT 1
1755 FROM BNE_INTERFACE_COLS_B IC
1756 WHERE SUBSTR(IC.INTERFACE_COL_NAME,3) = ATC.COLUMN_NAME
1757 AND IC.INTERFACE_CODE = CP_INTERFACE_CODE);
1758 --
1759 VV_ORACLE_USER VARCHAR2(20);
1760 VN_SEQUENCE_NUM NUMBER;
1761 BEGIN
1762 --
1763 -- Determine ORACLE_USERNAME - usually APPS - need to limit selections
1764 -- from the ALL_TAB_COLUMNS table using this user because there can be multiple entries.
1765 VV_ORACLE_USER := NULL;
1766 BEGIN
1767 SELECT ORACLE_USERNAME
1768 INTO VV_ORACLE_USER
1769 FROM FND_ORACLE_USERID
1770 WHERE ORACLE_ID = 900;
1771 EXCEPTION
1772 WHEN NO_DATA_FOUND THEN
1773 VV_ORACLE_USER := 'APPS';
1774 END;
1775 --
1776 -- Determine max sequence number of existing interface_columns
1777 VN_SEQUENCE_NUM := 0;
1778 BEGIN
1779 SELECT MAX(SEQUENCE_NUM)
1780 INTO VN_SEQUENCE_NUM
1781 FROM BNE_INTERFACE_COLS_B
1782 WHERE INTERFACE_CODE = P_INTERFACE_CODE
1783 AND APPLICATION_ID = P_APPLICATION_ID;
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 -- Interface doesnt exist? Error?
1787 NULL;
1788 END;
1789 --
1790 -- Now create Alias columns
1791 FOR API_PARAM_REC IN VIEW_COLS_C(P_APPLICATION_ID,
1792 P_INTERFACE_CODE,
1793 P_LANGUAGE,
1794 P_SOURCE_LANG,
1795 P_USER_ID,
1796 VV_ORACLE_USER,
1797 P_VIEW_NAME) LOOP
1798 -- Keep note of number of cols we've inserted
1799 VN_SEQUENCE_NUM := VN_SEQUENCE_NUM + 1;
1800
1801 -- Generate the view Columns in table BNE_INTERFACE_COLS_B
1802 -- for those view columns not matching an api param
1803 INSERT INTO BNE_INTERFACE_COLS_B
1804 (APPLICATION_ID,
1805 INTERFACE_CODE,
1806 OBJECT_VERSION_NUMBER,
1807 SEQUENCE_NUM,
1808 INTERFACE_COL_TYPE,
1809 DATA_TYPE,
1810 INTERFACE_COL_NAME,
1811 NOT_NULL_FLAG,
1812 SUMMARY_FLAG,
1813 ENABLED_FLAG,
1814 DISPLAY_FLAG,
1815 MAPPING_ENABLED_FLAG,
1816 REQUIRED_FLAG,
1817 READ_ONLY_FLAG,
1818 CREATED_BY,
1819 CREATION_DATE,
1820 LAST_UPDATED_BY,
1821 LAST_UPDATE_DATE,
1822 DISPLAY_ORDER,
1823 UPLOAD_PARAM_LIST_ITEM_NUM)
1824 VALUES
1825 (API_PARAM_REC.APPLICATION_ID,
1826 API_PARAM_REC.INTERFACE_CODE,
1827 API_PARAM_REC.OBJECT_VERSION_NUMBER,
1828 VN_SEQUENCE_NUM,
1829 API_PARAM_REC.INTERFACE_COL_TYPE,
1830 API_PARAM_REC.DATA_TYPE,
1831 API_PARAM_REC.INTERFACE_COL_NAME,
1832 API_PARAM_REC.NOT_NULL_FLAG,
1833 API_PARAM_REC.SUMMARY_FLAG,
1834 API_PARAM_REC.ENABLED_FLAG,
1835 API_PARAM_REC.DISPLAY_FLAG,
1836 API_PARAM_REC.MAPPING_ENABLED_FLAG,
1837 API_PARAM_REC.REQUIRED_FLAG,
1838 API_PARAM_REC.READ_ONLY_FLAG,
1839 API_PARAM_REC.CREATED_BY,
1840 API_PARAM_REC.CREATION_DATE,
1841 API_PARAM_REC.LAST_UPDATED_BY,
1842 API_PARAM_REC.LAST_UPDATE_DATE,
1843 VN_SEQUENCE_NUM,
1844 VN_SEQUENCE_NUM);
1845
1846 -- Generate the Interface columns in table BNE_INTERFACE_COLS_TL
1847
1848 INSERT INTO BNE_INTERFACE_COLS_TL
1849 (APPLICATION_ID,
1850 INTERFACE_CODE,
1851 SEQUENCE_NUM,
1852 LANGUAGE,
1853 SOURCE_LANG,
1854 PROMPT_LEFT,
1855 PROMPT_ABOVE,
1856 CREATED_BY,
1857 CREATION_DATE,
1858 LAST_UPDATED_BY,
1859 LAST_UPDATE_DATE)
1860 VALUES
1861 (API_PARAM_REC.APPLICATION_ID,
1862 API_PARAM_REC.INTERFACE_CODE,
1863 VN_SEQUENCE_NUM,
1864 API_PARAM_REC.LANGUAGE,
1865 API_PARAM_REC.SOURCE_LANG,
1866 API_PARAM_REC.PROMPT_LEFT,
1867 API_PARAM_REC.PROMPT_ABOVE,
1868 API_PARAM_REC.CREATED_BY,
1869 API_PARAM_REC.CREATION_DATE,
1870 API_PARAM_REC.LAST_UPDATED_BY,
1871 API_PARAM_REC.LAST_UPDATE_DATE);
1872
1873 EXIT WHEN VIEW_COLS_C%NOTFOUND;
1874
1875 END LOOP;
1876
1877 END CREATE_INTERFACE_ALIAS_COLS;
1878
1879
1880 --------------------------------------------------------------------------------
1881 -- PROCEDURE: CREATE_API_INTERFACE_AND_MAP --
1882 -- --
1883 -- DESCRIPTION: Procedure creates an interface in the Web ADI --
1884 -- repository for the first time. Including the alias --
1885 -- columns for an update-style API. It is assumed that a --
1886 -- content will already exist, from which data may be --
1887 -- downloaded to provide the context for update. --
1888 -- --
1889 -- --
1890 -- MODIFICATION HISTORY --
1891 -- Date Username Description --
1892 -- 04-DEC-2002 smcmilla Created. --
1893 --------------------------------------------------------------------------------
1894 PROCEDURE CREATE_API_INTERFACE_AND_MAP
1895 (P_APPLICATION_ID IN NUMBER,
1896 P_OBJECT_CODE IN VARCHAR2,
1897 P_INTEGRATOR_CODE IN VARCHAR2,
1898 P_API_PACKAGE_NAME IN VARCHAR2,
1899 P_API_PROCEDURE_NAME IN VARCHAR2,
1900 P_INTERFACE_USER_NAME IN VARCHAR2,
1901 P_CONTENT_CODE IN VARCHAR2,
1902 P_VIEW_NAME IN VARCHAR2,
1903 P_PARAM_LIST_NAME IN VARCHAR2,
1904 P_API_TYPE IN VARCHAR2,
1905 P_API_RETURN_TYPE IN VARCHAR2,
1906 P_UPLOAD_TYPE IN NUMBER,
1907 P_LANGUAGE IN VARCHAR2,
1908 P_SOURCE_LANG IN VARCHAR2,
1909 P_USER_ID IN NUMBER,
1910 P_PARAM_LIST_CODE OUT NOCOPY VARCHAR2,
1911 P_INTERFACE_CODE OUT NOCOPY VARCHAR2,
1912 P_MAPPING_CODE OUT NOCOPY VARCHAR2) IS
1913 --
1914 BEGIN
1915 --
1916 -- Create interface for API
1917 CREATE_INTERFACE_FOR_API
1918 (P_APPLICATION_ID => P_APPLICATION_ID,
1919 P_OBJECT_CODE => P_OBJECT_CODE,
1920 P_INTEGRATOR_CODE => P_INTEGRATOR_CODE,
1921 P_API_PACKAGE_NAME => P_API_PACKAGE_NAME,
1922 P_API_PROCEDURE_NAME => p_API_PROCEDURE_NAME,
1923 P_INTERFACE_USER_NAME => P_INTERFACE_USER_NAME,
1924 P_PARAM_LIST_NAME => P_PARAM_LIST_NAME,
1925 P_API_TYPE => P_API_TYPE,
1926 P_API_RETURN_TYPE => P_API_RETURN_TYPE,
1927 P_UPLOAD_TYPE => P_UPLOAD_TYPE,
1928 P_LANGUAGE => P_LANGUAGE,
1929 P_SOURCE_LANG => P_SOURCE_LANG,
1930 P_USER_ID => P_USER_ID,
1931 P_PARAM_LIST_CODE => P_PARAM_LIST_CODE,
1932 P_INTERFACE_CODE => P_INTERFACE_CODE);
1933 --
1934 -- Add additional columns for view columns which do not have matching api columns
1935 --
1936 CREATE_INTERFACE_ALIAS_COLS
1937 (P_APPLICATION_ID => P_APPLICATION_ID
1938 ,P_INTERFACE_CODE => P_INTERFACE_CODE
1939 ,P_LANGUAGE => P_LANGUAGE
1940 ,P_SOURCE_LANG => P_SOURCE_LANG
1941 ,P_USER_ID => P_USER_ID
1942 ,P_VIEW_NAME => P_VIEW_NAME
1943 ,P_CONTENT_CODE => P_CONTENT_CODE);
1944 --
1945 -- Create Mapping between content(view) and interface(API)
1946 --
1947 BNE_CONTENT_UTILS.CREATE_CONTENT_TO_API_MAP
1948 (P_APPLICATION_ID => P_APPLICATION_ID
1949 ,P_OBJECT_CODE => P_OBJECT_CODE
1950 ,P_INTEGRATOR_CODE => P_INTEGRATOR_CODE
1951 ,P_CONTENT_CODE => P_CONTENT_CODE
1952 ,P_INTERFACE_CODE => P_INTERFACE_CODE
1953 ,P_LANGUAGE => P_LANGUAGE
1954 ,P_SOURCE_LANGUAGE => P_SOURCE_LANG
1955 ,P_USER_ID => P_USER_ID
1956 ,P_MAPPING_CODE => P_MAPPING_CODE);
1957 --
1958 END CREATE_API_INTERFACE_AND_MAP;
1959 ------------------------------------------------------------------------
1960
1961
1962 --------------------------------------------------------------------------------
1963 -- PROCEDURE: CREATE_DEFAULT_LAYOUT --
1964 -- --
1965 -- DESCRIPTION: Procedure creates an default layout for an integrator. --
1966 -- RULES: --
1967 -- A layout will be created that places all required columns for the interface--
1968 -- in the LINES section of a new layout. If the layout code already exists, --
1969 -- return an error unless the FORCE parameter is set to true. If the FORCE --
1970 -- parameter is true, delete existing layout (if there) and create new layout.--
1971 -- The user name of the layout will be "Default". The code of the layout will --
1972 -- be the OBJECT_CODE with "_DFLT" appended. If the interface contains no --
1973 -- required columns, place all columns in the lines section. If the --
1974 -- ALL_COLUMNS parameter is true, place all columns in the lines section. -- --
1975 -- --
1976 -- MODIFICATION HISTORY --
1977 -- Date Username Description --
1978 -- 26-JUL-2004 FPOCKNEE Created. --
1979 --------------------------------------------------------------------------------
1980 PROCEDURE CREATE_DEFAULT_LAYOUT
1981 (P_APPLICATION_ID IN NUMBER, -- application id for the bne interface
1982 P_OBJECT_CODE IN VARCHAR2, -- will be used to construct the layout name "<object code>_DFLT"
1983 P_INTEGRATOR_CODE IN VARCHAR2, -- integrator associated with the interface
1984 P_INTERFACE_CODE IN VARCHAR2, -- interface on which to build the layout
1985 P_USER_ID IN NUMBER, -- user_id to use in the WHO columns
1986 P_FORCE IN BOOLEAN, -- when true - all existing layout data will be removed before recreating
1987 P_ALL_COLUMNS IN BOOLEAN, -- All columns will be included in the layout when true (otherwise only required columns are included)
1988 P_LAYOUT_CODE IN OUT NOCOPY VARCHAR2) IS
1989
1990 -- CURSOR TO RETRIEVE INTERFACE COLUMNS
1991 CURSOR INTERFACE_COLS_C (CP_APPLICATION_ID IN NUMBER,
1992 CP_INTERFACE_CODE IN VARCHAR2,
1993 CP_REQUIRED_FLAG IN VARCHAR2) IS
1994 SELECT APPLICATION_ID,
1995 INTERFACE_CODE,
1996 SEQUENCE_NUM
1997 FROM BNE_INTERFACE_COLS_VL
1998 WHERE APPLICATION_ID = CP_APPLICATION_ID
1999 AND INTERFACE_CODE = CP_INTERFACE_CODE
2000 AND REQUIRED_FLAG = CP_REQUIRED_FLAG;
2001
2002
2003 VV_LAYOUT_NAME VARCHAR2(15);
2004 VR_ROW_ID ROWID;
2005 VN_COLS_INSERTED NUMBER;
2006 VN_ROW_COUNT NUMBER;
2007
2008 BEGIN
2009 -- CREATE A LAYOUT CODE IF ONE IS NOT SUPPLIED
2010 if P_LAYOUT_CODE = '' or P_LAYOUT_CODE is NULL then
2011 P_LAYOUT_CODE := P_OBJECT_CODE || '_DFLT';
2012 end if;
2013
2014 VV_LAYOUT_NAME := 'Default';
2015
2016 if (P_FORCE) then
2017 -- REMOVE EXISTING DATA WHERE FORCE IS SPECIFIED
2018 BEGIN
2019 BNE_LAYOUTS_PKG.DELETE_ROW(P_APPLICATION_ID,P_LAYOUT_CODE);
2020
2021 DELETE BNE_LAYOUT_LOBS
2022 WHERE APPLICATION_ID = P_APPLICATION_ID
2023 AND LAYOUT_CODE = P_LAYOUT_CODE;
2024
2025 DELETE BNE_LAYOUT_BLOCKS_B
2026 WHERE APPLICATION_ID = P_APPLICATION_ID
2027 AND LAYOUT_CODE = P_LAYOUT_CODE;
2028
2029 DELETE BNE_LAYOUT_BLOCKS_TL
2030 WHERE APPLICATION_ID = P_APPLICATION_ID
2031 AND LAYOUT_CODE = P_LAYOUT_CODE;
2032
2033 DELETE BNE_LAYOUT_COLS
2034 WHERE APPLICATION_ID = P_APPLICATION_ID
2035 AND LAYOUT_CODE = P_LAYOUT_CODE;
2036
2037 DELETE BNE_LAYOUT_LOBS
2038 WHERE APPLICATION_ID = P_APPLICATION_ID
2039 AND LAYOUT_CODE = P_LAYOUT_CODE;
2040
2041 END;
2042
2043 else
2044
2045 VN_ROW_COUNT:=0;
2046 BEGIN
2047 SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUTS_B
2048 WHERE APPLICATION_ID = P_APPLICATION_ID
2049 AND LAYOUT_CODE = P_LAYOUT_CODE;
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND THEN NULL;
2052 END;
2053 IF VN_ROW_COUNT > 0 THEN
2054 RAISE_APPLICATION_ERROR(-20000,'Rows already exist in BNE_LAYOUTS_B for layout:' || P_APPLICATION_ID || ':' || P_LAYOUT_CODE || '. Use the FORCE flag to overide.');
2055 END IF;
2056
2057 VN_ROW_COUNT:=0;
2058 BEGIN
2059 SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_LOBS
2060 WHERE APPLICATION_ID = P_APPLICATION_ID
2061 AND LAYOUT_CODE = P_LAYOUT_CODE;
2062 EXCEPTION
2063 WHEN NO_DATA_FOUND THEN NULL;
2064 END;
2065 IF VN_ROW_COUNT > 0 THEN
2066 RAISE_APPLICATION_ERROR(-20000,'Rows already exist in BNE_LAYOUT_LOBS for layout:' || P_APPLICATION_ID || ':' || P_LAYOUT_CODE || '. Use the FORCE flag to overide.');
2067 END IF;
2068
2069 VN_ROW_COUNT:=0;
2070 BEGIN
2071 SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_BLOCKS_B
2072 WHERE APPLICATION_ID = P_APPLICATION_ID
2073 AND LAYOUT_CODE = P_LAYOUT_CODE;
2074 EXCEPTION
2075 WHEN NO_DATA_FOUND THEN NULL;
2076 END;
2077 IF VN_ROW_COUNT > 0 THEN
2078 RAISE_APPLICATION_ERROR(-20000,'Rows already exist in BNE_LAYOUT_BLOCKS_B for layout:' || P_APPLICATION_ID || ':' || P_LAYOUT_CODE || '. Use the FORCE flag to overide.');
2079 END IF;
2080
2081 VN_ROW_COUNT:=0;
2082 BEGIN
2083 SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_BLOCKS_TL
2084 WHERE APPLICATION_ID = P_APPLICATION_ID
2085 AND LAYOUT_CODE = P_LAYOUT_CODE;
2086 EXCEPTION
2087 WHEN NO_DATA_FOUND THEN NULL;
2088 END;
2089 IF VN_ROW_COUNT > 0 THEN
2090 RAISE_APPLICATION_ERROR(-20000,'Rows already exist in BNE_LAYOUT_BLOCKS_TL for layout:' || P_APPLICATION_ID || ':' || P_LAYOUT_CODE || '. Use the FORCE flag to overide.');
2091 END IF;
2092
2093 VN_ROW_COUNT:=0;
2094 BEGIN
2095 SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_COLS
2096 WHERE APPLICATION_ID = P_APPLICATION_ID
2097 AND LAYOUT_CODE = P_LAYOUT_CODE;
2098 EXCEPTION
2099 WHEN NO_DATA_FOUND THEN NULL;
2100 END;
2101 IF VN_ROW_COUNT > 0 THEN
2102 RAISE_APPLICATION_ERROR(-20000,'Rows already exist in BNE_LAYOUT_COLS for layout:' || P_APPLICATION_ID || ':' || P_LAYOUT_CODE || '. Use the FORCE flag to overide.');
2103 END IF;
2104
2105 VN_ROW_COUNT:=0;
2106 BEGIN
2107 SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_LOBS
2108 WHERE APPLICATION_ID = P_APPLICATION_ID
2109 AND LAYOUT_CODE = P_LAYOUT_CODE;
2110 EXCEPTION
2111 WHEN NO_DATA_FOUND THEN NULL;
2112 END;
2113 IF VN_ROW_COUNT > 0 THEN
2114 RAISE_APPLICATION_ERROR(-20000,'Rows already exist in BNE_LAYOUT_LOBS for layout:' || P_APPLICATION_ID || ':' || P_LAYOUT_CODE || '. Use the FORCE flag to overide.');
2115 END IF;
2116
2117 end if;
2118
2119
2120 -- NEW LAYOUT
2121 BNE_LAYOUTS_PKG.Insert_Row(
2122 VR_ROW_ID,
2123 P_APPLICATION_ID, --APPLICATION_ID
2124 P_LAYOUT_CODE, --LAYOUT_CODE
2125 1, --OBJECT_VERSION_NUMBER
2126 231, --STYLESHEET_APP_ID
2127 'DEFAULT', --STYLESHEET_CODE
2128 P_APPLICATION_ID, --INTEGRATOR_APP_ID
2129 P_INTEGRATOR_CODE, --INTEGRATOR_CODE
2130 NULL, --STYLE
2131 'BNE_PAGE', --STYLE_CLASS
2132 'N', --REPORTING_FLAG
2133 NULL, --REPORTING_INTERFACE_APP_ID
2134 NULL, --REPORTING_INTERFACE_CODE
2135 'Default', --USER_NAME
2136 SYSDATE, --CREATION_DATE
2137 P_USER_ID, --CREATED_BY
2138 SYSDATE, --LAST_UPDATE_DATE
2139 P_USER_ID, --LAST_UPDATED_BY
2140 P_USER_ID, --LAST_UPDATE_LOGIN
2141 NULL, --CREATE_DOC_LIST_APP_ID
2142 NULL --CREATE_DOC_LIST_CODE
2143 );
2144
2145 -- NEW LAYOUT BLOCK - ONLY CREATING THE LINES REGION
2146
2147 BNE_LAYOUT_BLOCKS_PKG.Insert_Row(
2148 VR_ROW_ID,
2149 P_APPLICATION_ID, --APPLICATION_ID
2150 P_LAYOUT_CODE, --LAYOUT_CODE
2151 3, --BLOCK_ID
2152 1, --OBJECT_VERSION_NUMBER
2153 NULL, --PARENT_ID
2154 'LINE', --LAYOUT_ELEMENT
2155 'BNE_LINES', --STYLE_CLASS
2156 NULL, --STYLE
2157 'BNE_LINES_ROW', --ROW_STYLE_CLASS
2158 NULL, --ROW_STYLE
2159 NULL, --COL_STYLE_CLASS
2160 NULL, --COL_STYLE
2161 'Y', --PROMPT_DISPLAYED_FLAG
2162 'BNE_LINES_HEADER', --PROMPT_STYLE_CLASS
2163 NULL, --PROMPT_STYLE
2164 'N', --HINT_DISPLAYED_FLAG
2165 'BNE_LINES_HINT', --HINT_STYLE_CLASS
2166 NULL, --HINT_STYLE
2167 'VERTICAL', --ORIENTATION
2168 'TABLE_FLOW', --LAYOUT_CONTROL
2169 'Y', --DISPLAY_FLAG
2170 10, --BLOCKSIZE
2171 1, --MINSIZE
2172 1, --MAXSIZE
2173 30, --SEQUENCE_NUM
2174 NULL, --PROMPT_COLSPAN
2175 NULL, --HINT_COLSPAN
2176 NULL, --ROW_COLSPAN
2177 'BNE_LINES_TOTAL', --SUMMARY_STYLE_CLASS
2178 NULL, --SUMMARY_STYLE
2179 'Line', --USER_NAME
2180 SYSDATE, --CREATION_DATE
2181 P_USER_ID, --CREATED_BY
2182 SYSDATE, --LAST_UPDATE_DATE
2183 P_USER_ID, --LAST_UPDATED_BY
2184 P_USER_ID, --LAST_UPDATE_LOGIN
2185 NULL, --PROMPT_ABOVE
2186 'TITLE', -- TITLE_STYLE_CLASS
2187 NULL --TITLE_STYLE
2188 );
2189
2190 -- NEW LAYOUT COLS - FOR REQUIRED COLUMNS
2191 VN_COLS_INSERTED:=0;
2192 FOR INTERFACE_COLS_REC IN INTERFACE_COLS_C(P_APPLICATION_ID,
2193 P_INTERFACE_CODE,
2194 'Y') LOOP
2195 BNE_LAYOUT_COLS_PKG.Insert_Row(
2196 VR_ROW_ID,
2197 P_APPLICATION_ID, --APPLICATION_ID
2198 P_LAYOUT_CODE, --LAYOUT_CODE
2199 3, --BLOCK_ID
2200 (INTERFACE_COLS_REC.SEQUENCE_NUM * 10), --SEQUENCE_NUM
2201 1, --OBJECT_VERSION_NUMBER
2202 P_APPLICATION_ID, --INTERFACE_APP_ID
2203 P_INTERFACE_CODE, --INTERFACE_CODE
2204 INTERFACE_COLS_REC.SEQUENCE_NUM, --INTERFACE_SEQ_NUM
2205 NULL, --STYLE_CLASS
2206 NULL, --HINT_STYLE
2207 NULL, --HINT_STYLE_CLASS
2208 NULL, --PROMPT_STYLE
2209 NULL, --PROMPT_STYLE_CLASS
2210 NULL, --DEFAULT_TYPE
2211 NULL, --DEFAULT_VALUE
2212 NULL, --STYLE
2213 SYSDATE, --CREATION_DATE
2214 P_USER_ID, --CREATED_BY
2215 SYSDATE, --LAST_UPDATE_DATE
2216 P_USER_ID, --LAST_UPDATED_BY
2217 P_USER_ID, --LAST_UPDATE_LOGIN
2218 NULL, --DISPLAY_WIDTH
2219 'N' --READ_ONLY_FLAG
2220 );
2221 VN_COLS_INSERTED:=VN_COLS_INSERTED+1;
2222 END LOOP;
2223
2224 -- NEW LAYOUT COLS - FOR NON-REQUIRED COLUMNS
2225 if (VN_COLS_INSERTED = 0 or P_ALL_COLUMNS) then
2226 FOR INTERFACE_COLS_REC IN INTERFACE_COLS_C(P_APPLICATION_ID,
2227 P_INTERFACE_CODE,
2228 'N') LOOP
2229 BNE_LAYOUT_COLS_PKG.Insert_Row(
2230 VR_ROW_ID,
2231 P_APPLICATION_ID, --APPLICATION_ID
2232 P_LAYOUT_CODE, --LAYOUT_CODE
2233 3, --BLOCK_ID
2234 (INTERFACE_COLS_REC.SEQUENCE_NUM * 10), --SEQUENCE_NUM
2235 1, --OBJECT_VERSION_NUMBER
2236 P_APPLICATION_ID, --INTERFACE_APP_ID
2237 P_INTERFACE_CODE, --INTERFACE_CODE
2238 INTERFACE_COLS_REC.SEQUENCE_NUM, --INTERFACE_SEQ_NUM
2239 NULL, --STYLE_CLASS
2240 NULL, --HINT_STYLE
2241 NULL, --HINT_STYLE_CLASS
2242 NULL, --PROMPT_STYLE
2243 NULL, --PROMPT_STYLE_CLASS
2244 NULL, --DEFAULT_TYPE
2245 NULL, --DEFAULT_VALUE
2246 NULL, --STYLE
2247 SYSDATE, --CREATION_DATE
2248 P_USER_ID, --CREATED_BY
2249 SYSDATE, --LAST_UPDATE_DATE
2250 P_USER_ID, --LAST_UPDATED_BY
2251 P_USER_ID, --LAST_UPDATE_LOGIN
2252 NULL, --DISPLAY_WIDTH
2253 'N' --READ_ONLY_FLAG
2254 );
2255 END LOOP;
2256 end if;
2257
2258 END CREATE_DEFAULT_LAYOUT;
2259
2260 PROCEDURE ADD_FLEX_LOV_PARAMETER_LIST
2261 (P_APPLICATION_SHORT_NAME IN VARCHAR2,
2262 P_PARAM_LIST_CODE IN VARCHAR2,
2263 P_PARAM_LIST_NAME IN VARCHAR2,
2264 P_WINDOW_CAPTION IN VARCHAR2,
2265 P_WINDOW_WIDTH IN NUMBER,
2266 P_WINDOW_HEIGHT IN NUMBER,
2267 P_EFFECTIVE_DATE_COL IN VARCHAR2, -- date col in sheet to get effective date.
2268 P_VRULE IN VARCHAR2,
2269 P_USER_NAME IN VARCHAR2)
2270 IS
2271 VV_PARAM_DEFN_CODE VARCHAR2(30);
2272 VV_PARAM_SEQ_NUM NUMBER(15);
2273 BEGIN
2274 VV_PARAM_SEQ_NUM := 0;
2275
2276 BNE_PARAM_LISTS_PKG.LOAD_ROW(
2277 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2278 x_param_list_code => P_PARAM_LIST_CODE,
2279 x_object_version_number => 1,
2280 x_persistent_flag => 'Y',
2281 x_comments => 'Auto Generated Component Parameter List',
2282 x_attribute_asn => NULL,
2283 x_attribute_code => NULL,
2284 x_list_resolver => NULL,
2285 x_user_tip => NULL,
2286 x_prompt_left => NULL,
2287 x_prompt_above => NULL,
2288 x_user_name => P_PARAM_LIST_NAME,
2289 x_owner => P_USER_NAME,
2290 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2291 x_custom_mode => NULL
2292 );
2293
2294 IF P_EFFECTIVE_DATE_COL IS NOT NULL
2295 THEN
2296 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2297 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2298 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2299 x_param_list_code => P_PARAM_LIST_CODE,
2300 x_sequence_num => VV_PARAM_SEQ_NUM,
2301 x_object_version_number => 1,
2302 x_param_defn_asn => NULL,
2303 x_param_defn_code => NULL,
2304 x_param_name => 'sheet:effectivedate',
2305 x_attribute_asn => NULL,
2306 x_attribute_code => NULL,
2307 x_string_value => P_EFFECTIVE_DATE_COL,
2308 x_date_value => NULL,
2309 x_number_value => NULL,
2310 x_boolean_value_flag => NULL,
2311 x_formula_value => NULL,
2312 x_desc_value => 'Interface column containing effective date for flex LOV.',
2313 x_owner => P_USER_NAME,
2314 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2315 x_custom_mode => NULL
2316 );
2317 END IF;
2318
2319 IF P_VRULE IS NOT NULL
2320 THEN
2321 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2322 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2323 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2324 x_param_list_code => P_PARAM_LIST_CODE,
2325 x_sequence_num => VV_PARAM_SEQ_NUM,
2326 x_object_version_number => 1,
2327 x_param_defn_asn => NULL,
2328 x_param_defn_code => NULL,
2329 x_param_name => 'field:vrule',
2330 x_attribute_asn => NULL,
2331 x_attribute_code => NULL,
2332 x_string_value => P_VRULE,
2333 x_date_value => NULL,
2334 x_number_value => NULL,
2335 x_boolean_value_flag => NULL,
2336 x_formula_value => NULL,
2337 x_desc_value => 'Text of VRULE for flex LOV.',
2338 x_owner => P_USER_NAME,
2339 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2340 x_custom_mode => NULL
2341 );
2342 END IF;
2343
2344 IF P_WINDOW_CAPTION IS NOT NULL
2345 THEN
2346 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2347 VV_PARAM_DEFN_CODE := P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM)||'D';
2348 BNE_PARAM_DEFNS_PKG.LOAD_ROW(
2349 x_param_defn_asn => P_APPLICATION_SHORT_NAME,
2350 x_param_defn_code => VV_PARAM_DEFN_CODE,
2351 x_object_version_number => 1,
2352 x_param_name => 'window-caption',
2353 x_param_source => 'Component LOV',
2354 x_param_category => '5', -- Data
2355 x_datatype => '1', -- String
2356 x_attribute_asn => NULL,
2357 x_attribute_code => NULL,
2358 x_param_resolver => NULL,
2359 x_default_required_flag => 'N',
2360 x_default_visible_flag => 'Y', -- Allow it to be seen when teting the list
2361 x_default_user_modifyable_flag => 'Y', -- Allow it to be modified when testing the list
2362 x_default_string => P_WINDOW_CAPTION,
2363 x_default_string_trans_flag => 'Y',
2364 x_default_date => NULL,
2365 x_default_number => NULL,
2366 x_default_boolean_flag => NULL,
2367 x_default_formula => NULL,
2368 x_val_type => '1', -- None
2369 x_val_value => NULL,
2370 x_max_size => '100',
2371 x_display_type => '4', -- Text Area
2372 x_display_style => '1', -- None
2373 x_display_size => '20',
2374 x_help_url => NULL,
2375 x_format_mask => NULL,
2376 x_user_name => 'PARAMETER DEFINITION FOR '||P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM), -- upper case to avoid translation
2377 x_default_desc => 'Parameter Definition for '||P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM), -- upper case to avoid translation,
2378 x_prompt_left => 'window-caption',
2379 x_prompt_above => NULL,
2380 x_user_tip => NULL,
2381 x_access_key => NULL,
2382 x_owner => P_USER_NAME,
2383 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2384 x_custom_mode => NULL,
2385 x_oa_flex_asn => NULL,
2386 x_oa_flex_code => NULL,
2387 x_oa_flex_num => NULL
2388 );
2389
2390 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2391 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2392 x_param_list_code => P_PARAM_LIST_CODE,
2393 x_sequence_num => VV_PARAM_SEQ_NUM,
2394 x_object_version_number => 1,
2395 x_param_defn_asn => P_APPLICATION_SHORT_NAME,
2396 x_param_defn_code => VV_PARAM_DEFN_CODE,
2397 x_param_name => NULL, -- inherit from definition
2398 x_attribute_asn => NULL,
2399 x_attribute_code => NULL,
2400 x_string_value => NULL, -- inherit from definition
2401 x_date_value => NULL,
2402 x_number_value => NULL,
2403 x_boolean_value_flag => NULL,
2404 x_formula_value => NULL,
2405 x_desc_value => NULL, -- inherit from definition
2406 x_owner => P_USER_NAME,
2407 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2408 x_custom_mode => NULL
2409 );
2410 END IF;
2411
2412 IF P_WINDOW_WIDTH IS NOT NULL AND P_WINDOW_WIDTH > 0
2413 THEN
2414 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2415 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2416 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2417 x_param_list_code => P_PARAM_LIST_CODE,
2418 x_sequence_num => VV_PARAM_SEQ_NUM,
2419 x_object_version_number => 1,
2420 x_param_defn_asn => NULL,
2421 x_param_defn_code => NULL,
2422 x_param_name => 'window-width',
2423 x_attribute_asn => NULL,
2424 x_attribute_code => NULL,
2425 x_string_value => to_char(P_WINDOW_WIDTH),
2426 x_date_value => NULL,
2427 x_number_value => NULL,
2428 x_boolean_value_flag => NULL,
2429 x_formula_value => NULL,
2430 x_desc_value => 'Window Width',
2431 x_owner => P_USER_NAME,
2432 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2433 x_custom_mode => NULL
2434 );
2435 END IF;
2436
2437 IF P_WINDOW_HEIGHT IS NOT NULL AND P_WINDOW_HEIGHT > 0
2438 THEN
2439 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2440 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2441 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2442 x_param_list_code => P_PARAM_LIST_CODE,
2443 x_sequence_num => VV_PARAM_SEQ_NUM,
2444 x_object_version_number => 1,
2445 x_param_defn_asn => NULL,
2446 x_param_defn_code => NULL,
2447 x_param_name => 'window-height',
2448 x_attribute_asn => NULL,
2449 x_attribute_code => NULL,
2450 x_string_value => to_char(P_WINDOW_HEIGHT),
2451 x_date_value => NULL,
2452 x_number_value => NULL,
2453 x_boolean_value_flag => NULL,
2454 x_formula_value => NULL,
2455 x_desc_value => 'Window Height',
2456 x_owner => P_USER_NAME,
2457 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2458 x_custom_mode => NULL
2459 );
2460 END IF;
2461
2462 END ADD_FLEX_LOV_PARAMETER_LIST;
2463
2464
2465 --------------------------------------------------------------------------------
2466 -- PROCEDURE: ADD_LOV_PARAMETER_LIST --
2467 -- --
2468 -- DESCRIPTION: Create a parameter list for a LOV. --
2469 -- RULES: Private/Internal --
2470 -- --
2471 -- MODIFICATION HISTORY --
2472 -- Date Username Description --
2473 -- 11-JUL-2006 dagroves Created. --
2474 --------------------------------------------------------------------------------
2475 PROCEDURE ADD_LOV_PARAMETER_LIST
2476 (P_APPLICATION_SHORT_NAME IN VARCHAR2,
2477 P_PARAM_LIST_CODE IN VARCHAR2,
2478 P_PARAM_LIST_NAME IN VARCHAR2,
2479 P_WINDOW_CAPTION IN VARCHAR2,
2480 P_WINDOW_WIDTH IN NUMBER,
2481 P_WINDOW_HEIGHT IN NUMBER,
2482 P_TABLE_BLOCK_SIZE IN NUMBER,
2483 P_TABLE_COLUMNS IN VARCHAR2,
2484 P_TABLE_SELECT_COLUMNS IN VARCHAR2,
2485 P_TABLE_COLUMN_ALIAS IN VARCHAR2,
2486 P_TABLE_HEADERS IN VARCHAR2,
2487 P_TABLE_SORT_ORDER IN VARCHAR2,
2488 P_USER_NAME IN VARCHAR2)
2489 IS
2490 VV_PARAM_DEFN_CODE VARCHAR2(30);
2491 VV_PARAM_SEQ_NUM NUMBER(15);
2492 BEGIN
2493 VV_PARAM_SEQ_NUM := 0;
2494
2495 BNE_PARAM_LISTS_PKG.LOAD_ROW(
2496 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2497 x_param_list_code => P_PARAM_LIST_CODE,
2498 x_object_version_number => 1,
2499 x_persistent_flag => 'Y',
2500 x_comments => 'Auto Generated Component Parameter List',
2501 x_attribute_asn => NULL,
2502 x_attribute_code => NULL,
2503 x_list_resolver => NULL,
2504 x_user_tip => NULL,
2505 x_prompt_left => NULL,
2506 x_prompt_above => NULL,
2507 x_user_name => P_PARAM_LIST_NAME,
2508 x_owner => P_USER_NAME,
2509 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2510 x_custom_mode => NULL
2511 );
2512
2513 IF P_TABLE_HEADERS IS NOT NULL
2514 THEN
2515 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2516 VV_PARAM_DEFN_CODE := P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM)||'D';
2517 BNE_PARAM_DEFNS_PKG.LOAD_ROW(
2518 x_param_defn_asn => P_APPLICATION_SHORT_NAME,
2519 x_param_defn_code => VV_PARAM_DEFN_CODE,
2520 x_object_version_number => 1,
2521 x_param_name => 'table-headers',
2522 x_param_source => 'Component LOV',
2523 x_param_category => '5', -- Data
2524 x_datatype => '1', -- String
2525 x_attribute_asn => NULL,
2526 x_attribute_code => NULL,
2527 x_param_resolver => NULL,
2528 x_default_required_flag => 'N',
2529 x_default_visible_flag => 'Y', -- Allow it to be seen when teting the list
2530 x_default_user_modifyable_flag => 'Y', -- Allow it to be modified when testing the list
2531 x_default_string => P_TABLE_HEADERS,
2532 x_default_string_trans_flag => 'Y',
2533 x_default_date => NULL,
2534 x_default_number => NULL,
2535 x_default_boolean_flag => NULL,
2536 x_default_formula => NULL,
2537 x_val_type => '1', -- None
2538 x_val_value => NULL,
2539 x_max_size => '100',
2540 x_display_type => '4', -- Text Area
2541 x_display_style => '1', -- None
2542 x_display_size => '20',
2543 x_help_url => NULL,
2544 x_format_mask => NULL,
2545 x_user_name => 'PARAMETER DEFINITION FOR '||P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM), -- upper case to avoid translation
2546 x_default_desc => 'Parameter Definition for '||P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM), -- upper case to avoid translation,
2547 x_prompt_left => 'table-headers',
2548 x_prompt_above => NULL,
2549 x_user_tip => NULL,
2550 x_access_key => NULL,
2551 x_owner => P_USER_NAME,
2552 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2553 x_custom_mode => NULL,
2554 x_oa_flex_asn => NULL,
2555 x_oa_flex_code => NULL,
2556 x_oa_flex_num => NULL
2557 );
2558
2559
2560 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2561 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2562 x_param_list_code => P_PARAM_LIST_CODE,
2563 x_sequence_num => VV_PARAM_SEQ_NUM,
2564 x_object_version_number => 1,
2565 x_param_defn_asn => P_APPLICATION_SHORT_NAME,
2566 x_param_defn_code => VV_PARAM_DEFN_CODE,
2567 x_param_name => NULL, -- inherit from definition
2568 x_attribute_asn => NULL,
2569 x_attribute_code => NULL,
2570 x_string_value => NULL, -- inherit from definition
2571 x_date_value => NULL,
2572 x_number_value => NULL,
2573 x_boolean_value_flag => NULL,
2574 x_formula_value => NULL,
2575 x_desc_value => NULL, -- inherit from definition
2576 x_owner => P_USER_NAME,
2577 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2578 x_custom_mode => NULL
2579 );
2580 END IF;
2581
2582 IF P_TABLE_COLUMNS IS NOT NULL
2583 THEN
2584 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2585 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2586 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2587 x_param_list_code => P_PARAM_LIST_CODE,
2588 x_sequence_num => VV_PARAM_SEQ_NUM,
2589 x_object_version_number => 1,
2590 x_param_defn_asn => NULL,
2591 x_param_defn_code => NULL,
2592 x_param_name => 'table-columns',
2593 x_attribute_asn => NULL,
2594 x_attribute_code => NULL,
2595 x_string_value => P_TABLE_COLUMNS,
2596 x_date_value => NULL,
2597 x_number_value => NULL,
2598 x_boolean_value_flag => NULL,
2599 x_formula_value => NULL,
2600 x_desc_value => 'Table Columns',
2601 x_owner => P_USER_NAME,
2602 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2603 x_custom_mode => NULL
2604 );
2605 END IF;
2606
2607 IF P_TABLE_SELECT_COLUMNS IS NOT NULL
2608 THEN
2609 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2610 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2611 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2612 x_param_list_code => P_PARAM_LIST_CODE,
2613 x_sequence_num => VV_PARAM_SEQ_NUM,
2614 x_object_version_number => 1,
2615 x_param_defn_asn => NULL,
2616 x_param_defn_code => NULL,
2617 x_param_name => 'table-select-column',
2618 x_attribute_asn => NULL,
2619 x_attribute_code => NULL,
2620 x_string_value => P_TABLE_SELECT_COLUMNS,
2621 x_date_value => NULL,
2622 x_number_value => NULL,
2623 x_boolean_value_flag => NULL,
2624 x_formula_value => NULL,
2625 x_desc_value => 'Table Select Column',
2626 x_owner => P_USER_NAME,
2627 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2628 x_custom_mode => NULL
2629 );
2630 END IF;
2631
2632 IF P_TABLE_COLUMN_ALIAS IS NOT NULL
2633 THEN
2634 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2635 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2636 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2637 x_param_list_code => P_PARAM_LIST_CODE,
2638 x_sequence_num => VV_PARAM_SEQ_NUM,
2639 x_object_version_number => 1,
2640 x_param_defn_asn => NULL,
2641 x_param_defn_code => NULL,
2642 x_param_name => 'table-column-alias',
2643 x_attribute_asn => NULL,
2644 x_attribute_code => NULL,
2645 x_string_value => P_TABLE_COLUMN_ALIAS,
2646 x_date_value => NULL,
2647 x_number_value => NULL,
2648 x_boolean_value_flag => NULL,
2649 x_formula_value => NULL,
2650 x_desc_value => 'Table Column Alias',
2651 x_owner => P_USER_NAME,
2652 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2653 x_custom_mode => NULL
2654 );
2655 END IF;
2656
2657
2658 IF P_TABLE_SORT_ORDER IS NOT NULL
2659 THEN
2660 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2661 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2662 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2663 x_param_list_code => P_PARAM_LIST_CODE,
2664 x_sequence_num => VV_PARAM_SEQ_NUM,
2665 x_object_version_number => 1,
2666 x_param_defn_asn => NULL,
2667 x_param_defn_code => NULL,
2668 x_param_name => 'table-column-sort',
2669 x_attribute_asn => NULL,
2670 x_attribute_code => NULL,
2671 x_string_value => P_TABLE_SORT_ORDER,
2672 x_date_value => NULL,
2673 x_number_value => NULL,
2674 x_boolean_value_flag => NULL,
2675 x_formula_value => NULL,
2676 x_desc_value => 'Table Column Sort. CSV list of ''no'', ''yes'', ''ascending'' or ''descending'' corresponding to the table-columns',
2677 x_owner => P_USER_NAME,
2678 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2679 x_custom_mode => NULL
2680 );
2681 END IF;
2682
2683 IF P_WINDOW_CAPTION IS NOT NULL
2684 THEN
2685 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2686 VV_PARAM_DEFN_CODE := P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM)||'D';
2687 BNE_PARAM_DEFNS_PKG.LOAD_ROW(
2688 x_param_defn_asn => P_APPLICATION_SHORT_NAME,
2689 x_param_defn_code => VV_PARAM_DEFN_CODE,
2690 x_object_version_number => 1,
2691 x_param_name => 'window-caption',
2692 x_param_source => 'Component LOV',
2693 x_param_category => '5', -- Data
2694 x_datatype => '1', -- String
2695 x_attribute_asn => NULL,
2696 x_attribute_code => NULL,
2697 x_param_resolver => NULL,
2698 x_default_required_flag => 'N',
2699 x_default_visible_flag => 'Y', -- Allow it to be seen when teting the list
2700 x_default_user_modifyable_flag => 'Y', -- Allow it to be modified when testing the list
2701 x_default_string => P_WINDOW_CAPTION,
2702 x_default_string_trans_flag => 'Y',
2703 x_default_date => NULL,
2704 x_default_number => NULL,
2705 x_default_boolean_flag => NULL,
2706 x_default_formula => NULL,
2707 x_val_type => '1', -- None
2708 x_val_value => NULL,
2709 x_max_size => '100',
2710 x_display_type => '4', -- Text Area
2711 x_display_style => '1', -- None
2712 x_display_size => '20',
2713 x_help_url => NULL,
2714 x_format_mask => NULL,
2715 x_user_name => 'PARAMETER DEFINITION FOR '||P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM), -- upper case to avoid translation
2716 x_default_desc => 'Parameter Definition for '||P_PARAM_LIST_CODE||'P'||to_char(VV_PARAM_SEQ_NUM), -- upper case to avoid translation,
2717 x_prompt_left => 'window-caption',
2718 x_prompt_above => NULL,
2719 x_user_tip => NULL,
2720 x_access_key => NULL,
2721 x_owner => P_USER_NAME,
2722 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2723 x_custom_mode => NULL,
2724 x_oa_flex_asn => NULL,
2725 x_oa_flex_code => NULL,
2726 x_oa_flex_num => NULL
2727 );
2728
2729 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2730 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2731 x_param_list_code => P_PARAM_LIST_CODE,
2732 x_sequence_num => VV_PARAM_SEQ_NUM,
2733 x_object_version_number => 1,
2734 x_param_defn_asn => P_APPLICATION_SHORT_NAME,
2735 x_param_defn_code => VV_PARAM_DEFN_CODE,
2736 x_param_name => NULL, -- inherit from definition
2737 x_attribute_asn => NULL,
2738 x_attribute_code => NULL,
2739 x_string_value => NULL, -- inherit from definition
2740 x_date_value => NULL,
2741 x_number_value => NULL,
2742 x_boolean_value_flag => NULL,
2743 x_formula_value => NULL,
2744 x_desc_value => NULL, -- inherit from definition
2745 x_owner => P_USER_NAME,
2746 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2747 x_custom_mode => NULL
2748 );
2749 END IF;
2750
2751 IF P_WINDOW_WIDTH IS NOT NULL AND P_WINDOW_WIDTH > 0
2752 THEN
2753 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2754 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2755 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2756 x_param_list_code => P_PARAM_LIST_CODE,
2757 x_sequence_num => VV_PARAM_SEQ_NUM,
2758 x_object_version_number => 1,
2759 x_param_defn_asn => NULL,
2760 x_param_defn_code => NULL,
2761 x_param_name => 'window-width',
2762 x_attribute_asn => NULL,
2763 x_attribute_code => NULL,
2764 x_string_value => to_char(P_WINDOW_WIDTH),
2765 x_date_value => NULL,
2766 x_number_value => NULL,
2767 x_boolean_value_flag => NULL,
2768 x_formula_value => NULL,
2769 x_desc_value => 'Window Width',
2770 x_owner => P_USER_NAME,
2771 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2772 x_custom_mode => NULL
2773 );
2774 END IF;
2775
2776 IF P_WINDOW_HEIGHT IS NOT NULL AND P_WINDOW_HEIGHT > 0
2777 THEN
2778 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2779 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2780 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2781 x_param_list_code => P_PARAM_LIST_CODE,
2782 x_sequence_num => VV_PARAM_SEQ_NUM,
2783 x_object_version_number => 1,
2784 x_param_defn_asn => NULL,
2785 x_param_defn_code => NULL,
2786 x_param_name => 'window-height',
2787 x_attribute_asn => NULL,
2788 x_attribute_code => NULL,
2789 x_string_value => to_char(P_WINDOW_HEIGHT),
2790 x_date_value => NULL,
2791 x_number_value => NULL,
2792 x_boolean_value_flag => NULL,
2793 x_formula_value => NULL,
2794 x_desc_value => 'Window Height',
2795 x_owner => P_USER_NAME,
2796 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2797 x_custom_mode => NULL
2798 );
2799 END IF;
2800
2801
2802 IF P_TABLE_BLOCK_SIZE IS NOT NULL AND P_TABLE_BLOCK_SIZE > 0
2803 THEN
2804 VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
2805 BNE_PARAM_LIST_ITEMS_PKG.LOAD_ROW (
2806 x_param_list_asn => P_APPLICATION_SHORT_NAME,
2807 x_param_list_code => P_PARAM_LIST_CODE,
2808 x_sequence_num => VV_PARAM_SEQ_NUM,
2809 x_object_version_number => 1,
2810 x_param_defn_asn => NULL,
2811 x_param_defn_code => NULL,
2812 x_param_name => 'table-block-size',
2813 x_attribute_asn => NULL,
2814 x_attribute_code => NULL,
2815 x_string_value => to_char(P_TABLE_BLOCK_SIZE),
2816 x_date_value => NULL,
2817 x_number_value => NULL,
2818 x_boolean_value_flag => NULL,
2819 x_formula_value => NULL,
2820 x_desc_value => 'Table Block Size',
2821 x_owner => P_USER_NAME,
2822 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
2823 x_custom_mode => NULL
2824 );
2825 END IF;
2826 END ADD_LOV_PARAMETER_LIST;
2827
2828
2829 --------------------------------------------------------------------------------
2830 -- PROCEDURE: CREATE_TABLE_LOV --
2831 -- --
2832 -- DESCRIPTION: Create a Table LOV for a specific interface Column. --
2833 -- EXAMPLES: --
2834 -- BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV --
2835 -- (P_APPLICATION_ID => 231, --
2836 -- P_INTERFACE_CODE => 'MY_INTERFACE', --
2837 -- P_INTERFACE_COL_NAME => 'COL_NAME', --
2838 -- P_ID_COL => 'LOOKUP_CODE', -- LOOKUP CODE UPLOADED --
2839 -- P_MEAN_COL => 'MEANING', -- Shown in sheet --
2840 -- P_DESC_COL => NULL, --
2841 -- P_TABLE => 'FND_LOOKUPS', --
2842 -- P_ADDL_W_C => 'lookup_type = ''YES_NO''', --
2843 -- P_WINDOW_CAPTION => 'Yes/No with Meaning, selecting Meaning, Meaning sortable',--
2844 -- P_WINDOW_WIDTH => 400, --
2845 -- P_WINDOW_HEIGHT => 300, --
2846 -- P_TABLE_BLOCK_SIZE => 10, --
2847 -- P_TABLE_SORT_ORDER => 'ascending', --
2848 -- P_USER_ID => 2); -- SEED USER --
2849 -- --
2850 -- BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV --
2851 -- (P_APPLICATION_ID => 231, --
2852 -- P_INTERFACE_CODE => 'MY_INTERFACE', --
2853 -- P_INTERFACE_COL_NAME => 'COL_NAME', --
2854 -- P_ID_COL => 'LOOKUP_CODE', -- LOOKUP CODE UPLOADED --
2855 -- P_MEAN_COL => 'MEANING', -- Shown in sheet --
2856 -- P_DESC_COL => 'DESCRIPTION', --
2857 -- P_TABLE => 'FND_LOOKUPS', --
2858 -- P_ADDL_W_C => 'lookup_type = ''FND_CLIENT_CHARACTER_SETS''',
2859 -- P_WINDOW_CAPTION => 'Yes/No/All with Meaning and Description, selecting Meaning, Meaning sortable',--
2860 -- P_WINDOW_WIDTH => 400, --
2861 -- P_WINDOW_HEIGHT => 300, --
2862 -- P_TABLE_BLOCK_SIZE => 10, --
2863 -- P_TABLE_SORT_ORDER => 'yes,no', -- sortable by meaning, not description--
2864 -- P_USER_ID => 2); -- SEED USER --
2865 -- --
2866 -- MODIFICATION HISTORY --
2867 -- Date Username Description --
2868 -- 11-JUL-2006 dagroves Created. --
2869 --------------------------------------------------------------------------------
2870 PROCEDURE CREATE_TABLE_LOV
2871 (P_APPLICATION_ID IN NUMBER,
2872 P_INTERFACE_CODE IN VARCHAR2,
2873 P_INTERFACE_COL_NAME IN VARCHAR2,
2874 P_ID_COL IN VARCHAR2,
2875 P_MEAN_COL IN VARCHAR2,
2876 P_DESC_COL IN VARCHAR2,
2877 P_TABLE IN VARCHAR2,
2878 P_ADDL_W_C IN VARCHAR2,
2879 P_WINDOW_CAPTION IN VARCHAR2,
2880 P_WINDOW_WIDTH IN NUMBER,
2881 P_WINDOW_HEIGHT IN NUMBER,
2882 P_TABLE_BLOCK_SIZE IN NUMBER,
2883 P_TABLE_SORT_ORDER IN VARCHAR2,
2884 P_USER_ID IN NUMBER,
2885 P_TABLE_COLUMNS IN VARCHAR2,
2886 P_TABLE_SELECT_COLUMNS IN VARCHAR2,
2887 P_TABLE_COLUMN_ALIAS IN VARCHAR2,
2888 P_TABLE_HEADERS IN VARCHAR2,
2889 P_POPLIST_FLAG IN VARCHAR2)
2890 IS
2891 VV_APPLICATION_SHORT_NAME VARCHAR2(30);
2892 VV_INTERFACE_CODE VARCHAR2(30);
2893 VV_INTERFACE_COL_NAME VARCHAR2(30);
2894 VV_ID_COL VARCHAR2(2000);
2895 VV_MEAN_COL VARCHAR2(2000);
2896 VV_DESC_COL VARCHAR2(2000);
2897 VV_PARAM_LIST_CODE VARCHAR2(30);
2898 VV_PARAM_DEFN_CODE VARCHAR2(30);
2899 VV_COMPONENT_CODE VARCHAR2(30);
2900 VV_USER_NAME VARCHAR2(30);
2901 VV_TABLE_HEADERS VARCHAR2(2000);
2902 VV_TABLE_COLUMNS VARCHAR2(2000);
2903 VV_TABLE_SELECT_COLUMNS VARCHAR2(2000);
2904 VV_TABLE_COLUMN_ALIAS VARCHAR2(2000);
2905 VV_INTERFACE_COL BNE_INTERFACE_COLS_B%ROWTYPE;
2906 VV_DATA_TYPE VARCHAR2(20);
2907 VV_LOV_TYPE VARCHAR2(30);
2908 BEGIN
2909
2910 VV_INTERFACE_CODE := TRIM(P_INTERFACE_CODE);
2911 VV_INTERFACE_COL_NAME := TRIM(P_INTERFACE_COL_NAME);
2912 VV_ID_COL := TRIM(P_ID_COL);
2913 VV_MEAN_COL := TRIM(P_MEAN_COL);
2914 VV_DESC_COL := TRIM(P_DESC_COL);
2915 IF UPPER(P_POPLIST_FLAG) = 'Y'
2916 THEN
2917 VV_LOV_TYPE := 'POPLIST';
2918 ELSE
2919 VV_LOV_TYPE := 'STANDARD';
2920 END IF;
2921
2922 IF VV_MEAN_COL IS NULL THEN
2923 RAISE_APPLICATION_ERROR(-20000,'Required: The meaning column is NULL.');
2924 END IF;
2925 IF VV_INTERFACE_COL_NAME IS NULL THEN
2926 RAISE_APPLICATION_ERROR(-20000,'Required: The interface column name is NULL.');
2927 END IF;
2928 -- we autogenerate component and param codes to a maximum of:
2929 -- VV_INTERFACE_CODE||_CXXXLPXD where XXX is the interface col sequence_num, and X is the parameter sequence num.
2930 IF LENGTH(VV_INTERFACE_CODE) > 21 THEN
2931 RAISE_APPLICATION_ERROR(-20000,'The interface column name '||VV_INTERFACE_CODE||' is too long to auto-generate parameter codes of the form VV_INTERFACE_CODE||''_CXXXLPXD''. Max size is 21.');
2932 END IF;
2933
2934
2935 SELECT APPLICATION_SHORT_NAME
2936 INTO VV_APPLICATION_SHORT_NAME
2937 FROM FND_APPLICATION
2938 WHERE APPLICATION_ID = P_APPLICATION_ID;
2939
2940 SELECT USER_NAME
2941 INTO VV_USER_NAME
2942 FROM FND_USER
2943 WHERE USER_ID = P_USER_ID;
2944
2945 SELECT *
2946 INTO VV_INTERFACE_COL
2947 FROM BNE_INTERFACE_COLS_B
2948 WHERE APPLICATION_ID = P_APPLICATION_ID
2949 AND INTERFACE_CODE = VV_INTERFACE_CODE
2950 AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
2951
2952 VV_INTERFACE_CODE := VV_INTERFACE_COL.INTERFACE_CODE||'_C'||TO_CHAR(VV_INTERFACE_COL.SEQUENCE_NUM);
2953 VV_PARAM_LIST_CODE := VV_INTERFACE_CODE||'_L';
2954 VV_COMPONENT_CODE := VV_INTERFACE_CODE||'_COMP';
2955
2956 UPDATE BNE_INTERFACE_COLS_B
2957 SET VAL_TYPE = 'TABLE',
2958 VAL_ID_COL = VV_ID_COL,
2959 VAL_MEAN_COL = VV_MEAN_COL,
2960 VAL_DESC_COL = VV_DESC_COL,
2961 VAL_OBJ_NAME = P_TABLE,
2962 VAL_ADDL_W_C = P_ADDL_W_C,
2963 VAL_COMPONENT_APP_ID = p_application_id,
2964 VAL_COMPONENT_CODE = VV_COMPONENT_CODE,
2965 LOV_TYPE = VV_LOV_TYPE
2966 WHERE APPLICATION_ID = P_APPLICATION_ID
2967 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
2968 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM;
2969
2970 if (VV_INTERFACE_COL.DATA_TYPE = 1) THEN
2971 VV_DATA_TYPE := 'Number';
2972 ELSIF (VV_INTERFACE_COL.DATA_TYPE = 2) THEN
2973 VV_DATA_TYPE := 'Text';
2974 ELSE
2975 VV_DATA_TYPE := 'Date';
2976 END IF;
2977
2978 IF VV_INTERFACE_COL.REQUIRED_FLAG = 'Y'
2979 THEN
2980 UPDATE BNE_INTERFACE_COLS_TL
2981 SET USER_HINT = '*List - '||VV_DATA_TYPE
2982 WHERE APPLICATION_ID = P_APPLICATION_ID
2983 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
2984 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
2985 ;
2986 ELSE
2987 UPDATE BNE_INTERFACE_COLS_TL
2988 SET USER_HINT = 'List - '||VV_DATA_TYPE
2989 WHERE APPLICATION_ID = P_APPLICATION_ID
2990 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
2991 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
2992 ;
2993 end if;
2994
2995
2996 IF P_TABLE_COLUMNS IS NOT NULL
2997 THEN
2998 VV_TABLE_COLUMNS := P_TABLE_COLUMNS;
2999 ELSE
3000 VV_TABLE_COLUMNS := VV_MEAN_COL;
3001 IF (VV_DESC_COL IS NOT NULL AND VV_DESC_COL <> VV_MEAN_COL)
3002 THEN
3003 VV_TABLE_COLUMNS := VV_TABLE_COLUMNS||','||VV_DESC_COL;
3004 END IF;
3005 END IF;
3006
3007 IF P_TABLE_HEADERS IS NOT NULL
3008 THEN
3009 VV_TABLE_HEADERS := P_TABLE_HEADERS;
3010 ELSE
3011 VV_TABLE_HEADERS := initcap(replace(VV_MEAN_COL, '_', ' '));
3012 IF (VV_DESC_COL IS NOT NULL AND VV_DESC_COL <> VV_MEAN_COL)
3013 THEN
3014 VV_TABLE_HEADERS := VV_TABLE_HEADERS||','||initcap(replace(VV_DESC_COL, '_', ' '));
3015 END IF;
3016 END IF;
3017
3018 IF P_TABLE_SELECT_COLUMNS IS NOT NULL
3019 THEN
3020 VV_TABLE_SELECT_COLUMNS := P_TABLE_SELECT_COLUMNS;
3021 ELSE
3022 VV_TABLE_SELECT_COLUMNS := VV_INTERFACE_COL_NAME;
3023 END IF;
3024
3025 IF P_TABLE_COLUMN_ALIAS IS NOT NULL
3026 THEN
3027 VV_TABLE_COLUMN_ALIAS := P_TABLE_COLUMN_ALIAS;
3028 ELSE
3029 VV_TABLE_COLUMN_ALIAS := VV_INTERFACE_COL_NAME;
3030 END IF;
3031
3032 ----------------------------------------------
3033 -- Component Parameter List
3034 ----------------------------------------------
3035 ADD_LOV_PARAMETER_LIST(
3036 P_APPLICATION_SHORT_NAME => VV_APPLICATION_SHORT_NAME,
3037 P_PARAM_LIST_CODE => VV_PARAM_LIST_CODE,
3038 P_PARAM_LIST_NAME => 'Param List for '||P_INTERFACE_CODE||'.'||VV_INTERFACE_COL_NAME,
3039 P_WINDOW_CAPTION => TRIM(P_WINDOW_CAPTION),
3040 P_WINDOW_WIDTH => P_WINDOW_WIDTH,
3041 P_WINDOW_HEIGHT => P_WINDOW_HEIGHT,
3042 P_TABLE_BLOCK_SIZE => P_TABLE_BLOCK_SIZE,
3043 P_TABLE_COLUMNS => VV_TABLE_COLUMNS,
3044 P_TABLE_SELECT_COLUMNS => VV_TABLE_SELECT_COLUMNS,
3045 P_TABLE_COLUMN_ALIAS => VV_TABLE_COLUMN_ALIAS,
3046 P_TABLE_HEADERS => VV_TABLE_HEADERS,
3047 P_TABLE_SORT_ORDER => P_TABLE_SORT_ORDER,
3048 P_USER_NAME => VV_USER_NAME
3049 );
3050
3051
3052 ----------------------------------------------
3053 -- Component
3054 ----------------------------------------------
3055 BNE_COMPONENTS_PKG.LOAD_ROW(
3056 x_component_asn => VV_APPLICATION_SHORT_NAME,
3057 x_component_code => VV_COMPONENT_CODE,
3058 x_object_version_number => 1,
3059 x_component_java_class => 'BneOAValueSet',
3060 x_param_list_asn => VV_APPLICATION_SHORT_NAME,
3061 x_param_list_code => VV_PARAM_LIST_CODE,
3062 x_user_name => 'Component for '||P_INTERFACE_CODE||'.'||VV_INTERFACE_COL_NAME,
3063 x_owner => VV_USER_NAME,
3064 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
3065 x_custom_mode => NULL
3066 );
3067
3068 END CREATE_TABLE_LOV;
3069
3070 --------------------------------------------------------------------------------
3071 -- PROCEDURE: CREATE_JAVA_LOV --
3072 -- --
3073 -- DESCRIPTION: Create a Table LOV for a specific interface Column. --
3074 -- EXAMPLES: --
3075 -- BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV --
3076 -- (P_APPLICATION_ID => P_APPLICATION_ID, --
3077 -- P_INTERFACE_CODE => P_INTERFACE_CODE, --
3078 -- P_INTERFACE_COL_NAME => 'COL_NAME', --
3079 -- P_JAVA_CLASS => 'oracle.apps.bne.lovtest.component.BneLOVTestSimpleJavaLOV01',--
3080 -- P_WINDOW_CAPTION => 'Java LOV selecting Code, Code sortable', --
3081 -- P_WINDOW_WIDTH => 400, --
3082 -- P_WINDOW_HEIGHT => 300, --
3083 -- P_TABLE_BLOCK_SIZE => 10, --
3084 -- P_TABLE_COLUMNS => 'LOOKUP_CODE', --
3085 -- P_TABLE_SELECT_COLUMNS => NULL, --
3086 -- P_TABLE_COLUMN_ALIAS => NULL, --
3087 -- P_TABLE_HEADERS => 'Lookup Code', --
3088 -- P_TABLE_SORT_ORDER => 'yes', --
3089 -- P_USER_ID => P_USER_ID); --
3090 -- --
3091 -- BNE_INTEGRATOR_UTILS.CREATE_JAVA_LOV --
3092 -- (P_APPLICATION_ID => P_APPLICATION_ID, --
3093 -- P_INTERFACE_CODE => P_INTERFACE_CODE, --
3094 -- P_INTERFACE_COL_NAME => 'COL_NAME', --
3095 -- P_JAVA_CLASS => 'oracle.apps.bne.lovtest.component.BneLOVTestSimpleJavaLOV01',--
3096 -- P_WINDOW_CAPTION => 'Java LOV, Code, Meaning and Description selecting Code, Meaning and Description. Meaning and Description sortable, tablesize of 50',
3097 -- P_WINDOW_WIDTH => 500, --
3098 -- P_WINDOW_HEIGHT => 500, --
3099 -- P_TABLE_BLOCK_SIZE => 50, --
3100 -- P_TABLE_COLUMNS => 'LOOKUP_CODE,MEANING,DESCRIPTION', --
3101 -- P_TABLE_SELECT_COLUMNS => 'STRING_COL06,STRING_COL08,STRING_COL07', --
3102 -- P_TABLE_COLUMN_ALIAS => 'STRING_COL06,STRING_COL08,STRING_COL07', --
3103 -- P_TABLE_HEADERS => 'Lookup Code, Meaning, Description', --
3104 -- P_TABLE_SORT_ORDER => 'no, yes, yes', --
3105 -- P_USER_ID => P_USER_ID); --
3106 -- --
3107 -- --
3108 -- MODIFICATION HISTORY --
3109 -- Date Username Description --
3110 -- 11-JUL-2006 dagroves Created. --
3111 --------------------------------------------------------------------------------
3112 PROCEDURE CREATE_JAVA_LOV
3113 (P_APPLICATION_ID IN NUMBER,
3114 P_INTERFACE_CODE IN VARCHAR2,
3115 P_INTERFACE_COL_NAME IN VARCHAR2,
3116 P_JAVA_CLASS IN VARCHAR2,
3117 P_WINDOW_CAPTION IN VARCHAR2,
3118 P_WINDOW_WIDTH IN NUMBER,
3119 P_WINDOW_HEIGHT IN NUMBER,
3120 P_TABLE_BLOCK_SIZE IN NUMBER,
3121 P_TABLE_COLUMNS IN VARCHAR2,
3122 P_TABLE_SELECT_COLUMNS IN VARCHAR2,
3123 P_TABLE_COLUMN_ALIAS IN VARCHAR2,
3124 P_TABLE_HEADERS IN VARCHAR2,
3125 P_TABLE_SORT_ORDER IN VARCHAR2,
3126 P_USER_ID IN NUMBER)
3127 IS
3128 VV_APPLICATION_SHORT_NAME VARCHAR2(30);
3129 VV_INTERFACE_CODE VARCHAR2(30);
3130 VV_INTERFACE_COL_NAME VARCHAR2(30);
3131 VV_PARAM_LIST_CODE VARCHAR2(30);
3132 VV_PARAM_DEFN_CODE VARCHAR2(30);
3133 VV_COMPONENT_CODE VARCHAR2(30);
3134 VV_USER_NAME VARCHAR2(30);
3135 VV_INTERFACE_COL BNE_INTERFACE_COLS_B%ROWTYPE;
3136 VV_DATA_TYPE VARCHAR2(20);
3137 BEGIN
3138 VV_INTERFACE_CODE := TRIM(P_INTERFACE_CODE);
3139 VV_INTERFACE_COL_NAME := TRIM(P_INTERFACE_COL_NAME);
3140
3141 IF VV_INTERFACE_COL_NAME IS NULL THEN
3142 RAISE_APPLICATION_ERROR(-20000,'Required: The interface column name is NULL.');
3143 END IF;
3144 -- we autogenerate component and param codes to a maximum of:
3145 -- VV_INTERFACE_CODE||_CXXXLPXD where XXX is the interface col sequence_num, and X is the parameter sequence num.
3146 IF LENGTH(VV_INTERFACE_CODE) > 21 THEN
3147 RAISE_APPLICATION_ERROR(-20000,'The interface column name '||VV_INTERFACE_CODE||' is too long to auto-generate parameter codes of the form VV_INTERFACE_CODE||''_CXXXLPXD''. Max size is 21.');
3148 END IF;
3149
3150
3151 SELECT APPLICATION_SHORT_NAME
3152 INTO VV_APPLICATION_SHORT_NAME
3153 FROM FND_APPLICATION
3154 WHERE APPLICATION_ID = P_APPLICATION_ID;
3155
3156 SELECT USER_NAME
3157 INTO VV_USER_NAME
3158 FROM FND_USER
3159 WHERE USER_ID = P_USER_ID;
3160
3161
3162 SELECT *
3163 INTO VV_INTERFACE_COL
3164 FROM BNE_INTERFACE_COLS_B
3165 WHERE APPLICATION_ID = P_APPLICATION_ID
3166 AND INTERFACE_CODE = VV_INTERFACE_CODE
3167 AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
3168
3169 VV_INTERFACE_CODE := VV_INTERFACE_COL.INTERFACE_CODE||'_C'||TO_CHAR(VV_INTERFACE_COL.SEQUENCE_NUM);
3170 VV_PARAM_LIST_CODE := VV_INTERFACE_CODE||'_L';
3171 VV_COMPONENT_CODE := VV_INTERFACE_CODE||'_COMP';
3172
3173 UPDATE BNE_INTERFACE_COLS_B
3174 SET VAL_TYPE = 'JAVA',
3175 VAL_ID_COL = NULL,
3176 VAL_MEAN_COL = NULL,
3177 VAL_DESC_COL = NULL,
3178 VAL_OBJ_NAME = NULL,
3179 VAL_ADDL_W_C = NULL,
3180 VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
3181 VAL_COMPONENT_CODE = VV_COMPONENT_CODE
3182 WHERE APPLICATION_ID = P_APPLICATION_ID
3183 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
3184 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM;
3185
3186 if (VV_INTERFACE_COL.DATA_TYPE = 1) THEN
3187 VV_DATA_TYPE := 'Number';
3188 ELSIF (VV_INTERFACE_COL.DATA_TYPE = 2) THEN
3189 VV_DATA_TYPE := 'Text';
3190 ELSE
3191 VV_DATA_TYPE := 'Date';
3192 END IF;
3193
3194 IF VV_INTERFACE_COL.REQUIRED_FLAG = 'Y'
3195 THEN
3196 UPDATE BNE_INTERFACE_COLS_TL
3197 SET USER_HINT = '*List - '||VV_DATA_TYPE
3198 WHERE APPLICATION_ID = P_APPLICATION_ID
3199 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
3200 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
3201 ;
3202 ELSE
3203 UPDATE BNE_INTERFACE_COLS_TL
3204 SET USER_HINT = 'List - '||VV_DATA_TYPE
3205 WHERE APPLICATION_ID = P_APPLICATION_ID
3206 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
3207 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
3208 ;
3209 end if;
3210
3211 ----------------------------------------------
3212 -- Component Parameter List
3213 ----------------------------------------------
3214 ADD_LOV_PARAMETER_LIST(
3215 P_APPLICATION_SHORT_NAME => VV_APPLICATION_SHORT_NAME,
3216 P_PARAM_LIST_CODE => VV_PARAM_LIST_CODE,
3217 P_PARAM_LIST_NAME => 'Param List for '||P_INTERFACE_CODE||'.'||P_INTERFACE_COL_NAME,
3218 P_WINDOW_CAPTION => TRIM(P_WINDOW_CAPTION),
3219 P_WINDOW_WIDTH => P_WINDOW_WIDTH,
3220 P_WINDOW_HEIGHT => P_WINDOW_HEIGHT,
3221 P_TABLE_BLOCK_SIZE => P_TABLE_BLOCK_SIZE,
3222 P_TABLE_COLUMNS => P_TABLE_COLUMNS,
3223 P_TABLE_SELECT_COLUMNS => nvl(TRIM(P_TABLE_SELECT_COLUMNS), P_INTERFACE_COL_NAME),
3224 P_TABLE_COLUMN_ALIAS => nvl(TRIM(P_TABLE_COLUMN_ALIAS), P_INTERFACE_COL_NAME),
3225 P_TABLE_HEADERS => TRIM(P_TABLE_HEADERS),
3226 P_TABLE_SORT_ORDER => TRIM(P_TABLE_SORT_ORDER),
3227 P_USER_NAME => VV_USER_NAME
3228 );
3229
3230 ----------------------------------------------
3231 -- Component
3232 ----------------------------------------------
3233 BNE_COMPONENTS_PKG.LOAD_ROW(
3234 x_component_asn => VV_APPLICATION_SHORT_NAME,
3235 x_component_code => VV_COMPONENT_CODE,
3236 x_object_version_number => 1,
3237 x_component_java_class => P_JAVA_CLASS,
3238 x_param_list_asn => VV_APPLICATION_SHORT_NAME,
3239 x_param_list_code => VV_PARAM_LIST_CODE,
3240 x_user_name => 'Component for '||P_INTERFACE_CODE||'.'||P_INTERFACE_COL_NAME,
3241 x_owner => VV_USER_NAME,
3242 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
3243 x_custom_mode => NULL
3244 );
3245 END CREATE_JAVA_LOV;
3246
3247 --------------------------------------------------------------------------------
3248 -- PROCEDURE: CREATE_CALENDAR_LOV --
3249 -- --
3250 -- DESCRIPTION: Create a Calendar LOV for a specific interface Column. --
3251 -- EXAMPLE: --
3252 -- BNE_INTEGRATOR_UTILS.CREATE_CALENDAR_LOV --
3253 -- (P_APPLICATION_ID => 231, --
3254 -- P_INTERFACE_CODE => 'MY_INTERFACE', --
3255 -- P_INTERFACE_COL_NAME => 'COL_NAME', --
3256 -- P_WINDOW_CAPTION => 'Date Col LOV', --
3257 -- P_WINDOW_WIDTH => 230, --
3258 -- P_WINDOW_HEIGHT => 220, --
3259 -- P_TABLE_COLUMNS => NULL, --
3260 -- P_USER_ID => 2); -- SEED USER --
3261 -- --
3262 -- --
3263 -- MODIFICATION HISTORY --
3264 -- Date Username Description --
3265 -- 11-JUL-2006 dagroves Created. --
3266 --------------------------------------------------------------------------------
3267 PROCEDURE CREATE_CALENDAR_LOV
3268 (P_APPLICATION_ID IN NUMBER,
3269 P_INTERFACE_CODE IN VARCHAR2,
3270 P_INTERFACE_COL_NAME IN VARCHAR2,
3271 P_WINDOW_CAPTION IN VARCHAR2,
3272 P_WINDOW_WIDTH IN NUMBER,
3273 P_WINDOW_HEIGHT IN NUMBER,
3274 P_TABLE_COLUMNS IN VARCHAR2,
3275 P_USER_ID IN NUMBER)
3276 IS
3277 VV_APPLICATION_SHORT_NAME VARCHAR2(30);
3278 VV_INTERFACE_CODE VARCHAR2(30);
3279 VV_INTERFACE_COL_NAME VARCHAR2(30);
3280 VV_PARAM_LIST_CODE VARCHAR2(30);
3281 VV_PARAM_DEFN_CODE VARCHAR2(30);
3282 VV_COMPONENT_CODE VARCHAR2(30);
3283 VV_USER_NAME VARCHAR2(30);
3284 VV_INTERFACE_COL BNE_INTERFACE_COLS_B%ROWTYPE;
3285 VV_DATA_TYPE VARCHAR2(20);
3286 BEGIN
3287 VV_INTERFACE_CODE := TRIM(P_INTERFACE_CODE);
3288 VV_INTERFACE_COL_NAME := TRIM(P_INTERFACE_COL_NAME);
3289
3290 IF VV_INTERFACE_COL_NAME IS NULL THEN
3291 RAISE_APPLICATION_ERROR(-20000,'Required: The interface column name is NULL.');
3292 END IF;
3293 -- we autogenerate component and param codes to a maximum of:
3294 -- VV_INTERFACE_CODE||_CXXXLPXD where XXX is the interface col sequence_num, and X is the parameter sequence num.
3295 IF LENGTH(VV_INTERFACE_CODE) > 21 THEN
3296 RAISE_APPLICATION_ERROR(-20000,'The interface column name '||VV_INTERFACE_CODE||' is too long to auto-generate parameter codes of the form VV_INTERFACE_CODE||''_CXXXLPXD''. Max size is 21.');
3297 END IF;
3298
3299
3300 SELECT APPLICATION_SHORT_NAME
3301 INTO VV_APPLICATION_SHORT_NAME
3302 FROM FND_APPLICATION
3303 WHERE APPLICATION_ID = P_APPLICATION_ID;
3304
3305 SELECT USER_NAME
3306 INTO VV_USER_NAME
3307 FROM FND_USER
3308 WHERE USER_ID = P_USER_ID;
3309
3310 SELECT *
3311 INTO VV_INTERFACE_COL
3312 FROM BNE_INTERFACE_COLS_B
3313 WHERE APPLICATION_ID = P_APPLICATION_ID
3314 AND INTERFACE_CODE = VV_INTERFACE_CODE
3315 AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
3316
3317 VV_INTERFACE_CODE := VV_INTERFACE_COL.INTERFACE_CODE||'_C'||TO_CHAR(VV_INTERFACE_COL.SEQUENCE_NUM);
3318 VV_PARAM_LIST_CODE := VV_INTERFACE_CODE||'_L';
3319 VV_COMPONENT_CODE := VV_INTERFACE_CODE||'_COMP';
3320
3321 UPDATE BNE_INTERFACE_COLS_B
3322 SET VAL_TYPE = 'JAVA',
3323 VAL_ID_COL = NULL,
3324 VAL_MEAN_COL = NULL,
3325 VAL_DESC_COL = NULL,
3326 VAL_OBJ_NAME = NULL,
3327 VAL_ADDL_W_C = NULL,
3328 VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
3329 VAL_COMPONENT_CODE = VV_COMPONENT_CODE
3330 WHERE APPLICATION_ID = P_APPLICATION_ID
3331 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
3332 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM;
3333
3334 if (VV_INTERFACE_COL.DATA_TYPE = 1) THEN
3335 VV_DATA_TYPE := 'Number';
3336 ELSIF (VV_INTERFACE_COL.DATA_TYPE = 2) THEN
3337 VV_DATA_TYPE := 'Text';
3338 ELSE
3339 VV_DATA_TYPE := 'Date';
3340 END IF;
3341
3342 IF VV_INTERFACE_COL.REQUIRED_FLAG = 'Y'
3343 THEN
3344 UPDATE BNE_INTERFACE_COLS_TL
3345 SET USER_HINT = '*List - '||VV_DATA_TYPE
3346 WHERE APPLICATION_ID = P_APPLICATION_ID
3347 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
3348 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
3349 ;
3350 ELSE
3351 UPDATE BNE_INTERFACE_COLS_TL
3352 SET USER_HINT = 'List - '||VV_DATA_TYPE
3353 WHERE APPLICATION_ID = P_APPLICATION_ID
3354 AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
3355 AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
3356 ;
3357 end if;
3358
3359 ----------------------------------------------
3360 -- Component Parameter List
3361 ----------------------------------------------
3362 ADD_LOV_PARAMETER_LIST(
3363 P_APPLICATION_SHORT_NAME => VV_APPLICATION_SHORT_NAME,
3364 P_PARAM_LIST_CODE => VV_PARAM_LIST_CODE,
3365 P_PARAM_LIST_NAME => 'Param List for '||P_INTERFACE_CODE||'.'||P_INTERFACE_COL_NAME,
3366 P_WINDOW_CAPTION => TRIM(P_WINDOW_CAPTION),
3367 P_WINDOW_WIDTH => P_WINDOW_WIDTH,
3368 P_WINDOW_HEIGHT => P_WINDOW_HEIGHT,
3369 P_TABLE_BLOCK_SIZE => NULL,
3370 P_TABLE_COLUMNS => P_TABLE_COLUMNS,
3371 P_TABLE_SELECT_COLUMNS => P_INTERFACE_COL_NAME,
3372 P_TABLE_COLUMN_ALIAS => NULL,
3373 P_TABLE_HEADERS => NULL,
3374 P_TABLE_SORT_ORDER => NULL,
3375 P_USER_NAME => VV_USER_NAME
3376 );
3377
3378 ----------------------------------------------
3379 -- Component
3380 ----------------------------------------------
3381 BNE_COMPONENTS_PKG.LOAD_ROW(
3382 x_component_asn => VV_APPLICATION_SHORT_NAME,
3383 x_component_code => VV_COMPONENT_CODE,
3384 x_object_version_number => 1,
3385 x_component_java_class => 'oracle.apps.bne.integrator.component.BneCalendarComponent',
3386 x_param_list_asn => VV_APPLICATION_SHORT_NAME,
3387 x_param_list_code => VV_PARAM_LIST_CODE,
3388 x_user_name => 'Component for '||P_INTERFACE_CODE||'.'||P_INTERFACE_COL_NAME,
3389 x_owner => VV_USER_NAME,
3390 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
3391 x_custom_mode => NULL
3392 );
3393 END CREATE_CALENDAR_LOV;
3394
3395
3396
3397
3398
3399
3400 --------------------------------------------------------------------------------
3401 -- PROCEDURE: CREATE_KFF --
3402 -- --
3403 -- DESCRIPTION: Create a Key Flexfield and generic LOV on an interface. --
3404 -- It is assumed that columns will already exist in the --
3405 -- interface in the form P_FLEX_SEG_COL_NAME_PREFIX%, for --
3406 -- example SEGMENT1,2,3 for P_FLEX_SEG_COL_NAME_PREFIX --
3407 -- of SEGMENT. An alias column will be created named --
3408 -- P_GROUP_NAME for this KFF, and all segments and this --
3409 -- alias column will be placed in a group P_GROUP_NAME. --
3410 -- The following parameters are only used in the LOV, for --
3411 -- upload validation, you need to develop a custom --
3412 -- validator to perform validation to your business rules. --
3413 -- (Refer to BneKFFValidator.java or --
3414 -- BneAccountingFlexValidator.java --
3415 -- P_VRULE --
3416 -- P_EFFECTIVE_DATE_COL --
3417 -- Reference: Web ADI Developers guide section 4.5 - --
3418 -- "Key Flexfield Validation/LOV Retrieval" --
3419 -- EXAMPLE: --
3420 -- BNE_INTEGRATOR_UTILS.CREATE_KFF --
3421 -- (P_APPLICATION_ID => P_APPLICATION_ID, --
3422 -- P_INTERFACE_CODE => P_INTERFACE_CODE, --
3423 -- P_FLEX_SEG_COL_NAME_PREFIX => 'SEGMENT', --
3424 -- P_GROUP_NAME => 'ACCOUNT', --
3425 -- P_REQUIRED_FLAG => 'N', --
3426 -- P_FLEX_APPLICATION_ID => 101, --
3427 -- P_FLEX_CODE => 'GL#', --
3428 -- P_FLEX_NUM => 101, --
3429 -- P_VRULE => 'my vrule', --
3430 -- P_EFFECTIVE_DATE_COL => 'DATE_COL01', --
3431 -- P_PROMPT_ABOVE => 'Accounting Flexfield', --
3432 -- P_PROMPT_LEFT => 'Accounting Flexfield', --
3433 -- P_USER_HINT => 'Enter Account', --
3434 -- P_USER_ID => P_USER_ID); --
3435 -- --
3436 -- MODIFICATION HISTORY --
3437 -- Date Username Description --
3438 -- 11-AUG-2006 dagroves Created. --
3439 --------------------------------------------------------------------------------
3440 PROCEDURE CREATE_KFF
3441 (P_APPLICATION_ID IN NUMBER,
3442 P_INTERFACE_CODE IN VARCHAR2,
3443 P_FLEX_SEG_COL_NAME_PREFIX IN VARCHAR2,
3444 P_GROUP_NAME IN VARCHAR2,
3445 P_REQUIRED_FLAG IN VARCHAR2,
3446 P_FLEX_APPLICATION_ID IN NUMBER,
3447 P_FLEX_CODE IN VARCHAR2,
3448 P_FLEX_NUM IN VARCHAR2,
3449 P_VRULE IN VARCHAR2,
3450 P_EFFECTIVE_DATE_COL IN VARCHAR2,
3451 P_PROMPT_ABOVE IN VARCHAR2,
3452 P_PROMPT_LEFT IN VARCHAR2,
3453 P_USER_HINT IN VARCHAR2,
3454 P_USER_ID IN NUMBER)
3455 IS
3456 VV_APPLICATION_SHORT_NAME VARCHAR2(30);
3457 VV_INTERFACE_CODE VARCHAR2(30);
3458 VV_FLEX_SEG_COL_NAME_PREFIX VARCHAR2(30);
3459 VV_GROUP_NAME VARCHAR2(30);
3460 VV_PARAM_LIST_CODE VARCHAR2(30);
3461 VV_COMPONENT_CODE VARCHAR2(30);
3462 VV_USER_NAME VARCHAR2(30);
3463 VV_DUMMY NUMBER;
3464 VV_SEQUENCE_NUM NUMBER;
3465 VV_DISPLAY_ORDER NUMBER;
3466 BEGIN
3467 VV_INTERFACE_CODE := TRIM(P_INTERFACE_CODE);
3468 VV_FLEX_SEG_COL_NAME_PREFIX := TRIM(P_FLEX_SEG_COL_NAME_PREFIX);
3469 VV_GROUP_NAME := TRIM(P_GROUP_NAME);
3470
3471 IF VV_FLEX_SEG_COL_NAME_PREFIX IS NULL THEN
3472 RAISE_APPLICATION_ERROR(-20000,'Required: The flex segment interface column name prefix is NULL.');
3473 END IF;
3474 -- we autogenerate component and param codes to a maximum of:
3475 -- VV_INTERFACE_CODE||_CXXXLPXD where XXX is the interface col sequence_num, and X is the parameter sequence num.
3476 IF LENGTH(VV_INTERFACE_CODE) > 21 THEN
3477 RAISE_APPLICATION_ERROR(-20000,'The interface column name '||VV_INTERFACE_CODE||' is too long to auto-generate parameter codes of the form VV_INTERFACE_CODE||''_CXXXLPXD''. Max size is 21.');
3478 END IF;
3479
3480 IF VV_GROUP_NAME IS NULL THEN
3481 RAISE_APPLICATION_ERROR(-20000,'Required: The group name is NULL.');
3482 END IF;
3483
3484 SELECT APPLICATION_SHORT_NAME
3485 INTO VV_APPLICATION_SHORT_NAME
3486 FROM FND_APPLICATION
3487 WHERE APPLICATION_ID = P_APPLICATION_ID;
3488
3489 SELECT USER_NAME
3490 INTO VV_USER_NAME
3491 FROM FND_USER
3492 WHERE USER_ID = P_USER_ID;
3493
3494 SELECT COUNT(*)
3495 into VV_dummy
3496 FROM BNE_INTERFACE_COLS_B
3497 WHERE APPLICATION_ID = P_APPLICATION_ID
3498 AND INTERFACE_CODE = VV_INTERFACE_CODE
3499 AND INTERFACE_COL_NAME like VV_FLEX_SEG_COL_NAME_PREFIX||'%';
3500
3501 if vv_dummy = 0
3502 then
3503 RAISE_APPLICATION_ERROR(-20000,'Missing: No columns match the The flex segment interface column name prefix:'||VV_FLEX_SEG_COL_NAME_PREFIX||'%');
3504 end if;
3505
3506 SELECT COUNT(*)
3507 INTO VV_DUMMY
3508 FROM BNE_INTERFACE_COLS_B
3509 WHERE APPLICATION_ID = P_APPLICATION_ID
3510 AND INTERFACE_CODE = VV_INTERFACE_CODE
3511 AND INTERFACE_COL_NAME = VV_GROUP_NAME;
3512
3513 if vv_dummy <> 0
3514 then
3515 RAISE_APPLICATION_ERROR(-20000,'Exists: Interface Column '||VV_GROUP_NAME||' is already in use, cannot create an alias column of this name. Choose another.');
3516 end if;
3517
3518 SELECT COUNT(*)
3519 INTO VV_DUMMY
3520 FROM BNE_INTERFACE_COLS_B
3521 WHERE APPLICATION_ID = P_APPLICATION_ID
3522 AND INTERFACE_CODE = VV_INTERFACE_CODE
3523 AND GROUP_NAME = VV_GROUP_NAME;
3524
3525 if vv_dummy <> 0
3526 then
3527 RAISE_APPLICATION_ERROR(-20000,'Missing: Group name '||VV_GROUP_NAME||' is already in use by '||to_char(VV_dummy)||' columns, choose another.');
3528 end if;
3529
3530 VV_PARAM_LIST_CODE := VV_INTERFACE_CODE||'_L';
3531 VV_COMPONENT_CODE := VV_INTERFACE_CODE||'_COMP';
3532
3533
3534 UPDATE BNE_INTERFACE_COLS_B
3535 SET GROUP_NAME = VV_GROUP_NAME,
3536 INTERFACE_COL_TYPE = 1,
3537 ENABLED_FLAG = 'Y',
3538 REQUIRED_FLAG = 'N',
3539 DISPLAY_FLAG = 'N',
3540 READ_ONLY_FLAG = 'N',
3541 NOT_NULL_FLAG = 'N',
3542 SUMMARY_FLAG = 'N',
3543 MAPPING_ENABLED_FLAG = 'Y',
3544 VAL_TYPE = 'KEYFLEXSEG',
3545 OA_FLEX_CODE = NULL,
3546 OA_FLEX_APPLICATION_ID = NULL,
3547 OA_FLEX_NUM = NULL,
3548 VAL_COMPONENT_APP_ID = NULL,
3549 VAL_COMPONENT_CODE = NULL,
3550 LOV_TYPE = NULL,
3551 OFFLINE_LOV_ENABLED_FLAG = 'N',
3552 LAST_UPDATE_DATE = SYSDATE,
3553 LAST_UPDATED_BY = P_USER_ID,
3554 LAST_UPDATE_LOGIN = 0
3555 WHERE APPLICATION_ID = P_APPLICATION_ID
3556 AND INTERFACE_CODE = VV_INTERFACE_CODE
3557 AND INTERFACE_COL_NAME like VV_FLEX_SEG_COL_NAME_PREFIX||'%';
3558
3559 SELECT MAX(SEQUENCE_NUM)+1, MAX(DISPLAY_ORDER) + 10
3560 INTO VV_SEQUENCE_NUM, VV_DISPLAY_ORDER
3561 FROM BNE_INTERFACE_COLS_B
3562 WHERE APPLICATION_ID = P_APPLICATION_ID
3563 AND INTERFACE_CODE = VV_INTERFACE_CODE;
3564
3565
3566 BNE_INTERFACE_COLS_PKG.LOAD_ROW(
3567 x_interface_asn => VV_APPLICATION_SHORT_NAME,
3568 x_interface_code => VV_INTERFACE_CODE,
3569 x_sequence_num => VV_SEQUENCE_NUM,
3570 x_interface_col_type => 2, -- alias column
3571 x_interface_col_name => VV_GROUP_NAME,
3572 x_enabled_flag => 'Y',
3573 x_required_flag => P_REQUIRED_FLAG,
3574 x_display_flag => 'Y',
3575 x_read_only_flag => 'N',
3576 x_not_null_flag => 'N',
3577 x_summary_flag => 'N',
3578 x_mapping_enabled_flag => 'N',
3579 x_data_type => 2,
3580 x_field_size => 25, -- arbitrary?
3581 x_default_type => NULL,
3582 x_default_value => NULL,
3583 x_segment_number => NULL,
3584 x_group_name => VV_GROUP_NAME,
3585 x_oa_flex_code => P_FLEX_CODE,
3586 x_oa_concat_flex => 'N',
3587 x_val_type => 'KEYFLEX',
3588 x_val_id_col => NULL,
3589 x_val_mean_col => NULL,
3590 x_val_desc_col => NULL,
3591 x_val_obj_name => 'oracle.apps.bne.integrator.validators.BneKFFValidator',
3592 x_val_addl_w_c => NULL,
3593 x_val_component_asn => VV_APPLICATION_SHORT_NAME,
3594 x_val_component_code => VV_COMPONENT_CODE,
3595 x_oa_flex_num => P_FLEX_NUM,
3596 x_oa_flex_application_id => P_FLEX_APPLICATION_ID,
3597 x_display_order => VV_DISPLAY_ORDER,
3598 x_upload_param_list_item_num => NULL,
3599 x_expanded_sql_query => NULL,
3600 x_object_version_number => 1,
3601 x_user_hint => P_USER_HINT,
3602 x_prompt_left => P_PROMPT_LEFT,
3603 x_user_help_text => NULL,
3604 x_prompt_above => P_PROMPT_ABOVE,
3605 x_owner => VV_USER_NAME,
3606 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
3607 x_lov_type => NULL,
3608 x_offline_lov_enabled_flag => 'N',
3609 x_custom_mode => NULL,
3610 x_variable_data_type_class => NULL,
3611 x_viewer_group => NULL,
3612 x_edit_type => NULL,
3613 x_val_query_asn => NULL,
3614 x_val_query_code => NULL,
3615 x_expanded_sql_query_asn => NULL,
3616 x_expanded_sql_query_code => NULL,
3617 x_display_width => NULL
3618 );
3619
3620
3621 ----------------------------------------------
3622 -- Component Parameter List
3623 ----------------------------------------------
3624 ADD_FLEX_LOV_PARAMETER_LIST(
3625 P_APPLICATION_SHORT_NAME => VV_APPLICATION_SHORT_NAME,
3626 P_PARAM_LIST_CODE => VV_PARAM_LIST_CODE,
3627 P_PARAM_LIST_NAME => 'Param List for '||P_INTERFACE_CODE||'.'||VV_GROUP_NAME,
3628 P_WINDOW_CAPTION => TRIM(P_PROMPT_ABOVE),
3629 P_WINDOW_WIDTH => NULL,
3630 P_WINDOW_HEIGHT => NULL,
3631 P_EFFECTIVE_DATE_COL => P_EFFECTIVE_DATE_COL, -- date col in sheet to get effective date.
3632 P_VRULE => P_VRULE,
3633 P_USER_NAME => VV_USER_NAME
3634 );
3635
3636 ----------------------------------------------
3637 -- Component
3638 ----------------------------------------------
3639 BNE_COMPONENTS_PKG.LOAD_ROW(
3640 x_component_asn => VV_APPLICATION_SHORT_NAME,
3641 x_component_code => VV_COMPONENT_CODE,
3642 x_object_version_number => 1,
3643 x_component_java_class => 'oracle.apps.bne.integrator.component.BneOAFlexComponent',
3644 x_param_list_asn => VV_APPLICATION_SHORT_NAME,
3645 x_param_list_code => VV_PARAM_LIST_CODE,
3646 x_user_name => 'Component for '||P_INTERFACE_CODE||'.'||VV_GROUP_NAME,
3647 x_owner => VV_USER_NAME,
3648 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
3649 x_custom_mode => NULL
3650 );
3651
3652 END CREATE_KFF;
3653
3654
3655 --------------------------------------------------------------------------------
3656 -- PROCEDURE: CREATE_DFF --
3657 -- --
3658 -- DESCRIPTION: Create a Descriptive Flexfield and generic LOV on an --
3659 -- interface. It is assumed that columns will already --
3660 -- exist in the interface in the form --
3661 -- P_FLEX_SEG_COL_NAME_PREFIX%, for example ATTRIBUTE1,2,3 --
3662 -- for P_FLEX_SEG_COL_NAME_PREFIX of ATTRIBUTE. --
3663 -- An alias column will be created named P_GROUP_NAME for --
3664 -- DFF, and all segments and this alias column will be --
3665 -- placed in a group P_GROUP_NAME. --
3666 -- If a P_CONTEXT_COL_NAME is set, it must correspond to an--
3667 -- existing column in the interface and it will be used as --
3668 -- an external reference column. It must correspond to the--
3669 -- Structure column as defined in the DFF Registered in --
3670 -- Oracle Applications. --
3671 -- The following parameters are only used in the LOV, for --
3672 -- upload validation, you need to develop a custom --
3673 -- validator to perform validation to your business rules. --
3674 -- (Refer to BneDFFValidator.java) --
3675 -- P_VRULE --
3676 -- P_EFFECTIVE_DATE_COL --
3677 -- Reference: Web ADI Developers guide section 4.7 - --
3678 -- "Descriptive Flexfield Validation/LOV Retrieval" --
3679 -- EXAMPLE: --
3680 -- BNE_INTEGRATOR_UTILS.CREATE_DFF --
3681 -- (P_APPLICATION_ID => P_APPLICATION_ID, --
3682 -- P_INTERFACE_CODE => P_INTERFACE_CODE, --
3683 -- P_FLEX_SEG_COL_NAME_PREFIX => 'ATTRIBUTE', --
3684 -- P_CONTEXT_COL_NAME => 'CONTEXT', --
3685 -- P_GROUP_NAME => 'JOURNAL_LINES', --
3686 -- P_REQUIRED_FLAG => 'N', --
3687 -- P_FLEX_APPLICATION_ID => 101, --
3688 -- P_FLEX_CODE => 'GL_JE_LINES', --
3689 -- P_VRULE => NULL, --
3690 -- P_EFFECTIVE_DATE_COL => 'DATE_COL01', --
3691 -- P_PROMPT_ABOVE => 'Journal Lines', --
3692 -- P_PROMPT_LEFT => 'Journal Lines', --
3693 -- P_USER_HINT => 'Enter Line', --
3694 -- P_USER_ID => P_USER_ID); --
3695 -- --
3696 -- MODIFICATION HISTORY --
3697 -- Date Username Description --
3698 -- 11-AUG-2006 dagroves Created. --
3699 --------------------------------------------------------------------------------
3700 PROCEDURE CREATE_DFF
3701 (P_APPLICATION_ID IN NUMBER,
3702 P_INTERFACE_CODE IN VARCHAR2,
3703 P_FLEX_SEG_COL_NAME_PREFIX IN VARCHAR2,
3704 P_CONTEXT_COL_NAME IN VARCHAR2,
3705 P_GROUP_NAME IN VARCHAR2,
3706 P_REQUIRED_FLAG IN VARCHAR2,
3707 P_FLEX_APPLICATION_ID IN NUMBER,
3708 P_FLEX_CODE IN VARCHAR2,
3709 P_VRULE IN VARCHAR2,
3710 P_EFFECTIVE_DATE_COL IN VARCHAR2,
3711 P_PROMPT_ABOVE IN VARCHAR2,
3712 P_PROMPT_LEFT IN VARCHAR2,
3713 P_USER_HINT IN VARCHAR2,
3714 P_USER_ID IN NUMBER)
3715 IS
3716 VV_APPLICATION_SHORT_NAME VARCHAR2(30);
3717 VV_INTERFACE_CODE VARCHAR2(30);
3718 VV_FLEX_SEG_COL_NAME_PREFIX VARCHAR2(30);
3719 VV_CONTEXT_COL_NAME VARCHAR2(30);
3720 VV_GROUP_NAME VARCHAR2(30);
3721 VV_PARAM_LIST_CODE VARCHAR2(30);
3722 VV_COMPONENT_CODE VARCHAR2(30);
3723 VV_USER_NAME VARCHAR2(30);
3724 VV_DUMMY NUMBER;
3725 VV_SEQUENCE_NUM NUMBER;
3726 VV_DISPLAY_ORDER NUMBER;
3727 BEGIN
3728 VV_INTERFACE_CODE := TRIM(P_INTERFACE_CODE);
3729 VV_FLEX_SEG_COL_NAME_PREFIX := TRIM(P_FLEX_SEG_COL_NAME_PREFIX);
3730 VV_CONTEXT_COL_NAME := TRIM(P_CONTEXT_COL_NAME);
3731 VV_GROUP_NAME := TRIM(P_GROUP_NAME);
3732
3733 IF VV_FLEX_SEG_COL_NAME_PREFIX IS NULL THEN
3734 RAISE_APPLICATION_ERROR(-20000,'Required: The flex segment interface column name prefix is NULL.');
3735 END IF;
3736 -- we autogenerate component and param codes to a maximum of:
3737 -- VV_INTERFACE_CODE||_CXXXLPXD where XXX is the interface col sequence_num, and X is the parameter sequence num.
3738 IF LENGTH(VV_INTERFACE_CODE) > 21 THEN
3739 RAISE_APPLICATION_ERROR(-20000,'The interface column name '||VV_INTERFACE_CODE||' is too long to auto-generate parameter codes of the form VV_INTERFACE_CODE||''_CXXXLPXD''. Max size is 21.');
3740 END IF;
3741
3742 IF VV_GROUP_NAME IS NULL THEN
3743 RAISE_APPLICATION_ERROR(-20000,'Required: The group name is NULL.');
3744 END IF;
3745
3746 SELECT APPLICATION_SHORT_NAME
3747 INTO VV_APPLICATION_SHORT_NAME
3748 FROM FND_APPLICATION
3749 WHERE APPLICATION_ID = P_APPLICATION_ID;
3750
3751 SELECT USER_NAME
3752 INTO VV_USER_NAME
3753 FROM FND_USER
3754 WHERE USER_ID = P_USER_ID;
3755
3756 SELECT COUNT(*)
3757 into VV_dummy
3758 FROM BNE_INTERFACE_COLS_B
3759 WHERE APPLICATION_ID = P_APPLICATION_ID
3760 AND INTERFACE_CODE = VV_INTERFACE_CODE
3761 AND INTERFACE_COL_NAME like VV_FLEX_SEG_COL_NAME_PREFIX||'%';
3762
3763 if vv_dummy = 0
3764 then
3765 RAISE_APPLICATION_ERROR(-20000,'Missing: No columns match the The flex segment interface column name prefix:'||VV_FLEX_SEG_COL_NAME_PREFIX||'%');
3766 end if;
3767
3768 SELECT COUNT(*)
3769 INTO VV_DUMMY
3770 FROM BNE_INTERFACE_COLS_B
3771 WHERE APPLICATION_ID = P_APPLICATION_ID
3772 AND INTERFACE_CODE = VV_INTERFACE_CODE
3773 AND INTERFACE_COL_NAME = VV_GROUP_NAME;
3774
3775 if vv_dummy <> 0
3776 then
3777 RAISE_APPLICATION_ERROR(-20000,'Exists: Interface Column '||VV_GROUP_NAME||' is already in use, cannot create an alias column of this name. Choose another.');
3778 end if;
3779
3780 SELECT COUNT(*)
3781 INTO VV_DUMMY
3782 FROM BNE_INTERFACE_COLS_B
3783 WHERE APPLICATION_ID = P_APPLICATION_ID
3784 AND INTERFACE_CODE = VV_INTERFACE_CODE
3785 AND GROUP_NAME = VV_GROUP_NAME;
3786
3787 if vv_dummy <> 0
3788 then
3789 RAISE_APPLICATION_ERROR(-20000,'Missing: Group name '||VV_GROUP_NAME||' is already in use by '||to_char(VV_dummy)||' columns, choose another.');
3790 end if;
3791
3792 VV_PARAM_LIST_CODE := VV_INTERFACE_CODE||'_L';
3793 VV_COMPONENT_CODE := VV_INTERFACE_CODE||'_COMP';
3794
3795 -- Check external context field.
3796 IF VV_CONTEXT_COL_NAME IS NOT NULL
3797 THEN
3798 SELECT COUNT(*)
3799 INTO VV_DUMMY
3800 FROM BNE_INTERFACE_COLS_B
3801 WHERE APPLICATION_ID = P_APPLICATION_ID
3802 AND INTERFACE_CODE = VV_INTERFACE_CODE
3803 AND INTERFACE_COL_NAME = VV_CONTEXT_COL_NAME;
3804
3805 if vv_dummy = 0
3806 then
3807 RAISE_APPLICATION_ERROR(-20000,'Missing: Context Interface Column '||VV_CONTEXT_COL_NAME||' does not exists in the interface.');
3808 end if;
3809
3810 -- update column after segments.
3811 END IF;
3812
3813 UPDATE BNE_INTERFACE_COLS_B
3814 SET GROUP_NAME = VV_GROUP_NAME,
3815 INTERFACE_COL_TYPE = 1,
3816 ENABLED_FLAG = 'Y',
3817 REQUIRED_FLAG = 'N',
3818 DISPLAY_FLAG = 'N',
3819 READ_ONLY_FLAG = 'N',
3820 NOT_NULL_FLAG = 'N',
3821 SUMMARY_FLAG = 'N',
3822 MAPPING_ENABLED_FLAG = 'Y',
3823 VAL_TYPE = 'DESCFLEXSEG',
3824 OA_FLEX_CODE = NULL,
3825 OA_FLEX_APPLICATION_ID = NULL,
3826 OA_FLEX_NUM = NULL,
3827 VAL_COMPONENT_APP_ID = NULL,
3828 VAL_COMPONENT_CODE = NULL,
3829 LOV_TYPE = NULL,
3830 OFFLINE_LOV_ENABLED_FLAG = 'N',
3831 LAST_UPDATE_DATE = SYSDATE,
3832 LAST_UPDATED_BY = P_USER_ID,
3833 LAST_UPDATE_LOGIN = 0
3834 WHERE APPLICATION_ID = P_APPLICATION_ID
3835 AND INTERFACE_CODE = VV_INTERFACE_CODE
3836 AND INTERFACE_COL_NAME LIKE VV_FLEX_SEG_COL_NAME_PREFIX||'%';
3837
3838 -- Update External context field AFTER segments, as they may share the same interface_col_name prefix.
3839 IF VV_CONTEXT_COL_NAME IS NOT NULL
3840 THEN
3841 UPDATE BNE_INTERFACE_COLS_B
3842 SET GROUP_NAME = VV_GROUP_NAME,
3843 INTERFACE_COL_TYPE = 1,
3844 ENABLED_FLAG = 'Y',
3845 REQUIRED_FLAG = 'N',
3846 DISPLAY_FLAG = 'Y',
3847 READ_ONLY_FLAG = 'N',
3848 NOT_NULL_FLAG = 'N',
3849 SUMMARY_FLAG = 'N',
3850 MAPPING_ENABLED_FLAG = 'Y',
3851 VAL_TYPE = 'DESCFLEXCONTEXT',
3852 OA_FLEX_CODE = P_FLEX_CODE,
3853 OA_FLEX_APPLICATION_ID = P_FLEX_APPLICATION_ID,
3854 OA_FLEX_NUM = NULL,
3855 VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
3856 VAL_COMPONENT_CODE = VV_COMPONENT_CODE,
3857 LOV_TYPE = NULL,
3858 OFFLINE_LOV_ENABLED_FLAG = 'N',
3859 LAST_UPDATE_DATE = SYSDATE,
3860 LAST_UPDATED_BY = P_USER_ID,
3861 LAST_UPDATE_LOGIN = 0
3862 WHERE APPLICATION_ID = P_APPLICATION_ID
3863 AND INTERFACE_CODE = VV_INTERFACE_CODE
3864 AND INTERFACE_COL_NAME = VV_CONTEXT_COL_NAME;
3865
3866 END IF;
3867
3868 SELECT MAX(SEQUENCE_NUM)+1, MAX(DISPLAY_ORDER) + 10
3869 INTO VV_SEQUENCE_NUM, VV_DISPLAY_ORDER
3870 FROM BNE_INTERFACE_COLS_B
3871 WHERE APPLICATION_ID = P_APPLICATION_ID
3872 AND INTERFACE_CODE = VV_INTERFACE_CODE;
3873
3874
3875 BNE_INTERFACE_COLS_PKG.LOAD_ROW(
3876 x_interface_asn => VV_APPLICATION_SHORT_NAME,
3877 x_interface_code => VV_INTERFACE_CODE,
3878 x_sequence_num => VV_SEQUENCE_NUM,
3879 x_interface_col_type => 2, -- alias column
3880 x_interface_col_name => VV_GROUP_NAME,
3881 x_enabled_flag => 'Y',
3882 x_required_flag => P_REQUIRED_FLAG,
3883 x_display_flag => 'Y',
3884 x_read_only_flag => 'N',
3885 x_not_null_flag => 'N',
3886 x_summary_flag => 'N',
3887 x_mapping_enabled_flag => 'Y',
3888 x_data_type => 2,
3889 x_field_size => 25, -- arbitrary?
3890 x_default_type => NULL,
3891 x_default_value => NULL,
3892 x_segment_number => NULL,
3893 x_group_name => VV_GROUP_NAME,
3894 x_oa_flex_code => P_FLEX_CODE,
3895 x_oa_concat_flex => 'Y',
3896 x_val_type => 'DESCFLEX',
3897 x_val_id_col => NULL,
3898 x_val_mean_col => NULL,
3899 x_val_desc_col => NULL,
3900 x_val_obj_name => 'oracle.apps.bne.integrator.validators.BneDFFValidator',
3901 x_val_addl_w_c => NULL,
3902 x_val_component_asn => VV_APPLICATION_SHORT_NAME,
3903 x_val_component_code => VV_COMPONENT_CODE,
3904 x_oa_flex_num => NULL,
3905 x_oa_flex_application_id => P_FLEX_APPLICATION_ID,
3906 x_display_order => VV_DISPLAY_ORDER,
3907 x_upload_param_list_item_num => NULL,
3908 x_expanded_sql_query => NULL,
3909 x_object_version_number => 1,
3910 x_user_hint => P_USER_HINT,
3911 x_prompt_left => P_PROMPT_LEFT,
3912 x_user_help_text => NULL,
3913 x_prompt_above => P_PROMPT_ABOVE,
3914 x_owner => VV_USER_NAME,
3915 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
3916 x_lov_type => NULL,
3917 x_offline_lov_enabled_flag => 'N',
3918 x_custom_mode => NULL,
3919 x_variable_data_type_class => NULL,
3920 x_viewer_group => NULL,
3921 x_edit_type => NULL,
3922 x_val_query_asn => NULL,
3923 x_val_query_code => NULL,
3924 x_expanded_sql_query_asn => NULL,
3925 x_expanded_sql_query_code => NULL,
3926 x_display_width => NULL
3927 );
3928
3929
3930 ----------------------------------------------
3931 -- Component Parameter List
3932 ----------------------------------------------
3933 ADD_FLEX_LOV_PARAMETER_LIST(
3934 P_APPLICATION_SHORT_NAME => VV_APPLICATION_SHORT_NAME,
3935 P_PARAM_LIST_CODE => VV_PARAM_LIST_CODE,
3936 P_PARAM_LIST_NAME => 'Param List for '||P_INTERFACE_CODE||'.'||VV_GROUP_NAME,
3937 P_WINDOW_CAPTION => TRIM(P_PROMPT_ABOVE),
3938 P_WINDOW_WIDTH => NULL,
3939 P_WINDOW_HEIGHT => NULL,
3940 P_EFFECTIVE_DATE_COL => P_EFFECTIVE_DATE_COL, -- date col in sheet to get effective date.
3941 P_VRULE => P_VRULE,
3942 P_USER_NAME => VV_USER_NAME
3943 );
3944
3945 ----------------------------------------------
3946 -- Component
3947 ----------------------------------------------
3948 BNE_COMPONENTS_PKG.LOAD_ROW(
3949 x_component_asn => VV_APPLICATION_SHORT_NAME,
3950 x_component_code => VV_COMPONENT_CODE,
3951 x_object_version_number => 1,
3952 x_component_java_class => 'oracle.apps.bne.integrator.component.BneOAFlexComponent',
3953 x_param_list_asn => VV_APPLICATION_SHORT_NAME,
3954 x_param_list_code => VV_PARAM_LIST_CODE,
3955 x_user_name => 'Component for '||P_INTERFACE_CODE||'.'||VV_GROUP_NAME,
3956 x_owner => VV_USER_NAME,
3957 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
3958 x_custom_mode => NULL
3959 );
3960
3961 END CREATE_DFF;
3962
3963
3964 --------------------------------------------------------------------------------
3965 -- PROCEDURE: CREATE_CCID_KFF --
3966 -- --
3967 -- DESCRIPTION: Create a Key Flexfield and generic LOV on an interface. --
3968 -- It is assumed that a code combination column will --
3969 -- already exist in the interface and be named --
3970 -- P_INTERFACE_COL_NAME. --
3971 -- Alias columns will be created in the interface named --
3972 -- P_INTERFACE_COL_NAME||'_SEGMENT1' to --
3973 -- P_INTERFACE_COL_NAME||'_SEGMENT'||P_NUM_FLEX_SEGS. --
3974 -- The following parameters are only used in the LOV, for --
3975 -- upload validation, you need to develop a custom --
3976 -- validator to perform validation to your business rules. --
3977 -- (Refer to BneKFFValidator.java or --
3978 -- BneAccountingFlexValidator.java --
3979 -- P_VRULE --
3980 -- P_EFFECTIVE_DATE_COL --
3981 -- Reference: Web ADI Developers guide section 4.5 - --
3982 -- "Key Flexfield Validation/LOV Retrieval" --
3983 -- --
3984 -- EXAMPLE: --
3985 -- BNE_INTEGRATOR_UTILS.CREATE_CCID_KFF --
3986 -- (P_APPLICATION_ID => P_APPLICATION_ID, --
3987 -- P_INTERFACE_CODE => P_INTERFACE_CODE, --
3988 -- P_INTERFACE_COL_NAME => 'KEYFLEX1_CCID', --
3989 -- P_NUM_FLEX_SEGS => 10, --
3990 -- P_GROUP_NAME => 'CCID_ACCOUNT1', --
3991 -- P_FLEX_APPLICATION_ID => 101, --
3992 -- P_FLEX_CODE => 'GL#', --
3993 -- P_FLEX_NUM => '50214', --
3994 -- P_VRULE => NULL, --
3995 -- P_EFFECTIVE_DATE_COL => 'DATE_COL01', --
3996 -- P_PROMPT_ABOVE => 'ADB Accounting Flexfield',--
3997 -- P_PROMPT_LEFT => 'ADB Accounting Flexfield',--
3998 -- P_USER_HINT => 'Enter Account', --
3999 -- P_USER_ID => P_USER_ID); --
4000 -- --
4001 -- MODIFICATION HISTORY --
4002 -- Date Username Description --
4003 -- 11-AUG-2006 dagroves Created. --
4004 --------------------------------------------------------------------------------
4005 PROCEDURE CREATE_CCID_KFF
4006 (P_APPLICATION_ID IN NUMBER,
4007 P_INTERFACE_CODE IN VARCHAR2,
4008 P_INTERFACE_COL_NAME IN VARCHAR2,
4009 P_NUM_FLEX_SEGS IN NUMBER,
4010 P_GROUP_NAME IN VARCHAR2,
4011 P_FLEX_APPLICATION_ID IN NUMBER,
4012 P_FLEX_CODE IN VARCHAR2,
4013 P_FLEX_NUM IN VARCHAR2,
4014 P_VRULE IN VARCHAR2,
4015 P_EFFECTIVE_DATE_COL IN VARCHAR2,
4016 P_PROMPT_ABOVE IN VARCHAR2,
4017 P_PROMPT_LEFT IN VARCHAR2,
4018 P_USER_HINT IN VARCHAR2,
4019 P_USER_ID IN NUMBER)
4020 IS
4021 VV_APPLICATION_SHORT_NAME VARCHAR2(30);
4022 VV_INTERFACE_CODE VARCHAR2(30);
4023 VV_INTERFACE_COL_NAME VARCHAR2(30);
4024 VV_FLEX_SEG_COL_NAME_PREFIX VARCHAR2(30);
4025 VV_GROUP_NAME VARCHAR2(30);
4026 VV_PARAM_LIST_CODE VARCHAR2(30);
4027 VV_COMPONENT_CODE VARCHAR2(30);
4028 VV_USER_NAME VARCHAR2(30);
4029 VV_DUMMY NUMBER;
4030 VV_SEQUENCE_NUM NUMBER;
4031 VV_DISPLAY_ORDER NUMBER;
4032 BEGIN
4033 VV_INTERFACE_CODE := TRIM(P_INTERFACE_CODE);
4034 VV_INTERFACE_COL_NAME := TRIM(P_INTERFACE_COL_NAME);
4035 VV_FLEX_SEG_COL_NAME_PREFIX := VV_INTERFACE_COL_NAME||'_SEGMENT';
4036 VV_GROUP_NAME := TRIM(P_GROUP_NAME);
4037
4038 IF VV_FLEX_SEG_COL_NAME_PREFIX IS NULL THEN
4039 RAISE_APPLICATION_ERROR(-20000,'Required: The flex segment interface column name prefix is NULL.');
4040 END IF;
4041 -- we autogenerate component and param codes to a maximum of:
4042 -- VV_INTERFACE_CODE||_CXXXLPXD where XXX is the interface col sequence_num, and X is the parameter sequence num.
4043 IF LENGTH(VV_INTERFACE_CODE) > 21 THEN
4044 RAISE_APPLICATION_ERROR(-20000,'The interface column name '||VV_INTERFACE_CODE||' is too long to auto-generate parameter codes of the form VV_INTERFACE_CODE||''_CXXXLPXD''. Max size is 21.');
4045 END IF;
4046
4047 IF VV_GROUP_NAME IS NULL THEN
4048 RAISE_APPLICATION_ERROR(-20000,'Required: The group name is NULL.');
4049 END IF;
4050
4051 IF P_NUM_FLEX_SEGS < 2 THEN
4052 RAISE_APPLICATION_ERROR(-20000,'Required: There should be more than one segment column.');
4053 END IF;
4054
4055 SELECT APPLICATION_SHORT_NAME
4056 INTO VV_APPLICATION_SHORT_NAME
4057 FROM FND_APPLICATION
4058 WHERE APPLICATION_ID = P_APPLICATION_ID;
4059
4060 SELECT USER_NAME
4061 INTO VV_USER_NAME
4062 FROM FND_USER
4063 WHERE USER_ID = P_USER_ID;
4064
4065 BEGIN
4066 SELECT SEQUENCE_NUM
4067 INTO VV_SEQUENCE_NUM
4068 FROM BNE_INTERFACE_COLS_B
4069 WHERE APPLICATION_ID = P_APPLICATION_ID
4070 AND INTERFACE_CODE = VV_INTERFACE_CODE
4071 AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
4072 EXCEPTION
4073 WHEN NO_DATA_FOUND THEN
4074 RAISE_APPLICATION_ERROR(-20000,'Missing: No columns match the interface column name:'||VV_INTERFACE_COL_NAME);
4075 END;
4076
4077 SELECT COUNT(*)
4078 INTO VV_DUMMY
4079 FROM BNE_INTERFACE_COLS_B
4080 WHERE APPLICATION_ID = P_APPLICATION_ID
4081 AND INTERFACE_CODE = VV_INTERFACE_CODE
4082 AND GROUP_NAME = VV_GROUP_NAME;
4083
4084 if vv_dummy <> 0
4085 then
4086 RAISE_APPLICATION_ERROR(-20000,'Group name '||VV_GROUP_NAME||' is already in use by '||to_char(VV_dummy)||' columns, choose another.');
4087 end if;
4088
4089 VV_PARAM_LIST_CODE := VV_INTERFACE_CODE||'_L';
4090 VV_COMPONENT_CODE := VV_INTERFACE_CODE||'_COMP';
4091
4092
4093 UPDATE BNE_INTERFACE_COLS_B
4094 SET GROUP_NAME = VV_GROUP_NAME,
4095 INTERFACE_COL_TYPE = 1,
4096 ENABLED_FLAG = 'Y',
4097 READ_ONLY_FLAG = 'N',
4098 SUMMARY_FLAG = 'N',
4099 VAL_TYPE = 'KEYFLEXID',
4100 OA_FLEX_CODE = P_FLEX_CODE,
4101 OA_FLEX_APPLICATION_ID = P_FLEX_APPLICATION_ID,
4102 OA_FLEX_NUM = P_FLEX_NUM,
4103 OA_CONCAT_FLEX = 'N',
4104 VAL_OBJ_NAME = 'oracle.apps.bne.integrator.validators.BneKFFValidator',
4105 VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
4106 VAL_COMPONENT_CODE = VV_COMPONENT_CODE,
4107 LOV_TYPE = 'NONE',
4108 OFFLINE_LOV_ENABLED_FLAG = 'N',
4109 LAST_UPDATE_DATE = SYSDATE,
4110 LAST_UPDATED_BY = P_USER_ID,
4111 LAST_UPDATE_LOGIN = 0
4112 WHERE APPLICATION_ID = P_APPLICATION_ID
4113 AND INTERFACE_CODE = VV_INTERFACE_CODE
4114 AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
4115
4116 UPDATE BNE_INTERFACE_COLS_TL
4117 SET
4118 USER_HINT = TRIM(P_USER_HINT),
4119 PROMPT_LEFT = TRIM(P_PROMPT_LEFT),
4120 PROMPT_ABOVE = TRIM(P_PROMPT_ABOVE),
4121 LAST_UPDATE_DATE = SYSDATE,
4122 LAST_UPDATED_BY = P_USER_ID,
4123 LAST_UPDATE_LOGIN = 0,
4124 SOURCE_LANG = USERENV('LANG')
4125 WHERE APPLICATION_ID = P_APPLICATION_ID
4126 AND INTERFACE_CODE = VV_INTERFACE_CODE
4127 AND SEQUENCE_NUM = VV_SEQUENCE_NUM
4128 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
4129
4130
4131 SELECT MAX(SEQUENCE_NUM)+1, MAX(DISPLAY_ORDER) + 10
4132 INTO VV_SEQUENCE_NUM, VV_DISPLAY_ORDER
4133 FROM BNE_INTERFACE_COLS_B
4134 WHERE APPLICATION_ID = P_APPLICATION_ID
4135 AND INTERFACE_CODE = VV_INTERFACE_CODE;
4136
4137 -- create the segment interface columns.
4138 FOR I IN 1..P_NUM_FLEX_SEGS
4139 LOOP
4140 BNE_INTERFACE_COLS_PKG.LOAD_ROW(
4141 x_interface_asn => VV_APPLICATION_SHORT_NAME,
4142 x_interface_code => VV_INTERFACE_CODE,
4143 x_sequence_num => VV_SEQUENCE_NUM,
4144 x_interface_col_type => 2, -- alias column
4145 x_interface_col_name => VV_FLEX_SEG_COL_NAME_PREFIX||TO_CHAR(I),
4146 x_enabled_flag => 'Y',
4147 x_required_flag => 'N',
4148 x_display_flag => 'N',
4149 x_read_only_flag => 'N',
4150 x_not_null_flag => 'N',
4151 x_summary_flag => 'N',
4152 x_mapping_enabled_flag => 'Y',
4153 x_data_type => 2,
4154 x_field_size => 25, -- arbitrary?
4155 x_default_type => NULL,
4156 x_default_value => NULL,
4157 x_segment_number => I,
4158 x_group_name => VV_GROUP_NAME,
4159 x_oa_flex_code => NULL,
4160 x_oa_concat_flex => NULL,
4161 x_val_type => 'KEYFLEXIDSEG',
4162 x_val_id_col => NULL,
4163 x_val_mean_col => NULL,
4164 x_val_desc_col => NULL,
4165 x_val_obj_name => NULL,
4166 x_val_addl_w_c => NULL,
4167 x_val_component_asn => NULL,
4168 x_val_component_code => NULL,
4169 x_oa_flex_num => NULL,
4170 x_oa_flex_application_id => NULL,
4171 x_display_order => VV_DISPLAY_ORDER,
4172 x_upload_param_list_item_num => NULL,
4173 x_expanded_sql_query => NULL,
4174 x_object_version_number => 1,
4175 x_user_hint => NULL,
4176 x_prompt_left => NULL,
4177 x_user_help_text => NULL,
4178 x_prompt_above => NULL,
4179 x_owner => VV_USER_NAME,
4180 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
4181 x_lov_type => 'NONE',
4182 x_offline_lov_enabled_flag => 'N',
4183 x_custom_mode => NULL,
4184 x_variable_data_type_class => NULL,
4185 x_viewer_group => NULL,
4186 x_edit_type => NULL,
4187 x_val_query_asn => NULL,
4188 x_val_query_code => NULL,
4189 x_expanded_sql_query_asn => NULL,
4190 x_expanded_sql_query_code => NULL,
4191 x_display_width => NULL
4192 );
4193 VV_SEQUENCE_NUM := VV_SEQUENCE_NUM + 1;
4194 VV_DISPLAY_ORDER := VV_DISPLAY_ORDER + 10;
4195 END LOOP;
4196
4197 ----------------------------------------------
4198 -- Component Parameter List
4199 ----------------------------------------------
4200 ADD_FLEX_LOV_PARAMETER_LIST(
4201 P_APPLICATION_SHORT_NAME => VV_APPLICATION_SHORT_NAME,
4202 P_PARAM_LIST_CODE => VV_PARAM_LIST_CODE,
4203 P_PARAM_LIST_NAME => 'Param List for '||P_INTERFACE_CODE||'.'||VV_GROUP_NAME,
4204 P_WINDOW_CAPTION => TRIM(P_PROMPT_ABOVE),
4205 P_WINDOW_WIDTH => NULL,
4206 P_WINDOW_HEIGHT => NULL,
4207 P_EFFECTIVE_DATE_COL => P_EFFECTIVE_DATE_COL, -- date col in sheet to get effective date.
4208 P_VRULE => P_VRULE,
4209 P_USER_NAME => VV_USER_NAME
4210 );
4211
4212 ----------------------------------------------
4213 -- Component
4214 ----------------------------------------------
4215 BNE_COMPONENTS_PKG.LOAD_ROW(
4216 x_component_asn => VV_APPLICATION_SHORT_NAME,
4217 x_component_code => VV_COMPONENT_CODE,
4218 x_object_version_number => 1,
4219 x_component_java_class => 'oracle.apps.bne.integrator.component.BneOAFlexComponent',
4220 x_param_list_asn => VV_APPLICATION_SHORT_NAME,
4221 x_param_list_code => VV_PARAM_LIST_CODE,
4222 x_user_name => 'Component for '||P_INTERFACE_CODE||'.'||VV_GROUP_NAME,
4223 x_owner => VV_USER_NAME,
4224 x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
4225 x_custom_mode => NULL
4226 );
4227
4228 END CREATE_CCID_KFF;
4229
4230 --------------------------------------------------------------------------------
4231 -- FUNCTION: DELETE_INTEGRATOR --
4232 -- --
4233 -- DESCRIPTION: Delete the given integrator. --
4234 -- This will include all subsiduary structures: --
4235 -- - Integrator and attached Parameter Lists --
4236 -- - Interfaces as per DELETE_ALL_INTERFACES() --
4237 -- - Contents as per DELETE_ALL_CONTENTS() --
4238 -- - Mappings as per DELETE_ALL_MAPPINGS() --
4239 -- - Layouts as per DELETE_ALL_LAYOUTS() --
4240 -- - Duplicate Profiles as per DELETE_ALL_DUP_PROFILES() --
4241 -- - Graphs/Graph Columns --
4242 -- The number of Integrators deleted is returned (0 or 1). --
4243 -- No commits are done. --
4244 -- --
4245 -- MODIFICATION HISTORY --
4246 -- Date Username Description --
4247 -- 20-JUL-2006 dagroves Created. --
4248 --------------------------------------------------------------------------------
4249 FUNCTION DELETE_INTEGRATOR
4250 (P_APPLICATION_ID IN NUMBER,
4251 P_INTEGRATOR_CODE IN VARCHAR2)
4252 RETURN NUMBER
4253 IS
4254 VV_COUNT NUMBER;
4255 VV_DUMMY NUMBER;
4256 VV_IMPORT_PROG_LIST_KEYS BNEKEY_TAB;
4257 VV_UPLOAD_LIST_APP_ID NUMBER(15);
4258 VV_UPLOAD_LIST_CODE VARCHAR2(30);
4259 VV_UPLOAD_SERV_LIST_APP_ID NUMBER(15);
4260 VV_UPLOAD_SERV_LIST_CODE VARCHAR2(30);
4261 VV_IMPORT_LIST_APP_ID NUMBER(15);
4262 VV_IMPORT_LIST_CODE VARCHAR2(30);
4263 VV_CREATE_DOC_LIST_APP_ID NUMBER(15);
4264 VV_CREATE_DOC_LIST_CODE VARCHAR2(30);
4265 VV_SESSION_LIST_APP_ID NUMBER(15);
4266 VV_SESSION_LIST_CODE VARCHAR2(30);
4267 VV_SECURITY_RULE_APP_ID NUMBER(15);
4268 VV_SECURITY_RULE_CODE VARCHAR2(30);
4269 BEGIN
4270 SELECT COUNT(*)
4271 INTO VV_COUNT
4272 FROM BNE_INTEGRATORS_B
4273 WHERE APPLICATION_ID = P_APPLICATION_ID
4274 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4275
4276 IF VV_COUNT = 0
4277 THEN
4278 RETURN(0);
4279 END IF;
4280
4281 -- Store import parameter lists referenced.
4282 BEGIN
4283 SELECT IMPORT_PARAM_LIST_APP_ID, IMPORT_PARAM_LIST_CODE
4284 BULK COLLECT
4285 INTO VV_IMPORT_PROG_LIST_KEYS
4286 FROM BNE_IMPORT_PROGRAMS
4287 WHERE APPLICATION_ID = P_APPLICATION_ID
4288 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4289 EXCEPTION
4290 WHEN NO_DATA_FOUND THEN NULL;
4291 END;
4292
4293 -- Store integrator level list FKs.
4294 SELECT
4295 UPLOAD_PARAM_LIST_APP_ID, UPLOAD_PARAM_LIST_CODE,
4296 UPLOAD_SERV_PARAM_LIST_APP_ID, UPLOAD_SERV_PARAM_LIST_CODE,
4297 IMPORT_PARAM_LIST_APP_ID, IMPORT_PARAM_LIST_CODE,
4298 CREATE_DOC_LIST_APP_ID, CREATE_DOC_LIST_CODE,
4299 SESSION_PARAM_LIST_APP_ID, SESSION_PARAM_LIST_CODE
4300 INTO
4301 VV_UPLOAD_LIST_APP_ID, VV_UPLOAD_LIST_CODE,
4302 VV_UPLOAD_SERV_LIST_APP_ID, VV_UPLOAD_SERV_LIST_CODE,
4303 VV_IMPORT_LIST_APP_ID, VV_IMPORT_LIST_CODE,
4304 VV_CREATE_DOC_LIST_APP_ID, VV_CREATE_DOC_LIST_CODE,
4305 VV_SESSION_LIST_APP_ID, VV_SESSION_LIST_CODE
4306 FROM BNE_INTEGRATORS_B
4307 WHERE APPLICATION_ID = P_APPLICATION_ID
4308 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4309
4310 VV_DUMMY := DELETE_ALL_MAPPINGS(P_APPLICATION_ID, P_INTEGRATOR_CODE);
4311 VV_DUMMY := DELETE_ALL_CONTENTS(P_APPLICATION_ID, P_INTEGRATOR_CODE);
4312 VV_DUMMY := DELETE_ALL_LAYOUTS(P_APPLICATION_ID, P_INTEGRATOR_CODE);
4313 VV_DUMMY := DELETE_ALL_INTERFACES(P_APPLICATION_ID, P_INTEGRATOR_CODE);
4314 VV_DUMMY := DELETE_ALL_DUP_PROFILES(P_APPLICATION_ID, P_INTEGRATOR_CODE);
4315
4316
4317 DELETE FROM BNE_INTEGRATORS_B
4318 WHERE APPLICATION_ID = P_APPLICATION_ID
4319 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4320
4321 DELETE FROM BNE_INTEGRATORS_TL
4322 WHERE APPLICATION_ID = P_APPLICATION_ID
4323 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4324
4325 DELETE FROM BNE_GRAPHS_B
4326 WHERE APPLICATION_ID = P_APPLICATION_ID
4327 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4328
4329 DELETE FROM BNE_GRAPHS_TL
4330 WHERE APPLICATION_ID = P_APPLICATION_ID
4331 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4332
4333 DELETE FROM BNE_GRAPH_COLUMNS
4334 WHERE APPLICATION_ID = P_APPLICATION_ID
4335 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4336
4337 DELETE FROM BNE_IMPORT_PROGRAMS
4338 WHERE APPLICATION_ID = P_APPLICATION_ID
4339 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
4340
4341 BEGIN
4342 SELECT SECURITY_RULE_APP_ID, SECURITY_RULE_CODE
4343 INTO
4344 VV_SECURITY_RULE_APP_ID, VV_SECURITY_RULE_CODE
4345 FROM BNE_SECURED_OBJECTS
4346 WHERE APPLICATION_ID = P_APPLICATION_ID
4347 AND OBJECT_CODE = P_INTEGRATOR_CODE
4348 AND OBJECT_TYPE = 'INTEGRATOR';
4349
4350 DELETE FROM BNE_SECURED_OBJECTS
4351 WHERE APPLICATION_ID = P_APPLICATION_ID
4352 AND OBJECT_CODE = P_INTEGRATOR_CODE
4353 AND OBJECT_TYPE = 'INTEGRATOR';
4354
4355 SELECT COUNT(*)
4356 INTO VV_DUMMY
4357 FROM BNE_SECURED_OBJECTS
4358 WHERE SECURITY_RULE_APP_ID = VV_SECURITY_RULE_APP_ID
4359 AND SECURITY_RULE_CODE = VV_SECURITY_RULE_CODE;
4360
4361 IF VV_DUMMY = 0
4362 THEN
4363 DELETE FROM BNE_SECURITY_RULES
4364 WHERE APPLICATION_ID = VV_SECURITY_RULE_APP_ID
4365 AND SECURITY_CODE = VV_SECURITY_RULE_CODE;
4366 END IF;
4367 EXCEPTION
4368 WHEN NO_DATA_FOUND THEN NULL;
4369 END;
4370
4371 -- Now delete the parameter lists if unreferenced.
4372 IF VV_IMPORT_PROG_LIST_KEYS.COUNT > 0
4373 THEN
4374 FOR I IN 1..VV_IMPORT_PROG_LIST_KEYS.LAST
4375 LOOP
4376 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_IMPORT_PROG_LIST_KEYS(I).APP_ID, VV_IMPORT_PROG_LIST_KEYS(I).CODE);
4377 END LOOP;
4378 END IF;
4379
4380 IF VV_UPLOAD_LIST_APP_ID IS NOT NULL
4381 AND VV_UPLOAD_LIST_CODE IS NOT NULL
4382 THEN
4383 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_UPLOAD_LIST_APP_ID,
4384 VV_UPLOAD_LIST_CODE);
4385 END IF;
4386 IF VV_UPLOAD_SERV_LIST_APP_ID IS NOT NULL
4387 AND VV_UPLOAD_SERV_LIST_CODE IS NOT NULL
4388 THEN
4389 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_UPLOAD_SERV_LIST_APP_ID,
4390 VV_UPLOAD_SERV_LIST_CODE);
4391 END IF;
4392
4393 IF VV_IMPORT_LIST_APP_ID IS NOT NULL
4394 AND VV_IMPORT_LIST_CODE IS NOT NULL
4395 THEN
4396 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_IMPORT_LIST_APP_ID,
4397 VV_IMPORT_LIST_CODE);
4398 END IF;
4399
4400 IF VV_CREATE_DOC_LIST_APP_ID IS NOT NULL
4401 AND VV_CREATE_DOC_LIST_CODE IS NOT NULL
4402 THEN
4403 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_CREATE_DOC_LIST_APP_ID,
4404 VV_CREATE_DOC_LIST_CODE);
4405 END IF;
4406
4407 IF VV_SESSION_LIST_APP_ID IS NOT NULL
4408 AND VV_SESSION_LIST_CODE IS NOT NULL
4409 THEN
4410 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_SESSION_LIST_APP_ID,
4411 VV_SESSION_LIST_CODE);
4412 END IF;
4413
4414 RETURN(VV_COUNT);
4415 END DELETE_INTEGRATOR;
4416
4417
4418 --------------------------------------------------------------------------------
4419 -- FUNCTION: DELETE_ALL_INTEGRATORS --
4420 -- --
4421 -- DESCRIPTION: Delete all integrators for the given application id. --
4422 -- This will delete each integrator for the application id --
4423 -- individually as per DELETE_INTEGRATOR(). --
4424 -- This will include all subsiduary structures: --
4425 -- - Integrator and attached Parameter Lists --
4426 -- - Interfaces as per DELETE_ALL_INTERFACES() --
4427 -- - Contents as per DELETE_ALL_CONTENTS() --
4428 -- - Mappings as per DELETE_ALL_MAPPINGS() --
4429 -- - Layouts as per DELETE_ALL_LAYOUTS() --
4430 -- - Duplicate Profiles as per DELETE_ALL_DUP_PROFILES() --
4431 -- The number of Integrators deleted is returned (0 or greater).--
4432 -- No commits are done. --
4433 -- --
4434 -- MODIFICATION HISTORY --
4435 -- Date Username Description --
4436 -- 20-JUL-2006 dagroves Created. --
4437 --------------------------------------------------------------------------------
4438 FUNCTION DELETE_ALL_INTEGRATORS
4439 (P_APPLICATION_ID IN NUMBER)
4440 RETURN NUMBER
4441 IS
4442 VV_COUNT NUMBER;
4443 BEGIN
4444 VV_COUNT := 0;
4445 FOR I IN (SELECT INTEGRATOR_CODE
4446 FROM BNE_INTEGRATORS_B
4447 WHERE APPLICATION_ID = P_APPLICATION_ID)
4448 LOOP
4449 VV_COUNT := VV_COUNT + DELETE_INTEGRATOR(P_APPLICATION_ID, I.INTEGRATOR_CODE);
4450 END LOOP;
4451 RETURN(VV_COUNT);
4452 END DELETE_ALL_INTEGRATORS;
4453
4454
4455 --------------------------------------------------------------------------------
4456 -- FUNCTION: DELETE_INTERFACE_COL --
4457 -- --
4458 -- DESCRIPTION: Delete the Interface Column. --
4459 -- This will include all subsiduary structures: --
4460 -- - Component as per DELETE_COMPONENT_IF_UNREF() --
4461 -- - Validation query as per DELETE_QUERY_IF_UNREF() --
4462 -- - Expanded SQL query as per DELETE_QUERY_IF_UNREF() --
4463 -- The number of Interface Columns deleted is returned (0 or 1).--
4464 -- No commits are done. --
4465 -- --
4466 -- MODIFICATION HISTORY --
4467 -- Date Username Description --
4468 -- 20-JUL-2006 dagroves Created. --
4469 --------------------------------------------------------------------------------
4470 FUNCTION DELETE_INTERFACE_COL
4471 (P_APPLICATION_ID IN NUMBER,
4472 P_INTERFACE_CODE IN VARCHAR2,
4473 P_SEQUENCE_NUM IN NUMBER)
4474 RETURN NUMBER
4475 IS
4476 VV_COUNT NUMBER;
4477 VV_DUMMY NUMBER;
4478 VV_COMPONENT_APP_ID NUMBER(15);
4479 VV_COMPONENT_CODE VARCHAR2(30);
4480 VV_QUERY_APP_ID NUMBER(15);
4481 VV_QUERY_CODE VARCHAR2(30);
4482 VV_EXP_QUERY_APP_ID NUMBER(15);
4483 VV_EXP_QUERY_CODE VARCHAR2(30);
4484 BEGIN
4485 SELECT COUNT(*)
4486 INTO VV_COUNT
4487 FROM BNE_INTERFACE_COLS_B
4488 WHERE APPLICATION_ID = P_APPLICATION_ID
4489 AND INTERFACE_CODE = P_INTERFACE_CODE
4490 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
4491
4492 IF VV_COUNT = 0
4493 THEN
4494 RETURN(0);
4495 END IF;
4496
4497 -- Store components and queries referenced.
4498 SELECT VAL_COMPONENT_APP_ID, VAL_COMPONENT_CODE,
4499 VAL_QUERY_APP_ID, VAL_QUERY_CODE,
4500 EXPANDED_SQL_QUERY_APP_ID, EXPANDED_SQL_QUERY_CODE
4501 INTO VV_COMPONENT_APP_ID, VV_COMPONENT_CODE,
4502 VV_QUERY_APP_ID, VV_QUERY_CODE,
4503 VV_EXP_QUERY_APP_ID, VV_EXP_QUERY_CODE
4504 FROM BNE_INTERFACE_COLS_B C
4505 WHERE APPLICATION_ID = P_APPLICATION_ID
4506 AND INTERFACE_CODE = P_INTERFACE_CODE
4507 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
4508
4509 DELETE FROM BNE_INTERFACE_COLS_B
4510 WHERE APPLICATION_ID = P_APPLICATION_ID
4511 AND INTERFACE_CODE = P_INTERFACE_CODE
4512 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
4513
4514 DELETE FROM BNE_INTERFACE_COLS_TL
4515 WHERE APPLICATION_ID = P_APPLICATION_ID
4516 AND INTERFACE_CODE = P_INTERFACE_CODE
4517 AND SEQUENCE_NUM = P_SEQUENCE_NUM;
4518
4519 -- Now delete the component if unreferenced.
4520 IF VV_COMPONENT_APP_ID IS NOT NULL AND VV_COMPONENT_CODE IS NOT NULL
4521 THEN
4522 VV_DUMMY := DELETE_COMPONENT_IF_UNREF(VV_COMPONENT_APP_ID, VV_COMPONENT_CODE);
4523 END IF;
4524
4525 -- Now delete the queries if unreferenced.
4526 IF VV_QUERY_APP_ID IS NOT NULL AND VV_QUERY_CODE IS NOT NULL
4527 THEN
4528 VV_DUMMY := DELETE_QUERY_IF_UNREF(VV_QUERY_APP_ID, VV_QUERY_CODE);
4529 END IF;
4530 IF VV_EXP_QUERY_APP_ID IS NOT NULL AND VV_EXP_QUERY_CODE IS NOT NULL
4531 THEN
4532 VV_DUMMY := DELETE_QUERY_IF_UNREF(VV_EXP_QUERY_APP_ID, VV_EXP_QUERY_CODE);
4533 END IF;
4534
4535 RETURN(VV_COUNT);
4536 END DELETE_INTERFACE_COL;
4537
4538
4539 --------------------------------------------------------------------------------
4540 -- FUNCTION: DELETE_INTERFACE --
4541 -- --
4542 -- DESCRIPTION: Delete the given interface. --
4543 -- This will include all subsiduary structures: --
4544 -- - Interface --
4545 -- - Interface Columns --
4546 -- - Interface Keys/Key columns --
4547 -- - Interface Duplicate information --
4548 -- - Queries as per DELETE_QUERY_IF_UNREF() --
4549 -- - Components as per DELETE_COMPONENT_IF_UNREF() --
4550 -- It will NOT delete layouts/components/mappings that reference--
4551 -- the interface, use DELETE_INTEGRATOR for consistent deletion.--
4552 -- of the entire entegrator structure. --
4553 -- The number of interfaces deleted is returned (0 or 1). --
4554 -- No commits are done. --
4555 -- --
4556 -- MODIFICATION HISTORY --
4557 -- Date Username Description --
4558 -- 20-JUL-2006 dagroves Created. --
4559 --------------------------------------------------------------------------------
4560 FUNCTION DELETE_INTERFACE
4561 (P_APPLICATION_ID IN NUMBER,
4562 P_INTERFACE_CODE IN VARCHAR2)
4563 RETURN NUMBER
4564 IS
4565 VV_COUNT NUMBER;
4566 VV_DUMMY NUMBER;
4567 VV_UPLOAD_LIST_APP_ID NUMBER(15);
4568 VV_UPLOAD_LIST_CODE VARCHAR2(30);
4569 BEGIN
4570 SELECT COUNT(*)
4571 INTO VV_COUNT
4572 FROM BNE_INTERFACES_B
4573 WHERE APPLICATION_ID = P_APPLICATION_ID
4574 AND INTERFACE_CODE = P_INTERFACE_CODE;
4575
4576 IF VV_COUNT = 0
4577 THEN
4578 RETURN(0);
4579 END IF;
4580
4581 -- Store interface level list FKs.
4582 SELECT
4583 UPLOAD_PARAM_LIST_APP_ID, UPLOAD_PARAM_LIST_CODE
4584 INTO
4585 VV_UPLOAD_LIST_APP_ID, VV_UPLOAD_LIST_CODE
4586 FROM BNE_INTERFACES_B
4587 WHERE APPLICATION_ID = P_APPLICATION_ID
4588 AND INTERFACE_CODE = P_INTERFACE_CODE;
4589
4590 DELETE FROM BNE_INTERFACES_B
4591 WHERE APPLICATION_ID = P_APPLICATION_ID
4592 AND INTERFACE_CODE = P_INTERFACE_CODE;
4593
4594 DELETE FROM BNE_INTERFACES_TL
4595 WHERE APPLICATION_ID = P_APPLICATION_ID
4596 AND INTERFACE_CODE = P_INTERFACE_CODE;
4597
4598 -- Duplicate key information ...
4599 DELETE FROM BNE_DUP_INTERFACE_COLS
4600 WHERE INTERFACE_APP_ID = P_APPLICATION_ID
4601 AND INTERFACE_CODE = P_INTERFACE_CODE;
4602
4603 DELETE FROM BNE_DUP_INTERFACE_PROFILES
4604 WHERE INTERFACE_APP_ID = P_APPLICATION_ID
4605 AND INTERFACE_CODE = P_INTERFACE_CODE;
4606
4607 DELETE FROM BNE_INTERFACE_KEY_COLS
4608 WHERE INTERFACE_APP_ID = P_APPLICATION_ID
4609 AND INTERFACE_CODE = P_INTERFACE_CODE;
4610
4611 DELETE FROM BNE_INTERFACE_KEYS
4612 WHERE INTERFACE_APP_ID = P_APPLICATION_ID
4613 AND INTERFACE_CODE = P_INTERFACE_CODE;
4614
4615 -- Interface Cols ...
4616 FOR I IN (SELECT SEQUENCE_NUM
4617 FROM BNE_INTERFACE_COLS_B
4618 WHERE APPLICATION_ID = P_APPLICATION_ID
4619 AND INTERFACE_CODE = P_INTERFACE_CODE)
4620 LOOP
4621 VV_DUMMY := DELETE_INTERFACE_COL(P_APPLICATION_ID, P_INTERFACE_CODE, I.SEQUENCE_NUM);
4622 END LOOP;
4623
4624 IF VV_UPLOAD_LIST_APP_ID IS NOT NULL
4625 AND VV_UPLOAD_LIST_CODE IS NOT NULL
4626 THEN
4627 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_UPLOAD_LIST_APP_ID,
4628 VV_UPLOAD_LIST_CODE);
4629 END IF;
4630
4631 RETURN(VV_COUNT);
4632 END DELETE_INTERFACE;
4633
4634
4635
4636 --------------------------------------------------------------------------------
4637 -- FUNCTION: DELETE_ALL_INTERFACES --
4638 -- --
4639 -- DESCRIPTION: Delete all interfaces for the given integrator. --
4640 -- This will delete each interface for the integrator --
4641 -- individually as per DELETE_INTERFACE(). --
4642 -- The number of interfaces deleted is returned (0 or greater). --
4643 -- No commits are done. --
4644 -- --
4645 -- MODIFICATION HISTORY --
4646 -- Date Username Description --
4647 -- 20-JUL-2006 dagroves Created. --
4648 --------------------------------------------------------------------------------
4649 FUNCTION DELETE_ALL_INTERFACES
4650 (P_APPLICATION_ID IN NUMBER,
4651 P_INTEGRATOR_CODE IN VARCHAR2)
4652 RETURN NUMBER
4653 IS
4654 VV_COUNT NUMBER;
4655 BEGIN
4656 VV_COUNT := 0;
4657 FOR I IN (SELECT APPLICATION_ID, INTERFACE_CODE
4658 FROM BNE_INTERFACES_B
4659 WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
4660 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
4661 LOOP
4662 VV_COUNT := VV_COUNT + DELETE_INTERFACE(I.APPLICATION_ID, I.INTERFACE_CODE);
4663 END LOOP;
4664 RETURN(VV_COUNT);
4665 END DELETE_ALL_INTERFACES;
4666
4667
4668 --------------------------------------------------------------------------------
4669 -- FUNCTION: DELETE_ALL_DUP_PROFILES --
4670 -- --
4671 -- DESCRIPTION: Delete all duplicate profiles for the given integrator. --
4672 -- This will delete each duplicate profile for the integrator --
4673 -- individually as per DELETE_DUP_PROFILE(). --
4674 -- The number of profiles deleted is returned (0 or greater). --
4675 -- No commits are done. --
4676 -- --
4677 -- MODIFICATION HISTORY --
4678 -- Date Username Description --
4679 -- 20-JUL-2006 dagroves Created. --
4680 --------------------------------------------------------------------------------
4681 FUNCTION DELETE_ALL_DUP_PROFILES
4682 (P_APPLICATION_ID IN NUMBER,
4683 P_INTEGRATOR_CODE IN VARCHAR2)
4684 RETURN NUMBER
4685 IS
4686 VV_COUNT NUMBER;
4687 BEGIN
4688 VV_COUNT := 0;
4689 FOR I IN (SELECT APPLICATION_ID, DUP_PROFILE_CODE
4690 FROM BNE_DUPLICATE_PROFILES_B
4691 WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
4692 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
4693 LOOP
4694 VV_COUNT := VV_COUNT + DELETE_DUP_PROFILE(I.APPLICATION_ID, I.DUP_PROFILE_CODE);
4695 END LOOP;
4696
4697 RETURN(VV_COUNT);
4698 END DELETE_ALL_DUP_PROFILES;
4699
4700
4701 --------------------------------------------------------------------------------
4702 -- FUNCTION: DELETE_DUP_PROFILE --
4703 -- --
4704 -- DESCRIPTION: Delete the given duplicate profile. --
4705 -- The number of duplicate profiles deleted is returned (0 or 1).--
4706 -- No commits are done. --
4707 -- --
4708 -- MODIFICATION HISTORY --
4709 -- Date Username Description --
4710 -- 20-JUL-2006 dagroves Created. --
4711 --------------------------------------------------------------------------------
4712 FUNCTION DELETE_DUP_PROFILE
4713 (P_APPLICATION_ID IN NUMBER,
4714 P_DUP_PROFILE_CODE IN VARCHAR2)
4715 RETURN NUMBER
4716 IS
4717 VV_COUNT NUMBER;
4718 BEGIN
4719 SELECT COUNT(*)
4720 INTO VV_COUNT
4721 FROM BNE_DUPLICATE_PROFILES_B
4722 WHERE APPLICATION_ID = P_APPLICATION_ID
4723 AND DUP_PROFILE_CODE = P_DUP_PROFILE_CODE;
4724
4725 IF VV_COUNT = 0
4726 THEN
4727 RETURN(0);
4728 END IF;
4729
4730 DELETE FROM BNE_DUPLICATE_PROFILES_B
4731 WHERE APPLICATION_ID = P_APPLICATION_ID
4732 AND DUP_PROFILE_CODE = P_DUP_PROFILE_CODE;
4733
4734 DELETE FROM BNE_DUPLICATE_PROFILES_TL
4735 WHERE APPLICATION_ID = P_APPLICATION_ID
4736 AND DUP_PROFILE_CODE = P_DUP_PROFILE_CODE;
4737
4738 RETURN(VV_COUNT);
4739 END DELETE_DUP_PROFILE;
4740
4741
4742
4743 --------------------------------------------------------------------------------
4744 -- FUNCTION: DELETE_CONTENT --
4745 -- --
4746 -- DESCRIPTION: Delete the given content. --
4747 -- This will include all subsiduary structures: --
4748 -- - Contents --
4749 -- - Content Columns --
4750 -- - Stored SQL definitions --
4751 -- - Text File definitions --
4752 -- - Queries as per DELETE_QUERY_IF_UNREF() --
4753 -- The number of content deleted is returned (0 or 1). --
4754 -- No commits are done. --
4755 -- --
4756 -- MODIFICATION HISTORY --
4757 -- Date Username Description --
4758 -- 20-JUL-2006 dagroves Created. --
4759 --------------------------------------------------------------------------------
4760 FUNCTION DELETE_CONTENT
4761 (P_APPLICATION_ID IN NUMBER,
4762 P_CONTENT_CODE IN VARCHAR2)
4763 RETURN NUMBER
4764 IS
4765 VV_COUNT NUMBER;
4766 VV_DUMMY NUMBER;
4767 VV_QUERY_KEYS BNEKEY_TAB;
4768 BEGIN
4769 SELECT COUNT(*)
4770 INTO VV_COUNT
4771 FROM BNE_CONTENTS_B
4772 WHERE APPLICATION_ID = P_APPLICATION_ID
4773 AND CONTENT_CODE = P_CONTENT_CODE;
4774
4775 IF VV_COUNT = 0
4776 THEN
4777 RETURN(0);
4778 END IF;
4779
4780 -- Reference all queries in the content.
4781 SELECT QUERY_APP_ID, QUERY_CODE
4782 BULK COLLECT
4783 INTO VV_QUERY_KEYS
4784 FROM BNE_STORED_SQL C
4785 WHERE APPLICATION_ID = P_APPLICATION_ID
4786 AND CONTENT_CODE = P_CONTENT_CODE
4787 AND QUERY_APP_ID IS NOT NULL
4788 AND QUERY_CODE IS NOT NULL;
4789
4790 DELETE FROM BNE_CONTENTS_B
4791 WHERE APPLICATION_ID = P_APPLICATION_ID
4792 AND CONTENT_CODE = P_CONTENT_CODE;
4793
4794 DELETE FROM BNE_CONTENTS_TL
4795 WHERE APPLICATION_ID = P_APPLICATION_ID
4796 AND CONTENT_CODE = P_CONTENT_CODE;
4797
4798 DELETE FROM BNE_STORED_SQL
4799 WHERE APPLICATION_ID = P_APPLICATION_ID
4800 AND CONTENT_CODE = P_CONTENT_CODE;
4801
4802 DELETE FROM BNE_CONTENT_COLS_B
4803 WHERE APPLICATION_ID = P_APPLICATION_ID
4804 AND CONTENT_CODE = P_CONTENT_CODE;
4805
4806 DELETE FROM BNE_CONTENT_COLS_TL
4807 WHERE APPLICATION_ID = P_APPLICATION_ID
4808 AND CONTENT_CODE = P_CONTENT_CODE;
4809
4810 DELETE FROM BNE_FILES
4811 WHERE APPLICATION_ID = P_APPLICATION_ID
4812 AND CONTENT_CODE = P_CONTENT_CODE;
4813
4814 -- Now delete the queries if unreferenced.
4815 IF VV_QUERY_KEYS.COUNT > 0
4816 THEN
4817 FOR I IN 1..VV_QUERY_KEYS.LAST
4818 LOOP
4819 VV_DUMMY := DELETE_QUERY(VV_QUERY_KEYS(I).APP_ID, VV_QUERY_KEYS(I).CODE);
4820 END LOOP;
4821 END IF;
4822
4823 RETURN(VV_COUNT);
4824 END DELETE_CONTENT;
4825
4826
4827 --------------------------------------------------------------------------------
4828 -- FUNCTION: DELETE_ALL_CONTENTS --
4829 -- --
4830 -- DESCRIPTION: Delete all contents for the given integrator. --
4831 -- This will delete each content for the integrator --
4832 -- individually as per DELETE_CONTENT(). --
4833 -- It will NOT delete any mappings that reference the content. --
4834 -- use DELETE_MAPPING or DELETE_INTEGRATOR for consistent --
4835 -- deletion. --
4836 -- The number of contents deleted is returned (0 or greater). --
4837 -- No commits are done. --
4838 -- --
4839 -- MODIFICATION HISTORY --
4840 -- Date Username Description --
4841 -- 20-JUL-2006 dagroves Created. --
4842 --------------------------------------------------------------------------------
4843 FUNCTION DELETE_ALL_CONTENTS
4844 (P_APPLICATION_ID IN NUMBER,
4845 P_INTEGRATOR_CODE IN VARCHAR2)
4846 RETURN NUMBER
4847 IS
4848 VV_COUNT NUMBER;
4849 BEGIN
4850 VV_COUNT := 0;
4851 FOR I IN (SELECT APPLICATION_ID, CONTENT_CODE
4852 FROM BNE_CONTENTS_B
4853 WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
4854 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
4855 LOOP
4856 VV_COUNT := VV_COUNT + DELETE_CONTENT(I.APPLICATION_ID, I.CONTENT_CODE);
4857 END LOOP;
4858 RETURN(VV_COUNT);
4859 END DELETE_ALL_CONTENTS;
4860
4861
4862
4863
4864 --------------------------------------------------------------------------------
4865 -- FUNCTION: DELETE_MAPPING --
4866 -- --
4867 -- DESCRIPTION: Delete the given mapping. --
4868 -- This will include all subsiduary structures: --
4869 -- - Mapping --
4870 -- - Mapping Lines --
4871 -- The number of mappings deleted is returned (0 or 1). --
4872 -- No commits are done. --
4873 -- --
4874 -- MODIFICATION HISTORY --
4875 -- Date Username Description --
4876 -- 20-JUL-2006 dagroves Created. --
4877 --------------------------------------------------------------------------------
4878 FUNCTION DELETE_MAPPING
4879 (P_APPLICATION_ID IN NUMBER,
4880 P_MAPPING_CODE IN VARCHAR2)
4881 RETURN NUMBER
4882 IS
4883 VV_COUNT NUMBER;
4884 BEGIN
4885 SELECT COUNT(*)
4886 INTO VV_COUNT
4887 FROM BNE_MAPPINGS_B
4888 WHERE APPLICATION_ID = P_APPLICATION_ID
4889 AND MAPPING_CODE = P_MAPPING_CODE;
4890
4891 IF VV_COUNT = 0
4892 THEN
4893 RETURN(0);
4894 END IF;
4895
4896 DELETE FROM BNE_MAPPINGS_B
4897 WHERE APPLICATION_ID = P_APPLICATION_ID
4898 AND MAPPING_CODE = P_MAPPING_CODE;
4899
4900 DELETE FROM BNE_MAPPINGS_TL
4901 WHERE APPLICATION_ID = P_APPLICATION_ID
4902 AND MAPPING_CODE = P_MAPPING_CODE;
4903
4904 DELETE FROM BNE_MAPPING_LINES
4905 WHERE APPLICATION_ID = P_APPLICATION_ID
4906 AND MAPPING_CODE = P_MAPPING_CODE;
4907
4908 RETURN(VV_COUNT);
4909 END DELETE_MAPPING;
4910
4911
4912 --------------------------------------------------------------------------------
4913 -- FUNCTION: DELETE_ALL_MAPPINGS --
4914 -- --
4915 -- DESCRIPTION: Delete all mappings for the given integrator. --
4916 -- This will delete each mapping for the integrator --
4917 -- individually as per DELETE_MAPPING(). --
4918 -- The number of mappings deleted is returned (0 or greater). --
4919 -- No commits are done. --
4920 -- --
4921 -- MODIFICATION HISTORY --
4922 -- Date Username Description --
4923 -- 20-JUL-2006 dagroves Created. --
4924 --------------------------------------------------------------------------------
4925 FUNCTION DELETE_ALL_MAPPINGS
4926 (P_APPLICATION_ID IN NUMBER,
4927 P_INTEGRATOR_CODE IN VARCHAR2)
4928 RETURN NUMBER
4929 IS
4930 VV_COUNT NUMBER;
4931 BEGIN
4932 VV_COUNT := 0;
4933 FOR I IN (SELECT APPLICATION_ID, MAPPING_CODE
4934 FROM BNE_MAPPINGS_B
4935 WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
4936 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
4937 LOOP
4938 VV_COUNT := VV_COUNT + DELETE_MAPPING(I.APPLICATION_ID, I.MAPPING_CODE);
4939 END LOOP;
4940 RETURN(VV_COUNT);
4941 END DELETE_ALL_MAPPINGS;
4942
4943
4944
4945
4946 --------------------------------------------------------------------------------
4947 -- FUNCTION: DELETE_ALL_LAYOUTS --
4948 -- --
4949 -- DESCRIPTION: Delete all layouts for the given integrator. --
4950 -- This will delete each layouts for the integrator --
4951 -- individually as per DELETE_LAYOUT(). --
4952 -- The number of layouts deleted is returned (0 or greater). --
4953 -- No commits are done. --
4954 -- --
4955 -- MODIFICATION HISTORY --
4956 -- Date Username Description --
4957 -- 20-JUL-2006 dagroves Created. --
4958 --------------------------------------------------------------------------------
4959 FUNCTION DELETE_ALL_LAYOUTS
4960 (P_APPLICATION_ID IN NUMBER,
4961 P_INTEGRATOR_CODE IN VARCHAR2)
4962 RETURN NUMBER
4963 IS
4964 VV_COUNT NUMBER;
4965 BEGIN
4966 VV_COUNT := 0;
4967 FOR I IN (SELECT APPLICATION_ID, LAYOUT_CODE
4968 FROM BNE_LAYOUTS_B
4969 WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
4970 AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
4971 LOOP
4972 VV_COUNT := VV_COUNT + DELETE_LAYOUT(I.APPLICATION_ID, I.LAYOUT_CODE);
4973 END LOOP;
4974 RETURN(VV_COUNT);
4975 END DELETE_ALL_LAYOUTS;
4976
4977
4978
4979
4980 --------------------------------------------------------------------------------
4981 -- FUNCTION: DELETE_LAYOUT --
4982 -- --
4983 -- DESCRIPTION: Delete the given layout. --
4984 -- This will include all subsiduary structures: --
4985 -- - Layout --
4986 -- - Layout Blocks --
4987 -- - Layout Columns --
4988 -- - Layout LOBS --
4989 -- - Graphs/Graph Columns referencing the layout --
4990 -- The number of layouts deleted is returned (0 or 1). --
4991 -- No commits are done. --
4992 -- --
4993 -- MODIFICATION HISTORY --
4994 -- Date Username Description --
4995 -- 20-JUL-2006 dagroves Created. --
4996 --------------------------------------------------------------------------------
4997 FUNCTION DELETE_LAYOUT
4998 (P_APPLICATION_ID IN NUMBER,
4999 P_LAYOUT_CODE IN VARCHAR2)
5000 RETURN NUMBER
5001 IS
5002 VV_COUNT NUMBER;
5003 BEGIN
5004 SELECT COUNT(*)
5005 INTO VV_COUNT
5006 FROM BNE_LAYOUTS_B
5007 WHERE APPLICATION_ID = P_APPLICATION_ID
5008 AND LAYOUT_CODE = P_LAYOUT_CODE;
5009
5010 IF VV_COUNT = 0
5011 THEN
5012 RETURN(0);
5013 END IF;
5014
5015 DELETE FROM BNE_LAYOUTS_B
5016 WHERE APPLICATION_ID = P_APPLICATION_ID
5017 AND LAYOUT_CODE = P_LAYOUT_CODE;
5018
5019 DELETE FROM BNE_LAYOUTS_TL
5020 WHERE APPLICATION_ID = P_APPLICATION_ID
5021 AND LAYOUT_CODE = P_LAYOUT_CODE;
5022
5023 DELETE FROM BNE_LAYOUT_BLOCKS_B
5024 WHERE APPLICATION_ID = P_APPLICATION_ID
5025 AND LAYOUT_CODE = P_LAYOUT_CODE;
5026
5027 DELETE FROM BNE_LAYOUT_BLOCKS_TL
5028 WHERE APPLICATION_ID = P_APPLICATION_ID
5029 AND LAYOUT_CODE = P_LAYOUT_CODE;
5030
5031 DELETE FROM BNE_LAYOUT_COLS
5032 WHERE APPLICATION_ID = P_APPLICATION_ID
5033 AND LAYOUT_CODE = P_LAYOUT_CODE;
5034
5035 DELETE FROM BNE_LAYOUT_LOBS
5036 WHERE APPLICATION_ID = P_APPLICATION_ID
5037 AND LAYOUT_CODE = P_LAYOUT_CODE;
5038
5039 -- Graphs referenced from this layout only.
5040 FOR I IN (SELECT APPLICATION_ID, INTEGRATOR_CODE, SEQUENCE_NUM
5041 FROM BNE_GRAPHS_B
5042 WHERE LAYOUT_APP_ID = P_APPLICATION_ID
5043 AND LAYOUT_CODE = P_LAYOUT_CODE)
5044 LOOP
5045 DELETE FROM BNE_GRAPHS_B
5046 WHERE APPLICATION_ID = I.APPLICATION_ID
5047 AND INTEGRATOR_CODE = I.INTEGRATOR_CODE
5048 AND SEQUENCE_NUM = I.SEQUENCE_NUM;
5049
5050 DELETE FROM BNE_GRAPHS_TL
5051 WHERE APPLICATION_ID = I.APPLICATION_ID
5052 AND INTEGRATOR_CODE = I.INTEGRATOR_CODE
5053 AND SEQUENCE_NUM = I.SEQUENCE_NUM;
5054
5055 DELETE FROM BNE_GRAPH_COLUMNS
5056 WHERE APPLICATION_ID = I.APPLICATION_ID
5057 AND INTEGRATOR_CODE = I.INTEGRATOR_CODE
5058 AND GRAPH_SEQ_NUM = I.SEQUENCE_NUM;
5059 END LOOP;
5060
5061 RETURN(VV_COUNT);
5062 END DELETE_LAYOUT;
5063
5064
5065
5066 --------------------------------------------------------------------------------
5067 -- FUNCTION: DELETE_COMPONENT --
5068 -- --
5069 -- DESCRIPTION: Delete the given component. --
5070 -- This will include all subsiduary structures: --
5071 -- - Component --
5072 -- - Parameter List as per DELETE_PARAM_LIST_IF_UNREF() --
5073 -- The number of components deleted is returned (0 or 1). --
5074 -- No commits are done. --
5075 -- --
5076 -- MODIFICATION HISTORY --
5077 -- Date Username Description --
5078 -- 20-JUL-2006 dagroves Created. --
5079 --------------------------------------------------------------------------------
5080 FUNCTION DELETE_COMPONENT
5081 (P_APPLICATION_ID IN NUMBER,
5082 P_COMPONENT_CODE IN VARCHAR2)
5083 RETURN NUMBER
5084 IS
5085 VV_DUMMY NUMBER;
5086 VV_COUNT NUMBER;
5087 VV_LIST_APP_ID NUMBER(15);
5088 VV_LIST_CODE VARCHAR2(30);
5089 BEGIN
5090 SELECT COUNT(*)
5091 INTO VV_COUNT
5092 FROM BNE_COMPONENTS_B
5093 WHERE APPLICATION_ID = P_APPLICATION_ID
5094 AND COMPONENT_CODE = P_COMPONENT_CODE;
5095
5096 IF VV_COUNT = 0
5097 THEN
5098 RETURN(0);
5099 END IF;
5100
5101 -- Reference the parameter list in the component.
5102 BEGIN
5103 SELECT PARAM_LIST_APP_ID, PARAM_LIST_CODE
5104 INTO VV_LIST_APP_ID, VV_LIST_CODE
5105 FROM BNE_COMPONENTS_B I
5106 WHERE APPLICATION_ID = P_APPLICATION_ID
5107 AND COMPONENT_CODE = P_COMPONENT_CODE;
5108 EXCEPTION
5109 WHEN NO_DATA_FOUND THEN
5110 VV_LIST_APP_ID := NULL;
5111 VV_LIST_CODE := NULL;
5112 END;
5113
5114
5115 DELETE FROM BNE_COMPONENTS_B
5116 WHERE APPLICATION_ID = P_APPLICATION_ID
5117 AND COMPONENT_CODE = P_COMPONENT_CODE;
5118
5119 DELETE FROM BNE_COMPONENTS_TL
5120 WHERE APPLICATION_ID = P_APPLICATION_ID
5121 AND COMPONENT_CODE = P_COMPONENT_CODE;
5122
5123 IF VV_LIST_APP_ID IS NOT NULL AND VV_LIST_CODE IS NOT NULL
5124 THEN
5125 VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_LIST_APP_ID, VV_LIST_CODE);
5126 END IF;
5127 RETURN(VV_COUNT);
5128 END DELETE_COMPONENT;
5129
5130
5131
5132 --------------------------------------------------------------------------------
5133 -- FUNCTION: DELETE_PARAM_LIST --
5134 -- --
5135 -- DESCRIPTION: Delete the given Parameter List. --
5136 -- This will include all subsiduary structures: --
5137 -- - List --
5138 -- - List Items --
5139 -- - List Item Groups/Group Items --
5140 -- - Definitions if otherwise unreferenced --
5141 -- - Queries on definitions as per DELETE_QUERY_IF_UNREF() --
5142 -- - Attributes for list/items/groups/definitions if otherwise --
5143 -- unreferenced. --
5144 -- The number of lists deleted is returned (0 or 1). --
5145 -- No commits are done. --
5146 -- --
5147 -- MODIFICATION HISTORY --
5148 -- Date Username Description --
5149 -- 20-JUL-2006 dagroves Created. --
5150 --------------------------------------------------------------------------------
5151 FUNCTION DELETE_PARAM_LIST
5152 (P_APPLICATION_ID IN NUMBER,
5153 P_PARAM_LIST_CODE IN VARCHAR2)
5154 RETURN NUMBER
5155 IS
5156 VV_COUNT NUMBER;
5157 VV_DUMMY NUMBER;
5158 VV_ATTRIBUTE_KEYS BNEKEY_TAB;
5159 VV_DEFN_KEYS BNEKEY_TAB;
5160 VV_QUERY_APP_ID NUMBER(15);
5161 VV_QUERY_CODE VARCHAR2(30);
5162 BEGIN
5163 SELECT COUNT(*)
5164 INTO VV_COUNT
5165 FROM BNE_PARAM_LISTS_B
5166 WHERE APPLICATION_ID = P_APPLICATION_ID
5167 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5168
5169 IF VV_COUNT = 0
5170 THEN
5171 RETURN(0);
5172 END IF;
5173
5174 -- Reference all attributes in the list.
5175 BEGIN
5176 SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
5177 BULK COLLECT
5178 INTO VV_ATTRIBUTE_KEYS
5179 FROM (SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
5180 FROM BNE_PARAM_LISTS_B
5181 WHERE APPLICATION_ID = P_APPLICATION_ID
5182 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
5183 AND ATTRIBUTE_APP_ID IS NOT NULL
5184 AND ATTRIBUTE_CODE IS NOT NULL
5185 UNION
5186 SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
5187 FROM BNE_PARAM_GROUPS_B
5188 WHERE APPLICATION_ID = P_APPLICATION_ID
5189 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
5190 AND ATTRIBUTE_APP_ID IS NOT NULL
5191 AND ATTRIBUTE_CODE IS NOT NULL
5192 UNION
5193 SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
5194 FROM BNE_PARAM_LIST_ITEMS
5195 WHERE APPLICATION_ID = P_APPLICATION_ID
5196 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
5197 AND ATTRIBUTE_APP_ID IS NOT NULL
5198 AND ATTRIBUTE_CODE IS NOT NULL
5199 UNION
5200 SELECT D.ATTRIBUTE_APP_ID, D.ATTRIBUTE_CODE
5201 FROM BNE_PARAM_LIST_ITEMS I, BNE_PARAM_DEFNS_B D
5202 WHERE I.APPLICATION_ID = P_APPLICATION_ID
5203 AND I.PARAM_LIST_CODE = P_PARAM_LIST_CODE
5204 AND I.PARAM_DEFN_APP_ID = D.APPLICATION_ID
5205 AND I.PARAM_DEFN_CODE = D.PARAM_DEFN_CODE
5206 AND D.ATTRIBUTE_APP_ID IS NOT NULL
5207 AND D.ATTRIBUTE_CODE IS NOT NULL
5208 );
5209 EXCEPTION
5210 WHEN NO_DATA_FOUND THEN NULL;
5211 END;
5212
5213 -- Reference all definitions in the list.
5214 BEGIN
5215 SELECT PARAM_DEFN_APP_ID, PARAM_DEFN_CODE
5216 BULK COLLECT
5217 INTO VV_DEFN_KEYS
5218 FROM BNE_PARAM_LIST_ITEMS I
5219 WHERE APPLICATION_ID = P_APPLICATION_ID
5220 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
5221 AND PARAM_DEFN_APP_ID IS NOT NULL
5222 AND PARAM_DEFN_CODE IS NOT NULL;
5223 EXCEPTION
5224 WHEN NO_DATA_FOUND THEN NULL;
5225 END;
5226
5227
5228 DELETE FROM BNE_PARAM_LISTS_B
5229 WHERE APPLICATION_ID = P_APPLICATION_ID
5230 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5231
5232 DELETE FROM BNE_PARAM_LISTS_TL
5233 WHERE APPLICATION_ID = P_APPLICATION_ID
5234 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5235
5236 DELETE FROM BNE_PARAM_LIST_ITEMS
5237 WHERE APPLICATION_ID = P_APPLICATION_ID
5238 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5239
5240 DELETE FROM BNE_PARAM_GROUPS_B
5241 WHERE APPLICATION_ID = P_APPLICATION_ID
5242 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5243
5244 DELETE FROM BNE_PARAM_GROUPS_TL
5245 WHERE APPLICATION_ID = P_APPLICATION_ID
5246 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5247
5248 DELETE FROM BNE_PARAM_GROUP_ITEMS
5249 WHERE APPLICATION_ID = P_APPLICATION_ID
5250 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5251
5252
5253 -- Now delete the definitions if unreferenced.
5254 IF VV_DEFN_KEYS.COUNT > 0
5255 THEN
5256 FOR I IN 1..VV_DEFN_KEYS.LAST
5257 LOOP
5258 SELECT COUNT(*)
5259 INTO VV_DUMMY
5260 FROM BNE_PARAM_LIST_ITEMS
5261 WHERE PARAM_DEFN_APP_ID = VV_DEFN_KEYS(I).APP_ID
5262 AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
5263
5264 IF VV_DUMMY = 0
5265 THEN
5266 -- Determine any referenced BNE Queries which are encoded in
5267 -- the val_type/val_value columns.
5268 BEGIN
5269 SELECT
5270 BNE_LCT_TOOLS_PKG.GET_APP_ID(VAL_VALUE),
5271 SUBSTRB(BNE_LCT_TOOLS_PKG.GET_CODE(VAL_VALUE),1,30)
5272 INTO VV_QUERY_APP_ID, VV_QUERY_CODE
5273 FROM BNE_PARAM_DEFNS_B
5274 WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
5275 AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE
5276 AND VAL_TYPE = 4
5277 AND VAL_VALUE IS NOT NULL;
5278 EXCEPTION
5279 WHEN NO_DATA_FOUND THEN
5280 VV_QUERY_APP_ID := NULL;
5281 VV_QUERY_CODE := NULL;
5282 END;
5283
5284 DELETE FROM BNE_PARAM_DEFNS_B
5285 WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
5286 AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
5287
5288 DELETE FROM BNE_PARAM_DEFNS_TL
5289 WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
5290 AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
5291
5292 DELETE FROM BNE_PARAM_OVERRIDES
5293 WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
5294 AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
5295
5296 IF VV_QUERY_APP_ID IS NOT NULL AND VV_QUERY_CODE IS NOT NULL
5297 THEN
5298 VV_DUMMY := DELETE_QUERY_IF_UNREF(VV_QUERY_APP_ID, VV_QUERY_CODE);
5299 END IF;
5300 END IF;
5301
5302 END LOOP;
5303 END IF;
5304
5305 -- Now delete the attributes if unreferenced.
5306 IF VV_ATTRIBUTE_KEYS.COUNT > 0
5307 THEN
5308 FOR I IN 1..VV_ATTRIBUTE_KEYS.LAST
5309 LOOP
5310 DELETE FROM BNE_ATTRIBUTES A
5311 WHERE APPLICATION_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
5312 AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE
5313 AND NOT EXISTS (SELECT 1 FROM BNE_PARAM_LISTS_B
5314 WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
5315 AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
5316 AND NOT EXISTS (SELECT 1
5317 FROM BNE_PARAM_GROUPS_B
5318 WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
5319 AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
5320 AND NOT EXISTS (SELECT 1
5321 FROM BNE_PARAM_LIST_ITEMS
5322 WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
5323 AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
5324 AND NOT EXISTS (SELECT 1
5325 FROM BNE_PARAM_DEFNS_B
5326 WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
5327 AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
5328 ;
5329 END LOOP;
5330 END IF;
5331
5332
5333 RETURN(VV_COUNT);
5334 END DELETE_PARAM_LIST;
5335
5336
5337 --------------------------------------------------------------------------------
5338 -- FUNCTION: DELETE_QUERY --
5339 -- --
5340 -- DESCRIPTION: Delete the given query. --
5341 -- This will include all subsiduary structures: --
5342 -- - Query --
5343 -- - Simple Query --
5344 -- - Raw Query Keys/Key columns --
5345 -- The number of queries deleted is returned (0 or 1). --
5346 -- No commits are done. --
5347 -- --
5348 -- MODIFICATION HISTORY --
5349 -- Date Username Description --
5350 -- 20-JUL-2006 dagroves Created. --
5351 --------------------------------------------------------------------------------
5352 FUNCTION DELETE_QUERY
5353 (P_APPLICATION_ID IN NUMBER,
5354 P_QUERY_CODE IN VARCHAR2)
5355 RETURN NUMBER
5356 IS
5357 VV_COUNT NUMBER;
5358 BEGIN
5359 SELECT COUNT(*)
5360 INTO VV_COUNT
5361 FROM BNE_QUERIES_B
5362 WHERE APPLICATION_ID = P_APPLICATION_ID
5363 AND QUERY_CODE = P_QUERY_CODE;
5364
5365 IF VV_COUNT = 0
5366 THEN
5367 RETURN(0);
5368 END IF;
5369
5370 DELETE FROM BNE_QUERIES_B
5371 WHERE APPLICATION_ID = P_APPLICATION_ID
5372 AND QUERY_CODE = P_QUERY_CODE;
5373
5374 DELETE FROM BNE_QUERIES_TL
5375 WHERE APPLICATION_ID = P_APPLICATION_ID
5376 AND QUERY_CODE = P_QUERY_CODE;
5377
5378 DELETE FROM BNE_SIMPLE_QUERY
5379 WHERE APPLICATION_ID = P_APPLICATION_ID
5380 AND QUERY_CODE = P_QUERY_CODE;
5381
5382 DELETE FROM BNE_RAW_QUERY
5383 WHERE APPLICATION_ID = P_APPLICATION_ID
5384 AND QUERY_CODE = P_QUERY_CODE;
5385
5386 RETURN(VV_COUNT);
5387 END DELETE_QUERY;
5388
5389
5390 --------------------------------------------------------------------------------
5391 -- FUNCTION: DELETE_COMPONENT_IF_UNREF --
5392 -- --
5393 -- DESCRIPTION: Delete the given Component only if it is unreferenced --
5394 -- throughout the entire schema. All FKs are checked. --
5395 -- Delete is done as per DELETE_COMPONENT() if unreferenced. --
5396 -- The number of components deleted is returned (0 or 1). --
5397 -- No commits are done. --
5398 -- --
5399 -- MODIFICATION HISTORY --
5400 -- Date Username Description --
5401 -- 20-JUL-2006 dagroves Created. --
5402 --------------------------------------------------------------------------------
5403 FUNCTION DELETE_COMPONENT_IF_UNREF
5404 (P_APPLICATION_ID IN NUMBER,
5405 P_COMPONENT_CODE IN VARCHAR2)
5406 RETURN NUMBER
5407 IS
5408 VV_COUNT NUMBER;
5409 VV_DUMMY NUMBER;
5410 BEGIN
5411 VV_COUNT := 0;
5412
5413 SELECT COUNT(*)
5414 INTO VV_DUMMY
5415 FROM BNE_INTERFACE_COLS_B
5416 WHERE VAL_COMPONENT_APP_ID = P_APPLICATION_ID
5417 AND VAL_COMPONENT_CODE = P_COMPONENT_CODE;
5418
5419 IF VV_DUMMY = 0
5420 THEN
5421 VV_COUNT := DELETE_COMPONENT(P_APPLICATION_ID, P_COMPONENT_CODE);
5422 END IF;
5423
5424 RETURN(VV_COUNT);
5425 END DELETE_COMPONENT_IF_UNREF;
5426
5427
5428 --------------------------------------------------------------------------------
5429 -- FUNCTION: DELETE_QUERY_IF_UNREF --
5430 -- --
5431 -- DESCRIPTION: Delete the given Query only if it is unreferenced --
5432 -- throughout the entire schema. All FKs are checked. --
5433 -- Delete is done as per DELETE_QUERY() if unreferenced. --
5434 -- The number of Queries deleted is returned (0 or 1). --
5435 -- No commits are done. --
5436 -- --
5437 -- MODIFICATION HISTORY --
5438 -- Date Username Description --
5439 -- 20-JUL-2006 dagroves Created. --
5440 --------------------------------------------------------------------------------
5441 FUNCTION DELETE_QUERY_IF_UNREF
5442 (P_APPLICATION_ID IN NUMBER,
5443 P_QUERY_CODE IN VARCHAR2)
5444 RETURN NUMBER
5445 IS
5446 VV_COUNT NUMBER;
5447 VV_DUMMY NUMBER;
5448 BEGIN
5449 VV_COUNT := 0;
5450
5451 SELECT COUNT(*)
5452 INTO VV_DUMMY
5453 FROM BNE_INTERFACE_COLS_B
5454 WHERE VAL_QUERY_APP_ID = P_APPLICATION_ID
5455 AND VAL_QUERY_CODE = P_QUERY_CODE;
5456
5457 IF VV_DUMMY > 0
5458 THEN
5459 RETURN(0);
5460 END IF;
5461
5462 SELECT COUNT(*)
5463 INTO VV_DUMMY
5464 FROM BNE_INTERFACE_COLS_B
5465 WHERE EXPANDED_SQL_QUERY_APP_ID = P_APPLICATION_ID
5466 AND EXPANDED_SQL_QUERY_CODE = P_QUERY_CODE;
5467
5468 IF VV_DUMMY > 0
5469 THEN
5470 RETURN(0);
5471 END IF;
5472
5473 SELECT COUNT(*)
5474 INTO VV_DUMMY
5475 FROM BNE_STORED_SQL
5476 WHERE QUERY_APP_ID = P_APPLICATION_ID
5477 AND QUERY_CODE = P_QUERY_CODE;
5478
5479 IF VV_DUMMY > 0
5480 THEN
5481 RETURN(0);
5482 END IF;
5483
5484 SELECT COUNT(*)
5485 INTO VV_DUMMY
5486 FROM BNE_PARAM_DEFNS_B
5487 WHERE VAL_TYPE = 4
5488 AND (VAL_VALUE = TO_CHAR(P_APPLICATION_ID)||P_QUERY_CODE OR
5489 VAL_VALUE = BNE_LCT_TOOLS_PKG.APP_ID_TO_ASN(P_APPLICATION_ID)||P_QUERY_CODE);
5490
5491 IF VV_DUMMY > 0
5492 THEN
5493 RETURN(0);
5494 END IF;
5495
5496 VV_COUNT := DELETE_QUERY(P_APPLICATION_ID, P_QUERY_CODE);
5497 RETURN(VV_COUNT);
5498 END DELETE_QUERY_IF_UNREF;
5499
5500
5501 --------------------------------------------------------------------------------
5502 -- FUNCTION: DELETE_PARAM_LIST_IF_UNREF --
5503 -- --
5504 -- DESCRIPTION: Delete the given Parameter List only if it is unreferenced --
5505 -- throughout the entire schema. All FKs are checked. --
5506 -- Delete is done as per DELETE_PARAM_LIST() if unreferenced. --
5507 -- The number of lists deleted is returned (0 or 1). --
5508 -- No commits are done. --
5509 -- --
5510 -- MODIFICATION HISTORY --
5511 -- Date Username Description --
5512 -- 20-JUL-2006 dagroves Created. --
5513 --------------------------------------------------------------------------------
5514 FUNCTION DELETE_PARAM_LIST_IF_UNREF
5515 (P_APPLICATION_ID IN NUMBER,
5516 P_PARAM_LIST_CODE IN VARCHAR2)
5517 RETURN NUMBER
5518 IS
5519 VV_COUNT NUMBER;
5520 VV_DUMMY NUMBER;
5521 BEGIN
5522 VV_COUNT := 0;
5523
5524 SELECT COUNT(*)
5525 INTO VV_DUMMY
5526 FROM BNE_INTEGRATORS_B
5527 WHERE UPLOAD_PARAM_LIST_APP_ID = P_APPLICATION_ID
5528 AND UPLOAD_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5529
5530 IF VV_DUMMY > 0
5531 THEN
5532 RETURN(0);
5533 END IF;
5534
5535 --
5536 SELECT COUNT(*)
5537 INTO VV_DUMMY
5538 FROM BNE_INTEGRATORS_B
5539 WHERE UPLOAD_SERV_PARAM_LIST_APP_ID = P_APPLICATION_ID
5540 AND UPLOAD_SERV_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5541
5542 IF VV_DUMMY > 0
5543 THEN
5544 RETURN(0);
5545 END IF;
5546
5547 --
5548 SELECT COUNT(*)
5549 INTO VV_DUMMY
5550 FROM BNE_INTEGRATORS_B
5551 WHERE IMPORT_PARAM_LIST_APP_ID = P_APPLICATION_ID
5552 AND IMPORT_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5553
5554 IF VV_DUMMY > 0
5555 THEN
5556 RETURN(0);
5557 END IF;
5558
5559 --
5560 SELECT COUNT(*)
5561 INTO VV_DUMMY
5562 FROM BNE_INTEGRATORS_B
5563 WHERE CREATE_DOC_LIST_APP_ID = P_APPLICATION_ID
5564 AND CREATE_DOC_LIST_CODE = P_PARAM_LIST_CODE;
5565
5566 IF VV_DUMMY > 0
5567 THEN
5568 RETURN(0);
5569 END IF;
5570
5571 --
5572 SELECT COUNT(*)
5573 INTO VV_DUMMY
5574 FROM BNE_INTEGRATORS_B
5575 WHERE SESSION_PARAM_LIST_APP_ID = P_APPLICATION_ID
5576 AND SESSION_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5577
5578 IF VV_DUMMY > 0
5579 THEN
5580 RETURN(0);
5581 END IF;
5582
5583 --
5584 SELECT COUNT(*)
5585 INTO VV_DUMMY
5586 FROM BNE_INTERFACES_B
5587 WHERE UPLOAD_PARAM_LIST_APP_ID = P_APPLICATION_ID
5588 AND UPLOAD_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5589
5590 IF VV_DUMMY > 0
5591 THEN
5592 RETURN(0);
5593 END IF;
5594
5595 --
5596 SELECT COUNT(*)
5597 INTO VV_DUMMY
5598 FROM BNE_COMPONENTS_B
5599 WHERE PARAM_LIST_APP_ID = P_APPLICATION_ID
5600 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5601
5602 IF VV_DUMMY > 0
5603 THEN
5604 RETURN(0);
5605 END IF;
5606
5607 SELECT COUNT(*)
5608 INTO VV_DUMMY
5609 FROM BNE_IMPORT_PROGRAMS
5610 WHERE IMPORT_PARAM_LIST_APP_ID = P_APPLICATION_ID
5611 AND IMPORT_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5612
5613 IF VV_DUMMY > 0
5614 THEN
5615 RETURN(0);
5616 END IF;
5617
5618 --
5619 SELECT COUNT(*)
5620 INTO VV_DUMMY
5621 FROM BNE_LAYOUTS_B
5622 WHERE CREATE_DOC_LIST_APP_ID = P_APPLICATION_ID
5623 AND CREATE_DOC_LIST_CODE = P_PARAM_LIST_CODE;
5624
5625 IF VV_DUMMY > 0
5626 THEN
5627 RETURN(0);
5628 END IF;
5629
5630 --
5631 SELECT COUNT(*)
5632 INTO VV_DUMMY
5633 FROM BNE_CONTENTS_B
5634 WHERE PARAM_LIST_APP_ID = P_APPLICATION_ID
5635 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5636
5637 IF VV_DUMMY > 0
5638 THEN
5639 RETURN(0);
5640 END IF;
5641
5642 --
5643 SELECT COUNT(*)
5644 INTO VV_DUMMY
5645 FROM BNE_VIEWERS_B
5646 WHERE PARAM_LIST_APP_ID = P_APPLICATION_ID
5647 AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
5648
5649 IF VV_DUMMY > 0
5650 THEN
5651 RETURN(0);
5652 END IF;
5653
5654 --
5655 SELECT COUNT(*)
5656 INTO VV_DUMMY
5657 FROM BNE_VIEWERS_B
5658 WHERE CREATE_DOC_LIST_APP_ID = P_APPLICATION_ID
5659 AND CREATE_DOC_LIST_CODE = P_PARAM_LIST_CODE;
5660
5661 IF VV_DUMMY > 0
5662 THEN
5663 RETURN(0);
5664 END IF;
5665
5666
5667 VV_COUNT := DELETE_PARAM_LIST(P_APPLICATION_ID, P_PARAM_LIST_CODE);
5668 RETURN(VV_COUNT);
5669 END DELETE_PARAM_LIST_IF_UNREF;
5670
5671 --------------------------------------------------------------------------------
5672 -- PROCEDURE: UPDATE_INTERFACE_COLUMN_TEXT --
5673 -- --
5674 -- DESCRIPTION: Procedure updates the user text in the --
5675 -- BNE_INTERFACE_COLS_TL table. --
5676 -- --
5677 -- PARAMETERS: --
5678 -- --
5679 -- MODIFICATION HISTORY --
5680 -- Date Username Description --
5681 -- 17-APR-2007 JRICHARD CREATED --
5682 --------------------------------------------------------------------------------
5683 PROCEDURE UPDATE_INTERFACE_COLUMN_TEXT
5684 (P_APPLICATION_ID IN NUMBER, P_INTERFACE_CODE IN VARCHAR2,
5685 P_SEQUENCE_NUM IN NUMBER, P_LANGUAGE IN VARCHAR2,
5686 P_SOURCE_LANG IN VARCHAR2, P_PROMPT_LEFT IN VARCHAR2,
5687 P_PROMPT_ABOVE IN VARCHAR2, P_USER_HINT IN VARCHAR2,
5688 P_USER_HELP_TEXT IN VARCHAR2, P_USER_ID IN NUMBER)
5689 IS
5690 BEGIN
5691
5692 -- Update the required row in BNE_INTERFACE_COLS_TL only where P_LANGUAGE is populated
5693 IF (P_LANGUAGE IS NOT NULL) THEN
5694 UPDATE BNE_INTERFACE_COLS_TL
5695 SET USER_HINT = P_USER_HINT,
5696 PROMPT_LEFT = P_PROMPT_LEFT,
5697 USER_HELP_TEXT = P_USER_HELP_TEXT,
5698 PROMPT_ABOVE = P_PROMPT_ABOVE,
5699 LAST_UPDATED_BY = P_USER_ID,
5700 LAST_UPDATE_LOGIN = P_USER_ID,
5701 LAST_UPDATE_DATE = SYSDATE
5702 WHERE APPLICATION_ID = P_APPLICATION_ID
5703 AND INTERFACE_CODE = P_INTERFACE_CODE
5704 AND SEQUENCE_NUM = P_SEQUENCE_NUM
5705 AND LANGUAGE = P_LANGUAGE
5706 AND SOURCE_LANG = P_SOURCE_LANG;
5707 END IF;
5708
5709 END UPDATE_INTERFACE_COLUMN_TEXT;
5710
5711 END BNE_INTEGRATOR_UTILS;