1 PACKAGE BODY DPP_PRICING_PVT AS
2 /* $Header: dppvqpnb.pls 120.7 2008/03/24 08:09:57 sdasan noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- DPP_PRICING_PVT
7 -- Purpose
8 -- Contains all APIs for Pricing Notifications
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_PRICING_PVT';
17
18 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
19 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppvqpnb.pls';
20 g_trunc_sysdate DATE := trunc(sysdate);
21
22 -- ==============================================================================
23 -- Start of Comments
24 -- ==============================================================================
25 -- API Name : Notify_OutboundPricelists
26 -- Type : Private
27 -- Pre-Req : None
28 -- Function : Derives outbound pricelists information for pricing notification
29 -- Parameters
30 --
31 -- IN
32 -- p_api_version_number IN NUMBER Required
33 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
34 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
35 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_NONE
36 -- p_pl_notify_line_tbl IN OUT dpp_pl_notify_line_tbl_type Required
37 --
38 -- OUT
39 -- x_return_status OUT VARCHAR2
40 -- x_msg_count OUT NUMBER
41 -- x_msg_data OUT VARCHAR2
42 -- Version : Current version 1.0
43 --
44 -- End of Comments
45 -- ==============================================================================
46
47 PROCEDURE Notify_OutboundPricelists(
48 p_api_version IN NUMBER
49 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
50 , p_commit IN VARCHAR2 := FND_API.G_FALSE
51 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE
52 , x_return_status OUT NOCOPY VARCHAR2
53 , x_msg_count OUT NOCOPY NUMBER
54 , x_msg_data OUT NOCOPY VARCHAR2
55 , p_pl_notify_hdr_rec IN OUT NOCOPY dpp_pl_notify_rec_type
56 , p_pl_notify_line_tbl IN OUT NOCOPY dpp_pl_notify_line_tbl_type
57 )
58 IS
59 l_api_name CONSTANT VARCHAR2(30) := 'Notify_OutboundPricelists';
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
62
63 l_result NUMBER;
64 l_return_status VARCHAR2(30);
65 l_pl_notify_hdr_rec dpp_pl_notify_rec_type := p_pl_notify_hdr_rec;
66 l_pl_notify_line_tbl DPP_PRICING_PVT.dpp_pl_notify_line_tbl_type := p_pl_notify_line_tbl;
67 l_pricelist_tbl DPP_PRICING_PVT.dpp_object_name_tbl_type;
68
69 CURSOR get_pricelist_csr(p_inventory_item_id IN NUMBER, p_org_id IN NUMBER)
70 IS
71
72 SELECT
73 qlh.name Pricelist_Name
74 FROM
75 qp_secu_list_headers_v qlh,
76 qp_list_lines_v qll
77 WHERE
78 qlh.active_flag = 'Y' AND
79 g_trunc_sysdate BETWEEN nvl(qlh.start_date_active, g_trunc_sysdate) AND
80 nvl(qlh.end_date_active, g_trunc_sysdate) AND
81 qlh.source_system_code = 'QP' AND
82 qll.product_id = p_inventory_item_id AND
83 qll.list_header_id = qlh.list_header_id AND
84 qll.product_attribute_context = 'ITEM' AND
85 qll.list_line_type_code = 'PLL' AND
86 g_trunc_sysdate BETWEEN nvl(qll.start_date_active, g_trunc_sysdate) AND
87 nvl(qll.end_date_active, g_trunc_sysdate) AND
88 (qlh.orig_org_id = p_org_id OR NVL(qlh.global_flag,'N') = 'Y');
89
90 CURSOR get_item_number_csr(p_inventory_item_id IN NUMBER)
91 IS
92 SELECT
93 msi.concatenated_segments
94 FROM
95 mtl_system_items_kfv msi
96 WHERE
97 inventory_item_id = p_inventory_item_id
98 AND ROWNUM = 1;
99
100
101 CURSOR get_vendor_csr(p_vendor_id IN NUMBER)
102 IS
103 SELECT
104 vendor_name,
105 segment1 vendor_num
106 FROM
107 ap_suppliers
108 WHERE
109 vendor_id = p_vendor_id;
110
111 CURSOR get_vendor_site_csr(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_org_id IN NUMBER)
112 IS
113 SELECT
114 vendor_site_code
115 FROM
116 ap_supplier_sites_all
117 WHERE
118 vendor_site_id = p_vendor_site_id and
119 vendor_id = p_vendor_id and
120 org_id = p_org_id;
121
122 CURSOR get_ou_csr(p_org_id IN NUMBER)
123 IS
124 SELECT
125 name
126 FROM
127 hr_operating_units
128 WHERE
129 organization_id = p_org_id;
130
131 BEGIN
132
133 -- Standard call to check for call compatibility.
134 IF NOT FND_API.Compatible_API_Call ( l_api_version,
135 p_api_version,
136 l_api_name,
137 G_PKG_NAME)
138 THEN
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END IF;
141 -- Initialize message list if p_init_msg_list is set to TRUE.
142 IF FND_API.to_Boolean( p_init_msg_list )
143 THEN
144 FND_MSG_PUB.initialize;
145 END IF;
146
147 -- Debug Message
148 IF g_debug THEN
149 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
150 END IF;
151 -- Initialize API return status to sucess
152 l_return_status := FND_API.G_RET_STS_SUCCESS;
153 --
154 -- API body
155 --
156 IF l_pl_notify_hdr_rec.vendor_id IS NULL THEN
157 IF g_debug THEN
158 DPP_Utility_PVT.debug_message('Vendor ID cannot be NULL');
159 END IF;
160 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
161 FND_MESSAGE.set_token('ID', 'Vendor ID');
162 FND_MSG_PUB.add;
163 RAISE FND_API.G_EXC_ERROR;
164 ELSIF l_pl_notify_hdr_rec.vendor_site_id IS NULL THEN
165 IF g_debug THEN
166 DPP_Utility_PVT.debug_message('Vendor Site ID cannot be NULL');
167 END IF;
168 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
169 FND_MESSAGE.set_token('ID', 'Vendor Site ID');
170 FND_MSG_PUB.add;
171 RAISE FND_API.G_EXC_ERROR;
172 ELSE
173 FOR get_vendor_rec IN get_vendor_csr(p_pl_notify_hdr_rec.vendor_id)
174 LOOP
175
176 p_pl_notify_hdr_rec.vendor_name := get_vendor_rec.vendor_name;
177 p_pl_notify_hdr_rec.vendor_number := get_vendor_rec.vendor_num;
178
179 END LOOP;
180
181 FOR get_vendor_site_rec IN get_vendor_site_csr(p_pl_notify_hdr_rec.vendor_id, p_pl_notify_hdr_rec.vendor_site_id,
182 p_pl_notify_hdr_rec.org_id)
183 LOOP
184
185 p_pl_notify_hdr_rec.vendor_site_code := get_vendor_site_rec.vendor_site_code;
186
187 END LOOP;
188
189 FOR get_ou_rec IN get_ou_csr(p_pl_notify_hdr_rec.org_id)
190 LOOP
191
192 p_pl_notify_hdr_rec.operating_unit := get_ou_rec.name;
193
194 END LOOP;
195
196 END IF;
197
198 FOR i IN l_pl_notify_line_tbl.FIRST..l_pl_notify_line_tbl.LAST
199 LOOP
200
201 IF l_pl_notify_line_tbl(i).inventory_item_id IS NULL THEN
202 IF g_debug THEN
203 DPP_Utility_PVT.debug_message('Inventory Item ID cannot be NULL');
204 END IF;
205 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
206 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
207 FND_MSG_PUB.add;
208 RAISE FND_API.G_EXC_ERROR;
209
210 ELSE
211 OPEN get_pricelist_csr(l_pl_notify_line_tbl(i).inventory_item_id, l_pl_notify_hdr_rec.org_id);
212 LOOP
213 FETCH get_pricelist_csr BULK COLLECT INTO l_pricelist_tbl;
214 EXIT WHEN get_pricelist_csr%NOTFOUND;
215 END LOOP;
216 CLOSE get_pricelist_csr;
217
218 -- Initializing Nested Table
219 IF NOT l_pricelist_tbl.EXISTS(1) THEN
220 l_pricelist_tbl(1) := NULL;
221 END IF;
222
223 p_pl_notify_line_tbl(i).object_name_tbl := l_pricelist_tbl;
224
225 FOR get_item_number_rec IN get_item_number_csr(l_pl_notify_line_tbl(i).inventory_item_id)
226 LOOP
227
228 p_pl_notify_line_tbl(i).item_number := get_item_number_rec.concatenated_segments;
229
230 END LOOP;
231
232 END IF;
233
234 END LOOP;
235
236 IF g_debug THEN
237 DPP_UTILITY_PVT.debug_message('return status for API =>'||l_return_status);
238 END IF;
239
240 -- Debug Message
241 IF g_debug THEN
242 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
243 END IF;
244 -- Standard call to get message count and if count is 1, get message info.
245 FND_MSG_PUB.Count_And_Get
246 (p_count => x_msg_count,
247 p_data => x_msg_data
248 );
249
250 x_return_status := l_return_status;
251
252 EXCEPTION
253
254 WHEN FND_API.G_EXC_ERROR THEN
255
256 x_return_status := FND_API.G_RET_STS_ERROR;
257 -- Standard call to get message count and if count=1, get the message
258 FND_MSG_PUB.Count_And_Get (
259 p_encoded => FND_API.G_FALSE,
260 p_count => x_msg_count,
261 p_data => x_msg_data
262 );
263 IF x_msg_count > 1 THEN
264 FOR I IN 1..x_msg_count LOOP
265 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
266 END LOOP;
267 END IF;
268
269
270 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
271
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 -- Standard call to get message count and if count=1, get the message
274 FND_MSG_PUB.Count_And_Get (
275 p_encoded => FND_API.G_FALSE,
276 p_count => x_msg_count,
277 p_data => x_msg_data
278 );
279 IF x_msg_count > 1 THEN
280 FOR I IN 1..x_msg_count LOOP
281 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
282 END LOOP;
283 END IF;
284
285
286 WHEN OTHERS THEN
287
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
290 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
291 fnd_message.set_token('ROUTINE', 'DPP_PRICING_PVT.Notify_OutboundPricelists');
292 fnd_message.set_token('ERRNO', sqlcode);
293 fnd_message.set_token('REASON', sqlerrm);
294 END IF;
295 -- Standard call to get message count and if count=1, get the message
296 FND_MSG_PUB.Count_And_Get (
297 p_encoded => FND_API.G_FALSE,
298 p_count => x_msg_count,
299 p_data => x_msg_data
300 );
301 IF x_msg_count > 1 THEN
302 FOR I IN 1..x_msg_count LOOP
303 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
304 END LOOP;
305 END IF;
306
307
308 END Notify_OutboundPricelists;
309
310 -- ==============================================================================
311 -- Start of Comments
312 -- ==============================================================================
313 -- API Name : Notify_InboundPricelists
314 -- Type : Private
315 -- Pre-Req : None
316 -- Function : Derives inbound pricelists information for pricing notification
317 -- Parameters
318 --
319 -- IN
320 -- p_api_version_number IN NUMBER Required
321 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
322 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
323 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_NONE
324 -- p_pl_notify_hdr_rec IN OUT dpp_pl_notify_rec_type Required
325 -- p_pl_notify_line_tbl IN OUT dpp_pl_notify_line_tbl_type Required
326 --
327 -- OUT
328 -- x_return_status OUT VARCHAR2
329 -- x_msg_count OUT NUMBER
330 -- x_msg_data OUT VARCHAR2
331 -- Version : Current version 1.0
332 --
333 -- End of Comments
334 -- ==============================================================================
335
336 PROCEDURE Notify_InboundPricelists(
337 p_api_version IN NUMBER
338 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
339 , p_commit IN VARCHAR2 := FND_API.G_FALSE
340 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE
341 , x_return_status OUT NOCOPY VARCHAR2
342 , x_msg_count OUT NOCOPY NUMBER
343 , x_msg_data OUT NOCOPY VARCHAR2
344 , p_pl_notify_hdr_rec IN OUT NOCOPY dpp_pl_notify_rec_type
345 , p_pl_notify_line_tbl IN OUT NOCOPY dpp_pl_notify_line_tbl_type
346 )
347 IS
348 l_api_name CONSTANT VARCHAR2(30) := 'Notify_InboundPricelists';
349 l_api_version CONSTANT NUMBER := 1.0;
350 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
351
352 l_result NUMBER;
353 l_pl_notify_hdr_rec DPP_PRICING_PVT.dpp_pl_notify_rec_type := p_pl_notify_hdr_rec;
354 l_pl_notify_line_tbl DPP_PRICING_PVT.dpp_pl_notify_line_tbl_type := p_pl_notify_line_tbl;
355 l_pricelist_tbl DPP_PRICING_PVT.dpp_object_name_tbl_type;
356
357 CURSOR get_pricelist_csr(p_inventory_item_id IN NUMBER, p_vendor_id IN NUMBER,p_vendor_site_id IN NUMBER, p_org_id IN NUMBER)
358 IS
359 SELECT qlh.name Pricelist_Name
360 FROM qp_secu_list_headers_v qlh,
361 qp_qualifiers_v qqv,
362 qp_list_lines_v qll
363 WHERE qlh.active_flag='Y'
364 and g_trunc_sysdate between nvl(qlh.start_date_active,g_trunc_sysdate)
365 and nvl(qlh.end_date_active,g_trunc_sysdate)
366 and qlh.list_header_id=qqv.list_header_id
367 and qlh.source_system_code='PO'
368 and qqv.qualifier_context='PO_SUPPLIER'
369 and (qualifier_attribute = 'QUALIFIER_ATTRIBUTE1' and qqv.qualifier_attr_value=NVL(p_vendor_id,qqv.qualifier_attr_value))
370 and qll.product_id = p_inventory_item_id
371 and qll.list_header_id=qlh.list_header_id
372 and qll.product_attribute_context='ITEM'
373 and qll.list_line_type_code='PLL'
374 and g_trunc_sysdate between nvl(qll.start_date_active,g_trunc_sysdate)
375 and nvl(qll.end_date_active,g_trunc_sysdate)
376 AND (qlh.orig_org_id = p_org_id OR NVL(qlh.global_flag,'N') = 'Y')
377 UNION
378 SELECT qlh.name Pricelist_Name
379 FROM qp_secu_list_headers_v qlh,
380 qp_qualifiers_v qqv,
381 qp_list_lines_v qll
382 WHERE qlh.active_flag='Y'
383 and g_trunc_sysdate between nvl(qlh.start_date_active,g_trunc_sysdate)
384 and nvl(qlh.end_date_active,g_trunc_sysdate)
385 and qlh.list_header_id=qqv.list_header_id
386 and qlh.source_system_code='PO'
387 and qqv.qualifier_context='PO_SUPPLIER'
388 and (qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' and qqv.qualifier_attr_value=NVL(p_vendor_site_id,qqv.qualifier_attr_value)) and qll.product_id = p_inventory_item_id
389 and qll.list_header_id=qlh.list_header_id
390 and qll.product_attribute_context='ITEM'
391 and qll.list_line_type_code='PLL'
392 and g_trunc_sysdate between nvl(qll.start_date_active,g_trunc_sysdate)
393 and nvl(qll.end_date_active,g_trunc_sysdate)
394 AND (qlh.orig_org_id = p_org_id OR NVL(qlh.global_flag,'N') = 'Y');
395
396 CURSOR get_item_number_csr(p_inventory_item_id IN NUMBER)
397 IS
398 SELECT
399 msi.concatenated_segments
400 FROM
401 mtl_system_items_kfv msi
402 WHERE
403 inventory_item_id = p_inventory_item_id
404 AND ROWNUM = 1;
405
406
407 CURSOR get_vendor_csr(p_vendor_id IN NUMBER)
408 IS
409 SELECT
410 vendor_name,
411 segment1 vendor_num
412 FROM
413 ap_suppliers
414 WHERE
415 vendor_id = p_vendor_id;
416
417 CURSOR get_vendor_site_csr(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_org_id IN NUMBER)
418 IS
419 SELECT
420 vendor_site_code
421 FROM
422 ap_supplier_sites_all
423 WHERE
424 vendor_site_id = p_vendor_site_id and
425 vendor_id = p_vendor_id and
426 org_id = p_org_id;
427
428 CURSOR get_ou_csr(p_org_id IN NUMBER)
429 IS
430 SELECT
431 name
432 FROM
433 hr_operating_units
434 WHERE
435 organization_id = p_org_id;
436
437 BEGIN
438
439 -- Standard call to check for call compatibility.
440 IF NOT FND_API.Compatible_API_Call ( l_api_version,
441 p_api_version,
442 l_api_name,
443 G_PKG_NAME)
444 THEN
445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
446 END IF;
447 -- Initialize message list if p_init_msg_list is set to TRUE.
448 IF FND_API.to_Boolean( p_init_msg_list )
449 THEN
450 FND_MSG_PUB.initialize;
451 END IF;
452
453 -- Debug Message
454 IF g_debug THEN
455 DPP_UTILITY_PVT.debug_message('Private API: ' || l_full_name || 'start');
456 END IF;
457 -- Initialize API return status to sucess
458 x_return_status := FND_API.G_RET_STS_SUCCESS;
459
460 --
461 -- API body
462 --
463 IF l_pl_notify_hdr_rec.vendor_id IS NULL THEN
464 IF g_debug THEN
465 DPP_Utility_PVT.debug_message('Vendor ID cannot be NULL');
466 END IF;
467 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
468 FND_MESSAGE.set_token('ID', 'Vendor ID');
469 FND_MSG_PUB.add;
470 RAISE FND_API.G_EXC_ERROR;
471 ELSIF l_pl_notify_hdr_rec.vendor_site_id IS NULL THEN
472 IF g_debug THEN
473 DPP_Utility_PVT.debug_message('Vendor Site ID cannot be NULL');
474 END IF;
475 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
476 FND_MESSAGE.set_token('ID', 'Vendor Site ID');
477 FND_MSG_PUB.add;
478 RAISE FND_API.G_EXC_ERROR;
479 ELSE
480 FOR get_vendor_rec IN get_vendor_csr(p_pl_notify_hdr_rec.vendor_id)
481 LOOP
482
483 p_pl_notify_hdr_rec.vendor_name := get_vendor_rec.vendor_name;
484 p_pl_notify_hdr_rec.vendor_number := get_vendor_rec.vendor_num;
485
486 END LOOP;
487
488 FOR get_vendor_site_rec IN get_vendor_site_csr(p_pl_notify_hdr_rec.vendor_id, p_pl_notify_hdr_rec.vendor_site_id,
489 p_pl_notify_hdr_rec.org_id)
490 LOOP
491
492 p_pl_notify_hdr_rec.vendor_site_code := get_vendor_site_rec.vendor_site_code;
493
494 END LOOP;
495
496 FOR get_ou_rec IN get_ou_csr(p_pl_notify_hdr_rec.org_id)
497 LOOP
498
499 p_pl_notify_hdr_rec.operating_unit := get_ou_rec.name;
500
501 END LOOP;
502
503 FOR i IN l_pl_notify_line_tbl.FIRST..l_pl_notify_line_tbl.LAST
504 LOOP
505
506 IF l_pl_notify_line_tbl(i).inventory_item_id IS NULL THEN
507 IF g_debug THEN
508 DPP_Utility_PVT.debug_message('Inventory Item ID cannot be NULL');
509 END IF;
510 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
511 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
512 FND_MSG_PUB.add;
513 RAISE FND_API.G_EXC_ERROR;
514
515 ELSE
516
517 OPEN get_pricelist_csr(l_pl_notify_line_tbl(i).inventory_item_id, l_pl_notify_hdr_rec.vendor_id,l_pl_notify_hdr_rec.vendor_site_id, l_pl_notify_hdr_rec.org_id);
518 LOOP
519 FETCH get_pricelist_csr BULK COLLECT INTO l_pricelist_tbl;
520 EXIT WHEN get_pricelist_csr%NOTFOUND;
521 END LOOP;
522 CLOSE get_pricelist_csr;
523
524 -- Initializing Nested Table
525 IF NOT l_pricelist_tbl.EXISTS(1) THEN
526 l_pricelist_tbl(1) := NULL;
527 END IF;
528
529 p_pl_notify_line_tbl(i).object_name_tbl := l_pricelist_tbl;
530
531 FOR get_item_number_rec IN get_item_number_csr(l_pl_notify_line_tbl(i).inventory_item_id)
532 LOOP
533
534 p_pl_notify_line_tbl(i).item_number := get_item_number_rec.concatenated_segments;
535
536 END LOOP;
537
538 END IF;
539
540 END LOOP;
541
542 END IF;
543
544 IF g_debug THEN
545 DPP_UTILITY_PVT.debug_message('return status for API =>'||x_return_status);
546 END IF;
547
548 IF g_debug THEN
549 DPP_UTILITY_PVT.debug_message('Private API: ' || l_full_name || 'end');
550 END IF;
551 -- Standard call to get message count and if count is 1, get message info.
552 FND_MSG_PUB.Count_And_Get
553 (p_count => x_msg_count,
554 p_data => x_msg_data
555 );
556
557 EXCEPTION
558
559 WHEN FND_API.G_EXC_ERROR THEN
560
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 -- Standard call to get message count and if count=1, get the message
563 FND_MSG_PUB.Count_And_Get (
564 p_encoded => FND_API.G_FALSE,
565 p_count => x_msg_count,
566 p_data => x_msg_data
567 );
568 IF x_msg_count > 1 THEN
569 FOR I IN 1..x_msg_count LOOP
570 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
571 END LOOP;
572 END IF;
573
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
577 -- Standard call to get message count and if count=1, get the message
578 FND_MSG_PUB.Count_And_Get (
579 p_encoded => FND_API.G_FALSE,
580 p_count => x_msg_count,
581 p_data => x_msg_data
582 );
583 IF x_msg_count > 1 THEN
584 FOR I IN 1..x_msg_count LOOP
585 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
586 END LOOP;
587 END IF;
588
589 WHEN OTHERS THEN
590
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
593 THEN
594 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
595 fnd_message.set_token('ROUTINE', 'DPP_PRICING_PVT.Notify_InboundPricelists');
596 fnd_message.set_token('ERRNO', sqlcode);
597 fnd_message.set_token('REASON', sqlerrm);
598 FND_MSG_PUB.ADD;
599 DPP_UTILITY_PVT.debug_message('Error in notify inbound pricelists: '||SQLERRM);
600 END IF;
601 -- Standard call to get message count and if count=1, get the message
602 FND_MSG_PUB.Count_And_Get (
603 p_encoded => FND_API.G_FALSE,
604 p_count => x_msg_count,
605 p_data => x_msg_data
606 );
607 IF x_msg_count > 1 THEN
608 FOR I IN 1..x_msg_count LOOP
609 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
610 END LOOP;
611 END IF;
612
613 END Notify_InboundPricelists;
614
615 -- ==============================================================================
616 -- Start of Comments
617 -- ==============================================================================
618 -- API Name : Notify_Promotions
619 -- Type : Private
620 -- Pre-Req : None
621 -- Function : Derives information for promotions notification
622 -- Parameters
623 --
624 -- IN
625 -- p_api_version_number IN NUMBER Required
626 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
627 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
628 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_NONE
629 -- p_pl_notify_line_tbl IN OUT dpp_pl_notify_line_tbl_type Required
630 --
631 -- OUT
632 -- x_return_status OUT VARCHAR2
633 -- x_msg_count OUT NUMBER
634 -- x_msg_data OUT VARCHAR2
635 -- Version : Current version 1.0
636 --
637 -- End of Comments
638 -- ==============================================================================
639
640 PROCEDURE Notify_Promotions(
641 p_api_version IN NUMBER
642 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
643 , p_commit IN VARCHAR2 := FND_API.G_FALSE
644 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE
645 , x_return_status OUT NOCOPY VARCHAR2
646 , x_msg_count OUT NOCOPY NUMBER
647 , x_msg_data OUT NOCOPY VARCHAR2
648 , p_pl_notify_hdr_rec IN OUT NOCOPY dpp_pl_notify_rec_type
649 , p_pl_notify_line_tbl IN OUT NOCOPY dpp_pl_notify_line_tbl_type
650 )
651 IS
652 l_api_name CONSTANT VARCHAR2(30) := 'Notify_Promotions';
653 l_api_version CONSTANT NUMBER := 1.0;
654 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
655
656 l_result NUMBER;
657 l_count NUMBER;
658
659 l_pl_notify_hdr_rec DPP_PRICING_PVT.dpp_pl_notify_rec_type := p_pl_notify_hdr_rec;
660 l_pl_notify_line_tbl DPP_PRICING_PVT.dpp_pl_notify_line_tbl_type := p_pl_notify_line_tbl;
661 l_offer_tbl DPP_PRICING_PVT.dpp_object_name_tbl_type;
662
663 CURSOR get_offers_csr(p_inventory_item_id IN NUMBER) IS
664 SELECT oov.name offer_name
665 FROM ozf_offers_v oov,
666 qp_modifier_summary_v qmsv
667 WHERE qmsv.list_header_id = oov.list_header_id
668 AND g_trunc_sysdate between nvl(oov.start_date_active,g_trunc_sysdate) AND nvl(oov.end_date_active,g_trunc_sysdate)
669 AND g_trunc_sysdate between nvl(qmsv.start_date_active,g_trunc_sysdate) AND nvl(qmsv.end_date_active,g_trunc_sysdate)
670 AND product_attribute_context = 'ITEM'
671 AND qmsv.prod_attr_segment_name = 'INVENTORY_ITEM_ID'
672 AND qmsv.product_attr_val = to_char(p_inventory_item_id)
673 AND oov.active_flag = 'Y'
674 GROUP BY oov.name
675 UNION
676 SELECT oov.name offer_name
677 FROM ozf_offers_v oov,
678 qp_modifier_summary_v qmsv,
679 mtl_item_categories mic
680 WHERE qmsv.list_header_id = oov.list_header_id
681 AND g_trunc_sysdate between nvl(oov.start_date_active,g_trunc_sysdate) AND nvl(oov.end_date_active,g_trunc_sysdate)
682 AND product_attribute_context = 'ITEM'
683 AND qmsv.prod_attr_segment_name = 'PRODUCT_CATEGORY'
684 AND mic.inventory_item_id = p_inventory_item_id
685 AND mic.category_id= product_attr_value
686 AND oov.active_flag = 'Y'
687 GROUP BY oov.name
688 UNION
689 SELECT NVL(qpl.description, qpl.name) offer_name
690 FROM ozf_activity_products oap,
691 qp_list_headers_all qpl
692 WHERE oap.OBJECT_TYPE IN('SCAN_DATA','LUMPSUM','NET_ACCRUAL','VOLUME_OFFER')
693 AND oap.ITEM = p_inventory_item_id
694 AND oap.object_id = qpl.list_header_id
695 AND g_trunc_sysdate between nvl(oap.start_date,g_trunc_sysdate) AND nvl(oap.end_date,g_trunc_sysdate)
696 AND oap.active_flag = 'Y'
697 GROUP BY NVL(qpl.description, qpl.name)
698 UNION
699 SELECT NVL(qpl.description, qpl.name) offer_name
700 FROM ams_act_products oap,
701 qp_list_headers_all qpl,
702 ozf_offers oo
703 WHERE oap.arc_act_product_used_by ='OFFR'
704 AND oap.inventory_item_id = p_inventory_item_id
705 AND oap.act_product_used_by_id = qpl.list_header_id
706 AND oap.enabled_flag = 'Y'
707 AND oo.offer_code = qpl.name
708 AND oo.status_code = 'ACTIVE'
709 AND trunc(nvl(oo.start_date,sysdate)) <= trunc(sysdate)
710 GROUP BY NVL(qpl.description, qpl.name);
711
712 CURSOR get_item_number_csr(p_inventory_item_id IN NUMBER)
713 IS
714 SELECT msi.concatenated_segments
715 FROM mtl_system_items_kfv msi
716 WHERE inventory_item_id = p_inventory_item_id
717 AND ROWNUM = 1;
718
719 CURSOR get_vendor_csr(p_vendor_id IN NUMBER)
720 IS
721 SELECT
722 vendor_name,
723 segment1 vendor_num
724 FROM
725 ap_suppliers
726 WHERE
727 vendor_id = p_vendor_id;
728
729 CURSOR get_vendor_site_csr(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER, p_org_id IN NUMBER)
730 IS
731 SELECT
732 vendor_site_code
733 FROM
734 ap_supplier_sites_all
735 WHERE
736 vendor_site_id = p_vendor_site_id and
737 vendor_id = p_vendor_id and
738 org_id = p_org_id;
739
740 CURSOR get_ou_csr(p_org_id IN NUMBER)
741 IS
742 SELECT
743 name
744 FROM
745 hr_operating_units
746 WHERE
747 organization_id = p_org_id;
748
749 BEGIN
750
751 -- Standard call to check for call compatibility.
752 IF NOT FND_API.Compatible_API_Call ( l_api_version,
753 p_api_version,
754 l_api_name,
755 G_PKG_NAME)
756 THEN
757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 END IF;
759 -- Initialize message list if p_init_msg_list is set to TRUE.
760 IF FND_API.to_Boolean( p_init_msg_list )
761 THEN
762 FND_MSG_PUB.initialize;
763 END IF;
764
765 -- Debug Message
766 IF g_debug THEN
767 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
768 END IF;
769 -- Initialize API return status to sucess
770 x_return_status := FND_API.G_RET_STS_SUCCESS;
771
772 --
773 -- API body
774 --
775
776 IF l_pl_notify_hdr_rec.vendor_id IS NULL THEN
777 IF g_debug THEN
778 DPP_Utility_PVT.debug_message('Vendor ID cannot be NULL');
779 END IF;
780 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
781 FND_MESSAGE.set_token('ID', 'Vendor ID');
782 FND_MSG_PUB.add;
783 RAISE FND_API.G_EXC_ERROR;
784 ELSIF l_pl_notify_hdr_rec.vendor_site_id IS NULL THEN
785 IF g_debug THEN
786 DPP_Utility_PVT.debug_message('Vendor Site ID cannot be NULL');
787 END IF;
788 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
789 FND_MESSAGE.set_token('ID', 'Vendor Site ID');
790 FND_MSG_PUB.add;
791 RAISE FND_API.G_EXC_ERROR;
792 ELSIF l_pl_notify_hdr_rec.org_id IS NULL THEN
793 IF g_debug THEN
794 DPP_Utility_PVT.debug_message('Org ID cannot be NULL');
795 END IF;
796 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
797 FND_MESSAGE.set_token('ID', 'Org ID');
798 FND_MSG_PUB.add;
799 RAISE FND_API.G_EXC_ERROR;
800 ELSE
801 FOR get_vendor_rec IN get_vendor_csr(p_pl_notify_hdr_rec.vendor_id) LOOP
802 p_pl_notify_hdr_rec.vendor_name := get_vendor_rec.vendor_name;
803 p_pl_notify_hdr_rec.vendor_number := get_vendor_rec.vendor_num;
804 END LOOP;
805
806 FOR get_vendor_site_rec IN get_vendor_site_csr(p_pl_notify_hdr_rec.vendor_id, p_pl_notify_hdr_rec.vendor_site_id,
807 p_pl_notify_hdr_rec.org_id) LOOP
808 p_pl_notify_hdr_rec.vendor_site_code := get_vendor_site_rec.vendor_site_code;
809 END LOOP;
810
811 FOR get_ou_rec IN get_ou_csr(p_pl_notify_hdr_rec.org_id) LOOP
812 p_pl_notify_hdr_rec.operating_unit := get_ou_rec.name;
813 END LOOP;
814 IF l_pl_notify_line_tbl.EXISTS(1) THEN
815 FOR i in l_pl_notify_line_tbl.FIRST..l_pl_notify_line_tbl.LAST LOOP
816 IF l_pl_notify_line_tbl(i).inventory_item_id IS NULL THEN
817 IF g_debug THEN
818 DPP_Utility_PVT.debug_message('Inventory Item ID cannot be NULL');
819 END IF;
820 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
821 FND_MESSAGE.set_token('ID', 'Inventory Item ID');
822 FND_MSG_PUB.add;
823 RAISE FND_API.G_EXC_ERROR;
824 ELSE
825 FOR get_item_number_rec IN get_item_number_csr(l_pl_notify_line_tbl(i).Inventory_Item_ID) LOOP
826 l_pl_notify_line_tbl(i).Item_Number := get_item_number_rec.concatenated_segments ;
827 END LOOP;
828
829 OPEN get_offers_csr(l_pl_notify_line_tbl(i).inventory_item_id); LOOP
830 FETCH get_offers_csr BULK COLLECT INTO l_offer_tbl;
831 EXIT WHEN get_offers_csr%NOTFOUND;
832 END LOOP;
833 CLOSE get_offers_csr;
834 -- Initializing Nested Table
835 IF NOT l_offer_tbl.EXISTS(1) THEN
836 l_offer_tbl(1) := NULL;
837 END IF;
838 l_pl_notify_line_tbl(i).object_name_tbl := l_offer_tbl;
839 END IF;
840 END LOOP;
841 ELSE
842 IF g_debug THEN
843 DPP_Utility_PVT.debug_message('Line detals cannot be NULL');
844 END IF;
845 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
846 FND_MESSAGE.set_token('ID', 'Line Details');
847 FND_MSG_PUB.add;
848 RAISE FND_API.G_EXC_ERROR;
849 END IF;
850 END IF;
851
852 --Reassign the local variable to the out variable
853 p_pl_notify_line_tbl := l_pl_notify_line_tbl ;
854
855 IF g_debug THEN
856 DPP_UTILITY_PVT.debug_message('return status for API =>'||x_return_status);
857 END IF;
858 -- Debug Message
859 IF g_debug THEN
860 DPP_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
861 END IF;
862 -- Standard call to get message count and if count is 1, get message info.
863 FND_MSG_PUB.Count_And_Get
864 (p_count => x_msg_count,
865 p_data => x_msg_data
866 );
867 EXCEPTION
868
869 WHEN FND_API.G_EXC_ERROR THEN
870
871 x_return_status := FND_API.G_RET_STS_ERROR;
872 -- Standard call to get message count and if count=1, get the message
873 FND_MSG_PUB.Count_And_Get (
874 p_encoded => FND_API.G_FALSE,
875 p_count => x_msg_count,
876 p_data => x_msg_data
877 );
878 IF x_msg_count > 1 THEN
879 FOR I IN 1..x_msg_count LOOP
880 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
881 END LOOP;
882 END IF;
883
884
885 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
886
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888 -- Standard call to get message count and if count=1, get the message
889 FND_MSG_PUB.Count_And_Get (
890 p_encoded => FND_API.G_FALSE,
891 p_count => x_msg_count,
892 p_data => x_msg_data
893 );
894 IF x_msg_count > 1 THEN
895 FOR I IN 1..x_msg_count LOOP
896 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
897 END LOOP;
898 END IF;
899
900 WHEN OTHERS THEN
901
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
904 THEN
905 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
906 fnd_message.set_token('ROUTINE', 'DPP_PRICING_PVT.Notify_Promotions');
907 fnd_message.set_token('ERRNO', sqlcode);
908 fnd_message.set_token('REASON', sqlerrm);
909 FND_MSG_PUB.ADD;
910 DPP_UTILITY_PVT.debug_message('Error in notify promotions: '||SQLERRM);
911 END IF;
912 -- Standard call to get message count and if count=1, get the message
913 FND_MSG_PUB.Count_And_Get (
914 p_encoded => FND_API.G_FALSE,
915 p_count => x_msg_count,
916 p_data => x_msg_data
917 );
918 IF x_msg_count > 1 THEN
919 FOR I IN 1..x_msg_count LOOP
920 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
921 END LOOP;
922 END IF;
923
924 END Notify_Promotions;
925
926 END DPP_PRICING_PVT;
[Home] [Help]