[Home] [Help]
145: -- If The Security profile is off, in which case local(operating unit specific offers cannot be created), clear the org_id and set the global_flag to Y, for the data sent to QP
146: -- while the org_id is sent to ozf_offers
147: -- If the Offer is Lumpsum or scandata then , raise an exception if the org_id is not passed in since scandata and lumpsum offers always are org specific
148: -- Fri Oct 21 2005:6/45 PM RSSHARMA r12 changes to function discount_lines_exist
149: -- Changed function to query ozf_offer_discount_lines and ozf_offer_discount_products
150: -- if offer type is VOLUME_OFFER
151: -- Mon Oct 31 2005:3/1 PM RSSHARMA Fixed bug # 4706367. Raise Error for Lumpsum and scandata only if it is null or g_miss in create mode
152: -- and null indicated by (g_miss) in update mode
153: -- Mon Nov 14 2005:4/45 PM Fixed bug # 4625922. Due to issues in process_regular_discounts, the user was not able to perform any database operations
8444: This function checks id active discount rules exist for an offer.
8445: If active discount rules exist for the offer then the function returns 0
8446: else it returns -1
8447: r12 changes
8448: Changed function to query ozf_offer_discount_lines and ozf_offer_discount_products
8449: if offer type is VOLUME_OFFER
8450: */
8451: FUNCTION discount_lines_exist(p_list_header_id IN NUMBER) RETURN NUMBER
8452: IS
8463: WHERE qp_list_header_id = cp_listHeaderId;
8464:
8465: CURSOR c_voCnt(cp_listHeaderId NUMBER) IS
8466: SELECT 1 FROM dual WHERE EXISTS(SELECT
8467: 'X' FROM ozf_offer_discount_lines a, ozf_offer_discount_products b, ozf_offers c
8468: WHERE a.offer_discount_line_id = b.offer_discount_line_id
8469: AND a.offer_id = c.offer_id
8470: AND c.qp_list_header_id = cp_listHeaderId
8471: );
8765: AND arc_act_budget_used_by = 'OFFR';
8766: -- end comment
8767:
8768: CURSOR c_vo_line (p_qp_list_header_id NUMBER)IS
8769: SELECT count(*) FROM ozf_offer_discount_products a, ozf_offer_discount_lines b
8770: WHERE a.offer_discount_line_id = b.offer_discount_line_id
8771: AND b.offer_id = (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_qp_list_header_id);
8772:
8773: CURSOR c_emptyDiscStruct(cp_qpListheaderId NUMBER) IS
8775: ozf_offer_discount_lines a , ozf_offers b
8776: WHERE a.tier_type = 'PBH'
8777: AND a.offer_id = b.offer_id --8013
8778: AND
8779: ( NOT EXISTS(SELECT 'X' FROM ozf_offer_discount_products WHERE offer_discount_line_id = a.offer_discount_line_id AND excluder_flag = 'N')
8780: OR NOT EXISTS(SELECT 'X' FROM ozf_offer_discount_lines WHERE parent_discount_line_id = a.offer_discount_line_id)
8781: )
8782: AND b.qp_list_header_id = cp_qpListheaderId;
8783:
9724: , b.volume_break_type
9725: , b.volume_type
9726: , b.discount_type
9727: , b.uom_code
9728: FROM ozf_offer_discount_products a, ozf_offer_discount_lines b
9729: WHERE a.offer_discount_line_id = b.offer_discount_line_id
9730: AND a.off_discount_product_id = cp_offDiscountProductId;
9731: BEGIN
9732: x_return_status := FND_API.G_RET_STS_SUCCESS;
9768: )
9769: RETURN NUMBER IS
9770: CURSOR c_discountLineId(cp_offDiscountProductId NUMBER) IS
9771: SELECT offer_discount_line_id
9772: FROM ozf_offer_discount_products
9773: WHERE off_discount_product_id = cp_offDiscountProductId ;
9774: l_discountLineId ozf_offer_discount_lines.offer_discount_line_id%TYPE;
9775: BEGIN
9776: OPEN c_discountLineId(cp_offDiscountProductId => p_offDiscountProductId);
9826: IS
9827: CURSOR c_discounts( cp_offDiscountProductId NUMBER) IS
9828: SELECT a.volume_break_type
9829: , a.discount_type
9830: FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
9831: WHERE a.offer_discount_line_id = b.offer_discount_line_id
9832: AND b.off_discount_product_id = cp_offDiscountProductId;
9833: BEGIN
9834: x_return_status := FND_API.G_RET_STS_SUCCESS;
9872: SELECT decode(c.apply_discount_flag, 'N',0,a.discount) discount
9873: , decode(c.apply_discount_flag, 'N',null,a.formula_id) formula_id
9874: , b.volume_break_type
9875: , b.discount_type
9876: FROM ozf_offer_discount_lines a, ozf_offer_discount_lines b, ozf_offer_discount_products c
9877: WHERE a.parent_discount_line_id = b.offer_discount_line_id
9878: AND b.offer_discount_line_id = c.offer_discount_line_id
9879: AND c.excluder_flag = 'N'
9880: AND c.off_discount_product_id = cp_offDiscountProductId
9915: RETURN NUMBER
9916: IS
9917: CURSOR c_minVolume(cp_offDiscountProductId NUMBER) IS
9918: SELECT min(volume_from)
9919: FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
9920: WHERE a.parent_discount_line_id = b.offer_discount_line_id
9921: AND b.off_discount_product_id = cp_offDiscountProductId;
9922:
9923: l_minVolume OZF_OFFER_DISCOUNT_LINES.volume_from%TYPE;
9942: )
9943: IS
9944: CURSOR c_discounts(cp_offerDiscountProductId NUMBER) IS
9945: SELECT a.offer_discount_line_id
9946: FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
9947: WHERE a.parent_discount_line_id = b.offer_discount_line_id
9948: AND b.off_discount_product_id = cp_offerDiscountProductId;
9949: i NUMBER;
9950: BEGIN
10123: )
10124: IS
10125: CURSOR c_exclusions(cp_qpListHeaderId IN NUMBER) IS
10126: SELECT a.product_attribute, a.product_attr_value, a.excluder_flag
10127: FROM ozf_offer_discount_products a, ozf_offers b
10128: WHERE a.offer_id = b.offer_id
10129: AND a.excluder_flag = 'Y'
10130: AND b.qp_list_header_id = cp_qpListHeaderId;
10131: i NUMBER;
10449: l_api_version_number CONSTANT NUMBER := 1.0;
10450:
10451: CURSOR c_products(cp_qpListHeaderId NUMBER)IS
10452: SELECT a.off_discount_product_id
10453: FROM ozf_offer_discount_products a, ozf_offers b
10454: WHERE a.offer_id = b.offer_id
10455: AND a.excluder_flag = 'N'
10456: AND b.qp_list_header_id = cp_qpListHeaderId;
10457: