eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OZF_OFFER_ADJ_PVT SQL Statements

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

Line: 23


SELECT offer_adjustment_id
FROM ozf_offer_adj_new_lines
WHERE offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 44


SELECT offer_type
FROM ozf_offers a, ozf_offer_adjustments_b b, ozf_offer_adj_new_lines c
WHERE
a.qp_list_header_id = b.list_header_id
AND b.offer_adjustment_id = c.offer_adjustment_id
AND c.offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 72


SELECT a.modified_discount
FROM ozf_offer_adjustment_lines a
WHERE a.offer_adjustment_line_id = cp_offerAdjustmentLineId;

Line: 92


SELECT effective_date
FROM ozf_offer_adjustments_b
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 120


select h.start_date_active
      ,l.start_date_active
      ,a.effective_date
INTO l_headerStDt
     ,l_lineStDt
     ,l_effectiveDt
FROM ozf_offer_adjustments_b a
     ,qp_list_headers_b h
     ,qp_list_lines l
WHERE a.list_header_id = h.list_header_id
     and a.list_header_id = l.list_header_id
     and l.list_line_id = p_listLineId
     and a.offer_adjustment_id = p_offerAdjustmentId;

Line: 239


SELECT to_rltd_modifier_id
FROM qp_rltd_modifiers
WHERE from_rltd_modifier_id = cp_pbhListLineId
ORDER BY to_rltd_modifier_id asc;

Line: 253


x_modifier_line_tbl.delete;

Line: 254


x_pricing_attr_tbl.delete;

Line: 255


l_pricing_attr_tbl.delete;

Line: 259


    l_pricing_attr_tbl.delete;

Line: 360


SELECT
nvl(a.modified_discount,c.operand) discount
FROM
ozf_offer_adjustment_lines a, qp_rltd_modifiers b,  qp_list_lines c
WHERE
a.list_line_id (+) =  b.to_rltd_modifier_id
AND b.to_rltd_modifier_id = c.list_line_id
AND b.from_rltd_modifier_id = cp_pbhListLineId
AND a.offer_adjustment_id (+) = cp_offerAdjustmentId
ORDER BY b.to_rltd_modifier_id asc;

Line: 394


SELECT *
FROM qp_limits
WHERE limit_id = cp_limitId;

Line: 452


SELECT list_header_id
FROM qp_list_lines
WHERE list_line_id = cp_listLineId;

Line: 493


FOR l_limits in (SELECT limit_id, list_line_id FROM qp_limits WHERE list_line_id = p_fromListLineId) LOOP
populate_limits_rec
(
x_limits_rec        => l_limitsRec
, x_return_status   => x_return_status
, p_limitId    => l_limits.limit_id
);

Line: 551


SELECT
qualifier_id
, qualifier_grouping_no
, qualifier_context
, qualifier_attribute
, qualifier_attr_value
, comparison_operator_code
, excluder_flag
, start_date_active
, end_date_active
, qualifier_precedence
, list_header_id
, list_line_id
, qualifier_attr_value_to
, context
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, active_flag
FROM qp_qualifiers
WHERE list_line_id = cp_listLineId;

Line: 587


x_qualifiers_tbl.delete;

Line: 669


l_qualifiers_tbl.delete;

Line: 817


  x_modifier_line_tbl.delete;

Line: 908


SELECT distinct b.from_rltd_modifier_id
FROM ozf_offer_adjustment_lines a, qp_rltd_modifiers b
WHERE a.list_line_id  = b.to_rltd_modifier_id
AND a.offer_adjustment_id = cp_offerAdjustmentId;

Line: 990


    SELECT
    list_line_id
    FROM qp_list_lines
    WHERE list_line_id = cp_listLineId;

Line: 1006


x_modifier_line_tbl.delete;

Line: 1007


x_pricing_attr_tbl.delete;

Line: 1008


l_pricing_attr_tbl.delete;

Line: 1011


l_pricing_attr_tbl.delete;

Line: 1103


l_modifier_line_tbl.delete;

Line: 1104


