APPS.OZF_OFFER_ADJ_PVT SQL Statements

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

Line: 25

SELECT offer_adjustment_id
FROM ozf_offer_adj_new_lines
WHERE offer_adj_new_line_id = cp_offerAdjNewLineId;

Line: 46

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: 74

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

Line: 94

SELECT effective_date
FROM ozf_offer_adjustments_b
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 122

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: 241

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

Line: 255

x_modifier_line_tbl.delete;

Line: 256

x_pricing_attr_tbl.delete;

Line: 257

l_pricing_attr_tbl.delete;

Line: 261

    l_pricing_attr_tbl.delete;

Line: 362

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: 396

SELECT *
FROM qp_limits
WHERE limit_id = cp_limitId;

Line: 454

SELECT list_header_id
FROM qp_list_lines
WHERE list_line_id = cp_listLineId;

Line: 495

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: 553

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: 589

x_qualifiers_tbl.delete;

Line: 671

l_qualifiers_tbl.delete;

Line: 819

  x_modifier_line_tbl.delete;

Line: 910

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: 992

    SELECT
    list_line_id
    FROM qp_list_lines
    WHERE list_line_id = cp_listLineId;

Line: 1008

x_modifier_line_tbl.delete;

Line: 1009

x_pricing_attr_tbl.delete;

Line: 1010

l_pricing_attr_tbl.delete;

Line: 1013

l_pricing_attr_tbl.delete;

Line: 1064

        SELECT l.discount_end_date
        FROM ozf_offer_adjustment_lines l
        WHERE l.offer_adjustment_line_id = p_offerAdjustmentLineId;

Line: 1123

SELECT request_header_id
          ,object_version_number
          ,trunc(request_start_date)
          ,trunc(request_end_date)
FROM ozf_sd_request_headers_all_b
WHERE offer_id = p_offer_id;

Line: 1131

SELECT custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;

Line: 1136

SELECT adjl.list_line_id
            ,qpl.list_line_no
            ,adjl.modified_discount
FROM ozf_offer_adjustment_lines adjl, qp_list_lines qpl
WHERE offer_adjustment_line_id = p_offerAdjustmentLineId
AND qpl.list_header_id = adjl.list_header_id
AND adjl.list_line_id = qpl.list_line_id ;

Line: 1145

SELECT request_line_id
            ,object_version_number
            ,product_context
            ,inventory_item_id
            ,prod_catg_id
            ,product_cat_set_id
            ,product_cost
            ,item_uom
            ,requested_discount_type
            ,requested_discount_value
            ,cost_basis
            ,max_qty
            ,limit_qty
            ,design_win
            ,end_customer_price
            ,requested_line_amount
            ,approved_discount_type
            ,approved_max_qty
            ,attribute_category
            ,attribute1
            ,attribute2
            ,attribute3
            ,attribute4
            ,attribute5
            ,attribute6
            ,attribute7
            ,attribute8
            ,attribute9
            ,attribute10
            ,attribute11
            ,attribute12
            ,attribute13
            ,attribute14
            ,attribute15
            ,vendor_approved_flag
            ,vendor_item_code
            ,end_customer_price_type
            ,end_customer_tolerance_type
            ,end_customer_tolerance_value
            ,org_id
            ,rejection_code
            ,requested_discount_currency
            ,product_cost_currency
            ,end_customer_currency
            ,approved_discount_currency
FROM ozf_sd_request_lines_all
WHERE request_line_id = p_request_line_id;

Line: 1194

   SELECT ozf_sd_request_lines_all_s.nextval
   FROM dual;

Line: 1208

l_modifier_line_tbl.delete;

Line: 1209

l_pricing_attr_tbl.delete;

Line: 1210

l_SDR_lines_tbl.delete;

Line: 1211

l_SDR_cust_tbl.delete;

Line: 1344

    OZF_SD_REQUEST_PUB.update_sd_Request(
              p_api_version_number    => 1.0,
              p_init_msg_list         => FND_API.G_TRUE,
              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_SDR_hdr_rec           => l_SDR_hdr_pub_rec,
              p_SDR_lines_tbl         => l_SDR_lines_tbl,
              p_SDR_cust_tbl          => l_SDR_cust_tbl
             );

Line: 1357

             OZF_UTILITY_PVT.debug_message('update_sd_Request x_return_status ' || x_return_status);

Line: 1443

l_modifier_line_tbl.delete;

Line: 1444

l_pricing_attr_tbl.delete;

Line: 1516

SELECT list_line_id
FROM ozf_offer_adjustment_lines
WHERE offer_adjustment_line_id = cp_offerAdjustmentLineId;

Line: 1622

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: 1695

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: 1703

x_modifier_line_tbl.delete;

Line: 1704

x_pricing_attr_tbl.delete;

Line: 1779

x_pricing_attr_tbl.delete;

Line: 1780

x_modifier_line_tbl.delete;

Line: 1865

l_modifier_line_tbl.delete;

Line: 1866

l_pricing_attr_tbl.delete;

Line: 1991

SELECT list_line_id, offer_adjustment_line_id
FROM ozf_offer_adjustment_lines
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 2026

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: 2049

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: 2106

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

