[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PURPOSE: Handles processing at the beginning of layer merge. It deletes
all information in the repository pertaining to the specified
TP layer developed by the specified layer provider for the
specified TP application. It also sets a variable to indicate
that layer merge process has begun.
========================================================================*/
PROCEDURE
preProcess
(
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_layers.layer_provider_code%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
p_tp_layer_name IN vea_tp_layers.name%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'preProcess';
g_tpLyr_fileId_dbId_tbl.delete;
g_tpLyr_fileId_dbId_ext_tbl.delete;
g_PU_fileId_dbId_tbl.delete;
g_PU_fileId_dbId_ext_tbl.delete;
g_pend_puId_tpaPUId_tbl.delete;
g_pend_puId_tpaPUId_ext_tbl.delete;
vea_tp_layers_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code,
p_tp_layer_id => l_tp_layer_id,
p_tp_layer_name => p_tp_layer_name,
p_application_short_name => p_application_short_name
);
PURPOSE: Handles processing at the end of layer merge. It deletes any
TP layers not licensed to current customer. It also sets a
variable to indicate that layer merge process has ended.
MODIFIED: This procedure has been modified by Ravi (rvishnuv) on 09/29/2000.
An additional parameter "layer_provider_name" has been added to
the existing parameters list for the procedure.
========================================================================*/
PROCEDURE
postProcess
(
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_layers.layer_provider_code%TYPE,
p_application_short_name IN vea_packages.application_short_name%TYPE,
p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
p_tp_layer_name IN vea_tp_layers.name%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'postProcess';
VEA_PROGRAM_UNITS_SV.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
);
UPDATE vea_program_units
SET tpa_program_unit_id = l_tpa_program_unit_id
WHERE layer_provider_code = p_layer_provider_code
AND program_unit_id = l_program_unit_id;
VEA_PROGRAM_UNITS_SV.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
);
UPDATE vea_program_units
SET tpa_program_unit_id = l_tpa_program_unit_id
WHERE layer_provider_code = p_layer_provider_code
AND program_unit_id = l_program_unit_id;
vea_tp_layers_sv.deleteUnlicensedLayers
(
p_layer_provider_code => p_layer_provider_code
);
vea_layerproviders_sv.delete_rows
(
p_layer_provider_code => p_layer_provider_code
);
vea_packages_sv.deleteUnreferencedPackages;
SELECT LA.layer_provider_code,
LA.layer_id,
DECODE(TLA.ACTIVE_FLAG, 'N', 'N', LA.ACTIVE_FLAG) IS_LAYER_ACTIVE
FROM vea_layers LA,
vea_program_units PU,
vea_layer_headers LH,
vea_program_units APU,
vea_packages PK,
vea_tp_layers TLA
WHERE PU.program_unit_id = p_program_unit_id
AND PU.layer_provider_code = p_layer_provider_code
AND LH.program_unit_id = PU.program_unit_id
AND LH.program_unit_lp_code = PU.layer_provider_code
AND LA.layer_provider_code = LH.layer_provider_code
AND LA.layer_header_id = LH.layer_header_id
AND APU.layer_provider_code = LA.program_unit_lp_code
AND APU.program_unit_id = LA.new_program_unit_id
AND PK.layer_provider_code = APU.layer_provider_code
AND PK.package_id = APU.package_id
AND TLA.layer_provider_code = PK.layer_provider_code
AND TLA.tp_layer_id = PK.tp_layer_id;
PROCEDURE NAME: insert_ece_xref_category
PURPOSE: Inserts a code conversion category into EDI table
ECE_XREF_CATEGORIES.
========================================================================*/
PROCEDURE insert_ece_xref_category
(
p_category_id IN ece_xref_categories.xref_category_id%TYPE,
p_category_code IN ece_xref_categories.xref_category_code%TYPE,
p_description IN ece_xref_categories.description%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'insert_ece_xref_category';
INSERT INTO ece_xref_categories
(
xref_category_id,
xref_category_code,
description,
key1_used_flag,
key2_used_flag,
key3_used_flag,
key4_used_flag,
key5_used_flag,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
p_category_id,
p_category_code,
p_description,
'Y',
'N',
'N',
'N',
'N',
vea_tpa_util_pvt.get_user_id,
SYSDATE,
SYSDATE,
vea_tpa_util_pvt.get_user_id,
vea_tpa_util_pvt.get_login_id
);
END insert_ece_xref_category;
PURPOSE: Inserts a code conversion category into EDI table
ECE_XREF_CATEGORIES, if not existing already.
========================================================================*/
FUNCTION process_ece_xref_category
(
p_category_code IN ece_xref_categories.xref_category_code%TYPE,
p_description IN ece_xref_categories.description%TYPE DEFAULT NULL
)
RETURN ece_xref_categories.xref_category_id%TYPE
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'process_ece_xref_category';
SELECT xref_category_id
FROM ece_xref_categories
WHERE xref_category_code = p_category_code;
SELECT ECE_XREF_CATEGORIES_S1.nextval
INTO l_xref_category_id
FROM DUAL;
vea_tpa_util_pvt.insert_ece_xref_category
(
p_category_id => l_xref_category_id,
p_category_code => p_category_code,
p_description => p_description
);
PROCEDURE NAME: insert_ece_xref_data
PURPOSE: Inserts a code conversion value into EDI table
ECE_XREF_DATA.
========================================================================*/
PROCEDURE insert_ece_xref_data
(
p_data_id IN ece_xref_data.xref_data_id%TYPE,
p_category_id IN ece_xref_data.xref_category_id%TYPE,
p_category_code IN ece_xref_data.xref_category_code%TYPE,
p_ext_value IN ece_xref_data.xref_ext_value1%TYPE,
p_key1 IN ece_xref_data.xref_key1%TYPE,
p_description IN ece_xref_data.description%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'insert_ece_xref_data';
INSERT INTO ece_xref_data
(
xref_data_id,
xref_category_id,
xref_category_code,
description,
xref_key1,
xref_int_value,
xref_ext_value1,
direction,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
p_data_id,
p_category_id,
p_category_code,
p_description,
p_key1,
p_ext_value,
p_ext_value,
'BOTH',
vea_tpa_util_pvt.get_user_id,
SYSDATE,
SYSDATE,
vea_tpa_util_pvt.get_user_id,
vea_tpa_util_pvt.get_login_id
);
END insert_ece_xref_data;
PURPOSE: Inserts a code conversion value into EDI table ECE_XREF_DATA,
if record does not existing for the correspoding category,
layer provider code(key1) and external value(branching
condition value). IF a record is inserted then appends a
message to log file informing user to specify code conversion
for the inserted record.
========================================================================*/
PROCEDURE process_ece_xref_data
(
p_category_id IN ece_xref_data.xref_category_id%TYPE,
p_category_code IN ece_xref_data.xref_category_code%TYPE,
p_ext_value IN ece_xref_data.xref_ext_value1%TYPE,
p_key1 IN ece_xref_data.xref_key1%TYPE,
p_description IN ece_xref_data.description%TYPE DEFAULT NULL
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'process_ece_xref_data';
SELECT xref_data_id
FROM ece_xref_data
WHERE xref_category_id = p_category_id
AND xref_ext_value1 = p_ext_value
AND xref_key1 = p_key1;
SELECT ECE_XREF_DATA_S1.nextval
INTO l_xref_data_id
FROM DUAL;
vea_tpa_util_pvt.insert_ece_xref_data
(
p_data_id => l_xref_data_id,
p_category_id => p_category_id,
p_category_code => p_category_code,
p_ext_value => p_ext_value,
p_key1 => p_key1,
p_description => p_description
);
SELECT PA.name
FROM vea_parameters PA,
vea_program_units PU,
vea_layer_headers LH
WHERE PA.parameter_id = p_tps_parameter_id
AND LH.layer_provider_code = p_layer_provider_code
AND LH.layer_header_id = p_layer_header_id
AND PU.program_unit_id = LH.tps_program_unit_id
AND PU.layer_provider_code = LH.tps_program_unit_lp_code
AND PA.program_unit_id = PU.program_unit_id
AND PA.layer_provider_code = PU.layer_provider_code;
- Inserts EDI code category, if not existing already.
- Inserts EDI code conversion value, if not existing already.
This procedure is called during layer merge.
========================================================================*/
PROCEDURE
process_code_conversion
(
p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
p_layer_header_id IN vea_layers.layer_header_id%TYPE,
p_tps_parameter_id IN vea_layers.tps_parameter1_id%TYPE,
p_tps_parameter_value IN vea_layers.tps_parameter1_value%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'process_code_conversion';
PROCEDURE update_lookup_values
(
p_lookup_type IN fnd_lookup_values.lookup_type%TYPE,
p_new_lookup_code IN fnd_lookup_values.lookup_code%TYPE,
p_current_lookup_code IN fnd_lookup_values.lookup_code%TYPE,
p_meaning IN fnd_lookup_values.meaning%TYPE,
p_description IN fnd_lookup_values.description%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'update_lookup_values';
UPDATE fnd_lookup_values
SET lookup_code = p_new_lookup_code,
meaning = p_meaning,
description = p_description
WHERE lookup_type = p_lookup_type
AND lookup_code = p_current_lookup_code;
END update_lookup_values;
PROCEDURE insert_lookup_values
(
p_lookup_type IN fnd_lookup_values.lookup_type%TYPE,
p_lookup_code IN fnd_lookup_values.lookup_code%TYPE,
p_meaning IN fnd_lookup_values.meaning%TYPE,
p_description IN fnd_lookup_values.description%TYPE
)
IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'insert_lookup_values';
FND_LOOKUP_VALUES_PKG.INSERT_ROW
(
x_rowid => l_row_id,
x_lookup_type => p_lookup_type,
x_security_group_id => FND_GLOBAL.SECURITY_GROUP_ID,
x_view_application_id => 0,
x_lookup_code => p_lookup_code,
x_tag => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active => SYSDATE,
x_end_date_active => NULL,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => p_meaning,
x_description => p_description,
x_creation_date => SYSDATE,
x_created_by => FND_GLOBAL.USER_ID,
x_last_update_date => SYSDATE,
x_last_updated_by => FND_GLOBAL.USER_ID,
x_last_update_login => FND_GLOBAL.LOGIN_ID
);
END insert_lookup_values;
g_programUnit_Tbl.DELETE;
g_programUnitExt_Tbl.DELETE;
g_layer_Tbl.DELETE;