APPS.OZF_VOLUME_OFFER_ADJ SQL Statements

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

Line: 21

PROCEDURE update_vo_tier
(
  x_return_status         OUT NOCOPY  VARCHAR2
  , x_msg_count             OUT NOCOPY  NUMBER
  , x_msg_data              OUT NOCOPY  VARCHAR2
  , p_offerDiscountLineId IN NUMBER
  , p_offerAdjustmentId IN NUMBER
)
IS
l_vo_disc_rec OZF_Volume_Offer_disc_PVT.vo_disc_rec_type;

Line: 32

SELECT a.object_version_number
, a.offer_id
, a.parent_discount_line_id
, b.modified_discount
FROM ozf_offer_discount_lines a, ozf_offer_adjustment_tiers b
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.offer_discount_line_id = cp_offerDiscountLineId
AND b.offer_adjustment_id = cp_offerAdjustmentId;

Line: 45

update VO discount tiers
*/
x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 56

OZF_Volume_Offer_disc_PVT.Update_vo_discount(
    p_api_version_number         => 1.0
    , p_init_msg_list            => FND_API.G_FALSE
    , p_commit                   => FND_API.G_FALSE
    , p_validation_level         => FND_API.G_VALID_LEVEL_FULL
    , x_return_status            => x_return_status
    , x_msg_count                => x_msg_count
    , x_msg_data                 => x_msg_data
    , p_vo_disc_rec              => l_vo_disc_rec
);

Line: 66

END update_vo_tier;

Line: 68

PROCEDURE update_adj_vo_tiers
(
  x_return_status         OUT NOCOPY  VARCHAR2
  ,x_msg_count             OUT NOCOPY  NUMBER
  ,x_msg_data              OUT NOCOPY  VARCHAR2
  ,p_offerAdjustmentId   IN   NUMBER
)
IS
CURSOR c_adjustmentTiers(cp_offerAdjustmentId NUMBER)
IS
SELECT offer_discount_line_id
FROM ozf_offer_adjustment_tiers
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 88

        update_vo_tier
        (
          x_return_status          => x_return_status
          , x_msg_count             => x_msg_count
          , x_msg_data              => x_msg_data
          , p_offerDiscountLineId   => l_adjustmentTiers.offer_discount_line_id
          , p_offerAdjustmentId     => p_offerAdjustmentId
        );

Line: 102

END update_adj_vo_tiers;

Line: 115

SELECT list_line_id, list_header_id , list_line_type_code, price_break_type_code
FROM qp_list_lines
WHERE list_line_id = cp_listLineId;

Line: 123

SELECT
decode(c.start_date_active
                            , null
                            , decode(d.start_date_active, null, a.effective_date -1 , decode(greatest(d.start_date_active,a.effective_date - 1) , d.start_date_active, d.start_date_active, a.effective_date -1 ))
                            , decode(greatest(c.start_date_active, a.effective_date -1 ), c.start_date_active , c.start_date_active ,a.effective_date - 1)
        ) endDate
--greatest(nvl(d.start_date_active, a.effective_date - 5 ),nvl(d.start_date_active, a.effective_date - 5 ), a.effective_date - 1 )  endDate
, b.offer_type, b.qp_list_header_id
, c.start_date_active lineStartDate, d.start_date_active headerStartDate
FROM ozf_offer_adjustments_b  a, ozf_offers b , qp_list_lines c , qp_list_headers_b d
WHERE
a.list_header_id = b.qp_list_header_id
AND b.qp_list_header_id = c.list_header_id
AND c.list_header_id = d.list_header_id
AND offer_adjustment_id = cp_offerAdjustmentId
AND c.list_line_id      = cp_listLineId;

Line: 169

    l_modifier_line_tbl(1).operation            := QP_GLOBALS.G_OPR_UPDATE;

Line: 202

SELECT distinct parent_discount_line_id , c.list_line_id
FROM ozf_offer_adjustment_tiers a, ozf_offer_discount_lines b , ozf_qp_discounts c
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.parent_discount_line_id = c.offer_discount_line_id
AND a.offer_adjustment_id = cp_offerAdjustmentId;

