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