l_pricing_attr_tbl.delete;

Line: 1209


l_modifier_line_tbl.delete;

Line: 1210


l_pricing_attr_tbl.delete;

Line: 1282


SELECT list_line_id
FROM ozf_offer_adjustment_lines
WHERE offer_adjustment_line_id = cp_offerAdjustmentLineId;

Line: 1371


SELECT a.list_line_id, b.list_line_type_code , a.offer_adjustment_line_id
FROM ozf_offer_adjustment_lines a, qp_list_lines b
WHERE
a.list_line_id = b.list_line_id
AND b.qualification_ind in (0,2,4,6,8,10,12,14,16,18,20,22,24,26, 28, 30, 32)
AND offer_adjustment_id = cp_offerAdjustmentId;

Line: 1444


SELECT from_rltd_modifier_id
FROM qp_rltd_modifiers
WHERE rltd_modifier_grp_type = 'BENEFIT'
AND rltd_modifier_grp_no  = 1
AND to_rltd_modifier_id = cp_listLineId;

Line: 1452


x_modifier_line_tbl.delete;

Line: 1453


x_pricing_attr_tbl.delete;

Line: 1528


x_pricing_attr_tbl.delete;

Line: 1529


x_modifier_line_tbl.delete;

Line: 1614


l_modifier_line_tbl.delete;

Line: 1615


l_pricing_attr_tbl.delete;

Line: 1740


SELECT list_line_id, offer_adjustment_line_id
FROM ozf_offer_adjustment_lines
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 1775


FOR l_pricingAttr IN (SELECT product_attribute, product_attr_value, product_uom_code,pricing_attribute,  pricing_attr_value_from
                        FROM qp_pricing_attributes
                        WHERE list_line_id = p_listLineId
                        AND excluder_flag = 'N')
LOOP
    px_modifier_line_rec.product_attr             := l_pricingAttr.product_attribute;

Line: 1798


SELECT
a.list_header_id
, a.list_line_id
, decode(nvl(a.accrual_flag,'N'),'N',a.arithmetic_operator, c.arithmetic_operator) operator
, decode(nvl(a.accrual_flag,'N'),'Y',a.arithmetic_operator, c.arithmetic_operator) accrual_operator
FROM
qp_list_lines a
, ozf_related_deal_lines b
, qp_list_lines c
WHERE
a.list_line_id = b.MODIFIER_ID
AND b.RELATED_MODIFIER_ID = c.list_line_id(+)
AND a.list_line_id =cp_listLineId;

Line: 1851


SELECT b.effective_date
, nvl(a.modified_discount,a.original_discount) discount
, nvl(a.modified_discount_td, a.original_discount_td) discount_td
FROM ozf_offer_adjustment_lines a, ozf_offer_adjustments_b b
WHERE a.offer_adjustment_id = b.offer_adjustment_id
AND a.offer_adjustment_id = cp_offerAdjustmentId
AND a.list_line_id = cp_listLineId;

Line: 1877


SELECT
decode(nvl(a.accrual_flag,'N'),'N',b.modifier_id,b.related_modifier_id) list_line_id
, a.list_header_id
, a.accrual_flag
--, greatest(a.start_date_active, d.start_date_active) start_date_active
, decode(nvl(a.accrual_flag,'N'),'N',a.arithmetic_operator, c.arithmetic_operator) arithmetic_operator
, decode(nvl(a.accrual_flag,'N'),'Y',a.arithmetic_operator, c.arithmetic_operator) arithmetic_operator_td
, decode(nvl(a.accrual_flag,'N'),'N',a.operand, c.operand) operand
, decode(nvl(a.accrual_flag,'N'),'Y',a.operand,c.operand) operand_td
, decode(nvl(a.accrual_flag,'N'),'Y',b.modifier_id, b.related_modifier_id) related_modifier_id
, b.related_deal_lines_id
, b.object_version_number
, a.start_date_active
FROM
 qp_list_lines a
