[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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;
update VO discount tiers
*/
x_return_status := FND_API.G_RET_STS_SUCCESS;
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
);
END update_vo_tier;
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;
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
);
END update_adj_vo_tiers;
SELECT list_line_id, list_header_id , list_line_type_code, price_break_type_code, list_line_no
FROM qp_list_lines
WHERE list_line_id = cp_listLineId;
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, a.effective_date adjEffectiveDate, b.custom_setup_id
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;
l_modifier_line_tbl(1).operation := QP_GLOBALS.G_OPR_UPDATE;
l_qualifiers_tbl.delete;
x_qualifiers_tbl.delete;
UPDATE ozf_sd_request_lines_all
SET end_date = l_endDate.endDate,
object_version_number = object_version_number + 1
WHERE request_line_id = l_request_line_id;
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;
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;
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;
x_pricing_attr_tbl.delete;
x_modifier_line_tbl.delete;
x_pricing_attr_tbl.delete;
x_modifier_line_tbl.delete;
x_pricing_attr_tbl.delete;
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;
/*SELECT to_rltd_modifier_id
FROM qp_rltd_modifiers
WHERE from_rltd_modifier_id = cp_listLineId;
x_modifier_line_tbl.delete;
x_pricing_attr_tbl.delete;
l_pricingAttrTbl.delete;
SELECT
a.effective_date, a.list_header_id
FROM ozf_offer_adjustments_b a
WHERE a.offer_adjustment_id = cp_offerAdjustmentId;
x_modifier_line_tbl.delete;
pbh_pricing_attr_tbl.delete;
dis_pricing_attr_tbl.delete;
pbh_modifier_line_tbl.delete;
dis_modifier_line_tbl.delete;
x_modifier_line_tbl.delete;
x_pricing_attr_tbl.delete;
l_modifier_line_tbl.delete;
l_pricing_attr_tbl.delete;
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;
SELECT off_discount_product_id
, product_attribute
, product_attr_value
, excluder_flag
FROM ozf_offer_discount_products
WHERE offer_discount_line_id = cp_offerDiscountlineId;
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;
x_modifier_line_tbl.delete;
x_pricing_attr_tbl.delete;
l_modifier_line_tbl.delete;
l_pricing_attr_tbl.delete;
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
);
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
);
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;
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;
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;
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(+);
x_modifier_line_tbl.delete;
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(+);
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;
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;
x_modifier_line_tbl.delete;
x_pricing_attr_tbl.delete;
x_modifier_line_tbl.delete;
x_pricing_attr_tbl.delete;
l_dis_modifier_line_tbl.delete;
l_pbh_modifier_line_tbl.delete;
l_dis_pricing_attr_tbl.delete;
l_pbh_pricing_attr_tbl.delete;
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;
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;
l_pricing_attr_tbl.delete;
SELECT
effective_date
FROM ozf_offer_adjustments_b
WHERE offer_adjustment_id = cp_offerAdjustmentId;
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';
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';
l_modifier_line_tbl.delete;
l_pricing_attr_tbl.delete;