Line: 236

SELECT
c.list_header_id
, c.arithmetic_operator
, c.operand
, c.list_line_id
, c.print_on_invoice_flag
, c.accrual_flag
, c.pricing_phase_id
, c.pricing_group_sequence
, c.incompatibility_grp_code
, c.product_precedence
, c.generate_using_formula_id
, c.price_by_formula_id
, c.context
, c.attribute1
, c.attribute2
, c.attribute3
, c.attribute4
, c.attribute5
, c.attribute6
, c.attribute7
, c.attribute8
, c.attribute9
, c.attribute10
, c.attribute11
, c.attribute12
, c.attribute13
, c.attribute14
, c.attribute15
, c.proration_type_code
, c.qualification_ind
, c.modifier_level_code
, c.automatic_flag
, c.override_flag
, c.price_break_type_code
, c.benefit_qty
, c.benefit_uom_code
, c.benefit_price_list_line_id
--, accum_context
, accum_attribute
--, accum_attr_run_src_flag
, list_line_type_code
FROM
qp_list_lines c
WHERE c.list_line_id = cp_listLineId;

Line: 342

SELECT
a.product_attribute_context
, a.product_attribute
, a.product_attr_value
, a.product_uom_code
, a.excluder_flag
, a.pricing_attr_value_from
, a.pricing_attr_value_to
, a.pricing_attribute_context
, a.pricing_attribute
, a.comparison_operator_code
FROM qp_pricing_attributes a
WHERE a.list_line_id = cp_listLineId;

Line: 358

x_pricing_attr_tbl.delete;

Line: 392

x_modifier_line_tbl.delete;

Line: 393

x_pricing_attr_tbl.delete;

Line: 420

x_modifier_line_tbl.delete;

Line: 461

x_pricing_attr_tbl.delete;

Line: 505

SELECT a.from_rltd_modifier_id, a.to_rltd_modifier_id, b.offer_discount_line_id , nvl(c.modified_discount,d.operand) discount  , d.arithmetic_operator
FROM qp_rltd_modifiers a, ozf_qp_discounts b , ozf_offer_adjustment_tiers c , qp_list_lines d
WHERE a.to_rltd_modifier_id = b.list_line_id(+)
and b.offer_discount_line_id = c.offer_discount_line_id(+)
AND a.to_rltd_modifier_id = d.list_line_id
AND a.from_rltd_modifier_id = cp_listLineId
AND c.offer_adjustment_id(+) = cp_offerAdjustmentId;

Line: 513

