The following lines contain the word 'select', 'insert', 'update' or 'delete':
--when a row is being inserted only the NOTACKNOWLEDGED status is valid.
--along with the one time migration value of NOTCOLLECTED
--the insert happens privately in the ERES Fwk
if (p_mode = G_INSERT_MODE)
then
if p_status <> EDR_CONSTANTS_GRP.g_no_ack_status
and p_status <> EDR_CONSTANTS_GRP.g_migration_ack_status
then
l_return_value := FALSE;
--when a row is being updated only the SUCCESS and ERROR are valid
--the upadate happens publicly by product teams
elsif (p_mode = G_UPDATE_MODE)
then
if p_status <> EDR_CONSTANTS_GRP.g_success_ack_status
and p_status <> EDR_CONSTANTS_GRP.g_error_ack_status
then
l_return_value := FALSE;
procedure INSERT_ROW
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 ,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_erecord_id IN NUMBER ,
p_trans_status IN VARCHAR2 ,
p_ackn_by IN VARCHAR2 ,
p_ackn_note IN VARCHAR2 ,
x_ackn_id OUT NOCOPY NUMBER
)
as
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
select count(ackn_id)
from edr_trans_ackn
where erecord_id = p_erecord_id;
p_mode => G_INSERT_MODE
);
-- after all the validations are done, insert the row in the
-- database
select edr_trans_ackn_s.nextval
into l_ackn_id
from dual;
last_update_date => l_last_update_date ,
last_updated_by => l_last_updated_by ,
last_update_login => l_last_update_login
);
insert into EDR_TRANS_ACKN(
ACKN_ID
,ERECORD_ID
,ACKN_DATE
,TRANSACTION_STATUS
,ACKN_BY
,ACKN_NOTE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
) values (
l_ackn_id
,p_erecord_id
,SYSDATE
,p_trans_status
,p_ackn_by
,p_ackn_note
,L_CREATION_DATE
,L_CREATED_BY
,L_LAST_UPDATE_DATE
,L_LAST_UPDATED_BY
,L_LAST_UPDATE_LOGIN
);
end INSERT_ROW;
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
-- so dont do any validation. just do an update and commit
EDR_UTILITIES.getWhoColumns
( creation_date => l_creation_date ,
created_by => l_created_by ,
last_update_date => l_last_update_date ,
last_updated_by => l_last_updated_by ,
last_update_login => l_last_update_login
);
update EDR_TRANS_ACKN SET
ACKN_DATE = SYSDATE ,
TRANSACTION_STATUS = p_trans_status ,
ACKN_BY = p_ackn_by ,
ACKN_NOTE = p_ackn_note ,
LAST_UPDATE_DATE = l_last_update_date ,
LAST_UPDATED_BY = l_last_updated_by ,
LAST_UPDATE_LOGIN = l_last_update_login
where ERECORD_ID = p_erecord_id;