[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
is select ecx_xref_hdr_s.nextval
from dual;
ECX_XREF_HDR_PKG.insert_row (
X_ROWID => i_rowid,
X_XREF_CATEGORY_CODE => p_xref_category_code,
X_XREF_CATEGORY_ID => x_xref_hdr_id,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => i_c_by,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => i_c_by,
X_LAST_UPDATE_LOGIN => i_c_by
);
PROCEDURE delete_code_category(
x_return_status OUT NOCOPY PLS_INTEGER,
x_msg OUT NOCOPY VARCHAR2,
p_xref_category_id IN PLS_INTEGER
) IS
i_num pls_integer := 0;
select 1 from ecx_object_attributes
where xref_category_id = p_xref_category_id;
select count(*) from ecx_xref_standards exs,
ecx_xref_dtl exd
where exs.xref_category_id = p_xref_category_id
and exd.xref_category_id = p_xref_category_id;
ECX_XREF_HDR_PKG.delete_row (
X_XREF_CATEGORY_ID => p_xref_category_id
);
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
'p_table', 'ecx_xref_hdr',
'p_param_name', 'Category Code ID',
'p_param_id', p_xref_category_id);
PROCEDURE update_code_category(
x_return_status OUT NOCOPY PLS_INTEGER,
x_msg OUT NOCOPY VARCHAR2,
p_xref_category_id IN PLS_INTEGER,
p_xref_category_code IN VARCHAR2,
p_description IN VARCHAR2,
p_owner IN VARCHAR2
) is
i_u_by pls_integer := 0;
ECX_XREF_HDR_PKG.update_row (
X_XREF_CATEGORY_ID => p_xref_category_id,
X_XREF_CATEGORY_CODE => p_xref_category_code,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => i_u_by,
X_LAST_UPDATE_LOGIN => i_u_by
);
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_xref_hdr_tl',
'p_param_name', 'Cateogry Code ID',
'p_param_id', p_xref_category_id);
select xref_dtl_id,
exd.xref_ext_value
into x_xref_dtl_id,
x_xref_ext_value
from ECX_XREF_HDR exh,
ECX_XREF_DTL exd,
ECX_STANDARDS es
where exh.xref_category_code = p_xref_category_code
and exh.xref_category_id = exd.xref_category_id
and exd.direction = 'OUT'
and exd.xref_int_value = p_xref_int_value
and es.standard_id = exd.standard_id
and es.standard_code = p_standard
and exd.tp_header_id = p_tp_header_id
and es.standard_type = nvl(p_standard_type, 'XML');
select xref_dtl_id,
exd.xref_int_value
into x_xref_dtl_id,
x_xref_int_value
from ECX_XREF_HDR exh,
ECX_XREF_DTL exd,
ECX_STANDARDS es
where exh.xref_category_code = p_xref_category_code
and exh.xref_category_id = exd.xref_category_id
and exd.direction = 'IN'
and exd.xref_ext_value = p_xref_ext_value
and es.standard_id = exd.standard_id
and es.standard_code = p_standard
and exd.tp_header_id = p_tp_header_id
and es.standard_type = nvl(p_standard_type, 'XML');
x_last_updated_by OUT NOCOPY PLS_INTEGER,
x_last_update_date OUT NOCOPY DATE
) is
begin
x_return_status := ECX_UTIL_API.G_NO_ERROR;
select
exh.xref_category_code,
es.standard_code,
exd.xref_ext_value,
exd.xref_int_value,
exd.direction,
exh.DESCRIPTION HDR_DESCRIPTION,
exd.XREF_CATEGORY_ID XREF_CATEGORY_ID,
es.STANDARD_ID,
TP_HEADER_ID,
exd.DESCRIPTION DTL_DESCRIPTION,
exd.LAST_UPDATE_DATE,
exd.LAST_UPDATED_BY,
exd.CREATION_DATE,
exd.CREATED_BY
into
x_xref_category_code,
x_standard_code,
x_xref_ext_value,
x_xref_int_value,
x_direction,
x_cat_description,
x_xref_category_id,
x_standard_id,
x_tp_header_id,
x_description,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by
from ECX_XREF_HDR_VL exh,
ECX_XREF_DTL_VL exd,
ECX_STANDARDS es
where exh.xref_category_id = exd.xref_category_id
and exd.xref_dtl_id = p_xref_dtl_id
and es.standard_id = exd.standard_id;
x_last_updated_by OUT NOCOPY PLS_INTEGER,
x_last_update_date OUT NOCOPY DATE,
p_standard_type IN VARCHAR2
) is
begin
x_return_status := ECX_UTIL_API.G_NO_ERROR;
select
exh.DESCRIPTION HDR_DESCRIPTION,
XREF_DTL_ID,
exd.XREF_CATEGORY_ID XREF_CATEGORY_ID,
es.STANDARD_ID,
TP_HEADER_ID,
exd.DESCRIPTION DTL_DESCRIPTION,
exd.LAST_UPDATE_DATE,
exd.LAST_UPDATED_BY,
exd.CREATION_DATE,
exd.CREATED_BY
into
x_cat_description,
x_xref_dtl_id,
x_xref_category_id,
x_standard_id,
x_tp_header_id,
x_description,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by
from ECX_XREF_HDR_VL exh,
ECX_XREF_DTL_VL exd,
ECX_STANDARDS es
where exh.xref_category_code = p_xref_category_code
and exh.xref_category_id = exd.xref_category_id
and exd.direction = p_direction
and exd.xref_ext_value = p_xref_ext_value
and exd.xref_int_value = p_xref_int_value
and es.standard_id = exd.standard_id
and es.standard_code = p_standard
and es.standard_type = nvl(p_standard_type, 'XML');
select xref_category_id
into x_xref_category_id
from ecx_xref_hdr
where xref_category_code = p_xref_category_code;
select standard_id
into i_standard_id
from ecx_standards
where standard_code = p_standard
and standard_type = nvl(p_standard_type, 'XML');
select ecx_xref_dtl_s.nextval into x_xref_dtl_id from dual;
ECX_XREF_DTL_PKG. insert_row (
X_ROWID => i_rowid,
X_XREF_DTL_ID => x_xref_dtl_id,
X_XREF_CATEGORY_ID => x_xref_category_id,
X_STANDARD_ID => i_standard_id,
X_XREF_STANDARD_CODE => p_standard,
X_TP_HEADER_ID => p_tp_header_id,
X_XREF_EXT_VALUE => p_xref_ext_value,
X_XREF_INT_VALUE => p_xref_int_value,
X_DIRECTION => p_direction,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => 0,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => 0,
X_LAST_UPDATE_LOGIN => 0
);
PROCEDURE update_tp_code_values(
x_return_status OUT NOCOPY PLS_INTEGER,
x_msg OUT NOCOPY VARCHAR2,
p_xref_dtl_id IN PLS_INTEGER,
p_xref_ext_value IN VARCHAR2,
p_xref_int_value IN VARCHAR2,
p_tp_header_id IN PLS_INTEGER,
p_description IN VARCHAR2,
p_direction IN VARCHAR2
) is
l_xref_cat_id ecx_xref_hdr.xref_category_id%type;
select xref_category_id,
standard_id,
tp_header_id,
xref_int_value,
xref_ext_value,
direction
from ecx_xref_dtl
where xref_dtl_id = p_xref_dtl_id;
update ECX_XREF_DTL set
XREF_EXT_VALUE = p_xref_ext_value,
XREF_INT_VALUE = p_xref_int_value,
TP_HEADER_ID = p_tp_header_id,
DIRECTION = p_direction,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = 0
where XREF_DTL_ID = p_xref_dtl_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_xref_dtl',
'p_param_name', 'Xref detail ID',
'p_param_id', p_xref_dtl_id);
update ECX_XREF_DTL_TL set
DESCRIPTION = p_description,
SOURCE_LANG = userenv('LANG')
where XREF_DTL_ID = p_xref_dtl_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_xref_dtl',
'p_param_name', 'Xref detail ID',
'p_param_id', p_xref_dtl_id);
PROCEDURE delete_tp_code_values(
x_return_status OUT NOCOPY PLS_INTEGER,
x_msg OUT NOCOPY VARCHAR2,
p_xref_dtl_id IN PLS_INTEGER
) is
begin
x_return_status := ECX_UTIL_API.G_NO_ERROR;
ECX_XREF_DTL_PKG.delete_row (
X_XREF_DTL_ID => p_xref_dtl_id
);
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
'p_table', 'ecx_xref_dtl',
'p_param_name', 'Xref detail ID',
'p_param_id', p_xref_dtl_id);
x_last_updated_by OUT NOCOPY PLS_INTEGER,
x_last_update_date OUT NOCOPY DATE,
p_standard_type IN VARCHAR2
) is
begin
x_return_status := ECX_UTIL_API.G_NO_ERROR;
select exh.DESCRIPTION HDR_DESCRIPTION,
XREF_STANDARD_ID,
exs.XREF_CATEGORY_ID XREF_CATEGORY_ID,
exs.STANDARD_ID STANDARD_ID,
exs.DESCRIPTION DTL_DESCRIPTION,
exs.DATA_SEEDED,
exs.LAST_UPDATE_DATE,
exs.LAST_UPDATED_BY,
exs.CREATION_DATE,
exs.CREATED_BY
into x_cat_description,
x_xref_std_id,
x_xref_category_id,
x_standard_id,
x_description,
x_data_seeded,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by
from ECX_XREF_HDR_vl exh,
ECX_XREF_STANDARDS_vl exs,
ECX_STANDARDS es
where exh.xref_category_code = p_xref_category_code
and exh.xref_category_id = exs.xref_category_id
and exs.xref_std_value = p_xref_std_value
and exs.xref_int_value = p_xref_int_value
and es.standard_id = exs.standard_id
and es.standard_code = p_standard
and es.standard_type = nvl(p_standard_type, 'XML');
select xref_category_id
into x_xref_category_id
from ecx_xref_hdr
where xref_category_code = p_xref_category_code;
select standard_id
into i_standard_id
from ecx_standards
where standard_code = p_standard
and standard_type = nvl(p_standard_type, 'XML');
select ecx_xref_standards_s.nextval
into x_xref_std_id from dual;
ECX_XREF_STANDARDS_PKG.insert_row (
X_ROWID => i_rowid,
X_XREF_STANDARD_ID => x_xref_std_id,
X_XREF_CATEGORY_ID => x_xref_category_id,
X_STANDARD_ID => i_standard_id,
X_XREF_STANDARD_CODE => p_standard,
X_XREF_STD_VALUE => p_xref_std_value,
X_XREF_INT_VALUE => p_xref_int_value,
X_DATA_SEEDED => p_data_seeded,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => i_c_by,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => i_c_by,
X_LAST_UPDATE_LOGIN => i_c_by
);
PROCEDURE update_standard_code_values(
x_return_status OUT NOCOPY PLS_INTEGER,
x_msg OUT NOCOPY VARCHAR2,
p_xref_standard_id IN PLS_INTEGER,
p_xref_std_value IN VARCHAR2,
p_xref_int_value IN VARCHAR2,
p_description IN VARCHAR2,
p_owner IN VARCHAR2
) is
i_u_by pls_integer := 0;
update ECX_XREF_STANDARDS set
XREF_STD_VALUE = p_xref_std_value,
XREF_INT_VALUE = p_xref_int_value,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = i_u_by,
LAST_UPDATE_LOGIN = i_u_by
where XREF_STANDARD_ID = p_xref_standard_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_xref_standards',
'p_param_name', 'Xref standard ID',
'p_param_id', p_xref_standard_id);
update ECX_XREF_STANDARDS_TL set
DESCRIPTION = p_description,
SOURCE_LANG = userenv('LANG')
where XREF_STANDARD_ID = p_xref_standard_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_xref_standards',
'p_param_name', 'Xref standard ID',
'p_param_id', p_xref_standard_id);
PROCEDURE delete_standard_code_values(
x_return_status OUT NOCOPY PLS_INTEGER,
x_msg OUT NOCOPY VARCHAR2,
p_xref_standard_id IN PLS_INTEGER
) is
begin
x_return_status := ECX_UTIL_API.G_NO_ERROR;
ECX_XREF_STANDARDS_PKG.delete_row (
X_XREF_STANDARD_ID => p_xref_standard_id
);
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
'p_table', 'ecx_xref_standards',
'p_param_name', 'Xref standard ID',
'p_param_id', p_xref_standard_id);