[Home] [Help]
112: IS
113: CURSOR c_listLines(cp_listLineId NUMBER)
114: IS
115: SELECT list_line_id, list_header_id , list_line_type_code, price_break_type_code
116: FROM qp_list_lines
117: WHERE list_line_id = cp_listLineId;
118: /*
119: Cursor works fine but the implementation is not satisfying, need to relook into the end date query
120: */
128: ) endDate
129: --greatest(nvl(d.start_date_active, a.effective_date - 5 ),nvl(d.start_date_active, a.effective_date - 5 ), a.effective_date - 1 ) endDate
130: , b.offer_type, b.qp_list_header_id
131: , c.start_date_active lineStartDate, d.start_date_active headerStartDate
132: FROM ozf_offer_adjustments_b a, ozf_offers b , qp_list_lines c , qp_list_headers_b d
133: WHERE
134: a.list_header_id = b.qp_list_header_id
135: AND b.qp_list_header_id = c.list_header_id
136: AND c.list_header_id = d.list_header_id
154: x_return_status := FND_API.G_RET_STS_SUCCESS;
155: -- retrieve end_date into local record
156: -- initialize record
157: -- populate record
158: -- call process_qp_list_lines
159: --l_modifier_line_tbl.delete;
160: OPEN c_endDate(cp_offerAdjustmentId => p_offerAdjustmentId , cp_listLineId => p_listLineId);
161: FETCH c_endDate INTO l_endDate;
162: CLOSE c_endDate;
275: , accum_attribute
276: --, accum_attr_run_src_flag
277: , list_line_type_code
278: FROM
279: qp_list_lines c
280: WHERE c.list_line_id = cp_listLineId;
281: i NUMBER := null;
282: l_modifiers_tbl Qp_Modifiers_Pub.modifiers_tbl_type;
283: BEGIN
502: IS
503: CURSOR c_discountLines(cp_listLineId NUMBER , cp_offerAdjustmentId NUMBER)
504: IS
505: 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
506: FROM qp_rltd_modifiers a, ozf_qp_discounts b , ozf_offer_adjustment_tiers c , qp_list_lines d
507: WHERE a.to_rltd_modifier_id = b.list_line_id(+)
508: and b.offer_discount_line_id = c.offer_discount_line_id(+)
509: AND a.to_rltd_modifier_id = d.list_line_id
510: AND a.from_rltd_modifier_id = cp_listLineId
682: l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
683: BEGIN
684: -- initialize return status, nested tables
685: -- for given adjustment id get the adjustment tiers
686: -- populate the qp_list_lines using the tiers and adjustment tiers
687: -- create the QP list lines
688: x_return_status := FND_API.G_RET_STS_SUCCESS;
689: x_modifier_line_tbl.delete;
690: x_pricing_attr_tbl.delete;
683: BEGIN
684: -- initialize return status, nested tables
685: -- for given adjustment id get the adjustment tiers
686: -- populate the qp_list_lines using the tiers and adjustment tiers
687: -- create the QP list lines
688: x_return_status := FND_API.G_RET_STS_SUCCESS;
689: x_modifier_line_tbl.delete;
690: x_pricing_attr_tbl.delete;
691: l_modifier_line_tbl.delete;
858: END LOOP;
859: END map_ozf_qp_lines;
860:
861: /**
862: Creates new qp_list_lines and qp_pricing_attributes
863: for a given offer_adjustment_id
864: */
865: PROCEDURE create_new_qp_lines
866: (
876: l_pricing_attr_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
877: CURSOR c_tierHeader(cp_offerAdjustmentId NUMBER)
878: IS
879: SELECT distinct parent_discount_line_id , c.list_line_id
880: FROM ozf_offer_adjustment_tiers a, ozf_offer_discount_lines b , ozf_qp_discounts c , qp_list_lines d
881: WHERE a.offer_discount_line_id = b.offer_discount_line_id
882: AND b.parent_discount_line_id = c.offer_discount_line_id
883: AND c.list_line_id = d.list_line_id
884: AND d.list_line_type_code = 'PBH'
945: -- passback the created modifier lines and pricing attributes.
946: END create_new_qp_lines;
947:
948: /**
949: Creates relation between two qp_list_lines for a given offer_adjustment_id
950: */
951: PROCEDURE relate_lines
952: (
953: p_from_list_line_id IN NUMBER
1051: BEGIN
1052: x_return_status := FND_API.G_RET_STS_SUCCESS;
1053: /*
1054: Corresponding to the discount changes, update the tier definitions.
1055: End Date the QP List lines corresponding to the tier definitions changed.
1056: Create new QP List lines with the updated tier definitions.(1)[1]
1057: Create new discount-tier mapping(1)
1058: *Create new product-product mapping
1059: */
1052: x_return_status := FND_API.G_RET_STS_SUCCESS;
1053: /*
1054: Corresponding to the discount changes, update the tier definitions.
1055: End Date the QP List lines corresponding to the tier definitions changed.
1056: Create new QP List lines with the updated tier definitions.(1)[1]
1057: Create new discount-tier mapping(1)
1058: *Create new product-product mapping
1059: */
1060: update_adj_vo_tiers
1155:
1156: END adjust_old_discounts;
1157:
1158: /**
1159: Old exclusions are not included in the new qp_list_lines created. Need to take care of that
1160: */
1161: PROCEDURE create_new_products
1162: (
1163: x_return_status OUT NOCOPY VARCHAR2
1255: , pricing_group_sequence
1256: , incompatibility_grp_code
1257: , product_precedence
1258: , proration_type_code
1259: FROM qp_list_lines
1260: WHERE list_header_id = cp_listHeaderId
1261: AND rownum < 2;
1262: BEGIN
1263: --x_return_status := FND_API.G_RET_STS_SUCCESS;
1672: /**
1673: This procedure creates the OZF qp mapping given the offDiscountProductId.
1674: This is a procedure for mapping a single product in OZF to the corresponding structure in QP
1675: The logic and assumptions used here are:
1676: 1. All the qp_list_lines are created with the offer_discount_line_id stored in the comments.
1677: 2. The offDiscountProductId passed in is mapped to all the pricing_attribute_id 's passed in
1678: */
1679: PROCEDURE map_ozf_qp_data
1680: (
1806: i NUMBER;
1807: CURSOR c_qpListLines(cp_offDiscountProductId NUMBER)
1808: IS
1809: SELECT b.list_line_id, a.product_attribute, a.product_attr_value , c.list_header_id
1810: FROM ozf_offer_discount_products a, ozf_qp_discounts b , qp_list_lines c
1811: WHERE a.offer_discount_line_id = b.offer_discount_line_id
1812: AND b.list_line_id = c.list_line_id
1813: AND a.off_discount_product_id = p_offDiscountProductId;
1814:
1822: V_PRICING_ATTR_tbl QP_MODIFIERS_PUB.Pricing_Attr_Tbl_Type;
1823: V_PRICING_ATTR_val_tbl QP_MODIFIERS_PUB.Pricing_Attr_Val_Tbl_Type;
1824: BEGIN
1825: -- initialize
1826: -- retrieve all the qp_list_lines corresponding to the discount structure to which the exclusion is added.
1827: -- exclude this new product from all the qp discount structures.
1828: -- map the newly created qp_pricing_attributes to this exclusion product.
1829: x_return_status := FND_API.G_RET_STS_SUCCESS;
1830: l_pricing_attr_tbl.delete;
2087: x_return_status := FND_API.G_RET_STS_SUCCESS;
2088: /*
2089: New Products are added to Particular discount tables, so discount table id is stored .
2090: Create new Product in OZF Tables
2091: Get the Discount table id for the new product and create QP list lines using the Discount table.(1)[1]
2092: Create new discount-tier mapping.(1)
2093: */
2094: create_new_products
2095: (