, ozf_related_deal_lines b
, qp_list_lines c
, qp_list_headers_b d
WHERE
a.list_line_id = b.modifier_id
AND b.related_modifier_id = c.list_line_id(+)
AND a.list_header_id = d.list_header_id
AND a.list_line_id = cp_listLineId;

Line: 1906


    l_modifier_line_rec.operation := 'UPDATE';

Line: 1959


l_modifier_line_tbl.delete;

Line: 2034


SELECT product_attribute, product_attr_value , list_header_id
FROM qp_pricing_attributes
WHERE list_line_id = cp_listLineId
and excluder_flag = 'Y';

Line: 2040


x_pricing_attr_tbl.delete;

Line: 2094


l_pricing_attr_tbl.delete;

Line: 2266


SELECT related_modifier_id
FROM ozf_related_deal_lines
WHERE modifier_id = cp_listLineId;

Line: 2300


SELECT accrual_flag
FROM qp_list_lines
WHERE list_line_id = cp_listLineId;

Line: 2584


SELECT
list_line_id
FROM ozf_offer_adjustment_lines
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 2626


SELECT
offer_type
FROM ozf_offers a, ozf_offer_adjustments_b b
WHERE a.qp_list_header_id = b.list_header_id
AND b.offer_adjustment_id = cp_offerAdjustmentId;

Line: 2705


SELECT
a.product_attribute
, a.product_attr_value
, a.uom_code
, b.volume_from
, b.volume_to
, b.volume_type
, b.end_date_active
, c.effective_date
, c.list_header_id
FROM
ozf_offer_adj_new_products a, ozf_offer_adj_new_lines b , ozf_offer_adjustments_b c
WHERE a.offer_adj_new_line_id = b.offer_adj_new_line_id
AND a.excluder_flag = 'N'
AND b.offer_adjustment_id = c.offer_adjustment_id
AND b.offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 2724


x_modifier_line_tbl.delete;

Line: 2786


l_modifier_line_tbl.delete;

Line: 2787


lx_modifier_tbl.delete;

Line: 2900


SELECT offer_adj_new_line_id
FROM ozf_offer_adj_new_lines
WHERE tier_type <> 'DIS'
AND offer_adjustment_id = cp_offerAdjustmentId;

Line: 2934


SELECT
a.end_date_active
, a.benefit_price_list_line_id
, a.discount
, a.discount_type
, a.quantity
, b.product_attribute
, b.product_attr_value
, b.uom_code
, c.effective_date
, c.list_header_id
FROM ozf_offer_adj_new_lines a, ozf_offer_adj_new_products b, ozf_offer_adjustments_b c
WHERE a.offer_adj_new_line_id = b.offer_adj_new_line_id
AND a.offer_adjustment_id = c.offer_adjustment_id
AND a.offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 2953


x_modifier_line_tbl.delete;

Line: 3015


l_modifier_line_tbl.delete;

Line: 3076


l_modifiers_tbl.delete;

Line: 3121


SELECT offer_adj_new_line_id
FROM ozf_offer_adj_new_lines
WHERE tier_type = 'DIS'
AND offer_adjustment_id = cp_offerAdjustmentId;

Line: 3203


SELECT
a.offer_adj_new_line_id
, a.offer_adjustment_id
, a.volume_from
, a.volume_to
, a.volume_type
, a.discount
, a.discount_type
, a.tier_type
, a.td_discount
, a.td_discount_type
, a.quantity
, a.benefit_price_list_line_id
, a.parent_adj_line_id
, a.start_date_active
, a.end_date_active
, b.product_context
, b.product_attribute
, b.product_attr_value
, b.excluder_flag
, b.uom_code
, c.list_header_id
, c.effective_date
FROM
ozf_offer_adj_new_lines a, ozf_offer_adj_new_products b , ozf_offer_adjustments_b c
WHERE a.offer_adj_new_line_id = b.offer_adj_new_line_id
AND a.offer_adjustment_id    = c.offer_adjustment_id
and a.offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 3237


x_modifier_line_tbl.delete;

Line: 3296


l_modifier_line_tbl.delete;

