[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: insert_row
PURPOSE: Inserts a record into VEA_LAYER_HEADERS table
========================================================================*/
PROCEDURE
insert_row
(
p_layer_provider_code IN vea_layer_headers.layer_provider_code%TYPE,
p_layer_header_id IN vea_layer_headers.layer_header_id%TYPE,
p_program_unit_id IN vea_layer_headers.program_unit_id%TYPE,
p_program_unit_lp_code IN vea_layer_headers.program_unit_lp_code%TYPE,
p_tps_program_unit_id IN vea_layer_headers.tps_program_unit_id%TYPE,
p_tps_program_unit_lp_code IN vea_layer_headers.tps_program_unit_lp_code%TYPE,
p_condition_type IN vea_layer_headers.condition_type%TYPE,
p_description IN vea_layer_headers.description%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
INSERT INTO vea_layer_headers
(
layer_provider_code, layer_header_id,
program_unit_id, program_unit_lp_code,
tps_program_unit_id, tps_program_unit_lp_code,
condition_type,
description,
created_by, creation_date,
last_updated_by, last_update_date,
last_update_login
)
VALUES
(
p_layer_provider_code, p_layer_header_id,
p_program_unit_id, p_program_unit_lp_code,
p_tps_program_unit_id, p_tps_program_unit_lp_code,
p_condition_type,
p_description,
l_user_id, SYSDATE,
l_user_id, SYSDATE,
l_login_id
);
END insert_row;
PROCEDURE NAME: update_row
PURPOSE: Updates a record into VEA_LAYER_HEADERS table
========================================================================*/
PROCEDURE
update_row
(
p_layer_provider_code IN vea_layer_headers.layer_provider_code%TYPE,
p_layer_header_id IN vea_layer_headers.layer_header_id%TYPE,
p_program_unit_id IN vea_layer_headers.program_unit_id%TYPE,
p_program_unit_lp_code IN vea_layer_headers.program_unit_lp_code%TYPE,
p_tps_program_unit_id IN vea_layer_headers.tps_program_unit_id%TYPE,
p_tps_program_unit_lp_code IN vea_layer_headers.tps_program_unit_lp_code%TYPE,
p_condition_type IN vea_layer_headers.condition_type%TYPE,
p_description IN vea_layer_headers.description%TYPE,
p_old_layer_header_id IN vea_layer_headers.layer_header_id%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
vea_parameter_mappings_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_layer_header_id => p_old_layer_header_id
);
UPDATE vea_layers
SET layer_header_id = p_layer_header_id
WHERE p_layer_provider_code = p_layer_provider_code
AND p_layer_header_id = p_old_layer_header_id;
vea_layers_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_layer_header_id => p_layer_header_id
);
insert_row
(
p_layer_provider_code => p_layer_provider_code,
p_layer_header_id => p_layer_header_id,
p_program_unit_id => p_program_unit_id,
p_program_unit_lp_code => p_program_unit_lp_code,
p_tps_program_unit_id => p_tps_program_unit_id,
p_tps_program_unit_lp_code => p_tps_program_unit_lp_code,
p_condition_type => p_condition_type,
p_description => p_description
);
UPDATE vea_layer_headers
SET
tps_program_unit_id = p_tps_program_unit_id,
tps_program_unit_lp_code = p_tps_program_unit_lp_code,
program_unit_id = p_program_unit_id,
program_unit_lp_code = p_program_unit_lp_code,
condition_type = p_condition_type,
description = p_description,
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 program_unit_id = p_program_unit_id
--AND program_unit_lp_code = p_program_unit_lp_code
AND layer_header_id = p_layer_header_id;
END update_row;
PROCEDURE NAME: delete_rows
PURPOSE: Queries all records which has at least one layer corresponding
to the specified TP Layer
For each layer header,
- delete layer header ( and all parameter mappings for it )
if
- it has at least one layer which contains customizations
for any of the customizable program units within the
specified application.
========================================================================*/
PROCEDURE
delete_rows
(
p_layer_provider_code IN vea_layer_headers.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,
x_layer_header_count OUT NOCOPY NUMBER
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
SELECT distinct layer_header_id
FROM vea_layers_v
WHERE layer_provider_code = p_layer_provider_code
AND tp_layer_id = p_tp_layer_id;
SELECT 'x'
FROM vea_layer_headers LH,
vea_program_units PU,
vea_packages PK
WHERE LH.layer_provider_code = p_layer_provider_code
AND LH.layer_header_id = p_layer_header_id
AND PU.program_unit_id = LH.program_unit_id
AND PU.layer_provider_code = LH.program_unit_lp_code
AND PK.layer_provider_code = PU.layer_provider_code
AND PK.package_id = PU.package_id
AND PK.tpa_flag = 'Y'
AND PU.tpa_program_unit_id IS NOT NULL
AND PK.application_short_name = NVL(p_application_short_name,PK.application_short_name);
vea_layers_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_layer_header_id => layer_header_rec.layer_header_id,
p_tp_layer_id => p_tp_layer_id,
x_layer_count => l_layer_count
);
vea_parameter_mappings_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_layer_header_id => layer_header_rec.layer_header_id
);
DELETE vea_layer_headers
WHERE layer_provider_code = p_layer_provider_code
AND layer_header_id = layer_header_rec.layer_header_id;
END delete_rows;
It inserts/updates a record in VEA_LAYER_HEADERS 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_layer_headers.layer_header_id%TYPE,
p_layer_provider_code IN vea_layer_headers.layer_provider_code%TYPE,
p_program_unit_id IN vea_layer_headers.program_unit_id%TYPE,
p_program_unit_lp_code IN vea_layer_headers.program_unit_lp_code%TYPE,
p_tps_program_unit_id IN vea_layer_headers.tps_program_unit_id%TYPE,
p_tps_program_unit_lp_code IN vea_layer_headers.tps_program_unit_lp_code%TYPE,
p_condition_type IN vea_layer_headers.condition_type%TYPE,
p_description IN vea_layer_headers.description%TYPE,
p_id IN vea_layer_headers.layer_header_id%TYPE := NULL,
p_package_name IN vea_packages.name%TYPE,
p_pkg_app_name IN vea_packages.application_short_name%TYPE,
p_pkg_cs_flag IN vea_packages.client_server_flag%TYPE,
p_program_unit_name IN vea_program_units.name%TYPE,
p_tps_package_name IN vea_packages.name%TYPE,
p_tps_program_unit_name IN vea_program_units.name%TYPE,
p_tpsPkg_app_name IN vea_packages.application_short_name%TYPE,
p_tpsPkg_cs_flag IN vea_packages.client_server_flag%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'process';
select tpa_program_unit_id
from vea_program_units
where program_unit_id = p_pu_id
and layer_provideR_code = p_lpc;
SELECT layer_header_id
FROM vea_layer_headers
WHERE layer_provider_code = p_layer_provider_code
AND program_unit_id = p_program_unit_id
AND program_unit_lp_code = p_program_unit_lp_code
AND NVL(condition_type,'!') = NVL(p_condition_type,'!');
SELECT NVL( p_id, vea_layer_headers_s.NEXTVAL )
INTO l_layer_header_id
FROM DUAL;
SELECT vea_layer_headers_s.NEXTVAL
INTO l_layer_header_id
FROM DUAL;
insert_row
(
p_layer_provider_code => p_layer_provider_code,
p_layer_header_id => l_layer_header_id,
p_program_unit_id => l_program_unit_id,
p_program_unit_lp_code => p_program_unit_lp_code,
p_tps_program_unit_id => l_tps_program_unit_id,
p_tps_program_unit_lp_code => p_tps_program_unit_lp_code,
p_condition_type => p_condition_type,
p_description => p_description
);
update_row
(
p_layer_provider_code => p_layer_provider_code,
p_layer_header_id => l_layer_header_id,
p_program_unit_id => l_program_unit_id,
p_program_unit_lp_code => p_program_unit_lp_code,
p_tps_program_unit_id => l_tps_program_unit_id,
p_tps_program_unit_lp_code => p_tps_program_unit_lp_code,
p_condition_type => p_condition_type,
p_description => p_description,
p_old_layer_header_id => l_layer_header_id
);