The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure VAL_SET_INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_VALIDATION_SET_CODE in VARCHAR2,
X_VALIDATION_LEVEL_CODE in VARCHAR2,
X_VALIDATION_CODE_PACKAGE in VARCHAR2,
X_VALIDATION_CODE_ENTRY_POINT in VARCHAR2,
X_VALIDATION_CODE_LANGUAGE in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_VALIDATION_SET_DISPLAY_NAME in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from IBY_VALIDATION_SETS_B
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
;
insert into IBY_VALIDATION_SETS_B (
VALIDATION_SET_CODE,
VALIDATION_LEVEL_CODE,
VALIDATION_CODE_PACKAGE,
VALIDATION_CODE_ENTRY_POINT,
VALIDATION_CODE_LANGUAGE,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_VALIDATION_SET_CODE,
X_VALIDATION_LEVEL_CODE,
X_VALIDATION_CODE_PACKAGE,
X_VALIDATION_CODE_ENTRY_POINT,
X_VALIDATION_CODE_LANGUAGE,
X_OBJECT_VERSION_NUMBER,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into IBY_VALIDATION_SETS_TL (
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
VALIDATION_SET_CODE,
VALIDATION_SET_DISPLAY_NAME,
LANGUAGE,
SOURCE_LANG
) select
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_OBJECT_VERSION_NUMBER,
X_VALIDATION_SET_CODE,
X_VALIDATION_SET_DISPLAY_NAME,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from IBY_VALIDATION_SETS_TL T
where T.VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
end VAL_SET_INSERT_ROW;
procedure VAL_SET_UPDATE_ROW (
X_VALIDATION_SET_CODE in VARCHAR2,
X_VALIDATION_LEVEL_CODE in VARCHAR2,
X_VALIDATION_CODE_PACKAGE in VARCHAR2,
X_VALIDATION_CODE_ENTRY_POINT in VARCHAR2,
X_VALIDATION_CODE_LANGUAGE in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_VALIDATION_SET_DISPLAY_NAME in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update IBY_VALIDATION_SETS_B set
VALIDATION_LEVEL_CODE = X_VALIDATION_LEVEL_CODE,
VALIDATION_CODE_PACKAGE = X_VALIDATION_CODE_PACKAGE,
VALIDATION_CODE_ENTRY_POINT = X_VALIDATION_CODE_ENTRY_POINT,
VALIDATION_CODE_LANGUAGE = X_VALIDATION_CODE_LANGUAGE,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
update IBY_VALIDATION_SETS_TL set
VALIDATION_SET_DISPLAY_NAME = X_VALIDATION_SET_DISPLAY_NAME,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end VAL_SET_UPDATE_ROW;
procedure VAL_SET_DELETE_ROW (
X_VALIDATION_SET_CODE in VARCHAR2
) is
begin
delete from IBY_VALIDATION_SETS_TL
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
delete from IBY_VALIDATION_SETS_B
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
end VAL_SET_DELETE_ROW;
delete from IBY_VALIDATION_SETS_TL T
where not exists
(select NULL
from IBY_VALIDATION_SETS_B B
where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
);
update IBY_VALIDATION_SETS_TL T set (
VALIDATION_SET_DISPLAY_NAME
) = (select
B.VALIDATION_SET_DISPLAY_NAME
from IBY_VALIDATION_SETS_TL B
where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.VALIDATION_SET_CODE,
T.LANGUAGE
) in (select
SUBT.VALIDATION_SET_CODE,
SUBT.LANGUAGE
from IBY_VALIDATION_SETS_TL SUBB, IBY_VALIDATION_SETS_TL SUBT
where SUBB.VALIDATION_SET_CODE = SUBT.VALIDATION_SET_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.VALIDATION_SET_DISPLAY_NAME <> SUBT.VALIDATION_SET_DISPLAY_NAME
));
insert into IBY_VALIDATION_SETS_TL (
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
VALIDATION_SET_CODE,
VALIDATION_SET_DISPLAY_NAME,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.OBJECT_VERSION_NUMBER,
B.VALIDATION_SET_CODE,
B.VALIDATION_SET_DISPLAY_NAME,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from IBY_VALIDATION_SETS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from IBY_VALIDATION_SETS_TL T
where T.VALIDATION_SET_CODE = B.VALIDATION_SET_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
row_id VARCHAR2(200);
VAL_SET_UPDATE_ROW (
X_VALIDATION_SET_CODE,
X_VALIDATION_LEVEL_CODE,
X_VALIDATION_CODE_PACKAGE,
X_VALIDATION_CODE_ENTRY_POINT,
X_VALIDATION_CODE_LANGUAGE,
X_OBJECT_VERSION_NUMBER,
X_VALIDATION_SET_DISPLAY_NAME,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
VAL_SET_INSERT_ROW (
row_id,
X_VALIDATION_SET_CODE,
X_VALIDATION_LEVEL_CODE,
X_VALIDATION_CODE_PACKAGE,
X_VALIDATION_CODE_ENTRY_POINT,
X_VALIDATION_CODE_LANGUAGE,
X_OBJECT_VERSION_NUMBER,
X_VALIDATION_SET_DISPLAY_NAME,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
begin
update iby_validation_sets_tl set
VALIDATION_SET_DISPLAY_NAME = X_VALIDATION_SET_DISPLAY_NAME,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
SOURCE_LANG = userenv('LANG')
where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and VALIDATION_SET_CODE = X_VALIDATION_SET_CODE;
procedure VAL_PARAM_INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_VALIDATION_SET_CODE in VARCHAR2,
X_VALIDATION_PARAMETER_CODE in VARCHAR2,
X_VALIDATION_PARAMETER_TYPE in VARCHAR2,
X_VALIDATION_PARAM_DISPLAY_ORD in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_VALIDATION_PARAM_DISPLAY_NAM in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from IBY_VALIDATION_PARAMS_B
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE
;
insert into IBY_VALIDATION_PARAMS_B (
VALIDATION_SET_CODE,
VALIDATION_PARAMETER_CODE,
VALIDATION_PARAMETER_TYPE,
VALIDATION_PARAM_DISPLAY_ORDER,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_VALIDATION_SET_CODE,
X_VALIDATION_PARAMETER_CODE,
X_VALIDATION_PARAMETER_TYPE,
X_VALIDATION_PARAM_DISPLAY_ORD,
X_OBJECT_VERSION_NUMBER,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into IBY_VALIDATION_PARAMS_TL (
VALIDATION_SET_CODE,
VALIDATION_PARAMETER_CODE,
VALIDATION_PARAM_DISPLAY_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select
X_VALIDATION_SET_CODE,
X_VALIDATION_PARAMETER_CODE,
X_VALIDATION_PARAM_DISPLAY_NAM,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from IBY_VALIDATION_PARAMS_TL T
where T.VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and T.VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
end VAL_PARAM_INSERT_ROW;
procedure VAL_PARAM_UPDATE_ROW (
X_VALIDATION_SET_CODE in VARCHAR2,
X_VALIDATION_PARAMETER_CODE in VARCHAR2,
X_VALIDATION_PARAMETER_TYPE in VARCHAR2,
X_VALIDATION_PARAM_DISPLAY_ORD in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_VALIDATION_PARAM_DISPLAY_NAM in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update IBY_VALIDATION_PARAMS_B set
VALIDATION_PARAMETER_TYPE = X_VALIDATION_PARAMETER_TYPE,
VALIDATION_PARAM_DISPLAY_ORDER = X_VALIDATION_PARAM_DISPLAY_ORD,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
update IBY_VALIDATION_PARAMS_TL set
VALIDATION_PARAM_DISPLAY_NAME = X_VALIDATION_PARAM_DISPLAY_NAM,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end VAL_PARAM_UPDATE_ROW;
procedure VAL_PARAM_DELETE_ROW (
X_VALIDATION_SET_CODE in VARCHAR2,
X_VALIDATION_PARAMETER_CODE in VARCHAR2
) is
begin
delete from IBY_VALIDATION_PARAMS_TL
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
delete from IBY_VALIDATION_PARAMS_B
where VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
end VAL_PARAM_DELETE_ROW;
delete from IBY_VALIDATION_PARAMS_TL T
where not exists
(select NULL
from IBY_VALIDATION_PARAMS_B B
where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
and B.VALIDATION_PARAMETER_CODE = T.VALIDATION_PARAMETER_CODE
);
update IBY_VALIDATION_PARAMS_TL T set (
VALIDATION_PARAM_DISPLAY_NAME
) = (select
B.VALIDATION_PARAM_DISPLAY_NAME
from IBY_VALIDATION_PARAMS_TL B
where B.VALIDATION_SET_CODE = T.VALIDATION_SET_CODE
and B.VALIDATION_PARAMETER_CODE = T.VALIDATION_PARAMETER_CODE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.VALIDATION_SET_CODE,
T.VALIDATION_PARAMETER_CODE,
T.LANGUAGE
) in (select
SUBT.VALIDATION_SET_CODE,
SUBT.VALIDATION_PARAMETER_CODE,
SUBT.LANGUAGE
from IBY_VALIDATION_PARAMS_TL SUBB, IBY_VALIDATION_PARAMS_TL SUBT
where SUBB.VALIDATION_SET_CODE = SUBT.VALIDATION_SET_CODE
and SUBB.VALIDATION_PARAMETER_CODE = SUBT.VALIDATION_PARAMETER_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.VALIDATION_PARAM_DISPLAY_NAME <> SUBT.VALIDATION_PARAM_DISPLAY_NAME
));
insert into IBY_VALIDATION_PARAMS_TL (
VALIDATION_SET_CODE,
VALIDATION_PARAMETER_CODE,
VALIDATION_PARAM_DISPLAY_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.VALIDATION_SET_CODE,
B.VALIDATION_PARAMETER_CODE,
B.VALIDATION_PARAM_DISPLAY_NAME,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from IBY_VALIDATION_PARAMS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from IBY_VALIDATION_PARAMS_TL T
where T.VALIDATION_SET_CODE = B.VALIDATION_SET_CODE
and T.VALIDATION_PARAMETER_CODE = B.VALIDATION_PARAMETER_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
row_id VARCHAR2(200);
VAL_PARAM_UPDATE_ROW (
X_VALIDATION_SET_CODE,
X_VALIDATION_PARAMETER_CODE,
X_VALIDATION_PARAMETER_TYPE,
X_VALIDATION_PARAM_DISPLAY_ORD,
X_OBJECT_VERSION_NUMBER,
X_VALIDATION_PARAM_DISPLAY_NAM,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
VAL_PARAM_INSERT_ROW (
row_id,
X_VALIDATION_SET_CODE,
X_VALIDATION_PARAMETER_CODE,
X_VALIDATION_PARAMETER_TYPE,
X_VALIDATION_PARAM_DISPLAY_ORD,
X_OBJECT_VERSION_NUMBER,
X_VALIDATION_PARAM_DISPLAY_NAM,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
begin
update iby_validation_params_tl set
VALIDATION_PARAM_DISPLAY_NAME = X_VALIDATION_PARAM_DISPLAY_NAM,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
SOURCE_LANG = userenv('LANG')
where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and VALIDATION_SET_CODE = X_VALIDATION_SET_CODE
and VALIDATION_PARAMETER_CODE = X_VALIDATION_PARAMETER_CODE;
procedure TRANS_PROT_INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_TRANSMIT_CODE_LANGUAGE in VARCHAR2,
X_TRANSMIT_CODE_PACKAGE in VARCHAR2,
X_TRANSMIT_CODE_ENTRY_POINT in VARCHAR2,
X_TRANSMIT_PROTOCOL_NAME in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from IBY_TRANSMIT_PROTOCOLS_B
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
;
insert into IBY_TRANSMIT_PROTOCOLS_B (
OBJECT_VERSION_NUMBER,
TRANSMIT_PROTOCOL_CODE,
TRANSMIT_CODE_LANGUAGE,
TRANSMIT_CODE_PACKAGE,
TRANSMIT_CODE_ENTRY_POINT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_OBJECT_VERSION_NUMBER,
X_TRANSMIT_PROTOCOL_CODE,
X_TRANSMIT_CODE_LANGUAGE,
X_TRANSMIT_CODE_PACKAGE,
X_TRANSMIT_CODE_ENTRY_POINT,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into IBY_TRANSMIT_PROTOCOLS_TL (
TRANSMIT_PROTOCOL_CODE,
TRANSMIT_PROTOCOL_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select
X_TRANSMIT_PROTOCOL_CODE,
X_TRANSMIT_PROTOCOL_NAME,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from IBY_TRANSMIT_PROTOCOLS_TL T
where T.TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
end TRANS_PROT_INSERT_ROW;
procedure TRANS_PROT_UPDATE_ROW (
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_TRANSMIT_CODE_LANGUAGE in VARCHAR2,
X_TRANSMIT_CODE_PACKAGE in VARCHAR2,
X_TRANSMIT_CODE_ENTRY_POINT in VARCHAR2,
X_TRANSMIT_PROTOCOL_NAME in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update IBY_TRANSMIT_PROTOCOLS_B set
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
TRANSMIT_CODE_LANGUAGE = X_TRANSMIT_CODE_LANGUAGE,
TRANSMIT_CODE_PACKAGE = X_TRANSMIT_CODE_PACKAGE,
TRANSMIT_CODE_ENTRY_POINT = X_TRANSMIT_CODE_ENTRY_POINT,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
update IBY_TRANSMIT_PROTOCOLS_TL set
TRANSMIT_PROTOCOL_NAME = X_TRANSMIT_PROTOCOL_NAME,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end TRANS_PROT_UPDATE_ROW;
procedure TRANS_PROT_DELETE_ROW (
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2
) is
begin
delete from IBY_TRANSMIT_PROTOCOLS_TL
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
delete from IBY_TRANSMIT_PROTOCOLS_B
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
end TRANS_PROT_DELETE_ROW;
delete from IBY_TRANSMIT_PROTOCOLS_TL T
where not exists
(select NULL
from IBY_TRANSMIT_PROTOCOLS_B B
where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
);
update IBY_TRANSMIT_PROTOCOLS_TL T set (
TRANSMIT_PROTOCOL_NAME
) = (select
B.TRANSMIT_PROTOCOL_NAME
from IBY_TRANSMIT_PROTOCOLS_TL B
where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.TRANSMIT_PROTOCOL_CODE,
T.LANGUAGE
) in (select
SUBT.TRANSMIT_PROTOCOL_CODE,
SUBT.LANGUAGE
from IBY_TRANSMIT_PROTOCOLS_TL SUBB, IBY_TRANSMIT_PROTOCOLS_TL SUBT
where SUBB.TRANSMIT_PROTOCOL_CODE = SUBT.TRANSMIT_PROTOCOL_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.TRANSMIT_PROTOCOL_NAME <> SUBT.TRANSMIT_PROTOCOL_NAME
));
insert into IBY_TRANSMIT_PROTOCOLS_TL (
TRANSMIT_PROTOCOL_CODE,
TRANSMIT_PROTOCOL_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.TRANSMIT_PROTOCOL_CODE,
B.TRANSMIT_PROTOCOL_NAME,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from IBY_TRANSMIT_PROTOCOLS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from IBY_TRANSMIT_PROTOCOLS_TL T
where T.TRANSMIT_PROTOCOL_CODE = B.TRANSMIT_PROTOCOL_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
row_id VARCHAR2(200);
TRANS_PROT_UPDATE_ROW (
X_TRANSMIT_PROTOCOL_CODE,
X_OBJECT_VERSION_NUMBER,
X_TRANSMIT_CODE_LANGUAGE,
X_TRANSMIT_CODE_PACKAGE,
X_TRANSMIT_CODE_ENTRY_POINT,
X_TRANSMIT_PROTOCOL_NAME,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
TRANS_PROT_INSERT_ROW (
row_id,
X_TRANSMIT_PROTOCOL_CODE,
X_OBJECT_VERSION_NUMBER,
X_TRANSMIT_CODE_LANGUAGE,
X_TRANSMIT_CODE_PACKAGE,
X_TRANSMIT_CODE_ENTRY_POINT,
X_TRANSMIT_PROTOCOL_NAME,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
begin
update iby_transmit_protocols_tl set
TRANSMIT_PROTOCOL_NAME = X_TRANSMIT_PROTOCOL_NAME,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
SOURCE_LANG = userenv('LANG')
where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE;
procedure TRANS_PARAM_INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_TRANSMIT_PARAMETER_CODE in VARCHAR2,
X_TRANSMIT_PARAMETER_TYPE in VARCHAR2,
X_MANDATORY_FLAG in VARCHAR2,
X_DISPLAY_ORDER in NUMBER,
X_DYNAMIC_FLAG in VARCHAR2,
X_DYN_CODE_LANGUAGE in VARCHAR2,
X_DYN_CODE_PACKAGE in VARCHAR2,
X_DYN_CODE_ENTRY_POINT in VARCHAR2,
X_SECURED_FLAG in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_TRANSMIT_PARAMETER_NAME in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from IBY_TRANSMIT_PARAMETERS_B
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE
;
insert into IBY_TRANSMIT_PARAMETERS_B (
TRANSMIT_PARAMETER_CODE,
TRANSMIT_PARAMETER_TYPE,
TRANSMIT_PROTOCOL_CODE,
MANDATORY_FLAG,
DISPLAY_ORDER,
DYNAMIC_FLAG,
DYN_CODE_LANGUAGE,
DYN_CODE_PACKAGE,
DYN_CODE_ENTRY_POINT,
SECURED_FLAG,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_TRANSMIT_PARAMETER_CODE,
X_TRANSMIT_PARAMETER_TYPE,
X_TRANSMIT_PROTOCOL_CODE,
X_MANDATORY_FLAG,
X_DISPLAY_ORDER,
X_DYNAMIC_FLAG,
X_DYN_CODE_LANGUAGE,
X_DYN_CODE_PACKAGE,
X_DYN_CODE_ENTRY_POINT,
X_SECURED_FLAG,
X_OBJECT_VERSION_NUMBER,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into IBY_TRANSMIT_PARAMETERS_TL (
TRANSMIT_PARAMETER_CODE,
TRANSMIT_PROTOCOL_CODE,
TRANSMIT_PARAMETER_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select
X_TRANSMIT_PARAMETER_CODE,
X_TRANSMIT_PROTOCOL_CODE,
X_TRANSMIT_PARAMETER_NAME,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from IBY_TRANSMIT_PARAMETERS_TL T
where T.TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and T.TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
end TRANS_PARAM_INSERT_ROW;
procedure TRANS_PARAM_UPDATE_ROW (
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_TRANSMIT_PARAMETER_CODE in VARCHAR2,
X_TRANSMIT_PARAMETER_TYPE in VARCHAR2,
X_MANDATORY_FLAG in VARCHAR2,
X_DISPLAY_ORDER in NUMBER,
X_DYNAMIC_FLAG in VARCHAR2,
X_DYN_CODE_LANGUAGE in VARCHAR2,
X_DYN_CODE_PACKAGE in VARCHAR2,
X_DYN_CODE_ENTRY_POINT in VARCHAR2,
X_SECURED_FLAG in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_TRANSMIT_PARAMETER_NAME in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update IBY_TRANSMIT_PARAMETERS_B set
TRANSMIT_PARAMETER_TYPE = X_TRANSMIT_PARAMETER_TYPE,
MANDATORY_FLAG = X_MANDATORY_FLAG,
DISPLAY_ORDER = X_DISPLAY_ORDER,
DYNAMIC_FLAG = X_DYNAMIC_FLAG,
DYN_CODE_LANGUAGE = X_DYN_CODE_LANGUAGE,
DYN_CODE_PACKAGE = X_DYN_CODE_PACKAGE,
DYN_CODE_ENTRY_POINT = X_DYN_CODE_ENTRY_POINT,
SECURED_FLAG = X_SECURED_FLAG,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
update IBY_TRANSMIT_PARAMETERS_TL set
TRANSMIT_PARAMETER_NAME = X_TRANSMIT_PARAMETER_NAME,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end TRANS_PARAM_UPDATE_ROW;
procedure TRANS_PARAM_DELETE_ROW (
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_TRANSMIT_PARAMETER_CODE in VARCHAR2
) is
begin
delete from IBY_TRANSMIT_PARAMETERS_TL
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
delete from IBY_TRANSMIT_PARAMETERS_B
where TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
end TRANS_PARAM_DELETE_ROW;
delete from IBY_TRANSMIT_PARAMETERS_TL T
where not exists
(select NULL
from IBY_TRANSMIT_PARAMETERS_B B
where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
and B.TRANSMIT_PARAMETER_CODE = T.TRANSMIT_PARAMETER_CODE
);
update IBY_TRANSMIT_PARAMETERS_TL T set (
TRANSMIT_PARAMETER_NAME
) = (select
B.TRANSMIT_PARAMETER_NAME
from IBY_TRANSMIT_PARAMETERS_TL B
where B.TRANSMIT_PROTOCOL_CODE = T.TRANSMIT_PROTOCOL_CODE
and B.TRANSMIT_PARAMETER_CODE = T.TRANSMIT_PARAMETER_CODE
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.TRANSMIT_PROTOCOL_CODE,
T.TRANSMIT_PARAMETER_CODE,
T.LANGUAGE
) in (select
SUBT.TRANSMIT_PROTOCOL_CODE,
SUBT.TRANSMIT_PARAMETER_CODE,
SUBT.LANGUAGE
from IBY_TRANSMIT_PARAMETERS_TL SUBB, IBY_TRANSMIT_PARAMETERS_TL SUBT
where SUBB.TRANSMIT_PROTOCOL_CODE = SUBT.TRANSMIT_PROTOCOL_CODE
and SUBB.TRANSMIT_PARAMETER_CODE = SUBT.TRANSMIT_PARAMETER_CODE
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.TRANSMIT_PARAMETER_NAME <> SUBT.TRANSMIT_PARAMETER_NAME
));
insert into IBY_TRANSMIT_PARAMETERS_TL (
TRANSMIT_PARAMETER_CODE,
TRANSMIT_PROTOCOL_CODE,
TRANSMIT_PARAMETER_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.TRANSMIT_PARAMETER_CODE,
B.TRANSMIT_PROTOCOL_CODE,
B.TRANSMIT_PARAMETER_NAME,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.OBJECT_VERSION_NUMBER,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from IBY_TRANSMIT_PARAMETERS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from IBY_TRANSMIT_PARAMETERS_TL T
where T.TRANSMIT_PROTOCOL_CODE = B.TRANSMIT_PROTOCOL_CODE
and T.TRANSMIT_PARAMETER_CODE = B.TRANSMIT_PARAMETER_CODE
and T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
row_id VARCHAR2(200);
TRANS_PARAM_UPDATE_ROW (
X_TRANSMIT_PROTOCOL_CODE,
X_TRANSMIT_PARAMETER_CODE,
X_TRANSMIT_PARAMETER_TYPE,
X_MANDATORY_FLAG,
X_DISPLAY_ORDER,
X_DYNAMIC_FLAG,
X_DYN_CODE_LANGUAGE,
X_DYN_CODE_PACKAGE,
X_DYN_CODE_ENTRY_POINT,
X_SECURED_FLAG,
X_OBJECT_VERSION_NUMBER,
X_TRANSMIT_PARAMETER_NAME,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
TRANS_PARAM_INSERT_ROW (
row_id,
X_TRANSMIT_PROTOCOL_CODE,
X_TRANSMIT_PARAMETER_CODE,
X_TRANSMIT_PARAMETER_TYPE,
X_MANDATORY_FLAG,
X_DISPLAY_ORDER,
X_DYNAMIC_FLAG,
X_DYN_CODE_LANGUAGE,
X_DYN_CODE_PACKAGE,
X_DYN_CODE_ENTRY_POINT,
X_SECURED_FLAG,
X_OBJECT_VERSION_NUMBER,
X_TRANSMIT_PARAMETER_NAME,
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
X_LAST_UPDATE_DATE,
fnd_load_util.owner_id(X_OWNER),
fnd_load_util.owner_id(X_OWNER));
X_LAST_UPDATE_DATE in DATE,
X_OWNER in VARCHAR2)
is
begin
update iby_transmit_parameters_tl set
TRANSMIT_PARAMETER_NAME = X_TRANSMIT_PARAMETER_NAME,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
SOURCE_LANG = userenv('LANG')
where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
and TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE
and TRANSMIT_PARAMETER_CODE = X_TRANSMIT_PARAMETER_CODE;
procedure TRANS_CONFIG_INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_TRANSMIT_CONFIGURATION_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_TUNNELING_TRANS_CONFIG_ID in NUMBER,
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_INACTIVE_DATE in DATE,
X_TRANSMIT_CONFIGURATION_NAME in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
cursor C is select ROWID from IBY_TRANSMIT_CONFIGS_B
where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID
;
insert into IBY_TRANSMIT_CONFIGS_B (
OBJECT_VERSION_NUMBER,
TUNNELING_TRANS_CONFIG_ID,
TRANSMIT_CONFIGURATION_ID,
TRANSMIT_PROTOCOL_CODE,
INACTIVE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_OBJECT_VERSION_NUMBER,
X_TUNNELING_TRANS_CONFIG_ID,
X_TRANSMIT_CONFIGURATION_ID,
X_TRANSMIT_PROTOCOL_CODE,
X_INACTIVE_DATE,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into IBY_TRANSMIT_CONFIGS_TL (
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
TRANSMIT_CONFIGURATION_NAME,
CREATED_BY,
TRANSMIT_CONFIGURATION_ID,
LANGUAGE,
SOURCE_LANG
) select
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_OBJECT_VERSION_NUMBER,
X_TRANSMIT_CONFIGURATION_NAME,
X_CREATED_BY,
X_TRANSMIT_CONFIGURATION_ID,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from IBY_TRANSMIT_CONFIGS_TL T
where T.TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end TRANS_CONFIG_INSERT_ROW;
procedure TRANS_CONFIG_UPDATE_ROW (
X_TRANSMIT_CONFIGURATION_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_TUNNELING_TRANS_CONFIG_ID in NUMBER,
X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
X_INACTIVE_DATE in DATE,
X_TRANSMIT_CONFIGURATION_NAME in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
) is
begin
update IBY_TRANSMIT_CONFIGS_B set
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
TUNNELING_TRANS_CONFIG_ID = X_TUNNELING_TRANS_CONFIG_ID,
TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE,
INACTIVE_DATE = X_INACTIVE_DATE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID;
update IBY_TRANSMIT_CONFIGS_TL set
TRANSMIT_CONFIGURATION_NAME = X_TRANSMIT_CONFIGURATION_NAME,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end TRANS_CONFIG_UPDATE_ROW;
procedure TRANS_CONFIG_DELETE_ROW (
X_TRANSMIT_CONFIGURATION_ID in NUMBER
) is
begin
delete from IBY_TRANSMIT_CONFIGS_TL
where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID;
delete from IBY_TRANSMIT_CONFIGS_B
where TRANSMIT_CONFIGURATION_ID = X_TRANSMIT_CONFIGURATION_ID;
end TRANS_CONFIG_DELETE_ROW;
delete from IBY_TRANSMIT_CONFIGS_TL T
where not exists
(select NULL
from IBY_TRANSMIT_CONFIGS_B B
where B.TRANSMIT_CONFIGURATION_ID = T.TRANSMIT_CONFIGURATION_ID
);
update IBY_TRANSMIT_CONFIGS_TL T set (
TRANSMIT_CONFIGURATION_NAME
) = (select
B.TRANSMIT_CONFIGURATION_NAME
from IBY_TRANSMIT_CONFIGS_TL B
where B.TRANSMIT_CONFIGURATION_ID = T.TRANSMIT_CONFIGURATION_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.TRANSMIT_CONFIGURATION_ID,
T.LANGUAGE
) in (select
SUBT.TRANSMIT_CONFIGURATION_ID,
SUBT.LANGUAGE
from IBY_TRANSMIT_CONFIGS_TL SUBB, IBY_TRANSMIT_CONFIGS_TL SUBT
where SUBB.TRANSMIT_CONFIGURATION_ID = SUBT.TRANSMIT_CONFIGURATION_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.TRANSMIT_CONFIGURATION_NAME <> SUBT.TRANSMIT_CONFIGURATION_NAME
));
insert into IBY_TRANSMIT_CONFIGS_TL (
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
TRANSMIT_CONFIGURATION_NAME,
CREATED_BY,
TRANSMIT_CONFIGURATION_ID,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.OBJECT_VERSION_NUMBER,
B.TRANSMIT_CONFIGURATION_NAME,
B.CREATED_BY,
B.TRANSMIT_CONFIGURATION_ID,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from IBY_TRANSMIT_CONFIGS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from IBY_TRANSMIT_CONFIGS_TL T
where T.TRANSMIT_CONFIGURATION_ID = B.TRANSMIT_CONFIGURATION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);