The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT offer_id FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id
AND offer_type = 'VOLUME_OFFER';
SELECT 'N' FROM dual WHERE EXISTS(SELECT 'X' FROM ozf_offr_market_options WHERE group_number = p_group_number and qp_list_header_id = p_qp_list_header_id);
SELECT offer_market_option_id FROM ozf_offr_market_options WHERE qp_list_header_id = p_qp_list_header_id AND group_number = p_group_number;
SELECT 'Y' FROM dual WHERE EXISTS (SELECT 'X' FROM qp_qualifiers WHERE list_header_id = p_qp_list_header_id AND qualifier_grouping_no = p_group_number);
1. BEFORE update get the old group number of the qualifier
check if members exist in the new group of the qualifier
2. Update the qualifier
3. check if members exist in the old group of the
4. If members did not exist in new group before creation of the qualifier
create a new market option
5. If members no longer exist in the old group delete the market option associated with the old group
6.Update the Market OPtion - QUalifier interface table to point to the new market_option
*/
PROCEDURE update_vo_qualifier
(
p_api_version_number 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_qualifiers_rec IN OZF_OFFER_PVT.qualifiers_Rec_Type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_vo_qualifier';
SELECT qualifier_grouping_no FROM qp_qualifiers WHERE qualifier_id = p_qp_qualifier_id;
SELECT offer_market_option_id, object_version_number FROM ozf_offr_market_options
WHERE offer_market_option_id = (SELECT offer_market_option_id FROM ozf_qualifier_market_option WHERE qp_qualifier_id = p_qp_qualifier_id);
SELECT * FROM ozf_qualifier_market_option
WHERE qp_qualifier_id = p_qualifier_id;
SELECT offer_market_option_id FROM ozf_offr_market_options WHERE qp_list_header_id = p_qp_list_header_id
AND group_number = p_group_number;
SAVEPOINT update_volume_offer_qual_pvt;
l_qualifiers_tbl(1).operation := 'UPDATE';
debug_message('Calling update');
OZF_offer_Market_Options_PVT.Delete_market_options(
p_api_version_number => p_api_version_number
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_offer_market_option_id => l_mkt_opt_dtail.offer_market_option_id
, p_object_version_number => l_mkt_opt_dtail.object_version_number
);
OZF_QUAL_MARKET_OPTION_PVT.update_qual_market_options(
p_api_version_number => p_api_version_number
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_qual_mo_rec => l_qual_mo_rec
);
OZF_Utility_PVT.Error_Message('OZF_CANT_UPDATE_INT');
ROLLBACK TO update_volume_offer_qual_pvt;
ROLLBACK TO update_volume_offer_qual_pvt;
ROLLBACK TO update_volume_offer_qual_pvt;
END update_vo_qualifier;
PROCEDURE Delete_vo_qualifier(
p_api_version_number 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_qualifier_id IN NUMBER
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_volume_offer_qualifier';
SELECT qualifier_grouping_no,list_header_id FROM qp_qualifiers
WHERE qualifier_id = p_qualifier_id;
SELECT offer_market_option_id, object_version_number FROM ozf_offr_market_options
WHERE offer_market_option_id = (SELECT offer_market_option_id FROM ozf_qualifier_market_option WHERE qp_qualifier_id = p_qp_qualifier_id);
SELECT qualifier_market_option_id, object_version_number FROM ozf_qualifier_market_option
WHERE qp_qualifier_id = p_qualifier_id;
SELECT qualifier_context FROM qp_qualifiers
WHERE qualifier_id = cp_qualifierId;
SAVEPOINT Delete_volume_offer_qual_pvt;
l_qualifiers_rec.operation := 'DELETE';
OZF_offer_Market_Options_PVT.Delete_market_options(
p_api_version_number => p_api_version_number
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_offer_market_option_id => l_mkt_opt_dtail.offer_market_option_id
, p_object_version_number => l_mkt_opt_dtail.object_version_number
);
OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_CANT_DELETE_MO');
OZF_QUAL_MARKET_OPTION_PVT.Delete_qual_market_options(
p_api_version_number => p_api_version_number
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_qualifier_market_option_id => l_qual_mo.qualifier_market_option_id
, p_object_version_number => l_qual_mo.object_version_number
);
ROLLBACK TO Delete_volume_offer_qual_pvt;
ROLLBACK TO Delete_volume_offer_qual_pvt;
ROLLBACK TO Delete_volume_offer_qual_pvt;
END Delete_vo_qualifier;