[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT APPLICATION_ID
INTO VN_APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_ID = P_APPLICATION_ID;
SELECT LENGTH(P_OBJECT_CODE),
DECODE(LENGTH(RTRIM(TRANSLATE(P_OBJECT_CODE,
VV_VALID_CHARS,
VV_TEMP_VALID_CHARS),
'*')),
NULL, 'Y', -- set to Y (VALID) if all characters are within the specified range.
'N') -- set to N (INVALID) if any other characters are contained in the string.
INTO VN_CODE_LENGTH, VV_VALID_FLAG
FROM DUAL;
UPDATE BNE_INTERFACES_B
SET UPLOAD_PARAM_LIST_APP_ID = P_PARAM_LIST_APP_ID,
UPLOAD_PARAM_LIST_CODE = P_PARAM_LIST_CODE,
OBJECT_VERSION_NUMBER = (OBJECT_VERSION_NUMBER + 1)
WHERE APPLICATION_ID = P_INTERFACE_APP_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
SELECT CP_APPLICATION_ID APPLICATION_ID,
CP_OBJECT_CODE||'_P'||TO_CHAR(A.SEQUENCE#)||'_ATT' ATTRIBUTE_CODE,
A.ARGUMENT PARAM_NAME,
DECODE(A.TYPE#, 252, 'boolean',
12, 'date',
2, 'number',
1, 'varchar2',
'varchar2') ATTRIBUTE2,
DECODE(A.IN_OUT,1,'OUT',2,'INOUT','IN') ATTRIBUTE3,
'N' ATTRIBUTE4,
DECODE(A.TYPE#, 252, NULL,
12, NULL,
2, NULL,
1, '2000') ATTRIBUTE6,
CP_OBJECT_CODE PARAM_LIST_CODE,
A.SEQUENCE# SEQ_NUM,
CP_USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
CP_USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE
FROM SYS.ARGUMENT$ A,
USER_OBJECTS B
WHERE A.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME = CP_API_PACKAGE_NAME
AND A.PROCEDURE$ = CP_API_PROCEDURE_NAME
AND A.LEVEL# = 0
AND A.OVERLOAD# = CP_OVERLOAD;
SELECT A.PARAM_LIST_CODE
INTO P_PARAM_LIST_CODE
FROM BNE_PARAM_LISTS_B A, BNE_PARAM_LISTS_TL B
WHERE A.APPLICATION_ID = B.APPLICATION_ID
AND A.PARAM_LIST_CODE = B.PARAM_LIST_CODE
AND B.LANGUAGE = P_LANGUAGE
AND A.APPLICATION_ID = P_APPLICATION_ID
AND A.PARAM_LIST_CODE = P_OBJECT_CODE;
SELECT DISTINCT ATTRIBUTE_CODE
INTO VV_TEMP_ATTRIBUTE_CODE
FROM BNE_ATTRIBUTES
WHERE APPLICATION_ID = P_APPLICATION_ID
AND ATTRIBUTE_CODE = VV_ATTRIBUTE_CODE;
INSERT INTO BNE_PARAM_LISTS_B
(APPLICATION_ID, PARAM_LIST_CODE, OBJECT_VERSION_NUMBER, PERSISTENT_FLAG, COMMENTS,
ATTRIBUTE_APP_ID, ATTRIBUTE_CODE, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_PARAM_LIST_CODE, 1, VV_PERSISTENT, P_PARAM_LIST_NAME,
P_APPLICATION_ID, VV_ATTRIBUTE_CODE, P_USER_ID, SYSDATE,
P_USER_ID, SYSDATE);
INSERT INTO BNE_PARAM_LISTS_TL
(APPLICATION_ID, PARAM_LIST_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_PARAM_LIST_CODE, P_LANGUAGE, P_SOURCE_LANG, P_PARAM_LIST_NAME,
P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
INSERT INTO BNE_ATTRIBUTES
(APPLICATION_ID,
ATTRIBUTE_CODE,
OBJECT_VERSION_NUMBER,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID,
VV_ATTRIBUTE_CODE,
1,
P_API_TYPE,
P_API_PACKAGE_NAME||'.'||P_API_PROCEDURE_NAME,
P_API_RETURN_TYPE,
'N',
'Y',
P_USER_ID,
SYSDATE,
P_USER_ID,
SYSDATE);
INSERT INTO BNE_ATTRIBUTES
(APPLICATION_ID,
ATTRIBUTE_CODE,
OBJECT_VERSION_NUMBER,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE6,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(API_PARAMS_REC.APPLICATION_ID,
API_PARAMS_REC.ATTRIBUTE_CODE,
1,
API_PARAMS_REC.PARAM_NAME,
API_PARAMS_REC.ATTRIBUTE2,
API_PARAMS_REC.ATTRIBUTE3,
API_PARAMS_REC.ATTRIBUTE4,
API_PARAMS_REC.ATTRIBUTE6,
API_PARAMS_REC.CREATED_BY,
API_PARAMS_REC.CREATION_DATE,
API_PARAMS_REC.LAST_UPDATED_BY,
API_PARAMS_REC.LAST_UPDATE_DATE);
INSERT INTO BNE_PARAM_LIST_ITEMS
(APPLICATION_ID,
PARAM_LIST_CODE,
SEQUENCE_NUM,
OBJECT_VERSION_NUMBER,
PARAM_NAME,
ATTRIBUTE_APP_ID,
ATTRIBUTE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(API_PARAMS_REC.APPLICATION_ID,
API_PARAMS_REC.PARAM_LIST_CODE,
API_PARAMS_REC.SEQ_NUM,
1,
API_PARAMS_REC.PARAM_NAME,
API_PARAMS_REC.APPLICATION_ID,
API_PARAMS_REC.ATTRIBUTE_CODE,
API_PARAMS_REC.CREATED_BY,
API_PARAMS_REC.CREATION_DATE,
API_PARAMS_REC.LAST_UPDATED_BY,
API_PARAMS_REC.LAST_UPDATE_DATE);
SELECT INTEGRATOR_CODE
INTO VV_INTEGRATOR_CODE
FROM BNE_INTEGRATORS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
INSERT INTO BNE_INTEGRATORS_B
(APPLICATION_ID, INTEGRATOR_CODE, OBJECT_VERSION_NUMBER, DATE_FORMAT,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, ENABLED_FLAG)
VALUES
(P_APPLICATION_ID, P_INTEGRATOR_CODE, 1, 'yyyy-MM-dd',
P_USER_ID, SYSDATE, P_USER_ID, SYSDATE, 'Y');
INSERT INTO BNE_INTEGRATORS_TL
(APPLICATION_ID, INTEGRATOR_CODE, LANGUAGE, SOURCE_LANG, USER_NAME, UPLOAD_HEADER,
UPLOAD_TITLE_BAR, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTEGRATOR_CODE, P_LANGUAGE, P_SOURCE_LANGUAGE, P_INTEGRATOR_USER_NAME,
'Upload Parameters', 'Upload Parameters', P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
SELECT INTEGRATOR_CODE
INTO VV_INTEGRATOR_CODE
FROM BNE_INTEGRATORS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
INSERT INTO BNE_INTEGRATORS_B
(APPLICATION_ID, INTEGRATOR_CODE, OBJECT_VERSION_NUMBER, DATE_FORMAT,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, ENABLED_FLAG)
VALUES
(P_APPLICATION_ID, P_INTEGRATOR_CODE, 1, 'yyyy-MM-dd',
P_USER_ID, SYSDATE, P_USER_ID, SYSDATE, 'Y');
INSERT INTO BNE_INTEGRATORS_TL
(APPLICATION_ID, INTEGRATOR_CODE, LANGUAGE, SOURCE_LANG, USER_NAME, UPLOAD_HEADER,
UPLOAD_TITLE_BAR, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTEGRATOR_CODE, P_LANGUAGE, P_SOURCE_LANGUAGE, P_INTEGRATOR_USER_NAME,
'Upload Parameters', 'Upload Parameters', P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
SELECT CP_APPLICATION_ID APPLICATION_ID,
CP_INTERFACE_CODE INTERFACE_CODE,
1 OBJECT_VERSION_NUMBER,
A.COLUMN_SEQUENCE SEQUENCE_NUM,
1 INTERFACE_COL_TYPE,
A.COLUMN_NAME INTERFACE_COL_NAME,
'Y' ENABLED_FLAG,
DECODE(A.NULL_ALLOWED_FLAG,'N','Y','Y','N') REQUIRED_FLAG,
'Y' DISPLAY_FLAG,
'N' READ_ONLY_FLAG,
DECODE(A.NULL_ALLOWED_FLAG,'N','Y','Y','N') NOT_NULL_FLAG,
'N' SUMMARY_FLAG,
'Y' MAPPING_ENABLED_FLAG,
DECODE(A.COLUMN_TYPE,'N',1,'V',2,'D',3) DATA_TYPE,
DECODE(A.COLUMN_TYPE,'N',A.WIDTH,'V',A.WIDTH,'D',0) FIELD_SIZE,
(A.COLUMN_SEQUENCE * 10) DISPLAY_ORDER,
CP_LANGUAGE LANGUAGE,
CP_SOURCE_LANG SOURCE_LANG,
A.COLUMN_NAME PROMPT_LEFT,
A.COLUMN_NAME PROMPT_ABOVE,
CP_USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
CP_USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE
FROM FND_COLUMNS A,
FND_TABLES B
WHERE A.TABLE_ID = B.TABLE_ID
AND B.TABLE_NAME = CP_INTERFACE_TABLE_NAME
ORDER BY A.COLUMN_SEQUENCE;
SELECT INTERFACE_CODE
INTO VV_INTERFACE_CODE
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
SELECT 1
INTO VN_INTERFACE_EXISTS
FROM BNE_INTERFACES_B BIB, BNE_INTERFACES_TL BIT
WHERE BIB.APPLICATION_ID = BIT.APPLICATION_ID
AND BIB.INTERFACE_CODE = BIT.INTERFACE_CODE
AND BIB.APPLICATION_ID = P_APPLICATION_ID
AND BIT.LANGUAGE = P_LANGUAGE
AND BIB.INTEGRATOR_APP_ID = P_APPLICATION_ID
AND BIB.INTEGRATOR_CODE = P_INTEGRATOR_CODE
AND BIB.INTERFACE_NAME = P_INTERFACE_TABLE_NAME;
INSERT INTO BNE_INTERFACES_B
(APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID, INTEGRATOR_CODE,
INTERFACE_NAME, UPLOAD_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTERFACE_CODE, 1, P_APPLICATION_ID, P_INTEGRATOR_CODE,
P_INTERFACE_TABLE_NAME, 1, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
INSERT INTO BNE_INTERFACES_TL
(APPLICATION_ID, INTERFACE_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTERFACE_CODE, P_LANGUAGE, P_SOURCE_LANG, P_INTERFACE_USER_NAME,
P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
INSERT INTO BNE_INTERFACE_COLS_B
(APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, INTERFACE_COL_TYPE,
INTERFACE_COL_NAME, ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG, READ_ONLY_FLAG, NOT_NULL_FLAG,
SUMMARY_FLAG, MAPPING_ENABLED_FLAG, DATA_TYPE, FIELD_SIZE, DISPLAY_ORDER,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(TABLE_COLUMN_REC.APPLICATION_ID,
TABLE_COLUMN_REC.INTERFACE_CODE,
TABLE_COLUMN_REC.OBJECT_VERSION_NUMBER,
TABLE_COLUMN_REC.SEQUENCE_NUM,
TABLE_COLUMN_REC.INTERFACE_COL_TYPE,
TABLE_COLUMN_REC.INTERFACE_COL_NAME,
TABLE_COLUMN_REC.ENABLED_FLAG,
TABLE_COLUMN_REC.REQUIRED_FLAG,
TABLE_COLUMN_REC.DISPLAY_FLAG,
TABLE_COLUMN_REC.READ_ONLY_FLAG,
TABLE_COLUMN_REC.NOT_NULL_FLAG,
TABLE_COLUMN_REC.SUMMARY_FLAG,
TABLE_COLUMN_REC.MAPPING_ENABLED_FLAG,
TABLE_COLUMN_REC.DATA_TYPE,
TABLE_COLUMN_REC.FIELD_SIZE,
TABLE_COLUMN_REC.DISPLAY_ORDER,
TABLE_COLUMN_REC.CREATED_BY,
TABLE_COLUMN_REC.CREATION_DATE,
TABLE_COLUMN_REC.LAST_UPDATED_BY,
TABLE_COLUMN_REC.LAST_UPDATE_DATE);
INSERT INTO BNE_INTERFACE_COLS_TL
(APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, LANGUAGE, SOURCE_LANG, PROMPT_LEFT,
PROMPT_ABOVE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(TABLE_COLUMN_REC.APPLICATION_ID,
TABLE_COLUMN_REC.INTERFACE_CODE,
TABLE_COLUMN_REC.SEQUENCE_NUM,
TABLE_COLUMN_REC.LANGUAGE,
TABLE_COLUMN_REC.SOURCE_LANG,
TABLE_COLUMN_REC.PROMPT_LEFT,
TABLE_COLUMN_REC.PROMPT_ABOVE,
TABLE_COLUMN_REC.CREATED_BY,
TABLE_COLUMN_REC.CREATION_DATE,
TABLE_COLUMN_REC.LAST_UPDATED_BY,
TABLE_COLUMN_REC.LAST_UPDATE_DATE);
SELECT COUNT(*)
INTO VN_STANDARD_WHO_COUNT
FROM BNE_INTERFACE_COLS_B BIC
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND INTERFACE_COL_NAME IN
('CREATED_BY','LAST_UPDATED_BY','LAST_UPDATE_LOGIN','CREATION_DATE','LAST_UPDATE_DATE');
UPDATE BNE_INTERFACE_COLS_B
SET DISPLAY_FLAG = 'N'
,REQUIRED_FLAG = 'Y'
,DEFAULT_TYPE = 'ENVIRONMENT'
,DEFAULT_VALUE = 'OAUSER.ID'
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND INTERFACE_COL_NAME IN
('CREATED_BY','LAST_UPDATED_BY','LAST_UPDATE_LOGIN');
UPDATE BNE_INTERFACE_COLS_B
SET DISPLAY_FLAG = 'N'
,REQUIRED_FLAG = 'Y'
,DEFAULT_TYPE = 'ENVIRONMENT'
,DEFAULT_VALUE = 'SYSDATE'
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND INTERFACE_COL_NAME IN ('CREATION_DATE','LAST_UPDATE_DATE');
SELECT CP_APPLICATION_ID APPLICATION_ID,
CP_INTERFACE_CODE INTERFACE_CODE,
1 OBJECT_VERSION_NUMBER,
A.SEQUENCE# SEQUENCE_NUM,
1 INTERFACE_COL_TYPE,
DECODE(A.TYPE#, 252, '2',
12, '3',
2, '1',
1, '2',
'2') DATA_TYPE,
A.ARGUMENT INTERFACE_COL_NAME,
'N' NOT_NULL_FLAG,
'N' SUMMARY_FLAG,
'Y' ENABLED_FLAG,
'Y' DISPLAY_FLAG,
'Y' MAPPING_ENABLED_FLAG,
DECODE(DEFAULT#,NULL,DECODE(IN_OUT,NULL,'Y','N'),'N') REQUIRED_FLAG,
'N' READ_ONLY_FLAG,
(A.SEQUENCE# * 10) DISPLAY_ORDER,
A.SEQUENCE# UPLOAD_PARAM_LIST_ITEM_NUM,
SUBSTR(A.ARGUMENT,3) PROMPT_LEFT,
SUBSTR(A.ARGUMENT,3) PROMPT_ABOVE,
CP_LANGUAGE LANGUAGE,
CP_SOURCE_LANG SOURCE_LANG,
CP_USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
CP_USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE
FROM SYS.ARGUMENT$ A, USER_OBJECTS B
WHERE A.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME = CP_API_PACKAGE_NAME
AND A.PROCEDURE$ = CP_API_PROCEDURE_NAME
AND A.LEVEL# = 0
AND A.OVERLOAD# = CP_OVERLOAD;
SELECT INTERFACE_CODE
INTO VV_INTERFACE_CODE
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
SELECT 1
INTO VN_INTERFACE_EXISTS
FROM BNE_INTERFACES_B BIB, BNE_INTERFACES_TL BIT
WHERE BIB.APPLICATION_ID = BIT.APPLICATION_ID
AND BIB.INTERFACE_CODE = BIT.INTERFACE_CODE
AND BIB.INTEGRATOR_APP_ID = P_APPLICATION_ID
AND BIB.INTEGRATOR_CODE = P_INTEGRATOR_CODE
AND BIT.SOURCE_LANG = P_SOURCE_LANG
AND BIT.LANGUAGE = P_LANGUAGE
AND BIB.APPLICATION_ID = P_APPLICATION_ID
AND BIB.INTERFACE_NAME = P_API_PROCEDURE_NAME;
SELECT MIN(A.OVERLOAD#)
INTO VN_OVERLOAD
FROM SYS.ARGUMENT$ A,
USER_OBJECTS B
WHERE A.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME = P_API_PACKAGE_NAME
AND A.PROCEDURE$ = P_API_PROCEDURE_NAME
AND A.LEVEL# = 0;
INSERT INTO BNE_INTERFACES_B
(APPLICATION_ID,
INTERFACE_CODE,
OBJECT_VERSION_NUMBER,
INTEGRATOR_APP_ID,
INTEGRATOR_CODE,
INTERFACE_NAME,
UPLOAD_TYPE,
UPLOAD_PARAM_LIST_APP_ID,
UPLOAD_PARAM_LIST_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID,
P_INTERFACE_CODE,
1,
P_APPLICATION_ID,
P_INTEGRATOR_CODE,
P_API_PROCEDURE_NAME,
P_UPLOAD_TYPE,
P_APPLICATION_ID,
P_PARAM_LIST_CODE,
P_USER_ID,
SYSDATE,
P_USER_ID,
SYSDATE);
INSERT INTO BNE_INTERFACES_TL
(APPLICATION_ID,
INTERFACE_CODE,
LANGUAGE,
SOURCE_LANG,
USER_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID,
P_INTERFACE_CODE,
P_LANGUAGE,
P_SOURCE_LANG,
P_INTERFACE_USER_NAME,
P_USER_ID,
SYSDATE,
P_USER_ID,
SYSDATE);
INSERT INTO BNE_INTERFACE_COLS_B
(APPLICATION_ID,
INTERFACE_CODE,
OBJECT_VERSION_NUMBER,
SEQUENCE_NUM,
INTERFACE_COL_TYPE,
DATA_TYPE,
INTERFACE_COL_NAME,
NOT_NULL_FLAG,
SUMMARY_FLAG,
ENABLED_FLAG,
DISPLAY_FLAG,
MAPPING_ENABLED_FLAG,
REQUIRED_FLAG,
READ_ONLY_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
DISPLAY_ORDER,
UPLOAD_PARAM_LIST_ITEM_NUM)
VALUES
(API_PARAM_REC.APPLICATION_ID,
API_PARAM_REC.INTERFACE_CODE,
API_PARAM_REC.OBJECT_VERSION_NUMBER,
API_PARAM_REC.SEQUENCE_NUM,
API_PARAM_REC.INTERFACE_COL_TYPE,
API_PARAM_REC.DATA_TYPE,
API_PARAM_REC.INTERFACE_COL_NAME,
API_PARAM_REC.NOT_NULL_FLAG,
API_PARAM_REC.SUMMARY_FLAG,
API_PARAM_REC.ENABLED_FLAG,
API_PARAM_REC.DISPLAY_FLAG,
API_PARAM_REC.MAPPING_ENABLED_FLAG,
API_PARAM_REC.REQUIRED_FLAG,
API_PARAM_REC.READ_ONLY_FLAG,
API_PARAM_REC.CREATED_BY,
API_PARAM_REC.CREATION_DATE,
API_PARAM_REC.LAST_UPDATED_BY,
API_PARAM_REC.LAST_UPDATE_DATE,
API_PARAM_REC.DISPLAY_ORDER,
API_PARAM_REC.UPLOAD_PARAM_LIST_ITEM_NUM);
INSERT INTO BNE_INTERFACE_COLS_TL
(APPLICATION_ID,
INTERFACE_CODE,
SEQUENCE_NUM,
LANGUAGE,
SOURCE_LANG,
PROMPT_LEFT,
PROMPT_ABOVE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(API_PARAM_REC.APPLICATION_ID,
API_PARAM_REC.INTERFACE_CODE,
API_PARAM_REC.SEQUENCE_NUM,
API_PARAM_REC.LANGUAGE,
API_PARAM_REC.SOURCE_LANG,
API_PARAM_REC.PROMPT_LEFT,
API_PARAM_REC.PROMPT_ABOVE,
API_PARAM_REC.CREATED_BY,
API_PARAM_REC.CREATION_DATE,
API_PARAM_REC.LAST_UPDATED_BY,
API_PARAM_REC.LAST_UPDATE_DATE);
SELECT 1
INTO VN_VALID_CONTENT
FROM BNE_CONTENTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE
AND INTEGRATOR_APP_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
SELECT INTERFACE_CODE
INTO VV_INTERFACE_CODE
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
SELECT 1
INTO VN_INTERFACE_EXISTS
FROM BNE_INTERFACES_B BI,
BNE_INTERFACES_TL BITL,
BNE_CONTENTS_B BC,
BNE_CONTENTS_TL BCTL
WHERE BI.APPLICATION_ID = P_APPLICATION_ID
AND BI.APPLICATION_ID = BC.APPLICATION_ID
AND BC.CONTENT_CODE = P_CONTENT_CODE
AND BI.APPLICATION_ID = BITL.APPLICATION_ID
AND BI.INTERFACE_CODE = BITL.INTERFACE_CODE
AND BI.INTEGRATOR_APP_ID = P_APPLICATION_ID
AND BI.INTEGRATOR_CODE = P_INTEGRATOR_CODE
AND BC.APPLICATION_ID = BCTL.APPLICATION_ID
AND BC.CONTENT_CODE = BCTL.CONTENT_CODE
AND BITL.USER_NAME = BCTL.USER_NAME
AND BCTL.LANGUAGE = P_LANGUAGE
AND BITL.LANGUAGE = P_LANGUAGE;
SELECT USER_NAME
INTO VV_DESCRIPTION
FROM BNE_CONTENTS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE
AND LANGUAGE = P_LANGUAGE;
INSERT INTO BNE_INTERFACES_B
(APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, INTEGRATOR_APP_ID, INTEGRATOR_CODE,
INTERFACE_NAME, UPLOAD_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTERFACE_CODE, 1, P_APPLICATION_ID, P_INTEGRATOR_CODE,
P_INTERFACE_CODE, 4, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
INSERT INTO BNE_INTERFACES_TL
(APPLICATION_ID, INTERFACE_CODE, LANGUAGE, SOURCE_LANG, USER_NAME,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTERFACE_CODE, P_LANGUAGE, P_SOURCE_LANG, VV_DESCRIPTION,
P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
INSERT INTO BNE_INTERFACE_COLS_B
(APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, INTERFACE_COL_TYPE, INTERFACE_COL_NAME,
ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG, READ_ONLY_FLAG, NOT_NULL_FLAG, SUMMARY_FLAG, DATA_TYPE, MAPPING_ENABLED_FLAG, DISPLAY_ORDER,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
SELECT P_APPLICATION_ID APPLICATION_ID,
P_INTERFACE_CODE INTERFACE_CODE,
1 OBJECT_VERSION_NUMBER,
BCC.SEQUENCE_NUM SEQUENCE_NUM,
1 INTERFACE_COL_TYPE,
BCC.COL_NAME INTERFACE_COL_NAME,
'Y' ENABLED_FLAG,
'N' REQUIRED_FLAG,
'Y' DISPLAY_FLAG,
'N' READ_ONLY_FLAG,
'N' NOT_NULL_FLAG,
'N' SUMMARY_FLAG,
2 DATA_TYPE,
'Y' MAPPING_ENABLED_FLAG,
(BCC.SEQUENCE_NUM * 10) DISPLAY_ORDER,
P_USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
P_USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE
FROM BNE_CONTENT_COLS_B BCC
WHERE BCC.APPLICATION_ID = P_APPLICATION_ID
AND BCC.CONTENT_CODE = P_CONTENT_CODE;
INSERT INTO BNE_INTERFACE_COLS_TL
(APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, PROMPT_LEFT, PROMPT_ABOVE, LANGUAGE, SOURCE_LANG,
CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
SELECT P_APPLICATION_ID APPLICATION_ID,
P_INTERFACE_CODE INTERFACE_CODE,
BCC.SEQUENCE_NUM SEQUENCE_NUM,
NVL(BCCTL.USER_NAME, BCC.COL_NAME) PROMPT_LEFT,
NVL(BCCTL.USER_NAME, BCC.COL_NAME) PROMPT_ABOVE,
P_LANGUAGE LANGUAGE,
P_SOURCE_LANG SOURCE_LANG,
P_USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
P_USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE
FROM BNE_CONTENT_COLS_B BCC,
BNE_CONTENT_COLS_TL BCCTL
WHERE BCC.APPLICATION_ID = BCCTL.APPLICATION_ID
AND BCC.CONTENT_CODE = BCCTL.CONTENT_CODE
AND BCC.SEQUENCE_NUM = BCCTL.SEQUENCE_NUM
AND BCC.APPLICATION_ID = P_APPLICATION_ID
AND BCC.CONTENT_CODE = P_CONTENT_CODE
AND BCCTL.LANGUAGE = P_LANGUAGE;
SELECT 1
INTO VN_NO_INTERFACE_COL_FLAG
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM;
INSERT INTO BNE_INTERFACE_COLS_B
(APPLICATION_ID, INTERFACE_CODE, OBJECT_VERSION_NUMBER, SEQUENCE_NUM, INTERFACE_COL_TYPE, INTERFACE_COL_NAME,
ENABLED_FLAG, REQUIRED_FLAG, DISPLAY_FLAG, READ_ONLY_FLAG, NOT_NULL_FLAG, SUMMARY_FLAG, MAPPING_ENABLED_FLAG,
DATA_TYPE, FIELD_SIZE, DEFAULT_TYPE, DEFAULT_VALUE, SEGMENT_NUMBER, GROUP_NAME, OA_FLEX_CODE, OA_CONCAT_FLEX,
VAL_TYPE, VAL_ID_COL, VAL_MEAN_COL, VAL_DESC_COL, VAL_OBJ_NAME, VAL_ADDL_W_C, VAL_COMPONENT_APP_ID,
VAL_COMPONENT_CODE, OA_FLEX_NUM, OA_FLEX_APPLICATION_ID, DISPLAY_ORDER, UPLOAD_PARAM_LIST_ITEM_NUM,
EXPANDED_SQL_QUERY, LOV_TYPE, OFFLINE_LOV_ENABLED_FLAG, VARIABLE_DATA_TYPE_CLASS, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTERFACE_CODE, 1, P_SEQUENCE_NUM, P_INTERFACE_COL_TYPE, P_INTERFACE_COL_NAME,
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,
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,
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,
P_VAL_COMPONENT_CODE, P_OA_FLEX_NUM, P_OA_FLEX_APPLICATION_ID, P_DISPLAY_ORDER, P_UPLOAD_PARAM_LIST_ITEM_NUM,
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);
INSERT INTO BNE_INTERFACE_COLS_TL
(APPLICATION_ID, INTERFACE_CODE, SEQUENCE_NUM, LANGUAGE, SOURCE_LANG, USER_HINT, PROMPT_LEFT,
USER_HELP_TEXT, PROMPT_ABOVE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE)
VALUES
(P_APPLICATION_ID, P_INTERFACE_CODE, P_SEQUENCE_NUM, P_LANGUAGE, P_SOURCE_LANG, P_USER_HINT, P_PROMPT_LEFT,
P_USER_HELP_TEXT, P_PROMPT_ABOVE, P_USER_ID, SYSDATE, P_USER_ID, SYSDATE);
UPDATE BNE_INTERFACE_COLS_B
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
INTERFACE_COL_TYPE = P_INTERFACE_COL_TYPE,
INTERFACE_COL_NAME = P_INTERFACE_COL_NAME,
ENABLED_FLAG = P_ENABLED_FLAG,
REQUIRED_FLAG = P_REQUIRED_FLAG,
DISPLAY_FLAG = P_DISPLAY_FLAG,
READ_ONLY_FLAG = NVL(P_READ_ONLY_FLAG,'N'),
NOT_NULL_FLAG = P_NOT_NULL_FLAG,
SUMMARY_FLAG = NVL(P_SUMMARY_FLAG,'N'),
MAPPING_ENABLED_FLAG = P_MAPPING_ENABLED_FLAG,
DATA_TYPE = P_DATA_TYPE,
FIELD_SIZE = P_FIELD_SIZE,
DEFAULT_TYPE = P_DEFAULT_TYPE,
DEFAULT_VALUE = P_DEFAULT_VALUE,
SEGMENT_NUMBER = P_SEGMENT_NUMBER,
GROUP_NAME = P_GROUP_NAME,
OA_FLEX_CODE = P_OA_FLEX_CODE,
OA_CONCAT_FLEX = P_OA_CONCAT_FLEX,
VAL_TYPE = P_VAL_TYPE,
VAL_ID_COL = P_VAL_ID_COL,
VAL_MEAN_COL = P_VAL_MEAN_COL,
VAL_DESC_COL = P_VAL_DESC_COL,
VAL_OBJ_NAME = P_VAL_OBJ_NAME,
VAL_ADDL_W_C = P_VAL_ADDL_W_C,
VAL_COMPONENT_APP_ID = P_VAL_COMPONENT_APP_ID,
VAL_COMPONENT_CODE = P_VAL_COMPONENT_CODE,
OA_FLEX_NUM = P_OA_FLEX_NUM,
OA_FLEX_APPLICATION_ID = P_OA_FLEX_APPLICATION_ID,
DISPLAY_ORDER = P_DISPLAY_ORDER,
UPLOAD_PARAM_LIST_ITEM_NUM = P_UPLOAD_PARAM_LIST_ITEM_NUM,
EXPANDED_SQL_QUERY = P_EXPANDED_SQL_QUERY,
LOV_TYPE = P_LOV_TYPE,
OFFLINE_LOV_ENABLED_FLAG = P_OFFLINE_LOV_ENABLED_FLAG,
VARIABLE_DATA_TYPE_CLASS = P_VARIABLE_DATA_TYPE_CLASS,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = P_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM;
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = P_USER_HINT,
PROMPT_LEFT = P_PROMPT_LEFT,
USER_HELP_TEXT = P_USER_HELP_TEXT,
PROMPT_ABOVE = P_PROMPT_ABOVE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = P_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM
AND LANGUAGE = P_LANGUAGE
AND SOURCE_LANG = P_SOURCE_LANG;
SELECT CP_APPLICATION_ID APPLICATION_ID,
CP_INTERFACE_CODE INTERFACE_CODE,
1 OBJECT_VERSION_NUMBER,
2 INTERFACE_COL_TYPE,
DECODE(ATC.DATA_TYPE, 'BOOLEAN', '2',
'DATE', '3',
'NUMBER', '1',
'VARCHAR2', '2') DATA_TYPE,
ATC.COLUMN_NAME INTERFACE_COL_NAME,
'N' NOT_NULL_FLAG,
'N' SUMMARY_FLAG,
'Y' ENABLED_FLAG,
'Y' DISPLAY_FLAG,
'Y' MAPPING_ENABLED_FLAG,
'N' REQUIRED_FLAG,
'Y' READ_ONLY_FLAG,
ATC.COLUMN_NAME PROMPT_LEFT,
ATC.COLUMN_NAME PROMPT_ABOVE,
CP_LANGUAGE LANGUAGE,
CP_SOURCE_LANG SOURCE_LANG,
CP_USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
CP_USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE
FROM ALL_TAB_COLUMNS ATC
WHERE ATC.OWNER = CP_ORACLE_USER
AND ATC.TABLE_NAME = CP_VIEW_NAME
AND NOT EXISTS
(SELECT 1
FROM BNE_INTERFACE_COLS_B IC
WHERE SUBSTR(IC.INTERFACE_COL_NAME,3) = ATC.COLUMN_NAME
AND IC.INTERFACE_CODE = CP_INTERFACE_CODE)
ORDER BY ATC.COLUMN_ID;
SELECT ORACLE_USERNAME
INTO VV_ORACLE_USER
FROM FND_ORACLE_USERID
WHERE ORACLE_ID = 900;
SELECT MAX(SEQUENCE_NUM)
INTO VN_SEQUENCE_NUM
FROM BNE_INTERFACE_COLS_B
WHERE INTERFACE_CODE = P_INTERFACE_CODE
AND APPLICATION_ID = P_APPLICATION_ID;
INSERT INTO BNE_INTERFACE_COLS_B
(APPLICATION_ID,
INTERFACE_CODE,
OBJECT_VERSION_NUMBER,
SEQUENCE_NUM,
INTERFACE_COL_TYPE,
DATA_TYPE,
INTERFACE_COL_NAME,
NOT_NULL_FLAG,
SUMMARY_FLAG,
ENABLED_FLAG,
DISPLAY_FLAG,
MAPPING_ENABLED_FLAG,
REQUIRED_FLAG,
READ_ONLY_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
DISPLAY_ORDER,
UPLOAD_PARAM_LIST_ITEM_NUM)
VALUES
(API_PARAM_REC.APPLICATION_ID,
API_PARAM_REC.INTERFACE_CODE,
API_PARAM_REC.OBJECT_VERSION_NUMBER,
VN_SEQUENCE_NUM,
API_PARAM_REC.INTERFACE_COL_TYPE,
API_PARAM_REC.DATA_TYPE,
API_PARAM_REC.INTERFACE_COL_NAME,
API_PARAM_REC.NOT_NULL_FLAG,
API_PARAM_REC.SUMMARY_FLAG,
API_PARAM_REC.ENABLED_FLAG,
API_PARAM_REC.DISPLAY_FLAG,
API_PARAM_REC.MAPPING_ENABLED_FLAG,
API_PARAM_REC.REQUIRED_FLAG,
API_PARAM_REC.READ_ONLY_FLAG,
API_PARAM_REC.CREATED_BY,
API_PARAM_REC.CREATION_DATE,
API_PARAM_REC.LAST_UPDATED_BY,
API_PARAM_REC.LAST_UPDATE_DATE,
VN_SEQUENCE_NUM,
VN_SEQUENCE_NUM);
INSERT INTO BNE_INTERFACE_COLS_TL
(APPLICATION_ID,
INTERFACE_CODE,
SEQUENCE_NUM,
LANGUAGE,
SOURCE_LANG,
PROMPT_LEFT,
PROMPT_ABOVE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(API_PARAM_REC.APPLICATION_ID,
API_PARAM_REC.INTERFACE_CODE,
VN_SEQUENCE_NUM,
API_PARAM_REC.LANGUAGE,
API_PARAM_REC.SOURCE_LANG,
API_PARAM_REC.PROMPT_LEFT,
API_PARAM_REC.PROMPT_ABOVE,
API_PARAM_REC.CREATED_BY,
API_PARAM_REC.CREATION_DATE,
API_PARAM_REC.LAST_UPDATED_BY,
API_PARAM_REC.LAST_UPDATE_DATE);
SELECT APPLICATION_ID,
INTERFACE_CODE,
SEQUENCE_NUM
FROM BNE_INTERFACE_COLS_VL
WHERE APPLICATION_ID = CP_APPLICATION_ID
AND INTERFACE_CODE = CP_INTERFACE_CODE
AND REQUIRED_FLAG = CP_REQUIRED_FLAG;
VN_COLS_INSERTED NUMBER;
BNE_LAYOUTS_PKG.DELETE_ROW(P_APPLICATION_ID,P_LAYOUT_CODE);
DELETE BNE_LAYOUT_LOBS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE BNE_LAYOUT_BLOCKS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE BNE_LAYOUT_BLOCKS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE BNE_LAYOUT_COLS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE BNE_LAYOUT_LOBS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_LOBS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_BLOCKS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_BLOCKS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_COLS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
SELECT COUNT(*) INTO VN_ROW_COUNT FROM BNE_LAYOUT_LOBS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
BNE_LAYOUTS_PKG.Insert_Row(
VR_ROW_ID,
P_APPLICATION_ID, --APPLICATION_ID
P_LAYOUT_CODE, --LAYOUT_CODE
1, --OBJECT_VERSION_NUMBER
231, --STYLESHEET_APP_ID
'DEFAULT', --STYLESHEET_CODE
P_APPLICATION_ID, --INTEGRATOR_APP_ID
P_INTEGRATOR_CODE, --INTEGRATOR_CODE
NULL, --STYLE
'BNE_PAGE', --STYLE_CLASS
'N', --REPORTING_FLAG
NULL, --REPORTING_INTERFACE_APP_ID
NULL, --REPORTING_INTERFACE_CODE
'Default', --USER_NAME
SYSDATE, --CREATION_DATE
P_USER_ID, --CREATED_BY
SYSDATE, --LAST_UPDATE_DATE
P_USER_ID, --LAST_UPDATED_BY
P_USER_ID, --LAST_UPDATE_LOGIN
NULL, --CREATE_DOC_LIST_APP_ID
NULL --CREATE_DOC_LIST_CODE
);
BNE_LAYOUT_BLOCKS_PKG.Insert_Row(
VR_ROW_ID,
P_APPLICATION_ID, --APPLICATION_ID
P_LAYOUT_CODE, --LAYOUT_CODE
3, --BLOCK_ID
1, --OBJECT_VERSION_NUMBER
NULL, --PARENT_ID
'LINE', --LAYOUT_ELEMENT
'BNE_LINES', --STYLE_CLASS
NULL, --STYLE
'BNE_LINES_ROW', --ROW_STYLE_CLASS
NULL, --ROW_STYLE
NULL, --COL_STYLE_CLASS
NULL, --COL_STYLE
'Y', --PROMPT_DISPLAYED_FLAG
'BNE_LINES_HEADER', --PROMPT_STYLE_CLASS
NULL, --PROMPT_STYLE
'N', --HINT_DISPLAYED_FLAG
'BNE_LINES_HINT', --HINT_STYLE_CLASS
NULL, --HINT_STYLE
'VERTICAL', --ORIENTATION
'TABLE_FLOW', --LAYOUT_CONTROL
'Y', --DISPLAY_FLAG
10, --BLOCKSIZE
1, --MINSIZE
1, --MAXSIZE
30, --SEQUENCE_NUM
NULL, --PROMPT_COLSPAN
NULL, --HINT_COLSPAN
NULL, --ROW_COLSPAN
'BNE_LINES_TOTAL', --SUMMARY_STYLE_CLASS
NULL, --SUMMARY_STYLE
'Line', --USER_NAME
SYSDATE, --CREATION_DATE
P_USER_ID, --CREATED_BY
SYSDATE, --LAST_UPDATE_DATE
P_USER_ID, --LAST_UPDATED_BY
P_USER_ID, --LAST_UPDATE_LOGIN
NULL, --PROMPT_ABOVE
'TITLE', -- TITLE_STYLE_CLASS
NULL --TITLE_STYLE
);
VN_COLS_INSERTED:=0;
BNE_LAYOUT_COLS_PKG.Insert_Row(
VR_ROW_ID,
P_APPLICATION_ID, --APPLICATION_ID
P_LAYOUT_CODE, --LAYOUT_CODE
3, --BLOCK_ID
(INTERFACE_COLS_REC.SEQUENCE_NUM * 10), --SEQUENCE_NUM
1, --OBJECT_VERSION_NUMBER
P_APPLICATION_ID, --INTERFACE_APP_ID
P_INTERFACE_CODE, --INTERFACE_CODE
INTERFACE_COLS_REC.SEQUENCE_NUM, --INTERFACE_SEQ_NUM
NULL, --STYLE_CLASS
NULL, --HINT_STYLE
NULL, --HINT_STYLE_CLASS
NULL, --PROMPT_STYLE
NULL, --PROMPT_STYLE_CLASS
NULL, --DEFAULT_TYPE
NULL, --DEFAULT_VALUE
NULL, --STYLE
SYSDATE, --CREATION_DATE
P_USER_ID, --CREATED_BY
SYSDATE, --LAST_UPDATE_DATE
P_USER_ID, --LAST_UPDATED_BY
P_USER_ID, --LAST_UPDATE_LOGIN
NULL, --DISPLAY_WIDTH
'N' --READ_ONLY_FLAG
);
VN_COLS_INSERTED:=VN_COLS_INSERTED+1;
if (VN_COLS_INSERTED = 0 or P_ALL_COLUMNS) then
FOR INTERFACE_COLS_REC IN INTERFACE_COLS_C(P_APPLICATION_ID,
P_INTERFACE_CODE,
'N') LOOP
BNE_LAYOUT_COLS_PKG.Insert_Row(
VR_ROW_ID,
P_APPLICATION_ID, --APPLICATION_ID
P_LAYOUT_CODE, --LAYOUT_CODE
3, --BLOCK_ID
(INTERFACE_COLS_REC.SEQUENCE_NUM * 10), --SEQUENCE_NUM
1, --OBJECT_VERSION_NUMBER
P_APPLICATION_ID, --INTERFACE_APP_ID
P_INTERFACE_CODE, --INTERFACE_CODE
INTERFACE_COLS_REC.SEQUENCE_NUM, --INTERFACE_SEQ_NUM
NULL, --STYLE_CLASS
NULL, --HINT_STYLE
NULL, --HINT_STYLE_CLASS
NULL, --PROMPT_STYLE
NULL, --PROMPT_STYLE_CLASS
NULL, --DEFAULT_TYPE
NULL, --DEFAULT_VALUE
NULL, --STYLE
SYSDATE, --CREATION_DATE
P_USER_ID, --CREATED_BY
SYSDATE, --LAST_UPDATE_DATE
P_USER_ID, --LAST_UPDATED_BY
P_USER_ID, --LAST_UPDATE_LOGIN
NULL, --DISPLAY_WIDTH
'N' --READ_ONLY_FLAG
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL,
x_oa_flex_asn => NULL,
x_oa_flex_code => NULL,
x_oa_flex_num => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
P_TABLE_SELECT_COLUMNS IN VARCHAR2,
P_TABLE_COLUMN_ALIAS IN VARCHAR2,
P_TABLE_HEADERS IN VARCHAR2,
P_TABLE_SORT_ORDER IN VARCHAR2,
P_USER_NAME IN VARCHAR2)
IS
VV_PARAM_DEFN_CODE VARCHAR2(30);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL,
x_oa_flex_asn => NULL,
x_oa_flex_code => NULL,
x_oa_flex_num => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
IF P_TABLE_SELECT_COLUMNS IS NOT NULL
THEN
VV_PARAM_SEQ_NUM := VV_PARAM_SEQ_NUM + 1;
x_param_name => 'table-select-column',
x_attribute_asn => NULL,
x_attribute_code => NULL,
x_string_value => P_TABLE_SELECT_COLUMNS,
x_date_value => NULL,
x_number_value => NULL,
x_boolean_value_flag => NULL,
x_formula_value => NULL,
x_desc_value => 'Table Select Column',
x_owner => P_USER_NAME,
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL,
x_oa_flex_asn => NULL,
x_oa_flex_code => NULL,
x_oa_flex_num => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
P_TABLE_SELECT_COLUMNS IN VARCHAR2,
P_TABLE_COLUMN_ALIAS IN VARCHAR2,
P_TABLE_HEADERS IN VARCHAR2,
P_POPLIST_FLAG IN VARCHAR2)
IS
VV_APPLICATION_SHORT_NAME VARCHAR2(30);
VV_TABLE_SELECT_COLUMNS VARCHAR2(2000);
SELECT APPLICATION_SHORT_NAME
INTO VV_APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID = P_APPLICATION_ID;
SELECT USER_NAME
INTO VV_USER_NAME
FROM FND_USER
WHERE USER_ID = P_USER_ID;
SELECT *
INTO VV_INTERFACE_COL
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
UPDATE BNE_INTERFACE_COLS_B
SET VAL_TYPE = 'TABLE',
VAL_ID_COL = VV_ID_COL,
VAL_MEAN_COL = VV_MEAN_COL,
VAL_DESC_COL = VV_DESC_COL,
VAL_OBJ_NAME = P_TABLE,
VAL_ADDL_W_C = P_ADDL_W_C,
VAL_COMPONENT_APP_ID = p_application_id,
VAL_COMPONENT_CODE = VV_COMPONENT_CODE,
LOV_TYPE = VV_LOV_TYPE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM;
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = '*List - '||VV_DATA_TYPE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
;
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = 'List - '||VV_DATA_TYPE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
;
IF P_TABLE_SELECT_COLUMNS IS NOT NULL
THEN
VV_TABLE_SELECT_COLUMNS := P_TABLE_SELECT_COLUMNS;
VV_TABLE_SELECT_COLUMNS := VV_INTERFACE_COL_NAME;
P_TABLE_SELECT_COLUMNS => VV_TABLE_SELECT_COLUMNS,
P_TABLE_COLUMN_ALIAS => VV_TABLE_COLUMN_ALIAS,
P_TABLE_HEADERS => VV_TABLE_HEADERS,
P_TABLE_SORT_ORDER => P_TABLE_SORT_ORDER,
P_USER_NAME => VV_USER_NAME
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
P_TABLE_SELECT_COLUMNS IN VARCHAR2,
P_TABLE_COLUMN_ALIAS IN VARCHAR2,
P_TABLE_HEADERS IN VARCHAR2,
P_TABLE_SORT_ORDER IN VARCHAR2,
P_USER_ID IN NUMBER)
IS
VV_APPLICATION_SHORT_NAME VARCHAR2(30);
SELECT APPLICATION_SHORT_NAME
INTO VV_APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID = P_APPLICATION_ID;
SELECT USER_NAME
INTO VV_USER_NAME
FROM FND_USER
WHERE USER_ID = P_USER_ID;
SELECT *
INTO VV_INTERFACE_COL
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
UPDATE BNE_INTERFACE_COLS_B
SET VAL_TYPE = 'JAVA',
VAL_ID_COL = NULL,
VAL_MEAN_COL = NULL,
VAL_DESC_COL = NULL,
VAL_OBJ_NAME = NULL,
VAL_ADDL_W_C = NULL,
VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
VAL_COMPONENT_CODE = VV_COMPONENT_CODE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM;
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = '*List - '||VV_DATA_TYPE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
;
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = 'List - '||VV_DATA_TYPE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
;
P_TABLE_SELECT_COLUMNS => nvl(TRIM(P_TABLE_SELECT_COLUMNS), P_INTERFACE_COL_NAME),
P_TABLE_COLUMN_ALIAS => nvl(TRIM(P_TABLE_COLUMN_ALIAS), P_INTERFACE_COL_NAME),
P_TABLE_HEADERS => TRIM(P_TABLE_HEADERS),
P_TABLE_SORT_ORDER => TRIM(P_TABLE_SORT_ORDER),
P_USER_NAME => VV_USER_NAME
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
SELECT APPLICATION_SHORT_NAME
INTO VV_APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID = P_APPLICATION_ID;
SELECT USER_NAME
INTO VV_USER_NAME
FROM FND_USER
WHERE USER_ID = P_USER_ID;
SELECT *
INTO VV_INTERFACE_COL
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
UPDATE BNE_INTERFACE_COLS_B
SET VAL_TYPE = 'JAVA',
VAL_ID_COL = NULL,
VAL_MEAN_COL = NULL,
VAL_DESC_COL = NULL,
VAL_OBJ_NAME = NULL,
VAL_ADDL_W_C = NULL,
VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
VAL_COMPONENT_CODE = VV_COMPONENT_CODE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM;
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = '*List - '||VV_DATA_TYPE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
;
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = 'List - '||VV_DATA_TYPE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_COL.INTERFACE_CODE
AND SEQUENCE_NUM = VV_INTERFACE_COL.SEQUENCE_NUM
;
P_TABLE_SELECT_COLUMNS => P_INTERFACE_COL_NAME,
P_TABLE_COLUMN_ALIAS => NULL,
P_TABLE_HEADERS => NULL,
P_TABLE_SORT_ORDER => NULL,
P_USER_NAME => VV_USER_NAME
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
SELECT APPLICATION_SHORT_NAME
INTO VV_APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID = P_APPLICATION_ID;
SELECT USER_NAME
INTO VV_USER_NAME
FROM FND_USER
WHERE USER_ID = P_USER_ID;
SELECT COUNT(*)
into VV_dummy
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME like VV_FLEX_SEG_COL_NAME_PREFIX||'%';
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_GROUP_NAME;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND GROUP_NAME = VV_GROUP_NAME;
UPDATE BNE_INTERFACE_COLS_B
SET GROUP_NAME = VV_GROUP_NAME,
INTERFACE_COL_TYPE = 1,
ENABLED_FLAG = 'Y',
REQUIRED_FLAG = 'N',
DISPLAY_FLAG = 'N',
READ_ONLY_FLAG = 'N',
NOT_NULL_FLAG = 'N',
SUMMARY_FLAG = 'N',
MAPPING_ENABLED_FLAG = 'Y',
VAL_TYPE = 'KEYFLEXSEG',
OA_FLEX_CODE = NULL,
OA_FLEX_APPLICATION_ID = NULL,
OA_FLEX_NUM = NULL,
VAL_COMPONENT_APP_ID = NULL,
VAL_COMPONENT_CODE = NULL,
LOV_TYPE = NULL,
OFFLINE_LOV_ENABLED_FLAG = 'N',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = 0
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME like VV_FLEX_SEG_COL_NAME_PREFIX||'%';
SELECT MAX(SEQUENCE_NUM)+1, MAX(DISPLAY_ORDER) + 10
INTO VV_SEQUENCE_NUM, VV_DISPLAY_ORDER
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE;
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_lov_type => NULL,
x_offline_lov_enabled_flag => 'N',
x_custom_mode => NULL,
x_variable_data_type_class => NULL,
x_viewer_group => NULL,
x_edit_type => NULL,
x_val_query_asn => NULL,
x_val_query_code => NULL,
x_expanded_sql_query_asn => NULL,
x_expanded_sql_query_code => NULL,
x_display_width => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
SELECT APPLICATION_SHORT_NAME
INTO VV_APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID = P_APPLICATION_ID;
SELECT USER_NAME
INTO VV_USER_NAME
FROM FND_USER
WHERE USER_ID = P_USER_ID;
SELECT COUNT(*)
into VV_dummy
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME like VV_FLEX_SEG_COL_NAME_PREFIX||'%';
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_GROUP_NAME;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND GROUP_NAME = VV_GROUP_NAME;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_CONTEXT_COL_NAME;
UPDATE BNE_INTERFACE_COLS_B
SET GROUP_NAME = VV_GROUP_NAME,
INTERFACE_COL_TYPE = 1,
ENABLED_FLAG = 'Y',
REQUIRED_FLAG = 'N',
DISPLAY_FLAG = 'N',
READ_ONLY_FLAG = 'N',
NOT_NULL_FLAG = 'N',
SUMMARY_FLAG = 'N',
MAPPING_ENABLED_FLAG = 'Y',
VAL_TYPE = 'DESCFLEXSEG',
OA_FLEX_CODE = NULL,
OA_FLEX_APPLICATION_ID = NULL,
OA_FLEX_NUM = NULL,
VAL_COMPONENT_APP_ID = NULL,
VAL_COMPONENT_CODE = NULL,
LOV_TYPE = NULL,
OFFLINE_LOV_ENABLED_FLAG = 'N',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = 0
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME LIKE VV_FLEX_SEG_COL_NAME_PREFIX||'%';
UPDATE BNE_INTERFACE_COLS_B
SET GROUP_NAME = VV_GROUP_NAME,
INTERFACE_COL_TYPE = 1,
ENABLED_FLAG = 'Y',
REQUIRED_FLAG = 'N',
DISPLAY_FLAG = 'Y',
READ_ONLY_FLAG = 'N',
NOT_NULL_FLAG = 'N',
SUMMARY_FLAG = 'N',
MAPPING_ENABLED_FLAG = 'Y',
VAL_TYPE = 'DESCFLEXCONTEXT',
OA_FLEX_CODE = P_FLEX_CODE,
OA_FLEX_APPLICATION_ID = P_FLEX_APPLICATION_ID,
OA_FLEX_NUM = NULL,
VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
VAL_COMPONENT_CODE = VV_COMPONENT_CODE,
LOV_TYPE = NULL,
OFFLINE_LOV_ENABLED_FLAG = 'N',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = 0
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_CONTEXT_COL_NAME;
SELECT MAX(SEQUENCE_NUM)+1, MAX(DISPLAY_ORDER) + 10
INTO VV_SEQUENCE_NUM, VV_DISPLAY_ORDER
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE;
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_lov_type => NULL,
x_offline_lov_enabled_flag => 'N',
x_custom_mode => NULL,
x_variable_data_type_class => NULL,
x_viewer_group => NULL,
x_edit_type => NULL,
x_val_query_asn => NULL,
x_val_query_code => NULL,
x_expanded_sql_query_asn => NULL,
x_expanded_sql_query_code => NULL,
x_display_width => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
SELECT APPLICATION_SHORT_NAME
INTO VV_APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID = P_APPLICATION_ID;
SELECT USER_NAME
INTO VV_USER_NAME
FROM FND_USER
WHERE USER_ID = P_USER_ID;
SELECT SEQUENCE_NUM
INTO VV_SEQUENCE_NUM
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND GROUP_NAME = VV_GROUP_NAME;
UPDATE BNE_INTERFACE_COLS_B
SET GROUP_NAME = VV_GROUP_NAME,
INTERFACE_COL_TYPE = 1,
ENABLED_FLAG = 'Y',
READ_ONLY_FLAG = 'N',
SUMMARY_FLAG = 'N',
VAL_TYPE = 'KEYFLEXID',
OA_FLEX_CODE = P_FLEX_CODE,
OA_FLEX_APPLICATION_ID = P_FLEX_APPLICATION_ID,
OA_FLEX_NUM = P_FLEX_NUM,
OA_CONCAT_FLEX = 'N',
VAL_OBJ_NAME = 'oracle.apps.bne.integrator.validators.BneKFFValidator',
VAL_COMPONENT_APP_ID = P_APPLICATION_ID,
VAL_COMPONENT_CODE = VV_COMPONENT_CODE,
LOV_TYPE = 'NONE',
OFFLINE_LOV_ENABLED_FLAG = 'N',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = 0
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND INTERFACE_COL_NAME = VV_INTERFACE_COL_NAME;
UPDATE BNE_INTERFACE_COLS_TL
SET
USER_HINT = TRIM(P_USER_HINT),
PROMPT_LEFT = TRIM(P_PROMPT_LEFT),
PROMPT_ABOVE = TRIM(P_PROMPT_ABOVE),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = 0,
SOURCE_LANG = USERENV('LANG')
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE
AND SEQUENCE_NUM = VV_SEQUENCE_NUM
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
SELECT MAX(SEQUENCE_NUM)+1, MAX(DISPLAY_ORDER) + 10
INTO VV_SEQUENCE_NUM, VV_DISPLAY_ORDER
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = VV_INTERFACE_CODE;
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_lov_type => 'NONE',
x_offline_lov_enabled_flag => 'N',
x_custom_mode => NULL,
x_variable_data_type_class => NULL,
x_viewer_group => NULL,
x_edit_type => NULL,
x_val_query_asn => NULL,
x_val_query_code => NULL,
x_expanded_sql_query_asn => NULL,
x_expanded_sql_query_code => NULL,
x_display_width => NULL
);
x_last_update_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
x_custom_mode => NULL
);
FUNCTION DELETE_INTEGRATOR
(P_APPLICATION_ID IN NUMBER,
P_INTEGRATOR_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_INTEGRATORS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
SELECT IMPORT_PARAM_LIST_APP_ID, IMPORT_PARAM_LIST_CODE
BULK COLLECT
INTO VV_IMPORT_PROG_LIST_KEYS
FROM BNE_IMPORT_PROGRAMS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
SELECT
UPLOAD_PARAM_LIST_APP_ID, UPLOAD_PARAM_LIST_CODE,
UPLOAD_SERV_PARAM_LIST_APP_ID, UPLOAD_SERV_PARAM_LIST_CODE,
IMPORT_PARAM_LIST_APP_ID, IMPORT_PARAM_LIST_CODE,
CREATE_DOC_LIST_APP_ID, CREATE_DOC_LIST_CODE,
SESSION_PARAM_LIST_APP_ID, SESSION_PARAM_LIST_CODE
INTO
VV_UPLOAD_LIST_APP_ID, VV_UPLOAD_LIST_CODE,
VV_UPLOAD_SERV_LIST_APP_ID, VV_UPLOAD_SERV_LIST_CODE,
VV_IMPORT_LIST_APP_ID, VV_IMPORT_LIST_CODE,
VV_CREATE_DOC_LIST_APP_ID, VV_CREATE_DOC_LIST_CODE,
VV_SESSION_LIST_APP_ID, VV_SESSION_LIST_CODE
FROM BNE_INTEGRATORS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
VV_DUMMY := DELETE_ALL_MAPPINGS(P_APPLICATION_ID, P_INTEGRATOR_CODE);
VV_DUMMY := DELETE_ALL_CONTENTS(P_APPLICATION_ID, P_INTEGRATOR_CODE);
VV_DUMMY := DELETE_ALL_LAYOUTS(P_APPLICATION_ID, P_INTEGRATOR_CODE);
VV_DUMMY := DELETE_ALL_INTERFACES(P_APPLICATION_ID, P_INTEGRATOR_CODE);
VV_DUMMY := DELETE_ALL_DUP_PROFILES(P_APPLICATION_ID, P_INTEGRATOR_CODE);
DELETE FROM BNE_INTEGRATORS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
DELETE FROM BNE_INTEGRATORS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
DELETE FROM BNE_GRAPHS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
DELETE FROM BNE_GRAPHS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
DELETE FROM BNE_GRAPH_COLUMNS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
DELETE FROM BNE_IMPORT_PROGRAMS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE;
SELECT SECURITY_RULE_APP_ID, SECURITY_RULE_CODE
INTO
VV_SECURITY_RULE_APP_ID, VV_SECURITY_RULE_CODE
FROM BNE_SECURED_OBJECTS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND OBJECT_CODE = P_INTEGRATOR_CODE
AND OBJECT_TYPE = 'INTEGRATOR';
DELETE FROM BNE_SECURED_OBJECTS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND OBJECT_CODE = P_INTEGRATOR_CODE
AND OBJECT_TYPE = 'INTEGRATOR';
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_SECURED_OBJECTS
WHERE SECURITY_RULE_APP_ID = VV_SECURITY_RULE_APP_ID
AND SECURITY_RULE_CODE = VV_SECURITY_RULE_CODE;
DELETE FROM BNE_SECURITY_RULES
WHERE APPLICATION_ID = VV_SECURITY_RULE_APP_ID
AND SECURITY_CODE = VV_SECURITY_RULE_CODE;
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_IMPORT_PROG_LIST_KEYS(I).APP_ID, VV_IMPORT_PROG_LIST_KEYS(I).CODE);
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_UPLOAD_LIST_APP_ID,
VV_UPLOAD_LIST_CODE);
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_UPLOAD_SERV_LIST_APP_ID,
VV_UPLOAD_SERV_LIST_CODE);
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_IMPORT_LIST_APP_ID,
VV_IMPORT_LIST_CODE);
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_CREATE_DOC_LIST_APP_ID,
VV_CREATE_DOC_LIST_CODE);
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_SESSION_LIST_APP_ID,
VV_SESSION_LIST_CODE);
END DELETE_INTEGRATOR;
FUNCTION DELETE_ALL_INTEGRATORS
(P_APPLICATION_ID IN NUMBER)
RETURN NUMBER
IS
VV_COUNT NUMBER;
FOR I IN (SELECT INTEGRATOR_CODE
FROM BNE_INTEGRATORS_B
WHERE APPLICATION_ID = P_APPLICATION_ID)
LOOP
VV_COUNT := VV_COUNT + DELETE_INTEGRATOR(P_APPLICATION_ID, I.INTEGRATOR_CODE);
END DELETE_ALL_INTEGRATORS;
FUNCTION DELETE_INTERFACE_COL
(P_APPLICATION_ID IN NUMBER,
P_INTERFACE_CODE IN VARCHAR2,
P_SEQUENCE_NUM IN NUMBER)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM;
SELECT VAL_COMPONENT_APP_ID, VAL_COMPONENT_CODE,
VAL_QUERY_APP_ID, VAL_QUERY_CODE,
EXPANDED_SQL_QUERY_APP_ID, EXPANDED_SQL_QUERY_CODE
INTO VV_COMPONENT_APP_ID, VV_COMPONENT_CODE,
VV_QUERY_APP_ID, VV_QUERY_CODE,
VV_EXP_QUERY_APP_ID, VV_EXP_QUERY_CODE
FROM BNE_INTERFACE_COLS_B C
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM;
DELETE FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM;
DELETE FROM BNE_INTERFACE_COLS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM;
VV_DUMMY := DELETE_COMPONENT_IF_UNREF(VV_COMPONENT_APP_ID, VV_COMPONENT_CODE);
VV_DUMMY := DELETE_QUERY_IF_UNREF(VV_QUERY_APP_ID, VV_QUERY_CODE);
VV_DUMMY := DELETE_QUERY_IF_UNREF(VV_EXP_QUERY_APP_ID, VV_EXP_QUERY_CODE);
END DELETE_INTERFACE_COL;
FUNCTION DELETE_INTERFACE
(P_APPLICATION_ID IN NUMBER,
P_INTERFACE_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
SELECT
UPLOAD_PARAM_LIST_APP_ID, UPLOAD_PARAM_LIST_CODE
INTO
VV_UPLOAD_LIST_APP_ID, VV_UPLOAD_LIST_CODE
FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
DELETE FROM BNE_INTERFACES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
DELETE FROM BNE_INTERFACES_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
DELETE FROM BNE_DUP_INTERFACE_COLS
WHERE INTERFACE_APP_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
DELETE FROM BNE_DUP_INTERFACE_PROFILES
WHERE INTERFACE_APP_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
DELETE FROM BNE_INTERFACE_KEY_COLS
WHERE INTERFACE_APP_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
DELETE FROM BNE_INTERFACE_KEYS
WHERE INTERFACE_APP_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE;
FOR I IN (SELECT SEQUENCE_NUM
FROM BNE_INTERFACE_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE)
LOOP
VV_DUMMY := DELETE_INTERFACE_COL(P_APPLICATION_ID, P_INTERFACE_CODE, I.SEQUENCE_NUM);
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_UPLOAD_LIST_APP_ID,
VV_UPLOAD_LIST_CODE);
END DELETE_INTERFACE;
FUNCTION DELETE_ALL_INTERFACES
(P_APPLICATION_ID IN NUMBER,
P_INTEGRATOR_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
FOR I IN (SELECT APPLICATION_ID, INTERFACE_CODE
FROM BNE_INTERFACES_B
WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
LOOP
VV_COUNT := VV_COUNT + DELETE_INTERFACE(I.APPLICATION_ID, I.INTERFACE_CODE);
END DELETE_ALL_INTERFACES;
FUNCTION DELETE_ALL_DUP_PROFILES
(P_APPLICATION_ID IN NUMBER,
P_INTEGRATOR_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
FOR I IN (SELECT APPLICATION_ID, DUP_PROFILE_CODE
FROM BNE_DUPLICATE_PROFILES_B
WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
LOOP
VV_COUNT := VV_COUNT + DELETE_DUP_PROFILE(I.APPLICATION_ID, I.DUP_PROFILE_CODE);
END DELETE_ALL_DUP_PROFILES;
FUNCTION DELETE_DUP_PROFILE
(P_APPLICATION_ID IN NUMBER,
P_DUP_PROFILE_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_DUPLICATE_PROFILES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND DUP_PROFILE_CODE = P_DUP_PROFILE_CODE;
DELETE FROM BNE_DUPLICATE_PROFILES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND DUP_PROFILE_CODE = P_DUP_PROFILE_CODE;
DELETE FROM BNE_DUPLICATE_PROFILES_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND DUP_PROFILE_CODE = P_DUP_PROFILE_CODE;
END DELETE_DUP_PROFILE;
FUNCTION DELETE_CONTENT
(P_APPLICATION_ID IN NUMBER,
P_CONTENT_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_CONTENTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE;
SELECT QUERY_APP_ID, QUERY_CODE
BULK COLLECT
INTO VV_QUERY_KEYS
FROM BNE_STORED_SQL C
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE
AND QUERY_APP_ID IS NOT NULL
AND QUERY_CODE IS NOT NULL;
DELETE FROM BNE_CONTENTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE;
DELETE FROM BNE_CONTENTS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE;
DELETE FROM BNE_STORED_SQL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE;
DELETE FROM BNE_CONTENT_COLS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE;
DELETE FROM BNE_CONTENT_COLS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE;
DELETE FROM BNE_FILES
WHERE APPLICATION_ID = P_APPLICATION_ID
AND CONTENT_CODE = P_CONTENT_CODE;
VV_DUMMY := DELETE_QUERY(VV_QUERY_KEYS(I).APP_ID, VV_QUERY_KEYS(I).CODE);
END DELETE_CONTENT;
FUNCTION DELETE_ALL_CONTENTS
(P_APPLICATION_ID IN NUMBER,
P_INTEGRATOR_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
FOR I IN (SELECT APPLICATION_ID, CONTENT_CODE
FROM BNE_CONTENTS_B
WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
LOOP
VV_COUNT := VV_COUNT + DELETE_CONTENT(I.APPLICATION_ID, I.CONTENT_CODE);
END DELETE_ALL_CONTENTS;
FUNCTION DELETE_MAPPING
(P_APPLICATION_ID IN NUMBER,
P_MAPPING_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_MAPPINGS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND MAPPING_CODE = P_MAPPING_CODE;
DELETE FROM BNE_MAPPINGS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND MAPPING_CODE = P_MAPPING_CODE;
DELETE FROM BNE_MAPPINGS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND MAPPING_CODE = P_MAPPING_CODE;
DELETE FROM BNE_MAPPING_LINES
WHERE APPLICATION_ID = P_APPLICATION_ID
AND MAPPING_CODE = P_MAPPING_CODE;
END DELETE_MAPPING;
FUNCTION DELETE_ALL_MAPPINGS
(P_APPLICATION_ID IN NUMBER,
P_INTEGRATOR_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
FOR I IN (SELECT APPLICATION_ID, MAPPING_CODE
FROM BNE_MAPPINGS_B
WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
LOOP
VV_COUNT := VV_COUNT + DELETE_MAPPING(I.APPLICATION_ID, I.MAPPING_CODE);
END DELETE_ALL_MAPPINGS;
FUNCTION DELETE_ALL_LAYOUTS
(P_APPLICATION_ID IN NUMBER,
P_INTEGRATOR_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
FOR I IN (SELECT APPLICATION_ID, LAYOUT_CODE
FROM BNE_LAYOUTS_B
WHERE INTEGRATOR_APP_ID = P_APPLICATION_ID
AND INTEGRATOR_CODE = P_INTEGRATOR_CODE)
LOOP
VV_COUNT := VV_COUNT + DELETE_LAYOUT(I.APPLICATION_ID, I.LAYOUT_CODE);
END DELETE_ALL_LAYOUTS;
FUNCTION DELETE_LAYOUT
(P_APPLICATION_ID IN NUMBER,
P_LAYOUT_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_LAYOUTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE FROM BNE_LAYOUTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE FROM BNE_LAYOUTS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE FROM BNE_LAYOUT_BLOCKS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE FROM BNE_LAYOUT_BLOCKS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE FROM BNE_LAYOUT_COLS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
DELETE FROM BNE_LAYOUT_LOBS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE;
FOR I IN (SELECT APPLICATION_ID, INTEGRATOR_CODE, SEQUENCE_NUM
FROM BNE_GRAPHS_B
WHERE LAYOUT_APP_ID = P_APPLICATION_ID
AND LAYOUT_CODE = P_LAYOUT_CODE)
LOOP
DELETE FROM BNE_GRAPHS_B
WHERE APPLICATION_ID = I.APPLICATION_ID
AND INTEGRATOR_CODE = I.INTEGRATOR_CODE
AND SEQUENCE_NUM = I.SEQUENCE_NUM;
DELETE FROM BNE_GRAPHS_TL
WHERE APPLICATION_ID = I.APPLICATION_ID
AND INTEGRATOR_CODE = I.INTEGRATOR_CODE
AND SEQUENCE_NUM = I.SEQUENCE_NUM;
DELETE FROM BNE_GRAPH_COLUMNS
WHERE APPLICATION_ID = I.APPLICATION_ID
AND INTEGRATOR_CODE = I.INTEGRATOR_CODE
AND GRAPH_SEQ_NUM = I.SEQUENCE_NUM;
END DELETE_LAYOUT;
FUNCTION DELETE_COMPONENT
(P_APPLICATION_ID IN NUMBER,
P_COMPONENT_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_DUMMY NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_COMPONENTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND COMPONENT_CODE = P_COMPONENT_CODE;
SELECT PARAM_LIST_APP_ID, PARAM_LIST_CODE
INTO VV_LIST_APP_ID, VV_LIST_CODE
FROM BNE_COMPONENTS_B I
WHERE APPLICATION_ID = P_APPLICATION_ID
AND COMPONENT_CODE = P_COMPONENT_CODE;
DELETE FROM BNE_COMPONENTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND COMPONENT_CODE = P_COMPONENT_CODE;
DELETE FROM BNE_COMPONENTS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND COMPONENT_CODE = P_COMPONENT_CODE;
VV_DUMMY := DELETE_PARAM_LIST_IF_UNREF(VV_LIST_APP_ID, VV_LIST_CODE);
END DELETE_COMPONENT;
FUNCTION DELETE_PARAM_LIST
(P_APPLICATION_ID IN NUMBER,
P_PARAM_LIST_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_PARAM_LISTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
BULK COLLECT
INTO VV_ATTRIBUTE_KEYS
FROM (SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
FROM BNE_PARAM_LISTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
AND ATTRIBUTE_APP_ID IS NOT NULL
AND ATTRIBUTE_CODE IS NOT NULL
UNION
SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
FROM BNE_PARAM_GROUPS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
AND ATTRIBUTE_APP_ID IS NOT NULL
AND ATTRIBUTE_CODE IS NOT NULL
UNION
SELECT ATTRIBUTE_APP_ID, ATTRIBUTE_CODE
FROM BNE_PARAM_LIST_ITEMS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
AND ATTRIBUTE_APP_ID IS NOT NULL
AND ATTRIBUTE_CODE IS NOT NULL
UNION
SELECT D.ATTRIBUTE_APP_ID, D.ATTRIBUTE_CODE
FROM BNE_PARAM_LIST_ITEMS I, BNE_PARAM_DEFNS_B D
WHERE I.APPLICATION_ID = P_APPLICATION_ID
AND I.PARAM_LIST_CODE = P_PARAM_LIST_CODE
AND I.PARAM_DEFN_APP_ID = D.APPLICATION_ID
AND I.PARAM_DEFN_CODE = D.PARAM_DEFN_CODE
AND D.ATTRIBUTE_APP_ID IS NOT NULL
AND D.ATTRIBUTE_CODE IS NOT NULL
);
SELECT PARAM_DEFN_APP_ID, PARAM_DEFN_CODE
BULK COLLECT
INTO VV_DEFN_KEYS
FROM BNE_PARAM_LIST_ITEMS I
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE
AND PARAM_DEFN_APP_ID IS NOT NULL
AND PARAM_DEFN_CODE IS NOT NULL;
DELETE FROM BNE_PARAM_LISTS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
DELETE FROM BNE_PARAM_LISTS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
DELETE FROM BNE_PARAM_LIST_ITEMS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
DELETE FROM BNE_PARAM_GROUPS_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
DELETE FROM BNE_PARAM_GROUPS_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
DELETE FROM BNE_PARAM_GROUP_ITEMS
WHERE APPLICATION_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_PARAM_LIST_ITEMS
WHERE PARAM_DEFN_APP_ID = VV_DEFN_KEYS(I).APP_ID
AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
SELECT
BNE_LCT_TOOLS_PKG.GET_APP_ID(VAL_VALUE),
SUBSTRB(BNE_LCT_TOOLS_PKG.GET_CODE(VAL_VALUE),1,30)
INTO VV_QUERY_APP_ID, VV_QUERY_CODE
FROM BNE_PARAM_DEFNS_B
WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE
AND VAL_TYPE = 4
AND VAL_VALUE IS NOT NULL;
DELETE FROM BNE_PARAM_DEFNS_B
WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
DELETE FROM BNE_PARAM_DEFNS_TL
WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
DELETE FROM BNE_PARAM_OVERRIDES
WHERE APPLICATION_ID = VV_DEFN_KEYS(I).APP_ID
AND PARAM_DEFN_CODE = VV_DEFN_KEYS(I).CODE;
VV_DUMMY := DELETE_QUERY_IF_UNREF(VV_QUERY_APP_ID, VV_QUERY_CODE);
DELETE FROM BNE_ATTRIBUTES A
WHERE APPLICATION_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE
AND NOT EXISTS (SELECT 1 FROM BNE_PARAM_LISTS_B
WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
AND NOT EXISTS (SELECT 1
FROM BNE_PARAM_GROUPS_B
WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
AND NOT EXISTS (SELECT 1
FROM BNE_PARAM_LIST_ITEMS
WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
AND NOT EXISTS (SELECT 1
FROM BNE_PARAM_DEFNS_B
WHERE ATTRIBUTE_APP_ID = VV_ATTRIBUTE_KEYS(I).APP_ID
AND ATTRIBUTE_CODE = VV_ATTRIBUTE_KEYS(I).CODE)
;
END DELETE_PARAM_LIST;
FUNCTION DELETE_QUERY
(P_APPLICATION_ID IN NUMBER,
P_QUERY_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_COUNT
FROM BNE_QUERIES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND QUERY_CODE = P_QUERY_CODE;
DELETE FROM BNE_QUERIES_B
WHERE APPLICATION_ID = P_APPLICATION_ID
AND QUERY_CODE = P_QUERY_CODE;
DELETE FROM BNE_QUERIES_TL
WHERE APPLICATION_ID = P_APPLICATION_ID
AND QUERY_CODE = P_QUERY_CODE;
DELETE FROM BNE_SIMPLE_QUERY
WHERE APPLICATION_ID = P_APPLICATION_ID
AND QUERY_CODE = P_QUERY_CODE;
DELETE FROM BNE_RAW_QUERY
WHERE APPLICATION_ID = P_APPLICATION_ID
AND QUERY_CODE = P_QUERY_CODE;
END DELETE_QUERY;
FUNCTION DELETE_COMPONENT_IF_UNREF
(P_APPLICATION_ID IN NUMBER,
P_COMPONENT_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE VAL_COMPONENT_APP_ID = P_APPLICATION_ID
AND VAL_COMPONENT_CODE = P_COMPONENT_CODE;
VV_COUNT := DELETE_COMPONENT(P_APPLICATION_ID, P_COMPONENT_CODE);
END DELETE_COMPONENT_IF_UNREF;
FUNCTION DELETE_QUERY_IF_UNREF
(P_APPLICATION_ID IN NUMBER,
P_QUERY_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE VAL_QUERY_APP_ID = P_APPLICATION_ID
AND VAL_QUERY_CODE = P_QUERY_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACE_COLS_B
WHERE EXPANDED_SQL_QUERY_APP_ID = P_APPLICATION_ID
AND EXPANDED_SQL_QUERY_CODE = P_QUERY_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_STORED_SQL
WHERE QUERY_APP_ID = P_APPLICATION_ID
AND QUERY_CODE = P_QUERY_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_PARAM_DEFNS_B
WHERE VAL_TYPE = 4
AND (VAL_VALUE = TO_CHAR(P_APPLICATION_ID)||P_QUERY_CODE OR
VAL_VALUE = BNE_LCT_TOOLS_PKG.APP_ID_TO_ASN(P_APPLICATION_ID)||P_QUERY_CODE);
VV_COUNT := DELETE_QUERY(P_APPLICATION_ID, P_QUERY_CODE);
END DELETE_QUERY_IF_UNREF;
FUNCTION DELETE_PARAM_LIST_IF_UNREF
(P_APPLICATION_ID IN NUMBER,
P_PARAM_LIST_CODE IN VARCHAR2)
RETURN NUMBER
IS
VV_COUNT NUMBER;
VV_SUB_LIST_DELETED NUMBER;
VV_SUB_LIST_DELETED := 0;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTEGRATORS_B
WHERE UPLOAD_PARAM_LIST_APP_ID = P_APPLICATION_ID
AND UPLOAD_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTEGRATORS_B
WHERE UPLOAD_SERV_PARAM_LIST_APP_ID = P_APPLICATION_ID
AND UPLOAD_SERV_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTEGRATORS_B
WHERE IMPORT_PARAM_LIST_APP_ID = P_APPLICATION_ID
AND IMPORT_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTEGRATORS_B
WHERE CREATE_DOC_LIST_APP_ID = P_APPLICATION_ID
AND CREATE_DOC_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTEGRATORS_B
WHERE SESSION_PARAM_LIST_APP_ID = P_APPLICATION_ID
AND SESSION_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_INTERFACES_B
WHERE UPLOAD_PARAM_LIST_APP_ID = P_APPLICATION_ID
AND UPLOAD_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_COMPONENTS_B
WHERE PARAM_LIST_APP_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_IMPORT_PROGRAMS
WHERE IMPORT_PARAM_LIST_APP_ID = P_APPLICATION_ID
AND IMPORT_PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_LAYOUTS_B
WHERE CREATE_DOC_LIST_APP_ID = P_APPLICATION_ID
AND CREATE_DOC_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_CONTENTS_B
WHERE PARAM_LIST_APP_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_VIEWERS_B
WHERE PARAM_LIST_APP_ID = P_APPLICATION_ID
AND PARAM_LIST_CODE = P_PARAM_LIST_CODE;
SELECT COUNT(*)
INTO VV_DUMMY
FROM BNE_VIEWERS_B
WHERE CREATE_DOC_LIST_APP_ID = P_APPLICATION_ID
AND CREATE_DOC_LIST_CODE = P_PARAM_LIST_CODE;
SELECT APPLICATION_ID, PARAM_LIST_CODE
BULK COLLECT
INTO VV_LIST_KEYS
FROM bne_param_lists_b
WHERE application_id
|| ':'
|| param_list_code IN
(SELECT String_value
FROM bne_param_list_items
WHERE (application_id = P_APPLICATION_ID and param_list_code = P_PARAM_LIST_CODE)
AND String_value is not null
AND substr(string_value, 4, 1) = ':'
);
VV_SUB_LIST_DELETED := VV_SUB_LIST_DELETED +
DELETE_PARAM_LIST_IF_UNREF(VV_LIST_KEYS(I).APP_ID, VV_LIST_KEYS(I).CODE);
IF (VV_SUB_LIST_DELETED = VV_LIST_KEYS.COUNT) THEN
VV_COUNT := DELETE_PARAM_LIST(P_APPLICATION_ID, P_PARAM_LIST_CODE);
END DELETE_PARAM_LIST_IF_UNREF;
PROCEDURE UPDATE_INTERFACE_COLUMN_TEXT
(P_APPLICATION_ID IN NUMBER, P_INTERFACE_CODE IN VARCHAR2,
P_SEQUENCE_NUM IN NUMBER, P_LANGUAGE IN VARCHAR2,
P_SOURCE_LANG IN VARCHAR2, P_PROMPT_LEFT IN VARCHAR2,
P_PROMPT_ABOVE IN VARCHAR2, P_USER_HINT IN VARCHAR2,
P_USER_HELP_TEXT IN VARCHAR2, P_USER_ID IN NUMBER)
IS
BEGIN
-- Update the required row in BNE_INTERFACE_COLS_TL only where P_LANGUAGE is populated
IF (P_LANGUAGE IS NOT NULL) THEN
UPDATE BNE_INTERFACE_COLS_TL
SET USER_HINT = P_USER_HINT,
PROMPT_LEFT = P_PROMPT_LEFT,
USER_HELP_TEXT = P_USER_HELP_TEXT,
PROMPT_ABOVE = P_PROMPT_ABOVE,
LAST_UPDATED_BY = P_USER_ID,
LAST_UPDATE_LOGIN = P_USER_ID,
LAST_UPDATE_DATE = SYSDATE
WHERE APPLICATION_ID = P_APPLICATION_ID
AND INTERFACE_CODE = P_INTERFACE_CODE
AND SEQUENCE_NUM = P_SEQUENCE_NUM
AND LANGUAGE = P_LANGUAGE
AND SOURCE_LANG = P_SOURCE_LANG;
END UPDATE_INTERFACE_COLUMN_TEXT;