Line: 2161

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: 2190

    l_modifier_line_rec.operation := 'UPDATE';

Line: 2243

l_modifier_line_tbl.delete;

Line: 2318

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

Line: 2324

x_pricing_attr_tbl.delete;

Line: 2378

l_pricing_attr_tbl.delete;

Line: 2550

SELECT related_modifier_id
FROM ozf_related_deal_lines
WHERE modifier_id = cp_listLineId;

Line: 2584

SELECT accrual_flag
FROM qp_list_lines
WHERE list_line_id = cp_listLineId;

Line: 2868

SELECT
list_line_id
FROM ozf_offer_adjustment_lines
WHERE offer_adjustment_id = cp_offerAdjustmentId;

Line: 2910

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: 2989

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: 3008

x_modifier_line_tbl.delete;

Line: 3070

l_modifier_line_tbl.delete;

Line: 3071

lx_modifier_tbl.delete;

Line: 3184

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

Line: 3218

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: 3237

x_modifier_line_tbl.delete;

Line: 3299

l_modifier_line_tbl.delete;

Line: 3360

l_modifiers_tbl.delete;

Line: 3405

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

Line: 3487

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: 3521

x_modifier_line_tbl.delete;

Line: 3553

SELECT TRUNC(l.end_date_active)
FROM ozf_offer_adj_new_lines l
WHERE l.offer_adj_new_line_id = p_offerAdjNewLineId;

Line: 3577

   SELECT request_header_id
            ,object_version_number
            ,org_id
            ,request_currency_code
            ,trunc(request_start_date)
            ,trunc(request_end_date)
   FROM ozf_sd_request_headers_all_b
  WHERE offer_id = p_list_header_id;

Line: 3612

   SELECT ozf_sd_request_lines_all_s.nextval
   FROM dual;

Line: 3616

  SELECT custom_setup_id
  FROM ozf_offers
  WHERE qp_list_header_id = p_list_header_id;

Line: 3621

   SELECT b.category_set_id
   FROM mtl_default_category_sets a ,
        mtl_category_sets_b b ,
        mtl_categories_v c
   WHERE a.functional_area_id in (7,11)
   AND   a.category_set_id   = b.category_set_id
   AND   b.structure_id      = c.structure_id
   AND   c.category_id       = p_category_id;

Line: 3631

    SELECT list_price_per_unit
    FROM mtl_system_items_kfv
    WHERE inventory_item_id = p_inventory_item_id
    AND organization_id     = p_org_id;

Line: 3637

   SELECT master_organization_id
   FROM   oe_system_parameters
   WHERE  org_id = p_header_org_id;

Line: 3642

   SELECT gs.currency_code
   FROM gl_sets_of_books gs ,
        ozf_sys_parameters_all org
   WHERE org.set_of_books_id = gs.set_of_books_id
   AND   org.org_id = p_org_id;

Line: 3775

SELECT custom_setup_id
  FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;

Line: 3786

l_modifier_line_tbl.delete;

Line: 3787

l_SDR_lines_tbl.delete;

Line: 3788

l_SDR_cust_tbl.delete;

Line: 3864

OZF_SD_REQUEST_PUB.update_sd_Request(
              p_api_version_number    => 1.0,
              p_init_msg_list         => FND_API.G_TRUE,
              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_SDR_hdr_rec           => l_SDR_hdr_pub_rec,
              p_SDR_lines_tbl         => l_SDR_lines_tbl,
              p_SDR_cust_tbl          => l_SDR_cust_tbl
             );

Line: 3877

         OZF_UTILITY_PVT.debug_message('update_sd_Request x_return_status ' || x_return_status);

Line: 3911

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: 3943

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: 3995

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: 4036

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: 4106

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

Line: 4154

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: 4200

SELECT offer_adj_new_line_id
FROM ozf_offer_adj_new_lines
WHERE parent_adj_line_id = cp_offerAdjNewLineId;

Line: 4258

x_pricing_attr_tbl.delete;

Line: 4300

x_modifier_line_tbl.delete;

Line: 4301

x_pricing_attr_tbl.delete;

Line: 4302

l_modifier_line_tbl.delete;

Line: 4303

l_pricing_attr_tbl.delete;

Line: 4382

l_modifier_line_tbl.delete;

Line: 4383

l_pricing_attr_tbl.delete;

Line: 4451

l_modifier_line_tbl.delete;

Line: 4491

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: 4524

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: 4596

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: 4651

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
          ,c.custom_setup_id
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: 4684

    l_modifier_rec.OFFER_OPERATION := Qp_Globals.G_OPR_UPDATE;

Line: 4685

    l_modifier_rec.MODIFIER_OPERATION := Qp_Globals.G_OPR_UPDATE;

Line: 4713

                    UPDATE ozf_sd_request_headers_all_b
                        SET request_start_date = l_dates.effective_date,
                               object_version_number = object_version_number + 1
                     WHERE offer_id = l_dates.list_header_id;

Line: 4722

END updateHeaderDate;

Line: 4747

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

Line: 4754

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