[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_PUB
Source
1 Package BODY OZF_Offer_PUB AS
2 /* $Header: ozfpofrb.pls 120.12.12020000.3 2013/03/10 03:16:25 bkunjan ship $ */
3
4 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
5
6
7 PROCEDURE process_modifiers(
8 p_init_msg_list IN VARCHAR2
9 ,p_api_version IN NUMBER
10 ,p_commit IN VARCHAR2
11 ,x_return_status OUT NOCOPY VARCHAR2
12 ,x_msg_count OUT NOCOPY NUMBER
13 ,x_msg_data OUT NOCOPY VARCHAR2
14 ,p_offer_type IN VARCHAR2
15 ,p_modifier_list_rec IN modifier_list_rec_type
16 ,p_modifier_line_tbl IN modifier_line_tbl_type
17 ,p_qualifier_tbl IN qualifiers_tbl_type
18 ,p_budget_tbl IN budget_tbl_type
19 ,p_act_product_tbl IN act_product_tbl_type
20 ,p_discount_tbl IN discount_line_tbl_type
21 ,p_excl_tbl IN excl_rec_tbl_type
22 ,p_offer_tier_tbl IN offer_tier_tbl_type
23 ,p_prod_tbl IN prod_rec_tbl_type
24 ,p_na_qualifier_tbl IN na_qualifier_tbl_type
25 ,x_qp_list_header_id OUT NOCOPY NUMBER
26 ,x_error_location OUT NOCOPY NUMBER)
27 IS
28
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_api_name CONSTANT VARCHAR2(30) := 'process_modifiers';
31
32 l_act_product_rec ams_actproduct_pvt.act_product_rec_type;
33 l_act_product_id NUMBER;
34 l_offer_id NUMBER;
35 l_obj_ver_num NUMBER;
36 l_discount_line_id NUMBER;
37 l_discount_product_id NUMBER;
38 l_activity_budget_id NUMBER;
39 l_na_qualifier_id NUMBER;
40 l_dummy NUMBER;
41 l_prod_index NUMBER := 0;
42 l_excl_index NUMBER := 0;
43
44 l_discount_line_rec ozf_disc_line_pvt.ozf_discount_line_rec_type;
45 l_prod_rec ozf_disc_line_pvt.ozf_prod_rec_type;
46 l_excl_rec ozf_disc_line_pvt.ozf_excl_rec_type;
47 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
48 l_offer_tier_rec ozf_disc_line_pvt.ozf_offer_tier_rec_type;
49 l_na_qualifier_rec ozf_offr_qual_pvt.ozf_offr_qual_rec_type;
50 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
51 l_modifier_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
52 l_qualifiers_tbl ozf_offer_pvt.qualifiers_tbl_type;
53 l_exclusion_tbl ozf_offer_pvt.pricing_attr_tbl_type;
54 l_qualifiers_tbl_out qp_qualifier_rules_pub.qualifiers_tbl_type;
55
56 CURSOR c_offer_info(l_qp_list_header_id NUMBER) IS
57 SELECT offer_id, offer_type, custom_setup_id, offer_code, tier_level, object_version_number
58 FROM ozf_offers
59 WHERE qp_list_header_id = l_qp_list_header_id;
60
61 CURSOR c_act_budget_obj_ver(l_act_budg_id NUMBER) IS
62 SELECT object_version_number
63 FROM ozf_act_budgets
64 WHERE activity_budget_id = l_act_budg_id;
65 BEGIN
66
67 SAVEPOINT process_modifiers_pub;
68 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
69 Fnd_Msg_Pub.initialize;
70 END IF;
71
72 IF NOT Fnd_Api.compatible_api_call(l_api_version,
73 p_api_version,
74 l_api_name,
75 g_pkg_name)
76 THEN
77 RAISE Fnd_Api.g_exc_unexpected_error;
78 END IF;
79
80 x_return_status := Fnd_Api.g_ret_sts_success;
81 x_error_location := 0;
82
83 l_modifier_list_rec.OFFER_ID := p_modifier_list_rec.OFFER_ID;
84 l_modifier_list_rec.QP_LIST_HEADER_ID := p_modifier_list_rec.QP_LIST_HEADER_ID;
85 l_modifier_list_rec.OFFER_TYPE := p_modifier_list_rec.OFFER_TYPE;
86 l_modifier_list_rec.OFFER_CODE := p_modifier_list_rec.OFFER_CODE;
87 l_modifier_list_rec.ACTIVITY_MEDIA_ID := p_modifier_list_rec.ACTIVITY_MEDIA_ID;
88 l_modifier_list_rec.REUSABLE := p_modifier_list_rec.REUSABLE;
89 l_modifier_list_rec.USER_STATUS_ID := p_modifier_list_rec.USER_STATUS_ID;
90 l_modifier_list_rec.WF_ITEM_KEY := p_modifier_list_rec.WF_ITEM_KEY;
91 l_modifier_list_rec.CUSTOMER_REFERENCE := p_modifier_list_rec.CUSTOMER_REFERENCE;
92 l_modifier_list_rec.BUYING_GROUP_CONTACT_ID := p_modifier_list_rec.BUYING_GROUP_CONTACT_ID;
93 l_modifier_list_rec.OBJECT_VERSION_NUMBER := p_modifier_list_rec.OBJECT_VERSION_NUMBER;
94 l_modifier_list_rec.PERF_DATE_FROM := p_modifier_list_rec.PERF_DATE_FROM;
95 l_modifier_list_rec.PERF_DATE_TO := p_modifier_list_rec.PERF_DATE_TO;
96 l_modifier_list_rec.STATUS_CODE := p_modifier_list_rec.STATUS_CODE;
97 l_modifier_list_rec.STATUS_DATE := p_modifier_list_rec.STATUS_DATE;
98 l_modifier_list_rec.MODIFIER_LEVEL_CODE := p_modifier_list_rec.MODIFIER_LEVEL_CODE;
99 l_modifier_list_rec.ORDER_VALUE_DISCOUNT_TYPE := p_modifier_list_rec.ORDER_VALUE_DISCOUNT_TYPE;
100 l_modifier_list_rec.LUMPSUM_AMOUNT := p_modifier_list_rec.LUMPSUM_AMOUNT;
101 l_modifier_list_rec.LUMPSUM_PAYMENT_TYPE := p_modifier_list_rec.LUMPSUM_PAYMENT_TYPE;
102 l_modifier_list_rec.CUSTOM_SETUP_ID := p_modifier_list_rec.CUSTOM_SETUP_ID;
103 l_modifier_list_rec.OFFER_AMOUNT := p_modifier_list_rec.OFFER_AMOUNT;
104 l_modifier_list_rec.BUDGET_AMOUNT_TC := p_modifier_list_rec.BUDGET_AMOUNT_TC;
105 l_modifier_list_rec.BUDGET_AMOUNT_FC := p_modifier_list_rec.BUDGET_AMOUNT_FC;
106 l_modifier_list_rec.TRANSACTION_CURRENCY_CODE := p_modifier_list_rec.TRANSACTION_CURRENCY_CODE;
107 l_modifier_list_rec.FUNCTIONAL_CURRENCY_CODE := p_modifier_list_rec.FUNCTIONAL_CURRENCY_CODE;
108 l_modifier_list_rec.CONTEXT := p_modifier_list_rec.CONTEXT;
109 l_modifier_list_rec.ATTRIBUTE1 := p_modifier_list_rec.ATTRIBUTE1;
110 l_modifier_list_rec.ATTRIBUTE2 := p_modifier_list_rec.ATTRIBUTE2;
111 l_modifier_list_rec.ATTRIBUTE3 := p_modifier_list_rec.ATTRIBUTE3;
112 l_modifier_list_rec.ATTRIBUTE4 := p_modifier_list_rec.ATTRIBUTE4;
113 l_modifier_list_rec.ATTRIBUTE5 := p_modifier_list_rec.ATTRIBUTE5;
114 l_modifier_list_rec.ATTRIBUTE6 := p_modifier_list_rec.ATTRIBUTE6;
115 l_modifier_list_rec.ATTRIBUTE7 := p_modifier_list_rec.ATTRIBUTE7;
116 l_modifier_list_rec.ATTRIBUTE8 := p_modifier_list_rec.ATTRIBUTE8;
117 l_modifier_list_rec.ATTRIBUTE9 := p_modifier_list_rec.ATTRIBUTE9;
118 l_modifier_list_rec.ATTRIBUTE10 := p_modifier_list_rec.ATTRIBUTE10;
119 l_modifier_list_rec.ATTRIBUTE11 := p_modifier_list_rec.ATTRIBUTE11;
120 l_modifier_list_rec.ATTRIBUTE12 := p_modifier_list_rec.ATTRIBUTE12;
121 l_modifier_list_rec.ATTRIBUTE13 := p_modifier_list_rec.ATTRIBUTE13;
122 l_modifier_list_rec.ATTRIBUTE14 := p_modifier_list_rec.ATTRIBUTE14;
123 l_modifier_list_rec.ATTRIBUTE15 := p_modifier_list_rec.ATTRIBUTE15;
124 l_modifier_list_rec.CURRENCY_CODE := p_modifier_list_rec.CURRENCY_CODE;
125 l_modifier_list_rec.START_DATE_ACTIVE := p_modifier_list_rec.START_DATE_ACTIVE;
126 l_modifier_list_rec.END_DATE_ACTIVE := p_modifier_list_rec.END_DATE_ACTIVE;
127 l_modifier_list_rec.LIST_TYPE_CODE := p_modifier_list_rec.LIST_TYPE_CODE;
128 l_modifier_list_rec.DISCOUNT_LINES_FLAG := p_modifier_list_rec.DISCOUNT_LINES_FLAG;
129 l_modifier_list_rec.NAME := p_modifier_list_rec.NAME;
130 l_modifier_list_rec.DESCRIPTION := p_modifier_list_rec.DESCRIPTION;
131 l_modifier_list_rec.COMMENTS := p_modifier_list_rec.COMMENTS;
132 l_modifier_list_rec.ASK_FOR_FLAG := p_modifier_list_rec.ASK_FOR_FLAG;
133 l_modifier_list_rec.START_DATE_ACTIVE_FIRST := p_modifier_list_rec.START_DATE_ACTIVE_FIRST;
134 l_modifier_list_rec.END_DATE_ACTIVE_FIRST := p_modifier_list_rec.END_DATE_ACTIVE_FIRST;
135 l_modifier_list_rec.ACTIVE_DATE_FIRST_TYPE := p_modifier_list_rec.ACTIVE_DATE_FIRST_TYPE;
136 l_modifier_list_rec.START_DATE_ACTIVE_SECOND := p_modifier_list_rec.START_DATE_ACTIVE_SECOND;
137 l_modifier_list_rec.END_DATE_ACTIVE_SECOND := p_modifier_list_rec.END_DATE_ACTIVE_SECOND;
138 l_modifier_list_rec.ACTIVE_DATE_SECOND_TYPE := p_modifier_list_rec.ACTIVE_DATE_SECOND_TYPE;
139 l_modifier_list_rec.ACTIVE_FLAG := p_modifier_list_rec.ACTIVE_FLAG;
140 l_modifier_list_rec.MAX_NO_OF_USES := p_modifier_list_rec.MAX_NO_OF_USES;
141 l_modifier_list_rec.BUDGET_SOURCE_ID := p_modifier_list_rec.BUDGET_SOURCE_ID;
142 l_modifier_list_rec.BUDGET_SOURCE_TYPE := p_modifier_list_rec.BUDGET_SOURCE_TYPE;
143 l_modifier_list_rec.OFFER_USED_BY_ID := p_modifier_list_rec.OFFER_USED_BY_ID;
144 l_modifier_list_rec.OFFER_USED_BY := p_modifier_list_rec.OFFER_USED_BY;
145 l_modifier_list_rec.QL_QUALIFIER_TYPE := p_modifier_list_rec.QL_QUALIFIER_TYPE;
146 l_modifier_list_rec.QL_QUALIFIER_ID := p_modifier_list_rec.QL_QUALIFIER_ID;
147 l_modifier_list_rec.DISTRIBUTION_TYPE := p_modifier_list_rec.DISTRIBUTION_TYPE;
148 l_modifier_list_rec.AMOUNT_LIMIT_ID := p_modifier_list_rec.AMOUNT_LIMIT_ID;
149 l_modifier_list_rec.USES_LIMIT_ID := p_modifier_list_rec.USES_LIMIT_ID;
150 l_modifier_list_rec.OFFER_OPERATION := p_modifier_list_rec.OFFER_OPERATION;
151 l_modifier_list_rec.MODIFIER_OPERATION := p_modifier_list_rec.MODIFIER_OPERATION;
152 l_modifier_list_rec.BUDGET_OFFER_YN := p_modifier_list_rec.BUDGET_OFFER_YN;
153 l_modifier_list_rec.BREAK_TYPE := p_modifier_list_rec.BREAK_TYPE;
154 l_modifier_list_rec.RETROACTIVE := p_modifier_list_rec.RETROACTIVE;
155 l_modifier_list_rec.VOLUME_OFFER_TYPE := p_modifier_list_rec.VOLUME_OFFER_TYPE;
156 l_modifier_list_rec.CONFIDENTIAL_FLAG := p_modifier_list_rec.CONFIDENTIAL_FLAG;
157 l_modifier_list_rec.COMMITTED_AMOUNT_EQ_MAX := p_modifier_list_rec.COMMITTED_AMOUNT_EQ_MAX;
158 l_modifier_list_rec.SOURCE_FROM_PARENT := p_modifier_list_rec.SOURCE_FROM_PARENT;
159 l_modifier_list_rec.BUYER_NAME := p_modifier_list_rec.BUYER_NAME;
160 l_modifier_list_rec.TIER_LEVEL := p_modifier_list_rec.TIER_LEVEL;
161 l_modifier_list_rec.NA_RULE_HEADER_ID := p_modifier_list_rec.NA_RULE_HEADER_ID;
162 l_modifier_list_rec.sales_method_flag := p_modifier_list_rec.sales_method_flag;
163 l_modifier_list_rec.global_flag := p_modifier_list_rec.global_flag;
164 l_modifier_list_rec.orig_org_id := p_modifier_list_rec.orig_org_id;
165
166 -- offer header
167 IF p_modifier_list_rec.offer_operation = 'CREATE' THEN
168 l_modifier_list_rec.offer_operation := 'CREATE';
169 l_modifier_list_rec.modifier_operation := 'CREATE';
170 l_modifier_list_rec.status_code := 'DRAFT';
171 l_modifier_list_rec.user_status_id := ozf_utility_pvt.get_default_user_status('OZF_OFFER_STATUS','DRAFT');--1600;
172
173 IF p_modifier_list_rec.OWNER_ID IS NULL OR p_modifier_list_rec.OWNER_ID = fnd_api.g_miss_num THEN
174 l_modifier_list_rec.OWNER_ID := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
175 ELSE
176 l_modifier_list_rec.OWNER_ID := p_modifier_list_rec.OWNER_ID;
177 END IF;
178
179 Ozf_Offer_Pvt.process_modifiers(
180 p_init_msg_list => p_init_msg_list
181 ,p_api_version => p_api_version
182 ,p_commit => p_commit
183 ,x_return_status => x_return_status
184 ,x_msg_count => x_msg_count
185 ,x_msg_data => x_msg_data
186 ,p_offer_type => p_offer_type
187 ,p_modifier_list_rec => l_modifier_list_rec
188 ,p_modifier_line_tbl => l_modifier_line_tbl -- need to create header first. use empty line.
189 ,x_qp_list_header_id => x_qp_list_header_id
190 ,x_error_location => x_error_location);
191
192 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
193 RAISE Fnd_Api.g_exc_error;
194 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
195 RAISE Fnd_Api.g_exc_unexpected_error;
196 END IF;
197
198 l_modifier_list_rec.qp_list_header_id := x_qp_list_header_id;
199 IF p_modifier_list_rec.user_status_id IS NOT NULL AND p_modifier_list_rec.user_status_id <> fnd_api.g_miss_num THEN -- might have additional stage eg ACTIVE to go
200 l_modifier_list_rec.offer_operation := 'UPDATE';
201 l_modifier_list_rec.modifier_operation := 'UPDATE';
202 l_modifier_list_rec.user_status_id := p_modifier_list_rec.user_status_id;
203 l_modifier_list_rec.status_code := ozf_utility_pvt.get_system_status_code(p_modifier_list_rec.user_status_id);
204 ELSE -- creating draft offer hdr only
205 l_modifier_list_rec.offer_operation := fnd_api.g_miss_char;
206 l_modifier_list_rec.modifier_operation := fnd_api.g_miss_char;
207 l_modifier_list_rec.user_status_id := fnd_api.g_miss_num;
208 l_modifier_list_rec.status_code := fnd_api.g_miss_char;
209 END IF;
210 ELSE
211 x_qp_list_header_id := p_modifier_list_rec.qp_list_header_id;
212 IF p_modifier_list_rec.OWNER_ID IS NULL THEN
213 l_modifier_list_rec.OWNER_ID := fnd_api.g_miss_num;
214 ELSE
215 l_modifier_list_rec.OWNER_ID := p_modifier_list_rec.OWNER_ID;
216 END IF;
217 END IF;
218
219 OPEN c_offer_info(x_qp_list_header_id);
220 FETCH c_offer_info INTO l_modifier_list_rec.offer_id, l_modifier_list_rec.offer_type, l_modifier_list_rec.custom_setup_id, l_modifier_list_rec.offer_code, l_modifier_list_rec.tier_level, l_modifier_list_rec.object_version_number;
221 CLOSE c_offer_info;
222
223 l_offer_id := l_modifier_list_rec.offer_id;
224 l_obj_ver_num := l_modifier_list_rec.object_version_number;
225
226 IF p_modifier_line_tbl.COUNT > 0 THEN
227 FOR i IN p_modifier_line_tbl.FIRST..p_modifier_line_tbl.LAST LOOP
228 IF p_modifier_line_tbl(i).excluder_flag = 'Y' THEN
229 l_excl_index := l_excl_index + 1;
230 l_exclusion_tbl(l_excl_index).operation := p_modifier_line_tbl(i).operation;
231 l_exclusion_tbl(l_excl_index).pricing_attribute_id := p_modifier_line_tbl(i).pricing_attribute_id;
232 l_exclusion_tbl(l_excl_index).list_line_id := p_modifier_line_tbl(i).list_line_id;
233 l_exclusion_tbl(l_excl_index).product_attribute_context := p_modifier_line_tbl(i).product_attribute_context;
234 l_exclusion_tbl(l_excl_index).product_attribute := p_modifier_line_tbl(i).product_attr;
235 l_exclusion_tbl(l_excl_index).product_attr_value := p_modifier_line_tbl(i).product_attr_val;
236 ELSE
237 l_prod_index := l_prod_index + 1;
238 l_modifier_line_tbl(l_prod_index).OFFER_LINE_TYPE := p_modifier_line_tbl(i).OFFER_LINE_TYPE;
239 l_modifier_line_tbl(l_prod_index).OPERATION := p_modifier_line_tbl(i).OPERATION;
240 l_modifier_line_tbl(l_prod_index).LIST_LINE_ID := p_modifier_line_tbl(i).LIST_LINE_ID;
241 l_modifier_line_tbl(l_prod_index).LIST_HEADER_ID := x_qp_list_header_id;
242 l_modifier_line_tbl(l_prod_index).LIST_LINE_TYPE_CODE := p_modifier_line_tbl(i).LIST_LINE_TYPE_CODE;
243 l_modifier_line_tbl(l_prod_index).OPERAND := p_modifier_line_tbl(i).OPERAND;
244 l_modifier_line_tbl(l_prod_index).START_DATE_ACTIVE := p_modifier_line_tbl(i).START_DATE_ACTIVE;
245 l_modifier_line_tbl(l_prod_index).END_DATE_ACTIVE := p_modifier_line_tbl(i).END_DATE_ACTIVE;
246 l_modifier_line_tbl(l_prod_index).ARITHMETIC_OPERATOR := p_modifier_line_tbl(i).ARITHMETIC_OPERATOR;
247 l_modifier_line_tbl(l_prod_index).INACTIVE_FLAG := p_modifier_line_tbl(i).ACTIVE_FLAG;
248 l_modifier_line_tbl(l_prod_index).QD_OPERAND := p_modifier_line_tbl(i).QD_OPERAND;
249 l_modifier_line_tbl(l_prod_index).QD_ARITHMETIC_OPERATOR := p_modifier_line_tbl(i).QD_ARITHMETIC_OPERATOR;
250 l_modifier_line_tbl(l_prod_index).QD_RELATED_DEAL_LINES_ID := p_modifier_line_tbl(i).QD_RELATED_DEAL_LINES_ID;
251 l_modifier_line_tbl(l_prod_index).QD_OBJECT_VERSION_NUMBER := p_modifier_line_tbl(i).QD_OBJECT_VERSION_NUMBER;
252 l_modifier_line_tbl(l_prod_index).QD_ESTIMATED_QTY_IS_MAX := p_modifier_line_tbl(i).QD_ESTIMATED_QTY_IS_MAX;
253 l_modifier_line_tbl(l_prod_index).QD_LIST_LINE_ID := p_modifier_line_tbl(i).QD_LIST_LINE_ID;
254 l_modifier_line_tbl(l_prod_index).QD_ESTIMATED_AMOUNT_IS_MAX := p_modifier_line_tbl(i).QD_ESTIMATED_AMOUNT_IS_MAX;
255 l_modifier_line_tbl(l_prod_index).ESTIM_GL_VALUE := p_modifier_line_tbl(i).ESTIM_GL_VALUE;
256 l_modifier_line_tbl(l_prod_index).BENEFIT_PRICE_LIST_LINE_ID := p_modifier_line_tbl(i).BENEFIT_PRICE_LIST_LINE_ID;
257 l_modifier_line_tbl(l_prod_index).BENEFIT_LIMIT := p_modifier_line_tbl(i).BENEFIT_LIMIT;
258 l_modifier_line_tbl(l_prod_index).BENEFIT_QTY := p_modifier_line_tbl(i).BENEFIT_QTY;
259 l_modifier_line_tbl(l_prod_index).BENEFIT_UOM_CODE := p_modifier_line_tbl(i).BENEFIT_UOM_CODE;
260 l_modifier_line_tbl(l_prod_index).SUBSTITUTION_CONTEXT := p_modifier_line_tbl(i).SUBSTITUTION_CONTEXT;
261 l_modifier_line_tbl(l_prod_index).SUBSTITUTION_ATTR := p_modifier_line_tbl(i).SUBSTITUTION_ATTR;
262 l_modifier_line_tbl(l_prod_index).SUBSTITUTION_VAL := p_modifier_line_tbl(i).SUBSTITUTION_VAL;
263 l_modifier_line_tbl(l_prod_index).PRICE_BREAK_TYPE_CODE := p_modifier_line_tbl(i).PRICE_BREAK_TYPE_CODE;
264 l_modifier_line_tbl(l_prod_index).PRICING_ATTRIBUTE_ID := p_modifier_line_tbl(i).PRICING_ATTRIBUTE_ID;
265 l_modifier_line_tbl(l_prod_index).PRODUCT_ATTRIBUTE_CONTEXT := p_modifier_line_tbl(i).PRODUCT_ATTRIBUTE_CONTEXT;
266 l_modifier_line_tbl(l_prod_index).PRODUCT_ATTR := p_modifier_line_tbl(i).PRODUCT_ATTR;
267 l_modifier_line_tbl(l_prod_index).PRODUCT_ATTR_VAL := p_modifier_line_tbl(i).PRODUCT_ATTR_VAL;
268 l_modifier_line_tbl(l_prod_index).PRODUCT_UOM_CODE := p_modifier_line_tbl(i).PRODUCT_UOM_CODE;
269 l_modifier_line_tbl(l_prod_index).PRICING_ATTRIBUTE_CONTEXT := p_modifier_line_tbl(i).PRICING_ATTRIBUTE_CONTEXT;
270 l_modifier_line_tbl(l_prod_index).PRICING_ATTR := p_modifier_line_tbl(i).PRICING_ATTR;
271 l_modifier_line_tbl(l_prod_index).PRICING_ATTR_VALUE_FROM := p_modifier_line_tbl(i).PRICING_ATTR_VALUE_FROM;
272 l_modifier_line_tbl(l_prod_index).PRICING_ATTR_VALUE_TO := p_modifier_line_tbl(i).PRICING_ATTR_VALUE_TO;
273 l_modifier_line_tbl(l_prod_index).EXCLUDER_FLAG := p_modifier_line_tbl(i).EXCLUDER_FLAG;
274 l_modifier_line_tbl(l_prod_index).ORDER_VALUE_FROM := p_modifier_line_tbl(i).ORDER_VALUE_FROM;
275 l_modifier_line_tbl(l_prod_index).ORDER_VALUE_TO := p_modifier_line_tbl(i).ORDER_VALUE_TO;
276 l_modifier_line_tbl(l_prod_index).QUALIFIER_ID := p_modifier_line_tbl(i).QUALIFIER_ID;
277 l_modifier_line_tbl(l_prod_index).COMMENTS := p_modifier_line_tbl(i).COMMENTS;
278 l_modifier_line_tbl(l_prod_index).CONTEXT := p_modifier_line_tbl(i).CONTEXT;
279 l_modifier_line_tbl(l_prod_index).ATTRIBUTE1 := p_modifier_line_tbl(i).ATTRIBUTE1;
280 l_modifier_line_tbl(l_prod_index).ATTRIBUTE2 := p_modifier_line_tbl(i).ATTRIBUTE2;
281 l_modifier_line_tbl(l_prod_index).ATTRIBUTE3 := p_modifier_line_tbl(i).ATTRIBUTE3;
282 l_modifier_line_tbl(l_prod_index).ATTRIBUTE4 := p_modifier_line_tbl(i).ATTRIBUTE4;
283 l_modifier_line_tbl(l_prod_index).ATTRIBUTE5 := p_modifier_line_tbl(i).ATTRIBUTE5;
284 l_modifier_line_tbl(l_prod_index).ATTRIBUTE6 := p_modifier_line_tbl(i).ATTRIBUTE6;
285 l_modifier_line_tbl(l_prod_index).ATTRIBUTE7 := p_modifier_line_tbl(i).ATTRIBUTE7;
286 l_modifier_line_tbl(l_prod_index).ATTRIBUTE8 := p_modifier_line_tbl(i).ATTRIBUTE8;
287 l_modifier_line_tbl(l_prod_index).ATTRIBUTE9 := p_modifier_line_tbl(i).ATTRIBUTE9;
288 l_modifier_line_tbl(l_prod_index).ATTRIBUTE10 := p_modifier_line_tbl(i).ATTRIBUTE10;
289 l_modifier_line_tbl(l_prod_index).ATTRIBUTE11 := p_modifier_line_tbl(i).ATTRIBUTE11;
290 l_modifier_line_tbl(l_prod_index).ATTRIBUTE12 := p_modifier_line_tbl(i).ATTRIBUTE12;
291 l_modifier_line_tbl(l_prod_index).ATTRIBUTE13 := p_modifier_line_tbl(i).ATTRIBUTE13;
292 l_modifier_line_tbl(l_prod_index).ATTRIBUTE14 := p_modifier_line_tbl(i).ATTRIBUTE14;
293 l_modifier_line_tbl(l_prod_index).ATTRIBUTE15 := p_modifier_line_tbl(i).ATTRIBUTE15;
294 l_modifier_line_tbl(l_prod_index).MAX_QTY_PER_ORDER := p_modifier_line_tbl(i).MAX_QTY_PER_ORDER;
295 l_modifier_line_tbl(l_prod_index).MAX_QTY_PER_ORDER_ID := p_modifier_line_tbl(i).MAX_QTY_PER_ORDER_ID;
296 l_modifier_line_tbl(l_prod_index).MAX_QTY_PER_CUSTOMER := p_modifier_line_tbl(i).MAX_QTY_PER_CUSTOMER;
297 l_modifier_line_tbl(l_prod_index).MAX_QTY_PER_CUSTOMER_ID := p_modifier_line_tbl(i).MAX_QTY_PER_CUSTOMER_ID;
298 l_modifier_line_tbl(l_prod_index).MAX_QTY_PER_RULE := p_modifier_line_tbl(i).MAX_QTY_PER_RULE;
299 l_modifier_line_tbl(l_prod_index).MAX_QTY_PER_RULE_ID := p_modifier_line_tbl(i).MAX_QTY_PER_RULE_ID;
300 l_modifier_line_tbl(l_prod_index).MAX_ORDERS_PER_CUSTOMER := p_modifier_line_tbl(i).MAX_ORDERS_PER_CUSTOMER;
301 l_modifier_line_tbl(l_prod_index).MAX_ORDERS_PER_CUSTOMER_ID := p_modifier_line_tbl(i).MAX_ORDERS_PER_CUSTOMER_ID;
302 l_modifier_line_tbl(l_prod_index).MAX_AMOUNT_PER_RULE := p_modifier_line_tbl(i).MAX_AMOUNT_PER_RULE;
303 l_modifier_line_tbl(l_prod_index).MAX_AMOUNT_PER_RULE_ID := p_modifier_line_tbl(i).MAX_AMOUNT_PER_RULE_ID;
304 l_modifier_line_tbl(l_prod_index).ESTIMATE_QTY_UOM := p_modifier_line_tbl(i).ESTIMATE_QTY_UOM;
305 l_modifier_line_tbl(l_prod_index).generate_using_formula_id := p_modifier_line_tbl(i).generate_using_formula_id;
306 l_modifier_line_tbl(l_prod_index).price_by_formula_id := p_modifier_line_tbl(i).price_by_formula_id;
307 l_modifier_line_tbl(l_prod_index).generate_using_formula := p_modifier_line_tbl(i).generate_using_formula;
308 l_modifier_line_tbl(l_prod_index).price_by_formula := p_modifier_line_tbl(i).price_by_formula;
309 l_modifier_line_tbl(l_prod_index).list_line_no := p_modifier_line_tbl(i).list_line_no; --//Fix for bug 13791027
310
311 END IF;
312 END LOOP;
313 END IF;
314
315 IF l_exclusion_tbl.COUNT > 0 THEN
316 ozf_offer_pvt.process_exclusions(
317 p_init_msg_list => p_init_msg_list
318 ,p_api_version => p_api_version
319 ,p_commit => p_commit
320 ,x_return_status => x_return_status
321 ,x_msg_count => x_msg_count
322 ,x_msg_data => x_msg_data
323 ,p_pricing_attr_tbl => l_exclusion_tbl
324 ,x_error_location => x_error_location);
325
326 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
327 RAISE Fnd_Api.g_exc_error;
328 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
329 RAISE Fnd_Api.g_exc_unexpected_error;
330 END IF;
331 END IF;
332
333 -- offer line
334 IF p_offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
335 IF p_act_product_tbl.COUNT > 0 THEN
336 FOR i IN p_act_product_tbl.FIRST..p_act_product_tbl.LAST LOOP
337 l_act_product_rec := NULL;
338
339 l_act_product_rec.arc_act_product_used_by := 'OFFR';
340 l_act_product_rec.primary_product_flag := p_act_product_tbl(i).primary_product_flag;
341 l_act_product_rec.enabled_flag := p_act_product_tbl(i).enabled_flag;
342 l_act_product_rec.inventory_item_id := p_act_product_tbl(i).inventory_item_id;
343 l_act_product_rec.organization_id := p_act_product_tbl(i).organization_id;
344 l_act_product_rec.category_id := p_act_product_tbl(i).category_id;
345 l_act_product_rec.category_set_id := p_act_product_tbl(i).category_set_id;
346 l_act_product_rec.attribute_category := p_act_product_tbl(i).attribute_category;
347 l_act_product_rec.level_type_code := p_act_product_tbl(i).level_type_code;
348 l_act_product_rec.excluded_flag := p_act_product_tbl(i).excluded_flag;
349 l_act_product_rec.line_lumpsum_amount := p_act_product_tbl(i).line_lumpsum_amount;
350 l_act_product_rec.line_lumpsum_qty := p_act_product_tbl(i).line_lumpsum_qty;
351 l_act_product_rec.scan_value := p_act_product_tbl(i).scan_value;
352 l_act_product_rec.uom_code := p_act_product_tbl(i).uom_code;
353 l_act_product_rec.scan_unit_forecast := p_act_product_tbl(i).scan_unit_forecast;
354 l_act_product_rec.channel_id := p_act_product_tbl(i).channel_id;
355 l_act_product_rec.quantity := p_act_product_tbl(i).quantity;
356 l_act_product_rec.adjustment_flag := p_act_product_tbl(i).adjustment_flag;
357
358 IF p_act_product_tbl(i).operation = 'CREATE' THEN
359 IF p_act_product_tbl(i).act_product_used_by_id IS NULL OR p_act_product_tbl(i).act_product_used_by_id = FND_API.g_miss_num THEN
360 l_act_product_rec.act_product_used_by_id := x_qp_list_header_id;
361 ELSE
362 l_act_product_rec.act_product_used_by_id := p_act_product_tbl(i).act_product_used_by_id;
363 END IF;
364
365 AMS_ActProduct_PVT.Create_Act_Product(
366 p_api_version => p_api_version,
367 p_init_msg_list => p_init_msg_list,
368 p_commit => p_commit,
369 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
370 x_return_status => x_return_status,
371 x_msg_count => x_msg_count,
372 x_msg_data => x_msg_data,
373 p_act_Product_rec => l_act_product_rec,
374 x_act_Product_id => l_act_product_id);
375
376 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
377 RAISE Fnd_Api.g_exc_error;
378 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
379 RAISE Fnd_Api.g_exc_unexpected_error;
380 END IF;
381 ELSIF p_act_product_tbl(i).operation = 'UPDATE' THEN
382 l_act_product_rec.activity_product_id := p_act_product_tbl(i).activity_product_id;
383 l_act_product_rec.object_version_number := p_act_product_tbl(i).object_version_number;
384 l_act_product_rec.act_product_used_by_id := p_act_product_tbl(i).act_product_used_by_id;
385
386 AMS_ActProduct_PVT.Update_Act_Product(
387 p_api_version => p_api_version,
388 p_init_msg_list => p_init_msg_list,
389 p_commit => p_commit,
390 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
391 x_return_status => x_return_status,
392 x_msg_count => x_msg_count,
393 x_msg_data => x_msg_data,
394 p_act_Product_rec => l_act_product_rec);
395
396 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
397 RAISE Fnd_Api.g_exc_error;
398 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
399 RAISE Fnd_Api.g_exc_unexpected_error;
400 END IF;
401 ELSIF p_act_product_tbl(i).operation = 'DELETE' THEN
402 AMS_ActProduct_PVT.Delete_Act_Product(
403 p_api_version => p_api_version,
404 p_init_msg_list => p_init_msg_list,
405 p_commit => p_commit,
406 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
407 x_return_status => x_return_status,
408 x_msg_count => x_msg_count,
409 x_msg_data => x_msg_data,
410 p_act_Product_id => p_act_product_tbl(i).activity_product_id,
411 p_object_version => p_act_product_tbl(i).object_version_number);
412
413 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
414 RAISE Fnd_Api.g_exc_error;
415 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
416 RAISE Fnd_Api.g_exc_unexpected_error;
417 END IF;
418 END IF;
419 END LOOP;
420 END IF;
421 ELSIF p_offer_type = 'NET_ACCRUAL' THEN
422 IF l_modifier_list_rec.tier_level = 'LINE' THEN
423 IF p_discount_tbl.COUNT > 0 THEN
424 FOR i IN p_discount_tbl.FIRST..p_discount_tbl.LAST LOOP
425 l_discount_line_rec.parent_discount_line_id := p_discount_tbl(i).parent_discount_line_id;
426 l_discount_line_rec.volume_from := p_discount_tbl(i).volume_from;
427 l_discount_line_rec.volume_to := p_discount_tbl(i).volume_to;
428 l_discount_line_rec.volume_operator := p_discount_tbl(i).volume_operator;
429 l_discount_line_rec.volume_type := p_discount_tbl(i).volume_type;
430 l_discount_line_rec.volume_break_type := p_discount_tbl(i).volume_break_type;
431 l_discount_line_rec.discount := p_discount_tbl(i).discount;
432 l_discount_line_rec.discount_type := p_discount_tbl(i).discount_type;
433 l_discount_line_rec.tier_type := p_discount_tbl(i).tier_type;
434 l_discount_line_rec.tier_level := p_discount_tbl(i).tier_level;
435 l_discount_line_rec.incompatibility_group := p_discount_tbl(i).incompatibility_group;
436 l_discount_line_rec.precedence := p_discount_tbl(i).precedence;
437 l_discount_line_rec.bucket := p_discount_tbl(i).bucket;
438 l_discount_line_rec.scan_value := p_discount_tbl(i).scan_value;
439 l_discount_line_rec.scan_data_quantity := p_discount_tbl(i).scan_data_quantity;
440 l_discount_line_rec.scan_unit_forecast := p_discount_tbl(i).scan_unit_forecast;
441 l_discount_line_rec.channel_id := p_discount_tbl(i).channel_id;
442 l_discount_line_rec.adjustment_flag := p_discount_tbl(i).adjustment_flag;
443 l_discount_line_rec.start_date_active := p_discount_tbl(i).start_date_active;
444 l_discount_line_rec.end_date_active := p_discount_tbl(i).end_date_active;
445 l_discount_line_rec.uom_code := p_discount_tbl(i).uom_code;
446 l_discount_line_rec.off_discount_product_id := p_discount_tbl(i).off_discount_product_id;
447 l_discount_line_rec.parent_off_disc_prod_id := p_discount_tbl(i).parent_off_disc_prod_id;
448 l_discount_line_rec.product_level := p_discount_tbl(i).product_level;
449 l_discount_line_rec.product_id := p_discount_tbl(i).product_id;
450 l_discount_line_rec.excluder_flag := p_discount_tbl(i).excluder_flag;
451
452 IF p_discount_tbl(i).operation = 'CREATE' THEN
453 IF p_discount_tbl(i).offer_id IS NULL OR p_discount_tbl(i).offer_id = FND_API.g_miss_num THEN
454 l_discount_line_rec.offer_id := l_offer_id;
455 ELSE
456 l_discount_line_rec.offer_id := p_discount_tbl(i).offer_id;
457 END IF;
458
459 OZF_Disc_Line_PVT.Create_discount_line(
460 p_api_version_number => p_api_version,
461 p_init_msg_list => p_init_msg_list,
462 p_commit => p_commit,
463 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
464 x_return_status => x_return_status,
465 x_msg_count => x_msg_count,
466 x_msg_data => x_msg_data,
467 p_ozf_discount_line_rec => l_discount_line_rec,
468 x_offer_discount_line_id => l_discount_line_id);
469
470 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
471 RAISE Fnd_Api.g_exc_error;
472 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
473 RAISE Fnd_Api.g_exc_unexpected_error;
474 END IF;
475 ELSIF p_discount_tbl(i).operation = 'UPDATE' THEN
476 l_discount_line_rec.offer_discount_line_id := p_discount_tbl(i).offer_discount_line_id;
477 l_discount_line_rec.offer_id := p_discount_tbl(i).offer_id;
478 l_discount_line_rec.object_version_number := p_discount_tbl(i).object_version_number;
479
480 OZF_Disc_Line_PVT.Update_discount_line(
481 p_api_version_number => p_api_version,
482 p_init_msg_list => p_init_msg_list,
483 p_commit => p_commit,
484 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
485 x_return_status => x_return_status,
486 x_msg_count => x_msg_count,
487 x_msg_data => x_msg_data,
488 p_ozf_discount_line_rec => l_discount_line_rec);
489
490 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
491 RAISE Fnd_Api.g_exc_error;
492 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
493 RAISE Fnd_Api.g_exc_unexpected_error;
494 END IF;
495 ELSIF p_discount_tbl(i).operation = 'DELETE' THEN
496 OZF_Disc_Line_PVT.Delete_offer_line(
497 p_api_version_number => p_api_version,
498 p_init_msg_list => p_init_msg_list,
499 p_commit => p_commit,
500 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
501 x_return_status => x_return_status,
502 x_msg_count => x_msg_count,
503 x_msg_data => x_msg_data,
504 p_offer_discount_line_id => p_discount_tbl(i).offer_discount_line_id,
505 p_object_version_number => p_discount_tbl(i).object_version_number);
506
507 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
508 RAISE Fnd_Api.g_exc_error;
509 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
510 RAISE Fnd_Api.g_exc_unexpected_error;
511 END IF;
512 END IF;
513 END LOOP;
514 END IF;
515
516 IF p_excl_tbl.COUNT > 0 THEN
517 FOR i IN p_excl_tbl.FIRST..p_excl_tbl.LAST LOOP
518 l_excl_rec.product_level := p_excl_tbl(i).product_level;
519 l_excl_rec.product_id := p_excl_tbl(i).product_id;
520 l_excl_rec.start_date_active := p_excl_tbl(i).start_date_active;
521 l_excl_rec.end_date_active := p_excl_tbl(i).end_date_active;
522 l_excl_rec.parent_off_disc_prod_id := p_excl_tbl(i).parent_off_disc_prod_id;
523
524 IF p_excl_tbl(i).operation = 'CREATE' THEN
525 OZF_Disc_Line_PVT.Create_Product_Exclusion(
526 p_api_version_number => p_api_version,
527 p_init_msg_list => p_init_msg_list,
528 p_commit => p_commit,
529 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
530 x_return_status => x_return_status,
531 x_msg_count => x_msg_count,
532 x_msg_data => x_msg_data,
533 p_ozf_excl_rec => l_excl_rec,
534 x_off_discount_product_id => l_discount_product_id);
535
536 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
537 RAISE Fnd_Api.g_exc_error;
538 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
539 RAISE Fnd_Api.g_exc_unexpected_error;
540 END IF;
541 ELSIF p_excl_tbl(i).operation = 'UPDATE' THEN
542 l_excl_rec.off_discount_product_id := p_excl_tbl(i).off_discount_product_id;
543 l_excl_rec.object_version_number := p_excl_tbl(i).object_version_number;
544
545 OZF_Disc_Line_PVT.Update_Product_Exclusion(
546 p_api_version_number => p_api_version,
547 p_init_msg_list => p_init_msg_list,
548 p_commit => p_commit,
549 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
550 x_return_status => x_return_status,
551 x_msg_count => x_msg_count,
552 x_msg_data => x_msg_data,
553 p_ozf_excl_rec => l_excl_rec);
554
555 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
556 RAISE Fnd_Api.g_exc_error;
557 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
558 RAISE Fnd_Api.g_exc_unexpected_error;
559 END IF;
560 ELSIF p_excl_tbl(i).operation = 'DELETE' THEN
561 OZF_Disc_Line_PVT.Delete_Ozf_Prod_Line(
562 p_api_version_number => p_api_version,
563 p_init_msg_list => p_init_msg_list,
564 p_commit => p_commit,
565 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
566 x_return_status => x_return_status,
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data,
569 p_off_discount_product_id => p_excl_tbl(i).off_discount_product_id,
570 p_object_version_number => p_excl_tbl(i).object_version_number);
571
572 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
573 RAISE Fnd_Api.g_exc_error;
574 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
575 RAISE Fnd_Api.g_exc_unexpected_error;
576 END IF;
577 END IF;
578 END LOOP;
579 END IF;
580
581 IF p_offer_tier_tbl.COUNT > 0 THEN
582 FOR i IN p_offer_tier_tbl.FIRST..p_offer_tier_tbl.LAST LOOP
583 l_offer_tier_rec.parent_discount_line_id := p_offer_tier_tbl(i).parent_discount_line_id;
584 l_offer_tier_rec.offer_id := p_offer_tier_tbl(i).offer_id;
585 l_offer_tier_rec.volume_from := p_offer_tier_tbl(i).volume_from;
586 l_offer_tier_rec.volume_to := p_offer_tier_tbl(i).volume_to;
587 l_offer_tier_rec.volume_operator := p_offer_tier_tbl(i).volume_operator;
588 l_offer_tier_rec.volume_type := p_offer_tier_tbl(i).volume_type;
589 l_offer_tier_rec.volume_break_type := p_offer_tier_tbl(i).volume_break_type;
590 l_offer_tier_rec.discount := p_offer_tier_tbl(i).discount;
591 l_offer_tier_rec.discount_type := p_offer_tier_tbl(i).discount_type;
592 l_offer_tier_rec.start_date_active := p_offer_tier_tbl(i).start_date_active;
593 l_offer_tier_rec.end_date_active := p_offer_tier_tbl(i).end_date_active;
594 l_offer_tier_rec.uom_code := p_offer_tier_tbl(i).uom_code;
595
596 IF p_offer_tier_tbl(i).operation = 'CREATE' THEN
597 OZF_Disc_Line_PVT.Create_Disc_Tiers(
598 p_api_version_number => p_api_version,
599 p_init_msg_list => p_init_msg_list,
600 p_commit => p_commit,
601 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
602 x_return_status => x_return_status,
603 x_msg_count => x_msg_count,
604 x_msg_data => x_msg_data,
605 p_tier_rec => l_offer_tier_rec,
606 x_offer_discount_line_id => l_discount_line_id);
607
608 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
609 RAISE Fnd_Api.g_exc_error;
610 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
611 RAISE Fnd_Api.g_exc_unexpected_error;
612 END IF;
613 ELSIF p_offer_tier_tbl(i).operation = 'UPDATE' THEN
614 l_offer_tier_rec.offer_discount_line_id := p_offer_tier_tbl(i).offer_discount_line_id;
615 l_offer_tier_rec.object_version_number := p_offer_tier_tbl(i).object_version_number;
616
617 OZF_Disc_Line_PVT.Update_Disc_Tiers(
618 p_api_version_number => p_api_version,
619 p_init_msg_list => p_init_msg_list,
620 p_commit => p_commit,
621 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
622 x_return_status => x_return_status,
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data,
625 p_tier_rec => l_offer_tier_rec);
626
627 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
628 RAISE Fnd_Api.g_exc_error;
629 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
630 RAISE Fnd_Api.g_exc_unexpected_error;
631 END IF;
632 ELSIF p_offer_tier_tbl(i).operation = 'DELETE' THEN
633 IF p_offer_tier_tbl(i).parent_discount_line_id IS NULL OR p_offer_tier_tbl(i).parent_discount_line_id = FND_API.g_miss_num THEN
634 OZF_Disc_Line_PVT.Delete_Disc_tiers(
635 p_api_version_number => p_api_version,
636 p_init_msg_list => p_init_msg_list,
637 p_commit => p_commit,
638 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
639 x_return_status => x_return_status,
640 x_msg_count => x_msg_count,
641 x_msg_data => x_msg_data,
642 p_parent_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id);
643
644 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
645 RAISE Fnd_Api.g_exc_error;
646 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
647 RAISE Fnd_Api.g_exc_unexpected_error;
648 END IF;
649 ELSE
650 OZF_Disc_Line_PVT.Delete_Tier_line(
651 p_api_version_number => p_api_version,
652 p_init_msg_list => p_init_msg_list,
653 p_commit => p_commit,
654 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
655 x_return_status => x_return_status,
656 x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data,
658 p_offer_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id,
659 p_object_version_number => p_offer_tier_tbl(i).object_version_number);
660
661 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
662 RAISE Fnd_Api.g_exc_error;
663 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
664 RAISE Fnd_Api.g_exc_unexpected_error;
665 END IF;
666 END IF;
667 END IF;
668 END LOOP;
669 END IF;
670 ELSIF l_modifier_list_rec.tier_level = 'HEADER' THEN
671 IF p_prod_tbl.COUNT > 0 THEN
672 FOR i IN p_prod_tbl.FIRST..p_prod_tbl.LAST LOOP
673 l_prod_rec.parent_off_disc_prod_id := p_prod_tbl(i).parent_off_disc_prod_id;
674 l_prod_rec.product_level := p_prod_tbl(i).product_level;
675 l_prod_rec.product_id := p_prod_tbl(i).product_id;
676 l_prod_rec.excluder_flag := p_prod_tbl(i).excluder_flag;
677 l_prod_rec.uom_code := p_prod_tbl(i).uom_code;
678 l_prod_rec.start_date_active := p_prod_tbl(i).start_date_active;
679 l_prod_rec.end_date_active := p_prod_tbl(i).end_date_active;
680 l_prod_rec.offer_discount_line_id := p_prod_tbl(i).offer_discount_line_id;
681
682 IF p_prod_tbl(i).operation = 'CREATE' THEN
683 IF p_prod_tbl(i).offer_id IS NULL OR p_prod_tbl(i).offer_id = FND_API.g_miss_num THEN
684 l_prod_rec.offer_id := l_offer_id;
685 ELSE
686 l_prod_rec.offer_id := p_prod_tbl(i).offer_id;
687 END IF;
688
689 OZF_Disc_Line_PVT.Create_Ozf_Prod_Line(
690 p_api_version_number => p_api_version,
691 p_init_msg_list => p_init_msg_list,
692 p_commit => p_commit,
693 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
694 x_return_status => x_return_status,
695 x_msg_count => x_msg_count,
696 x_msg_data => x_msg_data,
697 p_ozf_prod_rec => l_prod_rec,
698 x_off_discount_product_id => l_discount_product_id);
699
700 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
701 RAISE Fnd_Api.g_exc_error;
702 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
703 RAISE Fnd_Api.g_exc_unexpected_error;
704 END IF;
705 ELSIF p_prod_tbl(i).operation = 'UPDATE' THEN
706 l_prod_rec.off_discount_product_id := p_prod_tbl(i).off_discount_product_id;
707 l_prod_rec.offer_id := p_prod_tbl(i).offer_id;
708 l_prod_rec.object_version_number := p_prod_tbl(i).object_version_number;
709
710 OZF_Disc_Line_PVT.Update_Ozf_Prod_Line(
711 p_api_version_number => p_api_version,
712 p_init_msg_list => p_init_msg_list,
713 p_commit => p_commit,
714 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
715 x_return_status => x_return_status,
716 x_msg_count => x_msg_count,
717 x_msg_data => x_msg_data,
718 p_ozf_prod_rec => l_prod_rec);
719
720 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
721 RAISE Fnd_Api.g_exc_error;
722 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
723 RAISE Fnd_Api.g_exc_unexpected_error;
724 END IF;
725 ELSIF p_prod_tbl(i).operation = 'DELETE' THEN
726 OZF_Disc_Line_PVT.Delete_Ozf_Prod_Line(
727 p_api_version_number => p_api_version,
728 p_init_msg_list => p_init_msg_list,
729 p_commit => p_commit,
730 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
731 x_return_status => x_return_status,
732 x_msg_count => x_msg_count,
733 x_msg_data => x_msg_data,
734 p_off_discount_product_id => p_prod_tbl(i).off_discount_product_id,
735 p_object_version_number => p_prod_tbl(i).object_version_number);
736
737 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
738 RAISE Fnd_Api.g_exc_error;
739 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
740 RAISE Fnd_Api.g_exc_unexpected_error;
741 END IF;
742 END IF;
743 END LOOP;
744 END IF;
745 END IF;
746
747 IF p_excl_tbl.COUNT > 0 THEN
748 FOR i IN p_excl_tbl.FIRST..p_excl_tbl.LAST LOOP
749 l_excl_rec.product_level := p_excl_tbl(i).product_level;
750 l_excl_rec.product_id := p_excl_tbl(i).product_id;
751 l_excl_rec.start_date_active := p_excl_tbl(i).start_date_active;
752 l_excl_rec.end_date_active := p_excl_tbl(i).end_date_active;
753 l_excl_rec.parent_off_disc_prod_id := p_excl_tbl(i).parent_off_disc_prod_id;
754
755 IF p_excl_tbl(i).operation = 'CREATE' THEN
756 OZF_Disc_Line_PVT.Create_Product_Exclusion(
757 p_api_version_number => p_api_version,
758 p_init_msg_list => p_init_msg_list,
759 p_commit => p_commit,
760 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
761 x_return_status => x_return_status,
762 x_msg_count => x_msg_count,
763 x_msg_data => x_msg_data,
764 p_ozf_excl_rec => l_excl_rec,
765 x_off_discount_product_id => l_discount_product_id);
766
767 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
768 RAISE Fnd_Api.g_exc_error;
769 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
770 RAISE Fnd_Api.g_exc_unexpected_error;
771 END IF;
772 ELSIF p_excl_tbl(i).operation = 'UPDATE' THEN
773 l_excl_rec.off_discount_product_id := p_excl_tbl(i).off_discount_product_id;
774 l_excl_rec.object_version_number := p_excl_tbl(i).object_version_number;
775
776 OZF_Disc_Line_PVT.Update_Product_Exclusion(
777 p_api_version_number => p_api_version,
778 p_init_msg_list => p_init_msg_list,
779 p_commit => p_commit,
780 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
781 x_return_status => x_return_status,
782 x_msg_count => x_msg_count,
783 x_msg_data => x_msg_data,
784 p_ozf_excl_rec => l_excl_rec);
785
786 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
787 RAISE Fnd_Api.g_exc_error;
788 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
789 RAISE Fnd_Api.g_exc_unexpected_error;
790 END IF;
791 ELSIF p_excl_tbl(i).operation = 'DELETE' THEN
792 OZF_Disc_Line_PVT.Delete_Ozf_Prod_Line(
793 p_api_version_number => p_api_version,
794 p_init_msg_list => p_init_msg_list,
795 p_commit => p_commit,
796 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
797 x_return_status => x_return_status,
798 x_msg_count => x_msg_count,
799 x_msg_data => x_msg_data,
800 p_off_discount_product_id => p_excl_tbl(i).off_discount_product_id,
801 p_object_version_number => p_excl_tbl(i).object_version_number);
802
803 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
804 RAISE Fnd_Api.g_exc_error;
805 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
806 RAISE Fnd_Api.g_exc_unexpected_error;
807 END IF;
808 END IF;
809 END LOOP;
810 END IF;
811
812 IF p_offer_tier_tbl.COUNT > 0 THEN
813 FOR i IN p_offer_tier_tbl.FIRST..p_offer_tier_tbl.LAST LOOP
814 l_offer_tier_rec.parent_discount_line_id := p_offer_tier_tbl(i).parent_discount_line_id;
815 l_offer_tier_rec.offer_id := p_offer_tier_tbl(i).offer_id;
816 l_offer_tier_rec.volume_from := p_offer_tier_tbl(i).volume_from;
817 l_offer_tier_rec.volume_to := p_offer_tier_tbl(i).volume_to;
818 l_offer_tier_rec.volume_operator := p_offer_tier_tbl(i).volume_operator;
819 l_offer_tier_rec.volume_type := p_offer_tier_tbl(i).volume_type;
820 l_offer_tier_rec.volume_break_type := p_offer_tier_tbl(i).volume_break_type;
821 l_offer_tier_rec.discount := p_offer_tier_tbl(i).discount;
822 l_offer_tier_rec.discount_type := p_offer_tier_tbl(i).discount_type;
823 l_offer_tier_rec.start_date_active := p_offer_tier_tbl(i).start_date_active;
824 l_offer_tier_rec.end_date_active := p_offer_tier_tbl(i).end_date_active;
825 l_offer_tier_rec.uom_code := p_offer_tier_tbl(i).uom_code;
826
827 IF p_offer_tier_tbl(i).operation = 'CREATE' THEN
828 OZF_Disc_Line_PVT.Create_Disc_Tiers(
829 p_api_version_number => p_api_version,
830 p_init_msg_list => p_init_msg_list,
831 p_commit => p_commit,
832 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
833 x_return_status => x_return_status,
834 x_msg_count => x_msg_count,
835 x_msg_data => x_msg_data,
836 p_tier_rec => l_offer_tier_rec,
837 x_offer_discount_line_id => l_discount_line_id);
838
839 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
840 RAISE Fnd_Api.g_exc_error;
841 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
842 RAISE Fnd_Api.g_exc_unexpected_error;
843 END IF;
844 ELSIF p_offer_tier_tbl(i).operation = 'UPDATE' THEN
845 l_offer_tier_rec.offer_discount_line_id := p_offer_tier_tbl(i).offer_discount_line_id;
846 l_offer_tier_rec.object_version_number := p_offer_tier_tbl(i).object_version_number;
847
848 OZF_Disc_Line_PVT.Update_Disc_Tiers(
849 p_api_version_number => p_api_version,
850 p_init_msg_list => p_init_msg_list,
851 p_commit => p_commit,
852 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
853 x_return_status => x_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data,
856 p_tier_rec => l_offer_tier_rec);
857
858 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
859 RAISE Fnd_Api.g_exc_error;
860 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
861 RAISE Fnd_Api.g_exc_unexpected_error;
862 END IF;
863 ELSIF p_offer_tier_tbl(i).operation = 'DELETE' THEN
864 IF p_offer_tier_tbl(i).parent_discount_line_id IS NULL OR p_offer_tier_tbl(i).parent_discount_line_id = FND_API.g_miss_num THEN
865 OZF_Disc_Line_PVT.Delete_Disc_tiers(
866 p_api_version_number => p_api_version,
867 p_init_msg_list => p_init_msg_list,
868 p_commit => p_commit,
869 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
870 x_return_status => x_return_status,
871 x_msg_count => x_msg_count,
872 x_msg_data => x_msg_data,
873 p_parent_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id);
874
875 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
876 RAISE Fnd_Api.g_exc_error;
877 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
878 RAISE Fnd_Api.g_exc_unexpected_error;
879 END IF;
880 ELSE
881 OZF_Disc_Line_PVT.Delete_Tier_line(
882 p_api_version_number => p_api_version,
883 p_init_msg_list => p_init_msg_list,
884 p_commit => p_commit,
885 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
886 x_return_status => x_return_status,
887 x_msg_count => x_msg_count,
888 x_msg_data => x_msg_data,
889 p_offer_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id,
890 p_object_version_number => p_offer_tier_tbl(i).object_version_number);
891
892 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
893 RAISE Fnd_Api.g_exc_error;
894 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
895 RAISE Fnd_Api.g_exc_unexpected_error;
896 END IF;
897 END IF;
898 END IF;
899 END LOOP;
900 END IF;
901 END IF; -- end offer_type
902
903 Ozf_Offer_Pvt.process_modifiers(
904 p_init_msg_list => p_init_msg_list
905 ,p_api_version => p_api_version
906 ,p_commit => p_commit
907 ,x_return_status => x_return_status
908 ,x_msg_count => x_msg_count
909 ,x_msg_data => x_msg_data
910 ,p_offer_type => p_offer_type
911 ,p_modifier_list_rec => l_modifier_list_rec
912 ,p_modifier_line_tbl => l_modifier_line_tbl
913 ,x_qp_list_header_id => l_dummy
914 ,x_error_location => x_error_location);
915
916 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
917 RAISE Fnd_Api.g_exc_error;
918 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
919 RAISE Fnd_Api.g_exc_unexpected_error;
920 END IF;
921
922 -- process qualifier
923 IF p_offer_type = 'NET_ACCRUAL' THEN
924 IF p_na_qualifier_tbl.COUNT > 0 THEN
925 FOR i IN p_na_qualifier_tbl.FIRST..p_na_qualifier_tbl.LAST LOOP
926 l_na_qualifier_rec.qualifier_id := p_na_qualifier_tbl(i).qualifier_id;
927 l_na_qualifier_rec.qualifier_grouping_no := p_na_qualifier_tbl(i).qualifier_grouping_no;
928 l_na_qualifier_rec.qualifier_context := p_na_qualifier_tbl(i).qualifier_context;
929 l_na_qualifier_rec.qualifier_attribute := p_na_qualifier_tbl(i).qualifier_attribute;
930 l_na_qualifier_rec.qualifier_attr_value := p_na_qualifier_tbl(i).qualifier_attr_value;
931 l_na_qualifier_rec.start_date_active := p_na_qualifier_tbl(i).start_date_active;
932 l_na_qualifier_rec.end_date_active := p_na_qualifier_tbl(i).end_date_active;
933 l_na_qualifier_rec.offer_id := l_offer_id;
934 l_na_qualifier_rec.offer_discount_line_id := p_na_qualifier_tbl(i).offer_discount_line_id;
935 l_na_qualifier_rec.context := p_na_qualifier_tbl(i).context;
936 l_na_qualifier_rec.attribute1 := p_na_qualifier_tbl(i).attribute1;
937 l_na_qualifier_rec.attribute2 := p_na_qualifier_tbl(i).attribute2;
938 l_na_qualifier_rec.attribute3 := p_na_qualifier_tbl(i).attribute3;
939 l_na_qualifier_rec.attribute4 := p_na_qualifier_tbl(i).attribute4;
940 l_na_qualifier_rec.attribute5 := p_na_qualifier_tbl(i).attribute5;
941 l_na_qualifier_rec.attribute6 := p_na_qualifier_tbl(i).attribute6;
942 l_na_qualifier_rec.attribute7 := p_na_qualifier_tbl(i).attribute7;
943 l_na_qualifier_rec.attribute8 := p_na_qualifier_tbl(i).attribute8;
944 l_na_qualifier_rec.attribute9 := p_na_qualifier_tbl(i).attribute9;
945 l_na_qualifier_rec.attribute10 := p_na_qualifier_tbl(i).attribute10;
946 l_na_qualifier_rec.attribute11 := p_na_qualifier_tbl(i).attribute11;
947 l_na_qualifier_rec.attribute12 := p_na_qualifier_tbl(i).attribute12;
948 l_na_qualifier_rec.attribute13 := p_na_qualifier_tbl(i).attribute13;
949 l_na_qualifier_rec.attribute14 := p_na_qualifier_tbl(i).attribute14;
950 l_na_qualifier_rec.attribute15 := p_na_qualifier_tbl(i).attribute15;
951 IF p_na_qualifier_tbl(i).active_flag IS NULL THEN
952 l_na_qualifier_rec.active_flag := 'Y'; -- set qualifier default to Active
953 ELSE
954 l_na_qualifier_rec.active_flag := p_na_qualifier_tbl(i).active_flag;
955 END IF;
956 l_na_qualifier_rec.object_version_number := p_na_qualifier_tbl(i).object_version_number;
957
958 IF p_na_qualifier_tbl(i).operation = 'CREATE' THEN
959 OZF_Offr_Qual_PVT.Create_Offr_Qual(
960 p_api_version_number => p_api_version,
961 p_init_msg_list => p_init_msg_list,
962 p_commit => p_commit,
963 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
964 x_return_status => x_return_status,
965 x_msg_count => x_msg_count,
966 x_msg_data => x_msg_data,
967 p_ozf_offr_qual_rec => l_na_qualifier_rec,
968 x_qualifier_id => l_na_qualifier_id);
969
970 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
971 RAISE Fnd_Api.g_exc_error;
972 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
973 RAISE Fnd_Api.g_exc_unexpected_error;
974 END IF;
975 ELSIF p_na_qualifier_tbl(i).operation = 'UPDATE' THEN
976 OZF_Offr_Qual_PVT.Update_Offr_Qual(
977 p_api_version_number => p_api_version,
978 p_init_msg_list => p_init_msg_list,
979 p_commit => p_commit,
980 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
981 x_return_status => x_return_status,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_ozf_offr_qual_rec => l_na_qualifier_rec);
985
986 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
987 RAISE Fnd_Api.g_exc_error;
988 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
989 RAISE Fnd_Api.g_exc_unexpected_error;
990 END IF;
991 ELSIF p_na_qualifier_tbl(i).operation = 'DELETE' THEN
992 OZF_Offr_Qual_PVT.Delete_Offr_Qual(
993 p_api_version_number => p_api_version,
994 p_init_msg_list => p_init_msg_list,
995 p_commit => p_commit,
996 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
997 x_return_status => x_return_status,
998 x_msg_count => x_msg_count,
999 x_msg_data => x_msg_data,
1000 p_qualifier_id => l_na_qualifier_rec.qualifier_id,
1001 p_object_version_number => l_na_qualifier_rec.object_version_number);
1002
1003 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1004 RAISE Fnd_Api.g_exc_error;
1005 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1006 RAISE Fnd_Api.g_exc_unexpected_error;
1007 END IF;
1008 END IF;
1009 END LOOP;
1010 END IF;
1011 ELSE
1012 IF p_qualifier_tbl.COUNT > 0 THEN
1013 FOR i IN p_qualifier_tbl.FIRST..p_qualifier_tbl.LAST LOOP
1014 l_qualifiers_tbl(i).list_header_id := x_qp_list_header_id;
1015 l_qualifiers_tbl(i).qualifier_context := p_qualifier_tbl(i).qualifier_context;
1016 l_qualifiers_tbl(i).qualifier_attribute := p_qualifier_tbl(i).qualifier_attribute;
1017 l_qualifiers_tbl(i).qualifier_attr_value := p_qualifier_tbl(i).qualifier_attr_value;
1018 l_qualifiers_tbl(i).qualifier_attr_value_to := p_qualifier_tbl(i).qualifier_attr_value_to;
1019 l_qualifiers_tbl(i).comparison_operator_code := p_qualifier_tbl(i).comparison_operator_code;
1020 l_qualifiers_tbl(i).qualifier_grouping_no := p_qualifier_tbl(i).qualifier_grouping_no;
1021 l_qualifiers_tbl(i).list_line_id := p_qualifier_tbl(i).list_line_id;
1022 l_qualifiers_tbl(i).qualifier_id := p_qualifier_tbl(i).qualifier_id;
1023 l_qualifiers_tbl(i).start_date_active := p_qualifier_tbl(i).start_date_active;
1024 l_qualifiers_tbl(i).end_date_active := p_qualifier_tbl(i).end_date_active;
1025 l_qualifiers_tbl(i).activity_market_segment_id := p_qualifier_tbl(i).activity_market_segment_id;
1026 l_qualifiers_tbl(i).operation := p_qualifier_tbl(i).operation;
1027 l_qualifiers_tbl(i).context := p_qualifier_tbl(i).context;
1028 l_qualifiers_tbl(i).attribute1 := p_qualifier_tbl(i).attribute1;
1029 l_qualifiers_tbl(i).attribute2 := p_qualifier_tbl(i).attribute2;
1030 l_qualifiers_tbl(i).attribute3 := p_qualifier_tbl(i).attribute3;
1031 l_qualifiers_tbl(i).attribute4 := p_qualifier_tbl(i).attribute4;
1032 l_qualifiers_tbl(i).attribute5 := p_qualifier_tbl(i).attribute5;
1033 l_qualifiers_tbl(i).attribute6 := p_qualifier_tbl(i).attribute6;
1034 l_qualifiers_tbl(i).attribute7 := p_qualifier_tbl(i).attribute7;
1035 l_qualifiers_tbl(i).attribute8 := p_qualifier_tbl(i).attribute8;
1036 l_qualifiers_tbl(i).attribute9 := p_qualifier_tbl(i).attribute9;
1037 l_qualifiers_tbl(i).attribute10 := p_qualifier_tbl(i).attribute10;
1038 l_qualifiers_tbl(i).attribute11 := p_qualifier_tbl(i).attribute11;
1039 l_qualifiers_tbl(i).attribute12 := p_qualifier_tbl(i).attribute12;
1040 l_qualifiers_tbl(i).attribute13 := p_qualifier_tbl(i).attribute13;
1041 l_qualifiers_tbl(i).attribute14 := p_qualifier_tbl(i).attribute14;
1042 l_qualifiers_tbl(i).attribute15 := p_qualifier_tbl(i).attribute15;
1043 END LOOP;
1044
1045 Ozf_Offer_Pvt.process_market_qualifiers(
1046 p_init_msg_list => p_init_msg_list
1047 ,p_api_version => p_api_version
1048 ,p_commit => p_commit
1049 ,x_return_status => x_return_status
1050 ,x_msg_count => x_msg_count
1051 ,x_msg_data => x_msg_data
1052 ,p_qualifiers_tbl => l_qualifiers_tbl
1053 ,x_error_location => x_error_location
1054 ,x_qualifiers_tbl => l_qualifiers_tbl_out);
1055 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1056 RAISE Fnd_Api.g_exc_error;
1057 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1058 RAISE Fnd_Api.g_exc_unexpected_error;
1059 END IF;
1060 END IF;
1061 END IF;
1062
1063 -- budget
1064 IF p_budget_tbl.COUNT > 0 THEN
1065 FOR i IN p_budget_tbl.FIRST..p_budget_tbl.LAST LOOP
1066 l_act_budgets_rec.act_budget_used_by_id := x_qp_list_header_id;
1067 l_act_budgets_rec.budget_source_id := p_budget_tbl(i).budget_id;
1068 l_act_budgets_rec.request_amount := p_budget_tbl(i).budget_amount;
1069 l_act_budgets_rec.budget_source_type := 'FUND';
1070 l_act_budgets_rec.transfer_type := 'REQUEST';
1071 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
1072 l_act_budgets_rec.request_currency := p_modifier_list_rec.transaction_currency_code;
1073 l_act_budgets_rec.approved_in_currency := p_modifier_list_rec.transaction_currency_code;
1074 l_act_budgets_rec.activity_budget_id := p_budget_tbl(i).act_budget_id;
1075
1076 OPEN c_act_budget_obj_ver(l_act_budgets_rec.activity_budget_id);
1077 FETCH c_act_budget_obj_ver INTO l_act_budgets_rec.object_version_number;
1078 CLOSE c_act_budget_obj_ver;
1079
1080 IF p_budget_tbl(i).operation = 'CREATE' THEN
1081 Ozf_Actbudgets_Pvt.create_act_budgets(
1082 p_api_version => p_api_version
1083 ,p_init_msg_list => p_init_msg_list
1084 ,p_commit => p_commit
1085 ,p_validation_level => Fnd_Api.g_valid_level_full
1086 ,x_return_status => x_return_status
1087 ,x_msg_count => x_msg_count
1088 ,x_msg_data => x_msg_data
1089 ,p_act_budgets_rec => l_act_budgets_rec
1090 ,x_act_budget_id => l_activity_budget_id);
1091
1092 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1093 RAISE Fnd_Api.g_exc_error;
1094 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1095 RAISE Fnd_Api.g_exc_unexpected_error;
1096 END IF;
1097 ELSIF p_budget_tbl(i).operation = 'UPDATE' THEN
1098 Ozf_Actbudgets_Pvt.update_act_budgets(
1099 p_api_version => p_api_version
1100 ,p_init_msg_list => p_init_msg_list
1101 ,p_commit => p_commit
1102 ,p_validation_level => Fnd_Api.g_valid_level_full
1103 ,x_return_status => x_return_status
1104 ,x_msg_count => x_msg_count
1105 ,x_msg_data => x_msg_data
1106 ,p_act_budgets_rec => l_act_budgets_rec);
1107
1108 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1109 RAISE Fnd_Api.g_exc_error;
1110 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1111 RAISE Fnd_Api.g_exc_unexpected_error;
1112 END IF;
1113 ELSIF p_budget_tbl(i).operation = 'DELETE' THEN
1114 Ozf_Actbudgets_Pvt.delete_act_budgets(
1115 p_api_version => p_api_version
1116 ,p_init_msg_list => p_init_msg_list
1117 ,p_commit => p_commit
1118 ,p_validation_level => Fnd_Api.g_valid_level_full
1119 ,x_return_status => x_return_status
1120 ,x_msg_count => x_msg_count
1121 ,x_msg_data => x_msg_data
1122 ,p_act_budget_id => l_act_budgets_rec.activity_budget_id
1123 ,p_object_version => l_act_budgets_rec.object_version_number);
1124
1125 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1126 RAISE Fnd_Api.g_exc_error;
1127 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1128 RAISE Fnd_Api.g_exc_unexpected_error;
1129 END IF;
1130 END IF;
1131 END LOOP;
1132 END IF;
1133 /*
1134 Ozf_Offer_Pvt.process_modifiers(
1135 p_init_msg_list => p_init_msg_list
1136 ,p_api_version => p_api_version
1137 ,p_commit => p_commit
1138 ,x_return_status => x_return_status
1139 ,x_msg_count => x_msg_count
1140 ,x_msg_data => x_msg_data
1141 ,p_offer_type => p_offer_type
1142 ,p_modifier_list_rec => l_modifier_list_rec
1143 ,p_modifier_line_tbl => l_modifier_line_tbl
1144 ,x_qp_list_header_id => l_dummy
1145 ,x_error_location => x_error_location);
1146
1147 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1148 RAISE Fnd_Api.g_exc_error;
1149 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1150 RAISE Fnd_Api.g_exc_unexpected_error;
1151 END IF;
1152 */
1153 EXCEPTION
1154 WHEN Fnd_Api.G_EXC_ERROR THEN
1155 x_return_status := Fnd_Api.g_ret_sts_error ;
1156 ROLLBACK TO process_modifiers_pub;
1157 Fnd_Msg_Pub.Count_AND_Get
1158 ( p_count => x_msg_count,
1159 p_data => x_msg_data,
1160 p_encoded => Fnd_Api.G_FALSE
1161 );
1162 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1163 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1164 ROLLBACK TO process_modifiers_pub;
1165 Fnd_Msg_Pub.Count_AND_Get
1166 ( p_count => x_msg_count,
1167 p_data => x_msg_data,
1168 p_encoded => Fnd_Api.G_FALSE
1169 );
1170 WHEN OTHERS THEN
1171 x_return_status := Fnd_Api.g_ret_sts_unexp_erroR ;
1172 ROLLBACK TO process_modifiers_pub;
1173 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1174 THEN
1175 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1176 END IF;
1177 Fnd_Msg_Pub.Count_AND_Get
1178 ( p_count => x_msg_count,
1179 p_data => x_msg_data,
1180 p_encoded => Fnd_Api.G_FALSE
1181 );
1182
1183 END process_modifiers;
1184
1185
1186 PROCEDURE process_vo(
1187 p_init_msg_list IN VARCHAR2
1188 ,p_api_version IN NUMBER
1189 ,p_commit IN VARCHAR2
1190 ,x_return_status OUT NOCOPY VARCHAR2
1191 ,x_msg_count OUT NOCOPY NUMBER
1192 ,x_msg_data OUT NOCOPY VARCHAR2
1193 ,p_modifier_list_rec IN modifier_list_rec_type
1194 ,p_vo_pbh_tbl IN vo_disc_tbl_type
1195 ,p_vo_dis_tbl IN vo_disc_tbl_type
1196 ,p_vo_prod_tbl IN vo_prod_tbl_type
1197 ,p_qualifier_tbl IN qualifiers_tbl_type
1198 ,p_vo_mo_tbl IN vo_mo_tbl_type
1199 ,p_budget_tbl IN budget_tbl_type
1200 ,x_qp_list_header_id OUT NOCOPY NUMBER
1201 ,x_error_location OUT NOCOPY NUMBER
1202 )
1203 IS
1204 CURSOR c_act_budget_obj_ver(l_act_budg_id NUMBER) IS
1205 SELECT object_version_number
1206 FROM ozf_act_budgets
1207 WHERE activity_budget_id = l_act_budg_id;
1208
1209 CURSOR c_offer_info(l_qp_list_header_id NUMBER) IS
1210 SELECT offer_id, offer_type, custom_setup_id, offer_code, object_version_number
1211 FROM ozf_offers
1212 WHERE qp_list_header_id = l_qp_list_header_id;
1213
1214 l_api_version CONSTANT NUMBER := 1.0;
1215 l_api_name CONSTANT VARCHAR2(30) := 'process_vo';
1216
1217 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
1218 l_modifier_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
1219 l_qualifier_rec ozf_offer_pvt.qualifiers_rec_type;
1220 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1221 l_vo_pbh_rec ozf_volume_offer_disc_pvt.vo_disc_rec_type;
1222 l_vo_dis_rec ozf_volume_offer_disc_pvt.vo_disc_rec_type;
1223 l_vo_prod_rec ozf_volume_offer_disc_pvt.vo_prod_rec_type;
1224 l_vo_mo_rec ozf_offer_market_options_pvt.vo_mo_rec_type;
1225
1226 l_activity_budget_id NUMBER;
1227 l_vo_pbh_line_id NUMBER;
1228 l_vo_dis_line_id NUMBER;
1229 l_vo_prod_id NUMBER;
1230 l_vo_mo_id NUMBER;
1231 l_dummy NUMBER;
1232
1233 BEGIN
1234 SAVEPOINT process_vo;
1235 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
1236 Fnd_Msg_Pub.initialize;
1237 END IF;
1238
1239 IF NOT Fnd_Api.compatible_api_call(l_api_version,
1240 p_api_version,
1241 l_api_name,
1242 g_pkg_name)
1243 THEN
1244 RAISE Fnd_Api.g_exc_unexpected_error;
1245 END IF;
1246
1247 x_return_status := Fnd_Api.g_ret_sts_success;
1248 x_error_location := 0;
1249
1250 l_modifier_list_rec.offer_id := p_modifier_list_rec.offer_id;
1251 l_modifier_list_rec.qp_list_header_id := p_modifier_list_rec.qp_list_header_id;
1252 l_modifier_list_rec.offer_type := p_modifier_list_rec.offer_type;
1253 l_modifier_list_rec.offer_code := p_modifier_list_rec.offer_code;
1254 l_modifier_list_rec.activity_media_id := p_modifier_list_rec.activity_media_id;
1255 l_modifier_list_rec.reusable := p_modifier_list_rec.reusable;
1256 l_modifier_list_rec.user_status_id := p_modifier_list_rec.user_status_id;
1257 l_modifier_list_rec.customer_reference := p_modifier_list_rec.customer_reference;
1258 l_modifier_list_rec.buying_group_contact_id := p_modifier_list_rec.buying_group_contact_id;
1259 l_modifier_list_rec.object_version_number := p_modifier_list_rec.object_version_number;
1260 l_modifier_list_rec.perf_date_from := p_modifier_list_rec.perf_date_from;
1261 l_modifier_list_rec.perf_date_to := p_modifier_list_rec.perf_date_to;
1262 l_modifier_list_rec.status_code := p_modifier_list_rec.status_code;
1263 l_modifier_list_rec.modifier_level_code := p_modifier_list_rec.modifier_level_code;
1264 l_modifier_list_rec.custom_setup_id := p_modifier_list_rec.custom_setup_id;
1265 l_modifier_list_rec.offer_amount := p_modifier_list_rec.offer_amount;
1266 l_modifier_list_rec.budget_amount_tc := p_modifier_list_rec.budget_amount_tc;
1267 l_modifier_list_rec.budget_amount_fc := p_modifier_list_rec.budget_amount_fc;
1268 l_modifier_list_rec.transaction_currency_code := p_modifier_list_rec.transaction_currency_code;
1269 l_modifier_list_rec.functional_currency_code := p_modifier_list_rec.functional_currency_code;
1270 l_modifier_list_rec.context := p_modifier_list_rec.context;
1271 l_modifier_list_rec.attribute1 := p_modifier_list_rec.attribute1;
1272 l_modifier_list_rec.attribute2 := p_modifier_list_rec.attribute2;
1273 l_modifier_list_rec.attribute3 := p_modifier_list_rec.attribute3;
1274 l_modifier_list_rec.attribute4 := p_modifier_list_rec.attribute4;
1275 l_modifier_list_rec.attribute5 := p_modifier_list_rec.attribute5;
1276 l_modifier_list_rec.attribute6 := p_modifier_list_rec.attribute6;
1277 l_modifier_list_rec.attribute7 := p_modifier_list_rec.attribute7;
1278 l_modifier_list_rec.attribute8 := p_modifier_list_rec.attribute8;
1279 l_modifier_list_rec.attribute9 := p_modifier_list_rec.attribute9;
1280 l_modifier_list_rec.attribute10 := p_modifier_list_rec.attribute10;
1281 l_modifier_list_rec.attribute11 := p_modifier_list_rec.attribute11;
1282 l_modifier_list_rec.attribute12 := p_modifier_list_rec.attribute12;
1283 l_modifier_list_rec.attribute13 := p_modifier_list_rec.attribute13;
1284 l_modifier_list_rec.attribute14 := p_modifier_list_rec.attribute14;
1285 l_modifier_list_rec.attribute15 := p_modifier_list_rec.attribute15;
1286 l_modifier_list_rec.currency_code := p_modifier_list_rec.currency_code;
1287 l_modifier_list_rec.start_date_active := p_modifier_list_rec.start_date_active;
1288 l_modifier_list_rec.end_date_active := p_modifier_list_rec.end_date_active;
1289 l_modifier_list_rec.list_type_code := p_modifier_list_rec.list_type_code;
1290 l_modifier_list_rec.discount_lines_flag := p_modifier_list_rec.discount_lines_flag;
1291 l_modifier_list_rec.name := p_modifier_list_rec.name;
1292 l_modifier_list_rec.description := p_modifier_list_rec.description;
1293 l_modifier_list_rec.comments := p_modifier_list_rec.comments;
1294 l_modifier_list_rec.ask_for_flag := p_modifier_list_rec.ask_for_flag;
1295 l_modifier_list_rec.start_date_active_first := p_modifier_list_rec.start_date_active_first;
1296 l_modifier_list_rec.end_date_active_first := p_modifier_list_rec.end_date_active_first;
1297 l_modifier_list_rec.active_date_first_type := p_modifier_list_rec.active_date_first_type;
1298 l_modifier_list_rec.start_date_active_second := p_modifier_list_rec.start_date_active_second;
1299 l_modifier_list_rec.end_date_active_second := p_modifier_list_rec.end_date_active_second;
1300 l_modifier_list_rec.active_date_second_type := p_modifier_list_rec.active_date_second_type;
1301 l_modifier_list_rec.active_flag := p_modifier_list_rec.active_flag;
1302 l_modifier_list_rec.max_no_of_uses := p_modifier_list_rec.max_no_of_uses;
1303 l_modifier_list_rec.budget_source_id := p_modifier_list_rec.budget_source_id;
1304 l_modifier_list_rec.budget_source_type := p_modifier_list_rec.budget_source_type;
1305 l_modifier_list_rec.offer_used_by_id := p_modifier_list_rec.offer_used_by_id;
1306 l_modifier_list_rec.offer_used_by := p_modifier_list_rec.offer_used_by;
1307 l_modifier_list_rec.ql_qualifier_type := p_modifier_list_rec.ql_qualifier_type;
1308 l_modifier_list_rec.ql_qualifier_id := p_modifier_list_rec.ql_qualifier_id;
1309 l_modifier_list_rec.amount_limit_id := p_modifier_list_rec.amount_limit_id;
1310 l_modifier_list_rec.uses_limit_id := p_modifier_list_rec.uses_limit_id;
1311 l_modifier_list_rec.offer_operation := p_modifier_list_rec.offer_operation;
1312 l_modifier_list_rec.modifier_operation := p_modifier_list_rec.modifier_operation;
1313 l_modifier_list_rec.budget_offer_yn := p_modifier_list_rec.budget_offer_yn;
1314 l_modifier_list_rec.break_type := p_modifier_list_rec.break_type;
1315 l_modifier_list_rec.volume_offer_type := p_modifier_list_rec.volume_offer_type;
1316 l_modifier_list_rec.confidential_flag := p_modifier_list_rec.confidential_flag;
1317 l_modifier_list_rec.committed_amount_eq_max := p_modifier_list_rec.committed_amount_eq_max;
1318 l_modifier_list_rec.source_from_parent := p_modifier_list_rec.source_from_parent;
1319 l_modifier_list_rec.buyer_name := p_modifier_list_rec.buyer_name;
1320 l_modifier_list_rec.sales_method_flag := p_modifier_list_rec.sales_method_flag;
1321 l_modifier_list_rec.global_flag := p_modifier_list_rec.global_flag;
1322 l_modifier_list_rec.orig_org_id := p_modifier_list_rec.orig_org_id;
1323
1324 IF p_modifier_list_rec.offer_operation = 'CREATE' THEN
1325 l_modifier_list_rec.offer_operation := 'CREATE';
1326 l_modifier_list_rec.modifier_operation := 'CREATE';
1327 l_modifier_list_rec.status_code := 'DRAFT';
1328 l_modifier_list_rec.user_status_id := ozf_utility_pvt.get_default_user_status('OZF_OFFER_STATUS','DRAFT');--1600;
1329
1330 IF p_modifier_list_rec.OWNER_ID IS NULL OR p_modifier_list_rec.OWNER_ID = fnd_api.g_miss_num THEN
1331 l_modifier_list_rec.OWNER_ID := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
1332 ELSE
1333 l_modifier_list_rec.OWNER_ID := p_modifier_list_rec.OWNER_ID;
1334 END IF;
1335
1336 Ozf_Offer_Pvt.process_modifiers(
1337 p_init_msg_list => p_init_msg_list
1338 ,p_api_version => p_api_version
1339 ,p_commit => p_commit
1340 ,x_return_status => x_return_status
1341 ,x_msg_count => x_msg_count
1342 ,x_msg_data => x_msg_data
1343 ,p_offer_type => 'VOLUME_OFFER'
1344 ,p_modifier_list_rec => l_modifier_list_rec
1345 ,p_modifier_line_tbl => l_modifier_line_tbl -- need to create header first. use empty line.
1346 ,x_qp_list_header_id => x_qp_list_header_id
1347 ,x_error_location => x_error_location);
1348
1349 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1350 RAISE Fnd_Api.g_exc_error;
1351 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1352 RAISE Fnd_Api.g_exc_unexpected_error;
1353 END IF;
1354
1355 l_modifier_list_rec.offer_operation := 'UPDATE';
1356 l_modifier_list_rec.modifier_operation := 'UPDATE';
1357 l_modifier_list_rec.qp_list_header_id := x_qp_list_header_id;
1358 l_modifier_list_rec.user_status_id := p_modifier_list_rec.user_status_id;
1359 l_modifier_list_rec.status_code := p_modifier_list_rec.status_code;
1360 ELSE
1361 x_qp_list_header_id := p_modifier_list_rec.qp_list_header_id;
1362 IF p_modifier_list_rec.OWNER_ID IS NULL THEN
1363 l_modifier_list_rec.OWNER_ID := fnd_api.g_miss_num;
1364 ELSE
1365 l_modifier_list_rec.OWNER_ID := p_modifier_list_rec.OWNER_ID;
1366 END IF;
1367 END IF;
1368
1369 OPEN c_offer_info(x_qp_list_header_id);
1370 FETCH c_offer_info INTO l_modifier_list_rec.offer_id, l_modifier_list_rec.offer_type, l_modifier_list_rec.custom_setup_id, l_modifier_list_rec.offer_code, l_modifier_list_rec.object_version_number;
1371 CLOSE c_offer_info;
1372
1373 IF p_qualifier_tbl.COUNT > 0 THEN
1374 FOR i IN p_qualifier_tbl.FIRST..p_qualifier_tbl.LAST LOOP
1375 l_qualifier_rec.list_header_id := x_qp_list_header_id;
1376 l_qualifier_rec.qualifier_context := p_qualifier_tbl(i).qualifier_context;
1377 l_qualifier_rec.qualifier_attribute := p_qualifier_tbl(i).qualifier_attribute;
1378 l_qualifier_rec.qualifier_attr_value := p_qualifier_tbl(i).qualifier_attr_value;
1379 l_qualifier_rec.qualifier_attr_value_to := p_qualifier_tbl(i).qualifier_attr_value_to;
1380 l_qualifier_rec.comparison_operator_code := p_qualifier_tbl(i).comparison_operator_code;
1381 l_qualifier_rec.qualifier_grouping_no := p_qualifier_tbl(i).qualifier_grouping_no;
1382 l_qualifier_rec.list_line_id := p_qualifier_tbl(i).list_line_id;
1383 l_qualifier_rec.qualifier_id := p_qualifier_tbl(i).qualifier_id;
1384 l_qualifier_rec.start_date_active := p_qualifier_tbl(i).start_date_active;
1385 l_qualifier_rec.end_date_active := p_qualifier_tbl(i).end_date_active;
1386 l_qualifier_rec.activity_market_segment_id := p_qualifier_tbl(i).activity_market_segment_id;
1387 l_qualifier_rec.context := p_qualifier_tbl(i).context;
1388 l_qualifier_rec.attribute1 := p_qualifier_tbl(i).attribute1;
1389 l_qualifier_rec.attribute2 := p_qualifier_tbl(i).attribute2;
1390 l_qualifier_rec.attribute3 := p_qualifier_tbl(i).attribute3;
1391 l_qualifier_rec.attribute4 := p_qualifier_tbl(i).attribute4;
1392 l_qualifier_rec.attribute5 := p_qualifier_tbl(i).attribute5;
1393 l_qualifier_rec.attribute6 := p_qualifier_tbl(i).attribute6;
1394 l_qualifier_rec.attribute7 := p_qualifier_tbl(i).attribute7;
1395 l_qualifier_rec.attribute8 := p_qualifier_tbl(i).attribute8;
1396 l_qualifier_rec.attribute9 := p_qualifier_tbl(i).attribute9;
1397 l_qualifier_rec.attribute10 := p_qualifier_tbl(i).attribute10;
1398 l_qualifier_rec.attribute11 := p_qualifier_tbl(i).attribute11;
1399 l_qualifier_rec.attribute12 := p_qualifier_tbl(i).attribute12;
1400 l_qualifier_rec.attribute13 := p_qualifier_tbl(i).attribute13;
1401 l_qualifier_rec.attribute14 := p_qualifier_tbl(i).attribute14;
1402 l_qualifier_rec.attribute15 := p_qualifier_tbl(i).attribute15;
1403
1404 IF p_qualifier_tbl(i).operation = 'CREATE' THEN
1405 ozf_volume_offer_qual_pvt.create_vo_qualifier(
1406 p_api_version_number => p_api_version
1407 ,p_init_msg_list => p_init_msg_list
1408 ,p_commit => p_commit
1409 ,p_validation_level => Fnd_Api.g_valid_level_full
1410 ,x_return_status => x_return_status
1411 ,x_msg_count => x_msg_count
1412 ,x_msg_data => x_msg_data
1413 ,p_qualifiers_rec => l_qualifier_rec);
1414
1415 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1416 RAISE Fnd_Api.g_exc_error;
1417 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1418 RAISE Fnd_Api.g_exc_unexpected_error;
1419 END IF;
1420 ELSIF p_qualifier_tbl(i).operation = 'UPDATE' THEN
1421 ozf_volume_offer_qual_pvt.update_vo_qualifier(
1422 p_api_version_number => p_api_version
1423 ,p_init_msg_list => p_init_msg_list
1424 ,p_commit => p_commit
1425 ,p_validation_level => Fnd_Api.g_valid_level_full
1426 ,x_return_status => x_return_status
1427 ,x_msg_count => x_msg_count
1428 ,x_msg_data => x_msg_data
1429 ,p_qualifiers_rec => l_qualifier_rec);
1430
1431 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1432 RAISE Fnd_Api.g_exc_error;
1433 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1434 RAISE Fnd_Api.g_exc_unexpected_error;
1435 END IF;
1436 ELSIF p_qualifier_tbl(i).operation = 'DELETE' THEN
1437 ozf_volume_offer_qual_pvt.delete_vo_qualifier(
1438 p_api_version_number => p_api_version
1439 ,p_init_msg_list => p_init_msg_list
1440 ,p_commit => p_commit
1441 ,p_validation_level => Fnd_Api.g_valid_level_full
1442 ,x_return_status => x_return_status
1443 ,x_msg_count => x_msg_count
1444 ,x_msg_data => x_msg_data
1445 ,p_qualifier_id => l_qualifier_rec.qualifier_id);
1446
1447 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1448 RAISE Fnd_Api.g_exc_error;
1449 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1450 RAISE Fnd_Api.g_exc_unexpected_error;
1451 END IF;
1452 END IF;
1453 END LOOP;
1454
1455 /* Ozf_Offer_Pvt.process_market_qualifiers(
1456 p_init_msg_list => p_init_msg_list
1457 ,p_api_version => p_api_version
1458 ,p_commit => p_commit
1459 ,x_return_status => x_return_status
1460 ,x_msg_count => x_msg_count
1461 ,x_msg_data => x_msg_data
1462 ,p_qualifiers_tbl => l_qualifiers_tbl
1463 ,x_error_location => x_error_location
1464 ,x_qualifiers_tbl => l_qualifiers_tbl_out);
1465
1466 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1467 RAISE Fnd_Api.g_exc_error;
1468 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1469 RAISE Fnd_Api.g_exc_unexpected_error;
1470 END IF;*/
1471 END IF;
1472
1473 IF p_budget_tbl.COUNT > 0 THEN
1474 FOR i IN p_budget_tbl.FIRST..p_budget_tbl.LAST LOOP
1475 l_act_budgets_rec.act_budget_used_by_id := x_qp_list_header_id;
1476 l_act_budgets_rec.budget_source_id := p_budget_tbl(i).budget_id;
1477 l_act_budgets_rec.request_amount := p_budget_tbl(i).budget_amount;
1478 l_act_budgets_rec.budget_source_type := 'FUND';
1479 l_act_budgets_rec.transfer_type := 'REQUEST';
1480 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
1481 l_act_budgets_rec.request_currency := p_modifier_list_rec.transaction_currency_code;
1482 l_act_budgets_rec.approved_in_currency := p_modifier_list_rec.transaction_currency_code;
1483 l_act_budgets_rec.activity_budget_id := p_budget_tbl(i).act_budget_id;
1484
1485 OPEN c_act_budget_obj_ver(l_act_budgets_rec.activity_budget_id);
1486 FETCH c_act_budget_obj_ver INTO l_act_budgets_rec.object_version_number;
1487 CLOSE c_act_budget_obj_ver;
1488
1489 IF p_budget_tbl(i).operation = 'CREATE' THEN
1490 Ozf_Actbudgets_Pvt.create_act_budgets(
1491 p_api_version => p_api_version
1492 ,p_init_msg_list => p_init_msg_list
1493 ,p_commit => p_commit
1494 ,p_validation_level => Fnd_Api.g_valid_level_full
1495 ,x_return_status => x_return_status
1496 ,x_msg_count => x_msg_count
1497 ,x_msg_data => x_msg_data
1498 ,p_act_budgets_rec => l_act_budgets_rec
1499 ,x_act_budget_id => l_activity_budget_id);
1500
1501 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1502 RAISE Fnd_Api.g_exc_error;
1503 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1504 RAISE Fnd_Api.g_exc_unexpected_error;
1505 END IF;
1506 ELSIF p_budget_tbl(i).operation = 'UPDATE' THEN
1507 Ozf_Actbudgets_Pvt.update_act_budgets(
1508 p_api_version => p_api_version
1509 ,p_init_msg_list => p_init_msg_list
1510 ,p_commit => p_commit
1511 ,p_validation_level => Fnd_Api.g_valid_level_full
1512 ,x_return_status => x_return_status
1513 ,x_msg_count => x_msg_count
1514 ,x_msg_data => x_msg_data
1515 ,p_act_budgets_rec => l_act_budgets_rec);
1516
1517 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1518 RAISE Fnd_Api.g_exc_error;
1519 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1520 RAISE Fnd_Api.g_exc_unexpected_error;
1521 END IF;
1522 ELSIF p_budget_tbl(i).operation = 'DELETE' THEN
1523 Ozf_Actbudgets_Pvt.delete_act_budgets(
1524 p_api_version => p_api_version
1525 ,p_init_msg_list => p_init_msg_list
1526 ,p_commit => p_commit
1527 ,p_validation_level => Fnd_Api.g_valid_level_full
1528 ,x_return_status => x_return_status
1529 ,x_msg_count => x_msg_count
1530 ,x_msg_data => x_msg_data
1531 ,p_act_budget_id => l_act_budgets_rec.activity_budget_id
1532 ,p_object_version => l_act_budgets_rec.object_version_number);
1533
1534 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1535 RAISE Fnd_Api.g_exc_error;
1536 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1537 RAISE Fnd_Api.g_exc_unexpected_error;
1538 END IF;
1539 END IF;
1540 END LOOP;
1541 END IF;
1542
1543 -- PBH lines
1544 IF p_vo_pbh_tbl.COUNT > 0 THEN
1545 FOR i IN p_vo_pbh_tbl.FIRST..p_vo_pbh_tbl.LAST LOOP
1546 l_vo_pbh_rec.offer_discount_line_id := p_vo_pbh_tbl(i).offer_discount_line_id;
1547 l_vo_pbh_rec.volume_type := p_vo_pbh_tbl(i).volume_type;
1548 l_vo_pbh_rec.volume_break_type := p_vo_pbh_tbl(i).volume_break_type;
1549 l_vo_pbh_rec.discount_type := p_vo_pbh_tbl(i).discount_type;
1550 l_vo_pbh_rec.tier_type := p_vo_pbh_tbl(i).tier_type; -- 'PBH'
1551 l_vo_pbh_rec.tier_level := p_vo_pbh_tbl(i).tier_level; --'HEADER'
1552 l_vo_pbh_rec.uom_code := p_vo_pbh_tbl(i).uom_code;
1553 l_vo_pbh_rec.object_version_number := p_vo_pbh_tbl(i).object_version_number;
1554 l_vo_pbh_rec.offer_id := l_modifier_list_rec.offer_id;
1555 l_vo_pbh_rec.discount_by_code := p_vo_pbh_tbl(i).discount_by_code;
1556 l_vo_pbh_rec.offr_disc_struct_name_id := p_vo_pbh_tbl(i).offr_disc_struct_name_id;
1557 l_vo_pbh_rec.name := p_vo_pbh_tbl(i).name;
1558 l_vo_pbh_rec.description := p_vo_pbh_tbl(i).description;
1559
1560 IF p_vo_pbh_tbl(i).operation = 'CREATE' THEN
1561 ozf_volume_offer_disc_pvt.create_vo_discount(
1562 p_api_version_number => p_api_version
1563 ,p_init_msg_list => p_init_msg_list
1564 ,p_commit => p_commit
1565 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1566 ,x_return_status => x_return_status
1567 ,x_msg_count => x_msg_count
1568 ,x_msg_data => x_msg_data
1569 ,p_vo_disc_rec => l_vo_pbh_rec
1570 ,x_vo_discount_line_id => l_vo_pbh_line_id);
1571
1572 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1573 RAISE Fnd_Api.g_exc_error;
1574 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1575 RAISE Fnd_Api.g_exc_unexpected_error;
1576 END IF;
1577 ELSIF p_vo_pbh_tbl(i).operation = 'UPDATE' THEN
1578 ozf_volume_offer_disc_pvt.update_vo_discount(
1579 p_api_version_number => p_api_version
1580 ,p_init_msg_list => p_init_msg_list
1581 ,p_commit => p_commit
1582 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1583 ,x_return_status => x_return_status
1584 ,x_msg_count => x_msg_count
1585 ,x_msg_data => x_msg_data
1586 ,p_vo_disc_rec => l_vo_pbh_rec);
1587
1588 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1589 RAISE Fnd_Api.g_exc_error;
1590 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1591 RAISE Fnd_Api.g_exc_unexpected_error;
1592 END IF;
1593 ELSIF p_vo_pbh_tbl(i).operation = 'DELETE' THEN
1594 ozf_volume_offer_disc_pvt.delete_vo_discount(
1595 p_api_version_number => p_api_version
1596 ,p_init_msg_list => p_init_msg_list
1597 ,p_commit => p_commit
1598 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1599 ,x_return_status => x_return_status
1600 ,x_msg_count => x_msg_count
1601 ,x_msg_data => x_msg_data
1602 ,p_offer_discount_line_id => l_vo_pbh_rec.offer_discount_line_id
1603 ,p_object_version_number => l_vo_pbh_rec.object_version_number);
1604
1605 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1606 RAISE Fnd_Api.g_exc_error;
1607 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1608 RAISE Fnd_Api.g_exc_unexpected_error;
1609 END IF;
1610 END IF; -- end PBH operation switching
1611
1612 -- DIS lines
1613 IF p_vo_dis_tbl.COUNT > 0 THEN
1614 FOR j IN p_vo_dis_tbl.FIRST..p_vo_dis_tbl.LAST LOOP
1615 IF p_vo_dis_tbl(j).pbh_index = p_vo_pbh_tbl(i).pbh_index THEN
1616 l_vo_dis_rec.offer_discount_line_id := p_vo_dis_tbl(j).offer_discount_line_id;
1617 l_vo_dis_rec.parent_discount_line_id := l_vo_pbh_line_id;
1618 l_vo_dis_rec.volume_from := p_vo_dis_tbl(j).volume_from;
1619 l_vo_dis_rec.volume_to := p_vo_dis_tbl(j).volume_to;
1620 l_vo_dis_rec.volume_operator := p_vo_dis_tbl(j).volume_operator; -- 'BETWEEN'
1621 l_vo_dis_rec.discount := p_vo_dis_tbl(j).discount;
1622 l_vo_dis_rec.tier_type := p_vo_dis_tbl(j).tier_type; -- 'DIS'
1623 l_vo_dis_rec.tier_level := p_vo_dis_tbl(j).tier_level; -- 'HEADER'
1624 l_vo_dis_rec.object_version_number := p_vo_dis_tbl(j).object_version_number;
1625 l_vo_dis_rec.offer_id := l_modifier_list_rec.offer_id;
1626 l_vo_dis_rec.discount_by_code := p_vo_dis_tbl(j).discount_by_code;
1627 l_vo_dis_rec.formula_id := p_vo_dis_tbl(j).formula_id;
1628 --added for bug 8721678
1629 l_vo_dis_rec.volume_break_type := p_vo_dis_tbl(j).volume_break_type;
1630
1631 IF p_vo_dis_tbl(j).operation = 'CREATE' THEN
1632 ozf_volume_offer_disc_pvt.create_vo_discount(
1633 p_api_version_number => p_api_version
1634 ,p_init_msg_list => p_init_msg_list
1635 ,p_commit => p_commit
1636 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1637 ,x_return_status => x_return_status
1638 ,x_msg_count => x_msg_count
1639 ,x_msg_data => x_msg_data
1640 ,p_vo_disc_rec => l_vo_dis_rec
1641 ,x_vo_discount_line_id => l_vo_dis_line_id);
1642
1643 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1644 RAISE Fnd_Api.g_exc_error;
1645 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1646 RAISE Fnd_Api.g_exc_unexpected_error;
1647 END IF;
1648 ELSIF p_vo_dis_tbl(j).operation = 'UPDATE' THEN
1649 ozf_volume_offer_disc_pvt.update_vo_discount(
1650 p_api_version_number => p_api_version
1651 ,p_init_msg_list => p_init_msg_list
1652 ,p_commit => p_commit
1653 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1654 ,x_return_status => x_return_status
1655 ,x_msg_count => x_msg_count
1656 ,x_msg_data => x_msg_data
1657 ,p_vo_disc_rec => l_vo_dis_rec);
1658
1659 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1660 RAISE Fnd_Api.g_exc_error;
1661 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1662 RAISE Fnd_Api.g_exc_unexpected_error;
1663 END IF;
1664 ELSIF p_vo_dis_tbl(j).operation = 'DELETE' THEN
1665 ozf_volume_offer_disc_pvt.delete_vo_discount(
1666 p_api_version_number => p_api_version
1667 ,p_init_msg_list => p_init_msg_list
1668 ,p_commit => p_commit
1669 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1670 ,x_return_status => x_return_status
1671 ,x_msg_count => x_msg_count
1672 ,x_msg_data => x_msg_data
1673 ,p_offer_discount_line_id => l_vo_dis_rec.offer_discount_line_id
1674 ,p_object_version_number => l_vo_dis_rec.object_version_number);
1675
1676 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1677 RAISE Fnd_Api.g_exc_error;
1678 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1679 RAISE Fnd_Api.g_exc_unexpected_error;
1680 END IF;
1681 END IF; -- end DIS opeartion switching
1682 END IF; -- end pbh_index matching
1683 END LOOP;
1684 END IF; -- end DIS lines
1685
1686 -- products
1687 IF p_vo_prod_tbl.COUNT > 0 THEN
1688 FOR k IN p_vo_prod_tbl.FIRST..p_vo_prod_tbl.LAST LOOP
1689 IF p_vo_prod_tbl(k).pbh_index = p_vo_pbh_tbl(i).pbh_index THEN
1690 l_vo_prod_rec.off_discount_product_id := p_vo_prod_tbl(k).off_discount_product_id;
1691 l_vo_prod_rec.excluder_flag := p_vo_prod_tbl(k).excluder_flag;
1692 l_vo_prod_rec.offer_discount_line_id := l_vo_pbh_line_id;
1693 l_vo_prod_rec.offer_id := l_modifier_list_rec.offer_id;
1694 l_vo_prod_rec.object_version_number := p_vo_prod_tbl(k).object_version_number;
1695 l_vo_prod_rec.product_context := p_vo_prod_tbl(k).product_context;
1696 l_vo_prod_rec.product_attribute := p_vo_prod_tbl(k).product_attribute;
1697 l_vo_prod_rec.product_attr_value := p_vo_prod_tbl(k).product_attr_value;
1698 l_vo_prod_rec.apply_discount_flag := p_vo_prod_tbl(k).apply_discount_flag;
1699 l_vo_prod_rec.include_volume_flag := p_vo_prod_tbl(k).include_volume_flag;
1700
1701 IF p_vo_prod_tbl(k).operation = 'CREATE' THEN
1702 ozf_volume_offer_disc_pvt.create_vo_product(
1703 p_api_version_number => p_api_version
1704 ,p_init_msg_list => p_init_msg_list
1705 ,p_commit => p_commit
1706 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1707 ,x_return_status => x_return_status
1708 ,x_msg_count => x_msg_count
1709 ,x_msg_data => x_msg_data
1710 ,p_vo_prod_rec => l_vo_prod_rec
1711 ,x_off_discount_product_id => l_vo_prod_id);
1712
1713 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1714 RAISE Fnd_Api.g_exc_error;
1715 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1716 RAISE Fnd_Api.g_exc_unexpected_error;
1717 END IF;
1718 ELSIF p_vo_prod_tbl(k).operation = 'UPDATE' THEN
1719 ozf_volume_offer_disc_pvt.update_vo_product(
1720 p_api_version_number => p_api_version
1721 ,p_init_msg_list => p_init_msg_list
1722 ,p_commit => p_commit
1723 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1724 ,x_return_status => x_return_status
1725 ,x_msg_count => x_msg_count
1726 ,x_msg_data => x_msg_data
1727 ,p_vo_prod_rec => l_vo_prod_rec);
1728
1729 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1730 RAISE Fnd_Api.g_exc_error;
1731 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1732 RAISE Fnd_Api.g_exc_unexpected_error;
1733 END IF;
1734 ELSIF p_vo_prod_tbl(k).operation = 'DELETE' THEN
1735 ozf_volume_offer_disc_pvt.delete_vo_product(
1736 p_api_version_number => p_api_version
1737 ,p_init_msg_list => p_init_msg_list
1738 ,p_commit => p_commit
1739 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1740 ,x_return_status => x_return_status
1741 ,x_msg_count => x_msg_count
1742 ,x_msg_data => x_msg_data
1743 ,p_off_discount_product_id => l_vo_prod_rec.off_discount_product_id
1744 ,p_object_version_number => l_vo_prod_rec.object_version_number);
1745
1746 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1747 RAISE Fnd_Api.g_exc_error;
1748 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1749 RAISE Fnd_Api.g_exc_unexpected_error;
1750 END IF;
1751 END IF; -- end product operation switching
1752 END IF; -- end pbh_idex matching
1753 END LOOP;
1754 END IF; -- end products
1755 END LOOP;
1756 END IF; -- end PBH lines
1757
1758 -- market options
1759 IF p_vo_mo_tbl.COUNT > 0 THEN
1760 FOR i IN p_vo_mo_tbl.FIRST..p_vo_mo_tbl.LAST LOOP
1761 l_vo_mo_rec.offer_market_option_id := p_vo_mo_tbl(i).offer_market_option_id;
1762 l_vo_mo_rec.offer_id := l_modifier_list_rec.offer_id;
1763 l_vo_mo_rec.qp_list_header_id := x_qp_list_header_id;
1764 l_vo_mo_rec.group_number := p_vo_mo_tbl(i).group_number;
1765 l_vo_mo_rec.retroactive_flag := p_vo_mo_tbl(i).retroactive_flag;
1766 l_vo_mo_rec.beneficiary_party_id := p_vo_mo_tbl(i).beneficiary_party_id;
1767 l_vo_mo_rec.combine_schedule_flag := p_vo_mo_tbl(i).combine_schedule_flag;
1768 l_vo_mo_rec.volume_tracking_level_code := p_vo_mo_tbl(i).volume_tracking_level_code;
1769 l_vo_mo_rec.accrue_to_code := p_vo_mo_tbl(i).accrue_to_code;
1770 l_vo_mo_rec.precedence := p_vo_mo_tbl(i).precedence;
1771 l_vo_mo_rec.object_version_number := p_vo_mo_tbl(i).object_version_number;
1772 l_vo_mo_rec.security_group_id := p_vo_mo_tbl(i).security_group_id;
1773
1774 IF p_vo_mo_tbl(i).operation = 'CREATE' THEN
1775 ozf_offer_market_options_pvt.create_market_options(
1776 p_api_version_number => p_api_version
1777 ,p_init_msg_list => p_init_msg_list
1778 ,p_commit => p_commit
1779 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1780 ,x_return_status => x_return_status
1781 ,x_msg_count => x_msg_count
1782 ,x_msg_data => x_msg_data
1783 ,p_mo_rec => l_vo_mo_rec
1784 ,x_vo_market_option_id => l_vo_mo_id);
1785
1786 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1787 RAISE Fnd_Api.g_exc_error;
1788 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1789 RAISE Fnd_Api.g_exc_unexpected_error;
1790 END IF;
1791 ELSIF p_vo_mo_tbl(i).operation = 'UPDATE' THEN
1792 ozf_offer_market_options_pvt.update_market_options(
1793 p_api_version_number => p_api_version
1794 ,p_init_msg_list => p_init_msg_list
1795 ,p_commit => p_commit
1796 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1797 ,x_return_status => x_return_status
1798 ,x_msg_count => x_msg_count
1799 ,x_msg_data => x_msg_data
1800 ,p_mo_rec => l_vo_mo_rec);
1801
1802 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1803 RAISE Fnd_Api.g_exc_error;
1804 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1805 RAISE Fnd_Api.g_exc_unexpected_error;
1806 END IF;
1807 ELSIF p_vo_mo_tbl(i).operation = 'DELETE' THEN
1808 ozf_offer_market_options_pvt.delete_market_options(
1809 p_api_version_number => p_api_version
1810 ,p_init_msg_list => p_init_msg_list
1811 ,p_commit => p_commit
1812 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1813 ,x_return_status => x_return_status
1814 ,x_msg_count => x_msg_count
1815 ,x_msg_data => x_msg_data
1816 ,p_offer_market_option_id => l_vo_mo_rec.offer_market_option_id
1817 ,p_object_version_number => l_vo_mo_rec.object_version_number);
1818
1819 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1820 RAISE Fnd_Api.g_exc_error;
1821 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1822 RAISE Fnd_Api.g_exc_unexpected_error;
1823 END IF;
1824 END IF;
1825 END LOOP;
1826 END IF; -- end market options
1827
1828 Ozf_Offer_Pvt.process_modifiers(
1829 p_init_msg_list => p_init_msg_list
1830 ,p_api_version => p_api_version
1831 ,p_commit => p_commit
1832 ,x_return_status => x_return_status
1833 ,x_msg_count => x_msg_count
1834 ,x_msg_data => x_msg_data
1835 ,p_offer_type => 'VOLUME_OFFER'
1836 ,p_modifier_list_rec => l_modifier_list_rec
1837 ,p_modifier_line_tbl => l_modifier_line_tbl
1838 ,x_qp_list_header_id => l_dummy
1839 ,x_error_location => x_error_location);
1840
1841 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
1842 RAISE Fnd_Api.g_exc_error;
1843 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1844 RAISE Fnd_Api.g_exc_unexpected_error;
1845 END IF;
1846
1847 EXCEPTION
1848 WHEN Fnd_Api.G_EXC_ERROR THEN
1849 x_return_status := Fnd_Api.g_ret_sts_error ;
1850 ROLLBACK TO process_vo;
1851 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
1852 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1853 END IF;
1854 Fnd_Msg_Pub.Count_AND_Get
1855 ( p_count => x_msg_count,
1856 p_data => x_msg_data,
1857 p_encoded => Fnd_Api.G_FALSE
1858 );
1859 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1860 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1861 ROLLBACK TO process_vo;
1862 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
1863 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1864 END IF;
1865 Fnd_Msg_Pub.Count_AND_Get
1866 ( p_count => x_msg_count,
1867 p_data => x_msg_data,
1868 p_encoded => Fnd_Api.G_FALSE
1869 );
1870 WHEN OTHERS THEN
1871 x_return_status := Fnd_Api.g_ret_sts_unexp_erroR ;
1872 ROLLBACK TO process_vo;
1873 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
1874 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1875 END IF;
1876 Fnd_Msg_Pub.Count_AND_Get
1877 ( p_count => x_msg_count,
1878 p_data => x_msg_data,
1879 p_encoded => Fnd_Api.G_FALSE
1880 );
1881
1882 END process_vo;
1883
1884 --//ER 12977732 TPM Offer Integration ER.
1885 ---------------------------------------------------------------------
1886 -- PROCEDURE
1887 -- process_offer_from_adapter
1888 --
1889 -- PURPOSE
1890 -- Process a offer , called from adapter.
1891 --
1892 ---------------------------------------------------------------------
1893
1894 PROCEDURE process_offer_from_adapter(
1895 p_api_version IN NUMBER
1896 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1897 ,p_commit IN VARCHAR2 := fnd_api.g_false
1898 ,p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL
1899 ,x_return_status OUT NOCOPY VARCHAR2
1900 ,x_msg_count OUT NOCOPY NUMBER
1901 ,x_msg_data OUT NOCOPY VARCHAR2
1902 ,p_source IN VARCHAR2 := 'SIEBEL'
1903 ,p_modifier_rec IN modifier_list_rec_type
1904 ,p_modifier_line_tbl IN modifier_line_tbl_type
1905 ,x_qp_list_header_id OUT NOCOPY NUMBER
1906 ,x_error_location OUT NOCOPY NUMBER
1907 ,x_msg_tbl OUT NOCOPY OZF_Utility_PVT.ozf_msg_tbl_type
1908 ,x_line_tbl OUT NOCOPY list_line_tbl_type
1909 ) IS
1910 l_api_version CONSTANT NUMBER := 1.0;
1911 l_api_name VARCHAR2(90) := 'process_offer_from_adapter';
1912 l_modifier_list_rec OZF_OFFER_PVT.modifier_list_rec_type;
1913 l_modifier_line_tbl OZF_OFFER_PVT.modifier_line_tbl_type;
1914 l_act_product_rec AMS_ACTPRODUCT_PVT.act_product_rec_type;
1915 l_act_product_id NUMBER;
1916 l_lumpsum_distr_value NUMBER;
1917 l_user_id NUMBER := fnd_api.g_miss_num;
1918 l_line_index NUMBER := 0;
1919 l_dummy NUMBER;
1920 k NUMBER := 1;
1921
1922 l_uom VARCHAR2(3);
1923 l_lookup_stat VARCHAR2(1);
1924 l_qualifier BOOLEAN;
1925 l_currency_code VARCHAR2(30);
1926 l_fund_id NUMBER;
1927
1928 CURSOR c_offer_detail(l_list_header_id VARCHAR2) IS
1929 SELECT offer_id, object_version_number
1930 FROM ozf_offers
1931 WHERE qp_list_header_id = l_list_header_id;
1932
1933 CURSOR c_list_line(p_list_header_id IN NUMBER) IS
1934 SELECT NVL(map.xref_line_id_value,line.list_line_id) list_line_id
1935 , NVL(map.list_line_number,list_line_no) list_line_no
1936 FROM qp_list_lines line, ozf_xref_map map
1937 WHERE list_header_id = p_list_header_id
1938 AND line.list_line_id = map.list_line_id(+);
1939
1940 CURSOR c_lump_line(p_list_header_id IN NUMBER) IS
1941 SELECT NVL(map.xref_line_id_value,prod.activity_product_id) activity_product_id
1942 ,NVL(map.list_line_number,prod.attribute1) attribute1
1943 FROM ams_act_products prod, ozf_xref_map map
1944 WHERE act_product_used_by_id = p_list_header_id
1945 AND arc_act_product_used_by = 'OFFR'
1946 AND prod.activity_product_id = map.list_line_id(+);
1947
1948
1949 CURSOR c_resource_id (cv_user_name IN VARCHAR2) IS
1950 select jtf.resource_id
1951 from jtf_rs_resource_extns jtf,fnd_user fnd
1952 where upper(fnd.user_name) = cv_user_name --'SIEBEL'
1953 and fnd.user_id = jtf.user_id;
1954
1955 CURSOR c_uom ( p_product_id IN NUMBER
1956 , p_org_id IN NUMBER
1957 )
1958 IS
1959 select primary_uom_code
1960 from mtl_system_items
1961 where inventory_item_id = p_product_id
1962 and organization_id = p_org_id;
1963
1964 CURSOR c_fund_id(p_fund_id IN NUMBER) IS
1965 SELECT fund_id
1966 FROM ozf_funds_all_b
1967 WHERE fund_id = p_fund_id;
1968
1969 CURSOR c_currency(p_currency_code IN VARCHAR2) IS
1970 SELECT currency_code
1971 FROM fnd_currencies
1972 WHERE currency_code = p_currency_code
1973 AND enabled_flag='Y';
1974
1975 BEGIN
1976 -- Standard Start of API savepoint
1977 SAVEPOINT process_offer_from_adapter;
1978
1979 -- Standard call to check for call compatibility.
1980 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
1981 Fnd_Msg_Pub.initialize;
1982 END IF;
1983
1984 MO_GLOBAL.init('OZF');
1985 IF p_modifier_rec.orig_org_id <> FND_API.g_miss_num AND p_modifier_rec.orig_org_id IS NOT NULL THEN
1986 MO_GLOBAL.set_policy_context('S',p_modifier_rec.orig_org_id);
1987 END IF;
1988
1989 -- Initialize message list if p_init_msg_list is set to TRUE.
1990 IF NOT Fnd_Api.compatible_api_call(l_api_version,
1991 p_api_version,
1992 l_api_name,
1993 g_pkg_name)
1994 THEN
1995 RAISE Fnd_Api.g_exc_unexpected_error;
1996 END IF;
1997
1998 x_return_status := Fnd_Api.g_ret_sts_success;
1999 x_error_location := 0;
2000
2001 --//Validations
2002 IF p_modifier_rec.qp_list_header_id = FND_API.g_miss_num OR p_modifier_rec.qp_list_header_id IS NULL THEN
2003 IF p_modifier_rec.offer_type = FND_API.g_miss_char OR p_modifier_rec.offer_type IS NULL THEN
2004 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2005 FND_MESSAGE.set_name('OZF', 'OZF_OFFERS_NO_OFFER_TYPE');
2006 --//Please enter a value for Offer Type
2007 FND_MSG_PUB.add;
2008 END IF;
2009 RAISE FND_API.G_EXC_ERROR;
2010 END IF;
2011
2012 IF p_modifier_rec.offer_type NOT IN ('ACCRUAL','OFF_INVOICE','LUMPSUM') THEN
2013 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2014 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_TPM_OFFR_TYPE');
2015 --//Only Lump sum, Off Invoice and Accrual offer types are supported.
2016 FND_MSG_PUB.add;
2017 END IF;
2018 RAISE FND_API.G_EXC_ERROR;
2019 END IF;
2020
2021 IF p_modifier_rec.name = FND_API.g_miss_char OR p_modifier_rec.name IS NULL THEN
2022 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2023 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
2024 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Offer Name');
2025 FND_MSG_PUB.add;
2026 END IF;
2027 RAISE FND_API.G_EXC_ERROR;
2028 END IF;
2029
2030 --//Lumpsum offer Validations
2031 --//==============================
2032 IF p_modifier_rec.offer_type = 'LUMPSUM' THEN
2033 IF p_modifier_rec.lumpsum_amount = FND_API.g_miss_num OR p_modifier_rec.lumpsum_amount IS NULL THEN
2034 IF p_modifier_rec.offer_amount = FND_API.g_miss_num OR p_modifier_rec.offer_amount IS NULL THEN
2035 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2036 FND_MESSAGE.set_name('OZF', 'OZF_NO_LUMPSUM_AMOUNT');
2037 --//Lumpsum Amount is mandatory.Please provide Lumpsum Amount
2038 FND_MSG_PUB.add;
2039 END IF;
2040 RAISE FND_API.G_EXC_ERROR;
2041 ELSE
2042 l_modifier_list_rec.lumpsum_amount := p_modifier_rec.offer_amount;
2043 l_modifier_list_rec.offer_amount := p_modifier_rec.offer_amount;
2044 END IF;
2045 ELSE
2046 l_modifier_list_rec.lumpsum_amount := p_modifier_rec.lumpsum_amount;
2047 l_modifier_list_rec.offer_amount := NVL(p_modifier_rec.offer_amount,l_modifier_list_rec.lumpsum_amount);
2048 END IF;
2049
2050 IF p_modifier_rec.distribution_type <> FND_API.g_miss_char AND p_modifier_rec.distribution_type IS NOT NULL THEN
2051 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
2052 p_lookup_table_name =>'OZF_LOOKUPS'
2053 ,p_lookup_type =>'OZF_LUMPSUM_DISTRIBUTION_TYPE'
2054 ,p_lookup_code => p_modifier_rec.distribution_type);
2055
2056 IF l_lookup_stat = FND_API.g_false THEN
2057 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2058 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_DISTRIBUTION');
2059 --//Invalid distribution of Lumpsum Offer amount
2060 FND_MSG_PUB.add;
2061 END IF;
2062 RAISE FND_API.G_EXC_ERROR;
2063 END IF;
2064 l_modifier_list_rec.distribution_type := p_modifier_rec.distribution_type;
2065 ELSE
2066 l_modifier_list_rec.distribution_type := '%';
2067 END IF;
2068
2069 IF p_modifier_rec.lumpsum_payment_type <> FND_API.g_miss_char AND p_modifier_rec.lumpsum_payment_type IS NOT NULL THEN
2070 l_lookup_stat :=OZF_UTILITY_PVT.check_lookup_exists(
2071 p_lookup_table_name =>'OZF_LOOKUPS'
2072 ,p_lookup_type =>'OZF_OFFER_LUMPSUM_PAYMENT'
2073 ,p_lookup_code => p_modifier_rec.lumpsum_payment_type);
2074
2075 IF l_lookup_stat = FND_API.g_false THEN
2076 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2077 FND_MESSAGE.set_name('OZF', 'OZF_OFFER_LUMPSUM_PAYMENT');
2078 --//Lumpsum Offer payment Type provided is invalid, please provide a valid Payment type Type.
2079 FND_MSG_PUB.add;
2080 END IF;
2081 RAISE FND_API.G_EXC_ERROR;
2082 END IF;
2083 l_modifier_list_rec.lumpsum_payment_type := p_modifier_rec.lumpsum_payment_type;
2084 ELSE
2085 l_modifier_list_rec.lumpsum_payment_type := 'ACCRUE';
2086 END IF;
2087
2088 IF p_modifier_rec.start_date_active = FND_API.g_miss_date OR p_modifier_rec.start_date_active IS NULL THEN
2089 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2090 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_NO_START_DATE');
2091 --//Start date is required for this offer type.
2092 FND_MSG_PUB.add;
2093 END IF;
2094 RAISE FND_API.G_EXC_ERROR;
2095 END IF;
2096 IF p_modifier_rec.transaction_currency_code = FND_API.g_miss_char OR p_modifier_rec.transaction_currency_code IS NULL THEN
2097 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2098 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_LS_SD_CURR_REQD');
2099 --//Currency is required for this offer.
2100 FND_MSG_PUB.add;
2101 END IF;
2102 RAISE FND_API.G_EXC_ERROR;
2103 ELSE
2104 OPEN c_currency(p_modifier_rec.transaction_currency_code);
2105 FETCH c_currency INTO l_currency_code;
2106 CLOSE c_currency;
2107 IF l_currency_code IS NULL THEN
2108 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2109 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_CURRENCY_CODE');
2110 --//Mandatory field Currency Code is missing. Please enter a valid Currency Code
2111 FND_MSG_PUB.add;
2112 END IF;
2113 RAISE FND_API.G_EXC_ERROR;
2114 END IF;
2115 END IF;
2116 END IF; -- IF p_modifier_rec.offer_type = 'LUMPSUM' THEN
2117
2118 --//Budget validations
2119 IF p_modifier_rec.budget_source_id <> FND_API.g_miss_num AND p_modifier_rec.budget_source_id IS NOT NULL THEN
2120 OPEN c_fund_id(p_modifier_rec.budget_source_id);
2121 FETCH c_fund_id INTO l_fund_id;
2122 CLOSE c_fund_id;
2123
2124 IF l_fund_id IS NULL THEN
2125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2126 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_FUND_ID');
2127 --//The Fund ID provided is invalid. Please provide a valid Fund ID.
2128 FND_MSG_PUB.add;
2129 END IF;
2130 RAISE FND_API.G_EXC_ERROR;
2131 END IF;
2132 END IF;
2133
2134 IF p_modifier_rec.transaction_currency_code IS NULL OR p_modifier_rec.transaction_currency_code = FND_API.g_miss_char THEN
2135 l_modifier_list_rec.transaction_currency_code := p_modifier_rec.currency_code;
2136 ELSE
2137 l_modifier_list_rec.transaction_currency_code := p_modifier_rec.transaction_currency_code;
2138 END IF;
2139
2140 IF p_modifier_rec.functional_currency_code IS NULL OR p_modifier_rec.functional_currency_code = FND_API.g_miss_char THEN
2141 l_modifier_list_rec.functional_currency_code := NVL(l_modifier_list_rec.transaction_currency_code,p_modifier_rec.currency_code);
2142 ELSE
2143 l_modifier_list_rec.functional_currency_code := p_modifier_rec.functional_currency_code;
2144 END IF;
2145
2146
2147 --//Currency code validations
2148 IF l_modifier_list_rec.transaction_currency_code <> FND_API.g_miss_char AND l_modifier_list_rec.transaction_currency_code IS NOT NULL THEN
2149 OPEN c_currency(l_modifier_list_rec.transaction_currency_code);
2150 FETCH c_currency INTO l_currency_code;
2151 CLOSE c_currency;
2152
2153 IF l_currency_code IS NULL THEN
2154 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2155 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_CURRENCY_CODE');
2156 --//Mandatory field Currency Code is missing. Please enter a valid Currency Code
2157 FND_MSG_PUB.add;
2158 END IF;
2159 RAISE FND_API.G_EXC_ERROR;
2160 END IF;
2161 END IF;
2162 END IF;
2163
2164
2165
2166 --BPEL need default, otherwise throw exception.
2167 x_line_tbl(1).list_line_id := -1;
2168 x_line_tbl(1).list_line_number := '-1';
2169
2170 -- header values
2171 IF p_modifier_rec.OFFER_TYPE = 'ACCRUAL' THEN
2172 l_modifier_list_rec.custom_setup_id := 121;
2173 ELSIF p_modifier_rec.OFFER_TYPE = 'OFF_INVOICE' THEN
2174 l_modifier_list_rec.custom_setup_id := 122;
2175 ELSIF p_modifier_rec.OFFER_TYPE = 'LUMPSUM' THEN
2176 l_modifier_list_rec.custom_setup_id := 123;
2177 END IF;
2178
2179 OPEN c_resource_id(p_source);
2180 FETCH c_resource_id INTO l_user_id;
2181 CLOSE c_resource_id;
2182
2183 l_modifier_list_rec.offer_id := NVL(p_modifier_rec.offer_id,Fnd_Api.g_miss_num);
2184 l_modifier_list_rec.qp_list_header_id := NVL(p_modifier_rec.qp_list_header_id,Fnd_Api.g_miss_num);
2185 l_modifier_list_rec.offer_type := NVL(p_modifier_rec.offer_type,Fnd_Api.g_miss_char);
2186 l_modifier_list_rec.offer_code := NVL(p_modifier_rec.offer_code,Fnd_Api.g_miss_char);
2187 l_modifier_list_rec.activity_media_id := NVL(p_modifier_rec.activity_media_id,Fnd_Api.g_miss_num);
2188 l_modifier_list_rec.reusable := NVL(p_modifier_rec.reusable,'N');
2189 l_modifier_list_rec.user_status_id := NVL(p_modifier_rec.user_status_id,Fnd_Api.g_miss_num);
2190 l_modifier_list_rec.owner_id := l_user_id;
2191 l_modifier_list_rec.wf_item_key := NVL(p_modifier_rec.wf_item_key,Fnd_Api.g_miss_char);
2192 l_modifier_list_rec.customer_reference := NVL(p_modifier_rec.customer_reference,Fnd_Api.g_miss_char);
2193 l_modifier_list_rec.buying_group_contact_id := NVL(p_modifier_rec.buying_group_contact_id,Fnd_Api.g_miss_num);
2194 l_modifier_list_rec.object_version_number := NVL(p_modifier_rec.object_version_number,Fnd_Api.g_miss_num);
2195 l_modifier_list_rec.perf_date_from := NVL(p_modifier_rec.perf_date_from,Fnd_Api.g_miss_date);
2196 l_modifier_list_rec.perf_date_to := NVL(p_modifier_rec.perf_date_to,Fnd_Api.g_miss_date);
2197 l_modifier_list_rec.status_code := NVL(p_modifier_rec.status_code,Fnd_Api.g_miss_char);
2198 l_modifier_list_rec.status_date := NVL(p_modifier_rec.status_date,Fnd_Api.g_miss_date);
2199 l_modifier_list_rec.modifier_level_code := NVL(p_modifier_rec.modifier_level_code,'LINE');
2200 l_modifier_list_rec.order_value_discount_type := NVL(p_modifier_rec.order_value_discount_type,Fnd_Api.g_miss_char);
2201 l_modifier_list_rec.currency_code := NVL(p_modifier_rec.currency_code,Fnd_Api.g_miss_char);
2202 l_modifier_list_rec.lumpsum_amount := NVL(p_modifier_rec.lumpsum_amount,Fnd_Api.g_miss_num);
2203 l_modifier_list_rec.lumpsum_payment_type := NVL(p_modifier_rec.lumpsum_payment_type,Fnd_Api.g_miss_char);
2204 l_modifier_list_rec.offer_amount := NVL(p_modifier_rec.offer_amount,FND_API.g_miss_num);
2205
2206 --Fix for Bug 16400081
2207 l_modifier_list_rec.lumpsum_amount := OZF_UTILITY_PVT.CurrRound(l_modifier_list_rec.lumpsum_amount,l_modifier_list_rec.currency_code);
2208 l_modifier_list_rec.offer_amount := OZF_UTILITY_PVT.CurrRound(l_modifier_list_rec.offer_amount,l_modifier_list_rec.currency_code);
2209
2210 l_modifier_list_rec.budget_amount_tc := NVL(p_modifier_rec.budget_amount_tc, p_modifier_rec.offer_amount);
2211 l_modifier_list_rec.budget_amount_fc := NVL(p_modifier_rec.budget_amount_fc, p_modifier_rec.offer_amount);
2212 l_modifier_list_rec.transaction_currency_code := NVL(p_modifier_rec.transaction_currency_code,p_modifier_rec.currency_code);
2213 l_modifier_list_rec.functional_currency_code := NVL(p_modifier_rec.functional_currency_code,p_modifier_rec.currency_code);
2214 l_modifier_list_rec.context := NVL(p_modifier_rec.context,Fnd_Api.g_miss_char);
2215 l_modifier_list_rec.attribute1 := NVL(p_modifier_rec.attribute1,Fnd_Api.g_miss_char);
2216 l_modifier_list_rec.attribute2 := NVL(p_modifier_rec.attribute2,Fnd_Api.g_miss_char);
2217 l_modifier_list_rec.attribute3 := NVL(p_modifier_rec.attribute3,Fnd_Api.g_miss_char);
2218 l_modifier_list_rec.attribute4 := NVL(p_modifier_rec.attribute4,Fnd_Api.g_miss_char);
2219 l_modifier_list_rec.attribute5 := NVL(p_modifier_rec.attribute5,Fnd_Api.g_miss_char);
2220 l_modifier_list_rec.attribute6 := NVL(p_modifier_rec.attribute6,Fnd_Api.g_miss_char);
2221 l_modifier_list_rec.attribute7 := NVL(p_modifier_rec.attribute7,Fnd_Api.g_miss_char);
2222 l_modifier_list_rec.attribute8 := NVL(p_modifier_rec.attribute8,Fnd_Api.g_miss_char);
2223 l_modifier_list_rec.attribute9 := NVL(p_modifier_rec.attribute9,Fnd_Api.g_miss_char);
2224 l_modifier_list_rec.attribute10 := NVL(p_modifier_rec.attribute10,Fnd_Api.g_miss_char);
2225 l_modifier_list_rec.attribute11 := NVL(p_modifier_rec.attribute11,Fnd_Api.g_miss_char);
2226 l_modifier_list_rec.attribute12 := NVL(p_modifier_rec.attribute12,Fnd_Api.g_miss_char);
2227 l_modifier_list_rec.attribute13 := NVL(p_modifier_rec.attribute13,Fnd_Api.g_miss_char);
2228 l_modifier_list_rec.attribute14 := NVL(p_modifier_rec.attribute14,Fnd_Api.g_miss_char);
2229 l_modifier_list_rec.attribute15 := NVL(p_modifier_rec.attribute15,Fnd_Api.g_miss_char);
2230
2231 l_modifier_list_rec.start_date_active := NVL(p_modifier_rec.start_date_active,Fnd_Api.g_miss_date);
2232 l_modifier_list_rec.end_date_active := NVL(p_modifier_rec.end_date_active,Fnd_Api.g_miss_date);
2233 l_modifier_list_rec.list_type_code := NVL(p_modifier_rec.list_type_code,'PRO');
2234 l_modifier_list_rec.discount_lines_flag := NVL(p_modifier_rec.discount_lines_flag,Fnd_Api.g_miss_char);
2235 l_modifier_list_rec.name := NVL(p_modifier_rec.name,p_modifier_rec.offer_code);
2236 l_modifier_list_rec.description := NVL(p_modifier_rec.description,Fnd_Api.g_miss_char);
2237 l_modifier_list_rec.comments := NVL(p_modifier_rec.comments,Fnd_Api.g_miss_char);
2238 l_modifier_list_rec.ask_for_flag := NVL(p_modifier_rec.ask_for_flag,'N');
2239 l_modifier_list_rec.active_flag := NVL(p_modifier_rec.active_flag,Fnd_Api.g_miss_char);
2240 l_modifier_list_rec.max_no_of_uses := NVL(p_modifier_rec.max_no_of_uses,Fnd_Api.g_miss_num);
2241 l_modifier_list_rec.budget_source_id := NVL(p_modifier_rec.budget_source_id,Fnd_Api.g_miss_num);
2242 l_modifier_list_rec.budget_source_type := NVL(p_modifier_rec.budget_source_type,'FUND');
2243 l_modifier_list_rec.offer_used_by_id := NVL(p_modifier_rec.offer_used_by_id,Fnd_Api.g_miss_num);
2244 l_modifier_list_rec.offer_used_by := NVL(p_modifier_rec.offer_used_by,Fnd_Api.g_miss_char);
2245 l_modifier_list_rec.distribution_type := NVL(p_modifier_rec.distribution_type,Fnd_Api.g_miss_char);
2246 l_modifier_list_rec.amount_limit_id := NVL(p_modifier_rec.amount_limit_id,Fnd_Api.g_miss_num);
2247 l_modifier_list_rec.uses_limit_id := NVL(p_modifier_rec.uses_limit_id,Fnd_Api.g_miss_num);
2248 l_modifier_list_rec.offer_operation := NVL(p_modifier_rec.offer_operation,Fnd_Api.g_miss_char);
2249 l_modifier_list_rec.modifier_operation := NVL(p_modifier_rec.modifier_operation,Fnd_Api.g_miss_char);
2250 l_modifier_list_rec.budget_offer_yn := NVL(p_modifier_rec.budget_offer_yn,'N');
2251 l_modifier_list_rec.break_type := NVL(p_modifier_rec.break_type,Fnd_Api.g_miss_char);
2252 l_modifier_list_rec.retroactive := NVL(p_modifier_rec.retroactive,Fnd_Api.g_miss_char);
2253 l_modifier_list_rec.volume_offer_type := NVL(p_modifier_rec.volume_offer_type,Fnd_Api.g_miss_char);
2254 l_modifier_list_rec.confidential_flag := NVL(p_modifier_rec.confidential_flag,'N');
2255 l_modifier_list_rec.committed_amount_eq_max := NVL(p_modifier_rec.committed_amount_eq_max,'N');
2256 l_modifier_list_rec.source_from_parent := NVL(p_modifier_rec.source_from_parent,'N');
2257 l_modifier_list_rec.buyer_name := NVL(p_modifier_rec.buyer_name,Fnd_Api.g_miss_char);
2258 l_modifier_list_rec.tier_level := NVL(p_modifier_rec.tier_level,Fnd_Api.g_miss_char);
2259 l_modifier_list_rec.na_rule_header_id := NVL(p_modifier_rec.na_rule_header_id,Fnd_Api.g_miss_num);
2260 l_modifier_list_rec.amount_limit_id := NVL(p_modifier_rec.amount_limit_id,Fnd_Api.g_miss_num);
2261 l_modifier_list_rec.uses_limit_id := NVL(p_modifier_rec.uses_limit_id,Fnd_Api.g_miss_num);
2262 l_modifier_list_rec.global_flag := NVL(p_modifier_rec.global_flag,Fnd_Api.g_miss_char);
2263 l_modifier_list_rec.orig_org_id := NVL(p_modifier_rec.orig_org_id,Fnd_Api.g_miss_num);
2264
2265
2266
2267 -- determine CREATE or UPDATE
2268 IF p_modifier_rec.qp_list_header_id = FND_API.g_miss_num OR p_modifier_rec.qp_list_header_id IS NULL THEN
2269 --Defaulting in the create mode
2270 IF p_modifier_rec.reusable IS NULL OR p_modifier_rec.reusable = FND_API.g_miss_char THEN
2271 l_modifier_list_rec.reusable := 'N';
2272 ELSE
2273 l_modifier_list_rec.reusable := p_modifier_rec.reusable;
2274 END IF;
2275
2276 IF p_modifier_rec.modifier_level_code IS NULL OR p_modifier_rec.modifier_level_code = FND_API.g_miss_char THEN
2277 l_modifier_list_rec.modifier_level_code := 'LINE';
2278 ELSE
2279 l_modifier_list_rec.modifier_level_code := p_modifier_rec.modifier_level_code;
2280 END IF;
2281
2282 IF p_modifier_rec.list_type_code IS NULL OR p_modifier_rec.list_type_code = FND_API.g_miss_char THEN
2283 l_modifier_list_rec.list_type_code := 'PRO';
2284 ELSE
2285 l_modifier_list_rec.list_type_code := p_modifier_rec.list_type_code;
2286 END IF;
2287
2288 --//Fix for Bug 13640380
2289 IF p_modifier_rec.description IS NULL OR p_modifier_rec.description = FND_API.g_miss_char THEN
2290 l_modifier_list_rec.description := l_modifier_list_rec.name;
2291 ELSE
2292 l_modifier_list_rec.description := p_modifier_rec.description;
2293 END IF;
2294
2295 IF p_modifier_rec.ask_for_flag IS NULL OR p_modifier_rec.ask_for_flag = FND_API.g_miss_char THEN
2296 l_modifier_list_rec.ask_for_flag := 'N';
2297 ELSE
2298 l_modifier_list_rec.ask_for_flag := p_modifier_rec.ask_for_flag;
2299 END IF;
2300
2301 IF p_modifier_rec.budget_source_id <> FND_API.g_miss_num AND p_modifier_rec.budget_source_id IS NOT NULL THEN
2302 l_modifier_list_rec.budget_source_type := 'FUND';
2303 ELSE
2304 l_modifier_list_rec.budget_source_type := Fnd_Api.g_miss_char;
2305 END IF;
2306
2307 IF p_modifier_rec.confidential_flag IS NULL OR p_modifier_rec.confidential_flag = FND_API.g_miss_char THEN
2308 l_modifier_list_rec.confidential_flag := 'N';
2309 ELSE
2310 l_modifier_list_rec.confidential_flag := p_modifier_rec.confidential_flag;
2311 END IF;
2312
2313
2314 --pass qualifier in create offer flow only
2315 l_modifier_list_rec.ql_qualifier_type := NVL(p_modifier_rec.ql_qualifier_type,Fnd_Api.g_miss_char);
2316 l_modifier_list_rec.ql_qualifier_id := NVL(p_modifier_rec.ql_qualifier_id,Fnd_Api.g_miss_num);
2317 -- create draft offer
2318 l_modifier_list_rec.offer_operation := 'CREATE';
2319 l_modifier_list_rec.modifier_operation := 'CREATE';
2320 l_modifier_list_rec.status_code := 'DRAFT';
2321 l_modifier_list_rec.user_status_id := ozf_utility_pvt.get_default_user_status('OZF_OFFER_STATUS', 'DRAFT');
2322
2323 --//Create Offer Call
2324 OZF_OFFER_PVT.process_modifiers
2325 ( p_init_msg_list => p_init_msg_list
2326 , p_api_version => p_api_version
2327 , p_commit => p_commit
2328 , x_return_status => x_return_status
2329 , x_msg_count => x_msg_count
2330 , x_msg_data => x_msg_data
2331 , p_offer_type => l_modifier_list_rec.offer_type
2332 , p_modifier_list_rec => l_modifier_list_rec
2333 , p_modifier_line_tbl => l_modifier_line_tbl
2334 , x_qp_list_header_id => x_qp_list_header_id
2335 , x_error_location => x_error_location
2336 );
2337 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
2338 RAISE Fnd_Api.g_exc_error;
2339 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2340 RAISE Fnd_Api.g_exc_unexpected_error;
2341 END IF;
2342 l_modifier_list_rec.qp_list_header_id := x_qp_list_header_id;
2343
2344 -- create lines without changing header
2345 IF p_modifier_line_tbl.COUNT > 0 THEN
2346 l_modifier_list_rec.offer_operation := fnd_api.g_miss_char;
2347 l_modifier_list_rec.modifier_operation := fnd_api.g_miss_char;
2348 l_modifier_list_rec.status_code := fnd_api.g_miss_char;
2349 l_modifier_list_rec.user_status_id := fnd_api.g_miss_num;
2350
2351 -- line values
2352 IF p_modifier_rec.offer_type = 'LUMPSUM' THEN
2353 l_lumpsum_distr_value := 100 / p_modifier_line_tbl.COUNT;
2354 l_lumpsum_distr_value := ROUND(l_lumpsum_distr_value,2);
2355
2356 FOR i IN p_modifier_line_tbl.FIRST..p_modifier_line_tbl.LAST LOOP
2357 --Last product need to be handle for even and odd both number of products
2358 IF i = p_modifier_line_tbl.LAST THEN
2359 l_lumpsum_distr_value := 100 - (l_lumpsum_distr_value * (p_modifier_line_tbl.COUNT - 1));
2360 END IF;
2361
2362 l_act_product_rec := NULL;
2363 l_act_product_rec.arc_act_product_used_by := 'OFFR';
2364 l_act_product_rec.act_product_used_by_id := x_qp_list_header_id;
2365 l_act_product_rec.level_type_code := 'PRODUCT';
2366 l_act_product_rec.inventory_item_id := p_modifier_line_tbl(i).product_attr_val;
2367 l_act_product_rec.organization_id := FND_PROFILE.value('QP_ORGANIZATION_ID');
2368 l_act_product_rec.line_lumpsum_qty := l_lumpsum_distr_value;
2369 l_act_product_rec.enabled_flag := 'N';
2370 l_act_product_rec.excluded_flag := 'N';
2371 l_act_product_rec.attribute1 := p_modifier_line_tbl(i).list_line_no;
2372
2373 OPEN c_uom(l_act_product_rec.inventory_item_id, fnd_profile.value('QP_ORGANIZATION_ID'));
2374 FETCH c_uom INTO l_uom;
2375 IF (c_uom%NOTFOUND) THEN
2376 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2377 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
2378 FND_MSG_PUB.add;
2379 END IF;
2380 RAISE Fnd_Api.g_exc_error;
2381 END IF;
2382 CLOSE c_uom;
2383 l_act_product_rec.uom_code := l_uom;
2384
2385 AMS_ActProduct_PVT.Create_Act_Product(
2386 p_api_version => p_api_version,
2387 p_init_msg_list => p_init_msg_list,
2388 p_commit => p_commit,
2389 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2390 x_return_status => x_return_status,
2391 x_msg_count => x_msg_count,
2392 x_msg_data => x_msg_data,
2393 p_act_Product_rec => l_act_product_rec,
2394 x_act_Product_id => l_act_product_id);
2395
2396 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
2397 RAISE Fnd_Api.g_exc_error;
2398 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2399 RAISE Fnd_Api.g_exc_unexpected_error;
2400 END IF;
2401 --//Fix for bug 13717016
2402 INSERT INTO ozf_xref_map ( map_attr_id
2403 , list_line_id
2404 , list_line_number
2405 , xref_line_id_value
2406 , xref_line_number_value
2407 )
2408 VALUES ( x_qp_list_header_id
2409 , l_act_product_id
2410 , l_act_product_id
2411 , TO_NUMBER(l_act_product_id||'.'||'2')
2412 , l_act_product_id
2413 );
2414
2415 x_line_tbl(i).list_line_id := TO_NUMBER(l_act_product_id||'.'||'2');
2416 x_line_tbl(i).list_line_number := l_act_product_id;
2417 END LOOP;
2418 ELSE -- ACCRUAL and OFF INVOICE
2419 FOR i IN p_modifier_line_tbl.FIRST..p_modifier_line_tbl.LAST LOOP
2420 l_line_index := l_line_index + 1;
2421 l_modifier_line_tbl(l_line_index).offer_line_type := NVL(p_modifier_line_tbl(i).offer_line_type,Fnd_Api.g_miss_char);
2422 l_modifier_line_tbl(l_line_index).operation := 'CREATE';
2423 l_modifier_line_tbl(l_line_index).list_line_id := NVL(p_modifier_line_tbl(i).list_line_id,Fnd_Api.g_miss_num);
2424 l_modifier_line_tbl(l_line_index).list_header_id := x_qp_list_header_id;
2425 l_modifier_line_tbl(l_line_index).list_line_type_code := NVL(p_modifier_line_tbl(i).list_line_type_code,'DIS');
2426 l_modifier_line_tbl(l_line_index).operand := NVL(p_modifier_line_tbl(i).operand,Fnd_Api.g_miss_num);
2427 l_modifier_line_tbl(l_line_index).start_date_active := NVL(p_modifier_line_tbl(i).start_date_active,Fnd_Api.g_miss_date) ;
2428 l_modifier_line_tbl(l_line_index).end_date_active := NVL(p_modifier_line_tbl(i).end_date_active,Fnd_Api.g_miss_date) ;
2429 l_modifier_line_tbl(l_line_index).arithmetic_operator := NVL(p_modifier_line_tbl(i).arithmetic_operator,Fnd_Api.g_miss_char);
2430 l_modifier_line_tbl(l_line_index).inactive_flag := NVL(p_modifier_line_tbl(i).active_flag,'Y');
2431 l_modifier_line_tbl(l_line_index).qd_operand := NVL(p_modifier_line_tbl(i).qd_operand,Fnd_Api.g_miss_num);
2432 l_modifier_line_tbl(l_line_index).qd_arithmetic_operator := NVL(p_modifier_line_tbl(i).qd_arithmetic_operator,Fnd_Api.g_miss_char);
2433 l_modifier_line_tbl(l_line_index).qd_related_deal_lines_id := NVL(p_modifier_line_tbl(i).qd_related_deal_lines_id,Fnd_Api.g_miss_num);
2434 l_modifier_line_tbl(l_line_index).qd_object_version_number := NVL(p_modifier_line_tbl(i).qd_object_version_number,Fnd_Api.g_miss_num);
2435 l_modifier_line_tbl(l_line_index).qd_estimated_qty_is_max := NVL(p_modifier_line_tbl(i).qd_estimated_qty_is_max,Fnd_Api.g_miss_char);
2436 l_modifier_line_tbl(l_line_index).qd_list_line_id := NVL(p_modifier_line_tbl(i).qd_list_line_id,Fnd_Api.g_miss_num);
2437 l_modifier_line_tbl(l_line_index).qd_estimated_amount_is_max := NVL(p_modifier_line_tbl(i).qd_estimated_amount_is_max,Fnd_Api.g_miss_char);
2438 l_modifier_line_tbl(l_line_index).estim_gl_value := NVL(p_modifier_line_tbl(i).estim_gl_value,Fnd_Api.g_miss_num);
2439 l_modifier_line_tbl(l_line_index).benefit_price_list_line_id := NVL(p_modifier_line_tbl(i).benefit_price_list_line_id,Fnd_Api.g_miss_num);
2440 l_modifier_line_tbl(l_line_index).benefit_limit := NVL(p_modifier_line_tbl(i).benefit_limit,Fnd_Api.g_miss_num) ;
2441 l_modifier_line_tbl(l_line_index).benefit_qty := NVL(p_modifier_line_tbl(i).benefit_qty,Fnd_Api.g_miss_num) ;
2442 l_modifier_line_tbl(l_line_index).benefit_uom_code := NVL(p_modifier_line_tbl(i).benefit_uom_code,Fnd_Api.g_miss_char);
2443 l_modifier_line_tbl(l_line_index).substitution_context := NVL(p_modifier_line_tbl(i).substitution_context,Fnd_Api.g_miss_char);
2444 l_modifier_line_tbl(l_line_index).substitution_attr := NVL(p_modifier_line_tbl(i).substitution_attr,Fnd_Api.g_miss_char) ;
2445 l_modifier_line_tbl(l_line_index).substitution_val := NVL(p_modifier_line_tbl(i).substitution_val,Fnd_Api.g_miss_char);
2446 l_modifier_line_tbl(l_line_index).price_break_type_code := NVL(p_modifier_line_tbl(i).price_break_type_code,'POINT');
2447 l_modifier_line_tbl(l_line_index).pricing_attribute_id := NVL(p_modifier_line_tbl(i).pricing_attribute_id,Fnd_Api.g_miss_num);
2448 l_modifier_line_tbl(l_line_index).product_attribute_context := NVL(p_modifier_line_tbl(i).product_attribute_context,'ITEM');
2449 l_modifier_line_tbl(l_line_index).product_attr := NVL(p_modifier_line_tbl(i).product_attr,'PRICING_ATTRIBUTE1'); -- item only
2450 l_modifier_line_tbl(l_line_index).product_attr_val := NVL(p_modifier_line_tbl(i).product_attr_val,Fnd_Api.g_miss_char);
2451 l_modifier_line_tbl(l_line_index).product_uom_code := NVL(p_modifier_line_tbl(i).product_uom_code,Fnd_Api.g_miss_char);
2452 l_modifier_line_tbl(l_line_index).pricing_attribute_context := NVL(p_modifier_line_tbl(i).pricing_attribute_context,'VOLUME');
2453 l_modifier_line_tbl(l_line_index).pricing_attr := NVL(p_modifier_line_tbl(i).pricing_attr,Fnd_Api.g_miss_char);
2454 l_modifier_line_tbl(l_line_index).pricing_attr_value_from := NVL(p_modifier_line_tbl(i).pricing_attr_value_from,1);
2455 l_modifier_line_tbl(l_line_index).pricing_attr_value_to := NVL(p_modifier_line_tbl(i).pricing_attr_value_to,Fnd_Api.g_miss_char) ;
2456 l_modifier_line_tbl(l_line_index).excluder_flag := NVL(p_modifier_line_tbl(i).excluder_flag,'N');
2457 l_modifier_line_tbl(l_line_index).order_value_from := NVL(p_modifier_line_tbl(i).order_value_from,Fnd_Api.g_miss_char);
2458 l_modifier_line_tbl(l_line_index).order_value_to := NVL(p_modifier_line_tbl(i).order_value_to,Fnd_Api.g_miss_char);
2459 l_modifier_line_tbl(l_line_index).qualifier_id := NVL(p_modifier_line_tbl(i).qualifier_id,Fnd_Api.g_miss_num);
2460 l_modifier_line_tbl(l_line_index).comments := NVL(p_modifier_line_tbl(i).comments,Fnd_Api.g_miss_char);
2461 l_modifier_line_tbl(l_line_index).context := NVL(p_modifier_line_tbl(i).context,Fnd_Api.g_miss_char) ;
2462 l_modifier_line_tbl(l_line_index).attribute1 := NVL(p_modifier_line_tbl(i).attribute1,Fnd_Api.g_miss_char);
2463 l_modifier_line_tbl(l_line_index).attribute2 := NVL(p_modifier_line_tbl(i).attribute2,Fnd_Api.g_miss_char);
2464 l_modifier_line_tbl(l_line_index).attribute3 := NVL(p_modifier_line_tbl(i).attribute3,Fnd_Api.g_miss_char);
2465 l_modifier_line_tbl(l_line_index).attribute4 := NVL(p_modifier_line_tbl(i).attribute4,Fnd_Api.g_miss_char);
2466 l_modifier_line_tbl(l_line_index).attribute5 := NVL(p_modifier_line_tbl(i).attribute5,Fnd_Api.g_miss_char);
2467 l_modifier_line_tbl(l_line_index).attribute6 := NVL(p_modifier_line_tbl(i).attribute6,Fnd_Api.g_miss_char);
2468 l_modifier_line_tbl(l_line_index).attribute7 := NVL(p_modifier_line_tbl(i).attribute7,Fnd_Api.g_miss_char);
2469 l_modifier_line_tbl(l_line_index).attribute8 := NVL(p_modifier_line_tbl(i).attribute8,Fnd_Api.g_miss_char);
2470 l_modifier_line_tbl(l_line_index).attribute9 := NVL(p_modifier_line_tbl(i).attribute9,Fnd_Api.g_miss_char);
2471 l_modifier_line_tbl(l_line_index).attribute10 := NVL(p_modifier_line_tbl(i).attribute10,Fnd_Api.g_miss_char);
2472 l_modifier_line_tbl(l_line_index).attribute11 := NVL(p_modifier_line_tbl(i).attribute11,Fnd_Api.g_miss_char);
2473 l_modifier_line_tbl(l_line_index).attribute12 := NVL(p_modifier_line_tbl(i).attribute12,Fnd_Api.g_miss_char);
2474 l_modifier_line_tbl(l_line_index).attribute13 := NVL(p_modifier_line_tbl(i).attribute13,Fnd_Api.g_miss_char);
2475 l_modifier_line_tbl(l_line_index).attribute14 := NVL(p_modifier_line_tbl(i).attribute14,Fnd_Api.g_miss_char);
2476 l_modifier_line_tbl(l_line_index).attribute15 := NVL(p_modifier_line_tbl(i).attribute15,Fnd_Api.g_miss_char);
2477 l_modifier_line_tbl(l_line_index).max_qty_per_order := NVL(p_modifier_line_tbl(i).max_qty_per_order,Fnd_Api.g_miss_num);
2478 l_modifier_line_tbl(l_line_index).max_qty_per_order_id := NVL(p_modifier_line_tbl(i).max_qty_per_order_id,Fnd_Api.g_miss_num);
2479 l_modifier_line_tbl(l_line_index).max_qty_per_customer := NVL(p_modifier_line_tbl(i).max_qty_per_customer,Fnd_Api.g_miss_num);
2480 l_modifier_line_tbl(l_line_index).max_qty_per_customer_id := NVL(p_modifier_line_tbl(i).max_qty_per_customer_id,Fnd_Api.g_miss_num);
2481 l_modifier_line_tbl(l_line_index).max_qty_per_rule := NVL(p_modifier_line_tbl(i).max_qty_per_rule,Fnd_Api.g_miss_num);
2482 l_modifier_line_tbl(l_line_index).max_qty_per_rule_id := NVL(p_modifier_line_tbl(i).max_qty_per_rule_id,Fnd_Api.g_miss_num);
2483 l_modifier_line_tbl(l_line_index).max_orders_per_customer := NVL(p_modifier_line_tbl(i).max_orders_per_customer,Fnd_Api.g_miss_num);
2484 l_modifier_line_tbl(l_line_index).max_orders_per_customer_id := NVL(p_modifier_line_tbl(i).max_orders_per_customer_id,Fnd_Api.g_miss_num);
2485 l_modifier_line_tbl(l_line_index).max_amount_per_rule := NVL(p_modifier_line_tbl(i).max_amount_per_rule,Fnd_Api.g_miss_num);
2486 l_modifier_line_tbl(l_line_index).max_amount_per_rule_id := NVL(p_modifier_line_tbl(i).max_amount_per_rule_id,Fnd_Api.g_miss_num);
2487 l_modifier_line_tbl(l_line_index).estimate_qty_uom := NVL(p_modifier_line_tbl(i).estimate_qty_uom,Fnd_Api.g_miss_char);
2488 l_modifier_line_tbl(l_line_index).generate_using_formula_id := NVL(p_modifier_line_tbl(i).generate_using_formula_id,Fnd_Api.g_miss_num);
2489 l_modifier_line_tbl(l_line_index).price_by_formula_id := NVL(p_modifier_line_tbl(i).price_by_formula_id,Fnd_Api.g_miss_num);
2490 l_modifier_line_tbl(l_line_index).generate_using_formula := NVL(p_modifier_line_tbl(i).generate_using_formula,Fnd_Api.g_miss_char);
2491 l_modifier_line_tbl(l_line_index).price_by_formula := NVL(p_modifier_line_tbl(i).price_by_formula,Fnd_Api.g_miss_char);
2492 l_modifier_line_tbl(l_line_index).list_line_no := NVL(p_modifier_line_tbl(i).list_line_no,Fnd_Api.g_miss_char);
2493
2494 OPEN c_uom ( l_modifier_line_tbl(l_line_index).product_attr_val
2495 ,fnd_profile.value('QP_ORGANIZATION_ID')
2496 );
2497 FETCH c_uom INTO l_uom;
2498 IF ( c_uom%NOTFOUND) THEN
2499 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2500 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
2501 FND_MSG_PUB.add;
2502 END IF;
2503 RAISE Fnd_Api.g_exc_error;
2504 END IF;
2505 CLOSE c_uom;
2506
2507 l_modifier_line_tbl(l_line_index).product_uom_code := l_uom;
2508 END LOOP;
2509 Ozf_Offer_Pvt.process_modifiers
2510 ( p_init_msg_list => p_init_msg_list
2511 , p_api_version => p_api_version
2512 , p_commit => p_commit
2513 , x_return_status => x_return_status
2514 , x_msg_count => x_msg_count
2515 , x_msg_data => x_msg_data
2516 , p_offer_type => l_modifier_list_rec.offer_type
2517 , p_modifier_list_rec => l_modifier_list_rec
2518 , p_modifier_line_tbl => l_modifier_line_tbl
2519 , x_qp_list_header_id => l_dummy
2520 , x_error_location => x_error_location
2521 );
2522
2523 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
2524 RAISE Fnd_Api.g_exc_error;
2525 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2526 RAISE Fnd_Api.g_exc_unexpected_error;
2527 END IF;
2528 --//Fix for bug 13717016
2529 FOR list_line_rec IN c_list_line(x_qp_list_header_id) LOOP
2530 x_line_tbl(k).list_line_number := list_line_rec.list_line_no;
2531 x_line_tbl(k).list_line_id := TO_NUMBER(list_line_rec.list_line_id||'.'||'1');
2532
2533 INSERT INTO ozf_xref_map ( map_attr_id
2534 , list_line_id
2535 , list_line_number
2536 , xref_line_id_value
2537 , xref_line_number_value
2538 )
2539 VALUES ( x_qp_list_header_id
2540 , list_line_rec.list_line_id
2541 , list_line_rec.list_line_no
2542 , x_line_tbl(k).list_line_id
2543 , x_line_tbl(k).list_line_number
2544 );
2545 k := k+1;
2546 END LOOP;
2547 END IF; -- end offer_type
2548 END IF; -- end p_modifier_line_tbl.count
2549 ELSE
2550 x_qp_list_header_id := p_modifier_rec.qp_list_header_id;
2551 l_modifier_list_rec.qp_list_header_id := p_modifier_rec.qp_list_header_id;
2552 END IF;
2553
2554 OPEN c_offer_detail(l_modifier_list_rec.qp_list_header_id);
2555 FETCH c_offer_detail INTO l_modifier_list_rec.offer_id
2556 ,l_modifier_list_rec.object_version_number;
2557 CLOSE c_offer_detail;
2558
2559 l_modifier_list_rec.offer_operation := 'UPDATE';
2560 l_modifier_list_rec.modifier_operation := 'UPDATE';
2561 l_modifier_list_rec.status_code := p_modifier_rec.status_code;
2562 l_modifier_list_rec.user_status_id := OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS',p_modifier_rec.status_code);
2563 l_modifier_list_rec.end_date_active := p_modifier_rec.end_date_active;
2564
2565 l_modifier_line_tbl.DELETE;
2566
2567 Ozf_Offer_Pvt.process_modifiers
2568 ( p_init_msg_list => p_init_msg_list
2569 , p_api_version => p_api_version
2570 , p_commit => p_commit
2571 , x_return_status => x_return_status
2572 , x_msg_count => x_msg_count
2573 , x_msg_data => x_msg_data
2574 , p_offer_type => l_modifier_list_rec.offer_type
2575 , p_modifier_list_rec => l_modifier_list_rec
2576 , p_modifier_line_tbl => l_modifier_line_tbl
2577 , x_qp_list_header_id => l_dummy
2578 , x_error_location => x_error_location
2579 );
2580
2581 IF x_return_status = Fnd_Api.g_ret_sts_error THEN
2582 RAISE Fnd_Api.g_exc_error;
2583 ELSIF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2584 RAISE Fnd_Api.g_exc_unexpected_error;
2585 END IF;
2586
2587 --// 13791027
2588 IF l_modifier_list_rec.OFFER_TYPE = 'LUMPSUM' THEN
2589 FOR r_lump_line IN c_lump_line(x_qp_list_header_id)
2590 LOOP
2591 x_line_tbl(k).list_line_id := r_lump_line.activity_product_id;
2592 x_line_tbl(k).list_line_number := r_lump_line.attribute1;
2593 k := k+1;
2594 END LOOP;
2595 ELSE
2596 FOR r_lump_line IN c_list_line(x_qp_list_header_id)
2597 LOOP
2598 x_line_tbl(k).list_line_number := r_lump_line.list_line_no;
2599 x_line_tbl(k).list_line_id := r_lump_line.list_line_id;
2600 k := k+1;
2601 END LOOP;
2602 END IF;
2603
2604
2605 FND_MSG_PUB.Count_And_Get (
2606 p_encoded => FND_API.G_FALSE,
2607 p_count => x_msg_count,
2608 p_data => x_msg_data
2609 );
2610
2611 x_msg_tbl(1).msg_text :='successful';
2612
2613 FOR i IN 1..x_msg_count LOOP
2614 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
2615 END LOOP;
2616
2617 IF p_commit = Fnd_Api.g_true THEN
2618 COMMIT WORK;
2619 END IF;
2620
2621 EXCEPTION
2622 WHEN FND_API.G_EXC_ERROR THEN
2623 ROLLBACK TO process_offer_from_adapter;
2624 x_return_status := FND_API.G_RET_STS_ERROR;
2625 -- Standard call to get message count and if count=1, get the message
2626 FND_MSG_PUB.Count_And_Get (
2627 p_encoded => FND_API.G_FALSE,
2628 p_count => x_msg_count,
2629 p_data => x_msg_data
2630 );
2631
2632 for i in 1..x_msg_count loop
2633 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
2634 end loop;
2635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2636 ROLLBACK TO process_offer_from_adapter;
2637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2638 -- Standard call to get message count and if count=1, get the message
2639 FND_MSG_PUB.Count_And_Get (
2640 p_encoded => FND_API.G_FALSE,
2641 p_count => x_msg_count,
2642 p_data => x_msg_data
2643 );
2644 for i in 1..x_msg_count loop
2645 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
2646 end loop;
2647 WHEN OTHERS THEN
2648 ROLLBACK TO process_offer_from_adapter;
2649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2650 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2651 THEN
2652 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2653 END IF;
2654 -- Standard call to get message count and if count=1, get the message
2655 FND_MSG_PUB.Count_And_Get (
2656 p_encoded => FND_API.G_FALSE,
2657 p_count => x_msg_count,
2658 p_data => x_msg_data
2659 );
2660 for i in 1..x_msg_count loop
2661 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
2662 end loop;
2663
2664 END process_offer_from_adapter;
2665
2666 END OZF_Offer_PUB;