[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ozf_OFFER_ADJUSTMENTS_B_s.NEXTVAL
FROM dual;
SELECT 1
FROM ozf_OFFER_ADJUSTMENTS_B
WHERE OFFER_ADJUSTMENT_ID = l_id;
ozf_OFFER_ADJUSTMENTS_PKG.Insert_Row(
px_offer_adjustment_id => l_offer_adjustment_id,
p_effective_date => p_offer_backdate_rec.effective_date,
p_approved_date => p_offer_backdate_rec.approved_date,
p_settlement_code => p_offer_backdate_rec.settlement_code,
p_status_code => p_offer_backdate_rec.status_code,
p_list_header_id => p_offer_backdate_rec.list_header_id,
p_version => p_offer_backdate_rec.version,
p_budget_adjusted_flag => p_offer_backdate_rec.budget_adjusted_flag,
p_comments => p_offer_backdate_rec.comments,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
px_object_version_number => l_object_version_number,
p_security_group_id => p_offer_backdate_rec.security_group_id);
PROCEDURE Update_Offer_Backdate(
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_offer_backdate_rec IN offer_backdate_rec_type,
x_object_version_number OUT NOCOPY NUMBER
)
IS
CURSOR c_get_Offer_Backdate(p_offer_adjustment_id NUMBER) IS
SELECT *
FROM ozf_offer_adjustments_B
WHERE offer_adjustment_id = p_offer_adjustment_id ;
SELECT status_code
FROM ozf_offer_adjustments_B
WHERE offer_adjustment_id = p_offer_adjustment_id ;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Offer_Backdate';
SAVEPOINT UPDATE_Offer_Backdate_PVT;
OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
p_token_name => 'INFO',
p_token_value => 'Offer_Backdate') ;
p_token_value => 'Last_Update_Date') ;
p_validation_mode => JTF_PLSQL_API.g_update,
p_offer_backdate_rec => p_offer_backdate_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ozf_OFFER_ADJUSTMENTS_PKG.Update_Row(
p_offer_adjustment_id => p_offer_backdate_rec.offer_adjustment_id,
p_effective_date => p_offer_backdate_rec.effective_date,
p_approved_date => p_offer_backdate_rec.approved_date,
p_settlement_code => p_offer_backdate_rec.settlement_code,
p_status_code => l_new_status_code, -- p_offer_backdate_rec.status_code,
p_list_header_id => p_offer_backdate_rec.list_header_id,
p_version => p_offer_backdate_rec.version,
p_budget_adjusted_flag => p_offer_backdate_rec.budget_adjusted_flag,
p_comments => p_offer_backdate_rec.comments,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_object_version_number => p_offer_backdate_rec.object_version_number,
p_security_group_id => p_offer_backdate_rec.security_group_id);
ROLLBACK TO UPDATE_Offer_Backdate_PVT;
ROLLBACK TO UPDATE_Offer_Backdate_PVT;
ROLLBACK TO UPDATE_Offer_Backdate_PVT;
End Update_Offer_Backdate;
PROCEDURE Delete_Offer_Backdate(
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_offer_adjustment_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Offer_Backdate';
SAVEPOINT DELETE_Offer_Backdate_PVT;
OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
ozf_OFFER_ADJUSTMENTS_PKG.Delete_Row(
p_OFFER_ADJUSTMENT_ID => p_OFFER_ADJUSTMENT_ID);
ROLLBACK TO DELETE_Offer_Backdate_PVT;
ROLLBACK TO DELETE_Offer_Backdate_PVT;
ROLLBACK TO DELETE_Offer_Backdate_PVT;
End Delete_Offer_Backdate;
SELECT OFFER_ADJUSTMENT_ID
FROM ozf_OFFER_ADJUSTMENTS_B
WHERE OFFER_ADJUSTMENT_ID = p_OFFER_ADJUSTMENT_ID
AND object_version_number = p_object_version
FOR UPDATE NOWAIT;
SELECT *
FROM ozf_offer_adjustments_b
WHERE offer_adjustment_id = p_offer_backdate_rec.offer_adjustment_id;
IF p_offer_backdate_rec.last_update_date = FND_API.g_miss_date THEN
x_complete_rec.last_update_date := NULL;
IF p_offer_backdate_rec.last_update_date IS NULL THEN
x_complete_rec.last_update_date := l_offer_backdate_rec.last_update_date;
IF p_offer_backdate_rec.last_updated_by = FND_API.g_miss_num THEN
x_complete_rec.last_updated_by := NULL;
IF p_offer_backdate_rec.last_updated_by IS NULL THEN
x_complete_rec.last_updated_by := l_offer_backdate_rec.last_updated_by;
IF p_offer_backdate_rec.last_update_login = FND_API.g_miss_num THEN
x_complete_rec.last_update_login := NULL;
IF p_offer_backdate_rec.last_update_login IS NULL THEN
x_complete_rec.last_update_login := l_offer_backdate_rec.last_update_login;
SELECT ozf_OFFER_ADJUSTMENTS_B_s.NEXTVAL
FROM dual;
SELECT ozf_OFFER_ADJUSTMENT_LINES_s.NEXTVAL
FROM dual;
SELECT 1
FROM ozf_OFFER_ADJUSTMENTS_B
WHERE OFFER_ADJUSTMENT_ID = l_id;
select qp.pricing_attribute_id,
ql.list_line_id,
ql.arithmetic_operator,
ql.operand
from qp_pricing_attributes qp,
qp_list_lines ql
where ql.list_header_id = l_id
and qp.list_header_id = l_id -- julou added for sql performance xxfix
and ql.list_line_id = qp.list_line_id
and qp.excluder_flag = 'N';
INSERT INTO ozf_OFFER_ADJUSTMENTS_B(
offer_adjustment_id,
list_header_id,
status_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number
) VALUES (
l_OFFER_ADJUSTMENT_ID,
p_obj_id,
'DRAFT',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
1
);
INSERT INTO ozf_OFFER_ADJUSTMENT_LINES(
offer_adjustment_line_id,
offer_adjustment_id,
list_line_id,
arithmetic_operator,
original_discount,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number
) VALUES (
l_OFFER_ADJUSTMENT_LINE_ID,
l_OFFER_ADJUSTMENT_ID,
l_list_line_id,
l_arithmetic_operator,
l_operand,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
1
);
SELECT o.offer_type
FROM ozf_offers o,
ozf_offer_adjustments_vl a
WHERE a.offer_adjustment_id = p_offer_adjustment_id
and a.list_header_id = o.qp_list_header_id ;
SELECT *
FROM qp_list_lines
WHERE list_line_id = l_list_line_id;
SELECT effective_date
FROM ozf_offer_adjustments_b
WHERE offer_adjustment_id = cp_offerAdjustmentId;
SELECT list_line_id
,list_line_id_td
,list_header_id
, arithmetic_operator
, discount_end_date
FROM ozf_offer_adjustment_lines
WHERE offer_adjustment_id = p_offer_adjustment_id
AND created_from_adjustments = 'Y';
SELECT related_deal_lines_id
,modifier_id
,related_modifier_id
,object_version_number
FROM ozf_related_deal_lines
WHERE qp_list_header_id = p_list_header_id
AND modifier_id = p_list_line_id;
l_modifier_line_tbl(l_index).operation := 'UPDATE';
PROCEDURE update_volume_offer_discounts
(
p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_api_version IN NUMBER
,p_commit IN VARCHAR2 := FND_API.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_offer_adjustment_id IN NUMBER
)
IS
l_qp_list_header_id NUMBER;
SELECT *
FROM qp_list_lines
WHERE list_header_id = p_list_header_id;
SELECT modified_discount , qp_list_header_id
FROM OZF_OFFER_ADJUSTMENT_TIERS
WHERE
offer_adjustment_id = p_offer_adjustment_id
AND original_discount =
(SELECT operand FROM qp_list_lines WHERE list_header_id
= (select qp_list_header_id FROM ozf_offer_adjustment_tiers WHERE offer_adjustment_id = p_offer_adjustment_id and rownum < 2)
and rownum < 2)
;
SELECT pricing_attribute_id
,product_attribute_context
,product_attribute
,product_attr_value
,product_uom_code
,pricing_attribute_context
,pricing_attribute
,pricing_attr_value_from
,pricing_attr_value_to
,excluder_flag
FROM qp_pricing_attributes
WHERE list_header_id = l_list_header_id
AND list_line_id = l_list_line_id;
SELECT qualifier_id
,qualifier_attr_value
,qualifier_attr_value_to
FROM qp_qualifiers
WHERE list_header_id = l_list_header_id
AND list_line_id = l_list_line_id;
SELECT volume_offer_tiers_id
,modified_discount
FROM ozf_offer_adjustment_tiers
WHERE offer_adjustment_id = p_offer_adjustment_id;
SELECT *
FROM ozf_volume_offer_tiers
WHERE volume_offer_tiers_id = l_tier_id;
OZF_Vol_Offr_PVT.Update_Vol_Offr(p_api_version
,p_init_msg_list
,p_commit
,FND_API.G_VALID_LEVEL_FULL -- validation level
,x_return_status
,x_msg_count
,x_msg_data
,l_vol_offr_tier_rec
,l_dummy);
l_modifier_line_tbl(l_index).operation := 'UPDATE';
END update_volume_offer_discounts;
currently only inserts without checking for duplicates, so depends on judicious calling of the api
*/
PROCEDURE process_vo_adj_products
(
p_init_msg_list IN VARCHAR2 := FND_API.g_false
, p_api_version IN NUMBER
, p_commit IN VARCHAR2 := FND_API.g_false
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_offer_adjustment_id IN NUMBER
)
IS
l_vo_prod_rec OZF_Volume_Offer_disc_PVT.vo_prod_rec_type;
SELECT decode(apply_discount_flag,'N',decode(include_volume_flag,'N','Y','N'),'N') excluder_flag
, offer_discount_line_id
, offer_id
, product_context
, product_attribute
, product_attr_value
, apply_discount_flag
, include_volume_flag
FROM ozf_offer_adjustment_products a , ozf_offer_adjustments_b b, ozf_offers c
WHERE a.offer_adjustment_id = b.offer_adjustment_id
AND b.list_header_id = c.qp_list_header_id
AND a.offer_adjustment_id = p_adjustment_id;
SELECT a.product_context
, a.product_attribute
, a.product_attr_value
, c.uom_code
, c.volume_type
, d.qp_list_header_id list_header_id
, d.volume_offer_type
, d.modifier_level_code
, b.offer_discount_line_id
, c.discount_type
, e.effective_date
, a.apply_discount_flag
, a.include_volume_flag
FROM ozf_offer_adjustment_products a, ozf_offer_discount_products b , ozf_offer_discount_lines c , ozf_offers d , ozf_offer_adjustments_b e
WHERE a.offer_discount_line_id = c.offer_discount_line_id
AND b.offer_discount_line_id = c.offer_discount_line_id
AND a.product_context = b.product_context
AND a.product_attribute = b.product_attribute
AND a.product_attr_value = b.product_attr_value
AND c.offer_id = d.offer_id
AND e.offer_adjustment_id = a.offer_adjustment_id
--AND b.offer_discount_line_id = p_offer_discount_line_id
AND a.offer_adjustment_id = p_offer_adjustment_id;
SELECT volume_from, volume_to, discount_type, discount
FROM ozf_offer_discount_lines where parent_discount_line_id = p_offer_discount_line_id;
SELECT distinct b.off_discount_product_id , d.pricing_attribute_id
FROM ozf_offer_adjustment_products a, ozf_offer_discount_products b , ozf_offer_adjustments_b c, qp_pricing_attributes d
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND a.product_attribute = b.product_attribute
AND a.product_attr_value = b.product_attr_value
AND a.excluder_flag = b.excluder_flag
AND a.offer_adjustment_id = c.offer_adjustment_id
AND c.list_header_id = d.list_header_id
AND a.product_attribute = d.product_attribute
AND a.product_attr_value = d.product_attr_value
AND a.offer_adjustment_id = p_offer_adjustment_id;
SELECT b.offer_discount_line_id , d.list_line_id
FROM ozf_offer_adjustment_products a, ozf_offer_discount_lines b, ozf_offer_adjustments_b c, qp_list_lines d
WHERE a.offer_discount_line_id = DECODE(b.tier_type,'PBH',offer_discount_line_id,parent_discount_line_id)
AND b.offer_adjustment_id = a.offer_adjustment_id
AND a.offer_adjustment_id = p_offer_adjustment_id
AND c.list_header_id = d.list_header_id
AND b.tier_type = d.list_line_type_code
AND nvl(b.discount,-1) = nvl(d.operand,-1);
SELECT c.offer_discount_line_id, b.off_discount_product_id , d.list_line_id , d.pricing_attribute_id
FROM ozf_offer_adjustment_products a, ozf_offer_discount_products b, ozf_offer_discount_lines c , ozf_offer_adjustments_b e ,qp_pricing_attributes d
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND a.product_attr_value = b.product_attr_value
AND a.offer_adjustment_id = p_offer_adjustment_id
AND b.offer_discount_line_id = decode(c.tier_type , 'DIS',c.parent_discount_line_id, c.offer_discount_line_id)
AND e.offer_adjustment_id = a.offer_adjustment_id
AND d.list_header_id = e.list_header_id
AND d.product_attr_value = a.product_attr_value
AND to_number(nvl(d.pricing_attr_value_from,0)) = nvl(c.volume_from,0)
AND to_number(nvl(d.pricing_attr_value_to,0)) = nvl(c.volume_to,0)
AND a.apply_discount_flag = 'Y';
SELECT c.off_discount_product_id , d.pricing_attribute_id
FROM
ozf_offer_adjustment_products a, ozf_offer_adjustments_b b , ozf_offer_discount_products c, qp_pricing_attributes d
WHERE a.offer_adjustment_id = b.offer_adjustment_id
AND a.offer_adjustment_id = p_offer_adjustment_id
AND a.offer_discount_line_id = c.offer_discount_line_id
AND a.product_attribute = c.product_attribute
AND a.product_attr_value = c.product_attr_value
AND b.list_header_id = d.list_header_id
AND c.product_attribute = d.product_attribute
AND c.product_attr_value = d.product_attr_value
AND a.apply_discount_flag = 'N';
The approach taken here is just update the existing lines.
*/
PROCEDURE process_vo_adj_tiers
(
p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_api_version IN NUMBER
,p_commit IN VARCHAR2 := FND_API.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_offer_adjustment_id IN NUMBER
)
IS
i NUMBER := 1;
SELECT c.list_line_id , c.list_header_id , d.effective_date , a.modified_discount, c.arithmetic_operator
FROM ozf_offer_adjustment_tiers a, ozf_qp_discounts b , qp_list_Lines c, ozf_offer_adjustments_b d
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.list_line_id = c.list_line_id
AND a.offer_adjustment_id = d.offer_adjustment_id
AND a.offer_adjustment_id = p_adjustment_id;
l_modifier_line_tbl(i).operation := 'UPDATE';
SELECT
a.modified_discount, a.offer_discount_line_id , b.object_version_number , b.offer_id
FROM ozf_offer_adjustment_tiers a, ozf_offer_discount_lines b
WHERE a.offer_adjustment_id = p_offer_adjustment_id
AND a.offer_discount_line_id = b.offer_discount_line_id;
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
);
SELECT d.from_rltd_modifier_id list_line_id
, c.list_header_id list_header_id
, f.price_break_type_code price_break_type_code
, c.pricing_attr_value_from pricing_attr_value_from
, c.pricing_attr_value_to pricing_attr_value_to
, a.modified_discount operand
, f.arithmetic_operator arithmetic_operator
, c.pricing_attribute pricing_attribute
, e.effective_date start_date_active
FROM ozf_offer_adjustment_tiers a, ozf_qp_discounts b, qp_pricing_attributes c, qp_rltd_modifiers d
, ozf_offer_adjustments_b e, qp_list_lines f
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.list_line_id = c.list_line_id
AND c.list_line_id = d.to_rltd_modifier_id
AND a.offer_adjustment_id = e.offer_adjustment_id
AND b.list_line_id = f.list_line_id
AND a.offer_adjustment_id = p_offer_adjustment_id;
SELECT a.offer_discount_line_id , f.off_discount_product_id , e.pricing_attribute_id , d.list_line_id
FROM ozf_offer_adjustment_tiers a, ozf_offer_adjustments_b b , ozf_offer_discount_lines c , qp_list_lines d , qp_pricing_attributes e , ozf_offer_discount_products f
WHERE a.offer_adjustment_id = b.offer_adjustment_id
AND c.offer_discount_line_id = a.offer_discount_line_id
AND b.list_header_id = d.list_header_id
AND d.list_line_id = e.list_line_id
AND to_number(e.pricing_attr_value_from) = c.volume_from -- takes care of no apply discount products
AND to_number(e.pricing_attr_value_to) = c.volume_to
AND d.operand = a.modified_discount --c.discount
AND f.offer_discount_line_id = c.parent_discount_line_id
AND f.product_attribute = e.product_attribute
and f.product_attr_value = e.product_attr_value
AND d.start_date_active >= b.effective_date
AND a.offer_adjustment_id = p_offer_adjustment_id;
SELECT decode(greatest(a.effective_date,sysdate) , a.effective_date, 'Y','N') close_adjustment
FROM ozf_offer_adjustments_b a
WHERE offer_adjustment_id = cp_offerAdjustmentId;
* This procedure calls update statement directly since, this procedure may be called from update_offer_adjustments procedure
* and calling this procedure again to update will lead to an recursive call without exit condition
* p_offer_adjustment_id Primary key of the adjustment to be closed/activated
*/
PROCEDURE close_adjustment
(
p_offer_adjustment_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_newStatus OZF_OFFER_ADJUSTMENTS_B.STATUS_CODE%TYPE;
SELECT decode(greatest(a.effective_date,sysdate) , a.effective_date, 'Y','N') close_adjustment,
list_header_id offer_id
FROM ozf_offer_adjustments_b a
WHERE offer_adjustment_id = cp_offerAdjustmentId;
UPDATE ozf_offer_adjustments_b
SET budget_adjusted_flag = l_budgetAdjFlag,
object_version_number = object_version_number + 1,
approved_date = sysdate,
status_code = l_newStatus
WHERE offer_adjustment_id = p_offer_adjustment_id;
PROCEDURE Update_Offer_Discounts
(
p_init_msg_list IN VARCHAR2 := FND_API.g_false
,p_api_version IN NUMBER
,p_commit IN VARCHAR2 := FND_API.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_offer_adjustment_id IN NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_offer_discounts';
SAVEPOINT update_offer_discounts ;
SELECT list_header_id into l_list_header_id
FROM ozf_offer_adjustments_b a
WHERE offer_adjustment_id = p_offer_adjustment_id;
ROLLBACK TO update_offer_discounts;
ROLLBACK TO update_offer_discounts;
ROLLBACK TO update_offer_discounts ;
END Update_Offer_Discounts ;