Line: 3365


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

Line: 3397


SELECT
b.effective_date
, b.list_header_id
, decode(c.offer_type,'ACCRUAL','Y','N') accrual_flag
, c.modifier_level_code
FROM
ozf_offer_adj_new_lines 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_adj_new_line_id = cp_offerAdjNewLineId;

Line: 3449


SELECT a.product_context
, a.product_attribute
, a.product_attr_value
, a.uom_code
, a.excluder_flag
, b.volume_type
FROM ozf_offer_adj_new_products a, ozf_offer_adj_new_lines b
WHERE
a.offer_adj_new_line_id = b.offer_adj_new_line_id
AND a.offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 3490


SELECT
 a.tier_type
--, c.effective_date
--, c.list_header_id
--, d.modifier_level_code
--, decode(d.offer_type, 'ACCRUAL','Y','N') accrual_flag
, a.end_date_active
FROM ozf_offer_adj_new_lines a
WHERE a.offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 3560


SELECT
 discount
, discount_type
, tier_type
FROM ozf_offer_adj_new_lines
WHERE offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 3608


SELECT a.product_context
, a.product_attribute
, a.product_attr_value
, a.uom_code
, a.excluder_flag
, b.volume_type
, b.volume_from
, b.volume_to
FROM ozf_offer_adj_new_products a, ozf_offer_adj_new_lines b
WHERE
a.offer_adj_new_line_id = b.parent_adj_line_id
AND a.excluder_flag     = 'N'
AND b.offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 3654


SELECT offer_adj_new_line_id
FROM ozf_offer_adj_new_lines
WHERE parent_adj_line_id = cp_offerAdjNewLineId;

Line: 3712


x_pricing_attr_tbl.delete;

Line: 3754


x_modifier_line_tbl.delete;

Line: 3755


x_pricing_attr_tbl.delete;

Line: 3756


l_modifier_line_tbl.delete;

Line: 3757


l_pricing_attr_tbl.delete;

Line: 3836


l_modifier_line_tbl.delete;

Line: 3837


l_pricing_attr_tbl.delete;

Line: 3905


l_modifier_line_tbl.delete;

Line: 3945


SELECT offer_adj_new_line_id
FROM ozf_offer_adj_new_lines
WHERE tier_type = 'DIS'
AND parent_adj_line_id IS NULL
AND offer_adjustment_id = cp_offerAdjustmentId;

Line: 3978


SELECT offer_adj_new_line_id
FROM ozf_offer_adj_new_lines
WHERE offer_adjustment_id = cp_offerAdjustmentId
AND parent_adj_line_id IS NULL
AND tier_type = 'PBH';

Line: 4050


SELECT
offer_type
FROM ozf_offers a, ozf_offer_adjustments_b b
WHERE a.qp_list_header_id = b.list_header_id
AND b.offer_adjustment_id = cp_offerAdjustmentId;

Line: 4105


PROCEDURE updateHeaderDate
(
  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_dates(cp_offerAdjustmentId NUMBER) IS
SELECT a.effective_date , b.start_date_active , a.list_header_id , c.offer_type , c.object_version_number
FROM ozf_offer_adjustments_b a, qp_list_headers_b b , ozf_offers c
WHERE a.list_header_id = b.list_header_id
AND b.list_header_id = c.qp_list_header_id
AND a.offer_adjustment_id = cp_offerAdjustmentId ;

Line: 4136


    l_modifier_rec.OFFER_OPERATION := Qp_Globals.G_OPR_UPDATE;

Line: 4137


    l_modifier_rec.MODIFIER_OPERATION := Qp_Globals.G_OPR_UPDATE;

Line: 4161


END updateHeaderDate;

Line: 4186


updateHeaderDate
(
  x_return_status           => x_return_status
  ,x_msg_count              => x_msg_count
  ,x_msg_data               => x_msg_data
  ,p_offerAdjustmentId      => p_offerAdjustmentId
);

Line: 4193


ozf_utility_pvt.debug_message('GR Updated header date:'||x_return_status);