[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: insert_row
PURPOSE: Inserts a record into VEA_PROGRAM_UNITS table
========================================================================*/
PROCEDURE
insert_row
(
p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
p_package_id IN vea_program_units.program_unit_id%TYPE,
p_program_unit_type IN vea_program_units.program_unit_type%TYPE,
p_public_flag IN vea_program_units.public_flag%TYPE,
p_customizable_flag IN vea_program_units.customizable_flag%TYPE,
p_tps_flag IN vea_program_units.tps_flag%TYPE,
p_name IN vea_program_units.name%TYPE,
p_label IN vea_program_units.label%TYPE,
p_return_type IN vea_program_units.return_type%TYPE,
p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE,
p_description IN vea_program_units.description%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
INSERT INTO vea_program_units
(
layer_provider_code, program_unit_id,
package_id, program_unit_type,
name, label,
public_flag, customizable_flag,
tps_flag,
return_type, tpa_program_unit_id,
description,
created_by, creation_date,
last_updated_by, last_update_date,
last_update_login
)
VALUES
(
p_layer_provider_code, p_program_unit_id,
p_package_id, p_program_unit_type,
p_name, p_label,
p_public_flag, p_customizable_flag,
p_tps_flag,
p_return_type, p_tpa_program_unit_id,
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_PROGRAM_UNITS table
========================================================================*/
PROCEDURE
update_row
(
p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
p_package_id IN vea_program_units.program_unit_id%TYPE,
p_program_unit_type IN vea_program_units.program_unit_type%TYPE,
p_public_flag IN vea_program_units.public_flag%TYPE,
p_customizable_flag IN vea_program_units.customizable_flag%TYPE,
p_tps_flag IN vea_program_units.tps_flag%TYPE,
p_name IN vea_program_units.name%TYPE,
p_label IN vea_program_units.label%TYPE,
p_return_type IN vea_program_units.return_type%TYPE,
p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE,
p_description IN vea_program_units.description%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
UPDATE vea_program_units
SET package_id = p_package_id,
program_unit_type = p_program_unit_type,
name = p_name,
label = p_label,
public_flag = p_public_flag,
customizable_flag = p_customizable_flag,
tps_flag = p_tps_flag,
return_type = p_return_type,
tpa_program_unit_id = p_tpa_program_unit_id,
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;
END update_row;
PROCEDURE NAME: delete_row
PURPOSE: Deletes all parameters of the specified program unit and
program unit itself.
========================================================================*/
PROCEDURE
delete_row
(
p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
p_program_unit_id IN vea_program_units.program_unit_id%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
vea_parameters_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_program_unit_id => p_program_unit_id
);
DELETE vea_program_units
WHERE layer_provider_code = p_layer_provider_code
AND program_unit_id = p_program_unit_id;
END delete_row;
PROCEDURE NAME: delete_rows
PURPOSE: Deletes all packages developed by specified layer provider and
used in the specified TP layer of any customizable program
units of the specified application.
It first queries all program units developed by specified layer
provider and belonging to the specified package.
For each program unit,
- it deletes the program units ( and their parameters ),
if
- it unit is not a TPS program unit
AND it is used in the specified TP layer in any
customizable program units of the specified application.
OR
- if it is a TPS program unit and not used anywhere.
========================================================================*/
PROCEDURE
delete_rows
(
p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
p_package_id IN vea_packages.package_id%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE,
x_program_unit_count OUT NOCOPY NUMBER,
x_tps_program_unit_count OUT NOCOPY NUMBER
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
SELECT program_unit_id,
layer_provider_code,
tps_flag
FROM vea_program_units
WHERE layer_provider_code = p_layer_provider_code
AND package_id = p_package_id;
SELECT 'x'
FROM vea_layer_headers
WHERE tps_program_unit_lp_code = p_tps_program_unit_lp_code
AND tps_program_unit_id = p_tps_program_unit_id;
SELECT PK.application_short_name
FROM vea_program_units LPU,
vea_layer_headers LH,
vea_packages PK,
vea_program_units PU,
vea_layers_v LA
WHERE LA.layer_provider_code = p_layer_provider_code
AND LA.tp_layer_id = p_tp_layer_id
AND LH.layer_provider_code = LA.layer_provider_code
AND LH.layer_header_id = LA.layer_header_id
AND PU.program_unit_id = LH.program_unit_id
AND PU.layer_provider_code = LH.program_unit_lp_code
AND PK.package_id = PU.package_id
AND PK.layer_provider_code = PU.layer_provider_code
AND PK.tpa_flag = 'Y'
AND PU.tpa_program_unit_id IS NOT NULL
AND LPU.package_id = p_package_id
AND LPU.program_unit_id = p_program_unit_id
AND LPU.program_unit_id = LA.new_program_unit_id
AND LPU.layer_provider_code = LA.program_unit_lp_code;
delete_row
(
p_layer_provider_code => program_unit_rec.layer_provider_code,
p_program_unit_id => program_unit_rec.program_unit_id
);
delete_row
(
p_layer_provider_code => program_unit_rec.layer_provider_code,
p_program_unit_id => program_unit_rec.program_unit_id
);
END delete_rows;
SELECT PU.name program_unit_name,
PK.name package_name
FROM vea_program_units PU,
vea_packages PK
WHERE PU.layer_provider_code = p_layer_provider_code
AND PU.program_unit_id = p_program_unit_id
AND PK.layer_provider_code = PU.layer_provider_code
AND PK.package_id = PU.package_id;
PROCEDURE NAME: deleteUnreferencedProgramUnits
PURPOSE:
========================================================================*/
PROCEDURE
deleteUnreferencedProgramUnits
(
p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'deleteUnreferencedProgramUnits';
SELECT program_unit_id
FROM vea_program_units
WHERE tpa_program_unit_id = p_tpa_program_unit_id
AND layer_provider_code = p_layer_provider_code;
delete_row
(
p_layer_provider_code => p_layer_provider_code,
p_program_unit_id => program_unit_rec.program_unit_id
);
END deleteUnreferencedProgramUnits;
SELECT program_unit_id
FROM vea_program_units
WHERE layer_provider_code = p_layer_provider_code
AND package_id = p_package_id
AND UPPER(name) = UPPER(p_name);
It inserts/updates a record in VEA_PROGRAM_UNITS 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_program_units.program_unit_id%TYPE,
p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
p_package_id IN vea_program_units.program_unit_id%TYPE,
p_program_unit_type IN vea_program_units.program_unit_type%TYPE,
p_public_flag IN vea_program_units.public_flag%TYPE,
p_customizable_flag IN vea_program_units.customizable_flag%TYPE,
p_tps_flag IN vea_program_units.tps_flag%TYPE,
p_name IN vea_program_units.name%TYPE,
p_label IN vea_program_units.label%TYPE,
p_return_type IN vea_program_units.return_type%TYPE,
p_tpa_program_unit_id IN vea_program_units.tpa_program_unit_id%TYPE,
p_description IN vea_program_units.description%TYPE,
p_id IN vea_program_units.program_unit_id%TYPE := NULL,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE := NULL
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'process';
SELECT program_unit_id, tpa_program_unit_id
FROM vea_program_units
WHERE layer_provider_code = p_layer_provider_code
AND package_id = p_package_id
AND UPPER(name) = UPPER(p_name);
SELECT NVL( p_id, vea_program_units_s.NEXTVAL )
INTO l_program_unit_id
FROM DUAL;
SELECT vea_program_units_s.NEXTVAL
INTO l_program_unit_id
FROM DUAL;
deleteUnreferencedProgramUnits
(
p_layer_provider_code => p_layer_provider_code,
p_program_unit_id => l_program_unit_id,
p_tpa_program_unit_id => l_tpa_program_unit_id
);
insert_row
(
p_layer_provider_code => p_layer_provider_code,
p_program_unit_id => l_program_unit_id,
p_package_id => l_package_id,
p_program_unit_type => p_program_unit_type,
p_name => p_name,
p_label => p_label,
p_public_flag => p_public_flag,
p_customizable_flag => p_customizable_flag,
p_tps_flag => p_tps_flag,
p_return_type => p_return_type,
p_tpa_program_unit_id => l_tpa_program_unit_id,
--p_tpa_program_unit_id => p_tpa_program_unit_id,
p_description => p_description
);
update_row
(
p_layer_provider_code => p_layer_provider_code,
p_program_unit_id => l_program_unit_id,
p_package_id => l_package_id,
p_program_unit_type => p_program_unit_type,
p_name => p_name,
p_label => p_label,
p_public_flag => p_public_flag,
p_customizable_flag => p_customizable_flag,
p_tps_flag => p_tps_flag,
p_return_type => p_return_type,
p_tpa_program_unit_id => l_tpa_program_unit_id,
--p_tpa_program_unit_id => p_tpa_program_unit_id,
p_description => p_description
);