APPS.OZF_VOLUME_OFFER_QUAL_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 53

    SELECT offer_id FROM ozf_offers
    WHERE qp_list_header_id = p_qp_list_header_id
    AND offer_type = 'VOLUME_OFFER';

Line: 92

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);

Line: 107

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;

Line: 342

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);

Line: 359

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';

Line: 393

SELECT qualifier_grouping_no FROM qp_qualifiers WHERE qualifier_id = p_qp_qualifier_id;

Line: 397

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);

Line: 407

SELECT * FROM ozf_qualifier_market_option
WHERE qp_qualifier_id = p_qualifier_id;

Line: 413

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;

Line: 420

      SAVEPOINT update_volume_offer_qual_pvt;

Line: 453

l_qualifiers_tbl(1).operation := 'UPDATE';

Line: 455

debug_message('Calling update');

Line: 507

        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
            );

Line: 595

        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
            );

Line: 613

            OZF_Utility_PVT.Error_Message('OZF_CANT_UPDATE_INT');

Line: 636

    ROLLBACK TO update_volume_offer_qual_pvt;

Line: 644

    ROLLBACK TO update_volume_offer_qual_pvt;

Line: 652

    ROLLBACK TO update_volume_offer_qual_pvt;

Line: 662

END update_vo_qualifier;

Line: 666

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;

Line: 678

l_api_name                  CONSTANT VARCHAR2(30) := 'Delete_volume_offer_qualifier';

Line: 682

SELECT qualifier_grouping_no,list_header_id FROM qp_qualifiers
WHERE qualifier_id = p_qualifier_id;

Line: 695

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);

Line: 703

SELECT qualifier_market_option_id, object_version_number FROM ozf_qualifier_market_option
WHERE qp_qualifier_id = p_qualifier_id;

Line: 708

SELECT qualifier_context FROM qp_qualifiers
WHERE qualifier_id = cp_qualifierId;

Line: 712

      SAVEPOINT Delete_volume_offer_qual_pvt;

Line: 742

    l_qualifiers_rec.operation := 'DELETE';

Line: 785

            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
                );

Line: 799

                OZF_Utility_PVT.Error_Message('OZF_OFFR_MO_CANT_DELETE_MO');

Line: 814

        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
        );

Line: 846

    ROLLBACK TO Delete_volume_offer_qual_pvt;

Line: 854

    ROLLBACK TO Delete_volume_offer_qual_pvt;

Line: 862

    ROLLBACK TO Delete_volume_offer_qual_pvt;

Line: 873

    END Delete_vo_qualifier;