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