[Home] [Help]
PACKAGE BODY: APPS.OZF_PRICELIST_PVT
Source
1 PACKAGE BODY OZF_PRICELIST_PVT AS
2 /* $Header: ozfvpltb.pls 120.5 2006/01/18 20:12:42 julou ship $ */
3
4 -- start of comment
5 -- History
6 -- 19-MAY-2001 julou modified
7 -- 1. added primary_uom_flag
8 -- 2. allow change when price list is actived
9 -- 3. when price list goes active, no call to move_segments
10 -- 4. commented out reference to object_attributes
11 -- 17-AUG-2001 julou modified
12 -- added approval for status change
13 -- 21-AUG-2001 julou modified
14 -- added calls to ams_access_pvt.create_access and
15 -- ams_access_pvt.update_object_owner.
16 -- 01-Nov-2001 rssharma modified
17 -- passing list_price , along with operand
18 -- 22-Oct-2002 RSSHARMA Added Function to get Currency Header Name
19 -- Added Currency_header_id for multi currency support
20 -- 20-JAN-2003 julou fixed approval redirect issue.
21 -- the g_miss_num is parsed in as owner_id and access table are
22 -- updated with g_miss_num. solution: populate orig owner_id as
23 -- owner_id(if it is g_miss_num) before check owner change.
24 -- FEB-05-2003 julou bug 2784394 - moved WF after update price list
25 -- Fri Jan 09 2004:2/41 PM RSSHARMA Fixed bug # 3358056. Changed WOrkflow approval process from OZFGAPP to AMSGAPP
26 -- Tue Apr 13 2004:5/39 PM RSSHARMA Fixed bug # 3384634. Added Product Pricing Validation to make List header Id(Price List)
27 -- mandatory
28 -- Tue Jun 08 2004:3/30 PM RSSHARMA Fixed Category LOV issue. in get_Product_name function, if category belongs to
29 -- inventory categories then get concat_segments as before for backword compatibility, else if category belongs to unified
30 -- product catelog get the category description as the parentage is not correct.
31 -- end of comment
32
33 FUNCTION get_currency_header_name
34 ( p_currency_header_id IN NUMBER
35 ) RETURN VARCHAR2
36 IS
37 l_currency_name VARCHAR2(250);
38
39 CURSOR l_curr_currency_name(l_currency_header_id NUMBER) IS
40 SELECT name FROM qp_currency_lists_tl
41 WHERE currency_header_id = l_currency_header_id AND language = userenv('lang') ;
42
43 BEGIN
44
45 OPEN l_curr_currency_name(p_currency_header_id) ;
46 FETCH l_curr_currency_name INTO l_currency_name ;
47 CLOSE l_curr_currency_name ;
48 RETURN l_currency_name ;
49
50 EXCEPTION
51 WHEN OTHERS THEN
52 RETURN '';
53 END;
54
55 PROCEDURE process_price_list_attributes( p_price_list_attr_rec IN OZF_PRICE_LIST_PVT.OZF_PRICE_LIST_Rec_Type,
56 p_operation IN VARCHAR2,
57 x_return_status OUT NOCOPY VARCHAR2)
58 IS
59 l_msg_data varchar2(2000);
60 l_msg_count number;
61 l_return_status varchar2(1) := FND_API.g_ret_sts_success;
62 l_price_list_attribute_id NUMBER;
63 l_object_version_number NUMBER;
64
65 l_custom_setup_id NUMBER;
66
67 CURSOR c_custom_setup IS
68 SELECT custom_setup_id
69 FROM ams_custom_setups_vl
70 WHERE object_type = 'PRIC';
71
72 BEGIN
73 IF p_operation = QP_GLOBALS.G_OPR_CREATE THEN
74 OZF_PRICE_LIST_PVT.create_price_list(p_api_version_number => 1
75 ,p_init_msg_list => FND_API.G_FALSE
76 ,p_commit => FND_API.G_FALSE
77 ,x_return_status => l_return_status
78 ,x_msg_count => l_msg_count
79 ,x_msg_data => l_msg_data
80 ,p_ozf_PRICE_LIST_rec => p_price_list_attr_rec
81 ,x_price_list_attribute_id => l_price_list_attribute_id);
82
83 OPEN c_custom_setup;
84 FETCH c_custom_setup INTO l_custom_setup_id;
85 CLOSE c_custom_setup;
86 -- following code is removed by julou 19-MAY-2001.
87 /*
88 -- create attributes for this offer
89 IF l_custom_setup_id IS NOT NULL THEN
90 AMS_ObjectAttribute_PVT.create_object_attributes(
91 p_api_version => 1.0,
92 p_init_msg_list => FND_API.g_false,
93 p_commit => FND_API.g_false,
94 p_validation_level => FND_API.g_valid_level_full,
95 x_return_status => l_return_status,
96 x_msg_count => l_msg_count,
97 x_msg_data => l_msg_data,
98 p_object_type => 'PRIC',
99 p_object_id => p_price_list_attr_rec.qp_list_header_id,
100 p_setup_id => l_custom_setup_id
101 );
102 END IF;
103 */
104 -- end of comment
105
106 ELSIF p_operation = QP_GLOBALS.G_OPR_UPDATE THEN
107 OZF_PRICE_LIST_PVT.update_price_list(p_api_version_number => 1
108 ,p_init_msg_list => FND_API.G_FALSE
109 ,p_commit => FND_API.G_FALSE
110 ,x_return_status => l_return_status
111 ,x_msg_count => l_msg_count
112 ,x_msg_data => l_msg_data
113 ,p_ozf_PRICE_LIST_rec => p_price_list_attr_rec
114 ,x_object_version_number => l_object_version_number);
115
116
117 ELSIF p_operation = QP_GLOBALS.G_OPR_DELETE THEN
118
119 OZF_PRICE_LIST_PVT.delete_price_list( p_api_version_number => 1
120 ,p_init_msg_list => FND_API.G_FALSE
121 ,p_commit => FND_API.G_FALSE
122 ,x_return_status => l_return_status
123 ,x_msg_count => l_msg_count
124 ,x_msg_data => l_msg_data
125 ,p_price_list_attribute_id => p_price_list_attr_rec.price_list_attribute_id
126 ,p_object_version_number => p_price_list_attr_rec.object_version_number );
127
128 END IF;
129
130 x_return_status := l_return_status;
131
132 END;
133
134 PROCEDURE validate_price_list(
135 p_price_list_line_rec IN Price_List_Line_rec_Type
136 )
137 IS
138 BEGIN
139 IF p_price_list_line_rec.list_header_id IS NULL OR p_price_list_line_rec.list_header_id = FND_API.G_MISS_NUM THEN
140 FND_MESSAGE.set_name('OZF', 'OZF_NO_PRIC_LIST');
141 FND_MSG_PUB.add;
142 RAISE FND_API.g_exc_error;
143 END IF;
144 END validate_price_list;
145
146 PROCEDURE process_price_list(
147 p_api_version IN NUMBER,
148 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
149 p_commit IN VARCHAR2 := FND_API.g_false,
150 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2,
154 p_price_list_rec IN ozf_price_list_rec_type,
155 p_price_list_line_tbl IN price_list_line_tbl_type,
156 p_pricing_attr_tbl IN pricing_attr_tbl_type,
157 p_qualifiers_tbl IN QUALIFIERS_TBL_TYPE,
158 x_list_header_id OUT NOCOPY NUMBER,
159 x_error_source OUT NOCOPY VARCHAR2,
160 x_error_location OUT NOCOPY NUMBER
161 )
162 IS
163 l_msg_data varchar2(2000);
164 l_msg_count number;
165 l_return_status varchar2(1) := FND_API.g_ret_sts_success;
166 l_api_name constant varchar2(30) := 'process_price_list';
167 i number := 1;
168 j number := 1;
169 l_list_header_id number;
170 l_api_version CONSTANT NUMBER := 1.0;
171
172 l_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
173 temp_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
174 l_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
175 l_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
176 l_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
177 l_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
178 l_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
179 l_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
180 l_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
181 v_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
182 v_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
183 v_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
184 v_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
185 v_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
186 v_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
187 v_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
188 v_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
189 l_ozf_price_list_attr_rec OZF_PRICE_LIST_PVT.OZF_PRICE_LIST_Rec_Type;
190 l_access_rec ams_access_PVT.access_rec_type;
191 l_jtf_note_id NUMBER;
192 l_lines_delete VARCHAR2(1) := 'N';
193 l_access_id NUMBER;
194
195 CURSOR cur_get_status_code(p_list_header_id NUMBER) IS
196 SELECT status_code
197 FROM OZF_price_lists_v
198 WHERE list_header_id = p_list_header_id;
199
200 CURSOR cur_get_system_status_code(p_user_status_id NUMBER) IS
201 SELECT system_status_code
202 FROM ams_user_statuses_vl
203 WHERE user_status_id = p_user_status_id;
204
205 CURSOR cur_get_user_status_id(l_status_code VARCHAR2) IS
206 SELECT user_status_id
207 FROM ams_user_statuses_vl
208 WHERE system_status_type = 'OZF_PRICELIST_STATUS'
209 AND system_status_code = l_status_code
210 AND TRUNC(sysdate) BETWEEN NVL(start_date_active, TRUNC(SYSDATE)) and NVL(end_date_active, TRUNC(sysdate))
211 AND default_flag = 'Y'
212 AND enabled_flag = 'Y';
213
214 CURSOR c_get_old_status_id(l_id VARCHAR2) IS
215 SELECT user_status_id,custom_setup_id,owner_id
216 FROM OZF_price_list_attributes
217 WHERE qp_list_header_id = l_id;
218
219 l_resource_id NUMBER := OZF_UTILITY_PVT.get_resource_id(FND_GLOBAL.user_id);
220
221 l_new_status_code VARCHAR2(30);
222 l_existing_status_code VARCHAR2(30);
223 l_old_status_id NUMBER;
224 l_approval_type VARCHAR2(30) := NULL;
225 l_user_status_id NUMBER;
226 l_custom_setup_id NUMBER;
227 l_old_owner_id NUMBER;
228 l_owner_id NUMBER;
229 l_is_owner VARCHAR2(1);
230 l_login_is_owner VARCHAR2(1);
231 l_is_admin BOOLEAN;
232 BEGIN
233
234 SAVEPOINT process_price_list;
235
236 IF FND_API.to_boolean(p_init_msg_list) THEN
237 FND_MSG_PUB.initialize;
238 END IF;
239
240 IF NOT FND_API.compatible_api_call
241 (
242 l_api_version,
243 p_api_version,
244 l_api_name,
245 g_pkg_name
246 )
247 THEN
248 RAISE FND_API.g_exc_unexpected_error;
249 END IF;
250
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252
253
254 -- Commented to allow product association to Price Lists Created in QP
255 /* IF p_price_list_rec.operation <> QP_GLOBALS.G_OPR_CREATE THEN
256 IF AMS_ACCESS_PVT.check_update_access(p_price_list_rec.list_header_id, 'PRIC',l_resource_id,'USER') NOT IN ('F','R') THEN
257 FND_MESSAGE.SET_NAME('OZF','OZF_EVO_NO_UPDATE_ACCESS');
258 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
259 FND_MSG_PUB.ADD;
260 x_error_source := 'H';
261 RAISE FND_API.g_exc_error;
262 END IF;
263 END IF; */
264
265 OPEN cur_get_status_code(p_price_list_rec.list_header_id);
266 FETCH cur_get_status_code INTO l_existing_status_code;
267 CLOSE cur_get_status_code;
268
269 IF l_existing_status_code IN ('CANCELLED') THEN
270 FND_MESSAGE.SET_NAME('OZF','OZF_PRIC_NO_CHANGES_ALLOWED');
271 FND_MSG_PUB.ADD;
272 x_error_source := 'H';
273 RAISE FND_API.g_exc_error;
274 END IF;
275
276
277
278 IF p_price_list_rec.operation <> FND_API.G_MISS_CHAR THEN
279
280 l_price_list_rec.name := p_price_list_rec.name;
281 l_price_list_rec.description := p_price_list_rec.description;
282 l_price_list_rec.currency_code := p_price_list_rec.currency_code;
283 l_price_list_rec.operation := p_price_list_rec.operation;
284 l_price_list_rec.start_date_active := p_price_list_rec.start_date_active;
285 l_price_list_rec.end_date_active := p_price_list_rec.end_date_active;
286 l_price_list_rec.currency_header_id := p_price_list_rec.currency_header_id;
287
288 IF NVL(fnd_profile.value('QP_SECURITY_CONTROL'), 'OFF') = 'ON' THEN
289 l_price_list_rec.global_flag := p_price_list_rec.global_flag;
290 ELSE
291 l_price_list_rec.global_flag := 'Y';
292 END IF;
293
294 IF l_price_list_rec.global_flag = 'Y' THEN
295 IF p_price_list_rec.org_id IS NOT NULL AND p_price_list_rec.org_id <> fnd_api.g_miss_num THEN
296 FND_MESSAGE.set_name('OZF', 'OZF_CLEAR_OU');
297 FND_MSG_PUB.add;
298 RAISE FND_API.g_exc_error;
299 ELSE
300 l_price_list_rec.org_id := NULL; -- global offer does need org id
301 END IF;
302 ELSE -- local offer
303 l_price_list_rec.org_id := p_price_list_rec.org_id;
304 END IF;
305
306 -- julou bug 3863693: expose flex field
307 l_price_list_rec.context := p_price_list_rec.context;
308 l_price_list_rec.attribute1 := p_price_list_rec.attribute1;
309 l_price_list_rec.attribute2 := p_price_list_rec.attribute2;
310 l_price_list_rec.attribute3 := p_price_list_rec.attribute3;
311 l_price_list_rec.attribute4 := p_price_list_rec.attribute4;
312 l_price_list_rec.attribute5 := p_price_list_rec.attribute5;
313 l_price_list_rec.attribute6 := p_price_list_rec.attribute6;
314 l_price_list_rec.attribute7 := p_price_list_rec.attribute7;
315 l_price_list_rec.attribute8 := p_price_list_rec.attribute8;
316 l_price_list_rec.attribute9 := p_price_list_rec.attribute9;
317 l_price_list_rec.attribute10 := p_price_list_rec.attribute10;
318 l_price_list_rec.attribute11 := p_price_list_rec.attribute11;
319 l_price_list_rec.attribute12 := p_price_list_rec.attribute12;
323 -- julou end
320 l_price_list_rec.attribute13 := p_price_list_rec.attribute13;
321 l_price_list_rec.attribute14 := p_price_list_rec.attribute14;
322 l_price_list_rec.attribute15 := p_price_list_rec.attribute15;
324
325 -- added by julou 07-16-2002 bug 2452540
326 -- validate dates here instead of validating in QP.
327 -- QP checks twice and hence returns 2 error messages
328 IF l_price_list_rec.start_date_active IS NOT NULL
329 AND l_price_list_rec.start_date_active <> FND_API.G_MISS_DATE
330 AND l_price_list_rec.end_date_active IS NOT NULL
331 AND l_price_list_rec.end_date_active <> FND_API.G_MISS_DATE
332 THEN
333 IF l_price_list_rec.start_date_active > l_price_list_rec.end_date_active THEN
334 FND_MESSAGE.set_name('OZF', 'OZF_PRIC_START_AFTER_END');
335 FND_MSG_PUB.add;
336 RAISE FND_API.g_exc_error;
337 END IF;
338 END IF;
339 -- ended 2452540
340
341 IF p_price_list_rec.list_header_id IS NOT NULL AND p_price_list_rec.list_header_id <> fnd_api.g_miss_num THEN
342 OPEN c_get_old_status_id(p_price_list_rec.list_header_id);
343 FETCH c_get_old_status_id INTO l_old_status_id,l_custom_setup_id,l_old_owner_id;
344 CLOSE c_get_old_status_id;
345
346 -- check if approval is required
347 IF p_price_list_rec.user_status_id IS NOT NULL AND p_price_list_rec.user_status_id <> FND_API.G_MISS_NUM THEN
348 -- bug 3780070 exception when updating price list created from QP (QP price lists have no status code in OZF)
349 OZF_Utility_PVT.check_new_status_change(p_object_type => 'PRIC'
350 ,p_object_id => p_price_list_rec.list_header_id
351 ,p_old_status_id => l_old_status_id
352 ,p_new_status_id => p_price_list_rec.user_status_id
353 ,p_custom_setup_id => l_custom_setup_id
354 ,x_approval_type => l_approval_type
355 ,x_return_status => l_return_status);
356
357 OPEN cur_get_system_status_code(p_price_list_rec.user_status_id);
358 FETCH cur_get_system_status_code INTO l_new_status_code;
359 CLOSE cur_get_system_status_code;
360
361 IF l_new_status_code = 'ACTIVE' THEN
362 IF l_approval_type IS NULL THEN -- no approval needed
363 l_price_list_rec.active_flag := 'Y';
364 END IF;
365 END IF;
366 END IF; -- end bug 3780070 exception when updating price list created from QP
367 END IF;
368
369 /****************Uncomment once upgraded to R3 LEVEL **********************/
370
371 IF p_price_list_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
372 l_price_list_rec.list_type_code := 'PRL';
373 l_price_list_rec.active_flag := 'N';
374 l_price_list_rec.list_header_id := FND_API.G_MISS_NUM;
375 ELSE
376 l_price_list_rec.list_header_id := p_price_list_rec.list_header_id;
377 END IF;
378 END IF;
379
380 IF p_price_list_line_tbl.count > 0 THEN
381 FOR i in p_price_list_line_tbl.first..p_price_list_line_tbl.last LOOP
382
383 IF p_price_list_line_tbl.exists(i) AND p_price_list_line_tbl(i).operation <> FND_API.G_MISS_CHAR THEN
384 -- bug 2452540
385 IF p_price_list_line_tbl(i).start_date_active IS NOT NULL
386 AND p_price_list_line_tbl(i).start_date_active <> FND_API.G_MISS_DATE
387 AND p_price_list_line_tbl(i).end_date_active IS NOT NULL
388 AND p_price_list_line_tbl(i).end_date_active <> FND_API.G_MISS_DATE
389 THEN
390 IF p_price_list_line_tbl(i).start_date_active > p_price_list_line_tbl(i).end_date_active THEN
391 FND_MESSAGE.set_name('OZF', 'OZF_PRIC_START_AFTER_END');
392 FND_MSG_PUB.add;
393 RAISE FND_API.g_exc_error;
394 END IF;
395 END IF;
396 -- end bug 2452540
397 validate_price_list(p_price_list_line_rec =>p_price_list_line_tbl(i));
398
402 -- julou bug 3863693 expose flex field
399 l_price_list_line_tbl(i).list_line_id := p_price_list_line_tbl(i).list_line_id;
400 l_price_list_line_tbl(i).list_header_id := p_price_list_line_tbl(i).list_header_id;
401
403 l_price_list_line_tbl(i).context := p_price_list_line_tbl(i).context;
404 l_price_list_line_tbl(i).attribute1 := p_price_list_line_tbl(i).attribute1;
405 l_price_list_line_tbl(i).attribute2 := p_price_list_line_tbl(i).attribute2;
406 l_price_list_line_tbl(i).attribute3 := p_price_list_line_tbl(i).attribute3;
407 l_price_list_line_tbl(i).attribute4 := p_price_list_line_tbl(i).attribute4;
408 l_price_list_line_tbl(i).attribute5 := p_price_list_line_tbl(i).attribute5;
409 l_price_list_line_tbl(i).attribute6 := p_price_list_line_tbl(i).attribute6;
410 l_price_list_line_tbl(i).attribute7 := p_price_list_line_tbl(i).attribute7;
411 l_price_list_line_tbl(i).attribute8 := p_price_list_line_tbl(i).attribute8;
412 l_price_list_line_tbl(i).attribute9 := p_price_list_line_tbl(i).attribute9;
413 l_price_list_line_tbl(i).attribute10 := p_price_list_line_tbl(i).attribute10;
414 l_price_list_line_tbl(i).attribute11 := p_price_list_line_tbl(i).attribute11;
415 l_price_list_line_tbl(i).attribute12 := p_price_list_line_tbl(i).attribute12;
416 l_price_list_line_tbl(i).attribute13 := p_price_list_line_tbl(i).attribute13;
417 l_price_list_line_tbl(i).attribute14 := p_price_list_line_tbl(i).attribute14;
418 l_price_list_line_tbl(i).attribute15 := p_price_list_line_tbl(i).attribute15;
419 -- julou end
420
421 IF p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_CREATE THEN
422 l_price_list_line_tbl(i).list_line_type_code := 'PLL';
423 l_price_list_line_tbl(i).operation := p_price_list_line_tbl(i).operation ;
424 l_price_list_line_tbl(i).arithmetic_operator := 'UNIT_PRICE';
425 l_price_list_line_tbl(i).print_on_invoice_flag := 'N';
426 l_price_list_line_tbl(i).override_flag := 'N';
427 l_price_list_line_tbl(i).automatic_flag := 'Y';
428 l_price_list_line_tbl(i).modifier_level_code := 'LINE';
429
430
431 l_price_list_line_tbl(i).start_date_active := p_price_list_line_tbl(i).start_date_active;
432 l_price_list_line_tbl(i).end_date_active := p_price_list_line_tbl(i).end_date_active;
433 l_price_list_line_tbl(i).operand := p_price_list_line_tbl(i).operand;
434 l_price_list_line_tbl(i).list_price := p_price_list_line_tbl(i).list_price;
435 l_price_list_line_tbl(i).product_precedence := p_price_list_line_tbl(i).product_precedence;
436 l_price_list_line_tbl(i).primary_uom_flag := p_price_list_line_tbl(i).primary_uom_flag;
437 l_price_list_line_tbl(i).generate_using_formula_id := p_price_list_line_tbl(i).static_formula_id;
438 l_price_list_line_tbl(i).price_by_formula_id := p_price_list_line_tbl(i).dynamic_formula_id;
439 ELSIF p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_UPDATE THEN
440 l_price_list_line_tbl(i).operation := p_price_list_line_tbl(i).operation ;
441 l_price_list_line_tbl(i).start_date_active := p_price_list_line_tbl(i).start_date_active;
442 l_price_list_line_tbl(i).end_date_active := p_price_list_line_tbl(i).end_date_active;
443 l_price_list_line_tbl(i).operand := p_price_list_line_tbl(i).operand;
444 l_price_list_line_tbl(i).list_price := p_price_list_line_tbl(i).list_price;
445 l_price_list_line_tbl(i).product_precedence := p_price_list_line_tbl(i).product_precedence;
446 l_price_list_line_tbl(i).primary_uom_flag := p_price_list_line_tbl(i).primary_uom_flag;
447 l_price_list_line_tbl(i).generate_using_formula_id := p_price_list_line_tbl(i).static_formula_id;
451 l_lines_delete := 'Y';
448 l_price_list_line_tbl(i).price_by_formula_id := p_price_list_line_tbl(i).dynamic_formula_id;
449 l_price_list_line_tbl(i).comments := p_price_list_line_tbl(i).comments;
450 ELSE
452 END IF;
453 END IF;
454
455 END LOOP;
456 END IF;
457
458 IF p_pricing_attr_tbl.count > 0 THEN
459 FOR i in p_pricing_attr_tbl.first..p_pricing_attr_tbl.last LOOP
460
461 IF p_pricing_attr_tbl.exists(i) AND p_pricing_attr_tbl(i).operation <> FND_API.G_MISS_CHAR THEN
462
463 l_pricing_attr_tbl(i).pricing_attribute_id := p_pricing_attr_tbl(i).pricing_attribute_id;
464 l_pricing_attr_tbl(i).list_line_id := p_pricing_attr_tbl(i).list_line_id;
465 l_pricing_attr_tbl(i).operation := p_pricing_attr_tbl(i).operation ;
466 l_pricing_attr_tbl(i).PRODUCT_ATTR_VALUE := p_pricing_attr_tbl(i).PRODUCT_ATTR_VALUE;
467 l_pricing_attr_tbl(i).PRODUCT_UOM_CODE := p_pricing_attr_tbl(i).PRODUCT_UOM_CODE;
468
469 IF p_pricing_attr_tbl(i).operation = QP_GLOBALS.G_OPR_CREATE THEN
470
471 l_pricing_attr_tbl(i).PRODUCT_ATTRIBUTE_CONTEXT := 'ITEM';
472 l_pricing_attr_tbl(i).PRODUCT_ATTRIBUTE := p_pricing_attr_tbl(i).PRODUCT_ATTRIBUTE;
473 l_pricing_attr_tbl(i).EXCLUDER_FLAG := 'N';
474 l_pricing_attr_tbl(i).ATTRIBUTE_GROUPING_NO := 1;
475 l_pricing_attr_tbl(i).PRICE_LIST_LINE_INDEX := i;
476
477 END IF;
478
479 END IF;
480
481 END LOOP;
482 END IF;
483
484 QP_PRICE_LIST_PUB.Process_Price_List
485 ( p_api_version_number => 1
486 , p_init_msg_list => FND_API.G_TRUE
487 , p_return_values => FND_API.G_FALSE
488 , p_commit => FND_API.G_FALSE
489 , x_return_status => l_return_status
490 , x_msg_count => l_msg_count
491 , x_msg_data => l_msg_data
492 , p_PRICE_LIST_rec => l_price_list_rec
493 , p_PRICE_LIST_LINE_tbl => l_price_list_line_tbl
494 , p_PRICING_ATTR_tbl => l_pricing_attr_tbl
495 , x_PRICE_LIST_rec => v_price_list_rec
496 , x_PRICE_LIST_val_rec => v_price_list_val_rec
497 , x_PRICE_LIST_LINE_TBL => v_price_list_line_tbl
498 , x_PRICE_LIST_LINE_val_tbl => v_price_list_line_val_tbl
499 , x_QUALIFIERS_tbl => v_qualifiers_tbl
500 , x_QUALIFIERS_val_tbl => v_qualifiers_val_tbl
501 , x_PRICING_ATTR_tbl => v_pricing_attr_tbl
502 , x_PRICING_ATTR_val_tbl => v_pricing_attr_val_tbl
503 );
504
505 IF l_return_status <> fnd_api.g_ret_sts_success THEN
506 IF v_price_list_rec.return_status <> fnd_api.g_ret_sts_success THEN
507 x_error_source := 'H';
508 ELSE
509 IF v_price_list_line_tbl.count > 0 THEN
510 FOR i in v_price_list_line_tbl.first..v_price_list_line_tbl.last LOOP
511 IF v_price_list_line_tbl.exists(i) THEN
512 IF v_price_list_line_tbl(i).return_status <> fnd_api.g_ret_sts_success THEN
513 x_error_source := 'L';
514 x_error_location := i;
515 exit;
516 END IF;
517 END IF;
518 END LOOP;
519 END IF;
520 END IF;
521
522 IF x_error_source not in ('H','L') THEN
523
524 IF v_pricing_attr_tbl.count > 0 THEN
525 FOR i in v_pricing_attr_tbl.first..v_pricing_attr_tbl.last LOOP
526 IF v_pricing_attr_tbl.exists(i) THEN
527 IF v_pricing_attr_tbl(i).return_status <> fnd_api.g_ret_sts_success THEN
528 x_error_source := 'L';
529 x_error_location := i;
530 exit;
531 END IF;
532 END IF;
533 END LOOP;
534 END IF;
535 END IF;
536
537 ELSE
538
539 IF l_lines_delete = 'Y' THEN
540 l_price_list_rec := temp_price_list_rec;
541 l_price_list_line_tbl.delete;
542 l_pricing_attr_tbl.delete;
543 j := 1;
544 FOR i in p_price_list_line_tbl.first..p_price_list_line_tbl.last LOOP
545
546 IF p_price_list_line_tbl.exists(i) AND p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_DELETE THEN
547
548 l_price_list_line_tbl(j).list_line_id := p_price_list_line_tbl(i).list_line_id;
549 l_price_list_line_tbl(j).list_header_id := p_price_list_line_tbl(i).list_header_id;
550 l_price_list_line_tbl(j).operation := p_price_list_line_tbl(i).operation ;
551 j := j+1;
552 END IF;
553
554 END LOOP;
555
556 QP_PRICE_LIST_PUB.Process_Price_List
557 ( p_api_version_number => 1
558 , p_init_msg_list => FND_API.G_TRUE
559 , p_return_values => FND_API.G_FALSE
560 , p_commit => FND_API.G_FALSE
564 , p_PRICE_LIST_rec => l_price_list_rec
561 , x_return_status => l_return_status
562 , x_msg_count => l_msg_count
563 , x_msg_data => l_msg_data
565 , p_PRICE_LIST_LINE_tbl => l_price_list_line_tbl
566 , p_PRICING_ATTR_tbl => l_pricing_attr_tbl
567 , x_PRICE_LIST_rec => v_price_list_rec
568 , x_PRICE_LIST_val_rec => v_price_list_val_rec
569 , x_PRICE_LIST_LINE_TBL => v_price_list_line_tbl
570 , x_PRICE_LIST_LINE_val_tbl => v_price_list_line_val_tbl
571 , x_QUALIFIERS_tbl => v_qualifiers_tbl
572 , x_QUALIFIERS_val_tbl => v_qualifiers_val_tbl
573 , x_PRICING_ATTR_tbl => v_pricing_attr_tbl
574 , x_PRICING_ATTR_val_tbl => v_pricing_attr_val_tbl
575 );
576
577 IF l_return_status <> fnd_api.g_ret_sts_success THEN
578
579 IF v_price_list_line_tbl.count > 0 THEN
580 FOR i in v_price_list_line_tbl.first..v_price_list_line_tbl.last LOOP
581 IF v_price_list_line_tbl.exists(i) THEN
582 IF v_price_list_line_tbl(i).return_status <> fnd_api.g_ret_sts_success THEN
583 x_error_source := 'L';
584 x_error_location := i;
585 exit;
586 END IF;
587 END IF;
588 END LOOP;
589 END IF;
590 END IF;
591
592 END IF;
593
594 END IF;
595
596 IF l_return_status = fnd_api.g_ret_sts_error THEN
597 FOR i in 1 .. l_msg_count LOOP
598 l_msg_data := oe_msg_pub.get( p_msg_index => i,
599 p_encoded => 'F' );
600 FND_MESSAGE.SET_NAME('OZF','OZF_QP_ERROR');
601 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
602 FND_MSG_PUB.ADD;
603 END LOOP;
604 RAISE FND_API.g_exc_error;
605
606 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
607 FOR i in 1 .. l_msg_count LOOP
608 l_msg_data := oe_msg_pub.get( p_msg_index => i,
609 p_encoded => 'F' );
610
611 FND_MESSAGE.SET_NAME('OZF','OZF_QP_ERROR');
612 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
613 FND_MSG_PUB.ADD;
614 END LOOP;
615 RAISE FND_API.g_exc_unexpected_error;
616 END IF;
617
618 IF p_price_list_rec.operation <> FND_API.G_MISS_CHAR THEN
619 x_list_header_id := v_price_list_rec.list_header_id;
620 l_ozf_price_list_attr_rec.status_code := l_new_status_code;
621 l_ozf_price_list_attr_rec.user_status_id := p_price_list_rec.user_status_id;
622 l_ozf_price_list_attr_rec.owner_id := p_price_list_rec.owner_id;
623 l_ozf_price_list_attr_rec.custom_setup_id := p_price_list_rec.custom_setup_id ;
624
625 IF p_price_list_rec.operation = QP_GLOBALS.G_OPR_CREATE THEN
626 FND_MESSAGE.SET_NAME('OZF','CREATE');
627 l_ozf_price_list_attr_rec.object_version_number := 1;
628 l_ozf_price_list_attr_rec.qp_list_header_id := v_price_list_rec.list_header_id ;
629 l_ozf_price_list_attr_rec.custom_setup_id := p_price_list_rec.custom_setup_id ;
630 l_ozf_price_list_attr_rec.status_code := 'DRAFT';
631 OPEN cur_get_user_status_id('DRAFT');
632 FETCH cur_get_user_status_id into l_ozf_price_list_attr_rec.user_status_id;
633 CLOSE cur_get_user_status_id;
634 l_ozf_price_list_attr_rec.status_date := sysdate;
635
636 -- added by julou 08/21/2001 calling create_access to create an entry in ams_act_access
637 l_access_rec.ACT_ACCESS_TO_OBJECT_ID := x_list_header_id;
638 l_access_rec.ARC_ACT_ACCESS_TO_OBJECT := 'PRIC';
639 l_access_rec.USER_OR_ROLE_ID := p_price_list_rec.owner_id;
640 l_access_rec.ARC_USER_OR_ROLE_TYPE := 'USER';
641 l_access_rec.ACTIVE_FROM_DATE := p_price_list_rec.start_date_active;
642 l_access_rec.ADMIN_FLAG := 'Y';
643 l_access_rec.ACTIVE_TO_DATE := p_price_list_rec.end_date_active;
644 l_access_rec.OWNER_FLAG := 'Y';
645
646 ams_access_PVT.create_access(
647 p_api_version => l_api_version,
648 p_init_msg_list => FND_API.g_false,
649 p_commit => FND_API.g_false,
650 p_validation_level => FND_API.g_valid_level_full,
651
652 x_return_status => l_return_status,
653 x_msg_count => l_msg_count,
654 x_msg_data => l_msg_data,
655
656 p_access_rec => l_access_rec,
657 x_access_id => l_access_id
658 );
659 IF l_return_status = fnd_api.g_ret_sts_error THEN
660 RAISE FND_API.g_exc_error;
661 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
662 RAISE FND_API.g_exc_unexpected_error;
663 END IF;
664
665 process_price_list_attributes(p_price_list_attr_rec => l_ozf_price_list_attr_rec
666 ,p_operation => p_price_list_rec.operation
667 ,x_return_status => l_return_status
668 );
669
670 IF l_return_status = fnd_api.g_ret_sts_error THEN
671 x_error_source := 'H';
672 RAISE FND_API.g_exc_error;
673 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
674 x_error_source := 'H';
675 RAISE FND_API.g_exc_unexpected_error;
676 END IF;
677 -- end of comments
678
679 ELSE -- operation = 'update'
680 -- get old user_status id
681 IF p_price_list_rec.operation = 'UPDATE'
682 AND ( p_price_list_rec.list_header_id IS NULL
683 or p_price_list_rec.list_header_id = FND_API.g_miss_num) THEN
684 NULL;
685 ELSE
686 IF p_price_list_rec.owner_id = fnd_api.g_miss_num THEN
687 l_owner_id := l_old_owner_id;
688 ELSE
689 l_owner_id := p_price_list_rec.owner_id;
690 END IF;
691 -- check if approval is required
692 IF p_price_list_rec.user_status_id IS NOT NULL AND p_price_list_rec.user_status_id <> FND_API.G_MISS_NUM THEN
693 -- bug 3780070 exception when updating price list created from QP i.e. no status for QP price lists
694 IF l_approval_type IS NOT NULL THEN -- approval is required
695 l_ozf_price_list_attr_rec.user_status_id := ozf_utility_pvt.get_default_user_status('OZF_PRICELIST_STATUS','PENDING');
696 l_ozf_price_list_attr_rec.status_code := 'PENDING';
697 END IF;
698 END IF; -- end bug 3780070 exception when updating price list created from QP
699
700
701 l_ozf_price_list_attr_rec.price_list_attribute_id := p_price_list_rec.price_list_attribute_id;
702 l_ozf_price_list_attr_rec.object_version_number := p_price_list_rec.object_version_number;
703 l_ozf_price_list_attr_rec.qp_list_header_id := p_price_list_rec.list_header_id;
704 -- added by julou 08/21/2001 check if the owner is changed. if so, update the owner inof in ams_act_access
705 l_is_owner := ams_access_PVT.check_owner(p_object_id => p_price_list_rec.list_header_id
706 ,p_object_type => 'PRIC'
707 ,p_user_or_role_id => l_owner_id
708 ,p_user_or_role_type => 'USER');
709 IF l_owner_id IS NOT NULL AND l_owner_id <> FND_API.G_MISS_NUM AND l_is_owner = 'N' THEN -- owner is changed
710 -- bug 3780070 exception when updating price list created from QP i.e. no owner id for QP price lists
711 l_is_admin := ams_Access_PVT.Check_Admin_Access(l_resource_id); -- check if login user is super user
712 l_login_is_owner := ams_access_PVT.check_owner(p_object_id => p_price_list_rec.list_header_id
713 ,p_object_type => 'PRIC'
714 ,p_user_or_role_id => l_resource_id
715 ,p_user_or_role_type => 'USER');
716 IF l_is_admin OR l_login_is_owner = 'Y' THEN -- only super user/owner of price list can change the owner
717 ams_access_PVT.update_object_owner(
718 p_api_version => l_api_version,
719 p_init_msg_list => FND_API.g_false,
720 p_commit => FND_API.g_false,
721 p_validation_level => FND_API.g_valid_level_full,
722 x_return_status => l_return_status,
723 x_msg_count => l_msg_count,
724 x_msg_data => l_msg_data,
725 p_object_type => 'PRIC',
726 p_object_id => p_price_list_rec.list_header_id,
727 p_resource_id => p_price_list_rec.owner_id,
728 p_old_resource_id => l_old_owner_id);
729
730 IF l_return_status = fnd_api.g_ret_sts_error THEN
731 RAISE FND_API.g_exc_error;
732 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
733 RAISE FND_API.g_exc_unexpected_error;
734 END IF;
735 ELSE
736 OZF_Utility_PVT.error_message('OZF_PRIC_UPDT_OWNER_PERM');
737 x_return_status := FND_API.g_ret_sts_error;
738 END IF;
739
740
741
742 END IF;
743 -- end of comments
744 process_price_list_attributes(p_price_list_attr_rec => l_ozf_price_list_attr_rec
745 ,p_operation => p_price_list_rec.operation
746 ,x_return_status => l_return_status
747 );
748 IF l_return_status = fnd_api.g_ret_sts_error THEN
749 x_error_source := 'H';
750 RAISE FND_API.g_exc_error;
751 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
752 x_error_source := 'H';
753 RAISE FND_API.g_exc_unexpected_error;
754 END IF;
755
756 IF l_approval_type IS NOT NULL THEN -- approval is required
757 ams_gen_approval_pvt.StartProcess(p_activity_type => 'PRIC'
758 ,p_activity_id => p_price_list_rec.list_header_id
759 ,p_approval_type => 'PRICELIST'
760 ,p_object_version_number => p_price_list_rec.object_version_number
761 ,p_orig_stat_id => l_old_status_id
762 ,p_new_stat_id => p_price_list_rec.user_status_id
763 ,p_reject_stat_id => ozf_utility_pvt.get_default_user_status('OZF_PRICELIST_STATUS','REJECTED')
764 ,p_requester_userid => OZF_Utility_PVT.get_resource_id(FND_GLOBAL.user_id)
765 ,p_workflowprocess => 'AMSGAPP'
766 ,p_item_type => 'AMSGAPP');
767 END IF;
768
769 END IF;
770 END IF; -- end operation
771 END IF;
772 /*-- added by julou 08/16/2001 create or update note info in jtf notes tables.
773 IF p_price_list_line_tbl.count > 0 THEN
774 FOR i in p_price_list_line_tbl.first .. p_price_list_line_tbl.last LOOP
775 IF p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_UPDATE
776 OR p_price_list_line_tbl(i).operation = QP_GLOBALS.G_OPR_CREATE THEN
777 IF p_price_list_line_tbl(i).jtf_note_id IS NULL THEN
778 IF p_price_list_line_tbl(i).note IS NOT NULL THEN
779 JTF_NOTES_PUB.Create_note(
780 p_parent_note_id => FND_API.g_miss_num,
781 p_jtf_note_id => NULL,
782 p_api_version => l_api_version,
783 p_init_msg_list => FND_API.g_false,
784 p_commit => FND_API.g_false,
785 p_validation_level => FND_API.g_valid_level_full,
786 x_return_status => l_return_status,
787 x_msg_count => l_msg_count,
788 x_msg_data => l_msg_data,
789 p_org_id => NULL,
790 p_source_object_id => v_price_list_line_tbl(i).list_line_id,
791 p_source_object_code => 'OZF_PRIC_LINE',
792 p_notes => p_price_list_line_tbl(i).note,
793 p_notes_detail => NULL,
794 p_note_status => 'I',
795 p_entered_by => FND_GLOBAL.user_id,
796 p_entered_date => SYSDATE,
797 x_jtf_note_id => l_jtf_note_id,
798 p_last_update_date => SYSDATE,
799 p_last_updated_by => FND_GLOBAL.user_id,
800 p_creation_date => SYSDATE,
801 p_created_by => FND_GLOBAL.user_id,
802 p_last_update_login => FND_GLOBAL.login_id,
803 p_attribute1 => NULL,
804 p_attribute2 => NULL,
805 p_attribute3 => NULL,
806 p_attribute4 => NULL,
807 p_attribute5 => NULL,
808 p_attribute6 => NULL,
809 p_attribute7 => NULL,
810 p_attribute8 => NULL,
811 p_attribute9 => NULL,
812 p_attribute10 => NULL,
813 p_attribute11 => NULL,
814 p_attribute12 => NULL,
815 p_attribute13 => NULL,
816 p_attribute14 => NULL,
817 p_attribute15 => NULL,
818 p_context => NULL,
819 p_note_type => 'OZF_PRICELISTREPORT',
820 p_jtf_note_contexts_tab => JTF_NOTES_PUB.jtf_note_contexts_tab_dflt);
821 END IF;
822
823 IF l_return_status <> FND_API.g_ret_sts_success THEN
824 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
825 FND_MESSAGE.SET_NAME('OZF', 'OZF_NOTE_ERROR');
826 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
827 FND_MSG_PUB.Add;
828 RAISE FND_API.g_exc_unexpected_error;
829 ELSE
830 FND_MESSAGE.Set_Name('OZF', 'OZF_NOTE_ERROR');
831 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
832 FND_MSG_PUB.Add;
833 RAISE FND_API.g_exc_error;
834 END IF;
835 END IF;
836 ELSE
837 JTF_NOTES_PUB.Update_note(
838 p_api_version => l_api_version,
839 p_init_msg_list => FND_API.g_false,
840 p_commit => FND_API.g_false,
841 p_validation_level => FND_API.g_valid_level_full,
842 x_return_status => l_return_status,
843 x_msg_count => l_msg_count,
844 x_msg_data => l_msg_data,
845 p_jtf_note_id => p_price_list_line_tbl(i).jtf_note_id,
846 p_entered_by => FND_GLOBAL.user_id,
847 p_last_updated_by => FND_GLOBAL.user_id,
848 p_last_update_date => SYSDATE,
849 p_last_update_login => FND_GLOBAL.login_id,
850 p_notes => p_price_list_line_tbl(i).note,
851 p_notes_detail => NULL,
852 p_append_flag => FND_API.g_miss_char,
853 p_note_status => 'I',
854 p_note_type => 'OZF_PRICELISTREPORT',
855 p_jtf_note_contexts_tab => JTF_NOTES_PUB.jtf_note_contexts_tab_dflt);
856
857 IF l_return_status <> FND_API.g_ret_sts_success THEN
858 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
859 FND_MESSAGE.SET_NAME('OZF', 'OZF_NOTE_ERROR');
860 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
861 FND_MSG_PUB.Add;
862 RAISE FND_API.g_exc_unexpected_error;
863 ELSE
864 FND_MESSAGE.Set_Name('OZF', 'OZF_NOTE_ERROR');
865 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
866 FND_MSG_PUB.Add;
867 RAISE FND_API.g_exc_error;
868 END IF;
869 END IF;
870 END IF;
871 END IF;
872 END LOOP;
873 END IF;
874 */
875 -- end of code added by julou
876
877 IF p_commit = FND_API.g_true then
878 COMMIT WORK;
879 END IF;
880
881 FND_MSG_PUB.Count_AND_Get
882 ( p_count => x_msg_count,
883 p_data => x_msg_data,
884 p_encoded => FND_API.G_FALSE );
885
886 EXCEPTION
887 WHEN FND_API.G_EXC_ERROR THEN
888 x_return_status := FND_API.g_ret_sts_error ;
889 ROLLBACK TO process_price_list;
890 FND_MSG_PUB.Count_AND_Get
891 ( p_count => x_msg_count,
892 p_data => x_msg_data,
893 p_encoded => FND_API.G_FALSE
894 );
895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
896 x_return_status := FND_API.g_ret_sts_unexp_error ;
897 ROLLBACK TO process_price_list;
898 FND_MSG_PUB.Count_AND_Get
899 ( p_count => x_msg_count,
900 p_data => x_msg_data,
901 p_encoded => FND_API.G_FALSE
902 );
903 WHEN OTHERS THEN
904 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
905 x_error_source := 'H';
906 ROLLBACK TO process_price_list;
907 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
908 THEN
909 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
910 END IF;
911 FND_MSG_PUB.Count_AND_Get
912 ( p_count => x_msg_count,
913 p_data => x_msg_data,
914 p_encoded => FND_API.G_FALSE
915 );
916
917 END;
918
919 PROCEDURE move_segments (
920 p_api_version IN NUMBER,
921 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
922 p_commit IN VARCHAR2 := FND_API.g_false,
923 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
924 x_return_status OUT NOCOPY VARCHAR2,
925 x_msg_count OUT NOCOPY NUMBER,
926 x_msg_data OUT NOCOPY VARCHAR2,
927 p_price_list_id IN NUMBER
928 )IS
929 l_msg_data varchar2(2000);
930 l_msg_count number;
931 l_return_status varchar2(1) := FND_API.g_ret_sts_success;
932 l_api_name constant varchar2(30) := 'move_segments';
933 i number := 1;
934 l_list_header_id number;
935 l_api_version CONSTANT NUMBER := 1.0;
936
937 CURSOR cur_get_market_segments IS
938 SELECT aam.market_segment_name
939 FROM ams_act_mkt_segments_vl aam
940 WHERE aam.act_market_segment_used_by_id = p_price_list_id
941 AND aam.arc_act_market_segment_used_by ='PRIC'
942 AND aam.segment_type = 'MARKET_SEGMENT';
943
944 CURSOR cur_get_target_segments IS
945 SELECT aam.market_segment_name
946 FROM ams_act_mkt_segments_vl aam
947 WHERE aam.act_market_segment_used_by_id = p_price_list_id
948 AND aam.arc_act_market_segment_used_by = 'PRIC'
949 AND aam.segment_type = 'CELL';
950
951
952 l_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
953 l_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
954 l_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
955 l_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
956 l_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
957 l_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
958 l_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
959 l_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
960 v_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
961 v_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
962 v_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
963 v_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
964 v_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
965 v_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
966 v_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
967 v_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
968
969 BEGIN
970 SAVEPOINT move_segments;
971
972 IF FND_API.to_boolean(p_init_msg_list) THEN
973 FND_MSG_PUB.initialize;
974 END IF;
975
976 IF NOT FND_API.compatible_api_call
977 (
978 l_api_version,
979 p_api_version,
980 l_api_name,
981 g_pkg_name
982 )
983 THEN
984 RAISE FND_API.g_exc_unexpected_error;
985 END IF;
986
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 --Create market segments
990 FOR market_segment_rec in cur_get_market_segments
991 LOOP
992 l_qualifiers_tbl(i).qualifier_attr_value := market_segment_rec.market_segment_name;
993 l_qualifiers_tbl(i).list_header_id := p_price_list_id;
994 l_qualifiers_tbl(i).qualifier_context := 'SEGMENT';
995 l_qualifiers_tbl(i).qualifier_attribute := 'QUALIFIER_ATTRIBUTE1';
996 l_qualifiers_tbl(i).comparison_operator_code := '=';
997 l_qualifiers_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
998 i := i+1;
999 END LOOP;
1000
1001 --Create Target segments
1002 FOR target_segment_rec in cur_get_target_segments
1003 LOOP
1004 l_qualifiers_tbl(i).qualifier_attr_value := target_segment_rec.market_segment_name;
1005 l_qualifiers_tbl(i).list_header_id := p_price_list_id;
1006 l_qualifiers_tbl(i).qualifier_context := 'SEGMENT';
1007 l_qualifiers_tbl(i).qualifier_attribute := 'QUALIFER_ATTRIBUTE2';
1008 l_qualifiers_tbl(i).comparison_operator_code := '=';
1009 l_qualifiers_tbl(i).operation := QP_GLOBALS.G_OPR_CREATE;
1010 i := i+1;
1011 END LOOP;
1012
1013 IF l_qualifiers_tbl.count > 0 THEN
1014
1015 QP_PRICE_LIST_PUB.Process_Price_List
1016 ( p_api_version_number => 1
1017 , p_init_msg_list => FND_API.G_TRUE
1018 , p_return_values => FND_API.G_FALSE
1019 , p_commit => FND_API.G_FALSE
1020 , x_return_status => l_return_status
1021 , x_msg_count => l_msg_count
1022 , x_msg_data => l_msg_data
1023 , p_PRICE_LIST_rec => l_price_list_rec
1024 , p_PRICE_LIST_LINE_tbl => l_price_list_line_tbl
1025 , p_PRICING_ATTR_tbl => l_pricing_attr_tbl
1026 , p_QUALIFIERS_tbl => l_qualifiers_tbl
1027 , x_PRICE_LIST_rec => v_price_list_rec
1028 , x_PRICE_LIST_val_rec => v_price_list_val_rec
1029 , x_PRICE_LIST_LINE_TBL => v_price_list_line_tbl
1030 , x_PRICE_LIST_LINE_val_tbl => v_price_list_line_val_tbl
1031 , x_QUALIFIERS_tbl => v_qualifiers_tbl
1032 , x_QUALIFIERS_val_tbl => v_qualifiers_val_tbl
1033 , x_PRICING_ATTR_tbl => v_pricing_attr_tbl
1034 , x_PRICING_ATTR_val_tbl => v_pricing_attr_val_tbl
1035 );
1036
1037
1038 IF l_return_status = fnd_api.g_ret_sts_error THEN
1039 FOR i in 1 .. l_msg_count LOOP
1040 l_msg_data := oe_msg_pub.get( p_msg_index => i,
1041 p_encoded => 'F' );
1042
1043 FND_MESSAGE.SET_NAME('OZF','OZF_QP_ERROR');
1044 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
1045 FND_MSG_PUB.ADD;
1046 END LOOP;
1047 RAISE FND_API.g_exc_error;
1048
1049 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1050 FOR i in 1 .. l_msg_count LOOP
1051 l_msg_data := oe_msg_pub.get( p_msg_index => i,
1052 p_encoded => 'F' );
1053
1054 FND_MESSAGE.SET_NAME('OZF','OZF_QP_ERROR');
1055 FND_MESSAGE.SET_TOKEN('ERROR_MSG',l_msg_data);
1056 FND_MSG_PUB.ADD;
1057 END LOOP;
1058 RAISE FND_API.g_exc_unexpected_error;
1059 END IF;
1060
1061 END IF;
1062
1063 FND_MSG_PUB.Count_AND_Get
1064 ( p_count => x_msg_count,
1065 p_data => x_msg_data,
1066 p_encoded => FND_API.G_FALSE );
1067
1068 EXCEPTION
1069 WHEN FND_API.G_EXC_ERROR THEN
1070 x_return_status := FND_API.g_ret_sts_error ;
1071 ROLLBACK TO move_segments;
1072 FND_MSG_PUB.Count_AND_Get
1073 ( p_count => x_msg_count,
1074 p_data => x_msg_data,
1075 p_encoded => FND_API.G_FALSE
1076 );
1077 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078 x_return_status := FND_API.g_ret_sts_unexp_error ;
1079 ROLLBACK TO move_segments;
1080 FND_MSG_PUB.Count_AND_Get
1081 ( p_count => x_msg_count,
1082 p_data => x_msg_data,
1083 p_encoded => FND_API.G_FALSE
1084 );
1085 WHEN OTHERS THEN
1086 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
1087 ROLLBACK TO move_segments;
1088 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1089 THEN
1090 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1091 END IF;
1092 FND_MSG_PUB.Count_AND_Get
1093 ( p_count => x_msg_count,
1094 p_data => x_msg_data,
1095 p_encoded => FND_API.G_FALSE
1096 );
1097
1098 END;
1099 /**
1100 Helper method to get product or category name.
1101 Logic for getting category_name is as:
1102 if category belongs to inventory categories then get concat_segments as before
1103 for backword compatibility, else if category belongs to unified product catelog
1104 get the category description as the parentage is not correct.
1105 */
1106 FUNCTION get_product_name
1107 ( p_type IN VARCHAR2,
1108 p_prod_value IN NUMBER
1109 ) RETURN VARCHAR2 IS
1110
1111 CURSOR get_prod_name IS
1112 SELECT description
1113 FROM mtl_system_items_kfv
1114 WHERE inventory_item_id = p_prod_value;
1115
1116 CURSOR get_eni_cat_name (p_catg_id NUMBER) IS
1117 SELECT eni.category_desc Category_Name
1118 FROM eni_prod_den_hrchy_parents_v eni
1119 WHERE eni.category_id = p_catg_id;
1120
1121 CURSOR get_mtl_cat_name (p_catg_id NUMBER) IS
1122 SELECT mtl.concatenated_segments Category_Name
1123 FROM mtl_categories_kfv mtl
1124 WHERE mtl.category_id = p_catg_id;
1125
1126 l_name VARCHAR2(4000);
1127
1128 BEGIN
1129
1130 IF p_type = 'INV' THEN
1131 OPEN get_prod_name;
1132 FETCH get_prod_name INTO l_name;
1133 CLOSE get_prod_name;
1134 END IF;
1135
1136 IF p_type = 'CAT' THEN
1137 OPEN get_eni_cat_name(p_prod_value);
1138 FETCH get_eni_cat_name INTO l_name;
1139 CLOSE get_eni_cat_name;
1140 IF l_name is NULL THEN
1141 OPEN get_mtl_cat_name(p_prod_value);
1142 FETCH get_mtl_cat_name INTO l_name;
1143 CLOSE get_mtl_cat_name;
1144 END IF;
1145 END IF;
1146
1147 return (l_name);
1148
1149 END;
1150
1151 FUNCTION check_dup_product(p_list_header_id IN NUMBER, p_inv_item_id IN NUMBER)
1152 RETURN VARCHAR2
1153 IS
1154
1155 l_item_in_prl VARCHAR2(1) := 'N';
1156
1157 CURSOR c_item_in_prl IS
1158 SELECT 'Y'
1159 FROM DUAL
1160 WHERE EXISTS(SELECT 1
1161 FROM qp_pricing_attributes
1162 WHERE product_attribute = 'PRICING_ATTRIBUTE1'
1163 AND product_attr_value = p_inv_item_id
1164 AND list_header_id = p_list_header_id);
1165
1166 BEGIN
1167
1168 OPEN c_item_in_prl;
1169 FETCH c_item_in_prl INTO l_item_in_prl;
1170 CLOSE c_item_in_prl;
1171
1172 RETURN l_item_in_prl;
1173
1174 END;
1175
1176
1177 PROCEDURE add_inventory_item(
1178 p_api_version IN NUMBER,
1179 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1180 p_commit IN VARCHAR2 := FND_API.g_false,
1181 x_return_status OUT NOCOPY VARCHAR2,
1182 x_msg_count OUT NOCOPY NUMBER,
1183 x_msg_data OUT NOCOPY VARCHAR2,
1184 p_org_inv_item_id IN NUMBER,
1185 p_new_inv_item_id IN num_tbl_type
1186 )
1187 IS
1188 l_api_name CONSTANT VARCHAR2(30) := 'add_inventory_item';
1189 l_api_version CONSTANT NUMBER := 1.0;
1190 l_index NUMBER := 1;
1191 l_item_in_prl VARCHAR2(1);
1192 l_source_system_code VARCHAR2(30);
1193
1194 l_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type := QP_PRICE_LIST_PUB.G_MISS_PRICE_LIST_REC;
1195 l_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type := QP_PRICE_LIST_PUB.G_MISS_PRICE_LIST_LINE_TBL;
1196 l_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type := QP_PRICE_LIST_PUB.G_MISS_PRICING_ATTR_TBL;
1197
1198 v_price_list_rec QP_PRICE_LIST_PUB.Price_List_Rec_Type;
1199 v_price_list_val_rec QP_PRICE_LIST_PUB.Price_List_Val_Rec_Type;
1200 v_price_list_line_tbl QP_PRICE_LIST_PUB.Price_List_Line_Tbl_Type;
1201 v_price_list_line_val_tbl QP_PRICE_LIST_PUB.Price_List_Line_Val_Tbl_Type;
1202 v_qualifiers_tbl QP_Qualifier_Rules_Pub.Qualifiers_Tbl_Type;
1203 v_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
1204 v_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
1205 v_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
1206
1207 CURSOR c_list_header_line_id (l_source_system_code VARCHAR2) IS
1208 SELECT DISTINCT qpa.list_header_id, qpa.list_line_id
1209 FROM qp_pricing_attributes qpa, qp_list_lines qll, qp_list_headers_b qlh
1210 WHERE qll.list_line_id = qpa.list_line_id
1211 AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
1212 AND qpa.product_attr_value = TO_CHAR(p_org_inv_item_id)
1213 AND qlh.list_header_id = qpa.list_header_id
1214 AND qlh.list_type_code = 'PRL'
1215 AND qlh.source_system_code = l_source_system_code;
1216
1217 CURSOR c_list_header_detail (l_list_header_id NUMBER) IS
1218 SELECT *
1219 FROM qp_list_headers_vl
1220 WHERE list_header_id = l_list_header_id;
1221 l_list_header_detail c_list_header_detail%ROWTYPE;
1222
1223 CURSOR c_list_line_detail (l_list_line_id NUMBER) IS
1224 SELECT *
1225 FROM qp_list_lines
1226 WHERE list_line_id = l_list_line_id;
1227 l_list_line_detail c_list_line_detail%ROWTYPE;
1228
1229 CURSOR c_pricing_detail (l_list_line_id NUMBER) IS
1230 SELECT *
1231 FROM qp_pricing_attributes
1232 WHERE list_line_id = l_list_line_id;
1233 l_pricing_detail c_pricing_detail%ROWTYPE;
1234
1235
1236 CURSOR c_list_line_id IS
1237 SELECT qp_list_lines_s.NEXTVAL
1238 FROM DUAL;
1239
1240 CURSOR c_pricing_attr_id IS
1241 SELECT qp_pricing_attributes_s.NEXTVAL
1242 FROM DUAL;
1243
1244 l_list_line_id NUMBER;
1245 l_pricing_attr_id NUMBER;
1246
1247 BEGIN
1248
1249 SAVEPOINT add_inventory_item;
1250
1251 IF FND_API.to_boolean(p_init_msg_list) THEN
1252 FND_MSG_PUB.initialize;
1253 END IF;
1254
1255 IF NOT FND_API.compatible_api_call(l_api_version,
1256 p_api_version,
1257 l_api_name,
1258 g_pkg_name)
1259 THEN
1260 RAISE FND_API.g_exc_unexpected_error;
1261 END IF;
1262
1263 x_return_status := FND_API.G_RET_STS_SUCCESS;
1264
1265 l_source_system_code := FND_PROFILE.VALUE('QP_SOURCE_SYSTEM_CODE');
1266
1267 FOR l_list_header_line_id IN c_list_header_line_id(l_source_system_code) LOOP
1268 OPEN c_list_line_detail(l_list_header_line_id.list_line_id);
1269 FETCH c_list_line_detail INTO l_list_line_detail;
1270 CLOSE c_list_line_detail;
1271
1272 OPEN c_pricing_detail(l_list_header_line_id.list_line_id);
1273 FETCH c_pricing_detail INTO l_pricing_detail;
1274 CLOSE c_pricing_detail;
1275
1276 FOR i IN 1..p_new_inv_item_id.COUNT LOOP
1277 l_item_in_prl := check_dup_product(l_list_header_line_id.list_header_id, p_new_inv_item_id(i));
1278 IF l_item_in_prl <> 'Y' THEN
1279 OPEN c_list_line_id;
1280 FETCH c_list_line_id INTO l_list_line_id;
1281 CLOSE c_list_line_id;
1282
1283 OPEN c_pricing_attr_id;
1284 FETCH c_pricing_attr_id INTO l_pricing_attr_id;
1285 CLOSE c_pricing_attr_id;
1286
1287 INSERT INTO qp_list_lines(list_line_id
1288 ,accrual_qty
1289 ,accrual_uom_code
1290 ,arithmetic_operator
1291 ,attribute1
1292 ,attribute2
1293 ,attribute3
1294 ,attribute4
1295 ,attribute5
1296 ,attribute6
1297 ,attribute7
1298 ,attribute8
1299 ,attribute9
1300 ,attribute10
1301 ,attribute11
1302 ,attribute12
1303 ,attribute13
1304 ,attribute14
1305 ,attribute15
1306 ,automatic_flag
1307 ,base_qty
1308 ,base_uom_code
1309 ,comments
1310 ,context
1311 ,created_by
1312 ,creation_date
1313 ,last_updated_by
1314 ,last_update_date
1315 ,last_update_login
1316 ,effective_period_uom
1317 ,end_date_active
1318 ,estim_accrual_rate
1319 ,inventory_item_id
1320 ,list_header_id
1321 ,list_line_no
1322 ,list_line_type_code
1323 ,list_price
1324 ,modifier_level_code
1325 ,number_effective_periods
1326 ,operand
1327 ,organization_id
1328 ,override_flag
1329 ,percent_price
1330 ,price_break_type_code
1331 ,price_by_formula_id
1332 ,primary_uom_flag
1333 ,print_on_invoice_flag
1334 ,program_application_id
1335 ,program_id
1336 ,program_update_date
1337 ,rebate_transaction_type_code
1338 ,related_item_id
1339 ,relationship_type_id
1340 ,reprice_flag
1341 ,request_id
1342 ,revision
1343 ,revision_date
1344 ,revision_reason_code
1345 ,start_date_active
1346 ,substitution_attribute
1347 ,substitution_context
1348 ,substitution_value
1349 ,qualification_ind
1350 ,pricing_phase_id
1351 ,pricing_group_sequence
1352 ,incompatibility_grp_code
1353 ,product_precedence)
1354 VALUES(l_list_line_id
1355 ,l_list_line_detail.accrual_qty
1356 ,l_list_line_detail.accrual_uom_code
1357 ,l_list_line_detail.arithmetic_operator
1358 ,l_list_line_detail.attribute1
1359 ,l_list_line_detail.attribute2
1360 ,l_list_line_detail.attribute3
1361 ,l_list_line_detail.attribute4
1362 ,l_list_line_detail.attribute5
1363 ,l_list_line_detail.attribute6
1364 ,l_list_line_detail.attribute7
1365 ,l_list_line_detail.attribute8
1366 ,l_list_line_detail.attribute9
1367 ,l_list_line_detail.attribute10
1368 ,l_list_line_detail.attribute11
1369 ,l_list_line_detail.attribute12
1370 ,l_list_line_detail.attribute13
1371 ,l_list_line_detail.attribute14
1372 ,l_list_line_detail.attribute15
1373 ,l_list_line_detail.automatic_flag
1374 ,l_list_line_detail.base_qty
1375 ,l_list_line_detail.base_uom_code
1376 ,l_list_line_detail.comments
1377 ,l_list_line_detail.context
1378 ,FND_GLOBAL.user_id
1379 ,SYSDATE
1380 ,FND_GLOBAL.user_id
1381 ,SYSDATE
1382 ,FND_GLOBAL.conc_login_id
1383 ,l_list_line_detail.effective_period_uom
1384 ,l_list_line_detail.end_date_active
1385 ,l_list_line_detail.estim_accrual_rate
1386 ,l_list_line_detail.inventory_item_id
1387 ,l_list_header_line_id.list_header_id
1388 ,l_list_line_id
1389 ,l_list_line_detail.list_line_type_code
1390 ,l_list_line_detail.list_price
1391 ,l_list_line_detail.modifier_level_code
1392 ,l_list_line_detail.number_effective_periods
1393 ,l_list_line_detail.operand
1394 ,l_list_line_detail.organization_id
1395 ,l_list_line_detail.override_flag
1396 ,l_list_line_detail.percent_price
1397 ,l_list_line_detail.price_break_type_code
1398 ,l_list_line_detail.price_by_formula_id
1399 ,l_list_line_detail.primary_uom_flag
1400 ,l_list_line_detail.print_on_invoice_flag
1401 ,l_list_line_detail.program_application_id
1402 ,l_list_line_detail.program_id
1403 ,l_list_line_detail.program_update_date
1404 ,l_list_line_detail.rebate_transaction_type_code
1405 ,l_list_line_detail.related_item_id
1406 ,l_list_line_detail.relationship_type_id
1407 ,l_list_line_detail.reprice_flag
1408 ,l_list_line_detail.request_id
1409 ,l_list_line_detail.revision
1410 ,l_list_line_detail.revision_date
1411 ,l_list_line_detail.revision_reason_code
1412 ,l_list_line_detail.start_date_active
1413 ,l_list_line_detail.substitution_attribute
1414 ,l_list_line_detail.substitution_context
1415 ,l_list_line_detail.substitution_value
1416 ,l_list_line_detail.qualification_ind
1417 ,l_list_line_detail.pricing_phase_id
1418 ,l_list_line_detail.pricing_group_sequence
1419 ,l_list_line_detail.incompatibility_grp_code
1420 ,l_list_line_detail.product_precedence);
1421
1422 INSERT INTO qp_pricing_attributes(pricing_attribute_id
1423 ,accumulate_flag
1424 ,attribute1
1425 ,attribute2
1426 ,attribute3
1427 ,attribute4
1428 ,attribute5
1429 ,attribute6
1430 ,attribute7
1431 ,attribute8
1432 ,attribute9
1433 ,attribute10
1434 ,attribute11
1435 ,attribute12
1436 ,attribute13
1437 ,attribute14
1438 ,attribute15
1439 ,attribute_grouping_no
1440 ,context
1441 ,excluder_flag
1442 ,pricing_attribute
1443 ,pricing_attribute_context
1444 ,pricing_attr_value_from
1445 ,pricing_attr_value_to
1446 ,product_attribute
1447 ,product_attribute_context
1448 ,product_attr_value
1449 ,product_uom_code
1450 ,program_application_id
1451 ,program_id
1452 ,program_update_date
1453 ,request_id
1454 ,pricing_attr_value_from_number
1455 ,pricing_attr_value_to_number
1456 ,qualification_ind
1457 ,comparison_operator_code
1458 ,product_attribute_datatype
1459 ,pricing_attribute_datatype
1460 ,pricing_phase_id
1461 ,list_header_id
1462 ,list_line_id
1463 -- ,list_line_no
1464 ,created_by
1465 ,creation_date
1466 ,last_updated_by
1467 ,last_update_date
1468 ,last_update_login)
1469 VALUES(l_pricing_attr_id
1470 ,l_pricing_detail.accumulate_flag
1471 ,l_pricing_detail.attribute1
1472 ,l_pricing_detail.attribute2
1473 ,l_pricing_detail.attribute3
1474 ,l_pricing_detail.attribute4
1475 ,l_pricing_detail.attribute5
1476 ,l_pricing_detail.attribute6
1477 ,l_pricing_detail.attribute7
1478 ,l_pricing_detail.attribute8
1479 ,l_pricing_detail.attribute9
1480 ,l_pricing_detail.attribute10
1481 ,l_pricing_detail.attribute11
1482 ,l_pricing_detail.attribute12
1483 ,l_pricing_detail.attribute13
1484 ,l_pricing_detail.attribute14
1485 ,l_pricing_detail.attribute15
1486 ,l_pricing_detail.attribute_grouping_no
1487 ,l_pricing_detail.context
1488 ,l_pricing_detail.excluder_flag
1489 ,l_pricing_detail.pricing_attribute
1490 ,l_pricing_detail.pricing_attribute_context
1491 ,l_pricing_detail.pricing_attr_value_from
1492 ,l_pricing_detail.pricing_attr_value_to
1493 ,l_pricing_detail.product_attribute
1494 ,l_pricing_detail.product_attribute_context
1495 ,p_new_inv_item_id(i)
1496 ,l_pricing_detail.product_uom_code
1497 ,l_pricing_detail.program_application_id
1498 ,l_pricing_detail.program_id
1499 ,l_pricing_detail.program_update_date
1500 ,l_pricing_detail.request_id
1501 ,l_pricing_detail.pricing_attr_value_from_number
1502 ,l_pricing_detail.pricing_attr_value_to_number
1503 ,l_pricing_detail.qualification_ind
1504 ,l_pricing_detail.comparison_operator_code
1505 ,l_pricing_detail.product_attribute_datatype
1506 ,l_pricing_detail.pricing_attribute_datatype
1507 ,l_pricing_detail.pricing_phase_id
1508 ,l_list_header_line_id.list_header_id
1509 ,l_list_line_id
1510 -- ,l_list_line_id
1511 ,FND_GLOBAL.user_id
1512 ,SYSDATE
1513 ,FND_GLOBAL.user_id
1514 ,SYSDATE
1515 ,FND_GLOBAL.conc_login_id);
1516 END IF;
1517 END LOOP;
1518 END LOOP;
1519 /*
1520 FOR l_list_header_line_id IN c_list_header_line_id(l_source_system_code) LOOP
1521 l_price_list_line_tbl := QP_PRICE_LIST_PUB.G_MISS_PRICE_LIST_LINE_TBL;
1522 l_pricing_attr_tbl := QP_PRICE_LIST_PUB.G_MISS_PRICING_ATTR_TBL;
1523 l_price_list_rec := NULL;
1524 l_index := 1;
1525
1526 OPEN c_list_header_detail(l_list_header_line_id.list_header_id);
1527 FETCH c_list_header_detail INTO l_list_header_detail;
1528 CLOSE c_list_header_detail;
1529
1530 l_price_list_rec.attribute1 := l_list_header_detail.attribute1;
1531 l_price_list_rec.attribute2 := l_list_header_detail.attribute2;
1532 l_price_list_rec.attribute3 := l_list_header_detail.attribute3;
1533 l_price_list_rec.attribute4 := l_list_header_detail.attribute4;
1534 l_price_list_rec.attribute5 := l_list_header_detail.attribute5;
1535 l_price_list_rec.attribute6 := l_list_header_detail.attribute6;
1536 l_price_list_rec.attribute7 := l_list_header_detail.attribute7;
1537 l_price_list_rec.attribute8 := l_list_header_detail.attribute8;
1538 l_price_list_rec.attribute9 := l_list_header_detail.attribute9;
1539 l_price_list_rec.attribute10 := l_list_header_detail.attribute10;
1540 l_price_list_rec.attribute11 := l_list_header_detail.attribute11;
1541 l_price_list_rec.attribute12 := l_list_header_detail.attribute12;
1542 l_price_list_rec.attribute13 := l_list_header_detail.attribute13;
1543 l_price_list_rec.attribute14 := l_list_header_detail.attribute14;
1544 l_price_list_rec.attribute15 := l_list_header_detail.attribute15;
1545 l_price_list_rec.automatic_flag := l_list_header_detail.automatic_flag;
1546 l_price_list_rec.comments := l_list_header_detail.comments;
1547 l_price_list_rec.context := l_list_header_detail.context;
1548 l_price_list_rec.discount_lines_flag := l_list_header_detail.discount_lines_flag;
1549 l_price_list_rec.freight_terms_code := l_list_header_detail.freight_terms_code;
1550 l_price_list_rec.gsa_indicator := l_list_header_detail.gsa_indicator;
1551 l_price_list_rec.program_application_id := l_list_header_detail.program_application_id;
1552 l_price_list_rec.program_id := l_list_header_detail.program_id;
1553 l_price_list_rec.program_update_date := l_list_header_detail.program_update_date;
1554 l_price_list_rec.prorate_flag := l_list_header_detail.prorate_flag;
1555 l_price_list_rec.request_id := l_list_header_detail.request_id;
1556 l_price_list_rec.ship_method_code := l_list_header_detail.ship_method_code;
1557 l_price_list_rec.terms_id := l_list_header_detail.terms_id;
1558 l_price_list_rec.version_no := l_list_header_detail.version_no;
1559 l_price_list_rec.pte_code := l_list_header_detail.pte_code;
1560 l_price_list_rec.list_source_code := l_list_header_detail.list_source_code;
1561 l_price_list_rec.orig_system_header_ref := l_list_header_detail.orig_system_header_ref;
1562 l_price_list_rec.global_flag := l_list_header_detail.global_flag;
1563
1564 l_price_list_rec.name := l_list_header_detail.name;
1565 l_price_list_rec.description := l_list_header_detail.description;
1566 l_price_list_rec.currency_code := l_list_header_detail.currency_code;
1567 l_price_list_rec.start_date_active := l_list_header_detail.start_date_active;
1568 l_price_list_rec.end_date_active := l_list_header_detail.end_date_active;
1569 l_price_list_rec.currency_header_id := l_list_header_detail.currency_header_id;
1570 l_price_list_rec.active_flag := l_list_header_detail.active_flag;
1571 l_price_list_rec.list_type_code := l_list_header_detail.list_type_code;
1572 l_price_list_rec.list_header_id := l_list_header_line_id.list_header_id;
1573 l_price_list_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
1574 l_price_list_rec.rounding_factor := l_list_header_detail.rounding_factor;
1575 l_price_list_rec.created_by := l_list_header_detail.created_by;
1576 l_price_list_rec.creation_date := l_list_header_detail.creation_date;
1577
1578 OPEN c_list_line_detail(l_list_header_line_id.list_line_id);
1579 FETCH c_list_line_detail INTO l_list_line_detail;
1580 CLOSE c_list_line_detail;
1581
1582 OPEN c_pricing_detail(l_list_header_line_id.list_line_id);
1583 FETCH c_pricing_detail INTO l_pricing_detail;
1584 CLOSE c_pricing_detail;
1585
1586 FOR i IN 1..p_new_inv_item_id.COUNT LOOP
1587 -- check if the new item is already already in the price list
1588 -- l_item_in_prl := check_duplicate_item(l_list_header_line_id.list_header_id, p_new_inv_item_id(i));
1589 IF l_item_in_prl = 'N' THEN
1590 l_price_list_line_tbl(l_index).accrual_qty := l_list_line_detail.accrual_qty;
1591 l_price_list_line_tbl(l_index).accrual_uom_code := l_list_line_detail.accrual_uom_code;
1592 l_price_list_line_tbl(l_index).arithmetic_operator := l_list_line_detail.arithmetic_operator;
1593 l_price_list_line_tbl(l_index).attribute1 := l_list_line_detail.attribute1;
1594 l_price_list_line_tbl(l_index).attribute2 := l_list_line_detail.attribute2;
1595 l_price_list_line_tbl(l_index).attribute3 := l_list_line_detail.attribute3;
1596 l_price_list_line_tbl(l_index).attribute4 := l_list_line_detail.attribute4;
1597 l_price_list_line_tbl(l_index).attribute5 := l_list_line_detail.attribute5;
1598 l_price_list_line_tbl(l_index).attribute6 := l_list_line_detail.attribute6;
1599 l_price_list_line_tbl(l_index).attribute7 := l_list_line_detail.attribute7;
1600 l_price_list_line_tbl(l_index).attribute8 := l_list_line_detail.attribute8;
1601 l_price_list_line_tbl(l_index).attribute9 := l_list_line_detail.attribute9;
1602 l_price_list_line_tbl(l_index).attribute10 := l_list_line_detail.attribute10;
1603 l_price_list_line_tbl(l_index).attribute11 := l_list_line_detail.attribute11;
1604 l_price_list_line_tbl(l_index).attribute12 := l_list_line_detail.attribute12;
1605 l_price_list_line_tbl(l_index).attribute13 := l_list_line_detail.attribute13;
1606 l_price_list_line_tbl(l_index).attribute14 := l_list_line_detail.attribute14;
1607 l_price_list_line_tbl(l_index).attribute15 := l_list_line_detail.attribute15;
1608 l_price_list_line_tbl(l_index).automatic_flag := l_list_line_detail.automatic_flag;
1609 l_price_list_line_tbl(l_index).base_qty := l_list_line_detail.base_qty;
1610 l_price_list_line_tbl(l_index).base_uom_code := l_list_line_detail.base_uom_code;
1611 l_price_list_line_tbl(l_index).comments := l_list_line_detail.comments;
1612 l_price_list_line_tbl(l_index).context := l_list_line_detail.context;
1613 l_price_list_line_tbl(l_index).created_by := l_list_line_detail.created_by;
1614 l_price_list_line_tbl(l_index).creation_date := l_list_line_detail.creation_date;
1615 l_price_list_line_tbl(l_index).effective_period_uom := l_list_line_detail.effective_period_uom;
1616 l_price_list_line_tbl(l_index).end_date_active := l_list_line_detail.end_date_active;
1617 l_price_list_line_tbl(l_index).estim_accrual_rate := l_list_line_detail.estim_accrual_rate;
1618 l_price_list_line_tbl(l_index).generate_using_formula_id := l_list_line_detail.generate_using_formula_id;
1619 l_price_list_line_tbl(l_index).inventory_item_id := l_list_line_detail.inventory_item_id;
1620 l_price_list_line_tbl(l_index).list_header_id := l_list_line_detail.list_header_id;
1621 l_price_list_line_tbl(l_index).list_line_type_code := l_list_line_detail.list_line_type_code;
1622 l_price_list_line_tbl(l_index).list_price := l_list_line_detail.list_price;
1623 l_price_list_line_tbl(l_index).modifier_level_code := l_list_line_detail.modifier_level_code;
1624 l_price_list_line_tbl(l_index).number_effective_periods := l_list_line_detail.number_effective_periods;
1625 l_price_list_line_tbl(l_index).operand := l_list_line_detail.operand;
1626 l_price_list_line_tbl(l_index).organization_id := l_list_line_detail.organization_id;
1627 l_price_list_line_tbl(l_index).override_flag := l_list_line_detail.override_flag;
1628 l_price_list_line_tbl(l_index).percent_price := l_list_line_detail.percent_price;
1629 l_price_list_line_tbl(l_index).price_break_type_code := l_list_line_detail.price_break_type_code;
1630 l_price_list_line_tbl(l_index).price_by_formula_id := l_list_line_detail.price_by_formula_id;
1631 l_price_list_line_tbl(l_index).primary_uom_flag := l_list_line_detail.primary_uom_flag;
1632 l_price_list_line_tbl(l_index).print_on_invoice_flag := l_list_line_detail.print_on_invoice_flag;
1633 l_price_list_line_tbl(l_index).program_application_id := l_list_line_detail.program_application_id;
1634 l_price_list_line_tbl(l_index).program_id := l_list_line_detail.program_id;
1635 l_price_list_line_tbl(l_index).program_update_date := l_list_line_detail.program_update_date;
1636 l_price_list_line_tbl(l_index).rebate_trxn_type_code := l_list_line_detail.rebate_transaction_type_code;
1637 l_price_list_line_tbl(l_index).related_item_id := l_list_line_detail.related_item_id;
1638 l_price_list_line_tbl(l_index).relationship_type_id := l_list_line_detail.relationship_type_id;
1639 l_price_list_line_tbl(l_index).reprice_flag := l_list_line_detail.reprice_flag;
1640 l_price_list_line_tbl(l_index).request_id := l_list_line_detail.request_id;
1641 l_price_list_line_tbl(l_index).revision := l_list_line_detail.revision;
1642 l_price_list_line_tbl(l_index).revision_date := l_list_line_detail.revision_date;
1643 l_price_list_line_tbl(l_index).revision_reason_code := l_list_line_detail.revision_reason_code;
1644 l_price_list_line_tbl(l_index).start_date_active := l_list_line_detail.start_date_active;
1645 l_price_list_line_tbl(l_index).substitution_attribute := l_list_line_detail.substitution_attribute;
1646 l_price_list_line_tbl(l_index).substitution_context := l_list_line_detail.substitution_context;
1647 l_price_list_line_tbl(l_index).substitution_value := l_list_line_detail.substitution_value;
1648 l_price_list_line_tbl(l_index).product_precedence := l_list_line_detail.product_precedence;
1649 l_price_list_line_tbl(l_index).qualification_ind := l_list_line_detail.qualification_ind;
1650 l_price_list_line_tbl(l_index).operation := QP_GLOBALS.G_OPR_CREATE;
1651
1652 l_pricing_attr_tbl(l_index).accumulate_flag := l_pricing_detail.accumulate_flag ;
1653 l_pricing_attr_tbl(l_index).attribute1 := l_pricing_detail.attribute1;
1654 l_pricing_attr_tbl(l_index).attribute2 := l_pricing_detail.attribute2;
1655 l_pricing_attr_tbl(l_index).attribute3 := l_pricing_detail.attribute3;
1656 l_pricing_attr_tbl(l_index).attribute4 := l_pricing_detail.attribute4;
1657 l_pricing_attr_tbl(l_index).attribute5 := l_pricing_detail.attribute5;
1658 l_pricing_attr_tbl(l_index).attribute6 := l_pricing_detail.attribute6;
1659 l_pricing_attr_tbl(l_index).attribute7 := l_pricing_detail.attribute7;
1660 l_pricing_attr_tbl(l_index).attribute8 := l_pricing_detail.attribute8;
1661 l_pricing_attr_tbl(l_index).attribute9 := l_pricing_detail.attribute9;
1662 l_pricing_attr_tbl(l_index).attribute10 := l_pricing_detail.attribute10;
1663 l_pricing_attr_tbl(l_index).attribute11 := l_pricing_detail.attribute11;
1664 l_pricing_attr_tbl(l_index).attribute12 := l_pricing_detail.attribute12;
1665 l_pricing_attr_tbl(l_index).attribute13 := l_pricing_detail.attribute13;
1666 l_pricing_attr_tbl(l_index).attribute14 := l_pricing_detail.attribute14;
1667 l_pricing_attr_tbl(l_index).attribute15 := l_pricing_detail.attribute15;
1668 l_pricing_attr_tbl(l_index).attribute_grouping_no := l_pricing_detail.attribute_grouping_no;
1669 l_pricing_attr_tbl(l_index).context := l_pricing_detail.context;
1670 l_pricing_attr_tbl(l_index).excluder_flag := l_pricing_detail.excluder_flag;
1671 l_pricing_attr_tbl(l_index).pricing_attribute := l_pricing_detail.pricing_attribute;
1672 l_pricing_attr_tbl(l_index).pricing_attribute_context := l_pricing_detail.pricing_attribute_context;
1673 l_pricing_attr_tbl(l_index).pricing_attr_value_from := l_pricing_detail.pricing_attr_value_from;
1674 l_pricing_attr_tbl(l_index).pricing_attr_value_to := l_pricing_detail.pricing_attr_value_to;
1675 l_pricing_attr_tbl(l_index).product_attribute := l_pricing_detail.product_attribute;
1676 l_pricing_attr_tbl(l_index).product_attribute_context := l_pricing_detail.product_attribute_context;
1677 l_pricing_attr_tbl(l_index).product_attr_value := p_new_inv_item_id(i);
1678 l_pricing_attr_tbl(l_index).product_uom_code := l_pricing_detail.product_uom_code;
1679 l_pricing_attr_tbl(l_index).program_application_id := l_pricing_detail.program_application_id;
1680 l_pricing_attr_tbl(l_index).program_id := l_pricing_detail.program_id;
1681 l_pricing_attr_tbl(l_index).program_update_date := l_pricing_detail.program_update_date;
1682 l_pricing_attr_tbl(l_index).request_id := l_pricing_detail.request_id;
1683 l_pricing_attr_tbl(l_index).pricing_attr_value_from_number := l_pricing_detail.pricing_attr_value_from_number;
1684 l_pricing_attr_tbl(l_index).pricing_attr_value_to_number := l_pricing_detail.pricing_attr_value_to_number;
1685 l_pricing_attr_tbl(l_index).qualification_ind := l_pricing_detail.qualification_ind;
1686 l_pricing_attr_tbl(l_index).comparison_operator_code := l_pricing_detail.comparison_operator_code;
1687 l_pricing_attr_tbl(l_index).product_attribute_datatype := l_pricing_detail.product_attribute_datatype;
1688 l_pricing_attr_tbl(l_index).pricing_attribute_datatype := l_pricing_detail.pricing_attribute_datatype;
1689 l_pricing_attr_tbl(l_index).list_header_id := l_pricing_detail.list_header_id;
1690 l_pricing_attr_tbl(l_index).pricing_phase_id := l_pricing_detail.pricing_phase_id;
1691 l_pricing_attr_tbl(l_index).operation := QP_GLOBALS.G_OPR_CREATE;
1692
1693 l_index := l_index + 1;
1694 END IF;
1695 END LOOP;
1696
1697 QP_PRICE_LIST_PUB.Process_Price_List
1698 ( p_api_version_number => p_api_version
1699 , p_init_msg_list => p_init_msg_list
1700 , p_return_values => FND_API.G_FALSE
1701 , p_commit => p_commit
1702 , x_return_status => x_return_status
1703 , x_msg_count => x_msg_count
1704 , x_msg_data => x_msg_data
1705 , p_price_list_rec => l_price_list_rec
1706 , p_price_list_line_tbl => l_price_list_line_tbl
1707 , p_pricing_attr_tbl => l_pricing_attr_tbl
1708 , x_price_list_rec => v_price_list_rec
1709 , x_price_list_val_rec => v_price_list_val_rec
1710 , x_price_list_line_tbl => v_price_list_line_tbl
1711 , x_price_list_line_val_tbl => v_price_list_line_val_tbl
1712 , x_qualifiers_tbl => v_qualifiers_tbl
1713 , x_qualifiers_val_tbl => v_qualifiers_val_tbl
1714 , x_pricing_attr_tbl => v_pricing_attr_tbl
1715 , x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
1716 );
1717
1718 IF x_return_status = fnd_api.g_ret_sts_error THEN
1719 FOR i in 1 .. x_msg_count LOOP
1720 x_msg_data := oe_msg_pub.get( p_msg_index => i,
1721 p_encoded => 'F' );
1722 FND_MESSAGE.SET_NAME('OZF','OZF_QP_ERROR');
1723 FND_MESSAGE.SET_TOKEN('ERROR_MSG',x_msg_data);
1724 FND_MSG_PUB.ADD;
1725 END LOOP;
1726 RAISE FND_API.g_exc_error;
1727
1728 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1729 FOR i in 1 .. x_msg_count LOOP
1730 x_msg_data := oe_msg_pub.get( p_msg_index => i,
1731 p_encoded => 'F' );
1732
1733 FND_MESSAGE.SET_NAME('OZF','OZF_QP_ERROR');
1734 FND_MESSAGE.SET_TOKEN('ERROR_MSG',x_msg_data);
1735 FND_MSG_PUB.ADD;
1736 END LOOP;
1737 RAISE FND_API.g_exc_unexpected_error;
1738 END IF;
1739 END LOOP;
1740 */
1741 EXCEPTION
1742 WHEN FND_API.G_EXC_ERROR THEN
1743 x_return_status := FND_API.g_ret_sts_error ;
1744 -- ROLLBACK TO add_inventory_item;
1745 FND_MSG_PUB.Count_AND_Get
1746 ( p_count => x_msg_count,
1747 p_data => x_msg_data,
1748 p_encoded => FND_API.G_FALSE
1749 );
1750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1751 x_return_status := FND_API.g_ret_sts_unexp_error ;
1752 -- ROLLBACK TO add_inventory_item;
1753 FND_MSG_PUB.Count_AND_Get
1754 ( p_count => x_msg_count,
1755 p_data => x_msg_data,
1756 p_encoded => FND_API.G_FALSE
1757 );
1758 WHEN OTHERS THEN
1759 x_return_status := FND_API.g_ret_sts_unexp_erroR ;
1760 -- ROLLBACK TO add_inventory_item;
1761 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1762 THEN
1763 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1764 END IF;
1765 FND_MSG_PUB.Count_AND_Get
1766 ( p_count => x_msg_count,
1767 p_data => x_msg_data,
1768 p_encoded => FND_API.G_FALSE
1769 );
1770
1771 END add_inventory_item;
1772
1773 END OZF_PRICELIST_PVT;