/*SELECT to_rltd_modifier_id
FROM qp_rltd_modifiers
WHERE from_rltd_modifier_id = cp_listLineId;

Line: 521

x_modifier_line_tbl.delete;

Line: 522

x_pricing_attr_tbl.delete;

Line: 523

l_pricingAttrTbl.delete;

Line: 574

SELECT
a.effective_date, a.list_header_id
FROM ozf_offer_adjustments_b a
WHERE a.offer_adjustment_id = cp_offerAdjustmentId;

Line: 585

x_modifier_line_tbl.delete;

Line: 586

pbh_pricing_attr_tbl.delete;

Line: 587

dis_pricing_attr_tbl.delete;

Line: 588

pbh_modifier_line_tbl.delete;

Line: 589

dis_modifier_line_tbl.delete;

Line: 689

x_modifier_line_tbl.delete;

Line: 690

x_pricing_attr_tbl.delete;

Line: 691

l_modifier_line_tbl.delete;

Line: 692

l_pricing_attr_tbl.delete;

Line: 759

SELECT discount
, tier_type
, offer_discount_line_id
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = cp_offerDiscountlineId
OR parent_discount_line_id   = cp_offerDiscountLineId;

Line: 766

SELECT off_discount_product_id
, product_attribute
, product_attr_value
, excluder_flag
FROM ozf_offer_discount_products
WHERE offer_discount_line_id = cp_offerDiscountlineId;

Line: 879

SELECT distinct parent_discount_line_id , c.list_line_id
FROM ozf_offer_adjustment_tiers a, ozf_offer_discount_lines b , ozf_qp_discounts c , qp_list_lines d
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.parent_discount_line_id = c.offer_discount_line_id
AND c.list_line_id = d.list_line_id
AND d.list_line_type_code = 'PBH'
AND offer_adjustment_id = cp_offerAdjustmentId;

Line: 892

x_modifier_line_tbl.delete;

Line: 893

x_pricing_attr_tbl.delete;

Line: 896

l_modifier_line_tbl.delete;

Line: 897

l_pricing_attr_tbl.delete;

Line: 963

INSERT INTO ozf_offer_adj_rltd_lines
(
 OFFER_ADJ_RLTD_LINE_ID
 , OFFER_ADJUSTMENT_ID
 , FROM_LIST_LINE_ID
 , TO_LIST_LINE_ID
 , LAST_UPDATE_DATE
 , LAST_UPDATED_BY
 , CREATION_DATE
 , CREATED_BY
 , LAST_UPDATE_LOGIN
 , OBJECT_VERSION_NUMBER
 , security_group_id
)
VALUES
(
ozf_offer_adj_rltd_lines_s.nextval
, p_offer_adjustment_id
, p_from_list_line_id
, p_to_list_line_id
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.CONC_LOGIN_ID
, 1
, null
);

Line: 1054

        Corresponding to the discount changes, update the tier definitions.
        End Date the QP List lines corresponding to the tier definitions changed.
        Create new QP List lines with the updated tier definitions.(1)[1]
        Create new discount-tier mapping(1)
        *Create new product-product mapping
*/
update_adj_vo_tiers
(
  x_return_status         => x_return_status
  ,x_msg_count             => x_msg_count
  ,x_msg_data              => x_msg_data
  ,p_offerAdjustmentId   => p_offerAdjustmentId
);

Line: 1170

SELECT
 a.offer_discount_line_id
, a.off_discount_product_id
, a.product_context
, a.product_attribute
, a.product_attr_value
, a.excluder_flag
, a.apply_discount_flag
, a.include_volume_flag
, b.offer_id
, a.offer_adjustment_product_id
, a.object_version_number
FROM ozf_offer_adjustment_products a, ozf_offer_discount_lines b
WHERE
a.offer_discount_line_id = b.offer_discount_line_id
AND offer_adjustment_id = cp_offerAdjustmentId;

Line: 1220

UPDATE ozf_offer_adjustment_products
SET off_discount_product_id = l_objId , object_version_number = object_version_number + 1
WHERE offer_adjustment_product_id = l_products.offer_adjustment_product_id;

Line: 1251

SELECT
print_on_invoice_flag
, accrual_flag
, pricing_phase_id
, pricing_group_sequence
, incompatibility_grp_code
, product_precedence
, proration_type_code
FROM qp_list_lines
WHERE list_header_id = cp_listHeaderId
AND rownum < 2;

Line: 1294

SELECT
c.list_header_id
, b.modifier_level_code
, decode(b.offer_type, 'ACCRUAL','Y','VOLUME_OFFER', decode(b.volume_offer_type,'ACCRUAL','Y','N'),'N') accrual_flag
, nvl(a.discount_type,d.discount_type) discount_type
, a.volume_break_type price_break_type_code
, a.formula_id
, a.discount
, c.effective_date
FROM
ozf_offer_discount_lines a, ozf_offers b , ozf_offer_adjustments_b c , ozf_offer_discount_lines d
WHERE
a.offer_discount_line_id = cp_offerDiscountLineId
AND a.offer_id           = b.offer_id
AND b.qp_list_header_id  = c.list_header_id
AND a.parent_discount_line_id = d.offer_discount_line_id(+);

Line: 1351

x_modifier_line_tbl.delete;

Line: 1375

