The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_template_instance_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
l_template_instance_id NUMBER;
SELECT aso_sup_tmpl_instance_s.NEXTVAL
INTO l_template_instance_id
FROM DUAL;
INSERT INTO aso_sup_tmpl_instance
( TEMPLATE_INSTANCE_ID,
TEMPLATE_ID,
OWNER_TABLE_NAME,
OWNER_TABLE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY )
VALUES
( l_template_instance_id,
p_template_id,
p_owner_table_name,
p_owner_table_id,
SYSDATE, p_last_updated_by, p_last_update_login, SYSDATE, p_created_by);
PROCEDURE update_data (
p_template_instance_id IN NUMBER,
p_sect_comp_map_id IN NUMBER,
p_created_by IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_response_id IN NUMBER,
p_response_value IN VARCHAR2,
p_multiple_response_flag IN VARCHAR2,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
l_exists VARCHAR2(2);
l_api_name varchar2(50):= 'update_data';
SAVEPOINT update_data_int;
SELECT 'Y'
INTO l_exists
FROM aso_sup_instance_value
WHERE template_instance_id = p_template_instance_id
AND sect_comp_map_id = p_sect_comp_map_id
AND ROWNUM = 1 ;
UPDATE aso_sup_instance_value
SET created_by = p_created_by,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
response_id = p_response_id,
value = p_response_value
WHERE template_instance_id = p_template_instance_id
AND sect_comp_map_id = p_sect_comp_map_id;
INSERT INTO aso_sup_instance_value
(instance_value_id, template_instance_id,
sect_comp_map_id, created_by, last_updated_by,
last_update_login, response_id, value,
last_update_date, creation_date)
VALUES
(aso_sup_inst_value_s.nextval, p_template_instance_id,
p_sect_comp_map_id, p_created_by, p_last_updated_by,
p_last_update_login, p_response_id, p_response_value,
SYSDATE, SYSDATE);
END update_data;
PROCEDURE delete_data (
p_template_instance_id IN NUMBER,
p_sect_comp_map_id IN NUMBER,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
l_api_name varchar2(50) := 'delete_data';
SAVEPOINT delete_data_int;
DELETE FROM aso_sup_instance_value
WHERE template_instance_id = p_template_instance_id
AND sect_comp_map_id = p_sect_comp_map_id;
END delete_data;
l_lastUpdatedBy NUMBER :=l_createdBy;
l_lastUpdateLogin NUMBER := to_number( fnd_profile.value('LOGIN_ID') );
SELECT quote_header_id
FROM aso_quote_lines_all
WHERE quote_line_id = p_qte_line_id;
p_last_updated_by => l_lastUpdatedBy,
p_last_update_login => l_lastUpdateLogin,
p_commit => p_commit,
x_template_instance_id => lx_templateInstanceId,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
update_data (
p_template_instance_id => l_templateInstance,
p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
p_created_by => l_createdBy,
p_last_updated_by => l_lastUpdatedBy,
p_last_update_login =>l_lastUpdateLogin,
p_response_id =>l_responseIds(l_sectLoop),
p_response_value => l_responseValues(l_sectLoop),
p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
p_commit => p_commit,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
delete_data (
p_template_instance_id => l_templateInstance,
p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
p_commit => p_commit,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
update_data (
p_template_instance_id => l_templateInstance,
p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
p_created_by => l_createdBy,
p_last_updated_by => l_lastUpdatedBy,
p_last_update_login =>l_lastUpdateLogin,
p_response_id =>l_responseIds(l_sectLoop),
p_response_value => l_responseValues(l_sectLoop),
p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
p_commit => p_commit,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
PROCEDURE update_instance_value(
P_VERSION_NUMBER IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_Template_instance_id IN NUMBER := FND_API.G_MISS_NUM,
P_comp_sect_map_id IN JTF_NUMBER_TABLE,
P_response_value IN JTF_VARCHAR2_TABLE_2000,
P_response_id IN JTF_NUMBER_TABLE,
P_mult_ans_flag IN JTF_VARCHAR2_TABLE_100,
X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
l_compSectMapId aso_sup_instance_value.SECT_COMP_MAP_ID%TYPE;
l_lastUpdatedBy NUMBER :=l_createdBy;
l_lastUpdateLogin NUMBER := to_number( fnd_profile.value('LOGIN_ID') );
l_api_name VARCHAR2(50) := 'update_instance_value';
SELECT owner_table_id,owner_table_name
FROM ASO_SUP_TMPL_INSTANCE
WHERE template_instance_id = lc_template_instance_id;
SELECT quote_header_id
FROM aso_quote_lines_all
WHERE quote_line_id = p_qte_line_id;
SAVEPOINT update_instance_value_int;
update_data (
p_template_instance_id => l_templateInstance,
p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
p_created_by => l_createdBy,
p_last_updated_by => l_lastUpdatedBy,
p_last_update_login =>l_lastUpdateLogin,
p_response_id =>l_responseIds(l_sectLoop),
p_response_value => l_responseValues(l_sectLoop),
p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
p_commit => p_commit,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
delete_data (
p_template_instance_id => l_templateInstance,
p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
p_commit => p_commit,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
update_data (
p_template_instance_id => l_templateInstance,
p_sect_comp_map_id => l_compSectMapIds(l_sectLoop),
p_created_by => l_createdBy,
p_last_updated_by => l_lastUpdatedBy,
p_last_update_login => l_lastUpdateLogin,
p_response_id => l_responseIds(l_sectLoop),
p_response_value => l_responseValues(l_sectLoop),
p_multiple_response_flag => l_multAnsFlags(l_sectLoop),
p_commit => p_commit,
X_RETURN_STATUS => L_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
END update_instance_value;