[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: insert_row
PURPOSE: Inserts a record into VEA_TP_LAYERS table
========================================================================*/
PROCEDURE
insert_row
(
p_layer_provider_code IN vea_tp_layers.layer_provider_code%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
p_name IN vea_tp_layers.name%TYPE,
p_description IN vea_tp_layers.description%TYPE,
p_active_flag IN vea_tp_layers.active_flag%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
INSERT INTO vea_tp_layers
(
layer_provider_code, tp_layer_id,
name,
description,
active_flag,
created_by, creation_date,
last_updated_by, last_update_date,
last_update_login
)
VALUES
(
p_layer_provider_code, p_tp_layer_id,
p_name,
p_description,
'N', --p_active_flag,
l_user_id, SYSDATE,
l_user_id, SYSDATE,
l_login_id
);
END insert_row;
PROCEDURE NAME: update_row
PURPOSE: Updates a record into VEA_TP_LAYERS table
========================================================================*/
PROCEDURE
update_row
(
p_layer_provider_code IN vea_tp_layers.layer_provider_code%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
p_name IN vea_tp_layers.name%TYPE,
p_description IN vea_tp_layers.description%TYPE,
p_active_flag IN vea_tp_layers.active_flag%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
UPDATE vea_tp_layers
SET name = p_name,
description = p_description,
active_flag = 'N', --p_active_flag,
last_updated_by = l_user_id,
last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE layer_provider_code = p_layer_provider_code
AND tp_layer_id = p_tp_layer_id;
END update_row;
PROCEDURE NAME: delete_row
PURPOSE: Deletes the specified TP Layer
========================================================================*/
PROCEDURE
delete_row
(
p_layer_provider_code IN vea_tp_layers.layer_provider_code%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
vea_layer_licenses_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => p_tp_layer_id
);
DELETE vea_tp_layers
WHERE layer_provider_code = p_layer_provider_code
AND tp_layer_id = p_tp_layer_id;
END delete_row;
SELECT tp_layer_id
FROM vea_tp_layers
WHERE name = p_tp_layer_name
AND layer_provider_code = p_layer_provider_code;
SELECT tp_layer_id
FROM vea_tp_layers
WHERE name = p_tp_layer_name
AND layer_provider_code = p_layer_provider_code;
PROCEDURE NAME: delete_rows
PURPOSE: Removes all data belonging to the specified TP layer
and containing customizations for the specified application
========================================================================*/
PROCEDURE
delete_rows
(
p_layer_provider_code IN vea_tp_layers.layer_provider_code%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
vea_layer_headers_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id,
p_application_short_name => p_application_short_name,
x_layer_header_count => l_layer_header_count
);
vea_packages_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id,
p_application_short_name => p_application_short_name,
x_package_count => l_package_count
);
vea_layer_licenses_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id
);
delete_row
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id
);
END delete_rows;
PROCEDURE NAME: delete_rows
PURPOSE: Removes all data belonging to the specified TP layer
and containing customizations for the specified application
========================================================================*/
PROCEDURE
delete_rows
(
p_layer_provider_code IN vea_tp_layers.layer_provider_code%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
p_tp_layer_name IN vea_tp_layers.name%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
SELECT tp_layer_id
FROM vea_tp_layers
WHERE layer_provider_code = p_layer_provider_code
AND tp_layer_id = p_tp_layer_id;
SELECT tp_layer_id
FROM vea_tp_layers
WHERE layer_provider_code = p_layer_provider_code
AND name = NVL(p_tp_layer_name,name);
delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id,
p_application_short_name => p_application_short_name
);
delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id,
p_application_short_name => p_application_short_name
);
END delete_rows;
PROCEDURE NAME: deleteUnlicensedLayers
PURPOSE: Deletes all TP layers which are not licensed to the current
customer and which does not have any packages.
========================================================================*/
PROCEDURE
deleteUnlicensedLayers
(
p_layer_provider_code IN vea_tp_layers.layer_provider_code%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'deleteUnlicensedLayers';
DELETE vea_tp_layers
WHERE layer_provider_code = p_layer_provider_code
AND tp_layer_id NOT IN (
SELECT tp_layer_id
FROM vea_packages
WHERE layer_provider_code = p_layer_provider_code
)
AND tp_layer_id NOT IN (
SELECT tp_layer_id
FROM vea_layer_licenses
WHERE layer_provider_code = p_layer_provider_code
);
END deleteUnlicensedLayers;
It inserts/updates a record in VEA_TP_LAYERS table.
========================================================================*/
PROCEDURE
process
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_id OUT NOCOPY vea_tp_layers.tp_layer_id%TYPE,
p_layer_provider_code IN vea_tp_layers.layer_provider_code%TYPE,
p_name IN vea_tp_layers.name%TYPE,
p_description IN vea_tp_layers.description%TYPE,
p_active_flag IN vea_tp_layers.active_flag%TYPE,
p_id IN vea_tp_layers.tp_layer_id%TYPE := NULL
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'process';
SELECT tp_layer_id
FROM vea_tp_layers
WHERE layer_provider_code = p_layer_provider_code
AND name = p_name;
SELECT tp_layer_id
FROM vea_tp_layers
WHERE layer_provider_code = p_layer_provider_code
AND tp_layer_id = p_tp_layer_id;
SELECT NVL( p_id, vea_tp_layers_s.NEXTVAL )
INTO l_tp_layer_id
FROM DUAL;
SELECT vea_tp_layers_s.NEXTVAL
INTO l_tp_layer_id
FROM DUAL;
insert_row
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id,
p_name => p_name,
p_description => p_description,
p_active_flag => p_active_flag
);
update_row
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id,
p_name => p_name,
p_description => p_description,
p_active_flag => p_active_flag
);