[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT package_id, name
FROM vea_packages
WHERE specification_filename = p_specification_filename
AND layer_provider_code = p_layer_provider_code;
SELECT package_id, name
FROM vea_packages
WHERE body_filename = p_body_filename
AND layer_provider_code = p_layer_provider_code;
SELECT package_id, application_short_name
FROM vea_packages
WHERE specification_filename = p_specification_filename
AND layer_provider_code = p_layer_provider_code;
PROCEDURE NAME: insert_row
PURPOSE: Inserts a record into VEA_PACKAGES table
========================================================================*/
PROCEDURE
insert_row
(
p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
p_package_id IN vea_packages.package_id%TYPE,
p_client_server_flag IN vea_packages.client_server_flag%TYPE,
p_generate_flag IN vea_packages.generate_flag%TYPE,
p_tpa_flag IN vea_packages.tpa_flag%TYPE,
p_name IN vea_packages.name%TYPE,
p_specification_filename IN vea_packages.specification_filename%TYPE,
p_body_filename IN vea_packages.body_filename%TYPE,
p_label IN vea_packages.label%TYPE,
p_version_number IN vea_packages.version_number%TYPE,
p_description IN vea_packages.description%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE,
p_tp_layer_id IN vea_packages.tp_layer_id%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
INSERT INTO vea_packages
(
layer_provider_code, package_id,
client_server_flag,
name, label,
generate_flag, tpa_flag,
specification_filename, body_filename,
--version_number,
description,
application_short_name, tp_layer_id,
created_by, creation_date,
last_updated_by, last_update_date,
last_update_login
)
VALUES
(
p_layer_provider_code, p_package_id,
p_client_server_flag,
UPPER(p_name), p_label,
p_generate_flag, p_tpa_flag,
p_specification_filename, p_body_filename,
--p_version_number,
p_description,
UPPER(p_application_short_name), p_tp_layer_id,
l_user_id, SYSDATE,
l_user_id, SYSDATE,
l_login_id
);
END insert_row;
PROCEDURE NAME: update_row
PURPOSE: Updates a record into VEA_PACKAGES table
========================================================================*/
PROCEDURE
update_row
(
p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
p_package_id IN vea_packages.package_id%TYPE,
p_client_server_flag IN vea_packages.client_server_flag%TYPE,
p_generate_flag IN vea_packages.generate_flag%TYPE,
p_tpa_flag IN vea_packages.tpa_flag%TYPE,
p_name IN vea_packages.name%TYPE,
p_specification_filename IN vea_packages.specification_filename%TYPE,
p_body_filename IN vea_packages.body_filename%TYPE,
p_label IN vea_packages.label%TYPE,
p_version_number IN vea_packages.version_number%TYPE,
p_description IN vea_packages.description%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE,
p_tp_layer_id IN vea_packages.tp_layer_id%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
UPDATE vea_packages
SET client_server_flag = p_client_server_flag,
name = p_name,
label = p_label,
generate_flag = p_generate_flag,
tpa_flag = p_tpa_flag,
specification_filename = p_specification_filename,
body_filename = p_body_filename,
--version_number = p_version_number,
description = p_description,
application_short_name = p_application_short_name,
tp_layer_id = p_tp_layer_id,
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 package_id = p_package_id;
END update_row;
PROCEDURE NAME: deleteUnreferencedPackages
PURPOSE: Deletes all TPA packages which do not have any program units.
========================================================================*/
PROCEDURE
deleteUnreferencedPackages
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'deleteUnreferencedPackages';
delete vea_packages PK
where tpa_flag = 'Y'
and not exists ( select 1
from vea_program_units PU
where PU.layer_provider_code = PK.layer_provider_code
AND PU.package_id = PK.package_id
);
END deleteUnreferencedPackages;
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 packages developed by specified layer
provider and belonging to the specified TP Layer.
For each package,
- it deletes all 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.
- It deletes the package, if it has no more program units.
- It updates the TP_LAYER_ID to null, if it has only TPS
program units.
========================================================================*/
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_package_count OUT NOCOPY NUMBER
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
SELECT package_id
FROM vea_packages
WHERE layer_provider_code = p_layer_provider_code
AND tp_layer_id = p_tp_layer_id;
vea_program_units_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => p_tp_layer_id,
p_package_id => package_rec.package_id,
p_application_short_name => p_application_short_name,
x_program_unit_count => l_program_unit_count,
x_tps_program_unit_count => l_tps_program_unit_count
);
DELETE vea_packages
WHERE layer_provider_code = p_layer_provider_code
AND package_id = package_rec.package_id;
UPDATE vea_packages
SET tp_layer_id = null
WHERE layer_provider_code = p_layer_provider_code
AND package_id = package_rec.package_id;
END delete_rows;
PROCEDURE NAME: updateVersionNumber
PURPOSE: Increments version number by 1 for the specified package.
========================================================================*/
PROCEDURE
updateVersionNumber
(
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,
p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
p_package_id IN vea_packages.package_id%TYPE,
x_version_number OUT NOCOPY VARCHAR2,
p_user_name IN VARCHAR2
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'updateVersionNumber';
SELECT version_number, name
FROM vea_packages
WHERE layer_provider_code = p_layer_provider_code
AND package_id = p_package_id;
select SUBSTRB(text, INSTRB(text, ' ',1,3) + 1 , INSTRB(text, ' ',1,4) - INSTRB(text, ' ',1,3))
into l_version_text
from all_source
where name = pkg_rec.name
and type ='PACKAGE'
and owner = UPPER(p_user_name)
and text like '%$Head%';
UPDATE vea_packages
SET version_number = l_version_number,
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 package_id = p_package_id;
END updateVersionNumber;
PROCEDURE NAME: updateVersionNumber
PURPOSE: Increments version number by 1 for all the packages in the
specified client-side library file.
========================================================================*/
PROCEDURE
updateVersionNumber
(
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,
p_application_short_name IN vea_packages.application_short_name%TYPE,
p_specification_filename IN vea_packages.specification_filename%TYPE,
x_version_number OUT NOCOPY VARCHAR2,
p_user_name IN VARCHAR2
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'updateVersionNumber';
SELECT package_id,
layer_provider_code,
version_number,
name
FROM vea_packages
WHERE specification_filename = p_specification_filename;
select SUBSTRB(text, INSTRB(text, ' ',1,3) + 1 , INSTRB(text, ' ',1,4) - INSTRB(text, ' ',1,3))
into l_version_text
from all_source
where name = pkg_rec.name
and type ='PACKAGE'
and owner = UPPER(p_user_name)
and text like '%$Head%';
UPDATE vea_packages
SET version_number = l_version_number,
last_updated_by = l_user_id,
last_update_date = SYSDATE,
last_update_login = l_login_id
WHERE layer_provider_code = pkg_rec.layer_provider_code
AND package_id = pkg_rec.package_id;
END updateVersionNumber;
SELECT package_id
FROM vea_packages
WHERE layer_provider_code = p_layer_provider_code
AND application_short_name = p_application_short_name
AND client_server_flag = p_client_server_flag
AND UPPER(name) = UPPER(p_name);
It inserts/updates a record in VEA_PACKAGES 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_packages.package_id%TYPE,
p_layer_provider_code IN vea_packages.layer_provider_code%TYPE,
p_client_server_flag IN vea_packages.client_server_flag%TYPE,
p_generate_flag IN vea_packages.generate_flag%TYPE,
p_tpa_flag IN vea_packages.tpa_flag%TYPE,
p_name IN vea_packages.name%TYPE,
p_specification_filename IN vea_packages.specification_filename%TYPE,
p_body_filename IN vea_packages.body_filename%TYPE,
p_label IN vea_packages.label%TYPE,
p_version_number IN vea_packages.version_number%TYPE,
p_description IN vea_packages.description%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE,
p_tp_layer_id IN vea_packages.tp_layer_id%TYPE,
p_id IN vea_packages.package_id%TYPE := NULL
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'process';
SELECT package_id
FROM vea_packages
WHERE layer_provider_code = p_layer_provider_code
AND application_short_name = p_application_short_name
AND client_server_flag = p_client_server_flag
AND name = p_name;
SELECT NVL( p_id, vea_packages_s.NEXTVAL )
INTO l_package_id
FROM DUAL;
SELECT vea_packages_s.NEXTVAL
INTO l_package_id
FROM DUAL;
insert_row
(
p_layer_provider_code => p_layer_provider_code,
p_package_id => l_package_id,
p_client_server_flag => p_client_server_flag,
p_name => p_name,
p_label => p_label,
p_generate_flag => p_generate_flag,
p_tpa_flag => p_tpa_flag,
p_specification_filename => p_specification_filename,
p_body_filename => p_body_filename,
p_version_number => p_version_number,
p_description => p_description,
p_application_short_name => p_application_short_name,
p_tp_layer_id => l_tp_layer_id
);
update_row
(
p_layer_provider_code => p_layer_provider_code,
p_package_id => l_package_id,
p_client_server_flag => p_client_server_flag,
p_name => p_name,
p_label => p_label,
p_generate_flag => p_generate_flag,
p_tpa_flag => p_tpa_flag,
p_specification_filename => p_specification_filename,
p_body_filename => p_body_filename,
p_version_number => p_version_number,
p_description => p_description,
p_application_short_name => p_application_short_name,
p_tp_layer_id => l_tp_layer_id
);