The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_RULE_OBJECT_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_RULE_OBJECT_NAME IN VARCHAR2,
X_RESULT_TYPE IN VARCHAR2,
X_REQUIRED_FLAG IN VARCHAR2,
X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
X_DEFAULT_APPLICATION_ID IN NUMBER,
X_DEFAULT_VALUE IN VARCHAR2,
X_FLEX_VALUE_SET_ID IN NUMBER,
X_FLEXFIELD_NAME IN VARCHAR2,
X_FLEXFIELD_APP_SHORT_NAME IN VARCHAR2,
X_MULTI_RULE_RESULT_FLAG IN VARCHAR2,
X_CREATED_BY_MODULE IN VARCHAR2,
X_USER_RULE_OBJECT_NAME IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
X_ORG_ID IN NUMBER DEFAULT NULL,
X_CREATION_DATE IN DATE DEFAULT NULL,
X_CREATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
) IS
l_roa_rowid varchar2(64);
select FUN_RULE_OBJECTS_S.NEXTVAL into l_seq_val from dual;
INSERT_ROW(X_ROWID,
NVL(X_RULE_OBJECT_ID,l_seq_val),
X_APPLICATION_ID,
X_RULE_OBJECT_NAME,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
X_FLEX_VALUE_SET_ID,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
X_CREATED_BY_MODULE,
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
NVL(X_USE_INSTANCE_FLAG,'N'), --override internally to N
X_INSTANCE_LABEL,
X_PARENT_RULE_OBJECT_ID,
X_ORG_ID,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
INSERT_ROW(l_roa_rowid,
NVL(X_RULE_OBJECT_ID,l_seq_val),
X_APPLICATION_ID,
X_RULE_OBJECT_NAME,
X_DEFAULT_APPLICATION_ID,
X_DEFAULT_VALUE,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
end INSERT_ROW;
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_RULE_OBJECT_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_RULE_OBJECT_NAME IN VARCHAR2,
X_RESULT_TYPE IN VARCHAR2,
X_REQUIRED_FLAG IN VARCHAR2,
X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
X_FLEX_VALUE_SET_ID IN NUMBER,
X_FLEXFIELD_NAME IN VARCHAR2,
X_FLEXFIELD_APP_SHORT_NAME IN VARCHAR2,
X_MULTI_RULE_RESULT_FLAG IN VARCHAR2,
X_CREATED_BY_MODULE IN VARCHAR2,
X_USER_RULE_OBJECT_NAME IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
X_ORG_ID IN NUMBER DEFAULT NULL,
X_CREATION_DATE IN DATE DEFAULT NULL,
X_CREATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
) IS
cursor C(id number) is select ROWID from FUN_RULE_OBJECTS_B
where APPLICATION_ID = X_APPLICATION_ID
and RULE_OBJECT_ID = id
;
SELECT RULE_OBJECT_ID INTO l_parent_rule_object_id
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND UPPER(USE_INSTANCE_FLAG) = 'Y'
AND INSTANCE_LABEL IS NULL
AND ORG_ID IS NULL
AND PARENT_RULE_OBJECT_ID IS NULL;
insert into FUN_RULE_OBJECTS_B (
RULE_OBJECT_ID,
APPLICATION_ID,
RULE_OBJECT_NAME,
RESULT_TYPE,
REQUIRED_FLAG,
USE_DEFAULT_VALUE_FLAG,
FLEX_VALUE_SET_ID,
FLEXFIELD_NAME,
FLEXFIELD_APP_SHORT_NAME,
MULTI_RULE_RESULT_FLAG,
OBJECT_VERSION_NUMBER,
USE_INSTANCE_FLAG,
INSTANCE_LABEL,
PARENT_RULE_OBJECT_ID,
ORG_ID,
CREATED_BY_MODULE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) values (
X_RULE_OBJECT_ID,
X_APPLICATION_ID,
X_RULE_OBJECT_NAME,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
X_FLEX_VALUE_SET_ID,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
1,
NVL(X_USE_INSTANCE_FLAG,'N'), --override internally to N
X_INSTANCE_LABEL,
NVL(X_PARENT_RULE_OBJECT_ID,l_parent_rule_object_id),
X_ORG_ID,
X_CREATED_BY_MODULE,
NVL(X_CREATED_BY,FUN_RULE_UTILITY_PKG.CREATED_BY),
NVL(X_CREATION_DATE,FUN_RULE_UTILITY_PKG.CREATION_DATE),
NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY)
)RETURNING ROWID INTO X_ROWID;
insert into FUN_RULE_OBJECTS_TL (
RULE_OBJECT_ID,
USER_RULE_OBJECT_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LANGUAGE,
SOURCE_LANG
) select
X_RULE_OBJECT_ID,
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
NVL(X_CREATED_BY,FUN_RULE_UTILITY_PKG.CREATED_BY),
NVL(X_CREATION_DATE,FUN_RULE_UTILITY_PKG.CREATION_DATE),
NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from FUN_RULE_OBJECTS_TL T
where T.RULE_OBJECT_ID = X_RULE_OBJECT_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
END INSERT_ROW;
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_RULE_OBJECT_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_RULE_OBJECT_NAME IN VARCHAR2,
X_DEFAULT_APPLICATION_ID IN NUMBER,
X_DEFAULT_VALUE IN VARCHAR2,
X_CREATION_DATE IN DATE DEFAULT NULL,
X_CREATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
) IS
cursor C(id number) is select ROWID from FUN_RULE_OBJ_ATTRIBUTES
where RULE_OBJECT_ID = id
;
insert into FUN_RULE_OBJ_ATTRIBUTES (
RULE_OBJECT_ID,
DEFAULT_APPLICATION_ID,
DEFAULT_VALUE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
values (
X_RULE_OBJECT_ID,
X_DEFAULT_APPLICATION_ID,
X_DEFAULT_VALUE,
NVL(X_CREATED_BY,FUN_RULE_UTILITY_PKG.CREATED_BY),
NVL(X_CREATION_DATE,FUN_RULE_UTILITY_PKG.CREATION_DATE),
NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY)
);
END INSERT_ROW;
cursor c is select
OBJECT_VERSION_NUMBER
from FUN_RULE_OBJECTS_B
where RULE_OBJECT_ID = X_RULE_OBJECT_ID
for update of RULE_OBJECT_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE UPDATE_ROW (
X_RULE_OBJECT_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_RULE_OBJECT_NAME IN VARCHAR2,
X_RESULT_TYPE IN VARCHAR2,
X_REQUIRED_FLAG IN VARCHAR2,
X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
X_DEFAULT_APPLICATION_ID IN NUMBER,
X_DEFAULT_VALUE IN VARCHAR2,
X_FLEX_VALUE_SET_ID IN NUMBER,
X_FLEXFIELD_NAME IN VARCHAR2,
X_FLEXFIELD_APP_SHORT_NAME IN VARCHAR2,
X_MULTI_RULE_RESULT_FLAG IN VARCHAR2,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_CREATED_BY_MODULE IN VARCHAR2,
X_USER_RULE_OBJECT_NAME IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
X_ORG_ID IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
) IS
begin
update_row(X_APPLICATION_ID,
X_RULE_OBJECT_ID,
X_RULE_OBJECT_NAME,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
X_FLEX_VALUE_SET_ID,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
X_CREATED_BY_MODULE,
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
X_USE_INSTANCE_FLAG,
X_INSTANCE_LABEL,
X_PARENT_RULE_OBJECT_ID,
X_ORG_ID,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
update_row(X_APPLICATION_ID,
X_RULE_OBJECT_ID,
X_DEFAULT_APPLICATION_ID,
X_DEFAULT_VALUE,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
end UPDATE_ROW;
procedure UPDATE_ROW (
X_APPLICATION_ID in NUMBER,
X_RULE_OBJECT_ID in NUMBER,
X_RULE_OBJECT_NAME in VARCHAR2,
X_RESULT_TYPE in VARCHAR2,
X_REQUIRED_FLAG in VARCHAR2,
X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
X_FLEX_VALUE_SET_ID in NUMBER,
X_FLEXFIELD_NAME in VARCHAR2,
X_FLEXFIELD_APP_SHORT_NAME in VARCHAR2,
X_MULTI_RULE_RESULT_FLAG in VARCHAR2,
X_CREATED_BY_MODULE in VARCHAR2,
X_USER_RULE_OBJECT_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
X_INSTANCE_LABEL IN VARCHAR2 DEFAULT NULL,
X_PARENT_RULE_OBJECT_ID IN NUMBER DEFAULT NULL,
X_ORG_ID IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
) is
begin
update FUN_RULE_OBJECTS_B set
RULE_OBJECT_NAME = X_RULE_OBJECT_NAME,
RESULT_TYPE = X_RESULT_TYPE,
REQUIRED_FLAG = X_REQUIRED_FLAG,
USE_DEFAULT_VALUE_FLAG = X_USE_DEFAULT_VALUE_FLAG,
FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID,
FLEXFIELD_NAME = X_FLEXFIELD_NAME,
FLEXFIELD_APP_SHORT_NAME = X_FLEXFIELD_APP_SHORT_NAME,
MULTI_RULE_RESULT_FLAG = X_MULTI_RULE_RESULT_FLAG,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
CREATED_BY_MODULE = X_CREATED_BY_MODULE,
USE_INSTANCE_FLAG = NVL(X_USE_INSTANCE_FLAG,'N'),
INSTANCE_LABEL = X_INSTANCE_LABEL,
PARENT_RULE_OBJECT_ID = X_PARENT_RULE_OBJECT_ID,
ORG_ID = X_ORG_ID,
LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN)
where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
update FUN_RULE_OBJECTS_TL set
USER_RULE_OBJECT_NAME = X_USER_RULE_OBJECT_NAME,
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN),
SOURCE_LANG = userenv('LANG')
where RULE_OBJECT_ID = X_RULE_OBJECT_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure UPDATE_ROW (
X_APPLICATION_ID in NUMBER,
X_RULE_OBJECT_ID in NUMBER,
X_DEFAULT_APPLICATION_ID in NUMBER,
X_DEFAULT_VALUE in VARCHAR2,
X_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
X_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
X_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
) is
begin
update FUN_RULE_OBJ_ATTRIBUTES set
DEFAULT_APPLICATION_ID = X_DEFAULT_APPLICATION_ID,
DEFAULT_VALUE = X_DEFAULT_VALUE,
LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE),
LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN)
where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
end UPDATE_ROW;
PROCEDURE DELETE_ROW (
X_RULE_OBJECT_NAME IN VARCHAR2,
X_APPLICATION_ID IN NUMBER
) IS
begin
delete from FUN_RULE_OBJ_ATTRIBUTES
where RULE_OBJECT_ID in (select RULE_OBJECT_ID
from FUN_RULE_OBJECTS_B
where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND APPLICATION_ID = X_APPLICATION_ID
);
delete from FUN_RULE_OBJECTS_TL
where RULE_OBJECT_ID in (select RULE_OBJECT_ID
from FUN_RULE_OBJECTS_B
where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND APPLICATION_ID = X_APPLICATION_ID
);
delete from FUN_RULE_OBJECTS_B
where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND APPLICATION_ID = X_APPLICATION_ID;
end DELETE_ROW;
PROCEDURE DELETE_ROW (
X_RULE_OBJECT_NAME IN VARCHAR2,
X_APPLICATION_ID IN NUMBER,
X_INSTANCE_LABEL IN VARCHAR2,
X_ORG_ID IN NUMBER
) IS
begin
delete from FUN_RULE_OBJECTS_B
where RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND APPLICATION_ID = X_APPLICATION_ID
AND
( (INSTANCE_LABEL IS NULL AND X_INSTANCE_LABEL IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND X_INSTANCE_LABEL IS NOT NULL AND INSTANCE_LABEL = X_INSTANCE_LABEL))
AND
( (ORG_ID IS NULL AND X_ORG_ID IS NULL) OR
(ORG_ID IS NOT NULL AND X_ORG_ID IS NOT NULL AND ORG_ID = X_ORG_ID))
AND PARENT_RULE_OBJECT_ID IS NOT NULL;
end DELETE_ROW;
PROCEDURE DELETE_ROW (
X_RULE_OBJECT_ID IN NUMBER
) IS
begin
delete from FUN_RULE_OBJECTS_TL
where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
delete from FUN_RULE_OBJECTS_B
where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
delete from FUN_RULE_OBJ_ATTRIBUTES
where RULE_OBJECT_ID = X_RULE_OBJECT_ID;
end DELETE_ROW;
PROCEDURE Select_Row (
X_RULE_OBJECT_NAME IN OUT NOCOPY VARCHAR2,
X_RULE_OBJECT_ID OUT NOCOPY NUMBER,
X_APPLICATION_ID IN OUT NOCOPY NUMBER,
X_USER_RULE_OBJECT_NAME OUT NOCOPY VARCHAR2,
X_DESCRIPTION OUT NOCOPY VARCHAR2,
X_RESULT_TYPE OUT NOCOPY VARCHAR2,
X_REQUIRED_FLAG OUT NOCOPY VARCHAR2,
X_USE_DEFAULT_VALUE_FLAG OUT NOCOPY VARCHAR2,
X_DEFAULT_APPLICATION_ID OUT NOCOPY NUMBER,
X_DEFAULT_VALUE OUT NOCOPY VARCHAR2,
X_FLEX_VALUE_SET_ID OUT NOCOPY NUMBER,
X_FLEXFIELD_NAME OUT NOCOPY VARCHAR2,
X_FLEXFIELD_APP_SHORT_NAME OUT NOCOPY VARCHAR2,
X_MULTI_RULE_RESULT_FLAG OUT NOCOPY VARCHAR2,
X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
X_USE_INSTANCE_FLAG OUT NOCOPY VARCHAR2,
X_INSTANCE_LABEL OUT NOCOPY VARCHAR2,
X_PARENT_RULE_OBJECT_ID OUT NOCOPY NUMBER,
X_ORG_ID OUT NOCOPY NUMBER
) IS
l_count NUMBER;
SELECT
RULE_OBJECT_NAME,
RULE_OBJECT_ID,
APPLICATION_ID,
USER_RULE_OBJECT_NAME,
DESCRIPTION,
RESULT_TYPE,
REQUIRED_FLAG,
USE_DEFAULT_VALUE_FLAG,
DEFAULT_APPLICATION_ID,
DEFAULT_VALUE,
FLEX_VALUE_SET_ID,
FLEXFIELD_NAME,
FLEXFIELD_APP_SHORT_NAME,
MULTI_RULE_RESULT_FLAG,
CREATED_BY_MODULE,
USE_INSTANCE_FLAG ,
INSTANCE_LABEL ,
PARENT_RULE_OBJECT_ID ,
ORG_ID
INTO
X_RULE_OBJECT_NAME,
X_RULE_OBJECT_ID,
X_APPLICATION_ID,
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
X_DEFAULT_APPLICATION_ID,
X_DEFAULT_VALUE,
X_FLEX_VALUE_SET_ID,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
X_CREATED_BY_MODULE,
X_USE_INSTANCE_FLAG,
X_INSTANCE_LABEL,
X_PARENT_RULE_OBJECT_ID,
X_ORG_ID
FROM FUN_RULE_OBJECTS_VL
WHERE RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND APPLICATION_ID = X_APPLICATION_ID
AND INSTANCE_LABEL IS NULL
AND ORG_ID IS NULL;
SELECT
RULE_OBJECT_NAME,
RULE_OBJECT_ID,
APPLICATION_ID,
USER_RULE_OBJECT_NAME,
DESCRIPTION,
RESULT_TYPE,
REQUIRED_FLAG,
USE_DEFAULT_VALUE_FLAG,
DEFAULT_APPLICATION_ID,
DEFAULT_VALUE,
FLEX_VALUE_SET_ID,
FLEXFIELD_NAME,
FLEXFIELD_APP_SHORT_NAME,
MULTI_RULE_RESULT_FLAG,
CREATED_BY_MODULE,
USE_INSTANCE_FLAG ,
INSTANCE_LABEL ,
PARENT_RULE_OBJECT_ID ,
ORG_ID
INTO
X_RULE_OBJECT_NAME,
X_RULE_OBJECT_ID,
X_APPLICATION_ID,
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
X_DEFAULT_APPLICATION_ID,
X_DEFAULT_VALUE,
X_FLEX_VALUE_SET_ID,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
X_CREATED_BY_MODULE,
X_USE_INSTANCE_FLAG,
X_INSTANCE_LABEL,
X_PARENT_RULE_OBJECT_ID,
X_ORG_ID
FROM FUN_RULE_OBJECTS_VL
WHERE RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND APPLICATION_ID = X_APPLICATION_ID
AND
( (INSTANCE_LABEL IS NULL AND X_INSTANCE_LABEL IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND X_INSTANCE_LABEL IS NOT NULL AND INSTANCE_LABEL = X_INSTANCE_LABEL))
AND
( (ORG_ID IS NULL AND X_ORG_ID IS NULL) OR
(ORG_ID IS NOT NULL AND X_ORG_ID IS NOT NULL AND ORG_ID = X_ORG_ID))
AND PARENT_RULE_OBJECT_ID IS NOT NULL;
END Select_Row;
/*Overloaded procedure to select Rule Objects record based on the RULE_OBJECT_ID passed*/
PROCEDURE Select_Row_Rob_Id (
X_RULE_OBJECT_NAME OUT NOCOPY VARCHAR2,
X_RULE_OBJECT_ID IN OUT NOCOPY NUMBER,
X_APPLICATION_ID OUT NOCOPY NUMBER,
X_USER_RULE_OBJECT_NAME OUT NOCOPY VARCHAR2,
X_DESCRIPTION OUT NOCOPY VARCHAR2,
X_RESULT_TYPE OUT NOCOPY VARCHAR2,
X_REQUIRED_FLAG OUT NOCOPY VARCHAR2,
X_USE_DEFAULT_VALUE_FLAG OUT NOCOPY VARCHAR2,
X_DEFAULT_APPLICATION_ID OUT NOCOPY NUMBER,
X_DEFAULT_VALUE OUT NOCOPY VARCHAR2,
X_FLEX_VALUE_SET_ID OUT NOCOPY NUMBER,
X_FLEXFIELD_NAME OUT NOCOPY VARCHAR2,
X_FLEXFIELD_APP_SHORT_NAME OUT NOCOPY VARCHAR2,
X_MULTI_RULE_RESULT_FLAG OUT NOCOPY VARCHAR2,
X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
X_USE_INSTANCE_FLAG OUT NOCOPY VARCHAR2,
X_INSTANCE_LABEL OUT NOCOPY VARCHAR2,
X_PARENT_RULE_OBJECT_ID OUT NOCOPY NUMBER,
X_ORG_ID OUT NOCOPY NUMBER
) IS
l_count NUMBER;
SELECT
RULE_OBJECT_NAME,
RULE_OBJECT_ID,
APPLICATION_ID,
USER_RULE_OBJECT_NAME,
DESCRIPTION,
RESULT_TYPE,
REQUIRED_FLAG,
USE_DEFAULT_VALUE_FLAG,
DEFAULT_APPLICATION_ID,
DEFAULT_VALUE,
FLEX_VALUE_SET_ID,
FLEXFIELD_NAME,
FLEXFIELD_APP_SHORT_NAME,
MULTI_RULE_RESULT_FLAG,
CREATED_BY_MODULE,
USE_INSTANCE_FLAG ,
INSTANCE_LABEL ,
PARENT_RULE_OBJECT_ID ,
ORG_ID
INTO
X_RULE_OBJECT_NAME,
X_RULE_OBJECT_ID,
X_APPLICATION_ID,
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
X_DEFAULT_APPLICATION_ID,
X_DEFAULT_VALUE,
X_FLEX_VALUE_SET_ID,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
X_CREATED_BY_MODULE,
X_USE_INSTANCE_FLAG,
X_INSTANCE_LABEL,
X_PARENT_RULE_OBJECT_ID,
X_ORG_ID
FROM FUN_RULE_OBJECTS_VL
WHERE RULE_OBJECT_ID = X_RULE_OBJECT_ID;
END Select_Row_Rob_Id;
delete from FUN_RULE_OBJECTS_TL T
where not exists
(select NULL
from FUN_RULE_OBJECTS_B B
where B.RULE_OBJECT_ID = T.RULE_OBJECT_ID
);
update FUN_RULE_OBJECTS_TL T set (
USER_RULE_OBJECT_NAME,
DESCRIPTION
) = (select
B.USER_RULE_OBJECT_NAME,
B.DESCRIPTION
from FUN_RULE_OBJECTS_TL B
where B.RULE_OBJECT_ID = T.RULE_OBJECT_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.RULE_OBJECT_ID,
T.LANGUAGE
) in (select
SUBT.RULE_OBJECT_ID,
SUBT.LANGUAGE
from FUN_RULE_OBJECTS_TL SUBB, FUN_RULE_OBJECTS_TL SUBT
where SUBB.RULE_OBJECT_ID = SUBT.RULE_OBJECT_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_RULE_OBJECT_NAME <> SUBT.USER_RULE_OBJECT_NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into FUN_RULE_OBJECTS_TL (
RULE_OBJECT_ID,
USER_RULE_OBJECT_NAME,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.RULE_OBJECT_ID,
B.USER_RULE_OBJECT_NAME,
B.DESCRIPTION,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from FUN_RULE_OBJECTS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from FUN_RULE_OBJECTS_TL T
where T.RULE_OBJECT_ID = B.RULE_OBJECT_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE in VARCHAR2
)
IS
appid number;
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT application_id INTO appid
FROM fnd_application
WHERE application_short_name = X_APP_SHORT_NAME;
select RULE_OBJECT_ID
into roid
from FUN_RULE_OBJECTS_B
where APPLICATION_ID = appid
and RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND PARENT_RULE_OBJECT_ID IS NULL;
select last_updated_by, last_update_date
into db_luby, db_ludate
from FUN_RULE_OBJECTS_TL
where rule_object_id = roid
and language = userenv('LANG');
update FUN_RULE_OBJECTS_TL
set user_rule_object_name = nvl(x_user_rule_object_name, user_rule_object_name),
description = nvl(x_description, description),
source_lang = userenv('LANG')
where rule_object_id = roid
and userenv('LANG') in (language, source_lang);
Only if the USE_INSTANCE_FLAG is Y and update mode, then we will propagate
the changes to all the instances. */
procedure LOAD_ROW (
X_APP_SHORT_NAME in VARCHAR2,
X_RULE_OBJECT_NAME in VARCHAR2,
X_RESULT_TYPE in VARCHAR2,
X_REQUIRED_FLAG in VARCHAR2,
X_USE_DEFAULT_VALUE_FLAG IN VARCHAR2,
X_DEFAULT_APP_SHORT_NAME in VARCHAR2,
X_DEFAULT_VALUE in VARCHAR2,
X_FLEX_VALUE_SET_NAME in VARCHAR2,
X_FLEXFIELD_NAME in VARCHAR2,
X_FLEXFIELD_APP_SHORT_NAME in VARCHAR2,
X_MULTI_RULE_RESULT_FLAG in VARCHAR2,
X_USER_RULE_OBJECT_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USE_INSTANCE_FLAG IN VARCHAR2 DEFAULT NULL,
X_OWNER IN VARCHAR2,
X_LAST_UPDATE_DATE IN VARCHAR2,
X_ATT_OWNER IN VARCHAR2,
X_ATT_LAST_UPDATE_DATE IN VARCHAR2,
X_CUSTOM_MODE IN VARCHAR2)
is
begin
LOAD_ROW(X_APP_SHORT_NAME,
X_RULE_OBJECT_NAME,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
X_FLEX_VALUE_SET_NAME,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
NVL(X_USE_INSTANCE_FLAG, 'N'),
X_OWNER,
X_LAST_UPDATE_DATE,
X_CUSTOM_MODE);
X_LAST_UPDATE_DATE,
X_CUSTOM_MODE);
X_LAST_UPDATE_DATE IN VARCHAR2,
X_CUSTOM_MODE IN VARCHAR2)
is
appid number := null;
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
SELECT
B.RULE_OBJECT_ID,
B.USE_INSTANCE_FLAG,
B.INSTANCE_LABEL,
B.PARENT_RULE_OBJECT_ID,
B.ORG_ID
FROM FUN_RULE_OBJECTS_B B
WHERE B.PARENT_RULE_OBJECT_ID = p_rule_object_id;
SELECT application_id INTO appid
FROM fnd_application
WHERE application_short_name = X_APP_SHORT_NAME;
select flex_value_set_id into vsid
from fnd_flex_value_sets
where flex_value_set_name = X_FLEX_VALUE_SET_NAME;
f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
select RULE_OBJECT_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE , USE_INSTANCE_FLAG
into roid, db_luby, db_ludate, l_use_instance_flag
from FUN_RULE_OBJECTS_B
where APPLICATION_ID = appid
and RULE_OBJECT_NAME = X_RULE_OBJECT_NAME
AND PARENT_RULE_OBJECT_ID IS NULL;
app_exception.raise_exception(exception_text=>'Cannot update USE_INSTANCE_FLAG from Y to N. Please use the upgrdae script for this');
/*For Parent Rule Object, dont allow the users to Update the USE_INSTANCE_FLAG.
Also, for these rule objects, INSTANCE_LABEL, PARENT_RULE_OBJECT_ID, ORG_ID
should always be NULL. */
UPDATE_ROW (
appid,
roid,
X_RULE_OBJECT_NAME,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
vsid,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
'ORACLE',
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
l_use_instance_flag,
null, --INSTANCE_LABEL,
null, --PARENT_RULE_OBJECT_ID,
null, --ORG_ID,
f_ludate,
f_luby,
0);
UPDATE_ROW (
appid,
C_REC.RULE_OBJECT_ID,
X_RULE_OBJECT_NAME,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
vsid,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
'ORACLE',
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
C_REC.USE_INSTANCE_FLAG,
C_REC.INSTANCE_LABEL,
C_REC.PARENT_RULE_OBJECT_ID,
C_REC.ORG_ID,
f_ludate,
f_luby,
0);
SELECT fun_rule_objects_s.nextval into roid from dual;
INSERT_ROW (
row_id,
roid,
appid,
X_RULE_OBJECT_NAME,
X_RESULT_TYPE,
X_REQUIRED_FLAG,
X_USE_DEFAULT_VALUE_FLAG,
vsid,
X_FLEXFIELD_NAME,
X_FLEXFIELD_APP_SHORT_NAME,
X_MULTI_RULE_RESULT_FLAG,
'ORACLE',
X_USER_RULE_OBJECT_NAME,
X_DESCRIPTION,
X_USE_INSTANCE_FLAG,
null, --INSTANCE_LABEL
null, --PARENT_RULE_OBJECT_ID
null, --ORG_ID
f_ludate,
f_luby,
f_ludate,
f_luby,
0);
X_LAST_UPDATE_DATE IN VARCHAR2,
X_CUSTOM_MODE IN VARCHAR2)
is
appid number;
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
SELECT application_id INTO appid
FROM fnd_application
WHERE application_short_name = X_APP_SHORT_NAME;
SELECT application_id INTO default_appid
FROM fnd_application
WHERE application_short_name = X_DEFAULT_APP_SHORT_NAME;
f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT RULE_OBJECT_ID
into roid
FROM FUN_RULE_OBJECTS_B
WHERE application_id = appid
AND rule_object_name = X_RULE_OBJECT_NAME
AND parent_rule_object_id IS NULL;
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from FUN_RULE_OBJ_ATTRIBUTES
where RULE_OBJECT_ID = roid;
UPDATE_ROW (
appid,
roid,
default_appid,
X_DEFAULT_VALUE,
f_ludate,
f_luby,
0);
INSERT_ROW (row_id,
roid,
appid,
X_RULE_OBJECT_NAME,
default_appid,
X_DEFAULT_VALUE,
f_ludate,
f_luby,
f_ludate,
f_luby,
0);