SELECT
nvl(a.uom_code,b.uom_code) uom_code
, b. offer_discount_line_id
, b.volume_from
, b.volume_to
, nvl( a.volume_type,b.volume_type) volume_type
, nvl(b.volume_operator,'BETWEEN') comparison_operator_code
FROM ozf_offer_discount_lines b , ozf_offer_discount_lines a
WHERE b.offer_discount_line_id = cp_offerDiscountLineId
AND b.parent_discount_line_id = a.offer_discount_line_id(+);

Line: 1408

SELECT min(volume_from)
FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
WHERE a.parent_discount_line_id = b.offer_discount_line_id
AND b.off_discount_product_id = cp_offDiscountProductId;

Line: 1473

SELECT a.offer_discount_line_id , b.apply_discount_flag
FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
WHERE parent_discount_line_id = cp_parentDiscountLineId
AND a.parent_discount_line_id = b.offer_discount_line_id
AND b.off_discount_product_id = cp_offDiscountProductId;

Line: 1482

x_modifier_line_tbl.delete;

Line: 1483

x_pricing_attr_tbl.delete;

Line: 1555

x_modifier_line_tbl.delete;

Line: 1556

x_pricing_attr_tbl.delete;

Line: 1557

l_dis_modifier_line_tbl.delete;

Line: 1558

l_pbh_modifier_line_tbl.delete;

Line: 1559

l_dis_pricing_attr_tbl.delete;

Line: 1560

l_pbh_pricing_attr_tbl.delete;

Line: 1622

Note not initializing the  record to attribute_grouping_no leads to unexpected error cannot insert null into qp_pricing_attributes.attribute_grouping_no
*/
/**
This method populates product attributes ie. Product Attribute, Product Attr Value , excluder flag
into a Qp_Modifiers_Pub.pricing_attr_rec_type record given the Product Id in ozf_offer_discount_products table
*/
PROCEDURE populate_product_attributes
(
 x_pricing_attr_rec         OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_rec_type
 , p_offDiscountProductId   IN NUMBER
-- , p_index IN NUMBER
)
IS
CURSOR c_productAttributes(cp_offDiscountProductId NUMBER) IS
SELECT
product_context
, product_attribute
, product_attr_value
, excluder_flag
, apply_discount_flag
, include_volume_flag
FROM
ozf_offer_discount_products
WHERE off_discount_product_id = cp_offDiscountProductId;

Line: 1809

SELECT b.list_line_id, a.product_attribute, a.product_attr_value , c.list_header_id
FROM ozf_offer_discount_products a, ozf_qp_discounts b , qp_list_lines c
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.list_line_id = c.list_line_id
AND a.off_discount_product_id = p_offDiscountProductId;

Line: 1830

l_pricing_attr_tbl.delete;

Line: 1896

SELECT
effective_date
FROM ozf_offer_adjustments_b
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 1920

The Updates to old products are not handled.
Even if the old discounts will be processed it will only change the definition of the offer and not affect the Volume Calculations.
*/
PROCEDURE create_new_qp_products
(
  x_return_status         OUT NOCOPY  VARCHAR2
  ,x_msg_count             OUT NOCOPY  NUMBER
  ,x_msg_data              OUT NOCOPY  VARCHAR2
  ,p_offerAdjustmentId   IN   NUMBER
  , x_modifier_line_tbl     OUT NOCOPY QP_MODIFIERS_PUB.Modifiers_Tbl_Type
  , x_pricing_attr_tbl      OUT NOCOPY QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type
)
IS
CURSOR c_products(cp_offerAdjustmentId NUMBER) IS
SELECT a.off_discount_product_id
, a.offer_discount_line_id
FROM ozf_offer_adjustment_products a
WHERE offer_adjustment_id = cp_offerAdjustmentId
AND product_attr_value IS NOT NULL
AND excluder_flag = 'N';

Line: 1942

SELECT a.off_discount_product_id
, a.offer_discount_line_id
FROM ozf_offer_adjustment_products a
WHERE offer_adjustment_id = cp_offerAdjustmentId
AND product_attr_value IS NOT NULL
AND excluder_flag = 'Y';

Line: 1970

    l_modifier_line_tbl.delete;

Line: 1971

    l_pricing_attr_tbl.delete;